1 PACKAGE BODY IGS_RE_VAL_CA AS
2 /* $Header: IGSRE04B.pls 120.0 2005/06/01 21:14:09 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 29-AUG-2001 Bug No. 1956374 .The function genp_val_sdtt_sess removed
7 --kkillams 11-11-2002 As part of Legacy Build bug no:2661533,
8 -- New parameter p_legacy is added to following functions
9 -- resp_val_ca_minsbmsn,resp_val_ca_maxsbmsn and resp_val_ca_topic
10 -------------------------------------------------------------------------------------------
11 --
12 /***** Bug No : 1956374
13 Task : Duplicated Procedures and functions
14 PROCEDURE admp_val_ca_comm is removed and reference is changed
15 || Removed program unit (RESP_VAL_CA_ATT_PERC) - from the spec and body. -- kdande
16 PROCEDURE admp_val_ca_comm_val is removed and reference is changed *****/
17
18 -- Validate adm IGS_PS_COURSE application proposed commencement date.
19
20 FUNCTION admp_val_acai_comm(
21
22 p_person_id IN NUMBER ,
23
24 p_course_cd IN VARCHAR2 ,
25
26 p_crv_version_number IN NUMBER ,
27
28 p_acai_admission_appl_number IN NUMBER ,
29
30 p_acai_nominated_course_cd IN VARCHAR2 ,
31
32 p_acai_sequence_number IN NUMBER ,
33
34 p_adm_cal_type IN VARCHAR2 ,
35
36 p_adm_ci_sequence_number IN NUMBER ,
37
38 p_adm_outcome_status IN VARCHAR2 ,
39
40 p_prpsd_commencement_dt IN DATE ,
41
42 p_min_submission_dt IN DATE ,
43
44 p_ca_sequence_number IN OUT NOCOPY NUMBER ,
45
46 p_parent IN VARCHAR2 ,
47
48 p_message_name OUT NOCOPY VARCHAR2 )
49
50 RETURN BOOLEAN AS
51
52 BEGIN -- admp_val_acai_comm
53
54 -- This modules validates IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
55
56 -- Validations are:
57
58 -- * IGS_RE_CANDIDATURE must exist for the IGS_AD_PS_APPL_INST.
59
60 -- * Prpsd_commencement_dt must be greater than the earlier of the IGS_PS_COURSE
61
62 -- start date or the admission academic period earliest research start date.
63
64 -- * Warn if the Prpsd_commencement_dt is prior to passed census dates for
65
66 -- the admission academic period.
67
68 DECLARE
69
70 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
71
72 v_candidature_exists_ind VARCHAR(1);
73
74 v_message_name VARCHAR2(30);
75
76 BEGIN
77
78 -- Set the default message number
79
80 p_message_name := NULL;
81
82 v_candidature_exists_ind := NULL;
83
84 v_ca_sequence_number := p_ca_sequence_number;
85
86 IF p_prpsd_commencement_dt IS NOT NULL THEN
87
88 -- Validate research IGS_RE_CANDIDATURE details and commencemnt
89
90 IF IGS_EN_VAL_SCA.admp_val_ca_comm(
91
92 p_person_id,
93
94 p_course_cd,
95
96 p_crv_version_number,
97
98 p_acai_admission_appl_number,
99
100 p_acai_nominated_course_cd,
101
102 p_acai_sequence_number,
103
104 p_adm_outcome_status,
105
106 p_prpsd_commencement_dt,
107
108 p_min_submission_dt,
109
110 p_parent,
111
112 v_ca_sequence_number,
113
114 v_candidature_exists_ind,
115
116 v_message_name) = FALSE THEN
117
118 p_message_name := v_message_name;
119
120 RETURN FALSE;
121
122 ELSE
123
124 IF v_candidature_exists_ind = 'N' THEN
125
126 RETURN TRUE;
127
128 END IF;
129
130 END IF;
131
132 --Validate commencement date value
133
134 IF IGS_EN_VAL_SCA.admp_val_ca_comm_val(
135
136 p_person_id,
137
138 p_acai_admission_appl_number,
139
140 p_acai_nominated_course_cd,
141
142 p_acai_sequence_number,
143
144 p_adm_cal_type,
145
146 p_adm_ci_sequence_number,
147
148 NULL, -- (IGS_PS_COURSE start date)
149
150 p_prpsd_commencement_dt,
151
152 p_parent,
153
154 v_message_name) = FALSE THEN
155
156 p_message_name := v_message_name;
157
158 RETURN FALSE;
159
160 ELSE
161
162 IF v_message_name IS NOT NULL THEN
163
164 p_message_name := v_message_name;
165
166 RETURN TRUE;
167
168 END IF;
169
170 END IF;
171
172 END IF;-- p_commencement_dt
173
174 -- Return the default value
175
176 RETURN TRUE;
177
178 END;
179
180 EXCEPTION
181
182 WHEN OTHERS THEN
183 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186
187 END admp_val_acai_comm;
188
189 --
190
191 -- Validate IGS_RE_CANDIDATURE proposed commencement date value.
192
193 --
194
195 -- Validate IGS_RE_CANDIDATURE proposed commencement date.
196
197
198 -- Validate IGS_RE_CANDIDATURE update.
199
200 FUNCTION resp_val_ca_upd(
201
202 p_person_id IN NUMBER ,
203
204 p_sca_course_cd IN VARCHAR2 ,
205
206 p_message_name OUT NOCOPY VARCHAR2 )
207
208 RETURN BOOLEAN AS
209
210 -- Change History :
211 -- Who When What
212 -- stutta 05-May-2004 Added c_awd_exists,c_incomp_awd cursors and modified logic to return false
213 -- only if a completed program attempt has all its awards completed.
214 -- If atleast one award is incomplete or no award is associated, return true and
215 -- a warning message. (Bug #3577988)
216
217 BEGIN -- resp_val_ca_upd
218
219 -- This module validates the update of IGS_RE_CANDIDATURE details. Validations are:
220
221 -- IGS_RE_CANDIDATURE cannot be updated if
222
223 -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all its awards completed.
224
225 -- If atleast one award is incomplete or no award is associated, update is allowed but a
226
227 -- message is returned.
228
229
230 DECLARE
231
232 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
233
234 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
235 v_dummy VARCHAR2(10);
236 CURSOR c_sca IS
237
238 SELECT sca.course_attempt_status
239
240 FROM IGS_EN_STDNT_PS_ATT sca
241
242 WHERE sca.person_id = p_person_id AND
243
244 sca.course_cd = p_sca_course_cd;
245 CURSOR c_awd_exists IS
246 SELECT 'x'
247 FROM igs_en_spa_awd_aim
248 WHERE person_id = p_person_id
249 AND course_cd = p_sca_course_cd
250 AND ( end_dt IS NULL OR
251 (end_dt IS NOT NULL AND complete_ind = 'Y')
252 );
253 CURSOR c_incomp_awd IS
254 SELECT 'x'
255 FROM igs_en_spa_awd_aim
256 WHERE person_id = p_person_id
257 AND course_cd = p_sca_course_cd
258 AND NVL(complete_ind,'N') = 'N'
259 AND end_dt IS NULL;
260
261 BEGIN
262
263 p_message_name := NULL;
264
265 IF p_sca_course_cd IS NOT NULL THEN
266
267 OPEN c_sca;
268
269 FETCH c_sca INTO v_course_attempt_status;
270
271 IF c_sca%NOTFOUND THEN
272
273 CLOSE c_sca;
274
275 --Invalid parameters, handled elsewhere
276
277 RETURN TRUE;
278
279 END IF;
280
281 CLOSE c_sca;
282
283 IF v_course_attempt_status = cst_completed THEN
284 OPEN c_awd_exists;
285 FETCH c_awd_exists INTO v_dummy;
286 IF c_awd_exists%FOUND THEN
287 OPEN c_incomp_awd;
288 FETCH c_incomp_awd INTO v_dummy;
289 IF c_incomp_awd%FOUND THEN
290 p_message_name := 'IGS_RE_STDNT_PRG_ATT_COMP';
291 CLOSE c_awd_exists;
292 CLOSE c_incomp_awd;
293 RETURN TRUE;
294 ELSE
295 p_message_name :='IGS_RE_CANT_UPD_DET_WHEN_COUR';
296 CLOSE c_awd_exists;
297 CLOSE c_incomp_awd;
298 RETURN FALSE;
299 END IF;
300 ELSE
301 p_message_name := 'IGS_RE_STDNT_PRG_ATT_COMP';
302 CLOSE c_awd_exists;
303 RETURN TRUE;
304 END IF;
305 END IF;
306
307 END IF;
308
309 RETURN TRUE;
310
311 EXCEPTION
312
313 WHEN OTHERS THEN
314
315 IF (c_sca%ISOPEN) THEN
316
317 CLOSE c_sca;
318
319 END IF;
320
321 RAISE;
322
323 END;
324
325 EXCEPTION
326
327 WHEN OTHERS THEN
328 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END resp_val_ca_upd;
332
333 --
334
335 FUNCTION resp_val_ca_topic(
336 p_person_id IN NUMBER ,
337 p_sca_course_cd IN VARCHAR2 ,
338 p_acai_admission_appl_number IN NUMBER ,
339 p_acai_nominated_course_cd IN VARCHAR2 ,
340 p_acai_sequence_number IN NUMBER ,
341 p_research_topic IN VARCHAR2 ,
342 p_message_name OUT NOCOPY VARCHAR2 ,
343 p_legacy IN VARCHAR2 )
344 RETURN BOOLEAN AS
345 /*----------------------------------------------------------------------------
346 || Created By :
347 || Created On :
348 || Purpose : This module validates IGS_RE_CANDIDATURE.research_topic.
349 ||
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
354 || if p_legacy set to 'Y' then error message should be stacked instead of
355 || returning the function in the normal way else function should behave in
356 || normal way.Legacy Build Bug no: 2661533
357 ------------------------------------------------------------------------------*/
358 BEGIN -- resp_val_ca_topic
359 DECLARE
360 v_test VARCHAR2(1);
361 v_message_name VARCHAR2(30);
362 v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
363 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
364 cst_offer CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'OFFER';
365 cst_condoffer CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'COND-OFFER';
366 CURSOR c_sca IS
367 SELECT sca.student_confirmed_ind
368 FROM IGS_EN_STDNT_PS_ATT sca
369 WHERE sca.person_id = p_person_id AND
370 sca.course_cd = p_sca_course_cd;
371 CURSOR c_aos IS
372 SELECT aos.s_adm_outcome_status
373 FROM IGS_AD_PS_APPL_INST acai,
374 IGS_AD_OU_STAT aos
375 WHERE acai.person_id = p_person_id AND
376 acai.admission_appl_number = p_acai_admission_appl_number AND
377 acai.nominated_course_cd = p_acai_nominated_course_cd AND
378 acai.sequence_number = p_acai_sequence_number AND
379 acai.adm_outcome_status = aos.adm_outcome_status;
380 BEGIN
381 -- Setup the default message number value.
382 p_message_name := null;
383 IF p_research_topic IS NULL THEN
384 -- Validate against student IGS_PS_COURSE attempt
385 IF p_sca_course_cd IS NOT NULL THEN
386 OPEN c_sca;
387 FETCH c_sca INTO v_student_confirmed_ind;
388 -- Invalid parameters, handled elsewhere
389 IF c_sca%NOTFOUND THEN
390 CLOSE c_sca;
391 RETURN TRUE;
392 END IF;
393 CLOSE c_sca;
394 IF v_student_confirmed_ind = 'Y' THEN
395 p_message_name := 'IGS_RE_CAN_TOPIC_REQ_CRS_ATMP';
396 IF p_legacy ='Y' THEN
397 fnd_message.set_name('IGS',p_message_name);
398 fnd_msg_pub.add;
399 END IF;
400 RETURN FALSE;
401 END IF;
402 END IF;
403 IF p_legacy <> 'Y' THEN
404 --Validate against admission IGS_PS_COURSE application
405 IF p_acai_admission_appl_number IS NOT NULL THEN
406 OPEN c_aos;
407 FETCH c_aos INTO v_s_adm_outcome_status;
408 -- Invalid parameters, handled elsewhere
409 IF c_aos%NOTFOUND THEN
410 CLOSE c_aos;
411 RETURN TRUE;
412 END IF;
413 CLOSE c_aos;
414 IF v_s_adm_outcome_status IN (cst_offer,
415 cst_condoffer) THEN
416 p_message_name := 'IGS_RE_CAN_TOPIC_REQ_AMD';
417 RETURN FALSE;
418 END IF;
419 END IF;
420 END IF; --p_legacy <> 'Y'
421 END IF;
422 RETURN TRUE;
423 EXCEPTION
424 WHEN OTHERS THEN
425 IF c_sca%ISOPEN THEN
426 CLOSE c_sca;
427 END IF;
428 IF c_aos%ISOPEN THEN
429 CLOSE c_aos;
430 END IF;
431 RAISE;
432 END;
433 EXCEPTION
434 WHEN OTHERS THEN
435 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 END resp_val_ca_topic;
439
440 --
441 -- Validate IGS_RE_CANDIDATURE ACAI link.
442 FUNCTION resp_val_ca_acai(
443
444 p_person_id IN NUMBER ,
445
446 p_ca_sequence_number IN NUMBER ,
447
448 p_sca_course_cd IN VARCHAR2 ,
449
450 p_old_acai_admission_appl_num IN NUMBER ,
451
452 p_old_acai_nominated_course_cd IN VARCHAR2 ,
453
454 p_old_acai_sequence_number IN NUMBER ,
455
456 p_acai_admission_appl_number IN NUMBER ,
457
458 p_acai_nominated_course_cd IN VARCHAR2 ,
459
460 p_acai_sequence_number IN NUMBER ,
461
462 p_message_name OUT NOCOPY VARCHAR2 )
463
464 RETURN BOOLEAN AS
465
466 BEGIN -- resp_val_ca_acai
467
468 -- This module validates the IGS_RE_CANDIDATURE link to an IGS_AD_PS_APPL_INST.
469
470 -- The following is validated.
471
472 -- * An existing IGS_AD_PS_APPL_INST
473
474 -- (person_id/ acai_admission_appl_number/ acai_nominated_course_cd/
475
476 -- acai_sequence_number) link cannot be removed if the new application is
477
478 -- not re-admission and the IGS_PS_COURSE version of the admission application is
479
480 -- a research IGS_PS_COURSE (defined by IGS_PS_VER.course_cd/ version_number
481
482 -- mapping to IGS_PS_TYPE.research_type_ind set to 'Y') and
483
484 -- IGS_AD_PS_APPL_INST.adm_outcome_status maps to
485
486 -- IGS_AD_OU_STAT.s_adm_outcome_status 'OFFER' or 'COND-OFFER'.
487
488 -- * An existing IGS_AD_PS_APPL_INST
489
490 -- (person_id/ acai_admission_appl_number/ acai_nominated_course_cd/
491
492 -- acai_sequence_number) link cannot be removed if the admission application
493
494 -- is defined by an admission process category where research details are
495
496 -- mandatory ie IGS_AD_PRCS_CAT_STEP.mandatory_step_ind 'Y' where for
497
498 -- IGS_AD_PRCS_CAT_STEP.s_admission_step_type 'RESEARCH' and
499
500 -- IGS_AD_PS_APPL_INST.adm_outcome_status maps to
501
502 -- IGS_AD_OU_STAT.s_adm_outcome_status 'OFFER' or 'COND-OFFER'.
503
504 -- * The new IGS_AD_PS_APPL_INST (person_id/ acai_admission_appl_number/
505
506 -- acai_nominated_course_cd/ acai_sequence_number) does not already have a
507
508 -- research IGS_RE_CANDIDATURE.
509
510 -- * The new IGS_AD_PS_APPL_INST (person_id/ acai_admission_appl_number/
511
512 -- acai_nominated_course_cd/ acai_sequence_number) must map to an admission
513
514 -- application defined by an admission process category where research
515
516 -- details are collected ie IGS_AD_PRCS_CAT_STEP.s_admission_step_type
517
518 -- 'RESEARCH' exists.
519
520 DECLARE
521
522 cst_readmit CONSTANT VARCHAR2(10) := 'RE-ADMIT';
523
524 cst_research CONSTANT VARCHAR2(10) := 'RESEARCH';
525
526 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
527
528 v_test VARCHAR2(1);
529
530 v_message_name VARCHAR2(30);
531
532 CURSOR c_aa
533
534 IS
535
536 SELECT aa.s_admission_process_type
537
538 FROM IGS_AD_APPL aa
539
540 WHERE aa.person_id = p_person_id AND
541
542 aa.admission_appl_number = p_acai_admission_appl_number;
543
544 CURSOR c_ca
545
546 IS
547
548 SELECT 'x'
549
550 FROM IGS_RE_CANDIDATURE ca
551
552 WHERE ca.person_id = p_person_id AND
553
554 (p_ca_sequence_number IS NULL OR
555
556 ca.sequence_number <> p_ca_sequence_number) AND
557
558 ca.acai_admission_appl_number = p_acai_admission_appl_number AND
559
560 ca.acai_nominated_course_cd = p_acai_nominated_course_cd AND
561
562 ca.acai_sequence_number = p_acai_sequence_number;
563
564 CURSOR c_aa_apcs
565
566 IS
567
568 SELECT 'x'
569
570 FROM IGS_AD_APPL aa,
571
572 IGS_AD_PRCS_CAT_STEP apcs
573
574 WHERE aa.person_id = p_person_id AND
575
576 aa.admission_appl_number = p_acai_admission_appl_number AND
577
578 aa.admission_cat = apcs.admission_cat AND
579
580 aa.s_admission_process_type = apcs.s_admission_process_type AND
581
582 apcs.s_admission_step_type = cst_research AND
583
584 apcs.step_group_type <> 'TRACK'; --2402377
585
586 BEGIN
587
588 -- Set the default message number
589
590 p_message_name := null;
591
592 IF p_acai_admission_appl_number IS NOT NULL THEN
593
594 -- Determine if admission application details are being updated as a result
595
596 -- of readmission.
597
598 OPEN c_aa;
599
600 FETCH c_aa INTO v_s_admission_process_type;
601
602 IF c_aa%NOTFOUND THEN
603
604 CLOSE c_aa;
605
606 RETURN TRUE;
607
608 END IF;
609
610 CLOSE c_aa;
611
612 END IF;
613
614 IF p_old_acai_admission_appl_num IS NOT NULL THEN
615
616 IF (p_acai_admission_appl_number IS NULL OR
617
618 (p_acai_admission_appl_number <>
619
620 p_old_acai_admission_appl_num OR
621
622 p_acai_nominated_course_cd <>
623
624 p_old_acai_nominated_course_cd OR
625
626 p_acai_sequence_number <>
627
628 p_old_acai_sequence_number)) AND
629
630 (v_s_admission_process_type <>
631
632 cst_readmit) THEN
633
634 -- Validate that a required research IGS_RE_CANDIDATURE link is not being broken.
635
636 IF IGS_RE_VAL_CA.resp_val_ca_acai_del(
637
638 p_person_id,
639
640 p_old_acai_admission_appl_num,
641
642 p_old_acai_nominated_course_cd,
643
644 p_old_acai_sequence_number,
645
646 v_message_name) = FALSE THEN
647
648 p_message_name := v_message_name;
649
650 RETURN FALSE;
651
652 END IF;
653
654 END IF;
655
656 END IF;
657
658 IF p_acai_admission_appl_number IS NOT NULL THEN
659
660 -- Validate that admission IGS_PS_COURSE application does not already have a
661
662 -- research IGS_RE_CANDIDATURE
663
664 OPEN c_ca;
665
666 FETCH c_ca INTO v_test;
667
668 IF c_ca%FOUND THEN
669
670 CLOSE c_ca;
671
672 p_message_name := 'IGS_RE_CAND_ALREADY_EXIST_ADM';
673
674 RETURN FALSE;
675
676 END IF;
677
678 CLOSE c_ca;
679
680 -- Validate that research step exists for admission application definition.
681
682 IF v_s_admission_process_type <> cst_readmit THEN
683
684 OPEN c_aa_apcs;
685
686 FETCH c_aa_apcs INTO v_test;
687
688 IF c_aa_apcs%NOTFOUND THEN
689
690 CLOSE c_aa_apcs;
691
692 p_message_name := 'IGS_RE_CAND_DETAIL_NOT_REQR';
693
694 RETURN FALSE;
695
696 END IF;
697
698 CLOSE c_aa_apcs;
699
700 END IF;
701
702 END IF;
703
704 RETURN TRUE;
705
706 EXCEPTION
707
708 WHEN OTHERS THEN
709
710 IF c_aa%ISOPEN THEN
711
712 CLOSE c_aa;
713
714 END IF;
715
716 IF c_ca%ISOPEN THEN
717
718 CLOSE c_ca;
719
720 END IF;
721
722 IF c_aa_apcs%ISOPEN THEN
723
724 CLOSE c_aa_apcs;
725
726 END IF;
727
728 RAISE;
729
730 END;
731
732 EXCEPTION
733
734 WHEN OTHERS THEN
735 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
736 IGS_GE_MSG_STACK.ADD;
737 App_Exception.Raise_Exception;
738
739 END resp_val_ca_acai;
740
741 --
742 FUNCTION resp_val_ca_maxsbmsn(
743 p_person_id IN NUMBER ,
744 p_sca_course_cd IN VARCHAR2 ,
745 p_acai_admission_appl_number IN NUMBER ,
746 p_acai_nominated_course_cd IN VARCHAR2 ,
747 p_acai_sequence_number IN NUMBER ,
748 p_min_submission_dt IN DATE ,
749 p_max_submission_dt IN DATE ,
750 p_attendance_percentage IN NUMBER ,
751 p_commencement_dt IN DATE ,
752 p_message_name OUT NOCOPY VARCHAR2 ,
753 p_legacy IN VARCHAR2)
754 RETURN BOOLEAN AS
755 /*----------------------------------------------------------------------------
756 || Created By :
757 || Created On :
758 || Purpose : Validate IGS_RE_CANDIDATURE maximum submission date.
759 ||
760 || Known limitations, enhancements or remarks :
761 || Change History :
762 || Who When What
763 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
764 || if p_legacy set to 'Y' then error message should be stacked instead of
765 || returning the function in the normal way else function should behave in
766 || normal way.Legacy Build Bug no: 2661533
767 ------------------------------------------------------------------------------*/
768
769 BEGIN -- resp_val_ca_maxsbmsn
770 DECLARE
771
772 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
773 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
774 v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
775 v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
776 v_commencement_dt DATE;
777 v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
778 v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
779
780 CURSOR c_sca IS
781 SELECT sca.student_confirmed_ind
782 FROM IGS_EN_STDNT_PS_ATT sca
783 WHERE sca.person_id = p_person_id AND
784 sca.course_cd = p_sca_course_cd;
785 CURSOR c_aos_acai IS
786 SELECT aos.s_adm_outcome_status
787 FROM IGS_AD_PS_APPL_INST acai,
788 IGS_AD_OU_STAT aos
789 WHERE acai.person_id = p_person_id AND
790 acai.admission_appl_number = p_acai_admission_appl_number AND
791 acai.nominated_course_cd = p_acai_nominated_course_cd AND
792 acai.sequence_number = p_acai_sequence_number AND
793 acai.adm_outcome_status = aos.adm_outcome_status;
794 BEGIN
795 -- Set the default message number
796 p_message_name := null;
797 IF p_max_submission_dt IS NULL THEN
798 -- Get derived maximum submission date
799 v_max_submission_dt := IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN(
800 p_person_id,
801 NULL, --(ca.sequence_number)
802 p_sca_course_cd,
803 p_acai_admission_appl_number,
804 p_acai_nominated_course_cd,
805 p_acai_sequence_number,
806 p_attendance_percentage,
807 p_commencement_dt);
808 IF v_max_submission_dt IS NULL THEN
809 -- Validate against student IGS_PS_COURSE attempt
810 IF p_sca_course_cd IS NOT NULL THEN
811 OPEN c_sca;
812 FETCH c_sca INTO v_stdnt_confm_ind;
813 IF c_sca%NOTFOUND THEN
814 -- Invalid parameters, handled elsewhere
815 CLOSE c_sca;
816 RETURN TRUE;
817 END IF;
818 CLOSE c_sca;
819 IF v_stdnt_confm_ind = 'Y' THEN
820 p_message_name := 'IGS_RE_MAX_SUB_DT_REQ_CRSATMP';
821 IF p_legacy <> 'Y' THEN
822 RETURN FALSE;
823 ELSE
824 fnd_message.set_name('IGS',p_message_name);
825 fnd_msg_pub.add;
826 END IF;
827 END IF;
828 END IF;
829 -- Validate against admission IGS_PS_COURSE application
830 IF p_acai_admission_appl_number IS NOT NULL THEN
831 OPEN c_aos_acai;
832 FETCH c_aos_acai INTO v_s_adm_otcm_status;
833 IF c_aos_acai%NOTFOUND THEN
834 -- Invalid parameters, handled elsewhere
835 CLOSE c_aos_acai;
836 RETURN TRUE;
837 END IF;
838 CLOSE c_aos_acai;
839 IF v_s_adm_otcm_status IN ( cst_offer,
840 cst_cond_offer) THEN
841 p_message_name := 'IGS_RE_MAX_SUB_DT_REQ_ADM';
842 IF p_legacy <> 'Y' THEN
843 RETURN FALSE;
844 ELSE
845 fnd_message.set_name('IGS',p_message_name);
846 fnd_msg_pub.add;
847 END IF;
848 END IF;
849 END IF;
850 END IF; --IF v_max_submission_dt IS NUL
851 ELSE
852 -- Validate that maximum submission date is greater than or equal to the
853 -- minimum submission date
854 IF p_min_submission_dt IS NULL THEN
855 -- Validate against derived maximum submission date
856 v_min_submission_dt := IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN(
857 p_person_id,
858 NULL, -- ca.sequence_number
859 p_sca_course_cd,
860 p_acai_admission_appl_number,
861 p_acai_nominated_course_cd,
862 p_acai_sequence_number,
863 p_attendance_percentage,
864 p_commencement_dt);
865 ELSE
866 v_min_submission_dt := p_min_submission_dt;
867 END IF;
868 IF v_min_submission_dt IS NOT NULL AND
869 p_max_submission_dt < v_min_submission_dt THEN
870 p_message_name := 'IGS_RE_MAX_SUB_DT_LT_MIN_DT';
871 IF p_legacy <> 'Y' THEN
872 RETURN FALSE;
873 ELSE
874 fnd_message.set_name('IGS',p_message_name);
875 fnd_msg_pub.add;
876 END IF;
877 END IF;
878 END IF;
879 RETURN TRUE;
880 EXCEPTION
881 WHEN OTHERS THEN
882 IF c_sca%ISOPEN THEN
883 CLOSE c_sca;
884 END IF;
885 IF c_aos_acai%ISOPEN THEN
886 CLOSE c_aos_acai;
887 END IF;
888 RAISE;
889 END;
890 EXCEPTION
891 WHEN OTHERS THEN
892 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
893 IGS_GE_MSG_STACK.ADD;
894 App_Exception.Raise_Exception;
895 END resp_val_ca_maxsbmsn;
896
897 --
898 FUNCTION resp_val_ca_minsbmsn(
899 p_person_id IN NUMBER ,
900 p_sca_course_cd IN VARCHAR2 ,
901 p_acai_admission_appl_number IN NUMBER ,
902 p_acai_nominated_course_cd IN VARCHAR2 ,
903 p_acai_sequence_number IN NUMBER ,
904 p_min_submission_dt IN DATE ,
905 p_max_submission_dt IN DATE ,
906 p_attendance_percentage IN NUMBER ,
907 p_commencement_dt IN DATE ,
908 p_message_name OUT NOCOPY VARCHAR2 ,
909 p_legacy IN VARCHAR2)
910 RETURN BOOLEAN AS
911 /*----------------------------------------------------------------------------
912 || Created By :
913 || Created On :
914 || Purpose : Validate IGS_RE_CANDIDATURE minimum submission date.
915 ||
916 || Known limitations, enhancements or remarks :
917 || Change History :
918 || Who When What
919 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
920 || if p_legacy set to 'Y' then error message should be stacked instead of
921 || returning the function in the normal way else function should behave in
922 || normal way.Legacy Build Bug no: 2661533
923 ------------------------------------------------------------------------------*/
924 BEGIN -- resp_val_ca_minsbmsn
925 DECLARE
926
927 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
928 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
929 v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
930 v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE;
931 v_commencement_dt DATE;
932 v_stdnt_confm_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
933 v_s_adm_otcm_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
934 CURSOR c_sca IS
935 SELECT sca.student_confirmed_ind
936 FROM IGS_EN_STDNT_PS_ATT sca
937 WHERE sca.person_id = p_person_id AND
938 sca.course_cd = p_sca_course_cd;
939 CURSOR c_aos_acai IS
940 SELECT aos.s_adm_outcome_status
941 FROM IGS_AD_PS_APPL_INST acai,
942 IGS_AD_OU_STAT aos
943 WHERE acai.person_id = p_person_id AND
944 acai.admission_appl_number = p_acai_admission_appl_number AND
945 acai.nominated_course_cd = p_acai_nominated_course_cd AND
946 acai.sequence_number = p_acai_sequence_number AND
947 acai.adm_outcome_status = aos.adm_outcome_status;
948 BEGIN
949
950 -- Set the default message number
951 p_message_name := null;
952 IF p_min_submission_dt IS NULL THEN
953 -- Get derived minimum submission date
954 v_min_submission_dt := IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN(
955 p_person_id,
956 NULL, -- (ca.sequence_number)
957 p_sca_course_cd,
958 p_acai_admission_appl_number,
959 p_acai_nominated_course_cd,
960 p_acai_sequence_number,
961 p_attendance_percentage,
962 p_commencement_dt);
963
964 IF v_min_submission_dt IS NULL THEN
965 -- Validate against student IGS_PS_COURSE attempt
966 IF p_sca_course_cd IS NOT NULL THEN
967 OPEN c_sca;
968 FETCH c_sca INTO v_stdnt_confm_ind;
969 IF c_sca%NOTFOUND THEN
970 -- Invalid parameters, handled elsewhere
971 CLOSE c_sca;
972 RETURN TRUE;
973 END IF;
974 CLOSE c_sca;
975 IF v_stdnt_confm_ind = 'Y' THEN
976 p_message_name := 'IGS_RE_MIN_SUB_DT_REQ_CRSATMP';
977 IF p_legacy <> 'Y' THEN
978 RETURN FALSE;
979 ELSE
980 fnd_message.set_name('IGS',p_message_name);
981 fnd_msg_pub.add;
982 END IF;
983 END IF;
984 END IF;
985 -- Validate against admission IGS_PS_COURSE application
986 IF p_acai_admission_appl_number IS NOT NULL THEN
987 OPEN c_aos_acai;
988 FETCH c_aos_acai INTO v_s_adm_otcm_status;
989 IF c_aos_acai%NOTFOUND THEN
990 -- Invalid parameters, handled elsewhere
991 CLOSE c_aos_acai;
992 RETURN TRUE;
993 END IF;
994 CLOSE c_aos_acai;
995 IF v_s_adm_otcm_status IN (cst_offer,
996 cst_cond_offer) THEN
997 p_message_name := 'IGS_RE_MIN_SUBM_DT_REQ_ADM';
998 IF p_legacy <> 'Y' THEN
999 RETURN FALSE;
1000 ELSE
1001 fnd_message.set_name('IGS',p_message_name);
1002 fnd_msg_pub.add;
1003 END IF;
1004 END IF;
1005 END IF;
1006 END IF; --IF v_min_submission_dt IS NULL
1007 ELSE
1008 -- Get IGS_RE_CANDIDATURE commencement date
1009 IF p_commencement_dt IS NULL THEN
1010 v_commencement_dt := IGS_RE_GEN_001.RESP_GET_CA_COMM(
1011 p_person_id,
1012 p_sca_course_cd,
1013 p_acai_admission_appl_number,
1014 p_acai_nominated_course_cd,
1015 p_acai_sequence_number);
1016 ELSE
1017 v_commencement_dt := p_commencement_dt;
1018 END IF;
1019 IF p_min_submission_dt <= v_commencement_dt THEN
1020 p_message_name := 'IGS_RE_MIN_SUB_DT_LT_CRS_COMM';
1021 IF p_legacy <> 'Y' THEN
1022 RETURN FALSE;
1023 ELSE
1024 fnd_message.set_name('IGS',p_message_name);
1025 fnd_msg_pub.add;
1026 END IF;
1027 END IF;
1028 -- Validate minimum submission date is less than or equal to maximum
1029 -- submission date
1030 IF p_max_submission_dt IS NULL THEN
1031 -- Validate against derived maximum submission date
1032 v_max_submission_dt := IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN(
1033 p_person_id,
1034 NULL, -- ca.sequence_number
1035 p_sca_course_cd,
1036 p_acai_admission_appl_number,
1037 p_acai_nominated_course_cd,
1038 p_acai_sequence_number,
1039 p_attendance_percentage,
1040 p_commencement_dt);
1041 ELSE
1042 v_max_submission_dt := p_max_submission_dt;
1043 END IF;
1044 IF v_max_submission_dt IS NOT NULL AND
1045 p_min_submission_dt > v_max_submission_dt THEN
1046 p_message_name := 'IGS_RE_MIN_SUB_DT_GE_MAX_DT';
1047 IF p_legacy <> 'Y' THEN
1048 RETURN FALSE;
1049 ELSE
1050 fnd_message.set_name('IGS',p_message_name);
1051 fnd_msg_pub.add;
1052 END IF;
1053 END IF;
1054 END IF;
1055 RETURN TRUE;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 IF c_sca%ISOPEN THEN
1059 CLOSE c_sca;
1060 END IF;
1061 IF c_aos_acai%ISOPEN THEN
1062 CLOSE c_aos_acai;
1063 END IF;
1064 RAISE;
1065 END;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1069 IGS_GE_MSG_STACK.ADD;
1070 App_Exception.Raise_Exception;
1071 END resp_val_ca_minsbmsn;
1072
1073 --
1074
1075 -- Validate IGS_RE_CANDIDATURE SCA link.
1076
1077 FUNCTION resp_val_ca_sca(
1078 p_person_id IN NUMBER ,
1079 p_ca_sequence_number IN NUMBER ,
1080 p_old_sca_course_cd IN VARCHAR2 ,
1081 p_sca_course_cd IN VARCHAR2 ,
1082 p_acai_admission_appl_number IN NUMBER ,
1083 p_acai_nominated_course_cd IN VARCHAR2 ,
1084 p_acai_sequence_number IN NUMBER ,
1085 p_message_name OUT NOCOPY VARCHAR2 )
1086 RETURN BOOLEAN AS
1087 BEGIN -- resp_val_ca_sca
1088 -- This module validates the IGS_RE_CANDIDATURE link to a IGS_EN_STDNT_PS_ATT.
1089 DECLARE
1090 v_message_name VARCHAR2(30);
1091
1092 v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
1093
1094 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1095
1096 v_test VARCHAR2(1);
1097
1098 cst_discontin CONSTANT
1099
1100 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'DISCONTIN';
1101
1102 cst_lapsed CONSTANT
1103
1104 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'LAPSED';
1105
1106 cst_completed CONSTANT
1107
1108 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
1109
1110 CURSOR c_sca (
1111
1112 cp_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE)
1113
1114 IS
1115
1116 SELECT sca.student_confirmed_ind,
1117
1118 sca.course_attempt_status
1119
1120 FROM IGS_EN_STDNT_PS_ATT sca
1121
1122 WHERE sca.person_id = p_person_id AND
1123
1124 sca.course_cd = cp_course_cd;
1125
1126 CURSOR c_ca
1127
1128 IS
1129
1130 SELECT 'x'
1131
1132 FROM IGS_RE_CANDIDATURE ca
1133
1134 WHERE ca.person_id = p_person_id AND
1135
1136 (p_ca_sequence_number IS NULL OR
1137
1138 ca.sequence_number <> p_ca_sequence_number) AND
1139
1140 ca.sca_course_cd= p_sca_course_cd;
1141
1142 BEGIN
1143
1144 IF p_old_sca_course_cd IS NOT NULL THEN
1145
1146 IF p_sca_course_cd IS NULL OR
1147
1148 (p_sca_course_cd <> p_old_sca_course_cd) THEN
1149
1150 -- Validate that a required research IGS_RE_CANDIDATURE link is not being broken
1151
1152 IF NOT IGS_RE_VAL_CA.resp_val_ca_sca_del(
1153
1154 p_person_id,
1155
1156 p_old_sca_course_cd,
1157
1158 v_message_name) THEN
1159
1160 p_message_name := v_message_name;
1161
1162 RETURN FALSE;
1163
1164 END IF;
1165
1166 IF p_sca_course_cd IS NOT NULL THEN
1167
1168 -- Validate that student IGS_PS_COURSE attempt is not being changed
1169
1170 -- When the existing IGS_PS_COURSE attempt link is confirmed.
1171
1172 OPEN c_sca(p_old_sca_course_cd);
1173
1174 FETCH c_sca INTO v_student_confirmed_ind,
1175
1176 v_course_attempt_status;
1177
1178 IF c_sca%NOTFOUND THEN
1179
1180 CLOSE c_sca;
1181
1182 -- Parameters passed are invalid.
1183
1184 p_message_name := 'IGS_RE_INVALID_PARAMETERS';
1185
1186 RETURN FALSE;
1187
1188 END IF;
1189
1190 CLOSE c_sca;
1191
1192 IF v_student_confirmed_ind = 'Y' THEN
1193
1194 p_message_name := 'IGS_RE_CAND_CANNOT_BE_LNKED';
1195
1196 RETURN FALSE;
1197
1198 END IF;
1199
1200 END IF;
1201
1202 END IF;
1203
1204 END IF;
1205
1206 IF p_sca_course_cd IS NOT NULL THEN
1207
1208 -- Validate that student IGS_PS_COURSE attempt does not already have a
1209
1210 -- research IGS_RE_CANDIDATURE.
1211
1212 OPEN c_ca;
1213
1214 FETCH c_ca INTO v_test;
1215
1216 IF c_ca%FOUND THEN
1217
1218 CLOSE c_ca;
1219
1220 p_message_name := 'IGS_RE_CAND_ALREADY_EXISTS';
1221
1222 RETURN FALSE;
1223
1224 END IF;
1225
1226 CLOSE c_ca;
1227
1228 -- Validate that the student IGS_PS_COURSE attempt is not discontinued, lapsed or
1229
1230 -- completed.
1231
1232 OPEN c_sca(p_sca_course_cd);
1233
1234 FETCH c_sca INTO v_student_confirmed_ind,
1235
1236 v_course_attempt_status;
1237
1238 IF c_sca%NOTFOUND THEN
1239
1240 CLOSE c_sca;
1241
1242 -- Invalid data, handled elsewhere
1243
1244 p_message_name := null;
1245
1246 RETURN TRUE;
1247
1248 END IF;
1249
1250 CLOSE c_sca;
1251
1252 IF v_course_attempt_status IN (
1253
1254 cst_discontin,
1255
1256 cst_lapsed,
1257
1258 cst_completed) THEN
1259
1260 p_message_name := 'IGS_RE_CANT_INS_IF_CATMP_DISC';
1261
1262 RETURN FALSE;
1263
1264 END IF;
1265
1266 END IF;
1267
1268 p_message_name := null;
1269
1270 RETURN TRUE;
1271
1272 EXCEPTION
1273
1274 WHEN OTHERS THEN
1275
1276 IF c_sca%ISOPEN THEN
1277
1278 CLOSE c_sca;
1279
1280 END IF;
1281
1282 IF c_ca%ISOPEN THEN
1283
1284 CLOSE c_ca;
1285
1286 END IF;
1287
1288 RAISE;
1289
1290 END;
1291
1292 EXCEPTION
1293
1294 WHEN OTHERS THEN
1295 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1296 IGS_GE_MSG_STACK.ADD;
1297 App_Exception.Raise_Exception;
1298
1299 END resp_val_ca_sca;
1300
1301 --
1302
1303 -- Validate IGS_RE_CANDIDATURE deletion and ACAI link.
1304
1305 FUNCTION resp_val_ca_acai_del(
1306
1307 p_person_id IN NUMBER ,
1308
1309 p_acai_admission_appl_number IN NUMBER ,
1310
1311 p_acai_nominated_course_cd IN VARCHAR2 ,
1312
1313 p_acai_sequence_number IN NUMBER ,
1314
1315 p_message_name OUT NOCOPY VARCHAR2 )
1316
1317 RETURN BOOLEAN AS
1318
1319 BEGIN -- resp_val_ca_acai_del
1320
1321 -- This module validates the removal of a IGS_RE_CANDIDATURE/
1322
1323 -- IGS_AD_PS_APPL_INST link. This may be the result of deletion of
1324
1325 -- IGS_RE_CANDIDATURE, or removal/change of IGS_RE_CANDIDATURE.acai_admission_appl_number/
1326
1327 -- acai_nominated_course_cd/ acai_sequence_number which defines an existing
1328
1329 -- admission IGS_PS_COURSE application relationship.
1330
1331 DECLARE
1332
1333 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
1334
1335 v_research_type_ind IGS_PS_TYPE.research_type_ind%TYPE;
1336
1337 v_mandatory_step_ind IGS_AD_PRCS_CAT_STEP.mandatory_step_ind%TYPE;
1338
1339 CURSOR c_acai IS
1340
1341 SELECT aos.s_adm_outcome_status,
1342
1343 cty.research_type_ind
1344
1345 FROM IGS_AD_PS_APPL_INST acai,
1346
1347 IGS_AD_OU_STAT aos,
1348
1349 IGS_PS_VER crv,
1350
1351 IGS_PS_TYPE cty
1352
1353 WHERE acai.person_id = p_person_id AND
1354
1355 acai.admission_appl_number = p_acai_admission_appl_number AND
1356
1357 acai.nominated_course_cd = p_acai_nominated_course_cd AND
1358
1359 acai.sequence_number = p_acai_sequence_number AND
1360
1361 acai.course_cd = crv.course_cd AND
1362
1363 acai.crv_version_number = crv.version_number AND
1364
1365 acai.adm_outcome_status = aos.adm_outcome_status AND
1366
1367 crv.course_type = cty.course_type;
1368
1369 CURSOR c_apcs IS
1370
1371 SELECT apcs.mandatory_step_ind
1372
1373 FROM IGS_AD_APPL aa,
1374
1375 IGS_AD_PRCS_CAT_STEP apcs
1376
1377 WHERE aa.person_id = p_person_id AND
1378
1379 aa.admission_appl_number = p_acai_admission_appl_number AND
1380
1381 aa.admission_cat = apcs.admission_cat AND
1382
1383 aa.s_admission_process_type = apcs.s_admission_process_type AND
1384
1385 apcs.s_admission_step_type = 'RESEARCH' AND
1386
1387 apcs.step_group_type <> 'TRACK'; --2402377
1388
1389 BEGIN
1390
1391 IF p_acai_admission_appl_number IS NOT NULL THEN
1392
1393 OPEN c_acai;
1394
1395 FETCH c_acai INTO v_s_adm_outcome_status,
1396
1397 v_research_type_ind;
1398
1399 IF c_acai%NOTFOUND THEN
1400
1401 CLOSE c_acai;
1402
1403 -- Invalid Parameters, this will be handled by db constraints
1404
1405 p_message_name := null;
1406
1407 RETURN TRUE;
1408
1409 END IF;
1410
1411 CLOSE c_acai;
1412
1413 IF v_s_adm_outcome_status = 'OFFER' OR
1414
1415 v_s_adm_outcome_status = 'COND-OFFER' THEN
1416
1417 IF v_research_type_ind = 'Y' THEN
1418
1419 -- Research IGS_RE_CANDIDATURE is required by research only IGS_PS_COURSEs
1420
1421 p_message_name := 'IGS_RE_CAND_REQ_WHEN_CRS_OFF';
1422
1423 RETURN FALSE;
1424
1425 END IF;
1426
1427 OPEN c_apcs;
1428
1429 FETCH c_apcs INTO v_mandatory_step_ind;
1430
1431 IF c_apcs%NOTFOUND THEN
1432
1433 CLOSE c_apcs;
1434
1435 p_message_name := null;
1436
1437 RETURN TRUE;
1438
1439 END IF;
1440
1441 CLOSE c_apcs;
1442
1443 IF v_mandatory_step_ind = 'Y' THEN
1444
1445 p_message_name := 'IGS_RE_CAND_REQ_AS_PRT_OF_ADM';
1446
1447 RETURN FALSE;
1448
1449 END IF;
1450
1451 END IF;
1452
1453 END IF;
1454
1455 p_message_name := null;
1456
1457 RETURN TRUE;
1458
1459 EXCEPTION
1460
1461 WHEN OTHERS THEN
1462
1463 IF c_acai%ISOPEN THEN
1464
1465 CLOSE c_acai;
1466
1467 END IF;
1468
1469 IF c_apcs%ISOPEN THEN
1470
1471 CLOSE c_apcs;
1472
1473 END IF;
1474
1475 RAISE;
1476
1477 END;
1478
1479 EXCEPTION
1480
1481 WHEN OTHERS THEN
1482 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1483 IGS_GE_MSG_STACK.ADD;
1484 App_Exception.Raise_Exception;
1485
1486 END resp_val_ca_acai_del;
1487
1488 --
1489
1490 -- Validate IGS_RE_CANDIDATURE SCA link when deleting.
1491
1492 FUNCTION resp_val_ca_sca_del(
1493
1494 p_person_id IN NUMBER ,
1495
1496 p_sca_course_cd IN VARCHAR2 ,
1497
1498 p_message_name OUT NOCOPY VARCHAR2 )
1499
1500 RETURN BOOLEAN AS
1501
1502 BEGIN -- resp_val_ca_sca_del
1503
1504 -- This module validates the removal of a IGS_RE_CANDIDATURE/IGS_EN_STDNT_PS_ATT
1505
1506 -- link.
1507
1508 -- This may be the result of deletion of IGS_RE_CANDIDATURE, or removal/change of
1509
1510 -- IGS_RE_CANDIDATURE.sca_course_cd which defines an existing IGS_PS_COURSE attempt
1511
1512 -- relationship.
1513
1514 DECLARE
1515
1516 v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
1517
1518 v_research_type_ind IGS_PS_TYPE.research_type_ind%TYPE;
1519
1520 CURSOR c_sca IS
1521
1522 SELECT sca.student_confirmed_ind,
1523
1524 cty.research_type_ind
1525
1526 FROM IGS_EN_STDNT_PS_ATT sca,
1527
1528 IGS_PS_VER crv,
1529
1530 IGS_PS_TYPE cty
1531
1532 WHERE sca.course_cd = crv.course_cd AND
1533
1534 sca.version_number = crv.version_number AND
1535
1536 crv.course_type = cty.course_type AND
1537
1538 sca.person_id = p_person_id AND
1539
1540 sca.course_cd = p_sca_course_cd;
1541
1542 BEGIN
1543
1544 IF p_sca_course_cd IS NOT NULL THEN
1545
1546 OPEN c_sca;
1547
1548 FETCH c_sca INTO v_student_confirmed_ind,
1549
1550 v_research_type_ind;
1551
1552 IF c_sca%NOTFOUND THEN
1553
1554 CLOSE c_sca;
1555
1556 -- Invalid Parameters, this will be handled by db constraints
1557
1558 p_message_name := null;
1559
1560 RETURN TRUE;
1561
1562 END IF;
1563
1564 CLOSE c_sca;
1565
1566 IF v_student_confirmed_ind = 'Y' AND
1567
1568 v_research_type_ind = 'Y' THEN
1569
1570 -- Research IGS_RE_CANDIDATURE is required by research only courses.
1571
1572 p_message_name := 'IGS_RE_CAND_REQ_WHEN_CRS_DEF';
1573
1574 RETURN FALSE;
1575
1576 END IF;
1577
1578 END IF;
1579
1580 p_message_name := null;
1581
1582 RETURN TRUE;
1583
1584 EXCEPTION
1585
1586 WHEN OTHERS THEN
1587
1588 IF c_sca%ISOPEN THEN
1589
1590 CLOSE c_sca;
1591
1592 END IF;
1593
1594 RAISE;
1595
1596 END;
1597
1598 EXCEPTION
1599
1600 WHEN OTHERS THEN
1601 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1602 IGS_GE_MSG_STACK.ADD;
1603 App_Exception.Raise_Exception;
1604
1605 END resp_val_ca_sca_del;
1606
1607 --
1608
1609 -- Validate if Government Type of Activity Classification Code is closed.
1610
1611 FUNCTION resp_val_gtcc_closed(
1612
1613 p_govt_toa_class_cd IN VARCHAR2 ,
1614
1615 p_message_name OUT NOCOPY VARCHAR2 )
1616
1617 RETURN BOOLEAN AS
1618
1619 BEGIN -- resp_val_gtcc_closed
1620
1621 -- Validate if IGS_RE_GV_TOA_CLS_CD.govt_toa_class_cd is closed.
1622
1623 DECLARE
1624
1625 v_gtcc_rec VARCHAR2(1);
1626
1627 CURSOR c_gtcc IS
1628
1629 SELECT 'X'
1630
1631 FROM IGS_RE_GV_TOA_CLS_CD
1632
1633 WHERE govt_toa_class_cd = p_govt_toa_class_cd AND
1634
1635 closed_ind = 'Y';
1636
1637 BEGIN
1638
1639 p_message_name := null;
1640
1641 OPEN c_gtcc;
1642
1643 FETCH c_gtcc INTO v_gtcc_rec;
1644
1645 IF (c_gtcc%FOUND) THEN
1646
1647 CLOSE c_gtcc;
1648
1649 p_message_name := 'IGS_RE_GOV_TYPE_CLASS_CD_CLOS';
1650
1651 RETURN FALSE;
1652
1653 END IF;
1654
1655 CLOSE c_gtcc;
1656
1657 RETURN TRUE;
1658
1659 END;
1660
1661 EXCEPTION
1662
1663 WHEN OTHERS THEN
1664 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1665 IGS_GE_MSG_STACK.ADD;
1666 App_Exception.Raise_Exception;
1667
1668 END resp_val_gtcc_closed;
1669
1670 --
1671
1672 -- Validate IGS_RE_CANDIDATURE SCA/ACAI link.
1673
1674 FUNCTION resp_val_ca_sca_acai(
1675
1676 p_person_id IN NUMBER ,
1677
1678 p_sca_course_cd IN VARCHAR2 ,
1679
1680 p_acai_admission_appl_number IN NUMBER ,
1681
1682 p_acai_nominated_course_cd IN VARCHAR2 ,
1683
1684 p_acai_sequence_number IN NUMBER ,
1685
1686 p_message_name OUT NOCOPY VARCHAR2 )
1687
1688 RETURN BOOLEAN AS
1689
1690 BEGIN -- resp_val_ca_sca_acai
1691
1692 -- This module validates IGS_RE_CANDIDATURE details are linked to either an
1693
1694 -- admission IGS_PS_COURSE application or IGS_PS_COURSE attempt. Both these are defined
1695
1696 -- by optional IGS_RE_CANDIDATURE information that is determined by the process
1697
1698 -- that initiates the research IGS_RE_CANDIDATURE (ADMF3240/ENRF3000). This module
1699
1700 -- will only be called from the database trigger.
1701
1702 -- The following is validated:
1703
1704 -- * One of sca_course_cd(IGS_EN_STDNT_PS_ATT) or acai_admission_appl_number
1705
1706 -- / acai_nominated_course_cd/acai_sequence_number(IGS_AD_PS_APPL_INST)
1707
1708 -- must be defined.
1709
1710 -- * If sca_course_cd exists, then IGS_EN_STDNT_PS_ATT and IGS_RE_CANDIDATURE
1711
1712 -- admission details must match.
1713
1714 DECLARE
1715
1716 CURSOR c_sca IS
1717
1718 SELECT sca.adm_admission_appl_number,
1719
1720 sca.adm_nominated_course_cd,
1721
1722 sca.adm_sequence_number
1723
1724 FROM IGS_EN_STDNT_PS_ATT sca
1725
1726 WHERE sca.person_id = p_person_id AND
1727
1728 sca.course_cd = p_sca_course_cd;
1729
1730 v_sca_rec c_sca%ROWTYPE;
1731
1732 BEGIN
1733
1734 p_message_name := null;
1735
1736 IF p_sca_course_cd IS NULL AND
1737
1738 (p_acai_admission_appl_number IS NULL OR
1739
1740 p_acai_nominated_course_cd IS NULL OR
1741
1742 p_acai_sequence_number IS NULL) THEN
1743
1744 p_message_name := 'IGS_RE_CHK_CANDIDATURE';
1745
1746 RETURN FALSE;
1747
1748 END IF;
1749
1750 IF p_sca_course_cd IS NOT NULL THEN
1751
1752 -- Validate that student IGS_PS_COURSE attempt and research
1753
1754 -- IGS_RE_CANDIDATURE details match.
1755
1756 OPEN c_sca;
1757
1758 FETCH c_sca INTO v_sca_rec;
1759
1760 IF c_sca%NOTFOUND THEN
1761
1762 CLOSE c_sca;
1763
1764 RETURN TRUE;
1765
1766 END IF;
1767
1768 CLOSE c_sca;
1769
1770 IF (NVL(p_acai_admission_appl_number,0) <>
1771
1772 NVL(v_sca_rec.adm_admission_appl_number,0)) OR
1773
1774 (NVL(p_acai_nominated_course_cd, 'NULL') <>
1775
1776 NVL(v_sca_rec.adm_nominated_course_cd, 'NULL')) OR
1777
1778 (NVL(p_acai_sequence_number,0) <>
1779
1780 NVL(v_sca_rec.adm_sequence_number,0)) THEN
1781
1782 p_message_name := 'IGS_RE_CAND_STUD_DET_MISMATCH';
1783
1784 RETURN FALSE;
1785
1786 END IF;
1787
1788 END IF;
1789
1790 RETURN TRUE;
1791
1792 EXCEPTION
1793
1794 WHEN OTHERS THEN
1795
1796 IF c_sca%ISOPEN THEN
1797
1798 CLOSE c_sca;
1799
1800 END IF;
1801
1802 RAISE;
1803
1804 END;
1805
1806 EXCEPTION
1807
1808 WHEN OTHERS THEN
1809 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1810 IGS_GE_MSG_STACK.ADD;
1811 App_Exception.Raise_Exception;
1812
1813 END resp_val_ca_sca_acai;
1814
1815 END IGS_RE_VAL_CA;
[Home] [Help]