Useful Users & Responsibilities Scripts in Oracle EBS

Below are some of the useful Oracle EBS queries related to users and responsibilities.

LIST OF ACTIVE USERS

select user_id,user_name,description,email_address,start_date,end_date
from fnd_user 
where (end_date is null or end_date>sysdate);

COUNT OF ACTIVE USERS

select count(*)
from fnd_user 
where (end_date is null or end_date>sysdate);

LIST OF ALL ACTIVE USERS ALONG WITH THEIR ACTIVE RESPONSIBILITIES

NOTE: GIVES ONLY DIRECT RESPONSIBILITIES

Select fu.user_name,fu.EMAIL_ADDRESS,fu.DESCRIPTION, fu.start_date USER_START_DATE,fu.END_dATE USER_END_dATE,frt.responsibility_name, furg.START_DATE RESP_START_DATE, furg.END_DATE RES_END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl frt
where furg.user_id = fu.user_id
and furg.responsibility_id = frt.responsibility_id
AND ((furg.END_DATE IS NULL or furg.END_DATE>sysdate) AND (fu.END_DATE IS NULL or fu.END_DATE>sysdate))
AND frt.language='US'
order by fu.user_name;

COUNT OF ALL ACTIVE USERS ALONG WITH THEIR ACTIVE RESPONSIBILITIES

NOTE: ONLY DIRECT RESPONSIBILITIES CONSIDERED

Select count(*)
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl frt
where furg.user_id = fu.user_id
and furg.responsibility_id = frt.responsibility_id
AND ((furg.END_DATE IS NULL or furg.END_DATE>sysdate) AND (fu.END_DATE IS NULL or fu.END_DATE>sysdate))
AND frt.language='US';

List of ALL active responsibilities with Application

SELECT frt.responsibility_id,frt.responsibility_name,fr.responsibility_key,
(SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = frt.application_id
and language='US')
application,
fr.start_date,fr.end_date
FROM apps.fnd_responsibility_tl frt,
apps.fnd_responsibility fr
where language='US'
and fr.responsibility_id=frt.responsibility_id
and fr.end_date is NULL
order by application;

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).

Useful Users & Responsibilities Scripts in Oracle EBS

Related posts: