Cancel Sales Order Lines

/*************************************/
/* Parameters */
/*************************************/
define P_HEADER_ID = &&1;
define P_LINE_ID = &&2;
SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
set linesize 10000
WHENEVER SQLERROR exit 1 ROLLBACK;
DECLARE
/*************************************/
/* Variables */
/*************************************/
l_step varchar2(250);
l_err_msg varchar2(1000);
v varchar2(1000);

l_LINE_TBL oe_order_pub.Line_Tbl_Type;
lx_return_status VARCHAR2(10000);
lx_msg_count NUMBER;
lx_msg_data VARCHAR2(10000);
lx_header_rec oe_order_pub.Header_Rec_Type;
lx_header_val_rec oe_order_pub.Header_Val_Rec_Type;
lx_header_adj_tbl oe_order_pub.Header_Adj_Tbl_Type;
lx_header_adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type;
lx_header_price_att_tbl oe_order_pub.Header_Price_Att_Tbl_Type;
lx_header_adj_att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;
lx_header_adj_assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;
lx_header_scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type;
lx_header_scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type;
lx_Header_Payment_tbl oe_order_pub.Header_Payment_Tbl_Type;
lx_Header_Payment_val_tbl oe_order_pub.Header_Payment_Val_Tbl_Type;
lx_line_tbl oe_order_pub.Line_Tbl_Type;
lx_line_val_tbl oe_order_pub.Line_Val_Tbl_Type;
lx_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type;
lx_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type;
lx_Line_price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type;
lx_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type;
lx_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;
lx_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type;
lx_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type;
lx_Line_Payment_tbl oe_order_pub.Line_Payment_Tbl_Type;
lx_Line_Payment_val_tbl oe_order_pub.Line_Payment_Val_Tbl_Type;
lx_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type;
lx_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type;
lx_action_request_tbl oe_order_pub.Request_Tbl_Type;
/*************************************/
/* Main */
/*************************************/
BEGIN

--delete from temp;
fnd_global.apps_initialize(1090, 21623,660);
mo_global.set_policy_context('S',82);
FND_FILE.PUT_LINE(FND_FILE.LOG,'P_header_id = '||3451);
FND_FILE.PUT_LINE(FND_FILE.LOG,'P_line_id = '||6818);

l_step := 'Insert values into l_line_tbl';

l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).ordered_quantity := 0;
l_line_tbl(1).header_id := 351;
l_line_tbl(1).line_id := 6818;
l_line_tbl(1).change_reason := 'Not provided'; -- 'Test'; --'No reason provided';
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;

l_step := 'Run API OE_ORDER_PUB.PROCESS_ORDER';
apps.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_TRUE
, x_return_status => lx_return_status
, x_msg_count => lx_msg_count
, x_msg_data => lx_msg_data
, p_line_tbl => l_line_tbl
, x_header_rec => lx_header_rec
, x_header_val_rec => lx_header_val_rec
, x_Header_Adj_tbl => lx_Header_Adj_tbl
, x_Header_Adj_val_tbl => lx_Header_Adj_val_tbl
, x_Header_price_Att_tbl => lx_Header_price_Att_tbl
, x_Header_Adj_Att_tbl => lx_Header_Adj_Att_tbl
, x_Header_Adj_Assoc_tbl => lx_Header_Adj_Assoc_tbl
, x_Header_Scredit_tbl => lx_Header_Scredit_tbl
, x_Header_Scredit_val_tbl => lx_Header_Scredit_val_tbl
, x_Header_Payment_tbl => lx_Header_Payment_tbl
, x_Header_Payment_val_tbl => lx_Header_Payment_val_tbl
, x_line_tbl => lx_line_tbl
, x_line_val_tbl => lx_line_val_tbl
, x_Line_Adj_tbl => lx_Line_Adj_tbl
, x_Line_Adj_val_tbl => lx_Line_Adj_val_tbl
, x_Line_price_Att_tbl => lx_Line_price_Att_tbl
, x_Line_Adj_Att_tbl => lx_Line_Adj_Att_tbl
, x_Line_Adj_Assoc_tbl => lx_Line_Adj_Assoc_tbl
, x_Line_Scredit_tbl => lx_Line_Scredit_tbl
, x_Line_Scredit_val_tbl => lx_Line_Scredit_val_tbl
, x_Line_Payment_tbl => lx_Line_Payment_tbl
, x_Line_Payment_val_tbl => lx_Line_Payment_val_tbl
, x_Lot_Serial_tbl => lx_Lot_Serial_tbl
, x_Lot_Serial_val_tbl => lx_Lot_Serial_val_tbl
, x_action_request_tbl => lx_action_request_tbl
);

commit;

IF lx_msg_count > 0
THEN
dbms_output.put_line('Listing Errors .................for Order Number -');

FOR l_index IN 1 .. lx_msg_count
LOOP
dbms_output.put_line('Listing Errors .................for Order Number -');
v := apps.oe_msg_pub.get (p_msg_index => l_index, p_encoded => apps.fnd_api.g_false);
insert into xx_test
(error)
values (v);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_err_msg := 'Error in script ETALCANCELCI.sql at: '||l_step||': ';
l_err_msg := l_err_msg || sqlerrm;
FND_FILE.PUT_LINE(FND_FILE.LOG,CHR(10)||l_err_msg);
RAISE;
END;
/
create table XX_TEST
(Error varchar2(200)
)
select * from fnd_lookup_values where meaning like 'Order Cancelled'
select * from oe_order_headers_all where header_id = ****