---

первый запрос
DECLARE
    v_register_count integer := 0;
    v_register_decision_count integer := 0;
    v_new_register_id integer := 0;
BEGIN
create temp table register(

id integer NOT NULL,
    residence_status integer,
    num integer,
    payment_way varchar,
    register_type_code varchar,
    payment_register_code varchar,
    decision_id bigint[],
    "version" TIMESTAMP,
    bank_comission_sum numeric(22, 2)
) on commit drop;

--Сохранение во временной таблице реестров
insert into register(id, residence_status, num, payment_way, register_type_code, payment_register_code, "version", decision_id,
    bank_comission_sum)
select
    nextval('back_office.ops_payment_register_sequence'),
    --i.residence_status,
    case when coalesce(aibd_2.payment_way, aibd.payment_way) = '01' then --bank
        case when coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '426%' or
                 coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '40820%' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end --resident
         when coalesce(aibd_2.payment_way, aibd.payment_way) = '02' then --post
        case when i.citizenship_country_code != '643' or --R.F.
                 pa.country_code != '643' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end end, --resident
    --row_number() over (order by i.residence_status),
    row_number() over (order by
    case when coalesce(aibd_2.payment_way, aibd.payment_way) = '01' then --bank
        case when coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '426%' or
                 coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '40820%' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end --resident
         when coalesce(aibd_2.payment_way, aibd.payment_way) = '02' then --post
        case when i.citizenship_country_code != '643' or --R.F.
                 pa.country_code != '643' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end end                    
                     ),
    coalesce(aibd_2.payment_way, aibd.payment_way),
    case
        when r.check_code = _post_check_code then 'REGISTER_POST'
        else 'REGISTER_B1' -- coalesce(e.enum_value_abbr, 'ONE_PAYMENT') --brt.code
    end,
    'ONE_PAYMENT', --coalesce(e.enum_value_abbr, 'ONE_PAYMENT'), --brt.payment_register_code,
    r.version,
    array_agg(d.id),
    sum(back_office.f_get_payment_register_decision_bank_comission(d.id, _register_date))
from
    dev.check_result r
join back_office.ops_payment_decision d on r.id = d.id
join ourpension.decision_payment_type pt on pt.code = d.decision_payment_type_code
join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
join ourpension.sharer sh on a.sharer_id = sh.id
join ourpension.individual i on i.id = coalesce(a.representative_individual_id, sh.individual_id)
--left JOIN ourpension.application_individual_bank_detail aibd on aibd.individual_id = i.id and aibd.accepted notnull and aibd.canceled isnull
left join ourpension.application_individual_bank_detail aibd on a.bank_detail_id = aibd.id and aibd.accepted notnull and aibd.canceled isnull
        left join lateral (select dd.* from ourpension.application_individual_bank_detail dd
                                 where i.id = dd.individual_id
                                     and dd.accepted notnull and dd.canceled isnull order by dd.accepted desc limit 1) aibd_2 on aibd.canceled notnull
left JOIN ourpension.bankru b on coalesce(aibd_2.bic, aibd.bic) = b.bic     
left join ourpension.address pa on coalesce(aibd_2.post_address_id, aibd.post_address_id) = pa.id
left join (select id, case enum_value_abbr when 'SBERBANK' then 'SBER' else enum_value_abbr end enum_value_abbr from ourpension.enums) e on e.id = b.group_bank
where
    r.code = _check_code
    and r.check_type = 'SUCCESS'
    and
        case
            when r.check_code = _post_check_code then true
            when r.check_code = _bank_check_code then true
            else false
        end
    and r.username = _username
    and pt.group_code in (_decision_payment_type_code, 'К' || _decision_payment_type_code)
    group by --i.residence_status,
    case when coalesce(aibd_2.payment_way, aibd.payment_way) = '01' then --bank
        case when coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '426%' or
                 coalesce(aibd_2.personal_account, aibd.personal_account) ~~* '40820%' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end --resident
         when coalesce(aibd_2.payment_way, aibd.payment_way) = '02' then --post
        case when i.citizenship_country_code != '643' or --R.F.
                 pa.country_code != '643' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end end, --resident
    coalesce(aibd_2.payment_way, aibd.payment_way),
    case
        when r.check_code = _post_check_code then 'REGISTER_POST'
        else 'REGISTER_B1' -- coalesce(e.enum_value_abbr, 'ONE_PAYMENT') --coalesce(brt.payment_register_code, 'ONE_PAYMENT')
    end,
    --e.enum_value_abbr,
    r.version
    having coalesce(aibd_2.payment_way, aibd.payment_way) notnull;

--Создан новый реестр на выплату

