create or replace procedure     S_GK_NALOG_CB_REALIZ_R1  
      
       
/*                     
  Процедура создания налогового регистра по выбытию ц/б                     
*/                     
  (aFROM_DATE date, /* дата начала отчетного периода */                     
   aTO_DATE date, /* дата окончания отчетного периода */                     
   v_sPurpose varchar, /*0 - ТП, 1 - ИП, 2 - НП, проверяется, что в этой строке содержится цифра, соотв. цели учёта о сделке*/                     
   aMODE number := null,                     
   v_DOCOID_REALIZ number := null,                     
   v_DS_ID number := null                     
    )                     
/*                     
  aMODE = 1 - необращающиеся                     
  aMODE = 2 - обращающиеся                     
  aMODE = null - необращающиеся + обращающиеся                     
*/                     
/*                                                                                          
  @v_DS_ID - ID сделки по продаже...                     
*/                     
as                     
  /* TODO pad 15.11.2016 Нужно как-то учесть частичное погашение наминала. См. nсс2, бумага 524250150. Пока по страрому. */                     
  /* pad 10.11.2016                     
   WARNING При возникновении ликвидационного неттинга сделки по покупке не создается, необходимо брать данные из реестра, если их нет в покупке                     
  */                     
  vMODE number(1);                     
  vID_NALOG number(12);                     
  vID_TABLE number(12);                     
  vIS_FULL_SELL number(1);                     
  vALL_CDKDSH number(22);                     
  vALL_NKD number(22, 2);                     
  vALL_ZATR number(22, 2);                     
  vALL_DISC number(22, 2);                     
  vALL_PERE number(22, 2);                     
  vCSUM1 number(22, 2);                     
  vCSUM2 number(22, 2);                     
  vCSUM3 number(22, 2);                     
  vS11 number(22, 2);                     
  vS12 number(22, 2);                     
  vS13 number(22, 2);                     
  vS2 number(22, 2);                     
  vS1 number(22, 2);                     
  v_CURS number(16,8);                     
  v_CURS_VALPAY number(16,8);                     
  s_str varchar(255);                     
  vLS number(22);                     
                     
  vPKD_SUM number(22,2);                     
  vZATR_SUM number(22,2);                     
  vDISC_SUM number(22,2);                     
  vPKD_i number(22,2);                     
  vZATR_i number(22,2);                     
  vDISC_i number(22,2);                     
  vALL_ZATRATI number(22,2);                     
  vMax_ID number(12);                     
  v_CountCbPerSpis number(22,4);                     
  v_CountCbEndPeriod number(22,4);                     
  v_DateCalcAD date;                     
               
/*  v_NewVersion number;  */           
begin                     
/*  v_NewVersion := 1;  */           
/*  v_DateCalcAD := to_date('01.01.2000','dd.mm.yyyy');  */                   
  v_DateCalcAD := dbo.S_GETVARD('s_gk_date_calc_nalog_ad',aTO_DATE);                   
  v_DateCalcAD := nvl(v_DateCalcAD,to_date('01.01.2015','dd.mm.yyyy'));                
  vMODE := aMODE;                     
  /* Очистка временных таблиц */                     
  if not (aMODE is null) then                     
    delete from GK_NALOG_CB_REALIZ1;                     
    delete from GK_NALOG_CB_REALIZ_TABLE1;                     
  end if;                     
  /* Выполнение загрузки обращающихся и необращающихся разом */                     
  if aMODE is null then                     
    dbo.S_GK_NALOG_CB_REALIZ_R1(aFROM_DATE, aTO_DATE, v_sPurpose, 1, v_DOCOID_REALIZ, v_DS_ID); /* запускаем по необращающимся */                     
    vMODE := 2; /* текущий вызов считаем по обращающимся */                     
  end if;                     
  /* Заполнение таблицы по продаже */                     
  for qSELL in (/*select distinct                     
                    B.*,                     
                    case                     
                      when B.PRICE > B.MIN_PRICE then 0                     
                      else (B.MIN_PRICE - B.PRICE)                     
                    end as "MIN_DEVIATION"                     
               from (*/       
               select distinct A.*,                     
                            case                
                              when A.MIN_PRICE_R <= A.PRICE then A.PRICE                
                              else A.MIN_PRICE_R                
                            end as "MIN_PRICE",                
                            case                
                              when A.MIN_PRICE_R <= A.PRICE then 0       
                              else A.MIN_PRICE_R - A.PRICE       
                            end as "MIN_DEVIATION",                
/*                          nvl(nvl(A.SALDOP, A.SALDOO), 0) as "SALDO"         */       
                            case when A.PORTFEL_ID = 2 and A.DOCOID_REALIZ = 1003117 then       
                              nvl((select sum(VP.SUMRUB)       
                                     from RKO_VP VP       
                                    where VP.DOC_OID = 1003117       
                                      and VP.DOC_ID = A.DEAL_ID       
                                      and VP.NSD = A.LS_DOB_CAP_PLUS       
                                      and VP.S_S > 0),0)       
                              - nvl((select sum(VP.SUMRUB)       
                                       from RKO_VP VP       
                                      where VP.DOC_OID = 1003117       
                                        and VP.DOC_ID = A.DEAL_ID       
                                        and VP.NSK = A.LS_DOB_CAP_MINUS       
                                        and VP.S_S > 0),0)       
                                 else 0       
                            end "SALDO"       
                              
                       from (select nvl(dbo.GS_GK_HISTORY('NAME_SH', DS.CDKDSH, DS.DD),CB.NAME) as "CDKDSH_NAME",                
                                    DS.CDKDSH as "CDKDSH_ID",                
                                    DS.DD as "DEAL_DATE",                     
                                    DS.NDEAL as "DEAL_NUM",                     
                                    DS.AMSH as "CDKDSH_COUNT",                     
                                    case DS.PORTFEL                     
                                      when 0 then 'ТП'                     
                                      when 1 then 'ИП'                     
                                      when 2 then 'НП'                     
                                    end as "PORTFEL",                     
                                    case                     
                                      when CB.VALUT <> CC.ID then                     
                                        dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.DD)                     
                                      else 0                     
                                    end as "KURS",                     
                                    round(case CB.PRNM                     
                                            when 1 then                     
                                                case DS.PRICETYPE                     
                                                  when 0 then DS.SHPRICE * 100                     
                                                  else DS.SHPRICE                     
                                                       * dbo.S_GKO_FNOMINAL(DS.CDKDSH,                     
                                                                            nvl((select min(DTNMMAT) - 1                     
                                                                                   from GK_VALUE                     
                                                                                  where CODECB = DS.CDKDSH                     
                                                                                    and nvl(DTNMMAT_REAL,DTNMMAT) >= trunc(DS.DD)),                     
                                                                                 trunc(DS.DD)))                     
                                                end * DS.AMSH                     
                                            else DS.DEALSUM * 100                  /*nav250121*/     
                                          end * dbo.S_COM_CURS(1, CB.VALUT, CC.ID, case          
                                                                                     when (select nvl(ORCBSIGN,0) from GK_TRADEMARKETS where ID=DS.CONTRAGENT) = 1     
                                                                                          and CB.VALUT <> (select IDVAL from RKO_WORK_VALUTS where ID = DS.CDVALPAY) then DS.VBR_DTCONTR     
                                                                                     else DS.DD     
                                                                                   end) / 100,2)    
                                      /*nav250318*/    
                                    + case     
                                        when (select nvl(ORCBSIGN,0) from GK_TRADEMARKETS where ID=DS.CONTRAGENT) = 1     
                                             and CB.VALUT <> (select IDVAL from RKO_WORK_VALUTS where ID = DS.CDVALPAY)    
                                             and trunc(DS.DD) > DS.VBR_DTCONTR    
                                          then round(DS.NKD * dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.VBR_DTCONTR),2)    
                                             - round(DS.NKD * dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.DD),2)    
                                                                                  
                                        else 0    
                                      end    
                                    + case         
                                        when DS.DEALTYPE = 6 and O.ID is not null then         
                                          round((round(O.OFERTA_SUM         
                                                       * (select sum(DS1.AMSH) from GK_DEPODEAL DS1         
                                                           where DS1.DD = DS.DD and DS1.CDKDSH = DS.CDKDSH and DS1.DEALTYPE = 6  and DS1.S_S = 1000066         
                                                             and DS1.ID <= DS.ID)         
                                                        / (select sum(DS1.AMSH) from GK_DEPODEAL DS1         
                                                           where DS1.DD = DS.DD and DS1.CDKDSH = DS.CDKDSH and DS1.DEALTYPE = 6  and DS1.S_S = 1000066         
                                                             and 1=1), 2)         
                                                 - round(O.OFERTA_SUM         
                                                         * nvl((select sum(DS1.AMSH) from GK_DEPODEAL DS1         
                                                                 where DS1.DD = DS.DD and DS1.CDKDSH = DS.CDKDSH and DS1.DEALTYPE = 6  and DS1.S_S = 1000066         
                                                                   and DS1.ID < DS.ID), 0)         
                                                         / (select sum(DS1.AMSH) from GK_DEPODEAL DS1         
                                                             where DS1.DD = DS.DD and DS1.CDKDSH = DS.CDKDSH and DS1.DEALTYPE = 6  and DS1.S_S = 1000066         
                                                               and 1=1), 2))         
                                                * case O.OFERTA_VALUT         
                                                    when 1 then dbo.S_COM_CURS(1,CB.VALUT, CC.ID, DS.DD)         
                                                    else 1         
                                                  end, 2)         
                                        else 0         
                                      end          
                                      as "PRICE",         
                                   /*0**/    
                                    round(case                     
                                      when DS.DEALTYPE = 6 or nvl(DS.VIKUP_NOMINAL,0) = 1 then 0                
                                      else coalesce((select case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,DS.DD),1)    
                                                              when 0 then 0.8 * nvl(nullif(Q.LAST_PRICE,0), Q.PRICE_NEOBR)/*nvl(case K.PRICETYPE when 1 then Q.QUOTPERCENT else Q.QUOTABS end, Q.PRICE_NEOBR) *//* необращающиеся */                     
                                                              when 1 then Q.MINPRICE /* обращающиеся */                
                                                            end                
                                                       from GK_QUOT Q                
                                                      where Q.CODECB = DS.CDKDSH                
                                                        and nvl(Q.SOURCE,0) <> 3                
                                                        and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,DS.DD),1)    
                                                              when 0 then coalesce(nullif(Q.LAST_PRICE,0), Q.PRICE_NEOBR,0)/*coalesce(nullif(case K.PRICETYPE when 1 then Q.QUOTPERCENT else Q.QUOTABS end,0), Q.PRICE_NEOBR,0)*/                
                                                              when 1 then nvl(Q.MINPRICE,0)    
                                                            end > 0                
                                                        and trunc(Q.DTQUOT) =                
                                                              (select max(trunc(A.DTQUOT))                
                                                                 from GK_QUOT A                
                                                                where A.CODECB = DS.CDKDSH                
                                                                  and A.DTQUOT <= trunc(nvl(DS.VBR_DTCONTR,DS.DD))                
                                                                  and A.DTQUOT >= ADD_MONTHS(trunc(nvl(DS.VBR_DTCONTR,DS.DD)),-3)                
                                                                  and nvl(A.SOURCE,0) <> 3                
                                                                  and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,DS.DD),1)    
                                                                        when 0 then coalesce(nullif(A.LAST_PRICE,0), A.PRICE_NEOBR,0)/*coalesce(nullif(case K.PRICETYPE when 1 then QUOTPERCENT else QUOTABS end,0), PRICE_NEOBR,0)*/                
                                                                        when 1 then nvl(A.MINPRICE,0)    
                                                                      end > 0)                     
                                                        and rownum = 1),                     
                                                    (select 0.8 * case K.PRICETYPE when 1 then nvl(Q.SET_PRICE_PERCENT2,Q.SET_PRICE_PERCENT3) else nvl(Q.SET_PRICE2,Q.SET_PRICE3) end                
                                                       from GK_QUOT Q                     
                                                      where Q.CODECB = DS.CDKDSH                     
                                                        and Q.DTQUOT = trunc(nvl(DS.VBR_DTCONTR,DS.DD))                     
                                                        and Q.SOURCE = 3                     
                                                        and case K.PRICETYPE when 1 then nvl(Q.SET_PRICE_PERCENT2,Q.SET_PRICE_PERCENT3) else nvl(Q.SET_PRICE2,Q.SET_PRICE3) end > 0), 0)                
                                           * case when K.PRICETYPE = 1 then dbo.S_GKO_FNOMINAL(DS.CDKDSH,                     
                                                                                            nvl((select min(DTNMMAT) - 1                     
                                                                                                   from GK_VALUE                
                                                                                                  where CODECB = DS.CDKDSH                     
                                                                                                    and nvl(DTNMMAT_REAL,DTNMMAT) >= trunc(DS.DD)),                     
                                                                                                 trunc(DS.DD)))                     
                                                  else 1 end                
                                           * dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.DD)                
                                           * DS.AMSH                
                                           / 100                
                                    end, 2) as "MIN_PRICE_R",                
                                    case                
                                      when   (TS.CODEOFSHARE = 'BON1' and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',CB.ISSUER, DS.DD),0) = 9                
                                             or                
                                              TS.CODEOFSHARE in ('BON3','BON4'))                
                                          and nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',DS.CDKDSH,DS.DD),0) = 0                
                                        or                
                                          CB.VALUT <> CC.ID                
                                        or                
                                          TS.CODEOFSHARE not in ('BON1','BON2','BON3','BON4') then                
/*                                      when TS.CODEOFSHARE not in ('BON1','BON2') and nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',DS.CDKDSH,DS.DD),0) = 0                
                                           or CB.VALUT <> CC.ID then     */                
                                        case DS.DEALTYPE /*TS#45284*/    
                                          when 6 then round(round(nvl(DS.NKDSH,0)*dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.DD),2) * DS.AMSH,2)    
                                          else    
                                         round((nvl((select sum(V.SUMVK)                     
                                                 from GK_BUH_OFORML B                     
                                                 left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                                                 left join RKO_VP V on V.ID=T.DOCID                     
                                                where trunc(B.DD)=trunc(DS.DD)                     
                                                  and B.OPERATION = 10                     
                                                  and T.TYPE_OP = 20                     
                                                  and V.S_S>0                     
                                                  and T.CDKDSH = DS.CDKDSH                     
                                                  and DS.DEALTYPE = 6                     
                                                  and V.NSK in (dbo.S_GKO_GET_LSID_FROM_BIND(102, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                                               ,dbo.S_GKO_GET_LSID_FROM_BIND(103, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID))                
                                                            ),0)                     
                                               + nvl(DS.NKD,0)) * dbo.S_COM_CURS(1, CB.VALUT, CC.ID, DS.DD),2)     
                                        end   
                                      else 0                     
                                    end as "NKD",                     
                                    /* pad 03.11.2016 Тут собираются комиссая самой продажи, а не списанная при продаже, т.е. именно с типом 1,2,3,4,7 ... */                     
                                    /* pad 03.11.2016 +/- Возможна ситуация, когда сделка одна, а зписей в реестре много, для старого реестра это не актуально */                     
                                    case nvl(DS.VALUEBLE_COM,0)       
                                      when 1 then 1 /* существенные */                     
                                      when 0 then -1 /* несущественные */                     
                                    end                     
                              /*      * (select sum(                     
                                              ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = REESTR_S.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(REESTR_S.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = REESTR_S.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(REESTR_S.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = REESTR_S.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(REESTR_S.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = REESTR_S.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(REESTR_S.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = REESTR_S.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(REESTR_S.TIMEDEAL)),0)),2))                     
                                                  )                     
                                         from GK_REESTR REESTR_S                     
                                        where REESTR_S.DEAL_ID = DS.ID                     
                                     )*/                     
                                /*    * (select sum(round(COMM_SUM_VALPAY * dbo.S_COM_CURS(1, X.COMM_VALUT, CC.ID, DS.DD),2))       
                                         from (select DCS.COMM_VALUT       
                                                     ,sum(round(COMM_SUM_VALPAY       
                                                                * 100       
                                                                / (100 + nvl(dbo.S_GETVAR(case COMM_TYPE       
                                                                                            when 1 then 's_gk_com_birga'       
                                                                                            when 2 then 's_gk_com_cliring'       
                                                                                            when 3 then 's_gk_com_ITS'       
                                                                                            when 4 then 's_gk_com_diler'       
                                                                                            when 7 then 's_gk_com_birga'       
                                                                                          end, DS.DD),0)),2)) COMM_SUM_VALPAY       
                                                 from GK_DEPODEAL_COMMS DCS       
                                                where DCS.VNESHID = DS.ID       
                                                group by DCS.COMM_VALUT) X       
                                       )   */       
                                    * (select sum(round(round(COMM_SUM_VALPAY * dbo.S_COM_CURS(1, DCS.COMM_VALUT, CC.ID, DCS.COMM_DATE), 2)/*DS.DD*/       
                                                        * 100       
                                                        / (100 + nvl(dbo.S_GETVAR(case COMM_TYPE       
                                                                                    when 1 then 's_gk_com_birga'       
                                                                                    when 2 then 's_gk_com_cliring'       
                                                                                    when 3 then 's_gk_com_ITS'       
                                                                                    when 4 then 's_gk_com_diler'       
                                                                                    when 7 then 's_gk_com_birga'       
                                                                                  end, DS.DD),0)),2)) COMM_SUM_VALPAY       
                                         from GK_DEPODEAL_COMMS DCS       
                                        where DCS.VNESHID = DS.ID       
                                       )       
                                       as "CM",                     
                                    /*nvl(DS.DEALSUM, 0) + nvl(DS.NKDSH, 0 ) * nvl(DS.AMSH, 0) as "SMD",                     
                                    nvl(to_number(dbo.S_GETVAR('s_gk_proc_valueble_coms', DS.DD)), 0) as "V_PERCENT",   */                     
                                    1003117 as DOCOID_REALIZ,                     
                                    DS.ID as "DEAL_ID",                     
                                    DS.PORTFEL as "PORTFEL_ID",                     
                                    CB.PRNM,                     
/*                                    (select VP.SUMRUB              */       
                                       /*from GK_BUH_OFORML_T T                     
                                       left join RKO_VP VP on VP.ID = T.DOCID                     
                                      where T.DEALID = DS.ID              */       
/*                                        and VP.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Profit_NP', CB.ID, trunc(DS.DD))*/                
/*                                       from RKO_VP VP       
                                      where VP.DOC_OID = 1003117       
                                        and VP.DOC_ID = DS.ID       
                                        and DS.PORTFEL = 2       
                                        and VP.NSD = dbo.S_GKO_GET_LSID_FROM_BIND(407, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                        and VP.S_S > 0) as "SALDOP",                
                                    -1 * ( select VP.SUMRUB              */       
                                          /*from GK_BUH_OFORML_T T                     
                                            left join RKO_VP VP on VP.ID = T.DOCID                     
                                           where T.DEALID = DS.ID              */       
                                           /*and VP.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Loss_NP', CB.ID, trunc(DS.DD))*/                
/*                                             from RKO_VP VP       
                                            where VP.DOC_OID = 1003117       
                                              and VP.DOC_ID = DS.ID       
                                              and DS.PORTFEL = 2       
                                              and VP.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(408, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                              and VP.S_S > 0) as "SALDOO",         */       
                                   DS.DEALTYPE,                
                                   case DS.DEALKIND_SROK                
                                     when 3 then nvl(dbo.GN_GK_HISTORY('TSS_DEAL',DS.ID,trunc(DS.DD)),0)                
                                     else 0                
                                   end as "TSS_SELL",           
                                   case DS.PORTFEL           
                                     when 0 then           
                                      nvl((select sum(VP.SUMRUB)           
                                             from GK_BUH_OFORML_T T                     
                                             join RKO_VP VP on VP.ID = T.DOCID                     
                                            where T.DEALID = DS.ID           
                                              and VP.NSD = dbo.S_GKO_GET_LSID_FROM_BIND(401, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                              and VP.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(405, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                              and VP.S_S > 0),0)           
                                    - nvl((select sum(VP.SUMRUB)           
                                             from GK_BUH_OFORML_T T                     
                                             join RKO_VP VP on VP.ID = T.DOCID                     
                                            where T.DEALID = DS.ID           
                                              and VP.NSD = dbo.S_GKO_GET_LSID_FROM_BIND(406, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                              and VP.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(401, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)                
                                              and VP.S_S > 0),0)           
                                     else 0           
                                   end as SPIS_PEREOC_TP,       
                                   case DS.PORTFEL       
                                     when 2 then dbo.S_GKO_GET_LSID_FROM_BIND(407, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)       
                                     else null       
                                   end LS_DOB_CAP_PLUS,       
                                   case DS.PORTFEL       
                                     when 2 then dbo.S_GKO_GET_LSID_FROM_BIND(408, DS.PORTFEL, DS.CDKDSH, null, null, null, trunc(DS.DD), DS.ID)       
                                     else null       
                                   end LS_DOB_CAP_MINUS       
                                                     
                               from GK_DEPODEAL DS                     
                               left join GK_CDKDSH CB on CB.ID = DS.CDKDSH                     
                               left join GK_TYPEOFSHARE TS on TS.ID = CB.TPSHC                     
                               left join COM_CURRENCIES CC on CC.ID = dbo.S_GETVAR('NacValID', DS.DD)                     
                               left join GK_KINDOFSHARE K on K.ID = CB.SHKIND                     
                               left join GK_CDKDSH_OFERTA O on DS.DEALTYPE = 6         
                                                           and O.VNESHID = DS.CDKDSH         
                                                           and O.ENABLED = 1         
                                                           and nvl(O.OFERTA_REAL,dbo.GD_GK_HISTORY('OFERTA',O.ID,DS.DD)) = DS.DD         
                                                           and O.OFERTA_SUM > 0             
                               /*left join GK_REESTR RS on RS.DEAL_ID = DS.ID                     
                               left join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID  */                     
                              where (    (nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(DS.DD)),1) = 0                     
                                           and DS.DEALTYPE in (1,6)                     
                                           and vMODE = 1) /* необращающиеся */                     
                                      or (    nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(DS.DD)),1) = 1                     
                                           and DS.DEALTYPE in (1,6)                     
                                           and vMODE = 2) /* обращающиеся */                     
                                      or (    1 = 1 /* S_GK_NALOG_CB_REALIZ: параметр функции aMODE задан не верно (vMODE) */                     
                                           and not vMODE in (1, 2)))                     
                                and DS.S_S = 1000066                     
/*                                and exists (select null from GK_REESTR REESTR_S where REESTR_S.DEAL_ID = DS.ID and REESTR_S.DEALTYPE in (1,6)) отсекаем сделки без бух оформления */                     
                                and DS.DEALUSER = 0                     
                                and trunc(DS.DD) >= trunc(aFROM_DATE)                     
                                and trunc(DS.DD) <= trunc(aTO_DATE)                     
                                and DS.PORTFEL is not null                     
                                and ( v_DS_ID is null or v_DS_ID = DS.ID and v_DOCOID_REALIZ = 1003117 )                     
                                and v_sPurpose like '%'||DS.PORTFEL||'%'                     
                     
                              union                     
                     
                             select nvl(dbo.GS_GK_HISTORY('NAME_SH', CB.ID, NM.DTWR),CB.NAME) as "CDKDSH_NAME",                     
                                    CB.ID as "CDKDSH_ID",                     
                                    NM.DTWR as "DEAL_DATE",                     
                                    'ЧП от '||to_char(NM.DTWR,'dd.mm.yyyy') as "DEAL_NUM",                     
                                    (select sum(dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0))                     
                                       from GK_NMMATURITY NM1                     
                                       join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM1.ID                     
                                       join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
                                      where RB.TIMEDEAL >= v_DateCalcAD                     
                                        and NM1.CDKDSH = CB.ID                     
                                        and NM1.DTWR = NM.DTWR                     
                                        and NM1.S_S = 1000088                     
                                        and nvl(NM1.PORTFEL,-1) = nvl(NM.PORTFEL,-1)                     
                                        and ( v_DS_ID is null or v_DS_ID = NM1.ID and v_DOCOID_REALIZ = 1003262 )) as "CDKDSH_COUNT",                     
                                    case NM.PORTFEL                     
                                      when 0 then 'ТП'                     
                                      when 1 then 'ИП'                     
                                      when 2 then 'НП'                     
                                    end as "PORTFEL",                     
                                    case                     
                                      when CB.VALUT <> CC.ID then                     
                                        dbo.S_COM_CURS(1, CB.VALUT, CC.ID, NM.DTWR)                     
                                      else 0                     
                                    end as "KURS",                     
                                    round((select PAYSUM                     
                                             from GK_VALUE                     
                                            where CODECB = CB.ID                     
                                              and nvl(DTNMMAT_REAL,DTNMMAT) = trunc(NM.DTWR))                     
                                          * (select sum(dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0))                     
                                               from GK_NMMATURITY NM1                     
                                               join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM1.ID                     
                                               join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
                                              where RB.TIMEDEAL >= v_DateCalcAD                
                                                and NM1.CDKDSH = CB.ID                
                                                and NM1.DTWR = NM.DTWR                
                                                and NM1.S_S = 1000088                
                                                and nvl(NM1.PORTFEL,-1) = nvl(NM.PORTFEL,-1)                
                                                and ( v_DS_ID is null or v_DS_ID = NM1.ID and v_DOCOID_REALIZ = 1003262 )),2) as "PRICE",                     
                                    0 as "MIN_PRICE_R",                
                                    case                
                                      when   (TS.CODEOFSHARE = 'BON1' and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',CB.ISSUER, NM.DTWR),0) = 9                
                                             or                 
                                              TS.CODEOFSHARE in ('BON3','BON4'))                
                                          and nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',CB.ID,NM.DTWR),0) = 0                
                                        or                
                                          CB.VALUT <> CC.ID                
                                        or                
                                          TS.CODEOFSHARE not in ('BON1','BON2','BON3','BON4') then                
/*                                      when TS.CODEOFSHARE not in ('BON1','BON2') and nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',CB.ID,NM.DTWR),0) = 0                     
                                           or CB.VALUT <> CC.ID then     */                
                                         round(round(nvl((select sum(V.SUMVK)                     
                                                 from GK_BUH_OFORML B                     
                                                 left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                                                 left join RKO_VP V on V.ID=T.DOCID                     
                                                where trunc(B.DD)=trunc(NM.DTWR)                     
                                                  and B.OPERATION = 10                     
                                                  and T.TYPE_OP = 20                     
                                                  and V.S_S>0                     
                                                  and T.CDKDSH = CB.ID                     
/*                                                  and V.NSK in (dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104,                     
                                                                                               case NM.PORTFEL                     
                                                                                                 when 0 then 's_ls_NKD_Out_KTP'                     
                                                                                                 when 1 then 's_ls_NKD_Out_KIP'                     
                                                                                                 when 2 then 's_ls_NKD_Out_NP'                     
                                                                                                 else ''                     
                                                                                               end, CB.ID, trunc(NM.DTWR))                     
                                                                ,dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104,                     
                                                                                               case NM.PORTFEL                     
                                                                                                 when 0 then 's_NKDNach_KTP'                     
                                                                                                 when 1 then 's_NKDNach_KIP'                     
                                                                                                 when 2 then 's_NKDNach_NP'                     
                                                                                                 else ''                     
                                                                                               end, CB.ID, trunc(NM.DTWR)))*/                
                                                 and V.NSK in (dbo.S_GKO_GET_LSID_FROM_BIND(102, NM.PORTFEL, CB.ID, null, null, null, trunc(NM.DTWR), null)                
                                                              ,dbo.S_GKO_GET_LSID_FROM_BIND(103, NM.PORTFEL, CB.ID, null, null, null, trunc(NM.DTWR), null))),0)                     
                                  /*     * кол-во купленных после 2015 года                
                                         / кол-во всех (сумма в проводке - на все ц/б, нас интересует только на купленные после 2015 года)*/                
                                              * (select sum(dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0))                
                                                   from GK_NMMATURITY NM1                
                                                   join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM1.ID                
                                                   join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                
                                                  where RB.TIMEDEAL >= v_DateCalcAD                
                                                    and NM1.CDKDSH = CB.ID                
                                                    and NM1.DTWR = NM.DTWR                
                                                    and NM1.S_S = 1000088                
                                                    and nvl(NM1.PORTFEL,-1) = nvl(NM.PORTFEL,-1)                
                                                    and ( v_DS_ID is null or v_DS_ID = NM1.ID and v_DOCOID_REALIZ = 1003262 ))                
                                              / (select sum(dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0))                
                                                   from GK_NMMATURITY NM1                
                                                   join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM1.ID                
                                                  where NM1.CDKDSH = CB.ID                
                                                    and NM1.DTWR = NM.DTWR                
                                                    and NM1.S_S = 1000088                
                                                    and nvl(NM1.PORTFEL,-1) = nvl(NM.PORTFEL,-1)                
                                                    and ( v_DS_ID is null or v_DS_ID = NM1.ID and v_DOCOID_REALIZ = 1003262 ))                
                                              ,2)                
                                             *dbo.S_COM_CURS(1, CB.VALUT, CC.ID, NM.DTWR),2)                     
                                      else 0                     
                                    end as "NKD",                     
                                    /* pad 03.11.2016 Тут собираются комиссая самой продажи, а не списанная при продаже, т.е. именно с типом 1,2,3,4,7 ... */                     
                                    /* pad 03.11.2016 +/- Возможна ситуация, когда сделка одна, а зписей в реестре много, для старого реестра это не актуально */                     
                                    0 as "CM",                     
                                    /*nvl(DS.DEALSUM, 0) + nvl(DS.NKDSH, 0 ) * nvl(DS.AMSH, 0) as "SMD",                     
                                    nvl(to_number(dbo.S_GETVAR('s_gk_proc_valueble_coms', NM.DTWR)), 0) as "V_PERCENT",   */                     
                                    1003262 as DOCOID_REALIZ,                     
                                    NM.ID as "DEAL_ID",                     
                                    NM.PORTFEL as "PORTFEL_ID",                     
                                    1 as PRNM,                     
