DECLARE
v_api_version_number NUMBER := 1;v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
-- IN Variables --
v_header_rec oe_order_pub.header_rec_type;
v_line_tbl oe_order_pub.line_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
v_line_adj_tbl oe_order_pub.line_adj_tbl_type;
-- OUT Variables --
v_header_rec_out oe_order_pub.header_rec_type;
v_header_val_rec_out oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out oe_order_pub.line_tbl_type;
v_line_val_tbl_out oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out oe_order_pub.request_tbl_type;
CURSOR c1 IS
SELECT (SELECT order_number
FROM oe_order_headers_all
WHERE header_id = ool.header_id)order_num,
header_id,
line_id,
ordered_quantity
FROM oe_order_lines_all
WHERE header_id = (SELECT header_id
FROM oe_order_headers_all
WHERE order_number = <------->)
AND flow_status_code = 'AWAITING_SHIPPING';
BEGIN
dbms_output.Put_line('Starting of script');
-- Setting the Enviroment --
mo_global.Init('ONT');
fnd_global.Apps_initialize (user_id => 0,resp_id => ----,resp_appl_id => 660);
mo_global.Set_policy_context('S', 105);
V_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
FOR ord_rec IN c1 LOOP
-- Cancel a Line Record --
V_line_tbl (1) := oe_order_pub.g_miss_line_rec;
V_line_tbl (1).operation := oe_globals.g_opr_update;
V_line_tbl (1).header_id := ord_rec.header_id;
V_line_tbl (1).line_id := ord_rec.line_id; --12.2
V_line_tbl (1).ordered_quantity := ord_rec.ordered_quantity;
V_line_tbl (1).cancelled_flag := 'Y';
V_line_tbl (1).change_reason := 'Canceled Through API';
dbms_output.Put_line('Starting of API for Line Id'
||ord_rec.line_id);
-- Calling the API to cancel a line from an Existing Order --
oe_order_pub.Process_order (p_api_version_number => v_api_version_number
,
p_header_rec => v_header_rec, p_line_tbl => v_line_tbl,
p_action_request_tbl => v_action_request_tbl,
p_line_adj_tbl => v_line_adj_tbl
-- OUT variables
, x_header_rec => v_header_rec_out,
x_header_val_rec => v_header_val_rec_out
,
x_header_adj_tbl => v_header_adj_tbl_out,
x_header_adj_val_tbl => v_header_adj_val_tbl_out,
x_header_price_att_tbl => v_header_price_att_tbl_out,
x_header_adj_att_tbl => v_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out,
x_header_scredit_tbl => v_header_scredit_tbl_out,
x_header_scredit_val_tbl => v_header_scredit_val_tbl_out,
x_line_tbl => v_line_tbl_out, x_line_val_tbl => v_line_val_tbl_out,
x_line_adj_tbl => v_line_adj_tbl_out,
x_line_adj_val_tbl => v_line_adj_val_tbl_out,
x_line_price_att_tbl => v_line_price_att_tbl_out,
x_line_adj_att_tbl => v_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out,
x_line_scredit_tbl => v_line_scredit_tbl_out,
x_line_scredit_val_tbl => v_line_scredit_val_tbl_out,
x_lot_serial_tbl => v_lot_serial_tbl_out,
x_lot_serial_val_tbl => v_lot_serial_val_tbl_out,
x_action_request_tbl => v_action_request_tbl_out,
x_return_status => v_return_status, x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
dbms_output.Put_line('Completion of API for line id'
||ord_rec.line_id);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
dbms_output.Put_line ('Line Cancelation in Existing Order is Success '
);
ELSE
dbms_output.Put_line ('Line Cancelation in Existing Order failed:'
||v_msg_data);
ROLLBACK;
FOR i IN 1 .. v_msg_count LOOP
v_msg_data := oe_msg_pub.Get(p_msg_index => i, p_encoded => 'F');
dbms_output.Put_line(i
|| ') '
|| v_msg_data);
END LOOP;
END IF;
END LOOP;
END;
Note: If there is any issue with Sales Order Line Cancellation through above API, then we may also try below DMLs. Kindly make sure there is no any RESERVATION Quantity against those Lines :
UPDATE ont.oe_order_lines_all
SET flow_status_code = 'CANCELLED',
cancelled_flag = 'Y',
cancelled_quantity = ordered_quantity
--ORDERED_QUANTITY = 0
WHERE line_id IN (<-------------->);
SET flow_status_code = 'CANCELLED',
cancelled_flag = 'Y',
cancelled_quantity = ordered_quantity
--ORDERED_QUANTITY = 0
WHERE line_id IN (<-------------->);
SELECT wdd.released_status,
wdd.*
FROM oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE h.header_id = l.header_id
AND wdd.source_header_id = l.header_id
AND wdd.source_line_id = l.line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND h.order_number = <------>
AND wda.delivery_id = <----->;
wdd.*
FROM oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE h.header_id = l.header_id
AND wdd.source_header_id = l.header_id
AND wdd.source_line_id = l.line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND h.order_number = <------>
AND wda.delivery_id = <----->;
UPDATE wsh_delivery_details
SET released_status = 'D' --D: Cancelled -Line is Cancelled
WHERE delivery_detail_id = <---->;
SET released_status = 'D' --D: Cancelled -Line is Cancelled
WHERE delivery_detail_id = <---->;
