declare
  v_i number;
  v_sum decimal(22,2);
begin

  v_i := 0;
  for q in (
  select 
  R.ID,R.TIMEDEAL,R.PARENT_ID,R.REPOPARENT_ID,R.TOPPARENT_ID,R.DOCOID,R.DOCID,R.AMSHALL,R.DEALTYPE
  ,case when R.PARENT_ID is null then 4 else 24 end as COMM_TYPE
  ,nvl(R.PARENT_ID,R.REPOPARENT_ID) PREV_ID
  ,(select AMSHALL from GK_REESTR where ID = nvl(R.PARENT_ID,R.REPOPARENT_ID)) AMSH_0
  ,(select AMSHALL from GK_REESTR where ID = nvl(R.PARENT_ID,R.REPOPARENT_ID))
   - nvl((select sum(AMSHALL) from GK_REESTR where PARENT_ID = R.PARENT_ID and R.PARENT_ID is not null and ID < R.ID),0)
   - nvl((select sum(AMSHALL) from GK_REESTR where REPOPARENT_ID = R.REPOPARENT_ID and R.REPOPARENT_ID is not null and ID < R.ID),0) AMSH_BEFORE
  ,(select AMSHALL from GK_REESTR where ID = nvl(R.PARENT_ID,R.REPOPARENT_ID))
   - nvl((select sum(AMSHALL) from GK_REESTR where PARENT_ID = R.PARENT_ID and R.PARENT_ID is not null and ID <= R.ID),0)
   - nvl((select sum(AMSHALL) from GK_REESTR where REPOPARENT_ID = R.REPOPARENT_ID and R.REPOPARENT_ID is not null and ID <= R.ID),0) AMSH_AFTER
  ,(select ID from GK_REESTR_COMMS where VNESHID = R.ID and COMM_TYPE = case when R.PARENT_ID is null then 4 else 24 end) COMM_ID

  ,(select count(ID) from GK_REESTR_COMMS where VNESHID = R.ID and COMM_TYPE = case when R.PARENT_ID is null then 4 else 24 end) COMM_CNT
  ,dbo.S_GKO_FNOMINAL(R.CDKDSH_ID,(select TIMEDEAL from GK_REESTR where ID = nvl(R.PARENT_ID,R.REPOPARENT_ID))) NOM_PREV
  ,dbo.S_GKO_FNOMINAL(R.CDKDSH_ID,R.TIMEDEAL) NOM
  ,(select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = R.ID and COMM_TYPE = case when R.PARENT_ID is null then 4 else 24 end) COMM_SUM
  ,(select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = nvl(R.PARENT_ID,R.REPOPARENT_ID) and COMM_TYPE = case when R.PARENT_ID is null then 24 else 4 end) COMM_SUM_PREV
    from GK_REESTR R
    join GK_REESTR T on T.ID = R.TOPPARENT_ID
   where R.AMSHALL > 0
/*     and nvl(R.TOPPARENT_ID,R.ID) <> 4738129406*/
     and exists (select null from GK_REESTR_COMMS where VNESHID = R.TOPPARENT_ID and COMM_SUM > 0 and COMM_TYPE = 4)
     and not exists (select null from GK_REESTR_COMMS where VNESHID = R.ID and COMM_TYPE = case when R.PARENT_ID is null then 4 else 24 end and COMM_SUM > 0)
     and year(nvl(R.DTCLOSE,dbo.D4000())) > 2020
   order by R.TIMEDEAL,R.ID   
   )
  loop
    begin
      select round(COMM_SUM * q.AMSH_BEFORE / q.AMSH_0, 2) - round(COMM_SUM * q.AMSH_AFTER / q.AMSH_0, 2)
        into v_sum
        from GK_REESTR_COMMS
       where VNESHID = q.PREV_ID
         and COMM_TYPE = case q.COMM_TYPE when 4 then 24 else 4 end;
    exception
      when NO_DATA_FOUND then
        v_sum := 0;
    end;

    if q.COMM_ID is not null then
      update GK_REESTR_COMMS set COMM_SUM = v_sum, COMM_SUM_RUB = v_sum where ID = q.COMM_ID;
    else 
      v_i := v_i+1;
      insert into GK_REESTR_COMMS(ID, VNESHID, COMM_TYPE, COMM_SUM, COMM_VALUT, COMM_SUM_RUB, COMM_DATE)
      values (v_i, q.ID, q.COMM_TYPE, v_sum, 34, v_sum, to_date('07.06.2017','dd.mm.yyyy'));
    end if;
  end loop;
end;

