# Date: 21/02/06
# Version: 105

$DOC(UK_QUERY_ASSET_OPERS)
 name=Запрос на формирование учетного журнала
 class=1
 product=ALD_PIF
 name_formula=.TYPE_NAME(@type_id) + ' от ' + .DATE_TO_STR(@date_e)
 remark_formula=.ITEM_NAME(@owner_id)+ ' от '+ .DATE_TO_STR(@date_e) + ' ' + @remark

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

 $STATE(CRT)
  name=Исполнен
  class=0
 $ENDSTATE

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

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

 $PROP(DATE_E)
  name=Дата запроса конечная
  field_name=date_e
  table_name=td_queries
  field_type=datetime
  field_length=8
  field_scale=3
  field_prec=23
  page=1
  order=2
  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(REMARK)
  name=Комментарий
  field_name=remark
  table_name=td_queries
  field_type=varchar
  field_length=255
  field_scale=null
  field_prec=255
  page=1
  order=4
  init_class=null
  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(DATE_B)
  name=Дада запроса начальная
  field_name=date_b
  table_name=td_queries
  field_type=datetime
  field_length=8
  field_scale=3
  field_prec=23
  page=1
  order=1
  init_class=null
  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=td_queries
  field_type=numeric
  field_length=9
  field_scale=0
  field_prec=18
  page=1
  order=3
  init_class=1
  init_formula=.ORGANIZATION_PIF
  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(REL1)
  class=0
  type=PIF_FUND
  form=null
  prop=OWNER_ID
  idname=null
  order=null
 $ENDRELATION

 $RELATION(REL2)
  class=1
  type=UK_QUERY_ASSET_OPERS
  form=null
  prop=null
  idname=null
  order=40
 $ENDRELATION

 $RELATION(REL3)
  class=1
  type=UK_QUERY_ASSET_OPERS
  form=null
  prop=null
  idname=null
  order=40
 $ENDRELATION

 $FORM(D_UK_QUERY_ASSET_OPERS_EDIT)
  name=Просмотр
  class=5
  filter=null
  target_state=
  uo=uo_edit
  dw=d_pif_query_coll_pays_edit
  procedure=ap_21535000001001425
  patterns=GENERIC_VIEW_ITEM_P2
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485121)
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

.VIEW_ITEM(ALL,1)

.END
  $ENDTEXT(14485121)

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

  $ACTION2RELATION(REL1.PARTNER_NAME)
   relation=REL1
   where=null
   prop=PARTNER_NAME
   order=null
   alias=fond_name
  $ENDACTION2RELATION
 $ENDFORM

 $FORM(D_UK_QUERY_ASSET_OPERS_LIST)
  name=Список
  class=4
  filter=null
  target_state=
  uo=uo_list
  dw=d_pif_query_collect_pays_list
  procedure=ap_21535000001001427
  patterns=GENERIC_VIEW_LIST
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485121)
create procedure dbo.%PROC% @sid varchar(30) = null
   .ARGLIST_FILTER
as
.BEGIN('N')

.CHECK_USER
.CHECK_VIEW_LIST

.ARGCONVERT_FILTER

.VIEW_LIST

.END
  $ENDTEXT(14485121)

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

  $ACTION2RELATION(REL1.PARTNER_NAME)
   relation=REL1
   where=null
   prop=PARTNER_NAME
   order=null
   alias=fond_name
  $ENDACTION2RELATION
 $ENDFORM

 $ACTION(ACTION_INSERT)
  name=Добавить
  class=1
  form=D_UK_QUERY_ASSET_OPERS_EDIT
  target_state=ED
  procedure=ap_21535000001001429
  patterns=GENERIC_INSERT_P1
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485122)
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(14485122)
 $ENDACTION

 $ACTION(ACTION_MOVE)
  name=На редактирование
  class=9
  form=null
  target_state=ED
  procedure=ap_21535000001001430
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485122)
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



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



commit tran
  $ENDTEXT(14485122)
 $ENDACTION

 $ACTION(ACTION_MOVE1)
  name=Исполнить
  class=9
  form=null
  target_state=CRT
  procedure=ap_21535000001001431
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485122)
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
)

-----------   Добавлено  -----------------
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
*/

------------------   Добавлено ---------------

insert into #jur1(doc_id,sum)
select doc_id,sum(sum) from #jur group by doc_id,deb_or_cred     --- Добавленно группировка по deb_or_cred


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 distinct 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   --- Добавленн distinct



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

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











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,@op_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
drop table #jur1
drop table #jur2



.ARH_NUMBER_UPDATE
commit tran
  $ENDTEXT(14485122)
 $ENDACTION

 $ACTION(ACTION_MOVE2)
  name=Удалить
  class=9
  form=null
  target_state=DEL
  procedure=ap_21535000001001432
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485122)
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

/*-------Проводки ----------*/


commit tran
  $ENDTEXT(14485122)

  $BIND(BIND1)
   type=UK_QUERY_ASSET_OPERS
   action=D_UK_QUERY_ASSET_OPERS_EDIT
   class=1
   order=1
   idname=null
  $ENDBIND
 $ENDACTION

 $ACTION(ACTION_UPDATE)
  name=Изменить
  class=2
  form=D_UK_QUERY_ASSET_OPERS_EDIT
  target_state=null
  procedure=ap_21535000001001434
  patterns=GENERIC_UPDATE_P1
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(14485122)
create procedure dbo.%PROC% @sid varchar(30) = null,
.ARGLIST(ALL,1)
as
declare @action_id numeric,
        @id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @item_name varchar(100),
        @new_state_id numeric
begin tran

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

.CHECK_USER
.CHECK_UPDATE

.ARGCONVERT(ALL,1)

.TEST_UNIQUE(ALL,1)

.UPDATE(ALL,1)

.RELGEN
.UPDATE_STATE
.ACTION_HISTORY

commit tran
select sid = convert(varchar, @id)
  $ENDTEXT(14485122)
 $ENDACTION

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

 $STATE2ACTION(CRT.ACTION_MOVE)
  state=CRT
  action=ACTION_MOVE
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.D_UK_QUERY_ASSET_OPERS_EDIT)
  state=CRT
  action=D_UK_QUERY_ASSET_OPERS_EDIT
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.D_UK_QUERY_ASSET_OPERS_LIST)
  state=CRT
  action=D_UK_QUERY_ASSET_OPERS_LIST
 $ENDSTATE2ACTION

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

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

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

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

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

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

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

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

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

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

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

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

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