Open Sales Order Upload from Legacy system to Oracle Apps

In order to upload open sales order following steps need to be performed:

1. Create a staging table as below.

2. Populate the staging table from user provided data in excel sheet.

3. Update the null field values based on the data provided as shown below.

4. Populate the data from the staging table into the Interface tables "OE_HEADERS_IFACE_ALL" and "OE_LINES_IFACE_ALL" as shown below.

5. Run the order Import Request from the front end Apps Screen.

/**************** Create a Staging Table *******************/
CREATE TABLE XX_SO_UPL
(
LEGACY_SO_NUMBER VARCHAR2(100 BYTE),
ORDER_DATE DATE,
CUSTOMER_NO VARCHAR2(20 BYTE),
CUSTOMER_ID NUMBER,
LINE_NUM NUMBER,
ICODE VARCHAR2(20 BYTE),
ITEM VARCHAR2(100 BYTE),
UOM VARCHAR2(10 BYTE),
CURRENCY VARCHAR2(5 BYTE),
QUANTITY NUMBER,
ORGANIZATION_NAME VARCHAR2(100 BYTE),
PRICE_UNIT NUMBER,
PRICE_LIST_ID NUMBER,
PAYMENT_TERMS VARCHAR2(100 BYTE),
SCHEDULED_SHIP_DATE DATE,
REQUEST_DATE DATE,
LINE_TYPE NUMBER,
INVENTORY_ITEM_ID NUMBER,
SHIP_FROM_ORGID NUMBER,
TERM_ID NUMBER,
ORDER_TYPE_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
BILL_TO_ORG_ID NUMBER
)

/************** Get Ship_to_Org_id, Invoince_to_Org_id(Bill_to_org_id) **************/

UPDATE XX_SO_UPL M SET M.SHIP_TO_ORG_ID = (
SELECT K.SITE_USE_ID FROM
(
SELECT * FROM
(select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'SHIP_TO' and s.SHIP_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) = 1
UNION ALL
select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'SHIP_TO' and s.SHIP_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) > 1 )
) K
WHERE K.CUST_NO = M.CUSTOMER_NO) WHERE M.SHIP_TO_ORG_ID IS NULL


UPDATE XX_SO_UPL M SET M.BILL_TO_ORG_ID = (
SELECT K.SITE_USE_ID FROM
(
SELECT * FROM
(select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'BILL_TO' and s.BILL_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) = 1
UNION ALL
select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'BILL_TO' and s.BILL_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) > 1 )
) K
WHERE K.CUST_NO = M.CUSTOMER_NO) WHERE M.BILL_TO_ORG_ID IS NULL

/***************** Get order_type_id, line_type_id ****************************/

update XX_SO_UPL m set order_type_id =
(SELECT TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_TL
WHERE UPPER(NAME) = TRIM(UPPER(m.ORGANIZATION_NAME))) where m.order_type_id is null

UPDATE XX_SO_UPL M SET M.LINE_TYPE =
(
SELECT
A.DEFAULT_OUTBOUND_LINE_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL A
WHERE M.ORDER_TYPE_ID = A.TRANSACTION_TYPE_ID
) WHERE M.LINE_TYPE IS NULL

/***************** Get Ship_From_org_id, Term_id ****************************/

update XX_SO_UPL m set m.ship_FROM_ORGID =
(
SELECT o.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS o
WHERE o.ORGANIZATION_NAME = m.ORGANIZATION_NAME) where m.ship_FROM_ORGID is null

UPDATE XX_SO_UPL S SET TERM_ID =
(SELECT TERM_ID
FROM RA_TERMS_TL
WHERE UPPER(trim(NAME)) = UPPER(TRIM(S.PAYMENT_TERMS))) WHERE S.TERM_ID IS NULL

/************* Get order source id******************/
SELECT ORDER_SOURCE_ID
FROM OE_ORDER_SOURCES
WHERE NAME = 'Online'

/**************** Inserting into Headers Interface table ************************/

truncate table ont.OE_HEADERS_IFACE_ALL

