1 PACKAGE BODY IGS_GR_VAL_GR AS
2 /* $Header: IGSGR10B.pls 120.2 2006/02/21 00:54:09 sepalani noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function GRDP_VAL_AWARD_TYPE removed
7 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_aw_closed"
8 --Nalin Kumar 25-Oct-2002 Bug# 2640799- Modified the grdp_val_gr_rqrd and grdp_val_gr_type procedure
9 -- to remove the 'Graduand Type' check - as per the Conferral Date Build.
10 --ijeddy 06-Oct-2003 Build 3129913, Program completion Validation.
11 -------------------------------------------------------------------------------------------
12 -- Check if a specifc encumbrance effect applies to a person encumbrance
13 FUNCTION enrp_val_encmb_efct(
14 p_person_id HZ_PARTIES.party_id%TYPE ,
15 p_course_cd IGS_PS_COURSE.course_cd%TYPE ,
16 p_effective_dt DATE ,
17 p_encmb_effect_type IGS_EN_ENCMB_EFCTTYP.s_encmb_effect_type%TYPE ,
18 p_message_name OUT NOCOPY VARCHAR2 )
19 RETURN BOOLEAN AS
20 BEGIN -- enrp_val_encmb_efct
21 -- This routines checks if an encumbrance effect applies to a person.
22 DECLARE
23 v_pen_found BOOLEAN;
24 v_effect_exists BOOLEAN;
25 CURSOR c_pen IS
26 SELECT pen.encumbrance_type,
27 pen.start_dt
28 FROM IGS_PE_PERS_ENCUMB pen
29 WHERE pen.person_id = p_person_id AND
30 TRUNC(p_effective_dt) BETWEEN TRUNC(pen.start_dt) AND
31 TRUNC(NVL(pen.expiry_dt, p_effective_dt));
32 CURSOR c_pee_seet(
33 cp_pen_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
34 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE) IS
35 SELECT 'x'
36 FROM IGS_PE_PERSENC_EFFCT pee,
37 IGS_EN_ENCMB_EFCTTYP_V seet
38 WHERE pee.person_id = p_person_id AND
39 pee.encumbrance_type = cp_pen_encumbrance_type AND
40 TRUNC(pee.pen_start_dt) = TRUNC(cp_pen_start_dt) AND
41 pee.s_encmb_effect_type = p_encmb_effect_type AND
42 TRUNC(p_effective_dt) BETWEEN TRUNC(pee.pee_start_dt) AND
43 TRUNC(NVL(expiry_dt, p_effective_dt)) AND
44 seet.s_encmb_effect_type = pee.s_encmb_effect_type AND
45 ((seet.apply_to_course_ind = 'Y' AND
46 pee.course_cd = NVL(p_course_cd, pee.course_cd)) OR
47 seet.apply_to_course_ind = 'N');
48 v_pee_seet_exists VARCHAR2(1);
49 BEGIN
50 -- Set the default message number
51 p_message_name := NULL;
52 --1. Check parameters
53 IF p_person_id IS NULL OR
54 p_effective_dt IS NULL OR
55 p_encmb_effect_type IS NULL THEN
56 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
57 App_Exception.Raise_Exception;
58
59 END IF;
60 --2. Look for an encumbrance with a matching encumbrance type effect.
61 v_pen_found := FALSE;
62 v_effect_exists := FALSE;
63 FOR v_pen_rec IN c_pen LOOP
64 v_pen_found := TRUE;
65 OPEN c_pee_seet(
66 v_pen_rec.encumbrance_type,
67 v_pen_rec.start_dt);
68 FETCH c_pee_seet INTO v_pee_seet_exists;
69 IF c_pee_seet%FOUND THEN
70 CLOSE c_pee_seet;
71 --encumbrance effect type exists
72 v_effect_exists := TRUE;
73 Exit; -- quit from for loop
74 END IF;
75 CLOSE c_pee_seet;
76 END LOOP; -- c_pen
77 IF NOT v_pen_found
78 THEN
79 RETURN FALSE;
80 END IF;
81 IF v_effect_exists THEN
82 RETURN TRUE;
83 END IF;
84 --No person_encumbrance_effect's match the effect type
85 p_message_name := 'IGS_EN_PRSN_ENCUMB_EFFECTTYPE';
86 RETURN FALSE;
87 EXCEPTION
88 WHEN OTHERS THEN
89 IF c_pen%ISOPEN THEN
90 CLOSE c_pen;
91 END IF;
92 IF c_pee_seet%ISOPEN THEN
93 CLOSE c_pee_seet;
94 END IF;
95 RAISE;
96 END;
97 EXCEPTION
98 WHEN OTHERS THEN
99 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END enrp_val_encmb_efct;
103 --
104 -- Validate graduand student course attempt is a graduating course.
105 FUNCTION grdp_val_gr_sca(
106 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
107 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
108 p_message_name OUT NOCOPY VARCHAR2 )
109 RETURN BOOLEAN AS
110 BEGIN -- grdp_val_gr_sca
111 -- Validate the graduand record student course attempt is for a
112 -- course version that graduates students.
113 DECLARE
114 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
115 v_crv_exists VARCHAR2(1);
116 CURSOR c_sca IS
117 SELECT sca.version_number
118 FROM IGS_EN_STDNT_PS_ATT sca
119 WHERE sca.person_id = p_person_id AND
120 course_cd = p_course_cd;
121 CURSOR c_crv(
122 cp_version_number IGS_PS_VER.version_number%TYPE) IS
123 SELECT 'x'
124 FROM IGS_PS_VER crv
125 WHERE crv.course_cd = p_course_cd AND
126 crv.version_number = cp_version_number AND
127 crv.graduate_students_ind = 'Y';
128 BEGIN
129 -- Set the default message number
130 p_message_name := NULL;
131 IF p_person_id IS NULL OR p_course_cd IS NULL THEN
132 RETURN TRUE;
133 END IF;
134 -- Get the student course attempt course version number
135 OPEN c_sca;
136 FETCH c_sca INTO v_sca_version_number;
137 IF c_sca%NOTFOUND THEN
138 CLOSE c_sca;
139 RAISE NO_DATA_FOUND;
140 END IF;
141 CLOSE c_sca;
142 -- check course version graduates students
143 OPEN c_crv(v_sca_version_number);
144 FETCH c_crv INTO v_crv_exists;
145 IF c_crv%NOTFOUND THEN
146 CLOSE c_crv;
147 p_message_name := 'IGS_GR_DOES_NOT_GRAD_STUDENTS';
148 RETURN FALSE;
149 END IF;
150 CLOSE c_crv;
151 -- Return the default value
152 RETURN TRUE;
153 EXCEPTION
154 WHEN OTHERS THEN
155 IF c_sca%ISOPEN THEN
156 CLOSE c_sca;
157 END IF;
158 IF c_crv%ISOPEN THEN
159 CLOSE c_crv;
160 END IF;
161 RAISE;
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 grdp_val_gr_sca;
169 --
170 -- Validate Graduand Ceremony Round calendar instance.
171 FUNCTION grdp_val_gr_crd_ci(
172 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
173 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
174 p_message_name OUT NOCOPY VARCHAR2 )
175 RETURN BOOLEAN AS
176 BEGIN -- grdp_val_gr_crd_ci
177 -- Validate that the graduand is linked
178 -- to a ceremony_round that has an ACTIVE or PLANNED calendar instance.
179 DECLARE
180 CURSOR c_ci_cs IS
181 SELECT 'x'
182 FROM IGS_CA_INST ci,
183 IGS_CA_STAT cs
184 WHERE ci.cal_type = p_grd_cal_type AND
185 ci.sequence_number = p_grd_ci_sequence_number AND
186 cs.cal_status = ci.cal_status AND
187 cs.s_cal_status IN ('ACTIVE', 'PLANNED');
188 v_ci_cs_exists VARCHAR2(1);
189 BEGIN
190 -- Set the default message number
191 p_message_name := NULL;
192 IF p_grd_cal_type IS NULL OR p_grd_ci_sequence_number IS NULL THEN
193 RETURN TRUE;
194 END IF;
195 OPEN c_ci_cs;
196 FETCH c_ci_cs INTO v_ci_cs_exists;
197 IF c_ci_cs %NOTFOUND THEN
198 CLOSE c_ci_cs;
199 p_message_name :='IGS_GR_INSTANC_ACTIVE_PLANNED';
200 RETURN FALSE;
201 END IF;
202 CLOSE c_ci_cs;
203 -- Return the default value
204 RETURN TRUE;
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF c_ci_cs %ISOPEN THEN
208 CLOSE c_ci_cs;
209 END IF;
210 RAISE;
211 END;
212 EXCEPTION
213 WHEN OTHERS THEN
214 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END grdp_val_gr_crd_ci;
218 --
219 -- Validate GRADUAND required details.
220 FUNCTION grdp_val_gr_rqrd(
221 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
222 p_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
223 p_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
224 p_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
225 p_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
226 p_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
227 p_honours_level VARCHAR2 DEFAULT NULL,
228 p_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
229 p_sur_for_crs_version_number IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
230 p_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
231 p_conferral_dt IGS_GR_GRADUAND_V.conferral_dt%TYPE DEFAULT NULL,
232 p_message_name OUT NOCOPY VARCHAR2 )
233 RETURN BOOLEAN AS
234 BEGIN -- grdp_val_gr_rqrd
235 -- Validate the graduand record details:
236 -- * When s_graduand_status = 'GRADUATED' or 'SURRENDER', s_graduand_type
237 -- cannot be 'UNKNOWN'and conferral_dt must be set.
238 -- * When graduand is linked to a student_course_attempt a course_award must
239 -- be specified else an honorary award.
240 -- * An honorary award cannot be surrendered.
241 -- * When s_graduand_status = 'SURRENDER' or s_graduand_type = 'ARTICULATE'
242 -- surrendering course award is required.
243 -- * Honour level can only be specified when a course award is being conferred
244 DECLARE
245 cst_graduated CONSTANT VARCHAR2(10) := 'GRADUATED';
246 cst_surrender CONSTANT VARCHAR2(10) := 'SURRENDER';
247 cst_unknown CONSTANT VARCHAR2(10) := 'UNKNOWN';
248 cst_deferred CONSTANT VARCHAR2(10) := 'DEFERRED';
249 cst_articulate CONSTANT VARCHAR2(10) := 'ARTICULATE';
250 cst_honorary CONSTANT VARCHAR2(10) := 'HONORARY';
251 v_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
252 CURSOR c_gst IS
253 SELECT gst.s_graduand_status
254 FROM IGS_GR_STAT gst
255 WHERE gst.graduand_status = p_graduand_status;
256 BEGIN
257 -- Set the default message number
258 p_message_name := NULL;
259 -- Check parameters
260 IF p_graduand_status IS NULL OR
261 p_s_graduand_type IS NULL OR
262 p_award_cd IS NULL THEN
263 RETURN TRUE;
264 END IF;
265 -- Get the system graduand status
266 BEGIN
267 OPEN c_gst;
268 FETCH c_gst INTO v_s_graduand_status;
269 IF c_gst%NOTFOUND THEN
270 CLOSE c_gst;
271 RAISE NO_DATA_FOUND;
272 ELSE
273 CLOSE c_gst;
274 END IF;
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception;
280 END;
281 -- Check if course award details are required
282 IF p_course_cd IS NULL THEN
283 -- Honorary award
284 IF p_award_course_cd IS NOT NULL OR
285 p_award_crs_version_number IS NOT NULL THEN
286 p_message_name := 'IGS_GR_ONLY_HNRY_AWD';
287 RETURN FALSE;
288 END IF;
289 ELSE
290 -- Course award
291 IF p_award_course_cd IS NULL OR
292 p_award_crs_version_number IS NULL THEN
293 p_message_name := 'IGS_GR_SPECIFY_COURSE_AWD';
294 RETURN FALSE;
295 END IF;
296 END IF;
297 -- Check if surrendering course details are required
298 IF p_course_cd IS NULL THEN
299 -- Honorary award
300 IF p_sur_for_course_cd IS NOT NULL OR
301 p_sur_for_crs_version_number IS NOT NULL OR
302 p_sur_for_award_cd IS NOT NULL THEN
303 p_message_name := 'IGS_GR_INVALID_HNRY_AWD';
304 RETURN FALSE;
305 END IF;
306 END IF;
307 IF v_s_graduand_status = cst_surrender OR
308 p_s_graduand_type = cst_articulate THEN
309 IF p_sur_for_course_cd IS NULL OR
310 p_sur_for_crs_version_number IS NULL OR
311 p_sur_for_award_cd IS NULL THEN
312 p_message_name := 'IGS_GR_COURS_AWD_MUST_BE_SPEC';
313 RETURN FALSE;
314 END IF;
315 ELSE
316 IF p_sur_for_course_cd IS NOT NULL OR
317 p_sur_for_crs_version_number IS NOT NULL OR
318 p_sur_for_award_cd IS NOT NULL THEN
319 p_message_name := 'IGS_GR_CHECK_SURR_COURS_AWD';
320 RETURN FALSE;
321 END IF;
322 END IF;
323 RETURN TRUE;
324 EXCEPTION
325 WHEN OTHERS THEN
326 IF c_gst%ISOPEN THEN
327 CLOSE c_gst;
328 END IF;
329 RAISE;
330 END;
331 EXCEPTION
332 WHEN OTHERS THEN
333 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END grdp_val_gr_rqrd;
337 --
338 -- Validate graduand status.
339 FUNCTION grdp_val_gr_gst(
340 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
341 p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
342 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
343 p_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
344 p_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
345 p_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
346 p_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
347 p_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
348 p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
349 p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
350 p_message_name OUT NOCOPY VARCHAR2 )
351 RETURN BOOLEAN AS
352 BEGIN -- grdp_val_gr_gst
353 -- This routine validates the setting of graduand.graduand_status
354 DECLARE
355 v_s_graduand_appr_status IGS_GR_APRV_STAT.s_graduand_appr_status%TYPE;
356 v_new_s_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE;
357 v_old_s_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE;
358 cst_potential CONSTANT VARCHAR2(12) := 'POTENTIAL';
359 cst_graduated CONSTANT VARCHAR2(12) := 'GRADUATED';
360 cst_eligible CONSTANT VARCHAR2(12) := 'ELIGIBLE';
361 cst_approved CONSTANT VARCHAR2(12) := 'APPROVED';
362 cst_surrender CONSTANT VARCHAR2(12) := 'SURRENDER';
363 cst_attending CONSTANT VARCHAR2(12) := 'ATTENDING';
364 v_exit_loop BOOLEAN;
365 CURSOR c_gas IS
366 SELECT gas.s_graduand_appr_status
367 FROM IGS_GR_APRV_STAT gas
368 WHERE gas.graduand_appr_status = p_graduand_appr_status;
369 CURSOR c_gst (
370 cp_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE)
371 IS
372 SELECT gst.s_graduand_status
373 FROM IGS_GR_STAT gst
374 WHERE gst.graduand_status = cp_graduand_status;
375 CURSOR c_gac IS
376 SELECT gac.grd_cal_type,
377 gac.grd_ci_sequence_number,
378 gac.ceremony_number,
379 gac.us_group_number
380 FROM IGS_GR_AWD_CRMN gac
381 WHERE gac.person_id = p_person_id AND
382 gac.create_dt = p_create_dt AND
383 gac.award_course_cd = p_award_course_cd AND
384 gac.award_crs_version_number
385 = p_award_crs_version_number AND
386 gac.award_cd = p_award_cd;
387 BEGIN
388 p_message_name := NULL;
389 v_exit_loop := FALSE;
390 -- Check parameters
391 IF p_person_id IS NULL OR
392 p_create_dt IS NULL OR
393 p_course_cd IS NULL OR
394 p_graduand_appr_status IS NULL OR
395 p_s_graduand_type IS NULL OR
396 p_award_cd IS NULL OR
397 p_new_graduand_status IS NULL THEN
398 RETURN TRUE;
399 END IF;
400 -- validate change of graduand status
401 IF p_new_graduand_status = NVL(p_old_graduand_status,'NULL') THEN
402 RETURN TRUE;
403 END IF;
404 -- get the system status values.
405 OPEN c_gas;
406 FETCH c_gas INTO v_s_graduand_appr_status;
407 CLOSE c_gas;
408 OPEN c_gst(p_new_graduand_status);
409 FETCH c_gst INTO v_new_s_graduand_status;
410 CLOSE c_gst;
411 IF p_old_graduand_status IS NOT NULL THEN
412 OPEN c_gst(p_old_graduand_status);
413 FETCH c_gst INTO v_old_s_graduand_status;
414 CLOSE c_gst;
415 END IF;
416 -- validate the graduand status
417 IF v_new_s_graduand_status = cst_potential THEN
418 IF p_old_graduand_status IS NULL THEN
419 RETURN TRUE;
420 END IF;
421 IF v_old_s_graduand_status IN (cst_graduated,
422 cst_surrender) THEN
423 p_message_name := 'IGS_GR_STATUS_CANNOT_BE_CHANG';
424 RETURN FALSE;
425 END IF;
426 END IF;
427 IF v_new_s_graduand_status = cst_eligible THEN
428 IF p_old_graduand_status IS NOT NULL THEN
429 IF v_old_s_graduand_status IN (cst_graduated,
430 cst_surrender) THEN
431 p_message_name := 'IGS_GR_STATUS_CANNOT_BE_CHANG';
432 RETURN FALSE;
433 END IF;
434 END IF;
435 -- check eligibility
436 IF NOT IGS_GR_VAL_GR.grdp_val_aw_eligible(
437 p_person_id,
438 p_course_cd,
439 p_award_course_cd,
440 p_award_crs_version_number,
441 p_award_cd,
442 p_message_name) THEN
443 RETURN FALSE;
444 END IF;
445 IF p_s_graduand_type = cst_attending THEN
446 -- check primary unit sets are complete
447 -- when a determinant for attendance at
448 -- a ceremony.
449 FOR v_gac_rec IN c_gac LOOP
450 IF NOT IGS_GR_VAL_GAC.grdp_val_gac_susa(
451 p_person_id,
452 p_create_dt,
453 v_gac_rec.grd_cal_type,
454 v_gac_rec.grd_ci_sequence_number,
455 p_course_cd,
456 p_new_graduand_status,
457 v_gac_rec.ceremony_number,
458 p_award_course_cd,
459 p_award_crs_version_number,
460 p_award_cd,
461 v_gac_rec.us_group_number,
462 p_message_name) THEN
463 v_exit_loop := TRUE;
464 Exit;
465 END IF;
466 END LOOP;
467 IF v_exit_loop THEN
468 RETURN FALSE;
469 END IF;
470 END IF;
471 END IF;
472 IF v_new_s_graduand_status = cst_graduated THEN
473 IF v_s_graduand_appr_status <> cst_approved THEN
474 p_message_name := 'IGS_GR_SYSTEM_VAL_MUST_BE_APP';
475 RETURN FALSE;
476 END IF;
477 IF p_old_graduand_status IS NULL OR
478 v_old_s_graduand_status NOT IN (cst_eligible,
479 cst_surrender) THEN
480 IF NOT IGS_GR_VAL_GR.grdp_val_aw_eligible(
481 p_person_id,
482 p_course_cd,
483 p_award_course_cd,
484 p_award_crs_version_number,
485 p_award_cd,
486 p_message_name) THEN
487 RETURN FALSE;
488 END IF;
489 IF p_s_graduand_type = cst_attending THEN
490 -- check primary unit sets are complete
491 -- when a determinant for attendance at
492 -- a ceremony.
493 FOR v_gac_rec IN c_gac LOOP
494 IF NOT IGS_GR_VAL_GAC.grdp_val_gac_susa(
495 p_person_id,
496 p_create_dt,
497 v_gac_rec.grd_cal_type,
498 v_gac_rec.grd_ci_sequence_number,
499 p_course_cd,
500 p_new_graduand_status,
501 v_gac_rec.ceremony_number,
502 p_award_course_cd,
503 p_award_crs_version_number,
504 p_award_cd,
505 v_gac_rec.us_group_number,
506 p_message_name) THEN
507 v_exit_loop := TRUE;
508 Exit;
509 END IF;
510 END LOOP;
511 IF v_exit_loop THEN
512 RETURN FALSE;
513 END IF;
514 END IF;
515 END IF;
516 END IF;
517 IF v_new_s_graduand_status = cst_surrender THEN
518 IF p_old_graduand_status IS NULL OR
519 v_old_s_graduand_status <> cst_graduated THEN
520 p_message_name := 'IGS_GR_AWD_NOT_GIVEN_PRIOR';
521 RETURN FALSE;
522 END IF;
523 END IF;
524 RETURN TRUE;
525 EXCEPTION
526 WHEN OTHERS THEN
527 IF c_gas%ISOPEN THEN
528 CLOSE c_gas;
529 END IF;
530 IF c_gac%iSOPEN THEN
531 CLOSE c_gac;
532 END IF;
533 IF c_gst%ISOPEN THEN
534 CLOSE c_gst;
535 END IF;
536 RAISE;
537 END;
538 EXCEPTION
539 WHEN OTHERS THEN
540 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
541 IGS_GE_MSG_STACK.ADD;
542 App_Exception.Raise_Exception;
543 END grdp_val_gr_gst;
544 --
545 -- Validate graduand approval status.
546 FUNCTION grdp_val_gr_gas(
547 p_person_id IN HZ_PARTIES.party_id%TYPE ,
548 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
549 p_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
550 p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
551 p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
552 p_message_name OUT NOCOPY VARCHAR2 )
553 RETURN BOOLEAN AS
554 BEGIN -- grdp_val_gr_gas
555 DECLARE
556 v_gst_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
557 v_gas_s_graduand_appr_status IGS_GR_APRV_STAT.s_graduand_appr_status%TYPE;
558 cst_graduated CONSTANT VARCHAR2(9) := 'GRADUATED';
559 cst_surrender CONSTANT VARCHAR2(9) := 'SURRENDER';
560 cst_approved CONSTANT VARCHAR2(8) := 'APPROVED';
561 CURSOR c_gst IS
562 SELECT gst.s_graduand_status
563 FROM IGS_GR_STAT gst
564 WHERE gst.graduand_status = p_graduand_status;
565 CURSOR c_gas IS
566 SELECT gas.s_graduand_appr_status
567 FROM IGS_GR_APRV_STAT gas
568 WHERE gas.graduand_appr_status = p_new_graduand_appr_status;
569 BEGIN
570 -- Set the default message number
571 p_message_name := NULL;
572 --1. Check parameters :
573 IF p_graduand_status IS NULL OR
574 p_new_graduand_appr_status IS NULL THEN
575 RETURN TRUE;
576 END IF;
577 -- 2.Validate change of graduand approval status
578 IF p_new_graduand_appr_status = NVL(p_old_graduand_appr_status, 'NULL') THEN
579 RETURN TRUE;
580 END IF;
581 --check the graduand hasn't already graduated
582 OPEN c_gst;
583 FETCH c_gst INTO v_gst_s_graduand_status;
584 IF c_gst%NOTFOUND THEN
585 CLOSE c_gst;
586 RAISE NO_DATA_FOUND;
587 END IF;
588 CLOSE c_gst;
589 OPEN c_gas;
590 FETCH c_gas INTO v_gas_s_graduand_appr_status;
591 IF c_gas%NOTFOUND THEN
592 CLOSE c_gas;
593 RAISE NO_DATA_FOUND;
594 END IF;
595 CLOSE c_gas;
596 IF v_gst_s_graduand_status IN ( cst_graduated,
597 cst_surrender) THEN
598 IF v_gas_s_graduand_appr_status <> cst_approved THEN
599 p_message_name := 'IGS_GR_MUST_HAVE_VALUE_APPROV';
600 RETURN FALSE;
601 END IF;
602 END IF;
603 -- check no encumbrances restrict approval
604 IF v_gas_s_graduand_appr_status = cst_approved THEN
605 IF IGS_GR_VAL_GR.enrp_val_encmb_efct(
606 p_person_id,
607 p_course_cd,
608 SYSDATE,
609 'GRAD_BLK',
610 p_message_name) = TRUE THEN
611 p_message_name := 'IGS_GR_CANNOT_BE_APPROVED';
612 RETURN FALSE;
613 END IF;
614 END IF;
615 --3. Return no error:
616 RETURN TRUE;
617 EXCEPTION
618 WHEN OTHERS THEN
619 IF c_gst %ISOPEN THEN
620 CLOSE c_gst;
621 END IF;
622 IF c_gas %ISOPEN THEN
623 CLOSE c_gas;
624 END IF;
625 RAISE;
626 END;
627 EXCEPTION
628 WHEN OTHERS THEN
629 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
630 IGS_GE_MSG_STACK.ADD;
631 App_Exception.Raise_Exception;
632 END grdp_val_gr_gas;
633 --
634 -- Validate system graduand type.
635 FUNCTION GRDP_VAL_GR_TYPE(
636 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
637 p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
638 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
639 p_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
640 p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
641 p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
642 p_message_name OUT NOCOPY VARCHAR2 )
643 RETURN BOOLEAN AS
644 BEGIN -- grdp_val_gr_type
645 DECLARE
646 v_gst_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
647 cst_graduated CONSTANT IGS_GR_STAT.graduand_status%TYPE
648 := 'GRADUATED';
649 cst_surrender CONSTANT IGS_GR_STAT.graduand_status%TYPE
650 := 'SURRENDER';
651 cst_unknown CONSTANT IGS_GR_STAT.graduand_status%TYPE
652 := 'UNKNOWN';
653 cst_deferred CONSTANT IGS_GR_STAT.graduand_status%TYPE
654 := 'DEFERRED';
655 cst_attending CONSTANT IGS_GR_STAT.graduand_status%TYPE
656 := 'ATTENDING';
657 cst_inabsentia CONSTANT IGS_GR_STAT.graduand_status%TYPE
658 := 'INABSENTIA';
659 cst_eligible CONSTANT IGS_GR_STAT.graduand_status%TYPE
660 := 'ELIGIBLE';
661 v_should_return_false BOOLEAN;
662 CURSOR c_gst IS
663 SELECT gst.s_graduand_status
664 FROM IGS_GR_STAT gst
665 WHERE gst.graduand_status = p_graduand_status;
666 CURSOR c_gac IS
667 SELECT 'x'
668 FROM IGS_GR_AWD_CRMN gac
669 WHERE gac.person_id = p_person_id AND
670 gac.create_dt = p_create_dt;
671 CURSOR c_gac2 IS
672 SELECT gac.grd_cal_type,
673 gac.grd_ci_sequence_number,
674 gac.ceremony_number,
675 gac.award_course_cd,
676 gac.award_crs_version_number,
677 gac.award_cd,
678 gac.us_group_number
679 FROM IGS_GR_AWD_CRMN gac
680 WHERE gac.person_id = p_person_id AND
681 gac.create_dt = p_create_dt;
682 v_gac_exists VARCHAR2(1);
683 BEGIN
684 -- Set the default message number
685 p_message_name := NULL;
686 --1. Check parameters :
687 IF p_person_id IS NULL OR
688 p_create_dt IS NULL OR
689 p_graduand_status IS NULL OR
690 p_new_s_graduand_type IS NULL THEN
691 RETURN TRUE;
692 END IF;
693 --2. Validate change of graduand type
694 IF p_new_s_graduand_type = NVL(p_old_s_graduand_type, 'NULL') THEN
695 RETURN TRUE;
696 END IF;
697 --check the graduand hasn't already graduated
698 OPEN c_gst;
699 FETCH c_gst INTO v_gst_s_graduand_status;
700 IF c_gst%NOTFOUND THEN
701 CLOSE c_gst;
702 RAISE NO_DATA_FOUND;
703 END IF;
704 CLOSE c_gst;
705 --ijeddy, Bug 2996721, 9 June 2003, commented the following If block
706 /* IF v_gst_s_graduand_status IN(
707 cst_graduated,
708 cst_surrender) THEN
709 IF p_old_s_graduand_type IS NOT NULL THEN
710 --not setting the value FOR the first time
711 p_message_name := 'IGS_GR_GRAD_CANNOT_BE_CHANGED';
712 RETURN FALSE;
713 END IF;
714 ELSE
715 */
716 IF p_new_s_graduand_type NOT IN(
717 cst_attending,
718 cst_inabsentia,
719 cst_unknown) THEN
720 --check no related graduand award ceremonies exist
721 OPEN c_gac;
722 FETCH c_gac INTO v_gac_exists;
723 IF c_gac%FOUND THEN
724 CLOSE c_gac;
725 p_message_name := 'IGS_GR_CHECK_GRAD_TYPE';
726 RETURN FALSE;
727 END IF;
728 CLOSE c_gac;
729 ELSE
730 IF p_new_s_graduand_type = cst_attending AND
731 v_gst_s_graduand_status = cst_eligible THEN
732 --check primary unit sets are complete when a determinant
733 -- for attendance at a ceremony
734 v_should_return_false :=FALSE;
735 FOR v_gac2_rec IN c_gac2 LOOP
736 IF IGS_GR_VAL_GAC.grdp_val_gac_susa(
737 p_person_id,
738 p_create_dt,
739 v_gac2_rec.grd_cal_type,
740 v_gac2_rec.grd_ci_sequence_number,
741 p_course_cd,
742 p_graduand_status,
743 v_gac2_rec.ceremony_number,
744 v_gac2_rec.award_course_cd,
745 v_gac2_rec.award_crs_version_number,
746 v_gac2_rec.award_cd,
747 v_gac2_rec.us_group_number,
748 p_message_name) = FALSE THEN
749 v_should_return_false :=TRUE;
750 Exit;
751 END IF;
752 END LOOP;
753 IF v_should_return_false THEN
754 RETURN FALSE;
755 END IF;
756 END IF;
757 END IF;
758 --ijeddy, Bug 2996721, 9 June 2003, commented the following End If
759 -- END IF;
760 --3. Return no error:
761 RETURN TRUE;
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF c_gst %ISOPEN THEN
765 CLOSE c_gst;
766 END IF;
767 IF c_gac %ISOPEN THEN
768 CLOSE c_gac;
769 END IF;
770 IF c_gac2 %ISOPEN THEN
771 CLOSE c_gac2;
772 END IF;
773 RAISE;
774 END;
775 EXCEPTION
776 WHEN OTHERS THEN
777 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
778 IGS_GE_MSG_STACK.ADD;
779 App_Exception.Raise_Exception;
780 END grdp_val_gr_type;
781 --
782 -- Validate proxy details.
783 FUNCTION grdp_val_gr_proxy(
784 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
785 p_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
786 p_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
787 p_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
788 p_message_name OUT NOCOPY VARCHAR2 )
789 RETURN BOOLEAN AS
790 BEGIN -- grdp_val_gr_proxy
791 -- Validate the graduand record proxy details.
792 DECLARE
793 cst_attending CONSTANT IGS_GR_GRADUAND.s_graduand_type%TYPE := 'ATTENDING';
794 v_pe_exists VARCHAR2(1);
795 CURSOR c_pe(
796 cp_id IGS_PE_PERSON_BASE_V.person_id%TYPE) IS
797 SELECT 'x'
798 FROM IGS_PE_PERSON_BASE_V pe
799 WHERE pe.person_id = cp_id AND
800 pe.date_of_death is not NULL;
801 BEGIN
802 -- Set the default message number
803 p_message_name := NULL;
804 -- Check Parameters
805 IF p_person_id IS NULL OR
806 p_s_graduand_type IS NULL OR
807 p_proxy_award_ind IS NULL THEN
808 RETURN TRUE;
809 END IF;
810 IF p_proxy_award_ind = 'Y' THEN
811 IF p_proxy_award_person_id IS NULL THEN
812 p_message_name := 'IGS_GR_SPECIFY_PRXY_AWD_PERS';
813 RETURN FALSE;
814 ELSE
815 -- Check the proxy person is not the graduand.
816 IF p_proxy_award_person_id = p_person_id THEN
817 p_message_name := 'IGS_GR_PRXY_AWD_MUST_BE_DIFF';
818 RETURN FALSE;
819 ELSE
820 -- Check the proxy person is not deceased.
821 OPEN c_pe(p_proxy_award_person_id);
822 FETCH c_pe INTO v_pe_exists;
823 IF c_pe%FOUND THEN
824 CLOSE c_pe;
825 p_message_name := 'IGS_GR_PRXY_PERS_DECEASED';
826 RETURN FALSE;
827 END IF;
828 CLOSE c_pe;
829 END IF;
830 -- Check the graduand type is ATTENDING
831 IF p_s_graduand_type <> cst_attending THEN
832 p_message_name := 'IGS_GR_TYPE_MUST_BE_ATTENDING';
833 RETURN TRUE; -- Warning only
834 END IF;
835 END IF;
836 ELSE -- p_proxy_award_ind = 'N'
837 -- no proxy
838 IF p_proxy_award_person_id IS NOT NULL THEN
839 p_message_name := 'IGS_GR_PRXY_AWD_NOT_NEEDED';
840 RETURN FALSE;
841 ELSE
842 OPEN c_pe(p_person_id);
843 FETCH c_pe INTO v_pe_exists;
844 IF c_pe%FOUND AND p_s_graduand_type = cst_attending THEN
845 CLOSE c_pe;
846 p_message_name := 'IGS_GR_REQUIRES_PRXY_FOR_CERM';
847 RETURN FALSE;
848 END IF;
849 CLOSE c_pe;
850 END IF;
851 END IF;
852 -- Return the default value
853 RETURN TRUE;
854 EXCEPTION
855 WHEN OTHERS THEN
856 IF c_pe%ISOPEN THEN
857 CLOSE c_pe;
858 END IF;
859 RAISE;
860 END;
861 EXCEPTION
862 WHEN OTHERS THEN
863 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
864 IGS_GE_MSG_STACK.ADD;
865 App_Exception.Raise_Exception;
866 END grdp_val_gr_proxy;
867 --
868 -- Check for multiple instances of the same award for the person.
869 FUNCTION grdp_val_gr_unique(
870 p_person_id IN IGS_GR_GRADUAND_ALL.person_id%TYPE ,
871 p_create_dt IN IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
872 p_grd_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
873 p_grd_ci_sequence_num IN NUMBER ,
874 p_award_course_cd IN IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
875 p_award_crs_version_number IN IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
876 p_award_cd IN IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
877 p_message_name OUT NOCOPY VARCHAR2 )
878 RETURN BOOLEAN AS
879 BEGIN -- grdp_val_gr_unique
880 -- Validate that the graduand record is unique.
881 -- Note, both warnings and errors may result
882 -- from this routine.
883 DECLARE
884 cst_graduated CONSTANT VARCHAR2(12) := 'GRADUATED';
885 cst_surrender CONSTANT VARCHAR2(12) := 'SURRENDER';
886 v_gst_found VARCHAR2(1);
887 CURSOR c_gr IS
888 SELECT gr.grd_cal_type,
889 gr.grd_ci_sequence_number,
890 gr.graduand_status
891 FROM IGS_GR_GRADUAND gr
892 WHERE gr.person_id = p_person_id AND
893 gr.create_dt <> p_create_dt AND
894 gr.award_cd = p_award_cd AND
895 NVL(gr.award_course_cd, 'NULL') = NVL(p_award_course_cd,'NULL') AND
896 NVL(gr.award_crs_version_number,0) = NVL(p_award_crs_version_number,0);
897 CURSOR c_gst (
898 cp_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE)
899 IS
900 SELECT 'x'
901 FROM IGS_GR_STAT gst
902 WHERE gst.graduand_status = cp_graduand_status AND
903 gst.s_graduand_status IN (cst_graduated,
904 cst_surrender);
905 BEGIN
906 -- Set the default message number
907 p_message_name := NULL;
908 -- NULL parameter check
909 IF p_person_id IS NULL OR
910 p_create_dt IS NULL OR
911 p_grd_cal_type IS NULL OR
912 p_grd_ci_sequence_num IS NULL OR
913 p_award_cd IS NULL THEN
914 RETURN TRUE;
915 END IF;
916 -- Test for equivalent graduand records
917 FOR v_gr_rec IN c_gr LOOP
918 OPEN c_gst(v_gr_rec.graduand_status);
919 FETCH c_gst INTO v_gst_found;
920 IF c_gst%FOUND THEN
921 CLOSE c_gst;
922 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
923 RETURN FALSE;
924 ELSE
925 CLOSE c_gst;
926 IF v_gr_rec.grd_cal_type = p_grd_cal_type AND
927 v_gr_rec.grd_ci_sequence_number = p_grd_ci_sequence_num THEN
928 p_message_name := 'IGS_GR_GRAD_DETAIL_EXISTS';
929 RETURN FALSE;
930 ELSE
931 -- warning only
932 p_message_name := 'IGS_GR_GRD_AWD_EXISTS';
933 END IF;
934 END IF;
935 END LOOP;
936 -- Return the default value
937 RETURN TRUE;
938 EXCEPTION
939 WHEN OTHERS THEN
940 IF c_gr%ISOPEN THEN
941 CLOSE c_gr;
942 END IF;
943 IF c_gst%ISOPEN THEN
944 CLOSE c_gst;
945 END IF;
946 RAISE;
947 END;
948 EXCEPTION
949 WHEN OTHERS THEN
950 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
951 IGS_GE_MSG_STACK.ADD;
952 App_Exception.Raise_Exception;
953 END grdp_val_gr_unique;
954 --
955 -- Validate the update of a graduand with graduand awards ceremonies.
956 FUNCTION grdp_val_gr_upd(
957 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
958 p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
959 p_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
960 p_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
961 p_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
962 p_message_name OUT NOCOPY VARCHAR2 )
963 RETURN BOOLEAN AS
964 BEGIN -- grdp_val_gr_upd
965 -- Validate that the update of a graduand record does not occur
966 -- after the graduation_ceremony ceremony or closing date.
967 -- Note, warnings only result from failure of the validations.
968 DECLARE
969 CURSOR c_gac IS
970 SELECT gac.grd_cal_type,
971 gac.grd_ci_sequence_number,
972 gac.ceremony_number
973 FROM IGS_GR_AWD_CRMN gac
974 WHERE gac.person_id = p_person_id AND
975 gac.create_dt = p_create_dt AND
976 gac.award_cd = p_award_cd AND
977 NVL(gac.award_course_cd, 'NULL') = NVL(p_award_course_cd,'NULL') AND
978 NVL(gac.award_crs_version_number,0) = NVL(p_award_crs_version_number,0);
979 v_gac_rec c_gac%ROWTYPE;
980 CURSOR c_gc(
981 cp_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE,
982 cp_grd_ci_sequence_number
983 IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE,
984 cp_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE) IS
985 SELECT gc.ceremony_dt_alias,
986 gc.ceremony_dai_sequence_number,
987 gc.closing_dt_alias,
988 gc.closing_dai_sequence_number
989 FROM IGS_GR_CRMN gc
990 WHERE gc.grd_cal_type = cp_grd_cal_type AND
991 gc.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
992 gc.ceremony_number = cp_ceremony_number;
993 v_gc_rec c_gc%ROWTYPE;
994 v_ceremony_dt DATE DEFAULT NULL;
995 v_closing_dt DATE DEFAULT NULL;
996 BEGIN
997 -- Set the default message number
998 p_message_name := NULL;
999 -- NULL parameter check
1000 IF p_person_id IS NULL OR
1001 p_create_dt IS NULL OR
1002 p_award_cd IS NULL THEN
1003 RETURN TRUE;
1004 END IF;
1005 -- NOTE the date checks below are warnings only, hence the return of TRUE
1006 FOR v_gac_rec IN c_gac LOOP
1007 OPEN c_gc(
1008 v_gac_rec.grd_cal_type,
1009 v_gac_rec.grd_ci_sequence_number,
1010 v_gac_rec.ceremony_number);
1011 FETCH c_gc INTO v_gc_rec;
1012 CLOSE c_gc;
1013 v_ceremony_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
1014 v_gc_rec.ceremony_dt_alias,
1015 v_gc_rec.ceremony_dai_sequence_number,
1016 v_gac_rec.grd_cal_type,
1017 v_gac_rec.grd_ci_sequence_number);
1018 IF TRUNC(SYSDATE) > TRUNC(v_ceremony_dt) THEN
1019 p_message_name := 'IGS_GR_INV_DT_GRAD_CERM';
1020 RETURN TRUE;
1021 END IF;
1022 v_closing_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
1023 v_gc_rec.closing_dt_alias,
1024 v_gc_rec.closing_dai_sequence_number,
1025 v_gac_rec.grd_cal_type,
1026 v_gac_rec.grd_ci_sequence_number);
1027 IF TRUNC(SYSDATE) > TRUNC(v_closing_dt) THEN
1028 p_message_name := 'IGS_GR_CLOSING_DT_REACHED';
1029 RETURN TRUE;
1030 END IF;
1031 END LOOP;
1032 -- Return the default value
1033 RETURN TRUE;
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 IF c_gac %ISOPEN THEN
1037 CLOSE c_gac;
1038 END IF;
1039 IF c_gc %ISOPEN THEN
1040 CLOSE c_gc;
1041 END IF;
1042 RAISE;
1043 END;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1047 IGS_GE_MSG_STACK.ADD;
1048 App_Exception.Raise_Exception;
1049 END grdp_val_gr_upd;
1050 --
1051 -- Validate inserting or updating a graduand.
1052 FUNCTION grdp_val_gr_iu(
1053 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
1054 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
1055 p_message_name OUT NOCOPY VARCHAR2 )
1056 RETURN BOOLEAN AS
1057 BEGIN -- grdp_val_gac_iu
1058 -- Validate that the insert or update of a graduand record
1059 -- does not fall outside the ceremony round processing period.
1060 DECLARE
1061 CURSOR c_crd IS
1062 SELECT crd.start_dt_alias,
1063 crd.start_dai_sequence_number,
1064 crd.end_dt_alias,
1065 crd.end_dai_sequence_number
1066 FROM IGS_GR_CRMN_ROUND crd
1067 WHERE crd.grd_cal_type = p_grd_cal_type AND
1068 crd.grd_ci_sequence_number = p_grd_ci_sequence_number;
1069 v_crd_rec c_crd%ROWTYPE;
1070 v_start_dt DATE DEFAULT NULL;
1071 v_end_dt DATE DEFAULT NULL;
1072 BEGIN
1073 -- Set the default message number
1074 p_message_name := NULL;
1075 IF p_grd_cal_type IS NULL OR p_grd_ci_sequence_number IS NULL THEN
1076 RETURN TRUE;
1077 END IF;
1078 OPEN c_crd;
1079 FETCH c_crd INTO v_crd_rec;
1080 IF c_crd%FOUND THEN
1081 CLOSE c_crd;
1082 v_start_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
1083 v_crd_rec.start_dt_alias,
1084 v_crd_rec.start_dai_sequence_number,
1085 p_grd_cal_type,
1086 p_grd_ci_sequence_number);
1087 v_end_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
1088 v_crd_rec.end_dt_alias,
1089 v_crd_rec.end_dai_sequence_number,
1090 p_grd_cal_type,
1091 p_grd_ci_sequence_number);
1092 IF v_start_dt IS NULL OR v_end_dt IS NULL THEN
1093 p_message_name := NULL;
1094 RETURN TRUE;
1095 END IF;
1096 IF TRUNC(SYSDATE) < TRUNC(v_start_dt) OR
1097 TRUNC(SYSDATE) > TRUNC(v_end_dt) THEN
1098 p_message_name := 'IGS_GR_CUR_DT_OUTSIDE_CERROUN';
1099 RETURN TRUE;
1100 END IF;
1101 ELSE
1102 CLOSE c_crd;
1103 END IF;
1104 -- Return the default value
1105 RETURN TRUE;
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 IF c_crd %ISOPEN THEN
1109 CLOSE c_crd;
1110 END IF;
1111 RAISE;
1112 END;
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1116 IGS_GE_MSG_STACK.ADD;
1117 App_Exception.Raise_Exception;
1118 END grdp_val_gr_iu;
1119 --
1120 -- Validate the graduand has satisfied academic requirements for an award
1121 FUNCTION grdp_val_aw_eligible(
1122 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
1123 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
1124 p_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
1125 p_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
1126 p_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
1127 p_message_name OUT NOCOPY VARCHAR2 )
1128 RETURN BOOLEAN AS
1129 BEGIN -- grdp_val_aw_eligible
1130 -- Validate the graduand is academically eligible for the award.
1131 DECLARE
1132 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1133 v_sca_crs_rqrmnt_ind IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE;
1134 CURSOR c_sca IS
1135 SELECT sca.version_number,
1136 sca.course_rqrmnt_complete_ind
1137 FROM IGS_EN_STDNT_PS_ATT sca
1138 WHERE sca.person_id = p_person_id AND
1139 sca.course_cd = p_course_cd;
1140 v_scaae_exists VARCHAR2(1);
1141 CURSOR c_scaae
1142 (cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
1143 SELECT 'x'
1144 FROM IGS_PS_STDNT_APV_ALT scaae
1145 WHERE scaae.person_id = p_person_id AND
1146 scaae.course_cd = p_course_cd AND
1147 scaae.version_number = cp_sca_version_number AND
1148 scaae.exit_course_cd = p_award_course_cd AND
1149 scaae.exit_version_number = p_award_crs_version_number AND
1150 scaae.rqrmnts_complete_ind = 'Y';
1151 BEGIN
1152 -- Set the default message number
1153 p_message_name := NULL;
1154 --1. Check parameters :
1155 IF p_person_id IS NULL OR
1156 p_award_cd IS NULL OR
1157 (p_course_cd IS NOT NULL AND
1158 (p_award_course_cd IS NULL OR
1159 p_award_crs_version_number IS NULL)) THEN
1160 Fnd_Message.Set_Name('IGS', 'IGS_GE_INVALID_VALUE');
1161 IGS_GE_MSG_STACK.ADD;
1162 App_Exception.Raise_Exception;
1163 END IF;
1164 --2. Check if an honorary award rather than a course award is being given
1165 IF p_course_cd IS NULL THEN
1166 RETURN TRUE;
1167 END IF;
1168 --3. Match award to student course attempt
1169 OPEN c_sca;
1170 FETCH c_sca INTO
1171 v_sca_version_number,
1172 v_sca_crs_rqrmnt_ind;
1173 IF c_sca%NOTFOUND THEN
1174 CLOSE c_sca;
1175 RAISE NO_DATA_FOUND;
1176 END IF;
1177 CLOSE c_sca;
1178 IF p_award_course_cd = p_course_cd AND
1179 p_award_crs_version_number = v_sca_version_number THEN
1180 IF v_sca_crs_rqrmnt_ind = 'Y' THEN
1181 RETURN TRUE;
1182 ELSE
1183 p_message_name := 'IGS_GR_COURSE_REQIR_NOT_COMPL';
1184 RETURN FALSE;
1185 END IF;
1186 ELSE
1187 --check for a match with an approved alternative exit
1188 OPEN c_scaae(v_sca_version_number);
1189 FETCH c_scaae INTO v_scaae_exists;
1190 IF c_scaae%NOTFOUND THEN
1191 CLOSE c_scaae;
1192 p_message_name := 'IGS_GR_NOT_APPRV_EXIT';
1193 RETURN FALSE;
1194 ELSE
1195 CLOSE c_scaae;
1196 RETURN TRUE;
1197 END IF;
1198 END IF;
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 IF c_sca %ISOPEN THEN
1202 CLOSE c_sca;
1203 END IF;
1204 IF c_scaae %ISOPEN THEN
1205 CLOSE c_scaae;
1206 END IF;
1207 RAISE;
1208 END;
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1212 IGS_GE_MSG_STACK.ADD;
1213 App_Exception.Raise_Exception;
1214 END grdp_val_aw_eligible;
1215 --
1216 -- Validate graduand course award.
1217 FUNCTION grdp_val_gr_caw(
1218 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
1219 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
1220 p_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
1221 p_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
1222 p_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
1223 p_message_name OUT NOCOPY VARCHAR2 )
1224 RETURN BOOLEAN AS
1225 BEGIN -- grdp_val_gr_caw
1226 -- Validate the graduand record course award is an award for the
1227 -- student course attempt or an alternative exit.
1228 DECLARE
1229 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1230 v_ae_exists VARCHAR2(1);
1231 CURSOR c_sca IS
1232 SELECT sca.version_number
1233 FROM IGS_EN_STDNT_PS_ATT sca
1234 WHERE sca.person_id = p_person_id AND
1235 course_cd = p_course_cd;
1236 CURSOR c_ae(
1237 cp_version_number IGS_PE_ALTERNATV_EXT.version_number%TYPE) IS
1238 SELECT 'x'
1239 FROM IGS_PE_ALTERNATV_EXT ae
1240 WHERE ae.course_cd = p_course_cd AND
1241 ae.version_number = cp_version_number AND
1242 ae.exit_course_cd = p_award_course_cd;
1243 BEGIN
1244 -- Set the default message number
1245 p_message_name := NULL;
1246 IF p_person_id IS NULL OR
1247 p_course_cd IS NULL OR
1248 p_award_cd IS NULL THEN
1249 RETURN TRUE;
1250 END IF;
1251 -- Get the student course attempt course version number
1252 OPEN c_sca;
1253 FETCH c_sca INTO v_sca_version_number;
1254 IF c_sca%NOTFOUND THEN
1255 CLOSE c_sca;
1256 RAISE NO_DATA_FOUND;
1257 END IF;
1258 CLOSE c_sca;
1259 IF p_award_course_cd <> p_course_cd OR
1260 p_award_crs_version_number <> v_sca_version_number THEN
1261 -- check for a match with an alternative exit
1262 OPEN c_ae(v_sca_version_number);
1263 FETCH c_ae INTO v_ae_exists;
1264 IF c_ae%NOTFOUND THEN
1265 CLOSE c_ae;
1266 p_message_name := 'IGS_GR_NOT_A_VALID_AWARD';
1267 RETURN FALSE;
1268 END IF;
1269 CLOSE c_ae;
1270 END IF;
1271 -- Return the default value
1272 RETURN TRUE;
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 IF c_sca%ISOPEN THEN
1276 CLOSE c_sca;
1277 END IF;
1278 IF c_ae%ISOPEN THEN
1279 CLOSE c_ae;
1280 END IF;
1281 RAISE;
1282 END;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1286 IGS_GE_MSG_STACK.ADD;
1287 App_Exception.Raise_Exception;
1288 END grdp_val_gr_caw;
1289 --
1290 -- Validate if graduand approval status is closed.
1291 FUNCTION grdp_val_gas_closed(
1292 p_graduand_appr_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE ,
1293 p_message_name OUT NOCOPY VARCHAR2 )
1294 RETURN BOOLEAN AS
1295 BEGIN -- grdp_val_gas_closed
1296 -- Validate if the graduand approval status is closed
1297 DECLARE
1298 v_gas_found VARCHAR2(1);
1299 CURSOR c_gas IS
1300 SELECT 'x'
1301 FROM IGS_GR_APRV_STAT gas
1302 WHERE gas.graduand_appr_status = p_graduand_appr_status AND
1303 gas.closed_ind = 'Y';
1304 BEGIN
1305 p_message_name := NULL;
1306 OPEN c_gas;
1307 FETCH c_gas INTO v_gas_found;
1308 IF (c_gas%FOUND) THEN
1309 CLOSE c_gas;
1310 p_message_name := 'IGS_GR_GRAD_APPR_STATUS_CLOSE';
1311 RETURN FALSE;
1312 END IF;
1313 CLOSE c_gas;
1314 RETURN TRUE;
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 IF c_gas%ISOPEN THEN
1318 CLOSE c_gas;
1319 END IF;
1320 RAISE;
1321 END;
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1325 IGS_GE_MSG_STACK.ADD;
1326 App_Exception.Raise_Exception;
1327 END grdp_val_gas_closed;
1328 --
1329 -- Validate if graduand status is closed.
1330 FUNCTION grdp_val_gst_closed(
1331 p_graduand_status IGS_GR_STAT.graduand_status%TYPE ,
1332 p_message_name OUT NOCOPY VARCHAR2 )
1333 RETURN BOOLEAN AS
1334 BEGIN -- grdp_val_gst_closed
1335 -- Validate if the graduand status is closed
1336 DECLARE
1337 v_gst_found VARCHAR2(1);
1338 CURSOR c_gst IS
1339 SELECT 'x'
1340 FROM IGS_GR_STAT gst
1341 WHERE gst.graduand_status = p_graduand_status AND
1342 gst.closed_ind = 'Y';
1343 BEGIN
1344 p_message_name := NULL;
1345 OPEN c_gst;
1346 FETCH c_gst INTO v_gst_found;
1347 IF (c_gst%FOUND) THEN
1348 CLOSE c_gst;
1349 p_message_name := 'IGS_GR_GRAD_STATUS_CLOSED';
1350 RETURN FALSE;
1351 END IF;
1352 CLOSE c_gst;
1353 RETURN TRUE;
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 IF c_gst%ISOPEN THEN
1357 CLOSE c_gst;
1358 END IF;
1359 RAISE;
1360 END;
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1364 IGS_GE_MSG_STACK.ADD;
1365 App_Exception.Raise_Exception;
1366 END grdp_val_gst_closed;
1367 --
1368 --validate if IGS_GR_HONOURS_LEVEL.honours_level is closed
1369 FUNCTION grdp_val_hl_closed(
1370 p_honours_level IN VARCHAR2 DEFAULT NULL,
1371 p_message_name OUT NOCOPY VARCHAR2 )
1372 RETURN BOOLEAN AS
1373 BEGIN
1374 RETURN FALSE;
1375 END grdp_val_hl_closed;
1376 --
1377 -- Validate graduand surrender for award.
1378 FUNCTION GRDP_VAL_GR_SUR_CAW(
1379 p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
1380 p_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
1381 p_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
1382 p_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
1383 p_sur_for_crs_version_num IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
1384 p_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
1385 p_message_name OUT NOCOPY VARCHAR2 )
1386 RETURN BOOLEAN AS
1387 BEGIN -- grdp_val_gr_sur_caw
1388 DECLARE
1389 v_gst_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
1390 cst_graduated CONSTANT VARCHAR2(9) := 'GRADUATED';
1391 cst_surrender CONSTANT VARCHAR2(9) := 'SURRENDER';
1392 v_sca_exists CHAR(1);
1393 CURSOR c_gst IS
1394 SELECT gst.s_graduand_status
1395 FROM IGS_GR_STAT gst
1396 WHERE gst.graduand_status = p_graduand_status;
1397 CURSOR c_sca IS
1398 SELECT 'x'
1399 FROM IGS_EN_STDNT_PS_ATT sca
1400 WHERE sca.person_id = p_person_id AND
1401 sca.course_cd = p_sur_for_course_cd AND
1402 sca.version_number = p_sur_for_crs_version_num;
1403 BEGIN
1404 -- Set the default message number
1405 p_message_name := NULL;
1406 --1. Check parameters
1407 IF p_person_id IS NULL OR
1408 p_course_cd IS NULL OR
1409 p_graduand_status IS NULL OR
1410 p_sur_for_course_cd IS NULL OR
1411 p_sur_for_crs_version_num IS NULL OR
1412 p_sur_for_award_cd IS NULL THEN
1413 RETURN TRUE;
1414 END IF;
1415 --2. Validate surrending for course is not the same as the
1416 -- surrendering course
1417 IF p_sur_for_course_cd = p_course_cd THEN
1418 p_message_name := 'IGS_GR_CANNOT_SUBMIT_SAME_COU';
1419 RETURN FALSE;
1420 END IF;
1421 --3. When surrendering check the 'surrender for' course matches a
1422 -- student course attempt belonging to the graduand
1423 OPEN c_gst;
1424 FETCH c_gst INTO v_gst_s_graduand_status;
1425 IF c_gst%NOTFOUND THEN
1426 CLOSE c_gst;
1427 RAISE NO_DATA_FOUND;
1428 END IF;
1429 CLOSE c_gst;
1430 IF v_gst_s_graduand_status IN(
1431 cst_graduated,
1432 cst_surrender) THEN
1433 -- check a related student course attempt exist
1434 OPEN c_sca;
1435 FETCH c_sca INTO v_sca_exists;
1436 IF c_sca%NOTFOUND THEN
1437 CLOSE c_sca;
1438 p_message_name := 'IGS_GR_INVALID_COURSE_ATTEMPT';
1439 RETURN FALSE;
1440 END IF;
1441 CLOSE c_sca;
1442 END IF;
1443 --4. Return no error
1444 RETURN TRUE;
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 IF c_gst %ISOPEN THEN
1448 CLOSE c_gst;
1449 END IF;
1450 IF c_sca %ISOPEN THEN
1451 CLOSE c_sca;
1452 END IF;
1453 RAISE;
1454 END;
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1458 IGS_GE_MSG_STACK.ADD;
1459 App_Exception.Raise_Exception;
1460 END grdp_val_gr_sur_caw;
1461 END IGS_GR_VAL_GR;
[Home] [Help]