Query to fetch profile option for a Specific Responsibility
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like <PROFILE NAME>
and pro.profile_option_name = pro1.profile_option_name
and pro.profile_option_id = pov.profile_option_id
and resp.responsibility_name like <RESP NAME>
and pov.level_value = resp.responsibility_id (+)
and pov.level_value = appl.application_id (+)
and pov.level_value = u.user_id (+)
order by 1,2;
Query to fetch profile option values:
SELECT fpo.profile_option_name SHORT_NAME,
fpot.user_profile_option_name NAME,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002', fap.application_short_name,
'10003', frsp.responsibility_key,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
fnd_responsibility frsp,
fnd_nodes fnod,
hr_operating_units hou
WHERE fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name = fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) = fpov.level_value_application_id
AND frsp.responsibility_id(+) = fpov.level_value
AND fap.application_id(+) = fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) = fpov.level_value
AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;