ADF BC: Part 3: LOV 2: Cascading / Dependent LOVs

In the Last Post we saw how to create LOVs declaratively. This is one cool feature of ADF framework. In this post we will take the concept to a different level altogether in implementing Cascading or Dependent LOVs.
Take example of filling out an address form online which has Country, State and City. List of States depends on the Country chosen and the List of Cities depends on the State chosen. If you change Country then both the State and City LOV should refresh to see new Values. If State is changed then the City LOV should refresh to show the list of cities corresponding to that State.

This is all done declaratively, so Props to Oracle for implementing such an amazing feature out of the Box! Lets see this in all of its glory with an example.

For our example we are using the HR schema of XE Database, however I have chosen to create a new table and inserted some values

CREATE TABLE ADDRESS_MASTER
AS
SELECT DISTINCT COUNTRY_ID, STATE_PROVINCE,CITY
FROM LOCATIONS;

You should have your Locations in the XE database in HR Schema.

This table would have all the list of Countries, State and Cities.

I have also created another table which will hold the address user enters:

CREATE TABLE ADDRESSES 

(

  ADDRESS_LINE_1 VARCHAR2(100 BYTE) 

, ADDRESS_LINE_2 VARCHAR2(100 BYTE) 

, COUNTRY_ID VARCHAR2(20 BYTE) 

, STATE_PROVINCE VARCHAR2(100 BYTE) 

, CITY VARCHAR2(100 BYTE) 

) 

Continuing with the same project as the last post, lets invoke the Business Components Wizard and create a EO AddressesEO and an updatable VO AddressesVO.

Now Lets create separate VOs for Country, State and City. Lets create them individually as we will have to modify the Query.

Right click on uiview and create a Read Only VO for Country as shown below:

In the next screen give the Query as below:

select distinct country_id from address_master

Keep clicking next to finish. Similarly create two more Read only VOs for State and City.

Right click on uiview package and create Read Only View with name "StateProvinceVVO". In the sql write the following query:

select distinct state_province

from address_master

where country_id = :p_country_id

In the Bind Variable screen, create a Bind Variable p_country_id:

IMP: Bind variables are case sensitive and it should exactly match the bind variable defined in the Query.

Keep clicking next and finish.

Create another Read Only VO, CityVVO with the following query:

select distinct city

from address_master

where country_id = :p_country_id

and nvl(state_province,'1')=nvl(:p_state_province,nvl(state_province,'1'))

Please note the use of nvl on both sides which is required when there is no State defined.

This time create two bind variables p_country_id and p_state_province as shown below:

Finally the ADF BC components should look like below:

Since we need to add Country, State and City to Address, we will have to create View Accessors on AddressesVO. Open AddressesVO and goto View Accessor and add CityVVO, StateProvinceVVO and CountryVVO as View Accessors as shown below:

Click on CityVVO1 and click on Edit and enter CountryId against p_country_id and StateProvince against p_state_province as shown below.

Similarly for State_Province give CountryId against p_country_id as shown below:

IMP: Please note that when we are giving country_id and the state_province, we are matching that to the value in the AddressesVO. So this should exactly match the Attribute name of the record which is being created or modified, in our case, AddressesVO.

Lets create LOVs for the attributes of AddressesVO. Click on Country_Id of AddressesVO and create LOV with the following configuration:

In the UIHints move CountryId to the right hand side.

Click on StateProvince Attribute and add LOV, chose the configuration as shown below:

Chose StateProvince to the right hand side.

Click on City attribute and do the configuration as shown below:

In the UI Hints move City to the right hand side.

We are all set to run the Application Module (also called Tester). Right click on AppModuleAM -> Double tax on Addresses1 and add new record. Check how StateProvince and City change:

Now that our building blocks for the data is ready we can implement it on a jsf page.

Create a New Page Named RegisterAddress (Right Click on ViewController -> New -> Web Tier -> JSF -> JSF Page, give  the name as RegisterAddress). Drag Addresses1 from DataControl and create as Form->ADF From. In the resulting page, Edit Form Fields, chose Default options click on Ok.

From Operations under Addresses1 (this is important as you would be doing Create and Delete on the Addresses Record) on DataControl, drag CreateInsert and Delete to the top of the form field that you just created. It should look as shown below:

Click on Bindings for the page and it should look like below:

Make sure that CountryId and StateProvince's Auto Submit property is chosen as "true". For StateProvince chose the Partial Trigger as Country Id as shown below:

Similarly, for City, chose the Partial Trigger as Country and StateProvince.

Thats it. Now run the page, and when the page is up Press CreateInsert to create a Record and then enter your values. Change the Country and State to see the values changing.

You might wonder why its required when everything on Tester worked fine. The problem is that the Page does not know the changes done at the ADF BC level (Since they are only loosely coupled). For other fields to be made aware on the changes we have to submit them when they change (thats why Auto Submit for Country and State) and since only the dependent fields have to be made aware of this change (in this case StateProvince and City), the Partial trigger is chosen on the fields which are changed earlier. Thats the reason when you change the Country, both StateProvince and City fields get reset, but when you change the StateProvince only City gets reset.

Please note that, even if you make City's Partial Trigger as only State (instead of Country and State both), this would work fine as changing country would refresh State and that would in-turn refresh City.