Calling the Find Method on an Oracle Service Interface

When your Groovy script needs to invoke a find method on the service interface for an Oracle Fusion Applications object, you need to pass a structured Map value for its findCriteria parameter.

This section provides some simple examples you can use to jumpstart your usage of the find method. Assume that you've already registered a web service variable named EmployeesService for an Oracle Applications web service that provides information about staff members. Furthermore, assume that its standard find method is named findEmployees.

The script in the following example shows the simplest possible example of creating a correctly-structured findCriteria parameter to pass as the first argument to this findEmployees() method. This example retrieves all employees whose Deptno field is equal to the value 30. The results are returned using the default sorting order. All fields of the employee object are returned to your calling script by default. Note that the carriage returns and other whitespace used in these examples are purely to improve readability and assist with visually matching the square brackets.

// Example findCriteria parameter definition
def findCriteria =
[
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute        :'Deptno',
            operator         :'=',
            value            :[[item:30]]
          ]         
        ]
      ]   
    ]
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ] 
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  // Do something with each 'emp' row
  println(emp) 
}

The script in the example below expands on the previous one by adding two additional view criteria items to further constrain the search. To accomplish this, we added two additional maps to the comma-separated list-of-maps value provided for the item map entry in the group list-of-maps entry of the filter map entry. The default conjunction between multiple view criteria items in the same view criteria row is AND, so this example finds all employees whose Deptno field is equal to 30, and whose commission field named Comm is greater than 300, and whose Job field starts with the value sales (using a case-insensitive comparison).

// Example findCriteria parameter definition
def findCriteria =
[
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute :'Deptno',
            operator  :'=',
            value     :[[item:30]]
          ],
          [
            attribute :'Comm',
            operator  :'>',
            value     :[[item:300]]
          ],
          [
            upperCaseCompare :true,
            attribute        :'Job',
            operator         :'STARTSWITH',
            value            :[[item:'sales']]
          ]          
        ]
      ]    
    ]
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ]
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  println(emp) 
}

The script in this example extends the one above to add a second view criteria row to the filter. To accomplish this, we added an additional map to the comma-separated list-of-maps value provided for the group list-of-maps entry of the filter map entry. The default conjunction between separate view criteria rows in a view criteria filter is OR, so the filter in this example finds all employees matching the criteria from the previous example, or any employee whose Ename field equals allen. The upperCaseCompare : true entry ensures that a case-insensitive comparison is performed. For more information on the valid values you can pass for the operator entry for the view criteria item, see Understanding View Criteria Item Operators.

// Example findCriteria parameter definition
def findCriteria =
[
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute :'Deptno',
            operator  :'=',
            value     :[[item:30]]
          ],
          [
            attribute :'Comm',
            operator  :'>',
            value     :[[item:300]]
          ],
          [
            upperCaseCompare :true,
            attribute        :'Job',
            operator         :'STARTSWITH',
            value            :[[item:'sales']]
          ]          
        ]
      ],        
      [
        item:
        [
          [
            upperCaseCompare :true,
            attribute        :'Ename',
            operator         :'=',
            value            :[[item:'allen']]
          ]          
        ]
      ]     
    ]
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ]
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  println(emp) 
}

The script in the example below enhances the original one above to explicitly specify a single field sort order. The results will be sorted ascending by the value of their Ename field. Since the value of the sortAttribute entry is a list of maps, you could add additional maps separated a commas to perform a sort on multiple fields.

// Example findCriteria parameter definition
def findCriteria =
[
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute :'Deptno',
            operator  :'=',
            value     :[[item:30]]
          ]          
        ]
      ]   
    ]
  ],
  sortOrder:
  [
    sortAttribute:
    [
      [
        name       :'Ename',
        descending : false
      ]
    ]
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ]
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  println(emp) 
}

The script below extends the previous one to add a specific find attribute criteria in order to request that only a subset of employee object fields should be returned in the result. In this example, for each employee object in department 30, only its Empno and Ename field values will be returned to the calling groovy script.

// Example findCriteria parameter definition
def findCriteria =
[
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute        :'Deptno',
            operator         :'=',
            value            :[[item:30]]
          ]          
        ]
      ]   
    ]
  ],
  sortOrder:
  [
    sortAttribute:
    [
      [
        name       :'Ename',
        descending : false
      ]
    ]
  ],
  findAttribute:
  [
    [item :'Empno'],
    [item :'Ename']
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ]
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  println(emp) 
}

The script shown here shows how to use the fetchSize map entry to limit the number of rows returned to only the first 3 rows that match the supplied criteria in the requested sort order, including only the requested field values in the result. This example returns the EmpNo, Ename, and Sal fields of the top 3 employees whose Job fields equals CLERK (performed case-sensitively this time), ordered descending by Sal.

// Example findCriteria parameter definition
def findCriteria =
[
  fetchSize : 3,
  filter:
  [
    group:
    [
      [
        item:
        [
          [
            attribute        :'Job',
            operator         :'=',
            value            :[[item:'CLERK']]
          ]          
        ]
      ]   
    ]
  ],
  sortOrder:
  [
    sortAttribute:
    [
      [
        name       :'Sal',
        descending : true
      ]
    ]
  ],
  findAttribute:
  [
    [item :'Empno'],
    [item :'Ename'],
    [item :'Sal']
  ]
]
// findControl needs to be an empty list rather than null
def findControl = [ ]
def emps = adf.webServices.EmployeesService.findEmployees(findCriteria, findControl)
for (emp in emps) {
  println(emp) 
}