User and responsibility Query

Some useful scripts related to Oracle Apps (11i,R12) are listed.

The list is updated regularly so keep coming back for updated version of this webpage.

Script to check user availability

set lines 132

col USER_NAME format a50

select USER_ID,USER_NAME,START_DATE,END_DATE from FND_USER WHERE USER_NAME = upper(‘&user_name’);

Script to find list of responsibility attached to a specific userid OR all userids

set lines 132
col user_name format a25
col responsibility format a40
col application format a40

SELECT UNIQUE fu.user_id,
fu.user_name user_name,
fr.responsibility_key responsibility,
fa.application_name application
FROM fnd_user fu,
fnd_user_resp_groups fg,
fnd_application_tl fa,
fnd_responsibility fr
WHERE fg.user_id(+) = fu.user_id
AND fg.responsibility_application_id = fa.application_id
AND fa.application_id = fr.application_id
AND fg.responsibility_id = fr.responsibility_id
AND fu.user_name like upper(‘%&user_name%’)
AND fg.end_date is Null
ORDER BY fu.user_id,
fa.application_name,
fr.responsibility_key;

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

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
• Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021