Searching for Profile Options
I use this query to find profile option values in Oracle as there isn't a quick way to find the value for more than 1 responsibility or user at a time.
Note, if there is a profile with no value at any level, then the query will not return any results.
SELECT user_profile_option_name, DECODE (level_id, '10001', 'Site', '10002', 'Appl', '10003', 'Resp', '10004', 'User', '10005', 'Server', level_id) AT, DECODE (level_id, '10001', ' ', '10002', (SELECT application_name FROM apps.fnd_application_tl WHERE application_id = level_value), '10003', (SELECT responsibility_name FROM apps.fnd_responsibility_tl WHERE responsibility_id = level_value AND application_id = v.level_value_application_id), '10004', (SELECT user_name FROM apps.fnd_user WHERE user_id = level_value), level_value) "level_value", profile_option_value, u.user_name updated_by, v.last_update_date FROM apps.fnd_profile_options o, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl tl, apps.fnd_user u WHERE o.profile_option_name = tl.profile_option_name AND o.profile_option_id = v.profile_option_id AND tl.user_profile_option_name LIKE '&profile' AND u.user_id = v.last_updated_by ORDER BY user_profile_option_name, level_id, level_value, profile_option_value;