(85条消息) EBS API：EBS用户导入并分配职责_Acenol的博客-CSDN博客
创建用户并关联员工
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
2. 更改用户信息
--更改用户密码
BEGIN
fnd_user_pkg.updateuser(x_user_name => 'TEST001'
,x_owner => null
,x_unencrypted_password => '654321');
COMMIT;
END;
1
2
3
4
5
6
7
3. 失效用户
--更改终止日期为当前日期
BEGIN
fnd_user_pkg.disableuser(username => 'TEST001');
COMMIT;
END;
1
2
3
4
5
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 --application_short_name
,resp_key => l_resp_key
,security_group => 'STANDARD' --不能赋值为'標準'，select * from fnd_security_groups
,description => null
,start_date => SYSDATE
,end_date => null);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;
————————————————
版权声明：本文为CSDN博主「Acenol」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_42683386/article/details/107998675