Search Results ece_interface_columns_pk

Overview

ECE_INTERFACE_COLUMNS is a registration table in the Oracle e-Commerce Gateway (EC) module. It stores the interface file layout definitions that describe how inbound and outbound EDI transaction data maps to staging columns, base tables, and conversion rules. In effect, the table acts as the column-level catalog of the gateway's interface definitions: each row defines one field within an interface file associated with a specific EDI transaction, describing its position, width, data type, and the target or source column it corresponds to.

From a dimensional modeling perspective, the mined foreign-key structure classifies this object heuristically as a link table. It resolves relationships between interface table definitions (via INTERFACE_TABLE_ID) and cross-reference categories (via XREF_CATEGORY_ID), while carrying descriptive attributes of its own. This classification is offered as a modeling suggestion rather than as a documented product characteristic.

Key Information Stored

The table is owned by the EC schema and contains 36 documented columns in ETRM 12.2.2. The most significant include:

• INTERFACE_COLUMN_ID — the surrogate primary key, enforced by ECE_INTERFACE_COLUMNS_PK and also carried by unique index ECE_INTERFACE_COLUMNS_U1.
• INTERFACE_TABLE_ID — foreign key to ECE_INTERFACE_TABLES, linking the column definition to its parent interface file definition.
• INTERFACE_COLUMN_NAME — the logical name of the interface column as presented to the gateway.
• BASE_TABLE_NAME / BASE_COLUMN_NAME — the underlying base table and column to which the interface column is mapped.
• TABLE_NAME / COLUMN_NAME — the staging or working table and column used during processing.
• STAGING_COLUMN — identifies the staging column consumed or populated during interface processing.
• RECORD_NUMBER, POSITION, WIDTH — physical layout attributes describing where the field appears in the flat file and how many characters it spans.
• DATA_TYPE — the datatype of the interface column.
• CONVERSION_SEQUENCE — governs the order in which conversion logic is applied to the column.
• XREF_CATEGORY_ID / XREF_CATEGORY_ALLOWED — the cross-reference category used for value translation and whether cross-referencing is permitted.
• XREF_KEY1_SOURCE_COLUMN through XREF_KEY5_SOURCE_COLUMN — source columns supplying the key values for cross-reference lookups.
• MAP_ID, ELEMENT_TAG_NAME, EXTERNAL_LEVEL — mapping and XML/EDI element identification attributes.
• Standard WHO and concurrent program columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical usage centers on diagnosing why an inbound or outbound EDI transaction fails to populate the expected column, or on documenting the layout of a specific interface file for integration testing. A frequent query joins columns to their parent interface table definition:

• List all columns for a given interface table: SELECT interface_column_name, position, width, data_type FROM ece_interface_columns WHERE interface_table_id = :id ORDER BY position;
• Identify columns requiring cross-reference validation: SELECT interface_column_name, xref_category_id FROM ece_interface_columns WHERE xref_category_allowed = 'Y';
• Trace the mapping to base and staging objects: SELECT base_table_name, base_column_name, table_name, column_name FROM ece_interface_columns WHERE interface_column_id = :id;
• Audit recent setup changes using the WHO columns: SELECT interface_column_id, last_updated_by, last_update_date FROM ece_interface_columns WHERE last_update_date > SYSDATE - 30;

These queries support reconciliation reporting, interface change control, and troubleshooting of conversion sequences applied during gateway processing.

Related Objects

The documented foreign keys tie this table directly to two parent objects, and additional dependencies follow from those relationships:

• ECE_INTERFACE_TABLES — joined on INTERFACE_TABLE_ID; provides the parent interface file definition to which each column belongs.
• ECE_XREF_CATEGORIES — joined on XREF_CATEGORY_ID; defines the cross-reference category used for column value translation.
• ECE_INTERFACE_COLUMNS_PK and ECE_INTERFACE_COLUMNS_U1 — the primary key constraint and unique index, both enforcing uniqueness on INTERFACE_COLUMN_ID.
• Staging and base tables named in TABLE_NAME, BASE_TABLE_NAME, and STAGING_COLUMN — downstream targets whose population depends on these definitions.

Together these objects form the metadata backbone by which the e-Commerce Gateway interprets and maps EDI interface files against Oracle EBS application tables.