Search Results ecx_transactions_b_pk

Overview

ECX_TRANSACTIONS_B is a foundational configuration table in the Oracle XML Gateway (ECX) module, present in both Oracle E-Business Suite 12.1.1 and 12.2.2. The table stores internal transaction definitions that describe the message types the XML Gateway is capable of processing. Each row represents a distinct transaction that can be invoked through the XML Gateway infrastructure, governing how inbound and outbound messages are validated, routed, and mapped against the appropriate business events and trading partner agreements. Because the table is owned by the ECX schema and is referenced during runtime message processing, it acts as a control repository rather than a transactional log.

From a data modeling perspective, the ETRM metadata classifies this object heuristically as standalone under the Data Vault classification exercise. This suggests that ECX_TRANSACTIONS_B is best modeled as a reference or hub-like object that is not decomposed into satellite entities within the supplied metadata. The classification should be treated as a modeling suggestion only, since the physical schema does not expose foreign key relationships to other ECX tables in the documented data.

Key Information Stored

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

• TRANSACTION_ID — the surrogate primary key, enforced by the ECX_TRANSACTIONS_B_PK constraint. It uniquely identifies each internal transaction definition.
• TRANSACTION_TYPE — the broad category of the XML transaction (for example, ORDER, INVOICE, or SHIPMENT), forming the first component of the business key.
• TRANSACTION_SUBTYPE — a finer classification within the transaction type, used to distinguish message variants.
• PARTY_TYPE — identifies the trading partner role (such as Customer, Supplier, or Internal) for which the transaction definition applies.
• TRANSACTION_DESCRIPTION — a free-text description of the transaction's business purpose.
• ENABLED — a flag controlling whether the transaction definition is active for runtime processing.
• APPLICATION_ID — ties the transaction to the owning Oracle application, supporting multi-application deployments.
• ADMIN_USER — the administrative user responsible for the definition.
• ZD_EDITION_NAME — the editioning column introduced for Oracle EBS 12.2 online patching, enabling edition-based redefinition.
• Standard who-columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide audit lineage.

Two unique indexes act as business-key candidates: ECX_TRANSACTIONS_B_U1 on (TRANSACTION_TYPE, TRANSACTION_SUBTYPE, PARTY_TYPE, ZD_EDITION_NAME), and ECX_TRANSACTIONS_B_U2 on (TRANSACTION_ID, ZD_EDITION_NAME). The first index is the meaningful business key, while the second is a surrogate-plus-edition uniqueness constraint.

Common Use Cases and Queries

ECX_TRANSACTIONS_B is queried primarily during XML Gateway setup and troubleshooting. Common scenarios include verifying which transactions are enabled for a given trading partner, auditing configuration drift between environments, and diagnosing why an inbound or outbound message was rejected. A typical query to list enabled transactions for a party type is:

• SELECT transaction_id, transaction_type, transaction_subtype, transaction_description FROM ecx.ecx_transactions_b WHERE enabled = 'Y' AND party_type = 'CUSTOMER';
• SELECT * FROM ecx.ecx_transactions_b WHERE transaction_type = 'ORDER' AND transaction_subtype = 'PROCESS';

Reporting use cases include building a catalog of supported XML Gateway transactions, joining to XML Gateway message maps to confirm end-to-end coverage, and driving reconciliation reports that compare configured transactions against actual message traffic.

Related Objects

The supplied metadata documents no foreign key relationships, so linkage to other ECX objects is logical rather than enforced. The most significant related objects in a typical EBS XML Gateway implementation include:

• ECX_TRANSACTION_TYPES / ECX_TRANSACTION_SUBTYPES — referenced values used to populate TRANSACTION_TYPE and TRANSACTION_SUBTYPE.
• ECX_PARTY_TYPES — reference data for PARTY_TYPE.
• ECX_OUTBOUND_LOGS and ECX_INBOUND_LOGS — runtime logs that record executions of the transactions defined here.
• ECX_MESSAGE_MAP / ECX_MESSAGE_DEFINITIONS — message structure definitions associated with each transaction.
• ECX_TP_HEADERS — trading partner headers linked to party type values.
• XML Gateway APIs — the ECX_XML_GATEWAY package and related concurrent programs that consume these definitions.

In Oracle EBS 12.2.2, joins to these objects must account for ZD_EDITION_NAME to isolate the active edition during online patching cycles.