How to upload Qualification types in Oracle HRMS

Navigation: Global HRMS Manager -> Career Management -> Qualification Types

Table:

CREATE TABLE DEV_QUALIFICATION_TYPE

(

  LINE_ID            NUMBER,

  QUAL_NAME          VARCHAR2(150 BYTE),

  QUAL_CAT           VARCHAR2(150 BYTE),

  RANK               NUMBER,  

  EFFECTIVE_DATE     DATE,

  ERROR_DESCRIPTION  VARCHAR2(350 BYTE),

  PROCESS_FLAG       VARCHAR2(150 BYTE),

  OBJ_VER_NUMBER     NUMBER,

  QUAL_ID            NUMBER

)

Procedure:

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_QUAL_TYPE

IS

   CURSOR C_QUAL_TYPE

   IS

      SELECT LINE_ID, QUAL_NAME, QUAL_CAT, RANK, EFFECTIVE_DATE,

             ERROR_DESCRIPTION, PROCESS_FLAG, OBJ_VER_NUMBER, QUAL_ID

        FROM DEV_QUALIFICATION_TYPE

       WHERE NVL (PROCESS_FLAG, 'N') <> 'Y' AND ERROR_DESCRIPTION IS NULL;

   LC_C_QUAL_TYPE            C_QUAL_TYPE%ROWTYPE;

   L_QUALIFICATION_TYPE_ID   NUMBER;

   L_OBJECT_VERSION_NUMBER   NUMBER;

   ERROR_DESC                VARCHAR2 (240);

   LV_QUAL_FLAG              CHAR (1);

   L_TYPE                    VARCHAR2 (240);

BEGIN

   OPEN C_QUAL_TYPE;

   LOOP

      FETCH C_QUAL_TYPE

       INTO LC_C_QUAL_TYPE;

      EXIT WHEN C_QUAL_TYPE%NOTFOUND;

      SELECT LOOKUP_CODE

        INTO L_TYPE

        FROM HR_LOOKUPS

       WHERE LOOKUP_TYPE = 'PER_CATEGORIES'

         AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_QUAL_TYPE.QUAL_CAT));

      BEGIN

         HR_QUALIFICATION_TYPE_API.CREATE_QUALIFICATION_TYPE

                         (P_VALIDATE                   => FALSE,

                          P_NAME                       => LC_C_QUAL_TYPE.QUAL_NAME,

                          P_CATEGORY                   => L_TYPE,

                          P_RANK                       => LC_C_QUAL_TYPE.RANK,

                          --LC_C_QUAL_TYPE.QUAL_CAT,

                          P_EFFECTIVE_DATE             => LC_C_QUAL_TYPE.EFFECTIVE_DATE,

                          P_QUALIFICATION_TYPE_ID      => L_QUALIFICATION_TYPE_ID,

                          P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER

                         );

         IF L_QUALIFICATION_TYPE_ID IS NOT NULL

         THEN

            LV_QUAL_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_QUAL_FLAG = 'Y'

         THEN

            UPDATE DEV_QUALIFICATION_TYPE

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = LV_QUAL_FLAG,

                   QUAL_ID = L_QUALIFICATION_TYPE_ID,

                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER

             WHERE LINE_ID = LC_C_QUAL_TYPE.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_QUALIFICATION_TYPE

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_QUAL_TYPE.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE (   'QUALIFICATION ID        : '

                               || L_QUALIFICATION_TYPE_ID

                              );

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_QUAL_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_QUALIFICATION_TYPE

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = LV_QUAL_FLAG

             WHERE LINE_ID = LC_C_QUAL_TYPE.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      LV_QUAL_FLAG := '';

      L_QUALIFICATION_TYPE_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

   --COMMIT;

   END LOOP;

   CLOSE C_QUAL_TYPE;

--DBMS_OUTPUT.PUT_LINE('PHONE ID        : '||L_QUALIFICATION_TYPE_ID);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/