Grade Migration API

Table Script:

Create Table xxvion_grade_migration
(
EFFECTIVE_START_DATE DATE,
SEGMENT1 VARCHAR2(240 BYTE),
SEGMENT2 VARCHAR2(240 BYTE),
NAME VARCHAR2(240 BYTE),
GRADE_ID NUMBER,
GRADE_DEFINITION_ID NUMBER,
OBJ_VER_NUMBER NUMBER,
PROCESS_FLAG CHAR(1 BYTE),
ERROR_DESCRIPTION VARCHAR2(240 BYTE)
);

Migration API Script.

Create or Replace
PROCEDURE      xxvion_CREATE_GRADE
IS
/**************************************************************************
* Developed by : Mandeep Srivastava
*
* Module : APPS.
*
* Title : Create Grade
*
* Purpose : API Wrapper script - Grade Creation
* This will take the data residing in xxvion_grade_migration table and call
* the following API(s):-
*
*   HR_GRADE_API.CREATE_GRADE
**************************************************************************/
   CURSOR C_GRADE
   IS
select rowid,a.* from xxvion_grade_migration a
where process_flag='N'
and seq not in (100,110);
/*create table xxvion_grade_migration
(
   EFFECTIVE_START_DATE     DATE,
  SEGMENT1                 VARCHAR2(240 BYTE),
  SEGMENT2                 VARCHAR2(240 BYTE),
  NAME                     VARCHAR2(240 BYTE),
  GRADE_ID                 NUMBER,
  GRADE_DEFINITION_ID      NUMBER,
 OBJ_VER_NUMBER     NUMBER,
 PROCESS_FLAG       CHAR(1 BYTE),
  ERROR_DESCRIPTION  VARCHAR2(240 BYTE)
);*/
   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 seq
     INTO L_SEQ
       -- FROM PER_GRADES PG;
       from xxvion_grade_migration
       where segment1=LC_C_GRADE.segment1 and rowid=LC_C_GRADE.rowid;
      BEGIN
         HR_GRADE_API.CREATE_GRADE
                (P_VALIDATE                   => FALSE,
                 P_BUSINESS_GROUP_ID          => 7954,
                                           -- CHANGE BUSINESS GROUP ACCORDINLY
                 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_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 xxvion_grade_migration
               SET ERROR_DESCRIPTION = ERROR_DESC,
                   PROCESS_FLAG = LV_GRADE_FLAG,
                   GRADE_ID = L_GRADE_ID,
                   NAME = L_NAME,
                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER,
                   GRADE_DEFINITION_ID = L_GRADE_DEFINITION_ID
             WHERE segment1 = LC_C_GRADE.segment1
              and rowid=LC_C_GRADE.rowid;
         ELSE
            ERROR_DESC := ERROR_DESC || SQLERRM;
            UPDATE xxvion_grade_migration
               SET ERROR_DESCRIPTION = ERROR_DESC,
                  PROCESS_FLAG = 'N'
             WHERE segment1 = LC_C_GRADE.segment1
             and rowid=LC_C_GRADE.rowid;
         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 xxvion_grade_migration
               SET ERROR_DESCRIPTION = ERROR_DESC,
                 PROCESS_FLAG = LV_GRADE_FLAG
             WHERE segment1 = LC_C_GRADE.segment1
             and rowid=LC_C_GRADE.rowid;
            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;
--DBMS_OUTPUT.PUT_LINE('LOCATION ID        : '||L_LOCATION_ID);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END xxvion_CREATE_GRADE;