Hi,
Please use this script to cancel the work and then run a concistency check
please not that this script has been made as a standard under warehouse management > periodic > Clean up > cancel work
Regards,
Mehdi
declare @partition bigint = 5637144576
declare @dataareaid nvarchar(4) = 'AAA'
declare @workid nvarchar(20) = 'USMF-088395'
-- Update WHSLOADLINE.WORKCREATEDQTY
;with CreatedQtys as
(
select wl.LOADLINEREFRECID, sum(wl.INVENTQTYWORK) as CREATEDQTY, wl.DATAAREAID, wl.PARTITION from WHSWORKLINE wl
where wl.PARTITION = @partition and wl.DATAAREAID = @dataareaid and wl.WORKID = @workid
and wl.WORKTYPE = 1/*pick*/
and wl.LINENUM < (select min(LINENUM) from WHSWORKLINE where DATAAREAID = wl.DATAAREAID and [PARTITION] = wl.PARTITION and WORKID = wl.WORKID and WORKTYPE = 2/*put*/)
group by wl.PARTITION, wl.DATAAREAID, wl.LOADLINEREFRECID
)
update ll
set ll.WORKCREATEDQTY = ll.WORKCREATEDQTY - q.CREATEDQTY
from WHSLOADLINE ll
join CreatedQtys q on q.LOADLINEREFRECID = ll.RECID and q.DATAAREAID = ll.DATAAREAID and q.PARTITION = ll.PARTITION
-- List items that need on-hand consistency check
;select
t.ITEMID
from WHSWORKINVENTTRANS wt
join INVENTTRANSORIGIN o on (o.INVENTTRANSID = wt.INVENTTRANSIDFROM or o.INVENTTRANSID = wt.INVENTTRANSIDTO) and o.DATAAREAID = wt.DATAAREAID and o.PARTITION = wt.PARTITION
join INVENTTRANS t on t.INVENTTRANSORIGIN = o.RECID and t.DATAAREAID = o.DATAAREAID and t.PARTITION = o.PARTITION
where wt.WORKID = @workid and wt.DATAAREAID = @dataareaid and wt.PARTITION = @partition
group by t.ITEMID
-- Delete inventory transactions
delete t
from WHSWORKINVENTTRANS wt
join INVENTTRANSORIGIN o on (o.INVENTTRANSID = wt.INVENTTRANSIDFROM or o.INVENTTRANSID = wt.INVENTTRANSIDTO) and o.DATAAREAID = wt.DATAAREAID and o.PARTITION = wt.PARTITION
join INVENTTRANS t on t.INVENTTRANSORIGIN = o.RECID and t.DATAAREAID = o.DATAAREAID and t.PARTITION = o.PARTITION
where wt.WORKID = @workid and wt.DATAAREAID = @dataareaid and wt.PARTITION = @partition
-- Delete InventTransOrigin
delete o
from WHSWORKINVENTTRANS wt
join INVENTTRANSORIGIN o on (o.INVENTTRANSID = wt.INVENTTRANSIDFROM or o.INVENTTRANSID = wt.INVENTTRANSIDTO) and o.DATAAREAID = wt.DATAAREAID and o.PARTITION = wt.PARTITION
where wt.WORKID = @workid and wt.DATAAREAID = @dataareaid and wt.PARTITION = @partition
-- Delete WHSWorkInventTrans
delete wt
from WHSWORKINVENTTRANS wt
where wt.WORKID = @workid and wt.DATAAREAID = @dataareaid and wt.PARTITION = @partition
-- Delete WHSDimTracking
delete dt
from WHSDIMTRACKING dt
where dt.WORKID = @workid and dt.DATAAREAID = @dataareaid and dt.PARTITION = @partition
-- Update WHSWorkLineStatus to cancelled
update wl
set wl.WORKSTATUS = 5 -- Cancelled
from WHSWORKLINE wl
where wl.WORKID = @workid and wl.DATAAREAID = @dataareaid and wl.PARTITION = @partition
-- Update WHSWorkTable Status to cancelled
update w
set w.WORKSTATUS = 5 -- Cancelled
from WHSWORKTABLE w
where w.WORKID = @workid and w.DATAAREAID = @dataareaid and w.PARTITION = @partition
