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().
