# Date: 24/03/06 # Version: 105 $DOC(PIF_PAY_REPS_GROUP) name=Отчеты регистратора class=5 product=ALD_PIF name_formula=null 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=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(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=1 init_formula=.OPERDAY unique_index=1 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=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(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=.GET_CONST('DEPODOC_NO_PREFIX')+ .FILL(%N%,'0',6) unique_index=1 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=1 init_formula=(select td_pay_fond.spec_reg_id from td_pay_fond where id=@owner_id) 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=1 init_formula=1 unique_index=null numerator=null import_type=null import_table=null import_field= 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=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 $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=2 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=1 init_formula=.CUR("RUR") 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=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OWNER_ID) name=Фонд field_name=owner_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=16 init_class=1 init_formula=.ORGANIZATION_PIF unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(REG_DATE) name=Дата проведения в реестре field_name=reg_date table_name=ts_reg_report field_type=datetime field_length=8 field_scale=3 field_prec=23 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(PAY_PRICE) name=Цена пая field_name=pay_price table_name=ts_reg_report field_type=decimal field_length=9 field_scale=8 field_prec=18 page=1 order=18 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(REP_NO) name=Номер отчета field_name=rep_no table_name=ts_reg_report field_type=varchar field_length=50 field_scale=null field_prec=50 page=1 order=19 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_DATE) name=Дата ценообразования field_name=pay_date table_name=ts_reg_report field_type=datetime field_length=8 field_scale=3 field_prec=23 page=1 order=20 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 $RELATION(REL3) class=0 type=PARTNERS form=null prop=CORRESPONDENT_ID idname=null order=1 $ENDRELATION $RELATION(REL4) class=0 type=PARTNERS form=null prop=PAYER_ID idname=null order=2 $ENDRELATION $RELATION(REL5) class=0 type=PARTNERS form=null prop=INITIATOR_ID idname=null order=3 $ENDRELATION $RELATION(REL6) class=0 type=PIF_FUND form=null prop=OWNER_ID idname=null order=4 $ENDRELATION $RELATION(REL1) class=5 type=PIF_PAY_REPORT form=D_PIF_PAY_REP_EDIT prop=null idname=null order=null $ENDRELATION $RELATION(REL2) class=5 type=PIF_PAY_CANCEL form=FORM1 prop=null idname=null order=null $ENDRELATION $FORM(D_REESTR_IMP) name=Список для импорта заявок к отчету регистратора class=5 filter=null target_state= uo=uo_reestr_import dw=d_reestr_ircol_txt_imp procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(D_REESTR_IMP_DB) name=Список для импорта заявок к отчету регистратора(DB) class=5 filter=null target_state= uo=uo_csv_import dw=d_reestr_txt_imp_db procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(ED_PIF_PAY_REP_LIST) name=Список отчетов к импорту class=4 filter=null target_state= uo=uo_list dw=d_pif_pay_rep_group_list procedure=ap_10000000000089132 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224977) 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 and td_depo_docs.owner_id=.ORGANIZATION and upper((select t_states.state_code from t_states where t_items.state_id = t_states.id))='ED_CLAIM' end $ENDTEXT(17224977) $ACTION2RELATION(REL6.PARTNER_CODE) relation=REL6 where=null prop=PARTNER_CODE order=35 alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL5.PARTNER_CODE) relation=REL5 where=null prop=PARTNER_CODE order=19 alias=initiator_code $ENDACTION2RELATION $ACTION2RELATION(REL5.PARTNER_SHORT_NAME) relation=REL5 where=null prop=PARTNER_SHORT_NAME order=10 alias=initiator_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=19 alias=corr_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=10 alias=corr_short_name $ENDACTION2RELATION $ACTION2RELATION(REL4.PARTNER_CODE) relation=REL4 where=null prop=PARTNER_CODE order=19 alias=payer_code $ENDACTION2RELATION $ACTION2RELATION(REL4.PARTNER_SHORT_NAME) relation=REL4 where=null prop=PARTNER_SHORT_NAME order=10 alias=payer_short_name $ENDACTION2RELATION $ENDFORM $FORM(FORM2) name=Фильтр class=6 filter=null target_state= uo=uo_filter dw=d_pif_pay_rep_filter procedure=ap_10000000000089137 patterns=null user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=1458 height=684 proc_text=$TEXT(17224977) create procedure dbo.%PROC% as begin declare @owner_id numeric declare @owner_name varchar(255) select @owner_id=.ORGANIZATION_PIF select @owner_name=(select partner_short_name from t_partners where id=@owner_id) select convert(datetime, null) as in_date_db, convert(datetime, null) as in_date_de, convert(varchar(50), null) as in_no, convert(varchar(30), @owner_id) as owner_id_s, convert(datetime, null) as reg_date_db, convert(datetime, null) as reg_date_de, convert(varchar(50), null) as rep_no, @owner_name as owner_name end $ENDTEXT(17224977) $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 $FILTER(REP_NO) type=1 label=Номер отчета prop=REP_NO arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(REG_DATE) type=2 label=Дата проведения в реестре prop=REG_DATE arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(IN_DATE) type=2 label=IN_DATE prop=IN_DATE arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(IN_NO) type=1 label=IN_NO prop=IN_NO arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(PIF_PAY_REPS_LIST) name=Список для текущего фонда class=4 filter=FORM2 target_state= uo=uo_list dw=d_pif_pay_rep_group_list procedure=ap_10000000000089142 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=3575 height=1012 proc_text=$TEXT(17224977) 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 --and td_depo_docs.owner_id=.ORGANIZATION end $ENDTEXT(17224977) $ACTION2RELATION(REL5.PARTNER_CODE) relation=REL5 where=null prop=PARTNER_CODE order=20 alias=initiator_code $ENDACTION2RELATION $ACTION2RELATION(REL5.PARTNER_SHORT_NAME) relation=REL5 where=null prop=PARTNER_SHORT_NAME order=21 alias=initiator_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=20 alias=corr_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=21 alias=corr_short_name $ENDACTION2RELATION $ACTION2RELATION(REL4.PARTNER_CODE) relation=REL4 where=null prop=PARTNER_CODE order=20 alias=payer_code $ENDACTION2RELATION $ACTION2RELATION(REL4.PARTNER_SHORT_NAME) relation=REL4 where=null prop=PARTNER_SHORT_NAME order=21 alias=payer_short_name $ENDACTION2RELATION $ACTION2RELATION(REL6.PARTNER_CODE) relation=REL6 where=null prop=PARTNER_CODE order=35 alias=partner_code $ENDACTION2RELATION $ENDFORM $FORM(SBRF_REESTR_IMPORT_LIST) name=Список для импорта полученных и принятых заявок (СБРФ) class=5 filter=null target_state= uo=uo_sber_queries_reestr_import dw=d_pif_pay_opers_sbrf_import procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $ACTION(ACTION_IMPORT) name=Импорт class=1 form=D_REESTR_IMP target_state=null procedure=ap_10000000000089147 patterns=GENERIC_INSERT_O user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224979) create procedure dbo.%PROC% @q_type varchar(255), -- тип заявки, @payer varchar(255), -- пайщик @acc_no varchar(255), -- № счета @q_date varchar(255), -- дата заявки @q_no varchar(255), -- номер заявки @p_date varchar(255), -- дата плат @p_no varchar(255), -- № плат @q_sum varchar(255), -- сумма @qty varchar(255), -- кол-во паев @agent varchar(255), -- агент @agent_sum varchar(255), -- сумма комиссии @report_id_s varchar(30) as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% .ID_VAR(@report_id) .ID_VAR(@q_type_id) .INT_VAR(@is_cancel) declare @q_date_d datetime declare @p_date_d datetime declare @q_sum_m money declare @qty_m .PIF_PAY_TYPE declare @agent_sum_m money select @report_id=convert(numeric,@report_id_s) if @report_id is null .EXIT_MESSAGE('Не указан отчет регистратора') if upper(.ITEM_TYPE_CODE(@report_id))='PIF_PAY_REPORT' select @is_cancel=0 else select @is_cancel=1 select @q_type=.STR_FOR_IF(@q_type) if @q_type='1' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') if @q_type='2' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_BUY') if @q_type_id is null .EXIT_MESSAGE('Не указан тип заявки') if @q_sum='' select @q_sum=null if @qty='' select @qty=null if @agent_sum='' select @agent_sum=null select @q_sum_m=convert(money,@q_sum) select @qty_m=convert(.PIF_PAY_TYPE,@qty) if @is_cancel=0 and (@q_sum_m is null or @qty_m is null) .EXIT_MESSAGE('Не указана сумма или кол-во паев') select @agent_sum_m=convert(money,@agent_sum) if @q_date <> '' select @q_date_d=convert(datetime,(substring(@q_date,9,2)+'/'+substring(@q_date,6,2)+'/'+substring(@q_date,1,4)),103) if @p_date <> '' select @p_date_d=convert(datetime,(substring(@p_date,9,2)+'/'+substring(@p_date,6,2)+'/'+substring(@p_date,1,4)),103) if @q_date_d is null or @q_no is null .EXIT_MESSAGE('Не указана дата или номер заявки') if @q_type='1' and (@p_date_d is null or @p_no is null) and @is_cancel=0 .EXIT_MESSAGE('Не указана дата или номер платежа для заявки на выдачу') if exists(select 1 from tp_pay2rep_imp where q_no=@q_no and q_date=@q_date_d and q_type_id=@q_type_id and ((@q_type='1' and p_no=@p_no and p_date=@p_date_d and @is_cancel=0) or @q_type='2' or @is_cancel=1)) .EXIT_MESSAGE('Заявка уже была импортирована') begin tran .TYPE_INIT('PIF_PAY2REP_IMP') .TYPE_ASSIGN(extract_id,@report_id) .TYPE_ASSIGN(agent,@agent) .TYPE_ASSIGN(payer,@payer) .TYPE_ASSIGN(acc_no,@acc_no) .TYPE_ASSIGN(q_type_id,@q_type_id) .TYPE_ASSIGN(q_date,@q_date_d) .TYPE_ASSIGN(q_no,@q_no) .TYPE_ASSIGN(p_date,@p_date_d) .TYPE_ASSIGN(p_no,@p_no) .TYPE_ASSIGN(q_sum,@q_sum_m) .TYPE_ASSIGN(q_qty,@qty_m) .TYPE_ASSIGN(agent_sum,@agent_sum_m) .TYPE_INSERT('PIF_PAY2REP_IMP','WAIT') if upper(.ITEM_STATE_CODE(@report_id))='ED' .ITEM_MOVE_STATE(@report_id,'WAIT_CHECK') commit tran select sid = convert(varchar, @id) end $ENDTEXT(17224979) $ENDACTION $ACTION(ACTION_INSERT) name=Импорт (DB) class=1 form=D_REESTR_IMP_DB target_state=null procedure=ap_30000000000027225 patterns=null user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224979) create procedure dbo.%PROC% @code varchar(255), @r_no varchar(255), @q_no varchar(255), @q_date varchar(255), @qty varchar(255), @pay_date_s varchar(255), @sk_proc varchar(255), @agent_sum varchar(255), @pay_price varchar(255), @q_sum varchar(255), @round varchar(255), @acc_no varchar(255), @agent varchar(255), @agent_up varchar(255), @payer varchar(255), @pay_price_op varchar(255), @as_of varchar(255), @a_fond varchar(255), @a_acc_no varchar(255), @a_payer varchar(255), @q_type varchar(255), @status varchar(255), @op_date varchar(255), @reason varchar(255), @p_no varchar(255), @p_date varchar(255), @b_sum varchar(255), @bank varchar(255), @b_rec_no varchar(255), @b_date varchar(255)--, --@report_id_s varchar(30) as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% .ID_VAR(@report_id) .ID_VAR(@q_type_id) .INT_VAR(@is_cancel) declare @q_date_d datetime declare @p_date_d datetime declare @q_sum_m money declare @qty_m .PIF_PAY_TYPE declare @agent_sum_m money declare @reg_date datetime declare @pay_date datetime declare @owner_id numeric(18,0) declare @owner_imp_id numeric(18,0) declare @op_date_d datetime declare @pay_date_d datetime declare @sk_proc_m money declare @pay_price_m money declare @round_m money declare @pay_price_op_m money declare @as_of_m money declare @indoc_type_id numeric, @document_form_id numeric, @indoc_comment varchar(255), @corr_name varchar(255), @corr_id numeric, @indoc_id numeric, @in_time datetime, @indoc_id_s varchar(30), @days int, @out_date datetime, @indate datetime, @out_no varchar(50), @indocs_id numeric select @in_time=.OPERDAY select @indate=.OPERDAY .NRC_CUT_FIELD(@code) .NRC_CUT_FIELD(@r_no) .NRC_CUT_FIELD(@q_no) .NRC_CUT_FIELD(@qty) .NRC_CUT_FIELD(@sk_proc) .NRC_CUT_FIELD(@agent_sum) .NRC_CUT_FIELD(@pay_price) .NRC_CUT_FIELD(@q_sum) .NRC_CUT_FIELD(@round) .NRC_CUT_FIELD(@acc_no) .NRC_CUT_FIELD(@agent) .NRC_CUT_FIELD(@agent_up) .NRC_CUT_FIELD(@payer) .NRC_CUT_FIELD(@pay_price_op) .NRC_CUT_FIELD(@as_of) .NRC_CUT_FIELD(@a_fond) .NRC_CUT_FIELD(@a_acc_no) .NRC_CUT_FIELD(@a_payer) .NRC_CUT_FIELD(@status) .NRC_CUT_FIELD(@op_date) .NRC_CUT_FIELD(@reason) .NRC_CUT_FIELD(@p_no) .NRC_CUT_FIELD(@b_sum) .NRC_CUT_FIELD(@bank) .NRC_CUT_FIELD(@b_rec_no) .NRC_CUT_FIELD(@q_date) .NRC_CUT_FIELD(@pay_date_s) .NRC_CUT_FIELD(@q_type) .NRC_CUT_FIELD(@p_date) .NRC_CUT_FIELD(@b_date) .ID_VAR(@folder_id) select @folder_id = .DEPO_FOLDER_DEFAULT select @q_sum = replace(@q_sum,',','.') select @b_sum = replace(@b_sum,',','.') select @qty = replace(@qty,',','.') select @sk_proc= replace(@sk_proc,',','.') select @pay_price= replace(@pay_price,',','.') select @round = replace(@round,',','.') select @pay_price_op = replace(@pay_price_op,',','.') select @as_of= replace(@as_of,',','.') select @agent_sum = replace(@agent_sum,',','.') if @q_type not in ('4') begin select @op_date_d = convert(datetime,@op_date) select @pay_date_d = convert(datetime,@pay_date_s) select @owner_imp_id = td_depo_docs.owner_id from td_depo_docs, t_item2class, t_classvalues, t_classes where td_depo_docs.owner_id = t_item2class.item_id and t_classvalues.id=t_item2class.value_id and t_classvalues.code='FUND_IMPORT_CODE' and t_classes.id=t_classvalues.class_id and t_classes.code='FUND_IMPORT_CODE' and t_item2class.code = @code if @status = '1' select @status = '0' if @status = '2' select @status = '1' --** Отчет регистратора if @status = 0 begin select @report_id = ts_reg_report.id from ts_reg_report, td_depo_docs where ts_reg_report.reg_date = @op_date_d and ts_reg_report.pay_date = @pay_date_d and td_depo_docs.owner_id = @owner_imp_id and td_depo_docs.id = ts_reg_report.id and .ITEMS_EXISTS_BY_TYPE_STATE(ts_reg_report.id,'PIF_PAY_REPORT','WAIT_CHECK') and .ITEMS_EXISTS_BY_TYPE(ts_reg_report.id,'PIF_PAY_REPORT') if @report_id is null ----- вставка отчета ----- begin .TYPE_INIT('PIF_PAY_REPORT',1) .TYPE_ASSIGN(owner_id,@owner_imp_id,1) .TYPE_ASSIGN(reg_date,@op_date_d,1) .TYPE_ASSIGN(in_date,@op_date_d,1) .TYPE_ASSIGN(folder_id,@folder_id,1) .TYPE_ASSIGN(pay_date,@pay_date_d,1) .TYPE_INSERT('PIF_PAY_REPORT','ED_CLAIM',1) select @id = @id1_1 select @report_id = @id1_1 .DEPO_GEN_NO(@in_no1_1,1) .TYPE_UPDATE(@id1_1,'PIF_PAY_REPORT',1) ------- генерация вход. документа---------- -----проверка существования вх. документа с типом "отчета регистратора" ,с данным фондом и датой if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and owner_id=@owner_imp_id and indoc_type_id=(select id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора') and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) begin select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment='Фонды УК "ДВС Инвестмент"' select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days select @out_no =convert(varchar(10), @out_date,103) .TYPE_INIT('SD_INDOC',4) .TYPE_ASSIGN(out_date,@out_date,4) .TYPE_ASSIGN(in_date,.OPERDAY,4) .TYPE_ASSIGN(out_no,@out_no,4) .TYPE_ASSIGN(in_time,@in_time,4) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,4) .TYPE_ASSIGN(corr_name,@corr_name,4) .TYPE_ASSIGN(document_form_id,@document_form_id,4) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,4) .TYPE_ASSIGN(owner_id,@owner_imp_id,4) .TYPE_ASSIGN(comment,@indoc_comment,4) .TYPE_ASSIGN(send_date,.OPERDAY,4) .TYPE_INSERT('SD_INDOC','ED',4) select @indoc_id=.TYPE_FIELD(id,4) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',5) .TYPE_ASSIGN(indoc_id,@id4_1,5) .TYPE_ASSIGN(doc_id,@id1_1,5) .TYPE_INSERT('SD_INDOC2DOC','CRT',5) end else begin --прикрепляем к существующему вход. документу select @indocs_id=id from t_sd_indocs where in_date=.OPERDAY and owner_id=@owner_imp_id and indoc_type_id=(select id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора') and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC') .TYPE_INIT('SD_INDOC2DOC',6) .TYPE_ASSIGN(indoc_id,@indocs_id,6) .TYPE_ASSIGN(doc_id,@id1_1,6) .TYPE_INSERT('SD_INDOC2DOC','CRT',6) end -------конец генерации вход. документа---------- end end --** if @status = 1 begin select @report_id = ts_reg_report.id from ts_reg_report, td_depo_docs where ts_reg_report.reg_date = @op_date_d and ts_reg_report.pay_date = @pay_date_d and td_depo_docs.owner_id = @owner_imp_id and td_depo_docs.id = ts_reg_report.id and .ITEMS_EXISTS_BY_TYPE_STATE(ts_reg_report.id,'PIF_PAY_CANCEL','WAIT_CHECK') and .ITEMS_EXISTS_BY_TYPE(ts_reg_report.id,'PIF_PAY_CANCEL') if @report_id is null ----- вставка отчета ----- begin .TYPE_INIT('PIF_PAY_CANCEL',2) .TYPE_ASSIGN(owner_id,@owner_imp_id,2) .TYPE_ASSIGN(reg_date,@op_date_d,2) .TYPE_ASSIGN(in_date,@op_date_d,2) .TYPE_ASSIGN(folder_id,@folder_id,2) .TYPE_ASSIGN(pay_date,@pay_date_d,2) .TYPE_INSERT('PIF_PAY_CANCEL','ED_CLAIM',2) select @id = @id2_1 select @report_id = @id2_1 .DEPO_GEN_NO(@in_no2_1,2) .TYPE_UPDATE(@id2_1,'PIF_PAY_CANCEL',2) ------- генерация вход. документа---------- -----проверка существования вх. документа с типом "отчета регистратора" ,с данным фондом и датой if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and owner_id=@owner_imp_id and indoc_type_id=(select id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора') and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) begin select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment='Фонды УК "ДВС Инвестмент"' select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days select @out_no =convert(varchar(10), @out_date,103) .TYPE_INIT('SD_INDOC',7) .TYPE_ASSIGN(out_date,@out_date,7) .TYPE_ASSIGN(in_date,.OPERDAY,7) .TYPE_ASSIGN(out_no,@out_no,7) .TYPE_ASSIGN(in_time,@in_time,7) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,7) .TYPE_ASSIGN(corr_name,@corr_name,7) .TYPE_ASSIGN(document_form_id,@document_form_id,7) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,7) .TYPE_ASSIGN(owner_id,@owner_imp_id,7) .TYPE_ASSIGN(comment,@indoc_comment,7) .TYPE_ASSIGN(send_date,.OPERDAY,7) .TYPE_INSERT('SD_INDOC','ED',7) select @indoc_id=.TYPE_FIELD(id,7) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',8) .TYPE_ASSIGN(indoc_id,@id7_1,8) .TYPE_ASSIGN(doc_id,@id2_1,8) .TYPE_INSERT('SD_INDOC2DOC','CRT',8) end else begin --прикрепляем к существующему вход. документу select @indocs_id=id from t_sd_indocs where in_date=.OPERDAY and owner_id=@owner_imp_id and indoc_type_id=(select id FROM t_sd_indoc_types WHERE indoc_name = 'Отчет регистратора') and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC') .TYPE_INIT('SD_INDOC2DOC',9) .TYPE_ASSIGN(indoc_id,@indocs_id,9) .TYPE_ASSIGN(doc_id,@id2_1,9) .TYPE_INSERT('SD_INDOC2DOC','CRT',9) end -------конец генерации вход. документа---------- end end select @q_type=.STR_FOR_IF(@q_type) if @q_type='1' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') if @q_type='2' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') if @q_type='3' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_BUY') if @q_type='4' select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('TP_PIF_PAYS_MOVES') if @q_type_id is null .EXIT_MESSAGE('Не указан тип заявки') if @q_sum='' select @q_sum=null if @qty='' select @qty=null if @agent_sum='' select @agent_sum=null if @sk_proc='' select @sk_proc=null if @pay_price='' select @pay_price=null if @round='' select @round=null if @pay_price_op='' select @pay_price_op=null if @as_of='' select @as_of=null select @q_sum_m=convert(money,@q_sum) --select @q_sum_m=convert(money,@b_sum) select @qty_m=convert(.PIF_PAY_TYPE,@qty) select @sk_proc_m=convert(money,@sk_proc) select @pay_price_m=convert(money,@pay_price) select @round_m=convert(money,@round) select @pay_price_op_m=convert(money,@pay_price_op) select @as_of_m=convert(money,@as_of) if @status=0 and (@q_sum_m is null or @qty_m is null) .EXIT_MESSAGE('Не указана сумма или кол-во паев') select @agent_sum_m=convert(money,@agent_sum) --.EXIT_MESSAGE_PARM('1',@q_date) if @q_date <> '' select @q_date_d=convert(datetime,@q_date) if @p_date <> '' select @p_date_d=convert(datetime,@p_date) if @q_date_d is null or @q_no is null .EXIT_MESSAGE('Не указана дата или номер заявки') if @q_type='1' and (@p_date_d is null or @p_no is null) and @status=0 .EXIT_MESSAGE('Не указана дата или номер платежа для заявки на выдачу') if @q_type='2' and (@p_date_d is null or @p_no is null) and @status=0 .EXIT_MESSAGE('Не указана дата или номер платежа для заявки на выдачу') if exists(select 1 from tp_pay2rep_imp where q_no=@q_no and q_date=@q_date_d and q_type_id=@q_type_id and ((@q_type='1' and p_no=@p_no and p_date=@p_date_d and @status=0) or (@q_type='1' and p_no=@p_no and p_date=@p_date_d and @status=0) /* or @q_type='3' or @q_type='4'*/ or @status=1)) .EXIT_MESSAGE('Заявка уже была импортирована') begin tran .TYPE_INIT('PIF_PAY2REP_IMP',3) .TYPE_ASSIGN(extract_id,@report_id,3) .TYPE_ASSIGN(agent,@agent,3) .TYPE_ASSIGN(payer,@payer,3) .TYPE_ASSIGN(acc_no,@acc_no,3) .TYPE_ASSIGN(q_type_id,@q_type_id,3) .TYPE_ASSIGN(q_date,@q_date_d,3) .TYPE_ASSIGN(q_no,@q_no,3) .TYPE_ASSIGN(p_date,@p_date_d,3) .TYPE_ASSIGN(p_no,@p_no,3) .TYPE_ASSIGN(q_sum,@q_sum_m,3) .TYPE_ASSIGN(q_qty,@qty_m,3) .TYPE_ASSIGN(agent_sum,@agent_sum_m,3) .TYPE_ASSIGN(sk_proc,@sk_proc_m,3) .TYPE_ASSIGN(pay_price,@pay_price_m,3) .TYPE_ASSIGN(round,@round_m,3) .TYPE_ASSIGN(pay_price_op,@pay_price_op_m,3) .TYPE_ASSIGN(as_of,@as_of_m,3) .TYPE_ASSIGN(a_fond,@a_fond,3) .TYPE_ASSIGN(a_acc_no,@a_acc_no,3) .TYPE_ASSIGN(a_payer,@a_payer,3) .TYPE_ASSIGN(reason,@reason,3) .TYPE_INSERT('PIF_PAY2REP_IMP','WAIT',3) if upper(.ITEM_STATE_CODE(@report_id))='ED' .ITEM_MOVE_STATE(@report_id,'WAIT_CHECK') commit tran end select sid = convert(varchar, @id) end $ENDTEXT(17224979) $ENDACTION $ACTION(REESTR_CHECK) name=Квитовка новая class=9 form=null target_state=null procedure=ap_10000000000089148 patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224979) create procedure dbo.%PROC% @sid varchar(30) = null as .ID_VAR(@id) .ID_VAR(@fund_id) .INT_VAR(@res) .ID_VAR(@alloc_id) .INT_VAR(@is_cancel) declare @cl_id numeric(18,0) select @id=convert(numeric,@sid) if not exists(select 1 from tp_pay2rep_imp where extract_id=@id) .EXIT_MESSAGE('Нет заявок для квитовки') if upper(.ITEM_TYPE_CODE(@id))='PIF_PAY_REPORT' select @is_cancel=0 else select @is_cancel=1 select @fund_id=owner_id from td_depo_docs where id=@id select @res=1 select @alloc_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') .TRANSACTION_SAVE create table #check( imp_id numeric null, q_type_id numeric null, claim_id numeric null, paymt_id numeric null, mess varchar(255) null, bad int null ) insert into #check(imp_id,q_type_id) select tp_pay2rep_imp.id, tp_pay2rep_imp.q_type_id from tp_pay2rep_imp where upper(.ITEM_STATE_CODE(tp_pay2rep_imp.id))!='FIX' and tp_pay2rep_imp.extract_id=@id if exists(select 1 from #check) begin begin tran -- заявки update #check set claim_id = tp_pif_queries.id from tp_pif_queries, tp_pay2rep_imp imp, t_items, td_depo_docs where #check.imp_id=imp.id and rtrim(ltrim(tp_pif_queries.agent_regs_no))=rtrim(ltrim(imp.q_no)) and tp_pif_queries.agent_regs_date=imp.q_date and tp_pif_queries.id=td_depo_docs.id and td_depo_docs.owner_id=@fund_id and tp_pif_queries.id=t_items.id and t_items.type_id=#check.q_type_id and exists(select 1 from t_states where t_states.id=t_items.state_id and t_states.class_id=0) update #check set mess='Не найдена заявка по дате и номеру', bad=1 where claim_id is null update #check set mess='Найденная заявка находится в статусе '+t_states.state_name, bad=1 from t_items, t_states where #check.bad is null and #check.claim_id=t_items.id and t_items.state_id=t_states.id and upper(t_states.state_code) not in ('ED','NOT_PAYMT','PROV') update #check set mess='В найденной заявке не указан агентский договор', bad=1 from tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and tp_pif_queries.type_queries not in (3,4) and (tp_pif_queries.agent_id is null or tp_pif_queries.agent_dog_id is null) update #check set mess='В найденной заявке не указан пайщик', bad=1 from tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and tp_pif_queries.pay_owner_id is null -- платежи declare cur cursor for select claim_id from #check, tp_pif_queries where #check.claim_id=tp_pif_queries.id and tp_pif_queries.type_queries not in (3,4) open cur fetch cur into @cl_id while(@@fetch_status = 0) begin update #check set paymt_id=t_paydocs.id from t_paydocs, tp_pay2rep_imp imp, td_depo_docs , tp_pif_queries , t_items , t_states , #check where #check.bad is null and #check.imp_id=imp.id and #check.q_type_id=@alloc_id and rtrim(ltrim(t_paydocs.doc_number))=rtrim(ltrim(imp.p_no)) and t_paydocs.doc_date=imp.p_date and t_paydocs.id=td_depo_docs.id and td_depo_docs.owner_id=@fund_id and t_paydocs.doc_sum = imp.q_sum and tp_pif_queries.id = @cl_id and .PURPOSE_CODE(t_paydocs.purpose_id) in (4011) and rtrim(ltrim(tp_pif_queries.agent_regs_no))=rtrim(ltrim(imp.q_no)) and tp_pif_queries.agent_regs_date=imp.q_date and t_items.state_id=t_states.id and t_items.id = t_paydocs.id and upper(t_states.state_code) in ('ED','WAIT_QUERY','PERF') and t_paydocs.from_partner_id = tp_pif_queries.pay_owner_id and (.ITEMS_EXISTS_BY_TYPE(t_paydocs.id,'SD_PAYBANK_IN') or .ITEMS_EXISTS_BY_TYPE(t_paydocs.id,'SD_PAYDOCS_CASH_IN')) and claim_id = #check.claim_id update #check set mess='Не найден платеж', bad=2 where #check.bad is null and #check.paymt_id is null and #check.q_type_id=@alloc_id and @is_cancel=0 and #check.claim_id = @cl_id fetch cur into @cl_id end close cur deallocate cur -- заявки к платежам update t_paydocs set pif_pay_id=#check.claim_id from #check where #check.bad is null and #check.q_type_id=@alloc_id and t_paydocs.id=#check.paymt_id and t_paydocs.pif_pay_id is null update #check set mess='В найденном платеже указана другая заявка', bad=2 from t_paydocs, tp_pif_queries where #check.bad is null and #check.q_type_id=@alloc_id and #check.claim_id=tp_pif_queries.id and #check.paymt_id=t_paydocs.id and t_paydocs.pif_pay_id is not null and tp_pif_queries.type_queries not in (3,4) and t_paydocs.pif_pay_id != tp_pif_queries.id -- проверка привязок -- выкуп и одноразовые на размещение update #check set mess='Найденная заявка уже находится в отчете: док-т № '+ isnull(td_depo_docs.in_no,''), bad=1 from tp_extract2claim, td_depo_docs, tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and tp_pif_queries.id=tp_extract2claim.claim_id and tp_extract2claim.extract_id=td_depo_docs.id and (#check.q_type_id!=@alloc_id or (#check.q_type_id=@alloc_id and tp_pif_queries.pif_pay_type_id=1 and ((tp_extract2claim.extract_id!=@id and @is_cancel=0) or @is_cancel=1))) -- размещение с таким же платежем update #check set mess='Найденная заявка c платежем уже находится в отчете: док-т № '+ isnull(td_depo_docs.in_no,''), bad=1 from tp_extract2claim, td_depo_docs, tp_pif_queries, tp_paymt2pays where #check.bad is null and #check.q_type_id=@alloc_id and #check.claim_id=tp_pif_queries.id and tp_pif_queries.type_queries not in (3,4) and tp_pif_queries.id=tp_extract2claim.claim_id and tp_extract2claim.extract_id=td_depo_docs.id and tp_paymt2pays.pif_pay_id=tp_extract2claim.id and tp_paymt2pays.paymt_id=#check.paymt_id and (tp_pif_queries.pif_pay_type_id=2 or (tp_pif_queries.pif_pay_type_id=1 and tp_extract2claim.extract_id=@id)) -- проверка повторной квитовки (выкуп) update #check set mess='Найденная заявка уже сквитована в этом отчете', bad=1 where #check.bad is null and #check.q_type_id!=@alloc_id and #check.imp_id in (select min(ch.imp_id) from #check ch where ch.bad is null and ch.q_type_id!=@alloc_id group by ch.claim_id having count(*)>1) -- проверка повторной квитовки (размещение) update #check set mess='Найденная заявка уже сквитована в этом отчете', bad=1 where #check.bad is null and #check.q_type_id=@alloc_id and #check.imp_id in (select min(ch.imp_id) from #check ch where ch.bad is null and ch.q_type_id=@alloc_id group by ch.claim_id, ch.paymt_id having count(*)>1) -- обработка сквитованного .ID_VAR(@q_type_id) .ID_VAR(@claim_id) .ID_VAR(@paymt_id) .ID_VAR(@agent_id) .ID_VAR(@payer_id) .ID_VAR(@extract2claim_id) .ID_VAR(@claim_type_id) .ID_VAR(@agent_dog_id) .INT_VAR(@first) declare @imp_sum money, @imp_qty .PIF_PAY_TYPE, @imp_agent_sum money declare @claim_id_s varchar(30), @paymt_id_s varchar(30) declare claims cursor for select #check.q_type_id, #check.claim_id, #check.paymt_id, imp.q_sum, imp.q_qty, imp.agent_sum, tp_pif_queries.agent_id, tp_pif_queries.pay_owner_id, tp_pif_queries.agent_dog_id from #check, tp_pay2rep_imp imp, tp_pif_queries where #check.bad is null and #check.imp_id=imp.id and #check.claim_id=tp_pif_queries.id for read only open claims fetch claims into @q_type_id, @claim_id, @paymt_id, @imp_sum, @imp_qty, @imp_agent_sum, @agent_id, @payer_id, @agent_dog_id while (.CURSOR_STATE = 0) begin select @first=0 .PIF_ROUND_PAYS(@fund_id,@imp_qty,@imp_qty) if @q_type_id=@alloc_id select @claim_type_id=1 else select @claim_type_id=2 select @extract2claim_id=(select id from tp_extract2claim where extract_id=@id and claim_id=@claim_id) if @extract2claim_id is null begin -- привязка заявки if upper(.ITEM_STATE_CODE(@claim_id)) in ('ED','NOT_PAYMT') begin .ITEM_MOVE_STATE(@claim_id,'PROV') end .TYPE_INIT('PIF_PAYS2REPORT',2) .TYPE_ASSIGN(share_qty,@imp_qty,2) .TYPE_ASSIGN(agent_sum,@imp_agent_sum,2) .TYPE_ASSIGN(sum_for_transfer,@imp_sum,2) .TYPE_ASSIGN(claim_id,@claim_id,2) .TYPE_ASSIGN(extract_id,@id,2) .TYPE_ASSIGN(pay_agent_id,@agent_id,2) .TYPE_ASSIGN(pay_partner_id,@payer_id,2) .TYPE_ASSIGN(tax_percent,0,2) .TYPE_ASSIGN(is_new_tax,0,2) .TYPE_ASSIGN(is_manual_tax,0,2) .TYPE_ASSIGN(tax_sum,0,2) .TYPE_ASSIGN(alloc_sum,0,2) .TYPE_ASSIGN(get_paymt_type,2,2) .TYPE_ASSIGN(claim_type_id,@claim_type_id,2) .TYPE_ASSIGN(agent_dog_id,@agent_dog_id,2) .TYPE_INSERT('PIF_PAYS2REPORT','CRT',2) select @extract2claim_id=.TYPE_FIELD(id,2) select @first=1 end if @q_type_id=@alloc_id and (@is_cancel=0 or (@is_cancel=1 and @paymt_id is not null)) begin -- привязка платежа к привязке заявки if upper(.ITEM_STATE_CODE(@paymt_id)) in ('ED','WAIT_QUERY') begin select @paymt_id_s=convert(varchar(30),@paymt_id) if upper(.ITEM_TYPE_CODE(@paymt_id))='SD_PAYBANK_IN' begin if upper(.ITEM_STATE_CODE(@paymt_id))='ED' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYBANK_IN','OPS_IN',Y) end if upper(.ITEM_STATE_CODE(@paymt_id))='WAIT_QUERY' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYBANK_IN','OPS_FOR_QUERY',Y) end end if upper(.ITEM_TYPE_CODE(@paymt_id))='SD_PAYDOCS_CASH_IN' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYDOCS_CASH_IN','OPS_CASH_IN',Y) end end .TYPE_INIT('PIF_PAYMT2PAYS',3) .TYPE_ASSIGN(pif_pay_id,@extract2claim_id,3) .TYPE_ASSIGN(paymt_id,@paymt_id,3) .TYPE_INSERT('PIF_PAYMT2PAYS','CRT',3) if @first=0 update tp_extract2claim set sum_for_transfer=sum_for_transfer+@imp_sum, share_qty=share_qty+@imp_qty, agent_sum=agent_sum+@imp_agent_sum where id=@extract2claim_id end if @is_cancel=0 begin select @claim_id_s=convert(varchar(30),@extract2claim_id) .EXEC_ACTION(@claim_id_s,'PIF_PAYS2REPORT','claim_calc_parts',Y) end fetch claims into @q_type_id, @claim_id, @paymt_id, @imp_sum, @imp_qty, @imp_agent_sum, @agent_id, @payer_id, @agent_dog_id end close claims .DEALLOCATE claims .ID_VAR(@imp_type_id) .ID_VAR(@fix_id) .ID_VAR(@not1_id) .ID_VAR(@not2_id) select @imp_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY2REP_IMP') select @fix_id=.STATE_ID_FROM_CODE(@imp_type_id,'FIX') select @not1_id=.STATE_ID_FROM_CODE(@imp_type_id,'NOT_CLAIM') select @not2_id=.STATE_ID_FROM_CODE(@imp_type_id,'NOT_PAYMT') update t_items set state_id=@fix_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad is null update t_items set state_id=@not1_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad=1 update t_items set state_id=@not2_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad=2 update tp_pay2rep_imp set cause=#check.mess from #check where #check.imp_id=tp_pay2rep_imp.id if exists(select 1 from #check where bad is not null) select @res=0 commit tran end drop table #check .TRANSACTION_RESTORE begin tran if @res=1 begin .ITEM_MOVE_STATE(@id,'END_CHECK') end else begin .ITEM_MOVE_STATE(@id,'NOT_CHECK') end commit tran $ENDTEXT(17224979) $ENDACTION $ACTION(REESTR_CHECK_OLD) name=Квитовка class=9 form=null target_state=null procedure=ap_reestr_check patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224979) create procedure dbo.%PROC% @sid varchar(30) = null as .ID_VAR(@id) .ID_VAR(@fund_id) .INT_VAR(@res) .ID_VAR(@alloc_id) .INT_VAR(@is_cancel) select @id=convert(numeric,@sid) if not exists(select 1 from tp_pay2rep_imp where extract_id=@id) .EXIT_MESSAGE('Нет заявок для квитовки') if upper(.ITEM_TYPE_CODE(@id))='PIF_PAY_REPORT' select @is_cancel=0 else select @is_cancel=1 select @fund_id=owner_id from td_depo_docs where id=@id select @res=1 select @alloc_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') .TRANSACTION_SAVE create table #check( imp_id numeric null, q_type_id numeric null, claim_id numeric null, paymt_id numeric null, mess varchar(255) null, bad int null ) insert into #check(imp_id,q_type_id) select tp_pay2rep_imp.id, tp_pay2rep_imp.q_type_id from tp_pay2rep_imp where upper(.ITEM_STATE_CODE(tp_pay2rep_imp.id))!='FIX' and tp_pay2rep_imp.extract_id=@id if exists(select 1 from #check) begin begin tran -- заявки update #check set claim_id = tp_pif_queries.id from tp_pif_queries, tp_pay2rep_imp imp, t_items, td_depo_docs where #check.imp_id=imp.id and rtrim(ltrim(tp_pif_queries.agent_regs_no))=rtrim(ltrim(imp.q_no)) and tp_pif_queries.agent_regs_date=imp.q_date and tp_pif_queries.id=td_depo_docs.id and td_depo_docs.owner_id=@fund_id and tp_pif_queries.id=t_items.id and t_items.type_id=#check.q_type_id and exists(select 1 from t_states where t_states.id=t_items.state_id and t_states.class_id=0) update #check set mess='Не найдена заявка по дате и номеру', bad=1 where claim_id is null update #check set mess='Найденная заявка находится в статусе '+t_states.state_name, bad=1 from t_items, t_states where #check.bad is null and #check.claim_id=t_items.id and t_items.state_id=t_states.id and upper(t_states.state_code) not in ('ED','NOT_PAYMT','PROV') update #check set mess='В найденной заявке не указан агентский договор', bad=1 from tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and (tp_pif_queries.agent_id is null or tp_pif_queries.agent_dog_id is null) update #check set mess='В найденной заявке не указан пайщик', bad=1 from tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and tp_pif_queries.pay_owner_id is null -- платежи update #check set paymt_id=t_paydocs.id from t_paydocs, tp_pay2rep_imp imp, td_depo_docs where #check.bad is null and #check.imp_id=imp.id and #check.q_type_id=@alloc_id and rtrim(ltrim(t_paydocs.doc_number))=rtrim(ltrim(imp.p_no)) and t_paydocs.doc_date=imp.p_date and t_paydocs.id=td_depo_docs.id and td_depo_docs.owner_id=@fund_id and (.ITEMS_EXISTS_BY_TYPE(t_paydocs.id,'SD_PAYBANK_IN') or .ITEMS_EXISTS_BY_TYPE(t_paydocs.id,'SD_PAYDOCS_CASH_IN')) update #check set mess='Не найден платеж по дате и номеру', bad=2 where #check.bad is null and #check.paymt_id is null and #check.q_type_id=@alloc_id and @is_cancel=0 update #check set mess='В найденном платеже указана другая сумма', bad=2 from t_paydocs, tp_pay2rep_imp imp where #check.bad is null and #check.q_type_id=@alloc_id and #check.paymt_id=t_paydocs.id and #check.imp_id=imp.id and t_paydocs.doc_sum != imp.q_sum update #check set mess='В найденном платеже и заявке разные отправители', bad=2 from t_paydocs, tp_pif_queries where #check.bad is null and #check.q_type_id=@alloc_id and #check.claim_id=tp_pif_queries.id and #check.paymt_id=t_paydocs.id and t_paydocs.from_partner_id != tp_pif_queries.pay_owner_id update #check set mess='В найденном платеже указано другое целевое назначение', bad=2 from t_paydocs where #check.bad is null and #check.q_type_id=@alloc_id and #check.paymt_id=t_paydocs.id and .PURPOSE_CODE(t_paydocs.purpose_id)!=4011 update #check set mess='Найденный платеж находится в статусе '+t_states.state_name, bad=2 from t_items, t_states where #check.bad is null and #check.q_type_id=@alloc_id and #check.paymt_id=t_items.id and t_items.state_id=t_states.id and upper(t_states.state_code) not in ('ED','WAIT_QUERY','PERF') -- заявки к платежам update t_paydocs set pif_pay_id=#check.claim_id from #check where #check.bad is null and #check.q_type_id=@alloc_id and t_paydocs.id=#check.paymt_id and t_paydocs.pif_pay_id is null update #check set mess='В найденном платеже указана другая заявка', bad=2 from t_paydocs, tp_pif_queries where #check.bad is null and #check.q_type_id=@alloc_id and #check.claim_id=tp_pif_queries.id and #check.paymt_id=t_paydocs.id and t_paydocs.pif_pay_id is not null and t_paydocs.pif_pay_id != tp_pif_queries.id -- проверка привязок -- выкуп и одноразовые на размещение update #check set mess='Найденная заявка уже находится в отчете: док-т № '+ isnull(td_depo_docs.in_no,''), bad=1 from tp_extract2claim, td_depo_docs, tp_pif_queries where #check.bad is null and #check.claim_id=tp_pif_queries.id and tp_pif_queries.id=tp_extract2claim.claim_id and tp_extract2claim.extract_id=td_depo_docs.id and (#check.q_type_id!=@alloc_id or (#check.q_type_id=@alloc_id and tp_pif_queries.pif_pay_type_id=1 and ((tp_extract2claim.extract_id!=@id and @is_cancel=0) or @is_cancel=1))) -- размещение с таким же платежем update #check set mess='Найденная заявка c платежем уже находится в отчете: док-т № '+ isnull(td_depo_docs.in_no,''), bad=1 from tp_extract2claim, td_depo_docs, tp_pif_queries, tp_paymt2pays where #check.bad is null and #check.q_type_id=@alloc_id and #check.claim_id=tp_pif_queries.id and tp_pif_queries.id=tp_extract2claim.claim_id and tp_extract2claim.extract_id=td_depo_docs.id and tp_paymt2pays.pif_pay_id=tp_extract2claim.id and tp_paymt2pays.paymt_id=#check.paymt_id and (tp_pif_queries.pif_pay_type_id=2 or (tp_pif_queries.pif_pay_type_id=1 and tp_extract2claim.extract_id=@id)) -- проверка повторной квитовки (выкуп) update #check set mess='Найденная заявка уже сквитована в этом отчете', bad=1 where #check.bad is null and #check.q_type_id!=@alloc_id and #check.imp_id in (select min(ch.imp_id) from #check ch where ch.bad is null and ch.q_type_id!=@alloc_id group by ch.claim_id having count(*)>1) -- проверка повторной квитовки (размещение) update #check set mess='Найденная заявка уже сквитована в этом отчете', bad=1 where #check.bad is null and #check.q_type_id=@alloc_id and #check.imp_id in (select min(ch.imp_id) from #check ch where ch.bad is null and ch.q_type_id=@alloc_id group by ch.claim_id, ch.paymt_id having count(*)>1) -- обработка сквитованного .ID_VAR(@q_type_id) .ID_VAR(@claim_id) .ID_VAR(@paymt_id) .ID_VAR(@agent_id) .ID_VAR(@payer_id) .ID_VAR(@extract2claim_id) .ID_VAR(@claim_type_id) .ID_VAR(@agent_dog_id) .INT_VAR(@first) declare @imp_sum money, @imp_qty .PIF_PAY_TYPE, @imp_agent_sum money declare @claim_id_s varchar(30), @paymt_id_s varchar(30) declare claims cursor for select #check.q_type_id, #check.claim_id, #check.paymt_id, imp.q_sum, imp.q_qty, imp.agent_sum, tp_pif_queries.agent_id, tp_pif_queries.pay_owner_id, tp_pif_queries.agent_dog_id from #check, tp_pay2rep_imp imp, tp_pif_queries where #check.bad is null and #check.imp_id=imp.id and #check.claim_id=tp_pif_queries.id for read only open claims fetch claims into @q_type_id, @claim_id, @paymt_id, @imp_sum, @imp_qty, @imp_agent_sum, @agent_id, @payer_id, @agent_dog_id while (.CURSOR_STATE = 0) begin select @first=0 .PIF_ROUND_PAYS(@fund_id,@imp_qty,@imp_qty) if @q_type_id=@alloc_id select @claim_type_id=1 else select @claim_type_id=2 select @extract2claim_id=(select id from tp_extract2claim where extract_id=@id and claim_id=@claim_id) if @extract2claim_id is null begin -- привязка заявки if upper(.ITEM_STATE_CODE(@claim_id)) in ('ED','NOT_PAYMT') begin .ITEM_MOVE_STATE(@claim_id,'PROV') end .TYPE_INIT('PIF_PAYS2REPORT',2) .TYPE_ASSIGN(share_qty,@imp_qty,2) .TYPE_ASSIGN(agent_sum,@imp_agent_sum,2) .TYPE_ASSIGN(sum_for_transfer,@imp_sum,2) .TYPE_ASSIGN(claim_id,@claim_id,2) .TYPE_ASSIGN(extract_id,@id,2) .TYPE_ASSIGN(pay_agent_id,@agent_id,2) .TYPE_ASSIGN(pay_partner_id,@payer_id,2) .TYPE_ASSIGN(tax_percent,0,2) .TYPE_ASSIGN(is_new_tax,0,2) .TYPE_ASSIGN(is_manual_tax,0,2) .TYPE_ASSIGN(tax_sum,0,2) .TYPE_ASSIGN(alloc_sum,0,2) .TYPE_ASSIGN(get_paymt_type,2,2) .TYPE_ASSIGN(claim_type_id,@claim_type_id,2) .TYPE_ASSIGN(agent_dog_id,@agent_dog_id,2) .TYPE_INSERT('PIF_PAYS2REPORT','CRT',2) select @extract2claim_id=.TYPE_FIELD(id,2) select @first=1 end if @q_type_id=@alloc_id and (@is_cancel=0 or (@is_cancel=1 and @paymt_id is not null)) begin -- привязка платежа к привязке заявки if upper(.ITEM_STATE_CODE(@paymt_id)) in ('ED','WAIT_QUERY') begin select @paymt_id_s=convert(varchar(30),@paymt_id) if upper(.ITEM_TYPE_CODE(@paymt_id))='SD_PAYBANK_IN' begin if upper(.ITEM_STATE_CODE(@paymt_id))='ED' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYBANK_IN','OPS_IN',Y) end if upper(.ITEM_STATE_CODE(@paymt_id))='WAIT_QUERY' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYBANK_IN','OPS_FOR_QUERY',Y) end end if upper(.ITEM_TYPE_CODE(@paymt_id))='SD_PAYDOCS_CASH_IN' begin .EXEC_ACTION(@paymt_id_s,'SD_PAYDOCS_CASH_IN','OPS_CASH_IN',Y) end end .TYPE_INIT('PIF_PAYMT2PAYS',3) .TYPE_ASSIGN(pif_pay_id,@extract2claim_id,3) .TYPE_ASSIGN(paymt_id,@paymt_id,3) .TYPE_INSERT('PIF_PAYMT2PAYS','CRT',3) if @first=0 update tp_extract2claim set sum_for_transfer=sum_for_transfer+@imp_sum, share_qty=share_qty+@imp_qty, agent_sum=agent_sum+@imp_agent_sum where id=@extract2claim_id end if @is_cancel=0 begin select @claim_id_s=convert(varchar(30),@extract2claim_id) .EXEC_ACTION(@claim_id_s,'PIF_PAYS2REPORT','claim_calc_parts',Y) end fetch claims into @q_type_id, @claim_id, @paymt_id, @imp_sum, @imp_qty, @imp_agent_sum, @agent_id, @payer_id, @agent_dog_id end close claims .DEALLOCATE claims .ID_VAR(@imp_type_id) .ID_VAR(@fix_id) .ID_VAR(@not1_id) .ID_VAR(@not2_id) select @imp_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY2REP_IMP') select @fix_id=.STATE_ID_FROM_CODE(@imp_type_id,'FIX') select @not1_id=.STATE_ID_FROM_CODE(@imp_type_id,'NOT_CLAIM') select @not2_id=.STATE_ID_FROM_CODE(@imp_type_id,'NOT_PAYMT') update t_items set state_id=@fix_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad is null update t_items set state_id=@not1_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad=1 update t_items set state_id=@not2_id from tp_pay2rep_imp, #check where tp_pay2rep_imp.id=t_items.id and t_items.type_id=@imp_type_id and tp_pay2rep_imp.extract_id=@id and tp_pay2rep_imp.id=#check.imp_id and #check.bad=2 update tp_pay2rep_imp set cause=#check.mess from #check where #check.imp_id=tp_pay2rep_imp.id if exists(select 1 from #check where bad is not null) select @res=0 commit tran end drop table #check .TRANSACTION_RESTORE begin tran if @res=1 begin .ITEM_MOVE_STATE(@id,'END_CHECK') end else begin .ITEM_MOVE_STATE(@id,'NOT_CHECK') end commit tran $ENDTEXT(17224979) $ENDACTION $ACTION(SBRF_REESTR_IMPORT) name=Импорт полученных и принятых заявок (СБРФ) class=1 form=SBRF_REESTR_IMPORT_LIST target_state=null procedure=ap_30000000000012479 patterns=null user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224979) create procedure dbo.%PROC% @req varchar(255), -- № заявки, @reqdate varchar(255), -- дата заявки @reqtype varchar(255), -- тип заявки (операции), @reqstatus varchar(255), -- статус заявки, @operdate varchar(255), -- дата операции, @opertype varchar(255), -- 1-приход, -1-расход (для заявок на обмен паев) @dname varchar(255), -- пайщик (ФИО/Наименование) @dcclienttype varchar(255), -- тип лица @dpdoctype varchar(255), -- тип документа @dpdocseries varchar(255), -- док.-серия (физ.) @dpdocnumber varchar(255), -- док.-номер (физ.) @dpdocdep varchar(255), -- док. - кем выдан (физ.) @dpdocplace varchar(255), -- док. - где выдан (физ.) @dpdocdate varchar(255), -- док. - дата выдачи (физ.) @dudocnumber varchar(255), -- св-во - номер (юр.) @dudocdep varchar(255), -- св-во - кем выдано (юр.) @dudocdate varchar(255), -- св-во - дата выдачи (юр.) @dinn varchar(255), -- ИНН @dbankname varchar(255), -- наименование банка @dpersonalaccount varchar(255), -- № банковского счета (физ.) @daccount varchar(255), -- № банковского счета (юр.) @dbankcorr varchar(255), -- корр. счет @dbic varchar(255), -- БИК @dbankcity varchar(255), -- город банка @dbankinn varchar(255), -- ИНН банка @dpostindex varchar(255), -- почтовый индекс @dpostaddress varchar(255), -- почтовый адрес @dbaseindex varchar(255), -- индекс регистрации @dbaseaddress varchar(255), -- адрес регистрации @dphone varchar(255), -- телефон @dfax varchar(255), -- факс @demail varchar(255), -- email @dokpo varchar(255), -- ОКПО @dokonh varchar(255), -- ОКОНХ @dkpp varchar(255), -- КПП @daccname varchar(255), -- № счета в реестре @cname varchar(255), -- УК (наименование) @cinn varchar(255), -- ИНН @cbankname varchar(255), -- наименование банка @cpersonalaccount varchar(255), -- № банковского счета (физ.) ??????????????? @caccount varchar(255), -- № банковского счета (юр.) @cbankcorr varchar(255), -- корр. счет @cbic varchar(255), -- БИК @cbankcity varchar(255), -- город банка @cbankinn varchar(255), -- ИНН банка @cpostindex varchar(255), -- почтовый индекс @cpostaddress varchar(255), -- почтовый адрес @cbaseindex varchar(255), -- индекс регистрации @cbaseaddress varchar(255), -- адрес регистрации @cphone varchar(255), -- телефон @cfax varchar(255), -- факс @cemail varchar(255), -- email @cokpo varchar(255), -- ОКПО @cokonh varchar(255), -- ОКОНХ @ckpp varchar(255), -- КПП @cdoc varchar(255), -- документ @sharename varchar(255), -- фонд @agentname varchar(255), -- агент - пока =фонду @saldo decimal(18,8), -- кол-во паев @pnum varchar(255), -- № платежа @pdate varchar(255), -- дата платежа @ppremiumagent money, -- сумма комиссии (премия агенту) @ppayer varchar(255), -- плательщик @psumma money, -- сумма платежа @report_id_s varchar(30), -- отчет регистратора о принятых заявках @cancel_report_id_s varchar(30) -- отчет регистратора об отказах as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric begin select @action_id = %ACTION_ID% --**********Проверка заполнения полей файла************************* if @reqtype is null .EXIT_MESSAGE('Не указан тип заявки') declare @req_date datetime select @req_date = convert(datetime, @reqdate) if @req_date is null .EXIT_MESSAGE('Не указана дата заявки') if @req is null .EXIT_MESSAGE('Не указан номер заявки') --**********КОНЕЦ - Проверка заполнения полей файла************************* --**********************Определение ФОНДа и договора с агентом************************* -- ФОНД declare @fund_id numeric, @fund_code varchar(50) --для классификатора PARTNER_IMPORT_CODE - PAYER_ACC_NO select @fund_id = max(tp.id) from t_partners tp where ltrim(rtrim(upper(tp.partner_name))) = ltrim(rtrim(upper(@sharename))) and .ITEMS_EXISTS_BY_TYPE(tp.id,'PIF_FUND') if @fund_id is null .EXIT_MESSAGE('Фонд не определен') select @fund_code=partner_code from t_partners where id=@fund_id --Агент - всегда одно лицо -- declare @agent_id numeric declare @agent_dog_id numeric /* -- АГЕНТ - константа?! select @agent_id = max(tp.id) from t_partners tp where ltrim(rtrim(upper(tp.partner_name))) = ltrim(rtrim(upper(@agentname))) and .ITEMS_EXISTS_BY_TYPE(tp.id,'PARTNERS_JUR') if @agent_id is null .EXIT_MESSAGE('Не найден АГЕНТ!!!') */ --Только 1 договор комиссии (и соотв-но, агент) для каждого фонда select @agent_dog_id = max(tb_contracts.id) from tb_contracts, td_depo_docs where --tb_contracts.client_id = @agent_id and tb_contracts.dog_type_id = 2 and tb_contracts.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') if @agent_dog_id is null .EXIT_MESSAGE('Для фонда не задан договор комиссии') select @agent_id = client_id from tb_contracts where id=@agent_dog_id if @agent_id is null .EXIT_MESSAGE('Не найден АГЕНТ!!!') declare @agent varchar(255) select @agent=partner_name from t_partners where id=@agent_id --**********************КОНЕЦ - Определение ФОНДа и договора с агентом************************* --********************Импорт заявок************************************ declare @folder_id numeric select @folder_id = .DEPO_FOLDER_DEFAULT declare @req_type int select @req_type= --Тип заявки case when charindex('ПРИОБРЕТЕН', upper(@reqtype))>0 then 1 when charindex('ВЫКУП', upper(@reqtype))>0 then 2 when charindex('ОБМЕН', upper(@reqtype))>0 and convert(int,ltrim(rtrim(@opertype)))=1 then 3 when charindex('ОБМЕН', upper(@reqtype))>0 and convert(int,ltrim(rtrim(@opertype)))=-1 then 4 end --*******************НОВЫЕ заявки************************************ if charindex('НОВ', upper(@reqstatus))>0 begin declare @is_jur int select @is_jur=null --**********************Импорт пайщика************************* declare @partner_id numeric, @doc_type_id numeric, @i1 int, @i2 int, @last_name varchar(255), @first_name varchar(255), @second_name varchar(255), @partner_code varchar(50) if charindex('ФИ', upper(@dcclienttype))>0 or charindex('ПБОЮЛ', upper(@dcclienttype))>0 --ФИЗ.ЛИЦО или ПБОЮЛ begin if isnull(replace(@dpdocnumber,' ',''),'')='' and isnull(replace(@dpdocseries,' ',''),'')='' .EXIT_MESSAGE('Не заданы паспортные данные физ.лица') select @is_jur=0 --Пробуем найти пайщика select @partner_id = t_partners.id from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(upper(replace(f.f_passport_serial,' ','')),'') + isnull(upper(replace(f.f_passport_no,' ','')),'')) = (isnull(upper(replace(@dpdocseries,' ','')),'') + isnull(upper(replace(@dpdocnumber,' ','')),'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'partners_fis') end else if charindex('ЮР', upper(@dcclienttype))>0 begin select @is_jur=1 select @partner_id = t_partners.id from t_partners, t_partners_jur where t_partners.id = t_partners_jur.id and ltrim(upper(t_partners.partner_name)) = ltrim(upper(@dname)) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'partners_jur') end else .EXIT_MESSAGE('Тип лица не содержит слов фи/юр/пбоюл') --**************СОЧЕТАНИЕ реквизиты пайщика - Лиц. счет*************** declare @count int if @partner_id is null --если не нашли по документу или наименованию begin --Сколько партнеров с таким ЛС и фондом select @count=count(t_partners.id) from t_partners, t_item2class i, t_classvalues v, t_classes c where t_partners.id=i.item_id and i.value_id=v.id and v.class_id=c.id and upper(c.code)='PARTNER_IMPORT_CODE' and upper(v.code)='PAYER_ACC_NO' and upper(i.code)=upper(@daccname) and upper(i.remark)=upper(@fund_code) if @count=1 --один и тот самый begin select @partner_id=t_partners.id from t_partners, t_item2class i, t_classvalues v, t_classes c where t_partners.id=i.item_id and i.value_id=v.id and v.class_id=c.id and upper(c.code)='PARTNER_IMPORT_CODE' and upper(v.code)='PAYER_ACC_NO' and upper(i.code)=upper(@daccname) and upper(i.remark)=upper(@fund_code) --update реквизитов пайщика------------------------------------------------------- /*if @is_jur=0... if @is_jur=1... .TYPE_INIT('PARTNERS_JUR',5) .TYPE_ASSIGN(partner_name,@dname,5) .TYPE_UPDATE(@partner_id,'PARTNERS_JUR',5)*/ ---------------------------------------------------------------------------------- end if @count>1 --несколько с одинаковыми ЛС!!! .EXIT_MESSAGE('Существует несколько лиц с одинаковым сочетанием ЛС-Фонд в классификаторе PAYER_ACC_NO!') --if isnull(@count,0)=0 --нет ни одного => @partner_id=null end --**************КОНЕЦ - СОЧЕТАНИЕ реквизиты пайщика - Лиц. счет*************** --Если не нашли if @partner_id is null begin if @is_jur=0 begin --**********Новый пайщик-физик или ПБОЮЛ******************* select @doc_type_id= case when @dpdoctype='Паспорт РФ' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Паспорт РФ')))) when @dpdoctype='Воен бл' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Военный билет')))) when @dpdoctype='Удостоверение личности' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Удостоверение личности')))) when @dpdoctype='Национальный заграничный паспорт' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Национальный заграничный паспорт')))) when @dpdoctype='Паспорт моряка' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Паспорт моряка')))) when @dpdoctype='Вид на жительство' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Вид на жительство')))) when @dpdoctype='Удостоверение беженца в РФ' then (select id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper('Удостоверение беженца в РФ')))) end if @doc_type_id is null .EXIT_MESSAGE('Не найден тип документа у физического лица') --определяем фам., имя, отчество select @i1=charindex(' ',@dname,1) select @last_name=substring(@dname,1,@i1-1) select @i2=charindex(' ',@dname,@i1+1) select @first_name=substring(@dname,@i1+1,@i2-@i1-1) select @second_name=substring(@dname,@i2+1,.CHAR_LENGTH(@dname)-@i2) --Новый пайщик .TYPE_INIT('PARTNERS_FIS',3) .TYPE_ASSIGN(f_doctype_id,@doc_type_id,3) .TYPE_ASSIGN(f_passport_no,@dpdocnumber,3) .TYPE_ASSIGN(f_passport_serial,@dpdocseries,3) .TYPE_ASSIGN(f_passport_distributor,(@dpdocdep+' '+@dpdocplace),3) .TYPE_ASSIGN(f_passport_when,@dpdocdate,3) .TYPE_ASSIGN(f_first_name,@first_name,3) .TYPE_ASSIGN(f_middle_name,@second_name,3) .TYPE_ASSIGN(f_last_name,@last_name,3) .TYPE_ASSIGN(partner_name,@dname,3) .TYPE_ASSIGN(partner_short_name,(@last_name+' '+isnull(substring(@first_name,1,1)+'.','')+' '+isnull(substring(@second_name,1,1)+'.','')),3) select @partner_code='IMPORT'+'(' + @last_name+' '+isnull(@dpdocnumber, '')+')' .TYPE_ASSIGN(partner_code,@partner_code,3) .TYPE_ASSIGN(j_inn,@dinn,3) .TYPE_ASSIGN(tax_category_id,1,3) .TYPE_ASSIGN(postal_code,@dpostindex,3) .TYPE_ASSIGN(partner_post_address,@dpostaddress,3) .TYPE_ASSIGN(real_code,@dbaseindex,3) .TYPE_ASSIGN(partner_address,@dbaseaddress,3) .TYPE_ASSIGN(jur_code,@dbaseindex,3) .TYPE_ASSIGN(juraddress,@dbaseaddress,3) .TYPE_ASSIGN(partner_phone,(@dphone+' факс: '+@dfax),3) .TYPE_ASSIGN(partner_email,@demail,3) .TYPE_ASSIGN(okpo_code,@dokpo,3) .TYPE_ASSIGN(okonh_code,@dokonh,3) if charindex('ПБОЮЛ', upper(@dcclienttype))>0 begin .TYPE_ASSIGN(regist_sertificate_no,@dudocnumber,3) .TYPE_ASSIGN(regist_sertificate_date,@dudocdate,3) .TYPE_ASSIGN(regist_organisation,@dudocdep,3) end .TYPE_INSERT('PARTNERS_FIS','CRT',3) select @partner_id = @id3_1 --***************КОНЕЦ - Новый пайщик-физик или ПБОЮЛ************************ end if @is_jur=1 begin --***************Новый пайщик-юрик******************************************* .TYPE_INIT('PARTNERS_JUR',4) .TYPE_ASSIGN(partner_name,@dname,4) .TYPE_ASSIGN(partner_short_name,@dname,4) select @partner_code='IMPORT'+'(' + @dname+' '+isnull(@dinn, '')+')' .TYPE_ASSIGN(partner_code,@partner_code,4) .TYPE_ASSIGN(j_inn,@dinn,4) .TYPE_ASSIGN(tax_category_id,6,4) .TYPE_ASSIGN(postal_code,@dpostindex,4) .TYPE_ASSIGN(partner_post_address,@dpostaddress,4) .TYPE_ASSIGN(real_code,@dbaseindex,4) .TYPE_ASSIGN(partner_address,@dbaseaddress,4) .TYPE_ASSIGN(jur_code,@dbaseindex,4) .TYPE_ASSIGN(juraddress,@dbaseaddress,4) .TYPE_ASSIGN(partner_phone,(@dphone+' факс: '+@dfax),4) .TYPE_ASSIGN(partner_email,@demail,4) .TYPE_ASSIGN(okpo_code,@dokpo,4) .TYPE_ASSIGN(okonh_code,@dokonh,4) .TYPE_ASSIGN(cro,@dkpp,4) .TYPE_ASSIGN(regist_sertificate_no,@dudocnumber,4) .TYPE_ASSIGN(regist_sertificate_date,@dudocdate,4) .TYPE_ASSIGN(regist_organisation,@dudocdep,4) .TYPE_INSERT('PARTNERS_JUR','CRT',4) select @partner_id = @id4_1 --***************КОНЕЦ - Новый пайщик-юрик******************************************** end --лицевой счет пайщика в фонде - в классификатор .SET_CLASS_VALUES(@partner_id,'PARTNER_IMPORT_CODE','PAYER_ACC_NO',@daccname,100) --вставляем классификатор для пайщика --код фонда - в комментарий классификатора update t_item2class set remark=@fund_code from t_classvalues v, t_classes c where t_item2class.item_id=@partner_id and t_item2class.value_id=v.id and v.class_id=c.id and upper(c.code)='PARTNER_IMPORT_CODE' and upper(v.code)='PAYER_ACC_NO' and upper(t_item2class.code)=upper(@daccname) end --********************Импорт банковских реквизитов ****************** declare @partner_bank_acc_id numeric if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_FIS' select @partner_bank_acc_id = t_bank_accounts.id from t_partners, t_bank_accounts where t_partners.id = @partner_id and ltrim(upper(t_bank_accounts.bank_account_no)) = ltrim(upper(@dpersonalaccount)) and .ITEMS_EXISTS_BY_TYPE(t_bank_accounts.id, 'BANK_ACCOUNTS') if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_JUR' select @partner_bank_acc_id = t_bank_accounts.id from t_partners, t_bank_accounts where t_partners.id = @partner_id and ltrim(upper(t_bank_accounts.bank_account_no)) = ltrim(upper(@daccount)) and .ITEMS_EXISTS_BY_TYPE(t_bank_accounts.id, 'BANK_ACCOUNTS') if @partner_bank_acc_id is null begin .TYPE_INIT('BANK_ACCOUNTS',11) .TYPE_ASSIGN(partner_id,@partner_id,11) .TYPE_ASSIGN(corr_account,@dbankcorr,11) .TYPE_ASSIGN(bik,@dbic,11) .TYPE_ASSIGN(bank_name,@dbankname,11) .TYPE_ASSIGN(bank_city_name,@dbankcity,11) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_FIS' .TYPE_ASSIGN(bank_account_no,@dpersonalaccount,11) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_JUR' .TYPE_ASSIGN(bank_account_no,@daccount,11) .TYPE_INSERT('BANK_ACCOUNTS','CRT',11) end --******************** КОНЕЦ - Импорт банковских реквизитов ****************** --**********************КОНЕЦ - Импорт пайщика************************* --**********************НОВАЯ ЗАЯВКА*********************************** if @req_type in (1,3) begin -- Заявка на размещение (зачисление по обмену) паев - новая if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@req) and tp_pif_queries.agent_regs_date = @req_date and tp_pif_queries.id = td_depo_docs.id and tp_pif_queries.type_queries=@req_type and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_ALLOCATE')) .EXIT_MESSAGE('Заявка с такими реквизитами уже существует') .TYPE_INIT('PIF_PAY_ALLOCATE',12) .TYPE_ASSIGN(depo_doc_type,1,12) .DEPO_GEN_NO(@in_no12_1,@depo_doc_type12_1) .TYPE_ASSIGN(folder_id,@folder_id,12) .TYPE_ASSIGN(pif_pay_type_id,2,12) .TYPE_ASSIGN(agent_regs_date, @req_date, 12) .TYPE_ASSIGN(agent_regs_no, @req, 12) .TYPE_ASSIGN(pay_owner_id, @partner_id, 12) .TYPE_ASSIGN(agent_id, @agent_id, 12) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 12) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_FIS' .TYPE_ASSIGN(bank_account_no,@dpersonalaccount,12) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_JUR' .TYPE_ASSIGN(bank_account_no,@daccount,12) .TYPE_ASSIGN(bank_name, @dbankname, 12) .TYPE_ASSIGN(bik, @dbic, 12) .TYPE_ASSIGN(corr_account, @dbankcorr, 12) .TYPE_ASSIGN(bank_city_name, @dbankcity, 12) .TYPE_ASSIGN(type_queries, @req_type, 12) .TYPE_INSERT('PIF_PAY_ALLOCATE','ED',12) --или 'PROV' end else if @req_type in (2,4) begin -- Заявка на выкуп (списание по обмену) паев - новая if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@req) and tp_pif_queries.agent_regs_date = @req_date and tp_pif_queries.id = td_depo_docs.id and tp_pif_queries.type_queries=@req_type and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_BUY')) .EXIT_MESSAGE('Заявка с такими реквизитами уже существует') .TYPE_INIT('PIF_PAY_BUY',22) .TYPE_ASSIGN(depo_doc_type,1,22) .DEPO_GEN_NO(@in_no22_1,@depo_doc_type22_1) .TYPE_ASSIGN(folder_id,@folder_id,22) .TYPE_ASSIGN(pif_pay_type_id,1,22) if @saldo is null .EXIT_MESSAGE('В заявке на погашение не указано количество паев') .TYPE_ASSIGN(stock_qty, @saldo, 22) .TYPE_ASSIGN(agent_regs_date, @req_date, 22) .TYPE_ASSIGN(agent_regs_no, @req, 22) .TYPE_ASSIGN(pay_owner_id, @partner_id, 22) .TYPE_ASSIGN(agent_id, @agent_id, 22) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 22) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_FIS' .TYPE_ASSIGN(bank_account_no,@dpersonalaccount,22) if .ITEM_TYPE_CODE(@partner_id)='PARTNERS_JUR' .TYPE_ASSIGN(bank_account_no,@daccount,22) .TYPE_ASSIGN(bank_name, @dbankname, 22) .TYPE_ASSIGN(bik, @dbic, 22) .TYPE_ASSIGN(corr_account, @dbankcorr, 22) .TYPE_ASSIGN(bank_city_name, @dbankcity, 22) .TYPE_ASSIGN(type_queries, @req_type, 22) .TYPE_INSERT('PIF_PAY_BUY','ED',22) --или 'PROV' end else .EXIT_MESSAGE('Тип заявки не содержит слов Приобретение/выкуп/обмен') end --***********************КОНЕЦ - НОВЫЕ заявки************************* --if @partner_id is null .EXIT_MESSAGE('Пайщик не найден') --*******************ИСПОЛНЕННЫЕ заявки************************************ if @pdate='' select @pdate=null declare @p_date datetime select @p_date=convert(datetime,@pdate) .ID_VAR(@q_type_id) .INT_VAR(@is_cancel) --**********Проверка выбранных Отчетов регистратора************************* if @report_id_s='' select @report_id_s=null if @cancel_report_id_s='' select @cancel_report_id_s=null declare @report_id numeric, @cancel_report_id numeric select @report_id=convert(numeric,@report_id_s), @cancel_report_id=convert(numeric,@cancel_report_id_s) --**********КОНЕЦ - Проверка выбранных Отчетов регистратора************************* if charindex('ИСПОЛН', upper(@reqstatus))>0 begin if @report_id is null .EXIT_MESSAGE('Не задан отчет регистратора о принятых заявках!') if .ITEM_TYPE_CODE(@report_id)!='PIF_PAY_REPORT' .EXIT_MESSAGE('Выбранный документ не является Отчетом регистратора о принятых зявках!') select @is_cancel=0 if @req_type in (1,3) select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') if @req_type in (2,4) select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_BUY') if @q_type_id is null .EXIT_MESSAGE('Не определен тип исполненной заявки') if @saldo is null or @psumma is null .EXIT_MESSAGE('Не указана сумма или кол-во паев') if @req_type in (1,3) and (@p_date is null or @pnum is null) and @is_cancel=0 .EXIT_MESSAGE('Не указана дата или номер платежа для заявки на выдачу') if exists(select 1 from tp_pay2rep_imp where q_no=@req and q_date=@req_date and q_type_id=@q_type_id and @req_type in (1,3) and p_no=@pnum and p_date=@p_date) .EXIT_MESSAGE('Заявка уже была импортирована') begin tran .TYPE_INIT('PIF_PAY2REP_IMP',31) .TYPE_ASSIGN(extract_id,@report_id,31) .TYPE_ASSIGN(agent,@agent,31) .TYPE_ASSIGN(payer,@dname,31) .TYPE_ASSIGN(acc_no,@daccname,31) .TYPE_ASSIGN(q_type_id,@q_type_id,31) .TYPE_ASSIGN(q_date,@req_date,31) .TYPE_ASSIGN(q_no,@req,31) .TYPE_ASSIGN(p_date,@p_date,31) .TYPE_ASSIGN(p_no,@pnum,31) .TYPE_ASSIGN(q_sum,@psumma,31) .TYPE_ASSIGN(q_qty,@saldo,31) .TYPE_ASSIGN(agent_sum,@ppremiumagent,31) .TYPE_INSERT('PIF_PAY2REP_IMP','WAIT',31) if upper(.ITEM_STATE_CODE(@report_id))='ED_CLAIM' begin .ITEM_MOVE_STATE(@report_id,'WAIT_CHECK') end commit tran end --*******************КОНЕЦ - ИСПОЛНЕННЫЕ заявки***************************** --*******************ОТКАЗАННЫЕ заявки***************************** if charindex('ЗАПРЕЩ', upper(@reqstatus))>0 begin if @cancel_report_id is null .EXIT_MESSAGE('Не задан отчет регистратора об отказах!') if .ITEM_TYPE_CODE(@cancel_report_id)!='PIF_PAY_CANCEL' .EXIT_MESSAGE('Выбранный документ не является Отчетом регистратора об отказах!') select @is_cancel=1 if @req_type in (1,3) select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_ALLOCATE') if @req_type in (2,4) select @q_type_id=.TYPE_ID_FROM_CODE_FUNC('PIF_PAY_BUY') if @q_type_id is null .EXIT_MESSAGE('Не определен тип отказанной заявки') if exists(select 1 from tp_pay2rep_imp where q_no=@req and q_date=@req_date and q_type_id=@q_type_id) .EXIT_MESSAGE('Заявка уже была импортирована') begin tran .TYPE_INIT('PIF_PAY2REP_IMP',32) .TYPE_ASSIGN(extract_id,@cancel_report_id,32) .TYPE_ASSIGN(agent,@agent,32) .TYPE_ASSIGN(payer,@dname,32) .TYPE_ASSIGN(acc_no,@daccname,32) .TYPE_ASSIGN(q_type_id,@q_type_id,32) .TYPE_ASSIGN(q_date,@req_date,32) .TYPE_ASSIGN(q_no,@req,32) .TYPE_ASSIGN(p_date,@p_date,32) .TYPE_ASSIGN(p_no,@pnum,32) .TYPE_ASSIGN(q_sum,@psumma,32) .TYPE_ASSIGN(q_qty,@saldo,32) .TYPE_ASSIGN(agent_sum,@ppremiumagent,32) .TYPE_INSERT('PIF_PAY2REP_IMP','WAIT',32) if upper(.ITEM_STATE_CODE(@cancel_report_id))='ED_CLAIM' begin .ITEM_MOVE_STATE(@cancel_report_id,'WAIT_CHECK') end commit tran end --*******************КОНЕЦ - ОТКАЗАННЫЕ заявки***************************** --***************Конец - Импорт заявок*********** select sid = convert(varchar, @id) end $ENDTEXT(17224979) $ENDACTION $STATE2ACTION(CRT.ACTION_IMPORT) state=CRT action=ACTION_IMPORT $ENDSTATE2ACTION $STATE2ACTION(CRT.ACTION_INSERT) state=CRT action=ACTION_INSERT $ENDSTATE2ACTION $STATE2ACTION(CRT.ED_PIF_PAY_REP_LIST) state=CRT action=ED_PIF_PAY_REP_LIST $ENDSTATE2ACTION $STATE2ACTION(CRT.FORM2) state=CRT action=FORM2 $ENDSTATE2ACTION $STATE2ACTION(CRT.PIF_PAY_REPS_LIST) state=CRT action=PIF_PAY_REPS_LIST $ENDSTATE2ACTION $STATE2ACTION(CRT.SBRF_REESTR_IMPORT) state=CRT action=SBRF_REESTR_IMPORT $ENDSTATE2ACTION $ENDDOC $DOC(PIF_PAY_GROUP) name=Группа для заявок (для отчета регистратора) class=5 product=ALD_PIF name_formula=null remark_formula=null $STATE(СОЗДАН) name=Создан class=1 $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=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(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=1 init_formula=.OPERDAY unique_index=1 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=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(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=1 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=1 init_formula=1 unique_index=null numerator=null import_type=null import_table=null import_field= 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=1 init_formula=1 unique_index=1 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=2 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=1 init_formula=.CUR("RUR") 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=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(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=null init_formula=null unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(OWNER_ID) name=Владелец field_name=owner_id table_name=td_depo_docs field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=17 init_class=1 init_formula=.ORGANIZATION_PIF unique_index=null numerator=null import_type=null import_table=null import_field=null import_where=null ref_table=null ref_key=null ref_display=null ref_alias=null ref_where=null $ENDPROP $PROP(PAY_OWNER_ID) name=Пайщик field_name=pay_owner_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=21 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(STOCK_QTY) name=Количество паев field_name=stock_qty table_name=tp_pif_queries field_type=decimal field_length=9 field_scale=8 field_prec=18 page=2 order=28 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(TOTAL_SUM) name=Сумма без комиссии field_name=total_sum table_name=tp_pif_queries field_type=money field_length=8 field_scale=4 field_prec=19 page=2 order=34 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(SUBACC_ID) name=Раздел счета пайщика в реестре field_name=subacc_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=38 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(AGENT_ID) name=Агент field_name=agent_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=18 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(AGENT_REGS_DATE) name=Дата регистрации заявки field_name=agent_regs_date table_name=tp_pif_queries field_type=datetime field_length=8 field_scale=3 field_prec=23 page=2 order=19 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(AGENT_REGS_NO) name=Номер заявки у агента field_name=agent_regs_no table_name=tp_pif_queries field_type=varchar field_length=50 field_scale=null field_prec=50 page=2 order=20 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(PAY_TYPE_ID) name=Тип расчета и проведения field_name=pay_type_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=22 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 $PROP(PAYDOC_DATE) name=Дата платежного документа field_name=paydoc_date table_name=tp_pif_queries field_type=datetime field_length=8 field_scale=3 field_prec=23 page=2 order=23 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(PAYDOC_NO) name=Номер платежного документа field_name=paydoc_no table_name=tp_pif_queries field_type=varchar field_length=60 field_scale=null field_prec=60 page=2 order=24 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(CALC_DATE) name=Дата расчета field_name=calc_date table_name=tp_pif_queries field_type=datetime field_length=8 field_scale=3 field_prec=23 page=2 order=25 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(STOCK_PRICE) name=Цена пая field_name=stock_price table_name=tp_pif_queries field_type=decimal field_length=9 field_scale=8 field_prec=18 page=2 order=26 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(STOCK_PRICE_CUR_ID) name=Валюта цены пая field_name=stock_price_cur_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=27 init_class=1 init_formula=.CUR('RUR') 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(PAYMENT_SUM) name=Сумма оплаты field_name=payment_sum table_name=tp_pif_queries field_type=money field_length=8 field_scale=4 field_prec=19 page=2 order=29 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(AGENT_COMIS_SUM) name=Комиссия агента field_name=agent_comis_sum table_name=tp_pif_queries field_type=money field_length=8 field_scale=4 field_prec=19 page=2 order=30 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(TAX) name=Налог field_name=tax table_name=tp_pif_queries field_type=money field_length=8 field_scale=4 field_prec=19 page=2 order=31 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(REESTR_ACC_NO) name=Номер счета в реестре field_name=reestr_acc_no table_name=tp_pif_queries field_type=varchar field_length=100 field_scale=null field_prec=100 page=2 order=32 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(TYPE_QUERIES) name=Тип заявки (1 - выдача, 2 - погашение, 3 - зачисление по обмену, 4 - списание по обмену) field_name=type_queries table_name=tp_pif_queries field_type=int field_length=4 field_scale=0 field_prec=10 page=2 order=33 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(AGENT_DOG_ID) name=Агентский договор field_name=agent_dog_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=35 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(REESTR_REPORT_ID) name=Отчет о сверке из реестра field_name=reestr_report_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=36 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(PIF_PAY_TYPE_ID) name=Тип заявки (1-однораз, 2-многораз) field_name=pif_pay_type_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=37 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 $PROP(FUND4CHANGE) name=Фонд для обмена field_name=fund4change table_name=tp_pif_queries field_type=varchar field_length=255 field_scale=null field_prec=255 page=2 order=39 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(QUERIES4CHANGE_ID) name=Заявка на списание по обмену field_name=queries4change_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=40 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 $RELATION(REL7) class=0 type=SIMPLES form=null prop=PIF_PAY_TYPE_ID idname=null order=5 $ENDRELATION $RELATION(REL2) class=0 type=PARTNERS form=null prop=AGENT_ID idname=null order=1 $ENDRELATION $RELATION(REL3) class=0 type=PARTNERS form=null prop=PAY_OWNER_ID idname=null order=2 $ENDRELATION $RELATION(REL10) class=0 type=ITEMS form=null prop=QUERIES4CHANGE_ID idname=null order=8 $ENDRELATION $RELATION(REL8) class=0 type=PIF_FUND form=null prop=OWNER_ID idname=null order=7 $ENDRELATION $RELATION(REL5) class=0 type=FUND_CONTRACT_GROUP form=null prop=AGENT_DOG_ID idname=null order=3 $ENDRELATION $RELATION(REL9) class=0 type=SREG_SUBACC form=null prop=SUBACC_ID idname=null order=6 $ENDRELATION $RELATION(REL4) class=5 type=PIF_PAY_BUY form=FORM_VIEW prop=null idname=null order=null $ENDRELATION $RELATION(REL1) class=5 type=PIF_PAY_ALLOCATE form=FORM_VIEW prop=null idname=null order=null $ENDRELATION $RELATION(REL6) class=0 type=PIF_PAY_REPORT form=null prop=REESTR_REPORT_ID idname=null order=4 $ENDRELATION $FORM(D_PIF_PAYS4CHANGE_LIST) name=Список заявок списание по обмену class=4 filter=null target_state= uo=uo_list dw=d_pif_pays_list procedure=ap_50000000000374181 patterns=null user_define=1 isvisible=1 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(17224985) create procedure dbo.%PROC% @sid varchar(30) = null, @cl_id_s varchar(30) = null, @pay_owner_id_s varchar(30) = null as declare @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric declare @owner_id numeric, @cl_id numeric, @pay_owner_id numeric begin set nocount on select @action_id = %ACTION_ID% select @cl_id = convert(numeric,@cl_id_s) select @pay_owner_id = convert(numeric,@pay_owner_id_s) select @owner_id = (select owner_id from td_depo_docs where id = @cl_id) select sid = convert(varchar, t_items.id), state_sid = convert(varchar, t_items.state_id), type_sid = convert(varchar, t_items.type_id), t_items.item_name , t_items.item_remark , state_name=(select t_states.state_name from t_states where t_states.id=t_items.state_id), type_name=(select t_types.type_name from t_types where t_types.id=t_items.type_id), td_depo_docs.out_date as out_date_d, td_depo_docs.in_date as in_date_d, td_depo_docs.who_get, td_depo_docs.who_put, td_depo_docs.out_no, td_depo_docs.in_no, convert(varchar,td_depo_docs.correspondent_id) as correspondent_id_s, convert(varchar,td_depo_docs.payer_id) as payer_id_s, convert(decimal(18,4),td_depo_docs.pay_sum) as pay_sum, convert(varchar,td_depo_docs.initiator_id) as initiator_id_s, convert(varchar,td_depo_docs.document_form_id) as document_form_id_s, td_depo_docs.depo_doc_type, convert(varchar,td_depo_docs.folder_id) as folder_id_s, convert(varchar,td_depo_docs.sum_cur_id) as sum_cur_id_s, td_depo_docs.doc_comment, convert(varchar,td_depo_docs.foundation_id) as foundation_id_s, convert(varchar,td_depo_docs.owner_id) as owner_id_s, convert(varchar,tp_pif_queries.agent_id) as agent_id_s, tp_pif_queries.agent_regs_date as agent_regs_date_d, tp_pif_queries.agent_regs_no, convert(varchar,tp_pif_queries.pay_owner_id) as pay_owner_id_s, convert(varchar,tp_pif_queries.pay_type_id) as pay_type_id_s, tp_pif_queries.paydoc_date as paydoc_date_d, tp_pif_queries.paydoc_no, tp_pif_queries.calc_date as calc_date_d, convert(decimal(18,4),tp_pif_queries.stock_price) as stock_price, convert(varchar,tp_pif_queries.stock_price_cur_id) as stock_price_cur_id_s, tp_pif_queries.stock_qty, convert(decimal(18,4),tp_pif_queries.payment_sum) as payment_sum, convert(decimal(18,4),tp_pif_queries.agent_comis_sum) as agent_comis_sum, convert(decimal(18,4),tp_pif_queries.tax) as tax, tp_pif_queries.reestr_acc_no, tp_pif_queries.type_queries, convert(decimal(18,4),tp_pif_queries.total_sum) as total_sum, convert(varchar,tp_pif_queries.agent_dog_id) as agent_dog_id_s, convert(varchar,tp_pif_queries.reestr_report_id) as reestr_report_id_s, convert(varchar,tp_pif_queries.pif_pay_type_id) as pif_pay_type_id_s, convert(varchar,tp_pif_queries.subacc_id) as subacc_id_s, tp_pif_queries.fund4change, convert(varchar,tp_pif_queries.queries4change_id) as queries4change_id_s, r1861581670_10000000000089240.partner_short_name as agent_name, r1861581670_10000000000089243.j_inn as j_inn, r1861581670_10000000000089243.partner_name as partner_name, r1861581670_10000000000089243.partner_code as partner_code, r1861581670_10000000000089243.partner_short_name as partner_short_name, r1621125066_10000000000089259.simple_name as pif_pay_type_name, r1666104976_10000000000089260.depo_subacc_no as subacc_no, r1861581670_10000000000089239.partner_code as fund_code, convert(integer,((case when (select upper(t_types.type_code) from t_types where t_types.id=t_items.type_id) = 'PIF_PAY_ALLOCATE' then 1 else 2 end))) as CLAIM_TYPE_ID, convert(varchar(255),((select a.depo_acc_number from td_depo_accounts a, td_depo_subacc s where s.depo_acc_id=a.id and s.id=tp_pif_queries.subacc_id))) as acc_no from t_states, t_items, t_types, tp_pif_queries, td_depo_docs, t_partners r1861581670_10000000000089240, t_partners r1861581670_10000000000089243, t_simples r1621125066_10000000000089259, td_depo_subacc r1666104976_10000000000089260, t_partners r1861581670_10000000000089239 where t_states.id = t_items.state_id and t_types.id = t_items.type_id and t_items.id = tp_pif_queries.id and t_items.id = td_depo_docs.id and tp_pif_queries.agent_id *= r1861581670_10000000000089240.id and tp_pif_queries.pay_owner_id *= r1861581670_10000000000089243.id and tp_pif_queries.pif_pay_type_id *= r1621125066_10000000000089259.id and r1621125066_10000000000089259.type_id=4050 and tp_pif_queries.subacc_id *= r1666104976_10000000000089260.id and td_depo_docs.owner_id *= r1861581670_10000000000089239.id and td_depo_docs.owner_id in (select td_pay_fond.id from td_pay_fond, td_pay_fond c where c.id = @owner_id and c.ctrl_comp_id = td_pay_fond.ctrl_comp_id) and upper(t_types.type_code) = 'PIF_PAY_BUY' and upper(t_states.state_code) = 'CRT' and tp_pif_queries.type_queries = 4 and tp_pif_queries.pay_owner_id = @pay_owner_id and not exists(select 1 from tp_pif_queries p where p.queries4change_id = tp_pif_queries.id) end $ENDTEXT(17224985) $ENDFORM $FORM(D_PIF_PAYS4REP_LIST) name=Список заявок для выбора к отчету регистратора class=4 filter=D_PIF_PAYS_FILTER target_state= uo=uo_list_disable dw=d_pif_pays_list procedure=ap_10000000000089300 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=3666 height=1012 proc_text=$TEXT(17224985) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as declare @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric --declare @owner_id numeric begin set nocount on select @action_id = %ACTION_ID% --select @owner_id = .ORGANIZATION .CHECK_USER .CHECK_VIEW create table #pays4rep (id numeric) insert into #pays4rep select q.id from tp_pif_queries q, t_items i, t_states s, t_types t where i.id = q.id and i.state_id = s.id and i.type_id = t.id and s.class_id =0 and upper(s.state_code) ='PROV' and upper(t.type_code) in ('PIF_PAY_ALLOCATE','PIF_PAY_BUY') and (q.pif_pay_type_id=2 or (q.pif_pay_type_id=1 and not exists (select 1 from tp_extract2claim, td_depo_docs, .ACTUAL_TABLES where tp_extract2claim.claim_id=q.id and tp_extract2claim.extract_id=td_depo_docs.id .ACTUAL_WHERE(td_depo_docs.id)))) .ARGCONVERT_FILTER .VIEW_LIST and exists (select 1 from #pays4rep where #pays4rep.id=tp_pif_queries.id) --and td_depo_docs.owner_id = @owner_id end $ENDTEXT(17224985) $ACTION2RELATION(REL7.SIMPLE_NAME) relation=REL7 where=type_id=4050 prop=SIMPLE_NAME order=1 alias=pif_pay_type_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=10 alias=agent_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=18 alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_NAME) relation=REL3 where=null prop=PARTNER_NAME order=10 alias=partner_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=21 alias=partner_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.J_INN) relation=REL3 where=null prop=J_INN order=6 alias=j_inn $ENDACTION2RELATION $ACTION2RELATION(REL8.PARTNER_CODE) relation=REL8 where=null prop=PARTNER_CODE order=42 alias=fund_code $ENDACTION2RELATION $ACTION2RELATION(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $ENDACTION2RELATION $CALC(ACC_NO) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(17224986) (select a.depo_acc_number from td_depo_accounts a, td_depo_subacc s where s.depo_acc_id=a.id and s.id=tp_pif_queries.subacc_id) $ENDTEXT(17224986) label=Счет order=2 $ENDCALC $CALC(CLAIM_TYPE_ID) class=1 type=integer calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(17224986) (case when (select upper(t_types.type_code) from t_types where t_types.id=t_items.type_id) = 'PIF_PAY_ALLOCATE' then 1 else 2 end) $ENDTEXT(17224986) label=null order=1 $ENDCALC $ENDFORM $FORM(D_PIF_PAYS_FILTER) name=Фильтр class=6 filter=null target_state= uo=uo_filter dw=d_pif_pays_filter procedure=ap_10000000000089272 patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=2167 height=1264 proc_text=$TEXT(17224986) create procedure dbo.%PROC% as begin declare @date_b datetime, @date_e datetime, @owner_id numeric, @is_reg int, @owner_name varchar(255) select @date_b = .OPERDAY select @date_e = @date_b select @owner_id=.ORGANIZATION_PIF select @is_reg=.SR_FOR_PIF(@owner_id) select @owner_name=(select partner_short_name from t_partners where id=@owner_id) select convert(varchar(30), null) as sid, convert(varchar(30), null) as state_name_id_s, convert(varchar(30), null) as type_id_s, convert(varchar(30), null) as agent_id_s, @date_b as agent_regs_date_db, @date_e as agent_regs_date_de, convert(varchar(255), null) as agent_regs_no, convert(varchar(30), null) as pay_owner_id_s, convert(varchar(100), null) as agent_short_name, convert(varchar(100), null) as partner_short_name, convert(varchar(255), null) as item_remark, convert(datetime, null) as in_date_db, convert(datetime, null) as in_date_de, convert(varchar(100), null) as in_no, convert(varchar(30), null) as acc_id_s, convert(varchar(255), null) as acc_no, convert(varchar(30), null) as subacc_id_s, convert(varchar(255), null) as subacc_no, @is_reg as is_reg, convert(varchar(30), @owner_id) as owner_id_s, @owner_name as owner_name end $ENDTEXT(17224986) $FILTER(AGENT_REGS_DATE) type=2 label=Дата заявки prop=AGENT_REGS_DATE 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 $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(AGENT_REGS_NO) type=3 label=Номер заявки prop=AGENT_REGS_NO arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(AGENT_ID) type=1 label=Агент prop=AGENT_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(PAY_OWNER_ID) type=1 label=Пайщик prop=PAY_OWNER_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $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(ACC_ID) type=11 label=Счет в реестре prop=null arg_code=acc_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(SUBACC_ID) type=1 label=Раздел счета prop=SUBACC_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(D_PIF_PAY_CHOOSE_LIST) name=Список заявок для выбора в платежах class=4 filter=D_PIF_PAYS_FILTER target_state= uo=uo_list_disable dw=d_pif_pays_list procedure=ap_10000000000089289 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=2944 height=1012 proc_text=$TEXT(17224986) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as declare @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric --declare @owner_id numeric begin set nocount on select @action_id = %ACTION_ID% --select @owner_id = .ORGANIZATION .CHECK_USER .CHECK_VIEW create table #pays4paymt (id numeric) if @purpose_code in (4011,4021) -- выдача begin insert into #pays4paymt select q.id from tp_pif_queries q, t_items i, t_states s, t_types t where i.id = q.id and i.state_id = s.id and i.type_id = t.id and s.class_id =0 and upper(s.state_code) in ('ED','NOT_PAYMT','PROV') and upper(t.type_code) = 'PIF_PAY_ALLOCATE' and ((q.type_queries=1 and @purpose_code=4011) or (q.type_queries=3 and @purpose_code=4021)) -- обмен end if @purpose_code in (4014,4020) -- погашение begin insert into #pays4paymt select q.id from tp_pif_queries q, t_items i, t_states s, t_types t where i.id = q.id and i.state_id = s.id and i.type_id = t.id and s.class_id =0 and upper(t.type_code) = 'PIF_PAY_BUY' and ((upper(s.state_code) in ('ED','PROV','WAIT_CRT') and not exists (select 1 from t_paydocs p, .ACTUAL_TABLES where p.pif_pay_id=i.id .ACTUAL_WHERE(p.id))) or upper(s.state_code) in ('CRT_RET')) and ((q.type_queries=2 and @purpose_code=4014) or (q.type_queries=4 and @purpose_code=4020)) -- обмен end if @purpose_code=4015 -- возврат по отказанным заявкам на размещение begin insert into #pays4paymt select q.id from tp_pif_queries q, t_items i, t_states s, t_types t where i.id = q.id and i.state_id = s.id and i.type_id = t.id and s.class_id =0 and upper(s.state_code) in ('PROV','CANCEL') and upper(t.type_code) = 'PIF_PAY_ALLOCATE' end if @purpose_code=4013 -- возврат выкупа begin insert into #pays4paymt select q.id from tp_pif_queries q, t_items i, t_states s, t_types t where i.id = q.id and i.state_id = s.id and i.type_id = t.id and s.class_id =0 and upper(s.state_code) in ('CRT','CRT_NEXT') and upper(t.type_code) = 'PIF_PAY_BUY' end .ARGCONVERT_FILTER .VIEW_LIST and exists (select 1 from #pays4paymt where #pays4paymt.id=tp_pif_queries.id) --and td_depo_docs.owner_id = @owner_id end $ENDTEXT(17224986) $ACTION2RELATION(REL7.SIMPLE_NAME) relation=REL7 where=type_id=4050 prop=SIMPLE_NAME order=1 alias=pif_pay_type_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=10 alias=agent_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=18 alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_NAME) relation=REL3 where=null prop=PARTNER_NAME order=10 alias=partner_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=21 alias=partner_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.J_INN) relation=REL3 where=null prop=J_INN order=6 alias=j_inn $ENDACTION2RELATION $ACTION2RELATION(REL8.PARTNER_CODE) relation=REL8 where=null prop=PARTNER_CODE order=42 alias=fund_code $ENDACTION2RELATION $ACTION2RELATION(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $ENDACTION2RELATION $FILTER(PURPOSE_CODE) type=11 label=Целевое назначение платежа prop=null arg_code=purpose_code arg_type=int arg_length=null arg_prec=null arg_scale=null $ENDFILTER $CALC(ACC_NO) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(17224986) (select a.depo_acc_number from td_depo_accounts a, td_depo_subacc s where s.depo_acc_id=a.id and s.id=tp_pif_queries.subacc_id) $ENDTEXT(17224986) label=Счет order=2 $ENDCALC $CALC(CLAIM_TYPE_ID) class=1 type=integer calc_lenght=null calc_prec=null calc_scale=null formula=convert(integer,null) label=null order=1 $ENDCALC $ENDFORM $FORM(D_PIF_PAY_LIST) name=Список заявок class=4 filter=D_PIF_PAYS_FILTER target_state= uo=uo_list dw=d_pif_pays_list procedure=ap_10000000000089295 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=4663 height=1088 proc_text=$TEXT(17224988) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as declare @action_id numeric, @user_id numeric, @state_id numeric, @type_id numeric --declare @owner_id numeric begin set nocount on select @action_id = %ACTION_ID% --select @owner_id = .ORGANIZATION .CHECK_USER .CHECK_VIEW .ARGCONVERT_FILTER .VIEW_LIST --and td_depo_docs.owner_id = @owner_id and ((exists(select 1 from td_depo_subacc where id=tp_pif_queries.subacc_id and depo_acc_id=@acc_id) and @acc_id is not null) or @acc_id is null) end $ENDTEXT(17224988) $ACTION2RELATION(REL7.SIMPLE_NAME) relation=REL7 where=type_id=4050 prop=SIMPLE_NAME order=1 alias=pif_pay_type_name $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=10 alias=agent_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=18 alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_NAME) relation=REL3 where=null prop=PARTNER_NAME order=10 alias=partner_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=21 alias=partner_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.J_INN) relation=REL3 where=null prop=J_INN order=6 alias=j_inn $ENDACTION2RELATION $ACTION2RELATION(REL8.PARTNER_CODE) relation=REL8 where=null prop=PARTNER_CODE order=42 alias=fund_code $ENDACTION2RELATION $ACTION2RELATION(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $ENDACTION2RELATION $CALC(ACC_NO) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(17224988) (select a.depo_acc_number from td_depo_accounts a, td_depo_subacc s where s.depo_acc_id=a.id and s.id=tp_pif_queries.subacc_id) $ENDTEXT(17224988) label=Счет в реестре order=2 $ENDCALC $CALC(CLAIM_TYPE_ID) class=1 type=integer calc_lenght=null calc_prec=null calc_scale=null formula=convert(integer,null) label=null order=1 $ENDCALC $ENDFORM $FORM(D_QUERY_DB_IMP) name=Список для импорта заявок(DB) class=5 filter=null target_state= uo=uo_csv_import dw=d_pay_txt_imp_db procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(D_QUERY_IRCOL_IMP) name=Список для импорта заявок class=5 filter=null target_state= uo=uo_dbf_import dw=d_query_ircol_imp procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=null $ENDFORM $FORM(FORM1) name=Просмотр class=5 filter=null target_state= uo=null dw=null procedure=apt_pif_pay_group_ed patterns=GENERIC_VIEW_GROUP user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224988) 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 .ID_VAR(@owner_id) select @owner_id=.ORGANIZATION .VIEW_ITEM(ALL,2) end $ENDTEXT(17224988) $ACTION2RELATION(REL2.PARTNER_CODE) relation=REL2 where=null prop=PARTNER_CODE order=null alias=agent_code $ENDACTION2RELATION $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=null alias=agent_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=null alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=null alias=partner_short_name $ENDACTION2RELATION $ACTION2RELATION(REL6.IN_DATE) relation=REL6 where=null prop=IN_DATE order=5 alias=reestr_report_date $ENDACTION2RELATION $ACTION2RELATION(REL6.IN_NO) relation=REL6 where=null prop=IN_NO order=12 alias=reestr_report_no $ENDACTION2RELATION $ACTION2RELATION(REL5.CLIENT_REG_NO) relation=REL5 where=null prop=CLIENT_REG_NO order=null alias=agent_dog_no $ENDACTION2RELATION $ACTION2RELATION(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $ENDACTION2RELATION $ACTION2RELATION(REL10.ITEM_NAME) relation=REL10 where=null prop=ITEM_NAME order=3 alias=claim_name $ENDACTION2RELATION $CALC(ACC_IN_REG) class=1 type=integer calc_lenght=null calc_prec=null calc_scale=null formula=(select acc_in_reg from td_pay_fond where id=@owner_id) label=Спецдеп является спецрегистратором order=1 $ENDCALC $ENDFORM $FORM(FORM2) name=Список аннулированных заявок class=4 filter=null target_state= uo=uo_list dw=d_pif_pays_list procedure=ap_30000000000096552 patterns=GENERIC_VIEW_LIST user_define=0 isvisible=1 istab=1 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224988) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as .BEGIN('N') select @item_remark=null select @type_id_s=null .CHECK_USER .CHECK_VIEW_LIST .ARGCONVERT_FILTER .VIEW_LIST .END $ENDTEXT(17224988) $ACTION2RELATION(REL2.PARTNER_SHORT_NAME) relation=REL2 where=null prop=PARTNER_SHORT_NAME order=10 alias=agent_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_CODE) relation=REL3 where=null prop=PARTNER_CODE order=18 alias=partner_code $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_NAME) relation=REL3 where=null prop=PARTNER_NAME order=10 alias=partner_name $ENDACTION2RELATION $ACTION2RELATION(REL3.PARTNER_SHORT_NAME) relation=REL3 where=null prop=PARTNER_SHORT_NAME order=21 alias=partner_short_name $ENDACTION2RELATION $ACTION2RELATION(REL3.J_INN) relation=REL3 where=null prop=J_INN order=6 alias=j_inn $ENDACTION2RELATION $ACTION2RELATION(REL7.SIMPLE_NAME) relation=REL7 where=type_id=4050 prop=SIMPLE_NAME order=1 alias=pif_pay_type_name $ENDACTION2RELATION $ACTION2RELATION(REL8.PARTNER_CODE) relation=REL8 where=null prop=PARTNER_CODE order=42 alias=fund_code $ENDACTION2RELATION $ACTION2RELATION(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $ENDACTION2RELATION $FILTER(FOUNDATION_ID) type=1 label=Запрос prop=FOUNDATION_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $CALC(CLAIM_TYPE_ID) class=1 type=integer calc_lenght=null calc_prec=null calc_scale=null formula=convert(integer,null) label=null order=1 $ENDCALC $CALC(ACC_NO) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(17224990) (select a.depo_acc_number from td_depo_accounts a, td_depo_subacc s where s.depo_acc_id=a.id and s.id=tp_pif_queries.subacc_id) $ENDTEXT(17224990) label=Счет order=2 $ENDCALC $ENDFORM $ACTION(ACTION_INSERT) name=Импорт заявок(DB) class=1 form=D_QUERY_DB_IMP target_state=null procedure=ap_30000000000027200 patterns=GENERIC_IMPORT user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224990) create procedure dbo.%PROC% @fund_code varchar(100), -- код фонда из агентской базы @fund4change_code varchar(100), @agent_dog_no varchar(50), -- № агентского договора @agent_code varchar(50), -- @agent_dog_date varchar(30), -- дата агентского договора @agent_regs_no varchar(50), -- № документа @agent_regs_date varchar(30), -- дата документа @agent_regs_time varchar(30), -- дата документа @doc_type int, -- код типа документа @reestr_acc_no varchar(100) = null, -- № лицевого счета @change_acc_no varchar(100) = null, -- № счета в фонде для обмена @pif_pay_type_id numeric(18,0) = null, -- использование заявки (1=одноразовая,2=многоразовая) @pay_type_id numeric(18,0) = null, -- тип проведения (1=кол-во,2,3=по сумме,4=все паи) @stock_qty decimal(18,8) = null, -- кол-во паев @payment_sum money = null, -- сумма за паи @search_is_company int = null, -- 1=юрик, 0=физик, -1=нет данных для поиска @search_partner_name varchar(255) = null, -- наименование @search_j_inn varchar(30) = null, -- ИНН @search_f_passport_serial varchar(20) = null, -- серия док-та @search_f_passport_no varchar(20) = null, -- № док-та @change_is_company int = null, -- 1=юрик, 0=физик, -1=нет данных для поиска @change_partner_name varchar(255) = null, -- наименование @change_j_inn varchar(30) = null, -- ИНН @change_f_passport_serial varchar(20) = null, -- серия док-та @change_f_passport_no varchar(20) = null, -- № док-та @is_company int = null, -- 1=юрик, 0=физик, -1=нет данных для вставки-изменения @partner_name varchar(255) = null, @j_inn varchar(30) = null, @tax_category_id numeric(18, 0) = null , -- справочник 4007 @jur_code varchar (30) = null , @juraddress varchar (150) = null , @postal_code varchar (30) = null , @partner_post_address varchar (255) = null , @letter_receive_type numeric(18, 0) = null , -- справочник 1002 @f_passport_serial varchar(20) = null, @f_passport_no varchar(20) = null, @f_doctype_id numeric(18, 0) = null , -- справочник 1004 @f_passport_distributor varchar (150) = null , @f_passport_when varchar(30) = null , @f_birthday varchar(30) = null , @f_last_name varchar(50) = null , @f_first_name varchar(50) = null , @f_middle_name varchar(50) = null , @regist_sertificate_no varchar (50) = null , @regist_sertificate_date varchar(30) = null , @regist_organisation varchar (150) = null , @organisation_form_id numeric(18, 0) = null , -- справочник 1001 @bank_name varchar (150) = null , @bik varchar (30) = null , @corr_account varchar (150) = null , @bank_account_no varchar (50) = null as declare @sid varchar(30) .BEGIN('N') .CHECK_USER --.GET_PROPS declare @agent_dog_date_d datetime, @agent_regs_date_d datetime, @agent_regs_time_d datetime, @f_passport_when_d datetime, @f_birthday_d datetime, @regist_sertificate_date_d datetime, @egrn_date_d datetime, @a_id numeric, @acctype_id numeric, @subacctype_id numeric, @default_folder_id numeric, @rel_id numeric, @doc_id_s varchar(30), @newdoc_id numeric, @mess varchar(255), @partner_id numeric(18,0), @change_partner_id numeric(18,0), @acc_id numeric(18,0), @subacc_id numeric, @classvalue_id numeric, @fund2_id numeric(18,0), @acc2_id numeric(18,0), @acc_name varchar(255), @internal_no varchar(255), @acc_number varchar(255), @subacc_name varchar(255), @null_act_id numeric(18,0), @subacc_no varchar(255), @in_no varchar(255), @mode varchar(255), @out_no varchar(255), @in_date datetime, @partner_code varchar(100), @partner_short_name varchar(100) declare @indoc_type_id numeric, @document_form_id numeric, @indoc_comment varchar(255), @corr_name varchar(255), @corr_id numeric, @indoc_id numeric, @in_time datetime, @indoc_id_s varchar(30), @days int, @out_date datetime, @indate datetime select @in_time=.OPERDAY select @indate=.OPERDAY --Тип заявки --if @doc_type = 34 select @doc_type = 1 --if @doc_type = 14 select @doc_type = 1 --if @doc_type = 12 select @doc_type = 2 --if @doc_type = 13 select @doc_type = 3 --if @doc_type = 35 select @doc_type = 4 --if @doc_type = 17 select @doc_type = 5 if @letter_receive_type = 2 select @letter_receive_type = 8 if @letter_receive_type = 3 select @letter_receive_type = 9 if @letter_receive_type = 4 select @letter_receive_type = 10 if @letter_receive_type = 5 select @letter_receive_type = 11 if @letter_receive_type = 6 select @letter_receive_type = 12 if @f_doctype_id = 01 select @f_doctype_id = 10 if @f_doctype_id = 02 select @f_doctype_id = 11 if @f_doctype_id = 03 select @f_doctype_id = 8 if @f_doctype_id = 04 select @f_doctype_id = 12 if @f_doctype_id = 05 select @f_doctype_id = 13 if @f_doctype_id = 06 select @f_doctype_id = 14 if @f_doctype_id = 07 select @f_doctype_id = 15 if @f_doctype_id = 09 select @f_doctype_id = 16 if @f_doctype_id = 10 select @f_doctype_id = 6 if @f_doctype_id = 11 select @f_doctype_id = 17 if @f_doctype_id = 12 select @f_doctype_id = 18 if @f_doctype_id = 13 select @f_doctype_id = 19 if @f_doctype_id = 14 select @f_doctype_id = 20 if @f_doctype_id = 21 select @f_doctype_id = 5 if @f_doctype_id = 22 select @f_doctype_id = 6 if @f_doctype_id = 26 select @f_doctype_id = 21 if @f_doctype_id = 27 select @f_doctype_id = 22 if @f_doctype_id = 91 select @f_doctype_id = 23 if @tax_category_id = 0 select @tax_category_id = 3 if @tax_category_id = 1 select @tax_category_id = 14 if @tax_category_id = 2 select @tax_category_id = 15 if @tax_category_id = 3 select @tax_category_id = 16 if @tax_category_id = 4 select @tax_category_id = 17 if @tax_category_id = 5 select @tax_category_id = 13 if @doc_type=2 select @is_company=-1 if isnull(@partner_name,'')='' select @is_company=-1 if isnull(@search_partner_name,'')='' select @search_is_company=-1 if isnull(@change_partner_name,'')='' select @change_is_company=-1 --if isnull(@agent_dog_date,'')<>'' select @agent_dog_date_d=convert(datetime,@agent_dog_date,3) if isnull(@agent_regs_date,'')<>'' select @agent_regs_date_d=convert(datetime,@agent_regs_date,3) if isnull(@agent_regs_time,'')<>'' select @agent_regs_time_d=convert(datetime,@agent_regs_time,8) if isnull(@f_passport_when,'')<>'' select @f_passport_when_d=convert(datetime,@f_passport_when,3) if isnull(@f_birthday,'')<>'' select @f_birthday_d=convert(datetime,@f_birthday,3) if isnull(@regist_sertificate_date,'')<>'' select @regist_sertificate_date_d=convert(datetime,@regist_sertificate_date,3) --if isnull(@egrn_date,'')<>'' select @egrn_date_d=convert(datetime,@egrn_date,3) if @doc_type is null .EXIT_MESSAGE('Не указан тип заявки') if @agent_regs_date_d is null .EXIT_MESSAGE('Не указана дата заявки') if @agent_regs_no is null .EXIT_MESSAGE('Не указан номер заявки') -- ФОНД declare @fund_id numeric select @fund_id = max(i.item_id) from t_item2class i, t_classvalues v, t_classes c where rtrim(ltrim(upper(@fund_code))) = ltrim(upper(i.code)) -- +'%' -- код не умещ. в 30 символов! --ltrim(upper(i.code)) = ltrim(upper(@fund_code)) and i.value_id=v.id and v.class_id=c.id and upper(c.code)='FUND_IMPORT_CODE' and .ITEMS_EXISTS_BY_TYPE(i.item_id,'PIF_FUND') if @fund_id is null .EXIT_MESSAGE('Фонд не определен, внесите значение классификатора "Код фонда при импорте"') declare @agent_id numeric declare @agent_dog_id numeric if rtrim(ltrim(@agent_dog_no)) is null begin -- Поиск договора с УК select @agent_id = td_pay_fond.ctrl_comp_id from td_pay_fond where td_pay_fond.id = @fund_id if @agent_id is null .EXIT_MESSAGE('У фонда не задана управляющая компания') select @agent_dog_id = tb_contracts.id from tb_contracts, td_depo_docs where tb_contracts.client_id = @agent_id and tb_contracts.dog_type_id = 2 and tb_contracts.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') if @agent_dog_id is null .EXIT_MESSAGE('Для управляющей компании не задан договор комиссии') end else begin select @agent_dog_id = tb_contracts.id, @agent_id = tb_contracts.client_id from tb_contracts, td_depo_docs, t_item2class i, t_classvalues v, t_classes c where tb_contracts.dog_type_id = 2 and tb_contracts.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') and i.item_id=tb_contracts.id and ltrim(upper(i.code)) = ltrim(upper(@agent_dog_no)) and i.value_id=v.id and v.class_id=c.id and upper(c.code)='AGENT_IMPORT_CODE' and upper(v.code)='AGENT_IMPORT_CODE' select @mess = 'Для данного фонда не найден договор номер ' + @agent_dog_no if @agent_dog_id is null .EXIT_MESSAGE(@mess) end if @doc_type=1 and exists(select 1 from tp_pif_queries q, td_depo_docs d where q.agent_regs_no=@agent_regs_no and q.agent_regs_date=@agent_regs_date_d and q.agent_id=@agent_id and d.id=q.id and d.owner_id=@fund_id and .ITEMS_EXISTS_BY_TYPE(q.id,'PIF_PAY_ALLOCATE')) .EXIT_MESSAGE('Заявка с указанными датой и номером существует') if @doc_type in (2,4) and exists(select 1 from tp_pif_queries q, td_depo_docs d where q.agent_regs_no=@agent_regs_no and q.agent_regs_date=@agent_regs_date_d and q.agent_id=@agent_id and d.id=q.id and d.owner_id=@fund_id and .ITEMS_EXISTS_BY_TYPE(q.id,'PIF_PAY_BUY')) .EXIT_MESSAGE('Заявка с указанными датой и номером существует') if @doc_type=3 and exists(select 1 from td_depo_docs d, td_depo_accounts a where d.out_no=@agent_regs_no and d.out_date=@agent_regs_date_d and d.correspondent_id=@agent_id and d.id=a.id and a.fund_id=@fund_id and d.class_id=2 and isnull(d.out_no,'')<>'' and d.out_date is not null and d.correspondent_id is not null and ((.ITEMS_EXISTS_BY_TYPE(d.id,'PARTJUR_CHANGE') and @search_is_company=1) or (.ITEMS_EXISTS_BY_TYPE(d.id,'PARTFIS_CHANGE') and @search_is_company=0)) and @search_is_company<>-1) .EXIT_MESSAGE('Заявка с указанными датой и номером существует') if @search_is_company<>-1 begin if isnull(@search_f_passport_serial,'')<>'' while charindex(' ',@search_f_passport_serial)>0 begin select @search_f_passport_serial=stuff(@search_f_passport_serial,charindex(' ',@search_f_passport_serial),1,null) end if isnull(@search_f_passport_no,'')<>'' while charindex(' ',@search_f_passport_no)>0 begin select @search_f_passport_no=stuff(@search_f_passport_no,charindex(' ',@search_f_passport_no),1,null) end select @search_f_passport_serial=upper(@search_f_passport_serial), @search_f_passport_no=upper(@search_f_passport_no) if @search_is_company=0 begin if (select count(*) from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@search_f_passport_serial,'') + isnull(@search_f_passport_no,'')) and upper(ltrim(rtrim(t_partners.partner_name)))=upper(@search_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@search_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @partner_id = (select t_partners.id from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@search_f_passport_serial,'') + isnull(@search_f_passport_no,'')) and upper(t_partners.partner_name)=upper(@search_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@search_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS')) end if @search_is_company=1 begin if (select count(*) from t_partners where upper(ltrim(rtrim(t_partners.partner_name)))=upper(@search_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@search_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @partner_id = (select t_partners.id from t_partners where upper(t_partners.partner_name)=upper(@search_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@search_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR')) if @partner_id is null and @is_company=-1 .EXIT_MESSAGE('Не найдено лицо в базе регистратора по указанным параметрам поиска') end end if @is_company<>-1 and @partner_id is null begin if isnull(@f_passport_serial,'')<>'' while charindex(' ',@f_passport_serial)>0 begin select @f_passport_serial=stuff(@f_passport_serial,charindex(' ',@f_passport_serial),1,null) end if isnull(@f_passport_no,'')<>'' while charindex(' ',@f_passport_no)>0 begin select @f_passport_no=stuff(@f_passport_no,charindex(' ',@f_passport_no),1,null) end select @f_passport_serial=upper(@f_passport_serial), @f_passport_no=upper(@f_passport_no) if @is_company=0 begin if (select count(*) from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@f_passport_serial,'') + isnull(@f_passport_no,'')) and upper(ltrim(rtrim(t_partners.partner_name)))=upper(@partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @partner_id = (select t_partners.id from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@f_passport_serial,'') + isnull(@f_passport_no,'')) and upper(t_partners.partner_name)=upper(@partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS')) end if @is_company=1 begin if (select count(*) from t_partners where upper(ltrim(rtrim(t_partners.partner_name)))=upper(@partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @partner_id = (select t_partners.id from t_partners where upper(t_partners.partner_name)=upper(@partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR')) end end /* if isnull(@reestr_acc_no,'')<>'' and @partner_id is not null begin if (select count(*) from td_depo_accounts a where a.depo_acc_number=@reestr_acc_no and a.fund_id=@fund_id and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC'))>1 .EXIT_MESSAGE('В реестре фонда найдено более одного счета с указанным номером') select @acc_id = (select a.id from td_depo_accounts a where a.depo_acc_number=@reestr_acc_no and a.fund_id=@fund_id and a.partner_id=@partner_id and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC')) if @acc_id is null .EXIT_MESSAGE('Не найден счет в реестре фонда с указанным номером для указанного лица') end */ if @doc_type= 5 begin if @change_is_company<>-1 begin if isnull(@change_f_passport_serial,'')<>'' while charindex(' ',@change_f_passport_serial)>0 begin select @change_f_passport_serial=stuff(@change_f_passport_serial,charindex(' ',@change_f_passport_serial),1,null) end if isnull(@change_f_passport_no,'')<>'' while charindex(' ',@change_f_passport_no)>0 begin select @change_f_passport_no=stuff(@change_f_passport_no,charindex(' ',@change_f_passport_no),1,null) end select @change_f_passport_serial=upper(@change_f_passport_serial), @change_f_passport_no=upper(@change_f_passport_no) if @change_is_company=0 begin if (select count(*) from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@f_passport_serial,'') + isnull(@f_passport_no,'')) and upper(ltrim(rtrim(t_partners.partner_name)))=upper(@change_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@change_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @change_partner_id = (select t_partners.id from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(f.f_passport_serial,'') + isnull(f.f_passport_no,'')) = (isnull(@change_f_passport_serial,'') + isnull(@change_f_passport_no,'')) and upper(t_partners.partner_name)=upper(@change_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@change_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_FIS')) end if @change_is_company=1 begin if (select count(*) from t_partners where upper(ltrim(rtrim(t_partners.partner_name)))=upper(@change_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@change_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR'))>1 .EXIT_MESSAGE('Найдено более одного лица с указанными реквизитами') else select @change_partner_id = (select t_partners.id from t_partners where upper(t_partners.partner_name)=upper(@change_partner_name) and upper(isnull(t_partners.j_inn,''))=upper(isnull(@change_j_inn,'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR')) if @change_partner_id is null --and @is_company=-1 .EXIT_MESSAGE('Не найдено лицо в базе регистратора по указанным параметрам поиска') end end end if @doc_type=1 -- выдача begin -- if @acc_id is null -- новый счет -- begin if @partner_id is null -- новый пайщик begin if @is_company not in (0,1) .EXIT_MESSAGE("Не указаны реквизиты пайщика для вставки-изменения") end -- else -- проверка на существование счета в реестре -- if exists(select 1 from td_depo_accounts a where a.partner_id=@partner_id and a.fund_id=@fund_id -- and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC')) -- .EXIT_MESSAGE('Указанное лицо уже является владельцем счета в реестре Фонда') -- end end /* -- Проверки для заявки на погашение if @doc_type=2 begin if @acc_id is null and @partner_id is not null .EXIT_MESSAGE('Не указан счет пайщика в реестре фонда для заявки на погашение(обмен)') -- счет обязателен end */ -- Проверки для заявки на обмен if @doc_type=4 begin -- if @acc_id is null and @partner_id is not null .EXIT_MESSAGE('Не указан счет пайщика в реестре фонда для заявки на погашение(обмен)') -- счет обязателен select @classvalue_id=null select @classvalue_id=(select v.id from t_classes c, t_classvalues v where c.code='FUND_IMPORT_CODE' and v.code='FUND_IMPORT_CODE' and v.class_id=c.id and .ITEMS_EXISTS(c.id) and .ITEMS_EXISTS(v.id)) if @classvalue_id is null .EXIT_MESSAGE('Не найден тип классификатора для импорта сделок') if (select count(*) from t_item2class i where rtrim(ltrim(upper(i.code)))=rtrim(ltrim(upper(isnull(@fund4change_code,'')))) and .ITEMS_EXISTS(i.item_id) and .ITEMS_EXISTS(i.id) and i.value_id=@classvalue_id) > 1 .EXIT_MESSAGE('Найдено более одного фонда с указанным наименованием') select @fund2_id=(select i.item_id from t_item2class i where rtrim(ltrim(upper(i.code)))=rtrim(ltrim(upper(isnull(@fund4change_code,'')))) and .ITEMS_EXISTS(i.item_id) and .ITEMS_EXISTS(i.id) and i.value_id=@classvalue_id) if @fund2_id is null select @fund2_id=(select p.id from t_partners p where rtrim(ltrim(upper(p.partner_name)))=rtrim(ltrim(upper(isnull(@fund4change_code,'')))) and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_FUND') and p.id<>@fund_id) if @fund2_id is null .EXIT_MESSAGE('Для заявки на обмен не найден фонд-получатель') -- существование заявки для фонда-получателя if exists(select 1 from tp_pif_queries q, td_depo_docs d where q.agent_regs_no=@agent_regs_no and q.agent_regs_date=@agent_regs_date_d and q.agent_id=@agent_id and d.id=q.id and d.owner_id=@fund2_id and .ITEMS_EXISTS_BY_TYPE(q.id,'PIF_PAY_ALLOCATE')) .EXIT_MESSAGE('Заявка с указанными датой и номером существует') /* if (select count(*) from td_depo_accounts a where a.depo_acc_number=@change_acc_no and a.fund_id=@fund2_id and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC'))>1 .EXIT_MESSAGE('В реестре фонда-получателя найдено более одного счета с указанным номером') if isnull(@change_acc_no,'')<>'' begin if @partner_id is not null begin select @acc2_id = (select a.id from td_depo_accounts a where a.depo_acc_number=@change_acc_no and a.fund_id=@fund2_id and a.partner_id=@partner_id and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC')) if @acc2_id is null .EXIT_MESSAGE('Не найден счет в реестре фонда-получателя с указанным номером для указанного лица') end end */ /* if @acc2_id is null begin if exists(select 1 from td_depo_accounts a where a.partner_id=@partner_id and a.fund_id=@fund2_id and .ITEMS_EXISTS_BY_TYPE(a.id,'SREG_ACC')) .EXIT_MESSAGE('Указанное лицо уже является владельцем счета в реестре фонда-получателя') end */ end if @doc_type=3 begin if @partner_id is null .EXIT_MESSAGE('Не найдено лицо для изменения') if @is_company not in (0,1) .EXIT_MESSAGE('Не указаны реквизиты пайщика для вставки-изменения') end -- Заявка на выдачу if @doc_type=1 begin -- if @acc_id is null -- новый счет -- begin if @partner_id is null -- новый пайщик begin if @is_company=0 begin select @partner_code = @f_last_name + '.' + substring(@f_first_name,1,1) + '.' + substring(@f_middle_name,1,1) select @partner_short_name = @f_last_name + '.' + substring(@f_first_name,1,1) + '.' + substring(@f_middle_name,1,1) --select @partner_name = @f_last_name + ' ' + @f_first_name + ' ' + @f_middle_name .TYPE_INIT('PARTNERS_FIS',1) .TYPE_ASSIGN(partner_code,@partner_code,1) .TYPE_ASSIGN(partner_name,@partner_name,1) .TYPE_ASSIGN(partner_short_name,@partner_short_name,1) .TYPE_ASSIGN(j_inn,@j_inn,1) .TYPE_ASSIGN(tax_category_id,@tax_category_id,1) .TYPE_ASSIGN(jur_code,@jur_code,1) .TYPE_ASSIGN(juraddress,@juraddress,1) .TYPE_ASSIGN(postal_code,@postal_code,1) .TYPE_ASSIGN(partner_post_address,@partner_post_address,1) .TYPE_ASSIGN(real_code,@postal_code,1) .TYPE_ASSIGN(partner_address,@partner_post_address,1) .TYPE_ASSIGN(letter_receive_type,@letter_receive_type,1) -- .TYPE_ASSIGN(partner_comment,@partner_comment,1) .TYPE_ASSIGN(f_passport_serial,@f_passport_serial,1) .TYPE_ASSIGN(f_passport_no,@f_passport_no,1) .TYPE_ASSIGN(f_doctype_id,@f_doctype_id,1) .TYPE_ASSIGN(f_passport_distributor,@f_passport_distributor,1) .TYPE_ASSIGN(f_passport_when,@f_passport_when,1) .TYPE_ASSIGN(f_birthday,@f_birthday,1) .TYPE_ASSIGN(f_last_name,@f_last_name,1) .TYPE_ASSIGN(f_first_name,@f_first_name,1) .TYPE_ASSIGN(f_middle_name,@f_middle_name,1) .TYPE_ASSIGN(list_folder_id,@default_folder_id,1) .TYPE_INSERT('PARTNERS_FIS','CRT',1) select @partner_id=.TYPE_FIELD(id,1) end if @is_company=1 begin select @partner_code = @partner_name .TYPE_INIT('PARTNERS_JUR',2) .TYPE_ASSIGN(partner_code,@partner_code,2) .TYPE_ASSIGN(partner_name,@partner_name,2) .TYPE_ASSIGN(j_inn,@j_inn,2) .TYPE_ASSIGN(tax_category_id,@tax_category_id,2) .TYPE_ASSIGN(jur_code,@jur_code,2) .TYPE_ASSIGN(juraddress,@juraddress,2) .TYPE_ASSIGN(postal_code,@postal_code,2) .TYPE_ASSIGN(partner_post_address,@partner_post_address,2) .TYPE_ASSIGN(real_code,@postal_code,2) .TYPE_ASSIGN(partner_address,@partner_post_address,2) .TYPE_ASSIGN(letter_receive_type,@letter_receive_type,2) -- .TYPE_ASSIGN(partner_comment,@partner_comment,2) -- .TYPE_ASSIGN(partner_short_name,@partner_short_name,2) .TYPE_ASSIGN(regist_sertificate_no,@regist_sertificate_no,2) .TYPE_ASSIGN(regist_sertificate_date,@regist_sertificate_date,2) .TYPE_ASSIGN(regist_organisation,@regist_organisation,2) .TYPE_ASSIGN(organisation_form_id,@organisation_form_id,2) -- .TYPE_ASSIGN(j_head_last_name,@j_head_last_name,2) .TYPE_ASSIGN(list_folder_id,@default_folder_id,2) .TYPE_INSERT('PARTNERS_JUR','CRT',2) select @partner_id=.TYPE_FIELD(id,2) end -- расчетный счет if isnull(@bank_account_no,'')<>'' and isnull(@bik,'')<>'' and not exists(select 1 from t_bank_accounts b where partner_id=@partner_id and .ITEMS_EXISTS_BY_TYPE(b.id,'BANK_ACCOUNTS') and b.bank_account_no=@bank_account_no and b.bik=@bik) begin .TYPE_INIT('BANK_ACCOUNTS',5) .TYPE_ASSIGN(partner_id,@partner_id,5) .TYPE_ASSIGN(bank_account_no,@bank_account_no,5) .TYPE_ASSIGN(bank_name,@bank_name,5) .TYPE_ASSIGN(corr_account,@corr_account,5) .TYPE_ASSIGN(bik,@bik,5) .TYPE_ASSIGN(acc_type_id,10,5) .TYPE_ASSIGN(acc_purpose_id,1,5) .TYPE_ASSIGN(bank_acc_cur_id,(.CUR('RUR')),5) .TYPE_ASSIGN(list_folder_id,@default_folder_id,5) .TYPE_INSERT('BANK_ACCOUNTS','CRT',5) end end -- открытие счета /* select @subacctype_id=.SREG_SUBTYPE_FROM_CODE('OB') select @acctype_id=.SREG_ACCTYPE_FROM_CODE('LP') select @default_folder_id=.DEPO_FOLDER_DEFAULT select @newdoc_id=null select @acc_name = (select partner_name from t_partners where id=@partner_id) select @internal_no=null .SREG_GEN_ACC_NO(@acc_number,@acc_id,@acctype_id,@internal_no,@fund_id) .TYPE_INIT('SREG_ACC',3) .TYPE_ASSIGN(depo_acctypes_id,@acctype_id,3) .TYPE_ASSIGN(depo_acc_number,@acc_number,3) .TYPE_ASSIGN(partner_id,@partner_id,3) .TYPE_ASSIGN(depo_acc_name,@acc_name,3) .TYPE_ASSIGN(fund_id,@fund_id,3) .TYPE_ASSIGN(act_or_pass,2,3) .TYPE_ASSIGN(list_folder_id,@default_folder_id,3) .TYPE_ASSIGN(acc_open_date,@agent_regs_date_d,3) .TYPE_INSERT('SREG_ACC','CRT',3) select @acc_id=.TYPE_FIELD(id,3) -- Вставка раздела if not exists(select 1 from td_depo_subacc s where s.depo_acc_id=@acc_id and s.depo_subacc_type_id=@subacctype_id and .ITEMS_EXISTS(s.id)) begin select @subacc_name = .SREG_SUBACC_NAME(@acc_id,@subacctype_id) select @internal_no=null,@null_act_id=null .SREG_GEN_SUBACC_NO(@subacc_no,@id,@acc_id,@subacctype_id,@internal_no,@null_act_id) .TYPE_INIT('SREG_SUBACC',10) .TYPE_ASSIGN(depo_acc_id,@acc_id,10) .TYPE_ASSIGN(depo_subacc_type_id,@subacctype_id,10) .TYPE_ASSIGN(depo_subacc_name,@subacc_name,10) .TYPE_ASSIGN(internal_no,@internal_no,10) .TYPE_ASSIGN(act_or_pass,2,10) .TYPE_ASSIGN(subacc_open_date,@agent_regs_date_d,10) .TYPE_ASSIGN(depo_subacc_no,@subacc_no,10) .TYPE_INSERT('SREG_SUBACC','CRT',10) end end select @subacctype_id=.SREG_SUBTYPE_FROM_CODE('OB') -- основной раздел select @subacc_id=(select max(s.id) from td_depo_subacc s where s.depo_acc_id=@acc_id and s.depo_subacc_type_id=@subacctype_id and .ITEMS_EXISTS(s.id)) if @subacc_id is null .EXIT_MESSAGE_PARM('Невозможно получить ID основного раздела счета для заявки № ',@agent_regs_no) */ -- вставка заявки на выдачу .FUND_GEN_NO(@in_no,1,@agent_regs_date_d) .TYPE_INIT('PIF_PAY_ALLOCATE',4) .TYPE_ASSIGN(subacc_id,@subacc_id,4) .TYPE_ASSIGN(owner_id, @fund_id, 4) .TYPE_ASSIGN(depo_doc_type,1,4) .TYPE_ASSIGN(in_date,@agent_regs_date_d,4) .TYPE_ASSIGN(in_no,@in_no,4) .TYPE_ASSIGN(out_date,@agent_regs_date_d,4) .TYPE_ASSIGN(out_no,@agent_regs_no,4) .TYPE_ASSIGN(correspondent_id,@agent_id,4) .TYPE_ASSIGN(folder_id,(.DEPO_FOLDER_DEFAULT), 4) .TYPE_ASSIGN(agent_regs_date, @agent_regs_date_d, 4) .TYPE_ASSIGN(agent_regs_no, @agent_regs_no, 4) .TYPE_ASSIGN(pay_owner_id, @partner_id, 4) .TYPE_ASSIGN(agent_id, @agent_id, 4) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 4) .TYPE_ASSIGN(pif_pay_type_id,@pif_pay_type_id,4) .TYPE_ASSIGN(type_queries, 1, 4) .TYPE_INSERT('PIF_PAY_ALLOCATE','ED',4) ------- генерация вход. документа---------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Заявка на выдачу паев' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',14) .TYPE_ASSIGN(out_date,@out_date,14) .TYPE_ASSIGN(in_date,.OPERDAY,14) .TYPE_ASSIGN(out_no,@agent_regs_no,14) .TYPE_ASSIGN(in_time,@in_time,14) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,14) .TYPE_ASSIGN(corr_name,@corr_name,14) .TYPE_ASSIGN(document_form_id,@document_form_id,14) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,14) .TYPE_ASSIGN(owner_id,@fund_id,14) .TYPE_ASSIGN(comment,@indoc_comment,14) .TYPE_ASSIGN(send_date,.OPERDAY,14) .TYPE_INSERT('SD_INDOC','ED',14) select @indoc_id=.TYPE_FIELD(id,14) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',20) .TYPE_ASSIGN(indoc_id,@id14_1,20) .TYPE_ASSIGN(doc_id,@id4_1,20) .TYPE_INSERT('SD_INDOC2DOC','CRT',20) -------конец генерации вход. документа---------- end -- выдача -- Заявка на погашение/обмен if @doc_type in (2,4) begin /* select @subacctype_id=.SREG_SUBTYPE_FROM_CODE('OB') -- основной раздел select @subacc_id=(select max(s.id) from td_depo_subacc s where s.depo_acc_id=@acc_id and s.depo_subacc_type_id=@subacctype_id and .ITEMS_EXISTS(s.id)) if @subacc_id is null .EXIT_MESSAGE_PARM('Невозможно получить ID основного раздела счета для заявки № ',@agent_regs_no) */ -- вставка заявки на погашение/списание по обмену .FUND_GEN_NO(@in_no,1,@agent_regs_date_d) .TYPE_INIT('PIF_PAY_BUY',6) .TYPE_ASSIGN(subacc_id,@subacc_id,6) .TYPE_ASSIGN(owner_id, @fund_id, 6) .TYPE_ASSIGN(depo_doc_type,1,6) .TYPE_ASSIGN(in_date,@agent_regs_date_d,6) .TYPE_ASSIGN(in_no,@in_no,6) .TYPE_ASSIGN(out_date,@agent_regs_date_d,6) .TYPE_ASSIGN(out_no,@agent_regs_no,6) .TYPE_ASSIGN(correspondent_id,@agent_id,6) .TYPE_ASSIGN(folder_id,(.DEPO_FOLDER_DEFAULT), 6) .TYPE_ASSIGN(agent_id, @agent_id, 6) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 6) .TYPE_ASSIGN(agent_regs_date, @agent_regs_date_d, 6) .TYPE_ASSIGN(agent_regs_no, @agent_regs_no, 6) .TYPE_ASSIGN(pay_owner_id, @partner_id, 6) .TYPE_ASSIGN(pif_pay_type_id,@pif_pay_type_id,6) .TYPE_ASSIGN(pay_type_id,@pay_type_id,6) .TYPE_ASSIGN(stock_qty,@stock_qty,6) .TYPE_ASSIGN(payment_sum,@payment_sum,6) if @doc_type=2 begin -- погашение .TYPE_ASSIGN(type_queries, 2, 6) end if @doc_type=4 begin -- списание по обмену .TYPE_ASSIGN(type_queries, 4, 6) .TYPE_ASSIGN(fund4change,((select partner_name from t_partners where id=@fund2_id)),6) end .TYPE_INSERT('PIF_PAY_BUY','ED',6) -------генерация вход. документа---------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Заявка на погашение паев' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',15) .TYPE_ASSIGN(out_date,@out_date,15) .TYPE_ASSIGN(in_date,.OPERDAY,15) .TYPE_ASSIGN(out_no,@agent_regs_no,15) .TYPE_ASSIGN(in_time,@in_time,15) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,15) .TYPE_ASSIGN(corr_name,@corr_name,15) .TYPE_ASSIGN(document_form_id,@document_form_id,15) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,15) .TYPE_ASSIGN(owner_id,@fund_id,15) .TYPE_ASSIGN(comment,@indoc_comment,15) .TYPE_ASSIGN(send_date,.OPERDAY,15) .TYPE_INSERT('SD_INDOC','ED',15) select @indoc_id=.TYPE_FIELD(id,15) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',21) .TYPE_ASSIGN(indoc_id,@id15_1,21) .TYPE_ASSIGN(doc_id,@id6_1,21) .TYPE_INSERT('SD_INDOC2DOC','CRT',21) -------конец генерации вход. документа---------- -- выдача по обмену if @doc_type=4 begin /* if @acc2_id is null -- новый счет в фонде-получателе -- добавлено 06.02.05 begin .ITEM_MOVE_STATE(@acc_id,'BLK') .TYPE_GET('SREG_ACC',@acc_id,1) .FUND_GEN_NO(@in_no,1,@agent_regs_date_d) .TYPE_INIT('SREG_ACC_OPS',8) .TYPE_ASSIGN(class_id,@mode,8) .TYPE_ASSIGN(changed_item_id,@acc_id,8) .TYPE_ASSIGN(in_date,@agent_regs_date_d,8) .TYPE_ASSIGN(in_no,@in_no,8) .TYPE_ASSIGN(out_date,@agent_regs_date_d,8) .TYPE_ASSIGN(out_no,@agent_regs_no,8) .TYPE_ASSIGN(depo_doc_type,1,8) --.TYPE_ASSIGN(correspondent_id,@correspondent_id,8) .TYPE_ASSIGN(folder_id,@default_folder_id,8) .TYPE_ASSIGN(depo_acctypes_id,@depo_acctypes_id1,8) .TYPE_ASSIGN(depo_acc_number,@depo_acc_number1,8) .TYPE_ASSIGN(partner_id,@partner_id1,8) .TYPE_ASSIGN(depo_acc_name,@depo_acc_name1,8) .TYPE_ASSIGN(depo_acc_state_id,@depo_acc_state_id1,8) .TYPE_ASSIGN(fund_id,@fund_id1,8) .TYPE_ASSIGN(operator_doc_date,@operator_doc_date1,8) .TYPE_ASSIGN(operator_doc_no,@operator_doc_no1,8) .TYPE_ASSIGN(depo_acc_comment,@depo_acc_comment1,8) .TYPE_ASSIGN(depo_doc_no,@depo_doc_no1,8) .TYPE_ASSIGN(act_or_pass,@act_or_pass1,8) .TYPE_ASSIGN(list_folder_id,@list_folder_id1,8) .TYPE_ASSIGN(divid_pay_form_id,@divid_pay_form_id1,8) .TYPE_ASSIGN(acc_open_date,@acc_open_date1,8) .TYPE_ASSIGN(acc_close_date,@acc_close_date1,8) .TYPE_ASSIGN(internal_no,@internal_no1,8) .TYPE_ASSIGN(acc_id,@acc_id1,8) .TYPE_ASSIGN(stock_owner_id,@stock_owner_id1,8) .TYPE_ASSIGN(property_id,@property_id1,8) .TYPE_INSERT('SREG_ACC_OPS','ED',8) select @newdoc_id=.TYPE_FIELD(id,8) delete t_item2relation from t_item2relation,t_relation where t_item2relation.rel_id=t_relation.id and (t_relation.class_id =2) and t_item2relation.s_item_id = @newdoc_id select @rel_id=(select r.id from t_relation r, t_items i1, t_items i2 where i1.id=@acc_id and i1.type_id=r.t_type_id and i2.id=@newdoc_id and i2.type_id=r.s_type_id and r.class_id = 2) insert t_item2relation(rel_id, s_item_id, t_item_id) values(@rel_id, @newdoc_id, @acc_id) end if @acc2_id is null .EXIT_MESSAGE_PARM('Не определен ID счета в реестре фонда-получателя для заявки № ',@agent_regs_no) select @subacc_id=null select @subacctype_id=.SREG_SUBTYPE_FROM_CODE('OB') -- основной раздел на счете в реестре фонда-получателя select @subacc_id=(select max(s.id) from td_depo_subacc s where s.depo_acc_id=@acc2_id and s.depo_subacc_type_id=@subacctype_id and .ITEMS_EXISTS(s.id)) if @subacc_id is null .EXIT_MESSAGE_PARM('Невозможно получить ID основного раздела счета в реестре фонда-получателя для заявки № ',@agent_regs_no) */ declare @queries4change_id numeric(18,0) select @queries4change_id = q.id from tp_pif_queries q, td_depo_docs d where q.agent_regs_no=@agent_regs_no and q.type_queries = 4 and .ITEMS_EXISTS_BY_TYPE(q.id,'PIF_PAY_BAY') -- вставка заявки на выдачу по обмену .FUND_GEN_NO(@in_no,1,@agent_regs_date_d) .TYPE_INIT('PIF_PAY_ALLOCATE',9) .TYPE_ASSIGN(subacc_id,@subacc_id,9) .TYPE_ASSIGN(owner_id, @fund2_id, 9) .TYPE_ASSIGN(depo_doc_type,1,9) .TYPE_ASSIGN(in_date,@agent_regs_date_d,9) .TYPE_ASSIGN(in_no,@in_no,9) .TYPE_ASSIGN(out_date,@agent_regs_date_d,9) .TYPE_ASSIGN(out_no,@agent_regs_no,9) .TYPE_ASSIGN(correspondent_id,@agent_id,9) .TYPE_ASSIGN(folder_id,(.DEPO_FOLDER_DEFAULT), 9) .TYPE_ASSIGN(agent_id, @agent_id, 9) .TYPE_ASSIGN(agent_regs_date, @agent_regs_date_d, 9) .TYPE_ASSIGN(agent_regs_no, @agent_regs_no, 9) .TYPE_ASSIGN(pay_owner_id, @partner_id, 9) .TYPE_ASSIGN(pif_pay_type_id,@pif_pay_type_id,9) .TYPE_ASSIGN(type_queries,3,9) .TYPE_ASSIGN(fund4change,((select partner_name from t_partners where id=@fund_id)),9) .TYPE_INSERT('PIF_PAY_ALLOCATE','ED',9) ------- вход. документ---------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Заявка на обмен инвестиционных паев' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',16) .TYPE_ASSIGN(out_date,@out_date,16) .TYPE_ASSIGN(in_date,.OPERDAY,16) .TYPE_ASSIGN(out_no,@agent_regs_no,16) .TYPE_ASSIGN(in_time,@in_time,16) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,16) .TYPE_ASSIGN(corr_name,@corr_name,16) .TYPE_ASSIGN(document_form_id,@document_form_id,16) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,16) .TYPE_ASSIGN(owner_id,@fund_id,16) .TYPE_ASSIGN(comment,@indoc_comment,16) .TYPE_ASSIGN(send_date,.OPERDAY,16) .TYPE_INSERT('SD_INDOC','ED',16) select @indoc_id=.TYPE_FIELD(id,16) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',22) .TYPE_ASSIGN(indoc_id,@id16_1,22) .TYPE_ASSIGN(doc_id,@id9_1,22) .TYPE_INSERT('SD_INDOC2DOC','CRT',22) -------конец генерации вход. документа---------- end -- выдача по обмену end -- погашение/обмен -- Изменение анкеты лица if @doc_type=3 begin if @partner_id is null .EXIT_MESSAGE_PARM('Не определен ID пайщика для заявки на изменение анкетных данных № ',@agent_regs_no) .FUND_GEN_NO(@in_no,1,@in_date) if @is_company=0 begin -- .TYPE_GET('PARTNERS_FIS',@partner_id,1) .TYPE_INIT('PARTFIS_CHANGE',11) .TYPE_ASSIGN(class_id,@mode,11) .TYPE_ASSIGN(changed_item_id,@partner_id,11) .TYPE_ASSIGN(in_date,@in_date,11) .TYPE_ASSIGN(in_no,@in_no,11) .TYPE_ASSIGN(out_date,@in_date,11) .TYPE_ASSIGN(out_no,@out_no,11) .TYPE_ASSIGN(depo_doc_type,1,11) --.TYPE_ASSIGN(correspondent_id,@correspondent_id,11) .TYPE_ASSIGN(folder_id,@default_folder_id,11) --.TYPE_ASSIGN(partner_code,@partner_code,11) .TYPE_ASSIGN(partner_name,@partner_name,11) .TYPE_ASSIGN(j_inn,@j_inn,11) .TYPE_ASSIGN(tax_category_id,@tax_category_id,11) .TYPE_ASSIGN(jur_code,@jur_code,11) .TYPE_ASSIGN(juraddress,@juraddress,11) .TYPE_ASSIGN(postal_code,@postal_code,11) .TYPE_ASSIGN(partner_post_address,@partner_post_address,11) .TYPE_ASSIGN(real_code,@postal_code,11) .TYPE_ASSIGN(partner_address,@partner_post_address,11) .TYPE_ASSIGN(letter_receive_type,@letter_receive_type,11) --.TYPE_ASSIGN(partner_comment,@partner_comment,11) .TYPE_ASSIGN(f_passport_serial,@f_passport_serial,11) .TYPE_ASSIGN(f_passport_no,@f_passport_no,11) .TYPE_ASSIGN(f_doctype_id,@f_doctype_id,11) .TYPE_ASSIGN(f_passport_distributor,@f_passport_distributor,11) .TYPE_ASSIGN(f_passport_when,@f_passport_when,11) .TYPE_ASSIGN(f_birthday,@f_birthday,11) .TYPE_ASSIGN(f_last_name,@f_last_name,11) .TYPE_ASSIGN(f_first_name,@f_first_name,11) .TYPE_ASSIGN(f_middle_name,@f_middle_name,11) .TYPE_ASSIGN(list_folder_id,@default_folder_id,11) .TYPE_INSERT('PARTFIS_CHANGE','ED',11) select @newdoc_id=.TYPE_FIELD(id,11) ---------входящий документ----------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Анкета зарегистрированного лица для ф/л' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',17) .TYPE_ASSIGN(out_date,@out_date,17) .TYPE_ASSIGN(in_date,.OPERDAY,17) .TYPE_ASSIGN(out_no,@agent_regs_no,17) .TYPE_ASSIGN(in_time,@in_time,17) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,17) .TYPE_ASSIGN(corr_name,@corr_name,17) .TYPE_ASSIGN(document_form_id,@document_form_id,17) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,17) .TYPE_ASSIGN(owner_id,@fund_id,17) .TYPE_ASSIGN(comment,@indoc_comment,17) .TYPE_ASSIGN(send_date,.OPERDAY,17) .TYPE_INSERT('SD_INDOC','ED',17) select @indoc_id=.TYPE_FIELD(id,17) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',23) .TYPE_ASSIGN(indoc_id,@id17_1,23) .TYPE_ASSIGN(doc_id,@id11_1,23) .TYPE_INSERT('SD_INDOC2DOC','CRT',23) -------конец генерации вход. документа---------- end if @is_company=1 begin -- .TYPE_GET('PARTNERS_JUR',@partner_id,2) .TYPE_INIT('PARTJUR_CHANGE',12) .TYPE_ASSIGN(class_id,@mode,12) .TYPE_ASSIGN(changed_item_id,@partner_id,12) .TYPE_ASSIGN(in_date,@in_date,12) .TYPE_ASSIGN(in_no,@in_no,12) .TYPE_ASSIGN(out_date,@in_date,12) .TYPE_ASSIGN(out_no,@out_no,12) .TYPE_ASSIGN(depo_doc_type,1,12) -- .TYPE_ASSIGN(correspondent_id,@correspondent_id,12) .TYPE_ASSIGN(folder_id,@default_folder_id,12) --.TYPE_ASSIGN(partner_code,@partner_code,12) .TYPE_ASSIGN(partner_name,@partner_name,12) .TYPE_ASSIGN(j_inn,@j_inn,12) .TYPE_ASSIGN(tax_category_id,@tax_category_id,12) .TYPE_ASSIGN(jur_code,@jur_code,12) .TYPE_ASSIGN(juraddress,@juraddress,12) .TYPE_ASSIGN(postal_code,@postal_code,12) .TYPE_ASSIGN(partner_post_address,@partner_post_address,12) .TYPE_ASSIGN(real_code,@postal_code,12) .TYPE_ASSIGN(partner_address,@partner_post_address,12) .TYPE_ASSIGN(letter_receive_type,@letter_receive_type,12) --.TYPE_ASSIGN(partner_comment,@partner_comment,12) -- .TYPE_ASSIGN(partner_short_name,@partner_short_name,12) .TYPE_ASSIGN(regist_sertificate_no,@regist_sertificate_no,12) .TYPE_ASSIGN(regist_sertificate_date,@regist_sertificate_date,12) .TYPE_ASSIGN(regist_organisation,@regist_organisation,12) .TYPE_ASSIGN(organisation_form_id,@organisation_form_id,12) -- .TYPE_ASSIGN(j_head_last_name,@j_head_last_name,12) .TYPE_ASSIGN(list_folder_id,@default_folder_id,12) .TYPE_INSERT('PARTJUR_CHANGE','ED',12) select @newdoc_id=.TYPE_FIELD(id,12) ---------входящий документ----------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Анкета зарегистрированного лица для ю/л' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',18) .TYPE_ASSIGN(out_date,@out_date,18) .TYPE_ASSIGN(in_date,.OPERDAY,18) .TYPE_ASSIGN(out_no,@agent_regs_no,18) .TYPE_ASSIGN(in_time,@in_time,18) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,18) .TYPE_ASSIGN(corr_name,@corr_name,18) .TYPE_ASSIGN(document_form_id,@document_form_id,18) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,18) .TYPE_ASSIGN(owner_id,@fund_id,18) .TYPE_ASSIGN(comment,@indoc_comment,18) .TYPE_ASSIGN(send_date,.OPERDAY,18) .TYPE_INSERT('SD_INDOC','ED',18) select @indoc_id=.TYPE_FIELD(id,18) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',24) .TYPE_ASSIGN(indoc_id,@id18_1,24) .TYPE_ASSIGN(doc_id,@id12_1,24) .TYPE_INSERT('SD_INDOC2DOC','CRT',24) -------конец генерации вход. документа---------- end delete t_item2relation from t_item2relation,t_relation where t_item2relation.rel_id=t_relation.id and (t_relation.class_id =2) and t_item2relation.s_item_id = @newdoc_id select @rel_id=(select r.id from t_relation r, t_items i1, t_items i2 where i1.id=@partner_id and i1.type_id=r.t_type_id and i2.id=@newdoc_id and i2.type_id=r.s_type_id and r.class_id = 2) insert t_item2relation(rel_id, s_item_id, t_item_id) values(@rel_id, @newdoc_id, @partner_id) end -- изменение анкеты if @doc_type = 5 begin .TYPE_INIT('TP_PIF_PAYS_MOVES',13) .TYPE_ASSIGN(agent_regs_no,@agent_regs_no,13) .TYPE_ASSIGN(agent_regs_date,@agent_regs_date_d,13) .TYPE_ASSIGN(owner_id,@fund_id,13) .TYPE_ASSIGN(stock_qty,@stock_qty,13) .TYPE_ASSIGN(pay_owner_id,@partner_id,13) .TYPE_ASSIGN(d_pay_owner_id,@change_partner_id,13) .TYPE_ASSIGN(folder_id,(.DEPO_FOLDER_DEFAULT), 13) .TYPE_ASSIGN(subacc_id,@subacc_id,13) .TYPE_ASSIGN(type_queries,0,13) .TYPE_INSERT('TP_PIF_PAYS_MOVES','ED',13) ---------входящий документ----------- select @document_form_id=1 select @corr_id=id from t_partners where partner_code='NRC' select @corr_name=partner_name from t_partners where id = @corr_id select @indoc_comment=partner_short_name from t_partners where id=@partner_id select @indoc_type_id=id FROM t_sd_indoc_types WHERE indoc_name = 'Передаточное распоряжение' --время регистрации if not exists(select 1 from t_sd_indocs where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id,'SD_INDOC')) select @in_time=dateadd(Hour,11,@in_time) else begin --select @in_time=dateadd(Hour,11,@in_time) select @in_time=dateadd(mi,1,max(t.in_time)) from t_sd_indocs t where in_date=.OPERDAY and .ITEMS_EXISTS_BY_TYPE(t.id,'SD_INDOC') end exec ap_calc_end_period @date_beg = @indate, @days_beg = -1, @date_end = @out_date out, @days =@days .TYPE_INIT('SD_INDOC',19) .TYPE_ASSIGN(out_date,@out_date,19) .TYPE_ASSIGN(in_date,.OPERDAY,19) .TYPE_ASSIGN(out_no,@agent_regs_no,19) .TYPE_ASSIGN(in_time,@in_time,19) .TYPE_ASSIGN(indoc_type_id,@indoc_type_id,19) .TYPE_ASSIGN(corr_name,@corr_name,19) .TYPE_ASSIGN(document_form_id,@document_form_id,19) .TYPE_ASSIGN(folder_id,.DEPO_FOLDER_DEFAULT,19) .TYPE_ASSIGN(owner_id,@fund_id,19) .TYPE_ASSIGN(comment,@indoc_comment,19) .TYPE_ASSIGN(send_date,.OPERDAY,19) .TYPE_INSERT('SD_INDOC','ED',19) select @indoc_id=.TYPE_FIELD(id,19) select @indoc_id_s=convert(varchar(30),@indoc_id) exec ap_sd_indoc_fix @indoc_id_s if @@error<>0 begin if @@trancount>0 rollback tran return end .TYPE_INIT('SD_INDOC2DOC',25) .TYPE_ASSIGN(indoc_id,@id19_1,25) .TYPE_ASSIGN(doc_id,@id13_1,25) .TYPE_INSERT('SD_INDOC2DOC','CRT',25) -------конец генерации вход. документа---------- end .UPDATE_STATE --commit tran .END $ENDTEXT(17224990) $ENDACTION $ACTION(PIFPAY_IN) name=Расчет паев (выдача) class=9 form=null target_state=null procedure=ap_pifpay_in patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224990) create proc dbo.%PROC% @payment_sum money, @agent_dog_id numeric, @owner_id numeric, @operday datetime, @paydate datetime, @stock_price decimal(18,8) out, @stock_qty .PIF_PAY_TYPE out, @total_sum money out, @agent_comis_sum money out, @agent_proc money out, @pay_partner_id numeric = null as begin begin tran declare @agent_comis money, @stock_price_allocate decimal(18,8), @stock_price_calc decimal(36,16), @stock_qty_calc decimal(36,16) -- Цена пая select @stock_price = .PIF_PAY_PRICE(@owner_id,@paydate) if @stock_price is null .EXIT_MESSAGE('Для данного фонда не задана цена пая') -- Если надбавка зависит от общей суммы инвестиций -- DM 06.03.06 -- declare @for_comis_sum money, @price_last_interval decimal(18,8), @date_last_interval datetime, @pay_qty .PIF_PAY_TYPE, @saldo_qty money .ID_VAR(@fund_plan_id) .ID_VAR(@acc96_id) if (select all_pays from tb_contracts where id=@agent_dog_id)=1 -- % от (сумма взноса + сумма остатка) -- and (select fond_type from td_pay_fond where id=@owner_id)=2 -- интервальный фонд begin select @date_last_interval = (select max(date_int_e) from tp_pif_intervals where fund_id=@owner_id and date_int_e < @paydate and .ITEMS_EXISTS(tp_pif_intervals.id)) if @date_last_interval is null select @price_last_interval = @stock_price else select @price_last_interval = .PIF_PAY_PRICE(@owner_id,@date_last_interval) select @fund_plan_id = .ARG_PLAN('PIF_ACCOUNT') select @acc96_id = .SD_ACCID_FROM_EFFNO(@fund_plan_id,'96.1') .SALDO_FAST(@owner_id,@fund_plan_id,@paydate,@acc96_id,K,Y,'partners',@pay_partner_id,N,N,N,@saldo_qty) select @pay_qty=.PAYS_QTY_CALC(@saldo_qty,@owner_id) .PIF_ROUND_PAYS(@owner_id, @pay_qty, @pay_qty) select @for_comis_sum = @pay_qty * @price_last_interval .PIF_ROUND_MONEY(@owner_id,@for_comis_sum,@for_comis_sum) select @for_comis_sum = isnull(@for_comis_sum,0) + @payment_sum end else select @for_comis_sum = @payment_sum -- Процент комиссии if @agent_dog_id is not null begin .CALC_AGENT_COMIS(@for_comis_sum,@agent_comis,@agent_dog_id,'RUR',@operday,@owner_id,1) select @agent_proc=@agent_comission_percent_ac --> определен в шаблоне .CALC_AGENT_COMIS end else select @agent_proc=0 -- Цена размещения пая if isnull(@agent_proc,0) = 0 begin -- .PIF_ROUND_MONEY(@owner_id,@stock_price,@stock_price_allocate) select @stock_qty_calc = convert(decimal(36,16),(convert(decimal(36,16),@payment_sum)*convert(float,1.0) / convert(decimal(36,16),@stock_price))) .PIF_ROUND_PAYS(@owner_id, @stock_qty_calc, @stock_qty_calc) select @stock_qty=convert(.PIF_PAY_TYPE,@stock_qty_calc) select @total_sum = @payment_sum select @agent_comis_sum = 0 end else begin select @stock_price_calc = @stock_price + @stock_price * @agent_proc / 100.0 .PIF_ROUND_PRICE(@owner_id,@stock_price_calc,@stock_price_allocate) -- Количество паев if isnull(@stock_price_allocate,0)=0 select @stock_qty_calc = 0 else select @stock_qty_calc = convert(decimal(36,16),(convert(decimal(36,16),@payment_sum)*convert(float,1.0) / convert(decimal(36,16),@stock_price_allocate))) .PIF_ROUND_PAYS(@owner_id, @stock_qty_calc, @stock_qty_calc) select @stock_qty=convert(.PIF_PAY_TYPE,@stock_qty_calc) -- Сумма за паи select @total_sum = .ROUND_MONEY((@stock_qty * convert(.PIF_PAY_TYPE,@stock_price)),2) -- Сумма комиссии select @agent_comis_sum = @payment_sum - @total_sum select @agent_comis_sum = .ROUND_MONEY(@agent_comis_sum,2) /* -- IRCOL - Сумма комиссии select @agent_comis_sum = @stock_price * @agent_proc / 100 declare @agent_comis_sum_calc money select @agent_comis_sum_calc=.ROUND_MONEY(@agent_comis_sum,2) select @agent_comis_sum = .ROUND_MONEY((@stock_qty * @agent_comis_sum_calc),2) -- IRCOL - Балансовая сумма select @total_sum = @payment_sum - @agent_comis_sum select @total_sum = .ROUND_MONEY(@total_sum,2) */ end commit tran end $ENDTEXT(17224990) $ENDACTION $ACTION(PIFPAY_OUT) name=Расчет паев (выкуп) - старый (без партий) class=9 form=null target_state=null procedure=ap_pifpay_out patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224990) create proc dbo.%PROC% @stock_qty .PIF_PAY_TYPE, @is_manual_tax integer, @tax_sum money, @alloc_sum money, @tax_percent money, @pay_owner_id numeric, @agent_dog_id numeric, @owner_id numeric, @operday datetime, @paydate datetime, @stock_price money out, -- цена пая на @paydate @payment_sum money out, -- сумма оплаты = цена пая на кол-во за вычетом комиссии и налога -- @total_sum money out, -- сумма за паи (цена пая без комиссии на кол-во) @agent_comis_sum money out, -- агенту = (цена пая на кол-во) - (цена пая без комиссии на кол-во) @agent_proc money out, @tax money out -- сумма налога as begin begin tran declare @agent_comis money, @stock_price_allocate money, @stock_price_calc decimal(36,16), @for_tax_sum money .ID_VAR(@tax_city_id) -- Цена пая select @stock_price = .PIF_PAY_PRICE(@owner_id,@paydate) if @stock_price is null .EXIT_MESSAGE('Для данного фонда не задана цена пая') .PIF_ROUND_PAYS(@owner_id, @stock_qty, @stock_qty) -- Получение процента комиссии if @agent_dog_id is not null begin .CALC_AGENT_COMIS(@stock_price,@agent_comis,@agent_dog_id,'RUR',@operday,@owner_id,2) select @agent_proc=@agent_comission_percent_ac --> определен в шаблоне .CALC_AGENT_COMIS end else select @agent_proc=0 -- Цена выкупа пая select @stock_price_calc = @stock_price - @stock_price * isnull(@agent_proc,0) / 100.0 .PIF_ROUND_MONEY(@owner_id,@stock_price_calc,@stock_price_allocate) -- Cумма за паи --select @total_sum = .ROUND_MONEY((@stock_qty * @stock_price_allocate),2) -- Сумма комиссии select @agent_comis_sum = .ROUND_MONEY((@stock_qty * @stock_price - @stock_qty * @stock_price_allocate),2) -- Налог if @is_manual_tax=0 begin -- select @for_tax_sum = @total_sum - isnull(@alloc_sum,0) select @for_tax_sum = .ROUND_MONEY((@stock_qty * @stock_price_allocate - isnull(@alloc_sum,0)),2) if @for_tax_sum < 0 select @for_tax_sum=0 if @tax_percent is not null begin select @tax = @for_tax_sum * @tax_percent / 100.0 select @tax = .ROUND_MONEY(@tax,2) end else begin .CALC_TAX('INCOME_TAX',@pay_owner_id,@for_tax_sum,@operday,@tax,@tax_city_id,@tax_percent) end select @tax = .ROUND_MONEY(@tax,2) end else select @tax = isnull(@tax_sum,0) -- без копеек select @tax = round(@tax,0) -- Сумма к выплате select @payment_sum = .ROUND_MONEY((@stock_qty * @stock_price - @agent_comis_sum - @tax),2) commit tran end $ENDTEXT(17224990) $ENDACTION $ACTION(PIFPAY_OUT_NEW) name=Расчет паев (погашение) - новый class=9 form=null target_state=null procedure=ap_pifpay_out_new patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224990) create proc dbo.%PROC% @stock_qty .PIF_PAY_TYPE, @alloc_sum money, @pay_owner_id numeric, @agent_dog_id numeric, @owner_id numeric, @operday datetime, @paydate datetime, @partdate datetime, @agent_proc money, @stock_price decimal(18,8) out, -- цена пая на @paydate @payment_sum money out, -- сумма оплаты = цена пая на кол-во за вычетом комиссии и налога @agent_comis_sum money out, -- агенту = (цена пая на кол-во) - (цена пая без комиссии на кол-во) @agent_proc_calc money out as begin begin tran declare @agent_comis money, @stock_price_allocate decimal(18,8), @stock_price_calc decimal(36,16) -- Цена пая select @stock_price = .PIF_PAY_PRICE(@owner_id,@paydate) if @stock_price is null .EXIT_MESSAGE('Для данного фонда не задана цена пая') .PIF_ROUND_PAYS(@owner_id, @stock_qty, @stock_qty) -- Получение процента комиссии if isnull(@agent_dog_id,0) <> 0 begin select @partdate=.DATE_NORM(@partdate,B) select @operday=.DATE_NORM(@operday,B) if @agent_proc is null -- DM 09.03.06 -- if isnull((select period_type from tb_contracts where id=@agent_dog_id),0)=0 select @agent_proc_calc=.SD_AGENT_PROC4PARTION(@agent_dog_id,@operday,@partdate) -- порог в днях else select @agent_proc_calc=.SD_AGENT_PROC4INTERVAL(@agent_dog_id,@operday,@partdate,@owner_id) -- порог в интервалах else select @agent_proc_calc=@agent_proc end else select @agent_proc_calc=0 -- Цена выкупа пая select @stock_price_calc = @stock_price - @stock_price * isnull(@agent_proc_calc,0) / 100.0 .PIF_ROUND_PRICE(@owner_id,@stock_price_calc,@stock_price_allocate) -- Сумма комиссии select @agent_comis_sum = round((@stock_qty * @stock_price - @stock_qty * @stock_price_allocate),2) -- Сумма к выплате select @payment_sum = round((@stock_qty * @stock_price - isnull(@agent_comis_sum,0)),2) commit tran end $ENDTEXT(17224990) $ENDACTION $ACTION(PIFPAY_OUT_PARTS) name=Обработка партий при погашении class=9 form=null target_state=null procedure=ap_pifpay_out_parts patterns=null user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224990) create proc dbo.%PROC% @owner_id numeric, -- фонд @agent_dog_id numeric, -- агентский договор @pay_owner_id numeric, -- пайщик @out_doc_id numeric, -- документ-основание снятия (заявка,переход прав,обмен) @operday datetime, -- дата проведения операции @paydate datetime, -- дата цены пая @qty_all .PIF_PAY_TYPE, -- общее кол-во для погашения @is_manual_tax integer, -- = 1 - сумма налога вводится вручную @tax_percent money, -- % налога установленный вручную, @tax_sum money, -- сумма налога, установленная вручную @dop_sum_ed money, -- расходы (доп.или подтвержденные), введенные вручную @dop_sum_type numeric, -- тип расчета расходов (спр.5051) @is_operation integer, -- = 0 - без проводок @oper_doc_id numeric, -- id поручения, @oper_action_id numeric, -- id метода поручения @oper_action_history_id numeric, @p_sum_all money out, -- вся сумма оплаты (вычетом комиссии и налога) @comis_all money out, -- вся скидка @tax_all money out, -- вся сумма налога @out_sum money out, -- списанная балансовая сумма @dop_sum_all money out, -- все доп.расходы @partdate_last datetime out, -- дата посл.партии @qty_all_res .PIF_PAY_TYPE out, -- количество @loss_used_sum money out, -- использованный убыток @loss_used @income_sum money out -- прибыль @income as declare @calc_type int -- 1 - по изменению %, иначе - по каждой партии select @calc_type=1 --************* Комиссия зависит от суммы оплаты ****************** declare @is_agent_proc4sum int if exists (select 1 from tb_settings_comis setts, tb_contracts c where c.id=@agent_dog_id and setts.settings_id=c.id and setts.profit_type_id=12 --скидка зависит от суммы оплаты! and .ITEMS_EXISTS(setts.id)) select @is_agent_proc4sum=1 else select @is_agent_proc4sum=0 --***************************************************************** declare @plan_id numeric, @acc_id numeric, @acc7641_id numeric, @acc7642_id numeric, @subc_type_id numeric, @part_subc_type_id numeric, @pp_id numeric, @get_date datetime, @pays_acc_mode int declare @part_id numeric, @common_part_id numeric, @new_part_id numeric, @new_pay_owner_id numeric, @partdate datetime, @part_sum money, @part_sum_op money, @part_qty_bal money, @part_qty_op money, @part_qty_op_cred money, @part_qty_calc .PIF_PAY_TYPE, @all_sum_part money, -- вся сумма по партии, зачисленная на 86.1 @dop_sum_part money, -- доп.расходы по партии @dop_sum_more money, -- доп.расходы, введенные вне учета @dop_sum_op money, @qty_all_bal money, @qty_bal money, @qty_ost money, @part_price decimal(36,16), @oper_doc_type varchar(255), @out_doc_name varchar(255), @oper_id numeric, @cred_id numeric declare @calc_qty .PIF_PAY_TYPE, @calc_sum money, @calc_sum_all money, @stock_price decimal(18,8), @p_sum money, @comis money, @comis_proc money, @comis_proc_last money, @for_tax_sum money, @tax_city_id numeric declare @op_pay_owner_id numeric, @op_new_pay_owner_id numeric --.SR_ACC_BAL('PIF_ACCOUNT',@pay_owner_id,'96.1','partners',@qty_bal) --select @calc_qty=.PAYS_QTY_CALC(@qty_bal,@owner_id) --select @calc_qty select @plan_id=.ARG_PLAN('PIF_ACCOUNT') select @acc_id=.SD_ACCID_FROM_EFFNO(@plan_id,'96.1') select @acc7641_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.4.1') select @acc7642_id=.SD_ACCID_FROM_EFFNO(@plan_id,'76.4.2') select @subc_type_id=.SUBCTYPE_FROM_CODE('partners') select @part_subc_type_id=.SUBCTYPE_FROM_CODE('doc_item') select @get_date=dateadd(dd,1,(.DATE_NORM(@operday,B))) 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 @pays_acc_mode=isnull(pays_acc_mode,1) from td_pay_fond where id=@owner_id select @qty_all_bal=isnull((.PAYS_QTY_BAL(@qty_all,@owner_id)),0) select @new_pay_owner_id= @pay_owner_id select @oper_doc_type=upper(.ITEM_TYPE_CODE(@oper_doc_id)) select @out_doc_name=.ITEM_NAME(@out_doc_id) if @oper_doc_type='PIF_PAY_REPORT' select @cred_id=@acc7642_id if @oper_doc_type='TP_PIF_PAYS_MOVES' begin select @cred_id=@acc_id select @new_pay_owner_id=d_pay_owner_id from tp_pif_queries where id=@oper_doc_id end -- новая партия select @common_part_id=.SD_COMMON_PART4PAYS if @common_part_id is null exec ap_insert_common_part4pays select @common_part_id=.SD_COMMON_PART4PAYS if @cred_id=@acc_id if @pays_acc_mode=1 select @new_part_id=@common_part_id else select @new_part_id=@out_doc_id .CHECK_DEFAULT_PIF_PARTNER(@owner_id,@pay_owner_id,@op_pay_owner_id) .CHECK_DEFAULT_PIF_PARTNER(@owner_id,@new_pay_owner_id,@op_new_pay_owner_id) -- проверка на наличие select @qty_bal=isnull((select sum(isnull(ol.qty_sum*ol.deb_or_cred*(-1),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<@get_date and os.item_id=@op_pay_owner_id),0) if @is_operation=1 and (@qty_bal < @qty_all_bal or @qty_bal=0) .EXIT_MESSAGE_PARM('Не хватает паев на балансе',@out_doc_name) if @qty_bal < @qty_all_bal select @qty_all_bal=@qty_bal -- партии select @p_sum_all=0, @comis_all=0, @out_sum=0, @dop_sum_all=0, @comis_proc_last=null, @qty_ost=@qty_all_bal if @pays_acc_mode in (1,2) declare parts cursor for select osp.item_id, max(d.in_date), sum(isnull(ol.sum_bal*ol.deb_or_cred*(-1),0)), sum(isnull(ol.qty_sum*ol.deb_or_cred*(-1),0)) from t_oper_subconto os, t_oper_subconto osp, t_oper_list ol, t_operations o, td_depo_docs d 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 osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id and o.op_date<@get_date and os.item_id=@op_pay_owner_id and osp.item_id=d.id group by osp.item_id, d.in_date, d.in_no having sum(isnull(ol.qty_sum*ol.deb_or_cred*(-1),0))>0 order by d.in_date, d.in_no for read only if @pays_acc_mode=3 declare parts cursor for select osp.item_id, max(d.in_date), sum(isnull(ol.sum_bal*ol.deb_or_cred*(-1),0)), sum(isnull(ol.qty_sum*ol.deb_or_cred*(-1),0)) from t_oper_subconto os, t_oper_subconto osp, t_oper_list ol, t_operations o, td_depo_docs d 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 osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id and o.op_date<@get_date and os.item_id=@op_pay_owner_id and osp.item_id=d.id group by osp.item_id, d.in_date, d.in_no having sum(isnull(ol.qty_sum*ol.deb_or_cred*(-1),0))>0 order by d.in_date desc, d.in_no desc for read only open parts fetch parts into @part_id, @partdate, @part_sum, @part_qty_bal while .CURSOR_STATE=0 and @qty_ost>0 begin if @part_qty_bal <= @qty_ost -- вся партия begin select @part_qty_op = @part_qty_bal select @part_sum_op = @part_sum end else -- часть партии begin select @part_qty_op = @qty_ost select @part_price = convert(decimal(36,16),convert(float,1.0)*convert(decimal(36,16),@part_sum) / convert(decimal(36,16),@part_qty_bal)) select @part_sum_op = convert(money,round((convert(decimal(36,16),@part_qty_op) * @part_price),2)) end select @qty_ost = @qty_ost - @part_qty_op select @part_qty_calc = .PAYS_QTY_CALC(@part_qty_op,@owner_id) if @oper_doc_type='TP_PIF_PAYS_MOVES' select @part_qty_op_cred=@part_qty_op else select @part_qty_op_cred=0 -- доп.расходы по партии if isnull(@oper_doc_type,'') not in ('TP_PIF_PAYS_MOVES') and @dop_sum_type in (1,2,3) and @pay_owner_id=@op_pay_owner_id begin select @all_sum_part=null, @dop_sum_part=null, @dop_sum_more=null if @part_id=@common_part_id -- по общей партии begin -- вся сумма, зачисленная на 86.1 по этому пайщику на общую партию select @all_sum_part=(select sum(isnull(ol.sum_bal,0)) from t_oper_subconto os, t_oper_subconto osp, 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 ol.deb_or_cred=-1 and os.id=ol.id and os.subconto_type_id=@subc_type_id and osp.id=ol.id and osp.subconto_type_id=@part_subc_type_id and o.op_date<@get_date and os.item_id=@pay_owner_id and osp.item_id=@part_id) -- надбавка = вся сумма, оплаченная по заявкам общей партии этого пайщика и переведенная на нее с др.пайщиков - @all_sum_part if @dop_sum_type in (1,2) select @dop_sum_part=(select sum(isnull(ol.sum_bal,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=@acc7641_id -- сумма оплаты по заявкам or (ol.acc_id=@acc_id and ol.deb_or_cred=-1 and upper(.ITEM_TYPE_CODE(o.item_id)) in ('TP_PIF_PAYS_MOVES','PIF_START_PART')) -- по передаче паев, начальному зачислению ) and os.id=ol.id and os.subconto_type_id=@subc_type_id and os.item_id=@pay_owner_id and o.item_id in (select o1.item_id from t_oper_subconto os1, t_oper_subconto osp1, t_oper_list ol1, t_operations o1 where o1.partner2plan_id=@pp_id and ol1.head_id=o1.id and ol1.acc_id=@acc_id and os1.id=ol1.id and os1.subconto_type_id=@subc_type_id and osp1.id=ol1.id and osp1.subconto_type_id=@part_subc_type_id and o1.op_date<@get_date and os1.item_id=@pay_owner_id and osp1.item_id=@part_id) ) - @all_sum_part end else begin -- сумма по 86.1 в этом документе-партии select @all_sum_part=(select sum(isnull(ol.sum_bal,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 o.item_id=@part_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and os.item_id=@pay_owner_id) -- надбавка = сумма по 76.4.1 в этом документе-партии (т.е. вся оплата по этой партии) - @all_sum_part if @dop_sum_type in (1,2) select @dop_sum_part=(select 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=@acc7641_id and o.item_id=@part_id and os.id=ol.id and os.subconto_type_id=@subc_type_id and os.item_id=@pay_owner_id) - @all_sum_part end -- отдельные доп.расходы if @dop_sum_type in (1,3) begin select @dop_sum_more=(select sum(e.exp_sum) from td_pif_pay_expenses e where e.fund_id=@owner_id and e.doc_id=@part_id and e.partner_id=@pay_owner_id and e.exp_type=2 and .ITEMS_EXISTS(e.id)) /* -- реальные расходы при обмене if (select type_queries from tp_pif_queries where id = @part_id) = 3 and isnull(@dop_sum_more,0)=0 select @dop_sum_more= (select tx1.alloc_sum from tp_extract2claim tx1 where tx1.claim_id = (select max(tq.id) from tp_extract2claim tx2, tp_pif_queries tq where tx2.claim_id=tq.id and tq.type_queries = 4 and tx2.pay_partner_id = @pay_owner_id and tx2.sum_for_transfer = (select sum_for_transfer from tp_pif_queries where id = @part_id) and tq.id < @part_id and .ITEMS_EXISTS(tq.id))) */ if isnull(@dop_sum_more,0)>0 -- указаны реальные расходы по партии select @dop_sum_part=@dop_sum_more else -- указаны доп.расходы по партии select @dop_sum_part=isnull(@dop_sum_part,0) + isnull((select sum(e.exp_sum) from td_pif_pay_expenses e where e.fund_id=@owner_id and e.doc_id=@part_id and e.partner_id=@pay_owner_id and e.exp_type=1 and .ITEMS_EXISTS(e.id)),0) end -- отдельные доп.расходы if @part_sum_op < @all_sum_part -- списывается часть партии begin select @dop_sum_op=convert(money,round(convert(decimal(36,16),@dop_sum_part)*(convert(decimal(36,16),(convert(float,1.0)*convert(decimal(36,16),@part_sum_op)/convert(decimal(36,16),@all_sum_part)))),2)) if isnull(@dop_sum_more,0)>0 select @dop_sum_op=@dop_sum_op - @part_sum_op end else begin if isnull(@dop_sum_more,0)>0 select @dop_sum_op=@dop_sum_part - @part_sum_op else select @dop_sum_op=@dop_sum_part end end -- проводка по списанию if @is_operation=1 begin .OPERATION(@owner_id,@plan_id,@operday,@acc_id,@cred_id,@part_sum_op,'RUR',@operday,@part_qty_op,@part_qty_op_cred,@out_doc_name,N,@oper_id,@oper_doc_id,@oper_action_id,@oper_action_history_id) .UPDATE_SUBC(D,'partners',@op_pay_owner_id,@oper_id) .UPDATE_SUBC(D,'doc_item',@part_id,@oper_id) if @cred_id=@acc7642_id begin .UPDATE_SUBC(K,'partners',@new_pay_owner_id,@oper_id) .UPDATE_SUBC(K,'pifdocs',@out_doc_id,@oper_id) end if @cred_id=@acc_id begin .UPDATE_SUBC(K,'partners',@op_new_pay_owner_id,@oper_id) .UPDATE_SUBC(K,'doc_item',@new_part_id,@oper_id) end .SUBC_CHECK(@oper_id) end -- списанная балансовая сумма select @out_sum=@out_sum + @part_sum_op -- списанные расходы select @dop_sum_all=@dop_sum_all + isnull(@dop_sum_op,0) --***Если комиссия "по сумме" - пропускаем расчет "по времени владения" ******* if isnull(@is_agent_proc4sum,0)!=1 begin --**@is_agent_proc4sum* -- расчет -- DM 09.03.06 -- if isnull((select period_type from tb_contracts where id=@agent_dog_id),0)=0 select @comis_proc=.SD_AGENT_PROC4PARTION(@agent_dog_id,@operday,@partdate) -- порог в днях else select @comis_proc=.SD_AGENT_PROC4INTERVAL(@agent_dog_id,@operday,@partdate,@owner_id) -- порог в интервалах if isnull(@calc_type,0)=1 and @comis_proc_last is not null and isnull(@comis_proc,0)=@comis_proc_last -- та же комиссия select @calc_qty=@calc_qty+@part_qty_calc, @calc_sum=isnull(@calc_sum,0)+@part_sum_op+isnull(@dop_sum_op,0) else select @calc_qty=@part_qty_calc, @calc_sum=@part_sum_op+isnull(@dop_sum_op,0), @p_sum_all=@p_sum_all+isnull(@p_sum,0), @comis_all=@comis_all+isnull(@comis,0) select @calc_sum_all=isnull(@calc_sum_all,0)+@part_sum_op+isnull(@dop_sum_op,0) select @p_sum=0, @comis=0, @comis_proc_last=isnull(@comis_proc,0) exec ap_pifpay_out_new @calc_qty, @calc_sum, @pay_owner_id, @agent_dog_id, @owner_id, @operday, @paydate, @partdate, @comis_proc, @stock_price out, @p_sum out, @comis out, @comis_proc out if @@error != 0 begin if @@trancount<>0 rollback tran return end end --**@is_agent_proc4sum* else select @calc_qty=isnull(@calc_qty,0)+@part_qty_calc, @calc_sum=isnull(@calc_sum,0)+@part_sum_op+isnull(@dop_sum_op,0) fetch parts into @part_id, @partdate, @part_sum, @part_qty_bal end close parts .DEALLOCATE parts --***если комиссия "по сумме" - теперь делаем пропущенный расчет ******* if isnull(@is_agent_proc4sum,0)=1 begin -- расчет .CALC_AGENT_COMIS(@calc_sum,@comis,@agent_dog_id,'RUR',@operday,@owner_id,12) select @comis_proc=@agent_comission_percent_ac --> из шаблона! exec ap_pifpay_out_new @calc_qty, -- из курсора @calc_sum, -- из курсора @pay_owner_id, --не исп. внутри процедуры !!! @agent_dog_id, @owner_id, @operday, @paydate, @partdate, @comis_proc, @stock_price out, @p_sum out, @comis out, @comis_proc out if @@error != 0 begin if @@trancount<>0 rollback tran return end end --**@is_agent_proc4sum* select @p_sum_all=@p_sum_all+isnull(@p_sum,0), @comis_all=@comis_all+isnull(@comis,0), @partdate_last=@partdate, @qty_all_res=.PAYS_QTY_CALC(@qty_all_bal,@owner_id) -- доп.расходы, введенные вручную if @dop_sum_type in (1,2,3,4) begin select @dop_sum_all=isnull(@dop_sum_all,0) + isnull(@dop_sum_ed,0) select @calc_sum_all=isnull(@calc_sum_all,0) + isnull(@dop_sum_ed,0) end -- подтвержденные расходы, введенные вручную if @dop_sum_type=5 begin select @dop_sum_all=isnull(@dop_sum_ed,0)-@out_sum select @calc_sum_all=isnull(@dop_sum_ed,0) end -- расчет налога if @is_manual_tax=0 begin select @for_tax_sum = @p_sum_all - isnull(@calc_sum_all,0) select @income_sum = @for_tax_sum -- расчет накопленного убытка select @loss_used_sum = 0 declare @loss_sum money declare @ctrl_comp numeric select @ctrl_comp = .GET_CTRL_COMP_ID(@owner_id) select @loss_sum = (select (-1)*sum(case when (x.sum_for_transfer_c+x.tax_sum - x.alloc_sum)>0 then isnull(x.loss_used,0) else x.sum_for_transfer_c+x.tax_sum - x.alloc_sum end ) from tp_extract2claim x, td_depo_docs dd, td_pay_fond pf, tp_pif_queries tc where x.claim_id=tc.id and tc.type_queries=2 and x.pay_partner_id=@pay_owner_id and x.extract_id=dd.id and dd.owner_id=pf.id and pf.ctrl_comp_id=@ctrl_comp and year(@operday)=year(dd.in_date) and (x.confirm_status=1 or .ITEM_STATE_CODE(x.id)='FIX') and .ITEM_TYPE_CODE(x.extract_id)='PIF_PAY_REPORT') if @for_tax_sum > 0 and @loss_sum > 0 if @loss_sum > @for_tax_sum begin select @loss_used_sum = @for_tax_sum select @for_tax_sum = 0 end else select @for_tax_sum = @for_tax_sum - @loss_sum, @loss_used_sum = @loss_sum -- if @for_tax_sum < 0 select @for_tax_sum=0 if @tax_percent is not null select @tax_all = @for_tax_sum * @tax_percent / 100.0 else begin .CALC_TAX('INCOME_TAX',@pay_owner_id,@for_tax_sum,@operday,@tax_all,@tax_city_id,@tax_percent) end end else select @tax_all = isnull(@tax_sum,0) select @tax_all = round(@tax_all,0) select @p_sum_all=@p_sum_all-isnull(@tax_all,0) $ENDTEXT(17224990) $ENDACTION $ACTION(QUERY_IRCOL_IMP) name=Импорт заявок class=1 form=D_QUERY_IRCOL_IMP target_state=null procedure=ap_10000000000089308 patterns=GENERIC_IMPORT user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(17224991) create procedure dbo.%PROC% @req_type varchar(255) = null, @req_date varchar(30) = null, @req_num varchar(255) = null, @stock varchar(255) = null, @agen_agrt varchar(255) = null, @person_typ varchar(255) = null, @fst_name varchar(255) = null, @sec_name varchar(255) = null, @fam_name varchar(255) = null, @inn varchar(255) = null, @doc_type varchar(255) = null, @doc_seria varchar(255) = null, @doc_nr varchar(255) = null, @nalog_stat varchar(255) = null, @bank_name varchar(255) = null, @bik varchar(255) = null, @kor_count varchar(255) = null, @branch_nam varchar(255) = null, @branch_cnt varchar(255) = null, @count_num varchar(255) = null, @bank_city varchar(255) = null, @discount varchar(255) = null, @count_shar varchar(255) = null as begin declare @id numeric declare @r_date datetime declare @partner_id numeric declare @mess varchar(255) declare @doc_type_id numeric declare @partner_code varchar(255) /* Проверка полей */ if @req_type is null .EXIT_MESSAGE('Не указан тип заявки') select @r_date = convert(datetime, @req_date) if @r_date is null .EXIT_MESSAGE('Не указана дата заявки') if @req_num is null .EXIT_MESSAGE('Не указан номер заявки') -- ФОНД declare @fund_id numeric select @fund_id = max(i.item_id) from t_item2class i, t_classvalues v, t_classes c where rtrim(ltrim(upper(@stock))) like ltrim(upper(i.code))+'%' -- код не умещ. в 30 символов! --ltrim(upper(i.code)) = ltrim(upper(@stock)) and i.value_id=v.id and v.class_id=c.id and upper(c.code)='FUND_IMPORT_CODE' and .ITEMS_EXISTS_BY_TYPE(i.item_id,'PIF_FUND') if @fund_id is null .EXIT_MESSAGE('Фонд не определен, внесите значение классификатора "Код фонда при импорте"') declare @agent_id numeric declare @agent_dog_id numeric if rtrim(ltrim(@agen_agrt)) is null begin -- Поиск договора с УК select @agent_id = td_pay_fond.ctrl_comp_id from td_pay_fond where td_pay_fond.id = @fund_id if @agent_id is null .EXIT_MESSAGE('У фонда не задана управляющая компания') select @agent_dog_id = tb_contracts.id from tb_contracts, td_depo_docs where tb_contracts.client_id = @agent_id and tb_contracts.dog_type_id = 2 and tb_contracts.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') if @agent_dog_id is null .EXIT_MESSAGE('Для управляющей компании не задан договор комиссии') end else begin select @agent_dog_id = tb_contracts.id, @agent_id = tb_contracts.client_id from tb_contracts, td_depo_docs, t_item2class i, t_classvalues v, t_classes c where tb_contracts.dog_type_id = 2 and tb_contracts.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') and i.item_id=tb_contracts.id and ltrim(upper(i.code)) = ltrim(upper(@agen_agrt)) and i.value_id=v.id and v.class_id=c.id and upper(c.code)='AGENT_IMPORT_CODE' and upper(v.code)='AGENT_IMPORT_CODE' select @mess = 'Для данного фонда не найден договор номер ' + @agen_agrt if @agent_dog_id is null .EXIT_MESSAGE(@mess) end if charindex('ФИ', upper(@person_typ))>0 begin if isnull(replace(@doc_nr,' ',''),'')='' and isnull(replace(@doc_seria,' ',''),'')='' .EXIT_MESSAGE('Не заданы паспортные данные физ.лица') select @partner_id = t_partners.id from t_partners, t_partners_fis f where t_partners.id = f.id and (isnull(upper(replace(f.f_passport_serial,' ','')),'') + isnull(upper(replace(f.f_passport_no,' ','')),'')) = (isnull(upper(replace(@doc_seria,' ','')),'') + isnull(upper(replace(@doc_nr,' ','')),'')) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'partners_fis') if @partner_id is null begin select @doc_type_id = id from t_simples where type_id=1004 and upper(simple_name) = ltrim(rtrim(upper(@doc_type))) if @doc_type_id is null .EXIT_MESSAGE('Не найден тип документа у физического лица') .TYPE_INIT('PARTNERS_FIS',3) .TYPE_ASSIGN(f_doctype_id,@doc_type_id,3) .TYPE_ASSIGN(f_passport_no,@doc_nr,3) .TYPE_ASSIGN(f_passport_serial,@doc_seria,3) .TYPE_ASSIGN(f_first_name,@fst_name,3) .TYPE_ASSIGN(f_middle_name,@sec_name,3) .TYPE_ASSIGN(f_last_name,@fam_name,3) .TYPE_ASSIGN(partner_name,(@fam_name+' '+isnull(@fst_name,'')+' '+isnull(@sec_name,'')),3) .TYPE_ASSIGN(partner_short_name,(@fam_name+' '+isnull(substring(@fst_name,1,1)+'.','')+' '+isnull(substring(@sec_name,1,1)+'.','')),3) select @partner_code='IMPORT'+'(' + @fam_name+' '+isnull(@doc_nr, '')+')' .TYPE_ASSIGN(partner_code,@partner_code,3) .TYPE_ASSIGN(tax_category_id,1,3) .TYPE_INSERT('PARTNERS_FIS','CRT',3) select @partner_id = @id3_1 end end else if charindex('ЮР', upper(@person_typ))>0 begin select @partner_id = t_partners.id from t_partners, t_partners_jur where t_partners.id = t_partners_jur.id and ltrim(upper(t_partners.partner_name)) = ltrim(upper(@fst_name)) and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'partners_jur') if @partner_id is null begin .TYPE_INIT('PARTNERS_JUR',4) .TYPE_ASSIGN(partner_name,@fst_name,4) .TYPE_ASSIGN(partner_short_name,@fst_name,4) select @partner_code='IMPORT'+'(' + @fam_name+' '+isnull(@inn, '')+')' .TYPE_ASSIGN(partner_code,@partner_code,4) .TYPE_ASSIGN(j_inn,@inn,4) .TYPE_ASSIGN(tax_category_id,6,4) .TYPE_INSERT('PARTNERS_JUR','CRT',4) select @partner_id = @id4_1 end end else .EXIT_MESSAGE('Тип лица не содержит слов фи/юр') if @partner_id is null .EXIT_MESSAGE('Пайщик не найден') declare @folder_id numeric select @folder_id = .DEPO_FOLDER_DEFAULT /* Тип заявки */ if charindex('ПРИОБРЕТЕНИ', upper(@req_type))>0 begin /* Заявка на размещение паев */ if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@req_num) and tp_pif_queries.agent_regs_date = @r_date and tp_pif_queries.id = td_depo_docs.id and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_ALLOCATE')) .EXIT_MESSAGE('Заявка с такими реквизитаим уже существует') .TYPE_INIT('PIF_PAY_ALLOCATE',1) .TYPE_ASSIGN(depo_doc_type,1,1) .DEPO_GEN_NO(@in_no1_1,@depo_doc_type1_1) .TYPE_ASSIGN(folder_id,@folder_id,1) .TYPE_ASSIGN(pif_pay_type_id,2,1) .TYPE_ASSIGN(agent_regs_date, @r_date, 1) .TYPE_ASSIGN(agent_regs_no, @req_num, 1) .TYPE_ASSIGN(pay_owner_id, @partner_id, 1) .TYPE_ASSIGN(agent_id, @agent_id, 1) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 1) .TYPE_ASSIGN(bank_account_no, @count_num, 1) .TYPE_ASSIGN(bank_name, @bank_name, 1) .TYPE_ASSIGN(bik, @bik, 1) .TYPE_ASSIGN(corr_account, @kor_count, 1) .TYPE_ASSIGN(bank_city_name, @bank_city, 1) .TYPE_ASSIGN(branch_acc_no, @branch_cnt, 1) .TYPE_ASSIGN(branch_name, @branch_nam, 1) .TYPE_ASSIGN(type_queries, 1, 1) .TYPE_INSERT('PIF_PAY_ALLOCATE','ED',1) end else if charindex('ВЫКУП', upper(@req_type))>0 begin /* Заявка на выкуп паев */ if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@req_num) and tp_pif_queries.agent_regs_date = @r_date and tp_pif_queries.id = td_depo_docs.id and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_BUY')) .EXIT_MESSAGE('Заявка с такими реквизитаим уже существует') .TYPE_INIT('PIF_PAY_BUY',2) .TYPE_ASSIGN(depo_doc_type,1,2) .DEPO_GEN_NO(@in_no2_1,@depo_doc_type2_1) .TYPE_ASSIGN(folder_id,@folder_id,2) .TYPE_ASSIGN(pif_pay_type_id,1,2) declare @stock_qty money /* образец 324,2 руб. */ select charindex(' ', @count_shar) if charindex(' ', @count_shar) > 0 select @count_shar = substring(@count_shar, 1, charindex(' ', @count_shar) - 1) if charindex(',', @count_shar) > 0 select @count_shar = stuff(@count_shar, charindex(',', @count_shar), 1, '.') select @stock_qty = convert(money, @count_shar) /* if @stock_qty is null .EXIT_MESSAGE('В заявке на погашение не указано количество паев') */ .TYPE_ASSIGN(stock_qty, @stock_qty, 2) .TYPE_ASSIGN(agent_regs_date, @r_date, 2) .TYPE_ASSIGN(agent_regs_no, @req_num, 2) .TYPE_ASSIGN(pay_owner_id, @partner_id, 2) .TYPE_ASSIGN(agent_id, @agent_id, 2) .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 2) .TYPE_ASSIGN(bank_account_no, @count_num, 2) .TYPE_ASSIGN(bank_name, @bank_name, 2) .TYPE_ASSIGN(bik, @bik, 2) .TYPE_ASSIGN(corr_account, @kor_count, 2) .TYPE_ASSIGN(bank_city_name, @bank_city, 2) .TYPE_ASSIGN(branch_acc_no, @branch_cnt, 2) .TYPE_ASSIGN(branch_name, @branch_nam, 2) .TYPE_ASSIGN(type_queries, 2, 2) .TYPE_INSERT('PIF_PAY_BUY','ED',2) end else .EXIT_MESSAGE('Тип заявки не содержит слов размещение/выкуп') select sid = convert(varchar, @id) end $ENDTEXT(17224991) $ENDACTION $STATE2ACTION(СОЗДАН.ACTION_INSERT) state=СОЗДАН action=ACTION_INSERT $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.D_PIF_PAY_CHOOSE_LIST) state=СОЗДАН action=D_PIF_PAY_CHOOSE_LIST $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.D_PIF_PAY_LIST) state=СОЗДАН action=D_PIF_PAY_LIST $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.FORM1) state=СОЗДАН action=FORM1 $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.FORM2) state=СОЗДАН action=FORM2 $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.QUERY_IRCOL_IMP) state=СОЗДАН action=QUERY_IRCOL_IMP $ENDSTATE2ACTION $ENDDOC