Search Results mtl_cross_reference_types

Overview

The MTL_CROSS_REFERENCE_TYPES table is a core reference data object within the Oracle E-Business Suite Inventory (INV) module. Its primary role is to define and store the master list of valid cross-reference type codes that can be used to categorize relationships between different item identifiers. These types are essential for establishing and managing alternative item numbers, such as customer part numbers, supplier part numbers, or legacy codes, which are stored in the related MTL_CROSS_REFERENCES table. By centralizing these type definitions, the table enforces data integrity and provides a consistent framework for item identification across the supply chain, order management, and procurement processes in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to catalog discrete cross-reference types. The most critical column is CROSS_REFERENCE_TYPE, which serves as the table's primary key. This column stores the unique, user-defined code that identifies the nature of the item identifier relationship (e.g., 'CUSTOMER', 'SUPPLIER'). While the provided ETRM metadata does not list all columns, typical implementations include descriptive columns like DESCRIPTION to provide a meaningful name for the type code. The primary key constraint, MTL_CROSS_REFERENCE_TYPES_PK, ensures the uniqueness and mandatory nature of the CROSS_REFERENCE_TYPE value, making it a reliable lookup source for other applications.

Common Use Cases and Queries

This table is primarily used for validation and reporting on item identifier relationships. A common operational use case is populating a list of values (LOV) in forms where users must select a cross-reference type when entering or querying an alternative item number. For reporting, it is frequently joined to the MTL_CROSS_REFERENCES table to translate type codes into human-readable descriptions. A standard query pattern is:

• SELECT cr.cross_reference, crx.cross_reference_type, crt.description FROM mtl_cross_references cr, mtl_cross_reference_types crt WHERE cr.cross_reference_type = crt.cross_reference_type AND cr.inventory_item_id = :item_id;

This retrieves all alternate identifiers for a given item along with their type descriptions. System administrators also query this table to audit or maintain the list of active cross-reference types in the enterprise.

Related Objects

MTL_CROSS_REFERENCE_TYPES has defined foreign key relationships with other critical EBS tables, as documented in the metadata. The primary relationship is with the MTL_CROSS_REFERENCES table, where the CROSS_REFERENCE_TYPE column is a foreign key referencing this table. This ensures every cross-reference record is assigned a valid, predefined type. A significant integration point is with the Order Management (OE) module. The OE_ORDER_LINES_ALL.ITEM_IDENTIFIER_TYPE column is also a foreign key to this table, linking order line item identifiers directly to the centralized inventory type definitions. This integration allows order entry to utilize alternative item identifiers validated against the inventory master list.