create procedure dbo.%PROC% @sid varchar(30) = null
as
declare @id numeric
if @sid='' select @sid=null
select @id=convert(numeric,@sid)


create table #lines (
 row int not null,
 row_name varchar(255) not null,
 row_code varchar(10) null,
 asset_sum money null,
 asset_part decimal(18,8) null,
 stock_part decimal(18,8) null,
 comment varchar(255) null
)

insert into #lines (row,row_name,row_code)
select 1000,'Денежные средства на банковских счетах, всего','100'
union select 1005,'в том числе:',null
union select 1100,'- в рублях','110'
union select 1200,'- в иностранной валюте','120'
union select 2000,'Денежные средства в банковских вкладах, всего','200'
union select 2005,'в том числе:',null
union select 2100,'- в рублях','210'
union select 2200,'- в иностранной валюте','220'
union select 3000,'Ценные бумаги, имеющие признаваемую котировку, всего','300'
union select 3005,'в том числе:',null
union select 3100,'ценные бумаги российских эмитентов, включенные в котировальные списки организаторов торговли на рынке ценных бумаг:','310'
union select 3105,'включая',null
union select 3110,'- государственные ценные бумаги Российской Федерации','311'
union select 3120,'- государственные ценные бумаги субъектов Российской Федерации','312'
union select 3130,'- муниципальные ценные бумаги','313'
union select 3140,'- облигации российских хозяйственных обществ','314'
union select 3150,'- обыкновенные акции открытых акционерных обществ, за исключением акций акционерных инвестиционных фондов','315'
union select 3160,'- обыкновенные акции акционерных инвестиционных фондов','316'
union select 3170,'- привилегированные акции открытых акционерных обществ','317'
union select 3180,'- инвестиционные паи паевых инвестиционных фондов','318'
union select 3200,'- ценные бумаги российских эмитентов, не включенные в котировальные списки организаторов торговли на рынке ценных бумаг:','320'
union select 3205,'включая',null
union select 3210,'- государственные ценные бумаги Российской Федерации','321'
union select 3220,'- государственные ценные бумаги субъектов Российской Федерации','322'
union select 3230,'- муниципальные ценные бумаги','323'
union select 3240,'- облигации российских хозяйственных обществ','324'
union select 3250,'- обыкновенные акции открытых акционерных обществ, за исключением акций акционерных инвестиционных фондов','325'
union select 3260,'- обыкновенные акции акционерных инвестиционных фондов','326'
union select 3270,'- привилегированные акции открытых акционерных обществ','327'
union select 3280,'- обыкновенные акции закрытых акционерных обществ','328'
union select 3290,'- инвестиционные паи паевых инвестиционных фондов','329'
union select 4000,'Ценные бумаги российских эмитентов, не имеющие признаваемую котировку, всего','400'
union select 4005,'в том числе:',null
union select 4100,'- государственные ценные бумаги Российской Федерации','410'
union select 4200,'- государственные ценные бумаги субъектов Российской Федерации','420'
union select 4300,'- муниципальные ценные бумаги','430'
union select 4400,'- облигации российских хозяйственных обществ','440'
union select 4500,'- обыкновенные акции открытых акционерных обществ, за исключением акций акционерных инвестиционных фондов','450'
union select 4600,'- обыкновенные акции акционерных инвестиционных фондов','460'
union select 4700,'- привилегированные акции открытых акционерных обществ','470'
union select 4800,'- обыкновенные акции закрытых акционерных обществ','480'
union select 4900,'- инвестиционные паи паевых инвестиционных фондов','490'
union select 4910,'- векселя','491'
union select 5000,'Ценные бумаги иностранных эмитентов, всего','500'
union select 5005,'в том числе:',null
union select 5100,'- ценные бумаги иностранных государств','510'
union select 5200,'- ценные бумаги международных финансовых организаций','520'
union select 5300,'- облигации иностранных коммерческих организаций','530'
union select 5400,'- акции иностранных акционерных обществ','540'
union select 6000,'Доли в уставных капиталах российских обществ с ограниченной ответственностью','600'
union select 7000,'Недвижимое имущество','700'
union select 8000,'Имущественные права на недвижимое имущество','800'
union select 9000,'Строящиеся и реконструируемые объекты недвижимого имущества','900'
union select 10000,'Проектно-сметная документация','1000'
union select 11000,'Иные доходные вложения в материальные ценности','1100'
union select 12000,'Дебиторская задолженность, в том числе:','1200'
union select 12100,'- средства, переданные профессиональным участникам рынка ценных бумаг','1210'
union select 12200,'- дебиторская задолженность по сделкам купли-продажи имущества',1220
union select 12300,'- дебиторская задолженность по процентному (купонному) доходу по банковским вкладам и ценным бумагам','1230'
union select 12400,'- прочая дебиторская задолженность','1240'
union select 13000,'Итого активов (строки 100 + 200 + 300 + 400 + 500 + 600 + 700 + 800 + 900 + 1000 + 1100 + 1200)','1300'


