1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

I want the query for Menu and Sub menus in Sysadmin

Discussion in 'Oracle Apps Technical' started by gkpgidnan, Aug 27, 2009.

  1. gkpgidnan

    gkpgidnan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    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
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    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,
     
  3. gkpgidnan

    gkpgidnan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    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
     
  4. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Hi Srini,

    You can build a query like this :

    Code (SQL):
    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