# Date: 30/05/06
# Version: 105

$DOC(NPF_SREZ)
 name=Срез для НПФ
 class=1
 product=ALD_NPF
 name_formula=@remark
 remark_formula=@remark

 $STATE(CRT)
  name=Создан
  class=0
 $ENDSTATE

 $STATE(NEW)
  name=Начальный
  class=1
 $ENDSTATE

 $PROP(SREZ_DATE)
  name=Дата
  field_name=srez_date
  table_name=tp_pif_srez
  field_type=datetime
  field_length=8
  field_scale=null
  field_prec=null
  page=1
  order=1
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(FOND_ID)
  name=Фонд
  field_name=fond_id
  table_name=tp_pif_srez
  field_type=numeric
  field_length=9
  field_scale=0
  field_prec=18
  page=1
  order=2
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(REMARK)
  name=Комментарий
  field_name=remark
  table_name=tp_pif_srez
  field_type=varchar
  field_length=255
  field_scale=null
  field_prec=null
  page=1
  order=3
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(STOCK_PRICE)
  name=Цена пая
  field_name=stock_price
  table_name=tp_pif_srez
  field_type=money
  field_length=8
  field_scale=null
  field_prec=null
  page=1
  order=4
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(STOCK_QTY)
  name=Количество паев
  field_name=stock_qty
  table_name=tp_pif_srez
  field_type=decimal
  field_length=9
  field_scale=8
  field_prec=18
  page=1
  order=5
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(ACTIV_SUM)
  name=Стоимость активов
  field_name=activ_sum
  table_name=tp_pif_srez
  field_type=money
  field_length=8
  field_scale=null
  field_prec=null
  page=1
  order=6
  init_class=0
  init_formula=null
  unique_index=null
  numerator=null
  import_type=null
  import_table=null
  import_field=null
  import_where=null
  ref_table=null
  ref_key=null
  ref_display=null
  ref_alias=null
  ref_where=null
 $ENDPROP

 $PROP(SCHA_SUM)
  name=Стоимость чистых активов
  field_name=scha_sum
  table_name=tp_pif_srez
  field_type=money
  field_length=8
  field_scale=null
  field_prec=null
  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(QUERY_ID)
  name=Запрос
  field_name=query_id
  table_name=tp_pif_srez
  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=npf_fund
  form=null
  prop=FOND_ID
  idname=null
  order=null
 $ENDRELATION

 $RELATION(REL3)
  class=1
  type=NPF_SREZ_PORTFOLIO
  form=D_PIF_SRES_PORTFOLIO
  prop=null
  idname=srez_id_s
  order=2
 $ENDRELATION

 $RELATION(REL4)
  class=1
  type=NPF_SREZ_BALANCE
  form=D_PIF_SREZ_BALANCE_LIST
  prop=null
  idname=srez_id_s
  order=3
 $ENDRELATION

 $RELATION(REL1)
  class=1
  type=NPF_SREZ_PARTNERS
  form=D_PIF_SREZ_PARTNERS_LIST
  prop=null
  idname=null
  order=4
 $ENDRELATION

 $RELATION(REL5)
  class=1
  type=SD_REPORTS
  form=FORM2
  prop=null
  idname=foundation_id_s
  order=6
 $ENDRELATION

 $RELATION(REL6)
  class=4
  type=npf_fund
  form=null
  prop=FOND_ID
  idname=null
  order=null
 $ENDRELATION

 $RELATION(REL7)
  class=1
  type=ASSET_STRUCTURE_DIV_LIST
  form=D_ASSET_STRUCTURE_DIV_LIST
  prop=null
  idname=report_id_s
  order=null
 $ENDRELATION

 $FORM(ASSET_EXPORT)
  name=Экспорт сведений о выявлении нарушений
  class=5
  filter=null
  target_state=
  uo=uo_dbf
  dw=d_div_npf_exp
  procedure=ap_60995000000150776
  patterns=null
  user_define=1
  isvisible=0
  istab=0
  posx=null
  posy=null
  width=null
  height=null
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
  @sid varchar(30) = null
as
declare --
        @id numeric,
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric

begin

select @action_id = %ACTION_ID%
select @id = convert(numeric, @sid)

.CHECK_USER
.CHECK_VIEW_ITEM

declare
@npf_lic_no int,
@insp_no int,
@i112 decimal(18,4),
@srez_date datetime,
@kod varchar(16),
@pos int
.ID_VAR(@foundation_id)
.ID_VAR(@owner_id)

select @foundation_id=@id
select @type_id=id from t_types where upper(type_code)='REPORT_ASSET_STRUCTURE_DIV'

select
 @i112=isnull(convert(decimal(18,4),activ_sum),0),
 @srez_date=srez_date,
 @owner_id=fond_id
from tp_pif_srez where id = @foundation_id

select @npf_lic_no=convert(integer,max(l.lic_no))
   from t_licence l, td_pay_fond where td_pay_fond.id=@owner_id and .ITEMS_EXISTS(l.id)
    and l.partner_id=td_pay_fond.id and l.activity_id=6

select @insp_no=max(insp_no) from ts_sdeps where .ITEMS_EXISTS_BY_TYPE(ts_sdeps.id,'specdep')

.TRANSACTION_SAVE

create table #rep(
npf_code varchar(50) null,
line_id numeric(18,0) null
)

insert into #rep(line_id)
 select lim_id
   from ts_asset_structure_div
  where ts_asset_structure_div.report_id=@foundation_id
    and .ITEMS_EXISTS(ts_asset_structure_div.id)

update #rep set
 npf_code=line_code
from ts_npf_reps_tuning rt
where #rep.line_id=rt.line_id
  and rt.rep_type_id=@type_id
  and .ITEMS_EXISTS(rt.id)

update #rep set
 npf_code='15'
where npf_code is null

