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
-- https://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
-- Updated Sep 26, 2013 to add vendor credit limit
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
VM.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,
VM.CRLMTDLR Credit_Limit,
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, VM.CRLMTDLR
-- 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 Yudin 
Hi Victoria, on a SQL view, is there a way to bring the in the Checkbook ID attached to the invoice while the invoice is in Work or Open Status? Just to be clear, this would be an invoice that is entered or posted with no payment applied to it (Not in ‘History’). To my understanding, the Checkbook ID is selected while entering a payment trx on the Batch Entry Window. Could that Checkbook ID selected on the Batch Entry window be brought into a SQL view? I’ve been searching through tables and I’m seeing that the checkbook ID is not captured until the payment processing starts.
LikeLike
Hi Mwalimu, you mention “the Checkbook ID attached to the invoice” – what exactly does this mean? Where have you attached a Checkbook ID to an invoice prior to paying it?
-Victoria
LikeLike
Hi, my apologies, what I meant is; when a batch is made on the Batch Entry window in the purchase module, a checkbook ID is selected. Transactions are then created inside the batch. is the Checkbook ID that is selected on the Batch entry window tracked in tables for Invoices that are unpaid? I hope this is clearer
LikeLike
Thanks Mwalimu, that helps.
The Checkbook ID entered on the batch window is stored in the SY00500 while the batch is not posted. However, that is simply the DEFAULT Checkbook ID that will be used IF you happen to enter payments in that batch. Even with this being the default, you can still change the Checkbook ID on any payment you enter in the batch. A Checkbook ID is not actually associated with any non-payment transaction in any way.
Once the batch is posted, the default Checkbook ID that was on the batch (and in the SY00500 table) is not stored anywhere and is not associated with any transactions that were in that batch. Even if there were payments entered in the batch, those would have the actual Checkbook ID entered for them saved, not the default Checkbook ID from the batch.
Hope that helps with your query.
-Victoria
LikeLike
Hi Victoria, You have confirmed my thought process about this issue. Thank you as always!
MP
LikeLike
Hi Victoria,
I am trying to recreate the AP Aging as of 12/31/2019. I tried following the links to Siva’s blogs but they are no longer valid. I also ran the SQL in your 2011 response to Erin, but that only puts the payables in the aging buckets, based on a date (I replaced getdate() with a variable holding the value ’12/31/2019′), for currently open payables. It does not look at the historical table and back out payments made after 12/31/2019 and it does not exclude invoices with a docdate > 12/31/2019. In my mind, I have an idea how I can use the Apply tables to recreate what the aging would have look like at 12/31/2019, but my brain keeps freezing up when I sit down to write the code. I am new to GP and am just learning the data of each table and their relationships. Any advice is much appreciated to get me over my writer’s block.
Thanks,
Tony
LikeLike
Hi Tony,
Reproducing this in SQL is extremely difficult, it took me over 40 hours with my knowledge of the GP tables, relationships, data flow and logic. It’s more complicated than just the apply logic, as there are also voids, discounts, and other stuff to include.
And even after that it’s not always 100% what everyone wants. Because of that I do not give this code out for free and I customize it each time. I do offer this code for sale if what I have meets your needs – let me know if you are interested and I will email you directly about this.
Thanks,
-Victoria
LikeLike
This SQL View is cool. However, I want to add one more column in it which should be sum of ( 61 to 90 Days + 91 and Over ). How can I do that? Can you share updated SQL View with required new column?
or if I have created a smartlist and want to add a calculated field showing sum of these 2 columns, how can I do that? (Currently I am unable to do that from smartlist builder, add calculated field, as it gives me an error of incorrect syntax near ‘.61’.)
LikeLike
You can simply add the following on line 56:
,sum (CASEWHEN DATEDIFF(d, P.DUEDATE, getdate()) > 60
and P.DOCTYPE < 4 THEN P.CURTRXAM
WHEN DATEDIFF(d, P.DOCDATE, getdate()) > 60
and P.DOCTYPE > 3 THEN P.CURTRXAM * -1
ELSE 0
END) [61_and_Over]
-Victoria
LikeLike
great. Thank you so Much Victoria 🙂
LikeLike
Victoria, is there any way to take into account scheduled payments on the aging view? I was under the impression that GP would move the invoice to history, but on the view it looks like it is showing up as unpaid, maybe I’m missing something? Technically, it isn’t paid, but I think that they just want it to not show past due since they have made a payment contract with the vendor so it should actually show as current rather then past due.
LikeLike
Hi Rob,
This code does not take Scheduled Payments into account. So it will actually show incorrect results if you have Scheduled Payments. I have to say, this is the first request I have gotten for including Scheduled Payments in any of the payables views…I will put in on the request list. If you wanted to do this yourself, you would need to add some code to treat DOCTYPE 7 differently.
-Victoria
LikeLike
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
LikeLike
Hi John,
Keep an eye on my blog later today, I will post a new view with code for a detailed aging report.
-Victoria
LikeLike
That’s awesome! Thanks so much!
-John
LikeLike
John,
No problem, hopefully this is what you need. You should be able to see it here now.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Yes it does. I appreciate you help.
Thanks again
LikeLike
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
LikeLike
Taiwo,
I have a post about this: https://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/.
-Victoria
LikeLike
Is there a similar script to create a Receivables aging?
LikeLike
Lyndy,
How about this one?
-Victoria
LikeLike
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 😦
LikeLike
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
LikeLike
Good point. I’ll give it a shot! Thanks Victoria!
LikeLike
Hi Lyndy,
Take a look at my latest post – is this what you need?
-Victoria
LikeLike
Yep, this is exactly what I needed. Thanks Victoria!
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
This looks like it could work – Thank you for your assistance!
LikeLike
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
LikeLike
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
LikeLike
We look at the invoice distribution accounts in order to determine the fund.
LikeLike
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
LikeLike
We manually look each of the invoices up…
LikeLike
And what information on each invoice tell you the fund? Is there one field that you can consistently use for this?
-Victoria
LikeLike
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.
LikeLike
Erin,
What on the detail AP helps you determine what fund each transaction belongs to?
-Victoria
LikeLike
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?
LikeLike
Erin,
How do you do this right now in GP?
-Victoria
LikeLike
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
LikeLike
How do you use/run the view?
LikeLike
Bill,
I have a post with an example here: https://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
LikeLike