Employee
data could be inserted from back end using oracle HRMS API
(HR_PERSON_API.UPDATE_PERSON).
Declare
CURSOR c_emp
IS
SELECT *
FROM ynppo_temp_employee yte
WHERE yte.status IS NULL AND yte.flag = 'U';
ln_object_version_number per_all_people_f.object_version_number%TYPE;
lc_dt_ud_mode VARCHAR2 (100) := NULL;
ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
lc_employee_number per_all_people_f.employee_number%TYPE;
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
ld_effective_start_date DATE;
ld_effective_end_date DATE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_comment_id per_all_people_f.comment_id%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
v_person_id NUMBER;
v_object_version_number NUMBER;
v_effective_start_date DATE;
v_assignment_id NUMBER;
BEGIN
FOR r_emp IN c_emp
LOOP
lc_employee_number := r_emp.employee_code;
BEGIN
SELECT papf.person_id, papf.object_version_number,
papf.effective_start_date, paaf.assignment_id
INTO v_person_id, v_object_version_number,
v_effective_start_date, v_assignment_id
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE papf.employee_number = r_emp.employee_code
AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.effective_start_date)
AND TRUNC (paaf.effective_end_date)
AND papf.person_id = paaf.person_id;
EXCEPTION
WHEN OTHERS
THEN
v_person_id := 0;
v_person_id := NULL;
END;
IF v_person_id = 0
THEN
NULL;
ELSE
-- Update Employee API
-- ---------------------------------
IF TRUNC (r_emp.effective_start_date) <>
TRUNC (v_effective_start_date)
THEN
lc_dt_ud_mode := 'UPDATE';
ELSE
lc_dt_ud_mode := 'CORRECTION';
END IF;
BEGIN
hr_person_api.update_person
( -- Input Data Elements
-- ------------------------------
p_effective_date => TRUNC (r_emp.effective_start_date),
p_datetrack_update_mode => lc_dt_ud_mode,
p_person_id => v_person_id,
p_first_name => r_emp.first_name,
p_last_name => r_emp.last_name,
p_middle_names => r_emp.middle_names,
p_sex => r_emp.gender,
p_date_of_birth => r_emp.date_of_birth,
p_title => r_emp.title,
p_email_address => r_emp.email,
p_national_identifier => lc_employee_number,
p_attribute1 => r_emp.LEVELS,
p_attribute2 => r_emp.level_desc,
p_attribute3 => r_emp.position_code,
p_attribute4 => r_emp.position_desc,
p_attribute5 => r_emp.dept_code,
p_attribute6 => r_emp.department,
p_attribute7 => r_emp.sub_dept_code,
p_attribute8 => r_emp.sub_depat,
p_attribute9 => r_emp.plant_code,
p_attribute10 => r_emp.plant,
p_attribute11 => r_emp.supervise_emp_code,
p_attribute12 => r_emp.old_employee_code,
-- Output Data Elements
-- ----------------------------------
p_employee_number => lc_employee_number,
p_object_version_number => v_object_version_number,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_full_name => lc_full_name,
p_comment_id => ln_comment_id,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
UPDATE ynppo_temp_employee
SET status = 'Updated'
WHERE ID = r_emp.ID;
EXCEPTION
WHEN OTHERS
THEN
--ROLLBACK;
UPDATE ynppo_temp_employee
SET status = 'Not Updated'
WHERE ID = r_emp.ID;
DBMS_OUTPUT.put_line (SQLERRM);
END;
-- COMMIT;
END IF;
END LOOP;
END;
The
YNPPO_TEMP_EMPLOYEE is staging table to store employee data which will be updated
using API.
