Correcting Stock Movement Discrepancies


Stock/inventory discrepancies can be corrected by executing the data fix script provided below when inventory reports are not reflecting the accurate position in regard to stock movement.


The stock discrepancy data fix script is provided below (see script comments for execution steps):

Note: You need to run this procedure for each item that has an item history issue. The script cannot update multiple items in bulk as it needs to take care of serialized items as well.
by Raditya Perdhevi
1. Update @startFrom to the date where the calculation is started.
2. Update @item_number with the item required
3. run the script and make sure that the first result is correct in the inventory history
4. set the @runUpdate as 1
5. run the script again to make actual update

set nocount on

declare @startFrom datetime
declare @item_number varchar(50)
declare @runUpdate int

set @startFrom = '2020-04-01'
set @item_number =  '006R03514'
set @runUpdate = 0

declare itemRun cursor for
select ItemNo, Qty_Stk from items where active = 'T' and inventored = 'T' and SERIALIZE = 'F'
and itemno = @item_number

create table #tempLog
  UniqueID int,
  ItemNo varchar(15),
  old_qty_stk decimal(18,6),
  new_qty_stk decimal(18,6)

create table #updatedItem
  ItemNo varchar(15),
  old_qty decimal(18,6),
  new_qty decimal(18,6)

Declare @ItemNo as varchar(15)
declare @ItemQty_Stk as int;
declare @count as int;

set @count = 0;

open itemRun
fetch next from itemRun
into @ItemNo, @ItemQty_Stk;

--print 'select invhist where itemno in (';
while @@FETCH_STATUS = 0
declare run cursor for
select UNIQUE_ID, QTY,isnull(m.CONV_FACTOR,1) as conv, InvHist.STK_QTY, QTY_STK, COST, AVG_COST, type
from InvHist left join MEASURE_FACTOR m on InvHist.DOC_MEAS = m.MEAS_CODE and Invhist.STK_MEAS = m.CONV_MEAS_CODE
ItemNo = @ItemNo
and db_server_date >= @startFrom
order by  unique_ID asc, db_server_date asc, invhist.Doc_type asc

declare @ctr as decimal(18,6)
set @ctr = 0

select @ctr = isnull(QTY_STK,0) from invhist where unique_id = (
select max(unique_id) from invhist where db_server_date< @startFrom and itemNo = @ItemNo
Declare @UniqueID int
declare @Qty decimal(18,6)
declare @Conv decimal(28,16)
declare @Stk_Qty decimal(18,6)
declare @Qty_Stk decimal(18,6)
declare @Cost decimal(18,6)
declare @Avg_Cost decimal(18,6)
declare @type int
declare @problem int
declare @running_avg decimal(18,6)
set @problem = 0;

open run
Fetch next from run
into @UniqueID, @Qty, @Conv, @Stk_Qty, @Qty_Stk, @Cost, @Avg_Cost, @type;

--print 'UniqueID,Qty,Conv,Stk_Qty,Qty_Stk,Cost,Avg_Cost,ctr'
while @@FETCH_STATUS = 0
if(@type is null)
set @ctr =  @ctr + @Stk_Qty
end else
  if((@type = 1)or(@type=2))
-- just calculate the stock qty for qty adjustment
set @ctr =  @ctr + @Stk_Qty ;
--print Convert(varchar(30),@UniqueID ) +','+ Convert(varchar(30),@Qty) +','+ Convert(varchar(30),@Conv) +','+ Convert(varchar(30),@Stk_Qty) +','+ Convert(varchar(30),@Qty_Stk)+','+ Convert(varchar(30),@Cost) +','+ Convert(varchar(30),@Avg_Cost)+',' +Convert(varchar(30), @ctr)

        if(@ctr <> @Qty_Stk)
          --print Convert(varchar(30),@UniqueID) +' : updated from ' + Convert(varchar(30),@Qty_Stk) + ' to ' + Convert(varchar(30),@Ctr);
          insert into #tempLog values(@UniqueID, @ItemNo, @Qty_Stk, @ctr);
  if(@runUpdate = 1)
    update INVHIST set QTY_STK = @ctr where UNIQUE_ID = @UniqueID;    -- remove the -- character to actually run the update
  set @count = @count + 1;
          set @problem = 1;

-- we can recalculate the STK_QTY As well using Qty * Conv if necessary
Fetch next from run
into @UniqueID, @Qty, @Conv, @Stk_Qty, @Qty_Stk, @Cost, @Avg_Cost, @type;

close run;
deallocate run;  
declare @item_qty decimal(18,2);
select @item_qty = qty_stk from ITEMS where itemno = @ItemNo;
if(@item_qty <> @ctr)
  insert into #updatedItem values(@ItemNo, @item_qty, @ctr);
  --update Items set qty_stk = @ctr where itemno = @ItemNo;
  fetch next from itemRun
  into @ItemNo, @ItemQty_Stk;

  --if((@@FETCH_STATUS =0)and(@problem = 1))
  --  print ',';
--print ')'
print Convert(varchar(30),@count) + ' records';

close itemRun

select * from #tempLog;
select * from #updatedItem;

drop table #tempLog;
drop table #updatedItem;
deallocate itemRun

Contact support if you require assistance in executing this data fix script.

Note: For inventory mismatch errors on the same item across different screens see How to correct inventory mismatch errors

Back to top



Please sign in to leave a comment.