SELECT
  CaseHeader.caseReference,
  CaseHeader.statuscode,
  AppealRelationship.resolutioncode,
  AppealRelationship.appealCaseID,
  Appeal.deadlinedate,
  Appeal.appealtypecode,
  ConcernRole.concernrolename,
  OrgObjectLink.orgObjectReference,
  OrgObjectLink.orgObjectType,
  OrgObjectLink.userName
INTO
  :caseReference,
  :status,
  :resolution,
  :appealCaseID,
  :deadlinedate,
  :appealCaseType,
  :appelant,
  :orgObjectReference,
  :orgObjectType,
  :userName 
FROM
  CaseHeader,
  Appeal,
  AppealRelationship ,
  ConcernRole,
  OrgObjectLink 
WHERE OrgObjectLink.OrgObjectLinkID = CaseHeader.OwnerOrgObjectLinkID 
  AND AppealRelationship.AppealCaseID =Appeal.CaseID 
  AND AppealRelationship.AppealCaseID =CaseHeader.CaseID 
  AND AppealRelationship.statuscode <> :appealStatusCode 
  AND ConcernRole.ConcernRoleID =CaseHeader.ConcernRoleID 
  AND Appeal.AppealTypeCode = :caseAppealType 
  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 
  ) 
ORDER BY DeadlineDate desc