Tips on LOV Runtime

Do you know how does LOV query get executed if there doesn't exist any entity instance to back up the source ViewObject? The scenario usually arises if you have LOV defined for an attribute, which is part of query panel. This article discusses this specific scenario with a use case.

Use case

Let us take the classical Employee entity as example. This 'Employee' is based on HR schema. The detailed entity is shown below.

You can see that each Employee falls under specific Department and Manager. This means that while defining a new Employee record, it's required to associate an existing Employee as manager to the new record. To improve the usability, let us define a LOV against manager attribute in Employee ViewObject. Let us make this LOV much smarter by adding an additional requirement to filter down the values of ‘Manager LOV’ based on logged in user's Department Id. So 'Manager LOV' should display only those Employees who belong to the same Department as that of the business user who logged in to the system. There are many ways to query LOV based on this dynamic condition. One-way may be to override the ‘VewAccessor’ defined at EmployeesViewRowImpl and add the extra 'where' clause as shown below.

/**
* Gets the view accessor <code>RowSet</code> EmployeesImplVA.
*/
public RowSet getEmployeesVA() {

RowSet rs = (RowSet)getAttributeInternal(EMPLOYEESWRONGIMPLVA);
rs.setNamedWhereClauseParam("bindVariableDepartmentId", 80);
rs.executeQuery();
return rs;

}

Assume that we adopted this approach for realizing this use case. Now the real fun starts.
You can see that while running the application, if the LOV is invoked from query panel it displays all values without appending the extra 'where clause' added through 'getEmployeesVA()'. Where as LOV from a Data Capture Form or Table, filter out the values as we expected.

What has gone wrong?

When LOV is invoked from query panel (af:query) there doesn’t exist any record(ViewRow) for the source ViewObject. Hence framework uses slightly different execution path. LOV from query panel uses an internal class oracle.adf.model.bean.DCDataRow, which is different from EmployeesViewRowImpl. Obviously there is no chance to execute the overridden methods from EmployeesViewRowImpl.

However, there are solutions to this specific scenario.

Solutions

1. You can override the getCriteriaItemClause(..) method in EmployeesViewImpl which is the source ViewObject for the Manager LOV. Since the LOV uses EmpDeptViewCriteria, this method will be called for every ViewCriteriaItem in EmpDeptViewCriteria while generating where clause. This will be called irrespective of whether the LOV is invoked from Query Panel or from 'Data Capture Form’.

@Override
public String getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem) {
if ("EmpDeptViewCriteria".equals(viewCriteriaItem.getViewCriteria().getName())
"DepartmentId".equals(viewCriteriaItem.getName()))
{
ensureVariableManager().setVariableValue("bindVariableDeptId", new Number(80));
}
return super.getCriteriaItemClause(viewCriteriaItem);
}

2. Another possible approach is to keep the 'DepartmentId' in User Session and access it using Groovy expression e.g.: adf.userSession.userData.SomeKey . Below diagram shows how does the ViewCriteria look like.



You can download the sample workspace from here.

I would like to thank Sathish Kumar, Prakash ILLANCHERAN and Srinathreddy Komatireddy for assisting me in writing this blog.