End Date User Accounts

This can be useful to run after a clone of PROD - you might want to prevent all users accessing the test instance, except a few key users.

DECLARE
   CURSOR usercur   
   IS
    SELECT fu.user_name
      FROM apps.fnd_user fu
     WHERE NVL(fu.end_date, SYSDATE + 1) >= SYSDATE
       AND fu.user_name NOT IN -- EXCLUDE SYSTEM ACCOUNTS ** THIS IS NOT A COMPREHENSIVE LIST **
        (
            'AME_INVALID_APPROVER',
            'APPSMGR',
            'ASGADM',
            'ASGUEST',
            'AUTOINSTALL',
            'GUEST',
            'IBE_ADMIN',
            'IBE_GUEST',
            'IBEGUEST',
            'IEXADMIN',
            'IRC_EMP_GUEST',
            'IRC_EXT_GUEST',
            'MOBADM',
            'MOBDEV',
            'MOBILEADM',
            'OP_CUST_CARE_ADMIN',
            'OP_SYSADMIN',
            'PORTAL_PROD',
            'PRODAPOID',
            'PRODOIDAP',
            'SYSADMIN',
            'WIZARD'
        ) -- you can use this list to exclude staff in central support team if required:
       AND fu.user_name NOT IN('TESTUSER1',
                               'TESTUSER2',
                               'TESTUSER3');

BEGIN
   FOR myuser IN usercur
   LOOP
      fnd_user_pkg.updateuser(
         x_user_name                 => myuser.user_name,
         x_owner                     => 'CUST',
         x_end_date                  => TRUNC(SYSDATE-1)
      );
   END LOOP;
END;
/
COMMIT;
EXIT

Remove End Date from User Accounts

 DECLARE
   CURSOR usercur
   IS
      SELECT fu.user_name
        FROM apps.fnd_user fu
       WHERE fu.last_update_date > TO_DATE('17-FEB-2011 21:49:32', 'DD-MON-YYYY HH24:MI:SS')
         AND fu.end_date IS NOT NULL;
BEGIN
   FOR myuser IN usercur
   LOOP
      fnd_user_pkg.updateuser(
         x_user_name      => myuser.user_name
       , x_owner          => 'CUST'
       , x_end_date       => TO_DATE('2', 'J')             -- removes end-date
      );
   END LOOP;
END;
/
COMMIT;
EXIT