Query to get balance attached to payroll relationship in Fusion HCM

 select paa.assignment_number
,substr(ppa.effective_date,1,10) as effective_date
,ppa.action_type
,pprd.payroll_relationship_number
,pbt.balance_name
,pdu.dimension_name
,bal.balance_value
,papf.person_number
,ppn.first_name
,ppn.last_name
,hle.name as LEGAL_ENTITY_NAME
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
,per_all_people_f papf
,per_person_names_f ppn
,per_all_assignments_m paa
,pay_rel_groups_dn prel
,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
,hr_legal_entities hle
where
1 = 1
and pprd.legislative_data_group_id = ldg.legislative_data_group_id
and pprd.payroll_relationship_id = prel.payroll_relationship_id
and papf.person_id = ppn.person_id
and ppn.name_type = 'GLOBAL'
and papf.person_id = paa.person_id
and paa.assignment_status_type = 'ACTIVE'
and paa.assignment_type = 'E'
and paa.assignment_number = prel.assignment_number
and paa.effective_latest_change = 'Y'
and paa.legal_entity_id = hle.organization_id
and hle.classification_code = 'HCM_PSU'
and pra.payroll_relationship_id = pprd.payroll_relationship_id
and paa.assignment_status_type = 'ACTIVE'
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 ppa.action_type = 'I'
and pbt.BASE_BALANCE_NAME IN (:BALANCE_NAME)
and pac.classification_name = 'SEQUENCED'
and bal.balance_value > 0
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 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
and pdu.dimension_name in ('Relationship Month to Date','Relationship Period to Date','Relationship Quarter Year to Date','Relationship Year to Date','Relationship Run')
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and trunc(sysdate) between paa.effective_start_date and paa.effective_end_date
and trunc(sysdate) between pprd.start_date and pprd.end_date
and trunc(sysdate) between prel.start_date and prel.end_date
order by
papf.person_number
,ppa.effective_date
,ppa.action_type
,pprd.payroll_relationship_number
,pbt.balance_name
,paa.assignment_number