ORACLE FUSION CLOUD: Employee Conversion R12

Sunday, April 24, 2016

Employee Conversion R12

Create Staging Table

CREATE TABLE XXCUSTOM.XXZA_HR_EMPLOYEE_STG
(
  CURRENT_EMPLOYEE_NUMBER      VARCHAR2(30 BYTE),
  NEW_EMPLOYEE_NUMBER          VARCHAR2(30 BYTE),
  PERSON_TYPE                  VARCHAR2(30 BYTE),
  EMPLOYEE_SURNAME             VARCHAR2(150 BYTE),
  FIRST_NAME                   VARCHAR2(150 BYTE),
  TITLE                        VARCHAR2(30 BYTE),
  MIDDLE_NAME                  VARCHAR2(60 BYTE),
  KNOWN_AS                     VARCHAR2(80 BYTE),
  BIRTH_DATE                   DATE,
  GENDER                       VARCHAR2(30 BYTE),
  ID_NUMBER                    VARCHAR2(80 BYTE),
  TOWN_OF_BIRTH                VARCHAR2(90 BYTE),
  MARITAL_STATUS               VARCHAR2(80 BYTE),
  REGION_OF_BIRTH              VARCHAR2(90 BYTE),
  NATIONALITY                  VARCHAR2(30 BYTE),
  COUNTRY_OF_BIRTH             VARCHAR2(90 BYTE),
  REGISTERED_DISABLED          VARCHAR2(30 BYTE),
  INCOME_TAX_NUMBER            VARCHAR2(150 BYTE),
  SARS_BRANCH                  VARCHAR2(150 BYTE),
  PASSPORT_NUMBER              VARCHAR2(150 BYTE),
  COUNTRY_OF_PASSPORT_ISSUE    VARCHAR2(150 BYTE),
  FOREIGN_NATIONAL             VARCHAR2(150 BYTE),
  WORK_PERMIT_NUMBER           VARCHAR2(150 BYTE),
  WORK_PERMIT_EXPIRY_DATE      DATE,
  DATE_OF_NATRUALIZATION       DATE,
  HOME_LANGUAGE                VARCHAR2(150 BYTE),
  RACE                         VARCHAR2(150 BYTE),
  RELIGION                     VARCHAR2(150 BYTE),
  EMAIL                        VARCHAR2(240 BYTE),
  MAIDEN_NAME                  VARCHAR2(150 BYTE),
  PREVIOUS_LAST_NAME           VARCHAR2(150 BYTE),
  AVAILABILITY_SCHEDULE        VARCHAR2(30 BYTE),
  STUDENT_STATUS               VARCHAR2(30 BYTE),
  DATE_START                   DATE,
  CORRESPONDENCE_LANGUAGE      VARCHAR2(30 BYTE),
  DRIVERS_LICENSE_CODE         VARCHAR2(150 BYTE),
  DRIVERS_LICENCE_NUMBER       VARCHAR2(150 BYTE),
  DRIVING_LICENSE_EXPIRY_DATE  DATE,
  SECOND_PASSPORT_NUMBER       VARCHAR2(150 BYTE),
  SECOND_PP_COUNTRY_OF_ISSUE   VARCHAR2(150 BYTE),
  ARM_JOIN_DATE                DATE,
  PHONE_TYPE                   VARCHAR2(60 BYTE),
  PHONE_NUMBER                 VARCHAR2(60 BYTE),
  FROM_DATE                    DATE,
  DELIVERY_METHOD              VARCHAR2(60 BYTE),
  START_DATE                   DATE,
  END_DATE                     DATE,
  STATUS_FLAG                  VARCHAR2(30 BYTE),
  ERROR_MSG                    VARCHAR2(4000 BYTE)
);

CREATE OR REPLACE PACKAGE APPS.XXZA_HR_EMPLOYEE_MIGRATION_PKG
AS

