[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT unit_cd || '/' || unit_class
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = cp_uooid;
IGS_EN_PLAN_UNITS_PKG.INSERT_ROW(
x_rowid => l_row_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => l_uooid,
x_term_cal_type => p_load_cal_type,
x_term_ci_sequence_number => p_load_sequence_number,
x_no_assessment_ind => l_audit,
x_sup_uoo_id => NULL,
x_override_enrolled_cp => l_credits,
x_grading_schema_code => l_grading_code,
x_gs_version_number => l_grading_version,
x_core_indicator_code => NULL,
x_alternative_title => NULL,
x_cart_error_flag => 'N',
x_session_id => igs_en_add_units_api.g_ss_session_id,
x_mode => 'R');
PROCEDURE update_spa_terms_plan_sht_flag(
P_PERSON_ID IN NUMBER,
P_COURSE_CD IN VARCHAR2,
P_TERM_CAL_TYPE IN VARCHAR2,
P_TERM_SEQUENCE_NUMBER IN NUMBER,
P_PLAN_SHT_FLAG IN VARCHAR2
) AS
------------------------------------------------------------------
--Created by : ctyagi, Oracle IDC
--Date created: 18-JULY-2005
--
--Purpose: update plan_sht_status column of igs_en_spa_terms.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR select_rec IS
SELECT spt.rowid,spt.*
FROM igs_en_spa_terms spt
WHERE PERSON_ID=P_PERSON_ID
AND PROGRAM_CD=P_COURSE_CD
AND TERM_CAL_TYPE=P_TERM_CAL_TYPE
AND TERM_SEQUENCE_NUMBER=P_TERM_SEQUENCE_NUMBER;
old_references select_rec%ROWTYPE;
OPEN select_rec ;
FETCH select_rec INTO old_references;
IF select_rec%NOTFOUND THEN
IF P_PLAN_SHT_FLAG = 'SKIP' THEN
IF p_person_id IS NOT NULL THEN
-- Call the API to Create/Update the term record.
igs_en_spa_terms_api.create_update_term_rec(p_person_id => P_PERSON_ID,
p_program_cd => P_COURSE_CD,
p_term_cal_type => P_TERM_CAL_TYPE,
p_term_sequence_number => P_TERM_SEQUENCE_NUMBER,
p_plan_sht_status => 'PLAN',
p_ripple_frwrd => FALSE,
p_message_name => l_message_name,
p_update_rec => TRUE);
ClOSE select_rec;
OPEN select_rec ;
FETCH select_rec INTO old_references;
IF select_rec%FOUND THEN
igs_en_spa_terms_pkg.update_row(x_rowid => old_references.rowid ,
x_term_record_id => old_references.term_record_id,
x_person_id => old_references.person_id,
x_program_cd => old_references.program_cd,
x_program_version => old_references.program_version,
x_acad_cal_type => old_references.acad_cal_type,
x_term_cal_type => old_references.term_cal_type ,
x_term_sequence_number => old_references.term_sequence_number,
x_key_program_flag => old_references.key_program_flag,
x_location_cd => old_references.location_cd,
x_attendance_mode => old_references.attendance_mode,
x_attendance_type => old_references.attendance_type,
x_fee_cat => old_references.fee_cat,
x_coo_id => old_references.coo_id,
x_class_standing_id => old_references.class_standing_id,
x_attribute_category => old_references.attribute_category,
x_attribute1 => old_references.attribute1,
x_attribute2 => old_references.attribute2 ,
x_attribute3 => old_references.attribute3,
x_attribute4 => old_references.attribute4,
x_attribute5 => old_references.attribute5,
x_attribute6 => old_references.attribute6,
x_attribute7 => old_references.attribute7,
x_attribute8 => old_references.attribute8,
x_attribute9 => old_references.attribute9,
x_attribute10 => old_references.attribute10,
x_attribute11 => old_references.attribute11,
x_attribute12 => old_references.attribute12,
x_attribute13 => old_references.attribute13,
x_attribute14 => old_references.attribute14 ,
x_attribute15 => old_references.attribute15,
x_attribute16 => old_references.attribute16,
x_attribute17 => old_references.attribute17,
x_attribute18 => old_references.attribute18,
x_attribute19 => old_references.attribute19,
x_attribute20 => old_references.attribute20,
x_mode => 'R',
x_plan_sht_status => P_PLAN_SHT_FLAG
);
ClOSE select_rec;
END update_spa_terms_plan_sht_flag;
PROCEDURE update_plansheet_unitdetails(
P_PERSON_ID IN NUMBER,
P_COURSE_CD IN VARCHAR2,
P_UOOID IN NUMBER,
P_CARTFLAG IN VARCHAR2,
P_SOURCEFLAG IN VARCHAR2,
P_FIELDNAME IN VARCHAR2,
P_auditVAL IN VARCHAR2,
P_creditVAL IN NUMBER,
P_gradingVAL IN VARCHAR2
) AS
------------------------------------------------------------------
--Created by : ctyagi, Oracle IDC
--Date created: 18-JULY-2005
--
--Purpose: update plannig sheet unitdetails .
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
cursor select_sua is
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_UOOID ;
cursor select_plan is
select plan.rowid,plan.* from igs_en_plan_units plan where
PERSON_ID=P_PERSON_ID AND
COURSE_CD=P_COURSE_CD AND
UOO_ID=P_UOOID AND
CART_ERROR_FLAG=P_CARTFLAG ;
old_refsua select_sua%ROWTYPE;
old_refplan select_plan%ROWTYPE;
PROCEDURE update_plan( p_grd_schm_cd IN VARCHAR2,
p_gs_ver IN NUMBER,
p_audit IN VARCHAR2,
p_credit IN NUMBER,
p_old_refplan select_plan%ROWTYPE) IS
BEGIN
igs_en_plan_units_pkg.update_row(
x_rowid => p_old_refplan.rowid,
x_person_id => p_old_refplan.person_id ,
x_course_cd => p_old_refplan.course_cd,
x_uoo_id => p_old_refplan.uoo_id,
x_term_cal_type => p_old_refplan.term_cal_type,
x_term_ci_sequence_number => p_old_refplan.term_ci_sequence_number,
x_no_assessment_ind => p_audit,
x_sup_uoo_id => p_old_refplan.sup_uoo_id,
x_override_enrolled_cp => p_credit,
x_grading_schema_code => p_grd_schm_cd,
x_gs_version_number => p_gs_ver,
x_core_indicator_code => p_old_refplan.core_indicator_code,
x_alternative_title => p_old_refplan.alternative_title,
x_cart_error_flag => p_old_refplan.cart_error_flag,
x_session_id => p_old_refplan.session_id,
x_mode => 'R'
);
END update_plan;
PROCEDURE update_sua ( p_grd_schm_cd IN VARCHAR2,
p_gs_ver IN NUMBER,
p_audit IN VARCHAR2,
p_credit IN NUMBER,
p_old_refsua select_sua%ROWTYPE) IS
BEGIN
igs_en_su_attempt_pkg.UPDATE_ROW(
X_ROWID => p_old_refsua.rowid,
X_PERSON_ID => p_old_refsua.PERSON_ID,
X_COURSE_CD => p_old_refsua.COURSE_CD,
X_UNIT_CD => p_old_refsua.UNIT_CD,
X_CAL_TYPE => p_old_refsua.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => p_old_refsua.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => p_old_refsua.VERSION_NUMBER,
X_LOCATION_CD => p_old_refsua.LOCATION_CD,
X_UNIT_CLASS => p_old_refsua.UNIT_CLASS,
X_CI_START_DT => p_old_refsua.CI_START_DT,
X_CI_END_DT => p_old_refsua.CI_END_DT,
X_UOO_ID => p_old_refsua.UOO_ID,
X_ENROLLED_DT => p_old_refsua.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => p_old_refsua.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => p_old_refsua.ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT => p_old_refsua.DISCONTINUED_DT,
X_RULE_WAIVED_DT => p_old_refsua.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => p_old_refsua.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => p_audit,
X_SUP_UNIT_CD => p_old_refsua.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => p_old_refsua.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => p_old_refsua.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => p_old_refsua.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => p_credit,
X_OVERRIDE_EFTSU => p_old_refsua.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => p_old_refsua.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => p_old_refsua.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => p_old_refsua.OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY => p_old_refsua.ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT => p_old_refsua.WAITLIST_DT,
x_dcnt_reason_cd => p_old_refsua.dcnt_reason_cd,
X_MODE => 'R',
X_GS_VERSION_NUMBER => p_gs_ver,
X_ENR_METHOD_TYPE => p_old_refsua.ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE => p_old_refsua.FAILED_UNIT_RULE,
X_CART => p_old_refsua.CART,
X_RSV_SEAT_EXT_ID => p_old_refsua.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => p_old_refsua.ORG_UNIT_CD,
X_GRADING_SCHEMA_CODE => p_grd_schm_cd,
x_subtitle => p_old_refsua.subtitle,
x_session_id => p_old_refsua.session_id,
x_deg_aud_detail_id => p_old_refsua.deg_aud_detail_id,
x_student_career_transcript => p_old_refsua.student_career_transcript,
x_student_career_statistics => p_old_refsua.student_career_statistics,
x_waitlist_manual_ind => p_old_refsua.waitlist_manual_ind,
X_ATTRIBUTE_CATEGORY => p_old_refsua.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_old_refsua.ATTRIBUTE1,
X_ATTRIBUTE2 => p_old_refsua.ATTRIBUTE2,
X_ATTRIBUTE3 => p_old_refsua.ATTRIBUTE3,
X_ATTRIBUTE4 => p_old_refsua.ATTRIBUTE4,
X_ATTRIBUTE5 => p_old_refsua.ATTRIBUTE5,
X_ATTRIBUTE6 => p_old_refsua.ATTRIBUTE6,
X_ATTRIBUTE7 => p_old_refsua.ATTRIBUTE7,
X_ATTRIBUTE8 => p_old_refsua.ATTRIBUTE8,
X_ATTRIBUTE9 => p_old_refsua.ATTRIBUTE9,
X_ATTRIBUTE10 => p_old_refsua.ATTRIBUTE10,
X_ATTRIBUTE11 => p_old_refsua.ATTRIBUTE11,
X_ATTRIBUTE12 => p_old_refsua.ATTRIBUTE12,
X_ATTRIBUTE13 => p_old_refsua.ATTRIBUTE13,
X_ATTRIBUTE14 => p_old_refsua.ATTRIBUTE14,
X_ATTRIBUTE15 => p_old_refsua.ATTRIBUTE15,
X_ATTRIBUTE16 => p_old_refsua.ATTRIBUTE16,
X_ATTRIBUTE17 => p_old_refsua.ATTRIBUTE17,
X_ATTRIBUTE18 => p_old_refsua.ATTRIBUTE18,
X_ATTRIBUTE19 => p_old_refsua.ATTRIBUTE19,
x_ATTRIBUTE20 => p_old_refsua.ATTRIBUTE20,
X_WLST_PRIORITY_WEIGHT_NUM => p_old_refsua.WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM => p_old_refsua.WLST_PREFERENCE_WEIGHT_NUM,
X_CORE_INDICATOR_CODE => p_old_refsua.CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG => p_old_refsua.UPD_AUDIT_FLAG,
X_SS_SOURCE_IND => p_old_refsua.SS_SOURCE_IND
);
END update_sua;
OPEN select_sua;
FETCH select_sua INTO old_refsua;
IF select_sua%FOUND THEN
IF P_FIELDNAME = 'GRADING' THEN
l_gradingcode:=substr(P_gradingVAL, 0,instr(P_gradingVAL, ',')-1);
update_sua ( p_grd_schm_cd => l_gradingcode,
p_gs_ver => l_gradingver,
p_audit => l_no_assessment_ind,
p_credit => l_enrolled_cp,
p_old_refsua =>old_refsua);
CLOSE select_sua;
ELSIF P_SOURCEFLAG ='PLAN' THEN -- if the planning sheet record is getting updated.
OPEN select_plan;
FETCH select_plan INTO old_refplan;
IF select_plan%found THEN
IF P_FIELDNAME = 'GRADING' THEN
l_gradingcode:=substr(P_gradingVAL, 0,instr(P_gradingVAL, ',')-1);
update_plan( p_grd_schm_cd => l_gradingcode,
p_gs_ver => l_gradingver,
p_audit => l_no_assessment_ind,
p_credit => l_enrolled_cp,
p_old_refplan =>old_refplan);
CLOSE select_plan;
END update_plansheet_unitdetails;
PROCEDURE delete_plansheet_unit(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uooid IN NUMBER,
p_cartflag IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_message_name OUT NOCOPY VARCHAR2
) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT pl.rowid, pl.term_cal_type, pl.term_ci_sequence_number,pl.uoo_id, pl.core_indicator_code, pl.sup_uoo_id
FROM igs_en_plan_units pl
WHERE pl.person_id = p_person_id
AND pl.course_cd = p_course_cd
AND pl.cart_error_flag = P_CARTFLAG
AND (pl.uoo_id = P_UOOID
OR ( 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 unit_cd || '/' || unit_class UnitSection
FROM igs_ps_unit_ofr_opt_all
WHERE uoo_id = cp_n_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);
END delete_plansheet_unit;
PROCEDURE delete_sua_from_plan(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER,
p_tch_cal IN VARCHAR2,
p_tch_seq IN NUMBER,
p_term_cal IN VARCHAR2,
p_term_seq_num IN NUMBER,
p_core IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_message_name OUT NOCOPY VARCHAR2
)AS
------------------------------------------------------------------
--Created by : ctyagi, Oracle IDC
--Date created: 18-JULY-2005
--
--Purpose: delete sua record from planning sheet.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR c_multiple_load(cp_cal_type IN VARCHAR2,
cp_seq_num IN NUMBER ) IS
SELECT COUNT(*)
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = cp_cal_type
AND teach_ci_sequence_number = cp_seq_num
AND rownum < 3;
p_selected_uoo_ids => p_uoo_id,
p_ret_all_uoo_ids => l_ret_all_uoo_ids,
p_ret_sub_uoo_ids => l_ret_sub_uoo_ids,
p_ret_nonsub_uoo_ids => l_ret_nonsub_uoo_ids,
p_delete_flag => 'Y') ;
END delete_sua_from_plan;
SELECT 'X' FROM igs_en_su_attempt_all sua, igs_ps_unit_ofr_opt_all uoo
WHERE uoo.uoo_id = cp_uoo_id
AND sua.person_id = p_n_person_id
AND sua.course_cd = p_c_program_code
AND sua.core_indicator_code = 'CORE'
AND sua.ss_source_ind = 'S'
AND sua.unit_cd = uoo.unit_cd
AND sua.cal_type = uoo.cal_type
AND sua.ci_sequence_number = uoo.ci_sequence_number
AND sua.uoo_id <> uoo.uoo_id
AND sua.unit_attempt_status = 'UNCONFIRM';
SELECT DISCONTINUATION_REASON_CD
FROM igs_en_dcnt_reasoncd
WHERE S_DISCONTINUATION_REASON_TYPE = 'SWAP'
AND SYS_DFLT_IND = 'Y'
AND ROWNUM < 2 ;
SELECT *
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_n_person_id
AND sua.course_cd = cp_c_course_cd
AND sua.uoo_id = cp_n_uoo_id;
'SELECT U.unit_cd, U.unit_class, U.uoo_id
FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V V
WHERE U.person_id = :1
AND U.course_cd = :2
AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
AND U.uoo_id NOT IN('||p_uoo_ids||')
AND U.cal_type = V.teach_cal_type
AND U.ci_sequence_number = V.teach_ci_sequence_number
AND V.load_cal_type = '''||p_load_cal_type||'''
AND V.load_ci_sequence_number = '||p_load_sequence_number
USING p_person_id,p_program_cd;
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW (
X_ROWID => rec_sua.row_id,
X_PERSON_ID => rec_sua.person_id,
X_COURSE_CD => rec_sua.course_cd,
X_UNIT_CD => rec_sua.unit_cd,
X_CAL_TYPE => rec_sua.cal_type,
X_CI_SEQUENCE_NUMBER => rec_sua.ci_sequence_number,
X_VERSION_NUMBER => rec_sua.version_number,
X_LOCATION_CD => rec_sua.location_cd,
X_UNIT_CLASS => rec_sua.unit_class,
X_CI_START_DT => rec_sua.ci_start_dt,
X_CI_END_DT => rec_sua.ci_end_dt,
X_UOO_ID => rec_sua.uoo_id,
X_ENROLLED_DT => rec_sua.enrolled_dt,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => NULL,
X_DISCONTINUED_DT => SYSDATE,
X_RULE_WAIVED_DT => rec_sua.rule_waived_dt,
X_RULE_WAIVED_PERSON_ID => rec_sua.rule_waived_person_id,
X_NO_ASSESSMENT_IND => rec_sua.no_assessment_ind,
X_SUP_UNIT_CD => rec_sua.sup_unit_cd,
X_SUP_VERSION_NUMBER => rec_sua.sup_version_number,
X_EXAM_LOCATION_CD => rec_sua.exam_location_cd,
X_ALTERNATIVE_TITLE => rec_sua.alternative_title,
X_OVERRIDE_ENROLLED_CP => rec_sua.override_enrolled_cp,
X_OVERRIDE_EFTSU => rec_sua.override_eftsu,
X_OVERRIDE_ACHIEVABLE_CP => rec_sua.override_achievable_cp,
X_OVERRIDE_OUTCOME_DUE_DT => rec_sua.override_outcome_due_dt,
X_OVERRIDE_CREDIT_REASON => rec_sua.override_credit_reason,
X_ADMINISTRATIVE_PRIORITY => rec_sua.administrative_priority,
X_WAITLIST_DT => rec_sua.waitlist_dt,
X_DCNT_REASON_CD => l_c_reason_cd,
X_MODE => 'R',
X_GS_VERSION_NUMBER => rec_sua.gs_version_number,
X_ENR_METHOD_TYPE => rec_sua.enr_method_type,
X_FAILED_UNIT_RULE => rec_sua.failed_unit_rule,
X_CART => rec_sua.cart,
X_RSV_SEAT_EXT_ID => rec_sua.rsv_seat_ext_id,
X_ORG_UNIT_CD => rec_sua.org_unit_cd,
X_GRADING_SCHEMA_CODE => rec_sua.grading_schema_code,
X_subtitle => rec_sua.subtitle,
x_session_id => rec_sua.session_id,
X_deg_aud_detail_id => rec_sua.deg_aud_detail_id,
x_student_career_transcript => rec_sua.student_career_transcript,
x_student_career_statistics => rec_sua.student_career_statistics,
X_WAITLIST_MANUAL_IND => rec_sua.waitlist_manual_ind,
X_ATTRIBUTE_CATEGORY => rec_sua.attribute_category,
X_ATTRIBUTE1 => rec_sua.attribute1,
X_ATTRIBUTE2 => rec_sua.attribute2,
X_ATTRIBUTE3 => rec_sua.attribute3,
X_ATTRIBUTE4 => rec_sua.attribute4,
X_ATTRIBUTE5 => rec_sua.attribute5,
X_ATTRIBUTE6 => rec_sua.attribute6,
X_ATTRIBUTE7 => rec_sua.attribute7,
X_ATTRIBUTE8 => rec_sua.attribute8,
X_ATTRIBUTE9 => rec_sua.attribute9,
X_ATTRIBUTE10 => rec_sua.attribute10,
X_ATTRIBUTE11 => rec_sua.attribute11,
X_ATTRIBUTE12 => rec_sua.attribute12,
X_ATTRIBUTE13 => rec_sua.attribute13,
X_ATTRIBUTE14 => rec_sua.attribute14,
X_ATTRIBUTE15 => rec_sua.attribute15,
X_ATTRIBUTE16 => rec_sua.attribute16,
X_ATTRIBUTE17 => rec_sua.attribute17,
X_ATTRIBUTE18 => rec_sua.attribute18,
X_ATTRIBUTE19 => rec_sua.attribute19,
X_ATTRIBUTE20 => rec_sua.attribute20,
X_WLST_PRIORITY_WEIGHT_NUM => rec_sua.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => rec_sua.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => rec_sua.core_indicator_code
);
'SELECT U.unit_cd, U.unit_class, U.uoo_id
FROM IGS_EN_SU_ATTEMPT U, IGS_CA_LOAD_TO_TEACH_V V
WHERE U.person_id = :1
AND U.course_cd = :2
AND U.unit_attempt_status IN (''ENROLLED'',''INVALID'')
AND U.uoo_id NOT IN('||p_uoo_ids||')
AND U.cal_type = V.teach_cal_type
AND U.ci_sequence_number = V.teach_ci_sequence_number
AND V.load_cal_type = '''||p_load_cal_type||'''
AND V.load_ci_sequence_number = '||p_load_sequence_number
USING p_person_id,p_program_cd;
PROCEDURE swap_delete ( p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_course_version IN NUMBER,
p_usec_dtls IN VARCHAR2,
p_uoo_id IN NUMBER,
p_term_cal IN VARCHAR2,
p_term_seq_num IN NUMBER,
p_core IN VARCHAR2,
p_rel_type IN VARCHAR2,
p_ret_status OUT NOCOPY VARCHAR2,
p_msg OUT NOCOPY VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT sua.unit_cd || '/' || sua.unit_class AS UNIT_SECTION,
sua.uoo_id,
sua.core_indicator_code AS CORE
FROM igs_en_su_attempt_all sua,
igs_ps_unit_ofr_opt_all uoo
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.uoo_id = uoo.uoo_id
AND uoo.sup_uoo_id = cp_uoo_id;
PROCEDURE sua_delete (p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER) IS
-- Cursor to select the row id of SUA.
CURSOR c_del (cp_person_id IN NUMBER,
cp_course_cd IN VARCHAR2,
cp_uoo_id IN NUMBER) IS
SELECT sua.row_id AS ROW_ID
FROM igs_en_su_attempt sua
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.uoo_id = cp_uoo_id;
igs_en_su_attempt_pkg.delete_row ( X_ROWID => rec_del.row_id,
X_MODE => 'R');
END sua_delete;
sua_delete (p_person_id => p_person_id,
p_course_cd => p_course_cd,
p_uoo_id => p_uoo_id);
sua_delete (p_person_id => p_person_id,
p_course_cd => p_course_cd,
p_uoo_id => rec_sub_sua.uoo_id);
END swap_delete;
SELECT *
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = cp_person_id
AND course_cd = cp_program_cd
AND uoo_Id = cp_uoo_Id;
SELECT rsv.ROWID row_id, rsv.*
FROM igs_ps_rsv_ext rsv
WHERE rsv_ext_id = cp_rsv_ext_id FOR UPDATE;
igs_ps_rsv_ext_pkg.update_row( x_rowid => rec_igs_ps_rsv_ext.row_id,
x_rsv_ext_id => rec_igs_ps_rsv_ext.rsv_ext_id,
x_uoo_id => rec_igs_ps_rsv_ext.uoo_id,
x_priority_id => rec_igs_ps_rsv_ext.priority_id,
x_preference_id => rec_igs_ps_rsv_ext.preference_id,
x_rsv_level => rec_igs_ps_rsv_ext.rsv_level,
x_actual_seat_enrolled => rec_igs_ps_rsv_ext.actual_seat_enrolled -1,
x_mode => 'R'
);
igs_en_sua_api.upd_enrollment_counts( 'DELETE',
old_rec,
new_rec);
SELECT sua.ROWID ROW_ID,
sua.uoo_id
FROM igs_en_su_attempt_all sua,
igs_ca_teach_to_load_v rel
WHERE sua.person_id = cp_n_person_id
AND sua.course_cd = cp_c_program_code
AND sua.cal_type = rel.teach_cal_type
AND sua.ci_sequence_number = rel.teach_ci_sequence_number
AND rel.load_cal_type = cp_c_load_cal
AND rel.load_ci_sequence_number = cp_n_seq_num
AND sua.unit_attempt_status = 'UNCONFIRM'
AND sua.SS_SOURCE_IND = 'S';
SELECT warn.ROWID ROW_ID
FROM igs_en_std_warnings warn
WHERE warn.person_id = cp_n_person_id
AND warn.course_cd = cp_c_program_code
AND warn.term_cal_type = cp_c_load_cal
AND warn.term_ci_sequence_number = cp_n_seq_num;
IGS_EN_STD_WARNINGS_pkg.delete_row(x_rowid =>rec_warn.row_id);
igs_en_su_attempt_pkg.delete_row(X_ROWID => rec_sua.row_id,
x_mode => 'R');
Purpose : This func determines if credit points can be updated
Known limitations,
enhancements,
remarks :
Change History
Who When What
******************************************************************/
l_dummy VARCHAR2(100);
SELECT unit_cd, version_number,cal_type,ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
IS SELECT points_override_ind
FROM igs_ps_unit_ver_v
WHERE unit_cd = p_unit_cd
AND version_number = p_unit_ver_num;
SELECT dai.alias_val alias_val
FROM igs_ca_da_inst_v dai,igs_pe_usr_arg_all pua
WHERE pua.person_type = cp_person_type
AND dai.dt_alias = pua.grad_sch_dt_alias
AND dai.cal_type = cp_cal_type
AND dai.ci_sequence_number = cp_ci_sequence_number
ORDER BY 1;
SELECT enr_dl_date alias_val
FROM igs_en_nstd_usec_dl
WHERE function_name = 'GRADING_SCHEMA'
AND uoo_id = p_uoo_id
ORDER BY 1;
SELECT dai.alias_val alias_val
FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
WHERE dai.cal_type = cp_cal_type
AND dai.ci_sequence_number = cp_ci_sequence_number
AND dai.dt_alias = ecc.grading_schema_dt_alias
AND ecc.s_control_num =1
ORDER BY 1;
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = cp_person_type;
SELECT no_assessment_ind
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id
AND cart_error_flag='N';
IF fnd_profile.value('IGS_EN_UPDATE_CP_GS')='Y' THEN
RETURN 'N';
PROCEDURE swap_update(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uooid IN NUMBER,
p_fieldname IN VARCHAR2,
p_auditval IN VARCHAR2,
p_creditval IN NUMBER,
p_gradingval IN VARCHAR2,
X_ROWID 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_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_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,
X_UPD_AUDIT_FLAG IN VARCHAR2,
X_SS_SOURCE_IND IN VARCHAR2)
------------------------------------------------------------------
--Created by : vijrajag
--Date created: 04-July-2005
--
--Purpose: autonomous wrapper over update_plansheet_utnidetails
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
IS
PRAGMA AUTONOMOUS_TRANSACTION;
update_plansheet_unitdetails(
p_person_id => p_person_id,
p_course_cd => p_course_cd,
p_uooid => p_uooid,
p_cartflag => NULL,
p_sourceflag => 'SUA',
p_fieldname => p_fieldname,
p_auditval => p_auditval,
p_creditval => p_creditval,
p_gradingval => p_gradingval);
END swap_update;
PROCEDURE plan_update(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_uoo_id IN NUMBER,
p_fieldname IN VARCHAR2,
p_auditval IN VARCHAR2,
p_creditval IN NUMBER,
p_gradingval IN VARCHAR2,
p_row_id IN VARCHAR2,
p_term_cal_type IN VARCHAR2,
p_term_ci_sequence_number IN NUMBER,
p_no_assessment_ind IN VARCHAR2,
p_sup_uoo_id IN NUMBER,
p_override_enrolled_cp IN NUMBER,
p_grading_schema_code IN VARCHAR2,
p_gs_version_number IN NUMBER,
p_core_indicator_code IN VARCHAR2,
p_alternative_title IN VARCHAR2,
p_cart_error_flag IN VARCHAR2,
p_session_id IN NUMBER
)
------------------------------------------------------------------
--Created byp : Chanchal
--Date creatped: 30-Jul-2005
--
--Purpose: autonomous wrapper over update_plansheet_utnidetails to
-- update planning sheet record.
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
IS
PRAGMA AUTONOMOUS_TRANSACTION;
update_plansheet_unitdetails(
p_person_id => p_person_id,
p_course_cd => p_course_cd,
p_uooid => p_uoo_id,
p_cartflag => p_cart_error_flag,
p_sourceflag => 'PLAN',
p_fieldname => p_fieldname,
p_auditval => p_auditval,
p_creditval => p_creditval,
p_gradingval => p_gradingval);
END plan_update;
SELECT *
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = cp_person_id
AND course_cd = cp_program_cd
AND uoo_Id = cp_uoo_Id;
SELECT rsv.ROWID row_id, rsv.*
FROM igs_ps_rsv_ext rsv
WHERE rsv_ext_id = cp_rsv_ext_id FOR UPDATE;
SELECT *
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_Id = p_uoo_Id;
igs_ps_rsv_ext_pkg.update_row( x_rowid => l_cur_igs_ps_rsv_ext.row_id,
x_rsv_ext_id => l_cur_igs_ps_rsv_ext.rsv_ext_id,
x_uoo_id => l_cur_igs_ps_rsv_ext.uoo_id,
x_priority_id => l_cur_igs_ps_rsv_ext.priority_id,
x_preference_id => l_cur_igs_ps_rsv_ext.preference_id,
x_rsv_level => l_cur_igs_ps_rsv_ext.rsv_level,
x_actual_seat_enrolled => l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1,
x_mode => 'R'
);
igs_en_sua_api.upd_enrollment_counts( 'UPDATE',
old_rec,
new_rec);
SELECT susv.mark
FROM igs_as_su_stmptout susv
WHERE
susv.PERSON_ID = p_person_id AND
susv.course_cd =p_course_cd AND
susv.uoo_id = p_uoo_id AND
susv.finalised_outcome_ind = 'Y' and
susv.grading_period_cd <> 'MIDTERM'
ORDER BY susv.outcome_dt DESC ;
SELECT susv.grade
FROM igs_as_su_stmptout susv
WHERE
susv.PERSON_ID = p_person_id AND
susv.course_cd =p_course_cd AND
susv.uoo_id = p_uoo_id AND
susv.finalised_outcome_ind = 'Y' and
susv.grading_period_cd <> 'MIDTERM'
ORDER BY susv.outcome_dt DESC ;