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