[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_reserved_seat( p_action IN VARCHAR2);
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER,
x_org_id IN NUMBER,
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 ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
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,--Bug ID: 2554109 added by adhawan
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,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
x_wlst_priority_weight_num IN NUMBER,
x_wlst_preference_weight_num IN NUMBER,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
x_core_indicator_code IN VARCHAR2,
X_UPD_AUDIT_FLAG IN VARCHAR2 DEFAULT 'N',
X_SS_SOURCE_IND IN VARCHAR2 DEFAULT 'N'
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE ROWID = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IF p_action = 'INSERT' THEN
-- If the user is override the Value then don't populate the default value
new_references.override_enrolled_cp := NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(x_person_id,x_uoo_id), x_override_enrolled_cp);
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date ;
new_references.last_updated_by := x_last_updated_by ;
new_references.last_update_login := x_last_update_login;
SELECT 'x'
FROM IGS_AV_STND_UNIT asu
WHERE asu.person_id =new_references.person_id AND
asu.as_course_cd =new_references.course_cd AND
asu.unit_cd =new_references.unit_cd AND
asu.version_number =new_references.version_number AND
asu.s_adv_stnd_granting_status IN ('APPROVED','GRANTED') AND
asu.s_adv_stnd_recognition_type = 'PRECLUSION';
SELECT NVL(enrollment_maximum,0)
FROM igs_ps_usec_lim_wlst
WHERE uoo_id = p_uoo_id;
SELECT NVL(enrollment_maximum,999999)
FROM igs_ps_unit_ver
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
SELECT ENROLLMENT_ACTUAL
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
PROCEDURE BeforeRowInsertUpdateDelete1(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
/* WHO WHEN WHAT
pradhakr 20-Jan-2003 Added a parameter no_assessment_ind to the procedue call IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp
as part of ENCR26 build.
prraj 22-Oct-2002 Added condition to check new_references.no_assessment_ind <> 'Y'
| in the if clause of the call to IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp for Audit Build(Bug# )
pmarada 02-sep-2002 bug 2526021, Assigning the waitlist positions to the waitlisted students, and clearing
the ADMINISTRATIVE_PRIORITY value to null when unit attempt status is changed from waitlist to any other status
ayedubat 02-JUL-2002 Removed the Variation Window Validation added in the previous version and moved to IGSEN04B.pls
for the same bug fix:2423605
ayedubat 26-JUN-2002 Added a new validation to check the Variation Window Cutoff Date for the bug Fix:2423605
ayedubat 30-MAY-2002 Added a new parameter,p_message_name to the Function:Enrp_Get_Rec_Window
call and dsiplaying the returning message for the bug fix:2337161.
ayedubat 19-APR-2002 Changed the message names in the app_exception call from 'IGS_GE_RECORD_ALREADY_EXISTS'
to 'IGS_EN_SUA_NOTENR_RECENR_WIN' while validating the record cutoff date and in the remaining
places to the message returned by the calling function as part of the bug fix:2332137
Sudhir 23-MAY-2002 To show token for the new message in calling the procedure IGS_EN_VAL_SUA.enrp_val_discont_aus
svanukur 29-APR-03 Passing uoo_id to IGS_EN_GEN_010.ENRP_INS_SUAO_DISCON as part of MUS build # 2829262
myoganat 21-MAY-03 Set override enrolled and override achievable CP to zero for a unit section attempt that is being audited and
when the record is an audit attempt record the procedure IGS_EN_VAL_SUA.ENRP_VAL_SUA_OVRD_CP will not get called.
- as part of the build ENCR032 - Audit Attempt Credit Points. Bug #2855870
kkillams 25-07-2003 New cursor c_unit_opt added to lock the unit oferring option table for a uoo_id, to avoid duplicate
administrative priorities w.r.t. 2865921.
ptandon 03-SEP-2003 Modified the logic as per Waitlist Enhancements Build - Bug# 3052426
ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
*/
--Getting the max administrative priority value, bug 2526021
CURSOR c_admin_priority (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
SELECT max(ADMINISTRATIVE_PRIORITY) FROM igs_en_su_attempt
WHERE uoo_id = cp_uoo_id;
SELECT 1 FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id FOR UPDATE;
SELECT load_cal_type, load_ci_sequence_number FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = cp_teach_cal_type
AND teach_ci_sequence_number = cp_teach_ci_sequence_num
ORDER BY load_start_dt ASC;
SELECT sup.unit_cd, sup.version_number
FROM igs_ps_unit_ofr_opt sub, igs_ps_unit_ofr_opt sup
WHERE sub.uoo_id = new_references.uoo_id
AND sub.relation_type = 'SUBORDINATE'
AND sub.sup_uoo_id = sup.uoo_id;
SELECT 1
FROM igs_ps_unit_ver
WHERE unit_cd = new_references.unit_cd
AND version_number = new_references.version_number
AND practical_ind = 'Y';
IF p_inserting THEN
chk_precluded_unit;
IF p_inserting THEN
-- Validate that the IGS_PS_UNIT offering option is being offered.
IF IGS_EN_VAL_SUA.enrp_val_sua_uoo(
new_references.unit_cd,
new_references.version_number,
new_references.cal_type,
new_references.ci_sequence_number,
new_references.location_cd,
new_references.unit_class,
v_message_name,
'N' ) = FALSE THEN
IF (new_references.unit_attempt_status IN (cst_duplicate, cst_discontin, cst_completed)) THEN
-- Bypass IGS_PS_UNIT version must be active and offered error for duplicates.
NULL;
IF p_inserting OR p_updating THEN
IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
IF p_inserting THEN
-- Validate that discontinued date is not entered when p_inserting
-- This is temporary code
IF new_references.discontinued_dt IS NOT NULL AND
(new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin)) THEN
FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_OPER');
IF IGS_EN_VAL_SUA.enrp_val_sua_update(
new_references.person_id,
new_references.course_cd,
new_references.unit_cd,
new_references.cal_type,
new_references.ci_sequence_number,
new_references.unit_attempt_status,
new_references.version_number,
new_references.location_cd,
new_references.unit_class,
TRUNC(new_references.enrolled_dt),
TRUNC(new_references.discontinued_dt),
new_references.administrative_unit_status,
TRUNC(new_references.rule_waived_dt),
new_references.rule_waived_person_id,
new_references.no_assessment_ind,
new_references.sup_unit_cd,
new_references.sup_version_number,
new_references.exam_location_cd,
old_references.version_number,
old_references.location_cd,
old_references.unit_class,
TRUNC(old_references.enrolled_dt),
TRUNC(old_references.discontinued_dt),
old_references.administrative_unit_status,
TRUNC(old_references.rule_waived_dt),
old_references.rule_waived_person_id,
old_references.no_assessment_ind,
old_references.sup_unit_cd,
old_references.sup_version_number,
old_references.exam_location_cd,
v_message_name,
new_references.uoo_id) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF p_inserting THEN
IF new_references.enrolled_dt IS NULL THEN
v_effective_dt := SYSDATE;
IF p_inserting OR
(p_updating AND old_references.discontinued_dt IS NOT NULL AND
new_references.discontinued_dt IS NULL) THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_intrmt(
new_references.person_id,
new_references.course_cd,
new_references.cal_type,
new_references.ci_sequence_number,
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF(p_inserting OR
(p_updating AND
((old_references.discontinued_dt IS NOT NULL AND
new_references.discontinued_dt IS NULL) OR
(old_references.enrolled_dt IS NULL AND
new_references.enrolled_dt IS NOT NULL) OR
(old_references.rule_waived_dt IS NULL AND
old_references.unit_attempt_status = 'INVALID' AND
new_references.rule_waived_dt IS NOT NULL)))) AND
(new_references.enrolled_dt IS NOT NULL AND new_references.discontinued_dt IS NULL) THEN
IF IGS_EN_VAL_SUA.resp_val_sua_cnfrm(
new_references.person_id,
new_references.course_cd,
new_references.unit_cd,
new_references.version_number,
new_references.cal_type,
new_references.ci_sequence_number,
v_message_name ,
'N' ) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF p_inserting OR
(p_updating AND
(new_references.enrolled_dt IS NOT NULL AND
old_references.enrolled_dt IS NULL) OR
(new_references.discontinued_dt IS NULL AND
old_references.discontinued_dt IS NOT NULL) OR
(new_references.rule_waived_dt IS NOT NULL AND
old_references.rule_waived_dt IS NULL AND
old_references.unit_attempt_status = 'INVALID')) THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_advstnd(
new_references.person_id,
new_references.course_cd,
'',
new_references.unit_cd,
new_references.version_number,
v_message_name,'N' ) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF p_inserting OR p_updating THEN
IF (new_references.UNIT_ATTEMPT_STATUS = 'WAITLISTED'
AND NVL(old_references.unit_attempt_status,'UNCONFIRM') <> new_references.unit_attempt_status
AND new_references.waitlist_dt IS NOT NULL
AND new_references.administrative_priority IS NULL
AND new_references.wlst_priority_weight_num IS NULL) THEN
Igs_En_Wlst_Gen_Proc.enrp_wlst_pri_pref_calc(new_references.person_id,
new_references.course_cd,
new_references.uoo_id,
l_pri_weight,
l_pref_weight);
IF p_inserting THEN
-- Validate against IGS_PS_COURSE attempt status
IF IGS_EN_VAL_SUA.enrp_val_sua_insert(
new_references.person_id,
new_references.course_cd,
new_references.unit_attempt_status,
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF p_inserting THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_ci(
new_references.person_id,
new_references.course_cd,
new_references.cal_type,
new_references.ci_sequence_number,
new_references.unit_attempt_status,
NULL,
'T', -- Validation called from trigger
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF (p_inserting OR
(p_updating AND
new_references.unit_attempt_status <> old_references.unit_attempt_status)) THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_dupl(
new_references.person_id,
new_references.course_cd,
new_references.unit_cd,
new_references.version_number,
new_references.cal_type,
new_references.ci_sequence_number,
new_references.unit_attempt_status,
--The column duplicate_course_cd has been changed to course_cd
p_duplicate_course_cd,
v_message_name,
new_references.uoo_id) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF (p_inserting AND
new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin,cst_completed)) OR
(p_updating AND
((new_references.alternative_title IS NULL AND
old_references.alternative_title IS NOT NULL) OR
(new_references.alternative_title IS NOT NULL AND
old_references.alternative_title IS NULL))) THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_alt_ttl(
new_references.unit_cd,
new_references.version_number,
new_references.alternative_title,
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF (p_inserting AND
new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin,cst_completed) AND
new_references.override_enrolled_cp IS NULL) OR
(p_updating AND
(new_references.override_enrolled_cp IS NULL AND
old_references.override_enrolled_cp IS NOT NULL) OR
(new_references.override_enrolled_cp IS NOT NULL AND
(old_references.override_enrolled_cp IS NULL OR
old_references.override_enrolled_cp <> new_references.override_enrolled_cp)) OR
(((new_references.override_achievable_cp IS NULL AND
old_references.override_achievable_cp IS NOT NULL) OR
(new_references.override_achievable_cp IS NOT NULL AND
(old_references.override_achievable_cp IS NULL OR
old_references.override_achievable_cp <> new_references.override_achievable_cp)))) OR
(new_references.override_eftsu IS NULL AND
old_references.override_eftsu IS NOT NULL) OR
(new_references.override_eftsu IS NOT NULL AND
(old_references.override_eftsu IS NULL OR
old_references.override_eftsu <> new_references.override_eftsu)))
THEN
IF IGS_EN_GEN_015.enrp_get_appr_cr_pt(new_references.person_id,new_references.uoo_id) IS NULL AND
IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp(
new_references.unit_cd,
new_references.version_number,
new_references.override_enrolled_cp,
new_references.override_achievable_cp,
new_references.override_eftsu,
v_message_name,
new_references.uoo_id, --New parameter uoo_id is added w.r.t. 2375757 by kkillams
new_references.no_assessment_ind) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
IF p_inserting OR p_updating THEN
OPEN cur_sub_uoo ;
IF p_inserting OR
(p_updating AND
((old_references.enrolled_dt IS NULL AND
new_references.enrolled_dt IS NOT NULL) OR
(old_references.enrolled_dt IS NOT NULL AND
new_references.enrolled_dt IS NULL) OR
(old_references.discontinued_dt IS NOT NULL AND
new_references.discontinued_dt IS NULL) OR
(old_references.discontinued_dt IS NULL AND
new_references.discontinued_dt IS NOT NULL) OR
(new_references.rule_waived_dt IS NOT NULL AND
old_references.rule_waived_dt IS NULL) OR
(old_references.rule_waived_dt IS NOT NULL AND
new_references.rule_waived_dt IS NULL))) THEN
v_return_val := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(new_references.person_id,
'UNIT-RULES',
NULL,
'Y');
IF (p_inserting AND
new_references.unit_attempt_status = 'ENROLLED') OR
(p_updating AND
((old_references.unit_attempt_status <>
new_references.unit_attempt_status) OR
(old_references.location_cd <>
new_references.location_cd) OR
(old_references.unit_class<>
new_references.unit_class))) THEN
IF p_inserting THEN
v_old_unit_attempt_status := NULL;
END IF; -- IF p_inserting OR p_updating THEN
IF IGS_EN_VAL_SUA.enrp_val_sua_delete(
old_references.person_id,
old_references.course_cd,
old_references.unit_cd,
'T', -- indicates trigger validation
old_references.unit_attempt_status,
old_references.cal_type,
old_references.ci_sequence_number,
old_references.discontinued_dt,
SYSDATE,
v_message_name,
old_references.uoo_id) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
END BeforeRowInsertUpdateDelete1;
PROCEDURE BeforeRowInsertUpdateDelete2(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
v_sequence_number NUMBER;
SELECT count(*)
FROM IGS_PS_USEC_SP_FEES
WHERE uoo_id = NVL(old_references.uoo_id, new_references.uoo_id)
AND closed_flag = 'N';
SELECT load_ci_sequence_number, load_cal_type
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = NVL(old_references.cal_type, new_references.cal_type)
AND teach_ci_sequence_number = NVL(old_references.ci_sequence_number,new_references.ci_sequence_number)
ORDER BY load_start_dt ASC;
Select sref.rowid,sref.*
From IGS_AS_SUA_REF_CDS sref
Where sref.person_id = old_references.person_id
And sref.course_cd = old_references.course_cd
And sref.uoo_id = old_references.uoo_id
And sref.deleted_date is null;
IF p_inserting THEN
l_apply_spl_fee := TRUE;
IF p_inserting OR p_updating THEN
v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
new_references.person_id,
'FEE_RECALC',
SYSDATE,
'Y');
igs_as_sua_ref_cds_pkg.update_row (
x_rowid => v_cur_sua_ref_cds.rowid,
x_suar_id => v_cur_sua_ref_cds.suar_id,
x_person_id => v_cur_sua_ref_cds.person_id,
x_course_cd => v_cur_sua_ref_cds.course_cd,
x_uoo_id => v_cur_sua_ref_cds.uoo_id,
x_reference_code_id => v_cur_sua_ref_cds.reference_code_id,
x_reference_cd_type => v_cur_sua_ref_cds.reference_cd_type,
x_reference_cd => v_cur_sua_ref_cds.reference_cd,
x_applied_course_cd => v_cur_sua_ref_cds.applied_course_cd,
x_deleted_date => SYSDATE,
x_mode => 'R' );
END BeforeRowInsertUpdateDelete2;
PROCEDURE AfterRowInsertUpdateDelete3(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
v_message_name VARCHAR2(30);
p_inserting THEN
-- update of student IGS_PS_COURSE attempt after student IGS_PS_UNIT attempt is posted
-- to the database
IF v_rowid_saved = FALSE THEN
-- Save the rowid of the current row.
IF v_rowid_saved = FALSE THEN
IF IGS_EN_GEN_012.ENRP_UPD_SCA_STATUS(
new_references.person_id,
new_references.course_cd,
v_message_name) = FALSE THEN
FND_MESSAGE.SET_NAME('IGS',v_message_name);
END AfterRowInsertUpdateDelete3;
PROCEDURE AfterRowUpdate4(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
BEGIN
-- create a history
IGS_EN_GEN_010.ENRP_INS_SUA_HIST( old_references.person_id,
old_references.course_cd,
old_references.unit_cd,
old_references.cal_type,
old_references.ci_sequence_number,
new_references.version_number,
old_references.version_number,
new_references.location_cd,
old_references.location_cd,
new_references.unit_class,
old_references.unit_class,
new_references.enrolled_dt,
old_references.enrolled_dt,
new_references.unit_attempt_status,
old_references.unit_attempt_status,
new_references.administrative_unit_status,
old_references.administrative_unit_status,
new_references.discontinued_dt,
old_references.discontinued_dt,
new_references.rule_waived_dt,
old_references.rule_waived_dt,
new_references.rule_waived_person_id,
old_references.rule_waived_person_id,
new_references.no_assessment_ind,
old_references.no_assessment_ind,
new_references.exam_location_cd,
old_references.exam_location_cd,
new_references.sup_unit_cd,
old_references.sup_unit_cd,
new_references.sup_version_number,
old_references.sup_version_number,
new_references.alternative_title,
old_references.alternative_title,
new_references.override_enrolled_cp,
old_references.override_enrolled_cp,
new_references.override_eftsu,
old_references.override_eftsu,
new_references.override_achievable_cp,
old_references.override_achievable_cp,
new_references.override_outcome_due_dt,
old_references.override_outcome_due_dt,
new_references.override_credit_reason,
old_references.override_credit_reason,
new_references.last_updated_by,
old_references.last_updated_by,
new_references.last_update_date,
old_references.last_update_date,
new_references.dcnt_reason_cd,
old_references.dcnt_reason_Cd,
old_references.uoo_id,
new_references.core_indicator_code,
old_references.core_indicator_code);
END AfterRowUpdate4;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
SELECT ROWID
FROM IGS_AS_SU_ATMPT_ITM
WHERE person_id = old_references.person_id
AND course_cd = old_references.course_cd
AND uoo_id = old_references.uoo_id;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_H_ALL
WHERE person_id = old_references.person_id
AND course_cd = old_references.course_cd
AND uoo_id = old_references.uoo_id;
IGS_EN_SU_ATTEMPT_H_PKG.DELETE_ROW(X_ROWID => IGS_EN_SU_ATTEMPT_H_REC.ROWID);
IGS_AS_SU_ATMPT_ITM_PKG.DELETE_ROW(X_ROWID => IGS_AS_SU_ATMPT_ITM_REC.ROWID);
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND uoo_id = x_uoo_id
FOR UPDATE NOWAIT;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE cal_type= x_cal_type
AND ci_sequence_number = x_sequence_number
AND ci_start_dt = x_start_dt
AND ci_end_dt = x_end_dt ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE exam_location_cd = x_location_cd ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE grading_schema_code = x_grading_schema_code
AND
gs_version_number = x_gs_version_number ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE rule_waived_person_id = x_person_id ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = x_person_id
AND course_cd = x_course_cd ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE unit_attempt_status = x_unit_attempt_status ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE sup_unit_cd = x_unit_cd
AND sup_version_number = x_version_number
OR (unit_cd = x_unit_cd
AND version_number = x_version_number ) ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE unit_cd = x_unit_cd ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE unit_cd = x_unit_cd
AND version_number = x_version_number
AND cal_type= x_cal_type
AND ci_sequence_number = x_ci_sequence_number
AND location_cd = x_location_cd
AND unit_class= x_unit_class ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE uoo_id = x_uoo_id ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE version_number = x_version_number
AND grading_schema_code = x_grading_schema_code
AND gs_version_number = x_gs_version_number
AND unit_cd = x_unit_cd ;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE grading_schema_code = x_grading_schema_code
AND gs_version_number = x_gs_version_number
AND uoo_id = x_uoo_id ;
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER,
x_org_id IN NUMBER,
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,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
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 ,--Bug ID: 2554109 added by adhawan
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 ,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
x_wlst_priority_weight_num IN NUMBER ,
x_wlst_preference_weight_num IN NUMBER,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
x_core_indicator_code IN VARCHAR2,
X_UPD_AUDIT_FLAG IN VARCHAR2 ,
X_SS_SOURCE_IND IN VARCHAR2
) AS
-------------------------------------------------------------------------------------
--who when what
--smadathi 25-jul-2001 Call to Igs_En_Gen_015.validation_step_is_overridden added
-- to check for unit section status, if it is CLOSED, check whether
-- that has been overriden or not
--kkillams 27-Mar-03 Modified usec_cur Cursor, replaced * with unit_section_status
-- w.r.t. bug 2749648
--svanukur 29-APR-2003 Created cursor c_same_section to prevent students from enrolling in
-- multiple versions of same unit in the same teaching period and also
-- changed the where clauses to pass uoo_id as part of MUS build
--svanukur 23-May-2003 Redefined l_step_override_limit to refer to igs_en_elgb_ovr_uoo
-- as part of Deny/War behaviour build # 2829272
--svanukur 13-Jun-03 Checking for the unit section status of 'Not_offered' as part of
-- validation Impact CR bug#2881385
--svanukur 13-jun-03 The check for cancelled/planned unit section status was done only while
-- inserting the unit attempt record but was not checking while
-- updating a dropped or discontinued unit attempt.
-- Now the check is being done for both for updation and insertion for bug #2980069.
--rvivekan 16-Jun-2003 Added handling for same_teaching_period (MUS) checkbox in unit_Section level
-- as a part of Repeat and reenrollment build #2881363
--rvivekan 9-sep-2003 PSP integration build#3052433. modified behavior and declarations of
-- repeatable_ind in the igs_ps_unit_ver table and the column name of
-- same_teaching_period flag in igs_ps_unit_oft_opt to not_multiple_section_flag.
-- svanukur 14-jan-2004 Added teh code for MUS validations in case of update for bug 3368048
-- rvangala 25-Feb-2004 Change cursors c_mus_allowed and c_mus_participate to ensure that
-- unit attempts not in discontinued status are considered, Bug #3456893
-- bdeviset 22-NOV-2005 Moved mutiple section validtion to IGS_EN_VAL_SUA procedure and
-- Bypassed MUS validation when ss_source_ind is SWAP for bug# 4676023
-- ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
-- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
------------------------------------------------------------------------------------
CURSOR cur_rowid IS
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND uoo_id = x_uoo_id
AND unit_attempt_status = 'DROPPED';
SELECT unit_section_status
FROM igs_ps_unit_ofr_opt uoo
WHERE uoo_id = p_uoo_id;
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_org_id,
x_gs_version_number,
x_enr_method_type ,
x_failed_unit_rule ,
x_cart ,
x_rsv_seat_ext_id,
x_org_unit_cd ,
x_grading_schema_code,
x_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id,
x_deg_aud_detail_id,
x_student_career_transcript,
x_student_career_statistics,
x_waitlist_manual_ind , --Bug ID: 2554109 added by adhawan
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
x_wlst_priority_weight_num,
x_wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
x_core_indicator_code,
X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND
);
IF (p_action = 'INSERT')
OR
(
p_action = 'UPDATE' AND
old_references.unit_attempt_status IN ('DROPPED','DISCONTIN') AND
old_references.unit_attempt_status <> new_references.unit_attempt_status
) THEN
-- Enhancement in response to the bug 1552624
-- This piece of code does NOT allow enrollment into a unit attempt
-- if the unit section status is NOT open.
OPEN usec_cur(new_references.uoo_id);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
p_updating => FALSE,
p_deleting => FALSE );
BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
p_updating => FALSE,
p_deleting => FALSE);
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
p_updating => TRUE,
p_deleting => FALSE);
BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
p_updating => TRUE ,
p_deleting => FALSE);
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
p_updating => FALSE ,
p_deleting => TRUE );
BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
p_updating => FALSE ,
p_deleting => TRUE );
ELSIF (p_action = 'VALIDATE_INSERT') THEN
OPEN cur_rowid;
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Check_Child_Existance;
SELECT * FROM igs_en_su_attempt
WHERE uoo_id = cp_uoo_id AND
unit_attempt_status = 'WAITLISTED'
ORDER BY administrative_priority ASC;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id
FOR UPDATE;
SELECT uv.repeatable_ind
FROM igs_ps_unit_ver uv
WHERE uv.unit_cd = cp_unit_cd AND
uv.version_number = cp_uv_version_number;
SELECT 'x'
FROM igs_en_su_attempt sua
WHERE person_id=cp_person_id
AND uoo_id=cp_uoo_id
AND unit_attempt_status ='COMPLETED'
AND course_cd <> cp_course_cd
AND EXISTS (SELECT 'x'
FROM IGS_PS_STDNT_UNT_TRN sut
WHERE sut.person_id = cp_person_id
AND sut.uoo_id = cp_uoo_id
AND sut.transfer_course_cd = cp_course_cd
AND sut.course_cd = sua.course_cd
) ;
SELECT 'x'
FROM igs_en_su_attempt sua
WHERE person_id=cp_person_id
AND unit_cd=cp_unit_cd
AND version_number=cp_version_number
AND unit_attempt_status IN ('ENROLLED','DUPLICATE','COMPLETED','DISCONTIN')
AND (
(course_cd <> cp_course_cd
AND NOT EXISTS (SELECT 'x'
FROM IGS_PS_STDNT_UNT_TRN sut
WHERE sut.person_id = cp_person_id
and sut.uoo_id = cp_uoo_id
AND sut.transfer_course_cd = sua.course_cd
AND sut.course_cd = cp_course_cd)
)
OR
(course_cd = cp_course_cd AND uoo_id<>cp_uoo_id)); --so that the newly added sua is not counted
SELECT ttl.load_cal_type, ttl.load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V ttl,
IGS_EN_STDNT_PS_ATT spa,
IGS_CA_INST_REL cir
WHERE ttl.teach_cal_type= new_references.cal_type
AND ttl.teach_ci_sequence_number = new_references.ci_sequence_number
AND spa.person_id = new_references.person_id
AND spa.course_cd = new_references.course_cd
AND spa.cal_type = cp_acad_cal_type
AND cir.sup_cal_type = spa.cal_type
AND cir.sub_cal_type = ttl.load_cal_type
AND cir.sub_ci_sequence_number = ttl.load_ci_sequence_number;
SELECT 'x'
FROM igs_en_spa_terms spat, igs_en_stdnt_ps_att curspa, igs_ps_ver curcv,
igs_en_stdnt_ps_att spa, igs_ps_ver cv
WHERE spat.person_id = p_person_id
AND term_cal_type = p_term_cal_type AND term_sequence_number = p_term_ci_sequence_number
AND curspa.person_id=spat.person_id AND curspa.course_cd=p_course_Cd
AND curcv.course_Cd=curspa.course_cd AND curcv.version_number=curspa.version_number
AND spa.person_id=spat.person_id AND spat.program_cd=spa.course_cd
AND cv.course_Cd=spa.course_cd AND cv.version_number=spa.version_number
AND cv.course_type = curcv.course_type
AND (NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'Y' OR spat.program_cd = p_course_cd );
SELECT attendance_type, attendance_mode, key_program, fee_cat, igs_pr_class_std_id,
cal_type, location_cd, coo_id, version_number, student_confirmed_ind
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = new_references.person_id
AND course_cd = new_references.course_cd;
SELECT 'X'
FROM IGS_EN_SPA_TERMS
WHERE person_id = cp_person_id
AND program_cd <> cp_program_cd
AND term_cal_type = cp_term_cal_type
AND term_sequence_number = cp_term_sequence_number
AND acad_cal_type = cp_acad_cal_type
AND key_program_flag = 'Y';
SELECT spat.ROWID ROW_ID, spat.*
FROM igs_en_spa_terms spat
WHERE spat.person_id = p_person_id
AND program_cd=p_course_cd AND plan_sht_status IN ('PLAN', 'NONE')
AND EXISTS
(SELECT load_cal_type,load_ci_sequence_number FROM igs_ca_load_to_teach_v ltCal
WHERE ltCal.teach_cal_type = p_cal_type AND ltCal.teach_ci_sequence_number = p_ci_sequence_number
AND ltCal.load_cal_type = spat.term_cal_type AND ltCal.load_ci_sequence_number = spat.term_sequence_number) ;
IF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
-- AfterRowInsertUpdate5( p_updating => TRUE ); Removed the this procedure from the tbh in the 115.73 version of the file,
END IF; -- end of IF (p_action = 'UPDATE') THEN
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
-- AfterRowInsertUpdate5( p_inserting => TRUE ); Removed the this procedure from the tbh in the 115.73 version of the file,
AfterRowInsertUpdateDelete3 ( p_inserting => TRUE,
p_updating => FALSE,
p_deleting => FALSE);
ELSIF (p_action = 'UPDATE') THEN
-- modified this condition to include DROP also for bug#4864437
-- this code is being commented here but will be replicated in swap page and Drop page
-- SwapAMImpl.java :swapSubmit , StdDropAMImpl.java:dropSubmit
IF NVL(igs_en_su_attempt_pkg.pkg_source_of_drop,'NULL') NOT IN ( 'SWAP','DROP') THEN
AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
p_updating => TRUE,
p_deleting => FALSE );
update_reserved_seat('UPDATE');
AfterRowUpdate4 ( p_inserting => FALSE,
p_updating => TRUE,
p_deleting => FALSE);
igs_en_su_attempt_pkg.update_row(
X_ROWID => l_sua_details.row_id ,
X_PERSON_ID => l_sua_details.person_id ,
X_COURSE_CD => l_sua_details.course_cd ,
X_UNIT_CD => l_sua_details.unit_cd ,
X_CAL_TYPE => l_sua_details.cal_type ,
X_CI_SEQUENCE_NUMBER => l_sua_details.ci_sequence_number ,
X_VERSION_NUMBER => l_sua_details.version_number ,
X_LOCATION_CD => l_sua_details.location_cd ,
X_UNIT_CLASS => l_sua_details.unit_class ,
X_CI_START_DT => l_sua_details.ci_start_dt ,
X_CI_END_DT => l_sua_details.ci_end_dt ,
X_UOO_ID => l_sua_details.uoo_id ,
X_ENROLLED_DT => l_sua_details.enrolled_dt ,
X_UNIT_ATTEMPT_STATUS => l_sua_details.unit_attempt_status ,
X_ADMINISTRATIVE_UNIT_STATUS => l_sua_details.administrative_unit_status ,
X_DISCONTINUED_DT => l_sua_details.discontinued_dt ,
X_RULE_WAIVED_DT => l_sua_details.rule_waived_dt ,
X_RULE_WAIVED_PERSON_ID => l_sua_details.rule_waived_person_id ,
X_NO_ASSESSMENT_IND => l_sua_details.no_assessment_ind ,
X_SUP_UNIT_CD => l_sua_details.sup_unit_cd ,
X_SUP_VERSION_NUMBER => l_sua_details.sup_version_number ,
X_EXAM_LOCATION_CD => l_sua_details.exam_location_cd ,
X_ALTERNATIVE_TITLE => l_sua_details.alternative_title ,
X_OVERRIDE_ENROLLED_CP => l_sua_details.override_enrolled_cp ,
X_OVERRIDE_EFTSU => l_sua_details.override_eftsu ,
X_OVERRIDE_ACHIEVABLE_CP => l_sua_details.override_achievable_cp ,
X_OVERRIDE_OUTCOME_DUE_DT => l_sua_details.override_outcome_due_dt ,
X_OVERRIDE_CREDIT_REASON => l_sua_details.override_credit_reason ,
X_ADMINISTRATIVE_PRIORITY => l_sua_details.administrative_priority ,
X_WAITLIST_DT => l_sua_details.waitlist_dt ,
X_DCNT_REASON_CD => l_sua_details.dcnt_reason_cd ,
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => l_sua_details.gs_version_number ,
X_ENR_METHOD_TYPE => l_sua_details.enr_method_type ,
X_FAILED_UNIT_RULE => l_sua_details.failed_unit_rule ,
X_CART => l_sua_details.cart ,
X_RSV_SEAT_EXT_ID => l_sua_details.rsv_seat_ext_id ,
X_ORG_UNIT_CD => l_sua_details.org_unit_cd ,
X_SESSION_ID => l_sua_details.session_id,
X_GRADING_SCHEMA_CODE => l_sua_details.grading_schema_code ,
X_DEG_AUD_DETAIL_ID => l_sua_details.deg_aud_detail_id ,
X_STUDENT_CAREER_TRANSCRIPT => l_sua_details.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => l_sua_details.student_career_statistics,
x_waitlist_manual_ind => 'N', --Bug ID: 2554109 added by adhawan
X_ATTRIBUTE_CATEGORY => l_sua_details.attribute_category,
X_ATTRIBUTE1 => l_sua_details.attribute1,
X_ATTRIBUTE2 => l_sua_details.attribute2,
X_ATTRIBUTE3 => l_sua_details.attribute3,
X_ATTRIBUTE4 => l_sua_details.attribute4,
X_ATTRIBUTE5 => l_sua_details.attribute5,
X_ATTRIBUTE6 => l_sua_details.attribute6,
X_ATTRIBUTE7 => l_sua_details.attribute7,
X_ATTRIBUTE8 => l_sua_details.attribute8,
X_ATTRIBUTE9 => l_sua_details.attribute9,
X_ATTRIBUTE10 => l_sua_details.attribute10,
X_ATTRIBUTE11 => l_sua_details.attribute11,
X_ATTRIBUTE12 => l_sua_details.attribute12,
X_ATTRIBUTE13 => l_sua_details.attribute13,
X_ATTRIBUTE14 => l_sua_details.attribute14,
X_ATTRIBUTE15 => l_sua_details.attribute15,
X_ATTRIBUTE16 => l_sua_details.attribute16,
X_ATTRIBUTE17 => l_sua_details.attribute17,
X_ATTRIBUTE18 => l_sua_details.attribute18,
X_ATTRIBUTE19 => l_sua_details.attribute19,
X_ATTRIBUTE20 => l_sua_details.attribute20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM => l_sua_details.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_details.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => l_sua_details.core_indicator_code,
X_UPD_AUDIT_FLAG => l_sua_details.UPD_AUDIT_FLAG ,
X_SS_SOURCE_IND => l_sua_details.SS_SOURCE_IND
);
IF (p_action = 'DELETE') THEN
-- Call all the procedures related to After Delete.
AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
p_updating => FALSE,
p_deleting => TRUE );
update_reserved_seat('DELETE');
p_action = 'DELETE' ) THEN
igs_en_sua_api.upd_enrollment_counts( 'DELETE',
old_references,
new_references
);
END IF; -- IF (p_action = 'DELETE') THEN
IF (p_action = 'UPDATE') THEN
IF (old_references.unit_attempt_status <> new_references.unit_attempt_status) AND
(new_references.unit_attempt_status IN ('DROPPED', 'DISCONTIN')) THEN
l_new_cp := NULL;
igs_en_spa_terms_api.create_update_term_rec (
p_person_id => new_references.person_id,
p_program_cd => new_references.course_cd,
p_term_cal_type => rec_tl.load_cal_type ,
p_term_sequence_number => rec_tl.load_ci_sequence_number ,
p_ripple_frwrd => FALSE, -- ripple forward
p_message_name => l_message_name,
p_update_rec => TRUE);
IF p_action in ('INSERT','UPDATE') AND
old_references.unit_attempt_status <> new_references.unit_attempt_status AND
new_references.unit_attempt_status IN ('ENROLLED','WAITLISTED') THEN
FOR rec_spa_terms IN
c_spa_terms(new_references.person_id, new_references.course_cd, new_references.cal_type , new_references.ci_sequence_number)
LOOP
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 =>'SKIP');
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_PERSON_ID IN NUMBER,
X_COURSE_CD IN VARCHAR2,
X_UNIT_CD IN VARCHAR2,
X_CAL_TYPE IN VARCHAR2,
X_CI_SEQUENCE_NUMBER IN NUMBER,
X_VERSION_NUMBER IN NUMBER,
X_LOCATION_CD IN VARCHAR2,
X_UNIT_CLASS IN VARCHAR2,
X_CI_START_DT IN DATE,
X_CI_END_DT IN DATE,
X_UOO_ID IN NUMBER,
X_ENROLLED_DT IN DATE,
X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
X_DISCONTINUED_DT IN DATE,
X_RULE_WAIVED_DT IN DATE,
X_RULE_WAIVED_PERSON_ID IN NUMBER,
X_NO_ASSESSMENT_IND IN VARCHAR2,
X_SUP_UNIT_CD IN VARCHAR2,
X_SUP_VERSION_NUMBER IN NUMBER,
X_EXAM_LOCATION_CD IN VARCHAR2,
X_ALTERNATIVE_TITLE IN VARCHAR2,
X_OVERRIDE_ENROLLED_CP IN NUMBER,
X_OVERRIDE_EFTSU IN NUMBER,
X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
X_ADMINISTRATIVE_PRIORITY IN NUMBER,
X_WAITLIST_DT IN DATE,
X_DCNT_REASON_CD IN VARCHAR2,
X_MODE IN VARCHAR2 ,
x_org_id IN NUMBER,
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 ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
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,--Bug ID: 2554109 added by adhawan
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 ,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER ,
X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER ,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE IN VARCHAR2,
X_UPD_AUDIT_FLAG IN VARCHAR2,
X_SS_SOURCE_IND IN VARCHAR2
) AS
/*------------------------------------------------------------------------------------------------------------------------------------------
knaraset 10-Jul-2003 Added unique key along with pk to check existance of Dropped units
svanukur 28-APR-03 changed the where clauses to reflect teh new Primary key
as part of MUS build, # 2829262
sbaliga 13-feb-2002 assigned igs_ge_gen_003.get_org_id to x_org_id in call to before_dml
as part of SWCR006 build.
---------------------------------------------------------------------------------------*/
-- Changing the parameters X_UNIT_CD, X_CAL_TYPE, X_CI_SEQUENCE_NUMBER TO NEW_REFERENCES
-- in response to the bug 1766230. For further detils please refer to version 115.19 of IGSEI36B.pls
CURSOR C IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
WHERE PERSON_ID = X_PERSON_ID
AND COURSE_CD = X_COURSE_CD
AND UOO_ID = X_UOO_ID;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND unit_attempt_status = 'DROPPED'
AND ( uoo_id = x_uoo_id
OR ( unit_cd = x_unit_cd AND
version_number = x_version_number AND
cal_type = x_cal_type AND
ci_sequence_number = x_ci_sequence_number AND
location_cd = x_location_cd AND
unit_class = x_unit_class
));
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_PROGRAM_UPDATE_DATE DATE;
UPDATE_ROW (
x_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_CD,
X_CAL_TYPE,
X_CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER,
X_LOCATION_CD,
X_UNIT_CLASS,
X_CI_START_DT,
X_CI_END_DT,
X_UOO_ID,
X_ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT,
X_RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND,
X_SUP_UNIT_CD,
X_SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT,
X_DCNT_REASON_CD,
X_MODE,
X_GS_VERSION_NUMBER ,
X_ENR_METHOD_TYPE ,
X_FAILED_UNIT_RULE ,
X_CART ,
X_RSV_SEAT_EXT_ID ,
X_org_unit_cd ,
X_GRADING_SCHEMA_CODE,
x_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id,
x_deg_aud_detail_id,
x_student_career_transcript,
x_student_career_statistics,
x_waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND
);
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
IF X_LAST_UPDATED_BY IS NULL THEN
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
IF X_LAST_UPDATE_LOGIN IS NULL THEN
X_LAST_UPDATE_LOGIN := -1;
X_PROGRAM_UPDATE_DATE := NULL;
X_PROGRAM_UPDATE_DATE := SYSDATE;
p_action=>'INSERT',
x_rowid=>NULL,
x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
x_alternative_title=>X_ALTERNATIVE_TITLE,
x_cal_type=>X_CAL_TYPE,
x_ci_end_dt=>X_CI_END_DT,
x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
x_ci_start_dt=>X_CI_START_DT,
x_course_cd=>X_COURSE_CD,
x_discontinued_dt=>X_DISCONTINUED_DT,
x_enrolled_dt=>X_ENROLLED_DT,
x_exam_location_cd=>X_EXAM_LOCATION_CD,
x_location_cd=>X_LOCATION_CD,
x_no_assessment_ind=> NVL(X_NO_ASSESSMENT_IND,'N'),
x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
x_override_eftsu=>X_OVERRIDE_EFTSU,
x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
x_person_id=>X_PERSON_ID,
x_rule_waived_dt=>X_RULE_WAIVED_DT,
x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
x_sup_unit_cd=>X_SUP_UNIT_CD,
x_sup_version_number=>X_SUP_VERSION_NUMBER,
x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
x_unit_cd=>X_UNIT_CD,
x_unit_class=>X_UNIT_CLASS,
x_uoo_id=>X_UOO_ID,
x_version_number=>X_VERSION_NUMBER,
x_administrative_priority=>X_ADMINISTRATIVE_PRIORITY,
x_waitlist_dt=>X_WAITLIST_DT,
x_dcnt_reason_cd => X_DCNT_REASON_CD,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_org_id => igs_ge_gen_003.get_org_id,
x_gs_version_number => X_GS_VERSION_NUMBER,
x_enr_method_type => X_ENR_METHOD_TYPE ,
x_failed_unit_rule => X_FAILED_UNIT_RULE ,
x_cart => X_CART ,
x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
x_org_unit_cd=>X_ORG_UNIT_CD ,
x_grading_schema_code => X_GRADING_SCHEMA_CODE,
x_subtitle => x_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id => x_session_id,
x_deg_aud_detail_id => x_deg_aud_detail_id,
x_student_career_transcript => x_student_career_transcript,
x_student_career_statistics => x_student_career_statistics,
x_waitlist_manual_ind => x_waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
x_attribute_category=>X_ATTRIBUTE_CATEGORY,
x_attribute1=>X_ATTRIBUTE1,
x_attribute2=>X_ATTRIBUTE2,
x_attribute3=>X_ATTRIBUTE3,
x_attribute4=>X_ATTRIBUTE4,
x_attribute5=>X_ATTRIBUTE5,
x_attribute6=>X_ATTRIBUTE6,
x_attribute7=>X_ATTRIBUTE7,
x_attribute8=>X_ATTRIBUTE8,
x_attribute9=>X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
x_wlst_priority_weight_num=>X_WLST_PRIORITY_WEIGHT_NUM,
x_wlst_preference_weight_num=>X_WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
x_core_indicator_code=>X_CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG =>X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND => X_SS_SOURCE_IND
);
INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
PERSON_ID,
COURSE_CD,
UNIT_CD,
VERSION_NUMBER,
CAL_TYPE,
CI_SEQUENCE_NUMBER,
LOCATION_CD,
UNIT_CLASS,
CI_START_DT,
CI_END_DT,
UOO_ID,
ENROLLED_DT,
UNIT_ATTEMPT_STATUS,
ADMINISTRATIVE_UNIT_STATUS,
DISCONTINUED_DT,
RULE_WAIVED_DT,
RULE_WAIVED_PERSON_ID,
NO_ASSESSMENT_IND,
SUP_UNIT_CD,
SUP_VERSION_NUMBER,
EXAM_LOCATION_CD,
ALTERNATIVE_TITLE,
OVERRIDE_ENROLLED_CP,
OVERRIDE_EFTSU,
OVERRIDE_ACHIEVABLE_CP,
OVERRIDE_OUTCOME_DUE_DT,
OVERRIDE_CREDIT_REASON,
ADMINISTRATIVE_PRIORITY,
WAITLIST_DT,
DCNT_REASON_CD,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
org_id,
GS_VERSION_NUMBER,
ENR_METHOD_TYPE ,
FAILED_UNIT_RULE ,
CART ,
RSV_SEAT_EXT_ID ,
ORG_UNIT_CD ,
GRADING_SCHEMA_CODE ,
subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
session_id,
deg_aud_detail_id,
student_career_transcript,
student_career_statistics,
waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
WLST_PRIORITY_WEIGHT_NUM,
WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
CORE_INDICATOR_CODE,
UPD_AUDIT_FLAG,
SS_SOURCE_IND ) VALUES (
NEW_REFERENCES.PERSON_ID,
NEW_REFERENCES.COURSE_CD,
NEW_REFERENCES.UNIT_CD,
NEW_REFERENCES.VERSION_NUMBER,
NEW_REFERENCES.CAL_TYPE,
NEW_REFERENCES.CI_SEQUENCE_NUMBER,
NEW_REFERENCES.LOCATION_CD,
NEW_REFERENCES.UNIT_CLASS,
NEW_REFERENCES.CI_START_DT,
NEW_REFERENCES.CI_END_DT,
NEW_REFERENCES.UOO_ID,
NEW_REFERENCES.ENROLLED_DT,
NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
NEW_REFERENCES.DISCONTINUED_DT,
TRUNC(NEW_REFERENCES.RULE_WAIVED_DT),
NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
NEW_REFERENCES.NO_ASSESSMENT_IND,
NEW_REFERENCES.SUP_UNIT_CD,
NEW_REFERENCES.SUP_VERSION_NUMBER,
NEW_REFERENCES.EXAM_LOCATION_CD,
NEW_REFERENCES.ALTERNATIVE_TITLE,
NEW_REFERENCES.OVERRIDE_ENROLLED_CP,
NEW_REFERENCES.OVERRIDE_EFTSU,
NEW_REFERENCES.OVERRIDE_ACHIEVABLE_CP,
NEW_REFERENCES.OVERRIDE_OUTCOME_DUE_DT,
NEW_REFERENCES.OVERRIDE_CREDIT_REASON,
NEW_REFERENCES.ADMINISTRATIVE_PRIORITY,
NEW_REFERENCES.WAITLIST_DT,
NEW_REFERENCES.DCNT_REASON_CD,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_UPDATE_DATE,
NEW_REFERENCES.org_id,
NEW_REFERENCES.GS_VERSION_NUMBER,
NEW_REFERENCES.ENR_METHOD_TYPE,
NEW_REFERENCES.FAILED_UNIT_RULE,
NEW_REFERENCES.CART ,
NEW_REFERENCES.RSV_SEAT_EXT_ID ,
NEW_REFERENCES.ORG_UNIT_CD ,
NEW_REFERENCES.GRADING_SCHEMA_CODE ,
new_references.subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
new_references.session_id,
new_references.deg_aud_detail_id ,
new_references.student_career_transcript,
new_references.student_career_statistics,
new_references.waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
NEW_REFERENCES.ATTRIBUTE_CATEGORY,
NEW_REFERENCES.ATTRIBUTE1,
NEW_REFERENCES.ATTRIBUTE2,
NEW_REFERENCES.ATTRIBUTE3,
NEW_REFERENCES.ATTRIBUTE4,
NEW_REFERENCES.ATTRIBUTE5,
NEW_REFERENCES.ATTRIBUTE6,
NEW_REFERENCES.ATTRIBUTE7,
NEW_REFERENCES.ATTRIBUTE8,
NEW_REFERENCES.ATTRIBUTE9,
NEW_REFERENCES.ATTRIBUTE10,
NEW_REFERENCES.ATTRIBUTE11,
NEW_REFERENCES.ATTRIBUTE12,
NEW_REFERENCES.ATTRIBUTE13,
NEW_REFERENCES.ATTRIBUTE14,
NEW_REFERENCES.ATTRIBUTE15,
NEW_REFERENCES.ATTRIBUTE16,
NEW_REFERENCES.ATTRIBUTE17,
NEW_REFERENCES.ATTRIBUTE18,
NEW_REFERENCES.ATTRIBUTE19,
NEW_REFERENCES.ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM,
NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
NEW_REFERENCES.CORE_INDICATOR_CODE,
NEW_REFERENCES.UPD_AUDIT_FLAG,
NEW_REFERENCES.SS_SOURCE_IND );
p_action => 'INSERT',
x_rowid => X_ROWID
);
END INSERT_ROW;
CURSOR c1 IS SELECT
VERSION_NUMBER,
LOCATION_CD,
UNIT_CLASS,
CI_START_DT,
CI_END_DT,
UOO_ID,
ENROLLED_DT,
UNIT_ATTEMPT_STATUS,
ADMINISTRATIVE_UNIT_STATUS,
DISCONTINUED_DT,
RULE_WAIVED_DT,
RULE_WAIVED_PERSON_ID,
NO_ASSESSMENT_IND,
SUP_UNIT_CD,
SUP_VERSION_NUMBER,
EXAM_LOCATION_CD,
ALTERNATIVE_TITLE,
OVERRIDE_ENROLLED_CP,
OVERRIDE_EFTSU,
OVERRIDE_ACHIEVABLE_CP,
OVERRIDE_OUTCOME_DUE_DT,
OVERRIDE_CREDIT_REASON,
ADMINISTRATIVE_PRIORITY,
WAITLIST_DT,
dcnt_reason_cd,
GS_VERSION_NUMBER,
ENR_METHOD_TYPE ,
FAILED_UNIT_RULE ,
CART ,
RSV_SEAT_EXT_ID ,
ORG_UNIT_CD ,
GRADING_SCHEMA_CODE,
subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
session_id,
deg_aud_detail_id,
student_career_transcript,
student_career_statistics,
waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
WLST_PRIORITY_WEIGHT_NUM,
WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
CORE_INDICATOR_CODE,
UPD_AUDIT_FLAG,
SS_SOURCE_IND
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
svanukur 28-APR-03 Not allowing the update of uoo_id as it is now part of Pk
-- as part of MUS build, # 2829262
---------------------------------------------------------------------------------------*/
PROCEDURE UPDATE_ROW (
X_ROWID IN VARCHAR2,
X_PERSON_ID IN NUMBER,
X_COURSE_CD IN VARCHAR2,
X_UNIT_CD IN VARCHAR2,
X_CAL_TYPE IN VARCHAR2,
X_CI_SEQUENCE_NUMBER IN NUMBER,
X_VERSION_NUMBER IN NUMBER,
X_LOCATION_CD IN VARCHAR2,
X_UNIT_CLASS IN VARCHAR2,
X_CI_START_DT IN DATE,
X_CI_END_DT IN DATE,
X_UOO_ID IN NUMBER,
X_ENROLLED_DT IN DATE,
X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
X_DISCONTINUED_DT IN DATE,
X_RULE_WAIVED_DT IN DATE,
X_RULE_WAIVED_PERSON_ID IN NUMBER,
X_NO_ASSESSMENT_IND IN VARCHAR2,
X_SUP_UNIT_CD IN VARCHAR2,
X_SUP_VERSION_NUMBER IN NUMBER,
X_EXAM_LOCATION_CD IN VARCHAR2,
X_ALTERNATIVE_TITLE IN VARCHAR2,
X_OVERRIDE_ENROLLED_CP IN NUMBER,
X_OVERRIDE_EFTSU IN NUMBER,
X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
X_ADMINISTRATIVE_PRIORITY IN NUMBER,
X_WAITLIST_DT IN DATE,
x_dcnt_reason_cd IN VARCHAR2,
X_MODE IN VARCHAR2 ,
X_GS_VERSION_NUMBER IN NUMBER ,
X_ENR_METHOD_TYPE IN VARCHAR2,
X_FAILED_UNIT_RULE IN VARCHAR2,
X_CART IN VARCHAR2,
X_RSV_SEAT_EXT_ID IN NUMBER ,
X_ORG_UNIT_CD IN VARCHAR2 ,
X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
x_subtitle IN VARCHAR2 ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
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,--Bug ID: 2554109 added by adhawan
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 ,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER ,
X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER ,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE IN VARCHAR2,
X_UPD_AUDIT_FLAG IN VARCHAR2 ,
X_SS_SOURCE_IND IN VARCHAR2
) AS
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_PROGRAM_UPDATE_DATE DATE;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
IF X_LAST_UPDATED_BY IS NULL THEN
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
IF X_LAST_UPDATE_LOGIN IS NULL THEN
X_LAST_UPDATE_LOGIN := -1;
p_action=>'UPDATE',
x_rowid=>X_ROWID,
x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
x_alternative_title=>X_ALTERNATIVE_TITLE,
x_cal_type=>X_CAL_TYPE,
x_ci_end_dt=>X_CI_END_DT,
x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
x_ci_start_dt=>X_CI_START_DT,
x_course_cd=>X_COURSE_CD,
x_discontinued_dt=>X_DISCONTINUED_DT,
x_enrolled_dt=>X_ENROLLED_DT,
x_exam_location_cd=>X_EXAM_LOCATION_CD,
x_location_cd=>X_LOCATION_CD,
x_no_assessment_ind=>NVL(X_NO_ASSESSMENT_IND,'N'),
x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
x_override_eftsu=>X_OVERRIDE_EFTSU,
x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
x_person_id=>X_PERSON_ID,
x_rule_waived_dt=>X_RULE_WAIVED_DT,
x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
x_sup_unit_cd=>X_SUP_UNIT_CD,
x_sup_version_number=>X_SUP_VERSION_NUMBER,
x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
x_unit_cd=>X_UNIT_CD,
x_unit_class=>X_UNIT_CLASS,
x_uoo_id=>X_UOO_ID,
x_version_number=>X_VERSION_NUMBER,
x_administrative_priority=>X_ADMINISTRATIVE_PRIORITY,
x_waitlist_dt=>X_WAITLIST_DT,
x_dcnt_reason_cd => x_dcnt_reason_cd,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_gs_version_number=>X_GS_VERSION_NUMBER,
x_enr_method_type=>X_ENR_METHOD_TYPE,
x_failed_unit_rule=>X_FAILED_UNIT_RULE,
x_cart => X_CART,
x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
x_org_unit_cd => X_ORG_UNIT_CD ,
x_grading_schema_code => X_GRADING_SCHEMA_CODE ,
x_subtitle => x_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id => x_session_id,
x_deg_aud_detail_id => x_deg_aud_detail_id,
x_student_career_transcript => x_student_career_transcript,
x_student_career_statistics => x_student_career_statistics,
x_waitlist_manual_ind => x_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
x_attribute_category=>X_ATTRIBUTE_CATEGORY,
x_attribute1=>X_ATTRIBUTE1,
x_attribute2=>X_ATTRIBUTE2,
x_attribute3=>X_ATTRIBUTE3,
x_attribute4=>X_ATTRIBUTE4,
x_attribute5=>X_ATTRIBUTE5,
x_attribute6=>X_ATTRIBUTE6,
x_attribute7=>X_ATTRIBUTE7,
x_attribute8=>X_ATTRIBUTE8,
x_attribute9=>X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
x_wlst_priority_weight_num=>X_WLST_PRIORITY_WEIGHT_NUM,
x_wlst_preference_weight_num=>X_WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
x_core_indicator_code=>X_CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG => X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND => X_SS_SOURCE_IND
);
X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
X_PROGRAM_UPDATE_DATE := SYSDATE;
UPDATE IGS_EN_SU_ATTEMPT_ALL SET
VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
CI_START_DT = NEW_REFERENCES.CI_START_DT,
CI_END_DT = NEW_REFERENCES.CI_END_DT,
ENROLLED_DT = NEW_REFERENCES.ENROLLED_DT,
UNIT_ATTEMPT_STATUS = NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
ADMINISTRATIVE_UNIT_STATUS = NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
DISCONTINUED_DT = NEW_REFERENCES.DISCONTINUED_DT,
RULE_WAIVED_DT = TRUNC(NEW_REFERENCES.RULE_WAIVED_DT),
RULE_WAIVED_PERSON_ID = NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
NO_ASSESSMENT_IND = NEW_REFERENCES.NO_ASSESSMENT_IND,
SUP_UNIT_CD = NEW_REFERENCES.SUP_UNIT_CD,
SUP_VERSION_NUMBER = NEW_REFERENCES.SUP_VERSION_NUMBER,
EXAM_LOCATION_CD = NEW_REFERENCES.EXAM_LOCATION_CD,
ALTERNATIVE_TITLE = NEW_REFERENCES.ALTERNATIVE_TITLE,
OVERRIDE_ENROLLED_CP = NEW_REFERENCES.OVERRIDE_ENROLLED_CP,
OVERRIDE_EFTSU = NEW_REFERENCES.OVERRIDE_EFTSU,
OVERRIDE_ACHIEVABLE_CP = NEW_REFERENCES.OVERRIDE_ACHIEVABLE_CP,
OVERRIDE_OUTCOME_DUE_DT = NEW_REFERENCES.OVERRIDE_OUTCOME_DUE_DT,
OVERRIDE_CREDIT_REASON = NEW_REFERENCES.OVERRIDE_CREDIT_REASON,
ADMINISTRATIVE_PRIORITY = NEW_REFERENCES.ADMINISTRATIVE_PRIORITY,
WAITLIST_DT = NEW_REFERENCES.WAITLIST_DT,
DCNT_REASON_CD = new_references.DCNT_REASON_CD,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER,
ENR_METHOD_TYPE = NEW_REFERENCES.ENR_METHOD_TYPE ,
FAILED_UNIT_RULE = NEW_REFERENCES.FAILED_UNIT_RULE ,
CART = NEW_REFERENCES.CART ,
RSV_SEAT_EXT_ID = NEW_REFERENCES.RSV_SEAT_EXT_ID,
ORG_UNIT_CD = NEW_REFERENCES.ORG_UNIT_CD ,
GRADING_SCHEMA_CODE = NEW_REFERENCES.GRADING_SCHEMA_CODE,
subtitle = new_references.subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
session_id = new_references.session_id,
deg_aud_detail_id = new_references.deg_aud_detail_id ,
student_career_transcript = new_references.student_career_transcript,
student_career_statistics = new_references.student_career_statistics,
waitlist_manual_ind = new_references.waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
WLST_PRIORITY_WEIGHT_NUM = NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM,
WLST_PREFERENCE_WEIGHT_NUM = NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
CORE_INDICATOR_CODE = NEW_REFERENCES.CORE_INDICATOR_CODE,
UPD_AUDIT_FLAG = NEW_REFERENCES.UPD_AUDIT_FLAG ,
SS_SOURCE_IND = NEW_REFERENCES. SS_SOURCE_IND
WHERE ROWID = X_ROWID;
p_action => 'UPDATE',
x_rowid => X_ROWID
);
END UPDATE_ROW;
CURSOR c1 IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
WHERE PERSON_ID = X_PERSON_ID
AND COURSE_CD = X_COURSE_CD
AND UOO_ID = X_UOO_ID
;
INSERT_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_CD,
X_CAL_TYPE,
X_CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER,
X_LOCATION_CD,
X_UNIT_CLASS,
X_CI_START_DT,
X_CI_END_DT,
X_UOO_ID,
X_ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT,
X_RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND,
X_SUP_UNIT_CD,
X_SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT,
X_DCNT_REASON_CD,
X_MODE,
x_org_id ,
X_GS_VERSION_NUMBER ,
X_ENR_METHOD_TYPE ,
X_FAILED_UNIT_RULE ,
X_CART ,
X_RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD ,
X_GRADING_SCHEMA_CODE ,
X_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id,
X_deg_aud_detail_id ,
x_student_career_transcript,
x_student_career_statistics,
X_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND
);
UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_CD,
X_CAL_TYPE,
X_CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER,
X_LOCATION_CD,
X_UNIT_CLASS,
X_CI_START_DT,
X_CI_END_DT,
X_UOO_ID,
X_ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS,
X_DISCONTINUED_DT,
X_RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND,
X_SUP_UNIT_CD,
X_SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT,
X_DCNT_REASON_CD,
X_MODE,
X_GS_VERSION_NUMBER,
X_ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE,
X_CART ,
X_RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD,
X_GRADING_SCHEMA_CODE ,
X_subtitle,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
x_session_id,
X_deg_aud_detail_id ,
x_student_career_transcript,
x_student_career_statistics,
X_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM,
X_WLST_PREFERENCE_WEIGHT_NUM,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE,
X_UPD_AUDIT_FLAG,
X_SS_SOURCE_IND
);
PROCEDURE DELETE_ROW (
X_ROWID IN VARCHAR2,
x_mode IN VARCHAR2) AS
/* Cursor to get the unit attempt attempts attributes, bug 3000742*/
CURSOR cur_sua (cp_rowid VARCHAR2) IS
SELECT *
FROM igs_en_su_attempt
WHERE row_id = cp_rowid;
SELECT unit_attempt_status
FROM igs_en_su_attempt_h
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id
ORDER BY hist_end_dt DESC;
igs_en_sua_api.update_unit_attempt(
X_ROWID => X_ROWID,
X_PERSON_ID => l_cur_sua_rec.PERSON_ID,
X_COURSE_CD => l_cur_sua_rec.COURSE_CD ,
X_UNIT_CD => l_cur_sua_rec.UNIT_CD,
X_CAL_TYPE => l_cur_sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => l_cur_sua_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => l_cur_sua_rec.version_number ,
X_LOCATION_CD => l_cur_sua_rec.location_cd,
X_UNIT_CLASS => l_cur_sua_rec.unit_class,
X_CI_START_DT => l_cur_sua_rec.CI_START_DT,
X_CI_END_DT => l_cur_sua_rec.CI_END_DT,
X_UOO_ID => l_cur_sua_rec.uoo_id,
X_ENROLLED_DT => l_cur_sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => l_cur_sua_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => l_cur_sua_rec.administrative_priority,
X_DISCONTINUED_DT => nvl(l_cur_sua_rec.discontinued_dt,SYSDATE),
X_DCNT_REASON_CD => l_cur_sua_rec.DCNT_REASON_CD ,
X_RULE_WAIVED_DT => l_cur_sua_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => l_cur_sua_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => l_cur_sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => l_cur_sua_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => l_cur_sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => l_cur_sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => l_cur_sua_rec.ALTERNATIVE_TITLE ,
X_OVERRIDE_ENROLLED_CP => l_cur_sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => l_cur_sua_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => l_cur_sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => l_cur_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => l_cur_sua_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => l_cur_sua_rec.waitlist_dt,
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => l_cur_sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => l_cur_sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => l_cur_sua_rec.failed_unit_rule ,
X_CART => l_cur_sua_rec.cart ,
X_RSV_SEAT_EXT_ID => l_cur_sua_rec.rsv_seat_ext_id,
X_ORG_UNIT_CD => l_cur_sua_rec.org_unit_cd,
X_SESSION_ID => l_cur_sua_rec.session_id,
X_GRADING_SCHEMA_CODE => l_cur_sua_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => l_cur_sua_rec.deg_aud_detail_id,
X_SUBTITLE => l_cur_sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => l_cur_sua_rec.student_career_transcript ,
X_STUDENT_CAREER_STATISTICS => l_cur_sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => l_cur_sua_rec.attribute_category,
X_ATTRIBUTE1 => l_cur_sua_rec.attribute1,
X_ATTRIBUTE2 => l_cur_sua_rec.attribute2,
X_ATTRIBUTE3 => l_cur_sua_rec.attribute3,
X_ATTRIBUTE4 => l_cur_sua_rec.attribute4,
X_ATTRIBUTE5 => l_cur_sua_rec.attribute5,
X_ATTRIBUTE6 => l_cur_sua_rec.attribute6,
X_ATTRIBUTE7 => l_cur_sua_rec.attribute7,
X_ATTRIBUTE8 => l_cur_sua_rec.attribute8,
X_ATTRIBUTE9 => l_cur_sua_rec.attribute9,
X_ATTRIBUTE10 => l_cur_sua_rec.attribute10,
X_ATTRIBUTE11 => l_cur_sua_rec.attribute11,
X_ATTRIBUTE12 => l_cur_sua_rec.attribute12,
X_ATTRIBUTE13 => l_cur_sua_rec.attribute13,
X_ATTRIBUTE14 => l_cur_sua_rec.attribute14,
X_ATTRIBUTE15 => l_cur_sua_rec.attribute15,
X_ATTRIBUTE16 => l_cur_sua_rec.attribute16,
X_ATTRIBUTE17 => l_cur_sua_rec.attribute17,
X_ATTRIBUTE18 => l_cur_sua_rec.attribute18,
X_ATTRIBUTE19 => l_cur_sua_rec.attribute19,
X_ATTRIBUTE20 => l_cur_sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => l_cur_sua_rec.waitlist_manual_ind,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM => l_cur_sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_cur_sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by ptandon 01-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => l_cur_sua_rec.core_indicator_code/*,
X_UPD_AUDIT_FLAG => l_cur_sua_rec.UPD_AUDIT_FLAG ,
X_SS_SOURCE_IND => l_cur_sua_rec.SS_SOURCE_IND */
);
p_action => 'DELETE',
x_rowid => X_ROWID
);
DELETE FROM IGS_EN_SU_ATTEMPT_ALL
WHERE ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
END DELETE_ROW;
SELECT ROWID
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE unit_cd = x_unit_cd
AND cal_type = x_cal_type
AND ci_sequence_number = x_ci_sequence_number
AND location_cd = x_location_cd
AND unit_class = x_unit_class
AND person_id = x_person_id
AND course_cd = x_course_cd
AND version_number = x_version_number
AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
PROCEDURE update_reserved_seat( p_action IN VARCHAR2)
AS
--
-- For Enhancement Bug 1832830
-- Local Procedure added to decrement actual_seat_enrolled once the
-- unit administrative status changes from ENROLLED/INVALID to DROPPED/DISCCONTIN
-- Called in After DML
--
-- 25-Jun-2002 pmarada Added the code when the unit deleting from the su_attempt, decreasing the
-- actual enrollment. bug 2423787
CURSOR cur_igs_ps_rsv_ext (cp_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE)
IS
SELECT rsv.ROWID row_id, rsv.*
FROM igs_ps_rsv_ext rsv
WHERE rsv_ext_id = cp_rsv_ext_id;
IF ( p_action = 'UPDATE') THEN
-- checking if the unit attempt status has been changed from ENROLLED/INVALID to DROPPED/DISCONTIN
IF (old_references.unit_attempt_status IN ('ENROLLED','INVALID')
AND (new_references.unit_attempt_status IN ('DROPPED','DISCONTIN'))) THEN
IF ( old_references.rsv_seat_ext_id = new_references.rsv_seat_ext_id ) THEN
l_rsv_ext_id := old_references.rsv_seat_ext_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'
);
ELSIF p_action ='DELETE' THEN
-- While deleting the su attempt record, if record exists in the reserve extension table with this reserve extension id decreasing the
-- actual seat enrollment.
-- Suppose user added the unit to cart and closed the browser or timeout, the actual enrollment is matching.
-- while user login into SS, decrementing the actual seat enrollment., pmarada, bug 2423787
IF old_references.rsv_seat_ext_id IS NOT NULL THEN
OPEN cur_igs_ps_rsv_ext(old_references.rsv_seat_ext_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'
);
END update_reserved_seat;