1 PACKAGE BODY IGS_RE_VAL_RSUP AS
2 /* $Header: IGSRE11B.pls 120.3 2006/01/25 01:05:46 bdeviset ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --sarakshi 08-Sep-2004 Bug#3869178, modified resp_val_rsup_perc , such that funding and supervisor % are calculated correctly .
7 --smadathi 29-AUG-2001 Bug No. 1956374 .The Function genp_val_sdtt_sess removed
8 -- pradhakr 20-Nov-2002 Bug# 2661533. Created a new function to get the
9 -- organization start date for the given organisation unit code.
10 -- Added p_legacy paramter to some of the functions.
11 --ctyagi 11-April-2005 Bug No. 4287799 . Removed the cursor check_staff and modified the cursor c_funding.
12 --bdeviset 29-AUG-2005 Modified procedure resp_val_rsup_perc for bug# 4480892
13 --bdeviset 20-JAN-2006 Resolved Performance Issues related to bug# 4937664
14 -------------------------------------------------------------------------------------------
15 /*
16 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
17 || Removed program unit (RESP_VAL_CA_CHILDUPD) - from the spec and body. -- kdande
18 || Removed program unit (RESP_VAL_CA_TRG) - from the spec and body. -- kdande
19 */
20 --
21 -- Validate research supervisor principal at commencement.
22 FUNCTION resp_val_rsup_comm(
23 p_ca_person_id IN NUMBER ,
24 p_ca_sequence_number IN NUMBER ,
25 p_message_name OUT NOCOPY VARCHAR2 )
26 RETURN BOOLEAN AS
27 BEGIN -- resp_val_rsup_comm
28 -- This module validates that a principal supervisor exists
29 -- on the commencement date of the research IGS_RE_CANDIDATURE.
30 -- Validatios are:
31 -- * At least one IGS_RE_SPRVSR with
32 -- IGS_RE_SPRVSR_TYPE checked as a principal
33 -- must exist fir an offer where research details
34 -- are mandatory or where the IGS_PS_COURSE is defined as
35 -- a research supervisor.
36 DECLARE
37 v_commencement_dt DATE;
38 v_crv_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
39 v_candidature_exists_ind VARCHAR2(1);
40 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
41 CURSOR c_ca IS
42 SELECT ca.sca_course_cd,
43 ca.acai_admission_appl_number,
44 ca.acai_nominated_course_cd,
45 ca.acai_sequence_number
46 FROM IGS_RE_CANDIDATURE ca
47 WHERE ca.person_id = p_ca_person_id AND
48 ca.sequence_number = p_ca_sequence_number;
49 v_ca_rec c_ca%ROWTYPE;
50 CURSOR c_acai (
51 cp_admin_appl_num IGS_AD_PS_APPL.admission_appl_number%TYPE,
52 cp_nom_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
53 cp_seq_number IGS_AD_PS_APPL_INST.sequence_number%TYPE)
54 IS
55 SELECT acai.course_cd,
56 acai.crv_version_number,
57 acai.adm_outcome_status
58 FROM IGS_AD_PS_APPL_INST acai
59 WHERE acai.person_id = p_ca_person_id AND
60 acai.admission_appl_number = cp_admin_appl_num AND
61 acai.nominated_course_cd = cp_nom_course_cd AND
62 acai.sequence_number = cp_seq_number;
63 v_acai_rec c_acai%ROWTYPE;
64 CURSOR c_sca(
65 cp_course_cd IGS_PS_COURSE.course_cd%TYPE) IS
66 SELECT sca.version_number
67 FROM IGS_EN_STDNT_PS_ATT sca
68 WHERE sca.person_id = p_ca_person_id AND
69 sca.course_cd = cp_course_cd;
70 BEGIN
71 p_message_name := null;
72 OPEN c_ca;
73 FETCH c_ca INTO v_ca_rec;
74 CLOSE c_ca;
75 -- Get research commencement date
76 v_commencement_dt := IGS_RE_GEN_001.RESP_GET_CA_COMM (
77 p_ca_person_id,
78 v_ca_rec.sca_course_cd,
79 v_ca_rec.acai_admission_appl_number,
80 v_ca_rec.acai_nominated_course_cd,
81 v_ca_rec.acai_sequence_number);
82 IF v_commencement_dt IS NOT NULL THEN
83 IF v_ca_rec.sca_course_cd IS NOT NULL THEN
84 -- Get IGS_PS_COURSE attempt IGS_PS_COURSE version
85 OPEN c_sca(v_ca_rec.sca_course_cd);
86 FETCH c_sca INTO v_crv_version_number;
87 IF c_sca%NOTFOUND THEN
88 CLOSE c_sca;
89 RETURN TRUE;
90 END IF;
91 CLOSE c_sca;
92 END IF;
93 IF v_ca_rec.acai_admission_appl_number IS NOT NULL THEN
94 -- Get admission details.
95 OPEN c_acai(
96 v_ca_rec.acai_admission_appl_number,
97 v_ca_rec.acai_nominated_course_cd,
98 v_ca_rec.acai_sequence_number);
99 FETCH c_acai INTO v_acai_rec;
100 IF c_acai%NOTFOUND THEN
101 CLOSE c_acai;
102 RETURN TRUE;
103 ELSE
104 CLOSE c_acai;
105 IF v_ca_rec.sca_course_cd IS NULL THEN
106 v_ca_rec.sca_course_cd := v_acai_rec.course_cd;
107 v_crv_version_number := v_acai_rec.crv_version_number;
108 END IF;
109 END IF;
110 END IF;
111 v_ca_sequence_number := p_ca_sequence_number;
112 -- Validate existence of principal on commencement date.
113 IF NOT IGS_EN_VAL_SCA.admp_val_ca_comm(
114 p_ca_person_id,
115 v_ca_rec.sca_course_cd,
116 v_crv_version_number,
117 v_ca_rec.acai_admission_appl_number,
118 v_ca_rec.acai_nominated_course_cd,
119 v_ca_rec.acai_sequence_number,
120 v_acai_rec.adm_outcome_status,
121 v_commencement_dt,
122 NULL,
123 'RSUP',
124 v_ca_sequence_number,
125 v_candidature_exists_ind,
126 p_message_name) THEN
127 RETURN FALSE;
128 END IF;
129 END IF;
130 RETURN TRUE;
131 EXCEPTION
132 WHEN OTHERS THEN
133 IF c_ca%ISOPEN THEN
134 CLOSE c_ca;
135 END IF;
136 IF c_acai%ISOPEN THEN
137 CLOSE c_acai;
138 END IF;
139 IF c_sca%ISOPEN THEN
140 CLOSE c_sca;
141 END IF;
142 RAISE;
143 END;
144 EXCEPTION
145 WHEN OTHERS THEN
146 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END resp_val_rsup_comm;
150 --
151
152 -- Validate research supervisor percentage.
153 FUNCTION resp_val_rsup_perc(
154 p_ca_person_id IN NUMBER ,
155 p_ca_sequence_number IN NUMBER ,
156 p_sca_course_cd IN VARCHAR2 ,
157 p_acai_admission_appl_number IN NUMBER ,
158 p_acai_nominated_course_cd IN VARCHAR2 ,
159 p_acai_sequence_number IN NUMBER ,
160 p_val_supervision_perc_ind IN VARCHAR2 ,
161 p_val_funding_perc_ind IN VARCHAR2 ,
162 p_parent IN VARCHAR2 ,
163 p_supervision_start_dt OUT NOCOPY DATE ,
164 p_message_name OUT NOCOPY VARCHAR2 )
165 RETURN BOOLEAN AS
166 BEGIN -- resp_val_rsup_perc
167 -- This module validates IGS_RE_CANDIDATURE supervision. Validations are:
168 -- * supervision exists such that research load is covered
169 -- * supervision is 100% at all times from the start onwards
170 -- * funding exists such that research load is covered
171 -- * funding is 100% at all times from the start onwards
172 DECLARE
173 v_total_supervision_percentage NUMBER;
174 v_total_funding_percentage NUMBER;
175 v_exit_loop BOOLEAN := FALSE;
176 v_gap_exists BOOLEAN := FALSE;
177 v_rsup2_found VARCHAR2(1);
178 v_first_start_dt DATE;
179 cst_rsup CONSTANT VARCHAR2(5) := 'RSUP';
180 v_start_dt DATE;
181 v_end_dt DATE;
182 l_c_var VARCHAR2(1);
183 l_total_no_of_record NUMBER;
184 l_max_end_dt DATE;
185 l_date DATE;
186 v_enddt_total_sup_per NUMBER;
187
188 CURSOR c_rsup IS
189 SELECT rsup.start_dt, rsup.end_dt
190 FROM IGS_RE_SPRVSR rsup
191 WHERE rsup.ca_person_id = p_ca_person_id AND
192 rsup.ca_sequence_number = p_ca_sequence_number
193 ORDER BY
194 rsup.start_dt DESC;
195
196 -- cursor to find the sum of supervision percentage on the passed date
197 CURSOR c_rsup_per (
198 cp_date IGS_RE_SPRVSR.start_dt%TYPE) IS
199 SELECT NVL(SUM(rsup.supervision_percentage),0)
200 FROM IGS_RE_SPRVSR rsup
201 WHERE rsup.ca_person_id = p_ca_person_id AND
202 rsup.ca_sequence_number = p_ca_sequence_number AND
203 rsup.start_dt <= cp_date AND
204 (rsup.end_dt IS NULL OR
205 rsup.end_dt >= cp_date);
206
207 CURSOR c_funding IS
208 SELECT NVL(SUM(rsup.funding_percentage),0),count(*)
209 FROM IGS_RE_SPRVSR rsup,
210 igs_pe_person_types pt,
211 igs_pe_typ_instances pti
212 WHERE rsup.ca_person_id = p_ca_person_id
213 AND rsup.ca_sequence_number = p_ca_sequence_number
214 AND rsup.person_id = pti.person_id
215 AND pti.person_type_code = pt.person_type_code
216 AND pt.system_type = 'STAFF'
217 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
218
219 CURSOR c_rsup2 (
220 cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
221 SELECT 'x'
222 FROM IGS_RE_SPRVSR rsup
223 WHERE rsup.ca_person_id = p_ca_person_id AND
224 rsup.ca_sequence_number = p_ca_sequence_number AND
225 rsup.start_dt <= cp_start_dt AND
226 rsup.end_dt IS NOT NULL AND
227 rsup.end_dt >= cp_start_dt;
228 CURSOR c_rsup3 IS
229 SELECT rsup.start_dt,
230 rsup.end_dt
231 FROM IGS_RE_SPRVSR rsup
232 WHERE rsup.ca_person_id = p_ca_person_id AND
233 rsup.ca_sequence_number = p_ca_sequence_number
234 ORDER BY
235 rsup.start_dt,
236 rsup.end_dt;
237
238 -- cursor to get the latest supervsion end date
239 CURSOR c_get_max_end_dt IS
240 SELECT MAX(rsup.end_dt)
241 FROM IGS_RE_SPRVSR rsup
242 WHERE rsup.ca_person_id = p_ca_person_id AND
243 rsup.ca_sequence_number = p_ca_sequence_number;
244
245 -- cursor to find if a open end dated record exists
246 CURSOR c_chk_open_end_dt IS
247 SELECT rsup.end_dt
248 FROM IGS_RE_SPRVSR rsup
249 WHERE rsup.ca_person_id = p_ca_person_id AND
250 rsup.ca_sequence_number = p_ca_sequence_number AND
251 rsup.end_dt IS NULL;
252 BEGIN
253 p_message_name := NULL;
254 p_supervision_start_dt := NULL;
255 IF p_val_supervision_perc_ind = 'Y' OR
256 p_val_funding_perc_ind = 'Y' THEN
257 v_start_dt := NULL;
258 v_end_dt := NULL;
259 FOR v_rsup3_rec IN c_rsup3 LOOP
260 IF (c_rsup3%ROWCOUNT = 1) AND
261 v_rsup3_rec.end_dt IS NULL THEN
262 -- Research supervision is continuous.
263 EXIT;
264 END IF;
265 IF v_start_dt IS NULL THEN
266 -- Must be first record.
267 v_start_dt := v_rsup3_rec.start_dt;
268 v_end_dt := v_rsup3_rec.end_dt;
269 ELSE
270 IF v_start_dt <= v_rsup3_rec.start_dt AND
271 v_rsup3_rec.start_dt <= (v_end_dt + 1) THEN
272 IF v_rsup3_rec.end_dt > v_end_dt THEN
273 v_end_dt := v_rsup3_rec.end_dt;
274 END IF;
275 ELSE
276 -- There is a gap in research supervision.
277 p_message_name := 'IGS_RE_SUPERV_MUST_CONTINUE';
278 v_gap_exists := TRUE;
279 EXIT;
280 END IF;
281 END IF;
282 END LOOP;
283 IF v_gap_exists THEN
284 RETURN FALSE;
285 END IF;
286
287
288 --Funding pecentage must equal 100 at all times, for staff members only
289 IF p_val_funding_perc_ind = 'Y' THEN
290
291 OPEN c_funding;
292 FETCH c_funding INTO v_total_funding_percentage,l_total_no_of_record;
293
294 IF l_total_no_of_record = 0 THEN
295 CLOSE c_funding;
296
297 ELSE
298 IF v_total_funding_percentage <> 100 THEN
299 IF p_parent = cst_rsup THEN
300 p_message_name := 'IGS_RE_FUND_%_MUST_100_TIMES';
301 ELSE
302 p_message_name := 'IGS_RE_CAND_DETAIL_INCOMPLETE';
303 END IF;
304 CLOSE c_funding;
305 RETURN FALSE;
306 END IF;
307 CLOSE c_funding;
308 END IF;
309 END IF;
310
311 -- set the max end date
312 -- if an open end dated record exists then set the max end date to null
313 -- else to max end date
314 OPEN c_chk_open_end_dt;
315 FETCH c_chk_open_end_dt INTO l_max_end_dt;
316 IF c_chk_open_end_dt%NOTFOUND THEN
317 OPEN c_get_max_end_dt;
318 FETCH c_get_max_end_dt INTO l_max_end_dt;
319 CLOSE c_get_max_end_dt;
320 END IF;
321 CLOSE c_chk_open_end_dt;
322
323 FOR v_rsup_rec IN c_rsup LOOP
324
325
326 IF p_val_supervision_perc_ind = 'Y' THEN
327
328 -- 1.To ensure the supervsion is 100 on all days during the supervision check is made on start and next day of end date
329 -- if the end date is null then this check is not made
330 -- if the end date is same as max end date then the check is made on the end date only
331 OPEN c_rsup_per(v_rsup_rec.start_dt);
332 FETCH c_rsup_per INTO v_total_supervision_percentage;
333 CLOSE c_rsup_per;
334
335 v_enddt_total_sup_per := 100;
336 IF v_rsup_rec.end_dt IS NOT NULL THEN
337
338 IF l_max_end_dt IS NULL OR v_rsup_rec.end_dt < l_max_end_dt THEN
339 l_date := v_rsup_rec.end_dt + 1;
340 ELSE
341 l_date := v_rsup_rec.end_dt;
342 END IF;
343
344 OPEN c_rsup_per(l_date);
345 FETCH c_rsup_per INTO v_enddt_total_sup_per;
346 CLOSE c_rsup_per;
347
348 END IF;
349
350 IF v_total_supervision_percentage <> 100 OR v_enddt_total_sup_per <> 100 THEN
351 -- Supervision pecentage must equal 100
352 -- at all times.
353 IF p_parent = cst_rsup THEN
354 p_message_name := 'IGS_RE_CAND_SUPERV_%_MUST_100';
355 ELSE
356 p_message_name := 'IGS_RE_SUPERV_%_MUST_TOT_100';
357 END IF;
358 v_exit_loop := TRUE;
359 EXIT;
360 END IF;
361
362 END IF;
363 v_start_dt := v_rsup_rec.start_dt;
364
365 END LOOP; -- research supervisor
366 IF v_exit_loop THEN
367 RETURN FALSE;
368 END IF;
369 IF p_parent <> 'SCA' THEN
370 -- Check that fist start date is on or prior to
371 -- required IGS_RE_CANDIDATURE supervision start.
372 -- Get start date required for supervision
373 v_first_start_dt := IGS_RE_GEN_002.RESP_GET_RSUP_START (
374 p_ca_person_id,
375 p_ca_sequence_number,
376 p_sca_course_cd,
377 p_acai_admission_appl_number,
378 p_acai_nominated_course_cd,
379 p_acai_sequence_number,
380 p_parent);
381 IF v_first_start_dt IS NOT NULL AND
382 (v_start_dt IS NULL OR
383 v_first_start_dt < v_start_dt) THEN
384 IF p_parent = cst_rsup THEN
385 p_message_name := 'IGS_RE_SUPERV_MUST_EXIST';
386 ELSE
387 p_message_name := 'IGS_RE_CANDIDACY_DTL_INCOMPLETE';
388 END IF;
389 RETURN FALSE;
390 END IF;
391 p_supervision_start_dt := v_first_start_dt;
392 END IF;
393 END IF;
394 RETURN TRUE;
395 EXCEPTION
396 WHEN OTHERS THEN
397 IF c_rsup%ISOPEN THEN
398 CLOSE c_rsup;
399 END IF;
400 IF c_rsup2%ISOPEN THEN
401 CLOSE c_rsup2;
402 END IF;
403 IF c_rsup3%ISOPEN THEN
404 CLOSE c_rsup3;
405 END IF;
406 RAISE;
407 END;
408 END resp_val_rsup_perc;
409 --
410 -- Validate research supervisor IGS_PE_PERSON.
411 FUNCTION resp_val_rsup_person(
412 p_ca_person_id IN NUMBER ,
413 p_person_id IN NUMBER ,
414 p_legacy IN VARCHAR2,
415 p_message_name OUT NOCOPY VARCHAR2 )
416 RETURN BOOLEAN AS
417 BEGIN -- resp_val_rsup_person
418 -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:
419 -- Validations are:
420 -- * Cannot be the same as ca_person_id.
421 DECLARE
422 v_deceased_ind VARCHAR2(1);
423
424 BEGIN
425 -- Set the default message number
426 p_message_name := null;
427 IF p_person_id IS NOT NULL AND
428 p_ca_person_id IS NOT NULL THEN
429 IF p_person_id = p_ca_person_id THEN
430 p_message_name := 'IGS_RE_CHK_SUPERVISOR';
431 RETURN FALSE;
432 END IF;
433 END IF;
434
435 IF p_legacy = 'N' THEN
436 -- Validate that IGS_PE_PERSON exists and warn if deceased.
437 v_deceased_ind := IGS_PE_GEN_004.get_deceased_indicator( p_person_id );
438 IF v_deceased_ind = 'Y' THEN
439 p_message_name := 'IGS_GE_WARN_PERSON_DECEASED';
440 END IF;
441 END IF;
442
443 -- Return the default value
444 RETURN TRUE;
445 EXCEPTION
446 WHEN OTHERS THEN
447 RAISE;
448 END;
449 EXCEPTION
450 WHEN OTHERS THEN
451 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END resp_val_rsup_person;
455 --
456 -- Validate research supervisor principal.
457 FUNCTION resp_val_rsup_princ(
458 p_ca_person_id IN NUMBER ,
459 p_ca_sequence_number IN NUMBER ,
460 p_start_dt IN DATE ,
461 p_end_dt IN DATE ,
462 p_parent IN VARCHAR2 ,
463 p_message_name OUT NOCOPY VARCHAR2 )
464 RETURN BOOLEAN AS
465 BEGIN -- resp_val_rsup_princ
466 -- This module validates that research IGS_RE_CANDIDATURE has a principal supervisor
467 -- for the required time.
468 DECLARE
469 cst_rsup CONSTANT VARCHAR2(10) := 'RSUP';
470 v_start_dt IGS_RE_SPRVSR.start_dt%TYPE;
471 v_dummy VARCHAR2(1);
472 v_not_found BOOLEAN := FALSE;
473 v_rsup_exists BOOLEAN := FALSE;
474 CURSOR c_rsup IS
475 SELECT rsup.start_dt
476 FROM IGS_RE_SPRVSR rsup
477 WHERE rsup.ca_person_id = p_ca_person_id AND
478 rsup.ca_sequence_number = p_ca_sequence_number AND
479 (p_end_dt IS NULL OR
480 rsup.start_dt <= p_end_dt) AND
481 (rsup.end_dt IS NULL OR
482 rsup.end_dt >= p_start_dt)
483 ORDER BY rsup.start_dt;
484 CURSOR c_rsup_rst (
485 cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
486 SELECT 'X'
487 FROM IGS_RE_SPRVSR rsup,
488 IGS_RE_SPRVSR_TYPE rst
489 WHERE rsup.ca_person_id = p_ca_person_id AND
490 rsup.ca_sequence_number = p_ca_sequence_number AND
491 rsup.start_dt <= cp_start_dt AND
492 (rsup.end_dt IS NULL OR
493 rsup.end_dt >= cp_start_dt) AND
494 rsup.research_supervisor_type = rst.research_supervisor_type AND
495 rst.principal_supervisor_ind = 'Y';
496 BEGIN
497 -- Set the default message number
498 p_message_name := null;
499 IF p_start_dt IS NOT NULL THEN
500 FOR v_rsup_rec IN c_rsup LOOP
501 v_rsup_exists := TRUE;
502 IF v_rsup_rec.start_dt < p_start_dt THEN
503 v_start_dt := p_start_dt;
504 ELSE
505 v_start_dt := v_rsup_rec.start_dt;
506 END IF;
507 -- Validate that the principal supervisor exists
508 OPEN c_rsup_rst(
509 v_start_dt);
510 FETCH c_rsup_rst INTO v_dummy;
511 IF c_rsup_rst%NOTFOUND THEN
512 CLOSE c_rsup_rst;
513 IF p_parent = cst_rsup THEN
514 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
515 ELSE
516 p_message_name := 'IGS_RE_SPECIFY_PRIN_SUPERV';
517 END IF;
518 v_not_found := TRUE;
519 EXIT;
520 END IF;
521 CLOSE c_rsup_rst;
522 END LOOP;
523 IF NOT v_rsup_exists THEN
524 IF p_parent = cst_rsup THEN
525 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
526 ELSE
527 p_message_name := 'IGS_RE_SPECIFY_PRIN_SUPERV';
528 END IF;
529 v_not_found := TRUE;
530 END IF;
531 END IF;
532 IF v_not_found THEN
533 RETURN FALSE;
534 END IF;
535 RETURN TRUE;
536 EXCEPTION
537 WHEN OTHERS THEN
538 IF c_rsup%ISOPEN THEN
539 CLOSE c_rsup;
540 END IF;
541 IF c_rsup_rst%ISOPEN THEN
542 CLOSE c_rsup_rst;
543 END IF;
544 RAISE;
545 END;
546
547 END resp_val_rsup_princ;
548 --
549 -- Validate research supervisor replaced supervisor.
550 FUNCTION resp_val_rsup_repl(
551 p_ca_person_id IN NUMBER ,
552 p_ca_sequence_number IN NUMBER ,
553 p_person_id IN NUMBER ,
554 p_start_dt IN DATE ,
555 p_replaced_person_id IN NUMBER ,
556 p_replaced_sequence_number IN NUMBER ,
557 p_legacy IN VARCHAR2 ,
558 p_message_name OUT NOCOPY VARCHAR2 )
559 RETURN BOOLEAN AS
560 BEGIN -- resp_val_rsup_repl
561 -- This module validates IGS_RE_SPRVSR.replaced_person_id
562 -- IGS_RE_SPRVSR.replaced_start_dt. Validations are:
563 -- A supervisor cannot be replaced by themselves.
564 -- ie replaced_person_id <> person_id.
565 -- Replaced _person_id/_sequence_number has to be the latest occurrence
566 -- ie latest start_dt.
567 -- The replaced supervisor has to have an end date prior to
568 -- IGS_RE_SPRVSR.start_dt.
569 DECLARE
570 CURSOR c_rsup IS
571 SELECT rsup.sequence_number,
572 rsup.end_dt
573 FROM IGS_RE_SPRVSR rsup
574 WHERE rsup.ca_person_id = p_ca_person_id AND
575 rsup.ca_sequence_number = p_ca_sequence_number AND
576 rsup.person_id = p_replaced_person_id
577 ORDER BY rsup.start_dt DESC;
578 v_c_rsup_seq_num IGS_RE_SPRVSR.sequence_number%TYPE;
579 v_c_rsup_end_dt IGS_RE_SPRVSR.end_dt%TYPE;
580 BEGIN
581 -- Set the default message number
582 p_message_name := null;
583 IF p_replaced_person_id IS NOT NULL THEN
584 -- Validate that the supervisor and replacement are not the same IGS_PE_PERSON.
585 IF p_replaced_person_id = p_person_id THEN
586 p_message_name := 'IGS_RE_SUPERV_REPL_INVALID';
587 IF p_legacy = 'Y' THEN
588 FND_MESSAGE.SET_NAME('IGS', p_message_name);
589 FND_MSG_PUB.Add;
590 ELSE
591 RETURN FALSE;
592 END IF;
593 END IF;
594
595 -- Validate that replaced supervisor has an end_dt that is less than
596 -- the start_dt or the replacement supervisor
597 OPEN c_rsup;
598 FETCH c_rsup INTO v_c_rsup_seq_num,
599 v_c_rsup_end_dt;
600 IF c_rsup%NOTFOUND THEN
601 CLOSE c_rsup;
602 -- invalid parameters
603 p_message_name := 'IGS_GE_INVALID_VALUE';
604 IF p_legacy = 'Y' THEN
605 FND_MESSAGE.SET_NAME('IGS', p_message_name);
606 FND_MSG_PUB.Add;
607 ELSE
608 RETURN FALSE;
609 END IF;
610 ELSE
611 CLOSE c_rsup;
612 IF v_c_rsup_seq_num <> p_replaced_sequence_number THEN
613 -- not replacing latest occurence or research supervisor
614 p_message_name := 'IGS_RE_CHK_OCCURANCE_SUPERV';
615 IF p_legacy = 'Y' THEN
616 FND_MESSAGE.SET_NAME('IGS', p_message_name);
617 FND_MSG_PUB.Add;
618 ELSE
619 RETURN FALSE;
620 END IF;
621 END IF;
622 IF v_c_rsup_end_dt IS NULL THEN
623 -- replaced supervisor must have ended supervision
624 p_message_name := 'IGS_RE_END_DATE_NOT_NULL';
625 IF p_legacy = 'Y' THEN
626 FND_MESSAGE.SET_NAME('IGS', p_message_name);
627 FND_MSG_PUB.Add;
628 ELSE
629 RETURN FALSE;
630 END IF;
631 ELSIF v_c_rsup_end_dt >= p_start_dt THEN
632 -- replaced supervisor cannot overlap timeframe of replacing supervisor
633 p_message_name := 'IGS_RE_REPL_SUPERV_CANT_SUPER';
634 IF p_legacy = 'Y' THEN
635 FND_MSG_PUB.Add;
636 ELSE
637 RETURN FALSE;
638 END IF;
639 END IF; --IF v_c_rsup_end_dt IS NULL
640 END IF; -- if c_rsup%NOTFOUND
641 END IF; -- IF p_replaced_person_id IS NOT NULL
642 -- Return the default value
643 RETURN TRUE;
644 EXCEPTION
645 WHEN OTHERS THEN
646 IF c_rsup %ISOPEN THEN
647 CLOSE c_rsup;
648 END IF;
649 RAISE;
650 END;
651 EXCEPTION
652 WHEN OTHERS THEN
653 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
654 IGS_GE_MSG_STACK.ADD;
655 App_Exception.Raise_Exception;
656 END resp_val_rsup_repl;
657 --
658 -- Validate research supervisor funding percentage.
659 FUNCTION resp_val_rsup_fund(
660 p_person_id IN NUMBER ,
661 p_org_unit_cd IN VARCHAR2 ,
662 p_ou_start_dt IN DATE ,
663 p_funding_percentage IN NUMBER ,
664 p_staff_member_ind IN VARCHAR2 ,
665 p_legacy IN VARCHAR2 ,
666 p_message_name OUT NOCOPY VARCHAR2 )
667 RETURN BOOLEAN AS
668 BEGIN -- resp_val_rsup_fund
669 -- This module validates IGS_RE_SPRVSR.funding_percentage.
670 -- Validations are:
671 -- If funding percentage exists, then
672 -- organisational IGS_PS_UNIT must exist and be valid.
673 -- If funding percentage exists, then
674 -- supervisor must be a staff member.
675 DECLARE
676 v_staff_member_ind IGS_PE_PERSON.staff_member_ind%TYPE;
677 v_message_name VARCHAR2(30);
678
679 BEGIN
680 -- Set the default message number
681 p_message_name := NULL;
682
683 IF (p_staff_member_ind IS NULL) AND (p_legacy <> 'Y') THEN
684 -- determine if supervisor is a staff member
685 v_staff_member_ind := igs_en_gen_003.get_staff_ind(p_person_id);
686 ELSE
687 v_staff_member_ind := p_staff_member_ind;
688 END IF;
689
690
691 IF p_funding_percentage IS NOT NULL THEN
692 IF v_staff_member_ind = 'N' THEN
693 -- Only staff members req. funding percentage
694 p_message_name := 'IGS_RE_FUND_%_REQR_FOR_STAFF';
695 IF p_legacy <> 'Y' THEN
696 RETURN FALSE;
697 ELSE
698 FND_MESSAGE.SET_NAME('IGS', p_message_name);
699 FND_MSG_PUB.Add;
700 END IF;
701 END IF;
702
703 IF p_org_unit_cd IS NULL OR p_ou_start_dt IS NULL THEN
704 -- Organisational IGS_PS_UNIT must be specified with funding percentage.
705 p_message_name := 'IGS_RE_SPECIFY_ORG_UNIT';
706 IF p_legacy <> 'Y' THEN
707 RETURN FALSE;
708 ELSE
709 FND_MESSAGE.SET_NAME('IGS', p_message_name);
710 FND_MSG_PUB.Add;
711 END IF;
712 END IF;
713
714 IF p_legacy <> 'Y' THEN
715 -- Validate organisational IGS_PS_UNIT
716 IF NOT (IGS_RE_VAL_RSUP.resp_val_rsup_ou(
717 p_person_id,
718 p_org_unit_cd,
719 p_ou_start_dt,
720 p_staff_member_ind,
721 p_legacy,
722 p_message_name)) THEN
723 RETURN FALSE;
724 END IF;
725 END IF;
726
727 ELSE -- p_funding_percentage IS NULL
728 IF v_staff_member_ind = 'Y' THEN
729 p_message_name := 'IGS_RE_FUND_%_REQUIRED';
730 IF p_legacy <> 'Y' THEN
731 RETURN FALSE;
732 ELSE
733 FND_MESSAGE.SET_NAME('IGS', p_message_name);
734 FND_MSG_PUB.Add;
735 END IF;
736 END IF;
737 END IF;
738 -- Return the default value
739 RETURN TRUE;
740 EXCEPTION
741 WHEN OTHERS THEN
742 RAISE;
743 END;
744 EXCEPTION
745 WHEN OTHERS THEN
746 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
747 IGS_GE_MSG_STACK.ADD;
748 App_Exception.Raise_Exception;
749 END resp_val_rsup_fund;
750 --
751 -- Validate research supervisor organisational IGS_PS_UNIT.
752 FUNCTION resp_val_rsup_ou(
753 p_person_id IN NUMBER ,
754 p_org_unit_cd IN VARCHAR2 ,
755 p_ou_start_dt IN DATE ,
756 p_staff_member_ind IN VARCHAR2 ,
757 p_legacy IN VARCHAR2 ,
758 p_message_name OUT NOCOPY VARCHAR2 )
759 RETURN BOOLEAN AS
760 BEGIN -- resp_val_rsup_ou
761 -- This module validates IGS_RE_SPRVSR.org_unit_cd/ou_start_dt.
762 -- Organisational IGS_PS_UNIT must be active.
763 -- Organisational IGS_PS_UNIT must be local is supervisor is a staff member.
764 DECLARE
765 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
766 CURSOR c_ou_os_ins IS
767 SELECT os.s_org_status, ins.oi_local_institution_ind
768 FROM igs_or_status os,
769 igs_or_inst_org_base_v org,
770 igs_or_inst_org_base_v ins
771 WHERE org.inst_org_ind = 'O'
772 AND org.org_status = os.org_status
773 AND ins.inst_org_ind = 'I'
774 AND ins.party_number = org.ou_institution_cd
775 AND org.party_number = p_org_unit_cd
776 AND org.start_dt = p_ou_start_dt;
777
778 v_c_ooi_sos IGS_OR_STATUS.s_org_status%TYPE;
779 v_c_ooi_lii IGS_OR_INSTITUTION.local_institution_ind%TYPE;
780 v_staff_member_ind IGS_PE_PERSON.staff_member_ind%TYPE;
781 BEGIN
782 -- Set the default message number
783 p_message_name := NULL;
784 IF p_org_unit_cd IS NOT NULL AND p_ou_start_dt IS NOT NULL THEN
785 IF p_legacy <> 'Y' THEN
786 OPEN c_ou_os_ins;
787 FETCH c_ou_os_ins INTO v_c_ooi_sos,v_c_ooi_lii;
788 IF c_ou_os_ins%NOTFOUND THEN
789 CLOSE c_ou_os_ins;
790 RETURN TRUE;
791 END IF;
792 CLOSE c_ou_os_ins;
793 IF v_c_ooi_sos <> cst_active THEN
794 --must be organisational IGS_PS_UNIT
795 p_message_name := 'IGS_RE_ORG_UNIT_MUST_BE_ACTIV';
796 RETURN FALSE;
797 END IF;
798 IF p_staff_member_ind IS NULL THEN
799 v_staff_member_ind := igs_en_gen_003.get_staff_ind(p_person_id) ;
800 ELSE
801 v_staff_member_ind := p_staff_member_ind;
802 END IF;
803 END IF;
804
805 IF v_staff_member_ind = 'Y' THEN
806 -- Must be a local IGS_OR_INSTITUTION organisational IGS_PS_UNIT if
807 -- supervisor is a staff member.
808 IF v_c_ooi_lii ='N' THEN
809 p_message_name := 'IGS_RE_CHK_ORG_UNIT';
810 IF p_legacy <> 'Y' THEN
811 RETURN FALSE;
812 ELSE
813 FND_MESSAGE.SET_NAME('IGS', p_message_name);
814 FND_MSG_PUB.Add;
815 END IF;
816 END IF;
817 END IF;
818 END IF; -- p_org_unit_cd IS NOT NULL ...
819 -- Return the default value
820 RETURN TRUE;
821 EXCEPTION
822 WHEN OTHERS THEN
823 IF c_ou_os_ins%ISOPEN THEN
824 CLOSE c_ou_os_ins;
825 END IF;
826 RAISE;
827 END;
828 EXCEPTION
829 WHEN OTHERS THEN
830 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
831 IGS_GE_MSG_STACK.ADD;
832 App_Exception.Raise_Exception;
833 END resp_val_rsup_ou;
834 --
835 -- Validate research supervisor overlaps.
836 FUNCTION resp_val_rsup_ovrlp(
837 p_ca_person_id IN NUMBER ,
838 p_ca_sequence_number IN NUMBER ,
839 p_person_id IN NUMBER ,
840 p_sequence_number IN NUMBER ,
841 p_start_dt IN DATE ,
842 p_end_dt IN DATE ,
843 p_legacy IN VARCHAR2 ,
844 p_message_name OUT NOCOPY VARCHAR2 )
845 RETURN BOOLEAN AS
846 BEGIN -- resp_val_rsup_ovrlp
847 -- This module validates IGS_RE_SPRVSR.person_id overlaps.
848 DECLARE
849 CURSOR c_rsup IS
850 SELECT rsup.start_dt,
851 rsup.end_dt
852 FROM IGS_RE_SPRVSR rsup
853 WHERE rsup.ca_person_id = p_ca_person_id AND
854 rsup.ca_sequence_number = p_ca_sequence_number AND
855 rsup.person_id = p_person_id AND
856 (p_sequence_number IS NULL OR
857 rsup.sequence_number <> p_sequence_number)
858 ORDER BY rsup.start_dt desc;
859 BEGIN
860 -- Set the default message number
861 p_message_name := null;
862 FOR v_rsup_rec IN c_rsup LOOP
863 IF v_rsup_rec.end_dt IS NULL THEN
864 IF p_end_dt IS NULL OR
865 v_rsup_rec.start_dt <= p_end_dt THEN
866 p_message_name := 'IGS_RE_SUPERV_PER_OVERLAP';
867 IF p_legacy = 'Y' THEN
868 FND_MESSAGE.SET_NAME('IGS', p_message_name);
869 FND_MSG_PUB.Add;
870 ELSE
871 RETURN FALSE;
872 END IF;
873 END IF;
874 ELSIF v_rsup_rec.end_dt >= p_start_dt AND
875 (p_end_dt IS NULL OR
876 v_rsup_rec.start_dt <= p_end_dt) THEN
877 -- Supervision period must overlap
878 p_message_name := 'IGS_RE_SUPERV_PER_OVERLAP';
879 IF p_legacy = 'Y' THEN
880 FND_MESSAGE.SET_NAME('IGS', p_message_name);
881 FND_MSG_PUB.Add;
882 ELSE
883 RETURN FALSE;
884 END IF;
885 END IF;
886 END LOOP; --IGS_RE_SPRVSR
887 -- no records found.
888 -- Return the default value
889 RETURN TRUE;
890 EXCEPTION
891 WHEN OTHERS THEN
892 IF c_rsup %ISOPEN THEN
893 CLOSE c_rsup;
894 END IF;
895 RAISE;
896 END;
897 EXCEPTION
898 WHEN OTHERS THEN
899 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
900 IGS_GE_MSG_STACK.ADD;
901 App_Exception.Raise_Exception;
902 END resp_val_rsup_ovrlp;
903 --
904
905 --
906 -- Validate research supervisor end date.
907 FUNCTION resp_val_rsup_end_dt(
908 p_ca_person_id IN NUMBER ,
909 p_ca_sequence_number IN NUMBER ,
910 p_person_id IN NUMBER ,
911 p_sequence_number NUMBER ,
912 p_start_dt IN DATE ,
913 p_end_dt IN DATE ,
914 p_legacy IN VARCHAR2 ,
915 p_message_name OUT NOCOPY VARCHAR2 )
916 RETURN BOOLEAN AS
917 BEGIN -- resp_val_rsup_end_dt
918 -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:
919 -- end_dt >= start_dt
920 -- end_dt < any replacement supervisors
921 DECLARE
922 v_rsup_exists VARCHAR2(1);
923 CURSOR c_rsup IS
924 SELECT 'x'
925 FROM IGS_RE_SPRVSR rsup
926 WHERE rsup.ca_person_id = p_ca_person_id AND
927 rsup.ca_sequence_number = p_ca_sequence_number AND
928 rsup.replaced_person_id = p_person_id AND
929 rsup.replaced_sequence_number = p_sequence_number AND
930 (p_end_dt IS NULL OR
931 rsup.start_dt <= p_end_dt);
932 BEGIN
933 -- Set the default message number
934 p_message_name := null;
935 IF p_end_dt IS NOT NULL THEN
936 IF p_end_dt < p_start_dt THEN
937 p_message_name := 'IGS_GE_INVALID_DATE';
938 IF p_legacy <> 'Y' THEN
939 RETURN FALSE;
940 ELSE
941 FND_MESSAGE.SET_NAME('IGS', p_message_name);
942 FND_MSG_PUB.Add;
943 END IF;
944 END IF;
945 END IF;
946
947 IF p_legacy = 'N' THEN
948 IF p_person_id IS NOT NULL AND p_sequence_number IS NOT NULL THEN
949 OPEN c_rsup;
950 FETCH c_rsup INTO v_rsup_exists;
951 IF c_rsup%FOUND THEN
952 CLOSE c_rsup;
953 p_message_name := 'IGS_RE_END_DT_CANT_GE_ST_DT';
954 RETURN FALSE;
955 END IF;
956 CLOSE c_rsup;
957 END IF;
958 END IF;
959 -- Return the default value
960 RETURN TRUE;
961 EXCEPTION
962 WHEN OTHERS THEN
963 IF c_rsup %ISOPEN THEN
964 CLOSE c_rsup;
965 END IF;
966 RAISE;
967 END;
968 EXCEPTION
969 WHEN OTHERS THEN
970 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
971 IGS_GE_MSG_STACK.ADD;
972 App_Exception.Raise_Exception;
973 END resp_val_rsup_end_dt;
974 --
975 -- Validate if Research Supervisor Type is closed.
976 FUNCTION resp_val_rst_closed(
977 p_research_supervisor_type IN VARCHAR2 ,
978 p_message_name OUT NOCOPY VARCHAR2 )
979 RETURN BOOLEAN AS
980 BEGIN -- resp_val_rst_closed
981 -- Validate if IGS_RE_SPRVSR_TYPE.IGS_RE_SPRVSR_TYPE is closed.
982 DECLARE
983 CURSOR c_rst IS
984 SELECT 'x'
985 FROM IGS_RE_SPRVSR_TYPE rst
986 WHERE rst.research_supervisor_type = p_research_supervisor_type AND
987 rst.closed_ind = 'Y';
988 v_rst_exists VARCHAR2(1);
989 BEGIN
990 -- Set the default message number
991 p_message_name := null;
992 -- Cursor handling
993 OPEN c_rst;
994 FETCH c_rst INTO v_rst_exists;
995 IF c_rst %FOUND THEN
996 CLOSE c_rst;
997 p_message_name := 'IGS_RE_SUPERV_TYPE_CLOSED';
998 RETURN FALSE;
999 END IF;
1000 CLOSE c_rst;
1001 -- Return the default value
1002 RETURN TRUE;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 IF c_rst %ISOPEN THEN
1006 CLOSE c_rst;
1007 END IF;
1008 RAISE;
1009 END;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1013 IGS_GE_MSG_STACK.ADD;
1014 App_Exception.Raise_Exception;
1015 END resp_val_rst_closed;
1016
1017 -- Function to get the Start Date of the Organisation Unit.
1018 FUNCTION get_org_unit_dtls (
1019 p_org_unit_cd IN VARCHAR2,
1020 p_start_dt OUT NOCOPY DATE
1021 ) RETURN BOOLEAN IS
1022
1023 /**********************************************************************************************
1024 Created By : pradhakr
1025 Date Created By : 20-Nov-02
1026 Purpose : This function returns the organization start date for a given
1027 organisation unit code. Created as part of Research Supervisor Details build.
1028 Bug# 2661533
1029 Known limitations,enhancements,remarks:
1030 Change History
1031 Who When What
1032 ***********************************************************************************************/
1033
1034 -- Cursor to get the start date of the organization
1035 CURSOR c_org_date IS
1036 SELECT start_dt
1037 FROM igs_or_unit
1038 WHERE org_unit_cd = p_org_unit_cd;
1039
1040 l_start_dt igs_or_unit.start_dt%TYPE;
1041
1042 BEGIN
1043
1044 OPEN c_org_date;
1045 FETCH c_org_date INTO l_start_dt;
1046 CLOSE c_org_date;
1047
1048 IF l_start_dt IS NOT NULL THEN
1049 p_start_dt := l_start_dt;
1050 RETURN TRUE;
1051 ELSE
1052 p_start_dt := NULL;
1053 RETURN FALSE;
1054 END IF;
1055
1056 END get_org_unit_dtls;
1057
1058 END IGS_RE_VAL_RSUP;
[Home] [Help]