Loading Competency Profile for Employees

CREATE OR REPLACE PROCEDURE APPS.XXAA_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;

-- |  Load Data from custom Table   |

   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 xxaa_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 xxaa_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 XXAA_ADD_COMPT_ELEMENT_PRC;

/