SELECT
  AppealRelationship.appealRelationshipID,
  AppealRelationship.priorAppealCaseID AS caseID,
  caseReference,
  Appeal.appealTypeCode AS appealedCaseType,
  AppealRelationship.recordStatus,
  AppealRelationship.statusCode,
  AppealRelationship.receivedDate,
  AppealRelationship.receiptNoticeIndicator
INTO
  :appealRelationshipID,
  :caseID,
  :caseReference,
  :appealedCaseType,
  :recordStatus,
  :statusCode,
  :receivedDate,
  :receiptNoticeIndicator 
FROM
  AppealRelationship,
  CaseHeader,
  Appeal 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.CaseID = CaseHeader.caseID 
  AND AppealRelationship.priorAppealCaseID = Appeal.caseID 
  AND caseTypeCode = :caseTypeCode 
  AND AppealRelationship.priorAppealCaseID IS NOT NULL 

UNION ALL
  
  ( 
  SELECT
    AppealRelationship.appealRelationshipID,
    AppealRelationship.caseID AS caseID,
    caseReference,
    Appeal.appealTypeCode AS appealedCaseType,
    AppealRelationship.recordStatus,
    AppealRelationship.statusCode,
    AppealRelationship.receivedDate,
    AppealRelationship.receiptNoticeIndicator 
  FROM
    AppealRelationship,
    CaseHeader,
    Appeal 
  WHERE AppealRelationship.appealCaseID = :appealCaseID 
    AND AppealRelationship.caseID = CaseHeader.caseID 
    AND AppealRelationship.caseID = Appeal.caseID 
    AND caseTypeCode = :caseTypeCode 
    AND AppealRelationship.priorAppealCaseID IS NOT NULL 
  
  UNION
    
  SELECT
    AppealRelationship.appealRelationshipID,
    AppealRelationship.caseID AS caseID,
    caseReference,
    caseTypeCode AS appealedCaseType,
    AppealRelationship.recordStatus,
    AppealRelationship.statusCode,
    AppealRelationship.receivedDate,
    AppealRelationship.receiptNoticeIndicator 
  FROM
    AppealRelationship,
    CaseHeader,
    IssueDelivery 
  WHERE AppealRelationship.appealCaseid = :appealCaseID 
    AND AppealRelationship.caseID =IssueDelivery.caseID 
    AND IssueDelivery.caseID =CaseHeader.caseID 
    AND caseTypeCode = :caseTypeCode 
    AND AppealRelationship.priorAppealCaseID IS NULL 
  )