Search Results mtl_cross_references_interface




Overview
Explore in AI Chat

Overview

MTL_CROSS_REFERENCES_INTERFACE is an Oracle Inventory (INV) staging table that serves as the open-interface mechanism for creating and updating cross-reference relationships between inventory items and alternate identifiers such as manufacturer part numbers, customer part numbers, competitor part numbers, and substitute items. Cross-references allow an item to be located by identifiers other than its internal item number, which is essential for procurement, order entry, and receiving processes where external trading partners use their own numbering conventions.

In Oracle EBS 12.1.1 and 12.2.2, the table is owned by the INV schema and is documented in ETRM as "not currently used," meaning Oracle does not ship seeded concurrent programs that read or write to it in the standard flow. It remains a valid, available object for custom integration use. The heuristic Data Vault classification mined from the foreign-key structure is standalone, with no parent link to other tables. In modeling terms this suggests the table behaves as an independent staging entity rather than a hub, link, or satellite within a normalized Data Vault design.

Key Information Stored

The physical schema in 12.2.2 documents 44 columns. The most operationally significant are:

The documented primary key is the composite MTL_CROSS_REFERENCES_INT_PK on INVENTORY_ITEM_ID, ORGANIZATION_ID, CROSS_REFERENCE_TYPE, and CROSS_REFERENCE. These four columns are therefore the business-key candidates for unique identification; no separate surrogate column is documented.

Common Use Cases and Queries

The primary intended use is bulk loading cross-references from an external system. A typical pattern populates the interface table with the business keys (ITEM_NUMBER, ORGANIZATION_CODE, CROSS_REFERENCE_TYPE, CROSS_REFERENCE), sets PROCESS_FLAG, and a custom concurrent program validates and inserts into MTL_CROSS_REFERENCES. Diagnostic queries commonly inspect rows still awaiting processing:

  • SELECT * FROM mtl_cross_references_interface WHERE process_flag IS NULL OR process_flag = 'PENDING';
  • Joining the interface to the destination for reconciliation: SELECT i.item_number, i.cross_reference_type, i.cross_reference FROM mtl_cross_references_interface i LEFT JOIN mtl_cross_references c ON i.inventory_item_id = c.inventory_item_id AND i.organization_id = c.organization_id AND i.cross_reference_type = c.cross_reference_type AND i.cross_reference = c.cross_reference WHERE c.cross_reference IS NULL;
  • Reporting by type to measure alternate-identifier coverage per organization, using CROSS_REFERENCE_TYPE as the grouping dimension.
  • Auditing load batches by REQUEST_ID, PROGRAM_ID, and TRANSACTION_ID.

Related Objects

The most significant related objects follow the interface-to-base-table relationship — no documented foreign keys exist, so joins are by business key: