Receivables HATB With Aging By Due Date (using Document Date)

Microsoft Dynamics GPToday'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