CREATE OR REPLACE FUNCTION XXFND_USER_PASS_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';
After creating the above function, we can use the below query to get the current password.
SELECT USR.USER_NAME,
XXFND_USER_PASS_DECRYPT((SELECT (SELECT XXFND_USER_PASS_DECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, USERTABLE.ENCRYPTED_FOUNDATION_PASSWORD)
FROM DUAL) AS APPS_PASSWORD
FROM FND_USER USERTABLE
WHERE USERTABLE.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
FROM FND_USER USR
WHERE USR.USER_NAME = 'USERNAME';
