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;
