AVIO Consulting

Using a BPM Process Variable on an ADF form to Retrieve Database Information

Jul 14, 2016 | BPM

ADF projects used in BPM applications are often a mix of BPM process variables as well as ADF view object data control attributes. This is necessary on Oracle BPM projects because the BPM process might only store only an ID and the ADF forms might subsequently use the ID to extract the detailed information from the database for the end user to view on the ADF form.

The purpose of this blog is to show you an approach to dynamically retrieve and display information from a database table based on a BPM process variable’s value. I have seen this done with backing beans, but in my opinion the approach shown below is quicker and reduces the time required for testing.

Fields populated automatically on ADF

Two sections below specifically address how this is done in two different use cases:

  • During ADF form initialization – to see how to initialize an ADF page with prepopulated database information based on a BPM variable, go to the “Initialize the ADF Form With Values Read from the Database” section
  • When the BPM variable changes on an ADF form – to see how to automatically display new information on the ADF form when the BPM variable changes, go to the “Steps to Dynamically Refresh Fields from the Database When the ID Changes” section


Here is a summary of the steps that you will perform:

  1. Create the entity object, view object and application module for the database access (see the “What Has Already Been Done for You” and “Set the Application Module to Point to the HR Database” sections below)
  2. Create a BPM project with an interactive activity and associated human task (see the “What Has Already Been Done for You” section)
  3. Create an ADF form in a project for the interactive activity’s human task (see the “What Has Already Been Done for You” section)
  4. Add the view object’s database bound attributes to the form (see the “Add the View Object Fields to the ADF Form” section)
  5. Modify the page / task flow so that the ADF form initializes at runtime with the database values populated based on the BPM variable’s id (see the “Initialize the ADF Form With Values Read from the Database” section)
  6. Change to the ADF form so that when the value in the BPM id variable’s field on the form is modified, the view object attributes are refreshed with the correct values from the database table (see the “Steps to Dynamically Refresh Fields from the Database When the ID Changes” section)

What Has Already Been Done For You

The focus of this is to provide step-by-step instructions that show how to create a form that has this dynamic behavior.  In this example, the sample HR EMPLOYEES table that comes with Oracle Database 10g and 11g will be used.

If you downloaded the starting point of the application using the link shown above, these three things have already been done for you:

1. The Employees table in the HR database schema has been exposed in the separate ADF business component project named “EnrollmentAdfProject” shown below. The application module that will be referenced by the ADF form’s data control is called “AppModuleDataControl”. The view object that will be used to access the database’s Employees table is the “EmployeesView” shown below.

Application Module and Data Control

2. An Oracle BPM process has been created and the process has an BPM process object variable that contains the Employee id information to be entered and displayed on the ADF form. Here the “enrollment” process object variable has an attribute called “id” that is used to carry the employee id information through the process. The “Set Id Field” initiator interactive activity shown below already exists in the project. It creates the work item instance and allows the end user to set the BPM object’s enrollment Id field. The second of the two interactive activities shown below already has a human task and an simle ADF form that will be enhanced to include view object attributes.

Oracle BPM Process

3. The two ADF forms associated with the two interactive activities both look like this.

ADF Forms Already Created

Set the Application Module to Point to the HR Database

After having downloaded the project from the link provided, change the application module’s configuration to point to your HR database. If you do not know how to create jndi connection to a database in the WebLogic console, click here for step-by-step instructions. To point the project’s application module to your HR database, perform these steps.

1. Open the downloaded application.

2. In the Application tab, expand the EnrollmentAdfProject.

3. As shown below, right mouse click the AppModule application module -> click Configurations.

Application Module's Configurations

4. Click Edit.

5. Change the text in the Datasource Name field to your JNDI connection’s name on WebLogic (it is jdbc/hr in the environment shown below) -> click OK.

JNDI Connection

6. Click the second configuration called AppModuleShared -> repeat steps 4, 5 and 6 above -> click OK.

7. Click the Save All icon.

Add the View Object Fields to the ADF Form

In this section, you will drag the attributes from the view object that are bound to the database into the form. To save time, only the first and last name fields will be added for this example.

1. In the Application tab, open the Data Controls accordion section -> click the refresh icon in the Data Controls section.

Data Controls Refresh

2. In the Data Controls accordion section, expand the contents of the Application Module named AppModuleDataControl -> expand EmloyeesView1.

Expand Employees View

3. Add the first of the two database bound fields by selecting dragging the FirstName attribute from EmployeesView1 just below the existing ID BPM variable field that is already on the form.

Add Database Bound Attribute

4. From the pop-up, click Text -> ADF Input Text w/ Label.

Add ADF Input Text

5. Your form should now look like this.

First Name Added to Form

6. Drag the LastName field from the Data Controls accordion section into the form and repeat steps 5 and 6. Your form should now look like this.

Last Name Added to Form

7. Click the Save All icon.

Initialize the ADF Form With Values Read from the Database

In this section, you will make the form read the database and show the first and last names for the person associated with the ID when the form is first initialized. When the form was created, a page flow was automatically created. A new method will be added to the page flow that dynamically populates the database information into the form based on the id entered. This is done by adding an operation that to the user interface’s page flow that binds the BPM object variable to the database view.

1. In the Applications tab in the ADF UI project you created in section D, expand the Web Content folder -> expand the Page Flows folder.

