Search 800 + Posts

Nov 25, 2009

Sample Code for oe_order_pub.Process_Order

Got sample code to create Sales Order in Oracle Order Management .Please note that this is not my code , one of my friend has forwaded this .But I have tested it throughly and succesfully created Sales Orders.

create or replace procedure createsalesorder
(p_org_id NUMBER,
p_user_id NUMBER,
p_resp_id NUMBER,
p_appl_id NUMBER,
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_po_num VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2)
IS
 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 := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number := p_org_id;--204; -- OPERATING UNIT
l_user number := p_user_id;--1318; -- USER
l_resp number := p_resp_id;--21623; -- RESPONSIBLILTY
l_appl number := p_appl_id;--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 := p_order_type_id;--1430;
l_header_rec.sold_to_org_id := p_sold_to_org_id;--1006;
l_header_rec.ship_to_org_id := p_ship_to_org_id;--1026;
l_header_rec.price_list_id := p_price_list_id;--1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := p_curr_code;--'USD';
l_header_rec.flow_status_code := p_flow_status_code;--'ENTERED';
l_header_rec.cust_po_number := p_po_num;--'06112009-08';
l_header_rec.order_source_id := p_order_source_id;--0 ;
--l_header_rec.attribute1 := 'ABC';
/*******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********************************/
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;--1026 ;
l_line_tbl(1).tax_code := p_tax_code;--'Location' ;
/*****************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('****************************************************');

OE_DEBUG_PUB.DEBUG_OFF;
end if;
--END;
end createsalesorder;

14 comments:

  1. to Create the Model Item along with Option Class and Option with OE_ORDER_PUB refer my post http://eoracleapps.blogspot.com/2011/01/script-to-create-ato-model-in-oracle.html

    ReplyDelete
  2. Hi eoracleapps

    Whenever we call oe_order_pub.Process_Order API through a custom form (after commenting DBMS_OUTPUT.PUT_LINE etc) we receive compilations errors with objects like FND_API.G_FALSE and calls to other procedures like OE_GLOBALS.G_OPR_CREATE

    Does it mean, these APIs could only be called from a PL/SQL environment as in while using TOAD? What if we want to call the API through a custom form? Do you have any write up pointing towards calling Oracle standard APIs from developer forms?

    regards,
    raj

    ReplyDelete
    Replies
    1. It is better to make a PL/SQL Procedure. It will work.

      Delete
  3. Hi Raj

    you can call Oracle Public APIs from any where , there is no restriction to call these API from toad etc.when you call Oracle API make sure that the user from which you have connected to database should have access to these API.
    Try to run your form by connecting to database with user name APPS and see it that work for you.

    Hope that is helpful.

    ReplyDelete
  4. Hi again eoracleapps
    Thanks for your input. Yes we are compiling the developer form using apps/apps user only. The only one way we were able to compile the form(s) was by changing fnd_api.g_false with 'F' and other values with suitable substitutions. Can you please post some sample codes for APIs which are called from developer form or reports?

    Thanks

    ReplyDelete
  5. Hi! Raj

    We usually don't call these APIs from Forms , as Oracle has already provided forms to call these APIs.

    We usually use these APIS to build the Webservives, Interfaces etc.

    Try
    http://eoracleapps.blogspot.com/2011/04/api-to-convert-uom.html

    http://eoracleapps.blogspot.com/2011/04/api-to-check-status-of-pp.html

    both posts have examples and code for Oracle APIs.

    ReplyDelete
  6. Hi eoracleapps

    Once again :) I tried the sample code (already fixed the issues with fnd_api.g_false issues). the oe_order_pub.process_order works fine for us, just leaving the order lines with status 'Awaiting Shipping'. When I check the shipment mode, I get an error "Failed to rate shipment 1'

    Would you please clarify what could be causing this error?

    ReplyDelete
  7. Hey Raj

    Can you provide some additional details about what do you mean by "When I check the shipment mode, I get an error "Failed to rate shipment 1".

    If you provide step by Step information how you are gettng this error that will be help me to debug your error.

    Thanks

    ReplyDelete
  8. Sure, I am using the oe_order_pub.process_order to check out the API
    I pass the header information and line level information and the process finishes successfully.
    However when I query the sales order through Quick Sales Order screen, the header level status is 'Booked' and the order line is having 'Awaiting shipment' status.
    I created multiple orders to check the status were due to quantity availability, which is not true.

    From the right click popup menu "Shipping" -> Choose shipment method I get the error I mentioned with my last post.

    ReplyDelete
  9. make sure you have ship-from and ship-to infirmation from the customer . For me it is working fine.

    ReplyDelete
  10. Hi there :)

    Just noticed something with OE_ORDER_PUB.process_order API. While we make Sales Orders using Quick Sales screen, the unit_cost column with oe_order_lines_all populates automatically, which is not the case while the SO is created through API.
    What is missing? Other than unit_cost column rest all price related columns are properly populated against the price list id

    Regards,

    ReplyDelete
  11. This is populated from the price list .
    can you send me your code at eoracleapps@hotmail.com / sgupta@eoracleapps.com

    ReplyDelete
  12. Hi eoracleapps,

    I am using oe_order_pub.process_order API for order repricing,but it doesn't work in case when
    there are two orders with same item.Its work for one order but not for other one.I am updating calculate_price_flag = 'Y' through this API.Please help.

    regards
    vaibhav

    ReplyDelete
  13. It should work , I am not sure why it is working for one Order and not for others.
    Is there any difference in 2 orders in terms of
    Order Type
    Status etc

    Also what status API returns for the Order where it is not updating API calculate_price_flag to Y

    ReplyDelete