今天有整批修改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.