Friday 31 August 2012

Oracle Query to get Oracle Menus & Functions along with Sub Menu's and Their functions

SELECT LPAD(' ', 6*(LEVEL-1))
  || menu_entry.entry_sequence sequence ,
  LPAD(' ', 6*(LEVEL-1))
  || menu.user_menu_name SubMenu_Description ,
  LPAD(' ', 6*(LEVEL-1))
  || func.user_function_name Function_Description ,
  menu.menu_id ,
  func.function_id ,
  menu_entry.grant_flag Grant_Flag ,
  DECODE( menu_entry.sub_menu_id , NULL, 'FUNCTION' , DECODE( menu_entry.function_id , NULL, 'SUBMENU' , 'BOTH') ) Type
FROM fnd_menu_entries menu_entry ,
  fnd_menus_tl menu ,
  fnd_form_functions_tl func
WHERE menu_entry.sub_menu_id    = menu.menu_id(+)
AND menu_entry.function_id      = func.function_id(+)
AND grant_flag                  = 'Y'
  START WITH menu_entry.menu_id =
  (SELECT menu_id
  FROM fnd_menus_tl menu2
  WHERE menu2.user_menu_name = 'XXX_OM_USER'
  )
  CONNECT BY MENU_ENTRY.MENU_ID = PRIOR MENU_ENTRY.SUB_MENU_ID
ORDER SIBLINGS BY menu_entry.entry_sequence;

No comments:

Post a Comment