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).
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.
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.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
po_headers_all poh
WHERE
poh.segment1=NVL(:P_PONUMBER,poh.segment1)
poh.segment1=NVL(:P_PONUMBER,poh.segment1)
AND
poh.org_id=NVL(:P_ORG,poh.org_id);
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)
to be changed dynamically. (as highlighted in Code Snippet below)
Step-3:
—–
Code Snippet Start ————-
Code Snippet Start ————-
String query=
” SELECT poh.po_header_id,
poh.type_lookup_code,
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
poh
WHERE
poh.segment1=NVL(:P_PONUMBER,poh.segment1)
poh.segment1=NVL(:P_PONUMBER,poh.segment1)
AND
poh.org_id=NVL(:P_ORG,poh.org_id);
poh.org_id=NVL(:P_ORG,poh.org_id);
AND EXISTS
(
SELECT 1
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();
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
ORGID
voheader.executeQuery();
———- Code Snippet
End —————
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.
Recommended Posts
