1 PACKAGE BODY IGS_AS_GEN_007 AS
2 /* $Header: IGSAS07B.pls 120.0 2005/07/05 11:46:56 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 | igs_as_unitass_item_pkg; igs_ps_unitass_item_pkg
16 | Added the references of the newly added columns
17 | in the base tables. This is as per 'Assessment
18 | Item description Build'; Bug# 2829291;
19 | smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_suaai,
20 | c_sua_uai_v, c_todo and c_uai to select
21 | active (not closed) unit classes.
22 +======================================================================+*/
23 --
24 g_module_head VARCHAR2(30) := 'igs_as_gen_007';
25 --
26 PROCEDURE assp_ins_suaai_tri (
27 p_acad_perd_cal_type IN VARCHAR2,
28 p_acad_perd_sequence_number IN NUMBER,
29 p_course_cd IN VARCHAR2,
30 p_unit_cd IN VARCHAR2,
31 p_teach_perd_cal_type IN VARCHAR2,
32 p_teach_perd_sequence_number IN NUMBER,
33 p_location_cd IN VARCHAR2,
34 p_unit_class IN VARCHAR2,
35 p_unit_mode IN VARCHAR2,
36 p_person_id IN NUMBER,
37 p_ass_id IN NUMBER,
38 p_tracking_type IN VARCHAR2,
39 p_tracking_status IN VARCHAR2,
40 p_tracking_start_dt IN DATE,
41 p_tracking_item_originator IN NUMBER,
42 p_creation_dt OUT NOCOPY DATE
43 ) IS
44 gv_other_detail VARCHAR2 (255);
45 gv_log_created BOOLEAN DEFAULT FALSE;
46 BEGIN -- assp_ins_suaai_tri
47 -- Create a tracking item for a IGS_AS_SU_ATMPT_ITM.
48 DECLARE
49 v_uai_due_dt igs_as_unitass_item.due_dt%TYPE;
50 v_uai_reference igs_as_unitass_item.REFERENCE%TYPE;
51 v_uai_location_cd igs_en_su_attempt.location_cd%TYPE;
52 v_uai_unit_class igs_as_unitass_item.unit_class%TYPE;
53 v_uai_unit_mode igs_as_unitass_item.unit_mode%TYPE;
54 v_create_item BOOLEAN;
55 v_record VARCHAR2 (1024);
56 v_log_dt DATE := NULL;
57 v_check CHAR;
58 v_message_name VARCHAR2 (30);
59 e_resource_busy EXCEPTION;
60 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
61 v_tracking_id igs_tr_item.tracking_id%TYPE;
62 v_tri_start_dt igs_tr_item.start_dt%TYPE;
63 v_tri_business_days_ind igs_tr_item.business_days_ind%TYPE;
64 v_tsdv_action_dt igs_tr_step_v.action_dt%TYPE;
65 v_target_days NUMBER (3);
66 v_other_detail VARCHAR2 (255);
67 cst_enrolled CONSTANT VARCHAR2 (10) := 'ENROLLED';
68
69 -- select all_stdnt_unit_atmpt_ass_item records that are to have
70 -- a tracking item assigned to them
71 CURSOR c_suaai IS
72 SELECT suaai.person_id,
73 suaai.course_cd,
74 suaai.unit_cd,
75 suaai.cal_type,
76 suaai.ci_sequence_number,
77 suaai.ass_id,
78 suaai.creation_dt,
79 suaai.override_due_dt,
80 sua.version_number,
81 sua.uoo_id
82 FROM igs_as_su_atmpt_itm suaai,
83 igs_en_su_attempt sua,
84 igs_as_assessmnt_itm ai,
85 igs_as_unit_class ucl
86 WHERE suaai.person_id = NVL (p_person_id, suaai.person_id)
87 AND suaai.logical_delete_dt IS NULL
88 AND suaai.attempt_number = (SELECT MAX (attempt_number)
89 FROM igs_as_su_atmpt_itm suaai2
90 WHERE suaai2.person_id = suaai.person_id
91 AND suaai2.course_cd = suaai.course_cd
92 AND suaai2.uoo_id = suaai.uoo_id
93 AND suaai2.ass_id = suaai.ass_id)
94 AND suaai.tracking_id IS NULL
95 AND suaai.person_id = sua.person_id
96 AND suaai.course_cd = sua.course_cd
97 AND suaai.uoo_id = sua.uoo_id
98 AND sua.course_cd LIKE p_course_cd
99 AND sua.unit_cd LIKE p_unit_cd
100 AND sua.location_cd LIKE p_location_cd
101 AND sua.unit_class LIKE p_unit_class
102 AND sua.unit_class = ucl.unit_class
103 AND ucl.unit_mode LIKE p_unit_mode
104 AND ucl.closed_ind = 'N'
105 AND sua.unit_attempt_status = cst_enrolled
106 AND suaai.cal_type = NVL (p_teach_perd_cal_type, suaai.cal_type)
107 AND suaai.ass_id = NVL (p_ass_id, suaai.ass_id)
108 AND suaai.ci_sequence_number = NVL (p_teach_perd_sequence_number, suaai.ci_sequence_number)
109 AND igs_en_gen_014.enrs_get_within_ci (
110 p_acad_perd_cal_type,
111 p_acad_perd_sequence_number,
112 sua.cal_type,
113 sua.ci_sequence_number,
114 'Y'
115 ) = 'Y'
116 AND suaai.ass_id = ai.ass_id
117 AND igs_as_gen_002.assp_get_ai_s_type (ai.ass_id) = 'ASSIGNMENT';
118 --
119 CURSOR c_sua_uai_v (
120 cp_person_id igs_en_su_attempt.person_id%TYPE,
121 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
122 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
123 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
124 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
125 cp_ass_id igs_as_su_atmpt_itm.ass_id%TYPE,
126 cp_uoo_id igs_as_su_atmpt_itm.uoo_id%TYPE
127 ) IS
128 SELECT uai.due_dt,
129 uai.REFERENCE,
130 uai.location_cd,
131 uai.unit_class,
132 uai.unit_mode
133 FROM igs_en_su_attempt sua,
134 igs_as_unitass_item uai,
135 igs_as_unit_class uc
136 WHERE sua.person_id = cp_person_id
137 AND sua.course_cd = cp_course_cd
138 AND sua.uoo_id = cp_uoo_id
139 AND uai.ass_id = cp_ass_id
140 AND uai.logical_delete_dt IS NULL
141 AND sua.unit_cd = uai.unit_cd
142 AND sua.version_number = uai.version_number
143 AND sua.cal_type = uai.cal_type
144 AND sua.ci_sequence_number = uai.ci_sequence_number
145 AND sua.unit_class = uc.unit_class
146 AND uc.closed_ind = 'N'
147 AND igs_as_val_uai.assp_val_sua_ai_acot (uai.ass_id, sua.person_id, sua.course_cd) = 'TRUE';
148 --
149 CURSOR c_lock_suaai (
150 cp_person_id igs_en_su_attempt.person_id%TYPE,
151 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
152 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
153 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
154 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
155 cp_ass_id igs_as_su_atmpt_itm.ass_id%TYPE,
156 cp_creation_dt DATE,
157 cp_uoo_id igs_as_su_atmpt_itm.uoo_id%TYPE
158 ) IS
159 SELECT ROWID,
160 igs_as_su_atmpt_itm.*
161 FROM igs_as_su_atmpt_itm
162 WHERE person_id = cp_person_id
163 AND course_cd = cp_course_cd
164 AND uoo_id = cp_uoo_id
165 AND ass_id = cp_ass_id
166 AND creation_dt = cp_creation_dt
167 FOR UPDATE OF tracking_id NOWAIT;
168 --
169 CURSOR c_sle (
170 cp_creation_dt igs_ge_s_log_entry.creation_dt%TYPE,
171 cp_key igs_ge_s_log_entry.KEY%TYPE,
172 cp_text igs_ge_s_log_entry.text%TYPE
173 ) IS
174 SELECT 'x'
175 FROM igs_ge_s_log_entry
176 WHERE s_log_type = 'ASS3610'
177 AND creation_dt = cp_creation_dt
178 AND KEY = cp_key
179 AND text = cp_text;
180 --
181 CURSOR c_tri (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
182 SELECT start_dt,
183 business_days_ind
184 FROM igs_tr_item
185 WHERE tracking_id = cp_tracking_id;
186 --
187 CURSOR c_tsdv (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
188 SELECT MAX (action_dt)
189 FROM igs_tr_step_v
190 WHERE tracking_id = cp_tracking_id;
191 --
192 CURSOR c_tri_upd (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
193 SELECT ROWID,
194 igs_tr_item.*
195 FROM igs_tr_item
196 WHERE tracking_id = v_tracking_id
197 FOR UPDATE OF business_days_ind NOWAIT;
198 --
199 v_tri_upd_rec c_tri_upd%ROWTYPE;
200 c_lock_suaai_rec c_lock_suaai%ROWTYPE;
201 --
202 BEGIN
203 p_creation_dt := NULL;
204 -- Process each suaai record that is to have tracking item assigned.
205 FOR v_suaai_rec IN c_suaai LOOP
206 v_create_item := TRUE;
207 SAVEPOINT sp_ins_suaai_tri;
208 -- Determine the appropriate IGS_AS_UNITASS_ITEM for the
209 -- location mode and class of the student and get the due date
210 v_uai_reference := NULL;
211 OPEN c_sua_uai_v (
212 v_suaai_rec.person_id,
213 v_suaai_rec.course_cd,
214 v_suaai_rec.unit_cd,
215 v_suaai_rec.cal_type,
216 v_suaai_rec.ci_sequence_number,
217 v_suaai_rec.ass_id,
218 v_suaai_rec.uoo_id
219 );
220 FETCH c_sua_uai_v INTO v_uai_due_dt,
221 v_uai_reference,
222 v_uai_location_cd,
223 v_uai_unit_class,
224 v_uai_unit_mode;
225 IF c_sua_uai_v%NOTFOUND THEN
226 CLOSE c_sua_uai_v;
227 v_create_item := FALSE;
228 ELSIF (v_uai_due_dt IS NULL) THEN
229 CLOSE c_sua_uai_v;
230 -- Do not create a tracking item for this record.
231 -- Log an exception
232 v_create_item := FALSE;
233 v_record := v_suaai_rec.unit_cd
234 || '|'
235 || TO_CHAR (v_suaai_rec.version_number)
236 || '|'
237 || v_suaai_rec.cal_type
238 || '|'
239 || TO_CHAR (v_suaai_rec.ci_sequence_number)
240 || '|'
241 || TO_CHAR (v_suaai_rec.ass_id)
242 || '|'
243 || v_uai_reference
244 || '|'
245 || v_uai_unit_class
246 || '|'
247 || v_uai_unit_mode
248 || '|'
249 || v_uai_location_cd;
250 IF (gv_log_created = FALSE) THEN
251 igs_ge_gen_003.genp_ins_log ('ASS3610', NULL, v_log_dt);
252 gv_log_created := TRUE;
253 -- Insert into the IGS_GE_S_LOG_ENTRY table
254 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'NO_DUE_DT', NULL, v_record);
255 ELSE
256 -- Only insert if a log entry not already exists.
257 OPEN c_sle (v_log_dt, 'NO_DUE_DT', v_record);
258 FETCH c_sle INTO v_check;
259 IF (c_sle%NOTFOUND) THEN
260 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'NO_DUE_DT', NULL, v_record);
261 END IF;
262 CLOSE c_sle;
263 END IF;
264 ELSE
265 CLOSE c_sua_uai_v;
266 -- Determine if the override due date has been entered for the student
267 -- unit attempt assessment item and whether it is later than the due
268 -- date of the unit assessment item. If it is, then set it to be the
269 -- due date for the assessment item, this date will be used to set the
270 -- due date for the tracking item.
271 IF v_uai_due_dt < NVL (v_suaai_rec.override_due_dt, igs_ge_date.igsdate ('1900/01/01 00:00:00')) THEN
272 v_uai_due_dt := v_suaai_rec.override_due_dt;
273 END IF;
274 IF v_uai_due_dt < p_tracking_start_dt THEN
275 -- Do not create a tracking item for this record.
276 v_create_item := FALSE;
277 -- Log an exception to indicate the unit assessment item due date
278 -- was earlier than the tracking item start date.
282 || '|'
279 v_record := v_suaai_rec.unit_cd
280 || '|'
281 || TO_CHAR (v_suaai_rec.version_number)
283 || v_suaai_rec.cal_type
284 || '|'
285 || TO_CHAR (v_suaai_rec.ci_sequence_number)
286 || '|'
287 || TO_CHAR (v_suaai_rec.ass_id)
288 || '|'
289 || v_uai_reference
290 || '|'
291 || v_uai_unit_class
292 || '|'
293 || v_uai_unit_mode
294 || '|'
295 || v_uai_location_cd;
296 IF gv_log_created = FALSE THEN
297 igs_ge_gen_003.genp_ins_log ('ASS3610', NULL, v_log_dt); -- out NOCOPY
298 gv_log_created := TRUE;
299 -- Insert into the IGS_GE_S_LOG_ENTRY table.
300 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'EARLY_DUE_DT', NULL, v_record);
301 ELSE
302 -- Check to see if a log entry already exists for this
303 -- assessment item indicating that the due date is earlier
304 -- than the tracking start date..
305 OPEN c_sle (v_log_dt, 'EARLY_DUE_DT', v_record);
306 FETCH c_sle INTO v_check;
307 -- If no record found then insert the entry,
308 -- otherwise do nothing.
309 IF (c_sle%NOTFOUND) THEN
310 CLOSE c_sle;
311 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'EARLY_DUE_DT', NULL, v_record);
312 ELSE
313 CLOSE c_sle;
314 END IF;
315 END IF;
316 END IF;
317 END IF;
318 -- Assign a tracking item to the student unit assessment
319 -- item if successful processing so far.
320 IF (v_create_item = TRUE) THEN
321 BEGIN -- sub block
322 -- Lock suaai record (from the outter loop) for update
323 OPEN c_lock_suaai (
324 v_suaai_rec.person_id,
325 v_suaai_rec.course_cd,
326 v_suaai_rec.unit_cd,
327 v_suaai_rec.cal_type,
328 v_suaai_rec.ci_sequence_number,
329 v_suaai_rec.ass_id,
330 v_suaai_rec.creation_dt,
331 v_suaai_rec.uoo_id
332 );
333 FETCH c_lock_suaai INTO c_lock_suaai_rec;
334 -- Create the tracking item
335 igs_tr_gen_002.trkp_ins_trk_item (
336 p_tracking_status,
337 p_tracking_type,
338 v_suaai_rec.person_id,
339 p_tracking_start_dt,
340 NULL,
341 NULL,
342 NULL,
343 p_tracking_item_originator,
344 'Y',
345 v_tracking_id,
346 v_message_name
347 );
348 IF (v_message_name IS NOT NULL) THEN
349 -- Error occured in creating tracking item
350 ROLLBACK TO sp_ins_suaai_tri;
351 -- Log an exception to indicate the tracking step could
352 -- not be updated to the unit assessment item due date.
353 v_record := v_suaai_rec.unit_cd
354 || '|'
355 || TO_CHAR (v_suaai_rec.version_number)
356 || '|'
357 || v_suaai_rec.cal_type
358 || '|'
359 || TO_CHAR (v_suaai_rec.ci_sequence_number)
360 || '|'
361 || TO_CHAR (v_suaai_rec.ass_id)
362 || '|'
363 || v_uai_reference
364 || '|'
365 || v_uai_unit_class
366 || '|'
367 || v_uai_unit_mode
368 || '|'
369 || v_uai_location_cd;
370 IF gv_log_created = FALSE THEN
371 igs_ge_gen_003.genp_ins_log ('ASS3610', NULL, v_log_dt); -- out NOCOPY
372 gv_log_created := TRUE;
373 -- Insert into the IGS_GE_S_LOG_ENTRY table.
374 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'TRI_ERROR', v_message_name, v_record);
375 ELSE
376 -- Check to see if a log entry already exists for
377 -- this assessment item indicating that the due date
378 -- is earlier than the tracking start date..
379 OPEN c_sle (v_log_dt, 'TRI_ERROR', v_record);
380 FETCH c_sle INTO v_check;
381 -- If no record found then insert the entry,
382 -- otherwise do nothing.
383 IF (c_sle%NOTFOUND) THEN
384 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'TRI_ERROR', v_message_name, v_record);
385 CLOSE c_sle;
386 END IF;
387 CLOSE c_sle;
388 END IF;
389 ELSE
390 -- update suaai record that has been selected
391 -- for update nowait with the ID of the tracking item
392 igs_as_su_atmpt_itm_pkg.update_row (
396 x_unit_cd => c_lock_suaai_rec.unit_cd,
393 x_rowid => c_lock_suaai_rec.ROWID,
394 x_person_id => c_lock_suaai_rec.person_id,
395 x_course_cd => c_lock_suaai_rec.course_cd,
397 x_cal_type => c_lock_suaai_rec.cal_type,
398 x_ci_sequence_number => c_lock_suaai_rec.ci_sequence_number,
399 x_ass_id => c_lock_suaai_rec.ass_id,
400 x_creation_dt => c_lock_suaai_rec.creation_dt,
401 x_attempt_number => c_lock_suaai_rec.attempt_number,
402 x_outcome_dt => c_lock_suaai_rec.outcome_dt,
403 x_override_due_dt => c_lock_suaai_rec.override_due_dt,
404 x_tracking_id => v_tracking_id,
405 x_logical_delete_dt => c_lock_suaai_rec.logical_delete_dt,
406 x_s_default_ind => c_lock_suaai_rec.s_default_ind,
407 x_ass_pattern_id => NULL,
408 x_mode => 'R',
409 x_grading_schema_cd => c_lock_suaai_rec.grading_schema_cd,
410 x_gs_version_number => c_lock_suaai_rec.gs_version_number,
411 x_grade => c_lock_suaai_rec.grade,
412 x_outcome_comment_code => c_lock_suaai_rec.outcome_comment_code,
413 x_mark => c_lock_suaai_rec.mark,
414 x_attribute_category => c_lock_suaai_rec.attribute_category,
415 x_attribute1 => c_lock_suaai_rec.attribute1,
416 x_attribute2 => c_lock_suaai_rec.attribute2,
417 x_attribute3 => c_lock_suaai_rec.attribute3,
418 x_attribute4 => c_lock_suaai_rec.attribute4,
419 x_attribute5 => c_lock_suaai_rec.attribute5,
420 x_attribute6 => c_lock_suaai_rec.attribute6,
421 x_attribute7 => c_lock_suaai_rec.attribute7,
422 x_attribute8 => c_lock_suaai_rec.attribute8,
423 x_attribute9 => c_lock_suaai_rec.attribute9,
424 x_attribute10 => c_lock_suaai_rec.attribute10,
425 x_attribute11 => c_lock_suaai_rec.attribute11,
426 x_attribute12 => c_lock_suaai_rec.attribute12,
427 x_attribute13 => c_lock_suaai_rec.attribute13,
428 x_attribute14 => c_lock_suaai_rec.attribute14,
429 x_attribute15 => c_lock_suaai_rec.attribute15,
430 x_attribute16 => c_lock_suaai_rec.attribute16,
431 x_attribute17 => c_lock_suaai_rec.attribute17,
432 x_attribute18 => c_lock_suaai_rec.attribute18,
433 x_attribute19 => c_lock_suaai_rec.attribute19,
434 x_attribute20 => c_lock_suaai_rec.attribute20,
435 x_uoo_id => c_lock_suaai_rec.uoo_id,
436 x_unit_section_ass_item_id => c_lock_suaai_rec.unit_section_ass_item_id,
437 x_unit_ass_item_id => c_lock_suaai_rec.unit_ass_item_id,
438 x_sua_ass_item_group_id => c_lock_suaai_rec.sua_ass_item_group_id,
439 x_midterm_mandatory_type_code => c_lock_suaai_rec.midterm_mandatory_type_code,
440 x_midterm_weight_qty => c_lock_suaai_rec.midterm_weight_qty,
441 x_final_mandatory_type_code => c_lock_suaai_rec.final_mandatory_type_code,
442 x_final_weight_qty => c_lock_suaai_rec.final_weight_qty,
443 x_submitted_date => c_lock_suaai_rec.submitted_date,
444 x_waived_flag => c_lock_suaai_rec.waived_flag,
445 x_penalty_applied_flag => c_lock_suaai_rec.penalty_applied_flag
446 );
447 END IF;
448 -- Update the step that is considered the due date of the assignment.
449 -- This process should validate that the date is after the start date
450 -- of the item and also after the previous step action date if it is
451 -- sequential.
452 IF igs_tr_gen_002.trkp_upd_trst (
453 v_tracking_id,
454 NULL,
455 'ASSIGN-DUE',
456 v_uai_due_dt,
457 NULL,
458 NULL,
459 NULL,
460 NULL,
461 v_message_name
462 ) = FALSE THEN
463 -- Rollback creation of the tracking item.
464 ROLLBACK TO sp_ins_suaai_tri;
465 -- Log an exception to indicate the tracking step could
466 -- not be updated to the unit assessment item due date.
467 v_record := v_suaai_rec.unit_cd
468 || '|'
469 || TO_CHAR (v_suaai_rec.version_number)
470 || '|'
471 || v_suaai_rec.cal_type
472 || '|'
473 || TO_CHAR (v_suaai_rec.ci_sequence_number)
474 || '|'
475 || TO_CHAR (v_suaai_rec.ass_id)
476 || '|'
480 || '|'
477 || v_uai_reference
478 || '|'
479 || v_uai_unit_class
481 || v_uai_unit_mode
482 || '|'
483 || v_uai_location_cd;
484 IF gv_log_created = FALSE THEN
485 igs_ge_gen_003.genp_ins_log ('ASS3610', NULL, v_log_dt); -- out NOCOPY
486 gv_log_created := TRUE;
487 -- Insert into the IGS_GE_S_LOG_ENTRY table.
488 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'STEP_DUE_DT', v_message_name, v_record);
489 ELSE
490 -- Check to see if a log entry already exists for this
491 -- assessment item indicating that the due date is
492 -- earlier than the tracking start date..
493 OPEN c_sle (v_log_dt, 'STEP_DUE_DT', v_record);
494 FETCH c_sle INTO v_check;
495 -- If no record found then insert the entry,
496 -- otherwise do nothing.
497 IF (c_sle%NOTFOUND) THEN
498 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'STEP_DUE_DT', v_message_name, v_record);
499 CLOSE c_sle;
500 END IF;
501 CLOSE c_sle;
502 END IF;
503 END IF;
504 -- Update the target days of the item to be the maximum action date
505 -- of the item. Note: The required select statement have been placed
506 -- into Two separate statements as the view does quite a bit of
507 -- processing an this is considered to be the most efficient approach.
508 OPEN c_tri (v_tracking_id);
509 FETCH c_tri INTO v_tri_start_dt,
510 v_tri_business_days_ind;
511 CLOSE c_tri;
512 OPEN c_tsdv (v_tracking_id);
513 FETCH c_tsdv INTO v_tsdv_action_dt;
514 CLOSE c_tsdv;
515 -- Call tracking function to calculate the number of days between
516 -- the start date and the maximum action date (This function
517 -- determines the number of days overdue for a completion.
518 -- It?s functionality is the same as is required for the calculation
519 -- needed here).
520 v_target_days :=
521 igs_tr_gen_001.trkp_clc_days_ovrdue (v_tri_start_dt, v_tsdv_action_dt, v_tri_business_days_ind);
522 OPEN c_tri_upd (v_tracking_id);
523 FETCH c_tri_upd INTO v_tri_upd_rec;
524 IF c_tri_upd%FOUND THEN
525 igs_tr_item_pkg.update_row (
526 x_rowid => v_tri_upd_rec.ROWID,
527 x_tracking_id => v_tri_upd_rec.tracking_id,
528 x_tracking_status => v_tri_upd_rec.tracking_status,
529 x_tracking_type => v_tri_upd_rec.tracking_type,
530 x_source_person_id => v_tri_upd_rec.source_person_id,
531 x_start_dt => v_tri_upd_rec.start_dt,
532 x_target_days => v_target_days,
533 x_sequence_ind => v_tri_upd_rec.sequence_ind,
534 x_business_days_ind => v_tri_upd_rec.business_days_ind,
535 x_originator_person_id => v_tri_upd_rec.originator_person_id,
536 x_s_created_ind => v_tri_upd_rec.s_created_ind,
537 x_completion_due_dt => v_tri_upd_rec.completion_due_dt,
538 x_override_offset_clc_ind => v_tri_upd_rec.override_offset_clc_ind,
539 x_publish_ind => v_tri_upd_rec.publish_ind,
540 x_mode => 'R' --v_tri_upd_rec.mode
541 );
542 END IF;
543 -- Update the recipient of the step where the assignment is to be returned
544 -- to the student. Set the recipient id to be the student.
545 IF igs_tr_gen_002.trkp_upd_trst (
546 v_tracking_id,
547 NULL,
548 'ASSIGN-RTN',
549 NULL,
550 NULL,
551 NULL,
552 NULL,
553 v_suaai_rec.person_id,
554 v_message_name
555 ) = FALSE THEN
556 -- Ignore, exceptions should never occur.
557 -- If so, then may need to be log in the exception report as a future
558 -- enhancement.
559 NULL;
560 END IF;
561 CLOSE c_tri_upd;
562 CLOSE c_lock_suaai;
563 EXCEPTION
564 -- Can not lock suaai record for update,
565 -- Do not create the tracking item
566 WHEN e_resource_busy THEN
567 -- Log the exception indicating a lock on the suaai record.
568 v_uai_reference := NULL;
569 SELECT uai.REFERENCE
570 INTO v_uai_reference
571 FROM igs_en_su_attempt sua,
572 igs_as_unitass_item uai,
573 igs_as_unit_class uc
574 WHERE sua.person_id = v_suaai_rec.person_id
575 AND sua.course_cd = v_suaai_rec.course_cd
576 AND sua.uoo_id = v_suaai_rec.uoo_id
577 AND uai.ass_id = v_suaai_rec.ass_id
578 AND uai.logical_delete_dt IS NULL
579 AND sua.unit_cd = uai.unit_cd
583 AND sua.unit_class = uc.unit_class
580 AND sua.version_number = uai.version_number
581 AND sua.cal_type = uai.cal_type
582 AND sua.ci_sequence_number = uai.ci_sequence_number
584 AND uc.closed_ind = 'N'
585 AND igs_as_val_uai.assp_val_sua_ai_acot (uai.ass_id, sua.person_id, sua.course_cd) = 'TRUE';
586 v_record := TO_CHAR (v_suaai_rec.person_id)
587 || '|'
588 || v_suaai_rec.course_cd
589 || '|'
590 || v_suaai_rec.unit_cd
591 || '|'
592 || v_suaai_rec.cal_type
593 || '|'
594 || TO_CHAR (v_suaai_rec.ci_sequence_number)
595 || '|'
596 || TO_CHAR (v_suaai_rec.ass_id)
597 || '|'
598 || v_uai_reference
599 || '|'
600 || SUBSTR (igs_ge_date.igschardt (v_suaai_rec.creation_dt), 1, 18);
601 IF gv_log_created = FALSE THEN
602 igs_ge_gen_003.genp_ins_log ('ASS3610', NULL, v_log_dt); -- out NOCOPY
603 gv_log_created := TRUE;
604 END IF;
605 -- Insert into the IGS_GE_S_LOG_ENTRY table.
606 igs_ge_gen_003.genp_ins_log_entry ('ASS3610', v_log_dt, 'SUAAI_LOCK', NULL, v_record);
607 WHEN OTHERS THEN
608 RAISE;
609 END; -- sub block
610 END IF; -- IF create item
611 -- Commit the processing of each student unit assessment item.
612 COMMIT;
613 END LOOP;
614 IF (gv_log_created = TRUE) THEN
615 p_creation_dt := v_log_dt;
616 END IF;
617 END;
618 END assp_ins_suaai_tri;
619
620 PROCEDURE assp_ins_suao_hist (
621 p_person_id IN NUMBER,
622 p_course_cd IN VARCHAR2,
623 p_unit_cd IN VARCHAR2,
624 p_cal_type IN VARCHAR2,
625 p_ci_sequence_number IN NUMBER,
626 p_outcome_dt IN DATE,
627 p_new_grading_schema_cd IN VARCHAR2,
628 p_new_version_number IN NUMBER,
629 p_new_grade IN VARCHAR2,
630 p_new_s_grade_crtn_mthd_tp IN VARCHAR2,
631 p_new_finalised_outcome_ind IN VARCHAR2,
632 p_new_mark IN NUMBER,
633 p_new_number_times_keyed IN NUMBER,
634 p_new_trnsltd_grdng_schema_cd IN VARCHAR2,
635 p_new_trnsltd_version_number IN NUMBER,
636 p_new_translated_grade IN VARCHAR2,
637 p_new_translated_dt IN DATE,
638 p_new_update_who IN VARCHAR2,
639 p_new_update_on IN DATE,
640 p_old_grading_schema_cd IN VARCHAR2,
641 p_old_version_number IN NUMBER,
642 p_old_grade IN VARCHAR2,
643 p_old_s_grade_crtn_mthd_tp IN VARCHAR2,
644 p_old_finalised_outcome_ind IN VARCHAR2,
645 p_old_mark IN NUMBER,
646 p_old_number_times_keyed IN NUMBER,
647 p_old_trnsltd_grdng_schema_cd IN VARCHAR2,
648 p_old_trnsltd_version_number IN NUMBER,
649 p_old_translated_grade IN VARCHAR2,
650 p_old_translated_dt IN DATE,
651 p_old_update_who IN VARCHAR2,
652 p_old_update_on IN DATE,
653 p_uoo_id IN NUMBER
654 ) IS
655 gv_other_detail VARCHAR2 (255);
656 BEGIN -- assp_ins_suao_hist
657 DECLARE
658 v_suaoh_rec igs_as_su_atmptout_h%ROWTYPE;
659 v_create_history BOOLEAN := FALSE;
660 BEGIN
661 -- Create a history for a IGS_AS_SU_STMPTOUT record.
662 -- Check if any of the non-primary key fields have been changed
663 -- and set the flag v_create_history to indicate so.
664 IF p_new_grading_schema_cd <> p_old_grading_schema_cd THEN
665 v_suaoh_rec.grading_schema_cd := p_old_grading_schema_cd;
666 v_create_history := TRUE;
667 END IF;
668 IF p_new_version_number <> p_old_version_number THEN
669 v_suaoh_rec.version_number := p_old_version_number;
670 v_create_history := TRUE;
671 END IF;
672 IF p_new_grade <> p_old_grade THEN
673 v_suaoh_rec.grade := p_old_grade;
674 v_create_history := TRUE;
675 END IF;
676 IF p_new_s_grade_crtn_mthd_tp <> p_old_s_grade_crtn_mthd_tp THEN
677 v_suaoh_rec.s_grade_creation_method_type := p_old_s_grade_crtn_mthd_tp;
678 v_create_history := TRUE;
679 END IF;
680 IF p_new_finalised_outcome_ind <> p_old_finalised_outcome_ind THEN
681 v_suaoh_rec.finalised_outcome_ind := p_old_finalised_outcome_ind;
682 v_create_history := TRUE;
683 END IF;
684 IF NVL (p_new_mark, 0) <> NVL (p_old_mark, 0) THEN
685 v_suaoh_rec.mark := p_old_mark;
686 v_create_history := TRUE;
687 END IF;
688 IF NVL (p_new_number_times_keyed, 0) <> NVL (p_old_number_times_keyed, 0) THEN
689 v_suaoh_rec.number_times_keyed := p_old_number_times_keyed;
690 v_create_history := TRUE;
691 END IF;
695 END IF;
692 IF NVL (p_new_trnsltd_grdng_schema_cd, ' ') <> NVL (p_old_trnsltd_grdng_schema_cd, ' ') THEN
693 v_suaoh_rec.translated_grading_schema_cd := p_old_trnsltd_grdng_schema_cd;
694 v_create_history := TRUE;
696 IF NVL (p_new_trnsltd_version_number, 0) <> NVL (p_old_trnsltd_version_number, 0) THEN
697 v_suaoh_rec.translated_version_number := p_old_trnsltd_version_number;
698 v_create_history := TRUE;
699 END IF;
700 IF NVL (p_new_translated_grade, ' ') <> NVL (p_old_translated_grade, ' ') THEN
701 v_suaoh_rec.translated_grade := p_old_translated_grade;
702 v_create_history := TRUE;
703 END IF;
704 IF NVL (p_new_translated_dt, igs_ge_date.igsdate ('1900/01/01')) <>
705 NVL (p_old_translated_dt, igs_ge_date.igsdate ('1900/01/01')) THEN
706 v_suaoh_rec.translated_dt := p_old_translated_dt;
707 v_create_history := TRUE;
708 END IF;
709 -- Create a history record if a column has changed value
710 IF v_create_history = TRUE THEN
711 v_suaoh_rec.person_id := p_person_id;
712 v_suaoh_rec.course_cd := p_course_cd;
713 v_suaoh_rec.unit_cd := p_unit_cd;
714 v_suaoh_rec.cal_type := p_cal_type;
715 v_suaoh_rec.ci_sequence_number := p_ci_sequence_number;
716 v_suaoh_rec.outcome_dt := p_outcome_dt;
717 v_suaoh_rec.hist_start_dt := p_old_update_on;
718 v_suaoh_rec.hist_end_dt := p_new_update_on;
719 v_suaoh_rec.hist_who := p_old_update_who;
720 v_suaoh_rec.mark_capped_flag := 'N';
721 v_suaoh_rec.show_on_academic_histry_flag := 'Y';
722 v_suaoh_rec.release_date := NULL;
723 v_suaoh_rec.manual_override_flag := 'N';
724
725 DECLARE
726 x_rowid VARCHAR2 (25);
727 l_org_id NUMBER (15);
728 BEGIN
729 --get org id
730 l_org_id := igs_ge_gen_003.get_org_id;
731 -- This is Added by DDEY as a part of Bug # 2370562
732 -- remove one second from the hist_start_dt value
733 -- when the hist_start_dt and hist_end_dt are the same
734 -- to avoid a primary key constraint from occurring
735 -- when saving the record
736 IF (v_suaoh_rec.hist_start_dt = v_suaoh_rec.hist_end_dt) THEN
737 v_suaoh_rec.hist_start_dt := v_suaoh_rec.hist_start_dt - 1 / (60 * 24 * 60);
738 END IF;
739 igs_as_su_atmptout_h_pkg.insert_row (
740 x_rowid => x_rowid,
741 x_org_id => l_org_id,
742 x_person_id => v_suaoh_rec.person_id,
743 x_course_cd => v_suaoh_rec.course_cd,
744 x_unit_cd => v_suaoh_rec.unit_cd,
745 x_cal_type => v_suaoh_rec.cal_type,
746 x_ci_sequence_number => v_suaoh_rec.ci_sequence_number,
747 x_outcome_dt => v_suaoh_rec.outcome_dt,
748 x_hist_start_dt => v_suaoh_rec.hist_start_dt,
749 x_hist_end_dt => v_suaoh_rec.hist_end_dt,
750 x_hist_who => v_suaoh_rec.hist_who,
751 x_grading_schema_cd => v_suaoh_rec.grading_schema_cd,
752 x_version_number => v_suaoh_rec.version_number,
753 x_grade => v_suaoh_rec.grade,
754 x_s_grade_creation_method_type => v_suaoh_rec.s_grade_creation_method_type,
755 x_finalised_outcome_ind => v_suaoh_rec.finalised_outcome_ind,
756 x_mark => v_suaoh_rec.mark,
757 x_number_times_keyed => v_suaoh_rec.number_times_keyed,
758 x_translated_grading_schema_cd => v_suaoh_rec.translated_grading_schema_cd,
759 x_translated_version_number => v_suaoh_rec.translated_version_number,
760 x_translated_grade => v_suaoh_rec.translated_grade,
761 x_translated_dt => v_suaoh_rec.translated_dt,
762 x_mode => 'R',
763 x_uoo_id => p_uoo_id,
764 x_mark_capped_flag => v_suaoh_rec.mark_capped_flag,
765 x_show_on_academic_histry_flag => v_suaoh_rec.show_on_academic_histry_flag,
766 x_release_date => v_suaoh_rec.release_date,
767 x_manual_override_flag => v_suaoh_rec.manual_override_flag
768 );
769 END;
770 END IF;
771 END;
772 EXCEPTION
773 WHEN OTHERS THEN
774 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
775 fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.assp_ins_suao_hist');
776 igs_ge_msg_stack.ADD;
777 app_exception.raise_exception;
778 END assp_ins_suao_hist;
779
780 PROCEDURE assp_prc_suaai_todo (
781 p_acad_perd_cal_type IN VARCHAR2,
782 p_acad_perd_sequence_number IN NUMBER,
783 p_course_cd IN VARCHAR2,
784 p_unit_cd IN VARCHAR2,
785 p_teach_perd_cal_type IN VARCHAR2,
786 p_teach_perd_sequence_number IN NUMBER,
787 p_person_id IN NUMBER,
788 p_creation_dt OUT NOCOPY DATE,
789 p_uoo_id IN NUMBER
790 ) IS
791 gv_other_detail VARCHAR2 (255);
792 gv_log_created BOOLEAN := FALSE;
796 -- for the students.
793 BEGIN -- assp_prc_suaai_todo
794 -- This routine will process all Person To Do records that are associated
795 -- with automatically maintaining the Student Unit Attempt Assessment Items
797 -- The following actions will be applied.
798 -- Status becomes ENROLLED - To Do item created that is for 'ASS_INSERT'
799 -- Will need to create default Student Unit Attempt Assessment Items for the unit.
800 -- Status altered from ENROLLED to: DISCONTINUED, UNCONFIRMED, DELETED
801 -- INVALID - To Do item created that is for 'ASS_STATUS'
802 -- All Student Unit Attempt Assessment Item records for the student become logically
803 -- deleted. (Note: Student todo creation routine (assp_ins_suaai_todo) has now
804 -- been altered such that no todo item created when altered from enrolled.
805 -- Hence, items are no longer logically deleted upon status change.)
806 -- Student alters their location or CLASS. - Todo item created that is
807 -- for 'ASS_CHANGE'
808 DECLARE
809 cst_ass_insert CONSTANT VARCHAR2 (10) := 'ASS_INSERT';
810 cst_ass_status CONSTANT VARCHAR2 (10) := 'ASS_STATUS';
811 cst_ass_change CONSTANT VARCHAR2 (10) := 'ASS_CHANGE';
812 cst_ass3212 CONSTANT VARCHAR2 (10) := 'ASS3212';
813 cst_none CONSTANT VARCHAR2 (4) := 'NONE';
814 cst_key_label CONSTANT VARCHAR2 (40) := 'MAINTAIN STUDENT UNIT ASSESSMENT ITEMS';
815 -- Warning: Altering cst_key_label will result in ASSR3212 not being
816 -- able to select exceptions. This key label was used to distinuish
817 -- between layout version when altering layout and functionality
818 -- within the report.
819 cst_error_count CONSTANT VARCHAR2 (12) := 'ERROR_COUNT|';
820 cst_warning_count CONSTANT VARCHAR2 (14) := 'WARNING_COUNT|';
821 cst_unit_stdnt_count CONSTANT VARCHAR2 (17) := 'UNIT_STDNT_COUNT|';
822 cst_information CONSTANT VARCHAR2 (13) := 'INFORMATION||';
823 cst_error_stdnt_todo CONSTANT VARCHAR2 (19) := 'ERROR|STUDENT_TODO|';
824 v_error_count NUMBER := 0;
825 v_warning_count NUMBER := 0;
826 v_delete_todo BOOLEAN;
827 v_sle_key igs_ge_s_log_entry.KEY%TYPE;
828 v_new_student BOOLEAN;
829 v_previous_student igs_pe_person.person_id%TYPE;
830 v_previous_unit igs_en_su_attempt.unit_cd%TYPE;
831 v_key igs_ge_s_log.KEY%TYPE;
832 v_message_name VARCHAR2 (30);
833 v_log_dt DATE DEFAULT NULL;
834 v_record VARCHAR2 (255);
835 v_total_count NUMBER;
836 --
837 CURSOR c_todo IS
838 SELECT st.person_id,
839 str.s_student_todo_type,
840 str.sequence_number,
841 str.reference_number,
842 str.course_cd,
843 str.unit_cd,
844 sua.version_number,
845 sua.cal_type,
846 sua.ci_sequence_number,
847 sua.location_cd,
848 sua.unit_class,
849 uc.unit_mode,
850 st.todo_dt,
851 sua.uoo_id
852 FROM igs_pe_std_todo st,
853 igs_pe_std_todo_ref str,
854 igs_en_su_attempt_all sua,
855 igs_as_unit_class uc,
856 igs_ca_inst_rel cir
857 WHERE ((NVL (p_person_id, 9999999999) = 9999999999)
858 OR (st.person_id = p_person_id)
859 )
860 AND st.logical_delete_dt IS NULL
861 AND st.todo_dt <= SYSDATE
862 AND st.s_student_todo_type IN (cst_ass_insert, cst_ass_status, cst_ass_change)
863 AND st.person_id = str.person_id
864 AND st.s_student_todo_type = str.s_student_todo_type
865 AND st.sequence_number = str.sequence_number
866 AND str.logical_delete_dt IS NULL
867 AND str.course_cd LIKE p_course_cd
868 AND str.unit_cd LIKE p_unit_cd
869 AND str.uoo_id = NVL (p_uoo_id, str.uoo_id)
870 AND ((NVL (p_teach_perd_cal_type, 'x') = 'x')
871 OR (str.cal_type = p_teach_perd_cal_type)
872 )
873 AND ((NVL (p_teach_perd_sequence_number, 0) = 0)
874 OR (str.ci_sequence_number = p_teach_perd_sequence_number)
875 )
876 AND sua.person_id = str.person_id
877 AND sua.course_cd = str.course_cd
878 AND sua.uoo_id = str.uoo_id
879 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
880 AND cir.sup_cal_type = p_acad_perd_cal_type
881 AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
882 AND cir.sub_cal_type = sua.cal_type
883 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
884 AND uc.unit_class = sua.unit_class
885 AND uc.closed_ind = 'N'
886 ORDER BY st.person_id,
887 str.unit_cd,
888 st.todo_dt;
889 --
893 CURSOR c_usc_ass (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
890 -- Get the Active Assessment Items attached to a Unit Section
891 -- Added by ddey for the bug # 2162831
892 --
894 SELECT uai.ass_id
895 FROM igs_ps_unitass_item uai
896 WHERE uai.uoo_id = cp_uoo_id
897 AND uai.logical_delete_dt IS NULL;
898 --
899 usc_ass_rec c_usc_ass%ROWTYPE;
900 --
901 BEGIN
902 --
903 gv_log_created := FALSE;
904 p_creation_dt := NULL;
905 --
906 -- Select all person_id's from todo table with logical delete date NULL
907 -- and todo date < SYSDATE and todo type in
908 -- ('ASS_INSERT', 'ASS_STATUS', 'ASS_CHANGE').
909 -- Also items must be within the specified parameters with which this process
910 -- has been called.
911 --
912 v_key := cst_key_label;
913 v_previous_student := 0;
914 v_previous_unit := cst_none;
915 FOR v_todo_rec IN c_todo LOOP
916 --
917 -- Issue a savepoint for the todo record processing.
918 --
919 SAVEPOINT sp_todo_ref;
920 --
921 -- Initialise the logging structure.
922 --
923 igs_ge_ins_sle.genp_set_log_cntr;
924 v_error_count := 0;
925 v_warning_count := 0;
926 v_delete_todo := TRUE;
927 v_sle_key := TO_CHAR (v_todo_rec.person_id)
928 || '|'
929 || v_todo_rec.course_cd
930 || '|'
931 || v_todo_rec.unit_cd
932 || '|'
933 || v_todo_rec.version_number
934 || '|'
935 || v_todo_rec.cal_type
936 || '|'
937 || v_todo_rec.ci_sequence_number
938 || '|'
939 || v_todo_rec.uoo_id;
940 --
941 -- Log the count of students processed per unit.
942 --
943 IF (v_previous_student <> v_todo_rec.person_id)
944 OR (v_previous_unit <> v_todo_rec.unit_cd) THEN
945 --
946 -- Set the previous information
947 --
948 v_previous_student := v_todo_rec.person_id;
949 v_previous_unit := v_todo_rec.unit_cd;
950 v_new_student := TRUE;
951 ELSE
952 v_new_student := FALSE;
953 END IF;
954 --
955 -- Items are no longer deleted when the status is altered from ENROLLED
956 -- (ie. no IGS_PE_STD_TODO item created). (assp_ins_suaai_todo altered).
957 -- Hence, need to perform the processing associated with a change
958 -- as it is possible that if the student unit attempt was altered in status,
959 -- then while in the altered status e.g., INVALID, the unit offering option
960 -- was changed. Hence, when becoming enrolled, need to check if any
961 -- items/patterns should be removed.
962 --
963 IF v_todo_rec.s_student_todo_type IN (cst_ass_change, cst_ass_insert)
964 AND v_delete_todo = TRUE THEN
965 --
966 -- Check if any active Assessment Items are setup at Unit Section level
967 -- If Yes, then attach the Unit Section Assessment Item Groups and
968 -- Unit Section Assessment Items to the student
969 -- If No, then attach the Unit Assessment Item Groups and Unit
970 -- Assessment Items to the student
971 --
972 OPEN c_usc_ass (v_todo_rec.uoo_id);
973 FETCH c_usc_ass INTO usc_ass_rec;
974 IF c_usc_ass%FOUND THEN
975 CLOSE c_usc_ass;
976 --
977 -- Perform a routine that will check if assessment items still apply to
978 -- the students new unit attempt or should they be logically deleted
979 -- and default assessment items assigned for the new Unit Section.
980 --
981 IF (igs_as_gen_005.assp_upd_usec_suaai_dflt (
982 v_todo_rec.person_id,
983 v_todo_rec.course_cd,
984 v_todo_rec.unit_cd,
985 v_todo_rec.version_number,
986 v_todo_rec.cal_type,
987 v_todo_rec.ci_sequence_number,
988 v_todo_rec.location_cd,
989 v_todo_rec.unit_class,
990 v_todo_rec.uoo_id,
991 cst_ass3212,
992 v_key,
993 v_sle_key,
994 v_error_count,
995 v_warning_count,
996 v_message_name
997 ) = FALSE
998 ) THEN
999 --
1000 -- A lock has occurred so the todo item has been rolled back to be
1001 -- processed at a later date. Do not delete the todo item.
1002 --
1003 v_delete_todo := FALSE;
1004 END IF;
1005 ELSE
1006 CLOSE c_usc_ass;
1007 --
1008 -- Perform a routine that will check if assessment items still apply
1009 -- to the student's new unit attempt or should they be logically
1010 -- deleted and default items assigned.
1011 --
1012 IF igs_as_gen_005.assp_upd_suaai_dflt (
1016 v_todo_rec.cal_type,
1013 v_todo_rec.person_id,
1014 v_todo_rec.course_cd,
1015 v_todo_rec.unit_cd,
1017 v_todo_rec.ci_sequence_number,
1018 v_todo_rec.version_number,
1019 v_todo_rec.location_cd,
1020 v_todo_rec.unit_class,
1021 v_todo_rec.unit_mode,
1022 cst_ass3212,
1023 v_key,
1024 v_sle_key,
1025 v_error_count,
1026 v_warning_count,
1027 v_message_name,
1028 v_todo_rec.uoo_id
1029 ) = FALSE THEN
1030 --
1031 -- A lock has occurred so the todo item has been rolled back to be
1032 -- processed at a later date. Do not delete the todo item.
1033 --
1034 v_delete_todo := FALSE;
1035 END IF;
1036 END IF;
1037 END IF;
1038 IF v_delete_todo = TRUE THEN
1039 --
1040 -- Logically delete the IGS_PE_STD_TODO_REF table and determine if it was the
1041 -- last IGS_PE_STD_TODO_REF item for the IGS_PE_STD_TODO entry. If so, then
1042 -- logically delete the IGS_PE_STD_TODO entry.
1043 -- If a lock occurs on the item, rollback the whole event for
1044 -- this todo item. (Will also need a commit so that it can be restartable.)
1045 --
1046 IF igs_ge_gen_003.genp_upd_str_lgc_del (
1047 v_todo_rec.person_id,
1048 v_todo_rec.s_student_todo_type,
1049 v_todo_rec.sequence_number,
1050 v_todo_rec.reference_number,
1051 v_message_name
1052 ) = FALSE THEN
1053 --
1054 -- Log the exception to the system log table.
1055 --
1056 igs_ge_ins_sle.genp_set_log_entry (
1057 cst_ass3212,
1058 v_key,
1059 v_sle_key,
1060 'IGS_AS_UNABLE_LOGDEL_STUD_TOD', -- Error, unable to logically delele item.
1061 cst_error_stdnt_todo
1062 );
1063 v_error_count := v_error_count + 1;
1064 --
1065 -- Roll back any processing for this todo reference item.
1066 --
1067 ROLLBACK TO sp_todo_ref;
1068 END IF;
1069 ELSE
1070 --
1071 -- Error has occurred. Roll back any processing for this todo reference item.
1072 --
1073 ROLLBACK TO sp_todo_ref;
1074 igs_ge_ins_sle.genp_set_log_entry (
1075 cst_ass3212,
1076 v_key,
1077 v_sle_key,
1078 'IGS_AS_PROCESS_SUA_ROLLEDBACK', -- Processing rolled back due to error.
1079 cst_information
1080 );
1081 END IF;
1082 igs_ge_ins_sle.genp_ins_sle (v_log_dt);
1083 v_record := cst_error_count;
1084 --
1085 -- Increment the count of errors.
1086 --
1087 igs_ge_gen_003.genp_set_sle_count (
1088 cst_ass3212,
1089 v_key,
1090 v_record,
1091 'IGS_AS_TOTAL_ERRO_COUNT', -- message number - Total errors.
1092 v_error_count, -- count increment
1093 v_log_dt,
1094 v_total_count
1095 );
1096 v_record := cst_warning_count;
1097 --
1098 -- Increment the count of errors.
1099 --
1100 igs_ge_gen_003.genp_set_sle_count (
1101 cst_ass3212,
1102 v_key,
1103 v_record,
1104 'IGS_AS_TOTAL_WRNG_COUNT', -- message number - Total errors.
1105 v_warning_count, -- count increment
1106 v_log_dt,
1107 v_total_count
1108 );
1109 IF v_new_student = TRUE THEN
1110 v_record := cst_unit_stdnt_count
1111 || v_todo_rec.unit_cd
1112 || '|'
1113 || TO_CHAR (v_todo_rec.version_number)
1114 || '|'
1115 || v_todo_rec.cal_type
1116 || '|'
1117 || TO_CHAR (v_todo_rec.ci_sequence_number)
1118 || '|'
1119 || TO_CHAR (v_todo_rec.uoo_id);
1120 --
1121 -- Increment the count of students per unit.
1122 --
1123 igs_ge_gen_003.genp_set_sle_count (
1124 cst_ass3212,
1125 v_key,
1126 v_record,
1127 'IGS_AS_TOTAL_STUD_PROCESSED', -- message number
1128 1, -- count increment
1129 v_log_dt,
1130 v_total_count
1131 );
1132 END IF;
1133 --
1134 -- Commit the processing of the associated todo reference item.
1135 --
1136 COMMIT;
1137 END LOOP;
1138 --
1139 p_creation_dt := v_log_dt;
1140 --
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 ROLLBACK TO sp_todo_ref;
1144 IF (c_todo%ISOPEN) THEN
1145 CLOSE c_todo;
1146 END IF;
1147 RAISE;
1148 END;
1149 END assp_prc_suaai_todo;
1150 --
1151 --
1152 --
1153 PROCEDURE assp_prc_uai_actn_dt (
1154 p_acad_perd_cal_type IN VARCHAR2,
1155 p_acad_perd_sequence_number IN NUMBER,
1159 p_version_number IN NUMBER,
1156 p_teach_perd_cal_type IN VARCHAR2,
1157 p_teach_perd_sequence_number IN NUMBER,
1158 p_unit_cd IN VARCHAR2,
1160 p_assessment_type IN VARCHAR2,
1161 p_ass_id IN NUMBER,
1162 p_ass_pattern_id IN NUMBER,
1163 p_creation_dt OUT NOCOPY DATE
1164 ) IS
1165 gv_other_detail VARCHAR2 (255);
1166 BEGIN -- assp_prc_uai_actn_dt
1167 --
1168 -- This routine will process all Unit Assessment Item records that are
1169 -- associated with maintaining the stdnt_unit_atmpt_ass_items for the
1170 -- students within the unit.
1171 -- This process will insert or logically delete the assessment items that
1172 -- should apply to the Student Unit Attempt where the student is enrolled
1173 -- in the unit.
1174 -- All Unit Assessment Items where the action_dt is less than the run date
1175 -- will have the action applied to students within the unit.
1176 --
1177 DECLARE
1178 cst_error_item_count CONSTANT VARCHAR2 (30) := 'ERROR_ITEM_COUNT|';
1179 cst_warn_item_count CONSTANT VARCHAR2 (30) := 'WARNING_ITEM_COUNT|';
1180 cst_unit_item_count CONSTANT VARCHAR2 (30) := 'UNIT_ITEM_COUNT|';
1181 cst_ass3213 CONSTANT VARCHAR2 (10) := 'ASS3213';
1182 cst_error CONSTANT VARCHAR2 (10) := 'ERROR';
1183 cst_item CONSTANT VARCHAR2 (10) := 'ITEM';
1184 v_clear_action_dt BOOLEAN;
1185 v_record VARCHAR2 (255) DEFAULT NULL;
1186 v_session_id igs_ge_s_log.KEY%TYPE DEFAULT NULL;
1187 v_message_name VARCHAR2 (30);
1188 v_error_count NUMBER DEFAULT 0;
1189 v_warning_count NUMBER DEFAULT 0;
1190 v_total_count NUMBER DEFAULT 0;
1191 v_creation_dt DATE DEFAULT NULL;
1192 v_key igs_ge_s_log_entry.KEY%TYPE DEFAULT 'MAINTAIN STUDENT UNIT ASSESSMENT ITEMS';
1193 v_sle_key igs_ge_s_log_entry.KEY%TYPE DEFAULT NULL;
1194 --
1195 -- Included one more select clause in the below cursor to select the
1196 -- assessment item set up at unit section level as a part of calculation
1197 -- of records -1 bug n0:2162831
1198 --
1199 CURSOR c_uai IS
1200 SELECT usai.rowid row_id,
1201 uai.unit_cd,
1202 uai.version_number,
1203 uai.cal_type,
1204 uai.ci_sequence_number,
1205 usai.uoo_id,
1206 usai.unit_section_ass_item_id ass_item_id,
1207 usai.us_ass_item_group_id group_id,
1208 usai.ass_id ass_id,
1209 usai.ass_id assessment_id,
1210 usai.sequence_number,
1211 uai.location_cd,
1212 uai.unit_class,
1213 uc.unit_mode,
1214 usai.logical_delete_dt,
1215 'USEC' record_ind,
1216 usai.midterm_mandatory_type_code,
1217 usai.midterm_weight_qty,
1218 usai.final_mandatory_type_code,
1219 usai.final_weight_qty,
1220 usai.grading_schema_cd,
1221 usai.gs_version_number
1222 FROM igs_ps_unitass_item usai,
1223 igs_as_assessmnt_itm ai,
1224 igs_ps_unit_ofr_opt uai,
1225 igs_as_unit_class uc,
1226 igs_ca_inst_rel cir
1227 WHERE usai.uoo_id = uai.uoo_id
1228 AND uai.unit_class = uc.unit_class
1229 AND uc.closed_ind = 'N'
1230 AND usai.ass_id = ai.ass_id
1231 AND (p_teach_perd_cal_type IS NULL
1232 OR uai.cal_type = p_teach_perd_cal_type
1233 )
1234 AND (p_teach_perd_sequence_number IS NULL
1235 OR uai.ci_sequence_number = p_teach_perd_sequence_number
1236 )
1237 AND uai.unit_cd LIKE p_unit_cd
1238 AND cir.sup_cal_type = p_acad_perd_cal_type
1239 AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
1240 AND cir.sub_cal_type = uai.cal_type
1241 AND cir.sub_ci_sequence_number = uai.ci_sequence_number
1242 AND (p_version_number IS NULL
1243 OR uai.version_number = p_version_number
1244 )
1245 AND (p_assessment_type IS NULL
1246 OR ai.assessment_type LIKE p_assessment_type
1247 )
1248 AND (p_ass_id IS NULL
1249 OR usai.ass_id = p_ass_id
1250 )
1251 AND usai.action_dt <= SYSDATE
1252 AND EXISTS ( SELECT 'X'
1253 FROM igs_ps_unit_ver uv,
1254 igs_ps_unit_stat us
1255 WHERE uv.unit_cd = uai.unit_cd
1256 AND uv.version_number = uai.version_number
1257 AND uv.unit_status = us.unit_status
1258 AND us.s_unit_status = 'ACTIVE')
1259 UNION ALL
1260 SELECT uai.rowid row_id,
1261 uai.unit_cd,
1262 uai.version_number,
1263 uai.cal_type,
1264 uai.ci_sequence_number,
1268 uai.ass_id,
1265 TO_NUMBER (NULL) uoo_id,
1266 uai.unit_ass_item_id ass_item_id,
1267 uai.unit_ass_item_group_id group_id,
1269 uai.ass_id assessment_id,
1270 uai.sequence_number,
1271 NULL,
1272 NULL,
1273 NULL,
1274 uai.logical_delete_dt,
1275 'UNIT' record_ind,
1276 uai.midterm_mandatory_type_code,
1277 uai.midterm_weight_qty,
1278 uai.final_mandatory_type_code,
1279 uai.final_weight_qty,
1280 uai.grading_schema_cd,
1281 uai.gs_version_number
1282 FROM igs_as_unitass_item uai,
1283 igs_as_assessmnt_itm ai,
1284 igs_ca_inst_rel cir
1285 WHERE uai.ass_id = ai.ass_id
1286 AND (p_teach_perd_cal_type IS NULL
1287 OR uai.cal_type = p_teach_perd_cal_type
1288 )
1289 AND (p_teach_perd_sequence_number IS NULL
1290 OR uai.ci_sequence_number = p_teach_perd_sequence_number
1291 )
1292 AND uai.unit_cd LIKE p_unit_cd
1293 AND cir.sup_cal_type = p_acad_perd_cal_type
1294 AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
1295 AND cir.sub_cal_type = uai.cal_type
1296 AND cir.sub_ci_sequence_number = uai.ci_sequence_number
1297 AND (p_version_number IS NULL
1298 OR uai.version_number = p_version_number
1299 )
1300 AND (p_assessment_type IS NULL
1301 OR ai.assessment_type LIKE p_assessment_type
1302 )
1303 AND (p_ass_id IS NULL
1304 OR uai.ass_id = p_ass_id
1305 )
1306 AND uai.action_dt <= SYSDATE
1307 AND EXISTS ( SELECT 'X'
1308 FROM igs_ps_unit_ver uv,
1309 igs_ps_unit_stat us
1310 WHERE uv.unit_cd = uai.unit_cd
1311 AND uv.version_number = uai.version_number
1312 AND uv.unit_status = us.unit_status
1313 AND us.s_unit_status = 'ACTIVE')
1314 ORDER BY unit_cd,
1315 version_number,
1316 cal_type,
1317 ci_sequence_number,
1318 group_id,
1319 assessment_id;
1320 --
1321 --
1322 --
1323 PROCEDURE asspl_prc_update_uai (
1324 p_unit_cd igs_as_unitass_item.unit_cd%TYPE,
1325 p_version_number igs_as_unitass_item.version_number%TYPE,
1326 p_cal_type igs_as_unitass_item.cal_type%TYPE,
1327 p_ci_sequence_number igs_as_unitass_item.ci_sequence_number%TYPE,
1328 p_ass_id igs_as_unitass_item.ass_id%TYPE,
1329 p_sequence_number igs_as_unitass_item.sequence_number%TYPE,
1330 p_session_id igs_ge_s_log.KEY%TYPE,
1331 p_log_dt IN OUT NOCOPY DATE
1332 ) IS
1333 gv_other_detail VARCHAR2 (255);
1334 BEGIN -- asspl_prc_update_uai
1335 -- Select the IGS_AS_UNITASS_ITEM table for update NOWAIT and set
1336 -- the action date to null.
1337 -- If a lock occurs, then commit the processing anyway but report on
1338 -- the exception. No need to rollback as processing completed. If the
1339 -- item and action date is processed again then no changes will occur
1340 -- but there will be processing done for nothing.
1341 DECLARE
1342 e_resource_busy EXCEPTION;
1343 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
1344 CURSOR c_uai_upd IS
1345 SELECT ROWID,
1346 uai.*
1347 FROM igs_as_unitass_item uai
1348 WHERE uai.unit_cd = p_unit_cd
1349 AND uai.version_number = p_version_number
1350 AND uai.cal_type = p_cal_type
1351 AND uai.ci_sequence_number = p_ci_sequence_number
1352 AND uai.ass_id = p_ass_id
1353 AND uai.sequence_number = p_sequence_number
1354 FOR UPDATE OF action_dt NOWAIT;
1355 v_uai_upd_rec c_uai_upd%ROWTYPE;
1356 BEGIN
1357 OPEN c_uai_upd;
1358 FETCH c_uai_upd INTO v_uai_upd_rec;
1359 IF c_uai_upd%NOTFOUND THEN
1360 CLOSE c_uai_upd;
1361 RAISE NO_DATA_FOUND;
1362 ELSE
1363 igs_as_unitass_item_pkg.update_row (
1364 x_rowid => v_uai_upd_rec.ROWID,
1365 x_unit_ass_item_id => v_uai_upd_rec.unit_ass_item_id,
1366 x_unit_cd => v_uai_upd_rec.unit_cd,
1367 x_version_number => v_uai_upd_rec.version_number,
1368 x_cal_type => v_uai_upd_rec.cal_type,
1369 x_ci_sequence_number => v_uai_upd_rec.ci_sequence_number,
1370 x_ass_id => v_uai_upd_rec.ass_id,
1371 x_sequence_number => v_uai_upd_rec.sequence_number,
1372 x_ci_start_dt => v_uai_upd_rec.ci_start_dt,
1373 x_ci_end_dt => v_uai_upd_rec.ci_end_dt,
1377 x_due_dt => v_uai_upd_rec.due_dt,
1374 x_unit_class => v_uai_upd_rec.unit_class,
1375 x_unit_mode => v_uai_upd_rec.unit_mode,
1376 x_location_cd => v_uai_upd_rec.location_cd,
1378 x_reference => v_uai_upd_rec.REFERENCE,
1379 x_dflt_item_ind => v_uai_upd_rec.dflt_item_ind,
1380 x_logical_delete_dt => v_uai_upd_rec.logical_delete_dt,
1381 x_action_dt => NULL,
1382 x_exam_cal_type => v_uai_upd_rec.exam_cal_type,
1383 x_exam_ci_sequence_number => v_uai_upd_rec.exam_ci_sequence_number,
1384 x_mode => 'R',
1385 x_grading_schema_cd => v_uai_upd_rec.grading_schema_cd,
1386 x_gs_version_number => v_uai_upd_rec.gs_version_number,
1387 x_release_date => v_uai_upd_rec.release_date,
1388 x_description => v_uai_upd_rec.description,
1389 x_unit_ass_item_group_id => v_uai_upd_rec.unit_ass_item_group_id,
1390 x_midterm_mandatory_type_code => v_uai_upd_rec.midterm_mandatory_type_code,
1391 x_midterm_weight_qty => v_uai_upd_rec.midterm_weight_qty,
1392 x_final_mandatory_type_code => v_uai_upd_rec.final_mandatory_type_code,
1393 x_final_weight_qty => v_uai_upd_rec.final_weight_qty
1394 );
1395 END IF;
1396 IF c_uai_upd%ISOPEN THEN
1397 CLOSE c_uai_upd;
1398 END IF;
1399 EXCEPTION
1400 WHEN e_resource_busy THEN
1401 v_sle_key := cst_item
1402 || '|'
1403 || TO_CHAR (p_ass_id)
1404 || '|'
1405 || NULL
1406 || '|'
1407 || NULL
1408 || '|'
1409 || p_unit_cd
1410 || '|'
1411 || TO_CHAR (p_version_number)
1412 || '|'
1413 || p_cal_type
1414 || '|'
1415 || TO_CHAR (p_ci_sequence_number);
1416 igs_ge_ins_sle.genp_set_log_entry (
1417 cst_ass3213,
1418 v_key,
1419 v_sle_key,
1420 'IGS_AS_UNABLE_CLEAR_ACTDT_UAI', -- Record locked..
1421 cst_error || '|' || cst_item || '|' || TO_CHAR (p_ass_id) || '|'
1422 );
1423 -- No need to rollback as processing completed.
1424 -- Commit the changes as all processing completed successfully except
1425 -- for the clearing of the action date. This will mean this item can
1426 -- be processed again but no changes will occur.
1427 v_error_count := v_error_count + 1;
1428 WHEN OTHERS THEN
1429 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1430 fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.asspl_prc_update_uai');
1431 igs_ge_msg_stack.ADD;
1432 app_exception.raise_exception;
1433 RAISE;
1434 END;
1435 END asspl_prc_update_uai;
1436 --
1437 -- Included the below to update action date in IGS_PS_UNITASS_ITEM in case
1438 -- of assessment item setup is present at unit section level
1439 -- as a part of calculation of records -1 bug n0:2162831
1440 --
1441 PROCEDURE asspl_prc_upd_usec_uai (
1442 p_uoo_id igs_ps_unitass_item.uoo_id%TYPE,
1443 p_ass_id igs_ps_unitass_item.ass_id%TYPE,
1444 p_sequence_number igs_ps_unitass_item.sequence_number%TYPE,
1445 p_session_id igs_ge_s_log.KEY%TYPE,
1446 p_log_dt IN OUT NOCOPY DATE
1447 ) IS
1448 gv_other_detail VARCHAR2 (255);
1449 BEGIN -- asspl_prc_upd_usec_uai
1450 --
1451 -- Select the IGS_PS_UNITASS_ITEM table for update NOWAIT and set
1452 -- the action date to null.
1453 -- If a lock occurs, then commit the processing anyway but report on
1454 -- the exception. No need to rollback as processing completed. If the
1455 -- item and action date is processed again then no changes will occur
1456 -- but there will be processing done for nothing.
1457 --
1458 DECLARE
1459 e_resource_busy EXCEPTION;
1460 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
1461 CURSOR c_uai_upd_usec IS
1462 SELECT ROWID,
1463 pai.*
1464 FROM igs_ps_unitass_item pai
1465 WHERE pai.uoo_id = p_uoo_id
1466 AND pai.ass_id = p_ass_id
1467 AND pai.sequence_number = p_sequence_number
1468 FOR UPDATE OF action_dt NOWAIT;
1469 v_uai_upd_usec_rec c_uai_upd_usec%ROWTYPE;
1470 BEGIN
1471 OPEN c_uai_upd_usec;
1472 FETCH c_uai_upd_usec INTO v_uai_upd_usec_rec;
1473 IF c_uai_upd_usec%NOTFOUND THEN
1474 CLOSE c_uai_upd_usec;
1475 RAISE NO_DATA_FOUND;
1476 ELSE
1477 igs_ps_unitass_item_pkg.update_row (
1478 x_rowid => v_uai_upd_usec_rec.ROWID,
1479 x_unit_section_ass_item_id => v_uai_upd_usec_rec.unit_section_ass_item_id,
1483 x_ci_start_dt => v_uai_upd_usec_rec.ci_start_dt,
1480 x_uoo_id => v_uai_upd_usec_rec.uoo_id,
1481 x_ass_id => v_uai_upd_usec_rec.ass_id,
1482 x_sequence_number => v_uai_upd_usec_rec.sequence_number,
1484 x_ci_end_dt => v_uai_upd_usec_rec.ci_end_dt,
1485 x_due_dt => v_uai_upd_usec_rec.due_dt,
1486 x_reference => v_uai_upd_usec_rec.REFERENCE,
1487 x_dflt_item_ind => v_uai_upd_usec_rec.dflt_item_ind,
1488 x_logical_delete_dt => v_uai_upd_usec_rec.logical_delete_dt,
1489 x_action_dt => NULL,
1490 x_exam_cal_type => v_uai_upd_usec_rec.exam_cal_type,
1491 x_exam_ci_sequence_number => v_uai_upd_usec_rec.exam_ci_sequence_number,
1492 x_mode => 'R',
1493 x_grading_schema_cd => v_uai_upd_usec_rec.grading_schema_cd,
1494 x_gs_version_number => v_uai_upd_usec_rec.gs_version_number,
1495 x_release_date => v_uai_upd_usec_rec.release_date,
1496 x_description => v_uai_upd_usec_rec.description,
1497 x_us_ass_item_group_id => v_uai_upd_usec_rec.us_ass_item_group_id,
1498 x_midterm_mandatory_type_code => v_uai_upd_usec_rec.midterm_mandatory_type_code,
1499 x_midterm_weight_qty => v_uai_upd_usec_rec.midterm_weight_qty,
1500 x_final_mandatory_type_code => v_uai_upd_usec_rec.final_mandatory_type_code,
1501 x_final_weight_qty => v_uai_upd_usec_rec.final_weight_qty
1502 );
1503 END IF;
1504 IF c_uai_upd_usec%ISOPEN THEN
1505 CLOSE c_uai_upd_usec;
1506 END IF;
1507 EXCEPTION
1508 WHEN e_resource_busy THEN
1509 v_sle_key := cst_item
1510 || '|'
1511 || TO_CHAR (p_ass_id)
1512 || '|'
1513 || NULL
1514 || '|'
1515 || NULL
1516 || '|'
1517 || p_uoo_id
1518 || '|'
1519 || NULL
1520 || '|'
1521 || NULL
1522 || '|'
1523 || TO_CHAR (p_sequence_number);
1524 igs_ge_ins_sle.genp_set_log_entry (
1525 cst_ass3213,
1526 v_key,
1527 v_sle_key,
1528 'IGS_AS_UNABLE_CLEAR_ACTDT_UAI', -- Record locked..
1529 cst_error || '|' || cst_item || '|' || TO_CHAR (p_ass_id) || '|'
1530 );
1531 --
1532 -- No need to rollback as processing completed.
1533 -- Commit the changes as all processing completed successfully except
1534 -- for the clearing of the action date. This will mean this item can
1535 -- be processed again but no changes will occur.
1536 --
1537 v_error_count := v_error_count + 1;
1538 WHEN OTHERS THEN
1539 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1540 fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.asspl_prc_upd_usec_uai');
1541 igs_ge_msg_stack.ADD;
1542 app_exception.raise_exception;
1543 RAISE;
1544 END;
1545 END asspl_prc_upd_usec_uai;
1546 --
1547 BEGIN
1548 --
1549 -- Select all assessment items within the specified parameters that have an
1550 -- action date less than or equal to the current date.
1551 --
1552 FOR v_uai_rec IN c_uai LOOP
1553 BEGIN
1554 SAVEPOINT sp_uai_actn;
1555 --
1556 -- Initialise the logging structure.
1557 --
1558 igs_ge_ins_sle.genp_set_log_cntr;
1559 v_error_count := 0;
1560 v_warning_count := 0;
1561 v_clear_action_dt := TRUE;
1562 --
1563 IF v_uai_rec.logical_delete_dt IS NOT NULL THEN
1564 --
1565 -- Perform processing to logically delete any assessment items for
1566 -- students in the unit. If locking error occurs then set a flag
1567 -- to roll back processing associated with the Unit Assessment Item
1568 -- record so that all can be processed again at a later date.
1569 -- Report on the exception.
1570 -- Logically delete the associated suaai record for the Unit Assessment Item.
1571 --
1572 UPDATE igs_as_su_atmpt_itm suaai
1573 SET suaai.logical_delete_dt = SYSDATE,
1574 suaai.last_update_date = SYSDATE,
1575 suaai.last_updated_by = fnd_global.user_id,
1576 suaai.last_update_login = fnd_global.login_id,
1577 suaai.request_id = fnd_global.conc_request_id,
1578 suaai.program_id = fnd_global.conc_program_id,
1579 suaai.program_application_id = fnd_global.prog_appl_id,
1580 suaai.program_update_date = SYSDATE
1581 WHERE suaai.uoo_id = NVL (v_uai_rec.uoo_id, suaai.uoo_id)
1582 AND suaai.cal_type = v_uai_rec.cal_type
1583 AND suaai.ci_sequence_number = v_uai_rec.ci_sequence_number
1584 AND suaai.ass_id = v_uai_rec.ass_id
1585 AND suaai.unit_cd = v_uai_rec.unit_cd
1589 WHERE suaai2.person_id = suaai.person_id
1586 AND suaai.logical_delete_dt IS NULL
1587 AND suaai.attempt_number = (SELECT MAX (suaai2.attempt_number)
1588 FROM igs_as_su_atmpt_itm suaai2
1590 AND suaai2.course_cd = suaai.course_cd
1591 AND suaai2.uoo_id = suaai.uoo_id
1592 AND suaai2.ass_id = suaai.ass_id
1593 AND (suaai2.unit_section_ass_item_id = suaai.unit_section_ass_item_id
1594 OR suaai2.unit_ass_item_id = suaai.unit_ass_item_id))
1595 AND (suaai.unit_section_ass_item_id = v_uai_rec.ass_item_id
1596 OR suaai.unit_ass_item_id = v_uai_rec.ass_item_id)
1597 AND EXISTS (
1598 SELECT 'X'
1599 FROM igs_en_su_attempt_all sua
1600 WHERE sua.person_id = suaai.person_id
1601 AND sua.course_cd = suaai.course_cd
1602 AND sua.uoo_id = suaai.uoo_id
1603 AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
1604 );
1605 ELSE -- uai.logical_delete_dt is NULL
1606 --
1607 -- Perform processing to insert/update the students assessment items.
1608 -- Will first need to verify if the assessment item exists for a student,
1609 -- that it is still valid. If not then logically delete it.
1610 -- Will need to attempt to insert the item as it may not have existed
1611 -- for the student in the first place.
1612 -- If locking error occurs then set a flag to roll back processing
1613 -- associated with the IGS_AS_UNITASS_ITEM record so that all can be
1614 -- processed again at a later date. Report on the exception.
1615 --
1616 IF NOT igs_as_gen_005.assp_mnt_suaai_uai (
1617 v_uai_rec.unit_cd,
1618 v_uai_rec.version_number,
1619 v_uai_rec.cal_type,
1620 v_uai_rec.ci_sequence_number,
1621 v_uai_rec.ass_id,
1622 v_uai_rec.location_cd,
1623 v_uai_rec.unit_class,
1624 v_uai_rec.unit_mode,
1625 cst_ass3213,
1626 v_key,
1627 v_sle_key,
1628 v_error_count,
1629 v_warning_count,
1630 v_message_name,
1631 v_uai_rec.record_ind,
1632 v_uai_rec.ass_item_id ,
1633 v_uai_rec.group_id,
1634 v_uai_rec.midterm_mandatory_type_code ,
1635 v_uai_rec.midterm_weight_qty ,
1636 v_uai_rec.final_mandatory_type_code ,
1637 v_uai_rec.final_weight_qty ,
1638 v_uai_rec.grading_schema_cd ,
1639 v_uai_rec.gs_version_number,
1640 v_uai_rec.uoo_id
1641 ) THEN
1642 -- Locking error has occurred, initialise the logging
1643 -- structure so that the exception report does not
1644 -- report on processing that will be rolled back.
1645 -- Initialise the logging structure.
1646 igs_ge_ins_sle.genp_set_log_cntr;
1647 -- Reset the error and warning counts.
1648 v_error_count := 1;
1649 v_warning_count := 0;
1650 -- Report the error for the lock as re-initialising
1651 -- logging structure has also removed the locking
1652 -- error. Processing for the unit assessment item
1653 -- is to be rolled back.
1654 igs_ge_ins_sle.genp_set_log_entry (
1655 cst_ass3213,
1656 v_key,
1657 v_sle_key,
1658 v_message_name, -- Record locked..
1659 cst_error || '|' || cst_item || '|' || TO_CHAR (v_uai_rec.ass_id) || '|'
1660 );
1661 v_clear_action_dt := FALSE;
1662 END IF;
1663 END IF; --uai.logical_delete_dt is NOT NULL
1664 --
1665 -- Included code for checking the value of record_ind to set the value
1666 -- of action date at unit section level/unit offering level as a part
1667 -- of calculation of records -1 bug n0:2162831
1668 --
1669 IF v_uai_rec.record_ind = 'USEC' THEN
1670 IF v_clear_action_dt THEN
1671 UPDATE igs_ps_unitass_item
1672 SET action_dt = NULL,
1673 last_update_date = SYSDATE,
1674 last_updated_by = fnd_global.user_id,
1675 last_update_login = fnd_global.login_id,
1676 request_id = fnd_global.conc_request_id,
1677 program_id = fnd_global.conc_program_id,
1678 program_application_id = fnd_global.prog_appl_id,
1679 program_update_date = SYSDATE
1680 WHERE rowid = v_uai_rec.row_id;
1681 ELSE
1682 -- Rollback all processing associated with this item and log
1683 -- the exception. Continue processing other items.
1684 ROLLBACK TO sp_uai_actn;
1685 -- v_sle_key gets set previously
1686 igs_ge_ins_sle.genp_set_log_entry (
1687 cst_ass3213,
1688 v_key,
1689 v_sle_key,
1693 END IF;
1690 'IGS_AS_UAI_ROLLED_BACK', -- Error, processing will be rolled back.
1691 cst_error || '|' || cst_item || '|' || TO_CHAR (v_uai_rec.ass_id) || '|'
1692 );
1694 ELSIF v_uai_rec.record_ind = 'UNIT' THEN
1695 IF v_clear_action_dt THEN
1696 UPDATE igs_as_unitass_item
1697 SET action_dt = NULL,
1698 last_update_date = SYSDATE,
1699 last_updated_by = fnd_global.user_id,
1700 last_update_login = fnd_global.login_id,
1701 request_id = fnd_global.conc_request_id,
1702 program_id = fnd_global.conc_program_id,
1703 program_application_id = fnd_global.prog_appl_id,
1704 program_update_date = SYSDATE
1705 WHERE rowid = v_uai_rec.row_id;
1706 ELSE
1707 -- Rollback all processing associated with this item and log
1708 -- the exception. Continue processing other items.
1709 ROLLBACK TO sp_uai_actn;
1710 -- v_sle_key gets set previously
1711 igs_ge_ins_sle.genp_set_log_entry (
1712 cst_ass3213,
1713 v_key,
1714 v_sle_key,
1715 'IGS_AS_UAI_ROLLED_BACK', -- Error, processing will be rolled back.
1716 cst_error || '|' || cst_item || '|' || TO_CHAR (v_uai_rec.ass_id) || '|'
1717 );
1718 END IF;
1719 END IF;
1720 -- Create any Exception records.
1721 igs_ge_ins_sle.genp_ins_sle (v_creation_dt);
1722 v_record := cst_error_item_count;
1723 -- Increment the count of errors.
1724 igs_ge_gen_003.genp_set_sle_count (
1725 cst_ass3213,
1726 v_key,
1727 v_record,
1728 'IGS_AS_TOTAL_ERRO_COUNT', -- message number - Total errors.
1729 v_error_count, -- count increment
1730 v_creation_dt,
1731 v_total_count
1732 );
1733 v_record := cst_warn_item_count;
1734 -- Increment the count of errors.
1735 igs_ge_gen_003.genp_set_sle_count (
1736 cst_ass3213,
1737 v_key,
1738 v_record,
1739 'IGS_AS_TOTAL_WRNG_COUNT', -- message number - Total errors.
1740 v_warning_count, -- count increment
1741 v_creation_dt,
1742 v_total_count
1743 );
1744 v_record := cst_unit_item_count;
1745 -- Increment the count of unit assessment items processed.
1746 igs_ge_gen_003.genp_set_sle_count (
1747 cst_ass3213,
1748 v_key,
1749 v_record,
1750 'IGS_AS_TOTAL_UNIT_ASSITEM', -- message number
1751 1, -- count increment
1752 v_creation_dt,
1753 v_total_count
1754 );
1755 -- Perform commit to save any exception logging or
1756 -- commit processing applied for each modified unit assessment item.
1757 COMMIT;
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 ROLLBACK TO sp_uai_actn;
1761 fnd_file.put_line (
1762 fnd_file.log,
1763 substrb('Error for : uoo_id=>' || v_uai_rec.uoo_id || ';' ||
1764 'ass_id=>' || v_uai_rec.ass_id || ';' ||
1765 'record_ind=>' || v_uai_rec.record_ind || ';' ||
1766 'ass_item_id=>' || v_uai_rec.ass_item_id || ';' ||
1767 'group_id=>' || v_uai_rec.group_id || ';' ||
1768 'SQL Error: ' || SQLERRM, 1, 255));
1769 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1770 fnd_log.string (
1771 fnd_log.level_exception, g_module_head || 'assp_prc_uai_actn_dt.c_uai_exception',
1772 'Error for : uoo_id=>' || v_uai_rec.uoo_id || ';' ||
1773 'ass_id=>' || v_uai_rec.ass_id || ';' ||
1774 'record_ind=>' || v_uai_rec.record_ind || ';' ||
1775 'ass_item_id=>' || v_uai_rec.ass_item_id || ';' ||
1776 'group_id=>' || v_uai_rec.group_id || ';' ||
1777 'SQL Error: ' || SQLERRM
1778 );
1779 END IF;
1780 END;
1781 END LOOP;
1782 p_creation_dt := v_creation_dt;
1783 END;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1787 fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.assp_prc_uai_actn_dt');
1788 igs_ge_msg_stack.ADD;
1789 app_exception.raise_exception;
1790 END assp_prc_uai_actn_dt;
1791 --
1792 --
1793 --
1794 PROCEDURE assp_prc_uap_actn_dt (
1795 p_acad_perd_cal_type IN VARCHAR2,
1796 p_acad_perd_sequence_number IN NUMBER,
1797 p_teach_perd_cal_type IN VARCHAR2,
1798 p_teach_perd_sequence_number IN NUMBER,
1799 p_unit_cd IN VARCHAR2,
1800 p_version_number IN NUMBER,
1801 p_ass_pattern_id IN NUMBER,
1802 p_s_log_type IN VARCHAR2,
1803 p_key IN VARCHAR2,
1804 p_creation_dt IN OUT NOCOPY DATE
1805 ) IS
1806 BEGIN
1807 --
1808 -- This procedure is obsolete as the Grade Book Enhancement obsoleted the
1809 -- Assessment Patterns functionality
1810 --
1811 p_creation_dt := NULL;
1815 errbuf OUT NOCOPY VARCHAR2,
1812 END assp_prc_uap_actn_dt;
1813
1814 PROCEDURE assp_upd_finls_outcm (
1816 retcode OUT NOCOPY NUMBER,
1817 p_assess_calendar IN VARCHAR2,
1818 p_teaching_calendar IN VARCHAR2,
1819 p_crs_grp_cd IN VARCHAR2,
1820 p_crs_cd IN VARCHAR2,
1821 p_crs_org_unt_cd IN VARCHAR2,
1822 p_crs_lctn_cd IN VARCHAR2,
1823 p_crs_attd_md IN VARCHAR2,
1824 p_unt_cd IN VARCHAR2,
1825 p_unt_org_unt_cd IN VARCHAR2,
1826 p_unt_lctn_cd IN VARCHAR2,
1827 p_u_mode IN VARCHAR2,
1828 p_u_class IN VARCHAR2,
1829 p_allow_invalid_ind IN VARCHAR2,
1830 p_org_id IN NUMBER
1831 ) IS
1832 BEGIN
1833 --
1834 retcode := 0;
1835 --
1836 -- As per 2239087, this concurrent program is obsolete and if the user
1837 -- tries to run this program then an error message should be logged into the log
1838 -- file that the concurrent program is obsolete and should not be run.
1839 --
1840 fnd_message.set_name ('IGS', 'IGS_GE_OBSOLETE_JOB');
1841 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1842 --
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 retcode := 2;
1846 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1847 igs_ge_msg_stack.conc_exception_hndl;
1848 END assp_upd_finls_outcm;
1849
1850 PROCEDURE assp_ins_suaai_todo (
1851 p_person_id IN NUMBER,
1852 p_course_cd IN VARCHAR2,
1853 p_unit_cd IN VARCHAR2,
1854 p_cal_type VARCHAR2,
1855 p_ci_sequence_number IN NUMBER,
1856 p_old_unit_attempt_status IN VARCHAR2,
1857 p_new_unit_attempt_status IN VARCHAR2,
1858 p_old_location_cd IN VARCHAR2,
1859 p_new_location_cd IN VARCHAR2,
1860 p_old_unit_class IN VARCHAR2,
1861 p_new_unit_class IN VARCHAR2,
1862 p_uoo_id IN NUMBER
1863 ) IS
1864 gv_other_detail VARCHAR2 (255);
1865 BEGIN -- assp_ins_suaai_todo
1866 -- This routine will create a IGS_PE_STD_TODO entry for students who:
1867 -- 1. have just enrolled
1868 -- 2. had their location and class details changed
1869 DECLARE
1870 cst_enrolled CONSTANT igs_en_su_attempt.unit_attempt_status%TYPE := 'ENROLLED';
1871 cst_completed CONSTANT igs_en_su_attempt.unit_attempt_status%TYPE := 'COMPLETED';
1872 cst_ass_insert CONSTANT VARCHAR2 (15) := 'ASS_INSERT';
1873 cst_ass_change CONSTANT VARCHAR2 (15) := 'ASS_CHANGE';
1874 cst_yes CONSTANT CHAR := 'Y';
1875 v_return_val NUMBER;
1876 v_s_student_todo_type VARCHAR2 (15);
1877 v_todo_flag BOOLEAN;
1878 BEGIN
1879 v_todo_flag := FALSE;
1880 -- Check to see if inserting or updating the record to an ENROLLED status.
1881 -- If so, then create a IGS_PE_STD_TODO record
1882 IF (p_new_unit_attempt_status = cst_enrolled
1883 AND NVL (p_old_unit_attempt_status, 'NULL') NOT IN (cst_enrolled, cst_completed)
1884 ) THEN
1885 v_s_student_todo_type := cst_ass_insert;
1886 v_todo_flag := TRUE;
1887 -- Check if an enrolled unit offering has altered location or class.
1888 ELSIF (p_new_location_cd <> NVL (p_old_location_cd, p_new_location_cd)
1889 OR p_new_unit_class <> NVL (p_old_unit_class, p_new_unit_class)
1890 )
1891 AND (p_new_unit_attempt_status = cst_enrolled) THEN
1892 v_s_student_todo_type := cst_ass_change;
1893 v_todo_flag := TRUE;
1894 END IF;
1895 IF (v_todo_flag = TRUE) THEN
1896 v_return_val := igs_ge_gen_003.genp_ins_stdnt_todo (p_person_id, v_s_student_todo_type, SYSDATE, cst_yes);
1897 DECLARE
1898 l_val NUMBER;
1899 lv_rowid VARCHAR2 (25);
1900 BEGIN
1901 SELECT igs_pe_std_todo_ref_rf_num_s.NEXTVAL
1902 INTO l_val
1903 FROM DUAL;
1904 igs_pe_std_todo_ref_pkg.insert_row (
1905 x_rowid => lv_rowid,
1906 x_person_id => p_person_id,
1907 x_s_student_todo_type => v_s_student_todo_type,
1908 x_sequence_number => v_return_val,
1909 x_reference_number => l_val,
1910 x_cal_type => p_cal_type,
1911 x_ci_sequence_number => p_ci_sequence_number,
1912 x_course_cd => p_course_cd,
1913 x_unit_cd => p_unit_cd,
1914 x_other_reference => NULL,
1915 x_logical_delete_dt => NULL,
1916 x_mode => 'R',
1917 x_uoo_id => p_uoo_id
1918 );
1919 END;
1920 END IF;
1921 END;
1922 EXCEPTION
1923 WHEN OTHERS THEN
1924 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1925 fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.assp_ins_suaai_todo');
1926 igs_ge_msg_stack.ADD;
1927 app_exception.raise_exception;
1928 END assp_ins_suaai_todo;
1929 END igs_as_gen_007;
[Home] [Help]