How to get the Payroll Balance details in BI Report?

If the client has implemented Payroll, we have a common requirement to pull the balances in BI Report multiple times as part of third party integrations. Getting the Payroll Balance requires calling the balance package and providing the right inputs. Many of us get it wrong in writing the balances query and find it difficult to achieve it.

Here is the query which works and gets us the balance value for an employee and for a balance for the latest payroll run between date range.

SELECT TO_CHAR(BAL.BALANCE_VALUE)
 ,P.EFFECTIVE_DATE
FROM PAY_PAYROLL_REL_ACTIONS A
 ,PAY_PAYROLL_ACTIONS P
 ,pay_payroll_assignments C
 ,PAY_BALANCE_TYPES_VL B
 ,PAY_DIMENSION_USAGES_VL D
 ,TABLE (pay_balance_view_pkg.get_balance_dimensions(B.BALANCE_TYPE_ID
                                                   , A.PAYROLL_REL_ACTION_ID
                , NULL
         , NULL)) BAL
WHERE A.PAYROLL_RELATIONSHIP_ID = c.payroll_relationship_id
 --and c.hr_assignment_id = 300000244455190
 AND c.hr_assignment_id IN (SELECT DISTINCT assignment_id  
                              FROM per_all_assignments_m
                             WHERE paam.person_id = 300000010653849
                               AND assignment_type = 'E'
                               AND sysdate BETWEEN effective_start_date 
          AND effective_end_date
                               AND primary_flag = 'Y'
                               AND effective_latest_change = 'Y')
 AND A.RETRO_COMPONENT_ID IS NULL
 AND EXISTS (SELECT 1 FROM PAY_RUN_RESULTS PRR
  WHERE PRR.PAYROLL_REL_ACTION_ID = A.PAYROLL_REL_ACTION_ID)
 AND P.PAYROLL_ACTION_ID = A.PAYROLL_ACTION_ID
 AND P.ACTION_TYPE IN ('B','I','Q','R')
 AND P.EFFECTIVE_DATE = NVL((SELECT MAX(G.EFFECTIVE_DATE) 
                               FROM PAY_PAYROLL_ACTIONS G
                                   ,PAY_PAYROLL_REL_ACTIONS H
                                   ,PAY_RUN_RESULTS PR
                                   ,PAY_RUN_RESULT_VALUES PRV
                                   ,PAY_BALANCE_FEEDS_F PBF
WHERE G.PAYROLL_ACTION_ID = H.PAYROLL_ACTION_ID
 AND G.ACTION_TYPE IN ('B','I','Q','R')
 AND G.ACTION_STATUS IN ('C','P')
 AND H.ACTION_STATUS = 'C'
 AND A.PAYROLL_RELATIONSHIP_ID = H.PAYROLL_RELATIONSHIP_ID
 AND G.DATE_EARNED BETWEEN '2019-01-01' AND '2019-10-05'
 AND PBF.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
 AND PR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
 AND PR.PAYROLL_REL_ACTION_ID = H.PAYROLL_REL_ACTION_ID
 AND PBF.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID), TO_DATE('4712/12/31', 'YYYY/MM/DD'))
 AND b.balance_name = 'Pension Adjustment Hours'
 AND d.database_item_suffix = '_REL_YTD'
 --AND d.dimension_name = 'Relationship State Month to Date'
 AND d.balance_dimension_id = bal.balance_dimension_id
 AND BAL.BALANCE_VALUE <> 0
 AND NVL(d.legislation_code, 'US') = 'US'

Please feel free to modify it as per your requirement. Caveat here is if you are pulling multiple balances in BI Report, it will have performance issues.