ADF – Context specific filter in List of Values – (only list employees previously allocated on other project for this customer)

Another tale from the ADF (10.1.3.2) project for the Interior Decorator chain of stores. When the sales representative is creating the decoration plan for a home or office building, he or she will typically discuss one room at a time with the customer. The decoration plan may contain several rooms and for each room the plan will have actions (wall papering, floor paneling, painting) and with each action there will be materials associated (paint, carpet, wood). The materials for an action are selected in a List of Values (with advanced search) window. The specific requirement is that this List of Values can easily be filtered to only display materials earlier selected in the plan – since frequently the same materials are used in multiple rooms.

This article will discuss an approach to implementing that specific requirement – but in terms of Employees. Employees can be allocated on Projects. Projects are executed for Customers. When the user is allocating employees to a project, he or she should be able to restrict the list of employees to choose from to only those employees that have been allocated on projects for that same customer. Visually:

We want to allocate employees on the The Omega Challenge project , that we will run for our customer Global Warming Inc. We bring up the List of Values window from which we can select one or multiple employees. The list initially shows 106 employees. However, we know the customer has a preference for staff that worked on previous projects we did together. An example of such a project for this customer:

So the List of Values ideally should allow us to filter on exactly these employees. When we we switch the Radio Group "Only employees previously allocated on a project for this customer?" to Yes and press find, we will only see employees that have worked for Global Warming Inc. before (and also satisfy the other query conditions).

The implementation of this functionality is done like this:

• Model: add a bind parameter customerId to the EmployeesView that fuels to the List of Values; also modify the underlying where clause in the EmployeesView ViewObject:

and ( :customerId is null 
      or 
      exists ( select 'x' 
               from   project_allocations pan
               ,      projects pjt 
               where  pjt.ctr_id = :customerId 
               and    pjt.id = pan.pjt_id 
               and pan.emp_id = Employees.employee_id
             )
    )

• ViewController: create a managed bean that holds the Customer Id value and a switch whether or not to filter by that customer id; add logic to the Project Administration page to set the managed bean’s Customer Id. In the LovEmployees page, add a radio group to allow filter on /off , add logic to the execute query 

Since I have used JHeadstart to generate this application, my life is a little bit more simple. The steps in the ViewController project are limited to:

• Set the Query Bind Parameters property of the LovEmployees page to customerId=#{LovEmployeesFilterBean.ctrId} (meaning that whenever the LovEmployees ViewObject is queried, the value of the customerId bind parameter will be set to whatever value is read from the ctrId property on the LovEmployeesFilterBean.

• Add an unbound item FilterOnCustomer to the LovEmployees Group; it is a Radio Group, based on the YesNoDomain. It is only displayed in the Advanced Search region 

After generating the application, we need some small post-generation changes (that we could also make part of custom templates in order to maintain the generatability of the application). These post-gen changes are:

• change the value attribute of the SearchLovEmployeesFilterOnCustomer item in LovEmployeesTable.jspx to: value="#{LovEmployeesFilterBean.filterOnCtrId}"
• add a setActionListener element to the ProjectAdministration.jspx page, as child of the selectInputText element EmpId that is used to invoke the List of Values window:

<af:setActionListener from="#{bindings.ProjectAdministrationCtrId.inputValue}" to="#{LovEmployeesFilterBean.ctrId}" />
 

• add a managed bean definition to the faces-config.xml file:

 <managed-bean>
   <managed-bean-name>LovEmployeesFilterBean</managed-bean-name>
   <managed-bean-class>nl.amis.hrm.view.LovEmployeesFilterBean</managed-bean-class>
   <managed-bean-scope>session</managed-bean-scope>
 </managed-bean>
 

With these post gen changes, the application will set the Customer Id on the LovEmployeesFilterBean whenever the LOV for Employees is brought up. Every query against LovEmployeesView will retrieve the Customer Id value from that bean and use it to inject the bind parameter. The SearchLovEmployeesFilterOnCustomer item will determine whether or not the LovEmployeesFilterBean returns the value of the Customer Id:

package nl.amis.hrm.view;

import oracle.jbo.domain.Number;

public class LovEmployeesFilterBean {

    private Number ctrId;
    private String filterOnCtrId = "N";

    public LovEmployeesFilterBean() {
    }

    public void setCtrId(Number ctrId) {
        this.ctrId = ctrId;
    }

    public Number getCtrId() {
        if (isDoFilterOnCtrId())
            return ctrId;
        else
            return null;
    }

    public void setFilterOnCtrId(String filterOnCtrId) {
        this.filterOnCtrId = filterOnCtrId;
    }

    public boolean isDoFilterOnCtrId() {
        return "Y".equalsIgnoreCase(this.filterOnCtrId);
    }

    public String getFilterOnCtrId() {
        return filterOnCtrId;
    }
}
 

Resources

Download the complete JDeveloper 10.1.3.2 Application: LovWithContextFilter.zip.