This query returns the END_DATE (formatted as YYYY-MM-DD) from PAY_TIME_PERIODS for the most recent payroll assignment. It joins payroll assignment, relationship, and payroll definition tables to determine the applicable payroll.
SELECT TO_CHAR(PTPS3.END_DATE,'YYYY-MM-DD')
FROM
pay_payroll_assignments ppas1,
pay_pay_relationships_dn pprdn2,
pay_assigned_payrolls_dn papdn3,
pay_all_payrolls_f papf4,
PAY_TIME_PERIODS PTPS3
WHERE
1=1
--AND ppas1.hr_assignment_id = :{PARAMETER.ASG_ID}
AND pprdn2.payroll_relationship_id(+) = ppas1.payroll_relationship_id
AND papdn3.payroll_term_id(+) = ppas1.payroll_term_id
AND papf4.payroll_id(+) = papdn3.payroll_id
AND PTPS3.PAYROLL_ID = papdn3.payroll_id
AND PTPS3.CUT_OFF_DATE IS NOT NULL
--AND TO_DATE(:{PARAMETER.P_EFF_DATE}, 'YYYY/MM/DD') BETWEEN PTPS3.START_DATE AND PTPS3.END_DATE
AND papdn3.START_DATE =(SELECT MAX(papd1.START_DATE)
FROM pay_assigned_payrolls_dn papd1 WHERE papd1.payroll_term_id = papdn3.payroll_term_id
)
AND ROWNUM=1 Output Results
Related Posts
Update Salary of an Employee
Posted on
Create Grade and Grade Step
Posted on