1 PACKAGE BODY igs_en_gen_legacy AS
2 /* $Header: IGSEN91B.pls 120.5 2006/04/25 23:37:19 stutta ship $ */
3
4 FUNCTION validate_grading_schm (
5 p_grade IN VARCHAR2 ,
6 p_uoo_id IN NUMBER ,
7 p_unit_cd IN VARCHAR2 ,
8 p_version_number IN NUMBER)
9 RETURN BOOLEAN AS
10 /*------------------------------------------------------------------
11 Created By : SVENKATA
12 Date Created By : 12-NOV-02
13 Purpose : The function validates Outcome Grading schema details.
14 Known limitations,
15 enhancements,
16 remarks :
17 Change History
18 Who When What
19 ------------------------------------------------------------------*/
20 --
21 -- cursor to find the default grading schema at Unit section level. Note that only one
22 -- grading schema can exist as a default grading schema for a Unit offering/ Unit version.
23 CURSOR ger_grd_schm_usec IS
24 SELECT grading_schema_code , grd_schm_version_number
25 FROM igs_ps_usec_grd_schm
26 WHERE uoo_id = p_uoo_id AND
27 default_flag = 'Y' ;
28
29 --
30 -- cursor to find the default grading schema at Unit version level.
31 CURSOR ger_grd_schm_unit IS
32 SELECT grading_schema_code , grd_schm_version_number
33 FROM igs_ps_unit_grd_schm
34 WHERE unit_code = p_unit_cd AND
35 unit_version_number = p_version_number AND
36 default_flag = 'Y' ;
37
38 --
39 --cursor to check if the given grade belongs to the default grading schema of the Unit Offering .
40 CURSOR check_grade ( p_outcome_grading_schema_code IN VARCHAR2 , p_outcome_gs_version_number IN NUMBER ) IS
41 SELECT 'x'
42 FROM igs_as_grd_sch_grade
43 WHERE grading_schema_cd = p_outcome_grading_schema_code AND
44 version_number = p_outcome_gs_version_number AND
45 grade = p_grade;
46
47 l_grading_schema_code igs_ps_usec_grd_schm.grading_schema_code%TYPE;
48 l_grd_schm_version_number igs_ps_usec_grd_schm.grd_schm_version_number%TYPE;
49 l_dummy VARCHAR2(2) := NULL;
50
51 BEGIN
52
53 OPEN ger_grd_schm_usec ;
54 FETCH ger_grd_schm_usec INTO l_grading_schema_code ,l_grd_schm_version_number ;
55
56 -- If default grading schema is found at Unit section level, proceed
57 IF ger_grd_schm_usec%FOUND THEN
58
59 CLOSE ger_grd_schm_usec;
60 OPEN check_grade(l_grading_schema_code ,l_grd_schm_version_number );
61 FETCH check_grade INTO l_dummy ;
62
63 IF check_grade%NOTFOUND THEN
64 CLOSE check_grade;
65 Fnd_Message.Set_Name( 'IGS' , 'IGS_AS_GRADE_INVALID');
66 FND_MSG_PUB.ADD;
67 RETURN FALSE ;
68 END IF;
69 CLOSE check_grade;
70
71 ELSE
72
73 -- Check if Grading schema exists at Unit level.
74 CLOSE ger_grd_schm_usec;
75 OPEN ger_grd_schm_unit ;
76 FETCH ger_grd_schm_unit INTO l_grading_schema_code ,l_grd_schm_version_number ;
77
78 IF ger_grd_schm_unit%FOUND THEN
79 CLOSE ger_grd_schm_unit ;
80 OPEN check_grade(l_grading_schema_code ,l_grd_schm_version_number );
81 FETCH check_grade INTO l_dummy ;
82
83 IF check_grade%NOTFOUND THEN
84 CLOSE check_grade;
85 Fnd_Message.Set_Name( 'IGS' , 'IGS_AS_GRADE_INVALID');
86 FND_MSG_PUB.ADD;
87 RETURN FALSE ;
88 END IF;
89 CLOSE check_grade;
90
91 ELSE
92 CLOSE ger_grd_schm_unit ;
93 -- If default grading schema is not found
94 Fnd_Message.Set_Name( 'IGS' , 'IGS_PS_ONE_UGSV_DFLT_MARK' );
95 FND_MSG_PUB.ADD;
96 RETURN FALSE ;
97 END IF ;
98 END IF ;
99
100 RETURN TRUE ;
101
102 END validate_grading_schm ;
103
104 FUNCTION validate_disc_rsn_cd (
105 p_discontinuation_reason_cd IN VARCHAR2
106 ) RETURN BOOLEAN AS
107 /*------------------------------------------------------------------
108 Created By : SVENKATA
109 Date Created By : 12-NOV-02
110 Purpose : The function checks if Discontinuation Reason Code exists for a Unit.
111 Known limitations,
112 enhancements,
113 remarks :
114 Change History
115 Who When What
116 ------------------------------------------------------------------*/
117 --
118 -- Cursor to to check if the Discontinuation Reason Code is valid, and can be used for discontinuing a Unit.
119 CURSOR val_discn_cd IS
120 SELECT dcnt_unit_ind
121 FROM igs_en_dcnt_reasoncd
122 WHERE discontinuation_reason_cd = p_discontinuation_reason_cd;
123
124 l_discn_ind igs_en_dcnt_reasoncd.dcnt_unit_ind%TYPE;
125
126 BEGIN
127 OPEN val_discn_cd ;
128 FETCH val_discn_cd INTO l_discn_ind ;
129 CLOSE val_discn_cd ;
130
131 IF NVL (l_discn_ind , 'N' ) = 'N' THEN
132 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_DISC_CD_INV');
133 FND_MSG_PUB.ADD;
134 RETURN FALSE ;
135 END IF ;
136 RETURN TRUE;
137
138 END validate_disc_rsn_cd ;
139
140 FUNCTION validate_trn_unit (
141 p_person_id IN NUMBER ,
142 p_program_cd IN VARCHAR2 ,
143 p_cal_type IN VARCHAR2 ,
144 p_ci_sequence_number IN NUMBER ,
145 p_unit_cd IN VARCHAR2 ,
146 p_location_cd IN VARCHAR2 ,
147 P_unit_class IN VARCHAR2 ,
148 p_unit_attempt_status OUT NOCOPY VARCHAR2
149 ) RETURN BOOLEAN AS
150 /*------------------------------------------------------------------
151 Created By : SVENKATA
152 Date Created By : 12-NOV-02
153 Purpose : The function validates Unit Transfer Details.
154 Known limitations,
155 enhancements,
156 remarks :
157 Change History
158 Who When What
159 ------------------------------------------------------------------*/
160 --
161 -- Cursor to find matching Units Attempts for Transfer.
162 CURSOR cnt_unit IS
163 SELECT COUNT(*)
164 FROM igs_en_su_attempt sua
165 WHERE sua.person_id = p_person_id AND
166 sua.course_cd = p_program_cd AND
167 sua.unit_cd = p_unit_cd AND
168 sua.cal_type = p_cal_type AND
169 sua.ci_sequence_number = p_ci_sequence_number AND
170 sua.location_cd = p_location_cd AND
171 sua.unit_class = p_unit_class;
172
173 --
174 -- Cursor to get the unit attempt status.
175 CURSOR val_status IS
176 SELECT sua.unit_attempt_status
177 FROM igs_en_su_attempt sua
178 WHERE sua.person_id = p_person_id AND
179 sua.course_cd= p_program_cd AND
180 sua.unit_cd = p_unit_cd AND
181 sua.cal_type = p_cal_type AND
182 sua.ci_sequence_number = p_ci_sequence_number AND
183 sua.location_cd = p_location_cd AND
184 sua.unit_class = p_unit_class ;
185
186 l_count NUMBER := 0;
187 l_unit_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE ;
188
189 BEGIN
190
191 -- Validate unit to be transferred
192 -- If any of the transfer columns are specified, then another unit attempt must exist with matching person id,
193 -- transfer program code, unit code, teaching alternate code (resolved to calendar type and sequence number),
194 -- location code and unit class.
195
196 OPEN cnt_unit ;
197 FETCH cnt_unit INTO l_count ;
198 CLOSE cnt_unit ;
199
200 IF l_count = 0 THEN
201 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_TRNSFR_UNT_NT_FND');
202 FND_MSG_PUB.ADD;
203 RETURN FALSE ;
204 ELSE
205 IF l_count > 1 THEN
206 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_MULTI_PRM_FND');
207 FND_MSG_PUB.ADD;
208 RETURN FALSE ;
209 ELSIF l_count =1 THEN
210
211 OPEN val_status ;
212 FETCH val_status INTO l_unit_attempt_status;
213 CLOSE val_status ;
214 IF l_unit_attempt_status NOT IN ( 'COMPLETED','DISCONTIN' ) THEN
215 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_TRN_SUA_STAT_INV');
216 FND_MSG_PUB.ADD;
217 RETURN FALSE ;
218 END IF ;
219 END IF ;
220 END IF;
221
222 p_unit_attempt_status := l_unit_attempt_status;
223 RETURN TRUE ;
224
225 END validate_trn_unit;
226
227
228 FUNCTION validate_transfer (
229 p_person_id IN NUMBER ,
230 p_transfer_program_cd IN VARCHAR2
231 ) RETURN BOOLEAN AS
232 /*------------------------------------------------------------------
233 Created By : SVENKATA
234 Date Created By : 12-NOV-02
235 Purpose : The function validates Transfer Details.
236 Known limitations,
237 enhancements,
238 remarks :
239 Change History
240 Who When What
241 ------------------------------------------------------------------*/
242 --
243 -- Cursor to get the type of discontinuation reason code of a program
244 CURSOR get_prgm_reasn IS
245 SELECT 'x'
246 FROM igs_en_stdnt_ps_att spa , igs_en_dcnt_reasoncd disc
247 WHERE spa.person_id = p_person_id AND
248 spa.course_cd = p_transfer_program_cd AND
249 disc.discontinuation_reason_cd = spa.discontinuation_reason_cd AND
250 disc.s_discontinuation_reason_type = 'TRANSFER';
251
252 --
253 -- Cursor to get the program attempt status of the Source Transfer Program
254 CURSOR get_prgm_stat IS
255 SELECT sca.course_attempt_status
256 FROM igs_en_stdnt_ps_att sca
257 WHERE sca.course_cd = p_transfer_program_cd
258 AND sca.person_id = p_person_id;
259
260 l_dummy VARCHAR2(1) := NULL;
261 l_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
262
263 BEGIN
264
265 OPEN get_prgm_stat ;
266 FETCH get_prgm_stat INTO l_course_attempt_status ;
267 CLOSE get_prgm_stat;
268 IF l_course_attempt_status = 'DISCONTIN' THEN
269
270 --
271 -- If the Source Program is Discontinued , then
272 -- Determine if the source Program has a discontinuation reason code of type 'TRANSFER'.
273 OPEN get_prgm_reasn ;
274 FETCH get_prgm_reasn INTO l_dummy;
275 IF get_prgm_reasn%NOTFOUND THEN
276 CLOSE get_prgm_reasn;
277 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_TRNSFR_CD_INV' );
278 FND_MSG_PUB.ADD;
279 RETURN FALSE ;
280 END IF ;
281 CLOSE get_prgm_reasn;
282
283 END IF;
284 RETURN TRUE;
285
286 END validate_transfer;
287
288 FUNCTION get_uoo_id (
289 p_cal_type IN VARCHAR2 ,
290 p_ci_sequence_number IN NUMBER ,
291 p_unit_cd IN VARCHAR2 ,
292 p_location_cd IN VARCHAR2 ,
293 P_unit_class IN VARCHAR2 ,
294 p_version_number IN NUMBER ,
295 p_uoo_id OUT NOCOPY NUMBER ,
296 p_owner_org_unit_cd OUT NOCOPY VARCHAR2
297 ) RETURN BOOLEAN AS
298 /*------------------------------------------------------------------
299 Created By : SVENKATA
300 Date Created By : 12-NOV-02
301 Purpose : The function derives the Unit Offering Option ID.
302 Known limitations,
303 enhancements,
304 remarks :
305 Change History
306 Who When What
307 ------------------------------------------------------------------*/
308 --
309 -- Cursor to derive uoo_id
310 CURSOR get_uoo_id IS
311 SELECT uoo_id , owner_org_unit_cd
312 FROM IGS_PS_UNIT_OFR_OPT
313 WHERE unit_cd = p_unit_cd AND
314 version_number = p_version_number AND
315 cal_type = p_cal_type AND
316 ci_sequence_number = p_ci_sequence_number AND
317 location_cd = p_location_cd AND
318 unit_class = p_unit_class;
319
320 l_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
321 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
322
323 BEGIN
324 -- Derive uoo_id
325 OPEN get_uoo_id ;
326 FETCH get_uoo_id INTO l_uoo_id , l_org_unit_cd;
327
328 IF get_uoo_id%NOTFOUND THEN
329 CLOSE get_uoo_id ;
330 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_UNIT_OFR_OPT_NT_FND');
331 FND_MSG_PUB.ADD;
332 RETURN FALSE ;
333 END IF;
334
335 CLOSE get_uoo_id ;
336 p_uoo_id := l_uoo_id ;
337 p_owner_org_unit_cd := l_org_unit_cd ;
338 RETURN TRUE ;
339
340 END get_uoo_id;
341
342 FUNCTION get_unit_ver (
343 p_cal_type IN VARCHAR2 ,
344 p_ci_sequence_number IN NUMBER ,
345 p_unit_cd IN VARCHAR2 ,
346 p_location_cd IN VARCHAR2 ,
347 P_unit_class IN VARCHAR2 ,
348 p_version_number OUT NOCOPY NUMBER
349 ) RETURN BOOLEAN AS
350 /*------------------------------------------------------------------
351 Created By : SVENKATA
352 Date Created By : 12-NOV-02
353 Purpose : The function derives the Unit version Number.
354 Known limitations,
355 enhancements,
356 remarks :
357 Change History
358 Who When What
359 ------------------------------------------------------------------*/
360 --
361 -- Cursor to derive Unit version Number
362 CURSOR get_unit_ver IS
363 SELECT version_number
364 FROM igs_ps_unit_ofr_opt
365 WHERE cal_type = p_cal_type AND
366 unit_cd = p_unit_cd AND
367 ci_sequence_number = p_ci_sequence_number AND
368 location_cd = p_location_cd AND
369 unit_class = p_unit_class;
370
371 --
372 -- Cursor to find count of matching Unit version Number(s)
373 CURSOR get_unit_ver_count IS
374 SELECT COUNT(*)
375 FROM igs_ps_unit_ofr_opt
376 WHERE cal_type = p_cal_type AND
377 unit_cd = p_unit_cd AND
378 ci_sequence_number = p_ci_sequence_number AND
379 location_cd = p_location_cd AND
380 unit_class = p_unit_class;
381
382 l_version_number igs_en_su_attempt.version_number%TYPE;
383 l_count NUMBER := 0;
384
385 BEGIN
386
387 OPEN get_unit_ver ;
388 FETCH get_unit_ver INTO l_version_number ;
389
390 -- If No matching Unit versions found for a Unit section , Error Out.
391 IF get_unit_ver%NOTFOUND THEN
392 CLOSE get_unit_ver ;
393 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_UNT_VER_NA');
394 FND_MSG_PUB.ADD;
395 RETURN FALSE ;
396 ELSE
397 CLOSE get_unit_ver ;
398 OPEN get_unit_ver_count ;
399 FETCH get_unit_ver_count INTO l_count ;
400 CLOSE get_unit_ver_count ;
401
402 -- If Multiple Unit versions found for a Unit section , Error Out.
403 IF l_count > 1 THEN
404 Fnd_Message.Set_Name( 'IGS' , 'IGS_EN_MUL_UNT_VER_EXTS');
405 FND_MSG_PUB.ADD;
406 RETURN FALSE ;
407 END IF;
408 END IF;
409 p_version_number := l_version_number;
410 RETURN TRUE;
411
412 END get_unit_ver;
413
414 FUNCTION validate_grad_sch_cd_ver (
415 p_uoo_id IN NUMBER ,
416 p_unit_cd IN VARCHAR2 ,
417 p_version_number IN NUMBER ,
418 p_grading_schema_code IN VARCHAR2 ,
419 p_gs_version_number IN NUMBER ,
420 P_message_name OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN
421 AS
422 /*------------------------------------------------------------------
423 Created By : SVENKATA
424 Date Created By : 12-NOV-02
425 Purpose : The function verifies if the Grading Schema Code/Version is valid within the
426 enrolling unit section/version. It queries for the Grading Schema Code/Version
427 first at the Unit section level , and then at the Unit level. If it is not found
428 at both the levels , it returns an error message.
429 Known limitations,
430 enhancements,
431 remarks :
432 Change History
433 Who When What
434 ------------------------------------------------------------------*/
435 --
436 -- cursor to check if the Grading Schema has been defined at the Unit section level
437 CURSOR val_usec IS
438 SELECT 'x'
439 FROM IGS_PS_USEC_GRD_SCHM
440 WHERE uoo_id = p_uoo_id AND
441 grading_schema_code = p_grading_schema_code AND
442 grd_schm_version_number = p_gs_version_number;
443
444 --
445 -- cursor to check if the Grading Schema has been defined at the Unit level
446 CURSOR val_unit IS
447 SELECT 'x'
448 FROM IGS_PS_UNIT_GRD_SCHM
449 WHERE unit_code = p_unit_cd AND
450 unit_version_number = p_version_number AND
451 grading_schema_code = p_grading_schema_code AND
452 grd_schm_version_number = p_gs_version_number;
453
454 l_dummy VARCHAR2(1) := NULL ;
455
456 BEGIN
457 p_message_name := NULL;
458 OPEN val_usec ;
459 FETCH val_usec INTO l_dummy ;
460
461 IF val_usec%NOTFOUND THEN
462
463 OPEN val_unit ;
464 FETCH val_unit INTO l_dummy ;
465 IF val_unit%NOTFOUND THEN
466
467 -- Grading Schema has not been defined at the Unit section / Unit level.
468 CLOSE val_usec ;
469 CLOSE val_unit;
470 p_message_name := 'IGS_EN_GRD_SCH_NT_EXTS';
471 RETURN FALSE;
472
473 END IF ;
474 CLOSE val_unit;
475
476 END IF ;
477
478 CLOSE val_usec ;
479 RETURN TRUE;
480
481 END validate_grad_sch_cd_ver;
482
483
484 FUNCTION validate_prgm_att_stat (
485 p_person_id IN NUMBER ,
486 p_course_cd IN VARCHAR2 ,
487 p_discontin_dt OUT NOCOPY DATE ,
488 p_program_type OUT NOCOPY VARCHAR2 ,
489 p_commencement_dt OUT NOCOPY DATE ,
490 p_version_number OUT NOCOPY NUMBER) RETURN VARCHAR2 AS
491 /*------------------------------------------------------------------
492 Created By : SVENKATA
493 Date Created By : 12-NOV-02
494 Purpose : The function Queries the Program Attempt details based on the person_id and course_cd.It does not
495 derive all possible statuses , but derives only the ones that are required for the calling routine.
496 If the Confirmed Indicator is not set for the Program , the program status is UNCONFIRM.
497 If the discontinued date is set , the program status is DISCONTINUED.
498 If the Program Attempt has a corresponding date of commencement , the date is also fetched
499 Known limitations,
500 enhancements,
501 remarks :
502 Change History
503 Who When What
504 ------------------------------------------------------------------*/
505 --
506 -- cursor to fetch SPA details
507 CURSOR get_prgm_stats IS
508 SELECT student_confirmed_ind , course_attempt_status , discontinued_dt , primary_program_type , commencement_dt , version_number
509 FROM igs_en_stdnt_ps_att
510 WHERE person_id = p_person_id AND
511 course_cd = p_course_cd ;
512
513 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
514 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
515
516 l_prgm_stats igs_en_stdnt_ps_att.course_attempt_status%TYPE;
517 l_confirmed_ind igs_en_stdnt_ps_att.student_confirmed_ind%TYPE;
518
519 BEGIN
520
521 OPEN get_prgm_stats ;
522 FETCH get_prgm_stats INTO l_confirmed_ind , l_prgm_stats, p_discontin_dt, p_program_type , p_commencement_dt, p_version_number;
523 CLOSE get_prgm_stats ;
524
525 IF l_confirmed_ind = 'N' THEN
526 RETURN cst_unconfirm;
527 END IF;
528
529 IF l_prgm_stats = 'DISCONTIN' then
530 RETURN cst_discontin;
531 END IF ;
532
533 RETURN l_prgm_stats ;
534
535 END validate_prgm_att_stat;
536
537 PROCEDURE get_last_dt_of_att (
538 x_person_id IN NUMBER,
539 x_course_cd IN VARCHAR2,
540 x_last_date_of_attendance OUT NOCOPY DATE ) AS
541 /*------------------------------------------------------------------
542 Created By : SVENKATA
543 Date Created By : 12-NOV-02
544 Purpose : This routine calculates the last date of attendance for a discontinued Unit Attempt.
545 Known limitations,
546 enhancements,
547 remarks :
548 Change History
549 Who When What
550 ------------------------------------------------------------------*/
551 CURSOR cur_unit_atmpt_dis IS
552 SELECT cal_type,ci_sequence_number,discontinued_dt
553 FROM IGS_EN_SU_ATTEMPT
554 WHERE person_id = x_person_id
555 AND course_cd = x_course_cd
556 AND discontinued_dt IS NOT NULL
557 ORDER BY discontinued_dt DESC;
558
559 CURSOR cur_term_cal(p_cal_type VARCHAR2,p_ci_sequence_number NUMBER, p_discontinued_dt DATE) IS
560 SELECT *
561 FROM IGS_CA_TEACH_TO_LOAD_V
562 WHERE teach_cal_type = p_cal_type
563 AND teach_ci_sequence_number = p_ci_sequence_number
564 AND load_start_dt <= TRUNC(p_discontinued_dt)
565 ORDER BY load_start_dt DESC;
566
567 CURSOR cur_unit_atmpt_grd IS
568 SELECT cal_type,ci_sequence_number
569 FROM IGS_EN_SU_ATTEMPT
570 WHERE person_id = x_person_id
571 AND course_cd = x_course_cd
572 AND unit_attempt_status='COMPLETED';
573
574
575 CURSOR cur_term_cal_grd(p_cal_type VARCHAR2,p_ci_sequence_number NUMBER) IS
576 SELECT *
577 FROM IGS_CA_TEACH_TO_LOAD_V
578 WHERE teach_cal_type = p_cal_type
579 AND teach_ci_sequence_number = p_ci_sequence_number
580 ORDER BY load_end_dt DESC;
581
582 lv_cal_type IGS_CA_TEACH_TO_LOAD_V.teach_cal_type%TYPE;
583 lv_ci_sequence_number IGS_CA_TEACH_TO_LOAD_V.teach_ci_sequence_number%TYPE;
584 lv_discontinued_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
585
586 cur_unit_atmpt_dis_rec cur_unit_atmpt_dis%ROWTYPE;
587 cur_term_cal_rec cur_term_cal%ROWTYPE;
588 cur_unit_atmpt_grd_rec cur_unit_atmpt_grd%ROWTYPE;
589 cur_term_cal_grd_rec cur_term_cal_grd%ROWTYPE;
590
591 BEGIN
592
593 OPEN cur_unit_atmpt_dis;
594 FETCH cur_unit_atmpt_dis INTO cur_unit_atmpt_dis_rec;
595
596 IF cur_unit_atmpt_dis%FOUND THEN
597
598 lv_cal_type := cur_unit_atmpt_dis_rec.cal_type;
599 lv_ci_sequence_number := cur_unit_atmpt_dis_rec.ci_sequence_number;
600 lv_discontinued_dt := cur_unit_atmpt_dis_rec.discontinued_dt;
601 CLOSE cur_unit_atmpt_dis;
602
603
604 OPEN cur_term_cal(lv_cal_type,lv_ci_sequence_number,lv_discontinued_dt);
605 FETCH cur_term_cal INTO cur_term_cal_rec;
606 IF (cur_term_cal%FOUND) THEN
607
608 x_last_date_of_attendance := lv_discontinued_dt;
609 CLOSE cur_term_cal;
610 ELSE
611 CLOSE cur_term_cal;
612 lv_discontinued_dt := NULL;
613
614 FOR cur_unit_atmpt_grd_rec IN cur_unit_atmpt_grd
615 LOOP
616
617 OPEN cur_term_cal_grd(cur_unit_atmpt_grd_rec.cal_type,cur_unit_atmpt_grd_rec.ci_sequence_number);
618 FETCH cur_term_cal_grd INTO cur_term_cal_grd_rec;
619
620 IF (cur_term_cal_grd%FOUND) THEN
621
622 IF lv_discontinued_dt IS NULL THEN
623 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
624 ELSIF lv_discontinued_dt < cur_term_cal_grd_rec.load_end_dt THEN
625 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
626 END IF;
627
628 END IF;
629 CLOSE cur_term_cal_grd;
630
631 END LOOP;
632
633 x_last_date_of_attendance := lv_discontinued_dt;
634 END IF;
635
636 ELSE
637
638 CLOSE cur_unit_atmpt_dis;
639 lv_discontinued_dt := NULL;
640
641 FOR cur_unit_atmpt_grd_rec IN cur_unit_atmpt_grd
642 LOOP
643
644 OPEN cur_term_cal_grd(cur_unit_atmpt_grd_rec.cal_type,cur_unit_atmpt_grd_rec.ci_sequence_number);
645 FETCH cur_term_cal_grd INTO cur_term_cal_grd_rec;
646
647 IF (cur_term_cal_grd%FOUND) THEN
648
649 IF lv_discontinued_dt IS NULL THEN
650 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
651 ELSIF lv_discontinued_dt < cur_term_cal_grd_rec.load_end_dt THEN
652 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
653 END IF;
654
655 END IF;
656
657 CLOSE cur_term_cal_grd;
658
659 END LOOP;
660
661 x_last_date_of_attendance := lv_discontinued_dt;
662
663
664 END IF;
665
666 END get_last_dt_of_att ;
667
668
669 FUNCTION get_coo_id(
670 p_course_cd IN igs_ps_ofr_opt.course_cd%TYPE,
671 p_version_number IN igs_ps_ofr_opt.version_number%TYPE,
672 p_cal_type IN igs_ps_ofr_opt.cal_type%TYPE,
673 p_location_cd IN igs_ps_ofr_opt.location_cd%TYPE,
674 p_attendance_mode IN igs_ps_ofr_opt.attendance_mode%TYPE,
675 p_attendance_type IN igs_ps_ofr_opt.attendance_type%TYPE)
676 RETURN igs_ps_ofr_opt.coo_id%TYPE AS
677 /*----------------------------------------------------------------------------
678 || Created By : kkillams
679 || Created On : 08-11-2002
680 || Purpose : Returns the coo_id for a program offering.
681 || Known limitations, enhancements or remarks :
682 || Change History :
683 || Who When What
684 ------------------------------------------------------------------------------*/
685 CURSOR cur_coo_id IS SELECT coo_id FROM igs_ps_ofr_opt
686 WHERE course_cd = p_course_cd
687 AND version_number = p_version_number
688 AND cal_type = p_cal_type
689 AND location_cd = p_location_cd
690 AND attendance_mode = p_attendance_mode
691 AND attendance_type = p_attendance_type;
692 l_coo_id igs_ps_ofr_opt.coo_id%TYPE;
693 BEGIN
694 --Return null if any of input parameters is null.
695 IF p_course_cd IS NULL OR
696 p_version_number IS NULL OR
697 p_cal_type IS NULL OR
698 p_location_cd IS NULL OR
699 p_attendance_mode IS NULL OR
700 p_attendance_type IS NULL THEN
701 RETURN NULL;
702 END IF;
703 --Get the coo_id for a program offering option.
704 OPEN cur_coo_id;
705 FETCH cur_coo_id INTO l_coo_id;
706 IF cur_coo_id%NOTFOUND THEN
707 CLOSE cur_coo_id;
708 RETURN NULL;
709 END IF;
710 CLOSE cur_coo_id;
711 RETURN l_coo_id;
712 EXCEPTION
713 WHEN OTHERS THEN
714 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
715 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.get_coo_id');
716 FND_MSG_PUB.ADD;
717 APP_EXCEPTION.RAISE_EXCEPTION;
718 END get_coo_id;
719
720
721
722 FUNCTION get_class_std_id(
723 p_class_standing IN igs_pr_class_std.class_standing%TYPE)
724 RETURN igs_pr_class_std.igs_pr_class_std_id%TYPE AS
725 /*----------------------------------------------------------------------------
726 || Created By : kkillams
727 || Created On : 08-11-2002
728 || Purpose : Returns the class standing identifier for a class standing.
729 || Known limitations, enhancements or remarks :
730 || Change History :
731 || Who When What
732 ------------------------------------------------------------------------------*/
733 CURSOR cur_class_std IS SELECT igs_pr_class_std_id FROM IGS_PR_CLASS_STD
734 WHERE class_standing = p_class_standing;
735 l_igs_pr_class_std_id igs_pr_class_std.igs_pr_class_std_id%TYPE;
736 BEGIN
737 --Return null if input parameters is null.
738 IF p_class_standing IS NULL THEN
739 RETURN NULL;
740 END IF;
741 --Get class identifier for the given class standing.
742 OPEN cur_class_std;
743 FETCH cur_class_std INTO l_igs_pr_class_std_id;
744 IF cur_class_std%NOTFOUND THEN
745 CLOSE cur_class_std;
746 RETURN NULL;
747 END IF;
748 CLOSE cur_class_std;
749 RETURN l_igs_pr_class_std_id;
750 EXCEPTION
751 WHEN OTHERS THEN
752 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
753 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.get_class_std_id');
754 FND_MSG_PUB.ADD;
755 APP_EXCEPTION.RAISE_EXCEPTION;
756 END get_class_std_id;
757
758 FUNCTION get_course_att_status(
759 p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
760 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
761 p_student_confirmed_ind IN igs_en_stdnt_ps_att.student_confirmed_ind%TYPE,
762 p_discontinued_dt IN igs_en_stdnt_ps_att.discontinued_dt%TYPE,
763 p_lapsed_dt IN igs_en_stdnt_ps_att.lapsed_dt%TYPE,
764 p_course_rqrmnt_complete_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE,
765 p_primary_pg_type IN igs_en_stdnt_ps_att.primary_program_type%TYPE,
766 p_primary_prog_type_source IN igs_en_stdnt_ps_att.primary_prog_type_source%TYPE,
767 p_course_type IN igs_ps_type.course_type%TYPE,
768 p_career_flag IN VARCHAR2)
769 RETURN igs_en_stdnt_ps_att.course_attempt_status%TYPE AS
770 /*----------------------------------------------------------------------------
771 || Created By : kkillams
772 || Created On : 08-11-2002
773 || Purpose :Derives the program attempt status.
774 || Known limitations, enhancements or remarks :
775 || Change History :
776 || Who When What
777 ------------------------------------------------------------------------------*/
778
779 l_course_att_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
780 l_career_course_att_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
781 BEGIN
782 --Get program attempt status by calling following function.
783 l_course_att_status :=igs_en_gen_006.enrp_get_sca_status(p_person_id,
784 p_course_cd,
785 'UNKNOWN',
786 p_student_confirmed_ind,
787 p_discontinued_dt,
788 p_lapsed_dt,
789 p_course_rqrmnt_complete_ind,
790 NULL);
791 l_career_course_att_status :=NULL;
792
793 --Return the same program attempt status if career model is not enabled or primary program type is PRIMARY
794 IF p_career_flag = 'N' OR
795 NVL(p_primary_pg_type,'PRIMARY') = 'PRIMARY' THEN
796 RETURN l_course_att_status;
797 ELSE
798 IF l_course_att_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT') THEN
799 l_career_course_att_status :=igs_en_career_model.enrp_get_sec_sca_status(p_person_id,
800 p_course_cd,
801 'UNKNOWN',
802 p_primary_pg_type,
803 p_primary_prog_type_source,
804 p_course_type,
805 NULL);
806 END IF;
807 RETURN NVL(l_career_course_att_status,l_course_att_status);
808 END IF;
809 EXCEPTION
810 WHEN OTHERS THEN
811 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
812 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.get_course_att_status');
813 FND_MSG_PUB.ADD;
814 APP_EXCEPTION.RAISE_EXCEPTION;
815 END get_course_att_status;
816
817
818 FUNCTION get_sca_dropped_by
819 RETURN igs_en_stdnt_ps_att.dropped_by%TYPE AS
820 /*----------------------------------------------------------------------------
821 || Created By : kkillams
822 || Created On : 08-11-2002
823 || Purpose : Function returns the active staff person type.
824 || Known limitations, enhancements or remarks :
825 || Change History :
826 || Who When What
827 ------------------------------------------------------------------------------*/
828 CURSOR cur_per_type IS SELECT person_type_code FROM igs_pe_person_types_v
829 WHERE system_type = 'STAFF'
830 AND closed_ind ='N';
831 l_person_type_code igs_en_stdnt_ps_att.dropped_by%TYPE;
832 BEGIN
833 l_person_type_code:= NULL;
834
835 --Get the active person type of STAFF.
836 OPEN cur_per_type;
837 FETCH cur_per_type INTO l_person_type_code;
838 CLOSE cur_per_type;
839 RETURN l_person_type_code;
840 EXCEPTION
841 WHEN OTHERS THEN
842 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
843 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.get_sca_dropped_by');
844 FND_MSG_PUB.ADD;
845 APP_EXCEPTION.RAISE_EXCEPTION;
846 END get_sca_dropped_by;
847
848
849 FUNCTION get_sca_prog_type(
850 p_course_cd IN igs_ps_ver.course_cd%TYPE,
851 p_version_number IN igs_ps_ver.version_number%TYPE)
852 RETURN igs_ps_ver.course_type%TYPE AS
853 /*----------------------------------------------------------------------------
854 || Created By : kkillams
855 || Created On : 08-11-2002
856 || Purpose : Derives the program type for the given program code and version number.
857 || Known limitations, enhancements or remarks :
858 || Change History :
859 || Who When What
860 ------------------------------------------------------------------------------*/
861 CURSOR cur_cour_typ IS SELECT course_type FROM IGS_PS_VER
862 WHERE course_cd = p_course_cd
863 AND version_number = p_version_number;
864 l_course_type igs_ps_ver.course_type%TYPE;
865 BEGIN
866 --Return null if any of input parameters is null.
867 IF p_course_cd IS NULL OR
868 p_version_number IS NULL THEN
869 RETURN NULL;
870 END IF;
871
872 --Derive the program type for a given program code and version number.
873 OPEN cur_cour_typ;
874 FETCH cur_cour_typ INTO l_course_type;
875 IF cur_cour_typ%NOTFOUND THEN
876 CLOSE cur_cour_typ;
877 RETURN NULL;
878 END IF;
879 CLOSE cur_cour_typ;
880 RETURN l_course_type;
881 EXCEPTION
882 WHEN OTHERS THEN
883 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
884 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.get_sca_prog_type');
885 FND_MSG_PUB.ADD;
886 APP_EXCEPTION.RAISE_EXCEPTION;
887 END get_sca_prog_type;
888
889
890 FUNCTION val_sca_start_dt (
891 p_student_confirmed_ind IN igs_en_stdnt_ps_att.student_confirmed_ind%TYPE,
892 p_commencement_dt IN igs_en_stdnt_ps_att.commencement_dt%TYPE)
893 RETURN BOOLEAN AS
894 /*----------------------------------------------------------------------------
895 || Created By : kkillams
896 || Created On : 08-11-2002
897 || Purpose : Validates the commencement date against confirmation indicator.
898 || Known limitations, enhancements or remarks :
899 || Change History :
900 || Who When What
901 ------------------------------------------------------------------------------*/
902 BEGIN
903 IF (p_student_confirmed_ind ='N' AND p_commencement_dt IS NOT NULL) OR
904 (p_student_confirmed_ind ='Y' AND p_commencement_dt IS NULL) OR
905 (p_student_confirmed_ind IS NULL AND p_commencement_dt IS NOT NULL)THEN
906 RETURN FALSE;
907 ELSE
908 RETURN TRUE;
909 END IF;
910 EXCEPTION
911 WHEN OTHERS THEN
912 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
913 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_start_dt');
914 FND_MSG_PUB.ADD;
915 APP_EXCEPTION.RAISE_EXCEPTION;
916 END val_sca_start_dt;
917
918
919 FUNCTION val_sca_disc_date(
920 p_discontinued_dt igs_en_stdnt_ps_att.discontinued_dt%TYPE)
921 RETURN BOOLEAN AS
922 /*----------------------------------------------------------------------------
923 || Created By : kkillams
924 || Created On : 08-11-2002
925 || Purpose : Validates the discontinue date.
926 || Known limitations, enhancements or remarks :
927 || Change History :
928 || Who When What
929 ------------------------------------------------------------------------------*/
930 BEGIN
931 --If discontinue date is greater than the sysdate then return false else return true.
932 IF (p_discontinued_dt > TRUNC(SYSDATE)) THEN
933 RETURN FALSE;
934 ELSE
935 RETURN TRUE;
936 END IF;
937 EXCEPTION
938 WHEN OTHERS THEN
939 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
940 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_disc_date');
941 FND_MSG_PUB.ADD;
942 APP_EXCEPTION.RAISE_EXCEPTION;
943 END val_sca_disc_date;
944
945
946 FUNCTION val_sca_reqcmpl_dt(
947 p_course_rqrmnt_comp_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE,
948 p_course_rqrmnts_comp_dt IN igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE,
949 p_message_name OUT NOCOPY VARCHAR2)
950 RETURN BOOLEAN AS
951 /*----------------------------------------------------------------------------
952 || Created By : kkillams
953 || Created On : 08-11-2002
954 || Purpose : Validate the requirement completion date against requirement completion indicator
955 || Known limitations, enhancements or remarks :
956 || Change History :
957 || Who When What
958 ------------------------------------------------------------------------------*/
959 BEGIN
960 IF p_course_rqrmnt_comp_ind ='Y' AND p_course_rqrmnts_comp_dt IS NULL THEN
961 p_message_name := 'IGS_EN_MST_RQRMNT_DT_CMP_FLAG';
962 RETURN FALSE;
963 ELSIF p_course_rqrmnt_comp_ind ='N' AND p_course_rqrmnts_comp_dt IS NOT NULL THEN
964 p_message_name := 'IGS_EN_RQRMNT_DT_NO_COMP_FLAG';
965 RETURN FALSE;
966 END IF;
967 RETURN TRUE;
968 EXCEPTION
969 WHEN OTHERS THEN
970 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
971 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_reqcmpl_dt');
972 FND_MSG_PUB.ADD;
973 APP_EXCEPTION.RAISE_EXCEPTION;
974 END val_sca_reqcmpl_dt;
975
976
977 FUNCTION val_sca_key_prg(
978 p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
979 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
980 p_key_program IN igs_en_stdnt_ps_att.key_program%TYPE,
981 p_primary_prg_type IN igs_en_stdnt_ps_att.primary_program_type%TYPE,
982 p_course_attempt_st IN igs_en_stdnt_ps_att.course_attempt_status%TYPE,
983 p_career_flag IN VARCHAR2)
984 RETURN BOOLEAN AS
985 /*-------------------------------------------------------------------------------------------
986 || Created By : kkillams
987 || Created On : 08-11-2002
988 || Purpose : Validates the key program to a student program attempts.
989 || Known limitations, enhancements or remarks :
990 || Change History :
991 || Who When What
992 || kkillams 13-12-2002 Removed code logic which checks key program
993 || should be a active program w.r.t. bug no : 2708522
994 || kkillams 27-12-2002 Bypassing the "minimum one program set as key program"
995 || validation if course attempt status is UNCONFIRM
996 || w.r.t. to bug 2721076
997 ----------------------------------------------------------------------------------------------*/
998
999 CURSOR cur_count IS SELECT count(*) FROM igs_en_stdnt_ps_att
1000 WHERE key_program = 'Y'
1001 AND person_id = p_person_id;
1002 l_error NUMBER :=0;
1003 l_key_count NUMBER :=0;
1004 BEGIN
1005 --Get the total key program count for a given person.
1006 OPEN cur_count;
1007 FETCH cur_count INTO l_key_count;
1008 CLOSE cur_count;
1009
1010 --If count is zero and current program is also not a key program then log the error message.
1011 IF l_key_count =0 AND p_key_program = 'N' AND (p_course_attempt_st <> 'UNCONFIRM') THEN
1012 l_error:=1;
1013 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SCA_NO_KEY_PROG');
1014 FND_MSG_PUB.ADD;
1015 --If count is one and current program is also a key program then log the error message.
1016 ELSIF l_key_count =1 AND p_key_program = 'Y' THEN
1017 l_error:=1;
1018 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_KEY_PROG');
1019 FND_MSG_PUB.ADD;
1020 --If count is greater than one then log the error message.
1021 ELSIF l_key_count > 1 THEN
1022 l_error:=1;
1023 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_KEY_PRO');
1024 FND_MSG_PUB.ADD;
1025 END IF;
1026 --In career model, If primary program type is not a primary and key program set to Y then log an error message.
1027 IF p_career_flag = 'Y' AND
1028 p_primary_prg_type <> 'PRIMARY' AND
1029 p_key_program ='Y' THEN
1030 l_error:=1;
1031 FND_MESSAGE.SET_NAME('IGS','IGS_EN_KEY_PROG_NOT_PRIMARY');
1032 FND_MSG_PUB.ADD;
1033 END IF;
1034 IF l_error = 1 THEN
1035 RETURN FALSE;
1036 ELSE
1037 RETURN TRUE;
1038 END IF;
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1042 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_key_prg');
1043 FND_MSG_PUB.ADD;
1044 APP_EXCEPTION.RAISE_EXCEPTION;
1045 END val_sca_key_prg;
1046
1047
1048 FUNCTION val_sca_primary_pg(
1049 p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
1050 p_primary_prog_type IN igs_en_stdnt_ps_att.primary_program_type%TYPE,
1051 P_course_type IN igs_ps_type.course_type%TYPE)
1052 RETURN BOOLEAN AS
1053 /*----------------------------------------------------------------------------
1054 || Created By : kkillams
1055 || Created On : 08-11-2002
1056 || Purpose : Validates the primary program for a student program attempt for
1057 || a career.
1058 || Known limitations, enhancements or remarks :
1059 || Change History :
1060 || Who When What
1061 ------------------------------------------------------------------------------*/
1062 CURSOR cur_count IS SELECT count(primary_program_type) FROM igs_en_stdnt_ps_att sca,
1063 igs_ps_ver crv
1064 WHERE crv.course_type = p_course_type
1065 AND sca.course_cd = crv.course_cd
1066 AND sca.version_number = crv.version_number
1067 AND sca.person_id = p_person_id
1068 AND sca.primary_program_type = 'PRIMARY';
1069 l_count NUMBER(3) := 0;
1070 BEGIN
1071 --In career model, primary program type is null then log an error message.
1072 IF p_primary_prog_type IS NULL THEN
1073 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRIMARY_PRG_MUST');
1074 FND_MSG_PUB.ADD;
1075 RETURN FALSE;
1076 END IF;
1077 --In career model, get total number of primary program for a given program type.
1078 OPEN cur_count;
1079 FETCH cur_count INTO l_count;
1080 CLOSE cur_count;
1081
1082 --If count is zero and current primary program type is not a primary then log error message.
1083 IF l_count = 0 AND p_primary_prog_type <> 'PRIMARY' THEN
1084 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SCA_STDNT_NO_PRIMARY');
1085 FND_MSG_PUB.ADD;
1086 RETURN FALSE;
1087 --If count is one and current primary program type is a primary then log error message.
1088 ELSIF l_count = 1 AND p_primary_prog_type = 'PRIMARY' THEN
1089 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STDNT_PS_MORE_PRIMARY');
1090 FND_MSG_PUB.ADD;
1091 RETURN FALSE;
1092 --If count is more than one then log error message.
1093 ELSIF l_count > 1 THEN
1094 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STDNT_PS_MORE_PRIMARY');
1095 FND_MSG_PUB.ADD;
1096 RETURN FALSE;
1097 ELSE
1098 RETURN TRUE;
1099 END IF;
1100 EXCEPTION
1101 WHEN OTHERS THEN
1102 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1103 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_primary_pg');
1104 FND_MSG_PUB.ADD;
1105 APP_EXCEPTION.RAISE_EXCEPTION;
1106 END val_sca_primary_pg;
1107
1108
1109 FUNCTION val_sca_comp_flag (
1110 p_course_attempt_status IN igs_en_stdnt_ps_att.course_attempt_status%TYPE,
1111 p_course_rqrmnt_complete_ind IN igs_en_stdnt_ps_att.course_rqrmnt_complete_ind%TYPE)
1112 RETURN BOOLEAN AS
1113 /*----------------------------------------------------------------------------
1114 || Created By : kkillams
1115 || Created On : 08-11-2002
1116 || Purpose : Validates the requiriment compelete indicator against course attempt status.
1117 || Known limitations, enhancements or remarks :
1118 || Change History :
1119 || Who When What
1120 ------------------------------------------------------------------------------*/
1121 BEGIN
1122 --If requirement completion indicator is set to Y and program attempt status
1123 --is UNCONFIRM then return false else return ture.
1124 IF p_course_rqrmnt_complete_ind = 'Y' AND
1125 p_course_attempt_status = 'UNCONFIRM' THEN
1126 RETURN FALSE;
1127 ELSE
1128 RETURN TRUE;
1129 END IF;
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1133 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_comp_flag');
1134 FND_MSG_PUB.ADD;
1135 APP_EXCEPTION.RAISE_EXCEPTION;
1136 END val_sca_comp_flag;
1137
1138 PROCEDURE raise_person_type_event(
1139 p_person_id igs_pe_typ_instances_all.person_id%TYPE,
1140 p_person_type_code igs_pe_typ_instances_all.person_type_code%TYPE,
1141 p_person_type_start_date igs_pe_typ_instances_all.start_date%TYPE,
1142 p_person_type_end_date igs_pe_typ_instances_all.end_date%TYPE,
1143 p_type_instance_id igs_pe_typ_instances_all.type_instance_id%TYPE,
1144 p_system_person_type VARCHAR2,
1145 p_action VARCHAR2
1146 )
1147 AS
1148 /**********************************************************************************************
1149 Created By : pkpatel
1150 Date Created By : 30-Sep-05
1151 Purpose : Raise the Business event to create the Responsibility mapped with the Person type
1152 Known limitations,enhancements,remarks:
1153 Change History
1154 Who When What
1155 ***********************************************************************************************/
1156
1157 l_prog_label CONSTANT VARCHAR2(100) := 'igs.plsql.igs_en_gen_legacy.raise_person_type_event';
1158 l_label VARCHAR2(500);
1159 l_debug_str VARCHAR2(3200);
1160
1161 CURSOR get_active_inst_cur(cp_person_id hz_parties.party_id%type,
1162 cp_system_type igs_pe_person_types.system_type%type ,
1163 cp_type_instance_id igs_pe_typ_instances_all.type_instance_id%TYPE) IS
1164 SELECT MAX(NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))) FROM igs_pe_typ_instances_all pti
1165 WHERE pti.person_id = cp_person_id
1166 AND pti.type_instance_id <> cp_type_instance_id
1167 AND SYSDATE BETWEEN pti.start_date and NVL(pti.end_date, SYSDATE)
1168 AND pti.person_type_code IN
1169 (select person_type_code from igs_pe_person_types pt where system_type =cp_system_type) ;
1170
1171 l_max_active_date DATE;
1172 BEGIN
1173
1174 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1175 l_label := 'igs.plsql.igs_en_gen_legacy.raise_person_type_event.'||p_action;
1176 l_debug_str := 'Person Type Code : '||p_person_type_code||'/'|| ' Person id : ' ||p_person_id ||'/'||
1177 ' Start Date :'||p_person_type_start_date ||'/'||' End Date :' ||p_person_type_end_date;
1178 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
1179 END IF;
1180
1181 IF p_action = 'INSERT' THEN
1182 -- End date is always passed as NULL, hence raise the Business event without any check
1183 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(
1184 p_person_id,
1185 p_person_type_code,
1186 p_action,
1187 p_person_type_end_date
1188 );
1189
1190 ELSIF p_action = 'UPDATE' THEN
1191 -- End date is always passed as TRUNC(SYSDATE). So if there is any other active record for the same person id type then no need to
1192 -- raise the business event.
1193 OPEN get_active_inst_cur(p_person_id, p_system_person_type, p_type_instance_id);
1194 FETCH get_active_inst_cur INTO l_max_active_date;
1195 CLOSE get_active_inst_cur;
1196
1197 IF l_max_active_date IS NULL OR l_max_active_date < p_person_type_end_date THEN
1198 igs_pe_gen_003.RAISE_PERSON_TYPE_EVENT(
1199 p_person_id,
1200 p_person_type_code,
1201 p_action,
1202 p_person_type_end_date
1203 );
1204
1205 END IF;
1206 END IF;
1207
1208 END raise_person_type_event;
1209
1210 FUNCTION val_sca_per_type(
1211 p_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1212 p_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
1213 p_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE)
1214 RETURN BOOLEAN AS
1215 /*----------------------------------------------------------------------------
1216 || Created By : kkillams
1217 || Created On : 08-11-2002
1218 || Purpose : Validates the person types.
1219 || Known limitations, enhancements or remarks :
1220 || Change History :
1221 || Who When What
1222 || pkpatel 30-Sep-2005 Bug 4627888 (Raised the Business event after the Insert/update of the person type)
1223 ------------------------------------------------------------------------------*/
1224 --Cursor get the person type instance for a person for a given person type.
1225 CURSOR cur_per_inst(p_person_type igs_pe_person_types.system_type%TYPE)
1226 IS SELECT pti.*
1227 FROM igs_pe_typ_instances_all pti,
1228 igs_pe_person_types pty
1229 WHERE pti.person_id = p_person_id
1230 AND pti.course_cd = p_course_cd
1231 AND pti.end_date IS NULL
1232 AND pty.person_type_code = pti.person_type_code
1233 AND pty.system_type = p_person_type;
1234 rec_per_inst cur_per_inst%ROWTYPE;
1235
1236 --Cursor get the person type for given system person type.
1237 CURSOR cur_per_type(p_system_type igs_pe_person_types.system_type%TYPE)
1238 IS SELECT person_type_code FROM igs_pe_person_types
1239 WHERE SYSTEM_TYPE = p_system_type
1240 AND CLOSED_IND = 'N';
1241
1242 CURSOR get_usr_id_cur(cp_person_id fnd_user.person_party_id%type) IS
1243 SELECT user_id
1244 FROM fnd_user
1245 WHERE person_party_id = cp_person_id;
1246
1247 CURSOR cur_pe_seq IS SELECT IGS_PE_TYPE_INSTANCES_S.NEXTVAL FROM DUAL;
1248
1249 l_method VARCHAR2(50) := 'PERSON_ENROL_UNIT_SECT';
1250 l_error NUMBER := 0;
1251 l_person_type_code igs_pe_person_types.person_type_code%TYPE;
1252 l_type_instance_id igs_pe_typ_instances.type_instance_id%TYPE;
1253 l_user_id fnd_user.user_id%TYPE;
1254 l_sysdate DATE := TRUNC(SYSDATE);
1255 BEGIN
1256 -- Person Type business event should be raised only if the person is associated with a User
1257 OPEN get_usr_id_cur(p_person_id);
1258 FETCH get_usr_id_cur INTO l_user_id;
1259 CLOSE get_usr_id_cur;
1260
1261 --Do following validation for the program attempt status is inactive and enrolled.
1262 IF p_course_attempt_status IN ('INACTIVE' ,'ENROLLED') THEN
1263 IF p_course_attempt_status = 'INACTIVE' THEN
1264 --Check whether person type instance of type APPLICANT is exist for the given person id
1265 -- if exist then update the end date with sysdate.
1266 OPEN cur_per_inst('APPLICANT');
1267 FETCH cur_per_inst INTO rec_per_inst;
1268 IF cur_per_inst%FOUND THEN
1269 UPDATE igs_pe_typ_instances_all
1270 SET end_date = l_sysdate ,
1271 end_method = l_method ,
1272 last_update_date = SYSDATE,
1273 last_updated_by = NVL(fnd_global.user_id,-1) ,
1274 last_update_login = NVL(fnd_global.login_id,-1)
1275 WHERE type_instance_id = rec_per_inst.type_instance_id;
1276
1277 IF l_user_id IS NOT NULL THEN
1278 raise_person_type_event(
1279 p_person_id => p_person_id,
1280 p_person_type_code => rec_per_inst.person_type_code,
1281 p_person_type_start_date => rec_per_inst.start_date,
1282 p_person_type_end_date => l_sysdate,
1283 p_type_instance_id => rec_per_inst.type_instance_id,
1284 p_system_person_type => 'APPLICANT',
1285 p_action => 'UPDATE'
1286 );
1287 END IF;
1288
1289 END IF;
1290 CLOSE cur_per_inst;
1291 END IF;
1292 l_person_type_code := NULL;
1293 OPEN cur_per_type('STUDENT');
1294 FETCH cur_per_type INTO l_person_type_code;
1295 IF cur_per_type%NOTFOUND THEN
1296 l_error := 1;
1297 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PERSON_TYPE_NOTFOUND');
1298 FND_MESSAGE.SET_TOKEN('TYPE','STUDENT');
1299 FND_MSG_PUB.ADD;
1300 ELSE
1301 --Check whether person type instance of type STUDENT is exist for the given person id
1302 --If not exist then create the person type instance of type STUDENT
1303 OPEN cur_per_inst('STUDENT');
1304 FETCH cur_per_inst INTO rec_per_inst;
1305 IF cur_per_inst%NOTFOUND THEN
1306
1307 OPEN cur_pe_seq;
1308 FETCH cur_pe_seq INTO l_type_instance_id;
1309 CLOSE cur_pe_seq;
1310
1311 INSERT INTO igs_pe_typ_instances_all(type_instance_id,
1312 person_type_code,
1313 person_id,
1314 course_cd,
1315 cc_version_number,
1316 funnel_status,
1317 admission_appl_number,
1318 nominated_course_cd,
1319 ncc_version_number,
1320 sequence_number,
1321 start_date,
1322 end_date,
1323 create_method,
1324 ended_by,
1325 end_method,
1326 created_by,
1327 creation_date,
1328 last_updated_by,
1329 last_update_date,
1330 last_update_login,
1331 org_id)VALUES(
1332 l_type_instance_id,
1333 l_person_type_code,
1334 p_person_id,
1335 p_course_cd,
1336 NULL,
1337 NULL,
1338 NULL,
1339 NULL,
1340 NULL,
1341 NULL,
1342 l_sysdate,
1343 NULL,
1344 l_method,
1345 NULL,
1346 NULL,
1347 NVL(fnd_global.user_id,-1),
1348 SYSDATE,
1349 NVL(fnd_global.user_id,-1),
1350 SYSDATE,
1351 NVL(fnd_global.login_id,-1),
1352 igs_ge_gen_003.get_org_id);
1353
1354 IF l_user_id IS NOT NULL THEN
1355 raise_person_type_event(
1356 p_person_id => p_person_id,
1357 p_person_type_code => l_person_type_code,
1358 p_person_type_start_date => l_sysdate,
1359 p_person_type_end_date => TO_DATE(null),
1360 p_type_instance_id => TO_NUMBER(null),
1361 p_system_person_type => 'STUDENT',
1362 p_action => 'INSERT'
1363 );
1364 END IF;
1365 END IF;
1366 CLOSE cur_per_inst;
1367 END IF;
1368 CLOSE cur_per_type;
1369 --Check whether person type instance of type FORMER_STUDENT is exist for the given person id
1370 --if exist then update the end date with sysdate.
1371 OPEN cur_per_inst('FORMER_STUDENT');
1372 FETCH cur_per_inst INTO rec_per_inst;
1373 IF cur_per_inst%FOUND THEN
1374 UPDATE igs_pe_typ_instances_all
1375 SET end_date = l_sysdate,
1376 end_method = l_method,
1377 last_update_date = SYSDATE ,
1378 last_updated_by = NVL(fnd_global.user_id,-1) ,
1379 last_update_login = NVL(fnd_global.login_id,-1)
1380 WHERE type_instance_id = rec_per_inst.type_instance_id;
1381
1382 IF l_user_id IS NOT NULL THEN
1383 raise_person_type_event(
1384 p_person_id => p_person_id,
1385 p_person_type_code => rec_per_inst.person_type_code,
1386 p_person_type_start_date => rec_per_inst.start_date,
1387 p_person_type_end_date => l_sysdate,
1388 p_type_instance_id => rec_per_inst.type_instance_id,
1389 p_system_person_type => 'FORMER_STUDENT',
1390 p_action => 'UPDATE'
1391 );
1392 END IF;
1393 END IF;
1394 CLOSE cur_per_inst;
1395 ELSIF p_course_attempt_status IN ('LAPSED','DISCONTIN' ,'COMPLETED') THEN
1396 --Check whether person type instance of type STUDENT is exist for the given person id
1397 --if exist then update the end date with sysdate.
1398 OPEN cur_per_inst('STUDENT');
1399 FETCH cur_per_inst INTO rec_per_inst;
1400 IF cur_per_inst%FOUND THEN
1401 UPDATE igs_pe_typ_instances_all
1402 SET end_date = l_sysdate,
1403 end_method = l_method ,
1404 last_update_date = SYSDATE ,
1405 last_updated_by = NVL(fnd_global.user_id,-1) ,
1406 last_update_login = NVL(fnd_global.login_id,-1)
1407 WHERE type_instance_id = rec_per_inst.type_instance_id;
1408
1409 IF l_user_id IS NOT NULL THEN
1410 raise_person_type_event(
1411 p_person_id => p_person_id,
1412 p_person_type_code => rec_per_inst.person_type_code,
1413 p_person_type_start_date => rec_per_inst.start_date,
1414 p_person_type_end_date => l_sysdate,
1415 p_type_instance_id => rec_per_inst.type_instance_id,
1416 p_system_person_type => 'STUDENT',
1417 p_action => 'UPDATE'
1418 );
1419 END IF;
1420 END IF;
1421 CLOSE cur_per_inst;
1422
1423 l_person_type_code := NULL;
1424 OPEN cur_per_type('FORMER_STUDENT');
1425 FETCH cur_per_type INTO l_person_type_code;
1426 IF cur_per_type%NOTFOUND THEN
1427 l_error := 1;
1428 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PERSON_TYPE_NOTFOUND');
1429 FND_MESSAGE.SET_TOKEN('TYPE','FORMER_STUDENT');
1430 FND_MSG_PUB.ADD;
1431 ELSE
1432 --Check whether person type instance of type FORMER_STUDENT is exist for the given person id
1433 -- If not exist then create the person type instance of type FORMER_STUDENT
1434 OPEN cur_per_inst('FORMER_STUDENT');
1435 FETCH cur_per_inst INTO rec_per_inst;
1436 IF cur_per_inst%NOTFOUND THEN
1437
1438 OPEN cur_pe_seq;
1439 FETCH cur_pe_seq INTO l_type_instance_id;
1440 CLOSE cur_pe_seq;
1441
1442 INSERT INTO igs_pe_typ_instances_all(type_instance_id,
1443 person_type_code,
1444 person_id,
1445 course_cd,
1446 cc_version_number,
1447 funnel_status,
1448 admission_appl_number,
1449 nominated_course_cd,
1450 ncc_version_number,
1451 sequence_number,
1452 start_date,
1453 end_date,
1454 create_method,
1455 ended_by,
1456 end_method,
1457 created_by,
1458 creation_date,
1459 last_updated_by,
1460 last_update_date,
1461 last_update_login,
1462 org_id)VALUES(
1463 l_type_instance_id,
1464 l_person_type_code,
1465 p_person_id,
1466 p_course_cd,
1467 NULL,
1468 NULL,
1469 NULL,
1470 NULL,
1471 NULL,
1472 NULL,
1473 l_sysdate,
1474 NULL,
1475 l_method,
1476 NULL,
1477 NULL,
1478 NVL(fnd_global.user_id,-1),
1479 SYSDATE,
1480 NVL(fnd_global.user_id,-1),
1481 SYSDATE,
1482 NVL(fnd_global.login_id,-1),
1483 igs_ge_gen_003.get_org_id);
1484
1485 IF l_user_id IS NOT NULL THEN
1486 raise_person_type_event(
1487 p_person_id => p_person_id,
1488 p_person_type_code => l_person_type_code,
1489 p_person_type_start_date => l_sysdate,
1490 p_person_type_end_date => TO_DATE(null),
1491 p_type_instance_id => TO_NUMBER(null),
1492 p_system_person_type => 'FORMER_STUDENT',
1493 p_action => 'INSERT'
1494 );
1495 END IF;
1496 END IF;
1497 CLOSE cur_per_inst;
1498 END IF;
1499 CLOSE cur_per_type;
1500 END IF;
1501 IF l_error = 1 THEN
1502 RETURN FALSE;
1503 ELSE
1504 RETURN TRUE;
1505 END IF;
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1509 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.val_sca_per_type');
1510 FND_MSG_PUB.ADD;
1511 APP_EXCEPTION.RAISE_EXCEPTION;
1512 END val_sca_per_type;
1513
1514
1515 FUNCTION check_pre_enroll_prof (p_unit_set_cd IN igs_as_su_setatmpt.unit_set_cd%TYPE,
1516 p_us_version_number IN igs_as_su_setatmpt.us_version_number%TYPE)
1517 RETURN BOOLEAN AS
1518 /*----------------------------------------------------------------------------
1519 || Created By : prraj
1520 || Created On : 21-Nov-2002
1521 || Purpose : Check the condition that if profile option is set, unit sets is of
1522 || category 'pre-enrollment year'
1523 || Known limitations, enhancements or remarks :
1524 || Change History :
1525 || Who When What
1526 ------------------------------------------------------------------------------*/
1527
1528 CURSOR c_prenrol_unitset IS
1529 SELECT
1530 'x'
1531 FROM
1532 igs_en_unit_set us,
1533 igs_en_unit_set_cat usc
1534 WHERE
1535 us.unit_set_cd = p_unit_set_cd
1536 AND us.version_number = p_us_version_number
1537 AND us.unit_set_cat = usc.unit_set_cat
1538 AND usc.s_unit_set_cat = 'PRENRL_YR';
1539
1540 l_dummy VARCHAR2(1);
1541 BEGIN
1542
1543 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'N' THEN
1544 OPEN c_prenrol_unitset;
1545 FETCH c_prenrol_unitset INTO l_dummy;
1546 IF c_prenrol_unitset%FOUND THEN
1547 CLOSE c_prenrol_unitset;
1548 RETURN FALSE;
1549 ELSE
1550 CLOSE c_prenrol_unitset;
1551 RETURN TRUE;
1552 END IF;
1553 ELSE
1554 RETURN TRUE;
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1560 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.check_pre_enroll_prof');
1561 FND_MSG_PUB.Add;
1562 App_Exception.Raise_Exception;
1563
1564 END check_pre_enroll_prof;
1565
1566
1567
1568 FUNCTION check_usa_overlap (p_person_id IN igs_as_su_setatmpt.person_id%TYPE,
1569 p_program_cd IN igs_as_su_setatmpt.course_cd%TYPE,
1570 p_selection_dt IN igs_as_su_setatmpt.selection_dt%TYPE,
1571 p_rqrmnts_complete_dt IN igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE,
1572 p_end_dt IN igs_as_su_setatmpt.end_dt%TYPE,
1573 p_sequence_number IN igs_as_su_setatmpt.sequence_number%TYPE,
1574 p_unit_set_cd IN igs_as_su_setatmpt.unit_set_cd%TYPE,
1575 p_us_version_number IN igs_as_su_setatmpt.us_version_number%TYPE,
1576 p_message_name OUT NOCOPY VARCHAR2)
1577 RETURN BOOLEAN AS
1578 /*----------------------------------------------------------------------------
1579 || Created By : prraj
1580 || Created On : 21-Nov-2002
1581 || Purpose : Check the condition that unit sets with category of 'pre-enrollment year'
1582 || cannot overlap selection/completion dates
1583 || Known limitations, enhancements or remarks :
1584 || Change History :
1585 || Who When What
1586 || bdeviset 29-JUL-2004 Added extra parameters p_end_dt,p_sequence_number to
1587 || function check_usa_overlap for Bug 3149133.
1588 || Modified cursor c_usa_ovrlp as unit sets with category of
1589 || 'pre-enrollment year' cannot overlap selection,completion
1590 || and end dates for 3149133
1591 || ckasu 28-OCT-2005 Added code to check whether passed unit_set_cd is PRENRL_YR
1592 || type or not.if so return false else continue
1593 || stutta 26-APR-2005 Modified c_sua_ovrlp to correct a join conditions bug5070647
1594 ------------------------------------------------------------------------------*/
1595
1596 CURSOR c_us_cat (cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE,
1597 cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE) IS
1598 SELECT usc.s_unit_set_cat
1599 FROM igs_en_unit_set us,
1600 igs_en_unit_set_cat usc
1601 WHERE us.unit_set_cd = cp_unit_set_cd
1602 AND us.version_number = cp_us_version_number
1603 AND us.unit_set_cat = usc.unit_set_cat;
1604
1605
1606 CURSOR c_usa_ovrlp IS
1607 SELECT
1608 'x'
1609 FROM
1610 igs_as_su_setatmpt asu,
1611 igs_en_unit_set us,
1612 igs_en_unit_set_cat usc
1613 WHERE
1614 asu.person_id = p_person_id
1615 AND asu.course_cd = p_program_cd
1616 AND asu.unit_set_cd = us.unit_set_cd
1617 AND asu.us_version_number = us.version_number
1618 AND us.unit_set_cat = usc.unit_set_cat
1619 AND usc.s_unit_set_cat = 'PRENRL_YR'
1620 AND ((asu.selection_dt BETWEEN p_selection_dt
1621 AND NVL (p_rqrmnts_complete_dt,NVL(p_end_dt,(TO_DATE('9999/12/31','YYYY/MM/DD')))))
1622 OR (p_selection_dt BETWEEN asu.selection_dt
1623 AND NVL (asu.rqrmnts_complete_dt,NVL(asu.end_dt,(TO_DATE('9999/12/31','YYYY/MM/DD'))))))
1624 AND ((p_sequence_number IS NULL) OR (asu.sequence_number <> p_sequence_number));
1625
1626 l_dummy VARCHAR2(1);
1627 l_s_unit_set_cat igs_en_unit_set_cat.s_unit_set_cat%TYPE;
1628 BEGIN
1629
1630 -- check if the passed in unit set is a pre-enrollment year unit set
1631 -- if the passed in unit set is not a pre-enr unit set then return true
1632
1633 l_s_unit_set_cat := NULL;
1634 OPEN c_us_cat (p_unit_set_cd, p_us_version_number);
1635 FETCH c_us_cat INTO l_s_unit_set_cat;
1636 IF l_s_unit_set_cat IS NOT NULL AND l_s_unit_set_cat <> 'PRENRL_YR' THEN
1637 CLOSE c_us_cat;
1638 RETURN TRUE;
1639 END IF;
1640 CLOSE c_us_cat;
1641
1642 OPEN c_usa_ovrlp;
1643 FETCH c_usa_ovrlp INTO l_dummy;
1644
1645 IF c_usa_ovrlp%FOUND THEN
1646 CLOSE c_usa_ovrlp;
1647 p_message_name := 'IGS_EN_ONLY_ONE_PRENRL_YR_US';
1648 RETURN FALSE;
1649 ELSE
1650 CLOSE c_usa_ovrlp;
1651 RETURN TRUE;
1652 END IF;
1653
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1657 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.check_usa_overlap');
1658 FND_MSG_PUB.Add;
1659 App_Exception.Raise_Exception;
1660
1661 END check_usa_overlap;
1662
1663
1664
1665 FUNCTION check_dup_susa (p_person_id IN igs_as_su_setatmpt.person_id%TYPE,
1666 p_program_cd IN igs_as_su_setatmpt.course_cd%TYPE,
1667 p_unit_set_cd IN igs_as_su_setatmpt.unit_set_cd%TYPE,
1668 p_us_version_number IN igs_as_su_setatmpt.us_version_number%TYPE,
1669 p_selection_dt IN igs_as_su_setatmpt.selection_dt%TYPE)
1670 RETURN BOOLEAN AS
1671
1672 /*----------------------------------------------------------------------------
1673 || Created By : prraj
1674 || Created On : 21-Nov-2002
1675 || Purpose : Check for duplicate student unit set attempt
1676 || Known limitations, enhancements or remarks :
1677 || Change History :
1678 || Who When What
1679 ------------------------------------------------------------------------------*/
1680 CURSOR c_dup_susa IS
1681 SELECT
1682 'x'
1683 FROM
1684 igs_as_su_setatmpt
1685 WHERE
1686 person_id = p_person_id
1687 AND course_cd = p_program_cd
1688 AND unit_set_cd = p_unit_set_cd
1689 AND us_version_number = p_us_version_number
1690 AND ((selection_dt IS NULL AND p_selection_dt IS NULL)
1691 OR selection_dt = p_selection_dt );
1692
1693 l_dummy VARCHAR2(1);
1694 BEGIN
1695
1696 OPEN c_dup_susa;
1697 FETCH c_dup_susa INTO l_dummy;
1698
1699 IF c_dup_susa%FOUND THEN
1700 CLOSE c_dup_susa;
1701 RETURN TRUE;
1702 ELSE
1703 CLOSE c_dup_susa;
1704 RETURN FALSE;
1705 END IF;
1706
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1710 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_gen_legacy.check_dup_susa');
1711 FND_MSG_PUB.Add;
1712 App_Exception.Raise_Exception;
1713
1714 END check_dup_susa;
1715
1716
1717 FUNCTION validate_intm_ua_ovrlp (
1718 p_person_id IN igs_en_stdnt_ps_intm.person_id%TYPE,
1719 p_program_cd IN igs_en_stdnt_ps_intm.course_cd%TYPE,
1720 p_start_dt IN igs_en_stdnt_ps_intm.start_dt%TYPE,
1721 p_end_dt IN igs_en_stdnt_ps_intm.end_dt%TYPE
1722 ) RETURN BOOLEAN AS
1723
1724 /**********************************************************************************************
1725 Created By : pradhakr
1726 Date Created By : 21-Nov-2002
1727 Purpose : This function checks whether intermission period overlaps enrolled/completed
1728 unit attempt teaching period census dates.
1729 Known limitations,enhancements,remarks:
1730 Change History
1731 Who When What
1732 ***********************************************************************************************/
1733
1734 CURSOR c_s_gen_cal_conf IS
1735 SELECT census_dt_alias
1736 FROM igs_ge_s_gen_cal_con
1737 WHERE s_control_num = 1;
1738
1739 -- Cursor to check whether intermission period overlaps enrolled/completed unit attempt
1740 -- teaching period census dates.
1741
1742 CURSOR c_intm_census_ovrlp(l_census_dt_alias igs_ge_s_gen_cal_con.census_dt_alias%TYPE) IS
1743 SELECT 'x'
1744 FROM igs_en_su_attempt sua,
1745 igs_ca_da_inst_v da
1746 WHERE sua.person_id = p_person_id
1747 AND sua.course_cd = p_program_cd
1748 AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED')
1749 AND sua.cal_type = da.cal_type
1750 AND sua.ci_sequence_number = da.ci_sequence_number
1751 AND da.dt_alias = l_census_dt_alias
1752 AND da.alias_val IS NOT NULL
1753 AND da.alias_val BETWEEN p_start_dt AND p_end_dt ;
1754
1755 l_interm_perd VARCHAR2(1);
1756 l_census_dt_alias igs_ge_s_gen_cal_con.census_dt_alias%TYPE;
1757
1758 BEGIN
1759
1760 OPEN c_s_gen_cal_conf;
1761 FETCH c_s_gen_cal_conf INTO l_census_dt_alias;
1762
1763 IF c_s_gen_cal_conf%FOUND THEN
1764
1765 OPEN c_intm_census_ovrlp (l_census_dt_alias);
1766 FETCH c_intm_census_ovrlp INTO l_interm_perd;
1767 CLOSE c_s_gen_cal_conf;
1768
1769 IF c_intm_census_ovrlp%FOUND THEN
1770 CLOSE c_intm_census_ovrlp;
1771 RETURN FALSE;
1772 ELSE
1773 CLOSE c_intm_census_ovrlp;
1774 RETURN TRUE;
1775 END IF;
1776 END IF;
1777
1778 IF c_s_gen_cal_conf%ISOPEN THEN
1779 CLOSE c_s_gen_cal_conf;
1780 END IF;
1781 RETURN TRUE;
1782
1783 END validate_intm_ua_ovrlp;
1784
1785
1786 FUNCTION check_approv_reqd (
1787 p_intermission_type IN igs_en_stdnt_ps_intm.intermission_type%TYPE
1788 ) RETURN BOOLEAN as
1789
1790 /**********************************************************************************************
1791 Created By : pradhakr
1792 Date Created By : 21-Nov-2002
1793 Purpose : This function is used to check whether approval is required for the
1794 intermission or not.
1795 Known limitations,enhancements,remarks:
1796 Change History
1797 Who When What
1798 ***********************************************************************************************/
1799
1800 CURSOR c_approv IS
1801 SELECT appr_reqd_ind
1802 FROM igs_en_intm_types
1803 WHERE intermission_type = p_intermission_type;
1804
1805 l_appr_reqd_ind igs_en_intm_types.appr_reqd_ind%TYPE;
1806
1807 BEGIN
1808 -- Cursor to check whether Approval is required for intermission or not.
1809 OPEN c_approv;
1810 FETCH c_approv INTO l_appr_reqd_ind;
1811 CLOSE c_approv;
1812
1813 IF l_appr_reqd_ind = 'Y' THEN
1814 RETURN TRUE;
1815 ELSE
1816 RETURN FALSE;
1817 END IF;
1818
1819 END check_approv_reqd;
1820
1821
1822
1823 FUNCTION check_study_antr_instu (
1824 p_intermission_type IN igs_en_stdnt_ps_intm.intermission_type%TYPE
1825 ) RETURN BOOLEAN as
1826
1827 /**********************************************************************************************
1828 Created By : pradhakr
1829 Date Created By : 14-Nov-02
1830 Purpose : This function will check whether srudent is studying at another
1831 institution or not.
1832 Known limitations,enhancements,remarks:
1833 Change History
1834 Who When What
1835 ***********************************************************************************************/
1836
1837 CURSOR c_study_antr_instu IS
1838 SELECT study_antr_inst_ind
1839 FROM igs_en_intm_types
1840 WHERE intermission_type = p_intermission_type;
1841
1842 l_study_antr_inst_ind igs_en_intm_types.study_antr_inst_ind%TYPE;
1843
1844 BEGIN
1845
1846 -- Check whether study at another institution is set or not.
1847 OPEN c_study_antr_instu;
1848 FETCH c_study_antr_instu INTO l_study_antr_inst_ind;
1849 CLOSE c_study_antr_instu;
1850
1851 IF l_study_antr_inst_ind = 'Y' THEN
1852 RETURN TRUE;
1853 ELSE
1854 RETURN FALSE;
1855 END IF;
1856
1857 END check_study_antr_instu;
1858
1859
1860
1861 FUNCTION check_institution (
1862 p_institution_name IN igs_en_stdnt_ps_intm.institution_name%TYPE
1863 ) RETURN BOOLEAN AS
1864
1865 /**********************************************************************************************
1866 Created By : pradhakr
1867 Date Created By : 21-Nov-2002
1868 Purpose : This function checks the validity of the institution. (i.e) it checks
1869 whether the specified institution is present or not.
1870 Known limitations,enhancements,remarks:
1871 Change History
1872 Who When What
1873 ***********************************************************************************************/
1874
1875 CURSOR c_inst IS
1876 SELECT 'x'
1877 FROM hz_parties hp, igs_pe_hz_parties ihp
1878 WHERE hp.party_id = ihp.party_id AND
1879 ihp.inst_org_ind = 'I' AND
1880 ihp.oi_govt_institution_cd is not null AND
1881 ihp.oss_org_unit_cd = p_institution_name ;
1882
1883 CURSOR c_lkups IS
1884 SELECT 'X'
1885 FROM igs_lookup_values lk
1886 WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS' AND
1887 lk.enabled_flag = 'Y' AND
1888 lk.lookup_code = p_institution_name ;
1889
1890
1891 l_inst_name VARCHAR2(1);
1892
1893 BEGIN
1894 -- Check whether the institution is present or not. If is is found then return true.
1895 OPEN c_inst;
1896 FETCH c_inst INTO l_inst_name;
1897
1898 IF c_inst%FOUND THEN
1899 CLOSE c_inst;
1900 RETURN TRUE;
1901 ELSE
1902 CLOSE c_inst;
1903 OPEN c_lkups;
1904 FETCH c_lkups INTO l_inst_name;
1905 IF c_lkups%FOUND THEN
1906 CLOSE c_lkups;
1907 RETURN TRUE;
1908 ELSE
1909 CLOSE c_lkups;
1910 RETURN FALSE;
1911 END IF;
1912
1913 END IF;
1914
1915 END check_institution;
1916
1917
1918 FUNCTION check_sca_status_upd (
1919 p_person_id IN igs_en_stdnt_ps_intm.person_id%TYPE,
1920 p_program_cd IN igs_en_stdnt_ps_intm.course_cd%TYPE,
1921 p_called_from IN VARCHAR2,
1922 p_course_attempt_status OUT NOCOPY igs_en_stdnt_ps_att.course_attempt_status%TYPE
1923 ) RETURN BOOLEAN as
1924 /**********************************************************************************************
1925 Created By : pradhakr
1926 Date Created By : 14-Nov-02
1927 Purpose : This function is used to check whether program attempt status needs to be
1928 updated or not.
1929 Known limitations,enhancements,remarks:
1930 Change History
1931 Who When What
1932 ***********************************************************************************************/
1933
1934 CURSOR c_sca IS
1935 SELECT sca.course_attempt_status,
1936 sca.student_confirmed_ind,
1937 sca.discontinued_dt,
1938 sca.lapsed_dt,
1939 sca.course_rqrmnt_complete_ind,
1940 sca.logical_delete_dt
1941 FROM igs_en_stdnt_ps_att sca
1942 WHERE sca.person_id = p_person_id
1943 AND sca.course_cd = p_program_cd;
1944
1945 l_sca_row c_sca%ROWTYPE;
1946 l_pred_sca_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
1947
1948 BEGIN
1949
1950 OPEN c_sca;
1951 FETCH c_sca INTO l_sca_row;
1952 CLOSE c_sca;
1953
1954 -- Get the program attempt status
1955 l_pred_sca_status := igs_en_gen_006.enrp_get_sca_status (
1956 p_person_id,
1957 p_program_cd,
1958 l_sca_row.course_attempt_status,
1959 l_sca_row.student_confirmed_ind,
1960 l_sca_row.discontinued_dt,
1961 l_sca_row.lapsed_dt,
1962 l_sca_row.course_rqrmnt_complete_ind,
1963 l_sca_row.logical_delete_dt
1964 );
1965
1966 p_course_attempt_status := l_pred_sca_status;
1967
1968 -- Call from Intermission API
1969 IF p_called_from = 'SPI' THEN
1970 IF (l_pred_sca_status = 'INTERMIT') AND (l_sca_row.course_attempt_status <> l_pred_sca_status) THEN
1971 RETURN TRUE;
1972 ELSE
1973 RETURN FALSE;
1974 END IF;
1975 -- Call from Student Unit Attempt API
1976 ELSIF p_called_from = 'SUA' THEN
1977 IF l_sca_row.course_attempt_status <> l_pred_sca_status THEN
1978 RETURN TRUE;
1979 ELSE
1980 RETURN FALSE;
1981 END IF;
1982 END IF;
1983
1984 RETURN FALSE;
1985
1986 END check_sca_status_upd;
1987
1988 FUNCTION validate_awd_offer_pgm(
1989 p_person_id IN NUMBER,
1990 p_program_cd IN VARCHAR2,
1991 p_award_cd IN VARCHAR2)
1992 RETURN BOOLEAN AS
1993 /*
1994 || Created By : nbehera
1995 || Created On : 22-NOV-2002
1996 || Purpose : This function will check whether the award code is
1997 || offered within the enrolled program version.
1998 || Known limitations, enhancements or remarks :
1999 || Change History :
2000 || Who When What
2001 || smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_awd_prg_ver to pick up open
2002 || Program Awards only. As mentioned in TD.
2003 || (reverse chronological order - newest change first)
2004 */
2005 CURSOR c_awd_prg_ver IS
2006 SELECT 'X'
2007 FROM igs_en_stdnt_ps_att spa,
2008 igs_ps_award psa
2009 WHERE spa.person_id = p_person_id
2010 AND spa.course_cd = p_program_cd
2011 AND psa.award_cd = p_award_cd
2012 AND spa.course_cd = psa.course_cd
2013 AND spa.version_number = psa.version_number
2014 AND psa.closed_ind = 'N';
2015 l_dummy VARCHAR2(1);
2016
2017 BEGIN
2018 OPEN c_awd_prg_ver;
2019 FETCH c_awd_prg_ver INTO l_dummy;
2020
2021 IF c_awd_prg_ver%FOUND THEN
2022 CLOSE c_awd_prg_ver;
2023 RETURN TRUE;
2024 ELSE
2025 CLOSE c_awd_prg_ver;
2026 RETURN FALSE;
2027 END IF;
2028 END validate_awd_offer_pgm;
2029
2030 END igs_en_gen_legacy;
[Home] [Help]