Results for “pay_run_balances”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PAY_RUN_BALANCES is a payroll transaction table owned by the HR schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. As documented in the ETRM repository, it stores run level balances — the resolved monetary or numeric values of defined balances accumulated and evaluated at the payroll run, assignment action, or assignment level during payroll processing. Each row captures a balance value at a specific effective date, tied to the payroll action that produced it and the assignment action that triggered it. Because payroll processing writes balance results to this table incrementally as each assignment action completes, PAY_RUN_BALANCES functions as the transactional ledger from which payroll run results, gross-to-net calculations, and year-end accumulators are derived.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification is link. This reflects the table's structure: it does not merely describe an entity or serve as a reference list, but associates multiple business keys — a defined balance, a payroll action, an assignment action, a time definition, and optionally a tax unit — into a single event record, with BALANCE_VALUE acting as the measurable payload.

Key Information Stored

The table contains 24 documented columns. The most significant are:

• RUN_BALANCE_ID — surrogate primary key, enforced by unique index PAY_RUN_BALANCES_PK. There is no documented composite business-key unique index; RUN_BALANCE_ID is the sole unique identifier.
• DEFINED_BALANCE_ID — foreign key to PAY_DEFINED_BALANCES, identifying which defined balance (for example, gross earnings or taxable wages) the row quantifies.
• BALANCE_VALUE — the accumulated numeric result for the balance at the point of processing.
• PAYROLL_ACTION_ID — foreign key to PAY_PAYROLL_ACTIONS, linking the value to a specific payroll run.
• ASSIGNMENT_ACTION_ID — foreign key to PAY_ASSIGNMENT_ACTIONS, linking the value to the processing of a specific assignment.
• ASSIGNMENT_ID — the assignment for which the balance was calculated.
• EFFECTIVE_DATE and BALANCE_DATE — the effective and balance dates governing which rows qualify for a given reporting period.
• ACTION_SEQUENCE — orders rows within an assignment action, supporting reprocessing and correction scenarios.
• TAX_UNIT_ID, JURISDICTION_CODE, and the component columns JURISDICTION_COMP1 through JURISDICTION_COMP3 — tax jurisdiction context for balances that must be evaluated per taxing authority.
• TIME_DEFINITION_ID — foreign key to PAY_TIME_DEFINITIONS, tying the balance to a defined period such as a pay period or quarter.
• SOURCE_ID, SOURCE_TEXT, SOURCE_NUMBER, and the secondary variants — provide traceability back to the originating source of the balance.

Common Use Cases and Queries

Typical reporting retrieves the balances associated with a payroll run. A basic pattern joins the table to PAY_PAYROLL_ACTIONS by PAYROLL_ACTION_ID to filter runs, and to PAY_DEFINED_BALANCES by DEFINED_BALANCE_ID to attach balance names:

• Payroll run reconciliation — sum BALANCE_VALUE grouped by DEFINED_BALANCE_ID for a given PAYROLL_ACTION_ID to verify run totals.
• Assignment-level auditing — filter by ASSIGNMENT_ACTION_ID to inspect the sequence of balance rows, using ACTION_SEQUENCE to reconstruct processing order.
• Retroactive adjustments — compare rows across EFFECTIVE_DATE and BALANCE_DATE values for the same ASSIGNMENT_ID and DEFINED_BALANCE_ID.
• Jurisdictional tax reporting — group balances by JURISDICTION_CODE and TAX_UNIT_ID for tax filing extracts.

Because the table is processed in high volume, queries should always be constrained by payroll action or assignment action and by date range rather than scanning by DEFINED_BALANCE_ID alone.

Related Objects

The documented foreign keys establish the primary relationships:

• PAY_DEFINED_BALANCES — joined on DEFINED_BALANCE_ID; defines the balance being stored.
• PAY_ASSIGNMENT_ACTIONS — joined on ASSIGNMENT_ACTION_ID; the assignment processing instance.
• PAY_PAYROLL_ACTIONS — joined on PAYROLL_ACTION_ID; the payroll run itself.
• PAY_TIME_DEFINITIONS — joined on TIME_DEFINITION_ID; qualifies the dimensional period of the balance.
• PAY_BALANCE_TYPES and PAY_BALANCE_FEEDS — reference the balance framework that governs how run balances are defined and fed.
• PAY_RUN_RESULTS — functionally complementary, holding individual result values from which run-level balances are derived.

These joins support both ad hoc SQL and the seeded payroll balance reports delivered with the Payroll module.