Need Exchange Rate in Historical Aged Trial Balance

Hi all,

I am no expert in the GP table structure, but my manager asked me to add the exchange rate and currency to the HATB report (PM). I have a query for the HATB report, but I DID NOT use the MS canned query, which is a monster.  I tried adding in the gl20000 table, but it times out, and i'm not even sure what to join.

help is much appreciated.

SELECT        VM.VENDORID AS Vendor_ID, VM.VENDNAME AS Vendor_Name, VM.VNDCLSID AS Vendor_Class, VM.PYMTRMID AS Vendor_Terms, VM.CRLMTDLR AS Credit_Limit, 
                         SUM(CASE WHEN P.DOCTYPE < 4 THEN P.CURTRXAM ELSE P.CURTRXAM * - 1 END) AS Unapplied_Amount, SUM(CASE WHEN DATEDIFF(d, P.DUEDATE, getdate()) < 31 AND 
                         P.DOCTYPE < 4 THEN P.CURTRXAM WHEN DATEDIFF(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END) AS [Current], SUM(CASE WHEN DATEDIFF(d, P.DUEDATE, 
                         getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN DATEDIFF(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END) 
                         AS [31_to_60_Days], SUM(CASE WHEN DATEDIFF(d, P.DUEDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN DATEDIFF(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND 
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END) AS [61_to_90_Days], SUM(CASE WHEN DATEDIFF(d, P.DUEDATE, getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN DATEDIFF(d, P.DOCDATE, 
                         getdate()) > 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END) AS [91_and_Over], P.APLYWITH, P.DOCDATE, P.DOCNUMBR, dbo.PA10601.PAPROJNUMBER, dbo.PA10601.PABase_Unit_Cost, 
                         dbo.POP10110.XCHGRATE, dbo.POP10110.CURNCYID, dbo.PA10601.PApurordnum
FROM            dbo.PM00200 AS VM INNER JOIN
                         dbo.PM20000 AS P ON P.VENDORID = VM.VENDORID LEFT OUTER JOIN
                         dbo.PA10600 INNER JOIN
                         dbo.PA10601 ON dbo.PA10600.PApurordnum = dbo.PA10601.PApurordnum INNER JOIN
                         dbo.POP10110 ON dbo.PA10601.PApurordnum = dbo.POP10110.PONUMBER ON P.PORDNMBR = dbo.PA10600.PApurordnum
WHERE        (P.CURTRXAM <> 0) AND (P.VOIDED = 0)
GROUP BY VM.VENDORID, VM.VENDNAME, VM.PYMTRMID, VM.VNDCLSID, VM.CRLMTDLR, P.APLYWITH, P.DOCDATE, P.DOCNUMBR, dbo.PA10601.PAPROJNUMBER, dbo.PA10601.PABase_Unit_Cost, 
                         dbo.POP10110.XCHGRATE, dbo.POP10110.CURNCYID, dbo.PA10601.PApurordnum

 

thanks,

George h

Related
Recommended