Results for “ff_database_items_vl”

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

Overview

In Oracle EBS 12.1.1 and 12.2.2, APPS.FF_DATABASE_ITEMS_VL is the standard translated (_VL) view layered over the FastFormula database items dictionary. FastFormula is the rules and calculation engine used throughout Oracle EBS by modules such as Payroll, OAB (Oracle Advanced Benefits), Time and Labor, and Compensation Workbench. Database items are the named values — things like a payroll element, a balance, an assignment attribute, or a derived calculation — that a formula author may reference when writing a FastFormula.

FF_DATABASE_ITEMS stores the technical definition of each database item: its internal name, its owning entity, its data type, whether it may be null, and the SQL or PL/SQL expression that resolves the item's runtime value. This view also joins the translation table so that the user-facing name and description appear according to the current session language. The view is read-only and is populated automatically when formulas are compiled and database items are generated.

Underlying Base Objects

The view is defined over two objects, referenced through APPS synonyms:

• FF_DATABASE_ITEMS — the base table holding the untranslated database item definition, keyed by USER_NAME and USER_ENTITY_ID.
• FF_DATABASE_ITEMS_TL — the translation (_TL) table holding language-specific values (TRANSLATED_USER_NAME and DESCRIPTION) keyed by the same USER_NAME and USER_ENTITY_ID plus a LANGUAGE code.

The view text is:

SELECT B.ROWID ROW_ID, B.USER_NAME, B.USER_ENTITY_ID, B.DATA_TYPE, B.DEFINITION_TEXT, B.NULL_ALLOWED_FLAG, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.CREATED_BY, B.CREATION_DATE, T.TRANSLATED_USER_NAME, T.DESCRIPTION FROM FF_DATABASE_ITEMS_TL T, FF_DATABASE_ITEMS B WHERE B.USER_NAME = T.USER_NAME AND B.USER_ENTITY_ID = T.USER_ENTITY_ID AND T.LANGUAGE = USERENV('LANG')

Because the LANGUAGE predicate is bound to USERENV('LANG'), the view returns exactly one row per database item for the session language. This is important in multi-language installations where FF_DATABASE_ITEMS_TL carries multiple rows per item.

Key Columns

• USER_NAME — the internal (untranslated) name of the database item; the key used by formulas.
• USER_ENTITY_ID — identifies the entity (e.g., context or owning object) to which the item belongs; combined with USER_NAME this uniquely identifies an item.
• DEFINITION_TEXT — the actual SQL or PL/SQL expression that defines and resolves the database item value at runtime. This is the column users most often search for.
• DATA_TYPE — the data type of the item (character, number, date, etc.).
• NULL_ALLOWED_FLAG — indicates whether the item may return a null value.
• TRANSLATED_USER_NAME and DESCRIPTION — language-specific display name and description from the _TL table.
• ROW_ID — the ROWID of the underlying base row, exposed for DBA and troubleshooting use.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns.

Common Use Cases and Queries

The view is used for reviewing how an item is defined, identifying which items use a given expression, and supporting integration where an external program must resolve an item definition. A typical query retrieves definition text:
SELECT USER_NAME, USER_ENTITY_ID, DATA_TYPE, DEFINITION_TEXT FROM FF_DATABASE_ITEMS_VL WHERE USER_NAME = :item_name;

To find all items referencing a particular table or column:
SELECT USER_NAME, USER_ENTITY_ID, DATA_TYPE, DEFINITION_TEXT FROM FF_DATABASE_ITEMS_VL WHERE UPPER(DEFINITION_TEXT) LIKE '%PAY_PAYROLL_ACTIONS%';

To list items for a single entity with their definitions:
SELECT USER_NAME, DATA_TYPE, NULL_ALLOWED_FLAG, DEFINITION_TEXT FROM FF_DATABASE_ITEMS_VL WHERE USER_ENTITY_ID = :entity_id ORDER BY USER_NAME;

Because DEFINITION_TEXT can be long, display tools should treat it accordingly. The view itself carries no direct maintenance; changes are driven upstream by database item generation in FastFormula.