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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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(+))