FND Users

Description
Categories: Enginatics
Repository: Github
Similar to report FND Access Control, but also shows inactive / end dated user responsibilities while FND Access Control shows currently active assigned responsibilities only.
Same as Oracle's 'Active Users' report.
Run FND Users and other Oracle EBS reports with Blitz Report™ on our demo environment
select
fu.user_name,
fu.description,
papf.first_name,
papf.last_name,
fu.email_address,
papf.email_address hr_email_address, 
fu.start_date user_start_date,
fu.end_date user_end_date,
fu.last_logon_date,
(select count(*) from fnd_user_resp_groups furg where fu.user_id=furg.user_id) active_responsibilities,
xxen_util.user_name(fu.created_by) user_created_by,
xxen_util.client_time(fu.creation_date) user_creation_date,
xxen_util.user_name(fu.last_updated_by) user_last_updated_by,
xxen_util.client_time(fu.last_update_date) user_last_update_date,
fu.user_id
from
fnd_user fu,
(select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf
where
1=1 and
fu.employee_id=papf.person_id(+)
order by
case when nvl(fu.end_date,sysdate)>=trunc(sysdate) then 1 else 2 end,
fu.user_name
Parameter Name SQL text Validation
User Name
fu.user_name=:user_name
LOV
Has Access to Responsibility
fu.user_id in (
select
furg.user_id
from
fnd_responsibility_vl frv,
fnd_user_resp_groups furg
where
frv.responsibility_name=:responsibility and
frv.responsibility_id=furg.responsibility_id and
frv.application_id=furg.responsibility_application_id
)
LOV
Has Access to Application
fu.user_id in (
select
furg.user_id
from
fnd_application_vl fav,
fnd_user_resp_groups furg
where
fav.application_name=:application and
fav.application_id=furg.responsibility_application_id
)
LOV
Active only
sysdate between fu.start_date and nvl(fu.end_date,sysdate) and
exists (select null from fnd_user_resp_groups furg where fu.user_id=furg.user_id)
LOV