May 1, 2013 
Another reader request brings us this new script. Below is a view for current Receivables aging in detail. This code is only looking at functional currency and will return one row per open (unpaid) receivables transaction. I am hard-coding the aging using the following buckets and aging by due date:
-
Current
- 0 to 30 Days
-
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:
- Receivables SQL views
- Receivables module commonly used tables
- Other Dynamics GP reporting links
create view view_Current_Receivables_Aging_Detail
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Receivables_Aging_Detail
-- Created May 1, 2012 by Victoria Yudin, Flexible Solutions Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Shows current AR aging in detail w/ hard-coded aging buckets
-- Tables used:
-- CM - RM00101 - Customer Master
-- RM - RM20101 - Open Transactions
-- Updated Aug 12, 2013 to add 0 to 30 days bucket
-- Updated Sep 20, 2013 to add Last Payment Date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
select
CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms,
CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
case RM.RMDTYPAL
when 1 then 'Sale / Invoice'
when 3 then 'Debit Memo'
when 4 then 'Finance Charge'
when 5 then 'Service Repair'
when 6 then 'Warranty'
when 7 then 'Credit Memo'
when 8 then 'Return'
when 9 then 'Payment'
else 'Other'
end Document_Type,
RM.DOCNUMBR Document_Number,
RM.DOCDATE Document_Date,
RM.DUEDATE Due_Date,
S.LASTPYDT Last_Payment_Date,
case
when RM.RMDTYPAL < 7 then RM.ORTRXAMT
else RM.ORTRXAMT * -1
end Document_Amount,
case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end Unapplied_Amount,
case
when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [Current],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 30
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [0_to_30_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [31_to_60_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [61_to_90_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [91_and_Over]
from RM20101 RM
inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
left outer join RM00103 S
on RM.CUSTNMBR = S.CUSTNMBR
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
-- add permissions for DYNGRP
GO
grant select on view_Current_Receivables_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 
Hi Victoria – I have used this script often and it is brilliant, thank you. The one item a client is asking for is the exchange rate to be added. When I add this in GP’s Smartlist Designer (from the YTD Transaction Open Table) I get duplicate records. Is there an altered script for the view_Current_Receivables_Aging_Detail you could send me please? Thanks so much!
LikeLike
Hi Lisa,
Why not add the exchange rate from the MC020102 (Multicurrency Receivables Transactions) table? I believe that should only have one entry per RM transaction and thus you can avoid the duplicate records. Just trying to understand if there is some reason you need these from the GL instead?
-Victoria
LikeLike
Brilliant again, done. Thanks so much! And thanks for all you do!
LikeLike
Hi Victoria,
I’m fairly new to GP, and this has been a huge help for me, so thanks for sharing your knowledge and resources. I have two questions for you regarding this report.
First, when I run my report, I have a zero dollar value is every record in the Current column. From what I can tell there are only two possible explanations: None of the records have data that falls into that aging bucket or my code has an error in it. Have you seen this issue before, and if so, what do you think the solution could be?
Second, I am having trouble getting the totals of this report to match up with my Historical Aged Trial Balance. There is a significant difference between the totals. I’m guessing that a good portion of that difference would be sorted out if I could find the values that are not showing up in the Current column, but it doesn’t match up completely. Additionally, I’ve found that invoices and payments are not falling into the same aging buckets in the Trial Balance as they are in the Current Aging Detail.
Thanks in advance for any help you can offer!
LikeLike
Hi Nathan,
The current column simply shows transactions that are not due yet, based on the due date. If your invoices are all due upon receipt, then with this code they will show up in the 0-30 day column right away.
For your other questions – this is the CURRENT receivables, which is often very different from the HISTORICAL trial balance, so you really cannot compare the two. For the aging buckets being different for the same transactions, that’s likely because I am hard-coding the logic for the buckets and it is different from what your GP is set up to do for the buckets. If you’re looking to get the historical aged trial balance in Excel or directly in SQL, I can create a custom report for you, let me know if you’re interested.
-Victoria
LikeLike
Sorry, I don’t think I explained my question very well. My two reports are a Current Aging Past Due Detail (CAPDD) and a Historical Aged Trial Balance (HATB). In CAPDD, I’ve got several different customer terms for the invoices (net 30, net 120, COD, etc.), so while I don’t think there should be much data in the Current column, I keep thinking “surely there should be at least one record out of several hundred here.” Concerning the HATB, I am confused as to why the total here should not match the CAPDD. Most of the data is the same, with the exception being that the total in HATB are much higher, hence my initial question.
Sorry for the confusion, I appreciate the help!
LikeLike
Hi Nathan,
From the name of the report (PAST DUE DETAIL) I am guessing it’s ONLY pulling in what is past due and thus there is nothing “current”. This is just a guess based on the name as well as the results you are describing. Without looking at your code and your data, I cannot say for sure.
Similarly, if the CAPDD report is limited to only past due transactions, then it would not include everything due and would thus not match the HATB report.
-Victoria
LikeLike
That makes perfect sense. Thanks for the clarification!
LikeLike
After running your view it returns different amount for the current period compare to what is being returned by customer summary master table RM00103 – such that field ‘AGPERAMT_1’. I believe this field is meant to bring outstanding amount for current period 0-30 days.
LikeLike
Rakesh,
This is expected. The RM00103 table only get updated when you run the Aging Routine inside GP and the calculations use the settings on your Receivables Management Setup window. My code has hard-coded calculations for the aging buckets – this is on purpose so that you can have as many aging buckets as you want and use whatever logic you want for them.
-Victoria
LikeLike
Hi Victoria,
Thank you so much for your response. It helps.
Regards,
Rakesh
LikeLike
Hi Victoria,
I don’t know if you’re still checking these comments, but I’m hoping so.
First of all, thank you so much for your website. It has been a tremendous help.
Secondly, is there a way of using the aging buckets set up each month in GP under Sales, Setup, Receivables? I have been using this code to set up the aging, but with the way we change our buckets I would have to either go in and manually change the buckets each month and re-run, or set up a separate query for each month. I found other queries online that pull from the setup, but I am having issues combining them with this query.
LikeLike
Hi Heidi,
There is a way to do it, sure. I actually did not do it on purpose because most people do not want these to mimic the GP buckets and want to add more buckets or different ones.
May I ask why you are changing these in GP all the time? That’s pretty unusual in my experience.
-Victoria
LikeLike
Thank you so much for your response. We bill monthly, always dated the last day of the month. We do our aging based on the doc date, and then the first bucket is based on the most current month of invoices.
For example what we use for the aging as of February 28, and then all the way until the new invoices are processed on March 31 is as follows:
Current: 0-27 (for February dated invoices)
31-61: 28-58 (for January dated invoices)
62-92: 59-89 (for December dated invoices)
93 and over: 90-999 (for anything dated November and older)
Then as of March 31 until the new invoices are processed for April 30:
Current: 0-30 (for March dated invoices)
31-61: 31-58 (for February dated invoices)
62-92: 59-89 (for January dated invoices)
93 and over: 90-999 (for anything dated December and older
My work around was going to be to create this view for each month, which would work all the time except in a leap year, but I thought maybe if there was a way to pull directly from the periods in GP, it would be easier. We update those buckets in GP monthly.
I’d love to hear your thoughts.
LikeLike
Hi Heidi,
Thanks for the explanation, that makes sense. Certainly one way you could do it would be to update the buckets in GP each month and use those in the code, like you’re suggesting. Another would be to simply code in the buckets based on the invoice dates. (And call the buckets something like Last Month, 2 Months, 3 Months, and Older.) That should make it dynamic and you would not have to change the buckets in GP (unless you need that for other reasons) and to also only have one piece of code, it would even work for leap years.
If you need help with coding that, it is something I can do as a consulting project, let me know.
-Victoria
LikeLike
Thank you! I will see if I can figure it out. If not, I might contact you for a quote about this and a few other items we’re looking for related to this project.
LikeLike
I needed to have a backdated aging so I am trying it as a table valued function as below:
create Function [dbo].[TRM_Receivables_Aging_Detail_With_Offset](@DateOffset INTEGER)
RETURNS Table
as
— ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
— Shows current AR aging in detail w/ hard-coded aging buckets
— Allows for a date offset parameter
— Tables used:
— CM – RM00101 – Customer Master
— RM – RM20101 – Open Transactions
— Created Dec 14, 2016 based on a view from Victoria Yudin
— ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
RETURN(
select
CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms,
CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
case RM.RMDTYPAL
when 1 then ‘Sale / Invoice’
when 3 then ‘Debit Memo’
when 4 then ‘Finance Charge’
when 5 then ‘Service Repair’
when 6 then ‘Warranty’
when 7 then ‘Credit Memo’
when 8 then ‘Return’
when 9 then ‘Payment’
else ‘Other’
end Document_Type,
RM.DOCNUMBR Document_Number,
RM.DOCDATE Document_Date,
RM.DUEDATE Due_Date,
S.LASTPYDT Last_Payment_Date,
case
when RM.RMDTYPAL < 7 then RM.ORTRXAMT
else RM.ORTRXAMT * -1
end Document_Amount,
case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end Unapplied_Amount,
case
when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) <= 0
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) <= 0
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [Current],
case
when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 1 and 30
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 1 and 30
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [0_to_30_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 31 and 60
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 31 and 60
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [31_to_60_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 61 and 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 61 and 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [61_to_90_Days],
case
when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) > 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) > 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [91_and_Over]
from RM20101 RM
inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
left outer join RM00103 S
on RM.CUSTNMBR = S.CUSTNMBR
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
)
LikeLike
Hi Thomas,
This will not work. The historical trial balance, which is what you’re looking for, is MUCH more complicated than this and needs to include a lot of other tables, as well.
-Victoria
LikeLike
What table will i stores the historical trial balance?
LikeLike
There is no such table. This data needs to be calculated, it’s not stored.
-Victoria
LikeLike
I’m getting the following error :Incorrect syntax near ‘GO’.
could you please help.
LikeLike
Eugene,
I just copied all the code into a new query in SQL and ran it and didn’t get any errors. Did you make any changes to the code I posted? If not, can you please copy the last 5 lines of your code in here so I can take a look?
-Victoria
LikeLike
When attempting to add the Parent ID to this script I keep getting the Customer Number. How can I get it so that I will get the correct Parent information.
The column that is showing properly is CPRCSTNM
Kevin
LikeLike
Kevin,
What is probably happening is that you’re seeing open transactions at the parent level – for those, the Customer ID and Parent ID will be the same. For example, I added the following after line 18:
CM.CPRCSTNM Parent_ID,
and the results look like this:
The PLAZAONE0001 National Account has WORLDENT0001 as a child, but the parent customer itself also has open transactions.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
Thanks for getting back to me on the Child Parent issue.
For the Document types that are Credit Memo & Payments would you know of a way to reflect these amounts as negative in the results?
Thanks for your assistance.
Kevin
LikeLike
Hi Kevin,
My code is already showing payments, credit memos and returns as negative numbers. Are you not seeing that in your results?
-Victoria
LikeLike
Hi Victoria
I want to update the AR w/Options report that shows the Net Amount due for Open Invoices. For Example.. There is an Invoice for 1000.00 the Cash Receipt applied to this invoice is 500.00 so on the AR Aging w/options it shows the Invoice Number and amount and then the cash receipt amount underneath the invoice. We would like to have it show as net amount due like below.
Date Invoice # Net amount due
on Invoice
9/30/16 123 500.00
Can you give me any ideas how to update the exiting AR w/options or will I need to create one and what tool would you use?
Thank you!
LikeLike
Hi Tammy,
I cannot offer any help with Report Writer reports, sorry. Whenever possible, I use other reporting tools so as to avoid Report Writer as much as possible. I post all of this code on my blog to help others do the same. 🙂
-Victoria
LikeLike
Thank you so much for posting these views, very helpful,
Would it be possible to update using Doc Date vs Due Date for Aging columns?
LikeLike
Hi Michael,
You can change RM.DUEDATE to RM.DOCDATE on lines 46, 53, 60, 67 and 74 to accomplish this.
-Victoria
LikeLike
Hi Victoria,
We are using the above view to create an AR aging report. But the due date is not showing up correctly in the report. It is showing same as the Document Date. The due date should be calculated based on the payment terms right ? It is not being calculated correctly for most of the records. Could you please let me know how I can get the correct due date
Many thanks,
LikeLike
Hi Usha,
This view does not actually calculate due dates. It uses the due date stored in your Dynamics GP for each transaction. For Credit Memos, Returns and Payments, there is no ‘Due Date’ in GP, so the Document Date is used.
Hope that helps clarify.
-Victoria
LikeLike
Looking to have this as a summary, the pulling open orders into this as well, from the SOP10100 file. I cannot seem to get it to sum correctly, even before I tackle the SOP10100.
Any suggestions?
Here’s what I have:
select
CM.CUSTNMBR Customer_ID,
CM.CPRCSTNM Corp_Account,
sum(case
when RM.RMDTYPAL < 7 then RM.ORTRXAMT
else RM.ORTRXAMT * -1
end) Document_Amount,
sum(case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end) Unapplied_Amount,
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0 and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0 and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [Current],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30 and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 30 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [0_to_30_Days],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60 and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [31_to_60_Days],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90 and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [61_to_90_Days],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 90 and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 90 and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [91_and_Over]
from RM20101 RM inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
left outer join RM00103 S
on RM.CUSTNMBR = S.CUSTNMBR where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
GROUP by RM.CUSTNMBR
LikeLike
Chuck,
Try changing the last line to:
GROUP by CM.CUSTNMBR, CM.CPRCSTNM
-Victoria
LikeLike
Hi Victoria,
Love these! Is there any way to specify a specific date as opposed using todays dates?
LikeLike
Hi Adam,
Thanks for your kind words! Sounds like you are looking for a ‘historical’ aged trial balance instead of a ‘current’ one. 🙂 That requires much more complicated code and typically a SQL stored procedure, not a view. It’s certainly doable, but not something I will be sharing on my blog in the foreseeable future. If you are interested in having this created for you as a consulting project, please let me know.
-Victoria
LikeLike
Thanks for the information, this is awesome. I am trying to match some of this information to how our aging report pulls. Is their a way to pull the sql that the GP reports use?
LikeLike
Hi Michael,
I’ve not done this, but you could run SQL Profiler to see what GP is calling when you run the report.
-Victoria
LikeLike
Hi Victoria
I am grateful every week for your SQL views – save me SO much time!
I have use the view you have for Payables for Multicompany Open Invoices – do you have anything for AR/SOP -showing Open Invoices for Receivables for multiple companies?
Many thanks
Sarah
LikeLike
Hi Sarah,
Thanks for your kind words!
I don’t have anything similar for sales yet, but that is a good idea and I will put it on the list of requests. If you need something sooner and need help with it, let me know, perhaps we can do this for you as a consulting project.
-Victoria
LikeLike
Thanks so much!
LikeLike
Hi Victoria,
Is it possible to Have the Last Payment Date Included? Our company is always looking for the Last Payment received by a customer.
LikeLike
Hi Larry,
I just updated the code to include the Last Payment Date.
-Victoria
LikeLike
Is there a way to add a state field in this report? I need to be able to see my open AR by state.
Thanks!! 🙂
LikeLike
Delena,
State from where? The main address for the customer? Something else?
-Victoria
LikeLike
I guess the field is Item Class Code as I’m able to run Sales by State using that field.
LikeLike
This might be something that has to be created specific to your company, as the Item Class typically has no direct tie to Receivables. First, if there are unapplied payments in your receivables, they will have no items associated. You could also have one invoice with 20 items, each in a different class. Since this is showing one line per receivables transaction, you cannot tie it to items. And I have not even mentioned partial apply complications. 🙂
-Victoria
LikeLike
Ugh. Thanks for taking the time to answer my question!
LikeLike
Victoria… do you offer that as a service? Creating reports specific to a company? Or know anyone who does?
Thanks!
LikeLike
Hi Delena,
Yes, my company offers this service. I will send you an email with my contact information.
-Victoria
LikeLike
I need a historical aging in smartlist or excel, is this something you could do?
LikeLike
Hi Beth,
Historical aging is not something you can do in SmartList, as there is no way to pass the aging date to the code in a SmartList. I can create a historical aging in Excel as a consulting project. If you are interested, please let me know and I will contact you via email.
-Victoria
LikeLike
Thank you Victoria…this is MOST helpful!
LikeLike