select @pos=1
while @pos<=16
begin
 if exists(select 1 from #rep where convert(int,npf_code)=@pos)
  select @kod=@kod+'1'
 else
  select @kod=@kod+'0'
 select @pos=@pos+1
end

select
0 as n,
@insp_no as id_insp,
@srez_date as d_fisc,
@npf_lic_no as lic_npf,
round(@i112/1000,2) as i112,
@kod as kod

drop table #rep

.TRANSACTION_RESTORE

end
  $ENDTEXT(11362490)
 $ENDFORM

 $FORM(D_NPF_PARTNERS_REPORT)
  name=Отчет о дебиторских и кредиторских задолженностях
  class=5
  filter=null
  target_state=
  uo=uo_print
  dw=d_pif_partners_report
  procedure=ap_60040000000000666
  patterns=null
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
  @sid varchar(30) = null
as
declare
        @id numeric,
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @type_id_s varchar(30),
        @new_state_id numeric,
        @state_name_id_s varchar(30)

begin

select @id = convert(numeric, @sid)
select @action_id = %ACTION_ID%
select @type_id_s = NULL
select @state_name_id_s = NULL

.CHECK_USER
.CHECK_VIEW

declare @depo_name varchar(255)
select @depo_name = .DEPO_NAME

select
  sid = convert(varchar, tp_pif_srez.id),
  @depo_name as depo_name,
  tp_pif_srez.srez_date as srez_date_d,
  fond.partner_short_name as fond_name
from
  tp_pif_srez,
  t_partners fond
where
  tp_pif_srez.id = @id
  and tp_pif_srez.fond_id *= fond.id

end
  $ENDTEXT(11362490)
 $ENDFORM

 $FORM(D_NPF_PORTFOLIO_BROK)
  name=Отчет по портфелю краткий
  class=5
  filter=null
  target_state=
  uo=uo_print
  dw=d_npf_portfolio_brok
  procedure=ap_60040000000000667
  patterns=null
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
  @sid varchar(30) = null
as
declare
        @id numeric,
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @type_id_s varchar(30),
        @new_state_id numeric,
        @state_name_id_s varchar(30)

begin

select @id = convert(numeric, @sid)
select @action_id = %ACTION_ID%
select @type_id_s = NULL
select @state_name_id_s = NULL

.CHECK_USER
.CHECK_VIEW

select
  sid = convert(varchar, tp_pif_srez.id),
  tp_pif_srez.srez_date as srez_date_d,
  fond.partner_short_name as fond_name
from
  tp_pif_srez,
  t_partners fond
where
  tp_pif_srez.id = @id
  and tp_pif_srez.fond_id *= fond.id

end
  $ENDTEXT(11362490)
 $ENDFORM

 $FORM(D_NPF_PORTFOLIO_REPORT)
  name=Отчет по портфелю
  class=5
  filter=null
  target_state=
  uo=uo_print
  dw=d_npf_portfolio_report
  procedure=ap_npf_portfolio_rep_h
  patterns=null
  user_define=1
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
  @sid varchar(30) = null,
  @uk_id_s varchar(30) = null
as
declare
        @id numeric,
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric,
        @type_id_s varchar(30),
        @new_state_id numeric,
        @state_name_id_s varchar(30)

begin

select @id = convert(numeric, @sid)
select @action_id = %ACTION_ID%
select @type_id_s = NULL
select @state_name_id_s = NULL
declare @uk_name varchar(50)
select @uk_name = partner_short_name from t_partners where id = convert(numeric, @uk_id_s)

.CHECK_USER
.CHECK_VIEW

select
  sid = convert(varchar, tp_pif_srez.id),
  tp_pif_srez.srez_date as srez_date_d,
  fond.partner_short_name as fond_name,
  @uk_id_s as uk_id_s,
  @uk_name
from
  tp_pif_srez,
  t_partners fond
where
  tp_pif_srez.id = @id
  and tp_pif_srez.fond_id *= fond.id

end
  $ENDTEXT(11362490)
 $ENDFORM

 $FORM(D_NPF_SREZ_EDIT)
  name=Просмотр
  class=5
  filter=null
  target_state=
  uo=uo_edit
  dw=d_npf_srez_edit
  procedure=ap_60040000000000669
  patterns=GENERIC_VIEW_ITEM
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=2834
  height=1428
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
  @sid varchar(30) = null
as
declare
        @id numeric,
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric

begin

select @action_id = %ACTION_ID%
select @id = convert(numeric, @sid)

.CHECK_USER
.CHECK_VIEW_ITEM

if @id = -1
begin
begin tran
.GET_PROPS
.CHECK_INSERT
.INIT_VARS(B)
.INSERT_ITEMS
.INSERT
commit tran
end

.VIEW_ITEM

end
  $ENDTEXT(11362490)

  $ACTION2RELATION(REL2.PARTNER_CODE)
   relation=REL2
   where=null
   prop=PARTNER_CODE
   order=null
   alias=partner_code
  $ENDACTION2RELATION

  $ACTION2RELATION(REL2.PARTNER_SHORT_NAME)
   relation=REL2
   where=null
   prop=PARTNER_SHORT_NAME
   order=null
   alias=partner_short_name
  $ENDACTION2RELATION
 $ENDFORM

 $FORM(FORM1)
  name=Фильтр
  class=6
  filter=null
  target_state=
  uo=uo_filter
  dw=d_npf_sres_filter
  procedure=ap_60935000000007185
  patterns=EXTEND_FILTER
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=2039
  height=552
  proc_text=$TEXT(11362490)
create procedure dbo.%PROC%
as
begin

declare @srez_date_db datetime
declare @srez_date_de datetime
declare @fond_id numeric
declare @remark varchar(255)

select @fond_id = .ORGANIZATION

select
  @srez_date_db as srez_date_db,
  @srez_date_de as srez_date_de,
  convert(varchar(30), @fond_id) as fond_id_s,
  @remark as remark,
  t_partners.partner_short_name as owner_name
 from t_partners
where t_partners.id = @fond_id

end
  $ENDTEXT(11362490)

  $FILTER(SREZ_DATE)
   type=2
   label=Дата
   prop=SREZ_DATE
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(FOND_ID)
   type=1
   label=Фонд
   prop=FOND_ID
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(REMARK)
   type=3
   label=Комментарий
   prop=REMARK
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER
 $ENDFORM

 $FORM(FORM11)
  name=Отчет по портфелю УК
  class=5
  filter=null
  target_state=
  uo=uo_list_rep
  dw=d_npf_uk_portfolio_list
  procedure=ap_61005000000205620
  patterns=null
  user_define=1
  isvisible=0
  istab=0
  posx=null
  posy=null
  width=null
  height=null
  proc_text=$TEXT(11362492)
create procedure dbo.%PROC% @sid varchar(30) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_UPDATE

.GET_PROPS

select convert(varchar,@id) as sid,convert(varchar,c.client_id) as uk_id_s, partner_short_name as uk_name
from tb_contracts c, td_depo_docs d, t_partners p
where .ITEMS_EXISTS_BY_TYPE(c.id,'FUND_CONTRACTS')
and c.id=d.id
and d.owner_id=@fond_id
and c.dog_type_id=3
and p.id = c.client_id
union
select convert(varchar,@id), convert(varchar,@fond_id), 'Самостоятельное размещение'


.END
  $ENDTEXT(11362492)
 $ENDFORM

 $FORM(NPF_SREZ_LIST)
  name=Список
  class=4
  filter=FORM1
  target_state=
  uo=uo_list
  dw=d_npf_sres_list
  procedure=ap_60040000000000671
  patterns=GENERIC_VIEW_LIST
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=2834
  height=1428
  proc_text=$TEXT(11362492)
create procedure dbo.%PROC% @sid varchar(30) = null
   .ARGLIST_FILTER
as
declare
        @action_id numeric,
        @user_id numeric,
        @state_id numeric,
        @type_id numeric
begin

set nocount on

select @action_id = %ACTION_ID%

.CHECK_USER
.CHECK_VIEW

.ARGCONVERT_FILTER

.VIEW_LIST

end
  $ENDTEXT(11362492)

  $ACTION2RELATION(REL2.PARTNER_CODE)
   relation=REL2
   where=null
   prop=PARTNER_CODE
   order=null
   alias=partner_code
  $ENDACTION2RELATION

  $ACTION2RELATION(REL2.PARTNER_SHORT_NAME)
   relation=REL2
   where=null
   prop=PARTNER_SHORT_NAME
   order=null
   alias=partner_short_name
  $ENDACTION2RELATION
 $ENDFORM

 $FORM(D_PIF_SRES_LIST)
  name=Список срезов
  class=4
  filter=null
  target_state=
  uo=uo_list
  dw=d_npf_sres_list
  procedure=ap_60050000000000213
  patterns=GENERIC_VIEW_LIST
  user_define=1
  isvisible=1
  istab=1
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362492)
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%
select @item_remark=null

