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

declare @uk_name_npf varchar(255),--наименование УК для ПИФ/НПФ
@uk_name_npf2 varchar(255),@uk_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
--имена УК для НПФ
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

 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,
 upper(.ITEM_TYPE_CODE(@fund_id)) as fund_type_code,
 @uk_name_npf as uk_name,
 @uk_name_npf as uk_name_excel,
 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,
 p1.partner_name as fund_name_excel,
 td.in_no as in_no,
 td.in_date as rep_date_d,
 t_sd_violation.commit_date as commit_date_d,
 t_sd_violation.viol_name as viol_name,
 t_sd_violation.commentary as commentary,
 t_sd_violation.days_qty as days_qty
--.VIEW_CALC

from
 t_sd_violation,
 t_partners p1,
 td_depo_docs td,
td_pay_fond f

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 commit_date_d, fund_name

.END