1 PACKAGE BODY IGS_EN_VAL_ENCMB AS
2 /* $Header: IGSEN37B.pls 120.1 2006/05/18 11:32:31 amuthu noship $ */
3
4 /*------------------------------------------------------------------------------+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGS_EN_VAL_ENCMB |
11 | |
12 | |
13 | HISTORY |
14 | Who When What |
15 | amuthu 18-May-2006 Modified the spec for ENRP_VAL_ENR_ENCMB to pass the |
16 | the effective date |
17 |-----------------------------------------------------------------------------*/
18 --
19 -- Validate whether a IGS_PE_PERSON is excluded from a IGS_PS_UNIT.
20 FUNCTION enrp_val_excld_unit(
21 p_person_id IN NUMBER ,
22 p_course_cd IN VARCHAR2 ,
23 p_unit_cd IN VARCHAR2 ,
24 p_effective_dt IN DATE ,
25 p_message_name OUT NOCOPY VARCHAR2)
26 RETURN BOOLEAN AS
27
28 BEGIN
29 DECLARE
30 v_pue_start_dt IGS_PE_PERS_UNT_EXCL.pue_start_dt%TYPE;
31 v_expiry_dt IGS_PE_PERS_UNT_EXCL.expiry_dt%TYPE;
32 CURSOR c_psd_ed IS
33 SELECT pue.pue_start_dt,
34 pue.expiry_dt
35 FROM IGS_PE_PERSENC_EFFCT pee,
36 IGS_PE_PERS_UNT_EXCL pue
37 WHERE pee.person_id = p_person_id AND
38 pee.s_encmb_effect_type = 'EXC_CRS_U' AND
39 pee.course_cd = p_course_cd AND
40 pue.person_id = pee.person_id AND
41 pue.encumbrance_type = pee.encumbrance_type AND
42 pue.pen_start_dt = pee.pen_start_dt AND
43 pue.s_encmb_effect_type = pee.s_encmb_effect_type AND
44 pue.pee_start_dt = pee.pee_start_dt AND
45 pue.pee_sequence_number = pee.sequence_number AND
46 pue.unit_cd = p_unit_cd;
47 BEGIN
48 -- This function validates whether or not a IGS_PE_PERSON is
49 -- excluded from admission or enrolment in a specific IGS_PS_UNIT.
50 p_message_name := null;
51 -- Validate the input parameters
52 IF p_person_id IS NULL OR
53 p_course_cd IS NULL OR
54 p_unit_cd IS NULL OR
55 p_effective_dt IS NULL THEN
56 p_message_name := null;
57 RETURN TRUE;
58 END IF;
59 --Validate for an exclusion from the university
60 IF IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
61 p_person_id,
62 p_course_cd,
63 p_effective_dt,
64 p_message_name) = FALSE THEN
65 RETURN FALSE;
66 END IF;
67 --Validate for an exclusion from a specific IGS_PS_UNIT.
68 OPEN c_psd_ed;
69 LOOP
70 FETCH c_psd_ed INTO v_pue_start_dt,
71 v_expiry_dt;
72 EXIT WHEN c_psd_ed%NOTFOUND;
73 --Validate if the dates of a returned record overlap with the effective date.
74 IF v_expiry_dt IS NULL THEN
75 IF v_pue_start_dt <= p_effective_dt THEN
76 CLOSE c_psd_ed;
77 p_message_name := 'IGS_EN_PRSN_ENCUMB_EXC_ENR';
78 RETURN FALSE;
79 END IF;
80 ELSE
81 IF p_effective_dt BETWEEN v_pue_start_dt AND (v_expiry_dt - 1) THEN
82 CLOSE c_psd_ed;
83 p_message_name := 'IGS_EN_PRSN_ENCUMB_EXC_ENR';
84 RETURN FALSE;
85 END IF;
86 END IF;
87 END LOOP;
88 CLOSE c_psd_ed;
89 --- Return the default value
90 p_message_name := null;
91 RETURN TRUE;
92 END;
93 EXCEPTION
94 WHEN OTHERS THEN
95 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
96 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_excld_unit');
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99
100
101 END enrp_val_excld_unit;
102 --
103 -- Validate whether or not a IGS_PE_PERSON is excluded from the university.
104 FUNCTION enrp_val_excld_prsn(
105 p_person_id IN NUMBER ,
106 p_course_cd IN VARCHAR2 ,
107 p_effective_dt IN DATE ,
108 p_message_name OUT NOCOPY VARCHAR2)
109 RETURN BOOLEAN AS
110
111 BEGIN
112 DECLARE
113 TYPE t_pee_dt_record IS RECORD (
114 pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
115 expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE);
116 cst_suspend_service
117 CONSTANT IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE := 'SUS_SRVC';
118 cst_revoke_service
119 CONSTANT IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE := 'RVK_SRVC';
120 v_sus_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP_V.apply_to_course_ind%TYPE;
121 v_rvk_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP_V.apply_to_course_ind%TYPE;
122 v_validate_sus_srvc BOOLEAN := TRUE;
123 v_validate_rvk_srvc BOOLEAN := TRUE;
124 v_pee_dates t_pee_dt_record;
125 CURSOR c_chk_crs_for_srv_type
126 ( cp_srv_type IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE ) IS
127 SELECT apply_to_course_ind
128 FROM IGS_EN_ENCMB_EFCTTYP_V
129 WHERE s_encmb_effect_type = cp_srv_type;
130 CURSOR c_get_pee_dates (
131 cp_srv_type IGS_EN_ENCMB_EFCTTYP.s_encmb_effect_type%TYPE ) IS
132 SELECT pee_start_dt, expiry_dt
133 FROM IGS_PE_PERSENC_EFFCT
134 WHERE person_id = p_person_id AND
135 s_encmb_effect_type = cp_srv_type;
136 CURSOR c_get_pee_dates_for_crs (
137 cp_srv_type IGS_EN_ENCMB_EFCTTYP.s_encmb_effect_type%TYPE ) IS
138 SELECT pee_start_dt, expiry_dt
139 FROM IGS_PE_PERSENC_EFFCT
140 WHERE person_id = p_person_id AND
141 s_encmb_effect_type = cp_srv_type AND
142 course_cd = p_course_cd;
143 BEGIN
144
145 --- Set the default message number
146 p_message_name := null;
147 --- Validate the input parameters.
148 IF p_person_id IS NULL OR p_effective_dt IS NULL THEN
149 RETURN TRUE;
150 END IF;
151 --- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is
152 --- dependant on whether or not the system encumbrance effect should be applied
153 --- to a IGS_PS_COURSE (s_encmb_effect_type.apply_to_course_ind).
154 --- IGS_GE_NOTE: v_validate_sus_srvc and v_validate_rvk_srvc should be
155 --- defaulted to True when defined.
156 --- Check if a IGS_PS_COURSE code is required for the Suspended Services effect type.
157 OPEN c_chk_crs_for_srv_type( cst_suspend_service );
158 FETCH c_chk_crs_for_srv_type INTO v_sus_apply_to_course_ind;
159 IF c_chk_crs_for_srv_type%NOTFOUND THEN
160 CLOSE c_chk_crs_for_srv_type;
161 v_validate_sus_srvc := FALSE;
162 ELSE
163 CLOSE c_chk_crs_for_srv_type;
164 IF v_sus_apply_to_course_ind = 'Y' AND p_course_cd IS NULL THEN
165 v_validate_sus_srvc := FALSE;
166 END IF;
167 END IF;
168 --- Check if a IGS_PS_COURSE code is required for the Revoked Services effect type.
169 OPEN c_chk_crs_for_srv_type( cst_revoke_service );
170 FETCH c_chk_crs_for_srv_type INTO v_rvk_apply_to_course_ind;
171 IF c_chk_crs_for_srv_type%NOTFOUND THEN
172 CLOSE c_chk_crs_for_srv_type;
173 v_validate_rvk_srvc := FALSE;
174 ELSE
175 CLOSE c_chk_crs_for_srv_type;
176 IF v_rvk_apply_to_course_ind = 'Y' AND p_course_cd IS NULL THEN
177 v_validate_rvk_srvc := FALSE;
178 END IF;
179 END IF;
180 --- Validate for an encumbrance which suspends all services.
181
182 IF v_validate_sus_srvc = TRUE THEN
183
184 IF v_sus_apply_to_course_ind = 'N' THEN
185 --- Check when the Suspended Services effect type is not applied to a IGS_PS_COURSE.
186 FOR v_pee_dates IN c_get_pee_dates( cst_suspend_service ) LOOP
187 --- Validate if dates of a returned record overlap with the effective date.
188 IF v_pee_dates.expiry_dt IS NULL THEN
189 IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
190 p_message_name := 'IGS_EN_PERS_HAS_ENCUMB';
191 RETURN FALSE;
192 END IF;
193 ELSE --? The Expiry Date is set.
194 IF p_effective_dt BETWEEN v_pee_dates.pee_start_dt AND
195 (v_pee_dates.expiry_dt - 1) THEN
196 p_message_name := 'IGS_EN_PERS_HAS_ENCUMB';
197 RETURN FALSE;
198 END IF;
199 END IF;
200 END LOOP;
201 ELSE
202 --- Check when the Suspended Services effect type is applied to a IGS_PS_COURSE.
203 FOR v_pee_dates IN c_get_pee_dates_for_crs( cst_suspend_service ) LOOP
204 --- Validate if dates of a returned record overlap with the effective date.
205 IF v_pee_dates.expiry_dt IS NULL THEN
206 IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
207 p_message_name := 'IGS_EN_PERS_HAS_ENCUMB';
208 RETURN FALSE;
209 END IF;
210 ELSE --? The Expiry Date is set.
211 IF p_effective_dt BETWEEN v_pee_dates.pee_start_dt AND
212 (v_pee_dates.expiry_dt - 1) THEN
213 p_message_name := 'IGS_EN_PERS_HAS_ENCUMB';
214 RETURN FALSE;
215 END IF;
216 END IF;
217 END LOOP;
218 END IF;
219 END IF; -- (validating for suspended services).
220 --- Validate for an encumbrance which revokes all services.
221
222 IF v_validate_rvk_srvc = TRUE THEN
223
224 IF v_rvk_apply_to_course_ind = 'N' THEN
225 --- Check when the Revoked Services effect type is not applied to a IGS_PS_COURSE.
226 FOR v_pee_dates IN c_get_pee_dates( cst_revoke_service ) LOOP
227 --- Validate if dates of a returned record overlap with the effective date.
228 IF v_pee_dates.expiry_dt IS NULL THEN
229 IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
230 p_message_name := 'IGS_EN_PRSN_ENCUMB_REVOKING';
231
232 RETURN FALSE;
233 END IF;
234 ELSE
235 -- The Expiry Date is set.
236 IF p_effective_dt BETWEEN v_pee_dates.pee_start_dt AND
237 (v_pee_dates.expiry_dt - 1) THEN
238 p_message_name := 'IGS_EN_PRSN_ENCUMB_REVOKING';
239
240 RETURN FALSE;
241 END IF;
242 END IF;
243 END LOOP;
244 ELSE
245 --- Check when the Revoked Services effect type is applied to a IGS_PS_COURSE.
246 FOR v_pee_dates IN c_get_pee_dates_for_crs( cst_revoke_service ) LOOP
247 --- Validate if dates of a returned record overlap with the effective date.
248 IF v_pee_dates.expiry_dt IS NULL THEN
249 IF v_pee_dates.pee_start_dt <= p_effective_dt THEN
250 p_message_name := 'IGS_EN_PRSN_ENCUMB_REVOKING';
251
252 RETURN FALSE;
253 END IF;
254 ELSE
255 -- The Expiry Date is set.
256 IF p_effective_dt BETWEEN v_pee_dates.pee_start_dt AND
257 (v_pee_dates.expiry_dt - 1) THEN
258 p_message_name := 'IGS_EN_PRSN_ENCUMB_REVOKING';
259
260 RETURN FALSE;
261 END IF;
262 END IF;
263 END LOOP;
264 END IF;
265 END IF; -- (validating for revoked services)
266 --- Return the default value
267
268 RETURN TRUE;
269 END;
270 /*
271 EXCEPTION
272 WHEN OTHERS THEN
273 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
274 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_excld_prsn');
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception;
277 */
278 END enrp_val_excld_prsn;
279 --
280 -- Validate whether a IGS_PE_PERSON is excluded from a IGS_PS_COURSE.
281 FUNCTION enrp_val_excld_crs(
282 p_person_id IN NUMBER ,
283 p_course_cd IN VARCHAR2 ,
284 p_effective_dt IN DATE ,
285 p_message_name OUT NOCOPY VARCHAR2)
286 RETURN BOOLEAN AS
287
288 BEGIN -- enrp_val_excld_crs
289 -- Validate whether or not a IGS_PE_PERSON is excluded from admission or enrolment
290 -- in a specific IGS_PS_COURSE.
291 DECLARE
292 cst_excluded CONSTANT VARCHAR2(10) := 'EXC_COURSE';
293 cst_excluded_grp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
294 cst_suspended CONSTANT VARCHAR2(10) := 'SUS_COURSE';
295 v_message_name varchar2(30);
296 CURSOR c_pce IS
297 SELECT pce.pce_start_dt,
298 pce.expiry_dt
299 FROM IGS_PE_COURSE_EXCL pce
300 WHERE pce.person_id = p_person_id AND
301 pce.course_cd = p_course_cd AND
302 pce.s_encmb_effect_type IN (
303 cst_excluded,
304 cst_suspended);
305 CURSOR c_pcge IS
306 SELECT pcge.pcge_start_dt,
307 pcge.expiry_dt
308 FROM IGS_PE_CRS_GRP_EXCL pcge,
309 IGS_PS_GRP_MBR cgm,
310 IGS_PS_GRP cg
311 WHERE pcge.person_id = p_person_id AND
312 pcge.s_encmb_effect_type = cst_excluded_grp AND
313 pcge.course_group_cd = cg.course_group_cd AND
314 cg.course_group_cd = cgm.course_group_cd AND
315 cgm.course_cd = p_course_cd;
316 BEGIN
317 p_message_name := null;
318 IF p_person_id IS NULL OR
319 p_course_cd IS NULL OR
320 p_effective_dt IS NULL THEN
321 RETURN TRUE;
322 END IF;
323 -- Validate for an exclusion from the university.
324 -- Invoke existing function to perform this check.
325 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
326 p_person_id,
327 NULL,
328 p_effective_dt,
329 v_message_name) THEN
330 p_message_name := v_message_name;
331 RETURN FALSE;
332 END IF;
333 -- Validate for an exclusion from a specific IGS_PS_COURSE.
334 FOR v_pce_rec IN c_pce LOOP
335 -- Validate if the dates of a returned record
336 -- overlap with the effective date.
337 IF v_pce_rec.expiry_dt IS NULL THEN
338 IF v_pce_rec.pce_start_dt <= p_effective_dt THEN
339 p_message_name := 'IGS_EN_PRSN_ENCUMB_ADM_ENRPRG';
340 RETURN FALSE;
341 END IF;
342 ELSE
343 IF p_effective_dt BETWEEN v_pce_rec.pce_start_dt AND
344 (v_pce_rec.expiry_dt - 1) THEN
345 p_message_name := 'IGS_EN_PRSN_ENCUMB_ADM_ENRPRG';
346 RETURN FALSE;
347 END IF;
348 END IF;
349 END LOOP;
350 -- Validate for an exclusion from
351 -- a IGS_PS_COURSE within a IGS_PS_COURSE group.
352 FOR v_pcge_rec IN c_pcge LOOP
353 -- Validate if the dates of a returned
354 -- record overlap with the effective date.
355 IF v_pcge_rec.expiry_dt IS NULL THEN
356 IF v_pcge_rec.pcge_start_dt <= p_effective_dt THEN
357 p_message_name := 'IGS_EN_PRSN_ENCUMB_ADM_ENRPRG';
358 RETURN FALSE;
359 END IF;
360 ELSE
361 IF p_effective_dt BETWEEN v_pcge_rec.pcge_start_dt AND
362 (v_pcge_rec.expiry_dt - 1) THEN
363 p_message_name := 'IGS_EN_PRSN_ENCUMB_ADM_ENRPRG';
364 RETURN FALSE;
365 END IF;
366 END IF;
367 END LOOP;
368 RETURN TRUE;
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF c_pce%ISOPEN THEN
372 CLOSE c_pce;
373 END IF;
374 IF c_pcge%ISOPEN THEN
375 CLOSE c_pcge;
376 END IF;
377 RAISE;
378 END;
379 /*
380 EXCEPTION
381 WHEN OTHERS THEN
382 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
383 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_excld_crs');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386
387 */
388 END enrp_val_excld_crs;
389 --
390 -- Validate whether a IGS_PE_PERSON is excluded from a IGS_PS_UNIT set.
391 FUNCTION enrp_val_excld_us(
392 p_person_id IN NUMBER ,
393 p_course_cd IN VARCHAR2 ,
394 p_unit_set_cd IN VARCHAR2 ,
395 p_us_version_number IN NUMBER ,
396 p_effective_dt IN DATE ,
397 p_message_name OUT NOCOPY VARCHAR2)
398 RETURN BOOLEAN AS
399
400 BEGIN
401 DECLARE
402 v_puse_start_dt IGS_PE_UNT_SET_EXCL.puse_start_dt%TYPE;
403 v_expiry_dt IGS_PE_UNT_SET_EXCL.expiry_dt%TYPE;
404 CURSOR c_psd_ed IS
405 SELECT puse.puse_start_dt,
406 puse.expiry_dt
407 FROM IGS_PE_PERSENC_EFFCT pee,
408 IGS_PE_UNT_SET_EXCL puse
409 WHERE pee.person_id = p_person_id AND
410 pee.s_encmb_effect_type = 'EXC_CRS_US' AND
411 pee.course_cd = p_course_cd AND
412 puse.person_id = pee.person_id AND
413 puse.encumbrance_type = pee.encumbrance_type AND
414 puse.pen_start_dt = pee.pen_start_dt AND
415 puse.s_encmb_effect_type = pee.s_encmb_effect_type AND
416 puse.pee_start_dt = pee.pee_start_dt AND
417 puse.pee_sequence_number = pee.sequence_number AND
418 puse.unit_set_cd = p_unit_set_cd AND
419 puse.us_version_number = p_us_version_number;
420 BEGIN
421 -- This function validates whether or not a IGS_PE_PERSON is
422 -- excluded from admission or enrolment in a specific IGS_PS_UNIT set.
423 p_message_name := null;
424 -- Validate the input parameters
425 IF p_person_id IS NULL OR
426 p_course_cd IS NULL OR
427 p_unit_set_cd IS NULL OR
428 p_us_version_number IS NULL OR
429 p_effective_dt IS NULL THEN
430 p_message_name := null;
431 RETURN TRUE;
432 END IF;
433 --Validate for an exclusion from the university
434 IF IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
435 p_person_id,
436 p_course_cd,
437 p_effective_dt,
438 p_message_name) = FALSE THEN
439 RETURN FALSE;
440 END IF;
441 --Validate for an exclusion from a specific IGS_PS_UNIT set.
442 OPEN c_psd_ed;
443 LOOP
444 FETCH c_psd_ed INTO v_puse_start_dt,
445 v_expiry_dt;
446 EXIT WHEN c_psd_ed%NOTFOUND;
447 --Validate if the dates of a returned record overlap with the effective date.
448 IF v_expiry_dt IS NULL THEN
449 IF v_puse_start_dt <= p_effective_dt THEN
450 CLOSE c_psd_ed;
451 p_message_name := 'IGS_EN_PERS_EXL_ENRL_UNT_SET';
452 RETURN FALSE;
453 END IF;
454 ELSE
455 IF p_effective_dt BETWEEN v_puse_start_dt AND (v_expiry_dt - 1) THEN
456 CLOSE c_psd_ed;
457 p_message_name := 'IGS_EN_PERS_EXL_ENRL_UNT_SET';
458 RETURN FALSE;
459 END IF;
460 END IF;
461 END LOOP;
462 CLOSE c_psd_ed;
463 --- Return the default value
464 p_message_name := null;
465 RETURN TRUE;
466 END;
467 EXCEPTION
468 WHEN OTHERS THEN
469 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
470 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_excld_us');
471 IGS_GE_MSG_STACK.ADD;
472 App_Exception.Raise_Exception;
473
474
475 END enrp_val_excld_us;
476 --
477 -- Validate whether a IGS_PE_PERSON is enrolled in all required units.
478 FUNCTION enrp_val_rqrd_units(
479 p_person_id IN NUMBER ,
480 p_course_cd IN VARCHAR2 ,
481 p_effective_dt IN DATE ,
482 p_message_name OUT NOCOPY VARCHAR2,
483 p_return_type OUT NOCOPY VARCHAR2 )
484 RETURN BOOLEAN AS
485 ------------------------------------------------------------------------------
486 --Created by :
487 --Date created:
488 --
489 -- Purpose:
490
491 -- Known limitations/enhancements and/or remarks:
492 --
493 -- Change History:
494 -- Who When What
495 -- svanukur 24-nov-2003 setting the p_return_type if enrp_val_excld_prsn returns false.bug#3052426
496 ------------------------------------------------------------------------------
497 BEGIN
498 DECLARE
499 cst_warn CONSTANT VARCHAR2(1) := 'W';
500 cst_error CONSTANT VARCHAR2(1) := 'E';
501 cst_effect_type CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
502 -- boolean used to check if a warning occured.
503 v_warning_ind BOOLEAN := FALSE;
504 v_unit_cd IGS_PE_UNT_REQUIRMNT.unit_cd%TYPE;
505 v_pur_start_dt IGS_PE_UNT_REQUIRMNT.pur_start_dt%TYPE;
506 v_expiry_dt IGS_PE_UNT_REQUIRMNT.expiry_dt%TYPE;
507 v_ci_start_dt IGS_EN_SU_ATTEMPT.ci_start_dt%TYPE;
508 v_ci_end_dt IGS_EN_SU_ATTEMPT.ci_end_dt%TYPE;
509 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
510 -- Cursor on IGS_PE_PERSENC_EFFCT (pee) and
511 -- IGS_PE_UNT_REQUIRMNT (pur) tables.
512 -- Cursor validates required IGS_PS_UNIT(s).
513 CURSOR c_pee_pur IS
514 SELECT pur.unit_cd,
515 pur.pur_start_dt,
516 pur.expiry_dt
517 FROM IGS_PE_PERSENC_EFFCT pee,
518 IGS_PE_UNT_REQUIRMNT pur
519 WHERE pee.person_id = p_person_id AND
520 pee.s_encmb_effect_type = cst_effect_type AND
521 pee.course_cd = p_course_cd AND
522 pur.person_id = pee.person_id AND
523 pur.encumbrance_type = pee.encumbrance_type AND
524 pur.pen_start_dt = pee.pen_start_dt AND
525 pur.s_encmb_effect_type = pee.s_encmb_effect_type AND
526 pur.pee_start_dt = pee.pee_start_dt AND
527 pur.pee_sequence_number = pee.sequence_number;
528 -- Cursor on IGS_EN_SU_ATTEMPT (sua).
529 -- Cursor validates which units a student is enrolled in,
530 -- for a particular IGS_PS_COURSE.
531 CURSOR c_sua (
532 cp_unit_cd IGS_PE_UNT_REQUIRMNT.unit_cd%TYPE,
533 cp_p_person_id IGS_PE_PERSON.person_id%TYPE,
534 cp_p_course_cd IGS_PS_COURSE.course_cd%TYPE) IS
535 SELECT sua.ci_start_dt,
536 sua.ci_end_dt,
537 sua.unit_attempt_status
538 FROM IGS_EN_SU_ATTEMPT sua
539 WHERE sua.person_id = cp_p_person_id AND
540 sua.course_cd = cp_p_course_cd AND
541 sua.unit_cd = cp_unit_cd AND
542 sua.unit_attempt_status IN ('COMPLETED',
543 'DUPLICATE',
544 'ENROLLED',
545 'DISCONTIN');
546 BEGIN
547 -- This function validates whether or not a IGS_PE_PERSON is enrolled
548 -- in all units they are required to enrol in.
549 -- Validate the input parameters
550 IF p_person_id IS NULL OR
551 p_course_cd IS NULL OR
552 p_effective_dt IS NULL THEN
553 p_message_name := null;
554 RETURN TRUE;
555 END IF;
556
557 -- Validate for an exclusion from the university.
558 IF IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (
559 p_person_id,
560 p_course_cd,
561 p_effective_dt,
562 p_message_name) = FALSE THEN
563
564 p_return_type := cst_error;
565
566 RETURN FALSE;
567 END IF;
568 -- Loop through IGS_PE_UNT_REQUIRMNT records.
569 FOR v_rqrd_units IN c_pee_pur LOOP
570 IF v_rqrd_units.expiry_dt IS NULL THEN
571 IF v_rqrd_units.pur_start_dt <= p_effective_dt THEN
572 OPEN c_sua(v_rqrd_units.unit_cd, p_person_id, p_course_cd);
573 FETCH c_sua INTO v_ci_start_dt,
574 v_ci_end_dt,
575 v_unit_attempt_status;
576 IF (c_sua%NOTFOUND) THEN
577 -- Student is not enrolled in
578 -- the required IGS_PS_UNIT
579 p_message_name := 'IGS_EN_PRSN_NOTENR_REQUIRE';
580 p_return_type := cst_error;
581 CLOSE c_sua;
582 RETURN FALSE;
583 END IF;
584 CLOSE c_sua;
585 -- reset cursor
586 OPEN c_sua(v_rqrd_units.unit_cd, p_person_id, p_course_cd);
587 -- Loop through the units the student is enrolled
588 -- in to see if the student is enrolled in the
589 -- required IGS_PS_UNIT
590 LOOP
591 FETCH c_sua INTO v_ci_start_dt,
592 v_ci_end_dt,
593 v_unit_attempt_status;
594 IF (c_sua%NOTFOUND) THEN
595 -- Student is not enrolled in the required date period.
596 p_message_name := 'IGS_EN_PRSN_NOTENR_REQUIRE';
597 p_return_type := cst_error;
598 CLOSE c_sua;
599 RETURN FALSE;
600 END IF;
601 IF (v_rqrd_units.pur_start_dt BETWEEN
602 v_ci_start_dt AND
603 v_ci_end_dt) THEN
604 IF v_unit_attempt_status =
605 'DISCONTIN' THEN
606 -- IGS_PS_UNIT has been discontinued
607 v_warning_ind := TRUE;
608 END IF;
609 -- exit from the inner loop and
610 -- continue processing the outer loop
611 EXIT;
612 END IF;
613 IF (v_rqrd_units.pur_start_dt <=
614 v_ci_start_dt) THEN
615 -- Student is enrolled in the IGS_PS_UNIT
616 -- in the required period.
617 IF v_unit_attempt_status =
618 'DISCONTIN' THEN
619 -- IGS_PS_UNIT has been discontinued
620 v_warning_ind := TRUE;
621 END IF;
622 -- exit from the inner loop and
623 -- continue processing the outer loop
624 EXIT;
625 END IF;
626 END LOOP;
627 CLOSE c_sua;
628 END IF;
629 ELSE -- The expiry date is set
630 IF p_effective_dt BETWEEN v_rqrd_units.pur_start_dt AND
631 (v_rqrd_units.expiry_dt - 1) THEN
632 OPEN c_sua(v_rqrd_units.unit_cd, p_person_id, p_course_cd);
633 FETCH c_sua INTO v_ci_start_dt,
634 v_ci_end_dt,
635 v_unit_attempt_status;
636 IF (c_sua%NOTFOUND) THEN
637 -- Student is not enrolled in the required IGS_PS_UNIT
638 CLOSE c_sua;
639 p_message_name := 'IGS_EN_PRSN_NOTENR_REQUIRE';
640 p_return_type := cst_error;
641 RETURN FALSE;
642 END IF;
643 CLOSE c_sua;
644 -- reset cursor
645 OPEN c_sua(v_rqrd_units.unit_cd, p_person_id, p_course_cd);
646 -- Loop through the units the student is enrolled in
647 -- to see if the student is enrolled in the required
648 -- IGS_PS_UNIT in the required date period
649 LOOP
650 FETCH c_sua INTO v_ci_start_dt,
651 v_ci_end_dt,
652 v_unit_attempt_status;
653 IF (c_sua%NOTFOUND) THEN
654 -- Student is not enrolled in the
655 -- required date period
656 p_message_name := 'IGS_EN_PRSN_NOTENR_REQUIRE';
657 p_return_type := cst_error;
658 CLOSE c_sua;
659 RETURN FALSE;
660 END IF;
661 -- Check if the student is enrolled in the IGS_PS_UNIT
662 -- in the required date period.
663 IF (v_rqrd_units.pur_start_dt BETWEEN
664 v_ci_start_dt AND
665 v_ci_end_dt) THEN
666 -- Student is enrolled in the IGS_PS_UNIT
667 -- in the required period.
668 IF v_unit_attempt_status =
669 'DISCONTIN' THEN
670 -- IGS_PS_UNIT has been discontinued
671 v_warning_ind := TRUE;
672 END IF;
673 -- exit from the inner loop and
674 -- continue processing the outer loop
675 EXIT;
676 END IF;
677 IF ((v_rqrd_units.expiry_dt - 1) BETWEEN
678 v_ci_start_dt AND
679 v_ci_end_dt) THEN
680 -- Student is enrolled in the IGS_PS_UNIT
681 -- in the required period.
682 IF v_unit_attempt_status =
683 'DISCONTIN' THEN
684 -- IGS_PS_UNIT has been discontinued
685 v_warning_ind := TRUE;
686 END IF;
687 -- exit from the inner loop and
688 -- continue processing the outer loop
689 EXIT;
690 END IF;
691 IF ((v_ci_start_dt BETWEEN
692 v_rqrd_units.pur_start_dt AND
693 (v_rqrd_units.expiry_dt - 1)) AND
694 (v_ci_end_dt BETWEEN
695 v_rqrd_units.pur_start_dt AND
696 (v_rqrd_units.expiry_dt - 1))) THEN
697 -- Student is enrolled in the IGS_PS_UNIT
698 -- in the required period.
699 IF v_unit_attempt_status =
700 'DISCONTIN' THEN
701 -- IGS_PS_UNIT has been discontinued
702 v_warning_ind := TRUE;
703 END IF;
704 -- exit from the inner loop and
705 -- continue processing the outer loop
706 EXIT;
707 END IF;
708 END LOOP;
709 CLOSE c_sua;
710 END IF;
711 END IF;
712 END LOOP;
713 IF (v_warning_ind = TRUE) THEN
714 -- Warn that a required IGS_PS_UNIT has been discontinued
715 p_message_name := 'IGS_EN_PRSN_DISCONT_REQUNIT';
716 p_return_type := cst_warn;
717 RETURN FALSE;
718 END IF;
719 --- Return the default value
720 p_message_name := null;
721 RETURN TRUE;
722 END;
723 EXCEPTION
724 WHEN OTHERS THEN
725 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
726 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_rqrd_units');
727 IGS_GE_MSG_STACK.ADD;
728 App_Exception.Raise_Exception;
729
730
731 END enrp_val_rqrd_units;
732 --
733 -- Validate whether or not a IGS_PE_PERSON is restricted to an attendance type.
734 FUNCTION enrp_val_rstrct_atyp(
735 p_person_id IN NUMBER ,
736 p_course_cd IN VARCHAR2 ,
737 p_effective_dt IN DATE ,
738 p_restricted_attendance_type OUT NOCOPY VARCHAR2 ,
739 p_message_name OUT NOCOPY VARCHAR2)
740 RETURN BOOLEAN AS
741 BEGIN
742 DECLARE
743 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
744
745 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
746 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
747 v_restricted_attendance_type
748 IGS_PE_PERSENC_EFFCT.restricted_attendance_type%TYPE;
749 CURSOR c_course_ind IS
750 SELECT SEET.apply_to_course_ind
751 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
752 WHERE s_encmb_effect_type = 'RSTR_AT_TY';
753 CURSOR c_pee_details_1 IS
754 SELECT PEE.pee_start_dt,
755 PEE.expiry_dt,
756 PEE.restricted_attendance_type
757 FROM IGS_PE_PERSENC_EFFCT PEE
758 WHERE person_id = p_person_id AND
759 s_encmb_effect_type = 'RSTR_AT_TY';
760 CURSOR c_pee_details_2 IS
761 SELECT PEE.pee_start_dt,
762 PEE.expiry_dt,
763 PEE.restricted_attendance_type
764 FROM IGS_PE_PERSENC_EFFCT PEE
765 WHERE person_id = p_person_id AND
766 s_encmb_effect_type = 'RSTR_AT_TY' AND
767 course_cd = p_course_cd;
768 BEGIN
769 -- This module validates whether or not
770 -- a IGS_PE_PERSON is restricted to an attendance type.
771 p_message_name := null;
772 -- validating the input parameters
773 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
774 RETURN TRUE;
775 END IF;
776 -- check if a IGS_PS_COURSE code is required for the
777 -- Restricted Attendance Type effect type
778 OPEN c_course_ind;
779 FETCH c_course_ind INTO v_apply_to_course_ind;
780 -- check if a record was found
781 IF (c_course_ind%NOTFOUND) THEN
782 CLOSE c_course_ind;
783 RETURN TRUE;
784 ELSE
785 CLOSE c_course_ind;
786 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
787 RETURN TRUE;
788 END IF;
789 END IF;
790 -- validate for an exclusion from the university -
791 -- invoke and existing function to perform this check
792 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
793 p_course_cd,
794 p_effective_dt,
795 p_message_name) = FALSE) THEN
796 RETURN FALSE;
797 END IF;
798 -- validate for an encumbrance which restricts the
799 -- attendance type
800 IF (v_apply_to_course_ind = 'N') THEN
801 FOR c_pee_rec IN c_pee_details_1 LOOP
802 IF (c_pee_rec.expiry_dt IS NULL) THEN
803 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
804 p_restricted_attendance_type :=
805 c_pee_rec.restricted_attendance_type;
806 p_message_name := 'IGS_EN_PERS_RESTR_ATTEND_TYPE';
807 RETURN FALSE;
808 END IF;
809 ELSE -- expiry_dt is set
810 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
811 (c_pee_rec.expiry_dt - 1)) THEN
812 p_restricted_attendance_type :=
813 c_pee_rec.restricted_attendance_type;
814 p_message_name := 'IGS_EN_PERS_RESTR_ATTEND_TYPE';
815 RETURN FALSE;
816 END IF;
817 END IF;
818 END LOOP;
819 ELSE -- v_apply_to_course_ind = 'Y'
820 -- check whether the restricted type effect
821 -- is applied to a IGS_PS_COURSE
822 FOR c_pee_rec IN c_pee_details_2 LOOP
823 IF (c_pee_rec.expiry_dt IS NULL) THEN
824 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
825 p_restricted_attendance_type :=
826 c_pee_rec.restricted_attendance_type;
827 p_message_name := 'IGS_EN_PERS_RESTR_ATTEND_TYPE';
828 RETURN FALSE;
829 END IF;
830 ELSE -- expiry_dt is set
831 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
832 (c_pee_rec.expiry_dt - 1)) THEN
833 p_restricted_attendance_type :=
834 c_pee_rec.restricted_attendance_type;
835 p_message_name := 'IGS_EN_PERS_RESTR_ATTEND_TYPE';
836 RETURN FALSE;
837 END IF;
838 END IF;
839 END LOOP;
840 END IF;
841 RETURN TRUE;
842 EXCEPTION
843 WHEN OTHERS THEN
844 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
845 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_rstrct_atyp');
846 IGS_GE_MSG_STACK.ADD;
847 App_Exception.Raise_Exception;
848
849
850 END;
851 END enrp_val_rstrct_atyp;
852 --
853 -- Validate whether or not a IGS_PE_PERSON is restricted to an enrolment cp.
854 FUNCTION enrp_val_rstrct_cp(
855 p_person_id IN NUMBER ,
856 p_course_cd IN VARCHAR2 ,
857 p_effective_dt IN DATE ,
858 p_rstrct_le_cp_value OUT NOCOPY NUMBER ,
859 p_rstrct_ge_cp_value OUT NOCOPY NUMBER ,
860 p_message_name OUT NOCOPY VARCHAR2)
861 RETURN BOOLEAN AS
862 BEGIN
863 DECLARE
864 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
865 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
866 v_ge_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
867 v_le_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
868 v_message_name VARCHAR2(30);
869 v_validate_rstr_ge_cp BOOLEAN := TRUE;
870 v_validate_rstr_le_cp BOOLEAN := TRUE;
871 CURSOR c_ge_course_ind IS
872 SELECT SEET.apply_to_course_ind
873 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
874 WHERE s_encmb_effect_type = cst_rstr_ge_cp;
875 CURSOR c_le_course_ind IS
876 SELECT SEET.apply_to_course_ind
877 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
878 WHERE s_encmb_effect_type = cst_rstr_le_cp;
879 CURSOR c_pee_details_1 IS
880 SELECT PEE.pee_start_dt,
881 PEE.expiry_dt,
882 PEE.restricted_enrolment_cp
883 FROM IGS_PE_PERSENC_EFFCT PEE
884 WHERE person_id = p_person_id AND
885 s_encmb_effect_type = cst_rstr_ge_cp AND
886 restricted_enrolment_cp IS NOT NULL
887 ORDER BY PEE.restricted_enrolment_cp DESC;
888 CURSOR c_pee_details_2 IS
889 SELECT PEE.pee_start_dt,
890 PEE.expiry_dt,
891 PEE.restricted_enrolment_cp
892 FROM IGS_PE_PERSENC_EFFCT PEE
893 WHERE person_id = p_person_id AND
894 s_encmb_effect_type = cst_rstr_ge_cp AND
895 course_cd = p_course_cd AND
896 restricted_enrolment_cp IS NOT NULL
897 ORDER BY PEE.restricted_enrolment_cp DESC;
898 CURSOR c_pee_details_3 IS
899 SELECT PEE.pee_start_dt,
900 PEE.expiry_dt,
901 PEE.restricted_enrolment_cp
902 FROM IGS_PE_PERSENC_EFFCT PEE
903 WHERE person_id = p_person_id AND
904 s_encmb_effect_type = cst_rstr_le_cp AND
905 restricted_enrolment_cp IS NOT NULL
906 ORDER BY PEE.restricted_enrolment_cp ASC;
907 CURSOR c_pee_details_4 IS
908 SELECT PEE.pee_start_dt,
909 PEE.expiry_dt,
910 PEE.restricted_enrolment_cp
911 FROM IGS_PE_PERSENC_EFFCT PEE
912 WHERE person_id = p_person_id AND
913 s_encmb_effect_type = cst_rstr_le_cp AND
914 course_cd = p_course_cd AND
915 restricted_enrolment_cp IS NOT NULL
916 ORDER BY PEE.restricted_enrolment_cp ASC;
917 BEGIN
918 -- This module validates whether or not
919 -- a IGS_PE_PERSON is restricted to an enrolment credit point value.
920 v_message_name := null;
921 -- validating the input parameters
922 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
923 p_message_name := v_message_name;
924 RETURN TRUE;
925 END IF;
926 -- The requirement of the IGS_PS_COURSE code parameter (p_course_cd) is dependant on
927 -- whether or not the system encumbrance effect should be applied to a IGS_PS_COURSE.
928 -- (s_encmb_effect_type.apply_to_course_ind).
929 -- IGS_GE_NOTE: v_validate_rstr_ge_cp and v_validate_rstr_le_cp should be defaulted
930 -- to TRUE when defined.
931 -- Check if a IGS_PS_COURSE code is required for the
932 -- Restricted Greater Than Credit Point effect type
933 OPEN c_ge_course_ind;
934 FETCH c_ge_course_ind INTO v_ge_apply_to_course_ind;
935 -- check if a record was found
936 IF (c_ge_course_ind%NOTFOUND) THEN
937 CLOSE c_ge_course_ind;
938 v_validate_rstr_ge_cp := FALSE;
939 ELSE
940 CLOSE c_ge_course_ind;
941 IF (v_ge_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
942 v_validate_rstr_ge_cp := FALSE;
943 END IF;
944 END IF;
945 -- check if a IGS_PS_COURSE code is required for the
946 -- Restricted Less Than Credit Point effect type
947 OPEN c_le_course_ind;
948 FETCH c_le_course_ind INTO v_le_apply_to_course_ind;
949 -- check if a record was found
950 IF (c_le_course_ind%NOTFOUND) THEN
951 CLOSE c_le_course_ind;
952 v_validate_rstr_le_cp := FALSE;
953 ELSE
954 CLOSE c_le_course_ind;
955 IF (v_le_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
956 v_validate_rstr_le_cp := FALSE;
957 END IF;
958 END IF;
959 -- validate for an exclusion from the university -
960 -- invoke and existing function to perform this check
961 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
962 p_course_cd,
963 p_effective_dt,
964 v_message_name) = FALSE) THEN
965 p_message_name := v_message_name;
966 RETURN FALSE;
967 END IF;
968 -- validate for an encumbrance which restricts the
969 -- enrolment credit points to greater than or equal to a
970 -- nominated credit point value
971 IF (v_validate_rstr_ge_cp = TRUE) THEN
972 IF (v_ge_apply_to_course_ind = 'N') THEN
973 FOR c_pee_rec IN c_pee_details_1 LOOP
974 IF (c_pee_rec.expiry_dt IS NULL) THEN
975 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
976 p_rstrct_ge_cp_value := c_pee_rec.restricted_enrolment_cp;
977 v_message_name := 'IGS_EN_PRSN_ENCUMB_GE_ENRCRD';
978 EXIT;
979 END IF;
980 ELSE -- expiry_dt is set
981 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
982 (c_pee_rec.expiry_dt - 1)) THEN
983 p_rstrct_ge_cp_value := c_pee_rec.restricted_enrolment_cp;
984 v_message_name := 'IGS_EN_PRSN_ENCUMB_GE_ENRCRD';
985 EXIT;
986 END IF;
987 END IF;
988 END LOOP;
989 ELSE -- v_ge_apply_to_course_ind = 'Y'
990 -- check whether the restricted type effect
991 -- is applied to a IGS_PS_COURSE
992 FOR c_pee_rec IN c_pee_details_2 LOOP
993 IF (c_pee_rec.expiry_dt IS NULL) THEN
994 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
995 p_rstrct_ge_cp_value := c_pee_rec.restricted_enrolment_cp;
996 v_message_name := 'IGS_EN_PRSN_ENCUMB_GE_ENRCRD';
997 EXIT;
998 END IF;
999 ELSE -- expiry_dt is set
1000 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1001 (c_pee_rec.expiry_dt - 1)) THEN
1002 p_rstrct_ge_cp_value := c_pee_rec.restricted_enrolment_cp;
1003 v_message_name := 'IGS_EN_PRSN_ENCUMB_GE_ENRCRD';
1004 EXIT;
1005 END IF;
1006 END IF;
1007 END LOOP;
1008 END IF;
1009 END IF; -- Validating for greater than restriction.
1010 -- validate for an encumbrance which restricts the
1011 -- enrolment credit points to less than or equal to a
1012 -- nominated credit point value
1013 IF (v_validate_rstr_le_cp = TRUE) THEN
1014 IF (v_le_apply_to_course_ind = 'N') THEN
1015 FOR c_pee_rec IN c_pee_details_3 LOOP
1016 IF (c_pee_rec.expiry_dt IS NULL) THEN
1017 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1018 p_rstrct_le_cp_value :=
1019 c_pee_rec.restricted_enrolment_cp;
1020 v_message_name := 'IGS_EN_PRSN-ENCUMB_LE_ENRCRD';
1021 EXIT;
1022 END IF;
1023 ELSE -- expiry_dt is set
1024 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1025 (c_pee_rec.expiry_dt - 1)) THEN
1026 p_rstrct_le_cp_value :=
1027 c_pee_rec.restricted_enrolment_cp;
1028 v_message_name := 'IGS_EN_PRSN-ENCUMB_LE_ENRCRD';
1029 EXIT;
1030 END IF;
1031 END IF;
1032 END LOOP;
1033 ELSE -- v_le_apply_to_course_ind = 'Y'
1034 -- check whether the restricted type effect
1035 -- is applied to a IGS_PS_COURSE
1036 FOR c_pee_rec IN c_pee_details_4 LOOP
1037 IF (c_pee_rec.expiry_dt IS NULL) THEN
1038 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1039 p_rstrct_le_cp_value :=
1040 c_pee_rec.restricted_enrolment_cp;
1041 v_message_name := 'IGS_EN_PRSN-ENCUMB_LE_ENRCRD';
1042 EXIT;
1043 END IF;
1044 ELSE -- expiry_dt is set
1045 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1046 (c_pee_rec.expiry_dt - 1)) THEN
1047 p_rstrct_le_cp_value :=
1048 c_pee_rec.restricted_enrolment_cp;
1049 v_message_name := 'IGS_EN_PRSN-ENCUMB_LE_ENRCRD';
1050 EXIT;
1051 END IF;
1052 END IF;
1053 END LOOP;
1054 END IF;
1055 END IF; -- Validating for less than restriction
1056 -- return FALSE if the p_message_name
1057 -- has been set
1058 IF (v_message_name IS NOT NULL ) THEN
1059 p_message_name := v_message_name;
1060 RETURN FALSE;
1061 ELSE
1062 p_message_name := v_message_name;
1063 RETURN TRUE;
1064 END IF;
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1068 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_rstrct_cp');
1069 IGS_GE_MSG_STACK.ADD;
1070 App_Exception.Raise_Exception;
1071
1072
1073 END;
1074 END enrp_val_rstrct_cp;
1075 --
1076 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking IGS_PS_COURSE material.
1077 FUNCTION enrp_val_blk_crsmtrl(
1078 p_person_id IN NUMBER ,
1079 p_course_cd VARCHAR2 ,
1080 p_effective_dt IN DATE ,
1081 p_message_name OUT NOCOPY VARCHAR2)
1082 RETURN BOOLEAN AS
1083 BEGIN
1084 DECLARE
1085 cst_crsmtrl_blk CONSTANT VARCHAR2(10) := 'C_MTRL_BLK';
1086 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1087 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1088 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1089 CURSOR c_course_ind IS
1090 SELECT SEET.apply_to_course_ind
1091 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1092 WHERE s_encmb_effect_type = cst_crsmtrl_blk;
1093 CURSOR c_pee_details_1 IS
1094 SELECT PEE.pee_start_dt,
1095 PEE.expiry_dt
1096 FROM IGS_PE_PERSENC_EFFCT PEE
1097 WHERE person_id = p_person_id AND
1098 s_encmb_effect_type = cst_crsmtrl_blk;
1099 CURSOR c_pee_details_2 IS
1100 SELECT PEE.pee_start_dt,
1101 PEE.expiry_dt
1102 FROM IGS_PE_PERSENC_EFFCT PEE
1103 WHERE person_id = p_person_id AND
1104 s_encmb_effect_type = cst_crsmtrl_blk AND
1105 course_cd = p_course_cd;
1106 BEGIN
1107 -- This module validates whether or not
1108 -- a IGS_PE_PERSON has an encumbracne blocking the
1109 -- issue of IGS_PS_COURSE materials
1110 p_message_name := null;
1111 -- validating the input parameters
1112 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1113 RETURN TRUE;
1114 END IF;
1115 -- check if a IGS_PS_COURSE code is required for the
1116 -- Materials Blocked effect type
1117 OPEN c_course_ind;
1118 FETCH c_course_ind INTO v_apply_to_course_ind;
1119 -- check if a record was found
1120 IF (c_course_ind%NOTFOUND) THEN
1121 CLOSE c_course_ind;
1122 RETURN TRUE;
1123 ELSE
1124 CLOSE c_course_ind;
1125 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1126 RETURN TRUE;
1127 END IF;
1128 END IF;
1129 -- validate for an exclusion from the university -
1130 -- invoke and existing function to perform this check
1131 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1132 p_course_cd,
1133 p_effective_dt,
1134 p_message_name) = FALSE) THEN
1135 RETURN FALSE;
1136 END IF;
1137 -- validate for an encumbrance which blocks the issue
1138 -- of IGS_PS_COURSE materials
1139 IF (v_apply_to_course_ind = 'N') THEN
1140 -- check when the IGS_PS_COURSE Materials Blocked effect
1141 -- type is not appplied to a IGS_PS_COURSE
1142 FOR c_pee_rec IN c_pee_details_1 LOOP
1143 IF (c_pee_rec.expiry_dt IS NULL) THEN
1144 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1145 p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1146 RETURN FALSE;
1147 END IF;
1148 ELSE -- expiry_dt is set
1149 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1150 (c_pee_rec.expiry_dt - 1)) THEN
1151 p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1152 RETURN FALSE;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156 ELSE -- v_apply_to_course_ind = 'Y'
1157 -- check when the IGS_PS_COURSE Materials Blocked effect
1158 -- type is appplied to a IGS_PS_COURSE
1159 FOR c_pee_rec IN c_pee_details_2 LOOP
1160 IF (c_pee_rec.expiry_dt IS NULL) THEN
1161 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1162 p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1163 RETURN FALSE;
1164 END IF;
1165 ELSE -- expiry_dt is set
1166 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1167 (c_pee_rec.expiry_dt - 1)) THEN
1168 p_message_name := 'IGS_EN_ISSUE_OF_COURS_MATEER';
1169 RETURN FALSE;
1170 END IF;
1171 END IF;
1172 END LOOP;
1173 END IF;
1174 RETURN TRUE;
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1178 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_crsmtrl');
1179 IGS_GE_MSG_STACK.ADD;
1180 App_Exception.Raise_Exception;
1181
1182
1183 END;
1184 END enrp_val_blk_crsmtrl;
1185 --
1186 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking graduation.
1187 FUNCTION enrp_val_blk_grd(
1188 p_person_id IN NUMBER ,
1189 p_course_cd IN VARCHAR2 ,
1190 p_effective_dt IN DATE ,
1191 p_message_name OUT NOCOPY VARCHAR2)
1192 RETURN BOOLEAN AS
1193 BEGIN
1194 DECLARE
1195 cst_grad_blk CONSTANT VARCHAR2(10) := 'GRAD_BLK';
1196 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1197 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1198 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1199 CURSOR c_course_ind IS
1200 SELECT SEET.apply_to_course_ind
1201 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1202 WHERE s_encmb_effect_type = cst_grad_blk;
1203 CURSOR c_pee_details_1 IS
1204 SELECT PEE.pee_start_dt,
1205 PEE.expiry_dt
1206 FROM IGS_PE_PERSENC_EFFCT PEE
1207 WHERE person_id = p_person_id AND
1208 s_encmb_effect_type = cst_grad_blk;
1209 CURSOR c_pee_details_2 IS
1210 SELECT PEE.pee_start_dt,
1211 PEE.expiry_dt
1212 FROM IGS_PE_PERSENC_EFFCT PEE
1213 WHERE person_id = p_person_id AND
1214 s_encmb_effect_type = cst_grad_blk AND
1215 course_cd = p_course_cd;
1216 BEGIN
1217 -- This module validates whether or not
1218 -- a IGS_PE_PERSON has an encumbrance blocking
1219 -- graduation
1220 p_message_name := null;
1221 -- validating the input parameters
1222 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1223 RETURN TRUE;
1224 END IF;
1225 -- check if a IGS_PS_COURSE code is required for the
1226 -- Graduation Blocked effect type
1227 OPEN c_course_ind;
1228 FETCH c_course_ind INTO v_apply_to_course_ind;
1229 -- check if a record was found
1230 IF (c_course_ind%NOTFOUND) THEN
1231 CLOSE c_course_ind;
1232 RETURN TRUE;
1233 ELSE
1234 CLOSE c_course_ind;
1235 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1236 RETURN TRUE;
1237 END IF;
1238 END IF;
1239 -- validate for an exclusion from the university -
1240 -- invoke and existing function to perform this check
1241 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1242 p_course_cd,
1243 p_effective_dt,
1244 p_message_name) = FALSE) THEN
1245 RETURN FALSE;
1246 END IF;
1247 -- validate for an encumbrance which blocks
1248 -- graduation
1249 IF (v_apply_to_course_ind = 'N') THEN
1250 -- check when the Graduation Blocked effect
1251 -- type is not applied to a IGS_PS_COURSE
1252 FOR c_pee_rec IN c_pee_details_1 LOOP
1253 IF (c_pee_rec.expiry_dt IS NULL) THEN
1254 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1255 p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1256 RETURN FALSE;
1257 END IF;
1258 ELSE -- expiry_dt is set
1259 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1260 (c_pee_rec.expiry_dt - 1)) THEN
1261 p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1262 RETURN FALSE;
1263 END IF;
1264 END IF;
1265 END LOOP;
1266 ELSE -- v_apply_to_course_ind = 'Y'
1267 -- check when the Graduation Blocked effect
1268 -- type is applied to a IGS_PS_COURSE
1269 FOR c_pee_rec IN c_pee_details_2 LOOP
1270 IF (c_pee_rec.expiry_dt IS NULL) THEN
1271 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1272 p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1273 RETURN FALSE;
1274 END IF;
1275 ELSE -- expiry_dt is set
1276 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1277 (c_pee_rec.expiry_dt - 1)) THEN
1278 p_message_name := 'IGS_EN_PRSN_ENCUMB_GRADUATION';
1279 RETURN FALSE;
1280 END IF;
1281 END IF;
1282 END LOOP;
1283 END IF;
1284 RETURN TRUE;
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1288 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_grd');
1289 IGS_GE_MSG_STACK.ADD;
1290 App_Exception.Raise_Exception;
1291
1292
1293 END;
1294 END enrp_val_blk_grd;
1295 --
1296 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking their ID card.
1297 FUNCTION enrp_val_blk_id_card(
1298 p_person_id IN NUMBER ,
1299 p_course_cd IN VARCHAR2 ,
1300 p_effective_dt IN DATE ,
1301 p_message_name OUT NOCOPY VARCHAR2)
1302 RETURN BOOLEAN AS
1303 BEGIN
1304 DECLARE
1305 cst_idcard_blk CONSTANT VARCHAR2(10) := 'IDCARD_BLK';
1306 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1307 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1308 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1309 CURSOR c_course_ind IS
1310 SELECT SEET.apply_to_course_ind
1311 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1312 WHERE s_encmb_effect_type = cst_idcard_blk;
1313 CURSOR c_pee_details_1 IS
1314 SELECT PEE.pee_start_dt,
1315 PEE.expiry_dt
1316 FROM IGS_PE_PERSENC_EFFCT PEE
1317 WHERE person_id = p_person_id AND
1318 s_encmb_effect_type = cst_idcard_blk;
1319 CURSOR c_pee_details_2 IS
1320 SELECT PEE.pee_start_dt,
1321 PEE.expiry_dt
1322 FROM IGS_PE_PERSENC_EFFCT PEE
1323 WHERE person_id = p_person_id AND
1324 s_encmb_effect_type = cst_idcard_blk AND
1325 course_cd = p_course_cd;
1326 BEGIN
1327 -- This module validates whether or not
1328 -- a IGS_PE_PERSON has an encumbracne blocking the
1329 -- issue of an ID card
1330 p_message_name := null;
1331 -- validating the input parameters
1332 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1333 RETURN TRUE;
1334 END IF;
1335 -- check if a IGS_PS_COURSE code is required for the
1336 -- ID Card Blocked effect type
1337 OPEN c_course_ind;
1338 FETCH c_course_ind INTO v_apply_to_course_ind;
1339 -- check if a record was found
1340 IF (c_course_ind%NOTFOUND) THEN
1341 CLOSE c_course_ind;
1342 RETURN TRUE;
1343 ELSE
1344 CLOSE c_course_ind;
1345 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1346 RETURN TRUE;
1347 END IF;
1348 END IF;
1349 -- validate for an exclusion from the university -
1350 -- invoke and existing function to perform this check
1351 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1352 p_course_cd,
1353 p_effective_dt,
1354 p_message_name) = FALSE) THEN
1355 RETURN FALSE;
1356 END IF;
1357 -- validate for an encumbrance which blocks the issue
1358 -- of an ID card
1359 IF (v_apply_to_course_ind = 'N') THEN
1360 -- check when the ID Card Blocked effect
1361 -- type is not applied to a IGS_PS_COURSE
1362 FOR c_pee_rec IN c_pee_details_1 LOOP
1363 IF (c_pee_rec.expiry_dt IS NULL) THEN
1364 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1365 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1366 RETURN FALSE;
1367 END IF;
1368 ELSE -- expiry_dt is set
1369 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1370 (c_pee_rec.expiry_dt - 1)) THEN
1371 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1372 RETURN FALSE;
1373 END IF;
1374 END IF;
1375 END LOOP;
1376 ELSE -- v_apply_to_course_ind = 'Y'
1377 -- check when the ID Card Blocked effect
1378 -- type is applied to a IGS_PS_COURSE
1379 FOR c_pee_rec IN c_pee_details_2 LOOP
1380 IF (c_pee_rec.expiry_dt IS NULL) THEN
1381 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1382 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1383 RETURN FALSE;
1384 END IF;
1385 ELSE -- expiry_dt is set
1386 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1387 (c_pee_rec.expiry_dt - 1)) THEN
1388 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_IDCARD';
1389 RETURN FALSE;
1390 END IF;
1391 END IF;
1392 END LOOP;
1393 END IF;
1394 RETURN TRUE;
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1398 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_id_card');
1399 IGS_GE_MSG_STACK.ADD;
1400 App_Exception.Raise_Exception;
1401
1402
1403 END;
1404 END enrp_val_blk_id_card;
1405 --
1406 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking the info booth.
1407 FUNCTION enrp_val_blk_inf_bth(
1408 p_person_id IN NUMBER ,
1409 p_course_cd IN VARCHAR2 ,
1410 p_effective_dt IN DATE ,
1411 p_message_name OUT NOCOPY VARCHAR2)
1412 RETURN BOOLEAN AS
1413 BEGIN
1414 DECLARE
1415 cst_infbth_blk CONSTANT VARCHAR2(10) := 'INFBTH_BLK';
1416 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1417 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1418 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1419 CURSOR c_course_ind IS
1420 SELECT SEET.apply_to_course_ind
1421 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1422 WHERE s_encmb_effect_type = cst_infbth_blk;
1423 CURSOR c_pee_details_1 IS
1424 SELECT PEE.pee_start_dt,
1425 PEE.expiry_dt
1426 FROM IGS_PE_PERSENC_EFFCT PEE
1427 WHERE person_id = p_person_id AND
1428 s_encmb_effect_type = cst_infbth_blk;
1429 CURSOR c_pee_details_2 IS
1430 SELECT PEE.pee_start_dt,
1431 PEE.expiry_dt
1432 FROM IGS_PE_PERSENC_EFFCT PEE
1433 WHERE person_id = p_person_id AND
1434 s_encmb_effect_type = cst_infbth_blk AND
1435 course_cd = p_course_cd;
1436 BEGIN
1437 -- This module validates whether or not
1438 -- a IGS_PE_PERSON has an encumbracne blocking the
1439 -- secure services of the Information Booth
1440 -- (ie. services accessed via a PIN)
1441 p_message_name := null;
1442 -- validating the input parameters
1443 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1444 RETURN TRUE;
1445 END IF;
1446 -- check if a IGS_PS_COURSE code is required for the
1447 -- Information Booth Blocked effect type
1448 OPEN c_course_ind;
1449 FETCH c_course_ind INTO v_apply_to_course_ind;
1450 -- check if a record was found
1451 IF (c_course_ind%NOTFOUND) THEN
1452 CLOSE c_course_ind;
1453 RETURN TRUE;
1454 ELSE
1455 CLOSE c_course_ind;
1456 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1457 RETURN TRUE;
1458 END IF;
1459 END IF;
1460 -- validate for an exclusion from the university -
1461 -- invoke and existing function to perform this check
1462 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1463 p_course_cd,
1464 p_effective_dt,
1465 p_message_name) = FALSE) THEN
1466 RETURN FALSE;
1467 END IF;
1468 -- validate for an encumbrance which blocks the
1469 -- secure services of the Information Booth
1470 IF (v_apply_to_course_ind = 'N') THEN
1471 -- check when the Information Booth Blocked effect
1472 -- type is not applied to a IGS_PS_COURSE
1473 FOR c_pee_rec IN c_pee_details_1 LOOP
1474 IF (c_pee_rec.expiry_dt IS NULL) THEN
1475 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1476 p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1477 RETURN FALSE;
1478 END IF;
1479 ELSE -- expiry_dt is set
1480 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1481 (c_pee_rec.expiry_dt - 1)) THEN
1482 p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1483 RETURN FALSE;
1484 END IF;
1485 END IF;
1486 END LOOP;
1487 ELSE -- v_apply_to_course_ind = 'Y'
1488 -- check when the Information Booth Blocked effect
1489 -- type is applied to a IGS_PS_COURSE
1490 FOR c_pee_rec IN c_pee_details_2 LOOP
1491 IF (c_pee_rec.expiry_dt IS NULL) THEN
1492 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1493 p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1494 RETURN FALSE;
1495 END IF;
1496 ELSE -- expiry_dt is set
1497 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1498 (c_pee_rec.expiry_dt - 1)) THEN
1499 p_message_name := 'IGS_EN_SECURE_SERV_BLOCKING';
1500 RETURN FALSE;
1501 END IF;
1502 END IF;
1503 END LOOP;
1504 END IF;
1505 RETURN TRUE;
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1509 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_inf_bth');
1510
1511 IGS_GE_MSG_STACK.ADD;
1512 App_Exception.Raise_Exception;
1513
1514
1515 END;
1516 END enrp_val_blk_inf_bth;
1517 --
1518 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking issue of results
1519 FUNCTION enrp_val_blk_result(
1520 p_person_id IN NUMBER ,
1521 p_course_cd IN VARCHAR2 ,
1522 p_effective_dt IN DATE ,
1523 p_message_name OUT NOCOPY VARCHAR2)
1524 RETURN BOOLEAN AS
1525 BEGIN
1526 DECLARE
1527 cst_result_blk CONSTANT VARCHAR2(10) := 'RESULT_BLK';
1528 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1529 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1530 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1531 CURSOR c_course_ind IS
1532 SELECT SEET.apply_to_course_ind
1533 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1534 WHERE s_encmb_effect_type = cst_result_blk;
1535 CURSOR c_pee_details_1 IS
1536 SELECT PEE.pee_start_dt,
1537 PEE.expiry_dt
1538 FROM IGS_PE_PERSENC_EFFCT PEE
1539 WHERE person_id = p_person_id AND
1540 s_encmb_effect_type = cst_result_blk;
1541 CURSOR c_pee_details_2 IS
1542 SELECT PEE.pee_start_dt,
1543 PEE.expiry_dt
1544 FROM IGS_PE_PERSENC_EFFCT PEE
1545 WHERE person_id = p_person_id AND
1546 s_encmb_effect_type = cst_result_blk AND
1547 course_cd = p_course_cd;
1548 BEGIN
1549 -- This module validates whether or not
1550 -- a IGS_PE_PERSON has an encumbracne blocking the
1551 -- issue of results
1552 p_message_name := null;
1553 -- validating the input parameters
1554 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1555 RETURN TRUE;
1556 END IF;
1557 -- check if a IGS_PS_COURSE code is required for the
1558 -- Result Blocked effect type
1559 OPEN c_course_ind;
1560 FETCH c_course_ind INTO v_apply_to_course_ind;
1561 -- check if a record was found
1562 IF (c_course_ind%NOTFOUND) THEN
1563 CLOSE c_course_ind;
1564 RETURN TRUE;
1565 ELSE
1566 CLOSE c_course_ind;
1567 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1568 RETURN TRUE;
1569 END IF;
1570 END IF;
1571 -- validate for an exclusion from the university -
1572 -- invoke and existing function to perform this check
1573 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1574 p_course_cd,
1575 p_effective_dt,
1576 p_message_name) = FALSE) THEN
1577 RETURN FALSE;
1578 END IF;
1579 -- validate for an encumbrance which blocks the issue
1580 -- of results
1581 IF (v_apply_to_course_ind = 'N') THEN
1582 -- check when the Results Blocked effect
1583 -- type is not applied to a IGS_PS_COURSE
1584 FOR c_pee_rec IN c_pee_details_1 LOOP
1585 IF (c_pee_rec.expiry_dt IS NULL) THEN
1586 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1587 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1588 RETURN FALSE;
1589 END IF;
1590 ELSE -- expiry_dt is set
1591 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1592 (c_pee_rec.expiry_dt - 1)) THEN
1593 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1594 RETURN FALSE;
1595 END IF;
1596 END IF;
1597 END LOOP;
1598 ELSE -- v_apply_to_course_ind = 'Y'
1599 -- check when the Results Blocked effect
1600 -- type is applied to a IGS_PS_COURSE
1601 FOR c_pee_rec IN c_pee_details_2 LOOP
1602 IF (c_pee_rec.expiry_dt IS NULL) THEN
1603 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1604 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1605 RETURN FALSE;
1606 END IF;
1607 ELSE -- expiry_dt is set
1608 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1609 (c_pee_rec.expiry_dt - 1)) THEN
1610 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_RESULT';
1611 RETURN FALSE;
1612 END IF;
1613 END IF;
1614 END LOOP;
1615 END IF;
1616 RETURN TRUE;
1617 EXCEPTION
1618 WHEN OTHERS THEN
1619 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1620 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_result');
1621 IGS_GE_MSG_STACK.ADD;
1622 App_Exception.Raise_Exception;
1623
1624 END;
1625 END enrp_val_blk_result;
1626 --
1627 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking system corresp.
1628 FUNCTION enrp_val_blk_sys_cor(
1629 p_person_id IN NUMBER ,
1630 p_course_cd IN VARCHAR2 ,
1631 p_effective_dt IN DATE ,
1632 p_message_name OUT NOCOPY VARCHAR2)
1633 RETURN BOOLEAN AS
1634 BEGIN
1635 DECLARE
1636 cst_cor_blk CONSTANT VARCHAR2(10) := 'S_COR_BLK';
1637 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1638 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1639 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1640 CURSOR c_course_ind IS
1641 SELECT SEET.apply_to_course_ind
1642 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1643 WHERE s_encmb_effect_type = cst_cor_blk;
1644 CURSOR c_pee_details_1 IS
1645 SELECT PEE.pee_start_dt,
1646 PEE.expiry_dt
1647 FROM IGS_PE_PERSENC_EFFCT PEE
1648 WHERE person_id = p_person_id AND
1649 s_encmb_effect_type = cst_cor_blk;
1650 CURSOR c_pee_details_2 IS
1651 SELECT PEE.pee_start_dt,
1652 PEE.expiry_dt
1653 FROM IGS_PE_PERSENC_EFFCT PEE
1654 WHERE person_id = p_person_id AND
1655 s_encmb_effect_type = cst_cor_blk AND
1656 course_cd = p_course_cd;
1657 BEGIN
1658 -- This module validates whether or not
1659 -- a IGS_PE_PERSON has an encumbracne blocking the
1660 -- issue of systme generated correspondence
1661 p_message_name := null;
1662 -- validating the input parameters
1663 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1664 RETURN TRUE;
1665 END IF;
1666 -- check if a IGS_PS_COURSE code is required for the
1667 -- System Correspondence Blocked effect type
1668 OPEN c_course_ind;
1669 FETCH c_course_ind INTO v_apply_to_course_ind;
1670 -- check if a record was found
1671 IF (c_course_ind%NOTFOUND) THEN
1672 CLOSE c_course_ind;
1673 RETURN TRUE;
1674 ELSE
1675 CLOSE c_course_ind;
1676 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1677 RETURN TRUE;
1678 END IF;
1679 END IF;
1680 -- validate for an exclusion from the university -
1681 -- invoke and existing function to perform this check
1682 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1683 p_course_cd,
1684 p_effective_dt,
1685 p_message_name) = FALSE) THEN
1686 RETURN FALSE;
1687 END IF;
1688 -- validate for an encumbrance which blocks the issue
1689 -- of system generated correspondence
1690 IF (v_apply_to_course_ind = 'N') THEN
1691 -- check when the System Correspondence Blocked effect
1692 -- type is not applied to a IGS_PS_COURSE
1693 FOR c_pee_rec IN c_pee_details_1 LOOP
1694 IF (c_pee_rec.expiry_dt IS NULL) THEN
1695 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1696 p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1697 RETURN FALSE;
1698 END IF;
1699 ELSE -- expiry_dt is set
1700 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1701 (c_pee_rec.expiry_dt - 1)) THEN
1702 p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1703 RETURN FALSE;
1704 END IF;
1705 END IF;
1706 END LOOP;
1707 ELSE -- v_apply_to_course_ind = 'Y'
1708 -- check when the System Correspondence Blocked effect
1709 -- type is applied to a IGS_PS_COURSE
1710 FOR c_pee_rec IN c_pee_details_2 LOOP
1711 IF (c_pee_rec.expiry_dt IS NULL) THEN
1712 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1713 p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1714 RETURN FALSE;
1715 END IF;
1716 ELSE -- expiry_dt is set
1717 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1718 (c_pee_rec.expiry_dt - 1)) THEN
1719 p_message_name := 'IGS_EN_PERS_BLK_ISSUE_OF_CORR';
1720 RETURN FALSE;
1721 END IF;
1722 END IF;
1723 END LOOP;
1724 END IF;
1725 RETURN TRUE;
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1729 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_sys_cor');
1730 IGS_GE_MSG_STACK.ADD;
1731 App_Exception.Raise_Exception;
1732
1733
1734 END;
1735 END enrp_val_blk_sys_cor;
1736 --
1737 -- Validate whether a IGS_PE_PERSON has an encumbrance blocking acad transcript.
1738 FUNCTION enrp_val_blk_trscrpt(
1739 p_person_id IN NUMBER ,
1740 p_course_cd IN VARCHAR2 ,
1741 p_effective_dt IN DATE ,
1742 p_message_name OUT NOCOPY VARCHAR2)
1743 RETURN BOOLEAN AS
1744 BEGIN
1745 DECLARE
1746 cst_trans_blk CONSTANT VARCHAR2(10) := 'TRANS_BLK';
1747 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP.apply_to_course_ind%TYPE;
1748 v_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE;
1749 v_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE;
1750 CURSOR c_course_ind IS
1751 SELECT SEET.apply_to_course_ind
1752 FROM IGS_EN_ENCMB_EFCTTYP_V SEET
1753 WHERE s_encmb_effect_type = cst_trans_blk;
1754 CURSOR c_pee_details_1 IS
1755 SELECT PEE.pee_start_dt,
1756 PEE.expiry_dt
1757 FROM IGS_PE_PERSENC_EFFCT PEE
1758 WHERE person_id = p_person_id AND
1759 s_encmb_effect_type = cst_trans_blk;
1760 CURSOR c_pee_details_2 IS
1761 SELECT PEE.pee_start_dt,
1762 PEE.expiry_dt
1763 FROM IGS_PE_PERSENC_EFFCT PEE
1764 WHERE person_id = p_person_id AND
1765 s_encmb_effect_type = cst_trans_blk AND
1766 course_cd = p_course_cd;
1767 BEGIN
1768 -- This module validates whether or not
1769 -- a IGS_PE_PERSON has an encumbrance blocking the
1770 -- issue of an academic transcript
1771 p_message_name := null;
1772 -- validating the input parameters
1773 IF (p_person_id IS NULL OR p_effective_dt IS NULL) THEN
1774 RETURN TRUE;
1775 END IF;
1776 -- check if a IGS_PS_COURSE code is required for the
1777 -- Transcript Blocked effect type
1778 OPEN c_course_ind;
1779 FETCH c_course_ind INTO v_apply_to_course_ind;
1780 -- check if a record was found
1781 IF (c_course_ind%NOTFOUND) THEN
1782 CLOSE c_course_ind;
1783 RETURN TRUE;
1784 ELSE
1785 CLOSE c_course_ind;
1786 IF (v_apply_to_course_ind = 'Y' AND p_course_cd IS NULL) THEN
1787 RETURN TRUE;
1788 END IF;
1789 END IF;
1790 -- validate for an exclusion from the university -
1791 -- invoke and existing function to perform this check
1792 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (p_person_id,
1793 p_course_cd,
1794 p_effective_dt,
1795 p_message_name) = FALSE) THEN
1796 RETURN FALSE;
1797 END IF;
1798 -- validate for an encumbrance which blocks the issue
1799 -- of the academic transcript
1800 IF (v_apply_to_course_ind = 'N') THEN
1801 -- check when the Transcript Blocked effect
1802 -- type is not applied to a IGS_PS_COURSE
1803 FOR c_pee_rec IN c_pee_details_1 LOOP
1804 IF (c_pee_rec.expiry_dt IS NULL) THEN
1805 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1806 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1807 RETURN FALSE;
1808 END IF;
1809 ELSE -- expiry_dt is set
1810 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1811 (c_pee_rec.expiry_dt - 1)) THEN
1812 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1813 RETURN FALSE;
1814 END IF;
1815 END IF;
1816 END LOOP;
1817 ELSE -- v_apply_to_course_ind = 'Y'
1818 -- check whether the restricted type effect
1819 -- is applied to a IGS_PS_COURSE
1820 FOR c_pee_rec IN c_pee_details_2 LOOP
1821 IF (c_pee_rec.expiry_dt IS NULL) THEN
1822 IF (c_pee_rec.pee_start_dt <= p_effective_dt) THEN
1823 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1824 RETURN FALSE;
1825 END IF;
1826 ELSE -- expiry_dt is set
1827 IF (p_effective_dt BETWEEN c_pee_rec.pee_start_dt AND
1828 (c_pee_rec.expiry_dt - 1)) THEN
1829 p_message_name := 'IGS_EN_PRSN_ENCUMB_ISS_ACATRN';
1830 RETURN FALSE;
1831 END IF;
1832 END IF;
1833 END LOOP;
1834 END IF;
1835 RETURN TRUE;
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1839 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_blk_trscrpt');
1840 IGS_GE_MSG_STACK.ADD;
1841 App_Exception.Raise_Exception;
1842
1843
1844 END;
1845 END enrp_val_blk_trscrpt;
1846 --
1847 -- Valiate enrolment encumbrances related to load periods
1848 FUNCTION ENRP_VAL_ENR_ENCMB(
1849 p_person_id IN NUMBER ,
1850 p_course_cd IN VARCHAR2 ,
1851 p_cal_type IN VARCHAR2 ,
1852 p_ci_sequence_number IN NUMBER ,
1853 p_message_name OUT NOCOPY VARCHAR2,
1854 p_message_name2 OUT NOCOPY varchar2 ,
1855 p_return_type OUT NOCOPY VARCHAR2,
1856 p_effective_dt IN DATE DEFAULT NULL)
1857 RETURN BOOLEAN AS
1858 BEGIN
1859 DECLARE
1860 -------------------------------------------------------------------------------------------
1861 --Change History:
1862 --Who When What
1863 --svanukur 05-DEC-2003 The calendar parameters now refer to the load calendar and not the academic calendar.
1864 -- This was done as part of the bug 3227399 to validate holds only for the term in question and
1865 -- not the entire academic year.
1866 -- p_cal_type and p_ci_sequence_number are the load calendar instance details and
1867 -- the curosor
1868 -------------------------------------------------------------------------------------------
1869
1870
1871 cst_acad CONSTANT VARCHAR2(10) := 'ACADEMIC';
1872 v_load_error BOOLEAN;
1873 v_required_error BOOLEAN;
1874
1875 v_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
1876 v_period_cp NUMBER;
1877 v_period_load NUMBER;
1878 v_credit_points NUMBER;
1879 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
1880 v_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE;
1881 v_restricted_attendance_type
1882 IGS_PE_PERSENC_EFFCT.restricted_attendance_type%TYPE;
1883 v_rstrct_le_cp_value IGS_PE_PERSENC_EFFCT.restricted_enrolment_cp%TYPE;
1884 v_rstrct_ge_cp_value IGS_PE_PERSENC_EFFCT.restricted_enrolment_cp%TYPE;
1885 v_return_type VARCHAR2(1);
1886 v_load_effect_dt_alias IGS_EN_CAL_CONF.load_effect_dt_alias%TYPE;
1887 v_load_effect_dt DATE;
1888 v_message_name VARCHAR2(200);
1889
1890
1891
1892
1893 -- Getting the academic calender for the load calender.
1894 CURSOR sup_cal(cp_cal_Type igs_ca_inst.cal_type%TYPE, cp_cal_seq igs_ca_inst.sequence_number%TYPE) IS
1895 SELECT SUP_CAL_TYPE, SUP_CI_SEQUENCE_NUMBER
1896 FROM igs_ca_inst_rel
1897 WHERE sub_cal_type = cp_cal_Type
1898 AND sub_ci_sequence_number = cp_cal_seq
1899 AND sup_cal_type
1900 IN (SELECT CAL_TYPE FROM IGS_CA_TYPE WHERE S_CAL_CAT = 'ACADEMIC');
1901
1902 v_sup_rec sup_cal%ROWTYPE;
1903 l_effective_dt DATE;
1904 BEGIN
1905 -- Apply all of the encumbrance validation checks for all load calendar
1906 -- instances within a nominated academic calendar instance. All of the
1907 -- encumbrance checks are point in time based. This routine uses the 'load
1908 -- effective' date from all of the load calendars as the effective date for
1909 -- the encumbrance checks.
1910 -- This routine returns 2 possible message numbers - the first is for 'load'
1911 -- encumbrance errors and the second for 'restricted IGS_PS_UNIT' encumbrance
1912 -- errors. The routine will run for all periods even if an error has been
1913 -- found - the user needs to see the errors of both types.
1914
1915 p_message_name := null;
1916 p_message_name2 := null;
1917 v_load_error := FALSE;
1918 v_required_error := FALSE;
1919
1920 --fetch the superior acad calendar.
1921 OPEN sup_cal(p_cal_type, p_ci_sequence_number) ;
1922 FETCH sup_cal INTO v_sup_rec;
1923 CLOSE sup_cal;
1924
1925 --get the census date of the passed in load cal
1926 IF p_effective_dt IS NULL THEN
1927 l_effective_dt := Igs_En_Gen_015.get_effective_census_date(p_cal_type,
1928 p_ci_sequence_number,
1929 NULL,
1930 NULL);
1931 ELSE
1932 l_effective_dt := p_effective_dt;
1933 END IF;
1934
1935 --if census date is not found, then return true
1936 IF l_effective_dt IS NULL THEN
1937 RETURN TRUE;
1938 END IF;
1939
1940 -- call the routine to check whether the student
1941 -- IGS_PS_COURSE attempt has a credit point restriction
1942 IF(IGS_EN_VAL_ENCMB.ENRP_VAL_RSTRCT_CP(
1943 p_person_id,
1944 p_course_cd,
1945 l_effective_dt,
1946 v_rstrct_le_cp_value,
1947 v_rstrct_ge_cp_value,
1948 v_message_name) = FALSE) THEN
1949 -- call the routine to calculate the credit point
1950 -- figure for the load calendar
1951 v_period_cp := IGS_EN_PRC_LOAD.ENRP_CLC_LOAD_TOTAL(
1952 p_person_id => p_person_id,
1953 p_course_cd => p_course_cd,
1954 p_acad_cal_type =>v_sup_rec.sup_cal_type,
1955 p_acad_sequence_number =>v_sup_rec.sup_ci_sequence_number,
1956 p_load_cal_type => p_cal_type,
1957 p_load_sequence_number =>p_ci_sequence_number
1958 );
1959 -- depending on whether the restriction is 'greater or equal'
1960 -- or 'less ot equal' test against the calcuated credit points
1961 -- provided not equal to 0 (0 returned if no IGS_PS_UNIT exist for the load period)
1962 IF NVL(v_period_cp, 0) <> 0 THEN
1963 IF (v_rstrct_le_cp_value IS NOT NULL AND
1964 v_rstrct_ge_cp_value IS NOT NULL) THEN
1965 IF (v_period_cp NOT BETWEEN v_rstrct_le_cp_value AND
1966 v_rstrct_ge_cp_value) THEN
1967 p_message_name := 'IGS_EN_PRSN_ENR_CRDPNT_VALUE';
1968 v_load_error := TRUE;
1969 END IF;
1970 ELSIF (v_rstrct_le_cp_value IS NOT NULL) THEN
1971 IF (v_period_cp > v_rstrct_le_cp_value) THEN
1972 p_message_name := 'IGS_EN_PRSN_ENRCRDPNT';
1973 v_load_error := TRUE;
1974 END IF;
1975 ELSIF (v_rstrct_ge_cp_value IS NOT NULL) THEN
1976 IF (v_period_cp < v_rstrct_ge_cp_value) THEN
1977 p_message_name := 'IGS_EN_PRSN_ENR_CRDPOINT';
1978 v_load_error := TRUE;
1979 END IF;
1980 END IF;
1981 END IF; --NVL(v_period_cp, 0)
1982
1983 -- call the routine to check whether the student IGS_PS_COURSE
1984 -- attempt has a restricted attendance type
1985 ELSIF(IGS_EN_VAL_ENCMB.ENRP_VAL_RSTRCT_ATYP(
1986 p_person_id,
1987 p_course_cd,
1988 l_effective_dt,
1989 v_restricted_attendance_type,
1990 v_message_name) = FALSE) THEN
1991 -- call routine to calculate the load figure for
1992 -- the load calendar
1993 v_period_load := IGS_EN_PRC_LOAD.ENRP_CLC_EFTSU_TOTAL(
1994 p_person_id => p_person_id,
1995 p_course_cd => p_course_cd,
1996 p_acad_cal_type => v_sup_rec.sup_cal_type,
1997 p_acad_sequence_number => v_sup_rec.sup_ci_sequence_number ,
1998 p_load_cal_type => p_cal_type,
1999 p_load_sequence_number => p_ci_sequence_number,
2000 p_truncate_ind => 'Y',
2001 p_include_research_ind => 'Y',
2002 p_key_course_cd => NULL,
2003 p_key_version_number => NULL,
2004 p_credit_points => v_credit_points);
2005 -- call the routine to determine the attendance type
2006 -- for calculated load figure within the load calendar
2007 v_attendance_type := IGS_EN_PRC_LOAD.ENRP_GET_LOAD_ATT(
2008 p_cal_type,
2009 v_period_load);
2010 IF(v_attendance_type IS NOT NULL and
2011 v_attendance_type <> v_restricted_attendance_type) THEN
2012 p_message_name := 'IGS_EN_PRSN_ATTTYPE_NE_ATT_TY';
2013 v_load_error := TRUE;
2014 END IF;
2015 END IF;
2016
2017
2018
2019 -- check whether there is an outstanding encumbrance
2020 -- for a required IGS_PS_UNIT
2021
2022 IF(IGS_EN_VAL_ENCMB.ENRP_VAL_RQRD_UNITS(
2023 p_person_id,
2024 p_course_cd,
2025 l_effective_dt,
2026 v_message_name,
2027 v_return_type) = FALSE) THEN
2028 p_message_name2 := v_message_name;
2029 p_return_type := v_return_type;
2030 v_required_error := TRUE;
2031
2032 END IF;
2033
2034
2035 --return false if any one check failed.
2036 IF(v_load_error = TRUE OR v_required_error = TRUE) THEN
2037 RETURN FALSE;
2038 ELSE
2039 RETURN TRUE;
2040 END IF;
2041 EXCEPTION
2042 WHEN OTHERS THEN
2043 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2044 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_ENCMB.enrp_val_enr_encmbf 2');
2045 IGS_GE_MSG_STACK.ADD;
2046 App_Exception.Raise_Exception;
2047 END;
2048 END enrp_val_enr_encmb;
2049
2050 END IGS_EN_VAL_ENCMB;
[Home] [Help]