Script to find out the values of a profile option

It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. The following prompts for the profile name that a user sees. You could also query for the internal profile code instead by using the column profile_option_name instead.

SELECT   b.user_profile_option_name "Long Name",

         a.profile_option_name

               "Short Name",

         DECODE (

            TO_CHAR (c.level_id),

            '10001', 'Site',

            '10002', 'Application',

            '10003', 'Responsibility',

            '10004', 'User',

            'Unknown'

         ) "Level",

         DECODE (

            TO_CHAR (c.level_id),

            '10001', 'Site',

            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),

            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),

            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),

            'Unknown'

         ) "Level Value",

         c.profile_option_value "Profile Value",

         c.profile_option_id

               "Profile ID",

         TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",

         NVL (d.user_name, TO_CHAR (c.last_updated_by))

               "Updated By"

    FROM apps.fnd_profile_options a,

         apps.fnd_profile_options_vl b,

         apps.fnd_profile_option_values c,

         apps.fnd_user d,

         apps.fnd_user e,

         apps.fnd_responsibility_vl g,

         apps.fnd_application h

   WHERE b.user_profile_option_name LIKE '&ProfileName'

     AND a.profile_option_name = b.profile_option_name

     AND a.profile_option_id = c.profile_option_id

     AND a.application_id = c.application_id

     AND c.last_updated_by = d.user_id(+)

     AND c.level_value = e.user_id(+)

     AND c.level_value = g.responsibility_id(+)

     AND c.level_value = h.application_id(+)

ORDER BY b.user_profile_option_name,

         C.level_id,

         DECODE (

            TO_CHAR (C.level_id),

            '10001', 'Site',

            '10002', NVL (h.application_short_name, TO_CHAR (C.level_value)),

            '10003', NVL (g.responsibility_name, TO_CHAR (C.level_value)),

            '10004', NVL (e.user_name, TO_CHAR (C.level_value)),

            'Unknown'

         );