DECLARE
/*
Script Developed by Bharat Bhatia on 2021/07/26 06:44
*/
CURSOR l_data
IS
SELECT header_id, order_number, ordered_date
FROM oe_order_headers_all o
WHERE flow_status_code = 'ENTERED'
AND o.ordered_date <= '27-SEP-2020'
-- AND header_id = 1810230
AND org_id = 22
ORDER BY 3, 1;
l_header_rec oe_order_pub.header_rec_type;
v_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
v_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.request_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
l_header_scr_tbl oe_order_pub.header_scredit_tbl_type;
l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
l_request_rec oe_order_pub.request_rec_type;
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
p_api_version_number NUMBER := 1.0;
p_init_msg_list VARCHAR2 (10) := fnd_api.g_false;
p_return_values VARCHAR2 (10) := fnd_api.g_false;
p_action_commit VARCHAR2 (10) := fnd_api.g_false;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (100);
p_header_rec oe_order_pub.header_rec_type
:= oe_order_pub.g_miss_header_rec;
p_old_header_rec oe_order_pub.header_rec_type
:= oe_order_pub.g_miss_header_rec;
p_header_val_rec oe_order_pub.header_val_rec_type
:= oe_order_pub.g_miss_header_val_rec;
p_old_header_val_rec oe_order_pub.header_val_rec_type
:= oe_order_pub.g_miss_header_val_rec;
p_header_adj_tbl oe_order_pub.header_adj_tbl_type
:= oe_order_pub.g_miss_header_adj_tbl;
p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type
:= oe_order_pub.g_miss_header_adj_tbl;
p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
:= oe_order_pub.g_miss_header_adj_val_tbl;
p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
:= oe_order_pub.g_miss_header_adj_val_tbl;
p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
:= oe_order_pub.g_miss_header_price_att_tbl;
p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
:= oe_order_pub.g_miss_header_price_att_tbl;
p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
:= oe_order_pub.g_miss_header_adj_att_tbl;
p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
:= oe_order_pub.g_miss_header_adj_att_tbl;
p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
:= oe_order_pub.g_miss_header_adj_assoc_tbl;
p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
:= oe_order_pub.g_miss_header_adj_assoc_tbl;
p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
:= oe_order_pub.g_miss_header_scredit_tbl;
p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
:= oe_order_pub.g_miss_header_scredit_tbl;
p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
:= oe_order_pub.g_miss_header_scredit_val_tbl;
p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
:= oe_order_pub.g_miss_header_scredit_val_tbl;
p_line_tbl oe_order_pub.line_tbl_type
:= oe_order_pub.g_miss_line_tbl;
p_old_line_tbl oe_order_pub.line_tbl_type
:= oe_order_pub.g_miss_line_tbl;
p_line_val_tbl oe_order_pub.line_val_tbl_type
:= oe_order_pub.g_miss_line_val_tbl;
p_old_line_val_tbl oe_order_pub.line_val_tbl_type
:= oe_order_pub.g_miss_line_val_tbl;
p_line_adj_tbl oe_order_pub.line_adj_tbl_type
:= oe_order_pub.g_miss_line_adj_tbl;
p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type
:= oe_order_pub.g_miss_line_adj_tbl;
p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
:= oe_order_pub.g_miss_line_adj_val_tbl;
p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
:= oe_order_pub.g_miss_line_adj_val_tbl;
p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
:= oe_order_pub.g_miss_line_price_att_tbl;
p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
:= oe_order_pub.g_miss_line_price_att_tbl;
p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
:= oe_order_pub.g_miss_line_adj_att_tbl;
p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
:= oe_order_pub.g_miss_line_adj_att_tbl;
p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
:= oe_order_pub.g_miss_line_adj_assoc_tbl;
p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
:= oe_order_pub.g_miss_line_adj_assoc_tbl;
p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
:= oe_order_pub.g_miss_line_scredit_tbl;
p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
:= oe_order_pub.g_miss_line_scredit_tbl;
p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
:= oe_order_pub.g_miss_line_scredit_val_tbl;
p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
:= oe_order_pub.g_miss_line_scredit_val_tbl;
p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
:= oe_order_pub.g_miss_lot_serial_tbl;
p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
:= oe_order_pub.g_miss_lot_serial_tbl;
p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
:= oe_order_pub.g_miss_lot_serial_val_tbl;
p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
:= oe_order_pub.g_miss_lot_serial_val_tbl;
p_action_request_tbl oe_order_pub.request_tbl_type
:= oe_order_pub.g_miss_request_tbl;
x_header_val_rec oe_order_pub.header_val_rec_type;
x_header_adj_tbl oe_order_pub.header_adj_tbl_type;
x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
x_line_val_tbl oe_order_pub.line_val_tbl_type;
x_line_adj_tbl oe_order_pub.line_adj_tbl_type;
x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;
x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;
x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;
x_action_request_tbl oe_order_pub.request_tbl_type;
x_debug_file VARCHAR2 (100);
l_line_tbl_index NUMBER;
l_msg_index_out NUMBER (10);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
mo_global.init ('ONT');
mo_global.set_policy_context ('S', 22);
fnd_global.apps_initialize (26067, 50923, 660);
-- pass in user_id, responsibility_id, and application_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
x_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.setdebuglevel (5);
-- Use 5 for the most debuging output, I warn you its a lot of data
DBMS_OUTPUT.put_line ('START OF NEW DEBUG');
--Initialize header record to missing
FOR h IN l_data
LOOP
l_header_rec := oe_order_pub.g_miss_header_rec;
l_header_rec.header_id := h.header_id; -- Enter Desired Value
l_header_rec.operation := oe_globals.g_opr_delete;
oe_order_pub.process_order
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_action_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl
-- OUT PARAMETERS
,
x_header_rec => v_header_rec,
x_header_val_rec => x_header_val_rec,
x_header_adj_tbl => x_header_adj_tbl,
x_header_adj_val_tbl => x_header_adj_val_tbl,
x_header_price_att_tbl => x_header_price_att_tbl,
x_header_adj_att_tbl => x_header_adj_att_tbl,
x_header_adj_assoc_tbl => x_header_adj_assoc_tbl,
x_header_scredit_tbl => x_header_scredit_tbl,
x_header_scredit_val_tbl => x_header_scredit_val_tbl,
x_line_tbl => v_line_tbl,
x_line_val_tbl => x_line_val_tbl,
x_line_adj_tbl => x_line_adj_tbl,
x_line_adj_val_tbl => x_line_adj_val_tbl,
x_line_price_att_tbl => x_line_price_att_tbl,
x_line_adj_att_tbl => x_line_adj_att_tbl,
x_line_adj_assoc_tbl => x_line_adj_assoc_tbl,
x_line_scredit_tbl => x_line_scredit_tbl,
x_line_scredit_val_tbl => x_line_scredit_val_tbl,
x_lot_serial_tbl => x_lot_serial_tbl,
x_lot_serial_val_tbl => x_lot_serial_val_tbl,
x_action_request_tbl => v_action_request_tbl
);
DBMS_OUTPUT.put_line ( 'OM Debug file: '
|| oe_debug_pub.g_dir
|| '/'
|| oe_debug_pub.g_file
);
-- Retrieve messages
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('message is: ' || l_msg_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index_out);
END LOOP;
-- Check the return status
IF l_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'Order # => '
|| h.order_number
|| ' Date => '
|| h.ordered_date
|| ' deletion success '
);
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'Order # => '
|| h.order_number
|| ' Date => '
|| h.ordered_date
|| ' deletion failed '
);
END IF;
END LOOP;
END;
