SELECT 
CaseHeader.caseID,
CaseHeader.caseReference,
CaseHeader.caseTypeCode,
CaseHeader.integratedCaseType,
CaseHeader.statusCode,
OrgObjectLink.orgObjectLinkID,
VerificationsCount.verificationsCount,
IssueCount.issuesCount,
InEditEvidence.inEditEvidenceCount

INTO
:caseID,
:caseReference,
:caseType,
:integratedCaseType,
:statusCode,
:ownerOrgObjectLinkID,
:verificationsCount,
:issuesCount,
:inEditEvidenceCount

FROM 
OrgObjectLink,
CaseHeader 
  LEFT OUTER JOIN (
SELECT
  Count(Verification.verificationID) as verificationsCount,
  Verification.verificationLinkedID

FROM
  Verification,
  VDIEDLink,
  EvidenceDescriptor

WHERE 
Verification.verificationLinkedID = :caseID AND
Verification.verificationLinkedType = :integratedCaseLinkType AND 
Verification.VDIEDLinkID = VDIEDLink.VDIEDLinkID AND 
Verification.verificationStatus = :nonVerifiedStatus AND 
VDIEDLink.evidenceDescriptorID = EvidenceDescriptor.evidenceDescriptorID AND 
  (
    EvidenceDescriptor.statusCode = :evidenceDescriptorActive
    OR EvidenceDescriptor.statusCode = :evidenceDescriptorInedit
  )
GROUP BY Verification.verificationLinkedID
) VerificationsCount 

ON (VerificationsCount.verificationLinkedID = CaseHeader.caseID)

  LEFT OUTER JOIN (
SELECT 
Count(IssueDelivery.caseID) as issuesCount,
IssueDelivery.relatedCaseID

FROM 
IssueDelivery, 
CaseHeader

WHERE
IssueDelivery.relatedCaseID = :caseID AND
IssueDelivery.relatedCaseID = CaseHeader.caseID AND
CaseHeader.statusCode <> :cancelledCaseStatus AND
CaseHeader.statusCode <> :closedCaseStatus 

GROUP BY IssueDelivery.relatedCaseID
) IssueCount 
ON (IssueCount.relatedCaseID = CaseHeader.caseID)

  LEFT OUTER JOIN (
SELECT 
Count(EvidenceDescriptor.caseID) as inEditEvidenceCount, 
EvidenceDescriptor.caseID

FROM EvidenceDescriptor, CaseHeader 
WHERE 
EvidenceDescriptor.caseID = :caseID AND
EvidenceDescriptor.caseID  = CaseHeader.caseID AND 
EvidenceDescriptor.statusCode = :evidenceDescriptorInedit

GROUP BY EvidenceDescriptor.caseID
) InEditEvidence
ON (InEditEvidence.caseID  = CaseHeader.caseID)

WHERE 
CaseHeader.caseID = :caseID AND
OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID