create or replace procedure     S_MBK_RESTRUCT 
     
     
       
            
(mUSERID int ,mDOGID int ,mDD date, mDOCOID int, mDOCID int)                     
AS                       
           n int;       
           iDPODP date;       
           iEVTYPE int;       
           iEVTYPE2 int;     
           iEVTYPE_12 int;     
           iEVTYPE_75 int;     
           iEVTYPE_82 int;     
           iEVTYPE_VOZV_OSN int;       
                    
           iDEND_OLD date;       
           iDEND_NEW date;       
           iLSID_OLD int;       
           iLSID_NEW int;       
           iLSID int;       
           iPLAN_OLD int;       
           iPLAN_NEW int;      
           iPLAN_CHANGE int;       
           iPSTVALUE_OLD decimal (17,6);       
           iPSTVALUE_NEW decimal (17,6);       
           iL_PSTV_OLD int;       
           iL_PSTV_NEW int;       
           iUSLUGAID_OLD int;       
           iUSLUGAID_NEW int;       
           iLSTO int;     
           iLSTO_PROSR int;     
           iLSTO_PROSR_OST decimal (18,2);     
           mSM decimal (17,2);       
           iDGTYPE int;       
           iOPOID int;       
           iOPID int;       
           iTARIF int;  
           iTARIF_NEW int;       
           mSTART int;       
           t varchar2(100);       
           iEVTG int;       
           iPRD int;       
           iORG int;        
           iLSYS_TYPE int;       
           iFROM_LSID int;       
           iTO_LSID int;     
           iTO_LSID_NAME varchar2(100);     
           iLSID_P int;          
           mSave int;        
           txt varchar2(2000);         
           /*SSM*/     
           l_to_osn int;         
           to_osn1 decimal (18,2);        
           dog_oid int;         
           back_id int;       
           temp_back_id int;         
           ev_type int;            
           /*End SSM*/        
           /*GINW*/       
           nbsid_ls int;       
           nbsid_ls_new int;       
           iLOPEN_TO int;       
           mS_CLASS int;       
           nID int;       
           nDOCS int;       
           mEVID int;       
           mEV_CLASS int;       
           dgDBEG date;       
           iLSID_OLD2 int;         
                    
           iPR_LS_NAME varchar2(50);       
           mTRANSH_LEN int;       
           mLS1 int;       
           mLS2 int;       
           mSUMMA2 decimal (18,2);                         
           /*End GINW*/          
                    
           vDBDAY int; /*признак начала действия новой ставки в день ее изменения*/       
           iTR_BDATE date; 
           iDD_PLAN date;    
           iDENDTR_NEW date;       
           iDENDTR_OLD date;       
           l_dops_tr int; /*Если есть строки в MBK_DOPS_TRANSH, то = 1 и работаем с траншами, в противном случае 0*/                                 
           pDD date;     
           mGType int;     
            
           var_bsls_dbeg int; 
            
           var_DOG_VD int; 
                 
 cursor mTableLs is                 
  select DLS1.LSID      
        ,Max(DLS1.LSID_P)      
        ,Max(DLS1.LSID_OLD)      
        ,(case when exists (select 1     
                              from MBK_DOPS_LS DLS2     
                             where DLS2.LSID = DLS1.LSID     
                               and DLS2.VNESHID = mDOCID     
                               and nvl(DLS2.LSID_NEW,-1) > 0     
                               and nvl(DLS2.LSID_NEW,-1) <> nvl(DLS2.LSID_OLD,-1))     
                then (select Max(DLS2.LSID_NEW)     
                        from MBK_DOPS_LS DLS2     
                       where DLS2.VNESHID = mDOCID     
                         and DLS2.LSID = DLS1.LSID     
                         and nvl(DLS2.LSID_NEW,-1) > 0     
                         and nvl(DLS2.LSID_NEW,-1) <> nvl(DLS2.LSID_OLD,-1))     
           else (select Max(DLS2.LSID_NEW)     
                   from MBK_DOPS_LS DLS2     
                  where DLS2.VNESHID = mDOCID     
                    and DLS2.LSID = DLS1.LSID)     
         end)     
        ,(case when (select count(1) from MBK_DOPS_TRANSH DTR where DTR.VNESHID = mDOCID and DTR.TO_LSID = DLS1.LSID) > 0     
                 then (select DENDTR_OLD     
                         from MBK_DOPS_TRANSH     
                        where VNESHID = mDOCID     
                          and TO_LSID = DLS1.LSID)     
            else null     
          end)                     
        ,(case when (select count(1) from MBK_DOPS_TRANSH DTR where DTR.VNESHID = mDOCID and DTR.TO_LSID = DLS1.LSID) > 0     
                 then (select DENDTR_NEW     
                         from MBK_DOPS_TRANSH     
                        where VNESHID = mDOCID     
                          and TO_LSID = DLS1.LSID)                                
            else null     
          end) 
        ,(case when (select count(1) from MBK_DOPS_TRANSH DTR where DTR.VNESHID = mDOCID and DTR.TO_LSID = DLS1.LSID) > 0      
                 then (select DD      
                         from MBK_DOPS_TRANSH      
                        where VNESHID = mDOCID      
                          and TO_LSID = DLS1.LSID)                                 
            else null      
          end)      
        ,LD.BDATE                      
        ,dbo.S_DG_OSTAT(DLS1.LSID,mDD,1,1)       
    from MBK_DOPS_LS DLS1     
     left join DG_LS_DOG LD on LD.ID = DLS1.LSID     
   where DLS1.VNESHID = mDOCID     
     and not DLS1.LSID is null     
  group by DLS1.LSID,LD.BDATE;      
       
 cursor mTableDLs is     
     select DLS.PLAN_OLD,DLS.PLAN_NEW,DLS.PSTVALUE_OLD,DLS.PSTVALUE_NEW,DLS.L_PSTV_OLD,DLS.L_PSTV_NEW,DLS.USLUGAID_OLD,DLS.USLUGAID_NEW     
           ,(select Max(T.ID)     
               from DG_DOG_TARIF T     
              where T.LSTYPEID = iLSID     
                and T.PLANID = DLS.PLAN_OLD)     
       from MBK_DOPS_LS DLS     
      where DLS.VNESHID = mDOCID     
        and DLS.LSID = iLSID;     
        
  vNONEWLS int; /*не открывать новые л/с при сокращении срока сделки*/      
                           
BEGIN  
      
 select DENG     
       ,DENGOLD     
       ,nvl(RETURN_TO_OSN,0)     
       ,TO_OSN       
   into     
        iDEND_NEW     
       ,iDEND_OLD     
       ,l_to_osn     
       ,to_osn1     
   from MBK_DOPS     
  where ID = mDOCID;   
    
 select DG.DPODP     
       ,DG.DOG_TYPE     
       ,DG.PRGROUPID     
       ,DG.DBEG     
       ,DG.ORGID     
       ,nvl(DG.OVER_LEN,PR.OVER_LEN)     
       ,DG.SUMMA2     
       ,DG.S_CLASS     
       ,(case when (DG.GRAFIC_TYPE is null) and (nvl(DG.L_ANNU,0) = 1)     
                then 3     
           else nvl(DG.GRAFIC_TYPE,0)     
         end) 
       ,DG.DOG_VD       
  into     
        iDPODP     
       ,iDGTYPE     
       ,iPRD     
       ,dgDBEG     
       ,iORG     
       ,mTRANSH_LEN     
       ,mSUMMA2     
       ,mS_CLASS     
       ,mGType 
       ,var_DOG_VD      
  from DG_DOG DG     
   left join DG_PRODUCT PR on PR.ID = DG.PRGROUPID      
  where DG.ID = mDOGID;     
     
 if iDGTYPE in (4,5) and iDEND_NEW<iDEND_OLD then
  vNONEWLS := 1;
 else
  vNONEWLS := 0;
 end if;
  
 /*Проверки*/       
 if (not iDGTYPE in (11,12,16)) then 
 begin       
 select ET.ID into iEVTYPE_12     
   from DG_EVENT_TYPE ET       
  where ET.SYS_TYPE = 12 /*Пролонгация*/     
    and ET.ID in (select TYPE from DG_PRDEVT where PRD_ID = iPRD)     
    and rownum<2;       
 exception                            
  when NO_DATA_FOUND      
    then raise_application_error(-20202,'Не удалось определить типовое событие "Пролонгация" из продукта данного договора!');       
 end; 
 end if; 
 if (not iDGTYPE in (11,12,16)) then 
 begin       
 select ET.ID into iEVTYPE_75     
   from DG_EVENT_TYPE ET       
  where ET.SYS_TYPE = 75 /*Реструктуризация*/     
    and ET.ID in (select TYPE from DG_PRDEVT where PRD_ID = iPRD)     
    and rownum<2;       
 exception                            
  when NO_DATA_FOUND      
    then raise_application_error(-20202,'Не удалось определить типовое событие "Реструктуризация" из продукта данного договора!');       
 end;      
 end if; 
 if (l_to_osn = 1) then     
 begin       
 select ET.ID into iEVTYPE_82     
   from DG_EVENT_TYPE ET       
  where ET.SYS_TYPE = 82 /*Возврат на основной долг*/     
    and ET.ID in (select TYPE from DG_PRDEVT where PRD_ID = iPRD)     
    and rownum<2;       
 exception                            
  when NO_DATA_FOUND      
    then raise_application_error(-20202,'Не удалось определить типовое событие по возврату на основной долг из продукта данного договора !');       
 end;      
 end if;     
 if (l_to_osn = 1) then     
 begin       
 select ET.ID into iEVTYPE_VOZV_OSN     
   from DG_EVENT_TYPE ET       
  where ET.SYS_TYPE in (8/*Возврат ссуды*/,48 /*Гашение треб. осн*/)     
    and ET.ID in (select TYPE from DG_PRDEVT where PRD_ID = iPRD)     
    and rownum<2;       
 exception                            
  when NO_DATA_FOUND      
    then raise_application_error(-20202,'Не удалось определить типовое событие по возврату основного долга для данного типа договора !');       
 end;      
 end if; 
                      
 select count(1) into n       
   from DG_EVENT EV       
  where EV.DOGID = mDOGID      
    and nvl(EV.REAL_DD,TO_DATE('01.01.1900','dd.mm.yyyy')) > mDD;       
 if (n > 0) then       
   raise_application_error(-20202,'Имеются исполненные события в более поздних датах. Исполнение не возможно !');       
 end if;      
        
 select count(1) into n       
   from DG_VKLAD_PV PV       
  where PV.DOGID = mDOGID      
    and PV.TYPE=2      
    and nvl(PV.BUH_DD,TO_DATE('01.01.1900','dd.mm.yyyy')) > mDD;      
 if (n > 0) then       
   raise_application_error(-20202,'Имеются исполненные начисления в более поздних датах. Исполнение не возможно !');       
 end if;       
 /*End Проверки*/      
 if mS_CLASS in (1003790,1004230) then                   
  vDBDAY := cast(nvl(dbo.S_GETVAR('s_krd_restruct_stav_dd0',mDD),0) as int);        
 else       
  vDBDAY := 0;       
 end if;     
      
 if (vDBDAY = 1) then     
  pDD := mDD;     
 else     
  pDD := mDD + 1;     
 end if;      
          
 /*Ginw Запоминаем Сумму2*/       
 if (nvl(mSUMMA2,0) > 0) then       
   update MBK_DOPS set DGSUMMA2_OLD = mSUMMA2 where ID = mDOCID;       
 end if;          
        
 if (iDGTYPE = 14) then       
   iOPOID := 1004038;       
 else        
   if (iDGTYPE in (11,12,16)) then       
     iOPOID := 1004041;       
   else        
     iOPOID := 1004040;       
   end if;       
 end if;         
        
 select  MainID.NextVal into iOPID from dual;       
 insert into MBK_OPERATIONS(ID,S_S,DOGID, DD, S_CLASS, TOPDOCID, TOPDOCOID, SYS_TYPE, S_CR, S_CDT, S_MR, S_MDT)       
                    values(iOPID,1,mDOGID,mDD,iOPOID,mDOCID,mDOCOID,7,mUSERID,trunc(sysdate),mUSERID,trunc(sysdate));       
        
 update MBK_DOPS       
  set LINKDOCOID = iOPOID, LINKDOC = iOPID       
   where ID = mDOCID;      
 select count(1) into l_dops_tr from MBK_DOPS_TRANSH DTR where DTR.VNESHID = mDOCID; 
 select nvl(dbo.S_GETVAR('s_mbk_restruct_bsls_dbeg',mDD),0) into var_bsls_dbeg from dual; 
          
 /*Если поменялась дата окончания, то заносим эти изменения в историю*/       
 if (nvl(iDEND_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDEND_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))) then       
   begin       
     insert into MBK_DOPS_RERIODIC (ID,TOPDOCOID ,TOPDOCID,VNESHID,DDB,DDE,ATT,VAL_ID,VAL_STR,VAL_DATE,VAL_NUM)        
       select MainId.NextVal, mDOCOID  ,mDOCID  ,VNESHID,DDB,DDE,ATT,VAL_ID,VAL_STR,VAL_DATE,VAL_NUM        
         from DG_PERIODIC_OBJ      
        where ATT in ('DEND','TYPE_SROK')      
          and VNESHID = mDOGID;        
        
     update DG_PERIODIC_OBJ set DDE = (mDD-1)       
      where ID = (select Q.ID from (select ID from DG_PERIODIC_OBJ where ATT = 'DEND' and VNESHID = mDOGID order by DDB desc) Q where rownum < 2);      
             
     delete DG_PERIODIC_OBJ      
      where ATT = 'DEND'      
        and VNESHID = mDOGID      
        and DDB = mDD;      
                  
     insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_DATE)       
       values (MainId.NextVal,mDOGID,mDD,dbo.D4000(),'DEND',iDEND_NEW);      
              
     update DG_PERIODIC_OBJ set DDE = (mDD-1)       
      where ID = (select Q.ID from (select ID from DG_PERIODIC_OBJ where ATT = 'TYPE_SROK' and VNESHID = mDOGID order by DDB desc) Q where rownum < 2);      
             
     delete DG_PERIODIC_OBJ      
      where ATT = 'TYPE_SROK'      
        and VNESHID = mDOGID      
        and DDB = mDD;      
                                         
     insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_ID)       
       values  (MainId.NextVal,mDOGID,mDD,dbo.D4000(),'TYPE_SROK',(case when iDEND_NEW is null then 2 else 1 end));       
   end;       
 end if;       
 /*End Если поменялась дата окончания, то заносим эти изменения в историю*/      
       
 mEVID := null;      
  
 /*Идем по течениям*/           
 OPEN mTableLs;                     
 LOOP                         
  Fetch mTableLs INTO iLSID,iLSID_P,iLSID_OLD,iLSID_NEW,iDENDTR_OLD,iDENDTR_NEW,iDD_PLAN,iTR_BDATE,mSM;     
  exit when mTableLs%NOTFOUND;      
         
  nbsid_ls := null;      
  nbsid_ls_new := null;                     
  nID := null;      
  iPLAN_CHANGE := 0;     
  if (not iLSID_P is null) then     
   iLSTO := iLSID_P;     
  else     
   iLSTO := iLSID;     
  end if;      
  
  if vNONEWLS=0 then
  
  /*Если Не овердрафт - 23.04.2018, Замечание 28546 
    И 
    Если изменилась дата Окончания договора или дата окончания транша, считаем балансовые 2-ого порядка*/     
    if (nvl(var_DOG_VD,0) <> 4) 
        AND (   ((l_dops_tr = 0) and (nvl(iDEND_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDEND_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))     
             OR ((l_dops_tr > 0) and (nvl(iDENDTR_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDENDTR_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))) 
      then 
     begin       
      begin      
        select RLS.NBSID       
              ,(case when (l_dops_tr = 0)     
                       then dbo.S_MBK_GETNBSID(0,DG.DBEG,iDEND_NEW,CPL.PARENTOID, 0, 0, 0, 0) 
                     when (l_dops_tr > 0) and (var_bsls_dbeg = 1) 
                       then dbo.S_MBK_GETNBSID(0,DG.DBEG,iDENDTR_NEW,CPL.PARENTOID, 0, 0, 0, 0) 
                  else dbo.S_MBK_GETNBSID(0,LD.BDATE,iDENDTR_NEW,CPL.PARENTOID, 0, 0, 0, 0)     
                end)     
          into       
               nbsid_ls       
              ,nbsid_ls_new       
        from DG_LS_DOG LD     
         left join RKO_LS RLS on RLS.ID=dbo.S_DG_POBJ_GI('LSID',LD.ID,mDD)           
          left join COM_PLAN001 CPL on CPL.ID = RLS.NBSID     
          left join DG_PRODLS PLS on PLS.ID = LD.LS_PRD     
          left join DG_DOG DG on DG.ID=LD.DOG_ID      
       where LD.ID = iLSID;     
      exception                              
       when NO_DATA_FOUND      
         then nbsid_ls:=null;      
              nbsid_ls_new:=null;      
      end; 
       
      update DG_LS_DOG set EDATE = (case when (l_dops_tr = 0) then iDEND_NEW else iDENDTR_NEW end) 
       where ID = iLSTO; 
     end;            
    end if;           
  
  /*Если новый л/с задан, берем его. Если нет и балансовый поменялся - находим подходящий/открываем новый*/     
    if (nvl(iLSID_OLD,0) <> nvl(iLSID_NEW,0)) then     
     nID := iLSID_NEW;     
    else     
     begin     
      if (not nbsid_ls is null) and (not nbsid_ls_new is null) and (nbsid_ls <> nbsid_ls_new) and (nvl(mSM,-1)>0) then        
       begin       
        /*проверяем наличие счетов с таким же балансовым*/      
        if (nvl(dbo.S_GETVAR('s_mbk_restruct_bsls',mDD),0) = 1) then     
          select max(RLS.ID) into nID     
            from  DG_LS_DOG LD     
             left join RKO_LS RLS on RLS.ID=dbo.S_DG_POBJ_GI('LSID',LD.ID,mDD)      
           where LD.DOG_ID = mDOGID     
             and LD.TYPEID in (select TYPEID from DG_LS_DOG where ID = iLSID)     
             and RLS.NBSID = nbsid_ls_new 
             and RLS.DOPN <= mDD 
             and RLS.DCLS is null;     
        end if;     
        /*Если не подобрали, то откроем новый л/с*/               
        if (nvl(nID,0) = 0) then     
         begin     
          select PLS.LOPEN     
                ,PLS.NAME     
            into     
                 iLOPEN_TO     
                ,iPR_LS_NAME     
            from DG_LS_DOG LD     
             left join DG_PRODLS PLS on PLS.ID = LD.LS_PRD     
             left join DG_DOG DG on DG.ID=LD.DOG_ID     
           where LD.ID = iLSID;     
               
          if (not iLOPEN_TO in (1,3)) then     
           raise_application_error(-20202,'Не удалось открыть новый л/с по течению с типом '||iPR_LS_NAME||'. Режим открытия счета на продукте должен быть Автоматически с договором, либо По необходимости !');       
          end if;     
               
          /*Если необходимо, поставим нужную дату начала транша перед открытием л/с*/      
          update DG_LS_DOG set BDATE = dgDBEG          
           where ID = iLSID     
             and ((l_dops_tr = 0) or ((l_dops_tr > 0) and (var_bsls_dbeg = 1)));     
          /*ya-m-a в userID передаем ссылку на пролонгируемый счет, для обработки маски*/     
          iLSID_OLD2 := -1*nvl(iLSID_OLD,-1);     
               
          if (l_dops_tr > 0) then     
           dbo.S_MBK_AFFIRM (iLSID_OLD2, mS_CLASS, mDOGID, iLSID, mDD, nID, null, iDENDTR_NEW, null);     
          else     
           dbo.S_MBK_AFFIRM (iLSID_OLD2, mS_CLASS, mDOGID, iLSID, mDD, nID, null, iDEND_NEW, null);     
          end if;     
                
          /*Возвращаем дату начала транша*/     
          update DG_LS_DOG set BDATE = iTR_BDATE     
           where ID = iLSID     
             and ((l_dops_tr = 0) or ((l_dops_tr > 0) and (var_bsls_dbeg = 1)));     
               
          /*Проставим признак открытия нового л/с, пригодится в случае удаления операции*/        
          update MBK_DOPS_LS set L_LSID_NEW = 1     
           where VNESHID = mDOCID     
             and LSID = iLSID;     
         end;     
        end if;     
             
        if (nvl(nID,0) > 0) then     
         update MBK_DOPS_LS set LSID_NEW = nID     
          where VNESHID = mDOCID     
            and LSID = iLSID;     
        end if;         
       end;     
      end if;      
     end;     
    end if;      
    
    /*Привязываем л/с к течению*/     
    if (nvl(nID,0) > 0) then     
     begin     
      select count(1) into n from DG_PERIODIC_OBJ where VNESHID = iLSTO and ATT = 'LSID' and DDB >= mDD;     
      if (n > 0) then     
       begin     
        select NAME into iTO_LSID_NAME from DG_LS_DOG where ID = iLSTO;     
        raise_application_error(-20202,'Невозможно привязать л/с к течению '||iTO_LSID_NAME||'! Уже имеются л/с, привязанные в дату реструктуризации, либо позднее!');     
       end;     
      end if;      
            
      update DG_PERIODIC_OBJ set DDE = (mDD - 1) where VNESHID = iLSTO and ATT = 'LSID' and DDB < mDD and DDE >= mDD;     
           
      insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_ID)     
       values (MainId.NextVal,iLSTO,mDD,dbo.D4000(),'LSID',nID);     
            
      update MBK_DOPS_LS set LSID_NEW = nID     
           where VNESHID = mDOCID     
             and LSID = iLSID;     
     end;     
    end if;     
    
  end if;
  
  
  /*Если Течение не меняется*/     
  if (iLSTO = iLSID) and (iLSID_P is null) then     
   begin       
    /*запомним свои % ставки по тарифным строкам счета*/     
    insert into MBK_DOPS_RERIODIC (ID,TOPDOCOID,TOPDOCID,LSID,VNESHID,DDB,DDE,ATT,VAL_ID,VAL_STR,VAL_DATE,VAL_NUM)      
     select MainId.NextVal,mDOCOID,mDOCID,T.LSTYPEID,P.VNESHID,P.DDB,P.DDE,P.ATT,P.VAL_ID,P.VAL_STR,P.VAL_DATE,P.VAL_NUM     
       from DG_DOG_TARIF T      
        join DG_PERIODIC_OBJ P on P.VNESHID = T.ID and P.ATT='PSTVALUE'     
      where T.LSTYPEID = iLSID     
        and not exists (select 1 from MBK_DOPS_RERIODIC where ATT='PSTVALUE' and VNESHID=T.ID and TOPDOCOID = mDOCOID and TOPDOCID = mDOCID);     
        
    OPEN mTableDLs;                     
    LOOP                         
      Fetch mTableDLs INTO iPLAN_OLD,iPLAN_NEW,iPSTVALUE_OLD,iPSTVALUE_NEW,iL_PSTV_OLD,iL_PSTV_NEW,iUSLUGAID_OLD,iUSLUGAID_NEW,iTARIF;     
      exit when mTableDLs%NOTFOUND;     
         
      /*Если поменялся тарифный план*/     
      if (iPLAN_OLD <> iPLAN_NEW) then     
       begin     
        /*отключим Старую тарифную строку*/     
        delete DG_PERIODIC_OBJ where VNESHID = iTARIF and ATT = 'PSTVALUE' and DDB >= pDD;     
        update DG_PERIODIC_OBJ set DDE = (pDD - 1)     
         where VNESHID = iTARIF     
           and ATT = 'PSTVALUE'     
           and DDB < pDD     
           and DDE >= pDD;     
        insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_NUM)     
         values (MainId.NextVal,iTARIF,pDD,dbo.D4000(),'PSTVALUE',0);     
        update DG_DOG_TARIF set L_PSTV = 1     
         where ID = iTARIF;        
        /*добавим и включим Новую тарифную строку*/     
        select MainId.NextVal into iTARIF_NEW from dual;     
        insert into DG_DOG_TARIF (ID,DOGID,LSTYPEID,TARIFTYPE,COUNTTYPE,USLUGAID,PLANID,L_PSTV)     
         select iTARIF_NEW,DOGID,LSTYPEID,TARIFTYPE,COUNTTYPE,iUSLUGAID_NEW,iPLAN_NEW,iL_PSTV_NEW     
           from DG_DOG_TARIF     
          where ID = iTARIF;      
        insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_NUM)     
         values (MainId.NextVal,iTARIF_NEW,iTR_BDATE,(pDD - 1),'PSTVALUE',0);     
        insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_NUM)     
         values (MainId.NextVal,iTARIF_NEW,pDD,dbo.D4000(),'PSTVALUE',iPSTVALUE_NEW);     
        iPLAN_CHANGE := iPLAN_CHANGE + 1;     
       end;     
      else     
       iTARIF_NEW := iTARIF;     
      end if;        
               
      /*Если тарифный план Не менялся, но поменялась своя процентная ставка*/     
      if (iPLAN_OLD = iPLAN_NEW) and (nvl(iPSTVALUE_OLD,-1) <> nvl(iPSTVALUE_NEW,-1)) and (not iPSTVALUE_NEW is null) then     
       begin     
        delete DG_PERIODIC_OBJ where VNESHID = iTARIF and ATT = 'PSTVALUE' and DDB >= pDD;     
        update DG_PERIODIC_OBJ set DDE = (pDD - 1)     
         where VNESHID = iTARIF     
           and ATT = 'PSTVALUE'     
           and DDB < pDD     
           and DDE >= pDD;     
        insert into DG_PERIODIC_OBJ (ID,VNESHID,DDB,DDE,ATT,VAL_NUM)     
              values (MainId.NextVal,iTARIF,pDD,dbo.D4000(),'PSTVALUE',iPSTVALUE_NEW);     
       end;     
      end if;      
       
      update DG_DOG_TARIF set L_PSTV = iL_PSTV_NEW        
       where ID = iTARIF_NEW;     
    END LOOP;       
    Close mTableDLs;    
   end;    
  end if;    
  
   if (not iLSID_P is null) and (nvl(mSM,-1)>0) then      
     begin     
       insert into DG_EVENT_OLD (ID, DOGID, FROM_LSID, SM_FROM, DD, REAL_DD, DBEG_VERS, DEND_VERS, SOST, TYPE, DOC_ID, TO_LSID, SM_TO, PVID, BT_ROW, DOC_OID, LINKEV, TOPDOCOID,TOPDOCID)     
         select MainId.NextVal,DOGID,FROM_LSID,SM_FROM,DD,REAL_DD,DBEG_VERS,DEND_VERS,SOST,TYPE,DOC_ID,TO_LSID,SM_TO,PVID,BT_ROW,DOC_OID,-1,mDOCOID,mDOCID     
           from DG_EVENT     
          where ID in (select EV.ID     
                         from DG_EVENT EV     
                          left join DG_EVENT_TYPE ET on ET.ID = EV.TYPE     
                        where EV.DOGID = mDOGID     
                          and EV.DD < mDD     
                          and REAL_DD is null     
                          and FROM_LSID = iLSID);     
       delete from DG_EVENT     
        where ID in (select EV.ID     
                       from DG_EVENT EV     
                        left join DG_EVENT_TYPE ET on ET.ID = EV.TYPE     
                      where EV.DOGID = mDOGID     
                        and EV.DD < mDD     
                        and REAL_DD is null     
                        and FROM_LSID = iLSID);     
       update DG_EVENT      
         set FROM_LSID = iLSTO     
        where DOGID = mDOGID     
          and FROM_LSID = iLSID     
          and REAL_DD is null     
          and DD >= mDD;     
     end;     
    end if;     
  
  /*Ginw добавлена проверка на совпадение типов течений события "пролонгация" из справочника событий и пролонгируемого течения*/       
  /*пока только для Кредитов*/         
  if (iDGTYPE = 2) then       
   select count(1) into n from DG_EVENT_TYPE ET where ET.DG_TYPE = iDGTYPE and ET.SYS_TYPE in (12) and nvl(STREAM_FROM,-1)=(select TYPEID from DG_LS_DOG where ID=iLSID);       
  else        
   n:=1;       
  end if;         
  
  /*Если остаток на течении больше 0     
    И (есть течение iLSID_P для переноса остатка     
       ИЛИ изменилась дата окончания договора для Безтраншевого договора     
       ИЛИ изменилась дата окончания транша для договора с наличием траншей     
       ИЛИ изменился тарифный план     
      )     
         
    То Создаем событие пролонгации/реструктуризации     
  */      
  if (nvl(mSM,-1) > 0)     
    AND (   (not iLSID_P is null)     
         OR ((iLSID_P is null) and (l_dops_tr = 0) and (nvl(iDEND_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDEND_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))     
         OR ((iLSID_P is null) and (l_dops_tr > 0) and (nvl(iDENDTR_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDENDTR_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))     
         OR (iPLAN_CHANGE > 0))     
    AND (n > 0)     
    then     
   begin     
    /*Если дата окончания договора/транша увеличилась, то это пролонгация, в противном случае - реструктуризация*/     
    if      ((iLSID_P is null) and (l_dops_tr = 0) and (nvl(iDEND_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) < nvl(iDEND_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))     
         OR ((iLSID_P is null) and (l_dops_tr > 0) and (nvl(iDENDTR_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) < nvl(iDENDTR_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))))     
      then        
     iEVTYPE := iEVTYPE_12;     
    else     
     iEVTYPE := iEVTYPE_75;     
    end if;      
        
    select MainId.NextVal into mEVID from dual;       
    insert into DG_EVENT (ID,DOGID,FROM_LSID,SM_FROM,DD,DBEG_VERS,SOST,TYPE,DOC_ID,TO_LSID,SM_TO,DOC_OID,S_S,TOPDOCOID,TOPDOCID,DENDTR)     
     values (mEVID,mDOGID,iLSID,mSM,mDD,iDPODP,1,iEVTYPE,iOPID,iLSTO,mSM,iOPOID,1,iOPOID,iOPID,iDENDTR_NEW);     
    mEV_CLASS := (case when mS_CLASS=1003898 then 1003903     
                       when mS_CLASS=1003868 then 1003904     
                       when mS_CLASS=1003790 then 1003831     
                       when mS_CLASS=1003574 then 1003578     
                       when mS_CLASS=1003737 then 1003741     
                    end);     
        
    if (not nID is null) then     
     dbo.S_MBK_EXEVENT (mUSERID,mDOGID,mEVID,mS_CLASS,mDD,null,2,1,iOPOID,iOPID,nDOCS);       
    else      
     update DG_EVENT set REAL_DD = mDD, SOST = 2, S_S = 1000230     
      where ID = mEVID;     
    end if;       
   end;     
  end if;      
  if (iLSID_P is null) and (l_dops_tr > 0) and (nvl(iDENDTR_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDENDTR_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))) then     
   begin     
    update DG_EVENT set DD = iDENDTR_NEW     
     where DOGID = mDOGID     
       and FROM_LSID = iLSID     
       and REAL_DD is null     
       and DD >= iDENDTR_OLD;     
          
    update DG_EVENT set DENDTR = iDENDTR_NEW     
     where DOGID = mDOGID 
       and DD = iDD_PLAN     
       and TO_LSID = iLSID      
       and TYPE in (select ID from DG_EVENT_TYPE where SYS_TYPE in (7,75,12) and DG_TYPE=(select DOG_TYPE from DG_DOG where ID=mDOGID));     
   end;     
  end if;                           
  if (iLSID_P is null) and (l_dops_tr = 0) and (nvl(iDEND_OLD,TO_DATE('01.01.1900','dd.mm.yyyy')) <> nvl(iDEND_NEW,TO_DATE('01.01.1900','dd.mm.yyyy'))) then     
   begin     
    update DG_EVENT set DD = iDEND_NEW           
     where DOGID = mDOGID     
       and FROM_LSID = iLSID     
       and DD >= iDEND_OLD     
       and REAL_DD is null     
       and not TYPE in (select ID from DG_EVENT_TYPE where SYS_TYPE in (12,75)); /*Ginw пролонгацию и Реструктуризацию не трогаем*/     
   end;     
  end if;     
       
  /*Возврат на основной долг*/     
  if (l_to_osn = 1) and (to_osn1 > 0) then     
  begin     
   /*Ищем течение, на которое был вынос на просрочку ОСН*/     
   select nvl(Max(EV.TO_LSID),0) into iLSTO_PROSR     
     from DG_EVENT EV     
      left join DG_LS_DOG LD2 on LD2.ID = EV.TO_LSID     
       left join DG_LS_TYPES LT2 on LT2.ID = LD2.TYPEID     
    where EV.DOGID = mDOGID     
      and EV.FROM_LSID = iLSID     
      and LT2.SYSTYPE = 24; /*Просрочка ОСН*/     
   if (iLSTO_PROSR > 0) then     
    begin     
     select dbo.S_DG_OSTAT(iLSTO_PROSR,mDD,1,1) into iLSTO_PROSR_OST from dual;     
     if (iLSTO_PROSR_OST > to_osn1) then     
      iLSTO_PROSR_OST := to_osn1;     
     end if;      
     if (iLSTO_PROSR_OST > 0) then     
      begin     
       dbo.S_MBK_CR_EVENT (mDOGID, mDD, iLSTO_PROSR_OST,82,1,back_id);       
            
       update DG_EVENT     
         set FROM_LSID = iLSTO     
            ,TO_LSID = iLSTO_PROSR     
            ,DOC_OID = iOPOID     
            ,DOC_ID = iOPID     
            ,TOPDOCOID = iOPOID     
            ,TOPDOCID = iOPID      
        where ID = back_id;     
            
       select dbo.S_DG_POBJ_GI('LSID',EV.FROM_LSID,mDD)     
             ,dbo.S_DG_POBJ_GI('LSID',EV.TO_LSID,mDD)     
         into     
              mLS1     
             ,mLS2             
         from DG_EVENT EV     
        where EV.ID = back_id;     
       
       if (nvl(mLS1,0) > 0) and (nvl(mLS2,0) > 0) then     
        dbo.S_MBK_EXEVENT (mUSERID,mDOGID,back_id,mS_CLASS,mDD,Null,2,1,iOPOID,iOPID,temp_back_id);       
       end if;     
            
       /*Для Не Аннуитетных и Дифференцированных графиков закроем течение*/     
       if (not mGType in (2,3)) then     
        begin     
         if (iDGTYPE = 14) then /*Цессия*/     
           ev_type := 48; /*Гашение треб. осн*/     
         else /*Прочие договора*/     
           ev_type := 8; /*Возврат ссуды*/     
         end if;     
              
         if (l_dops_tr > 0) then     
          dbo.S_MBK_CR_EVENT (mDOGID,iDENDTR_NEW,iLSTO_PROSR_OST,ev_type,1,back_id);     
         else     
          dbo.S_MBK_CR_EVENT (mDOGID,iDEND_NEW,iLSTO_PROSR_OST,ev_type,1,back_id);     
         end if;     
               
         update DG_EVENT     
           set FROM_LSID = iLSTO     
              ,DOC_OID = iOPOID     
              ,DOC_ID = iOPID     
              ,TOPDOCOID = iOPOID     
              ,TOPDOCID = iOPID     
          where ID = back_id;     
        end;     
       end if;     
             
       to_osn1 := to_osn1 - iLSTO_PROSR_OST;     
      end;     
     end if;      
    end;     
   end if;           
  end;     
  end if;        
  
 END LOOP;       
 Close mTableLs;           
  
  
 /*если переводим в до востребования*/   
 if ((iDEND_NEW is null) and (not iDEND_OLD is null)) then   
   begin   
     insert into DG_EVENT_OLD (ID, DOGID , FROM_LSID, SM_FROM, DD, REAL_DD, DBEG_VERS, DEND_VERS, SOST, TYPE, DOC_ID, TO_LSID, SM_TO, PVID, BT_ROW, DOC_OID, LINKEV, TOPDOCOID,TOPDOCID)   
       select MainId.NextVal,DOGID,FROM_LSID,SM_FROM,DD,REAL_DD,DBEG_VERS,DEND_VERS,SOST,TYPE,DOC_ID,TO_LSID,SM_TO,PVID,BT_ROW,DOC_OID,-1,mDOCOID,mDOCID   
         from DG_EVENT   
        where ID in (select EV.ID   
                       from DG_EVENT EV   
                        left join DG_EVENT_TYPE ET on ET.ID=EV.TYPE   
                      where EV.DOGID = mDOGID   
                        and EV.DD >= mDD   
                        and REAL_DD is null   
                        and ET.SYS_TYPE in (4,8,13,29,36,37,38,39,40,48));   
   
     delete from DG_EVENT   
      where ID in (select EV.ID   
                     from DG_EVENT EV   
                      left join DG_EVENT_TYPE ET on ET.ID=EV.TYPE   
                    where EV.DOGID = mDOGID   
                      and EV.DD >= mDD   
                      and EV.REAL_DD is null   
                      and ET.SYS_TYPE in (4,8,13,29,36,37,38,39,40,48));   
   end;   
  end if;    
  
  
 /*если переводим из до востребования*/   
 if ((not iDEND_NEW is null) and (iDEND_OLD is null)) then   
   begin   
     OPEN mTableLs;                     
     LOOP                         
       Fetch mTableLs INTO iLSID,iLSID_P,iLSID_OLD,iLSID_NEW,iDENDTR_OLD,iDENDTR_NEW,iDD_PLAN,iTR_BDATE,mSM;     
       exit when mTableLs%NOTFOUND;      
   
       select ET.SYSTYPE into iLSYS_TYPE   
         from DG_LS_DOG LD   
          left join DG_LS_TYPES ET on ET.ID=LD.TYPEID   
        where LD.ID = iLSID   
          and rownum < 2;   
   
       if iLSYS_TYPE in (2,3,21,46,47,-48,-49,-50,51,52) then   
         begin   
           mSM := dbo.S_DG_OSTAT (iLSID, mDD , 2 , 1);   
           if  nvl(mSM,-1)>0 then   
             begin   
               select ID into iEVTG   
                           from DG_EVENT_TYPE   
                          where ID = (select PR.TYPE   
                                        from DG_PRDEVT PR   
                                         left join DG_EVENT_TYPE ET on ET.ID=PR.TYPE   
                                       where PR.PRD_ID = iPRD   
                                         and ET.SYS_TYPE = (case when iLSYS_TYPE=2 then 8   
                                                                 when iLSYS_TYPE=3 then 4   
                                                                 when iLSYS_TYPE=21 then 4   
                                                                 when iLSYS_TYPE=46 then 48   
                                                                 when iLSYS_TYPE=47 then 29   
                                                                 when iLSYS_TYPE=48 then 36   
                                                                 when iLSYS_TYPE=49 then 37   
                                                                 when iLSYS_TYPE=50 then 40   
                                                                 when iLSYS_TYPE=51 then 38   
                                                                 when iLSYS_TYPE=52 then 39   
                                                             end)   
                                         and rownum < 2)   
                            and rownum < 2;   
   
               if (not iEVTG is null) then   
                 begin   
                   iFROM_LSID := null;   
                   iTO_LSID := null;   
   
                   select ID into iFROM_LSID   
                     from DG_LS_DOG   
                    where DOG_ID = mDOGID   
                      and TYPEID in (select STREAM_FROM from DG_EVENT_TYPE where ID=iEVTG)   
                      and rownum < 2;   
                    
                   select ID into iTO_LSID   
                     from DG_LS_DOG   
                    where DOG_ID = mDOGID   
                      and TYPEID in (select STREAM_TO from DG_EVENT_TYPE where ID=iEVTG)   
                      and rownum < 2;   
   
                   insert into DG_EVENT (ID, DOGID  ,FROM_LSID  ,SM_FROM,DD  ,REAL_DD,DBEG_VERS,DEND_VERS,SOST,TYPE  ,DOC_ID  ,TO_LSID  ,SM_TO,PVID,BT_ROW,DOC_OID ,S_S,BUCH_DOC,TOPDOCID,TOPDOCOID,SYSTM)   
                       values(MainId.NextVal,mDOGID,iFROM_LSID,null   ,iDEND_NEW,null   ,mDD     ,null     ,1   ,iEVTG,iOPID,iTO_LSID,mSM ,null,null  ,iOPOID,1  ,null    ,null    ,null     ,null);   
                 end;   
               end if;     
             end;   
           end if;     
         end;   
       end if;     
     END LOOP;   
     Close mTableLs;   
   end;   
  end if;    
           
END S_MBK_RESTRUCT; 
 
/*OMEGA INFO: 30.06.2018 12:09:58, Дерунец А. Ю.; Версия: 240*/
