Oracle eBS經驗分享

今天有整批修改schedule_ship_date 的需求  測了一整天...

以下是實做分享：

Use Order Interface Method to Update Schedule Date.
First Step: Change the Profile :OM: Authorized To Override ATP ="Yes"

Scecond Step:Run Insert Interface Sql

=>Sample Sql:

/* Formatted on 2008/11/19 16:21 (Formatter Plus v4.8.7) */
DECLARE
   l_header_rec   oe_headers_iface_all%ROWTYPE;
   l_line_rec     oe_lines_iface_all%ROWTYPE;

   CURSOR cur_h IS
      SELECT created_by
           , creation_date
           , last_update_date
           , last_update_login
           , last_updated_by
           , order_source_id
           , orig_sys_document_ref
           , org_id
           , header_id
           , order_number
           , sold_to_org_id
           , invoice_to_org_id
           , order_type_id
        FROM oe_order_headers_all
       WHERE order_number = '245372'
         AND org_id = 86;

   CURSOR cur_l (
      p_header_id                         NUMBER
   ) IS
      SELECT order_source_id
           , orig_sys_document_ref
           , orig_sys_line_ref
           , orig_sys_shipment_ref
           , inventory_item_id
           , ordered_quantity
           , order_quantity_uom
           , request_date
           , promise_date
           , created_by
           , creation_date
           , last_update_date
           , last_update_login
           , last_updated_by
           , calculate_price_flag
           , unit_list_price
           , unit_selling_price
           , line_type_id
           , delivery_lead_time
           , ordered_item
           , item_identifier_type
        FROM oe_order_lines_all
       WHERE header_id = p_header_id
         AND org_id = 86;
BEGIN
   FOR rec_h IN cur_h LOOP
      l_line_rec.creation_date := SYSDATE;
      l_line_rec.last_update_date := SYSDATE;
      l_line_rec.order_source_id := rec_h.order_source_id;

      INSERT INTO oe_headers_iface_all
                  (
                   order_source_id, orig_sys_document_ref
                 , org_id, operation_code, created_by, creation_date
                 , last_update_date, last_update_login, last_updated_by
                  )
           VALUES (
                   rec_h.order_source_id, rec_h.orig_sys_document_ref
                 , rec_h.org_id, 'UPDATE', -1, SYSDATE
                 , SYSDATE, rec_h.last_update_login, -1
                  );

      DBMS_OUTPUT.put_line ('Sales Order='
         || rec_h.order_number);

      FOR rec_l IN cur_l (rec_h.header_id) LOOP
         --/*
         INSERT INTO oe_lines_iface_all
                     (
                      order_source_id, orig_sys_document_ref
                    , orig_sys_line_ref, orig_sys_shipment_ref
                    , inventory_item_id, ordered_quantity
                    , delivery_lead_time, schedule_arrival_date
                                                               --, schedule_ship_date
         ,            created_by
                    , creation_date, last_update_date, last_update_login
                    , last_updated_by, org_id, override_atp_date_code
                    , operation_code
                     --    , customer_item_id
                     )
              VALUES (
                      rec_h.order_source_id, rec_h.orig_sys_document_ref
                    , rec_l.orig_sys_line_ref, rec_l.orig_sys_shipment_ref
                    , rec_l.inventory_item_id, rec_l.ordered_quantity
                    , 0, TO_DATE ('20081128', 'yyyymmdd')
                                                         --, TO_DATE ('20081129', 'yyyymmdd')
         ,            -1
                    , SYSDATE, SYSDATE, rec_l.last_update_login
                    , -1, rec_h.org_id, 'Y'
                    , 'UPDATE'
                     --  , 60608
                     );                                                   --*/

         DBMS_OUTPUT.put_line ('Promise_date='
            || rec_l.promise_date);
      END LOOP;
   END LOOP;

   COMMIT;
END;

Third Step: Submit Order Import Request

Share with you.