# Date: 13/07/06
# Version: 105

$DOC(SD_OVERVALUE)
 name=Переоценка ЦБ
 class=1
 product=ALD_FUND
 name_formula=.TYPE_NAME(@type_id) + ' № ' + @in_no+' от '+.DATE_TO_STR(@in_date)
 remark_formula=null

 $STATE(BEG)
  name=Начальный
  class=1
 $ENDSTATE

 $STATE(DEL)
  name=Удален
  class=2
 $ENDSTATE

 $STATE(ED)
  name=Редактируется
  class=0
 $ENDSTATE

 $STATE(OP_END)
  name=Подтвержден
  class=0
 $ENDSTATE

 $PROP(IN_DATE)
  name=Дата документа
  field_name=in_date
  table_name=ts_sd_stock_overvalue
  field_type=datetime
  field_length=8
  field_scale=null
  field_prec=null
  page=1
  order=1
  init_class=1
  init_formula=.OPERDAY
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(IN_NO)
  name=Номер документа
  field_name=in_no
  table_name=ts_sd_stock_overvalue
  field_type=varchar
  field_length=50
  field_scale=null
  field_prec=null
  page=1
  order=2
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(INITIATOR_ID)
  name=Управляющий
  field_name=initiator_id
  table_name=ts_sd_stock_overvalue
  field_type=numeric
  field_length=9
  field_scale=0
  field_prec=18
  page=1
  order=3
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(OWNER_ID)
  name=Владелец
  field_name=owner_id
  table_name=ts_sd_stock_overvalue
  field_type=numeric
  field_length=9
  field_scale=0
  field_prec=18
  page=1
  order=4
  init_class=1
  init_formula=.ORGANIZATION
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(MOVE_COMMENT)
  name=Комментарий
  field_name=move_comment
  table_name=ts_sd_stock_overvalue
  field_type=varchar
  field_length=255
  field_scale=null
  field_prec=null
  page=1
  order=5
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(TYPE_OVERVALUE_ID)
  name=Тип переоценки
  field_name=type_overvalue_id
  table_name=ts_sd_stock_overvalue
  field_type=numeric
  field_length=9
  field_scale=0
  field_prec=18
  page=1
  order=6
  init_class=1
  init_formula=0
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(IS_FIX_RATE_DATE)
  name=Отбор котировок
  field_name=is_fix_rate_date
  table_name=ts_sd_stock_overvalue
  field_type=int
  field_length=4
  field_scale=null
  field_prec=null
  page=1
  order=7
  init_class=1
  init_formula=1
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(OPER_TYPE)
  name=Тип проведения
  field_name=oper_type
  table_name=ts_sd_stock_overvalue
  field_type=int
  field_length=4
  field_scale=null
  field_prec=null
  page=1
  order=8
  init_class=1
  init_formula=0
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $RELATION(REL3)
  class=0
  type=FUND_GROUP
  form=null
  prop=OWNER_ID
  idname=null
  order=2
 $ENDRELATION

 $RELATION(REL1)
  class=1
  type=t_operations
  form=item_opers_list
  prop=null
  idname=item_id_s
  order=130
 $ENDRELATION

 $RELATION(REL2)
  class=0
  type=partners
  form=null
  prop=INITIATOR_ID
  idname=null
  order=1
 $ENDRELATION

 $RELATION(REL4)
  class=1
  type=actions_history
  form=uo_actions_history_list
  prop=null
  idname=null
  order=120
 $ENDRELATION

 $RELATION(REL5)
  class=1
  type=SD_INDOC2DOC
  form=FORM2
  prop=null
  idname=doc_id_s
  order=135
 $ENDRELATION

 $FORM(D_SDEPO_OVERVALUE_OPER)
  name=Просмотр
  class=5
  filter=null
  target_state=
  uo=uo_edit
  dw=d_sdepo_overvalue_oper
  procedure=ap_60805000000048908
  patterns=GENERIC_VIEW_ITEM
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=4535
  height=1280
  proc_text=$TEXT(11035778)
create procedure dbo.%PROC%
  @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_VIEW_ITEM

if @id = -1
begin
begin tran
.GET_PROPS
.CHECK_INSERT
.INIT_VARS(B)
.INSERT_ITEMS
.INSERT
commit tran
end
.INSERT_INFO4OPER(@id)
.VIEW_ITEM

.END
  $ENDTEXT(11035778)

  $ACTION2RELATION(REL2.PARTNER_CODE)
   relation=REL2
   where=null
   prop=PARTNER_CODE
   order=19
   alias=initiator_code
  $ENDACTION2RELATION

  $ACTION2RELATION(REL2.PARTNER_SHORT_NAME)
   relation=REL2
   where=null
   prop=PARTNER_SHORT_NAME
   order=10
   alias=initiator_short_name
  $ENDACTION2RELATION

  $ACTION2RELATION(REL3.CTRL_COMP_ID)
   relation=REL3
   where=null
   prop=CTRL_COMP_ID
   order=2
   alias=ctrl_comp_id_s
  $ENDACTION2RELATION

  $ACTION2RELATION(REL3.PARTNER_CODE)
   relation=REL3
   where=null
   prop=PARTNER_CODE
   order=4
   alias=fond_code
  $ENDACTION2RELATION

  $ACTION2RELATION(REL3.PARTNER_SHORT_NAME)
   relation=REL3
   where=null
   prop=PARTNER_SHORT_NAME
   order=3
   alias=fond_name
  $ENDACTION2RELATION

  $BIND(BIND1)
   type=SD_INFO4OPER
   action=FORM_ED
   class=1
   order=2
   idname=sid
  $ENDBIND

  $BIND(BIND2)
   type=SD_OVERVALUE_LIST
   action=FORM2
   class=1
   order=1
   idname=doc_id_s
  $ENDBIND
 $ENDFORM

 $FORM(D_STOCK_OVERVALUE_FILTER)
  name=Фильтр
  class=6
  filter=null
  target_state=
  uo=uo_filter
  dw=d_sdepo_overvalue_filter
  procedure=ap_60805000000048913
  patterns=null
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=1563
  height=512
  proc_text=$TEXT(11035778)
create procedure dbo.%PROC%
as
begin

declare @in_date_db datetime
declare @in_date_de datetime
declare @owner_id numeric
declare @owner_name varchar(255)
select @in_date_db = .OPERDAY
select @in_date_de = @in_date_db
select @owner_id=.ORGANIZATION
select @owner_name=(select partner_short_name from t_partners where id=@owner_id)


select
convert(datetime, @in_date_db) as in_date_db,
convert(datetime, @in_date_de) as in_date_de,
convert(varchar(255), null) as in_no,
convert(varchar(30), @owner_id) as owner_id_s,
@owner_name as partner_name
end
  $ENDTEXT(11035778)

  $FILTER(IN_DATE)
   type=2
   label=Дата документа
   prop=IN_DATE
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(IN_NO)
   type=3
   label=Номер документа
   prop=IN_NO
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(OWNER_ID)
   type=1
   label=Фонд
   prop=OWNER_ID
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER
 $ENDFORM

 $FORM(D_STOCK_OVERVALUE_LIST)
  name=Список
  class=4
  filter=D_STOCK_OVERVALUE_FILTER
  target_state=
  uo=uo_list
  dw=d_sdepo_overvalue_list
  procedure=ap_60805000000048917
  patterns=GENERIC_VIEW_LIST
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=3685
  height=1012
  proc_text=$TEXT(11035778)
create procedure dbo.%PROC% @sid varchar(30) = null
   .ARGLIST_FILTER
as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric
begin

set nocount on

select @action_id = %ACTION_ID%

.CHECK_USER
.CHECK_VIEW

.ARGCONVERT_FILTER

.VIEW_LIST

end
  $ENDTEXT(11035778)

  $ACTION2RELATION(REL3.PARTNER_CODE)
   relation=REL3
   where=null
   prop=PARTNER_CODE
   order=4
   alias=partner_code
  $ENDACTION2RELATION

  $CALC(OP_DATE)
   class=1
   type=datetime
   calc_lenght=null
   calc_prec=null
   calc_scale=null
   formula=convert(datetime,null)
   label=null
   order=null
  $ENDCALC
 $ENDFORM

 $ACTION(ACTION1)
  name=Добавить
  class=1
  form=D_SDEPO_OVERVALUE_OPER
  target_state=ED
  procedure=ap_60805000000048920
  patterns=GENERIC_INSERT
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=3959
  height=1276
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null,
.ARGLIST
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.ARGCONVERT

.INIT_VARS

.TEST_UNIQUE

begin tran

.UPDATE_ITEMS

.UPDATE

.RELGEN
.UPDATE_STATE
.ACTION_HISTORY
commit tran
select sid = convert(varchar, @id)
.END
  $ENDTEXT(11035779)

  $BIND(BIND1)
   type=SD_INFO4OPER
   action=ACTION_UPDATE
   class=1
   order=10
   idname=sid
  $ENDBIND
 $ENDACTION

 $ACTION(ACTION2)
  name=Изменить
  class=2
  form=D_SDEPO_OVERVALUE_OPER
  target_state=null
  procedure=ap_60805000000048922
  patterns=GENERIC_UPDATE
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=4535
  height=1200
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null,
.ARGLIST
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.ARGCONVERT

