1 PACKAGE BODY IGS_PR_VAL_POT AS
2 /* $Header: IGSPR03B.pls 115.8 2002/11/29 05:17:58 nalkumar ship $ */
3 -- Validate the dflt_restricted_enrolment_cp field.
4 --
5 -- bug id : 1956374
6 -- sjadhav ,28-aug-2001
7 -- removed function enrp_val_et_closed
8 --
9 ----------------------------------------------------------------------------
10 -- Change History :
11 -- Who When What
12 -- ayedubat 15-DEC-2001 Enhance Bug no : 2138644 , changed the Function, prgp_val_pot_et
13 -- avenkatr 30-AUG-2001 Bug Id: 1956374, Removed procedure "crsp_val_att_closed"
14 -- Nalin Kumar 12-NOV-2002 Modified the 'prgp_val_pot_et' function as per the FA110 PR-ENH. bug# 2658550
15 ----------------------------------------------------------------------------
16 FUNCTION prgp_val_pot_att(
17 p_dflt_restricted_att_type IN VARCHAR2 ,
18 p_encumbrance_type IN VARCHAR2 ,
19 p_message_name OUT NOCOPY VARCHAR2 )
20 RETURN BOOLEAN AS
21 gv_other_detail VARCHAR2(255);
22 BEGIN -- prgp_val_pot_att
23 -- Validate the IGS_PR_OU_TYPE.dflt_restricted_att_type field.
24 -- Should only be specified where the related encumbrance type has a
25 -- restricted attendance type related effect.
26 DECLARE
27 cst_rstr_at_ty CONSTANT VARCHAR2(10) := 'RSTR_AT_TY';
28 v_dummy VARCHAR2(1);
29 CURSOR c_etde IS
30 SELECT 'X'
31 FROM IGS_FI_ENC_DFLT_EFT etde
32 WHERE etde.encumbrance_type = p_encumbrance_type AND
33 etde.s_encmb_effect_type = cst_rstr_at_ty;
34 BEGIN
35 -- Set the default message number
36 p_message_name := null;
37 IF p_dflt_restricted_att_type IS NULL THEN
38 RETURN TRUE;
39 END IF;
40 IF p_encumbrance_type IS NULL THEN
41 p_message_name := 'IGS_PR_CHK_DFLT_ATTEND_TYPE';
42 RETURN FALSE;
43 END IF;
44 OPEN c_etde;
45 FETCH c_etde INTO v_dummy;
46 IF c_etde%NOTFOUND THEN
47 CLOSE c_etde;
48 p_message_name := 'IGS_PR_CHK_DFLT_ATTEND_TYPE';
49 RETURN FALSE;
50 ELSE
51 CLOSE c_etde;
52 END IF;
53 RETURN TRUE;
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF c_etde%ISOPEN THEN
57 CLOSE c_etde;
58 END IF;
59 RAISE;
60 END;
61 EXCEPTION
62 WHEN OTHERS THEN
63 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
64 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_POT_ATT');
65 --IGS_GE_MSG_STACK.ADD;
66
67 END prgp_val_pot_att;
68
69 -- Validate the dflt_restricted_enrolment_cp field.
70 FUNCTION prgp_val_pot_cp(
71 p_dflt_restricted_enrolment_cp IN NUMBER ,
72 p_encumbrance_type IN VARCHAR2 ,
73 p_message_name OUT NOCOPY VARCHAR2 )
74 RETURN BOOLEAN AS
75 gv_other_detail VARCHAR2(255);
76 BEGIN -- prgp_val_pot_cp
77 -- Validate the IGS_PR_OU_TYPE.dflt_restricted_enrolment_cp field.
78 -- Should only be specified where the related encumbrance type has a
79 -- restricted enrolment related effect.
80 DECLARE
81 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
82 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
83 v_dummy VARCHAR2(1);
84 CURSOR c_etde IS
85 SELECT 'X'
86 FROM IGS_FI_ENC_DFLT_EFT etde
87 WHERE etde.encumbrance_type = p_encumbrance_type AND
88 etde.s_encmb_effect_type IN (
89 cst_rstr_ge_cp,
90 cst_rstr_le_cp);
91 BEGIN
92 -- Set the default message number
93 p_message_name := null;
94 IF p_dflt_restricted_enrolment_cp IS NULL THEN
95 RETURN TRUE;
96 END IF;
97 IF p_encumbrance_type IS NULL THEN
98 p_message_name := 'IGS_PR_SPECIF_ONLY_DFT_ENR_CP';
99 RETURN FALSE;
100 END IF;
101 OPEN c_etde;
102 FETCH c_etde INTO v_dummy;
103 IF c_etde%NOTFOUND THEN
104 CLOSE c_etde;
105 p_message_name := 'IGS_PR_SPECIF_ONLY_DFT_ENR_CP';
106 RETURN FALSE;
107 ELSE
108 CLOSE c_etde;
109 END IF;
110 RETURN TRUE;
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF c_etde%ISOPEN THEN
114 CLOSE c_etde;
115 END IF;
116 RAISE;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
121 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_POT_CP');
122 --IGS_GE_MSG_STACK.ADD;
123
124 END prgp_val_pot_cp;
125
126 -- Validate the Encumbrance Type.
127 FUNCTION prgp_val_pot_et(
128 p_s_progression_outcome_type IN VARCHAR2 ,
129 p_encumbrance_type IN VARCHAR2 ,
130 p_message_name OUT NOCOPY VARCHAR2)
131 RETURN BOOLEAN AS
132 gv_other_detail VARCHAR2(255);
133 /* Change History
134 WHO WHEN WHAT
135 ayedubat 15/12/2001 Added the Validation if the System Progression Outcome Type
136 is 'AWARD' as per Enhancement Bug No : 2138644
137 Nalin Kumar 13/11/2002 Added the Validation if the System Progression Outcome Type
138 is 'FUND' as per Enhancement Bug No : 2658550 */
139
140 BEGIN -- prgp_val_pot_et
141 -- Validate the encumbrance type for a IGS_PR_OU_TYPE record based
142 -- on the s_progression_outcome_type which has been entered.
143 DECLARE
144 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
145 cst_expulsion CONSTANT VARCHAR2(10) := 'EXPULSION';
146 cst_exclusion CONSTANT VARCHAR2(10) := 'EXCLUSION';
147 cst_suspension CONSTANT VARCHAR2(10) := 'SUSPENSION';
148 cst_probation CONSTANT VARCHAR2(10) := 'PROBATION';
149 cst_nopenalty CONSTANT VARCHAR2(10) := 'NOPENALTY';
150 cst_manual CONSTANT VARCHAR2(10) := 'MANUAL';
151 cst_award CONSTANT VARCHAR2(10) := 'AWARD';
152 cst_sus CONSTANT VARCHAR2(10) := 'SUS%';
153 cst_sus_course CONSTANT VARCHAR2(10) := 'SUS_COURSE';
154 cst_exc CONSTANT VARCHAR2(10) := 'EXC%';
155 cst_exc_course CONSTANT VARCHAR2(10) := 'EXC_COURSE';
156 cst_exc_crs_gp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
157 cst_rqrd_crs_u CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
158 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
159 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
160 cst_rstr_at_ty CONSTANT VARCHAR2(10) := 'RSTR_AT_TY';
161 --
162 cst_ex_fund CONSTANT VARCHAR2(15) := 'EX_FUND';
163 cst_ex_sp_awd CONSTANT VARCHAR2(10) := 'EX_SP_AWD';
164 cst_ex_sp_disb CONSTANT VARCHAR2(10) := 'EX_SP_DISB';
165 cst_ex_awd CONSTANT VARCHAR2(10) := 'EX_AWD';
166 cst_ex_disb CONSTANT VARCHAR2(10) := 'EX_DISB';
167 v_ex_fund_1_exists BOOLEAN := FALSE;
168
169 --
170 v_s_encumbrance_cat IGS_FI_ENCMB_TYPE.s_encumbrance_cat%TYPE;
171 v_suspension_1_exists BOOLEAN := FALSE;
172 v_suspension_2_exists BOOLEAN := FALSE;
173 v_exclusion_1_exists BOOLEAN := FALSE;
174 v_exclusion_2_exists BOOLEAN := FALSE;
175 v_expulsion_1_exists BOOLEAN := FALSE;
176 v_probation_1_exists BOOLEAN := FALSE;
177 v_probation_2_exists BOOLEAN := FALSE;
178 CURSOR c_et IS
179 SELECT et.s_encumbrance_cat
180 FROM IGS_FI_ENCMB_TYPE et
181 WHERE et.encumbrance_type = p_encumbrance_type;
182 CURSOR c_edte IS
183 SELECT edte.s_encmb_effect_type
184 FROM IGS_FI_ENC_DFLT_EFT edte
185 WHERE edte.encumbrance_type = p_encumbrance_type;
186 BEGIN
187 -- Set the default message number
188 p_message_name := null;
189 IF p_s_progression_outcome_type IN (
190 cst_expulsion,
191 cst_exclusion,
192 cst_suspension,
193 cst_probation,
194 cst_ex_fund) AND
195 p_encumbrance_type IS NULL THEN
196 p_message_name := 'IGS_PR_CHK_PRG_OUT_TYPE';
197 RETURN FALSE;
198 END IF;
199 IF p_encumbrance_type IS NULL THEN
200 RETURN TRUE;
201 END IF;
202 OPEN c_et;
203 FETCH c_et INTO v_s_encumbrance_cat;
204 IF c_et%FOUND THEN
205 CLOSE c_et;
206 IF v_s_encumbrance_cat <> cst_academic THEN
207 p_message_name := 'IGS_PR_ENCUM_TYPE_MUST_BE_ACA';
208 RETURN FALSE;
209 END IF;
210 ELSE
211 CLOSE c_et;
212 RETURN TRUE;
213 END IF;
214 FOR v_edte_rec IN c_edte LOOP
215 IF p_s_progression_outcome_type = cst_suspension THEN
216 IF v_edte_rec.s_encmb_effect_type = cst_sus_course THEN
217 v_suspension_1_exists := TRUE;
218 END IF;
219 IF v_edte_rec.s_encmb_effect_type LIKE cst_exc THEN
220 v_suspension_2_exists := TRUE;
221 END IF;
222 END IF;
223 IF p_s_progression_outcome_type IN (
224 cst_exclusion,
225 cst_expulsion) THEN
226 IF p_s_progression_outcome_type = cst_exclusion THEN
227 v_expulsion_1_exists := TRUE;
228 IF v_edte_rec.s_encmb_effect_type LIKE cst_exc THEN
229 v_exclusion_1_exists := TRUE;
230 END IF;
231 ELSE
232 v_exclusion_1_exists := TRUE;
233 IF v_edte_rec.s_encmb_effect_type IN (
234 cst_exc_course,
235 cst_exc_crs_gp) THEN
236 v_expulsion_1_exists := TRUE;
237 END IF;
238 END IF;
239 IF v_edte_rec.s_encmb_effect_type LIKE cst_sus THEN
240 v_exclusion_2_exists := TRUE;
241 END IF;
242 END IF;
243 IF p_s_progression_outcome_type = cst_probation THEN
244 IF v_edte_rec.s_encmb_effect_type IN (
245 cst_rqrd_crs_u,
246 cst_rstr_ge_cp,
247 cst_rstr_le_cp,
248 cst_rstr_at_ty) THEN
249 v_probation_1_exists := TRUE;
250 END IF;
251 IF v_edte_rec.s_encmb_effect_type LIKE cst_sus OR
252 v_edte_rec.s_encmb_effect_type LIKE cst_exc THEN
253 v_probation_2_exists := TRUE;
254 END IF;
255 END IF;
256 --Start of new code added as per FA110 PR-ENH. bug# 2658550
257 IF p_s_progression_outcome_type = cst_ex_fund THEN
258 IF v_edte_rec.s_encmb_effect_type IN (cst_ex_sp_awd, cst_ex_sp_disb, cst_ex_awd, cst_ex_disb) THEN
259 v_ex_fund_1_exists := TRUE;
260 END IF;
261 END IF;
262 --End of new code added as per FA110 PR-ENH. bug# 2658550
263 END LOOP;
264 IF p_s_progression_outcome_type = cst_suspension THEN
265 IF NOT v_suspension_1_exists THEN
266 p_message_name := 'IGS_PR_CHK_SUSP_PRG_OUTCOME';
267 RETURN FALSE;
268 END IF;
269 IF v_suspension_2_exists THEN
270 p_message_name := 'IGS_PR_CHK_SUSP_PRG_OUT_TYPES';
271 RETURN FALSE;
272 END IF;
273 RETURN TRUE;
274 END IF;
275 IF p_s_progression_outcome_type IN (
276 cst_exclusion,
277 cst_expulsion) THEN
278 IF NOT v_exclusion_1_exists OR
279 NOT v_expulsion_1_exists THEN
280 p_message_name := 'IGS_PR_CHK_EXCL_PRG_OUT_TYPES';
281 RETURN FALSE;
282 END IF;
283 IF v_exclusion_2_exists THEN
284 p_message_name := 'IGS_PR_CHK_EXPL_PRG_OUT_TYPES';
285 RETURN FALSE;
286 END IF;
287 RETURN TRUE;
288 END IF;
289 IF p_s_progression_outcome_type = cst_probation THEN
290 IF NOT v_probation_1_exists THEN
291 p_message_name := 'IGS_PR_CHK_PROB_PRG_OUT_TYPES';
292 RETURN FALSE;
293 END IF;
294 IF v_probation_2_exists THEN
295 p_message_name := 'IGS_PR_INVALID_PRG_OUTCOMES';
296 RETURN FALSE;
297 END IF;
298 RETURN TRUE;
299 END IF;
300 IF p_s_progression_outcome_type = cst_nopenalty AND
301 p_encumbrance_type IS NOT NULL THEN
302 p_message_name :='IGS_PR_CANNOT_LNK_NO_PRG_OUT';
303 RETURN FALSE;
304 END IF;
305 IF p_s_progression_outcome_type = cst_manual AND
306 p_encumbrance_type IS NOT NULL THEN
307 p_message_name := 'IGS_PR_CANT_LINK_MANUAL_PRG';
308 RETURN FALSE;
309 END IF;
310 IF p_s_progression_outcome_type = cst_award AND
311 p_encumbrance_type IS NOT NULL THEN
312 p_message_name := 'IGS_PR_CANT_LINK_AWARD_PRG';
313 RETURN FALSE;
314 END IF;
315 --Start of new code added as per FA110 PR-ENH. bug# 2658550
316 IF p_s_progression_outcome_type = cst_ex_fund THEN
317 IF NOT v_ex_fund_1_exists THEN
318 p_message_name := 'IGS_PR_CHK_EX_FND_PRG_OUT_TYPE';
319 RETURN FALSE;
320 END IF;
321 END IF;
322 --End of new code added as per FA110 PR-ENH. bug# 2658550
323 RETURN TRUE;
324 EXCEPTION
325 WHEN OTHERS THEN
326 IF c_et%ISOPEN THEN
327 CLOSE c_et;
328 END IF;
329 IF c_edte%ISOPEN THEN
330 CLOSE c_edte;
331 END IF;
332 RAISE;
333 END;
334 EXCEPTION
335 WHEN OTHERS THEN
336 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
337 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_POT_ET');
338 --IGS_GE_MSG_STACK.ADD;
339
340 END prgp_val_pot_et;
341
342 -- Validate the Change of Encumbrance Type.
343 FUNCTION prgp_val_pot_et_upd(
344 p_progression_outcome_type IN VARCHAR2 ,
345 p_old_encumbrance_type IN VARCHAR2 ,
346 p_new_encumbrance_type IN VARCHAR2 ,
347 p_message_name OUT NOCOPY VARCHAR2 )
348 RETURN BOOLEAN AS
349 gv_other_detail VARCHAR2(255);
350 BEGIN -- prgp_val_pot_et_upd
351 -- Validate the change of encumbrance type for a progression outcome type.
352 -- The encumbrance type cannot be changed once the progression outcome type
353 -- has been used.
354 DECLARE
355 v_dummy VARCHAR2(1);
356 CURSOR c_pro IS
357 SELECT 'X'
358 FROM IGS_PR_RU_OU pro
359 WHERE pro.progression_outcome_type = p_progression_outcome_type;
360 CURSOR c_spo IS
361 SELECT 'X'
362 FROM IGS_PR_STDNT_PR_OU spo
363 WHERE spo.progression_outcome_type = p_progression_outcome_type;
364 BEGIN
365 -- Set the default message number
366 p_message_name := null;
367 IF NVL(p_old_encumbrance_type, ' ') = NVL(p_new_encumbrance_type, ' ') THEN
368 RETURN TRUE;
369 END IF;
370 OPEN c_pro;
371 FETCH c_pro INTO v_dummy;
372 IF c_pro%FOUND THEN
373 CLOSE c_pro;
374 p_message_name := 'IGS_PR_ENCUM_TYPE_CANT_CHANGE';
375 RETURN FALSE;
376 ELSE
377 CLOSE c_pro;
378 OPEN c_spo;
379 FETCH c_spo INTO v_dummy;
380 IF c_spo%FOUND THEN
381 CLOSE c_spo;
382 p_message_name := 'IGS_PR_ENCUM_TYPE_CANT_CHANGE';
383 RETURN FALSE;
384 ELSE
385 CLOSE c_spo;
386 END IF;
387 END IF;
388 RETURN TRUE;
389 EXCEPTION
390 WHEN OTHERS THEN
391 IF c_pro%ISOPEN THEN
392 CLOSE c_pro;
393 END IF;
394 IF c_spo%ISOPEN THEN
395 CLOSE c_spo;
396 END IF;
397 RAISE;
398 END;
399 EXCEPTION
400 WHEN OTHERS THEN
401 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
402 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_POT_ET_UPD');
403 --IGS_GE_MSG_STACK.ADD;
404
405 END prgp_val_pot_et_upd;
406
407 -- Validate the Change of System Progression Outcome Type.
408 FUNCTION prgp_val_pot_spot_u(
409 p_progression_outcome_type IN VARCHAR2 ,
410 p_old_s_prg_outcome_type IN VARCHAR2 ,
411 p_new_s_prg_outcome_type IN VARCHAR2 ,
412 p_message_name OUT NOCOPY VARCHAR2 )
413 RETURN BOOLEAN AS
414 gv_other_detail VARCHAR2(255);
415 BEGIN -- prgp_val_pot_spot_u
416 -- Validate the change of system progression outcome type for a progression
417 -- outcome type.
418 -- The system type cannot be changed once the progression outcome type has
419 -- been used.
420 DECLARE
421 v_dummy VARCHAR2(1);
422 CURSOR c_pro IS
423 SELECT 'X'
424 FROM IGS_PR_RU_OU pro
425 WHERE pro.progression_outcome_type = p_progression_outcome_type;
426 CURSOR c_spo IS
427 SELECT 'X'
428 FROM IGS_PR_STDNT_PR_OU spo
429 WHERE spo.progression_outcome_type = p_progression_outcome_type;
430 BEGIN
431 -- Set the default message number
432 p_message_name := null;
436 OPEN c_pro;
433 IF NVL(p_old_s_prg_outcome_type, ' ') = NVL(p_new_s_prg_outcome_type, ' ') THEN
434 RETURN TRUE;
435 END IF;
437 FETCH c_pro INTO v_dummy;
438 IF c_pro%FOUND THEN
439 CLOSE c_pro;
440 p_message_name := 'IGS_PR_OUT_TYPE_CANT_CHANGED';
441 RETURN FALSE;
442 ELSE
443 CLOSE c_pro;
444 OPEN c_spo;
445 FETCH c_spo INTO v_dummy;
446 IF c_spo%FOUND THEN
447 CLOSE c_spo;
448 p_message_name := 'IGS_PR_OUT_TYPE_CANT_CHANGED';
449 RETURN FALSE;
450 ELSE
451 CLOSE c_spo;
452 END IF;
453 END IF;
454 RETURN TRUE;
455 EXCEPTION
456 WHEN OTHERS THEN
457 IF c_pro%ISOPEN THEN
458 CLOSE c_pro;
459 END IF;
460 IF c_spo%ISOPEN THEN
461 CLOSE c_spo;
462 END IF;
463 RAISE;
464 END;
465 EXCEPTION
466 WHEN OTHERS THEN
467 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
468 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_POT_SPOT_U');
469 --IGS_GE_MSG_STACK.ADD;
470
471 END prgp_val_pot_spot_u;
472
473
474 -- Validate that the s_progression_outcome_type is not closed
475 FUNCTION prgp_val_spot_closed(
476 p_s_prog_outcome_type IN VARCHAR2 ,
477 p_message_name OUT NOCOPY VARCHAR2 )
478 RETURN BOOLEAN AS
479 gv_other_detail VARCHAR2(255);
480 BEGIN -- prgp_val_spot_closed
481 -- Validate if s_progression_outcome_type.s_progression_outcome_type is closed
482 DECLARE
483 v_closed_ind VARCHAR2(1);
484 CURSOR c_spot IS
485 SELECT spot.closed_ind
486 FROM IGS_LOOKUPS_VIEW spot
487 WHERE spot.LOOKUP_TYPE = p_s_prog_outcome_type;
488 BEGIN
489 --set default message_name
490 p_message_name := null;
491 OPEN c_spot;
492 FETCH c_spot INTO v_closed_ind;
493 IF c_spot%FOUND THEN
494 CLOSE c_spot;
495 IF v_closed_ind = 'Y' THEN
496 p_message_name := 'IGS_PR_SYS_PRG_OUT_TYPE_CLOSE';
497 RETURN FALSE;
498 END IF;
499 ELSE
500 CLOSE c_spot;
501 END IF;
502 RETURN TRUE;
503 EXCEPTION
504 WHEN OTHERS THEN
505 IF c_spot%ISOPEN THEN
506 CLOSE c_spot;
507 END IF;
508 RAISE;
509 END;
510 EXCEPTION
511 WHEN OTHERS THEN
512 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
513 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_POT.PRGP_VAL_SPOT_CLOSED');
514 --IGS_GE_MSG_STACK.ADD;
515
516 END prgp_val_spot_closed;
517 END IGS_PR_VAL_POT;
[Home] [Help]