/*select * from GK_REESTR_COMMS where ID in (select COM_ID from (*/
begin
  delete from GK_NALOG_CB_REALIZ;
  delete from GK_NALOG_CB_REALIZ_TABLE;
  delete from GK_NALOG_CB_REALIZ1;
  delete from GK_NALOG_CB_REALIZ_TABLE1;

  dbo.S_GK_NALOG_CB_REALIZ_R(TO_DATE('01.01.2020','DD.MM.YYYY'), TO_DATE('31.03.2025','DD.MM.YYYY'), '0124', null, null, null);
  dbo.S_GK_NALOG_CB_REALIZ_R1(TO_DATE('01.01.2020','DD.MM.YYYY'), TO_DATE('31.03.2025','DD.MM.YYYY'), '0124', null, null, null);
  
  for q in (
    select sum(T1.CM) T1CM, sum(T.CM) TCM,abs (sum(T1.CM) - sum(T.CM)),N.DOCOID_REALIZ, N.DEAL_ID, N.ID,(select PRNM from GK_CDKDSH where ID = N.CDKDSH_ID) PRNM
    , max(C.ID) COM_ID, sum(C.COMM_SUM) CSUM, count(C.ID) COM_CNT
    , sum(C.COMM_SUM) - abs(sum(T.CM)) + abs(sum(T1.CM)) ITOG_CSUM
    , replace('update GK_REESTR_COMMS set COMM_SUM_NO_NDS = COMM_SUM + 0'||replace(to_char(abs(sum(T1.CM)) - abs(sum(T.CM))),',','.')||' where ID = '||to_char(max(C.ID))||';','+ 0-.','- 0.') BEGINN
    , abs(sum(T1.CM)) - abs(sum(T.CM)) COMM_KORREKT
    , max(C.ID) COMM_ID
      from GK_NALOG_CB_REALIZ N
      join GK_NALOG_CB_REALIZ_TABLE T on T.VNESHID = N.ID
      join GK_NALOG_CB_REALIZ1 N1 on N1.DEAL_ID = N.DEAL_ID
      join GK_NALOG_CB_REALIZ_TABLE1 T1 on T1.VNESHID = N1.ID
      join GK_REESTR_COMMS C on C.VNESHID = T.REESTR_ID and C.COMM_TYPE > 20 and C.COMM_TYPE = 24
     where T1.DEAL_ID = T.DEAL_ID
       and T1.REESTR_ID = T.REESTR_ID
       and (select PRNM from GK_CDKDSH where ID = N.CDKDSH_ID) = 0
      group by N.DOCOID_REALIZ , N.DEAL_ID, N.ID, N.CDKDSH_ID
      having sum(T1.CM) <> sum(T.CM)
     order by 3 desc
  )
  loop
    update GK_REESTR_COMMS set COMM_SUM_NO_NDS = COMM_SUM + q.COMM_KORREKT where ID = q.COMM_ID;
  end loop;

  for q in (
    select *
      from (select
             nvl(R.PARENT_ID,R.ID) PARENT_ID
            ,case when RC.COMM_TYPE > 20 then RC.COMM_TYPE-20 else RC.COMM_TYPE end as COMM_TYPE
            ,R.CDKDSH_ID
            ,max(R.ID) as MAX_ID
            ,sum(case when RC.COMM_TYPE < 20 then RC.COMM_SUM_NO_NDS else -RC.COMM_SUM_NO_NDS end) as COMM_OST
            
              from GK_REESTR R
              join GK_REESTR_COMMS RC on RC.VNESHID = R.ID and (RC.COMM_TYPE < 20 and R.PARENT_ID is null or RC.COMM_TYPE > 20 and R.PARENT_ID is not null)
             where 1=1
               and R.SUM_BS <> 0
               and year(R.DTCLOSE) = 2025
             group by nvl(R.PARENT_ID,R.ID) ,case when RC.COMM_TYPE > 20 then RC.COMM_TYPE-20 else RC.COMM_TYPE end ,R.CDKDSH_ID
            ) A
     where A.COMM_OST <> 0
  )
  loop
    update GK_REESTR_COMMS set COMM_SUM_NO_NDS = COMM_SUM_NO_NDS + q.COMM_OST where VNESHID = q.MAX_ID and COMM_TYPE = q.COMM_TYPE+20;
  end loop;

end;
