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>