You are currently viewing Query to Fetch the Payroll Assigned to a Person in Oracle Fusion HCM

Query to Fetch the Payroll Assigned to a Person in Oracle Fusion HCM

Below query will help us fetch the payroll assigned to the employees in Oracle Fusion HCM

SQL
				SELECT papf.person_number,
       ppasg.hr_assignment_id,
       ppa.action_type,
       ppa.effective_date,
       papd.payroll_id,
       pap.payroll_name
FROM   pay_payroll_assignments ppasg,
       pay_pay_relationships_dn pprd,
       pay_payroll_rel_actions ppra,
       pay_payroll_actions ppa,
       pay_rel_groups_dn prgd,
       per_all_people_f papf,
       pay_assigned_payrolls_dn papd,
       pay_all_payrolls_f pap
WHERE  1 = 1
       AND pprd.payroll_relationship_id = ppasg.payroll_relationship_id
       AND ppra.payroll_relationship_id = pprd.payroll_relationship_id
       AND ppra.source_action_id IS NOT NULL
       AND ppra.action_status = 'C'
       AND ppra.retro_component_id IS NULL
       AND ppa.payroll_action_id = ppra.payroll_action_id
       AND ppa.action_status = 'C'
       AND prgd.assignment_id = ppasg.hr_assignment_id
       AND prgd.group_type = 'A'
       AND ppasg.person_id = papf.person_id
       AND papd.payroll_term_id = ppasg.payroll_term_id
       AND papd.payroll_id = pap.payroll_id
       AND TRUNC(SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date
	   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
ORDER BY papf.person_number