/*                                    null as "SALDOP",                     
                                    null as "SALDOO",              */       
                                    6 as DEALTYPE,                     
                                    0 as "TSS_SELL",           
                                    null SPIS_PEREOC_TP,       
                                    null LS_DOB_CAP_PLUS,       
                                    null LS_DOB_CAP_MINUS       
                               from GK_NMMATURITY NM                     
                               join GK_CDKDSH CB on CB.ID = NM.CDKDSH                     
                               join GK_TYPEOFSHARE TS on TS.ID = CB.TPSHC                     
                               join COM_CURRENCIES CC on CC.ID = dbo.S_GETVAR('NacValID', NM.DTWR)                     
/*                               join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM.ID                     
                               join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID and RB.TIMEDEAL > v_DateCalcAD  */                     
                              where (    (nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(NM.DTWR)),1) = 0 and vMODE = 1)                     
                                      or (nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(NM.DTWR)),1) = 1 and vMODE = 2)                     
                                      or (vMODE not in (1, 2)))/* S_GK_NALOG_CB_REALIZ: параметр функции aMODE задан не верно (vMODE) */                     
                                and NM.S_S = 1000088                     
/*                                and exists (select null from GK_REESTR REESTR_S where REESTR_S.DEAL_ID = DS.ID and REESTR_S.DEALTYPE in (1,6)) отсекаем сделки без бух оформления */                     
                                /*and NM.CONTRACT_ID is null*/                     
                                and trunc(NM.DTWR) >= trunc(aFROM_DATE)                     
                                and trunc(NM.DTWR) <= trunc(aTO_DATE)                     
                                and NM.PORTFEL is not null                     
                                and ( v_DS_ID is null or v_DS_ID = NM.ID and v_DOCOID_REALIZ = 1003262 )                     
                                and v_sPurpose like '%'||NM.PORTFEL||'%'                     
                                and exists (select null                   
                                              from GK_REESTR RS                   
                                              join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                   
                                             where RS.DOCOID = 1003262                   
                                               and RS.DOCID = NM.ID                   
                                               and trunc(RB.TIMEDEAL) >= v_DateCalcAD)                   
                              group by CB.ID, NM.DTWR, CB.NAME, CB.ISSUER, NM.PORTFEL, CB.VALUT, CC.ID, TS.CODEOFSHARE,NM.ID                     
                                ) A/*) B              */       
                      order by A.CDKDSH_NAME, A.DEAL_DATE)                     
    loop                     
      vID_NALOG := MainID.NextVal;                     
      insert into GK_NALOG_CB_REALIZ1 (ID, CDKDSH_NAME, DEAL_DATE, DEAL_NUM, CDKDSH_COUNT, PORTFEL, KURS, PRICE, MIN_PRICE_R, NKD, CM, /*SMD, V_PERCENT, */MIN_PRICE, MIN_DEVIATION, MD, SALDO_P, CDKDSH_ID, DEAL_ID, PORTFEL_ID,TSS_SELL,DOCOID_REALIZ, SPIS_PEREOC_TP, SYS_DATE)           
           values (vID_NALOG, qSELL.CDKDSH_NAME, qSELL.DEAL_DATE, qSELL.DEAL_NUM, qSELL.CDKDSH_COUNT, qSELL.PORTFEL, qSELL.KURS, qSELL.PRICE, qSELL.MIN_PRICE_R, qSELL.NKD, qSELL.CM, /*qSELL.SMD, qSELL.V_PERCENT, */qSELL.MIN_PRICE, qSELL.MIN_DEVIATION, vMODE, qSELL.SALDO, qSELL.CDKDSH_ID, qSELL.DEAL_ID, qSELL.PORTFEL_ID, qSELL.TSS_SELL, qSELL.DOCOID_REALIZ, qSELL.SPIS_PEREOC_TP, sysdate);           
      vALL_ZATRATI:=0;                     
      /* Заполнение таблицы по покупке (табличная часть таблицы по продаже) */                     
      for qBUY in (select B.*,                     
                             case                     
                               when B.PRICE > B.MAX_PRICE then B.PRICE - B.MAX_PRICE                     
                               else 0                     
                             end as "MAX_DEVIATION"                     
                    from (select A.*,                     
                                 case                     
                                   when (A.MAX_PRICE_R > A.PRICE) or (A.MAX_PRICE_R = 0) then A.PRICE                     
                                   else A.MAX_PRICE_R                
                                 end as "MAX_PRICE"                     
                            from (select XXX.*, XXX.PRICER as "PRICE"                     
                                        ,round(XXX.TSS_BUY * XXX.AMSH_REST / XXX.TSS_AMSH_BUY, 2)                     
                                         - round(XXX.TSS_BUY * (XXX.AMSH_REST - XXX.CDKDSH_COUNT) / XXX.TSS_AMSH_BUY, 2) as "TSS_PACKET"                     
/*                                         case XXX.PRNM                     
                                           when 1 then                     
                                             dbo.S_GKO_CALC_BC(XXX.PACK_ID, XXX.CDKDSH_COUNT)                     
                                           else XXX.PRICER                     
                                         end as "PRICE"                     */                     
                                    from (select /*DS.AMSH as "CDKDSH_COUNT",         */                     
                                                 case                     
                                                   when CB.VALUT <> NAC_VAL.ID then                     
                                                     dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))                     
                                                   else 0                     
                                                 end as "KURS",                     
                                                 dbo.S_COM_CURS(1, (select IDVAL from RKO_WORK_VALUTS where ID = DB.CDVALPAY), NAC_VAL.ID, trunc(RB.TIMEDEAL)) as "CURS_VALPAY",                     
                                                 RS.AMSHALL as "CDKDSH_COUNT",                     
                                                 /*0**/    
                                                 case                     
                                                   when trunc(DB.DD) <= CB.DATE_RAZM                     
                                                        and (select nvl(ORCBSIGN,0) from GK_TRADEMARKETS where ID=DB.CONTRAGENT) = 1                     
                                                        and DB.DEALKIND_SROK = 1                
                                                        and CB.PRNM in (0,1)                
                                                        and not (CB.VALUT = dbo.S_GETVAR('NacValID',DB.DD) and TS.CODEOFSHARE = 'BON1')                
                                                     then case K.PRICETYPE                
                                                            when 1 then 100                
                                                            else 0                
                                                          end /*nav141006 если ц/б куплена на размещении, то максимальная цена приобретения равна номиналу*/                
                                                   else                     
                                                     coalesce((select case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)                     
                                                                        when 0 then 1.2 * nvl(nullif(Q.LAST_PRICE,0), Q.PRICE_NEOBR) /* необращающиеся */                     
                                                                        when 1 then Q.MAXPRICE /* обращающиеся */                     
                                                                      end                     
                                                                 from GK_QUOT Q                     
                                                                where Q.CODECB = case                     
                                                                                   when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                
                                                                                     nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                
                                                                                   else RB.CDKDSH_ID                     
                                                                                 end                     
                                                                   and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)                     
                                                                         when 0 then coalesce(nullif(Q.LAST_PRICE,0), Q.PRICE_NEOBR,0)                     
                                                                         when 1 then nvl(Q.MAXPRICE,0)                     
                                                                       end > 0                     
                                                                   and nvl(Q.SOURCE,0) <> 3                     
                                                                   and trunc(Q.DTQUOT) =                     
                                                                         (select max(trunc(A.DTQUOT))                     
                                                                            from GK_QUOT A                     
                                                                           where A.CODECB = case                     
                                                                                              when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                                                nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                              else RB.CDKDSH_ID                     
                                                                                            end                     
                                                                             and A.DTQUOT <= trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL))                     
                                                                             and A.DTQUOT >= ADD_MONTHS(trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL)),-3)                     
                                                                             and nvl(A.SOURCE,0) <> 3                     
                                                                             and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)                     
                                                                                   when 0 then coalesce(nullif(A.LAST_PRICE,0), A.PRICE_NEOBR,0)                     
                                                                                   when 1 then nvl(A.MAXPRICE,0)                
                                                                                 end > 0)                
                                                                  and rownum = 1),                
                                                              (select 1.2 * case K.PRICETYPE when 1 then nvl(Q.SET_PRICE_PERCENT2,Q.SET_PRICE_PERCENT3) else nvl(Q.SET_PRICE2,Q.SET_PRICE3) end                
                                                                 from GK_QUOT Q                
                                                                where Q.CODECB = case                
                                                                                   when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                
                                                                                     nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                
                                                                                   else RB.CDKDSH_ID                
                                                                                 end                
                                                                  and Q.DTQUOT = trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL))                
                                                                  and Q.SOURCE = 3                
                                                                  and case K.PRICETYPE when 1 then nvl(Q.SET_PRICE_PERCENT2,Q.SET_PRICE_PERCENT3) else nvl(Q.SET_PRICE2,Q.SET_PRICE3) end > 0), 0)                
                                                 end                
                                                   * case K.PRICETYPE when 1 then dbo.S_GKO_FNOMINAL(case                
                                                                                                       when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                
                                                                                                         nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                                       else RB.CDKDSH_ID                
                                                                                                     end, trunc(RB.TIMEDEAL)) else 1 end                
                                                   * RS.AMSHALL /* nav * qSELL.CDKDSH_COUNT*//*nav DB.AMSH*/                
                                                   * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))                
                                                   * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                
                                                   / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end                
                                                   / 100 as "MAX_PRICE_R",                     
/*                                                 round(RB.AMSHALL * RB.PKDSH,2) as "NKD",    */                     
/*case v_NewVersion when 0 then null else*/      RS.PKDUPL_NALOG as "NKD",/*end as "NKD",*/           
                                                               
                                                 round(RB.PRICE                     
                                                       * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                       / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end,11) as "RB_PRICE",                     
                                                 round(dbo.S_GKO_FNOMINAL(case                     
                                                                      when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                        nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                      else RB.CDKDSH_ID                     
                                                                    end,trunc(RB.TIMEDEAL))                     
                                                       * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                       / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end,11) as "NOM_BUY",                     
                                                 /* pad 03.11.2016 Брать AMSH_BUY_TSS для PRICE можно только из сделки, т.к. при переходе на новый реестр данные могли объединить */                     
                                                 /* pad 03.11.2016 Или необходимо зачитывать TSS_AMSH_BUY как-то иначе */                     
                                                 nvl(DB.AMSH,RB.AMSHALL) as "TSS_AMSH_BUY",                     
                                                 RB.AMSHALL as "AMSH_BUY",                     
                                                 RB.ID as "REESTR_ID",                     
/*                                                 RS.ID as "PACK_ID",   */                     
/*                                                 OS.ID as "DEAL_ID",   */                     
                                                 RB.CDKDSH_ID as "CDKDSH_ID",                     
                                                 RB.TIMEDEAL as "DD",                     
                                                 DS.ID as "XXX_ID",                     
                                                 TS.CODEOFSHARE AS "COS",                     
                                                 DB.ID as "DB_ID",                     
                                                 RS.ID as "RS_ID",                     
                                                 /*(dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, DS.DD)                     
                                                  - dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, RB.TIMEDEAL))                     
                                                 * */                     
                                                 round(RB.PRICE * RS.AMSHALL                     
                                                       * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                       / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end, 2) as "VAL_PEREOC",                     
                                                 round(round(RB.PRICE * RS.AMSHALL                     
                                                             * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                             / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end,2)                     
                                                       * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL)),2) as "PRICE_BAL",                     
/*case v_NewVersion when 0 then                  round(round(nvl(case                     
                                                                   when DB.PRICETYPE = 0 then DB.SHPRICE * 100                     
                                                                   when DB.PRICETYPE = 1 then                     
                                                                     DB.SHPRICE * dbo.S_GKO_FNOMINAL(DB.CDKDSH,trunc(RB.TIMEDEAL))                     
                                                                 end, RB.PRICE * 100)                     
                                                             * RS.AMSHALL                     
                                                             * case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                             / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end                     
                                                             / 100,2) // nav qSELL.CDKDSH_COUNT * ///           
                                                         * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL)),2)*/ /*as "PRICER", *//*nav DB.DEALSUM*/              
/*else*/                                         RS.TELO_NALOG as "PRICER",/*end as "PRICER",*/           
                                                 CB.PRNM as "PRNM",                     
/*                                                 round( */                    
              /* TODO pad 15.11.2016 Нужно как-то учесть частичное погашение наминала. См. nсс2, бумага 524250150. Пока по страрому.                     
                                              ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 21), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 22), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 23), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 24), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 27), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
              т.к. при частичном погашении тоже может списываться комиссия, т.е. она не будет учтена в регистре при продаже, возможно стоит собирать иначе */                     
/*                                            ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
//                                                        * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))///        
                                                        * dbo.S_COM_CURS(1, (select IDVAL from RKO_WORK_VALUTS where ID = DB.CDVALPAY), NAC_VAL.ID, trunc(RB.TIMEDEAL))        
                                                         ,2) */       
                                              (select sum(round(round(COMM_SUM * dbo.S_COM_CURS(1, X.COMM_VALUT, NAC_VAL.ID, X.COMM_DATE),2)/*RB.TIMEDEAL*/       
                                                                * 100       
                                                                / (100 + nvl(dbo.S_GETVAR(case X.COMM_TYPE       
                                                                                            when 1 then 's_gk_com_birga'       
                                                                                            when 2 then 's_gk_com_cliring'       
                                                                                            when 3 then 's_gk_com_ITS'       
                                                                                            when 4 then 's_gk_com_diler'       
                                                                                            when 7 then 's_gk_com_birga'       
                                                                                          end,RB.TIMEDEAL),0)),2))       
                                                 from (select sum(RCB.COMM_SUM) COMM_SUM, RCB.COMM_VALUT, RCB.COMM_TYPE, RCB.COMM_DATE       
                                                         from GK_REESTR_COMMS RCB       
                                                        where RCB.VNESHID = RB.ID       
                                                        group by RCB.COMM_VALUT, RCB.COMM_TYPE, RCB.COMM_DATE) X       
                                              )       
                                                  as "CM",                     
                                                 nvl(RB.IS_VALUEBLE,0) as "IS_VALUEBLE",                     
                                                 case DB.DEALKIND_SROK                
                                                   when 3 then nvl(dbo.GN_GK_HISTORY('TSS_DEAL',DB.ID,trunc(DB.DD)),0)                
                                                   else 0                
                                                 end as "TSS_BUY",                     
                                                 RB.AMSHALL                     
                                                 - nvl((select sum(A.AMSHALL)                     
                                                          from GK_REESTR A                     
                                                         where A.TOPPARENT_ID = RB.ID                     
                                                           and A.DEALTYPE = 1                     
/*                                                           and (A.DTCLOSE is null or A.DTCLOSE > trunc(aTO_DATE)) */                     
                                                           and (trunc(A.TIMEDEAL) < trunc(RS.TIMEDEAL)                     
                                                                or trunc(A.TIMEDEAL) = trunc(RS.TIMEDEAL) and A.ID < RS.ID)                     
                                                           and nvl(DOCOID,0) <> 1004904                     
/*                                                           and not exists                     
                                                             (select ID                     
                                                                from GK_DEPOORDER B                     
                                                               where ID = A.ORDER_ID                     
                                                                 and (B.DOCOID = 1004904                     
                                                                      or exists (select ID                     
                                                                                   from GK_DEPODEAL                     
                                                                                  where ID = B.DEAL_ID                     
                                                                                    and DOCOID = 1004563)))*/      
                                                                                ),0) as "AMSH_REST",                     
                                                 (select nvl(sum(A.AMSHALL),0)                     
                                                    from GK_REESTR A                     
                                                   where A.DEAL_ID = RS.DEAL_ID      
/*                                                   where A.ORDER_ID = RS.ORDER_ID               */      
/*                                                     and (A.DTCLOSE is null or A.DTCLOSE > trunc(aTO_DATE)) */                     
                                                     and A.TOPPARENT_ID = RS.TOPPARENT_ID                     
                                                     and not exists (select *                     
                                                                       from GK_REESTR                     
                                                                      where DEAL_ID = RS.DEAL_ID                     
/*                                                                      where ORDER_ID = RS.ORDER_ID               */      
/*                                                                        and (DTCLOSE is null or DTCLOSE > trunc(aTO_DATE))*/                     
                                                                        and TOPPARENT_ID = RS.TOPPARENT_ID                     
                                                                        and ID > RS.ID)) as AMSH_SELL_WITHOUT_DROBL, /*НКЦ декабрь 2014, одной сделкой продавали полностью пакеты, которые дробились */                     
