web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / Reconciling Quantity on Han...

Reconciling Quantity on Hand – SQL Script

Views (2232)
Mahmoud Saadi Profile Picture Mahmoud Saadi 32,738

 

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


This was originally posted here.

Comments

*This post is locked for comments