Microsoft Dynamics GP throw SQL : Find differences between Bin QTY & all site QTYs

To be sure that your quantity is correct. You need to check from time to time if there are any differences between quantities for the same item in multiple tables.

Item Quantity Master (IV00102)

Item Site Bin Master (IV00112)

This case applies only if you are using (Enable Multiple Bins)

By using a SQL query 

---------------------------------------------

SELECT *, ( All_Available - BIN_All_Available ) [Diff] FROM ( SELECT RTRIM(ITEMNMBR) ITEMNMBR, SUM(QTYONHND) QTYONHND, SUM(ATYALLOC) ATYALLOC, SUM(QTYONHND - ATYALLOC) SUM_Available, ( SELECT (QTYONHND - ATYALLOC) FROM IV00102 A WHERE A.ITEMNMBR = IV00102.ITEMNMBR AND LOCNCODE = '' ) [All_Available], ISNULL(( SELECT SUM(QUANTITY - ATYALLOC) FROM IV00112 B WHERE B.ITEMNMBR = IV00102.ITEMNMBR AND B.QTYTYPE = 1), 0) [BIN_All_Available] FROM IV00102 WHERE LOCNCODE = '' GROUP BY ITEMNMBR ) AS R WHERE [All_Available] <> [BIN_All_Available]

---------------------------------------------

The results will be like this :

Then you need to correct these items by applying item reconcile.

by Reconcile Inventory Quantities window.

 

The press Process button.

If there is anything to be corrected it will be done and it will give a report of it.