- 掛牌上市企業
- 60秒人工響應
- 99.99%連通率
- 7*24h人工
- 故障100倍補償
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.czbl888.cn】 |
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
