Search for:
BIP – Query to find Workers with missing Payrolls
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) 
ORDER BY 1,2
Reports (BIP) – Query to get assigned payroll details

Use the below query to extract assigned payroll details from Fusion HCM:

SELECT papf.person_id
	  ,papf.person_number
	  ,ppnf.full_name
	  ,papd.payroll_id
	  ,papf_pay.payroll_name
	  ,prrd.payroll_relationship_number
	  ,to_char(prrd.start_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_start_date
	  ,to_char(prrd.end_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_end_date
	  ,to_char(papd.FSED,'DD-Mon-RRRR', 'nls_date_language=American') fsed
	  ,to_char(papd.FINC,'DD-Mon-RRRR', 'nls_date_language=American') finc
	  ,to_char(papd.LSPD,'DD-Mon-RRRR', 'nls_date_language=American') lspd
	  ,to_char(papd.LSED,'DD-Mon-RRRR', 'nls_date_language=American') lsed
	  ,paam.assignment_number
	  ,prgf.time_card_req
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND papf.person_number = '11111'
 ORDER BY 2,3