insert into back_office.ops_payment_register(
    id,
    author_agent_id,
    payment_type_code,
    bank_register_type_code,
    comment,
    number,
    date,
    ops_payment_register_decision_type_code,
    fund_bank_details_id,
    created,
    version,
    register_link_id,
    residence_type_id,
    bank_comission,
    payment_order_date
)
select
    reg.id,
    (select a.id from ourpension.agent a where a.user_id = _user_id),
    reg.payment_way,
    reg.register_type_code,
    _comment,
    -- new '"VTB" "ZHIVAGO_BANK" "SBERBANK""PRIO_VNESHTORG_BANK"'
    case
        when reg.register_type_code in ('REGISTER_POST')
            then
                (select pt_n.espp || '_' || LPAD(cast (un.serial_number+reg.num as varchar (5)) , 5, '0')
                from dev.unique_number un
                left join ourpension.fund_bank_payment_type pt_n on pt_n."service_type" = 'OPS' and pt_n.ops_code = _decision_payment_type_code
                where un.code = 'payment_register_post_reference')
                
        when true then
                (select cast ( date_part('year', now()) || '-' || (serial_number+reg.num) as varchar (10) )
                from dev.unique_number where code = 'payment_register_bank_reference')
            
        when reg.register_type_code in ('BANK_REFERENCE', 'BANK_REFERENCE_VTB', 'BANK_REFERENCE_SBERBANK', 'REGISTER_B1')
            then
                (select cast ( (serial_number+reg.num) as varchar (10) )
                from dev.unique_number where code = 'payment_register_bank_reference')
        
        when reg.register_type_code in ('REGISTER_VTB_ER2','VTB')
            then
                (select reg.payment_register_code||'-'||lpad( cast (date_part('month',now()) as varchar (10) ),2,'0')
                     ||'-'|| cast (date_part('year',now()) as varchar (10) )
                    ||'-'|| cast ( (n.serial_number+reg.num) as varchar (10) )
                from dev.unique_number n
                where n.code = 'payment_register_vtb_er2')
    
        when reg.register_type_code in ('SBERBANK','SBER')
            then
                (select reg.payment_register_code||'-'||lpad(cast (date_part('month',now()) as varchar (10) ),2,'0')||'-'
             ||cast (date_part('year',now()) as varchar (10) )||'-'||cast ((n.serial_number+reg.num) as varchar (10) )
                from dev.unique_number n
                where n.code = 'payment_register_sberbank')
    
        when reg.register_type_code in ('ONE_PAYMENT')
            then 'ONE_PAYMENT' --ba.makeev пока так
    
        --when reg.register_type_code in ('REGISTER_B1')
        --    then 'Без_номера' --ba.makeev пока так
            
        else ''
            
    end,
    _register_date,
    _decision_payment_type_code,
    (    select
            fbd.id
        from back_office.ops_payment_register_decision_type dt
        join ourpension.fund_bank_payment_type fbp on dt.code = fbp.ops_code and dt.service_type = fbp.service_type
        join ourpension.fund_bank_details fbd on fbp.id = any(fbd.payment_type)
        where dt.code = _decision_payment_type_code
    ),
    current_timestamp,
    current_timestamp,
    null,
    reg.residence_status,
    reg.bank_comission_sum,
    _register_date
from register reg;

--Выбранные решения включены в Банковский реестр

with charged(decision_id, operation_id, value) as (
    select
        o.ops_payment_decision_id, o.id, sum(t.value)
    from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join register r on o.ops_payment_decision_id = any(r.decision_id) and r.payment_way = '01'
    join back_office.operation_type ot on o.operation_type_id = ot.id and ot.code = 'CHARGE_FROM_RESERVE'
    where o.deleted is null and o.approved is not null
    and not exists (
        select 1
        from back_office.ops_payment_register_decision rd
        where rd.ops_payment_decision_id = o.ops_payment_decision_id and rd.charge_operation_id = o.id
     )
    and o.ops_payment_decision_id is not null
group by o.ops_payment_decision_id, o.id
)
insert into back_office.ops_payment_register_decision(
    id,
    ops_payment_register_id,
    ops_payment_decision_id,
    charge_operation_id, -- Операция начисления
    value,
    period,
    recipient_individual_id,
    recipient_inn,
    recipient_name,
    recipient_bank_current_account,
    recipient_bank_personal_account,
    recipient_bank_card_account,
    recipient_bank_name,
    recipient_bank_place,
    recipient_bank_correspondent_account,
    recipient_bank_bic,
    --recipient_post_address,
    version,
    --*trustee_order_id,
    individual_bank_detail_id,
    sharer_id,
    bank_comission,
    --individual_address_id,
    --post_comission,
    --post_comission_params
    payment_order_date
)
select
    nextval('back_office.ops_payment_register_decision_sequence'),
    reg.id,
    d.id,
    o.id, -- Операция начисления
    ch.value,
    to_char(cast(o.period_date as date), 'MM месяц YYYY года'),
    coalesce(troi.id,i.id),--i.id,
    i.inn,
    coalesce((troi.last_name || ' ' || troi.first_name || coalesce(' ' ||troi.middle_name, '')), (i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, ''))),
    coalesce(br.bank_current_account, br_2.bank_current_account, br_3.bank_current_account),
    coalesce(br.personal_account, br_2.personal_account, br_3.personal_account),
    coalesce(br.card_number, br_2.card_number, br_3.card_number),
    b.bank_name,
    b.place,
    b.corraccount,
    coalesce(br.bic, br_2.bic, br_3.bic),
    current_timestamp,
    coalesce(a.bank_detail_id, br_2.id, br_3.id),
    sh.id,
    back_office.f_get_payment_register_decision_bank_comission(d.id, _register_date),
    _register_date
from dev.check_result r
join back_office.ops_payment_decision d on r.id = d.id
join charged ch on ch.decision_id = d.id
join back_office.operation o on ch.operation_id = o.id
join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
join ourpension.sharer sh on a.sharer_id = sh.id
join ourpension.individual i on sh.individual_id = i.id
join register reg on /*i.residence_status = reg.residence_status and*/ r.id = any(reg.decision_id)
join ourpension.decision_payment_type dt on dt.code = d.decision_payment_type_code
    
--left join ourpension.application_individual_bank_detail brt on coalesce(troi.id, i.id) = brt.individual_id and brt.payment_way = '01'
--        and brt.accepted is not null and brt.canceled is null --*and brt.trustee_account
left join ourpension.application_individual_bank_detail br on br.id = a.bank_detail_id and br.payment_way = '01'
        and br.accepted is not null and br.canceled is null
left join ourpension.individual troi on a.representative_individual_id = troi.id
left join lateral (select dd.* from ourpension.application_individual_bank_detail dd
                              where coalesce(troi.id, i.id) = dd.individual_id
                                 and dd.accepted notnull and dd.canceled isnull and dd.payment_way = '01' order by dd.accepted desc limit 1) br_2 on br.canceled notnull
left join lateral (select ddd.* from ourpension.application_individual_bank_detail ddd
                              where ddd.sharer_id = sh.id
                                 and ddd.accepted notnull and ddd.canceled isnull and ddd.payment_way = '01' order by ddd.accepted desc limit 1) br_3 on true
left join ourpension.bankru b on b.bic = coalesce(br.bic, br_2.bic, br_3.bic)
where r.code = _check_code
    and dt.group_code in (_decision_payment_type_code, 'К' || _decision_payment_type_code)
    and r.check_code in (_bank_check_code) --LruuKJxH - Банк, ulJWEYJp - Почта --check_code = 'ulJWEYJp'
    and r.username = _username;

--Выбранные решения включены в почтовый реестр

with charged(decision_id, operation_id, value) as (
    select
        o.ops_payment_decision_id, o.id, sum(t.value)
    from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join register r on o.ops_payment_decision_id = any(r.decision_id) and r.payment_way = '02'
    join back_office.operation_type ot on o.operation_type_id = ot.id and ot.code = 'CHARGE_FROM_RESERVE'
    where o.deleted is null and o.approved is not null and not exists (
        select *
        from back_office.ops_payment_register_decision rd
        where rd.ops_payment_decision_id = o.ops_payment_decision_id and rd.charge_operation_id = o.id
        )
        and o.ops_payment_decision_id is not null
    group by o.ops_payment_decision_id, o.id
)
insert into back_office.ops_payment_register_decision(
id,
ops_payment_register_id,
ops_payment_decision_id,
charge_operation_id, -- Операция начисления
value,
period,
    recipient_individual_id,
recipient_inn,
recipient_name,
recipient_post_address,
version,
    --individual_bank_detail_id,
    sharer_id,
individual_address_id,
    post_comission,
post_comission_params,
payment_order_date
)
select
nextval('back_office.ops_payment_register_decision_sequence'),
reg.id,
d.id,
o.id, -- Операция начисления
ch.value,
to_char(cast(o.period_date as date), 'MM месяц YYYY года'),
coalesce(troi.id,i.id),
i.inn,
coalesce((troi.last_name || ' ' || troi.first_name || coalesce(' ' ||troi.middle_name, '')), (i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, ''))),
--*coalesce(pat.address_text, pa.address_text),
pa.address_text,
current_timestamp,
    --coalesce(brt.id, br.id),
    sh.id,
--*coalesce(pat.id, pa.id),
pa.id,
--case when coalesce(brt.payment_way, br.payment_way) = '02' then post_com.comission end,
post_com.comission,
--case when coalesce(brt.payment_way, br.payment_way) = '02' then post_com.params end
post_com.params,
_register_date
from dev.check_result r
join back_office.ops_payment_decision d on r.id = d.id
join charged ch on ch.decision_id = d.id
join back_office.operation o on ch.operation_id = o.id
join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
join ourpension.sharer sh on a.sharer_id = sh.id
join ourpension.individual i on sh.individual_id = i.id
join register reg on r.id = any(reg.decision_id)
join ourpension.decision_payment_type dt on dt.code = d.decision_payment_type_code
    
