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