https://pastein.ru/t/8dg
Загрузка данных
-- DROP FUNCTION ourpension.f_reconciliation_of_operations(date, date);
CREATE OR REPLACE FUNCTION ourpension.f_reconciliation_of_operations(db date, de date)
RETURNS TABLE(num integer, title text, beg numeric, pfr_in numeric, npf_in numeric, other_in numeric, total_in numeric, pension numeric, assignee numeric, pens_ass numeric, pfr_out numeric, npf_out numeric, total_out numeric, returns numeric, acc_red numeric, acc_res_red numeric, gar numeric, id numeric, ops_to_pds numeric, comission numeric, from_rops_to_ops_pds numeric, "end" numeric, total_cr numeric, total_db numeric, total_cr_db numeric, "check" numeric)
LANGUAGE plpgsql
AS $function$
begin
create temp table acc_types_agg_in_group_temp (group_num int, group_type text, type_in text, type_out text default null)
on commit drop;
insert into acc_types_agg_in_group_temp
values
(1,'Н',null,null),
(1,'Р(Е)',null,null),
(1,null,'НВ ОПС',null),
(2,'Н',null,'{ДПС МК,ДПС МК ИД}'),
(2,'Р(Е)',null,'{Р(Е) ДПС МК,Р(Е) ДПС МК ИД}'),
(3,null,'НВ ДПС МК',null),
(3,null,'ДПС МК',null),
(3,null,'ДПС МК ИД',null),
(3,null,'Р(Е) ДПС МК',null),
(3,null,'Р(Е) ДПС МК ИД',null),
(4,'Р ПП',null,null),
(4,null,'НВ ПП ОПС',null),
(5,null,'НВ ОПС',null),
(5,null,'НВ ПП ОПС',null),
(5,'Н',null,'{ДПС МК,ДПС МК ИД}'),
(5,'Р(Е)',null,'{Р(Е) ДПС МК,Р(Е) ДПС МК ИД}'),
(5,null,'ДПС МК',null),
(5,null,'ДПС МК ИД',null),
(5,null,'Р(Е) ДПС МК',null),
(5,null,'Р(Е) ДПС МК ИД',null),
(5,'Р ПП',null,null),
(6,null,'НВ ДПС Г',null),
(6,null,'НВ ДПС Л',null),
(6,null,'Р(С)',null),
(6,null,'Р(С) ИД',null),
(6,null,'Р(С) ОПС',null),
(6,null,'Р(С) ОПС ИД',null),
(6,null,'Р(С) ДПС Л',null),
(6,null,'Р(С) ДПС Л ИД',null),
(6,null,'Р(С) ДПС Р',null),
(6,null,'Р(С) ДПС Р ИД',null),
(6,null,'Р(С) ДПС Г',null),
(6,null,'Р(С) ДПС Г ИД',null),
(7,null,'НВ ДПС МК',null),
(7,null,'Р(С) ДПС МК',null),
(7,null,'Р(С) ДПС МК ИД',null),
(7,null,'НВ ДПС МК',null),
(7,null,'НВ ПП ДПС МК',null),
(8,null,'Р(С)',null),
(8,null,'Р(С) ИД',null),
(8,null,'Р(С) ОПС',null),
(8,null,'Р(С) ОПС ИД',null),
(8,null,'Р(С) ДПС Л',null),
(8,null,'Р(С) ДПС Л ИД',null),
(8,null,'Р(С) ДПС Р',null),
(8,null,'Р(С) ДПС Р ИД',null),
(8,null,'Р(С) ДПС Г',null),
(8,null,'Р(С) ДПС Г ИД',null),
(8,null,'Р(С) ДПС МК',null),
(8,null,'Р(С) ДПС МК ИД',null),
(8,null,'НВ ДПС МК',null),
(8,null,'НВ ПП ДПС МК',null),
(9,'Р(П)',null,null),
(10,null,'Р ОПС',null),
(10,null,'Р ОПС ИД',null),
(11,'Н',null,'{ДПС МК,ДПС МК ИД}'),
(11,'Р(Е)',null,'{Р(Е) ДПС МК,Р(Е) ДПС МК ИД}'),
(11,null,'ДПС МК',null),
(11,null,'ДПС МК ИД',null),
(11,null,'Р(Е) ДПС МК',null),
(11,null,'Р(Е) ДПС МК ИД',null),
(11,'Р ПП',null,null),
(11,null,'Р(С)',null),
(11,null,'Р(С) ИД',null),
(11,null,'Р(С) ОПС',null),
(11,null,'Р(С) ОПС ИД',null),
(11,null,'Р(С) ДПС Л',null),
(11,null,'Р(С) ДПС Л ИД',null),
(11,null,'Р(С) ДПС Р',null),
(11,null,'Р(С) ДПС Р ИД',null),
(11,null,'Р(С) ДПС Г',null),
(11,null,'Р(С) ДПС Г ИД',null),
(11,null,'Р(С) ДПС МК',null),
(11,null,'Р(С) ДПС МК ИД',null),
(11,null,'НВ ДПС МК',null),
(11,null,'НВ ПП ДПС МК',null),
(11,'Р(П)',null,null),
(11,null,'Р ОПС',null),
(11,null,'Р ОПС ИД',null)
;
create temp table oper_types_agg_in_group_temp (group_num int, group_type text, cr_db text, type_out text default null)
on commit drop;
insert into oper_types_agg_in_group_temp
values
(1,'PENSION_SAVINGS_ENROLLMENT','cr',null)
,
(2,'PENSION_SAVINGS_ENROLLMENT','cr',null),
(3,'MANUAL_TEMPORARY',null,null),
(3,'ENROLLMENT_OTHER',null,null),
(4,'PENSION_SAVINGS_ENROLLMENT','cr',null),
(4,'MANUAL_TEMPORARY',null,null),
(4,'ENROLLMENT_OTHER',null,null),
(5,'PAYMENT_SPV',null,null),
(5,'PAYMENT_NP',null,null),
(5,'PAYMENT_EV',null,null),
(5,'ADDITIONAL_PAYMENT_EV',null,null),
(6,'PAYMENT_PP',null,null),
(6,'ADDITIONAL_PAYMENT_PP',null,null),
(7,'PAYMENT_SPV',null,null),
(7,'PAYMENT_NP',null,null),
(7,'PAYMENT_EV',null,null),
(7,'ADDITIONAL_PAYMENT_EV',null,null),
(7,'PAYMENT_PP',null,null),
(7,'ADDITIONAL_PAYMENT_PP',null,null),
(8,'TRANSFER','db',null),
(9,'TRANSFER','db',null),
(10,'TRANSFER','db',null),
(11,'RETURN',null,null),
(12,'TRANSFER_TO_ASSIGNEE_RESERVE',null,null),
(13,'TRANSFER_TO_RESERVE',null,null),
(14,'GUARANTEE_ENROLLMENT',null,null),
(14,'GUARANTEE',null,null),
(15,'PENSION_INVESTMENT_REVENUE_ENROLLMENT',null,null),
(16,'PDS_OPS_ENROLLMENT',null,null),
(16,'PDS_TRANSFER_OPS',null,null),
(17,'COMMISSION_NP_FROM_FUND_RESERVE',null,null),
(18,'TRANSFER_TO_INSURED_FUND_RESERVE_OPS_PDS',null,null)
;
case
when db is null or de is null then
return query
select null:: integer, null:: text, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric,
null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric;
else
create temp table temp_transactions (owner_sharer_id bigint, owner_entity_id int, owner_individual_id bigint, acc_type_value numeric, direction text,
effective_date date, mnemonics text, group_mnemonics text)
on commit drop;
create temp table selected_funds as
select a.id
from ourpension.contract c
join ourpension.sharer sh on sh.contract_id = c.id
join back_office."account" a on a.owner_sharer_id = sh.id
where c.fund_id = 296;
insert into temp_transactions
(select ca.owner_sharer_id, ca.owner_entity_id, ca.owner_individual_id, t.value acc_type_value, 'credit' direction, t.effective_date, at.mnemonics, atg.mnemonics group_mnemonics
from back_office."transaction" t
join back_office."account" ca on ca.id = t.credit_account_id
join selected_funds sf on sf.id = ca.id
join back_office.account_type at on at.id = ca.account_type_id and at.service_type = 'OPS'
join back_office.account_type_group atg on atg.id = at.account_type_group_id
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id--01.12.2025
join oper_types_agg_in_group_temp temp_1 on temp_1.group_type = ot.code--01.12.2025
join acc_types_agg_in_group_temp acc_temp on --01.12.2025
case when acc_temp.group_type is null then true else acc_temp.group_type = atg.mnemonics end--01.12.2025
and case when acc_temp.type_in is null then true else acc_temp.type_in = at.mnemonics end--01.12.2025
and case when acc_temp.type_out is null then true else at.mnemonics <> all(acc_temp.type_out::text[]) end--01.12.2025
where t.effective_date <= de
and o.approved is not null /*Авторизована*/
union all
select da.owner_sharer_id, da.owner_entity_id, da.owner_individual_id, -t.value acc_type_value, 'debit' direction, t.effective_date, at.mnemonics, atg.mnemonics group_mnemonics
from back_office."transaction" t
join back_office."account" da on da.id = t.debit_account_id
join selected_funds sf on sf.id = da.id
join back_office.account_type at on at.id = da.account_type_id and at.service_type = 'OPS'
join back_office.account_type_group atg on atg.id = at.account_type_group_id
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id--01.12.2025
join oper_types_agg_in_group_temp temp_1 on temp_1.group_type = ot.code--01.12.2025
join acc_types_agg_in_group_temp acc_temp on --01.12.2025
case when acc_temp.group_type is null then true else acc_temp.group_type = atg.mnemonics end--01.12.2025
and case when acc_temp.type_in is null then true else acc_temp.type_in = at.mnemonics end--01.12.2025
and case when acc_temp.type_out is null then true else at.mnemonics <> all(acc_temp.type_out::text[]) end--01.12.2025
where t.effective_date <= de
and o.approved is not null /*Авторизована*/
);
return query
with /*selected_fund as (
select
)
,*/balance_start as (
select
acc_temp.group_num as mnem_gr,
sum(q.acc_type_value) beg
from acc_types_agg_in_group_temp acc_temp
join (select
q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'start'
from temp_transactions q
where q.effective_date <= db
group by q.direction, q.mnemonics, q.group_mnemonics) q on
case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
group by acc_temp.group_num
), balance_end as (
select
acc_temp.group_num as mnem_gr,
sum(q.acc_type_value) end
from acc_types_agg_in_group_temp acc_temp
join (select
q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'end'
from temp_transactions q
where q.effective_date <= de
group by q.direction, q.mnemonics, q.group_mnemonics) q on
case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
group by acc_temp.group_num
), lets_start as (
select
acc_temp.group_num as mnem_gr,
t_temp.group_num as oper_gr,
sum(case when t_temp.group_num = '5' then t.value else 0 end) pension,
sum(case when t_temp.group_num = '6' then t.value else 0 end) assignee,
sum(case when t_temp.group_num = '7' then t.value else 0 end) pens_ass,
sum(case when t_temp.group_num = '16' then t.value else 0 end) ops_to_pds,
sum(case when t_temp.group_num = '17' then t.value else 0 end) comission,
sum(case when t_temp.group_num = '18' then t.value else 0 end) from_rops_to_ops_pds,
sum(t.value) sum_value
from back_office.transaction t
join back_office.account da on t.debit_account_id = da.id
join selected_funds sf on sf.id = da.id
join back_office.account_type at on at.id = da.account_type_id
join back_office.account_type_group atg on at.account_type_group_id = atg.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = atg.mnemonics end
and case when acc_temp.type_in is null then true else at.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else at.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code and t_temp.group_num in ('5','6','7','11','12','13','14','15','16','17','18')
where t.effective_date between db and de
and o.approved is not null /*Авторизована*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
), deposit as (
select
acc_temp.group_num as mnem_gr,
string_agg(distinct at.mnemonics,', ') ,
string_agg(distinct atg.mnemonics,', ') ,
t_temp.group_num as oper_gr,
sum(case when f.name_in_pfr in ('ПФР','СФР') then t.value else 0 end) pfr_in,
sum(case when f.name_in_pfr not in ('ПФР','СФР') then t.value else 0 end) npf_in,
sum(case when t_temp.group_num = '3' then t.value else 0 end) other_in,
sum(t.value) total_in
from back_office.transaction t
join back_office.account da on t.credit_account_id = da.id
join selected_funds sf on sf.id = da.id
join back_office.account_type at on at.id = da.account_type_id
join back_office.account_type_group atg on at.account_type_group_id = atg.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = atg.mnemonics end
and case when acc_temp.type_in is null then true else at.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else at.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code and t_temp.group_num in ('1','2','3','4')
join back_office.incoming_order_operation iop on iop.operation_id = t.operation_id
join back_office.incoming_order io on iop.incoming_order_id = io.id
and io.service_type = 'OPS'
join ourpension.entity e on io.payer_entity_id = e.id
join ourpension.fund f on f.entity_id = e.id
where t.effective_date between db and de
and o.approved is not null /*Авторизована*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
), transfert as (
select
acc_temp.group_num as mnem_gr,
string_agg(distinct at.mnemonics,', ') ,
string_agg(distinct atg.mnemonics,', ') ,
t_temp.group_num as oper_gr,
sum(case when f.name_in_pfr in ('ПФР','СФР') then t.value else 0 end) pfr_out,
sum(case when f.name_in_pfr not in ('ПФР','СФР') then t.value else 0 end) npf_out,
sum(t.value) total_out
from back_office.transaction t
join back_office.account da on t.debit_account_id = da.id
join selected_funds sf on sf.id = da.id
join back_office.account_type at on at.id = da.account_type_id
join back_office.account_type_group atg on at.account_type_group_id = atg.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = atg.mnemonics end
and case when acc_temp.type_in is null then true else at.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else at.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code and t_temp.group_num in ('8','9','10')
join back_office.account ca on t.credit_account_id = ca.id
join ourpension.entity e on ca.owner_entity_id = e.id
join ourpension.fund f on f.entity_id = e.id
where t.effective_date between db and de
and o.approved is not null /*Авторизована*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
), anker as (
select *
from unnest ((select '{'||string_agg(n::text,',')||'}' from pg_catalog.generate_series(1,11) gs(n))::int[],
array['СПН, учтенные на счетах ЗЛ (кроме умерших ЗЛ)',
'СПН, учтенные на счетах ЗЛ (кроме МК)',
'Материнский капитал, учтенный на счетах ЗЛ',
'СПН, учтенные на счете умерших ЗЛ',
'Итого СПН, учтенные на счетах',
'Резерв СПВ (выплаты без учета МК)',
'Резерв СПВ (выплаты за счет материнского капитала)',
'Резерв СПВ',
'ВР',
'РОПС',
'Итого ПН']) gs(num,title)
)
select gen.*, gen.total_cr+gen.total_db total_cr_db, gen.acc_res_red - (gen.total_cr+gen.total_db) "check"
from
(
select gs.num, gs.title, s0.beg, s1.pfr_in ,s2.npf_in ,s3.other_in ,s4.total_in ,s5.pension,s6.assignee,s7.pens_ass, s8.pfr_out,s9.npf_out ,s10.total_out ,s11.sum_value "returns",
s12.sum_value acc_red,s13.sum_value acc_res_red,s14.sum_value gar,s15.sum_value id, s19.ops_to_pds, s20.comission, s21.from_rops_to_ops_pds, s16."end", s17.total_cr, s18.total_db
from anker gs
left join (select *
from balance_start) s0 on gs.num = s0.mnem_gr
left join (select *
from deposit
where oper_gr = 1) s1 on gs.num = s1.mnem_gr
left join (select *
from deposit
where oper_gr = 2) s2 on gs.num = s2.mnem_gr
left join (select *
from deposit
where oper_gr = 3) s3 on gs.num = s3.mnem_gr
left join (select *
from deposit
where oper_gr = 4) s4 on gs.num = s4.mnem_gr
left join (select *
from lets_start
where oper_gr = 5) s5 on gs.num = s5.mnem_gr
left join (select *
from lets_start
where oper_gr = 6) s6 on gs.num = s6.mnem_gr
left join (select *
from lets_start
where oper_gr = 7) s7 on gs.num = s7.mnem_gr
left join (select *
from transfert
where oper_gr = 8) s8 on gs.num = s8.mnem_gr
left join (select *
from transfert
where oper_gr = 9) s9 on gs.num = s9.mnem_gr
left join (select *
from transfert
where oper_gr = 10) s10 on gs.num = s10.mnem_gr
left join (select *
from lets_start
where oper_gr = 11) s11 on gs.num = s11.mnem_gr
left join (select *
from lets_start
where oper_gr = 12) s12 on gs.num = s12.mnem_gr
left join (select *
from lets_start
where oper_gr = 13) s13 on gs.num = s13.mnem_gr
left join (select *
from lets_start
where oper_gr = 14) s14 on gs.num = s14.mnem_gr
left join (select *
from lets_start
where oper_gr = 15) s15 on gs.num = s15.mnem_gr
left join (select *
from balance_end) s16 on gs.num = s16.mnem_gr
left join (select *
from (select
acc_temp.group_num as mnem_gr,
sum(q.acc_type_value) total_cr
from acc_types_agg_in_group_temp acc_temp
join (select
q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'start'
from temp_transactions q
where direction = 'credit'
group by q.direction, q.mnemonics, q.group_mnemonics) q on
case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
group by acc_temp.group_num)) s17 on gs.num = s17.mnem_gr
left join (select *
from (select
acc_temp.group_num as mnem_gr,
sum(q.acc_type_value) total_db
from acc_types_agg_in_group_temp acc_temp
join (select
q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'start'
from temp_transactions q
where direction = 'debit'
group by q.direction, q.mnemonics, q.group_mnemonics) q on
case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
group by acc_temp.group_num)) s18 on gs.num = s18.mnem_gr
left join (select *
from lets_start
where oper_gr = 16) s19 on gs.num = s19.mnem_gr
left join (select *
from lets_start
where oper_gr = 17) s20 on gs.num = s20.mnem_gr
left join (select *
from lets_start
where oper_gr = 18) s21 on gs.num = s21.mnem_gr
order by num
) gen
;
end case;
drop table if exists selected_funds;
END;
$function$
;
