Application Composer Series: Querying Object Records in Groovy
By Richard Bingham-Oracle on May 08, 2014
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:
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
. 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
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') vci.setOperator('=') vci.setValue("CustomerABC") vc.insertRow(vcr) vo.appendViewCriteria(vc) vo.executeQuery()
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.