--#######################################################################################--
--# SYSTEM: #--
--# MODULE: HRMS #--
--# PURPOSE: This script is used to Upload Employees data #--
--# #--
--# API NAME: XXZA_HR_EMPLOYEE_MIGRATION_PKG.MAIN #--
--# REFERENCE: Migration Document #--
--# #--
--# #--
--# Date Ver Author Reason - CR/Bug Reference #--
--# -------- --- ------ -------------------------- #--
--# 07/02/2012 1.0 Mamidi Initial Version #--
--#######################################################################################--

PROCEDURE MAIN(
ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2
,P_BUSINEES_GROUP_ID IN NUMBER
,P_EFFECTIVE_DATE IN VARCHAR2
);
END XXZA_HR_EMPLOYEE_MIGRATION_PKG;
/

CREATE OR REPLACE PACKAGE BODY APPS.XXZA_HR_EMPLOYEE_MIGRATION_PKG
AS
--#######################################################################################--
--# SYSTEM: #--
--# MODULE: HRMS #--
--# PURPOSE: This script is used to Upload Employees data #--
--# #--
--# API NAME: XXZA_HR_EMPLOYEE_MIGRATION_PKG.MAIN #--
--# REFERENCE: Migration Document #--
--# #--
--# #--
--# Date Ver Author Reason - CR/Bug Reference #--
--# -------- --- ------ -------------------------- #--
--# 07/02/2012 1.0 Mamidi Initial Version #--
--#######################################################################################--

PROCEDURE MAIN(
ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2
,P_BUSINEES_GROUP_ID IN NUMBER
,P_EFFECTIVE_DATE IN VARCHAR2
)
AS

--Loc
al variables
V_STATUS_FLAG VARCHAR2(30);
V_ERROR_MSG VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(30);
V_BUSINESS_GROUP_ID NUMBER;
V_GENDER VARCHAR2(30);
V_PERSON_TYPE_ID NUMBER;
V_MARITAL_STATUS VARCHAR2(80);
V_NATIONALITY VARCHAR2(30);
V_TITLE VARCHAR2(30);
V_DRIVERS_LICENSE_CODE VARCHAR2(30);
V_SECOND_PP_COUNTRY_OF_ISSUE VARCHAR2(60);
V_SARS_BRANCH_TYPE VARCHAR2(150);
V_STUDENT_STATUS VARCHAR2(30);
V_AVAILABILITY_SCHEDULE VARCHAR2(30);
V_COUNTRY_OF_BIRTH VARCHAR2(90);
V_SARS_BRANCH VARCHAR2(150);
V_COUNTRY_OF_PASSPORT_ISSUE VARCHAR2(150);
V_FOREIGN_NATIONAL VARCHAR2(150);
V_HOME_LANGUAGE VARCHAR2(150);
V_RACE VARCHAR2(150);
V_RELIGION VARCHAR2(150);
V_DELIVERY_METHOD VARCHAR2(60);
V_EFFECTIVE_DATE DATE;
V_EFFECTIVE_DATE_1 DATE;
V_HIRE_DATE DATE;
V_SUCCESS_RECORDS NUMBER;
V_FAILURE_RECORDS NUMBER;
V_TOTAL_RECORDS NUMBER;
V_REGISTERED_DISABLED VARCHAR2(10);
V_CORRESPONDENCE_LANGUAGE VARCHAR2(30);
V_ADJUSTED_SVC_DATE DATE;
V_EMPLOYEE_NUMBER VARCHAR2(30);
V_NATIONAL_IDENTIFIER VARCHAR2(30);
V_PHONE_TYPE VARCHAR2(30);

X_PERSON_ID NUMBER;
X_ASSIGNMENT_ID NUMBER;
X_PER_OBJECT_VERSION_NUMBER NUMBER;
X_ASG_OBJECT_VERSION_NUMBER NUMBER;
X_PER_EFFECTIVE_START_DATE DATE;
X_PER_EFFECTIVE_END_DATE DATE;
X_FULL_NAME VARCHAR2(240);
X_PER_COMMENT_ID NUMBER;
X_ASSIGNMENT_SEQUENCE NUMBER;
X_ASSIGNMENT_NUMBER VARCHAR2(30);
X_NAME_COMBINATION_WARNING BOOLEAN;
X_ASSIGN_PAYROLL_WARNING BOOLEAN;
X_ORIG_HIRE_WARNING BOOLEAN;
X_OBJECT_VERSION_NUMBER2 NUMBER;
X_PHONE_ID NUMBER;
X_DELIVERY_METHOD_ID NUMBER;
X_OBJECT_VERSION_NUMBER NUMBER;

--Cusrsor to fetch the data from Staging table

CURSOR C1 IS
SELECT ROWID,EMP.*
FROM XXZA_HR_EMPLOYEE_STG EMP
WHERE STATUS_FLAG IS NULL;

CURSOR cur_emp_num(cp_employee_number IN varchar2,
cp_business_group_id IN NUMBER) IS
SELECT papf.employee_number
FROM per_all_people_f papf
WHERE UPPER(TRIM(papf.employee_number)) = UPPER(TRIM(cp_employee_number))
AND papf.business_group_id = cp_business_group_id
AND papf.current_employee_flag = 'Y'
AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND effective_end_date;

CURSOR cur_national_ID(cp_national_identifier IN varchar2,
cp_business_group_id IN NUMBER) IS
SELECT papf.NATIONAL_IDENTIFIER
FROM per_all_people_f papf
WHERE UPPER(TRIM(papf.NATIONAL_IDENTIFIER)) = UPPER(TRIM(cp_national_identifier))
AND papf.business_group_id = cp_business_group_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND effective_end_date;

BEGIN

APPS.FND_GLOBAL.APPS_INITIALIZE
(USER_ID => APPS.FND_GLOBAL.USER_ID,
RESP_ID =>APPS.FND_GLOBAL.RESP_ID,
RESP_APPL_ID => APPS.FND_GLOBAL.RESP_APPL_ID
);

FND_FILE.PUT_LINE(FND_FILE.LOG,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Data Migration Of Employees :');
FND_FILE.PUT_LINE(FND_FILE.LOG,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
V_SUCCESS_RECORDS :=0;
V_FAILURE_RECORDS :=0;
V_TOTAL_RECORDS :=0;
V_BUSINESS_GROUP_ID :=P_BUSINEES_GROUP_ID;
V_EFFECTIVE_DATE :=TO_DATE(P_EFFECTIVE_DATE,'RRRR/MM/DD SS:MI:HH24');
-- V_EFFECTIVE_DATE_1 := TO_DATE(P_EFFECTIVE_DATE,'DD-MON-YYYY');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Effective date 1 : ' || V_EFFECTIVE_DATE_1);

FOR I IN C1 LOOP

V_ERROR_FLAG :='Y';
V_STATUS_FLAG :='S' ;
V_ERROR_MSG :='';
V_GENDER := NULL;
V_PERSON_TYPE_ID := NULL;
V_MARITAL_STATUS := NULL;
V_NATIONALITY := NULL;
V_TITLE := NULL;
V_DRIVERS_LICENSE_CODE := NULL;
V_SECOND_PP_COUNTRY_OF_ISSUE := NULL;
V_SARS_BRANCH_TYPE := NULL;
V_STUDENT_STATUS := NULL;
V_AVAILABILITY_SCHEDULE := NULL;
V_COUNTRY_OF_BIRTH := NULL;
V_SARS_BRANCH := NULL;
V_COUNTRY_OF_PASSPORT_ISSUE := NULL;
V_FOREIGN_NATIONAL := NULL;
V_HOME_LANGUAGE := NULL;
V_RACE := NULL;
V_RELIGION := NULL;
V_DELIVERY_METHOD := NULL;
V_REGISTERED_DISABLED := NULL;
V_CORRESPONDENCE_LANGUAGE := NULL;
V_HIRE_DATE := NULL;
V_ADJUSTED_SVC_DATE := NULL;
V_PHONE_TYPE := NULL;

X_PERSON_ID := NULL;
X_ASSIGNMENT_ID := NULL;
X_PER_OBJECT_VERSION_NUMBER := NULL;
X_ASG_OBJECT_VERSION_NUMBER := NULL;
X_PER_EFFECTIVE_START_DATE := NULL;
X_PER_EFFECTIVE_END_DATE := NULL;
X_FULL_NAME := NULL;
X_PER_COMMENT_ID := NULL;
X_ASSIGNMENT_SEQUENCE := NULL;
X_ASSIGNMENT_NUMBER := NULL;
X_NAME_COMBINATION_WARNING := NULL;
X_ASSIGN_PAYROLL_WARNING := NULL;
X_ORIG_HIRE_WARNING := NULL;
X_OBJECT_VERSION_NUMBER2 := NULL;
X_PHONE_ID := NULL;
X_DELIVERY_METHOD_ID := NULL;
X_OBJECT_VERSION_NUMBER := NULL;

V_TOTAL_RECORDS :=V_TOTAL_RECORDS+1;

-- Checking for Employee Number Already exists are not

--Validation for NEW Employee Number
IF I.NEW_EMPLOYEE_NUMBER IS NULL THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'New Employee Number is Null; ';
END IF;

IF I.NEW_EMPLOYEE_NUMBER IS NOT NULL THEN
OPEN cur_emp_num(I.NEW_EMPLOYEE_NUMBER,V_BUSINESS_GROUP_ID);
FETCH cur_emp_num INTO v_employee_number;
IF (cur_emp_num%FOUND) THEN
V_ERROR_FLAG := 'N';
V_STATUS_FLAG := 'E';
V_ERROR_MSG :=v_error_msg||'Employee Number is already exists in system; ';
END IF;
CLOSE cur_emp_num;
END IF;

-- Validation for National Identification Number

IF I.ID_NUMBER IS NOT NULL THEN
OPEN cur_national_ID(I.ID_NUMBER,V_BUSINESS_GROUP_ID);
FETCH cur_national_ID INTO v_national_identifier;
IF (cur_national_ID%FOUND) THEN
V_ERROR_FLAG := 'N';
V_STATUS_FLAG := 'E';
V_ERROR_MSG :=v_error_msg||'National Identifier is already exists in system; ';
END IF;
CLOSE cur_national_ID;
END IF;

--Validation for Employee_Surname

IF I.EMPLOYEE_SURNAME IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='S';
V_ERROR_MSG:=V_ERROR_MSG||'Employee Surname is Null; ';

END IF;

--Validation For Gender
IF I.GENDER IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Employee Surname Null; ';

ELSIF I.GENDER IS NOT NULL THEN

BEGIN

SELECT
LOOKUP_CODE
INTO V_GENDER
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='SEX'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.GENDER));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Employee Gender is not valid; ';

END;

END IF;

--Validation for Person_type
IF I.PERSON_TYPE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Person Type is Null; ';

ELSIF I.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(I.PERSON_TYPE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Person Type is not valid; ';
END;
END IF;

--Validation for Birth Date
IF I.BIRTH_DATE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Birth Date is Null; ';
END IF;

--Validation for EMail
IF I.EMAIL IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Email is Null; ';
END IF;

--Validation for Current Employee Number
IF I.CURRENT_EMPLOYEE_NUMBER IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Current Employee Number is Null; ';
END IF;

--Validation for First Name
IF I.FIRST_NAME IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'First Name is Null; ';
END IF;

--Validation for Marital Status
IF I.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(I.MARITAL_STATUS));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Marital Status is not valid; ';

END;

END IF;

--Validation for Nationality
IF I.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(I.NATIONALITY));
--AND UPPER(TRIM(MEANING))=UPPER(TRIM('South African'));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Nationality is not valid; ';

END;

END IF;

--Validation for Title

IF I.TITLE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Title is Null; ';

--END IF;
ELSIF I.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(I.TITLE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Title is not valid; ';

END;

END IF;

--Validation for Drivers License Code
IF I.DRIVERS_LICENSE_CODE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Drivers License Code is Null; ';

ELSIF I.DRIVERS_LICENSE_CODE IS NOT NULL THEN

BEGIN

SELECT FLEX_VALUE
INTO V_DRIVERS_LICENSE_CODE
FROM
FND_FLEX_VALUE_SETS FS,
FND_FLEX_VALUES FV
WHERE
FS.FLEX_VALUE_SET_ID=FV.FLEX_VALUE_SET_ID
AND FLEX_VALUE_SET_NAME='ARM_DRIVERS_LICENCE_CODE'
AND UPPER(TRIM(FLEX_VALUE))=UPPER(TRIM(I.DRIVERS_LICENSE_CODE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Drivers Licence Code is not valid; ';

END;

END IF;

--Validation for Second Passport Issued Country
IF I.SECOND_PP_COUNTRY_OF_ISSUE IS NOT NULL THEN

BEGIN

SELECT
LOOKUP_CODE
INTO V_SECOND_PP_COUNTRY_OF_ISSUE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='ZA_COUNTRY_OF_PASSPORT_ISSUE'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.SECOND_PP_COUNTRY_OF_ISSUE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Second Passport issued Country is not Valid; ';

END;

END IF;

/*--Validation for SARS Branch Type
IF I.SARS_BRANCH_TYPE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'SARS Branch Type is Null; ';

ELSIF I.SARS_BRANCH_TYPE IS NOT NULL THEN

BEGIN

SELECT FLEX_VALUE
INTO V_SARS_BRANCH_TYPE
FROM
FND_FLEX_VALUE_SETS FS,
FND_FLEX_VALUES FV
WHERE
FS.FLEX_VALUE_SET_ID=FV.FLEX_VALUE_SET_ID
AND FLEX_VALUE_SET_NAME='ARM_SARS_BRANCH'
AND UPPER(TRIM(FLEX_VALUE))=UPPER(TRIM(I.SARS_BRANCH_TYPE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'SARS Branch Type is not valid; ';

END;

END IF;
*/

--Validation For Student Status
IF I.STUDENT_STATUS IS NOT NULL THEN

BEGIN

SELECT
LOOKUP_CODE
INTO V_STUDENT_STATUS
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='STUDENT_STATUS'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.STUDENT_STATUS));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Student Status is not valid; ';

END;

END IF;

--Validation for Availability Schedule
IF I.AVAILABILITY_SCHEDULE IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_AVAILABILITY_SCHEDULE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='WORK_SCHEDULE'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.AVAILABILITY_SCHEDULE))
AND ENABLED_FLAG='Y';

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Availability Schedule is not valid; ';

END;
END IF;

--Validation for Country
IF I.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(I.COUNTRY_OF_BIRTH));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Country of Birth is not valid; ';
END;

END IF;

--Validation For SARS Branch
IF I.SARS_BRANCH IS NOT NULL THEN

BEGIN
SELECT FLEX_VALUE
INTO V_SARS_BRANCH
FROM
FND_FLEX_VALUE_SETS FS,
FND_FLEX_VALUES FV
WHERE
FS.FLEX_VALUE_SET_ID=FV.FLEX_VALUE_SET_ID
AND FLEX_VALUE_SET_NAME='ARM_SARS_BRANCH'
AND UPPER(TRIM(FLEX_VALUE))=UPPER(TRIM(I.SARS_BRANCH));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'SARS Branch is not valid; ';

END;

END IF;

--Validation for Country Of Passport Issue
IF I.COUNTRY_OF_PASSPORT_ISSUE IS NOT NULL THEN

BEGIN

SELECT
LOOKUP_CODE
INTO V_COUNTRY_OF_PASSPORT_ISSUE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='ZA_COUNTRY_OF_PASSPORT_ISSUE'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.COUNTRY_OF_PASSPORT_ISSUE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Country Of Passport Country is not Valid; ';

END;

END IF;

--Validation For Foreign National
IF I.FOREIGN_NATIONAL IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_FOREIGN_NATIONAL
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='YES_NO'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.FOREIGN_NATIONAL));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Foreign National is not Valid; ';
END;
END IF;

--Validation for Home Language

IF I.HOME_LANGUAGE IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_HOME_LANGUAGE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='ZA_LANG_PREF'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.HOME_LANGUAGE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Home Language is not Valid; ';

END;

END IF;

--Validation for Race
IF I.RACE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Race is Null; ';

ELSIF I.RACE IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_RACE
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='ZA_RACE'
AND ENABLED_FLAG='Y'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.RACE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Race is not Valid; ';

END;
END IF;

--Validation for Religion
IF I.RELIGION IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_RELIGION
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='ZA_RELIGION'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.RELIGION));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Religion is not Valid; ';

END;
END IF;

--Validation For From Date
IF I.FROM_DATE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'From Date is Null; ';

END IF;

--Validation For Phone Number
IF I.PHONE_NUMBER IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Phone Number is Null; ';

END IF;

--Validation for Start Date
IF I.START_DATE IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Start Date is Null; ';

END IF;

--Validation For Delivery Method
IF I.DELIVERY_METHOD IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Delivery Method is Null; ';

ELSIF I.DELIVERY_METHOD IS NOT NULL THEN

BEGIN

SELECT
LOOKUP_CODE
INTO V_DELIVERY_METHOD
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='PER_CM_MTHD'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.DELIVERY_METHOD));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Delivery Method is not Valid; ';

END;

END IF;

--Validation For ID Number
IF I.ID_NUMBER IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'ID Number is Null; ';

END IF;

--Validation for Register Disabled
IF I.REGISTERED_DISABLED IS NOT NULL THEN

BEGIN
SELECT
LOOKUP_CODE
INTO V_REGISTERED_DISABLED
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE ='REGISTERED_DISABLED'
AND UPPER(TRIM(MEANING))=UPPER(TRIM(I.REGISTERED_DISABLED));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Register Disabled is not Valid; ';

END;
END IF;

--Validation For EMail
IF I.EMAIL IS NULL THEN

V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'EMail is Null; ';

END IF;

--Validation for Correspondence language
IF I.CORRESPONDENCE_LANGUAGE IS NOT NULL THEN

BEGIN
SELECT
LANGUAGE_CODE
INTO V_CORRESPONDENCE_LANGUAGE
FROM FND_LANGUAGES_VL
WHERE UPPER(TRIM(DESCRIPTION))=UPPER(TRIM(I.CORRESPONDENCE_LANGUAGE));

EXCEPTION WHEN OTHERS THEN
V_ERROR_FLAG:='N';
V_STATUS_FLAG:='E';
V_ERROR_MSG:=V_ERROR_MSG||'Corresponence Language is not Valid; ';

END;
END IF;

/* Validating Phone Type. */
IF (I.phone_type IS NULL) THEN
V_ERROR_MSG := V_ERROR_MSG|| ' Phone Type Is not null.';
V_STATUS_FLAG := 'E';
V_ERROR_FLAG :='N';
ELSE
v_phone_type :=XXZA_HRMS_GENERAL_PKG.XXZA_GET_LOOKUP_CODE('PHONE_TYPE',I.phone_type);
IF (v_phone_type IS NULL) THEN
V_ERROR_MSG := V_ERROR_MSG|| ' Invalid Phone Type . ';
V_STATUS_FLAG := 'E';
V_ERROR_FLAG :='N';
END IF;
END IF;

--Checking the Condition for V_ERROR_FLAG='N'

IF V_ERROR_FLAG='N' THEN

UPDATE XXZA_HR_EMPLOYEE_STG
SET STATUS_FLAG=V_STATUS_FLAG
,ERROR_MSG=V_ERROR_MSG
WHERE ROWID=I.ROWID;

V_FAILURE_RECORDS :=V_FAILURE_RECORDS+1;

COMMIT;
END IF;

--Checking the Condition for V_ERROR_FLAG='Y'

