create procedure dbo.%PROC% @sid varchar(30) = null
as
declare @action_id numeric,
        @id numeric,
        @user_id numeric,
        @state_id numeric,
        @new_state_id numeric,
        @type_id numeric
begin tran

  select @action_id = %ACTION_ID%
   select @id = convert(numeric, @sid)

.CHECK_USER
.CHECK_UPDATE

.UPDATE_STATE
.ACTION_HISTORY

/*-------Проводки ----------*/
.GET_PROPS
declare @claim_id numeric(18,0)
declare @paymt_id numeric(18,0)
declare @paymt_id_str varchar(255)

declare @op_date datetime,
        @op_time datetime,
        @reg_date datetime,
        @reg_time datetime,
        @assent_date datetime,
        @op_type_name varchar(255),
        @asset_name varchar(255),
        @op_sum money,
        @op_qty money,
        @doc_name varchar(255),
        @fund_id numeric(18,0),
        @item_id numeric(18,0),
        @asset_id numeric(18,0),
        @sd_id numeric(18,0)






if @date_b is null
   begin
select @date_b = @date_e
   end



select @date_b = @date_b + convert(datetime,'00:00:00',8)
select @date_e = @date_e + convert(datetime,'23:59:59',8)



delete from t_sd_asset_ops
 where convert(datetime,convert(varchar(30),op_date,3),3) between @date_b and @date_e
       and item_id is not null



create table #jur(
deb_or_cred int null,
doc_id numeric(18,0),
sum money null,
qty money null,
asset_id numeric(18,0) null,
fond_id numeric(18,0) null,
reg_date datetime null,
op_date datetime null,
asset_date datetime null,
asset_name varchar(100) null,
op_type_name varchar(255) null,
doc_name varchar(255) null,
sub_type_id numeric(18,0) null,
op_no varchar(50) null
)

-----------   Добавлено для DEUTCHE  -----------------
create table #jur1(
doc_id numeric(18,0),
sum money null
)

create table #jur2(
deb_or_cred int null,
doc_id numeric(18,0),
sum money null,
qty money null,
asset_id numeric(18,0) null,
fond_id numeric(18,0) null,
reg_date datetime null,
op_date datetime null,
asset_date datetime null,
asset_name varchar(100) null,
op_type_name varchar(255) null,
doc_name varchar(255) null,
sub_type_id numeric(18,0) null,
op_no varchar(50) null
)

--------------------------------------------




.ID_VAR(@plan_id)
.ID_VAR(@p2p_id)
.ID_VAR(@acc581_id)
.ID_VAR(@acc76_id)
.ID_VAR(@acc51_id)
.ID_VAR(@acc582_id)
.ID_VAR(@s1_id)
.ID_VAR(@s2_id)
.ID_VAR(@s3_id)


select @s2_id=.SUBCTYPE_FROM_CODE('bank_account')
select @s3_id=.SUBCTYPE_FROM_CODE('stock_emis')


select @plan_id = .ARG_PLAN('PIF_ACCOUNT')



select @acc51_id =   .SD_ACCID_FROM_EFFNO(@plan_id,'51')
select @acc582_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.2')
select @acc581_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @acc76_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.1')

select @p2p_id=pp.id from t_plans2partner pp where pp.plan_id=@plan_id
   and @plan_id is not null and pp.partner_id=@owner_id and .ITEMS_EXISTS(pp.id)




if @owner_id is null
begin
insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,op_date, op_type_name)
select ol.deb_or_cred,
       o.item_id,
       sum(ol.sum_bal),
       sum(ol.qty_sum),
       t_oper_subconto.item_id,
       pp.partner_id,
       max(.DATE_NORM(o.op_date,B)) + convert(datetime,convert(varchar,max(o.sysdate),108),108),
     --  t_oper_subconto.subconto_type_id
      (case when ol.deb_or_cred=1 then 'Покупка ценных бумаг' else 'Продажа ценных бумаг' end)
  from t_operations o, t_oper_list ol, t_oper_subconto, t_plans2partner pp ,t_items, t_types
 where pp.plan_id=@plan_id
   and o.partner2plan_id = pp.id
   and ol.id = t_oper_subconto.id
   and convert(datetime,convert(varchar(30),o.op_date,3),3) between @date_b and @date_e
   and ol.head_id=o.id
   and o.item_id = t_items.id
   and t_items.type_id = t_types.id
   and t_types.type_code not in ('MANOPER')
   and ol.acc_id in (@acc581_id,@acc76_id)
   and t_oper_subconto.subconto_type_id in (@s3_id)
group by o.item_id, convert(datetime,convert(varchar(30),o.op_date,3),3), ol.deb_or_cred,t_oper_subconto.item_id,pp.partner_id,o.op_name




insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,op_date, op_type_name)
select ol.deb_or_cred,
       o.item_id,
       sum(ol.sum_bal),
       sum(ol.qty_sum),
       t_oper_subconto.item_id,
       pp.partner_id,
       max(.DATE_NORM(o.op_date,B)) + convert(datetime,convert(varchar,max(o.sysdate),108),108),
     --  t_oper_subconto.subconto_type_id
       (case when ol.deb_or_cred=1 then 'Зачисление на расчетный счет' else 'Списание с  расчетного счета' end)
  from t_operations o, t_oper_list ol, t_oper_subconto, t_plans2partner pp, t_items, t_types
 where pp.plan_id=@plan_id
   and o.partner2plan_id = pp.id
   and ol.id = t_oper_subconto.id
   and convert(datetime,convert(varchar(30),o.op_date,3),3) between @date_b and @date_e
   and ol.head_id=o.id
   and o.item_id = t_items.id
   and t_items.type_id = t_types.id
   and t_types.type_code not in ('MANOPER')
   and ol.acc_id in (@acc51_id,@acc582_id)
   and t_oper_subconto.subconto_type_id in (@s2_id)
group by o.item_id, convert(datetime,convert(varchar(30),o.op_date,3),3), ol.deb_or_cred,t_oper_subconto.item_id,pp.partner_id,o.op_name
end
 else
  begin

insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,op_date, op_type_name)
select ol.deb_or_cred,
       o.item_id,
       sum(ol.sum_bal),
       sum(ol.qty_sum),
       t_oper_subconto.item_id,
       pp.partner_id,
       max(convert(datetime,convert(varchar(30),o.op_date,3),3)) + convert(datetime,convert(varchar,max(o.sysdate),108),108),
     --  t_oper_subconto.subconto_type_id
      (case when ol.deb_or_cred=1 then 'Покупка ценных бумаг' else 'Продажа ценных бумаг' end)
  from t_operations o, t_oper_list ol, t_oper_subconto, t_plans2partner pp, t_items, t_types
 where pp.plan_id=@plan_id
   and o.partner2plan_id = pp.id
   and pp.partner_id = @owner_id
   and ol.id = t_oper_subconto.id
   and convert(datetime,convert(varchar(30),o.op_date,3),3) between @date_b and @date_e
   and ol.head_id=o.id
   and o.item_id = t_items.id
   and t_items.type_id = t_types.id
   and t_types.type_code not in ('MANOPER')
   and ol.acc_id in (@acc581_id,@acc76_id)
   and t_oper_subconto.subconto_type_id in (@s3_id)
group by o.item_id, convert(datetime,convert(varchar(30),o.op_date,3),3), ol.deb_or_cred,t_oper_subconto.item_id,pp.partner_id,o.op_name




insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,op_date, op_type_name)
select ol.deb_or_cred,
       o.item_id,
       sum(ol.sum_bal),
       sum(ol.qty_sum),
       t_oper_subconto.item_id,
       pp.partner_id,
      max(convert(datetime,convert(varchar(30),o.op_date,3),3)) + convert(datetime,convert(varchar,max(o.sysdate),108),108),
     --  t_oper_subconto.subconto_type_id
       (case when ol.deb_or_cred=1 then 'Зачисление на расчетный счет' else 'Списание с  расчетного счета' end)
  from t_operations o, t_oper_list ol, t_oper_subconto, t_plans2partner pp, t_items, t_types
 where pp.plan_id=@plan_id
   and o.partner2plan_id = pp.id
   and pp.partner_id = @owner_id
   and ol.id = t_oper_subconto.id
   and o.item_id = t_items.id
   and t_items.type_id = t_types.id
   and t_types.type_code not in ('MANOPER')
   and convert(datetime,convert(varchar(30),o.op_date,3),3) between @date_b and @date_e
   and ol.head_id=o.id
   and ol.acc_id in (@acc51_id,@acc582_id)
   and t_oper_subconto.subconto_type_id in (@s2_id)
