How to upload Establishments (Schools & Colleges) in Oracle HRMS

Table:

CREATE TABLE DEV_ESTABISHMENT_INS

(

  LINE_ID                NUMBER,

  LOCATION               VARCHAR2(240 BYTE),

  NAME                   VARCHAR2(240 BYTE),

  EST_PROCESS_FLAG       CHAR(1 BYTE),

  EST_ERROR_DESCRIPTION  VARCHAR2(240 BYTE),

  EST_ID                 NUMBER,

  EST_DEFINITION_ID      NUMBER,

  EST_OBJ_VER_NUMBER     NUMBER

)

Procedure:

CREATE OR REPLACE PROCEDURE APPS.dev_create_establishment

IS

   CURSOR c_est

   IS

      SELECT line_id, LOCATION, NAME, est_process_flag,

             est_error_description, est_id, est_definition_id,

             est_obj_ver_number

        FROM dev_estabishment_ins

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

         AND est_error_description IS NULL;

   lc_c_est                  c_est%ROWTYPE;

   l_object_version_number   NUMBER;

   l_establishment_id        NUMBER;

   error_desc                VARCHAR2 (240);

   lv_est_flag               CHAR (1);

   l_seq                     VARCHAR2 (20);

BEGIN

   OPEN c_est;

   LOOP

      FETCH c_est

       INTO lc_c_est;

      EXIT WHEN c_est%NOTFOUND;

---------------------------

-- Sequence Number

---------------------------

      BEGIN

         per_est_ins.ins (p_validate                   => FALSE,

                          p_name                       => lc_c_est.NAME,

                          p_location                   => lc_c_est.LOCATION,

                          p_object_version_number      => l_object_version_number,

                          p_establishment_id           => l_establishment_id

                         );

         IF l_establishment_id IS NOT NULL

         THEN

            lv_est_flag := 'Y';

            error_desc := 'No Error';

         END IF;

         IF lv_est_flag = 'Y'

         THEN

            UPDATE dev_estabishment_ins

               SET est_error_description = error_desc,

                   est_process_flag = lv_est_flag,

                   est_obj_ver_number = l_object_version_number

             WHERE line_id = lc_c_est.line_id;

         ELSE

            error_desc := error_desc || SQLERRM;

            UPDATE dev_estabishment_ins

               SET est_error_description = error_desc,

                   est_process_flag = 'N'

             WHERE line_id = lc_c_est.line_id;

         END IF;

         DBMS_OUTPUT.put_line ('est ID    : ' || l_establishment_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            lv_est_flag := 'N';

            error_desc := error_desc || SQLERRM;

            UPDATE dev_estabishment_ins

               SET est_error_description = error_desc,

                   est_process_flag = lv_est_flag

             WHERE line_id = lc_c_est.line_id;

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

      l_establishment_id := '';

      l_object_version_number := '';

      error_desc := '';

      lv_est_flag := '';

      COMMIT;

   END LOOP;

   CLOSE c_est;

--dbms_output.put_line('Location ID        : '||l_location_id);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/