Operation Details
Description: Retrieves the details of the active case evidence tree record with the next highest effective from date after that supplied.
Sterotype: ns
SQL 
SELECT
  CaseEvidenceTree.caseEvidenceTreeID,
  CaseEvidenceTree.caseID,
  CaseEvidenceTree.effectiveFrom,
  CaseEvidenceTree.statusCode,
  CaseEvidenceTree.previousEvidenceID,
  CaseEvidenceTree.versionNo
INTO
  :caseEvidenceTreeID,
  :caseID,
  :effectiveFrom,
  :statusCode,
  :previousEvidenceID,
  :versionNo 
FROM
  CaseEvidenceTree 
WHERE CaseEvidenceTree.caseID = :caseID 
  AND CaseEvidenceTree.statusCode = :statusCode 
  AND CaseEvidenceTree.effectiveFrom = ( 
  SELECT
    MIN(effectiveFrom) 
  FROM
    CaseEvidenceTree 
  WHERE CaseEvidenceTree.caseID = :caseID 
    AND CaseEvidenceTree.statusCode = :statusCode 
    AND CaseEvidenceTree.effectiveFrom > :effectiveFrom 
  )