PL SQL: need to replace stored function with JOIN. How to do it optimally?

Question

Oracle

create or replace function          getlegalgroup_ce(p_ceid kollecto.collection_entities.ceid%type,
                                                    p_date kollecto.legal_executory.creation_date%type) return varchar2 as v_legalgroup kollecto.groups.name%type;
v_first_group kollecto.groups.name%type;
v_idusr kollecto.users.idusr%type;
begin

select distinct first_value(coj.id_new_owner)over(partition by coj.ceid order by coj.begin_date desc) ----into v_idusr
 from tver.coj
where coj.ceid=p_ceid
  and trunc(p_date) between trunc(coj.begin_date) and trunc(coj.end_date);

select (select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) 
         from tver.ce_usrgroup_jnl cuj,
              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=v_idusr
          and trunc(p_date)<=trunc(cuj.begin_date)) into v_first_group 
 from dual;
  
select nvl((select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) 
             from tver.ce_usrgroup_jnl cuj,
                  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=v_idusr
              and trunc(p_date) between trunc(cuj.begin_date) and trunc(cuj.end_date)),v_first_group) into v_legalgroup 
 from dual;
  
return v_legalgroup;

end getlegalgroup_ce;

This function takes 2 values: p_ceid and p_date , returns v_legalgroup.
How to rewrite it to JOIN so that there are no nested queries, because in this case, there is a problem of visibility of v_idusr in another subquery when calculating v_legalgroup, where the link goes to v_idusr.
I would like to find a concise solution that will be used instead of this function in the future.

Answer 1

Good afternoon, Dmitry Mityaev .
If I understand the problem correctly, then you can use cte and join.

with v_idusr_tbl as 
(
select distinct first_value(coj.id_new_owner)over(partition by coj.ceid order by coj.begin_date desc) id_new_owner
 from tver.coj
where coj.ceid=p_ceid
  and trunc(p_date) between trunc(coj.begin_date) and trunc(coj.end_date)
)
, tbl_second_group as (
select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) name
, cuj.idusr
             from tver.ce_usrgroup_jnl cuj,
                  kollecto.groups g,
                  v_idusr_tbl q
            where cuj.idgrp=g.idgrp
              and g.unique_flag=1
              and (g.name like 'Legal%' or g.name like 'Field%')
              and cuj.idusr=q.id_new_owner
              and trunc(p_date) between trunc(cuj.begin_date) and trunc(cuj.end_date)
)
, tbl_first_group as (
select distinct first_value(g.name)over(partition by cuj.idusr order by cuj.begin_date desc) name
         from tver.ce_usrgroup_jnl cuj,
              kollecto.groups g,
              v_idusr_tbl q
        where cuj.idgrp=g.idgrp
          and g.unique_flag=1
          and (g.name like 'Legal%' or g.name like 'Field%')
          and cuj.idusr=q.id_new_owner
          and trunc(p_date)<=trunc(cuj.begin_date)
          and 0 = (select count(1) from tbl_second_group) /*Если в tbl_second_group ничего нет, то выбираем. 
          Скорее всего, это условие будет выполнено в конце при выполнении запроса, я не придумал способ засунуть в секцию from или exists, чтобы он вообще не выполнялся в случае непустого tbl_second_group*/
)

, tbl_legal_group as (
select name from tbl_second_group 
union all
select name from tbl_first_group 
)
select name into v_legalgroup from tbl_legal_group /*Вставляем в переменнную*/