How to get the Payroll Balance details in BI Report?
How to get the Payroll Balance details in BI Report?

 1,598 total views

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.

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply