1. Implementing Order Management
  2. Compare Import Data to Oracle Database Data

Compare Import Data to Oracle Database Data

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

Assume you run the ProcessOrderRequest operation on the ReceiveOrderRequestService web service at https://server:port/soa-infra/services/default/DooDecompReceiveOrderExternalComposite/ReceiveOrderRequestService.

Assume the header section of the payload includes this code.

<ns1:OrchestrationOrderRequest>
<ns2:BuyingPartyName>XXXXXXXXXX</ns2:BuyingPartyName>
<ns2:BuyingPartyId>7777777777</ns2:BuyingPartyId>

And the order line section of the payload includes this code.

<ns2:OrchestrationOrderRequestLine>
<ns2:BillToCustomerName>ZZZZZZZZZZ</ns2:BillToCustomerName>
<ns2:BillToCustomerIdentifier>8888888888</ns2:BillToCustomerIdentifier>
<ns2:BillToAccountSiteUseIdentifier>9999999999</ns2:BillToAccountSiteUseIdentifier>

Run an SQL query to determine whether these values are correct. For details, see Use SQL to Query Order Management Data.

Here's how you can map.

Attribute in Payload

SQL

BuyingPartyName

PARTY_NAME

BuyingPartyId

PARTY_ID

BillToCustomerName

PARTY_NAME

BillToCustomerIdentifier

CUST_ACCOUNT_ID

BillToAccountSiteUseIdentifier

ORIG_SYSTEM_REFERENCE

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

Correct the Problem

Here's the mapping you can use to correct the problem.

Attribute in Payload

Value from SQL

BuyingPartyName

Computer Service and Rentals

BuyingPartyId

300000001469001

BillToCustomerName

Computer Service and Rentals

BillToCustomerIdentifier

300000001469002

BillToAccountSiteUseIdentifier

300000001469016

Here's the corrected payload for the order header:

<ns1:OrchestrationOrderRequest>
<ns2:BuyingPartyName>Computer Service and Rentals</ns2:BuyingPartyName>
<ns2:BuyingPartyId>300000001469001</ns2:BuyingPartyId>

Here's the corrected payload for the order line:

<ns2:OrchestrationOrderRequestLine>
<ns2:BillToCustomerName>Computer Service and Rentals</ns2:BillToCustomerName>
<ns2:BillToCustomerIdentifier>300000001469002</ns2:BillToCustomerIdentifier>
<ns2:BillToAccountSiteUseIdentifier>300000001469016</ns2:BillToAccountSiteUseIdentifier>

Related Topics