SQL view for current Payables aging in Dynamics GP

Over the past few years I have had several requests for a summary current Payables aging report that can be easily exported into Excel. Yes, you can play with the Report Writer aging report to take out the headers and make it export to Excel, but sometimes there are other reasons for wanting a report outside of Report Writer. 

Below is a script to create a view for this. It is only looking at functional currency and will return one row per vendor with a balance. I am hard-coding the aging using the default aging setup installed with GP, which is aging by due date and using the following buckets:

• Current
• 31 to 60 Days
• 61 to 90 Days
• 91 and Over

If you would like to use different aging buckets, just follow the examples in my code. 

~~~~~

create view view_Current_Payables_Aging_Summary
as
/******************************************************************
view_Current_Payables_Aging_Summary
Created Sep 30, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates please see http://victoriayudin.com/gp-reports/
- Shows current AP aging
- Functional currency only
******************************************************************/

SELECT
VM.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,

sum(CASE
WHEN P.DOCTYPE < 4 THEN P.CURTRXAM
ELSE P.CURTRXAM * -1
END) 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) 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) 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) 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) 91_and_Over

FROM PM00200 VM --vendor master 
INNER JOIN PM20000 P -- open payables 
ON P.VENDORID = VM.VENDORID

WHERE	P.CURTRXAM <> 0 AND VOIDED = 0

GROUP BY VM.VENDORID, VM.VENDNAME, VM.PYMTRMID, VM.VNDCLSID

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Current_Payables_Aging_Summary TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code Tagged: GP Reports code, GP SQL view, Payables, SQL code

More:
SQL view for current Payables aging in Dynamics GP