Grades in Oracle HRMS

How to upload grades in Oracle HRMS??

TABLE

CREATE TABLE DEV_GRADE

(

  LINE_ID                  NUMBER,

  EFFECTIVE_START_DATE     DATE,

  SEGMENT1                 VARCHAR2(240 BYTE),

  SEGMENT2                 VARCHAR2(240 BYTE),

  SEGMENT3                 VARCHAR2(240 BYTE),

  NAME

  GRADE_ERROR_DESCRIPTION  VARCHAR2(240 BYTE),

  GRADE_ID                 NUMBER,

  GRADE_DEFINITION_ID      NUMBER,

  GRADE_OBJ_VER_NUMBER     NUMBER

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.dev_create_grade

IS

   CURSOR c_grade

   IS

      SELECT line_id, effective_start_date, segment1, segment2, segment3,       

             grade_error_description, grade_id, grade_definition_id,

             grade_obj_ver_number

        FROM dev_grade

       WHERE grade_error_description IS NULL;

   lc_c_grade                c_grade%ROWTYPE;

   l_grade_id                NUMBER;

   l_object_version_number   NUMBER;

   l_grade_definition_id     NUMBER;

   l_name                    VARCHAR2 (240);

   error_desc                VARCHAR2 (240);

   lv_grade_flag             CHAR (1);

   l_seq                     VARCHAR2 (20);

BEGIN

   OPEN c_grade;

   LOOP

      FETCH c_grade

       INTO lc_c_grade;

      EXIT WHEN c_grade%NOTFOUND;

---------------------------

-- Sequence Number

---------------------------

      SELECT NVL (MAX (SEQUENCE), 0) + 10

        INTO l_seq

        FROM per_grades pg;

       

        ---Check Max of Sequence before Uploading

      BEGIN

         hr_grade_api.create_grade

                        (p_validate                   => FALSE,

                         p_business_group_id          => 2217,

                         p_date_from                  => lc_c_grade.effective_start_date,

                         p_sequence                   => l_seq,

                         p_effective_date             => lc_c_grade.effective_start_date,

                         p_segment1                   => lc_c_grade.segment1,

                         p_segment2                   => lc_c_grade.segment2,

                         p_segment3                   => lc_c_grade.segment3,

                         p_grade_id                   => l_grade_id,

                         p_object_version_number      => l_object_version_number,

                         p_grade_definition_id        => l_grade_definition_id,

                         p_name                       => l_name

                        );

         IF l_grade_id IS NOT NULL

         THEN

            lv_grade_flag := 'Y';

            error_desc := 'No Error';

         END IF;

         IF lv_grade_flag = 'Y'

         THEN

            UPDATE dev_grade

               SET grade_error_description = error_desc,

                   grade_id = l_grade_id,

                   NAME = l_name,

                   grade_obj_ver_number = l_object_version_number,

                   grade_definition_id = l_grade_definition_id

             WHERE line_id = lc_c_grade.line_id;

         ELSE

            error_desc := error_desc || SQLERRM;

            UPDATE dev_grade

               SET grade_error_description = error_desc

             WHERE line_id = lc_c_grade.line_id;

         END IF;

         DBMS_OUTPUT.put_line ('Grade ID    : ' || l_grade_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            lv_grade_flag := 'N';

            error_desc := error_desc || SQLERRM;

            UPDATE dev_grade

               SET grade_error_description = error_desc

             WHERE line_id = lc_c_grade.line_id;

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

      l_grade_id := '';

      l_object_version_number := '';

      l_grade_definition_id := '';

      l_name := '';

      error_desc := '';

      lv_grade_flag := '';

      l_seq := '';

--   COMMIT;

   END LOOP;

   CLOSE c_grade;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/