Today, I have decided to post this script which is generated by picking transactions based on the GL posting date.
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 for reconciliation 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],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[ActualApplyToAmount]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[ActualApplyToAmount]
FROM dbo.[RM30201]
) Y
WHERE Y.[GLPOSTDT] <= @ASOFDATE
AND Y.[ApplyFromGLPostDate] <= @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],
[GLPOSTDT],
[ApplyToGLPostDate],
[APFRDCNM],
[APFRDCTY],
[ActualApplyToAmount]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[GLPOSTDT],
[ApplyToGLPostDate],
[APFRDCNM],
[APFRDCTY],
[ActualApplyToAmount]
FROM dbo.[RM30201]
) Y
WHERE Y.[GLPOSTDT] <= @ASOFDATE
AND Y.[ApplyToGLPostDate] <= @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],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[WROFAMNT]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[WROFAMNT]
FROM dbo.[RM30201]
) Y
WHERE Y.[GLPOSTDT] <= @ASOFDATE
AND Y.[ApplyFromGLPostDate] <= @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],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[DISTKNAM]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[GLPOSTDT],
[ApplyFromGLPostDate],
[APTODCNM],
[APTODCTY],
[DISTKNAM]
FROM dbo.[RM30201]
) Y
WHERE Y.[GLPOSTDT] <= @ASOFDATE
AND Y.[ApplyFromGLPostDate] <= @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],
[GLPOSTDT],
[ApplyToGLPostDate],
[APFRDCNM],
[APFRDCTY],
[RLGANLOS]
FROM dbo.[RM20201]
UNION
SELECT [CUSTNMBR],
[GLPOSTDT],
[ApplyToGLPostDate],
[APFRDCNM],
[APFRDCTY],
[RLGANLOS]
FROM dbo.[RM30201]
) Y
WHERE Y.[GLPOSTDT] <= @ASOFDATE
AND Y.[ApplyToGLPostDate] <= @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.GLPOSTINGDATE <= @ASOFDATE
) X
http://cvakumar.com/msdynamics/2010/11/07/receivables-hatb-report-using-gl-posting-date/
No comments:
Post a Comment