IF V_ERROR_FLAG ='Y' THEN

IF V_EFFECTIVE_DATE IS NULL THEN
V_HIRE_DATE := I.DATE_START; --to_char(I.DATE_START,'dd-mon-yyyy');
V_ADJUSTED_SVC_DATE := NULL;
ELSE
V_HIRE_DATE := to_date(V_EFFECTIVE_DATE,'DD-MON-RRRR'); --P_EFFECTIVE_DATE; --TO_DATE(P_EFFECTIVE_DATE,'DD-MON-YYYY'); -- V_EFFECTIVE_DATE;
V_ADJUSTED_SVC_DATE := I.DATE_START;
END IF;

BEGIN

--Calling API to Insert Employee Records
HR_EMPLOYEE_API.CREATE_EMPLOYEE
(p_validate =>FALSE
,p_hire_date =>V_HIRE_DATE --I.DATE_START
,p_business_group_id =>V_BUSINESS_GROUP_ID
,p_last_name =>I.EMPLOYEE_SURNAME
,p_sex =>V_GENDER
,p_person_type_id =>V_PERSON_TYPE_ID
,p_date_of_birth =>I.BIRTH_DATE
,p_email_address =>I.EMAIL
,p_employee_number =>I.NEW_EMPLOYEE_NUMBER
,p_first_name =>I.FIRST_NAME
,p_known_as =>I.KNOWN_AS
,p_marital_status =>V_MARITAL_STATUS
,p_middle_names =>I.MIDDLE_NAME
,p_nationality =>V_NATIONALITY
,p_national_identifier =>I.ID_NUMBER
,p_previous_last_name =>I.PREVIOUS_LAST_NAME
,p_registered_disabled_flag =>V_REGISTERED_DISABLED
,p_title =>V_TITLE
,p_attribute_category =>'IRON_ORE_ADD_PER_DETAILS'
,p_attribute1 =>V_DRIVERS_LICENSE_CODE
,p_attribute2 =>I.DRIVERS_LICENCE_NUMBER
,p_attribute4 =>I.SECOND_PASSPORT_NUMBER
,p_attribute5 =>V_SECOND_PP_COUNTRY_OF_ISSUE
,p_attribute6 =>TO_CHAR(I.ARM_JOIN_DATE,'RRRR/MM/DD')
,p_attribute7 =>V_SARS_BRANCH
,p_attribute8 =>TO_CHAR(I.Driving_license_expiry_date,'RRRR/MM/DD')
,p_per_information_category =>'ZA'
,p_per_information1 =>I.INCOME_TAX_NUMBER
,p_per_information2 =>I.PASSPORT_NUMBER
,p_per_information3 =>I.WORK_PERMIT_NUMBER
,p_per_information4 =>V_RACE
,p_per_information6 =>V_HOME_LANGUAGE
,p_per_information7 =>V_RELIGION
,p_per_information8 =>TO_CHAR(I.WORK_PERMIT_EXPIRY_DATE,'RRRR/MM/DD')
,p_per_information9 =>V_FOREIGN_NATIONAL
,p_per_information10 =>V_COUNTRY_OF_PASSPORT_ISSUE
,p_per_information11 =>TO_CHAR(I.DATE_OF_NATRUALIZATION,'RRRR/MM/DD')
--,p_per_information15 =>V_SARS_BRANCH
,p_original_date_of_hire =>I.DATE_START --V_HIRE_DATE --I.DATE_START
,p_correspondence_language =>V_CORRESPONDENCE_LANGUAGE
,p_honors =>I.MAIDEN_NAME
,p_student_status =>V_STUDENT_STATUS
,p_work_schedule =>V_AVAILABILITY_SCHEDULE
,p_town_of_birth =>I.TOWN_OF_BIRTH
,p_region_of_birth =>I.REGION_OF_BIRTH
,p_country_of_birth =>V_COUNTRY_OF_BIRTH
,p_adjusted_svc_date => V_ADJUSTED_SVC_DATE
,p_person_id =>X_PERSON_ID
,p_assignment_id =>X_ASSIGNMENT_ID
,p_per_object_version_number =>X_PER_OBJECT_VERSION_NUMBER
,p_asg_object_version_number =>X_ASG_OBJECT_VERSION_NUMBER
,p_per_effective_start_date =>X_PER_EFFECTIVE_START_DATE
,p_per_effective_end_date =>X_PER_EFFECTIVE_END_DATE
,p_full_name =>X_FULL_NAME
,p_per_comment_id =>X_PER_COMMENT_ID
,p_assignment_sequence =>X_ASSIGNMENT_SEQUENCE
,p_assignment_number =>X_ASSIGNMENT_NUMBER
,p_name_combination_warning =>X_NAME_COMBINATION_WARNING
,p_assign_payroll_warning =>X_ASSIGN_PAYROLL_WARNING
,p_orig_hire_warning =>X_ORIG_HIRE_WARNING
);

