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'