Дополнен 4 года назад
Дополнен 4 года назад
create or replace function getlegalgroup(p_idusrkollecto.users.idusr%type,
p_date kollecto.legal_executory.creation_date%type) return varchar2 as v_legalgroupkollecto.groups.name%type;
v_first_groupkollecto.groups.name%type;
begin
select (select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc)
fr om tver.ce_usrgroup_jnlcuj,
kollecto.groups g
where cuj.idgrp=g.idgrp
and g.unique_flag=1
--and (g.name like 'Legal_EKAT%' or g.name like 'Legal_KALUGA%' or g.name like 'Legal_VOLGA%' or g.name like 'Legal_TVER%')
and (g.name like 'Legal%' or g.name like 'Field%')
and cuj.idusr=p_idusr
and p_date<cuj.begin_date) into v_first_group
from dual;
select nvl((select g.name
fr om tver.ce_usrgroup_jnlcuj,
kollecto.groups g
wh ere cuj.idgrp=g.idgrp
and g.unique_flag=1
--and (g.name like 'Legal_EKAT%' or g.name like 'Legal_KALUGA%' or g.name like 'Legal_VOLGA%' or g.name like 'Legal_TVER%')
and (g.name like 'Legal%' or g.name like 'Field%')
and cuj.idusr=p_idusr
and p_date between cuj.begin_date and cuj.end_date),v_first_group) into v_legalgroup
from dual;
return v_legalgroup;
end getlegalgroup;