Oracle Fusion Payroll Tables Complete Guide

1. Payroll Module Overview

Oracle Fusion Cloud Payroll uses the PAY_ prefix for its core tables. Unlike the Core HR tables (PER_) which are date-effective with _F suffixes, payroll tables use an action-based architecture: every payroll event (a payroll run, a QuickPay, a reversal, a balance adjustment) is recorded as a payroll action, and each person's slice of that action is an assignment action.

There are over 400 PAY_ tables in Oracle Fusion. This guide covers the 15 most important ones — the ones you'll query constantly for payroll reports, integrations, and troubleshooting.

ℹ️

Access note: In Oracle Fusion Cloud, you typically query payroll data through OTBI subject areas (like Payroll — Payroll Run Results Real Time) rather than direct SQL. Direct SQL access requires Oracle BI Publisher data models or HCM Extracts with the appropriate roles.

2. Core PAY_ Tables Reference

Here are the 15 most important payroll tables, grouped by function:

Table	Purpose	Key Columns
`PAY_PAYROLL_ACTIONS`	One row per payroll event (run, QuickPay, reversal, balance adj.)	PAYROLL_ACTION_ID, ACTION_TYPE, ACTION_STATUS, EFFECTIVE_DATE, DATE_EARNED, PAYROLL_ID
`PAY_ASSIGNMENT_ACTIONS`	One row per person per payroll action; links assignment to payroll event	ASSIGNMENT_ACTION_ID, PAYROLL_ACTION_ID, ASSIGNMENT_ID, ACTION_STATUS, CHUNK_NUMBER
`PAY_RUN_RESULTS`	Stores the result of each element processed during a payroll run	RUN_RESULT_ID, ASSIGNMENT_ACTION_ID, ELEMENT_TYPE_ID, STATUS, ENTRY_TYPE
`PAY_RUN_RESULT_VALUES`	Individual input/output values for each run result (the actual dollar amounts)	RUN_RESULT_ID, INPUT_VALUE_ID, RESULT_VALUE
`PAY_ELEMENT_ENTRIES_F`	Date-effective element assignments for each person (what they should be paid)	ELEMENT_ENTRY_ID, ASSIGNMENT_ID, ELEMENT_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, CREATOR_TYPE
`PAY_ELEMENT_ENTRY_VALUES_F`	Input values for each element entry (salary amount, hours, rate)	ELEMENT_ENTRY_VALUE_ID, ELEMENT_ENTRY_ID, INPUT_VALUE_ID, SCREEN_ENTRY_VALUE
`PAY_ELEMENT_TYPES_F`	Element definitions (Regular Salary, Overtime, Bonus, etc.)	ELEMENT_TYPE_ID, ELEMENT_NAME, CLASSIFICATION_ID, PROCESSING_TYPE, EFFECTIVE_START_DATE
`PAY_INPUT_VALUES_F`	Defines the inputs each element accepts (Pay Value, Hours, Rate)	INPUT_VALUE_ID, ELEMENT_TYPE_ID, NAME, UOM, EFFECTIVE_START_DATE
`PAY_PAYROLLS_F`	Payroll definitions (Weekly US Payroll, Monthly Salaried, etc.)	PAYROLL_ID, PAYROLL_NAME, PERIOD_TYPE, CUT_OFF_DATE_OFFSET, EFFECTIVE_START_DATE
`PAY_TIME_PERIODS`	Payroll periods (start/end dates for each pay cycle)	TIME_PERIOD_ID, PAYROLL_ID, START_DATE, END_DATE, PERIOD_NAME, PERIOD_NUM
`PAY_BALANCE_TYPES`	Balance definitions (Gross Earnings YTD, Federal Tax Withheld, etc.)	BALANCE_TYPE_ID, BALANCE_NAME, ASSIGNMENT_REMUNERATION_FLAG, CURRENCY_CODE
`PAY_BALANCE_DIMENSIONS`	Dimensions for balance reporting (YTD, QTD, MTD, Run)	BALANCE_DIMENSION_ID, DIMENSION_NAME, DATABASE_ITEM_SUFFIX, PERIOD_TYPE
`PAY_ASSIGNMENT_LATEST_BALANCES`	Cached balance values per assignment (fastest way to get balances)	ASSIGNMENT_ACTION_ID, DEFINED_BALANCE_ID, VALUE
`PAY_COSTS`	Cost distribution lines for each payroll run result	COST_ID, ASSIGNMENT_ACTION_ID, RUN_RESULT_ID, COST_VALUE, CREDIT_OR_DEBIT
`PAY_COST_ALLOCATIONS_F`	Date-effective GL cost allocation overrides at the assignment or element level	COST_ALLOCATION_ID, ASSIGNMENT_ID, ELEMENT_TYPE_ID, EFFECTIVE_START_DATE
`PAY_ELEMENT_CLASSIFICATIONS`	Element classification groups (Earnings, Deductions, Employer Charges, Taxes)	CLASSIFICATION_ID, CLASSIFICATION_NAME, LEGISLATION_CODE

3. Payroll Processing Flow

Understanding how Oracle processes a payroll run maps directly to how the tables are populated. Each step creates records in specific tables:

Submit Payroll RunCreates 1 row in PAY_PAYROLL_ACTIONS with ACTION_TYPE = 'R' (Run) and ACTION_STATUS = 'U' (Unprocessed).

Identify AssignmentsOracle creates 1 row per eligible employee in PAY_ASSIGNMENT_ACTIONS, linking each assignment to the payroll action.

Process ElementsFor each element entry in PAY_ELEMENT_ENTRIES_F, Oracle evaluates Fast Formulas and creates rows in PAY_RUN_RESULTS and PAY_RUN_RESULT_VALUES with the computed amounts.

Update BalancesOracle accumulates run results into balances, updating PAY_ASSIGNMENT_LATEST_BALANCES with YTD/QTD/MTD totals.

Cost DistributionIf costing is enabled, Oracle generates GL entries in PAY_COSTS, splitting earnings across cost centers per PAY_COST_ALLOCATIONS_F.

Mark CompleteACTION_STATUS in PAY_PAYROLL_ACTIONS and PAY_ASSIGNMENT_ACTIONS is updated to 'C' (Complete). Pre-payments and archiving follow.

4. Element Entries & Run Results

The most common payroll reporting join is element entries → run results → run result values. Here's how they relate:

PAY_ELEMENT_ENTRIES_F answers: "What should this person be paid?" — it holds the standing instruction
PAY_RUN_RESULTS answers: "What was actually processed in this run?" — one row per element per run
PAY_RUN_RESULT_VALUES answers: "What was the actual dollar amount?" — the RESULT_VALUE column holds the computed value

⚠️

ENTRY_TYPE matters: In PAY_RUN_RESULTS, filter on ENTRY_TYPE = 'E' (normal element entries) to exclude override entries ('D') and additional entries ('A') unless you specifically need them.

The Pay Value (the actual dollar output) is almost always the input value named 'Pay Value' in PAY_INPUT_VALUES_F. For most earnings elements, you join RRV.INPUT_VALUE_ID to the Pay Value input to get the gross amount.

5. Payroll Balances

Oracle Payroll has two ways to get balance values:

Route 1: PAY_ASSIGNMENT_LATEST_BALANCES (Fast, cached)

The PAY_ASSIGNMENT_LATEST_BALANCES table stores the most recent balance value for each assignment + defined balance combination. It's populated after every payroll run and is the fastest way to report current balances. Join it to PAY_DEFINED_BALANCES to decode what balance/dimension it represents.

Route 2: Summarize PAY_RUN_RESULT_VALUES (Accurate for historical)

For historical period analysis (e.g., "what was this person's gross earnings in Q2?"), sum RUN_RESULT_VALUES filtered by the relevant payroll actions and date range. This is slower but gives you point-in-time accuracy regardless of any retroactive adjustments.

💡

Tip: Always filter PAY_PAYROLL_ACTIONS on ACTION_STATUS = 'C' (Complete) and ACTION_TYPE IN ('R', 'Q', 'B') (Run, QuickPay, Balance Adjustment) to exclude incomplete or reversed runs from your totals.

6. Eight Production SQL Queries

All queries use the action-based architecture described above. Filter action_status = 'C' to include only completed runs.

Query 1: Payroll Register

Payroll Register — Earnings by Employee

SELECT pap.full_name,
       pa.assignment_number,
       pet.element_name,
       rrv.result_value     AS amount,
       ppa.effective_date   AS pay_date
FROM  pay_payroll_actions      ppa
JOIN  pay_assignment_actions  paa  ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  per_all_assignments_m   pa   ON pa.assignment_id       = paa.assignment_id
JOIN  per_all_people_f        pap  ON pap.person_id          = pa.person_id
JOIN  pay_run_results         rr   ON rr.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_result_values   rrv  ON rrv.run_result_id       = rr.run_result_id
JOIN  pay_element_types_f     pet  ON pet.element_type_id    = rr.element_type_id
JOIN  pay_input_values_f      piv  ON piv.input_value_id     = rrv.input_value_id
WHERE ppa.action_type IN ('R','Q') AND ppa.action_status = 'C'
  AND ppa.effective_date BETWEEN :start_date AND :end_date
  AND rr.entry_type = 'E' AND piv.name = 'Pay Value'
  AND pap.effective_start_date <= ppa.effective_date
  AND pap.effective_end_date   >= ppa.effective_date
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
  AND piv.effective_start_date <= ppa.effective_date
  AND piv.effective_end_date   >= ppa.effective_date
ORDER BY pap.full_name, pet.element_name;

Query 2: Earnings by Classification

Earnings vs Deductions vs Taxes Summary

SELECT pec.classification_name,
       pet.element_name,
       SUM(TO_NUMBER(rrv.result_value))   AS total_amount,
       COUNT(DISTINCT paa.assignment_id)  AS employee_count
FROM  pay_payroll_actions      ppa
JOIN  pay_assignment_actions  paa  ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  pay_run_results         rr   ON rr.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_result_values   rrv  ON rrv.run_result_id       = rr.run_result_id
JOIN  pay_element_types_f     pet  ON pet.element_type_id    = rr.element_type_id
JOIN  pay_input_values_f      piv  ON piv.input_value_id     = rrv.input_value_id
JOIN  pay_element_classifications pec ON pec.classification_id = pet.classification_id
WHERE ppa.action_type IN ('R','Q') AND ppa.action_status = 'C'
  AND ppa.effective_date BETWEEN :start_date AND :end_date
  AND piv.name = 'Pay Value' AND rr.entry_type = 'E'
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
  AND piv.effective_start_date <= ppa.effective_date
  AND piv.effective_end_date   >= ppa.effective_date
GROUP BY pec.classification_name, pet.element_name
ORDER BY pec.classification_name, total_amount DESC;

Query 3: Active Element Entries (Current Pay Setup)

Current Element Entries per Employee

SELECT pap.full_name, pa.assignment_number,
       pet.element_name, pec.classification_name,
       piv.name AS input_name, peev.screen_entry_value,
       pee.effective_start_date, pee.effective_end_date
FROM  pay_element_entries_f      pee
JOIN  pay_element_entry_values_f peev ON peev.element_entry_id  = pee.element_entry_id
JOIN  per_all_assignments_m      pa   ON pa.assignment_id       = pee.assignment_id
JOIN  per_all_people_f           pap  ON pap.person_id          = pa.person_id
JOIN  pay_element_types_f        pet  ON pet.element_type_id    = pee.element_type_id
JOIN  pay_input_values_f         piv  ON piv.input_value_id     = peev.input_value_id
JOIN  pay_element_classifications pec ON pec.classification_id  = pet.classification_id
WHERE SYSDATE BETWEEN pee.effective_start_date AND pee.effective_end_date
  AND SYSDATE BETWEEN peev.effective_start_date AND peev.effective_end_date
  AND SYSDATE BETWEEN pet.effective_start_date AND pet.effective_end_date
  AND SYSDATE BETWEEN piv.effective_start_date AND piv.effective_end_date
  AND SYSDATE BETWEEN pap.effective_start_date AND pap.effective_end_date
  AND pa.primary_flag = 'Y'
