APEX

So actually I've already created SSO, but the problem is that when logging in, you have to first check T_USER whether the account exists or not, so the problem is that on the Apex login page, I use the function APEX_AUTHENTICATION.LOGIN and it logs in successfully, but the problem is that it doesn't match the role. respectively, even though I have created Authorization Schemes, it still doesn't check the function

create or replace FUNCTION AUTHENTICATE_USER
(p_username in varchar2,
p_password in varchar2)
return boolean
is
-- l_user_name T_USER.USERNAME%type := upper(p_username);
l_user_name T_USER.EMAIL%type := lower(p_username);
l_password T_USER.PASSWORD%type;
l_hashed_password varchar2(1000);
l_count number;
begin
-- Mengembalikan dari fungsi AUTHENTICATE_USER
-- 0 Otentikasi normal, berhasil
-- 1 Nama Pengguna Tidak Dikenal
-- 2 Akun Terkunci
-- 3 Akun Kadaluarsa
-- 4 Password Salah
-- 5 Penggunaan Password Pertama
-- 6 Batas Maksimum Upaya Login Terlampaui
-- 7 Kesalahan Internal Tidak Dikenal
--
-- Pertama, periksa apakah pengguna ada
select count(*)
into l_count
from T_USER
-- where USERNAME = l_user_name;
where EMAIL = l_user_name;

if l\_count > 0 then  
     -- Hash password yang diberikan  
     l\_hashed\_password := hash\_password(l\_user\_name, p\_password);  

     -- Dapatkan password yang tersimpan  
     select PASSWORD   
       into l\_password   
       from T\_USER   
   --    where USERNAME = l\_user\_name;  
   where EMAIL = l\_user\_name;  

     -- Bandingkan keduanya, dan jika cocok, kembalikan TRUE  
     if l\_hashed\_password = l\_password then  
         -- Hasil baik.   
         return true;  
     else  
         -- Password tidak cocok  
         return false;  
     end if;  

else
-- Nama pengguna tidak ada
return false;
end if;
-- Jika kita sampai di sini, ada yang aneh terjadi.
return false;
exception
when others then
-- Kami tidak tahu apa yang terjadi jadi log kesalahan internal yang tidak dikenal
return false;
end authenticate_user;
/

pl/sql login page

APEX_AUTHENTICATION.LOGIN(
p_username => :P9999_USERNAME,
p_password => :P9999_PASSWORD);

DECLARE
l_id_unit NUMBER;
l_nama_unitkerja VARCHAR2(255);
l_nama VARCHAR2(255);
l_id_role NUMBER;
l_nama_role VARCHAR2(255);
l_email VARCHAR2(255);
l_username VARCHAR2(255);
BEGIN
SELECT ID_UNIT, NAMA_UNITKERJA,NAMA,ID_ROLE, NAMA_ROLE, EMAIL, NAMA
INTO l_id_unit, l_nama_unitkerja,l_nama,l_id_role, l_nama_role, l_email, l_username
FROM T_USER
WHERE USERNAME = :P9999_USERNAME
AND PASSWORD = HASH_PASSWORD(UPPER(:P9999_USERNAME),:P9999_PASSWORD);

-- Simpan ID_UNIT dalam session state
APEX_UTIL.SET_SESSION_STATE('P_ID', l_id_unit);

-- Simpan NAMA_UNITKERJA dalam session state
APEX_UTIL.SET_SESSION_STATE('P_UNITKERJA', l_nama_unitkerja);

-- Simpan NAMA_UNITKERJA dalam session state
APEX_UTIL.SET_SESSION_STATE('P_NAMA', l_nama);

-- Simpan ID_ROLE dan NAMA_ROLE dalam session state
apex_util.set_session_state('P_ID_ROLE', l_id_role);
apex_util.set_session_state('P_NAMA_ROLE', l_nama_role);
apex_util.set_session_state('P_EMAIL', l_email);
apex_util.set_session_state('P_NamaUser', l_username);
END;