/*                     
Кол-во ц/б пакета на балансе после реализации = (select RB.AMSHALL - ).....                     
Плюс надо к цене покупки прибавить существенные затраты на 1 ц/б                     
                     
round(                     
  Сумма по всем выбывающим пакетам {                     
     ((round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                      * (Кол-во ц/б пакета на балансе после реализации + RS.AMSHALL),2)                     
        - (round(                     
                round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                              * (Кол-во ц/б пакета на балансе после реализации + RS.AMSHALL),2)                     
                 * nvl(dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,(select min(DTNMMAT) from GK_VALUE where DTNMMAT > RS.TIMEDEAL and CODECB = CB.ID)),0) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2)                     
            + round(                     
                     (round(                     
                           round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                                          * (Кол-во ц/б пакета на балансе после реализации + RS.AMSHALL),2)                     
                           * dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RS.TIMEDEAL) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2)                     
                      - round(                     
                            round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                                          * (Кол-во ц/б пакета на балансе после реализации + RS.AMSHALL),2)                     
                                  * nvl(dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,(select min(DTNMMAT) from GK_VALUE where DTNMMAT > RS.TIMEDEAL and CODECB = CB.ID)),0) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2))                     
                   * (nvl((select min(DTNMMAT) from GK_VALUE where CODECB = CB.ID and DTNMMAT > RS.TIMEDEAL),CB.DTDN) - trunc(RS.TIMEDEAL))                     
                   /  (nvl((select min(DTNMMAT) from GK_VALUE where CODECB = CB.ID and DTNMMAT > RS.TIMEDEAL),CB.DTDN) - max(Дата предыдущего погашения номинала, дата начала учёта премии по пакету)),2)))                     
        -                     
      (round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                      * Кол-во ц/б пакета на балансе после реализации,2)                     
        - (round(                     
                round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                              * Кол-во ц/б пакета на балансе после реализации,2)                     
                 * nvl(dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,(select min(DTNMMAT) from GK_VALUE where DTNMMAT > RS.TIMEDEAL and CODECB = CB.ID)),0) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2)                     
            + round(                     
                     (round(                     
                           round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                                          * Кол-во ц/б пакета на балансе после реализации,2)                     
                           * dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RS.TIMEDEAL) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2)                     
                      - round(                     
                            round((RB.PRICE - dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL))                     
                                                                          * Кол-во ц/б пакета на балансе после реализации,2)                     
                                  * nvl(dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,(select min(DTNMMAT) from GK_VALUE where DTNMMAT > RS.TIMEDEAL and CODECB = CB.ID)),0) / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,case when dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) > RB.TIMEDEAL then dbo.S_GETVARD('s_gk_date_uchet_premia',RS.TIMEDEAL) else RB.TIMEDEAL end),2))                     
                   * (nvl((select min(DTNMMAT) from GK_VALUE where CODECB = CB.ID and DTNMMAT > RS.TIMEDEAL),CB.DTDN) - trunc(RS.TIMEDEAL))                     
                   /  (nvl((select min(DTNMMAT) from GK_VALUE where CODECB = CB.ID and DTNMMAT > RS.TIMEDEAL),CB.DTDN) - max(Дата предыдущего погашения номинала, дата начала учёта премии по пакету)),2))))                     
 } * dbo.S_COM_CURS(1,CB.VALUT,dbo.S_GETVAR('NacValID',RS.TIMEDEAL),RS.TIMEDEAL),2)*/                     
                     
                     
                                                /*round((*/                     
                                                  round(case                     
                                                             /* не по всем бумагам премия выделяется впринципе */                     
                                                             when not exists (select null from GK_KINDOFSHARE where ID = CB.SHKIND and SHCLASS in (0,3,15,20)) then 0                     
                                                             /* до 01.01.2016 выделения премии как таковой не было */                     
                                                             when dbo.S_GETVARD('s_gk_date_uchet_premia',trunc(RS.TIMEDEAL)) > trunc(RS.TIMEDEAL) then 0                     
                                                             /* премию по покупкам после 01.01.2016 высчитываем исходя из суммы премии при покупке */                     
                                                             when trunc(RB.TIMEDEAL) >= dbo.S_GETVARD('s_gk_date_uchet_premia',trunc(RS.TIMEDEAL))                     
                                                               then round( nvl(RB.SUM_PREMIA,0) * RS.AMSHALL / RB.AMSHALL, 2)                     
                                                             /* премию по покупкам до 01.01.2016 высчитываем исходя из общей расчетной на выбывающие бумаги (если премия есть) */                     
                                                             when round((RB.SUM_BS + nvl(RB.SUM_PREMIA, 0)) * RS.AMSHALL / RB.AMSHALL, 2)                     
                                                                  + case                     
                                                                      when RB.IS_VALUEBLE = 1 and cast(nvl(dbo.S_GETVAR('s_gk_val_zatr_in_premia',RB.TIMEDEAL),'0') as number) = 1                     
                                                                        then round(                     
                                              ( round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                                                                         * RS.AMSHALL / RB.AMSHALL, 2)                     
                                                                        else 0                     
                                                                    end                     
                                                                  > round( dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL) * RS.AMSHALL, 2)                     
                                                               then round((RB.SUM_BS + nvl(RB.SUM_PREMIA, 0)) * RS.AMSHALL / RB.AMSHALL, 2)                     
                                                                    + case                     
                                                                        when RB.IS_VALUEBLE = 1 and cast(nvl(dbo.S_GETVAR('s_gk_val_zatr_in_premia',RB.TIMEDEAL),'0') as number) = 1                     
                                                                          then round(                     
                                              ( round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_VALNOM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                                                                           * RS.AMSHALL / RB.AMSHALL, 2)                     
                                                                          else 0                     
                                                                      end                     
                                                                    - round( dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL) * RS.AMSHALL, 2)                     
                                                           else 0                     
                                                      end                     
                                                    - nvl(RS.SUM_PREMIA,0)                     
                                                         /** case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RS.TIMEDEAL - (DS.DEALTYPE-1)/5) else 1 end                     
                                                         / case when RB.TIMEDEAL >= v_DateCalcAD and CB.PRNM = 1 then dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL) else 1 end*/,2)                    
                                                      /*) * dbo.S_COM_CURS(1,CB.VALUT,dbo.S_GETVAR('NacValID',trunc(RS.TIMEDEAL)),trunc(RS.TIMEDEAL))                     
                                                   ,2)*/ as "PREMIA"                     
                                            from GK_DEPODEAL DS                     
                                            left join GK_CDKDSH CB on CB.ID = DS.CDKDSH                     
                                            left join GK_REESTR RS on RS.DEAL_ID = DS.ID                     
                                            left join GK_REESTR RB on RB.ID = RS.BUYPARENT_ID                     
                                            left join GK_DEPODEAL DB on DB.ID = RB.DEAL_ID                     
                                                                            /*    (OB.DEAL_ID is not null and DB.ID = OB.DEAL_ID)                     
                                                                             or (OB.DEAL_ID is null                     
                                                                                 and DB.CDKDSH = case DS.CDKDSH                     
                                                                                                           when 294092178 then 34742814 СЗ Телеком, 5 -> Ростелеком, 13                     
                                                                                                           when 294001995 then 34361564 СЗ Телеком, 4 -> Ростелеком, 12                     
                                                                                                           when 294150537 then 33261317 Центртелеком 5 -> Ростелеком, 10                     
                                                                                                           when 293690500 then 22564642 Сибирьтелеком 8 -> Ростелеком, 04                     
                                                                                                         end                     
                                                                                 and trunc(DB.DD) = trunc(RB.TIMEDEAL)                     
                                                                                 and DB.AMSH = RB.AMSHALL                     
                                                                                 and DB.DEALTYPE = 0                     
                                                                                 and case DB.PRICETYPE                     
                                                                                       when 0 then DB.SHPRICE                     
                                                                                       else DB.SHPRICE * dbo.S_GKO_FNOMINAL(DB.CDKDSH,trunc(DB.DD)) / 100                     
                                                                                     end = RB.PRICE                     
                                                                                 and not exists                     
                                                                                   (select ID from GK_DEPODEAL                     
                                                                                     where CDKDSH = DB.CDKDSH and trunc(DD) = trunc(RB.TIMEDEAL)                     
                                                                                       and AMSH = RB.AMSHALL and DEALTYPE = 0                     
                                                                                       and case PRICETYPE                     
                                                                                             when 0 then SHPRICE                     
                                                                                             else SHPRICE * dbo.S_GKO_FNOMINAL(CDKDSH,trunc(DD)) / 100                     
                                                                                           end = RB.PRICE                     
                                                                                       and ID > DB.ID)) */                     
                                            left join COM_CURRENCIES NAC_VAL on NAC_VAL.ID = dbo.S_GETVAR('NacValID', trunc(RB.TIMEDEAL))                     
                                            left join GK_TYPEOFSHARE TS on TS.ID = CB.TPSHC                     
                                            left join GK_KINDOFSHARE K on K.ID = CB.SHKIND                
                                           where DS.ID = qSELL.DEAL_ID                     
                                             and qSELL.DOCOID_REALIZ = 1003117                     
                                             /* зачем это здесь? все условия уже проверили при отбире qSELL, здесь продажу отбирем по ID    
                                             and (    (   nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(DS.DD)),1) = 0                     
                                                        and DS.DEALTYPE in (1,6)                     
                                                        and vMODE = 1)    
                                                   or (   nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,trunc(DS.DD)),1) = 1                     
                                                        and DS.DEALTYPE in (1,6)                     
                                                        and vMODE = 2)    
                                                   or (     1 = 1     
                                                        and not vMODE in (1, 2)))                     
                                             and trunc(DS.DD) >= trunc(aFROM_DATE)                     
                                             and trunc(DS.DD) <= trunc(aTO_DATE)                     
                                             and DS.S_S = 1000066 */                    
                     
                     
                                             union                     
                     
                     
                                          select case                     
                                                   when CB.VALUT <> NAC_VAL.ID then                     
                                                     dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))                     
                                                   else 0                     
                                                 end as "KURS",                     
                                                 dbo.S_COM_CURS(1, (select IDVAL from RKO_WORK_VALUTS where ID = DB.CDVALPAY), NAC_VAL.ID, trunc(RB.TIMEDEAL)) as "CURS_VALPAY",                     
                                                 dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) as "CDKDSH_COUNT",                     
                                                 case                     
                                                   when trunc(DB.DD) <= CB.DATE_RAZM                     
                                                        and (select nvl(ORCBSIGN,0) from GK_TRADEMARKETS where ID=DB.CONTRAGENT) = 1                     
                                                        and DB.DEALKIND_SROK = 1                
                                                        and not (CB.VALUT = dbo.S_GETVAR('NacValID',DB.DD) and TS.CODEOFSHARE = 'BON1') then 100 /*nav141006 если ц/б куплена на размещении, то максимальная цена приобретения равна номиналу*/                     
                                                   else                     
                                                     coalesce((select case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)    
                                                                        when 0 then nvl(case K.PRICETYPE when 1 then Q.QUOTPERCENT else Q.QUOTABS end, Q.PRICE_NEOBR) /* необращающиеся */                     
                                                                        when 1 then Q.MAXPRICE /* обращающиеся */                     
                                                                      end                     
                                                                 from GK_QUOT Q                     
                                                                where Q.CODECB = case                     
                                                                                   when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                                     nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                   else RB.CDKDSH_ID                     
                                                                                 end                     
                                                                   and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)    
                                                                         when 0 then coalesce(nullif(case K.PRICETYPE when 1 then Q.QUOTPERCENT else Q.QUOTABS end,0), Q.PRICE_NEOBR,0)                     
                                                                         when 1 then nvl(Q.MAXPRICE,0)                     
                                                                       end > 0                     
                                                                   and nvl(Q.SOURCE,0) <> 3                     
                                                                   and trunc(Q.DTQUOT) =                     
                                                                         (select max(trunc(A.DTQUOT))                     
                                                                            from GK_QUOT A                     
                                                                           where A.CODECB = case                     
                                                                                              when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                                                nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                              else RB.CDKDSH_ID                     
                                                                                            end                     
                                                                             and A.DTQUOT <= trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL))                     
                                                                             and A.DTQUOT >= ADD_MONTHS(trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL)),-3)                     
                                                                             and nvl(A.SOURCE,0) <> 3                     
                                                                             and case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1)                     
                                                                                   when 0 then coalesce(nullif(case K.PRICETYPE when 1 then QUOTPERCENT else QUOTABS end,0), PRICE_NEOBR,0)                     
                                                                                   when 1 then nvl(MAXPRICE,0)                     
                                                                                 end > 0)                     
                                                                  and rownum = 1),                     
                                                              (select case K.PRICETYPE when 1 then coalesce(SET_PRICE_PERCENT2,SET_PRICE_PERCENT3,QUOTPERCENT)                     
                                                                                              else coalesce(SET_PRICE2,SET_PRICE3,QUOTABS) end                     
                                                                 from GK_QUOT                     
                                                                where CODECB = case                     
                                                                                 when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                                   nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                 else RB.CDKDSH_ID                     
                                                                               end                     
                                                                  and DTQUOT = trunc(coalesce(DB.VBR_DTCONTR,RB.TIMEDEAL))                     
                                                                  and SOURCE = 3                     
                                                                  and case K.PRICETYPE when 1 then coalesce(SET_PRICE_PERCENT2,SET_PRICE_PERCENT3,QUOTPERCENT)                     
                                                                                              else coalesce(SET_PRICE2,SET_PRICE3,QUOTABS) end > 0)    
                                                               * case nvl(dbo.GI_GK_HISTORY('OBR_ORCB',CB.ID,RB.TIMEDEAL),1) when 0 then 1 else 1.2 end, 0)                     
                                                 end                     
                                                   * case K.PRICETYPE when 0 then 100 else dbo.S_GKO_FNOMINAL(case                     
                                                                                                                when trunc(RB.TIMEDEAL) < to_date('01.04.2011','dd.mm.yyyy') then                     
                                                                                                                  nvl(CB.CB_ID_BEF_CONV,RB.CDKDSH_ID)                     
                                                                                                                else RB.CDKDSH_ID                     
                                                                                                              end, trunc(RB.TIMEDEAL)) end                     
                                                   * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) /* nav * qSELL.CDKDSH_COUNT*//*nav DB.AMSH*/                     
                                                   * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))                     
                                                   * V.PAYSUM / dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL)                     
                                                   / 100 as "MAX_PRICE_R",                     
