declare
n integer:=1;
i integer:=1;
vdeal integer:=1;
vcnt integer;
vdt date;
--gdt date;
--gdeal integer;
--gamount integer;
vgt RDM_CTL.TMP_TOK_20190606_inst%rowtype;
cursor get_inst is
       SELECT *
       FROM RDM_CTL.TMP_TOK_20190606_inst
       WHERE amount > 0
       order by deal, dt;
begin
n:=n-1;
i:=0;
open get_inst;
DBMS_OUTPUT.ENABLE;
      loop
         fetch get_inst into vgt;
         if get_inst%notfound then exit;
         end if;
         exit when get_inst%notfound;
               if n=0 then vdeal := vgt.deal;
               end if;
               i:=i+1;
               if vdeal != vgt.deal
                  then vdeal := vgt.deal;
                       n := 0;
                       vdt := null;
               end if;
               SELECT
                     distinct
                     nvl(t.amount,0) + nvl(sum(case when d.dt>=t.inst and d.dt<t.next_inst then d.amount end),0)
                     + n, t.cnt into n, vcnt
               FROM (SELECT row_number()over(partition by a.deal order by a.dt) instalment
                           ,nvl(lag(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.1000','dd.mm.yyyy')) prev_inst
                           ,a.dt inst
                           ,nvl(lead(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.4000','dd.mm.yyyy')) next_inst
                           ,deal
                           ,amount
                           ,count(deal)over(partition by deal) cnt
                     FROM RDM_CTL.TMP_TOK_20190606_inst a
                     WHERE a.amount > 0
                    ) t
                          left join RDM_CTL.TMP_TOK_20190606_inst d
                               on t.deal = d.deal
                              and d.amount < 0
                     WHERE t.deal = vgt.deal
                      and inst = vgt.dt
                     group by t.deal, t.inst,t.amount, t.cnt;
               if n>0 and vdt is null then vdt := vgt.dt;
               end if;
               if vcnt = i then
                  i:=0;
                  if n>0 then
                     DBMS_OUTPUT.PUT_LINE(vdeal||'|'||n||'|'||vdt||'|'||vcnt);
                  end if;
               end if;
      end loop;
close get_inst;
 
end;

create table RDM_CTL.TMP_TOK_20190606_inst as
with pdcl as
(select to_date('20091212','yyyymmdd') dt, 111111 deal, 12000 amount from dual
union all
select to_date('20091225','yyyymmdd') dt, 111111 deal, 5000 amount from dual
union all
select to_date('20101201','yyyymmdd') dt, 111111 deal, -10100 amount from dual
union all
select to_date('20091212','yyyymmdd') dt, 122222 deal, 10000 amount from dual
union all
select to_date('20091229','yyyymmdd') dt, 122222 deal, -10000 amount from dual
union all
select to_date('20091120','yyyymmdd') dt, 222221 deal, 25000 amount from dual
union all
select to_date('20091220','yyyymmdd') dt, 222221 deal, 20000 amount from dual
union all
select to_date('20091231','yyyymmdd') dt, 222221 deal, -10000 amount from dual
union all
select to_date('20091127','yyyymmdd') dt, 222221 deal, -30000 amount from dual
union all
select to_date('20100120','yyyymmdd') dt, 222221 deal, 10000 amount from dual)
SELECT *
FROM pdcl
;

with pdcl as
(select to_date('20091212','yyyymmdd') dt, 111111 deal, 12000 amount from dual
union all
select to_date('20091225','yyyymmdd') dt, 111111 deal, 5000 amount from dual
union all
select to_date('20101201','yyyymmdd') dt, 111111 deal, -10100 amount from dual
union all
select to_date('20091212','yyyymmdd') dt, 122222 deal, 10000 amount from dual
union all
select to_date('20091229','yyyymmdd') dt, 122222 deal, -10000 amount from dual
union all
select to_date('20091120','yyyymmdd') dt, 222221 deal, 25000 amount from dual
union all
select to_date('20091220','yyyymmdd') dt, 222221 deal, 20000 amount from dual
union all
select to_date('20091231','yyyymmdd') dt, 222221 deal, -10000 amount from dual
union all
select to_date('20091127','yyyymmdd') dt, 222221 deal, -30000 amount from dual)
,
t as (
select nvl(LEAD(A.DT)OVER(partition by a.deal ORDER BY a.DT),to_date('01.01.4000','dd.mm.yyyy')) next_dt
      ,a.dt, a.deal, a.amount
      ,nvl(SUM(CASE WHEN PR.AMOUNT > 0 THEN PR.AMOUNT END),0) PREV_INST
      ,nvl(SUM(CASE WHEN PR.AMOUNT < 0 THEN PR.AMOUNT END),0) PREV_PAY
      ,nvl(SUM(CASE WHEN PR.AMOUNT > 0 THEN PR.AMOUNT END),0) + nvl(SUM(CASE WHEN PR.AMOUNT < 0 THEN PR.AMOUNT END),0) ostatok
from pdcl a
     LEFT JOIN pdcl PR
          ON PR.dt< A.DT
         AND PR.DEAL=A.DEAL    
WHERE a.amount > 0
GROUP BY a.dt, a.deal, a.amount
order by A.deal, A.dt
),
pd as (
SELECT t.dt
      ,t.next_dt
      ,t.deal
      --,t.amount
      ,case when ostatok < 0 then t.amount+ostatok else t.amount end amount
      --,PREV_INST
      --,PREV_PAY
      ,nvl(SUM(CASE WHEN NT.AMOUNT < 0 and NT.dt between t.dt and t.next_dt-1 THEN NT.AMOUNT END),0) cur_PAY
      ,nvl(SUM(CASE WHEN NT.AMOUNT > 0 THEN NT.AMOUNT END),0) next_INST
      ,nvl(SUM(CASE WHEN NT.AMOUNT < 0 and NT.dt >= t.next_dt THEN NT.AMOUNT END),0) next_PAY
      --,ostatok
      --,case when ostatok < 0 then  t.amount+ostatok end
FROM t
     LEFT JOIN pdcl NT
          ON NT.dt> t.DT
         AND NT.DEAL=t.DEAL
group by t.dt
      ,t.next_dt
      ,t.deal
      ,t.amount
      ,PREV_INST
      ,PREV_PAY
      ,ostatok)
      
SELECT --DT, NEXT_DT
        DEAL
        --, AMOUNT, CUR_PAY
--      ,AMOUNT + CUR_PAY--, pd.*
--      ,next_INST + next_PAY
--      ,next_INST
--      ,next_PAY
      ,AMOUNT + CUR_PAY+next_INST + next_PAY
      ,max(dt)keep(dense_rank first order by dt)over(partition by deal) pd_dt
FROM pd
WHERE AMOUNT + CUR_PAY > 0
order by DEAL, dt;