Search Results ego_itm_usr_attr_intrfc




Overview
Explore in AI Chat

Overview

EGO_ITM_USR_ATTR_INTRFC is the interface (staging) table for user-defined item attributes in Oracle E-Business Suite's Advanced Product Catalog module (EGO). It serves as the inbound holding area through which externally sourced or bulk-loaded user attribute values are validated, transformed, and ultimately applied to the item master and catalog structures. The table is owned by the EGO schema and is marked VALID in the ETRM 12.2.2 data dictionary, where it is documented with 51 columns.

Functionally, the table decouples the act of supplying attribute data from the act of persisting it. Rows arrive with a PROCESS_STATUS flag and are consumed by a concurrent program that maps interface rows into the item user-attribute structures. Because attribute definition is flexible — attributes belong to attribute groups and can be text, numeric, date, or unit-of-measure based — the interface table carries parallel value columns plus supporting descriptors, allowing heterogeneous attribute payloads to share one staging structure.

From a heuristic Data Vault perspective, the mined FK structure classifies this object as standalone, suggesting it behaves as an independent staging or transactional structure rather than a dependent hub, link, or satellite. Its single documented foreign key to MTL_ITEM_CATALOG_GROUPS_B anchors it to the catalog group hierarchy, but it does not participate in a broader normalized relationship web. This is consistent with its role as a transient interface entity: rows are created by imports, enriched during processing, and either promoted or rejected, so persistent referential modeling is intentionally light.

Key Information Stored

Identity and correlation are handled by two unique indexes. EGO_ITM_USR_ATTR_INTRFC_U1 covers (DATA_SET_ID, ROW_IDENTIFIER, ATTR_INT_NAME), which is the business-key candidate that uniquely identifies one attribute value for one staged row within a data set. EGO_ITM_USR_ATTR_INTRFC_U2 covers INTERFACE_TABLE_UNIQUE_ID, the surrogate identifier used by the processing program to address individual interface rows. TRANSACTION_ID and ROW_IDENTIFIER tie staged data back to the originating transaction and source row.

The payload columns capture the attribute definition and value: ATTR_GROUP_INT_NAME and ATTR_INT_NAME identify the target attribute group and attribute, while ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_VALUE_UOM, and ATTR_DISP_VALUE hold the typed value and its display representation. The item context is supplied by ORGANIZATION_CODE, ORGANIZATION_ID, INVENTORY_ITEM_ID, ITEM_CATALOG_GROUP_ID, REVISION, and REVISION_ID, with ITEM_NUMBER#1 available as an alternate item reference. Process control is expressed through PROCESS_STATUS, TRANSACTION_TYPE, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and the audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE. Integration and extensibility columns include SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE, BUNDLE_ID, DATA_LEVEL_ID, DATA_LEVEL_NAME, the PROG_INT_CHAR/NUM placeholders, and the PK1_VALUE through PK5_VALUE slots.

Common Use Cases and Queries

The primary use case is monitoring and troubleshooting batch attribute loads. Operations and technical teams query pending rows, inspect rejected rows, and confirm that successfully processed rows were applied to the item master.

  • Pending work: select TRANSACTION_ID, ROW_IDENTIFIER, ATTR_INT_NAME, PROCESS_STATUS from EGO_ITM_USR_ATTR_INTRFC where PROCESS_STATUS is null or PROCESS_STATUS not in ('PROCESSED','SUCCESS');
  • Load audit by request: filter on REQUEST_ID and PROGRAM_ID to report volume, success, and error counts for a specific concurrent request execution.
  • Item-level attribute verification: join INVENTORY_ITEM_ID and ORGANIZATION_ID to item master views to reconcile staged ATTR_VALUE_STR against the persisted user attribute value.
  • Source traceability: group by SOURCE_SYSTEM_ID and SOURCE_SYSTEM_REFERENCE to reconcile interface rows against the upstream system feed.
  • Data-set scoped review: use DATA_SET_ID with ROW_IDENTIFIER and ATTR_INT_NAME to reproduce the exact key covered by the U1 index, which is the most efficient access path for point lookups.

Because the table is a staging structure, reporting should generally target processed data rather than the interface rows themselves; the interface table is best used for lineage, error analysis, and reconciling load completeness.

Related Objects

The documented foreign key EGO_ITM_USR_ATTR_INTRFC.ITEM_CATALOG_GROUP_ID references MTL_ITEM_CATALOG_GROUPS_B, establishing the catalog group context for each staged attribute. Beyond this relationship, the following objects are significant in the attribute load lifecycle.

  • MTL_ITEM_CATALOG_GROUPS_B — catalog group definition; joined on ITEM_CATALOG_GROUP_ID.
  • EGO_ITM_USR_ATTR_DEFS_V / EGO_ITM_USR_ATTR_GROUPS_V — attribute and attribute-group definitions matched via ATTR_INT_NAME and ATTR_GROUP_INT_NAME.
  • EGO_ITEM_USER_ATTRS (persisted user attribute values) — the target structure populated after interface processing, joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
  • MTL_SYSTEM_ITEMS_B — item master, joined on INVENTORY_ITEM_ID for item validation and context.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the concurrent program that consumed the rows.
  • EGO_ITM_USR_ATTR_INTRFC_ERR or equivalent error staging — receives rejected rows for correction and reprocessing.