Employee Contracts Creation Procedure in Oracle HRMS

How to find correct API for Data Uploading

Kindly refer to the steps below.

1) TABLE

CREATE TABLE DEV_CONTRACT

(

  LINE_ID                NUMBER(15),

  EFFECTIVE_DATE         DATE,

  PERSON_ID              VARCHAR2(150 BYTE),

  REFERENCE              VARCHAR2(150 BYTE),

  TYPE                   VARCHAR2(150 BYTE),

  STATUS                 VARCHAR2(150 BYTE),

  DURATION               VARCHAR2(150 BYTE),

  DURATION_UNITS         VARCHAR2(150 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)

)

2) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_CONTRACT

IS

   CURSOR C_CONTRACT

   IS

      SELECT LINE_ID, EFFECTIVE_DATE, PERSON_ID, REFERENCE, TYPE, STATUS,

             DURATION, DURATION_UNITS, JOB_PROCESS_FLAG,

             JOB_ERROR_DESCRIPTION, JOB_ID, JOB_DEFINITION_ID,

             JOB_OBJ_VER_NUMBER

        FROM DEV_CONTRACT

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

         AND JOB_ERROR_DESCRIPTION IS NULL;

   LC_C_CONTRACT             C_CONTRACT%ROWTYPE;

   L_CONTRACT_ID             NUMBER;

   L_EFFECTIVE_START_DATE    DATE;

   L_EFFECTIVE_END_DATE      DATE;

   L_OBJECT_VERSION_NUMBER   NUMBER;

   ERROR_DESC                VARCHAR2 (240);

   LV_CONTRACT_FLAG          CHAR (1);

   L_SEQ                     VARCHAR2 (20);

BEGIN

   OPEN C_CONTRACT;

   LOOP

      FETCH C_CONTRACT

       INTO LC_C_CONTRACT;

      EXIT WHEN C_CONTRACT%NOTFOUND;

      BEGIN

         HR_CONTRACT_API.CREATE_CONTRACT

                          (P_VALIDATE                   => FALSE,

                           P_EFFECTIVE_DATE             => LC_C_CONTRACT.EFFECTIVE_DATE,

                           P_PERSON_ID                  => LC_C_CONTRACT.PERSON_ID,

                           P_REFERENCE                  => LC_C_CONTRACT.REFERENCE,

                           P_TYPE                       => LC_C_CONTRACT.TYPE,

                           P_STATUS                     => LC_C_CONTRACT.STATUS,

                           P_DURATION                   => LC_C_CONTRACT.DURATION,

                           P_DURATION_UNITS             => LC_C_CONTRACT.DURATION_UNITS,

                           P_CONTRACT_ID                => L_CONTRACT_ID,

                           P_EFFECTIVE_START_DATE       => L_EFFECTIVE_START_DATE,

                           P_EFFECTIVE_END_DATE         => L_EFFECTIVE_END_DATE,

                           P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER

                          );

         IF L_CONTRACT_ID IS NOT NULL

         THEN

            LV_CONTRACT_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_CONTRACT_FLAG = 'Y'

         THEN

            UPDATE DEV_CONTRACT

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = LV_CONTRACT_FLAG,

                   JOB_ID = L_CONTRACT_ID,

                   JOB_OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER

             WHERE LINE_ID = LC_C_CONTRACT.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_CONTRACT

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_CONTRACT.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('CONTRACT ID    : ' || L_CONTRACT_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_CONTRACT_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_CONTRACT

               SET JOB_ERROR_DESCRIPTION = ERROR_DESC,

                   JOB_PROCESS_FLAG = LV_CONTRACT_FLAG

             WHERE LINE_ID = LC_C_CONTRACT.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_CONTRACT_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_EFFECTIVE_START_DATE := '';

      L_EFFECTIVE_END_DATE := '';

      ERROR_DESC := '';

      LV_CONTRACT_FLAG := '';

   --COMMIT;

   END LOOP;

   CLOSE C_CONTRACT;

--DBMS_OUTPUT.PUT_LINE('LOCATION ID        : '||L_LOCATION_ID);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

How to create Employee Contacts

Leave a comment for any query.