[Home] [Help]
PACKAGE BODY: APPS.IGS_SS_ENROLL_PKG
Source
1 package body igs_ss_enroll_pkg as
2 /* $Header: IGSSS04B.pls 120.0 2005/06/01 18:33:09 appldev noship $ */
3
4 PROCEDURE insert_into_enr_cart
5 (
6 p_return_status out NOCOPY varchar2,
7 p_msg_count out NOCOPY number,
8 p_msg_data out NOCOPY varchar2,
9 p_person_id in varchar2,
10 p_cal_type in varchar2,
11 p_ci_sequence_number in varchar2,
12 p_call_number in varchar2,
13 p_org_id in number
14 )
15 is
16 lv_count number := 0 ;
17 begin
18 select count(*) into lv_count
19 from igs_ss_su_selection
20 where person_id = p_person_id
21 and call_number = p_call_number
22 and cal_type = p_cal_type
23 and ci_sequence_number = p_ci_sequence_number ;
24 if lv_count = 0
25 then
26 begin
27 insert into igs_ss_su_selection
28 (
29 usec_su_selection_id ,
30 person_id ,
31 unit_cd ,
32 version_number ,
33 cal_type ,
34 ci_sequence_number ,
35 location_cd ,
36 unit_class ,
37 uoo_id ,
38 call_number ,
39 enrolled_dt ,
40 enrolled_cp ,
41 grading_schema_cd ,
42 unit_attempt_status ,
43 created_by ,
44 creation_date ,
45 last_updated_by ,
46 last_update_date ,
47 last_update_login ,
48 org_id
49 )
50 select
51 igs_ss_su_selection_s.nextval,
52 p_person_id,
53 unit_cd,
54 version_number ,
55 cal_type ,
56 ci_sequence_number ,
57 location_cd ,
58 unit_class ,
59 uoo_id ,
60 call_number ,
61 sysdate ,
62 null ,
63 null ,
64 'CART' ,
65 p_person_id ,
66 sysdate ,
67 p_person_id ,
68 sysdate ,
69 p_person_id ,
70 p_org_id
71 from igs_ps_unit_ofr_opt
72 where cal_type = p_cal_type
73 and ci_sequence_number = p_ci_sequence_number
74 and call_number = p_call_number ;
75 if sql%rowcount = 0
76 then
77 fnd_message.set_name('IGS','IGS_SS_SU_NO_UNITS_FOUND');
78 fnd_msg_pub.add;
79 p_return_status := fnd_api.g_ret_sts_error;
80 end if ;
81 exception
82 when others
83 then
84 -- fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
85 fnd_message.set_name('IGS',sqlerrm);
86 fnd_msg_pub.add;
87 p_return_status := fnd_api.g_ret_sts_error;
88 end ;
89 else
90 fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
91 fnd_msg_pub.add;
92 p_return_status := fnd_api.g_ret_sts_error;
93 end if ;
94
95 fnd_msg_pub.count_and_get
96 (
97 p_count => p_msg_count,
98 p_data => p_msg_data
99 );
100 end insert_into_enr_cart;
101
102 PROCEDURE insert_into_enr_cart
103 (
104 p_return_status out NOCOPY varchar2,
105 p_msg_count out NOCOPY number,
106 p_msg_data out NOCOPY varchar2,
107 p_insert_flag out NOCOPY varchar2,
108 p_person_id in varchar2,
109 p_cal_type in varchar2,
110 p_ci_sequence_number in varchar2,
111 p_unit_cd in varchar2,
112 p_unit_class in varchar2,
113 p_org_id in number
114 )
115 is
116 lv_count number(2) := 0 ;
117 begin
118 begin
119 select
120 count(unit_cd)
121 into lv_count
122 from igs_ps_unit_ofr_opt
123 where cal_type = p_cal_type
124 and ci_sequence_number = p_ci_sequence_number
125 and lower(unit_cd) = lower(p_unit_cd)
126 and lower(unit_class) = lower(p_unit_class);
127 if lv_count = 0
128 then
129 fnd_message.set_name('IGS','IGS_SS_SU_NO_UNITS_FOUND');
130 fnd_msg_pub.add;
131 p_return_status := fnd_api.g_ret_sts_error;
132 p_insert_flag := 'NA' ;
133 elsif lv_count = 1
134 then
135 begin
136 insert into igs_ss_su_selection
137 (
138 usec_su_selection_id ,
139 person_id ,
140 unit_cd ,
141 version_number ,
142 cal_type ,
143 ci_sequence_number ,
144 location_cd ,
145 unit_class ,
146 uoo_id ,
147 call_number ,
148 enrolled_dt ,
149 enrolled_cp ,
150 grading_schema_cd ,
151 unit_attempt_status ,
152 created_by ,
153 creation_date ,
154 last_updated_by ,
155 last_update_date ,
156 last_update_login,
157 org_id
158 )
159 select
160 igs_ss_su_selection_s.nextval,
161 p_person_id,
162 unit_cd,
163 version_number ,
164 cal_type ,
165 ci_sequence_number ,
166 location_cd ,
167 unit_class ,
168 uoo_id ,
169 call_number ,
170 sysdate ,
171 null ,
172 null ,
173 'CART' ,
174 p_person_id ,
175 sysdate ,
176 p_person_id ,
177 sysdate ,
178 p_person_id,
179 p_org_id
180 from igs_ps_unit_ofr_opt
181 where cal_type = p_cal_type
182 and ci_sequence_number = p_ci_sequence_number
183 and upper(unit_cd) = upper(p_unit_cd)
184 and upper(unit_class) = upper(p_unit_class) ;
185 p_insert_flag := 'Y' ;
186 if sql%rowcount = 0
187 then
188 fnd_message.set_name('IGS','IGS_SS_SU_NO_UNITS_FOUND');
189 fnd_msg_pub.add;
190 p_return_status := fnd_api.g_ret_sts_error;
191 end if ;
192 exception
193 when dup_val_on_index
194 then
195 fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
196 fnd_msg_pub.add;
197 p_return_status := fnd_api.g_ret_sts_error;
198 end ;
199 else
200 p_insert_flag := 'N' ;
201 end if ;
202 exception when others
203 then
204 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
205 fnd_msg_pub.add;
206 p_return_status := fnd_api.g_ret_sts_error;
207 end ;
208 fnd_msg_pub.count_and_get
209 (
210 p_count => p_msg_count,
211 p_data => p_msg_data
212 );
213 end insert_into_enr_cart;
214
215 PROCEDURE insert_into_enr_cart
216 (
217 p_return_status out NOCOPY varchar2,
218 p_msg_count out NOCOPY number,
219 p_msg_data out NOCOPY varchar2,
220 p_person_id in varchar2,
221 p_uoo_id in varchar2,
222 p_org_id in number
223 )
224 as
225 lv_count number := 0 ;
226 begin
227 select count(*) into lv_count
228 from igs_ss_su_selection
229 where person_id = p_person_id
230 and uoo_id = p_uoo_id ;
231 if lv_count = 0
232 then
233 begin
234 insert into igs_ss_su_selection
235 (
236 usec_su_selection_id ,
237 person_id ,
238 unit_cd ,
239 version_number ,
240 cal_type ,
241 ci_sequence_number ,
242 location_cd ,
243 unit_class ,
244 uoo_id ,
245 call_number ,
246 enrolled_dt ,
247 enrolled_cp ,
248 grading_schema_cd ,
249 unit_attempt_status ,
250 created_by ,
251 creation_date ,
252 last_updated_by ,
253 last_update_date ,
254 last_update_login ,
255 org_id
256 )
257 select
258 igs_ss_su_selection_s.nextval,
259 p_person_id ,
260 a.unit_cd ,
261 a.version_number ,
262 a.cal_type ,
263 a.ci_sequence_number ,
264 a.location_cd ,
265 a.unit_class ,
266 a.uoo_id ,
267 a.call_number,
268 sysdate,
269 null,
270 null,
271 'CART',
272 p_person_id,
273 sysdate,
274 p_person_id,
275 sysdate,
276 p_person_id,
277 p_org_id
278 from igs_ps_unit_ofr_opt a
279 where uoo_id = p_uoo_id ;
280 exception
281 when dup_val_on_index
282 then
283 fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
284 fnd_msg_pub.add;
285 p_return_status := fnd_api.g_ret_sts_error;
286 when others
287 then
288 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
289 fnd_msg_pub.add;
290 p_return_status := fnd_api.g_ret_sts_error;
291 end ;
292 else
293 fnd_message.set_name('IGS','IGS_SS_SU_SELECTION_PK');
294 fnd_msg_pub.add;
295 p_return_status := fnd_api.g_ret_sts_error;
296 end if ;
297 fnd_msg_pub.count_and_get
298 (
299 p_count => p_msg_count,
300 p_data => p_msg_data
301 );
302 exception when others
303 then
304 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
305 fnd_msg_pub.Add;
306 p_return_status := fnd_api.g_ret_sts_error;
307 fnd_msg_pub.count_and_get
308 (
309 p_count => p_msg_count,
310 p_data => p_msg_data
311 );
312 end insert_into_enr_cart;
313
314 PROCEDURE remove_from_shopping_cart
315 (
316 p_return_status out NOCOPY varchar2,
317 p_msg_count out NOCOPY number,
318 p_msg_data out NOCOPY varchar2,
319 p_person_id in varchar2,
320 p_uoo_id in varchar2,
321 p_course_cd in varchar2
322 )
323 is
324 begin
325 begin
326 delete from igs_ss_su_selection
327 where person_id = p_person_id
328 and uoo_id = p_uoo_id ;
329 if sql%rowcount = 0
330 then
331 fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
332 fnd_msg_pub.add;
333 p_return_status := fnd_api.g_ret_sts_error;
334 end if ;
335 exception
336 when too_many_rows
337 then
338 fnd_message.set_name('IGS','IGS_SS_ENR_CART_TOO_MANY_ROWS');
339 fnd_msg_pub.Add;
340 p_return_status := fnd_api.g_ret_sts_error;
341 when others
342 then
343 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
344 fnd_msg_pub.Add;
345 p_return_status := fnd_api.g_ret_sts_error;
346 end ;
347 fnd_msg_pub.count_and_get
348 (
349 p_count => p_msg_count,
350 p_data => p_msg_data
351 );
352 end remove_from_shopping_cart;
353
354 PROCEDURE insert_into_su_attempt
355 (
356 p_return_status out NOCOPY varchar2,
357 p_msg_count out NOCOPY number,
358 p_msg_data out NOCOPY varchar2,
359 p_org_id in number ,
360 p_person_id in varchar2,
361 p_course_cd in varchar2,
362 p_uoo_id in varchar2,
363 p_grading_schema in varchar2,
364 p_enrolled_cp in varchar2
365 )
366 is
367 l_course_cd varchar2(6) ;
368 begin
369 if nvl(p_course_cd,' ') = ' '
370 then
371 begin
372 SELECT
373 a.course_cd
374 into l_course_cd
375 from igs_en_stdnt_ps_att a
376 where nvl(a.course_attempt_status,' ') not in ('INACTIVE')
377 and person_id = p_person_id ;
378 exception when others
379 then
380 fnd_message.set_name('IGS',sqlerrm);
381 --fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
382 fnd_msg_pub.add;
383 p_return_status := fnd_api.g_ret_sts_error;
384 end ;
385 else
386 l_course_cd := p_course_cd ;
387 end if ;
388 begin
389 insert into igs_en_su_attempt
390 (
391 org_id,
392 person_id,
393 course_cd,
394 unit_cd,
395 version_number,
396 cal_type,
397 ci_sequence_number,
398 location_cd,
399 unit_class,
400 ci_start_dt,
401 ci_end_dt,
402 uoo_id,
403 unit_attempt_status,
404 no_assessment_ind,
405 created_by,
406 creation_date,
407 last_updated_by,
408 last_update_date,
409 override_achievable_cp
410 )
411 select
412 p_org_id,
413 p_person_id,
414 l_course_cd,
415 a.unit_cd,
416 a.version_number,
417 a.cal_type,
418 a.ci_sequence_number,
419 a.location_cd,
420 a.unit_class,
421 b.start_dt,
422 b.end_dt,
423 a.uoo_id,
424 'ENROLLED',
425 'N',
426 p_person_id,
427 sysdate,
428 p_person_id,
429 sysdate,
430 p_enrolled_cp
431 from igs_ps_unit_ofr_opt a,igs_ca_inst b
432 where a.uoo_id = p_uoo_id
433 and b.sequence_number = a.ci_sequence_number
434 and b.cal_type = a.cal_type ;
435 if sql%rowcount = 0
436 then
437 fnd_message.set_name('IGS','IGS_SS_SU_NO_UNITS_FOUND');
438 fnd_msg_pub.add;
439 p_return_status := fnd_api.g_ret_sts_error;
440 end if ;
441 exception
442 when dup_val_on_index
443 then
444 fnd_message.set_name('IGS','IGS_SS_SU_ENROLLMENT_PK');
445 fnd_msg_pub.add;
446 p_return_status := fnd_api.g_ret_sts_error;
447 when others
448 then
449 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
450 fnd_msg_pub.add;
451 p_return_status := fnd_api.g_ret_sts_error;
452 end ;
453
454 begin
455 delete from igs_ss_su_selection
456 where person_id = p_person_id
457 and uoo_id = p_uoo_id ;
458 if sql%rowcount = 0
459 then
460 fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
461 fnd_msg_pub.add;
462 p_return_status := fnd_api.g_ret_sts_error;
463 end if ;
464 end ;
465
466 fnd_msg_pub.count_and_get
467 (
468 p_count => p_msg_count,
469 p_data => p_msg_data
470 );
471
472 end insert_into_su_attempt;
473
474 PROCEDURE delete_from_su_attempt
475 (
476 p_return_status out NOCOPY varchar2,
477 p_msg_count out NOCOPY number,
478 p_msg_data out NOCOPY varchar2,
479 p_org_id in number ,
480 p_person_id in varchar2,
481 p_course_cd in varchar2,
482 p_uoo_id in varchar2
483 )
484 is
485 begin
486 begin
487 delete from igs_en_su_attempt
488 where person_id = p_person_id
489 and course_cd = p_course_cd
490 and uoo_id = p_uoo_id
491 and org_id = p_org_id ;
492 if sql%rowcount = 0
493 then
494 fnd_message.set_name('IGS','IGS_SS_ENR_CART_NO_DELETE');
495 fnd_msg_pub.add;
496 p_return_status := fnd_api.g_ret_sts_error;
497 end if ;
498 exception
499 when others
500 then
501 fnd_message.set_name('IGS','IGS_SS_WHEN_OTHERS');
502 fnd_msg_pub.add;
503 p_return_status := fnd_api.g_ret_sts_error;
504 end ;
505 end delete_from_su_attempt;
506
507
508 FUNCTION get_Sch_disp_acad(
509 p_person_id IN NUMBER ,
510 p_program_cd IN VARCHAR2)
511 RETURN VARCHAR2 AS
512 ------------------------------------------------------------------
513 --Created by : knaraset, Oracle IDC
514 --Date created: 03-NOV-2003
515 --
516 --Purpose:This Function returns the concatendated current academic calendar
517 --
518 --Known limitations/enhancements and/or remarks:
519 --
520 --Change History:
521 --Who When What
522 -------------------------------------------------------------------
523
524 l_acad_year_flag igs_en_ss_disp_stps.academic_year_flag%TYPE;
525 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
526 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
527 l_message VARCHAR2(300);
528 BEGIN
529
530 -- Get the superior academic calendar instance
531 Igs_En_Gen_015.get_academic_cal
532 (
533 p_person_id => p_person_id,
534 p_course_cd => p_program_cd,
535 p_acad_cal_type => l_acad_cal_type,
536 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
537 p_message => l_message,
538 p_effective_dt => SYSDATE
539 );
540
541 RETURN l_acad_ci_sequence_number||l_acad_cal_type;
542
543 EXCEPTION
544 WHEN OTHERS THEN
548 END get_Sch_disp_acad;
545 -- supress any error message raised as this function is used in query
546 RETURN NULL;
547
549
550 FUNCTION get_Sch_disp_term(
551 p_person_id IN NUMBER ,
552 p_program_cd IN VARCHAR2)
553 RETURN VARCHAR2 AS
554 ------------------------------------------------------------------
555 --Created by : knaraset, Oracle IDC
556 --Date created: 03-NOV-2003
557 --
558 --Purpose:This Function returns the concatendated current Term calendar related to the current academic calendar
559 --
560 --Known limitations/enhancements and/or remarks:
561 --
562 --Change History:
563 --Who When What
564 -------------------------------------------------------------------
565
566 -- Get the Schedule display indicator
567 CURSOR c_sch_disp IS
568 SELECT academic_year_flag
569 FROM igs_en_ss_disp_stps;
570
571 -- Get the first Term calendar
572 CURSOR c_first_term_acad(cp_acad_cal_type VARCHAR2,cp_acad_ci_sequence_number NUMBER) IS
573 SELECT ci.sequence_number||ci.cal_type
574 FROM igs_ca_inst ci,
575 igs_ca_inst_rel cir,
576 igs_ca_type ct,
577 igs_ca_stat cs
578 WHERE ci.cal_type = cir.sub_cal_type AND
579 ci.sequence_number = cir.sub_ci_sequence_number AND
580 ci.cal_type = ct.cal_type AND
581 ct.s_cal_cat = 'LOAD' AND
582 ci.cal_status = cs.cal_status AND
583 cs.s_cal_status = 'ACTIVE' AND
584 cir.sup_cal_type = cp_acad_cal_type AND
585 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number
586 ORDER BY ci.start_dt;
587
588 -- Get the academic calendar
589 CURSOR c_acad_cal(cp_person_id NUMBER, cp_program_cd VARCHAR2) IS
590 SELECT cal_type
591 FROM igs_en_stdnt_ps_att
592 WHERE person_id = cp_person_id AND
593 course_cd = cp_program_cd;
594
595 l_acad_year_flag igs_en_ss_disp_stps.academic_year_flag%TYPE;
596 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
597 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
598 l_message VARCHAR2(300);
599 l_term_calendar VARCHAR2(30);
600 l_schedule_disp igs_en_ss_disp_stps.academic_year_flag%TYPE;
601 l_load_cal_type igs_ca_inst.cal_type%TYPE;
602 l_load_ci_seq_num igs_ca_inst.sequence_number%TYPE;
603 l_load_ci_alt_code igs_ca_inst.alternate_code%TYPE ;
604 l_load_ci_start_dt igs_ca_inst.start_dt%TYPE ;
605 l_load_ci_end_dt igs_ca_inst.end_dt%TYPE ;
606
607 BEGIN
608
609 l_schedule_disp := 'N';
610
611 OPEN c_sch_disp;
612 FETCH c_sch_disp INTO l_schedule_disp;
613 CLOSE c_sch_disp;
614
615 IF l_schedule_disp = 'Y' THEN
616 -- Get the superior academic calendar instance
617 Igs_En_Gen_015.get_academic_cal
618 (
619 p_person_id => p_person_id,
620 p_course_cd => p_program_cd,
621 p_acad_cal_type => l_acad_cal_type,
622 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
623 p_message => l_message,
624 p_effective_dt => SYSDATE
625 );
626
627 -- Get the first term calendar associated with the academic calendar
628 OPEN c_first_term_acad(l_acad_cal_type,l_acad_ci_sequence_number);
629 FETCH c_first_term_acad INTO l_term_calendar;
630 CLOSE c_first_term_acad;
631 ELSE
632
633 OPEN c_acad_cal(p_person_id, p_program_cd);
634 FETCH c_acad_cal INTO l_acad_cal_type;
635 CLOSE c_acad_cal;
636
637 igs_en_gen_015.get_curr_acad_term_cal (
638 p_acad_cal_type => l_acad_cal_type,
639 p_effective_dt => SYSDATE,
640 p_load_cal_type => l_load_cal_type,
641 p_load_ci_seq_num => l_load_ci_seq_num,
642 p_load_ci_alt_code => l_load_ci_alt_code,
643 p_load_ci_start_dt => l_load_ci_start_dt,
644 p_load_ci_end_dt => l_load_ci_end_dt,
645 p_message_name => l_message);
646
647 l_term_calendar := l_load_ci_seq_num||l_load_cal_type;
648 END IF;
649
650 RETURN l_term_calendar;
651 EXCEPTION
652 WHEN OTHERS THEN
653 -- supress any error message raised as this function is used in query
654 RETURN NULL;
655
656 END get_Sch_disp_term;
657
658 FUNCTION get_Sch_disp_term_st_dt(
659 p_person_id IN NUMBER ,
660 p_program_cd IN VARCHAR2)
661 RETURN DATE AS
662 ------------------------------------------------------------------
663 --Created by : knaraset, Oracle IDC
664 --Date created: 03-NOV-2003
665 --
666 --Purpose:This Function returns the start date of the current Term calendar.
667 --
668 --Known limitations/enhancements and/or remarks:
669 --
670 --Change History:
671 --Who When What
672 --rvangala 16-JUL-2004 Changed call to igs_en_gen_015.get_curr_acad_term_cal
673 -- with igs_en_gen_015.get_curr_term_for_schedule
674 -------------------------------------------------------------------
675
676 -- Get the Schedule display indicator
677 CURSOR c_sch_disp IS
678 SELECT academic_year_flag
679 FROM igs_en_ss_disp_stps;
680
681 -- Get the first Term calendar
682 CURSOR c_first_term_acad(cp_acad_cal_type VARCHAR2,cp_acad_ci_sequence_number NUMBER) IS
683 SELECT ci.start_dt
684 FROM igs_ca_inst ci,
685 igs_ca_inst_rel cir,
686 igs_ca_type ct,
687 igs_ca_stat cs
688 WHERE ci.cal_type = cir.sub_cal_type AND
689 ci.sequence_number = cir.sub_ci_sequence_number AND
690 ci.cal_type = ct.cal_type AND
691 ct.s_cal_cat = 'LOAD' AND
692 ci.cal_status = cs.cal_status AND
693 cs.s_cal_status = 'ACTIVE' AND
694 cir.sup_cal_type = cp_acad_cal_type AND
695 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number
696 ORDER BY ci.start_dt;
697
698 -- Get the academic calendar
699 CURSOR c_acad_cal(cp_person_id NUMBER, cp_program_cd VARCHAR2) IS
700 SELECT cal_type
701 FROM igs_en_stdnt_ps_att
702 WHERE person_id = cp_person_id AND
703 course_cd = cp_program_cd;
704
705 l_acad_year_flag igs_en_ss_disp_stps.academic_year_flag%TYPE;
706 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
707 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
708 l_message VARCHAR2(300);
709 l_schedule_disp igs_en_ss_disp_stps.academic_year_flag%TYPE;
710 l_term_start_dt DATE ;
711
712 l_load_cal_type igs_ca_inst.cal_type%TYPE;
713 l_load_ci_seq_num igs_ca_inst.sequence_number%TYPE;
714 l_load_ci_alt_code igs_ca_inst.alternate_code%TYPE ;
715 l_load_ci_start_dt igs_ca_inst.start_dt%TYPE ;
716 l_load_ci_end_dt igs_ca_inst.end_dt%TYPE ;
717
718 BEGIN
719 l_term_start_dt := SYSDATE;
720 l_schedule_disp := 'N';
721
722 OPEN c_sch_disp;
723 FETCH c_sch_disp INTO l_schedule_disp;
724 CLOSE c_sch_disp;
725
726 IF l_schedule_disp = 'Y' THEN
727 -- Get the superior academic calendar instance
728 Igs_En_Gen_015.get_academic_cal
729 (
730 p_person_id => p_person_id,
731 p_course_cd => p_program_cd,
732 p_acad_cal_type => l_acad_cal_type,
733 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
734 p_message => l_message,
735 p_effective_dt => SYSDATE
736 );
737
738 -- Get the first term calendar associated with the academic calendar
739 OPEN c_first_term_acad(l_acad_cal_type,l_acad_ci_sequence_number);
740 FETCH c_first_term_acad INTO l_term_start_dt;
741 CLOSE c_first_term_acad;
742 ELSE
743
744 OPEN c_acad_cal(p_person_id, p_program_cd);
745 FETCH c_acad_cal INTO l_acad_cal_type;
746 CLOSE c_acad_cal;
747
748 igs_en_gen_015.get_curr_term_for_schedule (
749 p_acad_cal_type => l_acad_cal_type,
750 p_effective_dt => SYSDATE,
751 p_load_cal_type => l_load_cal_type,
752 p_load_ci_seq_num => l_load_ci_seq_num,
753 p_load_ci_alt_code => l_load_ci_alt_code,
754 p_load_ci_start_dt => l_load_ci_start_dt,
755 p_load_ci_end_dt => l_load_ci_end_dt,
756 p_message_name => l_message);
757
758 l_term_start_dt := l_load_ci_start_dt;
759 END IF;
760 RETURN l_term_start_dt;
761
765 RETURN NULL;
762 EXCEPTION
763 WHEN OTHERS THEN
764 -- supress any error message raised as this function is used in query
766
767 END get_Sch_disp_term_st_dt;
768
769
770 FUNCTION enrf_get_lookup_meaning(
771 p_lookup_code IN VARCHAR2,
772 p_lookup_type IN VARCHAR2)
773 RETURN VARCHAR2 AS
774 ------------------------------------------------------------------
775 --Created by : sarakshi, Oracle IDC
776 --Date created: 09-NOV-2004
777 --
778 --Purpose:This Function returns the lookup code meaning.
779 --
780 --Known limitations/enhancements and/or remarks:
781 --
782 --Change History:
783 --Who When What
784 --
785 -------------------------------------------------------------------
786 CURSOR cur_get_meaning IS
787 SELECT meaning
788 FROM igs_lookup_values
789 WHERE lookup_code = p_lookup_code
790 AND lookup_type = p_lookup_type;
791 l_c_meaning igs_lookup_values.meaning%TYPE;
792
793 BEGIN
794 OPEN cur_get_meaning;
795 FETCH cur_get_meaning INTO l_c_meaning;
796 CLOSE cur_get_meaning;
797 RETURN l_c_meaning;
798 END enrf_get_lookup_meaning;
799
800 FUNCTION enrf_get_sca_trans_ind(
801 p_person_id IN NUMBER ,
802 p_source_program_cd IN VARCHAR2,
803 p_dest_program_cd IN VARCHAR2)
804 RETURN VARCHAR2 AS
805 ------------------------------------------------------------------
806 --Created by : sarakshi, Oracle IDC
807 --Date created: 09-NOV-2004
808 --
809 --Purpose:The following function is used to determine if the destination program attempt has been selected and program transfer
810 -- submitted once. This is being determined by the presence of the program attempt transfer record in the table IGS_PS_STDNT_TRN
811 --Known limitations/enhancements and/or remarks:
812 --
813 --Change History:
814 --Who When What
815 --
816 -------------------------------------------------------------------
817 CURSOR cur_prg_trns IS
818 SELECT 'X'
819 FROM igs_ps_stdnt_trn
820 WHERE course_cd= p_dest_program_cd
821 AND transfer_course_cd = p_source_program_cd
822 AND person_id = p_person_id;
823 l_c_var VARCHAR2(1);
824
825 BEGIN
826 OPEN cur_prg_trns;
827 FETCH cur_prg_trns INTO l_c_var;
828 IF cur_prg_trns%FOUND THEN
829 CLOSE cur_prg_trns;
830 RETURN 'SCA-Y';
831 ELSE
832 CLOSE cur_prg_trns;
833 RETURN 'SCA-N';
834 END IF;
835 END enrf_get_sca_trans_ind;
836
837 FUNCTION enrf_get_sua_trans_ind(
838 p_person_id IN NUMBER ,
839 p_program_cd IN VARCHAR2,
840 p_uoo_id IN NUMBER )
841 RETURN VARCHAR2 AS
842 ------------------------------------------------------------------
843 --Created by : sarakshi, Oracle IDC
844 --Date created: 09-NOV-2004
845 --
846 --Purpose:The following function is used to determine if a unit section in the source program has been transfer to the destination
847 -- program attempt. This is done, by checking the existance of the program attempt record, since the transfer record is not created for all unit attempt status.
848 --Known limitations/enhancements and/or remarks:
849 --
850 --Change History:
851 --Who When What
852 --
853 -------------------------------------------------------------------
854 CURSOR cur_usec_trns IS
855 SELECT 'X'
856 FROM igs_en_su_attempt
857 WHERE person_id = p_person_id
858 AND course_cd = p_program_cd
859 AND uoo_id = p_uoo_id
860 AND unit_Attempt_status <> 'DROPPED';
861 l_c_var VARCHAR2(1);
862 BEGIN
863 OPEN cur_usec_trns;
864 FETCH cur_usec_trns INTO l_c_var;
865 IF cur_usec_trns%FOUND THEN
866 CLOSE cur_usec_trns;
867 RETURN 'SUA-Y';
868 ELSE
869 CLOSE cur_usec_trns;
870 RETURN 'SUA-N';
871 END IF;
872 END enrf_get_sua_trans_ind;
873
874 FUNCTION enrf_get_susa_trans_ind(
875 p_person_id IN NUMBER ,
876 p_program_cd IN VARCHAR2,
877 p_unit_set_cd IN VARCHAR2,
878 p_us_version_number IN NUMBER)
879 RETURN VARCHAR2 AS
880 ------------------------------------------------------------------
881 --Created by : sarakshi, Oracle IDC
882 --Date created: 09-NOV-2004
883 --
884 --Purpose:The following function is used to return the value, which specifies in the unit set attempt has been transferred, or not.
885 -- This is determined based on the existence of the same unit set attempt (that is present in the source program) against the destination program attempt
886 --Known limitations/enhancements and/or remarks:
887 --
888 --Change History:
889 --Who When What
890 --
891 -------------------------------------------------------------------
892 CURSOR cur_unitset_trns IS
893 SELECT 'X'
894 FROM igs_as_su_setatmpt
895 WHERE person_id = p_person_id
896 AND course_cd = p_program_cd
897 AND unit_set_cd = p_unit_set_cd
898 AND us_version_number = p_us_version_number;
899 l_c_var VARCHAR2(1);
900 BEGIN
901 OPEN cur_unitset_trns;
902 FETCH cur_unitset_trns INTO l_c_var;
903 IF cur_unitset_trns%FOUND THEN
904 CLOSE cur_unitset_trns;
905 RETURN 'SUSA-Y';
906 ELSE
907 CLOSE cur_unitset_trns;
908 RETURN 'SUSA-N';
909 END IF;
910 END enrf_get_susa_trans_ind;
911
912 FUNCTION get_dup_sua_src_prog(
913 p_person_id IN NUMBER,
914 p_course_cd IN VARCHAR2,
915 p_uoo_id IN NUMBER)
916 RETURN VARCHAR2 AS
917 ------------------------------------------------------------------
918 --Created by : sarakshi, Oracle IDC
922 --
919 --Date created: 09-NOV-2004
920 --
921 --Purpose:Procedure to get the source unit attempt for the duplicate unit attempt
923 --Known limitations/enhancements and/or remarks:
924 --
925 --Change History:
926 --Who When What
927 --
928 -------------------------------------------------------------------
929 CURSOR cur_src_unit_atmpt IS
930 SELECT sut.transfer_course_cd
931 FROM igs_ps_stdnt_unt_trn sut,
932 igs_en_su_attempt sua
933 WHERE sut.person_id = p_person_id
934 AND sua.person_id = sut.person_id
935 AND sut.uoo_id = p_uoo_id
936 AND sua.uoo_id = sut.uoo_id
937 AND sua.course_cd = sut.transfer_course_cd
938 AND sut.course_cd = p_course_cd
939 ORDER BY sut.transfer_dt desc;
940 l_c_transfer_course_cd igs_ps_stdnt_unt_trn.transfer_course_cd%TYPE;
941
942 BEGIN
943 OPEN cur_src_unit_atmpt;
944 FETCH cur_src_unit_atmpt INTO l_c_transfer_course_cd;
945 CLOSE cur_src_unit_atmpt;
946 RETURN l_c_transfer_course_cd;
947 END get_dup_sua_src_prog;
948
949
950 FUNCTION enrf_get_mark_grade(
951 p_person_id IN NUMBER,
952 p_course_cd IN VARCHAR2,
953 p_uoo_id IN NUMBER,
954 p_unit_attempt_Status IN VARCHAR2)
955 RETURN VARCHAR2 AS
956 ------------------------------------------------------------------
957 --Created by : sarakshi, Oracle IDC
958 --Date created: 09-NOV-2004
959 --
960 --Purpose:The following procedure is used to retrieve the marks and grades for a particular unit attempt
961 --
962 --Known limitations/enhancements and/or remarks:
963 --
964 --Change History:
965 --Who When What
966 --
967 -------------------------------------------------------------------
968 l_c_result_type VARCHAR2(1000);
969 l_d_outcome_dt DATE;
970 l_c_grading_schema_cd VARCHAR2(30);
971 l_n_gs_version_number NUMBER;
972 l_c_grade VARCHAR2(100);
973 l_n_mark NUMBER;
974 l_c_origin_course_cd VARCHAR2(30);
975 BEGIN
976 l_c_result_type := igs_as_gen_003.assp_get_sua_outcome(
977 p_person_id,
978 p_course_cd,
979 NULL,
980 NULL,
981 NULL,
982 p_unit_attempt_Status,
983 'Y', -- require finalised result
984 l_d_outcome_dt,
985 l_c_grading_schema_cd,
986 l_n_gs_version_number,
987 l_c_grade,
988 l_n_mark,
989 l_c_origin_course_cd,
990 p_uoo_id,
991 'N');
992
993 RETURN NVL(TO_CHAR(l_n_mark), l_c_grade);
994
995 END enrf_get_mark_grade;
996
997 FUNCTION enrf_get_cal_desc(
998 p_cal_type IN VARCHAR2,
999 p_sequence_number IN NUMBER)
1000 RETURN VARCHAR2 AS
1001 ------------------------------------------------------------------
1002 --Created by : sarakshi, Oracle IDC
1003 --Date created: 09-NOV-2004
1004 --
1005 --Purpose:The following function is used to determine the calendar description based on the calendar type and sequence number
1006 --
1007 --Known limitations/enhancements and/or remarks:
1008 --
1009 --Change History:
1010 --Who When What
1011 --
1012 -------------------------------------------------------------------
1013 CURSOR cur_cal_desc IS
1014 SELECT description
1015 FROM igs_ca_inst
1016 WHERE cal_type = p_cal_type
1017 AND sequence_number = p_sequence_number;
1018 l_c_description igs_ca_inst.description%TYPE;
1019 BEGIN
1020 OPEN cur_cal_desc;
1021 FETCH cur_cal_desc INTO l_c_description;
1022 CLOSE cur_cal_desc;
1023
1024 RETURN l_c_description;
1025
1026 END enrf_get_cal_desc;
1027
1028 FUNCTION enrf_get_acad_cal_desc(
1029 p_teach_cal_type IN VARCHAR2,
1030 p_teach_seqeunce_number IN NUMBER)
1031 RETURN VARCHAR2 AS
1032 ------------------------------------------------------------------
1033 --Created by : sarakshi, Oracle IDC
1034 --Date created: 09-NOV-2004
1035 --
1036 --Purpose:The following function is used to determine the acadaemic calendar's description based on a teaching calendar instance.
1037 --
1038 --Known limitations/enhancements and/or remarks:
1039 --
1040 --Change History:
1041 --Who When What
1042 --
1043 -------------------------------------------------------------------
1044 l_c_result VARCHAR2(1000);
1045 l_c_acad_cal_type igs_ca_inst.cal_type%TYPE;
1046 l_n_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1047 l_d_acad_ci_start_dt DATE;
1048 l_d_acad_ci_end_dt DATE;
1049 l_c_message_name fnd_new_messages.message_name%TYPE;
1050 BEGIN
1051 l_c_result := igs_en_gen_002.enrp_get_acad_alt_cd(
1052 p_teach_cal_type,
1053 p_teach_seqeunce_number,
1054 l_c_acad_cal_type,
1055 l_n_acad_ci_sequence_number,
1056 l_d_acad_ci_start_dt,
1057 l_d_acad_ci_end_dt,
1058 l_c_message_name);
1059
1060
1061 RETURN enrf_get_cal_desc(l_c_acad_cal_type, l_n_acad_ci_sequence_number);
1062
1063 END enrf_get_acad_cal_desc;
1064
1065 FUNCTION enrp_get_career_drop_dup(
1066 p_person_id in NUMBER ,
1067 p_program_cd in VARCHAR2,
1068 p_uoo_id in NUMBER)
1069 RETURN VARCHAR2 AS
1070 ------------------------------------------------------------------
1071 --Created by : sarakshi, Oracle IDC
1072 --Date created: 09-NOV-2004
1073 --
1074 --Purpose:The following fucntion is used to determine if a particular duplicate unit attempt can be dropped or not
1075 --
1076 --Known limitations/enhancements and/or remarks:
1077 --
1081 -------------------------------------------------------------------
1078 --Change History:
1079 --Who When What
1080 --
1082 CURSOR cur_src_prg IS
1083 SELECT transfer_course_cd
1084 FROM igs_ps_stdnt_unt_trn
1085 WHERE person_id = p_person_id
1086 AND course_cd = p_program_cd
1087 AND uoo_id = p_uoo_id;
1088 l_c_transfer_course_cd igs_ps_stdnt_unt_trn.transfer_course_cd%TYPE;
1089
1090 CURSOR cur_prg_atmpt(cp_program_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
1091 SELECT course_type
1092 FROM igs_en_stdnt_ps_att sca,
1093 igs_ps_ver cv
1094 WHERE sca.person_id = p_person_id
1095 AND sca.course_cd = cp_program_cd
1096 AND cv.course_cd = sca.course_cd
1097 AND cv.version_number = sca.version_number;
1098 l_c_src_course_type igs_ps_ver.course_type%TYPE;
1099 l_c_dest_course_type igs_ps_ver.course_type%TYPE;
1100
1101 BEGIN
1102 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') = 'N' THEN
1103 RETURN 'Y';
1104 END IF;
1105
1106 -- select the source program based on the passed in parameters
1107 OPEN cur_src_prg;
1108 FETCH cur_src_prg INTO l_c_transfer_course_cd;
1109 CLOSE cur_src_prg;
1110
1111 -- select the career/course type for the source program attempt
1112 OPEN cur_prg_atmpt(l_c_transfer_course_cd);
1113 FETCH cur_prg_atmpt INTO l_c_src_course_type;
1114 CLOSE cur_prg_atmpt;
1115
1116 -- select the career/course type for the destination program attempt
1117 OPEN cur_prg_atmpt(p_program_cd);
1118 FETCH cur_prg_atmpt INTO l_c_dest_course_type;
1119 CLOSE cur_prg_atmpt;
1120
1121 --In career-centric mode, for transfers across careers, users can drop a
1122 --duplicate in the destination program from the schedule.
1123 -- if the source and destination programs belong to different careers then return 'Y'
1124 IF l_c_src_course_type <> l_c_dest_course_type THEN
1125 RETURN 'Y';
1126 ELSE
1127 RETURN 'N';
1128 END IF;
1129
1130 RETURN 'Y';
1131
1132 END enrp_get_career_drop_dup;
1133
1134 end igs_ss_enroll_pkg ;