Search Results pay_defined_balances

Overview

The PAY_DEFINED_BALANCES table is a core configuration table within the Oracle E-Business Suite Payroll module (PAY). It serves as the critical intersection or mapping table between balance types (PAY_BALANCE_TYPES) and balance dimensions (PAY_BALANCE_DIMENSIONS). A defined balance is the fundamental unit for tracking and storing payroll amounts, representing a specific monetary value (like Gross Earnings) measured over a specific period or context (like Assignment Lifetime to Date). This table creates the valid combinations of these two components, thereby defining every unique balance that can be calculated and stored by the payroll engine in releases 12.1.1 and 12.2.2.

Key Information Stored

The table's primary purpose is to store the relationships that constitute a defined balance. Its key columns are the foreign keys BALANCE_TYPE_ID and BALANCE_DIMENSION_ID, which together (along with BUSINESS_GROUP_ID and LEGISLATION_CODE) form a unique key (PAY_DEFINED_BALANCES_UK2). The primary key is the system-generated DEFINED_BALANCE_ID, used as a concise identifier for foreign key relationships throughout the payroll schema. The BUSINESS_GROUP_ID and LEGISLATION_CODE columns provide the security and legislative context for the defined balance, determining its visibility and applicability. This structure ensures that each balance (e.g., "Gross Earnings YTD") is uniquely and consistently defined for use in calculations, reporting, and compliance.

Common Use Cases and Queries

This table is central to any technical operation involving payroll balances. Common use cases include troubleshooting balance initialization issues, validating balance configurations, and building custom reports that require balance definitions. A typical query retrieves the readable name of a balance by joining to PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS. For example, to find all defined balances for a specific balance type, one might use:

SELECT pdb.defined_balance_id, pbt.balance_name, pbd.dimension_name
FROM pay_defined_balances pdb,
     pay_balance_types pbt,
     pay_balance_dimensions pbd
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = 'Gross Earnings';

Implementation consultants and developers reference this table when configuring fast formulas, defining balance feeds, or diagnosing why a particular balance value is not populating as expected.

Related Objects

As the intersection point for balances, PAY_DEFINED_BALANCES has extensive relationships with transactional and storage tables in the payroll schema. The documented foreign key relationships show it is referenced by key balance storage tables, including PAY_RUN_BALANCES (stores balance values for each payroll run), PAY_ASSIGNMENT_LATEST_BALANCES, and PAY_PERSON_LATEST_BALANCES. It is also referenced by configuration objects such as PAY_BALANCE_ATTRIBUTES (which defines balance validation rules) and PAY_BACKPAY_RULES. The primary relationships are:

• References (Parent Tables): PAY_BALANCE_TYPES (via BALANCE_TYPE_ID) and PAY_BALANCE_DIMENSIONS (via BALANCE_DIMENSION_ID).
• Referenced By (Child Tables):
  • PAY_ASSIGNMENT_LATEST_BALANCES (DEFINED_BALANCE_ID)
  • PAY_BACKPAY_RULES (DEFINED_BALANCE_ID)
  • PAY_BALANCE_ATTRIBUTES (DEFINED_BALANCE_ID)
  • PAY_LATEST_BALANCES (DEFINED_BALANCE_ID)
  • PAY_MONITOR_BALANCE_RETRIEVALS (DEFINED_BALANCE_ID)
  • PAY_ORG_PAYMENT_METHODS_F (DEFINED_BALANCE_ID)
  • PAY_PERSON_LATEST_BALANCES (DEFINED_BALANCE_ID)
  • PAY_RUN_BALANCES (DEFINED_BALANCE_ID)