To Get the All Menu Functions Under a Responsibility

SELECT DISTINCT fme.entry_sequence

               ,fme.menu_id

               ,(SELECT 'Y'

                   FROM fnd_responsibility_vl frv1

                       ,fnd_responsibility_tl frt

                  WHERE frv1.menu_id = fme.menu_id

                    AND frt.application_id = frv1.application_id

                    AND frt.responsibility_id = frv1.responsibility_id

                    AND frt.language = userenv('LANG')

                    AND frt.responsibility_name = '&RESP_NAME') "MAIN_FLAG"

               ,(SELECT fmt.user_menu_name

                   FROM fnd_menus_tl fmt

                  WHERE fmt.menu_id = fme.menu_id

                    AND fmt.language = userenv('LANG')) "MENU"

               ,fme.sub_menu_id

               ,(SELECT fmt.user_menu_name

                   FROM fnd_menus_tl fmt

                  WHERE fmt.menu_id = fme.sub_menu_id

                    AND fmt.language = userenv('LANG')) "SUB_MENU"

               ,fme.function_id

               ,(SELECT fff.user_function_name

                   FROM fnd_form_functions_tl fff

                  WHERE fff.function_id = fme.function_id

                    AND fff.language = userenv('LANG')) "FUNCTION"

               ,fme.grant_flag

FROM fnd_menu_entries fme

CONNECT BY fme.menu_id = PRIOR fme.sub_menu_id

START WITH EXISTS (SELECT 1

                                                               FROM fnd_responsibility_tl fr

                                                                           ,fnd_responsibility_vl frv

                                                              WHERE fr.responsibility_name = '&RESP_NAME'

                                                                        AND frv.responsibility_id = fr.responsibility_id

                                                                        AND frv.application_id = fr.application_id

                                                                        AND frv.menu_id = fme.menu_id)

ORDER BY fme.menu_id

         ,fme.entry_sequence