Monday 16 May 2016

Query to find All the responsibility attached with User and their end_date



SELECT FUSER.USER_NAME USER_NAME,
       FUSER.START_DATE,
       FUSER.END_DATE,
       FUSER.EMAIL_ADDRESS,
       fuser.description,
       per.FULL_NAME FULL_NAME,
       per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
       frt.RESPONSIBILITY_NAME RESPONSIBILITY
  FROM FND_USER fuser,
       PER_PEOPLE_F per,
       FND_USER_RESP_GROUPS furg,
       FND_RESPONSIBILITY_TL FRT
 WHERE     FUSER.EMPLOYEE_ID = PER.PERSON_ID
       AND (   PER.EFFECTIVE_END_DATE > SYSDATE
            OR PER.EFFECTIVE_END_DATE IS NULL)
       AND fuser.USER_ID = furg.USER_ID
       AND (TO_CHAR (fuser.END_DATE) IS NULL OR fuser.END_DATE > SYSDATE)
       AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
       AND (TO_CHAR (furg.END_DATE) IS NULL OR furg.END_DATE > SYSDATE)
       AND FRT.LANGUAGE = 'US'
       AND fuser.USER_NAME = :P_User_Name
;

No comments:

Post a Comment