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 http://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,
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
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
Hi Victoria,
Can we have a view that gets the aging buckets with the unposted SOP invoices.
Thanks,
Hi Ray,
Not sure what you mean – you want this view to also include unposted invoices from SOP? Or something else?
-Victoria
HI Victoria,
Yes this is what I mean, I want to have detail trail balance for receivables including the unposted invoices from SOP.
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
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
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
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
Hi Jhunn,
Unbilled transactions can be in a lot of different places. Where are you entering them? (What window in GP?)
-Victoria
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.
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
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
Jhunn,
You can post the script into the comments here if you would like.
-Victoria
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
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
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
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
Hi Victoria,
Is there anyway to have similar aging but in originating currency?
Thanks for all your help!
Hi Hishma,
You would need to add information from the MC020102 table for the originating currency details.
-Victoria
Thanks Victoria!
What would it take to get this to show originating currency?
David,
You would need to add information from the MC020102 table for the originating currency details.
-Victoria
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?
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
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.
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
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.
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:
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.
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
Victoria, I will update both the scripts in my blog with the braces. Thanks for your reply. Appreciated.
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.
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