I want the query for Menu and Sub menus in Sysadmin

[gkpgidnan]

Hi Every body,

i need the query which is relted to menus.

my requirement is like, menu is having sub menus.
in that sub menus are also having submens. SO i need the query to get all menus under sub menus.



Thanks,
GKP

[apps_expert]

The table FND_MENUS contains the menu names and menu id. The table FND_MENU_ENTRIES contains the menu id and it's entries inclusing the sub-menu ids. SO you can use these two to write your query,

[gkpgidnan]

HI,

Thanks for your reply..

I know those two tables. i tried to join with that...
Even though i am not able to get it.

My requirement like

Step1 : Menu--> Submenus1---
|
These submenus are also havig some other submenus..

Like chain....

Thanks,
Srini

[M_Anas_O]

Hi Srini,

You can build a query like this :

SELECT  FMEV.ENTRY_SEQUENCE,
       FMEV.PROMPT,
       FMEV.DESCRIPTION,
       SUB_MENU_FMEV.USER_MENU_NAME SUB_MENU_NAME,
       SUB_MENU_FMEV.DESCRIPTION SUB_MENU_DESCRIPTION,
        FFFT.USER_FUNCTION_NAME,
        FMEV.GRANT_FLAG
        --, FMEV.* 
 FROM apps.FND_MENU_ENTRIES_VL FMEV,
      apps.FND_MENUS_TL SUB_MENU_FMEV,
      APPS.FND_FORM_FUNCTIONS_TL FFFT,
      apps.FND_RESPONSIBILITY_VL FRV
 WHERE FRV.MENU_ID = FMEV.menu_id
 AND   SUB_MENU_FMEV.MENU_ID(+) = FMEV.SUB_menu_id
 AND   SUB_MENU_FMEV.LANGUAGE(+) = 'US'
 AND   FFFT.FUNCTION_ID(+) = FMEV.FUNCTION_ID
 AND   FFFT.LANGUAGE(+) = 'US'
 AND   FRV.responsibility_name = 'XXX Customer Service'
 ORDER BY FMEV.entry_sequence;

This query gives you one level of sub menus for any responsibility specified. You can build in a similar way for as many levels you want for submenu.
If you directly want to query for a menu, remove FND_RESPONSIBILITY_VL and its selects and joins.

Regards,
Mohammed