Oracle Ebuisness Suite -> List of users, who have one or more responsibility which has at least one Oracle form function associated with it.

Script to get List of users, who  have one or more responsibility which has at least one Oracle form function associated with it.


select fu.user_id,fu.user_name,count(1) ---to_char(fu.START_DATE,'DD-MON-RRRR'), to_char(fu.END_DATE,'DD-MON-RRRR')
from fnd_user fu
 ,fnd_user_resp_groups furg
where to_date(sysdate,'DD-MON-RRRR')
between to_date(fu.START_DATE,'DD-MON-RRRR')
and  NVL(to_date(fu.END_DATE,'DD-MON-RRRR'),to_date(sysdate,'DD-MON-RRRR'))
and fu.user_id = furg.user_id
and exists (
               SELECT rtl.responsibility_id
               FROM fnd_compiled_menu_functions fcmf
               , (SELECT *
                 FROM fnd_form_functions
                 WHERE type='FORM'
                 )ff
               , fnd_form_functions_tl ffl
               , fnd_responsibility fr
               , fnd_responsibility_tl rtl
               WHERE fcmf.function_id = ff.function_id
               AND fr.menu_id = fcmf.menu_id
               AND rtl.responsibility_id = fr.responsibility_id
               AND fr.responsibility_id=furg.responsibility_id
               AND fcmf.grant_flag = 'Y'
               AND ff.function_id = ffl.function_id
               AND SYSDATE BETWEEN fr.START_DATE AND NVL(fr.END_DATE,SYSDATE)
               group by rtl.responsibility_id,rtl.responsibility_name
             )
group by fu.user_id,fu.user_name
;