EBS: API创建用户

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; 
/