.CHECK_USER
.CHECK_VIEW

.ARGCONVERT_FILTER

.VIEW_LIST

end
  $ENDTEXT(11362492)

  $ACTION2RELATION(REL2.PARTNER_CODE)
   relation=REL2
   where=null
   prop=PARTNER_CODE
   order=null
   alias=partner_code
  $ENDACTION2RELATION

  $ACTION2RELATION(REL2.PARTNER_SHORT_NAME)
   relation=REL2
   where=null
   prop=PARTNER_SHORT_NAME
   order=null
   alias=partner_short_name
  $ENDACTION2RELATION

  $FILTER(QUERY_ID)
   type=11
   label=QUERY_ID
   prop=QUERY_ID
   arg_code=null
   arg_type=null
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER
 $ENDFORM

 $ACTION(ACTION1)
  name=Изменить
  class=2
  form=D_NPF_SREZ_EDIT
  target_state=null
  procedure=ap_60050000000000221
  patterns=GENERIC_UPDATE
  user_define=0
  isvisible=0
  istab=1
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362492)
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(11362492)
 $ENDACTION

 $ACTION(NPF_SREZ_CALCULATE)
  name=Пересчет среза
  class=9
  form=null
  target_state=CRT
  procedure=ap_60040000000000673
  patterns=GENERIC_MOVE
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(11362492)
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
.GET_PROPS

.TRANSACTION_SAVE

declare @plan_id numeric

select @plan_id=t_plans.id from t_plans,.ACTUAL_TABLES where t_plans.plan_code='NPF_ACCOUNT'
.ACTUAL_WHERE(t_plans.id)

if @plan_id is null
.EXIT_MESSAGE('Не найден план NPF_ACCOUNT')

declare @plan2partner numeric

select @plan2partner=(select t_plans2partner.id from t_plans2partner,.ACTUAL_TABLES
where t_plans2partner.plan_id=@plan_id and t_plans2partner.partner_id=@fond_id
.ACTUAL_WHERE(t_plans2partner.id))

if @plan2partner is null
.EXIT_MESSAGE('У фонда нет плана счетов NPF_ACCOUNT')

create table #oper_list(
  oper_list_id numeric null,
  object_qty money null,
  bal_sum money null,
  deb_or_cred int,
  acc_id numeric,
  uk_id numeric null)

create table #report(
  uk_id numeric null,
  object_id numeric null,
  object_qty money null,
  bal_sum money null,
  acc_id numeric)

declare @cur_period_id numeric,
@cur_period_date datetime

select @cur_period_id = (select id
  from t_periods
 where partner2plan_id = @plan2partner
   and period_date = (select max(period_date)
                      from t_periods
                      where partner2plan_id=@plan2partner
                        and period_date < @srez_date))


select @cur_period_date = (select period_date from t_periods where id = @cur_period_id)

declare @cur_stock_subconto numeric,
@cur_uk_subconto numeric,
@cur_other_subconto numeric,
@cur_bank_subconto numeric

select @cur_stock_subconto = .SUBCTYPE_FROM_CODE('stock_emis')
select @cur_uk_subconto = .SUBCTYPE_FROM_CODE('asset_manager')
select @cur_other_subconto = .SUBCTYPE_FROM_CODE('other_assets')
select @cur_bank_subconto = .SUBCTYPE_FROM_CODE('bank_account')

-- Активы ---------------------------------------

if @cur_period_date is null
begin
  select @cur_period_id = null
  select @cur_period_date=convert(datetime, '1/1/1800', 103)
end
else
begin

 insert into #oper_list(
  oper_list_id,
  object_qty,
  bal_sum,
  deb_or_cred,
  acc_id,
  uk_id)
 select
  t_account_history.id,
  t_account_history.saldo_qty,
  t_account_history.saldo,
  t_account_history.deb_or_cred,
  t_account_history.acc_id,
  subc1.item_id
 from t_account_history, t_acchistory_subconto subc1
 where t_account_history.period_id = @cur_period_id
  and t_account_history.id = subc1.id
  and subc1.subconto_type_id=@cur_uk_subconto

 insert into #report(acc_id, object_id, object_qty, bal_sum, uk_id)
 select
   #oper_list.acc_id,
   subc2.item_id,
   sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
   sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0)),
   #oper_list.uk_id
 from #oper_list,t_acchistory_subconto subc2
 where #oper_list.oper_list_id = subc2.id
 and subc2.subconto_type_id=@cur_stock_subconto
 group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id

 insert into #report(acc_id, object_id, object_qty, bal_sum, uk_id)
 select
   #oper_list.acc_id,
   subc2.item_id,
   sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
   sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0)),
   #oper_list.uk_id
 from #oper_list,t_acchistory_subconto subc2
 where #oper_list.oper_list_id = subc2.id
 and subc2.subconto_type_id=@cur_other_subconto
 group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id

 insert into #report(acc_id, object_id, object_qty,bal_sum,uk_id)
 select
   #oper_list.acc_id,
   subc2.item_id,
   null, -- sum(isnull(#oper_list.object_qty *#oper_list.deb_or_cred,0)),
   sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0)),
   #oper_list.uk_id
 from #oper_list,t_acchistory_subconto subc2
 where #oper_list.oper_list_id = subc2.id
 and subc2.subconto_type_id=@cur_bank_subconto
 group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id
