Results for “ecx_xref_dtl_vl”

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

Overview

ECX_XREF_DTL_VL is a bilingual (MLS) view owned by the APPS schema in Oracle E-Business Suite, belonging to the ECX — XML Gateway product. Its documented role is "Code conversion Trading Partner detail." It exposes the translation records that XML Gateway uses to map an external (trading partner) code value to an internal (Oracle) code value, scoped by cross-reference category, standard, and trading partner header. The view consolidates the transactional detail rows from ECX_XREF_DTL_B with their language-specific description from ECX_XREF_DTL_TL, filtered to the session language via USERENV('LANG').

The column most frequently sought by users — XREF_EXT_VALUE — is the external code value carried in an inbound or outbound XML message. It is paired with XREF_INT_VALUE, the corresponding internal value used inside Oracle EBS. Together these two columns define the inbound and outbound conversion behavior of the gateway. The view is a query-only dictionary object of type VIEW with STATUS VALID, and it is documented in ETRM for both 12.1.1 and 12.2.2 with the same definition.

Underlying Base Objects

The documented base objects referenced by the view are two synonyms: ECX_XREF_DTL_B and ECX_XREF_DTL_TL. The view text joins them on XREF_DTL_ID, with the TL (translation) alias supplying DESCRIPTION and the B (base) alias supplying all other columns, restricted by T.LANGUAGE = USERENV('LANG'). This is the standard MLS pattern: the _B table holds non-translatable attributes and the _TL table holds language-dependent description text. The entity that ultimately anchors the detail rows is the cross-reference header (referenced through TP_HEADER_ID), which in turn is associated with an XREF_CATEGORY_ID and a STANDARD_ID. Because the join is an inner join on XREF_DTL_ID plus the language predicate, a detail row whose translation for the session language is missing will not appear in the view.

Key Columns

• ROW_ID — ROWID of the base row in ECX_XREF_DTL_B; used as a surrogate row locator.
• XREF_DTL_ID — Primary key of the cross-reference detail, and the join key between the _B and _TL tables.
• XREF_CATEGORY_ID — Identifier of the cross-reference category that groups related conversion rules.
• STANDARD_ID / XREF_STANDARD_CODE — The standard (document/message standard) against which the mapping is defined.
• TP_HEADER_ID — Trading partner header that owns the cross-reference definition, tying the rule to a specific partner.
• XREF_EXT_VALUE — External code value as it appears in the trading partner's XML payload.
• XREF_INT_VALUE — Internal value used by the Oracle application after conversion.
• DIRECTION — Indicates whether the conversion applies to inbound, outbound, or both directions.
• DESCRIPTION — Language-specific description from ECX_XREF_DTL_TL.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include tracing why an inbound value was converted to a particular internal code, auditing the completeness of a trading partner's cross-reference setup, and validating direction-specific mappings before enabling an interface.

Look up a specific external value:

• SELECT xref_ext_value, xref_int_value, direction, tp_header_id, description FROM ecx_xref_dtl_vl WHERE xref_ext_value = :p_val;
• SELECT xref_ext_value, xref_int_value, direction, standard_id FROM ecx_xref_dtl_vl WHERE xref_category_id = :p_cat ORDER BY xref_ext_value;
• SELECT tp_header_id, COUNT(*) FROM ecx_xref_dtl_vl GROUP BY tp_header_id;

Because the view enforces the MLS language filter, queries should be run in the intended session language or against the _B/_TL tables directly when all translations must be inspected regardless of LANGUAGE.