Oracle HCM Payroll: YTD Balance Query

 

✅ Oracle HCM Payroll: YTD Balance Query (SQL) – Complete Guide

If you are working on Oracle HCM Cloud Payroll, one of the most common requirements is to extract YTD (Year-To-Date) balances for employees.

This is needed for:

• Payroll reconciliation

• Payslip validation

• Tax reporting

• Audit requirements

• Integration with third-party payroll/finance systems

In this blog, you will learn how to fetch Payroll YTD balances using SQL from Oracle Fusion HCM tables, with practical examples.

---

📌 What is YTD Balance in Payroll?

YTD (Year-To-Date) is the total accumulated balance for an employee from the beginning of the payroll year up to the current payroll period.

Example:

• Jan payroll run: YTD = Jan amount

• Feb payroll run: YTD = Jan + Feb

• Mar payroll run: YTD = Jan + Feb + Mar

Oracle Payroll stores balances at different dimensions like:

• Relationship

• Assignment

• Person

• Tax unit

• Payroll statutory unit

---

🔥 Important Oracle Payroll Tables for Balance Queries

Oracle Fusion payroll balances are stored and derived using these major objects:

Package 

• PAY_BALANCE_VIEW_PKG.GET_BALANCE_DIMENSIONS

Core Balance Tables (Commonly Used)

• PAY_PAYROLL_ACTIONS

• PAY_PAYROLL_REL_ACTIONS

• PAY_PAYROLL_ASSIGNMENTS

• PAY_BALANCE_TYPES_VL

• PAY_DIMENSION_USAGES_VL

Person/Assignment Tables

• PER_ALL_PEOPLE_F

• PER_ALL_ASSIGNMENTS_M

• PER_PERIODS_OF_SERVICE

---

✅ Best Practice Approach for YTD Balance Query

There are 2 ways to extract balances:

Method 1 : Query from Balance Package

This is the cleanest method.

Method 2 (Recommended): Using HCM Extract

This is the fasted Method.

---

✅ 1) YTD Balance Query (Most Common)

This query returns YTD balances for a person for a given balance name.

⚠️ Note: Table names and columns may vary slightly by release and security model, but the logic remains the same.

select   BAL.BALANCE_VALUE,
ppra.PAYROLL_ACTION_ID,
ppa.effective_date,
bal.balance_dimension_id  ,
bal.BALANCE_TYPE_ID  
from 
PAY_PAYROLL_ACTIONS ppa
,PAY_PAYROLL_REL_ACTIONS ppra
,PAY_PAYROLL_ASSIGNMENTS pasg
,PAY_BALANCE_TYPES_VL B
,PAY_DIMENSION_USAGES_VL D
,TABLE (pay_balance_view_pkg.get_balance_dimensions(B.BALANCE_TYPE_ID
   , ppra.PAYROLL_REL_ACTION_ID
		, NULL
 , NULL)) BAL
where 1=1
AND Ppa.PAYROLL_ACTION_ID = ppra.PAYROLL_ACTION_ID
AND BAL.BALANCE_VALUE <> 0
and  ppa1.effective_date =:effective_date
and ppra.PAYROLL_RELATIONSHIP_ID = pasg.PAYROLL_RELATIONSHIP_ID 
			 AND EXISTS (SELECT 1 FROM PAY_RUN_RESULTS PRR
			  WHERE PRR.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID)
 AND Ppa.ACTION_TYPE IN ('Q','R')
and ppa.effective_date =:P_Effective_date
AND d.balance_dimension_id = bal.balance_dimension_id
AND b.balance_name = 'Gross'
AND d.database_item_suffix = '_REL_NOCB_YTD'
and d.LEGISLATION_CODE ='US'
and :Assignment_id = pasg.HR_ASSIGNMENT_ID	

Example Inputs

• Balance Name = Gross Pay

• Dimension = Relationship Year to Date

Note:- Recommendation method to extract the balance using HCM Extract only due to performance issues in BIP Report.

---

🧠 How to Find Correct Balance Name and Dimension?

Balance Name

Navigate:
My Client Groups → Payroll → Balance Definitions

Dimension Name

Navigate:
Balance Definitions → Balance Dimensions

Common dimensions:

• Relationship Year to Date

• Assignment Year to Date

• PSU Year to Date

• Tax Unit Year to Date

---

⚠️ Common Issues in YTD Balance SQL

1) Balance value is coming as NULL

Possible reasons:

• balance not fed by any element

• payroll not run

• wrong dimension selected

• wrong effective date filters

2) Duplicate rows

Reason:

• multiple effective rows in *_F tables
  Solution:

• use correct effective date filters

3) Wrong totals

Reason:

• using Assignment YTD instead of Relationship YTD

• multiple payroll relationships

---

🔗 Internal Links 

• HDL Worker Data Load Guide → (Worker HDL post link)

• HDL Absence Entry Template → (Absence HDL post link)

• HDL Seniority Hours Template → (Seniority Hours post link)

• HDL Template for Benefit Enrollment  → (HDL post link)

• eText BIP Report → (HDL post link)

---

✅ FAQ (Frequently Asked Questions)

Q1. Which table stores payroll balances in Oracle Fusion?

Payroll balances are stored in PAY_BALANCES and related balance definition tables like PAY_BALANCE_TYPES_F and PAY_BALANCE_DIMENSIONS_F.

Q2. What is the best dimension for YTD in payroll?

For most use cases, the best dimension is Relationship Year to Date.

Q3. Why do I see multiple YTD rows for one employee?

This happens when the employee has multiple payroll relationships or multiple effective dated rows. Use Payroll Relationship ID for accuracy.

Q4. Can I calculate YTD from run results instead of balance tables?

Yes. You can sum PAY_RUN_RESULT_VALUES by period, but it is slower and mainly used for audit and breakdown.

Q5. What is the difference between PTD and YTD?

PTD is Period-To-Date (only current payroll period), while YTD is Year-To-Date (sum from payroll year start).

Q6. How do I find the correct balance name?

Go to Balance Definitions in the Payroll module and copy the exact balance name.

Q7. Can YTD balances be extracted for terminated employees?

Yes, but ensure your effective date filters include termination date or use a parameterized date instead of SYSDATE.