Finally! An OE_Order_PUB.Process_Order Script That Works!
December 6th, 2007 | Send this article to a friend!
Recently I was looking for any working example of the OE_Order_PUB.Process_Order API to use for a volume test I wanted to setup. The idea would be to identify a base script that with minimal changes could be used to create and book a mass amount of sales orders. I scoured Metalink and Google, and for the longest time I could not identify a single working example. Many authors claimed to have posted an OE_Order_PUB.Process_Order script that, with slight modifications, are working examples, but this never was the case. There was always syntax or incorrect API parameter issues with each script I encountered.
Additionally, to create a sales order header, at minimum you’ll need to specify a customer, price list, currency and any required descriptive flexfield values dictated by your setups. You may also need a customer purchase order number if you’re order type has been setup to require it before booking. The code excerpt below shows this information along with other required data that is needed to produce an order header:
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := 100000;
l_header_rec.sold_to_org_id := 1000;
l_header_rec.price_list_id := 100;
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 := ‘123′;
l_header_rec.attribute1 := ‘ABC’;
The script also doesn’t create any order lines, so this code would also have to be added. All that the OE_Order_PUB.Process_Order API needs to create an order line is an ordered item, quantity, and any required descriptive flexfield values that may be needed.
l_line_tbl(1).inventory_item_id := 201775;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).attribute1 := ‘ABC’;
At the beginning of the script you will also need to add an API call to dbms_application_info.set_client_info to set the organization context as well as initializing the user, responsibility, and application under which the order(s) will be created.
dbms_application_info.set_client_info(100000);
…
fnd_global.apps_initialize(l_user, l_resp, l_appl);
Since I want this script to create not just one order, but many orders, I needed to insert a loop into the script with parameters that let me control how many orders to create, and whether to enable or disable debug. For good performance and to avoid exceeding any buffer limitations, a debug “switch” is a good things to have, especially when creating hundreds or even thousands of sales orders.
l_debug_level number := 0; — OM DEBUG LEVEL (MAX 5)
l_no_orders number := 100; — NO OF ORDERS
…
for i in 1..l_no_orders loop — BEGIN LOOP
…
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE (’debug message here’);
end if;
…
end loop; — END LOOP
With these modifications in place, I now have a working script that will create a mass amount of sales orders for the volume test I need. I hope this comes in handy for you!
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 := 0; — OM DEBUG LEVEL (MAX 5)
l_org number := 1000; — OPERATING UNIT
l_no_orders number := 100; — NO OF ORDERS
l_user number := 10; — USER
l_resp number := 100; — 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 **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := 100000;
l_header_rec.sold_to_org_id := 1000;
l_header_rec.price_list_id := 100;
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 := ‘123′;
l_header_rec.attribute1 := ‘ABC’;
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
/*****************INITIALIZE LINE RECORD********************************/
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 := 201775;
l_line_tbl(1).ordered_quantity := 1;
for i in 1..l_no_orders loop — BEGIN LOOP
/*****************CALLTO PROCESS ORDER 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
if (l_debug_level > 0) then
dbms_output.put_line(’success’);
end if;
commit;
OE_ORDER_BOOK_UTIL.COMPLETE_BOOK_ELIGIBLE(1.0,
FND_API.G_FALSE,
l_header_rec_out.header_id,
b_return_status,
b_msg_count,
b_msg_data
);
commit;
else
if (l_debug_level > 0) then
dbms_output.put_line(’failure’);
end if;
rollback;
end if;
end loop; — END LOOP
/*****************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(’****************************************************’);
OE_DEBUG_PUB.DEBUG_OFF;
end if;
END;
Mr. Thompson is a Senior Oracle Applications Consultant with Lexerd Group Consulting. Visit www.LexerdGroup.com to find out more about our firm.
Interested in hosting your own blog? Lexerd Group’s Internet Services division provides specialized blog hosting services that utilizes the Wordpress blogging platform. Visit http://www.lexerdgroup.com/blog-yourself/ for more details.
Entry Filed under: Order Management, Technical
14 Comments Add your own
1. vamshi | December 14th, 2007 at 7:17 am
I found it very useful. Thank You.
2. Mou | January 2nd, 2008 at 1:29 am
Hi Bryan,
Thanks for the article, nice information, presented well.
But when I tried running the script with all correct values from the DB, it throws me an error,
‘ FND-FLEX-NULL SEGMENT’ and return status as E.
Could you please help me out what could be the issue
3. bryan | January 2nd, 2008 at 7:53 am
Hi Mou,
Do you have any order header or order line DFF values that are required? If so, you may need to populate these values in this script. For example, l_header_rec.attribute1 := ‘123′;
You might be doing this already, but wanted to check first before we look at other causes.
Bryan
4. Mou | January 2nd, 2008 at 3:42 pm
Hi Bryan,
Yes you are right, I just looked on the sales orders form that one of the DFF value is mandatory, I just populated and it worked out!
Thanks a lot!
Mou
5. Arun | April 2nd, 2008 at 1:29 am
Hi Bryan,
It is absolutely usefull.
Also can you help me to create a paid-Sales order using this api.
the business requirement is to pay the order while creating or before booking the sales ordr. e.g oline ticket booking.
thanks for your time,
>arun
6. Indu | April 18th, 2008 at 7:16 am
Hi Bryan,
I want to create many orders.
i call the API in a loop and initialize the header records to miss_record.
But still then only one order is created for every run of my program. How to handle this?
Thanks,
Indu
7. AMAN KHURANA | June 23rd, 2008 at 12:31 am
Hi Bryan,
Thanks for such an useful article. Can u plz send me the code for cancellation of order line? Please send me ASAP.
Regards,
Aman
8. Pradip | July 21st, 2008 at 10:57 am
Thanks a lot… Just needed this…
9. Pradip | July 22nd, 2008 at 11:27 am
i am unable to use this in r12 vision database…its gives me errors
SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.VALIDATE_ORGID_PUB_API, N, ERRNO, -20001, N, REASON, ORA-20001: in Package OE_Order_PUB Procedure Process_Order
message index is: 1
if u can also tell the defult org and operationg on the demo instance
i can try to run it
10. bryan | July 22nd, 2008 at 11:56 am
I haven’t run this code in R12, so I’m unsure as to what changes would be needed to make it compadible. I don’t know the default org in the Vision R12 instance off hand.
If you figure out what changes are needed to successfully run the script in R12 I definitely would be interested to know what changes were needed.
Thanks,
Bryan
11. Praveen Kumar | July 30th, 2008 at 3:25 am
Hi Bryan,
I want to create an order by using the api oe_order_pub.order_proceess. But when I am using the given code for my parameter values then There is no output is coming.
Can you send the code again to my ID.
It would be helpful for me.
Thanks,
Praveen Jindal
12. rafee | August 1st, 2008 at 9:44 am
I am finding difficulties while updating payment_term_id,ordered_quantity ,unit_selling_price based on some custom conditions.
if a=0 then
l_line_tbl (l_num_ctr).payment_term_id := 1185;
elsif a=1 then
l_line_tbl (l_num_ctr).shipping_method_code := l_shipping_method_code;
elsif……..
API is not returning code is succes.But values are no getting updated…
what could be the resons..
pls help me in this issue…
13. Rahul | August 21st, 2008 at 10:40 am
I am using this API in BPEL for creating the order and uupdating the order.
But BPEl does not support the table type.
Then what should i do in this case.
For this I’ve created the object type of the table type and then the VARRAY.
But still this is not working.
Please help me how I can do this without using the Table type as the parameter.
I mean I’ve to change the table type in to the any other type of collection.
Thanks in advance
14. Jag Biradar | October 20th, 2008 at 11:11 pm
Great information. Many thanks
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed | Send To a Friend