SELECT allitems.SUCCESSIONID, allitems.EVIDENCETYPE, allitems.RELATEDID, allitems.PARTICIPANTID
INTO  :successionID, :evidenceType, :evidenceID, :participantID
FROM
  (SELECT
      SUCCESSIONID, PARTICIPANTID, EVIDENCETYPE, MAX(effectiveFrom) AS maxeffectivefrom
    FROM
    ( 
      SELECT
        * 
      FROM
        EVIDENCEDESCRIPTOR 
      WHERE effectiveFrom IS NOT NULL 
        AND caseID = :caseID 
        AND statusCode = :statusCode
    UNION
      SELECT
        * 
      FROM
        EVIDENCEDESCRIPTOR 
      WHERE effectiveFrom IS NULL 
        AND caseID = :caseID 
        AND statusCode = :statusCode 
    ) all_maxitems
    GROUP BY SUCCESSIONID, PARTICIPANTID, EVIDENCETYPE
  ) maxitems,
  EVIDENCEDESCRIPTOR allitems
    WHERE
        allitems.successionID = maxitems.successionID
      AND
        (
          (allitems.effectiveFrom IS NOT NULL AND (allitems.effectiveFrom=maxitems.maxeffectivefrom))
          OR
          (allitems.effectiveFrom IS NULL AND maxitems.maxeffectivefrom IS NULL)
        )
      AND
        allitems.PARTICIPANTID = maxitems.PARTICIPANTID
      AND
        allitems.EVIDENCETYPE = maxitems.EVIDENCETYPE
      AND
        allitems.CASEID = :caseID
      AND 
        allitems.STATUSCODE = :statusCode