This SQL query is used to get user’s assigned responsibility name with start date and end end date of responsibility from user’s account, this query will extract all the assigned responsibility from the beginning till date.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SELECT distinct fuser.USER_NAME USER_NAME , fuser.user_id , fuser.creation_date , fuser.last_update_date , fuser.LAST_LOGON_DATE , fuser.START_DATE , fuser.END_DATE , per.FULL_NAME FULL_NAME , per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER , frt.RESPONSIBILITY_NAME RESPONSIBILITY , TO_CHAR(furg.START_DATE,'DD-MON-YYYY') resp_attched_date , TO_CHAR(furg.END_DATE,'DD-MON-YYYY') resp_remove_date FROM FND_USER fuser , PER_PEOPLE_F per , fnd_user_resp_groups_direct furg , FND_RESPONSIBILITY_TL frt WHERE fuser.EMPLOYEE_ID = per.PERSON_ID 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 frt.LANGUAGE = 'US' and fuser.user_name like 'SYS%' ORDER BY fuser.USER_NAME; |