select

    so_far.assignment_id,

    so_far.assignment_number,

    so_far.payroll_name,

    ptp.period_name,

    ptp.regular_payment_date,

    bv.defined_balance_id,

    hr_gbbal.calc_all_balances (ptp.regular_payment_date,so_far.assignment_id,bv.defined_balance_id) as "BalVal",

    papf_o.full_name    

from (

    select

        paaf.person_id,

        paaf.assignment_id,

        paaf.assignment_number,

        papf.payroll_name

    from per_all_assignments_f paaf

    inner join (

        select papf1.payroll_id, papf1.payroll_name

        from pay_all_payrolls_f papf1

        where sysdate between papf1.effective_start_date and papf1.effective_end_date

    ) papf on papf.payroll_id=paaf.payroll_id

    where paaf.payroll_id in (nvl(p_payroll_id_one,0),nvl(p_payroll_id_two,0),nvl(p_payroll_id_three,0))

    ---***********************TEST LINES TO RUN 2 EMPS ONLY*****************************

    --and paaf.assignment_number in ('33474','46754')

    --******************************END OF TEST LINES***********************************

    and not (

        (paaf.effective_end_date < p_tax_start)

        or (paaf.effective_start_date > p_tax_end)

    )

    group by paaf.person_id, paaf.assignment_id, paaf.assignment_number, papf.payroll_name

) so_far

inner join (

    select period_name, regular_payment_date

    from per_time_periods

    where payroll_id in (

        select papf1.payroll_id

        from pay_all_payrolls_f papf1

        where payroll_id in (nvl(p_payroll_id_one,0),nvl(p_payroll_id_two,0),nvl(p_payroll_id_three,0))

        and regular_payment_date between p_tax_start and p_tax_end      

    )

    group by period_name, regular_payment_date

) ptp on 1=1

left join (

    select

        pdb.defined_balance_id

    from pay_balance_types pbt

    inner join pay_defined_balances pdb on pdb.balance_type_id=pbt.balance_type_id

    inner join pay_balance_dimensions pbd on pbd.balance_dimension_id=pdb.balance_dimension_id

    where pbt.balance_name = p_balance_name

    AND PBD.DIMENSION_name = p_balance_dimension

) bv on 1=1

left join (

    select papf1.person_id, papf1.full_name

    from per_all_people_f papf1

    where papf1.effective_start_date = (

        select max(papf2.effective_start_date)

        from per_all_people_f papf2

        where papf1.person_id=papf2.person_id

    )

) papf_o on papf_o.person_id=so_far.person_id

order by so_far.assignment_id, ptp.regular_payment_date;