VIEW: APPS.BOM_BILL_RELEASED_REVISIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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