PL-SQL Code to find Menu Hierarchy of a responsibility

Application Object Library
PL-SQL Code to find Menu Hierarchy of a responsibility
Oracle apps ebs query to find Menus of responsibility
SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_main
   IS
      SELECT DISTINCT a.menu_id, fm.MENU_NAME
        FROM fnd_responsibility_vl a, fnd_menus fm
       WHERE     responsibility_name LIKE 'Your Responsibility Name'
             AND (end_date IS NULL OR end_date > SYSDATE)
             AND fm.MENU_ID = a.MENU_ID
             AND MENU_NAME LIKE 'Your Menu Name';

   CURSOR c_sub (p_menu_id NUMBER)
   IS
                 SELECT LEVEL padding,
                        menu_id,
                        (SELECT menu_name
                           FROM fnd_menus fm
                          WHERE fm.menu_id = fme.menu_id)
                           menu_name,
                        entry_sequence,
                        sub_menu_id,
                        (SELECT menu_name
                           FROM fnd_menus fm
                          WHERE fm.menu_id = fme.sub_menu_id)
                           submenu_name,
                        function_id
                   FROM fnd_menu_entries fme
             CONNECT BY menu_id = PRIOR sub_menu_id AND entry_sequence > -9e125
             START WITH menu_id = p_menu_id AND entry_sequence > -9e125
      ORDER SIBLINGS BY entry_sequence;
BEGIN
   FOR r_main IN c_main
   LOOP
      FOR r_sub IN c_sub (r_main.menu_id)
      LOOP
         IF r_sub.MENU_NAME LIKE '%' --Add criteria to print only specific menus e.g. '%XX%'
         THEN
            DBMS_OUTPUT.put_line (r_sub.MENU_NAME);
         END IF;

         IF r_sub.SUBMENU_NAME LIKE '%' --Add criteria to print only specific menus e.g. '%XX%'
         THEN
            DBMS_OUTPUT.put_line (r_sub.SUBMENU_NAME);
         END IF;
      END LOOP;
   END LOOP;
END;
Related posts: Upload your own post and refer it anywhere anytime:
Upload your own post

Leave a Reply