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. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com