group by o.item_id, convert(datetime,convert(varchar(30),o.op_date,3),3), ol.deb_or_cred,t_oper_subconto.item_id,pp.partner_id,o.op_name

  end


/*
insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,op_date, op_type_name)
select ol.deb_or_cred,
       o.item_id,
       sum(ol.sum_bal),
       sum(ol.qty_sum),
       t_oper_subconto.item_id,
       pp.partner_id,
       max(o.op_date) + convert(datetime,convert(varchar,max(o.sysdate),108),108),
   --    t_oper_subconto.subconto_type_id
       (case when ol.deb_or_cred=1 then 'Зачисление на расчетный счет' else 'Списание с  расчетного счета' end)
  from t_operations o, t_oper_list ol, t_oper_subconto, t_plans2partner pp
 where pp.plan_id=@plan_id
   and o.partner2plan_id = pp.id
   and ol.id = t_oper_subconto.id
   and convert(datetime,convert(varchar(30),o.op_date,3),3) between @date_b and @date_e
   and ol.head_id=o.id
   and ol.acc_id in (@acc582_id)
   and t_oper_subconto.subconto_type_id in (@s2_id)
   and  o.item_id not in (select doc_id from #jur)
group by o.item_id, convert(datetime,convert(varchar(30),o.op_date,3),3), ol.deb_or_cred,t_oper_subconto.item_id,pp.partner_id,o.op_name
*/


------------------   Добавлено для DEUTCHE ---------------
if rtrim(ltrim(.GET_CONST('CLIENT_CODE'))) = 'DEUTCHE'
begin

insert into #jur1(doc_id,sum)
select doc_id,sum(sum) from #jur group by doc_id


insert into #jur2(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,reg_date,op_date,asset_date,asset_name,op_type_name,
doc_name,sub_type_id,op_no)
select a.deb_or_cred,a.doc_id,b.sum,a.qty,asset_id,a.fond_id,a.reg_date,a.op_date,a.asset_date,a.asset_name,a.op_type_name,
a.doc_name,a.sub_type_id,a.op_no from #jur a,#jur1 b where a.doc_id=b.doc_id


delete  from #jur2 where qty is null


delete from #jur

insert into #jur(deb_or_cred,doc_id,sum,qty,asset_id,fond_id,reg_date,op_date,asset_date,asset_name,op_type_name,
doc_name,sub_type_id,op_no)
select deb_or_cred,doc_id,sum,qty,asset_id,fond_id,reg_date,op_date,asset_date,asset_name,op_type_name,
doc_name,sub_type_id,op_no from #jur2

drop table #jur1
drop table #jur2

end

----------------------------------------------




Update #jur
   SET reg_date = t_sd_info4oper.reg_date + isnull(.TIME_FROM_DATE(t_sd_info4oper.reg_time),convert(datetime,'00:00:00',8)),
       asset_date = t_sd_info4oper.assent_date,
       doc_name = t_sd_info4oper.doc_name
  from t_sd_info4oper
 where t_sd_info4oper.id = #jur.doc_id



Update #jur
   Set reg_date = t_sd_info4oper.reg_date + isnull(.TIME_FROM_DATE(t_sd_info4oper.reg_time),convert(datetime,'00:00:00',8)),
       asset_date = t_sd_info4oper.assent_date,
       doc_name = t_sd_info4oper.doc_name
  from t_sd_info4oper , td_depo_docs , td_depo_docs dd, t_items , t_types
 where t_sd_info4oper.id = td_depo_docs.id
       and td_depo_docs.id = dd.foundation_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('DOG_DEALINGS_BAY_VBR','DOG_DEALINGS_SALE_VBR','REAL_PAYMENT_IN','REAL_PAYMENT_OUT')
       and dd.id = #jur.doc_id





