[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
SELECT cst_insert dmlmode, rowid, a.*
FROM igs_ad_notes_int a
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2;
UPDATE igs_ad_notes_int
SET
status = cst_s_val_3
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = notes_rec.rowid;
UPDATE igs_ad_notes_int
SET
status = cst_s_val_3
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = notes_rec.rowid;
igs_ad_appl_notes_pkg.INSERT_ROW(
X_ROWID => l_Rowid,
X_APPL_NOTES_ID => l_Appl_Notes_Id,
x_Person_Id => notes_rec.person_id,
X_Admission_Appl_Number => notes_rec.Admission_Appl_Number,
x_Nominated_Course_Cd => notes_rec.Nominated_Course_Cd,
x_Sequence_Number => notes_rec.Sequence_Number,
x_Note_Type_Id => notes_rec.Note_Type_Id,
x_Ref_Notes_Id => l_Ref_Notes_Id,
x_Mode => 'R');
igs_ge_note_pkg.INSERT_ROW(
X_ROWID => l_Rowid2,
X_REFERENCE_NUMBER => l_Ref_Notes_Id,
X_S_NOTE_FORMAT_TYPE => 'TEXT',
X_NOTE_TEXT => notes_rec.notes,
X_MODE => 'R');
UPDATE igs_ad_notes_int
SET
status = cst_s_val_1
WHERE rowid = notes_rec.rowid;
UPDATE igs_ad_notes_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE rowid = notes_rec.rowid;
UPDATE igs_ad_notes_int
SET status = cst_s_val_3
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = notes_rec.rowid;
UPDATE igs_ad_notes_int in_rec
SET
status = cst_s_val_3
, error_code = cst_ec_val_E678
, error_text = cst_et_val_E678
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND EXISTS ( SELECT 1
FROM igs_ad_appl_notes mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd
AND mn_rec.note_type_id = in_rec.note_type_id
AND mn_rec.sequence_number = in_rec.sequence_number);
SELECT cst_insert dmlmode, rowid, a.*
FROM igs_ad_edugoal_int a
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2;
UPDATE igs_ad_edugoal_int
SET
status = cst_s_val_3
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = edugoal_rec.rowid;
igs_ad_edugoal_pkg.insert_row
(
X_ROWID => l_rowid,
X_POST_EDUGOAL_ID => l_post_edugoal_id,
X_PERSON_ID => edugoal_rec.person_id,
X_ADMISSION_APPL_NUMBER => edugoal_rec.admission_appl_number,
X_NOMINATED_COURSE_CD => edugoal_rec.nominated_course_cd,
X_SEQUENCE_NUMBER => edugoal_rec.sequence_number,
X_EDU_GOAL_ID => edugoal_rec.edu_goal_id,
X_MODE => 'R'
);
UPDATE igs_ad_edugoal_int
SET status = cst_s_val_1
WHERE rowid = edugoal_rec.rowid;
UPDATE igs_ad_edugoal_int
SET status = cst_s_val_3
,error_code = l_error_code
,error_text = l_error_text
WHERE rowid = edugoal_rec.rowid;
UPDATE igs_ad_edugoal_int
SET
status = cst_s_val_3
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = edugoal_rec.rowid;
UPDATE igs_ad_edugoal_int in_rec
SET status = cst_s_val_3
,error_code = cst_ec_val_E678
,error_text = cst_et_val_E678
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND EXISTS ( SELECT 1
FROM igs_ad_edugoal mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd
AND mn_rec.edu_goal_id = in_rec.edu_goal_id
AND mn_rec.sequence_number = in_rec.sequence_number);
SELECT cst_insert dmlmode, rowid, in_rec.*
FROM igs_ad_unitsets_int in_rec
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND ( ( NVL(match_ind,'15') = '15'
AND NOT EXISTS ( SELECT 1
FROM igs_ad_unit_sets mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.sequence_number = in_rec.sequence_number
AND mn_rec.unit_set_cd = in_rec.unit_set_cd
AND mn_rec.version_number = in_rec.version_number
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd))
OR (p_rule = cst_rule_val_R
AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
UNION ALL
SELECT cst_update dmlmode, rowid, in_rec.*
FROM igs_ad_unitsets_int in_rec
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND ( (p_rule = cst_rule_val_I)
OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
AND EXISTS ( SELECT 1
FROM igs_ad_unit_sets mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.sequence_number = in_rec.sequence_number
AND mn_rec.unit_set_cd = in_rec.unit_set_cd
AND mn_rec.version_number = in_rec.version_number
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
SELECT ROWID, mn_rec.*
FROM igs_ad_unit_sets mn_rec
WHERE mn_rec.person_id = cp_unit_set_cur.person_id
AND mn_rec.sequence_number = cp_unit_set_cur.sequence_number
AND mn_rec.unit_set_cd = cp_unit_set_cur.unit_set_cd
AND mn_rec.version_number = cp_unit_set_cur.version_number
AND mn_rec.admission_appl_number = cp_unit_set_cur.admission_appl_number
AND mn_rec.nominated_course_cd = cp_unit_set_cur.nominated_course_cd;
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_3
,match_ind = DECODE (unitsets_rec.dmlmode, cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
,error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = unitsets_rec.rowid;
SAVEPOINT before_creatupdate;
IF unitsets_rec.dmlmode = cst_insert THEN
igs_ad_unit_sets_pkg.INSERT_ROW (
x_rowid => l_rowid,
x_unit_set_id => l_unit_set_id,
x_person_id => unitsets_rec.person_id,
x_admission_appl_number => unitsets_rec.admission_appl_number,
x_nominated_course_cd => unitsets_rec.nominated_course_cd,
x_sequence_number => unitsets_rec.sequence_number,
x_unit_set_cd => unitsets_rec.unit_set_cd,
x_version_number => unitsets_rec.version_number,
x_rank => unitsets_rec.rank,
x_mode => 'R'
);
ELSIF unitsets_rec.dmlmode = cst_update THEN
OPEN c_null_hdlg_unitsets_cur_rec(unitsets_rec);
igs_ad_unit_sets_pkg.update_row(
x_rowid => c_null_hdlg_unitsets_rec.rowid,
x_unit_set_id => c_null_hdlg_unitsets_rec.unit_set_id,
x_person_id => NVL(unitsets_rec.person_id,c_null_hdlg_unitsets_rec.person_id),
x_admission_appl_number=> NVL(unitsets_rec.admission_appl_number,c_null_hdlg_unitsets_rec.admission_appl_number),
x_nominated_course_cd => NVL(unitsets_rec.nominated_course_cd, c_null_hdlg_unitsets_rec.nominated_course_cd),
x_sequence_number => NVL(unitsets_rec.sequence_number,c_null_hdlg_unitsets_rec.sequence_number),
x_unit_set_cd => NVL(unitsets_rec.unit_set_cd,c_null_hdlg_unitsets_rec.unit_set_cd),
x_version_number => NVL(unitsets_rec.version_number,c_null_hdlg_unitsets_rec.version_number),
x_rank => NVL(unitsets_rec.rank,c_null_hdlg_unitsets_rec.rank),
x_mode =>'R');
UPDATE igs_ad_unitsets_int
SET
status = cst_s_val_1
, match_ind = DECODE (unitsets_rec.dmlmode,cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
WHERE rowid = unitsets_rec.rowid;
ROLLBACK TO before_creatupdate;
IF unitsets_rec.dmlmode = cst_insert THEN
l_error_code := 'E322'; -- Insertion Failed
ELSIF unitsets_rec.dmlmode = cst_update THEN
l_error_code := 'E014'; -- Update Failed
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_3
, match_ind = DECODE ( unitsets_rec.dmlmode
,cst_update, DECODE ( match_ind, NULL, cst_mi_val_12, match_ind)
,cst_insert, DECODE ( p_rule
,cst_rule_val_R, DECODE ( match_ind, NULL, cst_mi_val_11, match_ind)
,cst_mi_val_11))
, error_code = l_error_code
, error_text = l_error_text
WHERE rowid = unitsets_rec.rowid;
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_3
, match_ind = DECODE (unitsets_rec.dmlmode, cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
, error_code = cst_ec_val_E701
, error_text = l_error_text
WHERE rowid = unitsets_rec.rowid;
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_3
, error_code = cst_ec_val_E700
, error_text = cst_et_val_E700
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_1
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);
UPDATE igs_ad_unitsets_int in_rec
SET status = cst_s_val_1
, match_ind = cst_mi_val_19
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND EXISTS ( SELECT 1
FROM igs_ad_unit_sets mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.sequence_number = in_rec.sequence_number
AND mn_rec.unit_set_cd = in_rec.unit_set_cd
AND mn_rec.version_number = in_rec.version_number
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
UPDATE igs_ad_unitsets_int in_rec
SET status = cst_s_val_1
, match_ind = cst_mi_val_23
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
AND EXISTS ( SELECT 1
FROM igs_ad_unit_sets mn_rec
WHERE NVL(mn_rec.person_id, -99) = NVL(in_rec.person_id,NVL(mn_rec.person_id, -99) )
AND NVL(mn_rec.admission_appl_number, -99) = NVL(in_rec.admission_appl_number,NVL(mn_rec.admission_appl_number, -99) )
AND NVL(mn_rec.nominated_course_cd,'~') = NVL(in_rec.nominated_course_cd, NVL(mn_rec.nominated_course_cd,'~') )
AND NVL(mn_rec.sequence_number, -99) = NVL(in_rec.sequence_number, NVL(mn_rec.sequence_number, -99))
AND NVL(mn_rec.unit_set_cd, '~') = NVL(in_rec.unit_set_cd, NVL(mn_rec.unit_set_cd, '~'))
AND NVL(mn_rec.version_number, -99) = NVL(in_rec.version_number,NVL(mn_rec.version_number, -99) )
AND NVL(mn_rec.rank, -99) = NVL(in_rec.rank, NVL(mn_rec.rank, -99))
);
UPDATE igs_ad_unitsets_int in_rec
SET
status = cst_s_val_3
, match_ind = cst_mi_val_20
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
AND EXISTS ( SELECT rowid
FROM igs_ad_unit_sets mn_rec
WHERE mn_rec.person_id = in_rec.person_id
AND mn_rec.sequence_number = in_rec.sequence_number
AND mn_rec.unit_set_cd = in_rec.unit_set_cd
AND mn_rec.version_number = in_rec.version_number
AND mn_rec.admission_appl_number = in_rec.admission_appl_number
AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
UPDATE igs_ad_unitsets_int
SET status = cst_s_val_3
, error_code = cst_ec_val_E700
, error_text = cst_et_val_E700
WHERE interface_run_id = p_interface_run_id
AND status = cst_s_val_2
AND match_ind IS NOT NULL;