文章內容

Oracle EBS中查詢Profile的各種SQL

發布時間: 2012/7/24 16:47:56

1.List E-Business Suite Profile Option Values For All Levels
1.set long 10000
2.set pagesize 500 3.set linesize 160 4.column SHORT_NAME format a30 5.column NAME format a40 6.column LEVEL_SET format a15 7.column CONTEXT format a30 8.column VALUE format a40 9.select p.profile_option_name SHORT_NAME, 10.n.user_profile_option_name NAME, 11.decode(v.level_id,
12.10001, 'Site', 13.10002, 'Application', 14.10003, 'Responsibility', 15.10004, 'User', 16.10005, 'Server', 17.10006, 'Org', 18.10007, decode(to_char(v.level_value2), '-1', 'Responsibility', 19.decode(to_char(v.level_value), '-1', 'Server', 20.'Server+Resp')), 21.'UnDef') LEVEL_SET, 22.decode(to_char(v.level_id),
23.'10001', '', 24.'10002', app.application_short_name, 25.'10003', rsp.responsibility_key, 26.'10004', usr.user_name, 27.'10005', svr.node_name, 28.'10006', org.name, 29.'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key, 30.decode(to_char(v.level_value), '-1', 31.(select node_name from fnd_nodes 32.where node_id = v.level_value2), 33.(select node_name from fnd_nodes 34.where node_id = v.level_value2)||'-'||rsp.responsibility_key)), 35.'UnDef') "CONTEXT", 36.v.profile_option_value VALUE
37.from fnd_profile_options p, 38.fnd_profile_option_values v,
39.fnd_profile_options_tl n,
40.fnd_user usr,
41.fnd_application app,
42.fnd_responsibility rsp,
43.fnd_nodes svr,
44.hr_operating_units org
45.where p.profile_option_id = v.profile_option_id (+) 46.and p.profile_option_name = n.profile_option_name 47.and upper(p.profile_option_name) in ( select profile_option_name 48.from fnd_profile_options_tl 49.where upper(user_profile_option_name) 50.like upper('%&user_profile_name%')) 51.and usr.user_id (+) = v.level_value 52.and rsp.application_id (+) = v.level_value_application_id 53.and rsp.responsibility_id (+) = v.level_value 54.and app.application_id (+) = v.level_value 55.and svr.node_id (+) = v.level_value 56.and org.organization_id (+) = v.level_value 57.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
1.SELECT   p.profile_option_name                                                                                                     profile_option_name                                                                ,
2.         n.user_profile_option_name                                                                                                user_profile_option_name                                                                      ,
3.         DECODE(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 'UnDef') LEVEL_SET                                                                 , 4.         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", 5.         v.profile_option_value VALUE
6.FROM     fnd_profile_options p      , 7.         fnd_profile_option_values v,
8.         fnd_profile_options_tl n   ,
9.         fnd_user usr               ,
10.         fnd_application app        ,
11.         fnd_responsibility rsp     ,
12.         fnd_nodes svr              ,
13.         hr_operating_units org
14.WHERE    p.profile_option_id       = v.profile_option_id (+) 15.AND      p.profile_option_name     = n.profile_option_name 16.AND      usr.user_id (+)           = v.level_value 17.AND      rsp.application_id (+)    = v.level_value_application_id 18.AND      rsp.responsibility_id (+) = v.level_value 19.AND      app.application_id (+)    = v.level_value 20.AND      svr.node_id (+)           = v.level_value 21.AND      org.organization_id (+)   = v.level_value 22.AND      v.PROFILE_OPTION_VALUE LIKE '207' 23.ORDER BY short_name,level_set;
3.How To Find All Users With A Particular Profile Option Set?
1.select p.profile_option_name SHORT_NAME,
2.n.user_profile_option_name NAME, 3.decode(v.level_id,
4.10001, 'Site', 5.10002, 'Application', 6.10003, 'Responsibility', 7.10004, 'User', 8.10005, 'Server', 9.'UnDef') LEVEL_SET, 10.decode(to_char(v.level_id),
11.'10001', '', 12.'10002', app.application_short_name, 13.'10003', rsp.responsibility_key, 14.'10005', svr.node_name, 15.'10006', org.name, 16.'10004', usr.user_name, 17.'UnDef') "CONTEXT", 18.v.profile_option_value VALUE
19.from fnd_profile_options p, 20.fnd_profile_option_values v,
21.fnd_profile_options_tl n,
22.fnd_user usr,
23.fnd_application app,
24.fnd_responsibility rsp,
25.fnd_nodes svr,
26.hr_operating_units org
27.where p.profile_option_id = v.profile_option_id (+) 28.and p.profile_option_name = n.profile_option_name 29.and usr.user_id (+) = v.level_value 30.and rsp.application_id (+) = v.level_value_application_id 31.and rsp.responsibility_id (+) = v.level_value 32.and app.application_id (+) = v.level_value 33.and svr.node_id (+) = v.level_value 34.and org.organization_id (+) = v.level_value 35.and Upper(n.user_profile_option_name) like upper('INV:Debug Level') 36.order by short_name 37.
38.where you will prompt for the User_Profile_Option_Name you want to check and you will put the 39.Profile name that you want to check, for example: Apps Servlet Agent 40.
41.If you want to check on the users level then you can append a condition : and v.level_id = 10004, 42.same goes for Responsibility level then append the condition v.level_id = 10003. 43.
44.If you want for a certain user, then you can append a condition: and usr.user_name = '&User_Name' 45.where you will prompt for the User_Name and then you will put the user you want to check, for 46.example: SYSADMIN

本文出自：億恩科技【www.endtimedelusion.com】

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

服務器租用

服務器托管

機柜批發

云服務器

建站俠

空間/域名

安全保姆

幫助類別

幫助中心

文章內容

Oracle EBS中查詢Profile的各種SQL

同類文章

億恩公告

在線客服