---

WITH sub AS (
 SELECT DISTINCT
   contract.number AS contract_number,
   sh.number AS sh_number,
   i.last_name,
   i.first_name,
   i.middle_name,
   to_char(i.birth_date, 'DD.MM.YYYY') AS birth_date,
   i.insurance_number,
   i.certificate_series,
   i.certificate_number,
   case 
     when i.sex = 'F' then 'Ж'
     else 'М'
   end AS sex,
   i.id
 FROM ourpension.application_termination_contract atc 
 left join ourpension.application_termination_contract_data atcd 
   on atcd.application_termination_contract_id = atc.id
 LEFT JOIN ourpension.sharer sh 
   ON sh.id = atcd.sharer_id
 LEFT JOIN ourpension.contract contract 
   ON contract.id = sh.contract_id
 left join ourpension.individual i 
   on sh.individual_id = i.id
 left join ourpension.uv_activity_type uat 
   on uat.activity_eng_type = contract.service_type 
   and uat.client_type = contract.client_type
 WHERE atc.id is not null 
   and (uat.activity_rus_type = 'ПДС' or uat.activity_rus_type = 'НПО')
)
SELECT
 sub.contract_number,
 sub.sh_number,
 sub.last_name,
 sub.first_name,
 sub.middle_name,
 sub.birth_date,
 sub.insurance_number,
 sub.certificate_series,
 sub.certificate_number,
 sub.sex,
 sub.id
FROM sub
WHERE 1=1
#if  
  (("$!fl_first_name" == "") 
   && ("$!fl_last_name" == "")
   && ("$!fl_middle_name" == "")
   && ("$!cert_number" == "")
   && ("$!cert_series" == "")
   && ("$!insurance_number_fl" == "")
   && ("$!fl_contract_number" == "")
   && ("$!fl_birth_date" == "")
   && ("$!fl_sh_number" == ""))
 and 1 = 0
#end

#if ("$!fl_first_name" != "")
   and sub.first_name ILIKE '%' || :fl_first_name || '%'
#end

#if ("$!fl_last_name" != "")
   and sub.last_name ILIKE '%' || :fl_last_name || '%'
#end

#if ("$!fl_middle_name" != "")
   and sub.middle_name ILIKE '%' || :fl_middle_name || '%'
#end

#if ("$!cert_number" != "")
   and sub.certificate_number ILIKE '%' || :cert_number || '%'
#end

#if ("$!cert_series" != "")
   and sub.certificate_series ILIKE '%' || :cert_series || '%'
#end

#if ("$!insurance_number_fl" != "")
   and sub.insurance_number ILIKE '%' || :insurance_number_fl || '%'
#end

#if ("$!fl_contract_number" != "")
   and sub.contract_number ILIKE '%' || replace(:fl_contract_number, chr(92), chr(92) || chr(92)) || '%' ESCAPE chr(92)
#end

#if ("$!fl_birth_date" != "")
   and sub.birth_date = :fl_birth_date
#end 

#if ("$!fl_sh_number" != "")
   and sub.sh_number ILIKE '%' || :fl_sh_number || '%'
#end