oracle ebs级联查询,Oracle EBS中查询Profile的各种SQL

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.enkj.com】