Page 1 of 6123456

Archive for the ‘SQL Server 2008’ 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,138

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

Post Through GL for Computer Checks – with Analytical Accounting

When you have Analytical Accounting module installed and configured in a GP company, the Post Through GL for Computer Checks gets automatically disabled in the specific company (even if it was turned on).

image

If we try to activate the same manually (with Analytical Accounting activated), we get the following error message and the system prevents us from turning on the Post Through GL functionality for Computer Checks.

image

However, when we login to another company on the same DYNAMICS instance, where Analytical Accounting is not activated, the system does not allow us to mark the Post Through GL option for Computer checks and gives the same error message as above. However, I had confirmed that Analytical Accounting was not installed by trying to open the Transaction Dimension window from Cards >> Financials >> Analytical Accounting >> Transaction Dimension, and I obtained the message below.

image

This indicated that Analytical Accounting was not installed or activated on this company and yet the system does not let me mark the Post Through GL option. (This issue happens on both Fabricam, Inc. and on a regular production company). This appears to be a genuine bug in Analytical Accounting, and I had tested this on GP v2010 R2 (11.0.1752)

As a work-around for this, I enabled the Post Through GL for Computer Checks in a company where Analytical Accounting was not activated by a SQL query, and “Eureka…”, it worked brilliantly and the computer check batches got posted through into the General Ledger successfully without any errors. The script I had used for updating the Posting Setup is given below. This script needs to be run on the specific company database where Analytical Accounting has not been activated.

UPDATE dbo.SY02300
    SET AUTPSTGL = 1
    WHERE SERIES = 4
        AND TRXSOURC = 'Computer Checks'

Hope this helps the community…

Until next post!

August 4, 2011 · veeyeskay · No Comments
Tags: , , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,603

Recent Updates to Payables HATB Script

Hi all

I have posted an updated for the following scripts today. Please make use of these updated scripts for the historical aged trial balance in payables.

AP Historical Aged Trial Balance – By GL Posting Date

http://msdynamicstips.com/2010/10/09/payables-hatb-report-sql-query/

AP Historical Aged Trial Balance – By Document Date

http://msdynamicstips.com/2010/10/08/payables-hatb-using-document-date/

Hope this update helps the community…

Until next post!

June 30, 2011 · veeyeskay · One Comment
Tags: , ,  · Posted in: Accounts Payables, Dynamics, Great Plains, Reporting, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 1,591

SQL Script for Purchase Order Line Items Not Yet Received

Hi all

Today, I am posting a script which provides the details of the Purchase Order Line Items which are yet to be received.

/******************************************************************
Created June 23, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view returns a returns the details of the PO Line which has
not been received fully. It provides the details of the quantity
to be received and the value of the line item to be received in
originating currency.
 
Tables used: 
 - POP10100 - Purchase Order Work
 - POP10110 - Purchase Order Line
 - POP10500 - Purchasing Receipt Line Quantities
 ******************************************************************/
CREATE VIEW vw_PORemainingDtl
AS  
SELECT  PO.[PO NUMBER] ,
        PO.[PO STATUS] ,
        PO.[PO TYPE] ,
        PO.[PO DATE] ,
        PO.[Currency ID] ,
        PO.[Vendor ID] ,
        PO.[Vendor Name] ,
        PO.[PO Line NUMBER] ,
        PO.[Item NUMBER] ,
        PO.[Item DESC] ,
        PO.[Vendor Item NUMBER] ,
        PO.[Vendor Item DESC] ,
        PO.[Location Code] ,
        PO.[UoM] ,
        ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] ) AS [Qty TO Recv] ,
        ( ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] )
          * PO.[Orig Unit Cost] ) AS [PO Line Remain VALUE]
FROM    ( SELECT    A.PONUMBER AS [PO NUMBER] ,
                    CASE WHEN A.POSTATUS = 1 THEN 'New'
                         WHEN A.POSTATUS = 2 THEN 'Released'
                         WHEN A.POSTATUS = 3 THEN 'Change Order'
                         WHEN A.POSTATUS = 4 THEN 'Received'
                         WHEN A.POSTATUS = 5 THEN 'Closed'
                         ELSE 'Canceled'
                    END AS [PO STATUS] ,
                    CASE WHEN A.POTYPE = 1 THEN 'Standard'
                         WHEN A.POTYPE = 2 THEN 'Drop-ship'
                         WHEN A.POTYPE = 3 THEN 'Blanket'
                         WHEN A.POTYPE = 4 THEN 'Drop-ship Blanket'
                         ELSE ''
                    END AS [PO TYPE] ,
                    A.DOCDATE AS [PO DATE] ,
                    A.CURNCYID AS [Currency ID] ,
                    A.ORSUBTOT AS [Orig Subtotal] ,
                    A.ORTDISAM AS [Orig Trade Disc] ,
                    A.ORFRTAMT AS [Orig Freight Amt] ,
                    A.OMISCAMT AS [Orig Misc Amt] ,
                    A.ORTAXAMT AS [Orig Tax Amt] ,
                    A.VENDORID AS [Vendor ID] ,
                    A.VENDNAME AS [Vendor Name] ,
                    B.ORD AS [PO Line NUMBER] ,
                    CASE WHEN B.POLNESTA = 1 THEN 'New'
                         WHEN B.POLNESTA = 2 THEN 'Released'
                         WHEN B.POLNESTA = 3 THEN 'Change Order'
                         WHEN B.POLNESTA = 4 THEN 'Received'
                         WHEN B.POLNESTA = 5 THEN 'Closed'
                         ELSE 'Canceled'
                    END AS [PO Line STATUS] ,
                    B.ITEMNMBR AS [Item NUMBER] ,
                    B.ITEMDESC AS [Item DESC] ,
                    B.VNDITNUM AS [Vendor Item NUMBER] ,
                    B.VNDITDSC AS [Vendor Item DESC] ,
                    B.LOCNCODE AS [Location Code] ,
                    B.UOFM AS [UoM] ,
                    B.QTYORDER AS [Qty Ordered] ,
                    B.QTYCANCE AS [Qty Canceled] ,
                    B.ORUNTCST AS [Orig Unit Cost] ,
                    B.OREXTCST AS [Orig Extd Cost] ,
                    ISNULL(( SELECT SUM(C.QTYSHPPD)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Shipped] ,
                    ISNULL(( SELECT SUM(C.QTYINVCD)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Invoiced] ,
                    ISNULL(( SELECT SUM(C.QTYRESERVED)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Returned]
          FROM      TWO.dbo.POP10100 A
                    INNER JOIN TWO.dbo.POP10110 B ON A.PONUMBER = B.PONUMBER
        ) PO
WHERE   ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] ) > 0

Hope this script is useful to the community…

Until next post!

June 23, 2011 · veeyeskay · 8 Comments
Tags: , , , , ,  · Posted in: Dynamics, Great Plains, Purchase Order Processing, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 7,946

SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Budget Tree Node

Hi all

This script is in continuation to the previous article where I had posted a script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code.

This script is for viewing Budgets vs. Actuals (for amounts) at Budget Tree Node level.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[CVA_aaGetLvlCodeString]')
                    AND TYPE IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    DROP FUNCTION [dbo].[CVA_aaGetLvlCodeString]
 
GO
 
/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This function returns a string which contains the AA codes for 
a specific distribution line from the AA tables for a specific
budget tree.
 
Tables used: 
 - AAG00901 - AA Budget Tree Trx Dim Master
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 ******************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CVA_aaGetLvlCodeString]
    (
      @aaBudgetTreeID INT ,
      @aaGLHdrID INT ,
      @aaGLDistID INT ,
      @aaGLAssignID INT
    )
