Automatically return nested records from ADF List of Values – for example allocate all subordinates on project along with manager

In a previous post I was telling you about an ADF project we are discussing for a chain of interior decorating shops. One challenge we encountered in the functional specifications is that whenever a salesman is discussing a possible decoration – wallpaper, curtain, carpet etc. – for a room, he can select the (primary) material from a list of values and add it to the actions for that particular room. However, associated with such primary materials are auxillary materials – such as glue, nails, curtain rails etc. When the salesman selects a primary material from the List of Values to have it returned to the set linked to the room decorating action, the auxilliary materials should be added to the set as well.

Turning this to more familiar terms, we could state a similar requirement as follows: we have a master-detail page for Projects and Project Allocations. From the Project Allocations table, we can open a List of Values to select one or multiple Employees. Each employee can have a set of associated employees that should automatically be added to any project the primary employee is added to. As a simple example, let’s assume that whenever we allocate a manager on a project, all of her or his subordinates should automatically be allocated on the project as well. And of course we want to see this automatic allocation right away, without having to save the allocation to the database.

In this example, I have opened the Employee List of Values window for the current allocation for Elizabeth Bates. In the list of values window, I have selected Nancy Greenberg, the Finance Manager. I press the Select button. Nancy Greenberg is returned as selected value and the subordinates of Nancy are also added to the project:

As a result, not just Nancy is allocated but her five subordinates as well.

In this article I will describe how I have implemented this functionality, starting from an ADF Application generated by JHeadstart.

Model 

The first challenge I addressed was the definition of the ‘associated values’. In this example, it is very easy to determine from the database using a simple query which records are associated with any particular employee:

select employee_id
,      first_name
,      last_name  
from   employees subs 
where  subs.manager_id = :employeeId

I decided to create an attribute Subordinates of type (java.sql.)ResultSet in ViewObject EmployeesView, using the expression:

cursor( select employee_id
        ,      first_name
        ,      last_name  
        from   employees subs 
        where  subs.manager_id = employees.employee_id
        ) subs

 

However,  this failed with ORA-22902: CURSOR expression not allowed, because of the way ADF BC wraps the Query statement and turns it into a subquery. As a workaround, I created a PL/SQL function:

 create or replace
 function get_subordinates
 ( p_employee_id in number
 ) return sys_refcursor 
 is
     sub_refcur      SYS_REFCURSOR;
 BEGIN
     OPEN sub_refcur 
     FOR  select employee_id
          ,      first_name
          ,      last_name  
          from   employees subs 
          where  subs.manager_id = p_employee_id
          ;
     return sub_refcur;
 end; 

that returns the cursor expression as a SYS_REFCURSOR. The Expression for my VO Attribute Subordinates now becomes: get_subordinates(employee_id ).

ViewController

The ViewController project is largely generated using JHeadstart. There are three Groups: Projects, Project Allocations (Detail) and LovEmployees (for the List of Values window, allowing multiple selection of employees).  

Now the ‘magic’ starts. The class oracle.jheadstart.controller.jsf.bean.LovItemBean is the one in the JHeadstart runtime framework that takes care of returning the (multiple) selected records from a List of Values popup to the underlying table or form. In order to deal with additional nested records (attributes of type ResultSet tied to a Ref Cursor), I have to extend this class. The extended class is called ExtendedLovItem, and is in package oracle.jheadstart.controller.jsf.bean. Note: It is somewhat unfortunate that method createNewRow() in JhsCollectionModel has been defined as protected; this forces me to create the ExtendedLovItem class in the same package as JhsCollectionModel and its superclass, namely oracle.jheadstart.controller.jsf.bean.

In the ExtendedLovItem I have to override two methods. The first is copyMultiSelectReturnValues(). This method kicks in when the user has made a selection in a multiple select LOV window and has returned to the invoking page. The method copyMultiSelectReturnValues iterates over the selected (LOV) records and turns each one into a new row in the invoking table or form. We need a little intervention in this method: for each record selected in the LOV window, we want to verify whether it has any associated, nested records and if so add those to the invoking table or form as well. The first step is to add a line right after the call to copyReturnValues:

  // LJ: deal with nested records - any key/values of type ResultSet
  copyNestedReturnValues(rows[0]);
 

This is a call to a new method that we will add right now:

    public void copyNestedReturnValues(Row lovRow) {
        Iterator keys = getReturnValues().keySet().iterator();
        while (keys.hasNext()) {
            String lovItem = (String)keys.next();
            Object value = lovRow.getAttribute(lovItem);
            if (value instanceof ResultSet) {
                processResultSet((ResultSet)value);
            }
        }
    }
 

