1 PACKAGE BODY IGS_AS_GEN_005 AS
2 /* $Header: IGSAS05B.pls 120.2 2006/08/16 11:35:29 sepalani noship $ */
3
4 /* Change History
5 who when what
6 smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_suaai, c_sua to select active (not closed) unit classes.
7 shimitta 21-Feb-2006 Bug# 5042414.
8 sepalani 16-Aug-2006 Bug# 5469461
9 */
10
11 FUNCTION assp_mnt_suaai_uap (
12 p_unit_cd IN VARCHAR2,
13 p_version_number IN NUMBER,
14 p_cal_type IN VARCHAR2,
15 p_ci_sequence_number IN NUMBER,
16 p_ass_pattern_id IN NUMBER,
17 p_location_cd IN VARCHAR2,
18 p_unit_class IN VARCHAR2,
19 p_unit_mode IN VARCHAR2,
20 p_s_log_type IN VARCHAR2,
21 p_key IN VARCHAR2,
22 p_sle_key IN OUT NOCOPY VARCHAR2,
23 p_error_count IN OUT NOCOPY NUMBER,
24 p_warning_count IN OUT NOCOPY NUMBER,
25 p_message_name OUT NOCOPY VARCHAR2
26 ) RETURN BOOLEAN IS
27 BEGIN
28 RETURN FALSE;
29 END assp_mnt_suaai_uap;
30
31 FUNCTION assp_mnt_uapi_suaai (
32 p_person_id IN NUMBER,
33 p_course_cd IN VARCHAR2,
34 p_unit_cd IN VARCHAR2,
35 p_version_number IN NUMBER,
36 p_cal_type IN VARCHAR2,
37 p_ci_sequence_number IN NUMBER,
38 p_ass_pattern_id IN NUMBER,
39 p_location_cd IN VARCHAR2,
40 p_unit_class IN VARCHAR2,
41 p_s_log_type IN VARCHAR2,
42 p_key IN VARCHAR2,
43 p_sle_key IN VARCHAR2,
44 p_error_count IN OUT NOCOPY NUMBER,
45 p_warning_count IN OUT NOCOPY NUMBER,
46 p_message_name OUT NOCOPY VARCHAR2
47 ) RETURN BOOLEAN IS
48 BEGIN
49 RETURN FALSE;
50 END assp_mnt_uapi_suaai;
51
52 FUNCTION assp_set_suao_trans (
53 p_person_id IN NUMBER,
54 p_course_cd IN VARCHAR2,
55 p_unit_cd IN VARCHAR2,
56 p_cal_type IN VARCHAR2,
57 p_ci_sequence_number IN NUMBER,
58 p_outcome_dt IN DATE,
59 p_grade IN VARCHAR2,
60 p_grading_schema_cd IN VARCHAR2,
61 p_version_number IN NUMBER,
62 p_translated_grading_schema_cd IN OUT NOCOPY VARCHAR2,
63 p_translated_version_number IN OUT NOCOPY NUMBER,
64 p_translated_grade IN OUT NOCOPY VARCHAR2,
65 p_translated_dt IN OUT NOCOPY DATE,
66 p_message_name OUT NOCOPY VARCHAR2,
67 p_uoo_id IN NUMBER
68 ) RETURN BOOLEAN IS
69 gv_other_detail VARCHAR2 (255);
70 BEGIN -- assp_set_suao_trans
71 -- This module is called when ever the IGS_AS_SU_STMPTOUT.grade is
72 -- altered. It will check to see if a grade has had a translation performed
73 -- (translated_dt set), if so, then determine the translation for the new
74 -- grade. Where a grading schema has been recorded against the course
75 -- offering pattern, this will be used to perform the translation.
76 --
77 -- NOTE: There is a batch routine (assp_upd_suao_trans) which is called to
78 -- perform the original translations. This module is called when a grade
79 -- is updated and will check if a translation performed previously and set
80 -- the translation fields as required.
81 --
82 -- Conditions where no translation occurs:
83 --
84 -- ? No IGS_PS_OFR_PAT grading schema specified.
85 -- ? IGS_PS_UNIT_OFR_OPT.grading_schema_prcdnce_ind = 'Y'
86 -- ? No grade mapping specified in IGS_AS_GRD_SCH_TRN.
87 DECLARE
88 v_suao_rec_exists VARCHAR2 (1);
89 v_to_grade igs_as_grd_sch_trn.to_grade%TYPE;
90 v_suao_exists BOOLEAN DEFAULT FALSE;
91 v_alt_cd VARCHAR2 (10);
92 v_acad_cal_type igs_ca_inst.cal_type%TYPE;
93 v_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
94 v_acad_ci_start_dt DATE;
95 v_acad_ci_end_dt DATE;
96 v_message_name VARCHAR2 (30);
97 CURSOR c_suao IS
98 SELECT 'x'
99 FROM igs_as_su_stmptout suao
100 WHERE suao.person_id = p_person_id
101 AND suao.course_cd = p_course_cd
102 AND suao.uoo_id = p_uoo_id
103 AND suao.outcome_dt < p_outcome_dt
104 AND suao.translated_dt IS NOT NULL;
105 CURSOR c_cop (
106 cp_acad_cal_type igs_ps_ofr_pat.cal_type%TYPE,
107 cp_acad_ci_seq_num igs_ps_ofr_pat.ci_sequence_number%TYPE
108 ) IS
109 SELECT cop.grading_schema_cd,
110 cop.gs_version_number
111 FROM igs_en_su_attempt sua,
112 igs_en_stdnt_ps_att sca,
113 igs_ps_unit_ofr_opt uoo,
114 igs_ps_ofr_pat cop
115 WHERE sua.person_id = p_person_id
116 AND sua.course_cd = p_course_cd
117 AND sua.uoo_id = p_uoo_id
118 AND uoo.uoo_id = sua.uoo_id
119 AND uoo.grading_schema_prcdnce_ind = 'N'
120 AND sca.person_id = sua.person_id
121 AND sca.course_cd = sua.course_cd
122 AND cop.coo_id = sca.coo_id
123 AND cop.cal_type = cp_acad_cal_type
124 AND cop.ci_sequence_number = cp_acad_ci_seq_num
125 AND cop.grading_schema_cd IS NOT NULL
126 AND cop.gs_version_number IS NOT NULL;
127 v_cop_rec c_cop%ROWTYPE;
128 CURSOR c_gsgt (
129 cp_grading_schema_cd igs_ps_ofr_pat.grading_schema_cd%TYPE,
130 cp_gs_ver_num igs_ps_ofr_pat.gs_version_number%TYPE
131 ) IS
132 SELECT gsgt.to_grade
133 FROM igs_as_grd_sch_trn gsgt
134 WHERE gsgt.grading_schema_cd = p_grading_schema_cd
135 AND gsgt.version_number = p_version_number
136 AND gsgt.grade = p_grade
137 AND gsgt.to_grading_schema_cd = cp_grading_schema_cd
138 AND gsgt.to_version_number = cp_gs_ver_num;
139 BEGIN
140 p_message_name := NULL;
141 IF p_translated_dt IS NULL THEN
142 -- If not translating, maybe a new record and field not yet
143 -- set or record not even exiosting, check if a previous
144 -- grade entry exists that has been translated.
145 OPEN c_suao;
146 FETCH c_suao INTO v_suao_rec_exists;
147 IF c_suao%NOTFOUND THEN
148 -- No translation required as outcome not yet
149 -- translated. Return from the module.
150 CLOSE c_suao;
151 p_translated_grading_schema_cd := NULL;
152 p_translated_version_number := NULL;
153 p_translated_grade := NULL;
154 RETURN TRUE;
155 ELSE
156 -- Set the tranlation date to indicate
157 -- translation attempt.
158 CLOSE c_suao;
159 v_suao_exists := TRUE;
160 p_translated_dt := SYSDATE;
161 END IF;
162 END IF;
163 -- Determine the academic period for the student
164 v_alt_cd := igs_en_gen_002.enrp_get_acad_alt_cd (
165 p_cal_type,
166 p_ci_sequence_number,
167 v_acad_cal_type,
168 v_acad_ci_sequence_number,
169 v_acad_ci_start_dt,
170 v_acad_ci_end_dt,
171 v_message_name
172 );
173 IF v_message_name IS NOT NULL THEN
174 p_message_name := v_message_name;
175 RETURN FALSE;
176 END IF;
177 -- Verify that the IGS_PS_UNIT_OFR_OPT.grading_schema_prcdnce_ind = 'N'
178 -- and that IGS_PS_OFR_PAT.grading_schema_cd is not null for the
179 -- student unit attempt and get the course offering pattern grading schema
180 -- that will be used in the translation. Otherwise skip the student unit
181 -- attempt as no translation possible.
182 OPEN c_cop (v_acad_cal_type, v_acad_ci_sequence_number);
183 FETCH c_cop INTO v_cop_rec;
184 IF c_cop%FOUND THEN
185 CLOSE c_cop;
186 -- Validate that their exists a grade mapping.
187 OPEN c_gsgt (v_cop_rec.grading_schema_cd, v_cop_rec.gs_version_number);
188 FETCH c_gsgt INTO v_to_grade;
189 IF c_gsgt%NOTFOUND THEN
190 CLOSE c_gsgt;
191 p_translated_grading_schema_cd := NULL;
192 p_translated_version_number := NULL;
193 p_translated_grade := NULL;
194 RETURN TRUE;
195 ELSE
196 -- If record already translated and is the same,
197 -- do not update.
198 CLOSE c_gsgt;
199 IF v_suao_exists
200 AND (NVL (p_translated_grading_schema_cd, 'NULL') = v_cop_rec.grading_schema_cd)
201 AND (NVL (p_translated_version_number, 0) = v_cop_rec.gs_version_number)
202 AND (NVL (p_translated_grade, 'NULL') = v_to_grade) THEN
203 -- Leave parameter fields the same.
204 RETURN TRUE;
205 END IF;
206 END IF;
207 -- Set the fields to the new translation.
208 p_translated_grading_schema_cd := v_cop_rec.grading_schema_cd;
209 p_translated_version_number := v_cop_rec.gs_version_number;
210 p_translated_grade := v_to_grade;
211 RETURN TRUE;
212 END IF;
213 -- IF processing has reached this point, then no translation possible.
214 p_translated_grading_schema_cd := NULL;
215 p_translated_version_number := NULL;
216 p_translated_grade := NULL;
217 RETURN TRUE;
218 EXCEPTION
219 WHEN OTHERS THEN
220 IF c_suao%ISOPEN THEN
221 CLOSE c_suao;
222 END IF;
223 IF c_cop%ISOPEN THEN
224 CLOSE c_cop;
225 END IF;
226 IF c_gsgt%ISOPEN THEN
227 CLOSE c_gsgt;
228 END IF;
229 RAISE;
230 END;
231 EXCEPTION
232 WHEN OTHERS THEN
233 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
234 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_set_suao_trans');
235 igs_ge_msg_stack.ADD;
236 app_exception.raise_exception;
237 END assp_set_suao_trans;
238
239 FUNCTION assp_upd_suaai_dflt (
240 p_person_id IN NUMBER,
241 p_course_cd IN VARCHAR2,
242 p_unit_cd IN VARCHAR2,
243 p_cal_type IN VARCHAR2,
244 p_ci_sequence_number IN NUMBER,
245 p_version_number IN NUMBER,
246 p_location_cd IN VARCHAR2,
247 p_unit_class IN VARCHAR2,
248 p_unit_mode IN VARCHAR2,
249 p_s_log_type IN VARCHAR2,
250 p_key IN VARCHAR2,
251 p_sle_key IN VARCHAR2,
252 p_error_count IN OUT NOCOPY NUMBER,
253 p_warning_count IN OUT NOCOPY NUMBER,
254 p_message_name OUT NOCOPY VARCHAR2,
255 p_uoo_id IN NUMBER
256 ) RETURN BOOLEAN IS
257 gv_other_detail VARCHAR2 (255);
258 BEGIN -- assp_upd_suaai_dflt
259 -- This routine will perform a routine that will check if assessment items
260 -- still apply to the students new unit offering oprion or if they should
261 -- be logically deleted and default items assigned for the new unit
262 -- offering option.
263 -- This routine will return false and rollback any alteration if a lock exists
264 -- when attempting to logically delete an assessment item.
265 DECLARE
266 cst_yes CONSTANT CHAR := 'Y';
267 v_message_name VARCHAR2 (30);
268 v_func_ret_flag BOOLEAN;
269 CURSOR cur_uoo_id IS
270 SELECT uoo_id
271 FROM igs_ps_unit_ofr_opt
272 WHERE unit_cd = p_unit_cd
273 AND version_number = p_version_number
274 AND cal_type = p_cal_type
275 AND ci_sequence_number = p_ci_sequence_number
276 AND location_cd = p_location_cd
277 AND unit_class = p_unit_class;
278 rec_uoo_id cur_uoo_id%ROWTYPE;
279 CURSOR c_suaai (
280 cp_person_id igs_as_su_atmpt_itm.person_id%TYPE,
281 cp_course_cd igs_as_su_atmpt_itm.course_cd%TYPE,
282 cp_unit_cd igs_as_su_atmpt_itm.unit_cd%TYPE,
283 cp_cal_type igs_as_su_atmpt_itm.cal_type%TYPE,
284 cp_ci_sequence_number igs_as_su_atmpt_itm.ci_sequence_number%TYPE,
285 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE
286 ) IS
287 SELECT suaai.ass_id,
288 suaai.unit_section_ass_item_id,
289 suaai.unit_ass_item_id
290 FROM igs_as_su_atmpt_itm suaai,
291 igs_en_su_attempt_all sua
292 WHERE suaai.person_id = cp_person_id
293 AND suaai.course_cd = cp_course_cd
294 AND suaai.uoo_id = cp_uoo_id
295 AND sua.person_id = suaai.person_id
296 AND sua.course_cd = suaai.course_cd
297 AND sua.uoo_id = suaai.uoo_id
298 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
299 AND suaai.attempt_number = (SELECT MAX (attempt_number)
300 FROM igs_as_su_atmpt_itm suaai2
301 WHERE suaai2.person_id = cp_person_id
302 AND suaai2.course_cd = cp_course_cd
303 AND suaai2.uoo_id = cp_uoo_id
304 AND suaai2.ass_id = suaai.ass_id)
305 AND suaai.s_default_ind = cst_yes
306 AND suaai.logical_delete_dt IS NULL;
307 CURSOR c_suv (
308 cp_person_id igs_as_su_atmpt_itm.person_id%TYPE,
309 cp_course_cd igs_as_su_atmpt_itm.course_cd%TYPE,
310 cp_unit_cd igs_as_su_atmpt_itm.unit_cd%TYPE,
311 cp_cal_type igs_as_su_atmpt_itm.cal_type%TYPE,
312 cp_ci_sequence_number igs_as_su_atmpt_itm.ci_sequence_number%TYPE,
313 cp_ass_id igs_as_su_atmpt_itm.ass_id%TYPE,
314 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE
315 ) IS
316 SELECT suv.ass_id
317 FROM igs_as_uai_sua_v suv
318 WHERE suv.person_id = cp_person_id
319 AND suv.course_cd = cp_course_cd
320 AND suv.uoo_id = cp_uoo_id
321 AND suv.ass_id = cp_ass_id
322 AND suv.uai_dflt_item_ind = cst_yes
323 AND suv.uai_logical_delete_dt IS NULL;
324 CURSOR c_uai (
325 cp_unit_cd igs_as_su_atmpt_itm.unit_cd%TYPE,
326 cp_version_number igs_as_unitass_item.version_number%TYPE,
327 cp_cal_type igs_as_su_atmpt_itm.cal_type%TYPE,
328 cp_ci_sequence_number igs_as_su_atmpt_itm.ci_sequence_number%TYPE
329 ) IS
330 SELECT DISTINCT ass_id,
331 unit_ass_item_id,
332 unit_ass_item_group_id,
333 midterm_mandatory_type_code ,
334 midterm_weight_qty ,
335 final_mandatory_type_code ,
336 final_weight_qty ,
337 grading_schema_cd ,
338 gs_version_number
339 FROM igs_as_unitass_item
340 WHERE unit_cd = cp_unit_cd
341 AND version_number = cp_version_number
342 AND cal_type = cp_cal_type
343 AND ci_sequence_number = cp_ci_sequence_number
344 AND dflt_item_ind = cst_yes
345 AND logical_delete_dt IS NULL;
346 v_suaai_rec c_suaai%ROWTYPE;
347 v_uai_rec c_uai%ROWTYPE;
348 v_suv_rec c_suv%ROWTYPE;
349 l_ass_id igs_as_su_atmpt_itm.unit_ass_item_id%TYPE;
350 BEGIN
351 -- Set the default message number
352 p_message_name := NULL;
353 OPEN cur_uoo_id;
354 FETCH cur_uoo_id INTO rec_uoo_id;
355 CLOSE cur_uoo_id;
356 SAVEPOINT sp_upd_suaai_dflt;
357 FOR v_suaai_rec IN c_suaai (
358 p_person_id,
359 p_course_cd,
360 p_unit_cd,
361 p_cal_type,
362 p_ci_sequence_number,
363 rec_uoo_id.uoo_id
364 ) LOOP
365 -- Validate if the item still applies to the new unit offering.
366 -- If not, then logically delete it.
367 -- Select from the IGS_AS_UAI_SUA_V as this provides the current assessment items
368 -- that are applicable the the unit offering of the student unit attempt.
369 -- If the assessment item is found in the view, this means that the item still
370 -- applies to the student.
371 OPEN c_suv (
372 p_person_id,
373 p_course_cd,
374 p_unit_cd,
375 p_cal_type,
376 p_ci_sequence_number,
377 v_suaai_rec.ass_id,
378 rec_uoo_id.uoo_id
379 );
380 FETCH c_suv INTO v_suv_rec;
381 IF c_suv%NOTFOUND THEN
382 CLOSE c_suv;
383 -- Delete the record as it is no longer valid.
384 IF(v_suaai_rec.unit_section_ass_item_id IS NULL) THEN
385 l_ass_id := v_suaai_rec.unit_ass_item_id ;
386 ELSE
387 l_ass_id := v_suaai_rec.unit_section_ass_item_id ;
388 END IF;
389 IF igs_as_gen_001.assp_del_suaai_dflt (
390 p_person_id,
391 p_cal_type,
392 p_ci_sequence_number,
393 p_course_cd,
394 p_unit_cd,
395 v_suaai_rec.ass_id,
396 p_s_log_type,
397 p_key,
398 p_sle_key,
399 p_error_count,
400 p_warning_count,
401 v_message_name,
402 rec_uoo_id.uoo_id,
403 l_ass_id
404 ) = FALSE THEN
405 -- If a logical delete has failed, then a lock must exist
406 -- when attempting to update the logical_delete_dt
407 -- Issue a rollback to the savepoint issued at the start of processing.
408 ROLLBACK TO sp_upd_suaai_dflt;
409 p_message_name := v_message_name;
410 RETURN FALSE;
411 END IF;
412 ELSE
413 CLOSE c_suv;
414 END IF;
415 END LOOP;
416 -- Insert any default assessment items that do not already exist for the new
417 -- unit offering.
418 FOR v_uai_rec IN c_uai (p_unit_cd, p_version_number, p_cal_type, p_ci_sequence_number) LOOP
419 v_func_ret_flag :=
420 igs_as_gen_004.assp_ins_suaai_dflt (
421 p_person_id,
422 p_course_cd,
423 p_unit_cd,
424 p_version_number,
425 p_cal_type,
426 p_ci_sequence_number,
427 p_location_cd,
428 p_unit_class,
429 v_uai_rec.ass_id,
430 NULL,
431 'UNIT', -- Added by DDEY as a part of enhancement Bug # 2162831
432 NULL,
433 p_s_log_type,
434 p_key,
435 p_sle_key,
436 p_error_count,
437 p_warning_count,
438 v_message_name,
439 v_uai_rec.unit_ass_item_id ,
440 v_uai_rec.unit_ass_item_group_id,
441 v_uai_rec.midterm_mandatory_type_code,
442 v_uai_rec.midterm_weight_qty,
443 v_uai_rec.final_mandatory_type_code,
444 v_uai_rec.final_weight_qty,
445 v_uai_rec.grading_schema_cd,
446 v_uai_rec.gs_version_number,
447 p_uoo_id
448 );
449 END LOOP;
450 -- Return the default value
451 RETURN TRUE;
452 END;
453 EXCEPTION
454 WHEN OTHERS THEN
455 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
456 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_upd_suaai_dflt');
457 igs_ge_msg_stack.ADD;
458 app_exception.raise_exception;
459 END assp_upd_suaai_dflt;
460
461 FUNCTION assp_upd_suaap_dflt (
462 p_person_id IN NUMBER,
463 p_course_cd IN VARCHAR2,
464 p_unit_cd IN VARCHAR2,
465 p_cal_type IN VARCHAR2,
466 p_ci_sequence_number IN NUMBER,
467 p_version_number IN NUMBER,
468 p_location_cd IN VARCHAR2,
469 p_unit_class IN VARCHAR2,
470 p_s_log_type IN VARCHAR2,
471 p_key IN VARCHAR2,
472 p_sle_key IN VARCHAR2,
473 p_error_count IN OUT NOCOPY NUMBER,
474 p_warning_count IN OUT NOCOPY NUMBER,
475 p_message_name OUT NOCOPY VARCHAR2
476 )
477 RETURN BOOLEAN IS
478 BEGIN
479 NULL;
480 END assp_upd_suaap_dflt;
481
482 FUNCTION assp_upd_uai_action (
483 p_ass_id IN igs_as_unitass_item_all.ass_id%TYPE,
484 p_message_name OUT NOCOPY VARCHAR2
485 ) RETURN BOOLEAN IS
486 e_resource_busy EXCEPTION;
487 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
488 CURSOR c_uai (cp_ass_id igs_as_unitass_item.ass_id%TYPE) IS
489 SELECT uai.ass_id
490 FROM igs_ca_stat cs,
491 igs_ca_inst ci,
492 igs_ps_unit_stat ust,
493 igs_ps_unit_ver uv,
494 igs_as_unitass_item uai
495 WHERE cs.s_cal_status <> 'INACTIVE'
496 AND ci.cal_status = cs.cal_status
497 AND uai.ci_sequence_number = ci.sequence_number
498 AND uai.cal_type = ci.cal_type
499 AND ust.s_unit_status <> 'INACTIVE'
500 AND uv.unit_status = ust.unit_status
501 AND uai.version_number = uv.version_number
502 AND uai.unit_cd = uv.unit_cd
503 AND uai.ass_id = cp_ass_id
504 AND uai.logical_delete_dt IS NULL
505 AND uai.action_dt IS NULL
506 FOR UPDATE OF uai.action_dt NOWAIT;
507 v_sysdate DATE;
508 v_record_found BOOLEAN;
509 v_other_detail VARCHAR2 (255);
510 BEGIN
511 -- This module updates the action date for all unit assessment items
512 -- for a particular assessment item when the latter has had a course
513 -- type (restriction) record added or deleted.
514 -- If a lock is encountered at any time, then the transaction is
515 -- rolled back.
516 p_message_name := NULL;
517 v_sysdate := SYSDATE;
518 v_record_found := FALSE;
519 FOR v_uai_rec IN c_uai (p_ass_id) LOOP
520 v_record_found := TRUE;
521 UPDATE igs_as_unitass_item uai
522 SET uai.action_dt = v_sysdate
523 WHERE CURRENT OF c_uai;
524 END LOOP;
525 RETURN TRUE;
526 EXCEPTION
527 WHEN e_resource_busy THEN
528 -- rollback any student_unit_attempts updated
529 p_message_name := 'IGS_AS_UAI_ASSITEM_NOUPD';
530 RETURN FALSE;
531 WHEN OTHERS THEN
532 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
533 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_upd_uai_action');
534 igs_ge_msg_stack.ADD;
535 app_exception.raise_exception;
536 END assp_upd_uai_action;
537
538 FUNCTION assp_upd_uap_uoo (
539 p_unit_cd IN VARCHAR2,
540 p_version_number IN NUMBER,
541 p_cal_type IN VARCHAR2,
542 p_ci_sequence_number IN NUMBER,
543 p_ass_pattern_id IN NUMBER,
544 p_location_cd IN VARCHAR2,
545 p_unit_class IN VARCHAR2,
546 p_unit_mode IN VARCHAR2,
547 p_call_by_db_trg IN VARCHAR2 DEFAULT 'N',
548 p_message_name OUT NOCOPY VARCHAR2
549 ) RETURN BOOLEAN IS
550 BEGIN
551 RETURN FALSE;
552 END assp_upd_uap_uoo;
553
554 FUNCTION assp_val_sca_comm (
555 p_person_id IN NUMBER,
556 p_course_cd IN VARCHAR2,
557 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
558 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
559 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
560 p_exclude_unit_category IN VARCHAR2,
561 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N'
562 ) RETURN VARCHAR2 IS
563 gv_other_detail VARCHAR2 (255);
564 BEGIN -- assp_val_sca_comm
565 -- This module retrieves the latest year in the IGS_PE_PERSON has enrolment
566 -- in the course.
567 DECLARE
568 v_min_year VARCHAR2 (10);
569 CURSOR c_sca IS
570 SELECT MIN
571 (SUBSTR
572 (igs_en_gen_014.enrs_get_acad_alt_cd (suav.cal_type,
573 suav.ci_sequence_number
574 ),
575 1,
576 10
577 )
578 )
579 FROM igs_en_stdnt_ps_att sca,
580 igs_en_su_attempt suav,
581 igs_ps_ofr_pat cop,
582 igs_ca_inst ci
583 WHERE sca.person_id = p_person_id
584 AND sca.course_cd = p_course_cd
585 AND sca.person_id = suav.person_id
586 AND sca.course_cd = suav.course_cd
587 AND EXISTS (
588 SELECT 'X'
589 FROM igs_en_su_attempt sua
590 WHERE sua.person_id = suav.person_id
591 AND sua.course_cd = suav.course_cd
592 AND sua.uoo_id = suav.uoo_id
593 AND igs_as_gen_001.assp_val_sua_display
594 (sua.person_id,
595 sua.course_cd,
596 sca.version_number,
597 sua.unit_cd,
598 sua.cal_type,
599 sua.ci_sequence_number,
600 sua.unit_attempt_status,
601 sua.administrative_unit_status,
602 'Y',
603 p_include_fail_grade_ind,
604 p_enrolled_units_ind,
605 p_exclude_research_units_ind,
606 p_exclude_unit_category,
607 sua.uoo_id
608 ) = 'Y')
609 AND sca.coo_id = cop.coo_id
610 AND sca.location_cd = cop.location_cd
611 AND sca.attendance_mode = cop.attendance_mode
612 AND sca.attendance_type = cop.attendance_type
613 AND cop.cal_type = ci.cal_type
614 AND cop.ci_sequence_number = ci.sequence_number
615 AND igs_en_gen_014.enrs_get_within_ci (cop.cal_type,
616 cop.ci_sequence_number,
617 suav.cal_type,
618 suav.ci_sequence_number,
619 'Y'
620 ) = 'Y';
621
622 BEGIN
623 -- Determine the latest year in which the IGS_PE_PERSON has active enrolment.
624 v_min_year := NULL;
625 OPEN c_sca;
626 FETCH c_sca INTO v_min_year;
627 IF c_sca%NOTFOUND THEN
628 CLOSE c_sca;
629 END IF;
630 CLOSE c_sca;
631 RETURN v_min_year;
632 EXCEPTION
633 WHEN OTHERS THEN
634 IF c_sca%ISOPEN THEN
635 CLOSE c_sca;
636 END IF;
637 RAISE;
638 END;
639 EXCEPTION
640 WHEN OTHERS THEN
641 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
642 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_val_sca_comm');
643 igs_ge_msg_stack.ADD;
644 app_exception.raise_exception;
645 END assp_val_sca_comm;
646
647 FUNCTION assp_val_sca_final (
648 p_person_id IN NUMBER,
649 p_course_cd IN VARCHAR2,
650 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
651 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
652 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
653 p_exclude_unit_category IN VARCHAR2,
654 p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N'
655 ) RETURN VARCHAR2 IS
656 gv_other_detail VARCHAR2 (255);
657 BEGIN -- assp_val_sca_final
658 -- This module retrieves the latest year in the IGS_PE_PERSON has enrolment
659 -- in the course.
660 DECLARE
661 v_max_year VARCHAR2 (10);
662 CURSOR c_sca IS
663 SELECT MAX (SUBSTR (igs_en_gen_014.enrs_get_acad_alt_cd (suav.cal_type, suav.ci_sequence_number), 1, 10)) acad_alternate_code
664 FROM igs_en_stdnt_ps_att sca,
665 igs_en_su_attempt suav,
666 igs_ps_ofr_pat cop,
667 igs_ca_inst ci
668 WHERE sca.person_id = p_person_id
669 AND sca.course_cd = p_course_cd
670 AND sca.person_id = suav.person_id
671 AND sca.course_cd = suav.course_cd
672 AND EXISTS ( SELECT 'X'
673 FROM igs_en_su_attempt sua
674 WHERE sua.person_id = suav.person_id
675 AND sua.course_cd = suav.course_cd
676 AND sua.uoo_id = suav.uoo_id
677 AND igs_as_gen_001.assp_val_sua_display (
678 sua.person_id,
679 sua.course_cd,
680 sca.version_number,
681 sua.unit_cd,
682 sua.cal_type,
683 sua.ci_sequence_number,
684 sua.unit_attempt_status,
685 sua.administrative_unit_status,
686 'Y',
687 p_include_fail_grade_ind,
688 p_enrolled_units_ind,
689 p_exclude_research_units_ind,
690 p_exclude_unit_category,
691 sua.uoo_id
692 ) = 'Y')
693 AND sca.coo_id = cop.coo_id
694 AND sca.location_cd = cop.location_cd
695 AND sca.attendance_mode = cop.attendance_mode
696 AND sca.attendance_type = cop.attendance_type
697 AND cop.cal_type = ci.cal_type
698 AND cop.ci_sequence_number = ci.sequence_number
699 AND igs_en_gen_014.enrs_get_within_ci (
700 cop.cal_type,
701 cop.ci_sequence_number,
702 suav.cal_type,
703 suav.ci_sequence_number,
704 'Y'
705 ) = 'Y';
706 BEGIN
707 -- Determine the latest year in which the IGS_PE_PERSON has active enrolment.
708 v_max_year := NULL;
709 OPEN c_sca;
710 FETCH c_sca INTO v_max_year;
711 IF c_sca%NOTFOUND THEN
712 CLOSE c_sca;
713 END IF;
714 CLOSE c_sca;
715 RETURN v_max_year;
716 EXCEPTION
717 WHEN OTHERS THEN
718 IF c_sca%ISOPEN THEN
719 CLOSE c_sca;
720 END IF;
721 RAISE;
722 END;
723 END assp_val_sca_final;
724
725 FUNCTION assp_mnt_suaai_uai (
726 p_unit_cd IN VARCHAR2,
727 p_version_number IN NUMBER,
728 p_cal_type IN VARCHAR2,
729 p_ci_sequence_number IN NUMBER,
730 p_ass_id IN NUMBER,
731 p_location_cd IN VARCHAR2,
732 p_unit_class IN VARCHAR2,
733 p_unit_mode IN VARCHAR2,
734 p_s_log_type IN VARCHAR2,
735 p_key IN VARCHAR2,
736 p_sle_key IN OUT NOCOPY VARCHAR2,
737 p_error_count IN OUT NOCOPY NUMBER,
738 p_warning_count IN OUT NOCOPY NUMBER,
739 p_message_name OUT NOCOPY VARCHAR2,
740 p_ass_id_usec_unit_ind IN VARCHAR2 DEFAULT 'UNIT',
741 p_ass_item_id IN NUMBER ,
742 p_group_id IN NUMBER,
743 p_midterm_mandatory_type_code IN VARCHAR2,
744 p_midterm_weight_qty IN NUMBER ,
745 p_final_mandatory_type_code IN VARCHAR2,
746 p_final_weight_qty IN NUMBER,
747 p_grading_schema_cd IN VARCHAR2,
748 p_gs_version_number IN NUMBER ,
749 p_uoo_id IN NUMBER
750 ) RETURN BOOLEAN IS
751 gv_other_detail VARCHAR2 (255);
752 BEGIN -- assp_mnt_suaai_uai
753 -- This routine is called from the process that determines if changes have
754 -- been made to unit_assessment_items and applies them to the
755 -- stdnt_unit_atmpt_ass_items.
756 -- This routine will determine if the IGS_AS_SU_ATMPT_ITM is
757 -- still valid for the student. The IGS_AS_UNITASS_ITEM may have been
758 -- updated and for example the location code may have been altered from
759 -- GEELONG to BURWOOD, making the assessment item for the GEELONG student
760 -- no longer valid. In such a case it will logically delete the assessment
761 -- item.
762 -- This routine will also insert items that may not already exist. To continue
763 -- on with the above example of the location code being updated, the student
764 -- who is studying the unit at BURWOOD, now will need to have the assessment
765 -- item allocated to them.
766 DECLARE
767 cst_enrolled CONSTANT VARCHAR2 (8) := 'ENROLLED';
768 --
769 -- Get the Unit Section Identifier
770 --
771 CURSOR cur_uoo_id IS
772 SELECT uoo_id
773 FROM igs_ps_unit_ofr_opt
774 WHERE unit_cd = p_unit_cd
775 AND version_number = p_version_number
776 AND cal_type = p_cal_type
777 AND ci_sequence_number = p_ci_sequence_number;
778 --
779 --
780 --
781 CURSOR c_sua IS
782 SELECT sua.person_id,
783 sua.course_cd,
784 sua.location_cd,
785 sua.unit_class,
786 uc.unit_mode,
787 sua.uoo_id
788 FROM igs_en_su_attempt_all sua,
789 igs_as_unit_class uc
790 WHERE sua.uoo_id = NVL(p_uoo_id,sua.uoo_id) AND
791 sua.unit_cd = p_unit_cd
792 AND sua.cal_type = p_cal_type
793 AND sua.ci_sequence_number = p_ci_sequence_number
794 AND sua.version_number = p_version_number
795 AND sua.unit_attempt_status = cst_enrolled
796 AND uc.unit_class = sua.unit_class
797 AND uc.closed_ind = 'N'
798 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED');
799 --
800 --
801 --
802 CURSOR cur_suaai_usec_exists (
803 cp_person_id NUMBER,
804 cp_course_cd VARCHAR2,
805 cp_uoo_id NUMBER
806 ) IS
807 SELECT 'X'
808 FROM igs_as_su_atmpt_itm
809 WHERE person_id = cp_person_id
810 AND course_cd = cp_course_cd
811 AND uoo_id = cp_uoo_id
812 AND unit_section_ass_item_id IS NOT NULL
813 AND ROWNUM = 1;
814 --
815 rec_suaai_usec_exists cur_suaai_usec_exists%ROWTYPE;
816 v_message_name VARCHAR2 (30);
817 v_valid_ass_item BOOLEAN;
818 CURSOR cur_as_prg_type (cp_ass_id IN NUMBER) IS
819 SELECT 'X' record_exists
820 FROM igs_as_course_type_all
821 WHERE ass_id = cp_ass_id;
822 rec_as_prg_type cur_as_prg_type%ROWTYPE;
823 BEGIN
824 -- For unit testing, it will be necessary to set p_s_log_type = 'ASS3213',
825 -- other parameters can be whatever.
826 -- Also, use IGS_GE_INS_SLE.genp_set_log_cntr to initialise the logging structure
827 -- at the start of the module, otherwise you may get a value or numeric error
828 -- when attempting to test this module.
829 -- If wanting to view log entries, at the end of the module will need to call
830 -- IGS_GE_INS_SLE.genp_ins_sle(SYSDATE) to insert into IGS_GE_S_LOG_ENTRY table any
831 -- exceptions raised by the modules called. (NOTE: Not necessary to test for
832 -- logged records as this will be tested in the unit test of the called
833 -- module.)
834 -- Set the default message number
835 p_message_name := NULL;
836 -- Issue a save point for the module so that if locks exist, a rollback can
837 -- be performed.
838 SAVEPOINT sp_suaai_uai;
839 -- Select the students who have been allocated this assessment item
840 -- and validate that it still applies to them.
841 OPEN cur_as_prg_type (p_ass_id);
842 FETCH cur_as_prg_type INTO rec_as_prg_type;
843 IF (cur_as_prg_type%FOUND) THEN
844 CLOSE cur_as_prg_type;
845 --
846 UPDATE igs_as_su_atmpt_itm suaai
847 SET suaai.logical_delete_dt = SYSDATE,
848 suaai.last_update_date = SYSDATE,
849 suaai.last_updated_by = fnd_global.user_id,
850 suaai.last_update_login = fnd_global.login_id,
851 suaai.request_id = fnd_global.conc_request_id,
852 suaai.program_id = fnd_global.conc_program_id,
853 suaai.program_application_id = fnd_global.prog_appl_id,
854 suaai.program_update_date = SYSDATE
855 WHERE suaai.unit_cd = p_unit_cd
856 AND suaai.cal_type = p_cal_type
857 AND suaai.ci_sequence_number = p_ci_sequence_number
858 AND suaai.ass_id = p_ass_id
859 AND suaai.logical_delete_dt IS NULL
860 AND (suaai.unit_section_ass_item_id = p_ass_item_id
861 OR suaai.unit_ass_item_id = p_ass_item_id)
862 AND suaai.attempt_number = (
863 SELECT MAX (suaai2.attempt_number)
864 FROM igs_as_su_atmpt_itm suaai2
865 WHERE suaai2.person_id = suaai.person_id
866 AND suaai2.course_cd = suaai.course_cd
867 AND suaai2.uoo_id = suaai.uoo_id
868 AND suaai2.ass_id = suaai.ass_id
869 AND (suaai2.unit_section_ass_item_id = suaai.unit_section_ass_item_id
870 OR suaai2.unit_ass_item_id = suaai.unit_ass_item_id))
871 AND EXISTS (
872 SELECT 'X'
873 FROM igs_en_su_attempt_all sua,
874 igs_en_stdnt_ps_att sca,
875 igs_ps_ver crv
876 WHERE suaai.person_id = sua.person_id
877 AND suaai.course_cd = sua.course_cd
878 AND suaai.uoo_id = sua.uoo_id
879 AND sua.person_id = sca.person_id
880 AND sua.course_cd = sca.course_cd
881 AND sca.course_cd = crv.course_cd
882 AND sca.version_number = crv.version_number
883 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
884 AND EXISTS (
885 SELECT 'X'
886 FROM igs_as_course_type_all act
887 WHERE act.course_type <> crv.course_type
888 AND act.ass_id = suaai.ass_id
889 ));
890 p_warning_count := SQL%ROWCOUNT;
891 IF (p_warning_count > 0) THEN
892 igs_ge_ins_sle.genp_set_log_entry (
893 p_s_log_type,
894 p_key,
895 p_sle_key,
896 v_message_name,
897 'WARNING|ITEM||' || TO_CHAR (p_ass_id)
898 );
899 END IF;
900 ELSE
901 CLOSE cur_as_prg_type;
902 END IF;
903 -- Select all students within the IGS_PS_UNIT, IGS_AD_LOCATION, IGS_AS_UNIT_MODE, IGS_AS_UNIT_CLASS and
904 -- attempt to add the assessment item via the module call. Ignore any message
905 -- if fails to insert, as it will mean the student already has the item or is
906 -- not suppose to have it anyway.The student must be ENROLLED within the unit.
907 FOR v_sua_rec IN c_sua LOOP
908 p_sle_key := 'ITEM|'
909 || TO_CHAR (p_ass_id)
910 || '|'
911 || TO_CHAR (v_sua_rec.person_id)
912 || '|'
913 || v_sua_rec.course_cd
914 || '|'
915 || p_unit_cd
916 || '|'
917 || TO_CHAR (p_version_number)
918 || '|'
919 || p_cal_type
920 || '|'
921 || TO_CHAR (p_ci_sequence_number);
922 -- to check if the assessment item passed is setup at section level then
923 -- associate teh assessment item with the student if not already associated
924 -- added as a part of bug 2162831
925 IF p_ass_id_usec_unit_ind = 'USEC' THEN
926 IF NOT igs_as_gen_004.assp_ins_suaai_dflt (
927 v_sua_rec.person_id,
928 v_sua_rec.course_cd,
929 p_unit_cd,
930 p_version_number,
931 p_cal_type,
932 p_ci_sequence_number,
933 v_sua_rec.location_cd,
934 v_sua_rec.unit_class,
935 p_ass_id,
936 NULL,
937 p_ass_id_usec_unit_ind, -- Added by DDEY as a part of enhancement Bug # 2162831
938 NULL, -- No log creation date.
939 p_s_log_type,
940 p_key,
941 p_sle_key,
942 p_error_count,
943 p_warning_count,
944 v_message_name,
945 p_ass_item_id,
946 p_group_id,
947 p_midterm_mandatory_type_code,
948 p_midterm_weight_qty,
949 p_final_mandatory_type_code,
950 p_final_weight_qty,
951 p_grading_schema_cd,
952 p_gs_version_number,
953 v_sua_rec.uoo_id
954 ) THEN
955 -- Do nothing as will have failed to create the default due
956 -- to student having the item already or it may not be valid
957 -- for the unit offering that the student is attempting.
958 -- No locking will occur as not processing a pattern.
959 NULL;
960 END IF;
961 ELSIF p_ass_id_usec_unit_ind = 'UNIT' THEN
962 --IF the assessment item passed is present at unit offering level then check if the assessment
963 --item st up is presetn at nit section level
964 --IF yes then need not associate the item with the student.else call the procedure to associate
965 -- the code has been added as a part of bug number 2162831
966 --
967 -- Skip this Unit Assessment Item as Student already has Assessment Items
968 -- attached from Unit Section Level
969 --
970 OPEN cur_suaai_usec_exists (
971 v_sua_rec.person_id,
972 v_sua_rec.course_cd,
973 v_sua_rec.uoo_id
974 );
975 FETCH cur_suaai_usec_exists INTO rec_suaai_usec_exists;
976 IF (cur_suaai_usec_exists%NOTFOUND) THEN
977 CLOSE cur_suaai_usec_exists;
978 IF NOT igs_as_gen_004.assp_ins_suaai_dflt (
979 v_sua_rec.person_id,
980 v_sua_rec.course_cd,
981 p_unit_cd,
982 p_version_number,
983 p_cal_type,
984 p_ci_sequence_number,
985 v_sua_rec.location_cd,
986 v_sua_rec.unit_class,
987 p_ass_id,
988 NULL, -- No assessment pattern.
989 'UNIT', -- Added by DDEY as a part of enhancement Bug # 2162831
990 NULL, -- No log creation date.
991 p_s_log_type,
992 p_key,
993 p_sle_key,
994 p_error_count,
995 p_warning_count,
996 v_message_name ,
997 p_ass_item_id ,
998 p_group_id,
999 p_midterm_mandatory_type_code,
1000 p_midterm_weight_qty,
1001 p_final_mandatory_type_code,
1002 p_final_weight_qty,
1003 p_grading_schema_cd,
1004 p_gs_version_number,
1005 v_sua_rec.uoo_id
1006 ) THEN
1007 -- Do nothing as will have failed to create the default due
1008 -- to student having the item already or it may not be valid
1009 -- for the unit offering that the student is attempting.
1010 -- No locking will occur as not processing a pattern.
1011 NULL;
1012 END IF;
1013 ELSE
1014 CLOSE cur_suaai_usec_exists;
1015 END IF;
1016 END IF;
1017 END LOOP;
1018 -- Return the default value
1019 RETURN TRUE;
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 IF c_sua%ISOPEN THEN
1023 CLOSE c_sua;
1024 END IF;
1025 RAISE;
1026 END;
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1030 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_mnt_suaai_uai');
1031 igs_ge_msg_stack.ADD;
1032 app_exception.raise_exception;
1033 END assp_mnt_suaai_uai;
1034
1035 FUNCTION assp_upd_usec_suaai_dflt (
1036 p_person_id IN NUMBER,
1037 p_course_cd IN VARCHAR2,
1038 p_unit_cd IN VARCHAR2,
1039 p_version_number IN NUMBER,
1040 p_cal_type IN VARCHAR2,
1041 p_ci_sequence_number IN NUMBER,
1042 p_location_cd IN VARCHAR2,
1043 p_unit_class IN VARCHAR2,
1044 p_uoo_id IN NUMBER,
1045 p_s_log_type IN VARCHAR2,
1046 p_key IN VARCHAR2,
1047 p_sle_key IN VARCHAR2,
1048 p_error_count IN OUT NOCOPY NUMBER,
1049 p_warning_count IN OUT NOCOPY NUMBER,
1050 p_message_name OUT NOCOPY VARCHAR2
1051 ) RETURN BOOLEAN IS
1052 /***********************************************************************************************
1053 Created By: Deepankar Dey
1054 Date Created By: 15-01-2002
1055 Purpose: This function will perform a routine that will check if assessment items still
1056 apply to the students new unit offering option or if they should be logically
1057 deleted and default items assigned for the new unit offering option. This
1058 routine will return false and rollback any alteration if a lock exists when
1059 attempting to logically delete an assessment item.
1060 Known limitations,enhancements,remarks:
1061 Change History
1062 Who When What
1063 DDEY as a part of enhancement Bug # 2162831
1064 ********************************************************************************************** */
1065 --
1066 cst_yes VARCHAR2 (1);
1067 l_should_return_false BOOLEAN;
1068 l_message_name VARCHAR2 (30);
1069 --
1070 -- Get the default Assessment Items setup at Unit Section level
1071 --
1072 CURSOR c_ass_setup IS
1073 SELECT suv.ass_id, unit_section_ass_item_id, us_ass_item_group_id,
1074 midterm_mandatory_type_code, midterm_weight_qty,
1075 final_mandatory_type_code, final_weight_qty, grading_schema_cd,
1076 gs_version_number
1077 FROM igs_ps_unitass_item suv
1078 WHERE suv.uoo_id = p_uoo_id
1079 AND suv.dflt_item_ind = cst_yes
1080 AND suv.logical_delete_dt IS NULL;
1081
1082 --
1083 BEGIN
1084 --
1085 -- Initialise IN OUT parameters if NULL
1086 --
1087 cst_yes := 'Y';
1088 p_error_count := NVL (p_error_count, 0);
1089 p_warning_count := NVL (p_warning_count, 0);
1090 p_message_name := NULL;
1091 --
1092 -- Issue a save point for the module so that if locks exist, a rollback can
1093 -- be performed.
1094 --
1095 SAVEPOINT assp_upd_usec_suaai_dflt_sp;
1096 --
1097 l_should_return_false := FALSE;
1098 --
1099 FOR ass_setup_rec IN c_ass_setup LOOP
1100 --
1101 -- Allocate the Default Unit Section Assessment Item against the student.
1102 --
1103 IF igs_as_gen_004.assp_ins_suaai_dflt (
1104 p_person_id,
1105 p_course_cd,
1106 p_unit_cd,
1107 p_version_number,
1108 p_cal_type,
1109 p_ci_sequence_number,
1110 p_location_cd,
1111 p_unit_class,
1112 ass_setup_rec.ass_id,
1113 NULL,
1114 'USEC', -- Added by DDEY as a part of enhancement Bug # 2162831
1115 NULL,
1116 p_s_log_type,
1117 p_key,
1118 p_sle_key,
1119 p_error_count,
1120 p_warning_count,
1121 l_message_name,
1122 ass_setup_rec.unit_section_ass_item_id,
1123 ass_setup_rec.us_ass_item_group_id,
1124 ass_setup_rec.midterm_mandatory_type_code,
1125 ass_setup_rec.midterm_weight_qty,
1126 ass_setup_rec.final_mandatory_type_code,
1127 ass_setup_rec.final_weight_qty,
1128 ass_setup_rec.grading_schema_cd,
1129 ass_setup_rec.gs_version_number,
1130 p_uoo_id
1131 ) = FALSE THEN
1132 IF (l_message_name = 'IGS_AS_UNABLE_TOUPD_SUA') THEN
1133 --
1134 -- If locking error occurs, return false.
1135 --
1136 l_should_return_false := TRUE;
1137 --
1138 -- This message was changed as the message specified in the called
1139 -- function would not be appropriate. This is because, an assessment
1140 -- item cannot be attached to an assessment pattern at unit section level.
1141 --
1142 p_message_name := 'IGS_AS_UNABLE_TOUPD_SUS';
1143 EXIT;
1144 END IF;
1145 END IF;
1146 END LOOP;
1147 --
1148 IF l_should_return_false THEN
1149 RETURN FALSE;
1150 END IF;
1151 --
1152 RETURN TRUE;
1153 --
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF c_ass_setup%ISOPEN THEN
1157 CLOSE c_ass_setup;
1158 END IF;
1159 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1160 fnd_message.set_token ('NAME', 'IGS_AS_GEN_005.assp_upd_usec_suaai_dflt');
1161 igs_ge_msg_stack.ADD;
1162 app_exception.raise_exception;
1163 END assp_upd_usec_suaai_dflt;
1164 END igs_as_gen_005;
[Home] [Help]