HCM Query to get Employee details without Payroll
We can use below query to get employee details to whom payroll is not assigned
SELECT papf.person_number
,ppnf.full_name
,paam.assignment_number
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam
WHERE paam.person_id = ppnf.person_id
AND papf.person_id = ppnf.person_id
AND papf.person_id = paam.person_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND paam.effective_latest_change = 'Y'
AND paam.primary_flag = 'Y'
AND paam.assignment_type ='E'
AND paam.assignment_status_type = 'ACTIVE'
AND ppnf.name_type = 'GLOBAL'
AND NOT EXISTS (SELECT 1
FROM pay_rel_groups_dn prgd
,pay_pay_relationships_dn prrd
,pay_assigned_payrolls_dn papd
,pay_payroll_terms ppt
WHERE prgd.assignment_id = paam.assignment_id
AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
AND papd.payroll_term_id = ppt.payroll_term_id
AND ppt.payroll_relationship_id = prrd.payroll_relationship_id)
0 Comments