This method loops over the return values defined in the (Extended)LovItemBean. For every Attribute of type ResultSet it encounters, it will invoke processResultSet.

    private void processResultSet(ResultSet rs) {
        // Fetch each row from the result set
        ViewObject vo = 
            getLovPage().getIterBinding().getRowSetIterator().getRowSet().getViewObject();
        try {
            while (rs.next()) {
                    String patternStr = ",";
                    String[] mappings = nestedRecordAttributeMapping.split(patternStr);
                    Row newRow = getTableCollectionModel().createNewRow(true);
                    for (int i=0;i<mappings.length;i++) {
                        System.out.println(mappings[i]);
                        String[] sourceTarget = mappings[i].split("=");
                        newRow.setAttribute(sourceTarget[1], rs.getObject(sourceTarget[0]));                                            
                    }
            }
        } catch (SQLException e) {
            // TODO
        }
        // We need to reset table component tree, because otherwise some of the items
        // set on the newly created row are not immediately visible in the page when
        // returning from the LOV (bug??)
        getTableCollectionModel().refresh();
        JsfUtils.getI
nstance().resetComponentTree(getTableCol
lectionModel().getTable());
    }
 

Finally, we need to make sure that the method copyReturnValues() does not choke on attributes of type ResultSet, so we explicitly skip those, by including these lines directly following the declaration and value assignment of the value variable.

  Object value = lovRow != null ? lovRow.getAttribute(lovItem) : selectedRowData.get(lovItem); 
  // new code to supported nested records          
  if (value instanceof ResultSet) {
    // do not process this key/item any further
    continue;
  }
 

Now we have the runtime processing in place, provided that the proper managed bean definition is created; instead of referencing the regular LovItemBean, we now need ExtendedLovItemBean. And we need to add the Subordinates attributes to the list of attributes to return from the LOV. And we need to specify the mapping between the nested record attributes and the attributes in the new records. We do that in three steps:

1. create a new custom template for the ExtendedLovItemBean
2. associate that template to the ProjectAllocations group
3. add the Subordinates attribute to the list of attributes to return from the LOV

1. The New Custom Template is called extendedLovItemInTableBean.vm and created in the directory ViewController\templates\custom. It has to modifications compared to the default version: it referenced the ExtendedLovItemBean class rather than the ‘plain’ LovItemBean and it has a new managed property nestedRecordAttributeMapping that specifies how values in the cursor records are mapped to the new ProjectAllocation records.

## revision_history
## 01-jul-2007   Lucas Jellema
## 1.2           Use ExtendedLovItemBean to process cursor with nested items, set mappings for Nested Subordinates
## 30-sep-2006   JHeadstart Team
## 1.1           Revision history added, base version is 10.1.3.0.97 (SU1)
#set ($bean = ${JHS.current.managedBean})
#set ($item = ${JHS.current.item})

<managed-bean>
  <managed-bean-name>${bean.beanName}</managed-bean-name>
  <managed-bean-class>oracle.jheadstart.controller.jsf.bean.ExtendedLovItemBean</managed-bean-class>
  <managed-bean-scope>request</managed-bean-scope>
  <managed-property>
    <property-name>lovFieldBinding</property-name>
    <value>#{bindings.${item.bindingName}}</value>
  </managed-property>
  <managed-property>
     <property-name>tableCollectionModel</property-name>
    <value>#{${JHS.current.group.name}CollectionModel}</value>
  </managed-property>
  <managed-property>
    <property-name>lovPage</property-name>
    <value>#{${item.lovGroupName}Page}</value>
  </managed-property>        
  <managed-property>
    <property-name>lovSearchBean</property-name>
    <value>#{search${item.lovGroupName}}</value>
  </managed-property>
  <managed-property>
    <property-name>returnValues</property-name>
    <map-entries>
      #foreach ($returnValue in ${item.returnValues})
      <map-entry>
        <key>${returnValue.source}</key>  
        <value>${JHS.current.group.name}${returnValue.target}</value> 
      </map-entry>
      #end
    </map-entries>
  </managed-property>
<managed-property>
    <property-name>nestedRecordAttributeMapping</property-name>
    <value>employee_id=EmpId,employee_id=Id</value>
  </managed-property>
  <managed-property>
    <property-name>copyToModel</property-name>
    <value>true</value>
  </managed-property>
  <managed-property>
    <property-name>itemsToClear</property-name>
    <list-entries>
      #foreach ($item in ${item.dependentItemsToClear})
        <value>${item.bindingName}</value> 
      #end
    </list-entries>
  </managed-property>
</managed-bean> 

2. The template is associated to the ProjectAllocations group by setting the template property in the JHeadstart Application Editor:

3. add the Subordinates attribute to the list of attributes to return from the LOV

In the JHeadstart Application Definition editor, I add a new return item for the Subordinates source attribute. It is does not matter which target attribute I link it to, as it is not really mapped to a target attribute but instead results in new (target) records:

Resources

Download the JDeveloper 10.1.3.2 Application with the sources for the above described functionality:  LovReturnNestedRecords.zip.