Search Results ece_interface_columns_u1

Overview

EC.ECE_INTERFACE_COLUMNS is a seeded E-Business Suite configuration table in the EC (E-Commerce Gateway) schema that stores the data file definitions for every EDI transaction processed by the gateway. Each row describes a single data element within a data file, capturing how that element maps between the Oracle Applications base table and the EDI interface structure, along with its physical attributes and code conversion behavior.

The table is essential to both inbound and outbound EDI processing. For outbound transactions, the base table and base column identify the source of the data; for inbound transactions, they identify the destination. The interface column name points to the outbound interface table column or the inbound open interface table column, giving the gateway the metadata it needs to extract, populate, and translate EDI payloads without application code changes.

From a Data Vault modeling perspective, the mined metadata suggests ECE_INTERFACE_COLUMNS functions as a link-style object: its primary key, INTERFACE_COLUMN_ID, is a surrogate system-generated value, while INTERFACE_TABLE_ID and XREF_CATEGORY_ID are foreign keys that connect data file definitions to their parent interface tables and to cross-reference category definitions. It also carries descriptive satellite attributes such as width, position, and data type, so the table behaves as a hybrid link-satellite construct in a raw vault interpretation.

Key Information Stored

The table contains 36 documented columns. The most significant include:

• INTERFACE_COLUMN_ID — System-generated surrogate primary key, invisible to end users. It is the single business-key candidate enforced by unique index ECE_INTERFACE_COLUMNS_U1 in the APPS_TS_SEED tablespace.
• INTERFACE_TABLE_ID — Foreign key to ECE_INTERFACE_TABLES, establishing the one-to-many relationship between a data file definition and its constituent elements.
• INTERFACE_COLUMN_NAME — The outbound interface table column or inbound open interface table column that holds the element value.
• BASE_TABLE_NAME / BASE_COLUMN_NAME — The Oracle Applications source (outbound) or destination (inbound) table and column for the element.
• STAGING_COLUMN — The staging column associated with the element during EDI processing.
• POSITION, WIDTH, DATA_TYPE — Physical layout attributes defining where and how the element appears in the flat data file.
• RECORD_NUMBER, RECORD_LAYOUT_CODE, RECORD_LAYOUT_QUALIFIER — Control placement of the element within a specific record of the transaction layout.
• XREF_CATEGORY_ALLOWED — Flag identifying the column as a candidate for code conversion.
• XREF_CATEGORY_ID — Foreign key to ECE_XREF_CATEGORIES, activated through the Assign Categories window.
• XREF_KEY1_SOURCE_COLUMN through XREF_KEY5_SOURCE_COLUMN — Source columns supplying the cross-reference search key values used during code conversion.
• CONVERSION_SEQUENCE and CONVERSION_GROUP_ID — Drive ordering and grouping of conversion logic.
• ELEMENT_TAG_NAME and MAP_ID — Tagging and mapping identifiers linking the element to its EDI transaction definition.

TABLE_NAME and COLUMN_NAME are documented as reserved for future use.

Common Use Cases and Queries

Configuration and implementation teams query this table to audit seeded data file definitions, verify mapping between EDI elements and Oracle base columns, and confirm cross-reference category activation. A typical diagnostic query lists all elements for a given interface table:

• SELECT interface_column_id, interface_column_name, base_table_name, base_column_name, position, width FROM ec.ece_interface_columns WHERE interface_table_id = :p_table_id ORDER BY position;
• Locate elements eligible for code conversion: SELECT interface_column_id, interface_column_name, xref_category_id FROM ec.ece_interface_columns WHERE xref_category_allowed = 'Y';
• Trace a base column back to its EDI element: SELECT * FROM ec.ece_interface_columns WHERE base_table_name = 'OE_ORDER_HEADERS_ALL' AND base_column_name = 'ORDER_NUMBER';

Reporting use cases include generating interface file definition inventory reports, comparing custom modifications against seeded definitions before upgrades, and documenting inbound open interface column mappings. Because rows are seeded per transaction, most queries are read-only; modifications flow through the Interface File Definition window.

Related Objects

• ECE_INTERFACE_TABLES — Parent table; joined on INTERFACE_TABLE_ID. Defines the data file to which the columns belong.
• ECE_XREF_CATEGORIES — Joined on XREF_CATEGORY_ID; supplies cross-reference category metadata for code conversion.
• ECE_INTERFACE_COLUMNS_U1 — Unique index on INTERFACE_COLUMN_ID, enforcing surrogate key uniqueness.
• ECE_XREF_VALUES / ECE_XREF_HEADERS — Cross-reference value tables consulted when XREF_KEY source columns are resolved.
• ECE_INTERFACE_FILES / ECE_EDIFACT_MESSAGES — Transaction definitions that reference interface table and column layouts during EDI processing.
• Inbound open interface tables such as OE_HEADERS_IFACE_ALL and RA_INTERFACE_LINES_ALL, which are populated using column definitions from this table.