# Date: 29/06/06 # Version: 105 $DOC(REPORT_FOR_INVESTMENT_ACT_PIF) name=Отчет о составе и структуре активов ПИФ class=6 product=ALD_PIF name_formula=$TEXT(13505501) .TYPE_NAME((.TYPE_ID_FROM_CODE_FUNC('report_for_investment_act_pif'))) + ' на '+.DATE_TO_STR(@date_b)+ ' по срезу ' + .ITEM_NAME(@foundation_id) $ENDTEXT(13505501) remark_formula=null $STATE(CRT) name=Создан class=0 $ENDSTATE $PROP(OUT_DATE) name=Внешняя дата корреспондента field_name=out_date table_name=td_depo_docs field_type=datetime field_length=8 field_scale=3 field_prec=23 page=1 order=1 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(IN_DATE) name=Внутр дата корреспонденции field_name=in_date table_name=td_depo_docs field_type=datetime field_length=8 field_scale=3 field_prec=23 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(WHO_GET) name=Кто принял field_name=who_get table_name=td_depo_docs field_type=varchar field_length=100 field_scale=null field_prec=100 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(WHO_PUT) name=Кто отправил field_name=who_put table_name=td_depo_docs field_type=varchar field_length=100 field_scale=null field_prec=100 page=1 order=4 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(OUT_NO) name=Номер контрагента field_name=out_no table_name=td_depo_docs field_type=varchar field_length=50 field_scale=null field_prec=50 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(IN_NO) name=Номер field_name=in_no table_name=td_depo_docs field_type=varchar field_length=50 field_scale=null field_prec=50 page=1 order=6 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(CORRESPONDENT_ID) name=Корреспондент field_name=correspondent_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=7 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(PAYER_ID) name=Плательщик field_name=payer_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=8 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(PAY_SUM) name=Сумма field_name=pay_sum table_name=td_depo_docs field_type=money field_length=8 field_scale=4 field_prec=19 page=1 order=9 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=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=10 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(DOCUMENT_FORM_ID) name=Форма документа field_name=document_form_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=11 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(DEPO_DOC_TYPE) name=Входящий исходящий field_name=depo_doc_type table_name=td_depo_docs field_type=int field_length=4 field_scale=0 field_prec=10 page=1 order=12 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(FOLDER_ID) name=Папка документов field_name=folder_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=13 init_class=0 init_formula=.DEPO_FOLDER_DEFAULT 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(SUM_CUR_ID) name=Валюта суммы field_name=sum_cur_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=14 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(DOC_COMMENT) name=Коментарии к документу field_name=doc_comment table_name=td_depo_docs field_type=varchar field_length=255 field_scale=null field_prec=255 page=1 order=15 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(FOUNDATION_ID) name=Документ основание field_name=foundation_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=16 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=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=17 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(DATE_B) name=Дата отчета field_name=date_b table_name=td_queries field_type=datetime field_length=8 field_scale=3 field_prec=23 page=2 order=18 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(QUERY_TYPE) name=Тип просмотра отчета field_name=query_type table_name=td_queries field_type=int field_length=4 field_scale=0 field_prec=10 page=2 order=19 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 $RELATION(REL3) class=0 type=PARTNERS form=null prop=CORRESPONDENT_ID idname=null order=null $ENDRELATION $RELATION(REL2) class=0 type=PARTNERS form=null prop=PAYER_ID idname=null order=null $ENDRELATION $RELATION(REL1) class=0 type=PARTNERS form=null prop=INITIATOR_ID idname=null order=null $ENDRELATION $RELATION(REL4) class=0 type=ITEMS form=null prop=FOUNDATION_ID idname=null order=null $ENDRELATION $RELATION(REL5) class=0 type=NPF_FUND form=null prop=OWNER_ID idname=null order=null $ENDRELATION $FORM(FORM1) name=Просмотр class=5 filter=null target_state= uo=uo_edit dw=d_pif_depooper_p1 procedure=ap_10000000000000583 patterns=GENERIC_VIEW_ITEM_P1 user_define=0 isvisible=0 istab=1 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505507) create procedure dbo.%PROC% @sid varchar(30) = null as declare @id numeric, @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @type_id_s varchar(30), @new_state_id numeric, @state_name_id_s varchar(30) begin select @id = convert(numeric, @sid) select @action_id = %ACTION_ID% select @type_id_s = NULL select @state_name_id_s = NULL .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(13505507) $ACTION2RELATION(REL4.ITEM_NAME) relation=REL4 where=null prop=ITEM_NAME order=2 alias=foundation_name $ENDACTION2RELATION $ACTION2RELATION(REL5.PARTNER_CODE) relation=REL5 where=null prop=PARTNER_CODE order=1 alias=found_code $ENDACTION2RELATION $ACTION2RELATION(REL5.PARTNER_SHORT_NAME) relation=REL5 where=null prop=PARTNER_SHORT_NAME order=3 alias=found_name $ENDACTION2RELATION $ACTION2RELATION(REL1.PARTNER_CODE) relation=REL1 where=null prop=PARTNER_CODE order=1 alias=initiator_code $ENDACTION2RELATION $ACTION2RELATION(REL1.PARTNER_SHORT_NAME) relation=REL1 where=null prop=PARTNER_SHORT_NAME order=4 alias=initiator_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=1 alias=corr_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=4 alias=corr_short_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_CODE) relation=REL2 where=null prop=PARTNER_CODE order=1 alias=payer_code $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=4 alias=payer_short_name $ENDACTION2RELATION $BIND(BIND1) type=REPORT_FOR_INVESTMENT_ACT_PIF action=FORM4 class=1 order=2 idname=null $ENDBIND $ENDFORM $FORM(FORM2) name=Просмотр отчета class=5 filter=null target_state= uo=uo_list_rep dw=d_investment_pif_head procedure=ap_investment_pif_head patterns=GENERIC_VIEW_ITEM user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505507) create procedure dbo.%PROC% @sid varchar(30) = null as declare @id numeric, @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric begin select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .CHECK_USER .CHECK_VIEW_ITEM .GET_PROPS /* select convert(varchar(30),@id) as sid, t_partners.partner_name as fond_name, t_partners.partner_phone as phone, t_partners.real_code+' '+t_partners.partner_address as fact_adr, @date_b as rep_date from t_partners where t_partners.id=@owner_id */ declare @fond_name varchar(255) declare @regist_emis varchar(255) declare @control_name varchar(255) declare @control_lic varchar(255) declare @control_addres varchar(255) declare @fund_type numeric declare @type_string varchar(255) select @fond_name = partner_name, @control_lic = isnull(t_partners.regist_sertificate_no + ' ', '') + isnull(convert(varchar, t_partners.regist_sertificate_date, 104) + ' ', '') + isnull(t_partners.regist_organisation + ' ', '') from t_partners where t_partners.id = @owner_id select @fund_type = td_pay_fond.fond_type from td_pay_fond where td_pay_fond.id = @owner_id select @control_name = t_partners.partner_name, @control_lic = isnull(t_partners.regist_sertificate_no + ' ', '') + isnull(convert(varchar, t_partners.regist_sertificate_date, 104) + ' ', '') + isnull(t_partners.regist_organisation + ' ', ''), @control_addres = isnull(t_partners.real_code + ' ', '') + isnull(t_partners.partner_address + ' ', '') + + isnull(t_partners.partner_phone, ''), @fund_type = td_pay_fond.fond_type from t_partners, td_pay_fond where td_pay_fond.id = @owner_id and td_pay_fond.ctrl_comp_id = t_partners.id select @control_lic = (select lic_autor + ' № ' + lic_no + ' от ' + convert(varchar,lic_date,104) from t_licence, td_pay_fond where td_pay_fond.id=@owner_id and .ITEMS_EXISTS(t_licence.id) and t_licence.partner_id=td_pay_fond.ctrl_comp_id and t_licence.activity_id=4) if @fund_type = 1 select @type_string = 'открытого' else select @type_string = 'интервального' select @sid, @fond_name, @regist_emis, @control_name, @control_lic, @control_addres, @type_string, @date_b as rep_date, @in_no as in_no, @in_date as in_date end $ENDTEXT(13505507) $ENDFORM $FORM(FORM3) name=Перечень отчета class=5 filter=null target_state= uo=null dw=d_investment_pif_det procedure=ap_investment_pif_det patterns=GENERIC_VIEW_ITEM user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505507) create procedure dbo.%PROC% @sid varchar(30) = null as declare @id numeric, @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric begin select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .ID_VAR(@owner_id) .INT_VAR(@query_type) .ID_VAR(@rep_id) .ID_VAR(@foundation_id) .TRANSACTION_SAVE create table #object_names ( asset_id numeric(18,0) not null, asset_name varchar(255) null ) select @rep_id=@id if upper(.ITEM_TYPE_CODE(@id)) in ('REPORT_INVESTMENT_STRUCT_DIV','PIF_SREZ') begin if .ITEM_TYPE_CODE(@id)='PIF_SREZ' select @foundation_id=@id else select @foundation_id=foundation_id from td_depo_docs where id=@id select @rep_id=td_depo_docs.id from td_depo_docs where td_depo_docs.foundation_id=@foundation_id and .ITEMS_EXISTS_BY_TYPE(td_depo_docs.id,'REPORT_FOR_INVESTMENT_ACT_PIF') end insert into #object_names (asset_id) select object_id from ts_investment_report where id=@rep_id and object_id is not null group by object_id update #object_names set asset_name=t.partner_name from t_partners t where t.id=#object_names.asset_id update #object_names set asset_name=t.bank_name from t_banks t where t.id=#object_names.asset_id update #object_names set asset_name=t.emi_name from td_emitent_info t where t.id=#object_names.asset_id update #object_names set asset_name=t.stock_name from td_stock_emis t where t.id=#object_names.asset_id update #object_names set asset_name='Счет № '+t.bank_account_no+', Банк: '+isnull(b.bank_name,'') from t_bank_accounts t, t_banks b where t.id=#object_names.asset_id and t.bank_id*=b.id update #object_names set asset_name=t.item_name from t_items t where t.id=#object_names.asset_id and isnull(#object_names.asset_name,'')='' if upper(.ITEM_TYPE_CODE(@id))='REPORT_FOR_INVESTMENT_ACT_PIF' begin select @owner_id=owner_id from td_depo_docs where id=@id select @query_type=query_type from td_queries where id=@id select case when (ts_restrict_types.calk_type_id in (3,5) or ts_restrict_types.lim_code in ('NKD','DEB')) then '' else #object_names.asset_name end as item_name, isnull(rep.bal_sum,0) as bal_sum, isnull(rep.ocen_sum,0) as ocen_sum, isnull(rep.nkd_sum,0) as nkd_sum, isnull(rep.object_qty_d,rep.object_qty) as object_qty, rep.line_name as lim_code, case when ts_restrict_types.is_control=0 or (isnull(rep.min_lim_value,0)=0 and isnull(rep.max_lim_value,100)=100) then '' when rep.min_lim_value=0 and rep.max_lim_value=0 then ' (вложения запрещены)' else ' (% по нормативу - от '+(convert(varchar,isnull(rep.min_lim_value,0))+' до '+convert(varchar,isnull(rep.max_lim_value,100)))+')' end as plan_percent, isnull(rep.fact_percent,0) as fact_percent, case when rep.line_code_eff like 'CNT%' then 1 when rep.line_code_eff like 'STR%' then 2 else 2 end as section_no, case when rep.line_code_eff like 'CNT%' then (select lim_name from ts_restrict_types where lim_code='CNT') when rep.line_code_eff like 'STR%' then (select lim_name from ts_restrict_types where lim_code='STR') else (select lim_name from ts_restrict_types where lim_code='STR') end as section_name, @query_type as query_type from ts_investment_report rep, #object_names, ts_restrict_types where rep.id=@rep_id and ts_restrict_types.id=rep.line_id and #object_names.asset_id=*rep.object_id and (exists(select 1 from ts_restrict_values where organization_id=@owner_id and lim_id=rep.line_id) or ts_restrict_types.is_control=0 or ts_restrict_types.lim_code_eff like 'CNT%') and .ITEMS_EXISTS(ts_restrict_types.id) and (((ts_restrict_types.calk_type_id in (3,5) or ts_restrict_types.lim_code in ('NKD','DEB')) and isnull(rep.ocen_sum,0)<>0 and @query_type=1) or (ts_restrict_types.calk_type_id not in (3,5) and ts_restrict_types.lim_code not in ('NKD','DEB') and rep.object_id is not null and @query_type=1) or @query_type=0) order by rep.line_code_eff end if upper(.ITEM_TYPE_CODE(@id)) in ('REPORT_INVESTMENT_STRUCT_DIV','PIF_SREZ') begin select @owner_id=owner_id from td_depo_docs where id=@rep_id select @query_type=query_type from td_queries where id=@rep_id select case when (ts_restrict_types.calk_type_id in (3,5) or ts_restrict_types.lim_code in ('NKD','DEB')) then '' else #object_names.asset_name end as item_name, isnull(rep.bal_sum,0), isnull(rep.ocen_sum,0), isnull(rep.nkd_sum,0) as nkd_sum, isnull(rep.object_qty_d,rep.object_qty) as object_qty, rep.line_name as lim_code, case when ts_restrict_types.is_control=0 or (isnull(rep.min_lim_value,0)=0 and isnull(rep.max_lim_value,100)=100) then '' when rep.min_lim_value=0 and rep.max_lim_value=0 then ' (вложения запрещены)' else ' (% по нормативу - от '+(convert(varchar,isnull(rep.min_lim_value,0))+' до '+convert(varchar,isnull(rep.max_lim_value,100)))+')' end as plan_percent, isnull(rep.fact_percent,0), 0 as section_no, 'Расшифровка пунктов, по которым возникли нарушения:' as section_name, @query_type as query_type from ts_investment_report rep, #object_names, ts_restrict_types where rep.id=@rep_id and ts_restrict_types.id=rep.line_id and rep.line_id in (select lim_id from ts_asset_structure_div,.ACTUAL_TABLES where ts_asset_structure_div.report_id=@foundation_id and isnull(ts_asset_structure_div.is_actual,1)=1 .ACTUAL_WHERE(ts_asset_structure_div.id)) and ts_restrict_types.calk_type_id not in (6,7) and #object_names.asset_id=*rep.object_id and (exists(select 1 from ts_restrict_values where organization_id=@owner_id and lim_id=rep.line_id) or ts_restrict_types.is_control=0) and .ITEMS_EXISTS(ts_restrict_types.id) order by rep.line_code_eff end drop table #object_names .TRANSACTION_RESTORE end $ENDTEXT(13505507) $ENDFORM $FORM(FORM4) name=Данные class=5 filter=null target_state= uo=uo_edit dw=d_investment_pif_p2 procedure=ap_10000000000088523 patterns=GENERIC_VIEW_ITEM_P2 user_define=0 isvisible=1 istab=1 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505507) create procedure dbo.%PROC% @sid varchar(30) = null as declare @id numeric, @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @new_state_id numeric begin select @id = convert(numeric, @sid) select @action_id = %ACTION_ID% .CHECK_USER .CHECK_VIEW_ITEM .VIEW_ITEM(ALL,2) end $ENDTEXT(13505507) $ENDFORM $FORM(OLD_D) name=Перечень отчета (старый) class=5 filter=null target_state= uo=null dw=d_old_investment_pif_d procedure=ap_old_investment_pif_d patterns=GENERIC_VIEW_ITEM user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505507) create procedure dbo.%PROC% @sid varchar(30) = null as declare @id numeric, @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric begin select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .GET_PROPS create table #class2asset( asset_id numeric null, class_risk varchar(255) null, class_dohod varchar(255) null) .ID_VAR(@asset_id) .NAME_VAR(@risk) .NAME_VAR(@dohod) declare cur1 cursor for select distinct rep.object_id from ts_investment_report rep where id=@id and rep.object_id is not null open cur1 fetch cur1 into @asset_id while .CURSOR_STATE=0 begin select @risk=t_nclass_values.value_code from t_nclass_types, t_nclass_value2item, t_nclass_values where t_nclass_value2item.item_id=@asset_id and t_nclass_value2item.class_type_id=t_nclass_types.id and t_nclass_values.id=t_nclass_value2item.class_value_id and upper(t_nclass_types.class_code)=upper('ASSET_RISKS') and t_nclass_value2item.partner_id=@owner_id and t_nclass_value2item.set_date=(select max(t_n_v.set_date) from t_nclass_value2item t_n_v,t_nclass_types t_n_t,t_nclass_values t_n_c where t_n_v.item_id=@asset_id and t_n_v.class_type_id=t_n_t.id and t_n_c.id=t_n_v.class_value_id and upper(t_n_t.class_code)=upper('ASSET_RISKS') and t_n_v.set_date'') select @id_index = (select id from t_indices where Upper(ltrim(rtrim(index_code))) = Upper(ltrim(rtrim(@code_idx))) and .ITEMS_EXISTS_BY_TYPE(t_indices.id,'T_INDICES')) else select @id_index = null ------------------------------------------ create table #line_value( line_id numeric null, plan_min_percent money null, plan_max_percent money null, line_code_eff varchar(50) null, fact_percent money null, is_last int, line_name varchar(255), object_id numeric(18,0) null) insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, 0, ts_restrict_types.is_last, ts_restrict_types.lim_name, null from ts_restrict_values,ts_restrict_types,.ACTUAL_TABLES where ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null .ACTUAL_WHERE(ts_restrict_values.id) -- исключиь специальные пункты and ts_restrict_types.calk_type_id not in (6,7,8) update #line_value set fact_percent=(select sum(isnull(ts_investment_report.fact_percent,0)) from ts_investment_report where ts_investment_report.id=@rep_struct_id and ts_investment_report.line_id=#line_value.line_id) -- специальные пункты -- (EMITENT) - по каждому эмитенту insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=(select partner_id from td_emitent_info where id=rep.object_id)),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and (ts_restrict_types.lim_code in ('EMITENT','UKPIF') or ts_restrict_types.calk_type_id=8) .ACTUAL_WHERE(ts_restrict_values.id) and rep.object_id is not null -- (GOS) - по выпускам гос.бумаг insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select stock_name from td_stock_emis where id=rep.object_id),'Без наименования')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and ts_restrict_types.lim_code in ('GOS','PIF') .ACTUAL_WHERE(ts_restrict_values.id) -- (BANK) - по вкладам в один банк insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=rep.object_id),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and ts_restrict_types.lim_code='BANK' .ACTUAL_WHERE(ts_restrict_values.id) ----- STOCK_NOT_RATES Оценочная стоимость ЦБ, не имеющих признаваемой котировки за исключением паев ОПИФ --- insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=rep.object_id),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and ts_restrict_types.lim_code='STOCK_NOT_RATES' .ACTUAL_WHERE(ts_restrict_values.id) ------------------------------------------------------------------- --- CNT.IDX_STOCK наличие бумаг не входящих в индекс (только для индексных фондов) -- if @id_index is not null begin insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=rep.object_id),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and ts_restrict_types.lim_code='IDX_STOCK' .ACTUAL_WHERE(ts_restrict_values.id) end ------------------------------------------------------------------------------------- ----- IDX.1 Оценочная стоимость ЦБ, по которым рассчитывается индекс --- insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=rep.object_id),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and ts_restrict_types.lim_code='IDX.1' .ACTUAL_WHERE(ts_restrict_values.id) ------------------------------------------------------------------- --- IDX.EMITENT Оценочная стоимость ЦБ одного эмитента, по которым расчитывается индекс --------- insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=(select partner_id from td_emitent_info where id=rep.object_id)),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id -- and ts_restrict_values.max_lim_value is not null and (ts_restrict_types.lim_code = 'IDX.EMITENT') ---or ts_restrict_types.calk_type_id=8) .ACTUAL_WHERE(ts_restrict_values.id) and rep.object_id is not null ------------------------------------------------------------------- /*IDX.2 Разница м/д долей ЦБ одного эмитента в суммарной капитализации ЦБ, по которым расчитывается индекс и долей оценочной стоимости этих бумаг в активах фонда */ insert #line_value select ts_restrict_values.lim_id, ts_restrict_values.min_lim_value, ts_restrict_values.max_lim_value, ts_restrict_types.lim_code_eff, isnull(rep.fact_percent,0), ts_restrict_types.is_last, ts_restrict_types.lim_name+' ('+ isnull((select partner_name from t_partners where id=(select partner_id from td_emitent_info where id=rep.object_id)),'')+')', rep.object_id from ts_restrict_values,ts_restrict_types,ts_investment_report rep,.ACTUAL_TABLES where rep.id=@rep_struct_id and rep.line_id=ts_restrict_values.lim_id and ts_restrict_values.organization_id=@owner_id and ts_restrict_types.id=ts_restrict_values.lim_id and ts_restrict_values.max_lim_value is not null and (ts_restrict_types.lim_code = 'IDX.2') ---or ts_restrict_types.calk_type_id=8) .ACTUAL_WHERE(ts_restrict_values.id) and rep.object_id is not null ---------------------------------------------------------------------------------------------- begin tran declare @cur_line_id numeric, @cur_plan_min_percent money, @cur_plan_max_percent money, @cur_line_code varchar(50), @cur_fact_percent money, @cur_line_name varchar(255) declare cur1 cursor for select line_id,plan_min_percent,plan_max_percent,line_code_eff,fact_percent,line_name,object_id from #line_value open cur1 fetch cur1 into @cur_line_id,@cur_plan_min_percent,@cur_plan_max_percent,@cur_line_code,@cur_fact_percent,@cur_line_name,@cur_object_id while .CURSOR_STATE=0 begin if (@cur_fact_percent not between isnull(@cur_plan_min_percent,0) and @cur_plan_max_percent) begin if @cur_line_code = 'STR.IDX.1.' select @isaktual = 0 else select @isaktual = 1 select @id_srez_back = id from tp_pif_srez where fond_id = (select fond_id from tp_pif_srez where id = @foundation_id) and srez_date = (select max(srez_date) from tp_pif_srez where srez_date < (select srez_date from tp_pif_srez where id = @foundation_id) and fond_id = (select fond_id from tp_pif_srez where id = @foundation_id)) select @date_lim_id = max(date_lim_id) from ts_asset_structure_div where report_id = @id_srez_back and lim_id = @cur_line_id if @date_lim_id is null select @date_lim_id = @date_b .TYPE_INIT('asset_structure_div_list',1) .TYPE_ASSIGN(lim_id,@cur_line_id,1) .TYPE_ASSIGN(lim_code,@cur_line_code,1) .TYPE_ASSIGN(lim_name,@cur_line_name,1) .TYPE_ASSIGN(report_id,@foundation_id,1) .TYPE_ASSIGN(min_lim_value,@cur_plan_min_percent,1) .TYPE_ASSIGN(max_lim_value,@cur_plan_max_percent,1) .TYPE_ASSIGN(calc_lim_value,@cur_fact_percent,1) .TYPE_ASSIGN(object_id,@cur_object_id,1) .TYPE_ASSIGN(date_lim_id,@date_lim_id,1) .TYPE_ASSIGN(is_actual,@isaktual,1) .TYPE_INSERT('asset_structure_div_list','CRT',1) end fetch cur1 into @cur_line_id,@cur_plan_min_percent,@cur_plan_max_percent,@cur_line_code,@cur_fact_percent,@cur_line_name,@cur_object_id end close cur1 .DEALLOCATE cur1 --------------------------------------------------------------------- ---- Контроль соблюдения ограничения в течение 2/3 рабочих дней ----- --------------------------------------------------------------------- /* Работает следующим образом: В первый день нового периода (период в зависимости от типа фонда :открытый фонд - месяц, интервыльный -квартал, закрытый - год. Период календарный, т.е. первый день месяца, первый день года) создается срез , в нем проверяется количество ошибочных и верных ограничений прошлого периода. Если количество верных будет меньше чем в 2/3 дней периода, то формируется ошибка о нарушении ограничения в 2/3 дней. Проверяемые ограничения зависят от установленного значения классификатора "Типы ПИФ по 31-му постановлению" в фонде: если установлен тип 0(все типы фондов) или 4(Фонд смешанных инвестиций) то проверка ограничения ведется по STR.03. (Оценочная стоимость государственных и муниципальных ЦБ РФ, акций ОАО РФ, облигаций хоз.обществ РФ, акций иностранных акционерных обществ, ЦБ иностранных государств, международных финансовых институтов, облигаций иностранных коммерческих организаций), если 2(Фонд облигаций), то по STR.03.2. (Оценочная стоимость государственных и муниципальных ЦБ РФ, облигаций хоз.обществ РФ, ЦБ иностранных государств, международных финансовых институтов, облигаций иностранных коммерческих организаций), если 3 (Фонд акций), то по STR.10. (Оценочная стоимость акций российских АО, за исключением акций российских акционерных инвестиционных фондов, и акций иностранных акционерных обществ). Так же можно установить код отслеживаемого ограничения в классификаторе в фонде (классификатор Пункт структуры активов ПИФ для контроля за период со значением Контроль соблюдения ограничения в течение 2/3 рабочих дней и в шифре устанавливается код ограничения Например для фонда фондов шифр: STR.11.) */ set datefirst 1 declare @fund_type int, @is_calc int, @date_start datetime, @p_line_id numeric, @p_line_code varchar(50), @code_31ps varchar(50), @lim_str varchar(50), @all_qty int, @good_qty int select @is_calc=0 select @fund_type=isnull((select fond_type from td_pay_fond where id=@owner_id),1) select @lim_str='Менее 2/3 рабочих дней в течение одного ' +(case @fund_type when 1 then 'месяца' when 2 then 'квартала' when 3 then 'года' end) + ': ' -- 1-й срез в периоде if not exists(select 1 from tp_pif_srez where fond_id=@owner_id and srez_date<@date_b and datepart(mm,srez_date)=datepart(mm,@date_b) and datepart(yy,srez_date)=datepart(yy,@date_b)) and (@fund_type=1 or (@fund_type=2 and datepart(mm,@date_b) in (1,4,7,10)) or (@fund_type=3 and datepart(mm,@date_b)=1)) select @is_calc=1 else select @is_calc=0 select @date_start=(case @fund_type when 1 then convert(datetime,stuff(convert(varchar(8),dateadd(mm,-1,@date_b),3),1,2,'01'),3) when 2 then convert(datetime,stuff(convert(varchar(8),dateadd(mm,-3,@date_b),3),1,2,'01'),3) when 3 then convert(datetime,stuff(convert(varchar(8),dateadd(yy,-1,@date_b),3),1,2,'01'),3) end) -- id ограничения if @is_calc=1 begin select @cur_line_id=null select @cur_line_id=t.id, @cur_line_code=t.lim_code from ts_restrict_values v,ts_restrict_types t where v.organization_id=@owner_id and t.id=v.lim_id and t.lim_code='PERIOD_2/3' and .ITEMS_EXISTS(v.id) if @cur_line_id is null select @is_calc=0 end -- пункт структуры для контроля if @is_calc=1 begin select @p_line_code=(select min(i.code) from t_classvalues v, t_classes c, t_item2class i where c.id=v.class_id and c.code='PIF_STRUCT_4_PERIOD_ESTIMATE' and i.value_id=v.id and v.code='PERIOD_2/3' and i.item_id=@owner_id and isnull(i.code,'')<>'') if isnull(@p_line_code,'')='' begin select @code_31ps=isnull((select min(v.code) from t_classvalues v, t_classes c, t_item2class i where c.id=v.class_id and c.code='PIF_TYPES_31' and i.item_id=@owner_id and i.value_id=v.id),'0') -- по умолчанию для if @code_31ps='0' or @code_31ps='4' select @p_line_code='STR.03.' -- смешанного (любого) if @code_31ps='3' select @p_line_code='STR.10.' -- акций if @code_31ps='2' select @p_line_code='STR.03.2.' -- облигаций end select @p_line_id=t.id, @cur_line_name=@lim_str+t.lim_name, @cur_plan_min_percent=v.min_lim_value, @cur_plan_max_percent=v.max_lim_value from ts_restrict_values v, ts_restrict_types t where v.organization_id=@owner_id and t.id=v.lim_id and t.lim_code_eff=@p_line_code and v.max_lim_value is not null and .ITEMS_EXISTS(v.id) if @p_line_id is null select @is_calc=0 end if @is_calc=1 begin select @all_qty=(select count(*) from tp_pif_srez s where s.fond_id=@owner_id and s.srez_date<@date_b and s.srez_date>=@date_start and s.id=(select max(d.id) from tp_pif_srez d where d.fond_id=@owner_id and d.srez_date=s.srez_date)) if @all_qty <> 0 begin select @good_qty=(select count(*) from tp_pif_srez s where s.fond_id=@owner_id and s.srez_date<@date_b and s.srez_date>=@date_start and s.id=(select max(d.id) from tp_pif_srez d where d.fond_id=@owner_id and d.srez_date=s.srez_date) and isnull((select sum(r.fact_percent) from ts_investment_report r where r.srez_id=s.id and r.line_id=@p_line_id),0) between isnull(@cur_plan_min_percent,0) and @cur_plan_max_percent) if convert(float,1.0)*@good_qty/@all_qty < convert(float,1.0)*2/3 begin select @id_srez_back = id from tp_pif_srez where fond_id = (select fond_id from tp_pif_srez where id = @foundation_id) and srez_date = (select max(srez_date) from tp_pif_srez where srez_date < (select srez_date from tp_pif_srez where id = @foundation_id) and fond_id = (select fond_id from tp_pif_srez where id = @foundation_id)) select @date_lim_id = max(date_lim_id) from ts_asset_structure_div where report_id = @id_srez_back and lim_id = @cur_line_id if @date_lim_id is null select @date_lim_id = @date_b .TYPE_INIT('asset_structure_div_list',2) .TYPE_ASSIGN(lim_id,@cur_line_id,2) .TYPE_ASSIGN(lim_code,@cur_line_code,2) .TYPE_ASSIGN(lim_name,@cur_line_name,2) .TYPE_ASSIGN(report_id,@foundation_id,2) .TYPE_ASSIGN(min_lim_value,@cur_plan_min_percent,2) .TYPE_ASSIGN(max_lim_value,@cur_plan_max_percent,2) .TYPE_ASSIGN(calc_lim_value,null,2) .TYPE_ASSIGN(date_lim_id,@date_lim_id,2) .TYPE_ASSIGN(object_id,null,2) .TYPE_ASSIGN(is_actual,1,2) .TYPE_INSERT('asset_structure_div_list','CRT',2) end end end ------------------------------------------------------------------------ ------------------------------------------------------------------------ ---- Контроль соблюдения ограничения по сделкам с некотируемыми ЦБ ----- ------------------------------------------------------------------------ set datefirst 1 declare @trade_id numeric, @stock_id numeric, @trade_date datetime, @rate_date datetime, @trade_no varchar(50), @stock_name varchar(255), @trade_type varchar(50), @viol_name varchar(255) select @cur_line_id=null select @cur_line_id=t.id, @cur_line_code=t.lim_code, @cur_line_name=t.lim_name from ts_restrict_values v,ts_restrict_types t where v.organization_id=@owner_id and t.id=v.lim_id and t.lim_code='TRADES_NOT_RATES' and .ITEMS_EXISTS(v.id) if @cur_line_id is not null begin declare trades cursor for select distinct b.id, b.stock_id, b.transaction_date, b.transaction_no, s.stock_name, (case upper(.ITEM_TYPE_CODE(b.id)) when 'SD_BROK_BUY' then 'Покупка ' else 'Продажа ' end) from tb_baysale_docitems b, td_stock_emis s where exists(select 1 from t_operations where .DATE_NORM(op_date,B)=@date_b and item_id=b.id) and .ITEMS_EXISTS(b.id) and upper(.ITEM_TYPE_CODE(b.id)) in ('SD_BROK_BUY') and s.id=b.stock_id open trades fetch trades into @trade_id, @stock_id, @trade_date, @trade_no, @stock_name, @trade_type while .CURSOR_STATE=0 begin select @rate_date=@trade_date if not exists(select 1 from tb_stock_rates where rate_date=@rate_date and stock_id=@stock_id and (isnull(stock_rate,0)>0 or isnull(acknowledged_price,0)>0) and isnull(rate_type,1)=1 and .ITEMS_EXISTS(tb_stock_rates.id)) begin select @rate_date=dateadd(dd,-1,@rate_date) while datepart(dw,@rate_date) in (6,7) select @rate_date=dateadd(dd,-1,@rate_date) if not exists(select 1 from tb_stock_rates where rate_date=@rate_date and stock_id=@stock_id and (isnull(stock_rate,0)>0 or isnull(acknowledged_price,0)>0) and isnull(rate_type,1)=1 and .ITEMS_EXISTS(tb_stock_rates.id)) begin select @viol_name=@cur_line_name+': '+@trade_type+@stock_name+', сделка №'+@trade_no+' от '+convert(varchar,@trade_date,3) select @id_srez_back = id from tp_pif_srez where fond_id = (select fond_id from tp_pif_srez where id = @foundation_id) and srez_date = (select max(srez_date) from tp_pif_srez where srez_date < (select srez_date from tp_pif_srez where id = @foundation_id) and fond_id = (select fond_id from tp_pif_srez where id = @foundation_id)) select @date_lim_id = max(date_lim_id) from ts_asset_structure_div where report_id = @id_srez_back and lim_id = @cur_line_id if @date_lim_id is null select @date_lim_id = @date_b .TYPE_INIT('asset_structure_div_list',3) .TYPE_ASSIGN(lim_id,@cur_line_id,3) .TYPE_ASSIGN(lim_code,@cur_line_code,3) .TYPE_ASSIGN(lim_name,@viol_name,3) .TYPE_ASSIGN(report_id,@foundation_id,3) .TYPE_ASSIGN(date_lim_id,@date_lim_id,3) .TYPE_ASSIGN(object_id,@stock_id,3) .TYPE_ASSIGN(is_actual,1,3) .TYPE_INSERT('asset_structure_div_list','CRT',3) end end fetch trades into @trade_id, @stock_id, @trade_date, @trade_no, @stock_name, @trade_type end close trades .DEALLOCATE trades end ----------------------------------- /* /*IDX.2 Разница м/д долей ЦБ одного эмитента в суммарной капитализации ЦБ, по которым расчитывается индекс и долей оценочной стоимости этих бумаг в активах фонда */ declare @emit_id numeric, @lim_raznici numeric, @raznica numeric(18,4), @dol_stock_emit numeric select @cur_line_id=null select @cur_line_id=t.id, @cur_line_code=t.lim_code_eff, @cur_line_name=t.lim_name,@lim_raznici=t.def_lim_value from ts_restrict_values v,ts_restrict_types t where v.organization_id=@owner_id and t.id=v.lim_id and t.lim_code='IDX.2' and .ITEMS_EXISTS(v.id) if @cur_line_id is not null begin declare #emitent cursor for select object_id from #line_value where line_code_eff = 'STR.IDX.EMITENT.' open #emitent fetch #emitent into @emit_id while @@fetch_status=0 begin select @dol_stock_emit = isnull(sum(index_proc),0) from t_indices_values where rate_date = @date_b and index_id = @id_index and stock_id in (select id from td_stock_emis where emmitent_id = @emit_id ) and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES') select @raznica =abs(@dol_stock_emit - (select isnull(fact_percent,0) from #line_value where line_code_eff = 'STR.IDX.EMITENT.' and object_id = @emit_id)) if @raznica > @lim_raznici begin select @id_srez_back = id from tp_pif_srez where fond_id = (select fond_id from tp_pif_srez where id = @foundation_id) and srez_date = (select max(srez_date) from tp_pif_srez where srez_date < (select srez_date from tp_pif_srez where id = @foundation_id) and fond_id = (select fond_id from tp_pif_srez where id = @foundation_id)) select @date_lim_id = max(date_lim_id) from ts_asset_structure_div where report_id = @id_srez_back and lim_id = @cur_line_id if @date_lim_id is null select @date_lim_id = @date_b .TYPE_INIT('asset_structure_div_list',4) .TYPE_ASSIGN(lim_id,@cur_line_id,4) .TYPE_ASSIGN(lim_code,@cur_line_code,4) .TYPE_ASSIGN(lim_name,@cur_line_name,4) .TYPE_ASSIGN(report_id,@foundation_id,4) .TYPE_ASSIGN(min_lim_value,@cur_plan_min_percent,4) .TYPE_ASSIGN(max_lim_value,@lim_raznici,4) .TYPE_ASSIGN(calc_lim_value,@raznica,4) .TYPE_ASSIGN(date_lim_id,@date_lim_id,4) .TYPE_ASSIGN(object_id,@emit_id,4) .TYPE_ASSIGN(is_actual,1,4) .TYPE_INSERT('asset_structure_div_list','CRT',4) end fetch #emitent into @emit_id end close #emitent .DEALLOCATE #emitent end --------------------------------------------------- */ commit tran drop table #line_value .TRAN_COMMIT(@trancount) .UPDATE_STATE commit tran $ENDTEXT(13505509) $ENDACTION $ACTION(INVESTMENT_ACT_PIF_RECALC) name=Пересчет отчета class=9 form=null target_state=CRT procedure=ap_investment_pif_recalc patterns=GENERIC_MOVE user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505509) 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 .ACTION_HISTORY /* таблица для отчета CREATE TABLE dbo.ts_investment_report ( id numeric(18, 0) NOT NULL , srez_id numeric(18, 0) NULL , line_id numeric(18, 0) NULL , line_name varchar (255) NULL , line_code varchar (50) NULL , stock_type_id numeric(18, 0) NULL , stock_rates_id numeric(18, 0) NULL , emi_id numeric(18, 0) NULL , emi_name varchar (255) NULL , bal_sum money NULL , plan_percent money NULL , fact_percent money NULL , is_show int NULL , rel_id numeric(18, 0) NULL , parent_id numeric(18, 0) NULL , object_id numeric(18, 0) NULL , object_name varchar (255) NULL , object_qty money NULL , object_qty_d decimal(18,8) NULL , line_code_eff varchar (50) NULL , min_lim_value money NULL , max_lim_value money NULL , ocen_sum money NULL, nkd_sum money NULL, em_qty numeric NULL, em_qty_dec decimal(36,8) NULL ) */ delete ts_investment_report where id=@id declare @plan_id numeric select @plan_id = .ARG_PLAN('PIF_ACCOUNT') if @plan_id is null .EXIT_MESSAGE('Не найден план PIF_ACCOUNT') declare @acc766_id numeric, @acc7610_id numeric, @acc551_id numeric select @acc766_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.6') select @acc7610_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.10') select @acc551_id=.SD_ACCID_FROM_EFFNO(@plan_id,'55.1') declare @rel_id numeric select @rel_id=max(ls.id) from ts_lim_sections ls where upper(ls.lim_sect_code)='PIF' and .ITEMS_EXISTS(ls.id) if @rel_id is null or not exists(select 1 from ts_restrict_types rt where .ITEMS_EXISTS(rt.id,'RESTRICTS') and rt.section_id=@rel_id) .EXIT_MESSAGE('Нет настроек состава и структуры для ПИФ') ---- проверка на наличие классификатора для индексного фонда ------- declare @id_index numeric, --- id индекса который установлен в индексном фонде @code_idx varchar(30) if exists(select v.* from t_classvalues v, t_classes c, t_item2class i where c.id=v.class_id and c.code='PIF_TYPES_31' and i.value_id=v.id and v.code='8' and i.item_id=@owner_id) begin select @code_idx = Upper(ltrim(rtrim((select v.code from t_classvalues v, t_classes c, t_item2class i where c.id=v.class_id and c.code='TYPE_IDX_FOND' and i.value_id=v.id and i.item_id=@owner_id)))) if (@code_idx is not null) and (@code_idx<>'') select @id_index = (select id from t_indices where Upper(ltrim(rtrim(index_code))) = Upper(ltrim(rtrim(@code_idx))) and .ITEMS_EXISTS_BY_TYPE(t_indices.id,'T_INDICES')) else .EXIT_MESSAGE('Не установлен классификатор Тип индекса фонда или его значение пусто ') end -------------------------------------------------------------------- .TRANSACTION_SAVE create table #report( line_id numeric, bal_sum money null, object_id numeric null, uk_id numeric null, object_qty money null, object_qty_d decimal(18,8) null, is_show int, line_code_eff varchar(50), pl_min int null, ocen_sum money null, nkd_sum money null, em_qty numeric null, em_qty_d decimal(36,8) null) create table #object(id numeric) create table #next_object(id numeric) declare @line_code_eff1 varchar(100) declare @acc_num varchar(100) declare @emi_class_type_id numeric, @rate_class_type_id numeric, @rate_yes_id numeric, @rate_no_id numeric, @emi_gos_id numeric, @emi_fed_id numeric, @emi_mun_id numeric, @stock_class_type_id numeric, @stock_pif_id numeric, @share_class_type_id numeric, @share_aif_id numeric, @line_id_tmp numeric ---- для индексного фонда, для расчета эмитента select @rate_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='STOCK_RATE') select @rate_yes_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@rate_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='YES') select @rate_no_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@rate_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='NO') select @emi_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='STOCK_EMITENT_TYPE') select @emi_gos_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='STOCK_GOS_PARTNER') select @emi_fed_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='STOCK_SUB_FED') select @emi_mun_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='STOCK_MUNICIPAL') select @stock_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='STOCK_TYPE') select @stock_pif_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@stock_class_type_id ---@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='PIFPAY') select @share_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='SHARE_RUS_AO') select @share_aif_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@share_class_type_id ---@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='SHARE_AIF') .FOR_DOCS(N,'restricts','CRT',C,'t_items100.id=ts_restrict_types.id and ts_lim_sections.id=@rel_id and ts_restrict_types.calk_type_id in (1,5,6,8) and ts_restrict_types.section_id=ts_lim_sections.id','ts_restrict_types,ts_lim_sections') .TYPE_GET('restricts',@for_id1) select @line_code_eff1=(select ts_restrict_types.lim_code_eff from ts_restrict_types where id=@for_id1) select @acc_num=(select acc_num_eff_rep from t_accounts where id=@acc_id1) if @calk_type_id1=1 begin if @class_value_id1 is null and @next_class_value_id1 is null begin insert into #report(line_id,bal_sum,object_id,object_qty_d,is_show,line_code_eff,pl_min,ocen_sum,nkd_sum) select @for_id1, tp_pif_srez_portfolio.bal_sum, tp_pif_srez_portfolio.stock_id, tp_pif_srez_portfolio.stock_qty*.STOCK_QTY_MULT(tp_pif_srez_portfolio.stock_id), @is_show1, @line_code_eff1, @plus_or_minus1, tp_pif_srez_portfolio.stock_sum, tp_pif_srez_portfolio.coupon_sum from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end else begin if @class_type_id1 is not null begin if @class_type_id1 != @rate_class_type_id begin .GET_NCLASS_ITEMS(#object,@class_type_id1,@class_value_id1,@owner_id,@date_b) end else begin if @class_value_id1=@rate_yes_id and @class_type_id1=@rate_class_type_id insert into #object (id) select stock_id from tp_pif_srez_portfolio, td_stock_emis where tp_pif_srez_portfolio.srez_id = @foundation_id and tp_pif_srez_portfolio.stock_id = td_stock_emis.id ---о and (tp_pif_srez_portfolio.rate_date=@date_b and tp_pif_srez_portfolio.rate_date is not null) ---з and tp_pif_srez_portfolio.is_rate = 1 and .ITEMS_EXISTS(tp_pif_srez_portfolio.id) if @class_value_id1=@rate_no_id and @class_type_id1=@rate_class_type_id insert into #object (id) select stock_id from tp_pif_srez_portfolio, td_stock_emis where tp_pif_srez_portfolio.srez_id = @foundation_id and tp_pif_srez_portfolio.stock_id = td_stock_emis.id --- о and ((tp_pif_srez_portfolio.rate_date != @date_b and tp_pif_srez_portfolio.rate_date is not null) or tp_pif_srez_portfolio.rate_date is null or isnull(tp_pif_srez_portfolio.is_rate,0) = 0)---з and isnull(tp_pif_srez_portfolio.is_rate,0) = 0 and .ITEMS_EXISTS(tp_pif_srez_portfolio.id) end end if @next_class_type_id1 is not null begin .GET_NCLASS_ITEMS(#next_object,@next_class_type_id1,@next_class_value_id1,@owner_id,@date_b) if @logic1=1 delete #object where #object.id not in (select id from #next_object) if @logic1=0 insert into #object(id) select #next_object.id from #next_object where #next_object.id not in (select id from #object) truncate table #next_object end insert into #report(line_id,bal_sum,object_id,object_qty_d,is_show,line_code_eff,pl_min,ocen_sum,nkd_sum) select @for_id1, tp_pif_srez_portfolio.bal_sum, tp_pif_srez_portfolio.stock_id, tp_pif_srez_portfolio.stock_qty*.STOCK_QTY_MULT(tp_pif_srez_portfolio.stock_id), @is_show1, @line_code_eff1, @plus_or_minus1, tp_pif_srez_portfolio.stock_sum, tp_pif_srez_portfolio.coupon_sum from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end end if @calk_type_id1=5 begin if @deb_or_cred1=1 begin insert into #report(line_id,bal_sum,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_balance.saldo_deb,0)), @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_balance.saldo_deb,0)) from tp_pif_srez_balance,t_accounts where tp_pif_srez_balance.srez_id=@foundation_id and tp_pif_srez_balance.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and .ITEMS_EXISTS_BY_TYPE(tp_pif_srez_balance.id,'PIF_SREZ_BALANCE') end if @deb_or_cred1=2 begin insert into #report(line_id,bal_sum,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_balance.saldo_cred,0)), @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_balance.saldo_cred,0)) from tp_pif_srez_balance,t_accounts where tp_pif_srez_balance.srez_id=@foundation_id and tp_pif_srez_balance.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and .ITEMS_EXISTS_BY_TYPE(tp_pif_srez_balance.id,'PIF_SREZ_BALANCE') end end if @calk_type_id1 in (6,8) begin -- в одного эмитента if @lim_code1='EMITENT' begin .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_gos_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_fed_id,@owner_id,@date_b) --.GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_mun_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id not in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end --- IDX.EMITENT Оценочная стоимость ЦБ одного эмитента, по которым расчитывается индекс --------- if @lim_code1='IDX.2' begin if @id_index is not null begin select @for_id1 = ts_restrict_types.id, @line_code_eff1 = ts_restrict_types.lim_code_eff, @is_show1 = ts_restrict_types.is_show, @plus_or_minus1 = ts_restrict_types.plus_or_minus from ts_restrict_types,ts_lim_sections,/*ts_restrict_values,*/.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id -- and ts_restrict_types.id = ts_restrict_values.lim_id -- and ts_restrict_values.organization_id = @owner_id and ts_restrict_types.lim_code_eff = 'STR.IDX.EMITENT.' and ts_restrict_types.is_show=1 .ACTUAL_WHERE(ts_restrict_types.id) /* end if @lim_code1='IDX.EMITENT' begin */ insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio, t_items,t_states,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id= @foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id in (select stock_id from t_indices_values where rate_date = @date_b and index_id = @id_index and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES')) and td_stock_emis.emmitent_id is not null .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end end ---------------------------------------------------------------- /* старый --- IDX.EMITENT Оценочная стоимость ЦБ одного эмитента, по которым расчитывается индекс --------- if @lim_code1='IDX.EMITENT' begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio, t_items,t_states,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id= @foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id in (select stock_id from t_indices_values where rate_date = @date_b and index_id = @id_index and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES')) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end ---------------------------------------------------------------- */ ---- IDX.1 Оценочная стоимость ЦБ по которым расчитывается индекс ---------- if @lim_code1='IDX.1' begin if @id_index is not null begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), null, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio, t_items,t_states,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id= @foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id in (select stock_id from t_indices_values where rate_date = @date_b and index_id = @id_index and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES')) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end end -------------------------------------------------------------------------------- ------CNT.IDX_STOCK наличие бумаг не входящих в индекс (только для индексных фондов) -- if @lim_code1='IDX_STOCK' begin if @id_index is not null begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), null, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio, t_items,t_states,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id= @foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id not in (select stock_id from t_indices_values where rate_date = @date_b and index_id = @id_index and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES')) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end end -------------------------------------------------------------------------------- --- STOCK_NOT_RATES Оценочная стоимость ЦБ, не имеющих признаваемой котировки за исключением паев ОПИФ --- if @lim_code1= 'STOCK_NOT_RATES' begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), null, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio, t_items,t_states,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id= @foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.is_rate = 0 and td_stock_emis.id not in (select item_id from t_nclass_value2item where class_value_id = (select id from t_nclass_values where value_code = 'OPIF')) and tp_pif_srez_portfolio.id = t_items.id and t_items.state_id = t_states.id and t_states.class_id = 0 group by tp_pif_srez_portfolio.is_rate ---td_stock_emis.emmitent_id end --------------------------------------------------------------------------- -- в одну УК ПИФ if @lim_code1='UKPIF' begin .GET_NCLASS_ITEMS(#object,@stock_class_type_id,@stock_pif_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@share_class_type_id,@share_aif_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end -- в одного эмитента (для ограничений по количеству) if @calk_type_id1 = 8 begin .GET_NCLASS_ITEMS(#object,@class_type_id1,@class_value_id1,@owner_id,@date_b) if @next_class_type_id1 is not null begin .GET_NCLASS_ITEMS(#next_object,@next_class_type_id1,@next_class_value_id1,@owner_id,@date_b) if @logic1=1 delete #object where #object.id not in (select id from #next_object) if @logic1=0 insert into #object(id) select #next_object.id from #next_object where #next_object.id not in (select id from #object) truncate table #next_object end insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum,em_qty_d) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)), sum(isnull(td_stock_emis.deploument_qty*isnull(td_stock_emis.qty_mult,1),0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end -- в один выпуск гос.(фед.)бумаг if @lim_code1='GOS' begin .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_gos_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_fed_id,@owner_id,@date_b) -- .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_mun_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.id end -- паи одного ПИФ (акции одного АИФ) if @lim_code1 in ('PIF') begin .GET_NCLASS_ITEMS(#object,@stock_class_type_id,@stock_pif_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@share_class_type_id,@share_aif_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum,object_qty_d,nkd_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)), sum(isnull(tp_pif_srez_portfolio.stock_qty*isnull(td_stock_emis.qty_mult,1),0)), sum(isnull(tp_pif_srez_portfolio.coupon_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.id end end truncate table #next_object truncate table #object .END_FOR_DOCS declare @line_id numeric, @calc_type_id numeric, @line_code_eff varchar(50), @lim_code varchar(50), @pl_min int, @is_show int declare curs cursor for select ts_restrict_types.id, ts_restrict_types.calk_type_id, ts_restrict_types.lim_code_eff, ts_restrict_types.lim_code from ts_restrict_types,ts_lim_sections,.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id and ts_restrict_types.is_show=1 .ACTUAL_WHERE(ts_restrict_types.id) open curs fetch curs into @line_id,@calc_type_id,@line_code_eff,@lim_code while .CURSOR_STATE=0 begin if @calc_type_id = 1 begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end if @calc_type_id=3 begin insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id, sum(isnull(rep.bal_sum*rep.pl_min,0)), @line_id, 1, @line_code_eff, sum(isnull(rep.ocen_sum*rep.pl_min,0)), sum(isnull(rep.nkd_sum*rep.pl_min,0)) from #report rep where rep.line_code_eff like @line_code_eff+"%" and rep.line_code_eff!=@line_code_eff end if @calc_type_id=4 begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,sum(isnull(rep.bal_sum*rep.pl_min,0)),rep.object_id,sum(isnull(rep.object_qty_d*rep.pl_min,0)), @line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum*rep.pl_min,0)),sum(isnull(rep.nkd_sum*rep.pl_min,0)) from #report rep where rep.line_code_eff like @line_code_eff+"%" and rep.line_code_eff!=@line_code_eff group by rep.object_id having (sum(isnull(rep.bal_sum*rep.pl_min,0))>0 or sum(isnull(rep.object_qty_d*rep.pl_min,0))>0) end if @calc_type_id=5 begin insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(rep.bal_sum*rep.pl_min,0)),@line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum*rep.pl_min,0)) from #report rep where rep.line_id=@line_id end if @calc_type_id in (6,8) begin -- Дебиторская задолженность if @lim_code='DEB' begin declare @deb7610 money select @deb7610=isnull((select sum(p.saldo_deb) from tp_pif_srez_balance p where p.srez_id=@foundation_id and p.acc_id=@acc7610_id and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PARTNERS')),0) insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(tp_pif_srez_balance.saldo_deb,0)),@line_id,1,@line_code_eff,sum(isnull(tp_pif_srez_balance.saldo_deb,0))-@deb7610 from tp_pif_srez_balance, t_accounts where srez_id = @foundation_id and tp_pif_srez_balance.acc_id = t_accounts.id and .ITEMS_EXISTS_BY_TYPE(tp_pif_srez_balance.id,'PIF_SREZ_PARTNERS') and (t_accounts.acc_num_eff_rep like '.76.%' or t_accounts.id=@acc551_id) end -- Начисленный купонный доход if @lim_code='NKD' begin declare @bal_nkd money, @ocen_nkd money if .GET_CLASS_VALUE_CODE_FOR_ITEM(@owner_id,'SELECT_UNKD_IN_SCHA_REP','ON')='ON' select @bal_nkd=(select sum(p.saldo_deb) from tp_pif_srez_balance p where p.srez_id=@foundation_id and p.acc_id=@acc766_id and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_BALANCE')) select @ocen_nkd=(select sum(p.coupon_sum) from tp_pif_srez_portfolio p where p.srez_id=@foundation_id and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO')) insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum) select @id,isnull(@bal_nkd,0),@line_id,1,@line_code_eff,isnull(@ocen_nkd,0) end -- один эмитент if @lim_code in ('EMITENT','UKPIF') or @calc_type_id=8 begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum,em_qty_dec) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum,rep.em_qty_d from #report rep where rep.line_id=@line_id end -- один выпуск гос-фед ЦБ if @lim_code in ('GOS','PIF') begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end if @lim_code = 'STOCK_NOT_RATES' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end --- IDX.1 Оценочная стоимость ЦБ по которым расчитывается индекс -------- if @lim_code = 'IDX.1' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end --------------------------------------------------------------------------- --- IDX.EMITENT Оценочная стоимость ЦБ одного эмитента, по которым расчитывается индекс --------- if @lim_code='IDX.2' begin if @id_index is not null begin select @line_id_tmp = ts_restrict_types.id from ts_restrict_types,ts_lim_sections,/*ts_restrict_values,*/.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id -- and ts_restrict_types.id = ts_restrict_values.lim_id -- and ts_restrict_values.organization_id = @owner_id and ts_restrict_types.lim_code_eff = 'STR.IDX.EMITENT.' and ts_restrict_types.is_show=1 .ACTUAL_WHERE(ts_restrict_types.id) insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id_tmp end end ------------------------------------------------------------------------------------------------- /* старый --- IDX.EMITENT Оценочная стоимость ЦБ одного эмитента, по которым расчитывается индекс --------- if @lim_code='IDX.EMITENT' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end ------------------------------------------------------------------------------------------------- */ ------ CNT.IDX_STOCK --------------------------------------------- if @lim_code='IDX_STOCK' begin if @id_index is not null begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum,nkd_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty_d,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum,rep.nkd_sum from #report rep where rep.line_id=@line_id end end --------------------------------------------------------------- end if @calc_type_id = 7 begin -- один банк if @lim_code='BANK' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty_d,line_id,is_show,line_code_eff,ocen_sum) select @id, sum(isnull(rep.bal_sum*rep.pl_min,0)), b.partner_id, null, @line_id, 1, @line_code_eff, sum(isnull(rep.ocen_sum*rep.pl_min,0)) from #report rep, t_bank_accounts a, t_banks b where rep.line_code_eff like @line_code_eff+"%" and rep.line_code_eff!=@line_code_eff and rep.object_id=a.id and a.bank_id=b.id group by b.partner_id end end fetch curs into @line_id,@calc_type_id,@line_code_eff,@lim_code end close curs .DEALLOCATE curs insert into ts_investment_report(id,line_id,is_show) select @id,ts_restrict_types.id,1 from ts_restrict_types,ts_lim_sections,.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id and ts_restrict_types.is_show=1 and ts_restrict_types.id not in (select ass.line_id from ts_investment_report ass where ass.id=@id) .ACTUAL_WHERE(ts_restrict_types.id) update ts_investment_report set rel_id=@rel_id, line_code=ts_restrict_types.lim_code, line_name=ts_restrict_types.lim_name, parent_id=ts_restrict_types.parent_id, srez_id=@foundation_id, line_code_eff=ts_restrict_types.lim_code_eff from ts_restrict_types where ts_investment_report.id=@id and ts_restrict_types.id=ts_investment_report.line_id update ts_investment_report set min_lim_value=ts_restrict_values.min_lim_value, max_lim_value=ts_restrict_values.max_lim_value from ts_restrict_values where ts_investment_report.id=@id and ts_restrict_values.lim_id=ts_investment_report.line_id and ts_restrict_values.organization_id=@owner_id declare @sum money select @sum=(select activ_sum from tp_pif_srez where tp_pif_srez.id=@foundation_id) if isnull(@sum,0)<>0 update ts_investment_report set -- fact_percent=round(convert(money,(ocen_sum*100.0/@sum)),2) fact_percent=convert(money, round(convert(double precision,(convert(double precision,ocen_sum)/convert(double precision,@sum))),7)*100) where id=@id -- Необрабатываемые пункты -- PERIOD_2/3, TRADES_NOT_RATES delete ts_investment_report from ts_restrict_types where ts_investment_report.id=@id and ts_investment_report.line_id=ts_restrict_types.id and ts_restrict_types.lim_code in ('PERIOD_2/3','TRADES_NOT_RATES') -- тип 8 - Отношение остатка к общему кол-ву размещ.ЦБ эмитента update ts_investment_report set fact_percent=null from ts_restrict_types where ts_investment_report.id=@id and ts_investment_report.line_id=ts_restrict_types.id and ts_restrict_types.calk_type_id=8 update ts_investment_report set fact_percent=convert(money, round(convert(double precision,(convert(double precision,ts_investment_report.object_qty_d) / convert(double precision,ts_investment_report.em_qty_dec))),7)*100) from ts_restrict_types where ts_investment_report.id=@id and ts_investment_report.line_id=ts_restrict_types.id and ts_restrict_types.calk_type_id=8 and isnull(ts_investment_report.em_qty_dec,0)<>0 drop table #object drop table #next_object drop table #report /* ----- REPORT_INVESTMENT_STRUCT_DIV ----- declare @rep_rec_id numeric, @rep_rec_sid varchar(30) select @rep_rec_id=td_depo_docs.id from td_depo_docs where td_depo_docs.foundation_id=@foundation_id and .ITEMS_EXISTS_BY_TYPE(td_depo_docs.id,'REPORT_INVESTMENT_STRUCT_DIV') select @rep_rec_sid=convert(varchar(30),@rep_rec_id) if @rep_rec_id is null begin declare @in_no_rep varchar(50) .DEPO_GEN_NO(@in_no_rep,2) .TYPE_INIT('REPORT_INVESTMENT_STRUCT_DIV',7) .TYPE_ASSIGN(foundation_id,@foundation_id,7) .TYPE_ASSIGN(owner_id,@owner_id,7) .TYPE_ASSIGN(in_date,(.OPERDAY),7) .TYPE_ASSIGN(in_no,@in_no_rep,7) .TYPE_ASSIGN(depo_doc_type,2,7) .TYPE_INSERT('REPORT_INVESTMENT_STRUCT_DIV','CRT',7) select @rep_rec_sid=convert(varchar(30),@id7_1) end ------- */ /*---- IDX.2. Разница м/д долей ЦБ одного эмитента в суммарной капитализации ЦБ, по которым расчитывается индекс и долей оценочной стоимости этих бумаг в активах фонда */ if @id_index is not null begin delete ts_investment_report from ts_restrict_types where ts_investment_report.id=@id and ts_investment_report.line_id=ts_restrict_types.id and ts_restrict_types.lim_code in ('IDX.2') declare @emit_id numeric, @lim_raznici_max numeric, @lim_raznici_min numeric, @raznica numeric(18,4), @dol_stock_emit numeric(18,4), @line_name varchar(255), @parent_id numeric select @line_id = null, @calc_type_id = null, @line_code_eff = null, @lim_code = null, @is_show = null, @pl_min = null, @lim_raznici_max = null, @lim_raznici_min = null, @line_name = null, @parent_id = null select @line_id = ts_restrict_types.id, @calc_type_id = ts_restrict_types.calk_type_id, @line_code_eff = ts_restrict_types.lim_code_eff, @lim_code = ts_restrict_types.lim_code, @is_show = ts_restrict_types.is_show, @pl_min = ts_restrict_types.plus_or_minus, @lim_raznici_max = ts_restrict_values.max_lim_value, @lim_raznici_min = ts_restrict_values.min_lim_value, @line_name = ts_restrict_types.lim_name, @parent_id = ts_restrict_types.parent_id from ts_restrict_types,ts_lim_sections,ts_restrict_values,.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id and ts_restrict_types.id = ts_restrict_values.lim_id and ts_restrict_values.organization_id = @owner_id and ts_restrict_types.lim_code_eff = 'STR.IDX.2.' and ts_restrict_types.is_show=1 .ACTUAL_WHERE(ts_restrict_types.id) ---.EXIT_MESSAGE_PARM('fgfg111',@line_id) if @line_id is not null begin declare #emitent cursor for select object_id from ts_investment_report where line_code_eff = 'STR.IDX.EMITENT.' and ts_investment_report.id=@id /* and object_id is not null group by object_id */ open #emitent fetch #emitent into @emit_id while @@fetch_status=0 begin select @dol_stock_emit = isnull(sum(index_proc),0) from t_indices_values where rate_date = @date_b and index_id = @id_index and stock_id in (select id from td_stock_emis where emmitent_id = @emit_id ) and .ITEMS_EXISTS_BY_TYPE(t_indices_values.id,'T_INDICES_VALUES') select @raznica =abs(@dol_stock_emit - (select isnull(fact_percent,0) from ts_investment_report where line_code_eff = 'STR.IDX.EMITENT.' and ts_investment_report.id=@id and object_id = @emit_id)) ---.EXIT_MESSAGE_PARM('fgfg',@line_id) insert into ts_investment_report( id , line_id , bal_sum , fact_percent , is_show , parent_id , object_id , object_qty_d , line_code_eff , ocen_sum , nkd_sum, min_lim_value, max_lim_value, rel_id, line_code, line_name, srez_id ) select @id, @line_id, bal_sum, @raznica, @is_show, @parent_id, object_id, object_qty_d, @line_code_eff, ocen_sum , nkd_sum, @lim_raznici_min, @lim_raznici_max, @rel_id, @lim_code, @line_name, @foundation_id from ts_investment_report where line_code_eff = 'STR.IDX.EMITENT.' and ts_investment_report.id=@id and object_id = @emit_id fetch #emitent into @emit_id end close #emitent .DEALLOCATE #emitent end end -------------------------------------------------------------------------------------------------------------------- .TRANSACTION_RESTORE .UPDATE_STATE --.EXEC_ACTION(@rep_rec_sid,'REPORT_INVESTMENT_STRUCT_DIV','RECALC_INVESTMENT_DIV') .EXEC_ACTION(@sid,'REPORT_FOR_INVESTMENT_ACT_PIF','FIND_VIOLATIONS_PIF') commit tran $ENDTEXT(13505509) $ENDACTION $ACTION(OLD_INVESTMENT_ACT_PIF_RECALC) name=Пересчет отчета (старый) class=9 form=null target_state=CRT procedure=ap_old_investment_pif_recalc patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(13505509) 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 .ACTION_HISTORY /* -- таблица для отчета CREATE TABLE dbo.ts_investment_report ( id numeric(18, 0) NOT NULL , srez_id numeric(18, 0) NULL , line_id numeric(18, 0) NULL , line_name varchar (255) NULL , line_code varchar (50) NULL , stock_type_id numeric(18, 0) NULL , stock_rates_id numeric(18, 0) NULL , emi_id numeric(18, 0) NULL , emi_name varchar (255) NULL , bal_sum money NULL , plan_percent money NULL , fact_percent money NULL , is_show int NULL , rel_id numeric(18, 0) NULL , parent_id numeric(18, 0) NULL , object_id numeric(18, 0) NULL , object_name varchar (255) NULL , object_qty money NULL , line_code_eff varchar (50) NULL , min_lim_value money NULL , max_lim_value money NULL , ocen_sum money NULL ) */ delete ts_investment_report where id=@id declare @plan_id numeric select @plan_id = .ARG_PLAN('PIF_ACCOUNT') if @plan_id is null .EXIT_MESSAGE('Не найден план PIF_ACCOUNT') declare @acc_dep_id numeric declare @acc_stock_id numeric select @acc_dep_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.2') select @acc_stock_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1') declare @rel_id numeric select @rel_id=ts_lim_sections.id from ts_restrict_types,ts_lim_sections,t_types where ts_restrict_types.acc_id in (select t_accounts.id from t_accounts where t_accounts.plan_id=@plan_id) and ts_restrict_types.section_id=ts_lim_sections.id and upper(ts_lim_sections.lim_sect_code)='PIF' and upper(t_types.type_code)='PIF_FUND' and ts_lim_sections.partner_type_id=t_types.id and .ITEMS_EXISTS(ts_lim_sections.id) if @rel_id is null .EXIT_MESSAGE('У фонда нет ограничений') .TRANSACTION_SAVE create table #report( line_id numeric, bal_sum money null, object_id numeric null, uk_id numeric null, object_qty money null, is_show int, line_code_eff varchar(50), pl_min int null, ocen_sum money null) create table #object(id numeric) create table #next_object(id numeric) declare @line_code_eff1 varchar(100) declare @emi_class_type_id numeric, @rate_class_type_id numeric, @rate_yes_id numeric, @rate_no_id numeric, @emi_gos_id numeric, @emi_fed_id numeric select @rate_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='STOCK_RATE') select @rate_yes_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@rate_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='YES') select @rate_no_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@rate_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='NO') select @emi_class_type_id=(select t_nclass_types.id from t_nclass_types where .STR_FOR_IF(t_nclass_types.class_code)='STOCK_EMITENT_TYPE') select @emi_gos_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='STOCK_GOS_PARTNER') select @emi_fed_id=(select t_nclass_values.id from t_nclass_values where t_nclass_values.class_type_id=@emi_class_type_id and .STR_FOR_IF(t_nclass_values.value_code)='STOCK_SUB_FED') .FOR_DOCS(N,'restricts','CRT',C,'t_items100.id=ts_restrict_types.id and ts_lim_sections.id=@rel_id and ts_restrict_types.calk_type_id in (1,5,6) and ts_restrict_types.section_id=ts_lim_sections.id','ts_restrict_types,ts_lim_sections') .TYPE_GET('restricts',@for_id1) select @line_code_eff1=(select ts_restrict_types.lim_code_eff from ts_restrict_types where id=@for_id1) declare @acc_num varchar(100) select @acc_num=(select acc_num_eff_rep from t_accounts where id=@acc_id1) if @calk_type_id1=1 begin if @class_value_id1 is null and @next_class_value_id1 is null begin insert into #report(line_id,bal_sum,object_id,object_qty,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, tp_pif_srez_portfolio.bal_sum, tp_pif_srez_portfolio.stock_id, tp_pif_srez_portfolio.stock_qty, @is_show1, @line_code_eff1, @plus_or_minus1, tp_pif_srez_portfolio.stock_sum from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end else begin if @class_type_id1 is not null begin if @class_type_id1 != @rate_class_type_id begin .GET_NCLASS_ITEMS(#object,@class_type_id1,@class_value_id1,@owner_id,@date_b) end else begin if @class_value_id1=@rate_yes_id and @class_type_id1=@rate_class_type_id insert into #object (id) select stock_id from tp_pif_srez_portfolio, td_stock_emis where tp_pif_srez_portfolio.srez_id = @foundation_id and tp_pif_srez_portfolio.stock_id = td_stock_emis.id and (tp_pif_srez_portfolio.rate_date=@date_b and tp_pif_srez_portfolio.rate_date is not null) and tp_pif_srez_portfolio.is_rate = 1 and .ITEMS_EXISTS(tp_pif_srez_portfolio.id) if @class_value_id1=@rate_no_id and @class_type_id1=@rate_class_type_id insert into #object (id) select stock_id from tp_pif_srez_portfolio, td_stock_emis where tp_pif_srez_portfolio.srez_id = @foundation_id and tp_pif_srez_portfolio.stock_id = td_stock_emis.id and ((tp_pif_srez_portfolio.rate_date != @date_b and tp_pif_srez_portfolio.rate_date is not null) or tp_pif_srez_portfolio.rate_date is null or isnull(tp_pif_srez_portfolio.is_rate,0) = 0) and .ITEMS_EXISTS(tp_pif_srez_portfolio.id) end end if @next_class_type_id1 is not null begin .GET_NCLASS_ITEMS(#next_object,@next_class_type_id1,@next_class_value_id1,@owner_id,@date_b) if @logic1=1 delete #object where #object.id not in (select id from #next_object) if @logic1=0 insert into #object(id) select #next_object.id from #next_object where #next_object.id not in (select id from #object) truncate table #next_object end insert into #report(line_id,bal_sum,object_id,object_qty,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, tp_pif_srez_portfolio.bal_sum, tp_pif_srez_portfolio.stock_id, tp_pif_srez_portfolio.stock_qty, @is_show1, @line_code_eff1, @plus_or_minus1, tp_pif_srez_portfolio.stock_sum from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and tp_pif_srez_portfolio.stock_id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) end end if @calk_type_id1=5 begin if @deb_or_cred1=1 begin insert into #report(line_id,bal_sum,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_balance.saldo_deb,0)), @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_balance.saldo_deb,0)) from tp_pif_srez_balance,t_accounts where tp_pif_srez_balance.srez_id=@foundation_id and tp_pif_srez_balance.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and .ITEMS_EXISTS_BY_TYPE(tp_pif_srez_balance.id,'PIF_SREZ_BALANCE') end if @deb_or_cred1=2 begin insert into #report(line_id,bal_sum,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_balance.saldo_cred,0)), @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_balance.saldo_cred,0)) from tp_pif_srez_balance,t_accounts where tp_pif_srez_balance.srez_id=@foundation_id and tp_pif_srez_balance.acc_id=t_accounts.id and t_accounts.acc_num_eff_rep like @acc_num+'%' and .ITEMS_EXISTS_BY_TYPE(tp_pif_srez_balance.id,'PIF_SREZ_BALANCE') end end if @calk_type_id1=6 begin -- в одного эмитента if @line_code_eff1 like 'EMITENT.%' begin .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_gos_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_fed_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.emmitent_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id not in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.emmitent_id end -- в один выпуск гос.(фед.)бумаг if @line_code_eff1 like 'GOS.%' begin .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_gos_id,@owner_id,@date_b) .GET_NCLASS_ITEMS(#object,@emi_class_type_id,@emi_fed_id,@owner_id,@date_b) insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_stock_emis.id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and t_accounts.acc_num_eff_rep like @acc_num+'%' and td_stock_emis.id in (select id from #object) .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_stock_emis.id end -- в один банк if @line_code_eff1 like 'BANK.%' begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), t_banks.partner_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,t_banks,t_bank_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_bank_accounts.id=tp_pif_srez_portfolio.stock_id and t_banks.id=t_bank_accounts.bank_id and t_accounts.acc_num_eff_rep like @acc_num+'%' .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by t_banks.partner_id end -- в одну организацию - банк if @line_code_eff1 like 'ORGBANK.%' begin insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), t_banks.partner_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,t_banks,t_bank_accounts where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and t_bank_accounts.id=tp_pif_srez_portfolio.stock_id and t_banks.id=t_bank_accounts.bank_id and t_accounts.id = @acc_dep_id .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by t_banks.partner_id insert into #report(line_id,bal_sum,object_id,is_show,line_code_eff,pl_min,ocen_sum) select @for_id1, sum(isnull(tp_pif_srez_portfolio.bal_sum,0)), td_emitent_info.partner_id, @is_show1, @line_code_eff1, @plus_or_minus1, sum(isnull(tp_pif_srez_portfolio.stock_sum,0)) from tp_pif_srez_portfolio,.ACTUAL_TABLES,t_accounts,td_stock_emis,td_emitent_info where tp_pif_srez_portfolio.srez_id=@foundation_id and tp_pif_srez_portfolio.acc_id=t_accounts.id and td_stock_emis.id=tp_pif_srez_portfolio.stock_id and td_emitent_info.id=td_stock_emis.emmitent_id and t_accounts.id = @acc_stock_id .ACTUAL_WHERE(tp_pif_srez_portfolio.id) group by td_emitent_info.partner_id end end truncate table #next_object truncate table #object .END_FOR_DOCS declare @line_id numeric, @calc_type_id numeric, @line_code_eff varchar(50), @pl_min int, @is_show int declare curs cursor for select ts_restrict_types.id, ts_restrict_types.calk_type_id, ts_restrict_types.lim_code_eff from ts_restrict_types,ts_lim_sections,.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id and ts_restrict_types.is_show=1 .ACTUAL_WHERE(ts_restrict_types.id) open curs fetch curs into @line_id,@calc_type_id,@line_code_eff while .CURSOR_STATE=0 begin if @calc_type_id = 1 begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum from #report rep where rep.line_id=@line_id end if @calc_type_id=3 begin insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(rep.bal_sum*rep.pl_min,0)),@line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum*rep.pl_min,0)) from #report rep where rep.line_code_eff like @line_code_eff+"%" and rep.line_code_eff!=@line_code_eff end if @calc_type_id=4 begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(rep.bal_sum*rep.pl_min,0)),rep.object_id,sum(isnull(rep.object_qty*rep.pl_min,0)), @line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum*rep.pl_min,0)) from #report rep where rep.line_code_eff like @line_code_eff+"%" and rep.line_code_eff!=@line_code_eff group by rep.object_id end if @calc_type_id=5 begin insert into ts_investment_report(id,bal_sum,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(rep.bal_sum*rep.pl_min,0)),@line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum*rep.pl_min,0)) from #report rep where rep.line_id=@line_id end if @calc_type_id = 6 begin -- один эмитент if @line_code_eff like 'EMITENT.%' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum from #report rep where rep.line_id=@line_id end -- один выпуск гос-фед ЦБ if @line_code_eff like 'GOS.%' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum from #report rep where rep.line_id=@line_id end -- один банк if @line_code_eff like 'BANK.%' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,rep.bal_sum,rep.object_id,rep.object_qty,rep.line_id,rep.is_show,@line_code_eff,rep.ocen_sum from #report rep where rep.line_id=@line_id end if @line_code_eff like 'ORGBANK.%' begin insert into ts_investment_report(id,bal_sum,object_id,object_qty,line_id,is_show,line_code_eff,ocen_sum) select @id,sum(isnull(rep.bal_sum,0)),rep.object_id,sum(isnull(rep.object_qty,0)), @line_id,1,@line_code_eff,sum(isnull(rep.ocen_sum,0)) from #report rep where rep.line_code_eff like 'ORGBANK.%' and exists (select 1 from t_banks where partner_id=rep.object_id) group by rep.object_id end end /* if @calc_type_id = 7 begin continue end */ fetch curs into @line_id,@calc_type_id,@line_code_eff end close curs .DEALLOCATE curs insert into ts_investment_report(id,line_id,is_show) select @id,ts_restrict_types.id,1 from ts_restrict_types,ts_lim_sections,.ACTUAL_TABLES where ts_lim_sections.id=@rel_id and ts_restrict_types.section_id=ts_lim_sections.id and ts_restrict_types.is_show=1 and ts_restrict_types.id not in (select ass.line_id from ts_investment_report ass where ass.id=@id) .ACTUAL_WHERE(ts_restrict_types.id) update ts_investment_report set rel_id=@rel_id, line_code=ts_restrict_types.lim_code, line_name=ts_restrict_types.lim_name, parent_id=ts_restrict_types.parent_id, srez_id=@foundation_id, line_code_eff=ts_restrict_types.lim_code_eff from ts_restrict_types where ts_investment_report.id=@id and ts_restrict_types.id=ts_investment_report.line_id update ts_investment_report set min_lim_value=ts_restrict_values.min_lim_value, max_lim_value=ts_restrict_values.max_lim_value from ts_restrict_values where ts_investment_report.id=@id and ts_restrict_values.lim_id=ts_investment_report.line_id and ts_restrict_values.organization_id=@owner_id declare @sum money select @sum=(select activ_sum from tp_pif_srez where tp_pif_srez.id=@foundation_id) update ts_investment_report set fact_percent=round(convert(money,(ocen_sum*100.0/@sum)),2) where id=@id drop table #object drop table #next_object drop table #report declare @rep_rec_id numeric, @rep_rec_sid varchar(30) select @rep_rec_id=(select td_depo_docs.id from td_depo_docs where td_depo_docs.foundation_id=@foundation_id and .ITEMS_EXISTS_BY_TYPE(td_depo_docs.id,'REPORT_INVESTMENT_STRUCT_DIV')) select @rep_rec_sid=convert(varchar(30),isnull(@rep_rec_id,0)) if @rep_rec_id=null begin declare @in_no_rep varchar(50) .DEPO_GEN_NO(@in_no_rep,2) .TYPE_INIT('REPORT_INVESTMENT_STRUCT_DIV',7) .TYPE_ASSIGN(foundation_id,@foundation_id,7) .TYPE_ASSIGN(owner_id,@owner_id,7) .TYPE_ASSIGN(in_date,(.OPERDAY),7) .TYPE_ASSIGN(in_no,@in_no_rep,7) .TYPE_ASSIGN(depo_doc_type,2,7) .TYPE_INSERT('REPORT_INVESTMENT_STRUCT_DIV','CRT',7) select @rep_rec_sid=convert(varchar(30),@id7_1) end .TRANSACTION_RESTORE .UPDATE_STATE .EXEC_ACTION(@rep_rec_sid,'REPORT_INVESTMENT_STRUCT_DIV','RECALC_INVESTMENT_DIV') commit tran $ENDTEXT(13505509) $ENDACTION $STATE2ACTION(CRT.ACTION_UPDATE) state=CRT action=ACTION_UPDATE is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.FIND_VIOLATIONS_PIF) state=CRT action=FIND_VIOLATIONS_PIF is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.FORM1) state=CRT action=FORM1 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.FORM2) state=CRT action=FORM2 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.FORM3) state=CRT action=FORM3 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.FORM4) state=CRT action=FORM4 is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.INVESTMENT_ACT_PIF_RECALC) state=CRT action=INVESTMENT_ACT_PIF_RECALC is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.OLD_D) state=CRT action=OLD_D is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.OLD_H) state=CRT action=OLD_H is_available=1 $ENDSTATE2ACTION $STATE2ACTION(CRT.OLD_INVESTMENT_ACT_PIF_RECALC) state=CRT action=OLD_INVESTMENT_ACT_PIF_RECALC is_available=0 $ENDSTATE2ACTION $STATE2PROP(CRT.CORRESPONDENT_ID) state=CRT prop=CORRESPONDENT_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.DATE_B) state=CRT prop=DATE_B enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.DEPO_DOC_TYPE) state=CRT prop=DEPO_DOC_TYPE enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.DOCUMENT_FORM_ID) state=CRT prop=DOCUMENT_FORM_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.DOC_COMMENT) state=CRT prop=DOC_COMMENT enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.FOLDER_ID) state=CRT prop=FOLDER_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.FOUNDATION_ID) state=CRT prop=FOUNDATION_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.INITIATOR_ID) state=CRT prop=INITIATOR_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.IN_DATE) state=CRT prop=IN_DATE enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.IN_NO) state=CRT prop=IN_NO enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.OUT_DATE) state=CRT prop=OUT_DATE enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.OUT_NO) state=CRT prop=OUT_NO enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.OWNER_ID) state=CRT prop=OWNER_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.PAYER_ID) state=CRT prop=PAYER_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.PAY_SUM) state=CRT prop=PAY_SUM enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.QUERY_TYPE) state=CRT prop=QUERY_TYPE enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(CRT.SUM_CUR_ID) state=CRT prop=SUM_CUR_ID enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.WHO_GET) state=CRT prop=WHO_GET enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $STATE2PROP(CRT.WHO_PUT) state=CRT prop=WHO_PUT enable=0 mandatory=0 visible=0 $ENDSTATE2PROP $ENDDOC