Search Results eng_change_subjects_v




Overview
Explore in AI Chat

Overview

ENG_CHANGE_SUBJECTS_V is an Oracle E-Business Suite view owned by the APPS schema within the Engineering (ENG) product module. It consolidates the entities affected by engineering change orders (ECOs) into a single, uniformly structured result set, allowing change subjects of differing types to be queried, reported on, and integrated against through one interface. The view is particularly relevant to Oracle Product Lifecycle Management and item-centric workflows, as indicated by the EGO_ITEM and EGO_ITEM_REVISION entity names it surfaces. By unioning item revisions, item revision definitions, and generic change subjects, the view abstracts the physical storage of change order relationships and presents a normalized projection of "what is affected by which change." In Oracle EBS 12.1.1 and 12.2.2 this view remains part of the documented ETRM object inventory and is used wherever engineering changes must be enumerated by subject.

Underlying Base Objects

The view is defined as a three-branch UNION ALL-style (UNION) query over the following documented base objects in the APPS schema:

  • ENG_REVISED_ITEMS — supplies the EGO_ITEM subject rows, exposing the revised item and organization.
  • MTL_ITEM_REVISIONS_B — joined to ENG_REVISED_ITEMS to derive the EGO_ITEM_REVISION subject rows where a new item revision exists.
  • ENG_CHANGE_SUBJECTS — supplies the generic change subject rows, exposing the entity name and PK values directly.

The view's definition ties these branches together by aligning the created/last-updated audit columns and mapping subject identifiers into the PK1_VALUE through PK5_VALUE columns. Each branch assigns SUBJECT_LEVEL = 1, and the first and second branches hard-code the entity/object name to EGO_ITEM and EGO_ITEM_REVISION respectively, while the third carries the entity name from ENG_CHANGE_SUBJECTS.

Key Columns

  • CHANGE_ID — the engineering change header identifier; the primary grouping key.
  • CHANGE_LINE_ID — the change line identifier; note that the first two branches return the literal -1, since revised items are not tied to a specific change line, whereas the third branch carries the real line ID.
  • ENTITY_NAME / OBJECT_NAME — the subject type, such as EGO_ITEM or EGO_ITEM_REVISION.
  • PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE — the generic primary-key payload identifying the subject. For item subjects, PK1 holds the revised item ID (TO_CHAR(REVISED_ITEM_ID)) and PK2 holds the organization ID; for item revisions, PK3 additionally holds the revision ID.
  • SUBJECT_LEVEL — indicates the level of the subject; all documented branches return 1.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard audit columns propagated from the underlying rows.

Common Use Cases and Queries

The view is used to list all subjects affected by a change order and to reconcile item and revision impacts. A typical query returns the affected items for a given change:

  • Enumerating all items and revisions impacted by a specific ECO via CHANGE_ID.
  • Reporting item-level change impact by joining PK1_VALUE to MTL_SYSTEM_ITEMS_B.
  • Distinguishing item subjects from revision subjects using ENTITY_NAME.

A representative statement:

  • SELECT CHANGE_ID, CHANGE_LINE_ID, ENTITY_NAME, PK1_VALUE, PK2_VALUE, PK3_VALUE, SUBJECT_LEVEL FROM APPS.ENG_CHANGE_SUBJECTS_V WHERE ENTITY_NAME = 'EGO_ITEM' AND CHANGE_ID = :change_id;

Because the view normalizes heterogeneous change subjects, it is well suited to integration extracts that must emit a consistent subject schema regardless of the underlying affiliation type.