SELECT
  DISTINCT CaseHeader.caseReference,
  CaseHeader.registrationDate,
  SocialEnterpriseFolder.caseID,
  SocialEnterpriseFolder.type,
  CaseHeader.statusCode
INTO
  :caseReference,
  :creationDate,
  :caseID,
  :caseType,
  :caseStatus 
FROM
  CaseHeader,
  SocialEnterpriseFolder,
  AlternateName alternateName1,
  AlternateName alternateName2,
  CaseParticipantRole,
  ConcernRole,
  Users,
  MultidisciplinaryTeamMember,
  TeamMembership,
  CaseTeamLink 
WHERE 
  (
    
    (
      :searchByMDTFirstname ='0' 
      OR 
      (
        
        (
          alternateName1.upperFirstForename like :mdtFirstname 
          AND alternateName1.concernRoleID = ConcernRole.concernRoleID 
          AND ConcernRole.concernRoleID = MultidisciplinaryTeamMember.concernRoleID 
        ) 
        OR 
        (
          Users.upperFirstname like :mdtFirstname 
          AND Users.upperUserName = upper(MultidisciplinaryTeamMember.userName) 
        ) 
        AND MultidisciplinaryTeamMember.multidisciplinaryTeamMemberID = TeamMembership.multidisciplinaryTeamMemberID 
        AND TeamMembership.multidisciplinaryTeamID = CaseTeamLink.multidisciplinaryTeamID 
        AND CaseTeamLink.caseID = SocialEnterpriseFolder.caseID 
      ) 
    ) 
    AND 
    (
      :searchByMDTSurname ='0' 
      OR 
      (
        
        (
          alternateName1.upperSurname like :mdtSurname 
          AND alternateName1.concernRoleID = ConcernRole.concernRoleID 
          AND ConcernRole.concernRoleID = MultidisciplinaryTeamMember.concernRoleID 
        ) 
        OR 
        (
          Users.upperSurname like :mdtSurname 
          AND Users.upperUserName = upper(MultidisciplinaryTeamMember.userName) 
        ) 
        AND MultidisciplinaryTeamMember.multidisciplinaryTeamMemberID = TeamMembership.multidisciplinaryTeamMemberID 
        AND TeamMembership.multidisciplinaryTeamID = CaseTeamLink.multidisciplinaryTeamID 
        AND CaseTeamLink.caseID = SocialEnterpriseFolder.caseID 
      ) 
    ) 
    AND 
    (
      :searchByClientFirstname = '0' 
      OR 
      (
        alternateName2.upperFirstForename like :clientFirstname 
        AND alternateName2.concernRoleID = CaseParticipantRole.participantRoleID 
        AND CaseParticipantRole.caseID = SocialEnterpriseFolder.caseID 
      ) 
    ) 
    AND 
    (
      :searchByClientSurname = '0' 
      OR 
      (
        alternateName2.upperSurname like :clientSurname 
        AND alternateName2.concernRoleID = CaseParticipantRole.participantRoleID 
        AND CaseParticipantRole.caseID = SocialEnterpriseFolder.caseID 
      ) 
    ) 
    AND SocialEnterpriseFolder.caseID = CaseHeader.caseID 
    AND 
    (
      :searchBySEFType = '0' 
      OR 
      (
        SocialEnterpriseFolder.type = :sefType 
        AND SocialEnterpriseFolder.caseID = CaseHeader.caseID 
      ) 
    ) 
    AND 
    (
      :searchBySEFStatus = '0' 
      OR 
      (
        CaseHeader.statusCode = :sefStatus 
        AND CaseHeader.caseID = SocialEnterpriseFolder.caseID 
      ) 
    ) 
  )