Results for “mtl_cross_references_int_pk”

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

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:

• INVENTORY_ITEM_ID and ORGANIZATION_ID — the item and inventory organization to which the cross-reference applies.
• CROSS_REFERENCE_TYPE and CROSS_REFERENCE — the type of alternate identifier (for example, MANUFACTURER_PART_NUMBER) and its literal value.
• ORG_INDEPENDENT_FLAG — indicates whether the cross-reference is organization-independent or organization-specific.
• DESCRIPTION — free-text description of the cross-reference.
• PROCESS_FLAG — the interface processing status column, typically set by the feeder program to signal pending, processed, or errored records.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program tracking columns used by the standard interface pattern.
• TRANSACTION_ID and TRANSACTION_TYPE — designate the source transaction that submitted the cross-reference row.
• ITEM_NUMBER and ORGANIZATION_CODE — denormalized business keys that allow the feeder to populate the table without resolving internal IDs.
• UOM_CODE, REVISION_ID, and REVISION — unit of measure and revision context for the cross-reference.
• CROSS_REFERENCE_ID and SET_PROCESS_ID — identifiers supporting conversion into the destination table.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — the standard EBS descriptive flexfield columns.

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:

• MTL_CROSS_REFERENCES — the destination table, joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, CROSS_REFERENCE_TYPE, and CROSS_REFERENCE.
• MTL_SYSTEM_ITEMS_B — the item master, joined on INVENTORY_ITEM_ID.
• MTL_PARAMETERS — the organization definition, joined on ORGANIZATION_ID.
• MTL_UNITS_OF_MEASURE — referenced through UOM_CODE.
• MTL_ITEM_REVISIONS_B — referenced through REVISION_ID.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID for batch tracking.
• FND_APPLICATION and FND_CONCURRENT_PROGRAMS — resolve PROGRAM_APPLICATION_ID and PROGRAM_ID.