Bulk and Individual Password Reset For Oracle E-Business ( EBS ) Users

Compatible Versions

Requirements

Solution

DECLARE
   l_ret_val     BOOLEAN;
   l_user_name   VARCHAR2 (50) := '&USER_NAME';
   l_new_pwd     VARCHAR2 (20) := '&PASSWORD';
BEGIN
   l_ret_val :=
      fnd_user_pkg.changepassword (username      => l_user_name,
                                   newpassword   => l_new_pwd);
   IF l_ret_val
   THEN
      DBMS_OUTPUT.PUT_LINE ('Password Changed Successfully');
      COMMIT;
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Password NOT Changed Successfully');
   END IF;
END;

DECLARE
    /*--Active User Lists */
    CURSOR C_EBSUSERS IS
    SELECT
        USER_ID,
        USER_NAME,
        START_DATE,
        END_DATE,
        EMPLOYEE_ID
    FROM
        APPLSYS.FND_USER
    WHERE 1=1 
        AND NVL(END_DATE, SYSDATE) > SYSDATE-1 /*Inactive users will not be listed.*/
        AND EMPLOYEE_ID IS NOT NULL /* --Users which doesn’t have employee id will not be listed*/
        --AND USER_NAME = 'EBSUSER' ;  /*--To test you can use few users but comment this condition to reset all user*/  
    L_USER_COUNT NUMBER; 
    L_RETURN_VALUE     BOOLEAN;
    L_NEW_PASSWORD VARCHAR2(4000) := 'Tomato98123';   /*--Enter any password you want for all users*/
BEGIN
    L_USER_COUNT:= 0;
    FOR CREC_EBSUSERS IN C_EBSUSERS
    LOOP
        L_USER_COUNT := L_USER_COUNT + 1;
        BEGIN
            L_RETURN_VALUE :=
            FND_USER_PKG.CHANGEPASSWORD /*--Without asking old password, It will reset all user’s pass */
            (
                USERNAME      => CREC_EBSUSERS.USER_NAME,
                NEWPASSWORD   => L_NEW_PASSWORD
            );
            IF L_RETURN_VALUE
            THEN
                 DBMS_OUTPUT.PUT_LINE(L_USER_COUNT || ' Password Changed Successfully for ' || CREC_EBSUSERS.USER_NAME );       
                COMMIT;
            ELSE
                DBMS_OUTPUT.PUT_LINE(L_USER_COUNT || ' Password NOT Changed Successfully for ' || CREC_EBSUSERS.USER_NAME );   
            END IF;
        EXCEPTION WHEN OTHERS 
        THEN
            DBMS_OUTPUT.PUT_LINE(L_USER_COUNT || ' - User_Name: ' || CREC_EBSUSERS.USER_NAME || ' - Status: API_ERROR - ERROR_MESSAGE: ' || SQLERRM);  
        END;
    END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Plsql_Error: ' || SQLERRM);    
END;