INSERT INTO OE_HEADERS_IFACE_ALL
( ORDER_SOURCE_ID
,ORIG_SYS_DOCUMENT_REF
,ORG_ID
-- ,SOLD_FROM_ORG_ID
,SHIP_FROM_ORG_ID
,ORDERED_DATE
,ORDER_TYPE_ID
,SOLD_TO_ORG_ID
,PAYMENT_TERM_ID
,OPERATION_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CUSTOMER_PO_NUMBER
,PRICE_LIST_ID
,CONTEXT
,ATTRIBUTE19
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,customer_number
,salesrep_id
,order_category
,TRANSACTION_PHASE_CODE
,TRANSACTIONAL_CURR_CODE
,order_type
)
select
0
,OE_ORDER_HEADERS_S.NEXTVAL
,81
,ship_from
,ord_date
,ord_type
,sold_to_org_id
,term_id
,'INSERT'
,1110
,SYSDATE --CREATION_DATE
,1110 --LAST_UPDATED_BY
,SYSDATE --LAST_UPDATE_DATE
,CUST_PO
,PRICE_LIST
,NULL
,'LEGACY SO NUMBER'
,ship_to
,bill_to
,cust_no
,-3
,'ORDER'
,'F'
,CURR
,order_type
from
(select distinct
-- ,H.BILL_FROM_ORG_ID
H.SHIP_FROM_ORGID ship_from
,H.ORDER_DATE ord_date
,H.ORDER_TYPE_ID ord_type
,h.SOLD_TO_ORG_ID sold_to_org_id
,H.TERM_ID term_id
,H.LEGACY_SO_NUMBER CUST_PO
,H.SHIP_TO_ORG_ID ship_to
,H.BILL_TO_ORG_ID bill_to
,h.CUSTOMER_ID sold_to
,h.CUSTOMER_NO cust_no
,decode(h.currency,'USD',7085,'EUR',7092,'SEK',7093) PRICE_LIST
,H.CURRENCY CURR
,h.ORGANIZATION_NAME order_type
FROM XX_so_upl H
where h.CUSTOMER_ID <>0

/*****************Inserting into Lines Interface table ***********/

truncate table ont.OE_LINES_IFACE_ALL

INSERT INTO OE_LINES_IFACE_ALL L
(
ORDER_SOURCE_ID
,ORIG_SYS_DOCUMENT_REF
,ORIG_SYS_LINE_REF
,LINE_NUMBER
,INVENTORY_ITEM_ID
,ORDERED_QUANTITY
,ORG_ID
,PRICING_QUANTITY
,UNIT_SELLING_PRICE
,UNIT_LIST_PRICE
,PRICE_LIST_ID
,PAYMENT_TERM_ID
,SCHEDULE_SHIP_DATE
,REQUEST_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CALCULATE_PRICE_FLAG
,SOLD_TO_ORG_ID
,SHIP_FROM_ORG_ID
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,SALESREP_ID
,OPERATION_CODE
,LINE_TYPE_ID
)
SELECT
0
,o.ORIG_SYS_DOCUMENT_REF
,OE_ORDER_LINES_S.NEXTVAL
,L.LINE_NUM
,l.INVENTORY_ITEM_ID
,L.QUANTITY
,81
,L.QUANTITY
,L.PRICE_UNIT
,L.PRICE_UNIT
,decode(l.currency,'USD',7085,'EUR',7092,'SEK',7093) PRICE_LIST
,l.TERM_ID
,TO_DATE(L.ORDER_DATE,'DD-MON-RRRR')
,TO_DATE(L.ORDER_DATE,'DD-MON-RRRR')
,1110
,SYSDATE
,1110
,SYSDATE
,'N'
,L.SOLD_TO_ORG_ID
,L.SHIP_FROM_ORGID
,L.SHIP_TO_ORG_ID
,L.BILL_TO_ORG_ID
,-3
,'INSERT'
,L.LINE_TYPE
FROM
XX_SO_UPL L, OE_HEADERS_IFACE_ALL o
where l.LEGACY_SO_NUMBER=o.CUSTOMER_PO_NUMBER
and l.CUSTOMER_NO = o.CUSTOMER_NUMBER
and o.ORDER_TYPE_ID = l.ORDER_TYPE_ID