[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select preferred_region_code and no_set_day_indicator values from
legacy interface table IGS_PS_LGCY_OC_INT into PL/SQL table v_tab_uso.
Select unit section occurrence start date/end date and no set day indicator
values from legacy interface table IGS_PS_LGCY_UR_INT into PL/SQL table v_tab_unit_ref.
smvk 11-Dec-2002 Bug # 2702065. Modified rec_c_unit_sec.version_number,
l_unit_ver_rec.contact_hrs_lab and rec_c_unit_sec.unit_cd.
smvk 23-Dec-2002 Bug # 2702147. Logging of successful message in the log file and
updation of import_status of the record as 'I' only when the
overall status (x_return_status of API) is 'S'.
smvk 24-Dec-2002 Bug # 2702147. Printing the row head if the value of
the variable is l_b_print_row_heading TRUE.
smvk 31-Dec-2002 Bug # 2710978. Collecting the statistics of the interface table as per standards.
smvk 02-Jan-2002 Bug # 2695956. The process return status is set to Success even if one of the
record is successfully imported.(i.e the process status will be error only if all
the attempted records to import ends up in error).
(reverse chronological order - newest change first)
***************************************************************/
-- Distinct Unit Versions from all 8 Interface Tables
CURSOR c_all_units IS
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_uv_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_tr_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_ud_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
unit_version_number
FROM igs_ps_lgcy_ug_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_us_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_sg_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_oc_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_ur_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
UNION
SELECT DISTINCT
unit_cd,
version_number
FROM igs_ps_lgcy_ins_int
WHERE batch_id=p_n_batch_id
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number;
SELECT *
FROM igs_ps_lgcy_uv_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R');
SELECT *
FROM igs_ps_lgcy_tr_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, teach_resp_int_id;
SELECT *
FROM igs_ps_lgcy_ud_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, unit_discip_int_id;
SELECT *
FROM igs_ps_lgcy_ug_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND unit_version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,unit_version_number, uv_grd_schm_int_id;
SELECT *
FROM igs_ps_lgcy_us_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, unit_section_int_id;
SELECT *
FROM igs_ps_lgcy_sg_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, usec_grd_schm_int_id;
SELECT *
FROM igs_ps_lgcy_oc_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, usec_occur_int_id;
SELECT *
FROM igs_ps_lgcy_ur_int
WHERE batch_id=p_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, unit_reference_int_id;
SELECT *
FROM igs_ps_lgcy_ins_int
WHERE batch_id=cp_n_batch_id
AND unit_cd=cp_c_unit_cd
AND version_number=cp_n_version_number
AND import_status IN ('U','R')
ORDER BY unit_cd,version_number, uso_instructor_int_id;
l_d_prog_upd_dt igs_ps_lgcy_uv_int.program_update_date%TYPE;
SELECT
message_number,
message_text
FROM fnd_new_messages
WHERE application_id=8405
AND language_code = USERENV('LANG')
AND message_name=cp_c_msg_name;
SELECT
description
FROM igs_ps_lgcy_bat_int
WHERE batch_id=p_n_batch_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_uv_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND unit_version_int_id = l_unit_ver_rec.interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_uv_int
SET import_status = l_unit_ver_rec.status
WHERE batch_id=p_n_batch_id
AND unit_version_int_id = l_unit_ver_rec.interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_VERSION','LEGACY_PS_REC_TABLES'),
l_unit_ver_rec.interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
/* Update the interface table */
UPDATE igs_ps_lgcy_tr_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND teach_resp_int_id = v_tab_unit_tr(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_tr_int
SET import_status = v_tab_unit_tr(i).status
WHERE batch_id=p_n_batch_id
AND teach_resp_int_id = v_tab_unit_tr(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_TEACH_RESP','LEGACY_PS_REC_TABLES'),
v_tab_unit_tr(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_unit_tr.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_ud_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND unit_discip_int_id = v_tab_unit_dscp(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_ud_int
SET import_status = v_tab_unit_dscp(i).status
WHERE batch_id=p_n_batch_id
AND unit_discip_int_id = v_tab_unit_dscp(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_DISCP','LEGACY_PS_REC_TABLES'),
v_tab_unit_dscp(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_unit_dscp.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_ug_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND uv_grd_schm_int_id = v_tab_unit_gs(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_ug_int
SET import_status = v_tab_unit_gs(i).status
WHERE batch_id=p_n_batch_id
AND uv_grd_schm_int_id = v_tab_unit_gs(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_GRD_SCH','LEGACY_PS_REC_TABLES'),
v_tab_unit_gs(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_unit_gs.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_us_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND unit_section_int_id = v_tab_usec(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_us_int
SET import_status = v_tab_usec(i).status
WHERE batch_id=p_n_batch_id
AND unit_section_int_id = v_tab_usec(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_SEC','LEGACY_PS_REC_TABLES'),
v_tab_usec(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_usec.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_sg_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND usec_grd_schm_int_id = v_tab_usec_gs(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_sg_int
SET import_status = v_tab_usec_gs(i).status
WHERE batch_id=p_n_batch_id
AND usec_grd_schm_int_id = v_tab_usec_gs(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_GRD_SCH','LEGACY_PS_REC_TABLES'),
v_tab_usec_gs(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_usec_gs.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_oc_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND usec_occur_int_id = v_tab_uso(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_oc_int
SET import_status = v_tab_uso(i).status
WHERE batch_id=p_n_batch_id
AND usec_occur_int_id = v_tab_uso(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_SEC_OCCUR','LEGACY_PS_REC_TABLES'),
v_tab_uso(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_uso.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_ur_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND unit_reference_int_id = v_tab_unit_ref(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_ur_int
SET import_status = v_tab_unit_ref(i).status
WHERE batch_id=p_n_batch_id
AND unit_reference_int_id = v_tab_unit_ref(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int
(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(
igs_ps_lgcy_err_int_s.nextval,
igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_REF','LEGACY_PS_REC_TABLES'),
v_tab_unit_ref(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt
);
v_tab_unit_ref.DELETE;
/* Update the interface table */
UPDATE igs_ps_lgcy_ins_int
SET import_status = 'I'
WHERE batch_id=p_n_batch_id
AND uso_instructor_int_id = v_tab_ins(i).interface_id;
/* Update the interface table */
UPDATE igs_ps_lgcy_ins_int
SET import_status = v_tab_ins(i).status
WHERE batch_id=p_n_batch_id
AND uso_instructor_int_id = v_tab_ins(i).interface_id;
INSERT INTO igs_ps_lgcy_err_int(
err_message_id,
int_table_code,
int_table_id,
message_num,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) VALUES(
igs_ps_lgcy_err_int_s.nextval,
fnd_message.get,
v_tab_ins(i).interface_id,
l_n_msg_num,
l_c_msg_txt,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.user_id,-1),
SYSDATE,
NVL(fnd_global.login_id,-1),
l_n_request_id,
l_n_prog_appl_id,
l_n_prog_id,
l_d_prog_upd_dt);
v_tab_ins.DELETE;
/* Delete imported records if user wishes to delete by passing the parameter p_delete='Y' */
IF p_c_del_flag='Y' AND l_c_return_status = 'S' THEN
/* Delete from Unit Version Interface Table */
DELETE FROM igs_ps_lgcy_uv_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Teaching Responsibility Interface Table */
DELETE FROM igs_ps_lgcy_tr_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Unit Disciplines Interface Table */
DELETE FROM igs_ps_lgcy_ud_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Grading Schema Interface Table */
DELETE FROM igs_ps_lgcy_ug_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Unit Section Interface Table */
DELETE FROM igs_ps_lgcy_us_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Unit Section Grading Schema Interface Table */
DELETE FROM igs_ps_lgcy_sg_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Unit Section Occurrences Interface Table */
DELETE FROM igs_ps_lgcy_oc_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Unit Reference Codes Table */
DELETE FROM igs_ps_lgcy_ur_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';
/* Delete from Instructor Table */
DELETE FROM igs_ps_lgcy_ins_int
WHERE batch_id=p_n_batch_id
AND import_status = 'I';