Friday, 17 October 2014

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.

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete