Results for “ams_mtl_categories_denorm_vl”

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

Overview

AMS_MTL_CATEGORIES_DENORM_VL is a VALID database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its documented purpose is to provide a description for each segment of an inventory category, concatenated together into a single denormalized string. In practice, this view exposes human-readable, concatenated category descriptions alongside the underlying numeric category identifiers, allowing marketing and Oracle Inventory category structures to be reported on without manually joining the individual category segment tables.

Because the object is a view rather than a table, it contains no stored data of its own; it is a query definition that resolves at runtime. The "_VL" suffix follows the standard EBS convention indicating a view that joins a language-independent base table with its corresponding translated ("_TL") table, filtered by the session language. This makes the view suitable for multilingual reporting and integration scenarios where category names must be presented in the user's active language. It occupies the same functional space as the MTL_CATEGORIES / MTL_CATEGORY_SETS base tables in Oracle Inventory, but is denormalized specifically for the AMS Marketing data model.

Underlying Base Objects

The view is defined over two documented base objects, both referenced as synonyms:

• AMS_MTL_CATEGORIES_DENORM_B — the language-independent ("base") object that holds the category identity and structure information.
• AMS_MTL_CATEGORIES_DENORM_TL — the translation object that holds the language-specific concatenated description.

The documented view text joins these two objects on CATEGORY_ID, then applies the language filter T.LANGUAGE = USERENV('LANG'). The join therefore returns exactly one description per category for the current session language, guaranteeing that the CONCATENATED_DESCRIPTION reflects the logged-in user's language environment. The translation object is rendered with the "VL" (view language) semantic that EBS uses throughout its schema for MLS-enabled objects.

Key Columns

• ROW_ID — the ROWID of the base row (aliased as ROW_ID), a unique physical row locator used for direct addressing or locking.
• CATEGORY_ID — the identifier of the inventory category; the primary join key between the base table and the translation table.
• STRUCTURE_ID — identifies the category structure (category set) to which the category belongs.
• CONCATENATED_IDS — the concatenated segment/category identifiers, i.e., the numeric denormalized category path.
• CONCATENATED_DESCRIPTION — the concatenated, language-specific description of each segment of the inventory category, resolved for the session language. This is the value most users search for.

Common Use Cases and Queries

The view is typically used to obtain a readable, denormalized category description for a given category or structure without traversing each segment individually. A straightforward lookup by category is:

SELECT category_id, structure_id, concatenated_ids, concatenated_description
FROM   apps.ams_mtl_categories_denorm_vl
WHERE  category_id = :p_category_id;

To list all categories within a particular structure:

SELECT category_id, concatenated_description
FROM   apps.ams_mtl_categories_denorm_vl
WHERE  structure_id = :p_structure_id
ORDER  BY concatenated_description;

Because the view is language-filtered, no additional WHERE clause on language is required; setting the session language via USERENV('LANG') automatically returns the correct translated description. Typical applications include marketing reporting, integration extracts that must carry descriptive category labels, and ad-hoc queries resolving a category ID to a meaningful label. Note that the denormalized descriptions reflect the AMS naming scheme and should not be conflated with the standard Inventory MTL_CATEGORIES tables.