Refer Oracle Order API documents for more information.
Same API can be used to create a Order Quote also. All quote fields are commented below in API. You can uncomment and use if quote has to be created instead of order. Order Quote uses order tables with few extra columns.
Also included code to initialize quote workflow at the end of code. Uncomment and use it for quote.
Must consider
Quote --Only if you are creating Quote else ignore
- This is standard order quote. If you have Oracle Quotes Module installed then you need to look at ASO_QUOTE_PUB API.
- Quote order type should be defined.
- In order to create a order quote, transaction_phase_code should be set to 'N' in API.
- Below API creates standard order quote which can be accessed from order responsibility under Negotiations menu.
Line --Generic for Both Order/Quote
- If custom price has to be used at order line then set calculate_price_flag to 'N' and then pass custom price to unit_selling_price or pricing fields. If calculate_price_flag is not used then standard item price will be used.
EXEC dbms_application_info.set_client_info(999); -- Set Organization Id
SET serveroutput on;
DECLARE
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;
l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
l_header_scr_tbl oe_order_pub.header_scredit_tbl_type;
l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
l_request_rec oe_order_pub.request_rec_type;
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
p_api_version_number NUMBER := 1.0;
p_init_msg_list VARCHAR2 (10) := fnd_api.g_false;
p_return_values VARCHAR2 (10) := fnd_api.g_false;
p_action_commit VARCHAR2 (10) := fnd_api.g_false;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (100);
p_header_rec oe_order_pub.header_rec_type
:= oe_order_pub.g_miss_header_rec;
p_old_header_rec oe_order_pub.header_rec_type
:= oe_order_pub.g_miss_header_rec;
p_header_val_rec oe_order_pub.header_val_rec_type
:= oe_order_pub.g_miss_header_val_rec;
p_old_header_val_rec oe_order_pub.header_val_rec_type
:= oe_order_pub.g_miss_header_val_rec;
p_header_adj_tbl oe_order_pub.header_adj_tbl_type
:= oe_order_pub.g_miss_header_adj_tbl;
p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type
:= oe_order_pub.g_miss_header_adj_tbl;
p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
:= oe_order_pub.g_miss_header_adj_val_tbl;
p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
:= oe_order_pub.g_miss_header_adj_val_tbl;
p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
:= oe_order_pub.g_miss_header_price_att_tbl;
p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
:= oe_order_pub.g_miss_header_price_att_tbl;
p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
:= oe_order_pub.g_miss_header_adj_att_tbl;
p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
:= oe_order_pub.g_miss_header_adj_att_tbl;
p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
:= oe_order_pub.g_miss_header_adj_assoc_tbl;
p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
:= oe_order_pub.g_miss_header_adj_assoc_tbl;
p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
:= oe_order_pub.g_miss_header_scredit_tbl;
p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
:= oe_order_pub.g_miss_header_scredit_tbl;
p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
:= oe_order_pub.g_miss_header_scredit_val_tbl;
p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
:= oe_order_pub.g_miss_header_scredit_val_tbl;
p_line_tbl oe_order_pub.line_tbl_type
:= oe_order_pub.g_miss_line_tbl;
p_old_line_tbl oe_order_pub.line_tbl_type
:= oe_order_pub.g_miss_line_tbl;
p_line_val_tbl oe_order_pub.line_val_tbl_type
:= oe_order_pub.g_miss_line_val_tbl;
p_old_line_val_tbl oe_order_pub.line_val_tbl_type
:= oe_order_pub.g_miss_line_val_tbl;
p_line_adj_tbl oe_order_pub.line_adj_tbl_type
:= oe_order_pub.g_miss_line_adj_tbl;
p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type
:= oe_order_pub.g_miss_line_adj_tbl;
p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
:= oe_order_pub.g_miss_line_adj_val_tbl;
p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
:= oe_order_pub.g_miss_line_adj_val_tbl;
p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
:= oe_order_pub.g_miss_line_price_att_tbl;
p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
:= oe_order_pub.g_miss_line_price_att_tbl;
p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
:= oe_order_pub.g_miss_line_adj_att_tbl;
p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
:= oe_order_pub.g_miss_line_adj_att_tbl;
p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
:= oe_order_pub.g_miss_line_adj_assoc_tbl;
p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
:= oe_order_pub.g_miss_line_adj_assoc_tbl;
p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
:= oe_order_pub.g_miss_line_scredit_tbl;
p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
:= oe_order_pub.g_miss_line_scredit_tbl;
p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
:= oe_order_pub.g_miss_line_scredit_val_tbl;
p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
:= oe_order_pub.g_miss_line_scredit_val_tbl;
p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
:= oe_order_pub.g_miss_lot_serial_tbl;
p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
:= oe_order_pub.g_miss_lot_serial_tbl;
p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
:= oe_order_pub.g_miss_lot_serial_val_tbl;
p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
:= oe_order_pub.g_miss_lot_serial_val_tbl;
p_action_request_tbl oe_order_pub.request_tbl_type
:= oe_order_pub.g_miss_request_tbl;
x_header_val_rec oe_order_pub.header_val_rec_type;
x_header_adj_tbl oe_order_pub.header_adj_tbl_type;
x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
x_line_val_tbl oe_order_pub.line_val_tbl_type;
x_line_adj_tbl oe_order_pub.line_adj_tbl_type;
x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;
x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;
x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;
x_action_request_tbl oe_order_pub.request_tbl_type;
x_debug_file VARCHAR2 (100);
l_line_tbl_index NUMBER;
l_msg_index_out NUMBER (10);
l_user_id fnd_user.user_id%TYPE;
l_appl_id fnd_application.application_id%TYPE;
l_resp_id fnd_responsibility.responsibility_id%TYPE;
l_org_id hr_operating_units.organization_id%TYPE;
l_sold_to_org_id hz_cust_accounts.cust_account_id%TYPE;
l_ship_to_org_id hz_cust_site_uses_all.site_use_id%TYPE;
l_cust_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
l_party_id hz_parties.party_id%TYPE;
l_price_list_id qp_list_headers_b.list_header_id%TYPE;
l_primary_salesrep_id ra_salesreps.salesrep_id%TYPE;
l_order_type_id oe_transaction_types_all.transaction_type_id%TYPE;
l_order_line_type_id oe_transaction_types_all.transaction_type_id%TYPE;
l_order_category_code oe_transaction_types_all.order_category_code%TYPE;
l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_currency_code qp_list_headers.currency_code%TYPE;
l_party_site_id hz_party_sites.party_site_id%TYPE;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
fnd_global.apps_initialize (21321, 51889, 660);
-- pass in user_id, responsibility_id, and application_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
l_org_id := fnd_profile.VALUE ('ORG_ID');
-- Get Price List ID, Primary SalesRep, Order Type ID and Order Category Code
BEGIN
SELECT price_list_id
,order_category_code
,default_outbound_line_type_id
INTO l_price_list_id
,l_order_category_code
,l_order_line_type_id
FROM oe_transaction_types_all
WHERE transaction_type_id =
(SELECT transaction_type_id
FROM oe_transaction_types_all
WHERE transaction_type_id IN (SELECT transaction_type_id
FROM oe_transaction_types_tl
WHERE NAME = 'Standard Order'));
fnd_file.put_line (fnd_file.LOG
, 'l_price_list_id = ' || l_price_list_id);
fnd_file.put_line (fnd_file.LOG
, 'l_primary_salesrep_id = ' || l_primary_salesrep_id
);
fnd_file.put_line (fnd_file.LOG
, 'l_order_type_id = ' || l_order_type_id);
fnd_file.put_line (fnd_file.LOG
, 'l_order_category_code = ' || l_order_category_code
);
fnd_file.put_line (fnd_file.LOG
, 'l_order_line_type_id = ' || l_order_line_type_id
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG
,'Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code'
);
DBMS_OUTPUT.put_line
('Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code'
);
--RAISE snrdr_exception;
END;
BEGIN
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books sob, org_organization_definitions ood
WHERE ood.organization_id = l_org_id
AND sob.set_of_books_id = ood.set_of_books_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
x_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.setdebuglevel (5);
-- Use 5 for the most debuging output, I warn you its a lot of data
DBMS_OUTPUT.put_line ('START OF NEW DEBUG');
--This is to CREATE an order header and an order line
--Create Header record
--Initialize header record to missing
l_header_rec := oe_order_pub.g_miss_header_rec;
--l_header_rec.transactional_curr_code := l_currency_code;
l_header_rec.pricing_date := SYSDATE;
--l_header_rec.cust_po_number := 'Test123';
l_header_rec.sold_to_org_id := 123456; -- Customer Id
l_header_rec.ship_to_org_id := 123; -- Ship Site use Id
l_header_rec.invoice_to_org_id := 456; -- Bill Site use Id
l_header_rec.price_list_id := l_price_list_id;
l_header_rec.ordered_date := SYSDATE;
--l_header_rec.shipping_method_code := 'DHL';
l_header_rec.sold_from_org_id := l_org_id;
l_header_rec.salesrep_id := -3;
l_header_rec.order_type_id := l_order_type_id;
l_header_rec.operation := oe_globals.g_opr_create;
-- Fields needed if Quote has to be created.
-- Start Quoting project related fields
/* l_header_rec.quote_date := SYSDATE;
l_header_rec.sales_document_name := 'Order Capture Quotes';
-- Quote Name field on form
l_header_rec.transaction_phase_code := 'N';
--l_header_rec.user_status_code:='DRAFT';
l_header_rec.draft_submitted_flag := 'N';
l_header_rec.source_document_version_number := '1.0';*/
-- End quoting project related fields
--Create Line record
l_line_tbl_index := 1;
-- FIRST LINE RECORD
-- Initialize record to missing
l_line_tbl (l_line_tbl_index) := oe_order_pub.g_miss_line_rec;
-- Line attributes
l_line_tbl (l_line_tbl_index).inventory_item_id := 999999;
l_line_tbl (l_line_tbl_index).ordered_quantity := 10;
-- Set to 'N' when you need to pass unit_selling_price
l_line_tbl (l_line_tbl_index).calculate_price_flag := 'N';
l_line_tbl (l_line_tbl_index).unit_selling_price := 10;
l_line_tbl (l_line_tbl_index).ship_from_org_id := l_org_id;
--l_line_tbl (l_line_tbl_index).subinventory := 'FGI';
l_line_tbl (l_line_tbl_index).operation := oe_globals.g_opr_create;
-- CALL TO PROCESS ORDER Check the return status and then commit.
oe_order_pub.process_order
(p_api_version_number => 1.0
,p_init_msg_list => fnd_api.g_false
,p_return_values => fnd_api.g_false
,p_action_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_header_rec => l_header_rec
,p_line_tbl => l_line_tbl
,p_action_request_tbl => l_action_request_tbl
-- OUT PARAMETERS
, x_header_rec => l_header_rec
,x_header_val_rec => x_header_val_rec
,x_header_adj_tbl => x_header_adj_tbl
,x_header_adj_val_tbl => x_header_adj_val_tbl
,x_header_price_att_tbl => x_header_price_att_tbl
,x_header_adj_att_tbl => x_header_adj_att_tbl
,x_header_adj_assoc_tbl => x_header_adj_assoc_tbl
,x_header_scredit_tbl => x_header_scredit_tbl
,x_header_scredit_val_tbl => x_header_scredit_val_tbl
,x_line_tbl => l_line_tbl
,x_line_val_tbl => x_line_val_tbl
,x_line_adj_tbl => x_line_adj_tbl
,x_line_adj_val_tbl => x_line_adj_val_tbl
,x_line_price_att_tbl => x_line_price_att_tbl
,x_line_adj_att_tbl => x_line_adj_att_tbl
,x_line_adj_assoc_tbl => x_line_adj_assoc_tbl
,x_line_scredit_tbl => x_line_scredit_tbl
,x_line_scredit_val_tbl => x_line_scredit_val_tbl
,x_lot_serial_tbl => x_lot_serial_tbl
,x_lot_serial_val_tbl => x_lot_serial_val_tbl
,x_action_request_tbl => l_action_request_tbl
);
DBMS_OUTPUT.put_line ( 'OM Debug file: '
|| oe_debug_pub.g_dir
|| '/'
|| oe_debug_pub.g_file
);
-- Retrieve messages
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_msg_data
,p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('message is: ' || l_msg_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index_out);
END LOOP;
-- Check the return status
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Process Quote Sucess');
DBMS_OUTPUT.put_line ('Quote Number ' || l_header_rec.quote_number);
DBMS_OUTPUT.put_line ('Quote Header_id ' || l_header_rec.header_id);
ELSE
DBMS_OUTPUT.put_line ('Process Quote Failed');
END IF;
-- START Order Quote WORKFLOW SUBMISSION
-- Call Workflow to Submit Draft
/* IF l_return_status = fnd_api.g_ret_sts_success
THEN
oe_negotiate_wf.submit_draft (l_header_rec.header_id, l_return_status);
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_msg_data
,p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('message is: ' || l_msg_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index_out);
END LOOP;
-- Call customer accepted workflow
IF l_return_status = fnd_api.g_ret_sts_success
THEN
oe_negotiate_wf.customer_accepted (l_header_rec.header_id
,l_return_status
);
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_msg_data
,p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('message is: ' || l_msg_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index_out);
END LOOP;
--IF l_return_status = = fnd_api.g_ret_sts_success THEN
-- OE_Order_Book_Util.Complete_Book_Eligible
--END IF;
ELSE
DBMS_OUTPUT.put_line ('Customer Accepted Workflow Failed');
END IF;
ELSE
DBMS_OUTPUT.put_line ('Submit Draft Workflow Failed');
END IF;*/
-- END Order Quote WORKFLOW SUBMISSION
END;
/
COMMIT ; 
Hi,
ReplyDeleteWe have a requirement where Order has to be created for which customer is paying theough Credit Card and not the PO. Could you kindly show me how and what credit card details have to be passed for the Order API to consider CC input.
Hi,
ReplyDeleteNice article,
I am able to create standard code as per your suggestion, but when I try oe_negotiate_wf.submit_draft, oe_negotiate_wf.customer_accepted API First One return status 'S' and second return 'E'
Can you please suggest any changes required, or need to verify.
You can suggest on my mail id also: akil.pariyani@gmail.com
Thanks,
Akil
:)
ReplyDeleteHo w to update the quote status to "LOST" using API
ReplyDeleteI could not able to copy the code
ReplyDeleteI need API to create Order types for around 400+ types in OM. Is there api available for the same.
ReplyDelete