PL-SQL Code to find Menu Hierarchy of a responsibility

1SET SERVEROUTPUT ON;
2
3DECLARE
4   CURSOR c_main
5   IS
6      SELECT DISTINCT a.menu_id, fm.MENU_NAME
7        FROM fnd_responsibility_vl a, fnd_menus fm
8       WHERE     responsibility_name LIKE 'Your Responsibility Name'
9             AND (end_date IS NULL OR end_date > SYSDATE)
10             AND fm.MENU_ID = a.MENU_ID
11             AND MENU_NAME LIKE 'Your Menu Name';
12
13   CURSOR c_sub (p_menu_id NUMBER)
14   IS
15                 SELECT LEVEL padding,
16                        menu_id,
17                        (SELECT menu_name
18                           FROM fnd_menus fm
19                          WHERE fm.menu_id = fme.menu_id)
20                           menu_name,
21                        entry_sequence,
22                        sub_menu_id,
23                        (SELECT menu_name
24                           FROM fnd_menus fm
25                          WHERE fm.menu_id = fme.sub_menu_id)
26                           submenu_name,
27                        function_id
28                   FROM fnd_menu_entries fme
29             CONNECT BY menu_id = PRIOR sub_menu_id AND entry_sequence > -9e125
30             START WITH menu_id = p_menu_id AND entry_sequence > -9e125
31      ORDER SIBLINGS BY entry_sequence;
32BEGIN
33   FOR r_main IN c_main
34   LOOP
35      FOR r_sub IN c_sub (r_main.menu_id)
36      LOOP
37         IF r_sub.MENU_NAME LIKE '%' --Add criteria to print only specific menus e.g. '%XX%'
38         THEN
39            DBMS_OUTPUT.put_line (r_sub.MENU_NAME);
40         END IF;
41
42         IF r_sub.SUBMENU_NAME LIKE '%' --Add criteria to print only specific menus e.g. '%XX%'
43         THEN
44            DBMS_OUTPUT.put_line (r_sub.SUBMENU_NAME);
45         END IF;
46      END LOOP;
47   END LOOP;
48END;