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.
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.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020