Script to Create ATO Model in Oracle Order Management with OE_ORDER_PUB

I have spent some good amount of time to write script to create ATO Model in Oracle Order Management by means of Process Order API (OE_ORDER_PUB)  , so thought of Sharing with my readers.

Please feel free to share your thoughts.

My Test case is - 

Model

     Class

       Option

-- API to create Sales Order and Return Order.

declare

l_api_version_number NUMBER := 1;

l_return_status VARCHAR2(2000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

/*****************PARAMETERS****************************************************/

l_debug_level number := 5; -- 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);

l_link_to_line_index    NUMBER;

-- 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 **********************************/

l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;

l_header_rec.order_type_id := 1437;

l_header_rec.sold_to_org_id :=1006;

l_header_rec.ship_to_org_id :=1026;

l_header_rec.price_list_id := 1000;

l_header_rec.pricing_date := SYSDATE;

l_header_rec.transactional_curr_code := 'USD';

l_header_rec.flow_status_code := 'ENTERED';

l_header_rec.cust_po_number := '023456-0181';

l_header_rec.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********************************/

---Ship line or RMA 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 := 27857;--149 ;

l_line_tbl(1).ordered_quantity := 1;

l_line_tbl(1).ship_to_org_id := 1026 ;

l_line_tbl(1).tax_code := 'Location' ;

l_line_tbl(1).item_type_code := 'MODEL' ;

l_line_tbl(1).ship_from_org_id := 207;

l_line_tbl(1).top_model_line_index := 1;

l_line_tbl(1).link_to_line_index := NULL;

l_link_to_line_index             :=1;

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 := 27858;--149 ;

l_line_tbl(2).ordered_quantity := 1;

l_line_tbl(2).ship_to_org_id := 1026 ;

l_line_tbl(2).ship_from_org_id := 207;

l_line_tbl(2).tax_code := 'Location' ;

l_line_tbl(2).item_type_code := 'CLASS';

l_line_tbl(2).top_model_line_index := 1;

l_line_tbl(2).link_to_line_index := l_link_to_line_index;

l_line_tbl(3) := oe_order_pub.G_MISS_LINE_REC;

l_line_tbl(3).operation := OE_GLOBALS.G_OPR_CREATE;

l_line_tbl(3).inventory_item_id := 27859;--149 ;

l_line_tbl(3).ordered_quantity := 1;

l_line_tbl(3).ship_to_org_id := 1026 ;

l_line_tbl(3).ship_from_org_id := 207;

l_line_tbl(2).tax_code := 'Location' ;

l_line_tbl(3).item_type_code := 'OPTION';

l_line_tbl(3).top_model_line_index := 1;

l_line_tbl(3).link_to_line_index := l_link_to_line_index;

/*****************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;

else

dbms_output.put_line('Return status failure ');

if (l_debug_level > 0) then

dbms_output.put_line('failure');

end if;

rollback;

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;

/*****************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;

END;