---

with base as (
    select
        l.id as load_snv_id,
        ct.id as application_termination_contract_id,
        c.individual_id,
        l.sum_tax_deduction,
        l.year,
        l.deduction_sign as type,
        c.service_type,
        sh.sharer_id_1c
    from ourpension.load_snv l

    join ourpension.contract c
        on l.contract_number = c.number

    join ourpension.individual i
        on i.id = c.individual_id
       and lower(btrim(i.last_name)) = lower(btrim(l.person_last_name))
       and lower(btrim(i.first_name)) = lower(btrim(l.person_first_name))

    join ourpension.sharer sh
        on sh.contract_id = c.id

    join ourpension.application_termination_contract_data ctd
        on sh.id = ctd.sharer_id

    join ourpension.application_termination_contract ct
        on ct.id = ctd.application_termination_contract_id
       and ct.accepted is not null
       and ct.rejected is null
       and ct.canceled is null
       and ct.termination_type in ('TRANSFER', 'PAYMENT', 'FULL')

    where l.file_id = :fileId
      and exists (
          select 1
          from tools.selection s
          where s.row_id = l.id
            and s.username = :username
            and s.code = :query_table_code
      )
),