Search Results mtl_descr_element_values_n1

Overview

INV.MTL_DESCR_ELEMENT_VALUES is a transactional detail table in the Oracle E-Business Suite Inventory (INV) schema. It stores the descriptive element values associated with a specific item. When an item is assigned to a particular item catalog group, the application inserts one row per descriptive element defined for that catalog group, then updates each row with the descriptive element values the user supplies for the item. Descriptive element values are defined once at the item master organization, and because an item may belong to only one item catalog group, it holds descriptive elements for a single catalog group only.

The table plays a central role in item catalog maintenance, descriptive-flexfield-driven search, and catalog description generation. Based on the heuristic Data Vault classification mined from its foreign-key structure, this object is best modeled as a standalone structure rather than a hub, link, or satellite, reflecting its lack of documented foreign-key dependencies within the provided metadata. It is a business-key-driven child of the item master, keyed on item and element name rather than on a generated surrogate identifier.

Key Information Stored

The documented physical schema contains fourteen columns. The most significant are:

• INVENTORY_ITEM_ID — Inventory item identifier; identifies the item to which the descriptive element value belongs.
• ELEMENT_NAME — Descriptive element name (VARCHAR2(30)); the name of the descriptive element for the catalog group.
• ELEMENT_VALUE — Descriptive element value (VARCHAR2(30)); the actual value assigned to the element for the item.
• DEFAULT_ELEMENT_FLAG — Flag indicating whether the descriptive element value is to be included in the item catalog description for this item. It defaults from MTL_DESCRIPTIVE_ELEMENTS but may be changed when the item is assigned to the catalog group.
• ELEMENT_SEQUENCE — Sequence value governing the ordering of the descriptive element.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns tracking creation and change audit information.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program columns recording the request that last touched the row.

Two indexes are documented. MTL_DESCR_ELEMENT_VALUES_U1 is a unique index on (INVENTORY_ITEM_ID, ELEMENT_NAME), making this pair the business-key candidate; it corresponds to the primary key MTL_DESCR_ELEMENT_VALUES_PK (INVENTORY_ITEM_ID, ELEMENT_NAME). MTL_DESCR_ELEMENT_VALUES_N1 is a non-unique index on (ELEMENT_NAME, ELEMENT_VALUE), which supports value-based lookup and search. Both indexes reside in the APPS_TS_TX_IDX tablespace, while the table itself resides in APPS_TS_TX_DATA with PCT FREE 10.

Common Use Cases and Queries

Typical uses include reporting on catalog descriptive values by item, retrieving element values for a given element name, and validating the completeness of catalog descriptions. The unique index MTL_DESCR_ELEMENT_VALUES_U1 is the primary access path for single-item lookups. The ELEMENT_NAME/ELEMENT_VALUE index supports queries such as:

• Retrieving all descriptive values for one item: filter on INVENTORY_ITEM_ID.
• Finding all items sharing an element value: filter on ELEMENT_NAME and ELEMENT_VALUE using the non-unique index.
• Reporting on elements flagged for inclusion in the catalog description: filter where DEFAULT_ELEMENT_FLAG indicates inclusion.
• Auditing changes by element: use LAST_UPDATE_DATE and LAST_UPDATED_BY, joined to the item master for context.

Related Objects

The table references and is referenced by several core Inventory objects, joined on the documented keys:

• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID to resolve item number, description, and organization context.
• MTL_DESCRIPTIVE_ELEMENTS — source of the default for DEFAULT_ELEMENT_FLAG; joined on ELEMENT_NAME.
• MTL_ITEM_CATALOG_GROUPS — defines the catalog group that determines which descriptive elements apply to an item.
• MTL_CATEGORIES_B / MTL_ITEM_CATEGORIES — catalog and category assignments that drive element definitions.
• MTL_DESCR_ELEMENT_VALUES public views or interface programs in the Inventory descriptive flexfield and item catalog setup flows.