January 25, 2012 
I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return data in summary, meaning one row per customer 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.
You can find more Receivables code here, or links to additional reporting resources on my GP Reports page.
create view view_Current_Receivables_Aging_Summary
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Receivables_Aging_Summary
-- Created Jan 25, 2012 by Victoria Yudin, Flexible Solutions Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Shows current AR aging with hard-coded aging buckets
-- Tables used:
-- CM - RM00101 - Customer Master
-- CS - RM00103 – Customer Master Summary
-- RM - RM20101 - Open Transactions
-- Updated May 1, 2013 to fix aging for credit docs
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
select
CM.CUSTNMBR Customer_ID, CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms, CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
sum(case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end) Total_Due,
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) < 31
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) < 31
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [Current],
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],
CS.LASTPYDT Last_Payment_Date,
CS.LPYMTAMT Last_Payment_Amount
from RM20101 RM
inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
inner join RM00103 CS
on RM.CUSTNMBR = CS.CUSTNMBR
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
group by CM.CUSTNMBR, CM.CUSTNAME, CM.PYMTRMID, CM.CUSTCLAS,
CM.PRCLEVEL, CS.LASTPYDT,CS.LPYMTAMT
-- add permissions for DYNGRP
GO
grant select on view_Current_Receivables_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 
Hello Victoria,
can you please share SQL scrip that can be run with as of date parameter to get same results as GP – HATB for AR
Thanks
LikeLike
Hi BJC,
I don’t give that one away for free – that’s something that I customize to each client and sell. If it’s something you’re interested in buying, please let me know and I will contact you via email.
-Victoria
LikeLike
Yes Please !
LikeLike
Hi Victoria,
Your website has been a huge help over the past few months! Question on this view: I am wanting to show unapplied transactions as well. My goal is to be able to pull this into Power BI and use DAX to create a running aging with an “As-Of” date. So i will need the Document date and Applied to Document date for applied and unapplied transactions as well. Do you have any ideas on how i could tweak this view for that?
LikeLike
Hi Tyler,
I am very happy that my blog has helped you! Always love to hear that.
This data is already including “unapplied transactions”. In fact, it’s only including unapplied transactions – my definition of that is any transaction that does not currently have a balance of $0.
From what you’re saying it sounds like what you’re really looking for is a “historical aged trial balance”. That can’t be accomplished by using this code, it would have to be infinitely more complicated based on how Dynamics GP stores the data. I can help with that, but it would have to be code that I charge for creating. If you’re interested in discussing in more detail, please let me know and I can email you directly.
Thanks,
-Victoria
LikeLike
This website is awesome – thank you. By any chance is there a way to add GL account numbers to this query? I would like to group this by GL account, then customer. Thanks again.
LikeLike
Hi Chris,
The problem with adding a GL account to this is that conceivably you could have multiple GL accounts per transaction.
I do have a different view that shows you all the GL accounts on all receivables transactions here: https://victoriayudin.com/2010/02/10/sql-view-with-all-gl-distributions-for-ar-transactions/. You could create a combination of these two to accomplish what you need.
-Victoria
LikeLiked by 1 person
Hi Victoria,
Thank you for your contributions to the GP community. We have a weird issue with HATB report in GP. For a couple of customers its showing a balance of 0.01 and -0.01 under 90 and over column. Would you happen to know what proc or tables are responsible for this data?
Best regards,
-Alex
LikeLike
Hi Alex,
It could be a combination of the following tables: RM20101, RM20201, RM30101, RM30201. Often when there is a penny showing on the report, it’s because more than 2 decimals were entered/imported for amounts.
Hope that helps,
-Victoria
LikeLike
Hi Victoria, First, I wanted to thank you for your posts. Really a BIG help. I used your script and was a big hit, it matches the Aging report from GP. That is until we did the option: Consolidate National Accounts. What happened there, could you enlighten me..
LikeLike
Hi Marion,
To match that in SQL, you would have to change the code to group on the Parent Customer and not the individual customer. However, the overall totals should still match.
-Victoria
LikeLike
Hi Victoria,
I really appreciate your site. It is a great reference. I made some modifications to your script and wanted to share them. The way your script is currently constructed, it is efficient, but excludes results for customers without activity. The following can be used to query the status of any customer:
create view [dbo].[view_Receivables_Aging_Summary]
as
select
CM.CUSTNMBR Customer_ID
, CM.CUSTNAME Customer_Name
, Rtrim(CM.PYMTRMID) Customer_Terms
, CM.CUSTCLAS Customer_Class
, CM.PRCLEVEL Price_Level
, (case when RM.Total_Due is null then 0 else RM.Total_Due end) [Total_Due]
, (case when RM.[Current] is null then 0 else RM.[Current] end) [Current]
, (case when RM.[1_to_30_Days_Late] is null then 0 else RM.[1_to_30_Days_Late] end) [1_to_30_Days_Late]
, (case when RM.[31_to_60_Days_Late] is null then 0 else RM.[31_to_60_Days_Late] end) [31_to_60_Days_Late]
, (case when RM.[61_and_Over_Late] is null then 0 else RM.[61_and_Over_Late] end) [61_and_Over_Late]
, CS.LASTPYDT Last_Payment_Date
, CS.LPYMTAMT Last_Payment_Amount
from RM00101 CM
inner join RM00103 CS
on CM.CUSTNMBR = CS.CUSTNMBR
full outer join
(select RM.CUSTNMBR
, sum(case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
when RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [Total_Due]
, sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) < 1
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) < 1
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) [1_to_30_Days_Late]
, 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_Late]
, sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 60
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 60
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [61_and_Over_late]
from RM20101 RM
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
group by RM.CUSTNMBR
) as RM
on CM.CUSTNMBR = RM.CUSTNMBR
LikeLike
HI Victoria,
I have used this code to create views in 3 different GP databases, then built my smartlist to pull form these views across all 3 database (consolidated AR Aging, if you will). Any thoughts on how I could add the database company ID to the views ?
LikeLike
Hi Michelle,
Since the views can be different in each database, you could just add a column and hardcode each company name into it. Another option would be to create a combined view, like what I show here.
-Victoria
LikeLike
Victoria,
I always refer back to your site when in need and find the information so useful. So, I was looking to see if you have any information regarding aging and multi-entity management (tables start with “ME”). I need to only pull transactions from one entity. Looking though all the tables, but haven’t been able to pin point the correct tables.
LikeLike
Hi Jonathon,
I don’t have a complete list of the MEM (multi-entity management) tables, so I am not sure what table you need for this specifically, but I do know that they all start with B39 and not ME. 🙂 You might want to ask Binary Stream Support for a list of the tables and maybe even specifically what table(s) to link to get what you are looking for – they might already have this readily available.
-Victoria
LikeLike
Well i appreciate your quick response here. When i get it figured out, I will share my findings with you. Thanks again!!
LikeLike
So with your help (pointing out “B39” tables), I was able to create the statement. Thought that you and/or your viewers would be interested with the details. In your posted code add the following join to line 67:
inner join dbo.B3950001 en on en.docnumbr = rm.docnumbr
–add to the where statement
and en.bssi_facility_id = [enter your entityid]
LikeLike
Jonathon,
Great – thanks!
-Victoria
LikeLike
Ok.. so now trying to run the balance “As of” a specific date and retrieving odd results. Do you have any suggestions?
LikeLike
Hi Jonathon,
For ‘as of’ a specific date you need a historical aged trial balance report. Infinitely more complicated than a current aging report and not something I will be sharing on the blog because of the effort it takes to create it. This is something I can offer as a consulting project for you if you are interested. Please let me know.
-Victoria
LikeLike
Kind of the response I expected. Why should i ever think things are easy? 🙂
Thanks again for the quick response. If this is required, I will let you know.
LikeLike
Hi Victoria, I know that this SQL is pulling current AR for the date and time that it is run. However, when I run a historical trial balance for the same date, my aging buckets are different though the total amount is correct. Is there something that I am missing?
LikeLike
Hi Herb,
Your AR aging buckets may be set up differently from what this code is doing. You can see the settings on the Receivables Management Setup window (Microsoft Dynamics GP | Tools | Setup | Sales | Receivables).
-Victoria
LikeLike
Hi Victoria, It’s posible display the Total_Due per month? I mean, Jan_total_due, Feb_total_due, ect…, by the way I love your blog.
LikeLike
Hi Julio,
Per month of what? Invoice Date, Due Date, something else? 🙂
-Victoria
LikeLike
Thanks for your fast reply, I’m looking for the Total Due amount at the end of a previous month using the Due Date. for example
SUM(CASE WHEN month(RM.DUEDATE) = 1 AND RM.RMDTYPAL 6 THEN RM.CURTRXAM * – 1 ELSE 0 END) AS [Jan Total Due]
this is your code:
sum(casewhen RM.RMDTYPAL < 7 then RM.CURTRXAMelse RM.CURTRXAM * -1end) Total_Due
Regards,
LikeLike
Ups!!! sorry for the large letters, I don’t know what happen.
LikeLike
Julio, no problem, I fixed it.
Sorry, I am still not 100% with you. Do you mean you want the aging as of a particular date? (So not the current aging, but the historical aging?) Or do you just want to sum in buckets based on the month of the due date?
-Victoria
LikeLike
Victoria,
Your website has saved me SO much time and effort – thank you so much for putting this information out there for us.
Is there a way to have this same data format for Detailed Aging? Basically, I am being asked to create the Receivables Historical Aged Trial Balance in Smartlist.
Thank you,
Jeff
LikeLike
Hi Jeff,
I am very glad that my blog has helped you! I have a variation on this view that lists all open receivables transactions. You could use this view in a SmartList, however, just to make sure we’re on the same page…this is not a ‘historical’ aging – this is current.
If you need to recreate the historical aging, you’re looking at a lot more work and you may not be able to do this in a SmartList, because SmartList does not have a way to enter a ‘parameter’ which is what you need to let the user specify the historical aging date. This blog post by Mark Polino lists some available resources for the historical aging that you might find useful.
-Victoria
LikeLike
Hello,
Thanks for this. Will this give me the option to have a subtotal at the end of each customer? Would I not need to do some sort of union in order to get the sub-totals / totals at the end of the details for each customer?
Regards,
LikeLike
Jay,
This returns one line per customer with the totals for each aging bucket. If that’s not what you’re looking for, can you please elaborate on what you’re looking to do?
-Victoria
LikeLike
Hi Victoria,
Can we have a view that gets the aging buckets with the unposted SOP invoices.
Thanks,
LikeLike
Hi Ray,
Not sure what you mean – you want this view to also include unposted invoices from SOP? Or something else?
-Victoria
LikeLike
HI Victoria,
Yes this is what I mean, I want to have detail trail balance for receivables including the unposted invoices from SOP.
LikeLike
Hi Ray,
Gotcha. That is certainly something that can be done, and I’ve done a variation on that in the past for a customer statement. However, I would not want to change this view, as most people would not want to see unposted transactions as part of their aging. I will add it to my list of requests for the blog, but no promises. If you need this right away, this is something that we can create for you as a consulting project, let me know.
-Victoria
LikeLike
I used the sql code and it worked great! Thanks so much for sharing it. The one question that I have is how I could create parameters that would allow users to view only those customers who are past due. I was thinking of creating a temp table that would hold the different buckets and use that for the available values that would be used in the parameter. Do you have any thoughts?
Thanks,
Jon
LikeLike
Jon,
I am not sure what you mean, do you want to give the user a list of ‘buckets’ to pick from as a parameter? If so, since the buckets are hardcoded in my example, and there are only a few of them, why not just hardcode the values?
If I misunderstood, can you give me an example? Also, what reporting tool are you going to be using for this?
-Victoria
LikeLike
Hi Victoria,
I’m working to a report that will give the un-billed and billed amount of a customer. But as of got the information I gather did not match on what GP report return. Hope you can give us information what possible tables and relation to each other.
Thank you..
jhunn
LikeLike
Hi Jhunn,
Unbilled transactions can be in a lot of different places. Where are you entering them? (What window in GP?)
-Victoria
LikeLike
Hi Victoria,
All in sales module, most RM table was here. but when i try to check on Customer Payment Summary the amount did not match on my report.
I don’t know if i missed some table of there’s a problem with the data i got.
LikeLike
Hi Jhunn,
The Sales series has 3 modules: Sales Order Processing (SOP), Receivables Managements (RM) and Invoicing (IVC). The Invoicing module is not used much by anyone, but still important to keep in mind just in case you are using it. Most companies use the SOP module to enter most of their sales transactions, with only some transactions entered in the RM module. SOP transactions only show up in the RM module once they are posted. Until then, they are only seen in the SOP tables. However, the Customer Payment Summary window only looks at the RM tables.
So…all that to say that if you are using the SOP module, then I would not recommend looking at the Unposted Sales on the Customer Payment Summary window because they will not be showing you the entire picture.
-Victoria
LikeLike
Hi Victoria,
Thank you for the information. do you had an email so i can forward the script i created.
Thank you
Best Regards,
Jhunn
LikeLike
Jhunn,
You can post the script into the comments here if you would like.
-Victoria
LikeLike
Hello Victoria,
I found your post with the help of the MS Dynamics 2010 GP web site forums. I was wondering if the SQL view you defined here is the typical method for obtaining AR aging data from GP for reporting purposes.
I am new to GP. My company has a home built application that uses some of the data from the GP database, and in this case we have a report that was built from query that joins directly to some of the tables that are used in your view definition. Is this the normal method for obtaining AR aging data from GP for reporting purposes? I was told by an IT manager in my company that we should be using either eConnect, or some other GP web service to obtain the aging data from GP, instead of joining directly to its db tables. The direct join is clearly more efficient that retrieving these data through something like eConnect, but I think the assumption was that eConnect is a more secured, and therefore a better approach than a direct query. Can AR aging data be obtained through eConnect or some other method, and if so, what method do you recommend?
Any help is appreciated.
Thanks,
Harold
LikeLike
Hi Harold,
I am not sure there is one answer for this, as a lot of times the best solution will depend on many other factors. I would recommend posting your question on the GP Community Forum to get some opinions from the GP community.
-Victoria
LikeLike
Hi Victoria,
Thank you for the reply, and your suggestion. I do have one more question – is it possible to obtain the aging data stored in tables RM00101, RM20101, and RM30101 – using eConnect? We will likely want to retrieve all of the data in these tables for now, as our original report joined to those tables without a where clause. It may be that out application performs the filtering of results from the report query.
Thanks again
LikeLike
Hi Harold,
I do not work with eConnect myself, so I am not the best source of answers on any eConnect questions, sorry.
However, I will say that if you are looking for current aging, there is no reason to need RM30101 – as only transactions that are fully paid will be in that table.
-Victoria
LikeLike
Hi Victoria,
Is there anyway to have similar aging but in originating currency?
Thanks for all your help!
LikeLike
Hi Hishma,
You would need to add information from the MC020102 table for the originating currency details.
-Victoria
LikeLike
Thanks Victoria!
LikeLike
What would it take to get this to show originating currency?
LikeLike
David,
You would need to add information from the MC020102 table for the originating currency details.
-Victoria
LikeLike
Victoria, thanks for the quick reply. Since we deployed GP a couple months ago, I am here all the time! I showed the results to the controller and she actually requires an historical ATB, so “aged as of”. What tables are involved in that?
LikeLike
Hi David,
Siva Venkataraman has a number of scripts published for the historical aged trial balance (HATB), here is one: http://msdynamicstips.com/2011/07/21/receivables-hatb-with-aging-by-due-date-using-gl-posting-date/. You should be able to find links to others from there.
-Victoria
LikeLike
Dear Victoria
I am printing the RM Historical Aged Trial Balance Summary. So when I give age as of 31/12/2011, I get a figure and if I give the age of for eg., 31/12/2015, I get a lesser figure. So, as I am a regular visitor of your site, I copied this script and exported excel. This give me another figure. What can be the problem. Mainly my issue is the difference happening in the report when the age as of date is different. I run the aging as on 29/02/2012. But still the figures differes. I am using GP 10 SP 3.
Thanks in advance.
LikeLike
Hi Ktarahman,
When you run a historical aged trial balance (HATB) as of a particular date the report you get is only as of that date. So on Dec. 31, 2011 the report would show that your customers owed you X at that time. Transactions posted in January of 2012 would make the same report on Jan. 31, 2012 have a different number…it would be X less whatever your customers have paid in January plus whatever new invoices you posted in January.
The view in this post is not restricting by any date, so it will give you every open receivables transaction in the system, whether it’s in the past or the future. I would suspect that if you run the HATB with an aging date of 12/31/2999 (yes, 2999, that’s not a typo) you will get the same results as this view. If not, there may be some data cleanup needed or you may have transactions with dates greater than 12/31/2999.
-Victoria
LikeLike
Dear Victoria
I really appreciate your such fast reply and this is not the first time. I have come to this assumption by this time. But now it’s confirmed. Thanks again.
LikeLike
How do I set a aging date? I want a query that will give me the balances at each month-end. Thanks.
LikeLike
RR,
It sounds like you’re looking for a historical trial balance – that’s much more difficult and often requires custom coding. We can do this as a consulting project if you are interested.
-Victoria
LikeLike
Victoria,
I didn’t reblog your script, but had no problem copying it with the braces and it runs fine. I have made a modication to your script which you may be interested in. Basically I changed a couple of the selections to match the AGPERAMT buckets in GP – where AGPERAMT1 is Current and AGPERAMT2 is 1-30 Days.
Example:
LikeLike
Victoria, Thanks for the reply and update. I did use the copy functionality to copy the script and the code failed without the brackets for the column name. I am on SQL Server 2008 R2. 🙂
Regarding the aging buckets, what you say makes perfect sense. :).
Thanks for the good script.
LikeLike
Hi Siva,
I just looked at the reblog of my post on your blog and I see that all the brackets around the column names are stripped out. 😦 As I mentioned before, WordPress has some special functionality for posting code…perhaps that is causing this not to be picked up properly when it is reblogged? Is it possible to create a link back to my blog for the code instead of reblogging the whole post? Maybe that would avoid a lot of issues. I see this is not isolated, for example, you will have the same problem with this one.
I just tested copying the code directly from my blog using both Google Chrome and IE 9.0 with SQL 2008 R2 and am not seeing any issue with the brackets.
-Victoria
LikeLike
Victoria, I will update both the scripts in my blog with the braces. Thanks for your reply. Appreciated.
LikeLike
Victoria, My two cents to this.
I executed this query and got the following error.
“Msg 102, Level 15, State 1, Procedure view_Current_Receivables_Aging_Summary, Line 35
Incorrect syntax near ’31’.”.
It looks like you will need to enclose the alias names within brackets (i.e.) like [91_and_Over] instead of just 91_and_Over, since the column name cannot begin with a number or special character. It has to be an alphabet.
Also, you have determined the aging bucket by calculating the date difference between the current date and the due date. Instead you can make use of the aging bucket column in RM20101 (AGNGBUKT) which can be joined with the RM40201 table to display the aging bucket for the specific record. This way, we can make use of the standard aging options in GP (either by doc date or due date) and once we run the aging process, we would be able to run the query to get the same result as the GP Current Open TB. 🙂
LikeLike
Hi Siva, thanks for your comments.
For the error – I do have brackets around the column names and am not able to duplicate what you’re seeing. 😦 Are you looking at the code on my blog directly, or somewhere else? Also, how are you copying the code? I have recently started using special functionality available on WordPress for posting code to try to avoid any issues with special characters. So when looking at it on my blog, there are 4 little gadgets at the top right of the code section – if you click the second one, that will copy all the code properly to your clipboard. Can you please let me know if that works for you? I have seen issues in the past with various browsers (cough, typically IE) stripping out some of the formatting, so I just want to make sure this is not a recurring issue for you and others. I also just noticed some extra spacing in the code…not sure where that came from, but I took it out…maybe that will help, as well.
For your other comment – I actually hard coded the aging buckets on purpose. I have found that often when I get asked for this type of report, the company asking either (a) wants something slightly different from what their aging setup in GP is or (b) wants to not have to run the aging routine in GP to move the data to the correct buckets prior to running the report. So I wanted to provide an easy example on how to set up your own aging buckets – this way, if someone wants to create 10 aging buckets or just 2, they know how to code it. 🙂 Hope that makes sense.
-Victoria
LikeLike