Search Results prod_value

Overview

FII_ALL_PRODUCTS_V is a VALID database view owned by the APPS schema within the Oracle E-Business Suite environment. It belongs to the FII (Financial Intelligence) product family and is designated in Oracle's ETRM repository as a Web ADI (Web Applications Desktop Integrator) component. The view exposes a flattened, query-friendly list of enabled product categories derived from the Oracle Inventory category model, presenting each entry as a selectable "product" value suitable for desktop integration layouts and reporting parameter lists.

Within Oracle EBS 12.1.1 and 12.2.2, Web ADI components rely on such views to populate value lists (LOVs) and import/export mappings that users interact with from the desktop. FII_ALL_PRODUCTS_V therefore acts as a denormalized bridge between the hierarchical category tables in Oracle Inventory and the flat key-value structure Web ADI expects. Rather than requiring users to navigate the category set and structure tables directly, the view surfaces a single, ordered list of product identifiers, their concatenated display values, and their descriptions.

The view returns only categories where the ENABLED_FLAG equals 'Y', ensuring inactive or disabled categories are excluded from selection. Results are ordered ascending by PROD_VALUE, which supports consistent display ordering in LOVs and list-based integrations.

Underlying Base Objects

Although the ETRM metadata lists no documented base objects, the view text exposes its actual dependencies. FII_ALL_PRODUCTS_V is defined over two Oracle Inventory objects:

• MTL_CATEGORY_SETS_VL — the language view over the category set definition. It supplies the STRUCTURE_ID associated with the target category set.
• MTL_CATEGORIES_KFV — the key flexfield view over MTL_CATEGORIES, which exposes the CONCATENATED_SEGMENTS column representing the fully rendered category flexfield value.

Two join conditions and one lookup govern the result set. First, the view calls the custom function ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID to obtain the specific category set identifier being targeted. That value is matched against MSV.CATEGORY_SET_ID. Second, MC.STRUCTURE_ID is matched to MSV.STRUCTURE_ID, aligning each category with the structure of its parent set. Third, the ENABLED_FLAG = 'Y' predicate filters out inactive categories. The dependency on ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID is a notable implementation detail: because the category set is resolved at runtime by a function, the view is effectively hard-wired to a specific category set configured through that customization layer.

Key Columns

The view projects exactly three columns, each serving a distinct integration purpose:

• PROD_ID — aliases MC.CATEGORY_ID, the surrogate primary key of the category. This is the value users typically search on (hence the "prod_id" query) and the value bound when a product or category selection is stored. Because the alias renames CATEGORY_ID to PROD_ID, code written against this view must use the PROD_ID column name.
• PROD_VALUE — aliases MC.CONCATENATED_SEGMENTS, the concatenated key flexfield segments that visually identify the category. This is the display value shown in LOVs and rendered in reports.
• DESCRIPTION — the descriptive text of the category, providing human-readable context to complement the coded value.

Common Use Cases and Queries

The view is most commonly queried to resolve a category identifier from a display value, to populate Web ADI parameter lists, or to validate that a product selection still exists and remains enabled. A straightforward retrieval listing all available products follows:

• SELECT PROD_ID, PROD_VALUE, DESCRIPTION FROM APPS.FII_ALL_PRODUCTS_V ORDER BY PROD_VALUE;
• SELECT PROD_ID, PROD_VALUE FROM APPS.FII_ALL_PRODUCTS_V WHERE PROD_VALUE LIKE :search_term;
• SELECT PROD_ID FROM APPS.FII_ALL_PRODUCTS_V WHERE PROD_ID = :p_id;

Because the underlying join is anchored to ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID, all queries implicitly return only categories belonging to the configured category set. Analysts troubleshooting unexpectedly empty results should verify the function's return value and confirm that the target category structure and enabled flags are correct.