Below is code to add New Line in an existing Sales Order with OE_ORDER_PUB.
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);
--MO_GLOBAL.set_policy_context('S',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 **********************************/
dbms_output.put_line('Start'||'--'||p_code||'--'||substr(p_code,1));
IF p_code = 'AL' THEN -- add Create Shippable Line
dbms_output.put_line('Add New 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' ;
l_line_tbl(1).header_id := p_header_id;
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;
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;
I am calling above Package from following PL/SQL block
------------------------
declare
l_status VARCHAR2(1000);
p_header_id NUMBER;
p_line_id NUMBER:=197953;
p_code VARCHAR2(10):='AL';
BEGIN
l_status:= xxorderprocess.xxcreateOrder(1437,1005,1024,1000,'USD','ENTERED',
'PO-9090',0,149,100,'Location',
p_code,p_header_id,p_line_id);
dbms_output.put_line('l_status ='||l_status);
end;
-----------------
No comments:
Post a Comment