How to select profile option values for a profile option name with setup level details

Following query provides details of each profile option value at Site, Application, Responsibility, User, Organization, Server levels. The output gives the profile option name, profile option value,level at which each profile value is set(site, application, responsibility, user etc.).

Note:- Standard Package "FND_PROFILE_OPTION_VALUES_PKG" can be used to
            insert/update/delete profile option values at various levels.

Application Name,Responsibility Name, User Name if a profile is setup at each of the respective level:

Parameters:

• User_Profile_Option_Name(can give partial value)

• P_level_id(Values should be as mentioned from the below level_ids)

•     10001  -  Site           
•     10002  -  Application
•     10003  -  Responsibility
•     10004  -  User
•     10005  -  Server
•     10007  -  Organization

SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",

       DECODE (level_id,

               '10001', 'Site',

               '10002', 'Application',

               '10003', 'Responsibility',

               '10004', 'User',

               '10005', 'Server',

               '10007', 'Organization'

              ) AS "LEVEL",

       fpov.profile_option_value AS "Value", fpo.profile_option_name,

       SUBSTR (fat.application_name, 1, 20) AS "Application",

       (SELECT responsibility_name

          FROM fnd_responsibility_tl frt

         WHERE frt.responsibility_id = fpov.level_value

           AND fpov.level_id = 10003) "Responsibility",

       (SELECT user_name

          FROM fnd_user fu

         WHERE user_id = fpov.level_value

           AND fpov.level_id = 10004) "User"

  FROM fnd_profile_options fpo,

       fnd_profile_options_tl fpot,

       fnd_application_tl fat,

       fnd_profile_option_values fpov

 WHERE fpo.application_id = fat.application_id

   AND fpov.application_id = fat.application_id

   AND fpov.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = fpot.profile_option_name

   AND fpov.level_id = NVL (&p_level_id, fpov.level_id)

   AND UPPER (fpot.user_profile_option_name) LIKE

          NVL (UPPER ('%&User_Profile_Option_Name%'),

               UPPER (fpot.user_profile_option_name)

              );