Employee Update Script

DECLARE
  v_comment_id            NUMBER;
  v_blood_grp             VARCHAR2(10);
  v_object_version_number NUMBER;
  v_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
  v_asg_eff_start_date DATE;
  v_business_group_id NUMBER := 81;
  v_effective_start_date DATE;
  v_effective_end_date DATE;
  v_row_count               NUMBER := 0;
  v_err_count               NUMBER := 0;
  v_error_msg1              VARCHAR2(240);
  v_full_name               VARCHAR2(240);
  v_no_managers_warning     BOOLEAN;
  v_other_manager_warning   BOOLEAN;
  v_hourly_salaried_warning BOOLEAN;
  v_employee_number VARCHAR2(30);
  v_nationality      VARCHAR2(240);
BEGIN
  FOR c_rec IN
  (SELECT t.rowid row_id,
    t.*
  FROM XXARJ_HR_CNV_PEOPLE_TMP t
  WHERE NVL(PROCESS_FLAG,'N') IN ('N','E')
  --AND employee_number   IN ('P1002039')
  )
  LOOP
    v_row_count               := v_row_count +1;
    v_person_id               := NULL;
    v_comment_id              := NULL;
    v_error_msg1              := NULL;
    v_object_version_number   := NULL;
    v_asg_eff_start_date      :=NULL;
    v_effective_start_date    :=NULL;
    v_effective_end_date      :=NULL;
    v_no_managers_warning     :=NULL;
    v_other_manager_warning   := NULL;
    v_full_name               := NULL;
    v_hourly_salaried_warning :=NULL;
    v_employee_number         := NULL;
    v_nationality             :=null;
    BEGIN
      SELECT ppf.person_id, ppf.employee_number,
        ppf.effective_start_date,
        ppf.object_version_number
      INTO v_person_id,v_employee_number,
        v_asg_eff_start_date,
        v_object_version_number
      FROM PER_ALL_PEOPLE_F ppf
      WHERE ppf.employee_number     = c_rec.employee_number
      AND ppf.business_group_id     = 81
      AND ppf.current_employee_flag = 'Y'
      AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
    EXCEPTION
    WHEN OTHERS THEN
      v_error_msg1 := ' Employee Not Found.';
      v_err_count  := v_err_count +1;
      UPDATE XXARJ_HR_CNV_PEOPLE_TMP
      SET ERROR_MESSAGE = v_error_msg1,
        PROCESS_FLAG          = 'E',
        CREATION_DATE   = TRUNC(SYSDATE)
      WHERE rowid       = c_rec.row_id;
    END;
    IF v_error_msg1 IS NULL THEN
   
      BEGIN
            --Validate Nationality
            SELECT lookup_code
              INTO v_nationality
              FROM apps.hr_lookups
             WHERE upper(lookup_type) = 'NATIONALITY'
               AND upper(meaning)          = upper(c_rec.nationality)
               AND enabled_flag  = 'Y'
               AND NVL(end_date_active,SYSDATE+1)>SYSDATE;
          EXCEPTION
          WHEN OTHERS THEN
            v_error_msg1 := v_error_msg1||' Invalid Nationality.';
          END;
     /* BEGIN
        --Find PAN Number
        SELECT lookup_code
        INTO v_blood_grp
        FROM hr_lookups
        WHERE upper(lookup_type) = 'BLOOD_TYPE'
        AND lookup_code          = c_rec.Blood_type;
      EXCEPTION
      WHEN OTHERS THEN
        v_error_msg1 := 'Invalid Blood Group.';
      END;*/
      BEGIN
        -- ADDRESS API START
        apps.HR_PERSON_API.update_person(
        p_validate => false
        ,p_effective_date => v_asg_eff_start_date
        ,p_datetrack_update_mode => 'CORRECTION'
        ,p_object_version_number => v_object_version_number
        ,p_email_address => c_rec.email_address
        ,p_person_id => v_person_id
        ,p_marital_status  =>c_rec.marital_status
        ,p_nationality  =>v_nationality  --c_rec.nationality
         ,p_country_of_birth  =>replace(c_rec.country_of_birth,'India','IN')
        ,p_employee_number =>v_employee_number
        ,p_per_information4  =>c_rec.PAN_NUMBER
        ,p_per_information7 => c_rec.residential_status
        ,p_per_information8  =>c_rec.PF_NUMBER
        ,p_comment_id => v_comment_id
        ,p_effective_start_date => v_effective_start_date
        ,p_effective_end_date => v_effective_end_date
        ,p_full_name => v_full_name
        ,p_name_combination_warning => v_no_managers_warning
        ,p_assign_payroll_warning => v_other_manager_warning
        ,p_orig_hire_warning =>v_hourly_salaried_warning
        );
        --TRACE1('ADDRESS UPDATED'||V_ADDRESS_ID);
        UPDATE XXARJ_HR_CNV_PEOPLE_TMP
        SET ERROR_MESSAGE = v_error_msg1,
          PROCESS_FLAG          = 'S',
          CREATION_DATE   = TRUNC(SYSDATE)
        WHERE rowid       = c_rec.row_id;
      EXCEPTION
      WHEN OTHERS THEN
        v_error_msg1 := SUBSTR(SQLERRM,1,200);
        DBMS_OUTPUT.PUT_LINE('Total Records errored out: '||SQLERRM);
        v_err_count  := v_err_count +1;
        UPDATE XXARJ_HR_CNV_PEOPLE_TMP
        SET ERROR_MESSAGE = v_error_msg1,
          PROCESS_FLAG          = 'E',
          CREATION_DATE   = TRUNC(SYSDATE)
        WHERE rowid       = c_rec.row_id;
      END;
    ELSE
      v_err_count := v_err_count +1;
      UPDATE XXARJ_HR_CNV_PEOPLE_TMP
      SET ERROR_MESSAGE = v_error_msg1,
        PROCESS_FLAG          = 'E',
        CREATION_DATE   = TRUNC(SYSDATE)
      WHERE rowid       = c_rec.row_id;
    END IF;
    IF MOD(v_row_count,20) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('************************************************************');
  DBMS_OUTPUT.PUT_LINE('Total Records to be loaded: '||v_row_count);
  DBMS_OUTPUT.PUT_LINE('Total Records errored out: '||v_err_count);
  DBMS_OUTPUT.PUT_LINE('************************************************************');
END;