Monday, November 30, 2009

Setting Query Dynamically in View Object. (Why in some cases even after using setQuery(), VO picks default query.)

Couple of time while making view object in OAF pages, you have conditions where instead of where clause or order by clause, you need to change the entire source of sql query of View Object.
There can be n number of such scenarios, for example you are using a querybean in a search page, and in a certain condition, you have to change your query, your VO is attached to a LOV and in certain condition you need the query to change in order to optimize the VO sql query etc. Following points always keep in mind while using setQuery():

1) The new query you want to set in the view object should have same column name and types as the original VO query.

2)As per OAF coding standards all coding related to setting where clause, order by clause or setQuery should be written in ViewObjectImpl class, so in such case, you should generate ViewObjectImpl class.

3)Most Important :Always call vo.setFullSqlMode(FULLSQL_MODE_AUGMENTATION) before calling setquery() 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:

a) The new query that you have programmatically set takes effect when you call setQuery and execute the query.
b) If you call setWhereClause or if a customer personalizes the criteria for your query 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/LOV region while using setQuery().

12 comments:

Nisar said...

I tried to use vo.setFullSqlMode(FULLSQL_MODE_AUGMENTATION);

But my class does not compile .. am i missing something ?

Error(46,27): field FULLSQL_MODE_AUGMENTATION not found in class erco.oracle.apps.ap.oie.entry.header.webui.ercoHeaderKffCO

Mukul said...

Typically this code should go in VOimpl class , where FULLSQL_MODE_AUGMENTATION is defined in master class, so this code will work. Anyways, in CO use,
VOImpl.FULLSQL_MODE_AUGMENTATION, then this should compile.

Rudra. said...

Hi Mukul,

I have a requirement in HGRID where if parent row is checked, all child rows have to be dynamically checked and vice versa. I am able to achieve it after expanding the HGRID for first time. But before expanding even for once, I can not catch the child VO and its rows. How can I achieve this then?

Thanks,
Rudra

Rudra. said...

Pls ignore. I have got that. Thanks.

Arya said...
This comment has been removed by the author.
Arya said...

Even though included the function this.setFullSqlMode(FULLSQL_MODE_AUGMENTATION) , default query defined in the VO fires !

I have one independent and one dependent LOV . I have written the select query with out where clause as the default query ,and setWhereClause though code . The dependent LOV behaves as an independent LOV.

How can i get rid of this issue .

Thanks in advance,

Arya

sachin said...
This comment has been removed by the author.
sachin said...

Hi,
I am using the same process described above to set a dynamic query for my VO.
I have self link to the same VO in viewlink. I want to create a tree structure from the result set. But I can only see the first parents, no child is visible.

Does this something to do with the viewlink? Viewlink should work same as it does if query is specified in design mode, but it is not.

Thanks

Deven Shah said...

I have a similar requirement where I need to dynamically change VO's SQL for an LOV item.

Should I do that in CO - ProcesRequest Call using method implied in this post?

If so, can someone please either provide a sample or confirm it will work will be greatly appreciated.

Thanks
Deven

My World said...

hi mukul,
how can we reset query to view object after setquery

My World said...

hi mukul,
how can we reset query to view object after setquery

My World said...

hi mukul,
how can we reset query to view object after setquery