SQL Query to get Payroll Element Entries
SQL Query to get Payroll Element Entries

 906 total views

In this article we will look into how to get the Element Entries report.

Want to learn Fusion Technical tools? Checkout this article

The Main table which stores the element entry information is PAY_ELEMENT_ENTRIES_F and all the entry values are stored in PAY_ELEMENT_ENTRY_VALUES_F

SQL Query for Element Entries:

SELECT ppl.person_number
	,paf.assignment_number
	,pet.base_element_name
	,pee.effective_start_date
	,pee.effective_end_date
FROM per_all_people_f ppl
	,pay_element_types_f pet
	,pay_element_entries_f pee
	,pay_entry_usages peu
	,pay_rel_groups_dn pasg
	,per_all_assignments_f paf
WHERE pee.element_type_id = pet.element_type_id
	AND ppl.person_id = pee.person_id
	AND pet.base_element_name  = :{PARAMETER.P_ELEMENT_NAME}
	AND sysdate BETWEEN ppl.effective_start_date
		AND ppl.effective_end_date
	AND sysdate BETWEEN pet.effective_start_date
		AND pet.effective_end_date
	AND sysdate BETWEEN pee.effective_start_date
		AND pee.effective_end_date
			--AND paf.assignment_number = :{PARAMETER.P_ASSIGNMENT_NUMBER}
	AND pee.element_entry_id = peu.element_entry_id
	AND peu.payroll_assignment_id = pasg.relationship_group_id(+)
	AND pasg.assignment_id = paf.assignment_id(+)
	AND pee.effective_start_date BETWEEN paf.effective_start_date(+)
		AND paf.effective_end_date(+)
order by 1,2,3,4 desc

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 2 Comments

  1. Raghavendra

    Hi Sricharan,
    I’m working on extract pull element entries, one element entry has attached payroll relationship level but employee has 2 assignment and both has same PRN. My root UE is pay ext employee ue, and it returns 2 rows and next ue returns element entries for prn, so finally I get 2 rows in output. Actually I need report 1 row in out put, how can I resolve this, any thoughts on this ?

  2. Sricharan

    Hi Raghavendra,

    You might need to handle this at template level by taking the distinct payroll relationship number, element and effective start date and it would skip the duplicate entry that you are facing now. In Extracts with PER_EXT_PAY_EMPLOYEES_UE, we get two rows as the employee has two active assignments. We cannot skip this duplicate entry at extract level as per my idea.

    Thanks,
    Sricharan

Leave a Reply