1 PACKAGE BODY Igs_En_Timeslots AS
2 /* $Header: IGSEN74B.pls 120.6 2006/03/15 22:08:15 svanukur ship $ */
3
4 /*************************************************************
5 Created By : sraj
6 Date Created By : 2000/13/05
7 Purpose : To set the column values before inserting.
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 jbegum 25-Jun-2003 BUG#2930935
12 Modified local function ENRP_CUR_CRITERIA
13 KNAG.IN 12-APR-2001 Included enrollment credit point
14 priority in timeslot allocation
15 as per enh bug 1710227
16 Nishikant 05AUG2002 Bug#2443771. The cursor cur_total_admted_stdnts got modified to select the students
17 who enrolled directly in Student Enrollments. A new function calc_cum_gpa_person was
18 written to calculate the Total GPA for a student ina provided LOAD or TEACHING calendar.
19 also a new function acad_teach_rel_exist introduced in Spec and Body to be used in a cursor only.
20 Nishikant 20DEC2002 Bug#2712493. The cursors cur_total_enrled_stdnts and cur_total_admted_stdnts got modified,
21 in the function enrp_total_students, to select properly the students under 'Enrolled'
22 and 'Admitted' category.
23 Nishikant 31MAR2003 The field full_name modified to last_name in the record
24 type pdata_1 and pdata_2. Bug#2455364.
25 smaddali 20-sep-2004 Modified enrp_total_students for cursor cur_total_enrled_stdnts bug#3918075
26 ctyagi 13-Apr-2005 Modified cursor cur_total_admted_stdnts bug#4297791
27 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in enrp_assign_timeslot procedure as a part of bug#4958173.
28 ***************************************************************/
29
30 plsql_empty plsql_table_1;
31 gpa_ord NUMBER := 0 ;
32 gpa_sort_ord igs_en_timeslot_pref.preference_code%TYPE;
33 cpc_ord NUMBER := 0 ;
34 cpc_sort_ord igs_en_timeslot_pref.preference_code%TYPE;
35 ecp_ord NUMBER := 0 ;
36 ecp_sort_ord igs_en_timeslot_pref.preference_code%TYPE;
37
38 cnt4 NUMBER := 0;
39 plsql_4 plsql_table_2;
40 plsql_empty_4 plsql_table_2;
41 -------------------------------------------------------------------------------
42 FUNCTION enrp_total_num_students(
43 /*************************************************************
44 Created By : sraj
45 Date Created By : 2000/13/05
46 Purpose : To set the column values before inserting.
47 Know limitations, enhancements or remarks
48 Change History
49 Who When What
50 (reverse chronological order - newest change first)
51 ***************************************************************/
52
53 p_prg_type_gr_cd IN VARCHAR2,
54 p_stdnt_type IN VARCHAR2,
55 p_cal_type IN VARCHAR2,
56 p_seq_num IN NUMBER)
57 RETURN NUMBER AS
58 plsql_1 plsql_table_1;
59 BEGIN
60 plsql_1 := enrp_total_students(p_prg_type_gr_cd,p_stdnt_type,p_cal_type,p_seq_num);
61 RETURN plsql_1.COUNT;
62 END enrp_total_num_students;
63
64 FUNCTION enrp_get_working_day (
65 /*************************************************************
66 Created By : sraj
67 Date Created By : 2000/13/05
68 Purpose : To set the column values before inserting.
69 Know limitations, enhancements or remarks
70 Change History
71 Who When What
72 (reverse chronological order - newest change first)
73 ***************************************************************/
74
75 p_prsnt_date DATE
76 ) RETURN DATE
77 AS
78 BEGIN
79 IF TO_CHAR(p_prsnt_date,'DY') NOT IN ('SAT','SUN') THEN
80 -- if present day is working day return the same
81 RETURN TRUNC(p_prsnt_date);
82 ELSE
83 -- call function to get the working day
84 RETURN enrp_get_working_day(p_prsnt_date +1);
85 END IF;
86 END enrp_get_working_day;
87
88 ----------------------------------------------------------------------
89 PROCEDURE enrp_para_calculation(
90 /*************************************************************
91 Created By : sraj
92 Date Created By : 2000/13/05
93 Purpose : To set the column values before inserting.
94 Know limitations, enhancements or remarks
95 Change History
96 Who When What
97 (reverse chronological order - newest change first)
98 ***************************************************************/
99
100 p_program_type_group_cd IN VARCHAR2,
101 p_student_type IN VARCHAR2,
102 p_cal_type IN VARCHAR2,
103 p_seq_number IN NUMBER,
104 p_timeslot IN VARCHAR2,
105 p_ts_start_dt IN DATE,
106 p_ts_end_dt IN DATE,
107 p_length_of_time IN VARCHAR2,
108 p_start_time IN DATE,
109 p_end_time IN DATE,
110 p_total_num_students OUT NOCOPY NUMBER,
111 p_num_ts_sessions OUT NOCOPY NUMBER) AS
112
113 total_min_per_day NUMBER := 0;
114 total_days NUMBER := 0;
115 v_date DATE;
116 BEGIN
117
118 -- calling the function to get total number of students who satisfied the selection criteria
119 p_total_num_students := enrp_total_num_students(p_program_type_group_cd,p_student_type,p_cal_type,p_seq_number);
120 IF p_length_of_time = 0 THEN
121 -- Unlimited Length of Time has been selected
122 p_num_ts_sessions := 1;
123 ELSE
124 -- looping to calculate total number of working days
125 v_date := enrp_get_working_day(p_ts_start_dt);
126 LOOP
127 IF v_date <= p_ts_end_dt THEN
128 total_days := total_days + 1;
129 ELSE
130 EXIT; -- reached end_date
131 END IF;
132 v_date := enrp_get_working_day(v_date+1);
133 END LOOP;
134 --calculating the total number of timeslot sessions
135 -- getting difference in hours converting to minutes
136 total_min_per_day := (TO_NUMBER(TO_CHAR(p_end_time,'HH24')) - TO_NUMBER(TO_CHAR(p_start_time,'HH24'))) * 60;
137 -- getting difference in minutes and adding to total
138 total_min_per_day := total_min_per_day + TO_NUMBER(TO_CHAR(p_end_time,'MI')) - TO_NUMBER(TO_CHAR(p_start_time,'MI'));
139 p_num_ts_sessions := TRUNC( (total_min_per_day * total_days)/p_length_of_time);
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
145 FND_MESSAGE.SET_TOKEN('NAME','Igs_en_timeslots.enrp_para_calculation');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END enrp_para_calculation;
149
150 -------------------------------------------------------------------------------
151 FUNCTION enrp_calc_slots(
152 /*************************************************************
153 Created By : sraj
154 Date Created By : 2000/13/05
155 Purpose : To set the column values before inserting.
156 Know limitations, enhancements or remarks
157 Change History
158 Who When What
159 (reverse chronological order - newest change first)
160 ***************************************************************/
161
162 p_start_time IN DATE,
163 p_end_time IN DATE,
164 p_start_date IN DATE,
165 p_end_date IN DATE,
166 p_length_of_time IN NUMBER)
167 RETURN plsql_table_3 AS
168
169 plsql_5 plsql_table_3;
170 l_start_time NUMBER ;
171 l_end_time NUMBER ;
172 l_date DATE;
173 l_time_counter NUMBER ;
174 tmp NUMBER := 0;
175 cnt NUMBER := 0;
176
177 BEGIN
178 l_start_time := TO_NUMBER(TO_CHAR(p_start_time,'HH24'))* 60 + TO_NUMBER(TO_CHAR(p_start_time,'MI'));
179 l_end_time := TO_NUMBER(TO_CHAR(p_end_time,'HH24'))* 60 + TO_NUMBER(TO_CHAR(p_end_time,'MI'));
180 l_time_counter := l_start_time;
181
182 IF p_length_of_time = 0 THEN
183 plsql_5(1).start_dt_time := TRUNC(p_start_date) + l_start_time/(24*60);
184 plsql_5(1).end_dt_time := TRUNC(p_end_date) + l_end_time/(24*60);
185 ELSE
186 l_date := enrp_get_working_day(p_start_date);
187 -- looping through to populate start And end date and time of timeslot sessions
188 LOOP
189 cnt := cnt + 1;
190 plsql_5(cnt).start_dt_time := TRUNC(l_date) + l_time_counter/(24*60);
191 l_time_counter := l_time_counter + p_length_of_time;
192 IF l_time_counter > l_end_time THEN
193 l_date := enrp_get_working_day(l_date + 1);
194 IF l_date > p_end_date THEN
195 plsql_5.DELETE(cnt);
196 EXIT; -- reached the end date of the time slot
197 END IF;
198 tmp := l_time_counter - l_end_time;
199 l_time_counter := l_start_time + tmp;
200 END IF;
201 plsql_5(cnt).end_dt_time := TRUNC(l_date) + l_time_counter/(24*60);
202 IF (l_time_counter = l_end_time) THEN
203 l_time_counter := l_start_time;
204 l_date := enrp_get_working_day(l_date + 1);
205 IF l_date > p_end_date THEN
206 EXIT; -- reached the end date of the time slot
207 END IF;
208 END IF;
209 END LOOP;
210 END IF;
211 RETURN plsql_5;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
216 FND_MESSAGE.SET_TOKEN('NAME','Igs_en_timeslots.enrp_calc_slots');
217 IGS_GE_MSG_STACK.ADD;
218 App_Exception.Raise_Exception;
219 END enrp_calc_slots;
220
221 --------------------------------------------------------------
222 FUNCTION enrp_cur_criteria(
223 /*************************************************************
224 Created By : sraj
225 Date Created By : 2000/13/05
226 Purpose : To set the column values before inserting.
227 Know limitations, enhancements or remarks
228 Change History
229 (reverse chronological order - newest change first)
230 Who When What
231 jbegum 25-Jun-2003 BUG#2930935
232 Modified cursors cur_load_teach_prd,cur_enrolled_cp.
233 KNAG.IN 12-APR-2001 Included enrollment credit point
234 priority in timeslot allocation
235 as per enh bug 1710227
236 Nishikant 02AUG2002 Bug#2443771. Calculation of GPA for the student was missing.
237 Created a local procedure calc_cum_gpa_person to calculate cumulative GPA
238 for a student of all the program attempts.
239 ***************************************************************/
240
241 p_plsql_1 IN OUT NOCOPY plsql_table_1,
242 p_priority_value IN VARCHAR2,
243 p_preference_code IN VARCHAR2,
244 p_preference_version IN NUMBER,
245 p_sequence_number IN NUMBER,
246 p_pointer IN NUMBER,
247 p_ts_stup_id IN NUMBER)
248 RETURN plsql_table_1 IS
249
250 CURSOR c_cal_type(p_ts_stup_id NUMBER) IS
251 SELECT ets.cal_type, ets.sequence_number
252 FROM igs_en_timeslot_stup ets
253 WHERE igs_en_timeslot_stup_id = p_ts_stup_id;
254 l_cal_type igs_en_timeslot_stup.cal_type%TYPE;
255 l_seq_num igs_en_timeslot_stup.sequence_number%TYPE;
256
257 CURSOR cur_prog_type(p_person_id NUMBER , p_course_type VARCHAR2 ) IS
258 SELECT sca.person_id
259 FROM igs_en_stdnt_ps_att sca,
260 igs_ps_ver pv
261 WHERE sca.person_id = p_person_id AND
262 sca.course_cd = pv.course_cd AND
263 sca.version_number = pv.version_number AND
264 pv.course_type = p_course_type;
265
266 CURSOR cur_org_unit (p_person_id NUMBER , p_org_unit_cd VARCHAR2 ) IS
267 SELECT sca.person_id
268 FROM igs_en_stdnt_ps_att sca,
269 igs_ps_ver pv
270 WHERE sca.person_id = p_person_id AND
271 sca.course_cd = pv.course_cd AND
272 sca.version_number = pv.version_number AND
273 pv.responsible_org_unit_cd = p_org_unit_cd;
274
275 CURSOR cur_program (p_person_id NUMBER , p_course_cd VARCHAR2,p_version_number NUMBER ) IS
276 SELECT sca.person_id
277 FROM igs_en_stdnt_ps_att sca
278 WHERE sca.person_id = p_person_id AND
279 sca.course_cd = p_course_cd AND
280 sca.version_number = p_version_number;
281
282 CURSOR cur_person_grp (p_person_id NUMBER , p_group_cd VARCHAR2 ) IS
283 SELECT pgm.person_id
284 FROM igs_pe_prsid_grp_mem pgm,
285 igs_pe_persid_group pg
286 WHERE pgm.person_id = p_person_id AND
287 pgm.group_id = pg.group_id AND
288 pg.group_cd = p_group_cd
289 AND nvl( pgm.START_DATE,SYSDATE)<= SYSDATE
290 AND nvl( pgm.END_DATE,SYSDATE)>= SYSDATE;
291
292 CURSOR cur_prog_stage (p_person_id NUMBER , p_course_stage_type VARCHAR2 ) IS
293 SELECT sca.person_id
294 FROM igs_en_stdnt_ps_att sca,
295 igs_ps_stage ps
296 WHERE sca.person_id = p_person_id AND
297 sca.course_cd = ps.course_cd AND
298 sca.version_number = ps.version_number AND
299 ps.course_stage_type = p_course_stage_type;
300
301 CURSOR cur_prog_cd (p_person_id NUMBER) IS
302 SELECT sca.course_cd
303 FROM igs_en_stdnt_ps_att sca
304 WHERE sca.person_id = p_person_id and
305 sca.course_attempt_status = 'ENROLLED';
306
307 CURSOR cur_load_teach_prd (p_person_id NUMBER,
308 p_cal_type VARCHAR2,
309 p_sequence_number NUMBER) IS
310 SELECT sua.unit_cd,sua.version_number,sua.override_enrolled_cp,sua.uoo_id
311 FROM igs_en_su_attempt sua,
312 igs_ca_inst_rel carel
313 WHERE sua.person_id = p_person_id and
314 sua.unit_attempt_status = 'ENROLLED' and
315 ((sua.cal_type = p_cal_type and
316 sua.ci_sequence_number = p_sequence_number)OR
317 (sua.cal_type = carel.sub_cal_type and
318 sua.ci_sequence_number = carel.sub_ci_sequence_number and
319 carel.sup_cal_type = p_cal_type and
320 carel.sup_ci_sequence_number = p_sequence_number));
321
322 CURSOR cur_enrolled_cp (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
323 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points
324 FROM igs_ps_unit_ver uv ,
325 igs_ps_usec_cps cps ,
326 igs_ps_unit_ofr_opt uoo
327 WHERE
328 uoo.uoo_id = cps.uoo_id(+) AND
329 uoo.unit_cd = uv.unit_cd AND
330 uoo.version_number = uv.version_number AND
331 uoo.uoo_id = cp_uoo_id;
332
333 cur_enrolled_cp_rec cur_enrolled_cp%ROWTYPE;
334 cur_load_teach_prd_rec cur_load_teach_prd%ROWTYPE;
335 cur_criteria_rec igs_en_stdnt_ps_att.person_id%TYPE;
336 cnt_2 NUMBER := 0;
337 cnt_tmp NUMBER := 0;
338 plsql_2 plsql_table_1;
339 plsql_tmp plsql_table_1;
340 l_gpa NUMBER := -1;
341 l_cpc NUMBER := -1;
342 l_ecp NUMBER := 0;
343 cur_rec_found BOOLEAN := FALSE;
344
345 /* Begin of the local Procedure calc_cum_gpa_person */
346 PROCEDURE calc_cum_gpa_person(
347 /*************************************************************
348 Created By : Nishikant
349 Date Created By : 31JUL2002
350 Purpose : This is a local procedure which calculates the cumulative gpa for all
351 the program attempt for a person. It considers all the unit attempts for the person
352 which are completed or discontinued before the end date of the provided LOAD calendar
353 or the load calendar associated with the provided TEACH calendar.
354 Know limitations, enhancements or remarks
355 Change History
356 Who When What
357 swaghmar 15-Sep-2005 Bug# 4491456 - Modified datatypes
358 (reverse chronological order - newest change first)
359 ***************************************************************/
360 p_person_id IN NUMBER,
361 p_cal_type IN VARCHAR2, -- It can be either LOAD or TEACH calendar
362 p_seq_num IN NUMBER,
363 p_gpa OUT NOCOPY NUMBER) AS
364
365 CURSOR c_chk_cal_cat IS
366 SELECT s_cal_cat
367 FROM igs_ca_type
368 WHERE cal_type = p_cal_type;
369 l_chk_cal_cat igs_ca_type.s_cal_cat%TYPE;
370
371 CURSOR c_teach_to_load IS
372 SELECT load_cal_type, load_ci_sequence_number
373 FROM igs_ca_teach_to_load_v
374 WHERE teach_cal_type = p_cal_type
375 AND teach_ci_sequence_number = p_seq_num
376 ORDER BY load_start_dt DESC;
377 l_load_cal_type igs_ca_teach_to_load_v.load_cal_type%TYPE;
378 l_load_ci_sequence_number igs_ca_teach_to_load_v.load_ci_sequence_number%TYPE;
379
380 CURSOR c_ps_att IS
381 SELECT course_cd, version_number, cal_type
382 FROM IGS_EN_STDNT_PS_ATT sca
383 WHERE person_id = p_person_id;
384 l_ps_att c_ps_att%ROWTYPE;
385
386 l_gpa NUMBER;
387 l_dummy_gpa_cp NUMBER;
388 l_dummy_gpa_quality_points NUMBER;
389 l_total_gpa NUMBER;
390
391 l_init_msg_list VARCHAR2(20) ;
392 l_return_status VARCHAR2(30);
393 l_msg_count NUMBER(2);
394 l_msg_data VARCHAR2(1000);
395
396 BEGIN
397 l_total_gpa := 0;
398 l_init_msg_list := FND_API.G_TRUE;
399
400 OPEN c_chk_cal_cat;
401 FETCH c_chk_cal_cat INTO l_chk_cal_cat;
402 CLOSE c_chk_cal_cat;
403 -- It checks here whether the provided calendar is TEACHING or LOAD.
404 -- If its TEACHING then it finds out the first LOAD attached with it.
405 IF l_chk_cal_cat = 'TEACHING' THEN
406 OPEN c_teach_to_load;
407 FETCH c_teach_to_load INTO l_load_cal_type, l_load_ci_sequence_number;
408 CLOSE c_teach_to_load;
409 ELSE
410 l_load_cal_type := p_cal_type;
411 l_load_ci_sequence_number := p_seq_num;
412 END IF;
413
414 -- It loops through each program attempts for the person and calls the function to get the
415 -- GPA for all the unit attempts under the progam attempt whose end date of the TEACHING calendar
416 -- is earlier than the end date of the LOAD calendar.
417 FOR l_ps_att IN c_ps_att
418 LOOP
419
420 -- If the Statistic Type and System Statistic Type are sent as NULL then it will find out the STANDARD GPA
421 -- for the unit attempts.
422 igs_pr_cp_gpa.get_gpa_stats(
423 p_person_id,
424 l_ps_att.course_cd,
425 NULL, -- Statistic type
426 l_load_cal_type,
427 l_load_ci_sequence_number,
428 NULL, -- System Statistic Type
429 'Y', -- Cumulative indicator
430 l_gpa, -- OUT parameter, which will be the required gpa value.
431 l_dummy_gpa_cp, -- OUT parameter, not required.
432 l_dummy_gpa_quality_points, -- OUT parameter, not required.
433 l_init_msg_list,
434 l_return_status, -- OUT parameter, not required.
435 l_msg_count, -- OUT parameter, not required.
436 l_msg_data -- OUT parameter, not required.
437 );
438 -- Sometimes the OUT parameter l_gpa value can be NULL if Statistic Type is not defined at Organisation or Institution level.
439 -- A record should exist in the Organization Unit Statistic Type Configuration form or Institution Statistic Type Configuration form
440 -- with Standard Indicator as checked and the Timeframe field value should be either Cumulative or Both.
441 -- Also when no unit COMPLETED or attempts found then GPA value will be NULL.
442 IF l_gpa IS NOT NULL THEN
443 l_total_gpa := l_total_gpa + l_gpa;
444 END IF;
445 END LOOP;
446 p_gpa := l_total_gpa;
447 EXCEPTION
448 WHEN OTHERS THEN
449 IF c_chk_cal_cat%ISOPEN THEN
450 CLOSE c_chk_cal_cat;
451 END IF;
452 IF c_teach_to_load%ISOPEN THEN
453 CLOSE c_teach_to_load;
454 END IF;
455 IF c_ps_att%ISOPEN THEN
456 CLOSE c_ps_att;
457 END IF;
458 END calc_cum_gpa_person; /* end of the local procedure calc_cum_gpa_person*/
459
460 BEGIN
461
462 IF p_priority_value = 'PROG_TYPE' THEN
463 FOR i IN 1..p_plsql_1.COUNT LOOP
464 OPEN cur_prog_type(p_plsql_1(i).person_id,p_preference_code);
465 FETCH cur_prog_type INTO cur_criteria_rec;
466 IF cur_prog_type%FOUND THEN
467 cnt_2 := cnt_2 + 1;
468 plsql_2(cnt_2) := p_plsql_1(i);
469 ELSE
470 cnt_tmp := cnt_tmp + 1;
471 plsql_tmp(cnt_tmp) := p_plsql_1(i);
472 END IF;
473 CLOSE cur_prog_type;
474 END LOOP; -- plsql_1
475 -- instead of deleting the records, storing them into temp. table And asigning back
476 p_plsql_1 := plsql_tmp;
477 RETURN plsql_2;
478 ELSIF p_priority_value = 'ORG_UNIT' THEN
479 FOR i IN 1..p_plsql_1.COUNT LOOP
480 OPEN cur_org_unit(p_plsql_1(i).person_id,p_preference_code);
481 FETCH cur_org_unit INTO cur_criteria_rec;
482 IF cur_org_unit%FOUND THEN
483 cnt_2 := cnt_2 + 1;
484 plsql_2(cnt_2) := p_plsql_1(i);
485 ELSE
486 cnt_tmp := cnt_tmp + 1;
487 plsql_tmp(cnt_tmp) := p_plsql_1(i);
488 END IF;
489 CLOSE cur_org_unit;
490 END LOOP; -- plsql_1
491 -- instead of deleting the records, storing them into temp. table And asigning back
492 p_plsql_1 := plsql_tmp;
493 RETURN plsql_2;
494 ELSIF p_priority_value = 'PROGRAM' THEN
495 FOR i IN 1..p_plsql_1.COUNT LOOP
496 OPEN cur_program(p_plsql_1(i).person_id,p_preference_code,p_preference_version);
497 FETCH cur_program INTO cur_criteria_rec;
498 IF cur_program%FOUND THEN
499 cnt_2 := cnt_2 + 1;
500 plsql_2(cnt_2) := p_plsql_1(i);
501 ELSE
502 cnt_tmp := cnt_tmp + 1;
503 plsql_tmp(cnt_tmp) := p_plsql_1(i);
504 END IF;
505 CLOSE cur_program;
506 END LOOP; -- plsql_1
507 -- instead of deleting the records, storing them into temp. table And asigning back
508 p_plsql_1 := plsql_tmp;
509 RETURN plsql_2;
510 ELSIF p_priority_value = 'PERSON_GRP' THEN
511 FOR i IN 1..p_plsql_1.COUNT LOOP
512 OPEN cur_person_grp(p_plsql_1(i).person_id,p_preference_code);
513 FETCH cur_person_grp INTO cur_criteria_rec;
514 IF cur_person_grp%FOUND THEN
515 cnt_2 := cnt_2 + 1;
516 plsql_2(cnt_2) := p_plsql_1(i);
517 ELSE
518 cnt_tmp := cnt_tmp + 1;
519 plsql_tmp(cnt_tmp) := p_plsql_1(i);
520 END IF;
521 CLOSE cur_person_grp;
522 END LOOP; -- plsql_1
523 -- instead of deleting the records, storing them into temp. table And asigning back
524 p_plsql_1 := plsql_tmp;
525 RETURN plsql_2;
526 ELSIF p_priority_value = 'PROG_STAGE' THEN
527 FOR i IN 1..p_plsql_1.COUNT LOOP
528 OPEN cur_prog_stage(p_plsql_1(i).person_id,p_preference_code);
529 FETCH cur_prog_stage INTO cur_criteria_rec;
530 IF cur_prog_stage%FOUND THEN
531 cnt_2 := cnt_2 + 1;
532 plsql_2(cnt_2) := p_plsql_1(i);
533 ELSE
534 cnt_tmp := cnt_tmp + 1;
535 plsql_tmp(cnt_tmp) := p_plsql_1(i);
536 END IF;
537 CLOSE cur_prog_stage;
538 END LOOP; -- plsql_1
539 -- instead of deleting the records, storing them into temp. table And asigning back
540 p_plsql_1 := plsql_tmp;
541 RETURN plsql_2;
542 ELSIF p_priority_value = 'GPA' THEN
543 -- copy the order of GPA priority And preference into variables visible for all methods in package
544 gpa_ord := p_pointer;
545 gpa_sort_ord := p_preference_code;
546 -- Finds out here the cal type and sequence type by the help of parameter p_ts_stup
547 OPEN c_cal_type(p_ts_stup_id);
548 FETCH c_cal_type INTO l_cal_type, l_seq_num;
549 CLOSE c_cal_type;
550 FOR i IN 1..p_plsql_1.COUNT LOOP
551 calc_cum_gpa_person( -- Local procudure calculates GPA for the student
552 p_plsql_1(i).person_id,
553 l_cal_type,
554 l_seq_num,
555 l_gpa);
556 -- There is chance to get the l_gpa value as 0. That case will not be considered.
557 -- If Enrolled Credit point has been defined as 0, and Override Credit points also either defined 0 or
558 -- not defined, then the l_gpa value will be zero.
559 -- Also if no grading schema found for the unit attempt or the result of unit attempt is FAIL or WITHDRAWN
560 -- or UNCOMPLETED then the l_gpa value will be 0.
561 IF l_gpa <> 0 THEN
562 cnt_2 := cnt_2 + 1;
563 plsql_2(cnt_2) := p_plsql_1(i);
564 plsql_2(cnt_2).gpa := l_gpa;
565 ELSE
566 cnt_tmp := cnt_tmp + 1;
567 plsql_tmp(cnt_tmp) := p_plsql_1(i);
568 END IF;
569 l_gpa := -1;
570 END LOOP; -- plsql_1
571 -- instead of deleting the records, storing them into temp. table And asigning back
572 p_plsql_1 := plsql_tmp;
573 RETURN plsql_2;
574 ELSIF p_priority_value = 'TOTAL_CP' THEN
575 -- copy the order of TOTAL_CP priority And preference into variables visible for all methods in package
576 cpc_ord := p_pointer;
577 cpc_sort_ord := p_preference_code;
578 FOR i IN 1..p_plsql_1.COUNT LOOP
579 -- Calculating TOTAL_CP for the student
580
581 --initialize the value for each student
582 l_cpc := 0;
583
584 for cur_prog_cd_rec in cur_prog_cd(p_plsql_1(i).person_id) loop
585 l_cpc := l_cpc + NVL(IGS_EN_GEN_001.enrp_clc_sca_pass_cp(p_plsql_1(i).person_id,cur_prog_cd_rec.course_cd,Trunc(sysdate)),0);
586 end loop;
587 IF l_cpc <> 0 THEN
588 cnt_2 := cnt_2 + 1;
589 plsql_2(cnt_2) := p_plsql_1(i);
590 plsql_2(cnt_2).cpc := l_cpc;
591 ELSE
592 cnt_tmp := cnt_tmp + 1;
593 plsql_tmp(cnt_tmp) := p_plsql_1(i);
594 END IF;
595 END LOOP; -- plsql_1
596 -- instead of deleting the records, storing them into temp. table And asigning back
597 p_plsql_1 := plsql_tmp;
598 RETURN plsql_2;
599 ELSIF p_priority_value = 'ENRCP_ASC' THEN
600 -- copy the order of ENRCP_ASC priority And preference into variables visible for all methods in package
601 ecp_ord := p_pointer;
602 ecp_sort_ord := 'A';
603 l_ecp := 0;
604 cur_rec_found := FALSE;
605 FOR i IN 1..p_plsql_1.COUNT LOOP
606 -- Calculating TOTAL_ECP for the student
607 OPEN cur_load_teach_prd(p_plsql_1(i).person_id, p_preference_code ,p_sequence_number);
608 LOOP
609 FETCH cur_load_teach_prd INTO cur_load_teach_prd_rec;
610 IF cur_load_teach_prd%FOUND THEN
611 cur_rec_found := TRUE;
612 IF cur_load_teach_prd_rec.override_enrolled_cp IS NOT NULL THEN
613 l_ecp := l_ecp + cur_load_teach_prd_rec.override_enrolled_cp;
614 ELSE
615 OPEN cur_enrolled_cp(cur_load_teach_prd_rec.uoo_id);
616 FETCH cur_enrolled_cp INTO cur_enrolled_cp_rec;
617 IF cur_enrolled_cp%FOUND THEN
618 l_ecp := l_ecp + cur_enrolled_cp_rec.enrolled_credit_points;
619 END IF;
620 CLOSE cur_enrolled_cp;
621 END IF;
622 ELSE
623 EXIT;
624 END IF;
625 END LOOP;
626 CLOSE cur_load_teach_prd;
627
628 IF cur_rec_found THEN
629 cnt_2 := cnt_2 + 1;
630 plsql_2(cnt_2) := p_plsql_1(i);
631 plsql_2(cnt_2).ecp := l_ecp;
632 l_ecp := 0;
633 cur_rec_found := FALSE;
634 ELSE
635 cnt_tmp := cnt_tmp + 1;
636 plsql_tmp(cnt_tmp) := p_plsql_1(i);
637 END IF;
638 END LOOP; -- plsql_1
639 -- instead of deleting the records, storing them into temp. table And asigning back
640 p_plsql_1 := plsql_tmp;
641 RETURN plsql_2;
642 ELSIF p_priority_value = 'ENRCP_DESC' THEN
643 -- copy the order of ENRCP_DESC priority And preference into variables visible for all methods in package
644 ecp_ord := p_pointer;
645 ecp_sort_ord := 'D';
646 l_ecp := 0;
647 cur_rec_found := FALSE;
648 FOR i IN 1..p_plsql_1.COUNT LOOP
649 -- Calculating TOTAL_ECP for the student
650 OPEN cur_load_teach_prd(p_plsql_1(i).person_id, p_preference_code ,p_sequence_number);
651 LOOP
652 FETCH cur_load_teach_prd INTO cur_load_teach_prd_rec;
653 IF cur_load_teach_prd%FOUND THEN
654 cur_rec_found := TRUE;
655 IF cur_load_teach_prd_rec.override_enrolled_cp IS NOT NULL THEN
656 l_ecp := l_ecp + cur_load_teach_prd_rec.override_enrolled_cp;
657 ELSE
658 OPEN cur_enrolled_cp(cur_load_teach_prd_rec.uoo_id);
659 FETCH cur_enrolled_cp INTO cur_enrolled_cp_rec;
660 IF cur_enrolled_cp%FOUND THEN
661 l_ecp := l_ecp + cur_enrolled_cp_rec.enrolled_credit_points;
662 END IF;
663 CLOSE cur_enrolled_cp;
664 END IF;
665 ELSE
666 EXIT;
667 END IF;
668 END LOOP;
669 CLOSE cur_load_teach_prd;
670
671 IF cur_rec_found THEN
672 cnt_2 := cnt_2 + 1;
673 plsql_2(cnt_2) := p_plsql_1(i);
674 plsql_2(cnt_2).ecp := l_ecp;
675 l_ecp := 0;
676 cur_rec_found := FALSE;
677 ELSE
678 cnt_tmp := cnt_tmp + 1;
679 plsql_tmp(cnt_tmp) := p_plsql_1(i);
680 END IF;
681 END LOOP; -- plsql_1
682 -- instead of deleting the records, storing them into temp. table And asigning back
683 p_plsql_1 := plsql_tmp;
684 RETURN plsql_2;
685 ELSE
686 --error message : selected priority is not available..
687 NULL;
688 END IF;
689 EXCEPTION
690 WHEN OTHERS THEN
691 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
692 FND_MESSAGE.SET_TOKEN('NAME','Igs_en_timeslots.enrp_cur_criteria');
693 IGS_GE_MSG_STACK.ADD;
694 App_Exception.Raise_Exception;
695 END enrp_cur_criteria;
696
697 -------------------------------------------------------------------------
698 FUNCTION enrp_alpha_sort(
699 /*************************************************************
700 Created By : sraj
701 Date Created By : 2000/13/05
702 Purpose : To set the column values before inserting.
703 Know limitations, enhancements or remarks
704 Change History
705 Who When What
706 (reverse chronological order - newest change first)
707 ***************************************************************/
708
709 p_plsql_3 IN OUT NOCOPY plsql_table_1,
710 p_surname_alpha VARCHAR2)
711 RETURN plsql_table_1 IS
712 -- record of plsql_table_1
713 plsql_tmp_rec pdata_1;
714 startcnt NUMBER := 1;
715 cnt NUMBER := 0;
716 plsql_rslt plsql_table_1;
717 BEGIN
718
719 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
720 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
721 IF p_plsql_3(i).last_name > p_plsql_3(j).last_name THEN
722 plsql_tmp_rec := p_plsql_3(i);
723 p_plsql_3(i) := p_plsql_3(j);
724 p_plsql_3(j) := plsql_tmp_rec;
725 END IF;
726 END LOOP;
727 END LOOP;
728 -- to sort on given alphbet
729 FOR i IN 1 .. p_plsql_3.COUNT LOOP
730 IF UPPER(p_plsql_3(i).last_name) >= UPPER(p_surname_alpha) THEN
731 startcnt := i;
732 EXIT;
733 END IF;
734 END LOOP;
735 FOR j IN startcnt .. p_plsql_3.COUNT LOOP
736 cnt := cnt + 1;
737 plsql_rslt(cnt) := p_plsql_3(j);
738 END LOOP;
739 FOR j IN 1 .. startcnt-1 LOOP
740 cnt := cnt + 1;
741 plsql_rslt(cnt) := p_plsql_3(j);
742 END LOOP;
743
744 RETURN plsql_rslt;
745
746 END enrp_alpha_sort;
747
748 -----------------------------------------------------------------------------
749 FUNCTION enrp_gpa_sort(
750 /*************************************************************
751 Created By : sraj
752 Date Created By : 2000/13/05
753 Purpose : To set the column values before inserting.
754 Know limitations, enhancements or remarks
755 Change History
756 Who When What
757 (reverse chronological order - newest change first)
758 ***************************************************************/
759
760 p_plsql_3 IN OUT NOCOPY plsql_table_1,
761 p_surname_alpha VARCHAR2)
762 RETURN plsql_table_1 IS
763 -- record of plsql_table_1
764 plsql_tmp_rec pdata_1;
765 started BOOLEAN := FALSE;
766 startcnt NUMBER := 1;
767 cnt NUMBER := 0;
768 plsql_tmp plsql_table_1;
769 BEGIN
770 IF gpa_sort_ord = 'A' THEN
771 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
772 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
773 IF p_plsql_3(i).gpa > p_plsql_3(j).gpa THEN
774 plsql_tmp_rec := p_plsql_3(i);
775 p_plsql_3(i) := p_plsql_3(j);
776 p_plsql_3(j) := plsql_tmp_rec;
777 END IF;
778 END LOOP;
779 END LOOP;
780 ELSIF gpa_sort_ord = 'D' THEN
781 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
782 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
783 IF p_plsql_3(i).gpa < p_plsql_3(j).gpa THEN
784 plsql_tmp_rec := p_plsql_3(i);
785 p_plsql_3(i) := p_plsql_3(j);
786 p_plsql_3(j) := plsql_tmp_rec;
787 END IF;
788 END LOOP;
789 END LOOP;
790 END IF;
791 -- sort students alphabetically who has the same GPA
792 FOR i IN 1 .. p_plsql_3.COUNT LOOP
793 IF i < p_plsql_3.COUNT AND p_plsql_3(i).gpa = p_plsql_3(i+1).gpa AND NOT started THEN
794 started := TRUE;
795 startcnt := i;
796 cnt := 0;
797 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
798 END IF;
799 IF started THEN
800 cnt := cnt +1 ;
801 plsql_tmp(cnt) := p_plsql_3(i);
802 IF i = p_plsql_3.COUNT OR p_plsql_3(i).gpa <> p_plsql_3(i+1).gpa THEN
803 started := FALSE;
804 plsql_tmp := enrp_alpha_sort(plsql_tmp,p_surname_alpha);
805 FOR j IN 1 .. plsql_tmp.COUNT LOOP
806 p_plsql_3(startcnt) := plsql_tmp(j);
807 startcnt := startcnt +1;
808 END LOOP;
809 END IF;
810 END IF;
811 END LOOP;
812 RETURN p_plsql_3;
813 END enrp_gpa_sort;
814
815 ---------------------------------------------------------------------
816 FUNCTION enrp_cpc_sort(
817 /*************************************************************
818 Created By : sraj
819 Date Created By : 2000/13/05
820 Purpose : To set the column values before inserting.
821 Know limitations, enhancements or remarks
822 Change History
823 Who When What
824 (reverse chronological order - newest change first)
825 ***************************************************************/
826
827 p_plsql_3 IN OUT NOCOPY plsql_table_1,
828 p_surname_alpha VARCHAR2)
829 RETURN plsql_table_1 IS
830 -- record of plsql_table_1
831 plsql_tmp_rec pdata_1;
832 started BOOLEAN := FALSE;
833 startcnt NUMBER := 1;
834 cnt NUMBER := 0;
835 plsql_tmp plsql_table_1;
836 BEGIN
837 IF cpc_sort_ord = 'A' THEN
838 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
839 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
840 IF p_plsql_3(i).cpc > p_plsql_3(j).cpc THEN
841 plsql_tmp_rec := p_plsql_3(i);
842 p_plsql_3(i) := p_plsql_3(j);
843 p_plsql_3(j) := plsql_tmp_rec;
844 END IF;
845 END LOOP;
846 END LOOP;
847 ELSIF cpc_sort_ord = 'D' THEN
848 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
849 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
850 IF p_plsql_3(i).cpc < p_plsql_3(j).cpc THEN
851 plsql_tmp_rec := p_plsql_3(i);
852 p_plsql_3(i) := p_plsql_3(j);
853 p_plsql_3(j) := plsql_tmp_rec;
854 END IF;
855 END LOOP;
856 END LOOP;
857 END IF;
858 -- sort students alphabetically who has the same CPC
859 FOR i IN 1 .. p_plsql_3.COUNT LOOP
860 IF i < p_plsql_3.COUNT AND p_plsql_3(i).cpc = p_plsql_3(i+1).cpc AND NOT started THEN
861 started := TRUE;
862 startcnt := i;
863 cnt := 0;
864 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
865 END IF;
866 IF started THEN
867 cnt := cnt +1 ;
868 plsql_tmp(cnt) := p_plsql_3(i);
869
870 IF i = p_plsql_3.COUNT OR p_plsql_3(i).cpc <> p_plsql_3(i+1).cpc THEN
871 started := FALSE;
872 plsql_tmp := enrp_alpha_sort(plsql_tmp,p_surname_alpha);
873 FOR j IN 1 .. plsql_tmp.COUNT LOOP
874 p_plsql_3(startcnt) := plsql_tmp(j);
875 startcnt := startcnt +1;
876 END LOOP;
877 END IF;
878 END IF;
879 END LOOP;
880 RETURN p_plsql_3;
881 END enrp_cpc_sort;
882
883 -------------------------------------------------------------------------
884
885 FUNCTION enrp_ecp_sort(
886 /*************************************************************
887 Created By : sraj
888 Date Created By : 2000/13/05
889 Purpose : To set the column values before inserting.
890 Know limitations, enhancements or remarks
891 Change History
892 Who When What
893 KNAG.IN 12-APR-2001 Included enrollment credit point
894 priority in timeslot allocation
895 as per enh bug 1710227
896 (reverse chronological order - newest change first)
897 ***************************************************************/
898
899 p_plsql_3 IN OUT NOCOPY plsql_table_1,
900 p_surname_alpha VARCHAR2)
901 RETURN plsql_table_1 IS
902 -- record of plsql_table_1
903 plsql_tmp_rec pdata_1;
904 started BOOLEAN := FALSE;
905 startcnt NUMBER := 1;
906 cnt NUMBER := 0;
907 plsql_tmp plsql_table_1;
908 BEGIN
909 IF ecp_sort_ord = 'A' THEN
910 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
911 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
912 IF p_plsql_3(i).ecp > p_plsql_3(j).ecp THEN
913 plsql_tmp_rec := p_plsql_3(i);
914 p_plsql_3(i) := p_plsql_3(j);
915 p_plsql_3(j) := plsql_tmp_rec;
916 END IF;
917 END LOOP;
918 END LOOP;
919 ELSIF ecp_sort_ord = 'D' THEN
920 FOR i IN 1 .. p_plsql_3.COUNT - 1 LOOP
921 FOR j IN i+1 .. p_plsql_3.COUNT LOOP
922 IF p_plsql_3(i).ecp < p_plsql_3(j).ecp THEN
923 plsql_tmp_rec := p_plsql_3(i);
924 p_plsql_3(i) := p_plsql_3(j);
925 p_plsql_3(j) := plsql_tmp_rec;
926 END IF;
927 END LOOP;
928 END LOOP;
929 END IF;
930 -- sort students alphabetically who has the same ECP
931 FOR i IN 1 .. p_plsql_3.COUNT LOOP
932 IF i < p_plsql_3.COUNT AND p_plsql_3(i).ecp = p_plsql_3(i+1).ecp AND NOT started THEN
933 started := TRUE;
934 startcnt := i;
935 cnt := 0;
936 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
937 END IF;
938 IF started THEN
939 cnt := cnt +1 ;
940 plsql_tmp(cnt) := p_plsql_3(i);
941 IF i = p_plsql_3.COUNT OR p_plsql_3(i).ecp <> p_plsql_3(i+1).ecp THEN
942 started := FALSE;
943 plsql_tmp := enrp_alpha_sort(plsql_tmp,p_surname_alpha);
944 FOR j IN 1 .. plsql_tmp.COUNT LOOP
945 p_plsql_3(startcnt) := plsql_tmp(j);
946 startcnt := startcnt +1;
947 END LOOP;
948 END IF;
949 END IF;
950 END LOOP;
951 RETURN p_plsql_3;
952 END enrp_ecp_sort;
953
954 -----------------------------------------------------------------------------
955
956 PROCEDURE enrp_sort_mngt(
957 /*************************************************************
958 Created By : sraj
959 Date Created By : 2000/13/05
960 Purpose : To set the column values before inserting.
961 Know limitations, enhancements or remarks
962 Change History
963 Who When What
964 KNAG.IN 12-APR-2001 Included enrollment credit point
965 priority in timeslot allocation
966 as per enh bug 1710227
967 (reverse chronological order - newest change first)
968 ***************************************************************/
969
970 p_plsql_2 IN OUT NOCOPY plsql_table_1,
971 p_surname_alpha IN VARCHAR2,
972 p_pointer NUMBER) IS
973 plsql_3 plsql_table_1;
974 started BOOLEAN := FALSE;
975 started_1 BOOLEAN := FALSE;
976 started_2 BOOLEAN := FALSE;
977 startcnt NUMBER := 1;
978 startcnt_1 NUMBER := 1;
979 startcnt_2 NUMBER := 1;
980 plsql_tmp plsql_table_1;
981 plsql_tmp_1 plsql_table_1;
982 plsql_tmp_2 plsql_table_1;
983 cnt NUMBER := 0;
984 cnt_1 NUMBER := 0;
985 cnt_2 NUMBER := 0;
986 BEGIN
987
988 IF gpa_ord <= p_pointer AND gpa_ord <> 0 AND
989 cpc_ord <= p_pointer AND cpc_ord <> 0 AND
990 ecp_ord <= p_pointer AND ecp_ord <> 0 THEN
991
992 -- --Begin of GPA has more priority than CPC and ECP----------------------------------
993
994 IF gpa_ord < cpc_ord AND gpa_ord < ecp_ord THEN -- GPA has more priority than CPC and ECP
995 plsql_3 := enrp_gpa_sort(p_plsql_2,p_surname_alpha);
996 FOR i IN 1 .. plsql_3.COUNT LOOP
997 IF i < plsql_3.COUNT AND plsql_3(i).gpa = plsql_3(i+1).gpa AND NOT started_1 THEN
998 started_1 := TRUE;
999 startcnt_1 := i;
1000 cnt_1 := 0;
1001 plsql_tmp_1 := plsql_empty; -- assigning empty plsql table to erase the content
1002 END IF;
1003 IF started_1 THEN
1004 cnt_1 := cnt_1 +1 ;
1005 plsql_tmp_1(cnt_1) := plsql_3(i);
1006 IF i = plsql_3.COUNT OR plsql_3(i).gpa <> plsql_3(i+1).gpa THEN
1007 started_1 := FALSE;
1008 IF cpc_ord < ecp_ord THEN -- CPC has more priority than ECP
1009 plsql_tmp_1 := enrp_cpc_sort(plsql_tmp_1,p_surname_alpha);
1010 started_2 := FALSE;
1011 startcnt_2 := 1;
1012 cnt_2 := 0 ;
1013 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1014 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).cpc = plsql_tmp_1(j+1).cpc AND
1015 NOT started_2 THEN
1016 started_2 := TRUE;
1017 startcnt_2 := j;
1018 cnt_2 := 0;
1019 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1020 END IF;
1021 IF started_2 THEN
1022 cnt_2 := cnt_2 +1 ;
1023 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1024 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).cpc <> plsql_tmp_1(j+1).cpc THEN
1025 started_2 := FALSE;
1026 plsql_tmp_2 := enrp_ecp_sort(plsql_tmp_2,p_surname_alpha);
1027 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1028 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1029 startcnt_2 := startcnt_2 +1;
1030 END LOOP;
1031 END IF;
1032 END IF;
1033 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1034 plsql_3(startcnt_1) := plsql_tmp_1(j);
1035 startcnt_1 := startcnt_1 +1;
1036 END LOOP;
1037 END LOOP;
1038 ELSIF ecp_ord < cpc_ord THEN -- ECP has more priority than CPC
1039 plsql_tmp_1 := enrp_ecp_sort(plsql_tmp_1,p_surname_alpha);
1040 started_2 := FALSE;
1041 startcnt_2 := 1;
1042 cnt_2 := 0 ;
1043 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1044 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).ecp = plsql_tmp_1(j+1).ecp AND
1045 NOT started_2 THEN
1046 started_2 := TRUE;
1047 startcnt_2 := j;
1048 cnt_2 := 0;
1049 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1050 END IF;
1051 IF started_2 THEN
1052 cnt_2 := cnt_2 +1 ;
1053 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1054 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).ecp <> plsql_tmp_1(j+1).ecp THEN
1055 started_2 := FALSE;
1056 plsql_tmp_2 := enrp_cpc_sort(plsql_tmp_2,p_surname_alpha);
1057 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1058 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1059 startcnt_2 := startcnt_2 +1;
1060 END LOOP;
1061 END IF;
1062 END IF;
1063 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1064 plsql_3(startcnt_1) := plsql_tmp_1(j);
1065 startcnt_1 := startcnt_1 +1;
1066 END LOOP;
1067 END LOOP;
1068 END IF;
1069 END IF;
1070 END IF;
1071 END LOOP;
1072 END IF;
1073
1074 -- --End of GPA has more priority than CPC and ECP----------------------------------
1075
1076 -- --Begin of CPC has more priority than GPA and ECP----------------------------------
1077
1078 IF cpc_ord < gpa_ord AND cpc_ord < ecp_ord THEN -- CPC has more priority than GPA and ECP
1079 plsql_3 := enrp_cpc_sort(p_plsql_2,p_surname_alpha);
1080 FOR i IN 1 .. plsql_3.COUNT LOOP
1081 IF i < plsql_3.COUNT AND plsql_3(i).cpc = plsql_3(i+1).cpc AND NOT started_1 THEN
1082 started_1 := TRUE;
1083 startcnt_1 := i;
1084 cnt_1 := 0;
1085 plsql_tmp_1 := plsql_empty; -- assigning empty plsql table to erase the content
1086 END IF;
1087 IF started_1 THEN
1088 cnt_1 := cnt_1 +1 ;
1089 plsql_tmp_1(cnt_1) := plsql_3(i);
1090 IF i = plsql_3.COUNT OR plsql_3(i).cpc <> plsql_3(i+1).cpc THEN
1091 started_1 := FALSE;
1092 IF gpa_ord < ecp_ord THEN -- GPA has more priority than ECP
1093 plsql_tmp_1 := enrp_gpa_sort(plsql_tmp_1,p_surname_alpha);
1094 started_2 := FALSE;
1095 startcnt_2 := 1;
1096 cnt_2 := 0 ;
1097 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1098 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).gpa = plsql_tmp_1(j+1).gpa AND
1099 NOT started_2 THEN
1100 started_2 := TRUE;
1101 startcnt_2 := j;
1102 cnt_2 := 0;
1103 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1104 END IF;
1105 IF started_2 THEN
1106 cnt_2 := cnt_2 +1 ;
1107 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1108 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).gpa <> plsql_tmp_1(j+1).gpa THEN
1109 started_2 := FALSE;
1110 plsql_tmp_2 := enrp_ecp_sort(plsql_tmp_2,p_surname_alpha);
1111 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1112 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1113 startcnt_2 := startcnt_2 +1;
1114 END LOOP;
1115 END IF;
1116 END IF;
1117 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1118 plsql_3(startcnt_1) := plsql_tmp_1(j);
1119 startcnt_1 := startcnt_1 +1;
1120 END LOOP;
1121 END LOOP;
1122 ELSIF ecp_ord < gpa_ord THEN -- ECP has more priority than GPA
1123 plsql_tmp_1 := enrp_ecp_sort(plsql_tmp_1,p_surname_alpha);
1124 started_2 := FALSE;
1125 startcnt_2 := 1;
1126 cnt_2 := 0 ;
1127 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1128 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).ecp = plsql_tmp_1(j+1).ecp AND
1129 NOT started_2 THEN
1130 started_2 := TRUE;
1131 startcnt_2 := j;
1132 cnt_2 := 0;
1133 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1134 END IF;
1135 IF started_2 THEN
1136 cnt_2 := cnt_2 +1 ;
1137 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1138 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).ecp <> plsql_tmp_1(j+1).ecp THEN
1139 started_2 := FALSE;
1140 plsql_tmp_2 := enrp_gpa_sort(plsql_tmp_2,p_surname_alpha);
1141 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1142 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1143 startcnt_2 := startcnt_2 +1;
1144 END LOOP;
1145 END IF;
1146 END IF;
1147 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1148 plsql_3(startcnt_1) := plsql_tmp_1(j);
1149 startcnt_1 := startcnt_1 +1;
1150 END LOOP;
1151 END LOOP;
1152 END IF;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156 END IF;
1157
1158 -- --End of CPC has more priority than GPA and ECP----------------------------------
1159
1160 -- --Begin of ECP has more priority than GPA and CPC----------------------------------
1161
1162 IF ecp_ord < gpa_ord AND ecp_ord < cpc_ord THEN -- ECP has more priority than GPA and CPC
1163 plsql_3 := enrp_ecp_sort(p_plsql_2,p_surname_alpha);
1164 FOR i IN 1 .. plsql_3.COUNT LOOP
1165 IF i < plsql_3.COUNT AND plsql_3(i).ecp = plsql_3(i+1).ecp AND NOT started_1 THEN
1166 started_1 := TRUE;
1167 startcnt_1 := i;
1168 cnt_1 := 0;
1169 plsql_tmp_1 := plsql_empty; -- assigning empty plsql table to erase the content
1170 END IF;
1171 IF started_1 THEN
1172 cnt_1 := cnt_1 +1 ;
1173 plsql_tmp_1(cnt_1) := plsql_3(i);
1174 IF i = plsql_3.COUNT OR plsql_3(i).ecp <> plsql_3(i+1).ecp THEN
1175 started_1 := FALSE;
1176 IF gpa_ord < cpc_ord THEN -- GPA has more priority than CPC
1177 plsql_tmp_1 := enrp_gpa_sort(plsql_tmp_1,p_surname_alpha);
1178 started_2 := FALSE;
1179 startcnt_2 := 1;
1180 cnt_2 := 0 ;
1181 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1182 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).gpa = plsql_tmp_1(j+1).gpa AND
1183 NOT started_2 THEN
1184 started_2 := TRUE;
1185 startcnt_2 := j;
1186 cnt_2 := 0;
1187 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1188 END IF;
1189 IF started_2 THEN
1190 cnt_2 := cnt_2 +1 ;
1191 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1192 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).gpa <> plsql_tmp_1(j+1).gpa THEN
1193 started_2 := FALSE;
1194 plsql_tmp_2 := enrp_cpc_sort(plsql_tmp_2,p_surname_alpha);
1195 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1196 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1197 startcnt_2 := startcnt_2 +1;
1198 END LOOP;
1199 END IF;
1200 END IF;
1201 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1202 plsql_3(startcnt_1) := plsql_tmp_1(j);
1203 startcnt_1 := startcnt_1 +1;
1204 END LOOP;
1205 END LOOP;
1206 ELSIF cpc_ord < gpa_ord THEN -- CPC has more priority than GPA
1207 plsql_tmp_1 := enrp_cpc_sort(plsql_tmp_1,p_surname_alpha);
1208 started_2 := FALSE;
1209 startcnt_2 := 1;
1210 cnt_2 := 0 ;
1211 FOR j IN 1 .. plsql_tmp_1.COUNT LOOP
1212 IF j < plsql_tmp_1.COUNT AND plsql_tmp_1(j).cpc = plsql_tmp_1(j+1).cpc AND
1213 NOT started_2 THEN
1214 started_2 := TRUE;
1215 startcnt_2 := j;
1216 cnt_2 := 0;
1217 plsql_tmp_2 := plsql_empty; -- assigning empty plsql table to erase the content
1218 END IF;
1219 IF started_2 THEN
1220 cnt_2 := cnt_2 +1 ;
1221 plsql_tmp_2(cnt_2) := plsql_tmp_1(j);
1222 IF j = plsql_tmp_1.COUNT OR plsql_tmp_1(j).cpc <> plsql_tmp_1(j+1).cpc THEN
1223 started_2 := FALSE;
1224 plsql_tmp_2 := enrp_gpa_sort(plsql_tmp_2,p_surname_alpha);
1225 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1226 plsql_tmp_1(startcnt_2) := plsql_tmp_2(k);
1227 startcnt_2 := startcnt_2 +1;
1228 END LOOP;
1229 END IF;
1230 END IF;
1231 FOR k IN 1 .. plsql_tmp_2.COUNT LOOP
1232 plsql_3(startcnt_1) := plsql_tmp_1(j);
1233 startcnt_1 := startcnt_1 +1;
1234 END LOOP;
1235 END LOOP;
1236 END IF;
1237 END IF;
1238 END IF;
1239 END LOOP;
1240 END IF;
1241
1242 -- --End of ECP has more priority than GPA and CPC----------------------------------
1243
1244 -- --Begin of GPA has more priority than CPC------------------------------------------
1245
1246 ELSIF gpa_ord <= p_pointer AND gpa_ord <> 0 AND
1247 cpc_ord <= p_pointer AND cpc_ord <> 0 AND
1248 ecp_ord = 0 THEN
1249 IF gpa_ord < cpc_ord THEN -- GPA has more priority than CPC
1250 plsql_3 := enrp_gpa_sort(p_plsql_2,p_surname_alpha);
1251 FOR i IN 1 .. plsql_3.COUNT LOOP
1252 IF i < plsql_3.COUNT AND plsql_3(i).gpa = plsql_3(i+1).gpa AND NOT started THEN
1253 started := TRUE;
1254 startcnt := i;
1255 cnt := 0;
1256 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1257 END IF;
1258 IF started THEN
1259 cnt := cnt +1 ;
1260 plsql_tmp(cnt) := plsql_3(i);
1261 IF i = plsql_3.COUNT OR plsql_3(i).gpa <> plsql_3(i+1).gpa THEN
1262 started := FALSE;
1263 plsql_tmp := enrp_cpc_sort(plsql_tmp,p_surname_alpha);
1264 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1265 plsql_3(startcnt) := plsql_tmp(j);
1266 startcnt := startcnt +1;
1267 END LOOP;
1268 END IF;
1269 END IF;
1270 END LOOP;
1271 ELSE -- CPC has more priority than GPA
1272 plsql_3 := enrp_cpc_sort(p_plsql_2,p_surname_alpha);
1273 FOR i IN 1 .. plsql_3.COUNT LOOP
1274 IF i < plsql_3.COUNT AND plsql_3(i).cpc = plsql_3(i+1).cpc AND NOT started THEN
1275 started := TRUE;
1276 startcnt := i;
1277 cnt := 0;
1278 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1279 END IF;
1280 IF started THEN
1281 cnt := cnt +1 ;
1282 plsql_tmp(cnt) := plsql_3(i);
1283 IF i = plsql_3.COUNT OR plsql_3(i).cpc <> plsql_3(i+1).cpc THEN
1284 started := FALSE;
1285 plsql_tmp := enrp_gpa_sort(plsql_tmp,p_surname_alpha);
1286 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1287 plsql_3(startcnt) := plsql_tmp(j);
1288 startcnt := startcnt +1;
1289 END LOOP;
1290 END IF;
1291 END IF;
1292 END LOOP;
1293 END IF;
1294
1295 -- --End of GPA has more priority than CPC------------------------------------------
1296
1297 -- --Begin of GPA has more priority than ECP------------------------------------------
1298
1299 ELSIF gpa_ord <= p_pointer AND gpa_ord <> 0 AND
1300 ecp_ord <= p_pointer AND ecp_ord <> 0 AND
1301 cpc_ord = 0 THEN
1302 IF gpa_ord < ecp_ord THEN -- GPA has more priority than ECP
1303 plsql_3 := enrp_gpa_sort(p_plsql_2,p_surname_alpha);
1304 FOR i IN 1 .. plsql_3.COUNT LOOP
1305 IF i < plsql_3.COUNT AND plsql_3(i).gpa = plsql_3(i+1).gpa AND NOT started THEN
1306 started := TRUE;
1307 startcnt := i;
1308 cnt := 0;
1309 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1310 END IF;
1311 IF started THEN
1312 cnt := cnt +1 ;
1313 plsql_tmp(cnt) := plsql_3(i);
1314 IF i = plsql_3.COUNT OR plsql_3(i).gpa <> plsql_3(i+1).gpa THEN
1315 started := FALSE;
1316 plsql_tmp := enrp_ecp_sort(plsql_tmp,p_surname_alpha);
1317 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1318 plsql_3(startcnt) := plsql_tmp(j);
1319 startcnt := startcnt +1;
1320 END LOOP;
1321 END IF;
1322 END IF;
1323 END LOOP;
1324 ELSE -- ECP has more priority than GPA
1325 plsql_3 := enrp_ecp_sort(p_plsql_2,p_surname_alpha);
1326 FOR i IN 1 .. plsql_3.COUNT LOOP
1327 IF i < plsql_3.COUNT AND plsql_3(i).ecp = plsql_3(i+1).ecp AND NOT started THEN
1328 started := TRUE;
1329 startcnt := i;
1330 cnt := 0;
1331 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1332 END IF;
1333 IF started THEN
1334 cnt := cnt +1 ;
1335 plsql_tmp(cnt) := plsql_3(i);
1336 IF i = plsql_3.COUNT OR plsql_3(i).ecp <> plsql_3(i+1).ecp THEN
1337 started := FALSE;
1338 plsql_tmp := enrp_gpa_sort(plsql_tmp,p_surname_alpha);
1339 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1340 plsql_3(startcnt) := plsql_tmp(j);
1341 startcnt := startcnt +1;
1342 END LOOP;
1343 END IF;
1344 END IF;
1345 END LOOP;
1346 END IF;
1347
1348 -- --End of GPA has more priority than ECP------------------------------------------
1349
1350 -- --Begin of CPC has more priority than ECP------------------------------------------
1351
1352 ELSIF cpc_ord <= p_pointer AND cpc_ord <> 0 AND
1353 ecp_ord <= p_pointer AND ecp_ord <> 0 AND
1354 gpa_ord = 0 THEN
1355 IF cpc_ord < ecp_ord THEN -- CPC has more priority than ECP
1356 plsql_3 := enrp_cpc_sort(p_plsql_2,p_surname_alpha);
1357 FOR i IN 1 .. plsql_3.COUNT LOOP
1358 IF i < plsql_3.COUNT AND plsql_3(i).cpc = plsql_3(i+1).cpc AND NOT started THEN
1359 started := TRUE;
1360 startcnt := i;
1361 cnt := 0;
1362 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1363 END IF;
1364 IF started THEN
1365 cnt := cnt +1 ;
1366 plsql_tmp(cnt) := plsql_3(i);
1367 IF i = plsql_3.COUNT OR plsql_3(i).cpc <> plsql_3(i+1).cpc THEN
1368 started := FALSE;
1369 plsql_tmp := enrp_ecp_sort(plsql_tmp,p_surname_alpha);
1370 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1371 plsql_3(startcnt) := plsql_tmp(j);
1372 startcnt := startcnt +1;
1373 END LOOP;
1374 END IF;
1375 END IF;
1376 END LOOP;
1377 ELSE -- ECP has more priority than CPC
1378 plsql_3 := enrp_ecp_sort(p_plsql_2,p_surname_alpha);
1379 FOR i IN 1 .. plsql_3.COUNT LOOP
1380 IF i < plsql_3.COUNT AND plsql_3(i).ecp = plsql_3(i+1).ecp AND NOT started THEN
1381 started := TRUE;
1382 startcnt := i;
1383 cnt := 0;
1384 plsql_tmp := plsql_empty; -- assigning empty plsql table to erase the content
1385 END IF;
1386 IF started THEN
1387 cnt := cnt +1 ;
1388 plsql_tmp(cnt) := plsql_3(i);
1389 IF i = plsql_3.COUNT OR plsql_3(i).ecp <> plsql_3(i+1).ecp THEN
1390 started := FALSE;
1391 plsql_tmp := enrp_cpc_sort(plsql_tmp,p_surname_alpha);
1392 FOR j IN 1 .. plsql_tmp.COUNT LOOP
1393 plsql_3(startcnt) := plsql_tmp(j);
1394 startcnt := startcnt +1;
1395 END LOOP;
1396 END IF;
1397 END IF;
1398 END LOOP;
1399 END IF;
1400
1401 -- --End of CPC has more priority than ECP------------------------------------------
1402
1403 -- having only GPA in the set of priorities
1404 ELSIF gpa_ord <= p_pointer AND gpa_ord <> 0 AND
1405 cpc_ord = 0 AND ecp_ord = 0 THEN
1406 plsql_3 := enrp_gpa_sort(p_plsql_2,p_surname_alpha);
1407
1408 -- having only CPC in the set of priorities
1409 ELSIF cpc_ord <= p_pointer AND cpc_ord <> 0 AND
1410 gpa_ord = 0 AND ecp_ord = 0 THEN
1411 plsql_3 := enrp_cpc_sort(p_plsql_2,p_surname_alpha);
1412
1413 -- having only ECP in the set of priorities
1414 ELSIF ecp_ord <= p_pointer AND ecp_ord <> 0 AND
1415 gpa_ord = 0 AND cpc_ord = 0 THEN
1416 plsql_3 := enrp_ecp_sort(p_plsql_2,p_surname_alpha);
1417 ELSE
1418 plsql_3 := enrp_alpha_sort(p_plsql_2,p_surname_alpha);
1419 END IF;
1420
1421 FOR i IN 1 .. plsql_3.COUNT LOOP
1422 cnt4 := cnt4 + 1; -- cnt4 variable is declared in package spec.
1423 plsql_4(cnt4).person_id := plsql_3(i).person_id;
1424 plsql_4(cnt4).last_name := plsql_3(i).last_name;
1425 END LOOP;
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1430 FND_MESSAGE.SET_TOKEN('NAME','Igs_en_timeslots.enrp_sort_mngt');
1431 IGS_GE_MSG_STACK.ADD;
1432 App_Exception.Raise_Exception;
1433 END enrp_sort_mngt;
1434
1435 ---------------------------------------------------
1436 PROCEDURE enrp_recur_sort(
1437 /*************************************************************
1438 Created By : sraj
1439 Date Created By : 2000/13/05
1440 Purpose : To set the column values before inserting.
1441 Know limitations, enhancements or remarks
1442 Change History
1443 Who When What
1444 KNAG.IN 12-APR-2001 Included enrollment credit point
1445 priority in timeslot allocation
1446 as per enh bug 1710227
1447 (reverse chronological order - newest change first)
1448 Nishikant 23JUL2002 Bug#2443771. The cursor cur_max_prty_ord and cur_prty got modified.
1449 Also the value assigned to the variabke max_prty_ord from the cursor
1450 cur_max_prty_ord before using it down the line.
1451 svanukur 08jul2003 checking for NVL of max_prty_ord as part of bug 3039661
1452 ***************************************************************/
1453
1454 p_plsql_2 IN OUT NOCOPY plsql_table_1,
1455 p_surname_alpha IN VARCHAR2,
1456 p_ts_stup_id IN NUMBER,
1457 p_pointer IN NUMBER
1458 ) IS
1459
1460 CURSOR cur_max_prty_ord IS
1461 SELECT MAX(priority_order)
1462 FROM igs_en_timeslot_prty prt
1463 WHERE prt.igs_en_timeslot_stup_id = p_ts_stup_id; -- Added the where condition by Nishikant - 23JUL2002 - bug#2443771
1464
1465 CURSOR cur_prty_pref(p_IGS_EN_TIMESLOT_STUP_ID NUMBER,p_order NUMBER) IS
1466 SELECT prt.priority_value,prf.preference_code,prf.preference_version,prf.sequence_number
1467 FROM igs_en_timeslot_prty prt,
1468 igs_en_timeslot_pref prf
1469 WHERE prt.IGS_EN_TIMESLOT_STUP_ID = p_IGS_EN_TIMESLOT_STUP_ID AND
1470 prt.priority_order = p_order AND
1471 prt.igs_en_timeslot_prty_id = prf.igs_en_timeslot_prty_id
1472 order by prt.priority_order, prf.preference_order;
1473
1474 CURSOR cur_prty(p_order NUMBER) IS
1475 SELECT priority_value
1476 FROM igs_en_timeslot_prty prt
1477 WHERE priority_order = p_order
1478 AND prt.igs_en_timeslot_stup_id = p_ts_stup_id; -- Added the AND condition by Nishikant - 23JUL2002 - bug#2443771
1479
1480 plsql_3 plsql_table_1;
1481 cnt NUMBER := 0;
1482 prty_value igs_en_timeslot_prty.priority_value%TYPE;
1483 max_prty_ord igs_en_timeslot_prty.priority_order%TYPE;
1484 BEGIN
1485 OPEN cur_max_prty_ord; -- Code added by Nishikant - 23JUL2002 - bug#2443771
1486 FETCH cur_max_prty_ord INTO max_prty_ord; -- Fetching to the variable max_prty_ord
1487 CLOSE cur_max_prty_ord; -- Before using it down the line.
1488
1489 FOR cur_prty_pref_rec_2 IN cur_prty_pref(p_ts_stup_id,p_pointer) LOOP
1490 -- All the students are already moved into plsql_4 table( Result Table)
1491 IF p_plsql_2.COUNT = 0 THEN
1492 EXIT;
1493 END IF;
1494 plsql_3 := enrp_cur_criteria(
1495 p_plsql_2,
1496 cur_prty_pref_rec_2.priority_value,
1497 cur_prty_pref_rec_2.preference_code,
1498 cur_prty_pref_rec_2.preference_version,
1499 cur_prty_pref_rec_2.sequence_number,
1500 p_pointer,
1501 p_ts_stup_id);
1502 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_PR_PF_PV');
1503 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||cur_prty_pref_rec_2.priority_value
1504 ||':'||cur_prty_pref_rec_2.preference_code||':'||cur_prty_pref_rec_2.preference_version
1505 ||':'||cur_prty_pref_rec_2.sequence_number);
1506 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_STD_SAT_PR');
1507 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||plsql_3.COUNT);
1508
1509 IF p_pointer = nvl(max_prty_ord,0) THEN
1510 enrp_sort_mngt(plsql_3,p_surname_alpha,p_pointer);
1511 plsql_3 := plsql_empty;
1512 ELSE
1513 -- call to recursive function to sort on further priorities
1514 enrp_recur_sort(plsql_3,p_surname_alpha,p_ts_stup_id,p_pointer+1);
1515 plsql_3 := plsql_empty;
1516 END IF;
1517 END LOOP; -- cur_prty_pref_rec_2
1518 -- To handle the case where GPA/CPC/ECP is the last priority,so for the remaining records apply only Alphabetic sort.
1519 -- resetting of these values will be useful in function enrp_sort_mngt
1520 OPEN cur_prty(p_pointer);
1521 FETCH cur_prty INTO prty_value;
1522 IF prty_value = 'GPA' THEN
1523 gpa_ord := 0;
1524 ELSIF prty_value = 'TOTAL_CP' THEN
1525 cpc_ord := 0;
1526 ELSIF prty_value = 'ENRCP_ASC' OR prty_value = 'ENRCP_DESC' THEN
1527 ecp_ord := 0;
1528 END IF;
1529 CLOSE cur_prty;
1530 -- sort the remaining students in p_plsql_2 and insert them into plsql_4
1531 IF p_plsql_2.COUNT > 0 THEN
1532 enrp_sort_mngt(p_plsql_2,p_surname_alpha,p_pointer);
1533 END IF;
1534
1535 END enrp_recur_sort;
1536
1537 --------------------------------------------------------------------------
1538 PROCEDURE enrp_rslt_ins_timeslot(
1539 /*************************************************************
1540 Created By : sraj
1541 Date Created By : 2000/13/05
1542 Purpose : To set the column values before inserting.
1543 Know limitations, enhancements or remarks
1544 Change History
1545 Who When What
1546 (reverse chronological order - newest change first)
1547 ***************************************************************/
1548
1549 p_plsql_4 IN plsql_table_2,
1550 p_plsql_5 IN plsql_table_3,
1551 p_max_head_count IN NUMBER,
1552 p_igs_en_timeslot_para_id IN NUMBER,
1553 p_length_of_time IN NUMBER) AS
1554
1555 cnt_4 NUMBER := 0;
1556 l_rowid VARCHAR2(25);
1557 l_igs_en_timeslot_rslt_id VARCHAR2(25);
1558
1559 BEGIN
1560 -- if the length of time is unlimited
1561 IF p_length_of_time = 0 THEN
1562 FOR i IN 1 .. plsql_4.COUNT LOOP
1563 l_rowid := null;
1564 Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
1565 x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
1566 x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
1567 x_person_id => p_plsql_4(i).person_id,
1568 x_start_dt_time => p_plsql_5(1).start_dt_time,
1569 x_end_dt_time => p_plsql_5(1).end_dt_time,
1570 x_mode => 'R');
1571 END LOOP;
1572 ELSE
1573 FOR i IN 1 .. p_plsql_5.COUNT LOOP
1574 FOR j IN 1 .. p_max_head_count LOOP
1575 cnt_4 := cnt_4 +1;
1576 l_rowid := null;
1577 Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
1578 x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
1579 x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
1580 x_person_id => p_plsql_4(cnt_4).person_id,
1581 x_start_dt_time => p_plsql_5(i).start_dt_time,
1582 x_end_dt_time => p_plsql_5(i).end_dt_time,
1583 x_mode => 'R');
1584 IF cnt_4 >= p_plsql_4.COUNT THEN
1585 EXIT;
1586 END IF;
1587 END LOOP; -- j
1588 IF cnt_4 >= p_plsql_4.COUNT THEN
1589 EXIT;
1590 END IF;
1591 END LOOP; -- i
1592 -- if unassigned students are there, then inserting into result table with NULL start And end date_time
1593 IF cnt_4 < plsql_4.COUNT THEN
1594 FOR i IN cnt_4 + 1 .. plsql_4.COUNT LOOP
1595 l_rowid := null;
1596 Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
1597 x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
1598 x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
1599 x_person_id => p_plsql_4(i).person_id,
1600 x_start_dt_time => NULL,
1601 x_end_dt_time => NULL,
1602 x_mode => 'R');
1603 END LOOP;
1604 END IF; -- cnt_4
1605 END IF;--p_length_of_time = 0
1606
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1610 FND_MESSAGE.SET_TOKEN('NAME','Igs_en_timeslots.enrp_rslt_ins_timeslot');
1611 IGS_GE_MSG_STACK.ADD;
1612 App_Exception.Raise_Exception;
1613 END enrp_rslt_ins_timeslot;
1614
1615 ---------------------------------------------------------------------------
1616 PROCEDURE enrp_assign_timeslot(
1617 /*************************************************************
1618 Created By : sraj
1619 Date Created By : 2000/13/05
1620 Purpose : To set the column values before inserting.
1621 Know limitations, enhancements or remarks
1622 Change History
1623 Who When What
1624 KNAG.IN 12-APR-2001 Included enrollment credit point
1625 priority in timeslot allocation
1626 as per enh bug 1710227
1627 (reverse chronological order - newest change first)
1628 Nishikant 23JUL2002 Bug#2443771. The cursor cur_max_prty_ord got modified to include the where condition.
1629 The cursor cur_assign_ran_alpha got modified to select surname_alphabet also. The cursor cur_alpha
1630 which was existing for selecting the surname_alphabet got removed.
1631 svanukur 08jul2003 modified the check for variable max_prty_ord ,if it is null instead of
1632 checking if the cur_max_prty_ord cursor is not found since an aggregate function is used.
1633 as part of bug 3039661
1634 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL); as a prt of bug#4958173.
1635 ***************************************************************/
1636
1637 ERRBUF OUT NOCOPY VARCHAR2,
1638 RETCODE OUT NOCOPY NUMBER,
1639 p_prg_type_gr_cd IN VARCHAR2,
1640 p_cal_type IN VARCHAR2,
1641 p_seq_num IN NUMBER,
1642 p_stud_type IN VARCHAR2,
1643 p_timeslot IN VARCHAR2,
1644 p_start_date IN DATE,
1645 p_end_date IN DATE,
1646 p_max_headcount IN NUMBER,
1647 p_length_of_time IN NUMBER,
1648 p1_start_time IN VARCHAR2,
1649 p1_end_time IN VARCHAR2,
1650 p_mode IN VARCHAR2,
1651 p_orgid IN NUMBER
1652 ) AS
1653
1654 CURSOR cur_max_prty_ord(p_igs_en_timeslot_stup_id NUMBER) IS -- Added the parameters of the cursor by Nishikant - 23JUL2002 - bug#2443771
1655 SELECT MAX(priority_order)
1656 FROM igs_en_timeslot_prty prt
1657 WHERE prt.igs_en_timeslot_stup_id = p_igs_en_timeslot_stup_id; -- Added the where condition by Nishikant - 23JUL2002 - bug#2443771
1658
1659 CURSOR cur_ts_stup_id(p_prg_type_gr_cd VARCHAR2, p_cal_type VARCHAR2, p_seq_num NUMBER, p_stud_type VARCHAR2) IS
1660 SELECT ets.IGS_EN_TIMESLOT_STUP_ID
1661 FROM igs_en_timeslot_stup ets
1662 WHERE ets.PROGRAM_TYPE_GROUP_CD = p_prg_type_gr_cd AND
1663 ets.CAL_TYPE = p_cal_type AND
1664 ets.SEQUENCE_NUMBER = p_seq_num AND
1665 ets.STUDENT_TYPE = p_stud_type;
1666
1667 CURSOR cur_prty_pref(p_IGS_EN_TIMESLOT_STUP_ID NUMBER,p_order NUMBER) IS
1668 SELECT prt.priority_value,prf.preference_code,prf.preference_version,prf.sequence_number
1669 FROM igs_en_timeslot_prty prt,
1670 igs_en_timeslot_pref prf
1671 WHERE prt.IGS_EN_TIMESLOT_STUP_ID = p_IGS_EN_TIMESLOT_STUP_ID AND
1672 prt.priority_order = p_order AND
1673 prt.igs_en_timeslot_prty_id = prf.igs_en_timeslot_prty_id
1674 order by prf.preference_order;
1675
1676 CURSOR cur_assign_ran_alpha IS
1677 SELECT ets.ASSIGN_RANDOMLY, ets.SURNAME_ALPHABET
1678 FROM igs_en_timeslot_stup ets
1679 WHERE ets.PROGRAM_TYPE_GROUP_CD = p_prg_type_gr_cd AND
1680 ets.CAL_TYPE = p_cal_type AND
1681 ets.SEQUENCE_NUMBER = p_seq_num AND
1682 ets.STUDENT_TYPE = p_stud_type;
1683
1684 CURSOR cur_mode_check IS
1685 SELECT etp.ROWID,etp.igs_en_timeslot_para_id,etp.ts_mode
1686 FROM igs_en_timeslot_para etp
1687 WHERE etp.program_type_group_cd = p_prg_type_gr_cd AND
1688 etp.student_type = p_stud_type AND
1689 etp.cal_type = p_cal_type AND
1690 etp.sequence_number = p_seq_num AND
1691 etp.timeslot_calendar = p_timeslot;
1692
1693 CURSOR cur_rslt(p_igs_en_timeslot_para_id igs_en_timeslot_para.igs_en_timeslot_para_id%TYPE) IS
1694 SELECT tsr.ROWID row_id
1695 FROM Igs_En_Timeslot_Rslt tsr
1696 WHERE tsr.igs_en_timeslot_para_id = p_igs_en_timeslot_para_id;
1697
1698 p_start_time DATE ;
1699 p_end_time DATE ;
1700 l_ts_para_id igs_en_timeslot_para.igs_en_timeslot_para_id%TYPE;
1701 l_ts_stup_id igs_en_timeslot_stup.IGS_EN_TIMESLOT_STUP_ID%TYPE;
1702 surname_alpha igs_en_timeslot_stup.SURNAME_ALPHABET%TYPE;
1703 assign_random igs_en_timeslot_stup.ASSIGN_RANDOMLY%TYPE;
1704 max_prty_ord igs_en_timeslot_prty.priority_order%TYPE;
1705
1706 plsql_1 plsql_table_1 ;
1707 plsql_2 plsql_table_1;
1708 plsql_5 plsql_table_3;
1709 l_rowid VARCHAR2(25);
1710 l_igs_en_timeslot_para_id NUMBER;
1711 l_ts_mode varchar2(1);
1712 BEGIN
1713
1714 RETCODE :=0;
1715 igs_ge_gen_003.set_org_id(NULL);
1716
1717 p_start_time := TO_DATE(p1_start_time,'DD/MM/YYYY HH24:MI');
1718 p_end_time := TO_DATE(p1_end_time,'DD/MM/YYYY HH24:MI');
1719
1720 -- getting the FK Value of setup table
1721 OPEN cur_ts_stup_id(p_prg_type_gr_cd ,p_cal_type ,p_seq_num,p_stud_type ) ;
1722 FETCH cur_ts_stup_id INTO l_ts_stup_id;
1723 CLOSE cur_ts_stup_id ;
1724
1725 plsql_1 := enrp_total_students(p_prg_type_gr_cd ,p_stud_type ,p_cal_type ,p_seq_num);
1726
1727 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_STD_SAT_CRI');
1728 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||plsql_1.COUNT);
1729 OPEN cur_assign_ran_alpha;
1730 FETCH cur_assign_ran_alpha INTO assign_random, surname_alpha; -- Assigned here to surname_alpha also by Nishikant - 23JUL2002 - bug#2443771
1731 CLOSE cur_assign_ran_alpha;
1732
1733 -- If assigning the timeslots randomly/if time is unlimited then no need to sort
1734 IF p_length_of_time = 0 OR assign_random = 'Y' THEN
1735 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_ASSGN_RAN');
1736 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET);
1737 FOR i IN 1 .. plsql_1.COUNT LOOP
1738 cnt4 := cnt4 + 1; -- cnt4 variable is declared in package spec.
1739 plsql_4(cnt4).person_id := plsql_1(i).person_id;
1740 plsql_4(cnt4).last_name := plsql_1(i).last_name;
1741 END LOOP;
1742 ELSE
1743 OPEN cur_max_prty_ord(l_ts_stup_id);
1744 FETCH cur_max_prty_ord INTO max_prty_ord;
1745 -- No Priorities were defined
1746 IF max_prty_ord IS NULL THEN
1747 max_prty_ord :=0;
1748 END IF;
1749 CLOSE cur_max_prty_ord;
1750 FOR i IN 1 .. max_prty_ord LOOP
1751 -- All the students are already moved into plsql_4 table( Result Table)
1752 IF plsql_1.COUNT = 0 THEN
1753 EXIT;
1754 END IF;
1755 FOR cur_prty_pref_rec_1 IN cur_prty_pref(l_ts_stup_id,i) LOOP
1756 -- All the students are already moved into plsql_4 table( Result Table)
1757 IF plsql_1.COUNT = 0 THEN
1758 EXIT;
1759 END IF;
1760 plsql_2 := enrp_cur_criteria(
1761 plsql_1,
1762 cur_prty_pref_rec_1.priority_value,
1763 cur_prty_pref_rec_1.preference_code,
1764 cur_prty_pref_rec_1.preference_version,
1765 cur_prty_pref_rec_1.sequence_number,
1766 i,
1767 l_ts_stup_id);
1768 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_PR_PF_PV');
1769 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||cur_prty_pref_rec_1.priority_value
1770 ||':'||cur_prty_pref_rec_1.preference_code||':'||cur_prty_pref_rec_1.preference_version
1771 ||':'||cur_prty_pref_rec_1.sequence_number);
1772 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_STD_SAT_PR');
1773 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||plsql_2.COUNT);
1774 IF i < max_prty_ord THEN
1775 -- call to recursive function to sort according to priority And preferences
1776 enrp_recur_sort(plsql_2,surname_alpha,l_ts_stup_id,i+1);
1777 ELSE
1778 -- call the function, which sorts the records with pointer = i and insert into plsql_4
1779 enrp_sort_mngt(plsql_2,surname_alpha,i);
1780 END IF;
1781 END LOOP; -- cur_prty_pref_rec_1
1782 END LOOP; -- max_prty_ord
1783 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_STD_NOT_SAT');
1784 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||plsql_1.COUNT);
1785 -- sort the remaining students in plsql_1 and insert them into plsql_4
1786 plsql_2 := enrp_alpha_sort(plsql_1,surname_alpha);
1787 FOR i IN 1 .. plsql_2.COUNT LOOP
1788 cnt4 := cnt4 + 1; -- cnt4 variable is declared in package spec.
1789 plsql_4(cnt4).person_id := plsql_2(i).person_id;
1790 plsql_4(cnt4).last_name := plsql_2(i).last_name;
1791 END LOOP;
1792 END IF; -- p_length_of_time
1793
1794 -- calculate the number of Tim slot sessions in the given Timeslot with the given "length of time"
1795 plsql_5 := enrp_calc_slots(p_start_time,
1796 p_end_time,
1797 p_start_date,
1798 p_end_date,
1799 p_length_of_time);
1800 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_TOT_TS_SES');
1801 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||plsql_5.COUNT );
1802
1803 -- If the same Criteria has run in Trial Mode, then delete the existing records and insert the new set of data
1804 OPEN cur_mode_check;
1805 FETCH cur_mode_check INTO l_rowid ,l_ts_para_id , l_ts_mode;
1806 IF cur_mode_check%FOUND THEN
1807 IF l_ts_mode = 'T' THEN
1808 FOR cur_rslt_rec IN cur_rslt(l_ts_para_id) LOOP
1809 Igs_En_Timeslot_Rslt_Pkg.delete_row(x_rowid => cur_rslt_rec.row_id);
1810 END LOOP;
1811 Igs_En_Timeslot_Para_Pkg.delete_row(x_rowid => l_rowid);
1812 ELSIF l_ts_mode = 'F' THEN
1813 fnd_message.set_name('IGS','IGS_EN_TS_CRI_FINAL_AL');
1814 IGS_GE_MSG_STACK.ADD;
1815 App_Exception.Raise_Exception;
1816 END IF;
1817 END IF;
1818 CLOSE cur_mode_check;
1819
1820 -- insertion of parameters into Parameter Table
1821 Igs_En_Timeslot_Para_Pkg.insert_row(x_rowid => l_rowid,
1822 x_igs_en_timeslot_para_id => l_igs_en_timeslot_para_id,
1823 x_program_type_group_cd => p_prg_type_gr_cd,
1824 x_cal_type => p_cal_type,
1825 x_sequence_number => p_seq_num,
1826 x_student_type => p_stud_type,
1827 x_timeslot_calendar => p_timeslot,
1828 x_timeslot_st_time => p_start_time,
1829 x_timeslot_end_time => p_end_time,
1830 x_ts_mode => p_mode,
1831 x_max_head_count => p_max_headcount,
1832 x_length_of_time => p_length_of_time,
1833 x_mode => 'R',
1834 x_org_id => p_orgid);
1835
1836 -- inserting results of timeslot assignment into results Table
1837 enrp_rslt_ins_timeslot(plsql_4,plsql_5,p_max_headcount,l_igs_en_timeslot_para_id,p_length_of_time);
1838
1839 Fnd_Message.SET_NAME ('IGS','IGS_EN_TS_COMP_SUSS');
1840 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET);
1841 plsql_4 := plsql_empty_4;
1842 cnt4:=0;
1843
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 retcode:=2;
1847 Fnd_File.PUT_LINE(Fnd_File.LOG,'Error due to :'||sqlerrm);
1848 ERRBUF := Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1849 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1850 END enrp_assign_timeslot;
1851
1852 --------------------------------------------------------------------------
1853
1854 FUNCTION acad_teach_rel_exist(
1855 /*************************************************************
1856 Created By : Nishikant
1857 Date Created By : 23JUL2002
1858 Purpose : Created while fix of the Bug#2443771. To check the relationship exists between
1859 the Academic Calendar Type and Teach Calendar Type passed as parameters.
1860 Know limitations, enhancements or remarks
1861 Change History
1862 Who When What
1863 (reverse chronological order - newest change first)
1864 ***************************************************************/
1865 p_acad_cal_type IN VARCHAR2,
1866 p_teach_cal_type IN VARCHAR2,
1867 p_teach_seq_num IN NUMBER)
1868 RETURN VARCHAR2 AS
1869 CURSOR cur_find_rel IS
1870 SELECT 'x' FROM igs_ca_inst_rel cir2
1871 WHERE cir2.sup_cal_type = p_acad_cal_type
1872 AND cir2.sub_cal_type = p_teach_cal_type
1873 AND cir2.sub_ci_sequence_number = p_teach_seq_num;
1874 l_dummy VARCHAR2(1);
1875 BEGIN
1876 OPEN cur_find_rel;
1877 FETCH cur_find_rel INTO l_dummy;
1878 CLOSE cur_find_rel;
1879 IF l_dummy = 'x' THEN
1880 RETURN 'TRUE';
1881 ELSE
1882 RETURN 'FALSE';
1883 END IF;
1884 END acad_teach_rel_exist;
1885
1886 FUNCTION enrp_total_students(
1887 /*************************************************************
1888 Created By : sraj
1889 Date Created By : 2000/13/05
1890 Purpose : To set the column values before inserting.
1891 Know limitations, enhancements or remarks
1892 Change History
1893 Who When What
1894 Nishikant 20DEC2002 Bug#2712493. The cursors cur_total_enrled_stdnts and cur_total_admted_stdnts got
1895 modified to select properly the students under 'Enrolled' and 'Admitted' category.
1896 Nishikant 23JUL2002 The cursor cur_total_admted_stdnts got modified to consider the students
1897 enrolled directly through the Student Enrollments form.
1898 rnirwani 13-Sep-2004 changed cursor cur_total_enrled_stdnts to not consider logically deleted records. Bug# 3885804
1899 smaddali 20-sep-2004 Modified for cursor cur_total_enrled_stdnts bug#3918075, to add outer join between intermissions and program attempts
1900 stutta 17-Feb-2006 Modified cursor cur_total_admted_stdnts for perf bug#5042384
1901 (reverse chronological order - newest change first)
1902 ***************************************************************/
1903
1904 p_prg_type_gr_cd IN VARCHAR2,
1905 p_stdnt_type IN VARCHAR2,
1906 p_cal_type IN VARCHAR2,
1907 p_seq_num IN NUMBER)
1908 RETURN plsql_table_1 AS
1909
1910 --Bug#2712493. The below cursor got modified to select students in "Enrolled" category as below
1911 --Entered through Student Enrollments, having program attempt of status ENROLLED.
1912 --And students having program status INACTIVE and have at least one unit attempt(of any status).
1913 --And students having program attempts of INTERMIT status where the end date of the intermission should
1914 -- be before the start date of the calendar instance provided.
1915 -- smaddali modified for bug#3918075, to move intermissions join to a subquery
1916 CURSOR cur_total_enrled_stdnts(p_start_dt DATE ) IS
1917 SELECT DISTINCT (pe.person_id) person_id ,pe.last_name
1918 FROM igs_en_stdnt_ps_att sca,
1919 igs_pe_person_base_v pe,
1920 igs_ps_ver pv,
1921 igs_ps_type pt
1922 WHERE pe.person_id = sca.person_id AND
1923 pv.course_cd = sca.course_cd AND
1924 pv.course_type = pt.course_type AND
1925 pt.course_type_group_cd = p_prg_type_gr_cd AND
1926 ( sca.course_attempt_status = 'ENROLLED'
1927 OR
1928 ( sca.course_attempt_status = 'INTERMIT' AND
1929 EXISTS (SELECT 'X' FROM igs_en_stdnt_ps_intm sci, igs_en_intm_types eit WHERE
1930 sci.end_dt < p_start_dt AND
1931 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
1932 sca.person_id = sci.person_id AND
1933 sca.course_cd = sci.course_cd AND
1934 sci.approved = eit.appr_reqd_ind AND
1935 eit.intermission_type = sci.intermission_type )
1936 )
1937 OR
1938 ( sca.course_attempt_status = 'INACTIVE'
1939 AND EXISTS ( SELECT 'X'
1940 FROM IGS_EN_SU_ATTEMPT
1941 WHERE person_id = sca.person_id
1942 AND course_cd = sca.course_cd
1943 )
1944 )
1945 );
1946
1947 --Bug#2712493. The Below cursor got modified to select students in "Admitted" category as below
1948 --1. Entered through Direct Admission, having program attempt of status INACTIVE or UNCONFIRM,
1949 -- where the application exists in application instance table and admission calendar is
1950 -- subordinate to the teaching calendar.
1951 --2. Entered through Student Enrollments, having program attempt of status INACTIVE and is not
1952 -- having any unit attempts.
1953 CURSOR cur_total_admted_stdnts(cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE, cp_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
1954
1955 SELECT sca.person_id person_id ,(select person_last_name from hz_parties where party_id = sca.person_id) last_name
1956 FROM IGS_EN_STDNT_PS_ATT_ALL sca,
1957 igs_ps_ver_all pv,
1958 igs_ps_type_all pt,
1959 IGS_AD_PS_APPL_INST_all acai,
1960 IGS_AD_APPL_all aav,
1961 IGS_CA_INST_REL cir
1962 WHERE pv.course_cd = sca.course_cd AND
1963 pv.version_number = sca.version_number AND
1964 sca.course_attempt_status IN ('INACTIVE','UNCONFIRM') AND
1965 pv.course_type = pt.course_type AND
1966 pt.course_type_group_cd = p_prg_type_gr_cd AND
1967 acai.person_id = sca.person_id AND
1968 sca.course_cd = acai.course_cd AND
1969 sca.version_number = acai.crv_version_number AND
1970 aav.person_id = acai.person_id AND
1971 aav.admission_appl_number = acai.admission_appl_number AND
1972 aav.adm_cal_type = cir.SUB_CAL_TYPE AND
1973 aav.adm_ci_sequence_number = cir.SUB_CI_SEQUENCE_NUMBER AND
1974 ((cir.sup_cal_type = cp_cal_type AND
1975 cir.sup_ci_sequence_number = cp_seq_num) OR
1976 (
1977 (cir.sup_cal_type,cir.sup_ci_sequence_number) IN
1978 ( SELECT teach_cal_type,teach_ci_sequence_number
1979 FROM igs_ca_load_to_teach_v
1980 WHERE load_cal_type = cp_cal_type
1981 AND load_ci_sequence_number = cp_seq_num
1982 )
1983 )
1984 )
1985
1986 UNION
1987 SELECT sca2.person_id person_id ,(select person_last_name from hz_parties where party_id = sca2.person_id) last_name
1988 FROM igs_ps_ver_all pv2,
1989 igs_ps_type_all pt2,
1990 IGS_EN_STDNT_PS_ATT_ALL sca2
1991 WHERE pt2.course_type_group_cd = p_prg_type_gr_cd AND
1992 pv2.course_type = pt2.course_type AND
1993 pv2.course_cd = sca2.course_cd AND
1994 pv2.version_number = sca2.version_number AND
1995 sca2.course_attempt_status = 'INACTIVE' AND
1996 NOT EXISTS
1997 (SELECT 'x' FROM igs_en_su_attempt_all sua
1998 WHERE sua.person_id = sca2.person_id AND
1999 sua.course_cd = sca2.course_cd AND
2000 sua.course_cd = pv2.course_cd) AND
2001 igs_en_timeslots.acad_teach_rel_exist(sca2.cal_type,cp_cal_type,cp_seq_num) = 'TRUE' ;
2002
2003
2004
2005
2006 CURSOR cur_cal_st_dt(p_cal_type VARCHAR2,p_seq_num NUMBER) IS
2007 SELECT start_dt
2008 FROM igs_ca_inst
2009 WHERE cal_type = p_cal_type AND
2010 sequence_number=p_seq_num;
2011
2012 start_date DATE;
2013 cnt NUMBER := 0;
2014 plsql_1 plsql_table_1;
2015
2016 BEGIN
2017 OPEN cur_cal_st_dt(p_cal_type ,p_seq_num);
2018 FETCH cur_cal_st_dt INTO start_date;
2019 CLOSE cur_cal_st_dt;
2020
2021 IF p_stdnt_type = 'ENROLLED' THEN
2022 -- insert all students who is ENROLLED and whose course_type_group_cd matches the given value
2023 FOR rec_cur_total_enrled_stdnts IN cur_total_enrled_stdnts(start_date) LOOP
2024 cnt := cnt + 1;
2025 plsql_1(cnt).person_id := rec_cur_total_enrled_stdnts.person_id ;
2026 plsql_1(cnt).last_name := rec_cur_total_enrled_stdnts.last_name ;
2027 END LOOP; -- cur_enrolled
2028 ELSIF p_stdnt_type = 'ADMITTED' THEN
2029 -- insert all students who is ADMITTED and whose admission commencement period is subordinate to the given teaching period
2030 --and whose course_type_group_cd matches the given value
2031 FOR rec_cur_total_admted_stdnts IN cur_total_admted_stdnts(p_cal_type,p_seq_num) LOOP
2032 cnt := cnt + 1;
2033 plsql_1(cnt).person_id := rec_cur_total_admted_stdnts.person_id ;
2034 plsql_1(cnt).last_name := rec_cur_total_admted_stdnts.last_name ;
2035 END LOOP; -- cur_total_admted_stdnts
2036 END IF;
2037 RETURN plsql_1;
2038 END enrp_total_students;
2039
2040 END Igs_En_Timeslots;
[Home] [Help]