---

select
    l.id as load_snv_id,
    atc.id as id,
    c.individual_id,
    l.sum_tax_deduction,
    l.year,
    l.deduction_sign as type,
    coalesce(sum_by_year.value, 0) as sum_income,
    m_nv.value as max_tax_deduction
from ourpension.load_snv l

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

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

left join ourpension.application_termination_contract atc
    on atc.sharer_id = sh.id
   and atc.accepted is not null
   and atc.rejected is null
   and atc.canceled is null

left join lateral (
    select
        sum(t.amount) as value
    from mgr.ft_dwh_get__pension_account_operation(
        cast(
            '{"p_sPensionAccountId":"' || sh.sharer_id_1c ||
            '", "p_sServiceType":"' || c.service_type || '"}'
            as jsonb
        )
    ) t
    where cast(date_part('year', t.period_date) as integer) = cast(l.year as integer)
      and case
            when c.service_type = 'NPO'
                then t.operation_type_name in ('Ч/з банк', 'От работодателя')
            when c.service_type = 'PDS'
                then t.operation_type_name = 'Сберегательные взносы'
            else false
          end
) sum_by_year on true

left join ourpension.max_year_tax_deduction m_nv
    on m_nv.year = cast(l.year as integer)
   and coalesce(sum_by_year.value, 0) >= 0

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
  )

order by l.id;