Oracle EBS中查询Profile的各种SQL

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

set long 10000set pagesize 500set linesize 160column SHORT_NAME format a30column NAME format a40column LEVEL_SET format a15column CONTEXT format a30column VALUE format a40select 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_nodeswhere node_id = v.level_value2),(select node_name from fnd_nodeswhere node_id = v.level_value2)||'-'||rsp.responsibility_key)),'UnDef') "CONTEXT",v.profile_option_value VALUEfrom 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 orgwhere p.profile_option_id = v.profile_option_id (+)and p.profile_option_name = n.profile_option_nameand upper(p.profile_option_name) in ( select profile_option_namefrom fnd_profile_options_tlwhere upper(user_profile_option_name)like upper('%&user_profile_name%'))and usr.user_id (+) = v.level_valueand rsp.application_id (+) = v.level_value_application_idand rsp.responsibility_id (+) = v.level_valueand app.application_id (+) = v.level_valueand svr.node_id (+) = v.level_valueand org.organization_id (+) = v.level_valueorder 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 VALUEFROM     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 orgWHERE    p.profile_option_id       = v.profile_option_id (+)AND      p.profile_option_name     = n.profile_option_nameAND      usr.user_id (+)           = v.level_valueAND      rsp.application_id (+)    = v.level_value_application_idAND      rsp.responsibility_id (+) = v.level_valueAND      app.application_id (+)    = v.level_valueAND      svr.node_id (+)           = v.level_valueAND      org.organization_id (+)   = v.level_valueAND      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 VALUEfrom 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 orgwhere p.profile_option_id = v.profile_option_id (+)and p.profile_option_name = n.profile_option_nameand usr.user_id (+) = v.level_valueand rsp.application_id (+) = v.level_value_application_idand rsp.responsibility_id (+) = v.level_valueand app.application_id (+) = v.level_valueand svr.node_id (+) = v.level_valueand org.organization_id (+) = v.level_valueand Upper(n.user_profile_option_name) like upper('INV:Debug Level')order by short_namewhere you will prompt for the User_Profile_Option_Name you want to check and you will put theProfile name that you want to check, for example: Apps Servlet AgentIf 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, forexample: SYSADMIN

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

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