Troubleshoot Problems with Web Services

No Matching Row

Assume you use a web service to import a sales order. The payload includes:

<ns2:ProductNumber>BIGSTORE1111</ns2:ProductNumber>
<ns2:InventoryOrganizationIdentifier>300000001384059</ns2:InventoryOrganizationIdentifier>

And you encounter this error:

An order was not created because no matching row for attribute ProductNumber with the value BIGSTORE1111 was found for the source order with the following details: source order BIGSTORE0007, source order line 2, source order schedule 2. Check the attribute value, and resubmit the order.

The No Matching Row error typically happens because the Oracle database doesn't contain the same value that your import contains. In this example, your import inventory item isn't defined in the Oracle database or isn't associated with the Inventory Organization that the web service payload specifies.

Use SQL to query the Oracle database.

SELECT HOU.ORGANIZATION_ID,
  HOUTL.NAME,
  HOUCL.CLASSIFICATION_CODE
FROM HR_ALL_ORGANIZATION_UNITS_F HOU,
  HR_ORGANIZATION_UNITS_F_TL HOUTL,
  HR_ORG_UNIT_CLASSIFICATIONS_F HOUCL
WHERE 
  HOU.ORGANIZATION_ID=HOUTL.ORGANIZATION_ID AND
  HOU.ORGANIZATION_ID =HOUCL.ORGANIZATION_ID AND
  HOUCL.CLASSIFICATION_CODE = 'INV'
ORDER BY 
  HOUTL.NAME;

The query returns:

ORGANIZATION_ID	NAME	CLASSIFICATION_CODE
300000001286109	BIGSTORE Item Org	INV
300000001286592	BIGSTORE_CN_DC1	INV
300000001286615	BIGSTORE_SG_DC1	INV
300000001286262	BIGSTORE_US_MFG1	INV
300000001286412	BS Master Inventory Org	INV
300000001286439	BS Retail Inventory Org	INV

Run another query to get the inventory item that's associated with the inventory organization.

SELECT 
  ItemPEO.INVENTORY_ITEM_ID,
  ItemPEO.ORGANIZATION_ID,
  HOUTL.NAME,
  ItemPEO.ITEM_NUMBER,
  ItemPEO.INVENTORY_ITEM_STATUS_CODE
FROM EGP_SYSTEM_ITEMS_B ItemPEO,
  HR_ORGANIZATION_UNITS_F_TL HOUTL
where
 HOUTL.ORGANIZATION_ID = ItemPEO.ORGANIZATION_ID and
 ItemPEO.ITEM_NUMBER = 'BIGSTORE1111';

The query returns:

INVENTORY_ITEM_ID	ORGANIZATION_ID	NAME	ITEM_NUMBER	INVENTORY_ITEM_STATUS_CODE
300000001292032	300000001286109	BIGSTORE Item Org	BIGSTORE1111	Active

The payload sent InventoryOrganizationIdentifier>300000001384059<, but the Oracle database contains 300000001286109 for ORGANIZATION_ID.

Here's the correct value to send.

<ns2:ProductNumber>BIGSTORE1111</ns2:ProductNumber>
<ns2:InventoryOrganizationIdentifier>300000001286109</ns2:InventoryOrganizationIdentifier>

You attempt to import a source order. The import payload includes:

<ns2:ProductNumber>BIGSTORE1111</ns2:ProductNumber> <ns2:InventoryOrganizationIdentifier>300000001384059</ns2:InventoryOrganizationIdentifier>

But you encounter this error during import:

An order was not created because no matching row for attribute ProductNumber with the value BIGSTORE1111 was found for the source order with the following details: source order BIGSTORE0007, source order line 2, source order schedule 2. Check the attribute value, and resubmit the order.

This problem happens because the inventory organization in your import payload isn't defined in the Oracle database.

To fix this problem, first get the list of inventory organizations that the Oracle database currently contains. Run this SQL.

SELECT HOU.ORGANIZATION_ID,
  HOUTL.NAME,
  HOUCL.CLASSIFICATION_CODE
FROM HR_ALL_ORGANIZATION_UNITS_F HOU,
  HR_ORGANIZATION_UNITS_F_TL HOUTL,
  HR_ORG_UNIT_CLASSIFICATIONS_F HOUCL
WHERE
  HOU.ORGANIZATION_ID=HOUTL.ORGANIZATION_ID AND
  HOU.ORGANIZATION_ID =HOUCL.ORGANIZATION_ID AND
  HOUCL.CLASSIFICATION_CODE = 'INV'
ORDER BY
  HOUTL.NAME;

Assume the query returns:

ORGANIZATION_ID	NAME	CLASSIFICATION_CODE
300000001286109	BIGSTORE Item Org	INV
300000001286592	BIGSTORE_CN_DC1	INV
300000001286615	BIGSTORE_SG_DC1	INV
300000001286262	BIGSTORE_US_MFG1	INV
300000001286412	BS Master Inventory Org	INV
300000001286439	BS Retail Inventory Org	INV

Next, get details for BIGSTORE1111, which is the inventory organization that your import payload specifies.

SELECT
  ItemPEO.INVENTORY_ITEM_ID,
  ItemPEO.ORGANIZATION_ID,
  HOUTL.NAME,
  ItemPEO.ITEM_NUMBER,
  ItemPEO.INVENTORY_ITEM_STATUS_CODE
FROM EGP_SYSTEM_ITEMS_B ItemPEO,
  HR_ORGANIZATION_UNITS_F_TL HOUTL
where
 HOUTL.ORGANIZATION_ID = ItemPEO.ORGANIZATION_ID and
 ItemPEO.ITEM_NUMBER = 'BIGSTORE1111';

Assume the query returns:

INVENTORY_ITEM_ID	ORGANIZATION_ID	NAME	ITEM_NUMBER	INVENTORY_ITEM_STATUS_CODE
300000001292032	300000001286109	BIGSTORE Item Org	BIGSTORE1111	Active

Next, fix your import payload. Change this current value:

<ns2:ProductNumber>BIGSTORE1111</ns2:ProductNumber> <ns2:InventoryOrganizationIdentifier>300000001384059</ns2:InventoryOrganizationIdentifier>

To the value that the query returned in column ORGANIZATION_ID:

<ns2:ProductNumber>BIGSTORE1111</ns2:ProductNumber> <ns2:InventoryOrganizationIdentifier>300000001286109</ns2:InventoryOrganizationIdentifier>

Attribute Values

Assume you use enterprise business message ProcessSalesOrderFulfillmentEBM to create a sales order and encounter this error.

