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.
FROM PAY_PAYROLL_REL_ACTIONS A
, 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
WHERE paam.person_id = 300000010653849
AND assignment_type = 'E'
AND sysdate BETWEEN effective_start_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
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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM