Data Migration using API

Data Migration using API

1.       Create the table where one wants to load records from the stage table

CREATE TABLE SUB_TEMP_EMP_MIG

(

  SEQNO           NUMBER(5),

  PERSON_ID       NUMBER(10),

  FULL_NAME       VARCHAR2(500 BYTE),

  JOB_ID          NUMBER(10),

  JOB             VARCHAR2(1000 BYTE),

  PERSON_TYPE_ID  NUMBER(10),

  PERSON_TYPE     VARCHAR2(500 BYTE),

  SUPERVISOR_ID   NUMBER(10),

  SUPERVISOR      VARCHAR2(500 BYTE),

  CREATION_DATE   DATE,

  CREATED_BY      VARCHAR2(50 BYTE)

)

2.       Create Error Table  

CREATE TABLE TEMP_ERROR_LOG --  error table

(

  ERROR_ID     NUMBER,

  ERR_CODE     VARCHAR2(100 BYTE),

  ERR_MSG      VARCHAR2(500 BYTE),

  ERR_DATE     DATE                             DEFAULT SYSDATE,

  STATUS       VARCHAR2(100 BYTE),

  COLUMN_NAME  VARCHAR2(100 BYTE)

)

3.       Procedure for Error tracking

CREATE OR REPLACE PROCEDURE APPS.sp_temp_error_log (

   err_code   IN   VARCHAR2,

   err_msg    IN   VARCHAR2,  

   err_date        DATE,

   status IN varchar2,

   COLUMN_NAME IN VARCHAR2

)

AS

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   INSERT INTO temp_error_log

        VALUES (seq1.NEXTVAL, err_code, err_msg, SYSDATE,status,upper(COLUMN_NAME));

        commit;

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;

/

4.       API – Data Migration

CREATE OR REPLACE PROCEDURE APPS.sp_temp_emp_migration (

   retcode   OUT   VARCHAR2,

   errbuff   OUT   VARCHAR2

)

AS

   CURSOR c1

   IS

      SELECT seqno, person_id, full_name, job_id, person_type_id,

             supervisor_id

        FROM temp_employee_details_mig;

   job_name           VARCHAR2 (500);

   person_type_name   VARCHAR2 (500);

   supervisor_name    VARCHAR2 (500);

   curr_user          VARCHAR2 (100);

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         -- get job name

         BEGIN

            SELECT pjd.segment4

              INTO job_name

              FROM per_jobs pj, per_job_definitions pjd

             WHERE pj.job_definition_id = pjd.job_definition_id

               AND i.job_id = pj.job_id;

         EXCEPTION

            WHEN OTHERS

            THEN

               sp_temp_error_log (SQLCODE, SQLERRM, SYSDATE, i.person_id,'job');

                fnd_file.put_line (fnd_file.LOG,

                               i.person_id || ' could not be inserted (JOB)'

                              );

         END;

         --person type

         BEGIN

            SELECT ppt.user_person_type

              INTO person_type_name

              FROM per_person_types ppt

             WHERE ppt.person_type_id = i.person_type_id;

         EXCEPTION

            WHEN OTHERS

            THEN

               sp_temp_error_log (SQLCODE, SQLERRM, SYSDATE, i.person_id,'person type');

                fnd_file.put_line (fnd_file.LOG,

                               i.person_id || ' could not be inserted (Person Type)'

                              );

         END;

         --supervisor name

         BEGIN

            SELECT full_name

              INTO supervisor_name

              FROM per_all_people_f

             WHERE sysdate between effective_start_date and effective_end_date

             and person_id = i.supervisor_id;

         EXCEPTION

            WHEN OTHERS

            THEN

               sp_temp_error_log (SQLCODE, SQLERRM, SYSDATE, i.person_id,'supervisor');

                fnd_file.put_line (fnd_file.LOG,

                               i.person_id || ' could not be inserted (supervisor)'

                              );

         END;

         BEGIN

            SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')

              INTO curr_user

              FROM DUAL;

            INSERT INTO sub_temp_emp_mig

                        (seqno, person_id, full_name, job_id,

                         job, person_type_id, person_type,

                         supervisor_id, supervisor, creation_date, created_by

                        )

                 VALUES (i.seqno, i.person_id, i.full_name, i.job_id,

                         job_name, i.person_type_id, person_type_name,

                         i.supervisor_id, supervisor_name, SYSDATE, curr_user

                        );

            fnd_file.put_line (fnd_file.LOG,

                               i.person_id

                               || ' has been inserted successfully'

                              );

            fnd_file.put_line (fnd_file.output,

                               i.person_id

                               || ' has been inserted successfully'

                              );

         EXCEPTION

            WHEN OTHERS

            THEN

               sp_temp_error_log (SQLCODE, SQLERRM, SYSDATE, i.person_id,'Insert');

         END;

      EXCEPTION

         WHEN OTHERS

         THEN

            fnd_file.put_line (fnd_file.LOG,

                               i.person_id || ' could not be inserted'

                              );

            fnd_file.put_line (fnd_file.output,

                               i.person_id || ' could not be inserted'

                              );

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      sp_temp_error_log (SQLCODE, SQLERRM, SYSDATE, 'Main Block','Main Block');

END;