Count of EBS users having responsibilities linked to applications

The below script can be used to find the count of users linked to each EBS application. You can also filter and get information for the specific application.

select apl.application_name,
decode(ins.status,
'I',
'Installed',
'S',
'Shared',
'N',
'Not License') status,
count(distinct usr.user_id) Active_User_Count
from apps.fnd_product_installations ins,
apps.fnd_application_tl apl,
apps.fnd_user_resp_groups grp,
apps.fnd_user usr,
apps.fnd_responsibility_tl res,
apps.fnd_responsibility rss
where ins.application_id = apl.application_id
and apl.application_id = res.application_id
and grp.RESPONSIBILITY_ID=res.responsibility_id
and rss.responsibility_id=res.responsibility_id
and usr.user_id=grp.user_id
and ins.status in ('I','S')
--and apl.application_name = 'Cash Management'
and (usr.end_date is null or usr.end_date>sysdate)
and (grp.END_DATE is null or grp.END_DATE> sysdate)
and (rss.END_DATE is null or rss.END_DATE> sysdate)
group by apl.application_name,
decode(ins.status,
'I',
'Installed',
'S',
'Shared',
'N',
'Not License')
order by 2;

It will give output like below;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Count of EBS users having responsibilities linked to applications

Related posts: