VIEW: APPS.BOM_REF_DESIGNATORS_VIEW

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )