Results for “mtl_cross_reference_types”

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

Overview

MTL_CROSS_REFERENCE_TYPES is an Oracle Inventory (INV) reference table that defines the valid cross reference types used throughout Oracle E-Business Suite. Each row establishes a classification that describes the kind of alternate identifier assigned to an item — for example a manufacturer part number, a customer item number, a supplier part number, or a barcode. The table functions as a controlled vocabulary: rather than permitting free-form identifier labels, the application requires every cross reference record in MTL_CROSS_REFERENCES to carry a CROSS_REFERENCE_TYPE value that already exists here.

The object is owned by the INV schema and is available in both Oracle EBS 12.1.1 and 12.2.2. Its documented status is VALID, and the physical schema in 12.2.2 exposes 30 columns. Under the heuristic Data Vault classification mined from the foreign key structure, MTL_CROSS_REFERENCE_TYPES is hub-leaning: it holds a stable, unique business key (CROSS_REFERENCE_TYPE) that is referenced by multiple dependent tables, making it a natural hub candidate in a dimensional or Data Vault style model.

Key Information Stored

The primary key is defined by the constraint MTL_CROSS_REFERENCE_TYPES_PK on the single column CROSS_REFERENCE_TYPE. Because the key is the type code itself, the surrogate-versus-business-key distinction collapses here: CROSS_REFERENCE_TYPE serves as both the primary key and the business identifier. A unique index, MTL_CROSS_REFERENCE_TYPES_U1 on (CROSS_REFERENCE_TYPE, ZD_EDITION_NAME), reflects the 12.2.2 editioning mechanism. The most significant columns include:

• CROSS_REFERENCE_TYPE — the primary key and the code identifying the type of cross reference.
• DESCRIPTION — the user-facing name or explanation of the cross reference type.
• DISABLE_DATE — the date on which the type becomes inactive and unavailable for new assignments.
• VALIDATE_FLAG — controls whether the application validates cross references of this type during entry or processing.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns that allow customers to extend the type definition without customization.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard who-columns used for auditing and change tracking.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context identifying the process that last modified the row.
• ZD_EDITION_NAME — the editioning column introduced in the 12.2 online patching architecture.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include listing all active cross reference types, identifying disabled types, and joining type definitions to the actual cross references captured against items. A common query retrieves active types:

• SELECT cross_reference_type, description FROM mtl_cross_reference_types WHERE disable_date IS NULL OR disable_date > SYSDATE;
• SELECT cr.cross_reference_type, t.description, cr.cross_reference, cr.inventory_item_id FROM mtl_cross_references cr JOIN mtl_cross_reference_types t ON cr.cross_reference_type = t.cross_reference_type;
• Reports that map customer item numbers or manufacturer part numbers to internal inventory items depend on this table to resolve the meaning of the stored type code.
• Integration and interface loads verify that an incoming cross reference type exists here before inserting into MTL_CROSS_REFERENCES.

Related Objects

The documented foreign key relationships identify the principal dependent objects:

• MTL_CROSS_REFERENCES — the primary dependent table; its CROSS_REFERENCE_TYPE column references MTL_CROSS_REFERENCE_TYPES.CROSS_REFERENCE_TYPE, storing the actual alternate identifiers for items.
• OE_ORDER_LINES_ALL — Order Management order lines reference this table through the ITEM_IDENTIFIER_TYPE column, allowing sales order lines to capture customer or manufacturer item identifiers.
• MTL_SYSTEM_ITEMS_B / MTL_SYSTEM_ITEMS_TL — the master item tables whose items are the subjects of cross references defined against these types.
• INV_ITEM_CROSS_REFERENCES_PUB / item cross reference APIs — the public programmatic interfaces that validate and create cross reference data using the type definitions held here.
• Descriptive flexfield definition tables such as FND_DESCR_FLEX_COL_USAGE, which govern the ATTRIBUTE columns in this table.

Together these relationships position MTL_CROSS_REFERENCE_TYPES as a compact but structurally central hub within the Inventory cross reference model.