HCM Query to get Employee details without Payroll

 

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) 


Post a Comment

0 Comments