Script to Cancel Sales Order Line Using Oe_order_pub

-- R12 - OM - Sample Script to Cancel Sales Order Line Using Oe_order_pub
=========================================================

DECLARE

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_header_rec_in oe_order_pub.header_rec_type; -- pl/sql table and record definition to be used as IN parameters
l_line_tbl_in oe_order_pub.line_tbl_type; -- pl/sql table and record definition to be used as IN parameters
l_action_request_tbl_in oe_order_pub.request_tbl_type;-- Used to assigining Book Order related input parameters
l_header_rec_out oe_order_pub.header_rec_type; -- pl/sql table and record definition to be used as OUT parameters
l_line_tbl_out 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_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;
l_line_val_tbl_out 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); -- To store the package and procedure name for logging
l_chr_ret_status VARCHAR2 (1000) := NULL;-- To store the error message code returned by API
l_msg_count NUMBER := 0; -- To store the number of error messages API has encountered
l_msg_data VARCHAR2 (2000); -- To store the error message text returned by API
l_num_api_version NUMBER := 1.0; -- API version

CURSOR c_so_details IS

SELECT oh.order_number, ol.*
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE oh.header_id = ol.header_id
AND oh.org_id = ol.org_id
AND NVL (ol.cancelled_flag,'N') = 'N'
AND oh.order_number = '10001690' -- Enter the Order Number
AND ol.line_number = 1 -- Enter the Line Number
AND ol.shipment_number = 2 -- Enter the Shipment Number
AND ol.flow_status_code = 'AWAITING_SHIPPING';

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'A42485';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Order Management Super User';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR iso_rec IN c_so_details LOOP

l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl_in (1).line_id := iso_rec.line_id;
l_line_tbl_in (1).ordered_quantity := 0;
l_line_tbl_in (1).change_reason := 'Admin Error';
l_line_tbl_in (1).change_comments := 'CANCEL ORDER';
l_line_tbl_in (1).operation := oe_globals.g_opr_update;

oe_msg_pub.delete_msg;
mo_global.init ('ONT');
mo_global.set_policy_context ('S', iso_rec.org_id);

oe_order_pub.process_order
(p_api_version_number => l_num_api_version,
p_org_id => mo_global.get_current_org_id,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_action_commit => fnd_api.g_false,
p_line_tbl => l_line_tbl_in,
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 => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
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;

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.ENABLE (10000);

DBMS_OUTPUT.put_line ('Sales Order => '
iso_rec.order_number
' - Line Number => '
iso_rec.line_number
' - Shipment Number => '
iso_rec.shipment_number
' Having Line ID=> '
iso_rec.line_id
' Cancelled Successfully' );

DBMS_OUTPUT.put_line ('Return Status: ' l_chr_ret_status);
DBMS_OUTPUT.put_line ('Error Message: ' l_msg_data);

END LOOP;

END;