Разработчику 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; 