Retrieve and Reset the FND USER Application Login Password

A. RETRIEVE THE FND USER PASSWORD

To Retrieve the active user Application password from back-end, first compile the below package in the corresponding instance.

CREATE OR REPLACE PACKAGE APPS.XX_get_pwd
AS

FUNCTION decrypt ( key IN VARCHAR2,
                               value IN VARCHAR2 ) Return Varchar2;
END XX_get_pwd;
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_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 XX_get_pwd;
/

Once the package XX_get_pwd is complied then execute the below piece of snippet to retrieve the encrypted password for a particular user.

SELECT usr.user_name,
       XX_get_pwd.decrypt
          ((SELECT (SELECT XX_get_pwd.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 UPPER(USR.USER_NAME) = UPPER('A_USER');


B. RESET THE FND USER PASSWORD

To reset the password for a particular Application user then use the below anonymous block to achieve it.

DECLARE

      lv_ret_val           BOOLEAN;
      lv_user_name     VARCHAR2(50) := 'A_USER';
      lv_new_pwd       VARCHAR2(20) := 'welcome2oracle';

BEGIN
      lv_ret_val := fnd_user_pkg.changepassword(username => lv_user_name,
                                                                        newpassword => lv_new_pwd);
                           
     IF l_ret_val THEN
           DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  lv_new_pwd);
           COMMIT;
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed ');
     End IF;

END;

Note: If you're not sure about the user name get the exact name from FND_USER table

select user_name from fnd_user where description like '%Senthil%Nathan%';