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

 

Useful SQL/Query - EBS Descriptive Flex Fields (DFF)

Here are a few SQLs to help working with Oracle EBS (E-Business Suite) Descriptive Flex Fields. You may have to make appropriate changes for your environment.

 

 

All custom DFFs - contexts, fields, values by module/application

 

SELECT APL.APPLICATION_ID, APL.APPLICATION_NAME,

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG   -- , APL.*

FROM FND_DESCR_FLEX_COL_USAGE_VL FUSG, FND_DESCRIPTIVE_FLEXS_VL FDF, FND_FLEX_VALUE_SETS FFV, FND_APPLICATION_VL APL

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

 -- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

 -- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

 -- AND FUSG.ENABLED_FLAG = 'Y'

 AND FUSG.CREATED_BY >= 1000

 AND FDF.APPLICATION_ID = APL.APPLICATION_ID

 AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

 AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY APL.APPLICATION_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

All DFF definitions in the system, includes lot of standard fields

This may be useful, if you are more comfortable to filter in Excel

 

SELECT FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG, FND_DESCRIPTIVE_FLEXS_VL      FDF, FND_FLEX_VALUE_SETS           FFV

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

-- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

-- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

-- AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY FDF.APPLICATION_TABLE_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

 

 

 

Query to find DFF information

 

SELECT FDF.TITLE                             "DFF TITLE",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME"

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG,

 FND_DESCRIPTIVE_FLEXS_VL      FDF,

 FND_FLEX_VALUE_SETS           FFV

WHERE FDF.TITLE = 'Employee Confirmation Date'         -- Replace this with Flex Field Title

AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US' 

AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY

 FUSG.DESCRIPTIVE_FLEXFIELD_NAME,

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE,

 FUSG.COLUMN_SEQ_NUM;

Oracle Data Base (DB) Link Introduction

Business Need:

Database Links are used to connect from one database to a remote database without exposing/repeating connection details to the end user.

Oracle DB 12g is used for these SQLs. But the SQLs will be applicable most of the recent versions.

DB Link Creation:

Here is the command to create DB Link:

CREATE DATABASE LINK DBLINK_NAME

CONNECT TO REMOTE_SCHEMA IDENTIFIED BY REMOTE_PWD

USING 'REMOTE_TNSSTRING';

To use the DB Link, the REMOTE_TNSSTRING needs to be defined in the DB Server’s TNSNAMES.ORA file, you are connecting to. Otherwise this will give TNS Error.

 

DB Link Creation without TNS Definition:

Here is command to create DB Link to a remote database whose TNS String is not defined in the server:

CREATE DATABASE LINK DBLINK_NAME

    CONNECT TO REMOTE_SCHEMA IDENTIFIED BY REMOTE_PWD

    USING '(DESCRIPTION=

                (ADDRESS=(PROTOCOL=TCP)(HOST=<mymachine.mydomain.com>)(PORT=<1521>))

                (CONNECT_DATA=(SERVICE_NAME=<service or sid name>))

            )';

<mymachine.mydomain.com> à Domain Name or IP Address of Remote Database

<1521> à Port Number of Remote Database

<service or sid name> à Service Name of Remote Database

 

DB Link Usage:

Tables within DB Link can be accessed using the format <table name> @ <DB Link name>, just like any local table (Eg: USERS@DEV).

If frequently used, create a local synonym:

CREATE SYNONYM RTUSERS FOR USERS@DBLINK_NAME;

So the below SQLs work in the same way:

SELECT * FROM USERS@DBLINK_NAME;

SELECT * FROM RTUSERS;

  

Other Notes/Considerations:

Few points to add before using DB Links: 

1. Direct DB Links to Production might cause data security policy violation. Be careful over sensitive data exposure.
2. Query Optimization is poor across DB Links. A few steps might help improve performance over DB Links:
3. If a single table is accessed frequently, use a local temp table and fill the required data in the local table and use. DB Link to be used only in the first selection (to store data in temp table)
4. Use query-in-query format to choose remote data, if possible

Eg: Only 1 order type will be present in remote table. Use “(SELECT COL1, COL2 FROM ORDERS WHERE ORDER_TYPE = 21) X” instead of Order Type in the main query.

  

Keywords:

Oracle, DB Link, Database Link, Remote Database, TNSNAMES, Query, SQL, SQLPLUS

 

Oracle Kill/Terminate Session: Example

 Introduction:

When a SQL session is hanging for a long time, it make sense to terminate/kill the session. This is to improve developer time as well as to free up system resources in server side.

Here are some scenarios where KILL SESSION is useful:

1. System is in deadlock
2. While testing a query, developer missed a join, which makes internal query processing prohibitively expensive
3. Some sessions are taking up a lot of resources and important processes or users get very slow response

 

Steps to Kill Session:

Find the Problem Session:

Identify the session that needs to be killed. Easiest way is to get the user by Database Schema/User name. But it is possible that a single DB user may have many sessions open due to multiple reasons. In that case, finding session is tricky. Use any of the WHERE clauses in the below query to identify the session.

 

-- get session by User/Schema
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'BAI';
 
-- Get session by other parameters/inputs
SELECT SID || ',' || SERIAL# AS KILLSTR, SID, SERIAL#, X.*
FROM V$SESSION X
WHERE USERNAME = 'APPS'
AND MACHINE NOT LIKE 'XXABCWEB%' -- REMOVE WEB SERVER TO DB Server Connection
AND OSUSER NOT IN ('APPLPRD') -- REMOVE UNIX AND APPLN SERVER USERS
AND PROGRAM IN ('TOAD.EXE', 'SQL DEVELOPER', 'SQLPLUS.EXE') -- FILTER BY SQL Client Program
AND ROWNUM < 21;

 

Kill/Terminate the Problem Session:

Use inputs from above query in the KILL command

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>' IMMEDIATE;
 
ALTER SYSTEM KILL SESSION '1794,64680' IMMEDIATE
/
 

 

Keywords:

Kill Session, Terminate, Oracle, ALTER SESSION, PL/SQL, Query, SQL, SQLPLUS

Oracle Date Conversion Functions : Random Examples

 

Introduction:

Some random examples of Oracle PL/SQL Date Conversion functions. Might be useful for some and thought it worth sharing.

 

Examples:

Get current system date in default format (Easy !):

select sysdate from dual;

  

Get current system date in different formats:

select 'x ' || to_char(sysdate, 'yyyymmdd') from dual;
 
select 'x ' || to_char(sysdate, 'yyyymmdd hh24miss') from dual;
 
select 'x ' || to_char(sysdate, 'hh24miss') from dual;

 

Number of seconds since beginning of the day:

-- number of seconds since beginning of the day
select 'x ' || to_char(sysdate, 'sssss') from dual;

  

Number of days since beginning of the year:

-- number of days since beginning of year
select 'x ' || to_char(sysdate, 'ddd') from dual;
 

Get System Timestamp:

select systimestamp from dual;

This will return time up to 6 decimal places. Very useful for performance tuning/improvement as the time is recorded with microsecond accuracy.

Few other Timestamp functions/examples:

select 'x ' || to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4') from dual;
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff4') from dual;
 
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'missff6') from dual;
 

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, Date Function, Oracle, PL/SQL, Query, SQL, SQLPLUS