Results for “ams_mtl_categories_denorm_tl”

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

Overview

AMS_MTL_CATEGORIES_DENORM_TL is a denormalized translation table owned by the AMS (Marketing) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores inventory category information in a pre-joined, flattened form, retaining both a concatenated description and concatenated category identifiers alongside the standard multilingual (TL) language columns. The denormalized design exists to support Oracle Marketing segmentation and targeting operations, where category hierarchies must be traversed and displayed rapidly without repeatedly resolving multi-level parent-child relationships across the base inventory category tables. By storing the concatenated rollup of category descriptions and IDs, the table eliminates expensive recursive joins during list generation, targeting query execution, and campaign audience selection.

The ETRM relationship metadata classifies this object heuristically as standalone from a Data Vault modeling perspective — it does not function as a conventional hub, link, or satellite. Where a Data Vault model would normally separate a business key hub (the category) from descriptive satellites, this table deliberately collapses category identity, translated description text, and audit attributes into one structure. In Data Vault terms it is best treated as a denormalized satellite-like convenience object that should be sourced from a proper hub and satellite pair rather than used as the system of record.

Key Information Stored

The table contains ten documented columns. The surrogate composite primary key is defined by the constraint AMS_MTL_CATEGORY_DENORM_TL_PK over (CATEGORY_ID, LANGUAGE), reflecting the standard Oracle translation-table pattern in which CATEGORY_ID identifies the base inventory category and LANGUAGE identifies the language-specific row.

• CATEGORY_ID — Identifier of the inventory category being denormalized; part of the composite primary key.
• LANGUAGE — Language code for the translated row; the second component of the primary key and the leading column of the unique index AMS_MTL_CATEGORY_DENORM_TL_U1.
• SOURCE_LANG — The source language of the translated description, consistent with the _TL convention used throughout EBS.
• CONCATENATED_DESCRIPTION — The flattened, pre-joined category description representing the concatenated hierarchy path; this is the primary business payload of the table.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, providing row-level security visibility control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns recording who created and last modified each row and when.

The unique index AMS_MTL_CATEGORY_DENORM_TL_U1 (LANGUAGE, CATEGORY_ID) is functionally equivalent to the primary key in reverse column order and serves as an alternate business-key access path.

Common Use Cases and Queries

The dominant use case is resolving a fully qualified, concatenated category description for targeting and segmentation UI screens, avoiding recursive traversal of MTL_CATEGORIES and MTL_CATEGORY_SETS. Typical queries filter by language and join on CATEGORY_ID.

• Retrieve the concatenated description for a specific category in a given language:
  SELECT CONCATENATED_DESCRIPTION FROM AMS.AMS_MTL_CATEGORIES_DENORM_TL WHERE CATEGORY_ID = :p_category_id AND LANGUAGE = USERENV('LANG');
• Bulk lookup for a set of categories in a campaign audience build:
  SELECT CATEGORY_ID, CONCATENATED_DESCRIPTION FROM AMS.AMS_MTL_CATEGORIES_DENORM_TL WHERE LANGUAGE = 'US' AND CATEGORY_ID IN (...);
• Security-scoped reporting joined to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to respect row-level access policies.
• Change-detection reporting using LAST_UPDATE_DATE to extract incrementally refreshed category translations for downstream data warehouse loads.

Related Objects

• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID; controls which rows are visible based on the operating unit security model.
• MTL_CATEGORIES — Base inventory category definitions; the ultimate source of CATEGORY_ID values.
• MTL_CATEGORIES_TL — Translated category names that feed the concatenation logic.
• MTL_CATEGORY_SETS — Category set definitions governing category structures available for targeting.
• FND_LANGUAGES — Provides valid values for the LANGUAGE column.
• AMS_MTL_CATEGORIES_DENORM_TL_PK / AMS_MTL_CATEGORY_DENORM_TL_U1 — Primary key and unique index enforcing uniqueness on category and language.