Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Wednesday, March 19, 2014

Reconciling Quantity on Hand – SQL Script

 

In a previous post, purchase receipt layers work and details SQL scrip was provided as part of the inventory reconciliation process; Inventory Reconciliation - Purchase Receipt Work and Details. This is a prerequisite for the next step illustrated below.

In this post, SQL script for the correct quantity on hand is provided to reconcile transaction balance versus master balance, as shown below;

Untitled

Helping Note !

  • Transaction Balance: as calculated from purchase receipt layers against purchase receipt layer details (in versus out transactions)
  • Master Balance: as retrieved from quantity master table and displayed on item quantities inquiry.

 

Tables Included:

·         IV00102 | Item Quantity Master

·         IV10200 | Purchase Receipt Layers

 

SELECT  TRX_BALANCE.ITEMNMBR AS ItemNumber ,

        TRXLOCTN AS Location ,

        BALANCE AS TRX_BALNACE ,

        QTYONHND AS Master_Balnace ,

        ATYALLOC AS Master_AllocatedQuantity ,

        AvailableQuantity ,

        BALANCE - QTYONHND AS Variance

FROM    ( SELECT    ITEMNMBR ,

                    TRXLOCTN ,

                    SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE

          FROM      dbo.IV10200

          GROUP BY  ITEMNMBR ,

                    TRXLOCTN

        ) AS TRX_BALANCE

        LEFT OUTER JOIN ( SELECT    ITEMNMBR ,

                                    LOCNCODE ,

                                    QTYONHND ,

                                    ATYALLOC ,

                                    QTYONHND - ATYALLOC AS AvailableQuantity

                          FROM      dbo.IV00102

                          WHERE     RCRDTYPE = 2

                        ) AS Master_Balance ON TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR

                                               AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE

WHERE   BALANCE - QTYONHND <> 0

 

 

Best Regards,
Mahmoud M. AlSaadi

7 comments:

  1. Hi,

    Whether this query take care of AA beginning balance as well?

    Thanks
    Prakash

    ReplyDelete
    Replies
    1. This has nothing to do with the Analytical Accounting, it has to do with inventory quantity balances.
      You may provide your inquiry to provide a relevant assistance.

      Best Regards,

      Delete
    2. HI
      can you explain me if we want out like given format output
      Opning Purchase Issue Closing
      ProductId StoreId Date OQty ORate OValue IQty IRate IValue OQty ORate OValue Qty Rate Value

      Delete
  2. Love your scripts Mahmood. Thanks for your efforts.

    ReplyDelete
    Replies
    1. You are more than welcome Mr. Robert,

      Best Regards,

      Delete
  3. Hi,
    Mahmood you missed one thing in your query, you have to add this condition:
    QTYTYPE =1
    because you are comparing to the qty on hand, so you must not include all types, only type 1 included.

    Thanks,

    ReplyDelete
    Replies
    1. Sal Suh
      You are definitely right, the other quantity types are excluded such as quantity returned, damaged ...etc.

      It is a good idea to include them under a different category matching. That will be taken into consideration.

      Thanks,

      Delete