Thursday, October 19, 2017

OM INTERFACES ORDER IMPORT API

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;
/

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...