1 PACKAGE BODY IGS_RE_VAL_TEX AS
2 /* $Header: IGSRE15B.pls 120.1 2006/07/25 15:05:40 sommukhe noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed
7 --svenkata 9-APR-2002 FUNCTION RESP_VAL_TEX_SBMSN has been modified 'cos the Submitted On field can now accept Future dates .
8 -- The code has to be bypassed so that the procedure can still go ahead and validate if the value for Sumitted
9 -- on is greater than Max. Submission Date. Bug # 2030672 , 2028078
10 --Nishikant 19NOV2002 Bug#2661533. The signature of the functions resp_val_tex_sbmsn got modified to add
11 -- two more parameer p_legacy and p_final_title_ind.
12 --Nishikant 31DEC2002 Bug#2722106. If p_submission_dt is null and p_thesis_result_cd is
13 -- not null then log error message in the function RESP_VAL_TEX_SBMSN.
14 --myoganat 24-Jun-2003 Bug# 2720102. Added validation to allow students with a program attempt status of 'COMPLETED'
15 -- to update the thesis exam details. As part of this, constant variable cst_completed was added and included
16 -- in the validation in procedure RESP_VAL_TEX_UPD
17 -------------------------------------------------------------------------------------------
18 -- Validate the deceased indicator for a person.
19 FUNCTION GENP_VAL_PE_DECEASED(
20 p_person_id IN NUMBER ,
21 p_message_type IN VARCHAR2 ,
22 p_message_name OUT NOCOPY VARCHAR2 )
23 RETURN BOOLEAN AS
24 BEGIN -- genp_val_pe_deceased
25 -- Validate the person is not deceased.
26 DECLARE
27 CURSOR c_pe IS
28 SELECT deceased_ind
29 FROM IGS_PE_PERSON
30 WHERE person_id = p_person_id;
31 v_deceased_ind VARCHAR2(1) DEFAULT NULL;
32 BEGIN
33 p_message_name := NULL;
34 -- Validate that the correct value is passed in p_message_type.
35 IF p_message_type NOT IN ('ERROR', 'WARN') THEN
36 p_message_name := 'IGS_GE_INVALID_VALUE';
37 RETURN FALSE;
38 END IF;
39 -- Determine if the person is deceased
40 OPEN c_pe;
41 FETCH c_pe INTO v_deceased_ind;
42 IF (c_pe%NOTFOUND) THEN
43 CLOSE c_pe;
44 p_message_name := 'IGS_GE_INVALID_VALUE';
45 RETURN FALSE;
46 ELSE
47 CLOSE c_pe;
48 IF (v_deceased_ind = 'Y') THEN
49 -- Determine if warning or error message to be returned
50 IF p_message_type = 'ERROR' THEN
51 p_message_name := 'IGS_GE_PERSON_DECEASED';
52 ELSE
53 p_message_name := 'IGS_GE_WARN_PERSON_DECEASED';
54 END IF;
55 RETURN FALSE;
56 END IF;
57 END IF;
58 RETURN TRUE;
59 EXCEPTION
60 WHEN OTHERS THEN
61 IF (c_pe%ISOPEN) THEN
62 CLOSE c_pe;
63 END IF;
64 RAISE;
65 END;
66 EXCEPTION
67 WHEN OTHERS THEN
68 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71 END genp_val_pe_deceased;
72 --
73 -- To validate IGS_RE_THESIS examination submission date
74 FUNCTION RESP_VAL_TEX_SBMSN(
75 p_person_id IN NUMBER ,
76 p_ca_sequence_number IN NUMBER ,
77 p_the_sequence_number IN NUMBER ,
78 p_creation_dt IN DATE ,
79 p_thesis_result_cd IN VARCHAR2 ,
80 p_submission_dt IN DATE ,
81 p_legacy IN VARCHAR2,
82 p_final_title_ind IN VARCHAR2,
83 p_message_name OUT NOCOPY VARCHAR2 )
84 RETURN BOOLEAN AS
85 /* Change History :
86 Who When What
87 (reverse chronological order - newest change first)
88
89 Nishikant 15NOV2002 The function got modified to skip some validation in case
90 It has been called from Legacy API. And if any error message then
91 logs in the message stack and proceed further.
92 Nishikant 31DEC2002 Bug#2722106. If p_submission_dt is null and p_thesis_result_cd is
93 not null then log error message.
94 stutta 05-May-2004 Added c_awd_exists,c_incomp_awd cursors and modified logic to return false(Bug #3577988)
95 if a completed program attempt has all its awards completed. If atleast one
96 award is complete or no award assoicated for a completed program attempt return true.
97 skpandey 10-JUL-2006 Bug#5343912, changed cursor c_cfos definition to include 'per fos_type_code' percentage check for all non CIP type
98 */
99 BEGIN -- resp_val_tex_sbmsn
100 -- Validate the thesis_examination.submission_dt, checking for :
101 -- Cannot be cleared if the thesis_result_cd is set
102 -- Cannot be cleared if any thesis_panel_member records exist
103 -- with their thesis_result_cd set
104 -- Cannot set if previous thesis_exam record exists which
105 -- has been submitted but for which no result has been recorded
106 -- Cannot set if parent IGS_RE_THESIS.final_title_ind = 'N'
107 -- Cannot be set if the parent IGS_EN_STDNT_PS_ATT.course_attempt_status
108 -- is not one of ENROLLED, INTERMIT or INACTIVE
109 -- Cannot be deceased person
110 -- Cannot be future dated
111 -- Cannot be less than the IGS_EN_STDNT_PS_ATT.commencement_dt
112 -- Cannot be prior to the submission date of a previous thesis_examination
113 -- record
114 -- Must have a principal supervisor
115 -- Must have fields of study = 100%
116 -- Must have socio-economic classifications = 100%
117 -- Must have type of activity code set
118 -- Cannot be prior to the minimum submission date
119 -- Warn if greater than maximum submission date
120 -- Cannot be set if the parent IGS_EN_STDNT_PS_ATT.course_attempt_status is
121 -- COMPLETED and all its associated awards completed.
122
123 DECLARE
124 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
125 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
126 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
127 cst_no_result CONSTANT VARCHAR2(10) := 'NORESULT';
128 CURSOR c_the IS
129 SELECT thes.final_title_ind
130 FROM IGS_RE_THESIS thes
131 WHERE thes.person_id = p_person_id AND
132 thes.ca_sequence_number = p_ca_sequence_number AND
133 thes.sequence_number = p_the_sequence_number;
134 v_the_rec c_the%ROWTYPE;
135 CURSOR c_sca IS
136 SELECT sca.course_attempt_status,
137 sca.commencement_dt,
138 ca.min_submission_dt,
139 ca.max_submission_dt,
140 ca.govt_type_of_activity_cd,
141 sca.person_id,
142 sca.course_cd
143 FROM IGS_RE_CANDIDATURE ca,
144 IGS_EN_STDNT_PS_ATT sca
145 WHERE ca.person_id = p_person_id AND
146 ca.sequence_number = p_ca_sequence_number AND
147 sca.person_id = ca.person_id AND
148 sca.course_cd = ca.sca_course_cd;
149 v_sca_rec c_sca%ROWTYPE;
150 CURSOR c_tex IS
151 SELECT 'x'
152 FROM IGS_RE_THESIS_EXAM tex
153 WHERE tex.person_id = p_person_id AND
154 tex.ca_sequence_number = p_ca_sequence_number AND
155 tex.the_sequence_number = p_the_sequence_number AND
156 tex.creation_dt < p_creation_dt AND
157 tex.submission_dt IS NOT NULL AND
158 tex.thesis_result_cd IS NULL;
159 v_tex_exists VARCHAR2(1);
160 CURSOR c_rsup IS
161 SELECT 'x'
162 FROM IGS_RE_SPRVSR rsup,
163 IGS_RE_SPRVSR_TYPE rst
164 WHERE rsup.ca_person_id = p_person_id AND
165 rsup.ca_sequence_number = p_ca_sequence_number AND
166 rsup.start_dt <= SYSDATE AND
167 (rsup.end_dt IS NULL OR
168 rsup.end_dt > SYSDATE) AND
169 rst.research_supervisor_type = rsup.research_supervisor_type AND
170 rst.principal_supervisor_ind = 'Y';
171 v_rsup_exists VARCHAR2(1);
172 CURSOR c_cfos IS
173 SELECT NVL(Sum(cfos.percentage), 0) total, cfos.fos_type_code
174 FROM igs_re_cdt_fld_of_sy_v cfos
175 WHERE cfos.fos_type_code <> 'CIP'
176 AND cfos.person_id = p_person_id
177 AND cfos.ca_sequence_number = p_ca_sequence_number
178 GROUP BY cfos.fos_type_code
179 HAVING Sum(cfos.percentage)<>100;
180 v_cfos_rec c_cfos%ROWTYPE;
181 CURSOR c_csc IS
182 SELECT SUM(csc.percentage) sum_percentage
183 FROM IGS_RE_CAND_SEO_CLS csc
184 WHERE csc.person_id = p_person_id AND
185 csc.ca_sequence_number = p_ca_sequence_number;
186 v_csc_rec c_csc%ROWTYPE;
187 CURSOR c_tex2 IS
188 SELECT 'x'
189 FROM IGS_RE_THESIS_EXAM tex
190 WHERE tex.person_id = p_person_id AND
191 tex.ca_sequence_number = p_ca_sequence_number AND
192 tex.the_sequence_number = p_the_sequence_number AND
193 tex.creation_dt < p_creation_dt AND
194 tex.submission_dt > p_submission_dt;
195 v_tex2_exists VARCHAR2(1);
196 v_dummy VARCHAR2(1);
197 CURSOR c_tpm IS
198 SELECT 'x'
199 FROM IGS_RE_THS_PNL_MBR tpm,
200 IGS_RE_THESIS_RESULT thr
201 WHERE tpm.person_id = p_person_id AND
202 tpm.ca_sequence_number = p_ca_sequence_number AND
203 tpm.the_sequence_number = p_the_sequence_number AND
204 tpm.creation_dt = p_creation_dt AND
205 tpm.confirmed_dt IS NOT NULL AND
206 thr.thesis_result_cd = tpm.thesis_result_cd AND
207 thr.s_thesis_result_cd <> cst_no_result;
208 CURSOR c_awd_exists(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
209 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
210 SELECT 'x'
211 FROM igs_en_spa_awd_aim
212 WHERE person_id = cp_person_id
213 AND course_cd = cp_course_cd
214 AND ( end_dt IS NULL OR
215 (end_dt IS NOT NULL AND complete_ind = 'Y')
216 );
217 CURSOR c_incomp_awd(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
218 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
219 SELECT 'x'
220 FROM igs_en_spa_awd_aim
221 WHERE person_id = cp_person_id
222 AND course_cd = cp_course_cd
223 AND NVL(complete_ind,'N') = 'N'
224 AND end_dt IS NULL;
225 v_tpm_exists VARCHAR2(1);
226 v_min_submission IGS_RE_CANDIDATURE.min_submission_dt%TYPE DEFAULT NULL;
227 v_max_submission IGS_RE_CANDIDATURE.max_submission_dt%TYPE DEFAULT NULL;
228 v_message_name VARCHAR2(30);
229 BEGIN
230 -- Set the default message number
231 p_message_name := NULL;
232 IF p_submission_dt IS NOT NULL THEN
233 -- 1. Cannot set if thesis title hasn't been finalised.
234 IF p_legacy <> 'Y' THEN --for legacy this validation is not required
235 OPEN c_the;
236 FETCH c_the INTO v_the_rec;
237 IF c_the%NOTFOUND THEN
238 CLOSE c_the;
239 -- Parameters are invalid, don't continue checking,
240 -- as the error will be picked up by the calling routine
241 RETURN TRUE;
242 END IF;
243 CLOSE c_the;
244 IF v_the_rec.final_title_ind = 'N' THEN
245 p_message_name := 'IGS_RE_CANT_SUBMIT_THESIS';
246 RETURN FALSE;
247 END IF;
248 ELSE --for legacy p_final_title_ind will have a value , hence no need to derive
249 IF p_final_title_ind = 'N' THEN
250 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CANT_SUBMIT_THESIS');
251 FND_MSG_PUB.ADD;
252 END IF;
253 END IF;
254
255 IF p_legacy <> 'Y' THEN --for legacy this validation is not required
256 -- Check for deceased person ; submission is not possible.
257 IF IGS_RE_VAL_TEX.genp_val_pe_deceased(
258 p_person_id,
259 'ERROR',
260 v_message_name) = FALSE THEN
261 p_message_name := v_message_name;
262 RETURN FALSE;
263 END IF;
264 END IF;
265 -- 2. Can only be set for students with course attempt attempt statuses
266 -- of ENROLLED, INTERMIT OR INACTIVE. If no course attempt attempt is
267 -- linked to the candidature then it also cannot be set.
268 OPEN c_sca;
269 FETCH c_sca INTO v_sca_rec;
270 IF p_legacy <> 'Y' THEN --for legacy this validation is not required
271 IF c_sca%NOTFOUND THEN
272 CLOSE c_sca;
273 p_message_name := 'IGS_RE_CANT_SUB_UNLES_CUR_ENR';
274 RETURN FALSE;
275 ELSIF v_sca_rec.course_attempt_status = 'COMPLETED' THEN
276 OPEN c_awd_exists(v_sca_rec.person_id,v_sca_rec.course_cd);
277 FETCH c_awd_exists INTO v_dummy;
278 IF c_awd_exists%FOUND THEN
279 OPEN c_incomp_awd(v_sca_rec.person_id,v_sca_rec.course_cd);
280 FETCH c_incomp_awd INTO v_dummy;
281 IF c_incomp_awd%FOUND THEN
282 CLOSE c_sca;
283 CLOSE c_awd_exists;
284 CLOSE c_incomp_awd;
285 RETURN TRUE;
286 ELSE
287 p_message_name := 'IGS_RE_CANT_SUB_UNLES_CUR_ENR';
288 CLOSE c_sca;
289 CLOSE c_awd_exists;
290 CLOSE c_incomp_awd;
291 RETURN FALSE;
292 END IF;
293 ELSE
294 CLOSE c_sca;
295 CLOSE c_awd_exists;
296 RETURN TRUE;
297 END IF;
298 ELSIF v_sca_rec.course_attempt_status NOT IN ( cst_enrolled,
299 cst_inactive,
300 cst_intermit) THEN
301 CLOSE c_sca;
302 p_message_name := 'IGS_RE_CANT_SUB_UNLES_CUR_ENR';
303 RETURN FALSE;
304 END IF;
305 CLOSE c_sca;
306 -- 3. Cannot be set if previous submitted IGS_RE_THESIS examination
307 -- records exist with no result.
308 OPEN c_tex;
309 FETCH c_tex INTO v_tex_exists;
310 IF c_tex%FOUND THEN
311 CLOSE c_tex;
312 p_message_name := 'IGS_RE_CANT_SUB_IF_PREV_EXAM';
313 RETURN FALSE;
314 END IF;
315 CLOSE c_tex;
316 END IF;
317 -- 4. Must have a principal supervisor
318 OPEN c_rsup;
319 FETCH c_rsup INTO v_rsup_exists;
320 IF c_rsup%NOTFOUND THEN
321 p_message_name := 'IGS_RE_CAND_MUST_HAVE_SUPERV';
322 IF p_legacy <> 'Y' THEN
323 RETURN FALSE;
324 ELSE
325 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CAND_MUST_HAVE_SUPERV');
326 FND_MSG_PUB.ADD;
327 END IF;
328 END IF;
329 CLOSE c_rsup;
330 -- 5. Current fields of study must total 100%
331 IF p_legacy <> 'Y' THEN --for legacy this validation is not required
332 OPEN c_cfos;
333 FETCH c_cfos INTO v_cfos_rec;
334 CLOSE c_cfos;
335 IF v_cfos_rec.total <> 100 THEN
336 p_message_name := 'IGS_RE_CAND_MUST_HAVE_STUDY';
337 RETURN FALSE;
338 END IF;
339 -- 6. Current socio-economic objectives must total 100%
340 OPEN c_csc;
341 FETCH c_csc INTO v_csc_rec;
342 CLOSE c_csc;
343 IF NVL(v_csc_rec.sum_percentage, 0) <> 100 THEN
344 p_message_name := 'IGS_RE_CAND_MUST_HAVE_CLASS';
345 RETURN FALSE;
346 END IF;
347 END IF;
348 -- Must have type of activity code set.
349 IF v_sca_rec.govt_type_of_activity_cd IS NULL THEN
350 p_message_name := 'IGS_RE_MUST_HAVE_GOV_TYPE';
351 IF p_legacy <> 'Y' THEN
352 RETURN FALSE;
353 ELSE
354 FND_MESSAGE.SET_NAME('IGS','IGS_RE_MUST_HAVE_GOV_TYPE');
355 FND_MSG_PUB.ADD;
356 END IF;
357 END IF;
358
359 -- 7. Cannot be future dated
360 -- svenkata - The following code has been commeneted 'cos the Submitted On field can now accept Future dates .
361 -- This code has to be bypassed so that the procedure can still go ahead and validate if the value for Sumitted
362 -- on is greater than Max. Submission Date. The code is not yet removed with the idea that there might be a future
363 -- requirement for this functionality . Bug # 2030672 , 2028078
364 /*IF p_submission_dt > SYSDATE THEN
365 p_message_name := 'IGS_RE_SUB_DT_CANT_GT_FUT_DT';
366 RETURN FALSE;
367 END IF;*/
368
369 -- 8. Cannot be prior to course attempt commencement date.
370 IF p_submission_dt < v_sca_rec.commencement_dt THEN
371 p_message_name := 'IGS_RE_SUBM_DT_CANT_LT_COM_DT';
372 IF p_legacy = 'Y' THEN
373 FND_MESSAGE.SET_NAME('IGS',p_message_name);
374 FND_MSG_PUB.ADD;
375 END IF;
376 RETURN FALSE;
377 END IF;
378 -- 9. Cannot be prior to the submission date of a prior examination.
379 OPEN c_tex2;
380 FETCH c_tex2 INTO v_tex2_exists;
381 IF c_tex2%FOUND THEN
382 CLOSE c_tex2;
383 p_message_name := 'IGS_RE_SUB_DT_CANT_LT_PREV_DT';
384 RETURN FALSE;
385 END IF;
386 CLOSE c_tex2;
387 -- 10. Cannot be prior to the override/derived submission date.
388 IF v_sca_rec.min_submission_dt IS NOT NULL THEN
389 v_min_submission := v_sca_rec.min_submission_dt;
390 ELSE
391 v_min_submission := NVL(IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN(
392 p_person_id,
393 p_ca_sequence_number,
394 NULL,
395 NULL,
396 NULL,
397 NULL,
398 NULL,
399 NULL), SYSDATE);
400 END IF;
401 IF p_submission_dt < v_min_submission THEN
402 p_message_name := 'IGS_RE_SUB_DT_CANT_LT_MIN_DT';
403 RETURN FALSE;
404 END IF;
405 -- 11. Warn IF past the maximum submission date.
406 IF v_sca_rec.max_submission_dt IS NOT NULL THEN
407 v_max_submission := v_sca_rec.max_submission_dt;
408 ELSE
409 v_max_submission := NVL(IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN(
410 p_person_id,
411 p_ca_sequence_number,
412 NULL,
413 NULL,
414 NULL,
415 NULL,
416 NULL,
417 NULL), SYSDATE);
418 END IF;
419 IF p_submission_dt > v_max_submission THEN
420 p_message_name := 'IGS_RE_CHK_SUB_DATE';
421 RETURN TRUE; -- Warning Only
422 END IF;
423 ELSE -- p_submission_dt IS NULL
424 -- 1. Cannot unset submission date once result has been entered.
425 IF p_thesis_result_cd IS NOT NULL THEN
426 p_message_name := 'IGS_RE_CHK_UNSUB_RES_ENTERED';
427 IF p_legacy = 'Y' THEN
428 --Different message will be logged if called from legacy procedure
429 FND_MESSAGE.SET_NAME('IGS','IGS_RE_SUB_DT_CNT_NULL');
430 FND_MSG_PUB.ADD;
431 END IF;
432 RETURN FALSE;
433 END IF;
434 IF p_legacy <> 'Y' THEN
435 -- 2. Cannot be cleared IF any IGS_RE_THESIS panel records exist
436 -- which have results recorded.
437 OPEN c_tpm;
438 FETCH c_tpm INTO v_tpm_exists;
439 IF c_tpm%FOUND THEN
440 CLOSE c_tpm;
441 p_message_name := 'IGS_RE_CHK_UNSUB_RES_ENT_PAN';
442 RETURN FALSE;
443 END IF;
444 CLOSE c_tpm;
445 END IF;
446 END IF;
447 -- Return the default value
448 RETURN TRUE;
449 EXCEPTION
450 WHEN OTHERS THEN
451 IF c_the%ISOPEN THEN
452 CLOSE c_the;
453 END IF;
454 IF c_sca%ISOPEN THEN
455 CLOSE c_sca;
456 END IF;
457 IF c_tex%ISOPEN THEN
458 CLOSE c_tex;
459 END IF;
460 IF c_rsup%ISOPEN THEN
461 CLOSE c_rsup;
462 END IF;
463 IF c_cfos%ISOPEN THEN
464 CLOSE c_cfos;
465 END IF;
466 IF c_csc%ISOPEN THEN
467 CLOSE c_csc;
468 END IF;
469 IF c_tex2%ISOPEN THEN
470 CLOSE c_tex2;
471 END IF;
472 IF c_tpm%ISOPEN THEN
473 CLOSE c_tpm;
474 END IF;
475 RAISE;
476 END;
477 EXCEPTION
478 WHEN OTHERS THEN
479 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
480 IGS_GE_MSG_STACK.ADD;
481 App_Exception.Raise_Exception;
482 END resp_val_tex_sbmsn;
483 --
484 -- To validate the IGS_RE_THESIS examination update
485 FUNCTION RESP_VAL_TEX_UPD(
486 p_person_id IN NUMBER ,
487 p_ca_sequence_number IN NUMBER ,
488 p_the_sequence_number IN NUMBER ,
489 p_transaction_type IN VARCHAR2 ,
490 p_submission_dt IN DATE ,
491 p_message_name OUT NOCOPY VARCHAR2 )
492 RETURN BOOLEAN AS
493 BEGIN
494 DECLARE
495 cst_insert CONSTANT VARCHAR2(10) := 'INSERT';
496 cst_update CONSTANT VARCHAR2(10) := 'UPDATE';
497 cst_delete CONSTANT VARCHAR2(10) := 'DELETE';
498 cst_enrolled CONSTANT
499 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
500 cst_inactive CONSTANT
501 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
502 cst_intermit CONSTANT
503 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
504 cst_completed CONSTANT
505 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
506 cst_examined CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'EXAMINED';
507 cst_deleted CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'DELETED';
508 v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
509 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
510 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
511 v_message_name VARCHAR2(30);
512 CURSOR c_ca IS
513 SELECT ca.sca_course_cd
514 FROM IGS_RE_CANDIDATURE ca
515 WHERE ca.person_id = p_person_id AND
516 ca.sequence_number = p_ca_sequence_number;
517 CURSOR c_sca (
518 cp_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE) IS
519 SELECT sca.course_attempt_status
520 FROM IGS_EN_STDNT_PS_ATT sca
521 WHERE sca.person_id = p_person_id AND
522 sca.course_cd = cp_sca_course_cd;
523 CURSOR c_thev IS
524 SELECT thev.thesis_status
525 FROM IGS_RE_THESIS_V thev
526 WHERE thev.person_id = p_person_id AND
527 thev.ca_sequence_number = p_ca_sequence_number AND
528 thev.sequence_number = p_the_sequence_number;
529 BEGIN
530 -- Check if person is dead.
531 IF IGS_RE_VAL_TEX.genp_val_pe_deceased( p_person_id,
532 'ERROR',
533 v_message_name) = FALSE THEN
534 p_message_name := v_message_name;
535 RETURN FALSE;
536 END IF;
537 -- Select details from candidature
538 OPEN c_ca;
539 FETCH c_ca INTO v_sca_course_cd;
540 IF (c_ca%NOTFOUND) THEN
541 -- Invalid parameters
542 CLOSE c_ca;
543 p_message_name := NULL;
544 RETURN TRUE;
545 END IF;
546 CLOSE c_ca;
547 IF (p_transaction_type IN (
548 cst_insert,
549 cst_update,
550 cst_delete)) THEN
551 -- 1. Not if the candidature is not linked to enrolments
552 IF (v_sca_course_cd IS NULL) THEN
553 p_message_name := 'IGS_RE_CANT_IU_THESIS_EXM_DET';
554 RETURN FALSE;
555 END IF;
556 -- Select details from IGS_EN_STDNT_PS_ATT
557 OPEN c_sca(v_sca_course_cd);
558 FETCH c_sca INTO v_course_attempt_status;
559 CLOSE c_sca;
560 -- 2. Not if the course_attempt_status is not ENROLLED, INTERMIT, COMPLETED or INACTIVE
561 IF (v_course_attempt_status NOT IN (
562 cst_enrolled,
563 cst_inactive,
564 cst_intermit,
565 cst_completed)) THEN
566 p_message_name := 'IGS_RE_CANT_IU_EXAM_DETAILS';
567 RETURN FALSE;
568 END IF;
569 -- 3. Not if the parent IGS_RE_THESIS is EXAMINED or DELETED
570 OPEN c_thev;
571 FETCH c_thev INTO v_thesis_status;
572 IF (c_thev%FOUND AND
573 v_thesis_status IN (
574 cst_examined,
575 cst_deleted)) THEN
576 CLOSE c_thev;
577 p_message_name := 'IGS_RE_CANT_IU_EXAM_DETAIL';
578 RETURN FALSE;
579 END IF;
580 CLOSE c_thev;
581 END IF;
582 IF (p_transaction_type = cst_delete) THEN
583 -- 1. Cannot delete if the examination has been submitted
584 IF (p_submission_dt IS NOT NULL) THEN
585 p_message_name := 'IGS_RE_CANT_DEL_THESIS_EXAM';
586 RETURN FALSE;
587 END IF;
588 END IF;
589 p_message_name := NULL;
590 RETURN TRUE;
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF (c_ca%ISOPEN) THEN
594 CLOSE c_ca;
595 END IF;
596 IF (c_sca%ISOPEN) THEN
597 CLOSE c_sca;
598 END IF;
599 IF (c_thev%ISOPEN) THEN
600 CLOSE c_thev;
601 END IF;
602 RAISE;
603 END;
604 EXCEPTION
605 WHEN OTHERS THEN
606 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
607 IGS_GE_MSG_STACK.ADD;
608 App_Exception.Raise_Exception;
609 END resp_val_tex_upd;
610 --
611 -- To validate IGS_RE_THESIS examination type
612 FUNCTION RESP_VAL_TEX_TET(
613 p_thesis_exam_type IN VARCHAR2 ,
614 p_message_name OUT NOCOPY VARCHAR2 )
615 RETURN BOOLEAN AS
616 BEGIN -- resp_val_tex_tet
617 -- Validate the thesis_examination.thesis_examination_type, checking for :
618 -- Closed examination type
619 DECLARE
620 v_dummy VARCHAR2(1);
621 CURSOR c_tet IS
622 SELECT 'x'
623 FROM IGS_RE_THS_EXAM_TYPE tet
624 WHERE tet.thesis_exam_type = p_thesis_exam_type AND
625 tet.closed_ind = 'Y';
626 BEGIN
627 -- set default value
628 p_message_name := NULL;
629 -- 1. Check for closed type
630 OPEN c_tet;
631 FETCH c_tet INTO v_dummy;
632 IF c_tet%FOUND THEN
633 CLOSE c_tet;
634 p_message_name := 'IGS_RE_THESIS_EXAM_TYPE_CLOSE';
635 RETURN FALSE;
636 END IF;
637 CLOSE c_tet;
638 RETURN TRUE;
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF c_tet%ISOPEN THEN
642 CLOSE c_tet;
643 END IF;
644 RAISE;
645 END;
646 EXCEPTION
647 WHEN OTHERS THEN
648 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
649 IGS_GE_MSG_STACK.ADD;
650 App_Exception.Raise_Exception;
651 END; -- resp_val_tex_tet
652 --
653 -- To validate IGS_RE_THESIS examination result code
654 FUNCTION RESP_VAL_TEX_THR(
655 p_person_id IN NUMBER ,
656 p_ca_sequence_number IN NUMBER ,
657 p_the_sequence_number IN NUMBER ,
658 p_creation_dt IN DATE ,
659 p_submission_dt IN DATE ,
660 p_thesis_result_cd IN VARCHAR2 ,
661 p_thesis_panel_type IN VARCHAR2 ,
662 p_message_name OUT NOCOPY VARCHAR2 )
663 RETURN BOOLEAN AS
664 BEGIN -- resp_val_tex_thr
665 -- Validate IGS_RE_THESIS_EXAM.thesis_result_cd, checking for :
666 -- Code cannot be closed
667 -- Code cannot be specified if submission_dt is not set
668 -- Warning if code does not match any of the panel members results
669 -- (if any results exist)
670 -- Cannot be cleared if IGS_RE_THESIS has been EXAMINED (ie. final result entered).
671 DECLARE
672 cst_examined CONSTANT VARCHAR2(10) := 'EXAMINED';
673 v_dummy VARCHAR2(1);
674 v_closed_ind IGS_RE_THESIS_RESULT.closed_ind%TYPE;
675 v_records_found BOOLEAN;
676 v_result_match BOOLEAN;
677 v_record_count NUMBER;
678 v_confirmed_count NUMBER;
679 v_recommended_panel_size IGS_RE_THS_PNL_TYPE.recommended_panel_size%TYPE;
680 CURSOR c_thr IS
681 SELECT thr.closed_ind
682 FROM IGS_RE_THESIS_RESULT thr
683 WHERE thr.thesis_result_cd = p_thesis_result_cd;
684 CURSOR c_tpm IS
685 SELECT tpm.thesis_result_cd
686 FROM IGS_RE_THS_PNL_MBR tpm
687 WHERE tpm.ca_person_id = p_person_id AND
688 tpm.ca_sequence_number = p_ca_sequence_number AND
689 tpm.the_sequence_number = p_the_sequence_number AND
690 tpm.creation_dt = p_creation_dt AND
691 tpm.confirmed_dt IS NOT NULL AND
692 tpm.thesis_result_cd IS NOT NULL;
693 CURSOR c_tpt IS
694 SELECT tpt.recommended_panel_size
695 FROM IGS_RE_THS_PNL_TYPE tpt
696 WHERE tpt.thesis_panel_type = p_thesis_panel_type;
697 CURSOR c_tpmc IS
698 SELECT confirmed_dt
699 FROM IGS_RE_THS_PNL_MBR tpm
700 WHERE tpm.ca_person_id = p_person_id AND
701 tpm.ca_sequence_number = p_ca_sequence_number AND
702 tpm.the_sequence_number = p_the_sequence_number AND
703 tpm.creation_dt = p_creation_dt;
704 CURSOR c_thev IS
705 SELECT 'x'
706 FROM IGS_RE_THESIS_V thev
707 WHERE thev.person_id = p_person_id AND
708 thev.ca_sequence_number = p_ca_sequence_number AND
709 thev.sequence_number = p_the_sequence_number AND
710 thev.thesis_status = cst_examined;
711 CURSOR c_tex IS
712 SELECT 'x'
713 FROM IGS_RE_THESIS_EXAM tex
714 WHERE tex.person_id = p_person_id AND
715 tex.ca_sequence_number = p_ca_sequence_number AND
716 tex.the_sequence_number = p_the_sequence_number AND
717 tex.creation_dt > p_creation_dt AND
718 tex.submission_dt IS NOT NULL;
719 BEGIN
720 -- set default value
721 p_message_name := NULL;
722 v_records_found := FALSE;
723 v_result_match := FALSE;
724 IF p_thesis_result_cd IS NOT NULL THEN
725 -- 1. Cannot be specified if not a submitted IGS_RE_THESIS.
726 IF p_submission_dt IS NULL THEN
727 p_message_name := 'IGS_RE_CANT_ENTER_RES_EXAM';
728 RETURN FALSE;
729 END IF;
730 -- 2. Cannot be closed.
731 OPEN c_thr;
732 FETCH c_thr INTO v_closed_ind;
733 IF c_thr%NOTFOUND THEN
734 -- Invalid parameters - will be picked up by calling routine
735 CLOSE c_thr;
736 RETURN TRUE;
737 END IF;
738 CLOSE c_thr;
739 IF v_closed_ind = 'Y' THEN
740 p_message_name := 'IGS_RE_THESIS_RESUILT_CLOSED';
741 RETURN FALSE;
742 END IF;
743 -- Validate that minimum panel size has been met.
744 OPEN c_tpt;
745 FETCH c_tpt INTO v_recommended_panel_size;
746 IF c_tpt%NOTFOUND THEN
747 CLOSE c_tpt;
748 RETURN TRUE;
749 END IF;
750 CLOSE c_tpt;
751 IF v_recommended_panel_size IS NOT NULL AND
752 v_recommended_panel_size <> 0 THEN
753 v_record_count := 0;
754 v_confirmed_count := 0;
755 FOR v_tpmc_rec IN c_tpmc
756 LOOP
757 v_record_count := v_record_count + 1;
758 IF v_tpmc_rec.confirmed_dt IS NOT NULL THEN
759 v_confirmed_count := v_confirmed_count + 1;
760 END IF;
761 END LOOP;
762 IF v_record_count > 0 AND
763 v_confirmed_count < v_recommended_panel_size THEN
764 p_message_name := 'IGS_RE_CANT_ENTER_RESULT';
765 RETURN FALSE;
766 END IF;
767 END IF;
768 -- 3. Warn if result doesn't match at least one of the IGS_RE_THESIS panel results.
769 FOR v_tpm_rec IN c_tpm Loop
770 v_records_found := TRUE;
771 IF p_thesis_result_cd = v_tpm_rec.thesis_result_cd THEN
772 v_result_match := TRUE;
773 END IF;
774 END LOOP;
775 IF v_records_found = TRUE AND
776 v_result_match = FALSE THEN
777 -- warning only
778 p_message_name := 'IGS_RE_RES_DOES_NOT_MATCH';
779 RETURN TRUE;
780 END IF;
781 ELSE -- result code null
782 --4. Cannot be cleared if the parent IGS_RE_THESIS status is EXAMINED.
783 OPEN c_thev;
784 FETCH c_thev INTO v_dummy;
785 IF c_thev%FOUND THEN
786 CLOSE c_thev;
787 p_message_name := 'IGS_RE_RES_CANT_BE_REMOVED';
788 RETURN FALSE;
789 END IF;
790 CLOSE c_thev;
791 -- Cannot be cleared if later submission exists.
792 OPEN c_tex;
793 FETCH c_tex INTO v_dummy;
794 IF c_tex%FOUND THEN
795 CLOSE c_tex;
796 p_message_name := 'IGS_RE_CANT_CLEAR_RESULT_CODE';
797 RETURN FALSE;
798 END IF;
799 CLOSE c_tex;
800 END IF;
801 RETURN TRUE;
802 EXCEPTION
803 WHEN OTHERS THEN
804 IF c_thr%ISOPEN THEN
805 CLOSE c_thr;
806 END IF;
807 IF c_tpm%ISOPEN THEN
808 CLOSE c_tpm;
809 END IF;
810 IF c_thev%ISOPEN THEN
811 CLOSE c_thev;
812 END IF;
813 IF c_tex%ISOPEN THEN
814 CLOSE c_tex;
815 END IF;
816 IF c_tpt%ISOPEN THEN
817 CLOSE c_tpt;
818 END IF;
819 RAISE;
820 END;
821 EXCEPTION
822 WHEN OTHERS THEN
823 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
824 IGS_GE_MSG_STACK.ADD;
825 App_Exception.Raise_Exception;
826 END; -- resp_val_tex_thr
827 --
828 -- To validate thesis_exam panel type
829 FUNCTION RESP_VAL_TEX_TPT(
830 p_thesis_panel_type IN VARCHAR2 ,
831 p_message_name OUT NOCOPY VARCHAR2 )
832 RETURN BOOLEAN AS
833 BEGIN -- resp_val_tex_tpt
834 -- Validate the thesis_examination.thesis_panel_type, checking for :
835 -- Closed examination panel type
836 DECLARE
837 v_dummy VARCHAR2(1);
838 CURSOR c_tpt IS
839 SELECT 'x'
840 FROM IGS_RE_THS_PNL_TYPE tpt
841 WHERE tpt.thesis_panel_type = p_thesis_panel_type AND
842 tpt.closed_ind = 'Y';
843 BEGIN
844 -- set default value
845 p_message_name := NULL;
846 -- 1. Check for closed type
847 OPEN c_tpt;
848 FETCH c_tpt INTO v_dummy;
849 IF c_tpt%FOUND THEN
850 CLOSE c_tpt;
851 p_message_name := 'IGS_RE_THESIS_EXAM_TYP_CLOSED';
852 RETURN FALSE;
853 END IF;
854 CLOSE c_tpt;
855 RETURN TRUE;
856 EXCEPTION
857 WHEN OTHERS THEN
858 IF c_tpt%ISOPEN THEN
859 CLOSE c_tpt;
860 END IF;
861 RAISE;
862 END;
863 EXCEPTION
864 WHEN OTHERS THEN
865 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
866 IGS_GE_MSG_STACK.ADD;
867 App_Exception.Raise_Exception;
868 END; -- resp_val_tex_tpt
869 END IGS_RE_VAL_TEX;
[Home] [Help]