Load data into staging table either using Control File or directly load from Toad/SQL Developer, etc.
DECLARE
/*Declaration Of Local variables*/
/*===========================================================================*/
v_status VARCHAR2(1) := NULL;
v_err_msg VARCHAR2(4000) := NULL;
v_total_records NUMBER(15) := 0;
v_success_records NUMBER(15) := 0;
v_failure_records NUMBER(15) := 0;
v_emp_exists_cnt NUMBER(15) := 0;
v_national_identifier_exts_cnt NUMBER(15) := 0;
x_person_id NUMBER := NULL;
v_gender_code VARCHAR2(15) := NULL;
v_marital_status VARCHAR2(15) := NULL;
v_nationality VARCHAR2(15) := NULL;
v_country_of_birth VARCHAR2(15) := NULL;
--x_lang_code VARCHAR2(15) := NULL;
v_title VARCHAR2(15) := NULL;
x_assignment_id NUMBER := NULL;
v_person_type_id NUMBER := NULL;
x_race VARCHAR2(60) := NULL;
x_out_employee_number NUMBER := NULL;
x_out_person_id NUMBER := NULL;
x_out_assignment_id NUMBER := NULL;
x_out_per_obj_vers_no NUMBER := NULL;
x_out_asg_obj_vers_no NUMBER := NULL;
x_out_per_effective_start_date DATE;
x_out_per_effective_end_date DATE;
x_out_full_name VARCHAR2(240) := NULL;
x_out_per_comment_id NUMBER := NULL;
x_out_assignment_sequence NUMBER := NULL;
x_out_assignment_number NUMBER := NULL;
x_religion_code VARCHAR2(200);
x_out_orig_hire_warning BOOLEAN;
ni_number EXCEPTION;
v_business_group_id NUMBER := 1;
x_ni VARCHAR2(30) := NULL;
x_work_schedule varchar2(50);
x_out_name_combination_warning BOOLEAN;
x_out_assign_payroll_warning BOOLEAN;
l_user_id fnd_user.user_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_resp_appl_id fnd_responsibility_tl.application_id%TYPE;
l_org_id hr_operating_units.organization_id%TYPE;
----
CURSOR cur_xxhr_employee IS
SELECT *
FROM XX_employee_stg stg
WHERE NVL(status, 'E') = 'E'
and employee_number NOT IN (SELECT NVL(employee_number, 1) FROM per_all_people_f papf)
and employee_number = '1234567890'
ORDER BY employee_number ASC, hire_date DESC;
BEGIN
l_user_id := NULL;
l_resp_id := NULL;
l_resp_appl_id := NULL;
l_org_id := NULL;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XX_USER';
EXCEPTION
WHEN OTHERS THEN
l_user_id := NULL;
END;
BEGIN
SELECT responsibility_id, application_id
INTO l_resp_id, l_resp_appl_id
FROM fnd_responsibility_tl
WHERE responsibility_name = 'Global HRMS Manager'
AND language = USERENV('LANG')
;
EXCEPTION
WHEN OTHERS THEN
l_resp_id := NULL;
l_resp_appl_id := NULL;
END;
-- Initialize apps session
fnd_global.apps_initialize(user_id => l_user_id
,resp_id => l_resp_id
,resp_appl_id => l_resp_appl_id
);
DBMS_OUTPUT.PUT_LINE('#############################################################');
DBMS_OUTPUT.PUT_LINE('DATA MIGRATION OF EMPLOYEE :');
DBMS_OUTPUT.PUT_LINE('#############################################################');
DBMS_OUTPUT.PUT_LINE('START TIME : ' ||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
FOR rec_xxhr_employee IN cur_xxhr_employee LOOP
dbms_output.put_line('Loop Opened');
v_status := 'N';
v_err_msg := NULL;
v_total_records := v_total_records + 1;
v_emp_exists_cnt := 0;
v_national_identifier_exts_cnt := 0;
x_person_id := 0;
v_gender_code := NULL;
x_assignment_id := 0;
v_person_type_id := 0;
v_marital_status := NULL;
v_nationality := NULL;
v_title := NULL;
v_country_of_birth := NULL;
/*x_registered_disabled := NULL;
x_country_code := NULL;
x_lang_code := NULL;*/
x_out_employee_number := NULL;
x_out_person_id := NULL;
x_out_assignment_id := NULL;
x_out_per_obj_vers_no := NULL;
x_out_asg_obj_vers_no := NULL;
x_out_full_name := NULL;
x_out_per_comment_id := NULL;
x_out_assignment_sequence := NULL;
x_out_assignment_number := NULL;
/* Check For Local Employee Numbmer */
IF rec_xxhr_employee.employee_number IS NULL THEN
v_err_msg := v_err_msg || 'Employee Number cannot be blank';
v_status := 'E';
ELSE
SELECT COUNT(1)
INTO v_emp_exists_cnt
FROM per_all_people_f papf
WHERE UPPER(TRIM(papf.employee_number)) = UPPER(TRIM(rec_xxhr_employee.employee_number))
AND papf.business_group_id = v_business_group_id
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND effective_end_date;
IF v_emp_exists_cnt > 0 THEN
v_status:='E';
v_err_msg := v_err_msg||'EMPLOYEE_NUMBER already exists; ';
END IF;
END IF;
--Validation For Gender
IF rec_xxhr_employee.sex IS NULL THEN
v_status:='E';
v_err_msg := v_err_msg||'Employee Gender Cannot be Null; ';
ELSIF rec_xxhr_employee.sex IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO v_gender_code
FROM hr_lookups
WHERE lookup_type ='SEX'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(rec_xxhr_employee.sex));
EXCEPTION
WHEN OTHERS THEN
v_status:='E';
v_err_msg := v_err_msg||'Employee GENDER(SEX) is not valid; ';
END;
END IF;
--Validation for Person_type
IF rec_xxhr_employee.person_type IS NULL THEN
v_err_msg := 'E';
v_err_msg := v_err_msg||'PERSON_TYPE cannot be Null; ';
ELSIF rec_xxhr_employee.person_type IS NOT NULL THEN
BEGIN
SELECT person_type_id
INTO v_person_type_id
FROM per_person_types
WHERE UPPER(TRIM(user_person_type)) = UPPER(TRIM(rec_xxhr_employee.person_type))
AND business_group_id = v_business_group_id;
EXCEPTION
WHEN OTHERS THEN
v_status :='E';
v_err_msg := v_err_msg||'PERSON_TYPE is not valid; ';
END;
END IF;
--Validation for Birth Date
IF rec_xxhr_employee.date_of_birth IS NULL THEN
v_status :='E';
v_err_msg := v_err_msg||'DATE_OF_BIRTH cannot Null; ';
END IF;
--Validation for Email Address
IF rec_xxhr_employee.email_address IS NULL THEN
v_status := 'E';
v_err_msg := v_err_msg||'EMAIL_ADDRESS cannot Null; ';
END IF;
--Validation for Marital Status
IF rec_xxhr_employee.marital_status IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO v_marital_status
FROM hr_lookups
WHERE lookup_type = 'MAR_STATUS'
AND UPPER(TRIM(meaning))=UPPER(TRIM(rec_xxhr_employee.marital_status));
EXCEPTION
WHEN OTHERS THEN
v_status := 'E';
v_err_msg := v_err_msg||'MARITAL_STATUS is not valid; ';
END;
END IF;
--Validation for Nationality
IF rec_xxhr_employee.nationality IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO v_nationality
FROM hr_lookups
WHERE lookup_type = 'NATIONALITY'
AND UPPER(TRIM(meaning)) = UPPER(TRIM(rec_xxhr_employee.nationality));
EXCEPTION
WHEN OTHERS THEN
v_status := 'E';
v_err_msg := v_err_msg||'NATIONALITY is not valid; ';
END;
END IF;
--Validation for Title
IF rec_xxhr_employee.title IS NULL THEN
v_status := 'E';
v_err_msg := v_err_msg||'TITLE cannot Null; ';
ELSIF rec_xxhr_employee.title IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO v_title
FROM hr_lookups
WHERE lookup_type ='TITLE'
AND UPPER(TRIM(meaning)) = UPPER(TRIM(rec_xxhr_employee.title));
EXCEPTION
WHEN OTHERS THEN
v_status := 'E';
v_err_msg := v_err_msg||'TITLE is not valid; ';
END;
END IF;
--Validation for Country
IF rec_xxhr_employee.COUNTRY_OF_BIRTH IS NOT NULL THEN
BEGIN
SELECT territory_code
INTO v_country_of_birth
FROM apps.fnd_territories_vl
--WHERE UPPER(TRIM (territory_short_name)) = UPPER(TRIM(rec_xxhr_employee.country_of_birth));
WHERE UPPER(TRIM (territory_code)) = UPPER(TRIM(rec_xxhr_employee.country_of_birth));
EXCEPTION
WHEN OTHERS THEN
v_status := 'E';
v_err_msg := v_err_msg||'COUNTRY_OF_BIRTH is not valid; ';
END;
END IF;
IF rec_xxhr_employee.national_identifier IS NOT NULL THEN
SELECT COUNT(national_identifier)
INTO v_national_identifier_exts_cnt
FROM per_all_people_f papf
WHERE UPPER(TRIM(papf.national_identifier)) = UPPER(TRIM(rec_xxhr_employee.national_identifier))
AND papf.business_group_id = v_business_group_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND effective_end_date;
IF v_national_identifier_exts_cnt > 0 THEN
v_status := 'E';
v_err_msg := v_err_msg||'NATIONAL_IDENTIFIER is already exists; ';
END IF;
END IF;
IF v_status <> 'E' THEN
BEGIN
APPS.Hr_Employee_Api.create_employee(p_validate => FALSE,
p_hire_date => rec_xxhr_employee.hire_date,
p_business_group_id => v_business_group_id,
p_last_name => rec_xxhr_employee.last_name,
p_sex => v_gender_code,--rec_xxhr_employee.sex,
p_person_type_id => v_person_type_id,
p_date_of_birth => rec_xxhr_employee.date_of_birth,
p_email_address => rec_xxhr_employee.email_address,
p_employee_number => rec_xxhr_employee.employee_number,
p_first_name => rec_xxhr_employee.first_name,
p_marital_status => v_marital_status,--rec_xxhr_employee.marital_status,
p_middle_names => rec_xxhr_employee.middle_names,
p_nationality => v_nationality,--rec_xxhr_employee.nationality,
p_national_identifier => rec_xxhr_employee.national_identifier,
p_registered_disabled_flag => rec_xxhr_employee.disability,
p_title => v_title,--UPPER(rec_xxhr_employee.title),
p_attribute_category => rec_xxhr_employee.attribute_Category,
p_attribute1 => rec_xxhr_employee.attribute1,
p_Attribute2 => rec_xxhr_employee.attribute2,
p_attribute3 => rec_xxhr_employee.attribute3,
p_blood_type => rec_xxhr_employee.blood_type,
p_office_number => rec_xxhr_employee.office_number,
p_original_date_of_hire => rec_xxhr_employee.original_date_of_hire,
p_town_of_birth => rec_xxhr_employee.town_of_birth,
p_region_of_birth => rec_xxhr_employee.region_of_birth,
p_country_of_birth => v_country_of_birth,--rec_xxhr_employee.country_of_birth,
p_person_id => x_out_person_id,
p_assignment_id => x_out_assignment_id,
p_per_object_version_number => x_out_per_obj_vers_no,
p_asg_object_version_number => x_out_asg_obj_vers_no,
p_per_effective_start_date => x_out_per_effective_start_date,
p_per_effective_end_date => x_out_per_effective_end_date,
p_full_name => x_out_full_name,
p_per_comment_id => x_out_per_comment_id,
p_assignment_sequence => x_out_assignment_sequence,
p_assignment_number => x_out_assignment_number,
p_name_combination_warning => x_out_name_combination_warning,
p_assign_payroll_warning => x_out_assign_payroll_warning,
p_orig_hire_warning => x_out_orig_hire_warning
);
--COMMIT;
v_success_records := v_success_records + 1;
UPDATE XX_employee_stg
SET status = 'S',
error_msg = 'Success',
person_id = x_out_person_id,
assignment_id = x_out_assignment_id
WHERE employee_number = rec_xxhr_employee.employee_number;
--COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_err_msg := SQLERRM;
v_failure_records := v_failure_records + 1;
DBMS_OUTPUT.PUT_LINE('API Exception :' || SQLERRM);
UPDATE XX_employee_stg
SET status = 'E'
,error_msg = v_err_msg
WHERE employee_number = rec_xxhr_employee.employee_number;
--COMMIT;
END;
ELSE
v_failure_records := v_failure_records + 1;
UPDATE XX_employee_stg
SET status = 'E'
,error_msg = v_err_msg
WHERE employee_number = rec_xxhr_employee.employee_number;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('v_total_records :' || v_total_records);
DBMS_OUTPUT.PUT_LINE('v_success_records :' || v_success_records);
DBMS_OUTPUT.PUT_LINE('v_failure_records :' || v_failure_records);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Main Exception :-' || SQLERRM);
END;
