Create Employee Records

How to create Employee records through "Hr_Employee_Api.CREATE_EMPLOYEE".

Please make the necessary changes according to your requirements.

Please create the structure for the staging table "XXX_HRMIG_EMP_PER_16MAR07".

CREATE OR REPLACE PROCEDURE XXX_Hrmig_Proc_Create_Emp AS
LN_ROWS_PROCESSED NUMBER := 0; 
LN_COMMIT_POINT NUMBER := 50; 
LN_PERSON_ID NUMBER;
LN_ASSIGNMENT_ID NUMBER;
LN_PER_OBJECT_VERSION_NUMBER NUMBER;
LN_ASG_OBJECT_VERSION_NUMBER NUMBER;
LD_PER_EFFECTIVE_START_DATE DATE;
LD_PER_EFFECTIVE_END_DATE DATE;
LS_FULL_NAME VARCHAR2(100);
LN_PER_COMMENT_ID NUMBER;
LN_ASSIGNMENT_SEQUENCE NUMBER;
LN_ASSIGNMENT_NUMBER NUMBER;
LB_NAME_COMBINATION_WARNING BOOLEAN;
LB_ASSIGN_PAYROLL_WARNING BOOLEAN;
LB_ORIG_HIRE_WARNING BOOLEAN;
LS_ERROR_TEXT VARCHAR2(2000);
LN_BUSINESS_GROUP_ID NUMBER;
LN_EMP_NO VARCHAR2(10);
L_ERR_MESSAGE VARCHAR2(1000);
LS_ERR_MSG VARCHAR2(1000);
L_TITLE VARCHAR2(10);
L_GENDER VARCHAR2(10);
L_HOUSING_STATUS VARCHAR2(100);
L_DOMICILE VARCHAR2(100);
L_RELIGION VARCHAR2(100);
L_BLOOD_GROUP VARCHAR2(20);
L_RESERVATION_CATEGORY VARCHAR2(30);
L_MARITAL_STATUS VARCHAR2(20);
L_RESIDENCE_ABROAD VARCHAR2(30);
L_VERIFY_FLAG VARCHAR2(10):='Y';
L_ANNIVERSARY_DATE DATE;
L_NATIONALITY VARCHAR2(100);
L_HAVE_A_PAN VARCHAR2(100);
L_RETIREMENT_DATE DATE;
L_PERSON_TYPE_ID NUMBER(10);

CURSOR CUR_EMP_APP IS
SELECT *
FROM XXX_HRMIG_EMP_PER_16MAR07 
WHERE NVL(VERIFY_FLAG,'N') = 'N';
-- AND EMPLOYEE_NUMBER = '7103';


BEGIN
Hr_General.G_DATA_MIGRATOR_MODE := 'Y';

SELECT ORGANIZATION_ID
INTO LN_BUSINESS_GROUP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_NAME = 'BUSINESS GROUP NAME';

FOR SEL IN CUR_EMP_APP LOOP
BEGIN
L_ERR_MESSAGE := NULL;
LS_ERR_MSG := NULL;


BEGIN
SELECT LOOKUP_CODE
INTO L_TITLE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'TITLE'
AND MEANING = trim(SEL.TITLE);

EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Title Tagged';
l_verify_flag := 'N';
END;

BEGIN

SELECT LOOKUP_CODE
INTO L_GENDER
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'SEX'
AND MEANING = trim(SEL.GENDER);

EXCEPTION
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Gender Tagged';
l_verify_flag := 'N';
END;

BEGIN

SELECT NVL(LOOKUP_CODE,'NOT KNOWN')
INTO L_BLOOD_GROUP
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE (+)= 'BLOOD_TYPE'
AND lookup_code (+)= TRIM(SEL.BLOOD_GROUP);

EXCEPTION

WHEN NO_DATA_FOUND THEN
L_BLOOD_GROUP:='NOT KNOWN';

WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid Blood Group Tagged';
END;

BEGIN

SELECT LOOKUP_CODE
INTO L_MARITAL_STATUS
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'MAR_STATUS'
AND MEANING = TRIM(SEL.MARITAL_STATUS);

EXCEPTION

WHEN NO_DATA_FOUND THEN
L_MARITAL_STATUS :=NULL;

WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Marital Status Tagged';
l_verify_flag := 'N';
END;

BEGIN

SELECT LOOKUP_CODE
INTO L_NATIONALITY
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND MEANING = TRIM(SEL.NATIONALITY);

EXCEPTION

WHEN NO_DATA_FOUND THEN
L_NATIONALITY:=NULL;
WHEN OTHERS THEN
L_VERIFY_FLAG:='N';
l_err_message :=l_err_message|| 'InValid NATIONALITY Tagged';
END;

BEGIN

SELECT FLEX_VALUE
INTO L_DOMICILE
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011575
AND DESCRIPTION = TRIM(INITCAP(SEL.DOMICILE));

EXCEPTION

WHEN NO_DATA_FOUND THEN
L_DOMICILE:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid domicile Tagged';
L_VERIFY_FLAG:='N';
END;

BEGIN