/*                                                 round(RB.AMSHALL * RB.PKDSH,2) as "NKD",    */                     
/*case v_NewVersion when 0 then null else*/      /*RS.PKDUPL_NALOG as "NKD",*/ /*end as "NKD",*/           
                                                (select sum(RS1.PKDUPL_NALOG)          
                                                   from GK_REESTR RS1          
                                                  where RS1.DOCOID = 1003260          
                                                    and RS1.TIMEDEAL = RS.TIMEDEAL          
                                                    and nvl(RS1.REPOPARENT_ID,RS1.PARENT_ID) = nvl(RS.REPOPARENT_ID,RS.PARENT_ID)) as "NKD",          
                                                 round(RB.PRICE * V.PAYSUM / dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL),11) as "RB_PRICE",                     
                                                 V.PAYSUM as "NOM_BUY",                     
                                                 /* pad 03.11.2016 Брать AMSH_BUY_TSS для PRICE можно только из сделки, т.к. при переходе на новый реестр данные могли объединить */                     
                                                 /* pad 03.11.2016 Или необходимо зачитывать TSS_AMSH_BUY как-то иначе */                     
                                                 nvl(DB.AMSH,RB.AMSHALL) as "TSS_AMSH_BUY",                     
                                                 RB.AMSHALL as "AMSH_BUY",                     
                                                 RB.ID as "REESTR_ID",                     
/*                                                 RS.ID as "PACK_ID",   */                     
/*                                                 OS.ID as "DEAL_ID",   */                     
                                                 RB.CDKDSH_ID as "CDKDSH_ID",                     
                                                 RB.TIMEDEAL as "DD",                     
                                                 NM.ID as "XXX_ID",                     
                                                 TS.CODEOFSHARE AS "COS",                     
                                                 DB.ID as "DB_ID",                     
                                                 RS.ID as "RS_ID",                     
                                                 round(RB.PRICE                     
                                                       * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0)                     
                                                       * V.PAYSUM / dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL), 2) as "VAL_PEREOC",                     
                                                 round(round(RB.PRICE                     
                                                             * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0),2)                     
                                                             * V.PAYSUM / dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL)                     
                                                       * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL)),2) as "PRICE_BAL",                     
/*case v_NewVersion when 0 then                    round(round(nvl(case                     
                                                                   when DB.PRICETYPE = 0 then DB.SHPRICE * 100                     
                                                                   when DB.PRICETYPE = 1 then                     
                                                                     DB.SHPRICE * dbo.S_GKO_FNOMINAL(DB.CDKDSH,trunc(RB.TIMEDEAL))                     
                                                                 end, RB.PRICE * 100)                     
                                                             * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0)                     
                                                             * V.PAYSUM / dbo.S_GKO_FNOMINAL(CB.ID,RB.TIMEDEAL)                     
                                                             / 100,2)                     
                                                         * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL)),2) *//*as "PRICER",*/             
/*else*/                                         RS.TELO_NALOG as "PRICER", /*end as "PRICER",*/           
                                                 CB.PRNM as "PRNM",                     
/*                                                 round(              */       
              /* TODO pad 15.11.2016 Нужно как-то учесть частичное погашение наминала. См. nсс2, бумага 524250150. Пока по страрому.                     
                                              ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 21), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 22), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 23), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 24), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RS.ID and COMM_TYPE = 27), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
              т.к. при частичном погашении тоже может списываться комиссия, т.е. она не будет учтена в регистре при продаже, возможно стоит собирать иначе */                     
/*                                            ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
//                                                        * dbo.S_COM_CURS(1, CB.VALUT, NAC_VAL.ID, trunc(RB.TIMEDEAL))///       
                                                        * dbo.S_COM_CURS(1, (select IDVAL from RKO_WORK_VALUTS where ID = DB.CDVALPAY), NAC_VAL.ID, trunc(RB.TIMEDEAL))        
                                                        ,2)*/       
                                              (select sum(round(round(COMM_SUM * dbo.S_COM_CURS(1, X.COMM_VALUT, NAC_VAL.ID, X.COMM_DATE),2)/*RB.TIMEDEAL*/       
                                                                * 100       
                                                                / (100 + nvl(dbo.S_GETVAR(case X.COMM_TYPE       
                                                                                            when 1 then 's_gk_com_birga'       
                                                                                            when 2 then 's_gk_com_cliring'       
                                                                                            when 3 then 's_gk_com_ITS'       
                                                                                            when 4 then 's_gk_com_diler'       
                                                                                            when 7 then 's_gk_com_birga'       
                                                                                          end,RB.TIMEDEAL),0)),2))       
                                                 from (select sum(RCB.COMM_SUM) COMM_SUM, RCB.COMM_VALUT, RCB.COMM_TYPE, RCB.COMM_DATE       
                                                         from GK_REESTR_COMMS RCB       
                                                        where RCB.VNESHID = RB.ID       
                                                        group by RCB.COMM_VALUT, RCB.COMM_TYPE, RCB.COMM_DATE) X       
                                              )       
                                                   as "CM",                     
                                                 nvl(RB.IS_VALUEBLE,0) as "IS_VALUEBLE",                     
                                                 null/*nvl(dbo.GN_GK_HISTORY('TSS_DEAL',DB.ID,trunc(DB.DD)),0)*/ as "TSS_BUY",                     
                                                 RB.AMSHALL                     
                                                 - nvl((select sum(AMSHALL)                     
                                                          from GK_REESTR A                     
                                                         where TOPPARENT_ID = RB.ID                     
                                                           and DEALTYPE = 1                     
/*                                                           and (A.DTCLOSE is null or A.DTCLOSE > trunc(aTO_DATE)) */                     
                                                           and (trunc(TIMEDEAL) < trunc(RS.TIMEDEAL)                     
                                                                or trunc(TIMEDEAL) = trunc(RS.TIMEDEAL) and ID < RS.ID)                     
                                                           and nvl(DOCOID,0) <> 1004904                     
/*                                                           and not exists                     
                                                             (select ID                     
                                                                from GK_DEPOORDER B                     
                                                               where ID = A.ORDER_ID                     
                                                                 and (B.DOCOID = 1004904                     
                                                                      or exists (select ID                     
                                                                                   from GK_DEPODEAL                     
                                                                                  where ID = B.DEAL_ID                     
                                                                                    and DOCOID = 1004563)))*/      
                                                                                    ),0) as "AMSH_REST",                     
                                                 (select nvl(sum(A.AMSHALL),0)                     
                                                    from GK_REESTR A                     
                                                   where A.DOCOID = RS.DOCOID                     
                                                     and A.DOCID = RS.DOCID                     
                                                     and A.TOPPARENT_ID = RS.TOPPARENT_ID                     
                                                     and not exists (select null                     
                                                                       from GK_REESTR                     
                                                                      where DOCOID = RS.DOCOID                     
                                                                        and DOCID = RS.DOCID                     
/*                                                                      where ORDER_ID = RS.ORDER_ID               */      
                                                                        and TOPPARENT_ID = RS.TOPPARENT_ID                     
                                                                        and ID > RS.ID)) as AMSH_SELL_WITHOUT_DROBL, /*НКЦ декабрь 2014, одной сделкой продавали полностью пакеты, которые дробились */                     
                     
                                                /*round(                     
                                                case                     
                                                  when not exists (select null from GK_KINDOFSHARE where ID = CB.SHKIND and SHCLASS in (0,3,15,20)) then 0                     
                                                  when dbo.S_GETVARD('s_gk_date_uchet_premia',trunc(RS.TIMEDEAL)) > trunc(RS.TIMEDEAL) then 0                     
                                                  when trunc(RB.TIMEDEAL) >= dbo.S_GETVARD('s_gk_date_uchet_premia',trunc(RS.TIMEDEAL))                     
                                                       then                     
                                                            round( nvl(RB.SUM_PREMIA,0)                     
                                                                 * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) / RB.AMSHALL, 2)                     
                                                  when                     
                                                            round((RB.SUM_BS + nvl(RB.SUM_PREMIA, 0))                     
                                                                 * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) / RB.AMSHALL, 2)                     
                                                          + case                     
                                                              when RB.IS_VALUEBLE = 1 and cast(nvl(dbo.S_GETVAR('s_gk_val_zatr_in_premia',RB.TIMEDEAL),'0') as number) = 1                     
                                                                then round(                     
                                              ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                                                                           * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) / RB.AMSHALL, 2)                     
                                                                          else 0                     
                                                                      end                     
                                                                    > round( dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL) * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0), 2)                     
                                                                 then                     
                                                                      round((RB.SUM_BS + nvl(RB.SUM_PREMIA, 0))                     
                                                                           * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) / RB.AMSHALL, 2)                     
                                                                    + case                     
                                                                        when RB.IS_VALUEBLE = 1 and cast(nvl(dbo.S_GETVAR('s_gk_val_zatr_in_premia',RB.TIMEDEAL),'0') as number) = 1                     
                                                                          then round(                     
                                              ( round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                                                                           * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0) / RB.AMSHALL, 2)                     
                                                                          else 0                     
                                                                      end                     
                                                                    - round( dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL) * dbo.S_GKO_CALC_SUM_CB_REESTR(nvl(RS.REPOPARENT_ID,RS.PARENT_ID),NM.DTWR-1,0), 2)                     
                                                           else 0                     
                                                      end * V.PAYSUM / dbo.S_GKO_FNOMINAL(RB.CDKDSH_ID,RB.TIMEDEAL),2)*/null as "PREMIA"                     
                                            from GK_NMMATURITY NM                     
                                            left join GK_CDKDSH CB on CB.ID = NM.CDKDSH                     
                                            left join GK_REESTR RS on RS.DOCOID = 1003262 and RS.DOCID = NM.ID                     
                                            left join GK_REESTR RB on RB.ID = RS.BUYPARENT_ID                     
                                            left join GK_DEPODEAL DB on DB.ID = RB.DEAL_ID                     
                                            left join COM_CURRENCIES NAC_VAL on NAC_VAL.ID = dbo.S_GETVAR('NacValID', trunc(RB.TIMEDEAL))                     
                                            left join GK_TYPEOFSHARE TS on TS.ID = CB.TPSHC                     
                                            left join GK_KINDOFSHARE K on K.ID = CB.SHKIND                     
                                            left join GK_VALUE V on V.CODECB = CB.ID and trunc(nvl(DTNMMAT_REAL,DTNMMAT)) = trunc(NM.DTWR)                     
                                           where qSELL.DOCOID_REALIZ = 1003262                     
                                             and NM.ID = qSELL.DEAL_ID                     
                                             and RB.TIMEDEAL > v_DateCalcAD                     
                                             ) XXX) A) B)                     
      loop                     
        vID_TABLE := MainID.NextVal;                     
        vALL_CDKDSH := qSELL.CDKDSH_COUNT; /* количество ц/б из сделки продажи */                     
        /* Заполнение простых значений */                     
        insert into GK_NALOG_CB_REALIZ_TABLE1 (ID, VNESHID, CDKDSH_COUNT, KURS, PRICE, MAX_PRICE_R, MAX_PRICE, MAX_DEVIATION, VALUT, PRICE_BAL, DEAL_ID,CDKDSH_ID,TSS_PACKET,PREMIA,REESTR_ID, NKD, SYS_DATE)             
             values (vID_TABLE, vID_NALOG, qBUY.CDKDSH_COUNT, qBUY.KURS, qBUY.PRICE, qBUY.MAX_PRICE_R, qBUY.MAX_PRICE, qBUY.MAX_DEVIATION, qBUY.VAL_PEREOC, qBUY.PRICE_BAL, qBUY.DB_ID,qBUY.CDKDSH_ID,qBUY.TSS_PACKET,qBUY.PREMIA,qBUY.RS_ID, qBUY.NKD, sysdate);    
                              
          update GK_NALOG_CB_REALIZ_TABLE1                     
             set CM = round(nvl(qBUY.CM,0) * nvl(qBUY.AMSH_SELL_WITHOUT_DROBL,1) / nvl(qBUY.AMSH_BUY,1) * (qBUY.IS_VALUEBLE - 1)                  
                            * case when trunc(qBUY.DD) >= v_DateCalcAD and qBUY.PRNM = 1 then dbo.S_GKO_FNOMINAL(qSELL.CDKDSH_ID,qSELL.DEAL_DATE) else 1 end                  
                            / case when trunc(qBUY.DD) >= v_DateCalcAD and qBUY.PRNM = 1 then dbo.S_GKO_FNOMINAL(qSELL.CDKDSH_ID,qBUY.DD) else 1 end,2)                  
/*             set CM = nvl(qBUY.CM,0) * nvl(qBUY.CDKDSH_COUNT,1) / nvl(qBUY.AMSH_BUY,1) * (qBUY.IS_VALUEBLE - 1)*/                     
           where ID = vID_TABLE; /*для существенных затрат в случае полного погшения пересчитываем потом*/                     
/*if qSELL.DEAL_ID = 4392719582 then                 
  begin                 
    select '#ICM = '||to_char(qBUY.CM)                 
      into s_str                 
     from GK_NALOG_CB_REALIZ_TABLE1                 
    where ID = vID_TABLE;                 
  exception                 
    when no_data_found then                 
      s_str := '#IBeda';                 
  end;                 
  raise_application_error(-20202,s_str);                 
end if;*/                 
        vALL_ZATRATI := vALL_ZATRATI + nvl(qBUY.CM,0) * nvl(qBUY.CDKDSH_COUNT,1) / nvl(qBUY.AMSH_BUY,1);        
                     
        v_CURS:=nvl(nullif(qBUY.KURS, 0), 1);        
        v_CURS_VALPAY:=nvl(nullif(qBUY.CURS_VALPAY, 0), 1);        
        begin                     
          select nvl(sum(V.SUMVK),0)                     
            into vALL_NKD                     
            from GK_BUH_OFORML B                     
            left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
            left join RKO_VP V on V.ID = T.DOCID                     
           where B.DD = trunc(qSELL.DEAL_DATE)                     
             and B.OPERATION = 10                     
             and T.TYPE_OP = 20                     
             and V.S_S>0                     
/*             and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                   when 0 then 's_ls_NKD_Out_KTP'                     
                                                                   when 1 then 's_ls_NKD_Out_KIP'                     
                                                                   when 2 then 's_ls_NKD_Out_NP'                     
                                                                   else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
             and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(102, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
        exception                     
          when NO_DATA_FOUND then                     
            vALL_NKD := 0;                     
        end;                     
        if vALL_NKD = 0 then                     
          begin                     
            select nvl(sum(V.SUMVK),0)                     
              into vALL_NKD                     
              from GK_BUH_OFORML B                     
              left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
              left join RKO_VP V on V.ID = T.DOCID                     
             where B.DD = trunc(qSELL.DEAL_DATE)                     
               and B.OPERATION = 20                     
               and T.DEALID = qSELL.DEAL_ID                     
               /*and T.TYPE_OP = 50  */                     
               and V.S_S>0                     
