1 PACKAGE BODY igs_ss_enr_details AS
2 /* $Header: IGSSS05B.pls 120.21 2006/04/12 23:02:49 snambaka ship $ */
3
4 g_tba_desc CONSTANT igs_lookup_values.meaning%TYPE := get_meaning('SCHEDULE_TYPE', 'TBA');
5 g_nsd_desc CONSTANT igs_lookup_values.meaning%TYPE := get_meaning('LEGACY_TOKENS','NO_SET_DAY');
6
7 FUNCTION get_location_bldg_room ( p_uoo_id IN NUMBER ) RETURN VARCHAR2
8 ------------------------------------------------------------------------------------
9 --Created by :
10 --Date created:
11 --
12 -- Purpose:
13 --
14 -- Known limitations/enhancements and/or remarks:
15 --
16 -- Change History:
17 -- Who When What
18 -- kkillams 15-04-2003 Modified c_igs_ps_usec_occurs cursor as part of performance bug 2749732
19 ------------------------------------------------------------------------------
20 IS
21 lv_location VARCHAR2(2000) ;
22 CURSOR c_igs_ps_usec_occurs(p_uoo_id IN NUMBER) IS
23 SELECT
24 uoo_id,
25 NVL(d.description,'-')||'<BR>'||NVL(b.description,'-')||'<BR>'||NVL(c.description,'-') location_description,
26 NVL(b.location_cd,'-')||'<BR>'||NVL(b.building_cd,'-')||'<BR>'||NVL(c.room_cd,'-') location_cd
27 FROM igs_ps_usec_occurs a,
28 igs_ad_building b,
29 igs_ad_room c,
30 igs_ad_location d
31 WHERE
32 a.building_code = b.building_id(+) AND
33 a.room_code = c.room_id(+) AND
34 b.location_cd = d.location_cd(+) AND
35 a.uoo_id = p_uoo_id
36 ORDER BY
37 b.location_cd,b.building_id,c.room_id;
38 BEGIN
39 FOR c_occurs_data IN c_igs_ps_usec_occurs(p_uoo_id)
40 LOOP
41 IF lv_location IS NOT NULL
42 THEN
43 lv_location := lv_location||'<BR><BR>'||c_occurs_data.location_description ;
44 ELSE
45 lv_location := c_occurs_data.location_description ;
46 END IF ;
47 END LOOP ;
48 RETURN lv_location ;
49 END get_location_bldg_room;
50
51
52 FUNCTION get_instructor_day_time(p_uoo_id IN NUMBER ) RETURN VARCHAR2 IS
53 ------------------------------------------------------------------------------------
54 --Created by :
55 --Date created:
56 --
57 -- Purpose:
58 --
59 -- Known limitations/enhancements and/or remarks:
60 --
61 -- Change History:
62 -- Who When What
63 -- kkillams 15-04-2003 Modified c_igs_ps_usec_occurs cursor as part of performance bug 2749732
64 ------------------------------------------------------------------------------
65 lv_instructor varchar2(2000);
66 CURSOR c_igs_ps_usec_occurs(p_uoo_id IN NUMBER)
67 IS
68 SELECT
69 uoo_id,
70 NVL(DECODE(a.monday, 'Y', 'Mon', NULL)||
71 DECODE(a.tuesday, 'Y', 'Tue', NULL)||
72 DECODE(a.wednesday, 'Y', 'Wed', NULL)||
73 DECODE(a.thursday, 'Y', 'Thu', NULL)||
74 DECODE(a.friday, 'Y', 'Fri', NULL)||
75 DECODE(a.saturday, 'Y', 'Sat', NULL)||
76 DECODE(a.sunday, 'Y', 'Sun', NULL),'-')||'<BR>'||
77 TO_CHAR(a.start_time, 'hh:miam')||'-'|| TO_CHAR(a.end_time, 'hh:miam')||'<BR>'||
78 LTRIM(f.person_last_name||', '||f.person_first_name||' '||f.person_middle_name) instructor_name
79 FROM igs_ps_usec_occurs a,
80 igs_ad_building b,
81 hz_parties f
82 WHERE
83 a.building_code = b.building_id(+) AND
84 a.instructor_id = f.party_id(+) AND
85 a.uoo_id = p_uoo_id ORDER BY
86 location_cd,building_id ,room_code;
87 BEGIN
88 FOR c_occurs_data IN c_igs_ps_usec_occurs(p_uoo_id)
89 LOOP
90 IF lv_instructor IS NOT NULL
91 THEN
92 lv_instructor := lv_instructor||'<BR><BR>'||c_occurs_data.instructor_name ;
93 ELSE
94 lv_instructor := c_occurs_data.instructor_name ;
95 END IF ;
96 END LOOP ;
97 RETURN lv_instructor ;
98 END get_instructor_day_time;
99
100 FUNCTION get_programs
101 (
102 p_person_id in number
103 ) return varchar2
104 is
105 cursor c_igs_ps_course(p_person_id in number)
106 is
107 select
108 a.course_attempt_status,
109 a.course_cd,
110 b.title,
111 a.course_cd||'-'||b.title program,
112 a.person_id
113 from igs_en_stdnt_ps_att a,igs_ps_ver b
114 where a.course_cd = b.course_cd
115 and a.version_number = b.version_number
116 and a.person_id = p_person_id
117 and nvl(a.course_attempt_status,' ') not in ('INACTIVE');
118 lv_program varchar2(2000) ;
119 begin
120 for c_igs_ps_course_data in c_igs_ps_course(p_person_id)
121 loop
122 if lv_program is not null
123 then
124 lv_program := lv_program ||','||c_igs_ps_course_data.program ;
125 else
126 lv_program := c_igs_ps_course_data.program ;
127 end if ;
128 end loop ;
129 return lv_program ;
130 end get_programs;
131 FUNCTION get_occur_desc_details
132 (p_uoo_id IN NUMBER) RETURN VARCHAR2
133 ------------------------------------------------------------------------------------
134 --Created by :
135 --Date created:
136 --
137 -- Purpose:
138 --
139 -- Known limitations/enhancements and/or remarks:
140 --
141 -- Change History:
142 -- Who When What
143 -- kkillams 15-04-2003 Modified c_igs_ps_usec_occurs cursor as part of performance bug 2749732
144 ------------------------------------------------------------------------------
145 is
146 lv_occurence_details varchar2(32000) ;
147 lv_location_details varchar2(32000) ;
148 CURSOR c_igs_ps_usec_occurs(p_uoo_id in number)
149 IS
150 SELECT
151 uoo_id,
152 DECODE(a.monday, 'Y', 'Mon', NULL)||
153 DECODE(a.tuesday, 'Y', 'Tue', NULL)||
154 DECODE(a.wednesday, 'Y', 'Wed', NULL)||
155 DECODE(a.thursday, 'Y', 'Thu', NULL)||
156 DECODE(a.friday, 'Y', 'Fri', NULL)||
157 DECODE(a.saturday, 'Y', 'Sat', NULL)||
158 DECODE(a.sunday, 'Y', 'Sun', NULL) CLASS_DAY,
159 TO_CHAR(a.start_time, 'hh:miam')||'-'||TO_CHAR(a.end_time, 'hh:miam') CLASS_TIME,
160 DECODE(a.monday, 'Y', 'Mon', NULL)||
161 DECODE(a.tuesday, 'Y', 'Tue', NULL)||
162 DECODE(a.wednesday, 'Y', 'Wed', NULL)||
163 DECODE(a.thursday, 'Y', 'Thu', NULL)||
164 DECODE(a.friday, 'Y', 'Fri', NULL)||
165 DECODE(a.saturday, 'Y', 'Sat', NULL)||
166 DECODE(a.sunday, 'Y', 'Sun', NULL)||' '||
167 TO_CHAR(a.start_time, 'hh:miam')||'-'|| TO_CHAR(a.end_time, 'hh:miam')||'<BR>'||
168 NVL(d.description,'-')||'<BR>'||
169 NVL(b.description,'-')||' '||
170 NVL(c.description,'')||'<BR>'||
171 LTRIM(f.person_last_name||', '||f.person_first_name||' '||f.person_middle_name) location
172 FROM igs_ps_usec_occurs a,
173 igs_ad_building b,
174 igs_ad_room c,
175 igs_ad_location d,
176 hz_parties f
177 WHERE
178 a.building_code = b.building_id(+) AND
179 a.room_code = c.room_id(+) AND
180 a.instructor_id = f.party_id(+) AND
181 b.location_cd = d.location_cd(+) AND
182 a.uoo_id = p_uoo_id
183 ORDER BY
184 class_day,class_time;
185 BEGIN
186 FOR c_occurs_data in c_igs_ps_usec_occurs(p_uoo_id)
187 LOOP
188 IF lv_location_details IS NOT NULL THEN
189 lv_location_details := lv_location_details ||'<BR><BR>'||c_occurs_data.location ;
190 ELSE
191 lv_location_details := c_occurs_data.location ;
192 END IF ;
193 END LOOP ;
194 lv_occurence_details := lv_location_details ;
195 RETURN lv_occurence_details ;
196 END get_occur_desc_details;
197 FUNCTION get_occur_cd_details(p_uoo_id IN NUMBER) RETURN VARCHAR2
198 ------------------------------------------------------------------------------------
199 --Created by :
200 --Date created:
201 --
202 -- Purpose: To return the meeting pattern details,
203 -- for a unit section
204 -- Known limitations/enhancements and/or remarks:
205 --
206 -- Change History:
207 -- Who When What
208 -- prgoyal 14-Oct-2001 Modifed the function to fetch the instructors from
209 -- table igs_ps_uso_instrctrs
210 -- kamohan 1/15/02 Modified for ENCR014
211 -- Added Start_date, End_date and TBA
212 -- kkillams 15-04-2003 Modified c_igs_ps_usec_occurs cursor as part of performance bug 2749732
213 ------------------------------------------------------------------------------
214 IS
215 lv_occurence_details VARCHAR2(32000) ;
216 lv_location_details VARCHAR2(32000) ;
217 CURSOR c_igs_ps_usec_occurs(p_uoo_id IN NUMBER)
218 IS SELECT
219 uoo_id,
220 unit_section_occurrence_id usec_id,
221 start_date,
222 end_date,
223 DECODE(a.monday, 'Y', 'Mon', NULL)
224 || DECODE(a.tuesday, 'Y', 'Tue', NULL)
225 || DECODE(a.wednesday, 'Y', 'Wed', NULL)
226 || DECODE(a.thursday, 'Y', 'Thu', NULL)
227 || DECODE(a.friday, 'Y', 'Fri', NULL)
228 || DECODE(a.saturday, 'Y', 'Sat', NULL)
229 || DECODE(a.sunday, 'Y', 'Sun', NULL) class_day,
230 TO_CHAR(a.start_time, 'hh:miam')||'-'||TO_CHAR(a.end_time, 'hh:miam') class_time,
231 DECODE(a.monday, 'Y', 'Mon', NULL)
232 || DECODE(a.tuesday, 'Y', 'Tue', NULL)
233 || DECODE(a.wednesday, 'Y', 'Wed', NULL)
234 || DECODE(a.thursday, 'Y', 'Thu', NULL)
235 || DECODE(a.friday, 'Y', 'Fri', NULL)
236 || DECODE(a.saturday, 'Y', 'Sat', NULL)
237 || DECODE(a.sunday, 'Y', 'Sun', NULL) ||' '
238 || TO_CHAR(a.start_time,'hh:miam')||'-'||TO_CHAR(a.end_time, 'hh:miam')
239 ||'<BR>'||NVL(b.location_cd,' ')||'<BR>'||NVL(b.building_cd,' ') ||' '||NVL(c.room_cd,' ') location
240 FROM igs_ps_usec_occurs a,
241 igs_ad_building b,
242 igs_ad_room c
243 WHERE
244 a.building_code = b.building_id(+) AND
245 a.room_code = c.room_id(+) AND
246 a.uoo_id = p_uoo_id
247 ORDER BY class_day,class_time;
248
249 cursor c_get_uso_instructor ( p_unit_section_occurence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE)
250 IS
251 SELECT
252 person_Last_name ||', '||Person_first_name||' '|| person_middle_name instructor_name
253 FROM
254 hz_parties hz,
255 igs_ps_uso_instrctrs usoi
256 WHERE
257 hz.party_id = usoi.instructor_id AND
258 usoi.unit_section_occurrence_id = p_unit_section_occurence_id;
259
260 lv_instructor VARCHAR2(4000);
261 cstMeetingTimes VARCHAR2(20);
262 l_announce BOOLEAN := FALSE;
263 l_display BOOLEAN := TRUE;
264 BEGIN
265
266 FOR c_occurs_data in c_igs_ps_usec_occurs(p_uoo_id) LOOP
267 IF c_occurs_data.start_date IS NULL AND c_occurs_data.end_date IS NULL THEN
268 l_announce := TRUE;
269 l_display := FALSE;
270 END IF;
271
272 IF l_display THEN
273
274 IF lv_location_details IS NOT NULL THEN
275 lv_location_details := lv_location_details ||'<BR>'||c_occurs_data.location ;
276 ELSE
277 lv_location_details := c_occurs_data.location ;
278 END IF ;
279
280 lv_instructor := NULL;
281 FOR r_get_uso_instructor IN c_get_uso_instructor(c_occurs_data.usec_id)
282 LOOP
283 IF r_get_uso_instructor.instructor_name IS NOT NULL
284 THEN
285 IF lv_instructor IS NOT NULL THEN
286 lv_instructor := lv_instructor || '<BR>' || r_get_uso_instructor.instructor_name || '<BR>';
287 ELSE
288 lv_instructor := r_get_uso_instructor.instructor_name || '<BR>';
289 END IF;
290 END IF;
291 END LOOP;
292 lv_location_details := lv_location_details || lv_instructor || '<BR>';
293 END IF;
294 l_display := TRUE;
295 END LOOP;
296 lv_occurence_details := lv_location_details;
297
298
299 IF lv_occurence_details IS NOT NULL THEN
300 IF l_announce THEN
301 fnd_message.set_name('IGS','IGS_EN_TO_BE_ANNOUNCED');
302 cstMeetingTimes := fnd_message.get;
303 lv_occurence_details := lv_occurence_details || '<BR>' || cstMeetingTimes;
304 END IF;
305 RETURN lv_occurence_details;
306 ELSE
307 IF l_announce THEN
308 fnd_message.set_name('IGS','IGS_EN_TO_BE_ANNOUNCED');
309 cstMeetingTimes := fnd_message.get;
310 RETURN cstMeetingTimes;
311 END IF;
312 END IF;
313
314 RETURN NULL;
315
316 END get_occur_cd_details;
317
318 FUNCTION get_occur_details_no_location(p_uoo_id IN NUMBER) RETURN VARCHAR2 IS
319 ------------------------------------------------------------------------------------
320 --Created by :
321 --Date created:
322 --
323 -- Purpose:
324 -- Known limitations/enhancements and/or remarks:
325 --
326 -- Change History:
327 -- Who When What
328 -- kkillams 15-04-2003 Modified c_igs_ps_usec_occurs cursor as part of performance bug 2749732
329 ------------------------------------------------------------------------------
330 lv_occurence_details varchar2(2000) ;
331 CURSOR c_igs_ps_usec_occurs(p_uoo_id IN NUMBER)
332 IS
333 SELECT
334 uoo_id,
335 DECODE(a.monday, 'Y', 'Mon', NULL)||
336 DECODE(a.tuesday, 'Y', 'Tue', NULL)||
337 DECODE(a.wednesday, 'Y', 'Wed', NULL)||
338 DECODE(a.thursday, 'Y', 'Thu', NULL)||
339 DECODE(a.friday, 'Y', 'Fri', NULL)||
340 DECODE(a.saturday, 'Y', 'Sat', NULL)||
341 DECODE(a.sunday, 'Y', 'Sun', NULL)||' '||
342 TO_CHAR(a.start_time, 'hh:miam')||'-'||
343 TO_CHAR(a.end_time, 'hh:miam')||'<BR>'||
344 LTRIM(f.person_last_name||', '||f.person_first_name||' '||f.person_middle_name) occurence
345 FROM igs_ps_usec_occurs a,
346 igs_ad_building b,
347 hz_parties f
348 WHERE
349 a.building_code = b.building_id(+) AND
350 a.instructor_id = f.party_id(+) AND
351 a.uoo_id = p_uoo_id ORDER BY
352 location_cd,building_id ,room_code;
353 BEGIN
354 FOR c_occurs_data in c_igs_ps_usec_occurs(p_uoo_id)
355 LOOP
356 IF lv_occurence_details IS NOT NULL
357 THEN
358 lv_occurence_details := lv_occurence_details ||'<BR><BR>'||c_occurs_data.occurence ;
359 ELSE
360 lv_occurence_details := c_occurs_data.occurence ;
361 END IF ;
362 END LOOP ;
363 RETURN lv_occurence_details ;
364 end get_occur_details_no_location;
365
366 FUNCTION get_usec_ref_cd (
367 p_uoo_id IN NUMBER
368 ) RETURN VARCHAR2 IS
369
370 lv_ref_cd VARCHAR2(32000) ;
371
372 CURSOR c_usec_ref_cd (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
373 SELECT a.reference_code||' - '||a.reference_code_desc ref_cd
374 FROM igs_ps_us_req_ref_cd a, igs_ps_usec_ref b
375 WHERE b.uoo_id = cp_uoo_id
376 AND a.unit_section_reference_id = b.unit_section_reference_id
377 ORDER BY 1 ;
378
379 CURSOR c_unit_ref_cd (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
380 SELECT unitref.REFERENCE_CODE || ' - ' || unitref.REFERENCE_CODE_DESC ref_cd
381 FROM igs_ps_unitreqref_cd unitref,
382 igs_ps_unit_ofr_opt uoo
383 WHERE uoo.uoo_id = cp_uoo_id
384 AND uoo.unit_cd = unitref.unit_cd
385 AND uoo.version_number = unitref.version_number
386 ORDER BY 1;
387
388 v_usec_record_exists BOOLEAN;
389
390 BEGIN
391
392 v_usec_record_exists := FALSE;
393
394 FOR c_usec_ref_cd_data IN c_usec_ref_cd(p_uoo_id)
395 LOOP
396
397 v_usec_record_exists :=TRUE;
398
399 IF lv_ref_cd IS NOT NULL THEN
400 lv_ref_cd := lv_ref_cd ||' , '||c_usec_ref_cd_data.ref_cd ;
401 ELSE
402 lv_ref_cd := c_usec_ref_cd_data.ref_cd;
403 END IF;
404
405 END LOOP ;
406
407 IF NOT v_usec_record_exists THEN
408 FOR c_unit_ref_cd_data IN c_unit_ref_cd(p_uoo_id)
409 LOOP
410
411 v_usec_record_exists :=TRUE;
412
413 IF lv_ref_cd IS NOT NULL THEN
414 lv_ref_cd := lv_ref_cd ||' , '||c_unit_ref_cd_data.ref_cd ;
415 ELSE
416 lv_ref_cd := c_unit_ref_cd_data.ref_cd;
417 END IF;
418
419 END LOOP ;
420 END IF;
421
422 RETURN lv_ref_cd ;
423
424 END get_usec_ref_cd;
425
426
427 FUNCTION get_usec_occurs_ref_cd (p_uoo_id IN NUMBER)
428 RETURN VARCHAR2 IS
429
430 lv_ref_cd VARCHAR2(32000) := NULL;
431
432 -- The parameter value passed into the function
433 -- and to the cursor will be the unit_section_occurrence_id
434 -- and not uoo_id
435 CURSOR c_usec_occurs_ref_cd IS
436 SELECT reference_code ||' - '|| reference_code_description ref_cd
437 FROM igs_ps_usec_ocur_ref_v
438 WHERE unit_section_occurrence_id = p_uoo_id
439 ORDER BY 1 ;
440
441 BEGIN
442
443 FOR c_usec_occurs_ref_cd_data in c_usec_occurs_ref_cd
444 LOOP
445 IF lv_ref_cd IS NOT NULL THEN
446 lv_ref_cd := lv_ref_cd ||'<BR>'||c_usec_occurs_ref_cd_data.ref_cd ;
447 ELSE
448 lv_ref_cd := c_usec_occurs_ref_cd_data.ref_cd;
449 END IF ;
450 END LOOP ;
451
452 RETURN lv_ref_cd ;
453
454 END get_usec_occurs_ref_cd;
455
456
457 function get_unit_note
458 (
459 p_unit_cd in varchar2,
460 p_version_number in number
461 ) return varchar2
462 is
463 lv_unit_note varchar2(32000) ;
464 cursor c_unit_note is
465 select
466 a.note_text,
467 b.crs_note_type
468 from igs_ge_note a, igs_ps_unit_ver_note b
469 where a.reference_number = b.reference_number
470 and b.unit_cd = p_unit_cd
471 and b.version_number = p_version_number
472 order by crs_note_type ;
473 begin
474 for c_unit_note_data in c_unit_note
475 loop
476 if lv_unit_note is not null
477 then
478 lv_unit_note := lv_unit_note||' , '||c_unit_note_data.note_text ;
479 else
480 lv_unit_note := c_unit_note_data.note_text ;
481 end if ;
482 end loop ;
483
484 return lv_unit_note;
485 end get_unit_note;
486
487 function get_usec_note
488 (
489 p_uoo_id in number
490 ) return varchar2
491 is
492 lv_usec_note varchar2(32000) ;
493 cursor c_usec_note is
494 select
495 a.note_text,
496 b.crs_note_type
497 from igs_ge_note a,igs_ps_unt_ofr_opt_n b
498 where a.reference_number = b.reference_number
499 and b.uoo_id = p_uoo_id
500 order by crs_note_type ;
501 begin
502 for c_usec_note_data in c_usec_note
503 loop
504 if lv_usec_note is not null
505 then
506 lv_usec_note := lv_usec_note||' , '||c_usec_note_data.note_text ;
507 else
508 lv_usec_note := c_usec_note_data.note_text ;
509 end if ;
510 end loop ;
511
512 return lv_usec_note;
513 end get_usec_note;
514 /*
515 | Added for November 2001 |
516 */
517
518 ------------------------------------------------------------------------------------
519 --Created by : ( Oracle IDC)
520 --Date created:
521 --
522 --Purpose: To get the title and subtitle from student level. If not available
523 -- at student level then from unit section level and if not avialble at
524 -- section level then from unit level
525 -- if person id is null then only the unit section and unit level would be seen
526 -- level
527 -- To be used only in self service as then fields are seperated by <BR> tag
528 -- and this is to be used only in SS.
529 --Change History:
530 --Who When What
531 -- prgoyal 8-NOV-2001 Added description, comments for procedure,
532 -- modifed procedure for input parameters and where clause
533 -- of cursor for student level
534 ------------------------------------------------------------------------------
535
536 FUNCTION get_title_section
537 (
538 p_person_id IN NUMBER,
539 p_uoo_id IN NUMBER,
540 p_unit_cd IN VARCHAR2,
541 p_version_number IN NUMBER,
542 p_course_cd IN VARCHAR2
543 )RETURN VARCHAR2 AS
544 -- To fetch the deatils from student level
545 CURSOR stdnt_subtitle_dtls IS
546 SELECT
547 alternative_title title,
548 subtitle
549 FROM
550 igs_en_su_attempt
551 WHERE
552 person_id = p_person_id AND
553 course_cd = p_course_cd AND
554 uoo_id = p_uoo_id;
555
556 -- To fetch the deatils from section level
557 CURSOR usec_subtitle_dtls IS
558 SELECT
559 uref.title,
560 usub.subtitle
561 FROM
562 igs_ps_usec_ref uref,
563 igs_ps_unit_subtitle usub
564 WHERE
565 uref.uoo_id = p_uoo_id AND
566 uref.subtitle_id = usub.subtitle_id(+);
567
568 -- To fetch the deatils from unit level level
569 CURSOR uv_title_dtls (cp_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE,
570 cp_version_number igs_ps_unit_ofr_opt.version_number%TYPE)
571 IS
572 SELECT
573 uv.title,
574 usub.subtitle
575 FROM
576 igs_ps_unit_ver uv ,
577 igs_ps_unit_subtitle usub
578 WHERE
579 uv.unit_cd = cp_unit_cd AND
580 uv.version_number = cp_version_number AND
581 uv.subtitle_id = usub.subtitle_id(+);
582
583 CURSOR cur_fetch_unit_dtls(cp_uoo_id NUMBER) IS
584 select unit_cd , version_number
585 from igs_ps_unit_ofr_opt
586 where uoo_id = cp_uoo_id;
587
588 title_subtitle_dtls_rec usec_subtitle_dtls%ROWTYPE;
589 l_stdnt_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
590 l_stdnt_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
591 l_usec_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
592 l_usec_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
593 l_uv_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
594 l_uv_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
595 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
596 l_unit_ver igs_ps_unit_ver.version_number%TYPE;
597 BEGIN
598 IF p_unit_cd IS NULL THEN
599 OPEN cur_fetch_unit_dtls(p_uoo_id);
600 FETCH cur_fetch_unit_dtls into l_unit_cd, l_unit_ver;
601 CLOSE cur_fetch_unit_dtls;
602 ELSE
603 l_unit_cd := p_unit_cd;
604 l_unit_ver := p_version_number;
605 END IF;
606
607
608 -- first check if title and subtitle exists at student level
609 OPEN stdnt_subtitle_dtls;
610 FETCH stdnt_subtitle_dtls INTO title_subtitle_dtls_rec;
611 l_stdnt_title := title_subtitle_dtls_rec.title;
612 l_stdnt_subtitle := title_subtitle_dtls_rec.subtitle;
613 CLOSE stdnt_subtitle_dtls;
614
615 -- Details are found at student level hence return the value
616 -- and no processing further in the procedure
617 IF ( l_stdnt_title IS NOT NULL AND l_stdnt_subtitle IS NOT NULL)
618 THEN
619 RETURN ( l_stdnt_title || '-' || '<BR>' || l_stdnt_subtitle);
620 END IF;
621
622 -- No details at student level hence now check at the unit section level
623 OPEN usec_subtitle_dtls;
624 FETCH usec_subtitle_dtls INTO title_subtitle_dtls_rec;
625 l_usec_title := title_subtitle_dtls_rec.title;
626 l_usec_subtitle := title_subtitle_dtls_rec.subtitle;
627 -- Either no record exists or one of the values is null
628 -- hence check at unit level
629 IF ( usec_subtitle_dtls%NOTFOUND) OR ( ( l_usec_title IS NULL) OR ( l_usec_subtitle IS NULL)) THEN
630 CLOSE usec_subtitle_dtls;
631 -- check at unit level
632 OPEN uv_title_dtls(l_unit_cd,l_unit_ver);
633 FETCH uv_title_dtls INTO title_subtitle_dtls_rec;
634 l_uv_title := title_subtitle_dtls_rec.title;
635 l_uv_subtitle := title_subtitle_dtls_rec.subtitle;
636 CLOSE uv_title_dtls;
637 ELSE
638 CLOSE usec_subtitle_dtls;
639 END IF;
640
641 IF ( NVL ( NVL ( l_stdnt_subtitle, l_usec_subtitle) , l_uv_subtitle)) IS NULL THEN
642 RETURN ( NVL ( NVL ( l_stdnt_title, l_usec_title) , l_uv_title));
643 ELSE
644 -- Return the concatenation of title abd subtitle
645 RETURN ( NVL ( NVL ( l_stdnt_title, l_usec_title) , l_uv_title) || '-' || '<BR>' || NVL ( NVL ( l_stdnt_subtitle, l_usec_subtitle) , l_uv_subtitle));
646 END IF;
647 END get_title_section;
648
649
650 FUNCTION get_title
651 (
652 p_person_id IN NUMBER,
653 p_uoo_id IN NUMBER,
654 p_unit_cd IN VARCHAR2,
655 p_version_number IN NUMBER,
656 p_course_cd IN VARCHAR2
657 )RETURN VARCHAR2 AS
658 -- To fetch the deatils from student level
659 CURSOR stdnt_subtitle_dtls IS
660 SELECT
661 alternative_title title,
662 subtitle
663 FROM
664 igs_en_su_attempt
665 WHERE
666 person_id = p_person_id AND
667 course_cd = p_course_cd AND
668 uoo_id = p_uoo_id;
669 -- To fetch the deatils from section level
670 CURSOR usec_subtitle_dtls IS
671 SELECT
672 uref.title,
673 usub.subtitle
674 FROM
675 igs_ps_usec_ref uref,
676 igs_ps_unit_subtitle usub
677 WHERE
678 uref.uoo_id = p_uoo_id AND
679 uref.subtitle_id = usub.subtitle_id(+);
680
681 -- To fetch the deatils from unit level level
682 CURSOR uv_title_dtls IS
683 SELECT
684 uv.title,
685 usub.subtitle
686 FROM
687 igs_ps_unit_ver uv ,
688 igs_ps_unit_subtitle usub
689 WHERE
690 uv.unit_cd = p_unit_cd AND
691 uv.version_number = p_version_number AND
692 uv.subtitle_id = usub.subtitle_id(+);
693 title_subtitle_dtls_rec usec_subtitle_dtls%ROWTYPE;
694 l_stdnt_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
695 l_stdnt_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
696 l_usec_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
697 l_usec_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
698 l_uv_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
699 l_uv_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
700 BEGIN
701 -- first check if title and subtitle exists at student level
702 OPEN stdnt_subtitle_dtls;
703 FETCH stdnt_subtitle_dtls INTO title_subtitle_dtls_rec;
704 l_stdnt_title := title_subtitle_dtls_rec.title;
705 l_stdnt_subtitle := title_subtitle_dtls_rec.subtitle;
706 CLOSE stdnt_subtitle_dtls;
707
708 -- Details are found at student level hence return the value
709 -- and no processing further in the procedure
710 IF ( l_stdnt_title IS NOT NULL AND l_stdnt_subtitle IS NOT NULL)
711 THEN
712 RETURN ( l_stdnt_title || '<BR>' || l_stdnt_subtitle);
713 END IF;
714
715 -- No details at student level hence now check at the unit section level
716 OPEN usec_subtitle_dtls;
717 FETCH usec_subtitle_dtls INTO title_subtitle_dtls_rec;
718 l_usec_title := title_subtitle_dtls_rec.title;
719 l_usec_subtitle := title_subtitle_dtls_rec.subtitle;
720 -- Either no record exists or one of the values is null
721 -- hence check at unit level
722 IF ( usec_subtitle_dtls%NOTFOUND) OR ( ( l_usec_title IS NULL) OR ( l_usec_title IS NULL)) THEN
723 CLOSE usec_subtitle_dtls;
724 -- check at unit level
725 OPEN uv_title_dtls;
726 FETCH uv_title_dtls INTO title_subtitle_dtls_rec;
727 l_uv_title := title_subtitle_dtls_rec.title;
728 l_uv_subtitle := title_subtitle_dtls_rec.subtitle;
729 CLOSE uv_title_dtls;
730 END IF;
731 RETURN ( NVL ( NVL ( l_stdnt_title, l_usec_title) , l_uv_title));
732
733 END get_title;
734
735 FUNCTION get_subtitle
736 (
737 p_person_id IN NUMBER,
738 p_uoo_id IN NUMBER,
739 p_unit_cd IN VARCHAR2,
740 p_version_number IN NUMBER,
741 p_course_cd IN VARCHAR2
742 )RETURN VARCHAR2 AS
743 -- To fetch the deatils from student level
744 CURSOR stdnt_subtitle_dtls IS
745 SELECT
746 alternative_title title,
747 subtitle
748 FROM
749 igs_en_su_attempt
750 WHERE
751 person_id = p_person_id AND
752 course_cd = p_course_cd AND
753 uoo_id = p_uoo_id;
754 -- To fetch the deatils from section level
755 CURSOR usec_subtitle_dtls IS
756 SELECT
757 uref.title,
758 usub.subtitle
759 FROM
760 igs_ps_usec_ref uref,
761 igs_ps_unit_subtitle usub
762 WHERE
763 uref.uoo_id = p_uoo_id AND
764 uref.subtitle_id = usub.subtitle_id(+);
765
766 -- To fetch the deatils from unit level level
767 CURSOR uv_title_dtls IS
768 SELECT
769 uv.title,
770 usub.subtitle
771 FROM
772 igs_ps_unit_ver uv ,
773 igs_ps_unit_subtitle usub
774 WHERE
775 uv.unit_cd = p_unit_cd AND
776 uv.version_number = p_version_number AND
777 uv.subtitle_id = usub.subtitle_id(+);
778 title_subtitle_dtls_rec usec_subtitle_dtls%ROWTYPE;
779 l_stdnt_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
780 l_stdnt_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
781 l_usec_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
782 l_usec_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
783 l_uv_title igs_ps_unit_ver.title%TYPE DEFAULT NULL;
784 l_uv_subtitle igs_ps_unit_subtitle.subtitle%TYPE DEFAULT NULL;
785 BEGIN
786 -- first check if title and subtitle exists at student level
787 OPEN stdnt_subtitle_dtls;
788 FETCH stdnt_subtitle_dtls INTO title_subtitle_dtls_rec;
789 l_stdnt_title := title_subtitle_dtls_rec.title;
790 l_stdnt_subtitle := title_subtitle_dtls_rec.subtitle;
791 CLOSE stdnt_subtitle_dtls;
792
793 -- Details are found at student level hence return the value
794 -- and no processing further in the procedure
795 IF ( l_stdnt_title IS NOT NULL AND l_stdnt_subtitle IS NOT NULL)
796 THEN
797 RETURN ( l_stdnt_title || '<BR>' || l_stdnt_subtitle);
798 END IF;
799
800 -- No details at student level hence now check at the unit section level
801 OPEN usec_subtitle_dtls;
802 FETCH usec_subtitle_dtls INTO title_subtitle_dtls_rec;
803 l_usec_title := title_subtitle_dtls_rec.title;
804 l_usec_subtitle := title_subtitle_dtls_rec.subtitle;
805 -- Either no record exists or one of the values is null
806 -- hence check at unit level
807 IF ( usec_subtitle_dtls%NOTFOUND) OR ( ( l_usec_title IS NULL) OR ( l_usec_title IS NULL)) THEN
808 CLOSE usec_subtitle_dtls;
809 -- check at unit level
810 OPEN uv_title_dtls;
811 FETCH uv_title_dtls INTO title_subtitle_dtls_rec;
812 l_uv_title := title_subtitle_dtls_rec.title;
813 l_uv_subtitle := title_subtitle_dtls_rec.subtitle;
814 CLOSE uv_title_dtls;
815 END IF;
816 RETURN NVL ( NVL ( l_stdnt_subtitle, l_usec_subtitle) , l_uv_subtitle);
817
818 END get_subtitle;
819
820 ------------------------------------------------------------------------------------
821 --Created by : ( Oracle IDC)
822 --Date created:
823 --
824 --Purpose: To get the grading schema from student level. If not available
825 -- at student level then from unit section level and if not avialble at
826 -- section level then from unit level
827 -- if person id is null then only the unit section and unit level would be seen
828 --Change History:
829 --Who When What
830 -- prgoyal 8-NOV-2001 Added description, comments for procedure,
831 -- modifed procedure for input parameters and where clause
832 -- of cursor for student level
833 ------------------------------------------------------------------------------
834 FUNCTION get_grading_schema
835 (
836 p_person_id IN NUMBER,
837 p_uoo_id IN NUMBER,
838 p_unit_cd IN VARCHAR2,
839 p_version_NUMBER IN NUMBER,
840 p_course_cd IN VARCHAR2
841 )RETURN VARCHAR2 AS
842 -- to get grading schema at student level
843 CURSOR stdnt_usec_grd_sch IS
844 SELECT
845 sua.grading_schema_code
846 FROM
847 igs_en_su_attempt sua
848 WHERE
849 sua.person_id = p_person_id AND
850 sua.uoo_id = p_uoo_id AND
851 sua.course_cd = p_course_cd;
852
853 -- to get grading schema at section level
854 CURSOR usec_grd_sch IS
855 SELECT
856 usgr.grading_schema_code
857 FROM
858 igs_ps_usec_grd_schm usgr
859 WHERE
860 usgr.uoo_id = p_uoo_id AND
861 usgr.default_flag = 'Y';
862
863 -- to get grading schema at unit level
864 CURSOR unit_grd_sch IS
865 SELECT
866 grading_schema_code
867 FROM
868 igs_ps_unit_grd_schm uvgr
869 WHERE
870 uvgr.unit_code = p_unit_cd AND
871 uvgr.unit_version_number = p_version_number AND
872 uvgr.default_flag = 'Y';
873
874 l_usec_grd_sch igs_en_su_attempt.grading_schema_code%TYPE DEFAULT NULL;
875 l_stdnt_usec_grd_sch igs_en_su_attempt.grading_schema_code%TYPE DEFAULT NULL;
876 l_unit_grd_sch igs_en_su_attempt.grading_schema_code%TYPE DEFAULT NULL;
877 BEGIN
878 -- get the grading schema at student level
879 OPEN stdnt_usec_grd_sch;
880 FETCH stdnt_usec_grd_sch INTO l_stdnt_usec_grd_sch;
881 CLOSE stdnt_usec_grd_sch;
882 -- if found then return the value else lok one level above at section level
883 IF l_stdnt_usec_grd_sch IS NOT NULL THEN
884 RETURN l_stdnt_usec_grd_sch;
885 END IF;
886
887 -- get the grading schema at section level
888 OPEN usec_grd_sch;
889 FETCH usec_grd_sch INTO l_usec_grd_sch;
890
891 -- Not found at section level hence look at the unit level
892 IF usec_grd_sch%NOTFOUND OR l_usec_grd_sch IS NULL THEN
893 OPEN unit_grd_sch;
894 FETCH unit_grd_sch INTO l_unit_grd_sch;
895 CLOSE unit_grd_sch;
896 END IF;
897 CLOSE usec_grd_sch;
898 RETURN ( NVL( l_usec_grd_sch, l_unit_grd_sch));
899 END get_grading_schema;
900
901 ------------------------------------------------------------------------------------
902 --Created by : ( Oracle IDC)
903 --Date created:
904 --
905 --Purpose: To get the grading schema from student level. If not available
906 -- at student level then from unit section level and if not avialble at
907 -- section level then from unit level
908 -- if person id is null then only the unit section and unit level would be seen
909 --Change History:
910 --Who When What
911 -- msrinivi 8-NOV-2001 Added description, comments for procedure,
912 -- modifed procedure for input parameters and where clause
913 -- of cursor for student level
914 ------------------------------------------------------------------------------
915 FUNCTION get_grading_schema_ver
916 (
917 p_person_id IN NUMBER,
918 p_uoo_id IN NUMBER,
919 p_unit_cd IN VARCHAR2,
920 p_version_NUMBER IN NUMBER,
921 p_course_cd IN VARCHAR2
922 )RETURN NUMBER AS
923 -- to get grading schema at student level
924 CURSOR stdnt_usec_grd_sch IS
925 SELECT
926 sua.gs_version_number
927 FROM
928 igs_en_su_attempt sua
929 WHERE
930 sua.person_id = p_person_id AND
931 sua.uoo_id = p_uoo_id AND
932 sua.course_cd = p_course_cd;
933
934 -- to get grading schema at section level
935 CURSOR usec_grd_sch IS
936 SELECT
937 usgr.grd_schm_version_number
938 FROM
939 igs_ps_usec_grd_schm usgr
940 WHERE
941 usgr.uoo_id = p_uoo_id AND
942 usgr.default_flag = 'Y';
943
944 -- to get grading schema at unit level
945 CURSOR unit_grd_sch IS
946 SELECT
947 grd_schm_version_number
948 FROM
949 igs_ps_unit_grd_schm uvgr
950 WHERE
951 uvgr.unit_code = p_unit_cd AND
952 uvgr.unit_version_number = p_version_number AND
953 uvgr.default_flag = 'Y';
954
955 l_usec_grd_sch_ver igs_en_su_attempt.gs_version_number%TYPE DEFAULT NULL;
956 l_stdnt_usec_grd_sch_ver igs_en_su_attempt.gs_version_number%TYPE DEFAULT NULL;
957 l_unit_grd_sch_ver igs_en_su_attempt.gs_version_number%TYPE DEFAULT NULL;
958 BEGIN
959 -- get the grading schema at student level
960 OPEN stdnt_usec_grd_sch;
961 FETCH stdnt_usec_grd_sch INTO l_stdnt_usec_grd_sch_ver;
962 CLOSE stdnt_usec_grd_sch;
963 -- if found then return the value else lok one level above at section level
964 IF l_stdnt_usec_grd_sch_ver IS NOT NULL THEN
965 RETURN l_stdnt_usec_grd_sch_ver;
966 END IF;
967
968 -- get the grading schema at section level
969 OPEN usec_grd_sch;
970 FETCH usec_grd_sch INTO l_usec_grd_sch_ver;
971
972 -- Not found at section level hence look at the unit level
973 IF usec_grd_sch%NOTFOUND OR l_usec_grd_sch_ver IS NULL THEN
974 OPEN unit_grd_sch;
975 FETCH unit_grd_sch INTO l_unit_grd_sch_ver;
976 CLOSE unit_grd_sch;
977 END IF;
978 CLOSE usec_grd_sch;
979 RETURN ( NVL( l_usec_grd_sch_ver, l_unit_grd_sch_ver));
980 END get_grading_schema_ver;
981
982 FUNCTION get_grading_schema_desc
983 (
984 p_person_id IN NUMBER,
985 p_uoo_id IN NUMBER,
986 p_unit_cd IN VARCHAR2,
987 p_version_number IN NUMBER,
988 p_course_cd IN VARCHAR2
989 )RETURN VARCHAR2 AS
990
991 -- to get grading schema at student level
992 CURSOR stdnt_usec_grd_sch (cp_n_person_id IN NUMBER,
993 cp_n_uoo_id IN NUMBER,
994 cp_c_course_cd IN VARCHAR2) IS
995 SELECT
996 sua.grading_schema_code,
997 sua.gs_version_number
998 FROM
999 igs_en_su_attempt sua
1000 WHERE
1001 sua.person_id = cp_n_person_id AND
1002 sua.uoo_id = cp_n_uoo_id AND
1003 sua.course_cd = p_course_cd;
1004
1005 -- to get grading schema at section level
1006 CURSOR usec_grd_sch (cp_n_uoo_id IN NUMBER) IS
1007 SELECT
1008 usgr.grading_schema_code,
1009 usgr.grd_schm_version_number
1010 FROM
1011 igs_ps_usec_grd_schm usgr
1012 WHERE
1013 usgr.uoo_id = cp_n_uoo_id AND
1014 usgr.default_flag = 'Y';
1015
1016 -- to get grading schema at unit level
1017 CURSOR unit_grd_sch (cp_c_unit_cd IN VARCHAR2,
1018 cp_n_ver_num IN NUMBER) IS
1019 SELECT
1020 uvgr.grading_schema_code,
1021 uvgr.grd_schm_version_number
1022 FROM
1023 igs_ps_unit_grd_schm uvgr
1024 WHERE
1025 uvgr.unit_code = cp_c_unit_cd AND
1026 uvgr.unit_version_number = cp_n_ver_num AND
1027 uvgr.default_flag = 'Y';
1028
1029 -- to get grading schema description
1030 CURSOR c_grd_desc(cp_grd_schm IN VARCHAR2,
1031 cp_ver_num IN NUMBER) IS
1032 SELECT description
1033 FROM igs_as_grd_schema
1034 WHERE grading_schema_cd = cp_grd_schm
1035 AND version_number = cp_ver_num;
1036
1037 l_grd_schm igs_as_grd_schema.grading_schema_cd%TYPE;
1038 l_grd_schm_ver igs_as_grd_schema.version_number%TYPE;
1039 l_grd_desc igs_as_grd_schema.description%TYPE;
1040
1041 BEGIN
1042
1043 -- Get the grading schema code and version from SUA level.
1044 OPEN stdnt_usec_grd_sch(p_person_id,p_uoo_id,p_course_cd);
1045 FETCH stdnt_usec_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1046 IF l_grd_schm IS NULL THEN
1047
1048 -- if does not exists at SUA level then get it from Unit Section Level.
1049 OPEN usec_grd_sch(p_uoo_id);
1050 FETCH usec_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1051 IF l_grd_schm IS NULL THEN
1052
1053 -- if does not exists at Unit Section level also then get it from Unit Version Level.
1054 OPEN unit_grd_sch(p_unit_cd, p_version_number);
1055 FETCH unit_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1056 CLOSE unit_grd_sch;
1057
1058 END IF;
1059 CLOSE usec_grd_sch;
1060
1061 END IF;
1062 CLOSE stdnt_usec_grd_sch;
1063
1064 -- Get the description of grading schema code and version number
1065 OPEN c_grd_desc (l_grd_schm,l_grd_schm_ver);
1066 FETCH c_grd_desc INTO l_grd_desc;
1067 CLOSE c_grd_desc;
1068
1069 RETURN l_grd_desc;
1070
1071 END get_grading_schema_desc;
1072
1073
1074 FUNCTION get_grading_cd_ver
1075 (
1076 p_person_id IN NUMBER,
1077 p_uoo_id IN NUMBER,
1078 p_unit_cd IN VARCHAR2,
1079 p_version_NUMBER IN NUMBER,
1080 p_course_cd IN VARCHAR2
1081 )RETURN VARCHAR2 AS
1082
1083 -- to get grading schema at student level
1084 CURSOR stdnt_usec_grd_sch (cp_n_person_id IN NUMBER,
1085 cp_n_uoo_id IN NUMBER,
1086 cp_c_course_cd IN VARCHAR2) IS
1087 SELECT
1088 sua.grading_schema_code,
1089 sua.gs_version_number
1090 FROM
1091 igs_en_su_attempt sua
1092 WHERE
1093 sua.person_id = cp_n_person_id AND
1094 sua.uoo_id = cp_n_uoo_id AND
1095 sua.course_cd = p_course_cd;
1096
1097 -- to get grading schema at section level
1098 CURSOR usec_grd_sch (cp_n_uoo_id IN NUMBER) IS
1099 SELECT
1100 usgr.grading_schema_code,
1101 usgr.grd_schm_version_number
1102 FROM
1103 igs_ps_usec_grd_schm usgr
1104 WHERE
1105 usgr.uoo_id = cp_n_uoo_id AND
1106 usgr.default_flag = 'Y';
1107
1108 -- to get grading schema at unit level
1109 CURSOR unit_grd_sch (cp_c_unit_cd IN VARCHAR2,
1110 cp_n_ver_num IN NUMBER) IS
1111 SELECT
1112 uvgr.grading_schema_code,
1113 uvgr.grd_schm_version_number
1114 FROM
1115 igs_ps_unit_grd_schm uvgr
1116 WHERE
1117 uvgr.unit_code = cp_c_unit_cd AND
1118 uvgr.unit_version_number = cp_n_ver_num AND
1119 uvgr.default_flag = 'Y';
1120
1121 l_grd_schm igs_as_grd_schema.grading_schema_cd%TYPE;
1122 l_grd_schm_ver igs_as_grd_schema.version_number%TYPE;
1123
1124 BEGIN
1125
1126 -- Get the grading schema code and version from SUA level.
1127 OPEN stdnt_usec_grd_sch(p_person_id,p_uoo_id,p_course_cd);
1128 FETCH stdnt_usec_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1129 IF l_grd_schm IS NULL THEN
1130
1131 -- if does not exists at SUA level then get it from Unit Section Level.
1132 OPEN usec_grd_sch(p_uoo_id);
1133 FETCH usec_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1134 IF l_grd_schm IS NULL THEN
1135
1136 -- if does not exists at Unit Section level also then get it from Unit Version Level.
1137 OPEN unit_grd_sch(p_unit_cd, p_version_number);
1138 FETCH unit_grd_sch INTO l_grd_schm,l_grd_schm_ver;
1139 CLOSE unit_grd_sch;
1140
1141 END IF;
1142 CLOSE usec_grd_sch;
1143
1144 END IF;
1145 CLOSE stdnt_usec_grd_sch;
1146
1147 RETURN l_grd_schm || ',' || l_grd_schm_ver;
1148
1149 END get_grading_cd_ver;
1150
1151 ------------------------------------------------------------------------------------
1152 --Created by : ( Oracle IDC)
1153 --Date created:
1154 --
1155 -- Purpose: To get the overrid enrolled credit points from student level. If not available
1156 -- at student level then from unit section level and if not avialble at
1157 -- section level then from unit level
1158 -- at unit level if the variable credit poins indicator is checked then
1159 -- the achievable credit points is not null at section level then fetch that value
1160 -- if that is null or the indiactor is not set then get the enrolled credit points
1161 -- at the unit level
1162 -- this is because it is assumed that at section level if variable credit points are
1163 -- to be defined then it would be stored in achievable credit points field as enrolled
1164 -- credit points are not stored at section level
1165
1166 --Change History:
1167 --Who When What
1168 -- prgoyal 8-NOV-2001 Added description, comments for procedure,
1169 -- modifed procedure for input parameters and where clause
1170 -- of cursor for student level
1171 -- added the section level check
1172 ------------------------------------------------------------------------------
1173 FUNCTION get_credit_points
1174 (
1175 p_person_id IN NUMBER,
1176 p_uoo_id IN NUMBER,
1177 p_unit_cd IN VARCHAR2,
1178 p_version_NUMBER IN NUMBER,
1179 p_course_cd IN VARCHAR2
1180 )RETURN NUMBER AS
1181
1182 -- get credit points at unit level
1183 CURSOR unit_credit_pts IS
1184 SELECT
1185 NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) credit_points
1186 FROM
1187 igs_ps_unit_ofr_opt uoo,
1188 igs_ps_usec_cps cps,
1189 igs_ps_unit_ver uv
1190 WHERE
1191 uoo.uoo_id=cps.uoo_id(+) AND
1192 uoo.uoo_id=p_uoo_id AND
1193 uoo.unit_cd=uv.unit_cd AND
1194 uoo.version_number=uv.version_number ;
1195
1196
1197 -- get the credit points at student level
1198 CURSOR stdnt_credit_pts IS
1199 SELECT
1200 sua.override_enrolled_cp
1201 FROM
1202 igs_en_su_attempt sua
1203 WHERE
1204 sua.person_id = p_person_id AND
1205 sua.uoo_id = p_uoo_id AND
1206 sua.course_cd= p_course_cd;
1207
1208 l_unit_credit_pts igs_ps_unit_ver.enrolled_credit_points%TYPE DEFAULT NULL;
1209 l_stdnt_credit_pts igs_ps_unit_ver.enrolled_credit_points%TYPE DEFAULT NULL;
1210
1211 BEGIN
1212 -- verify at student level and return if value found
1213 IF p_person_id IS NOT NULL THEN
1214 OPEN stdnt_credit_pts;
1215 FETCH stdnt_credit_pts INTO l_stdnt_credit_pts;
1216 CLOSE stdnt_credit_pts;
1217 IF l_stdnt_credit_pts IS NOT NULL THEN
1218 RETURN l_stdnt_credit_pts;
1219 END IF;
1220 END IF;
1221 -- fetch at unit level..
1222 OPEN unit_credit_pts;
1223 FETCH unit_credit_pts INTO l_unit_credit_pts;
1224 CLOSE unit_credit_pts;
1225
1226 RETURN l_unit_credit_pts;
1227
1228 END get_credit_points;
1229
1230 ------------------------------------------------------------------------------------
1231 --Created by : prgoyal ( Oracle IDC)
1232 --Date created: 14-OCT-2001
1233 --
1234 --Purpose: To be used in self service to return the primary program,
1235 -- version and list of program for the career for a student.
1236 -- The program list has primary program first followed by the other
1237 -- enrolled progrmas for the career
1238 --Known limitations/enhancements and/or remarks:
1239 --
1240 --Change History:
1241 --Who When What
1242 --stutta 25-NOV-2003 Changed get_primary_prgm_dtls and get_secondary_prgm_dtls cursors
1243 -- to check for term records while retrieving program_version and
1244 -- primary program. BUG #2829263
1245 ------------------------------------------------------------------------------
1246
1247 PROCEDURE enrp_get_prgm_for_career
1248 (
1249 p_primary_program OUT NOCOPY VARCHAR2,
1250 p_primary_program_version OUT NOCOPY NUMBER,
1251 p_programlist OUT NOCOPY VARCHAR2,
1252 p_person_id IN NUMBER,
1253 p_carrer IN VARCHAR2,
1254 p_term_cal_type IN VARCHAR2,
1255 p_term_sequence_number IN NUMBER
1256 ) AS
1257
1258 CURSOR get_primary_prgm_dtls IS
1259 SELECT
1260 pv.course_cd,
1261 pv.version_number,
1262 pv.title
1263 FROM IGS_EN_STDNT_PS_ATT_ALL sca,
1264 IGS_PS_VER_ALL pv
1265 WHERE pv.course_cd = sca.course_cd
1266 AND pv.version_number = igs_en_spa_terms_api.get_spat_program_version(sca.person_id, sca.course_cd, p_term_cal_type,
1267 p_term_sequence_number)
1268 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT')
1269 AND sca.person_id = p_person_id
1270 AND pv.course_type = p_carrer
1271 AND igs_en_spa_terms_api.get_spat_primary_prg(sca.person_id, sca.course_cd, p_term_cal_type,
1272 p_term_sequence_number) = 'PRIMARY';
1273
1274
1275
1276 CURSOR get_secondary_prgm_dtls (p_course_code igs_en_stdnt_ps_att.course_cd%TYPE)IS
1277 SELECT pv.title
1278 FROM IGS_EN_STDNT_PS_ATT_ALL sca,
1279 IGS_PS_VER_ALL pv
1280 WHERE pv.course_cd = sca.course_cd
1281 AND pv.version_number = sca.version_number
1282 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT')
1283 AND sca.person_id = p_person_id
1284 AND pv.course_type = p_carrer
1285 AND sca.course_cd <> p_course_code;
1286 -- Returns all programs which are not the primary program for that career using the passed in primary course_cd.
1287 l_all_program_title VARCHAR2(2000);
1288 l_primary_program_dtls get_primary_prgm_dtls%ROWTYPE;
1289
1290 BEGIN
1291 -- Get the Primary Program Details
1292 Open get_primary_prgm_dtls;
1293 FETCH get_primary_prgm_dtls INTO l_primary_program_dtls ;
1294 -- If there is No Primary program set NULL to OUT NOCOPY parameters and Return
1295 IF get_primary_prgm_dtls%NOTFOUND THEN
1296 CLOSE get_primary_prgm_dtls;
1297 p_primary_program := NULL;
1298 p_primary_program_version := NULL;
1299 p_programlist := NULL;
1300 RETURN;
1301 END IF;
1302 CLOSE get_primary_prgm_dtls;
1303
1304 -- Concatenate Titles of the Secondary Programs for the given Career
1305 FOR r_dtls IN get_secondary_prgm_dtls(l_primary_program_dtls.course_cd)
1306 LOOP
1307 l_all_program_title := l_all_program_title || ' , ' || r_dtls.title ;
1308 END LOOP;
1309
1310 --Concatenate the Primary Program Title with All Secondary program's Titles
1311
1312 l_all_program_title := l_primary_program_dtls.title || l_all_program_title;
1313
1314 p_primary_program := l_primary_program_dtls.course_cd;
1315 p_primary_program_version:= l_primary_program_dtls.version_number;
1316 p_programlist := l_all_program_title;
1317
1318 END enrp_get_prgm_for_career;
1319
1320 FUNCTION enrp_val_subttl_chg ( p_person_id IN NUMBER,
1321 p_uoo_id IN NUMBER
1322 ) RETURN CHAR IS
1323 CURSOR c_sua_chg_alwd IS
1324 SELECT NVL(subtitle_modifiable_flag,'N')
1325 FROM igs_ps_usec_ref
1326 WHERE
1327 uoo_id = p_uoo_id;
1328
1329 l_sua_chg_alwd igs_ps_usec_ref.subtitle_modifiable_flag%TYPE;
1330
1331 BEGIN
1332
1333 OPEN c_sua_chg_alwd;
1334 FETCH c_sua_chg_alwd INTO l_sua_chg_alwd;
1335 CLOSE c_sua_chg_alwd;
1336
1337 RETURN NVL(l_sua_chg_alwd,'N');
1338
1339 END enrp_val_subttl_chg;
1340
1341
1342 FUNCTION get_allowable_cp_range
1343 (
1344 p_uoo_id IN NUMBER
1345 ) RETURN VARCHAR2 IS
1346
1347 CURSOR c_get_unit_cd_ver (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1348 SELECT unit_cd,version_number
1349 FROM igs_ps_unit_ofr_opt
1350 WHERE uoo_id = p_uoo_id;
1351
1352 CURSOR c_usec_cp IS
1353 SELECT
1354 minimum_credit_points,
1355 maximum_credit_points,
1356 variable_increment
1357 FROM igs_ps_usec_cps
1358 WHERE uoo_id = p_uoo_id;
1359
1360 CURSOR c_unit_cp(p_unit_cd igs_ps_unit_ver.unit_cd%TYPE,p_unit_ver igs_ps_unit_ver.version_number%TYPE) IS
1361 SELECT
1362 POINTS_MIN ,
1363 POINTS_MAX ,
1364 points_increment
1365 FROM igs_ps_unit_ver
1366 WHERE unit_cd = p_unit_cd
1367 AND version_number = p_unit_ver;
1368
1369 CURSOR c_enr_cp IS
1370 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points
1371 FROM
1372 igs_ps_unit_ofr_opt uoo,
1373 igs_ps_usec_cps cps,
1374 igs_ps_unit_ver uv
1375 WHERE
1376 uoo.uoo_id = cps.uoo_id(+) AND
1377 uoo.uoo_id=p_uoo_id AND
1378 uoo.unit_cd=uv.unit_cd AND
1379 uoo.version_number = uv.version_number;
1380
1381 l_cp_range VARCHAR2(10000);
1382
1383 l_usec_min_cp igs_ps_usec_cps.minimum_credit_points%TYPE;
1384 l_usec_max_cp igs_ps_usec_cps.maximum_credit_points%TYPE;
1385 l_usec_var igs_ps_usec_cps.variable_increment%TYPE;
1386
1387 l_unit_min_cp igs_ps_unit_ver.points_min%TYPE;
1388 l_unit_max_cp igs_ps_unit_ver.points_max%TYPE;
1389 l_unit_var igs_ps_unit_ver.points_increment%TYPE;
1390 l_enr_cr_points igs_ps_unit_ver.enrolled_credit_points%TYPE;
1391
1392 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
1393 l_unit_ver igs_ps_unit_ver.version_number%TYPE;
1394
1395 l_count NUMBER(6,3) DEFAULT 0;
1396 l_return_val VARCHAR2(10000);
1397
1398 BEGIN
1399
1400 OPEN c_get_unit_cd_ver(p_uoo_id);
1401 FETCH c_get_unit_cd_ver INTO l_unit_cd,l_unit_ver;
1402 CLOSE c_get_unit_cd_ver;
1403
1404 OPEN c_usec_cp;
1405 FETCH c_usec_cp INTO l_usec_min_cp,l_usec_max_cp,l_usec_var;
1406
1407 IF c_usec_cp%NOTFOUND THEN
1408 OPEN c_unit_cp(l_unit_cd,l_unit_ver);
1409 FETCH c_unit_cp INTO l_unit_min_cp,l_unit_max_cp,l_unit_var;
1410 l_count := l_unit_min_cp;
1411
1412 WHILE l_count <= l_unit_max_cp LOOP
1413 l_cp_range := l_cp_range||','||TO_CHAR(l_count);
1414 l_count := l_count + l_unit_var;
1415 END LOOP;
1416 CLOSE c_unit_cp;
1417
1418 ELSE
1419 l_count := l_usec_min_cp;
1420 WHILE l_count <= l_usec_max_cp LOOP
1421 l_cp_range := l_cp_range||','||TO_CHAR(l_count);
1422 l_count := l_count + l_usec_var;
1423 END LOOP;
1424 END IF;
1425
1426 CLOSE c_usec_cp;
1427
1428
1429 l_return_val := SUBSTR(l_cp_range,2,LENGTH(l_cp_range));
1430
1431 IF l_return_val IS NULL THEN
1432 OPEN c_enr_cp;
1433 FETCH c_enr_cp INTO l_enr_cr_points;
1434 CLOSE c_enr_cp;
1435 l_return_val := l_enr_cr_points;
1436 END IF;
1437
1438 RETURN l_return_val;
1439
1440
1441 END;
1442
1443
1444 FUNCTION get_notification(
1445 p_person_type VARCHAR2,
1446 p_enrollment_category VARCHAR2,
1447 p_comm_type VARCHAR2,
1448 p_enr_method_type VARCHAR2,
1449 p_step_group_type VARCHAR2,
1450 p_step_type VARCHAR2,
1451 p_person_id NUMBER,
1452 p_message OUT NOCOPY VARCHAR2
1453 ) RETURN VARCHAR2
1454 AS
1455 /* Change History
1456 Who When What
1457 Nishikant 01NOV2002 SEVIS Build, Enh bug#2641905.
1458 p_person_id and p_message added in the signature. The new functionality is to check any validation setup existing
1459 at Person ID Group level before considering base(Enrollment Category Validation Setup) level.
1460 ayedubat 11-APR-2002 Changed the cursor statement of cur_program_steps to add an extra 'OR'
1461 condition(eru.s_student_comm_type = 'ALL') for s_student_comm_type as part of the bug fix: 2315245
1462 nalkumar 14-May-2002 Modified the cur_program_steps cursor as per the bug# 2364461.
1463 smaddali 15-oct-2004 Modified the ref cursor cur_program_steps for bug#3944353. Removed the join with lookups .
1464 */
1465 CURSOR cur_person_types
1466 IS
1467 SELECT system_type
1468 FROM igs_pe_person_types
1469 WHERE person_type_code = p_person_type;
1470
1471
1472 TYPE l_program_steps_rec IS RECORD (
1473 notification_flag igs_en_cpd_ext.notification_flag%TYPE
1474 );
1475 TYPE cur_ref_program_steps IS REF CURSOR RETURN l_program_steps_rec;
1476 cur_program_steps cur_ref_program_steps;
1477
1478 l_cur_program_steps cur_program_steps%ROWTYPE;
1479 l_cur_person_types cur_person_types%ROWTYPE;
1480 l_notification_flag igs_en_cpd_ext.notification_flag%TYPE;
1481 l_system_person_type igs_pe_person_types.system_type%TYPE;
1482 l_pig_deny_warn igs_en_cpd_ext.notification_flag%TYPE;
1483 l_message fnd_new_messages.message_name%TYPE;
1484
1485 BEGIN
1486
1487 OPEN cur_person_types;
1488 FETCH cur_person_types INTO l_cur_person_types;
1489 CLOSE cur_person_types;
1490
1491 l_system_person_type := l_cur_person_types.system_type;
1492
1493 -- Calling the below function to get the notification flag of the Step Type if defined at Person ID Group level.
1494 l_pig_deny_warn := igs_en_val_pig.get_pig_notify_flag (p_step_type, p_person_id,l_message);
1495 IF l_message IS NOT NULL THEN
1496 p_message := l_message;
1497 RETURN NULL;
1498 END IF;
1499
1500 IF l_system_person_type = 'STUDENT' THEN
1501 OPEN cur_program_steps FOR SELECT DECODE (l_pig_deny_warn, NULL, eru.notification_flag, l_pig_deny_warn) notification_flag
1502 FROM igs_en_cpd_ext eru,
1503 igs_lookups_view lkup
1504 WHERE eru.s_enrolment_step_type = lkup.lookup_code AND
1505 eru.enrolment_cat = p_enrollment_category AND
1506 (eru.s_student_comm_type = p_comm_type OR
1507 eru.s_student_comm_type = 'ALL' ) AND
1508 eru.enr_method_type = p_enr_method_type AND
1509 lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT' AND
1510 lkup.step_group_type = p_step_group_type AND
1511 eru.s_enrolment_step_type = p_step_type;
1512 ELSE
1513 --ijeddy modified the cursor for bug 3724930
1514 -- smaddali modified the cursor for bug#3944353 to revert the changes made by bug 3724930,
1515 -- removed equi join between lookups and uact as lookups is already joined to eru
1516 OPEN cur_program_steps FOR SELECT DECODE (uact.deny_warn,
1517 'WARN', 'WARN',
1518 'DENY', 'DENY',
1519 NULL, DECODE (l_pig_deny_warn, NULL, eru.notification_flag, l_pig_deny_warn)
1520 ) notification_flag
1521 FROM igs_en_cpd_ext_all eru,
1522 igs_pe_usr_aval_all uact,
1523 igs_lookups_view lkup
1524 WHERE eru.s_enrolment_step_type = lkup.lookup_code
1525 AND eru.enrolment_cat = p_enrollment_category
1526 AND eru.enr_method_type = p_enr_method_type
1527 AND (eru.s_student_comm_type = p_comm_type
1528 OR eru.s_student_comm_type = 'ALL')
1529 AND lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT'
1530 AND lkup.step_group_type = p_step_group_type
1531 AND eru.s_enrolment_step_type = uact.VALIDATION(+)
1532 AND uact.person_type(+) = p_person_type
1533 AND NVL (uact.override_ind, 'N') = 'N'
1534 AND eru.s_enrolment_step_type = p_step_type;
1535 END IF;
1536 LOOP
1537 FETCH cur_program_steps INTO l_cur_program_steps;
1538 EXIT WHEN cur_program_steps%NOTFOUND;
1539 l_notification_flag := l_cur_program_steps.notification_flag;
1540 END LOOP;
1541 CLOSE cur_program_steps ;
1542 RETURN l_notification_flag;
1543
1544 END get_notification;
1545
1546 FUNCTION get_usec_eff_dates(
1547 x_unit_cd VARCHAR2,
1548 x_version NUMBER,
1549 x_cal_type VARCHAR2,
1550 x_ci_seq_number NUMBER,
1551 x_location_cd VARCHAR2,
1552 x_unit_class VARCHAR2) RETURN VARCHAR2 IS
1553
1554 CURSOR c_usec_dates IS
1555 SELECT TO_CHAR(UNIT_SECTION_START_DATE) ||' - '||
1556 TO_CHAR(UNIT_SECTION_END_DATE )
1557 FROM igs_ps_unit_ofr_opt
1558 WHERE unit_cd = x_unit_cd
1559 AND version_number = x_version
1560 AND cal_type = x_cal_type
1561 AND ci_sequence_number = x_ci_seq_number
1562 AND location_cd = x_location_cd
1563 AND unit_class = x_unit_class ;
1564
1565 CURSOR c_cal_dates IS
1566 SELECT TO_CHAR(START_DT) || ' - ' ||
1567 TO_CHAR(END_DT)
1568 FROM igs_ca_inst
1569 WHERE cal_type = x_cal_type
1570 AND sequence_number = x_ci_seq_number;
1571
1572 l_eff_date VARCHAR2(50) DEFAULT NULL;
1573
1574 BEGIN
1575
1576 OPEN c_usec_dates;
1577 FETCH c_usec_dates INTO l_eff_date;
1578 IF l_eff_date = ' - ' THEN
1579 OPEN c_cal_dates;
1580 FETCH c_cal_dates INTO l_eff_date;
1581 CLOSE c_cal_dates;
1582 END IF;
1583 CLOSE c_usec_dates;
1584
1585
1586 RETURN l_eff_date;
1587
1588 END;
1589
1590 PROCEDURE get_enrollment_limits(p_uooid NUMBER,
1591 p_unitcode VARCHAR2,p_version NUMBER,p_actenrolled OUT NOCOPY NUMBER,
1592 p_maxlimit OUT NOCOPY NUMBER ,p_minlimit OUT NOCOPY NUMBER )
1593 AS
1594 --Cursor to fetch the actual enrolled from Unit Offering Option
1595 CURSOR cur_get_actual IS
1596 SELECT enrollment_actual
1597 FROM igs_ps_unit_ofr_opt
1598 WHERE uoo_id=p_uooid;
1599 --Cursor to fetch the limits from Unit Section
1600 CURSOR cur_get_usec_maxmin_limits IS
1601 SELECT enrollment_minimum,enrollment_maximum
1602 FROM igs_ps_usec_lim_wlst_v
1603 WHERE uoo_id =p_uooid;
1604 --Cursor to fetch the limits for Unit Code
1605 CURSOR cur_get_unit_maxmin_limits IS
1606 SELECT enrollment_minimum,enrollment_maximum
1607 FROM igs_ps_unit_ver_v
1608 WHERE unit_cd=p_unitcode
1609 AND version_number=p_version;
1610
1611 BEGIN
1612
1613 -- Actual Enrolled can be got from Unit offering Option Section Setup only
1614 -- as the Unit Setup will not have this information
1615 OPEN cur_get_actual;
1616 FETCH cur_get_actual INTO p_actenrolled;
1617 CLOSE cur_get_actual;
1618 -- Max and Min Enrollment Limits from UnitSection
1619 OPEN cur_get_usec_maxmin_limits;
1620 FETCH cur_get_usec_maxmin_limits INTO p_minlimit,p_maxlimit ;
1621 IF cur_get_usec_maxmin_limits%NOTFOUND THEN
1622 -- Max and Min Enrollment Limits from Unit
1623 BEGIN
1624 OPEN cur_get_unit_maxmin_limits;
1625 FETCH cur_get_unit_maxmin_limits INTO p_minlimit,p_maxlimit;
1626 CLOSE cur_get_unit_maxmin_limits;
1627 END;
1628 END IF;
1629 CLOSE cur_get_usec_maxmin_limits;
1630 END get_enrollment_limits;
1631
1632 ------------------------------------------------------------------------------------
1633 --Created by : knaraset ( Oracle IDC)
1634 --Date created: 09-Jan-2002
1635 --
1636 --Purpose: To Get the latest term calendar info for the given person/program,
1637 -- in which at least one ENROLLED unit attempt exist
1638 --Known limitations/enhancements and/or remarks:
1639 --
1640 --Change History:
1641 --Who When What
1642 ------------------------------------------------------------------------------
1643 PROCEDURE enrp_get_enr_term
1644 (
1645 p_person_id IN NUMBER,
1646 p_course_cd IN VARCHAR2,
1647 p_cal_type OUT NOCOPY VARCHAR2,
1648 p_sequence_number OUT NOCOPY NUMBER,
1649 p_term_desc OUT NOCOPY VARCHAR2
1650 ) AS
1651
1652 --ijeddy modified the cursor for bug 3724930
1653 CURSOR c_term_info_ccd IS
1654 SELECT ttl.load_cal_type, ttl.load_ci_sequence_number, ttl.load_description
1655 FROM igs_ca_teach_to_load_v ttl, igs_en_su_attempt_all sua
1656 WHERE ttl.teach_cal_type = sua.cal_type
1657 AND ttl.teach_ci_sequence_number = sua.ci_sequence_number
1658 AND sua.unit_attempt_status = 'ENROLLED'
1659 AND sua.person_id = p_person_id
1660 AND sua.course_cd = p_course_cd
1661 ORDER BY ttl.load_start_dt DESC;
1662
1663
1664 CURSOR c_term_info IS
1665 SELECT ttl.load_cal_type, ttl.load_ci_sequence_number, ttl.load_description
1666 FROM igs_ca_teach_to_load_v ttl, igs_en_su_attempt_all sua
1667 WHERE ttl.teach_cal_type = sua.cal_type
1668 AND ttl.teach_ci_sequence_number = sua.ci_sequence_number
1669 AND sua.unit_attempt_status = 'ENROLLED'
1670 AND sua.person_id = p_person_id
1671 ORDER BY ttl.load_start_dt DESC;
1672
1673
1674 l_term_info c_term_info%ROWTYPE;
1675
1676 BEGIN
1677
1678 IF (p_course_cd IS NULL) THEN
1679 OPEN c_term_info;
1680 FETCH c_term_info INTO l_term_info;
1681 CLOSE c_term_info;
1682 ELSE
1683 OPEN c_term_info_ccd;
1684 FETCH c_term_info_ccd INTO l_term_info;
1685 CLOSE c_term_info_ccd;
1686 END IF;
1687
1688 p_cal_type := l_term_info.load_cal_type;
1689 p_sequence_number := l_term_info.load_ci_sequence_number;
1690 p_term_desc := l_term_info.load_description;
1691
1692 END enrp_get_enr_term;
1693
1694 /* This Function returns Lead Instructor of a Unit Section if it exists otherwise returns NULL */
1695 FUNCTION get_lead_instructor_name(
1696 p_uoo_id IN NUMBER
1697 ) RETURN VARCHAR2 AS
1698 CURSOR cur_lead_instr_info IS
1699 SELECT first_name || ' ' || last_name Instructor_Name
1700 FROM igs_pe_person_base_v a,
1701 igs_ps_usec_tch_resp b
1702 WHERE b.uoo_id = p_uoo_id AND
1703 b.INSTRUCTOR_ID = a.person_id AND
1704 b.LEAD_INSTRUCTOR_FLAG = 'Y';
1705
1706 l_lead_instr_info cur_lead_instr_info%ROWTYPE;
1707 l_instructor_name varchar2(301);
1708
1709 BEGIN
1710 OPEN cur_lead_instr_info;
1711 FETCH cur_lead_instr_info INTO l_lead_instr_info;
1712 IF cur_lead_instr_info%FOUND THEN
1713 l_instructor_name := l_lead_instr_info.Instructor_Name;
1714 ELSE
1715 l_instructor_name := NULL;
1716 END IF;
1717 CLOSE cur_lead_instr_info;
1718 RETURN l_instructor_name;
1719
1720 END get_lead_instructor_name;
1721
1722
1723 -------------------------------------------------------------------------------
1724 --Created by : kkillams ( Oracle IDC)
1725 --Date created: 22-MAY-2002
1726 --
1727 --Purpose: This function returns the max waitlist defined at organization level
1728 -- for teaching calendar
1729 --Known limitations/enhancements and/or remarks:
1730 --
1731 --Change History:
1732 --Who When What
1733 ------------------------------------------------------------------------------
1734 FUNCTION get_max_std_wait_org_level(
1735 p_owner_org_unit_cd IN VARCHAR2,
1736 p_cal_type IN VARCHAR2,
1737 p_sequence_number IN NUMBER
1738 ) RETURN NUMBER AS
1739 CURSOR cur_org IS SELECT max_stud_per_wlst FROM igs_en_or_unit_wlst
1740 WHERE org_unit_cd = p_owner_org_unit_cd AND
1741 cal_type = p_cal_type AND
1742 sequence_number = p_sequence_number;
1743 lv_max_stud_per_wlst igs_en_or_unit_wlst.max_stud_per_wlst%TYPE DEFAULT NULL;
1744 BEGIN
1745 OPEN cur_org;
1746 FETCH cur_org INTO lv_max_stud_per_wlst;
1747 IF cur_org%NOTFOUND THEN
1748 close cur_org;
1749 RETURN NULL;
1750 ELSE
1751 close cur_org;
1752 RETURN lv_max_stud_per_wlst;
1753 END IF;
1754 END get_max_std_wait_org_level;
1755
1756 -------------------------------------------------------------------------------
1757 --Created by : TNATARAJ
1758 --Date created: 19-JUL-2002
1759 --
1760 --Purpose: This function returns the Name(s) of the Instructor(s)
1761 -- associated for the given Uoo_id. If multiple instructors
1762 -- are associated , the names of all the instructors are concatenated
1763 -- and the concatenated string is returned- bug # 2446078
1764 --Known limitations/enhancements and/or remarks:
1765 --
1766 --Change History:
1767 --Who When What
1768 ------------------------------------------------------------------------------
1769 FUNCTION get_usec_instructor_names
1770 (
1771 p_uoo_id in number
1772 ) return varchar2
1773 is
1774
1775 lv_instr_names varchar2(32000) ;
1776 CURSOR c_usec_instructor_names
1777 IS
1778 SELECT hz.person_last_name || ', '||hz.person_first_name || ' '||hz.person_middle_name instructor_name
1779 FROM
1780 hz_parties hz,
1781 igs_ps_uso_instrctrs a
1782 WHERE a.instructor_id(+) = hz.party_id
1783 AND a.unit_section_occurrence_id = p_uoo_id
1784 ORDER BY 1 ;
1785
1786 BEGIN
1787 FOR c_usec_instructor_names_data in c_usec_instructor_names
1788 LOOP
1789 IF lv_instr_names IS NOT NULL
1790 THEN
1791 lv_instr_names := lv_instr_names ||'<BR>'||c_usec_instructor_names_data.instructor_name ;
1792 ELSE
1793 lv_instr_names := c_usec_instructor_names_data.instructor_name;
1794 END IF ;
1795 END LOOP ;
1796 RETURN lv_instr_names ;
1797
1798 END get_usec_instructor_names ;
1799
1800 PROCEDURE Enrp_Get_Usec_Group (
1801 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
1802 p_return_status OUT NOCOPY VARCHAR2,
1803 p_group_type OUT NOCOPY igs_lookups_view.meaning%TYPE,
1804 p_group_name OUT NOCOPY igs_ps_usec_x_grp.usec_x_listed_group_name%TYPE
1805 ) AS
1806
1807 ------------------------------------------------------------------------------------
1808 --Created by : pradhakr
1809 --Date created: 27-Oct-2002
1810 --
1811 --Purpose:
1812 -- Procedure added to get the Group Name and Group Type of the passed Unit Section,
1813 -- if it belongs to any cross-listed / meet with group.
1814 -- Added as part of Cross List / Meet With DLD. bug# 2599929
1815 --
1816 --Known limitations/enhancements and/or remarks:
1817 --
1818 --Change History:
1819 --Who When What
1820 --
1821 -------------------------------------------------------------------------------------
1822
1823
1824 -- Cursor to get the enrollment maximum in cross listed group
1825 CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1826 SELECT grp.usec_x_listed_group_name
1827 FROM igs_ps_usec_x_grpmem grpmem,
1828 igs_ps_usec_x_grp grp
1829 WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
1830 AND grpmem.uoo_id = l_uoo_id;
1831
1832
1833 -- Cursor to get the enrollment maximum in Meet with class group
1834 CURSOR c_meet_with_cls (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1835 SELECT grp.class_meet_group_name
1836 FROM igs_ps_uso_clas_meet ucm,
1837 igs_ps_uso_cm_grp grp
1838 WHERE grp.class_meet_group_id = ucm.class_meet_group_id
1839 AND ucm.uoo_id = l_uoo_id;
1840
1841 -- Cursor to get the meaning for the lookup_code 'CROSS_LIST' / 'MEET_WITH'
1842 CURSOR c_group_type(l_lookup_code igs_lookups_view.lookup_code%type) IS
1843 SELECT meaning
1844 FROM igs_lookups_view
1845 WHERE lookup_type = 'IGS_PS_USEC_GROUPS'
1846 AND lookup_code = l_lookup_code;
1847
1848 l_cross_listed_row c_cross_listed%ROWTYPE;
1849 l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
1850
1851
1852 BEGIN
1853
1854 p_return_status := 'N';
1855
1856 -- Check whether the unit section belongs to any cross listed group If yes, return the status as Y
1857 -- and get the group name and type.
1858
1859 OPEN c_cross_listed(p_uoo_id);
1860 FETCH c_cross_listed INTO l_cross_listed_row;
1861
1862 IF c_cross_listed%FOUND THEN
1863 p_return_status := 'Y';
1864 p_group_name := l_cross_listed_row.usec_x_listed_group_name;
1865
1866 OPEN c_group_type('CROSS_LIST');
1867 fetch c_group_type INTO p_group_type;
1868 CLOSE c_group_type;
1869
1870 ELSE
1871
1872 -- Check whether the Unit Section belongs to Meet with class. If yes, return the status as Y
1873 -- and get the group name and type.
1874
1875 OPEN c_meet_with_cls(p_uoo_id);
1876 FETCH c_meet_with_cls INTO l_meet_with_cls_row;
1877
1878 IF c_meet_with_cls%FOUND THEN
1879 p_return_status := 'Y';
1880 p_group_name := l_meet_with_cls_row.class_meet_group_name;
1881
1882 -- Cursor to get the group type
1883 OPEN c_group_type('MEET_WITH');
1884 fetch c_group_type INTO p_group_type;
1885 CLOSE c_group_type;
1886 ELSE
1887 -- If the Unit Section doesn't belongs to any group then retunr the status as N
1888 p_return_status := 'N';
1889 p_group_type := NULL;
1890 p_group_name := NULL;
1891 END IF;
1892 CLOSE c_meet_with_cls;
1893 END IF;
1894 CLOSE c_cross_listed;
1895
1896 END Enrp_Get_Usec_Group;
1897
1898
1899 FUNCTION Enrp_Get_Enr_Max_Act (
1900 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE
1901 ) RETURN VARCHAR2 IS
1902
1903 ------------------------------------------------------------------------------------
1904 --Created by : pradhakr
1905 --Date created: 27-Oct-2002
1906 --
1907 --Purpose:
1908 -- The following function returns the concatenated value of Enrollment Maximum and
1909 -- Actual Enrollment if the passed unit section belongs to any cross-listed / meet with group.
1910 -- Added as part of Cross List / Meet With DLD. bug# 2599929
1911 --
1912 --Known limitations/enhancements and/or remarks:
1913 --
1914 --Change History:
1915 --Who When What
1916 -- pradhakr 30-Dec-02 Changed the data type of the variable l_max from
1917 -- igs_ps_usec_x_grp.max_enr_group to
1918 -- igs_ps_usec_lim_wlst.enrollment_maximum.
1919 -------------------------------------------------------------------------------------
1920
1921 -- Cursor to get the enrollment maximum in cross listed group
1922 CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1923 SELECT grp.max_enr_group, grpmem.usec_x_listed_group_id
1924 FROM igs_ps_usec_x_grpmem grpmem,
1925 igs_ps_usec_x_grp grp
1926 WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
1927 AND grpmem.uoo_id = l_uoo_id;
1928
1929
1930 -- Cursor to get the enrollment maximum in Meet with class group
1931 CURSOR c_meet_with_cls (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1932 SELECT grp.max_enr_group, ucm.class_meet_group_id
1933 FROM igs_ps_uso_clas_meet ucm,
1934 igs_ps_uso_cm_grp grp
1935 WHERE grp.class_meet_group_id = ucm.class_meet_group_id
1936 AND ucm.uoo_id = l_uoo_id;
1937
1938 -- Cursor to get the actual enrollment of all the unit sections that belong
1939 -- to this class listed group.
1940 CURSOR c_actual_enr_crs_lst(l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
1941 SELECT SUM(enrollment_actual)
1942 FROM igs_ps_unit_ofr_opt uoo,
1943 igs_ps_usec_x_grpmem ugrp
1944 WHERE uoo.uoo_id = ugrp.uoo_id
1945 AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id;
1946
1947
1948 -- Cursor to get the actual enrollment of all the unit sections that belong
1949 -- to this meet with class group.
1950 CURSOR c_actual_enr_meet_cls(l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE) IS
1951 SELECT SUM(enrollment_actual)
1952 FROM igs_ps_unit_ofr_opt uoo,
1953 igs_ps_uso_clas_meet ucls
1954 WHERE uoo.uoo_id = ucls.uoo_id
1955 AND ucls.class_meet_group_id = l_class_meet_group_id;
1956
1957
1958 -- Cursor to get the meaning for the lookup_code 'CROSS_LIST' / 'MEET_WITH'
1959 CURSOR c_group_type(l_lookup_code igs_lookups_view.lookup_code%type) IS
1960 SELECT meaning
1961 FROM igs_lookups_view
1962 WHERE lookup_type = 'IGS_PS_USEC_GROUPS'
1963 AND lookup_code = l_lookup_code;
1964
1965 -- Cursor to fetch the enrollment Maximum value defined at Unit Section level
1966 CURSOR cur_usec_enr_max( p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1967 SELECT enrollment_maximum
1968 FROM igs_ps_usec_lim_wlst
1969 WHERE uoo_id = p_uoo_id;
1970
1971 -- cursor to fetch the enrollment maximum value defined at unit level
1972 CURSOR cur_unit_enr_max( p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1973 SELECT enrollment_maximum
1974 FROM igs_ps_unit_ver
1975 WHERE (unit_cd , version_number ) IN (SELECT unit_cd , version_number
1976 FROM igs_ps_unit_ofr_opt
1977 WHERE uoo_id = p_uoo_id);
1978
1979 --
1980 -- Cursor to find the Actual Enrollment of the Unit section
1981 --
1982 CURSOR c_enroll_actual (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1983 SELECT enrollment_actual
1984 FROM igs_ps_unit_ofr_opt
1985 WHERE uoo_id = cp_uoo_id;
1986
1987
1988 l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE;
1989 l_max igs_ps_usec_lim_wlst.enrollment_maximum%TYPE;
1990 l_act igs_ps_unit_ofr_opt.enrollment_actual%TYPE;
1991 l_total_enrollment NUMBER;
1992 l_actual_enr igs_ps_unit_ofr_opt_all.enrollment_actual%TYPE;
1993 l_meet_meaning igs_lookups_view.meaning%TYPE;
1994 l_cross_meaning igs_lookups_view.meaning%TYPE;
1995 l_usec_partof_group BOOLEAN;
1996 l_cat_enr_act varchar2(100);
1997 l_setup_found NUMBER;
1998 l_temp VARCHAR2(10);
1999 l_cross_listed_row c_cross_listed%ROWTYPE;
2000 l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
2001
2002 BEGIN
2003
2004 -- Check whether the unit section belongs to any cross listed group. If so then get the
2005 -- maximim enrollment limit in the group level. If it is not null then get the actual enrollment
2006 -- of all the unit sections which belong to that group and return the concatenated string of
2007 -- Maximim enrollment and actual enrollment.
2008 -- Incase if the maximum enrollment limit is not set in the group level the get it from
2009 -- Unit Section level or in the unit level.
2010
2011 l_usec_partof_group := FALSE;
2012
2013 OPEN c_cross_listed(p_uoo_id);
2014 FETCH c_cross_listed INTO l_cross_listed_row ;
2015
2016 IF c_cross_listed%FOUND THEN
2017
2018 -- Get the maximum enrollment limit from the group level.
2019 IF l_cross_listed_row.max_enr_group IS NULL THEN
2020 l_usec_partof_group := FALSE;
2021
2022 ELSE
2023 l_usec_partof_group := TRUE;
2024 l_max := l_cross_listed_row.max_enr_group;
2025
2026 -- Get the actual enrollment count of all the unit sections that belongs to the cross listed group.
2027 OPEN c_actual_enr_crs_lst(l_cross_listed_row.usec_x_listed_group_id);
2028 FETCH c_actual_enr_crs_lst INTO l_act;
2029 CLOSE c_actual_enr_crs_lst;
2030
2031 OPEN c_group_type('CROSS_LIST');
2032 FETCH c_group_type INTO l_cross_meaning;
2033 CLOSE c_group_type;
2034
2035 -- Concatenate the meaning with the maximim enrollment limit and actual enrollment limit.
2036 -- The format should be like 'Cross Listed <BR> 10(5)'
2037 l_cat_enr_act := l_cross_meaning||' '||l_max||'('||(NVL(to_char(l_act),'0'))||')';
2038
2039 IF c_cross_listed%ISOPEN THEN
2040 CLOSE c_cross_listed;
2041 END IF;
2042 RETURN l_cat_enr_act;
2043
2044 END IF;
2045
2046 ELSE
2047
2048 OPEN c_meet_with_cls(p_uoo_id);
2049 FETCH c_meet_with_cls INTO l_meet_with_cls_row ;
2050
2051 IF c_meet_with_cls%FOUND THEN
2052
2053 -- Get the maximum enrollment limit from the group level.
2054 IF l_meet_with_cls_row.max_enr_group IS NULL THEN
2055 l_usec_partof_group := FALSE;
2056
2057 ELSE
2058 l_usec_partof_group := TRUE;
2059 l_max := l_meet_with_cls_row.max_enr_group;
2060
2061 -- Get the actual enrollment count of all the unit sections that belongs to
2062 -- the meet with class group.
2063 OPEN c_actual_enr_meet_cls(l_meet_with_cls_row.class_meet_group_id);
2064 FETCH c_actual_enr_meet_cls INTO l_act;
2065 CLOSE c_actual_enr_meet_cls;
2066
2067 OPEN c_group_type('MEET_WITH');
2068 FETCH c_group_type INTO l_meet_meaning;
2069 CLOSE c_group_type;
2070
2071 -- Concatenate the meaning with the maximim enrollment limit and actual enrollment limit.
2072 -- The format should be like 'Meet With <BR> 10(5)'
2073 l_cat_enr_act := l_meet_meaning||' '||l_max||'('||(NVL(to_char(l_act),'0'))||')';
2074
2075 IF c_meet_with_cls%ISOPEN THEN
2076 CLOSE c_meet_with_cls;
2077 END IF;
2078 RETURN l_cat_enr_act;
2079 END IF;
2080
2081 ELSE
2082 l_usec_partof_group := FALSE;
2083 END IF;
2084
2085 IF c_meet_with_cls%ISOPEN THEN
2086 CLOSE c_meet_with_cls;
2087 END IF;
2088 END IF;
2089
2090 IF c_cross_listed%ISOPEN THEN
2091 CLOSE c_cross_listed;
2092 END IF;
2093
2094 IF l_usec_partof_group = FALSE THEN
2095
2096 -- If the Unit Section passed doesn't belong to any of the group then
2097 -- check the maximum enrollment limit in the Unit Section level / Unit level.
2098
2099 OPEN cur_usec_enr_max(p_uoo_id);
2100 FETCH cur_usec_enr_max INTO l_max;
2101 CLOSE cur_usec_enr_max;
2102
2103
2104 IF l_max IS NULL THEN
2105 -- Get the maximum enrollment limit from Unit level.
2106 OPEN cur_unit_enr_max(p_uoo_id);
2107 FETCH cur_unit_enr_max INTO l_max;
2108 CLOSE cur_unit_enr_max;
2109 END IF;
2110
2111 -- get the actual enrollment limit.
2112 OPEN c_enroll_actual(p_uoo_id);
2113 FETCH c_enroll_actual INTO l_act;
2114 CLOSE c_enroll_actual;
2115 l_temp := l_max;
2116 l_cat_enr_act := NVL(l_temp,'-')||'('||(NVL(to_char(l_act),'0'))||')';
2117 RETURN l_cat_enr_act;
2118
2119 END IF;
2120 RETURN l_cat_enr_act;
2121
2122 END Enrp_Get_Enr_Max_Act;
2123
2124
2125 FUNCTION Enrp_Chk_Usec_Group (
2126 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE
2127 ) RETURN VARCHAR2 IS
2128
2129 ------------------------------------------------------------------------------------
2130 --Created by : pradhakr
2131 --Date created: 30-Oct-2002
2132 --
2133 --Purpose:
2134 --
2135 -- This function returns the value of Y/N deponding upon whether the unit section
2136 -- belongs to any group and whether the maximum group limit is set or not . It is
2137 -- called from the view IGS_SS_EN_ENROLL_CART_RSLT_V. Added as part of Cross List /
2138 -- Meet With DLD. bug# 2599929
2139 --
2140 --Known limitations/enhancements and/or remarks:
2141 --
2142 --Change History:
2143 --Who When What
2144 --
2145 -------------------------------------------------------------------------------------
2146 p_return_status VARCHAR2(1);
2147 p_group_type igs_lookups_view.meaning%TYPE;
2148 p_group_name igs_ps_usec_x_grp.usec_x_listed_group_name%TYPE;
2149
2150 -- Cursor to get the enrollment maximum in cross listed group
2151 CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2152 SELECT grp.max_enr_group, grpmem.usec_x_listed_group_id
2153 FROM igs_ps_usec_x_grpmem grpmem,
2154 igs_ps_usec_x_grp grp
2155 WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
2156 AND grpmem.uoo_id = l_uoo_id;
2157
2158
2159 -- Cursor to get the enrollment maximum in Meet with class group
2160 CURSOR c_meet_with_cls (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2161 SELECT grp.max_enr_group, ucm.class_meet_group_id
2162 FROM igs_ps_uso_clas_meet ucm,
2163 igs_ps_uso_cm_grp grp
2164 WHERE grp.class_meet_group_id = ucm.class_meet_group_id
2165 AND ucm.uoo_id = l_uoo_id;
2166
2167
2168 l_usec_partof_group BOOLEAN;
2169 l_cross_listed_row c_cross_listed%ROWTYPE;
2170 l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
2171
2172 BEGIN
2173
2174 l_usec_partof_group := FALSE;
2175
2176 OPEN c_cross_listed(p_uoo_id);
2177 FETCH c_cross_listed INTO l_cross_listed_row ;
2178
2179 -- Check in Cross Listed group
2180 IF c_cross_listed%FOUND THEN
2181
2182 IF l_cross_listed_row.max_enr_group IS NULL THEN
2183 l_usec_partof_group := FALSE;
2184 ELSE
2185 l_usec_partof_group := TRUE;
2186 END IF;
2187
2188 ELSE
2189
2190 OPEN c_meet_with_cls(p_uoo_id);
2191 FETCH c_meet_with_cls INTO l_meet_with_cls_row ;
2192
2193 -- Check in Meet with class group
2194 IF c_meet_with_cls%FOUND THEN
2195 IF l_meet_with_cls_row.max_enr_group IS NULL THEN
2196 l_usec_partof_group := FALSE;
2197 ELSE
2198 l_usec_partof_group := TRUE;
2199 END IF;
2200 ELSE
2201 l_usec_partof_group := FALSE;
2202 END IF;
2203
2204 END IF;
2205
2206 IF c_meet_with_cls%ISOPEN THEN
2207 CLOSE c_meet_with_cls;
2208 END IF;
2209
2210 IF c_cross_listed%ISOPEN THEN
2211 CLOSE c_cross_listed;
2212 END IF;
2213
2214 IF l_usec_partof_group = TRUE THEN
2215 p_return_status := 'Y';
2216 RETURN p_return_status;
2217 ELSE
2218 p_return_status := 'N';
2219 RETURN p_return_status;
2220 END IF;
2221
2222 END Enrp_Chk_Usec_Group;
2223
2224 FUNCTION get_core_disp_unit(
2225 p_person_id IN NUMBER ,
2226 p_program_cd IN VARCHAR2 ,
2227 p_uoo_id IN NUMBER )
2228
2229 ------------------------------------------------------------------
2230 --Created by : Parul Tandon, Oracle IDC
2231 --Date created: 06-OCT-2003
2232 --
2233 --Purpose: This Function checks whether the given unit section is
2234 --a core unit or not in the current pattern of study for the given
2235 --student program attempt.
2236 --
2237 --Known limitations/enhancements and/or remarks:
2238 --
2239 --Change History:
2240 --Who When What
2241 -------------------------------------------------------------------
2242
2243 RETURN VARCHAR2
2244 IS
2245
2246 --
2247 -- Cursor to find the Unit Code
2248 --
2249 CURSOR cur_unit_cd (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
2250 SELECT unit_cd, unit_class
2251 FROM igs_ps_unit_ofr_opt
2252 WHERE uoo_id = p_uoo_id;
2253
2254 --
2255 -- Cursor to find the meaning of lookup code CORE
2256 --
2257 CURSOR cur_get_lkp_meaning IS
2258 SELECT meaning
2259 FROM igs_lookup_values lkup, IGS_EN_SS_DISP_STPS en
2260 WHERE lkup.lookup_type = 'IGS_EN_CORE_IND'
2261 AND lkup.lookup_code = en.core_req_ind;
2262
2263
2264 l_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
2265 l_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE;
2266 l_core_meaning igs_lookup_values.meaning%TYPE;
2267
2268 BEGIN
2269 -- Get the Unit Code
2270 OPEN cur_unit_cd(p_uoo_id);
2271 FETCH cur_unit_cd INTO l_unit_cd,l_unit_class;
2272 CLOSE cur_unit_cd;
2273
2274 IF igs_en_gen_009.enrp_check_usec_core(p_person_id,p_program_cd,p_uoo_id) = 'CORE' THEN
2275 -- Get the meaning of lookup code
2276 OPEN cur_get_lkp_meaning;
2277 FETCH cur_get_lkp_meaning INTO l_core_meaning;
2278 CLOSE cur_get_lkp_meaning;
2279
2280 RETURN l_unit_cd||'/'||l_unit_class||'('||l_core_meaning||')';
2281 ELSE
2282 RETURN l_unit_cd||'/'||l_unit_class;
2283 END IF;
2284 END get_core_disp_unit;
2285
2286
2287 PROCEDURE get_enr_cat_step(
2288 p_person_id IN NUMBER ,
2289 p_program_cd IN VARCHAR2 ,
2290 p_enr_cat_prc_step IN VARCHAR2,
2291 p_ret_status OUT NOCOPY VARCHAR2 ) AS
2292 ------------------------------------------------------------------
2293 --Created by : knaraset, Oracle IDC
2294 --Date created: 03-NOV-2003
2295 --
2296 --Purpose:This Function checks whether the given enrollment category step is defined in the system or not
2297 --it returns TRUE if the step is defined and FALSE whe the step is not defined.
2298 --
2299 --Known limitations/enhancements and/or remarks:
2300 --
2301 --Change History:
2302 --Who When What
2303 -------------------------------------------------------------------
2304
2305 -- Cursor to check whether the given step is defined
2306 CURSOR c_enr_cat_prc_step (cp_enrolment_cat VARCHAR2,
2307 cp_student_comm_type VARCHAR2,
2308 cp_enr_method_type VARCHAR2,
2309 cp_enrolment_step_type VARCHAR2) IS
2310 SELECT 'TRUE'
2311 FROM igs_en_cat_prc_step
2312 WHERE enrolment_cat = cp_enrolment_cat AND
2313 (s_student_comm_type = cp_student_comm_type OR
2314 s_student_comm_type = 'ALL' ) AND
2315 enr_method_type = cp_enr_method_type AND
2316 s_enrolment_step_type = cp_enrolment_step_type;
2317
2318 l_enrollment_category igs_en_cat_prc_step.enrolment_cat%TYPE;
2319 l_comm_type igs_en_cat_prc_step.s_student_comm_type%TYPE;
2320 l_enr_method_type igs_en_cat_prc_step.enr_method_type%TYPE;
2321 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
2322 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
2323 l_enrol_cal_type igs_ca_type.cal_type%TYPE;
2324 l_enrol_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2325 l_message VARCHAR2(300);
2326 l_ret_status VARCHAR2(30);
2327 l_dummy VARCHAR2(300);
2328
2329 BEGIN
2330
2331 p_ret_status := 'FALSE';
2332
2333 -- Get the superior academic calendar instance
2334 Igs_En_Gen_015.get_academic_cal
2335 (
2336 p_person_id => p_person_id,
2337 p_course_cd => p_program_cd,
2338 p_acad_cal_type => l_acad_cal_type,
2339 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
2340 p_message => l_message,
2341 p_effective_dt => SYSDATE
2342 );
2343 -- Get the enrollment category and commencement type
2344 l_enrollment_category := Igs_En_Gen_003.enrp_get_enr_cat(
2345 p_person_id,
2346 p_program_cd,
2347 l_acad_cal_type,
2348 l_acad_ci_sequence_number,
2349 NULL,
2350 l_enrol_cal_type,
2351 l_enrol_sequence_number,
2352 l_comm_type,
2353 l_dummy);
2354 IF l_comm_type = 'BOTH' THEN
2355 l_comm_type :='ALL';
2356 END IF;
2357
2358 -- Get the enrollment method type
2359 Igs_En_Gen_017.enrp_get_enr_method(
2360 p_enr_method_type => l_enr_method_type,
2361 p_error_message => l_message,
2362 p_ret_status => l_ret_status);
2363
2364 -- check whether the given step is defined or not
2365 OPEN c_enr_cat_prc_step (l_enrollment_category,l_comm_type,l_enr_method_type,p_enr_cat_prc_step);
2366 FETCH c_enr_cat_prc_step INTO p_ret_status;
2367 CLOSE c_enr_cat_prc_step;
2368
2369 END get_enr_cat_step;
2370
2371 FUNCTION get_stud_yop_unit_set(
2372 p_person_id IN NUMBER ,
2373 p_program_cd IN VARCHAR2 ,
2374 p_term_cal_type IN VARCHAR2,
2375 p_term_sequence_number IN NUMBER)
2376 RETURN VARCHAR2 AS
2377 ------------------------------------------------------------------
2378 --Created by : knaraset, Oracle IDC
2379 --Date created: 03-NOV-2003
2380 --
2381 --Purpose: This function will return the current unit set title for the given student program attempt
2382 --
2383 --Known limitations/enhancements and/or remarks:
2384 --
2385 --Change History:
2386 --rvangala 09-Dec-2003 Changed logic to consider multiple census terms,
2387 -- if the term has more than one census date defined
2388 -------------------------------------------------------------------
2389
2390 -- cursor to fetch the unit set title
2391 CURSOR c_us_title (cp_person_id NUMBER,cp_program_cd VARCHAR2,cp_term_census_date DATE) IS
2392 SELECT us.title
2393 FROM igs_as_su_setatmpt susa ,
2394 igs_en_unit_set us ,
2395 igs_en_unit_set_cat usc
2396 WHERE susa.person_id = cp_person_id
2397 AND susa.course_cd = cp_program_cd
2398 AND susa.student_confirmed_ind = 'Y'
2399 AND cp_term_census_date
2400 BETWEEN susa.selection_dt
2401 AND NVL(susa.rqrmnts_complete_dt,NVL(susa.end_dt, cp_term_census_date))
2402 AND susa.unit_set_cd = us.unit_set_cd
2403 AND us.unit_set_cat = usc.unit_set_cat
2404 AND usc.s_unit_set_cat = 'PRENRL_YR'
2405 ORDER BY susa.selection_dt DESC;
2406
2407 -- cursor to fetch census date values
2408 CURSOR c_term_cen_dates (cp_cal_type IN VARCHAR2, cp_cal_seq_number IN NUMBER) IS
2409 SELECT NVL (absolute_val,
2410 igs_ca_gen_001.calp_get_alias_val (
2411 dai.dt_alias,
2412 dai.sequence_number,
2413 dai.cal_type,
2414 dai.ci_sequence_number
2415 )
2416 ) AS term_census_date
2417 FROM igs_ge_s_gen_cal_con sgcc,
2418 igs_ca_da_inst dai
2419 WHERE sgcc.s_control_num = 1
2420 AND dai.dt_alias = sgcc.census_dt_alias
2421 AND dai.cal_type = cp_cal_type
2422 AND dai.ci_sequence_number = cp_cal_seq_number
2423 ORDER by 1 desc;
2424
2425 l_us_title igs_en_unit_set.title%TYPE;
2426 BEGIN
2427 -- Check whether the pre-enrollment YOP profile is set to Y
2428 IF fnd_profile.value('IGS_PS_PRENRL_YEAR_IND') = 'Y' THEN
2429
2430 -- loop through the census dates for given term cal type and sequence number
2431 FOR l_rec_cen_dates IN c_term_cen_dates(p_term_cal_type,
2432 p_term_sequence_number) LOOP
2433
2434 -- fetch unit set title for most recent unit set attempt
2435 OPEN c_us_title(p_person_id,p_program_cd,l_rec_cen_dates.term_census_date);
2436 FETCH c_us_title INTO l_us_title;
2437
2438 -- if unit set title for most recent unit set attempt exists
2439 -- exit and return title
2440 IF c_us_title%FOUND THEN
2441 CLOSE c_us_title;
2442 EXIT;
2443 END IF;
2444
2445 CLOSE c_us_title;
2446
2447 END LOOP;
2448
2449 RETURN l_us_title;
2450
2451 ELSE
2452 -- return NULL as pre-enrollment YOP profile is not set or set to N
2453 RETURN NULL;
2454 END IF;
2455
2456 END get_stud_yop_unit_set;
2457
2458
2459 FUNCTION get_pri_prg_title(
2460 p_person_id IN NUMBER ,
2461 p_program_cd IN VARCHAR2,
2462 p_term_cal_type IN VARCHAR2,
2463 p_term_sequence_number IN NUMBER)
2464 RETURN VARCHAR2 AS
2465 ------------------------------------------------------------------
2466 --Created by : knaraset, Oracle IDC
2467 --Date created: 03-NOV-2003
2468 --
2469 --Purpose: This Function returns the title of the given program or given primary program
2470 --
2471 --Known limitations/enhancements and/or remarks:
2472 --
2473 --Change History:
2474 --Who When What
2475 -------------------------------------------------------------------
2476
2477 -- get the program title
2478 CURSOR c_prg_title (cp_person_id NUMBER,cp_program_cd VARCHAR2,cp_term_cal VARCHAR2,cp_term_seq_num NUMBER) IS
2479 SELECT pv.title
2480 FROM igs_en_stdnt_ps_att_all sca,
2481 igs_ps_ver_all pv
2482 WHERE pv.course_cd = sca.course_cd
2483 AND pv.version_number = sca.version_number
2484 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT')
2485 AND sca.person_id = cp_person_id
2486 AND sca.course_cd = cp_program_cd
2487 AND ((igs_en_spa_terms_api.get_spat_primary_prg(cp_person_id,cp_program_cd,cp_term_cal,cp_term_seq_num) = 'PRIMARY') OR
2488 (NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N')= 'N')
2489 );
2490
2491 l_prg_title igs_ps_ver_all.title%TYPE;
2492
2493 BEGIN
2494
2495 OPEN c_prg_title(p_person_id,p_program_cd,p_term_cal_type,p_term_sequence_number);
2496 FETCH c_prg_title INTO l_prg_title;
2497 CLOSE c_prg_title;
2498
2499 RETURN l_prg_title;
2500
2501 END get_pri_prg_title;
2502
2503 FUNCTION get_sec_prg_title(
2504 p_person_id IN NUMBER ,
2505 p_program_cd IN VARCHAR2,
2506 p_program_version IN NUMBER,
2507 p_term_cal_type IN VARCHAR2,
2508 p_term_sequence_number IN NUMBER)
2509 RETURN VARCHAR2 AS
2510 ------------------------------------------------------------------
2511 --Created by : knaraset, Oracle IDC
2512 --Date created: 03-NOV-2003
2513 --
2514 --Purpose: This Function returns return the concatenated titles of
2515 -- all the secondary programs in the same career of the given program
2516 --
2517 --Known limitations/enhancements and/or remarks:
2518 --
2519 --Change History:
2520 --Who When What
2521 -------------------------------------------------------------------
2522
2523 -- get the program title
2524 CURSOR c_sec_prg_title (cp_person_id NUMBER,cp_program_cd VARCHAR2,cp_version_number NUMBER,
2525 cp_term_cal VARCHAR2,cp_term_seq_num NUMBER) IS
2526 SELECT pv.title
2527 FROM igs_en_stdnt_ps_att_all sca,
2528 igs_ps_ver_all pv
2529 WHERE pv.course_cd = sca.course_cd
2530 AND pv.version_number = sca.version_number
2531 AND sca.course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT')
2532 AND sca.person_id = cp_person_id
2533 AND igs_en_spa_terms_api.get_spat_primary_prg(cp_person_id,cp_program_cd,cp_term_cal,cp_term_seq_num) <> 'PRIMARY'
2534 AND pv.course_type = (SELECT course_type
2535 FROM igs_ps_ver_all
2536 WHERE course_cd = cp_program_cd
2537 AND version_number = cp_version_number);
2538
2539 l_sec_prg_title VARCHAR2(2000);
2540
2541 BEGIN
2542
2543 IF fnd_profile.value('CAREER_MODEL_ENABLED') = 'Y' THEN
2544
2545 FOR l_sec_prg_rec IN c_sec_prg_title(p_person_id,p_program_cd,p_program_version,p_term_cal_type,p_term_sequence_number) LOOP
2546 l_sec_prg_title := l_sec_prg_title ||', '||l_sec_prg_rec.title;
2547 END LOOP;
2548
2549 RETURN ' '||SUBSTR(l_sec_prg_title,3);
2550 ELSE
2551 RETURN NULL;
2552 END IF;
2553
2554 END get_sec_prg_title;
2555 FUNCTION enrf_is_sup_Sub(
2556 p_uoo_id IN NUMBER
2557 )
2558 ------------------------------------------------------------------
2559 --Created by : Satya Vanukuri, Oracle IDC
2560 --Date created: 29-OCT-2003
2561 --
2562 --Purpose: This Function checks whether the given unit section is
2563 --a superior, subordinate or none as part of placements build #3052438.
2564 --If subordiante , it returns the superior unit section
2565 --if superior , it returns the passed uoo-id
2566 --else it returns null
2567 --
2568 --Known limitations/enhancements and/or remarks:
2569 --
2570 --Change History:
2571 --Who When What
2572 -------------------------------------------------------------------
2573
2574 RETURN VARCHAR2 IS
2575
2576 CURSOR chk_rel IS
2577 SELECT relation_type ,sup_uoo_Id
2578 FROM igs_ps_unit_ofr_opt
2579 WHERE uoo_id = p_uoo_id;
2580
2581 l_rel_type igs_ps_unit_ofr_opt.relation_type%TYPE;
2582 l_sup_uoo_id igs_ps_unit_ofr_opt.sup_uoo_id%TYPE;
2583
2584 BEGIN
2585 OPEN chk_rel;
2586 FETCH chk_rel INTO l_rel_type, l_sup_uoo_id;
2587 CLOSE chk_rel;
2588
2589 IF nvl(l_rel_type,'NONE') = 'NONE' THEN
2590 RETURN NULL;
2591 ELSIF l_rel_type = 'SUBORDINATE' THEN
2592 RETURN l_sup_uoo_id;
2593 ELSIF l_rel_type = 'SUPERIOR' THEN
2594 RETURN p_uoo_Id;
2595 END IF;
2596 END enrf_is_sup_Sub;
2597
2598 FUNCTION GET_SUP_SUB_UOO_IDS (
2599 p_uoo_id IN NUMBER,
2600 p_relation_type IN VARCHAR2,
2601 p_sup_uoo_id IN NUMBER
2602 )
2603 ------------------------------------------------------------------
2604 --Created by : Satya Vanukuri, Oracle IDC
2605 --Date created: 29-OCT-2003
2606 --
2607 --Purpose: This Function returns a string of conctenated subordinate uoo_ids
2608 --if the parameter p_uoo_Id is superior , if it is subordinate
2609 --it returns p_sup_uoo_id else it returns null
2610 --
2611 --Known limitations/enhancements and/or remarks:
2612 --
2613 --Change History:
2614 --Who When What
2615 -------------------------------------------------------------------
2616
2617 RETURN VARCHAR2 AS
2618
2619 recordexists BOOLEAN;
2620 ret_value VARCHAR2 (2000);
2621
2622 CURSOR c_sub_uoo_ids IS
2623 SELECT uoo_id
2624 FROM igs_ps_unit_ofr_opt
2625 WHERE sup_uoo_id = p_uoo_id;
2626
2627 BEGIN
2628
2629 IF NVL (p_relation_type,'NONE') = 'SUPERIOR' THEN
2630 ret_value := NULL;
2631 FOR v_sub_uoo_ids IN c_sub_uoo_ids LOOP
2632 IF ret_value IS NULL THEN
2633 ret_value := TO_CHAR(v_sub_uoo_ids.uoo_id);
2634 ELSE
2635 ret_value := ret_value || ','||TO_CHAR(v_sub_uoo_ids.uoo_id);
2636 END IF;
2637 END LOOP;
2638 ELSIF NVL (p_relation_type,'NONE') = 'SUBORDINATE' THEN
2639 ret_value := p_sup_uoo_id;
2640 END IF;
2641 RETURN ret_value;
2642 END get_sup_sub_uoo_ids;
2643
2644 FUNCTION GET_SUP_SUB_DETAILS (
2645 p_uoo_id IN NUMBER,
2646 p_sup_uoo_id IN NUMBER,
2647 p_relation_type IN VARCHAR2
2648 ) RETURN VARCHAR2 AS
2649 ------------------------------------------------------------------
2650 --Created by : Satya Vanukuri, Oracle IDC
2651 --Date created: 29-OCT-2003
2652 --
2653 --Purpose: This Function returns teh relation type of the unit section.
2654 --if the parameter p_uoo_Id is superior , it returns 'SUPERIOR'
2655 --if subordiante it returns 'SBORDINATE' concatenated to the superior unit code
2656 --
2657 --Known limitations/enhancements and/or remarks:
2658 --
2659 --Change History:
2660 --Who When What
2661 -------------------------------------------------------------------
2662 CURSOR c_lkups (CP_LOOKUP_CODE IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE) IS
2663 SELECT meaning
2664 FROM igs_lookup_values
2665 WHERE lookup_code = cp_lookup_code
2666 AND lookup_type = 'UOO_RELATION_TYPE';
2667
2668 CURSOR c_sup_unit_cd (CP_SUP_UOO_ID IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
2669 SELECT unit_cd
2670 FROM igs_ps_unit_ofr_opt
2671 WHERE uoo_id = cp_sup_uoo_id;
2672
2673 v_sup_unit_cd IGS_PS_UNIT_VER.UNIT_CD%TYPE;
2674 l_meaning IGS_LOOKUPS_VIEW.MEANING%TYPE;
2675
2676 BEGIN
2677
2678 IF NVL(p_relation_type,'NONE') = 'NONE' THEN
2679 RETURN NULL;
2680 END IF;
2681
2682 OPEN c_lkups(p_relation_type);
2683 FETCH c_lkups INTO l_meaning;
2684 CLOSE c_lkups;
2685
2686 IF p_relation_type = 'SUPERIOR' THEN
2687 RETURN '<BR>('||l_meaning||')';
2688 ELSIF p_relation_type = 'SUBORDINATE' THEN
2689
2690 IF p_sup_uoo_id IS NOT NULL THEN
2691 OPEN c_sup_unit_cd(p_sup_uoo_id);
2692 FETCH c_sup_unit_cd INTO v_sup_unit_cd;
2693 IF c_sup_unit_cd%FOUND THEN
2694 CLOSE c_sup_unit_cd;
2695 RETURN '<BR>('|| FND_MESSAGE.GET_STRING('IGS','IGS_EN_SUBORDINATE_TO') || ' ' || v_sup_unit_cd||')';
2696 ELSE
2697 CLOSE c_sup_unit_cd;
2698 RETURN NULL;
2699 END IF;
2700 ELSE
2701 RETURN NULL;
2702 END IF;
2703 END IF;
2704
2705 END get_sup_sub_details;
2706
2707 -- Procedure to get the level at which notes is defined for give unit section.
2708 -- Procedure retuns the following values in the out variable p_c_dfn_lvl.
2709 -- 'UNIT_SECTION' - when the notes are defined at unit section level.
2710 -- 'UNIT_OFFERING_PATTERN' - when the notes are defined at unit offering pattern level.
2711 -- 'UNIT_OFFERING' - when the notes are defined at unit offering level.
2712 -- 'UNIT_VERSION' - when the notes are defined at unit version level.
2713 -- 'NOTES_UN_DEFINED' - when the notes are not defined at any of the above levels.
2714 PROCEDURE get_notes_defn_lvl (
2715 p_n_uoo_id IN NUMBER,
2716 p_c_dfn_lvl OUT NOCOPY VARCHAR2) IS
2717 CURSOR c_usec (cp_n_uoo_id IN NUMBER) IS
2718 SELECT 1
2719 FROM IGS_PS_UNT_OFR_OPT_N USEC
2720 WHERE USEC.UOO_ID = cp_n_uoo_id
2721 AND ROWNUM < 2 ;
2722
2723 CURSOR c_uop (cp_n_uoo_id IN NUMBER) IS
2724 SELECT 1
2725 FROM IGS_PS_UNT_OFR_PAT_N UOP,
2726 IGS_PS_UNIT_OFR_OPT_ALL UOO
2727 WHERE UOO.UNIT_CD = UOP.UNIT_CD
2728 AND UOO.VERSION_NUMBER = UOP.VERSION_NUMBER
2729 AND UOO.CAL_TYPE = UOP.CAL_TYPE
2730 AND UOO.CI_SEQUENCE_NUMBER = UOP.CI_SEQUENCE_NUMBER
2731 AND UOO.UOO_ID = cp_n_uoo_id
2732 AND ROWNUM < 2;
2733
2734 CURSOR c_uo (cp_n_uoo_id IN NUMBER) IS
2735 SELECT 1
2736 FROM IGS_PS_UNIT_OFR_NOTE UO,
2737 IGS_PS_UNIT_OFR_OPT_ALL UOO
2738 WHERE UOO.UNIT_CD = UO.UNIT_CD
2739 AND UOO.VERSION_NUMBER = UO.VERSION_NUMBER
2740 AND UOO.CAL_TYPE = UO.CAL_TYPE
2741 AND UOO.UOO_ID = cp_n_uoo_id
2742 AND ROWNUM < 2;
2743
2744 CURSOR c_uv (cp_n_uoo_id IN NUMBER) IS
2745 SELECT 1
2746 FROM IGS_PS_UNIT_VER_NOTE UV,
2747 IGS_PS_UNIT_OFR_OPT_ALL UOO
2748 WHERE UOO.UNIT_CD = UV.UNIT_CD
2749 AND UOO.VERSION_NUMBER = UV.VERSION_NUMBER
2750 AND UOO.UOO_ID = cp_n_uoo_id
2751 AND ROWNUM < 2;
2752
2753 l_n_temp NUMBER;
2754
2755 BEGIN
2756 OPEN c_usec (p_n_uoo_id);
2757 FETCH c_usec INTO l_n_temp;
2758 IF c_usec%FOUND THEN
2759 CLOSE c_usec;
2760 p_c_dfn_lvl := 'UNIT_SECTION';
2761 ELSE
2762 CLOSE c_usec;
2763 OPEN c_uop(p_n_uoo_id);
2764 FETCH c_uop INTO l_n_temp;
2765 IF c_uop%FOUND THEN
2766 CLOSE c_uop;
2767 p_c_dfn_lvl := 'UNIT_OFFERING_PATTERN';
2768 ELSE
2769 CLOSE c_uop;
2770 OPEN c_uo(p_n_uoo_id);
2771 FETCH c_uo INTO l_n_temp;
2772 IF c_uo%FOUND THEN
2773 CLOSE c_uo;
2774 p_c_dfn_lvl := 'UNIT_OFFERING';
2775 ELSE
2776 CLOSE c_uo;
2777 OPEN c_uv(p_n_uoo_id);
2778 FETCH c_uv INTO l_n_temp;
2779 IF c_uv%FOUND THEN
2780 CLOSE c_uv;
2781 p_c_dfn_lvl := 'UNIT_VERSION';
2782 ELSE
2783 CLOSE c_uv;
2784 p_c_dfn_lvl := 'NOTES_UN_DEFINED';
2785 END IF;
2786 END IF;
2787 END IF;
2788 END IF;
2789 END get_notes_defn_lvl;
2790
2791 FUNCTION GET_DUP_SUA_SELECTION (
2792 p_person_id IN NUMBER,
2793 p_src_course_cd IN VARCHAR2,
2794 p_dest_course_cd IN VARCHAR2,
2795 p_uoo_id IN NUMBER
2796 ) RETURN VARCHAR2 AS
2797 ------------------------------------------------------------------
2798 --Created by : Satya Vanukuri, Oracle IDC
2799 --Date created: 23-Dec-2004
2800 --
2801 --Purpose: This Function returns 'Y' if a duplicate unit attempt
2802 --in the source program can be unchecked in the Program Transfer Page.
2803 --
2804 --Known limitations/enhancements and/or remarks:
2805 --
2806 --Change History:
2807 --Who When What
2808 --Somasekar 17th feb 2006 Bug # 5026874
2809 -------------------------------------------------------------------
2810 -- Get the details of
2811 Cursor uoo_attempt_status (
2812 cp_person_id IN NUMBER ,
2813 cp_course_cd IN VARCHAR2,
2814 cp_uoo_id NUMBER) IS
2815 SELECT unit_attempt_status
2816 FROM igs_en_su_Attempt
2817 WHERE person_id = cp_person_id
2818 AND course_cd = cp_course_cd
2819 AND uoo_id = cp_uoo_id;
2820
2821 l_src_status VARCHAR2(20);
2822 l_dest_status VARCHAR2(20);
2823
2824 BEGIN
2825 OPEN uoo_attempt_status(p_person_id, p_src_course_cd, p_uoo_id);
2826 FETCH uoo_attempt_status INTO l_src_status;
2827 CLOSE uoo_attempt_status;
2828
2829 IF l_src_status = 'COMPLETED' OR l_src_status = 'DISCONTIN' THEN
2830
2831 --chk if unit exists in destination with duplicate status for corresponding completed unit in source
2832 OPEN uoo_attempt_status(p_person_id, p_dest_course_cd, p_uoo_id);
2833 FETCH uoo_attempt_status INTO l_dest_status;
2834
2835 CLOSE uoo_attempt_status;
2836 IF l_dest_status IS NOT NULL AND l_dest_status = 'DUPLICATE' THEN
2837
2838 --completed unit can be unchecked in the page
2839 RETURN 'N';
2840 END IF;
2841
2842 RETURN 'Y';
2843 END IF;
2844
2845 IF l_src_status = 'DUPLICATE' THEN
2846
2847 --chk if unit exists in destination
2848 OPEN uoo_attempt_status(p_person_id, p_dest_course_cd, p_uoo_id);
2849 FETCH uoo_attempt_status INTO l_dest_status;
2850 CLOSE uoo_attempt_status;
2851
2852 IF l_dest_status IS NOT NULL THEN
2853 --unit can be unchecked in the page
2854 RETURN 'N';
2855 END IF;
2856
2857 RETURN 'Y';
2858 END IF;
2859
2860 RETURN 'Y';
2861
2862 END get_dup_sua_selection;
2863
2864 FUNCTION get_title_for_unit(p_unit_cd IN VARCHAR2, p_version IN NUMBER) RETURN VARCHAR2
2865 IS
2866 Cursor cur_title(cp_unit_cd VARCHAR2, cp_version NUMBER) IS
2867 Select title from igs_ps_unit_ver
2868 where unit_cd=cp_unit_cd
2869 and version_number=cp_version;
2870
2871 l_title igs_ps_unit_ver.title%TYPE;
2872 BEGIN
2873 OPEN cur_title(p_unit_cd,p_version);
2874 FETCH cur_title INTO l_title;
2875 CLOSE cur_title;
2876
2877 RETURN l_title;
2878
2879 END get_title_for_unit;
2880
2881 FUNCTION get_max_waitlist_for_unit(p_uoo_id IN NUMBER, p_unit_cd IN VARCHAR2,
2882 p_version IN NUMBER,
2883 p_cal_type IN VARCHAR2, p_sequence_number IN NUMBER,
2884 p_owner_org_unit_cd IN VARCHAR2) RETURN NUMBER
2885 IS
2886
2887 Cursor c_check(cp_uoo_id NUMBER) IS
2888 select max_students_per_waitlist
2889 from igs_ps_usec_lim_wlst
2890 where uoo_id=cp_uoo_id;
2891
2892 Cursor c_check2(cp_unit_cd VARCHAR2, cp_version NUMBER,
2893 cp_cal_type VARCHAR2, cp_sequence_number NUMBER) IS
2894 select max_students_per_waitlist
2895 from igs_ps_unit_ofr_pat
2896 where unit_cd=cp_unit_cd
2897 and version_number=cp_version
2898 and cal_type=cp_cal_type
2899 and ci_sequence_number=cp_sequence_number
2900 and delete_flag = 'N';
2901
2902 l_result igs_en_or_unit_wlst.max_stud_per_wlst%TYPE DEFAULT NULL;
2903 BEGIN
2904 OPEN c_check(p_uoo_id);
2905 FETCH c_check INTO l_result;
2906 CLOSE c_check;
2907
2908 IF l_result IS NOT NULL THEN
2909 RETURN l_result;
2910 END IF;
2911
2912 OPEN c_check2(p_unit_cd,p_version,p_cal_type,p_sequence_number);
2913 FETCH c_check2 INTO l_result;
2914 CLOSE c_check2;
2915
2916 IF l_result IS NOT NULL THEN
2917 RETURN l_result;
2918 END IF;
2919
2920 RETURN get_max_std_wait_org_level(
2921 p_owner_org_unit_cd ,
2922 p_cal_type ,
2923 p_sequence_number);
2924
2925 END get_max_waitlist_for_unit;
2926
2927
2928 FUNCTION get_enroll_max_for_unit(p_uooid IN NUMBER, p_unit_cd IN VARCHAR2,
2929 p_version IN NUMBER) RETURN NUMBER
2930 IS
2931 Cursor c_check1(cp_uooid NUMBER) IS
2932 select enrollment_maximum
2933 from igs_ps_usec_lim_wlst
2934 where uoo_id=cp_uooid;
2935
2936 Cursor c_check2(cp_unit_cd VARCHAR2,
2937 cp_version NUMBER) IS
2938 select enrollment_maximum
2939 from igs_ps_unit_ver
2940 where unit_cd=cp_unit_cd
2941 and version_number=cp_version;
2942
2943 l_result igs_ps_usec_lim_wlst.enrollment_maximum%TYPE DEFAULT NULL;
2944 BEGIN
2945 OPEN c_check1(p_uooid);
2946 FETCH c_check1 INTO l_result;
2947 CLOSE c_check1;
2948
2949 IF l_result IS NOT NULL THEN
2950 return l_result;
2951 END IF;
2952
2953 OPEN c_check2(p_unit_cd,p_version);
2954 FETCH c_check2 INTO l_result;
2955 CLOSE c_check2;
2956
2957 RETURN l_result;
2958
2959 END get_enroll_max_for_unit;
2960
2961
2962 FUNCTION get_enroll_min_for_unit(p_uooid IN NUMBER, p_unit_cd IN VARCHAR2,
2963 p_version IN NUMBER) RETURN NUMBER
2964 IS
2965 Cursor c_check1(cp_uooid NUMBER) IS
2966 select enrollment_minimum
2967 from igs_ps_usec_lim_wlst
2968 where uoo_id=cp_uooid;
2969
2970 Cursor c_check2(cp_unit_cd VARCHAR2,
2971 cp_version NUMBER) IS
2972 select enrollment_minimum
2973 from igs_ps_unit_ver
2974 where unit_cd=cp_unit_cd
2975 and version_number=cp_version;
2976
2977 l_result igs_ps_usec_lim_wlst.enrollment_minimum%TYPE DEFAULT NULL;
2978 BEGIN
2979 OPEN c_check1(p_uooid);
2980 FETCH c_check1 INTO l_result;
2981 CLOSE c_check1;
2982
2983 IF l_result IS NOT NULL THEN
2984 return l_result;
2985 END IF;
2986
2987 OPEN c_check2(p_unit_cd,p_version);
2988 FETCH c_check2 INTO l_result;
2989 CLOSE c_check2;
2990
2991 RETURN l_result;
2992
2993 END get_enroll_min_for_unit;
2994
2995
2996
2997 -- Function to get alias value for the given calendar instance and date alias.
2998 FUNCTION get_alias_val (p_c_cal_type IN VARCHAR2,
2999 p_n_seq_num IN NUMBER,
3000 p_c_dt_alias IN VARCHAR2) RETURN DATE IS
3001
3002 ------------------------------------------------------------------
3003 --Created by : Somasekar, Oracle IDC
3004 --Date created: 17-May-2005
3005 --
3006 --Purpose: Function to get alias value for the given
3007 -- calendar instance and date alias
3008 --
3009 --Known limitations/enhancements and/or remarks:
3010 --
3011 --Change History:
3012 --Who When What
3013 -------------------------------------------------------------------
3014
3015 CURSOR c_alias_val (cp_c_cal_type IN VARCHAR2,
3016 cp_n_seq_num IN NUMBER,
3017 cp_c_dt_alias IN VARCHAR2) IS
3018 SELECT alias_val
3019 FROM igs_ca_da_inst_v
3020 WHERE cal_type = cp_c_cal_type
3021 AND ci_sequence_number = cp_n_seq_num
3022 AND dt_alias = cp_c_dt_alias
3023 ORDER BY alias_val DESC;
3024
3025 l_d_alias_val igs_ca_da_inst_v.alias_val%TYPE;
3026
3027 BEGIN
3028 OPEN c_alias_val(p_c_cal_type, p_n_seq_num, p_c_dt_alias);
3029 FETCH c_alias_val INTO l_d_alias_val;
3030 CLOSE c_alias_val;
3031 RETURN l_d_alias_val;
3032 END get_alias_val;
3033
3034
3035 -- Function to check whether timeslot is open or close for a student
3036 -- returns true if the timeslot is open otherwise false
3037 FUNCTION stu_timeslot_open (p_n_person_id IN NUMBER,
3038 p_c_person_type IN VARCHAR2,
3039 p_c_program_cd IN VARCHAR2,
3040 p_c_cal_type IN VARCHAR2,
3041 p_n_seq_num IN NUMBER) RETURN BOOLEAN IS
3042 ------------------------------------------------------------------
3043 --Created by : Somasekar, Oracle IDC
3044 --Date created: 17-May-2005
3045 --
3046 --Purpose: Function to check whether timeslot is open or close for a student
3047 --
3048 --Known limitations/enhancements and/or remarks:
3049 --
3050 --Change History:
3051 --Who When What
3052 -------------------------------------------------------------------
3053
3054 l_c_msg VARCHAR2(2000);
3055 l_c_ret_sts VARCHAR2(10);
3056 l_c_en_meth_type igs_en_method_type.enr_method_type%TYPE;
3057 l_c_en_cal igs_ca_inst.cal_type%type;
3058 l_n_en_seq igs_ca_inst.sequence_number%type;
3059 l_c_en_com igs_en_cat_prc_dtl.S_STUDENT_COMM_TYPE%TYPE;
3060 l_c_acad_cal igs_ca_inst.cal_type%type;
3061 l_n_acad_seq igs_ca_inst.sequence_number%type;
3062 l_d_acad_st_dt igs_ca_inst.start_dt%type;
3063 l_d_acad_ed_dt igs_ca_inst.end_dt%type;
3064 l_c_alternate_cd igs_ca_inst.alternate_code%type;
3065 l_c_en_cat igs_en_enrolment_cat.enrolment_cat%TYPE;
3066 l_c_en_ctgs VARCHAR2(200);
3067 l_c_notify_flag igs_en_cpd_ext.notification_flag%TYPE;
3068 l_step_override_limit igs_en_elgb_ovr_step.step_override_limit%TYPE;
3069 l_step_override BOOLEAN := FALSE;
3070 lv_timeslot_rec_found BOOLEAN := FALSE;
3071
3072 CURSOR c_stud_timeslot (cp_cal_type igs_en_timeslot_para.cal_type%TYPE,
3073 cp_sequence_number igs_en_timeslot_para.sequence_number%TYPE) IS
3074 SELECT tr.start_dt_time,
3075 tr.end_dt_time
3076 FROM igs_en_timeslot_rslt tr,
3077 igs_en_timeslot_para tp
3078 WHERE tr.person_id = p_n_person_id
3079 AND tr.igs_en_timeslot_para_id = tp.igs_en_timeslot_para_id
3080 AND tp.cal_type = cp_cal_type
3081 AND tp.sequence_number = cp_sequence_number;
3082 rec_stud_timeslot c_stud_timeslot%ROWTYPE;
3083
3084 BEGIN
3085 -- Call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
3086 igs_en_gen_017.enrp_get_enr_method(
3087 p_enr_method_type => l_c_en_meth_type,
3088 p_error_message => l_c_msg,
3089 p_ret_status => l_c_ret_sts);
3090
3091 -- get the enrollment method message
3092 IF l_c_msg IS NOT NULL THEN
3093 RETURN FALSE;
3094 END IF;
3095
3096 -- get the academic calendar of the given Load Calendar
3097 l_c_alternate_cd := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
3098 p_cal_type => p_c_cal_type,
3099 p_ci_sequence_number => p_n_seq_num,
3100 p_acad_cal_type => l_c_acad_cal,
3101 p_acad_ci_sequence_number => l_n_acad_seq,
3102 p_acad_ci_start_dt => l_d_acad_st_dt,
3103 p_acad_ci_end_dt => l_d_acad_ed_dt,
3104 p_message_name => l_c_msg );
3105
3106 -- get the academic method message
3107 IF l_c_msg IS NOT NULL THEN
3108 RETURN FALSE;
3109 END IF;
3110
3111 -- get the enrollment category for the given calendar.
3112 l_c_en_cat := igs_en_gen_003.enrp_get_enr_cat(
3113 p_n_person_id,
3114 p_c_program_cd,
3115 l_c_acad_cal,
3116 l_n_acad_seq,
3117 NULL,
3118 l_c_en_cal,
3119 l_n_en_seq,
3120 l_c_en_com,
3121 l_c_en_ctgs);
3122
3123 IF l_c_en_com = 'BOTH' THEN
3124 l_c_en_com :='ALL';
3125 END IF;
3126
3127 l_c_notify_flag := igs_ss_enr_details.get_notification(
3128 p_person_type => p_c_person_type,
3129 p_enrollment_category => l_c_en_cat,
3130 p_comm_type => l_c_en_com,
3131 p_enr_method_type => l_c_en_meth_type,
3132 p_step_group_type => 'PERSON',
3133 p_step_type => 'CHK_TIME_PER',
3134 p_person_id => p_n_person_id,
3135 p_message => l_c_msg
3136 ) ;
3137
3138 -- get the notification method message
3139 IF l_c_msg IS NOT NULL THEN
3140 RETURN FALSE;
3141 END IF;
3142 -- if the step is not defined then notification flag will be null
3143 -- if the step is configured as 'Warn' then returns WARN.
3144 -- Either is step is not defined or configured as warn then no need to evaluate the step at all.
3145 IF NVL(l_c_notify_flag,'WARN') ='WARN' Then
3146 RETURN TRUE;
3147 ELSE
3148 -- check the step is overridden for the given load calendar/teaching period or not
3149 l_step_override := igs_en_gen_015.validation_step_is_overridden(
3150 p_eligibility_step_type => 'CHK_TIME_PER',
3151 p_load_cal_type => p_c_cal_type ,
3152 p_load_cal_seq_number => p_n_seq_num ,
3153 p_person_id => p_N_person_id,
3154 p_uoo_id => NULL,
3155 p_step_override_limit => l_step_override_limit );
3156
3157 IF l_step_override THEN
3158 RETURN TRUE;
3159 END IF;
3160 -- now fetch the timeslot based on the obtained cal_type and seq number and if the values are null
3161 -- pass p_load_calendar_type and p_load_cal_sequence_number
3162
3163 lv_timeslot_rec_found := FALSE;
3164 FOR rec_stud_timeslot IN c_stud_timeslot (p_c_cal_type, p_n_seq_num )
3165 LOOP
3166 -- Timeslot record found
3167 lv_timeslot_rec_found := TRUE;
3168 IF (SYSDATE >= rec_stud_timeslot.start_dt_time) OR (rec_stud_timeslot.start_dt_time IS NULL ) THEN
3169 --Student is eligible
3170 RETURN TRUE;
3171 END IF;
3172 END LOOP;
3173
3174 IF NOT lv_timeslot_rec_found THEN
3175 -- No Timeslot records defined/alloted for the Student
3176 RETURN TRUE;
3177 ELSE
3178 RETURN FALSE;
3179 END IF;
3180
3181 END IF;
3182
3183 END stu_timeslot_open;
3184
3185
3186 FUNCTION get_tba_desc RETURN VARCHAR2 IS
3187 BEGIN
3188 RETURN g_tba_desc;
3189 END get_tba_desc;
3190
3191
3192 FUNCTION get_nsd_desc RETURN VARCHAR2 IS
3193 BEGIN
3194 RETURN g_nsd_desc;
3195 END get_nsd_desc;
3196
3197 FUNCTION get_uso_instructors(p_n_uso_id IN NUMBER) RETURN VARCHAR2 IS
3198 CURSOR c_instr (cp_n_occurs_id IN NUMBER) IS
3199 SELECT pe.last_name || ', ' || pe.first_name || ' ' || pe.middle_name name
3200 FROM igs_ps_uso_instrctrs instr,
3201 igs_pe_person_base_v pe
3202 WHERE instr.unit_section_occurrence_id = cp_n_occurs_id AND
3203 instr.instructor_id = pe.person_id;
3204 l_c_instr VARCHAR2(32000);
3205 l_b_found boolean;
3206 BEGIN
3207 l_b_found := false;
3208 FOR rec_instr IN c_instr(p_n_uso_id)
3209 LOOP
3210 l_b_found := true;
3211 l_c_instr := l_c_instr || rec_instr.name || '; ';
3212 END LOOP;
3213 IF l_b_found THEN
3214 l_c_instr := substr(l_c_instr,0,length(l_c_instr) -2);
3215 END IF;
3216 return l_c_instr;
3217 END get_uso_instructors;
3218
3219
3220 FUNCTION get_none_desc RETURN VARCHAR2 IS
3221 BEGIN
3222 RETURN get_meaning ('CALL_NUMBER','NONE');
3223 END get_none_desc;
3224
3225
3226 FUNCTION get_meeting_pattern(p_n_uoo_id IN NUMBER) RETURN VARCHAR2
3227 ------------------------------------------------------------------
3228 --Created by : rvangala
3229 --Date created: 24-May-2005
3230 --
3231 --Purpose: Function to get the meeting pattern for a unit section
3232 -- to display in self service
3233 --
3234 --Known limitations/enhancements and/or remarks:
3235 --
3236 --Change History:
3237 --Who When What
3238 -------------------------------------------------------------------
3239 AS
3240 --Cursor to get all the occurrences of given unit section.
3241 CURSOR c_uso_dtls (cp_n_uoo_id IN NUMBER) IS
3242 SELECT
3243 TO_CHAR(NVL(NVL(USO.START_DATE,US.UNIT_SECTION_START_DATE),CA.START_DT),'DD MON YYYY') || ' - ' ||
3244 TO_CHAR(NVL(NVL(USO.END_DATE,US.UNIT_SECTION_END_DATE),CA.END_DT),'DD MON YYYY') effective_date,
3245 uso.building_code,
3246 uso.room_code,
3247 uso.to_be_announced,
3248 uso.no_set_day_ind,
3249 NVL(
3250 DECODE(uso.monday, 'Y', 'M', NULL) ||
3251 DECODE(uso.tuesday, 'Y', 'Tu', NULL) ||
3252 DECODE(uso.wednesday, 'Y', 'W', NULL) ||
3253 DECODE(uso.thursday, 'Y', 'Th', NULL) ||
3254 DECODE(uso.friday, 'Y', 'F', NULL) ||
3255 DECODE(uso.saturday, 'Y', 'Sa', NULL) ||
3256 DECODE(uso.sunday, 'Y', 'Su', NULL),'TBA') meetings,
3257 TO_CHAR(uso.start_time,'hh:miam') start_time,
3258 TO_CHAR(uso.end_time, 'hh:miam') end_time
3259 FROM igs_ps_usec_occurs_all USO,
3260 igs_ps_unit_ofr_opt_all US,
3261 igs_ca_inst_all CA
3262 WHERE uso.uoo_id = cp_n_uoo_id AND
3263 uso.uoo_id = us.uoo_id AND
3264 us.cal_type = ca.cal_type AND
3265 us.ci_sequence_number = ca.sequence_number
3266 ORDER BY uso.unit_section_occurrence_id;
3267
3268 l_c_meet_info VARCHAR2(32000);
3269
3270 -- Internal function to get the building code for the given building identifier
3271 FUNCTION get_building_code(p_n_building_id IN NUMBER) RETURN VARCHAR2 IS
3272 CURSOR c_building_code (cp_n_building_id IN NUMBER) IS
3273 SELECT building_cd
3274 FROM igs_ad_building_all
3275 WHERE building_id = cp_n_building_id;
3276 l_c_building_cd igs_ad_building_all.building_cd%TYPE;
3277 BEGIN
3278 OPEN c_building_code (p_n_building_id );
3279 FETCH c_building_code INTO l_c_building_cd;
3280 CLOSE c_building_code;
3281 RETURN l_c_building_cd;
3282 END get_building_code;
3283
3284 -- Internal function to get the room code for the given room identifier
3285 FUNCTION get_room_code(p_n_room_id IN NUMBER) RETURN VARCHAR2 IS
3286 CURSOR c_room_code (cp_n_room_id IN NUMBER) IS
3287 SELECT room_cd
3288 FROM igs_ad_room_all
3289 WHERE room_id = cp_n_room_id;
3290 l_c_room_cd igs_ad_room_all.room_cd%TYPE;
3291 BEGIN
3292 OPEN c_room_code (p_n_room_id );
3293 FETCH c_room_code INTO l_c_room_cd;
3294 CLOSE c_room_code;
3295 RETURN l_c_room_cd;
3296 END get_room_code;
3297
3298 -- function to display dummy data
3299 -- Called when the unit section does not have occurrences then need to display meeting information
3300 -- as if an to be announced occurrence exists.
3301 FUNCTION get_dummy_data(p_n_uoo_id IN NUMBER) RETURN VARCHAR2 IS
3302 CURSOR c_uoo(cp_n_uoo_id IN NUMBER) IS
3303 SELECT NVL(us.unit_section_start_date,ca.start_dt) start_date,
3304 NVL(us.unit_section_end_date, ca.end_dt) end_date
3305 FROM igs_ps_unit_ofr_opt_all us,
3306 igs_ca_inst_all ca
3307 WHERE us.uoo_id = cp_n_uoo_id AND
3308 us.cal_type = ca.cal_type AND
3309 us.ci_sequence_number = ca.sequence_number;
3310
3311 rec_uoo c_uoo%ROWTYPE;
3312
3313 BEGIN
3314 OPEN c_uoo(p_n_uoo_id);
3315 FETCH c_uoo INTO rec_uoo;
3316 CLOSE c_uoo;
3317 return rec_uoo.start_date ||' - ' || rec_uoo.end_date || '; ' || g_tba_desc ;
3318 END get_dummy_data;
3319
3320
3321 BEGIN
3322 -- loop through the unit section occurrences
3323 FOR rec_uso_dtls IN c_uso_dtls(p_n_uoo_id)
3324 LOOP
3325 l_c_meet_info := l_c_meet_info || rec_uso_dtls.effective_date || '; ';
3326 IF rec_uso_dtls.to_be_announced = 'Y' THEN
3327 l_c_meet_info := l_c_meet_info || g_tba_desc;
3328 ELSIF rec_uso_dtls.no_set_day_ind = 'Y' THEN
3329 l_c_meet_info := l_c_meet_info || g_nsd_desc;
3330 ELSE
3331 IF rec_uso_dtls.building_code IS NOT NULL THEN
3332 l_c_meet_info := l_c_meet_info || get_building_code(rec_uso_dtls.building_code) || '; ';
3333 ELSE
3334 l_c_meet_info := l_c_meet_info || 'TBA' || '; ';
3335 END IF;
3336 IF rec_uso_dtls.room_code IS NOT NULL THEN
3337 l_c_meet_info := l_c_meet_info || get_room_code(rec_uso_dtls.room_code) || '; ';
3338 ELSE
3339 l_c_meet_info := l_c_meet_info || 'TBA' || '; ';
3340 END IF;
3341 l_c_meet_info := l_c_meet_info || rec_uso_dtls.meetings || '; '
3342 || rec_uso_dtls.start_time || ' - ' || rec_uso_dtls.end_time;
3343 END IF;
3344 l_c_meet_info := l_c_meet_info || ' <BR> ' ;
3345 END LOOP;
3346
3347 IF l_c_meet_info IS NULL THEN
3348 l_c_meet_info := get_dummy_data(p_n_uoo_id);
3349 ELSE
3350 l_c_meet_info := substr(l_c_meet_info,0,length(l_c_meet_info) -6);
3351 END IF;
3352 RETURN l_c_meet_info;
3353
3354 END get_meeting_pattern;
3355
3356
3357
3358 FUNCTION get_usec_instructors(p_n_uoo_id IN NUMBER) RETURN VARCHAR2 AS
3359 CURSOR c_uso (cp_n_uoo_id IN NUMBER) IS
3360 SELECT unit_section_occurrence_id
3361 FROM igs_ps_usec_occurs_all
3362 WHERE uoo_id = cp_n_uoo_id
3363 ORDER BY unit_section_occurrence_id;
3364 CURSOR c_instr (cp_n_occurs_id IN NUMBER) IS
3365 SELECT pe.last_name || ', ' || pe.first_name || ' ' || pe.middle_name name
3366 FROM igs_ps_uso_instrctrs instr,
3367 igs_pe_person_base_v pe
3368 WHERE instr.unit_section_occurrence_id = cp_n_occurs_id AND
3369 instr.uso_instructor_id = pe.person_id;
3370 l_c_instr VARCHAR2(32000);
3371 l_c_uso_instr VARCHAR2(32000);
3372 BEGIN
3373 FOR rec_uso IN c_uso(p_n_uoo_id)
3374 LOOP
3375 l_c_uso_instr := NULL;
3376 l_c_uso_instr := get_uso_instructors(rec_uso.unit_section_occurrence_id);
3377 l_c_instr := l_c_instr || l_c_uso_instr || ' <BR> ' ;
3378 END LOOP;
3379 IF l_c_instr IS NOT NULL THEN
3380 l_c_instr := substr(l_c_instr,0,length(l_c_instr) -6);
3381 END IF;
3382 return l_c_instr;
3383 END get_usec_instructors;
3384
3385 /**
3386 This Function Returns the Title for the Unit Section for the given uoo_id
3387 **/
3388 FUNCTION get_us_title(p_n_uoo_id IN NUMBER) RETURN VARCHAR2 IS
3389
3390 CURSOR c_us_title(cp_n_uoo_id IN NUMBER) IS
3391 SELECT t.title
3392 FROM igs_ps_usec_ref t
3393 WHERE t.uoo_id = cp_n_uoo_id;
3394
3395 CURSOR c_uv_title(cp_n_uoo_id IN NUMBER) IS
3396 SELECT uv.title
3397 FROM igs_ps_unit_ofr_opt_all us,
3398 igs_ps_unit_ver_all uv
3399 WHERE us.uoo_id = cp_n_uoo_id
3400 AND us.unit_cd = uv.unit_cd
3401 AND us.version_number = uv.version_number;
3402
3403 l_c_title igs_ps_unit_ver_all.title%TYPE;
3404 BEGIN
3405 OPEN c_us_title (p_n_uoo_id);
3406 FETCH c_us_title INTO l_c_title;
3407 CLOSE c_us_title;
3408
3409 IF l_c_title IS NULL THEN
3410 OPEN c_uv_title(p_n_uoo_id);
3411 FETCH c_uv_title INTO l_c_title;
3412 CLOSE c_uv_title;
3413 END IF;
3414
3415 RETURN l_c_title;
3416
3417 END get_us_title;
3418
3419
3420 FUNCTION get_rule_text(p_rule_type IN VARCHAR2, p_n_uoo_id IN NUMBER) RETURN VARCHAR2
3421 ------------------------------------------------------------------
3422 --Created by : rvangala
3423 --Date created: 24-May-2005
3424 --
3425 --Purpose: Function to get the rule text for a rule type and unit section
3426 -- to display in self service
3427 --
3428 --Known limitations/enhancements and/or remarks:
3429 --
3430 --Change History:
3431 --Who When What
3432 -------------------------------------------------------------------
3433 IS
3434 --cursor to look up the rule code in unit section version rules
3435 CURSOR c_usec_ru (cp_n_uoo_id IN NUMBER, cp_c_rul_call_cd IN VARCHAR2) IS
3436 SELECT rul_sequence_number
3437 FROM igs_ps_usec_ru
3438 WHERE uoo_id = p_n_uoo_id
3439 AND s_rule_call_cd = cp_c_rul_call_cd;
3440
3441 --cursor to look up the rule code in unit version rules
3442 CURSOR c_unit_ru(cp_n_uoo_id IN NUMBER, cp_c_rul_call_cd IN VARCHAR2) IS
3443 SELECT ru.rul_sequence_number
3444 FROM igs_ps_unit_ver_ru ru,
3445 igs_ps_unit_ofr_opt_all uoo
3446 WHERE uoo.uoo_id = p_n_uoo_id
3447 AND uoo.unit_cd = ru.unit_cd
3448 AND uoo.version_number = ru.version_number
3449 AND ru.s_rule_call_cd = cp_c_rul_call_cd;
3450
3451 l_n_seq_num igs_ps_usec_ru.rul_sequence_number%TYPE;
3452 l_c_us_cal_cd igs_ps_usec_ru.s_rule_call_cd%TYPE;
3453 BEGIN
3454 IF p_rule_type = 'COREQ' THEN
3455 l_c_us_cal_cd := 'USECCOREQ';
3456 ELSIF p_rule_type = 'PREREQ' THEN
3457 l_c_us_cal_cd := 'USECPREREQ';
3458 END IF;
3459
3460 --check for rule at unit section level
3461 OPEN c_usec_ru(p_n_uoo_id,l_c_us_cal_cd);
3462 FETCH c_usec_ru INTO l_n_seq_num;
3463 CLOSE c_usec_ru;
3464
3465 IF l_n_seq_num IS NULL THEN
3466 --check for rule at unit level
3467 OPEN c_unit_ru(p_n_uoo_id, p_rule_type);
3468 FETCH c_unit_ru INTO l_n_seq_num;
3469 CLOSE c_unit_ru;
3470 END IF;
3471
3472 --get the rule text
3473 IF l_n_seq_num IS NULL THEN
3474 return get_none_desc;
3475 ELSE
3476 return igs_ru_gen_003.rulp_get_rule(l_n_seq_num);
3477 END IF;
3478
3479 END get_rule_text;
3480
3481
3482 -- returns the apportioned credit points for a unit section
3483 FUNCTION get_apor_credits(p_uoo_id IN NUMBER,
3484 p_override_enrolled_cp IN NUMBER,
3485 p_term_cal_type IN VARCHAR2,
3486 p_term_seq_num IN NUMBER
3487 ) RETURN NUMBER
3488 ------------------------------------------------------------------
3489 --Created by : rvangala
3490 --Date created: 24-May-2005
3491 --
3492 --Purpose: Function to get the apportioned credit points for a unit section
3493 --
3494 --Known limitations/enhancements and/or remarks:
3495 --
3496 --Change History:
3497 --Who When What
3498 -------------------------------------------------------------------
3499 IS
3500 -- get unit section details
3501 CURSOR c_unit_dtls(cp_uoo_id NUMBER)
3502 IS
3503 SELECT unit_cd,version_number,cal_type,ci_sequence_number
3504 FROM igs_ps_unit_ofr_opt uoo
3505 WHERE uoo_id = cp_uoo_id;
3506
3507 l_unit_dtls c_unit_dtls%ROWTYPE;
3508 l_dummy1 NUMBER;
3509 l_dummy2 NUMBER;
3510 l_dummy3 NUMBER;
3511 l_dummy4 NUMBER;
3512
3513 l_result NUMBER;
3514
3515 BEGIN
3516
3517 OPEN c_unit_dtls(p_uoo_id);
3518 FETCH c_unit_dtls INTO l_unit_dtls;
3519 CLOSE c_unit_dtls;
3520
3521 --calculate the apportioned credit points
3522 l_result := igs_en_prc_load.enrp_clc_sua_load(
3523 l_unit_dtls.unit_cd,
3524 l_unit_dtls.version_number,
3525 l_unit_dtls.cal_type,
3526 l_unit_dtls.ci_sequence_number,
3527 p_term_cal_type,
3528 p_term_seq_num,
3529 p_override_enrolled_cp,
3530 null,
3531 l_dummy1,
3532 p_uoo_id,
3533 'N',
3534 l_dummy2,
3535 l_dummy3,
3536 l_dummy4);
3537
3538 return l_result;
3539
3540 END get_apor_credits;
3541
3542 FUNCTION get_billable_credit_points(p_uoo_id IN IGS_PS_UNIT_OFR_OPT_ALL.uoo_id%TYPE)
3543 RETURN NUMBER
3544 ------------------------------------------------------------------
3545 --Created by : rvangala
3546 --Date created: 24-May-2005
3547 --
3548 --Purpose: Function to get the billable credit points for a unit section
3549 -- to display in self service
3550 --
3551 --Known limitations/enhancements and/or remarks:
3552 --
3553 --Change History:
3554 --Who When What
3555 -------------------------------------------------------------------
3556 IS
3557
3558 l_uv_enrolled_cp IGS_PS_UNIT_VER.enrolled_credit_points%TYPE;
3559 l_uv_billing_cp IGS_PS_UNIT_VER.billing_hrs%TYPE;
3560 l_uv_audit_cp IGS_PS_UNIT_VER.billing_credit_points%TYPE;
3561
3562 BEGIN
3563 IGS_PS_VAL_UV.get_cp_values(p_uoo_id, l_uv_enrolled_cp, l_uv_billing_cp,
3564 l_uv_audit_cp);
3565 RETURN l_uv_billing_cp;
3566 END get_billable_credit_points;
3567
3568
3569 FUNCTION is_unit_rule_defined(p_uoo_id IN NUMBER,
3570 p_rule_type IN VARCHAR2) RETURN VARCHAR2
3571 ------------------------------------------------------------------
3572 --Created by : rvangala
3573 --Date created: 24-May-2005
3574 --
3575 --Purpose: Function to check if a prereq/coreq rule has been setup for
3576 -- a unit section at the unit section/unit level.
3577 -- p_rule_type specifies the rule type that needs to be checked for
3578 -- for the unit section. Value can be either PREREQ or COREQ.
3579 --
3580 -- Returns Yes if rule has been setup.
3581 --Known limitations/enhancements and/or remarks:
3582 --
3583 --Change History:
3584 --Who When What
3585 -------------------------------------------------------------------
3586 AS
3587 --Check if the prereq/coreq rule is setup at the unit section level.
3588 CURSOR c_get_usec_rul_seq_no IS
3589 SELECT rul_sequence_number
3590 FROM igs_ps_usec_ru
3591 WHERE uoo_id = p_uoo_id
3592 AND s_rule_call_cd = 'USEC'|| p_rule_type;
3593
3594
3595 --Check if the prereq/coreq rule is setup at the unit version level.
3596 CURSOR c_get_unit_rul_seq_no IS
3597 SELECT rul_sequence_number
3598 FROM igs_ps_unit_ver_ru uvr,
3599 igs_ps_unit_ofr_opt uoo
3600 WHERE uvr.unit_cd = uoo.unit_cd
3601 AND uvr.version_number = uoo.version_number
3602 AND uoo_id = p_uoo_id
3603 AND uvr.s_rule_call_cd = p_rule_type;
3604
3605 l_usec_seq_no c_get_usec_rul_seq_no%ROWTYPE;
3606 l_unit_seq_no c_get_unit_rul_seq_no%ROWTYPE;
3607
3608 BEGIN
3609
3610 OPEN c_get_usec_rul_seq_no;
3611 FETCH c_get_usec_rul_seq_no INTO l_usec_seq_no;
3612 CLOSE c_get_usec_rul_seq_no;
3613
3614 OPEN c_get_unit_rul_seq_no;
3615 FETCH c_get_unit_rul_seq_no INTO l_unit_seq_no;
3616 CLOSE c_get_unit_rul_seq_no;
3617
3618 ---If rule is defined at either unit section or unit level
3619 IF l_usec_seq_no.rul_sequence_number IS NOT NULL OR l_unit_seq_no.rul_sequence_number IS NOT NULL THEN
3620 RETURN igs_ss_enroll_pkg.enrf_get_lookup_meaning('Y','YES_NO');
3621 END IF;
3622
3623 -- rule is not defined at either level so return false
3624 RETURN fnd_message.get_string('IGS','IGS_AZ_NONE');
3625
3626 END is_unit_rule_defined;
3627
3628 /**
3629 This function gets the meaning for the given lookup type and lookup code.
3630 **/
3631 FUNCTION get_meaning (p_c_lkup_type IN VARCHAR2,p_c_lkup_code IN VARCHAR2 ) RETURN VARCHAR2 IS
3632 cursor c_meaning (cp_c_lkup_type IN VARCHAR2, cp_c_lkup_code IN VARCHAR2) IS
3633 SELECT meaning
3634 FROM igs_lookup_values
3635 WHERE lookup_type = cp_c_lkup_type
3636 AND lookup_code = cp_c_lkup_code;
3637
3638 l_c_meaning igs_lookup_values.meaning%TYPE;
3639
3640 BEGIN
3641
3642 OPEN c_meaning(p_c_lkup_type, p_c_lkup_code);
3643 FETCH c_meaning INTO l_c_meaning;
3644 CLOSE c_meaning;
3645 RETURN l_c_meaning;
3646
3647 END get_meaning;
3648
3649
3650
3651 FUNCTION get_special_status(p_uoo_id IN NUMBER) RETURN VARCHAR2
3652 ------------------------------------------------------------------
3653 --Created by : rvangala
3654 --Date created: 24-May-2005
3655 --
3656 --Purpose: Function to check if special permission is required
3657 -- for a unit section
3658 --
3659 -- Returns 'Special' if special permission is required for this unit section
3660 -- Returns 'None' if no special permission is required
3661 --Known limitations/enhancements and/or remarks:
3662 --
3663 --Change History:
3664 --Who When What
3665 -------------------------------------------------------------------
3666 AS
3667 --cursor to check if special permission is setup at unit section level or at unit level
3668 CURSOR cur_usec_details ( cp_uoo_id NUMBER) IS
3669 SELECT NVL(usec.special_permission_ind,'N') special_permission_ind
3670 FROM igs_ps_unit_ofr_opt_all usec
3671 WHERE usec.uoo_id = cp_uoo_id;
3672
3673
3674 cur_usec_details_rec cur_usec_details %ROWTYPE;
3675
3676 BEGIN
3677 OPEN cur_usec_details (p_uoo_id);
3678 FETCH cur_usec_details INTO cur_usec_details_rec;
3679 CLOSE cur_usec_details;
3680
3681 --if special permission has been set up
3682 IF cur_usec_details_rec.special_permission_ind = 'Y' Then
3683 RETURN fnd_message.get_string('IGS','IGS_EN_SPECIAL_LINK');
3684 ELSE
3685 --no special permission has been set up, return 'None'
3686 RETURN fnd_message.get_string('IGS','IGS_AZ_NONE');
3687 END IF;
3688
3689
3690 END get_special_status;
3691
3692
3693 FUNCTION get_audit_status(p_uoo_id IN NUMBER) RETURN VARCHAR2
3694 ------------------------------------------------------------------
3695 --Created by : rvangala
3696 --Date created: 24-May-2005
3697 --
3698 --Purpose: Function to check if audit permission is required
3699 -- for a unit section
3700 --
3701 -- Returns 'Audit' if audit permission is required for this unit section
3702 -- Returns 'None' if no audit permission is required
3703 --Known limitations/enhancements and/or remarks:
3704 --
3705 --Change History:
3706 --Who When What
3707 -------------------------------------------------------------------
3708 AS
3709 --cursor to check if audit permission is setup at unit section level or at unit level
3710 CURSOR cur_usec_details ( cp_uoo_id NUMBER) IS
3711 SELECT NVL(usec.auditable_ind,'N') auditable_ind,
3712 NVL(usec.audit_permission_ind,'N') audit_permission_ind
3713 FROM igs_ps_unit_ofr_opt_all usec
3714 WHERE usec.uoo_id = cp_uoo_id;
3715
3716 cur_usec_details_rec cur_usec_details %ROWTYPE;
3717
3718 BEGIN
3719 OPEN cur_usec_details (p_uoo_id);
3720 FETCH cur_usec_details INTO cur_usec_details_rec;
3721 CLOSE cur_usec_details;
3722
3723 --if audit permission has been set up
3724 IF cur_usec_details_rec.auditable_ind ='Y' AND cur_usec_details_rec.audit_permission_ind ='Y' THEN
3725 RETURN fnd_message.get_string('IGS','IGS_EN_AUDIT_LINK');
3726 ELSE
3727 --no audit permission has been set up, return 'None'
3728 RETURN fnd_message.get_string('IGS','IGS_AZ_NONE');
3729 END IF;
3730
3731 END get_audit_status;
3732
3733
3734
3735 FUNCTION get_special_audit_status(p_uoo_id IN NUMBER) RETURN VARCHAR2
3736 ------------------------------------------------------------------
3737 --Created by : rvangala
3738 --Date created: 24-May-2005
3739 --
3740 --Purpose: Function to check if special or audit permission is required
3741 -- for a unit section
3742 --
3743 -- Returns Special if special permission is set up
3744 -- Returns Audit if audit permission is set up
3745 -- Returns Special / Audit if both special and audit permission are set up
3746 -- Returns None if niether special or audit permission are set up
3747 --Known limitations/enhancements and/or remarks:
3748 --
3749 --Change History:
3750 --Who When What
3751 -------------------------------------------------------------------
3752 AS
3753
3754 CURSOR c_get_spl_audit_perm(cp_uoo_id NUMBER) IS
3755 SELECT NVL(special_permission_ind,'N'),NVL(audit_permission_ind,'N')
3756 FROM igs_ps_unit_ofr_opt
3757 WHERE uoo_id=cp_uoo_id;
3758
3759 l_spl_perm VARCHAR2(1);
3760 l_aud_perm VARCHAR2(1);
3761 l_spl_audit_perm VARCHAR2(100);
3762
3763 BEGIN
3764
3765 OPEN c_get_spl_audit_perm(p_uoo_id);
3766 FETCH c_get_spl_audit_perm INTO l_spl_perm,l_aud_perm;
3767 CLOSE c_get_spl_audit_perm;
3768
3769 IF l_spl_perm = 'N' AND l_aud_perm = 'N' THEN
3770 l_spl_audit_perm := fnd_message.get_string('IGS','IGS_AZ_NONE');
3771 ELSIF l_spl_perm = 'Y' AND l_aud_perm = 'N' THEN
3772 l_spl_audit_perm := fnd_message.get_string('IGS','IGS_EN_SPECIAL_LINK') ;
3773 ELSIF l_spl_perm = 'N' AND l_aud_perm = 'Y' THEN
3774 l_spl_audit_perm := fnd_message.get_string('IGS','IGS_EN_AUDIT_LINK');
3775 ELSIF l_spl_perm = 'Y' AND l_aud_perm = 'Y' THEN
3776 l_spl_audit_perm := fnd_message.get_string('IGS','IGS_EN_SPECIAL_LINK') || '/' || fnd_message.get_string('IGS','IGS_EN_AUDIT_LINK');
3777 END IF;
3778
3779 RETURN l_spl_audit_perm;
3780
3781 END get_special_audit_status;
3782
3783
3784 FUNCTION is_audit_allowed(p_uoo_id IN NUMBER,
3785 p_person_type IN VARCHAR2) RETURN VARCHAR2
3786 ------------------------------------------------------------------
3787 --Created by : rvangala
3788 --Date created: 24-May-2005
3789 --
3790 --Purpose: Function to check if a unit section is auditable or not
3791 --
3792 -- Returns 'Y' if unit section is auditable
3793 -- Returns 'N' if unit section is not auditable
3794 --Known limitations/enhancements and/or remarks:
3795 --
3796 --Change History:
3797 --Who When What
3798 -- smaddali 13-oct-05 modified for bug4666977
3799 -------------------------------------------------------------------
3800 AS
3801 -- cursor to check if audit is allowed, checks first at unit section
3802 -- level, if not found checks at unit level
3803
3804 CURSOR cur_uv_audit_ind (cp_uoo_id NUMBER) IS
3805 Select NVL(usec.auditable_ind,'N') auditable_ind, cal_type,ci_sequence_number
3806 FROM igs_ps_unit_ofr_opt_all usec
3807 WHERE usec.uoo_id = cp_uoo_id;
3808
3809 CURSOR c_usr_conf (cp_person_type VARCHAR2) IS
3810 SELECT UPD_AUDIT_DT_ALIAS
3811 FROM igs_pe_usr_arg
3812 WHERE person_type = cp_person_type;
3813
3814 CURSOR c_cal_conf IS
3815 SELECT AUDIT_STATUS_DT_ALIAS
3816 FROM igs_en_cal_conf
3817 WHERE s_control_num = 1;
3818
3819
3820 cur_uv_audit_ind_rec cur_uv_audit_ind%ROWTYPE;
3821 l_audit_dt_alias igs_en_cal_conf. AUDIT_STATUS_DT_ALIAS %TYPE;
3822 l_dt_alias_val igs_ca_da_inst_v.alias_val%TYPE;
3823 BEGIN
3824
3825 OPEN cur_uv_audit_ind(p_uoo_id);
3826 FETCH cur_uv_audit_ind INTO cur_uv_audit_ind_rec;
3827 CLOSE cur_uv_audit_ind;
3828
3829 IF cur_uv_audit_ind_rec.auditable_ind ='Y' THEN
3830
3831 OPEN c_usr_conf(p_person_type);
3832 FETCH c_usr_conf INTO l_audit_dt_alias;
3833 CLOSE c_usr_conf;
3834 IF l_audit_dt_alias IS NULL THEN
3835 OPEN c_cal_conf;
3836 FETCH c_cal_conf INTO l_audit_dt_alias;
3837 CLOSE c_cal_conf;
3838 END IF;
3839
3840 IF l_audit_dt_alias IS NULL THEN
3841 RETURN 'Y';
3842 END IF;
3843
3844 l_dt_alias_val := igs_ss_enr_details.get_alias_val(cur_uv_audit_ind_rec.cal_type,
3845 cur_uv_audit_ind_rec.ci_sequence_number, l_audit_dt_alias);
3846
3847 -- Check whether the audit update date alias value is greater than or equal to sysdate
3848 -- smaddali modified to remove NVL handling in the if clause for bug4666977
3849
3850 IF TRUNC (l_dt_alias_val) <= TRUNC (SYSDATE) THEN
3851 RETURN 'N';
3852 ELSE
3853 RETURN 'Y';
3854 END IF;
3855
3856
3857 END IF;
3858
3859 RETURN 'N';
3860
3861 END is_audit_allowed;
3862
3863
3864 FUNCTION is_placement_allowed (p_unit_cd IN VARCHAR2, p_version_number IN NUMBER)
3865 RETURN VARCHAR2
3866 ------------------------------------------------------------------
3867 --Created by : rvangala
3868 --Date created: 24-May-2005
3869 --
3870 --Purpose: Function to check if placement is allowed for a unit section or not
3871 --
3872 -- Returns 'Y' if placement is allowed for unit section
3873 -- Returns 'N' if placement is not allowed for unit section
3874 --Known limitations/enhancements and/or remarks:
3875 --
3876 --Change History:
3877 --Who When What
3878 -------------------------------------------------------------------
3879 AS
3880 -- if the unit version has been marked as a practical unit then placement details will be available else no
3881 Cursor cur_practical_ind(cp_unit_cd VARCHAR2, cp_version_number NUMBER) IS
3882 Select practical_ind
3883 From igs_ps_unit_ver
3884 where unit_cd=cp_unit_cd
3885 and version_number= cp_version_number;
3886
3887 l_result igs_ps_unit_ver.practical_ind%TYPE;
3888
3889 BEGIN
3890
3891 OPEN cur_practical_ind(p_unit_cd,p_version_number);
3892 FETCH cur_practical_ind INTO l_result;
3893 CLOSE cur_practical_ind;
3894
3895 RETURN l_result;
3896
3897 END is_placement_allowed;
3898
3899 FUNCTION get_enrollment_capacity(p_uoo_id NUMBER) RETURN VARCHAR2
3900 ------------------------------------------------------------------
3901 --Created by : rvangala
3902 --Date created: 24-May-2005
3903 --
3904 --Purpose: Function returns the enrollment maximum and actual enrollment for
3905 -- a unit section in the format actual enrollment/maximum enrollment
3906 --
3907 --Known limitations/enhancements and/or remarks:
3908 --
3909 --Change History:
3910 --Who When What
3911 -- ckasu 01-Aug-2005 Modified as a part of EN317 SS UI Build
3912 -- bug# #4377985
3913 -------------------------------------------------------------------
3914 IS
3915
3916 -- Cursor to get the enrollment maximum in cross listed group
3917 CURSOR c_cross_listed (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3918 SELECT grp.max_enr_group, grpmem.usec_x_listed_group_id
3919 FROM igs_ps_usec_x_grpmem grpmem,
3920 igs_ps_usec_x_grp grp
3921 WHERE grp.usec_x_listed_group_id = grpmem.usec_x_listed_group_id
3922 AND grpmem.uoo_id = l_uoo_id;
3923
3924
3925 -- Cursor to get the enrollment maximum in Meet with class group
3926 CURSOR c_meet_with_cls (l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3927 SELECT grp.max_enr_group, ucm.class_meet_group_id
3928 FROM igs_ps_uso_clas_meet ucm,
3929 igs_ps_uso_cm_grp grp
3930 WHERE grp.class_meet_group_id = ucm.class_meet_group_id
3931 AND ucm.uoo_id = l_uoo_id;
3932
3933 -- Cursor to get the actual enrollment of all the unit sections that belong
3934 -- to this class listed group.
3935 CURSOR c_actual_enr_crs_lst(l_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
3936 SELECT SUM(enrollment_actual)
3937 FROM igs_ps_unit_ofr_opt uoo,
3938 igs_ps_usec_x_grpmem ugrp
3939 WHERE uoo.uoo_id = ugrp.uoo_id
3940 AND ugrp.usec_x_listed_group_id = l_usec_x_listed_group_id;
3941
3942
3943 -- Cursor to get the actual enrollment of all the unit sections that belong
3944 -- to this meet with class group.
3945 CURSOR c_actual_enr_meet_cls(l_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE) IS
3946 SELECT SUM(enrollment_actual)
3947 FROM igs_ps_unit_ofr_opt uoo,
3948 igs_ps_uso_clas_meet ucls
3949 WHERE uoo.uoo_id = ucls.uoo_id
3950 AND ucls.class_meet_group_id = l_class_meet_group_id;
3951
3952
3953 -- Cursor to fetch the enrollment Maximum value defined at Unit Section level
3954 CURSOR cur_usec_enr_max( p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3955 SELECT enrollment_maximum
3956 FROM igs_ps_usec_lim_wlst
3957 WHERE uoo_id = p_uoo_id;
3958
3959 -- cursor to fetch the enrollment maximum value defined at unit level
3960 CURSOR cur_unit_enr_max( p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3961 SELECT enrollment_maximum
3962 FROM igs_ps_unit_ver
3963 WHERE (unit_cd , version_number ) IN (SELECT unit_cd , version_number
3964 FROM igs_ps_unit_ofr_opt
3965 WHERE uoo_id = p_uoo_id);
3966
3967 --
3968 -- Cursor to find the Actual Enrollment of the Unit section
3969 --
3970 CURSOR c_enroll_actual (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
3971 SELECT enrollment_actual
3972 FROM igs_ps_unit_ofr_opt
3973 WHERE uoo_id = cp_uoo_id;
3974
3975 l_max igs_ps_usec_lim_wlst.enrollment_maximum%TYPE;
3976 l_act igs_ps_unit_ofr_opt.enrollment_actual%TYPE;
3977 l_setup_found NUMBER;
3978 l_cross_listed_row c_cross_listed%ROWTYPE;
3979 l_meet_with_cls_row c_meet_with_cls%ROWTYPE;
3980 l_usec_partof_group BOOLEAN;
3981 l_cat_enr_act varchar2(100);
3982 l_temp VARCHAR2(10);
3983
3984 BEGIN
3985
3986 -- Check whether the unit section belongs to any cross listed group. If so then get the
3987 -- maximim enrollment limit in the group level. If it is not null then get the actual enrollment
3988 -- of all the unit sections which belong to that group and return the concatenated string of
3989 -- Maximim enrollment and actual enrollment.
3990 -- Incase if the maximum enrollment limit is not set in the group level the get it from
3991 -- Unit Section level or in the unit level.
3992
3993 l_usec_partof_group := FALSE;
3994
3995 OPEN c_cross_listed(p_uoo_id);
3996 FETCH c_cross_listed INTO l_cross_listed_row ;
3997
3998
3999 IF c_cross_listed%FOUND THEN
4000 CLOSE c_cross_listed;
4001 -- Get the maximum enrollment limit from the group level.
4002 IF l_cross_listed_row.max_enr_group IS NULL THEN
4003 l_usec_partof_group := FALSE;
4004
4005 ELSE
4006 l_usec_partof_group := TRUE;
4007 l_max := l_cross_listed_row.max_enr_group;
4008
4009 -- Get the actual enrollment count of all the unit sections that belongs to the cross listed group.
4010 OPEN c_actual_enr_crs_lst(l_cross_listed_row.usec_x_listed_group_id);
4011 FETCH c_actual_enr_crs_lst INTO l_act;
4012 CLOSE c_actual_enr_crs_lst;
4013
4014 -- Concatenate the meaning with the maximim enrollment limit and actual enrollment limit.
4015 -- The format should be like 'Cross Listed <BR> 10(5)'
4016 RETURN get_meaning('IGS_PS_USEC_GROUPS','CROSS_LIST') ||' '||NVL(to_char(l_act),'-') || '/' || NVL(to_char(l_max),'-');
4017
4018 END IF;
4019
4020 ELSE
4021 CLOSE c_cross_listed;
4022 OPEN c_meet_with_cls(p_uoo_id);
4023 FETCH c_meet_with_cls INTO l_meet_with_cls_row ;
4024
4025
4026 IF c_meet_with_cls%FOUND THEN
4027 CLOSE c_meet_with_cls;
4028 -- Get the maximum enrollment limit from the group level.
4029 IF l_meet_with_cls_row.max_enr_group IS NULL THEN
4030 l_usec_partof_group := FALSE;
4031
4032 ELSE
4033 l_usec_partof_group := TRUE;
4034 l_max := l_meet_with_cls_row.max_enr_group;
4035
4036 -- Get the actual enrollment count of all the unit sections that belongs to
4037 -- the meet with class group.
4038 OPEN c_actual_enr_meet_cls(l_meet_with_cls_row.class_meet_group_id);
4039 FETCH c_actual_enr_meet_cls INTO l_act;
4040 CLOSE c_actual_enr_meet_cls;
4041
4042
4043
4044 -- Concatenate the meaning with the maximim enrollment limit and actual enrollment limit.
4045 -- The format should be like 'Meet With <BR> 10(5)'
4046 RETURN get_meaning('IGS_PS_USEC_GROUPS','MEET_WITH')||' '||NVL(to_char(l_act),'-') || '/' || NVL(to_char(l_max),'-');
4047 END IF;
4048 ELSE
4049 CLOSE c_meet_with_cls;
4050 l_usec_partof_group := FALSE;
4051 END IF;
4052
4053 END IF;
4054 IF l_usec_partof_group = FALSE THEN
4055
4056 -- If the Unit Section passed doesn't belong to any of the group then
4057 -- check the maximum enrollment limit in the Unit Section level / Unit level.
4058
4059 OPEN cur_usec_enr_max(p_uoo_id);
4060 FETCH cur_usec_enr_max INTO l_max;
4061 CLOSE cur_usec_enr_max;
4062
4063
4064 IF l_max IS NULL THEN
4065 -- Get the maximum enrollment limit from Unit level.
4066 OPEN cur_unit_enr_max(p_uoo_id);
4067 FETCH cur_unit_enr_max INTO l_max;
4068 CLOSE cur_unit_enr_max;
4069 END IF;
4070
4071 -- get the actual enrollment limit.
4072 OPEN c_enroll_actual(p_uoo_id);
4073 FETCH c_enroll_actual INTO l_act;
4074 CLOSE c_enroll_actual;
4075 l_temp := l_max;
4076 l_cat_enr_act := NVL(to_char(l_act),'-') || '/' || NVL(l_temp,'-');
4077 RETURN l_cat_enr_act;
4078 END IF;
4079 RETURN l_cat_enr_act;
4080 END get_enrollment_capacity;
4081
4082
4083 FUNCTION can_drop (p_cal_type IN VARCHAR2,
4084 p_ci_sequence_number IN NUMBER,
4085 p_effective_dt IN DATE,
4086 p_uoo_id IN NUMBER,
4087 p_c_core IN VARCHAR2,
4088 p_n_person_id IN NUMBER,
4089 p_c_course_cd IN VARCHAR2
4090 ) RETURN VARCHAR2
4091 ------------------------------------------------------------------
4092 --Created by : rvangala
4093 --Date created: 24-May-2005
4094 --
4095 --Purpose: Function determines if a unit attempt can be dropped for swap
4096 --
4097 -- Returns N if unit attempt can be dropped
4098 -- Return Y if unit attempt cannot be dropped
4099 --Known limitations/enhancements and/or remarks:
4100 --
4101 --Change History:
4102 --Who When What
4103 --ckasu 05-JUL-2005 modified as a part of EN317 BUILD
4104 -------------------------------------------------------------------
4105 AS
4106
4107 CURSOR c_uoo_teach_cal (cp_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
4108 SELECT cal_type, ci_sequence_number
4109 FROM IGS_PS_UNIT_OFR_OPT
4110 WHERE uoo_id = cp_uoo_id;
4111
4112 -- cursor to check whether unit section is superior unit section.
4113 CURSOR c_sup(cp_n_uoo_id IN NUMBER) IS
4114 SELECT 1
4115 FROM igs_ps_unit_ofr_opt_all
4116 WHERE sup_uoo_id = cp_n_uoo_id
4117 AND rownum <2;
4118
4119 -- cursor to checks whether subordinate unit section exists in unit attempt status
4120 -- other than enrolled, invalid, discontinued and dropped for the given superior unit section.
4121 CURSOR c_invalid_sub(cp_c_course_cd IN VARCHAR2,
4122 cp_n_person_id IN NUMBER,
4123 cp_n_uoo_id IN NUMBER) IS
4124 SELECT 1
4125 FROM IGS_EN_SU_ATTEMPT_ALL sua,
4126 IGS_PS_UNIT_OFR_OPT_ALL uoo
4127 WHERE sua.uoo_id = uoo.uoo_id
4128 AND uoo.sup_uoo_id = cp_n_uoo_id
4129 AND sua.course_cd = cp_c_course_cd
4130 AND sua.person_id = cp_n_person_id
4131 AND sua.unit_attempt_status NOT IN ('ENROLLED','INVALID','DISCONTIN','DROPPED')
4132 AND ROWNUM <2;
4133
4134 -- cursor to get all the subordinate unit attempt for the given superior unit attempt.
4135 CURSOR c_sub_sua (cp_c_course_cd IN VARCHAR2,
4136 cp_n_person_id IN NUMBER,
4137 cp_n_uoo_id IN NUMBER) IS
4138 SELECT sua.uoo_id,
4139 sua.core_indicator_code
4140 FROM IGS_EN_SU_ATTEMPT_ALL sua,
4141 IGS_PS_UNIT_OFR_OPT_ALL uoo
4142 WHERE sua.uoo_id = uoo.uoo_id
4143 AND uoo.sup_uoo_id = cp_n_uoo_id
4144 AND sua.course_cd = cp_c_course_cd
4145 AND sua.person_id = cp_n_person_id
4146 AND sua.unit_attempt_status IN ('ENROLLED','INVALID');
4147
4148 temp Number; -- temporary variable.
4149 l_c_deny_warn VARCHAR2(10); -- deny warn flag.
4150
4151 l_teach_cal_type IGS_PS_UNIT_OFR_OPT.CAL_TYPE%TYPE;
4152 l_teach_ci_sequence_number IGS_PS_UNIT_OFR_OPT.CI_SEQUENCE_NUMBER%TYPE;
4153
4154 BEGIN
4155
4156 OPEN c_uoo_teach_cal(p_uoo_id);
4157 FETCH c_uoo_teach_cal INTO l_teach_cal_type, l_teach_ci_sequence_number;
4158 CLOSE c_uoo_teach_cal;
4159
4160 -- Check whether enrollment window is open
4161 IF IGS_EN_GEN_008.enrp_get_var_window( l_teach_cal_type, l_teach_ci_sequence_number,
4162 p_effective_dt, p_uoo_id) THEN
4163
4164 -- check whether unit attempt can be dropped or discontinued
4165 -- function Enrp_Get_Ua_Del_Alwd returns 'N' if it can be discontinued 'Y' if it can only be dropped.
4166 IF IGS_EN_GEN_008.Enrp_Get_Ua_Del_Alwd(l_teach_cal_type, l_teach_ci_sequence_number,
4167 p_effective_dt, p_uoo_id) = 'Y' THEN
4168 -- check whether the unit attempt getting dropped is superior unit section.
4169 OPEN c_sup(p_uoo_id);
4170 FETCH c_sup INTO temp;
4171 IF c_sup%FOUND THEN
4172 CLOSE c_sup;
4173 -- check whether the superior unit attempt has subordinate unit attempt
4174 -- in status other than enrolled and invalid
4175 OPEN c_invalid_sub(p_c_course_cd,p_n_person_id,p_uoo_id);
4176 FETCH c_invalid_sub INTO temp;
4177 IF c_invalid_sub%FOUND THEN
4178 close c_invalid_sub;
4179 -- return 'Y' if there are subordinate unit attempt exists and
4180 -- their unit attempt status is other than 'enrolled' and 'invalid'
4181 RETURN 'Y';
4182 ELSE
4183 close c_invalid_sub;
4184 END IF;
4185
4186 -- check whether the subordinate unit attempt can be dropped without any issues.
4187 FOR rec_sub_sua IN c_sub_sua(p_c_course_cd,p_n_person_id,p_uoo_id)
4188 LOOP
4189 IF can_drop (p_cal_type => p_cal_type,
4190 p_ci_sequence_number => p_ci_sequence_number,
4191 p_effective_dt => p_effective_dt,
4192 p_uoo_id => rec_sub_sua.uoo_id,
4193 p_c_core => rec_sub_sua.core_indicator_code,
4194 p_n_person_id => p_n_person_id,
4195 p_c_course_cd => p_c_course_cd) = 'Y' THEN
4196 RETURN 'Y';
4197 END IF;
4198 END LOOP;
4199 ELSE
4200 CLOSE c_sup;
4201 END IF;
4202 RETURN 'N'; -- Can be selected for swap
4203 END IF; -- checking for drop / discontinue
4204 END IF; -- checking for variable window open
4205 RETURN 'Y';
4206 END can_drop;
4207
4208 /**
4209 This function returns the total credits for planned unit attempts in a term
4210 **/
4211 FUNCTION get_total_plan_credits(p_personid NUMBER,
4212 p_course_cd VARCHAR2,
4213 p_term_cal_type VARCHAR2,
4214 p_term_seq_num NUMBER) RETURN NUMBER
4215 ------------------------------------------------------------------
4216 --Created by : rvangala
4217 --Date created: 24-May-2005
4218 --
4219 --Purpose: Function returns the total credit points in the planning sheet
4220 -- for a term
4221 --
4222 --Known limitations/enhancements and/or remarks:
4223 --
4224 --Change History:
4225 --Who When What
4226 -------------------------------------------------------------------
4227 AS
4228
4229 CURSOR c_total_plan_credits(cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
4230 SELECT SUM(igs_ss_enr_details.get_apor_credits ( uoo_id, override_enrolled_cp,
4231 cp_term_cal_type,cp_term_seq_num)) apor_cp
4232 FROM IGS_EN_PLAN_UNITS pls
4233 WHERE pls.cart_error_flag= 'N'
4234 AND pls.person_id = p_personid
4235 AND pls.course_cd = p_course_cd
4236 AND pls.term_cal_type = cp_term_cal_type
4237 AND pls.term_ci_sequence_number = cp_term_seq_num;
4238
4239 CURSOR c_total_unconfirm_credits (cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
4240 SELECT SUM(igs_ss_enr_details.get_apor_credits ( uoo_id, override_enrolled_cp,
4241 cp_term_cal_type,cp_term_seq_num)) apor_cp
4242 FROM IGS_EN_SU_ATTEMPT sua
4243 WHERE sua.unit_attempt_status = 'UNCONFIRM'
4244 AND sua.person_id = p_personid
4245 AND sua.course_cd = p_course_cd
4246 AND (sua.cal_type , sua.ci_sequence_number) in (select teach_cal_type, teach_ci_sequence_number from igs_ca_teach_to_load_v where
4247 load_cal_type = cp_term_cal_type and load_ci_sequence_number = cp_term_seq_num);
4248
4249 l_total_plan_credits NUMBER;
4250 l_total_unconfirm_credits NUMBER;
4251
4252 BEGIN
4253
4254
4255 -- fetch the total credits points from planning sheet
4256 OPEN c_total_plan_credits(p_term_cal_type,p_term_seq_num);
4257 FETCH c_total_plan_credits INTO l_total_plan_credits;
4258 CLOSE c_total_plan_credits;
4259
4260 --fetch the total credit points of unconfirmed unit attempts
4261 OPEN c_total_unconfirm_credits(p_term_cal_type,p_term_seq_num);
4262 FETCH c_total_unconfirm_credits INTO l_total_unconfirm_credits;
4263 CLOSE c_total_unconfirm_credits;
4264
4265
4266 --return the sum of planned and unconfirmed unit attempts
4267 RETURN NVL(l_total_plan_credits,0) + NVL(l_total_unconfirm_credits,0);
4268
4269
4270 END;
4271
4272
4273 /**
4274 This function checks if enrollment is open for a given term
4275 **/
4276 FUNCTION get_enr_period_open_status( p_person_id IN NUMBER,
4277 p_course_cd IN VARCHAR2,
4278 p_load_calendar_type IN VARCHAR2,
4279 p_load_cal_sequence_number IN NUMBER,
4280 p_person_type IN VARCHAR2,
4281 p_message OUT NOCOPY VARCHAR2)
4282 RETURN BOOLEAN
4283 ------------------------------------------------------------------
4284 --Created by : rvangala
4285 --Date created: 24-May-2005
4286 --
4287 --Purpose: Function checks if enrollment is open for a term
4288 --
4289 -- Returns True if enrollment is open
4290 -- Returns False if enrollment is not open
4291 --Known limitations/enhancements and/or remarks:
4292 --
4293 --Change History:
4294 --Who When What
4295 -------------------------------------------------------------------
4296 AS
4297 --cursor to check if schedule is open for a given term
4298 CURSOR c_get_schedule_flag IS
4299 SELECT ci.schedule_flag schedule_flag
4300 FROM igs_ca_inst_all ci
4301 WHERE CAL_TYPE = p_load_calendar_type
4302 AND SEQUENCE_NUMBER = p_load_cal_sequence_number;
4303
4304 --cursor to fetch the schedule start date alias
4305 CURSOR c_cal_conf IS
4306 SELECT schedule_open_dt_alias
4307 FROM igs_en_cal_conf
4308 WHERE s_control_num = 1;
4309
4310 l_sch_dt_alias igs_en_cal_conf.SCHEDULE_OPEN_DT_ALIAS%TYPE;
4311 l_schedule_flag igs_ca_inst_all.schedule_flag%TYPE;
4312 l_dt_alias_val DATE;
4313
4314
4315 BEGIN
4316
4317
4318
4319 OPEN c_get_schedule_flag;
4320 FETCH c_get_schedule_flag INTO l_schedule_flag;
4321 CLOSE c_get_schedule_flag;
4322
4323 OPEN c_cal_conf;
4324 FETCH c_cal_conf INTO l_sch_dt_alias;
4325 CLOSE c_cal_conf;
4326
4327 --if schedule is open for the term
4328 IF l_schedule_flag = 'Y' THEN
4329 -- get the schedule alias value for the current term.
4330 l_dt_alias_val := igs_ss_enr_details.get_alias_val(p_load_calendar_type, p_load_cal_sequence_number, l_sch_dt_alias);
4331 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
4332 IF l_dt_alias_val IS NOT NULL AND TRUNC (l_dt_alias_val) <= TRUNC (SYSDATE) THEN
4333 -- Check whether student has timeslot
4334 IF igs_ss_enr_details.stu_timeslot_open (p_person_id,
4335 p_person_type,
4336 p_course_cd,
4337 p_load_calendar_type,
4338 p_load_cal_sequence_number) THEN
4339
4340 RETURN TRUE;
4341
4342 END IF; -- end of IF igs_ss_enr_details.stu_timeslot_open check
4343
4344 END IF; -- end of IF l_dt_alias_val IS NOT NULL AND TRUNC (l_dt_alias_val) <= TRUNC (SYSDATE)
4345
4346 END IF; -- end of IF l_schedule_flag = 'Y
4347
4348 RETURN FALSE;
4349
4350 END get_enr_period_open_status;
4351
4352
4353 FUNCTION is_selection_enabled (p_person_id IN NUMBER,
4354 p_load_cal_type IN VARCHAR2,
4355 p_load_seq_num IN NUMBER,
4356 p_person_type IN VARCHAR2,
4357 p_message OUT NOCOPY VARCHAR2
4358 ) RETURN VARCHAR2
4359 ------------------------------------------------------------------
4360 --Created by : rvangala
4361 --Date created: 24-May-2005
4362 --
4363 --Purpose: Function checks if unit sections can be selected to be enrolled
4364 -- for a term
4365 --
4366 --Known limitations/enhancements and/or remarks:
4367 --
4368 --Change History:
4369 --Who When What
4370 --ckasu 29-Jul-2005 modified as a part of EN 317 Build
4371 --svanukur 30-sep-2005 modified as a part of EN 317 Build
4372
4373 -------------------------------------------------------------------
4374 IS
4375 --cursor to check if the selected load calendar is open only for searching
4376 --but not for planning or for the schedule.
4377 CURSOR c_get_load_cal_open_status(cp_load_cal_type VARCHAR2, cp_load_seq_num NUMBER) IS
4378 SELECT 'x'
4379 FROM IGS_CA_INST
4380 WHERE CAL_TYPE = cp_load_cal_type
4381 AND SEQUENCE_NUMBER = cp_load_seq_num
4382 AND SS_DISPLAYED = 'Y'
4383 AND PLANNING_FLAG = 'N'
4384 AND SCHEDULE_FLAG = 'N';
4385
4386 --cursor to check if the passed in load calendar is associated with the academic calendar
4387 -- of any of the student's program attempts
4388 CURSOR c_check_is_load_assoc_to_acad (cp_person_id NUMBER, cp_load_cal_type VARCHAR2, cp_load_seq_num NUMBER) IS
4389 SELECT distinct cir.sub_cal_type ,cir.sub_ci_sequence_number
4390 FROM igs_en_stdnt_ps_att sca,
4391 igs_ca_inst_rel cir,
4392 igs_ca_type ct
4393 WHERE cir.sup_cal_type = sca.cal_type and
4394 ct.cal_type = cir.sub_cal_type and
4395 ct.s_cal_cat = 'LOAD' and
4396 sca.person_id = cp_person_id and
4397 cir.sub_cal_type = cp_load_cal_type and
4398 cir.sub_ci_sequence_number = cp_load_seq_num;
4399
4400 CURSOR cur_sys_pers_type(p_person_type_code VARCHAR2) IS
4401 SELECT system_type
4402 FROM igs_pe_person_types
4403 WHERE person_type_code = p_person_type_code;
4404
4405
4406 l_rec c_get_load_cal_open_status%ROWTYPE;
4407 l_load_acad_rec c_check_is_load_assoc_to_acad%ROWTYPE;
4408 l_system_type igs_pe_person_types.system_type%TYPE;
4409
4410 BEGIN
4411
4412 OPEN cur_sys_pers_type(p_person_type);
4413 FETCH cur_sys_pers_type INTO l_system_type;
4414 CLOSE cur_sys_pers_type;
4415
4416 IF l_system_type = 'STUDENT' THEN
4417 OPEN c_get_load_cal_open_status(p_load_cal_type,p_load_seq_num);
4418 FETCH c_get_load_cal_open_status INTO l_rec;
4419 --if load calendar is open only for searching
4420 IF c_get_load_cal_open_status%FOUND THEN
4421 p_message := 'IGS_EN_SEARCH_ONLY';
4422 CLOSE c_get_load_cal_open_status;
4423 RETURN 'FALSE';
4424 END IF;
4425 CLOSE c_get_load_cal_open_status;
4426
4427 END IF; -- end of IF l_system_type = 'STUDENT' THEN
4428
4429 OPEN c_check_is_load_assoc_to_acad(p_person_id,p_load_cal_type,p_load_seq_num);
4430 FETCH c_check_is_load_assoc_to_acad INTO l_load_acad_rec;
4431 --if load calendar is not associated with academic calendar of
4432 --any of the student's program attempts
4433 IF c_check_is_load_assoc_to_acad%NOTFOUND THEN
4434 p_message := 'IGS_EN_SEARCH_ONLY';
4435 CLOSE c_check_is_load_assoc_to_acad;
4436 RETURN 'FALSE';
4437 END IF;
4438 CLOSE c_check_is_load_assoc_to_acad;
4439
4440 RETURN 'TRUE';
4441
4442 END is_selection_enabled;
4443
4444 FUNCTION get_us_subtitle (p_n_uoo_id IN NUMBER) RETURN VARCHAR2
4445 ------------------------------------------------------------------
4446 --Created by : rvangala
4447 --Date created: 24-May-2005
4448 --
4449 --Purpose: Function returns the subtitle for a unit section
4450 --
4451 --Known limitations/enhancements and/or remarks:
4452 --
4453 --Change History:
4454 --Who When What
4455 -------------------------------------------------------------------
4456 IS
4457 CURSOR c_us_title(cp_n_uoo_id IN NUMBER) IS
4458 SELECT t.subtitle
4459 FROM igs_ps_unit_subtitle t,
4460 igs_ps_usec_ref us
4461 WHERE us.uoo_id = cp_n_uoo_id
4462 AND us.subtitle_id = t.subtitle_id;
4463
4464 CURSOR c_uv_title(cp_n_uoo_id IN NUMBER) IS
4465 SELECT t.subtitle
4466 FROM igs_ps_unit_subtitle t,
4467 igs_ps_unit_ofr_opt_all us,
4468 igs_ps_unit_ver_all uv
4469 WHERE us.uoo_id = cp_n_uoo_id
4470 AND us.unit_cd = uv.unit_cd
4471 AND us.version_number = uv.version_number
4472 AND uv.subtitle_id = t.subtitle_id;
4473 l_c_subtitle igs_ps_unit_subtitle.subtitle%TYPE;
4474 BEGIN
4475 OPEN c_us_title(p_n_uoo_id);
4476 FETCH c_us_title INTO l_c_subtitle;
4477 CLOSE c_us_title;
4478 IF l_c_subtitle IS NULL THEN
4479 OPEN c_uv_title(p_n_uoo_id);
4480 FETCH c_uv_title INTO l_c_subtitle;
4481 CLOSE c_uv_title;
4482 END IF;
4483 RETURN l_c_subtitle;
4484 END get_us_subtitle;
4485
4486
4487 FUNCTION get_waitlist_capacity(p_uoo_id NUMBER, p_unit_cd IN VARCHAR2,
4488 p_version IN NUMBER,
4489 p_cal_type IN VARCHAR2, p_sequence_number IN NUMBER,
4490 p_owner_org_unit_cd IN VARCHAR2) RETURN VARCHAR2
4491 ------------------------------------------------------------------
4492 --Created by : rvangala
4493 --Date created: 24-May-2005
4494 --
4495 --Purpose: Function returns the actual and maximum waitlist capacity for
4496 -- a unit section in the format actual waitlist/maximum waitlist
4497 --
4498 --Known limitations/enhancements and/or remarks:
4499 --
4500 --Change History:
4501 --Who When What
4502 -------------------------------------------------------------------
4503 IS
4504 Cursor cur_wait_actual(cp_uoo_id NUMBER) IS
4505 Select NVL(waitlist_actual,0)
4506 from igs_ps_unit_ofr_opt
4507 where uoo_id=cp_uoo_id;
4508
4509 l_result VARCHAR2(100);
4510
4511 BEGIN
4512 OPEN cur_wait_actual(p_uoo_id);
4513 FETCH cur_wait_actual INTO l_result;
4514 CLOSE cur_wait_actual;
4515
4516 l_result := l_result || '/' || get_max_waitlist_for_unit(p_uoo_id ,
4517 p_unit_cd ,
4518 p_version ,
4519 p_cal_type , p_sequence_number ,
4520 p_owner_org_unit_cd) ;
4521
4522 return l_result;
4523
4524 END get_waitlist_capacity;
4525
4526 FUNCTION get_class_day (p_n_uso_id IN NUMBER) RETURN VARCHAR2
4527 ------------------------------------------------------------------
4528 --Created by : rvangala
4529 --Date created: 24-May-2005
4530 --
4531 --Purpose: Function to get the meeting days for a unit section occurrence
4532 --
4533 --Known limitations/enhancements and/or remarks:
4534 --
4535 --Change History:
4536 --Who When What
4537 -------------------------------------------------------------------
4538 IS
4539 CURSOR c_uso(cp_n_uso_id IN NUMBER) IS
4540 SELECT uso.to_be_announced,
4541 uso.no_set_day_ind,
4542 DECODE(uso.monday, 'Y', 'M', NULL) ||
4543 DECODE(uso.tuesday, 'Y', 'Tu', NULL) ||
4544 DECODE(uso.wednesday, 'Y', 'W', NULL) ||
4545 DECODE(uso.thursday, 'Y', 'Th', NULL) ||
4546 DECODE(uso.friday, 'Y', 'F', NULL) ||
4547 DECODE(uso.saturday, 'Y', 'Sa', NULL) ||
4548 DECODE(uso.sunday, 'Y', 'Su', NULL) meetings
4549 FROM igs_ps_usec_occurs_all uso
4550 WHERE uso.unit_section_occurrence_id = cp_n_uso_id;
4551 rec_uso c_uso%ROWTYPE;
4552 l_c_ret_data varchar2(80);
4553 BEGIN
4554 OPEN c_uso(p_n_uso_id);
4555 FETCH c_uso INTO rec_uso;
4556 IF c_uso%FOUND THEN
4557 IF rec_uso.to_be_announced = 'Y' THEN
4558 l_c_ret_data := get_tba_desc;
4559 ELSIF rec_uso.no_set_day_ind ='Y' THEN
4560 l_c_ret_data := get_nsd_desc;
4561 ELSE
4562 l_c_ret_data := rec_uso.meetings;
4563 END IF;
4564 END IF;
4565 CLOSE c_uso;
4566 RETURN l_c_ret_data;
4567 END get_class_day;
4568
4569 FUNCTION get_class_time (p_n_uso_id IN NUMBER) RETURN VARCHAR2
4570 ------------------------------------------------------------------
4571 --Created by : rvangala
4572 --Date created: 24-May-2005
4573 --
4574 --Purpose: Function to get the meeting times for a unit section occurrence
4575 --
4576 --Known limitations/enhancements and/or remarks:
4577 --
4578 --Change History:
4579 --Who When What
4580 -------------------------------------------------------------------
4581 IS
4582 CURSOR c_uso(cp_n_uso_id IN NUMBER) IS
4583 SELECT uso.no_set_day_ind,
4584 to_char(uso.start_time,'hh:miam') start_time,
4585 to_char(uso.end_time,'hh:miam') end_time
4586 FROM igs_ps_usec_occurs_all uso
4587 WHERE uso.unit_section_occurrence_id = cp_n_uso_id;
4588 rec_uso c_uso%ROWTYPE;
4589
4590 l_c_ret_data varchar2(80);
4591 BEGIN
4592 OPEN c_uso (p_n_uso_id);
4593 FETCH c_uso INTO rec_uso;
4594 IF c_uso%FOUND THEN
4595 IF rec_uso.no_set_day_ind = 'Y' THEN
4596 l_c_ret_data := NULL;
4597 ELSIF rec_uso.start_time IS NULL THEN
4598 l_c_ret_data := get_tba_desc;
4599 ELSE
4600 l_c_ret_data := rec_uso.start_time || ' - ' || rec_uso.end_time;
4601 END IF;
4602 END IF;
4603 CLOSE c_uso;
4604 RETURN l_c_ret_data;
4605 END get_class_time;
4606
4607
4608 FUNCTION get_occur_dates (p_n_uso_id IN NUMBER) RETURN VARCHAR2
4609 ------------------------------------------------------------------
4610 --Created by : rvangala
4611 --Date created: 24-May-2005
4612 --
4613 --Purpose: Function to get the meeting dates for a unit section occurrence
4614 --
4615 --Known limitations/enhancements and/or remarks:
4616 --
4617 --Change History:
4618 --Who When What
4619 -------------------------------------------------------------------
4620 AS
4621 CURSOR c_uso_dtls (cp_n_uso_id IN NUMBER) IS
4622 SELECT TO_CHAR( NVL( NVL( USO.START_DATE, US.UNIT_SECTION_START_DATE),
4623 CA.START_DT), 'DD MON YYYY') || ' - ' ||
4624 TO_CHAR( NVL( NVL( USO.END_DATE, US.UNIT_SECTION_END_DATE),
4625 CA.END_DT), 'DD MON YYYY') effective_date
4626 FROM igs_ps_usec_occurs_all USO,
4627 igs_ps_unit_ofr_opt_all US,
4628 igs_ca_inst_all CA
4629 WHERE uso.unit_section_occurrence_id = cp_n_uso_id
4630 AND uso.uoo_id = us.uoo_id
4631 AND us.cal_type = ca.cal_type
4632 AND us.ci_sequence_number = ca.sequence_number;
4633
4634 l_uso_dates varchar2(50);
4635 BEGIN
4636 OPEN c_uso_dtls(p_n_uso_id);
4637 FETCH c_uso_dtls INTO l_uso_dates;
4638 CLOSE c_uso_dtls;
4639 return l_uso_dates;
4640
4641 END get_occur_dates;
4642
4643 FUNCTION get_calling_object (p_person_id IN NUMBER,
4644 p_course_cd IN VARCHAR2,
4645 p_load_cal_type IN VARCHAR2,
4646 p_load_seq_num IN NUMBER,
4647 p_person_type IN VARCHAR2,
4648 p_message OUT NOCOPY VARCHAR2
4649 ) RETURN VARCHAR2
4650 IS
4651 ------------------------------------------------------------------
4652 --Created by : rvangala
4653 --Date created: 24-May-2005
4654 --
4655 --Purpose: Function checks if unit sections can be selected to be enrolled
4656 -- for a term
4657 --
4658 --Known limitations/enhancements and/or remarks:
4659 --
4660 --Change History:
4661 --Who When What
4662 --ckasu 29-Jul-2005 modified as a part of EN 317 Build
4663 --stutta 28-Oct-2005 removed check for student as this method
4664 -- is called only from student page
4665 -------------------------------------------------------------------
4666
4667 CURSOR c_get_plan_schedule_flag(cp_load_cal_type VARCHAR2, cp_load_seq_num NUMBER) IS
4668 SELECT planning_flag,
4669 schedule_flag
4670 FROM igs_ca_inst_all ci
4671 WHERE CAL_TYPE = cp_load_cal_type
4672 AND SEQUENCE_NUMBER = cp_load_seq_num;
4673
4674 CURSOR c_cal_conf IS
4675 SELECT planning_open_dt_alias, schedule_open_dt_alias
4676 FROM igs_en_cal_conf
4677 WHERE s_control_num = 1;
4678
4679 CURSOR c_spat_check (cp_person_id IN NUMBER,
4680 cp_course_cd IN VARCHAR2,
4681 cp_load_cal_type IN VARCHAR2,
4682 cp_load_seq_num IN NUMBER) IS
4683 SELECT plan_sht_status
4684 FROM igs_en_spa_terms
4685 WHERE person_id= cp_person_id
4686 AND program_cd=cp_course_cd
4687 AND term_cal_type=cp_load_cal_type
4688 AND term_sequence_number=cp_load_seq_num;
4689
4690 CURSOR cur_sys_pers_type(p_person_type_code VARCHAR2) IS
4691 SELECT system_type
4692 FROM igs_pe_person_types
4693 WHERE person_type_code = p_person_type_code;
4694
4695
4696 l_plan_dt_alias igs_en_cal_conf.planning_open_dt_alias %TYPE;
4697 l_sch_dt_alias igs_en_cal_conf.SCHEDULE_open_DT_ALIAS%TYPE;
4698
4699 l_plan_flag igs_ca_inst_all.planning_flag%TYPE;
4700 l_schedule_flag igs_ca_inst_all.schedule_flag%TYPE;
4701
4702 l_dt_alias_val DATE;
4703
4704 l_planning_open BOOLEAN;
4705 l_schedule_open BOOLEAN;
4706 l_calling_object VARCHAR2(100);
4707
4708 l_spat_plan_flag igs_en_spa_terms.plan_sht_status%TYPE;
4709 l_system_type igs_pe_person_types.system_type%TYPE;
4710
4711
4712 BEGIN
4713 l_planning_open := FALSE;
4714 l_schedule_open := FALSE;
4715
4716 OPEN cur_sys_pers_type(p_person_type);
4717 FETCH cur_sys_pers_type INTO l_system_type;
4718 CLOSE cur_sys_pers_type;
4719
4720
4721 OPEN c_get_plan_schedule_flag(p_load_cal_type,p_load_seq_num);
4722 FETCH c_get_plan_schedule_flag INTO l_plan_flag,l_schedule_flag;
4723 CLOSE c_get_plan_schedule_flag;
4724
4725 OPEN c_cal_conf;
4726 FETCH c_cal_conf INTO l_plan_dt_alias,l_sch_dt_alias;
4727 CLOSE c_cal_conf;
4728
4729 -- Check whether the schedule is open for the given term
4730
4731 IF l_schedule_flag = 'Y' THEN
4732 -- get the schedule alias value for the current term.
4733 l_dt_alias_val := igs_ss_enr_details.get_alias_val(p_load_cal_type, p_load_seq_num, l_sch_dt_alias);
4734 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
4735 IF l_dt_alias_val IS NOT NULL AND TRUNC (l_dt_alias_val) <= TRUNC (SYSDATE) THEN
4736 -- Check whether student has timeslot and
4737 IF igs_ss_enr_details.stu_timeslot_open (p_person_id,
4738 p_person_type,
4739 p_course_cd,
4740 p_load_cal_type,
4741 p_load_seq_num) THEN
4742
4743 l_schedule_open := TRUE;
4744
4745 END IF; -- end of IF igs_ss_enr_details.stu_timeslot_open check
4746
4747 END IF; -- end of IF l_dt_alias_val IS NOT NULL AND TRUNC (l_dt_alias_val) >= TRUNC (SYSDATE)
4748
4749 END IF; -- end of IF l_schedule_flag = 'Y
4750
4751 -- Check whether the planning is open for the given term
4752 -- if the plannings is allowed for the plannig sheet and IGS: Use Planning Sheet Profile is ON
4753 IF l_plan_flag = 'Y' AND NVL(fnd_profile.value('IGS_EN_USE_PLAN'),'OFF') = 'ON' THEN
4754
4755 -- Get the planning sheet date alias value for the given load calendar.
4756 l_dt_alias_val := igs_ss_enr_details.get_alias_val( p_load_cal_type, p_load_seq_num,l_plan_dt_alias);
4757
4758 -- Check whether the planning sheet date alias value is greater than or equal to sysdate
4759 IF l_dt_alias_val IS NOT NULL AND TRUNC (l_dt_alias_val) <= TRUNC (SYSDATE) THEN
4760
4761 -- Get the planning sheet status at SPA TERMS records
4762 OPEN c_spat_check(p_person_id,p_course_cd,p_load_cal_type,p_load_seq_num);
4763 FETCH c_spat_check INTO l_spat_plan_flag;
4764 CLOSE c_spat_check;
4765
4766 -- Planning sheet is allowed only when the SPA Terms record does not exists or
4767 -- planning sheet status flag is PLAN or NONE. Otherwise plannnig sheet is not
4768 -- active and user work with the planning sheet.
4769 IF l_spat_plan_flag IS NULL OR l_spat_plan_flag in ('PLAN','NONE') THEN
4770 l_spat_plan_flag := NULL; -- nullifying it as it is getting re used down.
4771 l_planning_open := TRUE;
4772 END IF;
4773
4774 END IF;
4775
4776 END IF;
4777
4778 --if only planning is open
4779 IF l_planning_open AND NOT l_schedule_open THEN
4780 l_calling_object := 'PLAN';
4781
4782 --if only schedule is open
4783 ElSIF NOT l_planning_open AND l_schedule_open THEN
4784 l_calling_object := 'SCHEDULE';
4785
4786 --if both planning and schedule are not open
4787 ElSIF NOT l_planning_open AND NOT l_schedule_open THEN
4788 IF l_system_type = 'STUDENT' THEN
4789 p_message :='IGS_EN_PLAN_SCH_NOT_OPEN';
4790 ELSE
4791 p_message :='IGS_EN_SCH_NOT_OPEN';
4792 END IF;
4793 RETURN null;
4794
4795 --if both planning and schedule are open
4796 ELSIF l_planning_open AND l_schedule_open THEN
4797
4798 --check if term record exists
4799 OPEN c_spat_check(p_person_id,p_course_cd,p_load_cal_type,p_load_seq_num);
4800 FETCH c_spat_check INTO l_spat_plan_flag;
4801
4802 --if term record exists
4803 IF (c_spat_check%FOUND)THEN
4804 IF(l_spat_plan_flag='PLAN') THEN
4805 l_calling_object := 'DECISION';
4806 ELSIF(l_spat_plan_flag='SUB_PLAN') THEN
4807 l_calling_object := 'CART';
4808 --no term record was found
4809 ELSIF (l_spat_plan_flag='NONE' OR
4810 l_spat_plan_flag='SKIP' OR
4811 l_spat_plan_flag='SUB_CART') THEN
4812 l_calling_object := 'SCHEDULE';
4813 END IF;
4814 ELSE
4815 l_calling_object := 'SCHEDULE';
4816 END IF; -- end of if (c_spat_check%FOUND)
4817
4818 CLOSE c_spat_check;
4819 END IF;
4820 RETURN l_calling_object;
4821
4822 END get_calling_object;
4823
4824 FUNCTION parse_coreq1(p_coreq_string VARCHAR)
4825 RETURN VARCHAR2
4826 ------------------------------------------------------------------
4827 --Created by : rvangala
4828 --Date created: 24-May-2005
4829 --
4830 --Purpose: Function to parse unit codes from string containing both unit
4831 -- codes and version numbers
4832 -- if passed in string contains ENGL100.1,MATH100.1,
4833 -- this functions parses and returns ENGL100 and MATH100
4834 --
4835 --Known limitations/enhancements and/or remarks:
4836 --
4837 --Change History:
4838 --Who When What
4839 -------------------------------------------------------------------
4840 As
4841
4842 l_dot_index NUMBER;
4843 l_comma_index NUMBER;
4844 l_coreq_string VARCHAR2(2000);
4845
4846 BEGIN
4847 l_coreq_string := p_coreq_string;
4848 LOOP
4849 L_dot_index := 0;
4850 L_comma_index := 0;
4851
4852 L_dot_index := INSTR(l_coreq_string,'.',1 );
4853 EXIT when L_dot_index =0;
4854 L_comma_index := INSTR( l_coreq_string , ',',L_dot_index );
4855
4856 l_coreq_string := SUBSTR( l_coreq_string,1, L_dot_index-1) || SUBSTR( l_coreq_string, L_comma_index);
4857
4858 END LOOP;
4859
4860 return l_coreq_string;
4861
4862 end ;
4863
4864 FUNCTION parse_coreq2(p_coreq_string VARCHAR2)
4865 RETURN VARCHAR2
4866 ------------------------------------------------------------------
4867 --Created by : rvangala
4868 --Date created: 24-May-2005
4869 --
4870 --Purpose: Function to get all the unit codes for any unit codes containing
4871 -- wildcard characters as the unit code rule may contain %
4872 --
4873 --Known limitations/enhancements and/or remarks:
4874 --
4875 --Change History:
4876 --Who When What
4877 -------------------------------------------------------------------
4878 As
4879
4880 l_comma_index NUMBER(38);
4881 l_coreq_string VARCHAR2(2000);
4882 l_final_coreq_string VARCHAR2(2000);
4883 l_unit_cd VARCHAR2(2000);
4884
4885 CURSOR c_unit_cd(cp_unit_cd VARCHAR2) IS
4886 SELECT DISTINCT unit_cd
4887 FROM igs_ps_unit
4888 WHERE unit_cd like cp_unit_cd;
4889
4890 BEGIN
4891 l_coreq_string := p_coreq_string;
4892 L_comma_index := 0;
4893 LOOP
4894 l_coreq_string := LTRIM(l_coreq_string);
4895 L_comma_index:= INSTR(l_coreq_string,',',1 );
4896
4897 EXIT when L_comma_index =0;
4898
4899 l_unit_cd := SUBSTR(l_coreq_string,1, L_comma_index - 1) ;
4900
4901 FOR c_unit_cd_rec in c_unit_cd(l_unit_cd) LOOP
4902 IF l_final_coreq_string IS NOT NULL THEN
4903 l_final_coreq_string := l_final_coreq_string || ','|| c_unit_cd_rec.unit_cd ;
4904 ELSE
4905 l_final_coreq_string := c_unit_cd_rec.unit_cd ;
4906 END IF;
4907 END LOOP;
4908 l_coreq_string := SUBSTR( l_coreq_string, L_comma_index+1);
4909 EXIT when l_coreq_string IS NULL;
4910 END LOOP;
4911
4912 IF l_final_coreq_string IS NULL THEN
4913 l_final_coreq_string:= l_coreq_string;
4914 END IF;
4915
4916 return l_final_coreq_string;
4917
4918 end ;
4919
4920
4921 FUNCTION get_coreq_units(p_uoo_id IN NUMBER)
4922 RETURN VARCHAR2
4923 ------------------------------------------------------------------
4924 --Created by : rvangala
4925 --Date created: 24-May-2005
4926 --
4927 --Purpose: This function returns the string of unit codes, which have been defined
4928 -- as corequisite to the passed unit section. Only corequisite rule
4929 -- "Any co-req unit in {set of units}" is considered for this string.
4930 -- If the coreq rule contains any components other than this, this function will return null.
4931 -- The same pattern can exist more than once in the rule text; in this case we
4932 -- should concatenate all the unit code sets from all the occurances of this rule component
4933 --
4934 --Known limitations/enhancements and/or remarks:
4935 --
4936 --Change History:
4937 --Who When What
4938 -------------------------------------------------------------------
4939 AS
4940 /*** get the coreq rule defined at the unit section rules ***/
4941 Cursor c_coreq_usec(cp_uoo_id NUMBER) IS
4942 SELECT rul_sequence_number
4943 FROM igs_ps_usec_ru
4944 WHERE uoo_id = cp_uoo_id
4945 AND s_rule_call_cd = 'USECCOREQ';
4946
4947 /*** get the coreq rule defined at the unit version rules ***/
4948 Cursor c_coreq_unit(cp_uoo_id NUMBER) IS
4949 SELECT rul_sequence_number
4950 FROM igs_ps_unit_ver_ru uvr, igs_ps_unit_ofr_opt uoo
4951 WHERE uvr.unit_cd = uoo.unit_cd
4952 AND uvr.version_number = uoo.version_number
4953 AND uoo_id = cp_uoo_id
4954 AND uvr.s_rule_call_cd = 'COREQ';
4955
4956 l_rule_seq_num igs_ps_usec_ru.rul_sequence_number%TYPE;
4957 l_rule_text igs_ps_usec_ru_v.rule_text%TYPE;
4958 l_index NUMBER;
4959 l_coreq_units VARCHAR2(1000);
4960 l_coreq_string VARCHAR2(1000);
4961 l_start_index NUMBER;
4962 l_end_index NUMBER;
4963 BEGIN
4964 l_index := 0;
4965 l_start_index := 0;
4966 l_end_index := 0;
4967
4968 --check for rule at unit section level
4969 OPEN c_coreq_usec(p_uoo_id);
4970 FETCH c_coreq_usec INTO l_rule_seq_num;
4971 IF c_coreq_usec%FOUND THEN
4972 l_rule_text := IGS_RU_GEN_003.rulp_get_rule(l_rule_seq_num);
4973 END IF;
4974 CLOSE c_coreq_usec;
4975
4976
4977
4978 --if rule is not found at unit section level, check at unit level
4979 IF l_rule_text IS NULL THEN
4980 OPEN c_coreq_unit(p_uoo_id);
4981 FETCH c_coreq_unit INTO l_rule_seq_num;
4982 IF c_coreq_unit%FOUND THEN
4983 l_rule_text := IGS_RU_GEN_003.rulp_get_rule(l_rule_seq_num);
4984 END IF;
4985 CLOSE c_coreq_unit;
4986 END IF;
4987
4988 --if rule is not found at either unit section or at unit level
4989 IF l_rule_text IS NULL THEN
4990 RETURN NULL;
4991 END IF;
4992
4993 --check if rule contains the pattern 'Any co-req unit in('
4994 IF(INSTR(l_rule_text,'Any co-req unit in {')<>0 ) THEN
4995 --check that rule does not contain any other pattern
4996 IF(INSTR(l_rule_text,'Any passed co-req unit in')=0
4997 AND INSTR(l_rule_text,'Any co-req unit set')=0
4998 AND INSTR(l_rule_text,'Must be enrolled')=0) THEN
4999 --parse the units enclosed between { }
5000 LOOP
5001 l_index := l_index + 1;
5002 l_start_index := INSTR(l_rule_text,'{',1,l_index);
5003 l_end_index := INSTR(l_rule_text,'}',1,l_index);
5004 EXIT WHEN l_start_index = 0;
5005 l_coreq_units := NULL;
5006 l_coreq_units := SUBSTR(l_rule_text, l_start_index+1, ( l_end_index-l_start_index)-1 );
5007
5008
5009 IF l_coreq_string IS NULL THEN
5010 l_coreq_string := l_coreq_units;
5011 ELSE
5012 l_coreq_string := l_coreq_string || ',' || l_coreq_units;
5013 END IF;
5014
5015 END LOOP;
5016
5017 l_coreq_string := l_coreq_string || ',' ;
5018
5019 -- This string may contain unit_cd.version numbers also.
5020 --Hence we need to remove the version numbers from this string and only retain the unit codes
5021 l_coreq_string := parse_coreq1(p_coreq_string=>l_coreq_string);
5022
5023 --This string may contain wild characters like 'unitcd%' also; hence we need to get all the units matching the criteria
5024 l_coreq_string := parse_coreq2(l_coreq_string);
5025 RETURN l_coreq_string;
5026 END IF;
5027 END IF;
5028
5029 RETURN NULL;
5030
5031 END get_coreq_units;
5032
5033 PROCEDURE get_ref_defn_lvls ( p_n_uoo_id IN NUMBER,
5034 p_c_dfn_lvl OUT NOCOPY VARCHAR2,
5035 p_c_unit_cd OUT NOCOPY VARCHAR2,
5036 p_n_version OUT NOCOPY NUMBER,
5037 p_n_us_ref_id OUT NOCOPY NUMBER)
5038 ------------------------------------------------------------------
5039 --Created by : rvangala
5040 --Date created: 24-May-2005
5041 --
5042 --Purpose: This function returns the reference code definition level
5043 --
5044 --Known limitations/enhancements and/or remarks:
5045 --
5046 --Change History:
5047 --Who When What
5048 -------------------------------------------------------------------
5049 IS
5050 CURSOR c_ref_id (cp_n_uoo_id IN NUMBER) IS
5051 SELECT unit_section_reference_id
5052 FROM igs_ps_usec_ref
5053 WHERE uoo_id = cp_n_uoo_id;
5054 rec_ref_id c_ref_id%ROWTYPE;
5055
5056 CURSOR c_us_gen_ref (cp_n_ref_id IN NUMBER) IS
5057 SELECT 1
5058 FROM igs_ps_usec_ref_cd
5059 WHERE unit_section_reference_id =cp_n_ref_id
5060 AND ROWNUM <2;
5061
5062 CURSOR c_us_req_ref (cp_n_ref_id IN NUMBER) IS
5063 SELECT 1
5064 FROM igs_ps_us_req_ref_cd
5065 WHERE unit_section_reference_id =cp_n_ref_id
5066 AND ROWNUM <2;
5067
5068 CURSOR c_uv(cp_n_uoo_id IN NUMBER) IS
5069 SELECT unit_cd,
5070 version_number
5071 FROM igs_ps_unit_ofr_opt_all
5072 WHERE uoo_id = cp_n_uoo_id;
5073
5074
5075 CURSOR c_uv_gen_ref(cp_c_unit_cd IN VARCHAR2, cp_n_version IN NUMBER) IS
5076 SELECT 1
5077 FROM igs_ps_unit_ref_cd
5078 WHERE unit_cd = cp_c_unit_cd
5079 AND version_number = cp_n_version
5080 AND ROWNUM < 2;
5081
5082 CURSOR c_uv_req_ref(cp_c_unit_cd IN VARCHAR2, cp_n_version IN NUMBER) IS
5083 SELECT 1
5084 FROM igs_ps_unitreqref_cd
5085 WHERE unit_cd = cp_c_unit_cd
5086 AND version_number = cp_n_version
5087 AND ROWNUM <2 ;
5088
5089 rec_uv c_uv%ROWTYPE;
5090 rec_us_gen_ref c_us_req_ref%ROWTYPE;
5091 l_b_us_gen boolean;
5092 l_b_us_req boolean;
5093 l_b_uv_gen boolean;
5094 l_b_uv_req boolean;
5095 l_n_temp NUMBER;
5096
5097 BEGIN
5098 OPEN c_ref_id(p_n_uoo_id);
5099 FETCH c_ref_id INTO rec_ref_id;
5100 IF c_ref_id%FOUND THEN
5101 OPEN c_us_req_ref (rec_ref_id.unit_section_reference_id);
5102 FETCH c_us_req_ref INTO l_n_temp;
5103 IF c_us_req_ref%FOUND THEN
5104 l_b_us_req := TRUE;
5105 END IF;
5106 CLOSE c_us_req_ref;
5107
5108 OPEN c_us_gen_ref (rec_ref_id.unit_section_reference_id);
5109 FETCH c_us_gen_ref INTO rec_us_gen_ref;
5110 IF c_us_gen_ref%FOUND THEN
5111 l_b_us_gen := TRUE;
5112 END IF;
5113 CLOSE c_us_gen_ref;
5114
5115 END IF;
5116 CLOSE c_ref_id;
5117
5118 IF (l_b_us_req IS NULL OR l_b_us_gen IS NULL) THEN
5119 OPEN c_uv(p_n_uoo_id);
5120 FETCH c_uv INTO rec_uv;
5121 CLOSE c_uv;
5122 IF l_b_us_req IS NULL THEN
5123 OPEN c_uv_req_ref (rec_uv.unit_cd, rec_uv.version_number);
5124 FETCH c_uv_req_ref INTO l_n_temp;
5125 IF c_uv_req_ref%FOUND THEN
5126 l_b_uv_req := TRUE;
5127 END IF;
5128 CLOSE c_uv_req_ref;
5129 END IF;
5130 IF l_b_us_gen IS NULL THEN
5131 OPEN c_uv_gen_ref (rec_uv.unit_cd, rec_uv.version_number);
5132 FETCH c_uv_gen_ref INTO l_n_temp;
5133 IF c_uv_gen_ref%FOUND THEN
5134 l_b_uv_gen := TRUE;
5135 END IF;
5136 CLOSE c_uv_gen_ref;
5137 END IF;
5138 END IF;
5139
5140 IF (l_b_us_req AND l_b_us_gen) OR
5141 (l_b_us_req AND l_b_us_gen IS NULL AND l_b_uv_gen IS NULL) OR
5142 (l_b_us_gen AND l_b_us_req IS NULL AND l_b_uv_req IS NULL) THEN
5143 p_c_dfn_lvl := 'US';
5144 p_n_us_ref_id := rec_ref_id.unit_section_reference_id;
5145 ELSIF (l_b_us_req AND l_b_uv_gen) THEN
5146 p_c_dfn_lvl := 'US_REQ_UV_GEN';
5147 p_n_us_ref_id := rec_ref_id.unit_section_reference_id;
5148 p_c_unit_cd := rec_uv.unit_cd;
5149 p_n_version := rec_uv.version_number;
5150 ELSIF (l_b_us_gen AND l_b_uv_req) THEN
5151 p_c_dfn_lvl := 'US_GEN_UV_REQ';
5152 p_n_us_ref_id := rec_ref_id.unit_section_reference_id;
5153 p_c_unit_cd := rec_uv.unit_cd;
5154 p_n_version := rec_uv.version_number;
5155 ELSE
5156 p_c_dfn_lvl := 'UV';
5157 p_c_unit_cd := rec_uv.unit_cd;
5158 p_n_version := rec_uv.version_number;
5159 END IF;
5160
5161 END get_ref_defn_lvls;
5162
5163
5164
5165 PROCEDURE get_definition_levels (p_n_uoo_id IN NUMBER,
5166 p_c_notes_lvl OUT NOCOPY VARCHAR2,
5167 p_c_ref_lvl OUT NOCOPY VARCHAR2,
5168 p_c_unit_cd OUT NOCOPY VARCHAR2,
5169 p_n_version OUT NOCOPY NUMBER,
5170 p_n_us_ref_id OUT NOCOPY NUMBER)
5171 ------------------------------------------------------------------
5172 --Created by : rvangala
5173 --Date created: 24-May-2005
5174 --
5175 --Purpose: This procedure gets the notes, grading schema and reference codes definition levels
5176 --
5177 --Known limitations/enhancements and/or remarks:
5178 --
5179 --Change History:
5180 --Who When What
5181 -------------------------------------------------------------------
5182 IS
5183 BEGIN
5184 get_notes_defn_lvl( p_n_uoo_id => p_n_uoo_id,
5185 p_c_dfn_lvl => p_c_notes_lvl);
5186
5187 get_ref_defn_lvls ( p_n_uoo_id => p_n_uoo_id,
5188 p_c_dfn_lvl => p_c_ref_lvl,
5189 p_c_unit_cd => p_c_unit_cd,
5190 p_n_version => p_n_version,
5191 p_n_us_ref_id => p_n_us_ref_id);
5192
5193
5194 END get_definition_levels;
5195
5196
5197 FUNCTION get_sua_core_disp_unit(
5198 p_person_id IN NUMBER ,
5199 p_program_cd IN VARCHAR2 ,
5200 p_uoo_id IN NUMBER )
5201 RETURN VARCHAR2
5202 ------------------------------------------------------------------
5203 --Created by : rvangala
5204 --Date created: 24-May-2005
5205 --
5206 --Purpose: Function to get the unit cd/section with core indicator
5207 -- for a student unit attempt
5208 --
5209 --Known limitations/enhancements and/or remarks:
5210 --
5211 --Change History:
5212 --Who When What
5213 -------------------------------------------------------------------
5214 IS
5215
5216 --
5217 -- Cursor to find the Unit Code
5218 --
5219 CURSOR cur_unit_cd (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
5220 SELECT unit_cd, unit_class, CORE_INDICATOR_CODE
5221 FROM igs_en_su_attempt
5222 WHERE person_id = p_person_id
5223 AND course_cd = p_program_cd
5224 AND uoo_id = p_uoo_id;
5225
5226 --
5227 -- Cursor to find the meaning of lookup code CORE
5228 --
5229 CURSOR cur_get_lkp_meaning IS
5230 SELECT meaning
5231 FROM igs_lookup_values lkup, IGS_EN_SS_DISP_STPS en
5232 WHERE lkup.lookup_type = 'IGS_EN_CORE_IND'
5233 AND lkup.lookup_code = en.core_req_ind;
5234
5235
5236 l_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
5237 l_unit_class igs_ps_unit_ofr_opt.unit_class%TYPE;
5238 l_core_meaning igs_lookup_values.meaning%TYPE;
5239 l_core_ind_cd igs_en_su_attempt_all.CORE_INDICATOR_CODE%TYPE;
5240 BEGIN
5241 -- Get the Unit Code
5242 OPEN cur_unit_cd(p_uoo_id);
5243 FETCH cur_unit_cd INTO l_unit_cd,l_unit_class, l_core_ind_cd;
5244 CLOSE cur_unit_cd;
5245
5246 IF l_core_ind_cd = 'CORE' THEN
5247 -- Get the meaning of lookup code
5248 OPEN cur_get_lkp_meaning;
5249 FETCH cur_get_lkp_meaning INTO l_core_meaning;
5250 CLOSE cur_get_lkp_meaning;
5251
5252 RETURN l_unit_cd||'/'||l_unit_class||'('||l_core_meaning||')';
5253 ELSE
5254 RETURN l_unit_cd||'/'||l_unit_class;
5255 END IF;
5256 END get_sua_core_disp_unit;
5257
5258
5259 FUNCTION get_total_cart_credits(p_personid IN NUMBER,
5260 p_course_cd IN VARCHAR2,
5261 p_term_cal_type IN VARCHAR2,
5262 p_term_seq_num IN NUMBER) RETURN NUMBER
5263 ------------------------------------------------------------------
5264 --Created by : rvangala
5265 --Date created: 24-May-2005
5266 --
5267 --Purpose: Function returns the total credit points in the enrollment cart
5268 -- for a term
5269 --
5270 --Known limitations/enhancements and/or remarks:
5271 --
5272 --Change History:
5273 --Who When What
5274 -------------------------------------------------------------------
5275 AS
5276 --cursor to fetch the total credits from planning sheet unit attempts with cart_error_flag=Y
5277 CURSOR c_total_cart_credits(cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
5278 SELECT SUM(igs_ss_enr_details.get_apor_credits ( uoo_id, override_enrolled_cp,
5279 cp_term_cal_type,cp_term_seq_num)) apor_cp
5280 FROM IGS_EN_PLAN_UNITS pls
5281 WHERE pls.cart_error_flag= 'Y'
5282 AND pls.person_id = p_personid
5283 AND pls.course_cd = p_course_cd
5284 AND pls.term_cal_type = cp_term_cal_type
5285 AND pls.term_ci_sequence_number = cp_term_seq_num;
5286
5287 --cursor to fetch the total credits for all unconfirmed unit attempts from student unit attempts
5288 CURSOR c_total_unconfirm_credits (cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
5289 SELECT SUM(igs_ss_enr_details.get_apor_credits ( uoo_id, override_enrolled_cp,
5290 cp_term_cal_type,cp_term_seq_num)) apor_cp
5291 FROM IGS_EN_SU_ATTEMPT sua
5292 WHERE sua.unit_attempt_status = 'UNCONFIRM'
5293 AND sua.person_id = p_personid
5294 AND sua.course_cd = p_course_cd
5295 AND (sua.cal_type , sua.ci_sequence_number) in (select teach_cal_type, teach_ci_sequence_number from igs_ca_teach_to_load_v where
5296 load_cal_type = cp_term_cal_type and load_ci_sequence_number = cp_term_seq_num)
5297 AND ss_source_ind <> 'S';
5298
5299
5300 l_total_cart_credits NUMBER;
5301 l_total_unconfirm_credits NUMBER;
5302
5303 BEGIN
5304
5305 -- fetch the total credits points of planning sheet cart units
5306 OPEN c_total_cart_credits(p_term_cal_type,p_term_seq_num);
5307 FETCH c_total_cart_credits INTO l_total_cart_credits;
5308 CLOSE c_total_cart_credits;
5309
5310 --fetch the total credit points of unconfirmed unit attempts
5311 OPEN c_total_unconfirm_credits(p_term_cal_type,p_term_seq_num);
5312 FETCH c_total_unconfirm_credits INTO l_total_unconfirm_credits;
5313 CLOSE c_total_unconfirm_credits;
5314
5315 --return the sum of planned cart units and unconfirmed unit attempts
5316 RETURN NVL(l_total_cart_credits,0) + NVL(l_total_unconfirm_credits,0);
5317
5318 END;
5319
5320 FUNCTION get_sca_unit_sets( p_person_id IN NUMBER ,
5321 p_program_cd IN VARCHAR2 ,
5322 p_term_cal_type IN VARCHAR2,
5323 p_term_sequence_number IN NUMBER) RETURN VARCHAR2 AS
5324 ------------------------------------------------------------------
5325 --Created by : Somasekar.IDC
5326 --Date created: 12-July-2005
5327 --
5328 --Purpose: Function returns the total credit points in the enrollment cart
5329 -- for a term
5330 --
5331 --Known limitations/enhancements and/or remarks:
5332 --
5333 --Change History:
5334 --Who When What
5335 -------------------------------------------------------------------
5336 -- cursor to fetch census date values
5337 CURSOR c_term_cen_dates (cp_cal_type IN VARCHAR2, cp_cal_seq_number IN NUMBER) IS
5338 SELECT NVL (absolute_val,
5339 igs_ca_gen_001.calp_get_alias_val (
5340 dai.dt_alias,
5341 dai.sequence_number,
5342 dai.cal_type,
5343 dai.ci_sequence_number
5344 )
5345 ) AS term_census_date
5346 FROM igs_ge_s_gen_cal_con sgcc,
5347 igs_ca_da_inst dai
5348 WHERE sgcc.s_control_num = 1
5349 AND dai.dt_alias = sgcc.census_dt_alias
5350 AND dai.cal_type = cp_cal_type
5351 AND dai.ci_sequence_number = cp_cal_seq_number
5352 ORDER by 1 desc;
5353
5354 -- cursor to fetch the unit set title
5355 CURSOR c_us_title (cp_person_id NUMBER,
5356 cp_program_cd VARCHAR2,
5357 cp_term_census_date DATE,
5358 cp_unit_set IN VARCHAR2,
5359 cp_version IN NUMBER) IS
5360 SELECT NVL(susa.override_title, us.title) title,
5361 NVL(susa.rqrmnts_complete_dt,NVL(susa.end_dt, cp_term_census_date)) end_date
5362 FROM igs_as_su_setatmpt susa ,
5363 igs_en_unit_set us ,
5364 igs_en_unit_set_cat usc
5365 WHERE susa.person_id = cp_person_id
5366 AND susa.course_cd = cp_program_cd
5367 AND susa.student_confirmed_ind = 'Y'
5368 AND cp_term_census_date
5369 BETWEEN susa.selection_dt
5370 AND NVL(susa.rqrmnts_complete_dt,NVL(susa.end_dt, cp_term_census_date))
5371 AND susa.unit_set_cd = us.unit_set_cd
5372 AND us.unit_set_cat = usc.unit_set_cat
5373 AND usc.s_unit_set_cat <> 'PRENRL_YR'
5374 AND susa.unit_set_cd = cp_unit_set
5375 AND susa.us_version_number = cp_version
5376 ORDER BY end_date, susa.selection_dt DESC;
5377
5378 CURSOR c_unit_sets (cp_person_id IN NUMBER, cp_program_cd IN VARCHAR2) IS
5379 SELECT DISTINCT susa.unit_set_cd, susa.us_version_number
5380 FROM igs_as_su_setatmpt susa
5381 WHERE susa.person_id = cp_person_id
5382 AND susa.course_cd = cp_program_cd
5383 AND susa.student_confirmed_ind = 'Y';
5384
5385 l_pre_title VARCHAR2(2000);
5386 l_oth_title VARCHAR2(2000);
5387 l_c_temp igs_as_su_setatmpt.override_title%TYPE;
5388 l_c_title VARCHAR2(4000);
5389
5390 BEGIN
5391 l_pre_title := get_stud_yop_unit_set ( p_person_id => p_person_id,
5392 p_program_cd => p_program_cd,
5393 p_term_cal_type =>p_term_cal_type,
5394 p_term_sequence_number => p_term_sequence_number);
5395
5396 FOR rec_term_cen_dates IN c_term_cen_dates(p_term_cal_type, p_term_sequence_number) LOOP
5397 FOR rec_unit_sets IN c_unit_sets(p_person_id, p_program_cd) LOOP
5398 l_c_temp := NULL;
5399 FOR rec_us_title IN c_us_title( p_person_id, p_program_cd,rec_term_cen_dates.term_census_date,rec_unit_sets.unit_set_cd, rec_unit_sets.us_version_number) LOOP
5400 l_c_temp := rec_us_title.title;
5401 EXIT;
5402 END LOOP;
5403 IF l_c_temp IS NOT NULL THEN
5404 l_oth_title := l_oth_title || ',' || l_c_temp;
5405 END IF;
5406 END LOOP;
5407 END LOOP;
5408 IF l_pre_title IS NOT NULL AND l_oth_title IS NOT NULL THEN
5409 l_c_title := l_pre_title || ',' || SUBSTR(l_oth_title,2);
5410 ELSIF l_pre_title IS NOT NULL THEN
5411 l_c_title := l_pre_title;
5412 ELSIF l_oth_title IS NOT NULL THEN
5413 l_c_title := SUBSTR(l_oth_title,2);
5414 ELSE
5415 l_c_title := NULL;
5416 END IF;
5417
5418 RETURN l_c_title;
5419
5420 END get_sca_unit_sets;
5421
5422 FUNCTION get_sup_sub_text (
5423 p_uoo_id IN NUMBER,
5424 p_sup_uoo_id IN NUMBER,
5425 p_relation_type IN VARCHAR2
5426 ) RETURN VARCHAR2 AS
5427 ------------------------------------------------------------------
5428 --Created by : Somasekar, Oracle IDC
5429 --Date created: 12-July-2005
5430 --
5431 --Purpose: This Function returns teh relation type of the unit section.
5432 --if the parameter p_uoo_Id is superior , it returns 'SUPERIOR'
5433 --if subordiante it returns 'SBORDINATE' concatenated to the superior unit code
5434 --
5435 --Known limitations/enhancements and/or remarks:
5436 --
5437 --Change History:
5438 --Who When What
5439 -------------------------------------------------------------------
5440 CURSOR c_lkups (CP_LOOKUP_CODE IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE) IS
5441 SELECT meaning
5442 FROM igs_lookup_values
5443 WHERE lookup_code = cp_lookup_code
5444 AND lookup_type = 'UOO_RELATION_TYPE';
5445
5446 CURSOR c_sup_unit_cd (CP_SUP_UOO_ID IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE) IS
5447 SELECT unit_cd
5448 FROM igs_ps_unit_ofr_opt
5449 WHERE uoo_id = cp_sup_uoo_id;
5450
5451 v_sup_unit_cd IGS_PS_UNIT_VER.UNIT_CD%TYPE;
5452 l_meaning IGS_LOOKUPS_VIEW.MEANING%TYPE;
5453
5454 BEGIN
5455
5456 IF NVL(p_relation_type,'NONE') = 'NONE' THEN
5457 RETURN NULL;
5458 END IF;
5459
5460 OPEN c_lkups(p_relation_type);
5461 FETCH c_lkups INTO l_meaning;
5462 CLOSE c_lkups;
5463
5464 IF p_relation_type = 'SUPERIOR' THEN
5465 RETURN '('||l_meaning||')';
5466 ELSIF p_relation_type = 'SUBORDINATE' THEN
5467
5468 IF p_sup_uoo_id IS NOT NULL THEN
5469 OPEN c_sup_unit_cd(p_sup_uoo_id);
5470 FETCH c_sup_unit_cd INTO v_sup_unit_cd;
5471 IF c_sup_unit_cd%FOUND THEN
5472 CLOSE c_sup_unit_cd;
5473 RETURN '('|| FND_MESSAGE.GET_STRING('IGS','IGS_EN_SUBORDINATE_TO') || ' ' || v_sup_unit_cd||')';
5474 ELSE
5475 CLOSE c_sup_unit_cd;
5476 RETURN NULL;
5477 END IF;
5478 ELSE
5479 RETURN NULL;
5480 END IF;
5481 END IF;
5482
5483 END get_sup_sub_text;
5484
5485 FUNCTION is_enr_open(p_load_cal IN varchar2,
5486 p_load_seq_num IN Number,
5487 p_d_date IN DATE,
5488 p_n_uoo_id IN NUMBER)
5489 RETURN VARCHAR2
5490 ------------------------------------------------------------------
5491 --Created by : vijrajag
5492 --Date created: 04-July-2005
5493 --
5494 --Purpose: Function to check whether enrollment open (record cutoff is open)
5495 --
5496 --Known limitations/enhancements and/or remarks:
5497 --
5498 --Change History:
5499 --Who When What
5500 -------------------------------------------------------------------
5501 IS
5502 l_c_message VARCHAR2 (30);
5503 l_n_uoo_id NUMBER;
5504 BEGIN
5505 IF igs_en_gen_004.enrp_get_rec_window(p_load_cal, p_load_seq_num, p_d_date, p_n_uoo_id, l_c_message) THEN
5506 IF l_c_message IS NULL THEN
5507 RETURN 'Y';
5508 END IF;
5509 END IF;
5510 RETURN 'N';
5511 END is_enr_open;
5512
5513 FUNCTION get_total_cart_units(p_personid NUMBER,
5514 p_course_cd VARCHAR2,
5515 p_term_cal_type VARCHAR2,
5516 p_term_seq_num NUMBER)
5517 RETURN NUMBER
5518 ------------------------------------------------------------------
5519 --Created by : Siva Gurusamy, Oracle IDC
5520 --Date created: 12-Aug-05
5521 --
5522 --Purpose:
5523 -- This is a new function to get the total attempted units in the cart for a student, program and term
5524 -- Implementation:
5525 -- 1. Get the total units in the planning sheet with cart error flag as Y for the student in selected program and term.
5526 -- 2. Get the total units of all UNCONFIRM unit sections in the student unit attempt for the student in selected program and term.
5527 -- 3. Sum and return the total units from the planning sheet and student unit attempts
5528 --
5529 --Known limitations/enhancements and/or remarks:
5530 --
5531 --Change History:
5532 --Who When What
5533 --sgurusam 12-Aug-05 Created
5534 -------------------------------------------------------------------
5535 AS
5536 --cursor to fetch the total planning sheet unit attempts with cart_error_flag=Y
5537 CURSOR c_total_cart_units(cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
5538 SELECT count(uoo_id)
5539 FROM IGS_EN_PLAN_UNITS pls
5540 WHERE pls.cart_error_flag= 'Y'
5541 AND pls.person_id = p_personid
5542 AND pls.course_cd = p_course_cd
5543 AND pls.term_cal_type = cp_term_cal_type
5544 AND pls.term_ci_sequence_number = cp_term_seq_num;
5545
5546 --cursor to fetch the total unconfirmed unit attempts
5547 CURSOR c_total_unconfirm_units(cp_term_cal_type VARCHAR2, cp_term_seq_num NUMBER) IS
5548 SELECT count(uoo_id)
5549 FROM IGS_EN_SU_ATTEMPT sua
5550 WHERE sua.unit_attempt_status = 'UNCONFIRM'
5551 AND sua.person_id = p_personid
5552 AND sua.course_cd = p_course_cd
5553 AND sua.ss_source_ind <> 'S'
5554 AND (sua.cal_type , sua.ci_sequence_number) in (SELECT teach_cal_type, teach_ci_sequence_number
5555 FROM igs_ca_teach_to_load_v
5556 WHERE load_cal_type = cp_term_cal_type
5557 AND load_ci_sequence_number = cp_term_seq_num);
5558
5559
5560
5561 l_total_cart_units NUMBER;
5562 l_total_unconfirm_units NUMBER;
5563 l_total_units NUMBER;
5564
5565 BEGIN
5566 l_total_units :=0;
5567
5568 -- fetch the total planning sheet unit attempts
5569 OPEN c_total_cart_units(p_term_cal_type,p_term_seq_num);
5570 FETCH c_total_cart_units INTO l_total_cart_units;
5571 CLOSE c_total_cart_units;
5572
5573 --fetch the total unconfirmed unit attempts
5574 OPEN c_total_unconfirm_units(p_term_cal_type,p_term_seq_num);
5575 FETCH c_total_unconfirm_units INTO l_total_unconfirm_units;
5576 CLOSE c_total_unconfirm_units;
5577
5578 IF l_total_cart_units IS NULL THEN
5579 l_total_cart_units := 0;
5580 END IF;
5581
5582 IF l_total_unconfirm_units IS NULL THEN
5583 l_total_unconfirm_units := 0;
5584 END IF;
5585
5586 --return the sum of planned and unconfirmed units
5587 l_total_units := l_total_cart_units + l_total_unconfirm_units;
5588 RETURN l_total_units;
5589
5590 END get_total_cart_units;
5591
5592
5593
5594 END igs_ss_enr_details;
[Home] [Help]