SELECT FLEX_VALUE
INTO L_RELIGION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID = 1011578
AND DESCRIPTION = TRIM(INITCAP(SEL.RELIGION));

EXCEPTION

WHEN NO_DATA_FOUND THEN
L_RELIGION:=NULL;
WHEN OTHERS THEN
l_err_message :=l_err_message|| 'InValid Religion Tagged';
l_verify_flag := 'N';
END;

BEGIN
SELECT PERSON_TYPE_ID 
INTO L_PERSON_TYPE_ID 
FROM per_person_types 
WHERE business_group_id = 385 
AND user_person_type = trim(SEL.PERSON_TYPE);

EXCEPTION

WHEN OTHERS THEN
L_ERR_MESSAGE := L_ERR_MESSAGE||'Invalide Person Type Tagged';
L_VERIFY_FLAG := 'N';
END;

-- L_RETIREMENT_DATE := LAST_DAY(ADD_MONTHS(SEL.DOB,696));


IF(L_VERIFY_FLAG<>'N') THEN

BEGIN

Hr_Employee_Api.CREATE_EMPLOYEE
(P_HIRE_DATE => TRIM(SEL.DOJ)
,P_BUSINESS_GROUP_ID => LN_BUSINESS_GROUP_ID
,P_LAST_NAME => TRIM(SEL.LAST_NAME)
,P_SEX => L_GENDER
,p_person_type_id => L_PERSON_TYPE_ID
,P_EMPLOYEE_NUMBER => SEL.EMPLOYEE_NUMBER
,P_FIRST_NAME => TRIM(SEL.FIRST_NAME)
,P_MIDDLE_NAMES => TRIM(SEL.MIDDLE_NAME)
,P_DATE_OF_BIRTH => TRIM(SEL.DOB)
,P_TOWN_OF_BIRTH => TRIM(SEL.PLACE_OF_BIRTH)
,P_KNOWN_AS => TRIM(SEL.FIRST_NAME)-----
,P_MARITAL_STATUS => L_MARITAL_STATUS
,P_NATIONALITY => L_NATIONALITY
,P_TITLE => L_TITLE
,P_PER_INFORMATION_CATEGORY => 'IN'
,P_PER_INFORMATION5 => SEL.HAVE_A_PAN
,P_PER_INFORMATION4 => TRIM(SEL.PAN_NO)
-- ,P_PER_INFORMATION7 => 'RO'--L_RESIDENCE_ABROAD
,P_PER_INFORMATION18 => TRIM(SEL.APEX_CODE)
,P_PER_INFORMATION8 => TRIM(SEL.PF_NO)
,P_PER_INFORMATION15 => TRIM(SEL.FPF_NO)
,P_PER_INFORMATION19 => L_DOMICILE
,P_PER_INFORMATION11 => TRIM(SEL.GI_NUMBER)
,P_PER_INFORMATION12 => TRIM(SEL.GRATUITY_NUMBER)
,P_ATTRIBUTE_CATEGORY => 'EMP'
-- ,P_ATTRIBUTE17 => L_RETIREMENT_DATE
,P_ATTRIBUTE19 => TRIM(SEL.HEIGHT)
,P_ATTRIBUTE20 => TRIM(SEL.WEIGHT)
,P_ATTRIBUTE22 => SEL.RESERVATION_CATEGORY
,P_ATTRIBUTE23 => L_RELIGION
,P_BLOOD_TYPE => L_BLOOD_GROUP
,P_EMAIL_ADDRESS => TRIM(SEL.OFFICE_EMAIL_ID)
,P_PERSON_ID => LN_PERSON_ID
,P_ASSIGNMENT_ID => LN_ASSIGNMENT_ID
,P_PER_OBJECT_VERSION_NUMBER => LN_PER_OBJECT_VERSION_NUMBER
,P_ASG_OBJECT_VERSION_NUMBER => LN_ASG_OBJECT_VERSION_NUMBER
,P_PER_EFFECTIVE_START_DATE => LD_PER_EFFECTIVE_START_DATE
,P_PER_EFFECTIVE_END_DATE => LD_PER_EFFECTIVE_END_DATE
,P_FULL_NAME => LS_FULL_NAME
,P_PER_COMMENT_ID => LN_PER_COMMENT_ID
,P_ASSIGNMENT_SEQUENCE => LN_ASSIGNMENT_SEQUENCE
,P_ASSIGNMENT_NUMBER => LN_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 XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'Y',
PERSON_ID = LN_PERSON_ID
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;

EXCEPTION
WHEN OTHERS THEN
LS_ERR_MSG := SQLERRM;
UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERR_MSG
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;
END;

ELSE

UPDATE XXX_HRMIG_EMP_PER_16MAR07
SET VERIFY_FLAG = 'N',
ERR_MESSAGE = L_ERR_MESSAGE || LS_ERROR_TEXT
WHERE EMPLOYEE_NUMBER = SEL.EMPLOYEE_NUMBER;

END IF;
L_VERIFY_FLAG:='Y';
END;
END LOOP;
COMMIT;
END XXX_Hrmig_Proc_Create_Emp;
/