Results for “ecx_xref_hdr_b”

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

Overview

ECX_XREF_HDR_B is a core configuration table owned by the ECX schema, the repository that underpins the Oracle XML Gateway. Within the XML Gateway processing model, business documents exchanged with trading partners often require values to be transformed from an internal representation into a partner-specific representation (and vice versa). ECX_XREF_HDR_B stores the header records for these transformation rules, known as "code conversion categories." Each row represents one logical cross-reference category, effectively a named bucket of value mappings that the gateway consults when translating codes during inbound and outbound message processing.

Because the table holds the category definitions rather than the individual mapping pairs, it acts as a parent or governing entity for the detail mappings that hang beneath it. In Data Vault terms, the documented foreign-key structure suggests a hub-leaning classification: XREF_CATEGORY_ID behaves as a durable business key that is referenced by dependent tables, while descriptive and audit attributes sit alongside it. This is a modeling suggestion derived from the relationship data; the physical implementation is a conventional normalized EBS table, not a literal Data Vault construct.

Key Information Stored

The table is documented with ten columns, of which the following are most significant:

• XREF_CATEGORY_ID — the surrogate primary key, enforced by ECX_XREF_HDR_B_PK. It uniquely identifies each code conversion category and is the column referenced by child detail records.
• XREF_CATEGORY_CODE — the human-readable category identifier, combined with the edition name in the unique index ECX_XREF_HDR_B_U1, making it a primary business-key candidate.
• DESCRIPTION — the descriptive text explaining the purpose or scope of the category.
• APPLICATION_ID — identifies the owning application, scoping the category to a specific product or module.
• ZD_EDITION_NAME — the editioning column used in EBS 12.2 online patching; it participates in both unique indexes (ECX_XREF_HDR_B_U1 and ECX_XREF_HDR_B_U2).
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN capture who created and last modified each category, supporting standard EBS auditing and troubleshooting.

Note that the business-key uniqueness is defined on the pair (XREF_CATEGORY_CODE, ZD_EDITION_NAME); the surrogate-plus-edition index ECX_XREF_HDR_B_U2 provides the alternate unique access path anchored on XREF_CATEGORY_ID.

Common Use Cases and Queries

Typical scenarios include auditing the set of configured cross-reference categories, and joining the header to its detail mappings to review a full conversion set. Common patterns:

• Listing categories for an application: SELECT xref_category_id, xref_category_code, description FROM ecx.ecx_xref_hdr_b WHERE application_id = :app_id ORDER BY xref_category_code;
• Resolving a category by its business key: SELECT xref_category_id FROM ecx.ecx_xref_hdr_b WHERE xref_category_code = :code;
• Reporting header-to-detail mappings: SELECT h.xref_category_code, d.* FROM ecx.ecx_xref_hdr_b h JOIN ecx.ecx_xref_dtl_b d ON d.xref_category_id = h.xref_category_id;
• Change auditing: querying LAST_UPDATE_DATE and LAST_UPDATED_BY to detect recently modified categories.

Related Objects

The most significant related object is the detail table, which carries the individual mapping pairs referenced by the header. Join on XREF_CATEGORY_ID.

• ECX_XREF_DTL_B — the child table holding detail mapping rows. Its column ECX_XREF_DTL_B.XREF_CATEGORY_ID is a foreign key to ECX_XREF_HDR_B, forming the primary header-to-detail relationship.
• ECX_XREF_HDR_B_PK — the primary key constraint on XREF_CATEGORY_ID.
• Supporting lookups such as the application registry (FND_APPLICATION) are commonly joined via APPLICATION_ID to resolve owning module names, though this is not a documented FK here.

Together, the header and detail tables form the code conversion framework that the XML Gateway applies during message transformation.