Update #jur
   SET reg_date = td_depo_docs.in_date + isnull(.TIME_FROM_DATE(deal.in_time),convert(datetime,'00:00:00',8))
  from td_depo_docs,  t_items, t_types , #jur , tb_baysale_docitems deal
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_IN','D_STOCK_OUT','D_STOCK_IN_OTHER_DEPO')
       and reg_date is null
       and doc_id = #jur.doc_id
       and deal.id = td_depo_docs.foundation_id
       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.foundation_id)




Update #jur
   SET reg_date = td_depo_docs.in_date
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_IN','D_STOCK_OUT')
       and reg_date is null
       and doc_id = #jur.doc_id





Update #jur
   SET reg_date = td_depo_docs.in_date + isnull(.TIME_FROM_DATE(deal.in_time),convert(datetime,'00:00:00',8))
  from td_depo_docs,  t_items, t_types , #jur , tb_baysale_docitems deal
 where deal.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('DEALINGS_VIA_BROKER_SALE','DEALINGS_VIA_BROKER')
       and reg_date is null
       and doc_id = #jur.doc_id
       and deal.id = td_depo_docs.id
       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.id)


Update #jur
   SET reg_date = td_depo_docs.in_date
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT','STOCK_REDEMPTION')
       and reg_date is null
       and doc_id = #jur.doc_id




Update #jur
   SET reg_date = t_paydocs.doc_date
  from t_paydocs,  t_items, t_types , #jur
 where t_paydocs.id = #jur.doc_id
       and t_paydocs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('SD_PAYBANK_IN','SD_PAYBANK_OUT')
       and reg_date is null
       and doc_id = #jur.doc_id



Update #jur
   SET asset_date = td_depo_docs.in_date
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT','STOCK_REDEMPTION')
       and asset_date is null
       and doc_id = #jur.doc_id




Update #jur
   SET asset_date = t_paydocs.doc_date
  from t_paydocs,  t_items, t_types , #jur
 where t_paydocs.id = #jur.doc_id
       and t_paydocs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('SD_PAYBANK_IN','SD_PAYBANK_OUT')
       and asset_date is null
       and doc_id = #jur.doc_id



Update #jur
   SET asset_name = td_stock_emis.stock_name
  from   #jur,  td_stock_emis
 where td_stock_emis.id = #jur.asset_id
       and asset_id = #jur.asset_id



Update #jur
   SET asset_name = t_bank_accounts.bank_account_no + ' ' + isnull(t_bank_accounts.bank_name,'')
  from   #jur,  t_bank_accounts
 where t_bank_accounts.id = #jur.asset_id
       and asset_id = #jur.asset_id





Update #jur
   SET doc_name  = 'Поручение №' + ' ' + td_depo_docs.in_no + ' ' + 'от' + ' ' +  convert(varchar(30),td_depo_docs.in_date,103)
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_IN','D_STOCK_OUT')
       and doc_name is null
       and doc_id = #jur.doc_id
--       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.foundation_id)




Update #jur
   SET doc_name  = 'Поручение Прием ЦБ во внешнем депозитарии №' + ' ' + td_depo_docs.in_no + ' ' + 'от' + ' ' +  convert(varchar(30),td_depo_docs.in_date,103)
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_IN_OTHER_DEPO')
       and doc_name is null
       and doc_id = #jur.doc_id
--       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.foundation_id)




Update #jur
   SET doc_name  = 'Поручение Снятие ЦБ во внешнем депозитарии №' + ' ' + td_depo_docs.in_no + ' ' + 'от' + ' ' +  convert(varchar(30),td_depo_docs.in_date,103)
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_OUT_OTHER_DEPO')
       and doc_name is null
       and doc_id = #jur.doc_id
--       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.foundation_id)





/*
Update #jur
   SET doc_name  = 'Поручение №' + ' ' + td_depo_docs.in_no + ' ' + 'от' + ' ' +  convert(varchar(30),td_depo_docs.in_date,103)
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in ('D_STOCK_IN','D_STOCK_OUT')
       and doc_name is null
       and doc_id = #jur.doc_id
--       and exists (select 1 from tb_baysale_docitems where tb_baysale_docitems.id=td_depo_docs.foundation_id)
*/