left join ourpension.individual troi on a.representative_individual_id = troi.id
--*left join ourpension.application_individual_bank_detail brt on troi.id = brt.individual_id
--        and brt.accepted is not null and brt.canceled is null
left join lateral (select dd.* from ourpension.application_individual_bank_detail dd
                              where troi.id = dd.individual_id
                                 and dd.accepted notnull and dd.canceled isnull and dd.payment_way = '02' order by dd.accepted desc limit 1) brt on true
        
--*left join ourpension.address pat on troi.post_address_id = pat.id
    
left join ourpension.application_individual_bank_detail br_1 on br_1.id = a.bank_detail_id and
        br_1.accepted is not null and br_1.canceled is null
--*left join ourpension.application_individual_bank_detail br_2 on br_2.sharer_id = sh.id and
--        br_2.accepted is not null and br_2.canceled is null
left join lateral (select dd.* from ourpension.application_individual_bank_detail dd
                              where dd.sharer_id = sh.id
                                 and dd.accepted notnull and dd.canceled isnull and dd.payment_way = '02' order by dd.accepted desc limit 1) br_2 on true
--*left join ourpension.address pa on i.post_address_id = pa.id
left join ourpension.address pa on pa.id =
    case when br_1.id notnull then br_1.post_address_id
         when troi.id notnull then troi.post_address_id
         when br_2.id notnull then br_2.post_address_id end
    ,utils.f_calculate_post_comission(pa.country_code, ch.value) post_com
where r.code = _check_code
    and dt.group_code in (_decision_payment_type_code, 'К' || _decision_payment_type_code)
    and r.check_code in (_post_check_code)
    and r.username = _username;

--Сохранены удержания по исполнительным листам

with payment(register_id, payment_id, sharer_id, individual_id, value)
as (
    select
        r.id as register_id,
        p.id payment_id,
        p.sharer_id,
        p.recipient_individual_id as individual_id,
        p.value
    from back_office.ops_payment_register r
    join back_office.ops_payment_register_decision p on r.id = p.ops_payment_register_id
    where r.id in (select id from register) --currval('back_office.ops_payment_register_sequence')
    --group by r.id, p.id, p.sharer_id, p.recipient_individual_id
),
retention (register_id, payment_id, sharer_id, individual_id, application_retention_id,
                reach_limit_flag, reach_limit_value, value, recipient_individual_id, recipient_entity_id)
    as (
    select p.register_id,
        p.payment_id,
        p.sharer_id,
        p.individual_id,
        ar.id,
        ar.reach_limit_flag,
        ar.reach_limit_value,
        trunc(case ar.calculation_method
            when 1 then least(p.value, ar.fixed_value)
            when 2 then (cast(ar.percent as decimal) / 100.00) * p.value
            when 3 then (cast(ar.share_numerator as decimal) / cast(ar.share_denominator as decimal)) * p.value
            else 0
        end, 2),
        ar.payee_individual_id,
        ar.payee_entity_id    
    from payment p
    join ourpension.application_retention ar on ar.sharer_id = p.sharer_id
    where ar.accepted is not null and ar.canceled is null
        and cast(_register_date as date) between ar.start_date and coalesce(ar.end_date, to_date('9000-01-01', 'YYYY-MM-DD'))
)
insert into back_office.ops_payment_retention(
    ops_payment_register_id,
    payment_id, sharer_id,
    individual_id,
    application_retention_id,
    value,
    retention,
    recipient_individual_id,
    recipient_entity_id
)
select
    register_id,
    payment_id,
    sharer_id,
    individual_id,
    application_retention_id,
    value,
    case
        when reach_limit_flag = 1 then least(reach_limit_value, value)
        else value
    end,
    recipient_individual_id,
    recipient_entity_id    
from retention;

--Обновлен счетчик номеров

with rr as (select count(r.id) as count_r, register_type_code from register r group by register_type_code),
new_num as (select sum(rr.count_r) as summa, un.code
    from rr
    join dev.unique_number un on un.code =
        case
            when rr.register_type_code in ('BANK_REFERENCE', 'BANK_REFERENCE_VTB', 'BANK_REFERENCE_SBERBANK', 'REGISTER_B1') then 'payment_register_bank_reference'
            when rr.register_type_code in ('REGISTER_VTB_ER2','VTB') then 'payment_register_vtb_er2'
            when rr.register_type_code in ('SBERBANK','SBER') then 'payment_register_sberbank'    
            when rr.register_type_code = 'REGISTER_POST' then 'payment_register_post_reference'
            else null
        end
    group by un.code
)
UPDATE dev.unique_number AS t
SET serial_number = serial_number+ s.summa
FROM new_num AS s
WHERE t.code = s.code;

