Results for “pay_defined_balances_uk2”
22 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
PAY_DEFINED_BALANCES, owned by the HR schema in Oracle EBS 12.1.1 and 12.2.2, is the intersection table between balance types and balance dimensions. Its purpose is to fully qualify a payroll balance: a balance type (for example, Gross Pay) combined with a balance dimension (for example, Year to Date) yields a specific defined balance. This defined balance is the entity referenced by name in Oracle Payroll FastFormula logic through its associated database item, effectively providing the runtime pointer that formulas use when retrieving accumulated values. The object status is VALID and its FND design data reference is PAY.PAY_DEFINED_BALANCES.
From a modeling perspective, the heuristic Data Vault classification of this table is a hub, derived from its foreign key structure. It carries a single-column surrogate primary key and acts as the durable business concept (the qualified balance definition) that other satellite-style transaction and accumulation tables point to.
Key Information Stored
The primary key is DEFINED_BALANCE_ID, a system-generated surrogate number enforced by the unique index PAY_DEFINED_BALANCES_PK. The business-key candidates are captured by the unique index PAY_DEFINED_BALANCES_UK2, which combines BALANCE_TYPE_ID, BALANCE_DIMENSION_ID, BUSINESS_GROUP_ID, and LEGISLATION_CODE (together with the edition column on 12.2.x). This composite uniqueness enforces that a given balance type/dimension pairing is valid only once per business group and legislation.
- BALANCE_TYPE_ID – foreign key to PAY_BALANCE_TYPES, identifying the quantified concept.
- BALANCE_DIMENSION_ID – foreign key to PAY_BALANCE_DIMENSIONS, identifying the time or qualifying dimension.
- BUSINESS_GROUP_ID – foreign key to HR_ORGANIZATION_UNITS; the enterprise context for the definition.
- LEGISLATION_CODE – foreign key to FND_TERRITORIES; localises the definition.
- FORCE_LATEST_BALANCE_FLAG – retain-but-not-currently-supported flag.
- GROSSUP_ALLOWED_FLAG – whether the balance can be grossed up.
- SAVE_RUN_BALANCE and RUN_BALANCE_STATUS – run-balance persistence controls.
- Standard Who columns – LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE and OBJECT_VERSION_NUMBER for audit and concurrency.
- ZD_EDITION_NAME – editioning support column present in 12.2.x.
Common Use Cases and Queries
The table is central to payroll balance reporting, formula troubleshooting, and setup validation. Analysts typically join it to its parents to resolve the readable balance name and dimension meaning:
- Resolving a defined balance ID returned by a formula or a run-balance row into text for reconciliation reports.
- Enumerating the defined balances available to a specific business group and legislation before authoring FastFormula.
- Auditing which balance/dimension combinations exist for a given balance type, using the UK2 business key.
A representative query resolves the described balance:
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 pdb.business_group_id = :bg_id;
Related Objects
Dependency relationships confirm PAY_DEFINED_BALANCES as a hub referenced widely by balance-storage and rule tables (join on DEFINED_BALANCE_ID unless noted):
- PAY_BALANCE_TYPES – parent via BALANCE_TYPE_ID.
- PAY_BALANCE_DIMENSIONS – parent via BALANCE_DIMENSION_ID.
- PAY_LATEST_BALANCES and PAY_ASSIGNMENT_LATEST_BALANCES – current-balance snapshots.
- PAY_PERSON_LATEST_BALANCES – person-level latest values.
- PAY_RUN_BALANCES – balances saved during a payroll run.
- PAY_BALANCE_ATTRIBUTES – attribute-level qualifiers.
- PAY_BACKPAY_RULES – back-pay recalculation rules.
- PAY_ORG_PAYMENT_METHODS_F – payment-method balance linkage.
- PAY_MONITOR_BALANCE_RETRIEVALS – balance retrieval diagnostics.
-
Intersection between PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS.
-
Intersection between PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS.
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.