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.

Solution

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

1. Backup existing data:

-- 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> 

Example:

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) 
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