--------------------------------------------------------------------------------------------

declare
 @plan_id numeric,
 @fund_id numeric,

 @acc011_id numeric,
 @acc081_id numeric,
 @acc51_id numeric,
 @acc581_id numeric,
 @acc582_id numeric,
 @acc583_id numeric,
 @acc551_id numeric, --sys=76.5.4
 @acc7651_id numeric,
 @acc7652_id numeric,
 @acc7653_id numeric,
 @acc7655_id numeric,
 @acc7610_id numeric,
 @acc7612_id numeric,
 @acc7613_id numeric,
 @acc7615_id numeric,
 @acc7616_id numeric,
 @acc962_id numeric



select @plan_id=.ARG_PLAN('PIF_ACCOUNT')

select @acc011_id = .SD_ACCID_FROM_EFFNO(@plan_id,'01.1')
select @acc081_id = .SD_ACCID_FROM_EFFNO(@plan_id,'08.1')
select @acc51_id = .SD_ACCID_FROM_EFFNO(@plan_id,'51')
select @acc581_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @acc582_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.2')
select @acc583_id = .SD_ACCID_FROM_EFFNO(@plan_id,'58.3')
select @acc551_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.4')
select @acc7651_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.1')
select @acc7652_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.2')
select @acc7653_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.3')
select @acc7655_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.5')
select @acc7610_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.10')
select @acc7612_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.12')
select @acc7613_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.13')
select @acc7615_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.15')
select @acc7616_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.16')
select @acc962_id = .SD_ACCID_FROM_EFFNO(@plan_id,'89.2')

--Метрополь
declare @acc7659_id numeric
select @acc7659_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.5.9')
---------------


--------------------------------------------------------------------------------
declare
 @class_stock_type_id numeric,
 @class_share_type_id numeric,
 @class_share_rus_ao_type_id numeric,
 @class_emitent_type_id numeric,
 @class_tender_type_id numeric,
 @class_asset_type_id numeric,

 @value_in_list_id numeric,
 @value_not_in_list_id numeric,
 @value_aoi_share_id numeric,
 @value_api_share_id numeric,
 @value_share_oao_id numeric,
 @value_share_zao_id numeric,
 @value_share_aif_id numeric,
 @value_gos_owner_id numeric,
 @value_notgos_owner_id numeric,
 @value_fed_owner_id numeric,
 @value_municipal_owner_id numeric,
 @value_foreign_owner_id numeric,
 @value_pifpay_stock_id numeric,
 @value_oblig_stock_id numeric,
 @value_stock_stock_id numeric,
 @value_veks_stock_id numeric,
 @value_building_id numeric,
 @value_other_id numeric

select @class_stock_type_id=max(id) from t_nclass_types where upper(class_code)='STOCK_TYPE'
select @class_share_type_id=max(id) from t_nclass_types where upper(class_code)='SHARE_TYPE'
select @class_share_rus_ao_type_id=max(id) from t_nclass_types where upper(class_code)='SHARE_RUS_AO'
select @class_emitent_type_id=max(id) from t_nclass_types where upper(class_code)='STOCK_EMITENT_TYPE'
select @class_tender_type_id=max(id) from t_nclass_types where upper(class_code)='STOCK_ON_TENDER'
select @class_asset_type_id=max(id) from t_nclass_types where upper(class_code)='ASSET_TYPE_OTHER'

select @value_in_list_id=max(id) from t_nclass_values
 where class_type_id=@class_tender_type_id and upper(value_code)='IN_LIST'
select @value_not_in_list_id=max(id) from t_nclass_values
 where class_type_id=@class_tender_type_id and upper(value_code)='NOT_IN_LIST'

select @value_aoi_share_id=max(id) from t_nclass_values
 where class_type_id=@class_share_type_id and upper(value_code)='AOI_SHARE'
select @value_api_share_id=max(id) from t_nclass_values
 where class_type_id=@class_share_type_id and upper(value_code)='API_SHARE'

