Initializing the bind variables used for an LOV query

This post is continuation of one of my previous post on LOV run time. Today, I'm sharing another approach for initializing the bind variable used in an LOV query.

This example has an LOV defined for DepartmentId attribute of Employee View Object.

LOV query comes from Department View Object, and the 'where' clause has a bind variable defined as shown below.

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
WHERE ( Departments.LOCATION_ID = :bindVarLocId OR :bindVarLocId IS NULL )

How do you set bind variable value in the above case (when used in a LOV query)?

One possibility is to let the bind variable to read the value from User Session Data. In this case, you can supply the bind variable value at run time by putting in to ApplicationModule's User Session Data HashMap as shown in the following code snippet. You may need to expose this method (defined on AM) to the client as well.

public void setLocationIDSessionData(Number locId){
this.getSession().getUserData().put("LocationId", locId);
}

How does the query get value from User Session Data?

ADF BC run time can access User Session values using groovy expressions. You may need to use the bind variable editor to associate the groovy expression with bind variable as shown in the screen shot.

When do you need to set 'User Session Data' with value for the bind variable ?

You may need to do it before the LOV query execution. If you use ChoiceList ListOfValues, then you may need to do it through a method activity before the view activity.
If you are using LOV component displayed as a pop up window, you can even rely on launchPopupListener of ListOfValues component.

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS3 + HR Schema]

How to run this sample?

1. Run lovInForm.jspx. Click on the LOV displayed for DepartamentId in the parameter form. You may notice that list is restricted to display only those records with LocationId=1700

2. Run lovInQuery.jspx. Click on the LOV displayed for DepartamentId in the search panel. As in the above case, list of values is restricted to display only those records with LocationId=1700