1 PACKAGE BODY IGS_AS_VAL_UAI AS
2 /* $Header: IGSAS34B.pls 120.0 2005/07/05 11:41:37 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 -- DDEY 02-Jan-2001 Bug # 2162831 . FUNCTION assp_val_unit_sec_uniqref is added.
7 --smadathi 24-AUG-2001 Bug No. 1956374 .Removed references to duplicate
8 -- function GENP_VAL_SDTT_SESS
9 -------------------------------------------------------------------------------------------
10 -- Validate assessment item exists
11 FUNCTION assp_val_ai_exists(
12 p_ass_id IN IGS_AS_ASSESSMNT_ITM_ALL.ass_id%TYPE ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN IS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- assp_val_ai_exists
17 --Validate that the assessment item exists.
18 DECLARE
19 CURSOR c_ai (
20 cp_ass_id IGS_AS_ASSESSMNT_ITM.ass_id%TYPE) IS
21 SELECT COUNT(*)
22 FROM IGS_AS_ASSESSMNT_ITM
23 WHERE ass_id = cp_ass_id;
24 v_ai_count NUMBER;
25 BEGIN
26 -- Set the default message number
27 P_MESSAGE_NAME := null;
28 -- Cursor handling
29 OPEN c_ai(
30 p_ass_id);
31 FETCH c_ai INTO v_ai_count;
32 CLOSE c_ai;
33 IF (v_ai_count = 0) THEN
34 P_MESSAGE_NAME := 'IGS_GE_VAL_DOES_NOT_XS';
35 RETURN FALSE;
36 END IF;
37 -- Return the default value
38 RETURN TRUE;
39 END;
40 EXCEPTION
41 WHEN OTHERS THEN
42 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
43 --APP_EXCEPTION.RAISE_EXCEPTION;
44 END assp_val_ai_exists;
45 --
46
47 -- Validate IGS_PS_UNIT mode closed indicator.
48 FUNCTION crsp_val_um_closed(
49 p_unit_mode IN IGS_AS_UNIT_MODE.unit_mode%TYPE ,
50 p_message_name OUT NOCOPY VARCHAR2 )
51 RETURN BOOLEAN IS
52 gv_other_detail VARCHAR2(255);
53 BEGIN -- crsp_val_um_closed
54 -- Validate the IGS_AS_UNIT_MODE closed indicator
55 DECLARE
56 CURSOR c_um(
57 cp_unit_mode IGS_AS_UNIT_MODE.unit_mode%TYPE) IS
58 SELECT closed_ind
59 FROM IGS_AS_UNIT_MODE
60 WHERE unit_mode = cp_unit_mode;
61 v_um_rec c_um%ROWTYPE;
62 cst_yes CONSTANT CHAR := 'Y';
63 BEGIN
64 -- Set the default message number
65 P_MESSAGE_NAME := null;
66 -- Cursor handling
67 OPEN c_um(
68 p_unit_mode);
69 FETCH c_um INTO v_um_rec;
70 IF c_um%NOTFOUND THEN
71 CLOSE c_um;
72 RETURN TRUE;
73 END IF;
74 CLOSE c_um;
75 IF (v_um_rec.closed_ind = cst_yes) THEN
76 P_MESSAGE_NAME := 'IGS_PS_UNITMODE_CLOSED';
77 RETURN FALSE;
78 END IF;
79 -- Return the default value
80 RETURN TRUE;
81 END;
82
83 END crsp_val_um_closed;
84 --
85 -- Validate IGS_PS_UNIT class closed indicator.
86 FUNCTION crsp_val_ucl_closed(
87 p_unit_class IN IGS_AS_UNIT_CLASS_ALL.unit_class%TYPE ,
88 p_message_name OUT NOCOPY VARCHAR2 )
89 RETURN BOOLEAN IS
90 gv_other_detail VARCHAR2(255);
91 BEGIN -- crsp_val_ucl_closed
92 -- Validate the IGS_PS_UNIT class closed indicator
93 DECLARE
94 CURSOR c_ucl(
95 cp_unit_class IGS_AS_UNIT_CLASS.unit_class%TYPE) IS
96 SELECT closed_ind
97 FROM IGS_AS_UNIT_CLASS
98 WHERE unit_class = cp_unit_class;
99 v_ucl_rec c_ucl%ROWTYPE;
100 cst_yes CONSTANT CHAR := 'Y';
101 BEGIN
102 -- Set the default message number
103 P_MESSAGE_NAME := null;
104 -- Cursor handling
105 OPEN c_ucl(
106 p_unit_class);
107 FETCH c_ucl INTO v_ucl_rec;
108 IF c_ucl%NOTFOUND THEN
109 CLOSE c_ucl;
110 RETURN TRUE;
111 END IF;
112 CLOSE c_ucl;
113 IF (v_ucl_rec.closed_ind = cst_yes) THEN
114 P_MESSAGE_NAME := 'IGS_PS_UNITCLASS_CLOSED';
115 RETURN FALSE;
116 END IF;
117 -- Return the default value
118 RETURN TRUE;
119 END;
120 END crsp_val_ucl_closed;
121 --
122 -- Validate IGS_PS_UNIT assessment item links for invalid combinations.
123 FUNCTION assp_val_uai_links(
124 p_unit_cd IN IGS_AS_UNITASS_ITEM_ALL.unit_cd%TYPE ,
125 p_version_number IN IGS_AS_UNITASS_ITEM_ALL.version_number%TYPE ,
126 p_cal_type IN IGS_AS_UNITASS_ITEM_ALL.cal_type%TYPE ,
127 p_ci_sequence_number IN IGS_AS_UNITASS_ITEM_ALL.ci_sequence_number%TYPE ,
128 p_ass_id IN IGS_AS_UNITASS_ITEM_ALL.ass_id%TYPE ,
129 p_sequence_number IN IGS_AS_UNITASS_ITEM_ALL.sequence_number%TYPE ,
130 p_location_cd IN VARCHAR2,
131 p_unit_mode IN IGS_AS_UNIT_MODE.unit_mode%TYPE,
132 p_unit_class IN IGS_AS_UNIT_CLASS_ALL.unit_class%TYPE,
133 p_message_name OUT NOCOPY VARCHAR2 )
134 RETURN BOOLEAN IS
135 BEGIN -- assp_val_uai_links
136 --ijeddy, Bug 3201661, Grade Book. Obsoleted
137 RETURN TRUE;
138 END assp_val_uai_links;
139 --
140 -- Generic links validation routine.
141 -- Validate that date is not after the assessment variation cutoff date.
142 FUNCTION ASSP_VAL_CUTOFF_DT(
143 p_cal_type IN VARCHAR2 ,
144 p_ci_sequence_number IN NUMBER ,
145 p_effective_dt IN DATE ,
146 p_message_name OUT NOCOPY VARCHAR2 )
147 RETURN BOOLEAN IS
148 gv_other_detail VARCHAR2(255);
149 BEGIN -- assp_val_cutoff_dt
150 -- Validate that it is possible to alter assessment items for a teaching period
151 -- provided the efffective date is before the cutoff date.
152 DECLARE
153 cst_one CONSTANT NUMBER := 1;
154 v_ass_item_cutoff_dt_alias IGS_AS_CAL_CONF.ass_item_cutoff_dt_alias%TYPE;
155 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
156 CURSOR c_sacc IS
157 SELECT ass_item_cutoff_dt_alias
158 FROM IGS_AS_CAL_CONF
159 WHERE s_control_num = cst_one;
160 CURSOR c_daiv(
161 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
162 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
163 cp_dt_alias IGS_AS_CAL_CONF.ass_item_cutoff_dt_alias%TYPE) IS
164 SELECT alias_val
165 FROM IGS_CA_DA_INST_V
166 WHERE cal_type = cp_cal_type AND
167 ci_sequence_number = cp_ci_sequence_number AND
168 dt_alias = cp_dt_alias
169 ORDER BY alias_val DESC;
170 BEGIN
171 -- Set the default message number
172 P_MESSAGE_NAME := null;
173 -- Determine the date alias for the assessment item variation cutoff date.
174 OPEN c_sacc;
175 FETCH c_sacc INTO v_ass_item_cutoff_dt_alias;
176 IF c_sacc%NOTFOUND THEN
177 CLOSE c_sacc;
178 RETURN TRUE;
179 END IF;
180 CLOSE c_sacc;
181 IF (v_ass_item_cutoff_dt_alias IS NULL) THEN
182 P_MESSAGE_NAME := null;
183 RETURN TRUE;
184 END IF;
185 -- Determine the latest date alias instance within the teaching period and
186 -- verify that the effective date is less than the date alias instance value.
187 OPEN c_daiv(
188 p_cal_type,
189 p_ci_sequence_number,
190 v_ass_item_cutoff_dt_alias);
191 FETCH c_daiv INTO v_alias_val;
192 IF c_daiv%NOTFOUND THEN
193 CLOSE c_daiv;
194 RETURN TRUE;
195 END IF;
196 CLOSE c_daiv;
197 IF (p_effective_dt > v_alias_val) THEN
198 P_MESSAGE_NAME := 'IGS_AS_NOTALTER_ASSITEM_DET';
199 RETURN FALSE;
200 END IF;
201 -- Return the default value
202 RETURN TRUE;
203 END;
204 EXCEPTION
205 WHEN OTHERS THEN
206 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
207 --APP_EXCEPTION.RAISE_EXCEPTION;
208 END assp_val_cutoff_dt;
209 --
210 -- Validate Calendar Instance for IGS_PS_COURSE Information.
211 FUNCTION CRSP_VAL_CRS_CI(
212 p_cal_type IN VARCHAR2 ,
213 p_ci_sequence_number IN NUMBER ,
214 p_message_name OUT NOCOPY VARCHAR2 )
215 RETURN BOOLEAN IS
216 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
217 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
218 CURSOR c_cal_status(
219 cp_cal_type IGS_CA_INST.cal_type%TYPE,
220 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
221 SELECT IGS_CA_STAT.s_cal_status
222 FROM IGS_CA_INST, IGS_CA_STAT
223 WHERE IGS_CA_INST.cal_type = cp_cal_type AND
224 IGS_CA_INST.sequence_number = cp_ci_sequence_number AND
225 IGS_CA_INST.cal_status = IGS_CA_STAT.cal_status;
226 v_other_detail VARCHAR2(255);
227 BEGIN
228 P_MESSAGE_NAME := null;
229 OPEN c_cal_status(
230 p_cal_type,
231 p_ci_sequence_number);
232 FETCH c_cal_status INTO v_s_cal_status;
233 CLOSE c_cal_status;
234 IF (v_s_cal_status = cst_active) THEN
235 RETURN TRUE;
236 ELSE
237 P_MESSAGE_NAME := 'IGS_PS_CAL_MUSTBE_ACTIVE';
238 RETURN FALSE;
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
243 --APP_EXCEPTION.RAISE_EXCEPTION;
244 END crsp_val_crs_ci;
245 --
246 -- Validate IGS_PS_UNIT Offering Calendar Type.
247 FUNCTION crsp_val_uo_cal_type(
248 p_cal_type IN VARCHAR2 ,
249 p_message_name OUT NOCOPY VARCHAR2 )
250 RETURN BOOLEAN IS
251 v_other_detail VARCHAR2(255);
252 v_cal_type_rec IGS_CA_TYPE%ROWTYPE;
253 CURSOR c_cal_type IS
254 SELECT *
255 FROM IGS_CA_TYPE
256 WHERE cal_type = p_cal_type;
257 BEGIN
258 P_MESSAGE_NAME := null;
259 OPEN c_cal_type;
260 FETCH c_cal_type INTO v_cal_type_rec;
261 IF (c_cal_type%NOTFOUND) THEN
262 CLOSE c_cal_type;
263 RETURN TRUE;
264 END IF;
265 CLOSE c_cal_type;
266 -- Test cal_cat.closed_ind
267 IF (v_cal_type_rec.closed_ind <> 'N') THEN
268 P_MESSAGE_NAME := 'IGS_CA_CALTYPE_CLOSED';
269 RETURN FALSE;
270 -- Test cal_cat.SI_CA_S_CA_CAT
271
272 ELSIF (v_cal_type_rec.s_cal_cat <> 'TEACHING') THEN
273 P_MESSAGE_NAME := 'IGS_PS_CAL_CATEGORY_TEACHING';
274 RETURN FALSE;
275 END IF;
276 RETURN TRUE;
277
278 END crsp_val_uo_cal_type;
279 --
280 -- Retrofitted
281 FUNCTION assp_val_uai_uniqref(
282 p_unit_cd IN VARCHAR2 ,
283 p_version_number IN NUMBER ,
284 p_cal_type IN VARCHAR2 ,
285 p_ci_sequence_number IN NUMBER ,
286 p_sequence_number IN NUMBER ,
287 p_reference IN VARCHAR2 ,
288 p_ass_id IN NUMBER ,
289 p_message_name OUT NOCOPY VARCHAR2 )
290 RETURN BOOLEAN IS
291 gv_other_detail VARCHAR2(255);
292 BEGIN -- assp_val_uai_uniqref
293 -- Validate reference number is unique within a IGS_PS_UNIT offering pattern for
294 -- examinable items
295 DECLARE
296 CURSOR c_uai IS
297 SELECT 'x'
298 FROM IGS_AS_ASSESSMNT_TYP atyp,
299 IGS_AS_ASSESSMNT_ITM ai,
300 IGS_AS_UNITASS_ITEM uai
301 WHERE atyp.examinable_ind = 'Y' AND
302 ai.assessment_type = atyp.assessment_type AND
303 uai.ass_id = ai.ass_id AND
304 uai.unit_cd = p_unit_cd AND
305 uai.version_number = p_version_number AND
306 uai.cal_type = p_cal_type AND
307 uai.ci_sequence_number = p_ci_sequence_number AND
308 uai.ass_id <> p_ass_id AND
309 uai.sequence_number <> p_sequence_number AND
310 NVL(uai.reference, 'NULL') = NVL(p_reference, 'NULL');
311 v_uai_exists VARCHAR2(1);
312 BEGIN
313 -- Set the default message number
314 P_MESSAGE_NAME := null;
315 -- Check for the existence of a record
316 OPEN c_uai;
317 FETCH c_uai INTO v_uai_exists;
318 IF c_uai%NOTFOUND THEN
319 CLOSE c_uai;
320 RETURN TRUE;
321 END IF;
322 CLOSE c_uai;
323 -- Records have been found
324 P_MESSAGE_NAME := 'IGS_AS_REFCD_UAI_UNIQUE';
325 RETURN FALSE;
326 EXCEPTION
327 WHEN OTHERS THEN
328 IF c_uai%ISOPEN THEN
329 CLOSE c_uai;
330 END IF;
331 RAISE;
332 END;
333 EXCEPTION
334 WHEN OTHERS THEN
335 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
336 --APP_EXCEPTION.RAISE_EXCEPTION;
337 END assp_val_uai_uniqref;
338
339 -- Added by DDEY as part of bug # 2162831 .
340 -- This function validate if the reference number is unique
341 -- within the Unit Section.
342
343 -- There are 2 cases for checking the Reference Number
344 -- Case 1: For examinable items Reference must be unique for each item .
345 -- Case 2: For non-examinable items of assessment type ASSIGNMENT, reference must be unique
346 -- for a particular assessment type associated with a unit offering pattern.
347
348 FUNCTION assp_val_unit_sec_uniqref(
349 p_unit_cd IN VARCHAR2 ,
350 p_version_number IN NUMBER ,
351 p_cal_type IN VARCHAR2 ,
352 p_ci_sequence_number IN NUMBER ,
353 p_sequence_number IN NUMBER ,
354 p_reference IN VARCHAR2 ,
355 p_ass_id IN NUMBER ,
356 p_message_name OUT NOCOPY VARCHAR2 )
357 RETURN BOOLEAN IS
358 gv_other_detail VARCHAR2(255);
359 BEGIN -- assp_val_unit_sec_uniqref
360 -- Validate reference number is unique within a IGS_PS_UNIT offering pattern for
361 -- examinable items
362 DECLARE
363 CURSOR c_uai IS
364 SELECT 'x'
365 FROM IGS_AS_ASSESSMNT_TYP atyp,
366 IGS_AS_ASSESSMNT_ITM ai,
367 IGS_PS_UNITASS_ITEM uai,
368 IGS_PS_UNIT_OFR_OPT uoo
369 WHERE atyp.examinable_ind = 'Y' AND
370 ai.assessment_type = atyp.assessment_type AND
371 uai.ass_id = ai.ass_id AND
372 uoo.unit_cd = p_unit_cd AND
373 uoo.version_number = p_version_number AND
374 uoo.cal_type = p_cal_type AND
375 uoo.ci_sequence_number = p_ci_sequence_number AND
376 uoo.uoo_id = uai.uoo_id AND
377 uai.ass_id <> p_ass_id AND
378 uai.sequence_number <> p_sequence_number AND
379 NVL(uai.reference, 'NULL') = NVL(p_reference, 'NULL');
380
381 v_uai_exists VARCHAR2(1);
382 BEGIN
383 -- Set the default message number
384 P_MESSAGE_NAME := null;
385 -- Check for the existence of a record
386 OPEN c_uai;
387 FETCH c_uai INTO v_uai_exists;
388 IF c_uai%NOTFOUND THEN
389 CLOSE c_uai;
390 RETURN TRUE;
391 END IF;
392 CLOSE c_uai;
393 -- Records have been found
394 P_MESSAGE_NAME := 'IGS_PS_REFCD_UAI_UNIQUE';
395 RETURN FALSE;
396 EXCEPTION
397 WHEN OTHERS THEN
398 IF c_uai%ISOPEN THEN
399 CLOSE c_uai;
400 END IF;
401 RAISE;
402 END;
403 EXCEPTION
404 WHEN OTHERS THEN
405 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
406 --APP_EXCEPTION.RAISE_EXCEPTION;
407 END assp_val_unit_sec_uniqref;
408
409
410 --
411 -- Retrofitted
412 FUNCTION assp_val_uai_opt_ref(
413 p_unit_cd IN VARCHAR2 ,
414 p_version_number IN NUMBER ,
415 p_cal_type IN VARCHAR2 ,
416 p_ci_sequence_number IN NUMBER ,
417 p_sequence_number IN NUMBER ,
418 p_reference IN VARCHAR2 ,
419 p_ass_id IN NUMBER ,
420 p_assessment_type IN VARCHAR2 ,
421 p_message_name OUT NOCOPY VARCHAR2 )
422 RETURN BOOLEAN IS
423 gv_other_detail VARCHAR2(255);
424 BEGIN -- assp_val_uai_opt_ref
425 -- Validate that the reference number (when it has been set)
426 -- is unique within an assessment type within a IGS_PS_UNIT offering
427 -- pattern for non-examinable items which have not been deleted.
428 -- This is similar to ASSP_VAL_UAI_UNIQREF except that:
429 -- * The routine validates non-examinable items as opposed
430 -- to examinable items
431 -- * Reference is optional
432 -- * Reference when set is unique within an assessment type and
433 -- only for items that have not been deleted
434 DECLARE
435 CURSOR c_uai IS
436 SELECT 'x'
437 FROM IGS_AS_UNITASS_ITEM uai,
438 IGS_AS_ASSESSMNT_ITM ai,
439 IGS_AS_ASSESSMNT_TYP atyp
440 WHERE atyp.examinable_ind = 'N' AND
441 atyp.ASSESSMENT_TYPE = p_assessment_type AND
442 atyp.ASSESSMENT_TYPE = ai.ASSESSMENT_TYPE AND
443 uai.ass_id = ai.ass_id AND
444 uai.unit_cd = p_unit_cd AND
445 uai.version_number = p_version_number AND
446 uai.cal_type = p_cal_type AND
447 uai.ci_sequence_number = p_ci_sequence_number AND
448 uai.ass_id <> p_ass_id AND
449 uai.sequence_number <> p_sequence_number AND
450 uai.reference = p_reference AND
451 uai.logical_delete_dt IS NULL;
452 v_uai_exists VARCHAR2(1);
453 BEGIN
454 -- Set the default message number
455 P_MESSAGE_NAME := null;
456 IF p_reference IS NOT NULL THEN
457 -- Select from the table taking care not to select
458 -- record passed in.
459 OPEN c_uai;
460 FETCH c_uai INTO v_uai_exists;
461 IF c_uai%FOUND THEN
462 CLOSE c_uai;
463 P_MESSAGE_NAME := 'IGS_AS_REF_UAI_UNIQUE';
464 RETURN FALSE;
465 END IF;
466 CLOSE c_uai;
467 END IF;
468 -- Return the default value
469 RETURN TRUE;
470 EXCEPTION
471 WHEN OTHERS THEN
472 IF c_uai%ISOPEN THEN
473 CLOSE c_uai;
474 END IF;
475 RAISE;
476 END;
477 EXCEPTION
478 WHEN OTHERS THEN
479 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
480 --APP_EXCEPTION.RAISE_EXCEPTION;
481 END assp_val_uai_opt_ref;
482 --
483 -- Retrofitted
484
485 --
486 -- To validate the examination calendar type/sequence number of the uai
487 FUNCTION ASSP_VAL_UAI_CAL(
488 p_exam_cal_type IN VARCHAR2 ,
489 p_exam_sequence_number IN NUMBER ,
490 p_teach_cal_type IN VARCHAR2 ,
491 p_teach_sequence_number IN NUMBER ,
492 p_message_name OUT NOCOPY VARCHAR2 )
493 RETURN boolean IS
494 BEGIN
495 Return TRUE;
496 END;
497 --
498 -- Retrofitted
499 FUNCTION assp_val_uai_sameref(
500 p_unit_cd IN VARCHAR2 ,
501 p_version_number IN NUMBER ,
502 p_cal_type IN VARCHAR2 ,
503 p_ci_sequence_number IN NUMBER ,
504 p_sequence_number IN NUMBER ,
505 p_reference IN VARCHAR2 ,
506 p_ass_id IN NUMBER ,
507 p_message_name OUT NOCOPY VARCHAR2 )
508 RETURN BOOLEAN IS
509 gv_other_detail VARCHAR2(255);
510 BEGIN -- assp_val_uai_sameref
511 -- Validate reference number is the same for all items,
512 -- with the same assessment id, within a IGS_PS_UNIT offering pattern
513 -- for examinable items.
514 DECLARE
515 CURSOR c_uai IS
516 SELECT 'x'
517 FROM IGS_AS_ASSESSMNT_TYP atyp,
518 IGS_AS_ASSESSMNT_ITM ai,
519 IGS_AS_UNITASS_ITEM uai
520 WHERE atyp.examinable_ind = 'Y' AND
521 ai.assessment_type = atyp.assessment_type AND
522 uai.ass_id = ai.ass_id AND
523 uai.unit_cd = p_unit_cd AND
524 uai.version_number = p_version_number AND
525 uai.cal_type = p_cal_type AND
526 uai.ci_sequence_number = p_ci_sequence_number AND
527 uai.ass_id = p_ass_id AND
528 uai.sequence_number <> p_sequence_number AND
529 NVL(uai.reference, 'NULL') <> NVL(p_reference, 'NULL');
530 v_uai_exists VARCHAR2(1);
531 BEGIN
532 -- Set the default message number
533 P_MESSAGE_NAME := null;
534 -- Check for the existence of a record
535 OPEN c_uai;
536 FETCH c_uai INTO v_uai_exists;
537 IF c_uai%NOTFOUND THEN
538 CLOSE c_uai;
539 RETURN TRUE;
540 END IF;
541 CLOSE c_uai;
542 -- Records have been found
543 P_MESSAGE_NAME := 'IGS_AS_REF_UAI_SAME';
544 RETURN FALSE;
545 EXCEPTION
546 WHEN OTHERS THEN
547 IF c_uai%ISOPEN THEN
548 CLOSE c_uai;
549 END IF;
550 RAISE;
551 END;
552 EXCEPTION
553 WHEN OTHERS THEN
554 NULL; --FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
555 --APP_EXCEPTION.RAISE_EXCEPTION;
556 END assp_val_uai_sameref;
557 --
558 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
559 -- Val IGS_PS_UNIT assess item applies to stud IGS_PS_UNIT IGS_AD_LOCATION, class and mode.
560 FUNCTION ASSP_VAL_SUA_UAI(
561 p_student_location_cd IN VARCHAR2 ,
562 p_student_unit_class IN VARCHAR2 ,
563 p_student_unit_mode IN VARCHAR2 ,
564 p_location_cd IN VARCHAR2 ,
565 p_unit_class IN VARCHAR2 ,
566 p_unit_mode IN VARCHAR2 )
567 RETURN CHAR IS
568 v_message_name VARCHAR2(30);
569 BEGIN -- assp_val_sua_uai
570 -- Validate that the IGS_AS_UNITASS_ITEM's IGS_AD_LOCATION, mode and class
571 -- are applicable for the student
572 --ijeddy, Bug 3201661, Grade Book.Obsoleted
573 RETURN 'TRUE';
574 END assp_val_sua_uai;
575 --
576 -- Validate the IGS_PS_COURSE type for an assessment item against student IGS_PS_COURSE
577 FUNCTION ASSP_VAL_SUA_AI_ACOT(
578 p_ass_id IN NUMBER ,
579 p_person_id IN NUMBER ,
580 p_course_cd IN VARCHAR2 )
581 RETURN VARCHAR2 IS
582 gv_other_detail VARCHAR2(255);
583 BEGIN -- assp_val_sua_ai_acot
584 -- Validate that if the assessment item is of an examinable type,
585 -- then validate if there exists IGS_AS_COURSE_TYPE records that restrict
586 -- the assessment item to particular IGS_PS_COURSE type for the student's IGS_PS_COURSE.
587 DECLARE
588 cst_no CONSTANT CHAR := 'N';
589 v_course_type IGS_PS_VER.course_type%TYPE;
590 V_MESSAGE_NAME VARCHAR2(30) := NULL;
591 CURSOR c_sua( cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
592 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
593 SELECT crv.course_type
594 FROM IGS_EN_STDNT_PS_ATT sca,
595 IGS_PS_VER crv
596 WHERE sca.person_id = cp_person_id AND
597 sca.course_cd = cp_course_cd AND
598 sca.course_cd = crv.course_cd AND
599 sca.version_number = crv.version_number;
600 BEGIN
601 -- Cursor handling
602 OPEN c_sua( p_person_id,
603 p_course_cd);
604 FETCH c_sua INTO v_course_type;
605 IF c_sua%NOTFOUND THEN
606 CLOSE c_sua;
607 RAISE NO_DATA_FOUND;
608 END IF;
609 CLOSE c_sua;
610 IF IGS_AS_VAL_SUAAI.assp_val_ai_acot(p_ass_id,
611 v_course_type,
612 V_MESSAGE_NAME) = TRUE THEN
613 RETURN 'TRUE';
614 ELSE
615 RETURN 'FALSE';
616 END IF;
617 END;
618 END assp_val_sua_ai_acot;
619 --
620 --
621 -- Validate modification of IGS_PS_UNIT ass item does not conflict with uapi.
622 FUNCTION ASSP_VAL_UAI_UAPI(
623 p_unit_cd IN VARCHAR2 ,
624 p_version_number IN NUMBER ,
625 p_cal_type IN VARCHAR2 ,
626 p_ci_sequence_number IN NUMBER ,
627 p_ass_id IN NUMBER ,
628 p_sequence_number IN NUMBER ,
629 p_old_location_cd IN VARCHAR2 ,
630 p_old_unit_class IN VARCHAR2 ,
631 p_old_unit_mode IN VARCHAR2 ,
632 p_old_logical_delete_dt IN DATE ,
633 p_new_location_cd IN VARCHAR2 ,
634 p_new_unit_class IN VARCHAR2 ,
635 p_new_unit_mode IN VARCHAR2 ,
636 p_new_logical_delete_dt IN DATE ,
637 p_message_name OUT NOCOPY VARCHAR2 )
638 RETURN BOOLEAN IS
639 gv_other_detail VARCHAR2(255);
640 BEGIN -- assp_val_uai_uapi
641 -- Validate that can update the IGS_AS_UNITASS_ITEM.
642 -- Modification is not allowed if :
643 -- logically deleting and the item belongs to a pattern. It must be removed
644 -- from the pattern first.
645 -- Updating IGS_AD_LOCATION, IGS_PS_UNIT mode or IGS_PS_UNIT class and the item belongs to a
646 -- pattern. The pattern IGS_AD_LOCATION, IGS_PS_UNIT mode or IGS_PS_UNIT class must be update
647 -- first or the item removed from the pattern(s).
648 --stubbed by ijeddy for bug 3881046 on 22 Sept, 2004.
649 RETURN TRUE;
650 END assp_val_uai_uapi;
651 END IGS_AS_VAL_UAI;
[Home] [Help]