Search Results prod_value




Overview
Explore in AI Chat

Overview

APPS.FII_ALL_PRODUCTS_V is a reporting view in Oracle E-Business Suite releases 12.1.1 and 12.2.2 that exposes a flattened, denormalized list of enabled product categories. Its name reflects its origin within the FII (Financial Intelligence/analytics) schema, and it is a key object for Enterprise Resource Planning analytics, data warehouse extracts, and downstream ETRM reporting where a simple product dimension is required.

The view resolves product identity to a concatenated category value through the segment structure of a category set. Rather than requiring report developers to join category sets, category structures, and category hierarchies explicitly, the view returns a ready-to-use product row containing an identifier, a display value, and a description. This makes it particularly useful for ETL mappings and Discoverer-style folders where a single product key must be joined to fact data.

Underlying Base Objects

The documented view text defines FII_ALL_PRODUCTS_V as a join between two base objects:

  • MTL_CATEGORY_SETS_VL (alias MSV) — the category set definition view, supplying the category_set_id and structure_id.
  • MTL_CATEGORIES_KFV (alias MC) — the key-flexfield category view, supplying category_id, concatenated_segments, description, and enabled_flag.

The two objects are joined on structure_id (MC.STRUCTURE_ID = MSV.STRUCTURE_ID). The category set itself is not passed as a bind variable; it is resolved by the PL/SQL function ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID, which returns the active category set for the denormalized product hierarchy. Rows are restricted to MC.ENABLED_FLAG = 'Y', so disabled categories are excluded from the result set. No additional base objects are documented in the ETRM metadata, and the referenced base objects list is empty for this view.

Key Columns

  • PROD_ID — derived from MC.CATEGORY_ID. The numeric category identifier that serves as the surrogate product key.
  • PROD_VALUE — derived from MC.CONCATENATED_SEGMENTS. The concatenated, human-readable product category value, which is the column most often searched by users (for example, when searching for "prod_value").
  • DESCRIPTION — derived from MC.DESCRIPTION. The descriptive name of the category.

The view is ordered by PROD_VALUE, giving predictable, alphabetical output without an explicit ORDER BY in the calling query. Because PROD_VALUE originates from CONCATENATED_SEGMENTS, its format depends on the segment delimiter and value set definitions of the underlying category flexfield structure.

Common Use Cases and Queries

Typical scenarios include building a product dimension for BI Publisher reports, populating a lookup in ETRM or custom forms, and joining product values to transactional or fact tables for reporting extracts.

Basic listing of all enabled products:

  • SELECT prod_id, prod_value, description FROM apps.fii_all_products_v;

Looking up a specific product by its concatenated value, which is the common case when a user searches for "prod_value":

  • SELECT prod_id, prod_value, description FROM apps.fii_all_products_v WHERE prod_value = :search_value;
  • SELECT prod_id, prod_value FROM apps.fii_all_products_v WHERE UPPER(prod_value) LIKE UPPER('%' || :partial_value || '%');

Joining the product dimension to a fact query to label results by product:

  • SELECT f.prod_value, SUM(t.amount) FROM apps.fii_all_products_v f, apps.my_fact_table t WHERE f.prod_id = t.prod_id GROUP BY f.prod_value ORDER BY f.prod_value;

Because the category set is determined internally by ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID, the view always reflects the same product hierarchy regardless of caller context, which ensures consistency across reports. Report authors should note that the function must be valid and the category set must be properly configured, otherwise the view may return no rows or fail to resolve.