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
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
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.DUEDATE, 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.DUEDATE, 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.DUEDATE, 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.DUEDATE, 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.
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
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