Results for “pay_defined_balances_pk”

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

Overview

PAY_DEFINED_BALANCES is an Oracle E-Business Suite payroll table owned by the HR schema and delivered under the PAY (Payroll) product. It functions as the intersection entity between PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS, meaning each row defines a specific balance — a balance type combined with a particular dimension — that the payroll engine can accumulate, store, and report on. Because balances in Oracle Payroll are not a fixed set but are generated dynamically, this table is the registry that binds a named balance to the dimensions that qualify it, such as assignment, person, or run-level context. It is a core reference object in the payroll balance architecture and is referenced extensively by balance storage, backpay, and reporting tables.

Classification metadata mined from the foreign key structure suggests treating PAY_DEFINED_BALANCES as a hub in a Data Vault model, since it holds a stable surrogate-keyed identity (DEFINED_BALANCE_ID) referenced by many dependent tables. It also defines its own business key through the unique index PAY_DEFINED_BALANCES_UK2 (BALANCE_TYPE_ID, BALANCE_DIMENSION_ID, BUSINESS_GROUP_ID, LEGISLATION_CODE), reinforcing its role as a durable reference entity around which balance-related satellites and links would be constructed.

Key Information Stored

The table contains 17 documented columns. The most operationally significant are:

• DEFINED_BALANCE_ID — surrogate primary key (PAY_DEFINED_BALANCES_PK) uniquely identifying each defined balance.
• BALANCE_TYPE_ID — foreign key to PAY_BALANCE_TYPES; identifies the balance being defined.
• BALANCE_DIMENSION_ID — foreign key to PAY_BALANCE_DIMENSIONS; identifies the dimension applied to the balance.
• BUSINESS_GROUP_ID — the business group (legislative context) owning the definition.
• LEGISLATION_CODE — country legislation under which the balance is valid.
• LEGISLATION_SUBGROUP — further legislative grouping for the definition.
• FORCE_LATEST_BALANCE_FLAG — controls whether the latest balance value is forced for retention.
• GROSSUP_ALLOWED_FLAG — indicates whether grossup processing is permitted for the balance.
• SAVE_RUN_BALANCE — controls persistence of run-level balance results.
• RUN_BALANCE_STATUS — status indicator applied to run balances.

Audit and concurrency columns include LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER, with ZD_EDITION_NAME supporting editioning. The business-key candidates are captured by PAY_DEFINED_BALANCES_UK2, which combines balance type, dimension, business group, and legislation; the surrogate key DEFINED_BALANCE_ID remains the canonical identifier used in all downstream joins.

Common Use Cases and Queries

The most frequent use is resolving a balance to its type and dimension for reporting or configuration audits. A typical join pattern is:

• Query balances for a legislation: SELECT DEFINED_BALANCE_ID, BALANCE_TYPE_ID, BALANCE_DIMENSION_ID FROM PAY_DEFINED_BALANCES WHERE LEGISLATION_CODE = :code AND BUSINESS_GROUP_ID = :bg_id.
• Resolve balance meaning: join DEFINED_BALANCE_ID to PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS to obtain balance and dimension names.
• Trace retention behavior: filter on FORCE_LATEST_BALANCE_FLAG and SAVE_RUN_BALANCE to identify balances that persist across runs.
• Reporting on balances: join from PAY_LATEST_BALANCES or PAY_PERSON_LATEST_BALANCES back to this table via DEFINED_BALANCE_ID to label employee balance results.

Because it is a hub reference table, analysts generally treat it as read-only and join outward to fact-like balance tables rather than modifying it directly.

Related Objects

PAY_DEFINED_BALANCES sits at the center of a dense reference network:

• PAY_BALANCE_TYPES — parent of BALANCE_TYPE_ID.
• PAY_BALANCE_DIMENSIONS — parent of BALANCE_DIMENSION_ID.
• PAY_BALANCE_ATTRIBUTES — references DEFINED_BALANCE_ID for balance metadata.
• PAY_LATEST_BALANCES — holds latest aggregated balance values per DEFINED_BALANCE_ID.
• PAY_PERSON_LATEST_BALANCES and PAY_ASSIGNMENT_LATEST_BALANCES — person- and assignment-level latest balances linked via DEFINED_BALANCE_ID.
• PAY_RUN_BALANCES — run-level balance results keyed to the definition.
• PAY_BACKPAY_RULES — backpay processing rules that reference defined balances.
• PAY_MONITOR_BALANCE_RETRIEVALS and PAY_ORG_PAYMENT_METHODS_F — monitoring and payment method configurations referencing the definition.

These relationships confirm PAY_DEFINED_BALANCES as a foundational payroll reference table through which balance types, dimensions, and stored balance results are reconciled.