Operation Details
Description: <span lang="EN-IE" style="FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-IE; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">Reads the details of the appeal case including the case owner, hearing details, case status and resolution information.</span>
Sterotype: ns
SQL 
SELECT
CaseHeader.caseReference,
CaseHeader.caseTypeCode,
CaseHeader.receivedDate,
CaseHeader.registrationDate,
CaseHeader.caseID,
CaseStatus.statusCode,
CaseStatus.startDate,
Appeal.appealTypeCode,
Appeal.deadlineDate,
HearingDecisionDetails.resolutionCode,
HearingDecisionDetails.decisionDate,
OrgObjectLink.orgObjectLinkID,
NextOrLastHearing.hearingDateTime,
NextOrLastHearing.hearingStatusCode

INTO
:caseReference,
:caseTypeCode,
:receivedDate,
:creationDate,
:caseID,
:caseStatusCode,
:caseStatusStartDate,
:appealTypeCode,
:deadlineDate,
:resolutionCode,
:decisionDate,
:ownerOrgObjectLinkID,
:hearingDateTime,
:hearingStatusCode

FROM
CaseHeader,
OrgObjectLink,
CaseStatus,
Appeal

LEFT OUTER JOIN 
(
  SELECT 
  Hearing.scheduledDateTime AS hearingDateTime,
  Hearing.statusCode AS hearingStatusCode,
  Hearing.caseID
  FROM 
  Hearing
  WHERE 
  Hearing.caseID = :caseID AND 
  Hearing.scheduledDateTime = (
    SELECT
    MAX(Hearing.scheduledDateTime) 
    FROM 
    Hearing
    WHERE 
    Hearing.caseID = :caseID
  )
) NextOrLastHearing
ON (NextOrLastHearing.caseID = Appeal.caseID)

LEFT OUTER JOIN 
(
 SELECT 
  HearingDecision.caseID,
  HearingDecision.resolutionCode,
  HearingDecision.decisionDate

 FROM 
  HearingDecision

) HearingDecisionDetails
ON (HearingDecisionDetails.caseID = Appeal.caseID),

ConcernRole

WHERE 
CaseHeader.caseID = :caseID AND
 (
  CaseStatus.caseID = CaseHeader.caseID AND (
  CaseStatus.endDate IS NULL OR
  CaseStatus.endDate > :currentDate
)
) AND
Appeal.caseID = CaseHeader.caseID AND
ConcernRole.concernRoleID = CaseHeader.concernRoleID AND
OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID