SELECT
  COUNT(*)
INTO
  :numOfRecords 
FROM
  AppealRelationship,
  Appeal 
WHERE AppealRelationship.AppealCaseID =Appeal.CaseID 
  AND Appeal.AppealTypeCode = :caseAppealType 
  AND AppealRelationship.statuscode <> :appealStatusCode 
  AND AppealRelationship.CaseID IN 
  ( 
  SELECT
    ISSUEDELIVERY.CASEID 
  FROM
    CASEHEADER,
    ISSUEDELIVERY 
  WHERE CASEHEADER.CASEID = ISSUEDELIVERY.RELATEDCASEID 
    AND ISSUEDELIVERY.CASEID = :caseID 
  
  UNION
    
  SELECT
    PRODUCTDELIVERY.CASEID 
  FROM
    PRODUCTDELIVERY 
  WHERE PRODUCTDELIVERY.CASEID = :caseID 
  
  UNION
    
  SELECT
    CASEHEADER.CASEID 
  FROM
    CASEHEADER 
  WHERE CASEHEADER.INTEGRATEDCASEID IS NULL 
    AND CASEHEADER.CASEID = :caseID 
  )