Fusion Blog

EBS Blog


Contact Us

OA Framework - All Articles
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

Requirement: To dynamically set VO query based on a condition or event in OAF

Consider, you come across a requirement wherein you need to show the regions consuming a particular VO based on few conditions that are decided dynamically. It is a known fact that the query of the VO plays the major role in the result that any region shows. Hence setting the VO query dynamically based on conditions decided by user action can be the most appropriate solution. Yes, you read it right.. This can be easily achieved using the concept of setQuery on the desired view object.

Step 1: Create a new OA workspace and OA project

Create a OA workspace with file name as: SetVOQuery

Create a OA project with file name as: SetVOQuery

Default package: oaf.oracle.apps.fnd.query

Once your project is created, double click on SetVOQuery project and select Project content.

Click on 'Add' button next to the bottom pane in the Project content and select only that package which you want have in your project.

Click OK and save your project.





Step 2: Create a ADF Business component - Application Module AM

Right click on SetVOQuery project -> click New -> select ADF Business components -> select Application Module

Package: oaf.oracle.apps.fnd.query.server

AM Name: QueryAM


Check Application Module Class: QueryAMImpl Generate Java File(s)

Step 3: Create a OA Components page

Right click on SetVOQuery project -> click New -> select OA components under Web Tier -> select Page

Package: oaf.oracle.apps.fnd.query.webui

Page Name: DynamicQueryPG


Step 4: Set Page properties

Select on the DynamicQueryPG page and go to structure pane where a region of type 'pageLayout' and ID 'region1' is automatically created.

Click on region1 in structure page and set the project properties as in the below screenshot - set all the properties except the controller class, for now.


Step 5: Set new Controller

Select PageLayoutRN in the structure pane -> Right click on it -> Set new controller

Package: oaf.oracle.apps.fnd.query.webui

Controller name: DynamicSetQueryCO

This automatically sets the controller class property in DynamicQueryPG page properties


Step 6: Create a ADF Business component - View Object

Right click on SetVOQuery project -> click New -> select ADF Business components -> select View Object

Package: oaf.oracle.apps.fnd.query.server

VO Name: QueryVO


Keep click 'Next' until you land at step-5(SQL Statement) of VO creation.

Paste the following SQL statement in the 'Query Statement' block as in the below screenshot:


Ensure all the above mentioned column names comes up in the attributes step in VO creation


In step-8(Java) of VO creation uncheck all the pre-selected checkboxes and select View Row Class: ViewObjRowImpl -> Generate Java File -> Accessors check box Accept all other defaults in VO creation, click Finish to create the VO under the package specified.


Step 7: Attach VO to AM

After creating VO successfully, we must associate this VO with AM we have created

Right click on QueryAM -> Edit QueryAM

Select 'Data Model' on the window that pops up


You should find QueryVO listed in the left panel, Shuttle the QueryVO to the right pane to associate it with the QueryAM which is displayed as QueryVO1 as in the below screenshot

Click on Apply and OK to save the changes made to QueryAM


Step 8: Create Advanced Table

Select PageLayoutRN right click -> New -> Region

In the region’s property window:

Set ID as MainRN

Set Region Style as header


Again, Select MainRN -> New -> Region and set the region properties as in below screenshot:

advanced table properties apps2fusion article2


Step 9: Create columns and items in Advanced table

Create Column1

Select MainTableRN -> New -> column

Select column1 -> New -> Item

Now, set the properties for Item as in the below screenshot:

column item properties apps2fusion article2

Now, create column header as follows:

Under column1 right click on column header under column Components folder -> Select New -> select sortableHeader

Set the properties for the column header created as in the below screenshot:

column header properties apps2fusion article2


Create 2 more columns as column2 and column3 similar to the above with the properties set as shown in the following screenshots:

column2 item properties apps2fusion article2 column2 header properties apps2fusion article2


column3 item properties apps2fusion article2  column3 header properties apps2fusion article2


After creating all the columns, your page layout looks something like the below one:

page layout apps2fusion article2


Step 10: Create a submit button

Select PageLayoutRN right click -> New -> Item

Set the item properties as in the below screenshot:

submit button properties apps2fusion article2


Step 11: Load advanced table with data on page load

Put the below code in the processRequest method of DynamicSetQueryCO:




    QueryAMImpl am = (QueryAMImpl)pageContext.getApplicationModule(webBean);    

   QueryVOImpl voimpl = (QueryVOImpl)am.findViewObject("QueryVO1");    




Step 12: Capture button click event in CO

Here we write the code for capturing the button click event in the PFR. The code will ‘setQuery’ on the QueryVO created and reloads the advanced table with the filtered result according to the query set dynamically:





QueryAMImpl am = (QueryAMImpl)pageContext.getApplicationModule(webBean);    

QueryVOImpl voimpl = (QueryVOImpl)am.findViewObject("QueryVO1");    

voimpl.setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION); //Important!

voimpl.setQuery("SELECT ADDRESS_ID, " +











Warning: Ensure that the query string that you pass to the setQuery method has all the columns that was specified in the VO query while creating the VO itself. Otherwise, it might result in AttributeLoadException exception.

It is also important to maintain the order in which columns were specified in the VO query while creating the VO - otherwise, this will result in wrong/random results showing up.

Few Important Points about setQuery as per OAF standards:

If you use setQuery() on a view object associated with the query bean results region, then you should also call vo.setFullSqlMode(FULLSQL_MODE_AUGMENTATION) on the view object. This ensures that the order by or the WHERE clause, that is generated by OA Framework, can be correctly appended to your VO. The behavior with FULLSQL_MODE_AUGMENTATION is as follows:

1. The new query that you have programmatically set takes effect when you call setQuery and

execute the query.

2. If you call setWhereClause or if a customer personalizes the criteria for your region, BC4J augments the whereClause on the programmatic query that you set.

For example:

select * from (your programmatic query set through setQuery) where (your programmatic where clause set through setWhereClause) order by (your programmatic order set through setOrderBy)

The same query is changed as follows if a customer adds a criteria using personalization:

select * from (your programmatic query set through setQuery) where (your programmatic where clause set through setWhereClause) AND (additional personalization where clause) order by (your programmatic order set through setOrderBy)

Warning: If you do not set FULLSQL_MODE_AUGMENTATION, the whereClause and/or the orderBy, which was set programmatically, will not augment on the new query that you set using setQuery. It will instead augment on your design time VO query.

  • Due to timing issues, always use the controller on the query region for any OAQueryBean specific methods.

  • When using query regions in a multi-page flow, the query regions must have unique IDs

Now run your DynamicQueryPG and the result will be as follows:

Result on Page Load:

Personalized Advanced Table

Address ID   Address Name       Telephone No.
1                 MicroEdge WH1      345 565 4565

2                 MicroEdge WH1      345 565 7665

3                 MicroEdge HQ        345 987 7809

4                 HotChip HQ            555 000 7890  

5                 LooseWire HQ        602 000 7890
6                 Purchasing             345 789 0000

7                 HQ                        786 000 5678

8                 Manufacturing        703 000 8670

Result on button click:

Address ID   Address Name       Telephone No.
1                 MicroEdge WH1      345 565 4565

2                 MicroEdge WH1      345 565 7665

3                 MicroEdge HQ        345 987 7809

4                 HotChip HQ            555 000 7890



Roopa jetR

Add comment

Security code

About the Author

Roopa jetR

Roopa jetR is an budding OAF developer.

LinkedIn contact: https://www.linkedin.com/in/roopajetR

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner