Query to get Login Passwords for Application in Oracle Applications

Download Query

-- Package Specification

CREATE OR REPLACE PACKAGE GET_PASSWORD_PKG AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;
END GET_PASSWORD_PKG;

-- Package Body 

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

-- Call the package function as shown below
SELECT fu.user_name
            , (SELECT GET_PASSWORD_PKG.decrypt (UPPER ((SELECT (SELECT get_pswd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL))
            , fu.encrypted_foundation_password)
FROM DUAL) AS appln_password
FROM fnd_user fu
WHERE fu.user_name LIKE
        UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
        ,1
        , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
        - 1
        )
FROM DUAL))))
,fu.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user fu
WHERE fu.user_name LIKE UPPER ('username') --  username is application login such as 'OPERATIONS'

Download Query