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.
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