Setting VO query dynamically in OAF

Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the java code). 

In some scenarios we might have a requirement to change the VO query dynamically.

 Step-1:

For Example:

Let’s say VO (XxpoheaderVO) has the query as

SELECT  poh.po_header_id,  poh.type_lookup_code, 

        poh.segment1 po_number,

        poh.enabled_flag,

        to_char(poh.creation_date,'DD-MON-YYYY') cdate, 

        poh.org_id

FROM    po_headers_all   poh

WHERE   poh.segment1=NVL(:P_PONUMBER,poh.segment1)

AND     poh.org_id=NVL(:P_ORG,poh.org_id);

Step-2:

Let's assume the Query to be changed dynamically. (as highlighted in Code Snippet below)

Step-3:

----- Code Snippet Start ------------- 

String query=

   ” SELECT  poh.po_header_id,  poh.type_lookup_code, 

        poh.segment1 po_number,

        poh.enabled_flag,

        to_char(poh.creation_date,'DD-MON-YYYY') cdate, 

        poh.org_id

FROM    po_headers_all   poh

WHERE   poh.segment1=NVL(:P_PONUMBER,poh.segment1)

AND     poh.org_id=NVL(:P_ORG,poh.org_id);

AND EXISTS 

      ( SELECT 1

        FROM   po_lines_all  pol

        WHERE  pol.po_header_id=poh.po_header_id

        AND    pol.item_id =NVL(:P_ITEM_ID,pol.item_id))”;

XxpoheaderVO Impl voheader = am.get XxpoheaderVO 1();

if (voheader == null)

    {

     MessageToken[] tokens = { new MessageToken("OBJECT_NAME", " XxpoheaderVO Impl") };

     throw new OAException("AK", "FWK_TBX_OBJECT_NOT_FOUND", tokens);

     }

    voheader.setFullSqlMode(voheader.FULLSQL_MODE_AUGMENTATION);

    voheader.setQuery(query);

    voheader.setWhereClauseParams(null);

    voheader.setWhereClauseParam(0,PoNumStr); //PONUMBER

    voheader.setWhereClauseParam(1,orgIdStr);// ORGID

    voheader.executeQuery();

---------- Code Snippet End ---------------

Note :

• setQuery only sets the new query to the View Object, in order to effect the changes of the query we need to execute the query using below statement.
• Always need to call setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION) before executing the query. If not,  OA Framework will not append the where clause and Order by clauses correctly
• The above code is written in controller ( you can write the code in either processRequest method or in processFormRequest) or based on your requirement in AM.