Operation Details
Description: Returns all the active appealed cases for the given appealCaseID
Sterotype: nsmulti
SQL 
SELECT
  AppealRelationship.caseID,
  AppealRelationship.priorAppealCaseID
INTO
  :caseID,
  :priorAppealCaseID 
FROM
  AppealRelationship,
  CaseHeader,
  Appeal 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.priorAppealCaseID = CaseHeader.caseID 
  AND AppealRelationship.priorAppealCaseID = Appeal.caseID 
  AND AppealRelationship.priorAppealCaseID IS NOT NULL 
  AND AppealRelationship.recordStatus = :normalRecordStatus 
  AND CaseHeader.statusCode IN ( :openStatusCode, :approvedStatusCode, :activeStatusCode) 

UNION
  
SELECT
  AppealRelationship.caseID,
  AppealRelationship.priorAppealCaseID 
FROM
  AppealRelationship,
  CaseHeader 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.caseID = CaseHeader.caseID 
  AND AppealRelationship.priorAppealCaseID IS NULL 
  AND AppealRelationship.recordStatus = :normalRecordStatus 
  AND CaseHeader.statusCode IN ( :openStatusCode, :approvedStatusCode, :activeStatusCode)