How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus?

Reference : How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus (Doc ID 201945.1)

Solution

One can display Profile Options within eBusiness Suite Release 11i and Release 12
both through the "System Administrator" responsibility (forms based) and also
through the "Functional Administrator" responsibility (OA Framework page), but
there may be times when one wishes to quickly see all Profile Option values
outside of these screens.

One can use the SQL below to query all settings for a particular profile option
or profile options.

As this script is just performing a SELECT command, it is safe to run on any
eBusiness Suite instance and has been tested for 11.5.10 and 12.0.x environments

Instructions
1. Connect to the eBusiness Suite database using APPS schema name.

2. Execute the script listed below.

3. The script will prompt to enter a "profile_name".  Enter the value for the profile option name to display.  This value is case insensitive and is encased by % in the where clause.

For example, if entering the value 'fnd%debug' (do not enter the ' characters), then will see around 15 entries including:

FND: Debug Log Level
FND_DEBUG_RULE_THRESHOLD

4. If Oracle Support has asked to run this script, then one should enable "spool" in SQLPLus to capture the output before running the script.

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name)
like upper('%&user_profile_name%'))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;