Oracle EBS中查詢Profile的各種SQL

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 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]