Search Results mtl_cross_references_vl




Overview
Explore in AI Chat

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

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.