SELECT
  ConcernRole.primaryAlternateID,
  Person.dateOfBirth,
  Person.dateOfDeath,
  Person.gender,
  Address.addressID,
  Address.addressData,
  PhoneNumber.phoneNumberID,
  EmailAddress.emailAddress,
  IncPartRoleResult.incidentCount,
  IssPartRole.issueCount,
  ConRoleDuplicate.duplicateCount,
  ConRoleDuplicate.originalConcernRoleID,
  InvPartRole.investigationRoleCount
INTO
  :primaryAlternateID,
  :dateOfBirth,
  :dateOfDeath,
  :gender,
  :addressID,
  :addressData,
  :phoneNumberID,
  :emailAddress,
  :numIncidents,
  :numIssues,
  :numDuplicates,
  :originalConcernRoleID,
  :numInvestigations
FROM
  ConcernRole 
  
  LEFT OUTER JOIN
  
  Person 
  ON (ConcernRole.concernRoleID = Person.concernRoleID) 

  LEFT OUTER JOIN
  
  Address 
  ON (ConcernRole.primaryAddressID = Address.addressID) 
  
  LEFT OUTER JOIN
  
  PhoneNumber 
  ON (ConcernRole.primaryPhoneNumberID = PhoneNumber.phoneNumberID) 
  
  LEFT OUTER JOIN
  
  EmailAddress 
  ON (ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID AND EmailAddress.statusCode = 'RST1')
  
  LEFT OUTER JOIN
  (
  Select 
    ConcernRoleDuplicate.duplicateConcernRoleID,
    ConcernRoleDuplicate.originalConcernRoleID,
    COUNT(ConcernRoleDuplicate.duplicateConcernRoleID) as duplicateCount
  FROM 
    ConcernRoleDuplicate
  WHERE
    ConcernRoleDuplicate.duplicateConcernRoleID = :concernRoleID
    AND 
  ConcernRoleDuplicate.statusCode = :duplicateStatusCode
  GROUP
  BY ConcernRoleDuplicate.duplicateConcernRoleID, ConcernRoleDuplicate.originalConcernRoleID     
  ) ConRoleDuplicate
  ON (ConRoleDuplicate.duplicateConcernRoleID = ConcernRole.concernRoleID)
   
  
  LEFT OUTER JOIN
  
  (
  Select
    IncidentParticipantRole.concernRoleID,
    COUNT(IncidentParticipantRole.concernRoleID) as incidentCount
  FROM 
    IncidentParticipantRole,
    Incident
  WHERE
    IncidentParticipantRole.concernRoleID = :concernRoleID
    AND
    IncidentParticipantRole.role = :incidentParticipantRole
    AND 
    IncidentParticipantRole.recordStatus = :recordStatusCode
    AND
    IncidentParticipantRole.incidentID = Incident.incidentID
    AND 
    Incident.status = :incidentStatusCode    
  GROUP
  BY IncidentParticipantRole.concernRoleID  
  ) IncPartRoleResult
  ON (IncPartRoleResult.concernRoleID = ConcernRole.concernRoleID)
  
  LEFT OUTER JOIN
  (
  Select 
    CaseParticipantRole.participantRoleID,
    COUNT(CaseHeader.caseID) as issueCount
  FROM 
    CaseHeader,
    CaseParticipantRole
  WHERE
    CaseHeader.caseTypeCode = :issueCaseTypeCode
    AND
    CaseHeader.statusCode <> :cancelledCaseStatus
    AND
    CaseHeader.statusCode <> :closedCaseStatus    
    AND
    CaseHeader.caseID = CaseParticipantRole.caseID
    AND
    CaseParticipantRole.participantRoleID = :concernRoleID
    AND 
    CaseParticipantRole.typeCode = :primaryClientTypeCode
  GROUP
  BY CaseParticipantRole.participantRoleID     
  ) IssPartRole
  ON (IssPartRole.participantRoleID = ConcernRole.concernRoleID)
  
  LEFT OUTER JOIN
  (
  
  
  Select
  COUNT(investigationCount.caseID) as investigationRoleCount,
  investigationCount.participantRoleID
  FROM
  (
  
  Select 
  investigationRoles.caseID,
  investigationRoles.participantRoleID,
  SUM(investigationRoles.investDelCount)
  From 
  (
  
  Select 
    COUNT(CaseHeader.caseID) as investDelCount,
    CaseHeader.caseID,
    CaseParticipantRole.participantRoleID
  FROM 
    CaseHeader,
    CaseParticipantRole
  WHERE
    CaseHeader.caseTypeCode = :investigationCaseTypeCode
    AND
    CaseHeader.statusCode <> :cancelledCaseStatus
    AND
    CaseHeader.statusCode <> :closedCaseStatus
    AND
    CaseHeader.caseID = CaseParticipantRole.caseID
    AND
    CaseParticipantRole.participantRoleID = :concernRoleID
    AND 
    CaseParticipantRole.typeCode = :primaryClientTypeCode
    GROUP
    BY CaseHeader.caseID, CaseParticipantRole.participantRoleID
UNION
  Select 
    COUNT(AllegationRole.allegationID) as investDelCount,
    CaseHeader.caseID,
    CaseParticipantRole.participantRoleID
  From
    Allegation,
    AllegationRole,
    CaseParticipantRole,
    CaseHeader
  WHERE
    AllegationRole.allegationID = Allegation.allegationID
    AND 
    Allegation.caseID = CaseHeader.caseID
    AND 
    CaseHeader.caseID = CaseParticipantRole.caseID
    AND
    CaseHeader.caseTypeCode = :investigationCaseTypeCode
    AND
    CaseHeader.statusCode <> :cancelledCaseStatus
    AND
    CaseHeader.statusCode <> :closedCaseStatus
    AND
    CaseParticipantRole.participantRoleID = :concernRoleID
    GROUP
    BY CaseHeader.caseID, CaseParticipantRole.participantRoleID) investigationRoles
    GROUP BY
    investigationRoles.caseID, investigationRoles.participantRoleID) investigationCount GROUP BY investigationCount.participantRoleID  
  
   ) InvPartRole
    ON (InvPartRole.participantRoleID = ConcernRole.concernRoleID)  
 WHERE ConcernRole.concernRoleID = :concernRoleID