SELECT allitems.SUCCESSIONID, allitems.EVIDENCETYPE, allitems.RELATEDID, allitems.PARTICIPANTID, allitems.STATUSCODE, CONCERNROLE.CONCERNROLENAME, verifications.verCount
INTO  :successionID, :evidenceType, :evidenceID, :participantID, :edsCode, :concernRoleName, :verificationsCount
FROM
  (SELECT
     SUCCESSIONID, PARTICIPANTID, EVIDENCETYPE, MAX(effectiveFrom) AS maxeffectivefrom
   FROM
     EVIDENCEDESCRIPTOR 
   WHERE 
     caseID = :caseID
     AND (statusCode = :edsCode1 OR statusCode = :edsCode2)
   GROUP BY SUCCESSIONID, PARTICIPANTID, EVIDENCETYPE, STATUSCODE
  ) maxitems,

  EVIDENCEDESCRIPTOR allitems, CONCERNROLE,
(Select COUNT(*) as verCount
FROM VERIFICATION v
INNER JOIN 
VDIEDLINK vlink ON
v.VDIEDLINKID = vlink.VDIEDLINKID
INNER JOIN 
EVIDENCEDESCRIPTOR evDesc ON
vlink.EVIDENCEDESCRIPTORID=evDesc.EVIDENCEDESCRIPTORID
WHERE
v.VERIFICATIONSTATUS=:verStatusCode
AND
evDesc.CASEID=:caseID
AND
(evDesc.STATUSCODE =:edsCode1 OR evDesc.STATUSCODE =:edsCode2)) verifications

    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 =:edsCode1 OR allitems.STATUSCODE =:edsCode2)
AND  allitems.PARTICIPANTID = CONCERNROLE.CONCERNROLEID