Decrypt FND_USER password

First create a decruption function that will decrypt the password.
create or replace function xx_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';

Create a view used the decryption function: create or replace view xx_user_pass as
SELECT user_name,
      (SELECT xx_decrypt(UPPER(
                              (SELECT (SELECT xx_decrypt(UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)),usertable.encrypted_foundation_password) FROM dual) AS apps_password
                              FROM fnd_user usertable
                              WHERE usertable.user_name LIKE
                              upper((SELECT substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/') - 1) FROM dual)))
                               )
                               ,usertable.encrypted_user_password)
      FROM dual) AS PASS_WORD
FROM fnd_user usertable
WHERE usertable.end_date is null;

Reference:

• Login With Discoverer 11g With E-Business Suite (E-BS) User Fails With ' Unable To Connect To Oracle Applications Database (afscpgcs)' (Doc ID 2040879.1)