Results for “balance_type_column”

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

Overview

The HR_S_DIMENSION_ROUTES table resides in the HR schema and belongs to the PER (Human Resources) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its ETRM description is recorded simply as "-Retrofitted," indicating that the object was introduced or reconciled into the release during a documentation retrofit cycle rather than originated as a net-new design artifact. Functionally, the table defines the routing rules that map balance dimensions to the dimensions used at run time when payroll and balance processing resolves dimension values. It acts as a control or configuration table that governs how a balance dimension is translated, derived, or decoded during processing, including the ordering (priority) in which candidate routes are evaluated.

From a Data Vault modeling perspective, the mined foreign-key structure classifies HR_S_DIMENSION_ROUTES as standalone. This is a heuristic suggestion rather than a normative statement: the table references other objects but is not itself referenced by a documented foreign key, which is characteristic of a reference or configuration table rather than a hub, link, or satellite. Architects should treat the classification as a starting point for lineage modeling rather than a definitive Data Vault assignment.

Key Information Stored

The documented physical schema for 12.2.2 contains 13 columns. The most significant include:

• BALANCE_DIMENSION_ID — Foreign key to PAY_BALANCE_DIMENSIONS; identifies the balance dimension being routed.
• ROUTE_ID — Identifier for the individual route record associated with a given dimension.
• ROUTE_TYPE — Classifies the routing behavior applied to the dimension.
• PRIORITY — Determines evaluation order when multiple routes exist for the same dimension.
• RUN_DIMENSION_ID — Identifies the dimension used at run time as the routing target.
• BALANCE_TYPE_COLUMN — Names the column from which balance type information is sourced.
• DECODE_REQUIRED — Flag indicating whether a decode or lookup step must be applied during routing.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard audit columns tracking who created and last modified the row.
• OBJECT_VERSION_NUMBER — Optimistic locking token used by the Oracle EBS framework to detect concurrent updates.

The documented metadata does not identify a named surrogate primary key or a unique business-key index; ROUTE_ID combined with BALANCE_DIMENSION_ID serves as the practical business-key candidate based on the FK relationship.

Common Use Cases and Queries

Typical usage includes diagnosing why a balance resolves to an unexpected dimension, validating routing configuration before payroll runs, and reporting on the routing rule set for a given balance dimension. A representative query joins the table to its parent dimension definition:

• SELECT r.ROUTE_ID, r.ROUTE_TYPE, r.PRIORITY, r.RUN_DIMENSION_ID, r.DECODE_REQUIRED FROM HR.HR_S_DIMENSION_ROUTES r JOIN PAY.PAY_BALANCE_DIMENSIONS d ON r.BALANCE_DIMENSION_ID = d.BALANCE_DIMENSION_ID WHERE r.BALANCE_DIMENSION_ID = :dim_id ORDER BY r.PRIORITY;
• Configuration audits commonly filter on DECODE_REQUIRED = 'Y' to isolate routes requiring additional decode processing.
• Change-tracking queries use LAST_UPDATE_DATE and LAST_UPDATED_BY to identify recent routing modifications.

Related Objects

• PAY_BALANCE_DIMENSIONS — Parent reference through BALANCE_DIMENSION_ID; defines the balance dimension being routed.
• PAY_BALANCE_TYPES — Logical companion object supplying balance type context referenced by BALANCE_TYPE_COLUMN.
• PAY_RUN_BALANCES — Consumes run-time dimension resolution produced by these routes.
• PAY_BALANCE_DIMENSION_ROUTES — Related routing configuration used in balance dimension processing.
• PAY_BALANCE_ATTRIBUTE_DEFINITIONS — Defines the attributes that routing and decode logic operate against.

Because the object is classified as standalone, no dependent child tables are documented; impact analysis should focus on the parent balance dimension and the downstream payroll balance resolution logic.