Oracle Apps: How to get Oracle Apps (FND user) password from backend

You can run the below script from backend to retrieve the FND User password from backend.

Create the below packge:

 create or replace package xxaj_get_pwd  
 as  
   function decrypt (key in varchar2, value in varchar2)  
   return varchar2;  
 end xxaj_get_pwd;  
 /  
 create or replace package body xxaj_get_pwd  
 as  
   function decrypt (key in varchar2, value in varchar2)  
   return varchar2  
   as  
    language java  
    NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';  
 end xxaj_get_pwd;  
 /  

Execute the below script with the username to get the password.

 select fu.user_name  
       ,xxaj_get_pwd.decrypt ((select xxaj_get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd
                                                           ,usertable.encrypted_foundation_password )  
                               from fnd_user usertable  
                               where usertable.user_name = regexp_substr(fnd_web_sec.get_guest_username_pwd,'(.*)/(.*)',1,1,NULL,1))  
                             ,fu.encrypted_user_password ) password  
 from fnd_user fu  
 where upper(fu.user_name) = upper('<USER_NAME>');  
 /  

Related links :
Oracle Apps: Reset FND User password from backend
Oracle Apps: Create User and Add Responsibility from backend


Feel free to point out if anything is missing/wrong in this blog.