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.
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>
update INVOICES set shipped='T' where status=8 and order_no in ('SO_1001','SO_1002','SO_1003');
3. Run the script below to recalculate associated values:
-- Update Q_ON_RESER (SO Column)
set Q_ON_RESER = isnull(QTY,0)
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) ))
PENDQTY= case WHEN D.ACTUAL_QTY > D.COMMIT_QTY
THEN D.ACTUAL_QTY ELSE COMMIT_QTY
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)
where Items.ITEMNO = A.ITEMNO and
INVENTORED = 'T'
update ITEMS set Q_ON_RESER = 0
where Q_ON_RESER is null
Confirm that the Sales Orders are correctly closed by searching for the Sales Order using the SO#.
Please sign in to leave a comment.