Results for “external_tp_location_code”

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

Overview

ECX_TP_DETAILS is a table in the ECX schema within the Oracle E-Business Suite XML Gateway module. It stores trading partner detail information, forming the subordinate layer of the XML Gateway trading partner configuration model. Where ECX_TP_HEADERS captures the header-level definition of a trading partner, ECX_TP_DETAILS holds the per-transaction-type, per-routing configuration rows that determine how messages are mapped, processed, transmitted, and confirmed for that partner.

In Oracle EBS 12.1.1 and 12.2.2, the XML Gateway uses this configuration to route outbound business documents (such as purchase orders, invoices, and advance ship notices) and to validate and process inbound documents exchanged with external trading partners. The table therefore sits at the intersection of message mapping, connection setup, and transaction routing.

The heuristic Data Vault classification supplied with the metadata is satellite-leaning. From a modeling perspective, this suggests the table behaves primarily as a descriptive satellite attached to the ECX_TP_HEADERS hub, carrying attributes that change over time as partner configuration evolves. The presence of a self-referencing foreign key on ROUTING_ID introduces a degree of link-like behaviour, but the dominant structure remains satellite-like: each detail row enriches a parent trading partner with connection, mapping, and routing attributes rather than recording a relationship between independent business entities.

Key Information Stored

The primary key is the surrogate column TP_DETAIL_ID, enforced by the ECX_TP_DETAILS_PK constraint. A unique index, ECX_TP_DETAILS_U2, also exists on TP_DETAIL_ID, confirming it as the sole documented business-key candidate. The remaining columns fall into several functional groups.

• Parent linkage: TP_HEADER_ID associates each detail row with its trading partner header in ECX_TP_HEADERS.
• Mapping and processing: MAP_ID references ECX_MAPPINGS, identifying the transformation used to convert the document between internal and external formats. EXT_PROCESS_ID references ECX_EXT_PROCESSES, naming the external process that invokes or consumes the message. HUB_ID references ECX_HUBS, tying the detail to a specific integration hub.
• Connection attributes: CONNECTION_TYPE, PROTOCOL_TYPE, and PROTOCOL_ADDRESS define how the message is delivered. USERNAME and PASSWORD hold the credentials used for the connection.
• Routing: ROUTING_ID is a self-referencing foreign key to ECX_TP_DETAILS, used to chain detail rows where routing is composed or sequenced. SOURCE_TP_LOCATION_CODE and EXTERNAL_TP_LOCATION_CODE identify the internal and external trading partner locations involved.
• Ownership and control: HUB_USER_ID references ECX_HUB_USERS, while OMB_SYNC and CONFIRMATION act as configuration flags governing synchronisation and acknowledgement behaviour.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard EBS who-column auditing.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include verifying which trading partner a given mapping or connection applies to, identifying all detail rows configured for a partner, and auditing routing chains built through ROUTING_ID.

To list all detail rows for a specific trading partner header:

• SELECT d.TP_DETAIL_ID, d.MAP_ID, d.PROTOCOL_TYPE, d.PROTOCOL_ADDRESS FROM ECX.ECX_TP_DETAILS d WHERE d.TP_HEADER_ID = :p_header_id;

To trace the mapping and external process behind a configured connection:

• SELECT d.TP_DETAIL_ID, m.MAP_ID, e.EXT_PROCESS_ID FROM ECX.ECX_TP_DETAILS d, ECX.ECX_MAPPINGS m, ECX.ECX_EXT_PROCESSES e WHERE d.MAP_ID = m.MAP_ID AND d.EXT_PROCESS_ID = e.EXT_PROCESS_ID;

To follow a routing chain, a self-join on ROUTING_ID resolves the parent detail row:

• SELECT c.TP_DETAIL_ID AS child_id, p.TP_DETAIL_ID AS parent_id FROM ECX.ECX_TP_DETAILS c, ECX.ECX_TP_DETAILS p WHERE c.ROUTING_ID = p.TP_DETAIL_ID;

Because the table holds credentials in USERNAME and PASSWORD, reporting queries should restrict column selection and be subject to appropriate security controls.

Related Objects

The most significant related objects, based on the documented foreign key relationships, are:

• ECX_TP_HEADERS — parent of ECX_TP_DETAILS via TP_HEADER_ID; supplies the partner-level definition.
• ECX_MAPPINGS — joined via MAP_ID; defines document transformation logic.
• ECX_EXT_PROCESSES — joined via EXT_PROCESS_ID; defines the external processing routine.
• ECX_HUBS — joined via HUB_ID; identifies the owning integration hub.
• ECX_HUB_USERS — joined via HUB_USER_ID; identifies the hub user associated with the detail row.
• ECX_TP_DETAILS (self-reference) — joined via ROUTING_ID; supports chained or sequenced routing definitions.

Together these objects constitute the XML Gateway trading partner configuration set, with ECX_TP_DETAILS acting as the detailed satellite of ECX_TP_HEADERS and the convergence point for mapping, process, hub, and routing references.