View: BOM_REF_DESIGNATORS_VIEW

Product:	BOM - Bills of Material
Description:	Both implemented and unimplemented reference designators
Implementation/DBA Data:	APPS.BOM_REF_DESIGNATORS_VIEW

View Text

SELECT C.COMPONENT_SEQUENCE_ID
, B.COMPONENT_REFERENCE_DESIGNATOR
, B.ACD_TYPE
, B.CHANGE_NOTICE
, A.IMPLEMENTATION_DATE
, B.REF_DESIGNATOR_COMMENT
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, C.COMMON_COMPONENT_SEQUENCE_ID
FROM BOM_REFERENCE_DESIGNATORS B
, BOM_INVENTORY_COMPONENTS C
, BOM_INVENTORY_COMPONENTS A
WHERE A.COMPONENT_SEQUENCE_ID = B.COMPONENT_SEQUENCE_ID
AND NVL(B.ACD_TYPE
, 1) <> 3
AND C.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND DECODE(C.IMPLEMENTATION_DATE
, NULL
, C.OLD_COMPONENT_SEQUENCE_ID
, C.COMPONENT_SEQUENCE_ID)= DECODE(A.IMPLEMENTATION_DATE
, NULL
, A.OLD_COMPONENT_SEQUENCE_ID
, A.COMPONENT_SEQUENCE_ID)
AND A.EFFECTIVITY_DATE <= C.EFFECTIVITY_DATE
AND NOT EXISTS (SELECT 'MAX DATE'
FROM BOM_REFERENCE_DESIGNATORS D1
, BOM_INVENTORY_COMPONENTS C1
WHERE C1.COMPONENT_SEQUENCE_ID = D1.COMPONENT_SEQUENCE_ID
AND DECODE(C1.IMPLEMENTATION_DATE
, NULL
, C1.OLD_COMPONENT_SEQUENCE_ID
, C1.COMPONENT_SEQUENCE_ID) = DECODE(A.IMPLEMENTATION_DATE
, NULL
, A.OLD_COMPONENT_SEQUENCE_ID
, A.COMPONENT_SEQUENCE_ID)
AND C1.BILL_SEQUENCE_ID = C.BILL_SEQUENCE_ID
AND D1.COMPONENT_REFERENCE_DESIGNATOR = B.COMPONENT_REFERENCE_DESIGNATOR
AND C1.EFFECTIVITY_DATE <= C.EFFECTIVITY_DATE
AND C1.EFFECTIVITY_DATE > A.EFFECTIVITY_DATE )

Columns