PAY Dutch AOW HR
Description
Categories: BI Publisher
Application: Payroll
Source: Dutch AOW HR Report
Short Name: PAYNLAOW_HR
DB package: PAY_AOW_HR_REPORT_PKG
Source: Dutch AOW HR Report
Short Name: PAYNLAOW_HR
DB package: PAY_AOW_HR_REPORT_PKG
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' ) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Employer |
|
LOV Oracle | |
Retire From |
|
Date | |
Retire To |
|
Date |