The following query will list Menus, Responsibilities and Users for a specific Function:
SELECT fffv.user_function_name , fmv.menu_name , frv.responsibility_name , user_name FROM fnd_form_functions_vl fffv , fnd_menu_entries_vl fmev , fnd_menus_vl fmv , fnd_responsibility_vl frv , fnd_user fu , fnd_user_resp_groups_direct furgd WHERE fffv.function_name = 'SYSTEM_ADMINISTRATOR' AND fffv.function_id = fmev.function_id AND fmv.menu_id = fmev.menu_id AND fmv.menu_id = frv.menu_id AND TRUNC(SYSDATE) BETWEEN TRUNC(fu.start_date) AND NVL(TRUNC(fu.end_date),SYSDATE + 1) AND fu.user_id = furgd.user_id AND TRUNC(SYSDATE) BETWEEN TRUNC(furgd.start_date) AND NVL(TRUNC(furgd.end_date),SYSDATE + 1) AND frv.responsibility_id = furgd.responsibility_id AND TRUNC(SYSDATE) BETWEEN TRUNC(frv.start_date) AND NVL(TRUNC(frv.end_date),SYSDATE + 1) ;
Advertisements
