Page 1 of 3123

Archive for the ‘Accounts Payables’ Category

SQL Script for AP-GL Reconciliation – Updated

Hi all

Sometime back, I had posted the SQL Script for AP-GL Reconciliation in my blog. At the time of posting the script, it had supported only one account index for AP. Today, I have updated the script to support multiple account indexes for Accounts Payable.

Please take a look at the updated script in the URL below.

http://msdynamicstips.com/2012/01/17/sql-script-for-ap-gl-reconciliation/

Hope this update helps the community…

Until next post!

March 7, 2012 · veeyeskay · 4 Comments
Tags: , , , , , ,  · Posted in: Accounts Payables, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,146

SQL Script for AP-GL Reconciliation

Hi all

It has been some time since I have posted something useful on my blog. This was because I was busy writing up and testing the reconciliation script that I had developed to reconcile payables and receivables with the general ledger.

In this article, I have posted a script to reconcile payables with general ledger. I have tested it with a couple of live environments and it looks to be working fine. However, there might still be an unseen scenario which I might not have uncovered. Please let me know if there are any such scenarios which I might have missed out, so that I can incorporate the same in this.

/******************************************************************
Created Jan 17, 2012 by Sivakumar Venkataraman - Interdyn AKA
This view is for the reconcile the payables amounts with 
the corresponding amounts in the general ledger. 
 
Revision History
No.		Date			Description
1		01/17/2012		Original Version
2		03/07/2012		Updated the script to support multiple
						AP Account Indexes
*******************************************************************/
 
DECLARE @DELIMITER CHAR(1)
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
DECLARE @APACCOUNTS AS VARCHAR(50)
 
--Define the delimiter
SET @DELIMITER = '|'
--Leave this always as 1900-01-01. Do not change this. 
SET @EMPTYDATE = '1900-01-01' 
--This is the as of date to which we need to reconcile.
SET @ASOFDATE = '2017-04-12'
--Separate multiple account indexes by a comma
SET @APACCOUNTS = '35|36|37'
 
