Search Results ff_route_context_usages_uk2




Overview
Explore in AI Chat

Overview

The HR.FF_ROUTE_CONTEXT_USAGES table is a core data dictionary object within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically for the FastFormula engine in releases 12.1.1 and 12.2.2. It functions as a mapping table that defines the relationship between a FastFormula route and the database contexts it utilizes. A route is a SQL statement embedded within a FastFormula that retrieves data, and contexts are the bind variables or parameters used within that SQL. This table explicitly documents each instance where a specific context is employed as a bind variable in a specific route's SQL statement, ensuring the formula engine correctly resolves data dependencies during execution.

Key Information Stored

The table is structurally simple but critical for maintaining referential integrity between routes and their parameters. It contains three mandatory columns:

  • ROUTE_ID (NUMBER): A foreign key that references the ROUTE_ID in the FF_ROUTES table, uniquely identifying the SQL statement (route).
  • CONTEXT_ID (NUMBER): A foreign key that references the CONTEXT_ID in the FF_CONTEXTS table, identifying the specific context or bind variable used.
  • SEQUENCE_NO (NUMBER): Denotes the ordinal position of this context within the route's SQL text, which is essential for correctly binding values when the statement is prepared and executed.

The primary key (FF_ROUTE_CONTEXT_USAGES_PK) is a composite of ROUTE_ID and CONTEXT_ID, enforcing the rule that a given context can be used only once per route. A unique key (FF_ROUTE_CONTEXT_USAGES_UK2) on ROUTE_ID and SEQUENCE_NO ensures the order of contexts is uniquely maintained for each route.

Common Use Cases and Queries

This table is primarily accessed for diagnostic, impact analysis, and development purposes within the FastFormula framework. A common use case is tracing all routes that depend on a particular context, which is vital when a context definition is modified. The standard query to retrieve all records, as indicated in the ETRM, is:

SELECT ROUTE_ID, CONTEXT_ID, SEQUENCE_NO FROM HR.FF_ROUTE_CONTEXT_USAGES;

For practical analysis, this query is typically joined with FF_ROUTES and FF_CONTEXTS. For example, to list all contexts used by a specific route along with their sequence and names:

SELECT rcu.SEQUENCE_NO, c.CONTEXT_NAME
FROM ff_route_context_usages rcu,
     ff_contexts c
WHERE rcu.CONTEXT_ID = c.CONTEXT_ID
AND rcu.ROUTE_ID = <target_route_id>
ORDER BY rcu.SEQUENCE_NO;

Conversely, to identify all routes using a specific context for impact analysis:

SELECT r.ROUTE_NAME
FROM ff_route_context_usages rcu,
     ff_routes r
WHERE rcu.ROUTE_ID = r.ROUTE_ID
AND rcu.CONTEXT_ID = <target_context_id>;

Related Objects

The table maintains strict foreign key relationships with two central FastFormula dictionary tables, as documented in the metadata:

  • FF_ROUTES: The FF_ROUTE_CONTEXT_USAGES.ROUTE_ID column references FF_ROUTES.ROUTE_ID. This links a usage record to the master definition of the SQL route.
  • FF_CONTEXTS: The FF_ROUTE_CONTEXT_USAGES.CONTEXT_ID column references FF_CONTEXTS.CONTEXT_ID. This links a usage record to the definition of the context (bind variable).

The table is also referenced by several APPS-layer database objects, including the base table synonym (FF_ROUTE_CONTEXT_USAGES) and the Before Row Delete (BRD) and Before Row Insert (BRI) database triggers, which manage underlying business logic and integrity.