EBS批量导入用户及相应的职责

1、创建临时表 CUX_IMPORT_USER_TMP 用来存放登录名、对应的员工名及职责名称 如下图所示

USER_NO 字段对应 hr_employees_all_v（员工表） 表的 EMPLOYEE_NUM 字段

LOGIN_NAME 字段 作为 登录名

RESP_NAME 字段对应 FND_RESPONSIBILITY_TL （指责表）的 RESPONSIBILITY_NAME字段

2、创建 相应的 PKG

create or replace package CUX_IMPORT_USER_PKG is

    -- Author  : DIEGO
    -- Created : 2013-5-15 10:24:44
    -- Purpose : 通过CUX.CUX_IMPORT_USER表导入用户的PKG

    /*=====================================
    ** PROCEDURE:   main
    ** PARAMETER:   
    ** PURPOSE:     入口程序
    **=====================================*/
    PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT NUMBER);

end CUX_IMPORT_USER_PKG;

create or replace package body CUX_IMPORT_USER_PKG is

    PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT NUMBER) IS
        ---------游标开始--------------------------
        CURSOR cur_main IS
            SELECT ABC.USER_NO,
                   ABC.login_name,
                   TL.RESPONSIBILITY_ID,
                   hr.EMPLOYEE_ID
              FROM FND_RESPONSIBILITY_TL   TL,
                   CUX.CUX_IMPORT_USER_TMP ABC,
                   hr_employees_all_v      hr
             WHERE TL.LANGUAGE = 'ZHS'
               AND ABC.RESP_NAME = TL.RESPONSIBILITY_NAME
               and hr.EMPLOYEE_NUM = ABC.USER_NO
               and UPPER(ABC.login_name) not in
                   (select UPPER(FU.user_name) FROM FND_USER FU);
        ------------------------------------------------------------------------------------------
        CURSOR cur_resp(p_resp_id number) IS
            SELECT fa.application_id,
                   fa.application_short_name,
                   fr.responsibility_id,
                   fr.responsibility_name,
                   fr.responsibility_key,
                   fsg.security_group_key
              FROM fnd_application       fa,
                   fnd_responsibility_vl fr,
                   fnd_security_groups   fsg
             WHERE fr.RESPONSIBILITY_ID = p_resp_id --lower(fr.responsibility_name) = lower(p_resp)
               AND fa.application_id = fr.application_id
               AND fr.data_group_id = fsg.security_group_id;
        -----------------游标结束---------------------
        ---------------变量开始-----------
        l_createNumber number := 0;
        l_count_user number:=0;
        l_username VARCHAR2(20);
        --------------变量结束-------------
    begin    
            SELECT COUNT(*)
            INTO l_count_user
              FROM CUX.CUX_IMPORT_USER_TMP ABC
             WHERE UPPER(ABC.login_name) not in
                   (select UPPER(FU.user_name) FROM FND_USER FU);
    dbms_output.put_line('预计导入'|| l_count_user||'个用户');
     dbms_output.put_line('-----------------------------------------------------------------------');
        dbms_output.put_line('开始增加用户');
        FOR r_catg IN cur_main LOOP
        l_username:=r_catg.login_name;
            dbms_output.put_line('开始处理用户名为' || r_catg.login_name || '的用户');
            l_createNumber := 0;
            fnd_user_pkg.CreateUser(x_user_name            => r_catg.login_name,
                                    x_owner                => 'SEED',
                                    x_unencrypted_password => '111111',
                                    x_employee_id          => r_catg.employee_id);
            commit;
            select count(*)
              into l_createNumber
              from fnd_user fu
             where fu.user_name = upper(r_catg.login_name);
        
            if l_createNumber > 0 then
                dbms_output.put_line('用户增加成功');
                dbms_output.put_line('开始为该用户增加职责');
                for r_resp IN cur_resp(r_catg.responsibility_id) loop
                    dbms_output.put_line('职责名为' ||
                                         r_resp.responsibility_name);
                    fnd_user_pkg.AddResp(username       => upper(r_catg.login_name),
                                         resp_app       => r_resp.application_short_name,
                                         resp_key       => r_resp.responsibility_key,
                                         security_group => r_resp.security_group_key,
                                         description    => null,
                                         start_date     => sysdate,
                                         end_date       => null);
                
                end loop;
                COMMIT;
                dbms_output.put_line('职责添加成功');
            end if;
        END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
       dbms_output.put_line('用户名为'||l_username||'在传入时报错，请查看具体原因');
            ROLLBACK;
    end;
end CUX_IMPORT_USER_PKG;