Order Import Interface (Sales Order Conversion)
Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity
Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES
Concurrent Program:
Order Import
Validations:
Check for sold_to_org_id.
If does not exist, create new customer by calling create_new_cust_info API.
Check for sales_rep_id. Should exist for a booked order.
Ordered_date should exist (header level)
Delivery_lead_time should exist (line level)
Earliest_acceptable_date should exist.
Freight_terms should exist
Notes:
During import of orders, shipping tables are not populated.
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.
Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders
Order Import API
OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.
STAGING TABLES
CREATE TABLE ST_OE_HEADERS_IFACE_ALL (
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORG_ID NUMBER,
ORDERED_DATE DATE,
ORDER_TYPE_ID NUMBER,
PRICE_LIST_ID NUMBER,
TRANSACTIONAL_CURR_CODE VARCHAR2 (15),
SALESREP_ID NUMBER,
PAYMENT_TERM_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
INVOICE_TO_ORG_ID NUMBER,
CUSTOMER_ID NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
REQUEST_DATE DATE,
SOLD_FROM_ORG_ID NUMBER);
CREATE TABLE ST_OE_LINES_IFACE_ALL (
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORIG_SYS_LINE_REF VARCHAR2 (50),
ORG_ID NUMBER,
LINE_TYPE_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
SCHEDULE_SHIP_DATE DATE,
ORDERED_QUANTITY NUMBER,
ORDER_QUANTITY_UOM VARCHAR2 (3),
SOLD_TO_ORG_ID NUMBER,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
INVOICE_TO_ORG_ID NUMBER,
PRICE_LIST_ID NUMBER,
UNIT_LIST_PRICE NUMBER,
UNIT_SELLING_PRICE NUMBER,
PAYMENT_TERM_ID NUMBER,
SALESREP_ID NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
REQUEST_DATE DATE,
SOLD_FROM_ORG_ID NUMBER,
LINES_NUMBER NUMBER);
create table ST_OE_ACTIONS_IFACE_ALL (ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50),
ORG_ID NUMBER,OPERATION_CODE VARCHAR2(50));
SQL * LOADER
LOAD DATA INFILE *
INTO TABLE ST_OE_ACTIONS_IFACE_ALL
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ORDER_SOURCE_ID,ORIG_SYS_DOCUMENT_REF,ORG_ID,OPERATION_CODE)
INTO TABLE ST_OE_HEADERS_IFACE_ALL
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORG_ID,
ORDERED_DATE "TO_DATE(SYSDATE)",
ORDER_TYPE_ID,
PRICE_LIST_ID,
TRANSACTIONAL_CURR_CODE,
SALESREP_ID,
PAYMENT_TERM_ID,
SOLD_TO_ORG_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
CUSTOMER_ID,
CREATED_BY,
CREATION_DATE "TO_DATE(SYSDATE)",
LAST_UPDATED_BY,
LAST_UPDATE_DATE "TO_DATE(SYSDATE)",
REQUEST_DATE "TO_DATE(SYSDATE)",
SOLD_FROM_ORG_ID)
INTO TABLE ST_OE_LINES_IFACE_ALL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORG_ID,
LINE_TYPE_ID,
INVENTORY_ITEM_ID,
SCHEDULE_SHIP_DATE "TO_DATE(SYSDATE)",
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SOLD_TO_ORG_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
PRICE_LIST_ID,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
PAYMENT_TERM_ID,
SALESREP_ID,
CREATED_BY ,
CREATION_DATE "TO_DATE(SYSDATE)",
LAST_UPDATED_BY,
LAST_UPDATE_DATE "TO_DATE(SYSDATE)" ,
REQUEST_DATE "TO_DATE(SYSDATE)",
SOLD_FROM_ORG_ID,
LINES_NUMBER)
CREATE OR REPLACE PROCEDURE MOT_OM_CONV_PROC(ERRBUF OUT VARCHAR2,
RETCODE OUT vARCHAR2) IS
CURSOR CUR_HEADERs IS SELECT * FROM ST_OE_HEADERS_IFACE_ALL;
CURSOR CUR_LINES IS SELECT * FROM ST_OE_LINES_IFACE_ALL;
err_control EXCEPTION;
err_msg varchar2(250);
err_flag number;
ID NUMBER;
VERROR_FLAG OE_HEADERS_IFACE_ALL.ERROR_FLAG%TYPE;
VINTERFACE_STATUS OE_HEADERS_IFACE_ALL.INTERFACE_STATUS%TYPE;
VCREATION_DATE OE_HEADERS_IFACE_ALL.CREATION_DATE%TYPE;
VLAST_UPDATE_DATE OE_HEADERS_IFACE_ALL.LAST_UPDATE_DATE%TYPE;
VREQUEST_ID OE_HEADERS_IFACE_ALL.REQUEST_ID%TYPE;
VORIG_SYS_DOCUMENT_REF OE_HEADERS_IFACE_ALL.ORIG_SYS_DOCUMENT_REF%TYPE;
VORIG_SYS_LINE_REF OE_LINES_IFACE_ALL.ORIG_SYS_LINE_REF%TYPE;
VORDER_SOURCE_ID OE_ORDER_SOURCES.ORDER_SOURCE_ID%TYPE;
BEGIN
ID :=FND_GLOBAL.USER_ID;
VERROR_FLAG :=NULL;
VINTERFACE_STATUS :=NULL;
VCREATION_DATE :=SYSDATE;
VLAST_UPDATE_DATE :=SYSDATE;
VORIG_SYS_DOCUMENT_REF :='Order1';
VORIG_SYS_LINE_REF :='Line1';
ERR_MSG :='';
err_flag :=0;
VORDER_SOURCE_ID :=1047;
FOR V1 IN CUR_MOT_HEADERs
LOOP
BEGIN
IF order_valid(v1.ORDER_TYPE,v1.ORDER_TYPE_ID,'ORDER')='ERROR' THEN
err_msg:='Order_type or order_type_id must be valid';
err_flag:=1;
end if ;
IF price_item_validate(V1.PRICE_LIST_ID,NULL,'H')='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Price list must be valid';
else
err_msg:='Price list must be valid';
err_flag:=1;
end if ;
end if;
IF payment_term_validate(V1.PAYMENT_TERM_id)='ERROR' THEN
if err_flag=1 then
err_msg:=err_msg||'&'||'Invalid payment term';
else
err_msg:='Invalid payment term';
err_flag:=1;
end if ;
end if ;
IF validate_customer(V1.CUSTOMER_NAME,v1.CUSTOMER_ID,v1.SOLD_TO_ORG_ID)='ERROR' then
IF err_flag=1 then
err_msg:=err_msg||'&'||' iNVALID CUSTOMER or sold to org id ';
else
err_msg:=' iNVALID CUSTOMER or sold to org id ';
err_flag:=1;
end if ;
end if;
if invoice_to_ship_to(v1.SHIP_TO_ORG_ID,v1.invoice_to_org_id)='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'INVALID SHIP TO OR INVOICE TO';
else
err_msg:='INVALID SHIP TO OR INVOICE TO';
err_flag:=1;
end if ;
end if;
-- raise exception --
IF err_flag=1 then -- RAISE ERROR MSG --
dbms_output.put_line('raising error');
raise err_control;
ELSE -- INSERT INTO INTERFCAE TABLES TABLE --
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORDER_SOURCE,
ORDERED_DATE,
ORDER_TYPE,
ORDER_TYPE_ID,
PRICE_LIST_ID,
SALESREP_ID,
PAYMENT_TERM_id,
CUSTOMER_NAME,
CUSTOMER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
OPERATION_CODE,
ERROR_FLAG,
INTERFACE_STATUS,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID
)
VALUES
(
VORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
v1.ORDER_SOURCE,
v1.ORDERED_DATE,
v1.ORDER_TYPE,
v1.ORDER_TYPE_ID,
v1.PRICE_LIST_ID,
v1.SALESREP_ID,
v1.PAYMENT_TERM_id,
v1.CUSTOMER_NAME,
v1.CUSTOMER_ID,
ID,
VCREATION_DATE,
ID,
vLAST_UPDATE_DATE,
v1.REQUEST_ID,
v1.OPERATION_CODE,
VERROR_FLAG,
VINTERFACE_STATUS,
v1.SOLD_TO_ORG_ID,
v1.SHIP_TO_ORG_ID,
v1.INVOICE_TO_ORG_ID
);
INSERT INTO OE_ACTIONS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
OPERATION_CODE
)
VALUES
(
VORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
'BOOK_ORDER'
);
END IF ;
EXCEPTION
WHEN err_control THEN
update MOT_stage_HEADER set error_msg=err_msg;
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
ERR_MSG:=SUBSTR(SQLERRM,1,25);
UPDATE MOT_stage_HEADER set error_msg=err_msg;
END;
END LOOP ;
--- end of header processing ------------------
err_flag:=0;
FOR V2 in CUR_MOT_LINES
LOOP
begin
IF order_valid(v2.LINE_type,v2.LINE_TYPE_ID,'LINE')='ERROR' THEN
err_msg:='Order_type or order_type_id must be valid';
err_flag:=1;
END IF ;
dbms_output.put_line('price_item_validate('||V2.PRICE_LIST_ID||','||v2.inventory_item_id||',L');
IF price_item_validate(V2.PRICE_LIST_ID,v2.inventory_item_id,'L')='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Price list must be valid';
else
err_msg:='Price list must be valid';
err_flag:=1;
END IF ;
END IF ;
IF payment_term_validate(V2.PAYMENT_TERM_id)='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Invalid payment term';
ELSE
err_msg:='Invalid payment term';
err_flag:=1;
end if ;
end if ;
IF invoice_to_ship_to(v2.SHIP_TO_ORG_ID,v2.invoice_to_org_id)='ERROR' THEN
IF err_flag =1 then
err_msg:=err_msg||'&'||'INVALID SHIP TO OR INVOICE TO';
else
err_msg:='INVALID SHIP TO OR INVOICE TO';
err_flag:=1;
end if ;
end if;
IF uom_validate(V2.order_quantity_uom)='ERROR' THEN
IF err_flag =1 then
err_msg:=err_msg||'&'||'INVALID UNIT OF MEASURE';
else
err_msg:='INVALID UNIT OF MEASURE';
err_flag:=1;
end if ;
end if;
-- raise exception --
IF err_flag=1 then -- RAISE ERROR FLAG --
raise err_control;
ELSE -- INSERT TO INTERFACE TABLES --
INSERT INTO OE_LINES_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
LINE_TYPE,
LINE_TYPE_ID,
INVENTORY_ITEM_ID,
PAYMENT_TERM_id,
PRICE_LIST_ID,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
REQUEST_DATE,
SALESREP_ID,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
CALCULATE_PRICE_FLAG,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
OPERATION_CODE,
ERROR_FLAG,
INTERFACE_STATUS,
REQUEST_ID
)
VALUES
(
V2.ORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
VORIG_SYS_LINE_REF,
V2.LINE_TYPE,
V2.LINE_TYPE_ID,
V2.INVENTORY_ITEM_ID,
V2.PAYMENT_TERM_id,
V2.PRICE_LIST_ID,
V2.ORDERED_QUANTITY,
V2.ORDER_QUANTITY_UOM,
V2.REQUEST_DATE,
V2.SALESREP_ID,
V2.UNIT_LIST_PRICE,
V2.UNIT_SELLING_PRICE,
V2.CALCULATE_PRICE_FLAG,
V2.SHIP_TO_ORG_ID,
V2.INVOICE_TO_ORG_ID,
ID,
VCREATION_DATE,
ID,
VLAST_UPDATE_DATE,
V2.OPERATION_CODE,
VERROR_FLAG,
VINTERFACE_STATUS,
V2.REQUEST_ID
);
END IF ;
EXCEPTION
WHEN err_control THEN
update MOT_stage_lines set error_msg=err_msg;
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
ERR_MSG:=SUBSTR(SQLERRM,1,25);
UPDATE MOT_stage_lines set error_msg=err_msg;
END;
END LOOP ;
END;
/
