X

It's All About the Platform.

  • October 13, 2014

How To Query Data Using The Web Services Find Operation

Richard Bingham
Senior Development Manager

All our web services provide simple get operations, such as getSalesAccount, however these require a unique ID value as an input parameter (e.g. SalesAccountId), something not displayed on the UI and often hard to get at run-time. In addition, you'll commonly want to return more than one record back from the integrated Fusion Applications system (e.g. all open opportunities for a customer) therefore you need a more flexible querying approach.

From scrolling through the operations listed in OER, you'll quickly identify the find[ObjectName] operations. You'll notice in the example screenshot below from the Sales Lead Service, the first parameter for the find operations is a Service Data Object (SDO) of type oracle.jbo.common.service.types.FindCriteria. Understanding how to use this is the key to getting data via Fusion Applications find web services.



But Wait, Haven't I Seen This Before?

When coding you may notice that the find operations of Fusion Applications web services use the same ADF View Object methods that you may have used to interrogate data within Application Composer using Groovy. Both consist of building up your query using SDO objects and attributes. In the VO implementation, the ViewCriteria object has the ensureCriteriaItem() method that accepts a field name on which you're going to query, you then use the setOperator() and the setValue() methods to build the criteria row which gets run by executeQuery().

Whilst this is not explictly using a web service call, it uses the same underlying ADF functionality to return application data. You can see examples in this previous blog post.

Using a Map as Input 

Whilst the video below is intended to illustrate handling response payloads, what is not immediately obvious is that it's actually calling another Fusion Applications instance running the standard SalesLeadService. You'll notice that the web service call - adf.webServices.SalesLeadService.findSalesLead() - accepts the findCriteria SDO input as a much-simplified Map data type.

So whilst you can use the standard XML request payloads, these web services also support the use of a Map as the SDO input too.

Java Client SDO Example 

Below is a sample to show the creation of the FindCriteria SDO object in JDeveloper, used for a web service client in essentially the same way as mentioned above. You can see how the web service consumes the SDO object in Java, along with supporting a Map and standard XML.

Below we set the attributes using the setter methods and call the find operation of the opportunity service. This snippet is taken from our introduction to using web services, and hopefully you see the pattern emerging here.

ViewCriteriaItem item = new ViewCriteriaItem();
item.setUpperCaseCompare(false);
item.setAttribute("OptyId");
item.setOperator("=");
item.getValue().add("300100017410548");
..
contacts = opportunityService.findOpportunity(findCriteria, findControl); 

Multiple Fields and Sub-Queries 

Often you need more than one query criteria (i.e. a where clause) in the request, allowing for queries on multiple fields at once. This is done by simply specifying multiple Item elements in the request. In the sample below (using XML for visual clarity), the blue and green Item sections define the two criteria to use.





In terms of each item, the operator element can be one of the following values:

  • AFTER, BEFORE, BETWEEN, CONTAINS, CONTAINSALL,
  • NOTBETWEEN, ONORAFTER, ONORBEFORE, STARTSWITH
  • CONTAINSDELIMITEDID, DOESNOTCONTAIN, ENDSWITH,
  •  =, >=, >, ISBLANK, ISNOTBLANK, <=, <, <>

Note that IN and LIKE are not supported, however similar can be achieved using CONTAINS. As shown in the example below if you are making HTTP requests you may need to encode special characters like the greater-than angle brackets.

 <ns1:findCriteria xmlns:ns2="http://xmlns.oracle.com/adf/svc/types/">
  <ns2:fetchStart>0</ns2:fetchStart>
  <ns2:fetchSize>-1</ns2:fetchSize>
  <ns2:filter>
   <ns2:conjunction>And</ns2:conjunction>
   <ns2:group>
    <ns2:conjunction>And</ns2:conjunction>
    <ns2:upperCaseCompare/>
    <ns2:item>
      <ns2:conjunction>And</ns2:conjunction>
      <ns2:upperCaseCompare>false</ns2:upperCaseCompare>
      <ns2:attribute>OwnerId</ns2:attribute>
      <ns2:operator>=</ns2:operator>
      <ns2:value>300132120845</ns2:value>
    </ns2:item>
    <ns2:item>
      <ns2:conjunction>And</ns2:conjunction>
      <ns2:upperCaseCompare>true</ns2:upperCaseCompare>
      <ns2:attribute>Amount</ns2:attribute>
      <ns2:operator>&gt;=</ns2:operator>
      <ns2:value>10000</ns2:value>
    </ns2:item>
   </ns2:group>
   <ns2:nested/>
  </ns2:filter>
  <ns2:excludeAttribute/>
  <ns2:childFindCriteria/>
 <ns1:findCriteria>

In addition, the conjunction on the second item (in blue) is an "And", however it could also be: Or, Not, AndNot, OrNot. As such you can build up a more useful query with multiple criteria connected by these logical operators. You'll also notice there is a nested element at the bottom. This is where the support for sub-queries is available, by adding in another group-item criteria set.

Finally it is also possible to interrogate child objects, such as the revenue object lines on a parent opportunity object. This is through the use of the childFindCriteria element, and you simply add yet another filter-group-item set within it. Examples of this are in the whitepaper mentioned below.

Restricting The Data Returned

As you might have noted from the video above, the findCriteria by default returns every field/attribute for an object, commonly far too many. So to keep things simple and reducing the parsing it is more efficient to just specify in the request which fields (attributes) you wish to be returned. This may also speed up processing time, as the payloads are much smaller.

For each findCriteria you can add findAttribute element(s) to dictate this. In addition, the opposite can be done using the excludeAttributes element which returns all attributes except those specified. The following video shows an example of using findAttribute, for both XML and a Map in Groovy.

In addition, if you just want batches of records returned, say the first 4, then you can use both the fetchSize, fetchStart, and sortOrder attributes in the request payload. The following example below shows a request (left) with the fetchSize requesting just 4 records (-1 is all) and the sortOrder specifying the result to be descending based on LeadId. The result is shown on the right. The fetchStart attribute (defaults to 0) can then be used to get the next batch of records by passing a value of say 5 in this example.


Related References

  • Recommended Whitepaper: Understanding Oracle Sales Cloud SOAP Web Service Find and Get Request Payloads (Doc ID 1559863.1)
  • The findCriteria SDO is detailed in the Fusion Middleware Java API Reference for Oracle ADF Model
  • The Groovy Scripting Reference Guide R9 has a useful section 4.23 - Finding Objects Using a View Criteria.

Join the discussion

Comments ( 6 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.