Set page item value depending on selection of lov in oracle apex

Question

I have the following issue. I have a page element of type popUp-LoV with the following SQL query:

SELECT u.Lastname || ', ' || u.Firstname AS displayed, u.UUID as ID

from STAFF s

left outer join USERS u on s.UUID=u.UUID

union

SELECT u2.Lastname || ', ' || u2.Firstname AS displayed, m.MAID AS ID

FROM MANAGER m

left outer join USERS u2 on m.UUID=u2.UUID

In this way, I would like to show the user people from two tables to choose from. However, I want that as soon as the user selects a person, the foreign key to the corresponding table is set. The foreign keys to these tables will be kept in separate columns. In other words I created two hidden page items, each has a foreign key constraint to the corresponding table. Depending on the return value of the list of value, I would like then to set the page items (one would be null) in the corresponding page item.

To achieve this, I first created a function that should check whether the returned value of the LoV is in a table as an ID.

create or replace function search_id (p_key_value in number)
return number
is
found_person number;
begin

select m.ID into found_person from MANAGER m where p_key_value=m.ID ;
if sql%rowcount = 1 then
return found_person;
end if ;
exception
when no_data_found then
return null;
end;

---

changed sql query for LoV:

SELECT u.Lastname || ', ' || u.Firstname AS displayed, u.UUID as ID
    from USERS u 
   
     and u.UUID in (select u.UUID
               from STAFF s
               left outer join USERS u on s.UUID=u.UUID)
                OR u.UUID in (select u.UUID
               from MANAGER m
               left outer join USERS u on m.UUID=u.UUID)

changed function:

create or replace function search_id(p_key_value in number)
return number
is
found_person number;
begin

select s.STID
into found_person
from STAFF s
where s.UUID=p_key_value;

if sql%rowcount = 1 then
return found_person;
end if ;
 exception
  when no_data_found then
    select m.MID
into found_person
from MANAGER m
where m.UUID=p_key_value;;
  end;

---

Dynamic action: I then created a dynamic action for the page item with the popup LoV. The dynamic action fires when the page item is changed (so when the user chooses a value from LoV).

The action is: execute pl/sql code with following pl/sql function:

    declare
found_person number;
begin
found_person:= search_id(V('P12_RESPONSIBLE')); 
apex_util.set_session_state('P12_MANAGER_ID',found_person);
apex_util.set_session_state('P12_STAFF_ID',found_person);
exception
when no_data_found then
null;
end;

Items to submit: P12_RESPONSIBLE Items to return: P12_MANAGER_ID,P12_STAFF_ID