SELECT
  COUNT (*)
INTO
  :recordCount 
FROM
  CaseHeader,
  ISPSpendDown,
  ProductDelivery,
  Product 
WHERE CaseHeader.integratedCaseID = :caseID 
  AND 
  (
    CaseHeader.statusCode = :statusCode1 
    OR CaseHeader.statusCode = :statusCode2 
    OR CaseHeader.statusCode = :statusCode3 
    OR CaseHeader.statusCode = :statusCode4 
  ) 
  AND ProductDelivery.caseID = CaseHeader.caseID 
  AND Product.typeCode = ProductDelivery.productType 
  AND Product.name = :productName 
  AND ISPSpendDown.caseID = CaseHeader.caseID 
  AND ISPSpendDown.statusCode = :sdStatusCode 
  AND ISPSpendDown.startDate <= :checkEligibilityDate 
  AND ISPSpendDown.endDate >= :checkEligibilityDate