• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:June 12, 2020
  • Reading time:2 mins read
You are currently viewing SQL Query to get Payroll Element Entries
SQL Query to get Payroll Element Entries

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

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
  • 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 ?

  • 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

  • In visible box by plugintheme