Query to Retrieve Housing Allowance Deduction Element Details for Active Assignments

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  = 'Housing Allowance Deduction'
    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

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.