Introduction:
This Post illustrates the steps to Update the schedule ship date in Order Lines using API.
Script to Update the Scheduled Ship Date in Order Lines
DECLARE
p_scheduled_ship_date DATE := TRUNC (SYSDATE);
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_header_rec_in oe_order_pub.header_rec_type;
l_action_request_tbl_in oe_order_pub.request_tbl_type;
l_header_rec_out oe_order_pub.header_rec_type;
p_lin_rec_tbl oe_order_pub.line_tbl_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_oe_lin_rec oe_order_pub.line_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
p_line_val_rec oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_chr_program_unit_name VARCHAR2 (100);
l_chr_ret_status VARCHAR2 (1000) := NULL;
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (2000);
CURSOR c1
IS
SELECT oh.header_id, ool.line_id, oh.order_number,
ool.line_number || ‘.’ || ool.shipment_number line_number,
oh.cust_po_number, msi.segment1 item_number, hca.account_number,
hp.party_name customer_name, ool.schedule_ship_date,
ool.inventory_item_id, ool.line_type_id, ool.org_id
FROM oe_order_headers_all oh,
oe_order_lines_all ool,
mtl_system_items_b msi,
mtl_uom_conversions muc,
wsh_delivery_details wdd,
mtl_reservations mr,
hz_cust_accounts hca,
hz_parties hp
WHERE oh.header_id = ool.header_id
AND msi.inventory_item_id = ool.inventory_item_id
AND msi.organization_id = ool.ship_from_org_id
AND muc.inventory_item_id = msi.inventory_item_id
AND msi.primary_unit_of_measure = muc.unit_of_measure
AND ool.line_id = wdd.source_line_id
AND wdd.released_status = ‘R’
AND ool.line_id = mr.demand_source_line_id(+)
AND hca.cust_account_id = oh.sold_to_org_id
AND hp.party_id = hca.party_id
AND oh.order_number = p_order_number;
BEGIN
BEGIN
SELECT resp.responsibility_id, app.application_id
INTO l_resp_id, l_resp_appl_id
FROM fnd_responsibility_vl resp, fnd_application app
WHERE resp.application_id = app.application_id
AND resp.responsibility_name = ‘Order Management Super User’;
END;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘SYSADMIN’;
END;
BEGIN
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
END;
FOR cur_order IN c1
LOOP
BEGIN
l_oe_lin_rec (1) := oe_order_pub.g_miss_line_rec;
l_oe_lin_rec (1).line_id := cur_order.line_id;
l_oe_lin_rec (1).line_type_id := cur_order.line_type_id;
l_oe_lin_rec (1).schedule_ship_date :=
TRUNC (TO_DATE (TO_DATE (p_scheduled_ship_date,
‘RRRR/MM/DD HH24:MI:SS’
),
‘DD-MM-RRRR’
)
);
l_oe_lin_rec (1).operation := oe_globals.g_opr_update;
oe_msg_pub.delete_msg;
oe_debug_pub.setdebuglevel (5);
oe_order_pub.process_order
(p_api_version_number => 1.0,
p_action_commit => fnd_api.g_false,
p_line_tbl => l_oe_lin_rec,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => p_lin_rec_tbl,
x_line_val_tbl => p_line_val_rec,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_chr_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_msg_data := NULL;
DBMS_OUTPUT.put_line (‘STATUS => ‘ || l_chr_ret_status);
IF l_chr_ret_status <> ‘S’
THEN
FOR iindx IN 1 .. l_msg_count
LOOP
l_msg_data := l_msg_data || ‘ ‘ || oe_msg_pub.get (iindx);
END LOOP;
END IF;
DBMS_OUTPUT.put_line (‘API Return Status: ‘ || l_chr_ret_status);
DBMS_OUTPUT.put_line (‘API Error Message: ‘ || l_msg_data);
END;
END LOOP;
COMMIT;
END;
Got any queries?
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
