1 PACKAGE BODY Igs_En_Val_Susa AS
2 /* $Header: IGSEN69B.pls 120.3 2006/05/03 23:59:19 smaddali noship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
8 -- Also the call to function igs_en_val_susa.genp_val_staff_prsn is
9 -- is replaced by igs_ad_val_acai.genp_val_staff_prsn
10 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_en_val_susa.genp_val_sdtt_sess
11 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess.Also
12 -- Function genp_val_sdtt_sess Removed
13 --msrinivi 27-Aug-2001 Function genp_val_prsn_id removed
14 --prraj 15-Nov-2002 Added p_legacy parameter to functions enrp_val_susa_ins, enrp_val_susa_auth,
15 -- enrp_val_susa_cmplt, enrp_val_susa_sci_sd, enrp_val_susa_cousr, enrp_val_susa_parent,
16 -- enrp_val_susa_end_dt, enrp_val_susa_sci, enrp_val_susa_prmry as part of Legacy
17 -- build Bug# 2661533
18 -------------------------------------------------------------------------------------------
19 --
20 -- Routine to process susa rowids in PL/SQL TABLE for current commit.
21 --
22 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
23
24 --
25 -- Validate the authorisation fields.
26 FUNCTION enrp_val_susa_auth(
27 p_unit_set_cd IN VARCHAR2 ,
28 p_us_version_number IN NUMBER ,
29 p_end_dt IN IGS_AS_SU_SETATMPT.end_dt%TYPE ,
30 p_authorised_person_id IN NUMBER ,
31 p_authorised_on IN DATE ,
32 p_message_name OUT NOCOPY VARCHAR2,
33 p_legacy IN VARCHAR2)
34 RETURN BOOLEAN AS
35
36 v_message_name VARCHAR2(30);
37 BEGIN -- enrp_val_susa_auth
38 -- This module validates the authorisation fields associated with the
39 -- IGS_AS_SU_SETATMPT:
40 -- - If the authorised_person_id is set, then the authorised_on must also be
41
42 -- set and visa versa.
43 -- - authorised_person_id and authorised_on fields can only be set if the
44 -- end_dt is also set or the IGS_EN_UNIT_SET.authorisation_ind = 'Y'.
45 -- Validate that the person is a staff member.
46 DECLARE
47 v_authorisation_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
48 CURSOR c_us IS
49 SELECT us.authorisation_rqrd_ind
50 FROM IGS_EN_UNIT_SET us
51 WHERE us.unit_set_cd = p_unit_set_cd AND
52 us.version_number = p_us_version_number;
53 BEGIN
54 p_message_name := NULL;
55
56 -- If the authorised_person_id is set, then the authorised_on must also be
57 -- set and visa versa.
58 IF (p_authorised_person_id IS NOT NULL AND
59 p_authorised_on IS NULL) THEN
60 p_message_name := 'IGS_EN_AUTHDT_MUSTBE_SET';
61
62 IF (p_legacy = 'Y') THEN
63 -- Add excep to stack
64 FND_MESSAGE.Set_Name('IGS',p_message_name);
65 FND_MSG_PUB.Add;
66 ELSE
67 RETURN FALSE;
68 END IF;
69
70 END IF;
71
72 IF (p_authorised_person_id IS NULL AND
73 p_authorised_on IS NOT NULL) THEN
74 p_message_name := 'IGS_EN_AUTHDT_NOTBE_AUTHPRSN';
75
76 IF (p_legacy = 'Y') THEN
77 -- Add excep to stack
78 FND_MESSAGE.Set_Name('IGS',p_message_name);
79 FND_MSG_PUB.Add;
80 ELSE
81 RETURN FALSE;
82 END IF;
83
84 END IF;
85
86 -- authorised_person_id and authorised_on fields can only be set if the
87 -- end_dt is also set or the IGS_EN_UNIT_SET.authorisation_ind = 'Y'.
88 IF (p_authorised_person_id IS NOT NULL AND
89 p_end_dt IS NULL) THEN
90 OPEN c_us;
91 FETCH c_us INTO v_authorisation_rqrd_ind;
92 CLOSE c_us;
93 IF (v_authorisation_rqrd_ind = 'N') THEN
94 p_message_name := 'IGS_EN_AUTHDT_AUTHPRSN_SET';
95
96 IF (p_legacy = 'Y') THEN
97 -- Add excep to stack
98 FND_MESSAGE.Set_Name('IGS',p_message_name);
99 FND_MSG_PUB.Add;
100 ELSE
101 RETURN FALSE;
102 END IF;
103
104 END IF;
105 END IF;
106
107 -- Validate that the authorising person is a staff member.
108 IF p_authorised_person_id IS NOT NULL THEN
109 IF igs_ad_val_acai.genp_val_staff_prsn(p_authorised_person_id,
110 v_message_name) = FALSE THEN
111 p_message_name := 'IGS_EN_AUTHORISED_PRSN_NOT';
112
113 IF (p_legacy = 'Y') THEN
114 -- Add excep to stack
115 FND_MESSAGE.Set_Name('IGS',p_message_name);
116 FND_MSG_PUB.Add;
117 ELSE
118 RETURN FALSE;
119 END IF;
120
121 END IF;
122 END IF;
123
124 RETURN TRUE;
125
126 END;
127 EXCEPTION
128 WHEN OTHERS THEN
129 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
130 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_auth');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133
134 END enrp_val_susa_auth;
135
136
137 --
138 -- Validate the requirement complete fields for IGS_AS_SU_SETATMPT.
139 FUNCTION enrp_val_susa_cmplt(
140 p_rqrmnts_complete_dt IN DATE ,
141 p_rqrmnts_complete_ind IN VARCHAR2,
142 p_student_confirmed_ind IN VARCHAR2,
143 p_message_name OUT NOCOPY VARCHAR2,
144 p_legacy IN VARCHAR2)
145 RETURN BOOLEAN AS
146
147 BEGIN -- enrp_val_susa_cmplt
148 -- This module validates the requirements complete fields
149 -- associated with the IGS_AS_SU_SETATMPT:
150 -- - If the rqrmnts_complete_ind is set, then the rqrmnts_complete_dt
151 -- must also be set and visa versa.
152 -- - rqrmnts_complete_dt and rqrmnts_complete_ind fields can only be
153
154 -- set if the student_confirmed_ind is also set.
155 DECLARE
156 BEGIN
157 p_message_name := NULL;
158
159 -- If the rqrmnts_complete_ind is set, then the rqrmnts_complete_dt
160 -- must also be set and visa versa.
161 IF (p_rqrmnts_complete_ind = 'Y' AND
162 p_rqrmnts_complete_dt IS NULL) THEN
163 p_message_name := 'IGS_EN_COMPL_DT_SET_COMPL_IND';
164
165 IF (p_legacy = 'Y') THEN
166 -- Add excep to stack
167 FND_MESSAGE.Set_Name('IGS',p_message_name);
168 FND_MSG_PUB.Add;
169 ELSE
170 RETURN FALSE;
171 END IF;
172
173 END IF;
174
175 IF (p_rqrmnts_complete_ind = 'N' AND
176 p_rqrmnts_complete_dt IS NOT NULL) THEN
177 p_message_name := 'IGS_EN_COMPLDT_NOTBE_SET_COMP';
178
179 IF (p_legacy = 'Y') THEN
180 -- Add excep to stack
181 FND_MESSAGE.Set_Name('IGS',p_message_name);
182 FND_MSG_PUB.Add;
183 ELSE
184 RETURN FALSE;
185 END IF;
186
187 END IF;
188
189 -- rqrmnts_complete_dt and rqrmnts_complete_ind fields can only be
190 -- set if the student_confirmed_ind is also set.
191 IF (p_rqrmnts_complete_ind = 'Y' AND
192 p_student_confirmed_ind = 'N') THEN
193 p_message_name := 'IGS_EN_SU_SET_MUSTBE_CONFIRME';
194
195 IF (p_legacy = 'Y') THEN
196 -- Add excep to stack
197 FND_MESSAGE.Set_Name('IGS',p_message_name);
198 FND_MSG_PUB.Add;
199 ELSE
200 RETURN FALSE;
201 END IF;
202
203 END IF;
204
205 RETURN TRUE;
206
207 END;
208 EXCEPTION
209 WHEN OTHERS THEN
210 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
211 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_cmplt');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214
215 END enrp_val_susa_cmplt;
216
217
218
219 FUNCTION ENRP_VAL_SUSA_COUSR(
220 p_person_id IN NUMBER ,
221 p_course_cd IN VARCHAR2 ,
222 p_unit_set_cd IN VARCHAR2 ,
223 p_us_version_number IN NUMBER ,
224 p_parent_unit_set_cd IN VARCHAR2 ,
225 p_parent_sequence_number IN NUMBER ,
226 p_message_type IN VARCHAR2 ,
227 p_message_name OUT NOCOPY VARCHAR2,
228 p_legacy IN VARCHAR2)
229 RETURN BOOLEAN AS
230 gv_other_detail VARCHAR2(255);
231 BEGIN -- enrp_val_susa_cousr
232 -- Validates that the IGS_EN_UNIT_SET being allocated to the IGS_AS_SU_SETATMPT
233 -- is:
234
235 -- * If the unit set is a subordinate within the IGS_PS_OF_UNT_SET_RL
236 -- table, then it must be specified as a child of one of the superior
237 -- units.
238 -- * If the unit set is being specified as a child of another
239 -- IGS_AS_SU_SETATMPT, then then parent unit set version must be
240 -- permitted within the course_off_unit_set_relationship entries
241 DECLARE
242 v_ver_no IGS_EN_STDNT_PS_ATT.version_number%TYPE;
243 v_susa_us_version_no IGS_AS_SU_SETATMPT.us_version_number%TYPE;
244 v_cal_type IGS_EN_STDNT_PS_ATT.cal_type%TYPE;
245 v_only_as_sub_ind IGS_PS_OFR_UNIT_SET.only_as_sub_ind%TYPE;
246 v_check VARCHAR2(1) := NULL;
247 -- Determine if the student's course offering and the course version
248 CURSOR c_sca IS
249 SELECT sca.version_number,
250 sca.cal_type
251 FROM IGS_EN_STDNT_PS_ATT sca
252 WHERE sca.person_id = p_person_id AND
253 sca.course_cd = p_course_cd;
254 CURSOR c_susa IS
255 SELECT susa.us_version_number
256 FROM IGS_AS_SU_SETATMPT susa
257 WHERE susa.person_id = p_person_id AND
258
259 susa.course_cd = p_course_cd AND
260 susa.unit_set_cd = p_parent_unit_set_cd AND
261 susa.sequence_number = p_parent_sequence_number;
262 CURSOR c_cousr(
263 cp_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE,
264 cp_cal_type IGS_EN_STDNT_PS_ATT.cal_type%TYPE,
265 cp_susa_us_version_number IGS_AS_SU_SETATMPT.us_version_number%TYPE) IS
266 SELECT 'x'
267 FROM IGS_PS_OF_UNT_SET_RL cousr
268 WHERE cousr.course_cd = p_course_cd AND
269 cousr.crv_version_number = cp_version_number AND
270 cousr.cal_type = cp_cal_type AND
271 cousr.sub_unit_set_cd = p_unit_set_cd AND
272 cousr.sub_us_version_number =p_us_version_number AND
273 cousr.sup_unit_set_cd = p_parent_unit_set_cd AND
274 cousr.sup_us_version_number = cp_susa_us_version_number;
275 CURSOR c_cousr2(
276 cp_us_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE,
277 cp_cal_type IGS_EN_STDNT_PS_ATT.cal_type%TYPE) IS
278 SELECT 'x'
279 FROM IGS_PS_OF_UNT_SET_RL cousr
280 WHERE cousr.course_cd = p_course_cd AND
281 cousr.crv_version_number = cp_us_version_number AND
282
283 cousr.cal_type = cp_cal_type AND
284 cousr.sub_unit_set_cd = p_unit_set_cd AND
285 cousr.sub_us_version_number = p_us_version_number;
286 BEGIN
287 p_message_name := NULL;
288
289 OPEN c_sca;
290 FETCH c_sca INTO v_ver_no,
291 v_cal_type;
292 IF (c_sca%NOTFOUND) THEN
293 CLOSE c_sca;
294 RAISE NO_DATA_FOUND;
295 END IF;
296 CLOSE c_sca;
297
298 -- Validate that if the unit set is being specified as a child of another unit
299 -- set, then the parent unit set version must be permitted within the
300 -- IGS_PS_OF_UNT_SET_RL entries
301 IF (p_parent_unit_set_cd IS NOT NULL OR
302 p_parent_sequence_number IS NOT NULL) THEN
303 OPEN c_susa;
304
305 FETCH c_susa INTO v_susa_us_version_no;
306 IF (c_susa%NOTFOUND) THEN
307 p_message_name := 'IGS_EN_UNIT_SET_NOT_PARENT_EX';
308
309 IF (p_legacy = 'Y') THEN
310 -- Add excep to stack
311 FND_MESSAGE.Set_Name('IGS',p_message_name);
312 FND_MSG_PUB.Add;
313 ELSE
314 CLOSE c_susa;
315 RETURN FALSE;
316 END IF;
317 END IF;
318 CLOSE c_susa;
319
320 OPEN c_cousr (
321 v_ver_no,
322 v_cal_type,
323 v_susa_us_version_no);
324 FETCH c_cousr INTO v_check;
325 IF (c_cousr%NOTFOUND) THEN
326 p_message_name := 'IGS_EN_UNIT_SET_RELATIONSHIP';
327
328 IF (p_legacy = 'Y') THEN
329 -- Add excep to stack
330 FND_MESSAGE.Set_Name('IGS',p_message_name);
331 FND_MSG_PUB.Add;
332 ELSE
333 CLOSE c_cousr;
334 RETURN FALSE;
335 END IF;
336 END IF;
337 CLOSE c_cousr;
338
339 ELSE
340 -- (p_parent_unit_set_cd IS NULL OR p_parent_sequence_number IS NULL)
341 -- Validate that if the IGS_EN_UNIT_SET is defined as a subordinate within
342 -- the IGS_PS_OF_UNT_SET_RL table, then the parent details must be
343
344 -- specified.
345 OPEN c_cousr2(
346 v_ver_no,
347 v_cal_type);
348 FETCH c_cousr2 INTO v_check;
349 IF (c_cousr2%NOTFOUND) THEN
350 CLOSE c_cousr2;
351 p_message_name := NULL;
352 RETURN TRUE;
353 ELSE
354 IF p_message_type = 'W' THEN
355 -- Return the warning message.
356 p_message_name := 'IGS_EN_UNITSET_HAVE_ONE_PAREN';
357
358 IF (p_legacy = 'Y') THEN
359 -- Add excep to stack
360 FND_MESSAGE.Set_Name('IGS',p_message_name);
361 FND_MSG_PUB.Add;
362 END IF;
363 ELSE
364 -- Return the error message.
365 p_message_name := 'IGS_EN_UNIT_SET_PARENT_UNITSE';
366
367 IF (p_legacy = 'Y') THEN
368 -- Add excep to stack
369 FND_MESSAGE.Set_Name('IGS',p_message_name);
370 FND_MSG_PUB.Add;
371 END IF;
372 END IF;
373 CLOSE c_cousr2;
374
375 RETURN FALSE;
376 END IF;
377 CLOSE c_cousr2;
378 END IF;
379 -- If processing successful then
380
381 RETURN TRUE;
382 EXCEPTION
383 WHEN OTHERS THEN
384 IF (c_sca%ISOPEN) THEN
385 CLOSE c_sca;
386 END IF;
387 IF (c_susa%ISOPEN) THEN
388 CLOSE c_susa;
389 END IF;
390 IF (c_cousr2%ISOPEN) THEN
391 CLOSE c_cousr2;
392 END IF;
393 IF (c_cousr%ISOPEN) THEN
394 CLOSE c_cousr;
395 END IF;
396 RAISE;
397 END;
398 EXCEPTION
399 WHEN OTHERS THEN
400 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
401 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_cousr');
402 IGS_GE_MSG_STACK.ADD;
403 App_Exception.Raise_Exception;
404
405 END enrp_val_susa_cousr;
406
407
408 --
409 -- Validate the student unit set attempt is able to be deleted.
410 FUNCTION ENRP_VAL_SUSA_DEL(
411 p_person_id IN NUMBER ,
412 p_course_cd IN VARCHAR2 ,
413 p_unit_set_cd IN VARCHAR2 ,
414 p_sequence_number IN NUMBER ,
415 p_us_version_number IN NUMBER ,
416 p_end_dt IN DATE ,
417 p_rqrmnts_complete_ind IN VARCHAR2,
418 p_db_trg_call IN VARCHAR2 ,
419 p_message_name OUT NOCOPY VARCHAR2 )
420 RETURN BOOLEAN AS
421 gv_other_detail VARCHAR2(255);
422 BEGIN -- enrp_val_susa_del
423 -- This module validates that the IGS_AS_SU_SETATMPT record being
424 -- deleted meets the following conditions:
425 -- - Cannot be deleted if the unit set has been completed.
426 -- - Cannot be deleted if the unit set has been ended.
427
428 -- - Cannot be deleted if it is the parent of another unit set.
429 -- - Cannot be deleted if it is part of the terms and conditions
430 -- of the admissions offer for the student.
431 DECLARE
432 v_dummy VARCHAR2(1);
433 CURSOR c_susa IS
434 SELECT 'X'
435 FROM IGS_AS_SU_SETATMPT susa
436 WHERE susa.person_id = p_person_id AND
437 susa.course_cd = p_course_cd AND
438 susa.parent_unit_set_cd = p_unit_set_cd AND
439 susa.parent_sequence_number = p_sequence_number;
440 CURSOR c_acai_sca IS
441 SELECT acai.unit_set_cd,
442 acai.us_version_number
443 FROM IGS_AD_PS_APPL_INST acai,
444 IGS_EN_STDNT_PS_ATT sca
445 WHERE sca.person_id = p_person_id AND
446 sca.course_cd = p_course_cd AND
447 acai.person_id = sca.person_id AND
448 acai.admission_appl_number = sca.adm_admission_appl_number AND
449 acai.nominated_course_cd = sca.adm_nominated_course_cd AND
450 acai.sequence_number = sca.adm_sequence_number;
451
452 BEGIN
453 -- Cannot be deleted if the unit set has been completed.
454 IF (p_rqrmnts_complete_ind = 'Y') THEN
455 p_message_name := 'IGS_EN_NOTDEL_UNITSET_COMPL';
456 RETURN FALSE;
457 END IF;
458 -- Cannot be deleted if the unit set has been ended.
459 IF (p_end_dt IS NOT NULL) THEN
460 p_message_name := 'IGS_EN_NOTDEL_UNITSET_ENDED';
461 RETURN FALSE;
462 END IF;
463 -- Check if validation called from the database trigger. If yes, then
464 -- do not execute this query as will cause mutating trigger. The error
465 -- will be trapped by the RI constraints anyway.
466 IF p_db_trg_call = 'N' THEN
467 -- Cannot be deleted if it is the parent of another IGS_EN_UNIT_SET.
468 OPEN c_susa; -- Althogh handled by RI constraints, required for enrp_val_susa.
469 FETCH c_susa INTO v_dummy;
470 IF (c_susa%FOUND) THEN
471 CLOSE c_susa;
472 p_message_name := 'IGS_EN_NOTDEL_UNITSET_PARENT';
473 RETURN FALSE;
474 END IF;
475
476 CLOSE c_susa;
477 END IF;
478 -- Cannot be deleted if it is part of the terms and conditions
479 -- of the admissions offer for the student.
480 -- Determine if unit set is part of the admissions offer
481 IF igs_as_val_suaap.genp_val_sdtt_sess('ADMP_DEL_SCA_UNCONF') THEN
482 FOR v_acai_sca_rec IN c_acai_sca LOOP
483 IF (v_acai_sca_rec.unit_set_cd = p_unit_set_cd AND
484 v_acai_sca_rec.us_version_number = p_us_version_number) THEN
485 p_message_name := 'IGS_EN_NOTDEL_UNITSET_COND';
486 RETURN FALSE;
487 END IF;
488 END LOOP;
489 END IF;
490 p_message_name := NULL;
491 RETURN TRUE;
492 END;
493 EXCEPTION
494 WHEN OTHERS THEN
495 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
496 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_del');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499
500
501 END enrp_val_susa_del;
502 --
503 -- Validate the date fields associated with a student unit set attempt.
504 FUNCTION ENRP_VAL_SUSA_DTS(
505 p_selection_dt IN DATE ,
506 p_end_dt IN DATE ,
507 p_rqrmnts_complete_dt IN DATE ,
508 p_message_name OUT NOCOPY VARCHAR2 )
509 /* -------------------------------------------------------------------------------------------
510 --Change History:
511 --Who When What
512 --svanukur 12-sep-03 Removed the validation of selection date being greater than
513 -- sysdate as perbug 3106879 to allow selection date to be future dated.
514 -------------------------------------------------------------------------------------------*/
515
516 RETURN BOOLEAN AS
517 gv_other_detail VARCHAR2(255);
518 BEGIN -- enrp_val_susa_dts
519 -- This module validates the date fields associated with the
520 -- IGS_AS_SU_SETATMPT:
521 -- . If end_dt and selection_dt set, then end_dt >= selection_dt.
522 -- . end_dt and rqrmnts_complete_dt cannot both be set.
523 -- . Selection_dt, end_dt, rqrmnts_complete_dt cannot be future dated.
524 -- . If rqrmnts_complete_dt and selection_dt set,
525 -- then rqrmnts_complete_dt >= selection_dt.
526 DECLARE
527 v_sysdate DATE;
528 BEGIN
529 -- If end_dt and selection_dt set, then end_dt >= selection_dt.
530
531 IF p_end_dt IS NOT NULL AND
532 p_selection_dt IS NOT NULL THEN
533 IF p_end_dt < p_selection_dt THEN
534 p_message_name := 'IGS_EN_ENDDT_NOTBE_EARLIER_DT';
535 RETURN FALSE;
536 END IF;
537 END IF;
538 -- Validate that end_dt and rqrmnts_complete_dt cannot both be set.
539 IF p_end_dt IS NOT NULL AND
540 p_rqrmnts_complete_dt IS NOT NULL THEN
541 p_message_name := 'IGS_EN_ENDDT_COMPLDT_NOTSET';
542 RETURN FALSE;
543 END IF;
544 -- end_dt, rqrmnts_complete_dt cannot be future dated.
545 v_sysdate := TRUNC(SYSDATE);
546
547 IF p_end_dt IS NOT NULL THEN
548 IF TRUNC(p_end_dt) > v_sysdate THEN
549
550 p_message_name := 'IGS_EN_ENDDT_LE_CURR_DT';
551 RETURN FALSE;
552 END IF;
553 END IF;
554 IF p_rqrmnts_complete_dt IS NOT NULL THEN
555 IF TRUNC(p_rqrmnts_complete_dt) > v_sysdate THEN
556 p_message_name := 'IGS_EN_COMPLDT_LE_CURR_DT';
557 RETURN FALSE;
558 END IF;
559 END IF;
560 -- If rqrmnts_complete_dt and selection_dt set,
561 -- then rqrmnts_complete_dt >= selection_dt.
562 IF p_rqrmnts_complete_dt IS NOT NULL AND
563 p_selection_dt IS NOT NULL THEN
564 IF p_rqrmnts_complete_dt < p_selection_dt THEN
565 p_message_name := 'IGS_EN_COMPLDT_GE_CURR_DT';
566 RETURN FALSE;
567 END IF;
568 END IF;
569 -- If processing successful then
570 p_message_name := NULL;
571 RETURN TRUE;
572 END;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
577 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_dts');
578 IGS_GE_MSG_STACK.ADD;
579 App_Exception.Raise_Exception;
580
581 END enrp_val_susa_dts;
582
583
584 --
585 -- Validate the student unit set attempt end date.
586 FUNCTION ENRP_VAL_SUSA_END_DT(
587 p_person_id IN NUMBER ,
588 p_course_cd IN VARCHAR2 ,
589 p_unit_set_cd IN VARCHAR2 ,
590 p_sequence_number IN NUMBER ,
591 p_us_version_number IN NUMBER ,
592 p_end_dt IN DATE ,
593 p_authorised_person_id IN NUMBER ,
594 p_authorised_on IN DATE ,
595 p_parent_unit_set_cd IN VARCHAR2 ,
596 p_parent_sequence_number IN NUMBER ,
597 p_message_type IN VARCHAR2 ,
598 p_message_name OUT NOCOPY VARCHAR2,
599 p_legacy IN VARCHAR2)
600 RETURN BOOLEAN AS
601
602 BEGIN -- enrp_val_susa_end_dt
603 -- This module validates that the end date being altered for the
604 -- IGS_AS_SU_SETATMPT meets the following conditions:
605 -- . If the end date is being set and was specified as part of the students
606 -- offer (the admissions offer relating to the course attempt contains the
607 -- unit set), then the authorise person and authorise on fields must be
608 -- set. (If fields already set, then return a warning message.)
609 -- . Only one record within the student course attempt unit set can have an
610 -- open end date.
611 -- . Cannot be unset if the parent record is ended.
612 DECLARE
613 v_dummy VARCHAR2(1);
614 v_found BOOLEAN;
615 CURSOR c_susa IS
616 SELECT 'x'
617 FROM IGS_AS_SU_SETATMPT susa
618 WHERE susa.person_id = p_person_id AND
619 susa.course_cd = p_course_cd AND
620 susa.unit_set_cd = p_unit_set_cd AND
621 susa.end_dt IS NULL AND
622 susa.sequence_number <> NVL(p_sequence_number, 0);
623
624 CURSOR c_susa_parent IS
625 SELECT 'x'
626 FROM IGS_AS_SU_SETATMPT susa
627 WHERE susa.person_id = p_person_id AND
628 susa.course_cd = p_course_cd AND
629 susa.unit_set_cd = p_parent_unit_set_cd AND
630 susa.sequence_number = p_parent_sequence_number AND
631 susa.end_dt IS NOT NULL;
632 CURSOR c_chk_us IS
633 SELECT acai.unit_set_cd,
634 acai.us_version_number
635 FROM IGS_AD_PS_APPL_INST acai,
636 IGS_EN_STDNT_PS_ATT sca
637 WHERE sca.person_id = p_person_id AND
638 sca.course_cd = p_course_cd AND
639 sca.person_id = acai.person_id AND
640 sca.adm_admission_appl_number = acai.admission_appl_number AND
641 sca.adm_nominated_course_cd = acai.nominated_course_cd AND
642 sca.adm_sequence_number = acai.sequence_number;
643 BEGIN
644
645 v_found := FALSE;
646
647 -- set default value
648 p_message_name := NULL;
649 IF p_end_dt IS NULL THEN
650
651 -- Only one record within the student course attempt unit set can have an
652 -- open end date irrespective of version number.
653 OPEN c_susa;
654 FETCH c_susa INTO v_dummy;
655 IF c_susa%FOUND THEN
656 p_message_name := 'IGS_EN_UNIT_SET_EXISTS';
657
658 IF (p_legacy = 'Y') THEN
659 -- Add excep to stack
660 FND_MESSAGE.Set_Name('IGS',p_message_name);
661 FND_MSG_PUB.Add;
662 ELSE
663 CLOSE c_susa;
664 RETURN FALSE;
665 END IF;
666 END IF;
667 CLOSE c_susa;
668
669 -- If end date cleared, validate parent is not ended.
670 IF p_parent_unit_set_cd IS NOT NULL AND
671 p_parent_sequence_number IS NOT NULL THEN
672 OPEN c_susa_parent;
673 FETCH c_susa_parent INTO v_dummy;
674 IF c_susa_parent%FOUND THEN
675 p_message_name := 'IGS_EN_UNIT_SET_NO_OPEN';
676
677 IF (p_legacy = 'Y') THEN
678 -- Add excep to stack
679 FND_MESSAGE.Set_Name('IGS',p_message_name);
680 FND_MSG_PUB.Add;
681 ELSE
682 CLOSE c_susa_parent;
683 RETURN FALSE;
684 END IF;
685 END IF;
686 CLOSE c_susa_parent;
687
688 END IF;
689 ELSE -- p_end_dt IS NOT NULL
690
691 -- Validate that if the end date is being set and was specified as part of
692 -- the students offer (the admissions offer relating to the course attempt
693 -- contains the unit set), then the authorise person and authorise on fields
694 -- must be set. (If fields already set, then return a warning message.)
695 -- Determine if unit set is part of the admissions offer.
696 FOR v_chk_us_rec IN c_chk_us LOOP
697 IF v_chk_us_rec.unit_set_cd = p_unit_set_cd AND
698 v_chk_us_rec.us_version_number = p_us_version_number THEN
699 v_found := TRUE;
700 EXIT;
701 END IF;
702 END LOOP;
703 IF v_found = TRUE THEN
704 -- If authorise details not set then return an error/warning.
705 -- otherwise if set, then return warning.
706 IF p_authorised_person_id IS NULL AND
707 p_authorised_on IS NULL THEN
708 IF p_message_type = 'W' THEN
709 -- Return warning.
710 p_message_name := 'IGS_EN_UNITSET_REQ_AUTHORISAT';
711 RETURN FALSE;
712 ELSE
713 p_message_name := 'IGS_EN_UNITSET_REQ_ENDED';
714
715 IF (p_legacy = 'Y') THEN
716 FND_MESSAGE.Set_Name('IGS',p_message_name);
717 FND_MSG_PUB.Add;
718 ELSE
719 RETURN FALSE;
720 END IF;
721 END IF;
722 ELSE
723 -- Execute only in non-legacy mode
724 IF (p_legacy <> 'Y') THEN
725 -- Return warning.
726 p_message_name := 'IGS_EN_UNITSET_REQ_AUTHORISAT';
727 RETURN FALSE;
728 END IF;
729
730 END IF;
731 END IF;
732 END IF; -- if p_end_dt is null
733 -- If processing successful then
734 RETURN TRUE;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 IF c_susa%ISOPEN THEN
739 CLOSE c_susa;
740 END IF;
741 RAISE;
742 END;
743 EXCEPTION
744 WHEN OTHERS THEN
745 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
746 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_end_dt');
747 IGS_GE_MSG_STACK.ADD;
748 App_Exception.Raise_Exception;
749
750 END enrp_val_susa_end_dt;
751
752
753 --
754 -- Validate student unit set atmpt voluntary end indicator and end date.
755 FUNCTION ENRP_VAL_SUSA_END_VI(
756 p_voluntary_end_ind IN VARCHAR2,
757 p_end_dt IN DATE ,
758 p_message_name OUT NOCOPY VARCHAR2 )
759 RETURN BOOLEAN AS
760
761 BEGIN -- enrp_val_susa_end_vi
762 -- This module validates the voluntary_end_ind against the end_dt for a
763 -- IGS_AS_SU_SETATMPT record. The voluntary end indicator can only
764 -- be set if the end date is set, although it is not mandatory that it is set.
765 DECLARE
766 BEGIN
767 -- Validate the end date must be set if the voluntary end indicator is set
768 IF (p_voluntary_end_ind = 'Y' AND
769 p_end_dt IS NULL) THEN
770 p_message_name := 'IGS_EN_VOLUNTARY_END_INDICATO';
771 RETURN FALSE;
772 END IF;
773 -- If processing successful then
774
775 p_message_name := NULL;
776 RETURN TRUE;
777 END;
778 EXCEPTION
779 WHEN OTHERS THEN
780 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
781 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_end_vi');
782 IGS_GE_MSG_STACK.ADD;
783 App_Exception.Raise_Exception;
784 END enrp_val_susa_end_vi;
785 --
786 -- Validate the student unit set attempt is able to be created.
787 FUNCTION ENRP_VAL_SUSA_INS(
788 p_person_id IN NUMBER ,
789 p_course_cd IN VARCHAR2 ,
790 p_unit_set_cd IN VARCHAR2 ,
791 p_sequence_number IN NUMBER ,
792 p_us_version_number IN NUMBER ,
793 p_message_name OUT NOCOPY VARCHAR2,
794 p_legacy IN VARCHAR2)
795 RETURN BOOLEAN AS
796 -------------------------------------------------------------------------------------------
797 --Change History:
798 --Who When What
799 --stutta 06-Mar-2006 Split cursor c_susv to 3 cursors cur_susa, c_uso,c_usoo for
800 -- perf bug #3696127
801 BEGIN -- enrp_val_susa_ins
802 -- This module validates that the IGS_AS_SU_SETATMPT record being
803
804 -- created meets the following conditions:
805 -- . unit set version must be applicable to the course offering or the student
806 -- must have had it previously selected.
807 -- . Cannot be created against a unit set that has previously been completed
808 -- by the student (irrespective of version).
809 DECLARE
810 v_dummy VARCHAR2(1);
811 v_msg_num NUMBER(5);
812
813
814 CURSOR c_uso IS
815 SELECT 'X'
816 FROM IGS_PS_OFR_UNIT_SET cous,
817 igs_en_unit_set_stat uss1,
818 IGS_EN_UNIT_SET_ALL US,
819 IGS_EN_STDNT_PS_ATT spa
820 WHERE spa.person_id = p_person_id
821 AND spa.course_cd = p_course_cd
822 AND us.version_number = p_us_version_number
823 AND us.unit_set_cd = p_unit_set_cd
824 AND spa.course_cd = cous.course_cd
825 AND spa.version_number = cous.crv_version_number
826 AND spa.CAL_TYPE = cous.CAL_TYPE
827 AND us.unit_set_cd = cous.unit_set_cd
828 AND us.version_number = cous.us_version_number
829 AND us.unit_set_status = uss1.unit_set_status
830 AND uss1.s_unit_set_status ='ACTIVE'
831 AND NOT EXISTS (SELECT 1
832 FROM IGS_PS_OF_OPT_UNT_ST coous
833 WHERE coous.coo_id= spa.coo_id
834 );
835
836
837 CURSOR c_usoo IS
838 SELECT 'X'
839 FROM IGS_PS_OF_OPT_UNT_ST coous,
840 IGS_EN_UNIT_SET_ALL US,
841 igs_en_unit_set_stat uss1,
842 IGS_EN_STDNT_PS_ATT spa
843 WHERE spa.person_id = p_person_id
844 AND spa.course_cd = p_course_cd
845 AND coous.coo_id = spa.coo_id
846 AND us.version_number = p_us_version_number
847 AND us.unit_set_cd = p_unit_set_cd
848 AND us.unit_set_cd = coous.unit_set_cd
849 AND us.version_number = coous.us_version_number
850 AND us.unit_set_status = uss1.unit_set_status
851 AND uss1.s_unit_set_status = 'ACTIVE';
852
853 CURSOR c_susa IS
854 SELECT 'X'
855 FROM IGS_AS_SU_SETATMPT susa
856 WHERE susa.person_id = p_person_id AND
857 susa.course_cd = p_course_cd AND
858 susa.unit_set_cd = p_unit_set_cd AND
859 susa.sequence_number<> NVL(p_sequence_number, 0) AND
860 susa.rqrmnts_complete_ind = 'Y';
861
862 BEGIN
863 p_message_name := NULL;
864
865 -- Validate that the unit set version must be applicable to the course
866 -- offering option
867 v_dummy := NULL;
868 OPEN c_usoo;
869 FETCH c_usoo INTO v_dummy;
870 CLOSE c_usoo;
871 IF v_dummy IS NULL THEN -- c_usoo not found
872 --
873 v_dummy := NULL;
874 OPEN c_uso;
875 FETCH c_uso INTO v_dummy;
876 CLOSE c_uso;
877 IF v_dummy IS NULL THEN -- c_uso is not found
878 p_message_name := 'IGS_EN_UNIT_SETNOT_PERMITTED';
879 IF (p_legacy = 'Y') THEN
880 -- Add excep to stack
881 FND_MESSAGE.Set_Name('IGS',p_message_name);
882 FND_MSG_PUB.Add;
883 ELSE
884 RETURN FALSE;
885 END IF;
886 END IF;
887
888 END IF;
889
890 -- The student cannot create another attempt at a unit set if a record within
891 -- the student course attempt exists as completed.
892 v_dummy := NULL;
893 OPEN c_susa;
894 FETCH c_susa INTO v_dummy;
895 IF c_susa%FOUND THEN
896 p_message_name := 'IGS_EN_STUD_COMPL_UNITSET';
897
898 IF (p_legacy = 'Y') THEN
899 -- Add excep to stack
900 FND_MESSAGE.Set_Name('IGS',p_message_name);
901 FND_MSG_PUB.Add;
902 ELSE
903 CLOSE c_susa;
904 RETURN FALSE;
905 END IF;
906
907 END IF;
908 CLOSE c_susa;
909 -- If processing successful then
910
911 RETURN TRUE;
912 EXCEPTION
913 WHEN OTHERS THEN
914 IF c_uso%ISOPEN THEN
915 CLOSE c_uso;
916 END IF;
917 IF c_usoo%ISOPEN THEN
918 CLOSE c_usoo;
919 END IF;
920 IF c_susa%ISOPEN THEN
921 CLOSE c_susa;
922 END IF;
923 RAISE;
924 END;
925 EXCEPTION
926 WHEN OTHERS THEN
927 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
928 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_ins');
929 IGS_GE_MSG_STACK.ADD;
930 App_Exception.Raise_Exception;
931 END enrp_val_susa_ins;
932
933
934 --
935 -- Validate the linking of parent unit set to student unit set attempt .
936 FUNCTION ENRP_VAL_SUSA_PARENT(
937 p_person_id IN NUMBER ,
938 p_course_cd IN VARCHAR2 ,
939 p_unit_set_cd IN VARCHAR2 ,
940 p_sequence_number IN NUMBER ,
941 p_parent_unit_set_cd IN VARCHAR2 ,
942 p_parent_sequence_number IN NUMBER ,
943 p_student_confirmed_ind IN VARCHAR2,
944 p_message_name OUT NOCOPY VARCHAR2,
945 p_legacy IN VARCHAR2)
946 RETURN BOOLEAN AS
947
948 BEGIN -- enrp_val_susa_parent
949 -- This module validates the IGS_EN_UNIT_SET being allocated to the
950 -- IGS_AS_SU_SETATMPT as a parent:
951 -- - Cannot be a parent unit set attempt of itself.
952 -- - Can only be linked to a IGS_AS_SU_SETATMPT record for
953 -- the same person and course where the end date is null.
954 -- - Cannot be linked to itself via the parent student unit set
955 -- attempt relationship.
956 -- - Cannot be linked to a confirmed parent if the unit set is
957 -- unconfirmed.
958 DECLARE
959 v_end_dt IGS_AS_SU_SETATMPT.end_dt%TYPE;
960 v_student_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
961 v_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE;
962
963 CURSOR c_susa IS
964 SELECT susa.end_dt,
965 susa.student_confirmed_ind
966 FROM IGS_AS_SU_SETATMPT susa
967 WHERE susa.person_id = p_person_id AND
968 susa.course_cd = p_course_cd AND
969 susa.unit_set_cd = p_parent_unit_set_cd AND
970 susa.sequence_number = p_parent_sequence_number;
971 CURSOR c_susa_ancestor IS
972 SELECT susa1.unit_set_cd
973 FROM IGS_AS_SU_SETATMPT susa1
974 START WITH susa1.person_id = p_person_id AND
975 susa1.course_cd = p_course_cd AND
976 susa1.unit_set_cd = p_parent_unit_set_cd AND
977 susa1.sequence_number = p_parent_sequence_number
978 CONNECT BY PRIOR susa1.person_id = susa1.person_id AND
979 PRIOR susa1.course_cd = susa1.course_cd AND
980 PRIOR susa1.parent_unit_set_cd = susa1.unit_set_cd AND
981 PRIOR susa1.parent_sequence_number = susa1.sequence_number;
982 CURSOR c_susa_descendant IS
983 SELECT susa1.unit_set_cd
984 FROM IGS_AS_SU_SETATMPT susa1
985 START WITH susa1.person_id = p_person_id AND
986
987 susa1.course_cd = p_course_cd AND
988 susa1.unit_set_cd = p_unit_set_cd AND
989 susa1.sequence_number = NVL(p_sequence_number, 0)
990 CONNECT BY PRIOR susa1.person_id = susa1.person_id AND
991 PRIOR susa1.course_cd = susa1.course_cd AND
992 PRIOR susa1.unit_set_cd = susa1.parent_unit_set_cd AND
993 PRIOR susa1.sequence_number = susa1.parent_sequence_number;
994
995 BEGIN
996 p_message_name := NULL;
997
998 -- Check that a parent is being defined
999 IF (p_parent_unit_set_cd IS NULL OR
1000 p_parent_sequence_number IS NULL) THEN
1001 p_message_name := NULL;
1002 RETURN TRUE;
1003 END IF;
1004
1005 -- Validate the unit set is not being specified as a
1006 -- parent of itself irrespective of version.
1007 IF (p_unit_set_cd = p_parent_unit_set_cd) THEN
1008 p_message_name := 'IGS_EN_UNIT_SET_NOTBE_PARENT';
1009
1010 IF (p_legacy = 'Y') THEN
1011 -- Add excep to stack
1012 FND_MESSAGE.Set_Name('IGS',p_message_name);
1013 FND_MSG_PUB.Add;
1014 ELSE
1015 RETURN FALSE;
1016 END IF;
1017 END IF;
1018
1019 -- Validate can only be linked to a IGS_AS_SU_SETATMPT record for the
1020 -- same person and course where the end date is null.
1021 OPEN c_susa;
1022 FETCH c_susa INTO v_end_dt, v_student_confirmed_ind;
1023 IF (c_susa%NOTFOUND) THEN
1024 CLOSE c_susa;
1025 p_message_name := 'IGS_EN_UNIT_SET_NOT_PARENT_EX';
1026
1027 IF (p_legacy = 'Y') THEN
1028 -- Add excep to stack
1029 FND_MESSAGE.Set_Name('IGS',p_message_name);
1030 FND_MSG_PUB.Add;
1031 ELSE
1032 RETURN FALSE;
1033 END IF;
1034 ELSE
1035 CLOSE c_susa;
1036 -- Check if the end date is set
1037 IF (v_end_dt IS NOT NULL) THEN
1038 p_message_name := 'IGS_EN_UNIT_SET_NOT_ENDDT';
1039
1040 IF (p_legacy = 'Y') THEN
1041 -- Add excep to stack
1042 FND_MESSAGE.Set_Name('IGS',p_message_name);
1043 FND_MSG_PUB.Add;
1044 ELSE
1045 RETURN FALSE;
1046 END IF;
1047 END IF;
1048
1049 -- Validate that if the unit set is confirmed,
1050 -- then the parent must also be confirmed.
1051 IF (v_student_confirmed_ind = 'N' AND -- parent student confirmed ind
1052 p_student_confirmed_ind = 'Y') THEN -- child student confirmed ind
1053 p_message_name := 'IGS_EN_UNIT_SET_PARENTSET_CON';
1054
1055 IF (p_legacy = 'Y') THEN
1056 -- Add excep to stack
1057 FND_MESSAGE.Set_Name('IGS',p_message_name);
1058 FND_MSG_PUB.Add;
1059 ELSE
1060 RETURN FALSE;
1061 END IF;
1062 END IF;
1063 END IF;
1064 -- Validate that the unit set cannot be linked to itself via the
1065 -- parent relationship.
1066
1067 -- Check that the unit set does not already exist as an ancestor.
1068 OPEN c_susa_ancestor;
1069 FETCH c_susa_ancestor INTO v_unit_set_cd;
1070 LOOP
1071 IF (c_susa_ancestor%NOTFOUND) THEN
1072 EXIT;
1073 END IF;
1074 IF v_unit_set_cd = p_unit_set_cd THEN
1075 p_message_name := 'IGS_EN_INVALID_RELATIONSHIP';
1076
1077 IF (p_legacy = 'Y') THEN
1078 -- Add excep to stack
1079 FND_MESSAGE.Set_Name('IGS',p_message_name);
1080 FND_MSG_PUB.Add;
1081 EXIT;
1082 ELSE
1083 CLOSE c_susa_ancestor;
1084 RETURN FALSE;
1085 END IF;
1086 END IF;
1087 FETCH c_susa_ancestor INTO v_unit_set_cd;
1088 END LOOP;
1089 CLOSE c_susa_ancestor;
1090
1091 -- Check that the unit set does not already exist as a descendant
1092 OPEN c_susa_descendant;
1093 FETCH c_susa_descendant INTO v_unit_set_cd;
1094 LOOP
1095 IF (c_susa_descendant%NOTFOUND) THEN
1096 EXIT;
1097 END IF;
1098 IF v_unit_set_cd = p_parent_unit_set_cd THEN
1099 p_message_name := 'IGS_EN_INVALID_RELATIONSHIP';
1100
1101 IF (p_legacy = 'Y') THEN
1102 -- Add excep to stack
1103 FND_MESSAGE.Set_Name('IGS',p_message_name);
1104 FND_MSG_PUB.Add;
1105 EXIT;
1106 ELSE
1107 CLOSE c_susa_descendant;
1108 RETURN FALSE;
1109 END IF;
1110 END IF;
1111 FETCH c_susa_descendant INTO v_unit_set_cd;
1112 END LOOP;
1113 CLOSE c_susa_descendant;
1114
1115 RETURN TRUE;
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 IF (c_susa%ISOPEN) THEN
1119 CLOSE c_susa;
1120 END IF;
1121 IF (c_susa_ancestor%ISOPEN) THEN
1122 CLOSE c_susa_ancestor;
1123 END IF;
1124 IF (c_susa_descendant%ISOPEN) THEN
1125 CLOSE c_susa_descendant;
1126 END IF;
1127 RAISE;
1128 END;
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1132 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_parent');
1133 IGS_GE_MSG_STACK.ADD;
1134 App_Exception.Raise_Exception;
1135
1136 END enrp_val_susa_parent;
1137
1138 --
1139 -- Validate the IGS_AS_SU_SETATMPT.primary_set_ind field.
1140 FUNCTION ENRP_VAL_SUSA_PRMRY(
1141 p_person_id IN NUMBER ,
1142 p_course_cd IN VARCHAR2 ,
1143 p_unit_set_cd IN VARCHAR2 ,
1144 p_us_version_number IN NUMBER ,
1145 p_primary_set_ind IN VARCHAR2,
1146 p_message_name OUT NOCOPY VARCHAR2,
1147 p_legacy IN VARCHAR2)
1148 RETURN BOOLEAN AS
1149
1150 BEGIN -- enrp_val_susa_prmry
1151 -- This module validates the IGS_AS_SU_SETATMPT.primary_set_ind cannot
1152 -- be set if there exists a unit set of a higher rank within the students
1153 -- unit set. Also, check that the primary indicator is only set for
1154 -- non-administrative files.
1155 DECLARE
1156
1157 v_rank IGS_EN_UNIT_SET_CAT.rank%TYPE;
1158 v_administrative_ind IGS_EN_UNIT_SET.administrative_ind%TYPE;
1159 v_dummy VARCHAR2(1);
1160
1161 CURSOR c_us_usc IS
1162 SELECT us.administrative_ind,
1163 usc.rank
1164 FROM IGS_EN_UNIT_SET us,
1165 IGS_EN_UNIT_SET_CAT usc
1166 WHERE us.unit_set_cd = p_unit_set_cd AND
1167 us.version_number = p_us_version_number AND
1168 us.unit_set_cat = usc.unit_set_cat;
1169 CURSOR c_susa_us_usc (
1170 cp_rank IGS_EN_UNIT_SET_CAT.rank%TYPE) IS
1171 SELECT 'x'
1172 FROM IGS_AS_SU_SETATMPT susa,
1173 IGS_EN_UNIT_SET us,
1174 IGS_EN_UNIT_SET_CAT usc
1175 WHERE susa.person_id = p_person_id AND
1176 susa.course_cd = p_course_cd AND
1177 susa.unit_set_cd = us.unit_set_cd AND
1178 susa.us_version_number = us.version_number AND
1179 us.administrative_ind = 'N' AND
1180 us.unit_set_cat = usc.unit_set_cat AND
1181 usc.rank < cp_rank;
1182 BEGIN
1183 p_message_name := NULL;
1184
1185 -- If primary set indicator not set, return successful
1186 -- as no validation required.
1187 IF NVL(p_primary_set_ind, 'N') = 'N' THEN
1188 p_message_name := NULL;
1189 RETURN TRUE;
1190 END IF;
1191
1192 -- Check if the unit set is administrative.
1193 OPEN c_us_usc;
1194 FETCH c_us_usc INTO v_administrative_ind,
1195 v_rank;
1196 IF (c_us_usc%NOTFOUND) THEN
1197 CLOSE c_us_usc;
1198 RAISE NO_DATA_FOUND;
1199 ELSE
1200 IF (v_administrative_ind = 'Y') THEN
1201 p_message_name := 'IGS_EN_PRIMARY_INDICATOR_NOT';
1202
1203 IF (p_legacy = 'Y') THEN
1204 -- Add excep to stack
1205 FND_MESSAGE.Set_Name('IGS',p_message_name);
1206 FND_MSG_PUB.Add;
1207 ELSE
1208 CLOSE c_us_usc;
1209 RETURN FALSE;
1210 END IF;
1211 END IF;
1212 END IF;
1213 CLOSE c_us_usc;
1214
1215 -- The below validation need not
1216 -- be checked when running in legacy mode
1217 IF (p_legacy <> 'Y') THEN
1218 -- Check if there exists a non-administrative unit which has a higher rank.
1219 OPEN c_susa_us_usc(v_rank);
1220 FETCH c_susa_us_usc INTO v_dummy;
1221 IF (c_susa_us_usc%FOUND) THEN
1222 CLOSE c_susa_us_usc;
1223 p_message_name := 'IGS_EN_PRIMARY_IND_NOT_SET';
1224 RETURN FALSE;
1225 END IF;
1226 -- If processing successful then
1227 CLOSE c_susa_us_usc;
1228 END IF;
1229
1230 RETURN TRUE;
1231 END;
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1235 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_prmry');
1236 IGS_GE_MSG_STACK.ADD;
1237 App_Exception.Raise_Exception;
1238
1239 END enrp_val_susa_prmry;
1240
1241
1242 --
1243 -- Validate the student unit set attempt against for the stdnt crs atmpt.
1244 FUNCTION ENRP_VAL_SUSA_SCA(
1245 p_person_id IN NUMBER ,
1246 p_course_cd IN VARCHAR2 ,
1247 p_message_name OUT NOCOPY VARCHAR2 )
1248 RETURN BOOLEAN AS
1249
1250 BEGIN -- enrp_val_susa_sca
1251 -- This module validates the IGS_AS_SU_SETATMPT is created against
1252 -- a valid IGS_EN_STDNT_PS_ATT status.
1253 DECLARE
1254 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1255 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1256 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
1257 v_dummy VARCHAR2(1);
1258 CURSOR c_sca IS
1259 SELECT 'x'
1260 FROM IGS_EN_STDNT_PS_ATT sca
1261 WHERE sca.person_id = p_person_id AND
1262 sca.course_cd = p_course_cd AND
1263 sca.course_attempt_status IN ( cst_discontin,
1264 cst_lapsed,
1265 cst_deleted);
1266 BEGIN
1267
1268 -- set default value
1269 p_message_name := NULL;
1270 OPEN c_sca;
1271 FETCH c_sca INTO v_dummy;
1272 IF c_sca%FOUND THEN
1273 CLOSE c_sca;
1274 p_message_name := 'IGS_EN_SUA_NOT_CREATED';
1275 RETURN FALSE;
1276 END IF;
1277 CLOSE c_sca;
1278 RETURN TRUE;
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 IF c_sca%ISOPEN THEN
1282 CLOSE c_sca;
1283 END IF;
1284 RAISE;
1285 END;
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1289 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sca');
1290 IGS_GE_MSG_STACK.ADD;
1291
1292 App_Exception.Raise_Exception;
1293 END enrp_val_susa_sca;
1294
1295
1296 --
1297 -- Validate the student unit set attempt confirmation indicator.
1298 FUNCTION ENRP_VAL_SUSA_SCI(
1299 p_person_id IN NUMBER ,
1300 p_course_cd IN VARCHAR2 ,
1301 p_unit_set_cd IN VARCHAR2 ,
1302 p_sequence_number IN NUMBER ,
1303 p_us_version_number IN NUMBER ,
1304 p_parent_unit_set_cd IN VARCHAR2 ,
1305 p_parent_sequence_number IN NUMBER ,
1306 p_student_confirmed_ind IN VARCHAR2,
1307 p_selection_dt IN DATE ,
1308 p_end_dt IN DATE ,
1309 p_rqrmnts_complete_ind IN VARCHAR2,
1310 p_message_name OUT NOCOPY VARCHAR2,
1311 p_legacy IN VARCHAR2)
1312 RETURN BOOLEAN AS
1313
1314 BEGIN -- enrp_val_susa_sci
1315 -- This module validates the confirmation of a IGS_EN_UNIT_SET for a
1316 -- IGS_AS_SU_SETATMPT record. The validations are:
1317 -- - The student confirmed indicator cannot be unset once the
1318
1319 -- end date has been set.
1320 -- - The student confirmed indicator cannot be unset once the
1321 -- requirements complete indicator has been set.
1322 -- - The student confirmed indicator can only be set if the
1323 -- student course attempt status is 'ENROLLED' or 'INACTIVE'.
1324 -- - The student confirmed indicator cannot be set when a parent
1325 -- unit set exists that is unconfirmed.
1326 -- - The student confirmed indicator cannot be set if the student
1327 -- is excluded from the unit set via encumbrances.
1328 DECLARE
1329 cst_enrolled CONSTANT
1330 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
1331 cst_inactive CONSTANT
1332 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
1333 v_dummy VARCHAR2(1);
1334 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1335 v_message_name VARCHAR2(30);
1336 CURSOR c_sca IS
1337 SELECT sca.version_number
1338 FROM IGS_EN_STDNT_PS_ATT sca
1339 WHERE sca.person_id = p_person_id AND
1340 sca.course_cd = p_course_cd AND
1341 sca.course_attempt_status IN (
1342
1343 cst_enrolled,
1344 cst_inactive);
1345 CURSOR c_susa IS
1346 SELECT 'X'
1347 FROM IGS_AS_SU_SETATMPT susa
1348 WHERE susa.person_id = p_person_id AND
1349 susa.course_cd = p_course_cd AND
1350 susa.unit_set_cd = p_parent_unit_set_cd AND
1351 susa.sequence_number = p_parent_sequence_number AND
1352 susa.student_confirmed_ind = 'N';
1353
1354
1355 CURSOR c_sca_legacy IS
1356 SELECT sca.version_number
1357 FROM IGS_EN_STDNT_PS_ATT sca
1358 WHERE sca.person_id = p_person_id AND
1359 sca.course_cd = p_course_cd AND
1360 sca.course_attempt_status = 'UNCONFIRM';
1361
1362 BEGIN
1363 p_message_name := NULL;
1364
1365 IF (p_student_confirmed_ind = 'Y') THEN
1366 -- Validate the confirmed indicator can only be set when the
1367 -- student course attempt is enrolled or inactive
1368
1369 IF (p_legacy = 'Y') THEN
1370 OPEN c_sca_legacy;
1371 FETCH c_sca_legacy INTO v_sca_version_number;
1372 IF (c_sca_legacy%FOUND) THEN
1373 p_message_name := 'IGS_EN_CANT_SET_CONF_IND';
1374 FND_MESSAGE.Set_Name('IGS',p_message_name);
1375 FND_MSG_PUB.Add;
1376 END IF;
1377 CLOSE c_sca_legacy;
1378 ELSE
1379 OPEN c_sca;
1380 FETCH c_sca INTO v_sca_version_number;
1381 IF (c_sca%NOTFOUND) THEN
1382 CLOSE c_sca;
1383 p_message_name := 'IGS_EN_UNIT_SET_SPA_ENR_INACT';
1384 RETURN FALSE;
1385 END IF;
1386 CLOSE c_sca;
1387 END IF;
1388
1389 -- Validate the confirmed indicator cannot be set when a parent
1390
1391 -- unit set exists that is unconfirmed.
1392 IF (p_parent_unit_set_cd IS NOT NULL AND
1393 p_parent_sequence_number IS NOT NULL) THEN
1394 OPEN c_susa;
1395 FETCH c_susa INTO v_dummy;
1396 IF (c_susa%FOUND) THEN
1397 p_message_name := 'IGS_EN_UNIT_SET_PARENTSET_CON';
1398
1399 IF (p_legacy = 'Y') THEN
1400 -- Add excep to stack
1401 FND_MESSAGE.Set_Name('IGS',p_message_name);
1402 FND_MSG_PUB.Add;
1403 ELSE
1404 CLOSE c_susa;
1405 RETURN FALSE;
1406 END IF;
1407 END IF;
1408 CLOSE c_susa;
1409
1410 -- Check that the relationship is still valid within the course offering
1411 IF (Igs_En_Val_Susa.enrp_val_susa_cousr(
1412 p_person_id,
1413 p_course_cd,
1414 p_unit_set_cd,
1415 p_us_version_number,
1416 p_parent_unit_set_cd,
1417 p_parent_sequence_number,
1418 'E',
1419 v_message_name,
1420 p_legacy) = FALSE) THEN
1421 p_message_name := v_message_name;
1422
1423 IF (p_legacy <> 'Y') THEN
1424 RETURN FALSE;
1425 END IF;
1426 END IF;
1427 END IF;
1428
1429 -- The below validations need not be
1430 -- done when operating in legacy mode
1431 IF (p_legacy <> 'Y') THEN
1432
1433 -- Check that the unit set is still active
1434 IF (Igs_En_Val_Susa.enrp_val_susa_us_act(
1435 p_person_id,
1436 p_course_cd,
1437 p_unit_set_cd,
1438 p_sequence_number,
1439 p_us_version_number,
1440 v_message_name) = FALSE) THEN
1441 p_message_name := v_message_name;
1442 RETURN FALSE;
1443 END IF;
1444 -- Validate the confirmed indicator cannot be set if the student is
1445 -- excluded from the unit set via encumbrances
1446 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_us(
1447 p_person_id,
1448 p_course_cd,
1449 p_unit_set_cd,
1450 p_us_version_number,
1451 SYSDATE,
1452 v_message_name) = FALSE) THEN
1453 p_message_name := v_message_name;
1454
1455 RETURN FALSE;
1456 END IF;
1457 END IF;
1458 -- unit set rules for enrolment are validated in separate module
1459 -- (enrp_val_susa_sci_rl) because rules system contains savepoints and
1460 -- rollbacks whick limit the routine from being called from database triggers.
1461 ELSE
1462 -- Validate the confirmed indicator being unset
1463 -- Cannot be unset if the end date has been set
1464 IF (p_end_dt IS NOT NULL) THEN
1465 p_message_name := 'IGS_EN_UNIT_SET_UNCONF_ENDDT';
1466
1467 IF (p_legacy = 'Y') THEN
1468 -- Add excep to stack
1469 FND_MESSAGE.Set_Name('IGS',p_message_name);
1470 FND_MSG_PUB.Add;
1471 ELSE
1472 RETURN FALSE;
1473 END IF;
1474 END IF;
1475
1476 -- Cannot be unset if the requirements complete indicator has been set
1477 IF (p_rqrmnts_complete_ind = 'Y') THEN
1478 p_message_name := 'IGS_EN_UNIT_SET_UNCONF_REQ';
1479
1480 IF (p_legacy = 'Y') THEN
1481 -- Add excep to stack
1482 FND_MESSAGE.Set_Name('IGS',p_message_name);
1483 FND_MSG_PUB.Add;
1484 ELSE
1485 RETURN FALSE;
1486 END IF;
1487 END IF;
1488 END IF;
1489
1490 RETURN TRUE;
1491
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 IF (c_sca%ISOPEN) THEN
1495
1496 CLOSE c_sca;
1497 END IF;
1498 IF (c_susa%ISOPEN) THEN
1499 CLOSE c_susa;
1500 END IF;
1501 RAISE;
1502 END;
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1506 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci');
1507 IGS_GE_MSG_STACK.ADD;
1508 App_Exception.Raise_Exception;
1509
1510 END enrp_val_susa_sci;
1511 --
1512 -- Validate the student unit set attempt confirmation rules.
1513 FUNCTION ENRP_VAL_SUSA_SCI_RL(
1514 p_person_id IN NUMBER ,
1515 p_course_cd IN VARCHAR2 ,
1516 p_unit_set_cd IN VARCHAR2 ,
1517 p_us_version_number IN NUMBER ,
1518 p_new_student_confirmed_ind IN VARCHAR2,
1519 p_old_student_confirmed_ind IN VARCHAR2,
1520 p_message_name OUT NOCOPY VARCHAR2 ,
1521 p_message_text OUT NOCOPY VARCHAR2 )
1522 RETURN BOOLEAN AS
1523 BEGIN -- enrp_val_susa_sci_rl
1524 -- - The student confirmed indicator cannot be set if
1525 -- rules exist preventing this.
1526 DECLARE
1527 cst_enrolled CONSTANT
1528 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
1529 cst_inactive CONSTANT
1530 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
1531 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1532 v_message_name VARCHAR2(30);
1533 CURSOR c_sca IS
1534 SELECT sca.version_number
1535 FROM IGS_EN_STDNT_PS_ATT sca
1536 WHERE sca.person_id = p_person_id AND
1537 sca.course_cd = p_course_cd AND
1538 sca.course_attempt_status IN (
1539 cst_enrolled,
1540 cst_inactive);
1541 BEGIN
1542 p_message_text := NULL;
1543
1544 IF (p_new_student_confirmed_ind = 'Y') AND
1545 (NVL(p_old_student_confirmed_ind, 'N') = 'N') THEN
1546 -- Validate the confirmed indicator can only be set when the
1547 -- student course attempt is enrolled or inactive
1548 OPEN c_sca;
1549 FETCH c_sca INTO v_sca_version_number;
1550 IF (c_sca%NOTFOUND) THEN
1551 CLOSE c_sca;
1552 p_message_name := 'IGS_EN_UNIT_SET_SPA_ENR_INACT';
1553 RETURN FALSE;
1554 END IF;
1555 CLOSE c_sca;
1556 -- Validate unit set rules for enrolment
1557 IF (IGS_RU_VAL_USET_RULE.rulp_val_enrol_uset(
1558 p_person_id,
1559 p_course_cd,
1560 v_sca_version_number,
1561 p_unit_set_cd,
1562 p_us_version_number,
1563 p_message_text) = FALSE) THEN
1564 p_message_name := NULL;
1565 RETURN FALSE;
1566 END IF;
1567
1568 END IF;
1569 p_message_name := NULL;
1570 RETURN TRUE;
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 IF (c_sca%ISOPEN) THEN
1574 CLOSE c_sca;
1575 END IF;
1576 RAISE;
1577 END;
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1581 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci_rl');
1582 IGS_GE_MSG_STACK.ADD;
1583 App_Exception.Raise_Exception;
1584
1585 END enrp_val_susa_sci_rl;
1586
1587
1588 --
1589 -- Validate student unit set atmpt confirm indicator and selection date.
1590 FUNCTION ENRP_VAL_SUSA_SCI_SD(
1591 p_student_confirmed_ind IN VARCHAR2,
1592 p_selection_dt IN DATE,
1593 p_message_name OUT NOCOPY VARCHAR2,
1594 p_legacy IN VARCHAR2)
1595 RETURN BOOLEAN AS
1596 BEGIN -- enrp_val_susa_sci_sd
1597 -- This module validates the student_confirmed_ind against the selection_dt
1598 -- for a IGS_AS_SU_SETATMPT record. The validations are:
1599 -- - The selection date must be set if the confirmed indicator is set
1600 -- (and visa versa).
1601 -- - The selection date being unset, then the confirmed indicator must be
1602 -- unset (and visa versa).
1603 DECLARE
1604 BEGIN
1605 p_message_name := NULL;
1606
1607 -- Validate the selection date must be set if the confirmed
1608 -- indicator is set and visa versa.
1609 IF (p_student_confirmed_ind = 'Y' AND
1610 p_selection_dt IS NULL) THEN
1611 p_message_name := 'IGS_EN_UNIT_SET_UNCONF_SETDT';
1612
1613 IF (p_legacy = 'Y') THEN
1614 -- Add excep to stack
1615 FND_MESSAGE.Set_Name('IGS',p_message_name);
1616 FND_MSG_PUB.Add;
1617 ELSE
1618 RETURN FALSE;
1619 END IF;
1620 END IF;
1621
1622 -- Validate the selection date must be unset if the
1623 -- confirmed indicator is set and visa versa.
1624 IF (p_student_confirmed_ind = 'N' AND
1625 p_selection_dt IS NOT NULL) THEN
1626 p_message_name := 'IGS_EN_UNIT_SET_UNCONF_NOTSET';
1627
1628 IF (p_legacy = 'Y') THEN
1629 -- Add excep to stack
1630 FND_MESSAGE.Set_Name('IGS',p_message_name);
1631 FND_MSG_PUB.Add;
1632 ELSE
1633 RETURN FALSE;
1634 END IF;
1635 END IF;
1636
1637 RETURN TRUE;
1638
1639 END;
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1643 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_sci_sd');
1644 IGS_GE_MSG_STACK.ADD;
1645 App_Exception.Raise_Exception;
1646
1647 END enrp_val_susa_sci_sd;
1648
1649
1650 --
1651 -- Validate the unit set is active for student unit set attempt.
1652 FUNCTION ENRP_VAL_SUSA_US_ACT(
1653 p_person_id IN NUMBER ,
1654 p_course_cd IN VARCHAR2 ,
1655 p_unit_set_cd IN VARCHAR2 ,
1656 p_sequence_number IN NUMBER ,
1657 p_version_number IN NUMBER ,
1658 p_message_name OUT NOCOPY VARCHAR2 )
1659 RETURN BOOLEAN AS
1660
1661
1662 BEGIN -- enrp_val_susa_us_act
1663 -- This module validates that the IGS_EN_UNIT_SET being allocated to the
1664 -- IGS_AS_SU_SETATMPT is active with a null expiry date or active with
1665 -- expiry date set and the student has previously selected in within the same
1666 -- course attempt.
1667 DECLARE
1668 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
1669 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
1670 v_expiry_dt IGS_EN_UNIT_SET.expiry_dt%TYPE;
1671 v_dummy VARCHAR2(1);
1672 CURSOR c_us_uss IS
1673 SELECT uss.s_unit_set_status,
1674 us.expiry_dt
1675 FROM IGS_EN_UNIT_SET us,
1676 IGS_EN_UNIT_SET_STAT uss
1677 WHERE us.unit_set_status = uss.unit_set_status AND
1678 us.unit_set_cd = p_unit_set_cd AND
1679 us.version_number = p_version_number;
1680 CURSOR c_susa IS
1681 SELECT 'x'
1682 FROM IGS_AS_SU_SETATMPT susa
1683 WHERE susa.person_id = p_person_id AND
1684
1685 susa.course_cd = p_course_cd AND
1686 susa.unit_set_cd = p_unit_set_cd AND
1687 susa.us_version_number = p_version_number AND
1688 susa.sequence_number <> NVL(p_sequence_number, 0);
1689 BEGIN
1690 -- Validate that the unit set status is active and null expiry date.
1691 OPEN c_us_uss;
1692 FETCH c_us_uss INTO v_s_unit_set_status,
1693 v_expiry_dt;
1694 IF v_s_unit_set_status <> cst_active THEN
1695 CLOSE c_us_uss;
1696 p_message_name := 'IGS_EN_UNIT_SETST_ACTIVE';
1697 RETURN FALSE;
1698 ELSE
1699 IF v_expiry_dt IS NOT NULL THEN
1700 -- Determine if the student has previously had the version selected
1701 -- within the specified course
1702 -- NOTE: sequence number comparison is used as this validation is called
1703 -- from an after statement databae trigger in which case, want to ignore the
1704 -- newly created record.
1705 OPEN c_susa;
1706 FETCH c_susa INTO v_dummy;
1707 IF (c_susa%NOTFOUND) THEN
1708
1709 CLOSE c_susa;
1710 p_message_name := 'IGS_EN_UNIT_SET_EXPDT_NOTSET';
1711 RETURN FALSE;
1712 END IF;
1713 CLOSE c_susa;
1714 END IF;
1715 END IF;
1716 -- If processing successful then
1717 p_message_name := NULL;
1718 RETURN TRUE;
1719 END;
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1723 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_us_act');
1724 IGS_GE_MSG_STACK.ADD;
1725 App_Exception.Raise_Exception;
1726 END enrp_val_susa_us_act;
1727 --
1728 -- Validate the student unit set attempt requires authorisation.
1729 FUNCTION ENRP_VAL_SUSA_US_ATH(
1730 p_unit_set_cd IN VARCHAR2 ,
1731 p_version_number IN NUMBER ,
1732 p_authorised_person_id IN NUMBER ,
1733 p_authorised_on IN DATE ,
1734 p_message_name OUT NOCOPY VARCHAR2 )
1735 RETURN BOOLEAN AS
1736
1737 BEGIN -- enrp_val_susa_us_ath
1738 -- This module validates that the IGS_EN_UNIT_SET being allocated to the
1739 -- IGS_AS_SU_SETATMPT requires authorisation.
1740 DECLARE
1741 v_auth_rqrd_ind IGS_EN_UNIT_SET.authorisation_rqrd_ind%TYPE;
1742 CURSOR c_us IS
1743 SELECT us.authorisation_rqrd_ind
1744 FROM IGS_EN_UNIT_SET us
1745 WHERE unit_set_cd = p_unit_set_cd AND
1746 version_number = p_version_number;
1747 BEGIN
1748 -- Set the default message number
1749 p_message_name := NULL;
1750 -- Validate that the is active and null expiry dt.
1751 OPEN c_us;
1752 FETCH c_us INTO v_auth_rqrd_ind;
1753 -- Validate that if the authorised indicator is set, then the
1754 -- authorised_person_id and authorised_on fields must be set.
1755
1756 IF c_us%FOUND THEN
1757 IF (v_auth_rqrd_ind = 'Y') AND
1758 (p_authorised_person_id IS NULL OR
1759 p_authorised_on IS NULL) THEN
1760 CLOSE c_us;
1761 p_message_name := 'IGS_EN_UNIT_SET_REQ_AUTHORISA';
1762 RETURN FALSE;
1763 END IF;
1764 END IF;
1765 CLOSE c_us;
1766 -- If processing successful then
1767 RETURN TRUE;
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 IF (c_us%NOTFOUND) THEN
1771 CLOSE c_us;
1772 END IF;
1773 END;
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1777 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_SUSA.enrp_val_susa_us_ath');
1778 IGS_GE_MSG_STACK.ADD;
1779
1780 App_Exception.Raise_Exception;
1781 END enrp_val_susa_us_ath;
1782 --
1783 -- Validation routines for student unit set attempt.
1784 FUNCTION ENRP_VAL_SUSA(
1785 p_person_id IN NUMBER ,
1786 p_course_cd IN VARCHAR2 ,
1787 p_unit_set_cd IN VARCHAR2 ,
1788 p_sequence_number IN NUMBER ,
1789 p_us_version_number IN NUMBER ,
1790 p_selection_dt IN DATE ,
1791 p_student_confirmed_ind IN VARCHAR2,
1792 p_end_dt IN DATE ,
1793 p_parent_unit_set_cd IN VARCHAR2 ,
1794 p_parent_sequence_number IN NUMBER ,
1795 p_primary_set_ind IN VARCHAR2,
1796 p_voluntary_end_ind IN VARCHAR2,
1797 p_authorised_person_id IN NUMBER ,
1798 p_authorised_on IN DATE ,
1799 p_override_title IN VARCHAR2 ,
1800 p_rqrmnts_complete_ind IN VARCHAR2,
1801 p_rqrmnts_complete_dt IN DATE ,
1802 p_s_completed_source_type IN VARCHAR2 ,
1803 p_action IN VARCHAR2 ,
1804 p_message_name OUT NOCOPY VARCHAR2 ,
1805 p_message_text OUT NOCOPY VARCHAR2 )
1806 RETURN BOOLEAN AS
1807
1808 BEGIN -- enrp_val_susa
1809 -- This module validates
1810 DECLARE
1811 v_message_name VARCHAR2(30);
1812 v_old_student_confirmed_ind
1813 IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
1814 CURSOR c_old_susa IS
1815 SELECT *
1816 FROM IGS_AS_SU_SETATMPT susa
1817 WHERE susa.person_id = p_person_id AND
1818 susa.course_cd = p_course_cd AND
1819 susa.unit_set_cd = p_unit_set_cd AND
1820 susa.sequence_number = p_sequence_number;
1821 v_old_susa_rec c_old_susa%ROWTYPE;
1822 cst_insert CONSTANT VARCHAR2 (6) := 'INSERT';
1823 cst_update CONSTANT VARCHAR2 (6) := 'UPDATE';
1824 cst_delete CONSTANT VARCHAR2 (6) := 'DELETE';
1825 cst_error CONSTANT VARCHAR2(1) := 'E';
1826
1827 v_inserting BOOLEAN := FALSE;
1828 v_updating BOOLEAN := FALSE;
1829 v_deleting BOOLEAN := FALSE;
1830 BEGIN
1831 IF p_action = cst_insert THEN
1832 v_inserting := TRUE;
1833 ELSIF p_action = cst_update THEN
1834 v_updating := TRUE;
1835 ELSIF p_action = cst_delete THEN
1836 v_deleting := TRUE;
1837 ELSE
1838 -- Invalid value for p_action.
1839 p_message_name := 'IGS_GE_INVALID_VALUE';
1840 RETURN FALSE;
1841 END IF;
1842 -- If updating, select the values of the record prior to update.
1843 IF v_updating THEN
1844 OPEN c_old_susa;
1845 FETCH c_old_susa INTO v_old_susa_rec;
1846 IF c_old_susa%NOTFOUND THEN
1847 CLOSE c_old_susa;
1848 Fnd_Message.Set_Name('IGS','IGS_EN_SU_SETATT_NOT_EXIST');
1849 IGS_GE_MSG_STACK.ADD;
1850
1851 App_Exception.Raise_Exception;
1852
1853 END IF;
1854 CLOSE c_old_susa;
1855 END IF;
1856 IF v_inserting THEN
1857 -- Validate the the unit set is able to be created.
1858 -- against the student course attempt.
1859 IF Igs_En_Val_Susa.enrp_val_susa_sca(
1860 p_person_id,
1861 p_course_cd,
1862 v_message_name) = FALSE THEN
1863 p_message_name := v_message_name;
1864 RETURN FALSE;
1865 END IF;
1866 -- Validate the the unit set is able to be created.
1867 -- The student cannot have completed it previously,
1868 -- no encumbrances must exist and it must be applicable
1869 -- to the course offering.
1870 IF Igs_En_Val_Susa.enrp_val_susa_ins(
1871 p_person_id,
1872 p_course_cd,
1873 p_unit_set_cd,
1874 p_sequence_number,
1875 p_us_version_number,
1876 v_message_name,
1877 'N') = FALSE THEN
1878 p_message_name := v_message_name;
1879 RETURN FALSE;
1880 END IF;
1881 END IF;
1882 -- Validate that the authorisation fields can only be set when end date is set
1883 -- or the unit set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
1884 IF v_inserting OR
1885 (v_updating AND
1886 ((NVL(p_authorised_person_id, 0) <>
1887 NVL(v_old_susa_rec.authorised_person_id, 0)) OR
1888 (NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
1889 <> NVL(v_old_susa_rec.authorised_on,
1890 IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1891 IF Igs_En_Val_Susa.enrp_val_susa_auth(
1892 p_unit_set_cd,
1893 p_us_version_number,
1894 p_end_dt,
1895 p_authorised_person_id,
1896 p_authorised_on,
1897 v_message_name,
1898 'N') = FALSE THEN
1899 p_message_name := v_message_name;
1900 RETURN FALSE;
1901 END IF;
1902 END IF;
1903 IF v_inserting OR
1904 (v_updating AND
1905 ((NVL(p_authorised_person_id, 0) <>
1906 NVL(v_old_susa_rec.authorised_person_id, 0)) OR
1907 (p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind) OR
1908 (NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
1909 <> NVL(v_old_susa_rec.authorised_on,
1910 IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1911 -- Validate that the authorisation fields must be set when
1912 -- the unit set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
1913 -- Check required only when the unit set is confirmed.
1914 IF (p_student_confirmed_ind = 'Y') THEN
1915 IF Igs_En_Val_Susa.enrp_val_susa_us_ath(
1916 p_unit_set_cd,
1917 p_us_version_number,
1918 p_authorised_person_id,
1919
1920 p_authorised_on,
1921 v_message_name) = FALSE THEN
1922 p_message_name := v_message_name;
1923 RETURN FALSE;
1924 END IF;
1925 END IF;
1926 END IF;
1927 -- Validate that the completion fields can only be set when unit set is
1928 -- confirmed
1929 IF v_inserting OR
1930 (v_updating AND
1931 ((NVL(p_rqrmnts_complete_ind, 'x')
1932 <> NVL(v_old_susa_rec.rqrmnts_complete_ind, 'x')) OR
1933 (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1934 <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1935 IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
1936 IF Igs_En_Val_Susa.enrp_val_susa_cmplt(
1937 p_rqrmnts_complete_dt,
1938 p_rqrmnts_complete_ind,
1939 p_student_confirmed_ind,
1940 v_message_name,
1941 'N') = FALSE THEN
1942 p_message_name := v_message_name;
1943 RETURN FALSE;
1944
1945 END IF;
1946 END IF;
1947 -- Validate that the system competed source type field can only be
1948 -- set when completion fields are set.
1949 IF v_inserting OR
1950 (v_updating AND
1951 ((NVL(p_rqrmnts_complete_ind, 'x')
1952 <> NVL(v_old_susa_rec.rqrmnts_complete_ind, 'x')) OR
1953 (NVL(p_s_completed_source_type, 'x')
1954 <> NVL(v_old_susa_rec.s_completed_source_type, 'x')) OR
1955 (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1956 <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1957 IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
1958 IF Igs_En_Val_Susa.enrp_val_susa_scst(
1959 p_rqrmnts_complete_dt,
1960 p_rqrmnts_complete_ind,
1961 p_s_completed_source_type,
1962 v_message_name) = FALSE THEN
1963 p_message_name := v_message_name;
1964 RETURN FALSE;
1965 END IF;
1966 END IF;
1967 -- Validate the date fields.
1968
1969 IF v_inserting OR
1970 (v_updating AND
1971 ((NVL(p_selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1972 <> NVL(v_old_susa_rec.selection_dt,
1973 IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
1974 (NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1975 <> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
1976 (NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1977 <> NVL(v_old_susa_rec.rqrmnts_complete_dt,
1978 IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1979 IF Igs_En_Val_Susa.enrp_val_susa_dts(
1980 p_selection_dt,
1981 p_end_dt,
1982 p_rqrmnts_complete_dt,
1983 v_message_name) = FALSE THEN
1984 p_message_name := v_message_name;
1985 RETURN FALSE;
1986 END IF;
1987 END IF;
1988 -- Validate that the selection date can only be set/unset when unit set is
1989 -- confirmed/unconfirmed
1990 IF v_inserting OR
1991 (v_updating AND
1992
1993 ((p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind) OR
1994 (NVL(p_selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
1995 <> NVL(v_old_susa_rec.selection_dt,
1996 IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
1997 IF Igs_En_Val_Susa.enrp_val_susa_sci_sd(
1998 p_student_confirmed_ind,
1999 p_selection_dt,
2000 v_message_name,
2001 'N') = FALSE THEN
2002 p_message_name := v_message_name;
2003 RETURN FALSE;
2004 END IF;
2005 END IF;
2006 -- Validate that the voluntary_end_ind can only be set when the end date is
2007 -- set.
2008 IF v_inserting OR
2009 (v_updating AND
2010 ((p_voluntary_end_ind <> v_old_susa_rec.voluntary_end_ind) OR
2011 (NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
2012 <> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
2013 IF Igs_En_Val_Susa.enrp_val_susa_end_vi(
2014 p_voluntary_end_ind,
2015 p_end_dt,
2016 v_message_name) = FALSE THEN
2017
2018 p_message_name := v_message_name;
2019 RETURN FALSE;
2020 END IF;
2021 END IF;
2022 -- Validate that the unit set version number cannot be updated.
2023 IF (v_updating AND
2024 (p_us_version_number <> v_old_susa_rec.us_version_number)) THEN
2025 p_message_name := 'IGS_EN_UNIT_SET_VERNUM_NOTUPD';
2026 RETURN FALSE;
2027 END IF;
2028 IF v_deleting THEN
2029 -- Validate that the records can be deleted.
2030 IF Igs_En_Val_Susa.enrp_val_susa_del(
2031 p_person_id,
2032 p_course_cd,
2033 p_unit_set_cd,
2034 p_sequence_number,
2035 p_us_version_number,
2036 p_end_dt,
2037 p_rqrmnts_complete_ind,
2038 'N', -- Indicating not called from trigger.
2039 v_message_name) = FALSE THEN
2040
2041 p_message_name := v_message_name;
2042 RETURN FALSE;
2043 END IF;
2044 END IF;
2045 IF v_inserting THEN
2046 -- Validate the the unit set is able to be created
2047 -- with the unit set status being valid and the
2048 -- expiry date not set. If set then person must have
2049 -- previously selected it.
2050 IF Igs_En_Val_Susa.enrp_val_susa_us_act(
2051 p_person_id,
2052 p_course_cd,
2053 p_unit_set_cd,
2054 p_sequence_number,
2055 p_us_version_number,
2056 v_message_name) = FALSE THEN
2057 p_message_name := v_message_name;
2058 RETURN FALSE;
2059 END IF;
2060 END IF;
2061 -- Validate the unit set parent relationship.
2062 IF v_inserting OR
2063
2064 (v_updating AND
2065 ((NVL(p_parent_unit_set_cd, 'NULL')
2066 <> NVL(v_old_susa_rec.parent_unit_set_cd, 'NULL')) OR
2067 (NVL(p_parent_sequence_number, 0)
2068 <> NVL(v_old_susa_rec.parent_sequence_number, 0)))) THEN
2069 -- Validate if the unit set is to be defined as a subordinate or if
2070 -- relationship specified, that it is valid within the course offering.
2071 IF Igs_En_Val_Susa.enrp_val_susa_cousr(
2072 p_person_id,
2073 p_course_cd,
2074 p_unit_set_cd,
2075 p_us_version_number,
2076 p_parent_unit_set_cd,
2077 p_parent_sequence_number,
2078 cst_error,
2079 v_message_name,
2080 'N') = FALSE THEN
2081 p_message_name := v_message_name;
2082 RETURN FALSE;
2083 END IF;
2084 -- Validate if the parent unit set has a null end date, unit set is
2085 -- not being linked to itself (directly or indirectly). Cannot be
2086 -- confirmed if parent is unconfirmed.
2087
2088 IF Igs_En_Val_Susa.enrp_val_susa_parent(
2089 p_person_id,
2090 p_course_cd,
2091 p_unit_set_cd,
2092 p_sequence_number,
2093 p_parent_unit_set_cd,
2094 p_parent_sequence_number,
2095 p_student_confirmed_ind,
2096 v_message_name,
2097 'N') = FALSE THEN
2098 p_message_name := v_message_name;
2099 RETURN FALSE;
2100 END IF;
2101 END IF;
2102 IF v_inserting OR
2103 (NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
2104 <> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
2105 -- Validate the end date, check if the authorisation details
2106 -- need to be set or if more than one open end dated instance
2107 -- of the unit set exists. Also cannot be cleared if parent ended.
2108 -- If part of the admissions offer, authorisation required to end
2109 -- the unit set.
2110 IF Igs_En_Val_Susa.enrp_val_susa_end_dt(
2111 p_person_id,
2112 p_course_cd,
2113 p_unit_set_cd,
2114 p_sequence_number,
2115 p_us_version_number,
2116 p_end_dt,
2117 p_authorised_person_id,
2118 p_authorised_on,
2119 p_parent_unit_set_cd,
2120 p_parent_sequence_number,
2121 cst_error,
2122 v_message_name,
2123 'N') = FALSE THEN
2124 -- Check if warning message returned.
2125 IF v_message_name <> 'IGS_EN_UNITSET_REQ_AUTHORISAT' THEN
2126 p_message_name := v_message_name;
2127 RETURN FALSE;
2128 END IF;
2129 END IF;
2130 -- If updating and the end date has been set, validate that it is
2131 -- possible to cascade the end date through to any descendant unit sets
2132 -- (Inserted records cannot have children at that point).
2133 IF v_updating AND
2134
2135 p_end_dt IS NOT NULL THEN
2136
2137 IF Igs_En_Val_Susa.enrp_val_susa_ed_upd(
2138 p_person_id,
2139 p_course_cd,
2140 p_unit_set_cd,
2141 p_sequence_number,
2142 p_end_dt,
2143 p_voluntary_end_ind,
2144 p_authorised_person_id,
2145 p_authorised_on,
2146 v_message_name) = FALSE THEN
2147 p_message_name := v_message_name;
2148 RETURN FALSE;
2149 END IF;
2150 END IF;
2151 END IF;
2152 IF (v_inserting AND p_student_confirmed_ind = 'Y') OR
2153 (v_updating AND
2154 (p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind)) THEN
2155 -- Validate that the unit set is not confirmed when the student course
2156 -- attempt is unconfirmed.
2157 -- Also check that not unset one end date or complete date set. Cannot be
2158
2159 -- confirmed and linked to a parent that is unconfirmed. Cannot be
2160 -- confirmed if encumbrances exist.
2161 IF Igs_En_Val_Susa.enrp_val_susa_sci(
2162 p_person_id,
2163 p_course_cd,
2164 p_unit_set_cd,
2165 p_sequence_number,
2166 p_us_version_number,
2167 p_parent_unit_set_cd,
2168 p_parent_sequence_number,
2169 p_student_confirmed_ind,
2170 p_selection_dt,
2171 p_end_dt,
2172 p_rqrmnts_complete_ind,
2173 v_message_name,
2174 'N') = FALSE THEN
2175 p_message_name := v_message_name;
2176 RETURN FALSE;
2177 END IF;
2178 -- Validate that if student confirmation indicator set, check if passes
2179 -- any associated rules.
2180 IF v_inserting THEN
2181 v_old_student_confirmed_ind := NULL;
2182
2183 ELSE
2184 v_old_student_confirmed_ind := v_old_susa_rec.student_confirmed_ind;
2185 END IF;
2186 IF Igs_En_Val_Susa.enrp_val_susa_sci_rl(
2187 p_person_id,
2188 p_course_cd,
2189 p_unit_set_cd,
2190 p_us_version_number,
2191 p_student_confirmed_ind,
2192 v_old_student_confirmed_ind,
2193 v_message_name,
2194 p_message_text) = FALSE THEN
2195 p_message_name := v_message_name;
2196 RETURN FALSE;
2197 END IF;
2198 -- If updating and the student confirmed indicator is being unset,
2199 -- then validate that able to unset any descendant unit sets. (Only concerned
2200 -- with update as unit set cannot have descendant at the point of
2201 -- creation).
2202 IF v_updating AND
2203 p_student_confirmed_ind = 'N' THEN
2204
2205 IF Igs_En_Val_Susa.enrp_val_susa_sci_up(
2206 p_person_id,
2207 p_course_cd,
2208 p_unit_set_cd,
2209 p_sequence_number,
2210 p_student_confirmed_ind,
2211 v_message_name) = FALSE THEN
2212 p_message_name := v_message_name;
2213 RETURN FALSE;
2214 END IF;
2215 END IF;
2216 END IF;
2217 -- Validate if the primary set indicator.
2218 IF v_inserting OR
2219 (v_updating AND
2220 (p_primary_set_ind <> v_old_susa_rec.primary_set_ind)) THEN
2221 -- Validate the primary set indicator is only set for
2222 -- non-administrative sets and that there does not already
2223 -- exist a unit set that has a higher rank.
2224 IF Igs_En_Val_Susa.enrp_val_susa_prmry(
2225 p_person_id,
2226 p_course_cd,
2227
2228 p_unit_set_cd,
2229 p_us_version_number,
2230 p_primary_set_ind,
2231 v_message_name,
2232 'N') = FALSE THEN
2233 p_message_name := v_message_name;
2234 RETURN FALSE;
2235 END IF;
2236 END IF;
2237 p_message_name := NULL;
2238 RETURN TRUE;
2239 END;
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2243 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa');
2244 IGS_GE_MSG_STACK.ADD;
2245 App_Exception.Raise_Exception;
2246
2247 END enrp_val_susa;
2248 --
2249 -- Validate the cascading setting of the end date of an susa record.
2250 FUNCTION ENRP_VAL_SUSA_ED_UPD(
2251 p_person_id IN NUMBER ,
2252 p_course_cd IN VARCHAR2 ,
2253 p_unit_set_cd IN VARCHAR2 ,
2254 p_sequence_number IN NUMBER ,
2255 p_end_dt IN DATE ,
2256 p_voluntary_end_ind IN VARCHAR2,
2257 p_authorised_person_id IN NUMBER ,
2258 p_authorised_on IN DATE ,
2259 p_message_name OUT NOCOPY VARCHAR2 )
2260 RETURN BOOLEAN AS
2261
2262 BEGIN -- enrp_val_susa_ed_upd
2263 -- This module is called when a IGS_AS_SU_SETATMPT is ended (end_dt set).
2264 -- This module will check if the unit set has any child unit sets and
2265 -- validate if able to set the end_dt and associated authorisation details
2266 -- for all children
2267 DECLARE
2268 v_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE;
2269 v_us_version_number IGS_AS_SU_SETATMPT.us_version_number%TYPE;
2270 v_sequence_number IGS_AS_SU_SETATMPT.sequence_number%TYPE;
2271 v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
2272 v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
2273 v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
2274
2275 v_end_dt IGS_AS_SU_SETATMPT.end_dt%TYPE;
2276 v_rqrmnts_complete_dt IGS_AS_SU_SETATMPT.rqrmnts_complete_dt%TYPE;
2277 v_parent_unit_set_cd IGS_AS_SU_SETATMPT.parent_unit_set_cd%TYPE;
2278 v_parent_sequence_number IGS_AS_SU_SETATMPT.parent_sequence_number%TYPE;
2279 v_student_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
2280 v_primary_set_ind IGS_AS_SU_SETATMPT.primary_set_ind%TYPE;
2281 v_voluntary_end_ind IGS_AS_SU_SETATMPT.voluntary_end_ind%TYPE;
2282 v_override_title IGS_AS_SU_SETATMPT.override_title%TYPE;
2283 v_rqrmnts_complete_ind IGS_AS_SU_SETATMPT.rqrmnts_complete_ind%TYPE;
2284 v_s_completed_source_type
2285 IGS_AS_SU_SETATMPT.s_completed_source_type%TYPE;
2286 v_message_name VARCHAR2(30);
2287 v_message_text VARCHAR2 (2000) := NULL;
2288 CURSOR c_susa IS
2289 SELECT susa.unit_set_cd,
2290 susa.us_version_number,
2291 susa.sequence_number,
2292 susa.authorised_person_id,
2293 susa.authorised_on,
2294 susa.selection_dt,
2295 susa.end_dt,
2296 susa.rqrmnts_complete_dt,
2297 susa.parent_unit_set_cd,
2298
2299 susa.parent_sequence_number,
2300 susa.student_confirmed_ind,
2301 susa.primary_set_ind,
2302 susa.voluntary_end_ind,
2303 susa.override_title,
2304 susa.rqrmnts_complete_ind,
2305 susa.s_completed_source_type
2306 FROM IGS_AS_SU_SETATMPT susa
2307 START WITH susa.person_id = p_person_id AND
2308 susa.course_cd = p_course_cd AND
2309 susa.parent_unit_set_cd = p_unit_set_cd AND
2310 susa.parent_sequence_number = p_sequence_number
2311 CONNECT BY
2312 PRIOR susa.person_id = susa.person_id AND
2313 PRIOR susa.course_cd = susa.course_cd AND
2314 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
2315 PRIOR susa.sequence_number = susa.parent_sequence_number;
2316 BEGIN
2317 -- Set the default message number
2318 p_message_name := NULL;
2319 OPEN c_susa;
2320 FETCH c_susa INTO v_unit_set_cd,
2321 v_us_version_number,
2322
2323 v_sequence_number,
2324 v_authorised_person_id,
2325 v_authorised_on,
2326 v_selection_dt,
2327 v_end_dt,
2328 v_rqrmnts_complete_dt,
2329 v_parent_unit_set_cd,
2330 v_parent_sequence_number,
2331 v_student_confirmed_ind,
2332 v_primary_set_ind,
2333 v_voluntary_end_ind,
2334 v_override_title,
2335 v_rqrmnts_complete_ind,
2336 v_s_completed_source_type;
2337 LOOP
2338 EXIT WHEN (c_susa%NOTFOUND);
2339 -- For each descendant record found, validate if able to then end the unit
2340 -- set.
2341 IF (v_end_dt IS NULL AND
2342 v_rqrmnts_complete_dt IS NULL) THEN
2343 -- Determine if authorised person id required to be updated (That is, if
2344 -- authorise parameter fields null then do not alter fields.)
2345 IF (p_authorised_person_id IS NOT NULL OR
2346
2347 p_authorised_on IS NOT NULL) THEN
2348 v_authorised_person_id := p_authorised_person_id;
2349 v_authorised_on := p_authorised_on;
2350 END IF;
2351 -- Validate that able to update the record.
2352 IF Igs_En_Val_Susa.enrp_val_susa(
2353 p_person_id,
2354 p_course_cd,
2355 v_unit_set_cd,
2356 v_sequence_number,
2357 v_us_version_number,
2358 v_selection_dt,
2359 v_student_confirmed_ind,
2360 p_end_dt,
2361 v_parent_unit_set_cd,
2362 v_parent_sequence_number,
2363 v_primary_set_ind,
2364 p_voluntary_end_ind,
2365 v_authorised_person_id,
2366 v_authorised_on,
2367 v_override_title,
2368 v_rqrmnts_complete_ind,
2369 v_rqrmnts_complete_dt,
2370
2371 v_s_completed_source_type,
2372 'UPDATE',
2373 v_message_name,
2374 v_message_text) = FALSE THEN
2375 -- Ignore v_message_text as rules are not used here in any validation.
2376 CLOSE c_susa;
2377 p_message_name := v_message_name;
2378 RETURN FALSE;
2379 END IF;
2380 END IF;
2381 FETCH c_susa INTO v_unit_set_cd,
2382 v_us_version_number,
2383 v_sequence_number,
2384 v_authorised_person_id,
2385 v_authorised_on,
2386 v_selection_dt,
2387 v_end_dt,
2388 v_rqrmnts_complete_dt,
2389 v_parent_unit_set_cd,
2390 v_parent_sequence_number,
2391 v_student_confirmed_ind,
2392 v_primary_set_ind,
2393 v_voluntary_end_ind,
2394
2395 v_override_title,
2396 v_rqrmnts_complete_ind,
2397 v_s_completed_source_type;
2398 END LOOP;
2399 CLOSE c_susa;
2400 -- If processing successful then
2401 RETURN TRUE;
2402 EXCEPTION
2403 WHEN OTHERS THEN
2404 IF c_susa%ISOPEN THEN
2405 CLOSE c_susa;
2406 END IF;
2407 RAISE;
2408 END;
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2412 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_ed_upd');
2413 IGS_GE_MSG_STACK.ADD;
2414 App_Exception.Raise_Exception;
2415 END enrp_val_susa_ed_upd;
2416 --
2417 -- Validate cascade unsetting of stdnt unit set atmpt confirmation ind.
2418
2419 FUNCTION ENRP_VAL_SUSA_SCI_UP(
2420 p_person_id IN NUMBER ,
2421 p_course_cd IN VARCHAR2 ,
2422 p_unit_set_cd IN VARCHAR2 ,
2423 p_sequence_number IN NUMBER ,
2424 p_student_confirmed_ind IN VARCHAR2,
2425 p_message_name OUT NOCOPY VARCHAR2 )
2426 RETURN BOOLEAN AS
2427
2428 BEGIN -- enrp_val_susa_sci_up
2429 -- This module is called when the student_confimed_ind is unset.
2430 -- This module will check if the unit set has any child unit sets and
2431 -- validate if able unset the the student_confirmed_ind for all children.
2432 DECLARE
2433 v_enrp_val_susa_sci BOOLEAN;
2434 v_susa_rec_found BOOLEAN;
2435 v_message_name VARCHAR2(30);
2436 v_message_text VARCHAR2 (2000) := NULL;
2437 CURSOR c_susa IS
2438 SELECT susa.unit_set_cd,
2439 susa.us_version_number,
2440 susa.sequence_number,
2441 susa.authorised_person_id,
2442
2443 susa.authorised_on,
2444 susa.selection_dt,
2445 susa.end_dt,
2446 susa.rqrmnts_complete_dt,
2447 susa.parent_unit_set_cd,
2448 susa.parent_sequence_number,
2449 susa.student_confirmed_ind,
2450 susa.primary_set_ind,
2451 susa.voluntary_end_ind,
2452 susa.override_title,
2453 susa.rqrmnts_complete_ind,
2454 susa.s_completed_source_type
2455 FROM IGS_AS_SU_SETATMPT susa
2456 START WITH susa.person_id = p_person_id AND
2457 susa.course_cd = p_course_cd AND
2458 susa.parent_unit_set_cd = p_unit_set_cd AND
2459 susa.parent_sequence_number = p_sequence_number
2460 CONNECT BY
2461 PRIOR susa.person_id = susa.person_id AND
2462 PRIOR susa.course_cd = susa.course_cd AND
2463 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
2464 PRIOR susa.sequence_number = susa.parent_sequence_number;
2465 BEGIN
2466
2467 v_enrp_val_susa_sci := TRUE;
2468 v_susa_rec_found := FALSE;
2469
2470 -- Set the default message number
2471 p_message_name := NULL;
2472 -- If student confirmed indicator is NULL or 'Y' then
2473 -- not concerned with updating children
2474 IF p_student_confirmed_ind = 'Y' OR
2475 p_student_confirmed_ind IS NULL THEN
2476 p_message_name := NULL;
2477 RETURN TRUE;
2478 END IF;
2479 -- Process all descendants of the unit set and to validate if able
2480 -- to unset the student confirmed indicator.
2481 -- For each descendant record found, validate unsetting of the student
2482 -- confirmed indicator.
2483 FOR v_susa_rec IN c_susa LOOP
2484 v_susa_rec_found := TRUE;
2485 IF v_susa_rec.student_confirmed_ind = 'Y' THEN
2486 -- Validate that able to update the record.
2487 IF Igs_En_Val_Susa.enrp_val_susa(
2488 p_person_id,
2489 p_course_cd,
2490 v_susa_rec.unit_set_cd,
2491 v_susa_rec.sequence_number,
2492 v_susa_rec.us_version_number,
2493
2494 NULL, -- selection_dt
2495 'N', -- student_confirmed_ind
2496 v_susa_rec.end_dt,
2497 v_susa_rec.parent_unit_set_cd,
2498 v_susa_rec.parent_sequence_number,
2499 v_susa_rec.primary_set_ind,
2500 v_susa_rec.voluntary_end_ind,
2501 v_susa_rec.authorised_person_id,
2502 v_susa_rec.authorised_on,
2503 v_susa_rec.override_title,
2504 v_susa_rec.rqrmnts_complete_ind,
2505 v_susa_rec.rqrmnts_complete_dt,
2506 v_susa_rec.s_completed_source_type,
2507 'UPDATE',
2508 v_message_name,
2509 v_message_text) = FALSE THEN
2510 -- Ignore v_message_text as rules are not used here in any validation.
2511 p_message_name := v_message_name;
2512 v_enrp_val_susa_sci := FALSE;
2513 EXIT;
2514 END IF;
2515 END IF;
2516 END LOOP;
2517
2518 IF (v_susa_rec_found = TRUE AND
2519 v_enrp_val_susa_sci = FALSE) THEN
2520 RETURN FALSE;
2521 END IF;
2522 RETURN TRUE;
2523 EXCEPTION
2524 WHEN OTHERS THEN
2525 IF c_susa%ISOPEN THEN
2526 CLOSE c_susa;
2527 END IF;
2528 RAISE;
2529 END;
2530 EXCEPTION
2531 WHEN NO_DATA_FOUND THEN
2532 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2533 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_sci_up');
2534 IGS_GE_MSG_STACK.ADD;
2535 App_Exception.Raise_Exception;
2536 END enrp_val_susa_sci_up;
2537 --
2538 -- Validate the requirement complete fields for IGS_AS_SU_SETATMPT.
2539 FUNCTION enrp_val_susa_scst(
2540 p_rqrmnts_complete_dt IN DATE ,
2541 p_rqrmnts_complete_ind IN VARCHAR2,
2542 p_s_completed_source_type IN VARCHAR2,
2543 p_message_name OUT NOCOPY VARCHAR2 )
2544 RETURN BOOLEAN AS
2545
2546 BEGIN -- enrp_val_susa_scst
2547 -- This module validates the system completed source type
2548 -- field associated with the IGS_AS_SU_SETATMPT:
2549 -- - s_completed_source_type can only be set if rqrmnts_complete_dt
2550 -- and rqrmnts_complete_ind fields are set.
2551 DECLARE
2552 BEGIN
2553 -- s_completed_source_type can only be set if rqrmnts_complete_dt and
2554 -- rqrmnts_complete_ind fields are set.
2555 IF (p_rqrmnts_complete_ind = 'N' AND
2556 p_rqrmnts_complete_dt IS NULL AND
2557 p_s_completed_source_type IS NOT NULL) THEN
2558 p_message_name := 'IGS_EN_SYS_COMPL_SRCTYPE_SET';
2559 RETURN FALSE;
2560 END IF;
2561 p_message_name := NULL;
2562 RETURN TRUE;
2563 END;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2568 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Val_Susa.enrp_val_susa_scst');
2569 IGS_GE_MSG_STACK.ADD;
2570 App_Exception.Raise_Exception;
2571 END enrp_val_susa_scst;
2572 --
2573 -- Validate a person id.
2574 --
2575
2576 END Igs_En_Val_Susa;
[Home] [Help]