# Date: 06/05/06 # Version: 105 $DOC(SD_DEPOSIT_OVERVALUE) name=Переоценка депозитов class=1 product=ALD_FUND name_formula=.TYPE_NAME(@type_id) + ' № ' + @in_no+' от '+.DATE_TO_STR(@in_date) remark_formula=null $STATE(BEG) name=Начальный class=1 $ENDSTATE $STATE(DEL) name=Удален class=2 $ENDSTATE $STATE(ED) name=Редактируется class=0 $ENDSTATE $STATE(OP_END) name=Подтвержден class=0 $ENDSTATE $PROP(IN_DATE) name=Дата документа field_name=in_date table_name=ts_sd_deposit_overvalue field_type=datetime field_length=8 field_scale=3 field_prec=23 page=1 order=1 init_class=1 init_formula=.OPERDAY unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(IN_NO) name=Номер документа field_name=in_no table_name=ts_sd_deposit_overvalue field_type=varchar field_length=50 field_scale=null field_prec=50 page=1 order=2 init_class=0 init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(INITIATOR_ID) name=Управляющий (для НПФ) field_name=initiator_id table_name=ts_sd_deposit_overvalue field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=3 init_class=0 init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OWNER_ID) name=Владелец field_name=owner_id table_name=ts_sd_deposit_overvalue field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=4 init_class=1 init_formula=.ORGANIZATION unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(COMMENT) name=Комментарий field_name=comment table_name=ts_sd_deposit_overvalue field_type=varchar field_length=255 field_scale=null field_prec=255 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(DAYS_QTY) name=Кол-во дней для расчета field_name=days_qty table_name=ts_sd_deposit_overvalue field_type=int field_length=4 field_scale=0 field_prec=10 page=1 order=6 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(REL1) class=0 type=FUND_GROUP form=null prop=OWNER_ID idname=null order=2 $ENDRELATION $RELATION(REL2) class=1 type=T_OPERATIONS form=ITEM_OPERS_LIST prop=null idname=item_id_s order=130 $ENDRELATION $RELATION(REL3) class=0 type=PARTNERS form=null prop=INITIATOR_ID idname=null order=1 $ENDRELATION $RELATION(REL4) class=1 type=ACTIONS_HISTORY form=UO_ACTIONS_HISTORY_LIST prop=null idname=null order=120 $ENDRELATION $FORM(FORM1) name=Просмотр class=5 filter=null target_state= uo=uo_edit dw=d_deposit_overvalue_edit procedure=ap_40000000001385764 patterns=GENERIC_VIEW_ITEM user_define=1 isvisible=0 istab=1 posx=0 posy=0 width=3095 height=1056 proc_text=$TEXT(12495329) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_VIEW_ITEM if @id = -1 begin begin tran .GET_PROPS .CHECK_INSERT .INIT_VARS(B) .INSERT_ITEMS .INSERT commit tran end .INSERT_INFO4OPER(@id) .VIEW_ITEM .END $ENDTEXT(12495329) $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=null alias=initiator_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=null alias=initiator_short_name $ENDACTION2RELATION $ACTION2RELATION(REL1.CTRL_COMP_ID) relation=REL1 where=null prop=CTRL_COMP_ID order=2 alias=ctrl_comp_id_s $ENDACTION2RELATION $ACTION2RELATION(REL1.PARTNER_CODE) relation=REL1 where=null prop=PARTNER_CODE order=4 alias=fond_code $ENDACTION2RELATION $ACTION2RELATION(REL1.PARTNER_SHORT_NAME) relation=REL1 where=null prop=PARTNER_SHORT_NAME order=3 alias=fond_name $ENDACTION2RELATION $BIND(BIND1) type=SD_DEPOSIT_OVERVALUE_LIST action=FORM2 class=1 order=1 idname=doc_id_s $ENDBIND $BIND(BIND2) type=SD_INFO4OPER action=FORM_ED class=1 order=10 idname=sid $ENDBIND $ENDFORM $FORM(FORM2) name=Список class=4 filter=FORM3 target_state= uo=uo_list dw=d_deposit_overvalue_list procedure=ap_40000000001385768 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=2277 height=1012 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as declare @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric begin set nocount on select @action_id = %ACTION_ID% .CHECK_USER .CHECK_VIEW .ARGCONVERT_FILTER .VIEW_LIST end $ENDTEXT(12495331) $ACTION2RELATION(REL1.PARTNER_CODE) relation=REL1 where=null prop=PARTNER_CODE order=null alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=null alias=uk_code $ENDACTION2RELATION $ENDFORM $FORM(FORM3) name=Фильтр class=6 filter=null target_state= uo=uo_filter dw=d_deposit_overvalue_filter procedure=ap_40000000001385771 patterns=null user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=1920 height=516 proc_text=$TEXT(12495331) create procedure dbo.%PROC% as begin declare @in_date_db datetime declare @in_date_de datetime declare @owner_id numeric declare @owner_name varchar(255) select @in_date_db = .OPERDAY select @in_date_de = @in_date_db select @owner_id=.ORGANIZATION select @owner_name=(select partner_short_name from t_partners where id=@owner_id) select convert(datetime, @in_date_db) as in_date_db, convert(datetime, @in_date_de) as in_date_de, convert(varchar(255), null) as in_no, convert(varchar(30), @owner_id) as owner_id_s, @owner_name as partner_name end $ENDTEXT(12495331) $FILTER(IN_DATE) type=2 label=Дата документа prop=IN_DATE arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(IN_NO) type=3 label=Номер документа prop=IN_NO arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(OWNER_ID) type=1 label=Фонд prop=OWNER_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $ACTION(ACTION1) name=Добавить class=1 form=FORM1 target_state=ED procedure=ap_40000000001385775 patterns=GENERIC_INSERT user_define=1 isvisible=0 istab=1 posx=0 posy=0 width=2272 height=1204 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .ARGCONVERT .INIT_VARS .TEST_UNIQUE begin tran .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran select sid = convert(varchar, @id) .END $ENDTEXT(12495331) $BIND(BIND1) type=SD_INFO4OPER action=ACTION_UPDATE class=1 order=10 idname=sid $ENDBIND $ENDACTION $ACTION(ACTION2) name=Сформировать список депозитов class=9 form=null target_state=null procedure=ap_40000000001385776 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .GET_PROPS .ID_VAR(@plan_id) .ID_VAR(@pp_id) .ID_VAR(@acc_id) .ID_VAR(@subc_type_id) .ID_VAR(@uk_subc_type_id) .ID_VAR(@bank_acc_id) .NAME_VAR(@fund_type) declare @op_date datetime, @bal_sum money, @list_id_s varchar(30) select @fund_type=upper(.ITEM_TYPE_CODE(@owner_id)) if @fund_type='PIF_FUND' select @plan_id=.ARG_PLAN('PIF_ACCOUNT') if @fund_type='NPF_FUND' select @plan_id=.ARG_PLAN('NPF_ACCOUNT') select @acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.2') select @subc_type_id=.SUBCTYPE_FROM_CODE('bank_account') select @uk_subc_type_id=.SUBCTYPE_FROM_CODE('asset_manager') select @op_date=dateadd(dd,1,(.DATE_NORM(@in_date,B))) .TRANSACTION_SAVE create table #bank_accounts (id numeric, bal_sum money) select @pp_id=pp.id from t_plans2partner pp where pp.partner_id=@owner_id and pp.plan_id=@plan_id and .ITEMS_EXISTS(pp.id) if @fund_type='PIF_FUND' insert into #bank_accounts (id, bal_sum) select os.item_id, sum(isnull(ol.sum_bal*ol.deb_or_cred,0)) from t_oper_subconto os, t_oper_list ol, t_operations o where o.partner2plan_id=@pp_id and ol.head_id=o.id and ol.acc_id=@acc_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and o.op_date<@op_date group by os.item_id having sum(isnull(ol.sum_bal*ol.deb_or_cred,0))>0 if @fund_type='NPF_FUND' insert into #bank_accounts (id, bal_sum) select os.item_id, sum(isnull(ol.sum_bal*ol.deb_or_cred,0)) from t_oper_subconto os, t_oper_list ol, t_operations o where o.partner2plan_id=@pp_id and ol.head_id=o.id and ol.acc_id=@acc_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and o.op_date<@op_date and exists(select 1 from t_oper_subconto os2 where os2.id=ol.id and os2.subconto_type_id=@uk_subc_type_id and os2.item_id=isnull(@initiator_id,@owner_id)) group by os.item_id having sum(isnull(ol.sum_bal*ol.deb_or_cred,0))>0 begin tran delete t_items from ts_sd_deposit_overvalue_list t where t_items.id=t.id and t.doc_id=@id delete ts_sd_deposit_overvalue_list where doc_id = @id declare bank_accounts cursor for select id, bal_sum from #bank_accounts for read only open bank_accounts fetch bank_accounts into @bank_acc_id, @bal_sum while .CURSOR_STATE=0 begin .TYPE_INIT('SD_DEPOSIT_OVERVALUE_LIST',1) .TYPE_ASSIGN(doc_id,@id,1) .TYPE_ASSIGN(bank_acc_id,@bank_acc_id,1) .TYPE_ASSIGN(bal_sum,@bal_sum,1) .TYPE_INSERT('SD_DEPOSIT_OVERVALUE_LIST','CREATE',1) select @list_id_s=convert(varchar(30),.TYPE_FIELD(id,1)) .EXEC_ACTION(@list_id_s,'SD_DEPOSIT_OVERVALUE_LIST','GET_CALC_SUM') fetch bank_accounts into @bank_acc_id, @bal_sum end close bank_accounts .DEALLOCATE bank_accounts commit tran drop table #bank_accounts .TRANSACTION_RESTORE .END $ENDTEXT(12495331) $ENDACTION $ACTION(ACTION3) name=Изменить class=2 form=FORM1 target_state=null procedure=ap_40000000001385777 patterns=GENERIC_UPDATE user_define=0 isvisible=0 istab=1 posx=0 posy=0 width=3118 height=1152 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .ARGCONVERT .TEST_UNIQUE begin tran .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran .END $ENDTEXT(12495331) $BIND(BIND1) type=SD_DEPOSIT_OVERVALUE_LIST action=FORM2 class=1 order=1 idname=doc_id_s $ENDBIND $BIND(BIND2) type=SD_INFO4OPER action=ACTION_UPDATE class=1 order=10 idname=sid $ENDBIND $ENDACTION $ACTION(ACTION4) name=На редактирование class=9 form=null target_state=ED procedure=ap_40000000001385779 patterns=GENERIC_MOVE user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE begin tran .UPDATE_STATE .ACTION_HISTORY .OPERS_ROLL .SD_ASSET_OPER_DEL(@id) commit tran .END $ENDTEXT(12495331) $ENDACTION $ACTION(ACTION5) name=Удалить class=9 form=null target_state=DEL procedure=ap_40000000001385780 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE begin tran .UPDATE_STATE .ACTION_HISTORY delete t_items from ts_sd_deposit_overvalue_list t where t_items.id=t.id and t.doc_id=@id delete ts_sd_deposit_overvalue_list where doc_id = @id commit tran .END $ENDTEXT(12495331) $ENDACTION $ACTION(ACTION6) name=Подтвердить class=9 form=null target_state=OP_END procedure=ap_40000000001385781 patterns=GENERIC_MOVE user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(12495331) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE .GET_PROPS .ID_VAR(@plan_id) .ID_VAR(@pp_id) .ID_VAR(@acc_id) .ID_VAR(@subc_type_id) .ID_VAR(@uk_subc_type_id) .ID_VAR(@bank_acc_id) .NAME_VAR(@fund_type) declare @check_date datetime, @calc_sum money, @asset_manager_id numeric, @partner_id numeric, @deb_acc_id numeric, @cred_acc_id numeric, @doc_name varchar(255), @ms varchar(255) if exists(select 1 from ts_sd_deposit_overvalue_list where doc_id=@id and isnull(calc_sum,0)<=0) .EXIT_MESSAGE('Не заданы суммы для проводок') select @fund_type=upper(.ITEM_TYPE_CODE(@owner_id)) select @asset_manager_id=isnull(@initiator_id,@owner_id) if @fund_type='PIF_FUND' select @plan_id=.ARG_PLAN('PIF_ACCOUNT') if @fund_type='NPF_FUND' select @plan_id=.ARG_PLAN('NPF_ACCOUNT') select @pp_id=pp.id from t_plans2partner pp where pp.partner_id=@owner_id and pp.plan_id=@plan_id and .ITEMS_EXISTS(pp.id) select @acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'58.2') select @cred_acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'91.3') if @fund_type='NPF_FUND' begin if @asset_manager_id=@owner_id select @deb_acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.2') else select @deb_acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.3') end if @fund_type='PIF_FUND' select @deb_acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.10') select @subc_type_id=.SUBCTYPE_FROM_CODE('bank_account') select @uk_subc_type_id=.SUBCTYPE_FROM_CODE('asset_manager') select @check_date=dateadd(dd,1,(.DATE_NORM(@in_date,B))) select @doc_name='Начисление задолженности по получению процентного дохода по депозитному счету' if @fund_type='PIF_FUND' and exists(select 1 from ts_sd_deposit_overvalue_list t where t.doc_id=@id and t.bank_acc_id not in (select os.item_id from t_oper_subconto os, t_oper_list ol, t_operations o where o.partner2plan_id=@pp_id and ol.head_id=o.id and ol.acc_id=@acc_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and o.op_date<@check_date group by os.item_id having sum(isnull(ol.sum_bal*ol.deb_or_cred,0))>0)) .EXIT_MESSAGE('Нет остатков по одному из отобранных счетов') if @fund_type='NPF_FUND' and exists(select 1 from ts_sd_deposit_overvalue_list t where t.doc_id=@id and t.bank_acc_id not in (select os.item_id from t_oper_subconto os, t_oper_list ol, t_operations o where o.partner2plan_id=@pp_id and ol.head_id=o.id and ol.acc_id=@acc_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and o.op_date<@check_date and exists(select 1 from t_oper_subconto os2 where os2.id=ol.id and os2.subconto_type_id=@uk_subc_type_id and os2.item_id=isnull(@initiator_id,@owner_id)) group by os.item_id having sum(isnull(ol.sum_bal*ol.deb_or_cred,0))>0)) .EXIT_MESSAGE('Нет остатков по одному из отобранных счетов') begin tran .UPDATE_STATE .ACTION_HISTORY declare items cursor for select t.bank_acc_id, t.calc_sum from ts_sd_deposit_overvalue_list t where t.doc_id=@id for read only open items fetch items into @bank_acc_id, @calc_sum while .CURSOR_STATE=0 begin select @partner_id=null if @fund_type = 'NPF_FUND' and @asset_manager_id<>@owner_id select @partner_id=@asset_manager_id else select @partner_id=(select b.partner_id from t_banks b, t_bank_accounts ba where ba.id=@bank_acc_id and ba.bank_id=b.id and .ITEMS_EXISTS_BY_TYPE(b.partner_id,'PARTNERS_JUR')) if @partner_id is null begin select @ms='Не задано юр.лицо-банк для депозитного счета № '+(select bank_account_no from t_bank_accounts where id=@bank_acc_id) .EXIT_MESSAGE(@ms) end .OPERATION(@owner_id,@plan_id,@in_date,@deb_acc_id,@cred_acc_id,@calc_sum,'RUR',@in_date,null,null,@doc_name,N) .UPDATE_SUBC(D,'partners',@partner_id) if @fund_type = 'PIF_FUND' begin .UPDATE_SUBC(D,'bank_account',@bank_acc_id) end if @fund_type = 'NPF_FUND' begin .UPDATE_SUBC(K,'asset_manager',@asset_manager_id) end .SUBC_CHECK fetch items into @bank_acc_id, @calc_sum end close items .DEALLOCATE items .SD_ASSET_OPER_ADD(@id,null,null,null,@owner_id,null,null) commit tran .END $ENDTEXT(12495331) $ENDACTION $STATE2ACTION(BEG.ACTION1) state=BEG action=ACTION1 $ENDSTATE2ACTION $STATE2ACTION(ED.ACTION2) state=ED action=ACTION2 $ENDSTATE2ACTION $STATE2ACTION(ED.ACTION3) state=ED action=ACTION3 $ENDSTATE2ACTION $STATE2ACTION(ED.ACTION5) state=ED action=ACTION5 $ENDSTATE2ACTION $STATE2ACTION(ED.ACTION6) state=ED action=ACTION6 $ENDSTATE2ACTION $STATE2ACTION(ED.FORM1) state=ED action=FORM1 $ENDSTATE2ACTION $STATE2ACTION(ED.FORM2) state=ED action=FORM2 $ENDSTATE2ACTION $STATE2ACTION(ED.FORM3) state=ED action=FORM3 $ENDSTATE2ACTION $STATE2ACTION(OP_END.ACTION4) state=OP_END action=ACTION4 $ENDSTATE2ACTION $STATE2ACTION(OP_END.FORM1) state=OP_END action=FORM1 $ENDSTATE2ACTION $STATE2ACTION(OP_END.FORM2) state=OP_END action=FORM2 $ENDSTATE2ACTION $STATE2ACTION(OP_END.FORM3) state=OP_END action=FORM3 $ENDSTATE2ACTION $STATE2PROP(BEG.COMMENT) state=BEG prop=COMMENT enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(BEG.DAYS_QTY) state=BEG prop=DAYS_QTY enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(BEG.INITIATOR_ID) state=BEG prop=INITIATOR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(BEG.IN_DATE) state=BEG prop=IN_DATE enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BEG.IN_NO) state=BEG prop=IN_NO enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BEG.OWNER_ID) state=BEG prop=OWNER_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.COMMENT) state=ED prop=COMMENT enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.DAYS_QTY) state=ED prop=DAYS_QTY enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.INITIATOR_ID) state=ED prop=INITIATOR_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.IN_DATE) state=ED prop=IN_DATE enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.IN_NO) state=ED prop=IN_NO enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ED.OWNER_ID) state=ED prop=OWNER_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $ENDDOC