Results for “mtl_cross_references”

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

Overview

MTL_CROSS_REFERENCES is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores cross-reference assignments for items. A cross reference is an alternate identifier by which an item is known outside its internal item number—for example, a manufacturer part number, a supplier part number, a customer part number, a competitor part number, or a bar code. The table associates each such identifier with an inventory item within a specific inventory organization, enabling users to search for and transact on items using whatever identifier a trading partner or scanning device supplies. The ETRM metadata notes that the object is "Not implemented in this database," which indicates the extract was taken from an environment where the cross-reference feature was not configured, not that the table is absent from the product schema.

From a modeling perspective, the mined relationship structure suggests treating MTL_CROSS_REFERENCES as a link entity. It resolves the many-to-many association between items (MTL_SYSTEM_ITEMS_B), organizations (MTL_PARAMETERS), and reference types (MTL_CROSS_REFERENCE_TYPES), and its composite primary key is composed entirely of foreign key columns rather than a standalone surrogate identifier.

Key Information Stored

The table's business content is carried by a compact set of columns, all of which participate in the primary key:

• INVENTORY_ITEM_ID — Identifier of the item being cross-referenced. Also a foreign key to MTL_SYSTEM_ITEMS_B, linking back to the master item definition.
• ORGANIZATION_ID — The inventory organization in which the cross reference is valid. Foreign key to MTL_PARAMETERS, providing organization-level security and context.
• CROSS_REFERENCE_TYPE — The classification of the alternate identifier, such as manufacturer, supplier, customer, or bar code. Foreign key to MTL_CROSS_REFERENCE_TYPES, which supplies the descriptive name and validation rules.
• CROSS_REFERENCE — The actual alternate identifier value, stored as alphanumeric text.

The composite primary key MTL_CROSS_REFERENCES_PK spans all four columns (INVENTORY_ITEM_ID, ORGANIZATION_ID, CROSS_REFERENCE_TYPE, CROSS_REFERENCE). No separate surrogate key is documented, so uniqueness is enforced by the business combination itself: a given reference value of a given type may be assigned to an item only once per organization. This makes the four-column tuple the natural business-key candidate for downstream integration and interface design. Additional descriptive columns such as descriptions, status flags, and audit columns (WHO columns) are typically present in the physical table but are outside the documented ETRM extract.

Common Use Cases and Queries

The primary use case is identifier resolution: converting an external identifier into an internal item and organization so that receiving, ordering, shipping, or scanning processes can proceed. Cross references also drive item search in the Inventory and Purchasing user interfaces and are loaded in bulk by interface programs during item conversion or supplier catalog maintenance.

A representative query retrieves all references for a given item:

• SELECT cross_reference_type, cross_reference FROM mtl_cross_references WHERE inventory_item_id = :item_id AND organization_id = :org_id;
• SELECT inventory_item_id, organization_id FROM mtl_cross_references WHERE cross_reference = :scanned_value AND cross_reference_type = :type;

Reporting scenarios include cross-reference exception reports (items lacking a required manufacturer part number), duplicate-reference detection across organizations, and reconciliation of supplier part numbers against purchasing documents. Because CROSS_REFERENCE_TYPE is a foreign key, joins to MTL_CROSS_REFERENCE_TYPES are required whenever the human-readable type name is needed rather than the internal code.

Related Objects

• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID; supplies item number, description, and item attributes for each cross reference.
• MTL_CROSS_REFERENCE_TYPES — joined on CROSS_REFERENCE_TYPE; defines and describes the valid reference type codes.
• MTL_PARAMETERS — joined on ORGANIZATION_ID; confirms the organization context and provides organization-level defaults.
• MTL_ITEM_CROSS_REFERENCES / item cross-reference APIs — the interface layer used to create and maintain rows in this table during bulk loads.
• MTL_SYSTEM_ITEMS_VL and Inventory item search views — reporting views that expose cross-reference information alongside item master data.

Together these relationships position MTL_CROSS_REFERENCES as the operational bridge between internal item identity and the external identifiers used by suppliers, customers, and automated data-capture systems.