--Очищено выделение

delete from tools.selection
where code = 'decisions_charged_not_paid_out'
    and username = _username and row_id in (select unnest(decision_id) from register);

return (
    with
pr as (select id as pr_id from register),
rc as (select count(*) as rc
             from back_office.ops_payment_retention join pr on ops_payment_register_id = pr_id
    )
select json_agg(json_object(
ARRAY['ops_payment_register_id','retention_count'],
ARRAY[cast(pr_id as text),cast(rc as text)] )) as obj
from pr cross join rc
);
END;

второй
DECLARE
    v_register_count integer := 0;
    v_register_decision_count integer := 0;
    v_new_register_id integer := 0;
BEGIN
create temp table register(

id integer NOT NULL,
    residence_status integer,
    num integer,
    payment_way varchar,
    register_type_code varchar,
    payment_register_code varchar,
    --decision_id integer,
    --trustee_individual_id text,
    --unique_number_code varchar,
    decision_id bigint[],
    "version" TIMESTAMP,
    check_code text
) on commit drop;

--Сохранение во временной таблице реестров
insert into register(id, residence_status, num, payment_way, register_type_code, payment_register_code, "version", decision_id, check_code)
select
    nextval('back_office.ops_payment_register_sequence'),
    --i.residence_status,
    case when aibd.payment_way = '01' then --bank
        case when aibd.personal_account ~~* '426%' or aibd.personal_account ~~* '40820%' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end --resident
         when aibd.payment_way = '02' then --post
        case when i.citizenship_country_code != '643' or --R.F.
                 pa.country_code != '643' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end
    end, --resident
    row_number() over (order by
        case when aibd.payment_way = '01' then --bank
            case when aibd.personal_account ~~* '426%' or aibd.personal_account ~~* '40820%' or
                     i.residence_status in (66, 214) then 66 --no resident
                 else 65 end --resident
             when aibd.payment_way = '02' then --post
            case when i.citizenship_country_code != '643' or --R.F.
                     pa.country_code != '643' or
                     i.residence_status in (66, 214) then 66 --no resident
                 else 65 end
        end
                     ),
    case r.check_code
        when _bank_check_code then '01'
        when _post_check_code then '02'
    end,
    case
        when r.check_code = _post_check_code then 'REGISTER_POST'
        else 'REGISTER_B1' -- 25.03.2026 coalesce(e.enum_value_abbr, 'ONE_PAYMENT') --coalesce(brt.payment_register_code, 'ONE_PAYMENT')
    end,
    'ONE_PAYMENT', -- case when r.check_code = _bank_check_code then coalesce(e.enum_value_abbr, 'ONE_PAYMENT') end,
    r.version,
    array_agg(d.id),
    r.check_code
from
    dev.check_result r
join back_office.ops_assignee_decision d on r.id = d.id
join ourpension.decision_payment_type pt on pt.code = d.decision_payment_type_code
join ourpension.ops_assignee_payment p on p.id = d.ops_assignee_payment_id
join ourpension.individual i on i.id = p.assignee_id
left join ourpension.address pa on i.post_address_id = pa.id
left join ourpension.application_individual_bank_detail aibd on p.bank_detail_id = aibd.id
--*left JOIN ourpension.bankru b on aibd.bic = b.bic     
--*left join (select id, case enum_value_abbr when 'SBERBANK' then 'SBER' else enum_value_abbr end enum_value_abbr from ourpension.enums) e on e.id = b.group_bank
where
    r.code = _check_code
    and r.check_type = 'SUCCESS'
    and
        case
            when r.check_code = _post_check_code then true
            when r.check_code = _bank_check_code then true --brt.id notnull --true
            else false
        end
    and r.username = _username
    and pt.group_code = _decision_payment_type_code
    group by --i.residence_status,
    case when aibd.payment_way = '01' then --bank
        case when aibd.personal_account ~~* '426%' or aibd.personal_account ~~* '40820%' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end --resident
         when aibd.payment_way = '02' then --post
        case when i.citizenship_country_code != '643' or --R.F.
                 pa.country_code != '643' or
                 i.residence_status in (66, 214) then 66 --no resident
             else 65 end end, --resident
             r.check_code,
            case
                when r.check_code = _post_check_code then 'REGISTER_POST'
                else 'REGISTER_B1' -- 25.03.2026 coalesce(e.enum_value_abbr, 'ONE_PAYMENT') --coalesce(brt.payment_register_code, 'ONE_PAYMENT')
            end,
            --case when r.check_code = _bank_check_code then coalesce(e.enum_value_abbr, 'ONE_PAYMENT') end,
            r.version;

