How to upload Employee Previous Employer details in Oracle HRMS
Navigation: Global HRMS Manager --> People --> Enter and Maintain --> Others --> Previous Employment
PROCEDURE
TABLE
CREATE TABLE DEV_EXEMPLOYER_EXP
(
LINE_ID NUMBER,
EMP_CODE VARCHAR2(150 BYTE),
EMP_NAME VARCHAR2(150 BYTE),
EMPLOYER_NAME VARCHAR2(150 BYTE),
EMPLOYER_ADDRESS VARCHAR2(150 BYTE),
COUNTRY VARCHAR2(150 BYTE),
EMPLOYER_TYPE VARCHAR2(150 BYTE),
START_DATE DATE,
END_DATE DATE,
DESIGNATION VARCHAR2(150 BYTE),
EMPLOYEE_CATEGORY VARCHAR2(150 BYTE),
REASON_FOR_LEAVING VARCHAR2(150 BYTE),
LAST_SALARY_DRAWN VARCHAR2(150 BYTE),
EXEMP_PROCESS_FLAG VARCHAR2(150 BYTE),
EXEMP_ERROR_DESCRIPTION VARCHAR2(150 BYTE),
EXJOB_PROCESS_FLAG VARCHAR2(150 BYTE),
ESTAB_PROCESS_FLAG VARCHAR2(150 BYTE),
EXJOB_ERROR_DESCRIPTION VARCHAR2(150 BYTE),
PREVIOUS_EMPLOYER_ID NUMBER,
OBJECT_VERSION_NUMBER VARCHAR2(150 BYTE),
PREVIOUS_JOB_ID NUMBER,
PREV_JOB_OBJ_VER_NUMBER VARCHAR2(150 BYTE)
)
PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_EXEMPLOYER_INFO
IS
CURSOR C_EXEMP_INFO
IS
SELECT LINE_ID,
EMP_CODE, EMP_NAME, EMPLOYER_NAME, EMPLOYER_ADDRESS,
COUNTRY, EMPLOYER_TYPE, START_DATE, END_DATE, DESIGNATION,
EMPLOYEE_CATEGORY, REASON_FOR_LEAVING, LAST_SALARY_DRAWN,
EXEMP_PROCESS_FLAG, EXEMP_ERROR_DESCRIPTION,
EXJOB_PROCESS_FLAG, EXJOB_ERROR_DESCRIPTION,
PREVIOUS_EMPLOYER_ID,
OBJECT_VERSION_NUMBER, PREVIOUS_JOB_ID,
PREV_JOB_OBJ_VER_NUMBER
FROM DEV_EXEMPLOYER_EXP
WHERE NVL (EXEMP_PROCESS_FLAG, 'N') <> 'Y'
AND EXEMP_ERROR_DESCRIPTION IS NULL
AND START_DATE IS NOT NULL
ORDER BY EMP_CODE, START_DATE;
LC_C_EXEMP_INFO
C_EXEMP_INFO%ROWTYPE;
L_PREVIOUS_EMPLOYER_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_PREVIOUS_JOB_ID NUMBER;
L_PREV_JOB_OBJ_VER_NUMBER NUMBER;
ERROR_DESC_EXEMP VARCHAR2 (240);
ERROR_DESC_EXJOB VARCHAR2 (240);
LV_EXEMP_FLAG CHAR (1);
LV_JOB_FLAG CHAR (1);
L_EFFECTIVE_DATE DATE;
L_PERSON_ID NUMBER;
L_COUNTRY_CODE VARCHAR2 (5);
L_CATEGORY_CODE VARCHAR2 (50);
L_EMPLOYER_TYPE VARCHAR2 (50);
BEGIN
OPEN C_EXEMP_INFO;
LOOP
FETCH C_EXEMP_INFO
INTO LC_C_EXEMP_INFO;
EXIT WHEN C_EXEMP_INFO%NOTFOUND;
BEGIN
----------------------
-- COUNTRY LOOKUP VALUE
----------------------
BEGIN
SELECT LOOKUP_CODE
INTO L_COUNTRY_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'JEES_EURO_COUNTRY_CODES'
AND UPPER (MEANING) = UPPER (TRIM (LC_C_EXEMP_INFO.COUNTRY));
EXCEPTION
WHEN OTHERS
THEN
L_COUNTRY_CODE := '';
END;
---------------------------------
-- EMPLOYEE CATEGORY LOOKUP
VALUE
---------------------------------
BEGIN
SELECT LOOKUP_CODE
INTO L_CATEGORY_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'EMPLOYEE_CATG'
AND UPPER (MEANING) =
UPPER (TRIM (LC_C_EXEMP_INFO.EMPLOYEE_CATEGORY));
EXCEPTION
WHEN OTHERS
THEN
L_CATEGORY_CODE := '';
END;
---------------------------------
-- PREVIOUS EMPLOYER
TYPE LOOKUP VALUE
---------------------------------
BEGIN
SELECT LOOKUP_CODE
INTO L_EMPLOYER_TYPE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'PREV_EMP_TYPE'
AND UPPER (MEANING) =
UPPER (TRIM (LC_C_EXEMP_INFO.EMPLOYER_TYPE));
EXCEPTION
WHEN OTHERS
THEN
L_EMPLOYER_TYPE := '';
END;
----------------------
-- EFFECTIVE DATE
----------------------
SELECT PAPF.EFFECTIVE_START_DATE, PAPF.PERSON_ID
INTO L_EFFECTIVE_DATE, L_PERSON_ID
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.EMPLOYEE_NUMBER = LC_C_EXEMP_INFO.EMP_CODE
AND TO_CHAR (PAPF.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712';
HR_PREVIOUS_EMPLOYMENT_API.CREATE_PREVIOUS_EMPLOYER
(P_VALIDATE => FALSE,
P_BUSINESS_GROUP_ID => 0,
P_EFFECTIVE_DATE => L_EFFECTIVE_DATE,
P_PERSON_ID => L_PERSON_ID,
P_START_DATE => LC_C_EXEMP_INFO.START_DATE,
P_END_DATE => LC_C_EXEMP_INFO.END_DATE,
P_EMPLOYER_NAME => LC_C_EXEMP_INFO.EMPLOYER_NAME,
P_EMPLOYER_ADDRESS => LC_C_EXEMP_INFO.EMPLOYER_ADDRESS,
P_EMPLOYER_COUNTRY => L_COUNTRY_CODE,
P_EMPLOYER_TYPE => L_EMPLOYER_TYPE,
--LC_C_EXEMP_INFO.EMPLOYER_TYPE,
P_PREVIOUS_EMPLOYER_ID => L_PREVIOUS_EMPLOYER_ID,
P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
);
IF L_PREVIOUS_EMPLOYER_ID IS NOT NULL
THEN
LV_EXEMP_FLAG := 'Y';
ERROR_DESC_EXEMP := 'NO ERROR';
END IF;
IF L_PREVIOUS_EMPLOYER_ID IS NOT NULL
THEN
BEGIN
HR_PREVIOUS_EMPLOYMENT_API.CREATE_PREVIOUS_JOB
(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => L_EFFECTIVE_DATE,
P_PREVIOUS_EMPLOYER_ID => L_PREVIOUS_EMPLOYER_ID,
P_START_DATE => LC_C_EXEMP_INFO.START_DATE,
P_END_DATE => LC_C_EXEMP_INFO.END_DATE,
P_JOB_NAME => LC_C_EXEMP_INFO.DESIGNATION,
P_DESCRIPTION => LC_C_EXEMP_INFO.DESIGNATION,
P_EMPLOYMENT_CATEGORY => L_CATEGORY_CODE,
P_PREVIOUS_JOB_ID => L_PREVIOUS_JOB_ID,
P_OBJECT_VERSION_NUMBER => L_PREV_JOB_OBJ_VER_NUMBER
);
IF L_PREVIOUS_JOB_ID IS NOT NULL
THEN
LV_JOB_FLAG := 'Y';
ERROR_DESC_EXJOB := 'NO ERROR';
END IF;
EXCEPTION
WHEN OTHERS
THEN
ERROR_DESC_EXJOB := ERROR_DESC_EXJOB || SQLERRM;
LV_JOB_FLAG := 'N';
UPDATE DEV_EXEMPLOYER_EXP
SET EXJOB_ERROR_DESCRIPTION = ERROR_DESC_EXJOB,
EXJOB_PROCESS_FLAG = LV_JOB_FLAG
WHERE EMP_CODE = LC_C_EXEMP_INFO.EMP_CODE
AND LINE_ID = LC_C_EXEMP_INFO.LINE_ID;
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
END IF;
IF LV_EXEMP_FLAG = 'Y'
THEN
UPDATE DEV_EXEMPLOYER_EXP
SET EXEMP_ERROR_DESCRIPTION = ERROR_DESC_EXEMP,
EXEMP_PROCESS_FLAG = LV_EXEMP_FLAG,
EXJOB_ERROR_DESCRIPTION = ERROR_DESC_EXJOB,
EXJOB_PROCESS_FLAG = DECODE (LV_JOB_FLAG, 'N', 'N', 'Y'),
PREVIOUS_EMPLOYER_ID =
L_PREVIOUS_EMPLOYER_ID,
OBJECT_VERSION_NUMBER =
L_OBJECT_VERSION_NUMBER,
PREVIOUS_JOB_ID = L_PREVIOUS_JOB_ID,
PREV_JOB_OBJ_VER_NUMBER = L_PREV_JOB_OBJ_VER_NUMBER
WHERE EMP_CODE = LC_C_EXEMP_INFO.EMP_CODE
AND LINE_ID = LC_C_EXEMP_INFO.LINE_ID;
ELSE
ERROR_DESC_EXJOB := ERROR_DESC_EXJOB || SQLERRM;
UPDATE DEV_EXEMPLOYER_EXP
SET EXEMP_ERROR_DESCRIPTION = ERROR_DESC_EXEMP,
EXEMP_PROCESS_FLAG = 'N',
EXJOB_ERROR_DESCRIPTION = ERROR_DESC_EXJOB,
EXJOB_PROCESS_FLAG = DECODE (LV_JOB_FLAG, 'N', 'N', 'Y')
WHERE EMP_CODE = LC_C_EXEMP_INFO.EMP_CODE
AND LINE_ID = LC_C_EXEMP_INFO.LINE_ID;
END IF;
EXCEPTION
WHEN OTHERS
THEN
LV_EXEMP_FLAG := 'N';
ERROR_DESC_EXJOB := ERROR_DESC_EXJOB || SQLERRM;
UPDATE DEV_EXEMPLOYER_EXP
SET EXEMP_ERROR_DESCRIPTION = ERROR_DESC_EXEMP,
EXEMP_PROCESS_FLAG = LV_EXEMP_FLAG,
EXJOB_PROCESS_FLAG = 'N'
WHERE EMP_CODE = LC_C_EXEMP_INFO.EMP_CODE
AND LINE_ID = LC_C_EXEMP_INFO.LINE_ID;
DBMS_OUTPUT.PUT_LINE
(SQLERRM);
END;
LV_EXEMP_FLAG := '';
LV_JOB_FLAG := '';
ERROR_DESC_EXEMP := '';
ERROR_DESC_EXJOB := '';
L_EFFECTIVE_DATE := '';
L_PERSON_ID := '';
L_PREVIOUS_EMPLOYER_ID := '';
L_OBJECT_VERSION_NUMBER := '';
L_PREVIOUS_JOB_ID := '';
L_PREV_JOB_OBJ_VER_NUMBER :=
'';
--COMMIT;
END LOOP;
CLOSE C_EXEMP_INFO;
END;
/
Leave a comment for any query.
a love one or just plan it together. The following gift suggestions will really ensure it is simple for you personally and could be the ideal choice for your whole life partner. employee anniversary gift
ReplyDelete