end

truncate table #oper_list

insert into #oper_list(oper_list_id,object_qty,bal_sum,deb_or_cred,acc_id,uk_id)
select t_oper_list.id,t_oper_list.qty_sum,t_oper_list.sum_bal,t_oper_list.deb_or_cred,t_oper_list.acc_id,subc1.item_id
from t_oper_list, t_oper_subconto subc1
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and subc1.subconto_type_id=@cur_uk_subconto

insert into #report(acc_id, object_id, object_qty,bal_sum,uk_id)
select
  #oper_list.acc_id,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0)),
  #oper_list.uk_id
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_stock_subconto
group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id

insert into #report(acc_id, object_id, object_qty,bal_sum,uk_id)
select
  #oper_list.acc_id,
  subc2.item_id,
  sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0)),
  #oper_list.uk_id
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_other_subconto
group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id

insert into #report(acc_id, object_id, object_qty, bal_sum, uk_id)
select
  #oper_list.acc_id,
  subc2.item_id,
  null, -- sum(isnull(#oper_list.object_qty * #oper_list.deb_or_cred,0)),
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0)),
  #oper_list.uk_id
from #oper_list, t_oper_subconto subc2
where #oper_list.oper_list_id = subc2.id
and subc2.subconto_type_id=@cur_bank_subconto
group by #oper_list.acc_id,#oper_list.uk_id,subc2.item_id

truncate table #oper_list

-- Денежные средства

declare @acc_51 numeric,
@acc_74_2_1 numeric

select @acc_51=(select id from t_accounts
  where t_accounts.plan_id = @plan_id
    and t_accounts.acc_num_eff = '51'
    and .ITEMS_EXISTS_BY_TYPE(t_accounts.id,'accounts'))

select @acc_74_2_1=(select id from t_accounts
  where t_accounts.plan_id = @plan_id
    and t_accounts.acc_num_eff = '74.2.1'
    and .ITEMS_EXISTS_BY_TYPE(t_accounts.id,'accounts'))


-- Расч. счет ----------------------------------

if @cur_period_id is not null
begin

insert into #oper_list(oper_list_id, object_qty, bal_sum, deb_or_cred, acc_id, uk_id)
select t_account_history.id, null, t_account_history.saldo, t_account_history.deb_or_cred, t_account_history.acc_id,subc1.item_id
from t_account_history, t_acchistory_subconto subc1
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and subc1.subconto_type_id=@cur_bank_subconto
 and t_account_history.acc_id=@acc_51

insert into #report(acc_id, object_id, bal_sum, object_qty)
select
  #oper_list.acc_id,
  #oper_list.uk_id,
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0)),
  null
from #oper_list
group by #oper_list.acc_id,#oper_list.uk_id

truncate table #oper_list

end

insert into #oper_list(oper_list_id, object_qty, bal_sum, deb_or_cred, acc_id, uk_id)
select t_oper_list.id, null, t_oper_list.sum_bal, t_oper_list.deb_or_cred, t_oper_list.acc_id, subc1.item_id
from t_oper_list, t_oper_subconto subc1
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and subc1.subconto_type_id=@cur_bank_subconto
 and t_oper_list.acc_id=@acc_51


insert into #report(acc_id, object_id, bal_sum, object_qty)
select
  #oper_list.acc_id,
  #oper_list.uk_id,
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0)),
  null
from #oper_list
group by #oper_list.acc_id,#oper_list.uk_id


truncate table #oper_list

-- ДУ ----------------------------------

if @cur_period_id is not null
begin

insert into #oper_list(oper_list_id, object_qty, bal_sum, deb_or_cred, acc_id, uk_id)
select t_account_history.id, null, t_account_history.saldo, t_account_history.deb_or_cred, t_account_history.acc_id, subc1.item_id
from t_account_history, t_acchistory_subconto subc1
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and subc1.subconto_type_id=@cur_uk_subconto
 and t_account_history.acc_id=@acc_74_2_1