SELECT  APGL.*
FROM    ( SELECT  AP.*
          FROM    ( SELECT  APRECON.VENDORID ,
                            APRECON.DOCNUMBR ,
                            ISNULL(CONVERT(VARCHAR(20), APRECON.JRNENTRY), '') AS JRNENTRY ,
                            DT.DOCTYNAM AS DOCTYPE ,
                            APRECON.TRXSORCE AS AUDITTRAIL ,
                            APRECON.SLAMOUNT ,
                            APRECON.GLAMOUNT ,
                            CASE WHEN APRECON.GLAMOUNT = APRECON.SLAMOUNT
                                      AND APRECON.DOCSTATUS <> 'Work' THEN 'Matched'
                                 WHEN APRECON.DOCSTATUS = 'Work' THEN 'Unposted in GL, Posted in SL'
                                 WHEN APRECON.GLAMOUNT = 0 THEN 'Not in GL, Posted in SL'
                                 WHEN APRECON.GLAMOUNT <> APRECON.SLAMOUNT THEN 'Mismatched'
                            END AS RECONSTATUS
                    FROM    ( SELECT  AP.VENDORID ,
                                      AP.DOCNUMBR ,
                                      AP.DOCTYPE ,
                                      AP.TRXSORCE ,
                                      AP.MATCHKEY ,
                                      AP.APAMOUNT AS SLAMOUNT ,
                                      GL.JRNENTRY ,
                                      GL.DOCSTATUS ,
                                      ISNULL(GL.APAMOUNT, 0) AS GLAMOUNT
                              FROM    ( SELECT  W.VENDORID ,
                                                W.DOCNUMBR ,
                                                W.DOCTYPE ,
                                                W.TRXSORCE ,
                                                LTRIM(RTRIM(W.VENDORID)) + '-' + LTRIM(RTRIM(W.DOCTYPE)) + '-'
                                                + LTRIM(RTRIM(W.DOCNUMBR)) + LTRIM(RTRIM(W.TRXSORCE)) AS MATCHKEY ,
                                                SUM(W.DOCUMENTAMT) AS APAMOUNT
                                        FROM    ( SELECT  X.VENDORID ,
                                                          X.VCHRNMBR ,
                                                          X.DOCTYPE ,
                                                          X.DOCNUMBR ,
                                                          X.DOCDATE ,
                                                          X.TRXSORCE ,
                                                          X.VOIDED ,
                                                          X.PSTGDATE ,
                                                          X.VOIDPDATE ,
                                                          CASE WHEN X.DOCTYPE <= 3 THEN X.DOCUMENTAMT * -1
                                                               ELSE X.DOCUMENTAMT
                                                          END AS DOCUMENTAMT ,
                                                          X.APPLIEDAMT ,
                                                          X.WRITEOFFAMT ,
                                                          X.DISCTAKENAMT ,
                                                          X.REALGAINLOSSAMT ,
                                                          CASE WHEN X.DOCTYPE <= 3
                                                               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.VCHRNMBR ,
                                                                    Z.VENDORID ,
                                                                    Z.DOCTYPE ,
                                                                    Z.DOCDATE ,
                                                                    Z.DOCNUMBR ,
                                                                    Z.DOCAMNT AS DOCUMENTAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APPLDAMT
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APPLDAMT
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         WHEN DOCTYPE > 3
                                                                              AND DOCTYPE <= 6
                                                                         THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        APPLDAMT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        APPLDAMT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                              AND Y.DOCTYPE = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS APPLIEDAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        WROFAMNT
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        WROFAMNT
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS WRITEOFFAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        DISTKNAM
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        DISTKNAM
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS DISCTAKENAMT ,
                                                                    CASE WHEN DOCTYPE > 3
                                                                         THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate ,
                                                                                                        RLGANLOS
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate ,
                                                                                                        RLGANLOS
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                              AND Y.DOCTYPE = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS REALGAINLOSSAMT ,
                                                                    Z.TRXSORCE ,
                                                                    Z.VOIDED ,
                                                                    Z.PSTGDATE ,
                                                                    Z.VOIDPDATE
                                                            FROM    ( SELECT  VCHRNMBR ,
                                                                              VENDORID ,
                                                                              DOCTYPE ,
                                                                              DOCDATE ,
                                                                              DOCNUMBR ,
                                                                              DOCAMNT ,
                                                                              BACHNUMB ,
                                                                              TRXSORCE ,
                                                                              BCHSOURC ,
                                                                              DISCDATE ,
                                                                              VOIDED ,
                                                                              PSTGDATE ,
                                                                              @EMPTYDATE AS VOIDPDATE
                                                                      FROM    dbo.PM20000
                                                                      UNION ALL
                                                                      SELECT  VCHRNMBR ,
                                                                              VENDORID ,
                                                                              DOCTYPE ,
                                                                              DOCDATE ,
                                                                              DOCNUMBR ,
                                                                              DOCAMNT ,
                                                                              BACHNUMB ,
                                                                              TRXSORCE ,
                                                                              BCHSOURC ,
                                                                              DISCDATE ,
                                                                              VOIDED ,
                                                                              PSTGDATE ,
                                                                              VOIDPDATE
                                                                      FROM    dbo.PM30200
                                                                    ) Z
                                                            WHERE   Z.PSTGDATE <= @ASOFDATE
                                                                    AND Z.VOIDED = 0
                                                          ) X
                                                  UNION ALL
                                                  SELECT  X.VENDORID ,
                                                          X.VCHRNMBR ,
                                                          X.DOCTYPE ,
                                                          X.DOCNUMBR ,
                                                          X.DOCDATE ,
                                                          X.TRXSORCE ,
                                                          X.VOIDED ,
                                                          X.PSTGDATE ,
                                                          X.VOIDPDATE ,
                                                          X.DOCUMENTAMT ,
                                                          X.APPLIEDAMT ,
                                                          X.WRITEOFFAMT ,
                                                          X.DISCTAKENAMT ,
                                                          X.REALGAINLOSSAMT ,
                                                          ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT
                                                            - X.DISCTAKENAMT + X.REALGAINLOSSAMT ) * -1 AS CURTRXAMT
                                                  FROM    ( SELECT  Z.VCHRNMBR ,
                                                                    Z.VENDORID ,
                                                                    Z.DOCTYPE ,
                                                                    Z.DOCDATE ,
                                                                    Z.DOCNUMBR ,
                                                                    Z.DOCAMNT AS DOCUMENTAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APPLDAMT
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APPLDAMT
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         WHEN DOCTYPE > 3
                                                                              AND DOCTYPE <= 6
                                                                         THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        APPLDAMT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        APPLDAMT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                              AND Y.DOCTYPE = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS APPLIEDAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        WROFAMNT
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        WROFAMNT
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS WRITEOFFAMT ,
                                                                    CASE WHEN DOCTYPE <= 3
                                                                         THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        DISTKNAM
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        ApplyFromGLPostDate ,
                                                                                                        GLPOSTDT ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        DISTKNAM
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.APTVCHNM = Z.VCHRNMBR
                                                                                              AND Y.APTODCTY = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS DISCTAKENAMT ,
                                                                    CASE WHEN DOCTYPE > 3
                                                                         THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                                       FROM   ( SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate ,
                                                                                                        RLGANLOS
                                                                                                FROM    dbo.PM10200
                                                                                                UNION
                                                                                                SELECT  VENDORID ,
                                                                                                        GLPOSTDT ,
                                                                                                        VCHRNMBR ,
                                                                                                        DOCTYPE ,
                                                                                                        APTVCHNM ,
                                                                                                        APTODCTY ,
                                                                                                        ApplyToGLPostDate ,
                                                                                                        RLGANLOS
                                                                                                FROM    dbo.PM30300
                                                                                              ) Y
                                                                                       WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                              AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                              AND Y.VENDORID = Z.VENDORID
                                                                                              AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                              AND Y.DOCTYPE = Z.DOCTYPE
                                                                                     ), 0)
                                                                         ELSE 0
                                                                    END AS REALGAINLOSSAMT ,
                                                                    Z.TRXSORCE ,
                                                                    Z.VOIDED ,
                                                                    Z.PSTGDATE ,
                                                                    Z.VOIDPDATE
                                                            FROM    ( SELECT  VCHRNMBR ,
                                                                              VENDORID ,
                                                                              DOCTYPE ,
                                                                              DOCDATE ,
                                                                              DOCNUMBR ,
                                                                              DOCAMNT ,
                                                                              BACHNUMB ,
                                                                              TRXSORCE ,
                                                                              BCHSOURC ,
                                                                              DISCDATE ,
                                                                              VOIDED ,
                                                                              PSTGDATE ,
                                                                              @EMPTYDATE AS VOIDPDATE
                                                                      FROM    dbo.PM20000
                                                                      UNION
                                                                      SELECT  VCHRNMBR ,
                                                                              VENDORID ,
                                                                              DOCTYPE ,
                                                                              DOCDATE ,
                                                                              DOCNUMBR ,
                                                                              DOCAMNT ,
                                                                              BACHNUMB ,
                                                                              TRXSORCE ,
                                                                              BCHSOURC ,
                                                                              DISCDATE ,
                                                                              VOIDED ,
                                                                              PSTGDATE ,
                                                                              VOIDPDATE
                                                                      FROM    dbo.PM30200
                                                                    ) Z
                                                            WHERE   Z.PSTGDATE <= @ASOFDATE
                                                                    AND Z.VOIDED = 1
                                                                    AND Z.VOIDPDATE > @ASOFDATE
                                                          ) X
                                                ) W
                                        GROUP BY W.VENDORID ,
                                                W.DOCTYPE ,
                                                W.DOCNUMBR ,
                                                W.TRXSORCE
                                      ) AP
                                      LEFT OUTER JOIN ( SELECT  LTRIM(RTRIM(W.VENDORID)) + '-'
                                                                + LTRIM(RTRIM(W.DOCUMENTTYPE)) + '-'
                                                                + LTRIM(RTRIM(W.DOCUMENTNUMBER))
                                                                + LTRIM(RTRIM(W.ORIGSOURCE)) AS MATCHKEY ,
                                                                W.DOCSTATUS ,
                                                                W.JRNENTRY ,
                                                                W.APAMOUNT AS APAMOUNT
                                                        FROM    ( SELECT  'Open' AS DOCSTATUS ,
                                                                          ORMSTRID AS VENDORID ,
                                                                          CASE WHEN ORTRXTYP IN ( 2, 3 ) THEN 1
                                                                               ELSE ORTRXTYP
                                                                          END AS DOCUMENTTYPE ,
                                                                          CASE WHEN ORTRXTYP IN ( 2, 3 )
                                                                               THEN ( SELECT  VNDDOCNM
                                                                                      FROM    dbo.POP30300 A1
                                                                                      WHERE   A1.POPRCTNM = ORDOCNUM
                                                                                    )
                                                                               ELSE ORDOCNUM
                                                                          END AS DOCUMENTNUMBER ,
                                                                          JRNENTRY ,
                                                                          ORTRXSRC AS ORIGSOURCE ,
                                                                          SUM(DEBITAMT - CRDTAMNT) AS APAMOUNT
                                                                  FROM    dbo.GL20000
                                                                  WHERE   ACTINDX IN (
                                                                          SELECT  LISTITEM
                                                                          FROM    dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                                                                          AND TRXDATE <= @ASOFDATE
                                                                  GROUP BY ORMSTRID ,
                                                                          ORTRXTYP ,
                                                                          ORDOCNUM ,
                                                                          JRNENTRY ,
                                                                          ORTRXSRC
                                                                  UNION ALL
                                                                  SELECT  'Open' AS DOCSTATUS ,
                                                                          ORMSTRID AS VENDORID ,
                                                                          CASE WHEN ORTRXTYP IN ( 2, 3 ) THEN 1
                                                                               ELSE ORTRXTYP
                                                                          END AS DOCUMENTTYPE ,
                                                                          CASE WHEN ORTRXTYP IN ( 2, 3 )
                                                                               THEN ( SELECT  VNDDOCNM
                                                                                      FROM    dbo.POP30300 A1
                                                                                      WHERE   A1.POPRCTNM = ORDOCNUM
                                                                                    )
                                                                               ELSE ORDOCNUM
                                                                          END AS DOCUMENTNUMBER ,
                                                                          JRNENTRY ,
                                                                          ORTRXSRC AS ORIGSOURCE ,
                                                                          SUM(DEBITAMT - CRDTAMNT) AS APAMOUNT
                                                                  FROM    dbo.GL30000
                                                                  WHERE   ACTINDX IN (
                                                                          SELECT  LISTITEM
                                                                          FROM    dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                                                                          AND TRXDATE <= @ASOFDATE
                                                                  GROUP BY ORMSTRID ,
                                                                          ORTRXTYP ,
                                                                          ORDOCNUM ,
                                                                          JRNENTRY ,
                                                                          ORTRXSRC
                                                                  UNION ALL
                                                                  SELECT  'Work' AS DOCSTATUS ,
                                                                          A.ORMSTRID AS VENDORID ,
                                                                          CASE WHEN A.ORTRXTYP IN ( 2, 3 ) THEN 1
                                                                               ELSE A.ORTRXTYP
                                                                          END AS DOCUMENTTYPE ,
                                                                          CASE WHEN A.ORTRXTYP IN ( 2, 3 )
                                                                               THEN ( SELECT  VNDDOCNM
                                                                                      FROM    dbo.POP30300 A1
                                                                                      WHERE   A1.POPRCTNM = A.ORDOCNUM
                                                                                    )
                                                                               ELSE A.ORDOCNUM
                                                                          END AS DOCUMENTNUMBER ,
                                                                          A.JRNENTRY ,
                                                                          B.ORTRXSRC AS ORIGSOURCE ,
                                                                          SUM(A.DEBITAMT - A.CRDTAMNT) AS APAMOUNT
                                                                  FROM    dbo.GL10001 A
                                                                          INNER JOIN dbo.GL10000 B ON A.JRNENTRY = B.JRNENTRY
                                                                  WHERE   A.ACTINDX IN (
                                                                          SELECT  LISTITEM
                                                                          FROM    dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                                                                          AND B.TRXDATE <= @ASOFDATE
                                                                          AND B.SERIES = 4
                                                                  GROUP BY A.ORMSTRID ,
                                                                          A.ORTRXTYP ,
                                                                          A.ORDOCNUM ,
                                                                          A.JRNENTRY ,
                                                                          B.ORTRXSRC
                                                                ) W
                                                      ) GL ON AP.MATCHKEY = GL.MATCHKEY
                            ) APRECON
                            INNER JOIN dbo.PM40102 DT ON APRECON.DOCTYPE = DT.DOCTYPE
                  ) AP
          UNION ALL
          ( SELECT  '' AS VENDORID ,
                    '' AS DOCNUMBR ,
                    ISNULL(CONVERT(VARCHAR(20), JRNENTRY), '') AS JRNENTRY ,
                    'GL Entry' AS DOCTYPE ,
                    ORTRXSRC AS AUDITTRAIL ,
                    0 AS SLAMOUNT ,
                    SUM(DEBITAMT - CRDTAMNT) AS GLAMOUNT ,
                    'Posted in GL, Not in SL' AS RECONSTATUS
            FROM    dbo.GL20000
            WHERE   ACTINDX IN ( SELECT LISTITEM
                                 FROM   dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                    AND SERIES = 2
                    AND TRXDATE <= @ASOFDATE
            GROUP BY JRNENTRY ,
                    ORTRXSRC
          )
          UNION ALL
          ( SELECT  '' AS VENDORID ,
                    '' AS DOCNUMBR ,
                    ISNULL(CONVERT(VARCHAR(20), JRNENTRY), '') AS JRNENTRY ,
                    'GL Entry' AS DOCTYPE ,
                    ORTRXSRC AS AUDITTRAIL ,
                    0 AS SLAMOUNT ,
                    SUM(DEBITAMT - CRDTAMNT) AS GLAMOUNT ,
                    'Posted in GL, Not in SL' AS RECONSTATUS
            FROM    dbo.GL30000
            WHERE   ACTINDX IN ( SELECT LISTITEM
                                 FROM   dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                    AND SERIES = 2
                    AND TRXDATE <= @ASOFDATE
            GROUP BY JRNENTRY ,
                    ORTRXSRC
          )
          UNION ALL
          ( SELECT  '' AS VENDORID ,
                    '' AS DOCNUMBR ,
                    ISNULL(CONVERT(VARCHAR(20), A.JRNENTRY), '') AS JRNENTRY ,
                    'GL Entry' AS DOCTYPE ,
                    B.ORTRXSRC AS AUDITTRAIL ,
                    0 AS SLAMOUNT ,
                    SUM(A.DEBITAMT - A.CRDTAMNT) AS GLAMOUNT ,
                    'Unposted in GL, Not in SL' AS RECONSTATUS
            FROM    dbo.GL10001 A
                    INNER JOIN dbo.GL10000 B ON A.JRNENTRY = B.JRNENTRY
            WHERE   A.ACTINDX IN ( SELECT LISTITEM
                                   FROM   dbo.fnSplitter(@APACCOUNTS, @DELIMITER) )
                    AND B.SERIES = 2
                    AND B.TRXDATE <= @ASOFDATE
            GROUP BY A.JRNENTRY ,
                    B.ORTRXSRC
          )
        ) APGL

Please create the function below which would be needed to execute the script above with multiple AP accounts specified. The AP accounts need to be provided as a string with the delimiter as specified in the code above.

/******************************************************************
Created Jan 17, 2012 by Sivakumar Venkataraman - Interdyn AKA
This function is to split the string passed with the delimiter
into a table which can be used as a sub query in other queries. 
 
Revision History
No.		Date			Description
1		03/07/2012		Original Version
*******************************************************************/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[fnSplitter]
       (
         @INPUTSTRING VARCHAR(8000) -- List of delimited items
         ,
         @DELIMITER VARCHAR(8000) = ',' -- delimiter that separates items
       )
RETURNS @LISTING TABLE ( LISTITEM VARCHAR(8000) )
  BEGIN
    DECLARE @LISTITEM VARCHAR(8000)
    WHILE CHARINDEX(@DELIMITER, @INPUTSTRING, 0) <> 0
          BEGIN
                SELECT  @LISTITEM = RTRIM(LTRIM(SUBSTRING(@INPUTSTRING, 1, CHARINDEX(@DELIMITER, @INPUTSTRING, 0) - 1))) ,
                        @INPUTSTRING = RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,
                                                             CHARINDEX(@DELIMITER, @INPUTSTRING, 0) + LEN(@DELIMITER),
                                                             LEN(@INPUTSTRING))))
 
                IF LEN(@LISTITEM) > 0
                   INSERT INTO @LISTING
                          SELECT  @LISTITEM
          END
 
    IF LEN(@INPUTSTRING) > 0
       INSERT INTO @LISTING
              SELECT  @INPUTSTRING -- Put the last item in
    RETURN
  END
 
