Results for “ffbv_formula”

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

Overview

FFBV_FORMULA is an APPS-owned, read-only database view in Oracle E-Business Suite (12.1.1 and 12.2.2) delivered within the FastFormula (FF) product family. It is documented in ETRM as a "Discoverer view for ff_formulas_f," meaning its primary purpose is to expose FastFormula header and text data in a reporting-friendly format consumable by Oracle Business Intelligence Discoverer (and, in later deployments, by BI Publisher, OBIEE, and custom SQL reporting). The view's defining characteristic is that it does not simply mirror the base table; it decorates the raw FastFormula rows with decoded lookup values and an inline business-group security flag, so that downstream reports can present meaningful labels and enforce row-level access without requiring the report author to reproduce that logic.

As a view rather than a table, FFBV_FORMULA carries no independent storage. Its status is VALID, and it is defined WITH READ ONLY, which prevents DML against the view and confirms its purely analytical role.

Underlying Base Objects

Per the documented metadata, FFBV_FORMULA is defined over two referenced base objects:

• FF_FORMULAS_F (referenced as a SYNONYM) — the core, date-effective FastFormula definition table. It holds the formula name, text, legislation, type, and audit columns. The _F suffix denotes a date-tracked (effectivity-controlled) entity, which is why EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are exposed.
• HR_BIS (PACKAGE) — the HR Business Intelligence/security package. The view calls HR_BIS.BIS_DECODE_LOOKUP to translate coded flags into readable values, HR_BIS.GET_SEC_PROFILE_BG_ID to obtain the security profile's business group, and HR_API.RETURN_LEGISLATION_CODE for legislation resolution.

The view aliases FF_FORMULAS_F as FF and layers the HR_BIS and HR_API calls on top of it. Because these are PL/SQL function calls, each query execution invokes them per row, which is the principal performance consideration for large extracts.

Key Columns

• FORMULA_NAME — the user-visible identifier of the formula.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective window of the formula version.
• FORMULA_TEXT — the actual FastFormula source text, the most frequently reported payload.
• LEGISLATION_CODE / DESCRIPTION — legislative context and free-text description.
• STICKY_FLAG, COMPILE_FLAG — decoded via BIS_DECODE_LOOKUP('YES_NO', …) into readable Yes/No values.
• STICKY_FLAG_CODE, COMPILE_FLAG_CODE — the raw underlying coded values retained for filtering or joining.
• FORMULA_ID — surrogate primary key, useful for joins to related tables.
• BUSINESS_GROUP_ID, FORMULA_TYPE_ID — ownership and classification keys.
• Audit columns — LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE.
• BG_SECURITY_FLAG_CODE — a computed CASE expression returning 'Y' or 'N', indicating whether the row is visible under the current HR security profile's business group (or its legislation).

Common Use Cases and Queries

Typical scenarios include auditing which formulas exist for a given legislation, extracting formula text for migration or version control, and building Discoverer/BIP reports restricted to the user's business group.

List all valid formulas for a legislation:

SELECT formula_name, effective_start_date, effective_end_date, compile_flag
FROM   apps.ffbv_formula
WHERE  legislation_code = 'US'
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;

Extract formula source for review:

SELECT formula_name, formula_text
FROM   apps.ffbv_formula
WHERE  formula_name = 'MY_FORMULA';

Enforce security and expose only permitted rows:

SELECT formula_name, business_group_id, bg_security_flag_code
FROM   apps.ffbv_formula
WHERE  bg_security_flag_code = 'Y';

Because the view is read-only and relies on HR_BIS functions, queries should be executed within an APPS or suitably privileged reporting responsibility context so that the security profile globals resolve correctly.