rder Import Interface (Sales Order Conversion)
Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
Pre-requisites
1. Order Type
2. Line Type
3. Items
4. Customers
5. Ship Method/ Freight Carrier
6. Sales Person
7. Sales Territories
8. Customer Order Holds
9. Sub Inventory/ Locations
10. On hand Quantity
Interface tables
1. OE_HEADERS_IFACE_ALL
2. OE_LINES_IFACE_ALL
3. OE_ACTIONS_IFACE_ALL
4. OE_ORDER_CUST_IFACE_ALL
5. OE_PRICE_ADJS_IFACE_ALL
6. OE_PRICE_ATTS_IFACE_ALL
Base tables:
1. OE_ORDER_HEADERS_ALL
2. OE_ORDER_LINES_ALL
3. Pricing tables: QP_PRICING_ATTRIBUTES
Concurrent Program
1. Order Import
Validations
1. Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
2. Check for sales_rep_id. Should exist for a booked order.
3. Ordered_date should exist (header level)
4. Delivery_lead_time should exist (line level)
5. Earliest_acceptable_date should exist.
6. Freight_terms should exist
Notes
1. During import of orders, shipping tables are not populated.
2. If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.
3. Orders can be categorized based on their status: 1. Entered orders 2. Booked orders 3. Closed orders
4. Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders
Some important columns that need to populated in the OE_HEADERS_IFACE_ALL tables
1. ORIG_SYS_DOCUMENT_REF
2. ORDER_SOURCE
3. CONVERSION_RATE
4. ORG_ID
5. ORDER_TYPE_ID
6. PRICE_LIST
7. SOLD_FROM_ORG_ID
8. SOLD_TO_ORG_ID
9. SHIP_TO_ORG_ID
10. SHIP_FROM_ORG_ID
11. CUSTOMER_NAME
12. INVOICE_TO_ORG_ID
13. OPERATION_CODE
Some important columns that need to populated in the OE_LINES_IFACE_ALL tables
1. ORDER_SOURCE_ID
2. ORIG_SYS_DOCUMENT_REF
3. ORIG_SYS_LINE_REF
4. ORIG_SYS_SHIPMENT_REF
5. INVENTORY_ITEM_ID
6. LINK_TO_LINE_REF
7. REQUEST_DATE
8. DELIVERY_LEAD_TIME
9. DELIVERY_ID
10. ORDERED_QUANTITY
11. ORDER_QUANTITY_UOM
12. SHIPPING_QUANTITY
13. PRICING_QUANTITY
14. PRICING_QUANTITY_UOM
15. SOLD_FROM_ORG_ID
16. SOLD_TO_ORG_ID
17. INVOICE_TO_ ORG_ID
18. SHIP_TO_ORG_ID
19. PRICE_LIST_ID
20. PAYMENT_TERM_ID
Sample Script:
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 := 308; -- OPERATING UNIT
l_no_orders NUMBER := 1; -- NO OF ORDERS
-- 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);
BEGIN
-- INITIALIZATION REQUIRED FOR R12
mo_global.set_policy_context ('S', l_org);
mo_global.init ('ONT');
-- 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 (user_id => 2083, resp_id => 21623, resp_appl_id => 660);
-- 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.transactional_curr_code := 'AUD';
l_header_rec.pricing_date := SYSDATE;
l_header_rec.cust_po_number := 'TSTPO30';
l_header_rec.sold_to_org_id := 1006685;
l_header_rec.price_list_id := 33019;
l_header_rec.ordered_date := SYSDATE;
l_header_rec.shipping_method_code := '000001_Toll IPEC_T_2T5DGRD';
l_header_rec.sold_from_org_id := 308;
l_header_rec.ship_from_org_id := 381;
l_header_rec.ship_to_org_id := 2005460;
l_header_rec.salesrep_id := 100000069;
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.order_type_id := 5389;
-- REQUIRED HEADER DFF INFORMATIONS
l_header_rec.attribute1 := 193; -- Entering Branch
l_header_rec.attribute3 := 'Y'; -- Indexation applicable
l_header_rec.attribute5 := '2.5'; -- Indexation Tolerance percentage
l_header_rec.attribute7 := 100000045; -- Field Sales representative
l_header_rec.attribute11 := '100'; -- Indexation Applicability
-- 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; -- Mandatory Operation to Pass
l_line_tbl (1).inventory_item_id := 102775;
l_line_tbl (1).ordered_quantity := 1;
l_line_tbl (1).ship_from_org_id := 381;
l_line_tbl (1).subinventory := 'SELLABLE';
-- REQUIRED LINE DFF INFORMATIONS
l_line_tbl (1).attribute2 := '20.99998'; -- Gross Margin
l_line_tbl (1).attribute3 := '2.493288'; -- Business Cost
l_line_tbl (1).attribute10 := '1000'; -- Original Cust Requested Qty
l_line_tbl (1).attribute11 := '662.772'; -- Baseline Margin
l_line_tbl (1).attribute16 := 'DBP'; -- Buy Price Basis
FOR i IN 1 .. l_no_orders
LOOP -- BEGIN LOOP
-- CALLTO PROCESS ORDER API
oe_order_pub.
process_order (
p_org_id => l_org,
p_operating_unit => NULL,
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 ('Sales Order Successfully Created');
END IF;
COMMIT;
ELSE
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('Failed to Create Sales Order');
END IF;
ROLLBACK;
END IF;
END LOOP;
-- DISPLAY RETURN STATUS FLAGS
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.
put_line (
'Process Order Return Status is: ========>' || l_return_status);
DBMS_OUTPUT.
put_line ('Process Order msg data is: ===========>' || l_msg_data);
DBMS_OUTPUT.
put_line ('Process Order Message Count is:=======>' || l_msg_count);
DBMS_OUTPUT.
put_line (
'Sales Order Created is:===============>'
|| TO_CHAR (l_header_rec_out.order_number));
DBMS_OUTPUT.
put_line (
'Booked Flag for the Sales Order is:======>'
|| l_header_rec_out.booked_flag);
DBMS_OUTPUT.
put_line (
'Header_id for the Sales Order is:========>'
|| l_header_rec_out.header_id);
DBMS_OUTPUT.
put_line (
'Flow_Status_Code For the Sales Order 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);
oe_debug_pub.debug_off;
END IF;
END;
Source: http://blog.csdn.net/pan_tian/article/details/8266466
http://oracletechnofunctional.webs.com/apps/blog/show/4689245-how-to-import-sales-order-using-interface-api
No comments:
Post a Comment