Search Results ff_compiled_info_f_pk

Overview

HR.FF_COMPILED_INFO_F is a DateTracked table in the Oracle E-Business Suite Human Resources schema that stores the executable PL/SQL code produced by the FastFormula compiler and consumed by the FastFormula execution engine. When an Oracle FastFormula is compiled, the engine translates the user-authored formula syntax into a generated PL/SQL program; that program is persisted in this table so that subsequent executions of the formula incur no recompilation cost. The table is a core component of the FastFormula infrastructure that underpins payroll calculations, benefits eligibility, absence accruals, compensation rules, and numerous other configurable business rules across Oracle EBS 12.1.1 and 12.2.2.

The object resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index FF_COMPILED_INFO_F_PK is stored in APPS_TS_TX_IDX. From a dimensional modeling perspective, the mined FK structure suggests classifying this object as a standalone or hub-adjacent entity: it carries the executable artifact keyed by formula identity and date range, with no outgoing foreign keys formally declared to other tables, though FORMULA_ID logically references FF_FORMULAS. In 12.2.2 the table additionally carries the ZD_EDITION_NAME column introduced by the Edition-Based Redefinition architecture.

Key Information Stored

The table comprises eight documented columns. The most significant are:

• FORMULA_ID (NUMBER) — Identifies the parent formula. Documented as a foreign key to FF_FORMULAS and forms the leading column of the primary key.
• EFFECTIVE_START_DATE (DATE) — DateTrack effective start of the compiled version.
• EFFECTIVE_END_DATE (DATE) — DateTrack effective end of the compiled version.
• COMPILED_TEXT (LONG) — The generated PL/SQL program that represents the user formula; this is the payload executed at runtime.
• TEXT_LENGTH (NUMBER) — Length in bytes of the PL/SQL program, useful for sizing and diagnostics.
• FDIU_ENTRY_COUNT (NUMBER) — Count of Formula Data Item Usage entries associated with the formula.
• SELECT_STATEMENT_COUNT (NUMBER) — Number of separate SELECT statements embedded in the generated PL/SQL, an indicator of database round-trip cost during execution.
• ZD_EDITION_NAME — Edition identifier used in 12.2.2 Edition-Based Redefinition.

The surrogate/business key is FF_COMPILED_INFO_F_PK, defined over FORMULA_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE in the base documentation, with ZD_EDITION_NAME appended to the unique index in the 12.2.2 physical schema. This composite key is the DateTrack business key: multiple rows per formula are permitted across effective date ranges, but only one compiled version is current for any given date.

Common Use Cases and Queries

The primary operational use is execution: the FastFormula engine selects the compiled PL/SQL matching the applicable formula and effective date. Administrative scenarios include auditing which formulas have been compiled, measuring complexity via TEXT_LENGTH and SELECT_STATEMENT_COUNT, and diagnosing stale or missing compilations. A typical lookup for a specific formula's current compiled code:

• SELECT FORMULA_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, TEXT_LENGTH, SELECT_STATEMENT_COUNT, FDIU_ENTRY_COUNT FROM HR.FF_COMPILED_INFO_F WHERE FORMULA_ID = :id AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
• Reporting on the largest generated programs: SELECT FORMULA_ID, TEXT_LENGTH FROM HR.FF_COMPILED_INFO_F ORDER BY TEXT_LENGTH DESC;
• Identifying formulas with heavy embedded SQL: filter on SELECT_STATEMENT_COUNT to target performance tuning.

Because COMPILED_TEXT is a LONG datatype, it cannot be directly manipulated in SQL string functions; extraction requires PL/SQL or DBMS_LOB-style handling. The table supports DateTrack correction, so historical and future-dated rows coexist and must be filtered by effective dates in all queries.

Related Objects

The most significant related objects are:

• HR.FF_FORMULAS — Parent entity; join on FORMULA_ID from FF_COMPILED_INFO_F.
• FF_FORMULAS_F and FF_FORMULAS_TL — DateTracked base and translation tables for formula definitions.
• FF_FORMULA_FUNCTIONS — Function definitions referenced within compiled formulas.
• FF_FDI_USAGES_F — Formula Data Item Usage records counted by FDIU_ENTRY_COUNT.
• FF_DATABASE_ITEMS — Database item definitions resolved by the compiled program.
• The APPS synonym FF_COMPILED_INFO_F and PUBLIC synonym FF_COMPILED_INFO_F, which expose the table to the Oracle Applications schema for runtime access.

All joins should key on FORMULA_ID and be constrained by effective dates to respect DateTrack semantics.