.jpg)
1. 业务背景
在 Oracle EBS HRMS 开发中，通过程序批量导入员工是核心业务场景。相比直接操作基表，调用标准 API hr_employee_api.create_employee 能够自动处理复杂的业务逻辑校验。
2. 核心代码实现
以下是一个完整的 PL/SQL 调用示例，包含了必要的变量定义和异常处理逻辑：
SQL
DECLARE
-- 定义变量
LC_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
LN_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
LN_ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
LN_OBJECT_VER_NUMBER PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
ln_asg_ovn NUMBER := 5;
LD_PER_EFFECTIVE_START_DATE PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
LD_PER_EFFECTIVE_END_DATE PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE := NULL;
LC_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
LN_PER_COMMENT_ID PER_ALL_PEOPLE_F.COMMENT_ID%TYPE := NULL;
LN_ASSIGNMENT_SEQUENCE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
lc_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
LB_NAME_COMBINATION_WARNING BOOLEAN;
LB_ASSIGN_PAYROLL_WARNING BOOLEAN;
LB_ORIG_HIRE_WARNING BOOLEAN;
p_number varchar2(100) := 'test2019041'; -- 测试工号
BEGIN
-- 调用创建员工 API
hr_employee_api.create_employee(
p_person_type_id => 6, -- 需注意环境差异
p_hire_date => TO_DATE('2019-04-01','YYYY-MM-DD'),
P_BUSINESS_GROUP_ID => 0,
P_LAST_NAME => '王十',
P_FIRST_NAME => NULL,
p_sex => 'M',
p_employee_number => p_number,
-- 输出参数
p_person_id => ln_person_id,
p_assignment_id => ln_assignment_id,
p_per_object_version_number => ln_object_ver_number,
p_asg_object_version_number => ln_asg_ovn,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_assignment_sequence => ln_assignment_sequence,
p_assignment_number => lc_assignment_number,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
dbms_output.put_line('创建成功，Person ID: ' || ln_person_id);
-- COMMIT; -- 建议正式环境取消注释
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('错误信息: ' || SQLERRM);
END;
3. 常见坑点：ORA-20001 报错解析
在执行上述脚本时，常会遇到以下错误：
ORA-20001: A person type with a system person type EMP must be specified.
原因分析
这是由于 p_person_type_id 参数传入的 ID 在当前 Business Group 下不属于系统定义的 'EMP' 类型。不同环境下的 ID 往往不同，硬编码 ID（如示例中的 6）极易引发此报错。
解决方案
建议通过动态查询获取 ID，增强代码的可移植性：
SQL
DECLARE
-- 1. 定义 API 所需的输入输出变量
LC_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
LN_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
LN_ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
LN_OBJECT_VER_NUMBER PER_ALL_PEOPLE_F.OBJECT_VERSION_NUMBER%TYPE;
ln_asg_ovn NUMBER;
LD_PER_EFFECTIVE_START_DATE PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
LD_PER_EFFECTIVE_END_DATE PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
LC_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
LN_PER_COMMENT_ID PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
LN_ASSIGNMENT_SEQUENCE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
lc_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
LB_NAME_COMBINATION_WARNING BOOLEAN;
LB_ASSIGN_PAYROLL_WARNING BOOLEAN;
LB_ORIG_HIRE_WARNING BOOLEAN;
-- 2. 核心参数变量
p_number VARCHAR2(100) := 'test2019041';
l_business_group_id NUMBER := 0; -- 请根据实际 BG ID 修改
l_person_type_id NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
BEGIN
-- 3. 初始化环境上下文 (非常重要)
-- 替换下方的 ID 为你环境中的有效值（可通过 FND_USER 和 FND_RESPONSIBILITY 查看）
SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'OPERATIONS'; -- 示例用户
SELECT responsibility_id, application_id
INTO l_resp_id, l_resp_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'US HRMS Manager'; -- 示例职责
fnd_global.apps_initialize(user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id);
-- 4. 【核心修复】动态获取系统类型为 'EMP' 的人员类型 ID
-- 解决 ORA-20001: A person type with a system person type EMP must be specified.
BEGIN
SELECT person_type_id
INTO l_person_type_id
FROM per_person_types
WHERE business_group_id = l_business_group_id
AND system_person_type = 'EMP'
AND active_flag = 'Y'
AND default_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20001, '无法找到默认的员工(EMP)人员类型，请检查 Business Group 配置。');
END;
-- 5. 执行创建员工 API
hr_employee_api.create_employee(
p_person_type_id => l_person_type_id, -- 动态获取的 ID
p_hire_date => TO_DATE('2019-04-01', 'YYYY-MM-DD'),
p_business_group_id => l_business_group_id,
p_last_name => '王十',
p_first_name => NULL,
p_sex => 'M',
p_employee_number => p_number,
-- 输出参数
p_person_id => ln_person_id,
p_assignment_id => ln_assignment_id,
p_per_object_version_number => ln_object_ver_number,
p_asg_object_version_number => ln_asg_ovn,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_assignment_sequence => ln_assignment_sequence,
p_assignment_number => lc_assignment_number,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
-- 6. 输出结果
DBMS_OUTPUT.PUT_LINE('创建成功！');
DBMS_OUTPUT.PUT_LINE('Person ID: ' || ln_person_id);
DBMS_OUTPUT.PUT_LINE('Full Name: ' || lc_full_name);
-- COMMIT; -- 确认数据无误后取消注释进行提交
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('错误消息: ' || SQLERRM);
END;错误代码: -20001
错误消息: ORA-20001: You are no longer permitted to enter a number for this person. Please close and reopen the application window you are using. If you are using an API then please rerun the script that calls the API. Doing this enables the application to generate a sequential number automatically.
清空指定员工编号，让系统自动生成
将p_number VARCHAR2(100) := 'test2019041';改为p_number VARCHAR2(100) := null;后创建成功。
