Page 1 of 41234

Archive for the ‘General Ledger’ Category

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,622

Account Level Security and Creating New GL Accounts

When you have Account Level Security enabled for a company, you would have come across the error message shown below, when we try to add a new account number and tab off the account number field.

image

This is because new GL accounts can be created ONLY by a user who has access to all GL Accounts. So we need to have a super user in the GL module who has been assigned access to all accounts in the Organizational Structure Assignment window which opens from Microsoft Dynamics GP menu >> Tools >> Setup >> System >> User >> Select a user >> Organization button.

image

We need to create at least one user in GP who has the option for “Grant Access to All Accounts” checked in the above window, so that he can create GL accounts in GP.

Note: By default the user ID “sa” will have access to all GL accounts.

Hope this helps the community…

Until next post!

September 18, 2011 · veeyeskay · 3 Comments
Tags: , , , , ,  · Posted in: Dynamics, General Ledger, Great Plains Total Views: 2,455

Reporting Ledgers and Fixed Asset Depreciations

Recently, I was assisting a support request where one of our clients were trying to read the window help for the GL Posting window in Fixed Assets in Dynamics GP 2010. The window help was referencing a field called Selected Books, but this field was not visible to them at run time. This is because of the Reporting Ledgers functionality in Dynamics GP 2010.

image

In order to enable this field, we need to perform the following setups in Dynamics GP 2010.

First enable Reporting Ledgers in the General Ledger Setup from Microsoft Dynamics GP >> Setup >> Financials >> General Ledger window as shown below.

image

Then open the Fixed Assets Company Setup window from Microsoft Dynamics GP >> Setup >> Fixed Assets >> Company and choose the option “Allow Reporting Ledgers”.

image

Once this is setup, open the Book Setup window from Microsoft Dynamics GP >> Setup >> Fixed Assets >> Book and create a book and link it to a Reporting Ledger in the GL module.

image

Once these setups are completed, the system will list out the books which have been linked to the Reporting Ledgers in the GL Posting window, which opens from Microsoft Dynamics GP >> Routines >> Fixed Assets >> GL Posting.

image

Hope this helps the community…

Until next post!

August 17, 2011 · veeyeskay · 2 Comments
Tags: , , , ,  · Posted in: Dynamics, Fixed Assets, General Ledger, Great Plains Total Views: 2,888

History for Budget Transactions in Dynamics GP 2010 R2

Once you install Dynamics GP 2010, if you go to the General Ledger Setup, the following options will automatically have the history options marked.

  • Accounts
  • Transactions

However, the history option for Budget Transactions is not automatically checked and because of this default setting, the system does not maintain a history of the budget transactions posted in GP. I was assuming that this option would be checked by default for any new company (like the other history options in the GL Setup window), but it was not to be.

image

Luckily the client had not posted much in the system, so we were able to restore the data and check this option and then have the users post the budget transactions which then maintained a valid history.

So kindly ensure that you mark this option for maintaining the history for Budget Transactions when setting up a new company in Dynamics GP 2010. Even R2 has this issue and planning to report this to Microsoft as a bug.

Hope this helps the community…

Until next post!

August 16, 2011 · veeyeskay · One Comment
Tags: , , , ,  · Posted in: Budget Transactions, Dynamics, General Ledger, Great Plains Total Views: 983

Nice Features for Auditing Information in GP

Hi all

I read the recent article posted by Vaidy titled Sales Quote Line Item Cancellation, where he explains the use of the Qty Canceled field and how it helps the companies to track the actual quote and details of the canceled item, rather than deleting the line item completely.

As a follow-up article for that, I have decided to post some of the features which we can turn on in GP, which will allow the users to prevent deletion of information from GP for tracking, audit and analysis purposes.

General Ledger

In the General Ledger Setup window, there is an option to prevent Deletion of Saved Transactions.

image

Unchecking this box, prevents the user from deleting a saved journal entry. Once the user saves a journal entry, the Delete button gets disabled when you try to access the saved journal entry, thereby allowing the user to delete the journal entry. Further, the Delete button on the Batch window also gets deleted for that specific batch. The only way to have the journal entry removed from the un-posted status would be to Void the journal entry in that batch.

