[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_FINALIZE_GRADE
Source
1 PACKAGE BODY igs_as_finalize_grade AS
2 /* $Header: IGSAS47B.pls 120.2 2006/01/31 03:10:24 swaghmar ship $ */
3
4
5 PROCEDURE finalize_process_no_commit (
6 p_uoo_id IN NUMBER,
7 p_person_id IN NUMBER,
8 p_course_cd IN VARCHAR2,
9 p_unit_cd IN VARCHAR2,
10 p_teach_cal_type IN VARCHAR2,
11 p_teach_ci_sequence_number IN NUMBER
12 ) IS
13 BEGIN -- finalize_process
14 -- This process completes three main functions namely,
15 -- Repeat processing, Translation and Finalization.
16 -- This procedure will be called as part of Workflow by Business Events
17 -- such as Grade Submission, Change of Grade Approval and by
18 -- Grade Finalization through the Student Unit Attempt Outcome Form.
19 -------------------------------------------------------------------------------
20 -- This procedure can be called in the context of Unit Section using only the
21 -- p_uoo_id parameter or in the context of a single Student Unit attempt by
22 -- leaving the uoo_id NULL and providing the other parameters.
23 -------------------------------------------------------------------------------
24 DECLARE
25 gv_other_detail VARCHAR2 (255);
26 v_repeatable_ind igs_ps_unit_ver_all.repeatable_ind%TYPE;
27 v_use_most_recent_unit_attempt igs_en_rep_process.use_most_recent_unit_attempt%TYPE;
28 v_use_best_grade_attempt igs_en_rep_process.use_best_grade_attempt%TYPE;
29 v_external_formula igs_en_rep_process.external_formula%TYPE;
30 v_new_outcome_dt DATE;
31 v_translated_grading_schema_cd igs_as_grd_sch_trn_all.to_grading_schema_cd%TYPE;
32 v_translated_version_number igs_as_grd_sch_trn_all.to_version_number%TYPE;
33 v_translated_grade igs_as_grd_sch_trn_all.to_grade%TYPE;
34 v_translated_dt DATE;
35 v_rowid VARCHAR2 (25);
36 v_best_prev_rank igs_as_grd_sch_grade.RANK%TYPE;
37 v_best_prev_ci_end_dt igs_as_su_stmptout_all.ci_end_dt%TYPE;
38
39 CURSOR c_rp IS
40 SELECT uv.repeatable_ind,
41 rp_org.use_most_recent_unit_attempt,
42 rp_org.use_best_grade_attempt,
43 rp_org.external_formula
44 FROM igs_ps_unit_ofr_opt_all uoo,
45 igs_ps_unit_ver_all uv,
46 igs_en_rep_process rp_org,
47 igs_or_inst_org_base_v hp
48 WHERE uoo.uoo_id = p_uoo_id
49 AND uoo.unit_cd = uv.unit_cd
50 AND uoo.version_number = uv.version_number
51 AND uv.owner_org_unit_cd = hp.party_number
52 AND hp.party_id = rp_org.org_unit_id
53 AND hp.inst_org_ind = 'O'
54 UNION ALL
55 SELECT uv1.repeatable_ind,
56 rp_inst.use_most_recent_unit_attempt,
57 rp_inst.use_best_grade_attempt,
58 rp_inst.external_formula
59 FROM igs_ps_unit_ofr_opt_all uoo1,
60 igs_ps_unit_ver_all uv1,
61 igs_en_rep_process rp_inst
62 WHERE uoo1.uoo_id = p_uoo_id
63 AND uoo1.unit_cd = uv1.unit_cd
64 AND uoo1.version_number = uv1.version_number
65 AND rp_inst.org_unit_id IS NULL
66 AND NOT EXISTS ( SELECT 'X'
67 FROM igs_ps_unit_ofr_opt_all uoo2,
68 igs_en_rep_process rp_org,
69 igs_or_inst_org_base_v hp
70 WHERE uoo1.uoo_id = uoo2.uoo_id
71 AND uoo2.owner_org_unit_cd = hp.party_number
72 AND hp.party_id = rp_org.org_unit_id
73 AND hp.inst_org_ind = 'O');
74
75 CURSOR c_suao_with_uoo IS
76 SELECT suao.person_id,
77 suao.course_cd,
78 suao.unit_cd,
79 suao.cal_type,
80 suao.ci_sequence_number,
81 suao.uoo_id,
82 suao.ROWID,
83 suao.outcome_dt,
84 suao.ci_start_dt,
85 suao.ci_end_dt,
86 suao.grading_schema_cd,
87 suao.version_number,
88 suao.grade,
89 suao.s_grade_creation_method_type,
90 suao.mark,
91 suao.comments,
92 gsg.repeat_grade,
93 gsg.RANK,
94 suao.incomp_deadline_date,
95 suao.incomp_grading_schema_cd,
96 suao.incomp_version_number,
97 suao.incomp_default_grade,
98 suao.incomp_default_mark,
99 suao.attribute_category,
100 suao.attribute1,
101 suao.attribute2,
102 suao.attribute3,
103 suao.attribute4,
104 suao.attribute5,
105 suao.attribute6,
106 suao.attribute7,
107 suao.attribute8,
108 suao.attribute9,
109 suao.attribute10,
110 suao.attribute11,
111 suao.attribute12,
112 suao.attribute13,
113 suao.attribute14,
114 suao.attribute15,
115 suao.attribute16,
116 suao.attribute17,
117 suao.attribute18,
118 suao.attribute19,
119 suao.attribute20,
120 suao.mark_capped_flag,
121 suao.release_date,
122 suao.manual_override_flag,
123 suao.show_on_academic_histry_flag
124 FROM igs_as_su_stmptout_all suao,
125 igs_as_grd_sch_grade gsg
126 WHERE suao.uoo_id = p_uoo_id
127 AND suao.finalised_outcome_ind = 'N'
128 AND suao.grading_period_cd = 'FINAL'
129 AND suao.grading_schema_cd = gsg.grading_schema_cd
130 AND suao.version_number = gsg.version_number
131 AND suao.grade = gsg.grade
132 AND NVL(gsg.closed_ind,'N') = 'N'
133 AND suao.outcome_dt = (SELECT MAX (suao1.outcome_dt)
134 FROM igs_as_su_stmptout_all suao1
135 WHERE suao1.person_id = suao.person_id
136 AND suao1.course_cd = suao.course_cd
137 AND suao1.uoo_id = suao.uoo_id
138 AND suao1.grading_period_cd = suao.grading_period_cd);
139
140 CURSOR c_suao_with_sua IS
141 SELECT suao.person_id,
142 suao.course_cd,
143 suao.unit_cd,
144 suao.cal_type,
145 suao.ci_sequence_number,
146 suao.uoo_id,
147 suao.ROWID,
148 suao.outcome_dt,
149 suao.ci_start_dt,
150 suao.ci_end_dt,
151 suao.grading_schema_cd,
152 suao.version_number,
153 suao.grade,
154 suao.s_grade_creation_method_type,
155 suao.mark,
156 suao.comments,
157 gsg.repeat_grade,
158 gsg.RANK,
159 suao.incomp_deadline_date,
160 suao.incomp_grading_schema_cd,
161 suao.incomp_version_number,
162 suao.incomp_default_grade,
163 suao.incomp_default_mark,
164 suao.attribute_category,
165 suao.attribute1,
166 suao.attribute2,
167 suao.attribute3,
168 suao.attribute4,
169 suao.attribute5,
170 suao.attribute6,
171 suao.attribute7,
172 suao.attribute8,
173 suao.attribute9,
174 suao.attribute10,
175 suao.attribute11,
176 suao.attribute12,
177 suao.attribute13,
178 suao.attribute14,
179 suao.attribute15,
180 suao.attribute16,
181 suao.attribute17,
182 suao.attribute18,
183 suao.attribute19,
184 suao.attribute20,
185 suao.mark_capped_flag,
186 suao.release_date,
187 suao.manual_override_flag,
188 suao.show_on_academic_histry_flag
189 FROM igs_as_su_stmptout_all suao,
190 igs_as_grd_sch_grade gsg
191 WHERE suao.person_id = p_person_id
192 AND suao.course_cd = p_course_cd
193 AND suao.uoo_id = p_uoo_id
194 AND suao.finalised_outcome_ind = 'N'
195 AND suao.grading_period_cd = 'FINAL'
196 AND suao.grading_schema_cd = gsg.grading_schema_cd
197 AND suao.version_number = gsg.version_number
198 AND suao.grade = gsg.grade
199 AND NVL(gsg.closed_ind,'N') = 'N'
200 AND suao.outcome_dt = (SELECT MAX (suao1.outcome_dt)
201 FROM igs_as_su_stmptout_all suao1
202 WHERE suao1.person_id = suao.person_id
203 AND suao1.course_cd = suao.course_cd
204 AND suao1.uoo_id = suao.uoo_id
205 AND suao1.grading_period_cd = suao.grading_period_cd);
206
207 CURSOR c_prev_suao (
208 cp_person_id igs_as_su_stmptout_all.person_id%TYPE,
209 cp_unit_cd igs_as_su_stmptout_all.unit_cd%TYPE,
210 cp_course_cd igs_as_su_stmptout_all.course_cd%TYPE,
211 cp_cal_type igs_as_su_stmptout_all.cal_type%TYPE,
212 cp_ci_sequence_number igs_as_su_stmptout_all.ci_sequence_number%TYPE
213 ) IS
214 SELECT sua.person_id,
215 sua.course_cd,
216 sua.unit_cd,
217 sua.cal_type,
218 sua.ci_sequence_number,
219 -- anilk, 22-Apr-2003, Bug# 2829262
220 sua.uoo_id,
221 suao.outcome_dt,
222 suao.ci_start_dt,
223 suao.ci_end_dt,
224 suao.grading_schema_cd,
225 suao.version_number,
226 suao.grade,
227 suao.mark,
228 suao.incomp_deadline_date,
229 suao.incomp_grading_schema_cd,
230 suao.incomp_version_number,
231 suao.incomp_default_grade,
232 suao.incomp_default_mark,
233 suao.comments,
234 gsg.repeat_grade,
235 gsg.RANK,
236 --ijeddy, bug #3027047.
237 suao.attribute_category,
238 suao.attribute1,
239 suao.attribute2,
240 suao.attribute3,
241 suao.attribute4,
242 suao.attribute5,
243 suao.attribute6,
244 suao.attribute7,
245 suao.attribute8,
246 suao.attribute9,
247 suao.attribute10,
248 suao.attribute11,
249 suao.attribute12,
250 suao.attribute13,
251 suao.attribute14,
255 suao.attribute18,
252 suao.attribute15,
253 suao.attribute16,
254 suao.attribute17,
256 suao.attribute19,
257 suao.attribute20,
258 suao.mark_capped_flag,
259 suao.release_date,
260 suao.manual_override_flag,
261 suao.show_on_academic_histry_flag
262 FROM igs_en_su_attempt sua,
263 igs_as_su_stmptout_all suao,
264 igs_as_grd_sch_grade gsg
265 WHERE sua.person_id = cp_person_id
266 AND sua.unit_cd = cp_unit_cd
267 AND (sua.course_cd <> cp_course_cd
268 OR sua.cal_type <> cp_cal_type
269 OR sua.ci_sequence_number <> cp_ci_sequence_number
270 )
271 AND suao.person_id = sua.person_id
272 AND suao.course_cd = sua.course_cd
273 AND suao.uoo_id = sua.uoo_id
274 AND suao.incomp_deadline_date IS NULL
275 AND suao.finalised_outcome_ind = 'Y'
276 AND suao.grading_period_cd = 'FINAL'
277 AND suao.grading_schema_cd = gsg.grading_schema_cd
278 AND suao.version_number = gsg.version_number
279 AND suao.grade = gsg.grade
280 AND gsg.incl_in_repeat_process_ind = 'Y'
281 AND NVL(gsg.closed_ind,'N') = 'N'
282 AND suao.outcome_dt IN (SELECT MAX (outcome_dt)
283 FROM igs_as_su_stmptout
284 WHERE person_id = suao.person_id
285 AND course_cd = suao.course_cd
286 AND uoo_id = suao.uoo_id
287 AND grading_period_cd = suao.grading_period_cd)
288 ORDER BY gsg.RANK ASC,
289 suao.ci_end_dt DESC;
290 BEGIN -- Main
291 SAVEPOINT s_before_finalize;
292 --
293 -- Get repeat configuration
294 --
295 OPEN c_rp;
296 FETCH c_rp INTO v_repeatable_ind,
297 v_use_most_recent_unit_attempt,
298 v_use_best_grade_attempt,
299 v_external_formula;
300 CLOSE c_rp;
301 IF p_person_id IS NOT NULL THEN
302 FOR v_suao_rec IN c_suao_with_sua LOOP
303 v_new_outcome_dt := NULL;
304 --
305 -- Repeat Processing
306 -- If External Formula is set call Repeat Processing User Hook and exit
307 --
308 IF v_external_formula = 'Y'
309 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
310 igs_as_user_repeat.user_repeat_process (
311 v_suao_rec.person_id,
312 v_suao_rec.course_cd,
313 v_suao_rec.unit_cd,
314 v_suao_rec.cal_type,
315 v_suao_rec.ci_sequence_number,
316 v_suao_rec.outcome_dt,
317 v_suao_rec.grading_schema_cd,
318 v_suao_rec.version_number,
319 v_suao_rec.grade,
320 -- anilk, 22-Apr-2003, Bug# 2829262
321 v_suao_rec.uoo_id
322 );
323 ELSE
324 IF v_use_most_recent_unit_attempt = 'Y'
325 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
326 --
327 -- The current outcome is considered to be the most recent outcome so
328 -- any other outcomes with valid repeat grades should be set to that
329 -- repeat grade.
330 --
331 FOR v_prev_suao_rec IN c_prev_suao (
332 v_suao_rec.person_id,
333 v_suao_rec.unit_cd,
334 v_suao_rec.course_cd,
335 v_suao_rec.cal_type,
336 v_suao_rec.ci_sequence_number
337 ) LOOP
338 IF v_prev_suao_rec.repeat_grade IS NOT NULL THEN
339 --
340 -- Perform grade translation before inserting
341 --
342 get_translated_grade (
343 v_prev_suao_rec.person_id,
344 v_prev_suao_rec.course_cd,
345 v_prev_suao_rec.unit_cd,
346 v_prev_suao_rec.cal_type,
347 v_prev_suao_rec.ci_sequence_number,
348 v_prev_suao_rec.grading_schema_cd,
349 v_prev_suao_rec.version_number,
350 v_prev_suao_rec.repeat_grade,
351 v_prev_suao_rec.mark,
352 v_translated_grading_schema_cd,
353 v_translated_version_number,
354 v_translated_grade,
355 v_translated_dt,
356 -- anilk, 22-Apr-2003, Bug# 2829262
357 v_prev_suao_rec.uoo_id
358 );
359 --
360 -- Insert new outcome with repeat grade
361 --
362 igs_as_su_stmptout_pkg.insert_row (
363 x_rowid => v_rowid,
364 x_org_id => NULL,
365 x_person_id => v_prev_suao_rec.person_id,
366 x_course_cd => v_prev_suao_rec.course_cd,
367 x_unit_cd => v_prev_suao_rec.unit_cd,
368 x_cal_type => v_prev_suao_rec.cal_type,
372 x_ci_end_dt => v_prev_suao_rec.ci_end_dt,
369 x_ci_sequence_number => v_prev_suao_rec.ci_sequence_number,
370 x_outcome_dt => SYSDATE,
371 x_ci_start_dt => v_prev_suao_rec.ci_start_dt,
373 x_grading_schema_cd => v_prev_suao_rec.grading_schema_cd,
374 x_version_number => v_prev_suao_rec.version_number,
375 x_grade => v_prev_suao_rec.repeat_grade,
376 x_attribute_category => v_prev_suao_rec.attribute_category,
377 x_attribute1 => v_prev_suao_rec.attribute1,
378 x_attribute2 => v_prev_suao_rec.attribute2,
379 x_attribute3 => v_prev_suao_rec.attribute3,
380 x_attribute4 => v_prev_suao_rec.attribute4,
381 x_attribute5 => v_prev_suao_rec.attribute5,
382 x_attribute6 => v_prev_suao_rec.attribute6,
383 x_attribute7 => v_prev_suao_rec.attribute7,
384 x_attribute8 => v_prev_suao_rec.attribute8,
385 x_attribute9 => v_prev_suao_rec.attribute9,
386 x_attribute10 => v_prev_suao_rec.attribute10,
387 x_attribute11 => v_prev_suao_rec.attribute11,
388 x_attribute12 => v_prev_suao_rec.attribute12,
389 x_attribute13 => v_prev_suao_rec.attribute13,
390 x_attribute14 => v_prev_suao_rec.attribute14,
391 x_attribute15 => v_prev_suao_rec.attribute15,
392 x_attribute16 => v_prev_suao_rec.attribute16,
393 x_attribute17 => v_prev_suao_rec.attribute17,
394 x_attribute18 => v_prev_suao_rec.attribute18,
395 x_attribute19 => v_prev_suao_rec.attribute19,
396 x_attribute20 => v_prev_suao_rec.attribute20,
397 x_s_grade_creation_method_type => 'SYSTEM',
398 x_finalised_outcome_ind => 'Y',
399 x_mark => v_prev_suao_rec.mark,
400 x_number_times_keyed => NULL,
401 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
402 x_translated_version_number => v_translated_version_number,
403 x_translated_grade => v_translated_grade,
404 x_translated_dt => v_translated_dt,
405 x_mode => 'R',
406 x_grading_period_cd => 'FINAL',
407 x_incomp_deadline_date => v_prev_suao_rec.incomp_deadline_date,
408 x_incomp_grading_schema_cd => v_prev_suao_rec.incomp_grading_schema_cd,
409 x_incomp_version_number => v_prev_suao_rec.incomp_version_number,
410 x_incomp_default_grade => v_prev_suao_rec.incomp_default_grade,
411 x_incomp_default_mark => v_prev_suao_rec.incomp_default_mark,
412 x_comments => v_prev_suao_rec.comments,
413 x_uoo_id => v_prev_suao_rec.uoo_id,
414 x_mark_capped_flag => v_prev_suao_rec.mark_capped_flag,
415 x_release_date => v_prev_suao_rec.release_date,
416 x_manual_override_flag => v_prev_suao_rec.manual_override_flag,
417 x_show_on_academic_histry_flag => v_prev_suao_rec.show_on_academic_histry_flag
418 );
419 END IF;
420 END LOOP;
421 ELSIF v_use_best_grade_attempt = 'Y'
422 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
423 --
424 -- The best outcome is considered to be the outcome with the
425 -- any other outcomes with valid repeat grades should be set to that
426 -- repeat grade.
427 --
428 FOR v_prev_suao_rec IN c_prev_suao (
429 v_suao_rec.person_id,
430 v_suao_rec.unit_cd,
431 v_suao_rec.course_cd,
432 v_suao_rec.cal_type,
433 v_suao_rec.ci_sequence_number
434 ) LOOP
435 -- Determine the best rank for a previous outcome
436 IF NVL (v_best_prev_rank, 9999) < v_prev_suao_rec.RANK THEN
437 v_best_prev_rank := v_prev_suao_rec.RANK;
438 v_best_prev_ci_end_dt := v_prev_suao_rec.ci_end_dt;
439 END IF;
440 IF v_prev_suao_rec.repeat_grade IS NOT NULL
441 AND (v_prev_suao_rec.RANK >= v_suao_rec.RANK
442 OR (v_prev_suao_rec.RANK >= v_best_prev_rank
443 AND v_prev_suao_rec.ci_end_dt < v_best_prev_ci_end_dt
444 )
445 ) THEN
446 --
447 -- Perform grade translation before inserting
448 --
449 get_translated_grade (
450 v_prev_suao_rec.person_id,
451 v_prev_suao_rec.course_cd,
452 v_prev_suao_rec.unit_cd,
453 v_prev_suao_rec.cal_type,
457 v_prev_suao_rec.repeat_grade,
454 v_prev_suao_rec.ci_sequence_number,
455 v_prev_suao_rec.grading_schema_cd,
456 v_prev_suao_rec.version_number,
458 v_prev_suao_rec.mark,
459 v_translated_grading_schema_cd,
460 v_translated_version_number,
461 v_translated_grade,
462 v_translated_dt,
463 v_prev_suao_rec.uoo_id
464 );
465 --
466 -- Insert new outcome with repeat grade
467 --
468 igs_as_su_stmptout_pkg.insert_row (
469 x_rowid => v_rowid,
470 x_org_id => NULL,
471 x_person_id => v_prev_suao_rec.person_id,
472 x_course_cd => v_prev_suao_rec.course_cd,
473 x_unit_cd => v_prev_suao_rec.unit_cd,
474 x_cal_type => v_prev_suao_rec.cal_type,
475 x_ci_sequence_number => v_prev_suao_rec.ci_sequence_number,
476 x_outcome_dt => SYSDATE,
477 x_ci_start_dt => v_prev_suao_rec.ci_start_dt,
478 x_ci_end_dt => v_prev_suao_rec.ci_end_dt,
479 x_grading_schema_cd => v_prev_suao_rec.grading_schema_cd,
480 x_version_number => v_prev_suao_rec.version_number,
481 x_grade => v_prev_suao_rec.repeat_grade,
482 x_s_grade_creation_method_type => 'SYSTEM',
483 x_finalised_outcome_ind => 'Y',
484 x_attribute_category => v_prev_suao_rec.attribute_category,
485 x_attribute1 => v_prev_suao_rec.attribute1,
486 x_attribute2 => v_prev_suao_rec.attribute2,
487 x_attribute3 => v_prev_suao_rec.attribute3,
488 x_attribute4 => v_prev_suao_rec.attribute4,
489 x_attribute5 => v_prev_suao_rec.attribute5,
490 x_attribute6 => v_prev_suao_rec.attribute6,
491 x_attribute7 => v_prev_suao_rec.attribute7,
492 x_attribute8 => v_prev_suao_rec.attribute8,
493 x_attribute9 => v_prev_suao_rec.attribute9,
494 x_attribute10 => v_prev_suao_rec.attribute10,
495 x_attribute11 => v_prev_suao_rec.attribute11,
496 x_attribute12 => v_prev_suao_rec.attribute12,
497 x_attribute13 => v_prev_suao_rec.attribute13,
498 x_attribute14 => v_prev_suao_rec.attribute14,
499 x_attribute15 => v_prev_suao_rec.attribute15,
500 x_attribute16 => v_prev_suao_rec.attribute16,
501 x_attribute17 => v_prev_suao_rec.attribute17,
502 x_attribute18 => v_prev_suao_rec.attribute18,
503 x_attribute19 => v_prev_suao_rec.attribute19,
504 x_attribute20 => v_prev_suao_rec.attribute20,
505 x_mark => v_prev_suao_rec.mark,
506 x_number_times_keyed => NULL,
507 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
508 x_translated_version_number => v_translated_version_number,
509 x_translated_grade => v_translated_grade,
510 x_translated_dt => v_translated_dt,
511 x_mode => 'R',
512 x_grading_period_cd => 'FINAL',
513 x_incomp_deadline_date => v_prev_suao_rec.incomp_deadline_date,
514 x_incomp_grading_schema_cd => v_prev_suao_rec.incomp_grading_schema_cd,
515 x_incomp_version_number => v_prev_suao_rec.incomp_version_number,
516 x_incomp_default_grade => v_prev_suao_rec.incomp_default_grade,
517 x_incomp_default_mark => v_prev_suao_rec.incomp_default_mark,
518 x_comments => v_prev_suao_rec.comments,
519 x_uoo_id => v_prev_suao_rec.uoo_id,
520 x_mark_capped_flag => v_prev_suao_rec.mark_capped_flag,
521 x_release_date => v_prev_suao_rec.release_date,
522 x_manual_override_flag => v_prev_suao_rec.manual_override_flag,
523 x_show_on_academic_histry_flag => v_prev_suao_rec.show_on_academic_histry_flag
524 );
525 ELSIF v_suao_rec.repeat_grade IS NOT NULL THEN
526 v_new_outcome_dt := SYSDATE;
527 --
528 -- Perform grade translation before inserting
529 --
530 get_translated_grade (
531 v_suao_rec.person_id,
532 v_suao_rec.course_cd,
533 v_suao_rec.unit_cd,
534 v_suao_rec.cal_type,
535 v_suao_rec.ci_sequence_number,
536 v_suao_rec.grading_schema_cd,
537 v_suao_rec.version_number,
538 v_suao_rec.repeat_grade,
539 v_suao_rec.mark,
543 v_translated_dt,
540 v_translated_grading_schema_cd,
541 v_translated_version_number,
542 v_translated_grade,
544 v_suao_rec.uoo_id
545 );
546 --
547 -- Insert new outcome with repeat grade
548 --
549 igs_as_su_stmptout_pkg.insert_row (
550 x_rowid => v_rowid,
551 x_org_id => NULL,
552 x_person_id => v_suao_rec.person_id,
553 x_course_cd => v_suao_rec.course_cd,
554 x_unit_cd => v_suao_rec.unit_cd,
555 x_cal_type => v_suao_rec.cal_type,
556 x_ci_sequence_number => v_suao_rec.ci_sequence_number,
557 x_outcome_dt => v_new_outcome_dt,
558 x_ci_start_dt => v_suao_rec.ci_start_dt,
559 x_ci_end_dt => v_suao_rec.ci_end_dt,
560 x_grading_schema_cd => v_suao_rec.grading_schema_cd,
561 x_version_number => v_suao_rec.version_number,
562 x_grade => v_suao_rec.repeat_grade,
563 x_s_grade_creation_method_type => 'SYSTEM',
564 x_finalised_outcome_ind => 'Y',
565 x_attribute_category => v_suao_rec.attribute_category,
566 x_attribute1 => v_suao_rec.attribute1,
567 x_attribute2 => v_suao_rec.attribute2,
568 x_attribute3 => v_suao_rec.attribute3,
569 x_attribute4 => v_suao_rec.attribute4,
570 x_attribute5 => v_suao_rec.attribute5,
571 x_attribute6 => v_suao_rec.attribute6,
572 x_attribute7 => v_suao_rec.attribute7,
573 x_attribute8 => v_suao_rec.attribute8,
574 x_attribute9 => v_suao_rec.attribute9,
575 x_attribute10 => v_suao_rec.attribute10,
576 x_attribute11 => v_suao_rec.attribute11,
577 x_attribute12 => v_suao_rec.attribute12,
578 x_attribute13 => v_suao_rec.attribute13,
579 x_attribute14 => v_suao_rec.attribute14,
580 x_attribute15 => v_suao_rec.attribute15,
581 x_attribute16 => v_suao_rec.attribute16,
582 x_attribute17 => v_suao_rec.attribute17,
583 x_attribute18 => v_suao_rec.attribute18,
584 x_attribute19 => v_suao_rec.attribute19,
585 x_attribute20 => v_suao_rec.attribute20,
586 x_mark => v_suao_rec.mark,
587 x_number_times_keyed => NULL,
588 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
589 x_translated_version_number => v_translated_version_number,
590 x_translated_grade => v_translated_grade,
591 x_translated_dt => v_translated_dt,
592 x_mode => 'R',
593 x_grading_period_cd => 'FINAL',
594 x_incomp_deadline_date => v_suao_rec.incomp_deadline_date,
595 x_incomp_grading_schema_cd => v_suao_rec.incomp_grading_schema_cd,
596 x_incomp_version_number => v_suao_rec.incomp_version_number,
597 x_incomp_default_grade => v_suao_rec.incomp_default_grade,
598 x_incomp_default_mark => v_suao_rec.incomp_default_mark,
599 x_comments => v_suao_rec.comments,
600 x_uoo_id => v_suao_rec.uoo_id,
601 x_mark_capped_flag => v_suao_rec.mark_capped_flag,
602 x_release_date => v_suao_rec.release_date,
603 x_manual_override_flag => v_suao_rec.manual_override_flag,
604 x_show_on_academic_histry_flag => v_suao_rec.show_on_academic_histry_flag
605 );
606 END IF;
607 END LOOP;
608 END IF;
609 END IF;
610 --
611 -- Translation and Finalisation
612 -- If a new outcome has not been inserted translate and finalise the
613 -- current outcome.
614 --
615 IF v_new_outcome_dt IS NULL THEN
616 --
617 -- Perform grade translation before updating
618 --
619 get_translated_grade (
620 v_suao_rec.person_id,
621 v_suao_rec.course_cd,
622 v_suao_rec.unit_cd,
623 v_suao_rec.cal_type,
624 v_suao_rec.ci_sequence_number,
625 v_suao_rec.grading_schema_cd,
626 v_suao_rec.version_number,
627 v_suao_rec.grade,
628 v_suao_rec.mark,
629 v_translated_grading_schema_cd,
630 v_translated_version_number,
631 v_translated_grade,
632 v_translated_dt,
633 v_suao_rec.uoo_id
637 --
634 );
635 --
636 -- Update the current outcome to finlise and set translated grade
638 igs_as_su_stmptout_pkg.update_row (
639 x_rowid => v_suao_rec.ROWID,
640 x_person_id => v_suao_rec.person_id,
641 x_course_cd => v_suao_rec.course_cd,
642 x_unit_cd => v_suao_rec.unit_cd,
643 x_cal_type => v_suao_rec.cal_type,
644 x_ci_sequence_number => v_suao_rec.ci_sequence_number,
645 x_outcome_dt => v_new_outcome_dt,
646 x_ci_start_dt => v_suao_rec.ci_start_dt,
647 x_ci_end_dt => v_suao_rec.ci_end_dt,
648 x_grading_schema_cd => v_suao_rec.grading_schema_cd,
649 x_version_number => v_suao_rec.version_number,
650 x_grade => v_suao_rec.grade,
651 x_s_grade_creation_method_type => v_suao_rec.s_grade_creation_method_type,
652 x_finalised_outcome_ind => 'Y',
653 x_mark => v_suao_rec.mark,
654 x_number_times_keyed => NULL,
655 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
656 x_translated_version_number => v_translated_version_number,
657 x_translated_grade => v_translated_grade,
658 x_translated_dt => v_translated_dt,
659 x_mode => 'R',
660 x_grading_period_cd => 'FINAL',
661 x_attribute_category => v_suao_rec.attribute_category,
662 x_attribute1 => v_suao_rec.attribute1,
663 x_attribute2 => v_suao_rec.attribute2,
664 x_attribute3 => v_suao_rec.attribute3,
665 x_attribute4 => v_suao_rec.attribute4,
666 x_attribute5 => v_suao_rec.attribute5,
667 x_attribute6 => v_suao_rec.attribute6,
668 x_attribute7 => v_suao_rec.attribute7,
669 x_attribute8 => v_suao_rec.attribute8,
670 x_attribute9 => v_suao_rec.attribute9,
671 x_attribute10 => v_suao_rec.attribute10,
672 x_attribute11 => v_suao_rec.attribute11,
673 x_attribute12 => v_suao_rec.attribute12,
674 x_attribute13 => v_suao_rec.attribute13,
675 x_attribute14 => v_suao_rec.attribute14,
676 x_attribute15 => v_suao_rec.attribute15,
677 x_attribute16 => v_suao_rec.attribute16,
678 x_attribute17 => v_suao_rec.attribute17,
679 x_attribute18 => v_suao_rec.attribute18,
680 x_attribute19 => v_suao_rec.attribute19,
681 x_attribute20 => v_suao_rec.attribute20,
682 x_incomp_deadline_date => v_suao_rec.incomp_deadline_date,
683 x_incomp_grading_schema_cd => v_suao_rec.incomp_grading_schema_cd,
684 x_incomp_version_number => v_suao_rec.incomp_version_number,
685 x_incomp_default_grade => v_suao_rec.incomp_default_grade,
686 x_incomp_default_mark => v_suao_rec.incomp_default_mark,
687 x_comments => v_suao_rec.comments,
688 x_uoo_id => v_suao_rec.uoo_id,
689 x_mark_capped_flag => v_suao_rec.mark_capped_flag,
690 x_release_date => v_suao_rec.release_date,
691 x_manual_override_flag => v_suao_rec.manual_override_flag,
692 x_show_on_academic_histry_flag => v_suao_rec.show_on_academic_histry_flag
693 );
694 END IF;
695 END LOOP;
696 ELSE -- IF p_person_id IS NOT NULL THEN
697 FOR v_suao_rec IN c_suao_with_uoo LOOP
698 v_new_outcome_dt := NULL;
699 --
700 -- Repeat Processing
701 -- If External Formula is set call Repeat Processing User Hook and exit
702 --
703 IF v_external_formula = 'Y'
704 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
705 igs_as_user_repeat.user_repeat_process (
706 v_suao_rec.person_id,
707 v_suao_rec.course_cd,
708 v_suao_rec.unit_cd,
709 v_suao_rec.cal_type,
710 v_suao_rec.ci_sequence_number,
711 v_suao_rec.outcome_dt,
712 v_suao_rec.grading_schema_cd,
713 v_suao_rec.version_number,
714 v_suao_rec.grade,
715 -- anilk, 22-Apr-2003, Bug# 2829262
716 v_suao_rec.uoo_id
717 );
718 ELSE
719 IF v_use_most_recent_unit_attempt = 'Y'
720 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
721 --
722 -- The current outcome is considered to be the most recent outcome so
723 -- any other outcomes with valid repeat grades should be set to that
724 -- repeat grade.
725 --
726 FOR v_prev_suao_rec IN c_prev_suao (
727 v_suao_rec.person_id,
728 v_suao_rec.unit_cd,
732 ) LOOP
729 v_suao_rec.course_cd,
730 v_suao_rec.cal_type,
731 v_suao_rec.ci_sequence_number
733 IF v_prev_suao_rec.repeat_grade IS NOT NULL THEN
734 --
735 -- Perform grade translation before inserting
736 --
737 get_translated_grade (
738 v_prev_suao_rec.person_id,
739 v_prev_suao_rec.course_cd,
740 v_prev_suao_rec.unit_cd,
741 v_prev_suao_rec.cal_type,
742 v_prev_suao_rec.ci_sequence_number,
743 v_prev_suao_rec.grading_schema_cd,
744 v_prev_suao_rec.version_number,
745 v_prev_suao_rec.repeat_grade,
746 v_prev_suao_rec.mark,
747 v_translated_grading_schema_cd,
748 v_translated_version_number,
749 v_translated_grade,
750 v_translated_dt,
751 -- anilk, 22-Apr-2003, Bug# 2829262
752 v_prev_suao_rec.uoo_id
753 );
754 --
755 -- Insert new outcome with repeat grade
756 --
757 igs_as_su_stmptout_pkg.insert_row (
758 x_rowid => v_rowid,
759 x_org_id => NULL,
760 x_person_id => v_prev_suao_rec.person_id,
761 x_course_cd => v_prev_suao_rec.course_cd,
762 x_unit_cd => v_prev_suao_rec.unit_cd,
763 x_cal_type => v_prev_suao_rec.cal_type,
764 x_ci_sequence_number => v_prev_suao_rec.ci_sequence_number,
765 x_outcome_dt => SYSDATE,
766 x_ci_start_dt => v_prev_suao_rec.ci_start_dt,
767 x_ci_end_dt => v_prev_suao_rec.ci_end_dt,
768 x_grading_schema_cd => v_prev_suao_rec.grading_schema_cd,
769 x_version_number => v_prev_suao_rec.version_number,
770 x_grade => v_prev_suao_rec.repeat_grade,
771 x_attribute_category => v_prev_suao_rec.attribute_category,
772 x_attribute1 => v_prev_suao_rec.attribute1,
773 x_attribute2 => v_prev_suao_rec.attribute2,
774 x_attribute3 => v_prev_suao_rec.attribute3,
775 x_attribute4 => v_prev_suao_rec.attribute4,
776 x_attribute5 => v_prev_suao_rec.attribute5,
777 x_attribute6 => v_prev_suao_rec.attribute6,
778 x_attribute7 => v_prev_suao_rec.attribute7,
779 x_attribute8 => v_prev_suao_rec.attribute8,
780 x_attribute9 => v_prev_suao_rec.attribute9,
781 x_attribute10 => v_prev_suao_rec.attribute10,
782 x_attribute11 => v_prev_suao_rec.attribute11,
783 x_attribute12 => v_prev_suao_rec.attribute12,
784 x_attribute13 => v_prev_suao_rec.attribute13,
785 x_attribute14 => v_prev_suao_rec.attribute14,
786 x_attribute15 => v_prev_suao_rec.attribute15,
787 x_attribute16 => v_prev_suao_rec.attribute16,
788 x_attribute17 => v_prev_suao_rec.attribute17,
789 x_attribute18 => v_prev_suao_rec.attribute18,
790 x_attribute19 => v_prev_suao_rec.attribute19,
791 x_attribute20 => v_prev_suao_rec.attribute20,
792 x_s_grade_creation_method_type => 'SYSTEM',
793 x_finalised_outcome_ind => 'Y',
794 x_mark => v_prev_suao_rec.mark,
795 x_number_times_keyed => NULL,
796 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
800 x_mode => 'R',
797 x_translated_version_number => v_translated_version_number,
798 x_translated_grade => v_translated_grade,
799 x_translated_dt => v_translated_dt,
801 x_grading_period_cd => 'FINAL',
802 x_incomp_deadline_date => v_prev_suao_rec.incomp_deadline_date,
803 x_incomp_grading_schema_cd => v_prev_suao_rec.incomp_grading_schema_cd,
804 x_incomp_version_number => v_prev_suao_rec.incomp_version_number,
805 x_incomp_default_grade => v_prev_suao_rec.incomp_default_grade,
806 x_incomp_default_mark => v_prev_suao_rec.incomp_default_mark,
807 x_comments => v_prev_suao_rec.comments,
808 x_uoo_id => v_prev_suao_rec.uoo_id,
809 x_mark_capped_flag => v_prev_suao_rec.mark_capped_flag,
810 x_release_date => v_prev_suao_rec.release_date,
811 x_manual_override_flag => v_prev_suao_rec.manual_override_flag,
812 x_show_on_academic_histry_flag => v_prev_suao_rec.show_on_academic_histry_flag
813 );
814 END IF;
815 END LOOP;
816 ELSIF v_use_best_grade_attempt = 'Y'
817 AND NVL (v_repeatable_ind, 'Y') = 'N' THEN
818 --
819 -- The best outcome is considered to be the outcome with the
820 -- any other outcomes with valid repeat grades should be set to that
821 -- repeat grade.
822 --
823 FOR v_prev_suao_rec IN c_prev_suao (
824 v_suao_rec.person_id,
825 v_suao_rec.unit_cd,
826 v_suao_rec.course_cd,
827 v_suao_rec.cal_type,
828 v_suao_rec.ci_sequence_number
829 ) LOOP
830 -- Determine the best rank for a previous outcome
831 IF NVL (v_best_prev_rank, 9999) < v_prev_suao_rec.RANK THEN
832 v_best_prev_rank := v_prev_suao_rec.RANK;
833 v_best_prev_ci_end_dt := v_prev_suao_rec.ci_end_dt;
834 END IF;
835 IF v_prev_suao_rec.repeat_grade IS NOT NULL
836 AND (v_prev_suao_rec.RANK >= v_suao_rec.RANK
837 OR (v_prev_suao_rec.RANK >= v_best_prev_rank
838 AND v_prev_suao_rec.ci_end_dt < v_best_prev_ci_end_dt
839 )
840 ) THEN
841 --
842 -- Perform grade translation before inserting
843 --
844 get_translated_grade (
845 v_prev_suao_rec.person_id,
846 v_prev_suao_rec.course_cd,
847 v_prev_suao_rec.unit_cd,
848 v_prev_suao_rec.cal_type,
849 v_prev_suao_rec.ci_sequence_number,
850 v_prev_suao_rec.grading_schema_cd,
851 v_prev_suao_rec.version_number,
852 v_prev_suao_rec.repeat_grade,
853 v_prev_suao_rec.mark,
854 v_translated_grading_schema_cd,
855 v_translated_version_number,
856 v_translated_grade,
857 v_translated_dt,
858 v_prev_suao_rec.uoo_id
859 );
860 --
861 -- Insert new outcome with repeat grade
862 --
863 igs_as_su_stmptout_pkg.insert_row (
864 x_rowid => v_rowid,
865 x_org_id => NULL,
866 x_person_id => v_prev_suao_rec.person_id,
867 x_course_cd => v_prev_suao_rec.course_cd,
868 x_unit_cd => v_prev_suao_rec.unit_cd,
869 x_cal_type => v_prev_suao_rec.cal_type,
870 x_ci_sequence_number => v_prev_suao_rec.ci_sequence_number,
871 x_outcome_dt => SYSDATE,
872 x_ci_start_dt => v_prev_suao_rec.ci_start_dt,
873 x_ci_end_dt => v_prev_suao_rec.ci_end_dt,
874 x_grading_schema_cd => v_prev_suao_rec.grading_schema_cd,
875 x_version_number => v_prev_suao_rec.version_number,
876 x_grade => v_prev_suao_rec.repeat_grade,
877 x_s_grade_creation_method_type => 'SYSTEM',
878 x_finalised_outcome_ind => 'Y',
879 x_attribute_category => v_prev_suao_rec.attribute_category,
880 x_attribute1 => v_prev_suao_rec.attribute1,
881 x_attribute2 => v_prev_suao_rec.attribute2,
882 x_attribute3 => v_prev_suao_rec.attribute3,
883 x_attribute4 => v_prev_suao_rec.attribute4,
884 x_attribute5 => v_prev_suao_rec.attribute5,
885 x_attribute6 => v_prev_suao_rec.attribute6,
886 x_attribute7 => v_prev_suao_rec.attribute7,
887 x_attribute8 => v_prev_suao_rec.attribute8,
888 x_attribute9 => v_prev_suao_rec.attribute9,
889 x_attribute10 => v_prev_suao_rec.attribute10,
890 x_attribute11 => v_prev_suao_rec.attribute11,
891 x_attribute12 => v_prev_suao_rec.attribute12,
895 x_attribute16 => v_prev_suao_rec.attribute16,
892 x_attribute13 => v_prev_suao_rec.attribute13,
893 x_attribute14 => v_prev_suao_rec.attribute14,
894 x_attribute15 => v_prev_suao_rec.attribute15,
896 x_attribute17 => v_prev_suao_rec.attribute17,
897 x_attribute18 => v_prev_suao_rec.attribute18,
898 x_attribute19 => v_prev_suao_rec.attribute19,
899 x_attribute20 => v_prev_suao_rec.attribute20,
900 x_mark => v_prev_suao_rec.mark,
901 x_number_times_keyed => NULL,
902 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
903 x_translated_version_number => v_translated_version_number,
904 x_translated_grade => v_translated_grade,
905 x_translated_dt => v_translated_dt,
906 x_mode => 'R',
907 x_grading_period_cd => 'FINAL',
908 x_incomp_deadline_date => v_prev_suao_rec.incomp_deadline_date,
909 x_incomp_grading_schema_cd => v_prev_suao_rec.incomp_grading_schema_cd,
910 x_incomp_version_number => v_prev_suao_rec.incomp_version_number,
911 x_incomp_default_grade => v_prev_suao_rec.incomp_default_grade,
912 x_incomp_default_mark => v_prev_suao_rec.incomp_default_mark,
913 x_comments => v_prev_suao_rec.comments,
914 x_uoo_id => v_prev_suao_rec.uoo_id,
915 x_mark_capped_flag => v_prev_suao_rec.mark_capped_flag,
916 x_release_date => v_prev_suao_rec.release_date,
917 x_manual_override_flag => v_prev_suao_rec.manual_override_flag,
918 x_show_on_academic_histry_flag => v_prev_suao_rec.show_on_academic_histry_flag
919 );
920 ELSIF v_suao_rec.repeat_grade IS NOT NULL THEN
921 v_new_outcome_dt := SYSDATE;
922 --
923 -- Perform grade translation before inserting
924 --
925 get_translated_grade (
926 v_suao_rec.person_id,
927 v_suao_rec.course_cd,
928 v_suao_rec.unit_cd,
929 v_suao_rec.cal_type,
930 v_suao_rec.ci_sequence_number,
931 v_suao_rec.grading_schema_cd,
932 v_suao_rec.version_number,
933 v_suao_rec.repeat_grade,
934 v_suao_rec.mark,
935 v_translated_grading_schema_cd,
936 v_translated_version_number,
937 v_translated_grade,
938 v_translated_dt,
939 v_suao_rec.uoo_id
940 );
941 --
942 -- Insert new outcome with repeat grade
943 --
944 igs_as_su_stmptout_pkg.insert_row (
945 x_rowid => v_rowid,
946 x_org_id => NULL,
947 x_person_id => v_suao_rec.person_id,
948 x_course_cd => v_suao_rec.course_cd,
949 x_unit_cd => v_suao_rec.unit_cd,
950 x_cal_type => v_suao_rec.cal_type,
951 x_ci_sequence_number => v_suao_rec.ci_sequence_number,
952 x_outcome_dt => v_new_outcome_dt,
953 x_ci_start_dt => v_suao_rec.ci_start_dt,
954 x_ci_end_dt => v_suao_rec.ci_end_dt,
955 x_grading_schema_cd => v_suao_rec.grading_schema_cd,
956 x_version_number => v_suao_rec.version_number,
957 x_grade => v_suao_rec.repeat_grade,
958 x_s_grade_creation_method_type => 'SYSTEM',
959 x_finalised_outcome_ind => 'Y',
960 x_attribute_category => v_suao_rec.attribute_category,
961 x_attribute1 => v_suao_rec.attribute1,
962 x_attribute2 => v_suao_rec.attribute2,
963 x_attribute3 => v_suao_rec.attribute3,
964 x_attribute4 => v_suao_rec.attribute4,
965 x_attribute5 => v_suao_rec.attribute5,
966 x_attribute6 => v_suao_rec.attribute6,
967 x_attribute7 => v_suao_rec.attribute7,
968 x_attribute8 => v_suao_rec.attribute8,
969 x_attribute9 => v_suao_rec.attribute9,
970 x_attribute10 => v_suao_rec.attribute10,
971 x_attribute11 => v_suao_rec.attribute11,
972 x_attribute12 => v_suao_rec.attribute12,
973 x_attribute13 => v_suao_rec.attribute13,
974 x_attribute14 => v_suao_rec.attribute14,
975 x_attribute15 => v_suao_rec.attribute15,
976 x_attribute16 => v_suao_rec.attribute16,
977 x_attribute17 => v_suao_rec.attribute17,
978 x_attribute18 => v_suao_rec.attribute18,
979 x_attribute19 => v_suao_rec.attribute19,
980 x_attribute20 => v_suao_rec.attribute20,
981 x_mark => v_suao_rec.mark,
985 x_translated_grade => v_translated_grade,
982 x_number_times_keyed => NULL,
983 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
984 x_translated_version_number => v_translated_version_number,
986 x_translated_dt => v_translated_dt,
987 x_mode => 'R',
988 x_grading_period_cd => 'FINAL',
989 x_incomp_deadline_date => v_suao_rec.incomp_deadline_date,
990 x_incomp_grading_schema_cd => v_suao_rec.incomp_grading_schema_cd,
991 x_incomp_version_number => v_suao_rec.incomp_version_number,
992 x_incomp_default_grade => v_suao_rec.incomp_default_grade,
993 x_incomp_default_mark => v_suao_rec.incomp_default_mark,
994 x_comments => v_suao_rec.comments,
995 x_uoo_id => v_suao_rec.uoo_id,
996 x_mark_capped_flag => v_suao_rec.mark_capped_flag,
997 x_release_date => v_suao_rec.release_date,
998 x_manual_override_flag => v_suao_rec.manual_override_flag,
999 x_show_on_academic_histry_flag => v_suao_rec.show_on_academic_histry_flag
1000 );
1001 END IF;
1002 END LOOP;
1003 END IF;
1004 END IF;
1005 --
1006 -- Translation and Finalisation
1007 -- If a new outcome has not been inserted translate and finalise the
1008 -- current outcome.
1009 --
1010 IF v_new_outcome_dt IS NULL THEN
1011 --
1012 -- Perform grade translation before updating
1013 --
1014 get_translated_grade (
1015 v_suao_rec.person_id,
1016 v_suao_rec.course_cd,
1017 v_suao_rec.unit_cd,
1018 v_suao_rec.cal_type,
1019 v_suao_rec.ci_sequence_number,
1020 v_suao_rec.grading_schema_cd,
1021 v_suao_rec.version_number,
1022 v_suao_rec.grade,
1023 v_suao_rec.mark,
1024 v_translated_grading_schema_cd,
1025 v_translated_version_number,
1026 v_translated_grade,
1027 v_translated_dt,
1028 v_suao_rec.uoo_id
1029 );
1030 --
1031 -- Update the current outcome to finlise and set translated grade
1032 --
1033 igs_as_su_stmptout_pkg.update_row (
1034 x_rowid => v_suao_rec.ROWID,
1035 x_person_id => v_suao_rec.person_id,
1036 x_course_cd => v_suao_rec.course_cd,
1037 x_unit_cd => v_suao_rec.unit_cd,
1038 x_cal_type => v_suao_rec.cal_type,
1039 x_ci_sequence_number => v_suao_rec.ci_sequence_number,
1040 x_outcome_dt => v_new_outcome_dt,
1041 x_ci_start_dt => v_suao_rec.ci_start_dt,
1042 x_ci_end_dt => v_suao_rec.ci_end_dt,
1043 x_grading_schema_cd => v_suao_rec.grading_schema_cd,
1044 x_version_number => v_suao_rec.version_number,
1045 x_grade => v_suao_rec.grade,
1046 x_s_grade_creation_method_type => v_suao_rec.s_grade_creation_method_type,
1047 x_finalised_outcome_ind => 'Y',
1048 x_mark => v_suao_rec.mark,
1049 x_number_times_keyed => NULL,
1050 x_translated_grading_schema_cd => v_translated_grading_schema_cd,
1051 x_translated_version_number => v_translated_version_number,
1052 x_translated_grade => v_translated_grade,
1053 x_translated_dt => v_translated_dt,
1054 x_mode => 'R',
1055 x_grading_period_cd => 'FINAL',
1056 x_attribute_category => v_suao_rec.attribute_category,
1057 x_attribute1 => v_suao_rec.attribute1,
1058 x_attribute2 => v_suao_rec.attribute2,
1059 x_attribute3 => v_suao_rec.attribute3,
1060 x_attribute4 => v_suao_rec.attribute4,
1061 x_attribute5 => v_suao_rec.attribute5,
1062 x_attribute6 => v_suao_rec.attribute6,
1063 x_attribute7 => v_suao_rec.attribute7,
1064 x_attribute8 => v_suao_rec.attribute8,
1065 x_attribute9 => v_suao_rec.attribute9,
1066 x_attribute10 => v_suao_rec.attribute10,
1067 x_attribute11 => v_suao_rec.attribute11,
1068 x_attribute12 => v_suao_rec.attribute12,
1069 x_attribute13 => v_suao_rec.attribute13,
1070 x_attribute14 => v_suao_rec.attribute14,
1071 x_attribute15 => v_suao_rec.attribute15,
1072 x_attribute16 => v_suao_rec.attribute16,
1073 x_attribute17 => v_suao_rec.attribute17,
1074 x_attribute18 => v_suao_rec.attribute18,
1075 x_attribute19 => v_suao_rec.attribute19,
1076 x_attribute20 => v_suao_rec.attribute20,
1077 x_incomp_deadline_date => v_suao_rec.incomp_deadline_date,
1078 x_incomp_grading_schema_cd => v_suao_rec.incomp_grading_schema_cd,
1079 x_incomp_version_number => v_suao_rec.incomp_version_number,
1080 x_incomp_default_grade => v_suao_rec.incomp_default_grade,
1084 x_mark_capped_flag => v_suao_rec.mark_capped_flag,
1081 x_incomp_default_mark => v_suao_rec.incomp_default_mark,
1082 x_comments => v_suao_rec.comments,
1083 x_uoo_id => v_suao_rec.uoo_id,
1085 x_release_date => v_suao_rec.release_date,
1086 x_manual_override_flag => v_suao_rec.manual_override_flag,
1087 x_show_on_academic_histry_flag => v_suao_rec.show_on_academic_histry_flag
1088 );
1089 END IF;
1090 END LOOP;
1091 END IF;-- IF p_person_id IS NOT NULL THEN
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 ROLLBACK TO s_before_finalize;
1095 app_exception.raise_exception;
1096 END;
1097 END finalize_process_no_commit;
1098
1099
1100 PROCEDURE finalize_process (
1101 p_uoo_id IN NUMBER,
1102 p_person_id IN NUMBER,
1103 p_course_cd IN VARCHAR2,
1104 p_unit_cd IN VARCHAR2,
1105 p_teach_cal_type IN VARCHAR2,
1106 p_teach_ci_sequence_number IN NUMBER
1107 ) IS
1108 BEGIN
1109 finalize_process_no_commit (
1110 p_uoo_id => p_uoo_id ,
1111 p_person_id => p_person_id ,
1112 p_course_cd => p_course_cd ,
1113 p_unit_cd => p_unit_cd ,
1114 p_teach_cal_type => p_teach_cal_type ,
1115 p_teach_ci_sequence_number => p_teach_ci_sequence_number
1116 ) ;
1117 commit;
1118
1119 END finalize_process;
1120
1121
1122 --
1123 --
1124 --
1125 PROCEDURE get_translated_grade (
1126 p_person_id IN NUMBER,
1127 p_course_cd IN VARCHAR2,
1128 p_unit_cd IN VARCHAR2,
1129 p_teach_cal_type IN VARCHAR2,
1130 p_teach_ci_sequence_number IN NUMBER,
1131 p_grading_schema_cd IN VARCHAR2,
1132 p_version_number IN NUMBER,
1133 p_grade IN VARCHAR2,
1134 p_mark IN NUMBER,
1135 p_translated_grading_schema_cd OUT NOCOPY VARCHAR2,
1136 p_translated_version_number OUT NOCOPY NUMBER,
1137 p_translated_grade OUT NOCOPY VARCHAR2,
1138 p_translated_dt OUT NOCOPY DATE,
1139 -- anilk, 22-Apr-2003, Bug# 2829262
1140 p_uoo_id IN NUMBER
1141 ) IS
1142 BEGIN -- get_translated_grade
1143 --
1144 -- Process to determines the translated grade, if any,
1145 -- for a given Student Unit Attempt Outcome
1146 --
1147 DECLARE
1148 v_message_name VARCHAR2 (30) DEFAULT NULL;
1149 v_acad_cal_type igs_ca_inst.cal_type%TYPE;
1150 v_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1151 v_acad_ci_start_dt igs_ca_inst.start_dt%TYPE;
1152 v_acad_ci_end_dt igs_ca_inst.end_dt%TYPE;
1153 v_alt_cd igs_ca_inst.alternate_code%TYPE;
1154 v_pop_grading_schema_cd igs_ps_ofr_pat.grading_schema_cd%TYPE;
1155 v_pop_gs_version_number igs_ps_ofr_pat.gs_version_number%TYPE;
1156 v_gsgt_to_grade igs_as_grd_sch_trn.to_grade%TYPE;
1157 v_key_allow_invalid_ind igs_as_entry_conf.key_allow_invalid_ind%TYPE;
1158 CURSOR c_sua_sca_uoo_pop (
1159 cp_acad_cal_type igs_ca_inst.cal_type%TYPE,
1160 cp_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE
1161 ) IS
1162 SELECT gs.grading_schema_cd,
1163 gs.version_number,
1164 ec.key_allow_invalid_ind
1165 FROM igs_en_su_attempt sua,
1166 igs_en_stdnt_ps_att spa,
1167 igs_ps_unit_ofr_opt uoo,
1168 igs_as_grd_schema gs,
1169 igs_as_entry_conf ec
1170 WHERE sua.person_id = p_person_id
1171 AND sua.course_cd = p_course_cd
1172 AND sua.unit_cd = p_unit_cd
1173 AND sua.cal_type = p_teach_cal_type
1174 AND sua.ci_sequence_number = p_teach_ci_sequence_number
1175 AND uoo.uoo_id = sua.uoo_id
1176 -- anilk, 22-Apr-2003, Bug# 2829262
1177 AND sua.uoo_id = p_uoo_id
1178 AND spa.person_id = sua.person_id
1179 AND spa.course_cd = sua.course_cd
1180 AND (EXISTS ( SELECT 'X'
1181 FROM igs_ps_ofr_pat pop
1182 WHERE pop.coo_id = spa.coo_id
1183 AND pop.cal_type = cp_acad_cal_type
1184 AND pop.ci_sequence_number = cp_acad_ci_sequence_number
1185 AND uoo.grading_schema_prcdnce_ind = 'N'
1186 AND pop.grading_schema_cd IS NOT NULL
1187 AND pop.gs_version_number IS NOT NULL
1188 AND sua.grading_schema_code IS NULL
1189 AND sua.gs_version_number IS NULL
1190 AND pop.grading_schema_cd = gs.grading_schema_cd
1191 AND pop.gs_version_number = gs.version_number)
1192 OR (sua.grading_schema_code = gs.grading_schema_cd
1193 AND sua.gs_version_number = gs.version_number
1194 )
1195 )
1196 AND ec.s_control_num = 1;
1197 --
1198 --
1199 --
1200 CURSOR c_gsgt (
1201 cp_pop_grading_schema_cd igs_ps_ofr_pat.grading_schema_cd%TYPE,
1202 cp_pop_gs_version_number igs_ps_ofr_pat.gs_version_number%TYPE
1203 ) IS
1204 SELECT gsgt.to_grade
1205 FROM igs_as_grd_sch_trn gsgt
1206 WHERE gsgt.grading_schema_cd = p_grading_schema_cd
1207 AND gsgt.version_number = p_version_number
1208 AND gsgt.grade = p_grade
1209 AND gsgt.to_grading_schema_cd = cp_pop_grading_schema_cd
1210 AND gsgt.to_version_number = cp_pop_gs_version_number;
1211 BEGIN
1212 --
1213 -- Translation
1214 -- Verify that the IGS_PS_UNIT_OFR_OPT.grading_schema_prcdnce_ind = 'N' and
1215 -- that IGS_PS_OFR_PAT.grading_schema_cd is not null for the student
1216 -- unit attempt and get the IGS_PS_COURSE offering pattern grading schema that will
1217 -- be used in the translation. Otherwise return NULL as no translation possible.
1218 --
1219 p_translated_dt := SYSDATE;
1220 --
1221 -- Determine the academic period for the student.
1222 --
1223 v_alt_cd := igs_en_gen_002.enrp_get_acad_alt_cd (
1224 p_teach_cal_type,
1225 p_teach_ci_sequence_number,
1226 v_acad_cal_type,
1227 v_acad_ci_sequence_number,
1228 v_acad_ci_start_dt,
1229 v_acad_ci_end_dt,
1230 v_message_name
1231 );
1232 IF v_message_name IS NOT NULL THEN
1233 --
1234 -- Unable to determine the academic period.
1235 --
1236 fnd_message.set_name ('IGS', v_message_name);
1237 igs_ge_msg_stack.ADD;
1238 app_exception.raise_exception;
1239 RETURN;
1240 END IF;
1241 OPEN c_sua_sca_uoo_pop (v_acad_cal_type, v_acad_ci_sequence_number);
1242 FETCH c_sua_sca_uoo_pop INTO v_pop_grading_schema_cd,
1243 v_pop_gs_version_number,
1244 v_key_allow_invalid_ind;
1245 IF c_sua_sca_uoo_pop%FOUND THEN
1246 CLOSE c_sua_sca_uoo_pop;
1247 OPEN c_gsgt (v_pop_grading_schema_cd, v_pop_gs_version_number);
1248 FETCH c_gsgt INTO v_gsgt_to_grade;
1249 IF c_gsgt%FOUND THEN
1250 CLOSE c_gsgt;
1251 --
1252 -- Validate the mark and grade combination are valid.
1253 --
1254 IF v_key_allow_invalid_ind = 'Y'
1255 OR igs_as_val_suao.assp_val_mark_grade (
1256 p_mark,
1257 p_grade,
1258 p_grading_schema_cd,
1259 p_version_number,
1260 v_message_name
1261 ) THEN
1262 p_translated_grading_schema_cd := v_pop_grading_schema_cd;
1263 p_translated_version_number := v_pop_gs_version_number;
1264 p_translated_grade := v_gsgt_to_grade;
1265 END IF;
1266 END IF;
1267 END IF;
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1271 fnd_message.set_token ('NAME', 'IGS_AS_FINALIZE_GRADE.get_translated_grade');
1272 igs_ge_msg_stack.ADD;
1273 app_exception.raise_exception;
1274 END;
1275 END get_translated_grade;
1276 END igs_as_finalize_grade;