Operation Details
Description: Returns the resolution details for each appealed case for an appeal case. Only active and approved appeal relationship records are returned.
Sterotype: nsmulti
SQL 
SELECT
  AppealRelationship.appealRelationshipID,
  AppealRelationship.priorAppealCaseID AS caseID,
  CaseHeader.caseReference,
  Appeal.appealTypeCode AS appealedCaseTypeCode,
  AppealRelationship.resolutionCode,
  '1' AS priorAppealIndicator
INTO
  :appealRelationshipID,
  :caseID,
  :caseReference,
  :appealedCaseTypeCode,
  :resolutionCode,
  :priorAppealIndicator 
FROM
  AppealRelationship,
  CaseHeader,
  Appeal 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.recordStatus = :recordStatus 
  AND AppealRelationship.statusCode = :statusCode 
  AND AppealRelationship.priorAppealCaseID = CaseHeader.caseID 
  AND CaseHeader.caseTypeCode! ='CT10' 
  AND AppealRelationship.priorAppealCaseID = Appeal.caseID 
  AND AppealRelationship.priorAppealCaseID IS NOT NULL 

UNION
  
SELECT
  AppealRelationship.appealRelationshipID,
  AppealRelationship.caseID,
  CaseHeader.caseReference,
  CaseHeader.caseTypeCode AS appealedCaseTypeCode,
  AppealRelationship.resolutionCode,
  '0' AS priorAppealIndicator 
FROM
  AppealRelationship,
  CaseHeader 
WHERE AppealRelationship.appealCaseID = :appealCaseID 
  AND AppealRelationship.recordStatus = :recordStatus 
  AND AppealRelationship.statusCode = :statusCode 
  AND AppealRelationship.caseID = CaseHeader.caseID 
  AND CaseHeader.caseTypeCode! ='CT10' 
  AND AppealRelationship.priorAppealCaseID IS NULL