---

SQL Error [22P02]: ERROR: invalid input syntax for type numeric: ""
Where: SQL statement "with c_resp

        as

        (

        select (mgr.f_DWH_getData(jCallParm::text, TRUE)::xml) xresp         

        )

        select t.service_type,

                 mgr.f_to_date(t.period_date) period_date,

                 t.pension_account_id,

                 t.amount,

                 t.operation_type_id,

                 t.operation_type_code,

                 t.operation_type_name,

                 t.op_comment

         from c_resp r,

             xmltable('//root/pension_account_operation' passing xresp

                                         columns     service_type text path 'service_type',

                                                     period_date text path 'period_date',

                                                     pension_account_id text path 'pension_account_id',

                                                     amount numeric path 'amount',

                                                     operation_type_id text path 'operation_type_id',

                                                     operation_type_code text path 'operation_type_code',

                                                     operation_type_name text path 'operation_type_name',

                                                     op_comment text path 'op_comment'                                                 

                                                     ) t"
PL/pgSQL function mgr.ft_dwh_get__pension_account_operation(jsonb) line 8 at RETURN QUERY


Error position:




with base as (
  select distinct 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 lateral (
    select ct.*
    from ourpension.application_termination_contract_data ctd
    join ourpension.application_termination_contract ct on ct.id = ctd.application_termination_contract_id
    where sh.id = ctd.sharer_id
    union all
    select ct.*
    from ourpension.application_termination_contract ct
    where ct.sharer_id = sh.id
) ct on true
    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
      )
),

account_pairs as (
    select distinct
        b.sharer_id_1c,
        b.service_type
    from base b
),

operations as (
    select
        p.sharer_id_1c,
        p.service_type,
        cast(date_part('year', t.period_date) as integer) as year,
        t.amount
    from account_pairs p

    join mgr.ft_dwh_get__pension_account_operation(
        cast(
            '{"p_sPensionAccountId":"' || p.sharer_id_1c ||
            '", "p_sServiceType":"' || p.service_type || '"}'
            as jsonb
        )
    ) t on true

    where case
            when p.service_type = 'NPO'
                then t.operation_type_name in ('Ч/з банк', 'От работодателя')
            when p.service_type = 'PDS'
                then t.operation_type_name = 'Сберегательные взносы'
            else false
          end
),

sum_by_year as (
    select
        o.sharer_id_1c,
        o.service_type,
        o.year,
        sum(o.amount) as value
    from operations o
    group by
        o.sharer_id_1c,
        o.service_type,
        o.year
)

select
    b.load_snv_id,
    b.application_termination_contract_id as id,
    b.individual_id,
    b.sum_tax_deduction,
    b.year,
    b.type,
    coalesce(sum_by_year.value, 0) as sum_income,
    m_nv.value as max_tax_deduction
from base b

left join sum_by_year
    on sum_by_year.sharer_id_1c = b.sharer_id_1c
   and sum_by_year.service_type = b.service_type
   and sum_by_year.year = cast(b.year as integer)

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

order by b.load_snv_id