---

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
left join ourpension.uv_activity_type uat
  on uat.activity_eng_type = c.service_type
  and uat.client_type = c.client_type
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 i.first_name is not null
  and (uat.activity_rus_type = 'ПДС' or uat.activity_rus_type = 'НПО')
  and atc.id is not null
  and ( :isChecked = false or exists (
        select 1 from ourpension.year_tax_deduction td
        where td.application_termination_contract_id = atc.id
          and td.type != 'RECEIVED'))
ORDER BY c.number, i.id