Employee Creation in Oracle HRMS
How to find correct API for Data Uploading
Hi...I m providing the table definition and Procedure only, i hope you can create control file using Table structure.
How to create Control file for Sql Loader
Note:- You may change the number of segments for job, grade, position and any DFF according to your HRMS setup.
Find the table and procedure definition below.
1) TABLE
How to create an Oracle User and attach responsibility in Oracle HRMS
Hi...I m providing the table definition and Procedure only, i hope you can create control file using Table structure.
How to create Control file for Sql Loader
Note:- You may change the number of segments for job, grade, position and any DFF according to your HRMS setup.
Find the table and procedure definition below.
1) TABLE
CREATE TABLE DEV_EMPLOYEE
(
LINE_ID NUMBER,
EMP_CODE VARCHAR2(10 BYTE),
TITLE VARCHAR2(150 BYTE),
FIRST_NAME VARCHAR2(240 BYTE),
LAST_NAME VARCHAR2(240 BYTE),
DATE_OF_BIRTH DATE,
HIRE_DATE DATE,
PLACE_OF_BIRTH VARCHAR2(50 BYTE),
COUNTRY_OF_BIRTH VARCHAR2(50 BYTE),
NATIONALITY VARCHAR2(50 BYTE),
NIC VARCHAR2(100 BYTE),
GENDER VARCHAR2(20 BYTE),
MARITAL_STATUS VARCHAR2(20 BYTE),
OFFICE_EMAIL VARCHAR2(50 BYTE),
PERSONAL_EMAIL VARCHAR2(240 BYTE),
PERSON_TYPE VARCHAR2(100 BYTE),
ERROR_DESCRIPTION VARCHAR2(240 BYTE),
PERSON_ID NUMBER,
ASSIGNMENT_ID NUMBER,
PERSON_OBJ_VER_NUMBER NUMBER,
ASG_OBJ_VER_NUMBER NUMBER,
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE,
FULL_NAME VARCHAR2(240 BYTE),
COMMENT_ID NUMBER,
ASSG_SEQUENCE NUMBER,
ASSG_NUMBER VARCHAR2(24 BYTE),
NAME_COMB_WARNING VARCHAR2(10 BYTE),
ASSG_PAYROLL_WARNING VARCHAR2(10 BYTE),
ORIG_HIRE_WARNING VARCHAR2(10 BYTE),
ASSG_PROCESS_FLAG CHAR(1 BYTE),
ASSG_ERROR_DESCRIPTION VARCHAR2(240 BYTE),
ASSG_OBJ_VNUMBER NUMBER,
ASSG_EFF_SD DATE,
ASSG_EFF_ED DATE,
ASSG_PEOPLE_GROUP_ID NUMBER,
ASSG_GROUP_NAME VARCHAR2(240 BYTE),
ASSG_SCODE_FLEX_ID NUMBER,
ASSG_CONC_SEGMENTS VARCHAR2(240 BYTE)
)
2) PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_EMPLOYEE
IS
CURSOR C_EMP
IS
SELECT LINE_ID, EMP_CODE, TITLE, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH,
HIRE_DATE, PLACE_OF_BIRTH, COUNTRY_OF_BIRTH, NATIONALITY, NIC,
GENDER, MARITAL_STATUS,
PERSON_TYPE,
ERROR_DESCRIPTION, PERSON_ID, ASSIGNMENT_ID,
PERSON_OBJ_VER_NUMBER, ASG_OBJ_VER_NUMBER, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE, FULL_NAME, COMMENT_ID, ASSG_SEQUENCE,
ASSG_NUMBER, NAME_COMB_WARNING, ASSG_PAYROLL_WARNING,
ORIG_HIRE_WARNING
FROM DEV_EMPLOYEE;
--WHERE NVL (PROCESS_FLAG,
'N') <> 'Y' AND ERROR_DESCRIPTION IS NULL;
LC_C_EMP
C_EMP%ROWTYPE;
L_BATCH_ID NUMBER;
L_PER_OBJECT_VERSION_NUMBER NUMBER;
L_ASG_OBJECT_VERSION_NUMBER NUMBER;
L_PERSON_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_PER_EFFECTIVE_START_DATE DATE;
L_PER_EFFECTIVE_END_DATE DATE;
L_FULL_NAME VARCHAR2 (60);
L_PER_COMMENT_ID NUMBER;
L_ASSIGNMENT_SEQUENCE NUMBER;
L_ASSIGNMENT_NUMBER VARCHAR2 (30);
L_NAME_COMBINATION_WARNING BOOLEAN;
L_ASSIGN_PAYROLL_WARNING BOOLEAN;
L_EMP_NUMBER VARCHAR2 (30);
L_USER_KEY VARCHAR2 (4000);
L_ASSG_USER_KEY VARCHAR2 (4000);
L_ORIG_HIRE_WARNING BOOLEAN;
L_PERSON_EXTRA_INFO_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
ERROR_DESC VARCHAR2 (240);
LV_PER_FLAG CHAR (1);
LV_INFO_FLAG CHAR
(1);
L_GENDER VARCHAR2 (1);
L_COUNTRY_CODE VARCHAR2 (5);
L_MARITAL_STATUS VARCHAR2 (15);
L_NATIONALITY VARCHAR2 (20);
L_TITLE VARCHAR2 (20);
L_RELIGION VARCHAR2 (100);
L_BLOOD_GROUP VARCHAR2 (100);
L_PERSON_TYPE VARCHAR2 (100);
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP
INTO LC_C_EMP;
EXIT WHEN C_EMP%NOTFOUND;
----------------------
-- TITLE LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE
INTO L_TITLE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'TITLE'
AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_EMP.TITLE));
----------------------
-- GENDER LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE
INTO L_GENDER
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'IGW_SUBJECT_TYPE'
AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_EMP.GENDER));
BEGIN
----------------------
-- COUNTRY LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE
INTO L_COUNTRY_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'JEES_EURO_COUNTRY_CODES'
AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_EMP.COUNTRY_OF_BIRTH));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ERROR_DESC := ERROR_DESC || 'COUNTRY LOOKUP ERROR';
END;
IF LC_C_EMP.MARITAL_STATUS IS NOT NULL
THEN
BEGIN
----------------------
-- MARITAL STATUS LOOKUP
VALUE
----------------------
SELECT LOOKUP_CODE
INTO L_MARITAL_STATUS
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'MAR_STATUS'
AND UPPER (MEANING) = UPPER (TRIM (LC_C_EMP.MARITAL_STATUS));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ERROR_DESC := ERROR_DESC || 'MARITAL LOOKUP ERROR';
END;
END IF;
BEGIN
----------------------
-- NATIONALITY LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE
INTO L_NATIONALITY
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND UPPER (MEANING) = UPPER (TRIM (LC_C_EMP.NATIONALITY));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ERROR_DESC := ERROR_DESC || 'NATIONALITY LOOKUP ERROR';
END;
SELECT PERSON_TYPE_ID
INTO L_PERSON_TYPE
FROM PER_PERSON_TYPES_V
WHERE UPPER (USER_PERSON_TYPE) = UPPER (TRIM (LC_C_EMP.PERSON_TYPE))
AND BUSINESS_GROUP_ID = 2217;
/*
IF LC_C_EMP.RELIGION IS NOT
NULL THEN
BEGIN
----------------------
-- RELIGION LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE INTO L_RELIGION
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'PK_RELIGIONS'
AND UPPER(MEANING) =
UPPER(TRIM(LC_C_EMP.RELIGION));
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERROR_DESC := ERROR_DESC ||'RELIGION LOOKUP ERROR';
END;
END IF;
*/
/*
IF LC_C_EMP.BLOOD_GROUP IS
NOT NULL THEN
BEGIN
----------------------
-- BLOOD GROUP LOOKUP VALUE
----------------------
SELECT LOOKUP_CODE INTO L_BLOOD_GROUP
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'PK_BLOOD_GROUPS'
AND UPPER(MEANING) =
UPPER(TRIM(LC_C_EMP.BLOOD_GROUP));
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERROR_DESC := ERROR_DESC ||'BLOOD GROUP LOOKUP
ERROR';
END;
END IF;
*/
BEGIN
HR_EMPLOYEE_API.CREATE_EMPLOYEE
(P_VALIDATE => FALSE,
P_BUSINESS_GROUP_ID => 2217,
P_LAST_NAME => LC_C_EMP.LAST_NAME,
P_FIRST_NAME => LC_C_EMP.FIRST_NAME,
P_TITLE => L_TITLE,
P_SEX => L_GENDER,
P_PERSON_TYPE_ID => L_PERSON_TYPE ,
P_NATIONAL_IDENTIFIER => LC_C_EMP.NIC,
P_HIRE_DATE => LC_C_EMP.HIRE_DATE,
P_DATE_OF_BIRTH => LC_C_EMP.DATE_OF_BIRTH,
P_TOWN_OF_BIRTH => LC_C_EMP.PLACE_OF_BIRTH,
P_COUNTRY_OF_BIRTH => L_COUNTRY_CODE,
P_MARITAL_STATUS => L_MARITAL_STATUS,
P_NATIONALITY => L_NATIONALITY ,
-- P_ATTRIBUTE_CATEGORY => 'ADDITIONAL PERSONAL
DETAILS'
-- ,P_ATTRIBUTE2 => LC_C_EMP.OLD_NIC
P_PERSON_ID => L_PERSON_ID,
P_EMPLOYEE_NUMBER => LC_C_EMP.EMP_CODE,
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_PER_OBJECT_VERSION_NUMBER => L_PER_OBJECT_VERSION_NUMBER,
P_ASG_OBJECT_VERSION_NUMBER => L_ASG_OBJECT_VERSION_NUMBER,
P_PER_EFFECTIVE_START_DATE => L_PER_EFFECTIVE_START_DATE,
P_PER_EFFECTIVE_END_DATE => L_PER_EFFECTIVE_END_DATE,
P_FULL_NAME => L_FULL_NAME,
P_PER_COMMENT_ID => L_PER_COMMENT_ID,
P_ASSIGNMENT_SEQUENCE => L_ASSIGNMENT_SEQUENCE,
P_ASSIGNMENT_NUMBER => L_ASSIGNMENT_NUMBER,
P_NAME_COMBINATION_WARNING => L_NAME_COMBINATION_WARNING,
P_ASSIGN_PAYROLL_WARNING => L_ASSIGN_PAYROLL_WARNING,
P_ORIG_HIRE_WARNING => L_ORIG_HIRE_WARNING
);
IF L_PERSON_ID IS NOT NULL
THEN
LV_PER_FLAG := 'Y';
END IF;
IF L_NAME_COMBINATION_WARNING = FALSE
THEN
ERROR_DESC := 'NO NAME WARNING,';
ELSE
ERROR_DESC := 'NAME COMBINATION WARNING,';
END IF;
IF L_ASSIGN_PAYROLL_WARNING = FALSE
THEN
ERROR_DESC := ERROR_DESC || ' NO PAYROLL WARNING,';
ELSE
ERROR_DESC := ERROR_DESC || ' PAYROLL BIRTH_DATE WARNING,';
END IF;
IF L_ORIG_HIRE_WARNING = FALSE
THEN
ERROR_DESC := ERROR_DESC || ' NO HIRE_DATE WARNING,';
ELSE
ERROR_DESC := ERROR_DESC || ' HIRE_DATE WARNING,';
END IF;
IF LV_PER_FLAG = 'Y'
THEN
UPDATE DEV_EMPLOYEE
SET ERROR_DESCRIPTION = ERROR_DESC,
-- PROCESS_FLAG = LV_PER_FLAG,
PERSON_ID = L_PERSON_ID,
ASSIGNMENT_ID = L_ASSIGNMENT_ID,
PERSON_OBJ_VER_NUMBER =
L_PER_OBJECT_VERSION_NUMBER,
ASG_OBJ_VER_NUMBER =
L_ASG_OBJECT_VERSION_NUMBER,
EFFECTIVE_START_DATE =
L_PER_EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE =
L_PER_EFFECTIVE_END_DATE,
FULL_NAME = L_FULL_NAME,
COMMENT_ID = L_PER_COMMENT_ID,
ASSG_SEQUENCE = L_ASSIGNMENT_SEQUENCE,
ASSG_NUMBER = L_ASSIGNMENT_NUMBER
WHERE EMP_CODE = LC_C_EMP.EMP_CODE AND LINE_ID = LC_C_EMP.LINE_ID;
ELSE
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_EMPLOYEE
SET ERROR_DESCRIPTION = ERROR_DESC
-- PROCESS_FLAG = LV_PER_FLAG
WHERE EMP_CODE = LC_C_EMP.EMP_CODE AND LINE_ID = LC_C_EMP.LINE_ID;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LV_PER_FLAG := 'N';
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_EMPLOYEE
SET ERROR_DESCRIPTION = ERROR_DESC
-- PROCESS_FLAG = LV_PER_FLAG
WHERE EMP_CODE = LC_C_EMP.EMP_CODE AND LINE_ID = LC_C_EMP.LINE_ID;
DBMS_OUTPUT.PUT_LINE
(SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE
('PERSON ID : ' || L_PERSON_ID);
LV_PER_FLAG := '';
LV_INFO_FLAG := '';
ERROR_DESC := '';
L_PERSON_ID := '';
L_EMP_NUMBER := '';
L_ASSIGNMENT_ID := '';
L_ASG_OBJECT_VERSION_NUMBER :=
'';
L_PER_EFFECTIVE_START_DATE :=
'';
L_PER_EFFECTIVE_END_DATE :=
'';
L_FULL_NAME := '';
L_PER_COMMENT_ID := '';
L_ASSIGNMENT_SEQUENCE := '';
L_ASSIGNMENT_NUMBER := '';
L_NAME_COMBINATION_WARNING :=
FALSE;
L_ASSIGN_PAYROLL_WARNING :=
FALSE;
L_ORIG_HIRE_WARNING := FALSE;
L_PERSON_EXTRA_INFO_ID := '';
L_OBJECT_VERSION_NUMBER := '';
END LOOP;
CLOSE C_EMP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
(SQLERRM);
END;
/
Comment for any query.
How to create an Oracle User and attach responsibility in Oracle HRMS
0 comments: