This sql script gives an example of canceling an order using OE_ORDER_PUB.process_order API
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;
/
Posts
No comments:
Post a Comment