Oracle FND - 已知某功能或子菜单查询所在职责及路径SQL

原创已于 2024-11-21 15:58:21 修改 · 794 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #responsibility #menu

于 2019-04-17 23:24:24 首次发布

Oracle学习笔记专栏收录该内容

50 篇文章

订阅专栏

本文介绍了一种使用Oracle SQL查询菜单权限的方法，通过复杂的连接和条件筛选，确保只有被授权的用户才能访问特定的菜单项。此查询考虑了菜单层级、用户权限及组织ID，适用于企业级应用权限管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT substr(tree.path, 4) || '<--' || resp.responsibility_name path
  FROM (SELECT sys_connect_by_path(nvl(t.prompt, '(HIDDEN PROMPT)'), '<--') path
              ,sys_connect_by_path(t.menu_id, ',') menu_path
              ,t.menu_id
              ,t.function_id
          FROM fnd_menu_entries_vl t
         START WITH nvl(t.function_id, -1) =
                    nvl('&func_id', nvl(t.function_id, -1))
                AND nvl(t.sub_menu_id, -1) =
                    nvl('&sub_menu_id', nvl(t.sub_menu_id, -1))
        CONNECT BY PRIOR t.menu_id = t.sub_menu_id
               /* added by yusuf 1-feb-2024
                * add connection addition to rule out entries without grant.
                */
                AND PRIOR t.GRANT_FLAG = 'Y') tree
      ,fnd_responsibility_vl resp
      ,fnd_profile_option_values org
 WHERE resp.menu_id = tree.menu_id
      /* added by yusuf 23-may-2019
       * add additions to rule out responsibilities without available 
       * path to the function or sub menu wanted.
       */
   AND NOT EXISTS
 (SELECT 1
          FROM fnd_resp_functions frf
         WHERE frf.responsibility_id = resp.responsibility_id
           AND frf.rule_type = 'F'
           AND frf.action_id = nvl('&func_id', -1))
   AND NOT EXISTS
 (SELECT regexp_substr(tree.menu_path, '[^,]+', 1, LEVEL) menu_id
          FROM dual
        CONNECT BY LEVEL <= regexp_count(tree.menu_path, ',')
        INTERSECT
        SELECT to_char(frf.action_id)
          FROM fnd_resp_functions frf
         WHERE frf.responsibility_id = resp.responsibility_id
           AND frf.rule_type = 'M')
   AND resp.responsibility_name LIKE '%&resp_name%'
      /* updated by yusuf 20-may-2019
       * add out join in order to show responsibilities without org id.
       */
   AND org.level_value(+) = resp.responsibility_id
   AND org.level_id(+) = 10003
   AND org.profile_option_id(+) = 1991
   AND org.profile_option_value(+) =
       nvl('&org_id', org.profile_option_value(+))

注：查询结果职责在最右端，即路径需从右向左读。

---- version 0.2

---- features: 使用REVERSE函数使结果方便阅读

---- bugs: 某些数据场景下可能不适用

SELECT resp.responsibility_name || '-->' ||
       utl_raw.cast_to_varchar2(REVERSE(substr(tree.path, 7))) path
  FROM (SELECT sys_connect_by_path(REVERSE(utl_raw.cast_to_raw(nvl(t.prompt,
                                                                   '(HIDDEN PROMPT)')) || ''),
                                   'E3D2D2') path,
               sys_connect_by_path(t.menu_id, ',') menu_path,
               t.menu_id,
               t.function_id
          FROM fnd_menu_entries_vl t
         START WITH nvl(t.function_id, -1) =
                    nvl('&func_id', nvl(t.function_id, -1))
                AND nvl(t.sub_menu_id, -1) =
                    nvl('&sub_menu_id', nvl(t.sub_menu_id, -1))
        CONNECT BY PRIOR t.menu_id = t.sub_menu_id
               AND PRIOR t.grant_flag = 'Y') tree,
       fnd_responsibility_vl resp,
       fnd_profile_option_values org
 WHERE resp.menu_id = tree.menu_id
   AND NOT EXISTS
 (SELECT 1
          FROM fnd_resp_functions frf
         WHERE frf.responsibility_id = resp.responsibility_id
           AND frf.rule_type = 'F'
           AND frf.action_id = nvl('&func_id', -1))
   AND NOT EXISTS
 (SELECT regexp_substr(tree.menu_path, '[^,]+', 1, LEVEL) menu_id
          FROM dual
        CONNECT BY LEVEL <= regexp_count(tree.menu_path, ',')
        INTERSECT
        SELECT to_char(frf.action_id)
          FROM fnd_resp_functions frf
         WHERE frf.responsibility_id = resp.responsibility_id
           AND frf.rule_type = 'M')
   AND resp.responsibility_name LIKE '%&resp_name%'
   AND org.level_value(+) = resp.responsibility_id
   AND org.level_id(+) = 10003
   AND org.profile_option_id(+) = 1991
   AND org.profile_option_value(+) =
       nvl('&org_id', org.profile_option_value(+))