SQL Query To Find Profile Options Values Set At Different Levels

SQL Query/Script to find profile option value 

Following SQL query can be used to get the values of a particular profile option set at all levels. Following are different levels where we can set the value of a profile option:

• Site
• Application
• Responsibility
• User

The given script will lists all the values set at each level against a particular Profile option

Input:

Provide the name of the 'Profile Option while running this query.

Eg. FND: Debug Log Enabled

[You can get the exact name from table fnd_profile_options_tl]  

  SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')

ORDER BY short_name;