Inventory Reset – Average Item receipt QTY on hand does not equal IV QTY on hand.

  As a part of the inventory reset utility you might get an issue with “Run Data Checks” step, that the Average Item Receipt Quantity On Hand (Table IV10200) does not equal Inventory Quantity On Hand (Table IV00102), this is really confusing specially after doing a full inventory reconcile as a part of the inventory reset process. While investigating this I found indeed that Quantity at IV00102 does not match the IV10200 quantity and the Inventory Reconcile Process does not cover this part, where I had to find an alternative method to proceed. On of the community users did face this issue and posted this question on one of the community portal and got an answer from WAQAS who went through the stored procedures of the reset tool and extracted a query that detects variances between IV10200 and IV00102, I have used this query to create the below cursor to collect and fix differences automatically: DECLARE @ITEMNUMBER VARCHAR (500) DECLARE @DEXROWID BIGINT   DECLARE QTYFIX CURSOR FOR SELECT A.DEX_ROW_ID, A.ITEMNMBR FROM IV10200 A INNER JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR INNER JOIN IV00102 C ON A.ITEMNMBR = C.ITEMNMBR WHERE B.VCTNMTHD = 3 AND A.QTYTYPE = 1 AND C.LOCNCODE = ” AND A.QTYONHND C.QTYONHND AND EXISTS ( SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR AND VCTNMTHD 0 GROUP BY ITEMNMBR HAVING MAX (DATERECD) = A.DATERECD) AND EXISTS ( SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR =B.ITEMNMBR AND DATERECD = A.DATERECD AND VCTNMTHD 0 GROUP BY ITEMNMBR, DATERECD HAVING MAX (RCTSEQNM) = A.RCTSEQNM) AND EXISTS ( SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR AND DATERECD = A.DATERECD AND RCTSEQNM = A.RCTSEQNM AND VCTNMTHD 0 GROUP BY ITEMNMBR, DATERECD, RCTSEQNM HAVING MAX (DEX_ROW_ID) = A.DEX_ROW_ID)   OPEN QTYFIX FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER WHILE @@FETCH_STATUS = 0 BEGIN UPDATE IV10200 SET QTYONHND=( SELECT QTYONHND FROM IV00102 WHERE ITEMNMBR=@ITEMNUMBER AND RCRDTYPE= ’1′ ) WHERE DEX_ROW_ID= @DEXROWID FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER END CLOSE QTYFIX DEALLOCATE QTYFIX Enjoy! Regards, — Mohammad R

Taken from:
Inventory Reset – Average Item receipt QTY on hand does not equal IV QTY on hand.