List of users with responsibilities
SELECT d.employee_number EMPLOYEE_ID,d.full_name EMPLOYEE_NAME,a.USER_NAME USERID,e.name POSITION,frv.responsibility_name RESPONSIBILITIES_ASSIGNED,
decode(a.END_DATE,NULL,’ACTIVE’,’INACTIVE’) Status,a.start_date,a.end_date,a.last_logon_date,d.internal_location
FROM apps.fnd_responsibility_vl frv,apps.FND_USER a,apps.FND_USER_RESP_GROUPS_all b,apps. per_all_people_f d,apps.FND_RESPONSIBILITY_TL res,PER_JOBS_VL e,
per_all_assignments_f f
WHERE
d.person_id=f.person_id and
b.responsibility_id = res.responsibility_id
and a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID and a.employee_id=d.person_id
and sysdate between d.effective_start_date and d.effective_end_Date
and sysdate between nvl(frv.start_date,sysdate) and nvl(frv.end_date,sysdate)
AND f.job_id = e.job_id
group by d.employee_number,a.USER_NAME,frv.responsibility_name, d.full_name,b.START_DATE,b.end_date,decode(a.END_DATE,NULL,’ACTIVE’,’INACTIVE’),
a.start_date,a.end_date,a.last_logon_date,e.name,d.internal_location
ORDER BY d.full_name
DBA 