Oracle Order API (OE_ORDER_PUB.process_order) example

DECLARE i NUMBER; l_msg_data VARCHAR2(2000); l_user_id NUMBER; CURSOR c_orders IS SELECT order_number , header_id , flow_status_code FROM oe_order_headers_all WHERE created_by = l_user_id AND ordered_date > TRUNC(SYSDATE) AND flow_status_code <> 'CANCELLED' ; l_header_rec OE_ORDER_PUB.header_rec_type := OE_Order_PUB.G_MISS_HEADER_REC; l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE := OE_Order_PUB.G_MISS_LINE_TBL; l_return_status VARCHAR2(30);PROCEDURE oe_order_pub_example( p_header_rec IN OE_ORDER_PUB.header_rec_type, p_line_tbl IN OE_ORDER_PUB.line_tbl_type, x_return_status OUT VARCHAR2) IS l_msg_data VARCHAR2(2000); l_msg_count NUMBER; l_old_header_rec OE_ORDER_PUB.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC; l_header_val_rec OE_ORDER_PUB.header_val_rec_type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC; l_old_header_val_rec OE_ORDER_PUB.header_val_rec_type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC; l_Header_Adj_tbl OE_ORDER_PUB.header_adj_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL; l_old_Header_Adj_tbl OE_ORDER_PUB.header_adj_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL; l_Header_Adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL; l_old_Header_Adj_val_tbl OE_ORDER_PUB.header_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL; l_Header_price_Att_tbl OE_ORDER_PUB.header_price_att_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL; l_old_Header_Price_Att_tbl OE_ORDER_PUB.header_price_att_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL; l_Header_Adj_Att_tbl OE_ORDER_PUB.header_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL; l_old_Header_Adj_Att_tbl OE_ORDER_PUB.header_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL; l_Header_Adj_Assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL; l_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.header_adj_assoc_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL; l_Header_Scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL; l_old_Header_Scredit_tbl OE_ORDER_PUB.header_scredit_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL; l_Header_Scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL; l_old_Header_Scredit_val_tbl OE_ORDER_PUB.header_scredit_val_tbl_type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL; l_old_line_tbl OE_ORDER_PUB.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL; l_line_val_tbl OE_ORDER_PUB.line_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL; l_old_line_val_tbl OE_ORDER_PUB.line_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL; l_Line_Adj_tbl OE_ORDER_PUB.line_adj_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL; l_old_Line_Adj_tbl OE_ORDER_PUB.line_adj_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL; l_Line_Adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL; l_old_Line_Adj_val_tbl OE_ORDER_PUB.line_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL; l_Line_price_Att_tbl OE_ORDER_PUB.line_price_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL; l_old_Line_Price_Att_tbl OE_ORDER_PUB.line_price_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL; l_Line_Adj_Att_tbl OE_ORDER_PUB.line_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL; l_old_Line_Adj_Att_tbl OE_ORDER_PUB.line_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL; l_Line_Adj_Assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL; l_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.line_adj_assoc_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL; l_Line_Scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL; l_old_Line_Scredit_tbl OE_ORDER_PUB.line_scredit_tbl_type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL; l_Line_Scredit_val_tbl OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYpe := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL; l_old_Line_Scredit_val_tbl OE_ORDER_PUB.line_scredit_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL; l_Lot_Serial_tbl OE_ORDER_PUB.lot_serial_tbl_type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL; l_old_Lot_Serial_tbl OE_ORDER_PUB.lot_serial_tbl_type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL; l_Lot_Serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL; l_old_Lot_Serial_val_tbl OE_ORDER_PUB.lot_serial_val_tbl_type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL; l_Action_Request_tbl OE_ORDER_PUB.request_tbl_type := OE_ORDER_PUB.G_MISS_REQUEST_TBL; x_header_rec OE_ORDER_PUB.header_rec_type; 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_tbl OE_ORDER_PUB.line_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;BEGIN DBMS_OUTPUT.put_line('Before Return Status ' x_return_status ); OE_ORDER_PUB.process_order ( p_api_version_number => 1.0 , p_init_msg_list => FND_API.G_TRUE , p_return_values => FND_API.G_TRUE , p_action_commit => FND_API.G_FALSE , x_return_status => x_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data , p_header_rec => p_header_rec , p_old_header_rec => l_old_header_rec , p_header_val_rec => l_header_val_rec , p_old_header_val_rec => l_old_header_val_rec , p_header_adj_tbl => l_header_adj_tbl , p_old_header_adj_tbl => l_old_header_adj_tbl , p_header_adj_val_tbl => l_header_adj_val_tbl , p_old_header_adj_val_tbl => l_old_header_adj_val_tbl , p_header_price_att_tbl => l_header_price_att_tbl , p_old_header_price_att_tbl => l_old_header_price_att_tbl , p_header_adj_att_tbl => l_header_adj_att_tbl , p_old_header_adj_att_tbl => l_old_header_adj_att_tbl , p_header_adj_assoc_tbl => l_header_adj_assoc_tbl , p_old_header_adj_assoc_tbl => l_old_header_adj_assoc_tbl , p_header_scredit_tbl => l_header_scredit_tbl , p_old_header_scredit_tbl => l_old_header_scredit_tbl , p_header_scredit_val_tbl => l_header_scredit_val_tbl , p_old_header_scredit_val_tbl => l_old_header_scredit_val_tbl , p_line_tbl => p_line_tbl , p_old_line_tbl => l_old_line_tbl , p_line_val_tbl => l_line_val_tbl , p_old_line_val_tbl => l_old_line_val_tbl , p_line_adj_tbl => l_line_adj_tbl , p_old_line_adj_tbl => l_old_line_adj_tbl , p_line_adj_val_tbl => l_line_adj_val_tbl , p_old_line_adj_val_tbl => l_old_line_adj_val_tbl , p_line_price_att_tbl => l_line_price_att_tbl , p_old_line_price_att_tbl => l_old_line_price_att_tbl , p_line_adj_att_tbl => l_line_adj_att_tbl , p_old_line_adj_att_tbl => l_old_line_adj_att_tbl , p_line_adj_assoc_tbl => l_line_adj_assoc_tbl , p_old_line_adj_assoc_tbl => l_old_line_adj_assoc_tbl , p_line_scredit_tbl => l_line_scredit_tbl , p_old_line_scredit_tbl => l_old_line_scredit_tbl , p_line_scredit_val_tbl => l_line_scredit_val_tbl , p_old_line_scredit_val_tbl => l_old_line_scredit_val_tbl , p_lot_serial_tbl => l_lot_serial_tbl , p_old_lot_serial_tbl => l_old_lot_serial_tbl , p_lot_serial_val_tbl => l_lot_serial_val_tbl , p_old_lot_serial_val_tbl => l_old_lot_serial_val_tbl , p_action_request_tbl => l_action_request_tbl , x_header_rec => x_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 => x_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 => x_action_request_tbl ) ;END;BEGIN -- Set this variable to some valid oracle application user id l_user_id := 14304; DBMS_APPLICATION_INFO.set_client_info('1'); -- -- 21623: Responsibility id of Oracle Order managemenr super user -- 660 : Application id of ONT Module -- FND_GLOBAL.apps_initialize(l_user_id,21623,660); OE_DEBUG_PUB.debug_on(); OE_DEBUG_PUB.start_ont_debugger('/tmp','skm1',null); FOR l_order_rec in c_orders LOOP l_return_status := 'S'; l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE; l_header_rec.change_reason := 'SYSTEM'; l_header_rec.change_comments := 'Automatic clean up process'; l_header_rec.order_number := l_order_rec.order_number; l_header_rec.header_id := l_order_rec.header_id; l_header_rec.cancelled_flag := 'Y'; l_header_rec.created_by := FND_GLOBAL.user_id; oe_order_pub_example ( p_header_rec => l_header_rec , p_line_tbl => l_line_tbl , x_return_status => l_return_status ) ; DBMS_OUTPUT.put_line('Order Number:' l_order_rec.order_number ' Return Status:' l_return_status); IF fnd_msg_pub.count_msg > 0 THEN FOR j in 1..FND_MSG_PUB.count_msg LOOP FND_MSG_PUB.get ( p_msg_index => j , p_encoded => 'F' , p_data => l_msg_data , p_msg_index_out => i ) ; DBMS_OUTPUT.put_line( 'Error: ' j ':' l_msg_data); END LOOP; END IF; END LOOP;END;/