インポート・データとOracle Databaseデータの比較

Oracleデータベースを問い合せて、インポート・データがデータベース要件を満たしているかどうかを確認します。

ReceiveOrderRequestService webサービス(https://server:port/soa-infra/services/default/DooDecompReceiveOrderExternalComposite/ReceiveOrderRequestService)でProcessOrderRequest操作を実行するとします。

ペイロードのヘッダー・セクションにこのコードが含まれているとします。

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

また、ペイロードのオーダー明細セクションにこのコードが含まれます。

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

SQL問合せを実行して、これらの値が正しいかどうかを確認します。詳細は、「SQLを使用したオーダー管理データの問合せ」を参照してください。

マップする方法を次に示します。

ペイロードの属性	SQL
BuyingPartyName	PARTY_NAME
BuyingPartyId	PARTY_ID
BillToCustomerName	PARTY_NAME
BillToCustomerIdentifier	CUST_ACCOUNT_ID
BillToAccountSiteUseIdentifier	ORIG_SYSTEM_REFERENCE

SOLD_TO_PARTY_IDのエラー

エラーが発生したとします。

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.

SQLを実行して、使用する正しいデータを識別します。

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%')

BILL_TOのエラー

エラーが発生したとします。

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.

または、このエラー。

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.

次に、SQLを実行して、使用する正しいデータを識別します。

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%')

例S QL出力

SQLから結果が返されると想定します。

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

問題の修正

問題の修正に使用できるマッピングを次に示します。

ペイロードの属性	SQLの値
BuyingPartyName	Computer Service and Rentals
BuyingPartyId	300000001469001
BillToCustomerName	Computer Service and Rentals
BillToCustomerIdentifier	300000001469002
BillToAccountSiteUseIdentifier	300000001469016

オーダー・ヘッダーの修正済ペイロードを次に示します:

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

オーダー明細の修正済ペイロードは次のとおりです:

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