SELECT
  AlternateName.firstForename,
  AlternateName.surname,
  ProspectPerson.dateOfBirth,
  ProspectPerson.gender,
  ProspectPerson.personBirthName,
  ProspectPerson.motherBirthSurname,
  ProspectPerson.primaryAlternateID,
  ProspectPerson.concernRoleID,
  ProspectPerson.personConcernRoleID,
  ConcernRole.sensitivity,
  city.elementValue,
  addressLine1.elementValue,
  ConcernRoleDuplicate.originalConcernRoleID,
  ConcernRoleDuplicate.statusCode
INTO
  :firstForename,
  :surname,
  :dateOfBirth,
  :gender,
  :personBirthName,
  :motherBirthSurname,
  :primaryAlternateID,
  :concernRoleID,
  :personConcernRoleID,
  :sensitivity,
  :city,
  :addressLine1,
  :originalConcernRoleID,
  :duplicateStatus 
FROM
  (ProspectPerson 
  
  LEFT OUTER JOIN
  
  ConcernRoleDuplicate 
  ON (ConcernRoleDuplicate.duplicateConcernRoleID =ProspectPerson.concernRoleID 
  AND ConcernRoleDuplicate.statusCode = :concernRoleDuplicateStatus)),
  ConcernRole,
  AlternateName,
  AddressElement city,
  AddressElement addressLine1 
WHERE 
  (
    :searchByFirstForename = '0' 
    OR AlternateName.upperFirstForename like :firstForename 
  ) 
  AND 
  (
    :searchBySurname = '0' 
    OR AlternateName.upperSurname like :surname 
  ) 
  AND ConcernRole.concernRoleID = ProspectPerson.concernRoleID 
  AND AlternateName.concernRoleID = ProspectPerson.concernRoleID 
  AND addressLine1.addressID = ConcernRole.primaryAddressID 
  AND addressLine1.elementType = :addressLine1Type 
  AND city.addressID = ConcernRole.primaryAddressID 
  AND city.elementType = :cityType 
  AND 
  (
    :searchByAddressLine1 = '0' 
    OR addressLine1.upperElementValue like :addressLine1 
  ) 
  AND 
  (
    :searchByCity = '0' 
    OR city.upperElementValue like :city 
  ) 
  AND 
  (
    :searchByGender = '0' 
    OR ProspectPerson.gender = :gender 
  ) 
  AND 
  (
    :searchByDateOfBirth = '0' 
    OR ProspectPerson.dateOfBirth = :dateOfBirth 
  ) 
  AND 
  (
    :searchByPersonBirthName = '0' 
    OR ProspectPerson.upperPersonBirthName like :personBirthname 
  ) 
  AND 
  (
    :searchByMotherBirthName = '0' 
    OR ProspectPerson.upperMotherBirthSurname like :motherBirthSurname 
  ) 
ORDER BY primaryAlternateID