ORDER BY pap.full_name, pet.element_name;

Query 4: Payroll Period Summary

Completed Payroll Runs with Headcount and Total Gross

SELECT ppf.payroll_name, ptp.period_name,
       ptp.start_date, ptp.end_date,
       ppa.effective_date AS pay_date,
       COUNT(DISTINCT paa.assignment_id)   AS employee_count,
       SUM(TO_NUMBER(rrv.result_value))    AS total_gross
FROM  pay_payroll_actions      ppa
JOIN  pay_payrolls_f           ppf ON ppf.payroll_id         = ppa.payroll_id
JOIN  pay_time_periods         ptp ON ptp.time_period_id     = ppa.time_period_id
JOIN  pay_assignment_actions  paa  ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  pay_run_results         rr   ON rr.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_result_values   rrv  ON rrv.run_result_id       = rr.run_result_id
JOIN  pay_input_values_f      piv  ON piv.input_value_id     = rrv.input_value_id
JOIN  pay_element_types_f     pet  ON pet.element_type_id    = rr.element_type_id
JOIN  pay_element_classifications pec ON pec.classification_id = pet.classification_id
WHERE ppa.action_type = 'R' AND ppa.action_status = 'C'
  AND pec.classification_name = 'Earnings'
  AND piv.name = 'Pay Value' AND rr.entry_type = 'E'
  AND ppf.effective_start_date <= ppa.effective_date
  AND ppf.effective_end_date   >= ppa.effective_date
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
  AND piv.effective_start_date <= ppa.effective_date
  AND piv.effective_end_date   >= ppa.effective_date
GROUP BY ppf.payroll_name, ptp.period_name, ptp.start_date,
         ptp.end_date, ppa.effective_date
ORDER BY ptp.start_date DESC;

Query 5: Retro Pay Runs

Identify Retro Pay Actions and Amounts

SELECT pap.full_name, pa.assignment_number,
       ppa.effective_date  AS retro_pay_date,
       ppa.date_earned     AS original_period_date,
       pet.element_name,
       rrv.result_value    AS retro_amount
FROM  pay_payroll_actions      ppa
JOIN  pay_assignment_actions  paa ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  per_all_assignments_m   pa  ON pa.assignment_id       = paa.assignment_id
JOIN  per_all_people_f        pap ON pap.person_id          = pa.person_id
JOIN  pay_run_results         rr  ON rr.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_result_values   rrv ON rrv.run_result_id       = rr.run_result_id
JOIN  pay_element_types_f     pet ON pet.element_type_id    = rr.element_type_id
JOIN  pay_input_values_f      piv ON piv.input_value_id     = rrv.input_value_id
WHERE ppa.action_type = 'L'           -- 'L' = Retro Pay
  AND ppa.action_status = 'C'
  AND ppa.effective_date BETWEEN :start_date AND :end_date
  AND piv.name = 'Pay Value'
  AND pap.effective_start_date <= ppa.effective_date
  AND pap.effective_end_date   >= ppa.effective_date
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
  AND piv.effective_start_date <= ppa.effective_date
  AND piv.effective_end_date   >= ppa.effective_date
ORDER BY pap.full_name, ppa.effective_date;

Query 6: Reversals and Voids

Find Reversed Assignment Actions

-- Reversals create negative run results to back out a prior run
SELECT pap.full_name, pa.assignment_number,
       ppa.effective_date  AS reversal_date,
       pet.element_name,
       rrv.result_value    AS reversed_amount
FROM  pay_payroll_actions      ppa
JOIN  pay_assignment_actions  paa ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  per_all_assignments_m   pa  ON pa.assignment_id       = paa.assignment_id
JOIN  per_all_people_f        pap ON pap.person_id          = pa.person_id
JOIN  pay_run_results         rr  ON rr.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_result_values   rrv ON rrv.run_result_id       = rr.run_result_id
JOIN  pay_element_types_f     pet ON pet.element_type_id    = rr.element_type_id
JOIN  pay_input_values_f      piv ON piv.input_value_id     = rrv.input_value_id
WHERE ppa.action_type = 'V'           -- 'V' = Reversal
  AND ppa.action_status = 'C'
  AND ppa.effective_date BETWEEN :start_date AND :end_date
  AND piv.name = 'Pay Value'
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
  AND piv.effective_start_date <= ppa.effective_date
  AND piv.effective_end_date   >= ppa.effective_date
  AND pap.effective_start_date <= ppa.effective_date
  AND pap.effective_end_date   >= ppa.effective_date
ORDER BY ppa.effective_date DESC;

Query 7: Cost Allocation — GL Distribution

Payroll Cost Lines by Cost Center

-- Show how payroll costs are distributed to GL segments
SELECT pap.full_name,
       pa.assignment_number,
       pet.element_name,
       pec.classification_name,
       pc.cost_value,
       pc.credit_or_debit,
       /* cost_allocation_keyflex columns vary by chart of accounts */
       pc.segment1  AS company,
       pc.segment2  AS department,
       pc.segment3  AS account
FROM  pay_payroll_actions      ppa
JOIN  pay_assignment_actions  paa ON paa.payroll_action_id  = ppa.payroll_action_id
JOIN  per_all_assignments_m   pa  ON pa.assignment_id       = paa.assignment_id
JOIN  per_all_people_f        pap ON pap.person_id          = pa.person_id
JOIN  pay_costs               pc  ON pc.assignment_action_id = paa.assignment_action_id
JOIN  pay_run_results         rr  ON rr.run_result_id       = pc.run_result_id
JOIN  pay_element_types_f     pet ON pet.element_type_id    = rr.element_type_id
JOIN  pay_element_classifications pec ON pec.classification_id = pet.classification_id
WHERE ppa.action_type IN ('R','Q')
  AND ppa.action_status = 'C'
  AND ppa.effective_date BETWEEN :start_date AND :end_date
  AND pap.effective_start_date <= ppa.effective_date
  AND pap.effective_end_date   >= ppa.effective_date
  AND pet.effective_start_date <= ppa.effective_date
  AND pet.effective_end_date   >= ppa.effective_date
ORDER BY pap.full_name, pc.segment2, pet.element_name;

Query 8: Payroll Action Status Dashboard

All Payroll Actions — Status Overview for a Date Range

-- Quick audit of all payroll actions in a period: useful for month-end checks
SELECT ppf.payroll_name,
       ppa.action_type,
       CASE ppa.action_type
         WHEN 'R' THEN 'Payroll Run'
         WHEN 'Q' THEN 'QuickPay'
         WHEN 'V' THEN 'Reversal'
         WHEN 'B' THEN 'Balance Adjustment'
         WHEN 'L' THEN 'Retro Pay'
         WHEN 'P' THEN 'Pre-Payments'
         WHEN 'A' THEN 'Archive'
         ELSE ppa.action_type
       END                         AS action_description,
       ppa.action_status,
       ppa.effective_date,
       COUNT(paa.assignment_action_id) AS action_count
FROM  pay_payroll_actions      ppa
JOIN  pay_payrolls_f           ppf ON ppf.payroll_id         = ppa.payroll_id
LEFT JOIN pay_assignment_actions paa ON paa.payroll_action_id = ppa.payroll_action_id
WHERE ppa.effective_date BETWEEN :start_date AND :end_date
  AND ppf.effective_start_date <= ppa.effective_date
  AND ppf.effective_end_date   >= ppa.effective_date
