[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified update_enroll_stats
|| ctyagi 22-sept-2005 Added p_enroll_from_waitlsit_flag as a part of bug ||
4580204
||ctyagi 26-sept-2005 Removed p_enroll_from_waitlsit_flag as a part of bug ||
4580204
|| (reverse chronological order - newest change first) ||
||==============================================================================||
*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_EN_IVR_PUB';
|| for students selected program/career and term.
||
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| stutta 11-Feb-2004 Passing new parameter p_enrolled_dt as SYSDATE in
|| call to validate_enroll_validate.
|| bdeviset 23-Aug-2006 Passed extra param p_ss_session_id for enrp_ss_val_person_step added as part of bug# 5306874
|| (reverse chronological order - newest change first)
*/
l_api_name VARCHAR2(30) := 'ENROLL_CART';
SELECT sua.uoo_id,
sua.enr_method_type,
sua.cart
FROM igs_en_su_attempt sua
WHERE sua.person_id = l_person_id
AND sua.course_cd = l_primary_code
AND (sua.cal_type, sua.ci_sequence_number) IN (SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = l_cal_type
AND load_ci_sequence_number = l_ci_sequence_number)
AND sua.unit_attempt_status IN ('INVALID','UNCONFIRM') ;
SELECT sua.row_id
FROM igs_en_su_attempt sua
WHERE unit_attempt_status = 'UNCONFIRM' AND
cart = 'I' AND
person_id = cp_person_id AND
course_cd = cp_program_cd AND
version_number = cp_version_number;
igs_en_su_attempt_pkg.delete_row(rec_sua.row_id);
|| Purpose : The procedure will drop the section indicated by the student for the selected program/career and term.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
*/
l_api_name CONSTANT VARCHAR2(30) := 'drop_section_by_call_number';
SELECT
sua.unit_cd unit_code ,
sua1.unit_class unit_class ,
sua.version_number unit_version ,
ci.alternate_code teach_alternate_code ,
sua.call_number call_number ,
SUBSTR(sua.gradingschema,1,10) grading_schema ,
sua.creditpoints credit_points ,
sua.unit_attempt_status unit_attempt_status ,
sua.uas_meaning uas_meaning ,
sua.uoo_id uoo_id ,
sua.administrative_priority administrative_priority
FROM
igs_ss_en_sua_dtls_v sua,
igs_en_su_attempt sua1,
igs_ca_inst ci
WHERE
ci.cal_type = sua.cal_type AND
ci.sequence_number = sua.ci_sequence_number AND
sua.person_id = cp_person_id AND
sua.course_cd = cp_program_cd AND
(sua.cal_type ,sua.ci_sequence_number) IN
(SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_load_cal_type AND
load_ci_sequence_number = cp_load_ci_sequence_number) AND
sua.unit_attempt_status NOT IN (cp_unit_status_not_shown) AND
sua1.person_id = sua.person_id AND
sua1.course_cd = sua.course_cd AND
sua1.uoo_id = sua.uoo_id;
SELECT sua.call_number
FROM igs_ss_en_sua_dtls_v sua,
igs_ca_inst ci
WHERE
ci.cal_type = sua.cal_type AND
ci.sequence_number = sua.ci_sequence_number AND
sua.person_id = cp_person_id AND
sua.course_cd = cp_program_cd AND
(sua.cal_type ,sua.ci_sequence_number) IN
(SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_load_cal_type AND
load_ci_sequence_number = cp_load_ci_sequence_number) AND
sua.unit_attempt_status IN ('INVALID','UNCONFIRM');
SELECT sua.rowid
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;
igs_en_su_attempt_pkg.delete_row(x_rowid => l_rec_cart.rowid);
PROCEDURE update_enroll_stats(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_person_number IN VARCHAR2,
p_career IN VARCHAR2,
p_program_code IN VARCHAR2,
p_term_alt_code IN VARCHAR2,
p_call_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2) IS
/*
|| Created By : Nalin Kumar
|| Created On : 16-Jan-2003
|| Purpose : To Update the statistic in enrollment.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| 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 18-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.
*/
l_api_name CONSTANT VARCHAR2(30) := 'update_enroll_stats';
SELECT puo.*
FROM igs_ps_unit_ofr_opt puo
WHERE puo.uoo_id = cp_uoo_id;
SAVEPOINT update_enroll_stats;
igs_ps_unit_ofr_opt_pkg.update_row(
X_ROWID => l_rec_enroll_stat.row_id ,
X_UNIT_CD => l_rec_enroll_stat.unit_cd ,
X_VERSION_NUMBER => l_rec_enroll_stat.version_number ,
X_CAL_TYPE => l_rec_enroll_stat.cal_type ,
X_CI_SEQUENCE_NUMBER => l_rec_enroll_stat.ci_sequence_number ,
X_LOCATION_CD => l_rec_enroll_stat.location_cd ,
X_UNIT_CLASS => l_rec_enroll_stat.unit_class ,
X_UOO_ID => l_rec_enroll_stat.uoo_id ,
X_IVRS_AVAILABLE_IND => l_rec_enroll_stat.ivrs_available_ind ,
X_CALL_NUMBER => l_rec_enroll_stat.call_number ,
X_UNIT_SECTION_STATUS => l_rec_enroll_stat.unit_section_status ,
X_UNIT_SECTION_START_DATE => l_rec_enroll_stat.unit_section_start_date ,
X_UNIT_SECTION_END_DATE => l_rec_enroll_stat.unit_section_end_date ,
X_ENROLLMENT_ACTUAL => l_rec_enroll_stat.enrollment_actual ,
X_WAITLIST_ACTUAL => l_rec_enroll_stat.waitlist_actual ,
X_OFFERED_IND => l_rec_enroll_stat.offered_ind ,
X_STATE_FINANCIAL_AID => l_rec_enroll_stat.state_financial_aid ,
X_GRADING_SCHEMA_PRCDNCE_IND => l_rec_enroll_stat.grading_schema_prcdnce_ind ,
X_FEDERAL_FINANCIAL_AID => l_rec_enroll_stat.federal_financial_aid ,
X_UNIT_QUOTA => l_rec_enroll_stat.unit_quota ,
X_UNIT_QUOTA_RESERVED_PLACES => l_rec_enroll_stat.unit_quota_reserved_places ,
X_INSTITUTIONAL_FINANCIAL_AID => l_rec_enroll_stat.institutional_financial_aid,
X_UNIT_CONTACT => l_rec_enroll_stat.unit_contact ,
X_GRADING_SCHEMA_CD => l_rec_enroll_stat.grading_schema_cd ,
X_GS_VERSION_NUMBER => l_rec_enroll_stat.gs_version_number ,
X_OWNER_ORG_UNIT_CD => l_rec_enroll_stat.owner_org_unit_cd ,
X_ATTENDANCE_REQUIRED_IND => l_rec_enroll_stat.attendance_required_ind ,
X_RESERVED_SEATING_ALLOWED => l_rec_enroll_stat.reserved_seating_allowed ,
X_SPECIAL_PERMISSION_IND => l_rec_enroll_stat.special_permission_ind ,
X_SS_DISPLAY_IND => l_rec_enroll_stat.ss_display_ind ,
X_SS_ENROL_IND => l_rec_enroll_stat.ss_enrol_ind ,
X_DIR_ENROLLMENT => l_rec_enroll_stat.dir_enrollment ,
X_ENR_FROM_WLST => l_rec_enroll_stat.enr_from_wlst ,
X_INQ_NOT_WLST => NVL(l_rec_enroll_stat.inq_not_wlst,0) + 1 ,
X_REV_ACCOUNT_CD => l_rec_enroll_stat.rev_account_cd ,
X_ANON_UNIT_GRADING_IND => l_rec_enroll_stat.anon_unit_grading_ind ,
X_ANON_ASSESS_GRADING_IND => l_rec_enroll_stat.anon_assess_grading_ind ,
X_NON_STD_USEC_IND => l_rec_enroll_stat.non_std_usec_ind ,
X_AUDITABLE_IND => l_rec_enroll_stat.auditable_ind ,
X_AUDIT_PERMISSION_IND => l_rec_enroll_stat.audit_permission_ind ,
x_not_multiple_section_flag => l_rec_enroll_stat.not_multiple_section_flag ,
x_sup_uoo_id => l_rec_enroll_stat.sup_uoo_id ,
x_relation_type => l_rec_enroll_stat.relation_type ,
x_default_enroll_flag => l_rec_enroll_stat.default_enroll_flag ,
x_abort_flag => l_rec_enroll_stat.abort_flag
);
ROLLBACK TO update_enroll_stats;
ROLLBACK TO update_enroll_stats;
ROLLBACK TO update_enroll_stats;
END update_enroll_stats;
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd;
|| further with getting the term and career/program selected by student with
|| minimum OSS interaction.
|| This procedure will also validate the basic assumption that term is setup for
|| IVR and student has at least one active career/program to perform enrollment
|| activities. If any of these were not valid it would return with error message.
|| This is an API published /available to 3rd party
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
******************************************************************************************/
IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_person_details';
SELECT course_type,
course_cd,
version_number
FROM igs_en_sca_v
WHERE primary_program_type ='PRIMARY'
AND Course_attempt_status IN ('ENROLLED','INACTIVE')
AND person_id = lc_person_id;
SELECT course_type,
course_cd,
version_number
FROM igs_en_sca_v
WHERE course_attempt_status IN ('ENROLLED','INACTIVE')
AND person_id = lc_person_id;