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
