Sunday, August 14, 2016

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;

/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect