Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Sunday, July 28, 2013

Inventory versus GL

Inventory versus General Ledger reconciliation process has become much more easier with the new "IV Reconcile" tool in Dynamics GP 2013. Although, still a headache for previous version. The script below provides IV-GL matching per journal entry per account.


SQL SCRIPT - Inventory versus GL per Journal Entry per Account

--Tables Included:

---SEE30303 | Historical Inventory Trial Balance
---GL20000  | Year-to-Date Transaction Open
---GL30000  | Account Transaction History

 

DECLARE @AccountIndex NVARCHAR(MAX)
SET @AccountIndex = 'Fill in Account Index'

SELECT  *
FROM    ( SELECT    *
          FROM      ( SELECT   'OPEN' AS Status ,
                               [TRXDATE] ,
                               [JRNENTRY] ,
                              
[ACTINDX] ,
                               SUM([DEBITAMT]) Dr ,
                               SUM([CRDTAMNT]) Cr
                      FROM     GL20000
                      GROUP BY [TRXDATE] ,
                               [JRNENTRY] ,
                               [ACTINDX]
                      UNION ALL
                      SELECT   'History',
                               [TRXDATE],
                               [JRNENTRY],
                               [ACTINDX],
                               SUM([DEBITAMT]) Dr,
                               SUM([CRDTAMNT]) Cr
                      FROM     GL30000
                      GROUP BY [TRXDATE] ,
                               [JRNENTRY] ,
                               [ACTINDX]
                     ) AS C
                      WHERE  C.ACTINDX = @AccountIndex
        ) AS X
        FULL OUTER JOIN
       
( SELECT    [GLPOSTDT] ,
                    [JRNENTRY] ,
                    [IVIVINDX] ,
                    SUM([DEBITAMT]) AS Dr ,
                    SUM([CRDTAMNT]) AS Cr
                    FROM      ( SELECT  [GLPOSTDT] ,
                                        [JRNENTRY] ,
                                        [IVIVINDX] ,
                                        [DEBITAMT] ,
                                        [CRDTAMNT]
                                FROM    [SEE30303] AS M
                                UNION ALL
                                SELECT  [GLPOSTDT],
                                        [JRNENTRY],
                                        [IVIVOFIX],
                                        [CRDTAMNT],
                                        [DEBITAMT]
                                FROM  [SEE30303] AS N
                  ) AS C
                    WHERE     [IVIVINDX] = @AccountIndex
                    GROUP BY  [JRNENTRY],
                              [IVIVINDX],
                              [GLPOSTDT]
                    ) AS Y ON X.JRNENTRY = Y.JRNENTRY
                    WHERE  
                    ( ISNULL(X.Dr, 0) - ISNULL(X.Cr, 0) <>
                    ISNULL(Y.Dr, 0) - ISNULL(Y.Cr,0) )

 

Best Regards,
Mahmoud M. AlSaadi
 

6 comments:

  1. Thanks for you effort Mr. Alsaadi, but I can not understand how do you relate #AccountIndex could you please enlight to us how do you made the link.

    Thanks

    ReplyDelete
    Replies
    1. Mr. Julian
      The account index refers to the inventory account on which reconciliation between Inventory and General Ledger is to be retrieved by the script.

      The following query shall retrieve all the inventory account indexes:

      SELECT DISTINCT IVIVINDX AS AccountIndex,
      C.ACTNUMST AS InventoryAccountNumber,
      B.ACTDESCR AS InventoryAccountDescription
      FROM IV00101 AS A
      LEFT OUTER JOIN dbo.GL00100 AS B
      ON A.IVIVINDX = B.ACTINDX LEFT OUTER JOIN GL00105 AS C
      ON B.ACTINDX = C.ACTINDX

      Your feedback is highly appreciated,

      Delete
  2. Hello. I congratulate you again for such a wonderful blog. I found very interesting things and answers to several problems I 've had. I have now the followinr error
    "Conversion failed when converting the nvarchar value 'Fill in Account Index' to data type int."

    Thanks

    ReplyDelete
    Replies
    1. Hello again,
      I greatly appreciate having you as a follower,
      As for your question, apparently you have not filled in the "account index" at the declare statement

      DECLARE @AccountIndex NVARCHAR(MAX)
      SET @AccountIndex = 'Fill in Account Index'

      You will have to replace the (Fill in Account Index) by the inventory account index that you want to reconcile. To get the account index, consider checking the GL00100 or GL00105 as it includes an account index for every single account. In this essence, suppose the account index is (43), you will have to include it within the code above as follows:

      DECLARE @AccountIndex NVARCHAR(MAX)
      SET @AccountIndex = '43'

      Never hesitate to share any further inquiries,
      Mahmoud M. AlSaadi

      Delete