Using PL/SQL to create OA user and attach "System Administrator" responsibility

I faced several times when I had a full access to the DB, but could not access Oracle Application, because I had no user.

So I decided to write a function which will create a new user and attach it new responsibility

First of all, you need to compile a function which attaching the responsibility to specific user. Here is the source code

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
                                           --,security_group_id             => 0
                                           ,start_date                    => SYSDATE - 1000
                                           ,end_date                      => SYSDATE+1000
                                           ,description                   => 'Description');
  commit;
end  Attach_Responsibility;

And now the script that will create new user and call the function above to attach him X responsibility

declare
   l_user_exists  number;
   l_user_name    fnd_user.user_name%type := 'MYNEWUSER';
   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 => 'welcome!1');
       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 => 'WELCOME1');
   end if;
   commit;
   Attach_Responsibility('SYSTEM_ADMINISTRATOR',
                         l_user_id);
   Attach_Responsibility('APPLICATION_DEVELOPER',
                         l_user_id);                       
end; 

User's name is MYNEWUSER

l_user_name    fnd_user.user_name%type := 'MYNEWUSER';

check if user already exists

select count(1)
   into   l_user_exists
   from   fnd_user
   where  user_name = l_user_name;

if user exists, change the password to WELCOME!1

fnd_user_pkg.UpdateUser(x_user_name => l_user_name,
                              x_owner     => 'CUST',
                              x_unencrypted_password => 'WELCOME!1');

if it doesn't exists, create with initial password WELCOME!1

fnd_user_pkg.CreateUser(x_user_name => l_user_name,
                              x_owner     => 'CUST',
                              x_unencrypted_password => 'WELCOME!1');

After user created all we need to do is attach related responsibility. In this case we are going to attach "System Administrator" and "Application Developer" responsibilities

Attach_Responsibility('SYSTEM_ADMINISTRATOR',
                         l_user_id);
 Attach_Responsibility('APPLICATION_DEVELOPER',
                         l_user_id);