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.


Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code Tagged: featured, GP Reports code, GP SQL view, Receivables, SQL code