API for Employee Competency Upload

CREATE OR REPLACE PROCEDURE APPS.xx_add_compt_element_prc (
   errbuf    OUT   VARCHAR2,
   retcode   OUT   NUMBER
)
IS
   l_competence_element_id        NUMBER (20);
   l_comp_object_version_number   NUMBER;
   lv_person_id                   NUMBER (20);
   lv_business_group_id           NUMBER (20);
   l_competence_id                NUMBER (20);
   status                         VARCHAR2 (50);
   error                          VARCHAR2 (500);
   lv_rating_level_id             NUMBER (20);
   l_competence_name              VARCHAR2 (200);
   l_total_records                NUMBER (20)    := 0;
   l_error_rec_cnt                NUMBER (20)    := 0;
   l_total_succ_records           NUMBER (20)    := 0;

   CURSOR comp_cur
   IS
      SELECT TRIM (employee_number) employee_number,
             TRIM (competency_name) competency_name,
             TRIM (proficiency_level) proficiency_level,
             TRIM (date_from) date_from, TRIM (date_to) date_to
        FROM xx_comp_prf_stg_tab
       WHERE v_status IS NULL OR v_status = 'E';

BEGIN

   DBMS_OUTPUT.put_line
            ('##############################################################');
   DBMS_OUTPUT.put_line ('DATA INTERFACE FOR COMPETENCE ELEMENT');
   DBMS_OUTPUT.put_line
            ('##############################################################');
   fnd_file.put_line (fnd_file.LOG,
                         'Start Time : '
                      || TO_CHAR (SYSDATE, 'DD-MON-YYYY     HH:MI:SS')
                     );
   FOR comp_ptr IN comp_cur
   LOOP
      l_total_records := l_total_records + 1;
      status := 'S';
      error := '';
      DBMS_OUTPUT.put_line ('Inside Loop');

      BEGIN
         SELECT person_id, business_group_id
           INTO lv_person_id, lv_business_group_id
           FROM per_all_people_f
          WHERE employee_number = TRUNC (comp_ptr.employee_number)
            AND TO_DATE('31-OCT-2010') BETWEEN effective_start_date AND effective_end_date;
         DBMS_OUTPUT.put_line ('person_id : ' || lv_person_id);
         DBMS_OUTPUT.put_line ('business_group_id : ' || lv_business_group_id);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_rec_cnt := l_error_rec_cnt + 1;
            status := 'E';
            error := 'Error Occured at Person_ID' || SUBSTR (SQLERRM, 1, 200);
            DBMS_OUTPUT.put_line
                               (   'Error at Person_id and Business_group : '
                                || SUBSTR (SQLERRM, 2, 250)
                               );
      END;

     IF status = 'S'
      THEN
         BEGIN
            SELECT competence_id
              INTO l_competence_id
              FROM per_competences
             WHERE NAME = comp_ptr.competency_name;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_rec_cnt := l_error_rec_cnt + 1;
               status := 'E';
               error :=
                    'Error Occured competence_id' || SUBSTR (SQLERRM, 1, 200);
               DBMS_OUTPUT.put_line ('competence id error:');
         END;
         DBMS_OUTPUT.put_line ('Competence_id : ' || l_competence_id);
      END IF;

      IF status = 'S'
      THEN
         BEGIN
            SELECT rating_level_id
              INTO lv_rating_level_id
              FROM per_rating_levels
             WHERE step_value = comp_ptr.proficiency_level
               AND rating_scale_id = '1001'
               AND business_group_id = 81;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_rec_cnt := l_error_rec_cnt + 1;
               status := 'E';
               error :=
                  'Error Occured at rating level '
                  || SUBSTR (SQLERRM, 1, 200);
               DBMS_OUTPUT.put_line (   'Proficiency rating error:'
                                     || SUBSTR (SQLERRM, 1, 200)
                                    );
         END;

         DBMS_OUTPUT.put_line ('Rating_id : ' || lv_rating_level_id);
      END IF;

      IF status = 'S'
      THEN
         BEGIN
            DBMS_OUTPUT.put_line ('Entering API');
            hr_competence_element_api.create_competence_element
                    (p_validate                   => FALSE,
                     p_competence_element_id      => l_competence_element_id,
                     p_object_version_number      => l_comp_object_version_number,
                     p_type                       => 'REQUIREMENT',
                     p_business_group_id          => lv_business_group_id,
                     p_competence_id              => l_competence_id,
                     p_proficiency_level_id       => lv_rating_level_id,
                     p_person_id                  => lv_person_id,
                     p_effective_date             => TO_DATE('31-OCT-2010'),
                     p_effective_date_from        => comp_ptr.date_from,
                     p_effective_date_to          => comp_ptr.date_to
                    );
            COMMIT;
            status := 'S';
            error := 'Success';
            l_total_succ_records := l_total_succ_records + 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_rec_cnt := l_error_rec_cnt + 1;
               DBMS_OUTPUT.put_line (   'Error in Competence API : '
                                     || SUBSTR (SQLERRM, 1, 255)
                                    );
               error := 'Error At API' || SUBSTR (SQLERRM, 1, 250);
               status := 'E';
         END;
      END IF;

      UPDATE xx_comp_prf_stg_tab
         SET v_status = status,
             v_error = error
       WHERE employee_number = comp_ptr.employee_number
         AND competency_name = comp_ptr.competency_name;
      COMMIT;
   END LOOP;

   fnd_file.put_line
      (fnd_file.LOG,
       '*****************************Summary for Competence Profile Upload*****************'
      );
   fnd_file.put_line (fnd_file.LOG,
                      'Total Records processed: ' || l_total_records
                     );
   fnd_file.put_line (fnd_file.LOG,
                         'Total Records successfully Loaded: '
                      || l_total_succ_records
                     );
   fnd_file.put_line (fnd_file.LOG,
                      'Total Records which has error: ' || l_error_rec_cnt
                     );
   fnd_file.put_line (fnd_file.LOG,
                         'End Time : '
                      || TO_CHAR (SYSDATE, 'DD-MON-YYYY     HH:MI:SS')
                     );
   fnd_file.put_line
      (fnd_file.LOG,
       '**********************************************End**********************************'
      );
END xx_add_compt_element_prc;
/

SQL Query to Extract Employee Contacts

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = ‘CONTACT’
AND hl.lookup_code(+) = pcr.contact_type

Change of Person Types

To change Person Type of the existing employee.

Navigation : People > Enter and Maintain > Others > Person Type Usage
Select the required person type to be changed.

Note: The person type change is limited to any of the user person types corresponding to the system person type effective on the date of change.

User Person Types are defined, as part of setup activity : Other Definitions > Person Types

How to change Hire Date for the Employee

Many times, user may end up hiring an employee without considering the date to set before creation of record. This may lead to Incorrect hire date for the resource.

To correct the same, please follow the below

Navigate to People - Enter and Maintain - Query the employee.

Change the date in the field "Latest Start Date", which is available on the Person with the correct Hire Date for the employee.

This action is applicable only, if the payroll is not run.