---

select DISTINCT ON (c.number)
  to_char(i.birth_date, 'DD.MM.YYYY') as birth_date,
  i.inn as person_inn,
  i.last_name,
  i.first_name,
  i.middle_name,
  i.certificate_date,
  i.certificate_number || ' ' || i.certificate_series as ser_nom_doc,
  i.certificate_code,
  i.id as smev_register_id,
  to_char(now(), 'DD.MM.YYYY') as date_doc,
  'UT_ZAPRSTRORGNPF_' || i.id || '_' || to_char(i.birth_date,'YYYYMMDD') || '_' || replace(cast(gen_random_uuid() as text),'-','') as file_id,
  '5.02' as vers_form,
  'КОНТУР-ЭКСТЕРН, ВЕРСИЯ 16.0' as vers_prog,
  '7702' as kod_no,
  to_char(now(), 'DD.MM.YYYY') as doc_date,
  '1184042' as knd,
  f.kpp as org_kpp,
  f.inn as org_inn,
  f.name_full as org_name,
  (
    select coalesce(max(cast(request_number as int)), 0) + 1
    from report.fns_reference_request_journal
  ) as req_number,
  c.number as number,
  to_char(c.date, 'DD.MM.YYYY') as contract_date,
  '4' as pr_c,
  '1' as pr_z_cont
from ourpension.application_termination_contract atc
join ourpension.sharer s on s.id = atc.sharer_id
join ourpension.contract c on s.contract_id = c.id
  and (c."service_type" = 'NPO' or c."service_type" = 'PDS')
left join ourpension.individual i on i.id = c.individual_id
left join ourpension.fund f on i.fund_id = f.id
where atc."date" >= to_date(:start_date_app, 'YYYY-MM-DD')
and atc."date" < to_date(:end_date_app, 'YYYY-MM-DD') + interval '1 day'
and ( case when :isChecked = false then exists (select 1 from ourpension.year_tax_deduction td where td.application_termination_contract_id = atc.id and td.type != 'RECEIVED')
		else true
		end)
and i.first_name is not null
ORDER BY c.number, i.id