Script to Cancel Order Line and Workflow in Oracle Apps 11i

This script will cancel order lines in Oracle 11i OM. It will also cancel or abort the corresponding OEOL (order line) workflow. Take caution to run the select statement of the cursor standalone prior to running the script to verify the record set to be cancelled.

DECLARE
   v_itemkey   VARCHAR2 (240); –This is the line_id
   v_ctr       NUMBER := 0;

   CURSOR wf
   IS
      SELECT wi.item_type,
             wi.ITEM_KEY,
             wi.BEGIN_DATE,
             wi.end_date,
             ooh.order_number,
             ooh.OPEN_FLAG hdr_open_flag,
             ooh.CANCELLED_FLAG hdr_can_flag,
             ooh.header_id,
             ool.line_id,
             ooh.org_id,
             ool.FLOW_STATUS_CODE,
             ool.CANCELLED_FLAG,
             ool.OPEN_FLAG,
             ool.line_number || ‘.’ || ool.shipment_number line_no
        FROM oe_order_headers_all ooh,
             oe_order_lines_all ool,
             wf_item_attribute_values wiav,
             wf_items wi
       WHERE wi.item_type = ‘OEOL’
             AND wi.end_date IS NULL
             AND wiav.item_type = wi.ITEM_type
             AND wiav.ITEM_KEY = wi.ITEM_KEY
             AND wiav.name = ‘ORG_ID’
             AND wiav.number_value = ooh.org_id
             AND ool.LINE_ID = wi.ITEM_KEY
             AND ooh.HEADER_ID = ool.HEADER_ID
             AND wi.item_key IN
             –Modify this section to get the order lines desired
               (–No updates in 1 yr
               SELECT ol.line_id
                 FROM oe_order_headers_all oh,
                      oe_order_lines_all ol,
                      mtl_parameters p,
                      hz_parties hp,
                      hz_cust_accounts hca
                WHERE oh.header_id = ol.header_id
                AND ol.flow_status_code NOT IN
                     (‘ENTERED’,‘CANCELLED’, ‘CLOSED’)
                AND NVL (ol.shipped_quantity, 0) = 0
                AND p.organization_id = ol.ship_from_org_id
                AND hca.cust_account_id(+) = oh.sold_to_org_id
                AND hp.party_id(+) = hca.party_id
                AND f.line_detail_id(+) = ol.line_id
                AND TRUNC(ol.creation_date) <= TRUNC(SYSDATE-365)
                AND TRUNC(ol.last_update_date) <= TRUNC(SYSDATE-365)
                );


BEGIN
   DBMS_OUTPUT.put_line (‘Order No~Line No~WF Key~Status’);

   FOR wf_rec IN wf
   LOOP
      –Cancels the work flow of the line
      wf_engine.ABORTPROCESS (wf_rec.item_type, wf_rec.item_key);
      COMMIT;

      –Cancels the order line
      UPDATE oe_order_lines_all
         SET fulfilled_quantity = NULL,
             fulfilled_flag = NULL,
             fulfillment_date = NULL,
             cancelled_quantity = ordered_quantity,
             ordered_quantity = 0,
             pricing_quantity = 0,
             schedule_ship_date = NULL,
             visible_demand_flag = NULL,
             schedule_arrival_date = NULL,
             schedule_status_code = NULL,
             cancelled_flag = ‘Y’,
             open_flag = ‘N’,
             flow_status_code = ‘CANCELLED’,
             calculate_price_flag = ‘N’
       WHERE line_id = wf_rec.line_id;

      COMMIT;
      v_ctr := v_ctr + 1;
      DBMS_OUTPUT.put_line(   wf_rec.order_number
                           || ‘~’
                           || wf_rec.line_no
                           || ‘~’
                           || wf_rec.item_key
                           || ‘~CANCELLED’);
   END LOOP;

   DBMS_OUTPUT.put_line (‘No of lines ‘ || v_ctr);
END;
/