Dynamics GP - How to reset all Average costed inventory costs. Zero out and reset stock using stock count entry.

Adjust out all stock
Adjust Stock accounts to zero or transfer balances to suspense accounts
Set the qtyonhnd for the last record for each item in the IV10200 table to zero to reset the cost calculations for the next transaction entered.

-------------------------------------------------

SELECT     IV10200.ITEMNMBR, IV10200.RCTSEQNM AS LRSNM, IV10200.DATERECD, IV10200.QTYONHND, IV10200.DEX_ROW_ID AS Lastentry
FROM         IV10200 INNER JOIN
                          (SELECT     IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM
                            FROM          IV10200 AS IV10200_2 INNER JOIN
                                                       (SELECT     ITEMNMBR, MAX(DATERECD) AS LDR
                                                         FROM          IV10200 AS IV10200_1
                                                         GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                            GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND IV10200.RCTSEQNM = LRSNM.LRSNM
------------------------------------------------
update iv10200 set qtyonhnd = 0 where dex_row_id in(
select lastentry from(
SELECT     IV10200.ITEMNMBR, IV10200.RCTSEQNM AS LRSNM, IV10200.DATERECD, IV10200.QTYONHND, IV10200.DEX_ROW_ID AS Lastentry
FROM         IV10200 INNER JOIN
                          (SELECT     IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM
                            FROM          IV10200 AS IV10200_2 INNER JOIN
                                                       (SELECT     ITEMNMBR, MAX(DATERECD) AS LDR
                                                         FROM          IV10200 AS IV10200_1
                                                         GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                            GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND IV10200.RCTSEQNM = LRSNM.LRSNM) as LastCost)
-------------------------------------------
--To see the last record that needs to be reset for each item
-------------------------------------------
select * from IV10200 where ITEMNMBR = 'myitemnum' order by daterecd,rctseqnm

------------------------------------------------

Adjust In all stock

Check the costs, check the gl balance

You can use the Stock count entry window to quickly gather all items, quantities and lot numbers to create the adjustment to remove all stock

Use the IV10300, IV10301, IV10302, IV10303 tables to gather the information you need to create a positive adjustment to put back in all stock at the correct cost


Create a stock count schedule for all items
Create a stock count entry

Backup the stock count entry tables
select * into iv10301_all from iv10301
select * into iv10302_all from iv10302

Update the stock count entry

Update iv10301 set VERIFIED = 1,VARIANCEQTY = capturedqty*-1, STCKSRLLTVRNC = CAPTUREDQTY * -1
Update iv10302 set VERIFIED = 1,VARIANCEQTY = capturedqty*-1

----
Use this to grab non-lot stock count data if required
select * from iv10301 where STCKCNTID = 'SB' and CAPTUREDQTY <> 0
----

Open stock count entry
Process stock count entry
Confirm variance accounts
Print GL TB
Post adjustment
Print GL TB
Run script to wipe out last stock cost

update iv10200 set qtyonhnd = 0 where dex_row_id in(

select lastentry from(

SELECT     ITEMNMBR, MAX(DEX_ROW_ID) AS Lastentry

FROM         IV10200

GROUP BY ITEMNMBR) as LastCost)

Start the stock count again
Run this to populate with the original amounts from the backup
Change the costs if necessary


--Update all count records
UPDATE    IV10301
SET              VERIFIED = 1, varianceQTY = iv10301_all.CAPTUREDQTY, COUNTEDQTY = iv10301_all.CAPTUREDQTY, STCKSRLLTVRNC = IV10301_all.CAPTUREDQTY, Stock_Serial_Lot_Count  = IV10301_all.CAPTUREDQTY
FROM         IV10301 INNER JOIN
                      iv10301_all ON IV10301.STCKCNTID = iv10301_all.STCKCNTID AND IV10301.ITEMNMBR = iv10301_all.ITEMNMBR AND IV10301.LOCNCODE = iv10301_all.LOCNCODE

--Create all lot entries from backups
insert into iv10302(STCKCNTID, ITEMNMBR, LOCNCODE,ITMTRKOP,SERLTNUM,SERIALSTATUS,QTYTYPE)
select stckcntid,ITEMNMBR, LOCNCODE,3,'20140201',1,1 from iv10301_all where varianceQTY <> 0                  


 --Update lot entries
 UPDATE    IV10302
SET              VERIFIED = 1, VARIANCEQTY = IV10301_all.CAPTUREDQTY, COUNTEDQTY = IV10301_all.CAPTUREDQTY, SERLTNUM = '20140201'
FROM         IV10302 INNER JOIN
                      iv10301_all ON IV10302.ITEMNMBR = iv10301_all.ITEMNMBR AND IV10302.LOCNCODE = iv10301_all.LOCNCODE AND IV10302.STCKCNTID = iv10301_all.STCKCNTID
               
           

Check the inventory adjustment, adjust costs as necessary