https://pastein.ru/t/9W0
Загрузка данных
CREATE OR REPLACE FUNCTION ourpension.f_get_available_applications(p_id integer, p_full_info boolean DEFAULT false)
RETURNS json
LANGUAGE plpgsql
AS $function$
declare
v_apps jsonb;
v_is_not_death boolean;
v_application_for_cancel boolean;
v_contract jsonb[];
v_actual_zev_for_ouev boolean;
v_ops_app jsonb[];-- boolean;
v_active_zev text;
v_sharer_id int;
v_pens_date date;
v_pens_calc jsonb[];
v_is_spv_decision boolean;
v_is_np_decision boolean;
v_is_nops_decision boolean;
BEGIN
v_is_not_death := (select not exists(select 1
from ourpension.application_death_info a
where a.accepted is not null
and a.canceled is null
and a.rejected is null
and a.individual_id = p_id
)
and
exists (select 1 from ourpension.individual where id = p_id)
);
v_application_for_cancel := ( select
case
when (select count(1) from ourpension.f_get_application_fo_cancellation(p_id)) > 0 then true
else false
end
);
/*with termination(contract_id, termination_reason_id, termination_date, num) as (
select
c.id,
t.contract_termination_reason_id,
t.date,
row_number() over (partition by c.id order by t.date, t.version desc)
from ourpension.contract c
join ourpension.termination_contract tc on c.id = tc.contract_id
join ourpension.termination t on tc.termination_id = t.id
join ourpension.sharer s on s.contract_id = c.id
where s.individual_id = p_id
)
*/
if p_full_info then
v_contract := (select array_agg(row_to_json(c)) from (
select c.id, c.service_type, cs.contract_status state, c.client_type, s.id sharer_id, sharer_state.state sharer_state from ourpension.contract c
left join mgr.v_io_contract_status cs on cs.contract_id = c.id
inner join ourpension.sharer s on s.contract_id = c.id
inner join lateral ourpension.f_get_sharer_state(s.id) sharer_state on true
where s.individual_id = p_id
) c);
else
v_contract := (select array_agg(row_to_json(c)) from (
select c.id, c.service_type, cs.contract_status state, c.client_type, s.id sharer_id, sharer_state.state sharer_state from ourpension.contract c
left join mgr.v_io_contract_status cs on cs.contract_id = c.id
inner join ourpension.sharer s on s.contract_id = c.id
inner join lateral ourpension.f_get_sharer_state(s.id) sharer_state on true
where s.individual_id = p_id
/* select c.id, c.service_type, state.state, c.client_type, s.id, sharer_state.state from ourpension.contract c
inner join lateral ourpension.ops_get_contract_state_on_date(c.id) state on true
inner join ourpension.sharer s on s.contract_id = c.id
inner join lateral ourpension.f_get_sharer_state(s.id) sharer_state on true
where s.individual_id = p_id and c.service_type = 'OPS'
union all
select c.id, c.service_type, case
when t.termination_date is not null and coalesce(s.close_date, c.close_date) is null
then 'Прекращен'
when s.open_date is not null and coalesce(s.close_date, c.close_date) is null
then
case
when exists (
select
sh.id
from ourpension.sharer sh
left join back_office.pension_calculation pc on sh.id = pc.sharer_id
left join ourpension.npo_pension_agreement ag on ag.pension_calculation_id = pc.id and ag.accepted is not null and ag.canceled is null
left join ourpension.npo_pension_assignment_application pa on pa.sharer_id = sh.id and pa.accepted is not null and pa.canceled is null
where (coalesce(pa.id, ag.id) is not null) and sh.id = s.id
) then
'Выплатной период'
when not exists (
select sh.id
from ourpension.sharer sh
join ourpension.contract ct on sh.contract_id = ct.id and ct.service_type <> 'OPS'
left join back_office.pension_calculation pc on sh.id = pc.sharer_id
left join ourpension.npo_pension_agreement ag on ag.pension_calculation_id = pc.id and ag.accepted is not null and ag.canceled is null
left join ourpension.npo_pension_assignment_application pa on pa.sharer_id = sh.id and pa.accepted is not null and pa.canceled is null
where (coalesce(pa.id, ag.id) is not null)
and sh.id = s.id
) then
'Накопительный период'
else 'Действующий'
end
when coalesce(s.close_date, c.close_date) is not null
then 'Закрыт'
else 'Зарегистрирован'
end,
c.client_type,
s.id,
sharer_state.state
from ourpension.contract c
inner join ourpension.sharer s on s.contract_id = c.id
left join termination t on t.contract_id =c.id and t.num = 1
inner join lateral ourpension.f_get_sharer_state(s.id) sharer_state on true
where s.individual_id = p_id and c.service_type <> 'OPS'
*/
) c);
end if;
v_actual_zev_for_ouev := exists(
select 1
from ourpension.application_for_one_time_contribution a
join ourpension.sharer s on s.id = a.sharer_id
where a.accepted is not null
and a.canceled is null
and a.rejected is null
and s.individual_id = p_id
and case when extract(month from CURRENT_DATE) between 1 and 1 then -- по 31 января
a.date between date_trunc('year', CURRENT_DATE) - interval '1 year' and date_trunc('year', CURRENT_DATE) + interval '1 month' - interval '1 day'
else
date_trunc('year', a.date) = date_trunc('year', CURRENT_DATE) -- тот же самый год
end
-- and CURRENT_DATE < date_trunc('year', a.date) + interval '2 year' - interval '1 day'
);
v_apps = json_build_object(
'pdn_br', v_is_not_death
,'pdn_br_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти' else '' end
-- (select * from v_is_not_death) pdn_br,
,'cnl', v_application_for_cancel and v_is_not_death
,'cnl_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not v_application_for_cancel then 'нет заявлений для аннулирования'
else ''
end
--(select * from application) and (select * from is_not_death) cnl,
,'dpp', v_is_not_death
and ( exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Заключен', 'Ожидание подтверждения ПФР', 'Ожидание поступления взносов', 'Накопительный период', 'Выплатной период')
)
or exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'NPO' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Заключен')
)
or exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'PDS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Оформлен')
)
)
,'dpp_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not (exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Заключен', 'Ожидание подтверждения ПФР', 'Ожидание поступления взносов', 'Накопительный период', 'Выплатной период')
)
or exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'NPO' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Заключен')
)
or exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'PDS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Оформлен')
)
) then 'нет действующего договора'
else ''
end
-- (exists(
-- select 1 from contract where service_type = 'OPS' and state in ('Действующий', 'Зарегистрирован', 'Заключен', 'Ожидание подтверждения ПФР', 'Ожидание поступления взносов', 'Накопительный период', 'Выплатной период')
-- ) or exists(
-- select 1 from contract where service_type = 'NPO' and state in ('Действующий', 'Зарегистрирован', 'Заключен')
-- ) or exists(
-- select 1 from contract where service_type = 'PDS' and state in ('Действующий', 'Зарегистрирован', 'Оформлен')
-- )) and (select * from is_not_death) dpp, РСПН
,'np_spv', v_is_not_death
and exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Выплатной период','Накопительный период')
)
,'np_spv_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Выплатной период','Накопительный период')
) then 'нет действующего договора ОПС'
else ''
end
-- exists(
-- select 1 from contract where service_type = 'OPS' and state in ('Действующий', 'Выплатной период','Накопительный период')
-- ) and (select * from is_not_death) np_spv,
,'zev', v_is_not_death
and exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Выплатной период','Накопительный период')
)
and exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'PDS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Накопительный период', 'Выплатной период', 'Оформлен')
)
,'zev_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists( select ss from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Выплатной период','Накопительный период')
) then 'нет действующего договора ОПС'
when not exists(select ss from unnest(v_contract) as ss where ss->>'service_type' = 'PDS' and
ss->>'state' in ('Действующий', 'Зарегистрирован', 'Накопительный период', 'Выплатной период', 'Оформлен')
) then 'нет действующего договора ПДС'
else ''
end
-- exists(
-- select 1 from contract where service_type = 'OPS' and state in ('Действующий', 'Выплатной период','Накопительный период')
-- ) and
-- exists(
-- select 1 from contract where service_type = 'PDS' and state in ('Действующий', 'Зарегистрирован', 'Накопительный период', 'Выплатной период', 'Оформлен')
-- ) and (select * from is_not_death) zev,
,'rv', v_is_not_death
and exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS') and
ss->>'sharer_state' in ('Накопительный период', 'Действующий')
)
,'rv_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS') and
ss->>'sharer_state' in ('Накопительный период', 'Действующий')
) then 'нет счетов НПО или ПДС готовых к назначению выплат'
else ''
end
-- exists(
-- select 1 from contract where service_type in ('NPO', 'PDS') and sharer_state in ('Накопительный период', 'Действующий')
-- ) and (select * from is_not_death) rv,
,'ra', v_is_not_death
and exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS')
and (
( ss->>'state' in ('Действующий', 'Накопительный период', 'Выплатной период')
and (ss->>'client_type')::int = 0
)
or
( ss->>'state' in ('Действующий', 'Прекращен')
and (ss->>'client_type')::int > 0
)
)
)
,'ra_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS')
and (
( ss->>'state' in ('Действующий', 'Накопительный период', 'Выплатной период')
and (ss->>'client_type')::int = 0
)
or
( ss->>'state' in ('Действующий', 'Прекращен')
and (ss->>'client_type')::int > 0
)
)
) then 'нет договров НПО или ПДС готовых к расторжению'
else ''
end
-- exists(
-- select 1 from contract where service_type in ('NPO', 'PDS') and ((state in ('Действующий', 'Накопительный период', 'Выплатной период') and client_type = 0) or (state in ('Действующий', 'Прекращен') and client_type > 0))
-- ) and (select * from is_not_death) ra,
,'snv', v_is_not_death
and exists( select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS') and
ss->>'state' in ('Накопительный период', 'Выплатной период', 'Действующий')
)
,'snv_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists( select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO', 'PDS') and
ss->>'state' in ('Накопительный период', 'Выплатной период', 'Действующий')
) then 'нет договров НПО или ПДС для которых предусмотрен налоговый вычет'
else ''
end
-- exists(
-- select 1 from contract where service_type in ('NPO', 'PDS') and state in ('Накопительный период', 'Выплатной период', 'Действующий')
-- ) and (select * from is_not_death) snv,
,'uoev', v_is_not_death and v_actual_zev_for_ouev
,'uoev_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not v_actual_zev_for_ouev then 'нет подходящего заявления ЗЕВ'
else ''
end
-- exists(select 1
-- from ourpension.application_for_one_time_contribution a
-- join ourpension.sharer s on s.id = a.sharer_id
-- where a.accepted is not null
-- and a.canceled is null
-- and a.rejected is null
-- and s.individual_id = p_id
-- and CURRENT_DATE < date_trunc('year', a.date) + interval '2 year' - interval '1 day'
-- ) and (select * from is_not_death)
-- uoev,
,'pp', array_length(v_contract, 1) > 0
,'pp_comment', case when array_length(v_contract, 1) <= 0 then 'нет пенсионных счетов'
else ''
end
-- exists(
-- select 1 from contract
-- ) pp,
,'provo', v_is_not_death
and exists( select ss from unnest(v_contract) as ss
where ss->>'service_type' in ('NPO', 'PDS')
and
ss->>'state' in ('Действующий', 'Накопительный период', 'Выплатной период')
and
( ss->>'sharer_state' in ('Выплатной период', 'Период выплат')
or
ss->>'sharer_state' not in ('Закрыт', 'Накопительный период', 'Подготовка к закрытию')
)
)
,'provo_comment', case when not v_is_not_death then 'зарегистрированна информация о смерти'
when not exists( select ss from unnest(v_contract) as ss
where ss->>'service_type' in ('NPO', 'PDS')
and
ss->>'state' in ('Действующий', 'Накопительный период', 'Выплатной период')
and
( ss->>'sharer_state' in ('Выплатной период', 'Период выплат')
or
ss->>'sharer_state' not in ('Закрыт', 'Накопительный период', 'Подготовка к закрытию')
)
) then 'нет счетов для приостановки/возобновления выплат'
else ''
end
-- exists(
-- select 1 from contract c
-- where c.service_type in ('NPO', 'PDS')
-- and (c.state in ('Действующий', 'Накопительный период', 'Выплатной период'))
-- and (c.sharer_state in ('Выплатной период', 'Период выплат') or c.sharer_state not in ('Закрыт', 'Накопительный период', 'Подготовка к закрытию'))
--
-- ) and (select * from is_not_death) provo
);
if p_full_info then
v_ops_app := (select array_agg(row_to_json(c)) from (
select opaa.number, case when opaa.ops_payment_type_code = '02' then 'NOPS'
when opaa.ops_payment_type_code = '04' then 'NP'
when opaa.ops_payment_type_code = '05' then 'SPV'
end as app_type
from ourpension.ops_pension_assignment_application opaa
join ourpension.sharer s on opaa.sharer_id = s.id
join ourpension.contract c on s.contract_id = c.id
left join back_office.ops_payment_decision opd on opd.ops_pension_assignment_application_id = opaa.id
and opd.canceled is null
and opd.rejected is null
and opd.accepted is not null
and opd.decision_type_code in ('ОТКЗ')
/*
left join lateral (
select decision_type as res
from mgr.ft_dwh_get__ops_payment_decision_pension( jsonb_build_object('p_sOpsNumber', c.number, 'p_sDtOps', c.date) )
where app_number = opaa."number" and app_date = opaa."date"
and decision_type in ('Отказ')
) r on true
*/
where
opaa.ops_payment_type_code in ('02', '04', '05')
and s.individual_id = p_id
and opaa.accepted is not null and opaa.rejected is null and opaa.canceled is null
-- and r.res is null
and (opd.id is null and opaa.decision_payment_type_code is distinct from '00')
)c);
if v_apps->>'zev' = 'true' then
v_active_zev := (
select string_agg(nullif(concat( afotc.number, ' от ', to_char(afotc.accepted, 'DD ') || lower(m.genitive_case_name) || to_char(afotc.accepted, ' YYYY') ), ' от '), ', ')
from ourpension.f_get_active_zev(p_id) afotc
join "data"."month" m on m.month_number = extract(month from afotc.accepted)
-- from ourpension.application_for_one_time_contribution afotc
-- join "data"."month" m on m.month_number = extract(month from afotc.accepted)
-- join ourpension.sharer s on afotc.sharer_id = s.id
-- join ourpension.contract c on s.contract_id = c.id
--
-- left join lateral (
-- select pds_refuse_reason as res
-- from mgr.ft_dwh_get__pds_app_one_time_contribution_refuse_decision(jsonb_build_object('p_sPdsNumber', c.number,'p_sDtPds', c.date) )
-- where app_number = afotc."number" and app_date = afotc."date"
-- ) r on true
--
-- left join ourpension.application_for_one_time_contribution ouef
-- on ouef.depend_application = afotc.id and
-- ouef.accepted is not null and ouef.rejected is null and ouef.canceled is null
--
--
-- where afotc.type_app_id = 28 and afotc.individual_id = p_id
-- and r.res is null and ouef.id is null
-- and afotc.accepted is not null and afotc.rejected is null and afotc.canceled is null
);
v_apps := v_apps || json_build_object('zev', not( nullif(v_active_zev, ' от ') is not null or v_ops_app is not null),
'zev_comment', case when v_ops_app is not null then 'Есть неотклоненное выплатное заявление ОПС'
when v_active_zev is not null then 'Есть неотклонееное ЗЕВ'
else ''
end
)::jsonb;
else
end if;
if v_active_zev is not null then
v_active_zev := 'обращаем Ваше внимание, что в соответствии с действующим законодательством перевод накоплений в качестве единовременного взноса по договору долгосрочных сбережений возможен только до момента обращения за назначением выплаты за счет пенсионных накоплений.
После регистрации Вашего заявления на выплату Фондом будет принято Решение об отказе по Вашему заявлению ' || v_active_zev || '.';
else
v_active_zev := '';
end if;
if v_apps->>'np_spv' = 'true' then
select (ss->>'sharer_id')::bigint into v_sharer_id from unnest(v_contract) as ss where ss->>'service_type' = 'OPS' and
ss->>'state' in ('Действующий', 'Выплатной период','Накопительный период');
select birth_date + make_interval(years => case when sex = 'M' then 60 else 55 end ) into v_pens_date from ourpension.individual i where i.id = p_id;
-- value_pension, type_pension, dur, spn, ops, dps, barrier_ops, barrier_spn
v_pens_calc := (select array_agg(row_to_json(c)) from (
select value_pension, type_pension, dur, spn, ops, dps from back_office.pension_calculation(least( now()::date, v_pens_date), v_sharer_id)
)c);
v_is_spv_decision := exists(select * from integration.get_all_client_applications(p_id) applications
inner join lateral (select * from integration.get_decision_for_application(applications.id, applications.type) ) as decision on true
where type = 'SPV'
and decision.decision_params->>'status' = 'Принято'
and (decision.decision_params->>'success_flag')::boolean = true
);
v_is_np_decision := exists(select * from integration.get_all_client_applications(p_id) applications
inner join lateral (select * from integration.get_decision_for_application(applications.id, applications.type) ) as decision on true
where type = 'NP'
and decision.decision_params->>'status' = 'Принято'
and (decision.decision_params->>'success_flag')::boolean = true
);
v_is_nops_decision := exists(select * from integration.get_all_client_applications(p_id) applications
inner join lateral (select * from integration.get_decision_for_application(applications.id, applications.type) ) as decision on true
where type = 'NOPS'
and decision.decision_params->>'status' = 'Принято'
and (decision.decision_params->>'success_flag')::boolean = true
);
v_apps := v_apps || json_build_object(
'spv', not v_is_spv_decision and
exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED' and (ss->>'value_pension')::numeric > 0)
and not exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'SPV'),
'spv_comment', case when v_is_spv_decision then 'есть положительное решение о назначении cрочной пенсионной выплаты'
when exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'SPV') then 'есть неоклоненное заявление на назначение cрочной пенсионной выплаты'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED' and (ss->>'value_pension')::numeric > 0) then
'нет средств для назначении cрочной пенсионной выплаты'
else
'' || v_active_zev
end
)::jsonb;
v_apps := v_apps || json_build_object(
'np_ev', not v_is_nops_decision
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'ONETIME' and (ss->>'value_pension')::numeric > 0)
and not exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NOPS'),
'np_ev_comment', case when v_is_nops_decision then 'есть положительное решение о назначении пенсии'
when exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NOPS') then
'найдено неоклоненное заявление на назначение пожизненной пенсии'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'ONETIME' and (ss->>'value_pension')::numeric > 0)
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0) then
'будет назначена пожизненная пенсия'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'ONETIME' and (ss->>'value_pension')::numeric > 0)
and not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0) then
'нет средсв для назначении пенсии'
else
'' || v_active_zev
end
)::jsonb;
--raise notice 'v_is_nops_decision %', v_is_nops_decision::text;
--raise notice 'v_pens_calc %', exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0)::text;
--raise notice 'v_ops_app %', exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NOPS')::text;
--raise notice 'v_ops_app %', v_ops_app::text;
v_apps := v_apps || json_build_object(
'np_life', not v_is_nops_decision
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0)
and not exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NOPS'),
'np_life_comment', case when v_is_nops_decision then 'есть положительное решение о назначении пенсии'
when exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NOPS') then
'найдено неоклоненное заявление на назначение пожизненной пенсии'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0)
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'ONETIME' and (ss->>'value_pension')::numeric > 0) then
'будет назначена единовременная выплата'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME_ALL' and (ss->>'value_pension')::numeric > 0)
and not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'ONETIME' and (ss->>'value_pension')::numeric > 0) then
'нет средсв для назначении пенсии'
else
'' || v_active_zev
end
)::jsonb;
v_apps := v_apps || json_build_object(
'np_ev_dsp', not v_is_np_decision
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED_TERM' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and not exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NP'),
'np_ev_dsp_comment', case when v_is_np_decision then 'есть положительное решение о назначении пенсии с учетом ДСВ/СОФ/МК'
when exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NP') then
'найдено неоклоненное заявление на назначение пожизненной пенсии без учета ДСВ/СОФ/МК'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED_TERM' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0) then
'может быть назначена пожизненная пенсия без учета ДСВ/СОФ/МК'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED_TERM' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0) then
'нет средсв ДСВ/СОФ/МК'
else
'' || v_active_zev
end
)::jsonb;
v_apps := v_apps || json_build_object(
'np_life_dsp', not v_is_np_decision
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and not exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NP'),
'np_life_dsp_comment', case when v_is_np_decision then 'есть положительное решение о назначении пенсии с учетом ДСВ/СОФ/МК'
when exists(select ss from unnest(v_ops_app) as ss where ss->>'app_type' = 'NP') then
'найдено неоклоненное заявление на назначение пожизненной пенсии без учета ДСВ/СОФ/МК'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED_TERM' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0) then
'будет назначена единовременная выплата'
when not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'LIFETIME' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0)
and not exists(select ss from unnest(v_pens_calc) as ss where ss->>'type_pension' = 'FIXED_TERM' and (ss->>'value_pension')::numeric > 0 and (ss->>'dps')::numeric > 0) then
'нет средсв ДСВ/СОФ/МК'
else
'' || v_active_zev
end
)::jsonb;
v_apps := v_apps || json_build_object(
'is_ops_money', exists(select ss from unnest(v_pens_calc) as ss where (ss->>'ops')::numeric > 0),
'is_spn_money', exists(select ss from unnest(v_pens_calc) as ss where (ss->>'spn')::numeric > 0),
'is_dps_money', exists(select ss from unnest(v_pens_calc) as ss where (ss->>'dps')::numeric > 0)
)::jsonb;
else
v_apps := v_apps || json_build_object(
'spv', false,
'spv_comment', v_apps->>'np_spv_comment',
'np_ev', false,
'np_ev_comment', v_apps->>'np_spv_comment',
'np_life', false,
'np_life_comment', v_apps->>'np_spv_comment',
'np_ev_dsp', false,
'np_ev_dsp_comment', v_apps->>'np_spv_comment',
'np_life_dsp', false,
'np_life_dsp_comment', v_apps->>'np_spv_comment',
'is_ops_money', false,
'is_spn_money', false,
'is_dps_money', false
)::jsonb;
end if;
if v_apps->>'rv' = 'true' and exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('PDS') and
ss->>'sharer_state' in ('Накопительный период', 'Действующий') ) then -- есть возможность подать выплатное ПДС, проверяем возраст
select birth_date + make_interval(years => case when sex = 'M' then 60 else 55 end ) into v_pens_date from ourpension.individual i where i.id = p_id;
if v_pens_date < now() then
v_apps := v_apps || json_build_object(
'npds', true,
'npds_comment', ''
)::jsonb;
else
v_apps := v_apps || json_build_object(
'npds', false,
'npds_comment', 'не наступил пенсионный возраст для выплат по договору ПДС'
)::jsonb;
end if;
else
v_apps := v_apps || json_build_object(
'npds', false,
'npds_comment', 'нет действующего договора ПДС'
)::jsonb;
end if;
if v_apps->>'rv' = 'true' and exists(select ss from unnest(v_contract) as ss where ss->>'service_type' in ('NPO') and
ss->>'sharer_state' in ('Накопительный период', 'Действующий') ) then -- есть возможность подать выплатное ПДС, проверяем возраст
v_apps := v_apps || json_build_object(
'nnpo', true,
'nnpo_comment', ''
)::jsonb;
else
v_apps := v_apps || json_build_object(
'nnpo', false,
'nnpo_comment', 'нет действующего договора НПО'
)::jsonb;
end if;
if v_apps->>'pdn_br' = 'true' then
v_apps := v_apps || json_build_object(
'pdn', true,
'pdn_comment', ''
)::jsonb;
if exists(select ss from unnest(v_contract) as ss ) then
v_apps := v_apps || json_build_object(
'br', true,
'br_comment', ''
)::jsonb;
else
v_apps := v_apps || json_build_object(
'br', false,
'br_comment', 'не заключено ни одного договора'
)::jsonb;
end if;
else
v_apps := v_apps || json_build_object(
'pdn', false,
'pdn_comment', 'зарегистрированна информация о смерти',
'br', false,
'br_comment', 'зарегистрированна информация о смерти'
)::jsonb;
end if;
end if;
return v_apps;
END;
$function$