RETURNS NVARCHAR(51)
AS 
    BEGIN
        DECLARE @aaLvlCodeString AS NVARCHAR(51)
        SELECT  @aaLvlCodeString = CASE WHEN @aaLvlCodeString IS NULL THEN ''
                                        ELSE @aaLvlCodeString + ','
                                   END + LTRIM(RTRIM(STR(A.aaTrxCodeID)))
        FROM    ( SELECT    E.aaOrder ,
                            D.aaTrxCodeID
                  FROM      dbo.AAG30000 A
                            INNER JOIN dbo.AAG30001 B ON A.aaGLHdrID = B.aaGLHdrID
                            INNER JOIN dbo.AAG30002 C ON B.aaGLHdrID = C.aaGLHdrID
                                                         AND B.aaGLDistID = C.aaGLDistID
                            INNER JOIN dbo.AAG30003 D ON C.aaGLHdrID = D.aaGLHdrID
                                                         AND C.aaGLDistID = D.aaGLDistID
                                                         AND C.aaGLAssignID = D.aaGLAssignID
                            INNER JOIN dbo.AAG00901 E ON D.aaTrxDimID = E.aaTrxDimID
                  WHERE     E.aaBudgetTreeID = @aaBudgetTreeID
                            AND D.aaGLHdrID = @aaGLHdrID
                            AND D.aaGLDistID = @aaGLDistID
                            AND D.aaGLAssignID = @aaGLAssignID
                ) A
        ORDER BY A.aaOrder
        RETURN @aaLvlCodeString
    END
 
/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This script is to return budget vs. actual values from AA module
 
Tables used: 
 - AAG00905 - AA Budget Tree Account Balance
 - AAG00903 - AA Budget Master
 - AAG00902 - AA Budget Tree Trx Dim Code Master
 - AAG00900 - AA Budget Tree Master
 - AAG00401 - aaTrxDimCodeSetp
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 - GL00100 - Account Master
 - GL00102 - Account Category Master
 - GL00105 - Account Index Master
 - SY40100 - Period Setup 
 
Custom Functions used:
 - CVA_aaGetLvlCodeString()
 ******************************************************************/
DECLARE @aaBudgetID INTEGER
 
--Define the Budget ID for analysis purposes
SET @aaBudgetID = 3
 
