PAY Dutch AOW HR

Description
Categories: BI Publisher, Human Resources
Application: Payroll
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
Retire To
 
Date
Retire From
 
Date
Employer
 
LOV Oracle