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;

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