Query to find all the responsibilities assigned to a user/ all the users who have access to a responsibility,Responsibilities assigned to a specific user


The below query lists either all the responsibilities assigned to a particular user or all the users who have access to a particular responsibility.



/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : User and Responsibilities mapping
Description     : We can find the list of Responsibilities assigned to a specific user and
  list of users have a specific responsibility access.

It needs any one of two parameters (User_Name, Responsibility_Name):
1)If you pass User_Name, You will get all the Responsibilities assigned to that user.
2)If you pass Responsibility_Name, You will get all the Users Who has access to that responsibility.
****************************************************************** */

SELECT FU.USER_ID,FU.USER_NAME, FU.EMAIL_ADDRESS, FR.RESPONSIBILITY_NAME,FURG.start_date, FURG.END_DATE,fu.employee_id
from APPS.FND_USER_RESP_GROUPS_DIRECT FURG
, FND_USER FU
, FND_RESPONSIBILITY_TL FR
where 1=1
and FU.USER_NAME = nvl(:User_Name,FU.USER_NAME)
and FURG.USER_ID = FU.USER_ID
and FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
and FR.RESPONSIBILITY_NAME = nvl(:RESPONSIBILITY_NAME,FR.RESPONSIBILITY_NAME)
AND nvl(FURG.END_DATE, sysdate+1)>sysdate
and FR.LANGUAGE = 'US'
ORDER BY 4

Post a Comment