Wednesday, June 30, 2010
API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)
PURPOSE:
This post is to provide a sample script to cancel a line in an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.
TEST INSTANCE: R12.1.1
SCRIPT:
SET SERVEROUTPUT ON;
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;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
-- Setting the Enviroment --
mo_global.init('ONT');
fnd_global.apps_initialize ( user_id => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
-- 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 := 6006;
v_line_tbl (1).line_id := 4697;
v_line_tbl (1).ordered_quantity := 0;
v_line_tbl (1).cancelled_flag := 'Y';
v_line_tbl (1).change_reason := 'Not Provided';
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- 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');
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
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;
/
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
5 Responses to “API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)”
February 1, 2011 at 2:05 AM
It's working...
Thanks for saving our time.
July 25, 2013 at 12:45 PM
Perfect script
July 25, 2013 at 12:49 PM
As Shell Script : Some add on to above written script
APPS_PASS=apps/xxxxx
USER_NAME='AJAIN'
OUTFILE=/tmp/1.txt
rm -f $OUTFILE
#### STEP #1 CANCEL LINE ORDER
sqlplus -s ${APPS_PASS} >> ${OUTFILE} < 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');
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;
/
EOF
September 17, 2013 at 9:34 AM
tks a lot....
January 12, 2014 at 8:32 AM
Hi Ajay Jain, Thank you for sharing your knowledge with us.
Post a Comment