Getting Passwords for Users in R12

--Stetting a New Password from Back end

----------------------------------------

SET SERVEROUTPUT ON;

DECLARE

    v_user_name      VARCHAR2 (30) := UPPER ('USERNAME');

    v_new_password   VARCHAR2 (30) := 'oracle123';

    v_status         BOOLEAN;

BEGIN

    v_status :=

        fnd_user_pkg.ChangePassword (username      => v_user_name,

                                     newpassword   => v_new_password);

    IF v_status = TRUE

    THEN

        DBMS_OUTPUT.put_line (

            'The password reset successfully for the User:' || v_user_name);

        COMMIT;

    ELSE

        DBMS_OUTPUT.put_line (

               'Unable to reset password due to'

            || SQLCODE

            || ' '

            || SUBSTR (SQLERRM, 1, 100));

        ROLLBACK;

    END IF;

END;

/

--PACKAGE Specification

-------------------------

CREATE OR REPLACE PACKAGE XXX_GET_PWD

AS

    FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

        RETURN VARCHAR2;

END XXX_GET_PWD;

/

--PACKAGE BODY:

----------------

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

/

--QUERY to find  the Password

----------------------------

SELECT usr.user_name,

       get_pwd.decrypt (

           (SELECT (SELECT 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 usr.user_name = '&USER_NAME';