Results for “ff_routes”

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

Overview

FF_ROUTES is an HR-schema table within the Oracle E-Business Suite FastFormula (FF) module. It stores the FROM and WHERE clause fragments of SQL statements used to resolve specific database items referenced by FastFormula definitions. In practice, a "route" defines how the FastFormula engine navigates from a driving entity to the data value it needs, encapsulating the join and filter logic that the formula compiler and runtime require.

Under a heuristic Data Vault classification derived from its foreign-key topology, FF_ROUTES is best modeled as a hub. It is the anchor table referenced by six dependent child tables through the ROUTE_ID column, and it does not itself carry a foreign key to an upstream parent within the documented structure. This hub role reflects its function as the central registry of named routing definitions that other FastFormula and payroll structures attach to.

Key Information Stored

FF_ROUTES owns twelve documented columns. The most significant are:

• ROUTE_ID — surrogate primary key (FF_ROUTES_PK). Uniquely identifies each routing definition and is the column propagated to all dependent tables.
• ROUTE_NAME — the business-key candidate, enforced by unique index FF_ROUTES_UK2 (ROUTE_NAME, ZD_EDITION_NAME). This is the human-readable identifier most users search for and reference in formulas.
• TEXT — the core payload: the FROM and WHERE clause text that the SQL generator injects when resolving a database item.
• USER_DEFINED_FLAG — indicates whether the route was seeded by Oracle or created by a customer, which governs upgrade behavior.
• DESCRIPTION — free-text explanation of the route's purpose and intended database item.
• OPTIMIZER_HINT — optional Oracle optimizer hint text applied to the generated SQL for performance tuning.
• ZD_EDITION_NAME — editioning column supporting EBR (Edition-Based Redefinition) in 12.2.x, present in both unique indexes.

Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — provide audit lineage and are populated automatically by the application.

Common Use Cases and Queries

The dominant access pattern is a lookup by name, which the user's search term "route_name" directly reflects. A typical retrieval:

• SELECT ROUTE_ID, ROUTE_NAME, TEXT FROM HR.FF_ROUTES WHERE ROUTE_NAME = :p_route_name;

Developers debugging a FastFormula database item use this query to inspect the generated FROM/WHERE logic. Administrators auditing customer modifications filter on USER_DEFINED_FLAG to isolate non-seeded routes prior to upgrades. Because FF_ROUTES is a hub, join-based reporting typically combines it with its children; for example, listing every parameter tied to a route:

• SELECT r.ROUTE_NAME, p.* FROM HR.FF_ROUTES r JOIN HR.FF_ROUTE_PARAMETERS p ON p.ROUTE_ID = r.ROUTE_ID WHERE r.ROUTE_NAME = :p_route_name;

Context-sensitive routes can be reported by joining FF_ROUTE_CONTEXT_USAGES, and payroll dimension routing by joining PAY_DIMENSION_ROUTES or PAY_BALANCE_DIMENSIONS. Querying the OPTIMIZER_HINT column supports performance diagnostics when generated FastFormula SQL performs poorly.

Related Objects

Six documented tables reference FF_ROUTES through ROUTE_ID, forming the hub's dependent fan-out:

• FF_FDI_USAGES_F — FastFormula database item usage records linking items to routes.
• FF_ROUTE_CONTEXT_USAGES — context-specific applicability of each route.
• FF_ROUTE_PARAMETERS — bind parameters supplied to the route's SQL fragment.
• FF_USER_ENTITIES — user-defined entity definitions that resolve through routes.
• PAY_BALANCE_DIMENSIONS — payroll balance dimension routing.
• PAY_DIMENSION_ROUTES — maps payroll dimensions to their governing routes.

All relationships join on FF_ROUTES.ROUTE_ID to the child's ROUTE_ID column. Together these objects make FF_ROUTES the central integration point between FastFormula data resolution and the Oracle Payroll dimension model.