Search Results ece_xref_data

Overview

ECE_XREF_DATA is the code-conversion repository for the Oracle e-Commerce Gateway (EC) product. It stores cross-reference mappings that translate Oracle internal values into external (trading-partner or flat-file) values, and conversely translate inbound external values back into Oracle internal values. Because e-Commerce Gateway interfaces exchange data with third-party systems, translating codes such as units of measure, currency codes, and status codes is essential to successful message processing. Each row represents one mapping entry within a named cross-reference category.

The table resides in the EC schema and is documented as VALID. In the ETRM 12.2.2 physical schema it exposes 25 columns. Heuristically, the mined foreign-key structure classifies this object as satellite-leaning in a Data Vault model: it hangs off a parent dimension (ECE_XREF_CATEGORIES) via a foreign key and carries descriptive, attribute-style payload (the external/internal value pairs), rather than acting as a pure hub or a many-to-many link. This classification should be treated as a modeling suggestion; the operative role within EBS is a transactional lookup and conversion table.

Key Information Stored

The table's primary key and its principal business columns are as follows:

• XREF_DATA_ID — surrogate primary key (ECE_XREF_DATE_PK; also carried by the unique index ECE_XREF_DATA_U1). This is the surrogate, not the natural business key.
• XREF_CATEGORY_ID — foreign key to ECE_XREF_CATEGORIES. Identifies the conversion category (e.g., UOM, currency) this mapping belongs to. This is the effective business-key candidate when combined with the value columns.
• XREF_CATEGORY_CODE — the denormalized category code, useful for filtering without a join.
• DIRECTION — indicates whether the mapping converts internal-to-external, external-to-internal, or both.
• XREF_INT_VALUE — the Oracle internal value in the conversion pair.
• XREF_EXT_VALUE1 through XREF_EXT_VALUE5 — up to five external values associated with the internal value, allowing one internal code to map to multiple partner-specific external codes.
• XREF_KEY1 through XREF_KEY5 — qualifier keys that scope a mapping to a particular partner, location, or context.
• DESCRIPTION — free-text explanation of the mapping.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns for auditing.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context, identifying which request populated or last maintained the row.

Common Use Cases and Queries

The most common requirement is to resolve an Oracle internal value to a partner-specific external value during outbound interface generation, or to reverse the lookup on inbound files. A typical query filters by category and directional context:

• Resolve internal to external: select XREF_EXT_VALUE1 from ECE_XREF_DATA where XREF_CATEGORY_ID = :category and XREF_INT_VALUE = :internal and DIRECTION is compatible.
• List all mappings for a category: join to ECE_XREF_CATEGORIES on XREF_CATEGORY_ID and order by XREF_INT_VALUE.
• Audit changes: query rows by LAST_UPDATE_DATE or LAST_UPDATED_BY to identify recent maintenance.
• Multi-partner resolution: use XREF_KEY1..XREF_KEY5 to determine which of XREF_EXT_VALUE1..5 applies to a given trading partner.
• Reporting: build a cross-reference matrix report showing internal values against external values per category for implementation validation.

Related Objects

The dominant documented relationship is the foreign key from XREF_CATEGORY_ID to ECE_XREF_CATEGORIES, which supplies the category definition that qualifies every mapping row. Because e-Commerce Gateway conversion logic is invoked across outbound and inbound interfaces, dependent objects include the gateway transaction tables and the concurrent programs that populate and maintain cross-reference data. When querying or extending this table, always join to ECE_XREF_CATEGORIES to resolve the category context and constrain results to the intended conversion domain.