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
.GET_PROPS

----------------------------------------------------------------------------------
declare
@summ_doc_total_new money,
@rate_val money,
@rate_dat datetime

exec ap_calculate_comis @sid

.UPDATE_STATE
.ACTION_HISTORY

.TEST_RATE2CUR2DATE(@in_date,@cur_id)
.TEST_RATE2CUR2DATE(@in_date,@pay_cur_id)

---.CONVERT_SUM(@cur_id,@pay_cur_id,@in_date,0,@summ_doc_total,@summ_doc_total_new,@rate_val,@rate_dat)

select @payment_sum=round(@sum_in_paycur, 2)
----------------------------------------------------------------------------------

if @is_entry_cd is null
.EXIT_MESSAGE('Не задано вхождение КД в сумму')

declare @code_name_then varchar(255),
@side_id numeric

/*формирование сторон если сделка клиентская*/

if @character_id=2 begin
    select @code_name_then='Продавец',
    @side_id=4
  .TYPE_INIT('side_dog',1)
  .TYPE_ASSIGN(contract_id,@id,1)
  .TYPE_ASSIGN(sign_position,'Директор',1)
  .TYPE_ASSIGN(sign_position_rp,'Директора',1)
  .TYPE_ASSIGN(partner_id,@contragent_id,1)
  .TYPE_ASSIGN(sign_who,(t_partners_jur.j_head_staff from t_partners_jur where t_partners_jur.id = @contragent_id),1)
  .TYPE_ASSIGN(sign_who_rp,(t_partners_jur.j_head_staff_rp from t_partners_jur where t_partners_jur.id = @contragent_id),1)
  .TYPE_ASSIGN(bank_acc_id,(id from t_bank_accounts where partner_id=@contragent_id),1)
  .TYPE_ASSIGN(side_role_id,@side_id,1)
  .TYPE_ASSIGN(code_name_then,@code_name_then,1)
  .TYPE_INSERT('side_dog','CRT',1)
  .FOR_DOCS(N,'side_dog',ALL,1,'tb_contr_side.contract_id=@client_contract_id and
         t_items100.id=tb_contr_side.id','tb_contr_side',2)
  .TYPE_GET('side_dog',@for_id2)
  .TYPE_INIT('side_dog',2)
  .TYPE_ASSIGN(contract_id,@id,2)
  .TYPE_ASSIGN(sign_position,@sign_position1,2)
  .TYPE_ASSIGN(doc_foundation,@doc_foundation1,2)
  .TYPE_ASSIGN(sign_position_rp,@sign_position_rp1,2)
  .TYPE_ASSIGN(byer_or_saler_ring,@byer_or_saler_ring1,2)
  .TYPE_ASSIGN(ring_order,@ring_order1,2)
  .TYPE_ASSIGN(sign_dog,@sign_dog1,2)
  .TYPE_ASSIGN(sign_who,@sign_who1,2)
  .TYPE_ASSIGN(sign_who_rp,@sign_who_rp1,2)
  .TYPE_ASSIGN(partner_id,@partner_id1,2)
  .TYPE_ASSIGN(bank_acc_id,@bank_acc_id1,2)
  .TYPE_ASSIGN(side_role_id,@side_role_id1,2)

    if @side_role_id1=2 begin
      select @code_name_then='Комиссионер'
    end
    if @side_role_id1=1 begin
      select @code_name_then='Покупатель'
    end
    .TYPE_ASSIGN(code_name_then,@code_name_then,2)
    .TYPE_INSERT('side_dog','CRT',2)
  .END_FOR_DOCS(2)
end

/*Конец формирование сторон если сделка клиентская*/

declare
@inv_id numeric,
@plan_money_id numeric,
@plan_securs_id numeric,
@out_method_id numeric,
@mess_res varchar(255),
@kinstr_ds_acc_id numeric,
@kinstr_cb_acc_id numeric,
@dinstr_ds_acc_id numeric,
@dinstr_cb_acc_id numeric,
@dvbr_ds_acc_id numeric,
@kvbr_ds_acc_id numeric,
@dvbr_cb_acc_id numeric,
@kvbr_cb_acc_id numeric,
@org_id numeric

declare @is_realiz int,
@is_p2l_comis int,
@is_com_in int,
@is_allow_short4stocks int,
@is_allow_short4money int,
@asset4pereoc_type_id numeric

.BACK_GET_ORG_PARMS(@owner_id,@inv_id,@plan_money_id,@plan_securs_id,@out_method_id,@is_realiz,@is_p2l_comis,@is_com_in,@is_allow_short4stocks,@is_allow_short4money,@asset4pereoc_type_id)

/*Вычисление счетов по клиентским поручениям*/
----------
.ACCID_FROM_CONST2PLAN(@kinstr_ds_acc_id,'BACK_ACC_STOCK_MOVE_INSTR',@plan_money_id,2,1,'Счет ожидаемого прихода ЦБ по клиентскому поручению по плану ДС',@mess_res)

.ACCID_FROM_CONST2PLAN(@kinstr_cb_acc_id,'BACK_ACC_STOCK_MOVE_INSTR',@plan_securs_id,2,1,'Счет ожидаемого прихода ЦБ по клиентскому поручению по плану ЦБ',@mess_res)

.ACCID_FROM_CONST2PLAN(@dinstr_ds_acc_id,'BACK_ACC_MONEY_MOVE_INSTR',@plan_money_id,3,1,'Счет ожидаемого расхода ДС по клиентскому поручению по плану ДС',@mess_res)

.ACCID_FROM_CONST2PLAN(@dinstr_cb_acc_id,'BACK_ACC_MOVE_SECUR_KEEP',@plan_securs_id,2,1,'Счет ожидаемого прихода ЦБ по месту получения по плану ЦБ',@mess_res)
----------

/*Вычисление счетов по обязательствам ВБР*/
----------
if @is_payment=2 and @character_id=2
begin
.ACCID_FROM_CONST2PLAN(@kvbr_ds_acc_id,'BACK_ACC_CL_OBLIG_VBR',@plan_money_id,1,1,'Счет обязательств по ДС клиента',@mess_res)
end else
begin
.ACCID_FROM_CONST2PLAN(@kvbr_ds_acc_id,'BACK_ACC_MONEY_VBR_TIKET',@plan_money_id,3,1,'Счет обязательств по ДС (тикет)',@mess_res)
end

.ACCID_FROM_CONST2PLAN(@dvbr_ds_acc_id,'BACK_ACC_SECUR_VBR_TIKET',@plan_money_id,2,1,'Счет обязательств по ЦБ (тикет)',@mess_res)

.ACCID_FROM_CONST2PLAN(@dvbr_cb_acc_id,'BACK_ACC_SECUR_VBR_TIKET',@plan_securs_id,2,1,'Счет к получению ЦБ по ВБР',@mess_res)

.ACCID_FROM_CONST2PLAN(@kvbr_cb_acc_id,'BACK_ACC_SECUR_KEEP_VBR_DOG',@plan_securs_id,2,1,'Счет места нахождения ЦБ',@mess_res)
---------

/*Формирование сторон если сделка дилерская*/

declare @count int
/*
if @character_id=1 begin
.TYPE_INIT('side_dog',3)
.TYPE_ASSIGN(contract_id,@id,3)
.TYPE_ASSIGN(sign_position,'Директор',3)
.TYPE_ASSIGN(sign_position_rp,'Директора',3)
    select @count=1
    while @count<=2 begin
      if @count=1 begin
        select @org_id=@owner_id,
        @side_id=1,
        @code_name_then='Покупатель'
      end
      if @count=2 begin
        select @org_id=@contragent_id,
        @side_id=4,
        @code_name_then='Продавец'
      end
      .TYPE_ASSIGN(partner_id,@org_id,3)
      .TYPE_ASSIGN(sign_who,(t_partners_jur.j_head_staff from t_partners_jur where t_partners_jur.id = @org_id),3)
      .TYPE_ASSIGN(sign_who_rp,(t_partners_jur.j_head_staff_rp from t_partners_jur where t_partners_jur.id = @org_id),3)
      .TYPE_ASSIGN(bank_acc_id,(id from t_bank_accounts where partner_id=@org_id),3)
      .TYPE_ASSIGN(side_role_id,@side_id,3)
      .TYPE_ASSIGN(code_name_then,@code_name_then,3)
      .TYPE_INSERT('side_dog','CRT',3)
      select @count=@count+1
    end
end
*/
if @character_id=2
begin

.TYPE_GET('client_contracts',@client_contract_id,2)

.TYPE_GET('type_client_contracts',@dog_type_id2,20)

if @instruction_on2off=1
begin

.TYPE_INIT('instruction',5)
.TYPE_ASSIGN(sale_place_id,@sale_place_id,5)
.TYPE_ASSIGN(type_price,2,5)
.TYPE_ASSIGN(type_poruch_id,1,5)
.TYPE_ASSIGN(pay_cur_id,@cur_id,5)
.TYPE_ASSIGN(covered_stock_averprice,@stock_price,5)
.TYPE_ASSIGN(covered_stock_qty,@stock_qty,5)

.TYPE_GET('td_stock_emis',@stock_id)

.TYPE_ASSIGN(emitent_id,@emmitent_id1,5)
.TYPE_ASSIGN(stock_type_id,@stock_type_id1,5)
.TYPE_ASSIGN(stock_nominal,@stock_nominal1,5)
.TYPE_ASSIGN(stock_emiss_no,@stock_emiss_no1,5)
.TYPE_ASSIGN(nominal_cur_id,@stock_curr_id1,5)

declare @in_no1 varchar(50)
.BACK_GEN_NO(@in_no1,1,@in_date)

.TYPE_ASSIGN(in_no,@in_no1,5)
.TYPE_ASSIGN(depo_doc_type,1,5)
.TYPE_ASSIGN(in_date,@in_date,5)
.TYPE_ASSIGN(out_no,@direct_no,5)
.TYPE_ASSIGN(out_date,@in_date,5)

.TYPE_ASSIGN(folder_id,@folder_id,5)
.TYPE_ASSIGN(client_id,@client_id,5)

.TYPE_ASSIGN(depo_subacc_id,@depo_subacc_id,5)
.TYPE_ASSIGN(bank_account_id,@bank_account_id,5)
.TYPE_ASSIGN(price_cur_id,@cur_id,5)
.TYPE_ASSIGN(owner_id,@owner_id,5)
.TYPE_ASSIGN(stock_qty,@stock_qty,5)
.TYPE_ASSIGN(stock_qty_input,@stock_qty_input,5)
.TYPE_ASSIGN(poruch_units,@deal_units,5)
.TYPE_ASSIGN(complit,1,5)
.TYPE_ASSIGN(exec_end_date,@in_date,5)
.TYPE_ASSIGN(exec_begin_date,@in_date,5)
.TYPE_ASSIGN(receive_datetime,@in_date,5)
.TYPE_ASSIGN(client_contract_id,@client_contract_id,5)
.TYPE_ASSIGN(stock_id,@stock_id,5)
.TYPE_ASSIGN(portfolio_id,@portfolio_id,5)
.TYPE_ASSIGN(investor2portfolio_id,@portfolio_sub_id,5)
.TYPE_INSERT('instruction','ZAKL',5)

declare @direct_id numeric

select @direct_id=.TYPE_FIELD(id,5)

update tb_baysale_docitems set
direction_id=@direct_id
where id=@id

end

if (@instruction_on2off=0) begin

   if @code20!='1' begin

    declare @direct_stock_qty numeric,
    @direct_covered_stock_qty numeric

    select @direct_stock_qty=tb_directions.stock_qty,
           @direct_covered_stock_qty=isnull(tb_directions.covered_stock_qty,0)
    from tb_directions,t_states,t_items,t_types
    where upper(t_types.type_code)=upper('instruction')
    and tb_directions.id=@direction_id and t_states.type_id=t_types.id and t_states.state_code='ISP'
    and t_items.state_id=t_states.id and tb_directions.id=t_items.id

    declare @count_deal int
    declare @count_deal_podtv int

    select @count_deal=(select count(*) from tb_baysale_docitems,.ACTUAL_TABLES,t_types
    where tb_baysale_docitems.direction_id=@direction_id
    and t_types.type_code .GROUP_EXISTS_ITEM('dealings') and t_items.type_id=t_types.id .ACTUAL_WHERE(tb_baysale_docitems.id))

    select @count_deal_podtv=(select count(*) from tb_baysale_docitems,.ACTUAL_TABLES,t_types
    where tb_baysale_docitems.direction_id=@direction_id and
    tb_baysale_docitems.id<>@id and t_states.state_code<>'EDIT'
    and t_types.type_code .GROUP_EXISTS_ITEM('dealings') and t_items.type_id=t_types.id .ACTUAL_WHERE(tb_baysale_docitems.id))

    if ((@direct_stock_qty=@direct_covered_stock_qty) and (@count_deal=@count_deal_podtv+1))
    begin
    .ITEM_MOVE_STATE(@direction_id,'ZAKL')
    end

    declare @comment varchar(255),
    @comment2 varchar(255),
    @summ_doc_total_instr money

/* Проверка, существует ли поручение */
if isnull(@direction_id, 0) = 0
   .EXIT_MESSAGE('Отсутствует клиентское поручение на совершение сделки')

   .TYPE_GET('instruction',@direction_id,3)

    select @comment='Снятие задолженности по ожидаемым расчетам'

    select @summ_doc_total_instr=convert(money,round(((@doc_sum_lim3*@stock_qty)/@stock_qty3),2))

   .TEST_RATE2CUR2DATE(@in_date3,@price_cur_id3)

-- Проводка Снятие задолженности по ожидаемым расчетам

   .OPERATION(@owner_id,@plan_money_id,@in_date,@dinstr_ds_acc_id,@kinstr_ds_acc_id,@summ_doc_total_instr,@price_cur_id3,@in_date3,0,0,@comment,N)
   .UPDATE_SUBC('K','investors',@client_contract_id)
   .UPDATE_SUBC('K','portfolio',@portfolio_id3)
   .UPDATE_SUBC('K','investor2portfolio',@investor2portfolio_id3)
   .UPDATE_SUBC('K','stock_emis',@stock_id)
   .UPDATE_SUBC('K','instruction',@direction_id)
   .UPDATE_SUBC('D','investors',@client_contract_id)
   .UPDATE_SUBC('D','portfolio',@portfolio_id3)
   .UPDATE_SUBC('D','investor2portfolio',@investor2portfolio_id3)
   .UPDATE_SUBC('D','instruction',@direction_id)
   .SUBC_CHECK

    select @comment2='Снятие резерва ЦБ(по сделке)'

-- Проводка Снятие резерва ЦБ(по сделке)

   .OPERATION(@owner_id,@plan_securs_id,@in_date,@dinstr_cb_acc_id,@kinstr_cb_acc_id,@stock_qty,null,@in_date3,null,null,@comment2,N)
   .UPDATE_SUBC('K','investors',@client_contract_id)
   .UPDATE_SUBC('K','portfolio',@portfolio_id3)
   .UPDATE_SUBC('K','investor2portfolio',@investor2portfolio_id3)
   .UPDATE_SUBC('K','stock_emis',@stock_id)
   .UPDATE_SUBC('K','instruction',@direction_id)
   .UPDATE_SUBC('D','stock_emis',@stock_id)
   .SUBC_CHECK

  end
 end
end

.NAME_VAR(@oper_comment)

/* --------- Инвестор ------------ */
.ID_VAR(@investor_id)

if @character_id = 1 /*Дилерская*/
select @investor_id = (select tb_broker_change.id from tb_broker_change
                                          where @owner_id=tb_broker_change.partner_id
                                          and  .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info'))
else
select @investor_id = @client_contract_id

/* ----- Механизм контроля партий ----- */
.ID_VAR(@plan2partner_id)

.ASSIGN(@plan2partner_id,(.PLAN2PARTNER_ID(@owner_id,@plan_money_id)))

/*----------------- Покупка -------------------*/

declare @oper_date datetime,
@oper_cur numeric,
@oper_sum money,
@cred_acc_sum money,
@deb_acc_summ money,
@usd numeric,
@rur numeric,
@rate_value_cc money,
@rate_date_cc datetime,
@summ_doc_total_rur money,
@deb2cred_sum money,
@curr_id numeric

SELECT @usd = @cur_id

SELECT @rur = .CUR2PLAN(@plan_money_id)

IF @rur = @pay_cur_id BEGIN

--SELECT @character_cur_price AS character_cur_price, @summ_doc_total2unkd AS summ_doc_total2unkd,
--  @sum_in_paycur AS sum_in_paycur, @summ_doc_total AS summ_doc_total,
--  @coupon_account_sum AS coupon_account_sum

  IF ISNULL(@character_cur_price, 0) = 0
    SELECT @oper_sum = @summ_doc_total + ISNULL(@coupon_account_sum, 0)
  ELSE
    SELECT @oper_sum = @sum_in_paycur
END
ELSE BEGIN
  .CONVERT_SUM(@cur_id, @rur, @in_date, 0, @summ_doc_total2unkd, @oper_sum, @rate_val, @rate_dat)
END

SELECT @oper_sum = ROUND(@oper_sum, 2)

SELECT @oper_cur = @rur

if @character_cur_price=1
begin
select @oper_date=@rate_date
end else
begin
select @oper_date=@in_date
end

select @deb_acc_summ=round(@summ_doc_total2unkd,2)

/*
if @character_cur_price=1
begin
select @oper_date=@rate_date
select @oper_cur=@rur
select @oper_sum=round(@sum_in_paycur,2)
select @deb_acc_summ=round((@sum_in_paycur/@cur_price2pay_rate),2)
end else
begin
select @oper_date=@in_date

if @cur_id=@rur
   select @curr_id=@cur_id
else
   select @curr_id=@usd

.TEST_RATE2CUR2DATE(@in_date,@cur_id)

.CONVERT_SUM(@cur_id,@curr_id,@in_date,0,@summ_doc_total,@deb_acc_summ,@rate_value_cc,@rate_date_cc)

select @deb2cred_sum=round(@deb_acc_summ,2)

if isnull(@coupon_account_sum,0)<>0
begin

.CONVERT_SUM(@rur,@curr_id,@in_date,0,@coupon_account_sum,@deb_acc_summ,@rate_value_cc,@rate_date_cc)

select @deb2cred_sum=@deb2cred_sum+round(@deb_acc_summ,2)

end

select @deb_acc_summ=@deb2cred_sum

.CONVERT_SUM(@cur_id,@rur,@in_date,0,@summ_doc_total,@summ_doc_total_rur,@rate_value_cc,@rate_date_cc)

select @oper_sum=round((@summ_doc_total_rur+isnull(@coupon_account_sum,0)),2)

end
*/
select @cred_acc_sum=@deb_acc_summ

select @oper_comment = 'Заключена сделка покупки ВБР'

-- Проводка 1 Заключена сделка покупки ВБР

.OPERATION(@owner_id,@plan_money_id,@in_date,@dvbr_ds_acc_id,@kvbr_ds_acc_id,@oper_sum,@rur,@oper_date,@stock_qty,null,@oper_comment,N,@operation_id,@id,@action_id,@action_history_id,@deb_acc_summ,@cred_acc_sum,@cur_id,@cur_id)
.UPDATE_SUBC('D','stock_emis',@stock_id)
.UPDATE_SUBC('D','contragents',@contragent_id)
.UPDATE_SUBC('D','investors',@investor_id)
.UPDATE_SUBC('D','investor2portfolio',@portfolio_sub_id)
.UPDATE_SUBC('D','portfolio',@portfolio_id)
.UPDATE_SUBC('D','doc_sec',@id)
.UPDATE_SUBC('K','contragents',@contragent_id)
.UPDATE_SUBC('K','investors',@investor_id)
.UPDATE_SUBC('K','investor2portfolio',@portfolio_sub_id)
.UPDATE_SUBC('K','portfolio',@portfolio_id)
.UPDATE_SUBC('K','doc_sec',@id)
.SUBC_CHECK

-- Проводка 2 Заключена сделка покупки ВБР

.OPERATION(@owner_id,@plan_securs_id,@in_date,@dvbr_cb_acc_id,@kvbr_cb_acc_id,@stock_qty,null,@in_date,null,null,@oper_comment,N)
.UPDATE_SUBC('D','stock_emis',@stock_id)
.UPDATE_SUBC('D','contragents',@contragent_id)
.UPDATE_SUBC('D','investor2portfolio',@portfolio_sub_id)
.UPDATE_SUBC('D','portfolio',@portfolio_id)
.UPDATE_SUBC('D','investors',@investor_id)
.UPDATE_SUBC('D','doc_sec',@id)
.UPDATE_SUBC('K','stock_emis',@stock_id)
.UPDATE_SUBC('K','contragents',@contragent_id)
.SUBC_CHECK

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

.EXEC_ACTION(@sid,'dog_dealings_bay_vbr','END',N)

.UPDATE_ITEMS

declare @report_form_id numeric,
        @temp_id numeric
.ID_VAR(@report_action_id)

select @report_action_id = id from t_actions where type_id=@type_id and upper(action_code)='EXPORT'
select @report_form_id = id from t_report_form where to_type_id=@type_id and to_action_id=@report_action_id

if @report_form_id is not null
 begin
 select @temp_id = id
   from t_report_item
  where id = @id
 if (isnull(@temp_id, -1)) = -1
  begin
  insert into t_report_item(id, form_id, action_id)
   values (@id, @report_form_id, @report_action_id)
  end
 end

commit tran