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.
· 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 ,
BALANCE - QTYONHND AS Variance
FROM ( SELECT ITEMNMBR ,
SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE
GROUP BY ITEMNMBR ,
) AS TRX_BALANCE
LEFT OUTER JOIN ( SELECT ITEMNMBR ,
QTYONHND - ATYALLOC AS AvailableQuantity
WHERE RCRDTYPE = 2
) AS Master_Balance ON TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR
AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE
WHERE BALANCE - QTYONHND <> 0
Mahmoud M. AlSaadi