1.List E-Business Suite Profile Option Values For All Levels

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, \'Site\',
10002, \'Application\',
10003, \'Responsibility\',
10004, \'User\',
10005, \'Server\',
10006, \'Org\',
10007, decode(to_char(v.level_value2), \'-1\', \'Responsibility\',
decode(to_char(v.level_value), \'-1\', \'Server\',
\'Server+Resp\')),
\'UnDef\') LEVEL_SET,
decode(to_char(v.level_id),
\'10001\', \'\',
\'10002\', app.application_short_name,
\'10003\', rsp.responsibility_key,
\'10004\', usr.user_name,
\'10005\', svr.node_name,
\'10006\', org.name,
\'10007\', decode(to_char(v.level_value2), \'-1\', rsp.responsibility_key,
decode(to_char(v.level_value), \'-1\',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||\'-\'||rsp.responsibility_key)),
\'UnDef\') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name)
like upper(\'%&user_profile_name%\'))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;

2.How to Search all of the Profile Options for a Specific Value

SELECT   p.profile_option_name                                                                                                     profile_option_name                                                                ,
         n.user_profile_option_name                                                                                                user_profile_option_name                                                                      ,
         DECODE(v.level_id, 10001, \'Site\', 10002, \'Application\', 10003, \'Responsibility\', 10004, \'User\', 10005, \'Server\', \'UnDef\') LEVEL_SET                                                                 ,
         DECODE(TO_CHAR(v.level_id), \'10001\', \'\', \'10002\', app.application_short_name, \'10003\', rsp.responsibility_key, \'10005\', svr.node_name, \'10006\', org.name, \'10004\', usr.user_name, \'UnDef\') "CONTEXT",
         v.profile_option_value VALUE
FROM     fnd_profile_options p      ,
         fnd_profile_option_values v,
         fnd_profile_options_tl n   ,
         fnd_user usr               ,
         fnd_application app        ,
         fnd_responsibility rsp     ,
         fnd_nodes svr              ,
         hr_operating_units org
WHERE    p.profile_option_id       = v.profile_option_id (+)
AND      p.profile_option_name     = n.profile_option_name
AND      usr.user_id (+)           = v.level_value
AND      rsp.application_id (+)    = v.level_value_application_id
AND      rsp.responsibility_id (+) = v.level_value
AND      app.application_id (+)    = v.level_value
AND      svr.node_id (+)           = v.level_value
AND      org.organization_id (+)   = v.level_value
AND      v.PROFILE_OPTION_VALUE LIKE \'207\'
ORDER BY short_name,level_set;

3.How To Find All Users With A Particular Profile Option Set? 

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, \'Site\',
10002, \'Application\',
10003, \'Responsibility\',
10004, \'User\',
10005, \'Server\',
\'UnDef\') LEVEL_SET,
decode(to_char(v.level_id),
\'10001\', \'\',
\'10002\', app.application_short_name,
\'10003\', rsp.responsibility_key,
\'10005\', svr.node_name,
\'10006\', org.name,
\'10004\', usr.user_name,
\'UnDef\') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and Upper(n.user_profile_option_name) like upper(\'INV:Debug Level\')
order by short_name

where you will prompt for the User_Profile_Option_Name you want to check and you will put the
Profile name that you want to check, for example: Apps Servlet Agent

If you want to check on the users level then you can append a condition : and v.level_id = 10004,
same goes for Responsibility level then append the condition v.level_id = 10003.

If you want for a certain user, then you can append a condition: and usr.user_name = \'&User_Name\'
where you will prompt for the User_Name and then you will put the user you want to check, for
example: SYSADMIN

Related Topic:Using API FND_PROFILE.save to update profile from backend 

转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7652968