Operation Details
Description: Determine if the appealed case and prior case combination is already on an active appeal case.
Sterotype: nsmulti
SQL 
SELECT
  appealCaseID
INTO
  :appealCaseID 
FROM
  AppealRelationship,
  CaseHeader 
WHERE AppealRelationship.caseID = :caseID 
  AND priorAppealCaseID = :priorCaseID 
  AND appealCaseID = CaseHeader.caseID 
  AND recordStatus = :normalRecordStatus 
  AND 
  (
    CaseHeader.statusCode = :activeStatusCode 
    OR CaseHeader.statusCode = :openStatusCode 
    OR CaseHeader.statusCode = :approvedStatusCode 
  ) 

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