CREATE OR REPLACE PROCEDURE insert_employee
AS
CURSOR c_emp
IS
SELECT *
FROM ynppo_temp_employee yte
WHERE yte.status IS NULL AND yte.flag = 'I';
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;
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;
v_flag VARCHAR2 (1);
v_count_emp NUMBER;
BEGIN
FOR r_emp IN c_emp
LOOP
lc_employee_number := r_emp.employee_code;
BEGIN
SELECT COUNT (*)
INTO v_count_emp
FROM ynppo_temp_employee yte
WHERE yte.status = 'Inserted'
AND yte.flag = 'I'
AND employee_code = r_emp.employee_code;
EXCEPTION
WHEN OTHERS
THEN
v_count_emp := 0;
END;
IF v_count_emp > 0
THEN
UPDATE ynppo_temp_employee
SET status = 'Not Inserted',
notes = 'Your data is already inputed in oracle'
WHERE employee_code = r_emp.employee_code AND ID = r_emp.ID;
ELSE
BEGIN
hr_employee_api.create_employee
(-- Input data elements
-- ------------------------------
p_hire_date => r_emp.hire_date,
p_business_group_id => fnd_profile.value_specific
('PER_BUSINESS_GROUP_ID'
),
p_last_name => r_emp.last_name,
p_first_name => r_emp.first_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_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
);
UPDATE ynppo_temp_employee
SET status = 'Inserted'
WHERE employee_code = r_emp.employee_code AND ID = r_emp.ID;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
UPDATE ynppo_temp_employee
SET status = 'Not Inserted'
WHERE employee_code = r_emp.employee_code AND ID = r_emp.ID;
END;
END IF;
END LOOP;
END;
The YNPPO_TEMP_EMPLOYEE is staging table to store employee data which will be uploaded using API.
No comments:
Post a Comment