SELECT
ConcernRole.concernRoleName,
ConcernRole.concernRoleType,
ConcernRole.concernRoleID,
ConcernRole.primaryAlternateID,
Person.dateOfBirth,
Address.addressData,
ConcernRole.primaryPhoneNumberID,
EmailAddress.emailAddress,
LegalActionRole.typecode,
PrimaryClientDetail.isPrimaryClient

INTO
:concernRoleName,
:concernRoleType,
:concernRoleID,
:primaryAlternateID,
:dateOfBirth,
:addressData,
:phoneNumberID,
:emailAddress,
:caseParticipantRoleType,
:isPrimaryClientInd

FROM
  ConcernRole 

  LEFT OUTER JOIN (
  SELECT 
  CaseParticipantRole.participantRoleID, 
  CaseParticipantRole.typecode 

  FROM 
  CaseParticipantRole, 
  ConcernRole

  WHERE CaseParticipantRole.caseID = :caseID AND
  ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID AND
  CaseParticipantRole.recordStatus=:recordStatus
  
  ) LegalActionRole 
  ON (LegalActionRole.participantRoleID = ConcernRole.concernRoleID)

  LEFT OUTER JOIN
  Person 
  ON (ConcernRole.concernRoleID = Person.concernRoleID) 

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

  LEFT OUTER JOIN (
  SELECT 
  CaseHeader.concernRoleID,
  '1' AS isPrimaryClient
  FROM
  CaseHeader
  WHERE CaseHeader.caseID =:caseID
  ) PrimaryClientDetail 
  ON (PrimaryClientDetail.concernRoleID = ConcernRole.concernRoleID)

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 = :recordStatus
)