Responsibilities attached to a Menu

  SELECT DISTINCT frt.responsibility_name
                , fr.responsibility_key
                , fr.responsibility_id
                , fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_responsibility fr
       , applsys.fnd_responsibility_tl frt
       , applsys.wf_local_user_roles wlur
       , applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fr.application_id = frt.application_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = wlur.role_orig_system_id
     AND fm.menu_id = fmt.menu_id
     AND fr.menu_id = fmt.menu_id
     AND fr.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

-- without linking to responsibilities (since some menus are not linked to responsibilities,
-- but might be a parent menu containing a sub menu you want to delete)

  SELECT DISTINCT fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fm.menu_id = fmt.menu_id
     AND fmt.user_menu_name != :menu
     AND fmt.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;