- Extract Data from Source System.
- If Source system is Oracle EBS only then use the following query to extract data. Please note that query having few columns only. Based on your requirement add additional columns or remove which are not required.
- Employee Data Extraction Query
- Create Staging Table.
- Load Data into Staging Table
- Run API Script.
Step2: Create Staging Table.
DROP TABLE XXSCHEMA.XX_EMPLOYEE_STG
/
DROP SYNONYM APPS.XX_EMPLOYEE_STG
/
CREATE TABLE XXSCHEMA.XX_EMPLOYEE_STG
(
EMPLOYEE_NUMBER VARCHAR2(30),
LAST_NAME VARCHAR2(150),
FIRST_NAME VARCHAR2(150),
MIDDLE_NAMES VARCHAR2(60),
TITLE VARCHAR2(30),
FULL_NAME VARCHAR2(240),
HIRE_DATE DATE,
ORIGINAL_DATE_OF_HIRE DATE,
DATE_OF_BIRTH DATE,
TOWN_OF_BIRTH VARCHAR2(90),
REGION_OF_BIRTH VARCHAR2(90),
COUNTRY_OF_BIRTH VARCHAR2(90),
SEX VARCHAR2(30),
NATIONALITY VARCHAR2(30),
PERSON_TYPE VARCHAR2(90),
NATIONAL_IDENTIFIER VARCHAR2(30),
BLOOD_TYPE VARCHAR2(30),
EMAIL_ADDRESS VARCHAR2(240),
MARITAL_STATUS VARCHAR2(30),
OFFICE_NUMBER VARCHAR2(45),
DISABILITY VARCHAR2(1),
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150),
ATTRIBUTE2 VARCHAR2(150),
ATTRIBUTE3 VARCHAR2(150),
ATTRIBUTE4 VARCHAR2(150),
ATTRIBUTE5 VARCHAR2(150),
ATTRIBUTE6 VARCHAR2(150),
ATTRIBUTE7 VARCHAR2(150),
ATTRIBUTE8 VARCHAR2(150),
ATTRIBUTE9 VARCHAR2(150),
ATTRIBUTE10 VARCHAR2(150),
PERSON_ID NUMBER,
BUSINESS_GROUP_ID NUMBER,
ASSIGNMENT_ID NUMBER(15),
STATUS VARCHAR2(5),
ERROR_MSG VARCHAR2(4000)
)
/
CREATE SYNONYM APPS.XX_EMPLOYEE_STG FOR XXSCHEMA.XX_EMPLOYEE_STG
/
STEP3: Load Data into Staging Table
Load data into staging table either using Control File or directly load from Toad/SQL Developer, etc.
STEP4: API Script
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;
No comments:
Post a Comment