Oracle Application Framework (OAF): Random Scripts and SQLs

 

Introduction:

Some example SQLs (queries) to check and review Oracle Application Framework components within an Oracle database. These scripts/queries are useful to:

1. Ø Review custom and standard OA Objects
2. Ø Remove or disable personalizations
3. Ø Check for existing object for extension or modifications
4. Ø Compare current functionality of a UI with standard functionality

Note: Personalizations can be enabled/disabled at SITE, Responsibility and User levels within EBS.

SQLs:

All Personalizations: All OAF Personalizations within EBS – Both Custom and Standard personalizations are listed by this SQL:

-- all oaf object personalization - custom and standard
SELECT PATH.PATH_DOCID PERZ_DOC_ID,
jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH
FROM JDR_PATHS PATH
WHERE PATH.PATH_DOCID IN
(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS
WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'
AND COMP_ID IS NULL)
ORDER BY PERZ_DOC_PATH;
 

Personalizations within a Module: All personalizations within an EBS Module. In this example, OIE (Oracle Internet Expenses à iExpenses) is used. Substitute with any EBS Standard module codes to get its customizations.

 

SELECT PATH.PATH_DOCID PERZ_DOC_ID,
jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH
FROM JDR_PATHS PATH
WHERE PATH.PATH_DOCID IN
(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS
WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'
and jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) like '%oie%'            -- all from OIE - iExpenses
AND COMP_ID IS NULL)
ORDER BY PERZ_DOC_PATH;
 

 

All Objects within a Module: List of all OAF objects within a module. The module AP (Account Payables) is used here. Change module code accordingly (Eg: OE for Order Entry, ONT for Order management, PO for Purchasing, etc)

-- list all page, regions, customizations, personalizations
set serveroutput on;
set linesize 300;
 
DECLARE
BEGIN
jdr_utils.listdocuments('/oracle/apps/ap/', TRUE);
END;
/
 

  

JDR_UTILS Functions: JDR_UTILS is a standard package that provide lot of utilities to manage OA Framework pages and files. Here are some major functions available for OAF Troubleshooting usage:

JDR_UTILS Functions:
listCustomizations
printDocument
deleteDocument
listDocuments

 

Example jdr_utils.listCustomizations:

 

jdr_utils.listCustomizations()
This procedure can be used to check whether any personalization exists for a particular page or  substitution exists for a particular EO/VO/AM.
 begin 
  jdr_utils.listCustomizations('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
  jdr_utils.listCustomizations('/xxabc/oracle/apps/fnd/xxabc/server/XxabcVO'); 
 end; 
 

 

Example jdr_utils. printDocument:

 

jdr_utils.printDocument()
This procedure can be used to get the Page / Personalization / Substitution file. You can pass the output of the above procedure as a parameter to this procedure to get the details.
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/webui/customizations/site/0/XxabcPG'); 
 end; 
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/server/customizations/site/0/XxabcVO'); 
 end;
 

 

Example jdr_utils. deleteDocument:

 
 begin  
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/webui/customizations/site/0/XxabcPG'); 
 end; 
 begin 
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/server/customizations/site/0/XxabcVO'); 
 end; 
 

 

Example jdr_utils. listdocuments:

This procedure will print all the files under the specified path.
 begin 
   jdr_utils.listDocuments('/xxabc/oracle/apps/fnd/xxabc/webui'); 
 end; 
 
You can add an additional parameter to recursively print all the documents under the specified path
 begin 
   jdr_utils.listDocuments('/xxabc/oracle/apps/fnd/xxabc',true); 
 end; 
 
 

 

Keywords:

OAF, OA Framework, Oracle Application Framework, Oracle, UIX, Customization, Personalization, Application Module, PG.XML, Query, SQL, SQLPLUS