[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sua.*
FROM IGS_EN_SU_ATTEMPT_ALL sua
WHERE ROWID = CP_ROW_ID;
SELECT ROWID,uoo.*
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo_id = p_uoo_id
FOR UPDATE;
PROCEDURE local_update_unit_section( p_action IN VARCHAR2,
old_references EN_SUA_REC_TYPE%TYPE,
new_references EN_SUA_REC_TYPE%TYPE,
usec_row usec_upd_enr_act%ROWTYPE
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
sommukhe 28-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter abort_flag.
stutta 23-Aug-2004 Bug#3803790, passed course_cd as parameter to igs_en_gen_015.get_usec_status
sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_max_std_per_wait_uofr_pat,c_wait_allow_unit_offering .
sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include
new parameters sup_uoo_id,relation_type,default_enroll_flag.
vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the calls to igs_ps_unit_ofr_opt_pkg.update_row to
include new parameter not_multiple_section_flag.
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_usec_lim (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
SELECT NVL (usec.enrollment_maximum, NVL(uv.enrollment_maximum,999999) ) enrollment_maximum
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 = cp_uoo_id;
SELECT max_students_per_waitlist FROM igs_ps_usec_lim_wlst_v
WHERE uoo_id = cp_uoo_id;
SELECT max_students_per_waitlist FROM igs_ps_unit_ofr_pat
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND delete_flag='N';
SELECT max_stud_per_wlst FROM igs_en_or_unit_wlst_v
WHERE org_unit_cd = cp_org_unit_cd
AND cal_type = cp_cal_type
AND sequence_number = cp_ci_sequence_number ;
SELECT waitlist_allowed
FROM IGS_PS_USEC_LIM_WLST
WHERE uoo_id = cp_uoo_id ;
SELECT waitlist_allowed
FROM IGS_PS_UNIT_OFR_PAT
WHERE delete_flag='N' AND (unit_cd , version_number , cal_type , ci_sequence_number ) IN
(SELECT unit_cd , version_number , cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id);
SELECT grp.max_enr_group, grpmem.usec_x_listed_group_id
FROM igs_ps_usec_x_grpmem grpmem,
igs_ps_usec_x_grp grp
WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
AND grpmem.uoo_id = l_uoo_id;
SELECT grp.max_enr_group, ucm.class_meet_group_id
FROM igs_ps_uso_clas_meet ucm,
igs_ps_uso_cm_grp grp
WHERE grp.class_meet_group_id = ucm.class_meet_group_id
AND ucm.uoo_id = l_uoo_id;
SELECT SUM(enrollment_actual)
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_usec_x_grpmem ugrp
WHERE uoo.uoo_id = ugrp.uoo_id
AND ugrp.uoo_id <> l_uoo_id
AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id;
SELECT SUM(enrollment_actual)
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_uso_clas_meet ucls
WHERE uoo.uoo_id = ucls.uoo_id
AND ucls.uoo_id <> l_uoo_id
AND ucls.class_meet_group_id = l_class_meet_group_id;
SELECT uoo.rowid, uoo.*
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_usec_x_grpmem ugrp
WHERE uoo.uoo_id = ugrp.uoo_id
AND ugrp.uoo_id <> l_uoo_id
AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id
AND uoo.unit_section_status <> l_unit_section_status;
SELECT uoo.rowid, uoo.*
FROM igs_ps_unit_ofr_opt_all uoo,
igs_ps_uso_clas_meet ucls
WHERE uoo.uoo_id = ucls.uoo_id
AND ucls.uoo_id <> l_uoo_id
AND ucls.class_meet_group_id = l_class_meet_group_id
AND uoo.unit_section_status <> l_unit_section_status;
IF p_action = 'INSERT' THEN
IF new_references.UNIT_ATTEMPT_STATUS = cst_enrolled THEN
l_dir_enr := NVL(usec_row.DIR_ENROLLMENT,0) + 1 ;
ELSIF p_action = 'UPDATE' THEN
IF (new_references.UNIT_ATTEMPT_STATUS = cst_enrolled AND
old_references.UNIT_ATTEMPT_STATUS = cst_waitlisted ) THEN
-- if the student has been enrolled from the waitlist then increment
-- counter ENR_FROM_WLST
l_enr_from_wlst := NVL(usec_row.ENR_FROM_WLST, 0) + 1;
IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
X_ROWID => usec_row.ROWID ,
x_UNIT_CD => usec_row.UNIT_CD ,
x_VERSION_NUMBER => usec_row.VERSION_NUMBER ,
x_CAL_TYPE => usec_row.CAL_TYPE ,
x_CI_SEQUENCE_NUMBER => usec_row.CI_SEQUENCE_NUMBER ,
x_LOCATION_CD => usec_row.LOCATION_CD ,
x_UNIT_CLASS => usec_row.UNIT_CLASS ,
x_UOO_ID => usec_row.UOO_ID ,
x_IVRS_AVAILABLE_IND => usec_row.IVRS_AVAILABLE_IND ,
x_CALL_NUMBER => usec_row.CALL_NUMBER ,
x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,usec_row.UNIT_SECTION_STATUS ),
x_UNIT_SECTION_START_DATE => usec_row.UNIT_SECTION_START_DATE ,
x_UNIT_SECTION_END_DATE => usec_row.UNIT_SECTION_END_DATE ,
x_ENROLLMENT_ACTUAL => usec_row.ENROLLMENT_ACTUAL,
x_WAITLIST_ACTUAL => usec_row.WAITLIST_ACTUAL ,
x_OFFERED_IND => usec_row.OFFERED_IND ,
x_STATE_FINANCIAL_AID => usec_row.STATE_FINANCIAL_AID ,
x_GRADING_SCHEMA_PRCDNCE_IND => usec_row.GRADING_SCHEMA_PRCDNCE_IND,
x_FEDERAL_FINANCIAL_AID => usec_row.FEDERAL_FINANCIAL_AID ,
x_UNIT_QUOTA => usec_row.UNIT_QUOTA ,
x_UNIT_QUOTA_RESERVED_PLACES => usec_row.UNIT_QUOTA_RESERVED_PLACES ,
x_INSTITUTIONAL_FINANCIAL_AID => usec_row.INSTITUTIONAL_FINANCIAL_AID ,
x_UNIT_CONTACT => usec_row.UNIT_CONTACT ,
x_GS_VERSION_NUMBER => usec_row.GS_VERSION_NUMBER ,
X_MODE => 'R',
X_SS_ENROL_IND => usec_row.ss_enrol_ind,
X_SS_DISPLAY_IND => usec_row.ss_display_ind,
x_owner_org_unit_cd => usec_row.owner_org_unit_cd,
x_attendance_required_ind => usec_row.attendance_required_ind,
x_reserved_seating_allowed => usec_row.reserved_seating_allowed,
x_special_permission_ind => usec_row.special_permission_ind,
x_dir_enrollment => l_dir_enr,
x_enr_from_wlst => l_enr_from_wlst,
x_inq_not_wlst =>usec_row.inq_not_wlst,
x_rev_account_cd => usec_row.rev_account_cd ,
x_GRADING_SCHEMA_CD => usec_row.GRADING_SCHEMA_CD,
X_NON_STD_USEC_IND => usec_row.NON_STD_USEC_IND,
X_ANON_UNIT_GRADING_IND => usec_row.anon_unit_grading_ind,
X_ANON_ASSESS_GRADING_IND => usec_row.anon_assess_grading_ind,
x_auditable_ind => usec_row.auditable_ind,
x_audit_permission_ind => usec_row.audit_permission_ind,
x_not_multiple_section_flag => usec_row.not_multiple_section_flag,
x_sup_uoo_id => usec_row.sup_uoo_id,
x_relation_type => usec_row.relation_type,
x_default_enroll_flag => usec_row.default_enroll_flag,
x_abort_flag => usec_row.abort_flag
);
IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
X_ROWID => unit_sec.ROWID ,
x_UNIT_CD => unit_sec.UNIT_CD ,
x_VERSION_NUMBER => unit_sec.VERSION_NUMBER ,
x_CAL_TYPE => unit_sec.CAL_TYPE ,
x_CI_SEQUENCE_NUMBER => unit_sec.CI_SEQUENCE_NUMBER ,
x_LOCATION_CD => unit_sec.LOCATION_CD ,
x_UNIT_CLASS => unit_sec.UNIT_CLASS ,
x_UOO_ID => unit_sec.UOO_ID ,
x_IVRS_AVAILABLE_IND => unit_sec.IVRS_AVAILABLE_IND ,
x_CALL_NUMBER => unit_sec.CALL_NUMBER ,
x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,unit_sec.UNIT_SECTION_STATUS ),
x_UNIT_SECTION_START_DATE => unit_sec.UNIT_SECTION_START_DATE ,
x_UNIT_SECTION_END_DATE => unit_sec.UNIT_SECTION_END_DATE ,
x_ENROLLMENT_ACTUAL => unit_sec.ENROLLMENT_ACTUAL,
x_WAITLIST_ACTUAL => unit_sec.WAITLIST_ACTUAL ,
x_OFFERED_IND => unit_sec.OFFERED_IND ,
x_STATE_FINANCIAL_AID => unit_sec.STATE_FINANCIAL_AID ,
x_GRADING_SCHEMA_PRCDNCE_IND => unit_sec.GRADING_SCHEMA_PRCDNCE_IND,
x_FEDERAL_FINANCIAL_AID => unit_sec.FEDERAL_FINANCIAL_AID ,
x_UNIT_QUOTA => unit_sec.UNIT_QUOTA ,
x_UNIT_QUOTA_RESERVED_PLACES => unit_sec.UNIT_QUOTA_RESERVED_PLACES ,
x_INSTITUTIONAL_FINANCIAL_AID => unit_sec.INSTITUTIONAL_FINANCIAL_AID ,
x_UNIT_CONTACT => unit_sec.UNIT_CONTACT ,
x_GS_VERSION_NUMBER => unit_sec.GS_VERSION_NUMBER ,
X_MODE => 'R',
X_SS_ENROL_IND => unit_sec.ss_enrol_ind,
X_SS_DISPLAY_IND => unit_sec.ss_display_ind,
x_owner_org_unit_cd => unit_sec.owner_org_unit_cd,
x_attendance_required_ind => unit_sec.attendance_required_ind,
x_reserved_seating_allowed => unit_sec.reserved_seating_allowed,
x_special_permission_ind => unit_sec.special_permission_ind,
x_dir_enrollment =>unit_sec.dir_enrollment,
x_enr_from_wlst =>unit_sec.enr_from_wlst,
x_inq_not_wlst =>unit_sec.inq_not_wlst,
x_rev_account_cd => unit_sec.rev_account_cd ,
x_GRADING_SCHEMA_CD => unit_sec.GRADING_SCHEMA_CD,
X_NON_STD_USEC_IND => unit_sec.NON_STD_USEC_IND,
X_ANON_UNIT_GRADING_IND => unit_sec.anon_unit_grading_ind,
X_ANON_ASSESS_GRADING_IND => unit_sec.anon_assess_grading_ind,
x_auditable_ind => unit_sec.auditable_ind,
x_audit_permission_ind => unit_sec.audit_permission_ind,
x_not_multiple_section_flag => unit_sec.not_multiple_section_flag,
x_sup_uoo_id => unit_sec.sup_uoo_id,
x_relation_type => unit_sec.relation_type,
x_default_enroll_flag => unit_sec.default_enroll_flag,
x_abort_flag => unit_sec.abort_flag
);
IGS_PS_UNIT_OFR_OPT_PKG.UPDATE_ROW (
X_ROWID => usec_meet_with.ROWID ,
x_UNIT_CD => usec_meet_with.UNIT_CD ,
x_VERSION_NUMBER => usec_meet_with.VERSION_NUMBER ,
x_CAL_TYPE => usec_meet_with.CAL_TYPE ,
x_CI_SEQUENCE_NUMBER => usec_meet_with.CI_SEQUENCE_NUMBER ,
x_LOCATION_CD => usec_meet_with.LOCATION_CD ,
x_UNIT_CLASS => usec_meet_with.UNIT_CLASS ,
x_UOO_ID => usec_meet_with.UOO_ID ,
x_IVRS_AVAILABLE_IND => usec_meet_with.IVRS_AVAILABLE_IND ,
x_CALL_NUMBER => usec_meet_with.CALL_NUMBER ,
x_UNIT_SECTION_STATUS => NVL(lv_unit_section_status,usec_meet_with.UNIT_SECTION_STATUS ),
x_UNIT_SECTION_START_DATE => usec_meet_with.UNIT_SECTION_START_DATE ,
x_UNIT_SECTION_END_DATE => usec_meet_with.UNIT_SECTION_END_DATE ,
x_ENROLLMENT_ACTUAL => usec_meet_with.ENROLLMENT_ACTUAL,
x_WAITLIST_ACTUAL => usec_meet_with.WAITLIST_ACTUAL ,
x_OFFERED_IND => usec_meet_with.OFFERED_IND ,
x_STATE_FINANCIAL_AID => usec_meet_with.STATE_FINANCIAL_AID ,
x_GRADING_SCHEMA_PRCDNCE_IND => usec_meet_with.GRADING_SCHEMA_PRCDNCE_IND,
x_FEDERAL_FINANCIAL_AID => usec_meet_with.FEDERAL_FINANCIAL_AID ,
x_UNIT_QUOTA => usec_meet_with.UNIT_QUOTA ,
x_UNIT_QUOTA_RESERVED_PLACES => usec_meet_with.UNIT_QUOTA_RESERVED_PLACES ,
x_INSTITUTIONAL_FINANCIAL_AID => usec_meet_with.INSTITUTIONAL_FINANCIAL_AID ,
x_UNIT_CONTACT => usec_meet_with.UNIT_CONTACT ,
x_GS_VERSION_NUMBER => usec_meet_with.GS_VERSION_NUMBER ,
X_MODE => 'R',
X_SS_ENROL_IND => usec_meet_with.ss_enrol_ind,
X_SS_DISPLAY_IND => usec_meet_with.ss_display_ind,
x_owner_org_unit_cd => usec_meet_with.owner_org_unit_cd,
x_attendance_required_ind => usec_meet_with.attendance_required_ind,
x_reserved_seating_allowed => usec_meet_with.reserved_seating_allowed,
x_special_permission_ind => usec_meet_with.special_permission_ind,
x_dir_enrollment =>usec_meet_with.dir_enrollment,
x_enr_from_wlst =>usec_meet_with.enr_from_wlst,
x_inq_not_wlst =>usec_meet_with.inq_not_wlst,
x_rev_account_cd => usec_meet_with.rev_account_cd ,
x_GRADING_SCHEMA_CD => usec_meet_with.GRADING_SCHEMA_CD,
X_NON_STD_USEC_IND => usec_meet_with.NON_STD_USEC_IND,
X_ANON_UNIT_GRADING_IND => usec_meet_with.anon_unit_grading_ind,
X_ANON_ASSESS_GRADING_IND => usec_meet_with.anon_assess_grading_ind,
x_auditable_ind => usec_meet_with.auditable_ind,
x_audit_permission_ind => usec_meet_with.audit_permission_ind,
x_not_multiple_section_flag => usec_meet_with.not_multiple_section_flag,
x_sup_uoo_id => usec_meet_with.sup_uoo_id,
x_relation_type => usec_meet_with.relation_type,
x_default_enroll_flag => usec_meet_with.default_enroll_flag,
x_abort_flag => usec_meet_with.abort_flag
);
END local_update_unit_section;
SELECT load_cal_type, load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_teach_cal_type
AND teach_ci_sequence_number = p_teach_ci_sequence_number
ORDER BY LOAD_START_DT ASC;
SELECT max_auditors_allowed
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = cp_uoo_id;
SELECT count(*)
FROM igs_en_su_attempt
WHERE no_assessment_ind='Y'
AND uoo_id = cp_uoo_id
AND unit_attempt_status in (cst_enrolled,cst_completed,cst_invalid,cst_unconfirm);
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE
person_id = cp_person_id AND
course_cd = cp_course_cd;
SELECT max_wlst_per_stud
FROM igs_ps_ver
WHERE
course_cd = cp_course_cd AND
version_number = cp_version_number;
SELECT max_waitlists_student_num
FROM IGS_EN_INST_WL_STPS;
SELECT count(*)
FROM igs_en_su_attempt
WHERE person_id = cp_person_id
AND unit_attempt_status =cst_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 = cp_load_cal_type
AND load_ci_sequence_number = cp_load_seq_num);
SELECT same_teaching_period
FROM igs_ps_unit_ver
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT 'X'
FROM igs_en_su_attempt sua
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.unit_cd = cp_unit_cd AND
sua.cal_type = cp_cal_type AND
sua.ci_sequence_number = cp_sequence_number AND
sua.unit_attempt_status = cst_waitlisted AND
sua.uoo_id <> cp_uoo_id;
SELECT simultaneous_wlst_alwd_flag
FROM igs_en_inst_wl_stps;
SELECT 'X'
FROM igs_en_inst_wlst_opt
WHERE cal_type = p_load_cal_type AND
smlnes_waitlist_alwd = 'N';
SELECT smtanus_wlst_unit_enr_alwd
FROM igs_En_or_unit_wlst
WHERE cal_type = cp_load_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 = cp_uoo_id AND
uv.unit_cd = uoo.unit_cd AND
uv.version_number = uoo.version_number);
IF p_action = 'INSERT' THEN
IF (new_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled, cst_invalid, cst_completed)) OR
(new_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND new_references.CART = 'N') THEN
local_usec_status (old_references, new_references,usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
ELSIF p_action = 'UPDATE' THEN
IF (old_references.UNIT_ATTEMPT_STATUS IN (cst_dropped,cst_discontin,cst_duplicate, cst_waitlisted) AND
new_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled,cst_invalid,cst_completed) ) OR
(old_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND
new_references.UNIT_ATTEMPT_STATUS = cst_unconfirm AND
new_references.CART IN ('S','I','J') AND NVL(old_references.CART,'X') <> 'N') THEN
local_usec_status (old_references, new_references,usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
ELSIF p_action = 'DELETE' THEN
IF (old_references.UNIT_ATTEMPT_STATUS IN (cst_enrolled,cst_unconfirm,cst_invalid, cst_completed)) THEN
OPEN usec_upd_enr_act(old_references.uoo_id);
local_update_unit_section(p_action, old_references, new_references, usec_row);
local_update_unit_section(p_action, old_references, new_references, usec_row);
IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_CD,
X_CAL_TYPE,
X_CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER,
X_LOCATION_CD,
X_UNIT_CLASS,
X_CI_START_DT,
X_CI_END_DT,
X_UOO_ID,
X_ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT,
X_RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND,
X_SUP_UNIT_CD,
X_SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT,
X_DCNT_REASON_CD,
X_MODE,
x_org_id ,
X_GS_VERSION_NUMBER ,
X_ENR_METHOD_TYPE ,
X_FAILED_UNIT_RULE ,
X_CART ,
X_RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD ,
X_GRADING_SCHEMA_CODE ,
X_subtitle,
x_session_id,
X_deg_aud_detail_id ,
x_student_career_transcript,
x_student_career_statistics,
X_WAITLIST_MANUAL_IND,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_WLST_PRIORITY_WEIGHT_NUM ,
X_WLST_PREFERENCE_WEIGHT_NUM,
X_CORE_INDICATOR_CODE,
'N', -- for UPD_AUDIT_IND
'A' -- for SS_SOURCE_IND
);
upd_enrollment_counts('INSERT',
NULL,
new_references);
PROCEDURE UPDATE_UNIT_ATTEMPT (
X_ROWID IN VARCHAR2,
X_PERSON_ID IN NUMBER,
X_COURSE_CD IN VARCHAR2,
X_UNIT_CD IN VARCHAR2,
X_CAL_TYPE IN VARCHAR2,
X_CI_SEQUENCE_NUMBER IN NUMBER,
X_VERSION_NUMBER IN NUMBER,
X_LOCATION_CD IN VARCHAR2,
X_UNIT_CLASS IN VARCHAR2,
X_CI_START_DT IN DATE,
X_CI_END_DT IN DATE,
X_UOO_ID IN NUMBER,
X_ENROLLED_DT IN DATE,
X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
X_DISCONTINUED_DT IN DATE,
X_RULE_WAIVED_DT IN DATE,
X_RULE_WAIVED_PERSON_ID IN NUMBER,
X_NO_ASSESSMENT_IND IN VARCHAR2,
X_SUP_UNIT_CD IN VARCHAR2,
X_SUP_VERSION_NUMBER IN NUMBER,
X_EXAM_LOCATION_CD IN VARCHAR2,
X_ALTERNATIVE_TITLE IN VARCHAR2,
X_OVERRIDE_ENROLLED_CP IN NUMBER,
X_OVERRIDE_EFTSU IN NUMBER,
X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
X_ADMINISTRATIVE_PRIORITY IN NUMBER,
X_WAITLIST_DT IN DATE,
X_DCNT_REASON_CD IN VARCHAR2,
X_MODE IN VARCHAR2 ,
X_GS_VERSION_NUMBER IN NUMBER ,
X_ENR_METHOD_TYPE IN VARCHAR2 ,
X_FAILED_UNIT_RULE IN VARCHAR2 ,
X_CART IN VARCHAR2 ,
X_RSV_SEAT_EXT_ID IN NUMBER ,
X_ORG_UNIT_CD IN VARCHAR2 ,
X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
X_SUBTITLE IN VARCHAR2 ,
X_SESSION_ID IN NUMBER ,
X_DEG_AUD_DETAIL_ID IN NUMBER ,
X_STUDENT_CAREER_TRANSCRIPT IN VARCHAR2 ,
X_STUDENT_CAREER_STATISTICS IN VARCHAR2 ,
X_WAITLIST_MANUAL_IND IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
X_ATTRIBUTE1 IN VARCHAR2 ,
X_ATTRIBUTE2 IN VARCHAR2 ,
X_ATTRIBUTE3 IN VARCHAR2 ,
X_ATTRIBUTE4 IN VARCHAR2 ,
X_ATTRIBUTE5 IN VARCHAR2 ,
X_ATTRIBUTE6 IN VARCHAR2 ,
X_ATTRIBUTE7 IN VARCHAR2 ,
X_ATTRIBUTE8 IN VARCHAR2 ,
X_ATTRIBUTE9 IN VARCHAR2 ,
X_ATTRIBUTE10 IN VARCHAR2 ,
X_ATTRIBUTE11 IN VARCHAR2 ,
X_ATTRIBUTE12 IN VARCHAR2 ,
X_ATTRIBUTE13 IN VARCHAR2 ,
X_ATTRIBUTE14 IN VARCHAR2 ,
X_ATTRIBUTE15 IN VARCHAR2 ,
X_ATTRIBUTE16 IN VARCHAR2 ,
X_ATTRIBUTE17 IN VARCHAR2 ,
X_ATTRIBUTE18 IN VARCHAR2 ,
X_ATTRIBUTE19 IN VARCHAR2 ,
X_ATTRIBUTE20 IN VARCHAR2,
X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER,
X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER,
X_CORE_INDICATOR_CODE IN VARCHAR2
) AS
old_references EN_SUA_REC_TYPE%TYPE;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = cp_person_type_code;
SAVEPOINT update_unit_attempt;
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_CD,
X_CAL_TYPE,
X_CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER,
X_LOCATION_CD,
X_UNIT_CLASS,
X_CI_START_DT,
X_CI_END_DT,
X_UOO_ID,
X_ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT,
X_RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND,
X_SUP_UNIT_CD,
X_SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT,
X_DCNT_REASON_CD,
X_MODE,
X_GS_VERSION_NUMBER,
X_ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE,
X_CART ,
X_RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD,
X_GRADING_SCHEMA_CODE ,
X_subtitle,
x_session_id,
X_deg_aud_detail_id ,
x_student_career_transcript,
x_student_career_statistics,
X_WAITLIST_MANUAL_IND,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM,
X_CORE_INDICATOR_CODE,
old_references.UPD_AUDIT_FLAG,
old_references.SS_SOURCE_IND
);
upd_enrollment_counts('UPDATE',
old_references,
new_references);
ROLLBACK TO update_unit_attempt;
ROLLBACK TO update_unit_attempt;
END update_unit_attempt;
SELECT relation_type
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_Id;
SELECT sua.uoo_id sub_uoo_id, sua.unit_attempt_status sub_uoo_status
FROM igs_en_su_attempt sua, igs_ps_unit_ofr_opt uoo
WHERE uoo.sup_uoo_id = p_uoo_id
AND uoo.relation_type = 'SUBORDINATE'
AND sua.uoo_id = uoo.uoo_id
AND sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status <> 'DUPLICATE';
SELECT uoo.sup_uoo_id sup_uoo_id, sua.unit_attempt_status sup_uoo_status
FROM igs_en_su_attempt sua, igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = p_uoo_id
AND uoo.sup_uoo_id = sua.uoo_id
AND sua.person_id = p_person_id
AND sua.course_cd = p_course_cd;
|| a superior unit cannot be deleted if subordinate is
|| in any status other than dropped or discontinued.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR cur_chk_sup IS
SELECT relation_type
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_Id;
SELECT sua.unit_attempt_status
FROM igs_ps_unit_ofr_opt uoo,
igs_en_su_attempt sua
WHERE uoo.sup_uoo_id = p_uoo_id
AND uoo.relation_type = 'SUPERIOR'
AND sua.uoo_id = uoo.uoo_id
AND sua.person_Id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status NOT IN ('DROPPED','DUPLICATE');