1 PACKAGE BODY IGS_RE_VAL_THE AS
2 /* $Header: IGSRE16B.pls 115.6 2002/11/29 10:56:22 pradhakr ship $ */
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 8-MAR-2002 Bug # 2146848. A cursor in the function RESP_VAL_THE_THR was modified .
8 --Nishikant 19NOV2002 Bug#2661533. The functions resp_val_the_expct, resp_val_the_embrg, resp_val_the_thr
9 -- got modified to skip some validation in case It has been called from Legacy API.
10 -- Three more functions get_candidacy_dtls, check_dup_thesis, eval_min_sub_dt are added.
11 -- pradhakr 29-Nov-2002 Added the hint NOCOPY to all the OUT parameters. Replaced all
12 -- the OUT parameter with OUT NOCOPY. Bug# 2683043
13 -------------------------------------------------------------------------------------------
14 -- To valdate IGS_RE_THESIS citation fiels
15 FUNCTION RESP_VAL_THE_CTN(
16 p_thesis_status IN VARCHAR2 ,
17 p_citation IN VARCHAR2 ,
18 p_message_name OUT NOCOPY VARCHAR2 )
19 RETURN BOOLEAN AS
20 BEGIN -- resp_val_the_ctn
21 BEGIN
22 p_message_name := NULL;
23 IF NVL(p_thesis_status,' ') NOT IN (
24 'EXAMINED',
25 'SUBMITTED') AND
26 p_citation IS NOT NULL THEN
27 p_message_name := 'IGS_RE_CANT_ENTER_GRAD_CITAT';
28 RETURN FALSE;
29 END IF;
30 RETURN TRUE;
31 END;
32 EXCEPTION
33 WHEN OTHERS THEN
34 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 END resp_val_the_ctn;
38 --
39 -- Validate IGS_RE_THESIS logical deletion date
40 FUNCTION RESP_VAL_THE_DEL_DT(
41 p_old_logical_delete_dt IN DATE ,
42 p_new_logical_delete_dt IN DATE ,
43 p_message_name OUT NOCOPY VARCHAR2 )
44 RETURN BOOLEAN AS
45 BEGIN -- resp_val_the_del_dt
46 DECLARE
47 BEGIN
48 p_message_name := NULL;
49 -- 1. Date cannot be changed once set.
50 IF p_new_logical_delete_dt IS NOT NULL AND
51 p_old_logical_delete_dt IS NOT NULL AND
52 p_new_logical_delete_dt <> p_old_logical_delete_dt THEN
53 p_message_name := 'IGS_RE_LOGICA_DEL_DT_CANT_UPD';
54 RETURN FALSE;
55 END IF;
56 -- 2. If date being set, then must be equal to today.
57 IF p_old_logical_delete_dt IS NULL AND
58 p_new_logical_delete_dt IS NOT NULL AND
59 TRUNC(p_new_logical_delete_dt) <> TRUNC(SYSDATE) THEN
60 p_message_name := 'IGS_RE_SET_LOGICALDT_TO_CURR';
61 RETURN FALSE;
62 END IF;
63 RETURN TRUE;
64 END;
65 EXCEPTION
66 WHEN OTHERS THEN
67 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
68 IGS_GE_MSG_STACK.ADD;
69 App_Exception.Raise_Exception;
70 END resp_val_the_del_dt;
71 --
72 -- To validate the IGS_RE_THESIS expected submission date
73 FUNCTION RESP_VAL_THE_EXPCT(
74 p_person_id IN NUMBER ,
75 p_ca_sequence_number IN NUMBER ,
76 p_expected_submission_dt IN DATE ,
77 p_legacy IN VARCHAR2,
78 p_message_name OUT NOCOPY VARCHAR2 )
79 RETURN BOOLEAN AS
80 /* Change History :
81 Who When What
82 (reverse chronological order - newest change first)
83
84 Nishikant 15NOV2002 The function got modified to skip some validation in case
85 It has been called from Legacy API. And if any error message then
86 logs in the message stack and proceed further.
87 */
88 BEGIN -- resp_val_the_expct
89 -- Description: Validate the IGS_RE_THESIS.expected_submission_dt, checking for
90 -- * Cannot be backdated.
91 -- * Cannot be prior to the derived/override minimum submission date
92 -- * Cannot be beyond the derived/override maximum submission date
93 DECLARE
94 v_max_sub_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE := NULL;
95 v_min_sub_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE := NULL;
96 CURSOR c_ca IS
97 SELECT ca.max_submission_dt,
98 ca.min_submission_dt
99 FROM IGS_RE_CANDIDATURE ca
100 WHERE ca.person_id = p_person_id AND
101 ca.sequence_number = p_ca_sequence_number;
102 BEGIN
103 p_message_name := NULL;
104 IF p_legacy <> 'Y' THEN --this validation is not required for legacy
105 IF TRUNC(p_expected_submission_dt) < TRUNC(SYSDATE) THEN
106 p_message_name := 'IGS_RE_SUBM_DT_CANT_BACKDATED';
107 RETURN FALSE;
108 END IF;
109 END IF;
110 OPEN c_ca;
111 FETCH c_ca INTO v_max_sub_dt,
112 v_min_sub_dt;
113 IF (c_ca%NOTFOUND) THEN
114 CLOSE c_ca;
115 RETURN TRUE;
116 END IF;
117 CLOSE c_ca;
118 -- Check whether > maximum submission date
119 IF v_min_sub_dt IS NULL THEN
120 v_min_sub_dt := NVL(IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN(
121 p_person_id,
122 p_ca_sequence_number,
123 NULL,
124 NULL,
125 NULL,
126 NULL,
127 NULL,
128 NULL), SYSDATE);
129 END IF;
130 IF TRUNC(p_expected_submission_dt) < TRUNC(v_min_sub_dt) THEN
131 p_message_name := 'IGS_RE_SUB_DT_CANT_BEF_MIN_DT';
132 IF p_legacy <> 'Y' THEN
133 RETURN FALSE;
134 ELSE
135 FND_MESSAGE.SET_NAME ('IGS',p_message_name);
136 FND_MSG_PUB.ADD;
137 END IF;
138 END IF;
139 -- Check whether > maximum submission date
140 IF v_max_sub_dt IS NULL THEN
141 v_max_sub_dt := NVL(IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN(
142 p_person_id,
143 p_ca_sequence_number,
144 NULL,
145 NULL,
146 NULL,
147 NULL,
148 NULL,
149 NULL), SYSDATE);
150 END IF;
151 IF TRUNC(p_expected_submission_dt) > TRUNC(v_max_sub_dt) THEN
152 p_message_name := 'IGS_RE_SUB_DT_CANT_BEF_MAX_DT';
153 IF p_legacy <> 'Y' THEN
154 RETURN FALSE;
155 ELSE
156 FND_MESSAGE.SET_NAME ('IGS',p_message_name);
157 FND_MSG_PUB.ADD;
158 RETURN FALSE;
159 END IF;
160 END IF;
161 RETURN TRUE;
162 END;
163 EXCEPTION
164 WHEN OTHERS THEN
165 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
166 IGS_GE_MSG_STACK.ADD;
167 App_Exception.Raise_Exception;
168 END resp_val_the_expct;
169 --
170 -- To validate thesis embargo details
171 FUNCTION RESP_VAL_THE_EMBRG(
172 p_embargo_details IN VARCHAR2 ,
173 p_old_embargo_expiry_dt IN DATE ,
174 p_new_embargo_expiry_dt IN DATE ,
175 p_thesis_status IN VARCHAR2 ,
176 p_legacy IN VARCHAR2,
177 p_message_name OUT NOCOPY VARCHAR2 )
178 RETURN BOOLEAN AS
179 /* Change History :
180 Who When What
181 (reverse chronological order - newest change first)
182
183 Nishikant 15NOV2002 The function got modified to skip some validation in case
184 It has been called from Legacy API. And if any error message then
185 logs in the message stack and proceed further.
186 */
187 BEGIN -- resp_val_the_embrg
188 -- Validate thesis embargo fields (embargo_details, embargo_expiry_dt),
189 -- checking for :
190 -- Cannot enter details unless SUBMITTED or EXAMINED
191 -- That the expiry date cannot be set if the details aren't set
192 -- That the details cannot be unset if the expiry date is set
193 -- The expiry date cannot be backdated
194 DECLARE
195 BEGIN
196 -- Cannot enter details unless SUBMITTED or EXAMINED
197 IF NVL(p_thesis_status,' ') NOT IN ('SUBMITTED','EXAMINED') AND
198 (p_new_embargo_expiry_dt IS NOT NULL OR
199 p_embargo_details IS NOT NULL) THEN
200 p_message_name := 'IGS_RE_CANT_SPECIFY_EMBARGO';
201 IF p_legacy <> 'Y' THEN
202 RETURN FALSE;
203 ELSE
204 FND_MESSAGE.SET_NAME ('IGS',p_message_name);
205 FND_MSG_PUB.ADD;
206 END IF;
207 END IF;
208 -- 1. Check that if the expiry date is set that the details are set.
209 IF p_new_embargo_expiry_dt IS NOT NULL AND
210 p_embargo_details IS NULL THEN
211 p_message_name := 'IGS_RE_CANT_ENTER_EMBARGO_DT';
212 IF p_legacy <> 'Y' THEN
213 RETURN FALSE;
214 ELSE
215 FND_MESSAGE.SET_NAME ('IGS',p_message_name);
216 FND_MSG_PUB.ADD;
217 RETURN FALSE;
218 END IF;
219 END IF;
220 -- Check that the embargo expiry date is different to the old embargo date and
221 -- not backdated
222 IF p_legacy <> 'Y' THEN --for legacy this validation is not required
223 IF NVL(p_old_embargo_expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
224 NVL(p_new_embargo_expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
225 (p_new_embargo_expiry_dt IS NOT NULL AND
226 p_new_embargo_expiry_dt < TRUNC(SYSDATE)) THEN
227 p_message_name := 'IGS_RE_EMBARGO_DT_CANT_BACKDT';
228 RETURN FALSE;
229 END IF;
230 END IF;
231 p_message_name := NULL;
232 RETURN TRUE;
233 END;
234 EXCEPTION
235 WHEN OTHERS THEN
236 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
237 IGS_GE_MSG_STACK.ADD;
238 App_Exception.Raise_Exception;
239 END resp_val_the_embrg;
240 --
241 -- Validate thesis deletion (logical deletion)
242 FUNCTION RESP_VAL_THE_DEL(
243 p_person_id IN NUMBER ,
244 p_ca_sequence_number IN NUMBER ,
245 p_sequence_number IN NUMBER ,
246 p_logical_delete_dt IN DATE ,
247 p_thesis_status IN VARCHAR2 ,
248 p_message_name OUT NOCOPY VARCHAR2 )
249 RETURN BOOLEAN AS
250 BEGIN -- resp_val_the_del
251 -- Description: Validate the IGS_RE_THESIS.logical_delete_dt, checking for
252 -- * Cannot logically delete when status is SUBMITTED or EXAMINED
253 DECLARE
254 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
255 v_ret_val BOOLEAN := TRUE;
256 CURSOR c_thev IS
257 SELECT thesis_status
258 FROM IGS_RE_THESIS_V thev
259 WHERE person_id = p_person_id AND
260 ca_sequence_number = p_ca_sequence_number AND
261 sequence_number = p_sequence_number;
262 BEGIN
263 p_message_name := NULL;
264 IF p_logical_delete_dt IS NOT NULL THEN
265 IF p_thesis_status IS NULL THEN
266 OPEN c_thev;
267 FETCH c_thev INTO v_thesis_status;
268 IF (c_thev%NOTFOUND) THEN
269 CLOSE c_thev;
270 RETURN TRUE;
271 END IF;
272 CLOSE c_thev;
273 ELSE
274 v_thesis_status := p_thesis_status;
275 END IF;
276 IF v_thesis_status IN ( 'EXAMINED',
277 'SUBMITTED') THEN
278 p_message_name := 'IGS_RE_CANT_DEL_THESIS_DETAIL';
279 RETURN FALSE;
280 END IF;
281 END IF;
282 RETURN v_ret_val;
283 END;
284 EXCEPTION
285 WHEN OTHERS THEN
286 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception;
289 END resp_val_the_del;
290 --
291 -- To validate IGS_RE_THESIS library details
292 FUNCTION RESP_VAL_THE_LBRY(
293 p_person_id IN NUMBER ,
294 p_ca_sequence_number IN NUMBER ,
295 p_sequence_number IN NUMBER ,
296 p_library_catalogue_number IN VARCHAR2 ,
297 p_library_lodgement_dt IN DATE ,
298 p_thesis_status IN VARCHAR2 ,
299 p_message_name OUT NOCOPY VARCHAR2 )
300 RETURN BOOLEAN AS
301 BEGIN -- resp_val_the_lbry
302 -- Description: Validate the IGS_RE_THESIS library details
303 -- (library_catalogue_number, library_lodgement_dt), checking for :
304 -- *That the details cannot be specified if the IGS_RE_THESIS status is not
305 -- EXAMINED or SUBMITTED.
306 DECLARE
307 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
308 v_ret_val BOOLEAN := TRUE;
309 CURSOR c_thev IS
310 SELECT thesis_status
311 FROM IGS_RE_THESIS_V thev
312 WHERE person_id = p_person_id AND
313 ca_sequence_number = p_ca_sequence_number AND
314 sequence_number = p_sequence_number;
315 BEGIN
316 p_message_name := NULL;
317 IF p_library_catalogue_number IS NOT NULL OR
318 p_library_lodgement_dt IS NOT NULL THEN
319 IF p_thesis_status IS NULL THEN
320 OPEN c_thev;
321 FETCH c_thev INTO v_thesis_status;
322 IF (c_thev%NOTFOUND) THEN
323 CLOSE c_thev;
324 RETURN TRUE;
325 END IF;
326 CLOSE c_thev;
327 ELSE
328 v_thesis_status := p_thesis_status;
329 END IF;
330 IF v_thesis_status NOT IN (
331 'EXAMINED',
332 'SUBMITTED') THEN
333 p_message_name := 'IGS_RE_CANT_ENTER_LIBR_DETAIL';
334 RETURN FALSE;
335 END IF;
336 END IF;
337 RETURN v_ret_val;
338 END;
339 EXCEPTION
340 WHEN OTHERS THEN
341 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END resp_val_the_lbry;
345 --
346 -- To validate the IGS_RE_THESIS result code
347 FUNCTION RESP_VAL_THE_THR(
348 p_person_id IN NUMBER ,
349 p_ca_sequence_number IN NUMBER ,
350 p_sequence_number IN NUMBER ,
351 p_thesis_result_cd IN VARCHAR2 ,
352 p_thesis_status IN VARCHAR2 ,
353 p_legacy IN VARCHAR2,
354 p_message_name OUT NOCOPY VARCHAR2 )
355 RETURN BOOLEAN AS
356 /* Change History :
357 Who When What
358 (reverse chronological order - newest change first)
359
360 Nishikant 15NOV2002 The function got modified to skip some validation in case
361 It has been called from Legacy API. And if any error message then
362 logs in the message stack and proceed further.
363 */
364 BEGIN -- resp_val_the_thr
365 -- Validate IGS_RE_THESIS.thesis_result_cd, checking for :
366 -- Closed result code
367 -- Cannot be set against PENDING IGS_RE_THESIS status
368 -- Cannot enter if outstanding submitted IGS_RE_THESIS examination
369 -- Warn if result not the same as the last IGS_RE_THESIS examination
370 -- Can only link to results which are flagged as 'final results'
371 DECLARE
372 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
373 v_dummy VARCHAR2(1);
374 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
375 v_closed_ind IGS_RE_THESIS_RESULT.closed_ind%TYPE;
376 v_final_result_ind IGS_LOOKUPS_VIEW.final_result_ind%TYPE;
377 v_thesis_result_cd IGS_RE_THESIS_EXAM.thesis_result_cd%TYPE;
378 CURSOR c_thev IS
379 SELECT thev.thesis_status
380 FROM IGS_RE_THESIS_V thev
381 WHERE thev.person_id = p_person_id AND
382 thev.ca_sequence_number = p_ca_sequence_number AND
383 thev.sequence_number = p_sequence_number;
384 CURSOR c_tex1 IS
385 SELECT 'x'
386 FROM IGS_RE_THESIS_EXAM tex
387 WHERE tex.person_id = p_person_id AND
388 tex.ca_sequence_number = p_ca_sequence_number AND
389 tex.the_sequence_number = p_sequence_number AND
390 tex.submission_dt IS NOT NULL AND
391 tex.thesis_result_cd IS NULL;
392 -- svenkata : Modified the WHERE clause of the query to match s_thesis_result_cd with the LOOKUP_CODE and not lookup_type .
393 -- Bug # 2146848 .
394 CURSOR c_thr_sthr IS
395 SELECT thr.closed_ind,
396 sthr.final_result_ind
397 FROM IGS_RE_THESIS_RESULT thr,
398 IGS_LOOKUPS_VIEW sthr
399 WHERE thr.thesis_result_cd = p_thesis_result_cd AND
400 sthr.LOOKUP_CODE = thr.s_thesis_result_cd
401 AND sthr.lookup_type = 'THESIS_RESULT';
402
403 CURSOR c_tex2 IS
404 SELECT tex.thesis_result_cd
405 FROM IGS_RE_THESIS_EXAM tex
406 WHERE tex.person_id = p_person_id AND
407 tex.ca_sequence_number = p_ca_sequence_number AND
408 tex.the_sequence_number = p_sequence_number AND
409 tex.submission_dt IS NOT NULL AND
410 tex.thesis_result_cd IS NOT NULL
411 ORDER BY tex.submission_dt DESC;
412 BEGIN
413 p_message_name := NULL;
414 IF p_thesis_result_cd IS NOT NULL THEN
415 -- 1. Cannot be PENDING IGS_RE_THESIS status (selected from view).
416 IF p_thesis_status IS NULL THEN
417 OPEN c_thev;
418 FETCH c_thev INTO v_thesis_status;
419 IF c_thev%NOTFOUND THEN
420 CLOSE c_thev;
421 --Invalid data ; will be picked up by calling routine
422 RETURN TRUE;
423 END IF;
424 CLOSE c_thev;
425 ELSE
426 v_thesis_status := p_thesis_status;
427 END IF;
428 IF v_thesis_status = cst_pending THEN
429 p_message_name := 'IGS_RE_CHK_RES_NOT_YET_EXAMIN';
430 IF p_legacy = 'Y' THEN
431 FND_MESSAGE.SET_NAME('IGS',p_message_name);
432 FND_MSG_PUB.ADD;
433 END IF;
434 RETURN FALSE;
435 END IF;
436 --2. Cannot enter if outstanding (submitted) IGS_RE_THESIS examination.
437 IF p_legacy <> 'Y' THEN --this validation is not required for legacy
438 OPEN c_tex1;
439 FETCH c_tex1 INTO v_dummy;
440 IF c_tex1%FOUND THEN
441 CLOSE c_tex1;
442 p_message_name := 'IGS_RE_CHK_RES_OUTSTAND_EXAM';
443 RETURN FALSE;
444 END IF;
445 CLOSE c_tex1;
446 END IF;
447 -- 3. Check for closed code.
448 OPEN c_thr_sthr;
449 FETCH c_thr_sthr INTO v_closed_ind,
450 v_final_result_ind;
451 IF c_thr_sthr%NOTFOUND THEN
452 CLOSE c_thr_sthr;
453 --Invalid data ; will be picked up by calling routine
454 RETURN TRUE;
455 END IF;
456 CLOSE c_thr_sthr;
457
458 IF p_legacy <> 'Y' THEN
459 IF v_closed_ind = 'Y' THEN
460 p_message_name := 'IGS_RE_THESIS_RESUILT_CLOSED';
461 RETURN FALSE;
462 END IF;
463 END IF;
464
465 --4. Must be a final result
466 IF v_final_result_ind = 'N' THEN
467 p_message_name := 'IGS_RE_CHK_RES_NOT_FINAL_RES';
468 IF p_legacy <> 'Y' THEN
469 RETURN FALSE;
470 ELSE
471 FND_MESSAGE.SET_NAME('IGS',p_message_name);
472 FND_MSG_PUB.ADD;
473 RETURN FALSE;
474 END IF;
475 END IF;
476 -- 5. Warn if result not the same as the latest examination record.
477 IF p_legacy <> 'Y' THEN --this validation is not required for legacy
478 OPEN c_tex2;
479 FETCH c_tex2 INTO v_thesis_result_cd;
480 IF c_tex2%FOUND AND v_thesis_result_cd <> p_thesis_result_cd THEN
481 CLOSE c_tex2;
482 p_message_name := 'IGS_RE_FINAL_RESULT_MISMATCH';
483 RETURN TRUE; --(Warning Only)
484 END IF;
485 CLOSE c_tex2;
486 END IF;
487 END IF;
488 RETURN TRUE;
489 EXCEPTION
490 WHEN OTHERS THEN
491 IF (c_thev%ISOPEN) THEN
492 CLOSE c_thev;
493 END IF;
494 IF (c_tex1%ISOPEN) THEN
495 CLOSE c_tex1;
496 END IF;
497 IF (c_thr_sthr%ISOPEN) THEN
498 CLOSE c_thr_sthr;
499 END IF;
500 IF (c_tex2%ISOPEN) THEN
501 CLOSE c_tex2;
502 END IF;
503 RAISE;
504 END;
505 EXCEPTION
506 WHEN OTHERS THEN
507 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 END resp_val_the_thr;
511 --
512 -- To validate the update of the IGS_RE_THESIS table.
513 FUNCTION RESP_VAL_THE_UPD(
514 p_logical_delete_dt IN DATE ,
515 p_message_name OUT NOCOPY VARCHAR2 )
516 RETURN BOOLEAN AS
517 BEGIN -- resp_val_the_upd
518 -- Validate for insert, update, delete of IGS_RE_THESIS, checking for :
519 -- Cannot update if logical_delete_dt is set.
520 DECLARE
521 BEGIN
522 IF p_logical_delete_dt IS NOT NULL THEN
523 p_message_name := 'IGS_RE_CANT_UPD_THESIS';
524 RETURN FALSE;
525 END IF;
526 p_message_name := NULL;
527 RETURN TRUE;
528 END;
529 EXCEPTION
530 WHEN OTHERS THEN
531 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception;
534 END resp_val_the_upd;
535 --
536 -- To validate the IGS_RE_THESIS IGS_PE_TITLE
537 FUNCTION RESP_VAL_THE_TTL(
538 p_old_title IN VARCHAR2 ,
539 p_new_title IN VARCHAR2 ,
540 p_thesis_result_cd IN VARCHAR2 ,
541 p_message_name OUT NOCOPY VARCHAR2 )
542 RETURN BOOLEAN AS
543 BEGIN -- resp_val_the_ttl
544 -- Validate for change of the IGS_RE_THESIS IGS_PE_TITLE, checking that it cannot be
545 -- changed once a final result has been entered
546 DECLARE
547 BEGIN
548 IF p_thesis_result_cd IS NOT NULL AND
549 (p_old_title IS NOT NULL AND
550 p_old_title <> NVL(p_new_title, 'NULL')) THEN
551 p_message_name := 'IGS_RE_CANT_ALTER_THESIS_TITL';
552 RETURN FALSE;
553 END IF;
554 p_message_name := NULL;
555 RETURN TRUE;
556 END;
557 EXCEPTION
558 WHEN OTHERS THEN
559 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception;
562 END resp_val_the_ttl;
563 --
564 -- To validate IGS_RE_THESIS finalised_title_indicator
565 FUNCTION RESP_VAL_THE_FNL(
566 p_person_id IN NUMBER ,
567 p_ca_sequence_number IN NUMBER ,
568 p_sequence_number IN NUMBER ,
569 p_final_title_ind IN VARCHAR2 ,
570 p_thesis_status IN VARCHAR2 ,
571 p_message_name OUT NOCOPY VARCHAR2 )
572 RETURN BOOLEAN AS
573 BEGIN -- resp_val_the_fnl
574 -- Validate the IGS_RE_THESIS.final_title_ind, checking for :
575 -- Cannot be unset once IGS_RE_THESIS is SUBMITTED or EXAMINED
576 DECLARE
577 cst_submitted CONSTANT varchar2(10) := 'SUBMITTED';
578 cst_examined CONSTANT varchar2(10) := 'EXAMINED';
579 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
580 CURSOR c_thev IS
581 SELECT thev.thesis_status
582 FROM IGS_RE_THESIS_V thev
583 WHERE thev.person_id = p_person_id AND
584 thev.ca_sequence_number = p_ca_sequence_number AND
585 thev.sequence_number = p_sequence_number;
586 BEGIN
587 p_message_name := NULL;
588 IF p_final_title_ind = 'N' THEN
589 IF p_thesis_status IS NULL THEN
590 OPEN c_thev;
591 FETCH c_thev INTO v_thesis_status;
592 IF c_thev%NOTFOUND THEN
593 CLOSE c_thev;
594 RETURN TRUE;
595 END IF;
596 CLOSE c_thev;
597 ELSE
598 v_thesis_status := p_thesis_status;
599 END IF;
600 IF v_thesis_status IN (
601 cst_submitted,
602 cst_examined) THEN
603 p_message_name := 'IGS_RE_CANT_UNSET_FIN_TIT_IND';
604 RETURN FALSE;
605 END IF;
606 END IF;
607 RETURN TRUE;
608 EXCEPTION
609 WHEN OTHERS THEN
610 IF (c_thev%ISOPEN) THEN
611 CLOSE c_thev;
612 END IF;
613 RAISE;
614 END;
615 EXCEPTION
616 WHEN OTHERS THEN
617 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
618 IGS_GE_MSG_STACK.ADD;
619 App_Exception.Raise_Exception;
620 END resp_val_the_fnl;
621
622 FUNCTION get_candidacy_dtls (
623 p_person_id IN NUMBER ,
624 p_course_cd IN VARCHAR2 ,
625 p_ca_sequence_number OUT NOCOPY NUMBER )
626 RETURN BOOLEAN IS
627 /*
628 || Created By : nbehera
629 || Created On : 14-NOV-2002
630 || Purpose : The function will check for a mapping ca_sequence_number in the table
631 || igs_re_candidature_all for the corresponding person_id and course_cd combination
632 || Known limitations, enhancements or remarks :
633 || Change History :
634 || Who When What
635 || (reverse chronological order - newest change first)
636 */
637
638 CURSOR c_ca_seq_number IS
639 SELECT sequence_number
640 FROM igs_re_candidature_all
641 WHERE sca_course_cd IS NOT NULL
642 AND sca_course_cd = p_course_cd
643 AND person_id = p_person_id;
644
645 BEGIN
646
647 OPEN c_ca_seq_number;
648 FETCH c_ca_seq_number INTO p_ca_sequence_number;
649
650 IF c_ca_seq_number%FOUND THEN
651 CLOSE c_ca_seq_number;
652 RETURN TRUE;
653 ELSE
654 CLOSE c_ca_seq_number;
655 RETURN FALSE;
656 END IF;
657 END get_candidacy_dtls ;
658
659 FUNCTION check_dup_thesis(
660 p_person_id IN NUMBER ,
661 p_title IN VARCHAR2 ,
662 p_ca_sequence_number IN NUMBER )
663 RETURN BOOLEAN IS
664 /*
665 || Created By : nbehera
666 || Created On : 14-NOV-2002
667 || Purpose : The function checks if the Title of the thesis record already exists for
668 || the given student.
669 || Known limitations, enhancements or remarks :
670 || Change History :
671 || Who When What
672 || (reverse chronological order - newest change first)
673 */
674 CURSOR c_title IS
675 SELECT title
676 FROM igs_re_thesis_all the,
677 igs_re_candidature re
678 WHERE the.person_id = p_person_id
679 AND the.title = p_title
680 AND re.sequence_number = p_ca_sequence_number
681 AND re.person_id = the.person_id;
682 l_title igs_re_thesis_all.title%TYPE;
683
684 BEGIN
685 OPEN c_title;
686 FETCH c_title INTO l_title;
687
688 IF c_title%FOUND THEN
689 CLOSE c_title;
690 RETURN FALSE;
691 ELSE
692 CLOSE c_title;
693 RETURN TRUE;
694 END IF ;
695 END check_dup_thesis;
696
697 FUNCTION eval_min_sub_dt (
698 p_expected_submission_date IN DATE,
699 p_ca_sequence_number IN NUMBER ,
700 p_person_id IN NUMBER)
701 RETURN BOOLEAN IS
702 /*
703 || Created By : nbehera
704 || Created On : 14-NOV-2002
705 || Purpose : The function checks the value of the Expected Submission date of the Thesis details
706 || against the value of Minimum submission date, as entered in the Candidacy Details Form.
707 || Known limitations, enhancements or remarks :
708 || Change History :
709 || Who When What
710 || (reverse chronological order - newest change first)
711 */
712
713 CURSOR c_min_subm_dt IS
714 SELECT min_submission_dt
715 FROM igs_re_candidature
716 WHERE sequence_number = p_ca_sequence_number
717 AND person_id = p_person_id;
718 l_min_sub_date igs_re_candidature.min_submission_dt%TYPE;
719
720 BEGIN
721
722 OPEN c_min_subm_dt;
723 FETCH c_min_subm_dt INTO l_min_sub_date;
724 CLOSE c_min_subm_dt;
725
726 --Expected submission date cannot be before the minimum submission date
727
728 IF p_expected_submission_date < l_min_sub_date THEN
729 RETURN FALSE;
730 ELSE
731 RETURN TRUE;
732 END IF;
733 END eval_min_sub_dt;
734
735
736 END IGS_RE_VAL_THE;
[Home] [Help]