Match Import Data to Order Management Data
Make sure the data you import matches the structure and data type that Order Management uses.
Its important that the data you import or integrate for customer, ship-to, and bill-to attributes is compatible with data in the Order Management database. Use SQL to get data from the Order Management database, then modify your import data to make sure it matches Order Management database requirements for data type and structure.
Get Sold-To Customer
SELECT dha.ORDER_NUMBER ,
dha.source_order_number,
dha.SOLD_TO_PARTY_ID ,
dha.STATUS_CODE ,
hz.PARTY_ID ,
hz.PARTY_NUMBER ,
hz.PARTY_NAME
FROM fusion.doo_headers_all dha,
fusion.HZ_PARTIES HZ
WHERE dha.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
-- AND status_code <> 'DOO_REFERENCE'
-- AND Submitted_Flag = 'Y' -- is this the active/submitted version
and hz.PARTY_ID =dha.SOLD_TO_PARTY_ID
Get Ship-To Details on Order Header
SELECT SOURCE_ORDER_NUMBER,
SOLD_TO_CUSTOMER_ID,
SOLD_TO_PARTY_ID ,
HZP.PARTy_name
||
' '
||
HZP.PARTY_NUMBER "Sold to Customer",
DOA.ADDRESS_USE_TYPE ,
hza.account_number ,
hzp_ship_to.party_name ,
hza.account_name ,
doa.PARTY_SITE_ID ,
hzl.ADDRESS1 ,
hzl.ADDRESS2 ,
hzl.ADDRESS3 ,
hzl.ADDRESS4 ,
hzl.CITY ,
hzl.POSTAL_CODE ,
hzl.STATE ,
hzl.COUNTRY
FROM FUSION.HZ_PARTIES HZP ,
FUSION.HZ_PARTIES HZP_SHIP_TO ,
FUSION.DOO_HEADERS_aLL DHA ,
fusion.DOO_ORDER_ADDRESSES DOA ,
fusion.HZ_CUST_ACCOUNTS HZA ,
fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,
fusion.HZ_PARTY_SITES hzps ,
fusion.hz_locations HZL
WHERE HZP.PARTY_ID = DHA.SOLD_TO_PARTY_ID
AND dha.header_id = doa.header_id (+)
AND
(
doa.ADDRESS_USE_TYPE = 'SHIP_TO'
OR doa.ADDRESS_USE_TYPE IS NULL
)
AND doa.party_site_id = hzps.party_site_id (+)
AND hzcasa.PARTY_SITE_ID (+) = hzps.PARTY_SITE_ID
AND hzps.party_id = hzp_ship_to.party_id (+)
AND HZcasa.CUST_ACCounT_ID = hza.CUST_ACCOUNT_ID (+)
AND hzps.location_id = hzl.location_id (+)
AND DHA.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
AND DHA.status_code <> 'DOO_REFERENCE'
AND DHA.Submitted_Flag = 'Y' -- is this the active/submitted version
Get Bill-To Details on Order Header
SELECT SOURCE_ORDER_NUMBER,
SOLD_TO_CUSTOMER_ID,
SOLD_TO_PARTY_ID ,
HZP.PARTy_name
||
' '
||
HZP.PARTY_NUMBER "Sold to Customer",
DOA.ADDRESS_USE_TYPE ,
hza.account_number ,
hzp_ship_to.party_name ,
hza.account_name ,
doa.PARTY_SITE_ID ,
hzl.ADDRESS1 ,
hzl.ADDRESS2 ,
hzl.ADDRESS3 ,
hzl.ADDRESS4 ,
hzl.CITY ,
hzl.POSTAL_CODE ,
hzl.STATE ,
hzl.COUNTRY
FROM FUSION.HZ_PARTIES HZP ,
FUSION.HZ_PARTIES HZP_SHIP_TO ,
FUSION.DOO_HEADERS_aLL DHA ,
fusion.DOO_ORDER_ADDRESSES DOA ,
fusion.HZ_CUST_ACCOUNTS HZA ,
fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,
fusion.HZ_PARTY_SITES hzps ,
fusion.hz_locations HZL
WHERE HZP.PARTY_ID = DHA.SOLD_TO_PARTY_ID
AND dha.header_id = doa.header_id (+)
AND
(
doa.ADDRESS_USE_TYPE = 'SHIP_TO'
OR doa.ADDRESS_USE_TYPE IS NULL
)
AND doa.party_site_id = hzps.party_site_id (+)
AND hzcasa.PARTY_SITE_ID (+) = hzps.PARTY_SITE_ID
AND hzps.party_id = hzp_ship_to.party_id (+)
AND HZcasa.CUST_ACCounT_ID = hza.CUST_ACCOUNT_ID (+)
AND hzps.location_id = hzl.location_id (+)
AND DHA.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
AND DHA.status_code <> 'DOO_REFERENCE'
AND DHA.Submitted_Flag = 'Y' -- is this the active/submitted version
Get Ship-To and Bill-To Details on Order Line
SELECT dha.Source_order_number ,
dha.order_number ,
dha.submitted_Flag ,
dfla.SHIP_TO_PARTY_ID ,
dfla.SHIP_TO_PARTY_SITE_ID,
dfla.BILL_TO_CUSTOMER_ID ,
dfla.BILL_TO_SITE_USE_ID
FROM Fusion.DOO_headers_all dha,
Fusion.DOO_fulfill_lines_all dfla
WHERE dha.header_id = dfla.header_id
AND dha.source_order_number = '&ENTER SOURCE_ORDER_NUMBER'