[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hz.party_id
FROM hz_parties hz
WHERE hz.party_number = cp_person_number;
SELECT 'X'
FROM igs_en_stdnt_ps_att spa
WHERE spa.person_id = cp_person_id
AND spa.course_cd = cp_course_cd;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_inst ci,
igs_ca_type ct
WHERE ci.alternate_code = cp_alternate_code
AND ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'LOAD';
SELECT ost.timeframe,
stty.closed_ind,
stty.derivation
FROM igs_en_stdnt_ps_att spa,
igs_ps_ver pv,
igs_pr_org_stat ost,
igs_pr_stat_type stty
WHERE spa.person_id = cp_person_id
AND spa.course_cd = cp_course_cd
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND ost.org_unit_cd = pv.responsible_org_unit_cd
AND ost.stat_type = cp_stat_type
AND ost.stat_type = stty.stat_type;
SELECT ist.timeframe,
stty.closed_ind,
stty.derivation
FROM igs_pr_inst_stat ist,
igs_pr_stat_type stty
WHERE ist.stat_type = cp_stat_type
AND ist.stat_type = stty.stat_type;
SELECT 'X'
FROM igs_pr_sta_type_ele stte
WHERE stte.stat_type = cp_stat_type
AND stte.s_stat_element = cp_s_stat_element;
SELECT sasi.*
FROM igs_pr_stu_acad_stat_int sasi
WHERE sasi.batch_id = cp_batch_id
FOR UPDATE;
DELETE FROM igs_pr_stu_acad_stat_int
WHERE CURRENT OF cur_stu_acad_stat_int;
UPDATE igs_pr_stu_acad_stat_int
SET error_code = l_error_code
WHERE CURRENT OF cur_stu_acad_stat_int;
SELECT hz.party_id
FROM hz_parties hz
WHERE hz.party_number = cp_person_number;
SELECT spa.ROWID,
spa.course_attempt_status
FROM igs_en_stdnt_ps_att spa
WHERE spa.person_id = cp_person_id
AND spa.course_cd = cp_course_cd;
SELECT susa.ROWID,
susa.end_dt,
susa.rqrmnts_complete_ind
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.unit_set_cd = cp_unit_set_cd;
PROCEDURE update_susa (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_rowid IN ROWID,
p_end_dt IN igs_as_su_setatmpt.end_dt%TYPE,
p_voluntary_end_ind IN igs_as_su_setatmpt.voluntary_end_ind%TYPE,
p_rqrmnts_complete_ind IN igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE,
p_rqrmnts_complete_dt IN igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE,
p_s_completed_source_type IN igs_as_su_setatmpt.s_completed_source_type%TYPE
) IS
/****************************************************************************************************************
|| Created By : dlarsen
|| Created On : 16-DEC-2002
|| Purpose : This updates the Student Unit Set Attempt record with the completion or ending details.
||
|| This process can be called from upload_external_completion.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
****************************************************************************************************************/
CURSOR c_susa (
cp_rowid ROWID
) IS
SELECT susa.*
FROM igs_as_su_setatmpt susa
WHERE susa.ROWID = cp_rowid
FOR UPDATE NOWAIT;
igs_as_su_setatmpt_pkg.update_row (
x_mode => 'R',
x_rowid => p_rowid,
x_person_id => v_susa_rec.person_id,
x_course_cd => v_susa_rec.course_cd,
x_unit_set_cd => v_susa_rec.unit_set_cd,
x_us_version_number => v_susa_rec.us_version_number,
x_sequence_number => v_susa_rec.sequence_number,
x_selection_dt => v_susa_rec.selection_dt,
x_student_confirmed_ind => v_susa_rec.student_confirmed_ind,
x_end_dt => p_end_dt,
x_parent_unit_set_cd => v_susa_rec.parent_unit_set_cd,
x_parent_sequence_number => v_susa_rec.parent_sequence_number,
x_primary_set_ind => v_susa_rec.primary_set_ind,
x_voluntary_end_ind => p_voluntary_end_ind,
x_authorised_person_id => v_susa_rec.authorised_person_id,
x_authorised_on => v_susa_rec.authorised_on,
x_override_title => v_susa_rec.override_title,
x_rqrmnts_complete_ind => p_rqrmnts_complete_ind,
x_rqrmnts_complete_dt => p_rqrmnts_complete_dt,
x_s_completed_source_type => p_s_completed_source_type,
x_catalog_cal_type => v_susa_rec.catalog_cal_type,
x_catalog_seq_num => v_susa_rec.catalog_seq_num,
x_attribute_category => v_susa_rec.attribute_category,
x_attribute1 => v_susa_rec.attribute1,
x_attribute2 => v_susa_rec.attribute2,
x_attribute3 => v_susa_rec.attribute3,
x_attribute4 => v_susa_rec.attribute4,
x_attribute5 => v_susa_rec.attribute5,
x_attribute6 => v_susa_rec.attribute6,
x_attribute7 => v_susa_rec.attribute7,
x_attribute8 => v_susa_rec.attribute8,
x_attribute9 => v_susa_rec.attribute9,
x_attribute10 => v_susa_rec.attribute10,
x_attribute11 => v_susa_rec.attribute11,
x_attribute12 => v_susa_rec.attribute12,
x_attribute13 => v_susa_rec.attribute13,
x_attribute14 => v_susa_rec.attribute14,
x_attribute15 => v_susa_rec.attribute15,
x_attribute16 => v_susa_rec.attribute16,
x_attribute17 => v_susa_rec.attribute17,
x_attribute18 => v_susa_rec.attribute18,
x_attribute19 => v_susa_rec.attribute19,
x_attribute20 => v_susa_rec.attribute20
);
END update_susa;
PROCEDURE update_spa (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_rowid IN ROWID,
p_course_rqrmnts_complete_dt IN igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE
) IS
/****************************************************************************************************************
|| Created By : dlarsen
|| Created On : 16-DEC-2002
|| Purpose : This updates the Student Program Attempt record with the completion details.
||
|| This process can be called from upload_external_completion.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
|| (reverse chronological order - newest change first)
****************************************************************************************************************/
CURSOR c_spa (
cp_rowid ROWID
) IS
SELECT spa.*
FROM igs_en_stdnt_ps_att spa
WHERE spa.ROWID = cp_rowid
FOR UPDATE NOWAIT;
igs_en_stdnt_ps_att_pkg.update_row (
x_mode => 'R',
x_rowid => p_rowid,
x_person_id => v_spa_rec.person_id,
x_course_cd => v_spa_rec.course_cd,
x_version_number => v_spa_rec.version_number,
x_cal_type => v_spa_rec.cal_type,
x_location_cd => v_spa_rec.location_cd,
x_attendance_mode => v_spa_rec.attendance_mode,
x_attendance_type => v_spa_rec.attendance_type,
x_coo_id => v_spa_rec.coo_id,
x_student_confirmed_ind => v_spa_rec.student_confirmed_ind,
x_commencement_dt => v_spa_rec.commencement_dt,
x_course_attempt_status => v_spa_rec.course_attempt_status,
x_progression_status => v_spa_rec.progression_status,
x_derived_att_type => v_spa_rec.derived_att_type,
x_derived_att_mode => v_spa_rec.derived_att_mode,
x_provisional_ind => v_spa_rec.provisional_ind,
x_discontinued_dt => v_spa_rec.discontinued_dt,
x_discontinuation_reason_cd => v_spa_rec.discontinuation_reason_cd,
x_lapsed_dt => v_spa_rec.lapsed_dt,
x_funding_source => v_spa_rec.funding_source,
x_exam_location_cd => v_spa_rec.exam_location_cd,
x_derived_completion_yr => v_spa_rec.derived_completion_yr,
x_derived_completion_perd => v_spa_rec.derived_completion_perd,
x_nominated_completion_yr => v_spa_rec.nominated_completion_yr,
x_nominated_completion_perd => v_spa_rec.nominated_completion_perd,
x_rule_check_ind => v_spa_rec.rule_check_ind,
x_waive_option_check_ind => v_spa_rec.waive_option_check_ind,
x_last_rule_check_dt => v_spa_rec.last_rule_check_dt,
x_publish_outcomes_ind => v_spa_rec.publish_outcomes_ind,
x_course_rqrmnt_complete_ind => 'Y',
x_course_rqrmnts_complete_dt => p_course_rqrmnts_complete_dt,
x_s_completed_source_type => 'SYSTEM',
x_override_time_limitation => v_spa_rec.override_time_limitation,
x_advanced_standing_ind => v_spa_rec.advanced_standing_ind,
x_fee_cat => v_spa_rec.fee_cat,
x_correspondence_cat => v_spa_rec.correspondence_cat,
x_self_help_group_ind => v_spa_rec.self_help_group_ind,
x_logical_delete_dt => v_spa_rec.logical_delete_dt,
x_adm_admission_appl_number => v_spa_rec.adm_admission_appl_number,
x_adm_nominated_course_cd => v_spa_rec.adm_nominated_course_cd,
x_adm_sequence_number => v_spa_rec.adm_sequence_number,
x_last_date_of_attendance => v_spa_rec.last_date_of_attendance,
x_dropped_by => v_spa_rec.dropped_by,
x_igs_pr_class_std_id => v_spa_rec.igs_pr_class_std_id,
x_primary_program_type => v_spa_rec.primary_program_type,
x_primary_prog_type_source => v_spa_rec.primary_prog_type_source,
x_catalog_cal_type => v_spa_rec.catalog_cal_type,
x_catalog_seq_num => v_spa_rec.catalog_seq_num,
x_key_program => v_spa_rec.key_program,
x_override_cmpl_dt => v_spa_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_spa_rec.manual_ovr_cmpl_dt_ind,
x_attribute_category => v_spa_rec.attribute_category,
x_attribute1 => v_spa_rec.attribute1,
x_attribute2 => v_spa_rec.attribute2,
x_attribute3 => v_spa_rec.attribute3,
x_attribute4 => v_spa_rec.attribute4,
x_attribute5 => v_spa_rec.attribute5,
x_attribute6 => v_spa_rec.attribute6,
x_attribute7 => v_spa_rec.attribute7,
x_attribute8 => v_spa_rec.attribute8,
x_attribute9 => v_spa_rec.attribute9,
x_attribute10 => v_spa_rec.attribute10,
x_attribute11 => v_spa_rec.attribute11,
x_attribute12 => v_spa_rec.attribute12,
x_attribute13 => v_spa_rec.attribute13,
x_attribute14 => v_spa_rec.attribute14,
x_attribute15 => v_spa_rec.attribute15,
x_attribute16 => v_spa_rec.attribute16,
x_attribute17 => v_spa_rec.attribute17,
x_attribute18 => v_spa_rec.attribute18,
x_attribute19 => v_spa_rec.attribute19,
x_attribute20 => v_spa_rec.attribute20,
x_future_dated_trans_flag => v_spa_rec.future_dated_trans_flag
);
END update_spa;
SELECT susaci.ROWID,
susaci.*
FROM igs_pr_susa_complete_int susaci
WHERE susaci.batch_id = cp_batch_id
FOR UPDATE;
SELECT spaci.ROWID,
spaci.*
FROM igs_pr_spa_complete_int spaci
WHERE spaci.batch_id = cp_batch_id
FOR UPDATE;
SELECT susa.ROWID
FROM igs_as_su_setatmpt susa
WHERE susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.end_dt IS NULL
AND susa.rqrmnts_complete_ind = 'N';
update_susa (
l_errbuf,
l_retcode,
l_susa_rowid,
NULL,
'N',
'Y',
v_susaci_rec.complete_dt,
'SYSTEM'
);
update_susa (
l_errbuf,
l_retcode,
l_susa_rowid,
v_susaci_rec.complete_dt,
'Y',
'N',
NULL,
NULL
);
UPDATE igs_pr_susa_complete_int
SET error_code = l_errbuf
WHERE CURRENT OF c_susaci;
DELETE FROM igs_pr_susa_complete_int
WHERE CURRENT OF c_susaci;
UPDATE igs_pr_susa_complete_int
SET error_code = l_susa_error_code
WHERE CURRENT OF c_susaci;
UPDATE igs_pr_susa_complete_int
SET error_code = l_spa_error_code
WHERE CURRENT OF c_susaci;
update_susa (
l_errbuf,
l_retcode,
v_susa_rec.ROWID,
NULL,
'N',
'Y',
v_spaci_rec.complete_dt,
'SYSTEM'
);
update_susa (
l_errbuf,
l_retcode,
v_susa_rec.ROWID,
v_spaci_rec.complete_dt,
'Y',
'N',
NULL,
NULL
);
update_spa (
l_errbuf,
l_retcode,
l_spa_rowid,
v_spaci_rec.complete_dt
);
UPDATE igs_pr_spa_complete_int
SET error_code = l_errbuf
WHERE CURRENT OF c_spaci;
DELETE FROM igs_pr_spa_complete_int
WHERE CURRENT OF c_spaci;
UPDATE igs_pr_spa_complete_int
SET error_code = l_spa_error_code
WHERE CURRENT OF c_spaci;