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



Leave a comment for any query.



0 comments: