create procedure dbo.%PROC%
@owner_id numeric,
@rate_plase_id numeric,
@date_b datetime,
@date_e datetime,
@pos_id numeric,
@tarif_id numeric,
@contract_id numeric,
@sale_type_id numeric,
@object_id numeric,
@type_bill_display_id numeric,
@service_id numeric
as
---begin tran
begin

---.EXIT_MESSAGE_PARM('@pos_id',@pos_id)

declare @sale_type_id_bb numeric,
@sale_type_id_ee numeric,
@external_broker_id_bb numeric,
@external_broker_id_ee numeric,
@client_contract_id_bb numeric,
@client_contract_id_ee numeric,
@rate_plase_id_bb numeric,
@rate_plase_id_ee numeric

if @sale_type_id=0 or @sale_type_id is null
   select @sale_type_id_bb=1,
          @sale_type_id_ee=20
else
   select @sale_type_id_bb=@sale_type_id,
          @sale_type_id_ee=@sale_type_id

if @rate_plase_id is null
   select @rate_plase_id_bb=-999999999999999998,
          @rate_plase_id_ee=999999999999999999
else
   select @rate_plase_id_bb=@rate_plase_id,
          @rate_plase_id_ee=@rate_plase_id

if exists(select 1 from tb_contracts where id=@contract_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'CLIENT_CONTRACTS'))
begin
   select @external_broker_id_bb=-999999999999999998,
          @external_broker_id_ee=999999999999999999,
          @client_contract_id_bb=@contract_id,
          @client_contract_id_ee=@contract_id

end
else
begin

select @client_contract_id_bb=-999999999999999998,
       @client_contract_id_ee=999999999999999999

if exists(select 1 from tb_contracts where id=@contract_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'ORGANIZATION_CONTRACTS')
            and dog_type_id in (2,4,5))
      select @external_broker_id_bb=-999999999999999998,
             @external_broker_id_ee=999999999999999999
else if exists(select 1 from tb_contracts where id=@contract_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'ORGANIZATION_CONTRACTS')
            and dog_type_id=1)
      select @external_broker_id_bb=@contract_id,
             @external_broker_id_ee=@contract_id
end

if @type_bill_display_id=1
begin

if @service_id<>6
begin
declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s where
                i.id=td_depo_docs.id 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 ('EDIT','ED')
               and (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
               and isnull(oper_date,in_date)>=@date_b and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
               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
for read only
end else
begin

declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s where
                i.id=td_depo_docs.id 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 ('EDIT','ED')  and
              (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
              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.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
for read only



end

end else
if @type_bill_display_id=2
begin

if @service_id<>6
begin
declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s where
                i.id=td_depo_docs.id 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 ('EDIT','ED')  and
              (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
               and isnull(oper_date,in_date)>=@date_b and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
               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 tb_baysale_docitems.stock_id=@object_id
for read only
end else
begin
declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s where
                i.id=td_depo_docs.id 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 ('EDIT','ED')  and
              (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
               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.stock_id=#stocks.stock_id and tb_baysale_docitems.depo_subacc_id in (select #depo_subacc.sub_id from #depo_subacc)
               and tb_baysale_docitems.stock_id=@object_id
for read only

end

end else
if @type_bill_display_id=3
begin

if @service_id<>6
begin
declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s,td_depo_subacc ds where
                i.id=td_depo_docs.id 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 ('EDIT','ED')  and
              (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
               and isnull(oper_date,in_date)>=@date_b and isnull(oper_date,in_date)<dateadd(day,1,@date_e)
               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
               and ds.depo_acc_id=@object_id
for read only
end else
begin
declare curs cursor for
 select tb_baysale_docitems.id,depo_subacc_id,bank_account_id
         from tb_baysale_docitems,#stocks,td_depo_docs,t_items i,t_types t,t_states s,td_depo_subacc ds where
                i.id=td_depo_docs.id 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 ('EDIT','ED')  and
              (isnull(tb_baysale_docitems.sale_type_id,0) between @sale_type_id_bb and @sale_type_id_ee) and
              ((isnull(tb_baysale_docitems.external_broker_id,0) between @external_broker_id_bb and @external_broker_id_ee)) and
                td_depo_docs.owner_id=@owner_id and td_depo_docs.id=tb_baysale_docitems.id and
              ((isnull(tb_baysale_docitems.client_contract_id,0) between @client_contract_id_bb and @client_contract_id_ee)) and
               (sale_place_id between @rate_plase_id_bb and @rate_plase_id_ee)
              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.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
              and ds.depo_acc_id=@object_id
for read only

end

end


declare
@obj_id numeric,
@bay_or_sale int,
@investor_id numeric,
@dir_id numeric,
@b_a_id numeric,
@d_s_id numeric,
@d_id numeric

  open curs
  fetch curs into @d_id,@d_s_id,@b_a_id

  while .CURSOR_STATE=0
  begin

  .TYPE_GET('DEALINGS_VIA_BROKER',@d_id,4)

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

  if @character_id4=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_id4

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

 begin tran

  .TYPE_INIT('bill2dealing',4)
  .TYPE_ASSIGN(bank_account_id,@b_a_id,4)
  .TYPE_ASSIGN(bay_or_sale,@bay_or_sale,4)
  .TYPE_ASSIGN(cur_id,@cur_id4,4)
  .TYPE_ASSIGN(depo_subacc_id,@d_s_id,4)
  .TYPE_ASSIGN(investor_id,@investor_id,4)
  .TYPE_ASSIGN(item_id,@d_id,4)
  .TYPE_ASSIGN(object_id,@obj_id,4)
  .TYPE_ASSIGN(portfolio_id,@portfolio_id4,4)
  .TYPE_ASSIGN(portfolio_sub_id,@portfolio_sub_id4,4)
  .TYPE_ASSIGN(pos2bill_id,@pos_id,4)
  .TYPE_ASSIGN(stock_id,@stock_id4,4)
  .TYPE_ASSIGN(stock_qty,@stock_qty4,4)
  .TYPE_ASSIGN(deal_sum,@summ_doc_total4,4)
  .TYPE_ASSIGN(price_position_id,@tarif_id,4)
  .TYPE_INSERT('bill2dealing','CRT',4)

 commit tran

---  select @id4_1

  fetch curs into @d_id,@d_s_id,@b_a_id

  end

  close curs
  .DEALLOCATE curs

end