Results for “ego_mtl_sy_items_ext_b”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EGO_MTL_SY_ITEMS_EXT_B is the base table on the EGO (Advanced Product Catalog) side of Oracle E-Business Suite that stores user-defined (flexfield) attribute values for inventory items. In EBS 12.1.1 and 12.2.2, item master extensibility is implemented through descriptive flexfields and attribute groups; the EGO schema owns the definitional and value storage for item attributes that are captured through the Product Information Management (PIM) and Advanced Product Catalog user interfaces, then surfaced into the item master. This table is the storage anchor for those extended attribute values, holding the actual data entered against each attribute group across organizations, revisions, and data levels.

Heuristically, based on its foreign key structure, this object behaves as a standalone satelite-style table rather than a transactional hub or a link. It carries a single-column surrogate primary key and a wide, repeated set of typed attribute columns, and the only documented foreign key points outward to MTL_ITEM_CATALOG_GROUPS_B. From a Data Vault modeling perspective, it is best treated as a satellite attached to the item master and item attribute group hubs, keyed by its surrogate identifier, with the natural business key captured through its unique index.

Key Information Stored

The primary key EGO_MTL_SY_ITEMS_EXT_B_PK is defined on the single column EXTENSION_ID, the surrogate identifier for each extended attribute record. A second unique index, EGO_MTL_SY_ITEMS_EXT_B_U2, establishes the business-key candidate across INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION_ID, ATTR_GROUP_ID, DATA_LEVEL_ID, PK1_VALUE, PK2_VALUE, and UNIQUE_VALUE; a redundant single-column unique index exists on EXTENSION_ID as U1.

• EXTENSION_ID — surrogate primary key uniquely identifying each extended attribute row.
• INVENTORY_ITEM_ID / ORGANIZATION_ID / REVISION_ID — identify the item, the inventory organization, and the specific revision the attribute values apply to.
• ITEM_CATALOG_GROUP_ID — the only documented foreign key, referencing MTL_ITEM_CATALOG_GROUPS_B to tie the attribute set to a catalog group.
• ATTR_GROUP_ID — the attribute group (flexfield context) under which the values are stored.
• DATA_LEVEL_ID — the data level at which the attribute applies (for example item versus organization level).
• PK1_VALUE / PK2_VALUE / PK3_VALUE / PK4_VALUE / PK5_VALUE and UNIQUE_VALUE — key and uniqueness columns used to identify and disambiguate a given attribute occurrence.
• C_EXT_ATTR1 through C_EXT_ATTR40 — forty character-typed extended attribute columns holding text user-defined values.
• N_EXT_ATTR1 through N_EXT_ATTR20 with UOM_EXT_ATTR1 through UOM_EXT_ATTR20 — twenty numeric-typed extended attribute columns, each paired with its unit-of-measure column.
• D_EXT_ATTR1 through D_EXT_ATTR10 — ten date-typed extended attribute columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit who-columns.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — concurrent program and request traceability columns.

In total the documented 12.2.2 physical schema contains 112 columns, the majority being the typed C_EXT_ATTR, N_EXT_ATTR, UOM_EXT_ATTR, and D_EXT_ATTR slots.

Common Use Cases and Queries

Typical usage centers on reporting and validating user-defined item attributes entered through PIM/Advanced Product Catalog, auditing attribute changes, and joining attribute values back to the inventory item master for downstream analytics or interfaces.

• Listing all extended attribute values for a given item and organization by joining on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• Retrieving values for a specific attribute group using ATTR_GROUP_ID and DATA_LEVEL_ID to filter the correct context and level.
• Extracting character, numeric-with-UOM, and date attributes into a flattened report, reading C_EXT_ATTR, N_EXT_ATTR/UOM_EXT_ATTR, and D_EXT_ATTR columns respectively.
• Auditing changes by filtering on LAST_UPDATE_DATE, LAST_UPDATED_BY, and REQUEST_ID to trace which concurrent program population updated attribute values.
• Joining to MTL_ITEM_CATALOG_GROUPS_B on ITEM_CATALOG_GROUP_ID to group attributes by catalog grouping.
• Reconciling EGO-side extended attributes against item master descriptive flexfield values as part of data migration or integration validation.

Related Objects

The documented FK relationship and the EGO item attribute model establish the most significant related objects:

• MTL_ITEM_CATALOG_GROUPS_B — referenced via the foreign key on ITEM_CATALOG_GROUP_ID.
• EGO_MTL_SY_ITEMS_EXT_B_PK / EGO_MTL_SY_ITEMS_EXT_B_U1 / EGO_MTL_SY_ITEMS_EXT_B_U2 — the primary and unique indexes enforcing identity and business-key uniqueness on EXTENSION_ID and the item/group/level key combination.
• MTL_SYSTEM_ITEMS_B — the inventory item master joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• EGO item attribute group definition tables — referenced through ATTR_GROUP_ID to resolve attribute group metadata.
• Inventory item APIs (EGO_ITEM_PUB / INV_ITEM_PUB) — programmatic interfaces that populate and maintain extended attribute values, aligned with the PROGRAM_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID audit columns.