Search 800 + Posts

Feb 19, 2010

OE_ORDER_PUB to Create/Split/Cancel Order Line

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.
 (Oracle Apps)
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;
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_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: ' ||to_char(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.

8 comments:

  1. Nice Work sir... Surprised to see no appreciation till now!

    ReplyDelete
  2. Very good article. Thank You

    ReplyDelete
  3. Excellent Explanation ..Very useful

    Thanks,
    Sandeep

    ReplyDelete
  4. Very Nice article, Thank you....

    ReplyDelete
  5. Hi Sir

    I am facing issues while splitting the line using the above package. Able to split the line from order organizer but facing issues while doing the same using the package.

    ReplyDelete
  6. Hi Guys

    I am looking for an API that work for sale order return.Is there any Standard API?
    Thanks.

    ReplyDelete
  7. Hi Guys,

    I am looking for an API that work for Sale order return.Is there any Standard API.
    Thanks.

    ReplyDelete
  8. Please see error even after providing all parameter. also where do i get the header id or line ID
    Calling API
    Return status failure
    failure
    process ORDER ret status IS: E
    process ORDER msg data IS:
    process ORDER msg COUNT IS: 0
    header.order_number IS:
    header.return_status IS:
    header.booked_flag IS:
    header.header_id IS:
    header.order_source_id IS:
    header.flow_status_code IS:
    Debug = F
    Debug Level = 1
    Debug File = /sqlcom/outbound/l3005022.dbg
    ****************************************************

    ReplyDelete