# Date: 03/05/06 # Version: 105 $DOC(OFFICIAL_PROPS) name=Реквизиты лица class=2 product=ALD_SECUR name_formula=.ITEM_NAME(@investor_id)+','+.ITEM_NAME(@rate_place_id)+','+.ITEM_NAME(@depo_account_id)+','+.ITEM_NAME(@bank_account_id) remark_formula=null $STATE(ACT) name=Создан class=0 $ENDSTATE $STATE(BLK) name=Блокирован class=0 $ENDSTATE $STATE(DEL) name=Удален class=2 $ENDSTATE $STATE(NEW) name=Начальный class=1 $ENDSTATE $PROP(INVESTOR_ID) name=Инвестор field_name=investor_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=1 init_class=1 init_formula=convert(numeric,@investor_id_s) 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(RATE_PLACE_ID) name=Площадка field_name=rate_place_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=2 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(DEPO_ACCOUNT_ID) name=Раздел счёта ДЕПО field_name=depo_account_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=3 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(BANK_ACCOUNT_ID) name=Счет ДС field_name=bank_account_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=4 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(COMM_OFFICIAL) name=Комментарий field_name=comm_official table_name=tb_official_props field_type=varchar field_length=255 field_scale=null field_prec=255 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(ASSET_PLACE_ID) name=Место нахождения ЦБ field_name=asset_place_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=6 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(PORTFOLIO_SUB_ID) name=Раздел учета field_name=portfolio_sub_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=7 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(PORTFOLIO_MENEGER_ID) name=Портфель менеджера field_name=portfolio_meneger_id table_name=tb_official_props field_type=numeric field_length=9 field_scale=0 field_prec=18 page=1 order=8 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(REL2) class=0 type=ITEMS form=null prop=INVESTOR_ID idname=null order=null $ENDRELATION $RELATION(REL5) class=0 type=ITEMS form=null prop=ASSET_PLACE_ID idname=null order=100 $ENDRELATION $RELATION(REL8) class=1 type=ITEM2CLASSVALUES form=D_ITEM2VALUE_LIST prop=null idname=item_id_s order=10010 $ENDRELATION $RELATION(REL4) class=0 type=BANK_ACCOUNTS form=null prop=BANK_ACCOUNT_ID idname=null order=null $ENDRELATION $RELATION(REL1) class=0 type=RATE_PLACES form=null prop=RATE_PLACE_ID idname=null order=null $ENDRELATION $RELATION(REL6) class=0 type=PORTFOLIO_SECTION form=null prop=PORTFOLIO_SUB_ID idname=null order=10000 $ENDRELATION $RELATION(REL7) class=0 type=PORTFOLIO form=null prop=PORTFOLIO_MENEGER_ID idname=null order=null $ENDRELATION $RELATION(REL3) class=0 type=DEPO_SUBACC_GROUP_EXT form=null prop=DEPO_ACCOUNT_ID idname=null order=null $ENDRELATION $FORM(D_OFFICIAL_PROPS_LIST) name=Список реквизитов class=4 filter=null target_state= uo=uo_list dw=d_official_props_list procedure=ap_10000000000031009 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095198) 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% ---.EXIT_MESSAGE_PARM('@investor_id_s',@investor_id_s) .CHECK_USER .CHECK_VIEW .ARGCONVERT_FILTER .VIEW_LIST and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.asset_place_id)) or (tb_official_props.asset_place_id is null)) end $ENDTEXT(11095198) $ACTION2RELATION(REL2.ITEM_NAME) relation=REL2 where=null prop=ITEM_NAME order=4 alias=investor_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=14 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=13 alias=depo_subacc_number $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=2 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=3 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL1.PLACE_NAME) relation=REL1 where=null prop=PLACE_NAME order=1 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=3 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL6.SECTION_NAME) relation=REL6 where=null prop=SECTION_NAME order=7 alias=section_name $ENDACTION2RELATION $ACTION2RELATION(REL7.PORTFOLIO_CODE) relation=REL7 where=null prop=PORTFOLIO_CODE order=6 alias=portfolio_code $ENDACTION2RELATION $FILTER(INVESTOR_ID) type=11 label=investor_id prop=INVESTOR_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $CALC(OBJECT_CLASS_CODE) class=1 type=varchar calc_lenght=30 calc_prec=null calc_scale=null formula=$TEXT(11095198) select t_move_directions.code from tb_broker2exchange,t_move_directions where tb_broker2exchange.id=tb_official_props.asset_place_id and tb_broker2exchange.object_class_id=t_move_directions.id $ENDTEXT(11095198) label=Код класса объекта order=1 $ENDCALC $ENDFORM $FORM(D_OFFICIAL_PROPS_ORG_LIST) name=Реквизиты организаций class=4 filter=null target_state= uo=uo_list dw=d_official_props_org_list procedure=ap_10000000000307874 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(11095198) create procedure dbo.%PROC% @sid varchar(30) = null .ARGLIST_FILTER as .BEGIN('N') .CHECK_USER .CHECK_VIEW_LIST .ARGCONVERT_FILTER .VIEW_LIST and tb_official_props.investor_id in .ORG_FROM_PARTNER(@organisation_id) and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.asset_place_id)) or (tb_official_props.asset_place_id is null)) .END $ENDTEXT(11095198) $ACTION2RELATION(REL1.PLACE_NAME) relation=REL1 where=null prop=PLACE_NAME order=6 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=16 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=17 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=13 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=14 alias=depo_subacc_no $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=4 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL2.ITEM_NAME) relation=REL2 where=null prop=ITEM_NAME order=4 alias=investor_name $ENDACTION2RELATION $ACTION2RELATION(REL6.SECTION_NAME) relation=REL6 where=null prop=SECTION_NAME order=7 alias=section_name $ENDACTION2RELATION $FILTER(ORGANISATION_ID) type=11 label=organisation_id prop=null arg_code=organisation_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $ENDFORM $FORM(D_SUM_SALDO_FILTER) name=Фильтр списка ... class=6 filter=null target_state= uo=uo_filter dw=d_sum_saldo_filter procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=2953 height=1016 proc_text=null $FILTER(INV_ID) type=1 label=Инвестор prop=null arg_code=inv_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(MANAGER_PORFOLIO_ID) type=1 label=Портфель менеджера prop=null arg_code=manager_porfolio_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(BAL_PORTFOLIO_ID) type=1 label=Балансовый портфель prop=null arg_code=bal_portfolio_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(BANC_ACC_ID) type=1 label=Расчетный счет prop=null arg_code=banc_acc_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(ASSET_PLACE_ID) type=1 label=Место нахождение активов prop=null arg_code=asset_place_id arg_type=numeric arg_length=null arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(CLIENT_SHORT_NAME) type=1 label=Имя инвестора prop=null arg_code=client_short_name arg_type=varchar arg_length=255 arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(OFFICIAL_PROPS) name=Реквизиты class=4 filter=null target_state= uo=null dw=dd_official_props procedure=ap_official_props patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095200) 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 if (@investor_id_s is NULL) or (@investor_id_s='') begin select @investor_id_bb = -999999999999999998 select @investor_id_ee = 999999999999999999 end else begin select @investor_id_bb =(select tb_broker_change.id from tb_broker_change where convert(numeric,@investor_id_s)=tb_broker_change.partner_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info')) select @investor_id_ee =(select tb_broker_change.id from tb_broker_change where convert(numeric,@investor_id_s)=tb_broker_change.partner_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info')) end select @investor_id_nn = (select tb_broker_change.id from tb_broker_change where convert(numeric,@investor_id_s)=tb_broker_change.partner_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info')) .VIEW_LIST and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.asset_place_id)) or (tb_official_props.asset_place_id is null)) end $ENDTEXT(11095200) $FILTER(INVESTOR_ID) type=1 label=INVESTOR_ID prop=INVESTOR_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(OFFICIAL_PROPS_EDIT) name=Просмотр class=5 filter=null target_state= uo=uo_edit dw=d_official_props_edit procedure=ap_10000000000031017 patterns=GENERIC_VIEW_ITEM user_define=1 isvisible=1 istab=1 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095200) create procedure dbo.%PROC% @sid varchar(30) = null, @investor_id_s 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 if @id = -1 begin begin tran .GET_PROPS select @investor_id = convert(numeric,@investor_id_s) .CHECK_INSERT .INIT_VARS(B) .INSERT_ITEMS .INSERT commit tran end .VIEW_ITEM end $ENDTEXT(11095200) $ACTION2RELATION(REL2.ITEM_NAME) relation=REL2 where=null prop=ITEM_NAME order=2 alias=investor_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=2 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=3 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=7 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=1 alias=depo_subacc_no $ENDACTION2RELATION $ACTION2RELATION(REL1.PLACE_NAME) relation=REL1 where=null prop=PLACE_NAME order=1 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=4 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL6.SECTION_NAME) relation=REL6 where=null prop=SECTION_NAME order=7 alias=section_name $ENDACTION2RELATION $ACTION2RELATION(REL7.PORTFOLIO_CODE) relation=REL7 where=null prop=PORTFOLIO_CODE order=6 alias=portfolio_code $ENDACTION2RELATION $ACTION2RELATION(REL7.PORTFOLIO_NAME) relation=REL7 where=null prop=PORTFOLIO_NAME order=3 alias=portfolio_name $ENDACTION2RELATION $ENDFORM $FORM(OFFICIAL_PROPS_FILTER) name=Фильтр class=6 filter=null target_state= uo=uo_filter dw=d_official_props_filter procedure=null patterns=null user_define=0 isvisible=1 istab=0 posx=0 posy=0 width=2587 height=880 proc_text=null $FILTER(INVESTOR_ID) type=1 label=Инвестор prop=INVESTOR_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(BANK_ACCOUNT_ID) type=1 label=Счет ДС prop=BANK_ACCOUNT_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(RATE_PLACE_ID) type=1 label=Площадка prop=RATE_PLACE_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(DEPO_ACCOUNT_ID) type=1 label=Раздел счёта ДЕПО prop=DEPO_ACCOUNT_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(OFFICIAL_PROPS_LIST) name=Реквизиты для инвестора class=4 filter=null target_state= uo=uo_list_disable dw=d_official2props_list procedure=ap_off_props_list patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=5339 height=1872 proc_text=$TEXT(11095200) 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 declare @character_id numeric declare @organisation_id numeric declare @client_contract_id numeric declare @investor_id numeric select @character_id=convert(numeric,@character_id_s) select @organisation_id=convert(numeric,@organisation_id_s) select @client_contract_id=convert(numeric,@client_contract_id_s) if @organisation_id is null begin select @organisation_id=.ORGANIZATION end if @character_id=1 select @investor_id=(select tb_broker_change.id from tb_broker_change where @organisation_id=tb_broker_change.partner_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info')) if @character_id=2 select @investor_id=@client_contract_id .VIEW_LIST and investor_id=@investor_id and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.asset_place_id)) or (tb_official_props.asset_place_id is null)) end $ENDTEXT(11095200) $ACTION2RELATION(REL2.ITEM_NAME) relation=REL2 where=null prop=ITEM_NAME order=2 alias=investor_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=2 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=3 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=7 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=1 alias=depo_subacc_no $ENDACTION2RELATION $ACTION2RELATION(REL1.PLACE_NAME) relation=REL1 where=null prop=PLACE_NAME order=1 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=3 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL6.SECTION_NAME) relation=REL6 where=null prop=SECTION_NAME order=7 alias=section_name $ENDACTION2RELATION $ACTION2RELATION(REL7.PORTFOLIO_CODE) relation=REL7 where=null prop=PORTFOLIO_CODE order=6 alias=portfolio_code $ENDACTION2RELATION $FILTER(RATE_PLACE_ID) type=11 label=RATE_PLACE_ID prop=RATE_PLACE_ID arg_code=null arg_type=null arg_length=null arg_prec=null arg_scale=null $ENDFILTER $FILTER(CHARACTER_ID_S) type=11 label=null prop=null arg_code=character_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $FILTER(CLIENT_CONTRACT_ID_S) type=11 label=null prop=null arg_code=client_contract_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $FILTER(ORGANISATION_ID_S) type=11 label=null prop=null arg_code=organisation_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(OFFICIAL_PROPS_LIST_WDEALTYPE) name=Реквизиты для инвестора class=4 filter=null target_state= uo=uo_list dw=d_official2props_list procedure=ap_off_props_list_wdealtype patterns=null user_define=1 isvisible=1 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(11095200) 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 declare @character_id numeric declare @organisation_id numeric declare @client_contract_id numeric declare @investor_id numeric declare @rate_place_type_id numeric declare @deal_type_id numeric declare @deal_type_code varchar(255) select @character_id=convert(numeric,@character_id_s) select @organisation_id=convert(numeric,@organisation_id_s) select @client_contract_id=convert(numeric,@client_contract_id_s) if @organisation_id is null begin select @organisation_id=.ORGANIZATION end if @character_id=1 select @investor_id=(select tb_broker_change.id from tb_broker_change where @organisation_id=tb_broker_change.partner_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'broker_info')) if @character_id=2 select @investor_id=@client_contract_id .ASSIGN(@deal_type_id,(CONVERT(NUMERIC,ISNULL(@deal_type_sid,'null')))) .ASSIGN(@deal_type_code,(.TYPE_CODE(@deal_type_id))) IF((CHARINDEX('VBR',UPPER(@deal_type_code))) > 0) -- внебиржевая площадка BEGIN .ASSIGN(@rate_place_type_id,1) END ELSE BEGIN .ASSIGN(@rate_place_type_id,2) END ---.VIEW_LIST -- and investor_id=@investor_id -- and place_type_id = @rate_place_type_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 , 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), convert(varchar,tb_official_props.investor_id) as investor_id_s, convert(varchar,tb_official_props.rate_place_id) as rate_place_id_s, convert(varchar,tb_official_props.depo_account_id) as depo_account_id_s, convert(varchar,tb_official_props.bank_account_id) as bank_account_id_s, tb_official_props.comm_official, convert(varchar,tb_official_props.asset_place_id) as asset_place_id_s, convert(varchar,tb_official_props.portfolio_sub_id) as portfolio_sub_id_s, convert(varchar,tb_official_props.portfolio_meneger_id) as portfolio_meneger_id_s, r1813581499_10000000000031000.item_name as investor_name, r1808725496_10000000000031001.place_name as place_name, r1666104976_10000000000031002.depo_subacc_no as depo_subacc_no, r1666104976_10000000000031002.depo_subacc_name as depo_subacc_name, r1794105432_10000000000031003.bank_account_name as bank_account_name, r1794105432_10000000000031003.bank_account_no as bank_account_no, r1606557057_21535000001198472.portfolio_code as portfolio_code, r1813581499_10000000000066757.item_name as asset_place_name, r1590557000_21535000000510687.section_name as section_name from #state_list, t_items , tb_official_props , t_items r1813581499_10000000000031000 , td_rate_places r1808725496_10000000000031001 , td_depo_subacc r1666104976_10000000000031002 , t_bank_accounts r1794105432_10000000000031003 , tb_portfolio r1606557057_21535000001198472 , t_items r1813581499_10000000000066757, tb_portfolio_section r1590557000_21535000000510687 where #state_list.state_id = t_items.state_id and t_items.id = tb_official_props.id and tb_official_props.investor_id=@investor_id and tb_official_props.investor_id = r1813581499_10000000000031000.id and tb_official_props.portfolio_sub_id *= r1590557000_21535000000510687.id and tb_official_props.portfolio_meneger_id *= r1606557057_21535000001198472.id and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and tb_official_props.rate_place_id *= r1808725496_10000000000031001.id and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and tb_official_props.depo_account_id *= r1666104976_10000000000031002.id and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and tb_official_props.bank_account_id *= r1794105432_10000000000031003.id and ((.ITEMS_EXISTS(tb_official_props.asset_place_id)) or (tb_official_props.asset_place_id is null)) and tb_official_props.asset_place_id *= r1813581499_10000000000066757.id and r1808725496_10000000000031001.place_type_id = @rate_place_type_id end $ENDTEXT(11095200) $ACTION2RELATION(REL2.ITEM_NAME) relation=REL2 where=null prop=ITEM_NAME order=3 alias=investor_name $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=3 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=15 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=14 alias=depo_subacc_no $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=17 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=18 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL1.PLACE_NAME) relation=REL1 where=null prop=PLACE_NAME order=3 alias=place_name $ENDACTION2RELATION $ACTION2RELATION(REL6.SECTION_NAME) relation=REL6 where=null prop=SECTION_NAME order=7 alias=section_name $ENDACTION2RELATION $ACTION2RELATION(REL7.PORTFOLIO_CODE) relation=REL7 where=null prop=PORTFOLIO_CODE order=6 alias=portfolio_code $ENDACTION2RELATION $FILTER(DEAL_TYPE_SID) type=11 label=Тип сделки prop=null arg_code=deal_type_sid arg_type=varchar arg_length=30 arg_prec=18 arg_scale=0 $ENDFILTER $FILTER(CHARACTER_ID_S) type=11 label=Характер сделки prop=null arg_code=character_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $FILTER(CLIENT_CONTRACT_ID_S) type=11 label=Клиентский договор prop=null arg_code=client_contract_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $FILTER(ORGANISATION_ID_S) type=11 label=Организация prop=null arg_code=organisation_id_s arg_type=varchar arg_length=30 arg_prec=null arg_scale=null $ENDFILTER $ENDFORM $FORM(UO_BANKACC2ASSET_LIST) name=Список расчетных счетов class=4 filter=null target_state= uo=uo_list dw=d_bankacc2asset_list procedure=ap_10000000000071445 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, @investor_id_s varchar(30) = null, @org_id_s varchar(30) = null, @inv_type int /*1-лицо,2-инвестор*/ as .BEGIN('N') .CHECK_USER .CHECK_VIEW_LIST .ARGCONVERT_FILTER declare @part_id numeric, @investor_id numeric, @org_id numeric if @investor_id_s='' select @investor_id_s=null if @org_id_s='' select @org_id_s=null select @investor_id=convert(numeric,@investor_id_s) select @org_id=convert(numeric,@org_id_s) create table #inv(id numeric) if @inv_type=1 begin if @org_id=@investor_id begin insert #inv select id from tb_broker_change where partner_id=@investor_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO') end else begin insert #inv select tb_contracts.id from tb_contracts,td_depo_docs where client_id=@investor_id and td_depo_docs.id=tb_contracts.id and td_depo_docs.owner_id=@org_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'CLIENT_CONTRACTS') end end else begin if exists(select 1 from tb_contracts,td_depo_docs where tb_contracts.id=@investor_id and td_depo_docs.id=tb_contracts.id and td_depo_docs.owner_id=@org_id) insert #inv select @investor_id else begin insert #inv select id from tb_broker_change where partner_id=@org_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO') end end .VIEW_LIST and investor_id in (select id from #inv) and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.depo_account_id)) or (tb_official_props.depo_account_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and .ITEMS_EXISTS(tb_official_props.asset_place_id) .END $ENDTEXT(11095201) $ACTION2RELATION(REL4.BANK_ACCOUNT_NAME) relation=REL4 where=null prop=BANK_ACCOUNT_NAME order=10 alias=bank_account_name $ENDACTION2RELATION $ACTION2RELATION(REL4.BANK_ACCOUNT_NO) relation=REL4 where=null prop=BANK_ACCOUNT_NO order=12 alias=bank_account_no $ENDACTION2RELATION $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=3 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL1.PLACE_CODE) relation=REL1 where=null prop=PLACE_CODE order=6 alias=place_code $ENDACTION2RELATION $CALC(CLASS_NAME) class=1 type=varchar calc_lenght=255 calc_prec=null calc_scale=null formula=$TEXT(11095201) (select md.code from t_move_directions md,tb_broker2exchange be where be.id =tb_official_props.asset_place_id and be.object_class_id = md.id) $ENDTEXT(11095201) label=Наименование класса order=1 $ENDCALC $ENDFORM $FORM(UO_DEPOACC2ASSET_LIST) name=Список счетов ЦБ class=4 filter=null target_state= uo=uo_list dw=d_depoacc2asset_list procedure=ap_10000000000071448 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=1 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, @investor_id_s varchar(30) = null, @org_id_s varchar(30) = null, @inv_type int /*1-лицо,2-инвестор*/, @depo_account_id_s varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_VIEW_LIST .ARGCONVERT_FILTER declare @part_id numeric, @investor_id numeric, @org_id numeric, @depo_account_id numeric --.EXIT_MESSAGE_PARM('@investor_id_s',@investor_id_s) if @investor_id_s='' select @investor_id_s=null if @depo_account_id_s='' select @depo_account_id_s=null if @org_id_s='' select @org_id_s=null select @investor_id=convert(numeric,@investor_id_s) select @depo_account_id=convert(numeric,@depo_account_id_s) select @org_id=convert(numeric,@org_id_s) create table #inv(id numeric) if @inv_type=1 begin if @org_id=@investor_id begin insert #inv select id from tb_broker_change where partner_id=@investor_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO') end else begin insert #inv select tb_contracts.id from tb_contracts,td_depo_docs where client_id=@investor_id and td_depo_docs.id=tb_contracts.id and td_depo_docs.owner_id=@org_id and .ITEMS_EXISTS_BY_TYPE(tb_contracts.id,'CLIENT_CONTRACTS') end end else begin if exists(select 1 from tb_contracts,td_depo_docs where td_depo_docs.id=@investor_id and td_depo_docs.id=tb_contracts.id and td_depo_docs.owner_id=@org_id) insert #inv select @investor_id else begin insert #inv select id from tb_broker_change where partner_id=@org_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO') end end .VIEW_LIST and investor_id in (select id from #inv) and depo_account_id in (select id from td_depo_subacc where depo_acc_id=@depo_account_id) and ((.ITEMS_EXISTS(tb_official_props.rate_place_id)) or (tb_official_props.rate_place_id is null)) and ((.ITEMS_EXISTS(tb_official_props.bank_account_id)) or (tb_official_props.bank_account_id is null)) and .ITEMS_EXISTS(tb_official_props.asset_place_id) .END $ENDTEXT(11095201) $ACTION2RELATION(REL5.ITEM_NAME) relation=REL5 where=null prop=ITEM_NAME order=3 alias=asset_place_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NAME) relation=REL3 where=null prop=DEPO_SUBACC_NAME order=8 alias=depo_subacc_name $ENDACTION2RELATION $ACTION2RELATION(REL3.DEPO_SUBACC_NO) relation=REL3 where=null prop=DEPO_SUBACC_NO order=1 alias=depo_subacc_no $ENDACTION2RELATION $ENDFORM $FORM(UO_PROPS_MONEY_LIST) name=Список реквизитов с остатками ДС на NOW class=4 filter=D_SUM_SALDO_FILTER target_state= uo=uo_list_disable dw=d_account_sum_saldo procedure=ap_21535000001207784 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, @purpose_id_s varchar(30)=null, @organisation_id_s varchar(30)=null, @character_id_s varchar(30)=null, @inv_id_s varchar(30) = null, @manager_porfolio_id_s varchar(30) = null, @bal_portfolio_id_s varchar(30) = null, @banc_acc_id_s varchar(30) = null, @asset_place_id_s varchar(30) = null as begin declare @purpose_id numeric declare @inv_id numeric declare @manager_porfolio_id numeric declare @bal_portfolio_id numeric declare @banc_acc_id numeric declare @asset_place_id numeric declare @purpose_class_code varchar(30) declare @organisation_id numeric declare @character_id numeric if isnull(@organisation_id_s,'')<>'' select @organisation_id = convert(numeric,@organisation_id_s) else select @organisation_id =.ORGANIZATION if isnull(@character_id_s,'')<>'' select @character_id = convert(numeric,@character_id_s) else select @character_id =null if isnull(@purpose_id_s,'')<>'' select @purpose_id = convert(numeric,@purpose_id_s) else select @purpose_id=null if isnull(@inv_id_s,'')<>'' select @inv_id = convert(numeric,@inv_id_s) else select @inv_id=null if isnull(@manager_porfolio_id_s,'')<>'' select @manager_porfolio_id = convert(numeric,@manager_porfolio_id_s) else select @manager_porfolio_id=null if isnull(@bal_portfolio_id_s,'')<>'' select @bal_portfolio_id = convert(numeric,@bal_portfolio_id_s) else select @bal_portfolio_id=null if isnull(@banc_acc_id_s,'')<>'' select @banc_acc_id = convert(numeric,@banc_acc_id_s) else select @banc_acc_id=null if isnull(@asset_place_id_s,'')<>'' select @asset_place_id = convert(numeric,@asset_place_id_s) else select @asset_place_id=null declare @jur_id numeric declare @plan_id numeric declare @plan2partner_id numeric declare @invest_subc_str varchar(30) declare @portf_subc_str varchar(30) declare @bal_portf_subc_str varchar(30) declare @bank_acc_subc_str varchar(30) declare @ass_place_subc_str varchar(30) --учетная организация select @jur_id=@organisation_id --план счетов select @plan_id=(select back_money_id from tb_broker_change where partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO')) select @plan2partner_id=(select id from t_plans2partner where plan_id=@plan_id and partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(t_plans2partner.id,'PLANS2PARTNER')) if @character_id is null begin select @purpose_class_code=(select t_classvalues.code from t_classes, t_purposes, t_item2class, t_classvalues where t_purposes.id = t_item2class.item_id and t_item2class.value_id = t_classvalues.id and t_classes.id = t_classvalues.class_id and t_classes.code = 'TARGET_PURPOSES_CLASS' and t_purposes.id = @purpose_id) end else if @character_id=1 select @purpose_class_code='OWNER_PURPOSE' else select @purpose_class_code='CLIENT_PURPOSE' ----------------------------------- select @invest_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTORS')) select @portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('PORTFOLIO')) select @bal_portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTOR2PORTFOLIO')) select @bank_acc_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('BANK_ACCOUNT')) select @ass_place_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('ASSET_PLACES')) if @purpose_class_code is null begin SELECT inv_type.type_name, inv_name.item_name, t_partners.partner_short_name, balans_portf.section_name AS bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name AS asset_place, t_bank_accounts.bank_account_no, (SELECT SUM(acc.saldo * acc.deb_or_cred) FROM t_account_now acc WHERE acc.acc_id = t_obj2accounts.account_id AND acc.plan2partner_id =@plan2partner_id and acc.subc_eff_num='{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@bank_acc_subc_str+':'+isnull(convert(varchar,rekvizit.bank_account_id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )AS sum_saldo, CONVERT(varchar(30),inv_name.id) AS sid, CONVERT(varchar(30), man_port.id) AS manager_portfolio_id_s, CONVERT(varchar(30), balans_portf.id) AS bal_portfolio_id_s, CONVERT(varchar(30), rekvizit.bank_account_id) AS banc_acc_id_s, CONVERT(varchar(30), rekvizit.asset_place_id) AS asset_place_id_s, (select case ltrim(rtrim(upper(inv_type.type_code))) when 'CLIENT_CONTRACTS' then '2' else '1' end) as character_id_s, t_bank_accounts.bank_account_name, CONVERT(varchar(30), rekvizit.rate_place_id) AS rate_place_id_s, CONVERT(varchar(30), t_bank_accounts.fund_id) AS partner_id_s, t_partners.partner_code, CONVERT(varchar(30),t_bank_accounts.partner_id) as client_id_s, t_move_directions.code as object_class_code, tb_contracts.dogexec_begin_date, tb_contracts.dogexec_end_date FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts ON tb_broker2exchange.object_class_id = t_obj2accounts.object_id INNER JOIN tb_contracts ON tb_broker2exchange.ext_broker_deal = tb_contracts.id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN t_bank_accounts ON rekvizit.bank_account_id = t_bank_accounts.id LEFT OUTER JOIN t_partners ON t_bank_accounts.partner_id = t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id where t_obj2accounts.owner_class_id =1 and t_obj2accounts.acc_perpose_id=1 and t_obj2accounts.plan_id = @plan_id and .ITEMS_EXISTS(t_obj2accounts.id) and .ITEMS_EXISTS(rekvizit.id) and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.bank_account_id=@banc_acc_id) or (@banc_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end else if @purpose_class_code='CLIENT_PURPOSE' begin SELECT inv_type.type_name, inv_name.item_name, t_partners.partner_short_name, balans_portf.section_name AS bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name AS asset_place, t_bank_accounts.bank_account_no, (SELECT SUM(acc.saldo * acc.deb_or_cred) FROM t_account_now acc WHERE acc.acc_id = t_obj2accounts.account_id AND acc.plan2partner_id =@plan2partner_id and acc.subc_eff_num='{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@bank_acc_subc_str+':'+isnull(convert(varchar,rekvizit.bank_account_id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )AS sum_saldo, CONVERT(varchar(30), inv_name.id) AS sid, CONVERT(varchar(30), man_port.id) AS manager_portfolio_id_s, CONVERT(varchar(30), balans_portf.id) AS bal_portfolio_id_s, CONVERT(varchar(30), rekvizit.bank_account_id) AS banc_acc_id_s, CONVERT(varchar(30), rekvizit.asset_place_id) AS asset_place_id_s, (select case ltrim(rtrim(upper(inv_type.type_code))) when 'CLIENT_CONTRACTS' then '2' else '1' end) as character_id_s, t_bank_accounts.bank_account_name, CONVERT(varchar(30), rekvizit.rate_place_id) AS rate_place_id_s, CONVERT(varchar(30), t_bank_accounts.fund_id) AS partner_id_s, t_partners.partner_code, CONVERT(varchar(30),t_bank_accounts.partner_id) as client_id_s, t_move_directions.code as object_class_code, tb_contracts.dogexec_begin_date, tb_contracts.dogexec_end_date FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts ON tb_broker2exchange.object_class_id = t_obj2accounts.object_id INNER JOIN tb_contracts ON tb_broker2exchange.ext_broker_deal = tb_contracts.id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN t_bank_accounts ON rekvizit.bank_account_id = t_bank_accounts.id LEFT OUTER JOIN t_partners ON t_bank_accounts.partner_id = t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id WHERE (t_obj2accounts.owner_class_id = 1) AND (t_obj2accounts.acc_perpose_id = 1) AND (t_obj2accounts.plan_id =@plan_id) AND (inv_type.type_code = 'CLIENT_CONTRACTS') and .ITEMS_EXISTS(t_obj2accounts.id) and .ITEMS_EXISTS(rekvizit.id) and .ITEMS_EXISTS(balans_portf.id) and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.bank_account_id=@banc_acc_id) or (@banc_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end else if @purpose_class_code='OWNER_PURPOSE' begin SELECT inv_type.type_name, inv_name.item_name, t_partners.partner_short_name, balans_portf.section_name AS bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name AS asset_place, t_bank_accounts.bank_account_no, (SELECT SUM(acc.saldo * acc.deb_or_cred) FROM t_account_now acc WHERE acc.acc_id = t_obj2accounts.account_id AND acc.plan2partner_id =@plan2partner_id and acc.subc_eff_num='{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@bank_acc_subc_str+':'+isnull(convert(varchar,rekvizit.bank_account_id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )AS sum_saldo, CONVERT(varchar(30), inv_name.id) AS sid, CONVERT(varchar(30), man_port.id) AS manager_portfolio_id_s, CONVERT(varchar(30), balans_portf.id) AS bal_portfolio_id_s, CONVERT(varchar(30), rekvizit.bank_account_id) AS banc_acc_id_s, CONVERT(varchar(30), rekvizit.asset_place_id) AS asset_place_id_s, (select case ltrim(rtrim(upper(inv_type.type_code))) when 'CLIENT_CONTRACTS' then '2' else '1' end) as character_id_s, t_bank_accounts.bank_account_name, CONVERT(varchar(30), rekvizit.rate_place_id) AS rate_place_id_s, CONVERT(varchar(30), t_bank_accounts.fund_id) AS partner_id_s, t_partners.partner_code, CONVERT(varchar(30),t_bank_accounts.partner_id) as client_id_s, t_move_directions.code as object_class_code, tb_contracts.dogexec_begin_date, tb_contracts.dogexec_end_date FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts ON tb_broker2exchange.object_class_id = t_obj2accounts.object_id INNER JOIN tb_contracts ON tb_broker2exchange.ext_broker_deal = tb_contracts.id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN t_bank_accounts ON rekvizit.bank_account_id = t_bank_accounts.id LEFT OUTER JOIN t_partners ON t_bank_accounts.partner_id = t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id WHERE (t_obj2accounts.owner_class_id = 1) AND (t_obj2accounts.acc_perpose_id = 1) AND (t_obj2accounts.plan_id =@plan_id) AND (inv_type.type_code <> 'CLIENT_CONTRACTS') and .ITEMS_EXISTS(t_obj2accounts.id) and .ITEMS_EXISTS(rekvizit.id) and .ITEMS_EXISTS(balans_portf.id) and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.bank_account_id=@banc_acc_id) or (@banc_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end end $ENDTEXT(11095201) $ENDFORM $FORM(UO_PROPS_MONEY_STOCK_LIST) name=Список реквизитов с остатками ДС И ЦБ на NOW class=4 filter=D_SUM_SALDO_FILTER target_state= uo=uo_list_disable dw=d_account_money_stock_saldo procedure=ap_21535000001207786 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=null posy=null width=null height=null proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, @inv_id_s varchar(30) = null, @manager_porfolio_id_s varchar(30) = null, @bal_portfolio_id_s varchar(30) = null, @banc_acc_id_s varchar(30) = null, @depo_acc_id_s varchar(30) = null, @asset_place_id_s varchar(30) = null, @stock_id_s varchar(30)=null, @client_short_name varchar(255)=null as begin declare @inv_id numeric declare @manager_porfolio_id numeric declare @bal_portfolio_id numeric declare @banc_acc_id numeric declare @depo_acc_id numeric declare @asset_place_id numeric declare @stock_id numeric if isnull(@inv_id_s,'')<>'' select @inv_id = convert(numeric,@inv_id_s) else select @inv_id=null if isnull(@manager_porfolio_id_s,'')<>'' select @manager_porfolio_id = convert(numeric,@manager_porfolio_id_s) else select @manager_porfolio_id=null if isnull(@bal_portfolio_id_s,'')<>'' select @bal_portfolio_id = convert(numeric,@bal_portfolio_id_s) else select @bal_portfolio_id=null if isnull(@banc_acc_id_s,'')<>'' select @banc_acc_id = convert(numeric,@banc_acc_id_s) else select @banc_acc_id=null if isnull(@depo_acc_id_s,'')<>'' select @depo_acc_id = convert(numeric,@depo_acc_id_s) else select @depo_acc_id=null if isnull(@asset_place_id_s,'')<>'' select @asset_place_id = convert(numeric,@asset_place_id_s) else select @asset_place_id=null if isnull(@stock_id_s,'')<>'' select @stock_id = convert(numeric,@stock_id_s) else select @stock_id=null declare @jur_id numeric declare @plan_money_id numeric declare @plan_stock_id numeric declare @plan2partn_money_id numeric declare @plan2partn_stock_id numeric declare @invest_subc_str varchar(30) declare @portf_subc_str varchar(30) declare @bal_portf_subc_str varchar(30) declare @bank_acc_subc_str varchar(30) declare @ass_place_subc_str varchar(30) declare @stock_subc_str varchar(30) declare @depo_acc_subc_str varchar(30) --учетная организация select @jur_id=.ORGANIZATION --план счетов select @plan_money_id=(select back_money_id from tb_broker_change where partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO')) select @plan_stock_id=(select back_securs_id from tb_broker_change where partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO')) select @plan2partn_money_id=(select id from t_plans2partner where plan_id=@plan_money_id and partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(t_plans2partner.id,'PLANS2PARTNER')) select @plan2partn_stock_id=(select id from t_plans2partner where plan_id=@plan_stock_id and partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(t_plans2partner.id,'PLANS2PARTNER')) ----------------------------------- select @invest_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTORS')) select @portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('PORTFOLIO')) select @bal_portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTOR2PORTFOLIO')) select @bank_acc_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('BANK_ACCOUNT')) select @ass_place_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('ASSET_PLACES')) select @stock_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('STOCK_EMIS')) select @depo_acc_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('DEPO_SUBACC')) select '' as sid, inv_type.type_name, inv_name.item_name, balans_portf.section_name bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name as asset_place, t_bank_accounts.bank_account_no, (select sum(acc.saldo*acc.deb_or_cred) from t_account_now acc where acc.acc_id=t_obj2acc_money.account_id and acc.plan2partner_id=@plan2partn_money_id and acc.subc_eff_num='{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@bank_acc_subc_str+':'+isnull(convert(varchar,rekvizit.bank_account_id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )as sum_money_saldo, td_depo_subacc.depo_subacc_no, td_depo_subacc.depo_subacc_name, (select sum(acc.saldo*acc.deb_or_cred) from t_account_now acc where acc.acc_id=t_obj2acc_stock.account_id and acc.plan2partner_id=@plan2partn_stock_id and acc.subc_eff_num= '{'+@stock_subc_str+':'+isnull(convert(varchar,@stock_id),'')+ '}{'+@depo_acc_subc_str+':'+isnull(convert(varchar,rekvizit.depo_account_id),'')+ '}{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )as sum_stock_saldo, (select case ltrim(rtrim(upper(inv_type.type_code))) when 'CLIENT_CONTRACTS' then convert(varchar(30),inv_name.id) else null end) as inv_id_s, convert(varchar(30),man_port.id) as manager_portfolio_id_s, convert(varchar(30),balans_portf.id) as bal_portfolio_id_s, convert(varchar(30),rekvizit.bank_account_id) as banc_acc_id_s, convert(varchar(30),rekvizit.depo_account_id) as depo_acc_id_s, convert(varchar(30),rekvizit.asset_place_id) as asset_place_id_s, (select case ltrim(rtrim(upper(inv_type.type_code))) when 'CLIENT_CONTRACTS' then '2' else '1' end) as character_id_s, t_bank_accounts.bank_account_name, convert(varchar(30),rekvizit.rate_place_id) as rate_place_id_s, @client_short_name as client_short_name, t_move_directions.code as object_class_code FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts t_obj2acc_money ON tb_broker2exchange.object_class_id = t_obj2acc_money.object_id INNER JOIN t_obj2accounts t_obj2acc_stock ON tb_broker2exchange.object_class_id = t_obj2acc_stock.object_id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN t_bank_accounts ON rekvizit.bank_account_id = t_bank_accounts.id LEFT OUTER JOIN td_depo_subacc ON rekvizit.depo_account_id = td_depo_subacc.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id where t_obj2acc_money.owner_class_id =1 and t_obj2acc_money.acc_perpose_id=1 and t_obj2acc_money.plan_id = @plan_money_id and t_obj2acc_stock.owner_class_id =1 and t_obj2acc_stock.acc_perpose_id=1 and t_obj2acc_stock.plan_id = @plan_stock_id and .ITEMS_EXISTS(t_obj2acc_money.id) and .ITEMS_EXISTS(t_obj2acc_stock.id) and .ITEMS_EXISTS(rekvizit.id) and .ITEMS_EXISTS(tb_broker2exchange.object_class_id) and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.bank_account_id=@banc_acc_id) or (@banc_acc_id is null)) and ((rekvizit.depo_account_id=@depo_acc_id) or (@depo_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name,inv_name.item_name end $ENDTEXT(11095201) $ENDFORM $FORM(UO_PROPS_STOCK_LIST) name=Список реквизитов с остатками ЦБ на NOW class=4 filter=D_SUM_SALDO_FILTER target_state= uo=uo_list_disable dw=d_account_stock_saldo procedure=ap_21535000001207785 patterns=GENERIC_VIEW_LIST user_define=1 isvisible=0 istab=0 posx=0 posy=0 width=4699 height=2716 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, @inv_id_s varchar(30) = null, @manager_porfolio_id_s varchar(30) = null, @bal_portfolio_id_s varchar(30) = null, @depo_acc_id_s varchar(30) = null, @asset_place_id_s varchar(30) = null, @stock_id_s varchar(30)=null, @purpose_id_s varchar(30)=null, @character_id_s varchar(30)=null as begin declare @inv_id numeric declare @manager_porfolio_id numeric declare @bal_portfolio_id numeric declare @depo_acc_id numeric declare @asset_place_id numeric declare @stock_id numeric declare @purpose_id numeric declare @purpose_class_code varchar(30) declare @character_id numeric if isnull(@inv_id_s,'')<>'' select @inv_id = convert(numeric,@inv_id_s) else select @inv_id=null if isnull(@manager_porfolio_id_s,'')<>'' select @manager_porfolio_id = convert(numeric,@manager_porfolio_id_s) else select @manager_porfolio_id=null if isnull(@bal_portfolio_id_s,'')<>'' select @bal_portfolio_id = convert(numeric,@bal_portfolio_id_s) else select @bal_portfolio_id=null if isnull(@depo_acc_id_s,'')<>'' select @depo_acc_id = convert(numeric,@depo_acc_id_s) else select @depo_acc_id=null if isnull(@asset_place_id_s,'')<>'' select @asset_place_id = convert(numeric,@asset_place_id_s) else select @asset_place_id=null if isnull(@stock_id_s,'')<>'' select @stock_id = convert(numeric,@stock_id_s) else select @stock_id=null if isnull(@purpose_id_s,'')<>'' select @purpose_id = convert(numeric,@purpose_id_s) else select @purpose_id=null if isnull(@character_id_s,'')<>'' select @character_id = convert(numeric,@character_id_s) else select @character_id =null declare @jur_id numeric declare @plan_stock_id numeric declare @plan2partn_stock_id numeric declare @invest_subc_str varchar(30) declare @portf_subc_str varchar(30) declare @bal_portf_subc_str varchar(30) declare @ass_place_subc_str varchar(30) declare @stock_subc_str varchar(30) declare @depo_acc_subc_str varchar(30) --учетная организация select @jur_id=.ORGANIZATION --план счетов select @plan_stock_id=(select back_securs_id from tb_broker_change where partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(tb_broker_change.id,'BROKER_INFO')) select @plan2partn_stock_id=(select id from t_plans2partner where plan_id=@plan_stock_id and partner_id=@jur_id and .ITEMS_EXISTS_BY_TYPE(t_plans2partner.id,'PLANS2PARTNER')) if @character_id is null begin select @purpose_class_code=(select t_classvalues.code from t_classes, t_purposes, t_item2class, t_classvalues where t_purposes.id = t_item2class.item_id and t_item2class.value_id = t_classvalues.id and t_classes.id = t_classvalues.class_id and t_classes.code = 'TARGET_PURPOSES_CLASS' and t_purposes.id = @purpose_id) end else if @character_id=1 select @purpose_class_code='OWNER_PURPOSE' else select @purpose_class_code='CLIENT_PURPOSE' ----------------------------------- select @invest_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTORS')) select @portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('PORTFOLIO')) select @bal_portf_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('INVESTOR2PORTFOLIO')) select @ass_place_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('ASSET_PLACES')) select @stock_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('STOCK_EMIS')) select @depo_acc_subc_str=convert(varchar, .SUBCTYPE_FROM_CODE('DEPO_SUBACC')) if @purpose_class_code is null begin select CONVERT(varchar(30),inv_name.id) AS sid, inv_type.type_name, inv_name.item_name, balans_portf.section_name bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name as asset_place, td_depo_subacc.depo_subacc_no, td_depo_subacc.depo_subacc_name, (select sum(acc.saldo*acc.deb_or_cred) from t_account_now acc where acc.acc_id=t_obj2acc_stock.account_id and acc.plan2partner_id=@plan2partn_stock_id and acc.subc_eff_num= '{'+@stock_subc_str+':'+isnull(convert(varchar,@stock_id),'')+ '}{'+@depo_acc_subc_str+':'+isnull(convert(varchar,rekvizit.depo_account_id),'')+ '}{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )as sum_stock_saldo, convert(varchar(30),man_port.id) as manager_portfolio_id_s, convert(varchar(30),balans_portf.id) as bal_portfolio_id_s, convert(varchar(30),rekvizit.depo_account_id) as depo_acc_id_s, convert(varchar(30),rekvizit.asset_place_id) as asset_place_id_s, convert(varchar(30),rekvizit.rate_place_id) as rate_place_id_s, t_partners.partner_short_name, convert(varchar(30),contr.client_id) as client_id_s, t_partners.partner_code, convert(varchar(30),td_depo_accounts.id) as depo_account_id_s, td_depo_accounts.depo_acc_number, t_move_directions.code as object_class_code FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id LEFT OUTER JOIN tb_contracts contr ON contr.id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts t_obj2acc_stock ON tb_broker2exchange.object_class_id = t_obj2acc_stock.object_id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN td_depo_subacc ON rekvizit.depo_account_id = td_depo_subacc.id INNER JOIN td_depo_accounts ON td_depo_subacc.depo_acc_id=td_depo_accounts.id LEFT OUTER JOIN t_partners ON contr.client_id=t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id where t_obj2acc_stock.owner_class_id =1 and t_obj2acc_stock.acc_perpose_id=1 and t_obj2acc_stock.plan_id = @plan_stock_id and .ITEMS_EXISTS(t_obj2acc_stock.id) and .ITEMS_EXISTS(rekvizit.id) and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.depo_account_id=@depo_acc_id) or (@depo_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end else if @purpose_class_code='CLIENT_PURPOSE' begin select CONVERT(varchar(30),inv_name.id) AS sid, inv_type.type_name, inv_name.item_name, balans_portf.section_name bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name as asset_place, td_depo_subacc.depo_subacc_no, td_depo_subacc.depo_subacc_name, (select sum(acc.saldo*acc.deb_or_cred) from t_account_now acc where acc.acc_id=t_obj2acc_stock.account_id and acc.plan2partner_id=@plan2partn_stock_id and acc.subc_eff_num= '{'+@stock_subc_str+':'+isnull(convert(varchar,@stock_id),'')+ '}{'+@depo_acc_subc_str+':'+isnull(convert(varchar,rekvizit.depo_account_id),'')+ '}{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )as sum_stock_saldo, convert(varchar(30),man_port.id) as manager_portfolio_id_s, convert(varchar(30),balans_portf.id) as bal_portfolio_id_s, convert(varchar(30),rekvizit.depo_account_id) as depo_acc_id_s, convert(varchar(30),rekvizit.asset_place_id) as asset_place_id_s, convert(varchar(30),rekvizit.rate_place_id) as rate_place_id_s, t_partners.partner_short_name, convert(varchar(30),contr.client_id) as client_id_s, t_partners.partner_code, convert(varchar(30),td_depo_accounts.id) as depo_account_id_s, td_depo_accounts.depo_acc_number, t_move_directions.code as object_class_code FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id LEFT OUTER JOIN tb_contracts contr ON contr.id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts t_obj2acc_stock ON tb_broker2exchange.object_class_id = t_obj2acc_stock.object_id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN td_depo_subacc ON rekvizit.depo_account_id = td_depo_subacc.id INNER JOIN td_depo_accounts ON td_depo_subacc.depo_acc_id=td_depo_accounts.id LEFT OUTER JOIN t_partners ON contr.client_id=t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id where t_obj2acc_stock.owner_class_id =1 and t_obj2acc_stock.acc_perpose_id=1 and t_obj2acc_stock.plan_id = @plan_stock_id and .ITEMS_EXISTS(t_obj2acc_stock.id) and .ITEMS_EXISTS(rekvizit.id) and (inv_type.type_code = 'CLIENT_CONTRACTS') and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.depo_account_id=@depo_acc_id) or (@depo_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end else if @purpose_class_code='OWNER_PURPOSE' begin select CONVERT(varchar(30),inv_name.id) AS sid, inv_type.type_name, inv_name.item_name, balans_portf.section_name bal_portf_name, isnull(man_port.portfolio_name,'Не привязан') as portfolio_name, asset_name.item_name as asset_place, td_depo_subacc.depo_subacc_no, td_depo_subacc.depo_subacc_name, (select sum(acc.saldo*acc.deb_or_cred) from t_account_now acc where acc.acc_id=t_obj2acc_stock.account_id and acc.plan2partner_id=@plan2partn_stock_id and acc.subc_eff_num= '{'+@stock_subc_str+':'+isnull(convert(varchar,@stock_id),'')+ '}{'+@depo_acc_subc_str+':'+isnull(convert(varchar,rekvizit.depo_account_id),'')+ '}{'+@invest_subc_str+':'+isnull(convert(varchar,inv_name.id),'')+ '}{'+@portf_subc_str+':'+isnull(convert(varchar,man_port.id),'')+ '}{'+@bal_portf_subc_str+':'+isnull(convert(varchar,balans_portf.id),'')+ '}{'+@ass_place_subc_str+':'+isnull(convert(varchar,rekvizit.asset_place_id),'')+'}' )as sum_stock_saldo, convert(varchar(30),man_port.id) as manager_portfolio_id_s, convert(varchar(30),balans_portf.id) as bal_portfolio_id_s, convert(varchar(30),rekvizit.depo_account_id) as depo_acc_id_s, convert(varchar(30),rekvizit.asset_place_id) as asset_place_id_s, convert(varchar(30),rekvizit.rate_place_id) as rate_place_id_s, t_partners.partner_short_name, convert(varchar(30),contr.client_id) as client_id_s, t_partners.partner_code, convert(varchar(30),td_depo_accounts.id) as depo_account_id_s, td_depo_accounts.depo_acc_number, t_move_directions.code as object_class_code FROM tb_portfolio_section balans_portf INNER JOIN t_items inv_name ON balans_portf.investor_id = inv_name.id LEFT OUTER JOIN tb_contracts contr ON contr.id = inv_name.id INNER JOIN t_types inv_type ON inv_name.type_id = inv_type.id INNER JOIN tb_official_props rekvizit ON balans_portf.id = rekvizit.portfolio_sub_id INNER JOIN t_items asset_name ON rekvizit.asset_place_id = asset_name.id INNER JOIN tb_broker2exchange ON rekvizit.asset_place_id = tb_broker2exchange.id INNER JOIN t_obj2accounts t_obj2acc_stock ON tb_broker2exchange.object_class_id = t_obj2acc_stock.object_id LEFT OUTER JOIN tb_portfolio man_port ON rekvizit.portfolio_meneger_id = man_port.id LEFT OUTER JOIN td_rate_places ON rekvizit.rate_place_id = td_rate_places.id LEFT OUTER JOIN td_depo_subacc ON rekvizit.depo_account_id = td_depo_subacc.id INNER JOIN td_depo_accounts ON td_depo_subacc.depo_acc_id=td_depo_accounts.id LEFT OUTER JOIN t_partners ON contr.client_id=t_partners.id INNER JOIN t_move_directions ON tb_broker2exchange.object_class_id=t_move_directions.id where t_obj2acc_stock.owner_class_id =1 and t_obj2acc_stock.acc_perpose_id=1 and t_obj2acc_stock.plan_id = @plan_stock_id and .ITEMS_EXISTS(t_obj2acc_stock.id) and .ITEMS_EXISTS(rekvizit.id) and (inv_type.type_code <> 'CLIENT_CONTRACTS') and (exists (select 1 from td_depo_docs -- items_exists where td_depo_docs.id = inv_name.id and td_depo_docs.owner_id = @jur_id ) or exists(select 1 from tb_broker_change where tb_broker_change.id=inv_name.id and tb_broker_change.partner_id = @jur_id ) ) and ((inv_name.id=@inv_id) or (@inv_id is null)) and ((man_port.id=@manager_porfolio_id) or (@manager_porfolio_id is null)) and ((balans_portf.id=@bal_portfolio_id) or (@bal_portfolio_id is null)) and ((rekvizit.depo_account_id=@depo_acc_id) or (@depo_acc_id is null)) and ((rekvizit.asset_place_id=@asset_place_id) or (@asset_place_id is null)) order by inv_type.type_name, inv_name.item_name end end $ENDTEXT(11095201) $ENDFORM $ACTION(ACTION_DELETE) name=Удалить class=3 form=OFFICIAL_PROPS_EDIT target_state=DEL procedure=ap_10000000000031035 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null as declare @action_id numeric, @id numeric, @user_id numeric, @state_id numeric, @new_state_id numeric, @type_id numeric begin tran select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .CHECK_USER .CHECK_UPDATE .UPDATE_STATE .ACTION_HISTORY /*-------Проводки ----------*/ commit tran $ENDTEXT(11095201) $ENDACTION $ACTION(ACTION_INSERT) name=Добавить class=1 form=OFFICIAL_PROPS_EDIT target_state=ACT procedure=ap_10000000000031036 patterns=GENERIC_INSERT user_define=1 isvisible=0 istab=1 posx=0 posy=0 width=2290 height=1132 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST 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 begin tran select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .CHECK_USER .CHECK_UPDATE .ARGCONVERT .INIT_VARS .TEST_UNIQUE .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran select sid = convert(varchar, @id) end $ENDTEXT(11095201) $ENDACTION $ACTION(ACTION_UPDATE) name=Изменить class=2 form=OFFICIAL_PROPS_EDIT target_state=null procedure=ap_10000000000031037 patterns=GENERIC_UPDATE user_define=1 isvisible=0 istab=1 posx=0 posy=0 width=2418 height=1396 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null, .ARGLIST 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 tran select @action_id = %ACTION_ID% select @id = convert(numeric, @sid) .CHECK_USER .CHECK_UPDATE .ARGCONVERT .TEST_UNIQUE .UPDATE_ITEMS .UPDATE .RELGEN .UPDATE_STATE .ACTION_HISTORY commit tran $ENDTEXT(11095201) $ENDACTION $ACTION(ACTUAL) name=Блокировать class=9 form=null target_state=BLK procedure=ap_10000000000048381 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE begin tran .UPDATE_STATE .ACTION_HISTORY /*-------Проводки ----------*/ commit tran .END $ENDTEXT(11095201) $ENDACTION $ACTION(CANCEL_ACTUAL) name=Отменить class=9 form=null target_state=ACT procedure=ap_10000000000048382 patterns=GENERIC_MOVE user_define=0 isvisible=0 istab=0 posx=0 posy=0 width=0 height=0 proc_text=$TEXT(11095201) create procedure dbo.%PROC% @sid varchar(30) = null as .BEGIN('N') .CHECK_USER .CHECK_UPDATE begin tran .UPDATE_STATE .ACTION_HISTORY /*-------Проводки ----------*/ commit tran .END $ENDTEXT(11095201) $ENDACTION $STATE2ACTION(ACT.ACTION_DELETE) state=ACT action=ACTION_DELETE $ENDSTATE2ACTION $STATE2ACTION(ACT.ACTION_UPDATE) state=ACT action=ACTION_UPDATE $ENDSTATE2ACTION $STATE2ACTION(ACT.ACTUAL) state=ACT action=ACTUAL $ENDSTATE2ACTION $STATE2ACTION(ACT.D_OFFICIAL_PROPS_LIST) state=ACT action=D_OFFICIAL_PROPS_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.D_OFFICIAL_PROPS_ORG_LIST) state=ACT action=D_OFFICIAL_PROPS_ORG_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.OFFICIAL_PROPS) state=ACT action=OFFICIAL_PROPS $ENDSTATE2ACTION $STATE2ACTION(ACT.OFFICIAL_PROPS_EDIT) state=ACT action=OFFICIAL_PROPS_EDIT $ENDSTATE2ACTION $STATE2ACTION(ACT.OFFICIAL_PROPS_LIST) state=ACT action=OFFICIAL_PROPS_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.OFFICIAL_PROPS_LIST_WDEALTYPE) state=ACT action=OFFICIAL_PROPS_LIST_WDEALTYPE $ENDSTATE2ACTION $STATE2ACTION(ACT.UO_BANKACC2ASSET_LIST) state=ACT action=UO_BANKACC2ASSET_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.UO_DEPOACC2ASSET_LIST) state=ACT action=UO_DEPOACC2ASSET_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.UO_PROPS_MONEY_LIST) state=ACT action=UO_PROPS_MONEY_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.UO_PROPS_MONEY_STOCK_LIST) state=ACT action=UO_PROPS_MONEY_STOCK_LIST $ENDSTATE2ACTION $STATE2ACTION(ACT.UO_PROPS_STOCK_LIST) state=ACT action=UO_PROPS_STOCK_LIST $ENDSTATE2ACTION $STATE2ACTION(BLK.ACTION_UPDATE) state=BLK action=ACTION_UPDATE $ENDSTATE2ACTION $STATE2ACTION(BLK.CANCEL_ACTUAL) state=BLK action=CANCEL_ACTUAL $ENDSTATE2ACTION $STATE2ACTION(BLK.D_OFFICIAL_PROPS_LIST) state=BLK action=D_OFFICIAL_PROPS_LIST $ENDSTATE2ACTION $STATE2ACTION(BLK.OFFICIAL_PROPS) state=BLK action=OFFICIAL_PROPS $ENDSTATE2ACTION $STATE2ACTION(BLK.OFFICIAL_PROPS_EDIT) state=BLK action=OFFICIAL_PROPS_EDIT $ENDSTATE2ACTION $STATE2ACTION(BLK.UO_BANKACC2ASSET_LIST) state=BLK action=UO_BANKACC2ASSET_LIST $ENDSTATE2ACTION $STATE2ACTION(NEW.ACTION_INSERT) state=NEW action=ACTION_INSERT $ENDSTATE2ACTION $STATE2PROP(ACT.ASSET_PLACE_ID) state=ACT prop=ASSET_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.BANK_ACCOUNT_ID) state=ACT prop=BANK_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.COMM_OFFICIAL) state=ACT prop=COMM_OFFICIAL enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.DEPO_ACCOUNT_ID) state=ACT prop=DEPO_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.INVESTOR_ID) state=ACT prop=INVESTOR_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.PORTFOLIO_MENEGER_ID) state=ACT prop=PORTFOLIO_MENEGER_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.PORTFOLIO_SUB_ID) state=ACT prop=PORTFOLIO_SUB_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(ACT.RATE_PLACE_ID) state=ACT prop=RATE_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BLK.ASSET_PLACE_ID) state=BLK prop=ASSET_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BLK.BANK_ACCOUNT_ID) state=BLK prop=BANK_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BLK.COMM_OFFICIAL) state=BLK prop=COMM_OFFICIAL enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(BLK.DEPO_ACCOUNT_ID) state=BLK prop=DEPO_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(BLK.RATE_PLACE_ID) state=BLK prop=RATE_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.ASSET_PLACE_ID) state=NEW prop=ASSET_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.BANK_ACCOUNT_ID) state=NEW prop=BANK_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.COMM_OFFICIAL) state=NEW prop=COMM_OFFICIAL enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.DEPO_ACCOUNT_ID) state=NEW prop=DEPO_ACCOUNT_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.INVESTOR_ID) state=NEW prop=INVESTOR_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.PORTFOLIO_MENEGER_ID) state=NEW prop=PORTFOLIO_MENEGER_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.PORTFOLIO_SUB_ID) state=NEW prop=PORTFOLIO_SUB_ID enable=1 mandatory=0 visible=1 $ENDSTATE2PROP $STATE2PROP(NEW.RATE_PLACE_ID) state=NEW prop=RATE_PLACE_ID enable=1 mandatory=1 visible=1 $ENDSTATE2PROP $ENDDOC