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

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) 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(+))

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

最后

以上就是细腻汉堡为你收集整理的Oracle FND - 已知某功能或子菜单查询所在职责及路径SQL的全部内容，希望文章能够帮你解决Oracle FND - 已知某功能或子菜单查询所在职责及路径SQL所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。