A Sample Script to Update a Sales Order Using an API OE

This post is to provide a sample script to update a sales order using an API OE_ORDER_PUB.PROCESS_ORDER. We have tested the script in R12.1.1 instance. This script will update the request date value of an existing order. SCRIPT: SET SERVEROUTPUT ON; DECLARE v_api_version_number v_return_status v_msg_count v_msg_data -- IN Variables -v_header_rec v_line_tbl v_action_request_tbl v_line_adj_tbl -- OUT Variables -v_header_rec_out v_header_val_rec_out v_header_adj_tbl_out v_header_adj_val_tbl_out v_header_price_att_tbl_out v_header_adj_att_tbl_out v_header_adj_assoc_tbl_out v_header_scredit_tbl_out v_header_scredit_val_tbl_out v_line_tbl_out v_line_val_tbl_out v_line_adj_tbl_out v_line_adj_val_tbl_out v_line_price_att_tbl_out v_line_adj_att_tbl_out v_line_adj_assoc_tbl_out v_line_scredit_tbl_out v_line_scredit_val_tbl_out v_lot_serial_tbl_out v_lot_serial_val_tbl_out v_action_request_tbl_out v_msg_index v_data v_loop_count v_debug_file b_return_status b_msg_count b_msg_data BEGIN NUMBER := 1; VARCHAR2 (2000); NUMBER; VARCHAR2 (2000); oe_order_pub.header_rec_type; oe_order_pub.line_tbl_type; oe_order_pub.request_tbl_type; oe_order_pub.line_adj_tbl_type; oe_order_pub.header_rec_type; oe_order_pub.header_val_rec_type; oe_order_pub.header_adj_tbl_type; oe_order_pub.header_adj_val_tbl_type; oe_order_pub.header_price_att_tbl_type; oe_order_pub.header_adj_att_tbl_type; oe_order_pub.header_adj_assoc_tbl_type; oe_order_pub.header_scredit_tbl_type; oe_order_pub.header_scredit_val_tbl_type; oe_order_pub.line_tbl_type; oe_order_pub.line_val_tbl_type; oe_order_pub.line_adj_tbl_type; oe_order_pub.line_adj_val_tbl_type; oe_order_pub.line_price_att_tbl_type; oe_order_pub.line_adj_att_tbl_type; oe_order_pub.line_adj_assoc_tbl_type; oe_order_pub.line_scredit_tbl_type; oe_order_pub.line_scredit_val_tbl_type; oe_order_pub.lot_serial_tbl_type; oe_order_pub.lot_serial_val_tbl_type; oe_order_pub.request_tbl_type; NUMBER; VARCHAR2 NUMBER; VARCHAR2 VARCHAR2 NUMBER; VARCHAR2 (2000); (200); (200); (2000); DBMS_OUTPUT.PUT_LINE('Starting of script'); -- Setting the Enviroment -mo_global.init('ONT'); => 2585 fnd_global.apps_initialize ( user_id ,resp_id => 50864 ,resp_appl_id => 660); mo_global.set_policy_context('S',83); -- Header Record -v_header_rec v_header_rec.operation v_header_rec.request_date Request Date v_header_rec.header_id id := oe_order_pub.g_miss_header_rec; := OE_GLOBALS.G_OPR_UPDATE; := SYSDATE + 1; -- Updating the := 6006; -- Existing order header v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec; -- Line Record -v_line_tbl (1) := oe_order_pub.g_miss_line_rec; DBMS_OUTPUT.PUT_LINE('Starting of API'); -- Calling the API to update the header details of an existing Order -OE_ORDER_PUB.PROCESS_ORDER ( p_api_version_number , p_header_rec , p_line_tbl , p_action_request_tbl , p_line_adj_tbl -- OUT variables , x_header_rec , x_header_val_rec , x_header_adj_tbl , x_header_adj_val_tbl , x_header_price_att_tbl , x_header_adj_att_tbl , x_header_adj_assoc_tbl , x_header_scredit_tbl , x_header_scredit_val_tbl , x_line_tbl , x_line_val_tbl , x_line_adj_tbl , x_line_adj_val_tbl , x_line_price_att_tbl , x_line_adj_att_tbl , x_line_adj_assoc_tbl , x_line_scredit_tbl , x_line_scredit_val_tbl , x_lot_serial_tbl , x_lot_serial_val_tbl , x_action_request_tbl , x_return_status => => => => => => => => => => => => => => => => => => => => => => => => => => => v_api_version_number v_header_rec v_line_tbl v_action_request_tbl v_line_adj_tbl v_header_rec_out v_header_val_rec_out v_header_adj_tbl_out v_header_adj_val_tbl_out v_header_price_att_tbl_out v_header_adj_att_tbl_out v_header_adj_assoc_tbl_out v_header_scredit_tbl_out v_header_scredit_val_tbl_out v_line_tbl_out v_line_val_tbl_out v_line_adj_tbl_out v_line_adj_val_tbl_out v_line_price_att_tbl_out v_line_adj_att_tbl_out v_line_adj_assoc_tbl_out v_line_scredit_tbl_out v_line_scredit_val_tbl_out v_lot_serial_tbl_out v_lot_serial_val_tbl_out v_action_request_tbl_out v_return_status , x_msg_count , x_msg_data ); => v_msg_count => 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 ('Order Header Updation Success : '||v_header_rec_out.header_id); ELSE DBMS_OUTPUT.put_line ('Order Header Updation 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; /