Search Results interface_column_id

Overview

ECE_INTERFACE_COLS_UPG is an e-Commerce Gateway (EC) repository table in Oracle E-Business Suite that contains the interface file definitions for each EDI transaction. It stores the column-level mapping metadata that describes how inbound and outbound EDI flat-file records translate into staging and base table columns, including positional, layout, conversion, and cross-reference rules. Because the EC module is the foundation of Oracle's EDI processing, this table effectively drives the runtime interpretation of every interfaced transaction, such as purchase orders, invoices, advance ship notices, and remittance advice.

The object is marked VALID and resides in the EC schema for both 12.1.1 and 12.2.2. Its documented physical schema contains 36 columns. From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is standalone, indicating the row is best treated as an independent hub-style reference entity keyed by a single surrogate identifier rather than as a dependent link or satellite. This is consistent with a definition table whose rows are self-contained configuration entries.

Key Information Stored

The surrogate primary key is INTERFACE_COLUMN_ID (documented primary key constraint OCO_18586803). This identifier uniquely names each column-definition row. The principal business and descriptive attributes include:

• INTERFACE_TABLE_ID — the parent interface table definition to which the column belongs; the primary grouping attribute.
• INTERFACE_COLUMN_NAME and COLUMN_NAME — the interface and physical column identifiers.
• STAGING_COLUMN — the staging column this definition populates.
• BASE_TABLE_NAME and BASE_COLUMN_NAME — the destination base table and column receiving the mapped value.
• TABLE_NAME — the underlying table associated with the definition.
• RECORD_NUMBER, POSITION, and WIDTH — the flat-file layout coordinates that locate and size each field within the record.
• DATA_TYPE and CONVERSION_SEQUENCE — the data classification and the ordering of value conversions applied during processing.
• CONVERSION_GROUP_ID and MAP_ID — references to the conversion and mapping definitions used to transform values.
• XREF_CATEGORY_ID, XREF_CATEGORY_ALLOWED, and XREF_KEY1_SOURCE_COLUMN through XREF_KEY5_SOURCE_COLUMN — cross-reference lookup configuration and source columns.
• RECORD_LAYOUT_CODE, RECORD_LAYOUT_QUALIFIER, and ELEMENT_TAG_NAME — layout selection and element tagging.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, PROGRAM_ID, and REQUEST_ID — standard auditing columns that support lineage analysis.

Common Use Cases and Queries

Typical uses include auditing EDI mappings, troubleshooting field-conversion failures, and generating layout documentation. A common query retrieves all column definitions for a given interface table:

• SELECT interface_column_id, interface_column_name, column_name, position, width, data_type FROM ec.ece_interface_cols_upg WHERE interface_table_id = :p_table_id ORDER BY position;
• Identifying conversion-dependent columns: SELECT interface_column_name, conversion_group_id, conversion_sequence FROM ec.ece_interface_cols_upg WHERE conversion_group_id IS NOT NULL;
• Cross-reference impact analysis: SELECT interface_column_name, xref_category_id, xref_key1_source_column FROM ec.ece_interface_cols_upg WHERE xref_category_allowed = 'Y';
• Post-patch change tracking via last_update_date and last_updated_by.

Related Objects

The definition rows are referenced through their parent interface tables and consumed by the EC processing programs. Significant related objects include:

• ECE_INTERFACE_TABLES — joined on INTERFACE_TABLE_ID to obtain the parent table definition.
• ECE_INTERFACE_COLS — the parallel/active interface column definition set.
• ECE_XREF_CATEGORIES / ECE_XREF_HEADERS — joined on XREF_CATEGORY_ID for cross-reference validation.
• ECE_CONVERSION_GROUPS / ECE_CONVERSION_VALUES — joined on CONVERSION_GROUP_ID.
• FND_ID_FLEX_STRUCTURES — descriptive flexfield context for mapping fields.
• ECX_* EDI processing programs — the concurrent programs that read these definitions at runtime.