Search Results ego_mtl_sy_items_chg_b




Overview
Explore in AI Chat

Overview

EGO_MTL_SY_ITEMS_CHG_B is a transaction and staging table owned by the EGO schema (Advanced Product Catalog) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store pending change information for items — that is, the "before" image of item attribute values captured in the context of an Engineering Change Order (ECO) or item change request that has not yet been approved or implemented. The table mirrors the column structure of MTL_SYSTEM_ITEMS_B, carrying some 400 columns that replicate the vast majority of item master attributes, so that a pending revision of an item can be staged, reviewed, and later applied to the live item record.

Functionally, the table acts as a change-order buffer: rows are written when a change line is created against an item, evaluated during the change approval workflow, and finally merged into the production item definition on implementation. The ACD_TYPE column distinguishes the type of change action, and CHANGE_ID/CHANGE_LINE_ID tie each staged row back to the originating change object.

From a dimensional modeling perspective, the documented relationship structure does not reveal a clear hub-and-satellite or foreign-key-dependent topology across the change ecosystem. Using the supplied heuristic Data Vault classification, this object is assessed as standalone. This should be treated as a modeling suggestion rather than a definitive assertion, since the table has only a small number of documented outbound foreign keys to descriptive reference dimensions.

Key Information Stored

Although the table exposes 400 columns, the columns of greatest technical and functional significance are the following:

  • CHANGE_ID and CHANGE_LINE_ID — identify the parent change header and the specific change line to which the pending item attribute set belongs. These are the primary navigational keys for any change-order query. CHANGE_LINE_ID participates in the documented unique business key.
  • ACD_TYPE — the change-action type discriminator, the fourth component of the documented unique index and central to determining how the row is processed.
  • INVENTORY_ITEM_ID — the item being changed; part of the unique index and the principal link to the item master.
  • ORGANIZATION_ID — the item's owning inventory organization, also part of the unique index.
  • IMPLEMENTATION_DATE — the date the pending change is recorded as applied.
  • APPROVAL_STATUS — the workflow/approval state of the change line.
  • ITEM_NUMBER and DESCRIPTION — surrogate-friendly display attributes for the staged item.
  • ITEM_CATALOG_GROUP_ID, HAZARD_CLASS_ID, and UN_NUMBER_ID — the three documented foreign keys, referencing MTL_ITEM_CATALOG_GROUPS_B, PO_HAZARD_CLASSES_B, and PO_UN_NUMBERS_B respectively.
  • LAST_UPDATE_DATE, CREATED_BY, and LAST_UPDATED_BY — standard audit columns.
  • OBJECT_VERSION_NUMBER — optimistic locking support, characteristic of the 12.2.x EBS stack.
  • SEGMENT1 through SEGMENT20 and ATTRIBUTE1 through ATTRIBUTE30 — the concatenated key flexfield and descriptive flexfield segments mirrored from the item master.

The documented unique index EGO_MTL_SY_ITEMS_CHG_B_U1 on (INVENTORY_ITEM_ID, ORGANIZATION_ID, CHANGE_LINE_ID, ACD_TYPE) constitutes the business-key candidate. No surrogate single-column primary key is documented in the supplied metadata; the composite above functions as the effective access key.

Common Use Cases and Queries

Typical usage centers on change-order reporting and auditing: identifying items with outstanding pending changes, comparing pending values against the live item row, and confirming which changes have been implemented.

To list pending changes for a specific change order:

SELECT inventory_item_id, organization_id, item_number,
       change_line_id, acd_type, approval_status, implementation_date
FROM   ego.ego_mtl_sy_items_chg_b
WHERE  change_id = :p_change_id
ORDER BY change_line_id;

To find items with changes not yet implemented:

SELECT inventory_item_id, organization_id, item_number, change_id
FROM   ego.ego_mtl_sy_items_chg_b
WHERE  implementation_date IS NULL
  AND  approval_status = 'APPROVED';

Common reporting scenarios include: auditing attribute deltas between staged and production rows by joining on INVENTORY_ITEM_ID/ORGANIZATION_ID to MTL_SYSTEM_ITEMS_B; measuring change cycle time using LAST_UPDATE_DATE versus IMPLEMENTATION_DATE; and identifying configuration or hazardous-material classification changes via the foreign-key columns.

Related Objects

  • MTL_SYSTEM_ITEMS_B (INVENTORY / INV) — the production item master; joined on INVENTORY_ITEM_ID and ORGANIZATION_ID to compare pending versus current values.
  • MTL_ITEM_CATALOG_GROUPS_B — referenced through the documented foreign key ITEM_CATALOG_GROUP_ID.
  • PO_HAZARD_CLASSES_B — referenced through HAZARD_CLASS_ID.
  • PO_UN_NUMBERS_B — referenced through UN_NUMBER_ID.
  • EGO_CHANGE_ORDERS / EGO_CHANGE_LINE_HEADERS (EGO) — provide the change header and line context keyed by CHANGE_ID and CHANGE_LINE_ID.
  • EGO_MTL_SY_ITEMS_CHG_TL (EGO) — the translated counterpart holding language-specific item descriptions for the change rows.
  • EGO_ITEM_CHANGE_API / EGO_CHANGE_API — PL/SQL APIs that create and apply staged change rows, invoking the workflow that merges pending data into the item master.
  • MTL_ITEM_REVISIONS_B (INV) — records the revision history produced when a pending change is implemented for a revision-controlled item.
  • FND_ATTACHED_DOCUMENTS and EGO workflow tables — attach and route supporting documentation through the change approval process.

Because the table is classified as standalone from a Data Vault heuristic perspective, integrators should rely on the documented composite unique index and the FK dimensions above rather than assuming a broader normalized dependency chain.