Oracle Apps: Query to Fetch Users using Responsibilities having a specific function

  select distinct fus.user_name, fr.responsibility_name
   from  FND_USER_RESP_GROUPS_DIRECT fur,
         FND_user fus,
         fnd_responsibility_vl fr
   where fur.RESPONSIBILITY_ID in  (SELECT fr.RESPONSIBILITY_ID
                                      FROM fnd_responsibility_vl fr,
                                           fnd_menus fm,
                                           fnd_compiled_menu_functions fmf,
                                           fnd_form_functions_vl ff
                                     WHERE fr.menu_id = fm.menu_id
                                       AND ff.function_id = fmf.function_id
                                       AND fmf.menu_id = fm.menu_id
                                       AND nvl(fr.end_date,trunc(sysdate)) >= trunc(sysdate)
                                       AND ff.user_function_name = :function_name)
    AND nvl(fur.end_date,trunc(sysdate)) >= trunc(sysdate)
    AND fus.USER_ID = fur.USER_ID
    AND fr.responsibility_id = fur.responsibility_id
    order by fus.user_name, fr.responsibility_name

Tags:

oracleapps