Oracle HRMS API to Upload Competencies

CREATE OR REPLACE PROCEDURE APPS.xx_add_compt_element_prc (

   errbuf    OUT   VARCHAR2,

   retcode   OUT   NUMBER

)

-- +======================================================================+

-- |                     Oracle ort.com                        |

-- |                                                    |

-- +======================================================================+

-- |                                                                      |

-- |                                                                      |

-- | MODULE NAME:          apps.xx_add_compt_element_prc                  |

-- | ORIGINAL AUTHOR:      Faisal VENKATA NARAYANA GUTTA         |

-- | DATE:                 09-Mar-2011                                    |

-- | DESCRIPTION:          Interface for loading Competency Profile for   |

-- |                       Employees                                      |

-- |                                                                      |

-- |                                                                      |

-- +======================================================================+

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

/