VIEW: APPS.ENG_CHANGE_HDR_ITEM_SUBJECTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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