Application Composer Series: Querying Object Records in Groovy

As an extension to the original article on manipulating object records using Groovy code, this post looks a little deeper at the two main ways to query a particular subset of object records for use in your logic.

View Object Names

Application Composer implements a set of standard view objects which expose the most useful fields for use in customization and extension. For on-premises customizations using JDeveloper it may be useful to know that these are not always the exact same view objects that the application uses internally. So while most Standard Objects have their API as [ObjectName]VO, such as OpportunityVO, others have another level of abstraction over the underlying VO's, such as the Partner standard object having the API name just as "Partner". Here are some of the more common view object API names for use in Groovy, and notice the slight naming differences:

  • OpportunityVO
  • MklLeadVO
  • CampaignsVO
  • SalesAccountVO
  • OrganizationDVO
  • PersonDVO
  • PersonProfile
  • Partner
  • Address

The Extending Sales Guide lists out some examples of similar standard view objects for your use in scripting.

Getting One Row

The view acccessor object used to get at the underlying records provides the findByKey() function as the standard mechanism for looking up data records, based on their primary keys. It is quite simple to use; you create your view accessor object using the newView() function and then use its findByKey() function, passing in your primary key and the number of rows you wish to be returned (normally 1). Check out the first half of the video below for a simple example, getting data from a parent opportunity.

  def curOpty = getAttribute('OptyId')
  def myOpty = newView('OpportunityVO')
  def foundRows = myOpty.findByKey(key(curOpty),1)

Unfortunately Application Composer does not expose which fields are the primary keys, so using this can be tough. As a tip you can check out the Data Model diagrams in Oracle Enterprise Repository (OER) where the underlying table primary keys (_PK) are given, and try this against the corresponding VO's upon which they're based. For example, in Sales the opportunities shows that MOO_OPTY_PK(opty_id) is the primary key, and for Marketing the leads main table shows MKT_LM_LEADS_PK(mkt_lead_id). Another alternative is to look at the SDO definitions also listed in OER.

The key[] input parameter is actually an object type, hence can also accept an array of name:value pairs to support composite primary keys. Also of note is that the second input parameter can be set to -1 to return all rows, being ultimately limited by the internal ADF configuration value of MaxFetchSize. More detail on the ViewAccessor object and its methods can be found in the related JavaDoc. Note: For your custom objects, the key is the 'ID' Standard Field that is always implicitly created.

Using A View Criteria

As mentioned, unless you can determine the primary key of the object you wish to access, you need an alternative way to query records in Groovy. This is offered by the very flexible View Criteria feature. In fact, if you are working with an on-premises instance then you can do similar via a JDeveloper customization, as illustrated by the video here.

View criteria code can be simple or complex, depending on your requirements. The basic outline is that you create the view accessor object, and create a view criteria on that. You then create a view criteria row which contains items (i.e. fields), operators, and actual values, thereby emulating a query such as OptyId=12345. You then build all this together, adding the row to the view criteria object, and the view criteria to the view accessor. Finally you execute it and get the resulting row array returned. Check out the second half of the video below for a simple demonstration, and example below.

  def vo = newView('OpportunityVO')
  def vc = newViewCriteria(vo)
  def vcr = vc.createRow()
  def vci = vcr.ensureCriteriaItem('TargetPartyName')

The various operators available for use are listed in Table7 in the Groovy Scripting Guide, together with explanations on how to use multiple criteria, how to set the criteria row to be compound (multiple value), and changing the criteria conjunction from AND to OR. Read more under 'Finding Objects Using a View Criteria' and the subsequent chapters.

Also it is recommended to consider using Object and Global Functions to standardize accessing view objects and to encourage code reuse. The vanilla example in the Groovy Scripting Guide (page 45) has a global function applyFilter() which accepts the view object and a map of the criteria to use.

Also of note is that instead of using scripting code to access related object data, you can leverage the underlying view links that already exist, along with defining your own. These exist for many of the standard objects, and is available through the Joins feature in Application Composer. This is demonstrated and described in this video from our YouTube Channel.


The following example video shows both of these processes. First is a simple Field Trigger with Groovy that uses findByKey() to allow a custom child object to access fields from its parent opportunity and display them on the page. The second uses a view criteria within an Action button that updates a custom field with all the opportunity records that have the same customer value.


  • Extending Sales Guide - Accessing View Objects
  • Groovy Scripting Reference for Application Composer

Once you exectueQuery() on a view object, how do you find out how many objects remain in the collection?

Posted by dsimer on April 13, 2015 at 07:37 AM PDT #

On the rowIterator you can use the getEstimatedRowCount() function which from
my testing returns an accurate value. Interestingly you don't even have to run
executeQuery on the VO to get the count, as shown in the example below I just
tied to a button.
Kind regards

def vo = newView('OpportunityVO');
def vc = vo.createViewCriteria();
def vcRow = vc.createViewCriteriaRow();
def vc1 = vcRow.ensureCriteriaItem("Name");
def numRows = vo.getEstimatedRowCount()
setAttribute('ExtraField_c', numRows)

Posted by Richard Bingham on April 13, 2015 at 11:16 AM PDT #

How does one use the 'LIKE' operator with view criteria? I have been trying to limit the view object (Opportunity) to those objects in which the contact is listed among the OpportunityContacts without using any sort of iteration. I have a trigger that populates a text field with the partyid and function of every OpportunityContact, but when I attempt the view criteria it returns all opportunities. I have other formula fields returning filtered counts, but this is the only one using 'LIKE', and coincidentally the only one failing.

Posted by dsimer on July 02, 2015 at 06:39 AM PDT #

Hi DSimer
I tested this LIKE operator using a simple viewCriteria like that above, and it works OK for me. I suspect the problem is somewhere else in querying opportunities based on the contact value. If you'd like me to look at your example please post it to our forum here:
Kind regards

Posted by Richard Bingham on July 08, 2015 at 06:24 AM PDT #


What is the API name for ProductGroupVO ??

Thank you very much !!

Posted by guest on August 14, 2015 at 01:00 AM PDT #

How you access this kind of depends on what you are doing. For example, if you need to get the value inside an opportunity script based on what is part of the current record then you can access the product group name field from the ChildRevenue items collection.

Alternatively if you're trying to do CRUD operations on the Product Groups object as a standalone view object, then if you select the INSERT button from the Groovy Palette when on the Functions-Other-newView then you'll get a popup listing the VO names (API names). For example, for Product Groups it shows:


Hope this helps

Posted by Richard Bingham on August 17, 2015 at 03:15 AM PDT #

This post shows this popup:

Posted by Richard Bingham on August 17, 2015 at 03:18 AM PDT #


Thank you very (very very very ) much Richard !!

What i wanted to do works perfectly!

Posted by guest on August 21, 2015 at 07:12 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow us on twitter Fusion Applications Extensibility, Customizations and Integration forum Fusion Applications Dev Relations YouTube Channel
This blog offers news, tips and information for developers building extensions, customizations and integrations for Oracle Fusion Applications.


« November 2015