[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ENROLL_WLST
Source
1 PACKAGE BODY Igs_En_Enroll_Wlst AS
2 /* $Header: IGSEN73B.pls 120.9 2005/12/12 03:32:10 appldev ship $ */
3
4 /*** Enrolling Persons - Called in loop for Validated filtered student PL/SQL tables **/
5 PROCEDURE Enroll_Persons(p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
6 p_person_id igs_pe_person.person_id%TYPE,
7 p_course_cd igs_en_su_attempt.course_cd%TYPE,
8 p_waitlist_actual IN OUT NOCOPY igs_ps_unit_ofr_opt.waitlist_actual%TYPE,
9 p_enrollment_actual IN OUT NOCOPY igs_ps_unit_ofr_opt.enrollment_actual%TYPE,
10 p_max_quota IN igs_ps_usec_lim_wlst.enrollment_maximum%TYPE,
11 p_max_stdnts_per_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE,
12 p_enrolled_yn OUT NOCOPY VARCHAR2 ,
13 p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
14 p_version_number igs_en_su_attempt.version_number%TYPE,
15 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
16 ) AS
17
18
19 /******************************************************************
20 Created By :Syam
21 Date Created By :
22 Purpose :Enrolling Persons - Called in loop for Validated filtered student PL/SQL tables
23 Known limitations,
24 enhancements,
25 remarks :
26
27
28 Change History
29 Who When What
30 jbegum 25-Jun-2003 BUG#2930935 - Modified cursor unit_ver_min_pts_cur
31 kkillams 19-June-03 Added code to log messages raised in exception block in Enroll_Persons.
32 Nishikant 13jun2002 some commented code were removed as per bug#2413811
33 ptandon 26 Jun 2003 Modified to display person_number of students enrolled from Waitlist. - Bug# 2841584
34 ptandon 05-SEP-2003 Added to more parameters WLST_PRIORITY_WEIGHT_NUM and
35 WLST_PRIORITY_WEIGHT_NUM in call to igs_en_sua_api.update_unit_attempt
36 as part of Waitlist Enhancements Build. Enh Bug# 3052426.
37 ******************************************************************/
38
39
40 CURSOR c_sua
41 IS
42 SELECT sua.ROWID, sua.*
43 FROM IGS_EN_SU_ATTEMPT sua
44 WHERE uoo_id=p_uoo_id
45 AND unit_attempt_status = 'WAITLISTED'
46 AND person_id = p_person_id
47 AND course_cd = p_course_cd;
48
49 CURSOR unit_ver_min_pts_cur
50 IS
51 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points),
52 uv.points_override_ind
53 FROM igs_ps_unit_ver uv,
54 igs_ps_usec_cps cps,
55 igs_ps_unit_ofr_opt uoo
56 WHERE uoo.uoo_id = cps.uoo_id(+) AND
57 uoo.unit_cd = uv.unit_cd AND
58 uoo.version_number = uv.version_number AND
59 uoo.uoo_id = p_uoo_id;
60
61 CURSOR usec_min_pts_cur
62 IS
63 SELECT minimum_credit_points
64 FROM igs_ps_usec_cps
65 WHERE uoo_id = p_uoo_id;
66
67 lv_rec_ind NUMBER;
68 lv_enrolled_yn VARCHAR2(1);
69 lv_minimum_credit_points igs_ps_usec_cps.minimum_credit_points%TYPE;
70 lv_enrolled_credit_points igs_ps_unit_ver.enrolled_credit_points%TYPE;
71 lv_points_override_ind igs_ps_unit_ver.points_override_ind%TYPE;
72 l_exp_err VARCHAR2(2000);
73
74 CURSOR c_find_person_no
75 IS
76 SELECT party_number
77 FROM hz_parties
78 WHERE party_id=p_person_id;
79
80 v_person_number igs_pe_person.person_number%TYPE;
81
82 /** Start of enrol persons **/
83
84
85 BEGIN --Enroll_Persons
86
87 lv_enrolled_yn := 'N';
88 p_enrolled_yn := 'N' ;
89
90 OPEN unit_ver_min_pts_cur;
91 FETCH unit_ver_min_pts_cur INTO lv_enrolled_credit_points,
92 lv_points_override_ind;
93 CLOSE unit_ver_min_pts_cur;
94
95 OPEN usec_min_pts_cur;
96 FETCH usec_min_pts_cur INTO lv_minimum_credit_points;
97 CLOSE usec_min_pts_cur;
98
99 FOR i IN c_sua LOOP
100
101 -- Commented the following code as part of bug# 2396138.
102 /* IF lv_points_override_ind = 'Y' THEN
103 i.override_enrolled_cp :=lv_minimum_credit_points;
104 ELSIF lv_points_override_ind = 'N' THEN
105 i.override_enrolled_cp :=lv_enrolled_credit_points;
106 END IF;
107 */
108 BEGIN
109
110 -- Call the API to update the student unit attempt. This API is a
111 -- wrapper to the update row of the TBH.
112 igs_en_sua_api.update_unit_attempt(
113 x_rowid=>i.ROWID,
114 x_person_id=>i.person_id,
115 x_course_cd=>i.course_cd,
116 x_unit_cd=>i.unit_cd,
117 x_cal_type=>i.cal_type,
118 x_ci_sequence_number=>i.ci_sequence_number,
119 x_version_number=>i.version_number,
120 x_location_cd=>i.location_cd,
121 x_unit_class=>i.unit_class,
122 x_ci_start_dt=>i.ci_start_dt,
123 x_ci_end_dt=>i.ci_end_dt,
124 x_uoo_id=>i.uoo_id,
125 x_enrolled_dt=>SYSDATE,
126 x_unit_attempt_status=>'ENROLLED',
127 x_administrative_unit_status=>i.administrative_unit_status,
128 x_discontinued_dt=>i.discontinued_dt,
129 x_dcnt_reason_cd =>i.dcnt_reason_cd ,
130 x_rule_waived_dt=>i.rule_waived_dt,
131 x_rule_waived_person_id=>i.rule_waived_person_id,
132 x_no_assessment_ind=>i.no_assessment_ind,
133 x_sup_unit_cd=>i.sup_unit_cd,
134 x_sup_version_number=>i.sup_version_number,
135 x_exam_location_cd=>i.exam_location_cd,
136 x_alternative_title=>i.alternative_title,
137 x_override_enrolled_cp=>i.override_enrolled_cp,
138 x_override_eftsu=>i.override_eftsu,
139 x_override_achievable_cp=>i.override_achievable_cp,
140 x_override_outcome_due_dt=>i.override_outcome_due_dt,
141 x_override_credit_reason=>i.override_credit_reason,
142 x_administrative_priority=>i.administrative_priority,
143 x_waitlist_dt=> NULL, -- i.waitlist_dt, -- Update the waitlist_dt with 'Null'. This is as per the Bug# 2335455.
144 x_gs_version_number => i.gs_version_number,
145 x_enr_method_type => i.enr_method_type,
146 x_failed_unit_rule => i.failed_unit_rule,
147 x_cart => 'N', -- ( Enrolment Done - so removed from cart )
148 x_rsv_seat_ext_id => i.rsv_seat_ext_id,
149 x_mode=>'R',
150 x_org_unit_cd => i.org_unit_cd,
151 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
152 x_session_id => i.session_id,
153 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
154 X_GRADING_SCHEMA_CODE => i.grading_schema_code,
155 -- Added the column Deg_Aud_Detail_Id as part of
156 -- Degree Audit Interface build. Bug# 2033208 - by pradhakr
157 X_DEG_AUD_DETAIL_ID => i.deg_aud_detail_id ,
158 X_SUBTITLE => i.subtitle,
159 X_STUDENT_CAREER_TRANSCRIPT => i.student_career_transcript,
160 X_STUDENT_CAREER_STATISTICS => i.student_career_statistics,
161 X_ATTRIBUTE_CATEGORY => i.attribute_category,
162 X_ATTRIBUTE1 => i.attribute1,
163 X_ATTRIBUTE2 => i.attribute2,
164 X_ATTRIBUTE3 => i.attribute3,
165 X_ATTRIBUTE4 => i.attribute4,
166 X_ATTRIBUTE5 => i.attribute5,
167 X_ATTRIBUTE6 => i.attribute6,
168 X_ATTRIBUTE7 => i.attribute7,
169 X_ATTRIBUTE8 => i.attribute8,
170 X_ATTRIBUTE9 => i.attribute9,
171 X_ATTRIBUTE10 => i.attribute10,
172 X_ATTRIBUTE11 => i.attribute11,
173 X_ATTRIBUTE12 => i.attribute12,
174 X_ATTRIBUTE13 => i.attribute13,
175 X_ATTRIBUTE14 => i.attribute14,
176 X_ATTRIBUTE15 => i.attribute15,
177 X_ATTRIBUTE16 => i.attribute16,
178 X_ATTRIBUTE17 => i.attribute17,
179 X_ATTRIBUTE18 => i.attribute18,
180 X_ATTRIBUTE19 => i.attribute19,
181 X_ATTRIBUTE20 => i.attribute20,
182 X_WAITLIST_MANUAL_IND => i.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
183 -- Added WLST_PRIORITY_WEIGHT_NUM and WLST_PRIORITY_WEIGHT_NUM as part of Enh. Bug# 3052426 - ptandon
184 X_WLST_PRIORITY_WEIGHT_NUM => i.wlst_priority_weight_num,
185 X_WLST_PREFERENCE_WEIGHT_NUM => i.wlst_preference_weight_num,
186 X_CORE_INDICATOR_CODE => i.core_indicator_code
187 );
188 -- Enrolled:
189 OPEN c_find_person_no;
190 FETCH c_find_person_no INTO v_person_number;
191 CLOSE c_find_person_no;
192
193 Fnd_Message.Set_Name ('IGS','IGS_EN_STDNT_ENRD');
194 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||': '|| v_person_number);
195
196 /*** Reducing actual waitlist by 1 used for processing only - table updation done by en_sua TBH***/
197 p_waitlist_actual := p_waitlist_actual - 1;
198
199 /** Increasing actual enrolment by 1 used for processing only - table updation done by en_sua TBH***/
200 p_enrollment_actual := p_enrollment_actual + 1;
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 p_enrolled_yn := 'N' ;
205 -- Log the exception raised into the log file
206 l_exp_err := fnd_message.get();
207 IF l_exp_err is NOT NULL THEN
208 fnd_file.put_line (fnd_file.LOG, l_exp_err);
209 ELSE
210 fnd_file.put_line (fnd_file.LOG, SQLERRM);
211 END IF;
212
213
214 p_message_name := 'IGS_EN_STDNT_NOT_ENRD' ;
215 -- to set message that enrolment failed --
216 Fnd_Message.Set_Name ('IGS','IGS_EN_STDNT_NOT_ENRD');
217 fnd_file.put_line (fnd_file.LOG, ' '||fnd_message.get_string ('IGS','IGS_EN_STDNT_NOT_ENRD') ||' '||
218 TO_CHAR(i.person_id));
219 -- to set message that enrolment failed --
220 RETURN;
221
222 END;
223 END LOOP;
224
225
226 p_enrolled_yn := 'Y' ;
227
228 -- some commented code were here and removed as per bug#2413811
229 END Enroll_Persons;
230
231
232 /** Procedure to validate the unit -- All existing validations **/
233 FUNCTION validate_unit
234 (p_unit_cd IN igs_ps_unit_ofr_opt.unit_cd%TYPE,
235 p_version_number IN igs_ps_unit_ofr_opt.version_number%TYPE,
236 p_cal_type IN igs_ps_unit_ofr_opt.cal_type%TYPE,
237 p_ci_sequence_number IN igs_ps_unit_ofr_opt.ci_sequence_number%TYPE,
238 p_location_cd IN igs_ps_unit_ofr_opt.location_cd%TYPE,
239 p_person_id IN igs_en_su_attempt.person_id%TYPE,
240 p_unit_class IN igs_ps_unit_ofr_opt.unit_class%TYPE,
241 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
242 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE,
243 p_deny_warn OUT NOCOPY VARCHAR2,
244 p_course_cd IN igs_en_su_attempt.course_cd%TYPE)
245 RETURN BOOLEAN AS
246 /******************************************************************
247 Created By :Syam
248 Date Created By :
249 Purpose :Validation for Unit- Existing Validations
250 Known limitations,
251 enhancements,
252 remarks :
253
254 Change History
255 Who When What
256 sarakshi 24-Feb-2003 Enh#2797116,modified cursor igs_ps_ofr_opt_cur to include delete_flag in
257 the where clause
258 ayedubat 30-MAY-2002 Added a new parameter,p_message_name to the Function:Enrp_Get_Rec_Window
259 call and dsiplaying the returning message for the bug fix:2337161.
260 kkillams 28-04-2003 modified igs_en_su_attempt_cur cursor where clause and impacted object,
261 due to change in Igs_En_Val_Sua.enrp_val_sua_dupl function signature
262 w.r.t. bug number 2829262
263 kkillams 19-June-03 Modified the validate_unit to add additional parameter p_course_cd, as existing cursor may fetch more than one program
264 and also modified the cursor igs_en_su_attempt_cur for bug 2937182
265 ******************************************************************/
266
267 CURSOR igs_en_su_attempt_cur(cp_person_id igs_en_su_attempt.person_id%TYPE,
268 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
269 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)
270 IS
271 SELECT unit_attempt_status
272 FROM IGS_EN_SU_ATTEMPT
273 WHERE person_id = cp_person_id
274 AND course_cd = cp_course_cd
275 AND uoo_id = cp_uoo_id;
276
277
278 CURSOR igs_en_stdnt_ps_att_cur (cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
279 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE)
280 IS
281 SELECT version_number,
282 attendance_mode,
283 attendance_type,
284 course_attempt_status
285 FROM igs_en_stdnt_ps_att
286 WHERE course_cd = cp_course_cd
287 AND person_id = cp_person_id;
288
289 CURSOR igs_ps_ofr_opt_cur (cp_course_cd IGS_PS_OFR_OPT.course_cd%TYPE,
290 cp_version_number IGS_PS_OFR_OPT.version_number%TYPE,
291 cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
292 cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
293 cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
294 cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE)
295 IS
296 SELECT location_cd,
297 coo_id
298 FROM IGS_PS_OFR_OPT
299 WHERE course_cd = cp_course_cd
300 AND version_number = cp_version_number
301 AND cal_type = cp_cal_type
302 AND location_cd = cp_location_cd
303 AND attendance_mode = cp_attendance_mode
304 AND attendance_type = cp_attendance_type
305 AND delete_flag = 'N';
306
307 igs_ps_ofr_opt_cur_rec igs_ps_ofr_opt_cur%ROWTYPE;
308
309 v_duplicate_course_cd igs_en_su_attempt.course_cd%TYPE;
310
311 BEGIN
312
313 FOR igs_en_su_attempt_cur_rec IN igs_en_su_attempt_cur(p_person_id,p_course_cd,p_uoo_id) LOOP
314
315 FOR igs_en_stdnt_ps_att_cur_rec IN igs_en_stdnt_ps_att_cur(p_course_cd,
316 p_person_id) LOOP
317
318 IF NOT Igs_En_Val_Sua.enrp_val_sua_advstnd(p_person_id,
319 p_course_cd,
320 igs_en_stdnt_ps_att_cur_rec.version_number,
321 p_unit_cd,
322 p_version_number,
323 p_message_name ,
324 'N' ) THEN
325 p_deny_warn := 'DENY';
326 RETURN FALSE;
327 END IF;
328 IF NOT Igs_En_Val_Sua.resp_val_sua_cnfrm(p_person_id,
329 p_course_cd,
330 p_unit_cd,
331 p_version_number,
332 p_cal_type,
333 p_ci_sequence_number,
334 p_message_name ,
335 'N' ) THEN
336 p_deny_warn := 'DENY';
337 RETURN FALSE;
338 END IF;
339 IF NOT Igs_En_Gen_008.enrp_get_var_window(p_cal_type,
340 p_ci_sequence_number,
341 SYSDATE,
342 p_uoo_id) THEN
343 p_message_name := 'IGS_EN_CANT_UPD_OUTS_ENRL';
344 p_deny_warn := 'DENY';
345 RETURN FALSE;
346 END IF;
347 IF NOT Igs_En_Gen_004.enrp_get_rec_window(p_cal_type,
348 p_ci_sequence_number,
349 SYSDATE,
350 p_uoo_id,
351 p_message_name) THEN
352 p_message_name := p_message_name;
353 p_deny_warn := 'DENY';
354 RETURN FALSE;
355 END IF;
356 IF NOT Igs_En_Val_Sua.enrp_val_sua_intrmt(p_person_id,
357 p_course_cd,
358 p_cal_type,
359 p_ci_sequence_number,
360 p_message_name) THEN
361 p_deny_warn := 'DENY';
362 RETURN FALSE;
363 END IF;
364 OPEN igs_ps_ofr_opt_cur(p_course_cd,
365 igs_en_stdnt_ps_att_cur_rec.version_number,
366 p_cal_type,
367 p_location_cd,
368 igs_en_stdnt_ps_att_cur_rec.attendance_mode,
369 igs_en_stdnt_ps_att_cur_rec.attendance_type);
370 LOOP
371 FETCH igs_ps_ofr_opt_cur INTO igs_ps_ofr_opt_cur_rec;
372 IF igs_ps_ofr_opt_cur%NOTFOUND THEN
373 CLOSE igs_ps_ofr_opt_cur;
374 EXIT;
375 END IF;
376
377 IF NOT Igs_En_Val_Sua.enrp_val_coo_loc(igs_ps_ofr_opt_cur_rec.coo_id,
378 igs_ps_ofr_opt_cur_rec.location_cd,
379 p_message_name) THEN
380
381 p_deny_warn := 'WARN';
382 fnd_message.set_name('IGS',p_message_name);
383 fnd_file.put_line (fnd_file.LOG, ' '||TO_CHAR(igs_ps_ofr_opt_cur_rec.coo_id) || ' ' ||
384 igs_ps_ofr_opt_cur_rec.location_cd || ' ' ||
385 fnd_message.get_string ('IGS',p_message_name));
386
387 /**** Not Returning false as it is just a warning ***/
388 END IF;
389
390
391 IF NOT Igs_En_Val_Sua.enrp_val_coo_mode(igs_ps_ofr_opt_cur_rec.coo_id,
392 p_unit_class,
393 p_message_name) THEN
394 p_deny_warn := 'WARN';
395 fnd_message.set_name('IGS',p_message_name);
396 fnd_file.put_line (fnd_file.LOG, ' '||TO_CHAR(igs_ps_ofr_opt_cur_rec.coo_id) || ' ' ||
397 p_unit_class || ' ' ||
398 fnd_message.get_string ('IGS',p_message_name));
399
400 /**** Not Returning false as it is just a warning ***/
401
402
403 END IF;
404
405 END LOOP;
406
407 IF NOT Igs_En_Val_Sua.enrp_val_sua_dupl(p_person_id,
408 p_course_cd,
409 p_unit_cd,
410 p_version_number,
411 p_cal_type,
412 p_ci_sequence_number,
413 IGS_EN_SU_ATTEMPT_cur_rec.unit_attempt_status,
414 v_duplicate_course_cd,
415 p_message_name,
416 p_uoo_id) THEN
417 p_deny_warn := 'DENY';
418 RETURN FALSE;
419 END IF;
420
421 IF NOT Igs_En_Val_Sua.enrp_val_sua_excld(p_person_id,
422 p_course_cd,
423 p_unit_cd,
424 p_cal_type,
425 p_ci_sequence_number,
426 p_message_name) THEN
427 p_deny_warn := 'DENY';
428 RETURN FALSE;
429 END IF;
430
431
432 END LOOP;
433 END LOOP;
434 p_deny_warn := NULL;
435 RETURN TRUE;
436 END validate_unit;
437
438 /* Function to validate programs-New Validations - */
439
440 FUNCTION validate_prog (p_person_id igs_en_su_attempt.person_id%TYPE,
441 p_cal_type igs_ca_inst.cal_type%TYPE, --load calendar
442 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, --load calendar
443 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
444 p_course_cd igs_en_su_attempt.course_cd%TYPE,
445 p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
446 p_message_name OUT NOCOPY VARCHAR2,
447 p_deny_warn OUT NOCOPY VARCHAR2)
448 RETURN BOOLEAN AS
449
450 /******************************************************************
451 Created By :Syam
452 Date Created By :
453 Purpose :Validation for Program- New Validations
454 Known limitations,
455 enhancements,
456 remarks :
457
458 Change History
459 Who When What
460 ayedubat 07-JUN-2002 The function call,Igs_En_Gen_015.get_academic_cal is replaced with
461 Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd to get the academic calendar of the given
462 load calendar rather than current academic calendar for the bug fix: 2381603
463
464 prraj 08-Apr-2002
465
466 ******************************************************************/
467 lv_person_type igs_pe_person_types.person_type_code%TYPE;
468
469 CURSOR c_version_number
470 IS
471 SELECT version_number
472 FROM igs_en_stdnt_ps_att
473 WHERE course_cd = p_course_cd
474 AND person_id = p_person_id;
475 -- Cursor to get the Person Type Code corresponding to the System Type
476 -- Added as per the bug# 2364461.
477 CURSOR cur_per_typ IS
478 SELECT person_type_code
479 FROM igs_pe_person_types
480 WHERE system_type = 'OTHER';
481 l_cur_per_typ cur_per_typ%ROWTYPE;
482
483 lv_version_number igs_en_stdnt_ps_att.version_number%TYPE;
484 lv_message VARCHAR2(2000);
485 lv_deny_warn VARCHAR2(20);
486 l_commencement_type igs_en_cat_prc_dtl.S_STUDENT_COMM_TYPE%TYPE;
487 l_enrollment_category igs_en_cat_prc_dtl.enrolment_cat%TYPE;
488 l_enrol_cal_type igs_ca_type.cal_type%TYPE;
489 l_enrol_sequence_number igs_ca_inst_all.sequence_number%TYPE;
490 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
491 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
492 l_acad_start_dt IGS_CA_INST.start_dt%TYPE;
493 l_acad_end_dt IGS_CA_INST.end_dt%TYPE;
494 l_alternate_code IGS_CA_INST.alternate_code%TYPE;
495 l_dummy VARCHAR2(200);
496
497 BEGIN
498 lv_message := NULL;
499
500 /*** To get person type ***/
501 OPEN cur_per_typ; --Added as per bug# 2364461
502 FETCH cur_per_typ into l_cur_per_typ; --Added as per bug# 2364461
503 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_course_cd),l_cur_per_typ.person_type_code);
504 CLOSE cur_per_typ; --Added as per bug# 2364461
505 /*** To get person type ***/
506
507 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
508 p_cal_type => p_cal_type,
509 p_ci_sequence_number => p_ci_sequence_number,
510 p_acad_cal_type => l_acad_cal_type,
511 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
512 p_acad_ci_start_dt => l_acad_start_dt,
513 p_acad_ci_end_dt => l_acad_end_dt,
514 p_message_name => lv_message );
515
516 IF lv_message IS NOT NULL THEN
517 p_message_name := lv_message;
518 p_deny_warn := 'DENY';
519 RETURN FALSE;
520 END IF;
521
522 l_enrollment_category := Igs_En_Gen_003.enrp_get_enr_cat(
523 p_person_id => p_person_id,
524 p_course_cd => p_course_cd,
525 p_cal_type => l_acad_cal_type,
526 p_ci_sequence_number => l_acad_ci_sequence_number,
527 p_session_enrolment_cat =>NULL,
528 p_enrol_cal_type => l_enrol_cal_type ,
529 p_enrol_ci_sequence_number => l_enrol_sequence_number,
530 p_commencement_type => l_commencement_type,
531 p_enr_categories => l_dummy );
532
533
534
535 /*** To get course version ***/
536 OPEN c_version_number;
537 FETCH c_version_number INTO lv_version_number;
538 CLOSE c_version_number;
539 /*** To get course version ***/
540
541 IF Igs_En_Elgbl_Program.eval_program_steps( p_person_id =>p_person_id,
542 p_person_type =>lv_person_type,
543 p_load_calendar_type=> p_cal_type,
544 p_load_cal_sequence_number =>p_ci_sequence_number,
545 p_uoo_id =>p_uoo_id,
546 p_program_cd =>p_course_cd,
547 p_program_version =>lv_version_number,
548 p_enrollment_category => l_enrollment_category,
549 p_comm_type => l_commencement_type,
550 p_method_type =>p_enr_method_type,
551 p_message =>lv_message,
552 p_deny_warn =>lv_deny_warn,
553 p_calling_obj => 'JOB' ) THEN
554
555 p_message_name := lv_message;
556 p_deny_warn := lv_deny_warn;
557 RETURN TRUE;
558 ELSE
559
560 p_message_name := lv_message;
561 p_deny_warn := lv_deny_warn;
562 RETURN FALSE;
563
564 END IF;
565
566 END validate_prog;
567
568 FUNCTION validate_unit_steps (p_person_id igs_en_su_attempt.person_id%TYPE,
569 p_cal_type igs_ca_inst.cal_type%TYPE,
570 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
571 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
572 p_course_cd igs_en_su_attempt.course_cd%TYPE,
573 p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
574 p_message_name OUT NOCOPY VARCHAR2,
575 p_deny_warn OUT NOCOPY VARCHAR2,
576 p_calling_obj IN VARCHAR2
577 )
578 RETURN BOOLEAN AS
579 /* HISTORY
580 WHO WHEN WHAT
581 stutta 20-Nov-2003 Replaced a cursor which returns program version with a terms api function call
582 to do the same. Done as part of term records build. Bug 2829263
583 ayedubat 07-JUN-2002 The function call,Igs_En_Gen_015.get_academic_cal is replaced with
584 Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd to get the academic calendar of the given
585 load calendar rather than current academic calendar for the bug fix: 2381603
586
587 */
588 lv_person_type igs_pe_person_types.person_type_code%TYPE;
589
590 -- Cursor to get the Person Type Code corresponding to the System Type
591 -- Added as per the bug# 2364461.
592 CURSOR cur_per_typ IS
593 SELECT person_type_code
594 FROM igs_pe_person_types
595 WHERE system_type = 'OTHER';
596 l_cur_per_typ cur_per_typ%ROWTYPE;
597
598 lv_version_number igs_en_stdnt_ps_att.version_number%TYPE;
599 lv_message VARCHAR2(2000);
600 lv_deny_warn VARCHAR2(20);
601 l_commencement_type igs_en_cat_prc_dtl.S_STUDENT_COMM_TYPE%TYPE;
602 l_enrollment_category igs_en_cat_prc_dtl.enrolment_cat%TYPE;
603 l_enrol_cal_type igs_ca_inst.cal_type%TYPE;
604 l_enrol_sequence_number igs_ca_inst.sequence_number%TYPE;
605
606 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
607 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
608 l_acad_start_dt IGS_CA_INST.start_dt%TYPE;
609 l_acad_end_dt IGS_CA_INST.end_dt%TYPE;
610 l_alternate_code IGS_CA_INST.alternate_code%TYPE;
611 l_dummy VARCHAR2(200);
612
613
614 BEGIN
615
616
617 /*** To get person type ***/
618 OPEN cur_per_typ; --Added as per bug# 2364461
619 FETCH cur_per_typ into l_cur_per_typ; --Added as per bug# 2364461
620 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_course_cd),l_cur_per_typ.person_type_code);
621 CLOSE cur_per_typ; --Added as per bug# 2364461
622 /*** To get person type ***/
623
624 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
625 p_cal_type => p_cal_type,
626 p_ci_sequence_number => p_ci_sequence_number,
627 p_acad_cal_type => l_acad_cal_type,
628 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
629 p_acad_ci_start_dt => l_acad_start_dt,
630 p_acad_ci_end_dt => l_acad_end_dt,
631 p_message_name => lv_message );
632
633 IF lv_message IS NOT NULL THEN
634 p_message_name := lv_message;
635 p_deny_warn := 'DENY';
636 RETURN FALSE;
637 END IF;
638
639 l_enrollment_category := Igs_En_Gen_003.enrp_get_enr_cat(
640 p_person_id => p_person_id,
641 p_course_cd => p_course_cd,
642 p_cal_type => l_acad_cal_type,
643 p_ci_sequence_number => l_acad_ci_sequence_number,
644 p_session_enrolment_cat =>NULL,
645 p_enrol_cal_type => l_enrol_cal_type ,
646 p_enrol_ci_sequence_number => l_enrol_sequence_number,
647 p_commencement_type => l_commencement_type,
648 p_enr_categories => l_dummy );
649
650 /*** To get course version ***/
651 lv_version_number := igs_en_spa_terms_api.get_spat_program_version(
652 p_person_id => p_person_id,
653 p_program_cd => p_course_cd,
654 p_term_cal_type => p_cal_type,
655 p_term_sequence_number => p_ci_sequence_number);
656
657 IF Igs_En_Elgbl_Unit.eval_unit_steps(p_person_id => p_person_id ,
658 p_person_type => lv_person_type,
659 p_load_cal_type => p_cal_type,
660 p_load_sequence_number => p_ci_sequence_number,
661 p_uoo_id => p_uoo_id,
662 p_course_cd => p_course_cd,
663 p_course_version => lv_version_number,
664 p_enrollment_category => l_enrollment_category,
665 p_enr_method_type => p_enr_method_type,
666 p_comm_type => l_commencement_type,
667 p_message => lv_message,
668 p_deny_warn => lv_deny_warn,
669 p_calling_obj => p_calling_obj) THEN
670
671
672 p_message_name := lv_message;
673 p_deny_warn := lv_deny_warn;
674 RETURN TRUE;
675 ELSE
676
677 p_message_name := lv_message;
678 p_deny_warn := lv_deny_warn;
679 -- if the message name is not null only then should the transaction be rolled back
680 -- otherwise further processing should continue hence returning true if is null
681 -- and false otherwise.
682 IF p_message_name IS NOT NULL THEN
683 RETURN FALSE;
684 ELSE
685 RETURN TRUE;
686 END IF;
687
688 END IF;
689
690
691 END validate_unit_steps;
692
693 /**** Main function for validating prg , unit , unit steps ***/
694
695 FUNCTION finalize_unit (p_person_id igs_en_su_attempt.person_id%TYPE,
696 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
697 p_called_from_wlst VARCHAR2,
698 p_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
699 p_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
700 p_cal_type igs_ca_inst.cal_type%TYPE,
701 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
702 p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
703 p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
704 p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
705 p_course_cd igs_en_su_attempt.course_cd%TYPE,
706 p_rsv_seat_ext_id igs_en_su_attempt.rsv_seat_ext_id%TYPE,
707 p_message_name OUT NOCOPY VARCHAR2)
708
709
710 RETURN BOOLEAN AS
711 /******************************************************************
712 Created By :Syam
713 Date Created By :
714 Purpose :Finalizing the Unit Contains Existin + New Unit and Program Validations
715 Known limitations,
716 enhancements,
717 remarks :
718 Change History
719 Who When What
720 sommukhe 27-JUL-2005 Bug#4344483,Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
721 abort_flag.
722 sarakshi 22-Sep-2003 Enh#3052452, Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
723 sup_uoo_id,relation_type,default_enroll_flag.
724 vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified the call to igs_ps_unit_ofr_opt_pkg.update_row to include new parameter
725 not_multiple_section_flag.
726 kkillams 24-04-2003 Passing NULL/0 depending on the IGS_EN_INCL_WLST_CP profile w.r.t. bug 2889975
727 ptandon 26-06-2003 Modified to display Person Number and Unit Code instead of Person Id and Uoo Id in the log.
728 Modified to display Max CP failure Error Message once if the student fails the same. Bug# 2841584
729 ******************************************************************/
730 lv_validate_unit_message fnd_new_messages.message_name%TYPE;
731 lv_validate_unit_steps_message VARCHAR2(2000);
732 lv_validate_prog_message VARCHAR2(2000);
733 lv_dummy_message fnd_new_messages.message_name%TYPE;
734
735 lv_unit_deny_warn VARCHAR2(100);
736 lv_unit_steps_deny_warn VARCHAR2(100);
737 lv_prog_deny_warn VARCHAR2(100);
738
739 /*** Boolean variables to decide validation state **/
740 lb_validate_unit BOOLEAN := FALSE;
741 lb_validate_unit_steps BOOLEAN := FALSE;
742 lb_validate_prog BOOLEAN := FALSE;
743 ln_message_count NUMBER(4);
744 lv_return_status VARCHAR2(10);
745 l_credit_points NUMBER(10);
746 /*** Boolean variables to decide validation state **/
747
748 /** Cursor for UPDATE dir enrolment /or inq_not_wlst in IGS_PS_UNIT_OFR_OPT **/
749
750 CURSOR c_unit_ofr_opt IS
751 SELECT ROWID,
752 ofr.*
753 FROM igs_ps_unit_ofr_opt ofr
754 WHERE uoo_id = p_uoo_id;
755
756
757 v_unit_ofr_opt_rec c_unit_ofr_opt%ROWTYPE;
758
759 /** Cursor for UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
760
761
762 /** Cursor for UPDATE actual enrolment in igs_ps_rsv_ext **/
763
764 CURSOR c_rsv_ext IS
765 SELECT ROWID,
766 rsv.*
767 FROM igs_ps_rsv_ext rsv
768 WHERE rsv_ext_id = p_rsv_seat_ext_id;
769
770 v_rsv_ext_rec c_rsv_ext%ROWTYPE;
771
772 /** Cursor for UPDATE actual enrolment in igs_ps_rsv_ext **/
773
774 /** Cursor for Delete record from igs_en_su_attempt**/
775
776 CURSOR c_del_sua IS
777 SELECT ROWID
778 FROM igs_en_su_attempt
779 WHERE uoo_id = p_uoo_id
780 AND person_id = p_person_id
781 AND course_cd = p_course_cd;
782
783 v_del_sua_rec c_del_sua%ROWTYPE;
784
785 /** Cursor for Delete record from igs_en_su_attempt**/
786
787 CURSOR c_find_person_no IS
788 SELECT party_number
789 FROM hz_parties
790 WHERE party_id=p_person_id;
791
792 v_person_number igs_pe_person.person_number%TYPE;
793 CURSOR c_incl_org_wlst_cp is
794 SELECT asses_chrg_for_wlst_stud
795 FROM IGS_EN_OR_UNIT_WLST
796 WHERE cal_type = p_cal_type AND
797 closed_flag = 'N' AND
798 org_unit_cd = (SELECT NVL(uoo.owner_org_unit_cd, uv.owner_org_unit_cd)
799 FROM igs_ps_unit_ofr_opt uoo,
800 igs_ps_unit_ver uv
801 WHERE uoo.uoo_id = p_uoo_id AND
802 uv.unit_cd = uoo.unit_cd AND
803 uv.version_number = uoo.version_number);
804 CURSOR c_incl_inst_wlst_cp is
805 SELECT include_waitlist_cp_flag
806 FROM IGS_EN_INST_WL_STPS;
807 l_incl_wlst VARCHAR2(2);
808 BEGIN
809
810 p_message_name := null;
811
812 OPEN c_find_person_no;
813 FETCH c_find_person_no INTO v_person_number;
814 CLOSE c_find_person_no;
815
816 -- Passing 0 or NULL value to the credit points parameter of ss_eval_min_or_max_cp procedure
817 -- depending include waitlist profile.
818 l_credit_points := NULL;
819 OPEN c_incl_org_wlst_cp;
820 FETCH c_incl_org_wlst_cp INTO l_incl_wlst;
821 IF (c_incl_org_wlst_cp%FOUND) THEN
822 IF NVL(l_incl_wlst,'N') = 'Y' THEN
823 l_credit_points := 0;
824 ELSE
825 l_credit_points := NULL;
826 END IF;
827 ELSE
828 OPEN c_incl_inst_wlst_cp;
829 FETCH c_incl_inst_wlst_cp INTO l_incl_wlst;
830 IF c_incl_inst_wlst_cp%FOUND THEN
831 IF (NVL(l_incl_wlst,'N') = 'Y') THEN
832 l_credit_points := 0;
833 ELSE
834 l_credit_points := NULL;
835 END IF;
836 ELSE
837 IF fnd_profile.value('IGS_EN_VAL_WLST') = 'Y' THEN
838 l_credit_points := 0;
839 ELSE
840 l_credit_points := NULL;
841 END IF;
842 END IF;
843 CLOSE c_incl_inst_wlst_cp;
844 END IF;
845 CLOSE c_incl_org_wlst_cp;
846 /****************************************************************************
847 Previously only validate_prog was being called. Since the min cp and max cp
848 validation were commented out NOCOPY in the eval_program_steps, explicitly calling the
849 same from here
850 ****************************************************************************/
851 lv_return_status := NULL;
852 igs_en_enroll_wlst.ss_eval_min_or_max_cp(
853 p_person_id => p_person_id,
854 p_load_cal_type => p_cal_type,
855 p_load_ci_sequence_number => p_ci_sequence_number,
856 p_uoo_id => p_uoo_id,
857 p_program_cd => p_course_cd,
858 p_step_type => 'FMIN_CRDT',
859 p_credit_points => l_credit_points,
860 p_message_name => lv_validate_prog_message,
861 p_deny_warn => lv_prog_deny_warn,
862 p_return_status => lv_return_status,
863 p_enr_method => p_enr_method_type
864 );
865 IF lv_return_status = 'TRUE' THEN
866 lb_validate_prog := TRUE;
867 ELSIF lv_return_status = 'FALSE' AND lv_prog_deny_warn = 'WARN' THEN
868 lb_validate_prog := TRUE;
869 ELSE
870 lb_validate_prog := FALSE;
871 END IF;
872
873 IF p_message_name IS NOT NULL AND lv_validate_prog_message IS NOT NULL THEN
874 p_message_name := p_message_name || ';' || lv_validate_prog_message;
875 ELSIF p_message_name IS NULL AND lv_validate_prog_message IS NOT NULL THEN
876 p_message_name := lv_validate_prog_message;
877 END IF;
878
879 IF lb_validate_prog THEN
880 lv_validate_prog_message := NULL;
881 lv_return_status := NULL;
882 igs_en_enroll_wlst.ss_eval_min_or_max_cp(
883 p_person_id => p_person_id,
884 p_load_cal_type => p_cal_type,
885 p_load_ci_sequence_number => p_ci_sequence_number,
886 p_uoo_id => p_uoo_id,
887 p_program_cd => p_course_cd,
888 p_step_type => 'FMAX_CRDT',
889 p_credit_points => l_credit_points,
890 p_message_name => lv_validate_prog_message,
891 p_deny_warn => lv_prog_deny_warn,
892 p_return_status => lv_return_status,
893 p_enr_method => p_enr_method_type
894
895 );
896
897 IF lv_return_status = 'TRUE' THEN
898 lb_validate_prog := TRUE;
899 ELSIF lv_return_status = 'FALSE' AND lv_prog_deny_warn = 'WARN' THEN
900 lb_validate_prog := TRUE;
901 ELSE
902 lb_validate_prog := FALSE;
903 END IF;
904
905 IF p_message_name IS NOT NULL AND lv_validate_prog_message IS NOT NULL THEN
906 p_message_name := p_message_name || ';' || lv_validate_prog_message;
907 ELSIF p_message_name IS NULL AND lv_validate_prog_message IS NOT NULL THEN
908 p_message_name := lv_validate_prog_message;
909 END IF;
910
911 IF lb_validate_prog THEN
912 /****************************************************************************
913 Previously only validate_prog was being called. Since the min cp and max cp
914 validation were commented out NOCOPY in the eval_program_steps, explicitly calling the
915 same from here
916 ****************************************************************************/
917
918 lv_validate_prog_message := NULL;
919 lv_return_status := NULL;
920 lb_validate_prog := validate_prog (
921 p_person_id => p_person_id,
922 p_cal_type => p_cal_type,
923 p_ci_sequence_number => p_ci_sequence_number,
924 p_uoo_id => p_uoo_id,
925 p_course_cd => p_course_cd,
926 p_enr_method_type => p_enr_method_type,
927 p_message_name => lv_validate_prog_message,
928 p_deny_warn => lv_prog_deny_warn);
929 IF NOT lb_validate_prog AND lv_prog_deny_warn = 'WARN' THEN
930 lb_validate_prog := TRUE;
931 END IF;
932
933 IF INSTR(lv_validate_prog_message,'*') > 0 THEN
934 DECLARE
935 v_temp_message_Str VARCHAR2(2000);
936 BEGIN
937 v_temp_message_Str := NULL;
938 ln_message_count := Igs_En_Enroll_Wlst.get_message_count(lv_validate_prog_message);
939 IF ln_message_count > 0 THEN
940 FOR I IN 1..ln_message_count LOOP
941 lv_dummy_message := Igs_En_Enroll_Wlst.get_message(lv_validate_prog_message,I);
942 IF lv_dummy_message IS NOT NULL THEN
943 IF INSTR(lv_dummy_message,'*') > 0 THEN
944 lv_dummy_message := SUBSTR(lv_dummy_message,1,INSTR(lv_dummy_message,'*')-1) || '_NO_TOKEN' ;
945 END IF;
946
947 IF v_temp_message_Str IS NULL THEN
948 v_temp_message_Str := lv_dummy_message;
949 ELSE
950 v_temp_message_Str := v_temp_message_Str || ';' || lv_dummy_message;
951 END IF;
952 ELSE
953 EXIT;
954 END IF;
955 END LOOP;
956 lv_validate_prog_message := v_temp_message_Str;
957 END IF;
958 END;
959 END IF;
960
961 IF p_message_name IS NOT NULL AND lv_validate_prog_message IS NOT NULL THEN
962 p_message_name := p_message_name || ';' || lv_validate_prog_message;
963 ELSIF p_message_name IS NULL AND lv_validate_prog_message IS NOT NULL THEN
964 p_message_name := lv_validate_prog_message;
965 END IF;
966
967 END IF;
968 END IF;
969
970
971 /*** All program validations to be made here for both Y and N for Call from waitlist***/
972
973 lv_validate_prog_message := p_message_name;
974 IF lv_validate_prog_message IS NOT NULL THEN
975
976 ln_message_count := Igs_En_Enroll_Wlst.get_message_count(lv_validate_prog_message);
977 IF ln_message_count > 0 THEN
978 FOR I IN 1..ln_message_count LOOP
979 lv_dummy_message := Igs_En_Enroll_Wlst.get_message(lv_validate_prog_message,I);
980 IF lv_dummy_message IS NOT NULL THEN
981 fnd_message.set_name('IGS',lv_dummy_message);
982 fnd_file.put_line (fnd_file.LOG, ' '|| p_unit_cd || ' ' || v_person_number || ' ' ||fnd_message.get);
983 ELSE
984 EXIT;
985 END IF;
986 END LOOP;
987 END IF;
988
989 END IF;
990
991 /*** All program validations to be made here ***/
992
993 IF p_called_from_wlst = 'Y' THEN
994 /*** All existing unit level validations takes place here ***/
995 lv_validate_unit_message := null;
996 IF validate_unit (p_unit_cd => p_unit_cd,
997 p_version_number => p_version_number,
998 p_cal_type => p_cal_type,
999 p_ci_sequence_number => p_ci_sequence_number,
1000 p_location_cd =>p_location_cd,
1001 p_person_id =>p_person_id,
1002 p_unit_class =>p_unit_class,
1003 p_uoo_id => p_uoo_id,
1004 p_message_name => lv_validate_unit_message,
1005 p_deny_warn => lv_unit_deny_warn,
1006 p_course_cd => p_course_cd) THEN
1007
1008 /*** To set flag***/
1009 lb_validate_unit := TRUE;
1010 ELSE
1011
1012 /*** To set flag***/
1013 IF lv_unit_deny_warn = 'WARN' THEN
1014 lb_validate_unit := TRUE;
1015 ELSE
1016 lb_validate_unit := FALSE;
1017 END IF;
1018
1019 END IF;
1020
1021 ln_message_count := Igs_En_Enroll_Wlst.get_message_count(lv_validate_unit_message);
1022 IF ln_message_count > 0 THEN
1023 FOR I IN 1..ln_message_count LOOP
1024 lv_dummy_message := Igs_En_Enroll_Wlst.get_message(lv_validate_unit_message,I);
1025 IF lv_dummy_message IS NOT NULL THEN
1026 fnd_message.set_name('IGS',lv_dummy_message);
1027 fnd_file.put_line (fnd_file.LOG, ' '|| p_unit_cd || ' ' || v_person_number || ' ' ||fnd_message.get);
1028 ELSE
1029 EXIT;
1030 END IF;
1031 END LOOP;
1032 END IF;
1033
1034 IF p_message_name IS NULL AND lv_validate_unit_message IS NOT NULL THEN
1035 p_message_name := lv_validate_unit_message;
1036 ELSIF p_message_name IS NOT NULL AND lv_validate_unit_message IS NOT NULL THEN
1037 p_message_name := p_message_name || ';' || lv_validate_unit_message;
1038 END IF;
1039
1040
1041
1042 /**** Call new unit step validations -- which returns either WARN DENY***/
1043 --added a new parameter value JOB_FROM_WAITLIST for bug 4580204
1044 IF validate_unit_steps (p_person_id => p_person_id,
1045 p_cal_type => p_cal_type,
1046 p_ci_sequence_number => p_ci_sequence_number,
1047 p_uoo_id => p_uoo_id,
1048 p_course_cd => p_course_cd,
1049 p_enr_method_type => p_enr_method_type,
1050 p_message_name => lv_validate_unit_steps_message,
1051 p_deny_warn => lv_unit_steps_deny_warn,
1052 p_calling_obj => 'JOB_FROM_WAITLIST') THEN
1053
1054
1055 /*** To set flag***/
1056 lb_validate_unit_steps := TRUE;
1057 ELSE
1058
1059 /*** To set flag***/
1060 IF lv_unit_steps_deny_warn = 'WARN' THEN
1061 lb_validate_unit_steps := TRUE;
1062 ELSE
1063 lb_validate_unit_steps := FALSE;
1064 END IF;
1065 END IF;
1066
1067
1068 DECLARE
1069 v_temp_message_Str VARCHAR2(2000);
1070 BEGIN
1071
1072 v_temp_message_Str := NULL;
1073 ln_message_count := Igs_En_Enroll_Wlst.get_message_count(lv_validate_unit_steps_message);
1074 IF ln_message_count > 0 THEN
1075 FOR I IN 1..ln_message_count LOOP
1076 lv_dummy_message := Igs_En_Enroll_Wlst.get_message(lv_validate_unit_steps_message,I);
1077 IF lv_dummy_message IS NOT NULL THEN
1078 IF INSTR(lv_dummy_message,'*') > 0 THEN
1079 lv_dummy_message := SUBSTR(lv_dummy_message,1,INSTR(lv_dummy_message,'*')-1) || '_NO_TOKEN' ;
1080 END IF;
1081 fnd_message.set_name('IGS',lv_dummy_message);
1082 fnd_file.put_line (fnd_file.LOG, ' '|| p_unit_cd || ' ' || v_person_number || ' ' ||fnd_message.get);
1083 IF v_temp_message_Str IS NULL THEN
1084 v_temp_message_Str := lv_dummy_message;
1085 ELSE
1086 v_temp_message_Str := v_temp_message_Str || ';' || lv_dummy_message;
1087 END IF;
1088 ELSE
1089 EXIT;
1090 END IF;
1091 END LOOP;
1092 lv_validate_unit_steps_message := v_temp_message_Str;
1093 END IF;
1094
1095 END;
1096
1097 IF p_message_name IS NULL AND lv_validate_unit_steps_message IS NOT NULL THEN
1098 p_message_name := lv_validate_unit_steps_message;
1099 ELSIF p_message_name IS NOT NULL AND lv_validate_unit_steps_message IS NOT NULL THEN
1100 p_message_name := p_message_name || ';' || lv_validate_unit_steps_message;
1101 END IF;
1102
1103
1104 END IF;
1105
1106
1107 IF (p_called_from_wlst = 'N') THEN
1108 IF (lb_validate_prog) THEN
1109
1110 /** UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
1111
1112 OPEN c_unit_ofr_opt;
1113 FETCH c_unit_ofr_opt INTO v_unit_ofr_opt_rec;
1114 CLOSE c_unit_ofr_opt;
1115
1116 BEGIN
1117 -- Added auditable_ind and audit_permission_ind parameters as part of Bug# 2636716
1118 igs_ps_unit_ofr_opt_pkg.update_row
1119 (x_rowid => v_unit_ofr_opt_rec.ROWID,
1120 x_unit_cd => v_unit_ofr_opt_rec.unit_cd,
1121 x_version_number => v_unit_ofr_opt_rec.version_number ,
1122 x_cal_type => v_unit_ofr_opt_rec.cal_type,
1123 x_ci_sequence_number => v_unit_ofr_opt_rec.ci_sequence_number,
1124 x_location_cd => v_unit_ofr_opt_rec.location_cd,
1125 x_unit_class => v_unit_ofr_opt_rec.unit_class,
1126 x_uoo_id => v_unit_ofr_opt_rec.uoo_id,
1127 x_ivrs_available_ind => v_unit_ofr_opt_rec.ivrs_available_ind,
1128 x_call_number => v_unit_ofr_opt_rec.call_number,
1129 x_unit_section_status => v_unit_ofr_opt_rec.unit_section_status,
1130 x_unit_section_start_date => v_unit_ofr_opt_rec.unit_section_start_date,
1131 x_unit_section_end_date => v_unit_ofr_opt_rec.unit_section_end_date,
1132 x_enrollment_actual => v_unit_ofr_opt_rec.enrollment_actual,
1133 x_waitlist_actual => v_unit_ofr_opt_rec.waitlist_actual,
1134 x_offered_ind => v_unit_ofr_opt_rec.offered_ind,
1135 x_state_financial_aid => v_unit_ofr_opt_rec.state_financial_aid,
1136 x_grading_schema_prcdnce_ind => v_unit_ofr_opt_rec.grading_schema_prcdnce_ind,
1137 x_federal_financial_aid => v_unit_ofr_opt_rec.federal_financial_aid,
1138 x_unit_quota => v_unit_ofr_opt_rec.unit_quota,
1139 x_unit_quota_reserved_places => v_unit_ofr_opt_rec.unit_quota_reserved_places,
1140 x_institutional_financial_aid => v_unit_ofr_opt_rec.institutional_financial_aid,
1141 x_unit_contact => v_unit_ofr_opt_rec.unit_contact,
1142 x_grading_schema_cd => v_unit_ofr_opt_rec.grading_schema_cd,
1143 x_gs_version_number => v_unit_ofr_opt_rec.gs_version_number,
1144 x_owner_org_unit_cd => v_unit_ofr_opt_rec.owner_org_unit_cd,
1145 x_attendance_required_ind => v_unit_ofr_opt_rec.attendance_required_ind,
1146 x_reserved_seating_allowed => v_unit_ofr_opt_rec.reserved_seating_allowed,
1147 x_special_permission_ind => v_unit_ofr_opt_rec.special_permission_ind,
1148 x_ss_display_ind => v_unit_ofr_opt_rec.ss_display_ind,
1149 x_mode => 'R',
1150 x_ss_enrol_ind => v_unit_ofr_opt_rec.ss_enrol_ind,
1151 x_dir_enrollment => NVL(v_unit_ofr_opt_rec.dir_enrollment,0) + 1, -- Only Column Updated to add 1
1152 x_enr_from_wlst => v_unit_ofr_opt_rec.enr_from_wlst,
1153 x_inq_not_wlst => v_unit_ofr_opt_rec.inq_not_wlst,
1154 x_rev_account_cd => v_unit_ofr_opt_rec.rev_account_cd,
1155 x_anon_unit_grading_ind => v_unit_ofr_opt_rec.anon_unit_grading_ind ,
1156 x_anon_assess_grading_ind => v_unit_ofr_opt_rec.anon_assess_grading_ind,
1157 x_non_std_usec_ind => v_unit_ofr_opt_rec.non_std_usec_ind,
1158 x_auditable_ind => v_unit_ofr_opt_rec.auditable_ind,
1159 x_audit_permission_ind => v_unit_ofr_opt_rec.audit_permission_ind,
1160 x_not_multiple_section_flag => v_unit_ofr_opt_rec.not_multiple_section_flag,
1161 x_sup_uoo_id => v_unit_ofr_opt_rec.sup_uoo_id,
1162 x_relation_type => v_unit_ofr_opt_rec.relation_type,
1163 x_default_enroll_flag => v_unit_ofr_opt_rec.default_enroll_flag,
1164 x_abort_flag => v_unit_ofr_opt_rec.abort_flag
1165 );
1166
1167 END;
1168
1169 /** UPDATE dir enrolment in IGS_PS_UNIT_OFR_OPT **/
1170
1171 RETURN TRUE;
1172 END IF;
1173
1174 IF (NOT lb_validate_prog) AND (lv_prog_deny_warn = 'DENY') THEN
1175
1176 /*** UPDATE for IGS_PS_RSV_EXT -For actual seat enrolled - 1 **/
1177
1178 OPEN c_rsv_ext;
1179 FETCH c_rsv_ext INTO v_rsv_ext_rec;
1180 CLOSE c_rsv_ext;
1181
1182 -- Only x_actual_seat_enrolled Column Updated to substract 1
1183 BEGIN
1184 igs_ps_rsv_ext_pkg.update_row(
1185 x_rowid =>v_rsv_ext_rec.ROWID,
1186 x_rsv_ext_id=>v_rsv_ext_rec.rsv_ext_id,
1187 x_uoo_id=>v_rsv_ext_rec.uoo_id,
1188 x_priority_id=>v_rsv_ext_rec.priority_id,
1189 x_preference_id=>v_rsv_ext_rec.preference_id,
1190 x_rsv_level=>v_rsv_ext_rec.rsv_level,
1191 x_actual_seat_enrolled=>NVL(v_rsv_ext_rec.actual_seat_enrolled,0) - 1,
1192 x_mode => 'R');
1193 END;
1194 /*** UPDATE for IGS_PS_RSV_EXT -For actual seat enrolled - 1 **/
1195
1196
1197 /**** DELETE from igs_en_su_attempt for uoo_id***/
1198
1199 OPEN c_del_sua ;
1200 FETCH c_del_sua INTO v_del_sua_rec;
1201 CLOSE c_del_sua;
1202
1203 BEGIN
1204 Igs_En_Su_Attempt_Pkg.delete_row(v_del_sua_rec.ROWID);
1205 END;
1206
1207 /**** DELETE from igs_en_su_attempt for uoo_id***/
1208
1209 /*** update igs_ps_unit_ofr inq_not_wlst by 1 **/
1210
1211 OPEN c_unit_ofr_opt;
1212 FETCH c_unit_ofr_opt INTO v_unit_ofr_opt_rec;
1213 CLOSE c_unit_ofr_opt;
1214
1215 BEGIN
1216 -- Added auditable_ind and audit_permission_ind parameters as part of Bug# 2636716
1217 igs_ps_unit_ofr_opt_pkg.update_row
1218 (x_rowid => v_unit_ofr_opt_rec.ROWID,
1219 x_unit_cd => v_unit_ofr_opt_rec.unit_cd,
1220 x_version_number => v_unit_ofr_opt_rec.version_number ,
1221 x_cal_type => v_unit_ofr_opt_rec.cal_type,
1222 x_ci_sequence_number => v_unit_ofr_opt_rec.ci_sequence_number,
1223 x_location_cd => v_unit_ofr_opt_rec.location_cd,
1224 x_unit_class => v_unit_ofr_opt_rec.unit_class,
1225 x_uoo_id => v_unit_ofr_opt_rec.uoo_id,
1226 x_ivrs_available_ind => v_unit_ofr_opt_rec.ivrs_available_ind,
1227 x_call_number => v_unit_ofr_opt_rec.call_number,
1228 x_unit_section_status => v_unit_ofr_opt_rec.unit_section_status,
1229 x_unit_section_start_date => v_unit_ofr_opt_rec.unit_section_start_date,
1230 x_unit_section_end_date => v_unit_ofr_opt_rec.unit_section_end_date,
1231 x_enrollment_actual => v_unit_ofr_opt_rec.enrollment_actual,
1232 x_waitlist_actual => v_unit_ofr_opt_rec.waitlist_actual,
1233 x_offered_ind => v_unit_ofr_opt_rec.offered_ind,
1234 x_state_financial_aid => v_unit_ofr_opt_rec.state_financial_aid,
1235 x_grading_schema_prcdnce_ind => v_unit_ofr_opt_rec.grading_schema_prcdnce_ind,
1236 x_federal_financial_aid => v_unit_ofr_opt_rec.federal_financial_aid,
1237 x_unit_quota => v_unit_ofr_opt_rec.unit_quota,
1238 x_unit_quota_reserved_places => v_unit_ofr_opt_rec.unit_quota_reserved_places,
1239 x_institutional_financial_aid => v_unit_ofr_opt_rec.institutional_financial_aid,
1240 x_unit_contact => v_unit_ofr_opt_rec.unit_contact,
1241 x_grading_schema_cd => v_unit_ofr_opt_rec.grading_schema_cd,
1242 x_gs_version_number => v_unit_ofr_opt_rec.gs_version_number,
1243 x_owner_org_unit_cd => v_unit_ofr_opt_rec.owner_org_unit_cd,
1244 x_attendance_required_ind => v_unit_ofr_opt_rec.attendance_required_ind,
1245 x_reserved_seating_allowed => v_unit_ofr_opt_rec.reserved_seating_allowed,
1246 x_special_permission_ind => v_unit_ofr_opt_rec.special_permission_ind,
1247 x_ss_display_ind => v_unit_ofr_opt_rec.ss_display_ind,
1248 x_mode => 'R',
1249 x_ss_enrol_ind => v_unit_ofr_opt_rec.ss_enrol_ind,
1250 x_dir_enrollment => v_unit_ofr_opt_rec.dir_enrollment,
1251 x_enr_from_wlst => v_unit_ofr_opt_rec.enr_from_wlst,
1252 x_inq_not_wlst => NVL(v_unit_ofr_opt_rec.inq_not_wlst,0) + 1,
1253 x_rev_account_cd => v_unit_ofr_opt_rec.rev_account_cd ,
1254 x_anon_unit_grading_ind => v_unit_ofr_opt_rec.anon_unit_grading_ind ,
1255 x_anon_assess_grading_ind => v_unit_ofr_opt_rec.anon_assess_grading_ind,
1256 x_non_std_usec_ind => v_unit_ofr_opt_rec.non_std_usec_ind ,
1257 x_auditable_ind => v_unit_ofr_opt_rec.auditable_ind,
1258 x_audit_permission_ind => v_unit_ofr_opt_rec.audit_permission_ind,
1259 x_not_multiple_section_flag => v_unit_ofr_opt_rec.not_multiple_section_flag,
1260 x_sup_uoo_id => v_unit_ofr_opt_rec.sup_uoo_id,
1261 x_relation_type => v_unit_ofr_opt_rec.relation_type,
1262 x_default_enroll_flag => v_unit_ofr_opt_rec.default_enroll_flag,
1263 x_abort_flag => v_unit_ofr_opt_rec.abort_flag
1264 );
1265
1266 END;
1267
1268 /*** update igs_ps_unit_ofr inq_not_wlst by 1 **/
1269
1270 RETURN FALSE;
1271
1272 END IF;
1273
1274 END IF;
1275
1276
1277 IF (p_called_from_wlst = 'Y') THEN
1278
1279 IF lb_validate_prog THEN
1280
1281 IF lb_validate_unit THEN
1282
1283 IF lb_validate_unit_steps THEN
1284 RETURN TRUE;
1285 ELSE
1286 RETURN FALSE;
1287
1288 END IF;
1289
1290 ELSE
1291 RETURN FALSE;
1292 END IF;
1293
1294 ELSE
1295 RETURN FALSE;
1296 END IF;
1297 END IF;
1298
1299 RETURN TRUE;
1300
1301 END finalize_unit;
1302
1303
1304 /** For Combined validations for Unit Steps and Units - Not used in this package for external use**/
1305 FUNCTION validate_combined_unit (
1306 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1307 p_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
1308 p_version_number igs_ps_unit_ofr_opt.version_number%TYPE,
1309 p_cal_type igs_ca_inst.cal_type%TYPE, -- load calendar
1310 p_ci_sequence_number igs_ca_inst.sequence_number%TYPE, -- load calendar
1311 p_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE,
1312 p_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE,
1313 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
1314 p_course_cd igs_en_su_attempt.course_cd%TYPE,
1315 p_enr_method_type igs_en_su_attempt.enr_method_type%TYPE,
1316 p_message_name OUT NOCOPY VARCHAR2,
1317 p_deny_warn OUT NOCOPY VARCHAR2,
1318 p_calling_obj IN VARCHAR2
1319 )
1320 RETURN BOOLEAN AS
1321
1322 lv_validate_unit_steps_message VARCHAR2(2000);
1323 lv_unit_steps_deny_warn VARCHAR2(100);
1324 lv_validate_unit_message fnd_new_messages.message_name%TYPE;
1325 lv_unit_deny_warn VARCHAR2(100);
1326
1327 CURSOR c_teach_cal (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1328 SELECT cal_type, ci_sequence_number
1329 FROM igs_ps_unit_ofr_opt
1330 WHERE uoo_id = cp_uoo_id;
1331
1332 lv_teach_cal_type igs_ca_inst.cal_type%TYPE;
1333 lv_teach_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1334
1335 BEGIN
1336 /* Call old validate unit first and then unit steps if true else return false with message */
1337
1338 /* Cursor that returns the teaching calendar for a given uoo_id */
1339 OPEN c_teach_Cal(p_uoo_id);
1340 FETCH c_teach_cal INTO lv_teach_cal_type, lv_teach_ci_sequence_number;
1341 CLOSE c_teach_Cal;
1342
1343 IF validate_unit (p_unit_cd => p_unit_cd,
1344 p_version_number => p_version_number,
1345 p_cal_type => lv_teach_cal_type, --teach calendar
1346 p_ci_sequence_number => lv_teach_ci_sequence_number, -- teach calendar
1347 p_location_cd => p_location_cd,
1348 p_person_id => p_person_id,
1349 p_unit_class => p_unit_class,
1350 p_uoo_id => p_uoo_id,
1351 p_message_name => lv_validate_unit_message,
1352 p_deny_warn => lv_unit_deny_warn,
1353 p_course_cd => p_course_cd) THEN
1354
1355
1356 -- bypassing this for swap submitswap as unit steps should not
1357 -- validated here as this method is called in a autonomous transaction
1358 -- while unconfirming units and will not be considering units dropped in swap
1359 -- Unit step validation is done before enrolling where it is not
1360 -- a autonomous transaction and will be considering the dropped units
1361 -- in swap.
1362 IF p_calling_obj IN ('SWAP','SUBMITSWAP') THEN
1363
1364 RETURN TRUE;
1365 ELSE
1366
1367 /**** Call new unit step validations if unit validations are thru */
1368 IF validate_unit_steps (p_person_id => p_person_id,
1369 p_cal_type => p_cal_type, --load calendar
1370 p_ci_sequence_number => p_ci_sequence_number, -- load calendar
1371 p_uoo_id => p_uoo_id,
1372 p_course_cd => p_course_cd,
1373 p_enr_method_type => p_enr_method_type,
1374 p_message_name => lv_validate_unit_steps_message,
1375 p_deny_warn => lv_unit_steps_deny_warn,
1376 p_calling_obj => p_calling_obj
1377 ) THEN
1378
1379 p_message_name := lv_validate_unit_steps_message;
1380 p_deny_warn := lv_unit_steps_deny_warn;
1381 RETURN TRUE;
1382 ELSE
1383 p_message_name := lv_validate_unit_steps_message;
1384 p_deny_warn := lv_unit_steps_deny_warn;
1385 RETURN FALSE;
1386 END IF;
1387 END IF;
1388
1389 ELSE
1390 p_message_name := lv_validate_unit_message;
1391 p_deny_warn := lv_unit_deny_warn;
1392 RETURN FALSE;
1393
1394 END IF;
1395
1396 END validate_combined_unit;
1397
1398 -- This routine is used to enroll the student in NON Reserved Category
1399 PROCEDURE enroll_student_nonreserved (p_tab_succ_mail OUT NOCOPY tab_succ_mail,
1400 p_tab_fail_mail OUT NOCOPY tab_fail_mail,
1401 p_success_yn OUT NOCOPY VARCHAR2,
1402 p_uoo_id IN NUMBER ) AS
1403 /* History
1404 WHO WHEN WHAT
1405 kkillams 19-June-2003 Removed the logic of incrementing enr_from_wlst in igs_ps_unit_ofr, as the increment is happenig in SUA API
1406 pmarada 02-sep-2002 Commented the waitlist priority/preferences part of the code
1407 as per of the bug 2526021
1408 ayedubat 12-JUN-2002 Changed the finalize_unit function call to pass the
1409 Load Calendar instead of teaching calendar for the bug:2391510
1410 ptandon 26-Jun-2003 Modified to display waitlist details once before the enrollment process and once after the process. Bug# 2841584
1411 ptandon 05-SEP-2003 Removed the commented code. Enh. Bug No. 3052426
1412 ctyagi 10-OCT-2005 Modified for Bug 4329478
1413 */
1414 --for a Unit Section (uoo_id)
1415 lv_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE; --unit code
1416 lv_version_number igs_ps_unit_ofr_opt.version_number%TYPE; --version number
1417 lv_cal_type igs_ps_unit_ofr_opt.cal_type%TYPE; --calendar type
1418 lv_ci_sequence_number igs_ps_unit_ofr_opt.ci_sequence_number%TYPE; --ci sequence number
1419 lv_location_cd igs_ps_unit_ofr_opt.location_cd%TYPE; --location code
1420 lv_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE; --unit class
1421 lv_waitlist_actual igs_ps_unit_ofr_opt.waitlist_actual%TYPE; --actual waitlist
1422 lv_enrollment_actual igs_ps_unit_ofr_opt.enrollment_actual%TYPE; --actual enrollment
1423
1424 lv_max_quota igs_ps_usec_lim_wlst.enrollment_maximum%TYPE; --maximun quota
1425 lv_max_stdnts_per_wlst igs_ps_usec_lim_wlst.max_students_per_waitlist%TYPE; --maximun students per waitlist
1426 lv_enrolled_yn VARCHAR2(1);
1427 --unit section (unit offering option -uoo_id)
1428 CURSOR igs_ps_unit_ofr_opt_cur
1429 IS
1430 SELECT unit_cd,
1431 version_number,
1432 cal_type,
1433 ci_sequence_number,
1434 location_cd,
1435 unit_class,
1436 waitlist_actual,
1437 enrollment_actual
1438 FROM igs_ps_unit_ofr_opt
1439 WHERE unit_section_status = 'HOLD'
1440 AND uoo_id = p_uoo_id;
1441
1442 --maximum quota (max_quota) and maximum students per waitlist - Will be setting it to 9999 (unlimited) if null or no row
1443 CURSOR igs_ps_usec_lim_wlst_cur IS
1444 SELECT NVL (usec.enrollment_maximum, NVL(uv.enrollment_maximum,9999) ) enrollment_maximum,
1445 usec.max_students_per_waitlist
1446 FROM igs_ps_usec_lim_wlst usec,
1447 igs_ps_unit_ver uv,
1448 igs_ps_unit_ofr_opt uoo
1449 WHERE uoo.unit_cd = uv.unit_cd
1450 AND uoo.version_number = uv.version_number
1451 AND uoo.uoo_id = usec.uoo_id (+)
1452 AND uoo.uoo_id = p_uoo_id;
1453
1454 --- Cursor for Administrative preferences
1455 CURSOR c_adm_prf_cur
1456 IS
1457 SELECT ROWID,
1458 person_id,
1459 course_cd,
1460 unit_cd,
1461 version_number,
1462 cal_type,
1463 ci_sequence_number,
1464 location_cd,
1465 unit_class,
1466 ci_start_dt,
1467 ci_end_dt,
1468 uoo_id,
1469 enrolled_dt,
1470 unit_attempt_status,
1471 administrative_unit_status,
1472 discontinued_dt,
1473 rule_waived_dt,
1474 rule_waived_person_id,
1475 no_assessment_ind,
1476 sup_unit_cd,
1477 sup_version_number,
1478 exam_location_cd,
1479 alternative_title,
1480 override_enrolled_cp,
1481 override_eftsu,
1482 override_achievable_cp,
1483 override_outcome_due_dt,
1484 override_credit_reason,
1485 administrative_priority,
1486 waitlist_dt,
1487 rsv_seat_ext_id,
1488 enr_method_type
1489 FROM igs_en_su_attempt
1490 WHERE uoo_id=p_uoo_id
1491 AND NVL(administrative_priority,0) <> 0
1492 AND unit_attempt_status = 'WAITLISTED'
1493 ORDER BY administrative_priority;
1494
1495 /* Declare Variables */
1496 val_success_adm_persons_tab tab_succ_mail;
1497 ln_rec_ind_val_success NUMBER;
1498 ln_tab_succ_mail_ind NUMBER;
1499 ln_tab_fail_mail_ind NUMBER;
1500 val_success_nadm_persons_tab tab_succ_mail;
1501 ln_nadm_val_success NUMBER;
1502 lv_finalize_unit_message VARCHAR2(2000);
1503 lv_enroll_message_name fnd_new_messages.message_name%TYPE;
1504 filtered_sorted_tab tab_succ_mail;
1505 ln_message_count NUMBER(4);
1506 lv_dummy_message fnd_new_messages.message_name%TYPE;
1507
1508 -- cursor to fetch the First Load Calendar for the Teaching Calendar
1509 CURSOR cur_teach_to_load(p_cal_type IGS_CA_INST.cal_type%TYPE,
1510 p_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1511 SELECT load_cal_type,load_ci_sequence_number
1512 FROM IGS_CA_TEACH_TO_LOAD_V
1513 WHERE teach_cal_type = p_cal_type AND
1514 teach_ci_sequence_number = p_sequence_number AND
1515 load_end_dt >= TRUNC(SYSDATE)
1516 ORDER BY load_start_dt;
1517
1518 rec_teach_to_load cur_teach_to_load%ROWTYPE;
1519
1520 PROCEDURE write_status_to_log
1521 AS
1522 BEGIN
1523 --1) no of students in the waitlist
1524 Fnd_Message.Set_Name ('IGS','IGS_EN_NO_OF_STDNTS_IN_WLST');
1525 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||' '|| TO_CHAR(lv_waitlist_actual));
1526
1527 --2) max. enrollments
1528 Fnd_Message.Set_Name ('IGS','IGS_EN_MAX_ENROLLMENTS');
1529 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||' '|| TO_CHAR(lv_max_quota));
1530
1531 --3) actual enrollments
1532 Fnd_Message.Set_Name ('IGS','IGS_EN_ACT_ENROLLMENTS');
1533 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||' '|| TO_CHAR(lv_enrollment_actual));
1534
1535 --4) max. students per waitlist
1536 Fnd_Message.Set_Name ('IGS','IGS_EN_MAX_STDNTS_PER_WLST');
1537 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||' '|| TO_CHAR(lv_max_stdnts_per_wlst));
1538 END write_status_to_log;
1539
1540 BEGIN
1541 ln_rec_ind_val_success := 0;
1542 ln_tab_succ_mail_ind := 0;
1543 ln_tab_fail_mail_ind := 0;
1544 ln_nadm_val_success := 0;
1545
1546 OPEN igs_ps_unit_ofr_opt_cur;
1547 FETCH igs_ps_unit_ofr_opt_cur INTO lv_unit_cd,
1548 lv_version_number,
1549 lv_cal_type,
1550 lv_ci_sequence_number,
1551 lv_location_cd,
1552 lv_unit_class,
1553 lv_waitlist_actual,
1554 lv_enrollment_actual;
1555
1556 OPEN igs_ps_usec_lim_wlst_cur;
1557 FETCH igs_ps_usec_lim_wlst_cur INTO lv_max_quota,
1558 lv_max_stdnts_per_wlst;
1559 IF igs_ps_usec_lim_wlst_cur%NOTFOUND THEN
1560 lv_max_quota := 9999;
1561 lv_max_stdnts_per_wlst := 9999;
1562 END IF;
1563 CLOSE igs_ps_usec_lim_wlst_cur;
1564
1565 IF lv_max_quota IS NULL THEN
1566 lv_max_quota := 9999;
1567 END IF;
1568
1569 IF lv_max_stdnts_per_wlst IS NULL THEN
1570 lv_max_stdnts_per_wlst := 9999;
1571 END IF;
1572
1573
1574 p_tab_fail_mail.DELETE;
1575 p_tab_succ_mail.DELETE;
1576 filtered_sorted_tab.DELETE;
1577 ln_tab_fail_mail_ind :=NVL(p_tab_fail_mail.FIRST,0);
1578 ln_tab_succ_mail_ind :=NVL(p_tab_succ_mail.FIRST,0);
1579
1580 /********* For Students with administrative preferences ******/
1581
1582 val_success_adm_persons_tab.DELETE;
1583 ln_rec_ind_val_success :=NVL(val_success_adm_persons_tab.FIRST,0);
1584
1585 -- Before Enrollment
1586 Fnd_Message.Set_Name ('IGS','IGS_EN_BEFORE_ENRMT');
1587 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||'->');
1588 write_status_to_log;
1589 fnd_file.put_line (fnd_file.LOG, ' ');
1590
1591 FOR v_adm_prf_cur IN c_adm_prf_cur LOOP
1592 /* Call to finalize_unit to validate the units */
1593
1594 -- Find the Load Calendar for the teaching calendar of the unit section
1595 OPEN cur_teach_to_load(v_adm_prf_cur.cal_type ,v_adm_prf_cur.ci_sequence_number );
1596 FETCH cur_teach_to_load INTO rec_teach_to_load;
1597 CLOSE cur_teach_to_load;
1598 SAVEPOINT failed_unit_validation; -- If any validation failed then rollback, bug 2526021, pmarada.
1599
1600 IF finalize_unit( p_person_id => v_adm_prf_cur.person_id,
1601 p_uoo_id => v_adm_prf_cur.uoo_id,
1602 p_called_from_wlst => 'Y',
1603 p_unit_cd =>v_adm_prf_cur.unit_cd,
1604 p_version_number =>v_adm_prf_cur.version_number,
1605 p_cal_type =>rec_teach_to_load.load_cal_type,
1606 p_ci_sequence_number =>rec_teach_to_load.load_ci_sequence_number,
1607 p_location_cd =>v_adm_prf_cur.location_cd,
1608 p_unit_class =>v_adm_prf_cur.unit_class,
1609 p_enr_method_type =>v_adm_prf_cur.enr_method_type,
1610 p_course_cd =>v_adm_prf_cur.course_cd,
1611 p_rsv_seat_ext_id => v_adm_prf_cur.rsv_seat_ext_id,
1612 p_message_name => lv_finalize_unit_message ) THEN
1613
1614 ln_rec_ind_val_success := NVL(ln_rec_ind_val_success,0) + 1; /*Increment record*/
1615 val_success_adm_persons_tab(ln_rec_ind_val_success).person_id := v_adm_prf_cur.person_id;
1616 val_success_adm_persons_tab(ln_rec_ind_val_success).course_cd := v_adm_prf_cur.course_cd;
1617 -- Added by Chanchal
1618 lv_enrolled_yn := 'N';
1619 /* check for max quota*/
1620 IF (NVL(lv_max_quota,0) - NVL(lv_enrollment_actual,0)) > 0 THEN
1621
1622 Enroll_Persons( p_uoo_id => p_uoo_id,
1623 p_person_id => val_success_adm_persons_tab(ln_rec_ind_val_success).person_id ,
1624 p_course_cd => val_success_adm_persons_tab(ln_rec_ind_val_success).course_cd,
1625 p_waitlist_actual => lv_waitlist_actual, -- IN OUT NOCOPY
1626 p_enrollment_actual => lv_enrollment_actual, -- IN OUT NOCOPY
1627 p_max_quota => lv_max_quota, -- IN
1628 p_max_stdnts_per_wlst => lv_max_stdnts_per_wlst,
1629 p_enrolled_yn => lv_enrolled_yn, -- OUT NOCOPY
1630 p_unit_cd => lv_unit_cd,
1631 p_version_number => lv_version_number,
1632 p_message_name => lv_enroll_message_name);
1633
1634 IF lv_enrolled_yn = 'Y' THEN
1635 /****update success mail pl/sql structure***/
1636 ln_tab_succ_mail_ind := NVL(ln_tab_succ_mail_ind,0) + 1; -- Increment succ records by 1
1637 p_tab_succ_mail(ln_tab_succ_mail_ind).person_id := val_success_adm_persons_tab(ln_rec_ind_val_success).person_id;
1638 p_tab_succ_mail(ln_tab_succ_mail_ind).course_cd := val_success_adm_persons_tab(ln_rec_ind_val_success).course_cd;
1639 IF (NVL(lv_max_quota,0) - NVL(lv_enrollment_actual,0)) <= 0 THEN
1640 EXIT;
1641 END IF;
1642
1643 ELSIF lv_enrolled_yn = 'N' THEN
1644 /***Update failure mail pl/sql structure**/
1645 ln_tab_fail_mail_ind := NVL(ln_tab_fail_mail_ind,0) + 1; -- Increment by failure records by 1
1646 p_tab_fail_mail(ln_tab_fail_mail_ind).person_id := val_success_adm_persons_tab(ln_rec_ind_val_success).person_id;
1647 p_tab_fail_mail(ln_tab_fail_mail_ind).course_cd := val_success_adm_persons_tab(ln_rec_ind_val_success).course_cd;
1648 p_tab_fail_mail(ln_tab_fail_mail_ind).message_name := lv_enroll_message_name;
1649 END IF;
1650 ELSE
1651 EXIT;
1652 END IF;
1653 -- Added by Chanchal
1654
1655 ELSE
1656 ln_tab_fail_mail_ind := NVL(ln_tab_fail_mail_ind,0) + 1; -- Increment by failure records by 1
1657 p_tab_fail_mail(ln_tab_fail_mail_ind).person_id := v_adm_prf_cur.person_id;
1658 p_tab_fail_mail(ln_tab_fail_mail_ind).course_cd := v_adm_prf_cur.course_cd;
1659 p_tab_fail_mail(ln_tab_fail_mail_ind).message_name := Igs_En_Enroll_Wlst.get_message(lv_finalize_unit_message,ln_message_count);
1660
1661 -- Any unit validation failed then rollback. pmarada, bug 2526021
1662 ROLLBACK TO failed_unit_validation;
1663
1664 END IF;
1665
1666 END LOOP;
1667
1668 /* Log Message if Students with ADMIN Preferences dont exist */
1669
1670 IF (NVL(ln_rec_ind_val_success,0) = 0 AND NVL(ln_tab_fail_mail_ind,0) = 0) THEN
1671 fnd_message.set_name('IGS','IGS_EN_NO_ST_WLST_ADM');
1672 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get || ' ' || TO_CHAR(p_uoo_id));
1673
1674 END IF;
1675 -- After Enrollment
1676 IF NVL(val_success_adm_persons_tab.COUNT,0) > 0 THEN
1677
1678 fnd_file.put_line (fnd_file.LOG, ' ');
1679 Fnd_Message.Set_Name ('IGS','IGS_EN_AFTER_ENRMT');
1680 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get ||'->');
1681 write_status_to_log;
1682 END IF;
1683
1684
1685
1686
1687 END enroll_student_nonreserved;
1688
1689 -- The man Proc called from the CONC
1690 PROCEDURE enroll_from_waitlist (errbuf OUT NOCOPY VARCHAR2,
1691 retcode OUT NOCOPY NUMBER,
1692 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
1693 p_org_id IN NUMBER
1694 ) AS
1695 /******************************************************************
1696 Created By :Syam
1697 Date Created By :
1698 Purpose :The main proc called from Conc Manager -
1699 Known limitations,
1700 enhancements,
1701 remarks :
1702
1703 Change History
1704 Who When What
1705 ayedubat 12-JUN-2002 Removed the messages logging into Log file if enroll_student_nonreserved
1706 or enroll_student_reserved functions are returning 'N' as we are already logging
1707 within the corresponding functions.
1708
1709 Procedure Enroll_From_WaitList - Called from Conc Manager with uoo_id passed
1710 ptandon 05-SEP-2003 Added a new validation to call enroll_student_nonreserved to process
1711 waitlisted students only if waitlisting is allowed at institution level and
1712 not restricted at term calendar level otherwise log an error message
1713 as part of Waitlist Enhancements Buld. Enh Bug# 3052426.
1714 ******************************************************************/
1715
1716 /*** Declare PL/SQL structures for Output of enrol processes **/
1717 lv_tab_succ_mail tab_succ_mail;
1718 lv_tab_fail_mail tab_fail_mail;
1719 /*** Declare PL/SQL structures for Output of enrol processes **/
1720
1721 --
1722 -- Cursor to get Load Calendar Type associated with a Unit Section. Enh Bug# 3052426 (ptandon)
1723 --
1724 CURSOR c_get_load_cal_type(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1725 SELECT ctl.load_cal_type
1726 FROM igs_ca_teach_to_load_v ctl,
1727 igs_ps_unit_ofr_opt uoo
1728 WHERE uoo.uoo_id = cp_uoo_id
1729 AND ctl.teach_cal_type = uoo.cal_type
1730 AND ctl.teach_ci_sequence_number = uoo.ci_sequence_number
1731 AND ctl.load_end_dt >= TRUNC(SYSDATE)
1732 ORDER BY ctl.load_start_dt;
1733
1734 l_load_cal_type igs_ca_type.cal_type%TYPE;
1735
1736 --
1737 -- Cursor to Check if Waitlisting is allowed at the institution level. Enh Bug# 3052426 (ptandon)
1738 --
1739 CURSOR c_wait_allow_inst_level IS
1740 SELECT waitlist_allowed_flag
1741 FROM igs_en_inst_wl_stps;
1742
1743 --
1744 -- Cursor to Check if Waitlisting is allowed at the term calendar level. Enh Bug# 3052426 (ptandon)
1745 --
1746 CURSOR c_wait_allow_term_cal(cp_load_cal_type igs_en_inst_wlst_opt.cal_type%TYPE) IS
1747 SELECT waitlist_alwd
1748 FROM igs_en_inst_wlst_opt
1749 WHERE cal_type = cp_load_cal_type;
1750
1751 l_waitlist_allowed igs_en_inst_wl_stps.waitlist_allowed_flag%TYPE;
1752 lv_success_yn VARCHAR2(1);
1753
1754 -- Routine to send mails
1755 PROCEDURE wf_send_mail_stud_adm (p_tab_succ_mail IN tab_succ_mail,
1756 p_tab_fail_mail IN tab_fail_mail,
1757 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
1758 p_org_id IN NUMBER) AS
1759
1760 lv_fail_student_string VARCHAR2(4000);
1761
1762 BEGIN
1763
1764 lv_fail_student_string := NULL;
1765 -- This section is for sending mails to successfully enrolled students
1766 IF NVL(p_tab_succ_mail.COUNT,0) > 0 THEN
1767
1768 FOR succ_mail_rec IN 1..p_tab_succ_mail.COUNT LOOP
1769 Igs_En_Wlst_Gen_Proc.wf_send_mail_stud ( p_person_id => p_tab_succ_mail(succ_mail_rec).person_id,
1770 p_uoo_id => p_uoo_id,
1771 p_org_id => p_org_id);
1772 END LOOP;
1773
1774 END IF;
1775
1776 -- This section is for sending mails to Administrator if student is not successfully enrolled
1777 IF NVL(p_tab_fail_mail.COUNT,0) > 0 THEN
1778 FOR fail_mail_rec IN 1..p_tab_fail_mail.COUNT LOOP
1779 lv_fail_student_string := NVL(lv_fail_student_string,' ') ||' '|| TO_CHAR(p_tab_fail_mail(fail_mail_rec).person_id) ||':'||p_tab_fail_mail(fail_mail_rec).message_name;
1780 END LOOP;
1781 Igs_En_Wlst_Gen_Proc.wf_send_mail_adm ( p_person_id_list => lv_fail_student_string,
1782 p_uoo_id => p_uoo_id,
1783 p_org_id => p_org_id );
1784 END IF;
1785
1786 END wf_send_mail_stud_adm;
1787
1788 BEGIN -- Start of main procedure
1789 retcode :=0;
1790 igs_ge_gen_003.set_org_id(p_org_id);
1791
1792 -- Check whether waitlisting is allowed at institution level. Bug# 3052426 (ptandon)
1793 OPEN c_wait_allow_inst_level;
1794 FETCH c_wait_allow_inst_level INTO l_waitlist_allowed;
1795 CLOSE c_wait_allow_inst_level;
1796 IF l_waitlist_allowed = 'Y' THEN
1797 -- If allowed at institution level, check whether it is not restricted at term calendar level. Bug# 3052426 (ptandon)
1798
1799 -- Get the load calendar type
1800 OPEN c_get_load_cal_type(p_uoo_id);
1801 FETCH c_get_load_cal_type INTO l_load_cal_type;
1802 CLOSE c_get_load_cal_type;
1803
1804 OPEN c_wait_allow_term_cal(l_load_cal_type);
1805 FETCH c_wait_allow_term_cal INTO l_waitlist_allowed;
1806 CLOSE c_wait_allow_term_cal;
1807 ELSE
1808 l_waitlist_allowed := 'N';
1809 END IF;
1810
1811 IF l_waitlist_allowed = 'N' THEN
1812 fnd_message.set_name('IGS','IGS_EN_NO_ENR_WL_NOT_ALWD');
1813 fnd_file.put_line (fnd_file.LOG, ' '|| fnd_message.get || ' ');
1814 ELSE
1815 -- Call enroll_student_nonreserved to process waitlisted students if waitlisting is allowed at
1816 -- institution level and not restricted at term calendar level. Bug# 3052426 (ptandon)
1817 enroll_student_nonreserved (p_tab_succ_mail => lv_tab_succ_mail
1818 ,p_tab_fail_mail => lv_tab_fail_mail
1819 ,p_success_yn => lv_success_yn,
1820 p_uoo_id => p_uoo_id); -- Call enroll student in NOT reserved - Out NOCOPY params tables for Succ students and failed students;
1821
1822 -- WORK FLOW Event raised to send mail
1823
1824 IF NVL(FND_PROFILE.VALUE('IGS_WF_ENABLE'),'N') = 'Y' THEN
1825 wf_send_mail_stud_adm ( p_tab_succ_mail => lv_tab_succ_mail,
1826 p_tab_fail_mail => lv_tab_fail_mail ,
1827 p_uoo_id => p_uoo_id,
1828 p_org_id => p_org_id);
1829 END IF;
1830 END IF;
1831 EXCEPTION
1832 WHEN OTHERS THEN
1833
1834 retcode:=2;
1835 fnd_file.put_line(fnd_file.LOG,SQLERRM);
1836 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1837 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Enroll_Wlst.Enroll_From_WaitList');
1838 IGS_GE_MSG_STACK.ADD;
1839 igs_ge_msg_stack.conc_exception_hndl;
1840
1841 END Enroll_From_WaitList; -- End of main procedure
1842
1843
1844 FUNCTION get_message (p_messages VARCHAR2,
1845 p_message_index NUMBER)
1846 RETURN VARCHAR2 AS
1847 startpos NUMBER(15) ;
1848 endpos NUMBER(15);
1849 ret_value NUMBER(15);
1850 delimit_count NUMBER(15);
1851 mesg_count NUMBER(15);
1852 req_message VARCHAR2(2000);
1853 big_message VARCHAR2(2000);
1854 DELIMITER CONSTANT VARCHAR2(1) := ';' ;
1855 BEGIN
1856 startpos := 0;
1857 endpos := 0;
1858 ret_value := 0;
1859 delimit_count := 0;
1860 mesg_count := 0;
1861 req_message := NULL;
1862
1863 IF (p_message_index < 1) THEN
1864 req_message := NULL;
1865 RETURN req_message;
1866 END IF;
1867
1868 IF (LENGTH(TRIM(p_messages)) > 0) THEN
1869 big_message:= p_messages || DELIMITER ;
1870
1871 LOOP
1872 delimit_count:= delimit_count + 1;
1873 ret_value:= INSTR(big_message, DELIMITER, 1, delimit_count);
1874
1875 IF (ret_value = 0) THEN
1876 EXIT;
1877 END IF;
1878
1879 END LOOP;
1880
1881 mesg_count:= delimit_count - 1;
1882
1883 IF (p_message_index <= mesg_count) THEN
1884
1885 -- getting start delimiter pos for message
1886 IF (p_message_index = 1) THEN
1887 startpos:= 0;
1888 ELSE
1889 startpos := INSTR(big_message, DELIMITER, 1, p_message_index - 1);
1890 END IF;
1891
1892 -- getting end delimiter pos for message
1893 endpos := INSTR(big_message, DELIMITER, 1, p_message_index);
1894 req_message := SUBSTR(big_message, (startpos+1), (endpos-1) - startpos);
1895
1896 ELSE
1897 req_message := NULL;
1898 END IF;
1899
1900 ELSE
1901 req_message := NULL;
1902 END IF;
1903
1904 RETURN req_message;
1905
1906 END get_message;
1907
1908
1909 FUNCTION get_message_count(p_messages IN VARCHAR2)
1910 RETURN NUMBER AS
1911 mesg_count NUMBER(15) ;
1912 delimit_count NUMBER(15);
1913 ret_value NUMBER(15);
1914 DELIMITER CONSTANT VARCHAR2(1) := ';' ;
1915 BEGIN
1916 mesg_count := 0;
1917 delimit_count := 0;
1918 ret_value := 0;
1919
1920 IF (LENGTH(TRIM(p_messages)) < 1) OR p_messages is NULL THEN
1921 RETURN 0;
1922 ELSE
1923 LOOP
1924 delimit_count:= delimit_count + 1;
1925 ret_value:= INSTR(p_messages, DELIMITER, 1, delimit_count);
1926
1927 IF (ret_value = 0) THEN
1928 EXIT;
1929 END IF;
1930 END LOOP;
1931 mesg_count:= delimit_count;
1932 RETURN mesg_count;
1933 END IF;
1934 RETURN 0;
1935
1936 END get_message_count;
1937
1938 PROCEDURE ss_eval_min_or_max_cp(
1939 p_person_id IN igs_en_su_attempt.person_id%TYPE,
1940 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1941 p_load_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1942 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
1943 p_program_cd IN igs_en_su_attempt.course_cd%TYPE,
1944 p_step_type IN igs_en_cpd_ext.s_enrolment_step_type%TYPE,
1945 p_credit_points IN NUMBER,
1946 p_message_name OUT NOCOPY VARCHAR2,
1947 p_deny_warn OUT NOCOPY VARCHAR2,
1948 p_return_status OUT NOCOPY VARCHAR2,
1949 p_enr_method IN igs_en_cat_prc_dtl.enr_method_type%TYPE) AS
1950 /* HISTORY
1951 WHO WHEN WHAT
1952 stutta 20-Nov-2003 Replaced a cursor which would return a program_version with a terms api function call.
1953 Done as part of term records build. Bug 2829263
1954 smanglm 24-jan-2003 call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
1955 Nishikant 18-OCT-2002 The call to the function Igs_En_Elgbl_Program.eval_min_cp got modified since the signatue
1956 got modified. Enrl Elgbl and Validation Build. Bug#2616692.
1957 ayedubat 12-JUN-2002 Initialized the valriable,l_ret_value to TRUE for the bug fix:2391510
1958 ayedubat 07-JUN-2002 The function call,Igs_En_Gen_015.get_academic_cal is replaced with
1959 Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd to get the academic calendar of the
1960 given load calendar rather than current academic calendar for the bug fix:2381603
1961 Nishikant 01NOV2002 The call to the function igs_ss_enr_details.get_notification got modified to add two new
1962 parameters p_person_id, p_message.
1963 */
1964
1965 lv_person_type igs_pe_person_types.person_type_code%TYPE;
1966
1967 -- Cursor to get the Person Type Code corresponding to the System Type
1968 -- Added as per the bug# 2364461.
1969 CURSOR cur_per_typ IS
1970 SELECT person_type_code
1971 FROM igs_pe_person_types
1972 WHERE system_type = 'OTHER';
1973 l_cur_per_typ cur_per_typ%ROWTYPE;
1974
1975 lv_version_number igs_en_stdnt_ps_att.version_number%TYPE;
1976 lv_message VARCHAR2(2000);
1977 -- lv_deny_warn VARCHAR2(20);
1978 l_commencement_type igs_en_cat_prc_dtl.S_STUDENT_COMM_TYPE%TYPE;
1979 l_enrollment_category igs_en_cat_prc_dtl.enrolment_cat%TYPE;
1980 l_enr_method igs_en_cat_prc_dtl.enr_method_type%TYPE;
1981 l_enrol_cal_type igs_ca_type.cal_type%TYPE;
1982 l_enrol_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1983 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
1984 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1985 l_notification_flag igs_en_cpd_ext.notification_flag%TYPE;
1986 l_ret_value BOOLEAN := TRUE;
1987 -- lv_enrolment_step_type igs_en_cpd_ext.s_enrolment_step_type%TYPE;
1988 l_acad_start_dt IGS_CA_INST.start_dt%TYPE;
1989 l_acad_end_dt IGS_CA_INST.end_dt%TYPE;
1990 l_alternate_code IGS_CA_INST.alternate_code%TYPE;
1991
1992 -- Below Two local variables added as part of Enrl Elgbl and Validation Build. Bug#2616692
1993 l_credit_points igs_en_config_enr_cp.min_cp_per_term%TYPE := NULL;
1994 l_min_credit_point igs_en_config_enr_cp.min_cp_per_term%TYPE := NULL;
1995 l_message VARCHAR2(2000);
1996 l_return_status VARCHAR2(10);
1997 l_dummy VARCHAR2(200);
1998
1999 BEGIN
2000 lv_message := NULL;
2001
2002 /*** To get person type ***/
2003 OPEN cur_per_typ; --Added as per bug# 2364461
2004 FETCH cur_per_typ into l_cur_per_typ; --Added as per bug# 2364461
2005 lv_person_type := NVL(Igs_En_Gen_008.enrp_get_person_type(p_program_cd),l_cur_per_typ.person_type_code);
2006 CLOSE cur_per_typ; --Added as per bug# 2364461
2007 /*** To get person type ***/
2008 IF p_enr_method IS NULL THEN
2009 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
2010 igs_en_gen_017.enrp_get_enr_method(
2011 p_enr_method_type => l_enr_method,
2012 p_error_message => l_message,
2013 p_ret_status => l_return_status);
2014
2015 IF l_return_status = 'FALSE' THEN
2016 p_message_name := 'IGS_SS_EN_NOENR_METHOD' ;
2017 p_return_status := 'FALSE';
2018 p_deny_warn := 'DENY';
2019 END IF;
2020 ELSE
2021 l_enr_method:= p_enr_method;
2022 END IF;
2023
2024 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
2025 p_cal_type => p_load_cal_type,
2026 p_ci_sequence_number => p_load_ci_sequence_number,
2027 p_acad_cal_type => l_acad_cal_type,
2028 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
2029 p_acad_ci_start_dt => l_acad_start_dt,
2030 p_acad_ci_end_dt => l_acad_end_dt,
2031 p_message_name => lv_message );
2032
2033 IF lv_message IS NOT NULL THEN
2034 p_message_name := lv_message;
2035 p_deny_warn := 'DENY';
2036 p_return_status := 'FALSE';
2037 RETURN ;
2038 END IF;
2039
2040 l_enrollment_category := Igs_En_Gen_003.enrp_get_enr_cat(
2041 p_person_id => p_person_id,
2042 p_course_cd => p_program_cd,
2043 p_cal_type => l_acad_cal_type,
2044 p_ci_sequence_number => l_acad_ci_sequence_number,
2045 p_session_enrolment_cat => NULL,
2046 p_enrol_cal_type => l_enrol_cal_type ,
2047 p_enrol_ci_sequence_number => l_enrol_sequence_number,
2048 p_commencement_type => l_commencement_type,
2049 p_enr_categories => l_dummy );
2050
2051
2052 /*** To get course version ***/
2053 lv_version_number := igs_en_spa_terms_api.get_spat_program_version(
2054 p_person_id => p_person_id,
2055 p_program_cd => p_program_cd,
2056 p_term_cal_type => p_load_cal_type,
2057 p_term_sequence_number => p_load_ci_sequence_number);
2058
2059 IF p_step_type NOT IN ('FMAX_CRDT','FMIN_CRDT') THEN
2060 p_message_name := 'IGS_TR_SYS_STEP_TY_NOT_EXIST';
2061 p_deny_warn := 'DENY';
2062 p_return_status := 'FALSE';
2063 RETURN ;
2064 END IF;
2065
2066 lv_message := null;
2067
2068 l_notification_flag := igs_ss_enr_details.get_notification(
2069 p_person_type => lv_person_type,
2070 p_enrollment_category => l_enrollment_category,
2071 p_comm_type => l_commencement_type,
2072 p_enr_method_type => l_enr_method,
2073 p_step_group_type => 'PROGRAM',
2074 p_step_type => p_step_type ,
2075 p_person_id => p_person_id,
2076 p_message => lv_message
2077 );
2078 IF lv_message IS NOT NULL THEN
2079 p_return_status := 'FALSE';
2080 p_message_name := lv_message;
2081 RETURN;
2082 END IF;
2083 IF l_notification_flag IS NOT NULL THEN
2084 IF p_step_type = 'FMAX_CRDT' THEN
2085 l_ret_value := igs_en_elgbl_program.eval_max_cp( p_person_id,
2086 p_load_cal_type,
2087 p_load_ci_sequence_number,
2088 p_uoo_id,
2089 p_program_cd,
2090 lv_version_number,
2091 lv_message,
2092 l_notification_flag,
2093 p_credit_points,
2094 'JOB'
2095 );
2096 ELSIF p_step_type = 'FMIN_CRDT' THEN
2097 -- The p_credit_points parameter in the below call is made as an IN/OUT parameter
2098 -- Four new parameters p_enrollment_category, p_comm_type, p_method_type, p_min_credit_point added
2099 -- as part of the Enrl Elgbl and Validation Build. Bug#2616692.
2100 -- Hence the variable l_credit_points passed to the call.
2101 l_credit_points := p_credit_points;
2102 l_ret_value := igs_en_elgbl_program.eval_min_cp( p_person_id,
2103 p_load_cal_type,
2104 p_load_ci_sequence_number,
2105 p_uoo_id,
2106 p_program_cd,
2107 lv_version_number,
2108 lv_message,
2109 l_notification_flag,
2110 l_credit_points,
2111 l_enrollment_category,
2112 l_commencement_type,
2113 l_enr_method,
2114 l_min_credit_point,
2115 'JOB'
2116 );
2117 END IF;
2118 END IF;
2119
2120 IF l_ret_value THEN
2121 p_message_name := lv_message;
2122 IF p_message_name IS NOT NULL THEN
2123 p_deny_warn := l_notification_flag;
2124 ELSE
2125 p_deny_warn := NULL;
2126 END IF;
2127 p_return_status := 'TRUE';
2128 RETURN ;
2129 ELSE
2130 -- handling of DENY / WARN has been changed. Once the step is configured, the step evaluates to
2131 -- DENY/WARN programatically , and the initial value gets overriden.
2132 p_message_name := lv_message;
2133 IF p_message_name = 'IGS_SS_WARN_MIN_CP_REACHED' OR p_message_name = 'IGS_SS_WARN_MAX_CP_REACHED' then
2134 p_deny_warn := 'WARN';
2135 ELSE
2136 p_deny_warn := 'DENY';
2137 END IF ;
2138 p_return_status := 'FALSE';
2139 RETURN ;
2140 END IF;
2141
2142 -- if the step is not defined then the code will reach this point.
2143 p_message_name := NULL;
2144 p_deny_warn := NULL;
2145 p_return_status := 'TRUE';
2146 RETURN ;
2147
2148 END ss_eval_min_or_max_cp;
2149
2150 END Igs_En_Enroll_Wlst; -- End of package body