View: BOM_ITEM_CURRENT_REV_VIEW

Product:	BOM - Bills of Material
Description:	Item current revisions
Implementation/DBA Data:	APPS.BOM_ITEM_CURRENT_REV_VIEW

View Text

SELECT ORGANIZATION_ID
, INVENTORY_ITEM_ID
, REVISION
, EFFECTIVITY_DATE
, REVISION_LABEL
, REVISION_ID
FROM MTL_ITEM_REVISIONS_B MIR
WHERE EFFECTIVITY_DATE IN (SELECT FIRST_VALUE(IR2.EFFECTIVITY_DATE) OVER ( ORDER BY IR2.EFFECTIVITY_DATE DESC)EFFECTIVITY_DATE
FROM MTL_ITEM_REVISIONS_B IR2
WHERE IR2.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID
AND IR2.ORGANIZATION_ID = MIR.ORGANIZATION_ID
AND IR2.EFFECTIVITY_DATE <= SYSDATE
AND IR2.IMPLEMENTATION_DATE IS NOT NULL)
AND MIR.REVISION IN (SELECT FIRST_VALUE(IR3.REVISION) OVER ( ORDER BY IR3.REVISION DESC) REVISION
FROM MTL_ITEM_REVISIONS_B IR3
WHERE IR3.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID
AND IR3.ORGANIZATION_ID = MIR.ORGANIZATION_ID
AND IR3.IMPLEMENTATION_DATE IS NOT NULL
AND IR3.EFFECTIVITY_DATE = MIR.EFFECTIVITY_DATE)

Columns