image

Of course this voiding option is at the transaction level and if there is a batch of many transactions which need to be voided, it could be strenuous to void them manually. In such cases, we can still make use of a macro to void the journal entries in that batch.

Sales Order Processing

Just like the GL module, we have a setup to prevent deletion of the sales documents created within SOP. In the Sales Document Setup for Quote, Order, Invoices, Returns & Fulfillment Orders, there is an option to prevent deletion of documents.

image

So in a scenario when a customer requested a quote and later did not want to proceed with the order, we can Void the quote, to maintain audit, instead of deleting the saved quote. In this case, we have an additional control where we can provide deleting a quote with a specific authorization password. So in case there was a genuine mistake in preparing a quote (like a duplicate quote), we can make use of this password controlled deletion, by authorized people.

Purchase Order Processing

In case of Purchase Order processing, once a purchase order is created and saved it can be voided anytime before it is actually printed. However, there is no setup in Purchase Order Processing to force the users to void. It has to be manually controlled by the users. 

image 

However, once the purchase order is printed, it cannot be deleted and the system removes the option for Delete and Void from the Actions menu as shown below.

image

In such cases, we can choose the option Edit PO Status, which opens the Edit Purchase Orders window, where we can edit the status of the Purchase Order (or) the Purchase Order line.

image  image

Hope this article helps the community…

Until next post!

June 26, 2011 · veeyeskay · One Comment
Tags: , , , , ,  · Posted in: Dynamics, General Ledger, Great Plains, Purchase Order Processing, Sales Order Processing Total Views: 1,499

Table Structure Definitions – Multicurrency Account Master

Hi all

Today, I am starting a series of articles where I am planning to explain the table structure of some of the tables in Dynamics GP.

To begin with, I am going to explain the Multicurrency Summary Master (as revaluations is one of the concepts which I like most in a multicurrency environment). The physical name of this table in SQL Server is MC00200. This table has 5 columns as shown below.

Column  Name SQL Column Name Data type Length Comments
Account Index ACTINDX INT   This is the account index for the GL account defined in GP.
Currency ID CURNCYID CHAR 15 This field stores the currency ID for which the accounts have access.
Revalue REVALUE TINYINT   Stores information if the account is revalued or not.
Revalue How REVLUHOW SMALLINT   Stores the revaluation option (whether Net Change or Period Balance).
Post Results To Post_Results_To SMALLINT   Stores how the revaluations are posted (whether to the same account or offset account)

The window from which this table gets updated is the Account Currencies window which opens from Cards >> Financials >> Account Currencies. This window has both header and detailed line level information (in the scrolling window), but both information is stored in a single table which leads to the complication in the way the table information needs to be fetched.

image

Note that this table contains two sets of information and the data has to be read from this table depending on the type of information which needs to be fetched.

Fetching Account Currency Access Information

If we need to fetch the details of the various currencies which each account has access to, we need to use the following query.

SELECT  *
FROM    MC00200
WHERE   CURNCYID <> ''
ORDER BY ACTINDX,
        CURNCYID

In this scenario, the last three columns in the table above (REVALUE, REVLUHOW, Post_Results_To) do not have any relevance and for all the records returned by the above query, these three columns will always have a zero value. Further, we only need to pull out all the records which has a value for the CURNCYID column. There will be NO record for the functional currency of the database, since every GL account which is created in a company has access to the functional currency by default and cannot be changed by the user. This is why you ONLY see the various foreign currencies in the Account Currencies window shown above.

So when the user updates any values in the scrolling window, the system ONLY updates the CURNCYID column for the specific account and the other three columns are never updated and always remain at 0.

Note: If there is a non-zero value is present for any of these columns, it would be a data consistency issue and these columns have to be updated to 0 for the records returned by the above query. Data inconsistency in these records would result in discrepancies during the revaluation process.

Checking the Revaluation Status of an Account

