インポート・データをOrder Managementデータと照合
インポートするデータが、オーダー管理が使用する構造とデータ型と一致していることを確認してください。
顧客、出荷先および請求先属性用にインポートまたは統合するデータは、オーダー管理データベースのデータと互換性があることが重要です。 SQLを使用してオーダー管理データベースからデータを取得し、インポート・データを変更して、データ型および構造に対するオーダー管理データベース要件に一致するようにします。
販売先顧客の取得
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
オーダー・ヘッダーの出荷先詳細の取得
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
オーダー・ヘッダーの請求先詳細の取得
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
オーダー明細の出荷先および請求先詳細の取得
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'