View: BOM_BILL_NO_HOLD_REVISIONS_V

Product:	BOM - Bills of Material
Description:	Item revisions not on hold
Implementation/DBA Data:	APPS.BOM_BILL_NO_HOLD_REVISIONS_V

View Text

SELECT MIR.INVENTORY_ITEM_ID
, MIR.ORGANIZATION_ID
, MIR.REVISION
, MIR.EFFECTIVITY_DATE
, MIR.IMPLEMENTATION_DATE
, NVL ( /* THIS MIN() HAS ONLY ONE ARGUMENT BECAUSE IT IS AN AGGREGATION FUNCTION * RATHER THAN A SINGLE-LINE FUNCTION. */ MIN ( DECODE ( NVL(ERI2.STATUS_TYPE
, 0)
, 2
, TO_DATE(NULL)
, MIR2.EFFECTIVITY_DATE - (1/(1440*60)) /* 1 SECOND */ ) )
, GREATEST(SYSDATE
, MIR.EFFECTIVITY_DATE) )
, DECODE(MIR.IMPLEMENTATION_DATE
, NULL
, 2
, 1)
FROM ENG_REVISED_ITEMS ERI2
, MTL_ITEM_REVISIONS MIR
, ENG_REVISED_ITEMS ERI
, MTL_ITEM_REVISIONS MIR2
WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+) AND NVL(ERI.STATUS_TYPE
, 0) <> 2 AND MIR2.ORGANIZATION_ID (+) = MIR.ORGANIZATION_ID AND MIR2.INVENTORY_ITEM_ID(+) = MIR.INVENTORY_ITEM_ID AND MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE AND MIR2.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID(+) GROUP BY MIR.INVENTORY_ITEM_ID
, MIR.ORGANIZATION_ID
, MIR.REVISION
, MIR.EFFECTIVITY_DATE
, MIR.IMPLEMENTATION_DATE

Columns