Search Results ego_itm_usr_attr_intrfc_u2

Overview

EGO.EGO_ITM_USR_ATTR_INTRFC is an interface (staging) table in the EGO schema of Oracle E-Business Suite, residing in the APPS_TS_INTERFACE tablespace. It serves as the temporary landing area for user-defined item attribute data before that data is validated and applied to the base item definition. In Oracle EBS, item attributes extend beyond the seeded columns of MTL_SYSTEM_ITEMS_B; the EGO schema owns the extensible attribute framework, and this table is the mechanism through which externally sourced attribute values enter that framework. The table is registered as FND Design Data under the same name with Status VALID, and the supplied metadata describes it explicitly as the "Interface table for user attributes."

Rows arrive from a source such as a loaded spreadsheet, a batch program, or an external integration. The columns of the table mirror the structure of the target attribute hierarchy: a data set, an attribute group, an individual attribute, and a value. The ETRM heuristic Data Vault classification for this object is standalone, meaning no parent-child vault relationships are asserted from the FK structure. From a modeling perspective, it may be helpful to view the table as a transient link between an item and its extended attribute values, but it should be modeled in isolation because the metadata does not establish a hub or satellite dependency for it. The only documented foreign key reference is ITEM_CATALOG_GROUP_ID to MTL_ITEM_CATALOG_GROUPS_B. It is important to treat this object as a working area rather than a system of record: the authoritative attribute values live in the EGO production attribute tables, and the control columns here — PROCESS_STATUS, TRANSACTION_ID, DATA_SET_ID — govern the lifecycle of each staged row.

Key Information Stored

The documented physical schema comprises 51 columns. The most operationally significant are:

• INTERFACE_TABLE_UNIQUE_ID — the surrogate primary key candidate, enforced as the unique index EGO_ITM_USR_ATTR_INTRFC_U2. It provides a single-column, stable identifier for each staged row.
• DATA_SET_ID, ROW_IDENTIFIER, ATTR_INT_NAME — the business-key candidate enforced by the unique index EGO_ITM_USR_ATTR_INTRFC_U1. DATA_SET_ID groups rows processed together and should correspond to the parent object's data set ID; ROW_IDENTIFIER groups rows that constitute one logical attribute group row; ATTR_INT_NAME is the internal name of the individual attribute.
• TRANSACTION_ID — a user-specified identifier (for example a spreadsheet row number) used to log errors against each row.
• PROCESS_STATUS — indicates whether a row is eligible for processing, is being processed, encountered an error, or completed successfully.
• ATTR_GROUP_INT_NAME and ATTR_GROUP_ID — identify the attribute group, the container of related attributes.
• ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ATTR_VALUE_UOM, ATTR_UOM_DISP_VALUE — the typed value columns, allowing a single interface row shape to carry string, numeric, date, and unit-of-measure attribute values.
• ORGANIZATION_CODE / ORGANIZATION_ID, ITEM_NUMBER / INVENTORY_ITEM_ID, REVISION — the item identification context; the code-oriented columns are populated when the internal ID is unknown.
• SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE, BUNDLE_ID, CHANGE_ID, CHANGE_LINE_ID — provenance and change-tracking columns, indexed for traceability.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID — concurrent program context for the run that staged the row.

The remaining columns include audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN), generic programmable columns (PROG_INT_CHAR1/2, PROG_INT_NUM1–4), and data-level and primary-key mapping columns (DATA_LEVEL_ID, DATA_LEVEL_NAME, PK1_VALUE through PK5_VALUE, USER_DATA_LEVEL_NAME). The non-unique indexes U1 is unique for the three-column key; N1 through N5 and N10 support access by data set, attribute group, organization and item, process status, transaction, source system, and bundle.

Common Use Cases and Queries

Typical scenarios include pre-load validation of a staged user-attribute upload, error reporting keyed by TRANSACTION_ID, and reconciliation of what was staged versus what was ultimately applied. A representative query for pending rows is:

• SELECT TRANSACTION_ID, DATA_SET_ID, ROW_IDENTIFIER, ITEM_NUMBER, ATTR_GROUP_INT_NAME, ATTR_INT_NAME, ATTR_VALUE_STR FROM EGO_ITM_USR_ATTR_INTRFC WHERE PROCESS_STATUS = 1 AND DATA_SET_ID = :dataset;
• SELECT ATTR_INT_NAME, COUNT(*) FROM EGO_ITM_USR_ATTR_INTRFC WHERE PROCESS_STATUS = 3 GROUP BY ATTR_INT_NAME; — error distribution by attribute.
• SELECT * FROM EGO_ITM_USR_ATTR_INTRFC WHERE TRANSACTION_ID = :tid; — row-level investigation using the user-supplied row identifier.
• Joining staged rows to MTL_ITEM_CATALOG_GROUPS_B on ITEM_CATALOG_GROUP_ID to confirm the catalog group associated with the item attribute.

Because the table is an interface, reporting should generally be scoped to a single DATA_SET_ID or concurrent request to avoid mixing batches.

Related Objects

• MTL_ITEM_CATALOG_GROUPS_B — referenced through the foreign key on ITEM_CATALOG_GROUP_ID; the documented FK dependency.
• EGO_ITM_USR_ATTR_INTRFC_U1 / U2, N1–N5, N10 — the unique and non-unique indexes defined on this table in APPS_TS_INTERFACE.
• EGO production user-attribute tables (for example the applied attribute group and attribute value entities sharing ATTR_GROUP_ID and ATTR_INT_NAME) — the destination of validated, processed rows.
• MTL_SYSTEM_ITEMS_B — the item master against which staged rows are validated and to which attributes ultimately attach.
• EGO item user-attribute import concurrent programs and their child APIs — consume DATA_SET_ID, TRANSACTION_ID, and PROCESS_STATUS to drive validation and posting.
• FND concurrent request views — link via REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID for run-level diagnostics.