September 30, 2011 
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
-- Updated on Jan 25, 2012 to fix aging buckets to use
-- due dates
-- Updated Apr 25, 2013 to fix aging buckets for credit docs
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
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
-- add permissions for DYNGRP
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.
Victoria,
Do you know of this same report that would also have the details with it? The invoices that need to be paid in which make up these totals? I know there is an AP Trail Balance Report Writer report for this, but my users are having problems exporting this to Excel and it’s not exactly all that ‘pretty’ as we all know. This summary was great and I was able to get it in Excel Report Builder and it really did the job as far as a summary. If I could get details, that would rock!
Thanks,
John Olley
Hi John,
Keep an eye on my blog later today, I will post a new view with code for a detailed aging report.
-Victoria
That’s awesome! Thanks so much!
-John
John,
No problem, hopefully this is what you need. You should be able to see it here now.
-Victoria
Thanks Victoria for this. However I would like to ask if this can be modified to give a weekly aging instead of 30, 60,90. Can it be modified to give the amount due by vendor for every friday until the end of the year?
Thanks for any direction you can give me on this
Erica,
It sounds like you’re actually looking more for a cash forecast rather than an aging? I have not done something like that before, but I would think it’s doable in a similar way to what I have here. You would have to change the date calculations to what you need and create additional aging buckets as needed. Take a look at my blog post on coding dates in SQL to help you with the formulas. There are a number of examples there for specific days of the week. For what you’re asking, though, it might be best to find the last Friday (or first Friday) you want, then keep adding or subtracting 1 week at a time.
One issue you might run into with the logic as you have described it is that you would have a constantly changing number of columns in your results. For example, today, there might be about 18 weeks left in the year, but in the middle of December you would only get 2 or 3 columns in your results. For aging purposes, you might instead decide to always go X weeks out and everything else would be in an ‘over X weeks’ aging bucket?
Another thing to consider is the naming of your columns – if you are going to be using this in SmartList, it would have to be something generic like ’1 week’, ’2 weeks’, etc. In a different reporting tool you could actually change the name of the columns dynamically based on either a field you add to the view or a calculation in the report.
Hope that helps to give you some ideas.
-Victoria
Yes it does. I appreciate you help.
Thanks again
Thanks for the knowledge shared Victoria on this reporting. I was able to run the query in SQL and it was successful. i would like you to guide me on how to publish this report into smartlist using smartlist builder. Thanks
Taiwo,
I have a post about this: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/.
-Victoria
Is there a similar script to create a Receivables aging?
Lyndy,
How about this one?
-Victoria
Hi Victoria,
I saw that one but I do not think that will work for the AR side because it does not display the aging buckets. My client is looking for a SQL view that will let them display different AR aging buckets than what they have defined in the Receivables Setup window.
I have used that AR SQL View in the past, but I don’t think it will work with this specific request
Lyndy,
You can use the example in the payables view to do this yourself…or give me a few days and I will post a new view for AR with aging buckets.
-Victoria
Good point. I’ll give it a shot! Thanks Victoria!
Hi Lyndy,
Take a look at my latest post – is this what you need?
-Victoria
Yep, this is exactly what I needed. Thanks Victoria!
Thank you Victoria – I really appreciate your assistance. I have one more question – what would I need to add in order to pull historical data and run historically as of different dates?
Erin,
For historical trial balances, take a look at Siva’s blog:
http://msdynamicstips.com/2011/07/25/payables-hatb-with-aging-by-due-date-using-gl-posting-date/
http://msdynamicstips.com/2011/07/25/payables-hatb-with-aging-by-payment-terms-using-document-date/
-Victoria
exactly what i’ve been trying to do for last 2 days! This place is a veritable treasure trove of useful GP info.
Many thanks
This looks like it could work – Thank you for your assistance!
Hi Erin,
Try something like this, it will only work for the PAY distribution and only if there is one PAY distribution per transaction, but it will give you an idea:
SELECT
VM.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,
G.ACTNUMST Account_Number,
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()) > 3
THEN P.CURTRXAM * -1
ELSE 0
END) [Current],
sum(CASE
WHEN DATEDIFF(d, P.DUEDATE, 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 > 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.DUEDATE, getdate()) > 90
AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1
ELSE 0
END) [91_and_Over]
FROM PM00200 VM
INNER JOIN PM20000 P
ON P.VENDORID = VM.VENDORID
LEFT OUTER JOIN PM10100 D
ON P.VCHRNMBR = D.VCHRNMBR
AND P.CNTRLTYP = D.CNTRLTYP
AND D.DISTTYPE = 2
LEFT OUTER JOIN
GL00105 G ON D.DSTINDX = G.ACTINDX
WHERE P.CURTRXAM <> 0 AND VOIDED = 0
GROUP BY VM.VENDORID, VM.VENDNAME,
VM.PYMTRMID, VM.VNDCLSID, G.ACTNUMST
Sorry for the multiple posts, the comment was stripping out all the less than and greater than signs, so I had to fix them…hopefully that will work for you.
-Victoria
We look at the invoice distribution accounts in order to determine the fund.
Erin,
Then you might be better off with a variation of my AP Distributions view, which has the GL distributions. Or some combination of that one and this current aging view. If there is a clear cut way you can determine the fund by looking at the account number(s), then it can be coded.
-Victoria
We manually look each of the invoices up…
And what information on each invoice tell you the fund? Is there one field that you can consistently use for this?
-Victoria
We have used GP for only a year and have been manually calculating this from the detail AP. Currently we do not have a report that can break this down between funds.
Erin,
What on the detail AP helps you determine what fund each transaction belongs to?
-Victoria
I work for a municipality and we use fund accounting, therefore we have multiple balance sheets within one company. How can you break out the aged payables between funds?
Erin,
How do you do this right now in GP?
-Victoria
Victoria
You can also take a look at the following scripts which I had posted for Payables Aging.
http://msdynamicstips.com/2011/07/25/payables-hatb-with-aging-by-due-date-using-gl-posting-date/
http://msdynamicstips.com/2011/07/25/payables-hatb-with-aging-by-payment-terms-using-document-date/
The similar queries for Receivables Aging is posted in the URL’s below,
http://msdynamicstips.com/2011/07/21/receivables-hatb-with-aging-by-payment-terms-using-gl-posting-date/
http://msdynamicstips.com/2011/07/21/receivables-hatb-with-aging-by-payment-terms-using-document-date/
Regards
Siva
How do you use/run the view?
Bill,
I have a post with an example here: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/. There are also a bunch of links to additional resources at the bottom of that post.
-Victoria