SELECT DISTINCT fu.user_name,
TO_CHAR (fu.creation_date, 'DD-MON-YYYY') creation_date,
papf.GLOBAL_NAME employee_name, papf.employee_number,
papf.email_address,
houa.name department,
houa.NAME, houa.TYPE, frt.responsibility_name,
fat.application_name,
papf1.GLOBAL_NAME manager_name,
papf1.employee_number manager_number,
papf1.email_address manager_email_address,
--frt.DESCRIPTION responsibility_Description,
TO_CHAR (furgd.start_date, 'DD-MON-YYYY') start_date,
TO_CHAR (furgd.end_date, 'DD-MON-YYYY') end_date
FROM apps.fnd_user fu,
fnd_user_resp_groups_direct furgd,
apps.fnd_responsibility_tl frt,
per_all_people_f papf,
per_all_assignments_f paaf,
hr_all_organization_units houa,
per_all_people_f papf1,
fnd_application_tl fat
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frt.responsibility_id
AND fu.employee_id = papf.person_id
AND papf.person_id = paaf.person_id
AND paaf.supervisor_id = papf1.person_id
--and frt.application_id=101
AND papf.current_employee_flag = 'Y'
AND papf.business_group_id = 81
AND papf1.current_employee_flag = 'Y'
and paaf.primary_flag='Y'
AND papf1.business_group_id = 81
and frt.APPLICATION_ID=fat.APPLICATION_ID
and fat.language='US'
AND paaf.organization_id = houa.organization_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
--AND furgd.end_date IS NULL
AND fu.end_date IS NULL
AND furgd.end_date IS NULL
--and papf.employee_number='1051'
/* and papf.PERSON_ID in (Select Distinct person_id from apps.XXCBO_STAFF_WITH_DEPARTMENT
where department in ('Information Technology Department'))*/
AND frt.LANGUAGE = 'US'
ORDER BY 4
TO_CHAR (fu.creation_date, 'DD-MON-YYYY') creation_date,
papf.GLOBAL_NAME employee_name, papf.employee_number,
papf.email_address,
houa.name department,
houa.NAME, houa.TYPE, frt.responsibility_name,
fat.application_name,
papf1.GLOBAL_NAME manager_name,
papf1.employee_number manager_number,
papf1.email_address manager_email_address,
--frt.DESCRIPTION responsibility_Description,
TO_CHAR (furgd.start_date, 'DD-MON-YYYY') start_date,
TO_CHAR (furgd.end_date, 'DD-MON-YYYY') end_date
FROM apps.fnd_user fu,
fnd_user_resp_groups_direct furgd,
apps.fnd_responsibility_tl frt,
per_all_people_f papf,
per_all_assignments_f paaf,
hr_all_organization_units houa,
per_all_people_f papf1,
fnd_application_tl fat
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frt.responsibility_id
AND fu.employee_id = papf.person_id
AND papf.person_id = paaf.person_id
AND paaf.supervisor_id = papf1.person_id
--and frt.application_id=101
AND papf.current_employee_flag = 'Y'
AND papf.business_group_id = 81
AND papf1.current_employee_flag = 'Y'
and paaf.primary_flag='Y'
AND papf1.business_group_id = 81
and frt.APPLICATION_ID=fat.APPLICATION_ID
and fat.language='US'
AND paaf.organization_id = houa.organization_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
--AND furgd.end_date IS NULL
AND fu.end_date IS NULL
AND furgd.end_date IS NULL
--and papf.employee_number='1051'
/* and papf.PERSON_ID in (Select Distinct person_id from apps.XXCBO_STAFF_WITH_DEPARTMENT
where department in ('Information Technology Department'))*/
AND frt.LANGUAGE = 'US'
ORDER BY 4