OM INTERFACES
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 Interface tables: OE_HEADERS_IFACE_ALL Base tables: OE_ORDER_HEADERS_ALL 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;
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES
0 comments:
Post a Comment