VIEW: APPS.BOM_REFERENCE_DESIGNATORS_V

Source

View Text - Preformatted

SELECT BRD1.ROWID ROW_ID , BRD1.COMPONENT_REFERENCE_DESIGNATOR , BRD1.LAST_UPDATE_DATE , BRD1.LAST_UPDATED_BY , BRD1.CREATION_DATE , BRD1.CREATED_BY , BRD1.LAST_UPDATE_LOGIN , BRD1.REF_DESIGNATOR_COMMENT , BIC1.IMPLEMENTATION_DATE , BIC2.COMPONENT_SEQUENCE_ID , BRD1.ACD_TYPE , BRD1.CHANGE_NOTICE , BRD1.ATTRIBUTE_CATEGORY , BRD1.ATTRIBUTE1 , BRD1.ATTRIBUTE2 , BRD1.ATTRIBUTE3 , BRD1.ATTRIBUTE4 , BRD1.ATTRIBUTE5 , BRD1.ATTRIBUTE6 , BRD1.ATTRIBUTE7 , BRD1.ATTRIBUTE8 , BRD1.ATTRIBUTE9 , BRD1.ATTRIBUTE10 , BRD1.ATTRIBUTE11 , BRD1.ATTRIBUTE12 , BRD1.ATTRIBUTE13 , BRD1.ATTRIBUTE14 , BRD1.ATTRIBUTE15 , BRD1.REQUEST_ID , BRD1.PROGRAM_APPLICATION_ID , BRD1.PROGRAM_ID , BRD1.PROGRAM_UPDATE_DATE FROM BOM_REFERENCE_DESIGNATORS BRD1 , BOM_INVENTORY_COMPONENTS BIC1 , BOM_INVENTORY_COMPONENTS BIC2 WHERE BRD1.COMPONENT_SEQUENCE_ID = BIC1.COMPONENT_SEQUENCE_ID AND NVL(BRD1.ACD_TYPE, 1) <> 3 AND BIC2.BILL_SEQUENCE_ID = BIC1.BILL_SEQUENCE_ID AND DECODE(BIC2.IMPLEMENTATION_DATE, NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID , BIC2.COMPONENT_SEQUENCE_ID) = DECODE(BIC1.IMPLEMENTATION_DATE , NULL , BIC1.OLD_COMPONENT_SEQUENCE_ID , BIC1.COMPONENT_SEQUENCE_ID) AND BIC1.EFFECTIVITY_DATE = (SELECT MAX(BIC3.EFFECTIVITY_DATE) FROM BOM_REFERENCE_DESIGNATORS BRD2 , BOM_INVENTORY_COMPONENTS BIC3 WHERE BIC3.COMPONENT_SEQUENCE_ID = BRD2.COMPONENT_SEQUENCE_ID AND DECODE(BIC3.IMPLEMENTATION_DATE, NULL, BIC3.OLD_COMPONENT_SEQUENCE_ID , BIC3.COMPONENT_SEQUENCE_ID) = DECODE(BIC1.IMPLEMENTATION_DATE , NULL , BIC1.OLD_COMPONENT_SEQUENCE_ID , BIC1.COMPONENT_SEQUENCE_ID) AND BIC3.BILL_SEQUENCE_ID = BIC2.BILL_SEQUENCE_ID AND BRD2.COMPONENT_REFERENCE_DESIGNATOR = BRD1.COMPONENT_REFERENCE_DESIGNATOR AND BIC3.EFFECTIVITY_DATE <= BIC2.EFFECTIVITY_DATE)

View Text - HTML Formatted

SELECT BRD1.ROWID ROW_ID
, BRD1.COMPONENT_REFERENCE_DESIGNATOR
, BRD1.LAST_UPDATE_DATE
, BRD1.LAST_UPDATED_BY
, BRD1.CREATION_DATE
, BRD1.CREATED_BY
, BRD1.LAST_UPDATE_LOGIN
, BRD1.REF_DESIGNATOR_COMMENT
, BIC1.IMPLEMENTATION_DATE
, BIC2.COMPONENT_SEQUENCE_ID
, BRD1.ACD_TYPE
, BRD1.CHANGE_NOTICE
, BRD1.ATTRIBUTE_CATEGORY
, BRD1.ATTRIBUTE1
, BRD1.ATTRIBUTE2
, BRD1.ATTRIBUTE3
, BRD1.ATTRIBUTE4
, BRD1.ATTRIBUTE5
, BRD1.ATTRIBUTE6
, BRD1.ATTRIBUTE7
, BRD1.ATTRIBUTE8
, BRD1.ATTRIBUTE9
, BRD1.ATTRIBUTE10
, BRD1.ATTRIBUTE11
, BRD1.ATTRIBUTE12
, BRD1.ATTRIBUTE13
, BRD1.ATTRIBUTE14
, BRD1.ATTRIBUTE15
, BRD1.REQUEST_ID
, BRD1.PROGRAM_APPLICATION_ID
, BRD1.PROGRAM_ID
, BRD1.PROGRAM_UPDATE_DATE
FROM BOM_REFERENCE_DESIGNATORS BRD1
, BOM_INVENTORY_COMPONENTS BIC1
, BOM_INVENTORY_COMPONENTS BIC2
WHERE BRD1.COMPONENT_SEQUENCE_ID = BIC1.COMPONENT_SEQUENCE_ID
AND NVL(BRD1.ACD_TYPE
, 1) <> 3
AND BIC2.BILL_SEQUENCE_ID = BIC1.BILL_SEQUENCE_ID
AND DECODE(BIC2.IMPLEMENTATION_DATE
, NULL
, BIC2.OLD_COMPONENT_SEQUENCE_ID
, BIC2.COMPONENT_SEQUENCE_ID) = DECODE(BIC1.IMPLEMENTATION_DATE
, NULL
, BIC1.OLD_COMPONENT_SEQUENCE_ID
, BIC1.COMPONENT_SEQUENCE_ID)
AND BIC1.EFFECTIVITY_DATE = (SELECT MAX(BIC3.EFFECTIVITY_DATE)
FROM BOM_REFERENCE_DESIGNATORS BRD2
, BOM_INVENTORY_COMPONENTS BIC3
WHERE BIC3.COMPONENT_SEQUENCE_ID = BRD2.COMPONENT_SEQUENCE_ID
AND DECODE(BIC3.IMPLEMENTATION_DATE
, NULL
, BIC3.OLD_COMPONENT_SEQUENCE_ID
, BIC3.COMPONENT_SEQUENCE_ID) = DECODE(BIC1.IMPLEMENTATION_DATE
, NULL
, BIC1.OLD_COMPONENT_SEQUENCE_ID
, BIC1.COMPONENT_SEQUENCE_ID)
AND BIC3.BILL_SEQUENCE_ID = BIC2.BILL_SEQUENCE_ID
AND BRD2.COMPONENT_REFERENCE_DESIGNATOR = BRD1.COMPONENT_REFERENCE_DESIGNATOR
AND BIC3.EFFECTIVITY_DATE <= BIC2.EFFECTIVITY_DATE)