Mismatched Inventory Quantities

Sometime you face difficulties in your inventories where your quantities does not match between applications forms, for example the on hand quantity does not match the historical stock status report or the item stock inquiry, and this is really annoying as the inventory reconciliation utility does not fix this and the resolution for this is a nightmare.

Reason behind such an issue might be a packets loss during the transaction posting where the system inserted the transactions in some tables and did not insert into the other, for instance a normal inventory transaction posting affect IV00102 table to adjust quantity, insert records into IV30300 for history and affect IV10200 and IV10201, all these tables must have the same quantity in normal cases, but in some cases you might have differences.

To fix this issue you will need to follow how Dynamics GP works, as a start we know that the reconciliation utility for the inventory items reconciles the quantity of IV00102 based on the difference between Quantity Received and Quantity Sold of IV10200 table, therefore after performing “Reconcile” for your inventories you can be sure that IV00102 is matched to IV10200.

Now the challenge is how to reconcile IV30300 to IV10200, first of all you will need to identify differences, I have created the below script that generate differences in quantity for all your items per each site between IV10200 and IV30300:

SELECT ITEMNMBR                                       AS ITEMNUMBER, 
       LOCNCODE                                       AS LOCATIONCODE, 
       Sum(TRXQTY * QTYBSUOM)                         AS QTYIV30300, 
       (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS QTYIV10200, 
       Sum(TRXQTY * QTYBSUOM) - (SELECT Sum(QTYRECVD - QTYSOLD) FROM   IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE) AS DIFF

FROM   (
--Correct IV30300 To Include Transfers
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST, 
                      TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE <> 3)
UNION ALL
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY * - 1 AS Expr1, UNITCOST, EXTDCOST, 
                      TRXLOCTN AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE = 3)
UNION ALL
SELECT     TRXSORCE, DOCTYPE, DOCNUMBR, DOCDATE, HSTMODUL, CUSTNMBR, ITEMNMBR, UOFM, TRXQTY, UNITCOST, EXTDCOST, 
                      TRNSTLOC AS LOCNCODE, QTYBSUOM, TRFQTYTY, TRTQTYTY
FROM         dbo.IV30300 WHERE     (DOCTYPE = 3)
) AS  IV30300MODIFIED
WHERE  IV30300MODIFIED.ITEMNMBR IN 

--Select Inventory Items with Type "Sales Invetory" Only
(SELECT ITEMNMBR FROM IV00101 WHERE (ITEMTYPE = 1) )

GROUP  BY ITEMNMBR, 
          LOCNCODE 

HAVING
Sum(TRXQTY * QTYBSUOM) <> (SELECT Sum(QTYRECVD - QTYSOLD) FROM IV10200 WHERE  IV10200.ITEMNMBR = IV30300MODIFIED.ITEMNMBR AND TRXLOCTN = IV30300MODIFIED.LOCNCODE)

For the time being you had a list of variances, you will need to identify what figures are really matches your physical, if it was the IV30300, then you will need to create an adjustment using Inventory Transaction Entry to correct IV10200 to match IV30300 and then delete the record from the IV30300, by this the IV10200 will be affected and IV30300 will not. Otherwise if the IV10200 is correct and the issue is in IV30300 then you will need to create a database adjustment in IV30300 to correct the difference and match your IV30300 to the IV10200.

I know that this is not an easy process to be done but I can assure you that above steps can save you several weeks of researches specially if your inventories are loaded with items.

Hope that this helps.