/*               and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                     when 0 then 's_ls_NKD_Out_KTP'                     
                                                                     when 1 then 's_ls_NKD_Out_KIP'                     
                                                                     when 2 then 's_ls_NKD_Out_NP'                     
                                                                     else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
               and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(102, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
          exception                     
            when NO_DATA_FOUND then                     
              vALL_NKD := 0;                     
          end;                     
        end if;                     
/* pad Уже не актуально ...                     
        vALL_DISC := 0;                     
        begin                     
          select sum(nvl(DISCSPIS,-0.01))                     
            into vALL_DISC                     
            from GK_REESTR                     
           where DEAL_ID in (select ID from GK_DEPOORDER where DEAL_ID = qSELL.DEAL_ID);                     
        exception                     
          when NO_DATA_FOUND then                     
            vALL_DISC := - 1;                     
        end;                     
        if vALL_DISC <= 0 then                     
          dbo.S_GK_CALC_DISCSPIS_PACKET(qSELL.DEAL_ID);                     
        end if;               */                     
        begin                     
          select nvl(sum(V.SUMRUB),0)                     
            into vALL_DISC                     
            from GK_BUH_OFORML B                     
            left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
            left join RKO_VP V on V.ID = T.DOCID                     
           where B.DD = trunc(qSELL.DEAL_DATE)                     
             and B.OPERATION = 10                     
             and T.TYPE_OP in (30,40)                     
             and V.S_S>0                     
             and T.DEALID = qSELL.DEAL_ID                     
             and qSELL.DOCOID_REALIZ = 1003117                    
/*             and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                   when 0 then 's_DiscountKTP'                     
                                                                   when 1 then 's_DiscountKIP'                     
                                                                   when 2 then 's_DiscountNP'                     
                                                                   else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
             and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(104, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
        exception                     
          when NO_DATA_FOUND then                     
            vALL_DISC := 0;                     
        end;                     
        if vALL_DISC = 0 then                     
          begin                     
            select nvl(sum(V.SUMRUB),0)                     
              into vALL_DISC                     
              from GK_BUH_OFORML B                     
              left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
              left join RKO_VP V on V.ID = T.DOCID                     
             where B.DD = trunc(qSELL.DEAL_DATE)                     
               and B.OPERATION = 20                     
               and T.DEALID = qSELL.DEAL_ID                     
               and qSELL.DOCOID_REALIZ = 1003117                    
               and T.TYPE_OP in (30,50)         
               and V.S_S>0                     
/*               and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                     when 0 then 's_DiscountKTP'                     
                                                                     when 1 then 's_DiscountKIP'                     
                                                                     when 2 then 's_DiscountNP'                     
                                                                     else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
               and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(104, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
          exception                     
            when NO_DATA_FOUND then                     
              vALL_DISC := 0;                     
          end;                     
        end if;                     
        begin                     
          select nvl(sum(V.SUMVK),0)                     
            into vALL_ZATR                     
            from GK_BUH_OFORML B                     
            left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
            left join RKO_VP V on V.ID = T.DOCID                     
           where B.DD = trunc(qSELL.DEAL_DATE)                     
             and B.OPERATION = 10                     
             and T.TYPE_OP in (30,40)                     
             and V.S_S>0                     
             and T.DEALID = qSELL.DEAL_ID                     
/*             and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                   when 0 then 's_ls_ExpBuy_KTP'                     
                                                                   when 1 then 's_ls_ExpBuy_KIP'                     
                                                                   when 2 then 's_ls_ExpBuy_NP'                     
                                                                   else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
             and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(403, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
        exception                     
          when NO_DATA_FOUND then                     
            vALL_ZATR := 0;                     
        end;                     
        if vALL_ZATR = 0 then                     
          begin                     
            select nvl(sum(V.SUMVK),0)                     
              into vALL_ZATR                     
              from GK_BUH_OFORML B                     
              left join GK_BUH_OFORML_T T on T.VNESHID = B.ID                     
              left join RKO_VP V on V.ID = T.DOCID                     
             where B.DD = trunc(qSELL.DEAL_DATE)                     
               and B.OPERATION = 20                     
               and T.DEALID = qSELL.DEAL_ID                     
               and T.TYPE_OP in (30,50)         
               and V.S_S>0                     
/*               and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, case qSELL.PORTFEL_ID                     
                                                                     when 0 then 's_ls_ExpBuy_KTP'                     
                                                                     when 1 then 's_ls_ExpBuy_KIP'                     
                                                                     when 2 then 's_ls_ExpBuy_NP'                     
                                                                     else ''                     
                                                                   end, qSELL.CDKDSH_ID, trunc(qSELL.DEAL_DATE))*/                
             and V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(403, qSELL.PORTFEL_ID, qSELL.CDKDSH_ID, null, null, null, trunc(qSELL.DEAL_DATE), null);                
          exception                     
            when NO_DATA_FOUND then                     
              vALL_ZATR := 0;                     
          end;                     
        end if;                     
        /* Вычисление НКД, затрат, дисконта для пакета */                     
        begin                     
          select                     
             /* TODO pad 15.11.2016 Нужно как-то учесть частичное погашение наминала. См. nсс2, бумага 524250150. Пока по страрому. */                     
           sum(round(                     
                                              ( round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                     * RS.AMSHALL / RB.AMSHALL * nvl(RB.IS_VALUEBLE,0),2)) as "ZATR_SUM_VAL"                     
          ,sum(case                     
                 when nvl(DB.DD,RB.TIMEDEAL) < nvl((select max(nvl(DDEND_REAL,DDEND)) from GK_COUPON where CODECB = RB.CDKDSH_ID and nvl(DDEND_REAL,DDEND) < DS.DD),to_date('01.01.1900','dd.mm.yyyy'))                     
                   or (    (select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) in ('BON1','BON2')                
                           and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',(select ISSUER from GK_CDKDSH where ID = RB.CDKDSH_ID), RB.TIMEDEAL),0) <> 9                
                         or                     
                           nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',RB.CDKDSH_ID,RB.TIMEDEAL),0) = 1                
                           and ((select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) in ('BON3','BON4')                
                                or                
                                (select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) = 'BON1'                
                                and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',(select ISSUER from GK_CDKDSH where ID = RB.CDKDSH_ID), RB.TIMEDEAL),0) = 9))                
                      and (select VALUT from GK_CDKDSH where ID = RB.CDKDSH_ID) = dbo.S_GETVAR('NacValID',trunc(RB.TIMEDEAL)) then 0                     
                 else round(RB.PKDSH * RS.AMSHALL,2)                     
               end) as "PKD_SUM"                     
          ,case when qSELL.DOCOID_REALIZ = 1003117 then sum(nvl(RS.SUM_DISC,0)) else 0 end as "DISC_SUM"                     
            into vZATR_SUM,vPKD_SUM,vDISC_SUM                     
            from GK_DEPODEAL DS                     
            left join GK_REESTR RS on RS.DEAL_ID = DS.ID                     
            left join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
            left join GK_DEPODEAL DB on DB.ID = RB.DEAL_ID      
/*            left join GK_DEPOORDER OS on OS.DEAL_ID = DS.ID                     
            left join GK_REESTR RS on RS.ORDER_ID = OS.ID                     
            left join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
            left join GK_DEPOORDER OB on OB.ID = RB.ORDER_ID                     
            left join GK_DEPODEAL DB on DB.ID = OB.DEAL_ID               */      
           Where DS.ID = qSELL.DEAL_ID;                     
        exception                     
          when NO_DATA_FOUND then                     
            vZATR_SUM := 0;                     
            vPKD_SUM := 0;                     
            vDISC_SUM := 0;                     
        end;                     
        vPKD_i :=0;                     
        vZATR_i := 0;                     
        vDISC_i := 0;                     
        vCSUM1 := 0;                     
        vCSUM2 := 0;                     
        vCSUM3 := 0;                     
        /* pad 11.01.2016. Рразбивка остатка проводки бух.обормления по реестру сделкок придажи */                     
        /* TODO Стоит уменьшить на один цикл, т.к. реально тут происходит обработка количества обращений Sum(RS_IDi) * Sum(RS_IDi) к одному и тому же.... */                     
        for q in (select round(                     
             /* TODO pad 15.11.2016 Нужно как-то учесть частичное погашение наминала. См. nсс2, бумага 524250150. Пока по страрому. */                     
                                              ( round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 1), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 2), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_cliring',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 3), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_ITS',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 4), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_diler',trunc(RB.TIMEDEAL)),0)),2)                     
                                              + round(nvl((select sum(COMM_SUM_RUB) from GK_REESTR_COMMS where VNESHID = RB.ID and COMM_TYPE = 7), 0)*100/(100+nvl(dbo.S_GETVAR('s_gk_com_birga',trunc(RB.TIMEDEAL)),0)),2))                     
                       * RS.AMSHALL / RB.AMSHALL * nvl(RB.IS_VALUEBLE,0),2) as "CM"                    
                  ,case                     
                     when nvl(DB.DD,RB.TIMEDEAL) < nvl((select max(nvl(DDEND_REAL,DDEND)) from GK_COUPON where CODECB = RB.CDKDSH_ID and nvl(DDEND_REAL,DDEND) < DS.DD),to_date('01.01.1900','dd.mm.yyyy'))                     
                       or ( (select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) in ('BON1','BON2')                     
                            and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',(select ISSUER from GK_CDKDSH where ID = RB.CDKDSH_ID), RB.TIMEDEAL),0) <> 9                
                           or                     
                            nvl(dbo.GI_GK_HISTORY('IS_LGOT_PKD',RB.CDKDSH_ID,RB.TIMEDEAL),0) = 1                
                            and ((select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) in ('BON3','BON4')                
                               or                
                                 (select CODEOFSHARE from GK_TYPEOFSHARE where ID = (select TPSHC from GK_CDKDSH where ID = RB.CDKDSH_ID)) = 'BON1'                
                                 and nvl(dbo.GI_COM_ORG_AV('UR_TYPE',(select ISSUER from GK_CDKDSH where ID = RB.CDKDSH_ID), RB.TIMEDEAL),0) = 9))                
                          and (select VALUT from GK_CDKDSH where ID = RB.CDKDSH_ID) = dbo.S_GETVAR('NacValID',trunc(RB.TIMEDEAL)) then 0                     
                     else round(RB.PKDSH * RS.AMSHALL,2)                     
                   end as "PKD_UPL"                     
                  ,case when qSELL.DOCOID_REALIZ = 1003117 then nvl(RS.SUM_DISC,0) else 0 end as "DISCSPIS"                     
                  ,RS.ID                     
                  ,RS.TOPPARENT_ID                     
                  ,RB.DEAL_ID                     
                   /* pad Пишем в таблицу только когда дойдем до нашей записи */                     
                  ,case when RS.ID=qBUY.RS_ID then 1 else 0 end as TO_WRITE                     
/*                  ,case                     
                     when exists (select *                     
                                    from GK_NALOG_CB_REALIZ1 N                     
                                    left join GK_NALOG_CB_REALIZ_TABLE1 T on T.VNESHID = N.ID                     
                                   where T.REESTR_ID = RS.ID and T.DEAL_ID=RB.DEAL_ID) then 0                     
                     else 1                     
                     end as TO_WRITE */                     
                    from GK_DEPODEAL DS                     
                    left join GK_REESTR RS on RS.DEAL_ID = DS.ID                     
                    left join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
                    left join GK_DEPODEAL DB on DB.ID = RB.DEAL_ID                     
/*                    left join GK_DEPOORDER OS on OS.DEAL_ID = DS.ID                     
                    left join GK_REESTR RS on RS.ORDER_ID = OS.ID                     
                    left join GK_REESTR RB on RB.ID = RS.TOPPARENT_ID                     
                    left join GK_DEPOORDER OB on OB.ID = RB.ORDER_ID                     
                    left join GK_DEPODEAL DB on DB.ID = OB.DEAL_ID               */      
                   where DS.ID = qSELL.DEAL_ID)                     
        loop                     
          if vPKD_SUM <>0 then                     
            vPKD_i := round(vALL_NKD * q.PKD_UPL / vPKD_SUM,2);                     
          else                     
            vPKD_i :=0;                     
          end if;                     
          if vZATR_SUM <>0 then                     
            vZATR_i := round(vALL_ZATR * q.CM / vZATR_SUM,2);                     
          else                     
            vZATR_i :=0;                     
          end if;                     
          if vDISC_SUM <>0 then                     
            vDISC_i := round(vALL_DISC * q.DISCSPIS / vDISC_SUM,2);                     
          else                     
            vDISC_i :=0;                     
          end if;                     
          if (q.TOPPARENT_ID = qBUY.REESTR_ID) and (q.DEAL_ID = qBUY.DB_ID) and (q.TO_WRITE = 1) then                     
            vCSUM1 := vPKD_i;                     
            vCSUM2 := vZATR_i;                     
            vCSUM3 := vDISC_i;                     
            /* pad 15.11.2016 REESTR_S.ID сразу пишем в таблицу                     
            update GK_NALOG_CB_REALIZ_TABLE1 set REESTR_ID = q.ID where ID = vID_TABLE;  */                     
          end if;                     
          vALL_NKD := vALL_NKD - vPKD_i;                     
          vPKD_SUM := vPKD_SUM - q.PKD_UPL;                     
          vALL_ZATR := vALL_ZATR - vZATR_i;                     
          vZATR_SUM := vZATR_SUM - q.CM;                     
          vALL_DISC := vALL_DISC - vDISC_i;                     
          vDISC_SUM := vDISC_SUM - q.DISCSPIS;                     
        end loop;                     
        vCSUM2 := qBUY.IS_VALUEBLE * vCSUM2;                     
        update GK_NALOG_CB_REALIZ_TABLE1                     
           set /*NKD = case v_NewVersion when 1 then NKD else vCSUM1 * v_CURS end, //nav20210215 добавил для этого поле в реестр - PKDUPL_NALOG*/    
                CM = case                     
                       when CM < 0 then CM                     
                       else vCSUM2 * v_CURS_VALPAY                     
                     end,                     
              DISC = case when qSELL.DOCOID_REALIZ = 1003117 then vCSUM3 else DISC end                    
         where ID = vID_TABLE;                     
                     
        /* Вычисление переноса переоценки на доходы/расходы для пакета */                     
