https://pastein.ru/t/Z08
Загрузка данных
INSERT INTO UBRR_DATA.UBRR_SFM_INFO_OPERS_FL1MLN_GTT SELECT
CCUSNUMNAL,
CACCACC,
to_char(doc_date,'dd.mm.yyyy') doc_date,
DOC_NUM,
to_char(doc_date,'dd.mm.yyyy') doc_date2,
PAYER_BIK,
PAYER_BANK,
PAYER_CORRACC,
RESIP_BIK,
RESIP_BANK,
RESIP_CORRACC,
SUMM_DB,
SUMM_CR,
SUMM_DB_R,
SUMM_CR_R,
PAYER_INN,
PAYER_NAME,
PAYER_ACC,
RESIP_INN,
RESIP_NAME,
RESIP_ACC,
PURP,
CASE WHEN PAYER_ACC = CACCACC
THEN CASE WHEN RESIP_BIK = '046577795'
THEN regexp_substr(PURP,'\d{20}') ELSE RESIP_ACC END
ELSE NULL END AS acc_fl,
REGEXP_REPLACE(
REGEXP_SUBSTR(LOWER(REGEXP_REPLACE(REPLACE(PURP,' '), '[0-9]{20}')), '(карт[ауы][^0-9]*)([0-9* ]{12,19})', 1, 1, 'i', 2),
'[^0-9*]',
''
) AS card_num
FROM (WITH tab_acc as (SELECT CACCACC,
cacccur,
idsmr,
to_date('01.05.2026', 'dd.mm.rrrr') dat1,
to_date('15.05.2026', 'dd.mm.rrrr') dat2,
(SELECT ccusnumnal FROM xxi.cus WHERE icusnum = iacccus) AS ccusnumnal
FROM xxi."acc"
WHERE iacccus IN (select clid
from (select clid,
summc as sum_value,
ctrnclient_inn,
(select sum(t.mtrnrsum) summ_deb
from xxi."acc" a, xxi."trn" t
where a.caccacc = t.ctrnaccd
and a.iacccus = clid
and a.IACCBS2 IN (40701, 40702, 40703, 40802)
and t.idsmr = a.idsmr
and t.ctrncur = a.cacccur
and t.ctrnclient_inn <> t.ctrninna
and t.idsmr = '1'
and dtrntrn_trunc between to_date('01.05.2026', 'dd.mm.rrrr') and to_date('15.05.2026', 'dd.mm.rrrr') ) * 0.2 summd20
from (select a.iacccus clid, t.ctrnclient_inn, sum(t.mtrnrsum) summc
from xxi."acc" a, xxi."trn" t
where a.caccacc = t.ctrnaccd
and a.idsmr = t.idsmr
and a.cacccur = t.ctrncur
and substr(t.ctrnaccd, 1, 5) in ('40702', '40703', '40701', '40802')
-- Счет получателя равен указанным маскам
and (
substr(t.ctrnacca, 1, 5) in ('40817','40803','40806','40809','40810','40812','40813',
'40814','40815','40816','40820','42301','42302','42303',
'42304','42305','42306','42307','47422','42601','42602',
'42603','42604','42605','42606','42607')
-- Или счет по Кт равен указанным маскам И в назначении платежа присутствуют маски указанных счетов
or
(
(substr(t.ctrnaccc, 1, 5) in ('30232', '30233') or substr(t.ctrnaccc, 1, 3) in ('301', '303', '111', '100'))
and
substr(regexp_substr(t.ctrnpurp,'\d{20}(\D|$)'), 1, 5) in ('40817','40803','40806','40809','40810','40812','40813',
'40814','40815','40816','40820','42301','42302','42303',
'42304','42305','42306','42307','47422','42601','42602',
'42603','42604','42605','42606','42607')
)
)
and (lower(t.ctrnpurp) not like '%з/п%' and lower(t.ctrnpurp) not like '%зар%плат%' and
lower(t.ctrnpurp) not like '%плат% труд%' and lower(t.ctrnpurp) not like '%отпуск%' and
lower(t.ctrnpurp) not like '%аванс%' and lower(t.ctrnpurp) not like '%алимент%' and
lower(t.ctrnpurp) not like '%увольн%' and lower(t.ctrnpurp) not like '% зп %' and
lower(t.ctrnpurp) not like '%больнич%' and lower(t.ctrnpurp) not like '%реестр%' and
lower(t.ctrnpurp) not like '%ген%согл%')
and (substr(t.ctrnacca, 1, 3) not in ('474') or lower(t.ctrnpurp) not like '%куп%ин%валют%')
and dtrntrn_trunc between to_date('01.05.2026', 'dd.mm.rrrr') and to_date('15.05.2026', 'dd.mm.rrrr')
and t.idsmr = '1'
group by a.iacccus, t.ctrnclient_inn
-- Совокупный объем операций по снятию наличных за период более > 500 тыс. руб.
having sum(mtrnrsum) > 1000000))
-- Совокупный объем больше 20% общего объема списаний
where sum_value > summd20
) AND IACCBS2 IN (40701, 40702, 40703, 40802) AND idsmr = '1')
SELECT /*+ orderd use_nl(tac_acc,t) use_nl(t,s)*/
tab_acc.caccacc
,tab_acc.ccusnumnal
,t.idsmr
,t.ctrnaccd
,t.ctrncur
,t.itrnnum
,t.itrnanum
,t.itrntype
,t.itrndocnum doc_num --"Номер документа"
,dtrntrn_trunc doc_date ---"Дата операции"
--- Плательщик
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNMFOA,s.CSMRMFO8)
ELSE nvl(t.CTRNMFOO,s.CSMRMFO8)
END,chr(10),' ') AS payer_bik --"Плательщик:БИК"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOA,s.CSMRMFO8))
ELSE (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOO,s.CSMRMFO8))
END,chr(10),' ') AS payer_bank --"Плательщик:Банк"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44)
THEN t.cTrnOwnA
ELSE t.CTRNCLIENT_NAME
END,chr(10),' ') AS payer_Name --"Плательщик:Наимен"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNINNA
ELSE t.CTRNCLIENT_INN
END,chr(10),' ') AS payer_INN --"Плательщик:ИНН"
/*,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNKPPA
ELSE t.CTRNCLIENT_KPP
END,chr(10),' ') AS payer_KPP --"Плательщик:" КПП */
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44)THEN t.CTRNACCA
ELSE NVL (t.cTrnClient_Acc, t.cTrnAccD)
END,chr(10),' ') AS payer_acc --"Плательщик:Счет"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44)THEN t.CTRNCORACCA
ELSE NVL (t.CTRNCORACCO, t.cTrnAccD)
END,chr(10),' ') AS payer_corracc --"Плательщик:КоррСчет"
----- Получатель
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNMFOO,s.CSMRMFO8)
ELSE nvl(cTrnMfoA,CSMRMFO8)
END,chr(10),' ') AS resip_bik --"Получатель:БИК"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOO,s.CSMRMFO8))
ELSE (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.cTrnMfoA,s.CSMRMFO8))
END,chr(10),' ') AS resip_bank --"Получатель:Банк"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.cTrnClient_Name
ELSE t.cTrnOwnA
END,chr(10),' ') AS resip_name --"Получатель:Наимен"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNCLIENT_INN
ELSE t.CTRNINNA
END,chr(10),' ') AS resip_inn --"Получатель:ИНН"
,/*replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNCLIENT_KPP
ELSE t.CTRNKPPA
END,chr(10),' ') AS resip_kpp --"Получатель:КПП"
,*/ replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN NVL (t.cTrnClient_Acc, t.cTrnAccC)
ELSE nvl(t.cTrnAccA, t.cTrnAccC)
END,chr(10),' ') AS resip_acc --"Получатель:Счет"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNCORACCO
ELSE NVL (t.CTRNCORACCA, t.cTrnAccC)
END,chr(10),' ') AS resip_corracc --"Получатель:КоррСчет"
,mtrnsum summ_db --"Сумма Деб"
,mtrnrsum summ_db_r -- "Сумма Деб Экв"
,cast( null as number ) summ_cr --"Сумма Кред"
,cast( null as number ) summ_cr_r --"Сумма Кред Экв"
,replace(ctrnpurp,chr(10),' ') purp --"Назначение Платежа"
FROM tab_acc
,xxi."trn" t
,xxi."smr" s
WHERE dtrntrn_trunc between dat1 AND dat2
AND ctrnaccd=tab_acc.caccacc
AND ctrncur=tab_acc.cacccur
AND t.idsmr=tab_acc.idsmr
AND s.idsmr=t.idsmr
union all
SELECT /*+ orderd use_nl(tac_acc,t) use_nl(t,s)*/
tab_acc.caccacc
,tab_acc.ccusnumnal
,t.idsmr
,t.ctrnaccc
,t.ctrncurc
,t.itrnnum
,t.itrnanum
,t.itrntype
,t.itrndocnum "Номер документа"
,dtrntrn_trunc "Дата операции"
--- Плательщик
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNMFOA,s.CSMRMFO8)
WHEN iTrnType=-6 THEN (SELECT nvl(z.CTRNMFOO,s.CSMRMFO8) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE nvl(t.CTRNMFOO,s.CSMRMFO8)
END,chr(10),' ') AS "Плательщик:БИК"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOA,s.CSMRMFO8))
WHEN iTrnType=-6 THEN (SELECT f1.cfogname FROM xxi."trn" z,xxi.fog f1 WHERE z.itrnnum=t.itrnnum AND itrnanum=0 AND f1.cfogmfo8 = nvl(z.CTRNMFOO,s.CSMRMFO8))
ELSE (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOO,s.CSMRMFO8))
END,chr(10),' ') AS "Плательщик:Банк"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.cTrnOwnA
WHEN iTrnType=-6 THEN (SELECT z.CTRNCLIENT_NAME FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.CTRNCLIENT_NAME
END,chr(10),' ') AS "Плательщик:Наимен"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNINNA
WHEN iTrnType=-6 THEN (SELECT z.CTRNCLIENT_INN FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.CTRNCLIENT_INN
END,chr(10),' ') AS "Плательщик:ИНН"
/*,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNKPPA
WHEN iTrnType=-6 THEN (SELECT z.CTRNCLIENT_KPP FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.CTRNCLIENT_KPP
END,chr(10),' ') AS "Плательщик:КПП" */
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNACCA
WHEN iTrnType=-6 THEN (SELECT NVL (z.cTrnClient_Acc, z.cTrnAccD) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE NVL (t.cTrnClient_Acc, t.cTrnAccD)
END,chr(10),' ') AS "Плательщик:Счет"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNCORACCA,t.cTrnAccD)
WHEN iTrnType=-6 THEN (SELECT NVL (z.CTRNCORACCO, z.cTrnAccD) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE NVL (t.CTRNCORACCO, t.cTrnAccD)
END,chr(10),' ') AS payer_corracc --"Плательщик:КоррСчет"
----- Получатель
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNMFOO,s.CSMRMFO8)
WHEN iTrnType=-6 THEN (SELECT nvl(z.cTrnMfoA,s.CSMRMFO8) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE nvl(t.cTrnMfoA,s.CSMRMFO8)
END,chr(10),' ') AS "Получатель:БИК"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.CTRNMFOO,s.CSMRMFO8))
WHEN iTrnType=-6 THEN (SELECT f1.cfogname FROM xxi."trn" z,xxi.fog f1 WHERE z.itrnnum=t.itrnnum AND itrnanum=0
AND f1.cfogmfo8 = nvl(z.cTrnMfoA,s.CSMRMFO8))
ELSE (SELECT f.cfogname FROM xxi.fog f WHERE f.cfogmfo8 = nvl(t.cTrnMfoA,s.CSMRMFO8))
END,chr(10),' ') AS "Получатель:Банк"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.cTrnClient_Name
WHEN iTrnType=-6 THEN (SELECT z.cTrnOwnA FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.cTrnOwnA
END,chr(10),' ') AS "Получатель:Наимен"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN t.CTRNCLIENT_INN
WHEN iTrnType=-6 THEN (SELECT z.CTRNINNA FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.CTRNINNA
END,chr(10),' ') AS "Получатель:ИНН"
/*,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44)
THEN t.CTRNCLIENT_KPP
WHEN iTrnType=-6
THEN (SELECT z.CTRNKPPA FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE t.CTRNKPPA
END,chr(10),' ') AS "Получатель:КПП" */
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN NVL (t.cTrnClient_Acc, t.cTrnAccC)
WHEN iTrnType=-6 THEN (SELECT nvl(z.cTrnAccA, z.cTrnAccC) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE nvl(t.cTrnAccA, t.cTrnAccC)
END,chr(10),' ') AS "Получатель:Счет"
,replace(CASE WHEN iTrnType IN (5,50,53,52,55,17,41,42,43,44) THEN nvl(t.CTRNCORACCO,t.cTrnAccC)
WHEN iTrnType=-6 THEN (SELECT NVL (z.CTRNCORACCA, z.cTrnAccC) FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE NVL (t.CTRNCORACCA, t.cTrnAccC)
END,chr(10),' ') AS recip_corracc --"Плательщик:КоррСчет"
,cast( null as number ) "Сумма Деб"
,cast( null as number ) "Сумма Деб Экв"
,mtrnsumc "Сумма Кред"
,mtrnrsum "Сумма Кред Экв"
,replace(CASE WHEN iTrnType=-6 THEN (SELECT z.ctrnpurp FROM xxi."trn" z WHERE z.itrnnum=t.itrnnum AND itrnanum=0)
ELSE ctrnpurp
END,chr(10),' ') AS "Назначение Платежа"
FROM tab_acc
,xxi."trn" t
,xxi."smr" s
WHERE dtrntrn_trunc between dat1 AND dat2
AND ctrnaccc=tab_acc.caccacc
AND ctrncurc=tab_acc.cacccur
AND t.idsmr=tab_acc.idsmr
AND s.idsmr=t.idsmr
) order by caccacc, doc_date;