Compare Import Data to Oracle Database Data

Query the Oracle database to determine whether your import data meets database requirements.

Error with SOLD_TO_PARTY_ID

Assume you encounter an error.

An order was not created because a value was not provided for the required attribute SOLD_TO_PARTY_ID in the source order with the following details: source order 12345. Provide a value for SOLD_TO_PARTY_ID, and resubmit the order.

Run SQL to identify the correct data you should use.

SELECT  HZ.PARTY_ID                            ,
        CUSTOMERACCOUNTSITEPEO.CUST_ACCOUNT_ID ,
        HZ.PARTY_NAME                          ,
 lOCATIONPEO.ADDRESS_STYLE              ,
 lOCATIONPEO.COUNTRY ||','|| LOCATIONPEO.ADDRESS1 ||','|| LOCATIONPEO.CITY ||','|| LOCATIONPEO.POSTAL_CODE LOCATION       ,
        CUSTOMERACCOUNTSITEPEO.STATUS "Account Site Status"     ,
        CUSTOMERACCOUNTSITEPEO.BILL_TO_FLAG                     ,
        CUSTOMERACCOUNTSITEPEO.SHIP_TO_FLAG                     ,
        CUSTOMERACCOUNTSITEUSEPEO.SITE_USE_CODE                 ,
        CUSTOMERACCOUNTSITEUSEPEO.PRIMARY_FLAG                  ,
        CUSTOMERACCOUNTSITEUSEPEO.STATUS "Cust_Site_Use Status" ,
        CUSTOMERACCOUNTSITEUSEPEO.ORIG_SYSTEM_REFERENCE         ,
        CUSTOMERACCOUNTSITEUSEPEO.CUST_ACCT_SITE_ID             ,
        CUSTOMERACCOUNTSITEPEO.SET_ID
FROM    fusion.HZ_PARTIES HZ                                 ,
        fusion.HZ_PARTY_SITES PartySitePEO                   ,
        fusion.HZ_LOCATIONS LocationPEO                      ,
        fusion.HZ_CUST_ACCT_SITES_ALL CustomerAccountSitePEO ,
        fusion.HZ_CUST_SITE_USES_ALL CustomerAccountSiteUsePEO
WHERE   HZ.PARTY_ID = PARTYSITEPEO.PARTY_ID
        AND
        (PARTYSITEPEO.LOCATION_ID = LOCATIONPEO.LOCATION_ID)
        AND
        (PARTYSITEPEO.PARTY_SITE_ID = CUSTOMERACCOUNTSITEPEO.PARTY_SITE_ID)
        AND
        (CUSTOMERACCOUNTSITEPEO.CUST_ACCT_SITE_ID = CUSTOMERACCOUNTSITEUSEPEO.CUST_ACCT_SITE_ID)
        and upper(HZ.PARTY_NAME) like upper('&CUSTOMER_NAME%')

Error with BILL_TO

Assume you encounter an error.

An order was not created because a value was not provided for the required attribute BILL_TO_CUSTOMER_ID in the source order with the following details: source order XXXXXXXXX, source order line 1, source order schedule 1. Provide a value for BILL_TO_CUSTOMER_ID, and resubmit the order.

Or this error.

An order was not created because a value was not provided for the required attribute BILL_TO_SITE_USE_ID in the source order with the following details: source order XXXXXXXXX, source order line 1, source order schedule 1. Provide a value for BILL_TO_SITE_USE_ID, and resubmit the order.

Then run SQL to identify the correct data you should use.

SELECT  accounts.party_id           ,
        accounts.account_name       ,
        accounts.account_number     ,
        party_sites.party_site_name ,
        sites.cust_acct_site_id     ,
        sites.cust_account_id       ,
        site_uses.site_use_code     ,
        site_uses.primary_flag      ,
        site_uses.site_use_id       ,
        site_uses.location          ,
 locations.ADDRESS1 || ' ' || locations.ADDRESS2 || ' ' || locations.ADDRESS3 || ' ' || locations.ADDRESS4 || ' ' || locations.CITY || ' ' || locations.POSTAL_CODE || ' ' || locations.STATE ||
 ' ' || locations.country , 
        territory.territory_short_name
FROM    fusion.hz_cust_accounts accounts       ,
        fusion.hz_cust_acct_sites_all sites    ,
        fusion.hz_party_sites party_sites      ,
        fusion.hz_cust_site_uses_all site_uses ,
        fusion.hz_locations locations          ,
        fusion.fnd_territories_vl territory
WHERE   sites.cust_account_id           = accounts.cust_account_id
        AND party_sites.party_site_id   = sites.party_site_id
        AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
        AND locations.location_id       = party_sites.location_id
        --    AND site_uses.site_use_code = 'BILL_TO'
        --    AND site_uses.primary_flag = 'Y'
        AND site_uses.STATUS  = 'A'
        AND accounts.STATUS   = 'A'
        AND sites.STATUS      = 'A'
        AND locations.COUNTRY = territory.territory_code
        AND EXISTS
        (
                SELECT  1
                FROM    fusion.fnd_setid_assignments
                WHERE   set_id                   = sites.set_id
                        AND reference_group_name = 'HZ_CUSTOMER_ACCOUNT_SITE'
                        AND determinant_type     = 'BU'
        )
        and upper(accounts.account_name ) like upper('&CUSTOMER_NAME%')

Example SQL Output

Assume SQL returns results.

PARTY_ID	ACCOUNT_ID	PARTY_NAME	SITE_USE_CODE	ORIG_SYSTEM_REFERENCE
300000001469001	300000001469002	Computer Service and Rentals	SHIP_TO	300000001469006
300000001469001	300000001469002	Computer Service and Rentals	BILL_TO	300000001469016
300000001469001	300000001469002	Computer Service and Rentals	BILL_TO	300000002494053
300000001469001	300000001469002	Computer Service and Rentals	BILL_TO	300000002494059
300000001469001	300000001469002	Computer Service and Rentals	SHIP_TO	300000002494060