Skip to content

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;
Brijesh Gogia
Leave a Reply