Search Results fii_fa_cat_dimensions_pk

Overview

FII_FA_CAT_DIMENSIONS is a dimension table within the Oracle E-Business Suite Financial Intelligence (FII) product family. FII was Oracle's analytical layer built on top of the Enterprise Resource Planning (ERP) and Enterprise Asset Management (EAM) database schemas, designed to pre-stage and reshape transactional data into star-schema-like structures suitable for extraction into Oracle Daily Business Intelligence (DBI) and Oracle Business Intelligence (OBI) warehouses. The FII schema is documented as obsolete and, per the ETRM metadata, is not implemented in the reference database environment. Nevertheless, the table retains historical relevance for installations that deployed early Fixed Assets analytics and for environments that are migrating away from legacy FII-based extracts.

The table stores the Asset Category dimension information used by the Fixed Assets base summary extract. According to the ETRM description, it holds one record for each category flexfield defined, together with the identifiers and display values required to construct the category dimension of the base summary extract. Because the Asset Category is typically a keyed flexfield in Oracle Assets, this table denormalizes the flexfield structure and selected segment values into a flat dimension representation.

The heuristic Data Vault classification supplied in the metadata is standalone. In Data Vault terms, this object is not a link because it holds no foreign keys to peer hubs; its single documented reference (FLEX_STRUCTURE_ID to FII_FA_CAT_SEGMENTS) is internal to the FII dimension family. The modelling suggestion is therefore to treat FII_FA_CAT_DIMENSIONS as a hub or reference/dimension satellite centered on CATEGORY_ID, with the create/update audit columns forming the standard satellite metadata. It should not be modelled as a transaction or link entity.

Key Information Stored

The documented physical schema for FII_FA_CAT_DIMENSIONS in ETRM 12.1.1 comprises eleven columns owned by the FII schema. The most important of these are:

• CATEGORY_ID — The surrogate primary key of the table, surfaced through both the FII_FA_CAT_DIMENSIONS_PK constraint and the unique index FII_FA_CAT_DIMENSIONS_U1. In the dimension model it identifies a single asset category row in the extract.
• FLEX_STRUCTURE_ID — Identifies the key flexfield structure to which the category belongs. This is the column documented as referencing FII_FA_CAT_SEGMENTS, tying the category dimension back to the segment-level definition of the category key flexfield.
• MAJOR_ID / MAJOR_VALUE — The identifier and display value of the major (or first) segment of the Asset Category flexfield as configured at the time of extraction.
• MINOR_ID / MINOR_VALUE — The identifier and display value of the minor (or subordinate) segment of the Asset Category flexfield.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — The standard Oracle WHO audit columns that record when and by whom the dimension row was inserted and last modified.

CATEGORY_ID is the sole documented business-key candidate via the U1 unique index; no other unique constraint is recorded. The remaining non-audit columns (FLEX_STRUCTURE_ID, MAJOR_ID, MAJOR_VALUE, MINOR_ID, MINOR_VALUE) carry the descriptive content of the dimension.

Common Use Cases and Queries

The principal use case is populating and validating the Fixed Assets base summary extract. Typical queries join the dimension to the segment table to resolve structure descriptions:

• Enumerating all defined asset category dimensions: SELECT category_id, flex_structure_id, major_value, minor_value FROM fii.fii_fa_cat_dimensions.
• Resolving a category to its flexfield structure: SELECT d.category_id, d.major_value, s.* FROM fii.fii_fa_cat_dimensions d JOIN fii.fii_fa_cat_segments s ON d.flex_structure_id = s.structure column. The exact join column on FII_FA_CAT_SEGMENTS must be confirmed from that table's definition, as only the parent side is documented here.
• Currency and hierarchy reconciliation reporting that maps MAJOR_VALUE/MINOR_VALUE pairs to the asset category cache used by the extract programs.
• Audit and change-tracking queries filtering on LAST_UPDATE_DATE to identify dimensions refreshed since a prior extract run.

Because FII is obsolete, these queries are generally used for migration verification or for decommissioning analysis rather than for ongoing production reporting.

Related Objects

• FII_FA_CAT_SEGMENTS — Referenced by FLEX_STRUCTURE_ID. Defines the segment-level structure underlying each asset category dimension and is the primary companion table for join queries.
• FII_FA_CAT_DIMENSIONS_PK — The primary key constraint on CATEGORY_ID that enforces uniqueness at the row level.
• FII_FA_CAT_DIMENSIONS_U1 — Unique index on CATEGORY_ID, providing the documented business-key candidate.
• FA_CATEGORIES and FA_CATEGORY_BOOKS_DEFAULTS — The Oracle Assets source tables from which the category identifiers and values are ultimately derived.
• FND_FLEX_VALUES and FND_ID_FLEX_STRUCTURES — The Applications key flexfield definition tables that back MAJOR_ID/MAJOR_VALUE, MINOR_ID/MINOR_VALUE and FLEX_STRUCTURE_ID.
• FII_FA_* base summary extract tables — The dependent extract fact and dimension tables that consume FII_FA_CAT_DIMENSIONS during population of the Fixed Assets reporting extract.

These relationships should be validated against the customer's actual FII schema prior to use, given the obsolete status of the product and the absence of an implemented instance in the reference ETRM database.