[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
WLST_PRIORITY_WEIGHT_NUM in call to igs_en_sua_api.update_unit_attempt
as part of Waitlist Enhancements Build. Enh Bug# 3052426.
******************************************************************/
CURSOR c_sua
IS
SELECT sua.ROWID, sua.*
FROM IGS_EN_SU_ATTEMPT sua
WHERE uoo_id=p_uoo_id
AND unit_attempt_status = 'WAITLISTED'
AND person_id = p_person_id
AND course_cd = p_course_cd;
SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points),
uv.points_override_ind
FROM igs_ps_unit_ver uv,
igs_ps_usec_cps cps,
igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cps.uoo_id(+) AND
uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uoo.uoo_id = p_uoo_id;
SELECT minimum_credit_points
FROM igs_ps_usec_cps
WHERE uoo_id = p_uoo_id;
SELECT party_number
FROM hz_parties
WHERE party_id=p_person_id;
igs_en_sua_api.update_unit_attempt(
x_rowid=>i.ROWID,
x_person_id=>i.person_id,
x_course_cd=>i.course_cd,
x_unit_cd=>i.unit_cd,
x_cal_type=>i.cal_type,
x_ci_sequence_number=>i.ci_sequence_number,
x_version_number=>i.version_number,
x_location_cd=>i.location_cd,
x_unit_class=>i.unit_class,
x_ci_start_dt=>i.ci_start_dt,
x_ci_end_dt=>i.ci_end_dt,
x_uoo_id=>i.uoo_id,
x_enrolled_dt=>SYSDATE,
x_unit_attempt_status=>'ENROLLED',
x_administrative_unit_status=>i.administrative_unit_status,
x_discontinued_dt=>i.discontinued_dt,
x_dcnt_reason_cd =>i.dcnt_reason_cd ,
x_rule_waived_dt=>i.rule_waived_dt,
x_rule_waived_person_id=>i.rule_waived_person_id,
x_no_assessment_ind=>i.no_assessment_ind,
x_sup_unit_cd=>i.sup_unit_cd,
x_sup_version_number=>i.sup_version_number,
x_exam_location_cd=>i.exam_location_cd,
x_alternative_title=>i.alternative_title,
x_override_enrolled_cp=>i.override_enrolled_cp,
x_override_eftsu=>i.override_eftsu,
x_override_achievable_cp=>i.override_achievable_cp,
x_override_outcome_due_dt=>i.override_outcome_due_dt,
x_override_credit_reason=>i.override_credit_reason,
x_administrative_priority=>i.administrative_priority,
x_waitlist_dt=> NULL, -- i.waitlist_dt, -- Update the waitlist_dt with 'Null'. This is as per the Bug# 2335455.
x_gs_version_number => i.gs_version_number,
x_enr_method_type => i.enr_method_type,
x_failed_unit_rule => i.failed_unit_rule,
x_cart => 'N', -- ( Enrolment Done - so removed from cart )
x_rsv_seat_ext_id => i.rsv_seat_ext_id,
x_mode=>'R',
x_org_unit_cd => i.org_unit_cd,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id => i.session_id,
-- Added the column grading schema as a part pf the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => i.grading_schema_code,
-- Added the column Deg_Aud_Detail_Id as part of
-- Degree Audit Interface build. Bug# 2033208 - by pradhakr
X_DEG_AUD_DETAIL_ID => i.deg_aud_detail_id ,
X_SUBTITLE => i.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => i.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => i.student_career_statistics,
X_ATTRIBUTE_CATEGORY => i.attribute_category,
X_ATTRIBUTE1 => i.attribute1,
X_ATTRIBUTE2 => i.attribute2,
X_ATTRIBUTE3 => i.attribute3,
X_ATTRIBUTE4 => i.attribute4,
X_ATTRIBUTE5 => i.attribute5,
X_ATTRIBUTE6 => i.attribute6,
X_ATTRIBUTE7 => i.attribute7,
X_ATTRIBUTE8 => i.attribute8,
X_ATTRIBUTE9 => i.attribute9,
X_ATTRIBUTE10 => i.attribute10,
X_ATTRIBUTE11 => i.attribute11,
X_ATTRIBUTE12 => i.attribute12,
X_ATTRIBUTE13 => i.attribute13,
X_ATTRIBUTE14 => i.attribute14,
X_ATTRIBUTE15 => i.attribute15,
X_ATTRIBUTE16 => i.attribute16,
X_ATTRIBUTE17 => i.attribute17,
X_ATTRIBUTE18 => i.attribute18,
X_ATTRIBUTE19 => i.attribute19,
X_ATTRIBUTE20 => i.attribute20,
X_WAITLIST_MANUAL_IND => i.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
-- Added WLST_PRIORITY_WEIGHT_NUM and WLST_PRIORITY_WEIGHT_NUM as part of Enh. Bug# 3052426 - ptandon
X_WLST_PRIORITY_WEIGHT_NUM => i.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => i.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => i.core_indicator_code
);
sarakshi 24-Feb-2003 Enh#2797116,modified cursor igs_ps_ofr_opt_cur to include delete_flag in
the where clause
ayedubat 30-MAY-2002 Added a new parameter,p_message_name to the Function:Enrp_Get_Rec_Window
call and dsiplaying the returning message for the bug fix:2337161.
kkillams 28-04-2003 modified igs_en_su_attempt_cur cursor where clause and impacted object,
due to change in Igs_En_Val_Sua.enrp_val_sua_dupl function signature
w.r.t. bug number 2829262
kkillams 19-June-03 Modified the validate_unit to add additional parameter p_course_cd, as existing cursor may fetch more than one program
and also modified the cursor igs_en_su_attempt_cur for bug 2937182
******************************************************************/
CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
cp_course_cd igs_en_su_attempt.course_cd%TYPE,
cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
IS
SELECT unit_attempt_status
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT version_number,
attendance_mode,
attendance_type,
course_attempt_status
FROM igs_en_stdnt_ps_att
WHERE course_cd = cp_course_cd
AND person_id = cp_person_id;
SELECT location_cd,
coo_id
FROM IGS_PS_OFR_OPT
WHERE course_cd = cp_course_cd
AND version_number = cp_version_number
AND cal_type = cp_cal_type
AND location_cd = cp_location_cd
AND attendance_mode = cp_attendance_mode
AND attendance_type = cp_attendance_type
AND delete_flag = 'N';
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE course_cd = p_course_cd
AND person_id = p_person_id;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';
sommukhe 27-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
abort_flag.
sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
sup_uoo_id,relation_type,default_enroll_flag.
vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
not_multiple_section_flag.
kkillams 24-04-2003 Passing NULL/0 depending on the IGS_EN_INCL_WLST_CP profile w.r.t. bug 2889975
ptandon 26-06-2003 Modified to display Person Number and Unit Code instead of Person Id and Uoo Id in the log.
Modified to display Max CP failure Error Message once if the student fails the same. Bug# 2841584
******************************************************************/
lv_validate_unit_message fnd_new_messages.message_name%TYPE;
/** Cursor for UPDATE dir enrolment /or inq_not_wlst in IGS_PS_UNIT_OFR_OPT **/
CURSOR c_unit_ofr_opt IS
SELECT ROWID,
ofr.*
FROM igs_ps_unit_ofr_opt ofr
WHERE uoo_id = p_uoo_id;
/** Cursor for UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
/** Cursor for UPDATE actual enrolment in igs_ps_rsv_ext **/
CURSOR c_rsv_ext IS
SELECT ROWID,
rsv.*
FROM igs_ps_rsv_ext rsv
WHERE rsv_ext_id = p_rsv_seat_ext_id;
/** Cursor for UPDATE actual enrolment in igs_ps_rsv_ext **/
/** Cursor for Delete record from igs_en_su_attempt**/
CURSOR c_del_sua IS
SELECT ROWID
FROM igs_en_su_attempt
WHERE uoo_id = p_uoo_id
AND person_id = p_person_id
AND course_cd = p_course_cd;
/** Cursor for Delete record from igs_en_su_attempt**/
CURSOR c_find_person_no IS
SELECT party_number
FROM hz_parties
WHERE party_id=p_person_id;
SELECT asses_chrg_for_wlst_stud
FROM IGS_EN_OR_UNIT_WLST
WHERE cal_type = p_cal_type AND
closed_flag = 'N' AND
org_unit_cd = (SELECT NVL(uoo.owner_org_unit_cd, uv.owner_org_unit_cd)
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_unit_ver uv
WHERE uoo.uoo_id = p_uoo_id AND
uv.unit_cd = uoo.unit_cd AND
uv.version_number = uoo.version_number);
SELECT include_waitlist_cp_flag
FROM IGS_EN_INST_WL_STPS;
/** UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
OPEN c_unit_ofr_opt;
igs_ps_unit_ofr_opt_pkg.update_row
(x_rowid => v_unit_ofr_opt_rec.ROWID,
x_unit_cd => v_unit_ofr_opt_rec.unit_cd,
x_version_number => v_unit_ofr_opt_rec.version_number ,
x_cal_type => v_unit_ofr_opt_rec.cal_type,
x_ci_sequence_number => v_unit_ofr_opt_rec.ci_sequence_number,
x_location_cd => v_unit_ofr_opt_rec.location_cd,
x_unit_class => v_unit_ofr_opt_rec.unit_class,
x_uoo_id => v_unit_ofr_opt_rec.uoo_id,
x_ivrs_available_ind => v_unit_ofr_opt_rec.ivrs_available_ind,
x_call_number => v_unit_ofr_opt_rec.call_number,
x_unit_section_status => v_unit_ofr_opt_rec.unit_section_status,
x_unit_section_start_date => v_unit_ofr_opt_rec.unit_section_start_date,
x_unit_section_end_date => v_unit_ofr_opt_rec.unit_section_end_date,
x_enrollment_actual => v_unit_ofr_opt_rec.enrollment_actual,
x_waitlist_actual => v_unit_ofr_opt_rec.waitlist_actual,
x_offered_ind => v_unit_ofr_opt_rec.offered_ind,
x_state_financial_aid => v_unit_ofr_opt_rec.state_financial_aid,
x_grading_schema_prcdnce_ind => v_unit_ofr_opt_rec.grading_schema_prcdnce_ind,
x_federal_financial_aid => v_unit_ofr_opt_rec.federal_financial_aid,
x_unit_quota => v_unit_ofr_opt_rec.unit_quota,
x_unit_quota_reserved_places => v_unit_ofr_opt_rec.unit_quota_reserved_places,
x_institutional_financial_aid => v_unit_ofr_opt_rec.institutional_financial_aid,
x_unit_contact => v_unit_ofr_opt_rec.unit_contact,
x_grading_schema_cd => v_unit_ofr_opt_rec.grading_schema_cd,
x_gs_version_number => v_unit_ofr_opt_rec.gs_version_number,
x_owner_org_unit_cd => v_unit_ofr_opt_rec.owner_org_unit_cd,
x_attendance_required_ind => v_unit_ofr_opt_rec.attendance_required_ind,
x_reserved_seating_allowed => v_unit_ofr_opt_rec.reserved_seating_allowed,
x_special_permission_ind => v_unit_ofr_opt_rec.special_permission_ind,
x_ss_display_ind => v_unit_ofr_opt_rec.ss_display_ind,
x_mode => 'R',
x_ss_enrol_ind => v_unit_ofr_opt_rec.ss_enrol_ind,
x_dir_enrollment => NVL(v_unit_ofr_opt_rec.dir_enrollment,0) + 1, -- Only Column Updated to add 1
x_enr_from_wlst => v_unit_ofr_opt_rec.enr_from_wlst,
x_inq_not_wlst => v_unit_ofr_opt_rec.inq_not_wlst,
x_rev_account_cd => v_unit_ofr_opt_rec.rev_account_cd,
x_anon_unit_grading_ind => v_unit_ofr_opt_rec.anon_unit_grading_ind ,
x_anon_assess_grading_ind => v_unit_ofr_opt_rec.anon_assess_grading_ind,
x_non_std_usec_ind => v_unit_ofr_opt_rec.non_std_usec_ind,
x_auditable_ind => v_unit_ofr_opt_rec.auditable_ind,
x_audit_permission_ind => v_unit_ofr_opt_rec.audit_permission_ind,
x_not_multiple_section_flag => v_unit_ofr_opt_rec.not_multiple_section_flag,
x_sup_uoo_id => v_unit_ofr_opt_rec.sup_uoo_id,
x_relation_type => v_unit_ofr_opt_rec.relation_type,
x_default_enroll_flag => v_unit_ofr_opt_rec.default_enroll_flag,
x_abort_flag => v_unit_ofr_opt_rec.abort_flag
);
/** UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
RETURN TRUE;
/*** UPDATE for IGS_PS_RSV_EXT -For actual seat enrolled - 1 **/
OPEN c_rsv_ext;
igs_ps_rsv_ext_pkg.update_row(
x_rowid =>v_rsv_ext_rec.ROWID,
x_rsv_ext_id=>v_rsv_ext_rec.rsv_ext_id,
x_uoo_id=>v_rsv_ext_rec.uoo_id,
x_priority_id=>v_rsv_ext_rec.priority_id,
x_preference_id=>v_rsv_ext_rec.preference_id,
x_rsv_level=>v_rsv_ext_rec.rsv_level,
x_actual_seat_enrolled=>NVL(v_rsv_ext_rec.actual_seat_enrolled,0) - 1,
x_mode => 'R');
/*** UPDATE for IGS_PS_RSV_EXT -For actual seat enrolled - 1 **/
/**** DELETE from igs_en_su_attempt for uoo_id***/
OPEN c_del_sua ;
Igs_En_Su_Attempt_Pkg.delete_row(v_del_sua_rec.ROWID);
/**** DELETE from igs_en_su_attempt for uoo_id***/
/*** update igs_ps_unit_ofr inq_not_wlst by 1 **/
OPEN c_unit_ofr_opt;
igs_ps_unit_ofr_opt_pkg.update_row
(x_rowid => v_unit_ofr_opt_rec.ROWID,
x_unit_cd => v_unit_ofr_opt_rec.unit_cd,
x_version_number => v_unit_ofr_opt_rec.version_number ,
x_cal_type => v_unit_ofr_opt_rec.cal_type,
x_ci_sequence_number => v_unit_ofr_opt_rec.ci_sequence_number,
x_location_cd => v_unit_ofr_opt_rec.location_cd,
x_unit_class => v_unit_ofr_opt_rec.unit_class,
x_uoo_id => v_unit_ofr_opt_rec.uoo_id,
x_ivrs_available_ind => v_unit_ofr_opt_rec.ivrs_available_ind,
x_call_number => v_unit_ofr_opt_rec.call_number,
x_unit_section_status => v_unit_ofr_opt_rec.unit_section_status,
x_unit_section_start_date => v_unit_ofr_opt_rec.unit_section_start_date,
x_unit_section_end_date => v_unit_ofr_opt_rec.unit_section_end_date,
x_enrollment_actual => v_unit_ofr_opt_rec.enrollment_actual,
x_waitlist_actual => v_unit_ofr_opt_rec.waitlist_actual,
x_offered_ind => v_unit_ofr_opt_rec.offered_ind,
x_state_financial_aid => v_unit_ofr_opt_rec.state_financial_aid,
x_grading_schema_prcdnce_ind => v_unit_ofr_opt_rec.grading_schema_prcdnce_ind,
x_federal_financial_aid => v_unit_ofr_opt_rec.federal_financial_aid,
x_unit_quota => v_unit_ofr_opt_rec.unit_quota,
x_unit_quota_reserved_places => v_unit_ofr_opt_rec.unit_quota_reserved_places,
x_institutional_financial_aid => v_unit_ofr_opt_rec.institutional_financial_aid,
x_unit_contact => v_unit_ofr_opt_rec.unit_contact,
x_grading_schema_cd => v_unit_ofr_opt_rec.grading_schema_cd,
x_gs_version_number => v_unit_ofr_opt_rec.gs_version_number,
x_owner_org_unit_cd => v_unit_ofr_opt_rec.owner_org_unit_cd,
x_attendance_required_ind => v_unit_ofr_opt_rec.attendance_required_ind,
x_reserved_seating_allowed => v_unit_ofr_opt_rec.reserved_seating_allowed,
x_special_permission_ind => v_unit_ofr_opt_rec.special_permission_ind,
x_ss_display_ind => v_unit_ofr_opt_rec.ss_display_ind,
x_mode => 'R',
x_ss_enrol_ind => v_unit_ofr_opt_rec.ss_enrol_ind,
x_dir_enrollment => v_unit_ofr_opt_rec.dir_enrollment,
x_enr_from_wlst => v_unit_ofr_opt_rec.enr_from_wlst,
x_inq_not_wlst => NVL(v_unit_ofr_opt_rec.inq_not_wlst,0) + 1,
x_rev_account_cd => v_unit_ofr_opt_rec.rev_account_cd ,
x_anon_unit_grading_ind => v_unit_ofr_opt_rec.anon_unit_grading_ind ,
x_anon_assess_grading_ind => v_unit_ofr_opt_rec.anon_assess_grading_ind,
x_non_std_usec_ind => v_unit_ofr_opt_rec.non_std_usec_ind ,
x_auditable_ind => v_unit_ofr_opt_rec.auditable_ind,
x_audit_permission_ind => v_unit_ofr_opt_rec.audit_permission_ind,
x_not_multiple_section_flag => v_unit_ofr_opt_rec.not_multiple_section_flag,
x_sup_uoo_id => v_unit_ofr_opt_rec.sup_uoo_id,
x_relation_type => v_unit_ofr_opt_rec.relation_type,
x_default_enroll_flag => v_unit_ofr_opt_rec.default_enroll_flag,
x_abort_flag => v_unit_ofr_opt_rec.abort_flag
);
/*** update igs_ps_unit_ofr inq_not_wlst by 1 **/
RETURN FALSE;
SELECT cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
waitlist_actual,
enrollment_actual
FROM igs_ps_unit_ofr_opt
WHERE unit_section_status = 'HOLD'
AND uoo_id = p_uoo_id;
SELECT NVL (usec.enrollment_maximum, NVL(uv.enrollment_maximum,9999) ) enrollment_maximum,
usec.max_students_per_waitlist
FROM igs_ps_usec_lim_wlst usec,
igs_ps_unit_ver uv,
igs_ps_unit_ofr_opt uoo
WHERE uoo.unit_cd = uv.unit_cd
AND uoo.version_number = uv.version_number
AND uoo.uoo_id = usec.uoo_id (+)
AND uoo.uoo_id = p_uoo_id;
SELECT ROWID,
person_id,
course_cd,
unit_cd,
version_number,
cal_type,
ci_sequence_number,
location_cd,
unit_class,
ci_start_dt,
ci_end_dt,
uoo_id,
enrolled_dt,
unit_attempt_status,
administrative_unit_status,
discontinued_dt,
rule_waived_dt,
rule_waived_person_id,
no_assessment_ind,
sup_unit_cd,
sup_version_number,
exam_location_cd,
alternative_title,
override_enrolled_cp,
override_eftsu,
override_achievable_cp,
override_outcome_due_dt,
override_credit_reason,
administrative_priority,
waitlist_dt,
rsv_seat_ext_id,
enr_method_type
FROM igs_en_su_attempt
WHERE uoo_id=p_uoo_id
AND NVL(administrative_priority,0) <> 0
AND unit_attempt_status = 'WAITLISTED'
ORDER BY administrative_priority;
SELECT load_cal_type,load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_cal_type AND
teach_ci_sequence_number = p_sequence_number AND
load_end_dt >= TRUNC(SYSDATE)
ORDER BY load_start_dt;
p_tab_fail_mail.DELETE;
p_tab_succ_mail.DELETE;
filtered_sorted_tab.DELETE;
val_success_adm_persons_tab.DELETE;
/****update success mail pl/sql structure***/
ln_tab_succ_mail_ind := NVL(ln_tab_succ_mail_ind,0) + 1; -- Increment succ records by 1
/***Update failure mail pl/sql structure**/
ln_tab_fail_mail_ind := NVL(ln_tab_fail_mail_ind,0) + 1; -- Increment by failure records by 1
SELECT ctl.load_cal_type
FROM igs_ca_teach_to_load_v ctl,
igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cp_uoo_id
AND ctl.teach_cal_type = uoo.cal_type
AND ctl.teach_ci_sequence_number = uoo.ci_sequence_number
AND ctl.load_end_dt >= TRUNC(SYSDATE)
ORDER BY ctl.load_start_dt;
SELECT waitlist_allowed_flag
FROM igs_en_inst_wl_stps;
SELECT waitlist_alwd
FROM igs_en_inst_wlst_opt
WHERE cal_type = cp_load_cal_type;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';