Search Results ece_xref_categories_pk

Overview

ECE_XREF_CATEGORIES is a reference and configuration table owned by the EC schema (e-Commerce Gateway) in Oracle E-Business Suite 12.1.1 and 12.2.2. It contains the code conversion category definitions used by the e-Commerce Gateway to translate, or "cross-reference," values exchanged with external trading partners into values recognized internally by Oracle applications. Each row defines one conversion category, its descriptive text, the interface data table and column to which it applies, and a set of flags and column mappings that control how up to five key values are resolved during translation.

Because it stores a stable set of category identifiers that are referenced by dependent transaction and setup tables, a Data Vault modeling heuristic classifies this object as hub-leaning. In that view, XREF_CATEGORY_ID functions as the hub business key, while the remaining descriptive and configuration attributes behave as satellite-style detail. This is a modeling suggestion only; the table is a conventional relational setup table in the EC schema.

Key Information Stored

The table holds 29 documented columns. The most significant are:

• XREF_CATEGORY_ID — Surrogate primary key, enforced by ECE_XREF_CATEGORIES_PK and by the unique index ECE_XREF_CATEGORIES_U1. It is the value propagated to all dependent tables.
• XREF_CATEGORY_CODE — Business-key candidate, enforced by the unique index ECE_XREF_CATEGORIES_U2. This is the human-readable code that identifies the conversion category.
• DESCRIPTION — Text describing the purpose of the category.
• INT_DATA_TABLE and INT_DATA_COLUMN — Identify the interface data table and column whose values are converted under this category.
• KEY1_USED_FLAG through KEY5_USED_FLAG — Five processing flags indicating which of the five key positions are active for the category.
• KEY1_USED_TABLE / KEY1_USED_COLUMN through KEY5_USED_TABLE / KEY5_USED_COLUMN — Paired table and column mappings that resolve the source of each key position.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY — Standard Oracle who-columns for audit and concurrency control.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_LOGIN — Concurrent program and DML audit context.

Common Use Cases and Queries

Typical use is to inspect or report on which categories exist and how their key positions are configured, to diagnose translation failures, or to verify which interface columns participate in a conversion.

• List all categories and their active keys: SELECT XREF_CATEGORY_CODE, DESCRIPTION, KEY1_USED_FLAG, KEY2_USED_FLAG FROM EC.ECE_XREF_CATEGORIES ORDER BY XREF_CATEGORY_CODE;
• Find the interface table and column for a category: SELECT INT_DATA_TABLE, INT_DATA_COLUMN FROM EC.ECE_XREF_CATEGORIES WHERE XREF_CATEGORY_CODE = :p_code;
• Audit recent configuration changes: SELECT XREF_CATEGORY_CODE, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM EC.ECE_XREF_CATEGORIES WHERE LAST_UPDATE_DATE > SYSDATE - 30;
• Join to transaction-level conversion values to report unmapped codes, using ECE_XREF_DATA.XREF_CATEGORY_ID = ECE_XREF_CATEGORIES.XREF_CATEGORY_ID.

Related Objects

• ECE_XREF_DATA — References ECE_XREF_CATEGORIES via XREF_CATEGORY_ID; holds the individual from-value/to-value conversion pairs belonging to each category.
• ECE_INTERFACE_COLUMNS — References ECE_XREF_CATEGORIES via XREF_CATEGORY_ID; defines interface columns tied to a conversion category.
• ECE_XREF_CATEGORIES_PK and unique indexes ECE_XREF_CATEGORIES_U1 (XREF_CATEGORY_ID) and ECE_XREF_CATEGORIES_U2 (XREF_CATEGORY_CODE) — enforce key integrity.

These objects should be queried and maintained together when configuring or troubleshooting e-Commerce Gateway code conversion.