Oracle EBS用户导入API;分配职责EBS API;用户有关的API

自己的一个笔记
--创建用户并关联员工
declare
  v_employee_name varchar2(50) := 'SQLU.CN';
  v_employee_count number;
  p_person_id     number;
begin
  --检查员工是否存在
  select count(1)
    into v_employee_count
    from apps.per_all_people_f
   where last_name = v_employee_name;

  if v_employee_check = 0 then
    dbms_output.put_line('系统已经存在该职员');
  elsif v_employee_check > 1 then
    dbms_output.put_line('系统中已经存在多个职员 姓名为:' ||
                         v_employee_name);
  elsif v_employee_check = 1 then
    select person_id
      into p_person_id
      from  apps.per_all_people_f
     where last_name = ln_employee_name;
  
    --创建用户
     apps.fnd_user_pkg.createuser(x_user_name              => v_employee_name,
                            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          => 'SQLU@qq.com');
    COMMIT;
  end if;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

--2. 更改用户信息
--更改用户密码
BEGIN
   apps.fnd_user_pkg.updateuser(x_user_name            => 'SQLU.CN',
                          x_owner                => null,
                          x_unencrypted_password => '654321');
  COMMIT;
END;

--3. 失效用户
--更改终止日期为当前日期
BEGIN
   apps.fnd_user_pkg.disableuser(username =>'SQLU.CN');
  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  apps.fnd_responsibility_vl v,  apps.fnd_application a
   WHERE v.application_id = a.application_id
     AND v.responsibility_name = 'INV_USER';

   apps.fnd_user_pkg.addresp(username       => 'SQLU.CN',
                       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  apps.fnd_responsibility_vl v,  apps.fnd_application a
   WHERE v.application_id = a.application_id
     AND v.responsibility_name = 'INV_USER';

   apps.fnd_user_pkg.delresp(username       => 'SQLU.CN',
                       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;

APEX专家普通

打赏 收藏 海报 链接