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.

  • October 17, 2014 | 27 views