Every time a customer or client asks for a list of EBS Users and their responsibilities, the DBA team would generally execute the “Active Users” Concurrent Program from the System Administrator responsibility and would extract the output.
Now there is a easy way to extract the report without submitting the concurrent requests, we just have to execute the following SQL query in the Sqldeveloper/Toad and inport the output in an excel sheet and share it with the customers.
Use the following query to find all the active users in the Oracle E-Business Suite and all the responsibilities attached to the respective users.
select user_name, application_name, responsibility_name,
security_group_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
decode(
least(nvl(u.end_date, to_date(’01/01/4712′,’DD/MM/YYYY’)),
nvl(ur.end_date, to_date(’01/01/4712′,’DD/MM/YYYY’)),
nvl(r.end_date, to_date(’01/01/4712′,’DD/MM/YYYY’))),
to_date(’01/01/4712′,’DD/MM/YYYY’), ”,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date)))) end_date
from fnd_user u, fnd_user_resp_groups_all ur,
fnd_responsibility_vl r, fnd_application_vl a,
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and nvl(ur.end_date, sysdate + 1) > sysdate
and u.start_date <= sysdate
and nvl(u.end_date, sysdate + 1) > sysdate
and r.start_date <= sysdate
and nvl(r.end_date, sysdate + 1) > sysdate
and ur.security_group_id = s.security_group_id
and r.version in (‘4′,’W’,’M’)
order by user_name, application_name, responsibility_name, security_group_name;
Query provides the same output as the “Active Users” concurrent request in the EBS.
This is an easy and effective way to extract the same output, now the DBA’s dint have to login to the EBS and worry about having the System Administrator responsibility and submit the program and extract the report in the desired format. We just execute the above query.
