Jobs in Oracle HRMS

How to upload jobs in Oracle HRMS??

Here is the solution


TABLE

CREATE TABLE DEV_JOB

(

  LINE_ID                NUMBER(15),

  EFFECTIVE_START_DATE   DATE,

  SEGMENT1               VARCHAR2(150 BYTE),

  SEGMENT2               VARCHAR2(150 BYTE),

  SEGMENT3               VARCHAR2(150 BYTE),

  SEGMENT4               VARCHAR2(150 BYTE),

  SEGMENT5               VARCHAR2(150 BYTE),

  SEGMENT6               VARCHAR2(150 BYTE),

  NAME                   VARCHAR2(700 BYTE),

  JOB_PROCESS_FLAG       VARCHAR2(30 BYTE),

  JOB_ERROR_DESCRIPTION  VARCHAR2(150 BYTE),

  JOB_ID                 NUMBER(15),

  JOB_DEFINITION_ID      NUMBER(15),

  JOB_OBJ_VER_NUMBER     NUMBER(9)

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_JOB

IS

   CURSOR C_JOB

   IS

      SELECT LINE_ID, EFFECTIVE_START_DATE, SEGMENT1, SEGMENT2, SEGMENT3,

             NAME, JOB_PROCESS_FLAG, JOB_ERROR_DESCRIPTION, JOB_ID,

             JOB_DEFINITION_ID, JOB_OBJ_VER_NUMBER

        FROM DEV_JOB

       WHERE NVL (JOB_PROCESS_FLAG, 'N') <> 'Y'

         AND JOB_ERROR_DESCRIPTION IS NULL;

   LC_C_JOB                  C_JOB%ROWTYPE;

   L_JOB_ID                  NUMBER;

   L_OBJECT_VERSION_NUMBER   NUMBER;

   L_JOB_DEFINITION_ID       NUMBER;

   L_NAME                    VARCHAR2 (240);

   ERROR_DESC                VARCHAR2 (240);

   LV_JOB_FLAG               CHAR (1);

   L_SEQ                     VARCHAR2 (20);

BEGIN

   OPEN C_JOB;

   LOOP

      FETCH C_JOB

       INTO LC_C_JOB;

      EXIT WHEN C_JOB%NOTFOUND;

      BEGIN

         HR_JOB_API.CREATE_JOB

               (P_VALIDATE                   => FALSE,

                P_BUSINESS_GROUP_ID          => 0,

                                          -- CHANGE BUSINESS GROUP ACCORDINGLY

                P_DATE_FROM                  => LC_C_JOB.EFFECTIVE_START_DATE,

                P_JOB_GROUP_ID               => 1,

                P_SEGMENT1                   => LC_C_JOB.SEGMENT1,

                P_SEGMENT2                   => LC_C_JOB.SEGMENT2,

                P_SEGMENT3                   => LC_C_JOB.SEGMENT3,

                P_JOB_ID                     => L_JOB_ID,

                P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER,

                P_JOB_DEFINITION_ID          => L_JOB_DEFINITION_ID,

                P_NAME                       => L_NAME

               );

         IF L_JOB_ID IS NOT NULL

         THEN

            LV_JOB_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_JOB_FLAG = 'Y'

         THEN

            UPDATE DEV_JOB

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = LV_JOB_FLAG,

                   JOB_ID = L_JOB_ID,

                   NAME = L_NAME,

                   JOB_OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER,

                   JOB_DEFINITION_ID = L_JOB_DEFINITION_ID

             WHERE LINE_ID = LC_C_JOB.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_JOB

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_JOB.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('JOB ID    : ' || L_JOB_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_JOB_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_JOB

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = LV_JOB_FLAG

             WHERE LINE_ID = LC_C_JOB.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_JOB_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_JOB_DEFINITION_ID := '';

      L_NAME := '';

      ERROR_DESC := '';

      LV_JOB_FLAG := '';

   --COMMIT;

   END LOOP;

   CLOSE C_JOB;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

How to upload Grades in Oracle HRMS

Leave a comment for any query.