/*        vCSUM1 := 0;                     
        for q in (select ID,                     
                         DD,                     
                         null as VAL                     
                    from GK_SPIS_PEREOTS_NP                     
                   where CDKDSH = qSELL.CDKDSH_ID                     
                     and trunc(DD) >= trunc(aFROM_DATE)                     
                     and trunc(DD) <= trunc(aTO_DATE))                     
          loop                     
            vALL_PERE := q.VAL;  с обновлением данные будут заполнятсья в поле автоматом                     
            if vALL_PERE is null then    в случае старых записей в таблице значение берется из порожденной проводки                     
              begin                     
                select V.SUMRUB                     
                  into vALL_PERE                     
                  from GK_BUH_OFORML B                     
                  left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                  left join RKO_VP V on V.ID = T.DOCID                     
                 where B.DD = q.DD                     
                   and B.OPERATION = 40                     
                   and V.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Profit_NP', qSELL.CDKDSH_ID, q.DD)                     
                   and V.DOC_OID=1004615                     
                   and V.DOC_ID=q.ID                     
                   and T.TYPE_OP = 90;                     
                   and V.S_S>0                     
              exception                     
                when NO_DATA_FOUND then                     
                  vALL_PERE:=null;                     
              end;                     
            end if;                     
            if vALL_PERE is null then    в случае старых записей в таблице значение берется из порожденной проводки                     
              begin                     
                select -V.SUMRUB                     
                  into vALL_PERE                     
                  from GK_BUH_OFORML B                     
                  left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                  left join RKO_VP V on V.ID = T.DOCID                     
                 where B.DD = q.DD                     
                   and B.OPERATION = 40                     
                   and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Loss_NP', qSELL.CDKDSH_ID, q.DD)                     
                   and V.DOC_OID=1004615                     
                   and V.DOC_ID=q.ID                     
                   and T.TYPE_OP = 90;                     
                   and V.S_S>0                     
              exception                     
                when NO_DATA_FOUND then                     
                  vALL_PERE:=null;                     
              end;                     
            end if;                     
            vS11 := 0;                     
            vS2 := 0;                     
            vALL_CDKDSH := nvl(dbo.S_GKO_GET_PORTF_PZ_OSTAT('s_NoQuotPortfel', qSELL.CDKDSH_ID, q.DD), 0);  при расчете переноса переоценки надо отталкиваться от общего числа бумаг в портфеле на момент возникновения списания                     
            if vALL_CDKDSH > 0 then                     
            for qq in (select CREESTR.AMSHALL,                     
                                CREESTR.ID                     
                           from GK_REESTRDEAL PREESTR                     
                          right join GK_REESTRDEAL CREESTR on CREESTR.TOPPARENT_ID = PREESTR.ID and CREESTR.DEALTYPE = 1                     
                          where PREESTR.PARENT_ID is null                     
                            and PREESTR.CDKDSH_ID = qSELL.CDKDSH_ID                     
                            and PREESTR.BUYSELL_PORTFEL = 2  NoQuotPortfel                     
                            and trunc(PREESTR.TIMEDEAL) <= trunc(q.DD)  покупка ц/б должна происходить до наступления момента списания переоценки                     
                            and trunc(CREESTR.TIMEDEAL) >= trunc(q.DD))  продажа ц/б должна происходить после наступления момента списания переоценки                     
                loop                     
                  if qq.ID = qBUY.PACK_ID then                     
                    vCSUM1 := vCSUM1 + round((vALL_PERE - vS11) / (vALL_CDKDSH - vS2) * qq.AMSHALL, 2);  учитываем тот факт, что списаний переоценки для одного и того же пакета могло быть несколько                     
                  end if;                     
                  vS11 := vS11 + round((vALL_PERE - vS11) / (vALL_CDKDSH - vS2) * qq.AMSHALL, 2);                     
                  vS2 := vS2 + qq.AMSHALL;                     
                end loop;                     
            end if;                     
          end loop;                     
        update GK_NALOG_CB_REALIZ_TABLE1                     
           set PEREOC = vCSUM1                     
         where ID = vID_TABLE;                     
 */                     
        /* Вычисление переноса переоценки на доходы/расходы для пакета (по реализуемым ц/б, до начала периода)*/                     
        vCSUM1 := 0;                     
        for q in (select ID,                     
                         DD,                     
                         VAL/*null as VAL */                     
                    from GK_SPIS_PEREOTS_NP                     
                   where CDKDSH = qSELL.CDKDSH_ID                     
                     and trunc(DD) < trunc(aFROM_DATE)                     
                     and qSELL.DOCOID_REALIZ = 1003117                    
                     and not exists (select *                     
                                       from GK_NALOG_CB_REALIZ_TABLE1                     
                                      where VNESHID = vID_NALOG                     
                                        and PEREOC_N is not null                     
                                        and ID <> vID_TABLE))                     
          loop                     
            begin                     
              select PEREOC_N                     
                into vCSUM1                     
                from GK_NALOG_CB_REALIZ_TABLE1                     
               where ID = vID_TABLE;                     
            exception                     
              when NO_DATA_FOUND then                     
                vCSUM1 := 0;                     
            end;                     
            if nvl(vCSUM1,0) = 0 then                     
              update GK_NALOG_CB_REALIZ_TABLE1                     
                 set PEREOC_N = 0                     
               where ID = vID_TABLE;                     
            end if;                     
            vALL_PERE := q.VAL; /* с обновлением данные будут заполнятсья в поле автоматом */                     
            if vALL_PERE is null then   /* в случае старых записей в таблице значение берется из порожденной проводки */                     
              begin                     
                select sum(case                     
                         when V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(415, 2, qSELL.CDKDSH_ID, null, null, null, q.DD, null) then V.SUMRUB                
                         when V.NSD = dbo.S_GKO_GET_LSID_FROM_BIND(416, 2, qSELL.CDKDSH_ID, null, null, null, q.DD, null) then -V.SUMRUB                     
/*                         when V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_SellProfit_NP', qSELL.CDKDSH_ID, q.DD) then V.SUMRUB                     
                         when V.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_lossSell_NP', qSELL.CDKDSH_ID, q.DD) then -V.SUMRUB     */                
                       end)                     
                  into vALL_PERE                     
                  from GK_BUH_OFORML B                     
                  left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                  left join RKO_VP V on V.ID = T.DOCID                     
                 where B.DD = q.DD                     
                   and B.OPERATION = 40                     
                   and T.TYPE_OP = 90                     
                   and V.S_S>0                     
                   and V.DOC_OID=1004615                     
                   and V.DOC_ID=q.ID;                     
/*                   and V.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Profit_NP', qSELL.CDKDSH_ID, q.DD);           */                     
              exception                     
                when NO_DATA_FOUND then                     
                  vALL_PERE:=null;                     
              end;                     
            end if;                     
/*                     
            if vALL_PERE is null then                     
              begin                     
                select -V.SUMRUB                     
                  into vALL_PERE                     
                  from GK_BUH_OFORML B                     
                  left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
                  left join RKO_VP V on V.ID = T.DOCID                     
                 where B.DD = q.DD                     
                   and B.OPERATION = 40                     
                   and T.TYPE_OP = 90                     
                   and V.S_S>0                     
                   and V.DOC_OID=1004615                     
                   and V.DOC_ID=q.ID                     
                   and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Loss_NP', qSELL.CDKDSH_ID, q.DD);                     
              exception                     
                when NO_DATA_FOUND then                     
                  vALL_PERE:=null;                     
              end;                     
            end if;                     
*/                     
            vS11 := 0;                     
            vS2 := 0;                     
            vALL_CDKDSH := nvl(dbo.S_GK_OST_CB_VLAD(0,qSELL.CDKDSH_ID,q.DD,3,1,null,2),0);         
            /*nvl(dbo.S_GKO_GET_PORTF_PZ_OSTAT('s_NoQuotPortfel', qSELL.CDKDSH_ID, q.DD), 0); при расчете переноса переоценки надо отталкиваться от общего числа бумаг в портфеле на момент возникновения списания */                     
            if vALL_CDKDSH > 0 then                     
              /*for qq in (select CREESTR.AMSHALL,                     
                                CREESTR.ID                     
                           from GK_REESTRDEAL PREESTR                     
                          right join GK_REESTRDEAL CREESTR on CREESTR.TOPPARENT_ID = PREESTR.ID and CREESTR.DEALTYPE = 1                     
                          where PREESTR.PARENT_ID is null                     
                            and PREESTR.CDKDSH_ID = qSELL.CDKDSH_ID                     
                            and PREESTR.BUYSELL_PORTFEL = 2                     
                            and trunc(PREESTR.TIMEDEAL) <= trunc(q.DD)  покупка ц/б должна происходить до наступления момента списания переоценки                     
                            and trunc(CREESTR.TIMEDEAL) >= trunc(q.DD))  продажа ц/б должна происходить после наступления момента списания переоценки                     
                loop                     
                  if qq.ID = qBUY.PACK_ID then                     
                    vCSUM1 := vCSUM1 + round((vALL_PERE - vS11) / (vALL_CDKDSH - vS2) * qq.AMSHALL, 2); учитываем тот факт, что списаний переоценки для одного и того же пакета могло быть несколько                     
                  end if;                     
                  vS11 := vS11 + round((vALL_PERE - vS11) / (vALL_CDKDSH - vS2) * qq.AMSHALL, 2);                     
                  vS2 := vS2 + qq.AMSHALL;                     
                end loop;*/                     
              for qq in (select                     
                          D.DEALTYPE                     
                         ,D.DD                     
                         ,D.ID                     
                         ,D.AMSH                     
                           from GK_DEPODEAL D                     
                          where D.CDKDSH = qSELL.CDKDSH_ID                     
                            and D.DD > q.DD                     
                            and D.PORTFEL = 2                     
                            and D.DEALTYPE in (0,1,6)                     
                            and D.DEALUSER = 0                     
                          order by D.DD,D.ID)                     
              loop                     
                if vALL_PERE <> 0 then                     
                  if qq.DEALTYPE = 0 then                     
                    vALL_CDKDSH := vALL_CDKDSH + qq.AMSH;                     
                  else                     
                    if qq.ID = qSELL.DEAL_ID then                     
                      update GK_NALOG_CB_REALIZ_TABLE1                     
                         set PEREOC_N = PEREOC_N + vALL_PERE - round(vALL_PERE * (vALL_CDKDSH - qq.AMSH) / vALL_CDKDSH,2)                     
                       where ID = vID_TABLE;                     
                    end if;                     
                    vALL_PERE := round(vALL_PERE * (vALL_CDKDSH - qq.AMSH) / vALL_CDKDSH,2);                     
                    vALL_CDKDSH := vALL_CDKDSH - qq.AMSH;                     
                  end if;                     
                end if;                     
              end loop;                     
                     
            end if;                     
          end loop;                     
/*        update GK_NALOG_CB_REALIZ_TABLE1                     
           set PEREOC_N = vCSUM1                     
         where ID = vID_TABLE;                                   */                     
                     
        /*nav 11.07.2013 изменение алгоритма работы с АД:                     
              Затраты при приобретении = все,                     
              Дисконт = весь,                     
              Цена продажи = DEALSUM + старые погашения,                     
              MIN цена = котировка + старые погашения                     
              Отклонение от MIN - пусть пока не меняется*/                     
        if qSELL.PRNM = 1 then                     
          update GK_NALOG_CB_REALIZ1                     
             set PRICE = PRICE + nvl((select sum(PAYSUM)                     
                                        from GK_VALUE                     
                                       where CODECB = qSELL.CDKDSH_ID                     
                                         and nvl(DTNMMAT_REAL,DTNMMAT) < trunc(qSELL.DEAL_DATE)                     
                                         and nvl(DTNMMAT_REAL,DTNMMAT) > trunc(qBUY.DD)                     
                                         and trunc(qBUY.DD) < v_DateCalcAD),0) * qBUY.CDKDSH_COUNT                     
                ,MIN_PRICE = MIN_PRICE + nvl((select sum(PAYSUM)                     
                                                from GK_VALUE                     
                                               where CODECB = qSELL.CDKDSH_ID                     
                                                 and nvl(DTNMMAT_REAL,DTNMMAT) < trunc(qSELL.DEAL_DATE)                     
                                                 and nvl(DTNMMAT_REAL,DTNMMAT) > trunc(qBUY.DD)                     
                                                 and trunc(qBUY.DD) < v_DateCalcAD),0) * qBUY.CDKDSH_COUNT                     
                ,MIN_PRICE_R = MIN_PRICE_R + nvl((select sum(PAYSUM)                     
                                                    from GK_VALUE                     
                                                   where CODECB = qSELL.CDKDSH_ID                     
                                                     and nvl(DTNMMAT_REAL,DTNMMAT) < trunc(qSELL.DEAL_DATE)                     
                                                     and nvl(DTNMMAT_REAL,DTNMMAT) > trunc(qBUY.DD)                     
                                                     and trunc(qBUY.DD) < v_DateCalcAD),0) * qBUY.CDKDSH_COUNT                
           where ID = vID_NALOG;                     
/*nav151009 этот вариант расчёта погашенной в предыдущие ЧП части дисконта менее правильный, т.к. окргуление ведётся по каждому ЧП,                     
            но так сейчас работает..... А может, и нормально работает... Если переделать дисконт, возможно, надо будет вернуть                     
            update в комментарии ниже*/                     
          update GK_NALOG_CB_REALIZ_TABLE1                     
             set CM = round(nvl(qBUY.CM,0) * nvl(qBUY.CDKDSH_COUNT,1) / nvl(qBUY.AMSH_BUY,1) * (qBUY.IS_VALUEBLE*2-1)                 
/*                            * case when qSELL.DOCOID_REALIZ = 1003117 and trunc(qBUY.DD) >= v_DateCalcAD and qBUY.PRNM = 1 then dbo.S_GKO_FNOMINAL(qSELL.CDKDSH_ID,qSELL.DEAL_DATE) else 1 end                  
                            / case when qSELL.DOCOID_REALIZ = 1003117 and trunc(qBUY.DD) >= v_DateCalcAD and qBUY.PRNM = 1 then dbo.S_GKO_FNOMINAL(qSELL.CDKDSH_ID,qBUY.DD) else 1 end*/                 
                            ,2)                 
                ,DISC = DISC                     
                      + case when qSELL.DOCOID_REALIZ = 1003117 then nvl((select sum(                     
                                round(nvl(                     
                                 case                     
                                   when qBUY.NOM_BUY <= qBUY.RB_PRICE then 0                     
                                   else round((100 - qBUY.RB_PRICE * 100 / qBUY.NOM_BUY)                     
                                               * qBUY.CDKDSH_COUNT * V.PAYSUM / 100, 2  )                     
        /*                                      end * qBUY.CDKDSH_COUNT * (qBUY.NOM_BUY - dbo.S_GKO_FNOMINAL(qBUY.CDKDSH_ID, trunc(qSELL.DEAL_DATE)-5)) / 100, 2  ()*/                     
        /*                                      end * qBUY.CDKDSH_COUNT * (qBUY.NOM_BUY - dbo.S_GKO_FNOMINAL(qBUY.CDKDSH_ID, coalesce((select min(DTNMMAT)                     
                                                                                                                                    from GK_VALUE                     
                                                                                                                                   where nvl(DTNMMAT_REAL,DTNMMAT) >= trunc(qSELL.DEAL_DATE))                     
                                                                                                                                 ,(select DTDN                     
                                                                                                                                     from GK_CDKDSH                     
                                                                                                                                    where nvl(DTDNREAL,DTDN) >= trunc(qSELL.DEAL_DATE))                     
                                                                                                                                 ,trunc(qSELL.DEAL_DATE)) - 1)) / 100, 2() */                     
                                 end,0),2))                     
                               from GK_VALUE V                     
                              where V.CODECB = qBUY.CDKDSH_ID                     
                                and nvl(DTNMMAT_REAL,DTNMMAT) > qBUY.DD                     
                                and nvl(DTNMMAT_REAL,DTNMMAT) < qSELL.DEAL_DATE                     
                                 ),0)   else 0 end                    
           where ID = vID_TABLE;                     
