Results for “ff_routes_pk”

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

Overview

HR.FF_ROUTES is a seed data table in the Oracle E-Business Suite HR schema that stores the route definitions used by the Flexfields (FF) engine. Each row represents a reusable SQL FROM-and-WHERE fragment that describes how the application should access a specific data item. The complete SQL statement executed by the flexfield machinery is assembled by combining the route text held in this table with the definition text stored on the corresponding database item. In this sense FF_ROUTES functions as the shared "navigation layer" for flexfield value sets, key flexfields, descriptive flexfields, and the payroll dimension infrastructure that depends on the same routing mechanism.

The table resides in the APPS_TS_SEED tablespace, reflecting its role as an Oracle-seeded definition object shipped with the application. Its primary key, FF_ROUTES_PK, is defined on ROUTE_ID, and a secondary unique key, FF_ROUTES_UK2, is defined on ROUTE_NAME. In the 12.2.2 documented schema both unique indexes additionally include the ZD_EDITION_NAME column, which is consistent with the Edition-Based Redefinition architecture introduced in EBS 12.2. From a Data Vault modeling perspective, the metadata's heuristic classification of FF_ROUTES is a hub: ROUTE_ID is a stable, non-business surrogate key that anchors the route identity, while descriptive attributes such as DESCRIPTION, TEXT, and OPTIMIZER_HINT behave as satellite-style payload.

Key Information Stored

The most significant columns of FF_ROUTES are:

• ROUTE_ID — Numeric unique identifier for the route; the surrogate primary key (FF_ROUTES_PK).
• ROUTE_NAME — Name of the route (VARCHAR2(80)); the business-key candidate enforced by FF_ROUTES_UK2.
• TEXT — LONG column holding the actual FROM clause text (and WHERE semantics) used to build the access statement for a data item.
• OPTIMIZER_HINT — VARCHAR2(2000) hint passed to the Cost Based Optimizer when the generated SQL executes.
• DESCRIPTION — VARCHAR2(240) describing what the route does.
• USER_DEFINED_FLAG — Indicates whether a customer-defined route should be preserved during upgrades.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard WHO audit columns.
• ZD_EDITION_NAME — Editioning column present in the 12.2.2 physical schema, supporting edition-based redefinition of the seed data.

The ROUTE_ID/ROUTE_NAME pairing distinguishes the immutable technical key from the human-readable business key, a pattern typical of seeded reference hubs.

Common Use Cases and Queries

Developers and functional consultants inspect FF_ROUTES to diagnose flexfield SQL generation, review optimizer hints that may affect performance of value-set queries, and confirm whether a route is seeded or customer-defined. A basic lookup by route name is common:

• SELECT route_id, route_name, description, optimizer_hint FROM hr.ff_routes WHERE route_name = :p_name;
• SELECT r.route_id, r.route_name, u.entity_name FROM hr.ff_routes r JOIN hr.ff_user_entities u ON u.route_id = r.route_id WHERE r.user_defined_flag = 'Y';

Reporting use cases include listing all customer-defined routes that must be reviewed before an upgrade, and auditing which entities, parameters, or payroll dimensions depend on a given route.

Related Objects

FF_ROUTES is the referenced (parent) side of several foreign keys, confirming its hub role:

• FF_USER_ENTITIES — references FF_ROUTES.ROUTE_ID; maps user entities to their routes.
• FF_ROUTE_PARAMETERS — references FF_ROUTES.ROUTE_ID; defines parameters bound to a route.
• FF_ROUTE_CONTEXT_USAGES — references FF_ROUTES.ROUTE_ID; records context usage for routes.
• FF_FDI_USAGES_F — references FF_ROUTES.ROUTE_ID; flexfield data-item usage link.
• PAY_DIMENSION_ROUTES — references FF_ROUTES.ROUTE_ID; payroll dimension routing.
• PAY_BALANCE_DIMENSIONS — references FF_ROUTES.ROUTE_ID; balance dimension routing.

Together these relationships make FF_ROUTES a foundational parent for flexfield and payroll dimension configuration, and any modification to a route should be evaluated against every dependent child table listed above.