API to Cancel Sales order
In this Post I will explain in short how to call Process Order API to
1. Create Sales Order with 1 Shippable line.
2. Create Sales Order with 1 RMA line.
3.Create Sales Order with 1 Shippable and 1 RMA line.
4. Canel Order line.
Purpose of this post is to share some knowledge about OE_ORDER_PUB. Example that I use just creates simple Order and line.
CREATE OR REPLACE PACKAGE xxorderprocess
AS
FUNCTION xxcreateOrder (p_order_type_id NUMBER
, p_sold_to_org_id NUMBER
, p_ship_to_org_id NUMBER
, p_price_list_id NUMBER
, p_transactional_curr_code VARCHAR2
, p_flow_status_code VARCHAR2
, p_cust_po_number VARCHAR2
, p_order_source_id NUMBER
, p_inventory_item_id NUMBER
, p_ordered_quantity NUMBER
, p_tax_code VARCHAR2
, p_code VARCHAR2
, p_header_id NUMBER
, p_line_id NUMBER)
RETURN VARCHAR2;
END xxorderprocess;
/
CREATE OR REPLACE PACKAGE BODY xxorderprocess
AS
FUNCTION xxcreateOrder (p_order_type_id NUMBER
, p_sold_to_org_id NUMBER
, p_ship_to_org_id NUMBER
, p_price_list_id NUMBER
, p_transactional_curr_code VARCHAR2
, p_flow_status_code VARCHAR2
, p_cust_po_number VARCHAR2
, p_order_source_id NUMBER
, p_inventory_item_id NUMBER
, p_ordered_quantity NUMBER
, p_tax_code VARCHAR2
, p_code VARCHAR2
, p_header_id NUMBER
, p_line_id NUMBER)
RETURN VARCHAR2
IS
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_xxstatus VARCHAR2 (1000);
/*****************PARAMETERS****************************************************/
l_debug_level NUMBER := 1; -- OM DEBUG LEVEL (MAX 5)
l_org NUMBER := 204; -- OPERATING UNIT
l_user NUMBER := 1318; -- USER
l_resp NUMBER := 21623; -- RESPONSIBLILTY
l_appl NUMBER := 660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2 (2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2 (200);
-- book API vars
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
IF (l_debug_level > 0)
THEN
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
Oe_Msg_Pub.initialize;
END IF;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize (l_user, l_resp, l_appl);
-- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
IF SUBSTR (p_code, 1, 1) = 'N'
THEN
DBMS_OUTPUT.put_line ('Inside header');
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := p_order_type_id; --1437;--1430
l_header_rec.sold_to_org_id := p_sold_to_org_id; --1005;
l_header_rec.ship_to_org_id := p_ship_to_org_id; --1024;
l_header_rec.price_list_id := p_price_list_id; --1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := p_transactional_curr_code; --'USD';
l_header_rec.flow_status_code := p_flow_status_code; --'ENTERED';
l_header_rec.cust_po_number := p_cust_po_number; --'06112009-118';
l_header_rec.order_source_id := p_order_source_id; --0 ;
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl (1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl (1).request_type := oe_globals.g_book_order;
l_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
IF SUBSTR (p_code, 2) = 'E'
THEN -- Create Shippable Line
DBMS_OUTPUT.put_line ('Inside Ship Line');
---Create 1 Line
l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl (1).inventory_item_id := p_inventory_item_id; --149 ;
l_line_tbl (1).ordered_quantity := p_ordered_quantity; --1;
l_line_tbl (1).ship_to_org_id := p_ship_to_org_id; --1024 ;
l_line_tbl (1).tax_code := p_tax_code; --'Location' ;
ELSIF SUBSTR (p_code, 2) = 'R'
THEN -- Create RMA Line
DBMS_OUTPUT.put_line ('Inside RMA Line');
-- RMA ine
l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl (1).inventory_item_id := p_inventory_item_id; --149 ;
l_line_tbl (1).ordered_quantity := p_ordered_quantity; --1;
l_line_tbl (1).ship_to_org_id := p_ship_to_org_id; --1024 ;
l_line_tbl (1).tax_code := p_tax_code; --'Location' ;
l_line_tbl (1).line_type_id := 1425;
l_line_tbl (1).return_reason_code := 'DAMAGED PRODUCT';
ELSIF SUBSTR (p_code, 2) = 'B'
THEN -- Create Ship and RMA Line
DBMS_OUTPUT.put_line ('Inside Ship Line');
---Create 1 Line
l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl (1).inventory_item_id := p_inventory_item_id; --149 ;
l_line_tbl (1).ordered_quantity := p_ordered_quantity; --1;
l_line_tbl (1).ship_to_org_id := p_ship_to_org_id; --1024 ;
l_line_tbl (1).tax_code := p_tax_code; --'Location' ;
DBMS_OUTPUT.put_line ('Inside RMA Line');
-- RMA ine
l_line_tbl (2) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (2).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl (2).inventory_item_id := p_inventory_item_id; --149 ;
l_line_tbl (2).ordered_quantity := p_ordered_quantity; --1;
l_line_tbl (2).ship_to_org_id := p_ship_to_org_id; --1024 ;
l_line_tbl (2).tax_code := p_tax_code; --'Location' ;
l_line_tbl (2).line_type_id := 1425;
l_line_tbl (2).return_reason_code := 'DAMAGED PRODUCT';
END IF;
END IF;
IF p_code = 'CC'
THEN -- cancel the Order Line
DBMS_OUTPUT.put_line ('Inside cancel Line');
-- Cancel line
l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl (1).ordered_quantity := 0;
l_line_tbl (1).cancelled_quantity := p_ordered_quantity;
l_line_tbl (1).cancelled_flag := 'Y';
l_line_tbl (1).line_id := p_line_id;
l_line_tbl (1).header_id := p_header_id;
l_line_tbl (1).change_reason := 'SYSTEM';
END IF;
IF p_code = 'SP'
THEN -- Split the line
l_action_request_tbl (1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl (1).request_type := oe_globals.g_book_order;
l_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
DBMS_OUTPUT.put_line ('Inside SPLIT Line');
-- Cancel line
l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl (1).split_by := 'USER'; --1318; -- user_id
l_line_tbl (1).split_action_code := 'SPLIT';
l_line_tbl (1).header_id := p_header_id;
l_line_tbl (1).line_id := p_line_id;
l_line_tbl (1).ordered_quantity := 20;
l_line_tbl (2) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl (2).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl (2).header_id := p_header_id;
l_line_tbl (2).split_by := 'USER'; -- user_id
--l_line_tbl(2).split_action_code := 'SPLIT';
l_line_tbl (2).split_from_line_id := p_line_id;
l_line_tbl (2).inventory_item_id := p_inventory_item_id;
l_line_tbl (2).ordered_quantity := 80;
END IF;
/*****************CALLTO PROCESS ORDER API*********************************/
DBMS_OUTPUT.put_line ('Calling API');
oe_order_pub.Process_Order (
p_api_version_number => l_api_version_number
, p_header_rec => l_header_rec
, p_line_tbl => l_line_tbl
, p_action_request_tbl => l_action_request_tbl
, --OUT variables
x_header_rec => l_header_rec_out
, x_header_val_rec => l_header_val_rec_out
, x_header_adj_tbl => l_header_adj_tbl_out
, x_header_adj_val_tbl => l_header_adj_val_tbl_out
, x_header_price_att_tbl => l_header_price_att_tbl_out
, x_header_adj_att_tbl => l_header_adj_att_tbl_out
, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out
, x_header_scredit_tbl => l_header_scredit_tbl_out
, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out
, x_line_tbl => l_line_tbl_out
, x_line_val_tbl => l_line_val_tbl_out
, x_line_adj_tbl => l_line_adj_tbl_out
, x_line_adj_val_tbl => l_line_adj_val_tbl_out
, x_line_price_att_tbl => l_line_price_att_tbl_out
, x_line_adj_att_tbl => l_line_adj_att_tbl_out
, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out
, x_line_scredit_tbl => l_line_scredit_tbl_out
, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out
, x_lot_serial_tbl => l_lot_serial_tbl_out
, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out
, x_action_request_tbl => l_action_request_tbl_out
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
/*****************CHECK RETURN STATUS***********************************/
IF l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.put_line ('Return status is success ');
DBMS_OUTPUT.put_line ('debug level ' || l_debug_level);
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('success');
END IF;
COMMIT;
l_xxstatus := 'S';
ELSE
DBMS_OUTPUT.put_line ('Return status failure ');
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('failure');
END IF;
ROLLBACK;
l_xxstatus := 'F';
END IF;
/*****************DISPLAY RETURN STATUS FLAGS******************************/
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.PUT_LINE (
'process ORDER ret status IS: ' || l_return_status
);
DBMS_OUTPUT.PUT_LINE ('process ORDER msg data IS: ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE ('process ORDER msg COUNT IS: ' || l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: '|| l_header_rec_out.order_number);
DBMS_OUTPUT.PUT_LINE ('header.return_status IS: ' || l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE ('header.booked_flag IS: ' || l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE ('header.header_id IS: ' || l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE ('header.order_source_id IS: ' || l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
END IF;
l_xxstatus :=
l_xxstatus
|| '-'
|| l_header_rec_out.booked_flag
|| '- '
|| l_header_rec_out.header_id
|| '-'
|| l_header_rec_out.flow_status_code;
/*****************DISPLAY ERROR MSGS*************************************/
IF (l_debug_level > 0)
THEN
FOR i IN 1 .. l_msg_count
LOOP
Oe_Msg_Pub.get (p_msg_index => i
, p_encoded => Fnd_Api.G_FALSE
, p_data => l_data
, p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE ('message is: ' || l_data);
DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index);
END LOOP;
END IF;
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.PUT_LINE ('Debug = ' || OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE (
'Debug Level = ' || TO_CHAR (OE_DEBUG_PUB.G_DEBUG_LEVEL)
);
DBMS_OUTPUT.PUT_LINE( 'Debug File = '
|| OE_DEBUG_PUB.G_DIR
|| '/'
|| OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE (
'****************************************************'
);
END IF;
RETURN l_xxstatus;
EXCEPTION
WHEN OTHERS
THEN
l_xxstatus := l_xxstatus || ' ' || SQLERRM;
RETURN l_xxstatus;
END xxcreateOrder;
END xxorderprocess;
----------------------------------------------------
/
----------------------------------------------------
-- I have tested my program with below pl/sql block
DECLARE
l_status VARCHAR2 (1000);
p_header_id NUMBER := &Enter_header_id;
p_line_id NUMBER := &Enter_lline_id;
p_code VARCHAR2 (10) := 'SP';
--NE to create New Line
--SP to Split Line
--CC to Cancel line
--NB to Create RMA Line
BEGIN
l_status :=
xxorderprocess.xxcreateOrder (&order_type_id
, &sold_to_org_id
, &ship_to_org_id
, &price_list_id
, '&curr_code'
, '&flow_status_code'
, '&cust_po_number'
, &order_source_id
, &inventory_item_id
, &ordered_quantity
, '&tax_code'
, p_code
, p_header_id
, p_line_id);
dbms_output.put_line('l_status ='||l_status);
END;
-- All the parameters are not required ex. Header_id and line_id are required only if you want to do Line Split or Line Cancellation.
-- For Line Creation these 2 parameters doesn’t make any sense.
No comments:
Post a Comment