April 24, 2013 
I have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per open (unpaid) payables transaction. 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 and change the labels and number of days.
Additional resources:
create view view_Current_Payables_Aging_Detail
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Payables_Aging_Detail
-- Apr 24, 2013 - Victoria Yudin, Flexible Solutions, Inc.
-- Shows current AP aging
-- Functional currency only
-- Aging by due date
-- For updates please see:
-- https://victoriayudin.com/gp-reports/
-- For other payables SQL scripts:
-- https://victoriayudin.com/gp-reports/payables-sql-views/
-- 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,
case P.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
end Document_Type,
P.DOCNUMBR Document_Number,
P.DOCDATE Document_Date,
P.PSTGDATE GL_Posting_Date,
P.DUEDATE Due_Date,
P.TRXDSCRN [Description],
case
when P.DOCTYPE < 4 then P.DOCAMNT
else P.DOCAMNT * -1
end Document_Amount,
case
when P.DOCTYPE < 4 then P.CURTRXAM
else P.CURTRXAM * -1
end Unapplied_Amount,
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],
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],
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],
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 P.VOIDED = 0
-- add permissions for DYNGRP
GO
grant select on view_Current_Payables_Aging_Detail 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 
It looks to me like the P.CURTRXAM value for the invoices takes into account unposted cheques. Any thoughts on how to get around this? Based on our company’s internal processes, I’d like to report on the current transaction amount excluding amounts applied from the unposted cheques.
LikeLike
Hi Carlos,
You would have to add some additional code to add back the unposted applied amounts. It’s doable, but will depend on exactly how you want to do this, for example, is it only checks? What about manual payments and credit memos or returns? If you’re only looking for checks, their information will be in PM10300 and the apply details will be in PM10200 with a value of 0 in the POSTED column.
-Victoria
LikeLike
Victoria,
How difficult to add multi-currency information? Willing to contract you to create if neccessary.
Thanks!
LikeLike
Shawn,
What multicurrency information would you want to add?
-Victoria
LikeLike
If there was a ORCURTXAM (Originating Current Transaction Amount), that would be what I need. Basically the balance due in the originating currency.
LikeLike
Hi Shawn,
The code below adds the Currency ID and Originating Unapplied Amount:
selectVM.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,
case P.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
end Document_Type,
P.DOCNUMBR Document_Number,
P.DOCDATE Document_Date,
P.PSTGDATE GL_Posting_Date,
P.DUEDATE Due_Date,
P.TRXDSCRN [Description],
case
when P.DOCTYPE < 4 then P.DOCAMNT
else P.DOCAMNT * -1
end Document_Amount,
case
when P.DOCTYPE < 4 then P.CURTRXAM
else P.CURTRXAM * -1
end Unapplied_Amount,
P.CURNCYID Currency_ID,
case
when P.DOCTYPE < 4
then coalesce(m.ORCTRXAM, P.CURTRXAM)
else coalesce(m.ORCTRXAM, P.CURTRXAM) * -1
end Originating_Unapplied_Amount,
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],
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],
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],
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
left outer join MC020103 m
on p.DOCTYPE = m.DOCTYPE and p.VCHRNMBR = m.VCHRNMBR
where P.CURTRXAM <> 0 and P.VOIDED = 0
Hope that helps.
-Victoria
LikeLike
Wow, thanks perfect! Thank you!
LikeLike
Hi there,
I was running this view and notice AP Aged Trial balance is not same as this smartlist coming out of this view. it just put up amount under current bucket instead of 30-60days.
For example: Vendor ABC = Balance of $10 goes under “31-60” days as per out of box aged trial balance report. Whereas Smartlist show $10 under “Current”.
Not sure why documents are falling under different buckets. As per current setup of company on our end aging is calculated of “Document Date”
Please advice!
Thanks!
Sunaina
LikeLike
Sunaina,
This code is using Due Date, not document date for the aging. So that probably explains the discrepancy you’re seeing.
-Victoria
LikeLike
Hi Victoria,
Sorry to resurrect an old thread.
Our accounts team need to replicate the HATB as it would have been at previous month ends.
Your code works great for aging open payables transactions. But I need to somehow include paid historic transactions, as the report function does. So to replicate as exactly what the report does but to have the data in a raw tabular format.
Even if the SQL view cant pass a date, I can get past that if I have all the data
Any help would be appreciated.
Mark
LikeLike
Hi Mark,
The code for a historical version of this report is incredibly more complicated and is not something that I will be posting on my blog because of the amount of work it took to create. Also because it needs to be slightly different for each company. If you are interested in having this created as a custom report for you, please let me know.
Thanks,
-Victoria
LikeLike
Hello Victoria, We use this view that you created in order to pull the paybles info to an Excel report and it works perfectly with minor changes to fit our needs. We would like to add “Comment 1” or “Note” to this report to see some important info for the vendor with the invoices outstanding. We can not find a field name code for the Comment fields in the Vendor record. We thought we could just add a field name code to the view above similar to lines 18-21 above. Are you able to tell us if there a” VM.” field name for a comment or note field in the Vendor record? Thank you for any suggestions!
LikeLike
Hi Allison,
First step is to understand what data you’re trying to add. If you’re looking in GP – what window is the data on and where exactly is it? It sounds like you’re looking for the Note that is entered next to the Vendor ID on the Vendor Maintenance window. Is that right? Or are you looking for Comment 1 (at the bottom of the same window)? Or both? 🙂 If that’s not it, please give me some more details.
Thanks,
-Victoria
LikeLike
Thank you, Victoria, for responding! We are looking for either or both. I looked at the fields available on the header of the vendor card and could not find the actual field name for either. My A/P dept wants to be able to see a note they type in to give vendor info when we pull an Excel report from this “view”. Without knowing the field and table, I can’t add this info to the view. We would love to have the table/field name for both such as VM.VENDORID. Thank you for considering my request!
LikeLike
Hi Allison,
Comment 1 is in the VM table (PM00200), it’s called COMMENT1. So you can just add VM.COMMENT1, that should work. For the Note, you would need to link another table, you can use the following code after my line 81:
left outer join SY03900 Non VM.NOTEINDX = N.NOTEINDX
then add a line in the select section (like after line 21) with:
N.TXTFIELD,Hope that helps,
-Victoria
LikeLike
Victoria — I am sure it will! Of course I could have guessed the Comment 1 field was VM.COMMENT1 but I couldn’t find it listed in the Field Names so I thought it might not be available to export. I am looking forward to adding this as well as the note info above. You are amazing and I appreciate your work on this! The work I ordinarily do is in Management Reporter report writing. I taught myself how to use Integration Manager and I love what it can do. I would like to get more into SQL but have not been able to do so until recently. I found your blog and Belinda Allen’s blog/video on how to utilize this view and followed it completely. Now our A/P department is quite happy with their new report! Thank you so much! It is obvious your expertise is far beyond mine but I have written some pretty awesome reports in Management Reporter. Let me know if there is anything I can ever do for you!
Highest regards,
Allison
LikeLike
Thanks Allison,
I really appreciate your kind words and your offer.
Have a great weekend! 🙂
-Victoria
LikeLike
Hi Victoria,
Your SQL queries have been a true-godsend to me, I hope you know how appreciated you are! I’m not sure if this is the right place to post this…but do you have a query that shows aging as of a current date? For example, I could enter a date in the past and see what the aging looked like on that particular day. I think I know conceptually what needs to happen and have built a basic framework but I’m falling short on the execution!
As always, thanks for your insight and help –
Jess
LikeLike
Hi Jess,
Glad my SQL queries have helped! 🙂 It sounds like you are looking for a historical trial balance (HATB) report. I have created a number of different HATB reports in the past, and they usually take some effort. While the concept is somewhat straightforward, like you say, the execution, especially when combined with how the data is stored in GP, is not so straightforward. If you’re interested in having something like this created for you as a consulting project, please let me know.
-Victoria
LikeLike
Hello Victoria
Is there in view available for Historical Aged Summary Report? Can I add two columns Opening Balance and net change between Opening Balance and Current Period?
Regards,
Sanjay Parab.
LikeLike
Hi Sanjay,
It doesn’t quite work like that. For a ‘historical’ report, you need to be able to pass in a parameter, since the data returned and calculations made need to be ‘as of’ a particular date. There is no way to pass a parameter to a SQL view. So you would need to create a stored procedure to accomplish this.
-Victoria
LikeLike
Hi Victoria! This view is exactly what we need to review our open AP on a weekly basis since we are trying to set a pay schedule using the invoice due date. I have added 2 of our custom fields and also the VCHRNMBR; but wondering if it is possible to also add the GL Account Number, Account Name, and Distribution Type?
LikeLike
Hi Ana,
This is certainly possible. You would need to combine this view with my GL Distributions for AP Transactions view. This will, of course, result in multiple lines for each transaction…so it may be a little strange for the users in something like SmartList or Excel. If you need help with the code, please send me an email and we can talk about that.
-Victoria
LikeLike
Victoria,
Is there a similar script for AR?
Thanks,
Mark
LikeLike
Hi Mark,
I have this Unapplied Transactions script, but it’s not quite the same. Do you want the same format as this one for AR? If so, I can probably post it later today or tomorrow…let me know.
-Victoria
LikeLike
An AR one in the same format as this AP one would be awesome. I would be truly grateful. You’re website has saved me so much time and saved me from so many headaches over the years. Thank you!
Mark
LikeLiked by 1 person
Mark,
Thanks for the kind words. 🙂 Here is the new script: https://victoriayudin.com/2013/05/01/sql-view-for-current-receivables-aging-detail-in-dynamics-gp/. Hope that is what you were looking for.
-Victoria
LikeLike
You are awesome. Thank you!
LikeLike
Excellent post, but there is one detail, when the document type is greater than 3 can not be used the field DUEDATE because AGING BUCKET always be greater than 90 days. I’ve use DOCDATE instead.
Thank you very much for everything you do for the Dynamics Community
Rafael
Maracaibo, Venezuela
LikeLike
Rafael,
You are exactly right, thank you very much for catching that. I actually thought I had fixed that, the change must not have updated properly. It should be fixed now.
-Victoria
LikeLike
This is perfect. Thanks Victoria!
-John
LikeLike