SELECT COUNT(*)
FROM BOM_BILL_OF_MATERIALS BOM /* NEEDED TO GET ORG_ID */ ,
BOM_INVENTORY_COMPONENTS BIC ,
MTL_SYSTEM_ITEMS_B_KFV MSI ,
MTL_SYSTEM_ITEMS_TL MSIT ,
FND_COMMON_LOOKUPS FCL ,
MFG_LOOKUPS ML ,
MFG_LOOKUPS ML1
WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSIT.ORGANIZATION_ID
AND MSIT.LANGUAGE = USERENV('LANG')
AND ML.LOOKUP_CODE(+) = BIC.WIP_SUPPLY_TYPE
AND ML.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND ML1.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS'
AND ML1.LOOKUP_CODE(+) = BIC.ENFORCE_INT_REQUIREMENTS
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.LOOKUP_CODE(+) = MSI.ITEM_TYPE
AND FCL.APPLICATION_ID(+) = 401
AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2