Script To List The Values Of A Profile Option At All Levels

Script:

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name 
, substr(fpot.user_profile_option_name,1,60) profile_name 
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level 
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name 
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value 
, fpov.profile_option_value profile_value 
FROM fnd_profile_option_values fpov 

, fnd_profile_options fpo 
, fnd_profile_options_tl fpot 
, fnd_application fa 
, fnd_responsibility_tl fr 
, fnd_user fu 
WHERE (fpo.profile_option_name like nvl('X',fpo.profile_option_name) -- Replace X with the profile short name, ie 'ORG_ID' 
or fpot.user_profile_option_name like nvl('Y',fpot.user_profile_option_name)) -- Replace Y with profile user name, ie 'MO: Op%' 
and fpo.profile_option_name=fpot.profile_option_name 
and fpo.profile_option_id = fpov.profile_option_id 
and fa.application_id(+)=fpov.level_value 
and fr.responsibility_id(+)=fpov.level_value 
and fu.user_id(+)=fpov.level_value;

Script Output

Example:

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name
, substr(fpot.user_profile_option_name,1,60) profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value
, fpov.profile_option_value profile_value
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, fnd_responsibility_tl fr
, fnd_user fu
WHERE (fpo.profile_option_name like nvl('X',fpo.profile_option_name) -- Not mandatory. Replace X with profile short name, ie 'ORG_ID'
or fpot.user_profile_option_name like nvl('MO: Op%',fpot.user_profile_option_name)) -- Not mandatory. Replace Y with profile user name, ie 'MO: Op%'
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value; 

PROFILE   PROFILE   PROFILE                   PROFILE       LEVEL                     PROFILE
OPTION   SHORT       NAME                       LEVEL           VALUE                    VALUE
ID              NAME
----------   ----------    ---------------------   -----------     ----------------------  ------------
1991          ORG_ID     MO: Operating Unit    Resp              Payables Manager       204
1991          ORG_ID     MO: Operating Unit    Resp              Oracle Inventory         204
1991          ORG_ID     MO: Operating Unit    Resp              Receivables Manager  204