Wednesday, August 24, 2011

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

3 comments:

Julio Vasquez said...

Gran amigo..

Frank Hamelly said...

Thanks Mohammad, this helped me greatly today!

Frank

Constance Quigley said...

Very helpful!

Related Posts:

Related Posts with Thumbnails