SELECT
ppf.person_id,
ppf.person_number,
paam.assignment_number,
to_char(ptp.start_date,’YYYY/MM/DD’) startdate,
to_char(ptp.end_date,’YYYY/MM/DD’) enddate,
ptp.period_name,
to_char(trunc(sysdate),’YYYY/MM/DD’) executiondatetime
FROM
pay_payroll_actions ppa,
pay_payroll_rel_actions pra,
pay_assigned_payrolls_dn apd,
per_all_assignments_m paam,
pay_time_periods ptp,
pay_rel_groups_dn prg,
per_all_people_f ppf,
pay_all_payrolls_f py,
pay_consolidation_sets pcs,
pay_payroll_terms pt
WHERE
1 = 1
AND pra.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN (
‘R’,
‘Q’
)
AND paam.assignment_id = prg.assignment_id
AND pcs.consolidation_set_id = nvl(ppa.consolidation_set_id, py.consolidation_set_id)
AND ptp.period_category = ‘E’
AND prg.payroll_relationship_id = pra.payroll_relationship_id
AND pt.payroll_term_id = apd.payroll_term_id
AND py.payroll_id = apd.payroll_id
AND pra.source_action_id IS NULL
AND pt.hr_term_id = prg.term_id
AND ppf.person_number = :P_PERSON
AND paam.assignment_type = ‘E’
AND paam.primary_flag = ‘Y’
AND ppf.person_id = paam.person_id
AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND trunc(sysdate) BETWEEN py.effective_start_date AND py.effective_end_date
AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND ptp.payroll_id = py.payroll_id
AND ppa.earn_time_period_id = ptp.time_period_id
AND ( ( :p_last_run_date IS NULL AND 1 = 1 )
OR ( :p_last_run_date IS NOT NULL AND trunc(ppa.creation_date) BETWEEN :p_last_run_date AND sysdate ) )
