<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=269747997384049&amp;ev=PageView&amp;noscript=1">

AVIO Insights

Using a Database Driven List of Values to Populate a BPM Variable

(This is an excerpt from Lesson 14 "Oracle BPM and ADF (Part 2)" from AVIO Consulting's new self-paced online Oracle BPM Developer Workshop training.)

The purpose of this post is to show how to populate an ADF form field's dropdown with values from a database table.  Once an item in the list of values (LOV) is selected from the dropdown, this shows to have the selected item automatically populate a BPM process's data object variable. 

Although the steps documented here may seem simple, for most of us this seems to be one of the most difficult things to learn how to do on your own.  If you understand ADF but not Oracle BPM, you will struggle getting this to work.  If you understand Oracle BPM but not ADF, I believe it is almost impossible to get this to work on your own. 

Project Starting Point

Attached is a starting point project that already has a BPM process that has a variable, human task, and an associated ADF form.  Click here to download this application built using Oracle BPM  This assumes you have access to the standard HR database schema that comes with Oracle XE.

Create a New ADF Model Project

Here you will expose the database into a separate project reserved for the ADF model objects.  Once you add the project, you will use a wizard to add the Entity Objects, View Objects and Application Module that will be used to display the values in the dropdown.

1. With the starting point application already open, click FileNew → click the All Technologies tab → enter adf in the search field → select ADF Model Project from the list. 

ADF Model

Click OK → Call the project ADFModel → click Next → ensure the Default Package is set to model → click Finish.

2. Expose the JOBS HR database schema's table by right mouse clicking the new Model project → New → with adf in the search field, click Business Components from Tables from the list → OK.

ADF Model

3. Add the connection information for your HR database schema → click Test Connection and if it is successful click OK OK.

ADF Model

Click the Query button → click Jobs → shuttle it to the right Selected list by clicking the > button → click Next → click Next.

ADF Model

4. Click the Query button again in the Read Only View Object step → shuttle JOBS over again as you did in the last step → click Next.

ADF Model

5. In the Application Module step, ensure the Application Module checkbox is checked → ensure the Package is set to model.module → change the Name to be HrAppModuleAM → click Finish.

ADF Model

6. In the Application tab, expand the new Model project.  Because you will use the JobsVO as a list of values, rename it to JobsLOV.  This is done by right mouse clicking JobsVO → click Refactor → click Rename → change the name to JobsLOV.

7. Click Save All.

Set the Application Module to Point to the HR Database

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. In the Application tab, expand the Model project.

2. As shown below, right mouse click the HrAppModuleAM application module → click Configurations.

ADF Model

3. Click the Edit button.

4. 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.

ADF Model

5. Click the second configuration called HrAppModuleShared → repeat steps 3, 4 and 5 above → click OK.

6. Click Save All.

Add the Job List of Values to the ADF Form

In this section, you will add the LOV dropdown to the ADF form.  While doing this you will specify the existing BPM variable's title variable to be populated when an item from the list populated from the database is selected.

In the Application tab, expand the CreateEmployeeCandidate project.  This was already created for you from the BPM project's human task.

ADF Model

As shown above, double click the taskDetails.jspx web page → scroll down the page and click the Title field under the Employee Info - Employee Summary - Position section (note this text - it will help you find the BPM variable in the next step) → with the Title field selected, note the text in the Properties tab for the Value field: #{bindings.Title.inputValue}.

In this step you will add the BPM variable's title field into the form again.  While doing this, you will associate it with the JobsLOV.  Expand the Data Controls accordian tab on the left → Refresh the Data Controls list → expand CreateEmployeeCandidate_CreateEmployeeCandidate → expand getTaskDetails(...)  → expand Return → expand Task → expand Payload → expand Employee Info → expand Employee Summary → expand Position → click Title → drag the title attribute into the form just below the existing title field.

ADF Model

From the popup, select Single Selection Select One Choice.

ADF Model

Note that this populates the Base Data Source (top dropdown shown below).  Click the Add button next to the List Data Source dropdown.

ADF Model

Because the JobsLOV is in the HrAppModuleAMDataControl, expand it.  Select Jobs1 → click OK.

ADF Model

As shown below, change the Data Value to Title (the BPM attribute) → change the List Attribute to JobTitle (the list of values from the database table's column to show in the dropdown) → change the Display Attribute to JobTitle → click OK.

ADF Model

Prepare to Test the List of Values Dropdown

Because this process consists of a single step, it is useful to immediately verify that the item selected in the database actually populates the BPM variable correctly after changing the value in the dropdown.  To do this, you will use the original title field and update it with the value picked from the dropdown automatically.  Once testing has been completed, you should delete the original Title text field so all the end user would see is the one dropdown.

To have the value in the dropdown automatically insert the value picked in the text field above it, click the new Title dropdown you just added.  In the properties tab, set its AutoSubmit property to true → note that the Id of the dropdown is soc4.

ADF Model

To have the original Title field recognize when a value in the title dropdown has changed, click the original Title field directly above the dropdown → in the Properties tab, click the "v" to the right of the PartialTriggers → click Edit.

 ADF Model

Scroll down and pick selectOneChoice - soc4 from the list → click the > icon to add this to the selected list.

ADF Model

Deploy and Test the Application

1. Deploy the BPM project named BPM_ADF_Examples.

2. Deploy the CreateEmployeeCandidate ADF UI project.

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 BPMADFExamples (the name of the BPM process with the interactive activity) → once the ADF form initializes, click the Title dropdown → select one of the titles from the list.

ADF Model

Note the same value that was selected from the dropdown now appears also in the original Title text field above it. 

ADF Model


Understanding the steps documented here are essential to understanding how the database and BPM process variables can be connected.  Although there are other techniques to have accomplished this, I believe that these steps are the most straightforward. 

4 minute read