SELECT
  CaseEvidence.evidenceID,
  CaseEvidence.caseID,
  CaseEvidence.assessmentID,
  CaseEvidence.effectiveFrom,
  CaseEvidence.statusCode,
  CaseEvidence.previousEvidenceID,
  CaseEvidence.versionNo
INTO
  :evidenceID,
  :caseID,
  :assessmentID,
  :effectiveFrom,
  :statusCode,
  :previousEvidenceID,
  :versionNo 
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 = ( 
    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 
          ) 
        ) 
      ) 
    ) 
  )