[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1);
Purpose : This function will do validations after inserting records of Unit Section NOTES.
.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
v_message_name VARCHAR2(30);
p_insert_update VARCHAR2)
AS
/***********************************************************************************************
Created By : Sommukhe
Date Created By:
Purpose : This function will do validations before inserting records of Unit Section Assesments.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_check_host(cp_uoo_id IN igs_ps_us_exam_meet.exam_meet_group_id%TYPE) IS
SELECT host
FROM igs_ps_us_exam_meet
WHERE uoo_id = cp_uoo_id;
SELECT 'X'
FROM igs_ad_building_all
WHERE building_id = cp_building_id
AND location_cd = cp_location_cd
AND closed_ind = 'N';
SELECT 'X'
FROM igs_ad_room_all
WHERE room_id = cp_room_id
AND building_id =cp_building_id
AND closed_ind = 'N';
IF p_insert_update = 'U' THEN
OPEN c_check_host(p_n_uoo_id );
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_uoo_id
AND NVL(enrollment_actual,0) > 0;
p_insert VARCHAR2)
AS
/***********************************************************************************************
Created By : sommukhe
Date Created By:
Purpose :
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR cp_st_en_dt(cp_cal_type VARCHAR ,cp_seq_number NUMBER) IS
SELECT start_dt, end_dt
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT 'X'
FROM igs_ps_unitass_item
WHERE uoo_id = cp_n_uoo_id
AND reference = cp_reference
AND logical_delete_dt IS NULL;
SELECT *
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_uoo_id;
SELECT *
FROM igs_ps_unitass_item
WHERE ass_id =cp_assessment_id
AND uoo_id =cp_uoo_id
AND sequence_number =cp_sequence_number ;
SELECT assessment_type
FROM igs_as_assessmnt_itm
WHERE ass_id = cp_assessment_id;
SELECT 'X'
FROM igs_as_appr_grd_sch agrs,
igs_as_grd_schema grd
WHERE grd.grading_schema_cd = agrs.grading_schema_cd
AND grd.version_number = agrs.gs_version_number
AND agrs.unit_cd = cp_unit_cd
AND agrs.version_number = cp_version_number
AND agrs.assessment_type = cp_assessment_type
AND agrs.closed_ind = 'N'
AND agrs.grading_schema_cd = cp_grading_schema_cd
AND agrs.gs_version_number = cp_gs_version_number;
SELECT 'X'
FROM igs_as_grd_schema
WHERE grading_schema_cd = cp_grading_schema_cd
AND version_number = cp_gs_version_number
AND NVL(end_dt,SYSDATE) >= SYSDATE
AND grading_schema_type = 'ASSESSMENT_ITEM';
IF p_insert = 'I' THEN
OPEN c_reference(p_n_uoo_id,p_unitass_item_rec.reference);
ELSIF NVL(p_unitass_item_rec.logical_delete_dt, igs_ge_date.igsdate ('1900/01/01')) = igs_ge_date.igsdate ('1900/01/01') THEN
IF igs_ps_val_uai.assp_val_uai_opt_ref (
p_unitass_item_rec.unit_cd,
p_unitass_item_rec.version_number,
p_cal_type, p_ci_sequence_number,
p_unitass_item_rec.sequence_number,
p_unitass_item_rec.reference,
p_unitass_item_rec.assessment_id,
igs_as_gen_001.assp_get_ai_a_type (p_unitass_item_rec.assessment_id),
l_c_message) = FALSE THEN
fnd_message.set_name ( 'IGS', l_c_message );
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all uoo
WHERE uoo.unit_section_status IN ('OPEN','PLANNED','FULLWAITOK','CLOSED')
AND uoo.relation_type='NONE'
AND uoo.cal_type=p_c_cal_type
AND uoo.ci_sequence_number =p_n_seq_num
AND uoo.uoo_id=p_n_uoo_id;
SELECT *
FROM igs_en_su_attempt
WHERE uoo_id=cp_uoo_id
AND unit_attempt_status='WAITLISTED';
SELECT 'X'
FROM igs_ps_usec_x_grpmem
WHERE uoo_id=cp_uoo_id;
SELECT waitlist_actual
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;
IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
X_ROWID => l_c_fetch_record_cur.row_id,
x_waitlist_manual_ind => l_c_fetch_record_cur.waitlist_manual_ind,
X_PERSON_ID => l_c_fetch_record_cur.person_id,
X_COURSE_CD => l_c_fetch_record_cur.course_cd,
X_UNIT_CD => l_c_fetch_record_cur.unit_cd,
X_CAL_TYPE => l_c_fetch_record_cur.cal_type,
X_CI_SEQUENCE_NUMBER => l_c_fetch_record_cur.ci_sequence_number,
X_VERSION_NUMBER => l_c_fetch_record_cur.version_number,
X_LOCATION_CD => l_c_fetch_record_cur.location_cd,
X_UNIT_CLASS => l_c_fetch_record_cur.unit_class,
X_CI_START_DT => l_c_fetch_record_cur.ci_start_dt,
X_CI_END_DT => l_c_fetch_record_cur.ci_end_dt,
X_UOO_ID => l_c_fetch_record_cur.uoo_id,
X_ENROLLED_DT => l_c_fetch_record_cur.enrolled_dt,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => l_c_fetch_record_cur.administrative_unit_status,
X_DISCONTINUED_DT => nvl(l_c_fetch_record_cur.discontinued_dt, l_sysdate),
X_RULE_WAIVED_DT => l_c_fetch_record_cur.rule_waived_dt,
X_RULE_WAIVED_PERSON_ID => l_c_fetch_record_cur.rule_waived_person_id,
X_NO_ASSESSMENT_IND => l_c_fetch_record_cur.no_assessment_ind,
X_SUP_UNIT_CD => l_c_fetch_record_cur.sup_unit_cd,
X_SUP_VERSION_NUMBER => l_c_fetch_record_cur.sup_version_number,
X_EXAM_LOCATION_CD => l_c_fetch_record_cur.exam_location_cd,
X_ALTERNATIVE_TITLE => l_c_fetch_record_cur.alternative_title,
X_OVERRIDE_ENROLLED_CP => l_c_fetch_record_cur.override_enrolled_cp,
X_OVERRIDE_EFTSU => l_c_fetch_record_cur.override_eftsu,
X_OVERRIDE_ACHIEVABLE_CP => l_c_fetch_record_cur.override_achievable_cp,
X_OVERRIDE_OUTCOME_DUE_DT => l_c_fetch_record_cur.override_outcome_due_dt,
X_OVERRIDE_CREDIT_REASON => l_c_fetch_record_cur.override_credit_reason,
X_ADMINISTRATIVE_PRIORITY => l_c_fetch_record_cur.administrative_priority,
X_WAITLIST_DT => l_c_fetch_record_cur.waitlist_dt,
X_DCNT_REASON_CD => l_c_fetch_record_cur.dcnt_reason_cd,
X_MODE => 'R',
X_GS_VERSION_NUMBER => l_c_fetch_record_cur.gs_version_number,
X_ENR_METHOD_TYPE => l_c_fetch_record_cur.enr_method_type,
X_FAILED_UNIT_RULE => l_c_fetch_record_cur.failed_unit_rule,
X_CART => l_c_fetch_record_cur.cart,
X_RSV_SEAT_EXT_ID => l_c_fetch_record_cur.rsv_seat_ext_id ,
X_ORG_UNIT_CD => l_c_fetch_record_cur.org_unit_cd,
X_SESSION_ID => l_c_fetch_record_cur.session_id,
X_GRADING_SCHEMA_CODE => l_c_fetch_record_cur.grading_schema_code,
X_DEG_AUD_DETAIL_ID => l_c_fetch_record_cur.deg_aud_detail_id,
X_SUBTITLE => l_c_fetch_record_cur.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => l_c_fetch_record_cur.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => l_c_fetch_record_cur.student_career_statistics,
X_ATTRIBUTE_CATEGORY => l_c_fetch_record_cur.attribute_category,
X_ATTRIBUTE1 => l_c_fetch_record_cur.attribute1,
X_ATTRIBUTE2 => l_c_fetch_record_cur.attribute2,
X_ATTRIBUTE3 => l_c_fetch_record_cur.attribute3,
X_ATTRIBUTE4 => l_c_fetch_record_cur.attribute4,
X_ATTRIBUTE5 => l_c_fetch_record_cur.attribute5,
X_ATTRIBUTE6 => l_c_fetch_record_cur.attribute6,
X_ATTRIBUTE7 => l_c_fetch_record_cur.attribute7,
X_ATTRIBUTE8 => l_c_fetch_record_cur.attribute8,
X_ATTRIBUTE9 => l_c_fetch_record_cur.attribute9,
X_ATTRIBUTE10 => l_c_fetch_record_cur.attribute10,
X_ATTRIBUTE11 => l_c_fetch_record_cur.attribute11,
X_ATTRIBUTE12 => l_c_fetch_record_cur.attribute12,
X_ATTRIBUTE13 => l_c_fetch_record_cur.attribute13,
X_ATTRIBUTE14 => l_c_fetch_record_cur.attribute14,
X_ATTRIBUTE15 => l_c_fetch_record_cur.attribute15,
X_ATTRIBUTE16 => l_c_fetch_record_cur.attribute16,
X_ATTRIBUTE17 => l_c_fetch_record_cur.attribute17,
X_ATTRIBUTE18 => l_c_fetch_record_cur.attribute18,
X_ATTRIBUTE19 => l_c_fetch_record_cur.attribute19,
X_ATTRIBUTE20 => l_c_fetch_record_cur.attribute20,
X_WLST_PRIORITY_WEIGHT_NUM => l_c_fetch_record_cur.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_c_fetch_record_cur.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => l_c_fetch_record_cur.core_indicator_code
) ;
PROCEDURE update_usec_status(p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
p_unit_section_status IN igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
CURSOR cur_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
SELECT *
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;
igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_cur_usec.row_id,
x_unit_cd =>l_cur_usec.unit_cd,
x_version_number =>l_cur_usec.version_number,
x_cal_type =>l_cur_usec.cal_type,
x_ci_sequence_number =>l_cur_usec.ci_sequence_number,
x_location_cd =>l_cur_usec.location_cd,
x_unit_class =>l_cur_usec.unit_class,
x_uoo_id =>l_cur_usec.uoo_id,
x_ivrs_available_ind =>l_cur_usec.ivrs_available_ind,
x_call_number =>l_cur_usec.call_number,
x_unit_section_status =>p_unit_section_status,
x_unit_section_start_date =>l_cur_usec.unit_section_start_date,
x_unit_section_end_date =>l_cur_usec.unit_section_end_date,
x_enrollment_actual =>l_cur_usec.enrollment_actual,
x_waitlist_actual =>l_cur_usec.waitlist_actual,
x_offered_ind =>l_cur_usec.offered_ind,
x_state_financial_aid =>l_cur_usec.state_financial_aid,
x_grading_schema_prcdnce_ind =>l_cur_usec.grading_schema_prcdnce_ind,
x_federal_financial_aid =>l_cur_usec.federal_financial_aid,
x_unit_quota =>l_cur_usec.unit_quota,
x_unit_quota_reserved_places =>l_cur_usec.unit_quota_reserved_places,
x_institutional_financial_aid =>l_cur_usec.institutional_financial_aid,
x_grading_schema_cd =>l_cur_usec.grading_schema_cd,
x_gs_version_number =>l_cur_usec.gs_version_number,
x_unit_contact =>l_cur_usec.unit_contact,
x_mode =>'R',
x_ss_enrol_ind =>l_cur_usec.ss_enrol_ind,
x_owner_org_unit_cd => l_cur_usec.owner_org_unit_cd,
x_attendance_required_ind => l_cur_usec.attendance_required_ind,
x_reserved_seating_allowed => l_cur_usec.reserved_seating_allowed,
x_ss_display_ind => l_cur_usec.ss_display_ind,
x_special_permission_ind => l_cur_usec.special_permission_ind,
x_rev_account_cd => l_cur_usec.rev_account_cd ,
x_anon_unit_grading_ind => l_cur_usec.anon_unit_grading_ind,
x_anon_assess_grading_ind => l_cur_usec.anon_assess_grading_ind ,
x_non_std_usec_ind => l_cur_usec.non_std_usec_ind,
x_auditable_ind => l_cur_usec.auditable_ind,
x_audit_permission_ind => l_cur_usec.audit_permission_ind,
x_not_multiple_section_flag => l_cur_usec.not_multiple_section_flag,
x_sup_uoo_id => l_cur_usec.sup_uoo_id,
x_relation_type => l_cur_usec.relation_type,
x_default_enroll_flag => l_cur_usec.default_enroll_flag,
x_abort_flag => l_cur_usec.abort_flag
);
END update_usec_status;
SELECT count (ROWID) cnt
FROM igs_ps_uso_clas_meet
WHERE host = 'Y'
AND class_meet_group_id =cp_class_meet_group_id;
SELECT *
FROM igs_ps_uso_cm_grp
WHERE class_meet_group_id = cp_class_meet_group_id;
SELECT SUM(enrollment_actual) sum_enrollment_actual
FROM igs_ps_uso_clas_meet_v
WHERE class_meet_group_id = cp_class_meet_group_id ;
SELECT *
FROM igs_ps_uso_clas_meet_v
WHERE class_meet_group_id = cp_class_meet_group_id ;
SELECT uoo.uoo_id
FROM igs_ps_uso_clas_meet usm,
igs_ps_unit_ofr_opt uoo
WHERE usm.class_meet_group_id = cp_class_meet_group_id
AND usm.uoo_id=uoo.uoo_id
AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
SELECT a.class_meet_group_id
FROM igs_ps_uso_cm_grp a, igs_ca_inst_all b
WHERE a.class_meet_group_name = cp_group_name
AND a.cal_type = b.cal_type
AND a.ci_sequence_number =b.sequence_number
AND b.alternate_code=cp_alternate_cd;
--Update the maximum enrollment/ovrride group
UPDATE igs_ps_uso_cm_grp
SET max_enr_group=max_enr_group_temp,
max_ovr_group=max_ovr_group_temp,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE class_meet_group_id =p_class_meet_tab(i).class_meet_group_id;
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
--If the inserted/modified unit section is not an enrolled one then also change the status
--accordingly, bug#2702252
IF NVL(c_cm_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--4o
--Make all the unit section status to closed if open in that group
FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
p_insert_update VARCHAR2,
p_class_meet_rec IN OUT NOCOPY igs_ps_create_generic_pkg.class_meet_rec_type )
AS
/***********************************************************************************************
Created By : sommukhe
Date Created By: 18-Jun-2005
Purpose :
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_uso_cm_grp(cp_cmg_name VARCHAR2) IS
SELECT 'X' FROM igs_ps_uso_cm_grp
WHERE class_meet_group_name =cp_cmg_name;
SELECT *
FROM igs_ps_uso_cm_grp
WHERE class_meet_group_name =cp_cmg_name
AND Cal_type=cp_cal_type
AND ci_sequence_number=cp_sequence_number;
SELECT * FROM igs_ps_uso_cm_grp
WHERE class_meet_group_name =cp_cmg_name
AND cal_type=cp_cal_type
AND ci_sequence_number=cp_seq_no;
SELECT uoo.uoo_id
FROM igs_ps_uso_clas_meet usm,
igs_ps_unit_ofr_opt uoo
WHERE usm.class_meet_group_id = cp_class_meet_group_id
AND usm.uoo_id=uoo.uoo_id
AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
SELECT uoo_id
FROM igs_ps_uso_clas_meet
WHERE class_meet_group_id = cp_class_meet_group_id;
--update the schedule status to 'Rescheduling Requested', if unit section occurrence is a scheduled one.
ELSIF igs_ps_usec_schedule.prgp_upd_usec_dtls
(
p_uoo_id => rec_cur_usecs_in_group.uoo_id,
p_location_cd => NULL,
p_usec_status => NULL,
p_max_enrollments => NVL(p_uso_cm_grp_rec.max_enr_group,-999),
p_override_enrollment_max => NVL(p_uso_cm_grp_rec.max_ovr_group,-999),
p_enrollment_expected => NULL,
p_request_id => l_request_id,
p_message_name => l_message_name
) = FALSE THEN
fnd_message.set_name('IGS',l_message_name);
SELECT 'X'
FROM igs_ps_unit_ofr_opt_all uoo
WHERE uoo.unit_section_status IN ('OPEN','PLANNED','FULLWAITOK','CLOSED')
AND uoo.relation_type='NONE'
AND uoo.cal_type=p_c_cal_type
AND uoo.ci_sequence_number =p_n_seq_num
AND uoo.uoo_id=p_n_uoo_id;
SELECT 'X'
FROM igs_ps_uso_clas_meet
WHERE uoo_id=cp_uoo_id;
SELECT *
FROM igs_en_su_attempt
WHERE uoo_id=cp_uoo_id
AND unit_attempt_status='WAITLISTED';
SELECT waitlist_actual
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;
IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
X_ROWID => l_c_fetch_record_cur.row_id,
x_waitlist_manual_ind => l_c_fetch_record_cur.waitlist_manual_ind,
X_PERSON_ID => l_c_fetch_record_cur.person_id,
X_COURSE_CD => l_c_fetch_record_cur.course_cd,
X_UNIT_CD => l_c_fetch_record_cur.unit_cd,
X_CAL_TYPE => l_c_fetch_record_cur.cal_type,
X_CI_SEQUENCE_NUMBER => l_c_fetch_record_cur.ci_sequence_number,
X_VERSION_NUMBER => l_c_fetch_record_cur.version_number,
X_LOCATION_CD => l_c_fetch_record_cur.location_cd,
X_UNIT_CLASS => l_c_fetch_record_cur.unit_class,
X_CI_START_DT => l_c_fetch_record_cur.ci_start_dt,
X_CI_END_DT => l_c_fetch_record_cur.ci_end_dt,
X_UOO_ID => l_c_fetch_record_cur.uoo_id,
X_ENROLLED_DT => l_c_fetch_record_cur.enrolled_dt,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => l_c_fetch_record_cur.administrative_unit_status,
X_DISCONTINUED_DT => nvl(l_c_fetch_record_cur.discontinued_dt, l_sysdate),
X_RULE_WAIVED_DT => l_c_fetch_record_cur.rule_waived_dt,
X_RULE_WAIVED_PERSON_ID => l_c_fetch_record_cur.rule_waived_person_id,
X_NO_ASSESSMENT_IND => l_c_fetch_record_cur.no_assessment_ind,
X_SUP_UNIT_CD => l_c_fetch_record_cur.sup_unit_cd,
X_SUP_VERSION_NUMBER => l_c_fetch_record_cur.sup_version_number,
X_EXAM_LOCATION_CD => l_c_fetch_record_cur.exam_location_cd,
X_ALTERNATIVE_TITLE => l_c_fetch_record_cur.alternative_title,
X_OVERRIDE_ENROLLED_CP => l_c_fetch_record_cur.override_enrolled_cp,
X_OVERRIDE_EFTSU => l_c_fetch_record_cur.override_eftsu,
X_OVERRIDE_ACHIEVABLE_CP => l_c_fetch_record_cur.override_achievable_cp,
X_OVERRIDE_OUTCOME_DUE_DT => l_c_fetch_record_cur.override_outcome_due_dt,
X_OVERRIDE_CREDIT_REASON => l_c_fetch_record_cur.override_credit_reason,
X_ADMINISTRATIVE_PRIORITY => l_c_fetch_record_cur.administrative_priority,
X_WAITLIST_DT => l_c_fetch_record_cur.waitlist_dt,
X_DCNT_REASON_CD => l_c_fetch_record_cur.dcnt_reason_cd,
X_MODE => 'R',
X_GS_VERSION_NUMBER => l_c_fetch_record_cur.gs_version_number,
X_ENR_METHOD_TYPE => l_c_fetch_record_cur.enr_method_type,
X_FAILED_UNIT_RULE => l_c_fetch_record_cur.failed_unit_rule,
X_CART => l_c_fetch_record_cur.cart,
X_RSV_SEAT_EXT_ID => l_c_fetch_record_cur.rsv_seat_ext_id ,
X_ORG_UNIT_CD => l_c_fetch_record_cur.org_unit_cd,
X_SESSION_ID => l_c_fetch_record_cur.session_id,
X_GRADING_SCHEMA_CODE => l_c_fetch_record_cur.grading_schema_code,
X_DEG_AUD_DETAIL_ID => l_c_fetch_record_cur.deg_aud_detail_id,
X_SUBTITLE => l_c_fetch_record_cur.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => l_c_fetch_record_cur.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => l_c_fetch_record_cur.student_career_statistics,
X_ATTRIBUTE_CATEGORY => l_c_fetch_record_cur.attribute_category,
X_ATTRIBUTE1 => l_c_fetch_record_cur.attribute1,
X_ATTRIBUTE2 => l_c_fetch_record_cur.attribute2,
X_ATTRIBUTE3 => l_c_fetch_record_cur.attribute3,
X_ATTRIBUTE4 => l_c_fetch_record_cur.attribute4,
X_ATTRIBUTE5 => l_c_fetch_record_cur.attribute5,
X_ATTRIBUTE6 => l_c_fetch_record_cur.attribute6,
X_ATTRIBUTE7 => l_c_fetch_record_cur.attribute7,
X_ATTRIBUTE8 => l_c_fetch_record_cur.attribute8,
X_ATTRIBUTE9 => l_c_fetch_record_cur.attribute9,
X_ATTRIBUTE10 => l_c_fetch_record_cur.attribute10,
X_ATTRIBUTE11 => l_c_fetch_record_cur.attribute11,
X_ATTRIBUTE12 => l_c_fetch_record_cur.attribute12,
X_ATTRIBUTE13 => l_c_fetch_record_cur.attribute13,
X_ATTRIBUTE14 => l_c_fetch_record_cur.attribute14,
X_ATTRIBUTE15 => l_c_fetch_record_cur.attribute15,
X_ATTRIBUTE16 => l_c_fetch_record_cur.attribute16,
X_ATTRIBUTE17 => l_c_fetch_record_cur.attribute17,
X_ATTRIBUTE18 => l_c_fetch_record_cur.attribute18,
X_ATTRIBUTE19 => l_c_fetch_record_cur.attribute19,
X_ATTRIBUTE20 => l_c_fetch_record_cur.attribute20,
X_WLST_PRIORITY_WEIGHT_NUM => l_c_fetch_record_cur.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_c_fetch_record_cur.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => l_c_fetch_record_cur.core_indicator_code
) ;
SELECT count (ROWID) cnt
FROM igs_ps_usec_x_grpmem
WHERE parent = 'Y'
AND usec_x_listed_group_id =cp_usec_x_listed_group_id;
SELECT *
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
SELECT SUM(enrollment_actual) sum_enrollment_actual
FROM igs_ps_usec_x_grpmem_v
WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
SELECT *
FROM igs_ps_usec_x_grpmem_v
WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id ;
SELECT uoo.uoo_id
FROM igs_ps_usec_x_grpmem usm,
igs_ps_unit_ofr_opt uoo
WHERE usm.usec_x_listed_group_id = cp_usec_x_listed_group_id
AND usm.uoo_id=uoo.uoo_id
AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
SELECT a.usec_x_listed_group_id
FROM igs_ps_usec_x_grp a, igs_ca_inst_all b
WHERE a.usec_x_listed_group_name = cp_group_name
AND a.cal_type = b.cal_type
AND a.ci_sequence_number =b.sequence_number
AND b.alternate_code=cp_alternate_cd;
--Update the maximum enrollment/ovrride group
UPDATE igs_ps_usec_x_grp
SET max_enr_group=max_enr_group_temp,
max_ovr_group=max_ovr_group_temp,
last_updated_by = g_n_user_id,
last_update_date = SYSDATE,
last_update_login = g_n_login_id
WHERE usec_x_listed_group_id =p_cross_group_tab(i).usec_x_listed_group_id;
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
--If the inserted/modified unit section is not an enrolled one then also change the status
--accordingly, bug#2702252
IF NVL(c_x_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--4o
--Make all the unit section status to closed if open in that group
FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
update_usec_status(l_cur_usec.uoo_id,'OPEN');
update_usec_status(l_cur_usec.uoo_id,'CLOSED');
p_insert_update VARCHAR2,
p_cross_group_rec IN OUT NOCOPY igs_ps_create_generic_pkg.cross_group_rec_type )
AS
/***********************************************************************************************
Created By : sommukhe
Date Created By: 10-Jun-2005
Purpose :
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_usec_x_grp(cp_xgrp_name VARCHAR2) IS
SELECT 'X'
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name =cp_xgrp_name;
SELECT *
FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name =cp_x_name
AND Cal_type=cp_cal_type
AND ci_sequence_number=cp_sequence_number;
SELECT * FROM igs_ps_usec_x_grp
WHERE usec_x_listed_group_name =cp_x_name
AND cal_type=cp_cal_type
AND ci_sequence_number=cp_seq_no;
SELECT uoo_id
FROM igs_ps_usec_x_grpmem
WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
IF p_insert_update = 'U' THEN
IF p_usec_x_grp_rec.location_inheritance = 'N' AND c_old_cmgrp_rec.location_inheritance ='Y' THEN
Fnd_Message.Set_Name('IGS', 'IGS_PS_LOC_INHR_CANNOT_UPD');
--update the schedule status to 'Rescheduling Requested', if unit section occurrence is a scheduled one.
ELSIF igs_ps_usec_schedule.prgp_upd_usec_dtls
(
p_uoo_id => rec_cur_usecs_in_group.uoo_id,
p_location_cd => NULL,
p_usec_status => NULL,
p_max_enrollments => NVL(p_usec_x_grp_rec.max_enr_group,-999),
p_override_enrollment_max => NVL(p_usec_x_grp_rec.max_ovr_group,-999),
p_enrollment_expected => NULL,
p_request_id => l_request_id,
p_message_name => l_message_name
) = FALSE THEN
fnd_message.set_name('IGS',l_message_name);
SELECT us_ass_item_group_id,
group_name,
NVL (final_weight_qty, 0) group_final_weight_qty,
NVL (midterm_weight_qty, 0) group_midterm_weight_qty,
NVL (final_formula_qty, 0) group_final_formula_qty,
NVL (midterm_formula_qty, 0) group_midterm_formula_qty
FROM igs_as_us_ai_group
WHERE uoo_id = cp_uoo_id;
SELECT NVL (SUM (final_weight_qty), 0) sum_final_weight_qty,
NVL (SUM (midterm_weight_qty), 0) sum_midterm_weight_qty,
COUNT (us_ass_item_group_id) number_of_ai,
COUNT (DECODE (dflt_item_ind, 'Y', 1, NULL)) number_of_default_ai
FROM igs_ps_unitass_item
WHERE us_ass_item_group_id = cp_us_ass_item_group_id
AND logical_delete_dt IS NULL;
SELECT a.us_ass_item_group_id
FROM igs_as_us_ai_group a, igs_ps_unit_ofr_opt_all b, igs_ca_inst_all c
WHERE a.group_name = cp_rec_as_us_ai.group_name
AND a.uoo_id =b.uoo_id
AND b.unit_cd=cp_rec_as_us_ai.unit_cd
AND b.version_number=cp_rec_as_us_ai.version_number
AND b.cal_type=c.cal_type
AND b.ci_sequence_number=c.sequence_number
AND c.alternate_code=cp_rec_as_us_ai.teach_cal_alternate_code
AND b.unit_class=cp_rec_as_us_ai.unit_class
AND b.location_cd=cp_rec_as_us_ai.location_cd;
SELECT SUM(percentage) percentage
FROM igs_ps_tch_resp_ovrd_all
WHERE uoo_id=cp_uoo_id;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
WHERE a.unit_cd = cp_usec_tch_resp_rec.unit_cd
AND a.version_number = cp_usec_tch_resp_rec.version_number
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.sequence_number
AND b.alternate_code=cp_usec_tch_resp_rec.teach_cal_alternate_code
AND a.location_cd =cp_usec_tch_resp_rec.location_cd
AND a.unit_class = cp_usec_tch_resp_rec.unit_class;
SELECT nvl(SUM(percentage_reserved),0) percentage_reserved
FROM igs_ps_rsv_usec_pri usprv,igs_ps_rsv_usec_prf uspfv
WHERE usprv.rsv_usec_pri_id = uspfv.rsv_usec_pri_id
AND usprv.uoo_id =cp_uoo_id;
SELECT priority_order
FROM igs_ps_rsv_usec_pri
WHERE uoo_id=cp_uoo_id
ORDER BY priority_order;
SELECT rsv_usec_pri_id,priority_value
FROM igs_ps_rsv_usec_pri
WHERE uoo_id=cp_uoo_id;
SELECT preference_order
FROM igs_ps_rsv_usec_prf
WHERE rsv_usec_pri_id = cp_rsv_usec_pri_id
ORDER BY preference_order;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
WHERE a.unit_cd = cp_usec_rsv_rec.unit_cd
AND a.version_number = cp_usec_rsv_rec.version_number
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.sequence_number
AND b.alternate_code=cp_usec_rsv_rec.teach_cal_alternate_code
AND a.location_cd =cp_usec_rsv_rec.location_cd
AND a.unit_class = cp_usec_rsv_rec.unit_class;
SELECT priority_number
FROM igs_ps_usec_wlst_pri
WHERE uoo_id=cp_uoo_id
ORDER BY priority_number;
SELECT unit_sec_waitlist_priority_id,priority_value
FROM igs_ps_usec_wlst_pri
WHERE uoo_id=cp_uoo_id;
SELECT preference_order
FROM igs_ps_usec_wlst_prf
WHERE unit_sec_waitlist_priority_id = cp_waitlist_priority_id
ORDER BY preference_order;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
WHERE a.unit_cd = cp_usec_wlst_rec.unit_cd
AND a.version_number = cp_usec_wlst_rec.version_number
AND a.cal_type = b.cal_type
AND a.ci_sequence_number = b.sequence_number
AND b.alternate_code=cp_usec_wlst_rec.teach_cal_alternate_code
AND a.location_cd =cp_usec_wlst_rec.location_cd
AND a.unit_class = cp_usec_wlst_rec.unit_class;
SELECT 'X'
FROM igs_ps_media_equip_all
WHERE media_code = cp_facility_code
AND closed_ind = 'Y';
SELECT 'X'
FROM igs_ps_usec_occurs_all
WHERE unit_section_occurrence_id = cp_uso_id
AND schedule_status = 'PROCESSING';
SELECT uso.unit_section_occurrence_id
FROM igs_ps_usec_occurs_all uso
WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
AND uso.no_set_day_ind ='N'
AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
SELECT 'X'
FROM igs_ps_unit_cat
WHERE unit_cat = cp_unit_cat
AND closed_ind = 'Y';
p_insert_update IN VARCHAR2)
AS
/***********************************************************************************************
Created By : Sommukhe
Date Created By:
Purpose : Check if reserved seating is allowed and if priority value is 'PERSON_GRP'
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_rsv_allow (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
SELECT reserved_seating_allowed
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id =cp_n_uoo_id;
IF p_insert_update = 'I' THEN
IF p_usec_rsv_rec.priority_order IS NULL THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'PRIORITY_ORDER', 'LEGACY_TOKENS', FALSE);
p_insert_update IN VARCHAR2)
AS
/***********************************************************************************************
Created By : Sommukhe
Date Created By:
Purpose : Check if reserved seating is allowed and if priority value is 'PERSON_GRP'
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_org
********************************************************************************************** */
CURSOR c_clstd(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
SELECT 'X'
FROM igs_pr_class_std
WHERE closed_ind = 'N'
AND class_standing = cp_preference_code;
SELECT 'X'
FROM igs_ps_stage_type
WHERE closed_ind = 'N'
AND course_stage_type = cp_preference_code;
SELECT 'X'
FROM igs_ps_ver pv, igs_ps_stat pvst
WHERE pvst.course_status = pv.course_status
AND pvst.s_course_status <> 'INACTIVE'
AND pv.course_cd=cp_preference_code
AND pv.version_number=cp_preference_version;
SELECT 'X'
FROM igs_en_unit_set us, igs_en_unit_set_stat uss
WHERE us.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
AND us.unit_set_cd = cp_preference_code
AND us.version_number = cp_preference_version;
SELECT 'X'
FROM igs_pe_persid_group_all
WHERE closed_ind = 'N'
AND group_cd = cp_preference_code
AND file_name IS NULL
AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
SELECT 'X'
FROM igs_or_inst_org_base_v a, igs_or_status b
WHERE a.party_number = cp_preference_code
AND a.org_status = b.org_status
AND b.s_org_status <> 'INACTIVE';
IF p_insert_update = 'I' THEN
--validation when priority value as Person Group
IF p_usec_rsv_rec.priority_value = 'PERSON_GRP' THEN
OPEN c_pgrp (p_usec_rsv_rec.preference_code);
p_insert_update IN VARCHAR2) AS
/***********************************************************************************************
Created By : Sommukhe
Date Created By:
Purpose : Check if waitlist preference related validation are passed.
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_org
********************************************************************************************** */
CURSOR c_clstd(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
SELECT 'X'
FROM igs_pr_class_std
WHERE closed_ind = 'N'
AND class_standing = cp_preference_code;
SELECT 'X'
FROM igs_ps_stage_type
WHERE closed_ind = 'N'
AND course_stage_type = cp_preference_code;
SELECT 'X'
FROM igs_ps_ver pv, igs_ps_stat pvst
WHERE pvst.course_status = pv.course_status
AND pvst.s_course_status <> 'INACTIVE'
AND pv.course_cd=cp_preference_code
AND pv.version_number=cp_preference_version;
SELECT 'X'
FROM igs_en_unit_set us, igs_en_unit_set_stat uss
WHERE us.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
AND us.unit_set_cd = cp_preference_code
AND us.version_number = cp_preference_version;
SELECT 'X'
FROM igs_or_inst_org_base_v a, igs_or_status b
WHERE a.party_number = cp_preference_code
AND a.org_status = b.org_status
AND b.s_org_status <> 'INACTIVE';
IF p_insert_update = 'I' THEN
--validation when priority value as Org unit
IF p_usec_wlst_rec.priority_value = 'ORG_UNIT' THEN
OPEN c_org (p_usec_wlst_rec.preference_code);
p_insert_update VARCHAR2)
AS
/***********************************************************************************************
Created By : Sommukhe
Date Created By:
Purpose : Check if Waitlist is allowed and if priority value is 'PERSON_GRP'
Known limitations,enhancements,remarks:
Change History (in reverse chronological order)
Who When What
********************************************************************************************** */
CURSOR c_wlst_allow (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
SELECT waitlist_allowed
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_n_uoo_id;
SELECT 'X'
FROM igs_ps_unit_ofr_pat_all a, igs_ps_unit_ofr_opt_all b
WHERE b.uoo_id = cp_n_uoo_id
AND a.unit_cd=b.unit_cd
AND a.version_number=b.version_number
AND a.cal_type=b.cal_type
AND a.ci_sequence_number=b.ci_sequence_number
AND waitlist_allowed='Y';
IF p_insert_update = 'I' THEN
IF p_usec_wlst_rec.priority_number IS NULL THEN
igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'PRIORITY_NUMBER', 'LEGACY_TOKENS', FALSE);