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



/

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...