[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_type_code
FROM igs_pe_person_types
WHERE rank = ( SELECT MIN(rank)
FROM igs_pe_person_types
WHERE person_type_code IN ( SELECT person_type_code
FROM igs_pe_typ_instances_all
WHERE person_id = p_person_id
)
AND system_type = 'SS_ENROLL_STAFF'
AND closed_ind = 'N'
);
SELECT
igs_pe_std_todo_ref_rf_num_s.NEXTVAL
FROM
dual;
SELECT
person_id
FROM
igs_pe_std_todo_ref
WHERE
person_id = p_person_id AND
s_student_todo_type = 'FEE_RECALC' AND
sequence_number = c_return_val AND
cal_type = p_cal_type AND
ci_sequence_number = p_sequence_number AND
logical_delete_dt IS NULL; -- This condition is newly added by Nishikant - 20JUN2002 - bug#2420442.
igs_pe_std_todo_ref_pkg.insert_row (
X_ROWID => l_row_id,
X_PERSON_ID => p_person_id,
X_S_STUDENT_TODO_TYPE => 'FEE_RECALC',
X_SEQUENCE_NUMBER => l_return_val,
X_REFERENCE_NUMBER => l_reference_number,
X_CAL_TYPE => p_cal_type,
X_CI_SEQUENCE_NUMBER => p_sequence_number,
X_COURSE_CD => p_course_cd,
X_UNIT_CD => p_unit_cd,
X_OTHER_REFERENCE => NULL,
X_LOGICAL_DELETE_DT => NULL,
X_MODE => 'R',
X_UOO_ID => p_uoo_id
);
SELECT sua.*
FROM
igs_en_su_attempt sua
WHERE
person_id = p_person_id AND
course_cd = p_course_cd AND
unit_cd = p_unit_cd AND
version_number = p_version_number AND
uoo_id = p_uoo_id;
igs_en_sua_api.update_unit_attempt(
X_ROWID => cart_rec.ROW_ID,
X_PERSON_ID => cart_rec.PERSON_ID,
X_COURSE_CD => cart_rec.COURSE_CD,
X_UNIT_CD => cart_rec.UNIT_CD,
X_CAL_TYPE => cart_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => cart_rec.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => cart_rec.VERSION_NUMBER,
X_LOCATION_CD => cart_rec.LOCATION_CD,
X_UNIT_CLASS => cart_rec.UNIT_CLASS,
X_CI_START_DT => cart_rec.CI_START_DT,
X_CI_END_DT => cart_rec.CI_END_DT,
X_UOO_ID => cart_rec.UOO_ID,
X_ENROLLED_DT => l_enrolled_dt,
X_UNIT_ATTEMPT_STATUS => p_unit_attempt_status,
X_ADMINISTRATIVE_UNIT_STATUS => cart_rec.ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT => cart_rec.DISCONTINUED_DT,
X_RULE_WAIVED_DT => cart_rec.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => cart_rec.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => cart_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => cart_rec.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => cart_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => cart_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => cart_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => cart_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => cart_rec.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => cart_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => cart_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => cart_rec.OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY => cart_rec.ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT => cart_rec.WAITLIST_DT,
X_DCNT_REASON_CD => cart_rec.DCNT_REASON_CD,
X_MODE => 'R',
X_GS_VERSION_NUMBER => cart_rec.GS_VERSION_NUMBER,
X_ENR_METHOD_TYPE => cart_rec.ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE => cart_rec.FAILED_UNIT_RULE,
X_CART => 'N',
X_RSV_SEAT_EXT_ID => cart_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => cart_rec.ORG_UNIT_CD,
X_GRADING_SCHEMA_CODE => cart_rec.GRADING_SCHEMA_CODE,
X_SUBTITLE => cart_rec.SUBTITLE,
X_SESSION_ID => cart_rec.SESSION_ID,
X_DEG_AUD_DETAIL_ID => cart_rec.DEG_AUD_DETAIL_ID,
X_STUDENT_CAREER_TRANSCRIPT => cart_rec.STUDENT_CAREER_TRANSCRIPT,
X_STUDENT_CAREER_STATISTICS => cart_rec.STUDENT_CAREER_STATISTICS,
X_ATTRIBUTE_CATEGORY => cart_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => cart_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => cart_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => cart_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => cart_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => cart_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => cart_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => cart_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => cart_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => cart_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => cart_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => cart_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => cart_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => cart_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => cart_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => cart_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => cart_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => cart_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => cart_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => cart_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => cart_rec.ATTRIBUTE20,
X_WAITLIST_MANUAL_IND => cart_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => cart_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => cart_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => cart_rec.core_indicator_code
);
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT count(*) UNITS_IN_CART
FROM
IGS_EN_SU_ATTEMPT SUA,
IGS_EN_USEC_STAT_DSP_V ENUSECSTAT,
IGS_PS_UNIT_OFR_OPT UOO
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.uoo_id = uoo.uoo_id
AND enusecstat.unit_section_status = uoo.unit_section_status
AND enusecstat.displayed = 'Y'
AND sua.unit_attempt_status IN ( 'UNCONFIRM','INVALID' )
AND 0< (SELECT 1 FROM igs_ca_load_to_teach_v vt
WHERE vt.load_cal_type =cp_load_cal_type
AND vt.load_ci_sequence_number=cp_load_ci_seq_num
AND sua.cal_type = vt.teach_cal_type
AND sua.ci_sequence_number = vt.teach_ci_sequence_number) ;
SELECT SUM(NVL(sua.override_enrolled_cp,NVL(cps.enrolled_credit_points,
uv.enrolled_credit_points))) total_credit_points
FROM igs_en_su_attempt sua,
igs_en_usec_stat_dsp_v enusecstat,
igs_ps_unit_ofr_opt uoo,
igs_ps_usec_cps cps,
igs_ps_unit_ver uv
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.uoo_id = uoo.uoo_id AND
uoo.uoo_id = cps.uoo_id(+) AND
enusecstat.unit_section_status = uoo.unit_section_status AND
enusecstat.displayed = 'Y' AND
sua.unit_attempt_status IN ('UNCONFIRM','INVALID') AND
sua.no_assessment_ind = 'N' AND
0< (SELECT 1 FROM igs_ca_load_to_teach_v vt
WHERE vt.load_cal_type=cp_load_cal_type
AND vt.load_ci_sequence_number=cp_load_ci_seq_num
AND sua.cal_type=vt.teach_cal_type
AND sua.ci_sequence_number=vt.teach_ci_sequence_number)
AND uv.unit_cd= sua.unit_cd
AND uv.version_number = sua.version_number;
PROCEDURE insert_into_enr_worksheet(
p_person_number IN VARCHAR2,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER,
p_waitlist_ind IN VARCHAR2,
p_session_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2,
p_cal_type IN VARCHAR2,
p_ci_sequence_number IN NUMBER,
p_audit_requested IN VARCHAR2,
p_enr_method IN igs_en_cat_prc_dtl.enr_method_type%TYPE,
p_override_cp IN NUMBER,
p_subtitle IN VARCHAR2,
p_gradsch_cd IN VARCHAR2,
p_gs_version_num IN NUMBER,
p_core_indicator_code IN VARCHAR2,
p_calling_obj IN VARCHAR2) AS
l_enr_method_type igs_en_method_type.enr_method_type%TYPE;
END insert_into_enr_worksheet;
PROCEDURE drop_selected_units (
p_uoo_ids IN VARCHAR2,
p_person_id IN NUMBER,
p_person_type IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_program_cd IN VARCHAR2,
p_program_version IN NUMBER ,
p_dcnt_reason_cd IN VARCHAR2 ,
p_admin_unit_status IN VARCHAR2 ,
p_effective_date IN DATE ,
p_failed_uoo_ids OUT NOCOPY VARCHAR2,
p_failed_unit_cds OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2,
p_ovrrd_min_cp_chk IN VARCHAR2 ,
p_ovrrd_crq_chk IN VARCHAR2 , --msrinivi , added new param 2-may-2002
p_ovrrd_prq_chk IN VARCHAR2 , --msrinivi added new param 2-may-2002
p_ovrrd_att_typ_chk IN VARCHAR2
) AS
/* History
WHO WHEN WHAT
smanglm 03-02-2003 call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
svenkata 28-Jan-03 Modified the manner in which the string of unit codes that failed the co-req / pre-req valdns are created.
svenkata 7-Jan-03 Incorporated the logic for 'When first Reach Attendance Type'. The routine enrp_val_coo_att is being called to get the
Att Typ before updating the CP.The routine eval_unit_forced_type is then called called to evaluate with the fetched value-Bug#2737263
svenkata 20-Dec-02 Added a new parameter p_ovrrd_att_type for attendance Type validation. Incorporated Att Type
validation when dropping a Unit section.Bug# 2686793
Nishikant 01-NOV-2002 SEVIS Build Bug#2641905. parameters p_person_id and p_message added in the calls
get_notification.
svenkata 21-oct-02 Bug 2616692 - The call to the fucntion eval_min_cp has been modified to add
4 new parameters .
ayedubat 3-JUL-2002 1.Changed the dynamic sql creation of the cursor,c_ref_cur_inst to conside the 'WAITLISTED' unit attempt status
2.Added a validation to check the existence of records in the table,lData before
looping through the records while doing the prereq validations for the bug fix:2443876
svanukur 04-dec-2003 Passing the load calendar details to the procedure
IGS_EN_VAL_ENCMB.enrp_val_enr_encmb as part of holds bug 3227399
ptandon 16-Feb-2004 Added Exception handling section and handled the exception NO_AUSL_RECORD_FOUND to
return a meaningful error message. Bug# 3418087.
stutta 16-Nov-2004 Validate coreq/prereq rules for all sua before drop operation so as to suppress the rules
which failed even before the drop. Bug # 3926541
*/
l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT DISCONTINUATION_REASON_CD
FROM igs_en_dcnt_reasoncd
WHERE NVL(closed_ind,'N') ='N'
AND dflt_ind ='Y'
AND dcnt_unit_ind ='Y';
SELECT coo_id coo_id
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd ;
SELECT DISTINCT u.person_id
FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V
WHERE person_id =:1
AND course_cd = :2
AND unit_attempt_status IN (''ENROLLED'',''INVALID'',''WAITLISTED'')
AND cal_type = teach_cal_type
AND ci_sequence_number = teach_ci_sequence_number
AND load_cal_type = :3
AND load_ci_sequence_number =:4
AND uoo_id NOT IN('||l_drop_uoo_ids||')'
USING p_person_id, p_program_cd, p_load_cal_type, p_load_sequence_number ;
SELECT U.*
FROM IGS_EN_SU_ATTEMPT U
WHERE person_id =:1 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 = :2
AND load_ci_sequence_number =:3 )
AND uoo_id IN('||l_drop_uoo_ids||')'
USING p_person_id, p_load_cal_type, p_load_sequence_number;
p_selected_uoo_ids => p_uoo_ids,
p_ret_all_uoo_ids => l_drop_alluoo_ids,
p_ret_sub_uoo_ids => l_sub_drop_uoo_ids,
p_ret_nonsub_uoo_ids => l_nonsub_uoo_ids,
p_delete_flag => 'N'
);
OPEN c_chk_sub FOR 'Select ''X'' from igs_en_su_attempt sua where sua.uoo_id in ('||l_sub_drop_uoo_ids||') AND sua.person_id =
'||p_person_id|| 'AND sua.course_cd = '''|| p_program_cd|| '''AND exists (SELECT ''X'' from igs_ps_unit_ofr_opt uoo
WHERE uoo.sup_uoo_id IN (' ||l_drop_alluoo_ids||') AND uoo.relation_type = ''SUBORDINATE'' AND uoo.uoo_id = sua.uoo_id)';
OPEN c_ref_cur_inst FOR ' SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id
= :1 AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND
uoo_id NOT IN('||p_uoo_ids||')'
USING p_person_id;
OPEN c_chk_sub FOR 'Select ''X'' from igs_en_su_attempt sua where sua.uoo_id in ('||l_sub_drop_uoo_ids||') AND sua.person_id =
'||p_person_id|| 'AND sua.course_cd = '''|| p_program_cd|| '''AND exists (SELECT ''X'' from igs_ps_unit_ofr_opt uoo
WHERE uoo.sup_uoo_id IN (' ||l_drop_alluoo_ids||') AND uoo.relation_type = ''SUBORDINATE'' AND uoo.uoo_id = sua.uoo_id)';
END LOOP; -- End of while loop dropping the selected units
OPEN c_ref_cur_inst FOR ' SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id
= :1 AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND
uoo_id NOT IN('||p_uoo_ids||')'
USING p_person_id;
END drop_selected_units;
SELECT igs_en_status_mail_req_s.nextval seq_val
FROM DUAL;
CURSOR cur_user_name IS SELECT user_name FROM fnd_user WHERE user_id = fnd_global.user_id;
CURSOR cur_cal_desc IS SELECT description FROM igs_ca_inst WHERE cal_type = p_load_cal_type
AND sequence_number = p_load_sequence_number;
SELECT igs_en_wf_trans_notif_s.nextval seq_val
FROM DUAL;
SELECT grading_schema_code, grd_schm_version_number
FROM igs_ps_usec_grd_schm
WHERE uoo_id = p_uoo_ids;
SELECT grading_schema_code, grd_schm_version_number
FROM igs_ps_unit_grd_schm
WHERE unit_code = l_unit_cd
AND unit_version_number = l_unit_version ;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_ids ;
SELECT unit_cd,
version_number,
cal_type,
ci_sequence_number,
discontinued_dt,
administrative_unit_status ,
unit_attempt_status,
no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = x_person_id AND
course_cd = x_program_cd AND
uoo_id = x_uoo_id;
SELECT coo_id coo_id
FROM igs_en_stdnt_ps_att
WHERE person_id = x_person_id
AND course_cd = x_program_cd;
SELECT sua.rowid , sua.*
FROM igs_en_su_attempt_all sua
WHERE person_id = x_person_id AND
course_cd = x_program_cd AND
uoo_id = x_uoo_id FOR UPDATE NOWAIT;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = x_person_id
AND course_cd = x_program_cd
AND uoo_id = x_uoo_id;
Igs_En_Su_Attempt_Pkg.update_row
(x_rowid => l_sua_dtls_rec.ROWID,
x_person_id =>l_sua_dtls_rec.person_id,
x_course_cd =>l_sua_dtls_rec.course_cd,
x_unit_cd =>l_sua_dtls_rec.unit_cd,
x_cal_type =>l_sua_dtls_rec.cal_type,
x_ci_sequence_number =>l_sua_dtls_rec.ci_sequence_number,
x_version_number =>l_sua_dtls_rec.version_number,
x_location_cd =>l_sua_dtls_rec.location_cd,
x_unit_class =>l_sua_dtls_rec.unit_class,
x_ci_start_dt =>l_sua_dtls_rec.ci_start_dt,
x_ci_end_dt =>l_sua_dtls_rec.ci_end_dt,
x_uoo_id =>l_sua_dtls_rec.uoo_id,
x_enrolled_dt =>l_sua_dtls_rec.enrolled_dt,
x_unit_attempt_status => l_sua_dtls_rec.unit_attempt_status,
x_administrative_unit_status =>l_sua_dtls_rec.administrative_unit_status,
x_discontinued_dt =>l_sua_dtls_rec.discontinued_dt,
x_dcnt_reason_cd =>l_sua_dtls_rec.dcnt_reason_cd ,
x_rule_waived_dt =>l_sua_dtls_rec.rule_waived_dt,
x_rule_waived_person_id =>l_sua_dtls_rec.rule_waived_person_id,
x_no_assessment_ind =>l_sua_dtls_rec.no_assessment_ind,
x_sup_unit_cd =>l_sua_dtls_rec.sup_unit_cd,
x_sup_version_number =>l_sua_dtls_rec.sup_version_number,
x_exam_location_cd =>l_sua_dtls_rec.exam_location_cd,
x_alternative_title =>l_sua_dtls_rec.alternative_title,
x_override_enrolled_cp =>x_override_enrolled_cp ,
x_override_eftsu =>l_sua_dtls_rec.override_eftsu,
x_override_achievable_cp =>l_sua_dtls_rec.override_achievable_cp,
x_override_outcome_due_dt =>l_sua_dtls_rec.override_outcome_due_dt,
x_override_credit_reason =>l_sua_dtls_rec.override_credit_reason,
x_administrative_priority =>l_sua_dtls_rec.administrative_priority,
x_waitlist_dt =>l_sua_dtls_rec.waitlist_dt,
x_gs_version_number => l_sua_dtls_rec.gs_version_number,
x_enr_method_type => l_sua_dtls_rec.enr_method_type,
x_failed_unit_rule => l_sua_dtls_rec.failed_unit_rule,
x_cart => l_sua_dtls_rec.cart,
x_rsv_seat_ext_id => l_sua_dtls_rec.rsv_seat_ext_id,
x_mode =>'R',
x_org_unit_cd => l_sua_dtls_rec.org_unit_cd,
x_session_id => l_sua_dtls_rec.session_id,
x_grading_schema_code => l_sua_dtls_rec.grading_schema_code,
x_deg_aud_detail_id => l_sua_dtls_rec.deg_aud_detail_id,
x_student_career_transcript => l_sua_dtls_rec.student_career_transcript,
x_student_career_statistics => l_sua_dtls_rec.student_career_statistics,
x_subtitle => l_sua_dtls_rec.subtitle,
x_waitlist_manual_ind => l_sua_dtls_rec.waitlist_manual_ind,
x_attribute_category => l_sua_dtls_rec.attribute_category,
x_attribute1 => l_sua_dtls_rec.attribute1,
x_attribute2 => l_sua_dtls_rec.attribute2,
x_attribute3 => l_sua_dtls_rec.attribute3,
x_attribute4 => l_sua_dtls_rec.attribute4,
x_attribute5 => l_sua_dtls_rec.attribute5,
x_attribute6 => l_sua_dtls_rec.attribute6,
x_attribute7 => l_sua_dtls_rec.attribute7,
x_attribute8 => l_sua_dtls_rec.attribute8,
x_attribute9 => l_sua_dtls_rec.attribute9,
x_attribute10 => l_sua_dtls_rec.attribute10,
x_attribute11 => l_sua_dtls_rec.attribute11,
x_attribute12 => l_sua_dtls_rec.attribute12,
x_attribute13 => l_sua_dtls_rec.attribute13,
x_attribute14 => l_sua_dtls_rec.attribute14,
x_attribute15 => l_sua_dtls_rec.attribute15,
x_attribute16 => l_sua_dtls_rec.attribute16,
x_attribute17 => l_sua_dtls_rec.attribute17,
x_attribute18 => l_sua_dtls_rec.attribute18,
x_attribute19 => l_sua_dtls_rec.attribute19,
x_attribute20 => l_sua_dtls_rec.attribute20,
X_WLST_PRIORITY_WEIGHT_NUM => l_sua_dtls_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> l_sua_dtls_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => l_sua_dtls_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => l_sua_dtls_rec.upd_audit_flag,
X_SS_SOURCE_IND => l_sua_dtls_rec.ss_source_ind
);
SELECT version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT DISCONTINUATION_REASON_CD
FROM igs_en_dcnt_reasoncd
WHERE NVL(closed_ind,'N') ='N'
AND dflt_ind ='Y'
AND dcnt_unit_ind ='Y';
SELECT coo_id coo_id
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd ;
SELECT core_indicator_code
FROM igs_en_su_attempt
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT unit_cd
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
OPEN c_ref_cur_inst FOR 'SELECT DISTINCT U.PERSON_ID FROM IGS_EN_SU_ATTEMPT U, igs_ca_load_to_teach_v WHERE person_id =:1 AND course_cd = :2 '||
'AND unit_attempt_status IN (''ENROLLED'',''INVALID'',''WAITLISTED'') '||
'AND cal_type = teach_cal_type AND ci_sequence_number= teach_ci_sequence_number '||
'AND load_cal_type = :3 AND load_ci_sequence_number =:4 '||
'AND uoo_id <> :5 '
USING p_person_id, p_program_cd, p_load_cal_type, p_load_sequence_number,p_uoo_id ;
OPEN c_ref_cur_inst FOR 'SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id = :1 '||
'AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND ' ||
' uoo_id <> :2'
USING p_person_id, p_uoo_id;
OPEN c_ref_cur_inst FOR 'SELECT U.* FROM IGS_EN_SU_ATTEMPT U WHERE person_id = :1 '||
'AND unit_attempt_status IN (''ENROLLED'',''INVALID'') AND ' ||
' uoo_id <> :2'
USING p_person_id, p_uoo_id;
SELECT rowid
FROM igs_en_su_attempt
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW(l_source_rowid);
insert_into_enr_worksheet(
p_person_number => l_person_number,
p_course_cd => p_program_cd,
p_uoo_id => p_dest_uoo_id,
p_waitlist_ind => p_waitlist_ind,
p_session_id => p_session_id,
p_return_status => p_return_status,
p_message => p_message_name,
p_cal_type => p_cal_type,
p_ci_sequence_number => p_ci_sequence_number,
p_audit_requested => p_audit_requested,
p_enr_method => l_enr_method,
p_core_indicator_code => p_core_indicator_code,
p_calling_obj => 'JOB');
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,
p_delete_flag IN VARCHAR2 DEFAULT 'N'
) AS
------------------------------------------------------------------
--Created by : Satya Vanukuri, Oracle IDC
--Date created: 10-OCT-2003
--
--Purpose: This procedure reorders the units selected for drop such that the subordinate
-- units are processed before the superior units
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
TYPE t_uoo_ref_cur IS REF CURSOR;
v_select_stmt VARCHAR2(2000);
SELECT plan_sht_status
FROM igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_sequence_number = p_load_ci_seq_num
AND plan_sht_status NOT IN ('SKIP' ,'SUB_CART' , 'NONE');
SELECT sua.rowid,sua.* from igs_en_su_attempt sua
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;
igs_en_su_attempt_pkg.delete_row(v_sua_rec.row_id);
IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
X_ROWID => V_SUA_REC.ROW_ID,
X_PERSON_ID => V_SUA_REC.PERSON_ID,
X_COURSE_CD =>V_SUA_REC.COURSE_CD ,
X_UNIT_CD =>V_SUA_REC.UNIT_CD,
X_CAL_TYPE =>V_SUA_REC.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => V_SUA_REC.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER =>V_SUA_REC.VERSION_NUMBER ,
X_LOCATION_CD =>V_SUA_REC.LOCATION_CD,
X_UNIT_CLASS =>V_SUA_REC.UNIT_CLASS,
X_CI_START_DT =>V_SUA_REC.CI_START_DT,
X_CI_END_DT => V_SUA_REC.CI_END_DT,
X_UOO_ID =>V_SUA_REC.UOO_ID,
X_ENROLLED_DT =>V_SUA_REC.ENROLLED_DT ,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS =>V_SUA_REC.ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT =>V_SUA_REC.DISCONTINUED_DT,
X_RULE_WAIVED_DT =>V_SUA_REC.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID =>V_SUA_REC.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND =>V_SUA_REC.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD =>V_SUA_REC.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER =>V_SUA_REC.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD =>V_SUA_REC.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE =>V_SUA_REC.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP =>V_SUA_REC.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU =>V_SUA_REC.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP =>V_SUA_REC.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT =>V_SUA_REC.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON =>V_SUA_REC.OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY =>V_SUA_REC.ADMINISTRATIVE_PRIORITY ,
X_WAITLIST_DT =>V_SUA_REC.WAITLIST_DT,
X_DCNT_REASON_CD =>V_SUA_REC.DCNT_REASON_CD,
X_MODE =>'R',
X_GS_VERSION_NUMBER =>V_SUA_REC.GS_VERSION_NUMBER,
X_ENR_METHOD_TYPE =>V_SUA_REC.ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE =>V_SUA_REC.FAILED_UNIT_RULE,
X_CART => V_SUA_REC.CART ,
X_RSV_SEAT_EXT_ID =>V_SUA_REC.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD =>V_SUA_REC.ORG_UNIT_CD ,
X_GRADING_SCHEMA_CODE =>V_SUA_REC.GRADING_SCHEMA_CODE,
X_subtitle =>V_SUA_REC.subtitle,
x_session_id =>V_SUA_REC.session_id ,
X_deg_aud_detail_id =>V_SUA_REC.deg_aud_detail_id,
x_student_career_transcript =>V_SUA_REC.student_career_transcript,
x_student_career_statistics =>V_SUA_REC.student_career_statistics,
X_WAITLIST_MANUAL_IND =>V_SUA_REC.WAITLIST_MANUAL_IND ,
X_ATTRIBUTE_CATEGORY =>V_SUA_REC.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>V_SUA_REC.ATTRIBUTE1,
X_ATTRIBUTE2 =>V_SUA_REC.ATTRIBUTE2,
X_ATTRIBUTE3 =>V_SUA_REC.ATTRIBUTE3,
X_ATTRIBUTE4 =>V_SUA_REC.ATTRIBUTE4,
X_ATTRIBUTE5 =>V_SUA_REC.ATTRIBUTE5,
X_ATTRIBUTE6 =>V_SUA_REC.ATTRIBUTE6,
X_ATTRIBUTE7 =>V_SUA_REC.ATTRIBUTE7,
X_ATTRIBUTE8 =>V_SUA_REC.ATTRIBUTE8,
X_ATTRIBUTE9 =>V_SUA_REC.ATTRIBUTE9,
X_ATTRIBUTE10 =>V_SUA_REC.ATTRIBUTE10,
X_ATTRIBUTE11 =>V_SUA_REC.ATTRIBUTE11,
X_ATTRIBUTE12 =>V_SUA_REC.ATTRIBUTE12,
X_ATTRIBUTE13 =>V_SUA_REC.ATTRIBUTE13,
X_ATTRIBUTE14 =>V_SUA_REC.ATTRIBUTE14,
X_ATTRIBUTE15 =>V_SUA_REC.ATTRIBUTE15,
X_ATTRIBUTE16 =>V_SUA_REC.ATTRIBUTE16,
X_ATTRIBUTE17 =>V_SUA_REC.ATTRIBUTE17,
X_ATTRIBUTE18 =>V_SUA_REC.ATTRIBUTE18,
X_ATTRIBUTE19 =>V_SUA_REC.ATTRIBUTE19,
X_ATTRIBUTE20 =>V_SUA_REC.ATTRIBUTE20,
X_WLST_PRIORITY_WEIGHT_NUM =>V_SUA_REC.WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM =>V_SUA_REC.WLST_PREFERENCE_WEIGHT_NUM
);
PROCEDURE delete_plansheet_subunit(
P_PERSON_ID IN NUMBER,
P_COURSE_CD IN VARCHAR2,
P_UOOID IN NUMBER
) AS
CURSOR cur_plan_unit IS
SELECT pl.rowid, pl.term_cal_type, pl.term_ci_sequence_number,pl.uoo_id
FROM igs_en_plan_units pl
WHERE pl.person_id = p_person_id
AND pl.course_cd = p_course_cd
AND EXISTS ( SELECT 'X'
FROM IGS_PS_UNIT_OFR_OPT UOO
WHERE UOO.SUP_UOO_ID = P_UOOID
AND UOO.RELATION_TYPE = 'SUBORDINATE'
AND UOO.UOO_ID = pl.UOO_ID)
ORDER BY pl.SUP_Uoo_id;
SELECT spl.spl_perm_request_id
FROM igs_en_spl_perm spl
WHERE spl.student_person_id= p_person_id
AND spl.uoo_id = cp_uoo_id
AND spl.transaction_type <> 'WITHDRAWN';
igs_en_plan_units_pkg.delete_row (x_rowid => cur_plan_unit_rec.rowid);
v_select_stmt := ' SELECT SUA.UOO_ID, SUA.SUP_UNIT_CD '
|| ' FROM IGS_EN_SU_ATTEMPT SUA '
|| ' WHERE SUA.PERSON_ID = :1 '
|| ' AND SUA.COURSE_CD = :2 '
|| ' AND SUA.UNIT_ATTEMPT_STATUS NOT IN (''DROPPED'',''DISCONTIN'',''COMPLETED'') '
|| ' AND (( SUA.UOO_ID IN (' || P_SELECTED_UOO_IDS || ') )'
|| ' OR ( EXISTS ( SELECT ''X'' '
|| ' 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 = SUA.UOO_ID ) ) ) '
|| ' ORDER BY SUA.SUP_UNIT_CD ';
IF P_SELECTED_UOO_IDS IS NULL THEN
RETURN;
OPEN c_sub_sua_cur_inst FOR v_select_stmt USING p_person_id, p_course_cd;
IF NVL(p_delete_flag,'N') = 'Y' THEN
L_UPD_DEL_SUA(
p_person_id,
p_course_cd,
v_uoo_id);
delete_plansheet_subunit(
p_person_id ,
p_course_cd ,
v_uoo_id ) ;
PROCEDURE update_spa_plan_sts( p_n_person_id IN NUMBER,
p_c_program_cd IN VARCHAR2,
p_c_cal_type IN VARCHAR2,
p_n_seq_num IN NUMBER,
p_c_plan_sts IN VARCHAR2) IS
------------------------------------------------------------------
--Created by : Somasekar, Oracle IDC
--Date created: 17-May-2005
--
--Purpose: procedure to update the terms SPA
-- planning sheet status for a student.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR c_spa_terms (cp_n_person_id IN NUMBER,
cp_c_program_cd IN VARCHAR2,
cp_c_cal_type IN VARCHAR2,
cp_n_seq_num IN NUMBER) IS
SELECT spa.ROWID ROW_ID, spa.*
FROM igs_en_spa_terms spa
WHERE person_id = cp_n_person_id
AND program_cd = cp_c_program_cd
AND term_cal_type = cp_c_cal_type
AND term_sequence_number = cp_n_seq_num;
igs_en_spa_terms_pkg.update_row(
x_rowid =>rec_spa_terms.row_id,
x_term_record_id =>rec_spa_terms.term_record_id,
x_person_id =>rec_spa_terms.person_id,
x_program_cd =>rec_spa_terms.program_cd,
x_program_version =>rec_spa_terms.program_version,
x_acad_cal_type =>rec_spa_terms.acad_cal_type,
x_term_cal_type =>rec_spa_terms.term_cal_type,
x_term_sequence_number =>rec_spa_terms.term_sequence_number,
x_key_program_flag =>rec_spa_terms.key_program_flag,
x_location_cd =>rec_spa_terms.location_cd,
x_attendance_mode =>rec_spa_terms.attendance_mode,
x_attendance_type =>rec_spa_terms.attendance_type,
x_fee_cat =>rec_spa_terms.fee_cat,
x_coo_id =>rec_spa_terms.coo_id,
x_class_standing_id =>rec_spa_terms.class_standing_id,
x_attribute_category =>rec_spa_terms.attribute_category,
x_attribute1 =>rec_spa_terms.attribute1,
x_attribute2 =>rec_spa_terms.attribute2,
x_attribute3 =>rec_spa_terms.attribute3,
x_attribute4 =>rec_spa_terms.attribute4,
x_attribute5 =>rec_spa_terms.attribute5,
x_attribute6 =>rec_spa_terms.attribute6,
x_attribute7 =>rec_spa_terms.attribute7,
x_attribute8 =>rec_spa_terms.attribute8,
x_attribute9 =>rec_spa_terms.attribute9,
x_attribute10 =>rec_spa_terms.attribute10,
x_attribute11 =>rec_spa_terms.attribute11,
x_attribute12 =>rec_spa_terms.attribute12,
x_attribute13 =>rec_spa_terms.attribute13,
x_attribute14 =>rec_spa_terms.attribute14,
x_attribute15 =>rec_spa_terms.attribute15,
x_attribute16 =>rec_spa_terms.attribute16,
x_attribute17 =>rec_spa_terms.attribute17,
x_attribute18 =>rec_spa_terms.attribute18,
x_attribute19 =>rec_spa_terms.attribute19,
x_attribute20 =>rec_spa_terms.attribute20,
x_mode =>'R',
x_plan_sht_status =>p_c_plan_sts);
END update_spa_plan_sts;
PROCEDURE update_grading_schema(
p_person_id IN NUMBER,
p_uoo_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_grading_schema IN VARCHAR2,
p_gs_version IN NUMBER,
p_message OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : Vijay Rajagopal, Oracle IDC
--Date created: 8-JUN-2005
--
--Purpose: This procedure updates the override values of grading schema of an unit attempt
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
Cursor cur_grading(cp_person_id NUMBER,
cp_course_cd VARCHAR2,
cp_uoo_id NUMBER) IS
SELECT su.ROWID , su.*
FROM IGS_EN_SU_ATTEMPT_ALL su
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
igs_en_su_attempt_pkg.update_row (
x_rowid => l_data.rowid,
x_person_id => l_data.person_id,
x_course_cd => l_data.course_cd,
x_unit_cd => l_data.unit_cd,
x_cal_type => l_data.cal_type,
x_ci_sequence_number => l_data.ci_sequence_number,
x_version_number => l_data.version_number,
x_location_cd => l_data.location_cd,
x_unit_class => l_data.unit_class,
x_ci_start_dt => l_data.ci_start_dt,
x_ci_end_dt => l_data.ci_end_dt,
x_uoo_id => l_data.uoo_id,
x_enrolled_dt => l_data.enrolled_dt,
x_unit_attempt_status => l_data.unit_attempt_status,
x_administrative_unit_status => l_data.administrative_unit_status,
x_discontinued_dt => l_data.discontinued_dt,
x_rule_waived_dt => l_data.rule_waived_dt,
x_rule_waived_person_id => l_data.rule_waived_person_id,
x_no_assessment_ind => l_data.no_assessment_ind,
x_sup_unit_cd => l_data.sup_unit_cd,
x_sup_version_number => l_data.sup_version_number,
x_exam_location_cd => l_data.exam_location_cd,
x_alternative_title => l_data.alternative_title,
x_override_enrolled_cp => l_data.override_enrolled_cp,
x_override_eftsu => l_data.override_eftsu ,
x_override_achievable_cp => l_data.override_achievable_cp,
x_override_outcome_due_dt => l_data.override_outcome_due_dt,
x_override_credit_reason => l_data.override_credit_reason,
x_administrative_priority => l_data.administrative_priority,
x_waitlist_dt => l_data.waitlist_dt,
x_dcnt_reason_cd => l_data.dcnt_reason_cd,
x_mode => 'R',
x_gs_version_number => p_gs_version,
x_enr_method_type => l_data.enr_method_type,
x_failed_unit_rule => l_data.failed_unit_rule,
x_cart => l_data.cart,
x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
x_org_unit_cd => l_data.org_unit_cd,
x_grading_schema_code => p_grading_schema,
x_subtitle => l_data.subtitle,
x_session_id => l_data.session_id,
x_deg_aud_detail_id => l_data.deg_aud_detail_id,
x_student_career_transcript => l_data.student_career_transcript,
x_student_career_statistics => l_data.student_career_statistics,
x_waitlist_manual_ind => l_data.waitlist_manual_ind,
x_attribute_category => l_data.attribute_category,
x_attribute1 => l_data.attribute1,
x_attribute2 => l_data.attribute2,
x_attribute3 => l_data.attribute3,
x_attribute4 => l_data.attribute4,
x_attribute5 => l_data.attribute5,
x_attribute6 => l_data.attribute6,
x_attribute7 => l_data.attribute7,
x_attribute8 => l_data.attribute8,
x_attribute9 => l_data.attribute9,
x_attribute10 => l_data.attribute10,
x_attribute11 => l_data.attribute11,
x_attribute12 => l_data.attribute12,
x_attribute13 => l_data.attribute13,
x_attribute14 => l_data.attribute14,
x_attribute15 => l_data.attribute15,
x_attribute16 => l_data.attribute16,
x_attribute17 => l_data.attribute17,
x_attribute18 => l_data.attribute18,
x_attribute19 => l_data.attribute19,
x_attribute20 => l_data.attribute20,
x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
x_core_indicator_code => l_data.core_indicator_code ,
x_upd_audit_flag => l_data.upd_audit_flag,
x_ss_source_ind => l_data.ss_source_ind
);
END update_grading_schema;
PROCEDURE update_credit_points(
p_person_id IN NUMBER,
p_person_type IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_uoo_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_course_version IN NUMBER,
p_override_enrolled_cp IN NUMBER,
p_message OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : Vijay Rajagopal, Oracle IDC
--Date created: 9-JUN-2005
--
--Purpose: This procedure updates the override credit points of a unit attempt.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR cur_credit(cp_person_id NUMBER,
cp_course_cd VARCHAR2,
cp_uoo_id NUMBER) IS
SELECT su.ROWID, su.*
FROM IGS_EN_SU_ATTEMPT_ALL su
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
igs_en_su_attempt_pkg.update_row (
x_rowid => l_data.rowid,
x_person_id => l_data.person_id,
x_course_cd => l_data.course_cd,
x_unit_cd => l_data.unit_cd,
x_cal_type => l_data.cal_type,
x_ci_sequence_number => l_data.ci_sequence_number,
x_version_number => l_data.version_number,
x_location_cd => l_data.location_cd,
x_unit_class => l_data.unit_class,
x_ci_start_dt => l_data.ci_start_dt,
x_ci_end_dt => l_data.ci_end_dt,
x_uoo_id => l_data.uoo_id,
x_enrolled_dt => l_data.enrolled_dt,
x_unit_attempt_status => l_data.unit_attempt_status,
x_administrative_unit_status => l_data.administrative_unit_status,
x_discontinued_dt => l_data.discontinued_dt,
x_rule_waived_dt => l_data.rule_waived_dt,
x_rule_waived_person_id => l_data.rule_waived_person_id,
x_no_assessment_ind => l_data.no_assessment_ind,
x_sup_unit_cd => l_data.sup_unit_cd,
x_sup_version_number => l_data.sup_version_number,
x_exam_location_cd => l_data.exam_location_cd,
x_alternative_title => l_data.alternative_title,
x_override_enrolled_cp => p_override_enrolled_cp,
x_override_eftsu => l_data.override_eftsu ,
x_override_achievable_cp => l_data.override_achievable_cp,
x_override_outcome_due_dt => l_data.override_outcome_due_dt,
x_override_credit_reason => l_data.override_credit_reason,
x_administrative_priority => l_data.administrative_priority,
x_waitlist_dt => l_data.waitlist_dt,
x_dcnt_reason_cd => l_data.dcnt_reason_cd,
x_mode => 'R',
x_gs_version_number => l_data.gs_version_number,
x_enr_method_type => l_data.enr_method_type,
x_failed_unit_rule => l_data.failed_unit_rule,
x_cart => l_data.cart,
x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
x_org_unit_cd => l_data.org_unit_cd,
x_grading_schema_code => l_data.grading_schema_code,
x_subtitle => l_data.subtitle,
x_session_id => l_data.session_id,
x_deg_aud_detail_id => l_data.deg_aud_detail_id,
x_student_career_transcript => l_data.student_career_transcript,
x_student_career_statistics => l_data.student_career_statistics,
x_waitlist_manual_ind => l_data.waitlist_manual_ind,
x_attribute_category => l_data.attribute_category,
x_attribute1 => l_data.attribute1,
x_attribute2 => l_data.attribute2,
x_attribute3 => l_data.attribute3,
x_attribute4 => l_data.attribute4,
x_attribute5 => l_data.attribute5,
x_attribute6 => l_data.attribute6,
x_attribute7 => l_data.attribute7,
x_attribute8 => l_data.attribute8,
x_attribute9 => l_data.attribute9,
x_attribute10 => l_data.attribute10,
x_attribute11 => l_data.attribute11,
x_attribute12 => l_data.attribute12,
x_attribute13 => l_data.attribute13,
x_attribute14 => l_data.attribute14,
x_attribute15 => l_data.attribute15,
x_attribute16 => l_data.attribute16,
x_attribute17 => l_data.attribute17,
x_attribute18 => l_data.attribute18,
x_attribute19 => l_data.attribute19,
x_attribute20 => l_data.attribute20,
x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
x_core_indicator_code => l_data.core_indicator_code ,
x_upd_audit_flag => l_data.upd_audit_flag,
x_ss_source_ind => l_data.ss_source_ind
);
END update_credit_points;
PROCEDURE update_audit(
p_person_id IN NUMBER,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_uoo_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_no_assessment_ind IN VARCHAR2,
p_override_cp IN NUMBER,
p_message OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : Vijay Rajagopal, Oracle IDC
--Date created: 9-JUN-2005
--
--Purpose: This procedure updates the no_assessment_indicator of a unit attempt
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR c_usec_audit_lim IS
SELECT NVL (usec.max_auditors_allowed, NVL(uv.max_auditors_allowed,999999) )
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 COUNT(*)
FROM igs_en_su_attempt
WHERE uoo_id=p_uoo_id
AND no_assessment_ind = 'Y'
AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','INVALID','UNCONFIRM' ) ;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';
SELECT approval_status
FROM Igs_En_Spl_Perm
WHERE student_person_id = p_person_id
AND uoo_id = p_uoo_id
AND request_type = 'AUDIT_PERM'
AND transaction_type <> 'WITHDRAWN' ;
SELECT NVL(auditable_ind, 'N'), unit_cd, unit_class, NVL(audit_permission_ind, 'N')
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT eru.stud_audit_lim
FROM igs_en_cpd_ext eru, igs_lookups_view lkv
WHERE eru.s_enrolment_step_type =lkv.lookup_code
AND lkv.lookup_type = 'ENROLMENT_STEP_TYPE_EXT'
AND lkv.step_group_type = 'UNIT'
AND eru.enrolment_cat = cp_enr_cat
AND eru.enr_method_type = cp_enr_method
AND ( eru.s_student_comm_type = cp_comm_type OR eru.s_student_comm_type = 'ALL')
AND s_enrolment_step_type = cp_step_type
ORDER BY eru.step_order_num;
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE person_id=p_person_id
AND course_cd= p_course_cd;
SELECT su.ROWID , su.*
FROM IGS_EN_SU_ATTEMPT_ALL su
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
igs_en_su_attempt_pkg.update_row (
x_rowid => l_data.rowid,
x_person_id => l_data.person_id,
x_course_cd => l_data.course_cd,
x_unit_cd => l_data.unit_cd,
x_cal_type => l_data.cal_type,
x_ci_sequence_number => l_data.ci_sequence_number,
x_version_number => l_data.version_number,
x_location_cd => l_data.location_cd,
x_unit_class => l_data.unit_class,
x_ci_start_dt => l_data.ci_start_dt,
x_ci_end_dt => l_data.ci_end_dt,
x_uoo_id => l_data.uoo_id,
x_enrolled_dt => l_data.enrolled_dt,
x_unit_attempt_status => l_data.unit_attempt_status,
x_administrative_unit_status => l_data.administrative_unit_status,
x_discontinued_dt => l_data.discontinued_dt,
x_rule_waived_dt => l_data.rule_waived_dt,
x_rule_waived_person_id => l_data.rule_waived_person_id,
x_no_assessment_ind => p_no_assessment_ind,
x_sup_unit_cd => l_data.sup_unit_cd,
x_sup_version_number => l_data.sup_version_number,
x_exam_location_cd => l_data.exam_location_cd,
x_alternative_title => l_data.alternative_title,
x_override_enrolled_cp => l_override_enrolled_cp,
x_override_eftsu => l_override_eftsu ,
x_override_achievable_cp => NULL,
x_override_outcome_due_dt => l_data.override_outcome_due_dt,
x_override_credit_reason => l_override_credit_reason,
x_administrative_priority => l_data.administrative_priority,
x_waitlist_dt => l_data.waitlist_dt,
x_dcnt_reason_cd => l_data.dcnt_reason_cd,
x_mode => 'R',
x_gs_version_number => l_data.gs_version_number,
x_enr_method_type => l_data.enr_method_type,
x_failed_unit_rule => l_data.failed_unit_rule,
x_cart => l_data.cart,
x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
x_org_unit_cd => l_data.org_unit_cd,
x_grading_schema_code => l_data.grading_schema_code,
x_subtitle => l_data.subtitle,
x_session_id => l_data.session_id,
x_deg_aud_detail_id => l_data.deg_aud_detail_id,
x_student_career_transcript => l_data.student_career_transcript,
x_student_career_statistics => l_data.student_career_statistics,
x_waitlist_manual_ind => l_data.waitlist_manual_ind,
x_attribute_category => l_data.attribute_category,
x_attribute1 => l_data.attribute1,
x_attribute2 => l_data.attribute2,
x_attribute3 => l_data.attribute3,
x_attribute4 => l_data.attribute4,
x_attribute5 => l_data.attribute5,
x_attribute6 => l_data.attribute6,
x_attribute7 => l_data.attribute7,
x_attribute8 => l_data.attribute8,
x_attribute9 => l_data.attribute9,
x_attribute10 => l_data.attribute10,
x_attribute11 => l_data.attribute11,
x_attribute12 => l_data.attribute12,
x_attribute13 => l_data.attribute13,
x_attribute14 => l_data.attribute14,
x_attribute15 => l_data.attribute15,
x_attribute16 => l_data.attribute16,
x_attribute17 => l_data.attribute17,
x_attribute18 => l_data.attribute18,
x_attribute19 => l_data.attribute19,
x_attribute20 => l_data.attribute20,
x_wlst_priority_weight_num => l_data.wlst_priority_weight_num,
x_wlst_preference_weight_num => l_data.wlst_preference_weight_num,
x_core_indicator_code => l_data.core_indicator_code ,
x_upd_audit_flag => 'N',
x_ss_source_ind => l_data.ss_source_ind
);
END update_audit;
SELECT spl.ROWID,spl.*
FROM igs_en_spl_perm spl
WHERE spl.spl_perm_request_id=cp_request_id;
igs_en_spl_perm_pkg.update_row(
x_rowid => l_data.rowid,
x_spl_perm_request_id => l_data.spl_perm_request_id,
x_student_person_id => l_data.student_person_id,
x_uoo_id => l_data.uoo_id,
x_date_submission => l_data.date_submission,
x_audit_the_course => l_data.audit_the_course,
x_instructor_person_id => l_data.instructor_person_id,
x_approval_status => 'W',
x_reason_for_request => l_data.reason_for_request,
x_instructor_more_info => l_data.instructor_more_info,
x_instructor_deny_info => l_data.instructor_deny_info,
x_student_more_info => l_data.student_more_info,
x_transaction_type => 'WITHDRAWN',
x_request_type => l_data.request_type,
x_mode => 'R'
);
PROCEDURE update_core_indicator(
p_person_id IN NUMBER,
p_uoo_id IN NUMBER,
p_program_cd IN VARCHAR2,
p_core_indicator IN VARCHAR2,
p_message OUT NOCOPY VARCHAR2
) AS
------------------------------------------------------------------
--Created by : Siva Gurusamy, Oracle IDC
--Date created: 12-Aug-05
--
--Purpose:
-- This is a new function to update the core indicator code for a unit section
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
--sgurusam 12-Aug-05 Created
------------1-------------------------------------------------------
--cursor to fetch the rowid and other attributes from the student unit attempt for the passed in information
CURSOR cur_core_ind(p_person_id NUMBER,
p_course_cd VARCHAR2,
p_uoo_id NUMBER) IS
SELECT su.ROWID, su.*
FROM IGS_EN_SU_ATTEMPT_ALL su
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;
SAVEPOINT update_core_indicator;
Igs_En_Su_Attempt_Pkg.update_row(
x_rowid =>l_data.ROWID,
x_person_id =>l_data.person_id,
x_course_cd =>l_data.course_cd,
x_unit_cd =>l_data.unit_cd,
x_cal_type =>l_data.cal_type,
x_ci_sequence_number =>l_data.ci_sequence_number,
x_version_number =>l_data.version_number,
x_location_cd =>l_data.location_cd,
x_unit_class =>l_data.unit_class,
x_ci_start_dt =>l_data.ci_start_dt,
x_ci_end_dt =>l_data.ci_end_dt,
x_uoo_id =>l_data.uoo_id,
x_enrolled_dt =>l_data.enrolled_dt,
x_unit_attempt_status => l_data.unit_attempt_status,
x_administrative_unit_status => l_data.administrative_unit_status,
x_discontinued_dt =>l_data.discontinued_dt,
x_dcnt_reason_cd =>l_data.dcnt_reason_cd ,
x_rule_waived_dt =>l_data.rule_waived_dt,
x_rule_waived_person_id =>l_data.rule_waived_person_id,
x_no_assessment_ind =>l_data.no_assessment_ind,
x_sup_unit_cd =>l_data.sup_unit_cd,
x_sup_version_number =>l_data.sup_version_number,
x_exam_location_cd =>l_data.exam_location_cd,
x_alternative_title =>l_data.alternative_title,
x_override_enrolled_cp =>l_data.override_enrolled_cp ,
x_override_eftsu =>l_data.override_eftsu,
x_override_achievable_cp =>l_data.override_achievable_cp,
x_override_outcome_due_dt =>l_data.override_outcome_due_dt,
x_override_credit_reason =>l_data.override_credit_reason,
x_administrative_priority =>l_data.administrative_priority,
x_waitlist_dt =>l_data.waitlist_dt,
x_gs_version_number => l_data.gs_version_number,
x_enr_method_type => l_data.enr_method_type,
x_failed_unit_rule => l_data.failed_unit_rule,
x_cart => l_data.cart,
x_rsv_seat_ext_id => l_data.rsv_seat_ext_id,
x_mode => 'R',
x_org_unit_cd => l_data.org_unit_cd,
x_session_id => l_data.session_id,
x_grading_schema_code => l_data.grading_schema_code,
x_deg_aud_detail_id => l_data.deg_aud_detail_id,
x_student_career_transcript => l_data.student_career_transcript,
x_student_career_statistics => l_data.student_career_statistics,
x_subtitle => l_data.subtitle,
x_waitlist_manual_ind => l_data.waitlist_manual_ind,
x_attribute_category => l_data.attribute_category,
x_attribute1 => l_data.attribute1,
x_attribute2 => l_data.attribute2,
x_attribute3 => l_data.attribute3,
x_attribute4 => l_data.attribute4,
x_attribute5 => l_data.attribute5,
x_attribute6 => l_data.attribute6,
x_attribute7 => l_data.attribute7,
x_attribute8 => l_data.attribute8,
x_attribute9 => l_data.attribute9,
x_attribute10 => l_data.attribute10,
x_attribute11 => l_data.attribute11,
x_attribute12 => l_data.attribute12,
x_attribute13 => l_data.attribute13,
x_attribute14 => l_data.attribute14,
x_attribute15 => l_data.attribute15,
x_attribute16 => l_data.attribute16,
x_attribute17 => l_data.attribute17,
x_attribute18 => l_data.attribute18,
x_attribute19 => l_data.attribute19,
x_attribute20 => l_data.attribute20,
X_WLST_PRIORITY_WEIGHT_NUM => l_data.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> l_data.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => l_core_indicator,
X_UPD_AUDIT_FLAG => l_data.upd_audit_flag,
X_SS_SOURCE_IND => l_data.ss_source_ind
);
ROLLBACK TO update_core_indicator;
ROLLBACK TO update_core_indicator;
END update_core_indicator;
p_select_allowed OUT NOCOPY VARCHAR2,
p_update_allowed OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2
)
------------------------------------------------------------------
--Created by : Siva Gurusamy, Oracle IDC
--Date created: 12-Aug-05
--
--Purpose:
-- This is a new function to check the securty levels for the schedule page
-- Implementation
-- 1. Checks if logged in user has select/update access to the Student's program attempt object and update access to the Student's unit attempt object
-- 2. If p_mode is B, then checks if logged in user has select/update access to the Student's program attempt object
-- 3. If value of Out parameter p_select_allowed is 'Y', the user has select access to the student's program attempt object
-- 4. If value of Out parameter p_update_allowed is 'Y', the user has update access to the student's program/unit attempt object
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
--sgurusam 12-Aug-05 Created
-------------------------------------------------------------------
AS
--cursor to get the rowid for the program attempt of the student
CURSOR cur_program(p_person_id NUMBER, p_course_cd VARCHAR2) IS
SELECT spat.ROWID
FROM igs_en_stdnt_ps_att_all spat
WHERE spat.person_id = p_person_id
AND spat.course_cd = p_course_cd;
SELECT sua.ROWID
FROM igs_en_su_attempt_all sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.uoo_id = p_uoo_id;
p_select_allowed := 'N';
p_update_allowed := 'N';
p_select_allowed := 'Y';
p_update_allowed := 'Y';
p_select_allowed := 'Y';
p_update_allowed := 'Y';
p_update_allowed := 'Y';
PROCEDURE update_audit_flag(p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER,
p_upd_audit_flag IN VARCHAR2)
AS
CURSOR c_sua (p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER)
IS
SELECT *
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW( -- calling the TBH
X_ROWID => l_sua_rec.ROW_ID,
X_PERSON_ID => l_sua_rec.PERSON_ID,
X_COURSE_CD => l_sua_rec.COURSE_CD ,
X_UNIT_CD => l_sua_rec.UNIT_CD,
X_CAL_TYPE => l_sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => l_sua_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => l_sua_rec.VERSION_NUMBER ,
X_LOCATION_CD => l_sua_rec.LOCATION_CD,
X_UNIT_CLASS => l_sua_rec.UNIT_CLASS ,
X_CI_START_DT => l_sua_rec.CI_START_DT,
X_CI_END_DT => l_sua_rec.CI_END_DT,
X_UOO_ID => l_sua_rec.UOO_ID ,
X_ENROLLED_DT => l_sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => l_sua_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => l_sua_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => l_sua_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => l_sua_rec.discontinued_dt,
X_DCNT_REASON_CD => l_sua_rec.DCNT_REASON_CD,
X_RULE_WAIVED_DT => l_sua_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => l_sua_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => l_sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => l_sua_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => l_sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => l_sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => l_sua_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => l_sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => l_sua_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => l_sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => l_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => l_sua_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => l_sua_rec.waitlist_dt,
X_MODE => 'R',
X_GS_VERSION_NUMBER => l_sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => l_sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => l_sua_rec.FAILED_UNIT_RULE,
X_CART => l_sua_rec.CART,
X_RSV_SEAT_EXT_ID => l_sua_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => l_sua_rec.org_unit_cd ,
X_SESSION_ID => l_sua_rec.session_id,
X_GRADING_SCHEMA_CODE => l_sua_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => l_sua_rec.deg_aud_detail_id,
X_SUBTITLE => l_sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => l_sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => l_sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => l_sua_rec.attribute_category,
X_ATTRIBUTE1 => l_sua_rec.attribute1,
X_ATTRIBUTE2 => l_sua_rec.attribute2,
X_ATTRIBUTE3 => l_sua_rec.attribute3,
X_ATTRIBUTE4 => l_sua_rec.attribute4,
X_ATTRIBUTE5 => l_sua_rec.attribute5,
X_ATTRIBUTE6 => l_sua_rec.attribute6,
X_ATTRIBUTE7 => l_sua_rec.attribute7,
X_ATTRIBUTE8 => l_sua_rec.attribute8,
X_ATTRIBUTE9 => l_sua_rec.attribute9,
X_ATTRIBUTE10 => l_sua_rec.attribute10,
X_ATTRIBUTE11 => l_sua_rec.attribute11,
X_ATTRIBUTE12 => l_sua_rec.attribute12,
X_ATTRIBUTE13 => l_sua_rec.attribute13,
X_ATTRIBUTE14 => l_sua_rec.attribute14,
X_ATTRIBUTE15 => l_sua_rec.attribute15,
X_ATTRIBUTE16 => l_sua_rec.attribute16,
X_ATTRIBUTE17 => l_sua_rec.attribute17,
X_ATTRIBUTE18 => l_sua_rec.attribute18,
X_ATTRIBUTE19 => l_sua_rec.attribute19,
X_ATTRIBUTE20 => l_sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => l_sua_rec.waitlist_manual_ind ,
X_WLST_PRIORITY_WEIGHT_NUM => l_sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => l_sua_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => p_upd_audit_flag, -- updating audit flag
X_SS_SOURCE_IND => l_sua_rec.ss_source_ind
);
END update_audit_flag;
SELECT 'X' FROM igs_en_spl_perm perm
WHERE perm.uoo_id = p_uoo_id
AND perm.student_person_id = p_person_id
AND perm.approval_status <> 'A'
AND perm.request_type <> p_request_type;
SELECT 'X' FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id
AND sua.course_Cd = p_course_cd
AND sua.uoo_id = p_uoo_id;
SELECT 'x'
FROM igs_en_su_attempt sua,
igs_ca_teach_to_load_v ltt
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'UNCONFIRM'
AND NVL(sua.SS_SOURCE_IND,'N') <> 'S'
AND ltt.load_cal_type = p_load_cal_type
AND ltt.load_ci_sequence_number = p_load_sequence_number
AND ltt.teach_cal_type = sua.cal_type
AND ltt.teach_ci_sequence_number = sua.ci_sequence_number;