SELECT  B.aaBudget ,
        B.aaBudgetDescr ,
        C.aaBudgetTree ,
        C.aaBudgetTreeDescr ,
        J.YEAR1 AS glFiscalYear ,
        J.PERNAME AS glPeriodName ,
        CASE WHEN J.PERIODID <> 0
             THEN REPLICATE('0', 2 - LEN(J.PERIODID))
                  + ( LTRIM(RTRIM(STR(J.PERIODID))) + ':'
                      + ( LEFT({fn MONTHNAME(J.PERIODDT)}, 3) )
                      + '''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1))),
                                         3, 2) )
             ELSE '00:BBF''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1
                                                  - 1))), 3, 2)
        END AS glFiscalPeriod ,
        RTRIM(H.ACTNUMST) AS glAccountNo ,
        RTRIM(G.ACTDESCR) AS glAccountDesc ,
        RTRIM(I.ACCATDSC) AS glAccountCategory ,
        K.aaTrxDim AS aaTrxDimChild, 
        E.aaTrxDimCode AS aaTrxDimCodeChild ,
        ISNULL(L.aaTrxDim, '') AS aaTrxDimParent ,
        ISNULL(F.aaTrxDimCode, '') AS aaTrxDimCodeParent ,
        A.Balance AS aaBudgetAmount ,
        D.aaLvlCodeString ,
        ISNULL(( SELECT SUM(Z1.DEBITAMT - Z1.CRDTAMNT)
                 FROM   ( SELECT DISTINCT
                                    C1.DEBITAMT ,
                                    C1.CRDTAMNT
                          FROM      dbo.AAG30000 A1
                                    INNER JOIN dbo.AAG30001 B1 ON A1.aaGLHdrID = B1.aaGLHdrID
                                    INNER JOIN dbo.AAG30002 C1 ON B1.aaGLHdrID = C1.aaGLHdrID
                                                              AND B1.aaGLDistID = C1.aaGLDistID
                                    INNER JOIN dbo.AAG30003 D1 ON C1.aaGLHdrID = D1.aaGLHdrID
                                                              AND C1.aaGLDistID = D1.aaGLDistID
                                                              AND C1.aaGLAssignID = D1.aaGLAssignID
                                    INNER JOIN dbo.SY40100 E1 ON A1.GLPOSTDT >= E1.PERIODDT
                                                              AND A1.GLPOSTDT <= E1.PERDENDT
                          WHERE     E1.SERIES = 0
                                    AND E1.PERIODDT = A.PERIODDT
                                    AND B1.ACTINDX = A.ACTINDX
                                    AND dbo.CVA_aaGetLvlCodeString(C.aaBudgetTreeID,
                                                              C1.aaGLHdrID,
                                                              C1.aaGLDistID,
                                                              C1.aaGLAssignID) = D.aaLvlCodeString
                        ) Z1
               ), 0) AS aaActualAmount
FROM    dbo.AAG00905 A
        INNER JOIN dbo.AAG00903 B ON A.aaBudgetID = B.aaBudgetID
        INNER JOIN dbo.AAG00900 C ON B.aaBudgetTreeID = C.aaBudgetTreeID
        INNER JOIN dbo.AAG00902 D ON B.aaBudgetTreeID = D.aaBudgetTreeID
                                     AND A.aaCodeSequence = D.aaCodeSequence
        INNER JOIN dbo.AAG00401 E ON E.aaTrxDimCodeID = D.aaTrxDimCodeID
        LEFT OUTER JOIN dbo.AAG00401 F ON F.aaTrxDimCodeID = D.aaTrxDimParCodeID
        INNER JOIN dbo.AAG00400 K ON E.aaTrxDimID = K.aaTrxDimID
        LEFT OUTER JOIN dbo.AAG00400 L ON F.aaTrxDimID = L.aaTrxDimID
        INNER JOIN dbo.GL00100 G ON A.ACTINDX = G.ACTINDX
        INNER JOIN dbo.GL00105 H ON A.ACTINDX = H.ACTINDX
        INNER JOIN dbo.GL00102 I ON G.ACCATNUM = I.ACCATNUM
        INNER JOIN dbo.SY40100 J ON A.PERIODDT = J.PERIODDT
WHERE   J.SERIES = 0
        AND A.aaBudgetID = @aaBudgetID

Hope this script helps the community…

Until next post!

June 16, 2011 · veeyeskay · One Comment
Tags: , , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 3,435

SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code

Hi all

Today, I am posting a script for analyzing Budgets vs. Actuals (using Amounts) in Analytical Accounting. However, there are two methods of analyzing the budget information in Analytical Accounting. They are:

  • Node Level
  • Dimension Code Level

This script is for viewing Budgets vs. Actuals at Dimension Code level. I will be posting the script for analyzing budgets at Node level soon.

/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This script is to return budget vs. actual values from AA module
 
Tables used: 
 - AAG00905 - AA Budget Tree Account Balance
 - AAG00903 - AA Budget Master
 - AAG00902 - AA Budget Tree Trx Dim Code Master
 - AAG00900 - AA Budget Tree Master
 - AAG00401 - aaTrxDimCodeSetp
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 - GL00100 - Account Master
 - GL00102 - Account Category Master
 - GL00105 - Account Index Master
 - SY40100 - Period Setup 
******************************************************************/
SELECT  BvA.aaBudget ,
        BvA.glFiscalYear ,
        BvA.glFiscalPeriod ,
        Bva.glAccountCategory ,
        BvA.glAccountNo ,
        BvA.glAccountDesc ,
        BvA.aaTrxDim ,
        BvA.aaTrxDimCode ,
        BvA.aaTrxDimCodeDescr ,
        BvA.aaActualAmount AS aaActualAmount ,
        BvA.aaBudgetAmount AS aaBudgetAmount ,
        ( Bva.aaBudgetAmount - BvA.aaActualAmount ) AS aaVariance ,
        CASE WHEN BvA.aaBudgetAmount <> 0
             THEN CONVERT(NUMERIC(5, 2), ROUND(( ( ( Bva.aaBudgetAmount
                                                     - BvA.aaActualAmount )
                                                   / BvA.aaBudgetAmount )
                                                 * 100 ), 2))
             ELSE CONVERT(NUMERIC(19, 2), BvA.aaBudgetAmount)
        END AS aaVariancePercent
FROM    ( SELECT  DISTINCT
                    B.aaBudget ,
                    RTRIM(B.aaBudgetDescr) AS aaBudgetDescr ,
                    J.YEAR1 AS glFiscalYear ,
                    CASE WHEN J.PERIODID <> 0 THEN {fn MONTHNAME(J.PERIODDT)}
                         ELSE 'BBF'
                    END AS glPeriodName ,
                    CASE WHEN J.PERIODID <> 0
                         THEN REPLICATE('0', 2 - LEN(J.PERIODID))
                              + ( LTRIM(RTRIM(STR(J.PERIODID))) + ':'
                                  + ( LEFT({fn MONTHNAME(J.PERIODDT)}, 3) )
                                  + '''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1))),
                                                     3, 2) )
                         ELSE '00:BBF''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1
                                                              - 1))), 3, 2)
                    END AS glFiscalPeriod ,
                    RTRIM(H.ACTNUMST) AS glAccountNo ,
                    RTRIM(G.ACTDESCR) AS glAccountDesc ,
                    RTRIM(I.ACCATDSC) AS glAccountCategory ,
                    RTRIM(E1.aaTrxDim) AS aaTrxDim ,
                    RTRIM(E.aaTrxDimCode) AS aaTrxDimCode ,
                    RTRIM(E.aaTrxDimCodeDescr) AS aaTrxDimCodeDescr ,
                    A.Balance AS aaBudgetAmount ,
                    ISNULL(( SELECT SUM(C1.DEBITAMT - C1.CRDTAMNT)
                             FROM   dbo.AAG30000 A1
                                    INNER JOIN dbo.AAG30001 B1 ON A1.aaGLHdrID = B1.aaGLHdrID
                                    INNER JOIN dbo.AAG30002 C1 ON B1.aaGLHdrID = C1.aaGLHdrID
                                                              AND B1.aaGLDistID = C1.aaGLDistID
                                    INNER JOIN dbo.AAG30003 D1 ON C1.aaGLHdrID = D1.aaGLHdrID
                                                              AND C1.aaGLDistID = D1.aaGLDistID
                                                              AND C1.aaGLAssignID = D1.aaGLAssignID
                                    INNER JOIN dbo.SY40100 E1 ON A1.GLPOSTDT >= E1.PERIODDT
                                                              AND A1.GLPOSTDT <= E1.PERDENDT
                             WHERE  E1.SERIES = 0
                                    AND E1.PERIODDT = A.PERIODDT
                                    AND B1.ACTINDX = A.ACTINDX
                                    AND D1.aaTrxCodeID = D.aaTrxDimCodeID
                           ), 0) AS aaActualAmount
          FROM      dbo.AAG00905 A
                    INNER JOIN dbo.AAG00903 B ON A.aaBudgetID = B.aaBudgetID
                    INNER JOIN dbo.AAG00902 D ON B.aaBudgetTreeID = D.aaBudgetTreeID
                                                 AND A.aaCodeSequence = D.aaCodeSequence
                    INNER JOIN dbo.AAG00401 E ON E.aaTrxDimCodeID = D.aaTrxDimCodeID
                    INNER JOIN dbo.AAG00400 E1 ON E.aaTrxDimID = E1.aaTrxDimID
                    INNER JOIN dbo.GL00100 G ON A.ACTINDX = G.ACTINDX
                    INNER JOIN dbo.GL00105 H ON A.ACTINDX = H.ACTINDX
                    INNER JOIN dbo.GL00102 I ON G.ACCATNUM = I.ACCATNUM
                    INNER JOIN dbo.SY40100 J ON A.PERIODDT = J.PERIODDT
          WHERE     J.SERIES = 0
        ) BvA

Hope this script helps the community…

Until next post!

June 16, 2011 · veeyeskay · 7 Comments
Tags: , ,  · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 4,013