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.
Hi, For the table PAY_BALANCE_TYPES_VL B there is no joined condition mentioned .could you please check and provide correct join for this.
Hi Amarnath,
There is a join to the package TABLE (pay_balance_view_pkg.get_balance_dimensions(B.BALANCE_TYPE_ID, A.PAYROLL_REL_ACTION_ID, NULL, NULL)) BAL and that should solve the issue with the join.
Please run the query and check the result. Let me know in case of any issues.
Thanks,
FusionHCMKB Team