select @value_share_oao_id=max(id) from t_nclass_values
 where class_type_id=@class_share_rus_ao_type_id and upper(value_code)='SHARE_OAO'
select @value_share_zao_id=max(id) from t_nclass_values
 where class_type_id=@class_share_rus_ao_type_id and upper(value_code)='SHARE_ZAO'
select @value_share_aif_id=max(id) from t_nclass_values
 where class_type_id=@class_share_rus_ao_type_id and upper(value_code)='SHARE_AIF'

select @value_gos_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_GOS_PARTNER'
select @value_notgos_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_NOTGOS_PARTNER'
select @value_fed_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_SUB_FED'
select @value_municipal_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_MUNICIPAL'
select @value_foreign_owner_id=max(id) from t_nclass_values
 where class_type_id=@class_emitent_type_id and upper(value_code)='STOCK_FOREIGN_PARTNER'

select @value_pifpay_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='PIFPAY'
select @value_oblig_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='OBLIG'
select @value_stock_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='STOCK'
select @value_veks_stock_id=max(id) from t_nclass_values
 where class_type_id=@class_stock_type_id and upper(value_code)='VEKS'

select @value_building_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='BUILDING'
select @value_other_id=max(id) from t_nclass_values
 where class_type_id=@class_asset_type_id and upper(value_code)='OTHER'

-------------------------------------------------------------------------------------
declare
 @srez_date datetime,
 @startsrez_id numeric

select @srez_date=srez_date,
       @fund_id=fond_id,
       @startsrez_id=startsrez_id
  from tp_pif_srez where tp_pif_srez.id = @id
/*
if @startsrez_id is null
 .EXIT_MESSAGE('Не задан начальный срез!!!')
*/
create table #assets (
 acc_id numeric not null,
 asset_id numeric not null,
 asset_sum money null,
 stock_qty money null, --? decimal(18,8)
 is_rate int null,
 asset_type_id numeric null, -- значение класс., определяющего тип актива
 owner_type_id numeric null, -- значение класс., определяющего принадлежность актива
 tender_type_id numeric null, -- including in rate lists ('IN_LIST','NOT_IN_LIST')
 share_type_id numeric null, -- stock type (common,privilege)
 share_rus_type_id numeric null, -- rus ao stocks (oao,zao,aif)
 row int null,
 stock_emiss_no varchar(100) null, -- detailed info = stock_emis_no
 qty_in_emission decimal(18,0) null, -- detailed info = emission stock qty
 emitent varchar(255) null, -- detailed info = emitent
 asset_type varchar(150) null,
 comment varchar(255) null
)


create table #details(
 row int not null,
 detail varchar(255) null,
 asset_sum money null,
 asset_part decimal(18,4) null,
 stock_part decimal(18,4) null,
 comment varchar(255) null
)


--************************************** Information **********************************

----------------------------------
-- stocks and others
----------------------------------

-- stock
insert into #assets (
 acc_id, asset_id, asset_sum, stock_qty,
 is_rate,
 asset_type_id,
 owner_type_id,
 tender_type_id,
 share_type_id,
 share_rus_type_id
 )
select
 p.acc_id, p.stock_id, p.stock_sum, p.stock_qty,
 (case when isnull(p.is_rate,0)=1 and isnull(p.stock_rate,0)<>0
            and p.rate_date is not null and p.rate_date=@srez_date
        then 1
       else 0 end),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_stock_type_id),0),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_emitent_type_id),0),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_tender_type_id),0),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_share_type_id),0),
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_share_rus_ao_type_id),0)
  from tp_pif_srez_portfolio p
 where p.srez_id=@id
   and p.acc_id=@acc581_id
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO')

-- other assets
insert into #assets (
 acc_id, asset_id, asset_sum, stock_qty,
 is_rate,
 asset_type_id
 )
select
 p.acc_id, p.stock_id, p.stock_sum, p.stock_qty,
 0,
 isnull((select max(class_value_id) from t_nclass_value2item where item_id=p.stock_id
            and class_value_id is not null and class_type_id=@class_asset_type_id),0)
  from tp_pif_srez_portfolio p
 where p.srez_id=@id
   and p.acc_id in (@acc011_id,@acc081_id,@acc583_id)
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

------------update row --- may be its not correct to use CASE

update #assets set
 row=case
