Oracle EBS R12: Extract a Complete Menu, its Functions and Sub-Menus via SQL

The following query will extract all the functions, Sub-Menus & the functions & sub-menus within the above sub-menus on a single sheet.

Creating restricted responsibilities for users may require condensed Menus with fewer functions & sub-menus. Creating such menus can become very hectic by navigating in and out of the seeded menus to pick the right functions and sub-menus to include.

Very useful in scenarios mentioned above:

select   fmv.USER_MENU_NAME "MAIN MENU NAME"
        ,fmv.MENU_NAME "MAIN MENU CODE"
        ,fme.PROMPT "PROMT"
        ,fmv1.USER_MENU_NAME "SUBMENU NAME"
        ,fmv1.MENU_NAME "SUBMENU CODE"
        ,fff.USER_FUNCTION_NAME "FUNCTION NAME"
        ,fff.FUNCTION_NAME "FUNCTION CODE"
        ,fme.DESCRIPTION "DESCRIPTION"
        ,fme1.PROMPT "PROMT1"
        ,fmv2.USER_MENU_NAME "SUBMENU1 NAME"
        ,fmv2.MENU_NAME "SUBMENU1 CODE"
        ,fff1.USER_FUNCTION_NAME "FUNCTION1 NAME"
        ,fff1.FUNCTION_NAME "FUNCTION1 CODE"
        ,fme1.DESCRIPTION "DESCRIPTION1"
        ,fme2.PROMPT "PROMT2"
        ,fmv3.USER_MENU_NAME "SUBMENU2 NAME"
        ,fmv3.MENU_NAME "SUBMENU2 CODE"
        ,fff2.USER_FUNCTION_NAME "FUNCTION2 NAME"
        ,fff2.FUNCTION_NAME "FUNCTION2 CODE"
        ,fme2.DESCRIPTION "DESCRIPTION2"
        ,fme3.PROMPT "PROMT3"
        ,fmv4.USER_MENU_NAME "SUBMENU3 NAME"
        ,fmv4.MENU_NAME "SUBMENU3 CODE"
        ,fff3.USER_FUNCTION_NAME "FUNCTION3 NAME" 
        ,fff3.FUNCTION_NAME "FUNCTION3 CODE"
        ,fme3.DESCRIPTION "DESCRIPTION3"
        ,fme4.PROMPT "PROMT4"
        ,fmv5.USER_MENU_NAME "SUBMENU4 NAME"
        ,fmv5.MENU_NAME "SUBMENU4 CODE"
        ,fff4.USER_FUNCTION_NAME "FUNCTION4 NAME"
        ,fff4.FUNCTION_NAME "FUNCTION4 CODE"
        ,fme4.DESCRIPTION "DESCRIPTION4"
        from  FND_MENU_ENTRIES_VL  fme
        , FND_MENU_ENTRIES_VL  fme1
        , FND_MENU_ENTRIES_VL  fme2
        , FND_MENU_ENTRIES_VL  fme3
        , FND_MENU_ENTRIES_VL  fme4 
        , FND_MENU_ENTRIES_VL  fme5
        , FND_MENUS_VL fmv
        , FND_MENUS_VL fmv1
        , FND_MENUS_VL fmv2
        , FND_MENUS_VL fmv3
        , FND_MENUS_VL fmv4
        , FND_MENUS_VL fmv5
        , FND_FORM_FUNCTIONS_VL fff
        , FND_FORM_FUNCTIONS_VL fff1
        , FND_FORM_FUNCTIONS_VL fff2
        , FND_FORM_FUNCTIONS_VL fff3
        , FND_FORM_FUNCTIONS_VL fff4                   
where   fme.menu_id = 67605   -------- Insert Your Menu ID here --------
and fme.SUB_MENU_ID = fme1.MENU_ID  (+)
and fme.MENU_ID  = fmv.MENU_ID (+)
and fme.FUNCTION_ID = fff.FUNCTION_ID (+) 
and fme1.SUB_MENU_ID = fme2.MENU_ID (+)
and fme1.MENU_ID  = fmv1.MENU_ID (+)
and fme1.FUNCTION_ID = fff1.FUNCTION_ID (+)
and fme2.SUB_MENU_ID = fme3.MENU_ID (+)
and fme2.MENU_ID = fmv2.MENU_ID(+)
and fme2.FUNCTION_ID = fff2.FUNCTION_ID (+)
and fme3.SUB_MENU_ID = fme4.MENU_ID (+)
and fme3.MENU_ID = fmv3.MENU_ID (+)
and fme3.FUNCTION_ID = fff3.FUNCTION_ID (+)
and fme4.SUB_MENU_ID = fme5.MENU_ID (+)
and fme4.MENU_ID = fmv4.MENU_ID (+)
and fme4.FUNCTION_ID = fff4.FUNCTION_ID (+)
and fme5.MENU_ID  = fmv5.MENU_ID (+)

All you need is the MENU_ID, whose complete drilldown you require. This query digs down five levels into the MENU_ID you pass to it. If your menu is larger, feel free to extend it with the same methodology it is designed on.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s