Users and responsibilities list in oracle ebs(Application)
--Users and responsibilities list in oracle ebs(Application).
--user with employee
SELECT fu.user_name,
ppf.last_name,
hr_general.Decode_organization(paaf.organization_id) orgnization,
fu.start_date,
fu.end_date,
frt.responsibility_name,
furg.start_date,
furg.end_date
FROM fnd_user fu,
per_people_f ppf,
per_all_assignments_f paaf,
fnd_user_resp_groups furg,
fnd_responsibility_tl frt
WHERE fu.employee_id = ppf.person_id
AND ppf.person_id = paaf.person_id
AND fu.user_id = furg.user_id
AND furg.responsibility_id = frt.responsibility_id
AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
UNION ALL
--user without employee
SELECT fu.user_name,
NULL,
NULL,
fu.start_date,
fu.end_date,
frt.responsibility_name,
furg.start_date,
furg.end_date
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_tl frt
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fu.employee_id IS NULL
ORDER BY user_name