Search Results interface_table_id

Overview

ECE_INTERFACE_TBLS_UPG is a configuration and metadata table owned by the EC schema (e-Commerce Gateway) in Oracle E-Business Suite. It contains the information regarding the interface and extension tables used to support each EDI transaction. In practical terms, the table maps each supported EDI transaction type to the physical database structures that the gateway uses to stage inbound flat-file data and to hold outbound extracted data before translation, along with the extension tables, key columns, and versioning attributes that govern that mapping.

The object is significant because it drives the generic, data-driven behavior of the e-Commerce Gateway. Rather than hard-coding table names and key columns per transaction, the gateway resolves them at runtime from this repository, which is what allows new EDI transaction versions and customer-specific extension tables to be introduced without code changes.

The documented relationship metadata classifies this object as standalone, meaning no foreign key dependencies were mined from the FK structure. Under a Data Vault modeling heuristic, this would suggest treating ECE_INTERFACE_TBLS_UPG as a hub-style reference entity: its identity is carried by a single surrogate key, it has no documented upstream parents, and its descriptive attributes (direction, output level, table names, flags) would naturally be modeled as a satellite attached to that hub.

Key Information Stored

The table exposes 24 documented columns in the 12.2.2 physical schema. The most operationally important are:

• INTERFACE_TABLE_ID — the surrogate primary key (documented as OCO_18586890). This is the identifier referenced whenever another gateway object or a lookup needs to point at a specific interface table definition.
• TRANSACTION_TYPE — the EDI transaction code (for example, an 850 or 810 style transaction) that the row describes. This is the principal business-key candidate alongside the flat-file version.
• FLATFILE_VERSION — the EDI version of the flat file being processed, which distinguishes multiple mappings for the same transaction type.
• DIRECTION — indicates whether the mapping serves inbound (inbound flat file to interface table) or outbound (interface table to flat file) processing.
• OUTPUT_LEVEL — controls the granularity at which outbound data is written (for example header, line, or detail level).
• INTERFACE_TABLE_NAME and EXTENSION_TABLE_NAME — the physical tables used to stage the transaction data and any customer extension columns.
• KEY_COLUMN_NAME — the column on the interface table used as the unique or grouping key during processing.
• START_NUMBER — the starting sequence value used when generating run or batch identifiers for the transaction.
• PARENT_LEVEL — defines the hierarchy level relationship used when resolving parent and child records within the transaction.
• MAP_ID — links the row to the mapping definition used to convert between flat-file layout and interface table layout.
• PRIMARY_ADDRESS_TYPE — the address type (for example, ship-to or bill-to) used when the transaction references party sites.
• ENABLED and INSTALLED_FLAG — control whether the definition is active and whether the associated structures are installed in the current instance.
• UPGRADED_FLAG — indicates whether the row has been processed by the upgrade path, which is the specific reason for the _UPG suffix on the table.

Standard Oracle who-columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and concurrent request columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are also present and are useful for auditing when a mapping was introduced or last modified.

Common Use Cases and Queries

Typical usage is diagnostic and configuration-oriented rather than transactional. Support and implementation teams query this table to determine which physical interface table backs a given EDI transaction, to confirm that extension tables are registered, and to verify that a definition is enabled after an upgrade or patch.

A common pattern is to resolve the interface and extension table names for a transaction during troubleshooting of a failed inbound or outbound run:

• SELECT interface_table_name, extension_table_name, key_column_name, direction, output_level FROM ece_interface_tbls_upg WHERE transaction_type = :p_transaction_type AND flatfile_version = :p_version AND enabled = 'Y';
• SELECT interface_table_id, transaction_type, map_id, installed_flag, upgraded_flag FROM ece_interface_tbls_upg WHERE upgraded_flag = 'N'; — used to find definitions not yet processed by the upgrade.
• SELECT COUNT(*), transaction_type FROM ece_interface_tbls_upg WHERE enabled = 'Y' GROUP BY transaction_type; — inventory of supported transactions per instance.

Reporting use cases include documenting the gateway's EDI footprint for an audit, comparing enabled transactions across environments, and identifying rows whose MAP_ID or extension table references a mapping or table that no longer exists.

Related Objects

Because the mining classified this object as standalone, no enforced foreign keys are documented. Logical relationships nonetheless exist through the shared EC e-Commerce Gateway data model:

• ECE_INTERFACE_TBLS — the runtime interface table definition repository, closely related to this upgrade counterpart and sharing the same INTERFACE_TABLE_ID semantics.
• ECE_TP_HEADERS and ECE_TP_DETAILS — trading partner and transaction-level setup that determines which transaction types are actually exchanged.
• ECE_MAPPINGS / mapping definition objects — referenced by MAP_ID to drive flat-file to interface table conversion.
• ECE_EDIFILE_HEADERS and the EC flat-file staging tables — the inbound and outbound files processed using the interface table names resolved here.
• ECX_* interface tables — the physical per-transaction staging tables whose names are stored in INTERFACE_TABLE_NAME and EXTENSION_TABLE_NAME.
• Concurrent programs in the EC application (for example, the e-Commerce Gateway import and extract programs) that read this table at runtime to resolve processing parameters.

When tracing a specific transaction, joining on TRANSACTION_TYPE and FLATFILE_VERSION rather than on INTERFACE_TABLE_ID is the practical approach, since the business key is the transaction and version combination.