[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid,cst.*
FROM IGS_PE_STD_TODO cst
WHERE s_student_todo_type = 'UNIT-RULES' AND
logical_delete_dt IS NULL AND
NVL(todo_dt,TO_DATE('01/01/1980','DD/MM/YYYY')) <= SYSDATE
ORDER BY person_id asc
FOR UPDATE NOWAIT;
SELECT course_cd
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = cp_person_id AND
(course_cd = cp_course_cd OR
course_cd like cp_course_cd) AND
(cal_type = p_acad_cal_type OR
cal_type like p_acad_cal_type) AND
course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT');
/* For the coloumn to be updated,modify the record variable value fetched */
v_st_rec.logical_delete_dt := SYSDATE;
IGS_PE_STD_TODO_pkg.update_row(
X_ROWID => v_st_rec.ROWID,
X_PERSON_ID => v_st_rec.PERSON_ID,
X_S_STUDENT_TODO_TYPE => v_st_rec.S_STUDENT_TODO_TYPE,
X_SEQUENCE_NUMBER => v_st_rec.SEQUENCE_NUMBER,
X_TODO_DT => v_st_rec.TODO_DT,
X_LOGICAL_DELETE_DT => v_st_rec.LOGICAL_DELETE_DT,
X_MODE => 'R');
SELECT secc.enrolled_rule_cutoff_dt_alias,
secc.invalid_rule_cutoff_dt_alias
FROM IGS_EN_CAL_CONF secc
WHERE secc.s_control_num = cst_s_control_num;
SELECT
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.version_number,
sua.location_cd,
sua.unit_class,
ci.alternate_code,
uoo.uoo_id,
sua.sup_unit_cd
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST ci,
IGS_PS_UNIT_OFR_OPT uoo
WHERE
sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'ENROLLED'
AND sua.rule_waived_dt IS NULL
AND ci.cal_type = sua.cal_type
AND ci.sequence_number = sua.ci_sequence_number
AND uoo.uoo_id = sua.uoo_id
AND uoo.owner_org_unit_cd LIKE NVL(p_org_unit_cd,'%')
AND (
( cp_cal_cat = 'TEACHING'
AND sua.cal_type LIKE NVL(p_cal_type,'%')
AND sua.ci_sequence_number = nvl(p_ci_sequence_number,sua.ci_sequence_number)
) OR
( cp_cal_cat = 'LOAD' AND
(sua.cal_type,sua.ci_sequence_number) IN
( SELECT teach_cal_type,teach_ci_sequence_number
FROM IGS_CA_LOAD_TO_TEACH_V
WHERE load_cal_type LIKE NVL(p_cal_type,'%')
AND load_ci_sequence_number = nvl(p_ci_sequence_number,load_ci_sequence_number)
)
)
)
ORDER BY sup_unit_cd;
SELECT MAX(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.cal_type = cp_sua_cal_type AND
daiv.ci_sequence_number = cp_sua_sequence_number AND
daiv.dt_alias = p_invalid_rule_cutoff_dt;
SELECT sca.version_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT sua.*
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST ci
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status = cst_enrolled AND
sua.rule_waived_dt IS NULL AND
ci.cal_type = sua.cal_type AND
ci.sequence_number = sua.ci_sequence_number AND
sua.unit_cd = cp_unit_cd AND
--added for enrollment processes dld by smaddali
sua.uoo_id = cp_uoo_id;
SELECT s_cal_cat
FROM IGS_CA_TYPE
WHERE cal_type = p_cal_type ;
/* For the coloumn to be updated,modify the record variable value fetched */
sua_rec.unit_attempt_status := 'INVALID' ;
igs_en_sua_api.update_unit_attempt(
X_ROWID => sua_rec.ROW_ID,
X_PERSON_ID => sua_rec.PERSON_ID,
X_COURSE_CD => sua_rec.COURSE_CD,
X_UNIT_CD => sua_rec.UNIT_CD,
X_CAL_TYPE => sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
X_LOCATION_CD => sua_rec.LOCATION_CD,
X_UNIT_CLASS => sua_rec.UNIT_CLASS,
X_CI_START_DT => sua_rec.CI_START_DT,
X_CI_END_DT => sua_rec.CI_END_DT,
X_UOO_ID => sua_rec.UOO_ID,
X_ENROLLED_DT => sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => sua_rec.WAITLIST_DT,
X_MODE => 'R' ,
-- added for enrollment processes dld nov2001 by smaddali
X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => l_failed_rule,
X_CART => sua_rec.CART,
X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => sua_rec.session_id,
-- Added the column grading schema as a part pf the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
-- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
-- by pradhakr
X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
X_SUBTITLE => sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
X_ATTRIBUTE1 => sua_rec.attribute1,
X_ATTRIBUTE2 => sua_rec.attribute2,
X_ATTRIBUTE3 => sua_rec.attribute3,
X_ATTRIBUTE4 => sua_rec.attribute4,
X_ATTRIBUTE5 => sua_rec.attribute5,
X_ATTRIBUTE6 => sua_rec.attribute6,
X_ATTRIBUTE7 => sua_rec.attribute7,
X_ATTRIBUTE8 => sua_rec.attribute8,
X_ATTRIBUTE9 => sua_rec.attribute9,
X_ATTRIBUTE10 => sua_rec.attribute10,
X_ATTRIBUTE11 => sua_rec.attribute11,
X_ATTRIBUTE12 => sua_rec.attribute12,
X_ATTRIBUTE13 => sua_rec.attribute13,
X_ATTRIBUTE14 => sua_rec.attribute14,
X_ATTRIBUTE15 => sua_rec.attribute15,
X_ATTRIBUTE16 => sua_rec.attribute16,
X_ATTRIBUTE17 => sua_rec.attribute17,
X_ATTRIBUTE18 => sua_rec.attribute18,
X_ATTRIBUTE19 => sua_rec.attribute19,
X_ATTRIBUTE20 => sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
);
SELECT sua.unit_cd,
sua.version_number sua_version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_class,
sua.location_cd,
sca.version_number sca_version_number,
sca.coo_id,
ci.alternate_code ,
uoo.uoo_id,
sua.sup_unit_cd
FROM IGS_EN_SU_ATTEMPT sua,
IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci ,
IGS_PS_UNIT_OFR_OPT uoo
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'INVALID'
AND sca.person_id = sua.person_id
AND sca.course_cd = sua.course_cd
AND ci.sequence_number = sua.ci_sequence_number
AND ci.cal_type = sua.cal_type
AND uoo.uoo_id = sua.uoo_id
AND uoo.owner_org_unit_cd LIKE NVL(p_org_unit_cd,'%')
AND sua.cal_type LIKE NVL(cp_cal_type ,'%')
AND sua.ci_sequence_number = nvl( cp_ci_sequence_number ,sua.ci_sequence_number)
ORDER BY sup_unit_cd DESC;
SELECT sua.*
FROM IGS_EN_SU_ATTEMPT sua,
IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status = 'INVALID' AND
sca.person_id = sua.person_id AND
sca.course_cd = sua.course_cd AND
ci.cal_type = sua.cal_type AND
ci.sequence_number = sua.ci_sequence_number AND
sua.unit_cd = cp_unit_cd AND
--added for enrollment processes dld by smaddali
sua.uoo_id = cp_uoo_id;
SELECT MAX(daiv.alias_val)
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.cal_type = cp_sua_cal_type AND
daiv.ci_sequence_number = cp_sua_sequence_number AND
daiv.dt_alias = p_enrolled_rule_cutoff_dt;
SELECT s_cal_cat
FROM IGS_CA_TYPE
WHERE cal_type = p_cal_type ;
SELECT teach_cal_type,teach_ci_sequence_number
FROM IGS_CA_LOAD_TO_TEACH_V
WHERE load_cal_type = p_cal_type
AND load_ci_sequence_number = nvl( p_ci_sequence_number ,load_ci_sequence_number);
/* For the column to be updated,modify the record variable value fetched */
sua_rec.unit_attempt_status := 'ENROLLED' ;
igs_en_sua_api.update_unit_attempt(
X_ROWID => sua_rec.ROW_ID,
X_PERSON_ID => sua_rec.PERSON_ID,
X_COURSE_CD => sua_rec.COURSE_CD,
X_UNIT_CD => sua_rec.UNIT_CD,
X_CAL_TYPE => sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
X_LOCATION_CD => sua_rec.LOCATION_CD,
X_UNIT_CLASS => sua_rec.UNIT_CLASS,
X_CI_START_DT => sua_rec.CI_START_DT,
X_CI_END_DT => sua_rec.CI_END_DT,
X_UOO_ID => sua_rec.UOO_ID,
X_ENROLLED_DT => sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => sua_rec.waitlist_dt,
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => l_failed_rule ,
X_CART => sua_rec.CART,
X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => sua_rec.session_id,
-- Added the column grading schema as a part pf the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
-- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
-- by pradhakr
X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
X_SUBTITLE => sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
X_ATTRIBUTE1 => sua_rec.attribute1,
X_ATTRIBUTE2 => sua_rec.attribute2,
X_ATTRIBUTE3 => sua_rec.attribute3,
X_ATTRIBUTE4 => sua_rec.attribute4,
X_ATTRIBUTE5 => sua_rec.attribute5,
X_ATTRIBUTE6 => sua_rec.attribute6,
X_ATTRIBUTE7 => sua_rec.attribute7,
X_ATTRIBUTE8 => sua_rec.attribute8,
X_ATTRIBUTE9 => sua_rec.attribute9,
X_ATTRIBUTE10 => sua_rec.attribute10,
X_ATTRIBUTE11 => sua_rec.attribute11,
X_ATTRIBUTE12 => sua_rec.attribute12,
X_ATTRIBUTE13 => sua_rec.attribute13,
X_ATTRIBUTE14 => sua_rec.attribute14,
X_ATTRIBUTE15 => sua_rec.attribute15,
X_ATTRIBUTE16 => sua_rec.attribute16,
X_ATTRIBUTE17 => sua_rec.attribute17,
X_ATTRIBUTE18 => sua_rec.attribute18,
X_ATTRIBUTE19 => sua_rec.attribute19,
X_ATTRIBUTE20 => sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
);
/* For the coloumn to be updated,modify the record variable value fetched */
sua_rec.unit_attempt_status := 'INVALID';
igs_en_sua_api.update_unit_attempt(
X_ROWID => sua_rec.ROW_ID,
X_PERSON_ID => sua_rec.PERSON_ID,
X_COURSE_CD => sua_rec.COURSE_CD,
X_UNIT_CD => sua_rec.UNIT_CD,
X_CAL_TYPE => sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
X_LOCATION_CD => sua_rec.LOCATION_CD,
X_UNIT_CLASS => sua_rec.UNIT_CLASS,
X_CI_START_DT => sua_rec.CI_START_DT,
X_CI_END_DT => sua_rec.CI_END_DT,
X_UOO_ID => sua_rec.UOO_ID,
X_ENROLLED_DT => sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => sua_rec.waitlist_dt,
X_MODE => 'R' ,
--added the new fields for enrollment processes dld
X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => sua_rec.failed_unit_rule,
X_CART => sua_rec.CART,
X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => sua_rec.session_id,
-- Added the column grading schema as a part pf the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
-- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
-- by pradhakr
X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
X_SUBTITLE => sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
X_ATTRIBUTE1 => sua_rec.attribute1,
X_ATTRIBUTE2 => sua_rec.attribute2,
X_ATTRIBUTE3 => sua_rec.attribute3,
X_ATTRIBUTE4 => sua_rec.attribute4,
X_ATTRIBUTE5 => sua_rec.attribute5,
X_ATTRIBUTE6 => sua_rec.attribute6,
X_ATTRIBUTE7 => sua_rec.attribute7,
X_ATTRIBUTE8 => sua_rec.attribute8,
X_ATTRIBUTE9 => sua_rec.attribute9,
X_ATTRIBUTE10 => sua_rec.attribute10,
X_ATTRIBUTE11 => sua_rec.attribute11,
X_ATTRIBUTE12 => sua_rec.attribute12,
X_ATTRIBUTE13 => sua_rec.attribute13,
X_ATTRIBUTE14 => sua_rec.attribute14,
X_ATTRIBUTE15 => sua_rec.attribute15,
X_ATTRIBUTE16 => sua_rec.attribute16,
X_ATTRIBUTE17 => sua_rec.attribute17,
X_ATTRIBUTE18 => sua_rec.attribute18,
X_ATTRIBUTE19 => sua_rec.attribute19,
X_ATTRIBUTE20 => sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
) ;
Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');