insert into #report(acc_id, uk_id, bal_sum, object_qty)
select
  #oper_list.acc_id,
  #oper_list.uk_id,
  sum(isnull(#oper_list.bal_sum *#oper_list.deb_or_cred,0)),
  null
from #oper_list
group by #oper_list.acc_id,#oper_list.uk_id

end

truncate table #oper_list

insert into #oper_list(oper_list_id, object_qty, bal_sum, deb_or_cred, acc_id, uk_id)
select t_oper_list.id, null, t_oper_list.sum_bal, t_oper_list.deb_or_cred, t_oper_list.acc_id, subc1.item_id
from t_oper_list, t_oper_subconto subc1
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and subc1.subconto_type_id=@cur_uk_subconto
 and t_oper_list.acc_id=@acc_74_2_1


insert into #report(acc_id, uk_id, bal_sum, object_qty)
select
  #oper_list.acc_id,
  #oper_list.uk_id,
  sum(isnull(#oper_list.bal_sum * #oper_list.deb_or_cred,0)),
  null
from #oper_list
group by #oper_list.acc_id,#oper_list.uk_id


delete #report where isnull(bal_sum,0)=0 and isnull(object_qty,0)=0

-- Портфель ----------------------------------

-- Aladdin 18.05.04

declare
 @c_uk_id numeric,
 @c_object_id numeric,
 @c_object_qty money,
 @c_bal_sum money,
 @c_acc_id numeric,
 @place_id numeric,
 @stock_rate decimal(36,16),
 @stock_rate_rur decimal(36,16),
 @coupon_rate decimal(36,16),
 @coupon_rate_rur decimal(36,16),
 @class_value_code varchar(30),
 @class_comment varchar(255),
 @rur_cur_id numeric,
 @rate_cur_id numeric,
 @rate_value money,
 @convert_rate_date datetime,
 @rate_date datetime,
 @round_rate int,
 @ocen_sum money,
 @nkd_ocen_sum money,
 @srez_place_id numeric,
 @acc_581 numeric,
 @acc766_id numeric,
 @calc_qty decimal(18,8),
 @uk_dog_id numeric,
 @class_round int,
-- Aladdin 08/09/04
 @is_rate int
-- End Aladdin 08/09/04

select @acc_581 = .SD_ACCID_FROM_EFFNO(@plan_id,'58.1')
select @acc766_id = .SD_ACCID_FROM_EFFNO(@plan_id,'76.6')

select @rur_cur_id=.CUR('RUR')

declare cur1 cursor for
select uk_id,object_id,object_qty,
  bal_sum,acc_id from #report
for read only
open cur1
fetch cur1 into @c_uk_id,@c_object_id,
@c_object_qty,@c_bal_sum,@c_acc_id

while .CURSOR_STATE=0
begin

 select @stock_rate=null, @stock_rate_rur=null, @coupon_rate=null, @coupon_rate_rur=null, @calc_qty=null,
        @place_id=null, @rate_cur_id=null, @round_rate=null, @rate_date=null, @ocen_sum=null, @nkd_ocen_sum=null

 if (@c_object_qty <> 0) or (@c_object_qty is null) or (@c_object_qty=0 and @c_acc_id=@acc766_id)
 begin

   select @is_rate=null

  .TYPE_INIT('NPF_SREZ_PORTFOLIO')
  .TYPE_ASSIGN(srez_id,@id)
  .TYPE_ASSIGN(stock_id,@c_object_id)
  .TYPE_ASSIGN(stock_qty,@c_object_qty)
  .TYPE_ASSIGN(bal_sum,@c_bal_sum)
  .TYPE_ASSIGN(acc_id,@c_acc_id)
  .TYPE_ASSIGN(ctrl_comp_id,@c_uk_id)

  -- оцен.стоимость ЦБ
  if @c_acc_id=@acc_581
  begin

-- Aladdin 08/09/04
   select @is_rate=0
-- End Aladdin 08/09/04

   if @c_uk_id=@fond_id select @round_rate=(select round_sum from td_pay_fond where id=@c_uk_id),@uk_dog_id = null
    else begin
     select @uk_dog_id = (select max(b.id) from td_depo_docs d, tb_contracts b
                              where d.id=b.id and b.dog_type_id=3
                                and d.owner_id=@fond_id
                                and b.client_id=@c_uk_id)
     select @round_rate=(select b.round_sum from tb_contracts b where b.id=@uk_dog_id)
    end
   select @round_rate=isnull(@round_rate,5)

   if @srez_place_id is null
   begin
    exec ap_getclassvalue_code @c_object_id,'STOCK_RATE_PLACE',null,@fond_id, @class_value_code out, @class_comment out
    if @class_value_code is not null
    begin
     select @place_id = max(td_rate_places.id) from td_rate_places
      where upper(td_rate_places.place_code) = upper(@class_value_code)
        and .ITEMS_EXISTS(td_rate_places.id)
     if isnull(@class_comment,'')<>'' select @class_round=convert(int,@class_comment)
    end
   end
   else
    select @place_id=@srez_place_id

   if @place_id is not null
   begin

    select @stock_rate=tb_stock_rates.stock_rate,
           @rate_date=tb_stock_rates.rate_date,
           @rate_cur_id=tb_stock_rates.rate_cur_id
      from tb_stock_rates
     where tb_stock_rates.rate_place_id = @place_id
       and tb_stock_rates.stock_id = @c_object_id
       and .ITEMS_EXISTS(tb_stock_rates.id)
       and tb_stock_rates.rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates
                                        where tb_stock_rates.rate_place_id = @place_id
                                          and tb_stock_rates.rate_date <= @srez_date
                                          and isnull(tb_stock_rates.stock_rate,0)<>0
                                          and tb_stock_rates.stock_id = @c_object_id
                                          and .ITEMS_EXISTS(tb_stock_rates.id))
----.EXIT_MESSAGE_PARM('fgfg',@rate_cur_id)

   if @stock_rate is null
     begin
select @stock_rate=tb_stock_rates.stock_rate,
       @rate_date=tb_stock_rates.rate_date,
       @rate_cur_id=tb_stock_rates.rate_cur_id,
       @place_id=tb_stock_rates.rate_place_id
      from tb_stock_rates
     where tb_stock_rates.stock_id = @c_object_id
       and tb_stock_rates.stock_rate = (select max(a.stock_rate) from tb_stock_rates a where a.stock_id = @c_object_id
                                                                   and .ITEMS_EXISTS(a.id)
                                                                   and a.rate_date = (select max(b.rate_date) from tb_stock_rates b
                                                                                       where b.rate_date <= @srez_date
                                                                                         and isnull(b.stock_rate,0)<>0
                                                                                         and b.stock_id = @c_object_id
                                                                                         and .ITEMS_EXISTS(b.id))  )
       and .ITEMS_EXISTS(tb_stock_rates.id)
       and tb_stock_rates.rate_date = (select max(tb_stock_rates.rate_date) from tb_stock_rates
                                        where tb_stock_rates.rate_date <= @srez_date
                                          and isnull(tb_stock_rates.stock_rate,0)<>0
                                          and tb_stock_rates.stock_id = @c_object_id
                                          and .ITEMS_EXISTS(tb_stock_rates.id))

     end
---.EXIT_MESSAGE_PARM('fgfg',@stock_rate)
    if @rate_cur_id is null
     select @rate_cur_id=(select td_rate_places.cur_id from td_rate_places where id=@place_id)

-- Aladdin 08/09/04
    if exists (select 1 from tb_stock_rates
                where tb_stock_rates.rate_place_id = @place_id
                  and tb_stock_rates.stock_id = @c_object_id
                  and tb_stock_rates.rate_date = @srez_date
                  and isnull(tb_stock_rates.acknowledged_price,0) <> 0
                  and .ITEMS_EXISTS(tb_stock_rates.id))
     select @is_rate=1
-- End Aladdin 08/09/04

   end

   -- ocen_sum
   if @stock_rate is not null
   begin
    if @class_round is not null select @stock_rate = round(@stock_rate,@class_round)
    if @rur_cur_id=@rate_cur_id
     select @stock_rate_rur=@stock_rate
    else
    begin
     .CONVERT_SUM_DEC(@rate_cur_id, @rur_cur_id, @srez_date, 0, @stock_rate, @stock_rate_rur, @rate_value, @convert_rate_date)
    end
    if (@uk_dog_id is null and (.GET_CLASS_VALUE_CODE_FOR_ITEM(@fond_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'))
       or (@uk_dog_id is not null and (.GET_CLASS_VALUE_CODE_FOR_ITEM(@uk_dog_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'))
     select @stock_rate_rur=round(@stock_rate_rur,@round_rate)
    select @coupon_rate=(select tb_stock_rates.coupon_rate from tb_stock_rates
                          where tb_stock_rates.rate_place_id = @place_id
                            and tb_stock_rates.stock_id = @c_object_id
                            and tb_stock_rates.rate_date = @srez_date
                            and .ITEMS_EXISTS(tb_stock_rates.id))
    if @coupon_rate is not null
    begin
     if @rur_cur_id=@rate_cur_id
      select @coupon_rate_rur=@coupon_rate
     else begin
      .CONVERT_SUM_DEC(@rate_cur_id, @rur_cur_id, @srez_date, 0, @coupon_rate, @coupon_rate_rur, @rate_value, @convert_rate_date)
     end
     if (@uk_dog_id is null and (.GET_CLASS_VALUE_CODE_FOR_ITEM(@fond_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'))
          or (@uk_dog_id is not null and (.GET_CLASS_VALUE_CODE_FOR_ITEM(@uk_dog_id,'TYPE_ROUND_RATES_IN_CALC','BEFORE')='BEFORE'))
      select @coupon_rate_rur=round(@coupon_rate,@round_rate)
    end

    select @calc_qty=convert(decimal(18,8),@c_object_qty*isnull((select qty_mult from td_stock_emis where id=@c_object_id),1))
    select @ocen_sum = round(@calc_qty * @stock_rate_rur,2)
    select @nkd_ocen_sum = round(@calc_qty * @coupon_rate_rur,2)
    select @stock_rate_rur=round(@stock_rate_rur,@round_rate)
    select @coupon_rate_rur=round(@coupon_rate,@round_rate)

   end -- ocen_sum
  end -- оцен.стоимость ЦБ

/*
if @c_acc_id=@acc_581
  begin
   -- Определение котировки
   .NPF_RATE_CHECK(@c_object_id,@fond_id,@c_uk_id,@srez_place_id,@srez_date,@c_object_qty,@rate_date,@stock_rate_rur,@coupon_rate_rur,@ocen_sum,@nkd_ocen_sum,@rate_cur_id)
  end
*/

  if @ocen_sum is null select @ocen_sum=@c_bal_sum

  .TYPE_ASSIGN(stock_sum,@ocen_sum)
  .TYPE_ASSIGN(coupon_sum,@nkd_ocen_sum)
  .TYPE_ASSIGN(stock_rate,@stock_rate_rur)
  .TYPE_ASSIGN(coupon_rate,@coupon_rate_rur)
  .TYPE_ASSIGN(rate_date,@rate_date)

-- Aladdin 08/09/04
  .TYPE_ASSIGN(is_rate,@is_rate)
-- End Aladdin 08/09/04

  .TYPE_INSERT('NPF_SREZ_PORTFOLIO','CRT')
 end

-- end - Aladdin 18.05.04

 fetch cur1 into @c_uk_id,@c_object_id,@c_object_qty,@c_bal_sum,@c_acc_id
end
close cur1
.DEALLOCATE cur1

-- Дебиторы - кредиторы ----------------------------------

create table #partners(
  acc_id numeric null,
  partner_id numeric null,
  saldo_deb money null,
  saldo_cred money null,
  foundation_id numeric null)


truncate table #report

declare @cur_partners_subconto numeric,
@cur_partners_contr_subconto numeric,
@cur_paydocs_subconto numeric

select @cur_partners_subconto = .SUBCTYPE_FROM_CODE('partners')
select @cur_paydocs_subconto = .SUBCTYPE_FROM_CODE('paydocs')
select @cur_partners_contr_subconto = .SUBCTYPE_FROM_CODE('partners_cont')

truncate table #oper_list

if @cur_period_id!=null begin

insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_account_history.id,t_account_history.saldo,t_account_history.deb_or_cred,t_account_history.acc_id
from t_account_history, t_acchistory_subconto subc1,t_accounts
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and subc1.subconto_type_id=@cur_partners_subconto
 and t_account_history.acc_id = t_accounts.id
 and t_accounts.acc_num_eff_rep LIKE '.76.%'
 and t_accounts.acc_num_eff_rep!='.76.5.'
 and t_accounts.acc_num_eff_rep!='.76.8.'

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1
group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
group by #oper_list.acc_id, subc1.item_id


end

truncate table #oper_list


insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_oper_list.id,t_oper_list.sum_bal,t_oper_list.deb_or_cred,t_oper_list.acc_id
from t_oper_list, t_oper_subconto subc1,t_accounts
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and t_oper_list.acc_id=t_accounts.id
 and subc1.subconto_type_id=@cur_partners_subconto
 and t_accounts.acc_num_eff_rep LIKE '.76.%'
 and t_accounts.acc_num_eff_rep!='.76.5.'
 and t_accounts.acc_num_eff_rep!='.76.8.'

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1

group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
group by #oper_list.acc_id, subc1.item_id


truncate table #oper_list

if @cur_period_id!=null begin

insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_account_history.id,t_account_history.saldo,t_account_history.deb_or_cred,t_account_history.acc_id
from t_account_history, t_acchistory_subconto subc1,t_accounts
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and subc1.subconto_type_id=@cur_partners_contr_subconto
 and t_account_history.acc_id = t_accounts.id
 and t_accounts.acc_num_eff_rep='.76.5.'


insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id


end

truncate table #oper_list


insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_oper_list.id,t_oper_list.sum_bal,t_oper_list.deb_or_cred,t_oper_list.acc_id
from t_oper_list, t_oper_subconto subc1,t_accounts
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and t_oper_list.acc_id=t_accounts.id
 and subc1.subconto_type_id=@cur_partners_contr_subconto
 and t_accounts.acc_num_eff_rep='.76.5.'

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

truncate table #oper_list


if @cur_period_id is not null begin

insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_account_history.id,t_account_history.saldo,t_account_history.deb_or_cred,t_account_history.acc_id
from t_account_history, t_acchistory_subconto subc1,t_accounts
where t_account_history.period_id = @cur_period_id
 and t_account_history.id = subc1.id
 and subc1.subconto_type_id=@cur_partners_contr_subconto
 and t_account_history.acc_id = t_accounts.id
 and t_accounts.acc_num_eff_rep='.76.8.'


insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id


end

truncate table #oper_list


insert into #oper_list(oper_list_id,bal_sum,deb_or_cred,acc_id)
select t_oper_list.id,t_oper_list.sum_bal,t_oper_list.deb_or_cred,t_oper_list.acc_id
from t_oper_list, t_oper_subconto subc1,t_accounts
where t_oper_list.partner2plan_id = @plan2partner
 and t_oper_list.op_date >= @cur_period_date
 and t_oper_list.op_date < dateadd(day, 1, @srez_date)
 and t_oper_list.id = subc1.id
 and t_oper_list.acc_id=t_accounts.id
 and subc1.subconto_type_id=@cur_partners_contr_subconto
 and t_accounts.acc_num_eff_rep='.76.8.'

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  sum(#oper_list.bal_sum),
  0
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = 1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

insert into #partners(
  acc_id,
  partner_id,
  saldo_deb,
  saldo_cred)
select
  #oper_list.acc_id,
  subc1.item_id,
  0,
  sum(#oper_list.bal_sum)
from #oper_list, t_oper_subconto subc1
where #oper_list.oper_list_id = subc1.id
  and #oper_list.deb_or_cred = -1
  and subc1.subconto_type_id=@cur_partners_contr_subconto
group by #oper_list.acc_id, subc1.item_id

truncate table #oper_list

declare @cur_acc_id numeric,
@cur_partner_id numeric,
@cur_saldo_deb money,
@cur_saldo_cred money,
@cur_foundation_id numeric

declare partners_cur cursor for
select acc_id, partner_id, sum(saldo_deb), sum(saldo_cred)/*, foundation_id*/
from  #partners
group by acc_id, partner_id/*, foundation_id*/
for read only

open partners_cur
fetch partners_cur into @cur_acc_id, @cur_partner_id, @cur_saldo_deb, @cur_saldo_cred/*, @cur_foundation_id*/


while (.CURSOR_STATE=0)
begin

if (@cur_saldo_deb <> @cur_saldo_cred)
begin
if @cur_saldo_deb > @cur_saldo_cred
begin
  select @cur_saldo_deb = @cur_saldo_deb - @cur_saldo_cred
  select @cur_saldo_cred = 0
end
else
begin
  select @cur_saldo_cred = @cur_saldo_cred - @cur_saldo_deb
  select @cur_saldo_deb = 0
end
.TYPE_INIT('npf_srez_partners', 2)
.TYPE_ASSIGN(srez_id,@id,2)
.TYPE_ASSIGN(acc_id,@cur_acc_id,2)
.TYPE_ASSIGN(partner_id,@cur_partner_id,2)
.TYPE_ASSIGN(saldo_deb, @cur_saldo_deb, 2)
.TYPE_ASSIGN(saldo_cred, @cur_saldo_cred, 2)
.TYPE_INSERT('npf_srez_partners','CRT',2)

end

  fetch partners_cur into @cur_acc_id, @cur_partner_id, @cur_saldo_deb, @cur_saldo_cred/*, @cur_foundation_id*/
end

close partners_cur
.DEALLOCATE partners_cur

create table #balance(
  acc_id numeric null,
  acc_num varchar(255) null,
  saldo_deb money null,
  saldo_cred money null)


truncate table #balance

if @cur_period_id is not null
begin

/* Closed Period */

insert into #balance(
  acc_id,
  saldo_deb,
  saldo_cred)
select
  t_account_history.acc_id,
  t_account_history.saldo,
  0
from t_account_history
where t_account_history.period_id = @cur_period_id
  and t_account_history.deb_or_cred = 1

insert into #balance(
  acc_id,
  saldo_deb,
  saldo_cred)
select
  t_account_history.acc_id,
  0,
  t_account_history.saldo
from t_account_history
where t_account_history.period_id = @cur_period_id
  and t_account_history.deb_or_cred = -1

/* End Closed Period */
end

insert into #balance(
  acc_id,
  saldo_deb,
  saldo_cred)
select
  t_oper_list.acc_id,
  sum(t_oper_list.sum_bal),
  0
from t_oper_list
where t_oper_list.partner2plan_id = @plan2partner
and t_oper_list.op_date >= @cur_period_date
and t_oper_list.op_date < dateadd(day, 1, @srez_date)
and t_oper_list.deb_or_cred = 1
group by t_oper_list.acc_id

insert into #balance(
  acc_id,
  saldo_deb,
  saldo_cred)
select
  t_oper_list.acc_id,
  0,
  sum(t_oper_list.sum_bal)
from t_oper_list
where t_oper_list.partner2plan_id = @plan2partner
and t_oper_list.op_date >= @cur_period_date
and t_oper_list.op_date < dateadd(day, 1, @srez_date)
and t_oper_list.deb_or_cred = -1
group by t_oper_list.acc_id


declare @cur_acc_id_b numeric
declare @cur_saldo_deb_b money
declare @cur_saldo_cred_b money

declare bal_cur cursor for
select acc_id, sum(saldo_deb), sum(saldo_cred)
from  #balance
group by acc_id
for read only

.TYPE_INIT('npf_srez_balance', 3)

open bal_cur
fetch bal_cur into @cur_acc_id_b, @cur_saldo_deb_b, @cur_saldo_cred_b

while (.CURSOR_STATE=0)
begin

if (@cur_saldo_deb_b <> @cur_saldo_cred_b)
begin

if @cur_saldo_deb_b > @cur_saldo_cred_b
begin
  select @cur_saldo_deb_b = @cur_saldo_deb_b - @cur_saldo_cred_b
  select @cur_saldo_cred_b = 0
end
else
begin
  select @cur_saldo_cred_b = @cur_saldo_cred_b - @cur_saldo_deb_b
  select @cur_saldo_deb_b = 0
end

.TYPE_ASSIGN(srez_id,@id,3)
.TYPE_ASSIGN(acc_id,@cur_acc_id_b,3)
.TYPE_ASSIGN(saldo_deb, @cur_saldo_deb_b, 3)
.TYPE_ASSIGN(saldo_cred, @cur_saldo_cred_b, 3)

.TYPE_INSERT('npf_srez_balance','CRT',3)

end

  fetch bal_cur into @cur_acc_id_b, @cur_saldo_deb_b, @cur_saldo_cred_b
end

close bal_cur
.DEALLOCATE bal_cur

declare @is_no varchar(50),
@rep_sid varchar(30)

.TYPE_INIT('REPORT_FOR_STRUCTURE_ACT_NPF',6)
.TYPE_ASSIGN(depo_doc_type,2,6)
.DEPO_GEN_NO(@is_no,@depo_doc_type6_1)
.TYPE_ASSIGN(foundation_id,@id,6)
.TYPE_ASSIGN(in_date,(.OPERDAY),6)
.TYPE_ASSIGN(owner_id,@fond_id,6)
.TYPE_ASSIGN(in_no,@is_no,6)
.TYPE_ASSIGN(date_b,@srez_date,6)
.TYPE_INSERT('REPORT_FOR_STRUCTURE_ACT_NPF','CRT',6)

update t_items set
item_remark='Срез для НПФ от'+.DATE_TO_STR(@srez_date)+' , Описание: '+@remark
where id=@id6_1

select @rep_sid=convert(varchar(30),@id6_1)

drop table #balance
drop table #partners
drop table #oper_list
drop table #report

--Задолженности  Kav added 12/05/03 begin
/*
declare @acc_id_kd numeric(18,0)

select @acc_id_kd = -1001
select @c_bal_sum=sum(isnull(p.saldo_deb,0))
 from tp_pif_srez_balance p
 where p.srez_id=@id
   and .ITEMS_EXISTS_BY_TYPE(p.id,'NPF_SREZ_PARTNERS')
   and p.acc_id in .NPF_ACCS4RPR


-- .TYPE_INIT('NPF_SREZ_PORTFOLIO')

.TYPE_ASSIGN(stock_sum,@c_bal_sum)
.TYPE_ASSIGN(stock_rate,0)
.TYPE_ASSIGN(srez_id,@id)
.TYPE_ASSIGN(stock_id,null)
.TYPE_ASSIGN(stock_qty,null)
.TYPE_ASSIGN(bal_sum,@c_bal_sum)
.TYPE_ASSIGN(acc_id,@acc_id_kd)
.TYPE_ASSIGN(ctrl_comp_id,@fond_id)
.TYPE_ASSIGN(is_rate,1)

.TYPE_INSERT('NPF_SREZ_PORTFOLIO','CRT')

select @acc_id_kd = -1002
select @c_bal_sum=(-1)*sum(isnull(p.saldo_cred,0))
 from tp_pif_srez_balance p
 where p.srez_id=@id
   and .ITEMS_EXISTS_BY_TYPE(p.id,'NPF_SREZ_PARTNERS')
   and p.acc_id in .NPF_ACCS4RPR

.TYPE_ASSIGN(stock_sum,@c_bal_sum)
.TYPE_ASSIGN(stock_rate,0)
.TYPE_ASSIGN(srez_id,@id)
.TYPE_ASSIGN(stock_id,null)
.TYPE_ASSIGN(stock_qty,null)
.TYPE_ASSIGN(bal_sum,@c_bal_sum)
.TYPE_ASSIGN(acc_id,@acc_id_kd)
.TYPE_ASSIGN(ctrl_comp_id,@fond_id)
.TYPE_ASSIGN(is_rate,1)

.TYPE_INSERT('NPF_SREZ_PORTFOLIO','CRT')
*/
--Задолженности  Kav added 12/05/03 end



.TRANSACTION_RESTORE

.EXEC_ACTION(@rep_sid,'REPORT_FOR_STRUCTURE_ACT_NPF','STRUCTURE_ACT_NPF_RECALC')

select @is_no=null
.TYPE_INIT('SD_ASSET_ENUM',7)
.TYPE_ASSIGN(depo_doc_type,2,7)
.DEPO_GEN_NO(@is_no,@depo_doc_type7_1)
.TYPE_ASSIGN(foundation_id,@id,7)
.TYPE_ASSIGN(srezdoc_id,@id,7)
.TYPE_ASSIGN(in_date,(.OPERDAY),7)
.TYPE_ASSIGN(owner_id,@fond_id,7)
.TYPE_ASSIGN(in_no,@is_no,7)
--.TYPE_ASSIGN(date_b,(getdate()),7)
.TYPE_ASSIGN(date_b,(dateadd(hh,datepart(hh,getdate()),dateadd(mi,datepart(mi,getdate()),dateadd(ss,datepart(ss,getdate()),(@srez_date))))),7)
.TYPE_ASSIGN(doc_date,@srez_date,7)
.TYPE_INSERT('SD_ASSET_ENUM','CRT',7)


commit tran
  $ENDTEXT(11362492)
 $ENDACTION

 $STATE2ACTION(CRT.ACTION1)
  state=CRT
  action=ACTION1
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.ASSET_EXPORT)
  state=CRT
  action=ASSET_EXPORT
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.D_NPF_PORTFOLIO_REPORT)
  state=CRT
  action=D_NPF_PORTFOLIO_REPORT
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.D_NPF_SREZ_EDIT)
  state=CRT
  action=D_NPF_SREZ_EDIT
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.FORM1)
  state=CRT
  action=FORM1
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.FORM11)
  state=CRT
  action=FORM11
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.NPF_SREZ_LIST)
  state=CRT
  action=NPF_SREZ_LIST
 $ENDSTATE2ACTION

 $STATE2ACTION(CRT.D_PIF_SRES_LIST)
  state=CRT
  action=D_PIF_SRES_LIST
 $ENDSTATE2ACTION

 $STATE2ACTION(NEW.NPF_SREZ_CALCULATE)
  state=NEW
  action=NPF_SREZ_CALCULATE
 $ENDSTATE2ACTION

 $STATE2PROP(CRT.ACTIV_SUM)
  state=CRT
  prop=ACTIV_SUM
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(CRT.REMARK)
  state=CRT
  prop=REMARK
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(CRT.SCHA_SUM)
  state=CRT
  prop=SCHA_SUM
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(CRT.STOCK_PRICE)
  state=CRT
  prop=STOCK_PRICE
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP

 $STATE2PROP(CRT.STOCK_QTY)
  state=CRT
  prop=STOCK_QTY
  enable=1
  mandatory=0
  visible=1
 $ENDSTATE2PROP
$ENDDOC