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;

[Post Views: 4019]

• About
• Latest Posts

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Latest posts by Brijesh Gogia (see all)

• Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
• Physical and Logical Block Corruption in Oracle Database - March 10, 2018
• Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018