Profile option value at any level

Find out value of profile option at any level from backend.
User have to enter the name of profile during the execution of script.
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' -- 'AFLOG_ENABLED'
         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');
Profile option value at any level

Tags
