Invoice stuck in workflow - Financial updating quantity must have same sign in the posting unit and the stock unit.
https://endsupport.endclothing.com/a/tickets/30963?current_tab=details
Microsoft SQL
DECLARE @INVOICENUM AS NVARCHAR(20) = 'INV450' --INVOICE NUMBER
DECLARE @DATAAREAID AS NVARCHAR(20) = 'USMF' --DATAAREAID
-- Step 0) Check invoice status
select RECID, REQUESTSTATUS, PARMJOBSTATUS from VENDINVOICEINFOTABLE where num = @INVOICENUM and DATAAREAID = @DATAAREAID
-- Step 1) Check invoice data
---- a. check invoice lines
select line.RECID,line.ITEMID,line.RECEIVENOW,line.INVENTNOW from VENDINVOICEINFOLINE line
inner join VENDINVOICEINFOTABLE header
on line.TABLEREFID = header.TABLEREFID
and line.PARTITION = header.PARTITION
and line.DATAAREAID = header.DATAAREAID
where header.num = @INVOICENUM and
header.DATAAREAID = @DATAAREAID and
line.INVENTNOW <> line.RECEIVENOW
---- b. check invoice sub lines
select RECID,RECEIVENOW,INVENTNOW from VENDINVOICEINFOSUBLINE where LINEREFRECID IN (line.RECID in step 1) -> a)
- Step 2) check items
---- a. check if the item is non-stocked item
select b.STOCKEDPRODUCT from InventModelGroupItem a
join INVENTMODELGROUP b
on a.MODELGROUPID = b.MODELGROUPID
where itemid in (line.ITEMID in step 1) and ITEMDATAAREAID = @DATAAREAID and b.DATAAREAID = @DATAAREAID
---- b. check if the item has same unit for purchase and inventory
select b.SYMBOL as FromSymbol,factor,d.SYMBOL as ToSymbow,c.ITEMID from UnitOfMeasureConversion a
join UnitOfMeasure b
on a.FROMUNITOFMEASURE = b.RECID
join UnitOfMeasure d
on a.TOUNITOFMEASURE = d.RECID
join INVENTTABLE c
on c.PRODUCT = a.PRODUCT
where c.DATAAREAID = @DATAAREAID and c.itemid in (line.ITEMID in step 1)
-- Step 4) Check if cx uses workflow to post invoice
SELECT a.RECID,b.RECID FROM WORKFLOWTRACKINGSTATUSTABLE a
inner join VENDINVOICEINFOTABLE b
on a.CONTEXTRECID = b.RECID
and a.PARTITION = b.PARTITION
WHERE b.NUM = @INVOICENUM
and b.DATAAREAID = @DATAAREAID
and a.TRACKINGSTATUS > 2
UPDATE VENDINVOICEINFOLINE SET INVENTNOW = RECEIVENOW WHERE RECID IN (line.RECID in step 1 -> a);
UPDATE VENDINVOICEINFOSUBLINE SET INVENTNOW = RECEIVENOW WHERE RECID IN (RECID in step 1 -> b);go to step 4)
UPDATE VENDINVOICEINFOTABLE SET REQUESTSTATUS = 0 WHERE RECID IN (b.RECID in step 4);
UPDATE WORKFLOWTRACKINGSTATUSTABLE SET TRACKINGSTATUS = 2 WHERE RECID IN (a.RECID in step 4);