Results for “mtl_cross_references_vl”

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

Overview

MTL_CROSS_REFERENCES_VL is a seeded, VALID view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the INV (Inventory) product family and functions as the language-enabled (MLS) presentation layer for item cross-reference data. The view merges the non-translated base table MTL_CROSS_REFERENCES_B with its translation table MTL_CROSS_REFERENCES_TL, returning only the translation row whose LANGUAGE matches the session's USERENV('LANG') setting. Because it is a _VL (view, language) construct rather than a DML-capable _V view, it is intended strictly for query, reporting, and integration read access — not for direct inserts or updates. For report authors, integrations, and concurrent programs that must display cross-reference definitions in the end user's own language, this view eliminates the need to write explicit translation joins or filter by language, providing a single, self-filtering source.

Underlying Base Objects

Per documented ETRM metadata, the view is defined over two synonyms resolving to the following tables:

• MTL_CROSS_REFERENCES_B — the base (non-translated) table holding the cross-reference identifier and all descriptive attributes.
• MTL_CROSS_REFERENCES_TL — the translation table supplying the DESCRIPTION column per language.

The view text joins the two on CROSS_REFERENCE_ID and restricts MTL_CROSS_REFERENCES_TL.LANGUAGE to USERENV('LANG'). All non-translated columns are sourced from the BASE alias; only DESCRIPTION is drawn from the TL alias. The view is defined with the base table's ROWID exposed as ROW_ID, so row-level identification is preserved for downstream processing.

Key Columns

• CROSS_REFERENCE_ID — unique primary key of the cross-reference; the join key to MTL_CROSS_REFERENCES_TL.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — the item and the inventory organization to which the cross-reference applies. ORGANIZATION_ID may be null where ORG_INDEPENDENT_FLAG is set.
• CROSS_REFERENCE_TYPE — classifies the reference (for example, manufacturer part number, supplier part number, or internal code).
• CROSS_REFERENCE — the actual reference value, such as an external part number.
• DESCRIPTION — the language-specific descriptive text retrieved from the TL table.
• ORG_INDEPENDENT_FLAG — indicates whether the cross-reference is defined across organizations rather than tied to one.
• UOM_CODE, REVISION_ID, EPC_GTIN_SERIAL, SOURCE_SYSTEM_ID — supporting attributes for unit of measure, revision, serialized/GTIN identifiers, and originating system.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating for the cross-reference.
• OBJECT_VERSION_NUMBER — optimistic locking version for the base row.
• LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, and ATTRIBUTE1–15 — standard WHO/audit and descriptive-flex columns.

Common Use Cases and Queries

Typical scenarios include cross-reference inquiry forms and reports, item data extracts feeding downstream PIM or ERP systems, and validation queries during item import. The view's automatic language filtering makes it the preferred source whenever descriptions must be presented in the user's session language.

List active manufacturer part numbers for an item:

• SELECT cross_reference_type, cross_reference, description, uom_code FROM mtl_cross_references_vl WHERE inventory_item_id = :item_id AND organization_id = :org_id AND (end_date_active IS NULL OR end_date_active >= SYSDATE);

Locate an item by an external reference value:

• SELECT inventory_item_id, organization_id, cross_reference_type FROM mtl_cross_references_vl WHERE cross_reference = :value;

Because the view depends on USERENV('LANG'), results vary with the session language. When a report requires a fixed language regardless of session settings, query MTL_CROSS_REFERENCES_TL directly and filter LANGUAGE explicitly.