GROUP BY ppf.payroll_name, ppa.action_type, ppa.action_status, ppa.effective_date
ORDER BY ppa.effective_date DESC, ppf.payroll_name;

7. Cost Allocation Tables

Payroll cost allocation in Oracle Fusion determines which GL cost centers and accounts absorb payroll expenses. The key tables are:

PAY_COSTS — One row per GL debit/credit line per run result. Columns include COST_VALUE, CREDIT_OR_DEBIT ('C' or 'D'), and up to 30 SEGMENT_n columns matching your chart of accounts.
PAY_COST_ALLOCATIONS_F — Date-effective overrides at the assignment or element level. When an employee's costing differs from the default, this table stores the override segments. Joins to PAY_COST_ALLOCATION_KEYFLEX for the GL string.
PAY_COST_ALLOCATION_KEYFLEX — Stores unique combinations of GL segments. Each row is a distinct cost center/account combination referenced by multiple cost allocation records.

⚠️

Segment naming varies: The SEGMENT_1 through SEGMENT_30 columns in PAY_COSTS map to your specific GL chart of accounts structure. Common mappings are Company (SEG1), Cost Center (SEG2), Account (SEG3), but this is implementation-specific. Check your chart of accounts setup to know which segment is which.

8. Reporting Tips & Gotchas

Always filter ACTION_STATUS

The most common payroll report mistake is forgetting to filter on ACTION_STATUS = 'C'. An in-progress run ('U' = Unprocessed) or a failed run ('E' = Error) will have partial run results that inflate your totals. Always filter on Complete status.

ACTION_TYPE Codes Cheat Sheet

Code	Action	Common Use
`R`	Payroll Run	Regular scheduled payroll processing
`Q`	QuickPay	Off-cycle single employee payment
`V`	Reversal	Back out a prior payroll run (creates negative entries)
`B`	Balance Adjustment	Correct YTD balances without re-running
`L`	Retro Pay	Retroactive pay corrections
`P`	Pre-Payments	Calculates payment method splits (direct deposit, check)
`A`	Archive	Stores formatted payslip data for employee self-service
`H`	Costing	GL cost distribution run

Date-Effective Join Pattern

The standard date-effective join for payroll reports — filter date-effective tables against ppa.effective_date (not SYSDATE), since you're reporting as of the payment date:

Date-Effective Join Template

-- Standard pattern: join date-effective tables against the payroll action's effective_date
AND pet.effective_start_date <= ppa.effective_date
AND pet.effective_end_date   >= ppa.effective_date
AND piv.effective_start_date <= ppa.effective_date
AND piv.effective_end_date   >= ppa.effective_date
-- For person/assignment tables:
AND pap.effective_start_date <= ppa.effective_date
AND pap.effective_end_date   >= ppa.effective_date

ENTRY_TYPE Values in PAY_RUN_RESULTS

The ENTRY_TYPE column in PAY_RUN_RESULTS tells you the source of the run result:

'E' — Normal element entry (use this in most reports)
'D' — Additional entry (one-time additions)
'A' — Accrual (absence accrual plans)
'P' — Pay value (deduction from earnings)

For a clean payroll register showing regular plus one-time payments, use ENTRY_TYPE IN ('E', 'D').

Performance Optimization

Payroll tables can be very large (millions of rows for large organizations). Key performance tips:

Always filter PAY_PAYROLL_ACTIONS on date range first — this is the most selective filter
Use EFFECTIVE_DATE range on the payroll action before joining to run results
In OTBI, use the Payroll — Payroll Run Results Real Time subject area rather than custom SQL when possible
For balance queries, prefer PAY_ASSIGNMENT_LATEST_BALANCES over summing run result values

🔗

Related guides: Fast Formula Examples covers payroll formulas that control element processing. Compensation Tables Guide covers salary management upstream of payroll.