ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2F53869E053B46B63588994 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' ) Y PAY PAYNLAOW_HR US PAY Dutch AOW HR Application: Payroll Source: Dutch AOW HR Report Short Name: PAYNLAOW_HR DB package: PAY_AOW_HR_REPORT_PKG BI Publisher :p_employer :p_end_date :p_start_date 1 10 :p_employer LOV Oracle HR_NL_LEGAL_EMPLOYER Y select hou.organization_id id, hou.name value, null description from hr_organization_units hou where hou.business_group_id=fnd_global.per_business_group_id and exists (select 1 from pay_all_payrolls_f ppf where ppf.business_group_id=fnd_global.per_business_group_id and fnd_number.canonical_to_number(ppf.prl_information1)=hou.organization_id union select 1 from hr_all_organization_units hou1 ,hr_organization_information hoi1 where hou1.business_group_id=fnd_global.per_business_group_id and hoi1.org_information_context='CLASS' and hoi1.org_information1='HR_LEGAL_EMPLOYER' and hou1.organization_id=hoi1.organization_id and hou1.organization_id=hou.organization_id) order by value,description Y US Employer 2 20 :p_start_date Date US Retire From 3 30 :p_end_date Date US Retire To