Oraone

When cloning an EBS-environment, from production to test for example, it can be necessary to disable the end-users to prevent them from logging in. You can do this as SYSADMIN using the Apps-interface, but for more then 10 users that can take quite some time.

An alternative is to update the fnd_user table directly, something like this:

update fnd_user set end_date = sysdate
where (end_date is null or end_date > sysdate)and user_id > 1100;

But if you later have to enable some of the users you can run into troubles with workflow because of syncing-issues with the underlying tables. A much better way is to end-date the users with an official API. The following script can be used:

DECLARE
  g_user_id number;
  g_resp_id number;
  g_resp_appl_id number;
 
  CURSOR cur1 IS
    SELECT user_name FROM fnd_user
    WHERE (end_date IS NULL OR end_date > SYSDATE)
    AND user_id > 1100
  ;
BEGIN
   select fu.user_id,
          fr.responsibility_id,
          fr.application_id
   into   g_user_id,
          g_resp_id,
          g_resp_appl_id
   from fnd_user fu
   ,    fnd_responsibility_tl fr
   where fu.user_name = 'SYSADMIN'
   and   fr.responsibility_name = 'System Administrator';
 
   fnd_global.apps_initialize (
      user_id      => g_user_id
     ,resp_id      => g_resp_id
     ,resp_appl_id => g_resp_appl_id
   );
 
  FOR all_user IN cur1 LOOP
    BEGIN
      fnd_user_pkg.DisableUser(all_user.user_name);
    EXCEPTION
      WHEN OTHERS THEN
        Dbms_Output.Put_Line('Fout opgetreden bij het disabelen van: '||all_user.user_name);
        Dbms_Output.Put_Line(SubStr(SQLERRM, 1, 254));
    END;
  END LOOP;
END;
/

First a cursor is used to select the end-users that will be disabled, then, with apps.initialize the correct apps-context is set, so that the column LAST_UPDATED_BY will show SYSADMIN, and finally the API is called to disable the users.

(Thanks Patrick for improving the code!)