SELECT 'PERSON ID' person_id
, 'EMPLOYEE NAME' full_name
, 'DATE OF BIRTH' dob
, 'EMPLOYEE NUMBER' employee_number
, 'ORGANIZATION NAME' organization
, 'ACTUAL TERMINATION DATE' end_date
, 'MANAGER NAME' manager_name
, 'RETIRE DATE' aow_date
, 'RETIRE EXCEPTION FLAG' retire_flag
FROM dual
UNION ALL
SELECT DISTINCT
to_char(papf.person_id) person_id
, '"'
|| full_name
|| '"' full_name
, to_char (papf.date_of_birth, 'yyyy-mm-dd') dob
, papf.employee_number employee_number
, hou.name organization
, to_char (pay_aow_hr_report_pkg.get_contract_end_date (papf.person_id), 'yyyy-mm-dd') end_date
, pay_aow_hr_report_pkg.get_manager_name (papf.person_id) manager_name
, to_char (pay_aow_hr_report_pkg.get_aow_date (papf.person_id), 'yyyy-mm-dd') aow_date
, pay_aow_hr_report_pkg.get_retire_flag (papf.person_id) retire_flag
FROM per_all_people_f papf
, per_all_assignments_f paaf
, hr_organization_units hou
WHERE paaf.organization_id = :P_EMPLOYER
AND hou.organization_id = paaf.organization_id
AND papf.person_id = paaf.person_id
AND papf.effective_start_date =
(
SELECT max (papf2.effective_start_date)
FROM per_all_people_f papf2
WHERE papf2.person_id = papf.person_id
)
AND paaf.effective_start_date =
(
SELECT max (paaf2.effective_start_date)
FROM per_all_assignments_f paaf2
WHERE paaf2.assignment_id = paaf.assignment_id
)
AND (
(
pay_aow_hr_report_pkg.get_aow_date (papf.person_id) >= nvl(:p_start_date, to_date('01-01-0001', 'dd-mm-yyyy'))
AND pay_aow_hr_report_pkg.get_aow_date (papf.person_id) <= nvl(:p_end_date, trunc(add_months(trunc(sysdate, 'yyyy'), 12) -1))
)
OR pay_aow_hr_report_pkg.get_retire_flag (papf.person_id) = 'Y'
) |