Closing a Batch of Back Orders

Overview

Everest does not provide a way to close a batch of Back Orders. Users are able to manually close Sales Order, which only works through closing one Sales Order at a time. This article provides a workaround to close Sales Orders in bulk through a database update.

Important: This action should not be generalized for other tasks and clients should are not encouraged to do it on their own without guidance from Everest through a support ticket.

Solution

The following steps should close a batch of Back Orders from the Everest database:

1. Backup existing data:

Note: The scripts below are using the ZD-ticketnumber-tablename naming convention for the backup tables. 
-- Backing up X_INVOIC table
select * into ZD276625_X_INVOIC
from X_INVOIC where STATUS = 8 and ORDER_NO in <list of SOs to be closed> select * from ZD276625_X_INVOIC --test if proper list of SOs was loaded
-- Backing up INVOICES table
select * into ZD276625_INVOICES from INVOICES where STATUS = 8 and ORDER_NO in <list of SOs to be closed> select * from ZD276625_INVOICES -- test if proper list of SOs was loaded

2. Update the required fields to close the Sales Orders: 

update X_INVOIC set ITEM_QTY = 0 
where status = 8 and ORDER_NO in <list of SOs to be closed>
 
update INVOICES set shipped ='T'
where status = 8 and order_no in <list of SOs to be closed>

3. Run the script below to recalculate associated values:

-- Update Q_ON_RESER (SO Column) 
update ITEMS
set Q_ON_RESER = isnull(QTY,0)
from ( 
select ITEMNO , SUM(PENDQTY) QTY , MAX(Q_ON_RESER) Q_ON_RESER 
from ITEMS LEFT OUTER JOIN 
( select ITEM_CODE,
PENDQTY = (ISNULL(STK_ITEM_QTY,0) - ISNULL(STK_QTY_SHIP,0)) 
from X_INVOIC X WITH(NOLOCK) ,INVOICES P WITH(NOLOCK) 
where X.STATUS = P.STATUS and
X.ORDER_NO = P.ORDER_NO and
P.SHIPPED = 'F' and
((X.STATUS = 11 and
ISNULL(STK_ITEM_QTY,0) < 0 and
ISNULL(STK_ITEM_QTY,0) < ISNULL(STK_QTY_SHIP,0) )  or
( X.STATUS = 8 AND ISNULL(STK_ITEM_QTY,0) > 0 AND
ISNULL(STK_ITEM_QTY,0) > ISNULL(STK_QTY_SHIP,0) )) 
UNION ALL 
select ITEM_CODE,
PENDQTY= case WHEN D.ACTUAL_QTY > D.COMMIT_QTY
THEN D.ACTUAL_QTY ELSE COMMIT_QTY
end 
from ASSEMBLY_WO_HEADER H WITH(NOLOCK),
ASSEMBLY_WO_DETAILS D WITH(NOLOCK) 
where DOC_STATUS IN (1,2) and
H.ASSEMBLY_WO_HEADER_ID = D.ASSEMBLY_WO_HEADER_ID and
(D.ACTUAL_QTY > 0 OR D.COMMIT_QTY > 0) 
) X ON ITEMNO = ITEM_CODE 
group BY ITEMNO 
having ISNULL(SUM(PENDQTY),0) <> ISNULL(MAX(Q_ON_RESER),0) 
) A
where Items.ITEMNO = A.ITEMNO and
INVENTORED = 'T' 

update ITEMS set Q_ON_RESER = 0
where Q_ON_RESER is null

Testing

Confirm that the Sales Orders are correctly closed by searching for the Sales Order using the SO#.

Back to top

Comments

0 comments

Please sign in to leave a comment.