-- Внесение данных в регистр
insert into back_office.ops_payment_register (
id,
author_agent_id,
payment_type_code,
bank_register_type_code,
comment,
number,
date,
ops_payment_register_decision_type_code,
fund_bank_details_id,
created,
version,
register_link_id,
residence_type_id,
payment_order_date
)
select
    reg.id,
    (select a.id from ourpension.agent a where a.user_id = _user_id),
    reg.payment_way,
    reg.register_type_code,
    _comment,
    case     
        when false then
                (select cast ( date_part('year', now()) || '-' || (serial_number+reg.num) as varchar (10) )
                from dev.unique_number where code = 'payment_register_bank_reference')
                
        when back_office.f_get_unique_number_code(reg.register_type_code) = 'payment_register_bank_reference'
            then
                --(select cast ( (serial_number+reg.num) as varchar (10) )
                (select cast ( date_part('year', now()) || '-' || (serial_number+reg.num) as varchar (10) )
                from dev.unique_number where code = 'payment_register_bank_reference')
        
        when back_office.f_get_unique_number_code(reg.register_type_code) = 'payment_register_post_reference'
            then
                --(select pt_n.espp || '_' || LPAD(cast (un.serial_number+reg.num as varchar (6)) , 6, '0')
                (select pt_n.espp || '_' || LPAD(cast (un.serial_number+reg.num as varchar (5)) , 5, '0')
                from dev.unique_number un
                left join ourpension.fund_bank_payment_type pt_n on pt_n."service_type" = 'OPS' and pt_n.ops_code = _decision_payment_type_code
                where un.code = 'payment_register_post_reference')
                
        when back_office.f_get_unique_number_code(reg.register_type_code) = 'payment_register_vtb_er2'
            then
                (select reg.payment_register_code||'-'||lpad( cast (date_part('month',now()) as varchar (10) ),2,'0')
                     ||'-'|| cast (date_part('year',now()) as varchar (10) )
                    ||'-'|| cast ( (n.serial_number+reg.num) as varchar (10) )
                from dev.unique_number n
                where n.code = 'payment_register_vtb_er2')
    
        when back_office.f_get_unique_number_code(reg.register_type_code) = 'payment_register_sberbank'
            then
                (select reg.payment_register_code||'-'||lpad(cast (date_part('month',now()) as varchar (10) ),2,'0')||'-'
             ||cast (date_part('year',now()) as varchar (10) )||'-'||cast ((n.serial_number+reg.num) as varchar (10) )
                from dev.unique_number n
                where n.code = 'payment_register_sberbank')            
        else ''    
    end,
    _register_date,
    _decision_payment_type_code,
    (    select
            fbd.id
        from back_office.ops_payment_register_decision_type dt
        join ourpension.fund_bank_payment_type fbp on dt.code = fbp.ops_code and dt.service_type = fbp.service_type
        join ourpension.fund_bank_details fbd on fbp.id = any(fbd.payment_type)
        where dt.code = _decision_payment_type_code
    ),
    current_timestamp,--coalesce(r.payment_register_code, r.code) || '-' || to_char(current_timestamp, 'YYYYMMDDHH24MISS') || '-' || cast(s.id as varchar(32))
    current_timestamp,
    null,
    reg.residence_status,
    _register_date
from register reg;

--Выбранные решения включены в Банковский реестр
with charged(decision_id, operation_id, value) as (
    select
        o.ops_assignee_decision_id, o.id, sum(t.value)
    from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join register r on o.ops_assignee_decision_id = any(r.decision_id) and r.payment_way = '01'
    join back_office.operation_type ot on o.operation_type_id = ot.id and ot.code = 'CHARGE_FROM_RESERVE'
    where o.deleted is null and o.approved is not null
    and not exists (
        select *
        from back_office.ops_payment_register_decision rd
        where rd.ops_assignee_decision_id = o.ops_assignee_decision_id and rd.charge_operation_id = o.id
     )
    and o.ops_assignee_decision_id is not null
group by o.ops_assignee_decision_id, o.id
)
insert into back_office.ops_payment_register_decision(
    id,
    ops_payment_register_id,
    ops_assignee_decision_id,
    charge_operation_id, -- Операция начисления
    value,
    period,
    recipient_individual_id,
    recipient_inn,
    recipient_name,
    recipient_bank_current_account,
    recipient_bank_personal_account,
    recipient_bank_card_account,
    recipient_bank_name,
    recipient_bank_place,
    recipient_bank_correspondent_account,
    recipient_bank_bic,
    --recipient_post_address,
    version,
    individual_bank_detail_id,
    sharer_id,
    --individual_address_id,
    --post_comission,
    --post_comission_params
    payment_order_date
)
select
    nextval('back_office.ops_payment_register_decision_sequence'),
    reg.id,
    d.id,
    o.id, -- Операция начисления
    ch.value,
    to_char(cast(o.period_date as date), 'MM месяц YYYY года'),
    coalesce(troi.id,i.id),--i.id,
    i.inn,
    coalesce((troi.last_name || ' ' || troi.first_name || coalesce(' ' ||troi.middle_name, '')), (i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, ''))),
    coalesce(brt.bank_current_account, br.bank_current_account),
    coalesce(brt.personal_account, br.personal_account),
    coalesce(brt.card_number, br.card_number),
    coalesce(bt.bank_name, b.bank_name),
    coalesce(bt.place, b.place),
    coalesce(bt.corraccount, b.corraccount),
    coalesce(brt.bic, br.bic),
    --coalesce(pat.address_text, pa.address_text),
    current_timestamp,
    coalesce(brt.id, br.id),
    sh.id,
    --coalesce(pat.id, pa.id),
    --post_com.comission,
    --post_com.params
    _register_date
from dev.check_result r
join back_office.ops_assignee_decision d on r.id = d.id
join ourpension.decision_payment_type pt on pt.code = d.decision_payment_type_code
join charged ch on ch.decision_id = d.id
join back_office.operation o on ch.operation_id = o.id
join ourpension.ops_assignee_payment a on a.id = d.ops_assignee_payment_id
join ourpension.sharer sh on a.sharer_id = sh.id
join ourpension.individual i on a.assignee_id = i.id -- 15.06.2026 sh.individual_id = i.id
join register reg on /*i.residence_status = reg.residence_status and*/ r.id = any(reg.decision_id)
    
left join ourpension.individual troi on a.representative_individual_id = troi.id
left join ourpension.application_individual_bank_detail brt on troi.id = brt.individual_id and brt.payment_way = '01'
        and brt.accepted is not null and brt.canceled is null --*and brt.trustee_account
left join ourpension.bankru bt on bt.bic = brt.bic
    
left join ourpension.application_individual_bank_detail br on br.sharer_id = sh.id and br.payment_way = '01'
        and br.accepted is not null and br.canceled is null
left join ourpension.bankru b on b.bic = br.bic
where r.code = _check_code
    and pt.group_code = _decision_payment_type_code
    and r.check_code in (_bank_check_code) --LruuKJxH - Банк, ulJWEYJp - Почта --check_code = 'ulJWEYJp'
    and r.username = _username;

--Выбранные решения включены в почтовый реестр

with charged(decision_id, operation_id, value) as (
    select
        o.ops_assignee_decision_id, o.id, sum(t.value)
    from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join register r on o.ops_assignee_decision_id = any(r.decision_id) and r.payment_way = '02'
    join back_office.operation_type ot on o.operation_type_id = ot.id and ot.code = 'CHARGE_FROM_RESERVE'
    where o.deleted is null and o.approved is not null and not exists (
        select 1
        from back_office.ops_payment_register_decision rd
        where rd.ops_assignee_decision_id = o.ops_assignee_decision_id and rd.charge_operation_id = o.id
        )
        and o.ops_assignee_decision_id is not null
    group by o.ops_assignee_decision_id, o.id
)
insert into back_office.ops_payment_register_decision(
id,
ops_payment_register_id,
ops_assignee_decision_id,
charge_operation_id, -- Операция начисления
value,
period,
    recipient_individual_id,
recipient_inn,
recipient_name,
--recipient_bank_current_account,
--recipient_bank_personal_account,
--recipient_bank_card_account,
--recipient_bank_name,
--recipient_bank_place,
--recipient_bank_correspondent_account,
--recipient_bank_bic,
recipient_post_address,
version,
    --individual_bank_detail_id,
    sharer_id,
individual_address_id,
    post_comission,
post_comission_params,
payment_order_date
)
select
nextval('back_office.ops_payment_register_decision_sequence'),
reg.id,
d.id,
o.id, -- Операция начисления
ch.value,
to_char(cast(o.period_date as date), 'MM месяц YYYY года'),
    coalesce(troi.id,i.id),--i.id,
i.inn,
coalesce((troi.last_name || ' ' || troi.first_name || coalesce(' ' ||troi.middle_name, '')), (i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, ''))),
    --coalesce(brt.bank_current_account, br.bank_current_account),
    --coalesce(brt.personal_account, br.personal_account),
    --coalesce(brt.card_number, br.card_number),
    --coalesce(bt.bank_name, b.bank_name),
    --coalesce(bt.place, b.place),
    --coalesce(bt.corraccount, b.corraccount),
    --coalesce(brt.bic, br.bic),
    coalesce(pat.address_text, pa.address_text),
