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.

Comments

  1. Thanks Jobinesh for the info..
    Is the DCDataRow used only for the LOV or for all the Criteria Items in the query panel? ie Can't we set a value for non LOV items through ViewRowImpl?

    Also, how is the data stored in DCDataRow will the type defer in DCDataRow.. Is there anyway that we can extend it and use it to supply the bind variable?

    ReplyDelete
  2. Well,the reason for introducing DCDataRow is to work around the scenarios where run time doe not have any queried Row/EO instance to base the operation. For a form or table you have the the row/record in place, whereas a query component acts in air :). Apparently some virtual row needs to be there to base the actions, that is the story behind DCDataRow. As of now, there is no easy way to hook your code directly with DCDataRow, however certain delegated points are available on VO which will get engaged with DCDataRow where you can add hooks.

    ReplyDelete
  3. Are there any pointers on the how to hook it with DCDataRow?

    ReplyDelete
  4. As I said not with DCDataRow...but based on use case, there are certain ways..e,g: http://jobinesh.blogspot.com/2011/01/customizing-component-display-by.html

    ReplyDelete
  5. The information on the blog works but only for the first time. From the second attempt on the issue of LOV not being filter persists.

    ReplyDelete
  6. On the subsequent attempts the "CriteriaClasue" code mentioned in the blog is not being called.

    ReplyDelete
  7. It wont get called once the accessor is evaluated. You can try setting dependnet attributes if you want to reevalaute while changing values for other attributes

    ReplyDelete
  8. So if I understand you correctly, as per your example above, the 'Manager LOV' will display only those Employees who belong to the same Department as that of the business user who logged in to the system for the first time but in subsequent attempts to access the LOV in the query region the the 'Manager LOV' will display all the Employees. Am I correct in this interpretation? If so how do I filter the 'Manager LOV' for subsequent attempts too?

    ReplyDelete
  9. What do you meant by subsequent requests? Better post your query in OTN forum with detailed use case and pass me the link

    ReplyDelete
  10. By Susequent requests I mean clicking on the LOV a second time.

    ReplyDelete
  11. Hi,

    Sharing info related to LOV's is much interesting.
    I need assistance in implementing LOV to Emplid Attribute (Number(10) in ExecuteWithParams.
    Plz mail me @ kittu141@gmail.com
    Help me ASAP..

    ReplyDelete

Post a Comment