Payroll Balance Query
select
/*+ ordered first_rows */
ppa.effective_date
,ppa.action_type
,pprd.payroll_relationship_number
,pbt.balance_name
,pdu.dimension_name
,pdu.database_item_suffix
,bal.balance_value
,bal.ctx_user_string
from per_legislative_data_groups_vl ldg
,pay_pay_relationships_dn pprd
,pay_payroll_rel_actions pra
,pay_payroll_actions ppa
,pay_action_classes pac
,pay_balance_types_vl pbt
,table(pay_balance_view_pkg.get_balance_dimensions
(p_balance_type_id => pbt.balance_type_id
,p_payroll_rel_action_id => pra.payroll_rel_action_id
,p_payroll_term_id => null
,p_payroll_assignment_id => null
)) bal
,pay_dimension_usages_vl pdu
where 1=1
ldg.legislative_data_group_id = :ldg_id
and pprd.legislative_data_group_id = ldg.legislative_data_group_id
and pprd.payroll_relationship_number = :pay_rel_num
and pra.payroll_relationship_id = pprd.payroll_relationship_id
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
)
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(:effective_date, 'YYYY/MM/DD')
and nvl(pbt.legislation_code, ldg.legislation_code) = ldg.legislation_code
and nvl(pbt.legislative_data_group_id, ldg.legislative_data_group_id)
= ldg.legislative_data_group_id
and pbt.balance_name = :balance_name
and pdu.balance_dimension_id = bal.balance_dimension_id
and nvl(pdu.legislation_code, ldg.legislation_code) = ldg.legislation_code
and nvl(pdu.legislative_data_group_id, ldg.legislative_data_group_id)
= ldg.legislative_data_group_id
order by
ppa.effective_date
,ppa.action_type
,pprd.payroll_relationship_number
,pbt.balance_name
,pdu.database_item_suffix
;
