create procedure dbo.%PROC%
  @broker_id_s varchar(30) = null, /* Брокер */
  @date_b_d varchar(30) = null,
  @date_e_d varchar(30) = null,
  @investor_id_s varchar(30) = null  --клиентский договор

as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @id numeric
begin

select @action_id = %ACTION_ID%

declare @broker_id numeric
declare @date_b datetime
declare @date_e datetime
declare @cur_plan numeric
declare @partner_id numeric
declare @class_type_id numeric
declare @subc_str_bal varchar(255)
declare @subc_type_id numeric
declare @acc210_num_eff varchar(255)
declare @acc210_id numeric
declare @mess varchar(255)
declare @investor_id varchar(30)

select @broker_id = convert(numeric, @broker_id_s)
select @investor_id = convert(numeric, @investor_id_s)


select @partner_id = partner_id,
       @cur_plan = back_money_id
    from tb_broker_change
    where id = @broker_id

select @date_b = convert(datetime, @date_b_d, 3)
select @date_e = convert(datetime, @date_e_d, 3)

select @subc_str_bal=''
.SUBC_STR_ADD(@subc_str_bal,'STOCK_EMIS',null,Y)
.SUBC_STR_ADD(@subc_str_bal,'investors',@investor_id,Y)

select @subc_type_id = .SUBCTYPE_FROM_CODE('STOCK_EMIS')

.ACCID_FROM_CONST2PLAN(@acc210_id,'BACK_ACC_DIVIDENDS', @cur_plan, 1, 1,'Счёт по дивидендам', @mess)

select @acc210_num_eff = acc_num_eff from t_accounts where id = @acc210_id

declare @plan2partner_id numeric
select @plan2partner_id=.PLAN2PARTNER_ID(@partner_id, @cur_plan)


create table #report(
  emi_name varchar(255) null,
  stock_type varchar(255) null,
  data_post datetime null,
  god_pay int null,
  calc_prof money null,
  tax money null,
  pay_sum money null
)

/*
insert into #report(
  emi_name,
  stock_type,
  data_post,
  god_pay,
  calc_prof,
  tax,
  pay_sum)

select tei.emi_name, t1.item_name, tdq.date_b, datepart(year,tdq.date_e),
  tdv.div_sum, tdv.nalog_div_sum, div_sum_total

  from td_depo_docs tdd, td_devidend tdv, td_queries tdq, t_items t1,
    td_stock_emis tde, td_emitent_info tei

  where tdd.id = tdv.query_id
    and tdq.id = tdd.id
    and .ITEMS_EXISTS_BY_TYPE(tdv.id, 'DIVIDEND_RESULT')
    and t1.id = tdv.stock_id
    and tde.id = tdv.stock_id
    and tei.id = tde.emmitent_id
    and tdv.part_id = @partner_id
*/

insert into #report(
  emi_name,
  stock_type,
  data_post,
  god_pay,
  calc_prof,
  tax,
  pay_sum)

select
  (select tei.emi_name from td_stock_emis tde, td_emitent_info tei where tde.id = tos.item_id and tei.id = tde.emmitent_id),
  (select tst.stock_type_name from td_stock_emis tde, t_stock_types tst where tde.id = tos.item_id and tst.id = tde.stock_type_id),
  o.op_date,
  datepart(year, o.op_date),
  ol.sum_bal,
  null,
  null

from t_operations o, t_oper_list ol, 
t_accounts a, td_depo_docs tdd,
t_purposes tp, t_oper_subconto tos,
ts_sd_stock_moves, 
tb_list_stock_redemption, t_items i

where ol.partner2plan_id = @plan2partner_id
  and ol.acc_id = a.id
  and a.acc_num_eff like @acc210_num_eff+'%'
  and ol.head_id = o.id
  and ol.subc_eff_num like '%'+@subc_str_bal+'%'
  and tos.id = ol.id
  and tos.subconto_type_id = @subc_type_id
--
  and o.item_id=i.id
  and tb_list_stock_redemption.id=i.id
  and tb_list_stock_redemption.item_id= ts_sd_stock_moves.id
  and ts_sd_stock_moves.id=tdd.id 
  and tdd.perpose_id = tp.id
  and tp.code = 47
--
  and o.op_date >= @date_b
  and o.op_date <  dateadd(dd, 1, @date_e)

/*
delete #result
from t_types t,t_items i,td_depo_docs d
where i.id=#result.item_id
and t.id=i.type_id
and t.type_code not in ('REAL_PAYMENT_IN', 'REAL_PAYMENT_OUT',
'MONEY_MOVE_COMMISSION_2', 'MONEY_MOVE2INVESTORS', 'MANOPERLIST')
and d.id=#result.item_id
*/


select * from #report order by data_post, god_pay

end