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)


.GET_PROPS

.TRANSACTION_SAVE

declare @plan_id numeric
select @plan_id=.ARG_PLAN('PIF_ACCOUNT')

if @plan_id is null
.EXIT_MESSAGE('Не найден план PIF_ACCOUNT')

declare @plan2partner numeric

select @plan2partner=(select t_plans2partner.id from t_plans2partner,.ACTUAL_TABLES
where t_plans2partner.plan_id=@plan_id and t_plans2partner.partner_id=@fond_id
.ACTUAL_WHERE(t_plans2partner.id))

if @plan2partner is null
.EXIT_MESSAGE('У фонда нет плана счетов PIF_ACCOUNT')

declare @acc_51 numeric
declare @acc_57 numeric
declare @acc_58_1 numeric
declare @acc_58_2 numeric
declare @acc_58_3 numeric
declare @acc_08_1 numeric
declare @acc_01_1 numeric
select @acc_51 = .SD_ACCID_FROM_EFFNO(@plan_id,'51')
select @acc_57 = .SD_ACCID_FROM_EFFNO(@plan_id,'57')
select @acc_01_1 = .SD_ACCID_FROM_EFFNO(@plan_id,'01.1')
select @acc_58_1 = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @acc_58_3 = .SD_ACCID_FROM_EFFNO(@plan_id,'58.3')
select @acc_58_2 = .SD_ACCID_FROM_EFFNO(@plan_id,'58.2')
select @acc_08_1 = .SD_ACCID_FROM_EFFNO(@plan_id,'08.1')

create table #oper_list(
  oper_list_id numeric null,
  object_qty money null,
  bal_sum money null,
  deb_or_cred int,
  acc_id numeric)

create table #report(
  object_id numeric null,
  object_qty money null,
  bal_sum money null,
  acc_id numeric)

declare @cur_period_id numeric,
@cur_period_date datetime

/*Ищем закрытый период*/

select @cur_period_id = (select id
  from t_periods
 where partner2plan_id = @plan2partner
   and period_date = (select max(period_date)
                      from t_periods
                      where partner2plan_id=@plan2partner
                        and period_date < @srez_date))


select @cur_period_date = (select period_date from t_periods where id = @cur_period_id)

declare @cur_stock_subconto numeric,
@cur_other_subconto numeric,
@cur_bank_subconto numeric,
@cur_doc_subconto numeric

select @cur_stock_subconto = .SUBCTYPE_FROM_CODE('stock_emis')
select @cur_other_subconto = .SUBCTYPE_FROM_CODE('other_assets')
select @cur_bank_subconto = .SUBCTYPE_FROM_CODE('bank_account')
select @cur_doc_subconto = .SUBCTYPE_FROM_CODE('doc_item')

if @cur_period_date is null
begin
  select @cur_period_id = null
  select @cur_period_date=convert(datetime, '1/1/1800', 103)
end
else
begin

/* Closed Period */

insert into #oper_list(oper_list_id,object_qty,bal_sum,deb_or_cred,acc_id)
select t_account_history.id,t_account_history.saldo_qty,t_account_history.saldo,t_account_history.deb_or_cred,t_account_history.acc_id
from t_account_history, t_acchistory_subconto subc1
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and t_account_history.acc_id in (@acc_58_2,@acc_58_3,@acc_08_1,@acc_01_1,@acc_51,@acc_57)
 and subc1.subconto_type_id!=@cur_doc_subconto

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_58_3,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty*#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_58_3
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_58_2,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_58_2
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_51,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_51
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_57,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_57
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_08_1,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_08_1
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_01_1,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0))
from #oper_list,t_acchistory_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_01_1
group by subc2.item_id

end

truncate table #oper_list

insert into #oper_list(oper_list_id,object_qty,bal_sum,deb_or_cred,acc_id)
select t_oper_list.id,t_oper_list.qty_sum,t_oper_list.sum_bal,t_oper_list.deb_or_cred,t_oper_list.acc_id
from t_oper_list, t_oper_subconto subc1
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and t_oper_list.acc_id in (@acc_58_2,@acc_58_3,@acc_08_1,@acc_01_1,@acc_51,@acc_57)
 and subc1.subconto_type_id!=@cur_doc_subconto

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_58_3,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_58_3
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_58_2,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_58_2
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_51,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_51
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_57,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
and #oper_list.acc_id=@acc_57
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_08_1,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty*#oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_08_1
group by subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum)
select
  @acc_01_1,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0))
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
and #oper_list.acc_id=@acc_01_1
group by subc2.item_id

truncate table #oper_list

delete t_items
  from tp_pif_srez_portfolio, t_types
 where t_items.id = tp_pif_srez_portfolio.id
   and tp_pif_srez_portfolio.srez_id = @id
   and t_types.id=t_items.type_id
   and upper(t_types.type_code)='PIF_SREZ_ACCOUNTS'

delete tp_pif_srez_portfolio
 where tp_pif_srez_portfolio.srez_id = @id
   and not exists(select 1 from t_items where id=tp_pif_srez_portfolio.id)

declare @c_object_id numeric,
@c_object_qty money,
@c_bal_sum money,
@c_acc_id numeric,
@cur_is_rate int,
@cur_rate_place_id numeric,
@rate_cur_id numeric,
@cur_stock_rate money,
@class_value_code varchar(30),
@class_comment varchar(255),
@rur_cur_id numeric,
@cur_stock_rur money,
@rate_value money,
@rate_date datetime

select @rur_cur_id=.CUR('RUR')


declare cur1 cursor for
select object_id,object_qty,
  bal_sum,acc_id from #report
open cur1
fetch cur1 into @c_object_id,
@c_object_qty,@c_bal_sum,@c_acc_id

while .CURSOR_STATE=0 begin

if (@c_object_qty <> 0)
begin

-- Котируемость
 exec ap_getclassvalue_code @c_object_id, 'STOCK_RATE', null, null, @class_value_code out, @class_comment out
 if Upper(@class_value_code) = 'YES'
   select @cur_is_rate = 1
 else
   select @cur_is_rate = 0

 select @cur_stock_rate = null

end

if not (@c_object_qty=0 and @c_acc_id in (@acc_58_3,@acc_08_1,@acc_01_1))
begin
 .TYPE_INIT('PIF_SREZ_ACCOUNTS')
 .TYPE_ASSIGN(srez_id,@id)
 .TYPE_ASSIGN(stock_sum,@c_bal_sum)
 .TYPE_ASSIGN(stock_rate,0)
 .TYPE_ASSIGN(stock_id,@c_object_id)
 .TYPE_ASSIGN(stock_qty,@c_object_qty)
 .TYPE_ASSIGN(bal_sum,@c_bal_sum)
 .TYPE_ASSIGN(acc_id,@c_acc_id)
 .TYPE_ASSIGN(is_rate,@cur_is_rate)
 .TYPE_INSERT('PIF_SREZ_ACCOUNTS','CRT')
end

fetch cur1 into @c_object_id,
@c_object_qty,@c_bal_sum,@c_acc_id
end
close cur1
.DEALLOCATE cur1

drop table #oper_list
drop table #report

.TRANSACTION_RESTORE

commit tran