1 PACKAGE BODY igs_as_derive_grade AS
2 /* $Header: IGSAS59B.pls 120.0 2005/07/05 12:58:23 appldev noship $ */
3 g_module_head CONSTANT VARCHAR2(40) := 'igs.plsql.igs_as_derive_grade.';
4 g_person_id NUMBER(15);
5 g_course_cd VARCHAR2(10);
6 g_uoo_id NUMBER(15);
7 g_mark_grade VARCHAR2(60);
8 g_grading_period_cd VARCHAR2(30);
9 g_unit_section_submitted BOOLEAN;
10 g_attempt VARCHAR2(80) := fnd_message.get_string ('IGS', 'IGS_AS_ASSESSMENT_STATUS');
11 --
12 -- Function to validate the Grading Schema Mark Range and return an error
13 -- message when the mark range is null or has gaps
14 --
15 FUNCTION validate_grading_schema (
16 p_grading_schema_cd IN VARCHAR2,
17 p_version_number IN NUMBER
18 ) RETURN VARCHAR2 IS
19 --
20 -- Cursor to get the grade and mark range for a given Grading Schema
21 --
22 CURSOR cur_grading_schema_grades (
23 cp_grading_schema_cd IN VARCHAR2,
24 cp_version_number IN NUMBER
25 ) IS
26 SELECT grade,
27 lower_mark_range,
28 upper_mark_range
29 FROM igs_as_grd_sch_grade
30 WHERE grading_schema_cd = cp_grading_schema_cd
31 AND version_number = cp_version_number
32 AND s_result_type IN ('FAIL', 'PASS')
33 AND NVL (admin_only_ind, 'N') <> 'Y'
34 AND NVL (closed_ind, 'N') <> 'Y'
35 ORDER BY lower_mark_range ASC;
36 --
37 rec_grading_schema_grades cur_grading_schema_grades%ROWTYPE;
38 prev_rec_grading_schema_grades cur_grading_schema_grades%ROWTYPE;
39 l_routine VARCHAR2(30) := 'validate_grading_schema';
40 --
41 BEGIN
42 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
43 fnd_log.string (
44 fnd_log.level_procedure,
45 g_module_head || l_routine || '.begin',
46 'Params: p_grading_schema_cd=>' || p_grading_schema_cd || ';' ||
47 'p_version_number=>' || p_version_number || ';'
48 );
49 END IF;
50 FOR rec_grading_schema_grades IN cur_grading_schema_grades (
51 p_grading_schema_cd,
52 p_version_number
53 ) LOOP
54 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
55 fnd_log.string (
56 fnd_log.level_statement,
57 g_module_head || l_routine || '.mark_range',
58 'Lower =>' || rec_grading_schema_grades.lower_mark_range ||
59 '; Upper =>' || rec_grading_schema_grades.upper_mark_range || ';'
60 );
61 END IF;
62 IF ((rec_grading_schema_grades.lower_mark_range IS NULL) OR
63 (rec_grading_schema_grades.upper_mark_range IS NULL)) THEN
64 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
65 fnd_log.string (
66 fnd_log.level_statement,
67 g_module_head || l_routine || '.mark_range_null',
68 'Mark Range is NULL'
69 );
70 END IF;
71 RETURN 'IGS_AS_US_GRD_SCH_HAS_NO_MARKS';
72 ELSIF NOT ((rec_grading_schema_grades.lower_mark_range -
73 prev_rec_grading_schema_grades.upper_mark_range > 0) AND
74 (rec_grading_schema_grades.lower_mark_range -
75 prev_rec_grading_schema_grades.upper_mark_range <= 1)) THEN
76 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
77 fnd_log.string (
78 fnd_log.level_statement,
79 g_module_head || l_routine || '.lower_upper_mark_gap',
80 'Gap Between Lower mark Of Current Range And Upper Mark Of Previous Range'
81 );
82 END IF;
83 RETURN 'IGS_AS_US_GRD_SCH_HAS_NO_MARKS';
84 END IF;
85 prev_rec_grading_schema_grades := rec_grading_schema_grades;
86 END LOOP;
87 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
88 fnd_log.string (
89 fnd_log.level_procedure,
90 g_module_head || l_routine || '.end',
91 l_routine
92 );
93 END IF;
94 RETURN NULL;
95 END validate_grading_schema;
96 --
97 -- Procedure to validate the Assessment Item and Unit Section Grading Schema's
98 -- mark range and return an error message in case mark range has null values
99 -- or gaps
100 --
101 PROCEDURE validate_ai_us_grd_mark_range (
102 p_person_id IN NUMBER,
103 p_course_cd IN VARCHAR2,
104 p_uoo_id IN NUMBER,
105 p_unit_cd IN VARCHAR2,
106 p_usec_grading_schema IN VARCHAR2,
107 p_usec_grading_schema_version IN NUMBER,
108 p_validate_ai_grd_schema IN VARCHAR2,
109 p_message_name OUT NOCOPY VARCHAR2
110 ) IS
111 --
112 -- Cursor to get the default Grading Schema for the Unit Section
113 --
114 CURSOR cur_usec_dflt_grading_schema (
115 cp_uoo_id IN NUMBER
116 ) IS
117 SELECT grading_schema_code,
118 grd_schm_version_number
119 FROM igs_ps_usec_grd_schm
120 WHERE uoo_id = cp_uoo_id
121 AND default_flag = 'Y';
122 --
123 -- Cursor to get the default Grading Schema for the Unit if default Grading
124 -- Schema for the Unit Section is not setup
125 --
126 CURSOR cur_unit_dflt_grading_schema (
127 cp_uoo_id IN NUMBER
128 ) IS
129 SELECT ugs.grading_schema_code,
130 ugs.grd_schm_version_number
131 FROM igs_ps_unit_grd_schm ugs,
132 igs_ps_unit_ofr_opt_all uoo
133 WHERE uoo.uoo_id = cp_uoo_id
134 AND ugs.unit_code = uoo.unit_cd
135 AND ugs.unit_version_number = uoo.version_number
136 AND ugs.default_flag = 'Y';
137 --
138 -- Cursor to get the Active Student Unit Attempt Assessment Items
139 --
140 CURSOR cur_sua_ai (
141 cp_person_id IN NUMBER,
142 cp_course_cd IN VARCHAR2,
143 cp_uoo_id IN NUMBER
144 ) IS
145 SELECT DISTINCT grading_schema_cd,
146 gs_version_number
147 FROM igs_as_su_atmpt_itm
148 WHERE person_id = cp_person_id
149 AND course_cd = cp_course_cd
150 AND uoo_id = cp_uoo_id
151 AND logical_delete_dt IS NULL
152 AND grading_schema_cd IS NOT NULL;
153 --
154 -- Get the Assessment ID and Reference and return them to the calling program
155 --
156 CURSOR cur_sua_ai_failing (
157 cp_person_id IN NUMBER,
158 cp_course_cd IN VARCHAR2,
159 cp_uoo_id IN NUMBER,
160 cp_grading_schema_cd IN VARCHAR2,
161 cp_gs_version_number IN NUMBER
162 ) IS
163 SELECT suai.ass_id ass_id,
164 suai.unit_ass_item_id,
165 suai.unit_section_ass_item_id
166 FROM igs_as_su_atmpt_itm suai
167 WHERE suai.person_id = cp_person_id
168 AND suai.course_cd = cp_course_cd
169 AND suai.uoo_id = cp_uoo_id
170 AND suai.grading_schema_cd = cp_grading_schema_cd
171 AND suai.gs_version_number = cp_gs_version_number;
172 --
173 -- Get Unit Assessment Item Reference
174 --
175 CURSOR cur_uai_reference (
176 cp_unit_ass_item_id IN NUMBER
177 ) IS
178 SELECT uai.reference
179 FROM igs_as_unitass_item_all uai
180 WHERE uai.unit_ass_item_id = cp_unit_ass_item_id;
181 --
182 -- Get Unit Section Assessment Item Reference
183 --
184 CURSOR cur_usai_reference (
185 cp_unit_section_ass_item_id IN NUMBER
186 ) IS
187 SELECT usai.reference
188 FROM igs_ps_unitass_item usai
189 WHERE usai.unit_section_ass_item_id = cp_unit_section_ass_item_id;
190 --
191 rec_usec_dflt_grading_schema cur_usec_dflt_grading_schema%ROWTYPE;
192 l_routine CONSTANT VARCHAR2(30) := 'validate_ai_us_grd_mark_range';
193 rec_sua_ai_failing cur_sua_ai_failing%ROWTYPE;
194 rec_uai_reference cur_uai_reference%ROWTYPE;
195 rec_usai_reference cur_usai_reference%ROWTYPE;
196 --
197 BEGIN
198 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
199 fnd_log.string (
200 fnd_log.level_procedure,
201 g_module_head || l_routine || '.begin',
202 'Params: p_person_id=>' || p_person_id || ';' ||
203 'p_course_cd=>' || p_course_cd || ';' ||
204 'p_uoo_id=>' || p_uoo_id || ';' ||
205 'p_unit_cd=>' || p_unit_cd || ';' ||
206 'p_usec_grading_schema=>' || p_usec_grading_schema || ';' ||
207 'p_usec_grading_schema_version=>' || p_usec_grading_schema_version || ';' ||
208 'p_validate_ai_grd_schema=>' || p_validate_ai_grd_schema || ';'
209 );
210 END IF;
211 --
212 -- Identify the default Grading Schema for the Unit Section
213 --
214 IF (p_usec_grading_schema IS NULL) THEN
215 OPEN cur_usec_dflt_grading_schema (p_uoo_id);
216 FETCH cur_usec_dflt_grading_schema INTO rec_usec_dflt_grading_schema;
217 IF (cur_usec_dflt_grading_schema%NOTFOUND) THEN
218 CLOSE cur_usec_dflt_grading_schema;
219 OPEN cur_unit_dflt_grading_schema (p_uoo_id);
220 FETCH cur_unit_dflt_grading_schema INTO rec_usec_dflt_grading_schema;
221 CLOSE cur_unit_dflt_grading_schema;
222 ELSE
223 CLOSE cur_usec_dflt_grading_schema;
224 END IF;
225 ELSE
226 rec_usec_dflt_grading_schema.grading_schema_code := p_usec_grading_schema;
227 rec_usec_dflt_grading_schema.grd_schm_version_number := p_usec_grading_schema_version;
228 END IF;
229 --
230 -- Check that the mark ranges for the default Unit Section Grading Schema
231 -- are NOT NULL. And also check that there is no gap of marks in the Grading
232 -- Schema.
233 --
234 p_message_name := validate_grading_schema (
235 rec_usec_dflt_grading_schema.grading_schema_code,
236 rec_usec_dflt_grading_schema.grd_schm_version_number
237 );
238 IF (p_message_name IS NOT NULL) THEN
239 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
240 fnd_log.string (
241 fnd_log.level_statement,
242 g_module_head || l_routine || '.dflt_grd_sch_for_uoo_id',
243 'Default Unit Section Grading Schema has gaps/invalid range'
244 );
245 END IF;
246 RETURN;
247 END IF;
248 --
249 -- For all the grading schemas set for the Assessment Items attached to the
250 -- Student Unit Attempt check if the mark ranges are NOT NULL. And also
251 -- check that there is no gap of marks in the Grading Schema.
252 --
253 IF (p_validate_ai_grd_schema = 'Y') THEN
254 FOR rec_sua_ai IN cur_sua_ai (
255 p_person_id,
256 p_course_cd,
257 p_uoo_id
258 ) LOOP
259 p_message_name := validate_grading_schema (
260 rec_sua_ai.grading_schema_cd,
261 rec_sua_ai.gs_version_number
262 );
263 IF (p_message_name IS NOT NULL) THEN
264 OPEN cur_sua_ai_failing (
265 p_person_id,
266 p_course_cd,
267 p_uoo_id,
268 rec_sua_ai.grading_schema_cd,
269 rec_sua_ai.gs_version_number
270 );
271 FETCH cur_sua_ai_failing INTO rec_sua_ai_failing;
272 CLOSE cur_sua_ai_failing;
273 IF (rec_sua_ai_failing.unit_ass_item_id IS NOT NULL) THEN
274 OPEN cur_uai_reference (rec_sua_ai_failing.unit_ass_item_id);
275 FETCH cur_uai_reference INTO rec_uai_reference;
276 CLOSE cur_uai_reference;
277 p_message_name := 'IGS_AS_AI_GRD_SCH_HAS_NO_MARKS::' ||
278 rec_sua_ai_failing.ass_id || '^^' ||
279 rec_uai_reference.reference;
280 ELSE
281 OPEN cur_usai_reference (rec_sua_ai_failing.unit_section_ass_item_id);
282 FETCH cur_usai_reference INTO rec_usai_reference;
283 CLOSE cur_usai_reference;
284 p_message_name := 'IGS_AS_AI_GRD_SCH_HAS_NO_MARKS::' ||
285 rec_sua_ai_failing.ass_id || '^^' ||
286 rec_usai_reference.reference;
287 END IF;
288 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
289 fnd_log.string (
290 fnd_log.level_statement,
291 g_module_head || l_routine || '.suaai_grd_sch',
292 'Student Unit Attempt Assessment Item Grading Schema has gaps/invalid range'
293 );
294 END IF;
295 RETURN;
296 END IF;
297 END LOOP;
298 END IF;
299 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
300 fnd_log.string (
301 fnd_log.level_procedure,
302 g_module_head || l_routine || '.end',
303 l_routine
304 );
305 END IF;
306 END validate_ai_us_grd_mark_range;
307 --
308 -- Program Unit to derive the Mark and Grade for a Student Unit Attempt from
309 -- Student Unit Attempt Assessment Item Outcomes
310 -- The return value of this function is a concatenation of Mark and Grade
311 -- separated by ::
312 --
313 FUNCTION derive_suao_mark_grade (
314 p_person_id IN NUMBER,
315 p_course_cd IN VARCHAR2,
316 p_uoo_id IN NUMBER,
317 p_grading_period_cd IN VARCHAR2
318 ) RETURN VARCHAR2 IS
319 --
320 -- Cursor to get the Student Unit Attempt Assessment Items' information
321 --
322 CURSOR cur_suaaig (
323 cp_person_id IN NUMBER,
324 cp_course_cd IN VARCHAR2,
325 cp_uoo_id IN NUMBER,
326 cp_grading_period_cd IN VARCHAR2
327 ) IS
328 SELECT suag.group_name,
329 DECODE (cp_grading_period_cd,
330 'FINAL', suag.final_formula_code,
331 'MIDTERM', suag.midterm_formula_code) group_formula_code,
332 DECODE (cp_grading_period_cd,
333 'FINAL', suag.final_formula_qty,
334 'MIDTERM', suag.midterm_formula_qty) group_formula_qty,
335 DECODE (cp_grading_period_cd,
336 'FINAL', suag.final_weight_qty,
337 'MIDTERM', suag.midterm_weight_qty) group_weight_qty,
338 suai.ass_id,
339 suai.grading_schema_cd,
340 suai.gs_version_number,
341 DECODE (cp_grading_period_cd,
342 'FINAL', suai.final_mandatory_type_code,
343 'MIDTERM', suai.midterm_mandatory_type_code) mandatory_type_code,
344 DECODE (cp_grading_period_cd,
345 'FINAL', suai.final_weight_qty,
346 'MIDTERM', suai.midterm_weight_qty) ai_weight_qty,
347 NVL (suai.waived_flag, 'N') waived_flag,
348 suai.mark,
349 (suai.mark - gslu.lower_limit) *
350 (100 / (gslu.upper_limit - gslu.lower_limit)) common_base_mark
351 FROM igs_as_sua_ai_group suag,
352 igs_as_su_atmpt_itm suai,
353 (SELECT grading_schema_cd,
354 version_number,
355 MIN (lower_mark_range) lower_limit,
356 MAX (upper_mark_range) upper_limit
357 FROM igs_as_grd_sch_grade
358 GROUP BY grading_schema_cd,
359 version_number) gslu
360 WHERE suag.person_id = cp_person_id
361 AND suag.course_cd = cp_course_cd
362 AND suag.uoo_id = cp_uoo_id
363 AND suag.logical_delete_date IS NULL
364 AND suai.logical_delete_dt IS NULL
365 AND DECODE (cp_grading_period_cd,
366 'FINAL', suag.final_formula_code,
367 'MIDTERM', suag.midterm_formula_code) IS NOT NULL
368 AND DECODE (cp_grading_period_cd,
369 'FINAL', suag.final_weight_qty,
370 'MIDTERM', suag.midterm_weight_qty) > 0
371 AND DECODE (cp_grading_period_cd,
372 'FINAL', suai.final_mandatory_type_code,
373 'MIDTERM', suai.midterm_mandatory_type_code) IS NOT NULL
374 AND DECODE (suai.waived_flag,
375 'Y', 1,
376 NVL (DECODE (cp_grading_period_cd,
377 'FINAL', suai.final_weight_qty,
378 'MIDTERM', suai.midterm_weight_qty), 0)) > 0
379 AND suag.sua_ass_item_group_id = suai.sua_ass_item_group_id
380 AND suai.grading_schema_cd = gslu.grading_schema_cd (+)
381 AND suai.gs_version_number = gslu.version_number (+)
382 ORDER BY suag.group_name ASC, common_base_mark DESC;
383 --
384 -- Cursor to get the Default Unit Section Grading Schema details
385 --
386 CURSOR cur_usec_grd_sch (
387 cp_uoo_id IN NUMBER
388 ) IS
389 SELECT usgs.grading_schema_code,
390 usgs.grd_schm_version_number,
391 gslu.lower_limit,
392 gslu.upper_limit
393 FROM igs_ps_usec_grd_schm usgs,
394 (SELECT grading_schema_cd,
395 version_number,
396 MIN (lower_mark_range) lower_limit,
397 MAX (upper_mark_range) upper_limit
398 FROM igs_as_grd_sch_grade
399 GROUP BY grading_schema_cd,
400 version_number) gslu
401 WHERE usgs.uoo_id = cp_uoo_id
402 AND usgs.default_flag = 'Y'
403 AND usgs.grading_schema_code = gslu.grading_schema_cd
404 AND usgs.grd_schm_version_number = gslu.version_number;
405 --
406 -- Cursor to get the Default Unit Grading Schema details if Unit Section
407 -- Grading Schemas are not setup
408 --
409 CURSOR cur_unit_grd_sch (
410 cp_uoo_id IN NUMBER
411 ) IS
412 SELECT ugs.grading_schema_code,
413 ugs.grd_schm_version_number,
414 gslu.lower_limit,
415 gslu.upper_limit
416 FROM igs_ps_unit_grd_schm ugs,
417 igs_ps_unit_ofr_opt_all uoo,
418 (SELECT grading_schema_cd,
419 version_number,
420 MIN (lower_mark_range) lower_limit,
421 MAX (upper_mark_range) upper_limit
422 FROM igs_as_grd_sch_grade
423 GROUP BY grading_schema_cd,
424 version_number) gslu
425 WHERE uoo.uoo_id = cp_uoo_id
426 AND ugs.unit_code = uoo.unit_cd
427 AND ugs.unit_version_number = uoo.version_number
428 AND ugs.default_flag = 'Y'
429 AND ugs.grading_schema_code = gslu.grading_schema_cd
430 AND ugs.grd_schm_version_number = gslu.version_number;
431 --
432 -- Cursor to get the grade for a given mark from a given Grading Schema
433 --
434 CURSOR cur_grade (
435 cp_grading_schema_code IN VARCHAR2,
436 cp_grading_schema_version IN NUMBER,
437 cp_mark IN NUMBER
438 ) IS
439 SELECT grade,
440 s_result_type
441 FROM igs_as_grd_sch_grade
442 WHERE grading_schema_cd = cp_grading_schema_code
443 AND version_number = cp_grading_schema_version
444 AND cp_mark BETWEEN lower_mark_range AND upper_mark_range;
445 --
446 --
447 --
448 CURSOR cur_entry_conf IS
449 SELECT key_mark_entry_dec_points
450 FROM igs_as_entry_conf
451 WHERE s_control_num = 1;
452 --
453 rec_entry_conf cur_entry_conf%ROWTYPE;
454 l_format_mask VARCHAR2(9);
455 TYPE suaaio_table_type IS TABLE OF cur_suaaig%ROWTYPE INDEX BY BINARY_INTEGER;
456 suaaio_table suaaio_table_type;
457 TYPE rec_group_marks_weights_type IS RECORD (
458 group_name VARCHAR2(30),
459 mark NUMBER,
460 group_weight_qty NUMBER
461 );
462 TYPE suaaig_table_type IS TABLE OF rec_group_marks_weights_type INDEX BY BINARY_INTEGER;
463 suaaig_table suaaig_table_type;
464 rec_usec_grd_sch cur_usec_grd_sch%ROWTYPE;
465 rec_grade cur_grade%ROWTYPE;
466 aio_table_index NUMBER := 1;
467 aig_table_index NUMBER := 0;
468 v_group_best_atleast_n NUMBER := 0;
469 v_previous_group_name VARCHAR2(30) := '`';
470 v_previous_group_formula NUMBER;
471 v_previous_group_formula_cd VARCHAR2(30) := '`';
472 l_routine VARCHAR2(30) := 'derive_suao_mark_grade';
473 v_suao_raw_mark NUMBER;
474 v_suao_mark NUMBER;
475 v_sum_of_all_the_weights NUMBER := 0;
476 v_mark_weight_products NUMBER := 0;
477 v_mandatory_pass_items_passed BOOLEAN := TRUE;
478 --
479 BEGIN
480 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
481 fnd_log.string (
482 fnd_log.level_procedure,
483 g_module_head || l_routine || '.begin',
484 'Params: p_person_id=>' || p_person_id || ';' ||
485 'p_course_cd=>' || p_course_cd || ';' ||
486 'p_uoo_id=>' || p_uoo_id || ';' ||
487 'p_grading_period_cd=>' || p_grading_period_cd || ';'
488 );
489 END IF;
490 --
491 -- Initialise the global variables to avoid recomputing Mark/Grade for the
492 -- for the same Student Unit Attempt and Grading Period
493 --
494 g_person_id := p_person_id;
495 g_course_cd := p_course_cd;
496 g_uoo_id := p_uoo_id;
497 g_grading_period_cd := p_grading_period_cd;
498 --
499 -- Step 1
500 --
501 -- Select all the Assessment Item Groups which are associated with the
502 -- student that have Non-Zero weighting and convert them to the same base scale
503 --
504 FOR rec_suaaig IN cur_suaaig (
505 p_person_id,
506 p_course_cd,
507 p_uoo_id,
508 p_grading_period_cd
509 ) LOOP
510 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
511 fnd_log.string (
512 fnd_log.level_statement,
513 g_module_head || l_routine || '.selected values',
514 'group_name =>' || rec_suaaig.group_name || ';' ||
515 'group_formula_code =>' || rec_suaaig.group_formula_code || ';' ||
516 'group_formula_qty =>' || rec_suaaig.group_formula_qty || ';' ||
517 'group_weight_qty =>' || rec_suaaig.group_weight_qty || ';' ||
518 'ass_id =>' || rec_suaaig.ass_id || ';' ||
519 'grading_schema_cd =>' || rec_suaaig.grading_schema_cd || ';' ||
520 'gs_version_number =>' || rec_suaaig.gs_version_number || ';' ||
521 'mandatory_type_code =>' || rec_suaaig.mandatory_type_code || ';' ||
522 'ai_weight_qty =>' || rec_suaaig.ai_weight_qty || ';' ||
523 'waived_flag =>' || rec_suaaig.waived_flag || ';' ||
524 'mark =>' || rec_suaaig.mark || ';' ||
525 'common_base_mark =>' || rec_suaaig.common_base_mark || ';' ||
526 'v_group_best_atleast_n =>' || v_group_best_atleast_n || ';'
527 );
528 END IF;
529 --
530 -- Step 2
531 --
532 -- Group all Assessment Items of a grading period based on the
533 -- Assessment Type and Formula. Select the Best N or At least N assessment
534 -- items while accounting for any 'Waived' flag of the Student Unit
535 -- Assessment Items. Filter out all the outcomes that are not needed for
536 -- derivation. If the student has not attempted Best N or At least N then
537 -- set mark = NULL, Grade = NULL, and exit.
538 --
539 IF (v_previous_group_name <> rec_suaaig.group_name) THEN
540 IF (v_previous_group_formula > v_group_best_atleast_n) THEN
541 --
542 -- Student does not have enough Best N or Atleast N for SUAO derivation;
543 -- So return Mark and Grade as NULL
544 --
545 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
546 fnd_log.string (
547 fnd_log.level_statement,
548 g_module_head || l_routine || '.not_enough_best_atleast_n_items',
549 'Returning the SUAO Mark as NULL and Grade as NULL with error message IGS_AS_STU_HAS_INSUF_AI_ATTMPS'
550 );
551 END IF;
552 RETURN ('::;;IGS_AS_STU_HAS_INSUF_AI_ATTMPS');
553 END IF;
554 v_previous_group_name := rec_suaaig.group_name;
555 v_previous_group_formula := rec_suaaig.group_formula_qty;
556 v_previous_group_formula_cd := rec_suaaig.group_formula_code;
557 v_group_best_atleast_n := 0;
558 END IF;
559 --
560 -- Step 3
561 --
562 -- Check if All Mandatory Type of Assessment Items have been attempted.
563 -- If not, then set marks to 0 and set grade to NULL for the SUAO and exit.
564 --
565 IF ((rec_suaaig.mandatory_type_code = 'MANDATORY') AND
566 (rec_suaaig.mark IS NULL) AND
567 (rec_suaaig.waived_flag = 'N')) THEN
568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
569 fnd_log.string (
570 fnd_log.level_statement,
571 g_module_head || l_routine || '.mandatory_items_enough_best_atleast_n_items',
572 'Returning the SUAO Mark as 0 and Grade as NULL with error message IGS_AS_STU_NOT_ATMPT_MAND_AI'
573 );
574 END IF;
575 RETURN ('0::;;IGS_AS_STU_NOT_ATMPT_MAND_AI');
576 END IF;
577 --
578 -- Check if All 'Mandatory Pass' assessment items have been passed.
579 --
580 IF ((rec_suaaig.mandatory_type_code = 'MANDATORY_PASS') AND
581 (v_mandatory_pass_items_passed)) THEN
582 OPEN cur_grade (
583 rec_suaaig.grading_schema_cd,
584 rec_suaaig.gs_version_number,
585 rec_suaaig.mark
586 );
587 FETCH cur_grade INTO rec_grade;
588 CLOSE cur_grade;
589 IF (NVL (rec_grade.s_result_type, 'NOTPASS') <> 'PASS') THEN
590 v_mandatory_pass_items_passed := FALSE;
591 END IF;
592 END IF;
593 --
594 -- Get the Best N/Atleast N Assessment Items
595 --
596 IF ((rec_suaaig.group_formula_code IN ('BEST_N', 'ATLEAST_N')) AND
597 ((rec_suaaig.mark IS NOT NULL) OR
598 (rec_suaaig.waived_flag = 'Y'))) THEN
599 v_group_best_atleast_n := v_group_best_atleast_n + 1;
600 END IF;
601 --
602 -- Add the Student Unit Attempt Assessment Item information to a table to
603 -- derive the Assessment Item Group Mark
604 --
605 IF ((rec_suaaig.group_formula_code = 'BEST_N') AND
606 ((rec_suaaig.mark IS NOT NULL) OR
607 (rec_suaaig.waived_flag = 'Y'))) THEN
608 IF (v_group_best_atleast_n <= rec_suaaig.group_formula_qty) THEN
609 IF (rec_suaaig.waived_flag = 'Y') THEN
610 rec_suaaig.mark := 0;
611 END IF;
612 suaaio_table(aio_table_index) := rec_suaaig;
613 aio_table_index := aio_table_index + 1;
614 END IF;
615 ELSIF ((rec_suaaig.mark IS NOT NULL) OR
616 (rec_suaaig.waived_flag = 'Y')) THEN
617 IF (rec_suaaig.waived_flag = 'Y') THEN
618 rec_suaaig.mark := 0;
619 END IF;
620 suaaio_table(aio_table_index) := rec_suaaig;
621 aio_table_index := aio_table_index + 1;
622 END IF;
623 END LOOP;
624 --
625 IF ((v_previous_group_formula_cd IN ('BEST_N', 'ATLEAST_N')) AND
626 (v_previous_group_formula > v_group_best_atleast_n)) THEN
627 --
628 -- Student does not have enough Best N or Atleast N for SUAO derivation;
629 -- So return Mark and Grade as NULL
630 --
631 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
632 fnd_log.string (
633 fnd_log.level_statement,
634 g_module_head || l_routine || '.not_enough_best_atleast_n_items',
635 'Returning the SUAO Mark as NULL and Grade as NULL with error message IGS_AS_STU_HAS_INSUF_AI_ATTMPS'
636 );
637 END IF;
638 RETURN ('::;;IGS_AS_STU_HAS_INSUF_AI_ATTMPS');
639 END IF;
640 --
641 IF (v_previous_group_name = '`') THEN
642 --
643 -- No Student Unit Attempts Assessment Items found so return NULL
644 --
645 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
646 fnd_log.string (
647 fnd_log.level_statement,
648 g_module_head || l_routine || '.no_assessment_items_setup',
649 'Returning the SUAO Mark as NULL and Grade as NULL as there are no assessment items setup'
650 );
651 END IF;
652 RETURN ('::');
653 END IF;
654 --
655 v_previous_group_name := '`';
656 --
657 OPEN cur_entry_conf;
658 FETCH cur_entry_conf INTO rec_entry_conf;
659 CLOSE cur_entry_conf;
660 IF (NVL (rec_entry_conf.key_mark_entry_dec_points, 0) = 0) THEN
661 l_format_mask := 'FM990';
662 ELSE
663 l_format_mask := RPAD ('FM990D', 9 - (3 - rec_entry_conf.key_mark_entry_dec_points), '0');
664 END IF;
665 --
666 -- Step 4
667 --
668 -- Roll up Student Unit Attempt Assessment Item Group mark (SUAAIG Mark)
669 -- from each Student Unit Attempt Assessment Item mark (SUAAI Mark)
670 --
671 FOR i IN 1..(aio_table_index - 1) LOOP
672 IF (v_previous_group_name <> suaaio_table(i).group_name) THEN
673 --
674 -- Compute the Assessment Item Group Mark at the change of each group
675 --
676 IF (aig_table_index > 0) THEN
677 suaaig_table(aig_table_index).group_name := v_previous_group_name;
678 IF (v_sum_of_all_the_weights > 0) THEN
679 suaaig_table(aig_table_index).mark := TO_CHAR ((v_mark_weight_products / v_sum_of_all_the_weights), l_format_mask);
680 ELSE
681 suaaig_table(aig_table_index).mark := 0;
682 END IF;
683 suaaig_table(aig_table_index).group_weight_qty := v_previous_group_formula;
684 --
685 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
686 fnd_log.string (
687 fnd_log.level_statement,
688 g_module_head || l_routine || '.assessment_item_group_mark',
689 'Group Name =>' || v_previous_group_name || ';' ||
690 'Group Formula Weight =>' || v_previous_group_formula || ';' ||
691 'Mark =>' || suaaig_table(aig_table_index).mark || ';'
692 );
693 END IF;
694 END IF;
695 v_sum_of_all_the_weights := 0;
696 v_mark_weight_products := 0;
697 v_previous_group_name := suaaio_table(i).group_name;
698 v_previous_group_formula := suaaio_table(i).group_weight_qty;
699 aig_table_index := aig_table_index + 1;
700 END IF;
701 IF (suaaio_table(i).waived_flag = 'N') THEN
702 v_sum_of_all_the_weights := v_sum_of_all_the_weights + suaaio_table(i).ai_weight_qty;
703 v_mark_weight_products := v_mark_weight_products + (NVL (suaaio_table(i).common_base_mark, 0) * suaaio_table(i).ai_weight_qty);
704 END IF;
705 END LOOP;
706 --
707 -- Compute the Assessment Item Group Mark at the change of each group
708 --
709 IF (aig_table_index > 0) THEN
710 suaaig_table(aig_table_index).group_name := v_previous_group_name;
711 IF (v_sum_of_all_the_weights > 0) THEN
712 suaaig_table(aig_table_index).mark := TO_CHAR ((v_mark_weight_products / v_sum_of_all_the_weights), l_format_mask);
713 ELSE
714 suaaig_table(aig_table_index).mark := 0;
715 END IF;
716 suaaig_table(aig_table_index).group_weight_qty := v_previous_group_formula;
717 --
718 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
719 fnd_log.string (
720 fnd_log.level_statement,
721 g_module_head || l_routine || '.assessment_item_group_mark',
722 'Group Name =>' || v_previous_group_name || ';' ||
723 'Group Formula Weight =>' || v_previous_group_formula || ';' ||
724 'Mark =>' || suaaig_table(aig_table_index).mark || ';'
725 );
726 END IF;
727 END IF;
728 --
729 v_sum_of_all_the_weights := 0;
730 v_mark_weight_products := 0;
731 --
732 -- Step 5
733 --
734 -- Roll up Student Unit Attempt mark (SUA mark) from each Student
735 -- Unit Attempt Assessment Item Group mark (SUAAIG mark)
736 --
737 FOR i IN 1..aig_table_index LOOP
738 v_sum_of_all_the_weights := v_sum_of_all_the_weights + suaaig_table(i).group_weight_qty;
739 v_mark_weight_products := v_mark_weight_products + (NVL (suaaig_table(i).mark, 0) * suaaig_table(i).group_weight_qty);
740 END LOOP;
741 --
742 IF (v_sum_of_all_the_weights > 0) THEN
743 v_suao_raw_mark := TO_CHAR ((v_mark_weight_products / v_sum_of_all_the_weights), l_format_mask);
744 ELSE
745 v_suao_raw_mark := 0;
746 END IF;
747 --
748 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
749 fnd_log.string (
750 fnd_log.level_statement,
751 g_module_head || l_routine || '.suao_raw_mark',
752 'SUAO Raw Mark =>' || v_suao_raw_mark || ';'
753 );
754 END IF;
755 --
756 -- Step 6
757 --
758 -- Convert Student Unit Attempt Outcome mark to the Unit Section Grading
759 -- Schema and find corresponding Grade.
760 --
761 OPEN cur_usec_grd_sch (p_uoo_id);
762 FETCH cur_usec_grd_sch INTO rec_usec_grd_sch;
763 --
764 IF (cur_usec_grd_sch%NOTFOUND) THEN
765 CLOSE cur_usec_grd_sch;
766 OPEN cur_unit_grd_sch (p_uoo_id);
767 FETCH cur_unit_grd_sch INTO rec_usec_grd_sch;
768 CLOSE cur_unit_grd_sch;
769 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
770 fnd_log.string (
771 fnd_log.level_statement,
772 g_module_head || l_routine || '.usec_dflt_grd_sch_not_available',
773 '=>Default Unit Grading Schema=>' || rec_usec_grd_sch.grading_schema_code || ';' || rec_usec_grd_sch.grd_schm_version_number || ';'
774 );
775 END IF;
776 ELSE
777 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
778 fnd_log.string (
779 fnd_log.level_statement,
780 g_module_head || l_routine || '.usec_dflt_grd_sch_available',
781 '=>Default Unit Section Grading Schema=>' || rec_usec_grd_sch.grading_schema_code || ';' || rec_usec_grd_sch.grd_schm_version_number || ';'
782 );
783 END IF;
784 CLOSE cur_usec_grd_sch;
785 END IF;
786 --
787 v_suao_mark := TO_CHAR ((v_suao_raw_mark * ((rec_usec_grd_sch.upper_limit - rec_usec_grd_sch.lower_limit)/100)) + rec_usec_grd_sch.lower_limit, l_format_mask);
788 --
789 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
790 fnd_log.string (
791 fnd_log.level_statement,
792 g_module_head || l_routine || '.suao_mark_using_usec_grd_sch',
793 'SUAO Mark Converted to Unit Section Grading Schema=>' || v_suao_mark || ';'
794 );
795 END IF;
796 --
797 OPEN cur_grade (
798 rec_usec_grd_sch.grading_schema_code,
799 rec_usec_grd_sch.grd_schm_version_number,
800 v_suao_mark
801 );
802 FETCH cur_grade INTO rec_grade;
803 CLOSE cur_grade;
804 --
805 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
806 fnd_log.string (
807 fnd_log.level_statement,
808 g_module_head || l_routine || '.suao_grade',
809 'SUAO Grade=>' || rec_grade.grade || ';'
810 );
811 END IF;
812 --
813 -- Step 7
814 --
815 -- Check if All 'Mandatory Pass' assessment items have been passed.
816 -- If not, then keep marks unchanged and assign a "Fail" grade for the SUAO.
817 --
818 IF (NOT v_mandatory_pass_items_passed) THEN
819 rec_grade.grade := NULL;
820 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
821 fnd_log.string (
822 fnd_log.level_statement,
823 g_module_head || l_routine || '.not_passed_mand_pass_items',
824 'Returning the derived SUAO Mark and NULL for Grade with error message IGS_AS_STU_NOT_PAS_MAND_PAS_AI'
825 );
826 END IF;
827 RETURN (v_suao_mark || '::;;IGS_AS_STU_NOT_PAS_MAND_PAS_AI');
828 END IF;
829 --
830 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
831 fnd_log.string (
832 fnd_log.level_procedure,
833 g_module_head || l_routine || '.end',
834 l_routine
835 );
836 END IF;
837 --
838 RETURN (v_suao_mark || '::' || rec_grade.grade);
839 EXCEPTION
840 WHEN OTHERS THEN
841 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
842 fnd_log.string (
843 fnd_log.level_exception,
844 g_module_head || l_routine || '.exception',
845 'Returning the derived SUAO Mark and NULL for Grade with error : ' || SQLERRM
846 );
847 END IF;
848 RETURN (v_suao_mark || '::');
849 END derive_suao_mark_grade;
850 --
851 -- Procedure to derive the Student Unit Attempt Outcome Mark and Grade from
852 -- Student Unit Attempt Assessment Item Outcome
853 --
854 PROCEDURE derive_suao_mark_grade_suaio (
855 p_person_id IN NUMBER,
856 p_course_cd IN VARCHAR2,
857 p_uoo_id IN NUMBER,
858 p_grading_period_cd IN VARCHAR2,
859 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N',
860 p_mark OUT NOCOPY NUMBER,
861 p_grade OUT NOCOPY VARCHAR2,
862 p_message_name OUT NOCOPY VARCHAR2
863 ) IS
864 l_routine VARCHAR2(30) := 'derive_suao_mark_grade_suaio';
865 BEGIN
866 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
867 fnd_log.string (
868 fnd_log.level_procedure,
869 g_module_head || l_routine || '.begin',
870 'Params: p_person_id=>' || p_person_id || ';' ||
871 'p_course_cd=>' || p_course_cd || ';' ||
872 'p_uoo_id=>' || p_uoo_id || ';' ||
873 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
874 'p_reset_mark_grade=>' || p_reset_mark_grade || ';p_message_name'
875 );
876 END IF;
877 --
878 -- Nullify the global variables so that the Mark/Grade is derived afresh
879 --
880 IF (p_reset_mark_grade = 'Y') THEN
881 g_person_id := NULL;
882 g_course_cd := NULL;
883 g_uoo_id := NULL;
884 g_grading_period_cd := NULL;
885 END IF;
886 --
887 -- Check if the Mark/Grade is already derived for the Student
888 --
889 IF g_person_id = p_person_id AND
890 g_course_cd = p_course_cd AND
891 g_uoo_id = p_uoo_id AND
892 g_grading_period_cd = p_grading_period_cd THEN
893 --
894 -- Mark/Grade already derived for the Student so return mark without recomputing
895 --
896 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
897 fnd_log.string (
898 fnd_log.level_statement,
899 g_module_head || l_routine || '.mark_already_derived',
900 'Returning the already derived mark=>' ||
901 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1) ||
902 '; grade=>' || SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2) || '; with error message ' || p_message_name
903 );
904 END IF;
905 ELSE
906 --
907 -- Mark/Grade not derived for the Student so derive and return Mark
908 --
909 g_mark_grade := derive_suao_mark_grade (
910 p_person_id,
911 p_course_cd,
912 p_uoo_id,
913 p_grading_period_cd
914 );
915 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
916 fnd_log.string (
917 fnd_log.level_statement,
918 g_module_head || l_routine || '.derived_mark',
919 'Derived Mark=>' ||
920 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1) ||
921 '; with error message ' || p_message_name
922 );
923 END IF;
924 END IF;
925 --
926 -- Extract Message Name
927 --
928 IF (INSTR (g_mark_grade, ';;') > 0) THEN
929 p_message_name := SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2);
930 END IF;
931 --
932 -- Extract Grade
933 --
934 IF (p_message_name IS NULL) THEN
935 p_grade := SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2);
936 ELSE
937 p_grade := SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2, INSTR (g_mark_grade, ';;') - INSTR (g_mark_grade, '::')- 2);
938 END IF;
939 --
940 -- Extract Mark
941 --
942 p_mark := SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1);
943 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
944 fnd_log.string (
945 fnd_log.level_procedure,
946 g_module_head || l_routine || '.end',
947 l_routine
948 );
949 END IF;
950 END derive_suao_mark_grade_suaio;
951 --
952 -- Function to derive the Student Unit Attempt Outcome Mark from Student Unit
953 -- Attempt Assessment Item Outcome Marks
954 --
955 -- This function is a overloaded so that it can be called from SQL and PL/SQL
956 -- or Java separately so that the error message can be shown to the user in
957 -- case of PL/SQL or Java
958 --
959 FUNCTION derive_suao_mark_from_suaio (
960 p_person_id IN NUMBER,
961 p_course_cd IN VARCHAR2,
962 p_uoo_id IN NUMBER,
963 p_grading_period_cd IN VARCHAR2,
964 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N',
965 p_message_name OUT NOCOPY VARCHAR2
966 ) RETURN NUMBER IS
967 l_routine VARCHAR2(30) := 'derive_suao_mark_from_suaio';
968 BEGIN
969 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
970 fnd_log.string (
971 fnd_log.level_procedure,
972 g_module_head || l_routine || '.begin',
973 'Params: p_person_id=>' || p_person_id || ';' ||
974 'p_course_cd=>' || p_course_cd || ';' ||
975 'p_uoo_id=>' || p_uoo_id || ';' ||
976 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
977 'p_reset_mark_grade=>' || p_reset_mark_grade || ';p_message_name'
978 );
979 END IF;
980 --
981 -- Nullify the global variables so that the Mark/Grade is derived afresh
982 --
983 IF (p_reset_mark_grade = 'Y') THEN
984 g_person_id := NULL;
985 g_course_cd := NULL;
986 g_uoo_id := NULL;
987 g_grading_period_cd := NULL;
988 END IF;
989 --
990 -- Check if the Mark/Grade is already derived for the Student
991 --
992 IF g_person_id = p_person_id AND
993 g_course_cd = p_course_cd AND
994 g_uoo_id = p_uoo_id AND
995 g_grading_period_cd = p_grading_period_cd THEN
996 --
997 -- Mark/Grade already derived for the Student so return mark without recomputing
998 --
999 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1000 fnd_log.string (
1001 fnd_log.level_statement,
1002 g_module_head || l_routine || '.mark_already_derived',
1003 'Returning the already derived mark=>' ||
1004 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1) ||
1005 '; with error message ' || SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2)
1006 );
1007 END IF;
1008 ELSE
1009 --
1010 -- Mark/Grade not derived for the Student so derive and return Mark
1011 --
1012 g_mark_grade := derive_suao_mark_grade (
1013 p_person_id,
1014 p_course_cd,
1015 p_uoo_id,
1016 p_grading_period_cd
1017 );
1018 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1019 fnd_log.string (
1020 fnd_log.level_statement,
1021 g_module_head || l_routine || '.derived_mark',
1022 'Derived Mark=>' ||
1023 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1) ||
1024 '; with error message ' || SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2)
1025 );
1026 END IF;
1027 END IF;
1028 IF (INSTR (g_mark_grade, ';;') > 0) THEN
1029 p_message_name := SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2);
1030 END IF;
1031 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1032 fnd_log.string (
1033 fnd_log.level_procedure,
1034 g_module_head || l_routine || '.end',
1035 l_routine
1036 );
1037 END IF;
1038 RETURN (SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1));
1039 END derive_suao_mark_from_suaio;
1040 --
1041 -- Function to derive the Student Unit Attempt Outcome Mark from Student Unit
1042 -- Attempt Assessment Item Outcome Marks
1043 --
1044 FUNCTION derive_suao_mark_from_suaio (
1045 p_person_id IN NUMBER,
1046 p_course_cd IN VARCHAR2,
1047 p_uoo_id IN NUMBER,
1048 p_grading_period_cd IN VARCHAR2,
1049 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N'
1050 ) RETURN NUMBER IS
1051 l_routine VARCHAR2(30) := 'derive_suao_mark_from_suaio';
1052 BEGIN
1053 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1054 fnd_log.string (
1055 fnd_log.level_procedure,
1056 g_module_head || l_routine || '.begin',
1057 'Params: p_person_id=>' || p_person_id || ';' ||
1058 'p_course_cd=>' || p_course_cd || ';' ||
1059 'p_uoo_id=>' || p_uoo_id || ';' ||
1060 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
1061 'p_reset_mark_grade=>' || p_reset_mark_grade || ';'
1062 );
1063 END IF;
1064 --
1065 -- Nullify the global variables so that the Mark/Grade is derived afresh
1066 --
1067 IF (p_reset_mark_grade = 'Y') THEN
1068 g_person_id := NULL;
1069 g_course_cd := NULL;
1070 g_uoo_id := NULL;
1071 g_grading_period_cd := NULL;
1072 END IF;
1073 --
1074 -- Check if the Mark/Grade is already derived for the Student
1075 --
1076 IF g_person_id = p_person_id AND
1077 g_course_cd = p_course_cd AND
1078 g_uoo_id = p_uoo_id AND
1079 g_grading_period_cd = p_grading_period_cd THEN
1080 --
1081 -- Mark/Grade already derived for the Student so return Mark without recomputing
1082 --
1083 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1084 fnd_log.string (
1085 fnd_log.level_statement,
1086 g_module_head || l_routine || '.mark_already_derived',
1087 'Returning the already derived mark=>' ||
1088 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1089 );
1090 END IF;
1091 ELSE
1092 --
1093 -- Mark/Grade not derived for the Student so derive and return Mark
1094 --
1095 g_mark_grade := derive_suao_mark_grade (
1096 p_person_id,
1097 p_course_cd,
1098 p_uoo_id,
1099 p_grading_period_cd
1100 );
1101 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1102 fnd_log.string (
1103 fnd_log.level_statement,
1104 g_module_head || l_routine || '.derived_mark',
1105 'Derived Mark=>' ||
1106 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1107 );
1108 END IF;
1109 END IF;
1110 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1111 fnd_log.string (
1112 fnd_log.level_procedure,
1113 g_module_head || l_routine || '.end',
1114 l_routine
1115 );
1116 END IF;
1117 RETURN (SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1));
1118 END derive_suao_mark_from_suaio;
1119 --
1120 -- Function to check 'Derive Unit Mark from Assessment Item Mark' and if the
1121 -- Unit Section is not Submitted then derive the Student Unit Attempt Outcome
1122 -- Mark from Student Unit Attempt Assessment Item Outcome Marks if the Outcome
1123 -- is neither Finalized nor Manually Overridden. If the Mark and Grade are not
1124 -- to be derived then the passed on mark and grade will be returned back.
1125 --
1126 FUNCTION derive_suao_mark_from_suaio (
1127 p_person_id IN NUMBER,
1128 p_course_cd IN VARCHAR2,
1129 p_uoo_id IN NUMBER,
1130 p_grading_period_cd IN VARCHAR2,
1131 p_mark IN NUMBER,
1132 p_grade IN VARCHAR2,
1133 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N'
1134 ) RETURN NUMBER IS
1135 --
1136 -- Check if the Unit Section Grades are submitted
1137 --
1138 CURSOR cur_usec_submitted IS
1139 SELECT 'Y' submitted
1140 FROM igs_as_gaa_sub_hist sub
1141 WHERE sub.uoo_id = p_uoo_id
1142 AND sub.grading_period_cd = p_grading_period_cd
1143 AND sub.submission_type = 'GRADE'
1144 AND sub.submission_status = 'COMPLETE';
1145 --
1146 --
1147 --
1148 CURSOR cur_suao_final_man_ovr IS
1149 SELECT 'Y' finalized_manually_overridden
1150 FROM igs_as_su_stmptout_all suao
1151 WHERE suao.person_id = p_person_id
1152 AND suao.course_cd = p_course_cd
1153 AND suao.uoo_id = p_uoo_id
1154 AND suao.grading_period_cd = p_grading_period_cd
1155 AND suao.outcome_dt =
1156 (SELECT MAX (outcome_dt)
1157 FROM igs_as_su_stmptout_all
1158 WHERE person_id = suao.person_id
1159 AND course_cd = suao.course_cd
1160 AND uoo_id = suao.uoo_id
1161 AND grading_period_cd = suao.grading_period_cd)
1162 AND (suao.manual_override_flag = 'Y'
1163 OR suao.finalised_outcome_ind = 'Y');
1164 --
1165 l_routine VARCHAR2(30) := 'derive_suao_mark_from_suaio';
1166 rec_usec_submitted cur_usec_submitted%ROWTYPE;
1167 rec_suao_final_man_ovr cur_suao_final_man_ovr%ROWTYPE;
1168 --
1169 BEGIN
1170 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1171 fnd_log.string (
1172 fnd_log.level_procedure,
1173 g_module_head || l_routine || '.begin',
1174 'Params: p_person_id=>' || p_person_id || ';' ||
1175 'p_course_cd=>' || p_course_cd || ';' ||
1176 'p_uoo_id=>' || p_uoo_id || ';' ||
1177 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
1178 'p_mark=>' || p_mark || ';' ||
1179 'p_grade=>' || p_grade || ';' ||
1180 'p_reset_mark_grade=>' || p_reset_mark_grade || ';'
1181 );
1182 END IF;
1183 --
1184 -- Nullify the global variables so that the Mark/Grade is derived afresh
1185 --
1186 IF (p_reset_mark_grade = 'Y') THEN
1187 g_person_id := NULL;
1188 g_course_cd := NULL;
1189 g_uoo_id := NULL;
1190 g_grading_period_cd := NULL;
1191 g_unit_section_submitted := FALSE;
1192 END IF;
1193 --
1194 -- Check if the Mark/Grade is already derived for the Student
1195 --
1196 IF g_person_id = p_person_id AND
1197 g_course_cd = p_course_cd AND
1198 g_uoo_id = p_uoo_id AND
1199 g_grading_period_cd = p_grading_period_cd THEN
1200 IF g_unit_section_submitted THEN
1201 --
1202 -- As the Unit Section is submitted Mark/Grade need not be derived so
1203 -- return the passed values as Mark/Grade
1204 --
1205 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1206 fnd_log.string (
1207 fnd_log.level_statement,
1208 g_module_head || l_routine || '.usec_submitted_mark_not_derived',
1209 'Returning the passed mark as Unit Section is submitted=>' || p_mark
1210 );
1211 END IF;
1212 g_mark_grade := p_mark || '::' || p_grade;
1213 ELSE
1214 --
1215 -- Mark/Grade already derived for the Student so return Mark without recomputing
1216 --
1217 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1218 fnd_log.string (
1219 fnd_log.level_statement,
1220 g_module_head || l_routine || '.mark_already_derived_usec_not_submitted',
1221 'Returning the already derived mark as Unit Section is not Submitted=>' ||
1222 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1223 );
1224 END IF;
1225 END IF;
1226 ELSE
1227 --
1228 g_person_id := p_person_id;
1229 g_course_cd := p_course_cd;
1230 g_uoo_id := p_uoo_id;
1231 g_grading_period_cd := p_grading_period_cd;
1232 --
1233 -- Check if the Unit Section Grades are submitted
1234 --
1235 OPEN cur_usec_submitted;
1236 FETCH cur_usec_submitted INTO rec_usec_submitted;
1237 CLOSE cur_usec_submitted;
1238 IF (rec_usec_submitted.submitted = 'Y') THEN
1239 g_unit_section_submitted := TRUE;
1240 --
1241 -- Return back the passed Mark/Grade as Unit Section is Submitted
1242 --
1243 g_mark_grade := p_mark || '::' || p_grade;
1244 --
1245 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1246 fnd_log.string (
1247 fnd_log.level_statement,
1248 g_module_head || l_routine || '.mark_not_derived_as_usec_submitted',
1249 'Returning back the passed mark as Unit Section is Submitted=>' ||
1250 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1251 );
1252 END IF;
1253 ELSE
1254 g_unit_section_submitted := FALSE;
1255 --
1256 -- Check if the Student Unit Attempt Outcomes that are flagged as
1257 -- 'Manually Overridden' or 'Finalized' will not be recalculated.
1258 --
1259 OPEN cur_suao_final_man_ovr;
1260 FETCH cur_suao_final_man_ovr INTO rec_suao_final_man_ovr;
1261 CLOSE cur_suao_final_man_ovr;
1262 IF (rec_suao_final_man_ovr.finalized_manually_overridden = 'Y') THEN
1263 --
1264 g_unit_section_submitted := TRUE;
1265 --
1266 -- Return back the passed Mark/Grade as Student Unit Attempt Outcome
1267 -- is 'Finalized' or 'Manually Overridden'
1268 --
1269 g_mark_grade := p_mark || '::' || p_grade;
1270 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1271 fnd_log.string (
1272 fnd_log.level_statement,
1273 g_module_head || l_routine || '.mark_not_derived_as_suao_fin_man_ovr',
1274 'Returning back the passed mark as Student Unit Attempt Outcome is ' ||
1275 'Finalized or Manually Overridden=>' ||
1276 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1277 );
1278 END IF;
1279 ELSE
1280 --
1281 -- Mark/Grade not derived for the Student so derive and return Mark
1282 --
1283 g_mark_grade := derive_suao_mark_grade (
1284 p_person_id,
1285 p_course_cd,
1286 p_uoo_id,
1287 p_grading_period_cd
1288 );
1289 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1290 fnd_log.string (
1291 fnd_log.level_statement,
1292 g_module_head || l_routine || '.derived_mark',
1293 'Derived Mark=>' ||
1294 SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1)
1295 );
1296 END IF;
1297 END IF;
1298 END IF;
1299 END IF;
1300 --
1301 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1302 fnd_log.string (
1303 fnd_log.level_procedure,
1304 g_module_head || l_routine || '.end',
1305 l_routine
1306 );
1307 END IF;
1308 --
1309 RETURN (SUBSTR (g_mark_grade, 1, INSTR (g_mark_grade, '::') - 1));
1310 --
1311 END derive_suao_mark_from_suaio;
1312 --
1313 --
1314 --
1315 FUNCTION derive_suao_grade_from_suaio (
1316 p_person_id IN NUMBER,
1317 p_course_cd IN VARCHAR2,
1318 p_uoo_id IN NUMBER,
1319 p_grading_period_cd IN VARCHAR2,
1320 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N',
1321 p_message_name OUT NOCOPY VARCHAR2
1322 ) RETURN VARCHAR2 IS
1323 l_routine VARCHAR2(30) := 'derive_suao_grade_from_suaio';
1324 BEGIN
1325 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1326 fnd_log.string (
1327 fnd_log.level_procedure,
1328 g_module_head || l_routine || '.begin',
1329 'Params: p_person_id=>' || p_person_id || ';' ||
1330 'p_course_cd=>' || p_course_cd || ';' ||
1331 'p_uoo_id=>' || p_uoo_id || ';' ||
1332 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
1333 'p_reset_mark_grade=>' || p_reset_mark_grade || ';p_message_name'
1334 );
1335 END IF;
1336 --
1337 -- Nullify the global variables so that the Mark/Grade is derived afresh
1338 --
1339 IF (p_reset_mark_grade = 'Y') THEN
1340 g_person_id := NULL;
1341 g_course_cd := NULL;
1342 g_uoo_id := NULL;
1343 g_grading_period_cd := NULL;
1344 END IF;
1345 --
1346 -- Check if the Mark/Grade is already derived for the Student
1347 --
1348 IF g_person_id = p_person_id AND
1349 g_course_cd = p_course_cd AND
1350 g_uoo_id = p_uoo_id AND
1351 g_grading_period_cd = p_grading_period_cd THEN
1352 --
1353 -- Mark/Grade already derived for the Student so return Grade without recomputing
1354 --
1355 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1356 fnd_log.string (
1357 fnd_log.level_statement,
1358 g_module_head || l_routine || '.grade_already_derived',
1359 'Returning the already derived grade=>' ||
1360 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2) ||
1361 '; with error message ' || SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2)
1362 );
1363 END IF;
1364 ELSE
1365 --
1366 -- Mark/Grade not derived for the Student so derive and return Grade
1367 --
1368 g_mark_grade := derive_suao_mark_grade (
1369 p_person_id,
1370 p_course_cd,
1371 p_uoo_id,
1372 p_grading_period_cd
1373 );
1374 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1375 fnd_log.string (
1376 fnd_log.level_statement,
1377 g_module_head || l_routine || '.derived_grade',
1378 'Derived Grade=>' ||
1379 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2) ||
1380 '; with error message ' || SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2)
1381 );
1382 END IF;
1383 END IF;
1384 IF (INSTR (g_mark_grade, ';;') > 0) THEN
1385 p_message_name := SUBSTR (g_mark_grade, INSTR (g_mark_grade, ';;') + 2);
1386 END IF;
1387 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1388 fnd_log.string (
1389 fnd_log.level_procedure,
1390 g_module_head || l_routine || '.end',
1391 l_routine
1392 );
1393 END IF;
1394 IF (p_message_name IS NULL) THEN
1395 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2));
1396 ELSE
1397 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2, INSTR (g_mark_grade, ';;') - INSTR (g_mark_grade, '::')- 2));
1398 END IF;
1399 END derive_suao_grade_from_suaio;
1400 --
1401 --
1402 --
1403 FUNCTION derive_suao_grade_from_suaio (
1404 p_person_id IN NUMBER,
1405 p_course_cd IN VARCHAR2,
1406 p_uoo_id IN NUMBER,
1407 p_grading_period_cd IN VARCHAR2,
1408 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N'
1409 ) RETURN VARCHAR2 IS
1410 l_routine VARCHAR2(30) := 'derive_suao_grade_from_suaio';
1411 BEGIN
1412 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1413 fnd_log.string (
1414 fnd_log.level_procedure,
1415 g_module_head || l_routine || '.begin',
1416 'Params: p_person_id=>' || p_person_id || ';' ||
1417 'p_course_cd=>' || p_course_cd || ';' ||
1418 'p_uoo_id=>' || p_uoo_id || ';' ||
1419 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
1420 'p_reset_mark_grade=>' || p_reset_mark_grade || ';'
1421 );
1422 END IF;
1423 --
1424 -- Nullify the global variables so that the Mark/Grade is derived afresh
1425 --
1426 IF (p_reset_mark_grade = 'Y') THEN
1427 g_person_id := NULL;
1428 g_course_cd := NULL;
1429 g_uoo_id := NULL;
1430 g_grading_period_cd := NULL;
1431 END IF;
1432 --
1433 -- Check if the Mark/Grade is already derived for the Student
1434 --
1435 IF g_person_id = p_person_id AND
1436 g_course_cd = p_course_cd AND
1437 g_uoo_id = p_uoo_id AND
1438 g_grading_period_cd = p_grading_period_cd THEN
1439 --
1440 -- Mark/Grade already derived for the Student so return Grade without recomputing
1441 --
1442 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1443 fnd_log.string (
1444 fnd_log.level_statement,
1445 g_module_head || l_routine || '.grade_already_derived',
1446 'Returning the already derived grade=>' ||
1447 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1448 );
1449 END IF;
1450 ELSE
1451 --
1452 -- Mark/Grade not derived for the Student so derive and return Grade
1453 --
1454 g_mark_grade := derive_suao_mark_grade (
1455 p_person_id,
1456 p_course_cd,
1457 p_uoo_id,
1458 p_grading_period_cd
1459 );
1460 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1461 fnd_log.string (
1462 fnd_log.level_statement,
1463 g_module_head || l_routine || '.derived_grade',
1464 'Derived Grade=>' ||
1465 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1466 );
1467 END IF;
1468 END IF;
1469 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1470 fnd_log.string (
1471 fnd_log.level_procedure,
1472 g_module_head || l_routine || '.end',
1473 l_routine
1474 );
1475 END IF;
1476 IF (INSTR (g_mark_grade, ';;') > 0) THEN
1477 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2, INSTR (g_mark_grade, ';;') - INSTR (g_mark_grade, '::')- 2));
1478 ELSE
1479 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2));
1480 END IF;
1481 END derive_suao_grade_from_suaio;
1482 --
1483 -- Function to check 'Derive Unit Mark from Assessment Item Mark' and if the
1484 -- Unit Section is not Submitted then derive the Student Unit Attempt Outcome
1485 -- Grade from Student Unit Attempt Assessment Item Outcome Marks if the Outcome
1486 -- is neither Finalized nor Manually Overridden. If the Mark and Grade are not
1487 -- to be derived then the passed on mark and grade will be returned back.
1488 --
1489 FUNCTION derive_suao_grade_from_suaio (
1490 p_person_id IN NUMBER,
1491 p_course_cd IN VARCHAR2,
1492 p_uoo_id IN NUMBER,
1493 p_grading_period_cd IN VARCHAR2,
1494 p_mark IN NUMBER,
1495 p_grade IN VARCHAR2,
1496 p_reset_mark_grade IN VARCHAR2 DEFAULT 'N'
1497 ) RETURN VARCHAR2 IS
1498 --
1499 -- Check if the Unit Section Grades are submitted
1500 --
1501 CURSOR cur_usec_submitted IS
1502 SELECT 'Y' submitted
1503 FROM igs_as_gaa_sub_hist sub
1504 WHERE sub.uoo_id = p_uoo_id
1505 AND sub.grading_period_cd = p_grading_period_cd
1506 AND sub.submission_type = 'GRADE'
1507 AND sub.submission_status = 'COMPLETE';
1508 --
1509 --
1510 --
1511 CURSOR cur_suao_final_man_ovr IS
1512 SELECT 'Y' finalized_manually_overridden
1513 FROM igs_as_su_stmptout_all suao
1514 WHERE suao.person_id = p_person_id
1515 AND suao.course_cd = p_course_cd
1516 AND suao.uoo_id = p_uoo_id
1517 AND suao.grading_period_cd = p_grading_period_cd
1518 AND suao.outcome_dt =
1519 (SELECT MAX (outcome_dt)
1520 FROM igs_as_su_stmptout_all
1521 WHERE person_id = suao.person_id
1522 AND course_cd = suao.course_cd
1523 AND uoo_id = suao.uoo_id
1524 AND grading_period_cd = suao.grading_period_cd)
1525 AND (suao.manual_override_flag = 'Y'
1526 OR suao.finalised_outcome_ind = 'Y');
1527 --
1528 l_routine VARCHAR2(30) := 'derive_suao_grade_from_suaio';
1529 rec_usec_submitted cur_usec_submitted%ROWTYPE;
1530 rec_suao_final_man_ovr cur_suao_final_man_ovr%ROWTYPE;
1531 --
1532 BEGIN
1533 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1534 fnd_log.string (
1535 fnd_log.level_procedure,
1536 g_module_head || l_routine || '.begin',
1537 'Params: p_person_id=>' || p_person_id || ';' ||
1538 'p_course_cd=>' || p_course_cd || ';' ||
1539 'p_uoo_id=>' || p_uoo_id || ';' ||
1540 'p_grading_period_cd=>' || p_grading_period_cd || ';' ||
1541 'p_mark=>' || p_mark || ';' ||
1542 'p_grade=>' || p_grade || ';' ||
1543 'p_reset_mark_grade=>' || p_reset_mark_grade || ';'
1544 );
1545 END IF;
1546 --
1547 -- Nullify the global variables so that the Mark/Grade is derived afresh
1548 --
1549 IF (p_reset_mark_grade = 'Y') THEN
1550 g_person_id := NULL;
1551 g_course_cd := NULL;
1552 g_uoo_id := NULL;
1553 g_grading_period_cd := NULL;
1554 g_unit_section_submitted := FALSE;
1555 END IF;
1556 --
1557 -- Check if the Mark/Grade is already derived for the Student
1558 --
1559 IF g_person_id = p_person_id AND
1560 g_course_cd = p_course_cd AND
1561 g_uoo_id = p_uoo_id AND
1562 g_grading_period_cd = p_grading_period_cd THEN
1563 IF g_unit_section_submitted THEN
1564 --
1565 -- As the Unit Section is submitted Mark/Grade need not be derived so
1566 -- return the passed values as Mark/Grade
1567 --
1568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1569 fnd_log.string (
1570 fnd_log.level_statement,
1571 g_module_head || l_routine || '.usec_submitted_grade_not_derived',
1572 'Returning the passed grade as Unit Section is submitted=>' || p_grade
1573 );
1574 END IF;
1575 g_mark_grade := p_mark || '::' || p_grade;
1576 ELSE
1577 --
1578 -- Mark/Grade already derived for the Student so return Grade without recomputing
1579 --
1580 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1581 fnd_log.string (
1582 fnd_log.level_statement,
1583 g_module_head || l_routine || '.grade_already_derived_usec_not_submitted',
1584 'Returning the already derived grade as Unit Section is not Submitted=>' ||
1585 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1586 );
1587 END IF;
1588 END IF;
1589 ELSE
1590 --
1591 g_person_id := p_person_id;
1592 g_course_cd := p_course_cd;
1593 g_uoo_id := p_uoo_id;
1594 g_grading_period_cd := p_grading_period_cd;
1595 --
1596 -- Check if the Unit Section Grades are submitted
1597 --
1598 OPEN cur_usec_submitted;
1599 FETCH cur_usec_submitted INTO rec_usec_submitted;
1600 CLOSE cur_usec_submitted;
1601 IF (rec_usec_submitted.submitted = 'Y') THEN
1602 g_unit_section_submitted := TRUE;
1603 --
1604 -- Return back the passed Mark/Grade as Unit Section is Submitted
1605 --
1606 g_mark_grade := p_mark || '::' || p_grade;
1607 --
1608 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1609 fnd_log.string (
1610 fnd_log.level_statement,
1611 g_module_head || l_routine || '.grade_not_derived_as_usec_submitted',
1612 'Returning back the passed grade as Unit Section is Submitted=>' ||
1613 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1614 );
1615 END IF;
1616 ELSE
1617 g_unit_section_submitted := FALSE;
1618 --
1619 -- Check if the Student Unit Attempt Outcomes that are flagged as
1620 -- 'Manually Overridden' or 'Finalized' will not be recalculated.
1621 --
1622 OPEN cur_suao_final_man_ovr;
1623 FETCH cur_suao_final_man_ovr INTO rec_suao_final_man_ovr;
1624 CLOSE cur_suao_final_man_ovr;
1625 IF (rec_suao_final_man_ovr.finalized_manually_overridden = 'Y') THEN
1626 --
1627 g_unit_section_submitted := TRUE;
1628 --
1629 -- Return back the passed Mark/Grade as Student Unit Attempt Outcome
1630 -- is 'Finalized' or 'Manually Overridden'
1631 --
1632 g_mark_grade := p_mark || '::' || p_grade;
1633 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1634 fnd_log.string (
1635 fnd_log.level_statement,
1636 g_module_head || l_routine || '.grade_not_derived_as_suao_fin_man_ovr',
1637 'Returning back the passed grade as Student Unit Attempt Outcome is ' ||
1638 'Finalized or Manually Overridden=>' ||
1639 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1640 );
1641 END IF;
1642 ELSE
1643 --
1644 -- Mark/Grade not derived for the Student so derive and return Grade
1645 --
1646 g_mark_grade := derive_suao_mark_grade (
1647 p_person_id,
1648 p_course_cd,
1649 p_uoo_id,
1650 p_grading_period_cd
1651 );
1652 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1653 fnd_log.string (
1654 fnd_log.level_statement,
1655 g_module_head || l_routine || '.derived_grade',
1656 'Derived Grade=>' ||
1657 SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2)
1658 );
1659 END IF;
1660 END IF;
1661 END IF;
1662 END IF;
1663 --
1664 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1665 fnd_log.string (
1666 fnd_log.level_procedure,
1667 g_module_head || l_routine || '.end',
1668 l_routine
1669 );
1670 END IF;
1671 --
1672 IF (INSTR (g_mark_grade, ';;') > 0) THEN
1673 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2, INSTR (g_mark_grade, ';;') - INSTR (g_mark_grade, '::')- 2));
1674 ELSE
1675 RETURN (SUBSTR (g_mark_grade, INSTR (g_mark_grade, '::') + 2));
1676 END IF;
1677 --
1678 END derive_suao_grade_from_suaio;
1679 --
1680 -- Function to derive Student's Assessment Status
1681 --
1682 -- 1st time the student enrolls in unit; Assessment Status = 'First Attempt'
1683 -- 2nd time the student enrolls in unit section; Assessment Status =
1684 -- 'Second Attempt', so and so forth
1685 --
1686 FUNCTION get_assessment_status (
1687 p_person_id IN NUMBER,
1688 p_course_cd IN VARCHAR2,
1689 p_uoo_id IN NUMBER,
1690 p_unit_cd IN VARCHAR2
1691 ) RETURN VARCHAR2 AS
1692 --
1693 -- Cursor to get the unit repeat information for a student
1694 --
1695 -- Note: Discontinued Unit Attempts are also considered as a graded outcome
1696 -- is created for Discontinued Unit Attempts.
1697 --
1698 CURSOR cur_suao (
1699 cp_uoo_id IN NUMBER,
1700 cp_unit_cd IN VARCHAR2,
1701 cp_person_id IN NUMBER,
1702 cp_course_cd IN VARCHAR2
1703 ) IS
1704 SELECT DISTINCT suao.person_id,
1705 suao.course_cd,
1706 suao.unit_cd,
1707 suao.ci_start_dt
1708 FROM igs_as_su_stmptout_all suao,
1709 igs_en_su_attempt_all sua
1710 WHERE suao.person_id = sua.person_id
1711 AND suao.course_cd = sua.course_cd
1712 AND suao.uoo_id = sua.uoo_id
1713 AND sua.unit_attempt_status <> 'DROPPED'
1714 AND suao.uoo_id <> p_uoo_id
1715 AND suao.unit_cd = cp_unit_cd
1716 AND suao.course_cd = cp_course_cd
1717 AND suao.person_id = cp_person_id
1718 ORDER BY suao.ci_start_dt ASC;
1719 --
1720 -- Cursor to get the Unit Section's Teaching Calendar Start Date
1721 --
1722 CURSOR cur_start_dt (
1723 cp_uoo_id IN NUMBER
1724 ) IS
1725 SELECT start_dt
1726 FROM igs_ca_inst ci,
1727 igs_ps_unit_ofr_opt_all uoo
1728 WHERE uoo.cal_type = ci.cal_type
1729 AND ci.sequence_number = uoo.ci_sequence_number
1730 AND uoo.uoo_id = cp_uoo_id;
1731 --
1732 l_st_dt DATE;
1733 lnrepeat NUMBER := 0;
1734 l_routine CONSTANT VARCHAR2(30) := 'get_assessment_status';
1735 --
1736 BEGIN
1737 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1738 fnd_log.string (
1739 fnd_log.level_procedure,
1740 g_module_head || l_routine || '.begin',
1741 'Params: p_person_id=>' || p_person_id || ';' ||
1742 'p_course_cd=>' || p_course_cd || ';' ||
1743 'p_uoo_id=>' || p_uoo_id || ';' ||
1744 'p_unit_cd=>' || p_unit_cd || ';'
1745 );
1746 END IF;
1747 OPEN cur_start_dt (p_uoo_id);
1748 FETCH cur_start_dt INTO l_st_dt;
1749 CLOSE cur_start_dt;
1750 FOR rec_suao IN cur_suao (p_uoo_id, p_unit_cd, p_person_id, p_course_cd) LOOP
1751 IF (l_st_dt > rec_suao.ci_start_dt) THEN
1752 lnrepeat := lnrepeat + 1;
1753 END IF;
1754 END LOOP;
1755 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1756 fnd_log.string (
1757 fnd_log.level_procedure,
1758 g_module_head || l_routine || '.end',
1759 'Total number of repeats lnrepeat=>' || lnrepeat
1760 );
1761 END IF;
1762 RETURN TO_CHAR (TO_DATE (lnrepeat + 1, 'J'), 'Jspth') || ' ' || g_attempt;
1763 END get_assessment_status;
1764 --
1765 -- Function that derives the Grading Period for a given Teaching Calendar
1766 --
1767 FUNCTION get_grading_period_code (
1768 p_teach_cal_type IN VARCHAR2,
1769 p_teach_ci_sequence_number IN NUMBER
1770 ) RETURN VARCHAR2 IS
1771 --
1772 -- Get the Assessment Calendar Configuration
1773 --
1774 CURSOR cur_assessment_cal_conf IS
1775 SELECT acc.mid_mgs_start_dt_alias,
1776 acc.mid_mgs_end_dt_alias,
1777 acc.efinal_mgs_start_dt_alias,
1778 acc.efinal_mgs_end_dt_alias,
1779 acc.final_mgs_start_dt_alias,
1780 acc.final_mgs_end_dt_alias
1781 FROM igs_as_cal_conf acc
1782 WHERE s_control_num = 1;
1783 --
1784 -- Check if the Teaching Period falls within given Start and End Date Alias
1785 --
1786 CURSOR cur_grading_period_details (
1787 cp_teach_cal_type IN VARCHAR2,
1788 cp_teach_ci_sequence_number IN NUMBER,
1789 cp_start_date_alias IN VARCHAR2,
1790 cp_end_date_alias IN VARCHAR2
1791 ) IS
1792 SELECT 'Y'
1793 FROM igs_ca_da_inst_v dai1,
1794 igs_ca_da_inst_v dai2
1795 WHERE dai1.alias_val >= TRUNC (SYSDATE)
1796 AND dai2.alias_val <= TRUNC (SYSDATE)
1797 AND dai1.cal_type = dai2.cal_type
1798 AND dai1.ci_sequence_number = dai2.ci_sequence_number
1799 AND dai1.cal_type = cp_teach_cal_type
1800 AND dai1.ci_sequence_number = cp_teach_ci_sequence_number
1801 AND dai2.dt_alias = cp_start_date_alias
1802 AND dai1.dt_alias = cp_end_date_alias;
1803 --
1804 rec_grading_period_details cur_grading_period_details%ROWTYPE;
1805 rec_assessment_cal_conf cur_assessment_cal_conf%ROWTYPE;
1806 --
1807 BEGIN
1808 --
1809 OPEN cur_assessment_cal_conf;
1810 FETCH cur_assessment_cal_conf INTO rec_assessment_cal_conf;
1811 CLOSE cur_assessment_cal_conf;
1812 --
1813 OPEN cur_grading_period_details (
1814 p_teach_cal_type,
1815 p_teach_ci_sequence_number,
1816 rec_assessment_cal_conf.mid_mgs_start_dt_alias,
1817 rec_assessment_cal_conf.mid_mgs_end_dt_alias
1818 );
1819 FETCH cur_grading_period_details INTO rec_grading_period_details;
1820 --
1821 IF (cur_grading_period_details%FOUND) THEN
1822 CLOSE cur_grading_period_details;
1823 RETURN 'MIDTERM';
1824 ELSE
1825 CLOSE cur_grading_period_details;
1826 --
1827 OPEN cur_grading_period_details (
1828 p_teach_cal_type,
1829 p_teach_ci_sequence_number,
1830 rec_assessment_cal_conf.efinal_mgs_start_dt_alias,
1831 rec_assessment_cal_conf.efinal_mgs_end_dt_alias
1832 );
1833 FETCH cur_grading_period_details INTO rec_grading_period_details;
1834 --
1835 IF (cur_grading_period_details%FOUND) THEN
1836 CLOSE cur_grading_period_details;
1837 RETURN 'EARLY_FINAL';
1838 ELSE
1839 CLOSE cur_grading_period_details;
1840 --
1841 OPEN cur_grading_period_details (
1842 p_teach_cal_type,
1843 p_teach_ci_sequence_number,
1844 rec_assessment_cal_conf.final_mgs_start_dt_alias,
1845 rec_assessment_cal_conf.final_mgs_end_dt_alias
1846 );
1847 FETCH cur_grading_period_details INTO rec_grading_period_details;
1848 --
1849 IF (cur_grading_period_details%FOUND) THEN
1850 CLOSE cur_grading_period_details;
1851 RETURN 'FINAL';
1852 ELSE
1853 CLOSE cur_grading_period_details;
1854 END IF;
1855 END IF;
1856 END IF;
1857 --
1858 -- If nothing is derived then return 'FINAL'
1859 --
1860 RETURN 'FINAL';
1861 --
1862 END get_grading_period_code;
1863 END igs_as_derive_grade;
[Home] [Help]