Results for “item_number”

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

Overview

The ENG_CHANGE_HDR_ITEM_SUBJECTS_V view is a reporting and integration construct in the Oracle E-Business Suite Engineering (ENG) module, owned by the APPS schema. It exposes the item-level and item-revision-level subjects attached to an Engineering Change Order (ECO) header, allowing external reports, concurrent programs, and interfaces to retrieve the affected items and their associated revisions without directly navigating the normalized ENG_CHANGE_SUBJECTS table. The view is valid in both Oracle EBS 12.1.1 and 12.2.2 environments and is defined using a UNION ALL of two internal queries, each resolving a distinct subject entity type.

Underlying Base Objects

The view is defined over three referenced base objects, all exposed to APPS through synonyms:

• ENG_CHANGE_SUBJECTS — the primary change subject table, storing the linkage between a change header (CHANGE_ID) and its subjects via ENTITY_NAME, PK1_VALUE, PK2_VALUE, PK3_VALUE, and CHANGE_LINE_ID.
• MTL_SYSTEM_ITEMS_KFV — the key flexfield (concatenated segments) view of inventory items, supplying the formatted ITEM_NUMBER.
• MTL_ITEM_REVISIONS_B — the item revision base table, supplying the revision identifier and label.

The first branch of the union handles subjects where ENTITY_NAME = 'EGO_ITEM', joining PK1_VALUE to INVENTORY_ITEM_ID and PK2_VALUE to ORGANIZATION_ID. This branch returns NULL for ITEM_REVISION and is further filtered by a NOT EXISTS clause that suppresses the item row when a corresponding revision-level subject (ENTITY_NAME = 'EGO_ITEM_REVISION' with a non-null PK3_VALUE) exists for the same change. The second branch handles ENTITY_NAME = 'EGO_ITEM_REVISION', joining PK3_VALUE to REVISION_ID to resolve the revision label.

Key Columns

• CHANGE_ID — the engineering change order identifier, linking each returned subject back to the ECO header.
• ITEM_NUMBER — the concatenated flexfield segments of the affected inventory item, presented as ITEMS.CONCATENATED_SEGMENTS.
• ITEM_REVISION — the revision associated with the change subject. For item-level subjects this value is NULL; for revision-level subjects it resolves through DECODE(REV.REVISION_LABEL, NULL, REV.REVISION, REV.REVISION_LABEL), preferring the descriptive label when present and falling back to the revision number.

The item_revision column is the specific attribute most frequently queried against this view, as it distinguishes a change that affects an entire item from one that affects only a particular revision.

Common Use Cases and Queries

Typical scenarios include ECO impact reports, revision-controlled change tracking, and integrations that push affected items to downstream PLM or MES systems. A common query retrieves all subjects for a given change:

• SELECT change_id, item_number, item_revision FROM eng_change_hdr_item_subjects_v WHERE change_id = :p_change_id;
• SELECT item_number, item_revision FROM eng_change_hdr_item_subjects_v WHERE item_number = :p_item;

Because the NOT EXISTS clause suppresses item-level rows when a revision-level subject exists, consumers should interpret a NULL ITEM_REVISION as indicating that the change applies to the item as a whole rather than to a specific revision. This behavior is essential when reconciling engineering change scope, since it prevents double-counting when both an item and its revision are recorded as subjects. The view is read-only and does not support DML; all maintenance must occur through the underlying Engineering change subject APIs and forms.