Скрипт создания пользователя OeBS

Разработчику OeBS очень полезно для создания пользователя системы иметь скрипт для добваления пользователя на новых базах. Нижеприведенный скрипт создает пользователя системы, создает работника в модуле учет персонала и сопоставляет его с пользователем, добавляет базовые полномочия для разработки и администрирования.

-- Создание пользователя
declare
l_user     number;
l_resp     number;
l_appl     number;

l_responsibility_key     fnd_responsibility.responsibility_key%TYPE   := 'SYSTEM_ADMINISTRATOR';
l_application_short_name fnd_application.application_short_name%TYPE  := 'SYSADMIN';

l_responsibility_name    varchar2(200) := 'System Administrator';
l_security_group_id      number := 0;
l_user_name              varchar2(200) := 'ATYSHENKO';
l_unencrypted_password   varchar2(200) := 'password';
l_owner                  varchar2(200) := 'CUST';


l_user_id                         number;  
l_person_id                       number;
l_assignment_id                   number;
l_per_object_version_number       number;
l_asg_object_version_number       number;
l_per_effective_start_date        date;
l_per_effective_end_date          date;
l_employee_number                 varchar2(200);
l_full_name                       varchar2(2000);
l_per_comment_id                  number;
l_assignment_sequence             number;
l_assignment_number               varchar2(200);
l_name_combination_warning        boolean;
l_assign_payroll_warning          boolean;
l_orig_hire_warning               boolean; 
begin

select u.user_id
into l_user
from fnd_user u
where u.user_name = 'AUTOINSTALL';
--
select r.responsibility_id, r.application_id
into l_resp, l_appl
from fnd_responsibility_tl r
where r.language = 'US'
and r.responsibility_name = l_responsibility_name;

dbms_output.put_line('инициализация...');    
fnd_global.apps_initialize(l_user, l_resp, l_appl);
dbms_output.put_line('приложение инициализировано');  
--  hr_utility.switch_logging_on;
-- создать работника
hr_employee_api.create_employee(p_hire_date => to_date('01.02.2011','dd.mm.yyyy')
,p_business_group_id => l_security_group_id
,p_last_name         => 'Тышенко'
,p_first_name        => 'Александр'
,p_middle_names      => 'Анатольевич'
,p_sex               => 'M'
,p_date_of_birth     => to_date('21.06.1970','dd.mm.yyyy')
,p_employee_number           => l_employee_number      
,p_person_id                 => l_person_id
,p_assignment_id             => l_assignment_id
,p_per_object_version_number => l_per_object_version_number   
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date  => l_per_effective_start_date
,p_per_effective_end_date    => l_per_effective_end_date
,p_full_name                 => l_full_name
,p_per_comment_id            => l_per_comment_id
,p_assignment_sequence       => l_assignment_sequence
,p_assignment_number         => l_assignment_number
,p_name_combination_warning  => l_name_combination_warning
,p_assign_payroll_warning    => l_assign_payroll_warning
,p_orig_hire_warning         => l_orig_hire_warning
);
dbms_output.put_line('создан пользователь person_id='||l_person_id);

if not fnd_user_pkg.userExists(l_user_name) then
l_user_id := fnd_user_pkg.CreateUserId(
x_user_name            => l_user_name
, x_owner                => l_owner
, x_unencrypted_password => l_unencrypted_password
,x_employee_id           => l_person_id
);

dbms_output.put_line('создан пользователь user_name='||l_user_name);
else
select u.user_id
into l_user_id
from fnd_user u
where u.user_name = l_user_name;   
end if;   

for i  in (select r.application_id,
r.responsibility_id,
r.responsibility_name
from fnd_responsibility_tl r
where r.language = 'RU'
and r.RESPONSIBILITY_NAME in ('Системный администратор' ,'Интегрированный шлюз SOA' ,'Глобальный суперпользователь-руководитель СУПЕР' ,'Администратор функций' ,'Разработчик приложений')
and not exists(select *
from fnd_user_resp_groups urg
where urg.RESPONSIBILITY_ID = r.responsibility_id
and urg.RESPONSIBILITY_APPLICATION_ID = r.application_id
and urg.user_id = l_user_id)) loop   

fnd_user_resp_groups_api.insert_assignment(
user_id                         => l_user_id
, responsibility_id             => i.responsibility_id
, responsibility_application_id => i.application_id
, security_group_id             => l_security_group_id
, start_date                    => trunc(sysdate) 
, end_date                      => NULL
, description                   => i.responsibility_name
);

fnd_wf_engine.default_event_raise(
'oracle.apps.fnd.security.user.assignment.change'
, l_user_id || ':' || i.responsibility_id
);

fnd_wf_engine.propagate_user_role(
'FND_USR'
, l_user_id
, 'FND_RESP' || i.application_id
, i.responsibility_id
, trunc(sysdate)
, NULL
);

-- Added for Function Security Cache Invalidation Project
fnd_function_security_cache.insert_user_resp(
l_user_id
, i.responsibility_id
, i.application_id
);      
dbms_output.put_line('добавлена привилегия responsibility_id='||i.responsibility_id||' responsibility_name='||i.responsibility_name);  
end loop;    
commit; end;