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;
Latest posts by Brijesh Gogia (see all)
- Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
- Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
- Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024