---

select
    atc.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

    join tools.selection s
        on s.row_id = l.id
       and s.username = :username
       and s.code = :query_table_code

    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