Search Results pay_balance_sets




Overview
Explore in AI Chat

Overview

PAY_BALANCE_SETS is an Oracle E-Business Suite Payroll (PAY) table owned by the HR schema. It allows related balances to be grouped for reporting purposes, providing the container that defines a named collection of balance definitions used when producing statutory, legislative, or management reports. Rather than reporting on individual balance dimensions in isolation, a balance set aggregates the balances a payroll administrator or report writer wants to see together, such as gross earnings, taxable wages, and pretax deductions for a specific legislative context.

Under the heuristic Data Vault classification mined from its foreign key structure, this object is treated as standalone — that is, it is not a natural link or satellite in a Data Vault sense. In a modeled warehouse, it would most reasonably be represented as a reference or hub-style dimension holding the balance set definition, with membership detail resolved through its dependent member table rather than through columns on this entity itself. The row identity is established by a single-column surrogate primary key, while foreign key relationships are defined by other tables pointing at it.

Key Information Stored

Each row in PAY_BALANCE_SETS defines one balance set. The most significant columns documented in the ETRM 12.2.2 physical schema are:

  • BALANCE_SET_ID — the surrogate primary key and the value referenced by all dependent tables; also the only unique index (PAY_BALANCE_SETS_PK), making it the definitive business-key candidate for the row.
  • BALANCE_SET_NAME — the user-visible name by which the grouping is identified in the Payroll application and in reports.
  • BUSINESS_GROUP_ID — the business group that owns the definition, governing multi-organization visibility and security.
  • LEGISLATION_CODE — the legislation (country) under which the balance set is valid, since balance definitions and statutory reporting rules are legislation-specific.
  • COMMENTS — free-text description of the set's intended purpose.
  • ATTRIBUTE_1 through ATTRIBUTE_30 — the descriptive flexfield segment columns, providing extensibility for client-specific data without schema change.

The design pattern is typical of EBS descriptive flexfield storage: a compact set of core business columns followed by a large block of generic attribute columns. Only BALANCE_SET_ID carries a unique constraint; no alternate unique index on BALANCE_SET_NAME is documented, so uniqueness of the name is expected to be enforced in application logic.

Common Use Cases and Queries

Balance sets are consumed wherever payroll balances must be reported as a structured group. A common query retrieves the set definition for a given legislation:

  • Locate a set by name: SELECT balance_set_id, balance_set_name, legislation_code FROM pay_balance_sets WHERE balance_set_name LIKE :name AND business_group_id = :bg_id.
  • Enumerate members of a set: join PAY_BALANCE_SET_MEMBERS on BALANCE_SET_ID to list the individual balance definitions included in the grouping.
  • Identify sets used by payroll runs: join PAY_PAYROLL_ACTIONS on BALANCE_SET_ID to determine which sets are attached to specific payroll action parameters, useful when auditing statutory report output.
  • Legislation-scoped reporting: filter by LEGISLATION_CODE and BUSINESS_GROUP_ID to produce country-specific balance summaries.

Because the descriptive flexfield attributes are stored inline, client-specific reporting attributes can be surfaced directly from this table without additional joins.

Related Objects

  • PAY_BALANCE_SET_MEMBERS — the principal child table; its BALANCE_SET_ID column references PAY_BALANCE_SETS, defining which balances belong to the set.
  • PAY_PAYROLL_ACTIONS — references PAY_BALANCE_SETS via BALANCE_SET_ID, associating a balance set with payroll action processing parameters.
  • PAY_BALANCES — the underlying balance definitions whose identifiers appear as member values within a set.
  • PAY_BALANCE_TYPES — the balance type definitions grouped by the member rows of a set.
  • PAY_BALANCE_DIMENSIONS — the dimension definitions (assignment, organization, etc.) applied when a balance is evaluated for reporting.
  • HR_ALL_ORGANIZATION_UNITS — joined on BUSINESS_GROUP_ID to resolve the owning business group name.
  • FND_DESCRIPTIVE_FLEXS / FND_DF_SEGMENTS — metadata for the ATTRIBUTE_1–30 flexfield segments stored on this table.
  • Payroll reporting concurrent programs and Oracle Payroll balance reports — the primary functional consumers that resolve sets into report output.