hi all,
I am working up a Historical Aged Trial balance report, and I would like to see this restricted by GL posting date, and not document date.
Can someone tell me how gl30000 might link to this query? Although, upon further inspection, I might be able to join it to the PA receipts (PA31101)?
thanks,
geo
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.PA10600
INNER JOIN
dbo.PA10601
ON dbo.PA10600.PApurordnum = dbo.PA10601.PApurordnum
INNER JOIN
dbo.POP10110
ON dbo.PA10601.PApurordnum = dbo.POP10110.PONUMBER
RIGHT OUTER JOIN
dbo.PM00200 AS VM
INNER JOIN
dbo.PM20000 AS P
ON P.VENDORID = VM.VENDORID
ON dbo.PA10600.PApurordnum = P.PORDNMBR
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
HAVING (VM.VENDORID between @I_sStartVendorID and @I_sEndVendorID)
AND (P.DOCDATE < @I_dAgingDate)
AND (PA10601.PAPROJNUMBER BETWEEN @I_sProjectStart AND @I_sProjectEnd)
AND (P.DOCNUMBR BETWEEN @I_sStartDocumentNumber AND @I_sEndDocumentNumber);
END