create procedure dbo.%PROC%
  @query_id numeric = null,          --Id запроса
  @client_contract_id numeric = null,--Id клиенсткого договора (если запрос по договорам обслуживания, то null)
  @external_broker_id numeric = null,--Id договора обслуживания (если запрос по клиентским договорам, то null)
  @price_id numeric = null,          --Id прайс-листа по договору
  @date_b datetime = null,           --дата начала периода подсчета оборотов
  @date_e datetime = null,           --дата окончания подсчета оборотов
  @tarif_id numeric = null,          --Id тарифа из прайс-листа
  @bill_id numeric = null,           --Id счета сформированного для данного договора
  @plan_id numeric = null            --Id плана учета ДС

as

begin
declare
        @query_date datetime,
        @owner_id numeric,
        @t_rate_value money,
        @service_id numeric,
        @emitent_id numeric,
        @character_id numeric,
        @doc_sum money,
        @object_id numeric,
        @val_id numeric

select
  @query_date=td_depo_docs.in_date,
  @owner_id=td_depo_docs.owner_id,
  @service_id=td_queries.service_id,
  @emitent_id=td_queries.emitent_id
from
  td_depo_docs,
  td_queries
where td_depo_docs.id=@query_id
  and td_queries.id=@query_id

if @client_contract_id is null select @character_id=1
else select @character_id=2

.TYPE_GET('TARIFFING_FOR_BROK',@tarif_id)

insert into #stocks(stock_id,nominal,nominal_cur_id)
select td_stock_emis.id,
       stock_nominal,
       stock_curr_id
from td_stock_emis,
     t_stock_types
where ((td_stock_emis.stock_type_id=@stock_type_id1)or(@stock_type_id1 is null))
  and ((emmitent_id=@emitent_id) or (@emitent_id is null))
  and td_stock_emis.stock_type_id = t_stock_types.id
  and ((t_stock_types.stock_type_id=@base_stock_type_id1) or (@base_stock_type_id1 is null))
  and ((.ITEMS_EXISTS_BY_CLASS_VALUE(td_stock_emis.id,@stock_class_value_id1))or(@stock_class_value_id1 is null))
  and .ITEMS_EXISTS_BY_TYPE(td_stock_emis.id,'TD_STOCK_EMIS')

select @t_rate_value =round(1.0/(select rate_value
                                 from t_rates r,
                                      t_items i,
                                      t_states s
                                 where r.cur_id=@cur_tariff_id1
                                   and r.rate_date =(select max(rate_date)
                                                     from t_rates r,
                                                          t_items i,
                                                          t_states s
                                                     where r.cur_id=@cur_tariff_id1--@t_cur_id                              and r.rate_date <='20-12-2004'--@convert_date
 			                               and r.rate_date <=@query_date	
                                                       and r.id = i.id
                                                       and i.state_id = s.id
                                                       and s.class_id !=2)
                                   and r.id = i.id
                                   and i.state_id = s.id
                                   and s.class_id !=2),4)



