Operation Details
Description: Searches the nearest evidence on a specified case for the the specified date and status codes. The first step is to calculate the maximum effective from date for the required caseID and statusCodes before a specified cut-off date - where no date is available null is used. Once this is done the resulting date is used to select all data from the corresponding case evidence tree.
Sterotype: nsmulti
SQL 
SELECT
  allitems.caseEvidenceTreeID,
  allitems.caseID,
  allitems.effectiveFrom,
  allitems.statusCode,
  allitems.previousEvidenceID,
  allitems.versionNo
INTO
  :caseEvidenceTreeID,
  :caseID,
  :effectiveFrom,
  :statusCode,
  :previousEvidenceID,
  :versionNo 
FROM
  
  ( 
  SELECT
    MAX(effectiveFrom) AS maxeffectivefrom 
  FROM
    
    ( 
    SELECT
      * 
    FROM
      CaseEvidenceTree 
    WHERE effectiveFrom IS NOT NULL 
      AND effectiveFrom < :effectiveFrom 
      AND caseID = :caseID 
      AND 
      (
        statusCode = :statusCode 
        OR statusCode = :statusCode2 
      ) 
    
    UNION
      
    SELECT
      * 
    FROM
      CaseEvidenceTree 
    WHERE effectiveFrom IS NULL 
      AND caseID = :caseID 
      AND 
      (
        statusCode = :statusCode 
        OR statusCode = :statusCode2 
      ) 
    ) temp 
  ) maxitems,
  CaseEvidenceTree allitems 
WHERE 
  (
    
    (
      maxeffectivefrom IS NOT NULL 
      AND maxeffectivefrom = allitems.effectiveFrom 
    ) 
    OR 
    (
      maxeffectivefrom IS NULL 
      AND allitems.effectiveFrom IS NULL 
    ) 
  ) 
  AND allitems.caseID = :caseID 
  AND 
  (
    allitems.statusCode = :statusCode 
    OR allitems.statusCode = :statusCode2 
  )