銷貨後訂單還是BOOKED的處理

/*  SELECT invoice_interface_status_code  */
/*  FROM   oe_order_lines Line  */
/*  WHERE Line.line_id = 2690231  */
/*         AND invoice_interface_status_code = 'RFR-PENDING'  */

/*

檢查還有哪些異常的資料

SELECT h.order_number,l.line_number||'.'||l.shipment_number line_no,l.ship_from_org_id,l.flow_status_code, l.*
FROM wsh_delivery_details a,
             oe_order_lines_all l,
    oe_order_headers_all h
WHERE h.header_id=l.header_id
AND a.source_line_id=l.line_id
AND a.RELEASED_STATUS='C'
AND l.FLOW_STATUS_CODE<>'CLOSED'
AND l.org_id=801   
AND l.LAST_UPDATE_DATE

 

*/

DECLARE
l_result_out     VARCHAR2(30);
l_return_status  VARCHAR2(30);

CURSOR c1 IS
  SELECT line_id
  FROM OE_ORDER_LINES_ALL
WHERE line_id=6611817;
    --line_id in (2704842,2750637);
  --WHERE FLOW_STATUS_CODE = 'SHIPPED';

 
BEGIN
   FOR ii IN c1 LOOP
  
          UPDATE OE_ORDER_LINES_ALL
          SET    INVOICED_QUANTITY = NULL,
                 INVOICE_INTERFACE_STATUS_CODE = NULL,
                 OPEN_FLAG='N',
              FLOW_STATUS_CODE = 'CANCELLED',
                 LAST_UPDATED_BY = 1174,
                 LAST_UPDATE_DATE = SYSDATE
          WHERE  LINE_ID =:line_id;
     
          DELETE FROM RA_INTERFACE_LINES_ALL
          WHERE  INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(:line_id);
     
          DELETE FROM RA_INTERFACE_SALESCREDITS_ALL
          WHERE  INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(:line_id);
   
          COMMIT;
     
     
          Oe_Invoice_Pub.Interface_Line(ii.line_id,'OEOL',l_result_out,l_return_status);
          DBMS_OUTPUT.PUT_LINE(TO_CHAR(ii.line_id) ||'+'||l_result_out || '+' || l_return_status);
   
   
          Oe_Invoice_Pub.Interface_Line(6611817,'OEOL',l_result_out,l_return_status);  --代LINE_ID
    
          DBMS_OUTPUT.PUT_LINE(TO_CHAR(6611817) ||'+'||l_result_out || '+' || l_return_status); --代LINE_ID
      
          UPDATE OE_ORDER_LINES_ALL
          SET    OPEN_FLAG='N',
                 FLOW_STATUS_CODE='CLOSED'
          WHERE  LINE_ID = :line_id;
         
         
   UPDATE RA_INTERFACE_LINES_ALL RI
      SET TRX_NUMBER = (SELECT DISTINCT DELIVERY_ID
                          FROM WSH_DELIVERABLES_V WSH
          WHERE WSH.SOURCE_HEADER_NUMBER = RI.SALES_ORDER
                           AND WSH.SOURCE_LINE_ID = RI.INTERFACE_LINE_ATTRIBUTE6
            )
    WHERE ORG_ID IN (801,543)
    AND  RI.INTERFACE_LINE_ATTRIBUTE6=:line_id 
      AND TRX_NUMBER IS NULL;
     
   UPDATE OE_ORDER_LINES_ALL ol
      SET ol.shipped_quantity=ol.ordered_quantity
       WHERE ol.line_id=:line_id
         AND ol.shipped_quantity IS NULL;
     
        COMMIT; 
  
    END LOOP;
   
 END;

 DELETE MTL_RESERVATIONS
 WHERE
 RESERVATION_ID=18431033