1 PACKAGE BODY IGS_AS_GEN_001 AS
2 /* $Header: IGSAS01B.pls 120.0 2005/07/05 11:41:04 appldev noship $ */
3 /*======================================================================+
4 | |
5 | DESCRIPTION |
6 | PL/SQL boby for package: igs_as_gen_001 |
7 | |
8 | NOTES |
9 | |
10 | CHANGE HISTORY |
11 +======================================================================+
12 | WHO WHEN WHAT |
13 +======================================================================+
14 | Nalin Kumar 24-May-2003 Modified the call to the igs_as_su_atmpt_itm_pkg;
15 | Added two new parameters x_unit_section_ass_item_id and x_unit_ass_item_id in the call;
16 | This is as per 'Assessment Item description Build'; Bug# 2829291;
17 +======================================================================+*/
18 --
19 --
20 --
21 FUNCTION assp_clc_esu_ese_num (
22 p_person_id IN NUMBER,
23 p_exam_cal_type IN VARCHAR2,
24 p_exam_ci_sequence_number IN NUMBER
25 ) RETURN NUMBER IS
26 gv_other_detail VARCHAR2 (255);
27 BEGIN -- assp_clc_esu_ese_num
28 -- Calculate the number of distinct sessions for which a IGS_PE_PERSON has been a
29 -- supervisor.
30 -- If the exam period is specified (cal_type, ci_sequence_number), then
31 -- determine the count for that period, otherwise count the number of
32 -- sessions supervised prior to the current date.
33 DECLARE
34 v_sysdate DATE;
35 v_session_count NUMBER := 0;
36 v_exam_cal_type igs_as_exm_ins_spvsr.exam_cal_type%TYPE;
37 v_exam_ci_seq_num igs_as_exm_ins_spvsr.exam_ci_sequence_number%TYPE;
38 v_dt_alias igs_as_exm_ins_spvsr.dt_alias%TYPE;
39 v_dai_seq_num igs_as_exm_ins_spvsr.dai_sequence_number%TYPE;
40 v_start_time igs_as_exm_ins_spvsr.start_time%TYPE;
41 v_end_time igs_as_exm_ins_spvsr.end_time%TYPE;
42 --
43 CURSOR c_ese IS
44 SELECT DISTINCT eis.exam_cal_type,
45 eis.exam_ci_sequence_number,
46 eis.dt_alias,
47 eis.dai_sequence_number,
48 eis.start_time,
49 eis.end_time
50 FROM igs_as_exm_ins_spvsr eis,
51 igs_as_exam_session_v esev
52 WHERE eis.person_id = p_person_id
53 AND eis.exam_cal_type = esev.exam_cal_type
54 AND eis.exam_ci_sequence_number = esev.exam_ci_sequence_number
55 AND eis.dt_alias = esev.dt_alias
56 AND eis.dai_sequence_number = esev.dai_sequence_number
57 AND eis.start_time = esev.start_time
58 AND ((NVL (p_exam_cal_type, 'NULL') = 'NULL')
59 OR (eis.exam_cal_type = p_exam_cal_type)
60 )
61 AND ((NVL (p_exam_ci_sequence_number, 0) = 0)
62 OR (eis.exam_ci_sequence_number = p_exam_ci_sequence_number)
63 )
64 AND esev.alias_val < v_sysdate
65 UNION
66 SELECT DISTINCT esvs.exam_cal_type,
67 esvs.exam_ci_sequence_number,
68 esvs.dt_alias,
69 esvs.dai_sequence_number,
70 esvs.start_time,
71 esvs.end_time
72 FROM igs_as_exm_ses_vn_sp esvs,
73 igs_as_exam_session_v esev
74 WHERE esvs.person_id = p_person_id
75 AND esvs.exam_cal_type = esev.exam_cal_type
76 AND esvs.exam_ci_sequence_number = esev.exam_ci_sequence_number
77 AND esvs.dt_alias = esev.dt_alias
78 AND esvs.dai_sequence_number = esev.dai_sequence_number
79 AND esvs.start_time = esev.start_time
80 AND ((NVL (p_exam_cal_type, 'NULL') = 'NULL')
81 OR (esvs.exam_cal_type = p_exam_cal_type)
82 )
83 AND ((NVL (p_exam_ci_sequence_number, 0) = 0)
84 OR (esvs.exam_ci_sequence_number = p_exam_ci_sequence_number)
85 )
86 AND esev.alias_val < v_sysdate;
87 BEGIN
88 IF p_exam_cal_type IS NULL
89 AND p_exam_ci_sequence_number IS NULL THEN
90 v_sysdate := SYSDATE;
91 ELSE
92 v_sysdate := igs_ge_date.igsdate ('3000/12/31');
93 END IF;
94 OPEN c_ese;
95 LOOP
96 FETCH c_ese INTO v_exam_cal_type,
97 v_exam_ci_seq_num,
98 v_dt_alias,
99 v_dai_seq_num,
100 v_start_time,
101 v_end_time;
102 IF (c_ese%NOTFOUND) THEN
103 EXIT;
104 END IF;
105 v_session_count := v_session_count + 1;
106 END LOOP;
107 CLOSE c_ese;
108 RETURN v_session_count;
109 END;
110 END assp_clc_esu_ese_num;
111 --
112 --
113 --
114 FUNCTION assp_clc_suaai_valid (
115 p_person_id IN NUMBER,
116 p_unit_cd IN VARCHAR2,
117 p_course_cd IN VARCHAR2,
118 p_cal_type IN VARCHAR2,
119 p_ci_sequence_number IN NUMBER,
120 p_ass_pattern_id IN NUMBER,
121 p_ass_id IN NUMBER,
122 p_logical_delete_dt IN DATE,
123 p_uoo_id IN NUMBER
124 ) RETURN VARCHAR2 IS
125 gv_other_detail VARCHAR2 (255);
126 p_message_name VARCHAR2 (30);
127 BEGIN
128 IF igs_as_val_suaai.assp_val_suaai_valid (
129 p_person_id,
130 p_unit_cd,
131 p_course_cd,
132 p_cal_type,
133 p_ci_sequence_number,
134 NULL,
135 p_ass_id,
136 p_logical_delete_dt,
137 p_message_name,
138 p_uoo_id
139 ) = TRUE THEN
140 RETURN NULL;
141 ELSE
142 RETURN 'INVALID';
143 END IF;
144 EXCEPTION
145 WHEN OTHERS THEN
146 RAISE;
147 END assp_clc_suaai_valid;
148 --
149 --
150 --
151 FUNCTION assp_clc_week_extnsn (p_week_ending_due_dt IN DATE, p_override_due_dt IN DATE, p_num_week_extnsn IN NUMBER)
152 RETURN NUMBER IS
153 gv_other_detail VARCHAR2 (255);
154 BEGIN -- assp_clc_week_extnsn
155 -- This module will determine if the dates that are passed in indicate that
156 -- there is the specified number of weeks different. It will return 0
157 -- (zero) or 1 depending if an override due date matches the parameter
158 -- indicating the number of weeks extension.
159 -- This module is called from a view suaai_extension_v which is used in the
160 -- report "Assignment Due Date Summary Report".
161 DECLARE
162 v_days_difference NUMBER (5);
163 BEGIN
164 -- Parameters validation
165 IF p_week_ending_due_dt IS NULL
166 OR p_override_due_dt IS NULL
167 OR p_num_week_extnsn IS NULL THEN
168 RETURN 0;
169 END IF;
170 IF (p_override_due_dt <= p_week_ending_due_dt) THEN
171 RETURN 0;
172 END IF;
173 v_days_difference := TRUNC (p_override_due_dt) - TRUNC (p_week_ending_due_dt);
174 IF p_num_week_extnsn = 1
175 AND v_days_difference <= 7 THEN
176 RETURN 1;
177 ELSIF p_num_week_extnsn = 2
178 AND v_days_difference > 7
179 AND v_days_difference <= 14 THEN
180 RETURN 1;
181 ELSIF p_num_week_extnsn = 3
182 AND v_days_difference > 14 THEN
183 -- Want to consider everything >= 3 weeks.
184 RETURN 1;
185 ELSE
186 RETURN 0;
187 END IF;
188 END;
189 EXCEPTION
190 WHEN OTHERS THEN
191 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
192 fnd_message.set_token ('NAME', 'IGS_AS_GEN_001.assp_clc_week_extnsn');
193 igs_ge_msg_stack.ADD;
194 app_exception.raise_exception;
195 END assp_clc_week_extnsn;
196 --
197 --
198 --
199 FUNCTION assp_del_suaai (
200 p_person_id IN NUMBER,
201 p_course_cd IN VARCHAR2,
202 p_unit_cd IN VARCHAR2,
203 p_cal_type IN VARCHAR2,
204 p_ci_sequence_number IN NUMBER,
205 p_message_name OUT NOCOPY VARCHAR2,
206 p_uoo_id IN NUMBER
207 ) RETURN BOOLEAN IS
208 gv_other_detail VARCHAR2 (255);
209 BEGIN -- assp_del_suaai
210 -- Delete a student IGS_PS_UNIT attempt item records,
211 -- Called when a student has withdrawn from a IGS_PS_UNIT and due to the early
212 -- withdrawal, the student IGS_PS_UNIT attempt is deleted.
213 DECLARE
214 e_resource_busy EXCEPTION;
215 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
216 v_message_name VARCHAR2 (30);
217
218 CURSOR c_str IS
219 SELECT s_student_todo_type,
220 sequence_number,
221 reference_number
222 FROM igs_pe_std_todo_ref
223 WHERE person_id = p_person_id
224 AND s_student_todo_type IN ('ASS_INSERT', 'ASS_STATUS', 'ASS_CHANGE')
225 AND logical_delete_dt IS NULL
226 AND course_cd = p_course_cd
227 AND uoo_id = p_uoo_id;
228 ------------------------------------------------------------------------------
229 -- Delete all assessment items assigned to the student.
230 ------------------------------------------------------------------------------
231 FUNCTION asspl_del_suaai
232 RETURN BOOLEAN IS
233 BEGIN
234 DECLARE
235 CURSOR c_del_suaai IS
236 SELECT ROWID,
237 tracking_id
238 FROM igs_as_su_atmpt_itm
239 WHERE person_id = p_person_id
240 AND course_cd = p_course_cd
241 AND uoo_id = p_uoo_id
242 FOR UPDATE OF tracking_id NOWAIT;
243 c_del_suaai_rec c_del_suaai%ROWTYPE;
244 BEGIN
245 igs_as_su_atmpt_itm_pkg.delete_row (c_del_suaai_rec.ROWID);
246 FOR v_suaai_rec IN c_del_suaai LOOP
247 IF (v_suaai_rec.tracking_id IS NOT NULL) THEN
248 IF igs_tr_gen_002.trkp_del_tri (v_suaai_rec.tracking_id, v_message_name) = FALSE THEN
249 p_message_name := v_message_name;
250 EXIT;
251 END IF;
252 END IF;
253 END LOOP;
254 IF (v_message_name IS NOT NULL) THEN
255 RETURN FALSE;
256 END IF;
257 RETURN TRUE;
258 END;
259 EXCEPTION
260 WHEN e_resource_busy THEN
261 p_message_name := 'IGS_AS_UNABLE_PERFORM_SUAA';
262 RETURN FALSE;
263 WHEN OTHERS THEN
264 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
265 fnd_message.set_token ('NAME', 'IGS_AS_GEN_001.ASSPL_DEL_SUAAI');
266 igs_ge_msg_stack.ADD;
267 app_exception.raise_exception;
268 END asspl_del_suaai;
269 BEGIN
270 p_message_name := NULL;
271 SAVEPOINT sp_del_suaai;
272 --
273 -- Delete all assessment items assigned to the student.
274 --
275 IF (asspl_del_suaai = FALSE) THEN
276 ROLLBACK TO sp_del_suaai;
277 RETURN FALSE;
278 END IF;
279 --
280 -- Remove any student todo records associated with the student and this IGS_PS_UNIT.
281 --
282 FOR v_str_rec IN c_str LOOP
283 IF igs_ge_gen_003.genp_upd_str_lgc_del (
284 p_person_id,
285 v_str_rec.s_student_todo_type,
286 v_str_rec.sequence_number,
287 v_str_rec.reference_number,
288 v_message_name
289 ) = FALSE THEN
290 --
291 -- Do nothing, this is just a tidy up routine,
292 -- if the todo record remains, it will never be activated.
293 --
294 NULL;
295 END IF;
296 END LOOP;
297 RETURN TRUE;
298 END;
299 EXCEPTION
300 WHEN OTHERS THEN
301 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
302 fnd_message.set_token ('NAME', 'IGS_AS_GEN_001.assp_del_suaai');
303 igs_ge_msg_stack.ADD;
304 app_exception.raise_exception;
305 END assp_del_suaai;
306 --
307 --
308 --
309 FUNCTION assp_del_suaai_dflt (
310 p_person_id IN NUMBER,
311 p_cal_type IN VARCHAR2,
312 p_ci_sequence_number IN NUMBER,
313 p_course_cd IN VARCHAR2,
314 p_unit_cd IN VARCHAR2,
315 p_ass_id IN NUMBER,
316 p_s_log_type IN VARCHAR2,
317 p_key IN VARCHAR2,
318 p_sle_key IN VARCHAR2,
319 p_error_count IN OUT NOCOPY NUMBER,
320 p_warning_count IN OUT NOCOPY NUMBER,
321 p_message_name OUT NOCOPY VARCHAR2,
322 p_uoo_id IN NUMBER ,
323 p_unit_ass_id IN NUMBER
324 ) RETURN BOOLEAN IS
325 gv_other_details VARCHAR2 (255);
326 BEGIN --assp_del_suaai_dflt
327 --
328 -- This routine will logically delete stdnt_unit_atmpt_ass_items for the
329 -- students IGS_PS_UNIT. If p_ass_id is NULL then logically delete all system
330 -- maintained items, otherwise logically delete the student assessment item
331 -- regardless of whether it is system assigned.
332 --
333 DECLARE
334 e_resource_busy_exception EXCEPTION;
335 PRAGMA EXCEPTION_INIT (e_resource_busy_exception, -54);
336 CURSOR c_suaai IS
337 SELECT suaai.ROWID row_id,
338 suaai.*
339 FROM igs_as_su_atmpt_itm suaai,
340 igs_en_su_attempt_all sua
341 WHERE suaai.person_id = p_person_id
342 AND suaai.course_cd = p_course_cd
343 AND suaai.uoo_id = p_uoo_id
344 AND (((NVL (p_ass_id, 0) = 0)
345 AND suaai.s_default_ind = 'Y'
346 )
347 OR (suaai.ass_id = p_ass_id
348 AND (suaai.unit_section_ass_item_id = p_unit_ass_id
349 OR suaai.unit_ass_item_id = p_unit_ass_id))
350 )
351 AND suaai.logical_delete_dt IS NULL
352 AND sua.person_id = suaai.person_id
353 AND sua.course_cd = suaai.course_cd
354 AND sua.uoo_id = suaai.uoo_id
355 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED');
356 c_suaai_rec c_suaai%ROWTYPE;
357 BEGIN
358 -- initialise IN OUT NOCOPY parameters if NULL
359 p_error_count := NVL (p_error_count, 0);
360 p_warning_count := NVL (p_warning_count, 0);
361 -- Issue a save point for the module so that if locks
362 -- exist, a rollback can be performed.
363 SAVEPOINT sp_save_point;
364 -- Perform a logical delete of the system defaulted items. If the p_ass_id is
368 igs_as_su_atmpt_itm_pkg.update_row (
365 -- set then update only that item regardless of whether it is a default item,
366 -- otherwise update all default items for the student's IGS_PS_UNIT.
367 FOR c_suaai_rec IN c_suaai LOOP
369 x_mode => 'R',
370 x_rowid => c_suaai_rec.row_id,
371 x_person_id => c_suaai_rec.person_id,
372 x_course_cd => c_suaai_rec.course_cd,
373 x_unit_cd => c_suaai_rec.unit_cd,
374 x_cal_type => c_suaai_rec.cal_type,
375 x_ci_sequence_number => c_suaai_rec.ci_sequence_number,
376 x_ass_id => c_suaai_rec.ass_id,
377 x_creation_dt => c_suaai_rec.creation_dt,
378 x_attempt_number => c_suaai_rec.attempt_number,
379 x_outcome_dt => c_suaai_rec.outcome_dt,
380 x_override_due_dt => c_suaai_rec.override_due_dt,
381 x_tracking_id => c_suaai_rec.tracking_id,
382 x_logical_delete_dt => SYSDATE,
383 x_s_default_ind => c_suaai_rec.s_default_ind,
384 x_ass_pattern_id => c_suaai_rec.ass_pattern_id,
385 x_grading_schema_cd => c_suaai_rec.grading_schema_cd,
386 x_gs_version_number => c_suaai_rec.gs_version_number,
387 x_grade => c_suaai_rec.grade,
388 x_outcome_comment_code => c_suaai_rec.outcome_comment_code,
389 x_mark => c_suaai_rec.mark,
390 x_attribute_category => c_suaai_rec.attribute_category,
391 x_attribute1 => c_suaai_rec.attribute1,
392 x_attribute2 => c_suaai_rec.attribute2,
393 x_attribute3 => c_suaai_rec.attribute3,
394 x_attribute4 => c_suaai_rec.attribute4,
395 x_attribute5 => c_suaai_rec.attribute5,
396 x_attribute6 => c_suaai_rec.attribute6,
397 x_attribute7 => c_suaai_rec.attribute7,
398 x_attribute8 => c_suaai_rec.attribute8,
399 x_attribute9 => c_suaai_rec.attribute9,
400 x_attribute10 => c_suaai_rec.attribute10,
401 x_attribute11 => c_suaai_rec.attribute11,
402 x_attribute12 => c_suaai_rec.attribute12,
403 x_attribute13 => c_suaai_rec.attribute13,
404 x_attribute14 => c_suaai_rec.attribute14,
405 x_attribute15 => c_suaai_rec.attribute15,
406 x_attribute16 => c_suaai_rec.attribute16,
407 x_attribute17 => c_suaai_rec.attribute17,
408 x_attribute18 => c_suaai_rec.attribute18,
409 x_attribute19 => c_suaai_rec.attribute19,
410 x_attribute20 => c_suaai_rec.attribute20,
411 x_uoo_id => c_suaai_rec.uoo_id,
412 x_unit_section_ass_item_id => c_suaai_rec.unit_section_ass_item_id,
413 x_unit_ass_item_id => c_suaai_rec.unit_ass_item_id,
414 x_sua_ass_item_group_id => c_suaai_rec.sua_ass_item_group_id,
415 x_midterm_mandatory_type_code => c_suaai_rec.midterm_mandatory_type_code,
416 x_midterm_weight_qty => c_suaai_rec.midterm_weight_qty,
417 x_final_mandatory_type_code => c_suaai_rec.final_mandatory_type_code,
418 x_final_weight_qty => c_suaai_rec.final_weight_qty,
419 x_submitted_date => c_suaai_rec.submitted_date,
420 x_waived_flag => c_suaai_rec.waived_flag,
421 x_penalty_applied_flag => c_suaai_rec.penalty_applied_flag
422 );
423 IF c_suaai_rec.grade IS NOT NULL THEN
424 --
425 -- Log warning that the item has an outcome recorded against it.
426 --
427 igs_ge_ins_sle.genp_set_log_entry (
428 p_s_log_type,
429 p_key,
430 p_sle_key,
431 'IGS_AS_ASSITEM_LOGICALLY_DEL', -- Warn that an outcome exist.
432 'WARNING|ITEM||' || TO_CHAR (c_suaai_rec.ass_id)
433 );
434 p_warning_count := p_warning_count + 1;
435 END IF;
436 END LOOP;
437 p_message_name := NULL;
438 RETURN TRUE;
439 EXCEPTION
440 WHEN e_resource_busy_exception THEN
441 IF (c_suaai%ISOPEN) THEN
442 CLOSE c_suaai;
443 END IF;
444 ROLLBACK TO sp_save_point;
445 p_message_name := 'IGS_AS_UNABLE_LOGICAL_DEL';
446 p_error_count := p_error_count + 1;
447 igs_ge_ins_sle.genp_set_log_entry (
448 p_s_log_type,
449 p_key,
450 p_sle_key,
451 'IGS_AS_UNABLE_LOGICAL_DEL', -- Error, record locked.
452 'ERROR|ITEM||'
453 );
454 RETURN FALSE;
455 WHEN OTHERS THEN
456 IF (c_suaai%ISOPEN) THEN
457 CLOSE c_suaai;
458 END IF;
459 RAISE;
460 END;
461 EXCEPTION
462 WHEN OTHERS THEN
463 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
464 fnd_message.set_token ('NAME', 'IGS_AS_GEN_001.assp_del_suaai_dflt');
465 igs_ge_msg_stack.ADD;
466 app_exception.raise_exception;
470 --
467 END assp_del_suaai_dflt;
468 --
469 --
471 FUNCTION assp_del_suaap_dflt (
472 p_person_id IN NUMBER,
473 p_cal_type IN VARCHAR2,
474 p_ci_sequence_number IN NUMBER,
475 p_course_cd IN VARCHAR2,
476 p_unit_cd IN VARCHAR2,
477 p_ass_pattern_id IN NUMBER,
478 p_s_log_type IN VARCHAR2,
479 p_key IN VARCHAR2,
480 p_sle_key IN VARCHAR2,
481 p_error_count IN OUT NOCOPY NUMBER,
482 p_warning_count IN OUT NOCOPY NUMBER,
483 p_message_name OUT NOCOPY VARCHAR2,
484 p_uoo_id IN NUMBER
485 ) RETURN BOOLEAN IS
486 BEGIN
487 RETURN FALSE;
488 END assp_del_suaap_dflt;
489 --
490 --
491 --
492 FUNCTION assp_del_suaap_suaai (
493 p_person_id IN NUMBER,
494 p_course_cd IN VARCHAR2,
495 p_unit_cd IN VARCHAR2,
496 p_cal_type IN VARCHAR2,
497 p_ci_sequence_number IN NUMBER,
498 p_ass_pattern_id IN NUMBER,
499 p_creation_dt IN DATE,
500 p_ass_id IN NUMBER,
501 p_call_from_db_trg IN VARCHAR2 DEFAULT 'N',
502 p_s_log_type IN VARCHAR2,
503 p_key IN VARCHAR2,
504 p_sle_key IN VARCHAR2,
505 p_error_count IN OUT NOCOPY NUMBER,
506 p_warning_count IN OUT NOCOPY NUMBER,
507 p_message_name OUT NOCOPY VARCHAR2,
508 p_uoo_id IN NUMBER
509 ) RETURN BOOLEAN IS
510 BEGIN
511 RETURN FALSE;
512 END assp_del_suaap_suaai;
513 --
514 --
515 --
516 FUNCTION assp_get_actn_msg (p_action_type IN VARCHAR2, p_s_student_todo_type IN VARCHAR2)
517 RETURN VARCHAR2 IS
518 gv_other_detail VARCHAR2 (255);
519 BEGIN -- assp_get_actn_msg
520 -- This function will be called from the report ASSR3212 which
521 -- inturn calls a procedure that processes the student todo
522 -- entries associated with automatically maintaining the
523 -- default stdnt_unit_atmpt_ass_items.
524 DECLARE
525 e_unknown_action_type EXCEPTION;
526 e_unknown_s_student_todo_type EXCEPTION;
527 cst_cutoff_lock CONSTANT VARCHAR2 (30) := 'CUTOFF_LOCK';
528 cst_after_cutoff CONSTANT VARCHAR2 (30) := 'AFTER_CUTOFF';
529 cst_status_lock CONSTANT VARCHAR2 (30) := 'STATUS_LOCK';
530 cst_change_lock CONSTANT VARCHAR2 (30) := 'CHANGE_LOCK';
531 cst_todo_lock CONSTANT VARCHAR2 (30) := 'TODO_LOCK';
532 cst_delete_lock CONSTANT VARCHAR2 (30) := 'DELETE_LOCK';
533 cst_maintain_lock CONSTANT VARCHAR2 (30) := 'MAINTAIN_LOCK';
534 cst_clear_actn_dt CONSTANT VARCHAR2 (30) := 'CLEAR_ACTN_DT';
535 cst_ass_insert CONSTANT VARCHAR2 (30) := 'ASS_INSERT';
536 cst_ass_status CONSTANT VARCHAR2 (30) := 'ASS_STATUS';
537 cst_ass_change CONSTANT VARCHAR2 (30) := 'ASS_CHANGE';
538 BEGIN
539 -- Determine the message number to be returned based on the
540 -- p_action_type and student todo type.
541 IF (p_action_type = cst_after_cutoff) THEN
542 IF (p_s_student_todo_type = cst_ass_insert) THEN
543 RETURN ('IGS_AS_CREATE_DFLT_ASSITEMS');
544 ELSIF (p_s_student_todo_type = cst_ass_status) THEN
545 RETURN ('IGS_AS_LOGICAL_DEL_DFLT_ITEMS');
546 ELSIF (p_s_student_todo_type = cst_ass_change) THEN
547 RETURN ('IGS_AS_ADJUST_DFLT_ASSITEMS');
548 ELSE
549 RAISE e_unknown_s_student_todo_type;
550 END IF;
551 END IF;
552 IF (p_action_type = cst_cutoff_lock) THEN
553 RETURN ('IGS_AS_LOGICAL_DEL_STUD_TODO');
554 END IF;
555 IF (p_action_type = cst_status_lock) THEN
556 RETURN ('IGS_AS_LOGICAL_DEL_DFLT_ITEMS');
557 END IF;
558 IF (p_action_type = cst_change_lock) THEN
559 RETURN ('IGS_AS_ADJUST_DFLT_ASSITEMS');
560 END IF;
561 IF (p_action_type = cst_todo_lock) THEN
562 IF (p_s_student_todo_type = cst_ass_insert) THEN
563 RETURN ('IGS_AS_LOGDEL_STUD_TODO_ITEM');
564 ELSIF (p_s_student_todo_type = cst_ass_status) THEN
565 RETURN ('IGS_AS_LOGDEL_STUD_TODO_CHGST');
566 ELSIF (p_s_student_todo_type = cst_ass_change) THEN
567 RETURN ('IGS_AS_LOGDEL_STUD_TODO_ALT');
568 ELSE
569 RAISE e_unknown_s_student_todo_type;
570 END IF;
571 END IF;
572 IF (p_action_type = cst_delete_lock) THEN
573 RETURN ('IGS_AS_LOGDEL_SFLT_STUD_SUAI');
574 END IF;
575 IF (p_action_type = cst_maintain_lock) THEN
576 RETURN ('IGS_AS_VALID_UAI_SUA');
577 END IF;
578 IF (p_action_type = cst_clear_actn_dt) THEN
579 RETURN ('IGS_AS_CLEAR_ACTIONDT_UAI');
580 END IF;
581 -- If processing has reached this point then have not
582 -- found a valid action type.
583 RAISE e_unknown_action_type;
584 EXCEPTION
585 WHEN e_unknown_s_student_todo_type THEN
589 END;
586 RAISE;
587 WHEN e_unknown_action_type THEN
588 RAISE;
590 END assp_get_actn_msg;
591 --
592 --
593 --
594 FUNCTION assp_get_ai_a_type (p_ass_id IN NUMBER)
595 RETURN VARCHAR2 IS
596 gv_other_detail VARCHAR2 (255);
597 BEGIN -- assp_get_ai_a_type
598 -- Return the assessment type of an assessment item.
599 DECLARE
600 CURSOR c_ai (cp_ass_id igs_as_assessmnt_itm.ass_id%TYPE) IS
601 SELECT assessment_type
602 FROM igs_as_assessmnt_itm ai
603 WHERE ai.ass_id = cp_ass_id;
604 v_ai_rec c_ai%ROWTYPE;
605 BEGIN
606 OPEN c_ai (p_ass_id);
607 FETCH c_ai INTO v_ai_rec;
608 IF c_ai%NOTFOUND THEN
609 CLOSE c_ai;
610 RAISE NO_DATA_FOUND;
611 END IF;
612 CLOSE c_ai;
613 RETURN v_ai_rec.assessment_type;
614 EXCEPTION
615 WHEN OTHERS THEN
616 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
617 fnd_message.set_token ('NAME', 'IGS_AS_GEN_001.assp_get_ai_a_type');
618 igs_ge_msg_stack.ADD;
619 app_exception.raise_exception;
620 END;
621 END assp_get_ai_a_type;
622 --
623 --
624 --
625 FUNCTION assp_val_sua_display (
626 p_person_id IN NUMBER,
627 p_course_cd IN VARCHAR2,
628 p_version_number IN NUMBER,
629 p_unit_cd IN VARCHAR2,
630 p_cal_type IN VARCHAR2,
631 p_ci_sequence_number IN NUMBER,
632 p_unit_attempt_status IN VARCHAR2,
633 p_administrative_unit_status IN VARCHAR2,
634 p_finalised_ind IN VARCHAR2 DEFAULT 'N',
635 p_include_fail_grade_ind IN VARCHAR2 DEFAULT 'N',
636 p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
637 p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
638 p_exclude_unit_category IN VARCHAR2,
639 p_uoo_id IN NUMBER
640 ) RETURN VARCHAR2 IS
641 gv_other_detail VARCHAR2 (255);
642 BEGIN -- assp_val_sua_display
643 -- This module checks if the student IGS_PS_UNIT attempt is valid to be displayed on
644 -- documentation such as the Academic Transcripts. It determines if failed
645 -- grades are to be displayed and also if grades/IGS_PS_UNIT attempt status are to
646 -- be displayed.
647 DECLARE
648 v_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
649 v_gs_version_number igs_as_grd_sch_grade.version_number%TYPE;
650 v_grade igs_as_grd_sch_grade.grade%TYPE;
651 v_dummy VARCHAR2 (1);
652 v_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
653 v_acad_cal_type igs_ca_inst.cal_type%TYPE;
654 v_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
655 v_acad_start_dt igs_ca_inst.start_dt%TYPE;
656 v_acad_end_dt igs_ca_inst.end_dt%TYPE;
657 v_effect_enr_strt_dt_alias igs_en_cal_conf.effect_enr_strt_dt_alias%TYPE;
658 v_effective_dt DATE;
659 v_alt_code igs_ca_inst.alternate_code%TYPE;
660 v_message_name VARCHAR2 (30);
661 cst_enrolled CONSTANT VARCHAR2 (15) := 'ENROLLED';
662 cst_active CONSTANT VARCHAR2 (15) := 'ACTIVE';
663 CURSOR c_aus IS
664 SELECT 'x'
665 FROM igs_ad_adm_unit_stat aus
666 WHERE aus.administrative_unit_status = p_administrative_unit_status
667 AND aus.show_on_offic_ntfctn_ind = 'N';
668 CURSOR c_gsg (
669 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
670 cp_version_number igs_as_grd_sch_grade.version_number%TYPE,
671 cp_grade igs_as_grd_sch_grade.grade%TYPE
672 ) IS
673 SELECT 'x'
674 FROM igs_as_grd_sch_grade gsg
675 WHERE gsg.grading_schema_cd = cp_grading_schema_cd
676 AND gsg.version_number = cp_version_number
677 AND gsg.grade = cp_grade
678 AND gsg.show_on_official_ntfctn_ind = 'N';
679 CURSOR c_crv IS
680 SELECT 'x'
681 FROM igs_ps_ver crv,
682 igs_ps_type ct
683 WHERE crv.course_cd = p_course_cd
684 AND crv.version_number = p_version_number
685 AND ct.course_type = crv.course_type
686 AND ct.research_type_ind = 'Y';
687 CURSOR c_uv IS
688 SELECT 'x'
689 FROM igs_ps_unit_ver uv
690 WHERE uv.unit_cd = p_unit_cd
691 AND uv.research_unit_ind = 'Y';
692 CURSOR c_uc IS
693 SELECT 'x'
694 FROM igs_ps_unit_category uc
695 WHERE uc.unit_cd = p_unit_cd
696 AND uc.unit_cat = p_exclude_unit_category;
697 CURSOR c_ci (cp_acad_cal_type igs_ca_inst.cal_type%TYPE, cp_acad_ci_seq_no igs_ca_inst.sequence_number%TYPE) IS
698 SELECT 'x'
699 FROM igs_ca_inst ci,
700 igs_ca_stat cs
701 WHERE ci.cal_type = cp_acad_cal_type
702 AND ci.sequence_number = cp_acad_ci_seq_no
703 AND ci.cal_status = cs.cal_status
704 AND cs.s_cal_status = cst_active;
705 CURSOR c_secc IS
706 SELECT secc.effect_enr_strt_dt_alias
707 FROM igs_en_cal_conf secc
708 WHERE secc.s_control_num = 1;
709 CURSOR c_daiv (
710 cp_acad_cal_type igs_ca_inst.cal_type%TYPE,
711 cp_acad_ci_seq_no igs_ca_inst.sequence_number%TYPE,
712 cp_effect_dt_alias VARCHAR2
713 ) IS
714 SELECT igs_ca_gen_001.calp_set_alias_value (
715 absolute_val,
716 igs_ca_gen_002.cals_clc_dt_from_dai (ci_sequence_number, cal_type, dt_alias, sequence_number)
717 ) alias_val
718 FROM igs_ca_da_inst daiv
719 WHERE daiv.cal_type = cp_acad_cal_type
720 AND daiv.ci_sequence_number = cp_acad_ci_seq_no
721 AND daiv.dt_alias = cp_effect_dt_alias;
722 BEGIN
723 -- Determine if the administrative IGS_PS_UNIT status indicates not to
724 -- show on official notifications.
725 IF p_administrative_unit_status IS NOT NULL THEN
726 OPEN c_aus;
727 FETCH c_aus INTO v_dummy;
728 IF c_aus%FOUND THEN
729 CLOSE c_aus;
730 RETURN 'N';
731 END IF;
732 CLOSE c_aus;
733 END IF;
734 -- Determine the grade and schema used for the IGS_PS_UNIT.
735 v_result_type := igs_as_gen_003.assp_get_sua_grade (
736 p_person_id,
737 p_course_cd,
738 p_unit_cd,
739 p_cal_type,
740 p_ci_sequence_number,
741 p_unit_attempt_status,
742 p_finalised_ind,
743 v_grading_schema_cd,
744 v_gs_version_number,
745 v_grade,
746 p_uoo_id
747 );
748 -- Check if failed units allowed.
749 IF p_include_fail_grade_ind = 'N'
750 AND NVL (v_result_type, 'NULL') = 'FAIL' THEN
751 RETURN 'N';
752 END IF;
753 -- Check if the grade is allowed on official notification.
754 IF v_grading_schema_cd IS NOT NULL
755 AND v_gs_version_number IS NOT NULL
756 AND v_grade IS NOT NULL THEN
757 OPEN c_gsg (v_grading_schema_cd, v_gs_version_number, v_grade);
758 FETCH c_gsg INTO v_dummy;
759 IF c_gsg%FOUND THEN
760 CLOSE c_gsg;
761 RETURN 'N';
762 END IF;
763 CLOSE c_gsg;
764 END IF;
765 -- Determine if research units are to be included.
766 IF p_exclude_research_units_ind = 'Y' THEN
767 OPEN c_crv;
768 FETCH c_crv INTO v_dummy;
769 IF c_crv%NOTFOUND THEN
770 CLOSE c_crv;
771 ELSE
772 CLOSE c_crv;
773 OPEN c_uv;
774 FETCH c_uv INTO v_dummy;
775 IF c_uv%FOUND THEN
776 CLOSE c_uv;
777 RETURN 'N';
778 END IF;
779 CLOSE c_uv;
780 END IF;
781 END IF;
782 IF p_exclude_unit_category IS NOT NULL THEN
783 OPEN c_uc;
784 FETCH c_uc INTO v_dummy;
785 IF c_uc%FOUND THEN
786 CLOSE c_uc;
787 RETURN 'N';
788 END IF;
789 CLOSE c_uc;
790 END IF;
791 -- Determine if current/future units are to be included
792 v_acad_cal_type := NULL;
793 v_acad_ci_sequence_number := NULL;
794 v_acad_start_dt := NULL;
795 v_acad_end_dt := NULL;
796 v_alt_code := igs_en_gen_002.enrp_get_acad_alt_cd (
797 p_cal_type,
798 p_ci_sequence_number,
799 v_acad_cal_type,
800 v_acad_ci_sequence_number,
801 v_acad_start_dt,
802 v_acad_end_dt,
803 v_message_name
804 );
805 OPEN c_ci (v_acad_cal_type, v_acad_ci_sequence_number);
806 FETCH c_ci INTO v_dummy;
807 IF c_ci%FOUND THEN
808 CLOSE c_ci;
809 IF v_acad_end_dt > SYSDATE THEN
810 OPEN c_secc;
811 FETCH c_secc INTO v_effect_enr_strt_dt_alias;
812 IF c_secc%NOTFOUND THEN
813 CLOSE c_secc;
814 v_effective_dt := v_acad_start_dt;
815 ELSE
816 CLOSE c_secc;
817 OPEN c_daiv (v_acad_cal_type, v_acad_ci_sequence_number, v_effect_enr_strt_dt_alias);
818 FETCH c_daiv INTO v_effective_dt;
819 IF c_daiv%NOTFOUND THEN
820 CLOSE c_daiv;
821 v_effective_dt := v_acad_start_dt;
822 ELSE
823 CLOSE c_daiv;
824 END IF;
825 END IF;
826 IF p_enrolled_units_ind = 'C' THEN
827 -- Current
828 IF v_effective_dt > SYSDATE THEN
829 RETURN 'N';
830 END IF;
831 ELSIF p_enrolled_units_ind = 'F' THEN
832 -- Future
833 IF v_effective_dt > SYSDATE
834 AND p_unit_attempt_status <> cst_enrolled THEN
835 RETURN 'N';
836 END IF;
837 ELSIF p_enrolled_units_ind = 'E' THEN
838 -- Exclude
839 IF p_unit_attempt_status = cst_enrolled THEN
840 RETURN 'N';
841 END IF;
842 END IF;
843 END IF;
844 ELSE
845 CLOSE c_ci;
846 END IF;
847 -- If this point reached then IGS_PS_UNIT is valid to be displayed
848 -- on official notification.
849 RETURN 'Y';
850 EXCEPTION
851 WHEN OTHERS THEN
852 IF c_aus%ISOPEN THEN
853 CLOSE c_aus;
854 END IF;
855 IF c_gsg%ISOPEN THEN
856 CLOSE c_gsg;
857 END IF;
858 IF c_crv%ISOPEN THEN
859 CLOSE c_crv;
860 END IF;
861 IF c_uc%ISOPEN THEN
862 CLOSE c_uc;
863 END IF;
864 IF c_uv%ISOPEN THEN
865 CLOSE c_uv;
866 END IF;
867 IF c_ci%ISOPEN THEN
868 CLOSE c_ci;
869 END IF;
870 IF c_secc%ISOPEN THEN
871 CLOSE c_secc;
872 END IF;
873 IF c_daiv%ISOPEN THEN
874 CLOSE c_daiv;
875 END IF;
876 RAISE;
877 END;
878 END assp_val_sua_display;
879 END igs_as_gen_001;
[Home] [Help]