[Home] [Help]
View: BOM_BILL_RELEASED_REVISIONS_V
View Text
SELECT MIR.INVENTORY_ITEM_ID
, MIR.ORGANIZATION_ID
, MIR.REVISION
, MIR.EFFECTIVITY_DATE
, MIR.IMPLEMENTATION_DATE
, NVL(MIN(DECODE(NVL(ERI2.STATUS_TYPE
, 0)
, 1
, TO_DATE(NULL)
, 2
, TO_DATE(NULL)
, MIR2.EFFECTIVITY_DATE - 1/(60*60*24)))
, GREATEST(SYSDATE
, MIR.EFFECTIVITY_DATE))
, DECODE(MIR.IMPLEMENTATION_DATE
, NULL
, 2
, 1)
FROM ENG_REVISED_ITEMS ERI2
, MTL_ITEM_REVISIONS_B MIR
, ENG_REVISED_ITEMS ERI
, MTL_ITEM_REVISIONS_B MIR2
WHERE MIR.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND NVL(ERI.STATUS_TYPE
, 0) NOT IN (1
, 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
Name |
INVENTORY_ITEM_ID |
ORGANIZATION_ID |
REVISION |
EFFECTIVITY_DATE |
IMPLEMENTATION_DATE |
HIGH_DATE |
IMPLEMENTED_FLAG |