Payroll Balance Query

 select

pprd.payroll_relationship_number,

ppa.effective_date,

bal.BALANCE_NAME,

bal.Description,

bal.balance_value

from

fusion.pay_payroll_actions ppa

,fusion.pay_pay_relationships_dn pprd

,fusion.pay_payroll_rel_actions pra

,fusion.pay_action_classes pac

,table(pay_balance_view_pkg.get_balance_values

(p_payroll_rel_action_id => pra.payroll_rel_action_id

,p_base_balance_name1 => 'Net Pay'

,p_database_item_suffix1 => '_REL_ITD'

,p_base_balance_name2 => 'Net Pay'

,p_database_item_suffix2 => '_REL_YTD'

)) bal

where 1 =1

and pprd.payroll_relationship_number = :payroll_relationship_number

and pra.payroll_relationship_id = pprd.payroll_relationship_id

and ppa.payroll_action_id = pra.payroll_action_id

and pac.action_type = ppa.action_type

and pac.classification_name = 'SEQUENCED'

and ppa.effective_date = to_date(:P_DATE, 'YYYY/MM/DD')

and pra.retro_component_id is null

and exists

(select 1

from pay_run_results prr

where prr.payroll_rel_action_id = pra.payroll_rel_action_id

)