/* nav151009 Это более правильный вариант найти погашенную в предыдущие ЧП часть дисконта, чем update выше т.к. округляется на                     
             общую величину погашенной части номинала, а не на каждое погашение в отдельности. Если расчёт дисконта переделать, то                     
             можно будет вернуть этот вариант                     
          update GK_NALOG_CB_REALIZ_TABLE1                     
             set CM = nvl(qBUY.CM,0) * nvl(qBUY.CDKDSH_COUNT,1) / nvl(qBUY.AMSH_BUY,1) * (qBUY.IS_VALUEBLE*2-1)                     
                ,DISC = DISC                     
                      + round(nvl(                     
                         case                     
                           when qBUY.NOM_BUY <= qBUY.RB_PRICE then 0                     
                           else round((100 - qBUY.RB_PRICE * 100 / qBUY.NOM_BUY)                     
                                       * qBUY.CDKDSH_COUNT * (qBUY.NOM_BUY - dbo.S_GKO_FNOMINAL(qBUY.CDKDSH_ID, case qSELL.DEALTYPE                     
                                                                                                               when 1 then trunc(qSELL.DEAL_DATE)                     
                                                                                                               else trunc(qSELL.DEAL_DATE)-5                     
                                                                                                             end)) / 100, 2  )                     
                         end,0),2)                     
           where ID = vID_TABLE; */                     
        end if;                     
      end loop;                     
    end loop;                     
                     
  /* Вычисление переноса переоценки на доходы/расходы для пакета (по всем ц/б за период);                     
       Считается как сумма списания                     
       / (Количество ц/б на балансе на момент списания)                     
       * (Количество оставшихся из них на балансе на конец отчетного периода)*/                     
  if vMODE = 2 then                     
    update GK_NALOG_CB_REALIZ_TABLE1                     
       set PEREOC = 0                     
     where ID = vID_TABLE;                     
    for q in (select ID,                     
                     DD,                     
                     CDKDSH,                     
                     VAL/*null as VAL */                     
                from GK_SPIS_PEREOTS_NP                     
               where trunc(DD) >= trunc(aFROM_DATE)                     
                 and trunc(DD) <= trunc(aTO_DATE))                     
      loop                     
        vALL_PERE := q.VAL;                     
        if vALL_PERE is null then                     
          begin                     
            select sum(case                     
                         when V.NSK = dbo.S_GKO_GET_LSID_FROM_BIND(415, 2, q.CDKDSH, null, null, null, q.DD, null) then V.SUMRUB                
                         when V.NSD = dbo.S_GKO_GET_LSID_FROM_BIND(416, 2, q.CDKDSH, null, null, null, q.DD, null) then V.SUMRUB                
/*                         when V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_SellProfit_NP', q.CDKDSH, q.DD) then V.SUMRUB                     
                         when V.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_lossSell_NP', q.CDKDSH, q.DD) then -V.SUMRUB     */                
                       end)                     
              into vALL_PERE                     
              from GK_BUH_OFORML B                     
              left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
              left join RKO_VP V on V.ID = T.DOCID                     
             where B.DD = q.DD                     
               and B.OPERATION = 40                     
               and T.TYPE_OP = 90                     
               and V.S_S>0                     
               and V.DOC_OID=1004615                     
               and V.DOC_ID=q.ID;                     
  /*             and V.NSD = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Profit_NP', q.CDKDSH, q.DD);           */                     
          exception                     
            when NO_DATA_FOUND then                     
              vALL_PERE:=null;                     
          end;                     
        end if;                     
        if nvl(vALL_PERE,0) > 0 then                     
          /*TODO после того, как в НКЦ будет установлена доработка по Прямому РЕПО, нужно будет изменить запрос ниже.                     
                  Запрос должен возврящать:                     
                    1) кол-во ц/б q.CDKDSH, которые на дату признания ц/б некотируемой (q.DD) находились на балансе на                     
                       цели учета "имеющиеся в наличии для продажи"                     
                    2) кол-во ц/б из 1), которые остались на балансе на конец отчетного периода (aTO_DATE)                     
                  Смысл: в поле "Перенос переоценки со счетов добавочного капитала на счет доходов/расходов" должна попадать                     
                         сумма списания, приходящаяся на количество оставшихся на балансе ц/б на конец отчетного периода.                     
                  Проблема: на дату признания ц/б некотируемой на цели учета "им. в нал. для продажи" могут присутствовать                     
                            как купленные в нее ц/б, так и переведенные с другой цели учета. Далее мы могла эти пакеты переводить                     
                            более одного раза, затем продать. ID реестра по продаже по полю PORTFPARENT_ID или PARENT_ID                     
                            будут ссылаться на последний перевод, а по TOPPARENT_ID - на начальную покупку, то есть найти бумаги,                     
                            которые были переведены в некот.портф. на q.DD, затем еще несколько раз переведены, затем проданы,                     
                            довольно проблематично.                     
                  Если такой ситуации не будет (по ц/б либо были переводы только до q.DD и не более одного после, либо только после),                     
                    пока будет отрабатывать имеющийся запрос (пока не было Прямого РЕПО), после установки обновления по РЕПО                     
                    (поле PORTFPARENT_ID) можно будет использовать следующий запрос:                     
                     
begin                     
  select sum(OST_Q_DD) as OST_Q_DD                     
        ,sum(OST_Q_DD                     
         - nvl((select sum(RS.AMSHALL)                     
                  from GK_REESTRDEAL RS                     
                 where case                     
                         when TOPPARENT_ID = A.ID then TOPPARENT_ID                     
                         when PORTFPARENT_ID = A.ID then PORTFPARENT_ID                     
                         when (select PORTFPARENT_ID from GK_REESTRDEAL where ID = RS.PORTFPARENT_ID) = A.ID then                     
                              (select PORTFPARENT_ID from GK_REESTRDEAL where ID = RS.PORTFPARENT_ID)                     
                       end = A.ID                     
                   and RS.DEALTYPE = 1                     
                   and RS.TIMEDEAL > to_date('24.05.2013','dd.mm.yyyy')                     
                   and RS.TIMEDEAL <= to_date('31.12.2013','dd.mm.yyyy')                     
                   and not exists                     
                         (select O.ID                     
                            from GK_DEPOORDER O                     
                           where O.ID = RS.CURDOC_ID                     
                             and (O.DOCOID = 1004904                     
                               or (select DOCOID                     
                                     from GK_DEPODEAL                     
                                    where ID = O.DEAL_ID) = 1004563))),0)) as OST_END_PERIOD                     
    into v_CountCbPerSpis, v_CountCbEndPeriod                     
    from (                     
      select AMSHALL                     
             - nvl((select sum(AMSHALL)                     
                      from GK_REESTRDEAL RS                     
                     where nvl(PORTFPARENT_ID,TOPPARENT_ID) = RB.ID                     
                       and (DEALTYPE = 1 and not exists                     
                              (select O.ID                     
                                 from GK_DEPOORDER O                     
                                where O.ID = RS.CURDOC_ID                     
                                  and (O.DOCOID = 1004904                     
                                    or (select DOCOID                     
                                          from GK_DEPODEAL                     
                                         where ID = O.DEAL_ID) = 1004563))                     
                         or DEALTYPE = 2 and exists                     
                              (select ID                     
                                 from GK_DEPODEAL                     
                                where ID = (select ID from GK_DEPOORDER where ID = RS.CURDOC_ID)                     
                                  and DEALTYPE = 7                     
                                  and TRANSPFLTYPE in (4,5,8)))                     
                       and TIMEDEAL <= to_date('24.05.2013','dd.mm.yyyy')),0) as OST_Q_DD                     
            ,RB.ID                     
        from GK_REESTRDEAL RB                     
       where RB.CDKDSH_ID = 293690500                     
         and (RB.TOPPARENT_ID is null and DEALTYPE = 0 and dbo.S_GKO_GET_PORTF_BY_LS(nvl(OLD_LSDEPO2,LSDEPO_ID),trunc(TIMEDEAL)) = 2                     
           or RB.TOPPARENT_ID is not null and DEALTYPE = 2 and exists                     
                (select ID                     
                   from GK_DEPODEAL                     
                  where ID = (select ID from GK_DEPOORDER where ID = RB.CURDOC_ID)                     
                    and DEALTYPE = 7                     
                    and TRANSPFLTYPE in (1,3)))                     
         and RB.TIMEDEAL <= to_date('24.05.2013','dd.mm.yyyy')) A                     
   where nvl(OST_Q_DD,0) > 0;                     
exception                     
  when NO_DATA_FOUND then                     
    v_CountCbPerSpis := null;                     
    v_CountCbEndPeriod := null;                     
end;                     
          */                     
/*          begin                     
            select sum(OST_Q_DD) as OST_Q_DD                     
                  ,sum(OST_Q_DD                     
                   - nvl((select sum(RS.AMSHALL)                     
                            from GK_REESTRDEAL RS                     
                           where case                     
                                   when TOPPARENT_ID = A.ID then TOPPARENT_ID                     
                                   when PARENT_ID = A.ID then PARENT_ID                     
                                   when (select PARENT_ID from GK_REESTRDEAL where ID = RS.PARENT_ID) = A.ID then                     
                                        (select PARENT_ID from GK_REESTRDEAL where ID = RS.PARENT_ID)                     
                                 end = A.ID                     
                             and RS.DEALTYPE = 1                     
                             and RS.TIMEDEAL > q.DD                     
                             and RS.TIMEDEAL <= aTO_DATE                     
                             and not exists                     
                                   (select O.ID                     
                                      from GK_DEPOORDER O                     
                                     where O.ID = RS.CURDOC_ID                     
                                       and (O.DOCOID = 1004904                     
                                         or (select DOCOID                     
                                               from GK_DEPODEAL                     
                                              where ID = O.DEAL_ID) = 1004563))),0)) as OST_END_PERIOD                     
              into v_CountCbPerSpis, v_CountCbEndPeriod                     
              from (                     
                select AMSHALL                     
                       - nvl((select sum(AMSHALL)                     
                                from GK_REESTRDEAL RS                     
                               where nvl(PARENT_ID,TOPPARENT_ID) = RB.ID                     
                                 and (DEALTYPE = 1 and not exists                     
                                        (select O.ID                     
                                           from GK_DEPOORDER O                     
                                          where O.ID = RS.CURDOC_ID                     
                                            and (O.DOCOID = 1004904                     
                                              or (select DOCOID                     
                                                    from GK_DEPODEAL                     
                                                   where ID = O.DEAL_ID) = 1004563))                     
                                   or DEALTYPE = 2 and exists                     
                                        (select ID                     
                                           from GK_DEPODEAL                     
                                          where ID = (select ID from GK_DEPOORDER where ID = RS.CURDOC_ID)                     
                                            and DEALTYPE = 7                     
                                            and TRANSPFLTYPE in (4,5,8)))                     
                                 and TIMEDEAL <= q.DD),0) as OST_Q_DD                     
                      ,RB.ID                     
                  from GK_REESTRDEAL RB                     
                 where RB.CDKDSH_ID = q.CDKDSH                     
                   and (RB.TOPPARENT_ID is null and DEALTYPE = 0 and dbo.S_GKO_GET_PORTF_BY_LS(nvl(OLD_LSDEPO2,LSDEPO_ID),trunc(TIMEDEAL)) = 2                     
                     or RB.TOPPARENT_ID is not null and DEALTYPE = 2 and exists                     
                          (select ID                     
                             from GK_DEPODEAL                     
                            where ID = (select ID from GK_DEPOORDER where ID = RB.CURDOC_ID)                     
                              and DEALTYPE = 7                     
                              and TRANSPFLTYPE in (1,3)))                     
                   and RB.TIMEDEAL <= q.DD) A                     
             where nvl(OST_Q_DD,0) > 0;                     
          exception                     
            when NO_DATA_FOUND then                     
              v_CountCbPerSpis := null;                     
              v_CountCbEndPeriod := null;                     
          end;                     
*/                     
/*        if vALL_PERE is null then                     
          begin                     
            select -V.SUMRUB                     
              into vALL_PERE                     
              from GK_BUH_OFORML B                     
              left join GK_BUH_OFORML_T T on T.VNESHID=B.ID                     
              left join RKO_VP V on V.ID = T.DOCID                     
             where B.DD = q.DD                     
               and B.OPERATION = 40                     
               and T.TYPE_OP = 90                     
               and V.S_S>0                     
               and V.DOC_OID=1004615                     
               and V.DOC_ID=q.ID                     
               and V.NSK = dbo.S_GKO_BDA_BIND_LABEL_CLASS(1003104, 's_ls_AddCap_Loss_NP', q.CDKDSH, q.DD);                     
          exception                     
            when NO_DATA_FOUND then                     
              vALL_PERE:=null;                     
          end;                     
        end if;*/                     
                     
            vS11 := 0;                     
            vS2 := 0;                     
            vALL_CDKDSH := nvl(dbo.S_GK_OST_CB_VLAD(0,q.CDKDSH,q.DD,3,1,null,2),0);         
            /*nvl(dbo.S_GKO_GET_PORTF_PZ_OSTAT('s_NoQuotPortfel', q.CDKDSH, q.DD), 0); /* при расчете переноса переоценки надо отталкиваться от общего числа бумаг в портфеле на момент возникновения списания */                     
                     
            if vALL_CDKDSH > 0 then                     
              for qq in (select                     
                          D.DEALTYPE                     
                         ,D.DD                     
                         ,D.ID                     
                         ,D.AMSH                     
                           from GK_DEPODEAL D                     
                          where D.CDKDSH = q.CDKDSH                     
                            and D.DD > q.DD                     
                            and D.DD <= trunc(aTO_DATE)                     
                            and D.PORTFEL = 2                     
                            and D.DEALTYPE in (0,1,6)                     
                            and D.DEALUSER = 0                     
                          order by D.DD,D.ID)                     
              loop                     
                if vALL_PERE <> 0 then                     
                  if qq.DEALTYPE = 0 then                     
                    vALL_CDKDSH := vALL_CDKDSH + qq.AMSH;                     
                  else                     
                    vALL_PERE := round(vALL_PERE * (vALL_CDKDSH - qq.AMSH) / vALL_CDKDSH,2);                     
                    vALL_CDKDSH := vALL_CDKDSH - qq.AMSH;                     
                  end if;                     
                end if;                     
              end loop;                     
              update GK_NALOG_CB_REALIZ_TABLE1                     
                 set PEREOC = PEREOC + vALL_PERE/*PEREOC + round(vALL_PERE * nvl(v_CountCbEndPeriod,0) / nvl(v_CountCbPerSpis,0),2)*/                     
               where ID = vID_TABLE;                     
            end if;                     
        end if;                     
      end loop;                     
  end if;                     
end S_GK_NALOG_CB_REALIZ_R1; 
