Search Results ece_interface_tables_pk

Overview

ECE_INTERFACE_TABLES is a configuration and metadata repository within the Oracle e-Commerce Gateway (EC) product, owner schema EC. It contains the information regarding the interface and extension tables used to support each EDI transaction. In the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, this table serves as the control record that ties a given outbound or inbound EDI transaction to the physical staging tables that receive or supply its data. Because e-Commerce Gateway maps flat-file EDI structures onto relational staging tables, the gateway runtime must know which interface table holds the transaction payload, which extension table carries customer-specific overflow columns, and which single column uniquely keys each row. ECE_INTERFACE_TABLES answers those questions by storing one row per supported interface and extension table combination, keyed by transaction type, direction, and flat-file version.

The table occupies a central position in the transaction definition hierarchy. Its primary key is ECE_INTERFACE_TABLES_PK on INTERFACE_TABLE_ID, a surrogate identifier, and a second unique index ECE_INTERFACE_TABLE_U1 also covers INTERFACE_TABLE_ID. The documented heuristic Data Vault classification is hub-leaning, meaning the table is best modeled as a hub of interface table identities, with dependent descriptive attributes carried as satellite data and the ECE_INTERFACE_COLUMNS relationship resolving to a link between the interface table and its constituent columns. The documented physical schema in ETRM 12.2.2 lists 24 columns, all inventoried below by role rather than exhaustively.

Key Information Stored

The most significant columns fall into four groups: identity, mapping, runtime control, and audit.

• INTERFACE_TABLE_ID — surrogate primary key and the join key to ECE_INTERFACE_COLUMNS; the business-key candidate documented by unique index ECE_INTERFACE_TABLE_U1.
• TRANSACTION_TYPE — the EDI transaction served, such as an inbound purchase order or outbound invoice.
• FLATFILE_VERSION — the EDI flat-file version for which the mapping is valid, allowing multiple row sets per transaction type.
• DIRECTION — distinguishes inbound (file-to-table) from outbound (table-to-file) processing.
• OUTPUT_LEVEL — controls the granularity at which data is written to the flat file.
• INTERFACE_TABLE_NAME — the physical staging table that carries the transaction payload.
• EXTENSION_TABLE_NAME — the customer-defined extension table used for additional or non-standard data elements.
• KEY_COLUMN_NAME — the column in the interface table that uniquely identifies each record, with START_NUMBER supplying the sequence seed for generated keys.
• MAP_ID — associates the interface table with its mapping definition.
• PRIMARY_ADDRESS_TYPE — restricts which address type is used when more than one applies.
• ENABLED and INSTALLED_FLAG — gate whether the configuration is active and currently installed.
• PARENT_LEVEL and UPGRADED_FLAG — support hierarchical transaction layouts and upgrade tracking.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The most frequent scenario is diagnosing why an outbound or inbound EDI transaction did not populate expected data. A typical query lists every enabled interface mapping for a transaction and version:

• SELECT INTERFACE_TABLE_ID, TRANSACTION_TYPE, DIRECTION, FLATFILE_VERSION, INTERFACE_TABLE_NAME, EXTENSION_TABLE_NAME, KEY_COLUMN_NAME FROM ECE_INTERFACE_TABLES WHERE TRANSACTION_TYPE = :transaction_type AND ENABLED = 'Y' AND INSTALLED_FLAG = 'Y' ORDER BY FLATFILE_VERSION, DIRECTION;

Support and implementation teams also use the table to confirm whether an extension table has been registered, to identify the key column used when reconciling staging rows, and to audit recent configuration changes through the standard who-columns. Reporting queries frequently join to ECE_INTERFACE_COLUMNS to reconstruct a full column map per interface table, which is useful when documenting a customer-specific EDI layout.

Related Objects

• ECE_INTERFACE_COLUMNS — the child table; join on ECE_INTERFACE_COLUMNS.INTERFACE_TABLE_ID = ECE_INTERFACE_TABLES.INTERFACE_TABLE_ID. This is the only documented foreign key relationship and defines the column-level mapping for each interface table.
• ECE_INTERFACE_TABLES_PK / ECE_INTERFACE_TABLE_U1 — the primary key constraint and unique index on INTERFACE_TABLE_ID.
• ECE_INTERFACE_COLUMNS primary key — the inverse side of the documented referential integrity, confirming ECE_INTERFACE_TABLES as the parent in a hub-to-child structure.
• e-Commerce Gateway transaction definition data — the runtime components that consume DIRECTION, FLATFILE_VERSION, MAP_ID, and OUTPUT_LEVEL when translating between flat files and the staging tables named in INTERFACE_TABLE_NAME and EXTENSION_TABLE_NAME.
• EC audit and concurrent program context — REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID link configuration rows to the concurrent request that created or last modified them.