Oracle EBS - SQL&PL/SQL: 查询Profile设置，具体到Site、App、Resp、User

最新推荐文章于 2024-12-29 17:40:42 发布

原创最新推荐文章于 2024-12-29 17:40:42 发布 · 333 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#查询Profile设置 #具体到SiteAppRespUser

SQL & PL/SQL 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个复杂的 SQL 查询案例，用于从 Oracle 数据库中检索特定的配置文件选项信息。该查询涉及多个表的联接操作，并使用了子查询来增强灵活性和可读性。通过这个例子，读者可以了解到如何在实际业务场景中运用 SQL 联接技巧。

SELECT pro.profile_option_name,
       pro.user_profile_option_name,
       lev.level_type TYPE,
       --lev.level_code,
       lev.level_name,
       prv.profile_option_value
  FROM apps.fnd_profile_options_vl pro,
       applsys.fnd_profile_option_values prv,
       (SELECT 10001 level_id,
               'Site' level_type,
               0 level_value,
               'Site' level_code,
               'Site' level_name
          FROM dual
        UNION ALL
        SELECT 10002 level_id,
               'App' level_type,
               app.application_id level_value,
               app.application_short_name level_code,
               app.application_name level_name
          FROM apps.fnd_application_vl app
        UNION ALL
        SELECT 10003 level_id,
               'Resp' level_type,
               resp.responsibility_id level_value,
               resp.responsibility_key level_code,
               resp.responsibility_name level_name
          FROM apps.fnd_responsibility_vl resp
        UNION ALL
        SELECT 10004 level_id,
               'User' level_type,
               usr.user_id level_value,
               usr.user_name level_code,
               usr.user_name level_name
          FROM applsys.fnd_user usr) lev

 WHERE pro.profile_option_id = prv.profile_option_id(+)
   AND prv.level_id = lev.level_id(+)
   AND prv.level_value = lev.level_value(+)
      
      --参数Profile，下面连个条件用一个即可
   AND pro.user_profile_option_name LIKE 'CUX%' --Profile名称
 ORDER BY pro.profile_option_name, lev.level_type, lev.level_name;