Results for “pay_dimension_routes”

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

Overview

PAY_DIMENSION_ROUTES is a Payroll (PAY) module table owned by the HR schema. It stores balance dimension relationships, defining how a balance dimension is resolved into the underlying payroll run dimension through a configurable routing mechanism. In Oracle EBS 12.1.1 and 12.2.2, the table acts as a mapping layer that payroll balance processing uses to translate a requested balance dimension into the retrieval path defined by an FF_ROUTES (Fast Formula routes) definition and an associated run dimension.

The ETRM metadata records the object as VALID with 15 documented columns. Based on the foreign-key structure, the heuristic Data Vault classification is a link table. This is a modeling suggestion rather than a physical design: the table primarily resolves relationships between balance dimensions and routing definitions, rather than storing an independent business entity or descriptive attributes over time.

Key Information Stored

The primary key is PAY_DIMENSION_ROUTES_PK, documented as the composite (BALANCE_DIMENSION_ID, PRIORITY). The unique index is documented with the additional ZD_EDITION_NAME column, reflecting the 12.2.2 editioning architecture. The most significant columns are:

• BALANCE_DIMENSION_ID — foreign key to PAY_BALANCE_DIMENSIONS; identifies the balance dimension being routed. Part of the primary key and a business-key candidate.
• RUN_DIMENSION_ID — foreign key to PAY_BALANCE_DIMENSIONS; identifies the run dimension associated with the routing. Defines the second leg of the dimension relationship.
• ROUTE_ID — foreign key to FF_ROUTES; identifies the Fast Formula route used to resolve or retrieve the balance value.
• ROUTE_TYPE — indicates the type of routing applied when resolving the balance dimension.
• PRIORITY — numeric ordering that determines evaluation precedence when multiple routes exist for the same balance dimension. Part of the primary key and a business-key candidate.
• BALANCE_TYPE_COLUMN — specifies the column used to derive the balance type during retrieval.
• RETRIEVAL_COLUMN — identifies the column from which the balance value is retrieved.
• DECODE_REQUIRED — flag indicating whether a decode step is required during resolution.
• OBJECT_VERSION_NUMBER — standard EBS optimistic locking column.
• LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — standard EBS audit columns (with LAST_UPDATED_BY and LAST_UPDATE_LOGIN also present).
• ZD_EDITION_NAME — editioning discriminator introduced in 12.2.x, part of the unique index.

Common Use Cases and Queries

Typical usage centers on diagnosing balance retrieval configuration and tracing how a balance dimension resolves to its run dimension and Fast Formula route. A common query joins the routing table to its parent dimensions:

• Enumerate all routes for a given balance dimension in priority order: SELECT * FROM hr.pay_dimension_routes WHERE balance_dimension_id = :id ORDER BY priority;
• Resolve the run dimension and route definition: join to PAY_BALANCE_DIMENSIONS on both BALANCE_DIMENSION_ID and RUN_DIMENSION_ID, and to FF_ROUTES on ROUTE_ID.
• Audit routing changes using LAST_UPDATE_DATE, LAST_UPDATED_BY, and OBJECT_VERSION_NUMBER.
• Validate uniqueness of (BALANCE_DIMENSION_ID, PRIORITY, ZD_EDITION_NAME) against PAY_DIMENSION_ROUTES_PK when troubleshooting duplicate or missing route errors.

Related Objects

The following objects are most significant, based on the documented foreign-key relationships:

• PAY_BALANCE_DIMENSIONS — referenced twice, via BALANCE_DIMENSION_ID and RUN_DIMENSION_ID; the principal parent table.
• FF_ROUTES — referenced via ROUTE_ID; supplies the Fast Formula routing definition.
• PAY_DIMENSION_ROUTES_PK — the primary key constraint enforcing (BALANCE_DIMENSION_ID, PRIORITY).
• Payroll balance and Fast Formula APIs that consume dimension routing during balance calculation and reporting.
• Dependent balance processing views and concurrent programs that read routing configuration for payroll runs.