current_timestamp,
    --coalesce(brt.id, br.id),
    sh.id,
coalesce(pat.id, pa.id),
    post_com.comission,
post_com.params,
_register_date
from dev.check_result r
join back_office.ops_assignee_decision d on r.id = d.id
join ourpension.decision_payment_type pt on pt.code = d.decision_payment_type_code
join charged ch on ch.decision_id = d.id
join back_office.operation o on ch.operation_id = o.id
join ourpension.ops_assignee_payment a on a.id = d.ops_assignee_payment_id
join ourpension.sharer sh on a.sharer_id = sh.id
join ourpension.individual i on a.assignee_id = i.id -- 15.06.2026 sh.individual_id = i.id
join register reg on /*i.residence_status = reg.residence_status and*/ r.id = any(reg.decision_id)
join ourpension.decision_payment_type dt on dt.code = d.decision_payment_type_code
    
left join ourpension.individual troi on a.representative_individual_id = troi.id
left join ourpension.address pat on troi.post_address_id = pat.id --*and brt.id is null
    
left join ourpension.address pa on i.post_address_id = pa.id --*and br.id is null
    ,utils.f_calculate_post_comission(coalesce(pat.country_code, pa.country_code), ch.value) post_com
where r.code = _check_code
    and pt.group_code = _decision_payment_type_code
    and r.check_code in (_post_check_code)
    and r.username = _username;

