Dynamics GP - Voided Sales transactions appear in Historical Stock Status Report

The Historical Stock Status Report and the Item Stock Inquiry read their data from the IV30300 table.

Whenever a transaction is voided, the corresponding record is supposed to be removed from the IV30300 table.

If records are showing on your Historical Stock Status that should not be there, the offending records need to be removed.

Look for any doc number and item number in SOP30300 that is not in IV30300
Also look for any Doc numbers in IV30300 with multiple Trxsorce values. Chances are, the lowest trxsorce values are supposed to be removed.

--delete voided SOP30300 stuck in IV30300
--------------------------------------------------------------
delete from iv30300 where dex_row_id in (
select distinct dex_row_id from(
SELECT     IV30300.TRXSORCE, IV30300.DOCTYPE, IV30300.DOCNUMBR, IV30300.DOCDATE, IV30300.HSTMODUL, IV30300.CUSTNMBR, IV30300.ITEMNMBR, IV30300.LNSEQNBR, IV30300.UOFM,
                      IV30300.TRXQTY, IV30300.UNITCOST, IV30300.EXTDCOST, IV30300.TRXLOCTN, IV30300.TRNSTLOC, IV30300.TRFQTYTY, IV30300.TRTQTYTY, IV30300.IVIVINDX, IV30300.IVIVOFIX,
                      IV30300.DECPLCUR, IV30300.DECPLQTY, IV30300.QTYBSUOM, IV30300.Reason_Code, IV30300.DEX_ROW_ID, SOP30200.VOIDSTTS
FROM         SOP30200 INNER JOIN
                      SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV30300 ON SOP30300.SOPNUMBE = IV30300.DOCNUMBR AND SOP30300.ITEMNMBR = IV30300.ITEMNMBR
WHERE     (SOP30200.VOIDSTTS = 1)
) as a)


--Delete IV30300 that have no corresponding SOP30300 lines
-------------------------------------------------------------------

delete from iv30300 where dex_row_id in (
SELECT    IV30300.DEX_ROW_ID
FROM         (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, COUNT(ITEMNMBR) AS Expr1, DOCTYPE, ITEMNMBR, MIN(TRXSORCE) AS Expr2
                       FROM          (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, DOCTYPE, TRXSORCE, ITEMNMBR
                                               FROM          IV30300 AS IV30300_1
                                               GROUP BY DOCDATE, ITEMNMBR, DOCNUMBR, DOCTYPE, TRXSORCE
                                               ORDER BY DOCNUMBR) AS derivedtbl_1
                       GROUP BY DOCNUMBR, DOCTYPE, ITEMNMBR
                       HAVING      (DOCTYPE = 6) AND (COUNT(ITEMNMBR) > 1)
                       ORDER BY DOCNUMBR, ITEMNMBR) AS b INNER JOIN
                      IV30300 ON b.DOCTYPE = IV30300.DOCTYPE AND b.DOCNUMBR = IV30300.DOCNUMBR AND b.ITEMNMBR = IV30300.ITEMNMBR AND b.Expr2 = IV30300.TRXSORCE
                      )