始创于2000年 股票代码:831685
咨询热线:0371-60135900 注册有礼 登录
  • 挂牌上市企业
  • 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.enkj.com】

服务器租用/服务器托管中国五强!虚拟主机域名注册顶级提供商!15年品质保障!--亿恩科技[ENKJ.COM]

  • 您可能在找
  • 亿恩北京公司:
  • 经营性ICP/ISP证:京B2-20150015
  • 亿恩郑州公司:
  • 经营性ICP/ISP/IDC证:豫B1.B2-20060070
  • 亿恩南昌公司:
  • 经营性ICP/ISP证:赣B2-20080012
  • 服务器/云主机 24小时售后服务电话:0371-60135900
  • 虚拟主机/智能建站 24小时售后服务电话:0371-60135900
  • 专注服务器托管17年
    扫扫关注-微信公众号
    0371-60135900
    Copyright© 1999-2019 ENKJ All Rights Reserved 亿恩科技 版权所有  地址:郑州市高新区翠竹街1号总部企业基地亿恩大厦  法律顾问:河南亚太人律师事务所郝建锋、杜慧月律师   京公网安备41019702002023号
      0
     
     
     
     

    0371-60135900
    7*24小时客服服务热线