How to retrieve APPS password in R12 from database

If you forget APPS password when you can not change it or EBS services are not up/running for making the change, there is a way to retrieve and decrypt the password from its Oracle database.

SQL> show user

USER is "system"

SQL> create FUNCTION apps.decrypt_apps_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
Function created.

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG41BA6CBD7A2CD2DCEABED6.........919A4D

SQL> SELECT apps.decrypt_apps_pwd('GUEST/ORACLE','ZG41BA6CBD7A2CD2DCEABED6.........919A4D') apps_pwd from dual;

APPS_PWD
----------------------

ItISappsPWD

SQL> conn apps/ItIsappsPWD
Connected.

Optional:

SQL> drop function apps.decrypt_apps_pwd;

This function has to be created in APPS schema to avoid error "OAR-29540 oracle/apps/fnd/security/WebSessionManagerProc does not exist", and it does not get EBS front-end users' password.

UPDATE in 2020: 

I tested this method works in R12.2.10