Wednesday, 3 June 2015

Query to find the menus, functions for a responsibility in oracle apps

SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM apps.fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM apps.fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) = ('responsibility name')
                                   )
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence

No comments:

Post a Comment