[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT unit_attempt_status
FROM IGS_EN_SU_ATTEMPT_ALL sua
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
uoo_id = cp_uoo_id
FOR UPDATE NOWAIT;
'SELECT sua.sup_unit_cd ,sua.sup_version_number
FROM igs_en_su_attempt sua
WHERE sua.uoo_id =:1
AND sua.person_id =:2
AND sua.course_cd =:3
AND EXISTS
(SELECT ''X'' FROM igs_ps_unit_ofr_opt uoo
WHERE uoo.sup_uoo_id IN (' ||p_drop_alluoo_ids||')
AND uoo.relation_type = ''SUBORDINATE''
AND uoo.uoo_id = sua.uoo_id)' USING p_uoo_id,p_person_id,p_course_cd;
SELECT unit_cd||'/'||unit_class unit_det
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id ;
'SELECT U.* FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V V
WHERE U.person_id = :1
AND U.course_cd = :2
AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
AND U.uoo_id NOT IN ('||p_drop_alluoo_ids||')
AND U.cal_type = V.teach_cal_type
AND U.ci_sequence_number = V.teach_ci_sequence_number
AND V.load_cal_type = :3
AND V.load_ci_sequence_number = :4'
USING p_person_id,p_course_cd,p_load_cal_type,p_load_sequence_number;
'SELECT U.* FROM IGS_EN_SU_ATTEMPT U, igs_ca_load_to_teach_v V
WHERE U.person_id = :1
AND U.course_cd = :2
AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
AND U.uoo_id NOT IN ('||p_drop_alluoo_ids||')
AND U.cal_type = V.teach_cal_type
AND U.ci_sequence_number= V.teach_ci_sequence_number
AND V.load_cal_type = :3
AND V.load_ci_sequence_number = :4'
USING p_person_id, p_course_cd,p_load_cal_type,p_load_sequence_number;
p_selected_uoo_ids => p_drop_uoo_ids, --- uooids that are to be dropped
p_ret_all_uoo_ids => l_drop_alluoo_ids, -- uooids that are to be dropping after adding subordinate units
p_ret_sub_uoo_ids => l_sub_drop_uoo_ids, -- retunrs the subordinate uooids if any in the uooids to be dropped
p_ret_nonsub_uoo_ids => l_nonsub_uoo_ids
);
'SELECT U.* FROM IGS_EN_SU_ATTEMPT U
WHERE person_id =:1
AND course_cd = :2
AND unit_attempt_status IN (''ENROLLED'',''INVALID'',''WAITLISTED'')
AND (cal_type,ci_sequence_number) IN
(SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = :3
AND load_ci_sequence_number =:4 )
AND uoo_id NOT IN('||l_drop_alluoo_ids||')'
USING p_person_id, p_course_cd, p_load_cal_type, p_load_sequence_number ;
'SELECT U.* FROM IGS_EN_SU_ATTEMPT U
WHERE person_id =:1
AND course_cd = :2
AND unit_attempt_status <> ''DUPLICATE''
AND (cal_type,ci_sequence_number) IN
(SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = :3
AND load_ci_sequence_number =:4 )
AND uoo_id IN('||l_drop_alluoo_ids||')'
USING p_person_id, p_course_cd, p_load_cal_type, p_load_sequence_number;
p_selected_uoo_ids IN VARCHAR2,
p_ret_all_uoo_ids OUT NOCOPY VARCHAR2,
p_ret_sub_uoo_ids OUT NOCOPY VARCHAR2,
p_ret_nonsub_uoo_ids OUT NOCOPY VARCHAR2
) AS
-------------------------------------------------------------------------------------------
-- Created by : Basanth Kumar D, Oracle Student Systems Oracle IDC
-- Purpose : This procedure adds if any subordinates of the units to be dropped are not inlcuded
-- in the drop list and reorders them with subordinates followed by superior units.
--Change History:
--Who When What
-------------------------------------------------------------------------------------------
-- cursor to get unit details
CURSOR get_unit_dtls (p_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
SELECT unit_cd||'/'||unit_class unit_det
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id ;
l_selected_uoo_ids VARCHAR2(3000);
'SELECT uoo.uoo_id sub_uoo_id, uoo.unit_cd, uoo.unit_class, uoo.sup_uoo_id
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo.sup_uoo_id IN ('||p_selected_uoo_ids||')
AND uoo.RELATION_TYPE = ''SUBORDINATE''
AND uoo.uoo_id IN
( SELECT uoo_id FROM igs_en_su_attempt
WHERE person_id = :1
AND course_cd = :2
AND cal_type = uoo.cal_type
AND ci_sequence_number = uoo.ci_sequence_number
AND unit_Attempt_status NOT IN (''DROPPED'', ''DISCONTIN'')
AND uoo_id NOT IN ('||p_selected_uoo_ids||')
)' USING p_person_id,p_course_cd;
l_selected_uoo_ids := p_selected_uoo_ids ||','|| l_grep_uoo_ids;
l_selected_uoo_ids := p_selected_uoo_ids;
p_selected_uoo_ids => l_selected_uoo_ids,
p_ret_all_uoo_ids => p_ret_all_uoo_ids,
p_ret_sub_uoo_ids => p_ret_sub_uoo_ids,
p_ret_nonsub_uoo_ids => p_ret_nonsub_uoo_ids,
p_delete_flag => 'N'
);
p_ret_all_uoo_ids := l_selected_uoo_ids;
SELECT ROWID,warn.*
FROM igs_en_std_warnings warn
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_term_cal_type
AND term_ci_sequence_number = p_term_ci_sequence_number
AND step_type = p_step_type
AND (
(p_step_type = 'DROP' AND uoo_id = p_uoo_id AND message_for = p_message_for AND message_name = p_message_name)
OR (p_step_type IN ('PROGRAM','PERSON') AND message_for = p_message_for)
OR (p_step_type = 'UNIT' AND uoo_id = p_uoo_id AND message_name = p_message_name)
);
IGS_EN_STD_WARNINGS_PKG.INSERT_ROW (
x_rowid => l_row_id,
x_warning_id => x_warning_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_term_cal_type => p_term_cal_type,
x_term_ci_sequence_number => p_term_ci_sequence_number,
x_message_for => p_message_for,
x_message_icon => p_message_icon,
x_message_name => p_message_name,
x_message_text => l_message_text,
x_message_action => p_message_action,
x_destination => p_destination,
x_p_parameters => p_parameters,
x_step_type => p_step_type,
x_session_id => igs_en_add_units_api.g_ss_session_id,
x_mode => 'R' );
ELSE -- update the row
IGS_EN_STD_WARNINGS_PKG.UPDATE_ROW (
x_rowid => l_warn_rec.rowid,
x_warning_id => l_warn_rec.warning_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_term_cal_type => p_term_cal_type,
x_term_ci_sequence_number => p_term_ci_sequence_number,
x_message_for => p_message_for,
x_message_icon => p_message_icon,
x_message_name => p_message_name,
x_message_text => l_message_text,
x_message_action => p_message_action,
x_destination => p_destination,
x_p_parameters => p_parameters,
x_step_type => p_step_type,
x_session_id => igs_en_add_units_api.g_ss_session_id,
x_mode => 'R' );
l_stmt := 'SELECT description
FROM igs_ad_adm_unit_stat_v
WHERE unit_attempt_status = ''DISCONTIN''
AND closed_ind = ''N''
AND administrative_unit_status IN (' || l_token || ')';
SELECT discontinuation_reason_cd
FROM igs_en_dcnt_reasoncd
WHERE NVL(closed_ind,'N') ='N'
AND dflt_ind ='Y'
AND dcnt_unit_ind ='Y'
AND s_discontinuation_reason_type IS NULL;
SELECT coo_id coo_id
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_course_cd ;
igs_en_add_units_api.delete_ss_warnings
(
p_person_id => p_person_id,
p_course_cd => p_course_cd,
p_load_cal_type => p_load_cal_type,
p_load_sequence_number => p_load_sequence_number,
p_uoo_id => p_uoo_id,
p_message_for => NULL,
p_delete_steps => 'DROP'
);
FUNCTION update_dropped_units (
p_person_id igs_en_su_attempt.person_id%TYPE,
p_course_cd igs_en_su_attempt.course_cd%TYPE,
p_uoo_ids VARCHAR2,
p_discontinuation_reason_cd VARCHAR2 )
RETURN VARCHAR2 AS
BEGIN
RETURN update_dropped_units(p_person_id, p_course_cd, p_uoo_ids, p_discontinuation_reason_cd, NULL );
END update_dropped_units;
FUNCTION update_dropped_units (
p_person_id igs_en_su_attempt.person_id%TYPE,
p_course_cd igs_en_su_attempt.course_cd%TYPE,
p_uoo_ids VARCHAR2,
p_discontinuation_reason_cd VARCHAR2,
p_admin_unit_status VARCHAR2)
RETURN VARCHAR2 AS
l_discont_reason_cd igs_en_dcnt_reasoncd.discontinuation_reason_cd%TYPE;
'SELECT sua.* FROM igs_En_su_attempt sua
WHERE person_id = :1
AND course_cd = :2
AND uoo_id IN ('||p_uoo_ids||')' USING p_person_id,p_course_cd ;
igs_En_su_Attempt_pkg.update_row( X_ROWID => upd_units_rec.row_id ,
X_PERSON_ID => upd_units_rec.person_id ,
X_COURSE_CD => upd_units_rec.course_cd ,
X_UNIT_CD => upd_units_rec.unit_cd ,
X_CAL_TYPE => upd_units_rec.cal_type ,
X_CI_SEQUENCE_NUMBER => upd_units_rec.ci_sequence_number ,
X_VERSION_NUMBER => upd_units_rec.version_number ,
X_LOCATION_CD => upd_units_rec.location_cd ,
X_UNIT_CLASS => upd_units_rec.unit_class ,
X_CI_START_DT => upd_units_rec.ci_start_dt ,
X_CI_END_DT => upd_units_rec.ci_end_dt ,
X_UOO_ID => upd_units_rec.uoo_id ,
X_ENROLLED_DT => upd_units_rec.enrolled_dt ,
X_UNIT_ATTEMPT_STATUS => upd_units_rec.unit_attempt_status ,
X_ADMINISTRATIVE_UNIT_STATUS => l_admin_unit_status ,
X_DISCONTINUED_DT => upd_units_rec.discontinued_dt ,
X_RULE_WAIVED_DT => upd_units_rec.rule_waived_dt ,
X_RULE_WAIVED_PERSON_ID => upd_units_rec.rule_waived_person_id ,
X_NO_ASSESSMENT_IND => upd_units_rec.no_assessment_ind ,
X_SUP_UNIT_CD => upd_units_rec.sup_unit_cd ,
X_SUP_VERSION_NUMBER => upd_units_rec.sup_version_number ,
X_EXAM_LOCATION_CD => upd_units_rec.exam_location_cd ,
X_ALTERNATIVE_TITLE => upd_units_rec.alternative_title ,
X_OVERRIDE_ENROLLED_CP => upd_units_rec.override_enrolled_cp ,
X_OVERRIDE_EFTSU => upd_units_rec.override_eftsu ,
X_OVERRIDE_ACHIEVABLE_CP => upd_units_rec.override_achievable_cp ,
X_OVERRIDE_OUTCOME_DUE_DT => upd_units_rec.override_outcome_due_dt ,
X_OVERRIDE_CREDIT_REASON => upd_units_rec.override_credit_reason ,
X_ADMINISTRATIVE_PRIORITY => upd_units_rec.administrative_priority ,
X_WAITLIST_DT => upd_units_rec.waitlist_dt ,
X_DCNT_REASON_CD => p_discontinuation_reason_cd , --- upodate with passed discont reason cd
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => upd_units_rec.gs_version_number ,
X_ENR_METHOD_TYPE => upd_units_rec.enr_method_type ,
X_FAILED_UNIT_RULE => upd_units_rec.failed_unit_rule ,
X_CART => upd_units_rec.cart ,
X_RSV_SEAT_EXT_ID => upd_units_rec.rsv_seat_ext_id ,
X_ORG_UNIT_CD => upd_units_rec.org_unit_cd ,
X_SESSION_ID => upd_units_rec.session_id ,
X_GRADING_SCHEMA_CODE => upd_units_rec.grading_schema_code ,
X_DEG_AUD_DETAIL_ID => upd_units_rec.deg_aud_detail_id ,
X_STUDENT_CAREER_TRANSCRIPT => upd_units_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => upd_units_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => upd_units_rec.attribute_category,
X_ATTRIBUTE1 => upd_units_rec.attribute1,
X_ATTRIBUTE2 => upd_units_rec.attribute2,
X_ATTRIBUTE3 => upd_units_rec.attribute3,
X_ATTRIBUTE4 => upd_units_rec.attribute4,
X_ATTRIBUTE5 => upd_units_rec.attribute5,
X_ATTRIBUTE6 => upd_units_rec.attribute6,
X_ATTRIBUTE7 => upd_units_rec.attribute7,
X_ATTRIBUTE8 => upd_units_rec.attribute8,
X_ATTRIBUTE9 => upd_units_rec.attribute9,
X_ATTRIBUTE10 => upd_units_rec.attribute10,
X_ATTRIBUTE11 => upd_units_rec.attribute11,
X_ATTRIBUTE12 => upd_units_rec.attribute12,
X_ATTRIBUTE13 => upd_units_rec.attribute13,
X_ATTRIBUTE14 => upd_units_rec.attribute14,
X_ATTRIBUTE15 => upd_units_rec.attribute15,
X_ATTRIBUTE16 => upd_units_rec.attribute16,
X_ATTRIBUTE17 => upd_units_rec.attribute17,
X_ATTRIBUTE18 => upd_units_rec.attribute18,
X_ATTRIBUTE19 => upd_units_rec.attribute19,
X_ATTRIBUTE20 => upd_units_rec.attribute20,
X_WAITLIST_MANUAL_IND => upd_units_rec.waitlist_manual_ind ,
X_WLST_PRIORITY_WEIGHT_NUM => upd_units_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => upd_units_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => upd_units_rec.core_indicator_code
);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_DROP_UNITS_API.update_dropped_units');
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_drop_units_api.update_dropped_units:',SQLERRM);
END update_dropped_units;