Search Results pay_balance_set_members




Overview
Explore in AI Chat

Overview

PAY_BALANCE_SET_MEMBERS is a Payroll (PAY) module table owned by the HR schema that stores the individual members of a balance set. In Oracle EBS, a balance set (defined in PAY_BALANCE_SETS) is a logical grouping of balance dimensions and defined balances that can be processed, reported, or referenced together — for example, when configuring payroll calculations, running balance reports, or defining which balances an element feeds. PAY_BALANCE_SET_MEMBERS provides the line-level membership records that bind a given balance set to the specific balance dimensions and defined balances it contains.

The table is documented as VALID in ETRM for both 12.1.1 and 12.2.2, with a physical schema of four columns under the HR owner. From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. This suggests the entity is best modeled as a link (associative) table that resolves many-to-many relationships between balance sets, balance dimensions, and defined balances, rather than as a hub or satellite. Each row is a single membership assertion, so the natural grain is one member record per balance set.

Key Information Stored

The documented schema contains four columns:

  • BALANCE_SET_MEMBER_ID — the surrogate primary key, enforced by PAY_BALANCE_SET_MEMBERS_PK. It uniquely identifies each membership row and is the only documented unique index, making it the primary business-key candidate for the member record itself.
  • BALANCE_SET_ID — foreign key to PAY_BALANCE_SETS. Identifies the owning balance set to which this member belongs.
  • BALANCE_DIMENSION_ID — foreign key to PAY_BALANCE_DIMENSIONS. Identifies the balance dimension included as a member of the set.
  • DEFINED_BALANCE_ID — identifies the defined balance associated with the member. This column is present in the documented column list but is not listed among the modeled foreign keys, so its exact referential target should be confirmed against site-specific configuration.

The composite relationship between BALANCE_SET_ID, BALANCE_DIMENSION_ID, and DEFINED_BALANCE_ID effectively determines which balance is a member of which set, while BALANCE_SET_MEMBER_ID provides stable, surrogate row identity for references and updates.

Common Use Cases and Queries

Typical uses include reporting on balance set composition, validating payroll configuration, and supporting custom fast formulas or extracts that must resolve which dimensions and balances belong to a set.

Retrieve all members of a given balance set:

SELECT m.balance_set_member_id,
       m.balance_set_id,
       m.balance_dimension_id,
       m.defined_balance_id
FROM   hr.pay_balance_set_members m
WHERE  m.balance_set_id = :p_balance_set_id;

Join to balance sets and dimensions to produce a readable configuration listing:

SELECT s.balance_set_name,
       d.balance_dimension_name,
       m.defined_balance_id
FROM   hr.pay_balance_set_members m,
       hr.pay_balance_sets s,
       hr.pay_balance_dimensions d
WHERE  m.balance_set_id = s.balance_set_id
AND    m.balance_dimension_id = d.balance_dimension_id;

Lookup a specific membership row by primary key:

SELECT *
FROM   hr.pay_balance_set_members
WHERE  balance_set_member_id = :p_id;

These patterns support configuration auditing, migration verification between environments, and data extraction for downstream reporting or reconciliation.

Related Objects

  • PAY_BALANCE_SETS — parent table; joined on BALANCE_SET_ID to resolve the balance set header.
  • PAY_BALANCE_DIMENSIONS — referenced via BALANCE_DIMENSION_ID; supplies dimension definitions for each member.
  • PAY_BALANCE_SET_MEMBERS_PK — the primary-key index used for unique row access.
  • PAY_BALANCES / defined balance definitions — the likely logical target of DEFINED_BALANCE_ID, used to map members to concrete balances.
  • PAY_ELEMENT_TYPES and related payroll setup tables — consume balance sets when configuring element feeds, extending the operational dependency of this table.

Because PAY_BALANCE_SET_MEMBERS is a configuration-supporting table, changes ripple into payroll processing, balance reporting, and any interface that resolves balances through balance sets.