/*вычисление оборотов*/

  if @base_period_id1 in (2,3) ---- шкала выбирается в зависимости от оборота за день
  begin  -- 3
   if @type_bill_display_id1=1 ----- группировка для подсчета - без группировки. оборот по клиенту в целом.
     begin  --4

      if @type_speed_id1=1 --- 5 процент считать от полного оборота
      begin

      if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select 1,
              sum((case @base_sum_id1
                   when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                   when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                   else isnull(tb_baysale_docitems.summ_doc_total,0)
                   end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
       from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
     else  --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
        insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
        select 1,
               sum((case @base_sum_id1
                    when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                    when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                    else isnull(tb_baysale_docitems.summ_doc_total,0)
                    end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,
              td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
         and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id

      end else ---5
      if @type_speed_id1=2 --- 5  --- от нетто оборота
      begin

      if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
         insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
         select 1,
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
        group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
      else --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
         insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
         select 1,
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
      end  ---5

     end else
     if @type_bill_display_id1=2 --- группировка для подсчета ЦБ. обороты по ЦБ
     begin
       if @type_speed_id1=1 ---- процент считать от полного оборота
       begin
         if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
        insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
        select tb_baysale_docitems.stock_id,
              sum((case @base_sum_id1
                   when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                   when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                   else isnull(tb_baysale_docitems.summ_doc_total,0)
                   end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
       from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
       else --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select tb_baysale_docitems.stock_id,
               sum((case @base_sum_id1
                    when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                    when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                    else isnull(tb_baysale_docitems.summ_doc_total,0)
                    end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,
              td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
         and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
      end else --- от нетто оборота
      if @type_speed_id1=2
      begin

      if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select tb_baysale_docitems.stock_id,
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
        group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id

      else --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select tb_baysale_docitems.stock_id,
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
              #stocks,
              td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
       group by tb_baysale_docitems.stock_id,#stocks.nominal_cur_id,tb_baysale_docitems.cur_id
     end

     end else
     if @type_bill_display_id1=3  --- группировка по счету депо . обороты по счету депо (газпромбанк)
     begin

      if @type_speed_id1=1 ---- процент считать от полного оборота
      begin

      if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select ds.depo_acc_id,
              sum((case @base_sum_id1
                   when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                   when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                   else isnull(tb_baysale_docitems.summ_doc_total,0)
                   end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s,
             td_depo_subacc ds
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
          and ds.id=tb_baysale_docitems.depo_subacc_id
       group by ds.depo_acc_id,tb_baysale_docitems.stock_id,tb_baysale_docitems.cur_id, #stocks.nominal_cur_id
      else --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select ds.depo_acc_id,
              sum((case @base_sum_id1
                   when 5 then isnull(tb_baysale_docitems.summ_doc_total2unkd,0)
                   when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                   else isnull(tb_baysale_docitems.summ_doc_total,0)
                   end)),
              sum(tb_baysale_docitems.stock_qty),
              (case @base_sum_id1
               when 2 then #stocks.nominal_cur_id
               else tb_baysale_docitems.cur_id
               end),
              tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s,
             td_depo_subacc ds
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
         and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
         and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
          and ds.id=tb_baysale_docitems.depo_subacc_id
       group by ds.depo_acc_id,tb_baysale_docitems.stock_id,tb_baysale_docitems.cur_id, #stocks.nominal_cur_id

      end else --- от нетто оборота
      if @type_speed_id1=2
      begin

      if @service_id<>6  ---- для всех услуг кроме обслуживания клиентов, сделки отбираются по oper_date.
                         ---- это дата подтверждения сделки, поскольку дата проведения РЕПО отличается от даты сделки
       insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select ds.depo_acc_id,
               abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
        from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s,
             td_depo_subacc ds
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
          and ds.id=tb_baysale_docitems.depo_subacc_id
       group by ds.depo_acc_id,tb_baysale_docitems.stock_id,tb_baysale_docitems.cur_id, #stocks.nominal_cur_id
      else --- для услуги обслуживание клиентов сделки собираются по brok_com_date (туда запихивается дата взимания комиссии, в зависимости от условий
           ---                 клиентского договора. актуально для сделок вбр. поскольку даты могут отличаться)
        insert into #oborot(object_id,o_sum,qty,o_cur_id,stock_id)
       select ds.depo_acc_id,
               abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty)
                                 else summ_doc_total
                                 end)
                         else
                                (case @base_sum_id1
                                 when 5 then summ_doc_total2unkd*(-1.0)
                                 when 2 then (isnull(#stocks.nominal,0)*tb_baysale_docitems.stock_qty*(-1.0))
                                 else summ_doc_total*(-1.0)
                                 end)
                         end))),
                abs(sum((case tb_baysale_docitems.ticket_type_id
                         when 1 then tb_baysale_docitems.stock_qty
                         else tb_baysale_docitems.stock_qty*(-1.0)
                         end))),
               (case @base_sum_id1
                when 2 then #stocks.nominal_cur_id
                else tb_baysale_docitems.cur_id
                end),
                tb_baysale_docitems.stock_id
       from tb_baysale_docitems,
             #stocks,
             td_depo_docs,
             tb_broker2exchange,
             t_items i,
             t_types t,
             t_states s,
             td_depo_subacc ds
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
         and isnull(brok_com_date,in_date)>dateadd(day,-1,@date_b)
         and isnull(brok_com_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
          and ds.id=tb_baysale_docitems.depo_subacc_id
       group by ds.depo_acc_id,tb_baysale_docitems.stock_id,tb_baysale_docitems.cur_id, #stocks.nominal_cur_id
      end
   end ---- 4
end



update #oborot set
o_rate=(select rate_value
        from t_rates r,
             t_items i,
             t_states s
        where r.cur_id=o_cur_id
          and r.rate_date =(select max(rate_date)
                            from t_rates r,
                                 t_items i,
                                 t_states s
                            where r.cur_id=o_cur_id--@t_cur_id
                              and r.rate_date <= @query_date	
                              and r.id = i.id
                              and i.state_id = s.id
                              and s.class_id !=2)
                              and r.id = i.id
                              and i.state_id = s.id
                              and s.class_id !=2)
update #oborot set
all_sum=round((o_sum*@t_rate_value*convert(money,o_rate)),4),
o_cur_id=@cur_tariff_id1

--select * from #oborot


declare cur cursor for
select object_id,
       sum(all_sum),
       o_cur_id
from #oborot
group by object_id,o_cur_id
for read only

declare
@result money,
@comis_sum money,
@taritf_cur_id numeric,
@contragent_id numeric,
@contract_id numeric,
@rate_cur_id numeric,
@bill_pos_id numeric,
@date datetime,
@sum_new money,
@r_v_s money,
@r_d_s datetime,
@obj_id numeric,
@bay_or_sale int,
@investor_id numeric,
@dir_id numeric,
@b_a_id numeric,
@d_s_id numeric,
@d_id numeric,
@tarif_cur_id numeric

select @rate_cur_id=.CUR2PLAN(@plan_id)
select @contract_id=isnull(@client_contract_id,@external_broker_id)

open cur
fetch cur into @object_id, @doc_sum, @val_id

  while .CURSOR_STATE=0
  begin  -- курсор по таблице оборотов

  .CALC_COMIS(@doc_sum,@tarif_id,@query_date,@val_id,@result,-1,@comis_sum,@tarif_cur_id,@plan_id)

  .BACK_CALC_CONTRAGENT(@contragent_id,@tarif_id,@contract_id,@owner_id,2,@character_id)

---- вставка позиции в счет
  if isnull(@result,0)>0
  begin

  if @character_id=1
  begin

  .TYPE_INIT('BILL_IN_COM_OPER',2)
  .TYPE_ASSIGN(service_id,@service_id1,2)
  .TYPE_ASSIGN(bill_id,@bill_id,2)
  .TYPE_ASSIGN(bill_list_sum,@result,2)
  .TYPE_ASSIGN(bill_type_description,(.SIMPLE_NAME(5500,@service_id1)),2)
  .TYPE_ASSIGN(cur_id,@cur_tariff_id1,2)
  .TYPE_ASSIGN(doc_description,((select item_name from t_items where id=@object_id)),2)
  .TYPE_ASSIGN(doc_sum,@result,2)
  .TYPE_ASSIGN(rate,1,2)
  .TYPE_ASSIGN(rate_cur_id,@rate_cur_id,2)
  .TYPE_ASSIGN(stock_id,@object_id,2)
  .TYPE_ASSIGN(type_bill_display_id,@type_bill_display_id1,2)
  .TYPE_ASSIGN(type_partition2investor_id,@type_partition2investor_id1,2)
  .TYPE_ASSIGN(type_partition2oper_id,@type_partition2oper_id1,2)
  .TYPE_ASSIGN(part_sum_id,@part_sum_id1,2)
  .TYPE_ASSIGN(price_position_id,@tarif_id,2)
  .TYPE_ASSIGN(service_id,@service_id1,2)
  .TYPE_ASSIGN(type_exec_id,@type_exec_id1,2)
  .TYPE_ASSIGN(contract_id,@contract_id,2)
  .TYPE_ASSIGN(contragent_id,@contragent_id,2)
  .TYPE_INSERT('BILL_IN_COM_OPER','CRT',2)

  select @bill_pos_id=@id2_1

  end else
  begin

  .TYPE_INIT('BILL_OUT_COM_OPER',8)
  .TYPE_ASSIGN(service_id,@service_id1,8)
  .TYPE_ASSIGN(bill_id,@bill_id,8)
  .TYPE_ASSIGN(bill_list_sum,@result,8)
  .TYPE_ASSIGN(bill_type_description,(.SIMPLE_NAME(5500,@service_id1)),8)
  .TYPE_ASSIGN(cur_id,@cur_tariff_id1,8)
  .TYPE_ASSIGN(doc_description,('По операциям'),8)
  .TYPE_ASSIGN(doc_id,@object_id,8)
  .TYPE_ASSIGN(doc_sum,@result,8)
  .TYPE_ASSIGN(rate,1,8)
  .TYPE_ASSIGN(rate_cur_id,@rate_cur_id,8)
  .TYPE_ASSIGN(stock_id,@object_id,8)
  .TYPE_ASSIGN(type_bill_display_id,@type_bill_display_id1,8)
  .TYPE_ASSIGN(type_partition2investor_id,@type_partition2investor_id1,8)
  .TYPE_ASSIGN(type_partition2oper_id,@type_partition2oper_id1,8)
  .TYPE_ASSIGN(part_sum_id,@part_sum_id1,8)
  .TYPE_ASSIGN(type_exec_id,@type_exec_id1,8)
  .TYPE_ASSIGN(price_position_id,@tarif_id,8)
  .TYPE_ASSIGN(service_id,@service_id1,8)
  .TYPE_ASSIGN(contract_id,@contract_id,8)
  .TYPE_ASSIGN(contragent_id,@contragent_id,8)
  .TYPE_INSERT('BILL_OUT_COM_OPER','CRT',8)

  select @bill_pos_id=@id8_1

  end



  if @type_bill_display_id1 in (2,1,3)
  begin

  exec ap_cursor2query @owner_id,@rate_plase_id1,@date_b,@date_e,@bill_pos_id,@tarif_id,@contract_id,@sale_type_id1,@object_id,@type_bill_display_id1,@service_id

  end

  end

  select @doc_sum=null

  fetch cur into @object_id,@doc_sum,@val_id

  end -- конец курсора по объектам

 close cur
.DEALLOCATE cur

  if isnull(@min_sum1,0)<>0
  begin

  select @object_id=min(tb_baysale_docitems.id)
        from tb_baysale_docitems,
              #stocks,td_depo_docs,
              tb_broker2exchange,
              t_items i,
              t_types t,
              t_states s
        where td_depo_docs.id=tb_baysale_docitems.id
          and i.id=td_depo_docs.id
          and td_depo_docs.owner_id=@owner_id
          and isnull(oper_date,in_date)>dateadd(day,-1,@date_b)
          and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
          and ((tb_baysale_docitems.sale_type_id=@sale_type_id1)or(@sale_type_id1=0))
          and ((tb_baysale_docitems.client_contract_id= @client_contract_id)or( @client_contract_id is null))
          and ((tb_baysale_docitems.sale_place_id=@rate_plase_id1)or(@rate_plase_id1 is null))
          and i.type_id=t.id
          and s.id=i.state_id
          and t.type_code in ('DEALINGS_VIA_BROKER','DEALINGS_VIA_BROKER_SALE','DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR')
          and s.state_code in ('PODT','PODP','OBYAZ_END','OPL_END','POLUCH','POST_END','WAIT_EXEC')
          and tb_baysale_docitems.asset_place_id=tb_broker2exchange.id
          and ((tb_broker2exchange.ext_broker_deal=@external_broker_id)or(@external_broker_id is null))
          and tb_baysale_docitems.stock_id=#stocks.stock_id
          and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)

    select @date=(select isnull(oper_date,in_date) from td_depo_docs where id=@object_id)

   .TEST_RATE2CUR2DATE(@date,@cur_tariff_id1)

   .CONVERT_SUM(@cur_tariff_id1,@rate_cur_id,@date,0,@min_sum1,@sum_new,@r_v_s,@r_d_s)

   select @min_sum1=@min_sum1*1.2

  if isnull(@sum_new,0)>0
  begin

  if @character_id=1
  begin

  .TYPE_INIT('BILL_IN_COM_OPER',3)
  .TYPE_ASSIGN(service_id,@service_id1,3)
  .TYPE_ASSIGN(bill_id,@bill_id,3)
  .TYPE_ASSIGN(bill_list_sum,@sum_new,3)
  .TYPE_ASSIGN(bill_type_description,(.SIMPLE_NAME(5500,@service_id1)),3)
  .TYPE_ASSIGN(cur_id,@cur_tariff_id1,3)
  .TYPE_ASSIGN(doc_description,((select item_name from t_items where id=@object_id)),3)
  .TYPE_ASSIGN(doc_sum,@sum_new,3)
  .TYPE_ASSIGN(rate,1,3)
  .TYPE_ASSIGN(rate_cur_id,@rate_cur_id,3)
  .TYPE_ASSIGN(stock_id,@object_id,3)
  .TYPE_ASSIGN(type_bill_display_id,1,3)
  .TYPE_ASSIGN(type_exec_id,@type_exec_id1,3)
  .TYPE_ASSIGN(price_position_id,@tarif_id,3)
  .TYPE_ASSIGN(service_id,@service_id1,3)
  .TYPE_ASSIGN(contract_id,@contract_id,3)
  .TYPE_ASSIGN(contragent_id,@contragent_id,3)
  .TYPE_INSERT('BILL_IN_COM_OPER','CRT',3)

  end else
  begin

  .TYPE_INIT('BILL_OUT_COM_OPER',6)
  .TYPE_ASSIGN(service_id,@service_id1,6)
  .TYPE_ASSIGN(bill_id,@bill_id,6)
  .TYPE_ASSIGN(bill_list_sum,@sum_new,6)
  .TYPE_ASSIGN(bill_type_description,(.SIMPLE_NAME(5500,@service_id1)),6)
  .TYPE_ASSIGN(cur_id,@cur_tariff_id1,6)
  .TYPE_ASSIGN(doc_description,((select item_name from t_items where id=@object_id)),6)
  .TYPE_ASSIGN(doc_id,@object_id,6)
  .TYPE_ASSIGN(doc_sum,@sum_new,6)
  .TYPE_ASSIGN(rate,1,6)
  .TYPE_ASSIGN(rate_cur_id,@rate_cur_id,6)
  .TYPE_ASSIGN(stock_id,@object_id,6)
  .TYPE_ASSIGN(type_bill_display_id,1,6)
  .TYPE_ASSIGN(type_exec_id,@type_exec_id1,6)
  .TYPE_ASSIGN(price_position_id,@tarif_id,6)
  .TYPE_ASSIGN(service_id,@service_id1,6)
  .TYPE_ASSIGN(contract_id,@contract_id,6)
  .TYPE_ASSIGN(contragent_id,@contragent_id,6)
  .TYPE_INSERT('BILL_OUT_COM_OPER','CRT',6)

  end

  end

   select @d_s_id=depo_subacc_id,
          @b_a_id=bank_account_id
          from tb_baysale_docitems
          where tb_baysale_docitems.id=@object_id

  .TYPE_GET('DEALINGS_VIA_BROKER',@object_id,7)

  if @ticket_type_id7=1
     select @bay_or_sale=1
  else
     select @bay_or_sale=-1

  if @character_id7=1
     select @investor_id=(select tb_broker_change.id from tb_broker_change
            where tb_broker_change.partner_id=@owner_id
            and  .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info'))
  else
     select @investor_id=@client_contract_id7

  .BACK_GET_OBJ2BROK2TS(@asset_place_id7,@owner_id,@dir_id,@obj_id)

  .TYPE_INIT('bill2dealing',5)
  .TYPE_ASSIGN(bank_account_id,@b_a_id,5)
  .TYPE_ASSIGN(bay_or_sale,@bay_or_sale,5)
  .TYPE_ASSIGN(cur_id,@cur_id7,5)
  .TYPE_ASSIGN(depo_subacc_id,@d_s_id,5)
  .TYPE_ASSIGN(investor_id,@investor_id,5)
  .TYPE_ASSIGN(item_id,@object_id,5)
  .TYPE_ASSIGN(object_id,@obj_id,5)
  .TYPE_ASSIGN(portfolio_id,@portfolio_id7,5)
  .TYPE_ASSIGN(portfolio_sub_id,@portfolio_sub_id7,5)
  .TYPE_ASSIGN(pos2bill_id,@id3_1,5)
  .TYPE_ASSIGN(stock_id,@stock_id7,5)
  .TYPE_ASSIGN(stock_qty,@stock_qty7,5)
  .TYPE_ASSIGN(deal_sum,@summ_doc_total7,5)
  .TYPE_ASSIGN(price_position_id,@tarif_id,5)
  .TYPE_INSERT('bill2dealing','CRT',5)

  end
/*-------Проводки ----------*/

if object_id('tempdb..#oborot') is not null
   truncate table #oborot

if object_id('tempdb..#stocks') is not null
   truncate table #stocks

delete t_items
from t_types,
     td_depo_doc2bill
where td_depo_doc2bill.id = t_items.id
  and t_items.type_id =t_types.id
  and upper(t_types.type_code)='BILL_IN_COM_OPER'
  and isnull(td_depo_doc2bill.doc_sum,0)=0
  and bill_id=@bill_id

delete t_items
from t_types,
     td_depo_doc2bill
where td_depo_doc2bill.id = t_items.id
  and t_items.type_id =t_types.id
  and upper(t_types.type_code)='BILL_OUT_COM_OPER'
  and isnull(td_depo_doc2bill.doc_sum,0)=0
  and bill_id=@bill_id

delete td_depo_doc2bill
where isnull(td_depo_doc2bill.doc_sum,0)=0
  and bill_id=@bill_id

end