If we need to check the revaluation status of an account and the revaluation options for the account, we need to use the following query.

SELECT  *
FROM    MC00200
WHERE   CURNCYID = ''
ORDER BY ACTINDX

In this scenario, the second column in the above table (CURNCYID) does not have any relevance. In order to check the revaluation status of an account, we ONLY need to pull the records in this table with a blank CURNCYID value.

So when the user updates the revaluation options in the header section of the window above, the system ONLY updates the last three columns in the table above (REVALUE, REVLUHOW, Post_Results_To) for records which have a CURNCYID as blank. It DOES NOT update any records which have a value for the CURNCYID column.

Hope this information is helpful to all…

Until next post…

May 3, 2011 · veeyeskay · No Comments
Tags: , , , , ,  · Posted in: Dynamics, General Ledger, Great Plains, Multicurrency Total Views: 3,228

SQL View for AA Accounting Class–GL Account Link Information

In this post, I am posting a SQL script for fetching the AA Accounting Class Link for various GL accounts in a company. It also provides us some key information about the accounting class that has been defined in Analytical Accounting.

SELECT  RTRIM(B.ACTNUMST) AS GPACCOUNTNO,
        RTRIM(A.ACTDESCR) AS GPACCOUNTDESC,
        ISNULL(D.aaAccountClass, '') AS AAACCOUNTINGCLASS,
        ISNULL(D.aaAcctClassDescr, '') AS AAACCOUNTINGCLASSDESC,
        ISNULL(CASE WHEN E.aaDataEntry = 1 THEN 'Not Allowed'
                    WHEN E.aaDataEntry = 2 THEN 'Required'
                    WHEN E.aaDataEntry = 3 THEN 'Optional'
                    WHEN E.aaDataEntry = 4 THEN 'Fixed'
                    ELSE 'None'
               END, '') AS DATAENTRYRULE,
        ISNULL(CASE WHEN E.aaShow = 1 THEN 'True'
                    ELSE 'False'
               END, '') AS DIMENSIONSHOW,
        ISNULL(CASE WHEN D.aaSetVendID = 0 THEN 'True'
                    ELSE 'False'
               END, '') AS REPORTONVENDORID,
        ISNULL(CASE WHEN D.aaSetCustID = 0 THEN 'True'
                    ELSE 'False'
               END, '') AS REPORTONCUSTOMERID,
        ISNULL(CASE WHEN D.aaSetSiteID = 0 THEN 'True'
                    ELSE 'False'
               END, '') AS REPORTONSITEID,
        ISNULL(CASE WHEN D.aaSetItemID = 0 THEN 'True'
                    ELSE 'False'
               END, '') AS REPORTONITEMID,
        ISNULL(F.aaTrxDim, '') AS DIMENSION,
        ISNULL(F.aaTrxDimDescr, '') AS DIMENSIONDESC,
        ISNULL(G.aaTrxDimCode, '') AS DIMENSIONCODE,
        ISNULL(G.aaTrxDimCodeDescr, '') AS DIMENSIONCODEDESC
FROM    dbo.GL00100 A
        INNER JOIN dbo.GL00105 B ON A.ACTINDX = B.ACTINDX
        LEFT OUTER JOIN dbo.AAG00200 C ON A.ACTINDX = C.ACTINDX
        LEFT OUTER JOIN dbo.AAG00201 D ON C.aaAcctClassID = D.aaAcctClassID
        LEFT OUTER JOIN dbo.AAG00202 E ON D.aaAcctClassID = E.aaAcctClassID
        LEFT OUTER JOIN dbo.AAG00400 F ON E.aaTrxDimID = F.aaTrxDimID
        LEFT OUTER JOIN dbo.AAG00401 G ON E.aaTrxDimCodeIDDflt = G.aaTrxDimCodeID

Hope this script is useful for the community folks.

Until next post!

January 15, 2011 · veeyeskay · 5 Comments
Tags: , , , , ,  · Posted in: Analytical Accounting, Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2005 Total Views: 2,131