.TEST_UNIQUE

begin tran
.UPDATE_ITEMS

.UPDATE

.RELGEN
.UPDATE_STATE
.ACTION_HISTORY
commit tran
.END
  $ENDTEXT(11035779)

  $BIND(BIND1)
   type=SD_OVERVALUE_LIST
   action=FORM2
   class=1
   order=1
   idname=doc_id_s
  $ENDBIND

  $BIND(BIND2)
   type=SD_INFO4OPER
   action=ACTION_UPDATE
   class=1
   order=2
   idname=sid
  $ENDBIND
 $ENDACTION

 $ACTION(ACTION3)
  name=На редактирование
  class=9
  form=null
  target_state=ED
  procedure=ap_60805000000048925
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

begin tran
.UPDATE_STATE
.ACTION_HISTORY

.OPERS_ROLL

update ts_sd_stock_overvalue_st set
 stock_qty=null,
 margin_sum=null
where doc_id=@id

.SD_ASSENT_DEL(@id)
.SD_ASSET_OPER_DEL(@id)

commit tran
.END
  $ENDTEXT(11035779)
 $ENDACTION

 $ACTION(ACTION4)
  name=Подтвердить
  class=9
  form=null
  target_state=OP_END
  procedure=ap_60805000000048926
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.GET_PROPS

if @oper_type in (0,1) and exists(select 1 from ts_sd_stock_overvalue_st
                                   where doc_id=@id and isnull(sum_one_stock,0)=0 and isnull(sum_main,0)=0)
 .EXIT_MESSAGE('Не заданы суммы для переоценки')

.TRANSACTION_SAVE

.ID_VAR(@plan_id)
.ID_VAR(@pp_id)
.ID_VAR(@acc581_id)
.ID_VAR(@acc5810_id)
.ID_VAR(@acc5811_id)
.ID_VAR(@acc766_id)
.ID_VAR(@add_acc_id)
.ID_VAR(@subc_type_id)
.ID_VAR(@uk_subc_type_id)
.ID_VAR(@part_subc_type_id)
.ID_VAR(@stock_id)
.ID_VAR(@asset_manager_id)
.ID_VAR(@part_id)
.ID_VAR(@st_cur_id)
.ID_VAR(@rur_cur_id)
.INT_VAR(@j_type)
.NAME_VAR(@fund_type)
.NAME_VAR(@doc_name)
declare
 @oper_date datetime,
 @get_date datetime,
 @cur_qty money,
 @cur_sum money,
 @cur_diff money,
 @c_cur_sum money,
 @c_cur_diff money,
 @all_sum money,
 @c_all_sum money,
 @all_qty money,
 @diff money,
 @diff_all money,
 @c_diff money,
 @c_diff_all money,
 @one_sum decimal(18,8),
 @c_one_sum decimal(18,8),
 @one_sum_rur decimal(36,12),
 @all_sum_rur decimal(36,12),
 @part_sum money,
 @c_part_sum money,
 @part_qty money,
 @c_op_qty money,
 @rate_value decimal(18,8),
 @rate_date datetime,
 @round_rate int

declare @sd_scha_2005_d datetime

select @sd_scha_2005_d=convert(datetime,(.GET_CONST('SD_SCHA_2005')),3)

select @doc_name=.ITEM_NAME(@id)
select @fund_type=upper(.ITEM_TYPE_CODE(@owner_id))

if @fund_type='PIF_FUND'
begin
 select @plan_id=.ARG_PLAN('PIF_ACCOUNT')
 select @add_acc_id = .SD_ACCID_FROM_EFFNO(@plan_id,'96.3.1')
end

if @fund_type='NPF_FUND'
begin
 select @plan_id=.ARG_PLAN('NPF_ACCOUNT')
 select @asset_manager_id=isnull(@initiator_id,@owner_id)
 if @owner_id = @asset_manager_id
  select @add_acc_id = .SD_ACCID_FROM_EFFNO(@plan_id,'80.2')
 else
  select @add_acc_id = .SD_ACCID_FROM_EFFNO(@plan_id,'80.1')
end

select @acc581_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.1')

select @acc766_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.6')
select @subc_type_id=.SUBCTYPE_FROM_CODE('stock_emis')
select @uk_subc_type_id=.SUBCTYPE_FROM_CODE('asset_manager')
select @part_subc_type_id=.SUBCTYPE_FROM_CODE('doc_item')
select @rur_cur_id=.CUR('RUR')
select @oper_date=@in_date
select @get_date=dateadd(dd,1,(.DATE_NORM(@in_date,B)))

