SELECT KFV.ORGANIZATION_ID , KFV.INVENTORY_ITEM_ID SUBCOMPONENT_ID , KFV.DESCRIPTION DESCRIPTION , KFV.CONCATENATED_SEGMENTS , BOM.ASSEMBLY_ITEM_ID COMPONENT_ID FROM MTL_SYSTEM_ITEMS_VL KFV , BOM_INVENTORY_COMPONENTS BIC , BOM_BILL_OF_MATERIALS BOM WHERE BOM.ORGANIZATION_ID = KFV.ORGANIZATION_ID AND BIC.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID AND TRUNC(SYSDATE) BETWEEN TRUNC( BIC.EFFECTIVITY_DATE) AND NVL(BIC.DISABLE_DATE , TRUNC(SYSDATE)) AND KFV.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND EXISTS (SELECT COMP.COMPONENT_ID FROM CS_SR_INV_COMPONENTS_V COMP WHERE COMP.ORGANIZATION_ID = KFV.ORGANIZATION_ID AND COMP.COMPONENT_ID = BOM.ASSEMBLY_ITEM_ID)