-- ЦБ котируемые - rated stocks
-- in list
      when is_rate=1 and owner_type_id=@value_gos_owner_id and tender_type_id=@value_in_list_id
      then 3110
      when is_rate=1 and owner_type_id=@value_fed_owner_id and tender_type_id=@value_in_list_id
      then 3120
      when is_rate=1 and owner_type_id=@value_municipal_owner_id and tender_type_id=@value_in_list_id
      then 3130
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_oblig_stock_id
       and tender_type_id=@value_in_list_id
      then 3140
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_in_list_id and share_type_id=@value_aoi_share_id
       and share_rus_type_id=@value_share_oao_id
      then 3150
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_in_list_id and share_type_id=@value_aoi_share_id
       and share_rus_type_id=@value_share_aif_id
      then 3160
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_in_list_id and share_type_id=@value_api_share_id
       and share_rus_type_id=@value_share_oao_id
      then 3170
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_pifpay_stock_id
       and tender_type_id=@value_in_list_id
      then 3180
-- not in list
      when is_rate=1 and owner_type_id=@value_gos_owner_id and tender_type_id=@value_not_in_list_id
      then 3210
      when is_rate=1 and owner_type_id=@value_fed_owner_id and tender_type_id=@value_not_in_list_id
      then 3220
      when is_rate=1 and owner_type_id=@value_municipal_owner_id and tender_type_id=@value_not_in_list_id
      then 3230
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_oblig_stock_id
       and tender_type_id=@value_not_in_list_id
      then 3240
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_not_in_list_id and share_type_id=@value_aoi_share_id
       and share_rus_type_id=@value_share_oao_id
      then 3250
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_not_in_list_id and share_type_id=@value_aoi_share_id
       and share_rus_type_id=@value_share_aif_id
      then 3260
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_not_in_list_id and share_type_id=@value_api_share_id
       and share_rus_type_id=@value_share_oao_id
      then 3270
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and tender_type_id=@value_not_in_list_id and share_type_id=@value_aoi_share_id
       and share_rus_type_id=@value_share_zao_id
      then 3280
      when is_rate=1 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_pifpay_stock_id
       and tender_type_id=@value_not_in_list_id
      then 3290
-- ЦБ некотируемые -- not rated stocks
      when is_rate=0 and owner_type_id=@value_gos_owner_id
      then 4100
      when is_rate=0 and owner_type_id=@value_fed_owner_id
      then 4200
      when is_rate=0 and owner_type_id=@value_municipal_owner_id
      then 4300
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_oblig_stock_id
      then 4400
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and share_type_id=@value_aoi_share_id and share_rus_type_id=@value_share_oao_id
      then 4500
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and share_type_id=@value_aoi_share_id and share_rus_type_id=@value_share_aif_id
      then 4600
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and share_type_id=@value_api_share_id and share_rus_type_id=@value_share_oao_id
      then 4700
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_stock_stock_id
       and share_type_id=@value_aoi_share_id and share_rus_type_id=@value_share_zao_id
      then 4800
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_pifpay_stock_id
      then 4900
      when is_rate=0 and owner_type_id=@value_notgos_owner_id and asset_type_id=@value_veks_stock_id
      then 4910
