Search Results xref_key

Overview

EC.ECE_XREF_DATA is a code conversion (cross-reference) table in the Oracle E-Business Suite EDI/XML Gateway schema (EC). It stores the mapping rules that translate between Oracle internal values and the external values exchanged with trading partners across inbound and outbound transactions. Each row represents one unique internal-to-external relationship, qualified by a category and up to five search keys. The table is referenced extensively by the e-Commerce Gateway during outbound document generation and inbound document processing.

From a Data Vault modeling perspective, the mined FK structure classifies ECE_XREF_DATA as satellite-leaning: it carries descriptive and directional attributes anchored to a parent category key (XREF_CATEGORY_ID) rather than acting as a pure hub or link. This classification is a heuristic suggestion only; the table predates Data Vault conventions and uses a natural surrogate key design.

Key Information Stored

XREF_DATA_ID is the system-generated surrogate primary key (backed by unique index ECE_XREF_DATA_U1); it is invisible to end users. The documented physical schema defines 25 columns, of which the following are the most operationally significant:

• XREF_DATA_ID — Surrogate primary key; sole unique index column, no business meaning.
• XREF_CATEGORY_ID — Foreign key to ECE_XREF_CATEGORIES; the principal business-key candidate for partitioning conversion rules (indexed by ECE_XREF_DATA_N1).
• XREF_CATEGORY_CODE — Denormalized category identifier used in rule resolution.
• DIRECTION — Indicates whether the row applies to inbound or outbound conversion.
• XREF_INT_VALUE — The Oracle internal value; the source for outbound conversion and the target for inbound conversion.
• XREF_EXT_VALUE1 through XREF_EXT_VALUE5 — Up to five external values derived from (outbound) or received to produce (inbound) the internal value.
• XREF_KEY1 through XREF_KEY5 — Up to five search keys applied to select the correct external or internal value during conversion.
• DESCRIPTION — Free-text label for the conversion record.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — Standard WHO audit columns.
• PROGRAM_ID, PROGRAM_APPLICATION_ID, REQUEST_ID, PROGRAM_UPDATE_DATE — Concurrent-program execution context.

Common Use Cases and Queries

Typical scenarios include validating that every internal value has a defined external equivalent, auditing conversion rules per category, and troubleshooting failed EDI transactions where the cross-reference is missing or ambiguous.

• Listing all conversion values for a category:

  SELECT XREF_INT_VALUE, XREF_EXT_VALUE1, XREF_KEY1
  FROM   EC.ECE_XREF_DATA
  WHERE  XREF_CATEGORY_ID = :p_category_id
  AND    DIRECTION = 'OUTBOUND';

• Reverse lookup for inbound processing by external value and search key:

  SELECT XREF_INT_VALUE
  FROM   EC.ECE_XREF_DATA
  WHERE  XREF_CATEGORY_CODE = :p_code
  AND    XREF_EXT_VALUE1 = :p_ext_value
  AND    XREF_KEY1 = :p_key1;

• Identifying orphaned or duplicate mappings using the non-unique index columns (XREF_CATEGORY_ID, XREF_DATA_ID, XREF_KEY1).
• Reporting categories lacking required external values for go-live readiness.

Related Objects

The FK metadata identifies the following dependency chain. Additional EC.x gateway objects are commonly referenced during transaction processing.

• EC.ECE_XREF_CATEGORIES — Parent table; join on ECE_XREF_DATA.XREF_CATEGORY_ID = ECE_XREF_CATEGORIES.XREF_CATEGORY_ID (one-to-many).
• EC.ECE_XREF_DATA indexes — ECE_XREF_DATA_U1 (XREF_DATA_ID), ECE_XREF_DATA_N1 (XREF_CATEGORY_ID, XREF_DATA_ID, XREF_KEY1).
• EC.ECE_TP_LOCATIONS / EC.ECE_TP_HEADERS — Trading partner definitions providing category context for inbound/outbound processing.
• EC.ECE_IN_PROCESS_DATA / EC.ECE_OUT_PROCESS_DATA — Staging tables whose values are resolved against this table during conversion.
• Define Code Conversion Values window — The Oracle Forms UI used to maintain rows in this table.