Results for “ff_lookups”

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

Overview

The FF_LOOKUPS table, owned by the HR schema and validated in Oracle E-Business Suite 12.1.1 and 12.2.2, stores lookup values used specifically by the FastFormula (FF) product. FastFormula is the rule-based calculation engine embedded throughout Oracle EBS — it evaluates compensation, benefits eligibility, accrual balances, absence rules, payroll processing, and many other HRMS calculations. Rather than hard-coding enumerations such as formula types, formula statuses, context usage flags, and database item categories, FastFormula reads them from FF_LOOKUPS. This design lets Oracle deliver seed data and lets administrators extend or translate lookup values through standard lookup maintenance without patching application code.

From a dimensional modeling perspective, the ETRM metadata classifies this object heuristically as standalone — that is, it participates in no foreign-key relationships to other tables. In Data Vault terms, the natural modeling suggestion is a satellite or reference hub rather than a link: the table holds descriptive attributes keyed by an enumerated code set, and no foreign keys anchor it to parent entities. It functions as a code/translation reference table whose rows are consumed at runtime by the FastFormula parser and its administrative forms.

Key Information Stored

The documented physical schema comprises 13 columns. The most significant are:

• LOOKUP_TYPE — the category of lookup, for example a FastFormula-specific grouping such as formula type or context. Combined with LOOKUP_CODE it forms the business key.
• LOOKUP_CODE — the internal code that application logic and formula syntax reference.
• MEANING — the user-visible, translatable display value for the code.
• DESCRIPTION — supplementary explanatory text for administrators and developers.
• ENABLED_FLAG — indicates whether the value is selectable at runtime; disabled codes remain in the table for historical or upgrade purposes.
• START_DATE_ACTIVE and END_DATE_ACTIVE — effective-dating bounds controlling when a lookup value is valid.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard WHO audit columns.
• CREATED_BY and CREATION_DATE — row creation audit columns.
• ZD_EDITION_NAME — the editioning discriminator introduced by EBS online patching in 12.2, enabling edition-based redefinition.

The documented primary key constraint, FF_LOOKUPS_PK, covers LOOKUP_TYPE and LOOKUP_CODE. The unique index definition also includes ZD_EDITION_NAME (LOOKUP_TYPE, LOOKUP_CODE, ZD_EDITION_NAME), confirming that the business key is edition-scoped in 12.2. There is no separate surrogate key column; the composite business key serves as the primary key.

Common Use Cases and Queries

Typical uses include resolving codes to display values in reporting, auditing active lookup values, and diagnosing FastFormula parse failures caused by a missing or disabled code. A simple retrieval pattern follows:

• SELECT lookup_code, meaning, enabled_flag FROM ff_lookups WHERE lookup_type = :p_type;
• SELECT lookup_code, meaning FROM hr.ff_lookups WHERE lookup_type = :p_type AND enabled_flag = 'Y' AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
• SELECT lookup_type, COUNT(*) FROM hr.ff_lookups WHERE enabled_flag = 'Y' GROUP BY lookup_type ORDER BY 1;

Analysts commonly join FF_LOOKUPS to FF_FORMULAS or FF_CONTEXTS to translate stored type codes into readable labels in formula inventory and setup reports. In 12.2 environments, queries should be aware of ZD_EDITION_NAME when row counts appear inflated.

Related Objects

• FF_FORMULAS — stores formula definitions; its formula-type column resolves against FF_LOOKUPS LOOKUP_CODE values.
• FF_FORMULA_TYPES — the FastFormula catalog of formula types, closely aligned with FF_LOOKUPS entries.
• FF_CONTEXTS — defines formula contexts, several of whose code values are maintained as FF lookups.
• FF_ROUTES — routes formulas to contexts and often depends on lookup-defined type codes.
• FND_LOOKUP_TYPES — the general lookup-type registry; product-specific lookup tables such as FF_LOOKUPS mirror its structure.
• FND_LOOKUP_VALUES — the Applications-wide lookup values table whose pattern FF_LOOKUPS follows.
• HR_LOOKUPS — the broader HRMS lookup table from which FF-specific values are conceptually carved out.

No foreign keys are documented, so joins are performed on the shared LOOKUP_TYPE and LOOKUP_CODE business columns rather than enforced referential constraints.