Re: Profile Options for a User

Posted by ErmanArslansOracleBlog on Nov 20, 2020; 1:52pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Profile-Options-for-a-User-tp9029p9030.html

The following is query the values using the profile name .. However; you can modify it to query the values for a specific user. Basically you will remove that condition and add a condition for querying according to the user name.. fu.user_name.. fnd_user = fu in this query.

SELECT
fpo.user_profile_option_name
,fpo.profile_option_name
,site.profile_option_value site_val
,r.responsibility_name resp_name
,resp.profile_option_value resp_val
,fu.user_name
,usr.profile_option_value user_val
FROM fnd_profile_options_vl fpo
,fnd_profile_option_values site
,fnd_profile_option_values resp
,fnd_responsibility_vl r
,fnd_profile_option_values usr
,fnd_user fu
WHERE 1=1
AND UPPER( fpo.user_profile_option_name) LIKE 'ICX%'    -- Like ICX for example
AND site.profile_option_id(+) = fpo.profile_option_id
AND site.level_id(+) = 10001
AND resp.profile_option_id(+) = fpo.profile_option_id
AND r.responsibility_id (+) = resp.level_value
AND resp.level_id(+) = 10003
AND usr.profile_option_id(+) = fpo.profile_option_id
AND usr.level_id(+) = 10004
AND fu.user_id(+) = usr.level_value
ORDER BY 1