GO
 
GRANT SELECT ON fnSplitter TO DYNGRP

Currently this script works for a single payables account index. Will update the script to support multiple payable accounts soon.

Update 03/07/2012 – I have updated this script to support multiple AP account indexes.

Hope this helps the community…

Until next post!

January 17, 2012 · veeyeskay · 7 Comments
Tags: , , , , , ,  · Posted in: Accounts Payables, Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 6,252

Posting Interruptions in Accounts Payable – Check Runs

Recently, I was responding to a forum discussion where the issue mentioned was as shown below.

Occasionally, one of the subsystems that we integrate invoices from will send over an invoice with an incorrect date (11/20/1911 instead of 11/20/2011).  These invoices post just fine.  However, when we process a check run that includes an invoice with this old invoice date, the checks print just fine but then there is a check batch posting interruption.  Is this a known issue in GP?

I tried a similar scenario in v2010 R2 (11.0.1860) and I am able to replicate this issue. The error I receive is as follows. Even if I try to recover the batch from Batch Recovery, it tries posting again, and it ends up with the posting interruption. This causes the batch to be locked completely and I am unable to remove the batch from the application. I updated the batch status using SQL and the Edit List shows fine without issues.

1

2

This looked like a bug in the system and responded back accordingly. However, there was a response from a Microsoft moderator, and she had confirmed that there was a free tool in Professional Services Tools Library called “Document Date Verify Tool”, which will check to make sure the Document Date is valid within the fiscal periods you have set up for a variety of modules.

