VIEW: APPS.BOM_SUBSTITUTE_COMPONENTS_ERV

Source

View Text - Preformatted

SELECT BSC1.ROWID ROW_ID , MSI.PRIMARY_UOM_CODE , MSI.DESCRIPTION , BIC1.IMPLEMENTATION_DATE , BSC1.SUBSTITUTE_COMPONENT_ID , BSC1.LAST_UPDATE_DATE , BSC1.LAST_UPDATED_BY , BSC1.CREATION_DATE , BSC1.CREATED_BY , BSC1.LAST_UPDATE_LOGIN , BSC1.SUBSTITUTE_ITEM_QUANTITY , BIC2.COMPONENT_SEQUENCE_ID , BSC1.ACD_TYPE , BSC1.CHANGE_NOTICE , BSC1.ATTRIBUTE_CATEGORY , BSC1.ATTRIBUTE1 , BSC1.ATTRIBUTE2 , BSC1.ATTRIBUTE3 , BSC1.ATTRIBUTE4 , BSC1.ATTRIBUTE5 , BSC1.ATTRIBUTE6 , BSC1.ATTRIBUTE7 , BSC1.ATTRIBUTE8 , BSC1.ATTRIBUTE9 , BSC1.ATTRIBUTE10 , BSC1.ATTRIBUTE11 , BSC1.ATTRIBUTE12 , BSC1.ATTRIBUTE13 , BSC1.ATTRIBUTE14 , BSC1.ATTRIBUTE15 , BSC1.REQUEST_ID , BSC1.PROGRAM_APPLICATION_ID , BSC1.PROGRAM_ID , BSC1.PROGRAM_UPDATE_DATE , BSC1.ENFORCE_INT_REQUIREMENTS , ML.MEANING ENFORCE_INT_REQUIREMENTS_DESC , MSI.CONCATENATED_SEGMENTS COMPONENT_ITEM_NO FROM BOM_SUBSTITUTE_COMPONENTS BSC1 , BOM_BILL_OF_MATERIALS BOM , BOM_INVENTORY_COMPONENTS BIC1 , /* CURRENT COMPONENT RECORD */ BOM_INVENTORY_COMPONENTS BIC2 , /* COMPONENT BEING REFERENCED */ MTL_SYSTEM_ITEMS_VL MSI , MFG_LOOKUPS ML WHERE BSC1.COMPONENT_SEQUENCE_ID = BIC1.COMPONENT_SEQUENCE_ID AND BIC1.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID AND BSC1.SUBSTITUTE_COMPONENT_ID = MSI.INVENTORY_ITEM_ID AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND NVL(BSC1.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_SUBSTITUTE_COMPONENTS BSC2 , BOM_INVENTORY_COMPONENTS BIC3 WHERE BIC3.COMPONENT_SEQUENCE_ID = BSC2.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 BSC2.SUBSTITUTE_COMPONENT_ID = BSC1.SUBSTITUTE_COMPONENT_ID AND BIC3.EFFECTIVITY_DATE <= BIC2.EFFECTIVITY_DATE) AND ML.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS' AND ML.LOOKUP_CODE(+) = BSC1.ENFORCE_INT_REQUIREMENTS

View Text - HTML Formatted

SELECT BSC1.ROWID ROW_ID
, MSI.PRIMARY_UOM_CODE
, MSI.DESCRIPTION
, BIC1.IMPLEMENTATION_DATE
, BSC1.SUBSTITUTE_COMPONENT_ID
, BSC1.LAST_UPDATE_DATE
, BSC1.LAST_UPDATED_BY
, BSC1.CREATION_DATE
, BSC1.CREATED_BY
, BSC1.LAST_UPDATE_LOGIN
, BSC1.SUBSTITUTE_ITEM_QUANTITY
, BIC2.COMPONENT_SEQUENCE_ID
, BSC1.ACD_TYPE
, BSC1.CHANGE_NOTICE
, BSC1.ATTRIBUTE_CATEGORY
, BSC1.ATTRIBUTE1
, BSC1.ATTRIBUTE2
, BSC1.ATTRIBUTE3
, BSC1.ATTRIBUTE4
, BSC1.ATTRIBUTE5
, BSC1.ATTRIBUTE6
, BSC1.ATTRIBUTE7
, BSC1.ATTRIBUTE8
, BSC1.ATTRIBUTE9
, BSC1.ATTRIBUTE10
, BSC1.ATTRIBUTE11
, BSC1.ATTRIBUTE12
, BSC1.ATTRIBUTE13
, BSC1.ATTRIBUTE14
, BSC1.ATTRIBUTE15
, BSC1.REQUEST_ID
, BSC1.PROGRAM_APPLICATION_ID
, BSC1.PROGRAM_ID
, BSC1.PROGRAM_UPDATE_DATE
, BSC1.ENFORCE_INT_REQUIREMENTS
, ML.MEANING ENFORCE_INT_REQUIREMENTS_DESC
, MSI.CONCATENATED_SEGMENTS COMPONENT_ITEM_NO
FROM BOM_SUBSTITUTE_COMPONENTS BSC1
, BOM_BILL_OF_MATERIALS BOM
, BOM_INVENTORY_COMPONENTS BIC1
, /* CURRENT COMPONENT RECORD */ BOM_INVENTORY_COMPONENTS BIC2
, /* COMPONENT BEING REFERENCED */ MTL_SYSTEM_ITEMS_VL MSI
, MFG_LOOKUPS ML
WHERE BSC1.COMPONENT_SEQUENCE_ID = BIC1.COMPONENT_SEQUENCE_ID
AND BIC1.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BSC1.SUBSTITUTE_COMPONENT_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NVL(BSC1.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_SUBSTITUTE_COMPONENTS BSC2
, BOM_INVENTORY_COMPONENTS BIC3
WHERE BIC3.COMPONENT_SEQUENCE_ID = BSC2.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 BSC2.SUBSTITUTE_COMPONENT_ID = BSC1.SUBSTITUTE_COMPONENT_ID
AND BIC3.EFFECTIVITY_DATE <= BIC2.EFFECTIVITY_DATE)
AND ML.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS'
AND ML.LOOKUP_CODE(+) = BSC1.ENFORCE_INT_REQUIREMENTS