Query to fetch Payroll Balance details in Oracle HCM - Cloud

The below query can be used to fetch a Payroll Balance(SIT Withheld) on the Separate Run run type(Relationship State Run dimension). This Separate Run run type will generally be used for Bonus elements.

select papf.person_number,pprd.payroll_relationship_number,
ppnf.full_name,BASE_RUN_TYPE_NAME,RUN_METHOD,SHORTNAME,
to_char(ppa.effective_date,'rrrr/mm/dd')  process_date,
to_char(ppa.date_earned,'rrrr/mm/dd') date_earned,
bal.balance_value "REL ASG RUN BALANCE",
bal.ctx_user_string "Reference"
from
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
PER_PERIODS_OF_SERVICE ppos,
pay_payroll_assignments ppasg,
PAY_ASSIGNED_PAYROLLS_DN papd,
pay_all_payrolls_f pap,
pay_pay_relationships_dn pprd,
pay_payroll_actions ppa,
pay_payroll_rel_actions ppra,
pay_run_types_f prt,
pay_dimension_usages_vl pdu,
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 => ppra.payroll_rel_action_id
,p_payroll_term_id => null
,p_payroll_assignment_id => null
)) bal
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and papf.person_id = ppos.person_id
and paaf.period_of_service_id = ppos.period_of_service_id
and paaf.assignment_id = ppasg.hr_assignment_id
and ppasg.payroll_term_id = papd.payroll_term_id
and papd.payroll_id = pap.payroll_id
and ppasg.payroll_relationship_id = pprd.payroll_relationship_id
and pprd.payroll_relationship_id = ppra.payroll_relationship_id
and ppra.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = pap.payroll_id
and ppra.run_type_id = prt.run_type_id
and paaf.PRIMARY_FLAG ='Y'
and paaf.ASSIGNMENT_TYPE = 'E'
AND PPOS.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS_IN WHERE PPOS_IN.person_id = PAPF.person_id)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between prt.effective_start_date and prt.effective_end_date
and trunc(sysdate) between ppasg.start_date and ppasg.end_date
and trunc(sysdate) between papd.start_date and papd.end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and ppnf.name_type='GLOBAL'--'US'
and paaf.assignment_type IN ('E','C','P','N')
and ppa.action_type in ('R','Q')
and ppra.run_type_id is not null
and ppa.effective_date = to_date('30-04-2020','dd-mm-rrrr')
and prt.BASE_RUN_TYPE_NAME = 'Separate Payment'
and pdu.balance_dimension_id = bal.balance_dimension_id
and pbt.balance_name='SIT Withheld'
and pdu.dimension_name = 'Relationship State Run'
and bal.balance_value <> 0
and papf.person_number in ('12345')