Просмотр документа банкротство судебное.txt

create or replace procedure bancrupt_analys is


begin


execute immediate 'truncate table kd_user.bankrupt_4';
insert into kd_user.bankrupt_4

select ms.status,
       t.publishdate,
       t.bankruptid,
       t.keyid,
       t.osvobodit_ot_ispolneniya,
       t.MESSAGEGUID guid,
       t.idclient
from kd_stg.JUDICIAL_BANKRUPT_MESSAGES t
left join kd_user.bancrupt_messages_status ms
  on ms.decisiontype_name = t.decisiontype_name and (ms.discharge_isdischarged = case when t.discharge_isdischarged = 0 then 0 else 999 end or status = 'Возбуждение');
commit;


--таблица 5
execute immediate 'truncate table kd_user.bankrupt_5';
insert into kd_user.bankrupt_5
select t.*, tt.bankruptactivity
from kd_user.bankrupt_4 t
left join credoc.wh_bankrupt_operation@dwh tt on tt.idclient = t.idclient and tt.bankrupt = case when t.status = 'Возбуждение' then 320001 else 320006 end;

commit;

execute immediate 'truncate table kd_user.bankrupt_6_1';
insert into kd_user.bankrupt_6_1
select t.status,
       t.publishdate,
       t.bankruptid,
       t.keyid,
       t.osvobodit_ot_ispolneniya,
       t.guid,
       t.idclient,
       t.bankruptactivity,
       max(case when c.idclient is not null then 1 else 0 end) ac,
       max(case when c.iduser <> 285601 and c.idclient is not null then 1 else 0 end) ac2
from kd_user.bankrupt_5 t
left join kd_user.rsdebt_debtcomment_mv c on c.idclient = t.idclient and c.idstandartcomment = 244556671  -- Есть СК в ПО Фронт
                                -- "Банкротство. Вынесено Решение АС о признании банкротом/Введена процедура реструктуризации долгов

group by t.status,
         t.publishdate,
         t.bankruptid,
         t.keyid,
         t.osvobodit_ot_ispolneniya,
         t.bankruptactivity,
         t.guid,
         t.idclient;

--таблица 6
execute immediate 'truncate table kd_user.bankrupt_6';
insert into kd_user.bankrupt_6
select --+full(c) parallel(5)
       t.status,
       t.publishdate,
       t.bankruptid,
       t.keyid,
       t.osvobodit_ot_ispolneniya,
       t.guid,
       t.idclient,
       t.bankruptactivity,
       t.ac,
       t.ac2,
       max(case when tt.f_collcommenttag = 1497 then 1 else 0 end) end_comment,

       max(case when tt.f_collcommenttag = 1513 then 1 else 0 end) hodataystvo_il, --Кол-во клиентов, по которым обратились с Арбитражный суд за выдачей исполнительного листа
       max(case when c.f_collcomment = 3358 and c.isfileexists = 1 then 1 else 0 end) vidacha_il --Кол-во договоров, по которым обратились с Арбитражный суд за выдачей исполнительного листа

from kd_user.bankrupt_6_1 t
left join kd_visor.fnt_f_object o on o.ks_idclient = t.idclient

left join kd_visor.fnt_f_dog d on d.f_object = o.f_object

left join kd_visor.fnt_f_collclientcomment c on c.f_dog = d.f_dog and c.f_collcomment in (2267, 2285, 3358)

left join kd_visor.fnt_f_colltagcommment tt on tt.f_collclientcomment = c.f_collclientcomment and tt.f_collcommenttag in (1497, 1513)

group by t.status,
         t.publishdate,
         t.bankruptid,
         t.keyid,
         t.osvobodit_ot_ispolneniya,
         t.bankruptactivity,
         t.ac,
         t.ac2,
         t.guid,
         t.idclient;

--добавляем договоры
execute immediate 'truncate table kd_user.bankrupt_7';
insert into kd_user.bankrupt_7
select t.*,
       a.n_dog,
       a.n_schet,
       a.idblank,
       a.currentstatus


from kd_user.bankrupt_6 t
left join kd_user.agr_dogovors a on a.idclient = t.idclient
 and (nvl(a.datexit, date'2030-01-01') = case when t.status = 'Возбуждение' then date'2030-01-01' else nvl(a.datexit, date'2030-01-01') end
       or a.datexit >trunc(t.publishdate) );

commit;

--добавляем од, метки аспи, списания, текущий статус, статус на момент сообщения
delete from kd_user.bankrupt_8 ;--where trunc(publishdate, 'mm') = trunc(sysdate, 'mm'); --удаляем текущий месяц
insert into kd_user.bankrupt_8
with debt as (
               select --+matelialize
                  --+parallel(10)
                      t.keyid,
                      t.idblank,
                      f.vl_debt,
                      f.portf_subtype,
                      row_number() over(partition by t.keyid, t.idblank order by f.dt desc) rw
               from bankrupt_7 t
               join kd_user.fd_prtf f on f.idblank = t.idblank and f.dt < trunc(t.publishdate)
               where t.status = 'Возбуждение'
             )

select t.*,
       nvl(ar.arreststate, 0) arreststate,
       case when s.idblank is not null then 1 else 0 end bankrupt_status,
       d.vl_debt,
       case when d.portf_subtype = 'Bankrupt' then 1 else 0 end aspi_bankrupt,
       case when u.n_dog is not null then 1 else 0 end writeoff,
       case when s1.nover <> 42301 then 1 else 0 end not_bankrupt_now


