Operation Details
Description: This operation is used to read the nearest record in the past to the input date, for a given Case, which has an associated Case Evidence Type record of the specified type.
Sterotype: ns
SQL 
SELECT
  CaseEvidence.evidenceID,
  CaseEvidence.caseID,
  CaseEvidence.assessmentID,
  CaseEvidence.effectiveFrom,
  CaseEvidence.statusCode,
  CaseEvidence.versionNo,
  CaseEvidenceType.caseEvidenceTypeID,
  CaseEvidenceType.evidenceTypeCode,
  CaseEvidenceType.evidenceFormName,
  CaseEvidenceType.statusCode,
  CaseEvidenceType.versionNo
INTO
  :evidenceID,
  :caseID,
  :assessmentID,
  :effectiveFrom,
  :statusCode,
  :caseEvidenceVersionNo,
  :caseEvidenceTypeID,
  :evidenceTypeCode,
  :evidenceFormName,
  :evidenceTypeStatusCode,
  :caseEvidenceTypeVersionNo 
FROM
  CaseEvidence,
  CaseEvidenceType 
WHERE CaseEvidence.caseID = :caseID 
  AND 
  (
    CaseEvidence.assessmentID = :assessmentID 
    OR CaseEvidence.assessmentID IS NULL 
  ) 
  AND 
  (
    CaseEvidence.statusCode = :statusCode 
    OR CaseEvidence.statusCode = :statusCode2 
  ) 
  AND CaseEvidenceType.evidenceID = CaseEvidence.evidenceID 
  AND CaseEvidenceType.evidenceTypeCode = :evidenceTypeCode 
  AND 
  (
    CaseEvidence.effectiveFrom = ( 
    SELECT
      MAX(CaseEvidence.effectiveFrom) 
    FROM
      CaseEvidence 
    WHERE CaseEvidence.caseID = :caseID 
      AND 
      (
        CaseEvidence.assessmentID = :assessmentID 
        OR CaseEvidence.assessmentID IS NULL 
      ) 
      AND 
      (
        CaseEvidence.statusCode = :statusCode 
        OR CaseEvidence.statusCode = :statusCode2 
      ) 
      AND 
      (
        CaseEvidence.effectiveFrom < :effectiveFrom 
      ) 
    ) 
    OR 
    (
      CaseEvidence.effectiveFrom IS NULL 
      AND 
      (
        NOT EXISTS 
        ( 
        SELECT
          * 
        FROM
          CaseEvidence 
        WHERE CaseEvidence.caseID = :caseID 
          AND 
          (
            CaseEvidence.assessmentID = :assessmentID 
            OR CaseEvidence.assessmentID IS NULL 
          ) 
          AND 
          (
            CaseEvidence.statusCode = :statusCode 
            OR CaseEvidence.statusCode = :statusCode2 
          ) 
          AND 
          (
            CaseEvidence.effectiveFrom < :effectiveFrom 
          ) 
        ) 
      ) 
    ) 
  )