Update #jur
   SET doc_name = 'Платежное поручение №' + ' ' + td_depo_docs.in_no + ' ' + 'от' + ' ' +  convert(varchar(30),td_depo_docs.in_date,103)
  from td_depo_docs,  t_items, t_types , #jur
 where td_depo_docs.id = #jur.doc_id
       and td_depo_docs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('REAL_PAYMENT_IN','REAL_PAYMENT_OUT')
       and doc_name is null
       and doc_id = #jur.doc_id



Update #jur
   SET doc_name = 'Платежное поручение для банка по фонду №' + ' ' + t_paydocs.doc_number + ' ' + 'от' + ' ' +  convert(varchar(30),t_paydocs.doc_date,103)
  from t_paydocs,  t_items, t_types , #jur
 where t_paydocs.id = #jur.doc_id
       and t_paydocs.id = t_items.id
       and t_items.type_id = t_types.id
       and t_types.type_code in  ('SD_PAYBANK_IN','SD_PAYBANK_OUT')
       and doc_name is null
       and doc_id = #jur.doc_id




declare cur cursor for
select .DATE_NORM(op_date,B),
       .TIME_FROM_DATE(op_date),
       .DATE_NORM(reg_date,B),
       .TIME_FROM_DATE(reg_date),
       asset_date,
       op_type_name,
       asset_name,
       sum,
       qty,
       doc_name,
       fond_id,
       doc_id,
       asset_id
from #jur
order by .DATE_NORM(#jur.reg_date,B), .TIME_FROM_DATE(#jur.reg_date), #jur.doc_id
for read only
open cur

fetch cur into @op_date, @op_time, @reg_date, @reg_time, @assent_date, @op_type_name, @asset_name, @op_sum, @op_qty, @doc_name, @fund_id, @item_id, @asset_id

while  .cursor_state = 0
 begin

/*
.GET_ID(@sd_id)

insert into t_sd_asset_ops(id,op_date, op_time, reg_date, reg_time, assent_date, op_type_name, asset_name, op_sum, op_qty, doc_name, fund_id, item_id, asset_id)
values(@sd_id,
       @op_date,
       @op_date,
       @reg_date,
       @reg_date,
       @assent_date,
       @op_type_name,
       @asset_name,
       @op_sum,
       @op_qty,
       @doc_name,
       @fund_id,
       @item_id,
       @asset_id)
*/


.TYPE_INIT('UK_ASSET_OPERS', 1)
     .TYPE_ASSIGN(op_date,@op_date, 1)
     .TYPE_ASSIGN(op_time,@reg_time, 1)
     .TYPE_ASSIGN(reg_date,@reg_date, 1)
     .TYPE_ASSIGN(reg_time,@reg_time, 1)
     .TYPE_ASSIGN(assent_date,@assent_date, 1)
     .TYPE_ASSIGN(op_type_name,@op_type_name, 1)
     .TYPE_ASSIGN(asset_name,@asset_name, 1)
     .TYPE_ASSIGN(op_sum,@op_sum, 1)
     .TYPE_ASSIGN(op_qty,@op_qty, 1)
     .TYPE_ASSIGN(doc_name,@doc_name, 1)
     .TYPE_ASSIGN(fund_id,@fund_id, 1)
     .TYPE_ASSIGN(item_id,@item_id, 1)
     .TYPE_ASSIGN(asset_id,@asset_id, 1)
.TYPE_INSERT('UK_ASSET_OPERS','CRT', 1)







fetch cur into @op_date, @op_time, @reg_date, @reg_time, @assent_date, @op_type_name, @asset_name, @op_sum, @op_qty, @doc_name, @fund_id, @item_id, @asset_id

 end

close cur

.DEALLOCATE cur

/*
insert into t_sd_asset_ops(op_date, op_time, reg_date, reg_time, assent_date, op_type_name, asset_name, op_sum, op_qty, doc_name, fund_id, item_id, asset_id)
select .DATE_NORM(op_date,B),
       .TIME_FROM_DATE(op_date),
       .DATE_NORM(reg_date,B),
       .TIME_FROM_DATE(reg_date),
       asset_date,
       op_type_name,
       asset_name,
       sum,
       qty,
       doc_name,
       fond_id,
       doc_id,
       asset_id
from #jur
*/



drop table #jur





.ARH_NUMBER_UPDATE
commit tran