Wednesday, March 30, 2011

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

0 comments:

About This Blog

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Back to TOP