-- Foreign stocks (!!! 510,520 - can't find, additional classification is needed)
      when owner_type_id=@value_foreign_owner_id and asset_type_id=@value_oblig_stock_id
      then 5300
      when owner_type_id=@value_foreign_owner_id and asset_type_id=@value_stock_stock_id
      then 5400
      when acc_id=@acc011_id and asset_type_id=@value_building_id
      then 7000
      when acc_id=@acc081_id and asset_type_id=@value_building_id
      then 8000
      when acc_id=@acc583_id and asset_type_id=@value_other_id
      then 11000
    end

/*
update #assets set
 row=6000
where acc_id=@acc583_id and
      asset_type_id=@value...? new value?!!
*/
-- realty (900 and 1000 - can't get)


--getting detail information----------------------------------
update #assets set
 stock_emiss_no=isnull(e.emiss_statereg_no,'?'), --old=e.stock_emiss_no
 qty_in_emission=e.qty_in_emission,
 emitent=(select isnull(ei.emi_name,'?') from td_emitent_info ei where ei.id=e.emmitent_id),
 asset_type=case
             when asset_type_id=@value_pifpay_stock_id
             then 'Пай'
             when asset_type_id=@value_oblig_stock_id
             then 'Облигация'
             when asset_type_id=@value_stock_stock_id
             then 'Акция'
             when asset_type_id=@value_veks_stock_id
             then 'Вексель'
             when asset_type_id=@value_building_id
             then 'Недвижимость'
             when asset_type_id=@value_other_id
             then 'Проч. актив'
            end,
 comment=convert(varchar,isnull(e.stock_cancel_date,''),4)
from td_stock_emis e
where e.id=#assets.asset_id and
      .ITEMS_EXISTS_BY_TYPE(#assets.asset_id,'TD_STOCK_EMIS')

update #assets set
 stock_emiss_no=isnull(r.realty_other_assets_name,'?')+', '+
                isnull(r.realty_other_assets_code,'?')+', '+isnull(r.reg_no,'?'),
 emitent=(select tp.partner_name from t_partners tp where tp.id=r.owner_id)
from t_realty_other_assets r
where r.id=#assets.asset_id and
      .ITEMS_EXISTS_BY_TYPE(#assets.asset_id,'REALTY_OTHER_ASSETS')



insert #details(row,detail,asset_sum,stock_part,comment)
select a.row,
       case
        when a.row in (3110,3120,3130,3140,3210,3220,3230,3240,4100,4200,4300,4400,5100,5200,5300) or
             a.row in (3150,3160,3170,3250,3260,3270,3280,4500,4600,4700,4800,4910,5400)
        then isnull(a.emitent,'X')+' '+isnull(('№ гос. рег.: '+a.stock_emiss_no),'X') +' '+isnull(('('+a.asset_type+')'),'')
        when a.row in (3180,3290,4900)
        then isnull(a.emitent,'X') + isnull((select comment from t_nclass_value2item
                                              where item_id=a.asset_id and
                                                    class_value_id=@value_pifpay_stock_id),'X')
        when a.row in (6000,7000,8000)
        then isnull(a.emitent,'X')
        end,
       isnull(a.asset_sum,0),
       case
        when (a.row in (3110,3120,3130,3140,3150,3160,3170,3180,3210,3220,3230,3240,3250,3260,3270,3280,3290) or
              a.row in (4100,4200,4300,4400,4500,4600,4700,4800,4900,5100,5200,5300,5400)) and
             isnull(a.qty_in_emission,0)>0
        then isnull(a.stock_qty,0)*100/a.qty_in_emission
        else 0
       end,
       case
        when a.row in (3110,3120,3130,3140,3210,3220,3230,3240,4100,4200,4300,4400,4500,4600,4700,4800,4900,4910)
        then a.comment
       end
from #assets a
where a.row in (3110,3120,3130,3140,3150,3160,3170,3180,3210,3220,3230,3240,3250,3260,3270,3280,3290) or
      a.row in (4100,4200,4300,4400,4500,4600,4700,4800,4900,4910,5100,5200,5300,5400,6000,7000,8000)

-------------------------------------------------------------

-- to report
update #lines set
 asset_sum=(select sum(isnull(#assets.asset_sum,0))
            from #assets where #assets.row=#lines.row and #assets.row is not null)

--310
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
           where row in (3110,3120,3130,3140,3150,3160,3170,3180)),
 comment='Дата погашения'
where row=3100

--320
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
           where row in (3210,3220,3230,3240,3250,3260,3270,3280,3290)),
 comment='Дата погашения'
where row=3200

--300
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
           where row in (3100,3200))
where row=3000

--400
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
           where row in (4100,4200,4300,4400,4500,4600,4700,4800,4900,4910)),
 comment='Дата погашения'
where row=4000

--500
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
           where row in (5100,5200,5300,5400))
where row=5000


----------------
-- Money
----------------

-- 100 - at bank accounts
insert #details(row,detail,asset_sum)
select 1100,isnull('Расчетный счет в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@id
   and p.acc_id=@acc51_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)='RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

insert #details(row,detail,asset_sum)
select 1200,isnull('Расчетный счет в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@id
   and p.acc_id=@acc51_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)<>'RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

update #lines set
 asset_sum=(select sum(isnull(d.asset_sum,0))
            from #details d
           where d.row=#lines.row)
 where row in (1100,1200)

update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines where row in (1100,1200))
 where row=1000


-- 200 - deposit
insert #details(row,detail,asset_sum)
select 2100,isnull('Депозит в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@id
   and p.acc_id=@acc582_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)='RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

insert #details(row,detail,asset_sum)
select 2200,isnull('Депозит в '+tp.partner_name,''),p.stock_sum
 from tp_pif_srez_portfolio p, t_bank_accounts tb, t_partners tp, t_banks b
 where p.srez_id=@id
   and p.acc_id=@acc582_id
   and p.stock_id=tb.id
   and b.id=tb.bank_id
   and tp.id=b.partner_id
   and .CUR_CODE(tb.bank_acc_cur_id)<>'RUR'
   and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_ACCOUNTS')

update #lines set
 asset_sum=(select sum(isnull(d.asset_sum,0))
            from #details d
           where d.row=#lines.row)
 where row in (2100,2200)

update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines where row in (2100,2200))
 where row=2000

----------------
-- Задолженность-debts
----------------

-- 1200 - деб.задолженность
update #lines set
 asset_sum= (select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
-----из-за 1230-------
+(select isnull(sum(isnull(p.coupon_sum,0)),0)
            from tp_pif_srez_portfolio p
            where p.srez_id=@id
              and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO'))
----------
 where row=12000


-- 1210 - проф.участники - profi
update #lines set
 asset_sum= /* (select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id=@acc551_id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=12100

--Метрополь
update #lines set
 asset_sum=asset_sum+(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id=@acc7659_id
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')) */
-----------------------------
(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id in (@acc551_id,@acc7659_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=12100
-----------

-- 1220 - покупка имущества - asset buying
update #lines set
 asset_sum= /* (select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id in (@acc7651_id,@acc7652_id,@acc7653_id,@acc7655_id,
                              @acc7612_id,@acc7613_id,@acc7615_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')) */
------------------------------------
(select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id in (@acc7651_id,@acc7652_id,@acc7653_id,@acc7655_id,
                              @acc7612_id,@acc7613_id,@acc7615_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS'))
 where row=12200

-- 1230 - купоны и % по депозитам - coupon and deposit percents
--------------
update #lines set
 asset_sum= (select isnull(sum(isnull(p.coupon_sum,0)),0)
            from tp_pif_srez_portfolio p
            where p.srez_id=@id
              and .ITEMS_EXISTS_BY_TYPE(p.id,'PIF_SREZ_PORTFOLIO'))+
 isnull((select sum(isnull(b.saldo_deb,0))
            from tp_pif_srez_balance b
           where b.srez_id=@id
             and b.acc_id in (@acc7610_id)
             and .ITEMS_EXISTS_BY_TYPE(b.id,'PIF_SREZ_PARTNERS')),0)
 where row=12300
--------------


-- 1240 - проч. деб. задолженность - other debts

update #lines set
 asset_sum=(select isnull(asset_sum,0) from #lines where row=12000)-
           (select sum(isnull(asset_sum,0)) from #lines where row in (12100,12200,12300))
where row=12400

-- 1300 - Itogo
update #lines set
 asset_sum=(select sum(isnull(asset_sum,0)) from #lines
             where row in (1000,2000,3000,4000,5000,6000,7000,8000,9000,10000,11000,12000))
where row=13000

declare @itogo money
select @itogo=isnull(round(asset_sum,2),0) from #lines where row=13000


insert #lines(row,row_name,asset_sum,stock_part,comment)
select d.row,d.detail,isnull(d.asset_sum,0),isnull(d.stock_part,0),comment
 from #details d

--calculating of asset_part
update #lines set
 asset_part=case
             when @itogo!=0
             then isnull(round(asset_sum,2),0)*100/convert(decimal(18,8),@itogo)
             else 0
            end
 where isnull(asset_sum,0)!=0


--assets that don't have needed classification--------------------------------------------

if exists(select 1 from #assets where row is null)
  insert #lines (row,row_name,row_code,asset_sum)
  select 30000,
         'Актив: '+isnull(ti.item_name,'-')+
         ', эмитент/владелец: '+isnull(a.emitent,'-')+
         ', № выпуска/код: '+isnull(a.stock_emiss_no,'-'),
         null,
         a.asset_sum
  from #assets a, t_items ti
  where a.row is null and
        ti.id=a.asset_id

--------------------------------------------------------------------------------------------




select
 row as row,
 row_name as row_name,
 row_code as row_code,
 asset_sum=
  case
   when asset_sum=0 then null
   else round(asset_sum/1000,3)
  end,
 convert(decimal(18,4),round(asset_part,4)) as asset_part,
 stock_part=
  case
   when stock_part=0 then null
   else convert(decimal(18,4),stock_part)
  end,
 comment as comment
from #lines
order by row asc, row_code desc


drop table #lines
drop table #details
drop table #assets