Check Various Profile Options Enabled in R12

===================================================================================
You 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 you wish to quickly see all Profile Option values
outside of these screens

You 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 for you to enter a "profile_name"

Enter the value for the profile option name you wish to display

This value is case insensitive and is encased by % in the where clause

For example, if you enter the value  '%FND%Debug%' (do not enter the ' characters)
then you will see around 15 entries including:-

    FND: Debug Log Level
    FND_DEBUG_RULE_THRESHOLD

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


NOTE - the REM commands are for documentation purposes only and can be ignored

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

 
/* Formatted on 2010/11/02 15:17 (Formatter Plus v4.8.8) */
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',
                 'UnDef'
                ) level_set,
         DECODE (TO_CHAR (v.level_id),
                 '10001', '',
                 '10002', app.application_short_name,
                 '10003', rsp.responsibility_key,
                 '10005', svr.node_name,
                 '10006', org.NAME,
                 '10004', usr.user_name,
                 '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 n.user_profile_option_name LIKE '%&Profile_option%'
     AND p.profile_option_id = v.profile_option_id(+)
     AND p.profile_option_name = n.profile_option_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
--and n.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

==============================================================

 /* Formatted on 2010/11/02 15:26 (Formatter Plus v4.8.8) */
SELECT   SUBSTR (e.profile_option_name, 1, 30) PROFILE,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User'
                ) l,
         DECODE (a.level_id,
                 10001, 'Site',
                 10002, c.application_short_name,
                 10003, b.responsibility_name,
                 10004, d.user_name
                ) lvalue,
         NVL (a.profile_option_value, 'Is Null') VALUE,
         SUBSTR (a.last_update_date, 1, 25) updated_date
    FROM fnd_profile_option_values a INNER JOIN fnd_profile_options e
         ON a.profile_option_id = e.profile_option_id
         LEFT OUTER JOIN fnd_responsibility_tl b
         ON a.level_value = b.responsibility_id
         LEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id
         LEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id
   WHERE e.profile_option_name LIKE '%&Profile_option%'
ORDER BY profile_option_name;

===================================================================================