Pages

▼

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 

at

8 comments:

  1. UnknownMay 14, 2014 at 1:01 PM

    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. Mahmoud M. AlSaadiMay 15, 2014 at 4:36 AM

      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. UnknownAugust 26, 2014 at 5:40 AM

      Thanks again Mr Saadi

      Delete
  2. UnknownApril 21, 2015 at 8:29 AM

    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. Mahmoud M. AlSaadiApril 21, 2015 at 1:09 PM

      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
  3. UnknownApril 23, 2015 at 9:34 AM

    Thanks Again

    ReplyDelete
  4. JohnMay 2, 2019 at 6:49 AM

    any suggestions if I don't have SE30300 turned on?

    ReplyDelete
    Replies
    1. Mahmoud M. AlSaadiJuly 28, 2019 at 5:44 AM

      Yes,
      You need to run the inventory reset tool in order to rebuild the HITB table.

      Contact me on my primary email for further assistance MahmoodSaadi@Live.com

      Best Regards,
      Mahmoud M. ALSaadi

      Delete