https://pastein.ru/t/jfS
Загрузка данных
CREATE OR REPLACE FUNCTION back_office.get_eps_moving(p_beg_period date DEFAULT NULL::date, p_end_period date DEFAULT CURRENT_DATE, p_service_types character[] DEFAULT NULL::character(3)[], p_eps_code character varying DEFAULT NULL::character varying, p_is_show_list boolean DEFAULT true, p_periodicityes integer[] DEFAULT NULL::integer[], p_account character varying DEFAULT NULL::character varying, p_is_main_transaction boolean DEFAULT false)
RETURNS TABLE(eps_id integer, eps_detail_id integer, eps_code character varying, service_type character varying, eps_name character varying, line_number integer, contract_model_label character varying, account_dt character varying, kind_sub_dt1 text, kind_sub_dt2 text, kind_sub_dt3 text, account_ct character varying, kind_sub_ct1 text, kind_sub_ct2 text, kind_sub_ct3 text, oper_content character varying, is_main_transaction boolean, trans_description character varying, operation_id integer, operation_date date, operation_sum numeric, operation_name character varying, strategy_id integer, classification_id smallint, individual_id integer, entity_id integer, citizenship_country_code text, contract_id integer, pension_schema_id integer, incoming_order_id integer, incoming_order_number character varying, contragent_name text, periodicity smallint, href_sub_dt1 text, href_sub_dt2 text, href_sub_dt3 text, href_sub_ct1 text, href_sub_ct2 text, href_sub_ct3 text, account_number character varying)
LANGUAGE plpgsql
AS $function$
-- ========================================================================
-- Получение списка Движение ЕПС
-- Получатель - данные для списка Движение ЕПС и для выгрузки ежедневных операций
-- Создание: 10.08.2026 dv.pakhomov
-- ========================================================================
BEGIN
IF p_service_types IS NULL
OR ARRAY_LENGTH( p_service_types, 1 ) = 0
THEN
-- по умолчанию берем все Виды деятельности
p_service_types =
( SELECT ARRAY_AGG( uat.activity_eng_type )
FROM ourpension.uv_activity_type uat
WHERE uat.client_type = 0
);
END IF;
IF p_periodicityes IS NULL
OR ARRAY_LENGTH( p_periodicityes, 1 ) = 0
THEN
-- по умолчанию берем все Периодичности
p_periodicityes = ARRAY[ 1, 2 ];
END IF;
RETURN QUERY
WITH cte_var AS MATERIALIZED
( -- простые входные переменные
SELECT
p_eps_code as eps_code,
-- параметры-даты приводим к timestamp: начальная дата это начало суток
COALESCE( p_beg_period, '1990-01-01' )::timestamp as beg_period,
-- параметры-даты приводим к timestamp: конечная дата это конец суток
( SELECT COALESCE( p_end_period, CURRENT_DATE )::timestamp
+ interval '1 day'
- interval '1 msec'
) as end_period,
p_is_show_list as is_show_list,
p_is_main_transaction as is_main_transaction,
p_account as "account"
),
cte_service_types AS MATERIALIZED
( -- входной массив видов деятельности как набор данных
SELECT t.service_type
FROM UNNEST(p_service_types) t(service_type)
)
,
cte_periodicityes AS MATERIALIZED
( -- входной массив периодичностей как набор данных
SELECT t.periodicity
FROM UNNEST(p_periodicityes ) t(periodicity)
)
,
cte_href_parts AS MATERIALIZED
( -- постоянные фрагменты ссылок по ссылочным типам субконто
SELECT *
FROM
( VALUES
( 'PARTNERS_AGR', '', '', '' ),
( 'PARTNERS_I', '<a target="_blank" href="view?viewCode=Client_list&id=', '">', '</a>' ),
( 'PARTNERS_E', '<a target="_blank" href="view?viewCode=NCIuIMAW&id=', '">', '</a>' ),
( 'CONTRACT', '<a target="_blank" href="view?viewCode=7HPZX8vF&id=', '" target="_blank">', '</a>' ),
( 'PAY_ORDER', '<a target="_blank" href="view?viewCode=NL0wI3vg&id=', '" target="_blank">', '</a>' )
) v ( type_sub, id_prefix, id_name_text, name_suffix )
)
,
cte_eps_transaction AS MATERIALIZED
( -- здесь данные ЕПС с витрины
SELECT
et.id as eps_transaction_id,
e.id as eps_id,
s.operation_id,
e.code,
s.incoming_order_id,
COALESCE( e.service_type, '' ) as service_type,
COALESCE( e.contractor_type, -1 ) as contractor_type,
et.contract_id_dt,
et.contract_id_ct,
et.individual_id_dt,
et.individual_id_ct,
et.entity_id_dt,
et.entity_id_ct,
et.pens_schema_dt,
et.pens_schema_ct,
et.citizenship_country_code_dt,
et.citizenship_country_code_ct,
COALESCE( et.operation_sum, 0 ) as operation_sum,
et.strategy_id,
et.oper_date
FROM back_office.eps_transaction et
CROSS JOIN cte_var cv
INNER JOIN ourpension.eps e
ON e.id = et.eps_id
INNER JOIN back_office.eps_transaction_source s
ON s.id = et.eps_transaction_source_id
INNER JOIN cte_service_types cst
ON cst.service_type = e.service_type
INNER JOIN cte_periodicityes cp
ON cp.periodicity = e.periodicity
WHERE cv.is_show_list
AND et.oper_date BETWEEN
cv.beg_period AND cv.end_period
AND
( cv.eps_code IS NULL
OR e.code = cv.eps_code
)
)
,
cte_individual AS MATERIALIZED
( -- все ФЛ запроса с атрибутами (ФИО и гражданство)
SELECT
si.individual_id,
RTRIM( i.last_name || ' ' || i.first_name || ' ' || COALESCE( i.middle_name, '' ) ) as individual_name,
i.citizenship_country_code
FROM
( SELECT sg.individual_id
FROM
( SELECT dt.individual_id_dt AS individual_id
FROM cte_eps_transaction dt
WHERE dt.individual_id_dt IS NOT NULL
UNION
SELECT ct.individual_id_ct AS individual_id
FROM cte_eps_transaction ct
WHERE ct.individual_id_ct IS NOT NULL
) sg
GROUP BY sg.individual_id
) si
INNER JOIN ourpension.individual i
ON i.id = si.individual_id
)
,
cte_entity AS MATERIALIZED
( -- все ЮЛ запроса с названиями
SELECT
se.entity_id,
e.short_name as entity_name
FROM
( SELECT sg.entity_id
FROM
( SELECT dt.entity_id_dt AS entity_id
FROM cte_eps_transaction dt
WHERE dt.entity_id_dt IS NOT NULL
UNION
SELECT ct.entity_id_ct AS entity_id
FROM cte_eps_transaction ct
WHERE ct.entity_id_ct IS NOT NULL
) sg
GROUP BY sg.entity_id
) se
INNER JOIN ourpension.entity e
ON e.id = se.entity_id
)
,
cte_transaction_text AS
( -- определение текста в ячейках субконто
SELECT DISTINCT
ct.eps_transaction_id,
ct.eps_id,
ed.id as eps_detail_id,
ct.operation_sum,
ct.code,
lks.kind_subconto,
lks.subconto_order,
lns.text_sub,
( SELECT CONCAT
( chp.id_prefix
|| CASE lts.type_subconto
WHEN 'PARTNERS_I' THEN lks.individual_id::text
WHEN 'PARTNERS_E' THEN lks.entity_id::text
WHEN 'CONTRACT' THEN lks.contract_id::text
WHEN 'PAY_ORDER' THEN ct.incoming_order_id::text
END
|| chp.id_name_text,
lns.text_sub,
chp.name_suffix
) as href_sub
FROM cte_href_parts chp
WHERE chp.type_sub = lts.type_subconto
) as href_sub,
lks.contract_id,
ct.operation_id,
ct.strategy_id,
ct.incoming_order_id,
lks.individual_id,
lks.entity_id,
lks.citizenship_country_code,
lks.pension_schema_id,
CASE WHEN ct.contractor_type = 1
THEN ia."name" -- Агрегированный
ELSE COALESCE( ci.individual_name, ce.entity_name ) -- Персонифицированный
END as contragent_name,
ct.oper_date
FROM cte_eps_transaction ct
CROSS JOIN cte_var cv
INNER JOIN ourpension.eps_detail ed
ON ed.eps_id = ct.eps_id
AND
( ( NOT cv.is_main_transaction )
OR ed.is_main_transaction
)
INNER JOIN LATERAL
( SELECT
s_d.kind_subconto,
s_d.subconto_order, -- дебетовые ячейки субконто с 1 по 3
ct.individual_id_dt as individual_id,
ct.entity_id_dt as entity_id,
ct.contract_id_dt as contract_id,
ct.pens_schema_dt as pension_schema_id,
ct.citizenship_country_code_dt as citizenship_country_code
FROM ourpension.buh_account_subconto s_d
WHERE s_d.code_account = ed.account_dt
UNION
SELECT
s_c.kind_subconto,
s_c.subconto_order + 3, -- сдвигаем ячейку в кредитную зону с 4 по 6
ct.individual_id_ct as individual_id,
ct.entity_id_ct as entity_id,
ct.contract_id_ct as contract_id,
ct.pens_schema_ct as pension_schema_id,
ct.citizenship_country_code_ct as citizenship_country_code
FROM ourpension.buh_account_subconto s_c
WHERE s_c.code_account = ed.account_ct
) lks
ON true
INNER JOIN LATERAL
( -- Типизация субконто для ссылок
SELECT
CASE WHEN lks.kind_subconto = 'PARTNERS' THEN -- Контрагент
CASE
WHEN ct.contractor_type = 1 THEN 'PARTNERS_AGR'
WHEN lks.individual_id IS NOT NULL THEN 'PARTNERS_I'
WHEN lks.entity_id IS NOT NULL THEN 'PARTNERS_E'
END
ELSE lks.kind_subconto
END as type_subconto
) lts
ON true
LEFT JOIN cte_individual ci
ON ci.individual_id = lks.individual_id
LEFT JOIN cte_entity ce
ON ce.entity_id = lks.entity_id
LEFT JOIN back_office.incoming_order io
ON io.id = ct.incoming_order_id
LEFT JOIN ourpension.contract c
ON c.id = lks.contract_id
LEFT JOIN ourpension.individual_aggregated ia
ON ia.citizenship_country_code = lks.citizenship_country_code
LEFT JOIN prod_line.pens_scheme ps
ON ps.id = lks.pension_schema_id
INNER JOIN LATERAL
( -- Текстовое название субконто
SELECT
COALESCE
( CASE lks.kind_subconto
WHEN 'PARTNERS' THEN -- Контрагент
CASE WHEN ct.contractor_type = 1
THEN ia."name" -- Агрегированный
ELSE COALESCE( ci.individual_name, ce.entity_name ) -- Персонифицированный
END
WHEN 'CONTRACT' THEN c."number" -- Договор
WHEN 'PAY_ORDER' THEN io."number" -- Платежное поручение
END,
''
) as text_sub
) lns
ON true
WHERE lks.kind_subconto IS NOT null
AND
( cv.account IS NULL
OR ed.account_dt = cv.account
OR ed.account_ct = cv.account
)
)
,
cte_transaction_subconto AS
( -- соединение субконто в строки по транзакции + номер строки (проводка ЕПС)
SELECT
ctt.eps_transaction_id,
ctt.operation_id,
MAX( ctt.operation_sum ) as operation_sum,
ctt.eps_detail_id,
-- по ячейкам раскладываем имена субконто
MAX
( CASE WHEN ctt.subconto_order = 1
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_dt1,
MAX
( CASE WHEN ctt.subconto_order = 2
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_dt2,
MAX
( CASE WHEN ctt.subconto_order = 3
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_dt3,
MAX
( CASE WHEN ctt.subconto_order = 4
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_ct1,
MAX
( CASE WHEN ctt.subconto_order = 5
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_ct2,
MAX
( CASE WHEN ctt.subconto_order = 6
THEN ctt.text_sub
ELSE ''
END
) as kind_sub_ct3,
-- по ячейкам раскладываем ссылки субконто
MAX
( CASE WHEN ctt.subconto_order = 1
THEN ctt.href_sub
ELSE ''
END
) as href_sub_dt1,
MAX
( CASE WHEN ctt.subconto_order = 2
THEN ctt.href_sub
ELSE ''
END
) as href_sub_dt2,
MAX
( CASE WHEN ctt.subconto_order = 3
THEN ctt.href_sub
ELSE ''
END
) as href_sub_dt3,
MAX
( CASE WHEN ctt.subconto_order = 4
THEN ctt.href_sub
ELSE ''
END
) as href_sub_ct1,
MAX
( CASE WHEN ctt.subconto_order = 5
THEN ctt.href_sub
ELSE ''
END
) as href_sub_ct2,
MAX
( CASE WHEN ctt.subconto_order = 6
THEN ctt.href_sub
ELSE ''
END
) as href_sub_ct3,
MAX(ctt.strategy_id) as strategy_id,
ctt.incoming_order_id,
ctt.oper_date,
MAX( ctt.contragent_name ) as contragent_name,
MAX( ctt.individual_id ) as individual_id,
MAX( ctt.entity_id ) as entity_id,
MAX( ctt.citizenship_country_code ) as citizenship_country_code,
MAX( ctt.contract_id ) as contract_id,
MAX( ctt.pension_schema_id ) as pension_schema_id
FROM cte_transaction_text ctt
GROUP BY
ctt.eps_transaction_id,
ctt.eps_detail_id,
ctt.operation_id,
ctt.incoming_order_id,
ctt.oper_date
ORDER BY
ctt.eps_detail_id,
ctt.eps_transaction_id
)
,
cte_oper_sum AS
( -- соединение транзакций по операциям + номер строки (проводка ЕПС)
SELECT
cts.operation_id,
cts.eps_detail_id,
SUM( cts.operation_sum ) as operation_sum,
ed.account_dt,
cts.kind_sub_dt1,
cts.kind_sub_dt2,
cts.kind_sub_dt3,
ed.account_ct,
cts.kind_sub_ct1,
cts.kind_sub_ct2,
cts.kind_sub_ct3,
cts.strategy_id,
cts.incoming_order_id,
cts.oper_date,
MAX( cts.href_sub_dt1 ) as href_sub_dt1,
MAX( cts.href_sub_dt2 ) as href_sub_dt2,
MAX( cts.href_sub_dt3 ) as href_sub_dt3,
MAX( cts.href_sub_ct1 ) as href_sub_ct1,
MAX( cts.href_sub_ct2 ) as href_sub_ct2,
MAX( cts.href_sub_ct3 ) as href_sub_ct3,
MAX( cts.individual_id ) as individual_id,
MAX( cts.entity_id ) as entity_id,
MAX( cts.citizenship_country_code ) as citizenship_country_code,
MAX( cts.contract_id ) as contract_id,
MAX( cts.pension_schema_id ) as pension_schema_id,
MAX( cts.contragent_name ) as contragent_name
FROM cte_transaction_subconto cts
INNER JOIN ourpension.eps_detail ed
ON ed.id = cts.eps_detail_id
GROUP BY
cts.operation_id,
cts.incoming_order_id,
cts.eps_detail_id,
ed.account_dt,
cts.kind_sub_dt1,
cts.kind_sub_dt2,
cts.kind_sub_dt3,
ed.account_ct,
cts.kind_sub_ct1,
cts.kind_sub_ct2,
cts.kind_sub_ct3,
cts.strategy_id,
cts.oper_date
)
SELECT
e.id as eps_id,
c_os.eps_detail_id,
e.code as eps_code,
e.service_type,
e.name as eps_name,
ed.line_number,
e.contract_model_label,
ed.account_dt,
c_os.kind_sub_dt1,
c_os.kind_sub_dt2,
c_os.kind_sub_dt3,
ed.account_ct,
c_os.kind_sub_ct1,
c_os.kind_sub_ct2,
c_os.kind_sub_ct3,
ed.oper_content,
ed.is_main_transaction,
ed.trans_description,
c_os.operation_id,
c_os.oper_date::date as operation_date,
c_os.operation_sum,
CASE
WHEN c_os.operation_id IS NOT NULL THEN ot.name
WHEN c_os.incoming_order_id IS NOT NULL THEN CONCAT( 'ВХ ПП ', bs."number", ' от ', TO_CHAR( bs."date",'dd.MM.yyyy' ) )
END as operation_name,
c_os.strategy_id,
e.classification as classification_id,
c_os.individual_id,
c_os.entity_id,
c_os.citizenship_country_code,
c_os.contract_id,
c_os.pension_schema_id,
c_os.incoming_order_id,
bs."number" as incoming_order_number,
c_os.contragent_name,
e.periodicity,
c_os.href_sub_dt1,
c_os.href_sub_dt2,
c_os.href_sub_dt3,
c_os.href_sub_ct1,
c_os.href_sub_ct2,
c_os.href_sub_ct3,
bs.payer_account_number as account_number
FROM cte_oper_sum c_os
INNER JOIN ourpension.eps_detail ed
ON ed.id = c_os.eps_detail_id
INNER JOIN ourpension.eps e
ON e.id = ed.eps_id
LEFT JOIN back_office.operation o
ON o.id = c_os.operation_id
LEFT JOIN back_office.operation_type ot
ON ot.id = o.operation_type_id
LEFT JOIN "data".bank_statement_load_data bs
ON bs.payorderid = c_os.incoming_order_id
;
END;
$function$