create procedure dbo.%PROC% @sid varchar(30) = null,
@find_date_db varchar(30)=null,
@find_date_de varchar(30)=null,
@fund_id_s varchar(30) = null
as
.BEGIN('N')

.CHECK_USER

declare @find_date_e datetime, @find_date_b datetime, @fund_id numeric

select @find_date_e=convert(datetime,@find_date_de,3),
       @find_date_b=convert(datetime,@find_date_db,3)

if @find_date_e is null select @find_date_e=.OPERDAY
if @find_date_b is null or @find_date_b>@find_date_e select @find_date_b=@find_date_e
if @fund_id_s='' select @fund_id_s=null

select @fund_id=convert(numeric,@fund_id_s)
if @fund_id is null select @fund_id=.ORGANIZATION

create table #result(
 sid varchar(30) null,
 viol_id numeric(18,0) null,
 uk_name varchar(255) null,
 fond_type varchar(50) null,
 type varchar(50) null,
 fund_name varchar(255) null,
 in_no varchar(50) null,
 rep_date datetime null,
 state varchar(255) null,
 rep_no varchar(50) null,
 rep_corr_date datetime null
)


declare @uk_name_npf varchar(255), --наименование УК для ПИФ/НПФ
@uk_name_npf2 varchar(255),
@uk_id numeric
--
--Наименование УК для НПФ
if upper(.ITEM_TYPE_CODE(@fund_id))='NPF_FUND'
begin
declare uk cursor for
select  p.id
 from t_partners p, td_depo_docs dd, tb_contracts d
where  p.id=d.client_id

and d.id=dd.id
and dd.owner_id=@fund_id
  and exists(select 1
         from t_items, t_states, t_types
        where t_items.id=d.id
          and t_states.id=t_items.state_id
          and t_states.class_id=0
          and t_types.id=t_items.type_id
          and upper(t_types.type_code)=upper('FUND_CONTRACTS'))
  and (select t_states.state_code from t_items,t_states
  where t_items.id = d.id
    and t_items.state_id = t_states.id) in ('OK','CRT')
  and d.dog_type_id=3

open uk
fetch uk into @uk_id
select @uk_name_npf2=''
while .CURSOR_STATE=0
begin

select @uk_name_npf=partner_short_name
from t_partners where id=@uk_id
select @uk_name_npf=@uk_name_npf+', '+@uk_name_npf2
select @uk_name_npf2=@uk_name_npf
fetch uk into @uk_id

end

close uk
.DEALLOCATE uk
select @uk_name_npf=substring(@uk_name_npf,1,char_length(@uk_name_npf)-1)--left(@uk_name_npf,len(@uk_name_npf)-1)
end
--Наименование УК для ПИФ
if upper(.ITEM_TYPE_CODE(@fund_id))='PIF_FUND'
select @uk_name_npf=p.partner_name
from t_partners p,td_pay_fond f
where p.id=f.ctrl_comp_id and f.id=@fund_id


insert into #result
 select
 @sid,
 t_sd_violation.id,
 @uk_name_npf as uk_name,
 convert(varchar(50),f.fond_type) as fond_type,
 (case (isnull((select min(v.code) from t_classvalues v, t_classes c, t_item2class i
 where c.id=v.class_id and
       c.code='PIF_TYPES_31' and
       i.item_id=t_sd_violation.fund_id and
       i.value_id=v.id),'0'))
     when '1' then 'др'
     when '2' then 'обл.'
     when '3' then 'акц.'
     when '4' then 'си'
     when '5' then 'нед.'
     when '6' then 'ви'
     when '7' then 'фф'
     when '8' then 'инд.'
     else ''
     end) as type,
 p1.partner_name as fund_name,
 td.in_no as in_no,
 td.in_date as rep_date,
 null,
null,
null

from
 t_sd_violation,
 t_partners p1,
 td_pay_fond f,
 td_depo_docs td

where
 .ITEMS_EXISTS(t_sd_violation.id) and
  td.id=t_sd_violation.rep_detect_id and
  t_sd_violation.fund_id = p1.id and
  t_sd_violation.fund_id=@fund_id and
  (t_sd_violation.find_date between @find_date_b and @find_date_e)   and
   f.id= t_sd_violation.fund_id

order by rep_date, fund_name

update #result
set state='не устранено',
    rep_no=td1.in_no,
    rep_corr_date=td1.in_date
from
 t_sd_violation,
 td_depo_docs td1
where
  t_sd_violation.id=#result.viol_id and
  td1.id=t_sd_violation.rep_uncorr_id and
 .ITEMS_EXISTS_BY_TYPE_STATE(td1.id,'SD_VIOLATION_UNCORR_REP','CRT')
  and not (.ITEMS_EXISTS_BY_TYPE_STATE(t_sd_violation.id,'SD_VIOLATION','CORR'))

update #result
set
state='устранено',
rep_no=td2.in_no,
rep_corr_date=td2.in_date
from
 t_sd_violation,
 td_depo_docs td2

where
  t_sd_violation.id=#result.viol_id and
  td2.id=t_sd_violation.rep_corr_id and
 .ITEMS_EXISTS_BY_TYPE_STATE(t_sd_violation.id,'SD_VIOLATION','CORR')

select
 sid,
 (case when @find_date_e=@find_date_b then 'Дата выявления: '+convert(varchar,@find_date_e,104)
       else 'За период с '+convert(varchar,@find_date_b,104)+' по '+convert(varchar,@find_date_e,104)
  end) as date_str,
 uk_name,
 uk_name as uk_name_excel,
 fond_type,
 type,
 upper(.ITEM_TYPE_CODE(@fund_id)) as fund_type_code,
 fund_name,
 fund_name as fund_name_excel,
 in_no,
 rep_date as rep_date_d,
 state,
 rep_no,
 rep_corr_date as rep_corr_date_d
from #result

drop table #result

.END