EBS账号以员工号作为账号。
DECLARE p_employee_id number;
v_user_name varchar2(100);
v_owner varchar2(50);
v_unencrypted_password varchar2(50) default null;
v_session_number number default null;
v_start_date date default null;
v_end_date date default null;
v_last_logon_date date default null;
v_description varchar2(50) default null;
v_password_date date default null;
v_password_accesses_left number default null;
v_password_lifespan_accesses number default null;
v_password_lifespan_days number default null;
v_employee_id number default null;
v_email_address varchar2(50) default null;
v_fax varchar2(50) default null;
v_customer_id number default null;
v_supplier_id number default null;
i number;
cursor c1 is
select emp.employee_id, emp.employee_num, emp.full_name,
emp.last_name
from apps.hr_employees emp
where emp.employee_num like ('工员号')
and not exists ( select user_name from apps.fnd_user fu
where emp.employee_num = fu.user_name);
begin
i := 0;
for r1 in c1 loop
v_user_name := r1.employee_num ;
v_owner := 'SYSADMIN'; --操作人用户名
v_unencrypted_password :='ORACLE120' ; -- 初始密码
v_session_number :=null ;
v_start_date := SYSDATE ;
v_end_date :=null ;
v_last_logon_date :=null ;
v_description := r1.last_name ;
v_password_date :=null ;
v_password_accesses_left :=NULL ;
v_password_lifespan_accesses:=NULL ;
v_password_lifespan_days :=90 ;
v_employee_id := r1.employee_id ;
v_email_address :='' ;
v_fax :='' ;
v_customer_id :='' ;
v_supplier_id :='' ;
apps.fnd_user_pkg.LoadUser(
x_user_name =>v_user_name, -- in varchar2,
x_owner =>v_owner, -- in varchar2,
x_unencrypted_password =>v_unencrypted_password, -- in varchar2 default null,
x_session_number =>v_session_number, -- in number default null,
x_start_date =>v_start_date, -- in date default null,
x_end_date =>v_end_date, -- in date default null,
x_last_logon_date =>v_last_logon_date, -- in date default null,
x_description =>v_description, -- in varchar2 default null,
x_password_date =>v_password_date, -- in date default null,
x_password_accesses_left =>v_password_accesses_left, -- in number default null,
x_password_lifespan_accesses=>v_password_lifespan_accesses, -- in number default null,
x_password_lifespan_days =>v_password_lifespan_days, -- in number default null,
x_employee_id =>v_employee_id, -- in number default null,
x_email_address =>v_email_address, -- in varchar2 default null,
x_fax =>v_fax, -- in varchar2 default null,
x_customer_id =>v_customer_id, -- in number default null,
x_supplier_id =>v_supplier_id -- in number default null
) ;
i := i +1;
dbms_output.put_line('新增第'|| to_char(i) ||'个用户：'||v_user_name );
end loop;
commit;
dbms_output.put_line('共新增'|| to_char(i) ||'个用户。');
end;
/