SELECT
  appealRelationshipID,
  priorAppealCaseID AS caseID,
  CaseHeader.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,
  ProductDelivery 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.priorAppealCaseID = CaseHeader.caseID 
  AND AppealRelationship.priorAppealCaseID = Appeal.caseID 
  AND AppealRelationship.caseID = ProductDelivery.caseID 
  AND AppealRelationship.priorAppealCaseID IS NOT NULL 

UNION ALL
  
  ( 
  SELECT
    AppealRelationship.appealRelationshipID,
    AppealRelationship.caseID AS caseID,
    CaseHeader.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 AppealRelationship.priorAppealCaseID IS NOT NULL 
  
  UNION
    
  SELECT
    AppealRelationship.appealRelationshipID,
    AppealRelationship.caseID AS caseID,
    CaseHeader.caseReference,
    CaseHeader.caseTypeCode AS appealedCaseType,
    AppealRelationship.recordStatus,
    AppealRelationship.statusCode,
    AppealRelationship.receivedDate,
    AppealRelationship.receiptNoticeIndicator 
  FROM
    AppealRelationship,
    CaseHeader 
  WHERE AppealRelationship.appealCaseID = :appealCaseID 
    AND AppealRelationship.caseID = CaseHeader.caseID 
    AND AppealRelationship.priorAppealCaseID IS NULL 
  )