Search Results ff_routes

Overview

The FF_ROUTES table is a core metadata repository within the Oracle E-Business Suite FastFormula (FF) engine. It defines the data retrieval logic for database items, which are the fundamental building blocks for writing formulas across HR, Payroll, and other modules. Specifically, this table stores the FROM and WHERE clause components of a SQL statement that the FastFormula engine dynamically assembles and executes to fetch a specific value from the database. Each row in FF_ROUTES acts as a "route" or pathway that instructs the formula engine on how to locate the data for a given context, enabling the declarative and reusable nature of FastFormula items.

Key Information Stored

The table's structure is designed to encapsulate SQL fragments and their usage metadata. The primary columns include ROUTE_ID (the unique primary key identifier) and ROUTE_NAME (a unique name for the route). The most critical functional columns are those that store the SQL components, such as the FROM_CLAUSE, which specifies the table or view to query, and the WHERE_CLAUSE, which defines the join conditions and filters to locate the correct row of data. Additional columns manage sequencing, effective dating, and other control attributes necessary for the formula engine's operation.

Common Use Cases and Queries

The primary use case is the development and troubleshooting of FastFormula database items. When a formula references an item like `ASG_SALARY`, the engine consults FF_ROUTES to build the SQL to retrieve the salary for the correct assignment. Common queries involve analyzing the definition of routes or tracing dependencies. For example, to find the SQL definition for a specific route:

• SELECT route_name, from_clause, where_clause FROM ff_routes WHERE route_name = 'ROUTE_FOR_SALARY';

Another critical scenario is impact analysis before modifying or obsoleting a route, which requires checking for dependencies in related tables like FF_USER_ENTITIES or PAY_BALANCE_DIMENSIONS.

Related Objects

As indicated by the foreign key relationships, FF_ROUTES is a central hub referenced by numerous other FastFormula and payroll metadata tables. Key dependent objects include:

• FF_USER_ENTITIES: Links routes to the user-defined database items used in formulas.
• FF_ROUTE_PARAMETERS and FF_ROUTE_CONTEXT_USAGES: Define the context values and parameters required for the route's SQL to execute.
• PAY_BALANCE_DIMENSIONS and PAY_DIMENSION_ROUTES: Critical for the payroll balance engine, specifying how to fetch data for balance calculations.
• FF_FDI_USAGES_F: Tracks the usage of routes by specific database items.

These relationships underscore that FF_ROUTES is foundational; changes to it can directly impact formula calculations, balance feeds, and payroll results.