1 PACKAGE BODY IGS_PR_VAL_PRO AS
2 /* $Header: IGSPR19B.pls 115.10 2002/11/29 02:48:45 nsidana ship $ */
3 /*
4 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
5 || Removed program unit (PRGP_VAL_OU_ACTIVE) - from the spec and body. -- kdande
6 */
7 -------------------------------------------------------------------------------------------
8 --Change History:
9 --Who When What
10 --smadathi 29-AUG-2001 Bug Id : 1956374. Removed procedure "prgp_val_appeal_ind"
11 --smadathi 29-AUG-2001 Bug Id : 1956374. Removed procedure "prgp_val_cause_ind"
12 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_att_closed"
13 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_cgr_closed"
14 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_crv_active"
15 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_cty_closed"
16 --Nalin Kumar 12-NOV-2002 Modified 'PRGP_VAL_PRO_POT' function as per the FA110 PR-ENH. Bug# 2658550
17 -------------------------------------------------------------------------------------------
18 --
19 -- Validate progression outcome type clolsed indicator
20 FUNCTION prgp_val_pot_closed(
21 p_progression_outcome_type IN VARCHAR2 ,
22 p_message_name OUT NOCOPY VARCHAR2 )
23 RETURN BOOLEAN IS
24 gv_other_detail VARCHAR2(255);
25 BEGIN -- prgp_val_pot_closed
26 -- Validate the progression_outcome_type is not closed.
27 DECLARE
28 v_dummy VARCHAR2(1);
29 CURSOR c_pot IS
30 SELECT 'X'
31 FROM IGS_PR_OU_TYPE pot
32 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
33 pot.closed_ind = 'N';
34 BEGIN
35 -- Set the default message number
36 p_message_name := null;
37 IF p_progression_outcome_type IS NULL THEN
38 RETURN TRUE;
39 END IF;
40 OPEN c_pot;
41 FETCH c_pot INTO v_dummy;
42 IF c_pot%NOTFOUND THEN
43 CLOSE c_pot;
44 p_message_name := 'IGS_PR_PRG_OUT_TYCLD';
45 RETURN FALSE;
46 END IF;
47 CLOSE c_pot;
48 RETURN TRUE;
49 EXCEPTION
50 WHEN OTHERS THEN
51 IF c_pot%ISOPEN THEN
52 CLOSE c_pot;
53 END IF;
54 RAISE;
55 END;
56 EXCEPTION
57 WHEN OTHERS THEN
58 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 END prgp_val_pot_closed;
62 --
63 -- Validate progression rule outcome restrict attendance type
64 FUNCTION prgp_val_pro_att(
65 p_progression_outcome_type IN VARCHAR2 ,
66 p_restricted_attendance_type IN VARCHAR2 ,
67 p_message_name OUT NOCOPY VARCHAR2 )
68 RETURN BOOLEAN IS
69 gv_other_detail VARCHAR2(255);
70 BEGIN -- prgp_val_pro_att
71 -- Validate that if progression_rule_outcome.restricted_attendance_type is set
72 -- that the progression_outcome_type relates to a s_encmb_effect_type of
73 -- RSTR_AT_TY
74 DECLARE
75 cst_rstr_at_ty CONSTANT VARCHAR(10) := 'RSTR_AT_TY';
76 v_dummy VARCHAR2(1);
77 CURSOR c_pot IS
78 SELECT 'X'
79 FROM IGS_PR_OU_TYPE pot,
80 IGS_FI_ENC_DFLT_EFT etde
81 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
82 pot.encumbrance_type = etde.encumbrance_type AND
83 etde.s_encmb_effect_type = cst_rstr_at_ty;
84 BEGIN
85 -- Set the default message number
86 p_message_name := null;
87 IF p_progression_outcome_type IS NULL OR
88 p_restricted_attendance_type IS NULL THEN
89 RETURN TRUE;
90 END IF;
91 OPEN c_pot;
92 FETCH c_pot INTO v_dummy;
93 IF c_pot%NOTFOUND THEN
94 CLOSE c_pot;
95 p_message_name := 'IGS_PR_RSTR_AT_TY';
96 RETURN FALSE;
97 END IF;
98 CLOSE c_pot;
99 RETURN TRUE;
100 EXCEPTION
101 WHEN OTHERS THEN
102 IF c_pot%ISOPEN THEN
103 CLOSE c_pot;
104 END IF;
105 RAISE;
106 END;
107 EXCEPTION
108 WHEN OTHERS THEN
109 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
110 IGS_GE_MSG_STACK.ADD;
111 App_Exception.Raise_Exception;
112 END prgp_val_pro_att;
113 --
114 -- Validate progression rule outcome automatically apply indicator
115 FUNCTION prgp_val_pro_auto(
116 p_progression_rule_cat IN VARCHAR2 ,
117 p_pra_sequence_number IN NUMBER ,
118 p_sequence_number IN NUMBER ,
119 p_progression_outcome_type IN VARCHAR2 ,
120 p_apply_automatically_ind IN VARCHAR2,
121 p_encmb_course_group_cd IN VARCHAR2 ,
122 p_restricted_enrolment_cp IN NUMBER ,
123 p_restricted_attendance_type IN VARCHAR2 ,
124 p_message_name OUT NOCOPY VARCHAR2 )
125 RETURN BOOLEAN IS
126 gv_other_detail VARCHAR2(255);
127 BEGIN -- prgp_val_pro_auto
128 -- Validate that if progression_rule_outcome.apply_automatically_ind is set
129 -- to 'Y' that the progression_outcome_type relates to a s_encmb_effect_type of
130 -- EXC_COURSE, EXC_CRS_GP or SUS_COURSE
131 DECLARE
132 cst_exc_course CONSTANT VARCHAR2(10) := 'EXC_COURSE';
133 cst_exc_crs_gp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
134 cst_sus_course CONSTANT VARCHAR2(10) := 'SUS_COURSE';
135 cst_exc_crs_us CONSTANT VARCHAR2(10) := 'EXC_CRS_US';
136 cst_exc_crs_u CONSTANT VARCHAR2(10) := 'EXC_CRS_U';
137 cst_rqrd_crs_u CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
138 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
139 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
140 cst_rstr_at_ty CONSTANT VARCHAR2(10) := 'RSTR_AT_TY';
141 cst_excluded CONSTANT VARCHAR2(10) := 'EXCLUDED';
142 cst_required CONSTANT VARCHAR2(10) := 'REQUIRED';
143 v_exit BOOLEAN := FALSE;
144 v_dummy VARCHAR2(1);
145 CURSOR c_pot_etde IS
146 SELECT etde.s_encmb_effect_type
147 FROM IGS_PR_OU_TYPE pot,
148 IGS_FI_ENC_DFLT_EFT etde
149 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
150 pot.encumbrance_type = etde.encumbrance_type;
151 CURSOR c_pous IS
152 SELECT 'X'
153 FROM IGS_PR_OU_UNIT_SET
154 WHERE progression_rule_cat = p_progression_rule_cat AND
155 pra_sequence_number = p_pra_sequence_number AND
156 pro_sequence_number = p_sequence_number;
157 CURSOR c_pou_1 IS
158 SELECT 'X'
159 FROM IGS_PR_OU_UNIT
160 WHERE progression_rule_cat = p_progression_rule_cat AND
161 pra_sequence_number = p_pra_sequence_number AND
162 pro_sequence_number = p_sequence_number AND
163 s_unit_type = cst_excluded;
164 CURSOR c_pou_2 IS
165 SELECT 'X'
166 FROM IGS_PR_OU_UNIT
167 WHERE progression_rule_cat = p_progression_rule_cat AND
168 pra_sequence_number = p_pra_sequence_number AND
169 pro_sequence_number = p_sequence_number AND
170 s_unit_type = cst_required;
171 BEGIN
172 -- Set the default message number
173 p_message_name := null;
174 IF p_progression_rule_cat IS NULL OR
175 p_pra_sequence_number IS NULL OR
176 p_sequence_number IS NULL OR
177 p_progression_outcome_type IS NULL OR
178 p_apply_automatically_ind = 'N' THEN
179 RETURN TRUE;
180 END IF;
181 FOR v_pot_etde_rec IN c_pot_etde LOOP
182 -- If related encumbrance effects contain EXC_COURSE,EXC_CRS_GP or SUS_COURSEE
183 -- then raise error
184 IF v_pot_etde_rec.s_encmb_effect_type IN (
185 cst_exc_course,
186 cst_exc_crs_gp,
187 cst_sus_course) THEN
188 p_message_name := 'IGS_PR_EXC_CRS_GP_SUS';
189 v_exit := TRUE;
190 EXIT;
191 END IF;
192 -- If related encumbrance effects contain EXC_CRS_US, then at least one
193 -- record must exist in the prg_outcome_unit_set table
194 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_us THEN
195 OPEN c_pous;
196 FETCH c_pous INTO v_dummy;
197 IF c_pous%NOTFOUND THEN
198 CLOSE c_pous;
199 p_message_name := 'IGS_PR_APAUO_SOT_EMEF_USER_CR';
200 v_exit := TRUE;
201 EXIT;
202 ELSE
203 CLOSE c_pous;
204 END IF;
205 END IF;
206 -- If related encumbrance effects contain EXC_CRS_U, then record must exist
207 -- in the prg_outcome_unit table with s_unit_type of 'EXCLUDED'
208 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_u THEN
209 OPEN c_pou_1;
210 FETCH c_pou_1 INTO v_dummy;
211 IF c_pou_1%NOTFOUND THEN
212 CLOSE c_pou_1;
213 p_message_name := 'IGS_PR_APAUO_SOT_EMEF_UER_CR';
214 v_exit := TRUE;
215 EXIT;
216 ELSE
217 CLOSE c_pou_1;
218 END IF;
219 END IF;
220 -- If related encumbrance effects contain RQRD_CRS_U, then a record must
221 -- exist in the prg_outcome_unit table with s_unit_type of 'REQUIRED'
222 IF v_pot_etde_rec.s_encmb_effect_type = cst_rqrd_crs_u THEN
223 OPEN c_pou_2;
224 FETCH c_pou_2 INTO v_dummy;
225 IF c_pou_2%NOTFOUND THEN
226 CLOSE c_pou_2;
227 p_message_name := 'IGS_PR_APAUO_SOT_EMEF_UROR_CR';
228 v_exit := TRUE;
229 EXIT;
230 ELSE
231 CLOSE c_pou_2;
232 END IF;
233 END IF;
234 -- If related encumbrance effects contain RSTR_{GE,LE}_CP then
235 -- pro.restricted_enrolment_cp must be set
236 IF v_pot_etde_rec.s_encmb_effect_type IN (
237 cst_rstr_ge_cp,
238 cst_rstr_le_cp) AND
239 NVL(p_restricted_enrolment_cp, 0) = 0 THEN
240 p_message_name := 'IGS_PR_APTUO_SOT_CPR_RNCV_EN';
241 v_exit := TRUE;
242 EXIT;
243 END IF;
244 -- If related encumbrance effects contain RSTR_AT_TY, then
245 -- pro.restricted_attendance_type must be set
246 IF v_pot_etde_rec.s_encmb_effect_type = cst_rstr_at_ty AND
247 p_restricted_attendance_type IS NULL THEN
248 p_message_name := 'IGS_PR_APTUO_SOT_ATYR_RATY_EN';
249 v_exit := TRUE;
250 EXIT;
251 END IF;
252 -- If related encumbrance effects contain EXC_CRS_GP, then
253 -- pro.encmb_course_group_cd must be set
254 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_gp AND
255 p_encmb_course_group_cd IS NULL THEN
256 p_message_name := 'IGS_PR_APTUO_SOT_ENE_CGE_EN';
257 v_exit := TRUE;
258 EXIT;
259 END IF;
260 END LOOP;
261 IF v_exit THEN
262 RETURN FALSE;
263 END IF;
264 RETURN TRUE;
265 EXCEPTION
266 WHEN OTHERS THEN
267 IF c_pot_etde%ISOPEN THEN
268 CLOSE c_pot_etde;
269 END IF;
270 IF c_pous%ISOPEN THEN
271 CLOSE c_pous;
272 END IF;
273 IF c_pou_1%ISOPEN THEN
274 CLOSE c_pou_1;
275 END IF;
276 IF c_pou_2%ISOPEN THEN
277 CLOSE c_pou_2;
278 END IF;
279 RAISE;
280 END;
281 EXCEPTION
282 WHEN OTHERS THEN
283 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END prgp_val_pro_auto;
287 --
288 -- Validate progression rule outcome exclude course group
289 FUNCTION prgp_val_pro_cgr(
290 p_progression_outcome_type IN VARCHAR2 ,
291 p_encmb_course_group_cd IN VARCHAR2 ,
292 p_message_name OUT NOCOPY VARCHAR2 )
293 RETURN BOOLEAN IS
294 gv_other_detail VARCHAR2(255);
295 BEGIN -- prgp_val_pro_cgr
296 -- Validate that if progression_rule_outcome.encmb_course-group_cd is set
297 --that the progression-outcome_type relates to a s_encmb_effect_type of
298 -- EXC_CRS_GP
299 DECLARE
300 cst_exc_crs_gp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
301 v_dummy VARCHAR2(1);
302 CURSOR c_pot IS
303 SELECT 'X'
304 FROM IGS_PR_OU_TYPE pot,
305 IGS_FI_ENC_DFLT_EFT etde
306 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
307 pot.encumbrance_type = etde.encumbrance_type AND
308 etde.s_encmb_effect_type = cst_exc_crs_gp;
309 BEGIN
310 -- Set the default message number
311 p_message_name := null;
312 IF p_progression_outcome_type IS NULL OR
313 p_encmb_course_group_cd IS NULL THEN
314 RETURN TRUE;
315 END IF;
316 OPEN c_pot;
317 FETCH c_pot INTO v_dummy;
318 IF c_pot%NOTFOUND THEN
319 CLOSE c_pot;
320 p_message_name := 'IGS_PR_ENCGP_EXC_CRS_GP';
321 RETURN FALSE;
322 END IF;
323 CLOSE c_pot;
324 RETURN TRUE;
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF c_pot%ISOPEN THEN
328 CLOSE c_pot;
329 END IF;
330 RAISE;
331 END;
332 EXCEPTION
333 WHEN OTHERS THEN
334 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END prgp_val_pro_cgr;
338 --
342 p_restricted_enrolment_cp IN NUMBER ,
339 -- Validate progression outcome type restrict enrolled credit points
340 FUNCTION prgp_val_pro_cp(
341 p_progression_outcome_type IN VARCHAR2 ,
343 p_message_name OUT NOCOPY VARCHAR2 )
344 RETURN BOOLEAN IS
345 gv_other_detail VARCHAR2(255);
346 BEGIN -- prgp_val_pro_cp
347 -- Validate that if progression_rule_outcome.restricted_enrolment_cp is set
348 --that the progression_outcome_type relates to a s_encmb_effect_type of
349 -- RSTR_GE_CP or RSTR_LE_CP.
350 DECLARE
351 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
352 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
353 v_dummy VARCHAR2(1);
354 CURSOR c_pot IS
355 SELECT 'X'
356 FROM IGS_PR_OU_TYPE pot,
357 IGS_FI_ENC_DFLT_EFT etde
358 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
359 pot.encumbrance_type = etde.encumbrance_type AND
360 etde.s_encmb_effect_type IN ( cst_rstr_ge_cp,
361 cst_rstr_le_cp);
362 BEGIN
363 -- Set the default message number
364 p_message_name := null;
365 IF p_progression_outcome_type IS NULL OR
366 p_restricted_enrolment_cp IS NULL THEN
367 RETURN TRUE;
368 END IF;
369 OPEN c_pot;
370 FETCH c_pot INTO v_dummy;
371 IF c_pot%NOTFOUND THEN
372 CLOSE c_pot;
373 p_message_name := 'IGS_PR_RSTR_GE_LE_CP';
374 RETURN FALSE;
375 END IF;
376 CLOSE c_pot;
377 RETURN TRUE;
378 EXCEPTION
379 WHEN OTHERS THEN
380 IF c_pot%ISOPEN THEN
381 CLOSE c_pot;
382 END IF;
383 RAISE;
384 END;
385 EXCEPTION
386 WHEN OTHERS THEN
387 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
388 IGS_GE_MSG_STACK.ADD;
389 App_Exception.Raise_Exception;
390 END prgp_val_pro_cp;
391 --
392 -- Validate progression rule outcome progression outcome type
393 FUNCTION prgp_val_pro_pot(
394 p_progression_rule_cat IN VARCHAR2 ,
395 p_pra_sequence_number IN NUMBER ,
396 p_sequence_number IN NUMBER ,
397 p_progression_outcome_type IN VARCHAR2 ,
398 p_message_name OUT NOCOPY VARCHAR2 )
399 RETURN BOOLEAN IS
400 ----------------------------------------------------------------------------
401 --Change History:
402 --Who When What
403 --kdande 17-Jul-2002 Changed message name to IGS_PR_PROT_TY_INCO_EPOCR
404 -- from 5149 for Bug# 2462120
405 --Nalin Kumar 12-NOV-2002 Modified this function as per the FA110 PR-ENH. Bug# 2658550
406 ----------------------------------------------------------------------------
407 gv_other_detail VARCHAR2(255);
408 BEGIN -- Validate changes to the progression_rule_outcome.progression_outcome
409 --_type.
410 -- * If prg_outcome_course records exist the progression_outcome_type must
411 -- relate to a s_encmb_effect_type of EXC_COURSE or SUS_COURSE.
412 -- * If prg_outcome_unit_set records exist the progression_outcome_type
413 -- must relate to a s_encmb_effect_type of EXC_CRS_US.
414 -- * If prg_outcome_unit records exist the progression_outcome_type
415 -- must relate to a s_encmb_effect_type of EXC_CRS_U when s_unit_type = EXCLUDE
416 -- or a s_encmb_effect_type of RQRD_CRS_U when s_unit_type = REQUIRED.
417 -- * If prg_outcome_fund records exist the progression_outcome_type must
418 -- relate to a s_encmb_effect_type of EX_FUND.
419 DECLARE
420 cst_exc_course CONSTANT VARCHAR2(10) := 'EXC_COURSE';
421 cst_sus_course CONSTANT VARCHAR2(10) := 'SUS_COURSE';
422 cst_exc_crs_us CONSTANT VARCHAR2(10) := 'EXC_CRS_US';
423 cst_exc_crs_u CONSTANT VARCHAR2(10) := 'EXC_CRS_U';
424 cst_rqrd_crs_u CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
425 cst_excluded CONSTANT VARCHAR2(10) := 'EXCLUDED';
426 cst_required CONSTANT VARCHAR2(10) := 'REQUIRED';
427 cst_exe_fund CONSTANT VARCHAR2(10) := 'EX_FUND';
428 v_dummy VARCHAR2(1);
429 v_record_not_found BOOLEAN DEFAULT FALSE;
430 CURSOR c_poc IS
431 SELECT 'X'
432 FROM IGS_PR_OU_PS poc
433 WHERE poc.progression_rule_cat = p_progression_rule_cat AND
434 poc.pra_sequence_number = p_pra_sequence_number AND
435 poc.pro_sequence_number = p_sequence_number;
436 CURSOR c_pot_etde1 IS
437 SELECT 'X'
438 FROM IGS_PR_OU_TYPE pot,
439 IGS_FI_ENC_DFLT_EFT etde
440 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
441 pot.encumbrance_type = etde.encumbrance_type AND
442 etde.s_encmb_effect_type IN (
443 cst_exc_course,
444 cst_sus_course);
445 CURSOR c_pous IS
446 SELECT 'X'
447 FROM IGS_PR_OU_UNIT_SET pous
448 WHERE pous.progression_rule_cat = p_progression_rule_cat AND
449 pous.pra_sequence_number = p_pra_sequence_number AND
450 pous.pro_sequence_number = p_sequence_number;
451 CURSOR c_pot_etde2 IS
452 SELECT 'X'
453 FROM IGS_PR_OU_TYPE pot,
457 etde.s_encmb_effect_type = cst_exc_crs_us;
454 IGS_FI_ENC_DFLT_EFT etde
455 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
456 pot.encumbrance_type = etde.encumbrance_type AND
458 CURSOR c_popu IS
459 SELECT DISTINCT s_unit_type
460 FROM IGS_PR_OU_UNIT popu
461 WHERE popu.progression_rule_cat = p_progression_rule_cat AND
462 popu.pra_sequence_number = p_pra_sequence_number AND
463 popu.pro_sequence_number = p_sequence_number;
464 CURSOR c_pot_etde3 IS
465 SELECT 'X'
466 FROM IGS_PR_OU_TYPE pot,
467 IGS_FI_ENC_DFLT_EFT etde
468 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
469 pot.encumbrance_type = etde.encumbrance_type AND
470 etde.s_encmb_effect_type = cst_exc_crs_u;
471 CURSOR c_pot_etde4 IS
472 SELECT 'X'
473 FROM IGS_PR_OU_TYPE pot,
474 IGS_FI_ENC_DFLT_EFT etde
475 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
476 pot.encumbrance_type = etde.encumbrance_type AND
477 etde.s_encmb_effect_type = cst_rqrd_crs_u;
478 -- Added as per the FA110 PR ENH. Bug# 2658550
479 CURSOR c_pof IS
480 SELECT 'X'
481 FROM IGS_PR_OU_FND pof
482 WHERE pof.progression_rule_cat = p_progression_rule_cat AND
483 pof.pra_sequence_number = p_pra_sequence_number AND
484 pof.pro_sequence_number = p_sequence_number;
485
486 CURSOR c_pot_etde5 IS
487 SELECT 'X'
488 FROM IGS_PR_OU_TYPE pot,
489 IGS_FI_ENC_DFLT_EFT etde
490 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
491 pot.encumbrance_type = etde.encumbrance_type AND
492 etde.s_encmb_effect_type = cst_exe_fund;
493 BEGIN
494 -- Set the default message number
495 p_message_name := null;
496 IF p_progression_rule_cat IS NULL OR
497 p_pra_sequence_number IS NULL OR
498 p_progression_outcome_type IS NULL THEN
499 p_message_name := null;
500 RETURN TRUE;
501 END IF;
502
503 OPEN c_poc;
504 FETCH c_poc INTO v_dummy;
505 IF c_poc%FOUND THEN
506 CLOSE c_poc;
507 OPEN c_pot_etde1;
508 FETCH c_pot_etde1 INTO v_dummy;
509 IF c_pot_etde1%NOTFOUND THEN
510 CLOSE c_pot_etde1;
511 -- Start of fix for Bug# 2462120
512 p_message_name := 'IGS_PR_PROT_TY_INCO_EPOCR';
513 -- End of fix for Bug# 2462120
514 RETURN FALSE;
515 END IF;
516 CLOSE c_pot_etde1;
517 ELSE
518 CLOSE c_poc;
519 END IF;
520
521 OPEN c_pous;
522 FETCH c_pous INTO v_dummy;
523 IF c_pous%FOUND THEN
524 CLOSE c_pous;
525 OPEN c_pot_etde2;
526 FETCH c_pot_etde2 INTO v_dummy;
527 IF c_pot_etde2%NOTFOUND THEN
528 CLOSE c_pot_etde2;
529 p_message_name := 'IGS_PR_PROUT_TYICM_EPOU_STRE';
530 RETURN FALSE;
531 END IF;
532 CLOSE c_pot_etde2;
533 ELSE
534 CLOSE c_pous;
535 END IF;
536 FOR v_popu_rec IN c_popu LOOP
537 IF v_popu_rec.s_unit_type = cst_excluded THEN
538 OPEN c_pot_etde3;
539 FETCH c_pot_etde3 INTO v_dummy;
540 IF c_pot_etde3%NOTFOUND THEN
541 CLOSE c_pot_etde3;
542 p_message_name := 'IGS_PR_PROUT_TYICM_EPOU_RE';
543 v_record_not_found := TRUE;
544 EXIT;
545 END IF;
546 CLOSE c_pot_etde3;
547 ELSE
548 OPEN c_pot_etde4;
549 FETCH c_pot_etde4 INTO v_dummy;
550 IF c_pot_etde4%NOTFOUND THEN
551 CLOSE c_pot_etde4;
552 p_message_name := 'IGS_PR_PROUT_TYICM_EPOU_RE';
553 v_record_not_found := TRUE;
554 EXIT;
555 END IF;
556 CLOSE c_pot_etde4;
557 END IF;
558 END LOOP;
559 --
560 --Start of code added as per the FA110 PR-ENH. Bug# 2658550
561 --
562 OPEN c_pof;
563 FETCH c_pof INTO v_dummy;
564 IF c_pof%FOUND THEN
565 CLOSE c_pof;
566 OPEN c_pot_etde5;
567 FETCH c_pot_etde5 INTO v_dummy;
568 IF c_pot_etde5%NOTFOUND THEN
569 CLOSE c_pot_etde5;
570 p_message_name := 'IGS_PR_PROT_TY_INCO_FUND';
571 RETURN FALSE;
572 END IF;
573 CLOSE c_pot_etde5;
574 ELSE
575 CLOSE c_pof;
576 END IF;
577 --
578 --End of code added as per the FA110 PR-ENH. Bug# 2658550
579 --
580
581 IF v_record_not_found THEN
582 RETURN FALSE;
583 END IF;
584 RETURN TRUE;
585 EXCEPTION
586 WHEN OTHERS THEN
587 IF c_poc%ISOPEN THEN
588 CLOSE c_poc;
589 END IF;
590 IF c_pot_etde1%ISOPEN THEN
591 CLOSE c_pot_etde1;
592 END IF;
593 IF c_pous%ISOPEN THEN
594 CLOSE c_pous;
595 END IF;
596 IF c_pot_etde2%ISOPEN THEN
597 CLOSE c_pot_etde2;
598 END IF;
599 IF c_popu%ISOPEN THEN
600 CLOSE c_popu;
601 END IF;
605 IF c_pot_etde4%ISOPEN THEN
602 IF c_pot_etde3%ISOPEN THEN
603 CLOSE c_pot_etde3;
604 END IF;
606 CLOSE c_pot_etde4;
607 END IF;
608 RAISE;
609 END;
610 EXCEPTION
611 WHEN OTHERS THEN
612 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
613 IGS_GE_MSG_STACK.ADD;
614 App_Exception.Raise_Exception;
615 END prgp_val_pro_pot;
616 --
617 -- Validate progression rule outcome has required details
618 FUNCTION prgp_val_pro_rqrd(
619 p_progression_outcome_type IN VARCHAR2 ,
620 p_duration IN NUMBER ,
621 p_duration_type IN VARCHAR2 ,
622 p_message_name OUT NOCOPY VARCHAR2 )
623 RETURN BOOLEAN IS
624 gv_other_detail VARCHAR2(255);
625 BEGIN -- prgp_val_pro_rqrd
626 -- Validate that if progression_rule_outcome has the required details:
627 --1. If duration_type is specified as (NORMAL or EFFECTIVE) then a duration
628 -- must be specified and vice versa
629 --2. If related s_progression_type is SUSPENSION, then duration and duration_
630 --type must be specified
631 --3. If related s_progression_outcome_type is EXCLUSION, EXPULSION, NOPENALTY,
632 -- MANUAL or EX_FUND, then duration and duration_type cannot be specified
633 --4. If related s_progress_outcome_type not PROBATION then duration_type
634 --cannot be effective
635 DECLARE
636 v_s_progression_outcome_type IGS_PR_OU_TYPE.s_progression_outcome_type%TYPE;
637 cst_suspension CONSTANT VARCHAR2(10) := 'SUSPENSION';
638 cst_exclusion CONSTANT VARCHAR2(10) := 'EXCLUSION';
639 cst_expulsion CONSTANT VARCHAR2(10) := 'EXPULSION';
640 cst_nopenalty CONSTANT VARCHAR2(10) := 'NOPENALTY';
641 cst_manual CONSTANT VARCHAR2(10) := 'MANUAL';
642 cst_probation CONSTANT VARCHAR2(10) := 'PROBATION';
643 cst_effective CONSTANT VARCHAR2(10) := 'EFFECTIVE';
644 cst_ex_fund CONSTANT VARCHAR2(10) := 'EX_FUND';
645 CURSOR c_pot IS
646 SELECT pot.s_progression_outcome_type
647 FROM IGS_PR_OU_TYPE pot
648 WHERE pot.progression_outcome_type = p_progression_outcome_type;
649 BEGIN
650 -- Set the default message number
651 p_message_name := null;
652 IF (p_duration_type IS NULL AND
653 p_duration IS NOT NULL) THEN
654 p_message_name := 'IGS_PR_DU_SET_DTYP_MSET';
655 RETURN FALSE;
656 END IF;
657 IF (p_duration_type IS NOT NULL AND
658 p_duration IS NULL) THEN
659 p_message_name := 'IGS_PR_DUTY_SET_DU_MSET';
660 RETURN FALSE;
661 END IF;
662 IF p_progression_outcome_type IS NULL THEN
663 RETURN TRUE;
664 END IF;
665 OPEN c_pot;
666 FETCH c_pot INTO v_s_progression_outcome_type;
667 IF c_pot%NOTFOUND THEN
668 CLOSE c_pot;
669 RETURN TRUE;
670 END IF;
671 CLOSE c_pot;
672 IF v_s_progression_outcome_type = cst_suspension AND
673 p_duration IS NULL THEN
674 p_message_name := 'IGS_PR_DU_DUTY_SUS';
675 RETURN FALSE;
676 END IF;
677 IF (v_s_progression_outcome_type = cst_exclusion OR
678 v_s_progression_outcome_type = cst_expulsion OR
679 v_s_progression_outcome_type = cst_ex_fund OR
680 v_s_progression_outcome_type = cst_nopenalty) AND
681 p_duration IS NOT NULL THEN
682 p_message_name := 'IGS_PR_DUTY_PRTY_EXC_NOP';
683 RETURN FALSE;
684 END IF;
685 IF v_s_progression_outcome_type NOT IN (cst_probation,cst_manual) AND
686 p_duration_type = cst_effective THEN
687 p_message_name := 'IGS_PR_DTYP_CNTEF_PRO_MAN';
688 RETURN FALSE;
689 END IF;
690 RETURN TRUE;
691 EXCEPTION
692 WHEN OTHERS THEN
693 IF c_pot%ISOPEN THEN
694 CLOSE c_pot;
695 END IF;
696 RAISE;
697 END;
698 EXCEPTION
699 WHEN OTHERS THEN
700 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
701 IGS_GE_MSG_STACK.ADD;
702 App_Exception.Raise_Exception;
703 END prgp_val_pro_rqrd;
704 END IGS_PR_VAL_PRO;
[Home] [Help]