Today’s script is an unusual one for me to post, as it is not a script which I have written; it is a script which returns the data for a Receivables HATB With Aging By Due Date (using Document Date). It was written by Sivakumar Venkataraman back in 2011 and came up in conversation recently; the site he posted it to no longer exists and I had to hunt the script out from the wayback machine to retrieve a copy.
The script is below is Siva’s script which I am reposting “as is” as it may be useful to others.
/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging by due date and
picking transactions using the Document Date.
Tables used:
- RM Open File Debit Copy (RM20101)
- RM History File (RM30101)
- RM Apply Open File (RM20201)
- RM Apply History File (RM30201)
- Document Type Setup File (RM40401)
- RM Period Setup (RM40201)
Revision History
No Date Reason for Change
1 12/12/2017 Updated the logic for due date for credits
******************************************************************/
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
SELECT W.CUSTNMBR AS CUSTOMERNO ,
W1.DOCDESCR AS DOCTYPE ,
W.DOCNUMBR ,
W.DOCDATE ,
W.TRXSORCE ,
W.GLPOSTDT AS POSTINGDATE ,
W.DUEDATE ,
W.AGINGBUCKET ,
W.DOCUMENTAMT ,
W.CURTRXAMT
FROM ( SELECT X.CUSTNMBR ,
X.RMDTYPAL ,
X.DOCNUMBR ,
X.DOCDATE ,
X.TRXSORCE ,
X.VOIDED ,
X.GLPOSTDT ,
X.DUEDATE ,
X.DAYSDUE ,
CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201
ORDER BY RMPEREND DESC
)
WHEN X.DAYSDUE < 0 THEN 'Not Due'
ELSE ISNULL(( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201 AG
WHERE X.DAYSDUE <= AG.RMPEREND
ORDER BY RMPEREND
), '')
END AS AGINGBUCKET ,
X.VOIDPDATE ,
X.DOCUMENTAMT ,
X.APPLIEDAMT ,
X.WRITEOFFAMT ,
X.DISCTAKENAMT ,
X.REALGAINLOSSAMT ,
CASE WHEN X.RMDTYPAL < 6
THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
END AS CURTRXAMT
FROM ( SELECT Z.CUSTNMBR ,
Z.RMDTYPAL ,
Z.DOCDATE ,
Z.DOCNUMBR ,
Z.ORTRXAMT AS DOCUMENTAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
WHEN RMDTYPAL > 6
AND RMDTYPAL <= 9 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
DATE1 ,
APPTOAMT AS APPLDAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
DATE1 ,
APPTOAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APTODCDT <= @ASOFDATE
AND Y.APTODCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS APPLIEDAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS WRITEOFFAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS DISCTAKENAMT ,
CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
FROM ( SELECT CUSTNMBR ,
DATE1 ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT ,
RLGANLOS
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
DATE1 ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT ,
RLGANLOS
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APTODCDT <= @ASOFDATE
AND Y.APTODCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS REALGAINLOSSAMT ,
Z.TRXSORCE ,
Z.VOIDED ,
Z.GLPOSTDT ,
Z.DUEDATE ,
DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
Z.VOIDPDATE
FROM ( SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM20101
UNION
SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM30101
) Z
WHERE Z.DOCDATE <= @ASOFDATE
AND Z.VOIDED = 0
AND Z.RMDTYPAL <> 6
) X
UNION ALL
SELECT X.CUSTNMBR ,
X.RMDTYPAL ,
X.DOCNUMBR ,
X.DOCDATE ,
X.TRXSORCE ,
X.VOIDED ,
X.PSTGDATE ,
X.DUEDATE ,
X.DAYSDUE ,
CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201
ORDER BY RMPEREND DESC
)
WHEN X.DAYSDUE < 0 THEN 'Not Due'
ELSE ISNULL(( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201 AG
WHERE X.DAYSDUE <= AG.RMPEREND
ORDER BY RMPEREND
), '')
END AS AGINGBUCKET ,
X.VOIDPDATE ,
X.DOCUMENTAMT ,
X.APPLIEDAMT ,
X.WRITEOFFAMT ,
X.DISCTAKENAMT ,
X.REALGAINLOSSAMT ,
CASE WHEN X.RMDTYPAL < 6
THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
END AS CURTRXAMT
FROM ( SELECT Z.CUSTNMBR ,
Z.RMDTYPAL ,
Z.DOCDATE ,
Z.DOCNUMBR ,
Z.ORTRXAMT AS DOCUMENTAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
WHEN RMDTYPAL > 6
AND RMDTYPAL <= 9 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
DATE1 ,
APPTOAMT AS APPLDAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
DATE1 ,
APPTOAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APTODCDT <= @ASOFDATE
AND Y.APTODCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS APPLIEDAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS WRITEOFFAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
FROM ( SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
APFRDCDT ,
DATE1 ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APFRDCDT <= @ASOFDATE
AND Y.APFRDCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS DISCTAKENAMT ,
CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
FROM ( SELECT CUSTNMBR ,
DATE1 ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT ,
RLGANLOS
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
DATE1 ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
APTODCDT ,
RLGANLOS
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.DATE1 <= @ASOFDATE
AND Y.APTODCDT <= @ASOFDATE
AND Y.APTODCDT <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS REALGAINLOSSAMT ,
Z.TRXSORCE ,
Z.VOIDED ,
Z.PSTGDATE ,
Z.DUEDATE ,
DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
Z.VOIDPDATE
FROM ( SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT AS PSTGDATE ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM20101
UNION
SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM30101
) Z
WHERE Z.DOCDATE <= @ASOFDATE
AND Z.VOIDED = 1
AND Z.RMDTYPAL <> 6
AND Z.VOIDPDATE > @ASOFDATE
) X
) W
INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE W.CURTRXAMT <> 0
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.