Description:
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 terms correctly
- The above code is written in controller ( you can write the code in either process request method or in processFormRequest) or based on your requirement in AM.
Summary:
This Post explained what the steps should follow to create setting VO query dynamically in OAF
Queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