--Calling API to Insert Employee Phone numbers
HR_PHONE_API.CREATE_PHONE
(
p_date_from =>I.FROM_DATE,
p_phone_type => v_phone_type, --'M',
p_phone_number =>I.PHONE_NUMBER,
p_parent_id =>X_PERSON_ID,
p_parent_table =>'PER_ALL_PEOPLE_F',
p_validate =>FALSE,
p_effective_date =>I.FROM_DATE,--V_HIRE_DATE, --I.DATE_START,
p_object_version_number =>X_OBJECT_VERSION_NUMBER2,
p_phone_id =>X_PHONE_ID
);

--Calling API to Insert Delivery Method Details
HR_DELIVERY_METHODS_API.CREATE_DELIVERY_METHOD
(
p_validate =>FALSE
,p_effective_date =>I.FROM_DATE --V_HIRE_DATE --I.DATE_START
,p_person_id =>X_PERSON_ID
,p_comm_dlvry_method =>V_DELIVERY_METHOD
,p_date_start =>I.FROM_DATE
,p_delivery_method_id =>X_DELIVERY_METHOD_ID
,p_object_version_number =>X_OBJECT_VERSION_NUMBER
);

UPDATE XXZA_HR_EMPLOYEE_STG
SET STATUS_FLAG='S'
,ERROR_MSG=''
WHERE ROWID=I.ROWID;

V_SUCCESS_RECORDS :=V_SUCCESS_RECORDS+1;

COMMIT;

EXCEPTION WHEN OTHERS THEN

ROLLBACK;

V_STATUS_FLAG:='API E';
V_ERROR_MSG:=SUBSTR(SQLERRM,1,2000);

UPDATE XXZA_HR_EMPLOYEE_STG
SET STATUS_FLAG=V_STATUS_FLAG
,ERROR_MSG=V_ERROR_MSG
WHERE ROWID=I.ROWID;

V_FAILURE_RECORDS :=V_FAILURE_RECORDS+1;

COMMIT;

END;

COMMIT;

END IF;

END LOOP;

FND_FILE.PUT_LINE(FND_FILE.LOG,'End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FND_FILE.PUT_LINE(FND_FILE.LOG,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Records To Be Loaded : ' || v_total_records);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Success Records : ' || v_success_records);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Failure Records : ' || v_failure_records);
FND_FILE.PUT_LINE(FND_FILE.LOG,'#############################################################');

END MAIN;

END XXZA_HR_EMPLOYEE_MIGRATION_PKG;

Executable Name:::::XXZA_HR_EMPLOYEE_MIGRATION_PKG

Program Name::::: Assmang Employee Migration Prog

request group::::ZA HRMS Reports & Processes

Responsibility Name::ZA HRMS Manager

ORACLE FUSION CLOUD

Sunday, April 24, 2016

Employee Conversion R12

No comments:

Post a Comment

Drilldown from GL to Inventory Transactions, Inv to gl Link Query R12,Inventory to General ledger links, r12 ,Inv to gl drill down

Search This Blog