if @sd_scha_2005_d <= @oper_date
begin
  select @acc5810_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.10')
  select @acc5811_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.11')
end
else
begin
  select @acc5810_id=-1
  select @acc5811_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
end

create table #parts (
 stock_id numeric not null,
 part_id numeric not null,
 part_sum money not null,
 c_part_sum money null,
 part_qty money not null
)

create table #stocks (
 stock_id numeric,
 cur_diff money null, -- проведенная прибыль
 cur_sum money null, -- проведенная
 c_cur_diff money null, -- проведенная прибыль по 76.6
 c_cur_sum money null, -- проведенная сумма по 76.6
 cur_qty money null, -- к-во проведенных
 bal_sum money not null, -- по балансу
 c_bal_sum money not null, -- по балансу УНКД
 all_qty money, -- к-во ЦБ
 one_sum decimal(18,8) null, -- заданная на 1 ЦБ
 all_sum money null, -- заданная общая
 c_one_sum decimal(18,8) null, -- заданный НКД на 1 ЦБ
 c_all_sum money null -- заданный НКД общий
)

select @pp_id=pp.id from t_plans2partner pp
 where pp.partner_id=@owner_id and pp.plan_id=@plan_id and .ITEMS_EXISTS(pp.id)

if @fund_type='PIF_FUND'
 insert into #parts (stock_id, part_id, part_sum, part_qty)
 select os.item_id, osp.item_id, sum(isnull(ol.sum_bal*ol.deb_or_cred,0)), sum(isnull(ol.qty_sum*ol.deb_or_cred,0))
   from t_oper_subconto os, t_oper_subconto osp, t_oper_list ol, t_operations o
  where o.partner2plan_id=@pp_id
    and ol.head_id=o.id and ol.acc_id in (@acc581_id, @acc5810_id, @acc5811_id)
    and os.id=ol.id and os.subconto_type_id=@subc_type_id
    and osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id
    and o.op_date<@get_date
    and os.item_id in (select stock_id from ts_sd_stock_overvalue_st where doc_id=@id)
  group by os.item_id, osp.item_id
 having sum(isnull(ol.qty_sum*ol.deb_or_cred,0))>0

if @fund_type='NPF_FUND'
 insert into #parts (stock_id, part_id, part_sum, part_qty)
 select os.item_id, osp.item_id, sum(isnull(ol.sum_bal*ol.deb_or_cred,0)), sum(isnull(ol.qty_sum*ol.deb_or_cred,0))
   from t_oper_subconto os, t_oper_subconto osp, t_oper_list ol, t_operations o
  where o.partner2plan_id=@pp_id
    and ol.head_id=o.id and ol.acc_id in (@acc581_id, @acc5810_id, @acc5811_id)
    and os.id=ol.id and os.subconto_type_id=@subc_type_id
    and osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id
    and o.op_date<@get_date
    and exists(select 1 from t_oper_subconto os2 where os2.id=ol.id
                  and os2.subconto_type_id=@uk_subc_type_id and os2.item_id=@asset_manager_id)
    and os.item_id in (select stock_id from ts_sd_stock_overvalue_st where doc_id=@id)
  group by os.item_id, osp.item_id
 having sum(isnull(ol.qty_sum*ol.deb_or_cred,0))>0