from kd_user.bankrupt_7 t
left join (select b.idblank,
                  m.changedate,
                  b.publishdate,
                  row_number() over(partition by m.idblank order by changedate desc) rw,
                  arreststate
          from kd_stg.moneyarrest m
          join kd_user.bankrupt_7 b on b.idblank = m.idblank and changedate <= b.publishdate
          )
            ar
               on ar.idblank = t.idblank and ar.arreststate=1 and ar.rw=1--берем последнюю запись до начала месяца в таблице в договорами

left join kd_user.nover_history s
  on s.idblank=t.idblank and s.nover=42301 --статус банкрот
and t.publishdate between s.dfnover and s.dtnover

left join kd_user.nover_history s1 on s1.idblank=t.idblank and s1.dtnover=date'4000-12-31' --текущий статус

left join debt d on d.keyid = t.keyid and d.idblank = t.idblank and d.rw = 1

left join priv_kd_report.ucrb_write_off@dwh u on u.n_dog=t.n_dog and u.id_client is not null; --списания
--where trunc(publishdate, 'mm') = trunc(sysdate, 'mm')
commit;


--итоговая таблица
delete from kd_user.bankrupt_9; --where trunc(t.publishdate, 'mm') = trunc(sysdate, 'mm'); --удаляем текущий месяц
insert into kd_user.bankrupt_9
select trunc(t.publishdate, 'mm') publishdate,
       t.status,
       max(t1.bankrupt_messages) bankrupts_messages,
       max(t2.identify) bankrupts_identified,
       max(t2.hand_identified) hand_identified,
       max(t2.clients) clients,
       max(t2.bankruptactivity_clients) bankruptactivity_clients,
       max(t2.ac_clients) ac_clients,
       count(distinct t.n_schet) accounts,
       sum(t.arreststate) arrests,
       count(case when nvl(vl_debt,0) > 0 then t.idblank end) dogs_dolg,
       count(case when nvl(vl_debt,0) > 0 and t.bankrupt_status = 1 then t.idblank end) dogs_dolg_bankrupt,
       count(case when nvl(vl_debt,0) > 0 and t.bankrupt_status = 1 and t.aspi_bankrupt = 1 then t.idblank end) dogs_dolg_bankrupt_aspi,

       count(case when t.currentstatus = 46 then t.n_schet end) arrest_snyatie, --Снятие ареста по Банкротству (!) в исторической таблице с арестами
       count(case when t.writeoff = 1 then t.n_schet end) writeoff_dogs, --Из этих договоров количество тех, по которым списана задолженность
       count(case when t.not_bankrupt_now = 1 then t.n_schet end) bankrupt_changed, --из них сменился статус "Банкрот"

       count(distinct case when t.hodataystvo_il = 1 then t.idclient end) hodataystvo_il, --Кол-во клиентов, по которым обратились с Арбитражный суд за выдачей исполнительного листа
       sum(t.vidacha_il) vidacha_il --Кол-во договоров, по которым обратились с Арбитражный суд за выдачей исполнительного листа

from kd_user.bankrupt_8 t

left join (
            select trunc(t.publishdate, 'mm') publishdate,
                   t.status,
                   count(distinct t.bankruptid) bankrupt_messages
            from kd_user.bankrupt_5 t
            group by trunc(t.publishdate, 'mm'), t.status
          )
            t1 on t1.publishdate = trunc(t.publishdate, 'mm') and t1.status = t.status

left join (select trunc(t.publishdate, 'mm') publishdate,
                  t.status,
                  count(distinct t.guid) identify,
                  count(distinct case when t.ac2 = 1 then t.guid end) hand_identified,
                  count(distinct t.idclient) clients,
                  count(distinct case when lower(t.status) like '%завершена%' and t.bankruptactivity = 0 then t.idclient
                        when lower(t.status) not like '%завершена%' and t.bankruptactivity = 1 then t.idclient end) bankruptactivity_clients,
                  count(distinct case when t.ac = 1 then t.idclient end) ac_clients
           from kd_user.bankrupt_6 t
           where t.idclient is not null
           group by trunc(t.publishdate, 'mm'),
                    t.status
           ) t2 on t2.publishdate = trunc(t.publishdate, 'mm') and t2.status = t.status
--where trunc(t.publishdate, 'mm') = trunc(sysdate, 'mm')
group by
       t.status,
       trunc(t.publishdate, 'mm');
commit;



-------------------------------
--детализация
-------------------------------
execute immediate 'truncate table kd_user.detal_bankr';
insert into kd_user.detal_bankr


select ms.status,b.PUBLISHDATE,b.BANKRUPTID,bs.IDCLIENT,m.DECISIONTYPE_NAME,b.number_message MESSAGEID,bs.IDENTIFIKATOR_KARTOCHKI

from kd_stg.JUDICIAL_BANKRUPT_MESSAGES b --сообщения о банкротстве
join bankrupt.message_court_decision@dwh  m on m.keyid=b.keyid --для decisiontype_name
join BANKRUPT.BANKRUPT_DEBTORPERSON@dwh db on db.bankruptid = b.bankruptid and db.category in ('Физическое лицо','Индивидуальный предприниматель') --для идентификатора карточки
left join ORAWH.EFRSB_BANKRUPTCYSEARCH@dwh bs on bs.identifikator_kartochki = db.guid --для идентификатора карточки
join kd_user.bancrupt_messages_status ms
  on ms.decisiontype_name = b.decisiontype_name and ms.discharge_isdischarged = case when b.discharge_isdischarged = 0 then 0 else 999 end --справочник по статусам
where trunc(b.publishdate)between sysdate-30 and sysdate;

commit;
kd_user.PKG$REPORTER_new.send_report('bankrupt_seg_analys');
end;