SELECT
  ConcernRole.primaryAlternateID,
  ProspectPerson.dateOfBirth,
  ProspectPerson.dateOfDeath,
  ProspectPerson.gender,
  Address.addressID,
  Address.addressData,
  PhoneNumber.phoneNumberID,
  EmailAddress.emailAddress,
  ConRoleDuplicate.duplicateCount,
  ConRoleDuplicate.originalConcernRoleID,
  InvPartRole.investigationRoleCount
INTO
  :primaryAlternateID,
  :dateOfBirth,
  :dateOfDeath,
  :gender,
  :addressID,
  :addressData,
  :phoneNumberID,
  :emailAddress,
  :numDuplicates,
  :originalConcernRoleID,
  :numInvestigations
FROM
  ConcernRole 
  
  LEFT OUTER JOIN
  
  ProspectPerson
  ON (ConcernRole.concernRoleID =  ProspectPerson.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)
  
  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
  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