SELECT
ConcernRole.concernRoleName,
ConcernRole.concernRoleType,
ConcernRole.concernRoleID,
ConcernRole.primaryAlternateID,
Address.addressData,
ConcernRole.primaryPhoneNumberID,
EmailAddress.emailAddress,
RelationshipToPrimaryClient.relationshipType

INTO
:concernRoleName,
:concernRoleType,
:concernRoleID,
:primaryAlternateID,
:addressData,
:phoneNumberID,
:emailAddress,
:relationshipType

FROM
  ConcernRole 
  LEFT OUTER JOIN (
  SELECT 
  CaseParticipantRole.participantRoleID, 
  ConcernRoleRelationship.relationshipType 

  FROM 
  ConcernRoleRelationship, 
  CaseParticipantRole, 
  ConcernRole

  WHERE CaseParticipantRole.caseID = :caseID AND 
  ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID AND
  CaseParticipantRole.recordStatus = :recordStatusCode AND
    (
      CaseParticipantRole.typeCode = :participantTypePrimary OR
      CaseParticipantRole.typeCode = :participantTypeMember 
    ) AND  
  ConcernRoleRelationship.relConcernRoleID = CaseParticipantRole.participantRoleID AND 
  ConcernRoleRelationship.statusCode = :recordStatusCode AND
  ConcernRoleRelationship.concernRoleID = ( 
      SELECT 
      CaseParticipantRole.participantRoleID 
      FROM 
      CaseParticipantRole  
      WHERE 
      CaseParticipantRole.typeCode = :participantTypePrimary AND 
      CaseParticipantRole.caseID = :caseID 
    ) 
  ) RelationshipToPrimaryClient 
  ON (RelationshipToPrimaryClient.participantRoleID = ConcernRole.concernRoleID)

  LEFT OUTER JOIN
   Address 
  ON (ConcernRole.primaryAddressID = Address.addressID) 
  
  LEFT OUTER JOIN
    EmailAddress 
  ON (ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID)


WHERE ConcernRole.concernRoleID IN (
SELECT
CaseParticipantRole.participantRoleID
FROM 
CaseParticipantRole
WHERE 
CaseParticipantRole.caseID = :caseID AND
  (
    CaseParticipantRole.toDate is NULL OR
    CaseParticipantRole.toDate > :currentDate
  ) AND 
CaseParticipantRole.recordStatus = :recordStatusCode AND
 (
  CaseParticipantRole.typeCode = :participantTypePrimary OR
  CaseParticipantRole.typeCode = :participantTypeMember 
  )
)