Expand Page Flows Folder

2. Double click the task flow in this folder that ends with the text “_TaskFlow”.

Open Page Flow

3. The ADF form you have been working on is in the taskDetails1_jspx view on this page flow. Drag this view to the right to make room for the new operation (method call) that will be added in the next few steps.

Move the View on the Page Flow

4. In the Data Controls accordion section, expand EmployeesView1 -> expand Operations.

Expand the Operations

5. Scroll down the operations until you see the setCurrentRowWithKeyValue operation -> drag this operation into the page flow to the left of the existing view.

Drag the Operation into the Page Flow

6. To associate the value currently in the form’s ID field to the primary key for the table’s row being retrieved, click the Value dropdown -> select Show EL Expression Builder.

Set the Value Dropdown

As shown below, expand ADF Bindings -> expand data -> expand the binding that ends in the text “…_taskDetails1PageDev”.

Expand the Data Binding

Scroll down and select the ID attribute in the list -> click OK. As shown below, the Value field should now be populated -> click OK. This causes the form when initialized to automatically display the database table’s row information associated with the ID BPM variable.

Set the Value Dropdown to the BPM Variable

Note the new method call icon was added to the page flow as result of performing the last few steps.

Method Call Added to Page Flow

7. To connect the method call with the taskDetails1_jspx view, in the Component Palette on the right side scroll down and click Control Flow Case.

Connect using a Control Flow Case

Click the SetCurrentRowWithKeyValue method call and then click the taskDetails1_jspx. Your page flow should now look like this.

Final Version of the Page Flow

8. One more modification needs to be made before this project can be tested. You can tell the taskDetails1_jspx view is the current default activity because of the halo surrounding it. The new SetCurrentRowWithKeyValue method call needs to be the default activity so that it is invoked before the ADF form is displayed. To set this to be the default activity in the page flow, click anywhere in the white space in the page flow diagram -> in the Properties panel, click the Default Activity dropdown.

Default Activity Dropdown

Select the SetCurrentRowWithKeyValue method call from the list.

Set Method Call as the Default

The page flow should now look like this. Note the halo that now surrounds the method call.

Final Version of the Page Flow

Now when the second activity in the process is selected in the Workspace task list, the BPM ID variable entered in the first activity will be used to retrieve the row in the table that matches that id.

9. Click the Save All icon.

Deploy and Test the Application

1. Deploy the EnrollmentProject BPM project.

2. Deploy the two ADF UI projects.

3. Log into the Workspace as a user authorized for the BPM process’s role where the activities in the process are located.

4. From the Applications folder in the upper left corner of the Workspace, click Enrollment (the name of the BPM process with the two interactive activities) -> once the ADF form initializes, enter 111 in the ID field (111 is a valid number for the EMPLOYEE_ID primary key column of the EMPLOYEES table in the HR database) -> click Submit.

5. From your list of tasks in the Workspace, click the work item instance that was just created. Note the first and last names automatically populated from the database.

Dynamically Initialized Form

Steps to Dynamically Refresh Fields from the Database When the ID Changes

When testing, note that the user interface works fine when the form is first initialized. Based on the id entered in the first activity in the process, the first and last names are read and displayed correctly in the second activity in the process. The problem is that when the id is changed, the first and last names are not updated dynamically from the database. This section describes how to automatically update the field values on a form immediately when another field’s value changes.

1. If it is not already open, open the taskDetails1.jspx ADF form in the DynamicallyLoadFromTheDatabaseUI ADF project.

2. The first and last name fields need to recognize when the id has changed so click the ID field on the form -> change the Autosubmit property to true. Note also the value of the Id for this field (in this example it is “it7”). This value will be used by the first and last name fields to recognize when the ID has changed.

Set ID field to Autosubmit

Click the FirstName field on the form -> in the Properties tab, scroll down and change the PartialTriggers property to be the id of the ID field (“it7” in this example).

First Name Partial Trigger

Click the LastName field on the form and change its value to be the id of the ID field (again “it7” in this example).

Last Name Partial Trigger

3. Click the Bindings tab .

Bindings Tab

4. Add a new binding by clicking the plus icon in the Bindings section.

Add New Binding

Select action -> click OK.

Select Action Binding

Expand AppModuleDataControl -> select EmployeesView1 -> select setCurrentRowWithKeyValue from the Operation dropdown -> change the Value by clicking this field’s dropdown -> clicking Show El Expression Builder -> expand ADF Bindings -> expand data -> expand dynamicallyloadfromdatabaseui_taskDetails1PageDef -> scroll down and select ID from the list.

Set Value in Binding

5. Add a new Executable by clicking the plus icon in the Executables section.

Add New Executable

Click invokeAction.

Add Invoke Action

Give it a meaningful name and select setCurrentRowWithKeyValue from the dropdown.

Invoke Action

Drag the new executable to just below taskIterator in the list.

Drag New Executable Up in the List

With the new executable still selected in the list, select ifNeeded as the Refresh property in the Properties panel.

Set Refresh Property to ifNeeded

6. Select EmployeesView1Iterator in the Executables list and change its Refresh property to ifNeeded as well.

Set Entity View Refresh to ifNeeded

7. Redeploy the ADF project and retest the user interface. Change the value in the ID field from 111 to 112 and tab off the field. You should now notice that the first and last name fields change automatically when the id field is changed.