Error Message: {http://xmlns.oracle.com/apps/scm/doo/decomposition/receiveTransform/transformSalesOrder/DooDecompTransformSalesOrderComposite}ProcessSalesOrderFulfillmentResponseEBM
Fault ID default/DooDecompTransformSalesOrderComposite!11.1.5.0.0*soa_7289e448-8b78-408f-af5c-116da7e1d9d2/DecompositionProcess/70017-BpThw8-BpSeq55.49-2
Fault Time Sep 16, 2013 11:24:34 AM
Non Recoverable Business Fault :1 env:Server Attribute ORG_ID has invalid value 300000000562209 for service ProcessOrderValidation.

This error happens because the payload specifies value 300000000562209 for attribute ORG_ID, but the Order Management database doesn't contain this value. The payload also doesn't correctly specify attribute BusinessUnitReference and attribute BusinessUnitIdentification.

To fix this problem, query the database.

SELECT
  FABUV.BU_ID,
  FABUV.BU_NAME,
  FABUV.DEFAULT_SET_ID,
  IOP.ORGANIZATION_ID,
  IOP.ORGANIZATION_CODE,
  HAOU.NAME
FROM
  INV_ORG_PARAMETERS IOP,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  FUSION.FUN_ALL_BUSINESS_UNITS_V FABUV
WHERE
  HAOU.ORGANIZATION_ID = IOP.ORGANIZATION_ID AND
  IOP.BUSINESS_unit_id = FABUV.BU_ID
ORDER BY
  IOP.ORGANIZATION_CODE,
  FABUV.BU_ID;

Assume the query returns:

BU_ID	BU_NAME	DEFAULT_SET_ID	ORGANIZATION_ID	ORGANIZATION_CODE	NAME
300000001110916	USA1 Business Unit	300000001111782	300000001201056	001	Seattle Warehouse
300000001110916	USA1 Business Unit	300000001111782	300000003887955	999	External Item Organization
300000001130053	USA2 Business Unit	300000001111783	300000001201066	002	Atlanta Warehouse
300000001130053	USA2 Business Unit	300000001111783	300000001130184	003	Chicago Distribution Center
300000001341196	China Business Unit	300000001341955	300000004396135	050	Shanghai Distribution Center
300000001341196	China Business Unit	300000001341955	300000004396139	051	Shenzhen Warehouse

Assume you're attempting to create a sales order for Chicago Distribution Center, which is in USA2 Business Unit, and attribute BU_ID for this business unit contains 300000001130053. To fix the problem, you must define these attributes. Add this code to you payload.

<corecom:BusinessUnitReference>
             <corecom:BusinessUnitIdentification>
              <!-- BUSINESS UNIT  ID-->
                 <corecom:AlternateObjectKey>
                     <corecom:ID>300000001130053</corecom:ID>
                 </corecom:AlternateObjectKey>
             </corecom:BusinessUnitIdentification>
  </corecom:BusinessUnitReference>

Performance Problems

I use OrderFulfillmentResponseService to set up an integration with Integration Cloud Service so we can update the status on the fulfillment line. It works fine until we have a lot of sales orders to process. When we have thousands of order lines to update we expect the update to finish in about 10 to 15 minutes, but it actually takes over two hours.

The integration processes requests in parallel whether you set the Invocation Mode attribute on the connector to Asynchronous Service or Synchronous. For example, if you have 1,000 requests, the integration will process them all at the same time. It doesn't put them in a queue. Also, serviceOrderFulfillmentResponseService doesn't limit volume. For example, it doesn't put limits on the number of web service calls that it can send in one minute.

This problem usually happens because the server that you use to host Integration Cloud Service isn't optimized to handle a high volume of transactions. To fix this problem, work with the person who administers the server that hosts Integration Cloud Service and see if you can optimize it to handle more transactions, such as adding the number of threads the server can process at the same time.

Other Weird Stuff

You send a payload to web service ReceiveOrderRequest to request to create a sales order.

<ns1:OrchestrationOrderRequest xmlns:ns2="http://xmlns.oracle.com/apps/scm/doo/decomposition/receiveTransform/receiveSalesOrder/model/">
  <ns2:SourceTransactionIdentifier>PMC-070716-013</ns2:SourceTransactionIdentifier>
  <ns2:SourceTransactionNumber>PMC-070716-013</ns2:SourceTransactionNumber>
  <ns2:BuyingPartyName>JAH CUSTOMER SHARED</ns2:BuyingPartyName>
  <ns2:BuyingPartyContactName>Peter Pan</ns2:BuyingPartyContactName>
  <ns2:TransactionalCurrencyCode>USD</ns2:TransactionalCurrencyCode>
  <ns2:TransactionOn>2016-07-06T02:12:01</ns2:TransactionOn>
  <ns2:RequestingBusinessUnitIdentifier>US_W_888_BU</ns2:RequestingBusinessUnitIdentifier>
  <ns2:PartialShipAllowedFlag>false</ns2:PartialShipAllowedFlag>
  <ns2:OrchestrationOrderRequestLine>

The request seems to run successfully but the Order Management work area doesn't display the sales order. You run a flow trace in Oracle Enterprise Manager, which displays:

<bpelFault><faultType>0</faultType><selectionFailure xmlns="http://docs.xyz.org/wsbpel/2.0/process/executable"></selectionFailure></bpelFault>

And also displays:

Exception is thrown because the from-spec at line 1,616 is evaluated to be empty

And also displays:

AssignEILAMServiceInput (faulted)

<details>
  <from-spec>
    <from>$inputVariable.payload/client:OrchestrationOrderRequest/ns4:SourceTransactionSystem</from>
  </from-spec>
  <variable>
    <message>inputVariable</message>
  </variable>
  <fault>
    <bpelFault>
      <faultType>0</faultType>
      <selectionFailure/>
    </bpelFault>
  </fault>
</details>

For example:

The problem happens because your request payload doesn't specify the source system. To fix it, add attribute SourceTransactionSystem to your payload.

<ns2:SourceTransactionSystem>ORA_BM_CPQ</ns2:SourceTransactionSystem>

For example:



<ns1:OrchestrationOrderRequest xmlns:ns2="http://xmlns.oracle.com/apps/scm/doo/decomposition/receiveTransform/receiveSalesOrder/model/">
  <ns2:SourceTransactionIdentifier>PMC-070716-013</ns2:SourceTransactionIdentifier>
  <ns2:SourceTransactionNumber>PMC-070716-013</ns2:SourceTransactionNumber>

  <ns2:SourceTransactionSystem>ORA_BM_CPQ</ns2:SourceTransactionSystem>

  <ns2:BuyingPartyName>GED CUSTOMER SHARED</ns2:BuyingPartyName>
  <ns2:BuyingPartyContactName>Peter Pan</ns2:BuyingPartyContactName>
  <ns2:TransactionalCurrencyCode>USD</ns2:TransactionalCurrencyCode>
  <ns2:TransactionOn>2016-07-06T02:12:01</ns2:TransactionOn>
  <ns2:RequestingBusinessUnitIdentifier>US_WT_DG09_BU</ns2:RequestingBusinessUnitIdentifier>
  <ns2:PartialShipAllowedFlag>false</ns2:PartialShipAllowedFlag>
  <ns2:OrchestrationOrderRequestLine>

View an example that uses flow trace. For details, see Route Requests from Order Management to Fulfillment Systems.

For details about Oracle Enterprise Manager, see https://www.oracle.com/technetwork/oem/enterprise-manager/overview/index.html.

We use the Order Import web service and Receive Order service to import source orders. I need the header details and order line details for holds that Order Management applies as a result of the import. I need to know whether Order Management applies the hold and, if not, why not.

The response from Order Import provides details in the callback for each asynchronous operation. Examine the elements in the response.

ord:ReturnStatus?</ord:ReturnStatus>
<!--Optional:-->
ord:MessageName?</ord:MessageName>
<!--Optional:-->
ord:MessageText?</ord:MessageText>

These elements indicate whether the service successfully processed the sales order or, if not, the errors it encountered and error text.

The RequestHold operation and Release Pause Tasks operation are asynchronous. You must implement the callback to receive a response from them.