After a long time.....

I'm decided to blog .

Here's my original script to find who are all owning System administrator responsibility.

select DISTINCT(PAPF.PERSON_ID), fu.user_name, fu.end_date, frt.responsibility_name,
PAPF.FULL_NAME,
furga.start_date "RESPONSIBILITY_START_DATE",
furga.end_date "RESPONSIBILITY_END_DATE"
from FND_USER_RESP_GROUPS_ALL furga,
fnd_user fu,
fnd_responsibility_tl frt,
PER_ALL_PEOPLE_F PAPF
where
FU.EMPLOYEE_ID=PAPF.PERSON_ID
--fu.employee_id IN (SELECT DISTINCT(PERSON_ID) FROM PER_ALL_PEOPLE_F )
AND fu.user_id=furga.user_id
and frt.responsibility_id=furga.responsibility_id
and furga.responsibility_id=20420
AND FRT.LANGUAGE='US'
-- GROUP BY FU.EMPLOYEE_ID
ORDER BY 1 ;


I've added DISTINCT(PAPF.PERSON_ID) to mitigate the duplicate entries in the result. Since PER_ALL_PEOPLE_F  table sometimes contain duplicate entry for a given person_id, when joining with other tables, we'll get duplicate entries.