EBS API：EBS用户导入并分配职责

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            => '[email protected]');
       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    --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;

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;