query gives all function names under a menu considering submenus's also‏

SELECT   DISTINCT

           fmep.menu_id,

           DECODE (

              fmep.function_id,

              NULL,

              DECODE (

                 fmec.function_id,

                 NULL,

                 DECODE (fmec1.function_id, NULL, 'No Func', fmec1.function_id),

                 fmec.function_id

              ),

              fmep.function_id

           )

              funcid,

           fff.user_function_name,

           fff.description

    FROM   fnd_form_functions_tl fff,

           fnd_menu_entries fmec1,

           fnd_menu_entries fmec,

           fnd_menu_entries fmep

   WHERE       fmep.menu_id = (SELECT   menu_id

                                 FROM   fnd_menus

                                WHERE   menu_name = 'ONT_SUPER_USER' --Change the menu according to your requirement

                                        AND ROWNUM = 1)

           AND fmep.sub_menu_id = fmec.menu_id(+)

           AND fmec.sub_menu_id = fmec1.menu_id(+)

           AND fff.function_id =

                 DECODE (

                    fmep.function_id,

                    NULL,

                    DECODE (

                       fmec.function_id,

                       NULL,

                       DECODE (fmec1.function_id,

                               NULL, -999,

                               fmec1.function_id),

                       fmec.function_id

                    ),

                    fmep.function_id

                 )

ORDER BY   DECODE (

              fmep.function_id,

              NULL,

              DECODE (

                 fmec.function_id,

                 NULL,

                 DECODE (fmec1.function_id,

                         NULL, 'No Func',

                         fmec1.function_id),

                 fmec.function_id

              ),

              fmep.function_id

           )

 

 

SELECT FM.MENU_NAME,

(SELECT A.MENU_NAME FROM FND_MENUS_VL A WHERE a.menu_id=FME.SUB_MENU_ID)"SUB_MENU_NAME",

(SELECT F.FUNCTION_NAME FROM FND_FORM_FUNCTIONS_VL F WHERE FME.FUNCTION_ID = F.FUNCTION_ID) "FUNCTION_NAME",

(SELECT F.USER_FUNCTION_NAME FROM FND_FORM_FUNCTIONS_VL F WHERE FME.FUNCTION_ID = F.FUNCTION_ID) "USER_FUNCTION_NAME",

--FME.FUNCTION_ID,

FME.PROMPT,

FME.GRANT_FLAG

FROM

FND_MENUS_VL FM,

FND_MENU_ENTRIES_VL FME

where FM.MENU_ID= FME.MENU_ID

AND FM.MENU_NAME ='PA_SUPERUSER_GUI_PB'