-- УНКД
update #parts set
c_part_sum=(select sum(isnull(ol.sum_bal*ol.deb_or_cred,0))
              from t_oper_subconto os, t_oper_subconto osp, t_oper_list ol, t_operations o
             where o.partner2plan_id=@pp_id
               and ol.head_id=o.id and ol.acc_id=@acc766_id
               and os.id=ol.id and os.subconto_type_id=@subc_type_id
               and osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id
               and o.op_date<@get_date
               and os.item_id=#parts.stock_id and osp.item_id=#parts.part_id)
update #parts set c_part_sum=0 where c_part_sum is null

insert into #stocks (stock_id, bal_sum, c_bal_sum, all_qty, cur_diff, cur_sum, c_cur_diff, c_cur_sum, cur_qty)
select stock_id, sum(part_sum), sum(c_part_sum), sum(part_qty), 0, 0, 0, 0, 0
 from #parts
group by stock_id

if @fund_type='NPF_FUND'
begin
 if @owner_id=@asset_manager_id
  select @round_rate=(select round_sum from td_pay_fond where id=@owner_id)
 else
  select @round_rate=(select max(b.round_sum) from td_depo_docs d, tb_contracts b
                       where d.id=b.id and b.dog_type_id=3
                         and d.owner_id=@owner_id
                         and b.client_id=@asset_manager_id)
 select @round_rate=isnull(@round_rate,2)
end

declare stocks cursor for
 select st.stock_id, st.sum_main, st.sum_one_stock, st.cur_id from ts_sd_stock_overvalue_st st
  where st.doc_id=@id and st.stock_id in (select stock_id from #stocks)
for read only
open stocks
fetch stocks into @stock_id, @all_sum, @one_sum, @st_cur_id
while .CURSOR_STATE=0
begin

 if @st_cur_id is not null and @st_cur_id<>@rur_cur_id
 begin
  if isnull(@all_sum,0)<>0
  begin
   .CONVERT_SUM_DEC(@st_cur_id, @rur_cur_id, @oper_date, 0, @all_sum, @all_sum_rur, @rate_value, @rate_date)
   select @all_sum=convert(money, round(@all_sum_rur,2))
  end
  if isnull(@one_sum,0)<>0
  begin
   .CONVERT_SUM_DEC(@st_cur_id, @rur_cur_id, @oper_date, 0, @one_sum, @one_sum_rur, @rate_value, @rate_date)
   if @fund_type='PIF_FUND'
   begin
    .PIF_ROUND_RATE(@owner_id, @one_sum_rur, @one_sum_rur)
   end
   if @fund_type='NPF_FUND'
    select @one_sum_rur=round(@one_sum_rur,@round_rate)
   select @one_sum=convert(decimal(18,8),@one_sum_rur)
  end
 end

 update #stocks set
  all_sum=isnull(@all_sum,0),
  one_sum=isnull(@one_sum,0)
 where stock_id=@stock_id

 fetch stocks into @stock_id, @all_sum, @one_sum, @st_cur_id
end
close stocks
.DEALLOCATE stocks

update #stocks set
 c_all_sum=st.sum_nkd_main,
 c_one_sum=st.sum_nkd_one_stock
from ts_sd_stock_overvalue_st st
where st.doc_id=@id and st.stock_id=#stocks.stock_id
  and (st.sum_nkd_main is not null or st.sum_nkd_one_stock is not null)

begin tran
.UPDATE_STATE
.ACTION_HISTORY

declare ops cursor for
 select stock_id, part_id, part_sum, part_qty, c_part_sum from #parts
for read only
open ops
fetch ops into @stock_id, @part_id, @part_sum, @part_qty, @c_part_sum
while .CURSOR_STATE=0
begin
  select
   @cur_qty=#stocks.cur_qty+@part_qty,
   @diff_all=#stocks.cur_diff,
   @c_diff_all=#stocks.c_cur_diff,
   @cur_sum=#stocks.cur_sum+@part_sum,
   @c_cur_sum=#stocks.c_cur_sum+@c_part_sum,
   @one_sum=#stocks.one_sum,
   @c_one_sum=#stocks.c_one_sum,
   @all_qty=#stocks.all_qty,
   @all_sum=#stocks.all_sum,
   @c_all_sum=#stocks.c_all_sum
  from #stocks where #stocks.stock_id=@stock_id

  if @one_sum<>0
   select @diff=(round(@part_qty*@one_sum,2))-@part_sum
  else
  begin
   if @cur_qty < @all_qty
    select @diff=round((convert(double precision,@all_sum)*@part_qty/@all_qty),2)-@part_sum
   else
    select @diff=@all_sum-@cur_sum-isnull(@diff_all,0)
  end
  select @diff_all=@diff_all+@diff

  if @c_one_sum is not null or @c_all_sum is not null
  begin
   if @c_one_sum is not null
    select @c_diff=(round(@part_qty*@c_one_sum,2))-@c_part_sum
   else
   begin
    if @cur_qty < @all_qty
     select @c_diff=round((convert(double precision,@c_all_sum)*@part_qty/@all_qty),2)-@c_part_sum
    else
     select @c_diff=@c_all_sum-@c_cur_sum-isnull(@c_diff_all,0)
   end
   select @c_diff_all=@c_diff_all+@c_diff
  end

  update #stocks set
   cur_qty=@cur_qty,
   cur_diff=@diff_all,
   cur_sum=@cur_sum,
   c_cur_diff=@c_diff_all,
   c_cur_sum=@c_cur_sum
  where #stocks.stock_id=@stock_id

  -- Переоценка по 58.1
  if @oper_type in (0,1)
  begin
   if @diff > 0 -- Прибыль
   begin
    .OPERATION(@owner_id,@plan_id,@oper_date,@acc5811_id,@add_acc_id,@diff,'RUR',@oper_date,0,null,@doc_name,N)
    .UPDATE_SUBC(D,'stock_emis',@stock_id)
    .UPDATE_SUBC(D,'doc_item',@part_id)
    if @fund_type = 'NPF_FUND'
    begin
     .UPDATE_SUBC(D,'asset_manager',@asset_manager_id)
     .UPDATE_SUBC(K,'asset_manager',@asset_manager_id)
    end
    .SUBC_CHECK
   end
   if @diff < 0 -- Убыток
   begin
    select @diff=@diff * (-1)
    .OPERATION(@owner_id,@plan_id,@oper_date,@add_acc_id,@acc5811_id,@diff,'RUR',@oper_date,null,0,@doc_name,N)
    .UPDATE_SUBC(K,'stock_emis',@stock_id)
    .UPDATE_SUBC(K,'doc_item',@part_id)
    if @fund_type = 'NPF_FUND'
    begin
     .UPDATE_SUBC(K,'asset_manager',@asset_manager_id)
     .UPDATE_SUBC(D,'asset_manager',@asset_manager_id)
    end
    .SUBC_CHECK
   end
  end

  -- Переоценка по 76.6
  if @oper_type in (0,2) and (@c_one_sum is not null or @c_all_sum is not null)
  begin
   if @c_part_sum=0 select @c_op_qty=@part_qty else select @c_op_qty=0
   if @c_diff > 0 -- Прибыль
   begin
    .OPERATION(@owner_id,@plan_id,@oper_date,@acc766_id,@add_acc_id,@c_diff,'RUR',@oper_date,@c_op_qty,null,@doc_name,N)
    .UPDATE_SUBC(D,'stock_emis',@stock_id)
    .UPDATE_SUBC(D,'doc_item',@part_id)
    if @fund_type = 'NPF_FUND'
    begin
     .UPDATE_SUBC(D,'asset_manager',@asset_manager_id)
     .UPDATE_SUBC(K,'asset_manager',@asset_manager_id)
    end
    .SUBC_CHECK
   end
   if @c_diff < 0 -- Убыток
   begin
    select @c_diff=@c_diff * (-1)
    .OPERATION(@owner_id,@plan_id,@oper_date,@add_acc_id,@acc766_id,@c_diff,'RUR',@oper_date,null,@c_op_qty,@doc_name,N)
    .UPDATE_SUBC(K,'stock_emis',@stock_id)
    .UPDATE_SUBC(K,'doc_item',@part_id)
    if @fund_type = 'NPF_FUND'
    begin
     .UPDATE_SUBC(K,'asset_manager',@asset_manager_id)
     .UPDATE_SUBC(D,'asset_manager',@asset_manager_id)
    end
    .SUBC_CHECK
   end
  end

 fetch ops into @stock_id, @part_id, @part_sum, @part_qty, @c_part_sum
end
close ops
.DEALLOCATE ops

update ts_sd_stock_overvalue_st set
 stock_qty=st.all_qty,
 margin_sum=(case @oper_type when 0 then st.cur_diff+isnull(st.c_cur_diff,0)
                             when 1 then st.cur_diff
                             when 2 then isnull(st.c_cur_diff,0) end)
from #stocks st
where ts_sd_stock_overvalue_st.stock_id=st.stock_id and ts_sd_stock_overvalue_st.doc_id=@id

update ts_sd_stock_overvalue_st set
 sum_main=st.all_qty*ts_sd_stock_overvalue_st.sum_one_stock
from #stocks st
where ts_sd_stock_overvalue_st.stock_id=st.stock_id and ts_sd_stock_overvalue_st.doc_id=@id
  and isnull(ts_sd_stock_overvalue_st.sum_one_stock,0)<>0

update ts_sd_stock_overvalue_st set
 sum_nkd_main=st.all_qty*ts_sd_stock_overvalue_st.sum_nkd_one_stock
from #stocks st
where ts_sd_stock_overvalue_st.stock_id=st.stock_id and ts_sd_stock_overvalue_st.doc_id=@id
  and ts_sd_stock_overvalue_st.sum_nkd_one_stock is not null

-- Запись в журнал
declare ops cursor for
select st.stock_id, st.margin_sum, st.stock_qty from ts_sd_stock_overvalue_st st
 where st.doc_id=@id and isnull(st.margin_sum,0)<>0
for read only
open ops
fetch ops into @stock_id, @diff_all, @all_qty
while .CURSOR_STATE=0
begin

 if @diff_all > 0 select @j_type=1 else select @j_type=2
 select @diff_all=abs(@diff_all)

 .SD_ASSENT_ADD(@id,@stock_id,@diff_all,@all_qty,@owner_id,null)
 .SD_ASSET_OPER_ADD(@id,@stock_id,@diff_all,@all_qty,@owner_id,null,@j_type)

 fetch ops into @stock_id, @diff_all, @all_qty
end
close ops
.DEALLOCATE ops

commit tran

drop table #parts
drop table #stocks

.TRANSACTION_RESTORE

.END
  $ENDTEXT(11035779)
 $ENDACTION

 $ACTION(ACTION5)
  name=Удалить
  class=9
  form=null
  target_state=DEL
  procedure=ap_60805000000048927
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

begin tran
.UPDATE_STATE
.ACTION_HISTORY

delete t_items from ts_sd_stock_overvalue_st st where t_items.id=st.id and st.doc_id=@id
delete ts_sd_stock_overvalue_st where doc_id = @id

/*-------Проводки ----------*/
commit tran
.END
  $ENDTEXT(11035779)
 $ENDACTION

 $ACTION(ACTION6)
  name=Добавить ЦБ из портфеля
  class=9
  form=null
  target_state=null
  procedure=ap_60805000000048928
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.GET_PROPS
.ID_VAR(@plan_id)
.ID_VAR(@pp_id)
.ID_VAR(@acc_id)
.ID_VAR(@subc_type_id)
.ID_VAR(@uk_subc_type_id)
.ID_VAR(@stock_id)
.INT_VAR(@is_get_rate)
.NAME_VAR(@fund_type)
declare @op_date datetime

select @fund_type=upper(.ITEM_TYPE_CODE(@owner_id))

if @fund_type='PIF_FUND'
 select @plan_id=.ARG_PLAN('PIF_ACCOUNT')

if @fund_type='NPF_FUND'
 select @plan_id=.ARG_PLAN('NPF_ACCOUNT')

select @acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @subc_type_id=.SUBCTYPE_FROM_CODE('stock_emis')
select @uk_subc_type_id=.SUBCTYPE_FROM_CODE('asset_manager')
select @is_get_rate=(case @type_overvalue_id when 1 then 1 else 0 end)
select @op_date=dateadd(dd,1,(.DATE_NORM(@in_date,B)))

.TRANSACTION_SAVE

create table #stocks (id numeric)

select @pp_id=pp.id from t_plans2partner pp
 where pp.partner_id=@owner_id and pp.plan_id=@plan_id and .ITEMS_EXISTS(pp.id)

if @fund_type='PIF_FUND'
 insert into #stocks (id)
 select os.item_id
   from t_oper_subconto os, t_oper_list ol, t_operations o
  where o.partner2plan_id=@pp_id
    and ol.head_id=o.id and ol.acc_id=@acc_id
    and os.id=ol.id and os.subconto_type_id=@subc_type_id
    and o.op_date<@op_date
  group by os.item_id
 having sum(isnull(ol.qty_sum*ol.deb_or_cred,0))>0

if @fund_type='NPF_FUND'
 insert into #stocks (id)
 select os.item_id
   from t_oper_subconto os, t_oper_list ol, t_operations o
  where o.partner2plan_id=@pp_id
    and ol.head_id=o.id and ol.acc_id=@acc_id
    and os.id=ol.id and os.subconto_type_id=@subc_type_id
    and o.op_date<@op_date
    and exists(select 1 from t_oper_subconto os2 where os2.id=ol.id
                  and os2.subconto_type_id=@uk_subc_type_id and os2.item_id=isnull(@initiator_id,@owner_id))
  group by os.item_id
 having sum(isnull(ol.qty_sum*ol.deb_or_cred,0))>0

begin tran

delete t_items from ts_sd_stock_overvalue_st st
 where t_items.id=st.id and st.doc_id=@id
   and st.stock_id not in (select id from #stocks)
delete ts_sd_stock_overvalue_st where doc_id = @id
   and stock_id not in (select id from #stocks)

delete #stocks from ts_sd_stock_overvalue_st st
 where st.stock_id=#stocks.id and st.doc_id=@id

declare stocks cursor for select id from #stocks for read only
open stocks
fetch stocks into @stock_id
while .CURSOR_STATE=0
begin

 .TYPE_INIT('SD_OVERVALUE_LIST',1)
 .TYPE_ASSIGN(doc_id,@id,1)
 .TYPE_ASSIGN(stock_id,@stock_id,1)
 .TYPE_ASSIGN(is_get_rate,@is_get_rate,1)
 .TYPE_INSERT('SD_OVERVALUE_LIST','CREATE',1)

 fetch stocks into @stock_id
end
close stocks
.DEALLOCATE stocks

.EXEC_ACTION(@sid,'SD_OVERVALUE','GET_RATES')

commit tran

-- select stock_code from #stocks, td_stock_emis where #stocks.id=td_stock_emis.id

drop table #stocks

.TRANSACTION_RESTORE

.END
  $ENDTEXT(11035779)
 $ENDACTION

 $ACTION(GET_RATES)
  name=Определить котировки
  class=9
  form=null
  target_state=null
  procedure=ap_60805000000048929
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11035779)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.GET_PROPS
declare @o_stock_id numeric

if isnull((select type_overvalue_id from ts_sd_stock_overvalue where id=@id),0) <> 0
begin

begin tran

 declare o_stocks cursor for
  select id from ts_sd_stock_overvalue_st st where st.doc_id=@id
  order by id
 for read only
 open o_stocks
 fetch o_stocks into @o_stock_id
 while .CURSOR_STATE=0
 begin

  exec ap_get_rate4overvalue @o_stock_id, @in_date
  if @@error != 0
  begin
   rollback tran
   return
  end
  fetch o_stocks into @o_stock_id
 end
 close o_stocks
 .DEALLOCATE o_stocks

commit tran

end

.END
  $ENDTEXT(11035779)
 $ENDACTION

 $STATE2ACTION(BEG.ACTION1)
  state=BEG
  action=ACTION1
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.ACTION2)
  state=ED
  action=ACTION2
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.ACTION4)
  state=ED
  action=ACTION4
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.ACTION5)
  state=ED
  action=ACTION5
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.ACTION6)
  state=ED
  action=ACTION6
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.D_SDEPO_OVERVALUE_OPER)
  state=ED
  action=D_SDEPO_OVERVALUE_OPER
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.D_STOCK_OVERVALUE_FILTER)
  state=ED
  action=D_STOCK_OVERVALUE_FILTER
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.D_STOCK_OVERVALUE_LIST)
  state=ED
  action=D_STOCK_OVERVALUE_LIST
 $ENDSTATE2ACTION

 $STATE2ACTION(ED.GET_RATES)
  state=ED
  action=GET_RATES
 $ENDSTATE2ACTION

 $STATE2ACTION(OP_END.ACTION3)
  state=OP_END
  action=ACTION3
 $ENDSTATE2ACTION

 $STATE2ACTION(OP_END.D_SDEPO_OVERVALUE_OPER)
  state=OP_END
  action=D_SDEPO_OVERVALUE_OPER
 $ENDSTATE2ACTION

 $STATE2ACTION(OP_END.D_STOCK_OVERVALUE_FILTER)
  state=OP_END
  action=D_STOCK_OVERVALUE_FILTER
 $ENDSTATE2ACTION

 $STATE2ACTION(OP_END.D_STOCK_OVERVALUE_LIST)
  state=OP_END
  action=D_STOCK_OVERVALUE_LIST
 $ENDSTATE2ACTION

 $STATE2PROP(BEG.INITIATOR_ID)
  state=BEG
  prop=INITIATOR_ID
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.IN_DATE)
  state=BEG
  prop=IN_DATE
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.IN_NO)
  state=BEG
  prop=IN_NO
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.IS_FIX_RATE_DATE)
  state=BEG
  prop=IS_FIX_RATE_DATE
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.MOVE_COMMENT)
  state=BEG
  prop=MOVE_COMMENT
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.OPER_TYPE)
  state=BEG
  prop=OPER_TYPE
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.OWNER_ID)
  state=BEG
  prop=OWNER_ID
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(BEG.TYPE_OVERVALUE_ID)
  state=BEG
  prop=TYPE_OVERVALUE_ID
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.INITIATOR_ID)
  state=ED
  prop=INITIATOR_ID
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.IN_DATE)
  state=ED
  prop=IN_DATE
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.IN_NO)
  state=ED
  prop=IN_NO
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.IS_FIX_RATE_DATE)
  state=ED
  prop=IS_FIX_RATE_DATE
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.MOVE_COMMENT)
  state=ED
  prop=MOVE_COMMENT
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.OPER_TYPE)
  state=ED
  prop=OPER_TYPE
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.OWNER_ID)
  state=ED
  prop=OWNER_ID
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(ED.TYPE_OVERVALUE_ID)
  state=ED
  prop=TYPE_OVERVALUE_ID
  enable=1
  mandatory=1
  visible=1
 $ENDSTATE2PROP
$ENDDOC