# Date: 07/07/06 # Version: 105 $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=money field_length=8 field_scale=4 field_prec=19 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(LETTER_RECEIVE_TYPE) name=Способ получения корреспонденции field_name=letter_receive_type 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 $PROP(AGENT_POINT_ID) name=Агентский пункт field_name=agent_point_id table_name=tp_pif_queries field_type=numeric field_length=9 field_scale=0 field_prec=18 page=2 order=41 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(REL8) class=0 type=PIF_FUND form=null prop=OWNER_ID idname=null order=7 $ENDRELATION $RELATION(REL10) class=0 type=FUND_CONTRACTS form=null prop=AGENT_DOG_ID idname=null order=3 $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 $RELATION(REL11) class=0 type=AGENT_POINTS form=null prop=AGENT_POINT_ID idname=null order=8 $ENDRELATION $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=0 isvisible=1 istab=0 posx=0 posy=0 width=2944 height=1012 proc_text=$TEXT(09110174) 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(09110174) $ACTION2RELATION(REL10.IN_NO) relation=REL10 where=null prop=IN_NO order=4 alias=agent_dog_no $ENDACTION2RELATION $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(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(09110174) (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(09110174) label=Счет order=2 $ENDCALC $ENDFORM $FORM(D_PIF_PAY_LIST) name=Список заявок class=4 filter=D_PIF_PAYS_FILTER_FUND target_state= uo=uo_list dw=d_pif_pays_list procedure=ap_10000000000089295 patterns=GENERIC_VIEW_LIST user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=4663 height=1088 proc_text=$TEXT(09110174) 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 --print isnull(@owner_id_s,1001) .VIEW_LIST and td_depo_docs.owner_id = @owner_id -- закомментарено DmChe 25.03.2005 --and td_depo_docs.owner_id = convert(numeric(18,0),@owner_id_s) 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(09110174) $ACTION2RELATION(REL10.IN_NO) relation=REL10 where=null prop=IN_NO order=32 alias=agent_dog_no $ENDACTION2RELATION $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(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(09110174) (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(09110174) label=Счет в реестре order=2 $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=0 isvisible=1 istab=0 posx=0 posy=0 width=2149 height=1152 proc_text=$TEXT(09110174) create procedure dbo.%PROC% as begin declare @date_b datetime, @date_e datetime, @owner_id numeric, @is_reg int select @date_b = .OPERDAY select @date_e = @date_b select @owner_id=.ORGANIZATION select @is_reg=.SR_FOR_PIF(@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 end $ENDTEXT(09110174) $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 $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(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(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(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 $ENDFORM $FORM(D_PIF_PAYS_FILTER_FUND) name=Фильтр фонд class=6 filter=null target_state= uo=uo_filter dw=d_pif_pays_filter_fund procedure=ap_30000000000056160 patterns=GENERIC_VIEW_LIST user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=2190 height=1340 proc_text=$TEXT(09110174) create procedure dbo.%PROC% as begin declare @date_b datetime, @date_e datetime, @owner_id numeric, @is_reg int, @owner_short_name varchar(150) select @date_b = .OPERDAY select @date_e = @date_b select @owner_id=.ORGANIZATION select @is_reg=.SR_FOR_PIF(@owner_id) select @owner_short_name = partner_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), null) as owner_id -- ,convert(varchar(150), null) as owner_short_name ,convert(varchar(30), @owner_id) as owner_id ,@owner_short_name as owner_short_name end $ENDTEXT(09110174) $FILTER(ACC_ID) type=11 label=Счет в реестре prop=null arg_code=acc_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=null $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 $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(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(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(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(OWNER_ID) type=1 label=Фонд prop=OWNER_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $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=0 isvisible=1 istab=0 posx=0 posy=0 width=3666 height=1012 proc_text=$TEXT(09110174) 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(09110174) $ACTION2RELATION(REL10.IN_NO) relation=REL10 where=null prop=IN_NO order=4 alias=agent_dog_no $ENDACTION2RELATION $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(CLAIM_TYPE_ID) class=1 type=integer calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(09110174) (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(09110174) label=null order=1 $ENDCALC $CALC(ACC_NO) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(09110174) (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(09110174) label=Счет order=2 $ENDCALC $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(F_IMP_HOLDDOCS) name=Форма импорта заявок (РБА) class=5 filter=null target_state= uo=uo_rba_holddocs_import dw=d_rba_holddocs 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=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110174) 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(09110174) $ACTION2RELATION(REL11.POINT_NAME) relation=REL11 where=null prop=POINT_NAME order=7 alias=point_name $ENDACTION2RELATION $ACTION2RELATION(REL10.IN_NO) relation=REL10 where=null prop=IN_NO order=4 alias=agent_dog_no $ENDACTION2RELATION $ACTION2RELATION(REL10.IS_MC) relation=REL10 where=null prop=IS_MC order=32 alias=is_mc $ENDACTION2RELATION $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(REL9.DEPO_SUBACC_NO) relation=REL9 where=null prop=DEPO_SUBACC_NO order=1 alias=subacc_no $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(09110174) 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(09110174) $ACTION2RELATION(REL10.IN_NO) relation=REL10 where=null prop=IN_NO order=4 alias=agent_dog_no $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(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(09110174) (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(09110174) label=Счет order=2 $ENDCALC $ENDFORM $ACTION(M_IMP_HOLDDOCS) name=Импорт заявок пайщиков (РБА) class=1 form=F_IMP_HOLDDOCS target_state=СОЗДАН procedure=sp_rba_import_holddocs patterns=GENERIC_IMPORT user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110176) create procedure dbo.%PROC% @sid varchar(30) = null, @id int = null, @rcod varchar(32) = null, @rdate varchar(20) = null, @dtnamerus varchar(100) = null, @fnum int = null, -- фонд, если обмен фонд "откуда" @fnamerus varchar(100) = null, @to_fnum int = null, -- 0, если обмен фонд "куда" @to_fnamerus varchar(100) = null, @branchid int = null, @branchname varchar(20) = null, @hnum int = null, @hfullname varchar(100) = null, @accountregistrar varchar(20) = null, @nmnamerus varchar(100) = null, @rquantity numeric(16,5) = null, @transaction_date varchar(20) = null as declare @action_id numeric, @id1 numeric, @id1_s varchar(30), @user_id numeric, @state_id numeric, @type_id numeric, @item_name varchar(100), @new_state_id numeric, @doc_type int, @mess varchar(120), @rdate1 smalldatetime, @fund_id numeric, @to_fund_id numeric, @agent_id numeric, @agent_dog_id numeric, @to_agent_dog_id numeric, @partner_id numeric, @folder_id numeric, @stock_qty money, @in_no varchar(50), @in_date smalldatetime, @in_date_indoc smalldatetime, @in_time smalldatetime, @fund_from_name varchar(255), @fund_to_name varchar(255), @document_form_id numeric, @corr_name varchar(80), @indoc_type_id numeric, @indoc_id numeric, @indoc_id_s varchar(30), @ap_id numeric, @to_ap_id numeric, @rinum int, -- способ получения корресп. @indoc_comment varchar(80), @transaction_date1 datetime -- дата заявки в УК /* DmChe 24.02.-11.03.2005 Импорт заявок пайщиков из ICDB Алгоритм: из таблицы t_rba_holddocs отбираются все документы за диапазон дат, в кот. поле dtnamerus содержит слово "заявка" 1) проверка заполнения критических полей 2) определяем тип заявки, если обмен будет сформировано две заявки(погашение паев "откуда" и приобретение паев "куда") 3) находим фонд, агента(жестко RZBM) агентский договор (в случае обмена то же самое для второго фонда "куда") 4) находим пайщика 5) заявка на приобретение паев 6) вход.документ к заявке на приобр., привязка вход.док-та к заявке 7) заявка на погашение паев 7) вход.документ к заявке на погаш., привязка вход.док-та к заявке DmChe 04.05.2005 update @in_no, @in_date DmChe 02.08.2005 изменено с этой даты для нормальной работы DmChe 23.08.2005 a) определение агентского пункта б) способ получ.корресп. DmChe 20.10.2005 берем способ получения корресп. не из анкеты пайщика, а прямо из заявки DmChe 27.10.2005 добавлен во вх.документ @indoc_comment DmChe 23.12.2005 добавлено поле @transaction_date - дата заявки в УК DmChe 18.01.2006 вход.дата и исх.дата берется из transaction_date DmChe 27.01.2006 исх.дата берется из rdate */ select @action_id = %ACTION_ID% .CHECK_USER .CHECK_INSERT if @dtnamerus is null .EXIT_MESSAGE('Не указан тип заявки') select @rdate1 = convert(smalldatetime, @rdate, 3), @transaction_date1 = convert(datetime, @transaction_date, 3) if @rdate1 is null .EXIT_MESSAGE('Не указана дата заявки') if @rcod is null .EXIT_MESSAGE('Не указан номер заявки') if @hnum is null .EXIT_MESSAGE('Не указан hnum пайщика в ICDB') begin tran select @folder_id = .DEPO_FOLDER_DEFAULT -- DmChe 18.01.2006 -- select @in_date = @rdate1 select @in_date = @transaction_date1 -- Тип заявки if charindex('приобретение', upper(@dtnamerus)) > 0 select @doc_type = 1 else if charindex('погаш', upper(@dtnamerus)) > 0 select @doc_type = 2 else select @doc_type = 3 -- обмен -- DmChe 20.10.2005 берем спосбо получения корресп. не из анкеты пайщика, а прямо из заявки -- DmChe 23.08.2005 способ получения корресп. /* select @rinum = rinum from t_rba_holders where hnum = @hnum if @rinum is not null if @rinum = 1 -- в месте подачи док-в select @rinum = 7 else if @rinum = 3 -- по почте select @rinum = 1 else if @rinum = 2 -- у лица с реестром select @rinum = 8 */ if left(upper(@nmnamerus),12) = upper('пункт приема') select @rinum = 7 -- в месте подачи док-в else if left(upper(@nmnamerus),7) = upper('У лица,') select @rinum = 8 -- у лица с реестром else select @rinum = 7 -- в месте подачи док-в -- ФОНД select @fund_id = max(i.item_id) from t_item2class i, t_classvalues v, t_classes c where upper(c.code) = 'FUND_IMPORT_CODE' and c.id = v.class_id and upper(v.code) = 'ICDB' and i.value_id = v.id and ltrim(i.code) = convert(varchar(20), @fnum) -- код фонда and .ITEMS_EXISTS_BY_TYPE(i.item_id,'PIF_FUND') if @fund_id is null .EXIT_MESSAGE('Фонд не определен, внесите значение классификатора "Код фонда при импорте"') if @doc_type = 3 -- найдем фонд, в который уходят паи begin select @to_fund_id = max(i.item_id) from t_item2class i, t_classvalues v, t_classes c where upper(c.code) = 'FUND_IMPORT_CODE' and c.id = v.class_id and upper(v.code) = 'ICDB' and i.value_id = v.id and ltrim(i.code) = convert(varchar(20), @to_fnum) -- код фонда назначения and .ITEMS_EXISTS_BY_TYPE(i.item_id,'PIF_FUND') if @to_fund_id is null .EXIT_MESSAGE('Для заявки по обмену не определен Фонд "куда", внесите значение класс."Код фонда при импорте"') select @fund_from_name = partner_name from t_partners where id = @fund_id select @fund_to_name = partner_name from t_partners where id = @to_fund_id end /* -- Поиск договора с УК, в случае если нет агента 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('У фонда не задана управляющая компания') */ -- Поиск договора с фондом, агент RZBM select @agent_id = p.id from t_partners p where p.partner_code = 'rzbm' and .ITEMS_EXISTS_BY_TYPE(p.id,'PARTNERS_JUR') if @agent_id is null .EXIT_MESSAGE('Для фонда не нашли агента RZBM') /* 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 ( (@doc_type = 3 and tb_contracts.comission_period_id != 4) or (@doc_type != 3 and tb_contracts.comission_period_id = 4) -- если не обмен, то тип комиссии пооперационно ) and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') */ 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 ( (@doc_type = 3 and not exists(select 1 from tb_settings_comis -- если обмен, то шкалы комиссий нет where settings_id = tb_contracts.id and .ITEMS_EXISTS_BY_TYPE(tb_settings_comis.id,'REVARD') ) ) or (@doc_type != 3 and exists(select 1 from tb_settings_comis -- если не обмен, то шкала комиссий есть where settings_id = tb_contracts.id and .ITEMS_EXISTS_BY_TYPE(tb_settings_comis.id,'REVARD') ) ) ) and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') if @agent_dog_id is null .EXIT_MESSAGE('Для управляющей компании не задан договор комиссии') -- DmChe 23.08.2005 -- найдем агентский пункт select @ap_id = ap.id from t_agent_points ap where ap.agent_dog_id = @agent_dog_id -- договор фонда and ap.point_code = @branchname -- код филиала из заявки and .ITEMS_EXISTS_BY_TYPE(ap.id,'AGENT_POINTS') if @ap_id is null .EXIT_MESSAGE('Не смогли определить агентский пункт') if @doc_type = 3 -- найдем агента фонд, в который уходят паи begin /* select @to_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 = @to_fund_id and tb_contracts.comission_period_id != 4 and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') */ select @to_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 = @to_fund_id and not exists(select 1 from tb_settings_comis -- шкалы комиссий нет where settings_id = tb_contracts.id and .ITEMS_EXISTS_BY_TYPE(tb_settings_comis.id,'REVARD') ) and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'FUND_CONTRACTS') if @to_agent_dog_id is null .EXIT_MESSAGE('Для управляющей компании фонда "куда" не задан договор комиссии') -- DmChe 23.08.2005 -- найдем агентский пункт select @to_ap_id = ap.id from t_agent_points ap where ap.agent_dog_id = @to_agent_dog_id -- договор фонда and ap.point_code = @branchname -- код филиала из заявки and .ITEMS_EXISTS_BY_TYPE(ap.id,'AGENT_POINTS') if @to_ap_id is null .EXIT_MESSAGE('Не смогли определить агентский пункт("куда" по обмену)') end -- ****** поиск пайщика по hnum'у select @partner_id = p.id from t_partners p, t_item2class ic, t_classes c, t_classvalues cv where cv.code = 'ICDB' and ic.value_id = cv.id and c.id = cv.class_id and c.code = 'HOLDER_CODES' and p.id = ic.item_id and ic.code = convert(varchar(30), @hnum) and exists(select 1 from t_items, t_states, t_types where t_items.id=p.id and t_states.id=t_items.state_id and t_states.class_id=0 and t_types.id=t_items.type_id and upper(t_types.type_code)in (upper('PARTNERS_JUR'),upper('PARTNERS_FIS')) ) if @partner_id is null begin select @mess = 'Пайщик не найден для hnum=' + convert(varchar(20), @hnum) .EXIT_MESSAGE(@mess) end -- ************************************ Тип заявки -- +++++++++++++++++++++++++++++ Заявка на размещение паев if @doc_type = 1 or @doc_type = 3 begin if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@rcod) and tp_pif_queries.agent_regs_date = @rdate1 and tp_pif_queries.id = td_depo_docs.id and ((@doc_type = 1 and td_depo_docs.owner_id = @fund_id) -- фонд or (@doc_type = 3 and td_depo_docs.owner_id = @to_fund_id) ) and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_ALLOCATE') ) .EXIT_MESSAGE('Заявка с такими реквизитаим уже существует') if @doc_type = 1 select @indoc_type_id=(select max(id) from t_sd_indoc_types where indoc_name='заявка на приобретение паев') else select @indoc_type_id=(select max(id) from t_sd_indoc_types where indoc_name='заявка на обмен паев') .TYPE_INIT('PIF_PAY_ALLOCATE', 1) .TYPE_ASSIGN(depo_doc_type, 1, 1) .DEPO_GEN_NO(@in_no, 1, @in_date) -- .DEPO_GEN_NO(@in_no1_1,@depo_doc_type1_1) .TYPE_ASSIGN(in_no, @in_no, 1) .TYPE_ASSIGN(in_date, @in_date, 1) .TYPE_ASSIGN(folder_id, @folder_id, 1) -- тип заявки (одноразовая, многоразовая) if @doc_type = 1 begin .TYPE_ASSIGN(pif_pay_type_id, 2,1) .TYPE_ASSIGN(type_queries, 1, 1) end else begin .TYPE_ASSIGN(pif_pay_type_id, 1,1) .TYPE_ASSIGN(type_queries, 3, 1) end .TYPE_ASSIGN(agent_regs_date, @rdate1, 1) .TYPE_ASSIGN(agent_regs_no, @rcod, 1) .TYPE_ASSIGN(pay_owner_id, @partner_id, 1) .TYPE_ASSIGN(agent_id, @agent_id, 1) if @doc_type = 1 begin .TYPE_ASSIGN(agent_dog_id, @agent_dog_id, 1) .TYPE_ASSIGN(owner_id, @fund_id, 1) .TYPE_ASSIGN(agent_point_id, @ap_id, 1) end else begin .TYPE_ASSIGN(agent_dog_id, @to_agent_dog_id, 1) .TYPE_ASSIGN(owner_id, @to_fund_id, 1) .TYPE_ASSIGN(agent_point_id, @to_ap_id, 1) end -- .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, @branchname, 1) .TYPE_ASSIGN(letter_receive_type, @rinum, 1) if @doc_type = 3 -- проставим фонд для обмена .TYPE_ASSIGN(fund4change, @fund_from_name, 1) .TYPE_INSERT('PIF_PAY_ALLOCATE','ED',1) select @id1 = .TYPE_FIELD(id, 1) select @id1_s = convert(varchar(30), @id1) -- входящий документ к заявке select @folder_id =.DEPO_FOLDER_DEFAULT, @document_form_id = 1, @indoc_comment = @hfullname select @corr_name = partner_name -- 'RBA', from t_partners where partner_code = 'rzbm' and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR') if @indoc_type_id is not null if not exists(select 1 from t_sd_indocs where indoc_type_id = @indoc_type_id and out_no = @rcod -- нет вход.док-та такого типа с таким номером заявки and ((@doc_type = 1 and t_sd_indocs.owner_id = @fund_id) -- фонд or (@doc_type = 3 and t_sd_indocs.owner_id = @to_fund_id) ) and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id, 'SD_INDOC') ) begin select @in_no = null -- DmChe 02.08.2005 изменено с этой даты для нормальной работы select @in_date_indoc = convert(smalldatetime,convert(varchar(20),getdate(),112)) .DEPO_GEN_NO(@in_no, 1, @in_date_indoc) select @in_time = convert(smalldatetime, '19000101 ' + convert(varchar(30),getdate(),108)) .TYPE_INIT('SD_INDOC', 4) -- DmChe 18.01.2006 -- .TYPE_ASSIGN(out_date, @rdate1, 4) -- DmChe 27.01.2006 -- .TYPE_ASSIGN(out_date, @transaction_date1, 4) .TYPE_ASSIGN(out_date, @rdate1, 4) .TYPE_ASSIGN(out_no, @rcod,4) .TYPE_ASSIGN(in_date, @in_date_indoc, 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, @folder_id, 4) if @doc_type = 1 .TYPE_ASSIGN(owner_id, @fund_id, 4) else .TYPE_ASSIGN(owner_id, @to_fund_id, 4) .TYPE_ASSIGN(comment, @indoc_comment, 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, @indoc_id, 5) .TYPE_ASSIGN(doc_id, @id1, 5) .TYPE_INSERT('SD_INDOC2DOC', 'CRT', 5) end else -- есть вход.док-т такого типа с таким номером заявки begin -- привязка к заявке уже существующего вход.документа (удаляли заявку, а вход.док-т остался) select @indoc_id = id from t_sd_indocs where indoc_type_id = @indoc_type_id and out_no = @rcod and ((@doc_type = 1 and t_sd_indocs.owner_id = @fund_id) -- фонд or (@doc_type = 3 and t_sd_indocs.owner_id = @to_fund_id) ) and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id, 'SD_INDOC') .TYPE_INIT('SD_INDOC2DOC', 51) .TYPE_ASSIGN(indoc_id, @indoc_id, 51) .TYPE_ASSIGN(doc_id, @id1, 51) .TYPE_INSERT('SD_INDOC2DOC', 'CRT', 51) end -- конец вход.документа end -- ++++++++++++++++++++++++++++ Заявка на выкуп паев if @doc_type = 2 or @doc_type = 3 begin if exists(select 1 from tp_pif_queries, td_depo_docs where upper(tp_pif_queries.agent_regs_no) = upper(@rcod) and tp_pif_queries.agent_regs_date = @rdate1 and tp_pif_queries.id = td_depo_docs.id and td_depo_docs.owner_id = @fund_id -- фонд and .ITEMS_EXISTS_BY_TYPE(tp_pif_queries.id, 'PIF_PAY_BUY') ) .EXIT_MESSAGE('Заявка на погашение паев с такими реквизитаим уже существует') if @doc_type = 2 select @indoc_type_id=(select max(id) from t_sd_indoc_types where indoc_name='заявка на погашение паев') else select @indoc_type_id=(select max(id) from t_sd_indoc_types where indoc_name='заявка на обмен паев') .TYPE_INIT('PIF_PAY_BUY', 2) .TYPE_ASSIGN(depo_doc_type, 1, 2) select @in_no = null .DEPO_GEN_NO(@in_no, 1, @in_date) -- .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) .TYPE_ASSIGN(in_no, @in_no, 2) .TYPE_ASSIGN(in_date, @in_date, 2) if @rquantity is null .EXIT_MESSAGE('В заявке на погашение или обмен не указано количество паев') .TYPE_ASSIGN(stock_qty, @rquantity, 2) .TYPE_ASSIGN(agent_regs_date, @rdate1, 2) .TYPE_ASSIGN(agent_regs_no, @rcod, 2) .TYPE_ASSIGN(pay_owner_id, @partner_id, 2) .TYPE_ASSIGN(owner_id, @fund_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, @branchname, 2) .TYPE_ASSIGN(letter_receive_type, @rinum, 2) if @doc_type = 2 begin .TYPE_ASSIGN(type_queries, 2, 2) .TYPE_ASSIGN(agent_point_id, @ap_id, 2) end else begin .TYPE_ASSIGN(type_queries, 4, 2) .TYPE_ASSIGN(agent_point_id, @to_ap_id, 2) end if @doc_type = 3 -- проставим фонд для обмена .TYPE_ASSIGN(fund4change, @fund_to_name, 2) .TYPE_INSERT('PIF_PAY_BUY','ED',2) select @id1 = .TYPE_FIELD(id, 2) select @id1_s = convert(varchar(30), @id1) -- входящий документ к заявке select @folder_id = .DEPO_FOLDER_DEFAULT, @document_form_id = 1, @indoc_comment = @hfullname select @corr_name = partner_name -- 'RBA', from t_partners where partner_code = 'rzbm' and .ITEMS_EXISTS_BY_TYPE(t_partners.id, 'PARTNERS_JUR') if @indoc_type_id is not null if not exists(select 1 from t_sd_indocs where indoc_type_id = @indoc_type_id and out_no = @rcod -- нет вход.док-та такого типа с таким номером заявки and t_sd_indocs.owner_id = @fund_id -- фонд and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id, 'SD_INDOC') ) begin select @in_no = null -- DmChe 02.08.2005 изменено с этой даты для нормальной работы select @in_date_indoc = convert(smalldatetime,convert(varchar(20),getdate(),112)) .DEPO_GEN_NO(@in_no, 1, @in_date_indoc) select @in_time = convert(smalldatetime, '19000101 ' + convert(varchar(30),getdate(),108)) .TYPE_INIT('SD_INDOC', 6) -- DmChe 18.01.2006 -- .TYPE_ASSIGN(out_date, @rdate1, 6) -- DmChe 27.01.2006 -- .TYPE_ASSIGN(out_date, @transaction_date1, 6) .TYPE_ASSIGN(out_date, @rdate1, 6) .TYPE_ASSIGN(out_no, @rcod, 6) .TYPE_ASSIGN(in_date, @in_date_indoc, 6) .TYPE_ASSIGN(in_time, @in_time, 6) .TYPE_ASSIGN(indoc_type_id, @indoc_type_id, 6) .TYPE_ASSIGN(corr_name, @corr_name, 6) .TYPE_ASSIGN(document_form_id, @document_form_id, 6) .TYPE_ASSIGN(folder_id, @folder_id, 6) .TYPE_ASSIGN(owner_id, @fund_id, 6) .TYPE_ASSIGN(comment, @indoc_comment, 6) .TYPE_INSERT('SD_INDOC', 'ED', 6) select @indoc_id=.TYPE_FIELD(id, 6) 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', 7) .TYPE_ASSIGN(indoc_id, @indoc_id, 7) .TYPE_ASSIGN(doc_id, @id1, 7) .TYPE_INSERT('SD_INDOC2DOC', 'CRT', 7) end else -- есть вход.док-т такого типа с таким номером заявки begin -- привязка к заявке уже существующего вход.документа (удаляли заявку, а вход.док-т остался) select @indoc_id = id from t_sd_indocs where indoc_type_id = @indoc_type_id and out_no = @rcod and t_sd_indocs.owner_id = @fund_id -- фонд and .ITEMS_EXISTS_BY_TYPE(t_sd_indocs.id, 'SD_INDOC') .TYPE_INIT('SD_INDOC2DOC', 71) .TYPE_ASSIGN(indoc_id, @indoc_id, 71) .TYPE_ASSIGN(doc_id, @id1, 71) .TYPE_INSERT('SD_INDOC2DOC', 'CRT', 71) end -- конец вход.документа end --.ACTION_HISTORY -- Начало записи протокола declare @action_history_id numeric if @action_history_id is null begin exec ap_getid @action_history_id out if @@error!=0 begin rollback tran raiserror 40000 'Ошибка генерации уникального номера протокола метода' return end insert t_actions_history(id,action_date,doc_id,action_id,src_id,dst_id,user_id,mes_id) values(@action_history_id,getdate(),@id1,@action_id,@state_id,@new_state_id,@user_id,null) end else begin update t_actions_history set action_date=getdate(), doc_id=@id1, action_id=@action_id, src_id=@state_id, dst_id=@new_state_id, user_id=@user_id, mes_id=null where id=@action_history_id end if @@error!=0 begin rollback tran raiserror 40000 'Запись протокола не прошла' return end -- Конец записи протокола commit tran -- select sid = convert(varchar, @id1) .END $ENDTEXT(09110176) $ENDACTION $ACTION(PIFPAY_IN) name=Расчет паев (выдача) class=9 form=null target_state=null procedure=ap_pifpay_in patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110176) create proc dbo.%PROC% @payment_sum money, @agent_dog_id numeric, @owner_id numeric, @operday datetime, @paydate datetime, @stock_price money out, @stock_qty .PIF_PAY_TYPE out, @total_sum money out, @agent_comis_sum money out, @agent_proc money out as begin begin tran declare @agent_comis money, @stock_price_allocate money, @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('Для данного фонда не задана цена пая') -- Процент комиссии if @agent_dog_id is not null begin .CALC_AGENT_COMIS(@payment_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(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 + round((@stock_price * @agent_proc / 100.0),2) .PIF_ROUND_MONEY(@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(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(09110176) $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(09110176) 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(09110176) $ENDACTION $ACTION(PIFPAY_OUT_NEW) name=Расчет паев (погашение) - новый class=9 form=null target_state=null procedure=ap_pifpay_out_new patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110176) 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 money 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 money, @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 select @agent_proc_calc=.SD_AGENT_PROC4PARTION(@agent_dog_id,@operday,@partdate) 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_MONEY(@owner_id,@stock_price_calc,@stock_price_allocate) -- Сумма к выплате select @payment_sum = round((@stock_qty * @stock_price_allocate),2) -- Сумма комиссии select @agent_comis_sum = round(@stock_qty * @stock_price,2) - @payment_sum commit tran end $ENDTEXT(09110176) $ENDACTION $ACTION(PIFPAY_OUT_PARTS) name=Обработка партий при погашении class=9 form=null target_state=null procedure=ap_pifpay_out_parts patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110176) 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 -- количество as declare @calc_type int -- 1 - по изменению %, иначе - по каждой партии select @calc_type=1 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 money, @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 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 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) -- расчет select @comis_proc=.SD_AGENT_PROC4PARTION(@agent_dog_id,@operday,@partdate) 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 fetch parts into @part_id, @partdate, @part_sum, @part_qty_bal end close parts .DEALLOCATE parts 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) 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(09110176) $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=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(09110176) 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(09110176) $ENDACTION $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(СОЗДАН.M_IMP_HOLDDOCS) state=СОЗДАН action=M_IMP_HOLDDOCS $ENDSTATE2ACTION $STATE2ACTION(СОЗДАН.QUERY_IRCOL_IMP) state=СОЗДАН action=QUERY_IRCOL_IMP $ENDSTATE2ACTION $ENDDOC