Results for “mtl_cross_reference_types_u1”

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

Overview

INV.MTL_CROSS_REFERENCE_TYPES is a foundational Inventory (INV) reference table in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the set of cross-reference types that provide context when an inventory item is mapped to an alternative identification system. Typical contexts include legacy item numbering schemes, supplier or manufacturer part numbers, competitor part numbers, and customer-defined item identifiers. Because the cross-reference type supplies the semantic meaning of the mapping, an item may carry cross-references across any number of defined types without ambiguity.

The table functions as a small, low-volume lookup/master object. In Data Vault modeling terms, its heuristic classification is hub-leaning: CROSS_REFERENCE_TYPE serves as the natural business key and the table carries descriptive attributes directly. In a formal Data Vault design, this object is best represented as a hub (business key: CROSS_REFERENCE_TYPE) with descriptive attributes such as DESCRIPTION and DISABLE_DATE optionally split into an adjacent satellite. The metadata records the object status as VALID, with FND Design Data registered as INV.MTL_CROSS_REFERENCE_TYPES.

Key Information Stored

The primary key of the table is MTL_CROSS_REFERENCE_TYPES_PK, defined on the single column CROSS_REFERENCE_TYPE. A unique index, MTL_CROSS_REFERENCE_TYPES_U1, is documented on the business-key candidate columns (CROSS_REFERENCE_TYPE, ZD_EDITION_NAME) in the 12.2.2 physical schema, reflecting the edition-based redefinition support introduced in Release 12.2. The most operationally significant columns are:

• CROSS_REFERENCE_TYPE (VARCHAR2, 25, mandatory) — the business key and primary key column; the short code that names the cross-reference type.
• DESCRIPTION (VARCHAR2, 240) — human-readable description of the cross-reference type, commonly surfaced in list-of-values and reporting output.
• DISABLE_DATE (DATE) — the date after which the cross-reference type may no longer be assigned. Null indicates the type remains active.
• VALIDATE_FLAG (VARCHAR2) — documented as not currently used; retained for backward compatibility.
• ATTRIBUTE_CATEGORY (VARCHAR2, 30) — the descriptive flexfield structure definition column.
• ATTRIBUTE1 through ATTRIBUTE15 (VARCHAR2, 150) — descriptive flexfield segments exposed to the DFF framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Who columns present on all EBS transactional and setup tables.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent request audit columns identifying the program that last modified the row.
• ZD_EDITION_NAME — editioning column that supports edition-based redefinition in EBS 12.2.

Common Use Cases and Queries

The most common use case is retrieving the active set of cross-reference types for a list of values or a setup verification report. The following pattern returns all types that have not been disabled:

SELECT cross_reference_type, description
FROM inv.mtl_cross_reference_types
WHERE disable_date IS NULL
ORDER BY cross_reference_type;

Another frequent requirement is joining the type definition to the actual item cross-references stored in MTL_CROSS_REFERENCES to produce a denormalized view of item identifiers:

SELECT mcr.inventory_item_id, mcr.cross_reference_type,
mct.description, mcr.cross_reference
FROM inv.mtl_cross_references mcr,
inv.mtl_cross_reference_types mct
WHERE mcr.cross_reference_type = mct.cross_reference_type;

Setup administrators also audit for orphaned or discontinued types by comparing the DISABLE_DATE with the number of dependent rows in MTL_CROSS_REFERENCES, and reporting teams use the DESCRIPTION column as the display label in item master extracts. Because the table is small, queries against it are inexpensive and it is frequently introduced into larger item master query blocks as a lookup join.

Related Objects

• INV.MTL_CROSS_REFERENCES — the child table that stores each item-to-identifier mapping. The join column is CROSS_REFERENCE_TYPE, the foreign key from MTL_CROSS_REFERENCES to this table.
• OE_ORDER_LINES_ALL — the order-management line table references the type through its ITEM_IDENTIFIER_TYPE column, used to qualify item identifier values supplied on order lines.
• MTL_SYSTEM_ITEMS_B — the item master, joined indirectly through MTL_CROSS_REFERENCES and INV_ITEM_ID to obtain item detail alongside the cross-reference type.
• FND_DESCRIPTIVE_FLEXS — the descriptive flexfield metadata table that governs the ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 columns.
• FND_FLEX_VALUES / FND_FLEX_VALUES_TL — flexfield value tables used when validating DFF segments populated on this setup table.