View: ENG_CHANGE_HDR_ITEM_SUBJECTS_V

Product:	ENG - Engineering
Description:
Implementation/DBA Data:	APPS.ENG_CHANGE_HDR_ITEM_SUBJECTS_V

View Text

SELECT CHANGE_SUBJECTS.CHANGE_ID
, ITEMS.CONCATENATED_SEGMENTS AS ITEM_NUMBER
, NULL AS ITEM_REVISION
FROM ENG_CHANGE_SUBJECTS CHANGE_SUBJECTS
, MTL_SYSTEM_ITEMS_KFV ITEMS
WHERE CHANGE_SUBJECTS.CHANGE_LINE_ID IS NULL
AND CHANGE_SUBJECTS.ENTITY_NAME = 'EGO_ITEM'
AND CHANGE_SUBJECTS.PK1_VALUE = ITEMS.INVENTORY_ITEM_ID
AND CHANGE_SUBJECTS.PK2_VALUE = ITEMS.ORGANIZATION_ID
AND NOT EXISTS (SELECT REVSUB.CHANGE_ID
FROM ENG_CHANGE_SUBJECTS REVSUB
WHERE REVSUB.CHANGE_ID = CHANGE_SUBJECTS.CHANGE_ID
AND REVSUB.CHANGE_LINE_ID IS NULL
AND REVSUB.ENTITY_NAME = 'EGO_ITEM_REVISION'
AND REVSUB.PK3_VALUE IS NOT NULL) UNION ALL SELECT CHANGE_SUBJECTS.CHANGE_ID
, ITEMS.CONCATENATED_SEGMENTS AS ITEM_NUMBER
, DECODE(REV.REVISION_LABEL
, NULL
, REV.REVISION
, REV.REVISION_LABEL) AS ITEM_REVISION
FROM ENG_CHANGE_SUBJECTS CHANGE_SUBJECTS
, MTL_SYSTEM_ITEMS_KFV ITEMS
, MTL_ITEM_REVISIONS_B REV
WHERE CHANGE_SUBJECTS.CHANGE_LINE_ID IS NULL
AND CHANGE_SUBJECTS.ENTITY_NAME = 'EGO_ITEM_REVISION'
AND CHANGE_SUBJECTS.PK1_VALUE = REV.INVENTORY_ITEM_ID
AND CHANGE_SUBJECTS.PK2_VALUE = REV.ORGANIZATION_ID
AND CHANGE_SUBJECTS.PK3_VALUE =REV.REVISION_ID
AND ITEMS.INVENTORY_ITEM_ID = CHANGE_SUBJECTS.PK1_VALUE
AND ITEMS.ORGANIZATION_ID = CHANGE_SUBJECTS.PK2_VALUE

Columns