1 PACKAGE BODY Igs_En_Su_Attempt_Pkg AS
2 /* $Header: IGSEI36B.pls 120.22 2006/03/02 03:30:18 bdeviset ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
6 new_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
7 l_old_cp NUMBER(10);
8 l_new_cp NUMBER(10);
9 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
10 l_load_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
11
12 -- Cursor to update the enrollment actual column
13 -- The field enrollment_actual needs to be updated by 1
14 -- if the unit attempt is successful.
15 -- decrement by 1 if the unit attempt is discontinued or deleted
16 PROCEDURE update_reserved_seat( p_action IN VARCHAR2);
17
18 PROCEDURE Set_Column_Values (
19 p_action IN VARCHAR2,
20 x_rowid IN VARCHAR2 ,
21 x_uoo_id IN NUMBER ,
22 x_enrolled_dt IN DATE ,
23 x_unit_attempt_status IN VARCHAR2 ,
24 x_administrative_unit_status IN VARCHAR2,
25 x_discontinued_dt IN DATE ,
26 x_rule_waived_dt IN DATE ,
27 x_rule_waived_person_id IN NUMBER,
28 x_no_assessment_ind IN VARCHAR2 ,
29 x_sup_unit_cd IN VARCHAR2,
30 x_sup_version_number IN NUMBER,
31 x_exam_location_cd IN VARCHAR2,
32 x_alternative_title IN VARCHAR2,
33 x_override_enrolled_cp IN NUMBER,
34 x_override_eftsu IN NUMBER,
35 x_override_achievable_cp IN NUMBER,
36 x_override_outcome_due_dt IN DATE,
37 x_override_credit_reason IN VARCHAR2,
38 x_person_id IN NUMBER,
39 x_course_cd IN VARCHAR2,
40 x_unit_cd IN VARCHAR2,
41 x_version_number IN NUMBER,
42 x_cal_type IN VARCHAR2 ,
43 x_ci_sequence_number IN NUMBER ,
44 x_location_cd IN VARCHAR2 ,
45 x_unit_class IN VARCHAR2 ,
46 x_ci_start_dt IN DATE ,
47 x_ci_end_dt IN DATE ,
48 x_administrative_priority IN NUMBER ,
49 x_waitlist_dt IN DATE ,
50 x_dcnt_reason_cd IN VARCHAR2 ,
51 x_creation_date IN DATE ,
52 x_created_by IN NUMBER,
53 x_last_update_date IN DATE,
54 x_last_updated_by IN NUMBER,
55 x_last_update_login IN NUMBER,
56 x_org_id IN NUMBER,
57 x_gs_version_number IN NUMBER,
58 x_enr_method_type IN VARCHAR2 ,
59 x_failed_unit_rule IN VARCHAR2 ,
60 x_cart IN VARCHAR2 ,
61 x_rsv_seat_ext_id IN NUMBER ,
62 x_org_unit_cd IN VARCHAR2 ,
63 x_grading_schema_code IN VARCHAR2,
64 x_subtitle IN VARCHAR2 ,
65 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
66 x_session_id IN NUMBER ,
67 x_deg_aud_detail_id IN NUMBER ,
68 x_student_career_transcript IN VARCHAR2,
69 x_student_career_statistics IN VARCHAR2,
70 x_waitlist_manual_ind IN VARCHAR2,--Bug ID: 2554109 added by adhawan
71 x_attribute_category IN VARCHAR2 ,
72 x_attribute1 IN VARCHAR2 ,
73 x_attribute2 IN VARCHAR2 ,
74 x_attribute3 IN VARCHAR2 ,
75 x_attribute4 IN VARCHAR2 ,
76 x_attribute5 IN VARCHAR2 ,
77 x_attribute6 IN VARCHAR2 ,
78 x_attribute7 IN VARCHAR2 ,
79 x_attribute8 IN VARCHAR2 ,
80 x_attribute9 IN VARCHAR2 ,
81 x_attribute10 IN VARCHAR2,
82 x_attribute11 IN VARCHAR2,
83 x_attribute12 IN VARCHAR2,
84 x_attribute13 IN VARCHAR2,
85 x_attribute14 IN VARCHAR2,
86 x_attribute15 IN VARCHAR2,
87 x_attribute16 IN VARCHAR2,
88 x_attribute17 IN VARCHAR2,
89 x_attribute18 IN VARCHAR2,
90 x_attribute19 IN VARCHAR2,
91 x_attribute20 IN VARCHAR2,
92 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
93 x_wlst_priority_weight_num IN NUMBER,
94 x_wlst_preference_weight_num IN NUMBER,
95 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
96 x_core_indicator_code IN VARCHAR2,
97 X_UPD_AUDIT_FLAG IN VARCHAR2 DEFAULT 'N',
98 X_SS_SOURCE_IND IN VARCHAR2 DEFAULT 'N'
99 ) AS
100 CURSOR cur_old_ref_values IS
101 SELECT *
102 FROM IGS_EN_SU_ATTEMPT_ALL
103 WHERE ROWID = x_rowid;
104 BEGIN
105 l_rowid := x_rowid;
106 -- initialising the global variables for bug#5020285
107 old_references := NULL;
108 new_references := NULL;
109
110 -- Code for setting the Old and New Reference Values.
111 -- Populate Old Values.
112 OPEN cur_old_ref_values;
113 FETCH cur_old_ref_values INTO old_references;
114 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
115 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
116 IGS_GE_MSG_STACK.ADD;
117 CLOSE cur_old_ref_values;
118 APP_EXCEPTION.RAISE_EXCEPTION;
119 RETURN;
120 END IF;
121 CLOSE cur_old_ref_values;
122
123 /** TRUNCATING the following date fields to remove the hours,minutes and seconds attributes from
124 date fields to overcome the lock row and comparison problems w.r.t.2321858 by kkillams **/
125 old_references.enrolled_dt := TRUNC(old_references.enrolled_dt);
126 old_references.discontinued_dt := TRUNC(old_references.discontinued_dt);
127 old_references.rule_waived_dt := TRUNC(old_references.rule_waived_dt);
128 -- Truncating the following Date fields for the bug fix : 2397855
129 old_references.override_outcome_due_dt := TRUNC(old_references.override_outcome_due_dt);
130 old_references.waitlist_dt := old_references.waitlist_dt;
131
132 /*************************************************************************/
133
134 -- Populate New Values.
135 new_references.uoo_id := x_uoo_id;
136 new_references.unit_attempt_status := x_unit_attempt_status;
137 new_references.administrative_unit_status:= x_administrative_unit_status;
138 IF (old_references.unit_attempt_status IS NULL OR (old_references.unit_attempt_status <> new_references.unit_attempt_status))
139 AND new_references.unit_attempt_status = 'ENROLLED' THEN
140 new_references.enrolled_dt := TO_DATE(TO_CHAR(x_enrolled_dt,'DD-MM-YYYY') || ' ' || TO_CHAR(sysdate,'HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS');
141 ELSE
142 new_references.enrolled_dt := x_enrolled_dt;
143 END IF;
144 IF (old_references.unit_attempt_status is null OR (old_references.unit_attempt_status <> new_references.unit_attempt_status))
145 AND new_references.unit_attempt_status IN ('DROPPED','DISCONTIN') THEN
146 new_references.discontinued_dt := TO_DATE(TO_CHAR(x_discontinued_dt,'DD-MM-YYYY') || ' ' || TO_CHAR(sysdate,'HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS');
147 ELSE
148 new_references.discontinued_dt := x_discontinued_dt;
149 END IF;
150
151 new_references.rule_waived_dt := TRUNC(x_rule_waived_dt);
152 new_references.rule_waived_person_id := x_rule_waived_person_id;
153 new_references.no_assessment_ind := x_no_assessment_ind;
154 new_references.sup_unit_cd := x_sup_unit_cd;
155 new_references.sup_version_number := x_sup_version_number;
156 new_references.exam_location_cd := x_exam_location_cd;
157 new_references.alternative_title := x_alternative_title;
158 -- As part of ENCR013 DLD
159 IF p_action = 'INSERT' THEN
160 -- If the user is override the Value then don't populate the default value
161 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);
162 -- Modified as a part of EN317 build.
163 new_references.override_achievable_cp := NVL( IGS_EN_GEN_015.enrp_get_appr_cr_pt(x_person_id,x_uoo_id),x_override_achievable_cp);
164 ELSE
165 new_references.override_enrolled_cp := x_override_enrolled_cp;
166 new_references.override_achievable_cp := x_override_achievable_cp;
167 END IF;
168
169 new_references.override_eftsu := x_override_eftsu;
170 new_references.override_outcome_due_dt := TRUNC(x_override_outcome_due_dt);
171 new_references.override_credit_reason := x_override_credit_reason;
172 new_references.person_id := x_person_id;
173 new_references.course_cd := x_course_cd;
174 new_references.unit_cd := x_unit_cd;
175 new_references.version_number := x_version_number;
176 new_references.cal_type:= x_cal_type;
177 new_references.ci_sequence_number := x_ci_sequence_number;
178 new_references.location_cd := x_location_cd;
179 new_references.unit_class:= x_unit_class;
180 new_references.ci_start_dt := x_ci_start_dt;
181 new_references.ci_end_dt := x_ci_end_dt;
182 new_references.administrative_priority := x_administrative_priority;
183 new_references.waitlist_dt := x_waitlist_dt;
184 new_references.dcnt_reason_cd := x_dcnt_reason_cd;
185 new_references.org_unit_cd := x_org_unit_cd;
186 IF (p_action = 'UPDATE') THEN
187 new_references.creation_date := old_references.creation_date;
188 new_references.created_by := old_references.created_by;
189 ELSE
190 new_references.creation_date := x_creation_date;
191 new_references.created_by := x_created_by;
192 END IF;
193 new_references.last_update_date := x_last_update_date ;
194 new_references.last_updated_by := x_last_updated_by ;
195 new_references.last_update_login := x_last_update_login;
196 new_references.org_id := x_org_id ;
197 new_references.gs_version_number := x_gs_version_number;
198 new_references.enr_method_type := x_enr_method_type ;
199 new_references.failed_unit_rule := x_failed_unit_rule ;
200 new_references.cart := x_cart ;
201 new_references.rsv_seat_ext_id := x_rsv_seat_ext_id ;
202 new_references.grading_schema_code := x_grading_schema_code;
203 new_references.subtitle := x_subtitle;
204 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
205 new_references.session_id := x_session_id;
206 new_references.deg_aud_detail_id := x_deg_aud_detail_id;
207 new_references.student_career_transcript := x_student_career_transcript;
208 new_references.student_career_statistics := x_student_career_statistics;
209 new_references.waitlist_manual_ind := x_waitlist_manual_ind; --Bug ID: 2554109 added by adhawan
210 new_references.attribute_category := x_attribute_category;
211 new_references.attribute1 := x_attribute1;
212 new_references.attribute2 := x_attribute2;
213 new_references.attribute3 := x_attribute3;
214 new_references.attribute4 := x_attribute4;
215 new_references.attribute5 := x_attribute5;
216 new_references.attribute6 := x_attribute6;
217 new_references.attribute7 := x_attribute7;
218 new_references.attribute8 := x_attribute8;
219 new_references.attribute9 := x_attribute9;
220 new_references.attribute10 := x_attribute10;
221 new_references.attribute11 := x_attribute11;
222 new_references.attribute12 := x_attribute12;
223 new_references.attribute13 := x_attribute13;
224 new_references.attribute14 := x_attribute14;
225 new_references.attribute15 := x_attribute15;
226 new_references.attribute16 := x_attribute16;
227 new_references.attribute17 := x_attribute17;
228 new_references.attribute18 := x_attribute18;
229 new_references.attribute19 := x_attribute19;
230 new_references.attribute20 := x_attribute20;
231 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
232 new_references.wlst_priority_weight_num := x_wlst_priority_weight_num;
233 new_references.wlst_preference_weight_num := x_wlst_preference_weight_num;
234 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
235 new_references.core_indicator_code := x_core_indicator_code;
236 new_references.UPD_AUDIT_FLAG := X_UPD_AUDIT_FLAG;
237 new_references.SS_SOURCE_IND := X_SS_SOURCE_IND;
238 END Set_Column_Values;
239
240
241 --For bug 2121602
242 --Checking whether attempting unit is precluded or not
243 --added by kkillams,08-MAY-2002
244 PROCEDURE chk_precluded_unit AS
245 CURSOR c_adv IS
246 SELECT 'x'
247 FROM IGS_AV_STND_UNIT asu
248 WHERE asu.person_id =new_references.person_id AND
249 asu.as_course_cd =new_references.course_cd AND
250 asu.unit_cd =new_references.unit_cd AND
251 asu.version_number =new_references.version_number AND
252 asu.s_adv_stnd_granting_status IN ('APPROVED','GRANTED') AND
253 asu.s_adv_stnd_recognition_type = 'PRECLUSION';
254 v_adv_exists VARCHAR2(1);
255 BEGIN
256 OPEN c_adv;
257 FETCH c_adv INTO v_adv_exists;
258 IF c_adv%FOUND THEN
259 CLOSE c_adv;
260 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CANNOT_ATT_PRE_UNIT');
261 FND_MESSAGE.SET_TOKEN('UNIT',new_references.unit_cd);
262 IGS_GE_MSG_STACK.ADD;
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 END IF;
265 CLOSE c_adv;
266 END chk_precluded_unit;
267
268 --
269 -- For Enhancement Bug 1287292
270 -- Local Procedure added to check enrollment maximum
271 -- Called in Before DML
272 --
273 PROCEDURE chk_enrollment_max
274 AS
275 -- Local variables
276 p_uoo_id NUMBER;
277 p_unit_cd VARCHAR2(10);
278 p_version_number NUMBER(3);
279 l_actual_enr NUMBER;
280 l_maximum_enr NUMBER;
281 l_lvl_unit VARCHAR2(1);
282 -- Unit Section Level Cursor
283 CURSOR usec_enr_max(p_uoo_id NUMBER) IS
284 SELECT NVL(enrollment_maximum,0)
285 FROM igs_ps_usec_lim_wlst
286 WHERE uoo_id = p_uoo_id;
287 -- Unit Level Cursor
288 CURSOR unit_enr_max ( p_unit_cd VARCHAR2,
289 p_version_number NUMBER ) IS
290 /******************Enhancement bug no 1517114 -- NVL from 0 made to 999999***********/
291 SELECT NVL(enrollment_maximum,999999)
292 FROM igs_ps_unit_ver
293 WHERE unit_cd = p_unit_cd AND
294 version_number = p_version_number;
295 /******************Enhancement bug no 1517114 -- NVL from 0 made to 999999***********/
296 --
297 -- Cursor to count the actual enrollment - Unit Section Level
298 --
299 -- Bug 19055975
300 -- previous the cursor was doing a select count(*) from igs_en_su_attempt
301 -- now changed it to use the enrollment actual from the unit section table
302 CURSOR usec_enr_act(p_uoo_id NUMBER) IS
303 SELECT ENROLLMENT_ACTUAL
304 FROM igs_ps_unit_ofr_opt
305 WHERE uoo_id = p_uoo_id;
306 BEGIN
307 -- This procedure checks the maximum enrollment for the unit section and
308 -- Raises a error message.
309 -- When user attempts to confirm a unit attempt for which the maximum enrollment
310 -- has been reached, deliver an error message: "Can not confirm enrollment in
311 -- unit attempt UNIT CODE and VERSION NUMBER. Maximum enrollment has
312 -- been reached."
313 -- The enrollment maximum IS SET up IN unit details OR modified IN
314 -- unit section details. The system looks first TO unit section details, AND IF
315 -- no VALUES are stored AT that LEVEL, THEN looks TO unit details.
316 --
317 -- Check only if the status is ENROLLED
318 IF new_references.unit_attempt_status = 'ENROLLED' AND
319 new_references.unit_attempt_status <> NVL(old_references.unit_attempt_status,'Unknown') THEN
320 --Intialize the variables from the new references.
321 p_uoo_id := new_references.uoo_id;
322 p_unit_cd := new_references.unit_cd;
323 p_version_number := new_references.version_number;
324 -- 1.Get the maximum enroolment values
325 l_maximum_enr := 0;
326 -- Check in unit section level
327 OPEN usec_enr_max(p_uoo_id);
328 FETCH usec_enr_max INTO l_maximum_enr;
329 CLOSE usec_enr_max;
330 -- Check in unit level
331 IF l_maximum_enr = 0 THEN
332 OPEN unit_enr_max(p_unit_cd,p_version_number);
333 FETCH unit_enr_max INTO l_maximum_enr;
334 CLOSE unit_enr_max;
335 END IF;
336
337 -- 2.Get the count of actual enrollment
338 OPEN usec_enr_act(p_uoo_id);
339 FETCH usec_enr_act INTO l_actual_enr;
340 CLOSE usec_enr_act;
341 -- Check and raise the Error Message
342 IF l_actual_enr >= l_maximum_enr THEN
343 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MAX_ENR_REACHED');
344 FND_MESSAGE.SET_TOKEN('UNIT_CODE',p_unit_cd );
345 APP_EXCEPTION.RAISE_EXCEPTION;
346 END IF;
347 END IF;
348 -- 3.End of the Procedure
349 END chk_enrollment_max;
350
351 -- Trigger description :-
352 -- "OSS_TST".trg_sua_br_iud
353 -- BEFORE INSERT OR DELETE OR UPDATE
354 -- ON IGS_EN_SU_ATTEMPT_ALL
355 -- FOR EACH ROW
356 PROCEDURE BeforeRowInsertUpdateDelete1(
357 p_inserting IN BOOLEAN ,
358 p_updating IN BOOLEAN ,
359 p_deleting IN BOOLEAN
360 ) AS
361 /* WHO WHEN WHAT
362 pradhakr 20-Jan-2003 Added a parameter no_assessment_ind to the procedue call IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp
363 as part of ENCR26 build.
364 prraj 22-Oct-2002 Added condition to check new_references.no_assessment_ind <> 'Y'
365 | in the if clause of the call to IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp for Audit Build(Bug# )
366 pmarada 02-sep-2002 bug 2526021, Assigning the waitlist positions to the waitlisted students, and clearing
367 the ADMINISTRATIVE_PRIORITY value to null when unit attempt status is changed from waitlist to any other status
368 ayedubat 02-JUL-2002 Removed the Variation Window Validation added in the previous version and moved to IGSEN04B.pls
369 for the same bug fix:2423605
370 ayedubat 26-JUN-2002 Added a new validation to check the Variation Window Cutoff Date for the bug Fix:2423605
371 ayedubat 30-MAY-2002 Added a new parameter,p_message_name to the Function:Enrp_Get_Rec_Window
372 call and dsiplaying the returning message for the bug fix:2337161.
373 ayedubat 19-APR-2002 Changed the message names in the app_exception call from 'IGS_GE_RECORD_ALREADY_EXISTS'
374 to 'IGS_EN_SUA_NOTENR_RECENR_WIN' while validating the record cutoff date and in the remaining
375 places to the message returned by the calling function as part of the bug fix:2332137
376 Sudhir 23-MAY-2002 To show token for the new message in calling the procedure IGS_EN_VAL_SUA.enrp_val_discont_aus
377 svanukur 29-APR-03 Passing uoo_id to IGS_EN_GEN_010.ENRP_INS_SUAO_DISCON as part of MUS build # 2829262
378 myoganat 21-MAY-03 Set override enrolled and override achievable CP to zero for a unit section attempt that is being audited and
379 when the record is an audit attempt record the procedure IGS_EN_VAL_SUA.ENRP_VAL_SUA_OVRD_CP will not get called.
380 - as part of the build ENCR032 - Audit Attempt Credit Points. Bug #2855870
381 kkillams 25-07-2003 New cursor c_unit_opt added to lock the unit oferring option table for a uoo_id, to avoid duplicate
382 administrative priorities w.r.t. 2865921.
383 ptandon 03-SEP-2003 Modified the logic as per Waitlist Enhancements Build - Bug# 3052426
384
385 ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
386 add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
387
388 */
389 --Getting the max administrative priority value, bug 2526021
390 CURSOR c_admin_priority (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
391 SELECT max(ADMINISTRATIVE_PRIORITY) FROM igs_en_su_attempt
392 WHERE uoo_id = cp_uoo_id;
393
394 CURSOR c_unit_opt (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
395 SELECT 1 FROM igs_ps_unit_ofr_opt
396 WHERE uoo_id = cp_uoo_id FOR UPDATE;
397 CURSOR cur_load(cp_teach_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
398 cp_teach_ci_sequence_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
399 SELECT load_cal_type, load_ci_sequence_number FROM igs_ca_teach_to_load_v
400 WHERE teach_cal_type = cp_teach_cal_type
401 AND teach_ci_sequence_number = cp_teach_ci_sequence_num
402 ORDER BY load_start_dt ASC;
403
404 CURSOR cur_sub_uoo IS
405 SELECT sup.unit_cd, sup.version_number
406 FROM igs_ps_unit_ofr_opt sub, igs_ps_unit_ofr_opt sup
407 WHERE sub.uoo_id = new_references.uoo_id
408 AND sub.relation_type = 'SUBORDINATE'
409 AND sub.sup_uoo_id = sup.uoo_id;
410
411 CURSOR cur_placement_unit IS
412 SELECT 1
413 FROM igs_ps_unit_ver
414 WHERE unit_cd = new_references.unit_cd
415 AND version_number = new_references.version_number
416 AND practical_ind = 'Y';
417 l_sup_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
418 l_sup_ver igs_ps_unit_ofr_opt.version_number%TYPE;
419 l_placement_unit NUMBER;
420
421 l_admin_priority igs_en_su_attempt.administrative_priority%TYPE;
422 v_message_name VARCHAR2(30);
423 v_message_token VARCHAR2(2000);
424 v_rule_waived_person_id IGS_EN_SU_ATTEMPT_ALL.rule_waived_person_id%TYPE;
425 v_effective_dt DATE;
426 v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427 v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428 v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
429 v_old_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE;
430 v_old_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE;
431 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
432 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
433 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
434 p_duplicate_course_cd VARCHAR2(30);
435 l_dummy NUMBER;
436 l_pri_weight NUMBER;
437 l_pref_weight NUMBER;
438 l_dummy_value NUMBER;
439 BEGIN
440
441
442 --For bug No : 2121602, by kkillams
443 --Checking the unit whether unit is precluded or not in advance standing
444 IF p_inserting THEN
445 chk_precluded_unit;
446 -- set override enrolled and override achievable CP to zero for a unit section
447 --attempt that is being audited
448 IF (new_references.no_assessment_ind = 'Y') THEN
449 new_references.override_enrolled_cp := 0;
450 new_references.override_achievable_cp := 0;
451 END IF;
452
453 END IF;
454
455 IF p_updating THEN
456 --set override enrolled and override achievable CP to zero
457 --for a unit section attempt that is
458 --being audited
459 IF old_references.no_assessment_ind = 'N' AND new_references.no_assessment_ind = 'Y' THEN
460 new_references.override_enrolled_cp := 0;
461 new_references.override_achievable_cp := 0;
462 END IF;
463 END IF;
464
465 -- If trigger has not been disabled, perform required processing
466 --IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_SU_ATTEMPT_ALL') THEN
467 -- Set audit details and discontinuation details
468 IF p_inserting THEN
469 -- Validate that the IGS_PS_UNIT offering option is being offered.
470 IF IGS_EN_VAL_SUA.enrp_val_sua_uoo(
471 new_references.unit_cd,
472 new_references.version_number,
473 new_references.cal_type,
474 new_references.ci_sequence_number,
475 new_references.location_cd,
476 new_references.unit_class,
477 v_message_name,
478 'N' ) = FALSE THEN
479 IF (new_references.unit_attempt_status IN (cst_duplicate, cst_discontin, cst_completed)) THEN
480 -- Bypass IGS_PS_UNIT version must be active and offered error for duplicates.
481 NULL;
482 ELSE
483 FND_MESSAGE.SET_NAME('IGS',v_message_name);
484 IGS_GE_MSG_STACK.ADD;
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487 END IF;
488
489 END IF;
490 IF p_inserting OR p_updating THEN
491
492 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
493
494 IF p_inserting THEN
495
496 -- Validate that discontinued date is not entered when p_inserting
497 -- This is temporary code
498 IF new_references.discontinued_dt IS NOT NULL AND
499 (new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin)) THEN
500 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_OPER');
501 IGS_GE_MSG_STACK.ADD;
502 APP_EXCEPTION.RAISE_EXCEPTION;
503 END IF;
504
505 END IF;
506
507
508 IF p_updating THEN
509
510 -- Validate that update is allowed
511 IF IGS_EN_VAL_SUA.enrp_val_sua_update(
512 new_references.person_id,
513 new_references.course_cd,
514 new_references.unit_cd,
515 new_references.cal_type,
516 new_references.ci_sequence_number,
517 new_references.unit_attempt_status,
518 new_references.version_number,
519 new_references.location_cd,
520 new_references.unit_class,
521 TRUNC(new_references.enrolled_dt),
522 TRUNC(new_references.discontinued_dt),
523 new_references.administrative_unit_status,
524 TRUNC(new_references.rule_waived_dt),
525 new_references.rule_waived_person_id,
526 new_references.no_assessment_ind,
527 new_references.sup_unit_cd,
528 new_references.sup_version_number,
529 new_references.exam_location_cd,
530 old_references.version_number,
531 old_references.location_cd,
532 old_references.unit_class,
533 TRUNC(old_references.enrolled_dt),
534 TRUNC(old_references.discontinued_dt),
535 old_references.administrative_unit_status,
536 TRUNC(old_references.rule_waived_dt),
537 old_references.rule_waived_person_id,
538 old_references.no_assessment_ind,
539 old_references.sup_unit_cd,
540 old_references.sup_version_number,
541 old_references.exam_location_cd,
542 v_message_name,
543 new_references.uoo_id) = FALSE THEN
544 FND_MESSAGE.SET_NAME('IGS',v_message_name);
545 IGS_GE_MSG_STACK.ADD;
546 APP_EXCEPTION.RAISE_EXCEPTION;
547 END IF;
548 END IF;
549
550 -- Validate that insert or update is in the variation
551 -- of enrolments window
552 IF p_inserting THEN
553 IF new_references.enrolled_dt IS NULL THEN
554 v_effective_dt := SYSDATE;
555 ELSE
556 v_effective_dt := new_references.enrolled_dt;
557 END IF;
558 ELSE
559 v_effective_dt := SYSDATE;
560 END IF;
561 -- Set IGS_PS_UNIT_OFR_OPT key.
562 IGS_PS_GEN_006.CRSP_GET_UOO_KEY (
563 new_references.unit_cd,
564 new_references.version_number,
565 new_references.cal_type,
566 new_references.ci_sequence_number,
567 new_references.location_cd,
568 new_references.UNIT_CLASS,
569 new_references.uoo_id);
570 -- Validate enrolled date
571
572 IF IGS_EN_VAL_SUA.enrp_val_sua_enr_dt(
573 new_references.person_id,
574 new_references.course_cd,
575 new_references.enrolled_dt,
576 new_references.unit_attempt_status,
577 new_references.ci_end_dt,
578 '',
579 v_message_name , 'N' ) = FALSE THEN
580 FND_MESSAGE.SET_NAME('IGS',v_message_name);
581 IGS_GE_MSG_STACK.ADD;
582 APP_EXCEPTION.RAISE_EXCEPTION;
583 END IF;
584
585 -- Validate that student IGS_PS_UNIT attempt can only be confirmed or
586 -- unconfirmed in the record enrolments timeframe.
587 IF (new_references.enrolled_dt IS NULL AND
588 old_references.enrolled_dt IS NOT NULL AND
589 old_references.unit_attempt_status <> 'DROPPED') OR
590 (new_references.enrolled_dt IS NOT NULL AND
591 old_references.enrolled_dt IS NULL) THEN
592 IF new_references.enrolled_dt IS NULL THEN
593 v_effective_dt := SYSDATE;
594 ELSE
595 v_effective_dt := new_references.enrolled_dt;
596 END IF;
597 IF IGS_EN_GEN_004.ENRP_GET_REC_WINDOW(
598 new_references.CAL_TYPE,
599 new_references.ci_sequence_number,
600 v_effective_dt,
601 new_references.uoo_id,
602 v_message_name) = FALSE THEN
603 FND_MESSAGE.SET_NAME('IGS',v_message_name);
604 IGS_GE_MSG_STACK.ADD;
605 APP_EXCEPTION.RAISE_EXCEPTION;
606 END IF;
607 END IF;
608 -- Validate IGS_RU_RULE waived date
609
610 IF IGS_EN_VAL_SUA.enrp_val_sua_rule_wv(
611 new_references.rule_waived_dt,
612 new_references.enrolled_dt,
613 v_rule_waived_person_id,
614 v_message_name) = FALSE THEN
615 FND_MESSAGE.SET_NAME('IGS',v_message_name);
616 IGS_GE_MSG_STACK.ADD;
617 APP_EXCEPTION.RAISE_EXCEPTION;
618 ELSE
619 -- Set IGS_RU_RULE waived IGS_PE_PERSON id from oracle username
620 IF new_references.rule_waived_person_id IS NULL THEN
621 new_references.rule_waived_person_id := v_rule_waived_person_id;
622 END IF;
623 END IF;
624
625
626 -- Validate discontinued date
627 IF (p_updating AND
628 ( new_references.discontinued_dt IS NULL AND
629 old_references.discontinued_dt IS NOT NULL) OR
630 ( new_references.discontinued_dt IS NOT NULL AND
631 TRUNC(new_references.discontinued_dt) <> TRUNC(old_references.discontinued_dt))) THEN
632 IF IGS_EN_VAL_SUA.enrp_val_sua_discont(
633 new_references.person_id,
634 new_references.course_cd,
635 new_references.unit_cd,
636 new_references.version_number,
637 new_references.ci_start_dt,
638 new_references.enrolled_dt,
639 new_references.administrative_unit_status,
640 new_references.unit_attempt_status,
641 new_references.discontinued_dt,
642 v_message_name,'N') = FALSE THEN
643 FND_MESSAGE.SET_NAME('IGS',v_message_name);
644 IGS_GE_MSG_STACK.ADD;
645 APP_EXCEPTION.RAISE_EXCEPTION;
646
647 END IF;
648 END IF;
649 -- Validate administrative IGS_PS_UNIT status
650 --
651 IF p_updating THEN
652 IF (new_references.administrative_unit_status IS NULL AND
653 ((new_references.discontinued_dt IS NOT NULL) OR
654 (old_references.administrative_unit_status IS NOT NULL))) OR
655 (new_references.administrative_unit_status IS NOT NULL AND
656 (old_references.administrative_unit_status IS NULL OR
657 (old_references.administrative_unit_status<> new_references.administrative_unit_status)))
658 THEN
659 IF IGS_EN_VAL_SUA.enrp_val_discont_aus(
660 new_references.administrative_unit_status,
661 new_references.discontinued_dt,
662 new_references.cal_type,
663 new_references.ci_sequence_number,
664 v_message_name,
665 new_references.uoo_id,
666 v_message_token ,
667 'N' ) = FALSE THEN
668 FND_MESSAGE.SET_NAME('IGS',v_message_name);
669 IF v_message_name = 'IGS_SS_EN_INVLD_ADMIN_UNITST' THEN
670 FND_MESSAGE.SET_TOKEN('LIST',v_message_token);
671 END IF;
672 IGS_GE_MSG_STACK.ADD;
673 APP_EXCEPTION.RAISE_EXCEPTION;
674 END IF;
675 END IF;
676 END IF;
677 -- Validate the teaching period against any intermission. This is performed
678 -- on insert and on removal of the discontinuation date.
679 IF p_inserting OR
680 (p_updating AND old_references.discontinued_dt IS NOT NULL AND
681 new_references.discontinued_dt IS NULL) THEN
682 IF IGS_EN_VAL_SUA.enrp_val_sua_intrmt(
683 new_references.person_id,
684 new_references.course_cd,
685 new_references.cal_type,
686 new_references.ci_sequence_number,
687 v_message_name) = FALSE THEN
688 FND_MESSAGE.SET_NAME('IGS',v_message_name);
689 IGS_GE_MSG_STACK.ADD;
690 APP_EXCEPTION.RAISE_EXCEPTION;
691 END IF;
692 END IF;
693 -- validate research units being inserted or confirmed.
694 IF(p_inserting OR
695 (p_updating AND
696 ((old_references.discontinued_dt IS NOT NULL AND
697 new_references.discontinued_dt IS NULL) OR
698 (old_references.enrolled_dt IS NULL AND
699 new_references.enrolled_dt IS NOT NULL) OR
700 (old_references.rule_waived_dt IS NULL AND
701 old_references.unit_attempt_status = 'INVALID' AND
702 new_references.rule_waived_dt IS NOT NULL)))) AND
703 (new_references.enrolled_dt IS NOT NULL AND new_references.discontinued_dt IS NULL) THEN
704 IF IGS_EN_VAL_SUA.resp_val_sua_cnfrm(
705 new_references.person_id,
706 new_references.course_cd,
707 new_references.unit_cd,
708 new_references.version_number,
709 new_references.cal_type,
710 new_references.ci_sequence_number,
711 v_message_name ,
712 'N' ) = FALSE THEN
713 FND_MESSAGE.SET_NAME('IGS',v_message_name);
714 IGS_GE_MSG_STACK.ADD;
715 APP_EXCEPTION.RAISE_EXCEPTION;
716 END IF;
717 END IF;
718
719 END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
720
721 -- Validate that advanced standing does not already exist
722 IF p_inserting OR
723 (p_updating AND
724 (new_references.enrolled_dt IS NOT NULL AND
725 old_references.enrolled_dt IS NULL) OR
726 (new_references.discontinued_dt IS NULL AND
727 old_references.discontinued_dt IS NOT NULL) OR
728 (new_references.rule_waived_dt IS NOT NULL AND
729 old_references.rule_waived_dt IS NULL AND
730 old_references.unit_attempt_status = 'INVALID')) THEN
731
732 IF IGS_EN_VAL_SUA.enrp_val_sua_advstnd(
733 new_references.person_id,
734 new_references.course_cd,
735 '',
736 new_references.unit_cd,
737 new_references.version_number,
738 v_message_name,'N' ) = FALSE THEN
739 FND_MESSAGE.SET_NAME('IGS',v_message_name);
740 IGS_GE_MSG_STACK.ADD;
741 APP_EXCEPTION.RAISE_EXCEPTION;
742 END IF;
743 END IF;
744
745 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
746
747 -- Set IGS_PS_UNIT attempt status
748 IF new_references.unit_attempt_status IS NULL THEN
749 v_unit_attempt_status := 'Unknown';
750 ELSE
751 v_unit_attempt_status := new_references.unit_attempt_status;
752 END IF;
753 --
754 -- Added as per the Bug# 2335455.
755 -- If the Unit Attempt Status is changing from 'Waitlist' to
756 -- any other status then make the 'Waitlisted date' as NULL.
757 --
758
759 -- Modified as per Bug# 3052426 - Waitlists Enhancements Build
760 -- Modified the If condition and assigned NULL to WLST_PRIORITY_WEIGHT_NUM and
761 -- WLST_PREFERENCE_WEIGHT_NUM columns also if Unit Attempt Status is changed from 'Waitlisted' - ptandon
762 IF p_updating THEN
763 IF NVL(old_references.unit_attempt_status,'Unknown') = 'WAITLISTED' AND
764 NVL(NEW_REFERENCES.unit_attempt_status,'Unknown') IN ('DROPPED','ENROLLED')
765 THEN
766 NEW_REFERENCES.waitlist_dt := NULL;
767 NEW_REFERENCES.ADMINISTRATIVE_PRIORITY := NULL; -- clearing waitlist position, pmarada, bug 2526021
768 NEW_REFERENCES.WAITLIST_MANUAL_IND :='N'; --Bug ID 2554109 adhawan
769 NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM := NULL;
770 NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM := NULL;
771 END IF;
772 END IF;
773 --
774 -- End of the new code - added as per the Bug# 2335455.
775 --
776 -- Assigning waitlist positions for waitlisted students. bug 2526021, pmarada
777
778 -- Modified as per Bug# 3052426 - Waitlists Enhancements Build
779 -- Modified so that if waitlist priorities/preferences exist then system should calculate
780 -- the waitlist position based on the priority/preference weightage otherwise student would
781 -- be put at the end of the waitlist queue (FIFO) - ptandon
782
783 IF p_inserting OR p_updating THEN
784 IF (new_references.UNIT_ATTEMPT_STATUS = 'WAITLISTED'
785 AND NVL(old_references.unit_attempt_status,'UNCONFIRM') <> new_references.unit_attempt_status
786 AND new_references.waitlist_dt IS NOT NULL
787 AND new_references.administrative_priority IS NULL
788 AND new_references.wlst_priority_weight_num IS NULL) THEN
789 Igs_En_Wlst_Gen_Proc.enrp_wlst_pri_pref_calc(new_references.person_id,
790 new_references.course_cd,
791 new_references.uoo_id,
792 l_pri_weight,
793 l_pref_weight);
794
795 IF l_pri_weight IS NOT NULL AND l_pref_weight IS NOT NULL THEN
796 NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM := l_pri_weight;
797 NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM := l_pref_weight;
798 ELSE
799 OPEN c_unit_opt(new_references.uoo_id); -- Locking the unit offerring option table for the given uoo_id to avoid duplicate administrative priorities.
800 FETCH c_unit_opt INTO l_dummy;
801 OPEN c_admin_priority(new_references.uoo_id);
802 FETCH c_admin_priority INTO l_admin_priority;
803 CLOSE c_admin_priority;
804 CLOSE c_unit_opt;
805 NEW_REFERENCES.ADMINISTRATIVE_PRIORITY := NVL(l_admin_priority,0) + 1;
806 NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM := NULL;
807 NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM := NULL;
808 END IF;
809 END IF;
810 END IF;
811 --End of the code added as part of bug 2526021
812
813 new_references.unit_attempt_status := IGS_EN_GEN_007.ENRP_GET_SUA_STATUS(
814 new_references.person_id,
815 new_references.course_cd,
816 new_references.unit_cd,
817 new_references.version_number,
818 new_references.cal_type,
819 new_references.ci_sequence_number,
820 v_unit_attempt_status,
821 new_references.enrolled_dt,
822 new_references.rule_waived_dt,
823 new_references.discontinued_dt,
824 new_references.waitlist_dt,
825 new_references.uoo_id); -- Added waitlist_dt parameter to call IGS_EN_GEN_007.ENRP_GET_SUA_STATUS.
826 -- This is as per the Bug# 2335455.
827 IF p_inserting THEN
828 -- Validate against IGS_PS_COURSE attempt status
829 IF IGS_EN_VAL_SUA.enrp_val_sua_insert(
830 new_references.person_id,
831 new_references.course_cd,
832 new_references.unit_attempt_status,
833 v_message_name) = FALSE THEN
834 FND_MESSAGE.SET_NAME('IGS',v_message_name);
835 IGS_GE_MSG_STACK.ADD;
836 APP_EXCEPTION.RAISE_EXCEPTION;
837 END IF;
838 -- Validate that teaching period is not prior to commencement date
839 -- with the exception of DUPLICATE IGS_PS_UNIT attempts
840 END IF;
841
842 END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
843
844 IF p_inserting THEN
845 IF IGS_EN_VAL_SUA.enrp_val_sua_ci(
846 new_references.person_id,
847 new_references.course_cd,
848 new_references.cal_type,
849 new_references.ci_sequence_number,
850 new_references.unit_attempt_status,
851 NULL,
852 'T', -- Validation called from trigger
853 v_message_name) = FALSE THEN
854 FND_MESSAGE.SET_NAME('IGS',v_message_name);
855 IGS_GE_MSG_STACK.ADD;
856 APP_EXCEPTION.RAISE_EXCEPTION;
857 END IF;
858 END IF;
859
860 -- Save details when IGS_PS_UNIT attempt status is set to ENROLLED
861 -- to validate for duplicates across all student IGS_PS_UNIT attempts
862 IF (p_inserting OR
863 (p_updating AND
864 new_references.unit_attempt_status <> old_references.unit_attempt_status)) THEN
865 IF IGS_EN_VAL_SUA.enrp_val_sua_dupl(
866 new_references.person_id,
867 new_references.course_cd,
868 new_references.unit_cd,
869 new_references.version_number,
870 new_references.cal_type,
871 new_references.ci_sequence_number,
872 new_references.unit_attempt_status,
873 --The column duplicate_course_cd has been changed to course_cd
874 p_duplicate_course_cd,
875 v_message_name,
876 new_references.uoo_id) = FALSE THEN
877 FND_MESSAGE.SET_NAME('IGS',v_message_name);
878 IGS_GE_MSG_STACK.ADD;
879 APP_EXCEPTION.RAISE_EXCEPTION;
880 END IF;
881 END IF;
882
883
884 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
885 -- Save rowid and old discontinuation date
886 -- if discontinuation details change
887
888 IF p_updating THEN
889 IF (old_references.discontinued_dt IS NOT NULL AND
890 new_references.discontinued_dt IS NULL) OR -- clear discontinuation details
891 (new_references.discontinued_dt IS NOT NULL AND
892 old_references.discontinued_dt IS NULL) OR -- add discontinuation details
893 ((old_references.discontinued_dt IS NOT NULL AND
894 new_references.discontinued_dt IS NOT NULL) AND
895 (TRUNC(old_references.discontinued_dt) <> TRUNC(new_references.discontinued_dt))) THEN
896 IF old_references.discontinued_dt IS NOT NULL THEN
897 IGS_EN_GEN_001.ENRP_DEL_SUAO_DISCON(
898 old_references.person_id,
899 old_references.course_cd,
900 old_references.unit_cd,
901 old_references.cal_type,
902 old_references.ci_sequence_number,
903 old_references.discontinued_dt,
904 old_references.uoo_id);
905 END IF;
906 -- Insert student unit attempt outcome
907 IF new_references.discontinued_dt IS NOT NULL THEN
908 IF IGS_EN_GEN_010.ENRP_INS_SUAO_DISCON(
909 new_references.person_id,
910 new_references.course_cd,
911 new_references.unit_cd,
912 new_references.cal_type,
913 new_references.ci_sequence_number,
914 new_references.ci_start_dt,
915 new_references.ci_end_dt,
916 new_references.discontinued_dt,
917 new_references.administrative_unit_status,
918 v_message_name,
919 new_references.uoo_id) = FALSE THEN
920 FND_MESSAGE.SET_NAME('IGS',v_message_name);
921 IGS_GE_MSG_STACK.ADD;
922 APP_EXCEPTION.RAISE_EXCEPTION;
923 END IF;
924 END IF;
925 ELSE
926 IF (old_references.administrative_unit_status IS NOT NULL AND
927 new_references.administrative_unit_status IS NULL) OR
928 (new_references.administrative_unit_status IS NOT NULL AND
929 old_references.administrative_unit_status IS NULL) OR
930 ((old_references.administrative_unit_status IS NOT NULL AND
931 new_references.administrative_unit_status IS NOT NULL) AND
932 (old_references.administrative_unit_status <> new_references.administrative_unit_status))
933 THEN
934 IF old_references.discontinued_dt IS NOT NULL THEN
935 IGS_EN_GEN_001.ENRP_DEL_SUAO_DISCON(
936 old_references.person_id,
937 old_references.course_cd,
938 old_references.unit_cd,
939 old_references.cal_type,
940 old_references.ci_sequence_number,
941 old_references.discontinued_dt,
942 old_references.uoo_id);
943 END IF;
944 -- Insert student unit attempt outcome
945 IF new_references.discontinued_dt IS NOT NULL THEN
946 IF IGS_EN_GEN_010.ENRP_INS_SUAO_DISCON(
947 new_references.person_id,
948 new_references.course_cd,
949 new_references.unit_cd,
950 new_references.cal_type,
951 new_references.ci_sequence_number,
952 new_references.ci_start_dt,
953 new_references.ci_end_dt,
954 new_references.discontinued_dt,
955 new_references.administrative_unit_status,
956 v_message_name,
957 new_references.uoo_id) = FALSE THEN
958 FND_MESSAGE.SET_NAME('IGS',v_message_name);
959 IGS_GE_MSG_STACK.ADD;
960 APP_EXCEPTION.RAISE_EXCEPTION;
961 END IF;
962 END IF;
963 END IF;
964 END IF;
965 END IF;
966
967 -- Validate alternative IGS_PE_TITLE
968 IF (p_inserting AND
969 new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin,cst_completed)) OR
970 (p_updating AND
971 ((new_references.alternative_title IS NULL AND
972 old_references.alternative_title IS NOT NULL) OR
973 (new_references.alternative_title IS NOT NULL AND
974 old_references.alternative_title IS NULL))) THEN
975 IF IGS_EN_VAL_SUA.enrp_val_sua_alt_ttl(
976 new_references.unit_cd,
977 new_references.version_number,
978 new_references.alternative_title,
979 v_message_name) = FALSE THEN
980 FND_MESSAGE.SET_NAME('IGS',v_message_name);
981 IGS_GE_MSG_STACK.ADD;
982 APP_EXCEPTION.RAISE_EXCEPTION;
983 END IF;
984 END IF;
985 END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
986
987 -- Validate override enrolled and achievable credit points
988 IF new_references.no_assessment_ind <> 'Y' THEN
989 --skip validation of override enrolled and achievable credit points for audited record
990
991 IF (p_inserting AND
992 new_references.unit_attempt_status NOT IN (cst_duplicate,cst_discontin,cst_completed) AND
993 new_references.override_enrolled_cp IS NULL) OR
994 (p_updating AND
995 (new_references.override_enrolled_cp IS NULL AND
996 old_references.override_enrolled_cp IS NOT NULL) OR
997 (new_references.override_enrolled_cp IS NOT NULL AND
998 (old_references.override_enrolled_cp IS NULL OR
999 old_references.override_enrolled_cp <> new_references.override_enrolled_cp)) OR
1000 (((new_references.override_achievable_cp IS NULL AND
1001 old_references.override_achievable_cp IS NOT NULL) OR
1002 (new_references.override_achievable_cp IS NOT NULL AND
1003 (old_references.override_achievable_cp IS NULL OR
1004 old_references.override_achievable_cp <> new_references.override_achievable_cp)))) OR
1005 (new_references.override_eftsu IS NULL AND
1006 old_references.override_eftsu IS NOT NULL) OR
1007 (new_references.override_eftsu IS NOT NULL AND
1008 (old_references.override_eftsu IS NULL OR
1009 old_references.override_eftsu <> new_references.override_eftsu)))
1010 THEN
1011 IF IGS_EN_GEN_015.enrp_get_appr_cr_pt(new_references.person_id,new_references.uoo_id) IS NULL AND
1012 IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp(
1013 new_references.unit_cd,
1014 new_references.version_number,
1015 new_references.override_enrolled_cp,
1016 new_references.override_achievable_cp,
1017 new_references.override_eftsu,
1018 v_message_name,
1019 new_references.uoo_id, --New parameter uoo_id is added w.r.t. 2375757 by kkillams
1020 new_references.no_assessment_ind) = FALSE THEN
1021 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1022 IGS_GE_MSG_STACK.ADD;
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024 END IF;
1025 END IF;
1026 END IF; -- IF new_references.no_assessment_ind <> 'Y' THEN
1027
1028 -- Validate override credit reason
1029 IF IGS_EN_VAL_SUA.enrp_val_sua_cp_rsn(
1030 new_references.override_enrolled_cp,
1031 new_references.override_achievable_cp,
1032 new_references.override_credit_reason,
1033 v_message_name) = FALSE THEN
1034 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1035 IGS_GE_MSG_STACK.ADD;
1036 APP_EXCEPTION.RAISE_EXCEPTION;
1037 END IF;
1038
1039 --ading the following validations as part of placement build to populate the
1040 --sup unit cd and sup version number if context unit is a subordinate unit
1041
1042 IF p_inserting OR p_updating THEN
1043
1044 OPEN cur_sub_uoo ;
1045 FETCH cur_sub_uoo INTO l_sup_unit_cd, l_sup_ver;
1046 IF cur_sub_uoo%FOUND THEN
1047 new_references.sup_unit_cd := l_sup_unit_cd;
1048 new_references.sup_version_number := l_sup_ver;
1049 END IF;
1050 CLOSE cur_sub_uoo;
1051
1052 END IF;
1053
1054 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
1055
1056 -- If required add a todo entry to forced the re-checking of the IGS_PS_UNIT IGS_RU_RULEs
1057 -- for the student. This is done in insert of a new ENROLLED/INVALID IGS_PS_UNIT
1058 -- attempt, on the confirmation of a IGS_PS_UNIT attempt or on lifting of a
1059 -- discontinuation of a IGS_PS_UNIT attempt.
1060 IF new_references.unit_attempt_status = 'ENROLLED' OR
1061 new_references.unit_attempt_status = 'INVALID' THEN
1062 IF p_inserting OR
1063 (p_updating AND
1064 ((old_references.enrolled_dt IS NULL AND
1065 new_references.enrolled_dt IS NOT NULL) OR
1066 (old_references.enrolled_dt IS NOT NULL AND
1067 new_references.enrolled_dt IS NULL) OR
1068 (old_references.discontinued_dt IS NOT NULL AND
1069 new_references.discontinued_dt IS NULL) OR
1070 (old_references.discontinued_dt IS NULL AND
1071 new_references.discontinued_dt IS NOT NULL) OR
1072 (new_references.rule_waived_dt IS NOT NULL AND
1073 old_references.rule_waived_dt IS NULL) OR
1074 (old_references.rule_waived_dt IS NOT NULL AND
1075 new_references.rule_waived_dt IS NULL))) THEN
1076 v_return_val := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(new_references.person_id,
1077 'UNIT-RULES',
1078 NULL,
1079 'Y');
1080 END IF;
1081 END IF;
1082 -- If a IGS_PS_UNIT attempt was unconfirmed from being enrolled, add a todo entry
1083 -- to force a re-checking of the students IGS_PS_UNIT rules.
1084 IF p_updating AND
1085 new_references.unit_attempt_status = 'UNCONFIRM' AND
1086 old_references.unit_attempt_status = 'ENROLLED' THEN
1087 v_return_val := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(new_references.person_id,
1088 'UNIT-RULES',
1089 NULL,
1090 'Y');
1091 END IF;
1092 -- If required, add a todo entry to forced the maintenance of default
1093 -- stdnt_unit_atmpt_ass_items for the student.
1094 -- The todo entry is created for students that have:
1095 -- 1. have just enrolled
1096 -- 2. had their enrolment status changed
1097 -- 3. had their IGS_AD_LOCATION and class details changed
1098 IF (p_inserting AND
1099 new_references.unit_attempt_status = 'ENROLLED') OR
1100 (p_updating AND
1101 ((old_references.unit_attempt_status <>
1102 new_references.unit_attempt_status) OR
1103 (old_references.location_cd <>
1104 new_references.location_cd) OR
1105 (old_references.unit_class<>
1106 new_references.unit_class))) THEN
1107 IF p_inserting THEN
1108 v_old_unit_attempt_status := NULL;
1109 v_old_location_cd := NULL;
1110 v_old_unit_class := NULL;
1111 ELSE
1112 v_old_unit_attempt_status := old_references.unit_attempt_status;
1113 v_old_location_cd := old_references.location_cd;
1114 v_old_unit_class := old_references.unit_class;
1115 END IF;
1116 IGS_AS_GEN_007.ASSP_INS_SUAAI_TODO(
1117 new_references.person_id,
1118 new_references.course_cd,
1119 new_references.unit_cd,
1120 new_references.cal_type,
1121 new_references.ci_sequence_number,
1122 v_old_unit_attempt_status,
1123 new_references.unit_attempt_status,
1124 v_old_location_cd,
1125 new_references.location_cd,
1126 v_old_unit_class,
1127 new_references.unit_class,
1128 new_references.uoo_id);
1129 END IF;
1130
1131 IF p_updating THEN
1132 IF (old_references.unit_attempt_status <> new_references.unit_attempt_status) AND
1133 (new_references.unit_attempt_status IN ('DROPPED', 'DISCONTIN')) THEN
1134 l_old_cp := NULL;
1135 l_load_cal_type := NULL;
1136 l_load_seq_num := NULL;
1137 OPEN cur_load(new_references.cal_type, new_references.ci_sequence_number);
1138 FETCH cur_load INTO l_load_cal_type, l_load_seq_num;
1139 IF cur_load%FOUND THEN
1140 IGS_EN_PRC_LOAD.enrp_get_prg_eftsu_cp(
1141 p_person_id =>new_references.person_id,
1142 p_course_cd =>new_references.course_cd,
1143 p_cal_type =>l_load_cal_type,
1144 p_sequence_number =>l_load_seq_num,
1145 p_eftsu_total =>l_dummy_value,
1146 p_credit_points => l_old_cp);
1147 END IF;
1148 CLOSE cur_load;
1149 END IF;
1150 END IF;
1151
1152 IF p_updating THEN
1153 IF (old_references.unit_attempt_status <> new_references.unit_attempt_status) AND
1154 (new_references.unit_attempt_status = 'ENROLLED') THEN
1155 OPEN cur_placement_unit;
1156 FETCH cur_placement_unit INTO l_placement_unit;
1157 IF cur_placement_unit%FOUND THEN
1158 IGS_EN_WORKFLOW.student_placement_event (
1159 P_person_id => new_references.person_id,
1160 P_program_cd => new_references.course_cd,
1161 P_unit_cd => new_references.unit_cd,
1162 P_unit_class => new_references.unit_class,
1163 P_location_cd => new_references.location_cd,
1164 p_uoo_id => new_references.uoo_id);
1165 END IF;
1166 CLOSE cur_placement_unit;
1167 END IF;
1168 END IF;
1169 END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
1170
1171 END IF; -- IF p_inserting OR p_updating THEN
1172 IF p_deleting THEN
1173
1174 -- Validate student IGS_PS_UNIT attempt
1175 IF IGS_EN_VAL_SUA.enrp_val_sua_delete(
1176 old_references.person_id,
1177 old_references.course_cd,
1178 old_references.unit_cd,
1179 'T', -- indicates trigger validation
1180 old_references.unit_attempt_status,
1181 old_references.cal_type,
1182 old_references.ci_sequence_number,
1183 old_references.discontinued_dt,
1184 SYSDATE,
1185 v_message_name,
1186 old_references.uoo_id) = FALSE THEN
1187 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1188 IGS_GE_MSG_STACK.ADD;
1189 APP_EXCEPTION.RAISE_EXCEPTION;
1190 END IF;
1191 -- Save student IGS_PS_COURSE attempt details in package table information
1192 -- so that IGS_PS_COURSE attempt status can be updated in
1193 -- after statement trigger
1194 IF IGS_EN_GEN_012.ENRP_UPD_SCA_STATUS(
1195 old_references.person_id,
1196 old_references.course_cd,
1197 v_message_name) = FALSE THEN
1198 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1199 IGS_GE_MSG_STACK.ADD;
1200 APP_EXCEPTION.RAISE_EXCEPTION;
1201 END IF;
1202 -- Save student IGS_PS_COURSE attempt details in package table information
1203 -- so that student IGS_PS_UNIT transfer detail can be deleted in an
1204 -- after statement trigger
1205 IF IGS_EN_GEN_001.ENRP_DEL_SUA_SUT(
1206 old_references.person_id,
1207 old_references.course_cd,
1208 old_references.unit_cd,
1209 old_references.cal_type,
1210 old_references.ci_sequence_number,
1211 old_references.unit_attempt_status,
1212 v_message_name,
1213 old_references.uoo_id) = FALSE THEN
1214 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1215 IGS_GE_MSG_STACK.ADD;
1216 APP_EXCEPTION.RAISE_EXCEPTION;
1217 END IF;
1218 IF old_references.unit_attempt_status = 'ENROLLED' THEN
1219 v_return_val := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(old_references.person_id,
1220 'UNIT-RULES',
1221 NULL,
1222 'Y');
1223 END IF;
1224 IF NOT IGS_EN_SUA_API.chk_sup_del_alwd( old_references.person_id,
1225 old_references.course_cd,
1226 old_references.uoo_id) THEN
1227 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SUP_DEL_NOTALWD');
1228 IGS_GE_MSG_STACK.ADD;
1229 APP_EXCEPTION.RAISE_EXCEPTION;
1230 END IF;
1231
1232 END IF; -- IF p_deleting THEN
1233
1234
1235 END BeforeRowInsertUpdateDelete1;
1236 -- Procedure for checking the uniqueness
1237 PROCEDURE Check_Uniqueness AS
1238 -- The Unique key has been commented as part of the bug fix 2554109
1239 -- The uniqueness will be validated by the the new function igs_en_wlst_gen_proc.enrp_resequence_wlst();
1240 BEGIN
1241
1242 /*This Uk check was added as part of MUS build, # 2829262*/
1243 IF Get_Uk_For_Validation(x_unit_cd => new_references.unit_cd,
1244 x_cal_type => new_references.cal_type,
1245 x_ci_sequence_number => new_references.ci_sequence_number,
1246 x_location_cd => new_references.location_cd,
1247 x_unit_class => new_references.unit_class,
1248 x_person_id => new_references.person_id,
1249 x_course_cd => new_references.course_cd,
1250 x_version_number => new_references.version_number
1251 ) THEN
1252 FND_MESSAGE.SET_NAME('IGS','IGS_GE_MULTI_ORG_DUP_REC');
1253 IGS_GE_MSG_STACK.ADD;
1254 APP_EXCEPTION.RAISE_EXCEPTION;
1255 END IF;
1256
1257 END Check_Uniqueness;
1258
1259 -- Trigger description :-
1260 -- "OSS_TST".trg_sua_br_iud_fin
1261 -- BEFORE INSERT OR DELETE OR UPDATE
1262 -- ON IGS_EN_SU_ATTEMPT_ALL
1263 -- FOR EACH ROW
1264 PROCEDURE BeforeRowInsertUpdateDelete2(
1265 p_inserting IN BOOLEAN,
1266 p_updating IN BOOLEAN ,
1267 p_deleting IN BOOLEAN
1268 ) AS
1269 v_sequence_number NUMBER;
1270 l_special_fee NUMBER;
1271 l_apply_spl_fee BOOLEAN;
1272 l_load_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1273 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1274
1275 CURSOR cur_special_fee IS
1276 SELECT count(*)
1277 FROM IGS_PS_USEC_SP_FEES
1278 WHERE uoo_id = NVL(old_references.uoo_id, new_references.uoo_id)
1279 AND closed_flag = 'N';
1280
1281 CURSOR cur_cal_info IS
1282 SELECT load_ci_sequence_number, load_cal_type
1283 FROM igs_ca_teach_to_load_v
1284 WHERE teach_cal_type = NVL(old_references.cal_type, new_references.cal_type)
1285 AND teach_ci_sequence_number = NVL(old_references.ci_sequence_number,new_references.ci_sequence_number)
1286 ORDER BY load_start_dt ASC;
1287
1288 CURSOR cur_sua_ref_cds IS
1289 Select sref.rowid,sref.*
1290 From IGS_AS_SUA_REF_CDS sref
1291 Where sref.person_id = old_references.person_id
1292 And sref.course_cd = old_references.course_cd
1293 And sref.uoo_id = old_references.uoo_id
1294 And sref.deleted_date is null;
1295
1296 BEGIN
1297 -- anilk, Audit special fee build
1298 OPEN cur_special_fee;
1299 FETCH cur_special_fee INTO l_special_fee;
1300 CLOSE cur_special_fee;
1301
1302 IF l_special_fee = 0 THEN
1303 l_apply_spl_fee := FALSE;
1304 ELSE
1305 l_apply_spl_fee := TRUE;
1306 END IF;
1307
1308 IF l_apply_spl_fee THEN
1309 IF p_inserting THEN
1310 l_apply_spl_fee := TRUE;
1311 ELSIF p_updating AND new_references.unit_attempt_status <> old_references.unit_attempt_status THEN
1312 l_apply_spl_fee := TRUE;
1313 ELSIF p_deleting THEN
1314 l_apply_spl_fee := TRUE;
1315 ELSE
1316 l_apply_spl_fee := FALSE;
1317 END IF;
1318 END IF;
1319
1320 IF l_apply_spl_fee THEN
1321
1322 OPEN cur_cal_info;
1323 FETCH cur_cal_info INTO l_load_ci_sequence_number, l_load_cal_type;
1324 CLOSE cur_cal_info;
1325
1326 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
1327 NVL(old_references.person_id, new_references.person_id),
1328 'SPECIAL_FEE',
1329 SYSDATE,
1330 'Y');
1331
1332 IGS_GE_GEN_003.GENP_INS_TODO_REF(
1333 NVL(old_references.person_id,new_references.person_id),
1334 'SPECIAL_FEE',
1335 v_sequence_number,
1336 l_load_cal_type,
1337 l_load_ci_sequence_number,
1338 NVL(old_references.course_cd, new_references.course_cd),
1339 NVL(old_references.unit_cd, new_references.unit_cd),
1340 NULL,
1341 NVL(old_references.uoo_id, new_references.uoo_id));
1342 END IF;
1343 -- END, anilk, Audit special fee build
1344
1345 -- Log an entry in the IGS_PE_STD_TODO table, indicating that a fee re-assessment
1346 -- is required.
1347 IF p_inserting OR p_updating THEN
1348 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
1349 new_references.person_id,
1350 'FEE_RECALC',
1351 SYSDATE,
1352 'Y');
1353 ELSE
1354 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
1355 old_references.person_id,
1356 'FEE_RECALC',
1357 SYSDATE,
1358 'Y');
1359 END IF;
1360
1361 --logic for marking unit attempt reference codes as dropped
1362 --when the unit attempt is being dropped
1363 IF p_updating AND
1364 old_references.unit_attempt_status <> 'DROPPED' AND
1365 new_references.unit_attempt_status = 'DROPPED' THEN
1366
1367 FOR v_cur_sua_ref_cds IN cur_sua_ref_cds LOOP
1368 igs_as_sua_ref_cds_pkg.update_row (
1369 x_rowid => v_cur_sua_ref_cds.rowid,
1370 x_suar_id => v_cur_sua_ref_cds.suar_id,
1371 x_person_id => v_cur_sua_ref_cds.person_id,
1372 x_course_cd => v_cur_sua_ref_cds.course_cd,
1373 x_uoo_id => v_cur_sua_ref_cds.uoo_id,
1374 x_reference_code_id => v_cur_sua_ref_cds.reference_code_id,
1375 x_reference_cd_type => v_cur_sua_ref_cds.reference_cd_type,
1376 x_reference_cd => v_cur_sua_ref_cds.reference_cd,
1377 x_applied_course_cd => v_cur_sua_ref_cds.applied_course_cd,
1378 x_deleted_date => SYSDATE,
1379 x_mode => 'R' );
1380 END LOOP;
1381
1382 END IF;
1383
1384 END BeforeRowInsertUpdateDelete2;
1385
1386
1387
1388 -- Trigger description :-
1389 -- "OSS_TST".trg_sua_ar_iud
1390 -- AFTER INSERT OR DELETE OR UPDATE
1391 -- ON IGS_EN_SU_ATTEMPT_ALL
1392 -- FOR EACH ROW
1393 PROCEDURE AfterRowInsertUpdateDelete3(
1394 p_inserting IN BOOLEAN,
1395 p_updating IN BOOLEAN ,
1396 p_deleting IN BOOLEAN
1397 ) AS
1398 v_message_name VARCHAR2(30);
1399 v_rowid_saved BOOLEAN := FALSE;
1400 BEGIN
1401 -- If trigger has not been disabled, perform required processing
1402 -- IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_SU_ATTEMPT_ALL') THEN
1403 IF(p_updating AND
1404 old_references.unit_attempt_status <> new_references.unit_attempt_status) OR
1405 p_inserting THEN
1406 -- update of student IGS_PS_COURSE attempt after student IGS_PS_UNIT attempt is posted
1407 -- to the database
1408 IF v_rowid_saved = FALSE THEN
1409 -- Save the rowid of the current row.
1410 IF v_rowid_saved = FALSE THEN
1411 IF IGS_EN_GEN_012.ENRP_UPD_SCA_STATUS(
1412 new_references.person_id,
1413 new_references.course_cd,
1414 v_message_name) = FALSE THEN
1415 FND_MESSAGE.SET_NAME('IGS',v_message_name);
1416 IGS_GE_MSG_STACK.ADD;
1417 APP_EXCEPTION.RAISE_EXCEPTION;
1418 END IF;
1419 v_rowid_saved := TRUE;
1420 END IF;
1421 END IF;
1422 END IF;
1423 -- END IF;
1424 END AfterRowInsertUpdateDelete3;
1425
1426 -- Trigger description :-
1427 -- "OSS_TST".trg_sua_ar_u_hist
1428 -- AFTER UPDATE
1429 -- ON IGS_EN_SU_ATTEMPT_ALL
1430 -- FOR EACH ROW
1431 PROCEDURE AfterRowUpdate4(
1432 p_inserting IN BOOLEAN,
1433 p_updating IN BOOLEAN ,
1434 p_deleting IN BOOLEAN
1435 ) AS
1436 BEGIN
1437 -- create a history
1438 IGS_EN_GEN_010.ENRP_INS_SUA_HIST( old_references.person_id,
1439 old_references.course_cd,
1440 old_references.unit_cd,
1441 old_references.cal_type,
1442 old_references.ci_sequence_number,
1443 new_references.version_number,
1444 old_references.version_number,
1445 new_references.location_cd,
1446 old_references.location_cd,
1447 new_references.unit_class,
1448 old_references.unit_class,
1449 new_references.enrolled_dt,
1450 old_references.enrolled_dt,
1451 new_references.unit_attempt_status,
1452 old_references.unit_attempt_status,
1453 new_references.administrative_unit_status,
1454 old_references.administrative_unit_status,
1455 new_references.discontinued_dt,
1456 old_references.discontinued_dt,
1457 new_references.rule_waived_dt,
1458 old_references.rule_waived_dt,
1459 new_references.rule_waived_person_id,
1460 old_references.rule_waived_person_id,
1461 new_references.no_assessment_ind,
1462 old_references.no_assessment_ind,
1463 new_references.exam_location_cd,
1464 old_references.exam_location_cd,
1465 new_references.sup_unit_cd,
1466 old_references.sup_unit_cd,
1467 new_references.sup_version_number,
1468 old_references.sup_version_number,
1469 new_references.alternative_title,
1470 old_references.alternative_title,
1471 new_references.override_enrolled_cp,
1472 old_references.override_enrolled_cp,
1473 new_references.override_eftsu,
1474 old_references.override_eftsu,
1475 new_references.override_achievable_cp,
1476 old_references.override_achievable_cp,
1477 new_references.override_outcome_due_dt,
1478 old_references.override_outcome_due_dt,
1479 new_references.override_credit_reason,
1480 old_references.override_credit_reason,
1481 new_references.last_updated_by,
1482 old_references.last_updated_by,
1483 new_references.last_update_date,
1484 old_references.last_update_date,
1485 new_references.dcnt_reason_cd,
1486 old_references.dcnt_reason_Cd,
1487 old_references.uoo_id,
1488 new_references.core_indicator_code,
1489 old_references.core_indicator_code);
1490 END AfterRowUpdate4;
1491 -- Trigger description :-
1492 -- "OSS_TST".trg_sua_as_iud
1493 -- AFTER INSERT OR DELETE OR UPDATE
1494 -- ON IGS_EN_SU_ATTEMPT_ALL
1495
1496
1497 PROCEDURE Check_Parent_Existance AS
1498 BEGIN
1499
1500 /* WHO WHEN WHAT
1501 ckasu 27-NOV-2005 Added logic inorder toby pass validation IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION
1502 when IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN' since Plaaning Sheet Records
1503 are created in 'PLANNED' Status which is not a looup value as a part of bug#4666102
1504
1505 */
1506
1507 IF igs_en_su_attempt_pkg.pkg_source_of_drop = 'SWAP' THEN
1508 RETURN;
1509 END IF;
1510 IF (((old_references.administrative_unit_status= new_references.administrative_unit_status)) OR
1511 ((new_references.administrative_unit_status IS NULL))) THEN
1512 NULL;
1513 ELSIF NOT IGS_AD_ADM_UNIT_STAT_PKG.Get_PK_For_Validation (
1514 new_references.administrative_unit_status,'N') THEN
1515 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1516 IGS_GE_MSG_STACK.ADD;
1517 APP_EXCEPTION.RAISE_EXCEPTION;
1518 END IF;
1519
1520 -- Check whether Grading schema code and gs_version_number are present in the master table IGS_AS_GRD_SCHEMA
1521 IF (
1522 (
1523 (old_references.grading_schema_code = new_references.grading_schema_code) AND
1524 (old_references.gs_version_number = new_references.gs_version_number )
1525 )
1526 OR
1527 (
1528 (new_references.grading_schema_code IS NULL) OR
1529 (new_references.gs_version_number IS NULL)
1530 )
1531 ) THEN
1532 NULL;
1533 ELSIF NOT IGS_AS_GRD_SCHEMA_PKG.Get_PK_For_Validation (
1534 new_references.grading_schema_code,
1535 new_references.gs_version_number ) THEN
1536 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1537 IGS_GE_MSG_STACK.ADD;
1538 APP_EXCEPTION.RAISE_EXCEPTION;
1539 END IF;
1540
1541 IF (((old_references.CAL_TYPE = new_references.cal_type) AND
1542 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
1543 (old_references.ci_start_dt = new_references.ci_start_dt) AND
1544 (old_references.ci_end_dt = new_references.ci_end_dt)) OR
1545 ((new_references.cal_type IS NULL) OR
1546 (new_references.ci_sequence_number IS NULL) OR
1547 (new_references.ci_start_dt IS NULL) OR
1548 (new_references.ci_end_dt IS NULL))) THEN
1549 NULL;
1550 ELSIF NOT IGS_CA_INST_PKG.Get_UK_For_Validation (
1551 new_references.cal_type,
1552 new_references.ci_sequence_number,
1553 new_references.ci_start_dt,
1554 new_references.ci_end_dt ) THEN
1555 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1556 IGS_GE_MSG_STACK.ADD;
1557 APP_EXCEPTION.RAISE_EXCEPTION;
1558 END IF;
1559
1560 IF (((old_references.exam_location_cd = new_references.exam_location_cd)) OR
1561 ((new_references.exam_location_cd IS NULL))) THEN
1562 NULL;
1563 ELSIF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
1564 new_references.exam_location_cd,'N'
1565 ) THEN
1566 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1567 IGS_GE_MSG_STACK.ADD;
1568 APP_EXCEPTION.RAISE_EXCEPTION;
1569 END IF;
1570 IF (((old_references.rule_waived_person_id = new_references.rule_waived_person_id)) OR
1571 ((new_references.rule_waived_person_id IS NULL))) THEN
1572 NULL;
1573 ELSIF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
1574 new_references.rule_waived_person_id ) THEN
1575 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1576 IGS_GE_MSG_STACK.ADD;
1577 APP_EXCEPTION.RAISE_EXCEPTION;
1578 END IF;
1579 IF (((old_references.person_id = new_references.person_id) AND
1580 (old_references.course_cd = new_references.course_cd)) OR
1581 ((new_references.person_id IS NULL) OR
1582 (new_references.course_cd IS NULL))) THEN
1583 NULL;
1584 ELSIF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
1585 new_references.person_id,
1586 new_references.course_cd
1587 ) THEN
1588 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1589 IGS_GE_MSG_STACK.ADD;
1590 APP_EXCEPTION.RAISE_EXCEPTION;
1591 END IF;
1592 IF (((old_references.unit_attempt_status = new_references.unit_attempt_status)) OR
1593 ((new_references.unit_attempt_status IS NULL)) OR (IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NOT NULL
1594 AND IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN') ) THEN
1595 NULL;
1596 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION('UNIT_ATTEMPT_STATUS',
1597 new_references.unit_attempt_status) THEN
1598 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1599 IGS_GE_MSG_STACK.ADD;
1600 APP_EXCEPTION.RAISE_EXCEPTION;
1601 END IF;
1602 IF (((old_references.sup_unit_cd = new_references.sup_unit_cd) AND
1603 (old_references.sup_version_number = new_references.sup_version_number)) OR
1604 ((new_references.sup_unit_cd IS NULL) OR
1605 (new_references.sup_version_number IS NULL))) THEN
1606 NULL;
1607 ELSIF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
1608 new_references.sup_unit_cd,
1609 new_references.sup_version_number
1610 ) THEN
1611 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1612 IGS_GE_MSG_STACK.ADD;
1613 APP_EXCEPTION.RAISE_EXCEPTION;
1614 END IF;
1615 IF (((old_references.unit_cd = new_references.unit_cd) AND
1616 (old_references.version_number = new_references.version_number) AND
1617 (old_references.cal_type= new_references.cal_type) AND
1618 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
1619 (old_references.location_cd = new_references.location_cd) AND
1620 (old_references.unit_class= new_references.unit_class)) OR
1621 ((new_references.unit_cd IS NULL) OR
1622 (new_references.version_number IS NULL) OR
1623 (new_references.cal_type IS NULL) OR
1624 (new_references.ci_sequence_number IS NULL) OR
1625 (new_references.location_cd IS NULL) OR
1626 (new_references.unit_class IS NULL))) THEN
1627 NULL;
1628 ELSIF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_PK_For_Validation (
1629 new_references.unit_cd,
1630 new_references.version_number,
1631 new_references.cal_type,
1632 new_references.ci_sequence_number,
1633 new_references.location_cd,
1634 new_references.unit_class
1635 ) THEN
1636 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1637 IGS_GE_MSG_STACK.ADD;
1638 APP_EXCEPTION.RAISE_EXCEPTION;
1639 END IF;
1640 IF ( (old_references.dcnt_reason_cd = new_references.dcnt_reason_cd) OR
1641 (new_references.dcnt_reason_Cd IS NULL ) ) THEN
1642 NULL;
1643 ELSIF NOT IGS_EN_DCNT_REASONCD_PKG.Get_PK_For_Validation (
1644 new_references.dcnt_reason_cd
1645 ) THEN
1646 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1647 IGS_GE_MSG_STACK.ADD;
1648 APP_EXCEPTION.RAISE_EXCEPTION;
1649 END IF;
1650
1651 IF ( (old_references.rsv_seat_ext_id = new_references.rsv_seat_ext_id) OR
1652 (new_references.rsv_seat_ext_id IS NULL ) ) THEN
1653 NULL;
1654 ELSIF NOT IGS_PS_RSV_EXT_PKG.Get_PK_For_Validation (
1655 new_references.rsv_seat_ext_id
1656 ) THEN
1657 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1658 IGS_GE_MSG_STACK.ADD;
1659 APP_EXCEPTION.RAISE_EXCEPTION;
1660 END IF;
1661
1662
1663 IF (((old_references.grading_schema_code = new_references.grading_schema_code) AND
1664 (old_references.gs_version_number = new_references.gs_version_number) AND
1665 (old_references.uoo_id = new_references.uoo_id)) OR
1666 ((new_references.grading_schema_code IS NULL) OR
1667 (new_references.gs_version_number IS NULL) OR
1668 (new_references.uoo_id IS NULL) )) THEN
1669 NULL;
1670 ELSIF NOT IGS_PS_USEC_GRD_SCHM_PKG.Get_UK_For_Validation ( new_references.grading_schema_code,
1671 new_references.gs_version_number,
1672 new_references.uoo_id
1673 ) THEN
1674
1675 IF (((old_references.version_number = new_references.version_number) AND
1676 (old_references.grading_schema_code = new_references.grading_schema_code) AND
1677 (old_references.gs_version_number = new_references.gs_version_number) AND
1678 (old_references.unit_cd = new_references.unit_cd)) OR
1679 ((new_references.version_number IS NULL) OR
1680 (new_references.grading_schema_code IS NULL) OR
1681 (new_references.gs_version_number IS NULL) OR
1682 (new_references.unit_cd IS NULL) )) THEN
1683 NULL;
1684 ELSIF NOT IGS_PS_UNIT_GRD_SCHM_PKG.Get_UK_For_Validation ( new_references.version_number,
1685 new_references.grading_schema_code,
1686 new_references.gs_version_number,
1687 new_references.unit_cd
1688 ) THEN
1689
1690 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1691 IGS_GE_MSG_STACK.ADD;
1692 APP_EXCEPTION.RAISE_EXCEPTION;
1693 END IF;
1694 END IF;
1695
1696 END Check_Parent_Existance;
1697
1698
1699 PROCEDURE Check_Child_Existance AS
1700 CURSOR CUR_IGS_AS_SU_ATMPT_ITM IS
1701 SELECT ROWID
1702 FROM IGS_AS_SU_ATMPT_ITM
1703 WHERE person_id = old_references.person_id
1704 AND course_cd = old_references.course_cd
1705 AND uoo_id = old_references.uoo_id;
1706
1707 CURSOR CUR_IGS_EN_SU_ATTEMPT_H IS
1708 SELECT ROWID
1709 FROM IGS_EN_SU_ATTEMPT_H_ALL
1710 WHERE person_id = old_references.person_id
1711 AND course_cd = old_references.course_cd
1712 AND uoo_id = old_references.uoo_id;
1713 BEGIN
1714 IGS_AS_MSHT_SU_ATMPT_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1715 old_references.person_id,
1716 old_references.course_cd,
1717 old_references.uoo_id
1718 );
1719 /*deleting the history record*/
1720 FOR IGS_EN_SU_ATTEMPT_H_REC IN CUR_IGS_EN_SU_ATTEMPT_H LOOP
1721 IGS_EN_SU_ATTEMPT_H_PKG.DELETE_ROW(X_ROWID => IGS_EN_SU_ATTEMPT_H_REC.ROWID);
1722 END LOOP;
1723 FOR IGS_AS_SU_ATMPT_ITM_REC IN CUR_IGS_AS_SU_ATMPT_ITM LOOP
1724 IGS_AS_SU_ATMPT_ITM_PKG.DELETE_ROW(X_ROWID => IGS_AS_SU_ATMPT_ITM_REC.ROWID);
1725 END LOOP;
1726 IGS_AS_SU_STMPTOUT_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1727 old_references.person_id,
1728 old_references.course_cd,
1729 old_references.uoo_id
1730 );
1731 IGS_PS_STDNT_UNT_TRN_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1732 old_references.person_id,
1733 old_references.course_cd,
1734 old_references.uoo_id
1735 );
1736
1737 IGS_AS_ANON_ID_US_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1738 old_references.person_id,
1739 old_references.course_cd,
1740 old_references.uoo_id
1741 );
1742 --Bug 3199686
1743 IGS_AS_SUA_SES_ATTS_pkg.GET_FK_IGS_EN_SU_ATTEMPT(
1744 old_references.person_id,
1745 old_references.course_cd,
1746 old_references.uoo_id
1747 );
1748
1749 END Check_Child_Existance;
1750 -------------------------------------------------------------------------------------------
1751 --Change History:
1752 --Who When What
1753 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
1754 -------------------------------------------------------------------------------------------
1755 FUNCTION Get_PK_For_Validation (
1756 x_person_id IN NUMBER,
1757 x_course_cd IN VARCHAR2,
1758 x_uoo_id IN NUMBER
1759 ) RETURN BOOLEAN AS
1760 CURSOR cur_rowid IS
1761 SELECT ROWID
1762 FROM IGS_EN_SU_ATTEMPT_ALL
1763 WHERE person_id = x_person_id
1764 AND course_cd = x_course_cd
1765 AND uoo_id = x_uoo_id
1766 FOR UPDATE NOWAIT;
1767 lv_rowid cur_rowid%ROWTYPE;
1768 BEGIN
1769 OPEN cur_rowid;
1770 FETCH cur_rowid INTO lv_rowid;
1771 IF (cur_rowid%FOUND) THEN
1772 CLOSE cur_rowid;
1773 RETURN (TRUE);
1774 ELSE
1775 CLOSE cur_rowid;
1776 RETURN (FALSE);
1777 END IF;
1778 END Get_PK_For_Validation;
1779
1780 PROCEDURE GET_UFK_IGS_CA_INST (
1781 x_cal_type IN VARCHAR2,
1782 x_sequence_number IN NUMBER,
1783 x_start_dt IN DATE,
1784 x_end_dt IN DATE
1785 ) AS
1786 CURSOR cur_rowid IS
1787 SELECT ROWID
1788 FROM IGS_EN_SU_ATTEMPT_ALL
1789 WHERE cal_type= x_cal_type
1790 AND ci_sequence_number = x_sequence_number
1791 AND ci_start_dt = x_start_dt
1792 AND ci_end_dt = x_end_dt ;
1793 lv_rowid cur_rowid%ROWTYPE;
1794 BEGIN
1795 OPEN cur_rowid;
1796 FETCH cur_rowid INTO lv_rowid;
1797 IF (cur_rowid%FOUND) THEN
1798 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_CI_UFK');
1799 IGS_GE_MSG_STACK.ADD;
1800 CLOSE cur_rowid;
1801 APP_EXCEPTION.RAISE_EXCEPTION;
1802 RETURN;
1803 END IF;
1804 CLOSE cur_rowid;
1805 END GET_UFK_IGS_CA_INST;
1806
1807 PROCEDURE GET_FK_IGS_AD_LOCATION (
1808 x_location_cd IN VARCHAR2
1809 ) AS
1810 CURSOR cur_rowid IS
1811 SELECT ROWID
1812 FROM IGS_EN_SU_ATTEMPT_ALL
1813 WHERE exam_location_cd = x_location_cd ;
1814 lv_rowid cur_rowid%ROWTYPE;
1815 BEGIN
1816 OPEN cur_rowid;
1817 FETCH cur_rowid INTO lv_rowid;
1818 IF (cur_rowid%FOUND) THEN
1819 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_LOC_FK');
1820 IGS_GE_MSG_STACK.ADD;
1821 CLOSE cur_rowid;
1822 APP_EXCEPTION.RAISE_EXCEPTION;
1823 RETURN;
1824 END IF;
1825 CLOSE cur_rowid;
1826 END GET_FK_IGS_AD_LOCATION;
1827
1828 PROCEDURE GET_FK_IGS_AS_GRD_SCH_GRADE (
1829 x_grading_schema_code IN VARCHAR2 ,
1830 x_gs_version_number IN NUMBER
1831 ) AS
1832 CURSOR cur_rowid IS
1833 SELECT ROWID
1834 FROM IGS_EN_SU_ATTEMPT_ALL
1835 WHERE grading_schema_code = x_grading_schema_code
1836 AND
1837 gs_version_number = x_gs_version_number ;
1838
1839 lv_rowid cur_rowid%ROWTYPE;
1840 BEGIN
1841 OPEN cur_rowid;
1842 FETCH cur_rowid INTO lv_rowid;
1843 IF (cur_rowid%FOUND) THEN
1844 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_GS_FK');
1845 IGS_GE_MSG_STACK.ADD;
1846 CLOSE cur_rowid;
1847 APP_EXCEPTION.RAISE_EXCEPTION;
1848 RETURN;
1849 END IF;
1850 CLOSE cur_rowid;
1851 END GET_FK_IGS_AS_GRD_SCH_GRADE;
1852
1853 PROCEDURE GET_FK_IGS_PE_PERSON (
1854 x_person_id IN NUMBER
1855 ) AS
1856 CURSOR cur_rowid IS
1857 SELECT ROWID
1858 FROM IGS_EN_SU_ATTEMPT_ALL
1859 WHERE rule_waived_person_id = x_person_id ;
1860 lv_rowid cur_rowid%ROWTYPE;
1861 BEGIN
1862 OPEN cur_rowid;
1863 FETCH cur_rowid INTO lv_rowid;
1864 IF (cur_rowid%FOUND) THEN
1865 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_PE_FK');
1866 IGS_GE_MSG_STACK.ADD;
1867 CLOSE cur_rowid;
1868 APP_EXCEPTION.RAISE_EXCEPTION;
1869 RETURN;
1870 END IF;
1871 CLOSE cur_rowid;
1872 END GET_FK_IGS_PE_PERSON;
1873
1874 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
1875 x_person_id IN NUMBER,
1876 x_course_cd IN VARCHAR2
1877 ) AS
1878 CURSOR cur_rowid IS
1879 SELECT ROWID
1880 FROM IGS_EN_SU_ATTEMPT_ALL
1881 WHERE person_id = x_person_id
1882 AND course_cd = x_course_cd ;
1883 lv_rowid cur_rowid%ROWTYPE;
1884 BEGIN
1885 OPEN cur_rowid;
1886 FETCH cur_rowid INTO lv_rowid;
1887 IF (cur_rowid%FOUND) THEN
1888 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_SCA_FK');
1889 IGS_GE_MSG_STACK.ADD;
1890 CLOSE cur_rowid;
1891 APP_EXCEPTION.RAISE_EXCEPTION;
1892 RETURN;
1893 END IF;
1894 CLOSE cur_rowid;
1895 END GET_FK_IGS_EN_STDNT_PS_ATT;
1896
1897 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
1898 x_unit_attempt_status IN VARCHAR2
1899 ) AS
1900 CURSOR cur_rowid IS
1901 SELECT ROWID
1902 FROM IGS_EN_SU_ATTEMPT_ALL
1903 WHERE unit_attempt_status = x_unit_attempt_status ;
1904 lv_rowid cur_rowid%ROWTYPE;
1905 BEGIN
1906 OPEN cur_rowid;
1907 FETCH cur_rowid INTO lv_rowid;
1908 IF (cur_rowid%FOUND) THEN
1909 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_LKUPV_FK');
1910 IGS_GE_MSG_STACK.ADD;
1911 CLOSE cur_rowid;
1912 APP_EXCEPTION.RAISE_EXCEPTION;
1913 RETURN;
1914 END IF;
1915 CLOSE cur_rowid;
1916 END GET_FK_IGS_LOOKUPS_VIEW;
1917
1918 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
1919 x_unit_cd IN VARCHAR2,
1920 x_version_number IN NUMBER
1921 ) AS
1922 CURSOR cur_rowid IS
1923 SELECT ROWID
1924 FROM IGS_EN_SU_ATTEMPT_ALL
1925 WHERE sup_unit_cd = x_unit_cd
1926 AND sup_version_number = x_version_number
1927 OR (unit_cd = x_unit_cd
1928 AND version_number = x_version_number ) ;
1929 lv_rowid cur_rowid%ROWTYPE;
1930 BEGIN
1931 OPEN cur_rowid;
1932 FETCH cur_rowid INTO lv_rowid;
1933 IF (cur_rowid%FOUND) THEN
1934 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_SUP_UV_FK');
1935 IGS_GE_MSG_STACK.ADD;
1936 CLOSE cur_rowid;
1937 APP_EXCEPTION.RAISE_EXCEPTION;
1938 RETURN;
1939 END IF;
1940 CLOSE cur_rowid;
1941 END GET_FK_IGS_PS_UNIT_VER;
1942
1943 PROCEDURE GET_FK_IGS_PS_UNIT (
1944 x_unit_cd IN VARCHAR2
1945 ) AS
1946 CURSOR cur_rowid IS
1947 SELECT ROWID
1948 FROM IGS_EN_SU_ATTEMPT_ALL
1949 WHERE unit_cd = x_unit_cd ;
1950 lv_rowid cur_rowid%ROWTYPE;
1951 BEGIN
1952 OPEN cur_rowid;
1953 FETCH cur_rowid INTO lv_rowid;
1954 IF (cur_rowid%FOUND) THEN
1955 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_UN_FK');
1956 IGS_GE_MSG_STACK.ADD;
1957 CLOSE cur_rowid;
1958 APP_EXCEPTION.RAISE_EXCEPTION;
1959 RETURN;
1960 END IF;
1961 CLOSE cur_rowid;
1962 END GET_FK_IGS_PS_UNIT;
1963
1964 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_OPT (
1965 x_unit_cd IN VARCHAR2,
1966 x_version_number IN NUMBER,
1967 x_cal_type IN VARCHAR2,
1968 x_ci_sequence_number IN NUMBER,
1969 x_location_cd IN VARCHAR2,
1970 x_unit_class IN VARCHAR2
1971 ) AS
1972 CURSOR cur_rowid IS
1973 SELECT ROWID
1974 FROM IGS_EN_SU_ATTEMPT_ALL
1975 WHERE unit_cd = x_unit_cd
1976 AND version_number = x_version_number
1977 AND cal_type= x_cal_type
1978 AND ci_sequence_number = x_ci_sequence_number
1979 AND location_cd = x_location_cd
1980 AND unit_class= x_unit_class ;
1981 lv_rowid cur_rowid%ROWTYPE;
1982 BEGIN
1983 OPEN cur_rowid;
1984 FETCH cur_rowid INTO lv_rowid;
1985 IF (cur_rowid%FOUND) THEN
1986 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_UOO_UFK');
1987 IGS_GE_MSG_STACK.ADD;
1988 CLOSE cur_rowid;
1989 APP_EXCEPTION.RAISE_EXCEPTION;
1990 RETURN;
1991 END IF;
1992 CLOSE cur_rowid;
1993 END GET_FK_IGS_PS_UNIT_OFR_OPT;
1994
1995 PROCEDURE GET_UFK_IGS_PS_UNIT_OFR_OPT (
1996 x_uoo_id IN NUMBER
1997 ) AS
1998 CURSOR cur_rowid IS
1999 SELECT ROWID
2000 FROM IGS_EN_SU_ATTEMPT_ALL
2001 WHERE uoo_id = x_uoo_id ;
2002 lv_rowid cur_rowid%ROWTYPE;
2003 BEGIN
2004 OPEN cur_rowid;
2005 FETCH cur_rowid INTO lv_rowid;
2006 IF (cur_rowid%FOUND) THEN
2007 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_UOO_UFK');
2008 IGS_GE_MSG_STACK.ADD;
2009 CLOSE cur_rowid;
2010 APP_EXCEPTION.RAISE_EXCEPTION;
2011 RETURN;
2012 END IF;
2013 CLOSE cur_rowid;
2014 END GET_UFK_IGS_PS_UNIT_OFR_OPT;
2015
2016 PROCEDURE GET_UFK_IGS_PS_UNIT_GRD_SCHM ( x_version_number IN NUMBER,
2017 x_grading_schema_code IN VARCHAR2,
2018 x_gs_version_number IN NUMBER,
2019 x_unit_cd IN VARCHAR2
2020 ) AS
2021 CURSOR cur_rowid IS
2022 SELECT ROWID
2023 FROM IGS_EN_SU_ATTEMPT_ALL
2024 WHERE version_number = x_version_number
2025 AND grading_schema_code = x_grading_schema_code
2026 AND gs_version_number = x_gs_version_number
2027 AND unit_cd = x_unit_cd ;
2028
2029 lv_rowid cur_rowid%ROWTYPE;
2030 BEGIN
2031 OPEN cur_rowid;
2032 FETCH cur_rowid INTO lv_rowid;
2033 IF (cur_rowid%FOUND) THEN
2034 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SUA_GRD_FK');
2035 IGS_GE_MSG_STACK.ADD;
2036 CLOSE cur_rowid;
2037 APP_EXCEPTION.RAISE_EXCEPTION;
2038 RETURN;
2039 END IF;
2040 CLOSE cur_rowid;
2041 END GET_UFK_IGS_PS_UNIT_GRD_SCHM;
2042
2043
2044 PROCEDURE GET_UFK_IGS_PS_USEC_GRD_SCHM ( x_grading_schema_code IN VARCHAR2,
2045 x_gs_version_number IN NUMBER,
2046 x_uoo_id IN NUMBER
2047 ) AS
2048 CURSOR cur_rowid IS
2049 SELECT ROWID
2050 FROM IGS_EN_SU_ATTEMPT_ALL
2051 WHERE grading_schema_code = x_grading_schema_code
2052 AND gs_version_number = x_gs_version_number
2053 AND uoo_id = x_uoo_id ;
2054
2055 lv_rowid cur_rowid%ROWTYPE;
2056 BEGIN
2057 OPEN cur_rowid;
2058 FETCH cur_rowid INTO lv_rowid;
2059 IF (cur_rowid%FOUND) THEN
2060 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SUA_GRD_FK');
2061 IGS_GE_MSG_STACK.ADD;
2062 CLOSE cur_rowid;
2063 APP_EXCEPTION.RAISE_EXCEPTION;
2064 RETURN;
2065 END IF;
2066 CLOSE cur_rowid;
2067 END GET_UFK_IGS_PS_USEC_GRD_SCHM;
2068
2069
2070
2071
2072 PROCEDURE Before_DML (
2073 p_action IN VARCHAR2,
2074 x_rowid IN VARCHAR2,
2075 x_uoo_id IN NUMBER ,
2076 x_enrolled_dt IN DATE ,
2077 x_unit_attempt_status IN VARCHAR2,
2078 x_administrative_unit_status IN VARCHAR2 ,
2079 x_discontinued_dt IN DATE ,
2080 x_rule_waived_dt IN DATE ,
2081 x_rule_waived_person_id IN NUMBER,
2082 x_no_assessment_ind IN VARCHAR2 ,
2083 x_sup_unit_cd IN VARCHAR2,
2084 x_sup_version_number IN NUMBER,
2085 x_exam_location_cd IN VARCHAR2,
2086 x_alternative_title IN VARCHAR2 ,
2087 x_override_enrolled_cp IN NUMBER,
2088 x_override_eftsu IN NUMBER,
2089 x_override_achievable_cp IN NUMBER ,
2090 x_override_outcome_due_dt IN DATE ,
2091 x_override_credit_reason IN VARCHAR2,
2092 x_person_id IN NUMBER,
2093 x_course_cd IN VARCHAR2 ,
2094 x_unit_cd IN VARCHAR2 ,
2095 x_version_number IN NUMBER ,
2096 x_cal_type IN VARCHAR2,
2097 x_ci_sequence_number IN NUMBER ,
2098 x_location_cd IN VARCHAR2 ,
2099 x_unit_class IN VARCHAR2 ,
2100 x_ci_start_dt IN DATE ,
2101 x_ci_end_dt IN DATE ,
2102 x_administrative_priority IN NUMBER ,
2103 x_waitlist_dt IN DATE ,
2104 x_dcnt_reason_cd IN VARCHAR2 ,
2105 x_creation_date IN DATE ,
2106 x_created_by IN NUMBER ,
2107 x_last_update_date IN DATE ,
2108 x_last_updated_by IN NUMBER,
2109 x_last_update_login IN NUMBER,
2110 x_org_id IN NUMBER,
2111 x_gs_version_number IN NUMBER,
2112 x_enr_method_type IN VARCHAR2,
2113 x_failed_unit_rule IN VARCHAR2,
2114 x_cart IN VARCHAR2,
2115 x_rsv_seat_ext_id IN NUMBER ,
2116 x_org_unit_cd IN VARCHAR2,
2117 x_grading_schema_code IN VARCHAR2,
2118 x_subtitle IN VARCHAR2,
2119 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
2120 x_session_id IN NUMBER ,
2121 x_deg_aud_detail_id IN NUMBER ,
2122 x_student_career_transcript IN VARCHAR2,
2123 x_student_career_statistics IN VARCHAR2,
2124 x_waitlist_manual_ind IN VARCHAR2 ,--Bug ID: 2554109 added by adhawan
2125 x_attribute_category IN VARCHAR2 ,
2126 x_attribute1 IN VARCHAR2 ,
2127 x_attribute2 IN VARCHAR2 ,
2128 x_attribute3 IN VARCHAR2 ,
2129 x_attribute4 IN VARCHAR2 ,
2130 x_attribute5 IN VARCHAR2 ,
2131 x_attribute6 IN VARCHAR2 ,
2132 x_attribute7 IN VARCHAR2 ,
2133 x_attribute8 IN VARCHAR2 ,
2134 x_attribute9 IN VARCHAR2 ,
2135 x_attribute10 IN VARCHAR2 ,
2136 x_attribute11 IN VARCHAR2 ,
2137 x_attribute12 IN VARCHAR2 ,
2138 x_attribute13 IN VARCHAR2 ,
2139 x_attribute14 IN VARCHAR2 ,
2140 x_attribute15 IN VARCHAR2 ,
2141 x_attribute16 IN VARCHAR2 ,
2142 x_attribute17 IN VARCHAR2 ,
2143 x_attribute18 IN VARCHAR2 ,
2144 x_attribute19 IN VARCHAR2 ,
2145 x_attribute20 IN VARCHAR2 ,
2146 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
2147 x_wlst_priority_weight_num IN NUMBER ,
2148 x_wlst_preference_weight_num IN NUMBER,
2149 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
2150 x_core_indicator_code IN VARCHAR2,
2151 X_UPD_AUDIT_FLAG IN VARCHAR2 ,
2152 X_SS_SOURCE_IND IN VARCHAR2
2153 ) AS
2154 -------------------------------------------------------------------------------------
2155 --who when what
2156 --smadathi 25-jul-2001 Call to Igs_En_Gen_015.validation_step_is_overridden added
2157 -- to check for unit section status, if it is CLOSED, check whether
2158 -- that has been overriden or not
2159 --kkillams 27-Mar-03 Modified usec_cur Cursor, replaced * with unit_section_status
2160 -- w.r.t. bug 2749648
2161 --svanukur 29-APR-2003 Created cursor c_same_section to prevent students from enrolling in
2162 -- multiple versions of same unit in the same teaching period and also
2163 -- changed the where clauses to pass uoo_id as part of MUS build
2164 --svanukur 23-May-2003 Redefined l_step_override_limit to refer to igs_en_elgb_ovr_uoo
2165 -- as part of Deny/War behaviour build # 2829272
2166 --svanukur 13-Jun-03 Checking for the unit section status of 'Not_offered' as part of
2167 -- validation Impact CR bug#2881385
2168 --svanukur 13-jun-03 The check for cancelled/planned unit section status was done only while
2169 -- inserting the unit attempt record but was not checking while
2170 -- updating a dropped or discontinued unit attempt.
2171 -- Now the check is being done for both for updation and insertion for bug #2980069.
2172 --rvivekan 16-Jun-2003 Added handling for same_teaching_period (MUS) checkbox in unit_Section level
2173 -- as a part of Repeat and reenrollment build #2881363
2174 --rvivekan 9-sep-2003 PSP integration build#3052433. modified behavior and declarations of
2175 -- repeatable_ind in the igs_ps_unit_ver table and the column name of
2176 -- same_teaching_period flag in igs_ps_unit_oft_opt to not_multiple_section_flag.
2177 -- svanukur 14-jan-2004 Added teh code for MUS validations in case of update for bug 3368048
2178 -- rvangala 25-Feb-2004 Change cursors c_mus_allowed and c_mus_participate to ensure that
2179 -- unit attempts not in discontinued status are considered, Bug #3456893
2180 -- bdeviset 22-NOV-2005 Moved mutiple section validtion to IGS_EN_VAL_SUA procedure and
2181 -- Bypassed MUS validation when ss_source_ind is SWAP for bug# 4676023
2182 -- ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
2183 -- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
2184 ------------------------------------------------------------------------------------
2185 CURSOR cur_rowid IS
2186 SELECT ROWID
2187 FROM IGS_EN_SU_ATTEMPT_ALL
2188 WHERE person_id = x_person_id
2189 AND course_cd = x_course_cd
2190 AND uoo_id = x_uoo_id
2191 AND unit_attempt_status = 'DROPPED';
2192 l_rowid VARCHAR2(25);
2193 l_step_override_limit IGS_EN_ELGB_OVR_UOO.step_override_limit%TYPE ;
2194 l_result BOOLEAN ;
2195
2196 CURSOR usec_cur(p_uoo_id NUMBER) IS
2197 SELECT unit_section_status
2198 FROM igs_ps_unit_ofr_opt uoo
2199 WHERE uoo_id = p_uoo_id;
2200
2201 usec_cur_row usec_cur%ROWTYPE;
2202
2203 BEGIN
2204 l_result :=FALSE;
2205 l_step_override_limit := 0;
2206 Set_Column_Values (
2207 p_action,
2208 x_rowid,
2209 x_uoo_id,
2210 x_enrolled_dt,
2211 x_unit_attempt_status,
2212 x_administrative_unit_status,
2213 x_discontinued_dt,
2214 x_rule_waived_dt,
2215 x_rule_waived_person_id,
2216 x_no_assessment_ind,
2217 x_sup_unit_cd,
2218 x_sup_version_number,
2219 x_exam_location_cd,
2220 x_alternative_title,
2221 x_override_enrolled_cp,
2222 x_override_eftsu,
2223 x_override_achievable_cp,
2224 x_override_outcome_due_dt,
2225 x_override_credit_reason,
2226 x_person_id,
2227 x_course_cd,
2228 x_unit_cd,
2229 x_version_number,
2230 x_cal_type,
2231 x_ci_sequence_number,
2232 x_location_cd,
2233 x_unit_class,
2234 x_ci_start_dt,
2235 x_ci_end_dt,
2236 x_administrative_priority,
2237 x_waitlist_dt,
2238 x_dcnt_reason_cd,
2239 x_creation_date,
2240 x_created_by,
2241 x_last_update_date,
2242 x_last_updated_by,
2243 x_last_update_login,
2244 x_org_id,
2245 x_gs_version_number,
2246 x_enr_method_type ,
2247 x_failed_unit_rule ,
2248 x_cart ,
2249 x_rsv_seat_ext_id,
2250 x_org_unit_cd ,
2251 x_grading_schema_code,
2252 x_subtitle,
2253 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
2254 x_session_id,
2255 x_deg_aud_detail_id,
2256 x_student_career_transcript,
2257 x_student_career_statistics,
2258 x_waitlist_manual_ind , --Bug ID: 2554109 added by adhawan
2259 x_attribute_category,
2260 x_attribute1,
2261 x_attribute2,
2262 x_attribute3,
2263 x_attribute4,
2264 x_attribute5,
2265 x_attribute6,
2266 x_attribute7,
2267 x_attribute8,
2268 x_attribute9,
2269 x_attribute10,
2270 x_attribute11,
2271 x_attribute12,
2272 x_attribute13,
2273 x_attribute14,
2274 x_attribute15,
2275 x_attribute16,
2276 x_attribute17,
2277 x_attribute18,
2278 x_attribute19,
2279 x_attribute20,
2280 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
2281 x_wlst_priority_weight_num,
2282 x_wlst_preference_weight_num,
2283 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
2284 x_core_indicator_code,
2285 X_UPD_AUDIT_FLAG,
2286 X_SS_SOURCE_IND
2287 );
2288 IF (p_action = 'INSERT')
2289 OR
2290 (
2291 p_action = 'UPDATE' AND
2292 old_references.unit_attempt_status IN ('DROPPED','DISCONTIN') AND
2293 old_references.unit_attempt_status <> new_references.unit_attempt_status
2294 ) THEN
2295
2296
2297
2298 -- Enhancement in response to the bug 1552624
2299 -- This piece of code does NOT allow enrollment into a unit attempt
2300 -- if the unit section status is NOT open.
2301 OPEN usec_cur(new_references.uoo_id);
2302 FETCH usec_cur INTO usec_cur_row;
2303 CLOSE usec_cur;
2304 --
2305 -- check for unit section status, if it is CLOSED, check whether
2306 -- that has been overriden or not
2307 -- if not, display error
2308 -- Also display error if it is PLANNED or CANCELLED
2309 --added on 25-jul-2001 by smadathi
2310
2311 -- removed the closed section override code added by smadathi
2312 -- this will now be checked in IGS_EN_GEN_015.get_usec_status
2313 -- amuthu 02-Jul-2002
2314
2315 IF (USEC_CUR_ROW.UNIT_SECTION_STATUS IN ('CANCELLED','PLANNED','NOT_OFFERED') ) THEN
2316 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SS_CANNOT_WAITLIST');
2317 IGS_GE_MSG_STACK.ADD;
2318 APP_EXCEPTION.RAISE_EXCEPTION;
2319 END IF;
2320
2321 -- multiple unit sections validation is performed for SWAP in add units api
2322 -- and it should not be done here as this is done in a autonomus txn.
2323 IF NVL(X_SS_SOURCE_IND,'N') <> 'S' THEN
2324
2325 /*checking for multiple versions of same unit section, if exists raise an exception*/
2326 --processing for same_teaching_period at unit section level added as a part of Repeat and Reeenrollment build
2327 IGS_EN_VAL_SUA.validate_mus(p_person_id => x_person_id,
2328 p_course_cd => x_course_cd,
2329 p_uoo_id => x_uoo_id);
2330
2331
2332 END IF; -- IF NVL(X_SS_SOURCE_IND,'N') <> 'S' OR (IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NOT NULL AND IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN') THEN
2333 END IF;
2334
2335 IF (p_action = 'INSERT') THEN
2336 -- Call all the procedures related to Before Insert.
2337
2338 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
2339 p_updating => FALSE,
2340 p_deleting => FALSE );
2341
2342 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2343
2344 BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
2345 p_updating => FALSE,
2346 p_deleting => FALSE);
2347
2348 END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2349
2350 IF Get_PK_For_Validation (
2351 NEW_REFERENCES.person_id,
2352 NEW_REFERENCES.course_cd,
2353 NEW_REFERENCES.uoo_id
2354 ) THEN
2355 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
2356 IGS_GE_MSG_STACK.ADD;
2357 APP_EXCEPTION.RAISE_EXCEPTION;
2358 END IF;
2359
2360 Check_Constraints;
2361 Check_Parent_Existance;
2362 Check_Uniqueness;
2363 -- For Enhancement Bug 1287292
2364 -- Commented the call for the bug Fix:2366438
2365 -- chk_enrollment_max;
2366
2367
2368 ELSIF (p_action = 'UPDATE') THEN
2369
2370 -- Call all the procedures related to Before Update.
2371 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
2372 p_updating => TRUE,
2373 p_deleting => FALSE);
2374 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2375
2376 BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
2377 p_updating => TRUE ,
2378 p_deleting => FALSE);
2379 END IF;--IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2380
2381 Check_Constraints;
2382 Check_Parent_Existance;
2383 Check_Uniqueness;
2384 -- For Enhancement Bug 1287292
2385 -- Commented the call for the bug Fix:2366438
2386 --chk_enrollment_max;
2387
2388
2389
2390 ELSIF (p_action = 'DELETE') THEN
2391 -- Call all the procedures related to Before Delete.
2392 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
2393 p_updating => FALSE ,
2394 p_deleting => TRUE );
2395 BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
2396 p_updating => FALSE ,
2397 p_deleting => TRUE );
2398 Check_Child_Existance;
2399 ELSIF (p_action = 'VALIDATE_INSERT') THEN
2400 OPEN cur_rowid;
2401 FETCH cur_rowid INTO l_rowid;
2402 -- for Bug 1575677
2403 IF cur_rowid%NOTFOUND THEN
2404 IF Get_PK_For_Validation (
2405 NEW_REFERENCES.person_id,
2406 NEW_REFERENCES.course_cd,
2407 NEW_REFERENCES.uoo_id
2408 ) THEN
2409 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
2410 IGS_GE_MSG_STACK.ADD;
2411 APP_EXCEPTION.RAISE_EXCEPTION;
2412 END IF;
2413 Check_Constraints;
2414 Check_Uniqueness;
2415 END IF;
2416 CLOSE cur_rowid;
2417 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
2418 Check_Constraints;
2419 Check_Uniqueness;
2420 ELSIF (p_action = 'VALIDATE_DELETE') THEN
2421 Check_Child_Existance;
2422 END IF;
2423
2424 END Before_DML;
2425
2426
2427 PROCEDURE After_DML (
2428 p_action IN VARCHAR2,
2429 x_rowid IN VARCHAR2
2430 ) AS
2431 -- Getting the sua details for all waitlisted students, pmarada bug 2526021
2432 -- ptandon 03-SEP-2003 Added logic to re-sequence the waitlisted
2433 -- students when waitlist priorities/preferences
2434 -- are defined in the system.
2435 -- stutta 17-NOV-2003 Added code to create a term record, if one doesn't
2436 -- already exist, when a student enrolls or waitlists
2437 -- for a unit. Part of Term Records Build
2438 -- vkarthik 13-Feb-04 Waitlist position re-sequencing logic was not present
2439 -- in 'Insert' action, added as part bug 3433446
2440 -- stutta 10-MAR-2004 Added call to igs_en_gen_015.get_academic_cal to get acad cal
2441 -- instance and pass it to c_teach_to_load cursor. BUG# 3481403
2442 -- stutta 16-Mar-2004 Passing new parameter p_update_rec in call
2443 -- to igs_en_spa_terms_api.create_update_term_rec. Bug # 3421436
2444 -- stutta 11-Jan-2004 Modified c_teach_to_load to allow term record creation for terms
2445 -- which are subordinate to any instance of the academic cal type.
2446 -- Bug #4016319
2447 -- ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
2448 -- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
2449 -- bdeviset 02-Mar-2006 Modifed cursor c_spa_terms for bug# 5073761
2450
2451 CURSOR c_sua_details(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
2452 SELECT * FROM igs_en_su_attempt
2453 WHERE uoo_id = cp_uoo_id AND
2454 unit_attempt_status = 'WAITLISTED'
2455 ORDER BY administrative_priority ASC;
2456
2457 -- Cursor to lock parent unit section record. Bug# 3052426 - ptandon
2458 CURSOR c_unit_sec_lock(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2459 SELECT uoo_id
2460 FROM igs_ps_unit_ofr_opt
2461 WHERE uoo_id = cp_uoo_id
2462 FOR UPDATE;
2463
2464 CURSOR c_repeatable_unit (cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
2465 cp_uv_version_number igs_ps_unit_ver.version_number%TYPE) IS
2466 SELECT uv.repeatable_ind
2467 FROM igs_ps_unit_ver uv
2468 WHERE uv.unit_cd = cp_unit_cd AND
2469 uv.version_number = cp_uv_version_number;
2470 CURSOR c_renrollcheck(cp_person_id igs_en_su_attempt.person_id%TYPE,
2471 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2472 cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2473 SELECT 'x'
2474 FROM igs_en_su_attempt sua
2475 WHERE person_id=cp_person_id
2476 AND uoo_id=cp_uoo_id
2477 AND unit_attempt_status ='COMPLETED'
2478 AND course_cd <> cp_course_cd
2479 AND EXISTS (SELECT 'x'
2480 FROM IGS_PS_STDNT_UNT_TRN sut
2481 WHERE sut.person_id = cp_person_id
2482 AND sut.uoo_id = cp_uoo_id
2483 AND sut.transfer_course_cd = cp_course_cd
2484 AND sut.course_cd = sua.course_cd
2485 ) ;
2486
2487 CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490 cp_version_number igs_en_su_attempt.version_number%TYPE,
2491 cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492
2493 SELECT 'x'
2494 FROM igs_en_su_attempt sua
2495 WHERE person_id=cp_person_id
2496 AND unit_cd=cp_unit_cd
2497 AND version_number=cp_version_number
2498 AND unit_attempt_status IN ('ENROLLED','DUPLICATE','COMPLETED','DISCONTIN')
2499 AND (
2500 (course_cd <> cp_course_cd
2501 AND NOT EXISTS (SELECT 'x'
2502 FROM IGS_PS_STDNT_UNT_TRN sut
2503 WHERE sut.person_id = cp_person_id
2504 and sut.uoo_id = cp_uoo_id
2505 AND sut.transfer_course_cd = sua.course_cd
2506 AND sut.course_cd = cp_course_cd)
2507 )
2508 OR
2509 (course_cd = cp_course_cd AND uoo_id<>cp_uoo_id)); --so that the newly added sua is not counted
2510
2511 -- find all term calendars assiciated with the teach calendar of the unit and subordinate to any instance
2512 -- of the academic calendar of the student program attempt.
2513 CURSOR c_teach_to_load(cp_acad_cal_type VARCHAR2) IS
2514 SELECT ttl.load_cal_type, ttl.load_ci_sequence_number
2515 FROM IGS_CA_TEACH_TO_LOAD_V ttl,
2516 IGS_EN_STDNT_PS_ATT spa,
2517 IGS_CA_INST_REL cir
2518 WHERE ttl.teach_cal_type= new_references.cal_type
2519 AND ttl.teach_ci_sequence_number = new_references.ci_sequence_number
2520 AND spa.person_id = new_references.person_id
2521 AND spa.course_cd = new_references.course_cd
2522 AND spa.cal_type = cp_acad_cal_type
2523 AND cir.sup_cal_type = spa.cal_type
2524 AND cir.sub_cal_type = ttl.load_cal_type
2525 AND cir.sub_ci_sequence_number = ttl.load_ci_sequence_number;
2526
2527 CURSOR c_term (p_person_id number, p_course_cd varchar2,
2528 p_term_cal_type varchar2, p_term_ci_sequence_number number) IS
2529 SELECT 'x'
2530 FROM igs_en_spa_terms spat, igs_en_stdnt_ps_att curspa, igs_ps_ver curcv,
2531 igs_en_stdnt_ps_att spa, igs_ps_ver cv
2532 WHERE spat.person_id = p_person_id
2533 AND term_cal_type = p_term_cal_type AND term_sequence_number = p_term_ci_sequence_number
2534 AND curspa.person_id=spat.person_id AND curspa.course_cd=p_course_Cd
2535 AND curcv.course_Cd=curspa.course_cd AND curcv.version_number=curspa.version_number
2536 AND spa.person_id=spat.person_id AND spat.program_cd=spa.course_cd
2537 AND cv.course_Cd=spa.course_cd AND cv.version_number=spa.version_number
2538 AND cv.course_type = curcv.course_type
2539 AND (NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'Y' OR spat.program_cd = p_course_cd );
2540
2541
2542 CURSOR c_sca IS
2543 SELECT attendance_type, attendance_mode, key_program, fee_cat, igs_pr_class_std_id,
2544 cal_type, location_cd, coo_id, version_number, student_confirmed_ind
2545 FROM IGS_EN_STDNT_PS_ATT
2546 WHERE person_id = new_references.person_id
2547 AND course_cd = new_references.course_cd;
2548
2549 rec_sca c_sca%ROWTYPE;
2550 l_message_name VARCHAR2(200);
2551
2552 CURSOR c_other_key_recs ( cp_person_id IGS_EN_SPA_TERMS.PERSON_ID%TYPE,
2553 cp_term_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2554 cp_term_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
2555 cp_program_cd IGS_PS_VER.COURSE_CD%TYPE,
2556 cp_acad_cal_type IGS_EN_SPA_TERMS.ACAD_CAL_TYPE%TYPE) IS
2557 SELECT 'X'
2558 FROM IGS_EN_SPA_TERMS
2559 WHERE person_id = cp_person_id
2560 AND program_cd <> cp_program_cd
2561 AND term_cal_type = cp_term_cal_type
2562 AND term_sequence_number = cp_term_sequence_number
2563 AND acad_cal_type = cp_acad_cal_type
2564 AND key_program_flag = 'Y';
2565
2566 CURSOR c_spa_terms (p_person_id IN number, p_course_cd IN varchar2,
2567 p_cal_type IN varchar2, p_ci_sequence_number IN number) IS
2568 SELECT spat.ROWID ROW_ID, spat.*
2569 FROM igs_en_spa_terms spat
2570 WHERE spat.person_id = p_person_id
2571 AND program_cd=p_course_cd AND plan_sht_status IN ('PLAN', 'NONE')
2572 AND EXISTS
2573 (SELECT load_cal_type,load_ci_sequence_number FROM igs_ca_load_to_teach_v ltCal
2574 WHERE ltCal.teach_cal_type = p_cal_type AND ltCal.teach_ci_sequence_number = p_ci_sequence_number
2575 AND ltCal.load_cal_type = spat.term_cal_type AND ltCal.load_ci_sequence_number = spat.term_sequence_number) ;
2576
2577 v_dummy c_other_key_recs%ROWTYPE;
2578
2579
2580 l_sua_details c_sua_details%ROWTYPE;
2581 l_cnt igs_en_su_attempt.administrative_priority%TYPE;
2582 l_repeatable_unit igs_ps_unit_ver.repeatable_ind%TYPE;
2583 l_act_enr NUMBER;
2584 l_dummy VARCHAR2(10);
2585 l_dummy_value NUMBER;
2586 l_return_status VARCHAR2(100);
2587 l_sup_sub_unit_status igs_en_su_attempt.unit_attempt_status%TYPE;
2588
2589 BEGIN
2590
2591
2592 IF (p_action = 'UPDATE') THEN
2593 -- Call all the procedures related to After Update.
2594 -- AfterRowInsertUpdate5( p_updating => TRUE ); Removed the this procedure from the tbh in the 115.73 version of the file,
2595 -- the validation moved to the igsss09b.pls at the time of enrolling a unit. pmarada, 2385096
2596 --adding the call to ENR_SUA_SUP_SUB_VAL to validate the superior subordinate unit attempt status
2597
2598 IF old_references.unit_attempt_status <> new_references.unit_attempt_status THEN
2599
2600 IF NOT IGS_EN_SUA_API.ENR_SUA_SUP_SUB_VAL(new_references.person_id,
2601 new_references.course_cd,
2602 new_references.uoo_id,
2603 new_references.unit_attempt_status,
2604 l_sup_sub_unit_status) THEN
2605
2606 IF l_sup_sub_unit_status IS NOT NULL THEN
2607 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_INVALID_SUP_SUB');
2608 FND_MESSAGE.SET_TOKEN ('CONSTAT', new_references.unit_attempt_status);
2609 FND_MESSAGE.SET_TOKEN ('SSTAT',l_sup_sub_unit_status);
2610 ELSE
2611 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_INVALID_SUP');
2612 END IF;
2613 IGS_GE_MSG_STACK.ADD;
2614 APP_EXCEPTION.RAISE_EXCEPTION;
2615 END IF;
2616
2617 END IF; --IF old_references.unit_attempt_status <> new_references.unit_attempt_status THEN
2618
2619 END IF; -- end of IF (p_action = 'UPDATE') THEN
2620
2621 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2622
2623
2624 l_rowid := x_rowid;
2625 IF (p_action = 'INSERT') THEN
2626 -- Call all the procedures related to After Insert.
2627 -- AfterRowInsertUpdate5( p_inserting => TRUE ); Removed the this procedure from the tbh in the 115.73 version of the file,
2628 -- the validation moved to the igsss09b.pls at the time of enrolling a unit.,pmarada, 2385096
2629
2630 IF NOT IGS_EN_SUA_API.ENR_SUA_SUP_SUB_VAL(new_references.person_id,
2631 new_references.course_cd,
2632 new_references.uoo_id,
2633 new_references.unit_attempt_status,
2634 l_sup_sub_unit_status) THEN
2635 IF l_sup_sub_unit_status IS NOT NULL THEN
2636 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_INVALID_SUP_SUB');
2637 FND_MESSAGE.SET_TOKEN ('CONSTAT', new_references.unit_attempt_status);
2638 FND_MESSAGE.SET_TOKEN ('SSTAT',l_sup_sub_unit_status);
2639 ELSE
2640 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_INVALID_SUP');
2641 END IF;
2642 IGS_GE_MSG_STACK.ADD;
2643 APP_EXCEPTION.RAISE_EXCEPTION;
2644
2645 END IF;
2646 AfterRowInsertUpdateDelete3 ( p_inserting => TRUE,
2647 p_updating => FALSE,
2648 p_deleting => FALSE);
2649
2650 --For enhancement 2043044,making a call to UPD_MAT_MRADM_CAT_TERMS procedure
2651 IGS_EN_GEN_003.UPD_MAT_MRADM_CAT_TERMS(new_references.person_id,
2652 new_references.course_cd,
2653 new_references.unit_attempt_status,
2654 new_references.cal_type,
2655 new_references.ci_sequence_number
2656 ) ;
2657 -- Code added as part of Waitlist Enhancements Build Bug# 3052426
2658 -- To re-sequence th students after calculating priority/preference weightages
2659 -- of the students in context - ptandon
2660 IF new_references.wlst_priority_weight_num IS NOT NULL
2661 AND new_references.wlst_preference_weight_num IS NOT NULL
2662 AND new_references.unit_attempt_status = 'WAITLISTED'
2663 AND new_references.administrative_priority IS NULL
2664 THEN
2665 Igs_En_Wlst_Gen_proc.enrp_wlst_assign_pos(new_references.person_id,new_references.course_cd,new_references.uoo_id);
2666 END IF;
2667
2668 ELSIF (p_action = 'UPDATE') THEN
2669
2670 -- modified this condition to include DROP also for bug#4864437
2671 -- this code is being commented here but will be replicated in swap page and Drop page
2672 -- SwapAMImpl.java :swapSubmit , StdDropAMImpl.java:dropSubmit
2673 IF NVL(igs_en_su_attempt_pkg.pkg_source_of_drop,'NULL') NOT IN ( 'SWAP','DROP') THEN
2674
2675 AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
2676 p_updating => TRUE,
2677 p_deleting => FALSE );
2678
2679
2680 --enrollment processes build bug#1832130, pg No:24 s1a version of DLD
2681 update_reserved_seat('UPDATE');
2682
2683 --For enhancement 2043044,making a call to UPD_MAT_MRADM_CAT_TERMS procedure
2684 IGS_EN_GEN_003.UPD_MAT_MRADM_CAT_TERMS(new_references.person_id,
2685 new_references.course_cd,
2686 new_references.unit_attempt_status,
2687 new_references.cal_type,
2688 new_references.ci_sequence_number
2689 ) ;
2690
2691 END IF;
2692
2693 AfterRowUpdate4 ( p_inserting => FALSE,
2694 p_updating => TRUE,
2695 p_deleting => FALSE);
2696
2697 -- when a waitlisted student is enrolled/dropped from the unit section then
2698 -- Re-sequence the waitlisted positions for the succeeding waitlisted students. pmarada, bug 2526021
2699 IF (OLD_REFERENCES.UNIT_ATTEMPT_STATUS = 'WAITLISTED' AND
2700 NVL(old_references.unit_attempt_status,'WAITLISTED') <> new_references.unit_attempt_status) THEN
2701 -- Lock the parent unit section record before updating unit attempt records - Bug# 3052426
2702 OPEN c_unit_sec_lock(new_references.uoo_id);
2703
2704 l_cnt := 1;
2705 -- For each uoo_id updating the sua with administrative_priority
2706 FOR l_sua_details IN c_sua_details(new_references.uoo_id) LOOP
2707 IF l_cnt <> NVL(l_sua_details.administrative_priority,0) THEN
2708 l_sua_details.administrative_priority := l_cnt;
2709 BEGIN
2710 igs_en_su_attempt_pkg.update_row(
2711 X_ROWID => l_sua_details.row_id ,
2712 X_PERSON_ID => l_sua_details.person_id ,
2713 X_COURSE_CD => l_sua_details.course_cd ,
2714 X_UNIT_CD => l_sua_details.unit_cd ,
2715 X_CAL_TYPE => l_sua_details.cal_type ,
2716 X_CI_SEQUENCE_NUMBER => l_sua_details.ci_sequence_number ,
2717 X_VERSION_NUMBER => l_sua_details.version_number ,
2718 X_LOCATION_CD => l_sua_details.location_cd ,
2719 X_UNIT_CLASS => l_sua_details.unit_class ,
2720 X_CI_START_DT => l_sua_details.ci_start_dt ,
2721 X_CI_END_DT => l_sua_details.ci_end_dt ,
2722 X_UOO_ID => l_sua_details.uoo_id ,
2723 X_ENROLLED_DT => l_sua_details.enrolled_dt ,
2724 X_UNIT_ATTEMPT_STATUS => l_sua_details.unit_attempt_status ,
2725 X_ADMINISTRATIVE_UNIT_STATUS => l_sua_details.administrative_unit_status ,
2726 X_DISCONTINUED_DT => l_sua_details.discontinued_dt ,
2727 X_RULE_WAIVED_DT => l_sua_details.rule_waived_dt ,
2728 X_RULE_WAIVED_PERSON_ID => l_sua_details.rule_waived_person_id ,
2729 X_NO_ASSESSMENT_IND => l_sua_details.no_assessment_ind ,
2730 X_SUP_UNIT_CD => l_sua_details.sup_unit_cd ,
2731 X_SUP_VERSION_NUMBER => l_sua_details.sup_version_number ,
2732 X_EXAM_LOCATION_CD => l_sua_details.exam_location_cd ,
2733 X_ALTERNATIVE_TITLE => l_sua_details.alternative_title ,
2734 X_OVERRIDE_ENROLLED_CP => l_sua_details.override_enrolled_cp ,
2735 X_OVERRIDE_EFTSU => l_sua_details.override_eftsu ,
2736 X_OVERRIDE_ACHIEVABLE_CP => l_sua_details.override_achievable_cp ,
2737 X_OVERRIDE_OUTCOME_DUE_DT => l_sua_details.override_outcome_due_dt ,
2738 X_OVERRIDE_CREDIT_REASON => l_sua_details.override_credit_reason ,
2739 X_ADMINISTRATIVE_PRIORITY => l_sua_details.administrative_priority ,
2740 X_WAITLIST_DT => l_sua_details.waitlist_dt ,
2741 X_DCNT_REASON_CD => l_sua_details.dcnt_reason_cd ,
2742 X_MODE => 'R' ,
2743 X_GS_VERSION_NUMBER => l_sua_details.gs_version_number ,
2744 X_ENR_METHOD_TYPE => l_sua_details.enr_method_type ,
2745 X_FAILED_UNIT_RULE => l_sua_details.failed_unit_rule ,
2746 X_CART => l_sua_details.cart ,
2747 X_RSV_SEAT_EXT_ID => l_sua_details.rsv_seat_ext_id ,
2748 X_ORG_UNIT_CD => l_sua_details.org_unit_cd ,
2749 X_SESSION_ID => l_sua_details.session_id,
2750 X_GRADING_SCHEMA_CODE => l_sua_details.grading_schema_code ,
2751 X_DEG_AUD_DETAIL_ID => l_sua_details.deg_aud_detail_id ,
2752 X_STUDENT_CAREER_TRANSCRIPT => l_sua_details.student_career_transcript,
2753 X_STUDENT_CAREER_STATISTICS => l_sua_details.student_career_statistics,
2754 x_waitlist_manual_ind => 'N', --Bug ID: 2554109 added by adhawan
2755 X_ATTRIBUTE_CATEGORY => l_sua_details.attribute_category,
2756 X_ATTRIBUTE1 => l_sua_details.attribute1,
2757 X_ATTRIBUTE2 => l_sua_details.attribute2,
2758 X_ATTRIBUTE3 => l_sua_details.attribute3,
2759 X_ATTRIBUTE4 => l_sua_details.attribute4,
2760 X_ATTRIBUTE5 => l_sua_details.attribute5,
2761 X_ATTRIBUTE6 => l_sua_details.attribute6,
2762 X_ATTRIBUTE7 => l_sua_details.attribute7,
2763 X_ATTRIBUTE8 => l_sua_details.attribute8,
2764 X_ATTRIBUTE9 => l_sua_details.attribute9,
2765 X_ATTRIBUTE10 => l_sua_details.attribute10,
2766 X_ATTRIBUTE11 => l_sua_details.attribute11,
2767 X_ATTRIBUTE12 => l_sua_details.attribute12,
2768 X_ATTRIBUTE13 => l_sua_details.attribute13,
2769 X_ATTRIBUTE14 => l_sua_details.attribute14,
2770 X_ATTRIBUTE15 => l_sua_details.attribute15,
2771 X_ATTRIBUTE16 => l_sua_details.attribute16,
2772 X_ATTRIBUTE17 => l_sua_details.attribute17,
2773 X_ATTRIBUTE18 => l_sua_details.attribute18,
2774 X_ATTRIBUTE19 => l_sua_details.attribute19,
2775 X_ATTRIBUTE20 => l_sua_details.attribute20,
2776 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
2777 X_WLST_PRIORITY_WEIGHT_NUM => l_sua_details.wlst_priority_weight_num,
2778 X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_details.wlst_preference_weight_num,
2779 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
2780 X_CORE_INDICATOR_CODE => l_sua_details.core_indicator_code,
2781 X_UPD_AUDIT_FLAG => l_sua_details.UPD_AUDIT_FLAG ,
2782 X_SS_SOURCE_IND => l_sua_details.SS_SOURCE_IND
2783 );
2784 END;
2785 END IF;
2786 l_cnt := l_cnt + 1;
2787 END LOOP;
2788 CLOSE c_unit_sec_lock;
2789 END IF;
2790 -- end of the code added as part of the bug 2526021
2791
2792 -- Code added as part of Waitlist Enhancements Build Bug# 3052426
2793 -- To re-sequence th students after calculating priority/preference weightages
2794 -- of the students in context - ptandon
2795 IF new_references.wlst_priority_weight_num IS NOT NULL
2796 AND new_references.wlst_preference_weight_num IS NOT NULL
2797 AND new_references.unit_attempt_status = 'WAITLISTED'
2798 AND new_references.administrative_priority IS NULL
2799 THEN
2800 Igs_En_Wlst_Gen_proc.enrp_wlst_assign_pos(new_references.person_id,new_references.course_cd,new_references.uoo_id);
2801 END IF;
2802
2803 END IF;
2804
2805 END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2806
2807 IF (p_action = 'DELETE') THEN
2808 -- Call all the procedures related to After Delete.
2809 AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
2810 p_updating => FALSE,
2811 p_deleting => TRUE );
2812 update_reserved_seat('DELETE');
2813 -- This block of code is included to ensure that the counter gets
2814 -- decremented even if an enrolled unit attempt is deleted from the
2815 -- unit attempt window. This works as an additional enhancement for
2816 -- the bug #1525863
2817 IF ( (old_references.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM')) AND
2818 p_action = 'DELETE' ) THEN
2819
2820 igs_en_sua_api.upd_enrollment_counts( 'DELETE',
2821 old_references,
2822 new_references
2823 );
2824 END IF;
2825
2826 END IF; -- IF (p_action = 'DELETE') THEN
2827
2828 --validate newly enrolled units and check if the person has any active
2829 --unit attempts in diff. unit sections of the same unit across program/careers
2830 IF new_references.unit_attempt_status ='ENROLLED' THEN
2831 OPEN c_repeatable_unit(new_references.unit_cd,new_references.version_number);
2832 FETCH c_repeatable_unit INTO l_repeatable_unit;
2833 CLOSE c_repeatable_unit;
2834 IF l_repeatable_unit='X' THEN
2835
2836 OPEN c_renrollcheck(new_references.person_id,new_references.uoo_id,new_references.course_cd);
2837 FETCH c_renrollcheck INTO l_dummy;
2838 IF c_renrollcheck%FOUND THEN
2839
2840 CLOSE c_renrollcheck;
2841 ELSE
2842 CLOSE c_renrollcheck;
2843
2844 OPEN c_conflict_suas(new_references.person_id,new_references.uoo_id,new_references.unit_cd,
2845 new_references.version_number,new_references.course_cd);
2846 FETCH c_conflict_suas INTO l_dummy;
2847 IF c_conflict_suas%FOUND THEN
2848 CLOSE c_conflict_suas;
2849 fnd_message.set_name('IGS', 'IGS_EN_REP_REENR_NOT_ALWD');
2850 IGS_GE_MSG_STACK.ADD;
2851 APP_EXCEPTION.RAISE_EXCEPTION;
2852 END IF;
2853 CLOSE c_conflict_suas;
2854 END IF; --renroll check
2855 END IF; --l_repeatable_unit
2856 END IF;
2857
2858 IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2859
2860 IF (p_action = 'UPDATE') THEN
2861 IF (old_references.unit_attempt_status <> new_references.unit_attempt_status) AND
2862 (new_references.unit_attempt_status IN ('DROPPED', 'DISCONTIN')) THEN
2863 l_new_cp := NULL;
2864 IF l_load_cal_type IS NOT NULL THEN
2865 --Get the total enrolled cp after unit section got discontinue.
2866 IGS_EN_PRC_LOAD.enrp_get_prg_eftsu_cp(
2867 p_person_id =>new_references.person_id,
2868 p_course_cd =>new_references.course_cd,
2869 p_cal_type =>l_load_cal_type,
2870 p_sequence_number =>l_load_seq_num,
2871 p_eftsu_total =>l_dummy_value,
2872 p_credit_points => l_new_cp);
2873 --Api Raises the drop business event.
2874 igs_ss_en_wrappers.drop_all_workflow(
2875 p_uoo_idS =>new_references.uoo_id,
2876 p_person_id =>new_references.person_id,
2877 p_load_cal_type =>l_load_cal_type,
2878 p_load_sequence_number =>l_load_seq_num,
2879 p_program_cd =>new_references.course_cd,
2880 p_return_status =>l_return_status,
2881 p_drop_date =>new_references.discontinued_dt,
2882 p_old_cp =>l_old_cp,
2883 p_new_cp =>l_new_cp);
2884 END IF;
2885 END IF;
2886 END IF;
2887
2888 IF new_references.unit_attempt_status IN ('WAITLISTED', 'ENROLLED','UNCONFIRM') THEN
2889 OPEN c_sca;
2890 FETCH c_sca into rec_sca;
2891 CLOSE c_sca;
2892 IF (rec_sca.student_confirmed_ind = 'Y') THEN
2893 FOR rec_tl in c_teach_to_load(rec_sca.cal_type)
2894 LOOP
2895 IF NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'N' OR
2896 ('PRIMARY'=igs_en_spa_terms_api.get_spat_primary_prg (new_references.person_id, new_references.course_cd,
2897 rec_tl.load_cal_type, rec_tl.load_ci_sequence_number)) THEN
2898
2899 OPEN c_term (new_references.person_id, new_references.course_cd,
2900 rec_tl.load_cal_type, rec_tl.load_ci_sequence_number);
2901 FETCH c_term INTO l_dummy;
2902 l_message_name := NULL;
2903
2904 IF c_term%NOTFOUND THEN
2905 igs_en_spa_terms_api.create_update_term_rec (
2906 p_person_id => new_references.person_id,
2907 p_program_cd => new_references.course_cd,
2908 p_term_cal_type => rec_tl.load_cal_type ,
2909 p_term_sequence_number => rec_tl.load_ci_sequence_number ,
2910 p_ripple_frwrd => FALSE, -- ripple forward
2911 p_message_name => l_message_name,
2912 p_update_rec => TRUE);
2913 END IF;
2914 CLOSE c_term;
2915 IF l_message_name IS NOT NULL THEN
2916 fnd_message.set_name('IGS', l_message_name);
2917 IGS_GE_MSG_STACK.ADD;
2918 app_exception.raise_exception;
2919 END IF;
2920 END IF;
2921 END LOOP;
2922 END IF;
2923 END IF;
2924
2925 IF p_action in ('INSERT','UPDATE') AND
2926 old_references.unit_attempt_status <> new_references.unit_attempt_status AND
2927 new_references.unit_attempt_status IN ('ENROLLED','WAITLISTED') THEN
2928
2929 FOR rec_spa_terms IN
2930 c_spa_terms(new_references.person_id, new_references.course_cd, new_references.cal_type , new_references.ci_sequence_number)
2931 LOOP
2932 igs_en_spa_terms_pkg.update_row(
2933 x_rowid =>rec_spa_terms.row_id,
2934 x_term_record_id =>rec_spa_terms.term_record_id,
2935 x_person_id =>rec_spa_terms.person_id,
2936 x_program_cd =>rec_spa_terms.program_cd,
2937 x_program_version =>rec_spa_terms.program_version,
2938 x_acad_cal_type =>rec_spa_terms.acad_cal_type,
2939 x_term_cal_type =>rec_spa_terms.term_cal_type,
2940 x_term_sequence_number =>rec_spa_terms.term_sequence_number,
2941 x_key_program_flag =>rec_spa_terms.key_program_flag,
2942 x_location_cd =>rec_spa_terms.location_cd,
2943 x_attendance_mode =>rec_spa_terms.attendance_mode,
2944 x_attendance_type =>rec_spa_terms.attendance_type,
2945 x_fee_cat =>rec_spa_terms.fee_cat,
2946 x_coo_id =>rec_spa_terms.coo_id,
2947 x_class_standing_id =>rec_spa_terms.class_standing_id,
2948 x_attribute_category =>rec_spa_terms.attribute_category,
2949 x_attribute1 =>rec_spa_terms.attribute1,
2950 x_attribute2 =>rec_spa_terms.attribute2,
2951 x_attribute3 =>rec_spa_terms.attribute3,
2952 x_attribute4 =>rec_spa_terms.attribute4,
2953 x_attribute5 =>rec_spa_terms.attribute5,
2954 x_attribute6 =>rec_spa_terms.attribute6,
2955 x_attribute7 =>rec_spa_terms.attribute7,
2956 x_attribute8 =>rec_spa_terms.attribute8,
2957 x_attribute9 =>rec_spa_terms.attribute9,
2958 x_attribute10 =>rec_spa_terms.attribute10,
2959 x_attribute11 =>rec_spa_terms.attribute11,
2960 x_attribute12 =>rec_spa_terms.attribute12,
2961 x_attribute13 =>rec_spa_terms.attribute13,
2962 x_attribute14 =>rec_spa_terms.attribute14,
2963 x_attribute15 =>rec_spa_terms.attribute15,
2964 x_attribute16 =>rec_spa_terms.attribute16,
2965 x_attribute17 =>rec_spa_terms.attribute17,
2966 x_attribute18 =>rec_spa_terms.attribute18,
2967 x_attribute19 =>rec_spa_terms.attribute19,
2968 x_attribute20 =>rec_spa_terms.attribute20,
2969 x_mode =>'R',
2970 x_plan_sht_status =>'SKIP');
2971 END LOOP;
2972 END IF;
2973
2974 END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2975
2976 END After_DML;
2977 --
2978 PROCEDURE INSERT_ROW (
2979 X_ROWID IN OUT NOCOPY VARCHAR2,
2980 X_PERSON_ID IN NUMBER,
2981 X_COURSE_CD IN VARCHAR2,
2982 X_UNIT_CD IN VARCHAR2,
2983 X_CAL_TYPE IN VARCHAR2,
2984 X_CI_SEQUENCE_NUMBER IN NUMBER,
2985 X_VERSION_NUMBER IN NUMBER,
2986 X_LOCATION_CD IN VARCHAR2,
2987 X_UNIT_CLASS IN VARCHAR2,
2988 X_CI_START_DT IN DATE,
2989 X_CI_END_DT IN DATE,
2990 X_UOO_ID IN NUMBER,
2991 X_ENROLLED_DT IN DATE,
2992 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
2993 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
2994 X_DISCONTINUED_DT IN DATE,
2995 X_RULE_WAIVED_DT IN DATE,
2996 X_RULE_WAIVED_PERSON_ID IN NUMBER,
2997 X_NO_ASSESSMENT_IND IN VARCHAR2,
2998 X_SUP_UNIT_CD IN VARCHAR2,
2999 X_SUP_VERSION_NUMBER IN NUMBER,
3000 X_EXAM_LOCATION_CD IN VARCHAR2,
3001 X_ALTERNATIVE_TITLE IN VARCHAR2,
3002 X_OVERRIDE_ENROLLED_CP IN NUMBER,
3003 X_OVERRIDE_EFTSU IN NUMBER,
3004 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
3005 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
3006 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
3007 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
3008 X_WAITLIST_DT IN DATE,
3009 X_DCNT_REASON_CD IN VARCHAR2,
3010 X_MODE IN VARCHAR2 ,
3011 x_org_id IN NUMBER,
3012 X_GS_VERSION_NUMBER IN NUMBER ,
3013 X_ENR_METHOD_TYPE IN VARCHAR2 ,
3014 X_FAILED_UNIT_RULE IN VARCHAR2 ,
3015 X_CART IN VARCHAR2 ,
3016 X_RSV_SEAT_EXT_ID IN NUMBER ,
3017 X_org_unit_cd IN VARCHAR2 ,
3018 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
3019 x_subtitle IN VARCHAR2 ,
3020 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3021 x_session_id IN NUMBER ,
3022 x_deg_aud_detail_id IN NUMBER ,
3023 x_student_career_transcript IN VARCHAR2 ,
3024 x_student_career_statistics IN VARCHAR2 ,
3025 X_WAITLIST_MANUAL_IND IN VARCHAR2,--Bug ID: 2554109 added by adhawan
3026 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
3027 X_ATTRIBUTE1 IN VARCHAR2 ,
3028 X_ATTRIBUTE2 IN VARCHAR2 ,
3029 X_ATTRIBUTE3 IN VARCHAR2 ,
3030 X_ATTRIBUTE4 IN VARCHAR2 ,
3031 X_ATTRIBUTE5 IN VARCHAR2 ,
3032 X_ATTRIBUTE6 IN VARCHAR2 ,
3033 X_ATTRIBUTE7 IN VARCHAR2 ,
3034 X_ATTRIBUTE8 IN VARCHAR2 ,
3035 X_ATTRIBUTE9 IN VARCHAR2 ,
3036 X_ATTRIBUTE10 IN VARCHAR2 ,
3037 X_ATTRIBUTE11 IN VARCHAR2 ,
3038 X_ATTRIBUTE12 IN VARCHAR2 ,
3039 X_ATTRIBUTE13 IN VARCHAR2 ,
3040 X_ATTRIBUTE14 IN VARCHAR2 ,
3041 X_ATTRIBUTE15 IN VARCHAR2 ,
3042 X_ATTRIBUTE16 IN VARCHAR2 ,
3043 X_ATTRIBUTE17 IN VARCHAR2 ,
3044 X_ATTRIBUTE18 IN VARCHAR2 ,
3045 X_ATTRIBUTE19 IN VARCHAR2 ,
3046 x_ATTRIBUTE20 IN VARCHAR2 ,
3047 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3048 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER ,
3049 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER ,
3050 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3051 X_CORE_INDICATOR_CODE IN VARCHAR2,
3052 X_UPD_AUDIT_FLAG IN VARCHAR2,
3053 X_SS_SOURCE_IND IN VARCHAR2
3054 ) AS
3055 /*------------------------------------------------------------------------------------------------------------------------------------------
3056 knaraset 10-Jul-2003 Added unique key along with pk to check existance of Dropped units
3057 svanukur 28-APR-03 changed the where clauses to reflect teh new Primary key
3058 as part of MUS build, # 2829262
3059 sbaliga 13-feb-2002 assigned igs_ge_gen_003.get_org_id to x_org_id in call to before_dml
3060 as part of SWCR006 build.
3061 ---------------------------------------------------------------------------------------*/
3062 -- Changing the parameters X_UNIT_CD, X_CAL_TYPE, X_CI_SEQUENCE_NUMBER TO NEW_REFERENCES
3063 -- in response to the bug 1766230. For further detils please refer to version 115.19 of IGSEI36B.pls
3064
3065 CURSOR C IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
3066 WHERE PERSON_ID = X_PERSON_ID
3067 AND COURSE_CD = X_COURSE_CD
3068 AND UOO_ID = X_UOO_ID;
3069
3070
3071 CURSOR cur_rowid IS
3072 SELECT ROWID
3073 FROM IGS_EN_SU_ATTEMPT_ALL
3074 WHERE person_id = x_person_id
3075 AND course_cd = x_course_cd
3076 AND unit_attempt_status = 'DROPPED'
3077 AND ( uoo_id = x_uoo_id
3078 OR ( unit_cd = x_unit_cd AND
3079 version_number = x_version_number AND
3080 cal_type = x_cal_type AND
3081 ci_sequence_number = x_ci_sequence_number AND
3082 location_cd = x_location_cd AND
3083 unit_class = x_unit_class
3084 ));
3085
3086 X_LAST_UPDATE_DATE DATE;
3087 X_LAST_UPDATED_BY NUMBER;
3088 X_LAST_UPDATE_LOGIN NUMBER;
3089 X_REQUEST_ID NUMBER;
3090 X_PROGRAM_ID NUMBER;
3091 X_PROGRAM_APPLICATION_ID NUMBER;
3092 X_PROGRAM_UPDATE_DATE DATE;
3093
3094 resource_busy EXCEPTION;
3095 PRAGMA EXCEPTION_INIT(resource_busy,-00054);
3096
3097 BEGIN
3098
3099 OPEN cur_rowid;
3100 FETCH cur_rowid INTO x_rowid;
3101 -- for Bug 1575677
3102 IF cur_rowid%FOUND THEN
3103 UPDATE_ROW (
3104 x_ROWID,
3105 X_PERSON_ID,
3106 X_COURSE_CD,
3107 X_UNIT_CD,
3108 X_CAL_TYPE,
3109 X_CI_SEQUENCE_NUMBER,
3110 X_VERSION_NUMBER,
3111 X_LOCATION_CD,
3112 X_UNIT_CLASS,
3113 X_CI_START_DT,
3114 X_CI_END_DT,
3115 X_UOO_ID,
3116 X_ENROLLED_DT,
3117 X_UNIT_ATTEMPT_STATUS,
3118 X_ADMINISTRATIVE_UNIT_STATUS,
3119 X_DISCONTINUED_DT,
3120 X_RULE_WAIVED_DT,
3121 X_RULE_WAIVED_PERSON_ID,
3122 X_NO_ASSESSMENT_IND,
3123 X_SUP_UNIT_CD,
3124 X_SUP_VERSION_NUMBER,
3125 X_EXAM_LOCATION_CD,
3126 X_ALTERNATIVE_TITLE,
3127 X_OVERRIDE_ENROLLED_CP,
3128 X_OVERRIDE_EFTSU,
3129 X_OVERRIDE_ACHIEVABLE_CP,
3130 X_OVERRIDE_OUTCOME_DUE_DT,
3131 X_OVERRIDE_CREDIT_REASON,
3132 X_ADMINISTRATIVE_PRIORITY,
3133 X_WAITLIST_DT,
3134 X_DCNT_REASON_CD,
3135 X_MODE,
3136 X_GS_VERSION_NUMBER ,
3137 X_ENR_METHOD_TYPE ,
3138 X_FAILED_UNIT_RULE ,
3139 X_CART ,
3140 X_RSV_SEAT_EXT_ID ,
3141 X_org_unit_cd ,
3142 X_GRADING_SCHEMA_CODE,
3143 x_subtitle,
3144 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3145 x_session_id,
3146 x_deg_aud_detail_id,
3147 x_student_career_transcript,
3148 x_student_career_statistics,
3149 x_waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3150 X_ATTRIBUTE_CATEGORY,
3151 X_ATTRIBUTE1,
3152 X_ATTRIBUTE2,
3153 X_ATTRIBUTE3,
3154 X_ATTRIBUTE4,
3155 X_ATTRIBUTE5,
3156 X_ATTRIBUTE6,
3157 X_ATTRIBUTE7,
3158 X_ATTRIBUTE8,
3159 X_ATTRIBUTE9,
3160 X_ATTRIBUTE10,
3161 X_ATTRIBUTE11,
3162 X_ATTRIBUTE12,
3163 X_ATTRIBUTE13,
3164 X_ATTRIBUTE14,
3165 X_ATTRIBUTE15,
3166 X_ATTRIBUTE16,
3167 X_ATTRIBUTE17,
3168 X_ATTRIBUTE18,
3169 X_ATTRIBUTE19,
3170 X_ATTRIBUTE20,
3171 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3172 X_WLST_PRIORITY_WEIGHT_NUM,
3173 X_WLST_PREFERENCE_WEIGHT_NUM,
3174 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3175 X_CORE_INDICATOR_CODE,
3176 X_UPD_AUDIT_FLAG,
3177 X_SS_SOURCE_IND
3178 );
3179 ELSE
3180 X_LAST_UPDATE_DATE := SYSDATE;
3181 IF(X_MODE = 'I') THEN
3182 X_LAST_UPDATED_BY := 1;
3183 X_LAST_UPDATE_LOGIN := 0;
3184 ELSIF (X_MODE IN ('R', 'S')) THEN
3185 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3186 IF X_LAST_UPDATED_BY IS NULL THEN
3187 X_LAST_UPDATED_BY := -1;
3188 END IF;
3189 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
3190 IF X_LAST_UPDATE_LOGIN IS NULL THEN
3191 X_LAST_UPDATE_LOGIN := -1;
3192 END IF;
3193 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
3194 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
3195 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
3196 IF (X_REQUEST_ID = -1) THEN
3197 X_REQUEST_ID := NULL;
3198 X_PROGRAM_ID := NULL;
3199 X_PROGRAM_APPLICATION_ID := NULL;
3200 X_PROGRAM_UPDATE_DATE := NULL;
3201 ELSE
3202 X_PROGRAM_UPDATE_DATE := SYSDATE;
3203 END IF;
3204 ELSE
3205 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
3206 IGS_GE_MSG_STACK.ADD;
3207 APP_EXCEPTION.RAISE_EXCEPTION;
3208 END IF;
3209 --
3210 Before_DML(
3211 p_action=>'INSERT',
3212 x_rowid=>NULL,
3213 x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
3214 x_alternative_title=>X_ALTERNATIVE_TITLE,
3215 x_cal_type=>X_CAL_TYPE,
3216 x_ci_end_dt=>X_CI_END_DT,
3217 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
3218 x_ci_start_dt=>X_CI_START_DT,
3219 x_course_cd=>X_COURSE_CD,
3220 x_discontinued_dt=>X_DISCONTINUED_DT,
3221 x_enrolled_dt=>X_ENROLLED_DT,
3222 x_exam_location_cd=>X_EXAM_LOCATION_CD,
3223 x_location_cd=>X_LOCATION_CD,
3224 x_no_assessment_ind=> NVL(X_NO_ASSESSMENT_IND,'N'),
3225 x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
3226 x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
3227 x_override_eftsu=>X_OVERRIDE_EFTSU,
3228 x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
3229 x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
3230 x_person_id=>X_PERSON_ID,
3231 x_rule_waived_dt=>X_RULE_WAIVED_DT,
3232 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
3233 x_sup_unit_cd=>X_SUP_UNIT_CD,
3234 x_sup_version_number=>X_SUP_VERSION_NUMBER,
3235 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
3236 x_unit_cd=>X_UNIT_CD,
3237 x_unit_class=>X_UNIT_CLASS,
3238 x_uoo_id=>X_UOO_ID,
3239 x_version_number=>X_VERSION_NUMBER,
3240 x_administrative_priority=>X_ADMINISTRATIVE_PRIORITY,
3241 x_waitlist_dt=>X_WAITLIST_DT,
3242 x_dcnt_reason_cd => X_DCNT_REASON_CD,
3243 x_creation_date=>X_LAST_UPDATE_DATE,
3244 x_created_by=>X_LAST_UPDATED_BY,
3245 x_last_update_date=>X_LAST_UPDATE_DATE,
3246 x_last_updated_by=>X_LAST_UPDATED_BY,
3247 x_last_update_login=>X_LAST_UPDATE_LOGIN,
3248 x_org_id => igs_ge_gen_003.get_org_id,
3249 x_gs_version_number => X_GS_VERSION_NUMBER,
3250 x_enr_method_type => X_ENR_METHOD_TYPE ,
3251 x_failed_unit_rule => X_FAILED_UNIT_RULE ,
3252 x_cart => X_CART ,
3253 x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
3254 x_org_unit_cd=>X_ORG_UNIT_CD ,
3255 x_grading_schema_code => X_GRADING_SCHEMA_CODE,
3256 x_subtitle => x_subtitle,
3257 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3258 x_session_id => x_session_id,
3259 x_deg_aud_detail_id => x_deg_aud_detail_id,
3260 x_student_career_transcript => x_student_career_transcript,
3261 x_student_career_statistics => x_student_career_statistics,
3262 x_waitlist_manual_ind => x_waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3263 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
3264 x_attribute1=>X_ATTRIBUTE1,
3265 x_attribute2=>X_ATTRIBUTE2,
3266 x_attribute3=>X_ATTRIBUTE3,
3267 x_attribute4=>X_ATTRIBUTE4,
3268 x_attribute5=>X_ATTRIBUTE5,
3269 x_attribute6=>X_ATTRIBUTE6,
3270 x_attribute7=>X_ATTRIBUTE7,
3271 x_attribute8=>X_ATTRIBUTE8,
3272 x_attribute9=>X_ATTRIBUTE9,
3273 x_attribute10=>X_ATTRIBUTE10,
3274 x_attribute11=>X_ATTRIBUTE11,
3275 x_attribute12=>X_ATTRIBUTE12,
3276 x_attribute13=>X_ATTRIBUTE13,
3277 x_attribute14=>X_ATTRIBUTE14,
3278 x_attribute15=>X_ATTRIBUTE15,
3279 x_attribute16=>X_ATTRIBUTE16,
3280 x_attribute17=>X_ATTRIBUTE17,
3281 x_attribute18=>X_ATTRIBUTE18,
3282 x_attribute19=>X_ATTRIBUTE19,
3283 x_attribute20=>X_ATTRIBUTE20,
3284 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3285 x_wlst_priority_weight_num=>X_WLST_PRIORITY_WEIGHT_NUM,
3286 x_wlst_preference_weight_num=>X_WLST_PREFERENCE_WEIGHT_NUM,
3287 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3288 x_core_indicator_code=>X_CORE_INDICATOR_CODE,
3289 X_UPD_AUDIT_FLAG =>X_UPD_AUDIT_FLAG,
3290 X_SS_SOURCE_IND => X_SS_SOURCE_IND
3291 );
3292 --
3293 IF (x_mode = 'S') THEN
3294 igs_sc_gen_001.set_ctx('R');
3295 END IF;
3296 INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
3297 PERSON_ID,
3298 COURSE_CD,
3299 UNIT_CD,
3300 VERSION_NUMBER,
3301 CAL_TYPE,
3302 CI_SEQUENCE_NUMBER,
3303 LOCATION_CD,
3304 UNIT_CLASS,
3305 CI_START_DT,
3306 CI_END_DT,
3307 UOO_ID,
3308 ENROLLED_DT,
3309 UNIT_ATTEMPT_STATUS,
3310 ADMINISTRATIVE_UNIT_STATUS,
3311 DISCONTINUED_DT,
3312 RULE_WAIVED_DT,
3313 RULE_WAIVED_PERSON_ID,
3314 NO_ASSESSMENT_IND,
3315 SUP_UNIT_CD,
3316 SUP_VERSION_NUMBER,
3317 EXAM_LOCATION_CD,
3318 ALTERNATIVE_TITLE,
3319 OVERRIDE_ENROLLED_CP,
3320 OVERRIDE_EFTSU,
3321 OVERRIDE_ACHIEVABLE_CP,
3322 OVERRIDE_OUTCOME_DUE_DT,
3323 OVERRIDE_CREDIT_REASON,
3324 ADMINISTRATIVE_PRIORITY,
3325 WAITLIST_DT,
3326 DCNT_REASON_CD,
3327 CREATION_DATE,
3328 CREATED_BY,
3329 LAST_UPDATE_DATE,
3330 LAST_UPDATED_BY,
3331 LAST_UPDATE_LOGIN,
3332 REQUEST_ID,
3333 PROGRAM_ID,
3334 PROGRAM_APPLICATION_ID,
3335 PROGRAM_UPDATE_DATE,
3336 org_id,
3337 GS_VERSION_NUMBER,
3338 ENR_METHOD_TYPE ,
3339 FAILED_UNIT_RULE ,
3340 CART ,
3341 RSV_SEAT_EXT_ID ,
3342 ORG_UNIT_CD ,
3343 GRADING_SCHEMA_CODE ,
3344 subtitle,
3345 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3346 session_id,
3347 deg_aud_detail_id,
3348 student_career_transcript,
3349 student_career_statistics,
3350 waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3351 ATTRIBUTE_CATEGORY,
3352 ATTRIBUTE1,
3353 ATTRIBUTE2,
3354 ATTRIBUTE3,
3355 ATTRIBUTE4,
3356 ATTRIBUTE5,
3357 ATTRIBUTE6,
3358 ATTRIBUTE7,
3359 ATTRIBUTE8,
3360 ATTRIBUTE9,
3361 ATTRIBUTE10,
3362 ATTRIBUTE11,
3363 ATTRIBUTE12,
3364 ATTRIBUTE13,
3365 ATTRIBUTE14,
3366 ATTRIBUTE15,
3367 ATTRIBUTE16,
3368 ATTRIBUTE17,
3369 ATTRIBUTE18,
3370 ATTRIBUTE19,
3371 ATTRIBUTE20,
3372 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3373 WLST_PRIORITY_WEIGHT_NUM,
3374 WLST_PREFERENCE_WEIGHT_NUM,
3375 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3376 CORE_INDICATOR_CODE,
3377 UPD_AUDIT_FLAG,
3378 SS_SOURCE_IND ) VALUES (
3379 NEW_REFERENCES.PERSON_ID,
3380 NEW_REFERENCES.COURSE_CD,
3381 NEW_REFERENCES.UNIT_CD,
3382 NEW_REFERENCES.VERSION_NUMBER,
3383 NEW_REFERENCES.CAL_TYPE,
3384 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
3385 NEW_REFERENCES.LOCATION_CD,
3386 NEW_REFERENCES.UNIT_CLASS,
3387 NEW_REFERENCES.CI_START_DT,
3388 NEW_REFERENCES.CI_END_DT,
3389 NEW_REFERENCES.UOO_ID,
3390 NEW_REFERENCES.ENROLLED_DT,
3391 NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
3392 NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
3393 NEW_REFERENCES.DISCONTINUED_DT,
3394 TRUNC(NEW_REFERENCES.RULE_WAIVED_DT),
3395 NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
3396 NEW_REFERENCES.NO_ASSESSMENT_IND,
3397 NEW_REFERENCES.SUP_UNIT_CD,
3398 NEW_REFERENCES.SUP_VERSION_NUMBER,
3399 NEW_REFERENCES.EXAM_LOCATION_CD,
3400 NEW_REFERENCES.ALTERNATIVE_TITLE,
3401 NEW_REFERENCES.OVERRIDE_ENROLLED_CP,
3402 NEW_REFERENCES.OVERRIDE_EFTSU,
3403 NEW_REFERENCES.OVERRIDE_ACHIEVABLE_CP,
3404 NEW_REFERENCES.OVERRIDE_OUTCOME_DUE_DT,
3405 NEW_REFERENCES.OVERRIDE_CREDIT_REASON,
3406 NEW_REFERENCES.ADMINISTRATIVE_PRIORITY,
3407 NEW_REFERENCES.WAITLIST_DT,
3408 NEW_REFERENCES.DCNT_REASON_CD,
3409 X_LAST_UPDATE_DATE,
3410 X_LAST_UPDATED_BY,
3411 X_LAST_UPDATE_DATE,
3412 X_LAST_UPDATED_BY,
3413 X_LAST_UPDATE_LOGIN,
3414 X_REQUEST_ID,
3415 X_PROGRAM_ID,
3416 X_PROGRAM_APPLICATION_ID,
3417 X_PROGRAM_UPDATE_DATE,
3418 NEW_REFERENCES.org_id,
3419 NEW_REFERENCES.GS_VERSION_NUMBER,
3420 NEW_REFERENCES.ENR_METHOD_TYPE,
3421 NEW_REFERENCES.FAILED_UNIT_RULE,
3422 NEW_REFERENCES.CART ,
3423 NEW_REFERENCES.RSV_SEAT_EXT_ID ,
3424 NEW_REFERENCES.ORG_UNIT_CD ,
3425 NEW_REFERENCES.GRADING_SCHEMA_CODE ,
3426 new_references.subtitle,
3427 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3428 new_references.session_id,
3429 new_references.deg_aud_detail_id ,
3430 new_references.student_career_transcript,
3431 new_references.student_career_statistics,
3432 new_references.waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3433 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
3434 NEW_REFERENCES.ATTRIBUTE1,
3435 NEW_REFERENCES.ATTRIBUTE2,
3436 NEW_REFERENCES.ATTRIBUTE3,
3437 NEW_REFERENCES.ATTRIBUTE4,
3438 NEW_REFERENCES.ATTRIBUTE5,
3439 NEW_REFERENCES.ATTRIBUTE6,
3440 NEW_REFERENCES.ATTRIBUTE7,
3441 NEW_REFERENCES.ATTRIBUTE8,
3442 NEW_REFERENCES.ATTRIBUTE9,
3443 NEW_REFERENCES.ATTRIBUTE10,
3444 NEW_REFERENCES.ATTRIBUTE11,
3445 NEW_REFERENCES.ATTRIBUTE12,
3446 NEW_REFERENCES.ATTRIBUTE13,
3447 NEW_REFERENCES.ATTRIBUTE14,
3448 NEW_REFERENCES.ATTRIBUTE15,
3449 NEW_REFERENCES.ATTRIBUTE16,
3450 NEW_REFERENCES.ATTRIBUTE17,
3451 NEW_REFERENCES.ATTRIBUTE18,
3452 NEW_REFERENCES.ATTRIBUTE19,
3453 NEW_REFERENCES.ATTRIBUTE20,
3454 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3455 NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM,
3456 NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM,
3457 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3458 NEW_REFERENCES.CORE_INDICATOR_CODE,
3459 NEW_REFERENCES.UPD_AUDIT_FLAG,
3460 NEW_REFERENCES.SS_SOURCE_IND );
3461 IF (x_mode = 'S') THEN
3462 igs_sc_gen_001.unset_ctx('R');
3463 END IF;
3464
3465 OPEN c;
3466 FETCH c INTO X_ROWID;
3467 IF (c%NOTFOUND) THEN
3468 CLOSE c;
3469 RAISE NO_DATA_FOUND;
3470 END IF;
3471 CLOSE c;
3472 --
3473 After_DML(
3474 p_action => 'INSERT',
3475 x_rowid => X_ROWID
3476 );
3477 --
3478 END IF;
3479 CLOSE cur_rowid;
3480
3481 EXCEPTION
3482 WHEN resource_busy THEN
3483 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
3484 IGS_GE_MSG_STACK.ADD;
3485 APP_EXCEPTION.RAISE_EXCEPTION;
3486
3487 WHEN OTHERS THEN
3488 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
3489 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
3490 fnd_message.set_token ('ERR_CD', SQLCODE);
3491 igs_ge_msg_stack.add;
3492 igs_sc_gen_001.unset_ctx('R');
3493 app_exception.raise_exception;
3494 ELSE
3495 igs_sc_gen_001.unset_ctx('R');
3496 RAISE;
3497 END IF;
3498
3499 END INSERT_ROW;
3500
3501 PROCEDURE LOCK_ROW (
3502 X_ROWID IN VARCHAR2,
3503 X_PERSON_ID IN NUMBER,
3504 X_COURSE_CD IN VARCHAR2,
3505 X_UNIT_CD IN VARCHAR2,
3506 X_CAL_TYPE IN VARCHAR2,
3507 X_CI_SEQUENCE_NUMBER IN NUMBER,
3508 X_VERSION_NUMBER IN NUMBER,
3509 X_LOCATION_CD IN VARCHAR2,
3510 X_UNIT_CLASS IN VARCHAR2,
3511 X_CI_START_DT IN DATE,
3512 X_CI_END_DT IN DATE,
3513 X_UOO_ID IN NUMBER,
3514 X_ENROLLED_DT IN DATE,
3515 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
3516 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
3517 X_DISCONTINUED_DT IN DATE,
3518 X_RULE_WAIVED_DT IN DATE,
3519 X_RULE_WAIVED_PERSON_ID IN NUMBER,
3520 X_NO_ASSESSMENT_IND IN VARCHAR2,
3521 X_SUP_UNIT_CD IN VARCHAR2,
3522 X_SUP_VERSION_NUMBER IN NUMBER,
3523 X_EXAM_LOCATION_CD IN VARCHAR2,
3524 X_ALTERNATIVE_TITLE IN VARCHAR2,
3525 X_OVERRIDE_ENROLLED_CP IN NUMBER,
3526 X_OVERRIDE_EFTSU IN NUMBER,
3527 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
3528 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
3529 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
3530 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
3531 X_WAITLIST_DT IN DATE,
3532 X_DCNT_REASON_CD IN VARCHAR2,
3533 X_GS_VERSION_NUMBER IN NUMBER ,
3534 X_ENR_METHOD_TYPE IN VARCHAR2,
3535 X_FAILED_UNIT_RULE IN VARCHAR2,
3536 X_CART IN VARCHAR2,
3537 X_RSV_SEAT_EXT_ID IN NUMBER ,
3538 X_ORG_UNIT_CD IN VARCHAR2 ,
3539 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
3540 x_subtitle IN VARCHAR2 ,
3541 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3542 x_session_id IN NUMBER ,
3543 X_deg_aud_detail_id IN NUMBER ,
3544 x_student_career_transcript IN VARCHAR2 ,
3545 x_student_career_statistics IN VARCHAR2 ,
3546 x_waitlist_manual_ind IN VARCHAR2,--Bug ID: 2554109 added by adhawan
3547 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
3548 X_ATTRIBUTE1 IN VARCHAR2 ,
3549 X_ATTRIBUTE2 IN VARCHAR2 ,
3550 X_ATTRIBUTE3 IN VARCHAR2 ,
3551 X_ATTRIBUTE4 IN VARCHAR2 ,
3552 X_ATTRIBUTE5 IN VARCHAR2 ,
3553 X_ATTRIBUTE6 IN VARCHAR2 ,
3554 X_ATTRIBUTE7 IN VARCHAR2 ,
3555 X_ATTRIBUTE8 IN VARCHAR2 ,
3556 X_ATTRIBUTE9 IN VARCHAR2 ,
3557 X_ATTRIBUTE10 IN VARCHAR2,
3558 X_ATTRIBUTE11 IN VARCHAR2,
3559 X_ATTRIBUTE12 IN VARCHAR2,
3560 X_ATTRIBUTE13 IN VARCHAR2,
3561 X_ATTRIBUTE14 IN VARCHAR2,
3562 X_ATTRIBUTE15 IN VARCHAR2,
3563 X_ATTRIBUTE16 IN VARCHAR2,
3564 X_ATTRIBUTE17 IN VARCHAR2,
3565 X_ATTRIBUTE18 IN VARCHAR2,
3566 X_ATTRIBUTE19 IN VARCHAR2,
3567 x_ATTRIBUTE20 IN VARCHAR2,
3568 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3569 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER,
3570 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER,
3571 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3572 X_CORE_INDICATOR_CODE IN VARCHAR2,
3573 X_UPD_AUDIT_FLAG IN VARCHAR2,
3574 X_SS_SOURCE_IND IN VARCHAR2
3575 ) AS
3576 CURSOR c1 IS SELECT
3577 VERSION_NUMBER,
3578 LOCATION_CD,
3579 UNIT_CLASS,
3580 CI_START_DT,
3581 CI_END_DT,
3582 UOO_ID,
3583 ENROLLED_DT,
3584 UNIT_ATTEMPT_STATUS,
3585 ADMINISTRATIVE_UNIT_STATUS,
3586 DISCONTINUED_DT,
3587 RULE_WAIVED_DT,
3588 RULE_WAIVED_PERSON_ID,
3589 NO_ASSESSMENT_IND,
3590 SUP_UNIT_CD,
3591 SUP_VERSION_NUMBER,
3592 EXAM_LOCATION_CD,
3593 ALTERNATIVE_TITLE,
3594 OVERRIDE_ENROLLED_CP,
3595 OVERRIDE_EFTSU,
3596 OVERRIDE_ACHIEVABLE_CP,
3597 OVERRIDE_OUTCOME_DUE_DT,
3598 OVERRIDE_CREDIT_REASON,
3599 ADMINISTRATIVE_PRIORITY,
3600 WAITLIST_DT,
3601 dcnt_reason_cd,
3602 GS_VERSION_NUMBER,
3603 ENR_METHOD_TYPE ,
3604 FAILED_UNIT_RULE ,
3605 CART ,
3606 RSV_SEAT_EXT_ID ,
3607 ORG_UNIT_CD ,
3608 GRADING_SCHEMA_CODE,
3609 subtitle,
3610 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3611 session_id,
3612 deg_aud_detail_id,
3613 student_career_transcript,
3614 student_career_statistics,
3615 waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
3616 ATTRIBUTE_CATEGORY,
3617 ATTRIBUTE1,
3618 ATTRIBUTE2,
3619 ATTRIBUTE3,
3620 ATTRIBUTE4,
3621 ATTRIBUTE5,
3622 ATTRIBUTE6,
3623 ATTRIBUTE7,
3624 ATTRIBUTE8,
3625 ATTRIBUTE9,
3626 ATTRIBUTE10,
3627 ATTRIBUTE11,
3628 ATTRIBUTE12,
3629 ATTRIBUTE13,
3630 ATTRIBUTE14,
3631 ATTRIBUTE15,
3632 ATTRIBUTE16,
3633 ATTRIBUTE17,
3634 ATTRIBUTE18,
3635 ATTRIBUTE19,
3636 ATTRIBUTE20,
3637 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3638 WLST_PRIORITY_WEIGHT_NUM,
3639 WLST_PREFERENCE_WEIGHT_NUM,
3640 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3641 CORE_INDICATOR_CODE,
3642 UPD_AUDIT_FLAG,
3643 SS_SOURCE_IND
3644 FROM IGS_EN_SU_ATTEMPT_ALL
3645 WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
3646 tlinfo c1%ROWTYPE;
3647 BEGIN
3648 OPEN c1;
3649 FETCH c1 INTO tlinfo;
3650 IF (c1%NOTFOUND) THEN
3651 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
3652 IGS_GE_MSG_STACK.ADD;
3653 APP_EXCEPTION.RAISE_EXCEPTION;
3654 CLOSE c1;
3655 RETURN;
3656 END IF;
3657 CLOSE c1;
3658 IF ( (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
3659 AND (tlinfo.LOCATION_CD = X_LOCATION_CD)
3660 AND (tlinfo.UNIT_CLASS = X_UNIT_CLASS)
3661 AND (tlinfo.CI_START_DT = X_CI_START_DT)
3662 AND (tlinfo.CI_END_DT = X_CI_END_DT)
3663 AND (tlinfo.UOO_ID = X_UOO_ID)
3664 AND (( TRUNC(tlinfo.ENROLLED_DT) = TRUNC(X_ENROLLED_DT) )
3665 OR ((tlinfo.ENROLLED_DT IS NULL)
3666 AND (X_ENROLLED_DT IS NULL)))
3667 AND (tlinfo.UNIT_ATTEMPT_STATUS = X_UNIT_ATTEMPT_STATUS)
3668 AND ((tlinfo.ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS)
3669 OR ((tlinfo.ADMINISTRATIVE_UNIT_STATUS IS NULL)
3670 AND (X_ADMINISTRATIVE_UNIT_STATUS IS NULL)))
3671 AND (( TRUNC(tlinfo.DISCONTINUED_DT) = TRUNC(X_DISCONTINUED_DT) )
3672 OR ((tlinfo.DISCONTINUED_DT IS NULL)
3673 AND (X_DISCONTINUED_DT IS NULL)))
3674 AND (( TRUNC(tlinfo.RULE_WAIVED_DT) = TRUNC(X_RULE_WAIVED_DT))
3675 OR ((tlinfo.RULE_WAIVED_DT IS NULL)
3676 AND (X_RULE_WAIVED_DT IS NULL)))
3677 AND ((tlinfo.RULE_WAIVED_PERSON_ID = X_RULE_WAIVED_PERSON_ID)
3678 OR ((tlinfo.RULE_WAIVED_PERSON_ID IS NULL)
3679 AND (X_RULE_WAIVED_PERSON_ID IS NULL)))
3680 AND (tlinfo.NO_ASSESSMENT_IND = X_NO_ASSESSMENT_IND)
3681 AND ((tlinfo.SUP_UNIT_CD = X_SUP_UNIT_CD)
3682 OR ((tlinfo.SUP_UNIT_CD IS NULL)
3683 AND (X_SUP_UNIT_CD IS NULL)))
3684 AND ((tlinfo.SUP_VERSION_NUMBER = X_SUP_VERSION_NUMBER)
3685 OR ((tlinfo.SUP_VERSION_NUMBER IS NULL)
3686 AND (X_SUP_VERSION_NUMBER IS NULL)))
3687 AND ((tlinfo.EXAM_LOCATION_CD = X_EXAM_LOCATION_CD)
3688 OR ((tlinfo.EXAM_LOCATION_CD IS NULL)
3689 AND (X_EXAM_LOCATION_CD IS NULL)))
3690 AND ((tlinfo.ALTERNATIVE_TITLE = X_ALTERNATIVE_TITLE)
3691 OR ((tlinfo.ALTERNATIVE_TITLE IS NULL)
3692 AND (X_ALTERNATIVE_TITLE IS NULL)))
3693 AND ((tlinfo.OVERRIDE_ENROLLED_CP = X_OVERRIDE_ENROLLED_CP)
3694 OR ((tlinfo.OVERRIDE_ENROLLED_CP IS NULL)
3695 AND (X_OVERRIDE_ENROLLED_CP IS NULL)))
3696 AND ((tlinfo.OVERRIDE_EFTSU = X_OVERRIDE_EFTSU)
3697 OR ((tlinfo.OVERRIDE_EFTSU IS NULL)
3698 AND (X_OVERRIDE_EFTSU IS NULL)))
3699 AND ((tlinfo.OVERRIDE_ACHIEVABLE_CP = X_OVERRIDE_ACHIEVABLE_CP)
3700 OR ((tlinfo.OVERRIDE_ACHIEVABLE_CP IS NULL)
3701 AND (X_OVERRIDE_ACHIEVABLE_CP IS NULL)))
3702 AND (( TRUNC(tlinfo.OVERRIDE_OUTCOME_DUE_DT) = TRUNC(X_OVERRIDE_OUTCOME_DUE_DT))
3703 OR ((tlinfo.OVERRIDE_OUTCOME_DUE_DT IS NULL)
3704 AND (X_OVERRIDE_OUTCOME_DUE_DT IS NULL)))
3705 AND ((tlinfo.OVERRIDE_CREDIT_REASON = X_OVERRIDE_CREDIT_REASON)
3706 OR ((tlinfo.OVERRIDE_CREDIT_REASON IS NULL)
3707 AND (X_OVERRIDE_CREDIT_REASON IS NULL)))
3708 AND ((tlinfo.dcnt_reason_cd = X_dcnt_reason_Cd)
3709 OR ((tlinfo.dcnt_reason_cd IS NULL)
3710 AND (X_dcnt_reason_cd IS NULL)))
3711 AND ((tlinfo.GS_VERSION_NUMBER = X_GS_VERSION_NUMBER)
3712 OR ((tlinfo.GS_VERSION_NUMBER IS NULL)
3713 AND (X_GS_VERSION_NUMBER IS NULL)))
3714 AND ((tlinfo.ENR_METHOD_TYPE = X_ENR_METHOD_TYPE)
3715 OR ((tlinfo.ENR_METHOD_TYPE IS NULL)
3716 AND (X_ENR_METHOD_TYPE IS NULL)))
3717 AND ((tlinfo.FAILED_UNIT_RULE = X_FAILED_UNIT_RULE)
3718 OR ((tlinfo.FAILED_UNIT_RULE IS NULL)
3719 AND (X_FAILED_UNIT_RULE IS NULL)))
3720 AND ((tlinfo.CART = X_CART)
3721 OR ((tlinfo.CART IS NULL)
3722 AND (X_CART IS NULL)))
3723 AND ((tlinfo.RSV_SEAT_EXT_ID = X_RSV_SEAT_EXT_ID)
3724 OR ((tlinfo.RSV_SEAT_EXT_ID IS NULL)
3725 AND (X_RSV_SEAT_EXT_ID IS NULL)))
3726 AND ((tlinfo.ORG_UNIT_CD = X_ORG_UNIT_CD)
3727 OR ((tlinfo.ORG_UNIT_CD IS NULL)
3728 AND (X_ORG_UNIT_CD IS NULL)))
3729 AND ((tlinfo.GRADING_SCHEMA_CODE = X_GRADING_SCHEMA_CODE)
3730 OR ((tlinfo.GRADING_SCHEMA_CODE IS NULL)
3731 AND (X_GRADING_SCHEMA_CODE IS NULL)))
3732 AND ((tlinfo.subtitle= X_subtitle)
3733 OR ((tlinfo.subtitle IS NULL)
3734 AND (X_subtitle IS NULL)))
3735 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3736 AND ((tlinfo.session_id = x_session_id)
3737 OR ((tlinfo.session_id IS NULL)
3738 AND (x_session_id IS NULL)))
3739 AND ((tlinfo.deg_aud_detail_id = X_deg_aud_detail_id)
3740 OR ((tlinfo.deg_aud_detail_id IS NULL)
3741 AND (X_deg_aud_detail_id IS NULL)))
3742 AND ((tlinfo.student_career_transcript = X_student_career_transcript)
3743 OR ((tlinfo.student_career_transcript IS NULL)
3744 AND (X_student_career_transcript IS NULL)))
3745
3746 AND ((tlinfo.student_career_statistics = X_student_career_statistics)
3747 OR ((tlinfo.student_career_statistics IS NULL)
3748 AND (X_student_career_statistics IS NULL)))
3749
3750 --Bug ID: 2554109 added by adhawan
3751 AND ((tlinfo.waitlist_manual_ind = X_waitlist_manual_ind)
3752 OR (( tlinfo.waitlist_manual_ind IS NULL )
3753 AND (X_WAITLIST_MANUAL_IND IS NULL)))
3754
3755 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
3756 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
3757 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
3758 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
3759 OR ((tlinfo.ATTRIBUTE1 IS NULL)
3760 AND (X_ATTRIBUTE1 IS NULL)))
3761 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
3762 OR ((tlinfo.ATTRIBUTE2 IS NULL)
3763 AND (X_ATTRIBUTE2 IS NULL)))
3764 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
3765 OR ((tlinfo.ATTRIBUTE3 IS NULL)
3766 AND (X_ATTRIBUTE3 IS NULL)))
3767 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
3768 OR ((tlinfo.ATTRIBUTE4 IS NULL)
3769 AND (X_ATTRIBUTE4 IS NULL)))
3770 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
3771 OR ((tlinfo.ATTRIBUTE5 IS NULL)
3772 AND (X_ATTRIBUTE5 IS NULL)))
3773 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
3774 OR ((tlinfo.ATTRIBUTE6 IS NULL)
3775 AND (X_ATTRIBUTE6 IS NULL)))
3776 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
3777 OR ((tlinfo.ATTRIBUTE7 IS NULL)
3778 AND (X_ATTRIBUTE7 IS NULL)))
3779 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
3780 OR ((tlinfo.ATTRIBUTE8 IS NULL)
3781 AND (X_ATTRIBUTE8 IS NULL)))
3782 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
3783 OR ((tlinfo.ATTRIBUTE9 IS NULL)
3784 AND (X_ATTRIBUTE9 IS NULL)))
3785 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
3786 OR ((tlinfo.ATTRIBUTE10 IS NULL)
3787 AND (X_ATTRIBUTE10 IS NULL)))
3788 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
3789 OR ((tlinfo.ATTRIBUTE11 IS NULL)
3790 AND (X_ATTRIBUTE11 IS NULL)))
3791 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
3792 OR ((tlinfo.ATTRIBUTE12 IS NULL)
3793 AND (X_ATTRIBUTE12 IS NULL)))
3794 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
3795 OR ((tlinfo.ATTRIBUTE13 IS NULL)
3796 AND (X_ATTRIBUTE13 IS NULL)))
3797 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
3798 OR ((tlinfo.ATTRIBUTE14 IS NULL)
3799 AND (X_ATTRIBUTE14 IS NULL)))
3800 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
3801 OR ((tlinfo.ATTRIBUTE15 IS NULL)
3802 AND (X_ATTRIBUTE15 IS NULL)))
3803 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
3804 OR ((tlinfo.ATTRIBUTE16 IS NULL)
3805 AND (X_ATTRIBUTE16 IS NULL)))
3806 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
3807 OR ((tlinfo.ATTRIBUTE17 IS NULL)
3808 AND (X_ATTRIBUTE17 IS NULL)))
3809 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
3810 OR ((tlinfo.ATTRIBUTE18 IS NULL)
3811 AND (X_ATTRIBUTE18 IS NULL)))
3812 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
3813 OR ((tlinfo.ATTRIBUTE19 IS NULL)
3814 AND (X_ATTRIBUTE19 IS NULL)))
3815 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
3816 OR ((tlinfo.ATTRIBUTE20 IS NULL)
3817 AND (X_ATTRIBUTE20 IS NULL)))
3818 AND ((tlinfo.ADMINISTRATIVE_PRIORITY = X_ADMINISTRATIVE_PRIORITY)
3819 OR ((tlinfo.ADMINISTRATIVE_PRIORITY IS NULL)
3820 AND (X_ADMINISTRATIVE_PRIORITY IS NULL)))
3821 AND (( TRUNC(tlinfo.WAITLIST_DT) = TRUNC(X_WAITLIST_DT) )
3822 OR ((tlinfo.WAITLIST_DT IS NULL)
3823 AND (X_WAITLIST_DT IS NULL)))
3824 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3825 AND ((tlinfo.wlst_priority_weight_num = X_wlst_priority_weight_num)
3826 OR (( tlinfo.wlst_priority_weight_num IS NULL )
3827 AND (X_WLST_PRIORITY_WEIGHT_NUM IS NULL)))
3828 AND ((tlinfo.wlst_preference_weight_num = X_wlst_preference_weight_num)
3829 OR (( tlinfo.wlst_preference_weight_num IS NULL )
3830 AND (X_WLST_PREFERENCE_WEIGHT_NUM IS NULL)))
3831 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3832 AND ((tlinfo.core_indicator_code = X_core_indicator_code)
3833 OR (( tlinfo.core_indicator_code IS NULL )
3834 AND (X_CORE_INDICATOR_CODE IS NULL)))
3835 AND (tlinfo.UPD_AUDIT_FLAG = X_UPD_AUDIT_FLAG)
3836 AND (tlinfo.SS_SOURCE_IND = X_SS_SOURCE_IND)
3837 ) THEN
3838 NULL;
3839 ELSE
3840 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
3841 IGS_GE_MSG_STACK.ADD;
3842 APP_EXCEPTION.RAISE_EXCEPTION;
3843 END IF;
3844 RETURN;
3845 END LOCK_ROW;
3846
3847 /*------------------------------------------------------------------------------------------------------------------------------------------
3848 svanukur 28-APR-03 Not allowing the update of uoo_id as it is now part of Pk
3849 -- as part of MUS build, # 2829262
3850 ---------------------------------------------------------------------------------------*/
3851 PROCEDURE UPDATE_ROW (
3852 X_ROWID IN VARCHAR2,
3853 X_PERSON_ID IN NUMBER,
3854 X_COURSE_CD IN VARCHAR2,
3855 X_UNIT_CD IN VARCHAR2,
3856 X_CAL_TYPE IN VARCHAR2,
3857 X_CI_SEQUENCE_NUMBER IN NUMBER,
3858 X_VERSION_NUMBER IN NUMBER,
3859 X_LOCATION_CD IN VARCHAR2,
3860 X_UNIT_CLASS IN VARCHAR2,
3861 X_CI_START_DT IN DATE,
3862 X_CI_END_DT IN DATE,
3863 X_UOO_ID IN NUMBER,
3864 X_ENROLLED_DT IN DATE,
3865 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
3866 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
3867 X_DISCONTINUED_DT IN DATE,
3868 X_RULE_WAIVED_DT IN DATE,
3869 X_RULE_WAIVED_PERSON_ID IN NUMBER,
3870 X_NO_ASSESSMENT_IND IN VARCHAR2,
3871 X_SUP_UNIT_CD IN VARCHAR2,
3872 X_SUP_VERSION_NUMBER IN NUMBER,
3873 X_EXAM_LOCATION_CD IN VARCHAR2,
3874 X_ALTERNATIVE_TITLE IN VARCHAR2,
3875 X_OVERRIDE_ENROLLED_CP IN NUMBER,
3876 X_OVERRIDE_EFTSU IN NUMBER,
3877 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
3878 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
3879 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
3880 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
3881 X_WAITLIST_DT IN DATE,
3882 x_dcnt_reason_cd IN VARCHAR2,
3883 X_MODE IN VARCHAR2 ,
3884 X_GS_VERSION_NUMBER IN NUMBER ,
3885 X_ENR_METHOD_TYPE IN VARCHAR2,
3886 X_FAILED_UNIT_RULE IN VARCHAR2,
3887 X_CART IN VARCHAR2,
3888 X_RSV_SEAT_EXT_ID IN NUMBER ,
3889 X_ORG_UNIT_CD IN VARCHAR2 ,
3890 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
3891 x_subtitle IN VARCHAR2 ,
3892 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3893 x_session_id IN NUMBER ,
3894 x_deg_aud_detail_id IN NUMBER ,
3895 x_student_career_transcript IN VARCHAR2 ,
3896 x_student_career_statistics IN VARCHAR2 ,
3897 x_waitlist_manual_ind IN VARCHAR2,--Bug ID: 2554109 added by adhawan
3898 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
3899 X_ATTRIBUTE1 IN VARCHAR2 ,
3900 X_ATTRIBUTE2 IN VARCHAR2 ,
3901 X_ATTRIBUTE3 IN VARCHAR2 ,
3902 X_ATTRIBUTE4 IN VARCHAR2 ,
3903 X_ATTRIBUTE5 IN VARCHAR2 ,
3904 X_ATTRIBUTE6 IN VARCHAR2 ,
3905 X_ATTRIBUTE7 IN VARCHAR2 ,
3906 X_ATTRIBUTE8 IN VARCHAR2 ,
3907 X_ATTRIBUTE9 IN VARCHAR2 ,
3908 X_ATTRIBUTE10 IN VARCHAR2 ,
3909 X_ATTRIBUTE11 IN VARCHAR2 ,
3910 X_ATTRIBUTE12 IN VARCHAR2 ,
3911 X_ATTRIBUTE13 IN VARCHAR2 ,
3912 X_ATTRIBUTE14 IN VARCHAR2 ,
3913 X_ATTRIBUTE15 IN VARCHAR2 ,
3914 X_ATTRIBUTE16 IN VARCHAR2 ,
3915 X_ATTRIBUTE17 IN VARCHAR2 ,
3916 X_ATTRIBUTE18 IN VARCHAR2 ,
3917 X_ATTRIBUTE19 IN VARCHAR2 ,
3918 x_ATTRIBUTE20 IN VARCHAR2 ,
3919 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
3920 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER ,
3921 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER ,
3922 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3923 X_CORE_INDICATOR_CODE IN VARCHAR2,
3924 X_UPD_AUDIT_FLAG IN VARCHAR2 ,
3925 X_SS_SOURCE_IND IN VARCHAR2
3926 ) AS
3927 X_LAST_UPDATE_DATE DATE;
3928 X_LAST_UPDATED_BY NUMBER;
3929 X_LAST_UPDATE_LOGIN NUMBER;
3930 X_REQUEST_ID NUMBER;
3931 X_PROGRAM_ID NUMBER;
3932 X_PROGRAM_APPLICATION_ID NUMBER;
3933 X_PROGRAM_UPDATE_DATE DATE;
3934
3935 resource_busy EXCEPTION;
3936 PRAGMA EXCEPTION_INIT(resource_busy,-00054);
3937
3938 BEGIN
3939 X_LAST_UPDATE_DATE := SYSDATE;
3940 IF(X_MODE = 'I') THEN
3941 X_LAST_UPDATED_BY := 1;
3942 X_LAST_UPDATE_LOGIN := 0;
3943 ELSIF (X_MODE IN ('R', 'S')) THEN
3944 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3945 IF X_LAST_UPDATED_BY IS NULL THEN
3946 X_LAST_UPDATED_BY := -1;
3947 END IF;
3948 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
3949 IF X_LAST_UPDATE_LOGIN IS NULL THEN
3950 X_LAST_UPDATE_LOGIN := -1;
3951 END IF;
3952 ELSE
3953 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
3954 IGS_GE_MSG_STACK.ADD;
3955 APP_EXCEPTION.RAISE_EXCEPTION;
3956 END IF;
3957
3958 Before_DML(
3959 p_action=>'UPDATE',
3960 x_rowid=>X_ROWID,
3961 x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
3962 x_alternative_title=>X_ALTERNATIVE_TITLE,
3963 x_cal_type=>X_CAL_TYPE,
3964 x_ci_end_dt=>X_CI_END_DT,
3965 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
3966 x_ci_start_dt=>X_CI_START_DT,
3967 x_course_cd=>X_COURSE_CD,
3968 x_discontinued_dt=>X_DISCONTINUED_DT,
3969 x_enrolled_dt=>X_ENROLLED_DT,
3970 x_exam_location_cd=>X_EXAM_LOCATION_CD,
3971 x_location_cd=>X_LOCATION_CD,
3972 x_no_assessment_ind=>NVL(X_NO_ASSESSMENT_IND,'N'),
3973 x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
3974 x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
3975 x_override_eftsu=>X_OVERRIDE_EFTSU,
3976 x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
3977 x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
3978 x_person_id=>X_PERSON_ID,
3979 x_rule_waived_dt=>X_RULE_WAIVED_DT,
3980 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
3981 x_sup_unit_cd=>X_SUP_UNIT_CD,
3982 x_sup_version_number=>X_SUP_VERSION_NUMBER,
3983 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
3984 x_unit_cd=>X_UNIT_CD,
3985 x_unit_class=>X_UNIT_CLASS,
3986 x_uoo_id=>X_UOO_ID,
3987 x_version_number=>X_VERSION_NUMBER,
3988 x_administrative_priority=>X_ADMINISTRATIVE_PRIORITY,
3989 x_waitlist_dt=>X_WAITLIST_DT,
3990 x_dcnt_reason_cd => x_dcnt_reason_cd,
3991 x_creation_date=>X_LAST_UPDATE_DATE,
3992 x_created_by=>X_LAST_UPDATED_BY,
3993 x_last_update_date=>X_LAST_UPDATE_DATE,
3994 x_last_updated_by=>X_LAST_UPDATED_BY,
3995 x_last_update_login=>X_LAST_UPDATE_LOGIN,
3996 x_gs_version_number=>X_GS_VERSION_NUMBER,
3997 x_enr_method_type=>X_ENR_METHOD_TYPE,
3998 x_failed_unit_rule=>X_FAILED_UNIT_RULE,
3999 x_cart => X_CART,
4000 x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
4001 x_org_unit_cd => X_ORG_UNIT_CD ,
4002 x_grading_schema_code => X_GRADING_SCHEMA_CODE ,
4003 x_subtitle => x_subtitle,
4004 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
4005 x_session_id => x_session_id,
4006 x_deg_aud_detail_id => x_deg_aud_detail_id,
4007 x_student_career_transcript => x_student_career_transcript,
4008 x_student_career_statistics => x_student_career_statistics,
4009 x_waitlist_manual_ind => x_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
4010 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
4011 x_attribute1=>X_ATTRIBUTE1,
4012 x_attribute2=>X_ATTRIBUTE2,
4013 x_attribute3=>X_ATTRIBUTE3,
4014 x_attribute4=>X_ATTRIBUTE4,
4015 x_attribute5=>X_ATTRIBUTE5,
4016 x_attribute6=>X_ATTRIBUTE6,
4017 x_attribute7=>X_ATTRIBUTE7,
4018 x_attribute8=>X_ATTRIBUTE8,
4019 x_attribute9=>X_ATTRIBUTE9,
4020 x_attribute10=>X_ATTRIBUTE10,
4021 x_attribute11=>X_ATTRIBUTE11,
4022 x_attribute12=>X_ATTRIBUTE12,
4023 x_attribute13=>X_ATTRIBUTE13,
4024 x_attribute14=>X_ATTRIBUTE14,
4025 x_attribute15=>X_ATTRIBUTE15,
4026 x_attribute16=>X_ATTRIBUTE16,
4027 x_attribute17=>X_ATTRIBUTE17,
4028 x_attribute18=>X_ATTRIBUTE18,
4029 x_attribute19=>X_ATTRIBUTE19,
4030 x_attribute20=>X_ATTRIBUTE20,
4031 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4032 x_wlst_priority_weight_num=>X_WLST_PRIORITY_WEIGHT_NUM,
4033 x_wlst_preference_weight_num=>X_WLST_PREFERENCE_WEIGHT_NUM,
4034 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
4035 x_core_indicator_code=>X_CORE_INDICATOR_CODE,
4036 X_UPD_AUDIT_FLAG => X_UPD_AUDIT_FLAG,
4037 X_SS_SOURCE_IND => X_SS_SOURCE_IND
4038 );
4039 IF (X_MODE IN ('R', 'S')) THEN
4040 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
4041 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
4042 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
4043 IF (X_REQUEST_ID = -1) THEN
4044 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
4045 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
4046 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
4047 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
4048 ELSE
4049 X_PROGRAM_UPDATE_DATE := SYSDATE;
4050 END IF;
4051 --Before_DML(
4052 --
4053 END IF;
4054
4055 IF (x_mode = 'S') THEN
4056 igs_sc_gen_001.set_ctx('R');
4057 END IF;
4058 UPDATE IGS_EN_SU_ATTEMPT_ALL SET
4059 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
4060 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
4061 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
4062 CI_START_DT = NEW_REFERENCES.CI_START_DT,
4063 CI_END_DT = NEW_REFERENCES.CI_END_DT,
4064 ENROLLED_DT = NEW_REFERENCES.ENROLLED_DT,
4065 UNIT_ATTEMPT_STATUS = NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
4066 ADMINISTRATIVE_UNIT_STATUS = NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
4067 DISCONTINUED_DT = NEW_REFERENCES.DISCONTINUED_DT,
4068 RULE_WAIVED_DT = TRUNC(NEW_REFERENCES.RULE_WAIVED_DT),
4069 RULE_WAIVED_PERSON_ID = NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
4070 NO_ASSESSMENT_IND = NEW_REFERENCES.NO_ASSESSMENT_IND,
4071 SUP_UNIT_CD = NEW_REFERENCES.SUP_UNIT_CD,
4072 SUP_VERSION_NUMBER = NEW_REFERENCES.SUP_VERSION_NUMBER,
4073 EXAM_LOCATION_CD = NEW_REFERENCES.EXAM_LOCATION_CD,
4074 ALTERNATIVE_TITLE = NEW_REFERENCES.ALTERNATIVE_TITLE,
4075 OVERRIDE_ENROLLED_CP = NEW_REFERENCES.OVERRIDE_ENROLLED_CP,
4076 OVERRIDE_EFTSU = NEW_REFERENCES.OVERRIDE_EFTSU,
4077 OVERRIDE_ACHIEVABLE_CP = NEW_REFERENCES.OVERRIDE_ACHIEVABLE_CP,
4078 OVERRIDE_OUTCOME_DUE_DT = NEW_REFERENCES.OVERRIDE_OUTCOME_DUE_DT,
4079 OVERRIDE_CREDIT_REASON = NEW_REFERENCES.OVERRIDE_CREDIT_REASON,
4080 ADMINISTRATIVE_PRIORITY = NEW_REFERENCES.ADMINISTRATIVE_PRIORITY,
4081 WAITLIST_DT = NEW_REFERENCES.WAITLIST_DT,
4082 DCNT_REASON_CD = new_references.DCNT_REASON_CD,
4083 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
4084 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
4085 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
4086 REQUEST_ID = X_REQUEST_ID,
4087 PROGRAM_ID = X_PROGRAM_ID,
4088 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
4089 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
4090 GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER,
4091 ENR_METHOD_TYPE = NEW_REFERENCES.ENR_METHOD_TYPE ,
4092 FAILED_UNIT_RULE = NEW_REFERENCES.FAILED_UNIT_RULE ,
4093 CART = NEW_REFERENCES.CART ,
4094 RSV_SEAT_EXT_ID = NEW_REFERENCES.RSV_SEAT_EXT_ID,
4095 ORG_UNIT_CD = NEW_REFERENCES.ORG_UNIT_CD ,
4096 GRADING_SCHEMA_CODE = NEW_REFERENCES.GRADING_SCHEMA_CODE,
4097 subtitle = new_references.subtitle,
4098 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
4099 session_id = new_references.session_id,
4100 deg_aud_detail_id = new_references.deg_aud_detail_id ,
4101 student_career_transcript = new_references.student_career_transcript,
4102 student_career_statistics = new_references.student_career_statistics,
4103 waitlist_manual_ind = new_references.waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
4104 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
4105 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
4106 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
4107 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
4108 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
4109 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
4110 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
4111 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
4112 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
4113 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
4114 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
4115 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
4116 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
4117 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
4118 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
4119 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
4120 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
4121 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
4122 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
4123 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
4124 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
4125 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4126 WLST_PRIORITY_WEIGHT_NUM = NEW_REFERENCES.WLST_PRIORITY_WEIGHT_NUM,
4127 WLST_PREFERENCE_WEIGHT_NUM = NEW_REFERENCES.WLST_PREFERENCE_WEIGHT_NUM,
4128 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
4129 CORE_INDICATOR_CODE = NEW_REFERENCES.CORE_INDICATOR_CODE,
4130 UPD_AUDIT_FLAG = NEW_REFERENCES.UPD_AUDIT_FLAG ,
4131 SS_SOURCE_IND = NEW_REFERENCES. SS_SOURCE_IND
4132 WHERE ROWID = X_ROWID;
4133 IF (SQL%NOTFOUND) THEN
4134 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
4135 igs_ge_msg_stack.add;
4136 igs_sc_gen_001.unset_ctx('R');
4137 app_exception.raise_exception;
4138 END IF;
4139 IF (x_mode = 'S') THEN
4140 igs_sc_gen_001.unset_ctx('R');
4141 END IF;
4142
4143 --
4144 After_DML(
4145 p_action => 'UPDATE',
4146 x_rowid => X_ROWID
4147 );
4148 --
4149 EXCEPTION
4150 WHEN resource_busy THEN
4151 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
4152 IGS_GE_MSG_STACK.ADD;
4153 APP_EXCEPTION.RAISE_EXCEPTION;
4154
4155 WHEN OTHERS THEN
4156 IF (SQLCODE = (-28115)) THEN
4157 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
4158 fnd_message.set_token ('ERR_CD', SQLCODE);
4159 igs_ge_msg_stack.add;
4160 igs_sc_gen_001.unset_ctx('R');
4161 app_exception.raise_exception;
4162 ELSE
4163 igs_sc_gen_001.unset_ctx('R');
4164 RAISE;
4165 END IF;
4166
4167 END UPDATE_ROW;
4168 PROCEDURE ADD_ROW (
4169 X_ROWID IN OUT NOCOPY VARCHAR2,
4170 X_PERSON_ID IN NUMBER,
4171 X_COURSE_CD IN VARCHAR2,
4172 X_UNIT_CD IN VARCHAR2,
4173 X_CAL_TYPE IN VARCHAR2,
4174 X_CI_SEQUENCE_NUMBER IN NUMBER,
4175 X_VERSION_NUMBER IN NUMBER,
4176 X_LOCATION_CD IN VARCHAR2,
4177 X_UNIT_CLASS IN VARCHAR2,
4178 X_CI_START_DT IN DATE,
4179 X_CI_END_DT IN DATE,
4180 X_UOO_ID IN NUMBER,
4181 X_ENROLLED_DT IN DATE,
4182 X_UNIT_ATTEMPT_STATUS IN VARCHAR2,
4183 X_ADMINISTRATIVE_UNIT_STATUS IN VARCHAR2,
4184 X_DISCONTINUED_DT IN DATE,
4185 X_RULE_WAIVED_DT IN DATE,
4186 X_RULE_WAIVED_PERSON_ID IN NUMBER,
4187 X_NO_ASSESSMENT_IND IN VARCHAR2,
4188 X_SUP_UNIT_CD IN VARCHAR2,
4189 X_SUP_VERSION_NUMBER IN NUMBER,
4190 X_EXAM_LOCATION_CD IN VARCHAR2,
4191 X_ALTERNATIVE_TITLE IN VARCHAR2,
4192 X_OVERRIDE_ENROLLED_CP IN NUMBER,
4193 X_OVERRIDE_EFTSU IN NUMBER,
4194 X_OVERRIDE_ACHIEVABLE_CP IN NUMBER,
4195 X_OVERRIDE_OUTCOME_DUE_DT IN DATE,
4196 X_OVERRIDE_CREDIT_REASON IN VARCHAR2,
4197 X_ADMINISTRATIVE_PRIORITY IN NUMBER,
4198 X_WAITLIST_DT IN DATE,
4199 x_dcnt_reason_cd IN VARCHAR2,
4200 X_MODE IN VARCHAR2 ,
4201 x_org_id IN NUMBER,
4202 X_GS_VERSION_NUMBER IN NUMBER ,
4203 X_ENR_METHOD_TYPE IN VARCHAR2 ,
4204 X_FAILED_UNIT_RULE IN VARCHAR2 ,
4205 X_CART IN VARCHAR2 ,
4206 X_RSV_SEAT_EXT_ID IN NUMBER ,
4207 X_ORG_UNIT_CD IN VARCHAR2 ,
4208 X_GRADING_SCHEMA_CODE IN VARCHAR2 ,
4209 x_subtitle IN VARCHAR2 ,
4210 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
4211 x_session_id IN NUMBER ,
4212 X_deg_aud_detail_id IN NUMBER ,
4213 x_student_career_transcript IN VARCHAR2 ,
4214 x_student_career_statistics IN VARCHAR2 ,
4215 x_waitlist_manual_ind IN VARCHAR2,--Bug ID: 2554109 added by adhawan
4216 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
4217 X_ATTRIBUTE1 IN VARCHAR2 ,
4218 X_ATTRIBUTE2 IN VARCHAR2 ,
4219 X_ATTRIBUTE3 IN VARCHAR2 ,
4220 X_ATTRIBUTE4 IN VARCHAR2 ,
4221 X_ATTRIBUTE5 IN VARCHAR2 ,
4222 X_ATTRIBUTE6 IN VARCHAR2 ,
4223 X_ATTRIBUTE7 IN VARCHAR2 ,
4224 X_ATTRIBUTE8 IN VARCHAR2 ,
4225 X_ATTRIBUTE9 IN VARCHAR2 ,
4226 X_ATTRIBUTE10 IN VARCHAR2 ,
4227 X_ATTRIBUTE11 IN VARCHAR2 ,
4228 X_ATTRIBUTE12 IN VARCHAR2 ,
4229 X_ATTRIBUTE13 IN VARCHAR2 ,
4230 X_ATTRIBUTE14 IN VARCHAR2 ,
4231 X_ATTRIBUTE15 IN VARCHAR2 ,
4232 X_ATTRIBUTE16 IN VARCHAR2 ,
4233 X_ATTRIBUTE17 IN VARCHAR2 ,
4234 X_ATTRIBUTE18 IN VARCHAR2 ,
4235 X_ATTRIBUTE19 IN VARCHAR2 ,
4236 x_ATTRIBUTE20 IN VARCHAR2 ,
4237 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4238 X_WLST_PRIORITY_WEIGHT_NUM IN NUMBER ,
4239 X_WLST_PREFERENCE_WEIGHT_NUM IN NUMBER ,
4240 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
4241 X_CORE_INDICATOR_CODE IN VARCHAR2,
4242 X_UPD_AUDIT_FLAG IN VARCHAR2 ,
4243 X_SS_SOURCE_IND IN VARCHAR2
4244 ) AS
4245 CURSOR c1 IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
4246 WHERE PERSON_ID = X_PERSON_ID
4247 AND COURSE_CD = X_COURSE_CD
4248 AND UOO_ID = X_UOO_ID
4249
4250 ;
4251 BEGIN
4252 OPEN c1;
4253 FETCH c1 INTO X_ROWID;
4254 IF (c1%NOTFOUND) THEN
4255 CLOSE c1;
4256 INSERT_ROW (
4257 X_ROWID,
4258 X_PERSON_ID,
4259 X_COURSE_CD,
4260 X_UNIT_CD,
4261 X_CAL_TYPE,
4262 X_CI_SEQUENCE_NUMBER,
4263 X_VERSION_NUMBER,
4264 X_LOCATION_CD,
4265 X_UNIT_CLASS,
4266 X_CI_START_DT,
4267 X_CI_END_DT,
4268 X_UOO_ID,
4269 X_ENROLLED_DT,
4270 X_UNIT_ATTEMPT_STATUS,
4271 X_ADMINISTRATIVE_UNIT_STATUS,
4272 X_DISCONTINUED_DT,
4273 X_RULE_WAIVED_DT,
4274 X_RULE_WAIVED_PERSON_ID,
4275 X_NO_ASSESSMENT_IND,
4276 X_SUP_UNIT_CD,
4277 X_SUP_VERSION_NUMBER,
4278 X_EXAM_LOCATION_CD,
4279 X_ALTERNATIVE_TITLE,
4280 X_OVERRIDE_ENROLLED_CP,
4281 X_OVERRIDE_EFTSU,
4282 X_OVERRIDE_ACHIEVABLE_CP,
4283 X_OVERRIDE_OUTCOME_DUE_DT,
4284 X_OVERRIDE_CREDIT_REASON,
4285 X_ADMINISTRATIVE_PRIORITY,
4286 X_WAITLIST_DT,
4287 X_DCNT_REASON_CD,
4288 X_MODE,
4289 x_org_id ,
4290 X_GS_VERSION_NUMBER ,
4291 X_ENR_METHOD_TYPE ,
4292 X_FAILED_UNIT_RULE ,
4293 X_CART ,
4294 X_RSV_SEAT_EXT_ID ,
4295 X_ORG_UNIT_CD ,
4296 X_GRADING_SCHEMA_CODE ,
4297 X_subtitle,
4298 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
4299 x_session_id,
4300 X_deg_aud_detail_id ,
4301 x_student_career_transcript,
4302 x_student_career_statistics,
4303 X_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
4304 X_ATTRIBUTE_CATEGORY,
4305 X_ATTRIBUTE1,
4306 X_ATTRIBUTE2,
4307 X_ATTRIBUTE3,
4308 X_ATTRIBUTE4,
4309 X_ATTRIBUTE5,
4310 X_ATTRIBUTE6,
4311 X_ATTRIBUTE7,
4312 X_ATTRIBUTE8,
4313 X_ATTRIBUTE9,
4314 X_ATTRIBUTE10,
4315 X_ATTRIBUTE11,
4316 X_ATTRIBUTE12,
4317 X_ATTRIBUTE13,
4318 X_ATTRIBUTE14,
4319 X_ATTRIBUTE15,
4320 X_ATTRIBUTE16,
4321 X_ATTRIBUTE17,
4322 X_ATTRIBUTE18,
4323 X_ATTRIBUTE19,
4324 X_ATTRIBUTE20,
4325 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4326 X_WLST_PRIORITY_WEIGHT_NUM,
4327 X_WLST_PREFERENCE_WEIGHT_NUM,
4328 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
4329 X_CORE_INDICATOR_CODE,
4330 X_UPD_AUDIT_FLAG,
4331 X_SS_SOURCE_IND
4332 );
4333 RETURN;
4334 END IF;
4335 CLOSE c1;
4336 UPDATE_ROW (
4337 X_ROWID,
4338 X_PERSON_ID,
4339 X_COURSE_CD,
4340 X_UNIT_CD,
4341 X_CAL_TYPE,
4342 X_CI_SEQUENCE_NUMBER,
4343 X_VERSION_NUMBER,
4344 X_LOCATION_CD,
4345 X_UNIT_CLASS,
4346 X_CI_START_DT,
4347 X_CI_END_DT,
4348 X_UOO_ID,
4349 X_ENROLLED_DT,
4350 X_UNIT_ATTEMPT_STATUS,
4351 X_ADMINISTRATIVE_UNIT_STATUS,
4352 X_DISCONTINUED_DT,
4353 X_RULE_WAIVED_DT,
4354 X_RULE_WAIVED_PERSON_ID,
4355 X_NO_ASSESSMENT_IND,
4356 X_SUP_UNIT_CD,
4357 X_SUP_VERSION_NUMBER,
4358 X_EXAM_LOCATION_CD,
4359 X_ALTERNATIVE_TITLE,
4360 X_OVERRIDE_ENROLLED_CP,
4361 X_OVERRIDE_EFTSU,
4362 X_OVERRIDE_ACHIEVABLE_CP,
4363 X_OVERRIDE_OUTCOME_DUE_DT,
4364 X_OVERRIDE_CREDIT_REASON,
4365 X_ADMINISTRATIVE_PRIORITY,
4366 X_WAITLIST_DT,
4367 X_DCNT_REASON_CD,
4368 X_MODE,
4369 X_GS_VERSION_NUMBER,
4370 X_ENR_METHOD_TYPE,
4371 X_FAILED_UNIT_RULE,
4372 X_CART ,
4373 X_RSV_SEAT_EXT_ID ,
4374 X_ORG_UNIT_CD,
4375 X_GRADING_SCHEMA_CODE ,
4376 X_subtitle,
4377 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
4378 x_session_id,
4379 X_deg_aud_detail_id ,
4380 x_student_career_transcript,
4381 x_student_career_statistics,
4382 X_waitlist_manual_ind,--Bug ID: 2554109 added by adhawan
4383 X_ATTRIBUTE_CATEGORY,
4384 X_ATTRIBUTE1,
4385 X_ATTRIBUTE2,
4386 X_ATTRIBUTE3,
4387 X_ATTRIBUTE4,
4388 X_ATTRIBUTE5,
4389 X_ATTRIBUTE6,
4390 X_ATTRIBUTE7,
4391 X_ATTRIBUTE8,
4392 X_ATTRIBUTE9,
4393 X_ATTRIBUTE10,
4394 X_ATTRIBUTE11,
4395 X_ATTRIBUTE12,
4396 X_ATTRIBUTE13,
4397 X_ATTRIBUTE14,
4398 X_ATTRIBUTE15,
4399 X_ATTRIBUTE16,
4400 X_ATTRIBUTE17,
4401 X_ATTRIBUTE18,
4402 X_ATTRIBUTE19,
4403 X_ATTRIBUTE20,
4404 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4405 X_WLST_PRIORITY_WEIGHT_NUM,
4406 X_WLST_PREFERENCE_WEIGHT_NUM,
4407 -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
4408 X_CORE_INDICATOR_CODE,
4409 X_UPD_AUDIT_FLAG,
4410 X_SS_SOURCE_IND
4411 );
4412 END ADD_ROW;
4413 PROCEDURE DELETE_ROW (
4414 X_ROWID IN VARCHAR2,
4415 x_mode IN VARCHAR2) AS
4416
4417 /* Cursor to get the unit attempt attempts attributes, bug 3000742*/
4418 CURSOR cur_sua (cp_rowid VARCHAR2) IS
4419 SELECT *
4420 FROM igs_en_su_attempt
4421 WHERE row_id = cp_rowid;
4422
4423 /* Cursor to check whether the unit attempt status before adding to cart is DROPPED, bug 3000742*/
4424 CURSOR cur_sua_hist (cp_person_id NUMBER,cp_course_cd VARCHAR2, cp_uoo_id NUMBER) IS
4425 SELECT unit_attempt_status
4426 FROM igs_en_su_attempt_h
4427 WHERE person_id = cp_person_id
4428 AND course_cd = cp_course_cd
4429 AND uoo_id = cp_uoo_id
4430 ORDER BY hist_end_dt DESC;
4431
4432 l_cur_sua_rec cur_sua%ROWTYPE;
4433 l_sua_status igs_en_su_attempt.unit_attempt_status%TYPE;
4434
4435 BEGIN
4436
4437 OPEN cur_sua(X_ROWID);
4438 FETCH cur_sua INTO l_cur_sua_rec;
4439 CLOSE cur_sua;
4440 --
4441 OPEN cur_sua_hist(l_cur_sua_rec.person_id,l_cur_sua_rec.course_cd,l_cur_sua_rec.uoo_id );
4442 FETCH cur_sua_hist INTO l_sua_status;
4443 CLOSE cur_sua_hist;
4444 --
4445 -- If the unit attempt status before adding to cart was DROPPED then while cleaning the cart move back the unit
4446 -- to DROPPED status.
4447 --
4448 IF l_sua_status='DROPPED' THEN
4449 igs_en_sua_api.update_unit_attempt(
4450 X_ROWID => X_ROWID,
4451 X_PERSON_ID => l_cur_sua_rec.PERSON_ID,
4452 X_COURSE_CD => l_cur_sua_rec.COURSE_CD ,
4453 X_UNIT_CD => l_cur_sua_rec.UNIT_CD,
4454 X_CAL_TYPE => l_cur_sua_rec.CAL_TYPE,
4455 X_CI_SEQUENCE_NUMBER => l_cur_sua_rec.CI_SEQUENCE_NUMBER ,
4456 X_VERSION_NUMBER => l_cur_sua_rec.version_number ,
4457 X_LOCATION_CD => l_cur_sua_rec.location_cd,
4458 X_UNIT_CLASS => l_cur_sua_rec.unit_class,
4459 X_CI_START_DT => l_cur_sua_rec.CI_START_DT,
4460 X_CI_END_DT => l_cur_sua_rec.CI_END_DT,
4461 X_UOO_ID => l_cur_sua_rec.uoo_id,
4462 X_ENROLLED_DT => l_cur_sua_rec.ENROLLED_DT,
4463 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
4464 X_ADMINISTRATIVE_UNIT_STATUS => l_cur_sua_rec.administrative_unit_status,
4465 X_ADMINISTRATIVE_PRIORITY => l_cur_sua_rec.administrative_priority,
4466 X_DISCONTINUED_DT => nvl(l_cur_sua_rec.discontinued_dt,SYSDATE),
4467 X_DCNT_REASON_CD => l_cur_sua_rec.DCNT_REASON_CD ,
4468 X_RULE_WAIVED_DT => l_cur_sua_rec.RULE_WAIVED_DT ,
4469 X_RULE_WAIVED_PERSON_ID => l_cur_sua_rec.RULE_WAIVED_PERSON_ID ,
4470 X_NO_ASSESSMENT_IND => l_cur_sua_rec.NO_ASSESSMENT_IND,
4471 X_SUP_UNIT_CD => l_cur_sua_rec.SUP_UNIT_CD ,
4472 X_SUP_VERSION_NUMBER => l_cur_sua_rec.SUP_VERSION_NUMBER,
4473 X_EXAM_LOCATION_CD => l_cur_sua_rec.EXAM_LOCATION_CD,
4474 X_ALTERNATIVE_TITLE => l_cur_sua_rec.ALTERNATIVE_TITLE ,
4475 X_OVERRIDE_ENROLLED_CP => l_cur_sua_rec.OVERRIDE_ENROLLED_CP,
4476 X_OVERRIDE_EFTSU => l_cur_sua_rec.OVERRIDE_EFTSU ,
4477 X_OVERRIDE_ACHIEVABLE_CP => l_cur_sua_rec.OVERRIDE_ACHIEVABLE_CP,
4478 X_OVERRIDE_OUTCOME_DUE_DT => l_cur_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
4479 X_OVERRIDE_CREDIT_REASON => l_cur_sua_rec.OVERRIDE_CREDIT_REASON,
4480 X_WAITLIST_DT => l_cur_sua_rec.waitlist_dt,
4481 X_MODE => 'R' ,
4482 X_GS_VERSION_NUMBER => l_cur_sua_rec.gs_version_number,
4483 X_ENR_METHOD_TYPE => l_cur_sua_rec.enr_method_type,
4484 X_FAILED_UNIT_RULE => l_cur_sua_rec.failed_unit_rule ,
4485 X_CART => l_cur_sua_rec.cart ,
4486 X_RSV_SEAT_EXT_ID => l_cur_sua_rec.rsv_seat_ext_id,
4487 X_ORG_UNIT_CD => l_cur_sua_rec.org_unit_cd,
4488 X_SESSION_ID => l_cur_sua_rec.session_id,
4489 X_GRADING_SCHEMA_CODE => l_cur_sua_rec.grading_schema_code,
4490 X_DEG_AUD_DETAIL_ID => l_cur_sua_rec.deg_aud_detail_id,
4491 X_SUBTITLE => l_cur_sua_rec.subtitle,
4492 X_STUDENT_CAREER_TRANSCRIPT => l_cur_sua_rec.student_career_transcript ,
4493 X_STUDENT_CAREER_STATISTICS => l_cur_sua_rec.student_career_statistics,
4494 X_ATTRIBUTE_CATEGORY => l_cur_sua_rec.attribute_category,
4495 X_ATTRIBUTE1 => l_cur_sua_rec.attribute1,
4496 X_ATTRIBUTE2 => l_cur_sua_rec.attribute2,
4497 X_ATTRIBUTE3 => l_cur_sua_rec.attribute3,
4498 X_ATTRIBUTE4 => l_cur_sua_rec.attribute4,
4499 X_ATTRIBUTE5 => l_cur_sua_rec.attribute5,
4500 X_ATTRIBUTE6 => l_cur_sua_rec.attribute6,
4501 X_ATTRIBUTE7 => l_cur_sua_rec.attribute7,
4502 X_ATTRIBUTE8 => l_cur_sua_rec.attribute8,
4503 X_ATTRIBUTE9 => l_cur_sua_rec.attribute9,
4504 X_ATTRIBUTE10 => l_cur_sua_rec.attribute10,
4505 X_ATTRIBUTE11 => l_cur_sua_rec.attribute11,
4506 X_ATTRIBUTE12 => l_cur_sua_rec.attribute12,
4507 X_ATTRIBUTE13 => l_cur_sua_rec.attribute13,
4508 X_ATTRIBUTE14 => l_cur_sua_rec.attribute14,
4509 X_ATTRIBUTE15 => l_cur_sua_rec.attribute15,
4510 X_ATTRIBUTE16 => l_cur_sua_rec.attribute16,
4511 X_ATTRIBUTE17 => l_cur_sua_rec.attribute17,
4512 X_ATTRIBUTE18 => l_cur_sua_rec.attribute18,
4513 X_ATTRIBUTE19 => l_cur_sua_rec.attribute19,
4514 X_ATTRIBUTE20 => l_cur_sua_rec.attribute20,
4515 X_WAITLIST_MANUAL_IND => l_cur_sua_rec.waitlist_manual_ind,
4516 -- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
4517 X_WLST_PRIORITY_WEIGHT_NUM => l_cur_sua_rec.wlst_priority_weight_num,
4518 X_WLST_PREFERENCE_WEIGHT_NUM => l_cur_sua_rec.wlst_preference_weight_num,
4519 -- CORE_INDICATOR_CODE added by ptandon 01-OCT-2003. Enh Bug# 3052432
4520 X_CORE_INDICATOR_CODE => l_cur_sua_rec.core_indicator_code/*,
4521 X_UPD_AUDIT_FLAG => l_cur_sua_rec.UPD_AUDIT_FLAG ,
4522 X_SS_SOURCE_IND => l_cur_sua_rec.SS_SOURCE_IND */
4523 );
4524 ELSE
4525 --
4526
4527 Before_DML(
4528 p_action => 'DELETE',
4529 x_rowid => X_ROWID
4530 );
4531
4532 IF (x_mode = 'S') THEN
4533 igs_sc_gen_001.set_ctx('R');
4534 END IF;
4535 DELETE FROM IGS_EN_SU_ATTEMPT_ALL
4536 WHERE ROWID = X_ROWID;
4537
4538 IF (SQL%NOTFOUND) THEN
4539 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
4540 igs_ge_msg_stack.add;
4541 igs_sc_gen_001.unset_ctx('R');
4542 app_exception.raise_exception;
4543 END IF;
4544 IF (x_mode = 'S') THEN
4545 igs_sc_gen_001.unset_ctx('R');
4546 END IF;
4547
4548 --
4549 After_DML(
4550 p_action => 'DELETE',
4551 x_rowid => X_ROWID
4552 );
4553 --
4554 END IF;
4555
4556 END DELETE_ROW;
4557 PROCEDURE Check_Constraints (
4558 Column_Name IN VARCHAR2,
4559 Column_Value IN VARCHAR2
4560 )
4561 AS
4562 BEGIN
4563 IF column_name IS NULL THEN
4564 NULL;
4565 ELSIF UPPER(Column_name) = 'ADMINISTRATIVE_UNIT_STATUS' THEN
4566 new_references.ADMINISTRATIVE_UNIT_STATUS := column_value;
4567 ELSIF UPPER(Column_name) = 'ALTERNATIVE_TITLE' THEN
4568 new_references.ALTERNATIVE_TITLE := column_value;
4569 ELSIF UPPER(Column_name) = 'CAL_TYPE' THEN
4570 new_references.CAL_TYPE := column_value;
4571 ELSIF UPPER(Column_name) = 'COURSE_CD' THEN
4572 new_references.COURSE_CD := column_value;
4573 ELSIF UPPER(Column_name) = 'EXAM_LOCATION_CD' THEN
4574 new_references.EXAM_LOCATION_CD := column_value;
4575 ELSIF UPPER(Column_name) = 'LOCATION_CD' THEN
4576 new_references.LOCATION_CD := column_value;
4577 ELSIF UPPER(Column_name) = 'NO_ASSESSMENT_IND' THEN
4578 new_references.NO_ASSESSMENT_IND := column_value;
4579 ELSIF UPPER(Column_name) = 'SUP_UNIT_CD' THEN
4580 new_references.SUP_UNIT_CD := column_value;
4581 ELSIF UPPER(Column_name) = 'UNIT_ATTEMPT_STATUS' THEN
4582 new_references.UNIT_ATTEMPT_STATUS := column_value;
4583 ELSIF UPPER(Column_name) = 'UNIT_CD' THEN
4584 new_references.UNIT_CD := column_value;
4585 ELSIF UPPER(Column_name) = 'UNIT_CLASS' THEN
4586 new_references.UNIT_CLASS := column_value;
4587 ELSIF UPPER(Column_name) = 'OVERRIDE_EFTSU' THEN
4588 new_references.OVERRIDE_EFTSU := igs_ge_number.to_num(column_value);
4589 ELSIF UPPER(Column_name) = 'OVERRIDE_ENROLLED_CP' THEN
4590 new_references.OVERRIDE_ENROLLED_CP := igs_ge_number.to_num(column_value);
4591 ELSIF UPPER(Column_name) = 'NO_ASSESSMENT_IND' THEN
4592 new_references.NO_ASSESSMENT_IND := column_value;
4593 ELSIF UPPER(Column_name) = 'OVERRIDE_ACHIEVABLE_CP' THEN
4594 new_references.OVERRIDE_ACHIEVABLE_CP := igs_ge_number.to_num(column_value);
4595 ELSIF UPPER(Column_name) = 'DCNT_REASON_CD' THEN
4596 new_references.DCNT_REASON_CD := column_value;
4597 END IF;
4598 IF UPPER(column_name) = 'ADMINISTRATIVE_UNIT_STATUS' OR
4599 column_name IS NULL THEN
4600 IF new_references.ADMINISTRATIVE_UNIT_STATUS <> UPPER(new_references.ADMINISTRATIVE_UNIT_STATUS) THEN
4601 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4602 IGS_GE_MSG_STACK.ADD;
4603 APP_EXCEPTION.RAISE_EXCEPTION;
4604 END IF;
4605 END IF;
4606 IF UPPER(column_name) = 'ALTERNATIVE_TITLE' OR
4607 column_name IS NULL THEN
4608 IF new_references.ALTERNATIVE_TITLE <> UPPER(new_references.ALTERNATIVE_TITLE) THEN
4609 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4610 IGS_GE_MSG_STACK.ADD;
4611 APP_EXCEPTION.RAISE_EXCEPTION;
4612 END IF;
4613 END IF;
4614 IF UPPER(column_name) = 'CAL_TYPE' OR
4615 column_name IS NULL THEN
4616 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) THEN
4617 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4618 IGS_GE_MSG_STACK.ADD;
4619 APP_EXCEPTION.RAISE_EXCEPTION;
4620 END IF;
4621 END IF;
4622 IF UPPER(column_name) = 'COURSE_CD' OR
4623 column_name IS NULL THEN
4624 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) THEN
4625 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4626 IGS_GE_MSG_STACK.ADD;
4627 APP_EXCEPTION.RAISE_EXCEPTION;
4628 END IF;
4629 END IF;
4630 IF UPPER(column_name) = 'EXAM_LOCATION_CD' OR
4631 column_name IS NULL THEN
4632 IF new_references.EXAM_LOCATION_CD <> UPPER(new_references.EXAM_LOCATION_CD) THEN
4633 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4634 IGS_GE_MSG_STACK.ADD;
4635 APP_EXCEPTION.RAISE_EXCEPTION;
4636 END IF;
4637 END IF;
4638 IF UPPER(column_name) = 'LOCATION_CD' OR
4639 column_name IS NULL THEN
4640 IF new_references.LOCATION_CD <> UPPER(new_references.LOCATION_CD) THEN
4641 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4642 IGS_GE_MSG_STACK.ADD;
4643 APP_EXCEPTION.RAISE_EXCEPTION;
4644 END IF;
4645 END IF;
4646 IF UPPER(column_name) = 'NO_ASSESSMENT_IND' OR
4647 column_name IS NULL THEN
4648 IF new_references.NO_ASSESSMENT_IND <> UPPER(new_references.NO_ASSESSMENT_IND) THEN
4649 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4650 IGS_GE_MSG_STACK.ADD;
4651 APP_EXCEPTION.RAISE_EXCEPTION;
4652 END IF;
4653 END IF;
4654 IF UPPER(column_name) = 'SUP_UNIT_CD' OR
4655 column_name IS NULL THEN
4656 IF new_references.SUP_UNIT_CD <> UPPER(new_references.SUP_UNIT_CD) THEN
4657 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4658 IGS_GE_MSG_STACK.ADD;
4659 APP_EXCEPTION.RAISE_EXCEPTION;
4660 END IF;
4661 END IF;
4662 IF UPPER(column_name) = 'UNIT_ATTEMPT_STATUS' OR
4663 column_name IS NULL THEN
4664 IF new_references.UNIT_ATTEMPT_STATUS <> UPPER(new_references.UNIT_ATTEMPT_STATUS) THEN
4665 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4666 IGS_GE_MSG_STACK.ADD;
4667 APP_EXCEPTION.RAISE_EXCEPTION;
4668 END IF;
4669 END IF;
4670 IF UPPER(column_name) = 'UNIT_CD' OR
4671 column_name IS NULL THEN
4672 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) THEN
4673 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4674 IGS_GE_MSG_STACK.ADD;
4675 APP_EXCEPTION.RAISE_EXCEPTION;
4676 END IF;
4677 END IF;
4678 IF UPPER(column_name) = 'UNIT_CLASS' OR
4679 column_name IS NULL THEN
4680 IF new_references.UNIT_CLASS <> UPPER(new_references.UNIT_CLASS) THEN
4681 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4682 IGS_GE_MSG_STACK.ADD;
4683 APP_EXCEPTION.RAISE_EXCEPTION;
4684 END IF;
4685 END IF;
4686 IF UPPER(column_name) = 'OVERRIDE_EFTSU' OR
4687 column_name IS NULL THEN
4688 IF new_references.OVERRIDE_EFTSU < 0 OR new_references.OVERRIDE_EFTSU > 9999.999 THEN
4689 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4690 IGS_GE_MSG_STACK.ADD;
4691 APP_EXCEPTION.RAISE_EXCEPTION;
4692 END IF;
4693 END IF;
4694 IF UPPER(column_name) = 'OVERRIDE_ENROLLED_CP' OR
4695 column_name IS NULL THEN
4696 IF new_references.OVERRIDE_ENROLLED_CP < 0 OR new_references.OVERRIDE_ENROLLED_CP> 999.999 THEN
4697 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4698 IGS_GE_MSG_STACK.ADD;
4699 APP_EXCEPTION.RAISE_EXCEPTION;
4700 END IF;
4701 END IF;
4702 IF UPPER(column_name) = 'NO_ASSESSMENT_IND' OR
4703 column_name IS NULL THEN
4704 IF new_references.NO_ASSESSMENT_IND NOT IN ('Y','N') THEN
4705 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4706 IGS_GE_MSG_STACK.ADD;
4707 APP_EXCEPTION.RAISE_EXCEPTION;
4708 END IF;
4709 END IF;
4710 IF UPPER(column_name) = 'OVERRIDE_ACHIEVABLE_CP' OR
4711 column_name IS NULL THEN
4712 IF new_references.OVERRIDE_ACHIEVABLE_CP < 0 OR new_references.OVERRIDE_ACHIEVABLE_CP > 999.999 THEN
4713 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4714 IGS_GE_MSG_STACK.ADD;
4715 APP_EXCEPTION.RAISE_EXCEPTION;
4716 END IF;
4717 END IF;
4718 IF UPPER(column_name) = 'DCNT_REASON_CD' OR
4719 column_name IS NULL THEN
4720 IF new_references.DCNT_REASON_CD <> UPPER(new_references.DCNT_REASON_CD) THEN
4721 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
4722 IGS_GE_MSG_STACK.ADD;
4723 APP_EXCEPTION.RAISE_EXCEPTION;
4724 END IF;
4725
4726 END IF;
4727 END Check_Constraints;
4728 -- Function for getting the UK
4729 -- If the record exists for the parameters passed, then it returns TRUE
4730 -- Else it returns false
4731 --changed the UK columns as part of MUS build, # 2829262
4732
4733 FUNCTION Get_Uk_For_Validation (
4734 x_unit_cd IN VARCHAR2,
4735 x_cal_type IN VARCHAR2,
4736 x_ci_sequence_number IN NUMBER,
4737 x_location_cd IN VARCHAR2,
4738 x_unit_class IN VARCHAR2,
4739 x_person_id IN NUMBER,
4740 x_course_cd IN VARCHAR2,
4741 x_version_number IN NUMBER
4742 ) RETURN BOOLEAN AS
4743 CURSOR cur_sua IS
4744 SELECT ROWID
4745 FROM IGS_EN_SU_ATTEMPT_ALL
4746 WHERE unit_cd = x_unit_cd
4747 AND cal_type = x_cal_type
4748 AND ci_sequence_number = x_ci_sequence_number
4749 AND location_cd = x_location_cd
4750 AND unit_class = x_unit_class
4751 AND person_id = x_person_id
4752 AND course_cd = x_course_cd
4753 AND version_number = x_version_number
4754 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
4755 lv_row_id cur_sua%ROWTYPE;
4756 BEGIN
4757 /*IF x_administrative_priority IN (NULL,0) THEN
4758 RETURN (FALSE);
4759 ELSE*/
4760 OPEN cur_sua;
4761 FETCH cur_sua INTO lv_row_id;
4762 IF cur_sua%FOUND THEN
4763 CLOSE cur_sua;
4764 RETURN(TRUE);
4765 ELSE
4766 CLOSE cur_sua;
4767 RETURN(FALSE);
4768 END IF;
4769
4770 END Get_Uk_For_Validation;
4771
4772 PROCEDURE update_reserved_seat( p_action IN VARCHAR2)
4773 AS
4774 --
4775 -- For Enhancement Bug 1832830
4776 -- Local Procedure added to decrement actual_seat_enrolled once the
4777 -- unit administrative status changes from ENROLLED/INVALID to DROPPED/DISCCONTIN
4778 -- Called in After DML
4779 --
4780 -- 25-Jun-2002 pmarada Added the code when the unit deleting from the su_attempt, decreasing the
4781 -- actual enrollment. bug 2423787
4782
4783 CURSOR cur_igs_ps_rsv_ext (cp_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE)
4784 IS
4785 SELECT rsv.ROWID row_id, rsv.*
4786 FROM igs_ps_rsv_ext rsv
4787 WHERE rsv_ext_id = cp_rsv_ext_id;
4788
4789 l_cur_igs_ps_rsv_ext cur_igs_ps_rsv_ext%ROWTYPE;
4790 l_rsv_ext_id igs_ps_rsv_ext.rsv_ext_id%TYPE;
4791
4792 BEGIN
4793 IF ( p_action = 'UPDATE') THEN
4794 -- checking if the unit attempt status has been changed from ENROLLED/INVALID to DROPPED/DISCONTIN
4795 IF (old_references.unit_attempt_status IN ('ENROLLED','INVALID')
4796 AND (new_references.unit_attempt_status IN ('DROPPED','DISCONTIN'))) THEN
4797 IF ( old_references.rsv_seat_ext_id = new_references.rsv_seat_ext_id ) THEN
4798 l_rsv_ext_id := old_references.rsv_seat_ext_id;
4799 ELSE
4800 l_rsv_ext_id := new_references.rsv_seat_ext_id;
4801 END IF;
4802
4803 OPEN cur_igs_ps_rsv_ext(l_rsv_ext_id);
4804 FETCH cur_igs_ps_rsv_ext INTO l_cur_igs_ps_rsv_ext;
4805 CLOSE cur_igs_ps_rsv_ext;
4806
4807
4808 -- If the unit attempt status is changed then the actual seats enrolled column has to be decreased by one
4809 IF ((l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1) >= 0) THEN
4810 igs_ps_rsv_ext_pkg.update_row( x_rowid => l_cur_igs_ps_rsv_ext.row_id,
4811 x_rsv_ext_id => l_cur_igs_ps_rsv_ext.rsv_ext_id,
4812 x_uoo_id => l_cur_igs_ps_rsv_ext.uoo_id,
4813 x_priority_id => l_cur_igs_ps_rsv_ext.priority_id,
4814 x_preference_id => l_cur_igs_ps_rsv_ext.preference_id,
4815 x_rsv_level => l_cur_igs_ps_rsv_ext.rsv_level,
4816 x_actual_seat_enrolled => l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1,
4817 x_mode => 'R'
4818 );
4819 END IF;
4820 END IF;
4821 ELSIF p_action ='DELETE' THEN
4822 -- While deleting the su attempt record, if record exists in the reserve extension table with this reserve extension id decreasing the
4823 -- actual seat enrollment.
4824 -- Suppose user added the unit to cart and closed the browser or timeout, the actual enrollment is matching.
4825 -- while user login into SS, decrementing the actual seat enrollment., pmarada, bug 2423787
4826
4827 IF old_references.rsv_seat_ext_id IS NOT NULL THEN
4828 OPEN cur_igs_ps_rsv_ext(old_references.rsv_seat_ext_id);
4829 FETCH cur_igs_ps_rsv_ext INTO l_cur_igs_ps_rsv_ext;
4830 CLOSE cur_igs_ps_rsv_ext;
4831 IF ((l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1) >= 0) THEN
4832 igs_ps_rsv_ext_pkg.update_row( x_rowid => l_cur_igs_ps_rsv_ext.row_id,
4833 x_rsv_ext_id => l_cur_igs_ps_rsv_ext.rsv_ext_id,
4834 x_uoo_id => l_cur_igs_ps_rsv_ext.uoo_id,
4835 x_priority_id => l_cur_igs_ps_rsv_ext.priority_id,
4836 x_preference_id => l_cur_igs_ps_rsv_ext.preference_id,
4837 x_rsv_level => l_cur_igs_ps_rsv_ext.rsv_level,
4838 x_actual_seat_enrolled => l_cur_igs_ps_rsv_ext.actual_seat_enrolled -1,
4839 x_mode => 'R'
4840 );
4841 END IF;
4842 END IF;
4843 END IF;
4844 END update_reserved_seat;
4845
4846 END Igs_En_Su_Attempt_Pkg;
[Home] [Help]