Create new Application user without login to Oracle Application.

Hi All.

Here the simple SQL script that will help you to create a new Application user without login to Application or if you don't know the SYSADMIN password. This script will create user with System Administrator responsibility.

1. Run as apps:

create or replace procedure Attach_Responsibility(p_resp_key    varchar2, p_user_id     number) is
  l_respons_id   number;
  l_resp_appl    number;                              
begin
 
   begin
      select t.responsibility_id
      into   l_respons_id
      from fnd_responsibility_vl t
      where t.responsibility_key=p_resp_key;
   
      select t.application_id
      into   l_resp_appl
      from fnd_responsibility_vl t
      where t.responsibility_key=p_resp_key;
   exception
      when no_data_found then
         dbms_output.put_line('Cannot get responsibility_id or application_id for SYSTEM_ADMINISTRATOR');
      when others then
         dbms_output.put_line(sqlerrm);
   end;

 fnd_user_resp_groups_api.insert_assignment(user_id                       => p_user_id
                                           ,responsibility_id             => l_respons_id  --System Administrator
                                           ,responsibility_application_id => l_resp_appl
                                           ,start_date                    => SYSDATE - 1000
                                           ,end_date                      => SYSDATE+1000
                                           ,description                   => 'Description');
     
  commit;
 
end  Attach_Responsibility;

2. Run as apps:

declare
   l_user_exists  number;
   l_user_name    fnd_user.user_name%type := '<YOUR USERNAME>';
   l              varchar2(1000);
   l_user_id      number;
   l_respons_id   number;
   l_resp_appl    number;
 
 
begin
 
   select count(1)
   into   l_user_exists
   from   fnd_user
   where  user_name = l_user_name;
 
   dbms_output.put_line('User exist: '||l_user_exists);
 
   if l_user_exists = 0 then
      fnd_user_pkg.CreateUser(x_user_name => l_user_name,
                              x_owner     => 'CUST',
                              x_unencrypted_password => '<password>');
     
       commit;
     
       select user_id
       into   l_user_id
       from   fnd_user
       where  user_name=l_user_name;
     
       dbms_output.put_line('User ID ['||l_user_name||']'||l_user_id);
     
   else
     
      select user_id
      into   l_user_id
      from   fnd_user
      where  user_name=l_user_name;
     
      fnd_user_pkg.UpdateUser(x_user_name => l_user_name,
                              x_owner     => 'CUST',
                              x_unencrypted_password => '<password>');
   end if;
   commit;
 
   Attach_Responsibility('SYSTEM_ADMINISTRATOR',
                         l_user_id);
 
end;

Good luck...