Interesting Findings & Knowledge Sharing

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. Continue reading →