Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
Ranger 3/27/2017 2:25:30 PM

looking to join this to the GL table for posted date

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

Version: GP 2015, GP 2013, GP 2010
Section: Dynamics GP, SQL Scripts, SSRS








Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables