Operation Details
Description: Retrieves the details of the evidence record with the next highest effective from date after that supplied.
Sterotype: ns
SQL 
SELECT
  CaseEvidence.evidenceID,
  CaseEvidence.caseID,
  CaseEvidence.assessmentID,
  CaseEvidence.effectiveFrom,
  CaseEvidence.statusCode,
  CaseEvidence.versionNo
INTO
  :evidenceID,
  :caseID,
  :assessmentID,
  :effectiveFrom,
  :statusCode,
  :versionNo 
FROM
  CaseEvidence 
WHERE CaseEvidence.caseID = :caseID 
  AND 
  (
    CaseEvidence.assessmentID = :assessmentID 
    OR CaseEvidence.assessmentID IS NULL 
  ) 
  AND CaseEvidence.statusCode = :statusCode 
  AND CaseEvidence.effectiveFrom = ( 
  SELECT
    MIN(CaseEvidence.effectiveFrom) 
  FROM
    CaseEvidence 
  WHERE CaseEvidence.caseID = :caseID 
    AND 
    (
      CaseEvidence.assessmentID = :assessmentID 
      OR CaseEvidence.assessmentID IS NULL 
    ) 
    AND CaseEvidence.statusCode = :statusCode 
    AND CaseEvidence.effectiveFrom > :effectiveFrom 
  )