Element entries query

 SELECT
    'BURSTING_KEY' BUR_KEY,
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date,
    SUM(ELE1)      ELE1,
    SUM(ELE2)      ELE2,
    SUM(ELE3)      ELE3,
    SUM(ELE4)      ELE4
FROM
    (
        SELECT
            papf.person_number,
            paaf.ass_attribute8                                                                       plan_id,
            paaf.assignment_id,
            paaf.person_id,
            to_char(ptp.start_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') start_date,
to_char(ptp.end_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') end_date,
            decode(ele.element_name, 'Ele1',(prrv.result_value), 0) ELE1,
            decode(ele.element_name, 'Ele2',(prrv.result_value), 0) ELE2,
            decode(ele.element_name, 'Ele3',(prrv.result_value), 0) ELE3,
            decode(ele.element_name, 'Ele4',(prrv.result_value), 0) ELE4
        FROM
            pay_element_types_vl     ele,
            pay_run_results          prr,
            pay_run_result_values    prrv,
            pay_input_values_vl      piv,
            pay_payroll_rel_actions  ppra,
            pay_payroll_assignments  ppasg,
            per_all_assignments_f    paaf,
            per_all_people_f         papf,
            pay_payroll_actions      ppa,
            pay_all_payrolls_f       pap,
            pay_time_periods         ptp
        WHERE
                1 = 1
            AND ele.element_type_id = prr.element_type_id
            AND prr.run_result_id = prrv.run_result_id
            AND prrv.input_value_id = piv.input_value_id
            AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
            AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id
            AND ppasg.hr_assignment_id = paaf.assignment_id
            AND paaf.person_id = papf.person_id
            AND ppra.payroll_action_id = ppa.payroll_action_id
            AND ppa.payroll_id = pap.payroll_id
            AND pap.payroll_id = ptp.payroll_id
            AND ((ppra.run_type_id IS NOT NULL AND ppa.action_type in ('R','Q','V')) 
OR ppa.action_type in ('B'))
AND ppra.RETRO_COMPONENT_ID is NULL
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
            AND paaf.ass_attribute_category = 'CA'
            AND paaf.ass_attribute8 IS NOT NULL
            AND ((ele.element_name IN ('Element1','Element2')
            AND piv.name = 'Pay Value')
OR (ele.element_name IN ('Element3','Element4')
            AND piv.name = 'Earnings'))
            AND ptp.period_category = 'E'
            AND :p_eff_date BETWEEN pap.effective_start_date AND pap.effective_end_date
            AND :p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND :p_eff_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND :p_eff_date BETWEEN ele.effective_start_date AND ele.effective_end_date
            AND :p_eff_date BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND :p_eff_date BETWEEN ptp.start_date AND ptp.end_date
            AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
            AND papf.person_number = nvl(:p_person_number, papf.person_number)
    )
GROUP BY
    'BURSTING_KEY',
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date