Dynamics GP - How to reset all Average Costed Inventory Costs at a specific Historical Date

Adjust all stock out at that date to ensure your HITB reads 0

--Run this to identify all of the cost rows that need to be updated, change the date accordingly.
--copy the UpdOnHnd column, and run the scripts to update each row.

SELECT iv10200.qtyonhnd, 
       iv10200.itemnmbr, 
       iv10200.trxloctn, 
       iv10200.daterecd, 
       iv10200.rctseqnm, 
       iv10200.dex_row_id, 
       iv10200.unitcost, 
       'update iv10200 set qtyonhnd = 0 where dex_row_id = ' 
       + Cast(iv10200.dex_row_id AS VARCHAR(50)) AS UpdOnHnd, 
       'delete from see30303 where ITEMNMBR = ' 
       + '''' 
       + Rtrim(Cast(iv10200.itemnmbr AS VARCHAR(50))) 
       + ''''                                    AS DelHITB 
FROM   iv10200 
       INNER JOIN (SELECT itemnmbr, 
                          Max(expr1) AS MaxSeq 
                   FROM   (SELECT itemnmbr, 
                                  Max(rctseqnm) AS Expr1, 
                                  daterecd 
                           FROM   iv10200 AS IV10200_1 
                           GROUP  BY itemnmbr, 
                                     daterecd 
                          --                          HAVING ( daterecd <= CONVERT(DATETIME,  
                          --                                               '2014-06-30 00:00:00'  
                          --                                                , 102) ) 
                          ) AS atdate 
                   GROUP  BY itemnmbr) AS MaxLine 
               ON iv10200.itemnmbr = MaxLine.itemnmbr 
                  AND iv10200.rctseqnm = MaxLine.maxseq 
--WHERE  ( iv10200.qtyonhnd <> 0 )  



--If you HITB is showing 0 quantity, but +ve and -ve values, and you do not need the history
--Adjust the stock to 0
--Delete all HITB records for the item (Copy the DelHITB column)
delete from see30303 where ITEMNMBR = 'I0001'