Frankly speaking, this fix should have been in the application code, and would need a change to the application code (instead of having to install a separate tool (even if free) to fix the problem. The moderator recommended me to add this as a product suggestion which I have done using Microsoft Connect in the URL below.

https://connect.microsoft.com/dynamicssuggestions/feedback/details/708061/posting-interruptions-in-accounts-payable

Let’s see how many people get to vouch for this. I understand that there is a solution which fixes this, but technically speaking, this should have been fixed in the product code itself, in my understanding. Smile Hope you people get to vouch for it and take it to Microsoft to build this into the standard application logic.

Until next post!

November 24, 2011 · veeyeskay · 3 Comments
Tags: , , , , , , , , , , ,  · Posted in: Accounts Payables, Dynamics, Great Plains Total Views: 3,179

SQL View for Daily Payment Summary

Hi all

Based on a request from the community, I have decided to post a script for identifying all the payments made on a specific date from a checkbook and the details of what was voided for the specific date.

This query helps a great deal in reconciling the funds that were used for processing the payments.

SELECT A.CHEKBKID ,
        A.PSTGDATE ,
        ISNULL((
                SELECT SUM(B.DOCAMNT)
                    FROM dbo.PM30200 B
                    WHERE B.DOCTYPE = 6
                        AND B.CHEKBKID = A.CHEKBKID
                        AND B.PSTGDATE = A.PSTGDATE
               ), 0) AS CHECKAMOUNT ,
        ISNULL((
                SELECT SUM(B.DOCAMNT)
                    FROM dbo.PM30200 B
                    WHERE B.DOCTYPE = 6
                        AND B.CHEKBKID = A.CHEKBKID
                        AND B.PSTGDATE = A.PSTGDATE
                        AND B.VOIDED = 1
               ), 0) AS VOIDEDCHECKS
    FROM dbo.PM30200 A
    WHERE A.DOCTYPE = 6
        AND A.PSTGDATE <> '1900-01-01'
    GROUP BY A.CHEKBKID ,
        A.PSTGDATE

Hope this helps the community…

Until next post!

August 5, 2011 · veeyeskay · 2 Comments
Tags: , , ,  · Posted in: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 1,982

Additional Purchases Accounts for Vendors

Hi all

Sometimes, when we book invoices against vendors, we might have to post the expense into multiple GL accounts based for the purpose of financial reporting. Mostly I have seen a specific purchases account defaulted at the vendor level and the users manually add additional expense lines at the distribution window at the time of transaction entry and apportion the expense to the various expense GL accounts.

This can be quickened, especially if we are able to identify the most common expense heads which get posted into at the time of the payables transaction. Once the accounts are defined, you can enter the various purchases accounts at the Vendor Account Maintenance window, by clicking on the image button near the purchases account description as shown below.

image      image

When the button is clicked, the Additional Vendor Accounts window is opened where we can enter the additional purchases accounts needed. You can also define these additional purchases accounts at the vendor class level as well. We can also specify which accounts should default on the transaction distribution entry window as well. These additional accounts which have been marked to default will automatically display on the distribution entry window as shown below.

image

The amounts default to 0 for these lines. The user can apportion the amounts to these accounts if necessary (or) leave them as 0 and continue posting the invoice without apportioning. If we leave the additional purchases account as 0, the system ignores those lines during the posting process and only picks the accounts which have an amount associated to it.

This would be really a time saving feature for many of the clients who may be doing this process manually day in and day out.

Hope this helps the community…

Until next post!

July 29, 2011 · veeyeskay · One Comment
Tags: , , , ,  · Posted in: Accounts Payables, Dynamics, Great Plains Total Views: 1,243

Payables HATB With Aging By Payment Terms (using Document Date)

As a follow up to my script for generating the Payables HATB With Aging By Due Date (using Document Date), I have decided to post a script for the same with aging buckets based on the document date (with the logic of payment terms). This will provide the aging buckets for each transaction based on the payables aging buckets setup.

/******************************************************************
Created July 25, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Payables HATB for aging the document
date (using the payment terms on the document) and
picking transactions using the Document Date.
 
Tables used:
 - PM20000 - PM Transaction OPEN File
 - PM30200 - PM Paid Transaction History File
 - PM10200 - PM Apply To WORK OPEN File
 - PM30300 - PM Apply To History File
 - PM40101 - PM Period Setup File
 - PM40102 - Payables Document Types
 - SY03300 - Payment Terms Master
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
 
SELECT  W.VENDORID ,
        W.VCHRNMBR ,
        W1.DOCTYNAM AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.PSTGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         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.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.DATE1 <= @ASOFDATE
                                                                                AND Y.APTODCDT <= @ASOFDATE
                                                                                AND Y.APTODCDT <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION ALL
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 0
                    ) X
          UNION ALL
          SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         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.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.DATE1 <= @ASOFDATE
                                                                                AND Y.APTODCDT <= @ASOFDATE
                                                                                AND Y.APTODCDT <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.PM40102 W1 ON W.DOCTYPE = W1.DOCTYPE
WHERE   W.CURTRXAMT <> 0

Hope this helps the community…

Update: 11/28/2011 – Based on feedback from a community member Louella Thomas, fixed an issue where applied information for an unposted payment was considered in the result.

Until nest post!

July 25, 2011 · veeyeskay · 2 Comments
Tags: , , , , ,  · Posted in: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 1,334

Payables HATB With Aging By Payment Terms (using GL Posting Date)

As a follow up to my script for generating the Payables HATB With Aging By Due Date (using GL Posting Date), I have decided to post a script for the same with aging buckets based on the document date (with the logic of payment terms). This will provide the aging buckets for each transaction based on the payables aging buckets setup.

/******************************************************************
Created July 25, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Payables HATB for aging the document
date (using the payment terms on the document) and
picking transactions using the GL Posting Date.
 
Tables used:
 - PM20000 - PM Transaction OPEN File
 - PM30200 - PM Paid Transaction History File
 - PM10200 - PM Apply To WORK OPEN File
 - PM30300 - PM Apply To History File
 - PM40101 - PM Period Setup File
 - PM40102 - Payables Document Types
 - SY03300 - Payment Terms Master
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
 
SELECT  W.VENDORID ,
        W.VCHRNMBR ,
        W1.DOCTYNAM AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.PSTGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         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.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION ALL
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 0
                    ) X
          UNION ALL
          SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         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.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.PM40102 W1 ON W.DOCTYPE = W1.DOCTYPE
WHERE   W.CURTRXAMT <> 0

Hope this helps the community…

Update: 11/28/2011 – Based on feedback from a community member Louella Thomas, fixed an issue where applied information for an unposted payment was considered in the result.

Until next post!

July 25, 2011 · veeyeskay · One Comment
Tags: , , , , ,  · Posted in: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 1,553