select papf.person_number, ppnf.list_name employeename, pee.effective_start_date accru_startdt, peev.effective_end_date accru_end_dt, peev.screen_entry_value hrs_to_accrue, pet.base_element_name, pee.person_id, pee.effective_start_date work_date

 from pay_element_entries_f   pee,

    pay_element_entry_values_f peev,

    pay_input_values_f     piv,

    pay_element_types_f    pet,

  PER_PERSON_NAMES_F ppnf,

per_all_people_f papf

 where 1 = 1

  and peev.element_entry_id = pee.element_entry_id

  and piv.input_value_id = peev.input_value_id

  and peev.effective_start_date between piv.effective_start_date and

    piv.effective_end_date

  and pee.effective_start_date between peev.effective_start_date and

    peev.effective_end_date

  and :eff_date3 between peev.effective_start_date and peev.effective_end_date

  and pee.element_type_id = pet.element_type_id

  and :eff_date3 between pet.effective_start_date and pet.effective_end_date

  and :eff_date3 between ppnf.effective_start_date and ppnf.effective_end_date

  and piv.base_name = 'Hours'

  and peev.screen_entry_value is not null

 and pee.person_id = papf.person_id

 and pee.person_id = ppnf.person_id

  and ppnf.NAME_TYPE='GLOBAL'

 AND (:p_person_num is null or papf.person_number = :p_person_num)

  and pet.base_element_name in

    (select replace(pet.base_element_name, ' Earnings Results', '')

     from pay_balance_types_vl pbt,

        pay_balance_feeds_f pbf,

        pay_element_types_f pet

     where upper(pbt.balance_name) like 'ACCRUAL HOURS WORKED'

      and pbf.balance_type_id = pbt.balance_type_id

      and sysdate between pbf.effective_start_date and

        pbf.effective_end_date

      and pbf.element_type_id = pet.element_type_id)

order by papf.person_number, pee.effective_start_date

UNION

-- ABSENCES

select papf.person_number, ppnf.list_name employeename, p.start_date, p.end_date, abs(det.value), 'Absence', det.person_id, det.procd_date

         from anc_per_acrl_entry_dtls det,

PER_PERSON_NAMES_F ppnf,

per_all_people_f papf,

            (select ptp.start_date, ptp.end_date

             from pay_time_periods ptp

             where :eff_date3 between ptp.start_date and

                ptp.end_date

              and ptp.period_category = 'E') p

         where det.type = 'ABS'

 --         and det.person_id = 300000007840522

  and det.person_id = papf.person_id

and det.person_id = ppnf.person_id

AND (:p_person_num is null or papf.person_number = :p_person_num)

and ppnf.NAME_TYPE='GLOBAL'

          and det.voided_acrl = 'N'

          and det.procd_date between p.start_date and p.end_date

  and det.procd_date between ppnf.effective_start_date and ppnf.effective_end_date

          and det.pl_id in

            (select a.absence_plan_id

             from ANC_ABSENCE_PLANS_VL a

             where substr(a.name, 1, 9) in

                (select substr(base_element_name, 1, 9)

                 from pay_balance_types_vl pbt,

                    pay_balance_feeds_f pbf,

                    pay_element_types_f pet

                 where upper(pbt.balance_name) like

                    'ACCRUAL HOURS WORKED'

                  and pbf.balance_type_id = pbt.balance_type_id

                  and sysdate between pbf.effective_start_date and

                    pbf.effective_end_date

                  and pbf.element_type_id = pet.element_type_id

                  and pet.classification_id in

                    (select a.classification_id

                     from pay_ele_classifications a

                     where a.base_classification_name = 'Absences'

                      and a.legislation_code is null)))