create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .TRANSACTION_SAVE .GET_PROPS if exists(select 1 from t_client_assets a where a.id <> @id and a.contract_id = @contract_id and a.calc_date = @calc_date and .ITEM_STATE_CODE(a.id) = 'CRT') begin declare @mess varchar (255) select @mess = (select contr_code from tb_contracts where id=@contract_id) +' на дату '+ convert(varchar,@calc_date,104) .EXIT_MESSAGE_PARM('Существует сформированный срез по договору '@mess) end declare @plan_securs_id numeric , @plan_money_id numeric , @p2p_securs_id numeric , @p2p_money_id numeric , @st_stock_id numeric , @st_place_id numeric , @st_bank_acc_id numeric , @st_contrag_id numeric , @st_inv_id numeric , @rur_id numeric , @investor4rate_id numeric , @rate_date datetime , @asset_qty decimal(18,8) , @asset_yield_proc decimal(18,8) , @rate_value decimal(18,8) , @asset_bal_sum money , @asset_proc_sum money , @asset_rate_sum money , @asset_id numeric , @place_id numeric , @asset_name varchar(255) , @asset_type int , @inout_name varchar(255) , @inout_date datetime , @inout_sum money , @inout_type int -- 0 - первый ввод, 1 - ввод ДС, 2 - ввод ЦБ, 11 - вывод ДС, 12 - вывод ЦБ , @doc_id numeric , @doc_type varchar(255) , @tmp_int int , @tmp_money money , @rate_id numeric create table #res ( rate_date datetime null , asset_qty decimal(18,8) null , asset_yield_proc decimal(18,8) null , rate_value decimal(18,8) null , asset_bal_sum money null , asset_proc_sum money null , asset_rate_sum money null , asset_id numeric(18,0) not null , place_id numeric(18,0) null , asset_name varchar(255) null , asset_type int not null -- 1 - ЦБ, 2 - ДС, 3 - Депозиты, 11 - дебиторка, 12 - кредиторка ) create table #accs ( id numeric not null ) select @plan_securs_id = .ARG_PLAN('BACK_SECURS') select @plan_money_id = .ARG_PLAN('BACK_MONEY') select @p2p_securs_id = .PLAN2PARTNER_ID(@org_id, @plan_securs_id) select @p2p_money_id = .PLAN2PARTNER_ID(@org_id, @plan_money_id) select @st_stock_id = .SUBCTYPE_FROM_CODE('STOCK_EMIS') select @st_place_id = .SUBCTYPE_FROM_CODE('ASSET_PLACES') select @st_bank_acc_id = .SUBCTYPE_FROM_CODE('BANK_ACCOUNT') select @st_contrag_id = .SUBCTYPE_FROM_CODE('CONTRAGENTS') select @st_inv_id = .SUBCTYPE_FROM_CODE('INVESTORS') select @rur_id = .CUR('RUR') if @p2p_securs_id is null or @p2p_money_id is null .EXIT_MESSAGE('Не заданы планы счетов внутреннего учета для выбранной организации') ---------- -- ЦБ ---------- -- количественный остаток по ЦБ по плану ЦБ в разбивке по местам нахождения truncate table #accs insert into #accs (id) select id from t_accounts where acc_num_eff like 'S.A.%' and plan_id = @plan_securs_id insert into #res ( asset_qty , asset_id , place_id , asset_type ) select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_stock.item_id , place.exchange_id , 1 from t_operations o, t_oper_list ol, t_oper_subconto os_stock, t_oper_subconto os_place, t_oper_subconto os, tb_broker2exchange place where o.id = ol.head_id and o.partner2plan_id = @p2p_securs_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_stock.id and os_stock.subconto_type_id = @st_stock_id and ol.id = os_place.id and os_place.subconto_type_id = @st_place_id and os_place.item_id = place.id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_stock.item_id, place.exchange_id having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 -- балансовая стоимость и количество ЦБ по плану ДС truncate table #accs insert into #accs (id) select id from t_accounts where acc_num_eff like '1.0.0%' and plan_id = @plan_money_id insert into #res ( asset_qty , asset_bal_sum , asset_id , asset_type ) select isnull(sum(ol.qty_sum * ol.deb_or_cred),0) , isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_stock.item_id , 1 from t_operations o, t_oper_list ol, t_oper_subconto os_stock, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_stock.id and os_stock.subconto_type_id = @st_stock_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_stock.item_id having isnull(sum(ol.qty_sum * ol.deb_or_cred),0) <> 0 -- удаляем то, чего нет по плану ДС delete #res where place_id is not null and asset_id not in (select asset_id from #res where place_id is null) and asset_type = 1 -- разброс балансовой стоимости по площадкам update #res set asset_bal_sum = round(#res.asset_qty * (convert(float,1.0) * money.asset_bal_sum / money.asset_qty),2) from #res, #res money where money.asset_id = #res.asset_id and #res.place_id is not null and money.place_id is null and #res.asset_type = 1 and money.asset_type = 1 -- удаляем остатки по плану ДС delete #res where place_id is null and asset_type = 1 -- проставление котировок и расчет оценочной стоимости -- добавлено 19_10_06 так как затиралась внешняя котировка sobolev_de declare @rate_place_id_tmp numeric(18,0) declare stock cursor for select asset_id from #res group by asset_id for read only open stock fetch stock into @asset_id while .CURSOR_STATE = 0 begin select @rate_date = null, @rate_value = null, @asset_proc_sum = null, @investor4rate_id = null, @asset_yield_proc = null, @rate_id = null, @rate_place_id_tmp =null -- котировка для ПИФ if upper(.ITEM_TYPE_CODE(@partner_id)) = 'PIF_FUND' begin select @investor4rate_id = @partner_id .PIF_CALC_STOCK_PRICE(@asset_id, @investor4rate_id, @rate_place_id, @calc_date, 1, @rate_date, @tmp_int, @rate_value, @asset_proc_sum, @tmp_money, @tmp_money) select @asset_yield_proc = (select max(yield_wap) from tb_stock_rates r where rate_date = @rate_date and stock_id = @asset_id and .ITEMS_EXISTS(r.id)) end -- котировка для обычного ДУ else begin select @investor4rate_id = @org_id --@contract_id if @rate_place_id is null exec ap_get_stock_rate_class @stock_id = @asset_id, @rate_date = @calc_date, @org_id = @investor4rate_id, @cur_id = @rur_id, @stock_rate = @rate_value out, @rate_id = @rate_id out, @type_kotir = 1, @rate_place_id= @rate_place_id_tmp out else exec ap_get_stock_rate2place @stock_id = @asset_id, @rate_date = @calc_date, @org_id = @investor4rate_id, @cur_id = @rur_id, @rate_place_id = @rate_place_id, @stock_rate = @rate_value out, @rate_id = @rate_id out, @type_kotir = 1 if @rate_place_id is null select @rate_date = rate_date, @asset_yield_proc = yield_wap from tb_stock_rates where id = @rate_id else select @rate_place_id_tmp=@rate_place_id .GET_STOCK_RATE_COUPON(@asset_id, @calc_date, @rur_id, @asset_proc_sum) end update #res set rate_date = @rate_date , rate_value = @rate_value , asset_proc_sum = round(@asset_proc_sum * asset_qty, 2) , asset_rate_sum = round(@rate_value * asset_qty, 2) , asset_yield_proc = @asset_yield_proc , place_id = @rate_place_id_tmp where asset_id = @asset_id and asset_type = 1 fetch stock into @asset_id end close stock .DEALLOCATE stock ---------------- -- ден.средства ---------------- -- кроме депозитов truncate table #accs insert into #accs (id) select id from t_accounts where acc_num_eff like '2.0%' and acc_num_eff <> '2.0.13' and plan_id = @plan_money_id insert into #res ( asset_bal_sum , asset_id , asset_type ) select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_bank_acc.item_id , 2 from t_operations o, t_oper_list ol, t_oper_subconto os_bank_acc, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_bank_acc.id and os_bank_acc.subconto_type_id = @st_bank_acc_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_bank_acc.item_id having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 -- депозиты - остаток средств truncate table #accs insert into #accs (id) select id from t_accounts where acc_num_eff = '2.0.13' and plan_id = @plan_money_id insert into #res ( asset_bal_sum , asset_id , asset_type ) select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_bank_acc.item_id , 3 from t_operations o, t_oper_list ol, t_oper_subconto os_bank_acc, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_bank_acc.id and os_bank_acc.subconto_type_id = @st_bank_acc_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_bank_acc.item_id having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 -- депозиты - начисленные проценты update #res set asset_proc_sum = (select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) from t_operations o, t_oper_list ol, t_oper_subconto os_bank_acc, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_bank_acc.id and os_bank_acc.subconto_type_id = @st_bank_acc_id and os_bank_acc.item_id = #res.asset_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from t_accounts where acc_num_eff = '2.10.4' and plan_id = @plan_money_id) ) where asset_type = 3 ---------------- -- задолженность ---------------- -- по счетам, где есть разбивка по контрагентам truncate table #accs insert into #accs (id) select id from t_accounts where plan_id = @plan_money_id and acc_num_eff in ( '1.1.1', '1.1.2', '1.2.1', '1.2.2', '1.5.0', '1.6.0' -- по ЦБ, кроме ожидаемых , '2.1.1', '2.1.2', '2.2.1', '2.2.2', '2.10.0', '2.10.1', '2.10.2', '2.9' -- ДС по сделкам и групповым , '2.5.2', '2.5.3', '2.6.0', '2.6.2' -- ДС по вознаграждениям, взносам, налогам ) insert into #res ( asset_bal_sum , asset_id , asset_type ) select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_contrag_acc.item_id , (case when isnull(sum(ol.sum_bal * ol.deb_or_cred),0) > 0 then 11 else 12 end) from t_operations o, t_oper_list ol, t_oper_subconto os_contrag_acc, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_contrag_acc.id and os_contrag_acc.subconto_type_id = @st_contrag_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_contrag_acc.item_id having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 -- по счетам, где есть разбивка по местам нахождения truncate table #accs insert into #accs (id) select id from t_accounts where plan_id = @plan_money_id and acc_num_eff in ( '2.5.0', '2.5.4', '2.5.5', '2.5.6', '2.6.4', '2.6.5', '2.6.6' -- маржа, гар.взнос ) insert into #res ( asset_bal_sum , asset_id , asset_type ) select isnull(sum(ol.sum_bal * ol.deb_or_cred),0) , os_place_acc.item_id , (case when isnull(sum(ol.sum_bal * ol.deb_or_cred),0) > 0 then 11 else 12 end) from t_operations o, t_oper_list ol, t_oper_subconto os_place_acc, t_oper_subconto os where o.id = ol.head_id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os_place_acc.id and os_place_acc.subconto_type_id = @st_place_id and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) group by os_place_acc.item_id having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 --------------- -- Наименование актива --------------- -- ЦБ update #res set asset_name = s.stock_name from td_stock_emis s where s.id = #res.asset_id and #res.asset_name is null and #res.asset_type = 1 -- ДС, депозиты update #res set asset_name = isnull((select simple_name from t_simples where type_id = 5110 and id = ba.acc_type_id),'Расчетный счет') + ' (' + isnull((select cur_code from t_currencies where id = ba.bank_acc_cur_id),'RUR') + ')' + ' №' + ba.bank_account_no + ' в ' + isnull((select isnull(b.bank_name, p.partner_name) from t_banks b, t_partners p where b.id = p.id and b.id = ba.bank_id),'') from t_bank_accounts ba where ba.id = #res.asset_id and #res.asset_name is null and #res.asset_type in (2,3) -- Задолженность update #res set asset_name = i.item_name from t_items i where i.id = #res.asset_id and #res.asset_name is null and #res.asset_type in (11,12) begin tran --------------- -- Заполнение - Портфель и задолженности --------------- delete t_items from t_client_assets_list where t_items.id = t_client_assets_list.id and t_client_assets_list.client_assets_id = @id delete t_client_assets_list where t_client_assets_list.client_assets_id = @id declare list cursor for select rate_date , asset_qty , asset_yield_proc , rate_value , asset_bal_sum , asset_proc_sum , asset_rate_sum , asset_id , place_id , asset_name , asset_type from #res for read only open list fetch list into @rate_date , @asset_qty , @asset_yield_proc , @rate_value , @asset_bal_sum , @asset_proc_sum , @asset_rate_sum , @asset_id , @place_id , @asset_name , @asset_type while .CURSOR_STATE=0 begin .TYPE_INIT('CLIENT_ASSETS_LIST',1) .TYPE_ASSIGN(asset_bal_sum,@asset_bal_sum,1) .TYPE_ASSIGN(asset_id,@asset_id,1) .TYPE_ASSIGN(asset_name,@asset_name,1) .TYPE_ASSIGN(asset_proc_sum,@asset_proc_sum,1) .TYPE_ASSIGN(asset_qty,@asset_qty,1) .TYPE_ASSIGN(asset_rate_sum,@asset_rate_sum,1) .TYPE_ASSIGN(asset_yield_proc,@asset_yield_proc,1) .TYPE_ASSIGN(client_assets_id,@id,1) .TYPE_ASSIGN(place_id,@place_id,1) .TYPE_ASSIGN(rate_date,@rate_date,1) .TYPE_ASSIGN(rate_value,@rate_value,1) .TYPE_ASSIGN(asset_type,@asset_type,1) .TYPE_INSERT('CLIENT_ASSETS_LIST','CRT',1) fetch list into @rate_date , @asset_qty , @asset_yield_proc , @rate_value , @asset_bal_sum , @asset_proc_sum , @asset_rate_sum , @asset_id , @place_id , @asset_name , @asset_type end close list .DEALLOCATE list -------------------- -- Вводы - выводы -------------------- delete t_items from t_client_assets_inout where t_items.id = t_client_assets_inout.id and t_client_assets_inout.client_assets_id = @id delete t_client_assets_inout where t_client_assets_inout.client_assets_id = @id truncate table #accs insert into #accs (id) select id from t_accounts where acc_num_eff in ('3.0.9','3.0.0','3.3.0') and plan_id = @plan_money_id declare inout cursor for select i.id, upper(t.type_code), abs(isnull(sum(ol.sum_bal * ol.deb_or_cred),0)), min(.DATE_NORM(o.op_date,B)) from t_items i, t_types t, t_operations o, t_oper_list ol, t_oper_subconto os where i.type_id = t.id and i.id = o.item_id and ol.head_id = o.id and o.partner2plan_id = @p2p_money_id and .DATE_NORM(o.op_date,B) <= @calc_date and ol.id = os.id and os.subconto_type_id = @st_inv_id and os.item_id = @contract_id and ol.acc_id in (select id from #accs) -- целевые для вводов/выводов and ((select pu.code from t_purposes pu, t_paydocs d where d.id = i.id and d.purpose_id = pu.id) in (37, 39,64) -- ЦН зачисление/списание ДС or (select pu.code from t_purposes pu, td_stock_moves d where d.id = i.id and d.is_dogovor_id = pu.id) in (29, 32,93) -- ЦН зачисление/снятие ЦБ с клиента ) group by i.id, t.type_code having isnull(sum(ol.sum_bal * ol.deb_or_cred),0) <> 0 for read only open inout fetch inout into @doc_id, @doc_type, @inout_sum, @inout_date while .CURSOR_STATE=0 begin select @inout_name = null, @inout_type = null, @asset_qty = null, @asset_id = null if @doc_type = 'REAL_PAYMENT_IN' select @inout_name = 'Ввод ДС', @inout_type = 1, @asset_id = to_partner_acc_id from t_paydocs where id = @doc_id if @doc_type = 'REAL_PAYMENT_OUT' select @inout_name = 'Вывод ДС', @inout_type = 11, @asset_id = from_partner_acc_id from t_paydocs where id = @doc_id if @doc_type = 'D_STOCK_IN_OTHER_DEPO' select @inout_name = 'Ввод ЦБ', @inout_type = 2, @asset_id = stock_id, @asset_qty = stock_qty from td_stock_moves where id = @doc_id if @doc_type = 'D_STOCK_OUT_OTHER_DEPO' select @inout_name = 'Вывод ЦБ', @inout_type = 12, @asset_id = stock_id, @asset_qty = stock_qty from td_stock_moves where id = @doc_id .TYPE_INIT('CLIENT_ASSETS_INOUT',2) .TYPE_ASSIGN(client_assets_id,@id,2) .TYPE_ASSIGN(doc_id,@doc_id,2) .TYPE_ASSIGN(inout_sum,@inout_sum,2) .TYPE_ASSIGN(inout_date,@inout_date,2) .TYPE_ASSIGN(asset_id,@asset_id,2) .TYPE_ASSIGN(asset_qty,@asset_qty,2) .TYPE_ASSIGN(inout_type,@inout_type,2) .TYPE_ASSIGN(inout_name,@inout_name,2) .TYPE_INSERT('CLIENT_ASSETS_INOUT','CRT',2) fetch inout into @doc_id, @doc_type, @inout_sum, @inout_date end close inout .DEALLOCATE inout -- первый ввод средств select @start_date = (select min(inout_date) from t_client_assets_inout where inout_type in (1,2) and client_assets_id = @id and inout_date <= @calc_date) update t_client_assets_inout set inout_type = 0 where inout_type in (1,2) and client_assets_id = @id and inout_date = @start_date select @days_qty = isnull(datediff(dd, @start_date, @calc_date),0) select @start_sum = isnull((select sum(inout_sum) from t_client_assets_inout where inout_type = 0 and client_assets_id = @id),0) update t_client_assets set start_date = @start_date , start_sum = @start_sum , days_qty = @days_qty where id = @id ------------ -- СЧА ------------ update t_client_assets_list set asset_ocen_sum = isnull(asset_proc_sum,0) + (case isnull(asset_rate_sum,0) when 0 then isnull(asset_bal_sum,0) else isnull(asset_rate_sum,0) end) where client_assets_id = @id select @scha_sum = isnull((select sum(asset_ocen_sum) from t_client_assets_list where client_assets_id = @id),0) update t_client_assets set scha_sum = @scha_sum where id = @id update t_client_assets_list set scha_proc = (convert(float,1.0) * asset_ocen_sum / @scha_sum) * 100 where client_assets_id = @id ------------ -- Доходность ------------ declare @year_days_qty int , @years_qty int , @denominator_sum money select @year_days_qty = 365 if @days_qty > 0 begin select @years_qty = ceiling(convert(float,1.0) * @days_qty / @year_days_qty) select @year_days_qty = @year_days_qty * @years_qty select @denominator_sum = @start_sum -- сумма выводов средств на отношение прошедших дней после вывода ко всем дням - isnull((select sum(round((inout_sum * (convert(float, 1.0) * datediff(dd, inout_date, @calc_date) / @days_qty)),2)) from t_client_assets_inout where inout_type in (11,12) and client_assets_id = @id and inout_date >= @start_date and inout_date <= @calc_date),0) -- сумма вводов средств на отношение прошедших дней после ввода ко всем дням + isnull((select sum(round((inout_sum * (convert(float, 1.0) * datediff(dd, inout_date, @calc_date) / @days_qty)),2)) from t_client_assets_inout where inout_type in (1,2) and client_assets_id = @id and inout_date > @start_date and inout_date <= @calc_date),0) if @denominator_sum <> 0 select @yield_sum = round(( ( convert(float,1.0) * ( @scha_sum - @start_sum -- сумма выводов средств + isnull((select sum(inout_sum) from t_client_assets_inout where inout_type in (11,12) and client_assets_id = @id and inout_date >= @start_date and inout_date <= @calc_date),0) -- сумма вводов средств - isnull((select sum(inout_sum) from t_client_assets_inout where inout_type in (1,2) and client_assets_id = @id and inout_date > @start_date and inout_date <= @calc_date),0) ) / @denominator_sum ) * (convert(float, 1.0) * @year_days_qty / @days_qty) * 100 ), 2) end update t_client_assets set yield_sum = @yield_sum where id = @id ------------------------------------------- .UPDATE_STATE .ACTION_HISTORY commit tran drop table #res drop table #accs .TRANSACTION_RESTORE .END