Today, I have decided to post a script for the Receivables Historical Aged Trial Balance (HATB), which is generated by picking transactions based on the GL posting date for the receivables transactions.
This script can also be used as a base to reconcile the sub-ledger balances as of a certain date with the corresponding GL transactions. The script for generating general ledger balances is available here.
DECLARE @ASOFDATE DATETIME
SET @ASOFDATE = '2017-04-12'
SELECT X.CUSTOMERNUMBER,
X.CORPORATECUSTNUMBER,
X.DOCUMENTNO,
X.DOCUMENTTYPE,
X.DOCUMENTDATE,
X.GLPOSTINGDATE,
X.DOCUMENTAMT,
X.APPLIEDAMT,
X.WRITEOFFAMT,
X.DISCTAKENAMT,
X.REALGAINLOSSAMT,
( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT
+ X.REALGAINLOSSAMT ) AS CURRENTAMTFROM ( SELECT Z.CUSTNMBR AS CUSTOMERNUMBER,
Z.CPRCSTNM AS CORPORATECUSTNUMBER,
Z.DOCNUMBR AS DOCUMENTNO,
Z.DOCTYPE AS DOCUMENTTYPE,
Z.DOCDATE AS DOCUMENTDATE,
Z.GLPOSTINGDATE AS GLPOSTINGDATE,
Z.ORTRXAMT AS DOCUMENTAMT,
CASE WHEN Z.[RMDTYPAL] <= 6
THEN ISNULL(( SELECT SUM(Y.[ActualApplyToAmount])
FROM ( SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[ActualApplyToAmount]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[ActualApplyToAmount]
FROM dbo.[RM30201]
) Y
WHERE Y.[DATE1] <= @ASOFDATE
AND Y.[APFRDCDT] <= @ASOFDATE
AND Y.[CUSTNMBR] = Z.CUSTNMBR
AND Y.[APTODCNM] = Z.DOCNUMBR
AND Y.[APTODCTY] = Z.RMDTYPAL
), 0)
WHEN Z.[RMDTYPAL] > 7
AND Z.[RMDTYPAL] <= 9
THEN ISNULL(( SELECT SUM(Y.[ActualApplyToAmount])
FROM ( SELECT [CUSTNMBR],
[DATE1],
[APTODCDT],
[APFRDCNM],
[APFRDCTY],
[ActualApplyToAmount]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[DATE1],
[APTODCDT],
[APFRDCNM],
[APFRDCTY],
[ActualApplyToAmount]
FROM dbo.[RM30201]
) Y
WHERE Y.[DATE1] <= @ASOFDATE
AND Y.[APTODCDT] <= @ASOFDATE
AND Y.[CUSTNMBR] = Z.CUSTNMBR
AND Y.[APFRDCNM] = Z.DOCNUMBR
AND Y.[APFRDCTY] = Z.RMDTYPAL
), 0)
ELSE 0
END AS APPLIEDAMT,
CASE WHEN Z.[RMDTYPAL] <= 6
THEN ISNULL(( SELECT SUM(Y.[WROFAMNT])
FROM ( SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[WROFAMNT]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[WROFAMNT]
FROM dbo.[RM30201]
) Y
WHERE Y.[DATE1] <= @ASOFDATE
AND Y.[APFRDCDT] <= @ASOFDATE
AND Y.[CUSTNMBR] = Z.CUSTNMBR
AND Y.[APTODCNM] = Z.DOCNUMBR
AND Y.[APTODCTY] = Z.RMDTYPAL
), 0)
ELSE 0
END AS WRITEOFFAMT,
CASE WHEN Z.[RMDTYPAL] <= 6
THEN ISNULL(( SELECT SUM(Y.[DISTKNAM])
FROM ( SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[DISTKNAM]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[DATE1],
[APFRDCDT],
[APTODCNM],
[APTODCTY],
[DISTKNAM]
FROM dbo.[RM30201]
) Y
WHERE Y.[DATE1] <= @ASOFDATE
AND Y.[APFRDCDT] <= @ASOFDATE
AND Y.[CUSTNMBR] = Z.CUSTNMBR
AND Y.[APTODCNM] = Z.DOCNUMBR
AND Y.[APTODCTY] = Z.RMDTYPAL
), 0)
ELSE 0
END AS DISCTAKENAMT,
CASE WHEN Z.[RMDTYPAL] > 7
AND Z.[RMDTYPAL] <= 9
THEN ISNULL(( SELECT SUM(Y.[RLGANLOS])
FROM ( SELECT [CUSTNMBR],
[DATE1],
[APTODCDT],
[APFRDCNM],
[APFRDCTY],
[RLGANLOS]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[DATE1],
[APTODCDT],
[APFRDCNM],
[APFRDCTY],
[RLGANLOS]
FROM dbo.[RM30201]
) Y
WHERE Y.[DATE1] <= @ASOFDATE
AND Y.[APTODCDT] <= @ASOFDATE
AND Y.[CUSTNMBR] = Z.CUSTNMBR
AND Y.[APFRDCNM] = Z.DOCNUMBR
AND Y.[APFRDCTY] = Z.RMDTYPAL
), 0)
ELSE 0
END AS REALGAINLOSSAMT
FROM ( SELECT A.[CUSTNMBR],
A.[CPRCSTNM],
A.[DOCNUMBR],
A.[RMDTYPAL],
B.[DOCDESCR] AS DOCTYPE,
A.[DOCDATE],
A.[GLPOSTDT] AS GLPOSTINGDATE,
A.[ORTRXAMT]
FROM [dbo].[RM20101] A
INNER JOIN RM40401 B ON A.[RMDTYPAL] = B.[RMDTYPAL]
WHERE [VOIDSTTS] = 0
UNION
SELECT A.[CUSTNMBR],
A.[CPRCSTNM],
A.[DOCNUMBR],
A.[RMDTYPAL],
B.[DOCDESCR] AS DOCTYPE,
A.[DOCDATE],
A.[VOIDDATE] AS GLPOSTINGDATE,
A.[ORTRXAMT] * -1
FROM [dbo].[RM20101] A
INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
WHERE [VOIDSTTS] = 1
UNION
SELECT A.[CUSTNMBR],
A.[CPRCSTNM],
A.[DOCNUMBR],
A.[RMDTYPAL],
B.[DOCDESCR] AS DOCTYPE,
A.[DOCDATE],
A.[GLPOSTDT] AS GLPOSTINGDATE,
A.[ORTRXAMT]
FROM [dbo].[RM30101] A
INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
WHERE [VOIDSTTS] = 0
UNION
SELECT A.[CUSTNMBR],
A.[CPRCSTNM],
A.[DOCNUMBR],
A.[RMDTYPAL],
B.[DOCDESCR] AS DOCTYPE,
A.[DOCDATE],
A.[VOIDDATE] AS GLPOSTINGDATE,
A.[ORTRXAMT] * -1
FROM [dbo].[RM30101] A
INNER JOIN [dbo].[RM40401] B ON A.[RMDTYPAL] = B.[RMDTYPAL]
WHERE [VOIDSTTS] = 1
) Z
WHERE Z.DOCDATE <= @ASOFDATE
) X
http://cvakumar.com/msdynamics/2010/11/07/receivables-hatb-report-using-gl-posting-date/
