1 Package Body IGS_GE_VAL_PARAM
2 /* $Header: IGSGE08B.pls 120.0 2005/06/01 15:52:59 appldev noship $ */
3
4 AS
5 -- Global Variables
6 gv_other_detail VARCHAR2(256) := NULL;
7 -- Module: repp_val_unit_cd
8 -- Purpose: validation for unit code
9 --
10 -- MODIFICATION HISTORY
11 -- IGS_PE_PERSON Date Comments
12 -- --------- -------- ------------------------------------------
13 -- MSONTER 21/08/98 Initial creation of function
14 -- smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_uc and c_mode to select active (not closed) unit classes.
15 FUNCTION repp_val_unit_cd( p_unit_cd IN OUT NOCOPY IGS_PS_UNIT_VER_all.unit_cd%TYPE,
16 p_msg_name OUT NOCOPY VARCHAR2)
17 RETURN BOOLEAN
18 AS
19 --
20 -- Local Cursors
21 CURSOR c_unit(cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE)
22 IS
23 SELECT
24 'x'
25 FROM IGS_PS_UNIT_VER UV,
26 IGS_PS_UNIT_STAT UNS
27 WHERE
28 uv.unit_cd LIKE cp_unit_cd
29 AND uns.unit_status = uv.unit_status
30 AND uns.s_unit_status = 'ACTIVE';
31 --
32 -- Local Variables
33 v_unit_ok VARCHAR2(1) := NULL;
34 BEGIN
35 --
36 -- check for uppper case requirement
37 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('UNIT_CD','IGS_PS_UNIT_VER')
38 THEN
39 p_unit_cd := UPPER(p_unit_cd);
40 END IF;
41 --
42 -- validate IGS_PS_UNIT entered
43 IF c_unit%ISOPEN
44 THEN
45 CLOSE c_unit;
46 END IF;
47 OPEN c_unit(p_unit_cd);
48 FETCH c_unit INTO v_unit_ok;
49 IF c_unit%NOTFOUND
50 THEN
51 CLOSE c_unit;
52 p_msg_name := 'IGS_GE_VAL_DOES_NOT_XS';
53 RETURN FALSE;
54 END IF;
55 RETURN TRUE;
56 EXCEPTION
57 WHEN OTHERS THEN
58 IF c_unit%ISOPEN
59 THEN
60 CLOSE c_unit;
61 END IF;
62 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
63 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
64 RETURN FALSE;
65 END; -- repp_val_unit_cd
66 -- Module: repp_val_location
67 -- Purpose: validation for unit code
68 --
69 -- MODIFICATION HISTORY
70 -- Person Date Comments
71 -- --------- -------- ------------------------------------------
72 -- MSONTER 21/08/98 Initial creation of function
73 FUNCTION repp_val_location( p_location_cd IN OUT NOCOPY IGS_AD_LOCATION_ALL.location_cd%TYPE,
74 p_msg_name OUT NOCOPY VARCHAR2)
75 RETURN BOOLEAN
76 AS
77 --
78 -- Local Cursors
79 CURSOR c_loc(cp_loc_cd IGS_AD_LOCATION.location_cd%TYPE)
80 IS
81 SELECT
82 'x'
83 FROM IGS_AD_LOCATION LOC
84 WHERE
85 loc.location_cd LIKE cp_loc_cd
86 AND loc.closed_ind = 'N';
87 --
88 -- Local Variables
89 v_loc_ok VARCHAR2(1) := NULL;
90 BEGIN
91 --
92 -- check for uppper case requirement
93 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('LOCATION_CD','LOCATION')
94 THEN
95 p_location_cd := UPPER(p_location_cd);
96 END IF;
97 --
98 -- validate unit entered
99 IF c_loc%ISOPEN
100 THEN
101 CLOSE c_loc;
102 END IF;
103 OPEN c_loc(p_location_cd);
104 FETCH c_loc INTO v_loc_ok;
105 IF c_loc%NOTFOUND
106 THEN
107 CLOSE c_loc;
108 p_msg_name := 'IGS_GE_INVALID_VALUE';
109 RETURN FALSE;
110 END IF;
111 RETURN TRUE;
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF c_loc%ISOPEN
115 THEN
116 CLOSE c_loc;
117 END IF;
118 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
119 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
120 RETURN FALSE;
121 END; -- repp_val_location_cd
122 -- Module: repp_val_att_mode
123 -- Purpose: validation for attendance modes
124 --
125 -- MODIFICATION HISTORY
126 -- Person Date Comments
127 -- --------- -------- ------------------------------------------
128 -- MSONTER 21/08/98 Initial creation of function
129 FUNCTION repp_val_att_mode( p_att_mode IN OUT NOCOPY IGS_EN_ATD_MODE_ALL.ATTENDANCE_MODE%TYPE,
130 p_msg_name OUT NOCOPY VARCHAR2)
131 RETURN BOOLEAN
132 AS
133 --
134 -- Local Cursors
135 CURSOR c_att(cp_att_mode IGS_EN_ATD_MODE.ATTENDANCE_MODE%TYPE)
136 IS
137 SELECT
138 'x'
139 FROM IGS_EN_ATD_MODE atm
140 WHERE
141 atm.attendance_mode LIKE cp_att_mode
142 AND atm.closed_ind = 'N';
143 --
144 -- Local Variables
145 v_ok VARCHAR2(1) := NULL;
146 BEGIN
147 --
148 -- check for uppper case requirement
149 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_EN_ATD_MODE','IGS_EN_ATD_MODE')
150 THEN
151 p_att_mode := UPPER(p_att_mode);
152 END IF;
153 --
154 -- validate unit entered
155 IF c_att%ISOPEN
156 THEN
157 CLOSE c_att;
158 END IF;
159 OPEN c_att(p_att_mode);
160 FETCH c_att INTO v_ok;
161 IF c_att%NOTFOUND
162 THEN
163 CLOSE c_att;
164 p_msg_name := 'IGS_GE_INVALID_VALUE';
165 RETURN FALSE;
166 END IF;
167 RETURN TRUE;
168 EXCEPTION
169 WHEN OTHERS THEN
170 IF c_att%ISOPEN
171 THEN
172 CLOSE c_att;
173 END IF;
174 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
175 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
176 RETURN FALSE;
177 END; -- repp_val_att_mode
178 -- Module: repp_val_att_type
179 -- Purpose: validation for attendance types
180 --
181 -- MODIFICATION HISTORY
182 -- PERSON Date Comments
183 -- --------- -------- ------------------------------------------
184 -- MSONTER 21/08/98 Initial creation of function
185 FUNCTION repp_val_att_type( p_att_type IN OUT NOCOPY IGS_EN_ATD_TYPE_ALL.attendance_type%TYPE,
186 p_msg_name OUT NOCOPY VARCHAR2)
187 RETURN BOOLEAN
188 AS
189 --
190 -- Local Cursors
191 CURSOR c_att(cp_att_type IGS_EN_ATD_TYPE.ATTENDANCE_TYPE%TYPE)
192 IS
193 SELECT
194 'x'
195 FROM IGS_EN_ATD_TYPE att
196 WHERE
197 att.attendance_type LIKE cp_att_type
198 AND att.closed_ind = 'N';
199 --
200 -- Local Variables
201 v_att_ok VARCHAR2(1) := NULL;
202 BEGIN
203 --
204 -- check for uppper case requirement
205 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('ATTEDANCE_TYPE','IGS_EN_ATD_TYPE')
206 THEN
207 p_att_type := UPPER(p_att_type);
208 END IF;
209 --
210 -- validate unit entered
211 IF c_att%ISOPEN
212 THEN
213 CLOSE c_att;
214 END IF;
215 OPEN c_att(p_att_type);
216 FETCH c_att INTO v_att_ok;
217 IF c_att%NOTFOUND
218 THEN
219 CLOSE c_att;
220 p_msg_name := 'IGS_GE_INVALID_VALUE';
221 RETURN FALSE;
222 END IF;
223 RETURN TRUE;
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF c_att%ISOPEN
227 THEN
228 CLOSE c_att;
229 END IF;
230 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
231 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
232 RETURN FALSE;
233 END; -- repp_val_att_type
234 -- Module: repp_val_unit_class
235 -- Purpose: validation for unit class
236 --
237 -- MODIFICATION HISTORY
238 -- Person Date Comments
239 -- --------- -------- ------------------------------------------
240 -- MSONTER 21/08/98 Initial creation of function
241 FUNCTION repp_val_unit_class( p_unit_class IN OUT NOCOPY IGS_AS_UNIT_CLASS_ALL.unit_class%TYPE,
242 p_msg_name OUT NOCOPY VARCHAR2)
243 RETURN BOOLEAN
244 AS
245 --
246 -- Local Cursors
247 CURSOR c_uc(cp_unit_class IGS_AS_UNIT_CLASS.UNIT_CLASS%TYPE)
248 IS
249 SELECT
250 'x'
251 FROM IGS_AS_UNIT_CLASS UC
252 WHERE
253 uc.unit_class LIKE cp_unit_class
254 AND uc.closed_ind = 'N';
255 --
256 -- Local Variables
257 v_ok VARCHAR2(1) := NULL;
258 BEGIN
259 --
260 -- check for uppper case requirement
261 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_AS_UNIT_CLASS','IGS_AS_UNIT_CLASS')
262 THEN
263 p_unit_class := UPPER(p_unit_class);
264 END IF;
265 --
266 -- validate unit entered
267 IF c_uc%ISOPEN
268 THEN
269 CLOSE c_uc;
270 END IF;
271 OPEN c_uc(p_unit_class);
272 FETCH c_uc INTO v_ok;
273 IF c_uc%NOTFOUND
274 THEN
275 CLOSE c_uc;
276 p_msg_name := 'IGS_GE_INVALID_VALUE';
277 RETURN FALSE;
278 END IF;
279 RETURN TRUE;
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF c_uc%ISOPEN
283 THEN
284 CLOSE c_uc;
285 END IF;
286 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
287 IGS_GE_MSG_STACK.ADD;
288 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
289 RETURN FALSE;
290 END; -- repp_val_unit_class
291 -- Module: repp_val_unit_mode
292 -- Purpose: validation for unit mode
293 --
294 -- MODIFICATION HISTORY
295 -- Person Date Comments
296 -- --------- -------- ------------------------------------------
297 -- MSONTER 21/08/98 Initial creation of function
298 FUNCTION repp_val_unit_mode( p_unit_mode IN OUT NOCOPY IGS_AS_UNIT_MODE.unit_mode%TYPE,
299 p_msg_name OUT NOCOPY VARCHAR2)
300 RETURN BOOLEAN
301 AS
302 --
303 -- Local Cursors
304 CURSOR c_um(cp_unit_mode IGS_AS_UNIT_MODE.UNIT_MODE%TYPE)
305 IS
306 SELECT
307 'x'
308 FROM IGS_AS_UNIT_MODE um
309 WHERE
310 um.unit_mode LIKE cp_unit_mode
311 AND um.closed_ind = 'N';
312 --
313 -- Local Variables
314 v_um_ok VARCHAR2(1) := NULL;
315 BEGIN
316 --
317 -- check for uppper case requirement
318 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_AS_UNIT_MODE','IGS_AS_UNIT_MODE')
319 THEN
320 p_unit_mode := UPPER(p_unit_mode);
321 END IF;
322 --
323 -- validate unit entered
324 IF c_um%ISOPEN
325 THEN
326 CLOSE c_um;
327 END IF;
328 OPEN c_um(p_unit_mode);
329 FETCH c_um INTO v_um_ok;
330 IF c_um%NOTFOUND
331 THEN
332 CLOSE c_um;
333 p_msg_name := 'IGS_GE_INVALID_VALUE';
334 RETURN FALSE;
335 END IF;
336 RETURN TRUE;
337 EXCEPTION
338 WHEN OTHERS THEN
339 IF c_um%ISOPEN
340 THEN
341 CLOSE c_um;
342 END IF;
343 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
344 IGS_GE_MSG_STACK.ADD;
345 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
346 RETURN FALSE;
347 END; -- repp_val_unit_mode
348 -- Module: repp_val_crs_type
349 -- Purpose: validation for IGS_PS_TYPEs
350 --
351 -- MODIFICATION HISTORY
352 -- Person Date Comments
353 -- --------- -------- ------------------------------------------
354 -- MSONTER 21/08/98 Initial creation of function
355 FUNCTION repp_val_crs_type( p_crs_type IN OUT NOCOPY IGS_PS_TYPE_ALL.COURSE_TYPE%TYPE,
356 p_msg_name OUT NOCOPY VARCHAR2)
357 RETURN BOOLEAN
358 AS
359 --
360 -- Local Cursors
361 CURSOR c_crs(cp_crs_type IGS_PS_TYPE.course_type%TYPE)
362 IS
363 SELECT
364 'x'
365 FROM IGS_PS_TYPE CT
366 WHERE
367 ct.course_type LIKE cp_crs_type
368 AND ct.closed_ind = 'N';
369 --
370 -- Local Variables
371 v_ok VARCHAR2(1) := NULL;
372 BEGIN
373 --
374 -- check for uppper case requirement
375 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_PS_TYPE','IGS_PS_TYPE')
376 THEN
377 p_crs_type := UPPER(p_crs_type);
378 END IF;
379 --
380 -- validate unit entered
381 IF c_crs%ISOPEN
382 THEN
383 CLOSE c_crs;
384 END IF;
385 OPEN c_crs(p_crs_type);
386 FETCH c_crs INTO v_ok;
387 IF c_crs%NOTFOUND
388 THEN
389 CLOSE c_crs;
390 p_msg_name := 'IGS_GE_INVALID_VALUE';
391 RETURN FALSE;
392 END IF;
393 RETURN TRUE;
394 EXCEPTION
395 WHEN OTHERS THEN
396 IF c_crs%ISOPEN
397 THEN
398 CLOSE c_crs;
399 END IF;
400 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
401 IGS_GE_MSG_STACK.ADD;
402 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
403 RETURN FALSE;
404 END; -- repp_val_crs_type
405 -- Module: repp_val_enr_cat
406 -- Purpose: validation for enrolment categories
407 --
408 -- MODIFICATION HISTORY
409 -- Person Date Comments
410 -- --------- -------- ------------------------------------------
411 -- MSONTER 21/08/98 Initial creation of function
412 FUNCTION repp_val_enr_cat( p_enr_cat IN OUT NOCOPY IGS_EN_ENROLMENT_CAT.ENROLMENT_CAT%TYPE,
413 p_msg_name OUT NOCOPY VARCHAR2)
414 RETURN BOOLEAN
415 AS
416 --
417 -- Local Cursors
418 CURSOR c_crs(cp_enr_cat IGS_EN_ENROLMENT_CAT.enrolment_cat%TYPE)
419 IS
420 SELECT
421 'x'
422 FROM IGS_EN_ENROLMENT_CAT EC
423 WHERE
424 ec.enrolment_cat LIKE cp_enr_cat
425 AND ec.closed_ind = 'N';
426 --
427 -- Local Variables
428 v_ok VARCHAR2(1) := NULL;
429 BEGIN
430 --
431 -- check for uppper case requirement
432 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_EN_ENROLMENT_CAT','IGS_EN_ENROLMENT_CAT')
433 THEN
434 p_enr_cat := UPPER(p_enr_cat);
435 END IF;
436 --
437 -- validate unit entered
438 IF c_crs%ISOPEN
439 THEN
440 CLOSE c_crs;
441 END IF;
442 OPEN c_crs(p_enr_cat);
443 FETCH c_crs INTO v_ok;
444 IF c_crs%NOTFOUND
445 THEN
446 CLOSE c_crs;
447 p_msg_name := 'IGS_GE_INVALID_VALUE';
448 RETURN FALSE;
449 END IF;
450 RETURN TRUE;
451 EXCEPTION
452 WHEN OTHERS THEN
453 IF c_crs%ISOPEN
454 THEN
455 CLOSE c_crs;
456 END IF;
457 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
458 IGS_GE_MSG_STACK.ADD;
459 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
460 RETURN FALSE;
461 END; -- repp_val_enr_cat
462 -- Module: repp_val_adm_cat
463 -- Purpose: validation for admission categories
464 --
465 -- MODIFICATION HISTORY
466 -- Person Date Comments
467 -- --------- -------- ------------------------------------------
468 -- MSONTER 21/08/98 Initial creation of function
469 FUNCTION repp_val_adm_cat( p_adm_cat IN OUT NOCOPY IGS_AD_CAT.ADMISSION_CAT%TYPE,
470 p_msg_name OUT NOCOPY VARCHAR2)
471 RETURN BOOLEAN
472 AS
473 --
474 -- Local Cursors
475 CURSOR c_crs(cp_adm_cat IGS_AD_CAT.admission_cat%TYPE)
476 IS
477 SELECT
478 'x'
479 FROM IGS_AD_CAT ADC
480 WHERE
481 adc.admission_cat LIKE cp_adm_cat
482 AND adc.closed_ind = 'N';
483 --
484 -- Local Variables
485 v_ok VARCHAR2(1) := NULL;
486 BEGIN
487 --
488 -- check for uppper case requirement
489 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_AD_CAT','IGS_AD_CAT')
490 THEN
491 p_adm_cat := UPPER(p_adm_cat);
492 END IF;
493 --
494 -- validate unit entered
495 IF c_crs%ISOPEN
496 THEN
497 CLOSE c_crs;
498 END IF;
499 OPEN c_crs(p_adm_cat);
500 FETCH c_crs INTO v_ok;
501 IF c_crs%NOTFOUND
502 THEN
503 CLOSE c_crs;
504 p_msg_name := 'IGS_GE_INVALID_VALUE';
505 RETURN FALSE;
506 END IF;
507 RETURN TRUE;
508 EXCEPTION
509 WHEN OTHERS THEN
510 IF c_crs%ISOPEN
511 THEN
512 CLOSE c_crs;
513 END IF;
514 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
515 IGS_GE_MSG_STACK.ADD;
516 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
517 RETURN FALSE;
518 END; -- repp_val_adm_cat
519 -- Module: repp_val_fee_cat
520 -- Purpose: validation for fee categories
521 --
522 -- MODIFICATION HISTORY
523 -- Person Date Comments
524 -- --------- -------- ------------------------------------------
525 -- MSONTER 21/08/98 Initial creation of function
526 FUNCTION repp_val_fee_cat( p_fee_cat IN OUT NOCOPY IGS_FI_FEE_CAT_ALL.FEE_CAT%TYPE,
527 p_msg_name OUT NOCOPY VARCHAR2)
528 RETURN BOOLEAN
529 AS
530 --
531 -- Local Cursors
532 CURSOR c_crs(cp_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE)
533 IS
534 SELECT
535 'x'
536 FROM IGS_FI_FEE_CAT fc
537 WHERE
538 fc.fee_cat LIKE cp_fee_cat
539 AND fc.closed_ind = 'N';
540 --
541 -- Local Variables
542 v_ok VARCHAR2(1) := NULL;
543 BEGIN
544 --
545 -- check for uppper case requirement
546 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('IGS_FI_FEE_CAT','IGS_FI_FEE_CAT')
547 THEN
548 p_fee_cat := UPPER(p_fee_cat);
549 END IF;
550 --
551 -- validate unit entered
552 IF c_crs%ISOPEN
553 THEN
554 CLOSE c_crs;
555 END IF;
556 OPEN c_crs(p_fee_cat);
557 FETCH c_crs INTO v_ok;
558 IF c_crs%NOTFOUND
559 THEN
560 CLOSE c_crs;
561 p_msg_name := 'IGS_GE_INVALID_VALUE';
562 RETURN FALSE;
563 END IF;
564 RETURN TRUE;
565 EXCEPTION
566 WHEN OTHERS THEN
567 IF c_crs%ISOPEN
568 THEN
569 CLOSE c_crs;
570 END IF;
571 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
572 IGS_GE_MSG_STACK.ADD;
573 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
574 RETURN FALSE;
575 END; -- repp_val_adm_cat
576 -- Module: repp_val_unit_cal
577 -- Purpose: validation for unit code against a specific calendar
578 --
579 -- MODIFICATION HISTORY
580 -- Person Date Comments
581 -- --------- -------- ------------------------------------------
582 -- MSONTER 28/08/98 Initial creation of function
583 FUNCTION repp_val_unit_cal( p_unit_cd IN IGS_PS_UNIT_VER_ALL.unit_cd%TYPE,
584 p_ass_cal_type IN IGS_CA_INST_ALL.CAL_TYPE%TYPE,
585 p_ass_seq_num IN IGS_CA_INST_ALL.sequence_number%TYPE,
586 p_teach_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE,
587 p_teach_seq_num IN IGS_CA_INST_ALL.sequence_number%TYPE,
588 p_msg_name OUT NOCOPY VARCHAR2)
589 RETURN BOOLEAN
590 AS
591 --
592 -- Local Cursors
593 --
594 -- Local Variables
595 BEGIN
596 /*
597 ** complete this later
598 */
599 RETURN TRUE;
600 EXCEPTION
601 WHEN OTHERS THEN
602 RETURN TRUE;
603 END; -- repp_unit_cal_val
604 -- Module: repp_val_unit_mode_class
605 -- Purpose: validation for unit mode and class parameters
606 --
607 -- MODIFICATION HISTORY
608 -- Person Date Comments
609 -- --------- -------- ------------------------------------------
610 -- MSONTER 21/08/98 Initial creation of function
611 FUNCTION repp_val_unit_mode_class
612 ( p_mode IN IGS_AS_UNIT_CLASS_ALL.unit_mode%TYPE,
613 p_class IN IGS_AS_UNIT_CLASS_ALL.unit_class%TYPE,
614 p_msg_name OUT NOCOPY VARCHAR2)
615 RETURN BOOLEAN
616 AS
617 --
618 -- Local Cursors
619 CURSOR c_mode(cp_unit_class IGS_AS_UNIT_CLASS.unit_class%TYPE,
620 cp_unit_mode IGS_AS_UNIT_MODE.unit_mode%TYPE)
621 IS
622 SELECT
623 'x'
624 FROM IGS_AS_UNIT_CLASS uc
625 WHERE uc.unit_class LIKE cp_unit_class
626 AND uc.unit_mode LIKE cp_unit_mode
627 AND uc.closed_ind = 'N';
628 --
629 -- Local Variables
630 v_found VARCHAR2(1) := NULL;
631 BEGIN
632 IF c_mode%ISOPEN
633 THEN
634 CLOSE c_mode;
635 END IF;
636 OPEN c_mode(p_class, p_mode);
637 FETCH c_mode INTO v_found;
638 IF c_mode%NOTFOUND
639 THEN
640 CLOSE c_mode;
641 p_msg_name := 'IGS_AD_UC_UM_INCOMPLATIBLE';
642 RETURN FALSE;
643 END IF;
644 CLOSE c_mode;
645 p_msg_name := NULL;
646 RETURN TRUE;
647 EXCEPTION
648 WHEN OTHERS THEN
649 IF c_mode%ISOPEN
650 THEN
651 CLOSE c_mode;
652 END IF;
653 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
654 IGS_GE_MSG_STACK.ADD;
655 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
656 RETURN FALSE;
657 END; -- repp_val_unit_mode_class
658 -- Module: repp_val_course
659 -- Purpose: validation for course code
660 --
661 -- MODIFICATION HISTORY
662 -- Person Date Comments
663 -- --------- -------- ------------------------------------------
664 -- MSONTER 21/08/98 Initial creation of function
665 FUNCTION repp_val_course( p_course_cd IN OUT NOCOPY IGS_PS_VER_ALL.course_cd%TYPE,
666 p_msg_name OUT NOCOPY VARCHAR2)
667 RETURN BOOLEAN
668 AS
669 --
670 -- Local Cursors
671 CURSOR c_cv(cp_course_cd IGS_PS_VER.course_cd%TYPE)
672 IS
673 SELECT 'x'
674 FROM IGS_PS_VER cv,
675 IGS_PS_STAT cs
676 WHERE course_cd LIKE cp_course_cd
677 AND cs.course_status = cv.course_status
678 AND cs.s_course_status = 'ACTIVE';
679 --
680 -- Local Variables
681 v_cv_exists VARCHAR2(1) := NULL;
682 BEGIN
683 -- null test
684 IF p_course_cd IS NULL THEN
685 p_course_cd := '%';
686 END IF;
687 -- upper case test
688 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('COURSE_CD','COURSE')
689 THEN
690 p_course_cd := UPPER(p_course_cd);
691 END IF;
692 -- start of main loop
693 IF p_course_cd <> '%' THEN
694 IF c_cv%ISOPEN
695 THEN
696 CLOSE c_cv;
697 END IF;
698 OPEN c_cv(p_course_cd);
699 FETCH c_cv INTO v_cv_exists;
700 IF c_cv%NOTFOUND THEN
701 CLOSE c_cv;
702 p_msg_name := 'IGS_GE_INVALID_VALUE';
703 RETURN FALSE;
704 END IF; --IF c_cv%NOTFOUND
705 CLOSE c_cv;
706 p_msg_name := NULL;
707 END IF; --IF p_course_cd <> '%'
708 RETURN TRUE;
709 EXCEPTION
710 WHEN OTHERS THEN
711 IF c_cv%ISOPEN
712 THEN
713 CLOSE c_cv;
714 END IF;
715 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
716 IGS_GE_MSG_STACK.ADD;
717 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
718 RETURN FALSE;
719 END; --repp_val_course
720 -- Module: repp_val_ass
721 -- Purpose: validation for assessment item
722 --
723 -- MODIFICATION HISTORY
724 -- Person Date Comments
725 -- --------- -------- ------------------------------------------
726 -- MSONTER 21/08/98 Initial creation of function
727 FUNCTION repp_val_ass( p_ass_id IN VARCHAR2,
728 p_ass_desc OUT NOCOPY VARCHAR2,
729 p_msg_name OUT NOCOPY VARCHAR2)
730 RETURN BOOLEAN
731 AS
732 --
733 -- Local Cursors
734 CURSOR c_ass(cp_ass_id IGS_AS_ASSESSMNT_ITM.ass_id%TYPE)
735 IS
736 SELECT
737 TO_CHAR(asi.ass_id) || ' - ' ||
738 asi.description
739 FROM IGS_AS_ASSESSMNT_ITM asi
740 WHERE asi.ass_id = cp_ass_id;
741 --
742 -- Local Variables
743 v_ass_desc VARCHAR2(256) := NULL;
744 v_ass_id NUMBER := 0;
745 --
746 -- Local IGS_GE_EXCEPTIONS
747 begin
748 --
749 -- validation
750 IF p_ass_id <> '%'
751 THEN
752 IF c_ass%ISOPEN
753 THEN
754 CLOSE c_ass;
755 END IF;
756 --
757 -- convert to numeric value - error not expected
758 v_ass_id := TO_NUMBER(p_ass_id);
759 OPEN c_ass(v_ass_id);
760 FETCH c_ass INTO v_ass_desc;
761 IF c_ass%NOTFOUND
762 THEN
763 CLOSE c_ass;
764 p_msg_name := 'IGS_GE_VAL_DOES_NOT_XS';
765 RETURN FALSE;
766 END IF;
767 CLOSE c_ass;
768 p_ass_desc := v_ass_desc;
769 ELSE
770 p_ass_desc := p_ass_id;
771 END IF;
772 return (TRUE);
773 EXCEPTION
774 WHEN INVALID_NUMBER THEN
775 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
776 IGS_GE_MSG_STACK.ADD;
777 App_Exception.Raise_Exception ;
778 p_msg_name := 'IGS_GE_INVALID_VALUE';
779 RETURN FALSE;
780 WHEN OTHERS THEN
781 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
782 IGS_GE_MSG_STACK.ADD;
783 p_msg_name := 'IGS_AS_USER_EXCEPTION_RAISED';
784 RETURN FALSE;
785 end; --repp_val_ass
786 -- Module: repp_get_curr_cal
787 -- Purpose: return of current calendar for given SI_CA_S_CA_CAT
788 --
789 -- MODIFICATION HISTORY
790 -- Person Date Comments
791 -- --------- -------- ------------------------------------------
792 -- MSONTER 27/08/98 Initial creation of function
793 FUNCTION repp_get_curr_cal( p_s_cal_cat IN IGS_CA_TYPE.s_cal_cat%TYPE,
794 p_cal OUT NOCOPY VARCHAR2)
795 RETURN BOOLEAN
796 AS
797 --
798 -- Local Cursors
799 CURSOR c_cal(cp_s_cal_cat IN IGS_CA_TYPE.s_cal_cat%TYPE)
800 IS
801 SELECT RPAD(RPAD(NVL(ci.alternate_code, ci.cal_type), 10) || ' ' ||
802 IGS_GE_DATE.igschar(ci.start_dt) || ' - ' ||
803 IGS_GE_DATE.igschar(ci.end_dt) || ' ' || ct.abbreviation, 100) ||
804 RPAD(ci.cal_type, 10) ||
805 TO_CHAR(ci.sequence_number, '999990') cal_desc
806 FROM IGS_CA_TYPE ct,
807 IGS_CA_INST ci,
808 IGS_CA_STAT cs
809 WHERE ct.s_cal_cat = cp_s_cal_cat AND
810 ci.cal_type = ct.cal_type AND
811 cs.cal_status = ci.cal_status AND
812 cs.s_cal_status = 'ACTIVE' AND
813 ci.start_dt < SYSDATE AND
814 ci.end_dt > SYSDATE;
815 --
816 -- Local Variables
817 v_cal VARCHAR2(120) := NULL;
818 BEGIN
819 IF c_cal%ISOPEN THEN
820 CLOSE c_cal;
821 END IF;
822 OPEN c_cal(p_s_cal_cat);
823 FETCH c_cal INTO v_cal;
824 IF c_cal%NOTFOUND
825 THEN
826 CLOSE c_cal;
827 p_cal := v_cal;
828 RETURN TRUE;
829 END IF;
830 CLOSE c_cal;
831 p_cal := v_cal;
832 RETURN TRUE;
833 EXCEPTION
834 WHEN OTHERS THEN
835 IF c_cal%ISOPEN THEN
836 CLOSE c_cal;
837 END IF;
838 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
839 IGS_GE_MSG_STACK.ADD;
840 P_cal := v_cal;
841 RETURN TRUE;
842 END; --repp_get_curr_cal
843 -- Module: repp_get_nomin_cal
844 -- Purpose: return of nominated calendar for given IGS_CA_TYPE/sequence_number
845 --
846 -- MODIFICATION HISTORY
847 -- Person Date Comments
848 -- --------- -------- ------------------------------------------
849 -- MSONTER 27/08/98 Initial creation of function
850 FUNCTION repp_get_nomin_cal( p_cal_type IN IGS_CA_TYPE.cal_type%TYPE,
851 p_seq_num IN IGS_CA_INST_ALL.sequence_number%TYPE,
852 p_cal OUT NOCOPY VARCHAR2)
853 RETURN BOOLEAN
854 AS
855 --
856 -- Local Cursors
857 CURSOR c_cal(cp_cal_type IN IGS_CA_TYPE.cal_type%TYPE,
858 cp_seq_num IN IGS_CA_INST.sequence_number%TYPE)
859 IS
860 SELECT RPAD(RPAD(NVL(ci.alternate_code, ci.cal_type), 10) || ' ' ||
861 IGS_GE_DATE.igschar(ci.start_dt) || ' - ' ||
862 IGS_GE_DATE.igschar(ci.end_dt) || ' ' || ct.abbreviation, 100) ||
863 RPAD(ci.cal_type, 10) ||
864 TO_CHAR(ci.sequence_number, '999990') cal_desc
865 FROM IGS_CA_TYPE ct,
866 IGS_CA_INST ci
867 WHERE ct.cal_type = cp_cal_type AND
868 ci.cal_type = ct.cal_type AND
869 ci.sequence_number = cp_seq_num;
870 --
871 -- Local Variables
872 v_cal VARCHAR2(120) := NULL;
873 BEGIN
874 FOR c_rec IN c_cal(p_cal_type, p_seq_num) LOOP
875 v_cal := c_rec.cal_desc;
876 END LOOP;
877 p_cal := v_cal;
878 RETURN TRUE;
879 EXCEPTION
880 WHEN OTHERS THEN
881 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
882 IGS_GE_MSG_STACK.ADD;
883 p_cal := v_cal;
884 RETURN TRUE;
885 END; --repp_get_nomin_cal
886 -- Module: repp_get_cal_str
887 -- Purpose: return of nominated calendar for given IGS_CA_TYPE/sequence_number
888 -- used in select statements
889 -- MODIFICATION HISTORY
890 -- Person Date Comments
891 -- --------- -------- ------------------------------------------
892 -- MSONTER 27/08/98 Initial creation of function
893 FUNCTION repp_get_cal_str( p_cal_type IN IGS_CA_TYPE.cal_type%TYPE,
894 p_seq_num IN IGS_CA_INST_ALL.sequence_number%TYPE)
895 RETURN VARCHAR2
896 AS
897 --
898 -- Local Cursors
899 CURSOR c_cal(cp_cal_type IN IGS_CA_TYPE.cal_type%TYPE,
900 cp_seq_num IN IGS_CA_INST.sequence_number%TYPE)
901 IS
902 SELECT RPAD(RPAD(NVL(ci.alternate_code, ci.cal_type), 10) || ' ' ||
903 IGS_GE_DATE.igschar(ci.start_dt) || ' - ' ||
904 IGS_GE_DATE.igschar(ci.end_dt) || ' ' || ct.abbreviation, 100) ||
905 RPAD(ci.cal_type, 10) ||
906 TO_CHAR(ci.sequence_number, '999990') cal_desc
907 FROM IGS_CA_TYPE ct,
908 IGS_CA_INST ci
909 WHERE ct.cal_type = cp_cal_type AND
910 ci.cal_type = ct.cal_type AND
911 ci.sequence_number = cp_seq_num;
912 --
913 -- Local Variables
914 v_cal VARCHAR2(120) := NULL;
915 BEGIN
916 FOR c_rec IN c_cal(p_cal_type, p_seq_num) LOOP
917 v_cal := c_rec.cal_desc;
918 END LOOP;
919 RETURN v_cal;
920 EXCEPTION
921 WHEN OTHERS THEN
922 RETURN v_cal;
923 END; --repp_get_cal_str
924 -- Module: repp_val_org_cd
925 -- Purpose: validation of org units
926 --
927 -- MODIFICATION HISTORY
928 -- Person Date Comments
929 -- --------- -------- ------------------------------------------
930 -- MSONTER 28/08/98 Initial creation of function
931 FUNCTION repp_val_org_cd(p_org_cd IN OUT NOCOPY HZ_PARTIES.PARTY_NUMBER%TYPE,
932 p_msg_name OUT NOCOPY VARCHAR2 )
933 RETURN BOOLEAN
934 AS
935 --
936 -- Local Cursors
937 CURSOR c_cv(cp_org_cd IGS_OR_UNIT.org_unit_cd%TYPE)
938 IS
939 SELECT 'x'
940 FROM IGS_OR_UNIT ou,
941 IGS_OR_STATUS ous
942 WHERE org_unit_cd LIKE cp_org_cd
943 AND ous.org_status = ou.org_status
944 AND ous.s_org_status = 'ACTIVE';
945 --
946 -- Local Variables
947 v_exists VARCHAR2(1) := NULL;
948 BEGIN
949 -- null test
950 IF p_org_cd IS NULL THEN
951 p_org_cd := '%';
952 END IF;
953 -- upper case test
954 IF IGS_GE_GEN_001.GENP_CHK_COL_UPPER('ORG_UNIT_CD','IGS_OR_UNIT')
955 THEN
956 p_org_cd := UPPER(p_org_cd);
957 END IF;
958 -- start of main loop
959 IF p_org_cd <> '%' THEN
960 IF c_cv%ISOPEN
961 THEN
962 CLOSE c_cv;
963 END IF;
964 OPEN c_cv(p_org_cd);
965 FETCH c_cv INTO v_exists;
966 IF c_cv%NOTFOUND THEN
967 CLOSE c_cv;
968 p_msg_name := 'IGS_GE_INVALID_VALUE';
969 RETURN FALSE;
970 END IF; --IF c_cv%NOTFOUND
971 CLOSE c_cv;
972 p_msg_name := NULL;
973 END IF; --IF p_org_cd <> '%'
974 RETURN TRUE;
975 EXCEPTION
976 WHEN OTHERS THEN
977 IF c_cv%ISOPEN
978 THEN
979 CLOSE c_cv;
980 END IF;
981 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
982 IGS_GE_MSG_STACK.ADD;
983 RETURN FALSE;
984 END; --repp_val_org_cd
985 END IGS_GE_VAL_PARAM;
[Home] [Help]