1. 创建用户并关联员工
declare
ln_employee_name varchar2(50) := 'TEST001';
v_employee_check number;
ln_person_id number;
begin
select count(1)
into v_employee_check
from per_all_people_f
where last_name = ln_employee_name;
if v_employee_check = 0 then
dbms_output.put_line('The employee does not exists in system' );
elsif v_employee_check > 1 then
dbms_output.put_line('there are more than 1 employee named ' || ln_employee_name);
elsif v_employee_check = 1 then
select person_id
into ln_person_id
from per_all_people_f
where last_name = ln_employee_name;
fnd_user_pkg.createuser (
x_user_name => 'TEST001',
x_owner => null,
x_unencrypted_password => '123456',
x_start_date => sysdate,
x_end_date => null,
x_password_date => sysdate,
x_password_lifespan_days => 90,
x_employee_id => ln_person_id,
x_email_address => 'TEST001@163.com');
COMMIT;
end if;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
2. 更改用户信息
BEGIN
fnd_user_pkg.updateuser(x_user_name => 'TEST001'
,x_owner => null
,x_unencrypted_password => '654321');
COMMIT;
END;
3. 失效用户
BEGIN
fnd_user_pkg.disableuser(username => 'TEST001');
COMMIT;
END;
4. 分配职责给用户
DECLARE
l_resp_app VARCHAR2(50);
l_resp_key VARCHAR2(30);
BEGIN
SELECT a.application_short_name, v.responsibility_key
INTO l_resp_app, l_resp_key
FROM fnd_responsibility_vl v, fnd_application a
WHERE v.application_id = a.application_id
AND v.responsibility_name = 'INV_USER';
fnd_user_pkg.addresp(username => 'TEST001'
,resp_app => l_resp_app
,resp_key => l_resp_key
,security_group => 'STANDARD'
,description => null
,start_date => SYSDATE
,end_date => null);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
5. 失效已分配职责
DECLARE
l_resp_app VARCHAR2(50);
l_resp_key VARCHAR2(30);
BEGIN
SELECT a.application_short_name, v.responsibility_key
INTO l_resp_app, l_resp_key
FROM fnd_responsibility_vl v, fnd_application a
WHERE v.application_id = a.application_id
AND v.responsibility_name = 'INV_USER';
fnd_user_pkg.delresp(username => 'TEST001'
,resp_app => l_resp_app
,resp_key => l_resp_key
,security_group => 'STANDARD');
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;