--Сохранены удержания по исполнительным листам

with payment(register_id, payment_id, sharer_id, individual_id, value)
as (
    select
        r.id as register_id,
        p.id payment_id,
        p.sharer_id,
        p.recipient_individual_id as individual_id,
        p.value
    from back_office.ops_payment_register r
    join back_office.ops_payment_register_decision p on r.id = p.ops_payment_register_id
    where r.id in (select id from register) --currval('back_office.ops_payment_register_sequence')
    --group by r.id, p.id, p.sharer_id, p.recipient_individual_id
),
retention (register_id, payment_id, sharer_id, individual_id, application_retention_id,
                reach_limit_flag, reach_limit_value, value, recipient_individual_id, recipient_entity_id)
    as (
    select p.register_id,
        p.payment_id,
        p.sharer_id,
        p.individual_id,
        ar.id,
        ar.reach_limit_flag,
        ar.reach_limit_value,
        trunc(case ar.calculation_method
            when 1 then least(p.value, ar.fixed_value)
            when 2 then (cast(ar.percent as decimal) / 100.00) * p.value
            when 3 then (cast(ar.share_numerator as decimal) / cast(ar.share_denominator as decimal)) * p.value
            else 0
        end, 2),
        ar.payee_individual_id,
        ar.payee_entity_id    
    from payment p
    join ourpension.application_retention ar on ar.sharer_id = p.sharer_id
    where ar.accepted is not null and ar.canceled is null
        and cast(_register_date as date) between ar.start_date and coalesce(ar.end_date, to_date('9000-01-01', 'YYYY-MM-DD'))
)
insert into back_office.ops_payment_retention(
    ops_payment_register_id,
    payment_id, sharer_id,
    individual_id,
    application_retention_id,
    value,
    retention,
    recipient_individual_id,
    recipient_entity_id
)
select
    register_id,
    payment_id,
    sharer_id,
    individual_id,
    application_retention_id,
    value,
    case
        when reach_limit_flag = 1 then least(reach_limit_value, value)
        else value
    end,
    recipient_individual_id,
    recipient_entity_id    
from retention;

--Обновлен счетчик номеров

with rr as (select count(r.id) as count_r, register_type_code from register r group by register_type_code),
new_num as (select sum(rr.count_r) as summa, un.code
    from rr
    join dev.unique_number un on un.code =
        case
            when rr.register_type_code in ('BANK_REFERENCE', 'BANK_REFERENCE_VTB', 'BANK_REFERENCE_SBERBANK') then 'payment_register_bank_reference'
            when rr.register_type_code in ('REGISTER_VTB_ER2','VTB') then 'payment_register_vtb_er2'
            when rr.register_type_code in ('SBERBANK','SBER') then 'payment_register_sberbank'    
            when rr.register_type_code = 'REGISTER_POST' then 'payment_register_post_reference'
            else null
        end
    group by un.code
)
UPDATE dev.unique_number AS t
SET serial_number = serial_number+ s.summa
FROM new_num AS s
WHERE t.code = s.code;

--Очищено выделение

delete from tools.selection
where code = 'i0uboyuM'
    and username = _username and row_id in (select unnest(decision_id) from register);

return (
    with
pr as (select id as pr_id from register),
rc as (select count(*) as rc
             from back_office.ops_payment_retention join pr on ops_payment_register_id = pr_id
    )
select json_agg(json_object(
ARRAY['ops_assignee_register_id','retention_count'],
ARRAY[cast(pr_id as text),cast(rc as text)] )) as obj
from pr cross join rc
);
END;