Results for “mtl_categories_tl”

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

Overview

MTL_CATEGORIES_TL is the translation table for item categories in the Oracle Inventory (INV) module. It stores the language-specific DESCRIPTION value for each category defined in the base table MTL_CATEGORIES_B. In Oracle EBS 12.1.1 and 12.2.2, EBS supports multi-language (MLS) functionality, and any user-facing descriptive text must be maintained in a "_TL" companion table keyed by LANGUAGE. The base table holds language-independent attributes and structural identity, while MTL_CATEGORIES_TL holds the translatable description that is rendered to a user based on their session language.

Although the object's declared primary key is MTL_CATEGORIES_TL_PK on the combination of CATEGORY_ID and LANGUAGE, the physical schema documented for 12.2.2 also carries a ZD_EDITION_NAME column and a unique index MTL_CATEGORIES_TL_U1 over CATEGORY_ID, LANGUAGE, and ZD_EDITION_NAME. This reflects the Edition-Based Redefinition (EBR) enablement introduced in 12.2.x, where the edition name participates in the uniqueness of translated rows. Heuristically, the mined foreign-key structure classifies this object as satellite-leaning — a modeling suggestion that the table behaves as a dependent, descriptive satellite hanging off the MTL_CATEGORIES_B hub rather than as an independent hub or a link.

Key Information Stored

The most significant columns of MTL_CATEGORIES_TL are:

• CATEGORY_ID — The surrogate foreign key pointing to the parent category in MTL_CATEGORIES_B; part of the declared primary key and the join key to the base table.
• LANGUAGE — The NLS language code identifying which translation this row represents; the other half of the primary key.
• SOURCE_LANG — Indicates the language of the source record, used by the MLS framework to track the origin language for the description.
• DESCRIPTION — The translated, user-facing category description rendered in the corresponding LANGUAGE. This is the column whose translation is the whole purpose of the table.
• ZD_EDITION_NAME — The EBR edition identifier present in 12.2.2, which scopes uniqueness and allows online redefinition without conflicting with prior editions.

The business-key candidate is captured by the unique index MTL_CATEGORIES_TL_U1 (CATEGORY_ID, LANGUAGE, ZD_EDITION_NAME), distinguishing the true business identity of a row from the surrogate-style PK definition. Standard WHO audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — complete the ten documented columns and provide standard change tracking.

Common Use Cases and Queries

Typical usage involves joining the translation table to its base table to retrieve a display-ready category name for a specified language. A representative query follows:

• Display one language: SELECT b.category_id, t.description FROM mtl_categories_b b, mtl_categories_tl t WHERE b.category_id = t.category_id AND t.language = USERENV('LANG').
• All translations for a category: filter on a specific CATEGORY_ID and omit the language predicate to list every language row.
• Reporting: category performance, item assignment, and inventory valuation reports frequently join MTL_CATEGORY_SETS, MTL_CATEGORIES_B, and MTL_CATEGORIES_TL to substitute the translatable description for the numeric category identifier.
• EBR-aware access: in 12.2.2, queries run through editioning views so the correct edition row is resolved automatically; direct base-table access should still respect ZD_EDITION_NAME where relevant.

Related Objects

The following objects are most significant to MTL_CATEGORIES_TL:

• MTL_CATEGORIES_B — The parent base table; joined on MTL_CATEGORIES_TL.CATEGORY_ID = MTL_CATEGORIES_B.CATEGORY_ID. This is the only documented foreign-key relationship.
• MTL_CATEGORY_SETS — Defines category sets and links categories into structured groups, providing the broader context in which category descriptions are displayed.
• MTL_CATEGORY_SETS_V — A common view exposing category set and category data, which in turn resolves descriptions through the translation layer.
• MTL_ITEM_CATEGORIES — Associates items with categories; reporting tools frequently traverse this table to reach MTL_CATEGORIES_TL for descriptive output.
• MTL_CATEGORIES_VL — The MLS view that combines the base and translation tables, and is the canonical interface through which applications read translated category descriptions.

Together these objects form the category definition and translation stack through which Oracle Inventory presents human-readable category names across multiple languages.