Search Results ece_xref_categories_u2

Overview

EC.ECE_XREF_CATEGORIES is a seed-owned reference table in the Oracle E-Business Suite EDI/XML Gateway (ETRM) schema. It defines the code conversion categories used by the EDI cross-reference infrastructure to map internal Oracle values to external trading partner values and vice versa. Each seeded code category occupies one row, and customers define additional categories through the Define Code Conversion Categories form. The table therefore acts as the classification master for all cross-reference data maintained in the ECE_XREF_DATA table.

Under a heuristic Data Vault classification, ECE_XREF_CATEGORIES is hub-leaning: XREF_CATEGORY_ID is a system-generated surrogate key that is invisible to end users, while XREF_CATEGORY_CODE serves as the stable business key. This pattern is characteristic of a hub entity, with the descriptive and processing-flag columns functioning as attached satellite-style attributes. The table resides in the APPS_TS_SEED tablespace, reflecting its mixed seed and customer-defined content.

Key Information Stored

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

• XREF_CATEGORY_ID — Numeric surrogate primary key (ECE_XREF_CATEGORIES_PK), system-generated and invisible to the user.
• XREF_CATEGORY_CODE — The user-defined identifier for the code category, limited to 30 characters. This is the business-key candidate enforced by unique index ECE_XREF_CATEGORIES_U2.
• DESCRIPTION — Free-text description of the category, up to 80 characters.
• KEY1_USED_FLAG through KEY5_USED_FLAG — Search key enable flags. When a code conversion applies to all trading partners and all EDI transactions, none of these flags are enabled. When the conversion is scoped to a specific rule, the relevant KEY(n)_USED_FLAG columns are enabled to represent that rule.
• INT_DATA_TABLE, INT_DATA_COLUMN — Reserved for future use, describing the internal data source.
• KEY1_USED_TABLE through KEY5_USED_COLUMN — Reserved for future use, pairing each search key with its qualifying table and column.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, which record the concurrent request that last modified the row.

The unique index ECE_XREF_CATEGORIES_U1 on XREF_CATEGORY_ID is the specific object referenced by the search term "ece_xref_categories_u1".

Common Use Cases and Queries

Typical usage centers on resolving a category code to its surrogate ID before querying or inserting cross-reference values, and on reporting which categories are rule-scoped versus global.

• Resolve a category code: SELECT xref_category_id FROM ec.ece_xref_categories WHERE xref_category_code = :code;
• List all defined categories: SELECT xref_category_id, xref_category_code, description FROM ec.ece_xref_categories ORDER BY xref_category_code;
• Identify rule-scoped categories: SELECT xref_category_code FROM ec.ece_xref_categories WHERE key1_used_flag = 'Y' OR key2_used_flag = 'Y' OR key3_used_flag = 'Y' OR key4_used_flag = 'Y' OR key5_used_flag = 'Y';
• Join to cross-reference values: SELECT c.xref_category_code, d.* FROM ec.ece_xref_categories c, ec.ece_xref_data d WHERE c.xref_category_id = d.xref_category_id;
• Audit recent changes: SELECT xref_category_code, last_update_date, last_updated_by FROM ec.ece_xref_categories WHERE last_update_date > SYSDATE - 30;

Reporting use cases include documenting the mapping strategy per trading partner, auditing newly created customer categories, and validating that reserved columns remain unpopulated prior to a future release.

Related Objects

The following objects depend on or reference ECE_XREF_CATEGORIES through documented foreign keys:

• EC.ECE_XREF_DATA — Holds the actual code conversion values; joined via XREF_CATEGORY_ID = XREF_CATEGORY_ID.
• EC.ECE_INTERFACE_COLUMNS — Describes interface columns participating in cross-referencing; joined via XREF_CATEGORY_ID.
• ECE_XREF_CATEGORIES_PK — Primary key constraint on XREF_CATEGORY_ID.
• ECE_XREF_CATEGORIES_U1 — Unique index on XREF_CATEGORY_ID, the object named in the search.
• ECE_XREF_CATEGORIES_U2 — Unique index on XREF_CATEGORY_CODE, enforcing the business key.
• Define Code Conversion Categories form — The maintenance UI through which new categories are created.

Because both foreign keys point into this table, ECE_XREF_CATEGORIES must exist and be populated before any category-specific cross-reference data or interface column definitions can be loaded.