1 PACKAGE BODY IGS_AS_CALC_AWARD_MARK AS
2 /* $Header: IGSAS57B.pls 120.5 2006/07/31 07:33:11 ijeddy ship $ */
3 /*************************************************************
4 Created By : smanglm
5 Date Created on : 10-Oct-2003
6 Purpose : This package is created as part iof Summary Measurement
7 of attainment build.
8 This will have program unit to calculate
9 unit level marks,
10 award marks and honors level.
11 Change History
12 Who When What
13 Nalin Kumar 10-Feb-2004 Modified the fn_calc_unit_lvl_mark function to fix Bug# 3427366
14 Imran Jeddy 15-Apr-2005 Bug 4281818, Created a new function chk_if_excluded_unit.
15 It checks if a given uoo_id, unit_cd have the reference_cd_type
16 of SUMMEAS attached. It returns FALSE if its not and TRUE if it is.
17 This Check is used in the following cursors:
18 1. c_avail_cp in the function get_avail_cp.
19 2. Cursors c_total_unt_lvl_cp_alt, REF CURSORS l_stmt_cp_based and
20 l_stmt_priority_based and in Cursor c_unit_lvl_mark_wo_setup
21 in function fn_calc_unit_lvl_mark
22 Jitendra 15-Jun-2005 Changed function fn_calc_unit_lvl_mark for
23 Transfer Evaluation UI Build.
24 swaghmar 16-Jan-2006 Bug# 4951054 - Added check for disabling the UI's
25
26 (reverse chronological order - newest change first)
27 ***************************************************************/
28
29
30 -- below are some private utilities program units, which will be
31 -- called by the main public program units
32
33 -- ===============Utilities Method Section Begins================
34
35 FUNCTION chk_if_excluded_unit (p_uoo_id igs_en_su_attempt_all.uoo_id%TYPE,
36 p_unit_cd igs_en_su_attempt_all.unit_cd%TYPE,
37 p_version_number igs_en_su_attempt_all.version_number%TYPE)
38 RETURN VARCHAR2
39 IS
40 CURSOR C1 (cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE,
41 cp_unit_cd igs_en_su_attempt_all.unit_cd%TYPE,
42 cp_version_number igs_en_su_attempt_all.version_number%TYPE)
43 IS
44 SELECT 'X'
45 FROM igs_ps_usec_ref_cd refcd,
46 igs_ps_usec_ref usecref,
47 igs_ge_ref_cd_type rct
48 WHERE usecref.uoo_id = cp_uoo_id
49 AND usecref.unit_section_reference_id = refcd.unit_section_reference_id
50 AND refcd.reference_code_type = rct.reference_cd_type
51 AND rct.s_reference_cd_type = 'SUMMEAS'
52 UNION
53 SELECT 'X'
54 FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
55 WHERE urc.unit_cd = cp_unit_cd
56 AND urc.version_number = cp_version_number
57 AND urc.reference_cd_type = rct.reference_cd_type
58 AND rct.s_reference_cd_type = 'SUMMEAS'
59 AND NOT EXISTS ( SELECT refcd.reference_code_type
60 FROM igs_ps_usec_ref_cd refcd,
61 igs_ps_usec_ref usecref,
62 igs_ps_unit_ofr_opt_all opt,
63 igs_ge_ref_cd_type rct
64 WHERE usecref.uoo_id = cp_uoo_id
65 AND usecref.unit_section_reference_id =
66 refcd.unit_section_reference_id
67 AND refcd.reference_code_type = rct.reference_cd_type
68 AND rct.s_reference_cd_type = 'SUMMEAS')
69 UNION
70 SELECT 'X'
71 FROM igs_ps_us_req_ref_cd refcd,
72 igs_ps_usec_ref usecref,
73 igs_ps_unit_ofr_opt_all opt,
74 igs_ge_ref_cd_type rct
75 WHERE usecref.uoo_id = cp_uoo_id
76 AND usecref.unit_section_reference_id = refcd.unit_section_reference_id
77 AND refcd.reference_cd_type = rct.reference_cd_type
78 AND rct.s_reference_cd_type = 'SUMMEAS'
79 UNION
80 SELECT 'X'
81 FROM igs_ps_unitreqref_cd urc, igs_ge_ref_cd_type rct
82 WHERE urc.unit_cd = cp_unit_cd
83 AND urc.version_number = cp_version_number
84 AND urc.reference_cd_type = rct.reference_cd_type
85 AND rct.s_reference_cd_type = 'SUMMEAS'
86 AND NOT EXISTS ( SELECT refcd.reference_cd_type
87 FROM igs_ps_us_req_ref_cd refcd,
88 igs_ps_usec_ref usecref,
89 igs_ps_unit_ofr_opt_all opt,
90 igs_ge_ref_cd_type rct
91 WHERE usecref.uoo_id = cp_uoo_id
92 AND usecref.unit_section_reference_id =
93 refcd.unit_section_reference_id
94 AND refcd.reference_cd_type = rct.reference_cd_type
95 AND rct.s_reference_cd_type = 'SUMMEAS');
96
97 temp VARCHAR2(1);
98 BEGIN
99 OPEN c1(p_uoo_id,p_unit_cd,p_version_number);
100 FETCH c1 INTO temp;
101 IF c1%FOUND THEN
102 RETURN 'FALSE';
103 ELSE
104 RETURN 'TRUE';
105 END IF;
106 CLOSE c1;
107 END chk_if_excluded_unit;
108
109 FUNCTION get_mark (p_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
110 p_gs_version_number igs_as_su_stmptout.version_number%TYPE,
111 p_grade igs_as_su_stmptout.grade%TYPE)
112 RETURN NUMBER
113 IS
114
115 -- cursor to get amrk from outcome table
116 CURSOR c_mark (cp_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
117 cp_gs_version_number igs_as_su_stmptout.version_number%TYPE,
118 cp_grade igs_as_su_stmptout.grade%TYPE) IS
119 SELECT upper_mark_range
120 FROM igs_as_grd_sch_grade
121 WHERE grading_schema_cd = cp_grading_schema_cd
122 AND version_number = cp_gs_version_number
123 AND grade = cp_grade;
124 l_mark igs_as_grd_sch_grade.upper_mark_range%TYPE;
125
126 BEGIN
127 l_mark := NULL;
128 -- get the mark
129 OPEN c_mark (p_grading_schema_cd,
130 p_gs_version_number,
131 p_grade);
132 FETCH c_mark INTO l_mark;
133 CLOSE c_mark;
134 RETURN l_mark;
135 END get_mark;
136
137
138 FUNCTION get_unit_ver (p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE)
139 RETURN NUMBER
140 IS
141 -- cursor to get the unit version number
142 CURSOR c_unit_ver (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
143 SELECT version_number
144 FROM igs_ps_unit_ofr_opt
145 WHERE uoo_id = cp_uoo_id;
146 l_unit_ver igs_ps_unit_ofr_opt.version_number%TYPE := 1;
147
148 BEGIN
149 OPEN c_unit_ver (p_uoo_id);
150 FETCH c_unit_ver INTO l_unit_ver;
151 CLOSE c_unit_ver;
152 RETURN l_unit_ver;
153 END get_unit_ver;
154
155 FUNCTION get_earned_cp (p_person_id igs_as_su_stmptout.person_id%TYPE,
156 p_course_cd igs_as_su_stmptout.course_cd%TYPE,
157 p_unit_cd igs_as_su_stmptout.unit_cd%TYPE,
158 p_version_number igs_ps_unit_ver.version_number%TYPE,
159 p_unit_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE,
160 p_teach_cal_type igs_ca_inst.cal_type%TYPE,
161 p_teach_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
162 p_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
163 p_override_achievable_cp NUMBER DEFAULT NULL,
164 p_override_enrolled_cp NUMBER DEFAULT NULL)
165 RETURN NUMBER IS
166 -- cursor to get the cp defined at sua
167 CURSOR c_sua_cp (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
168 cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
169 cp_version_number igs_ps_unit_ver.version_number%TYPE) IS
170 SELECT NVL(uc.achievable_credit_points,
171 NVL(uv.achievable_credit_points,
172 NVL(p_override_enrolled_cp,
173 NVL(uc.enrolled_credit_points,uv.enrolled_credit_points
174 )
175 )
176 )
177 ) sua_cp
178 FROM igs_ps_unit_ver uv,
179 igs_ps_unit_ofr_opt uoo,
180 igs_ps_usec_cps uc
181 WHERE uoo.uoo_id = cp_uoo_id
182 AND uoo.uoo_id = uc.uoo_id(+)
183 AND uv.unit_cd = cp_unit_cd
184 AND uv.version_number = cp_version_number;
185
186 l_earned_cp NUMBER;
187 l_result_type VARCHAR2(200);
188
189
190 -- OUT variables for the assp_get_sua_outcomes
191 l_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE;
192 l_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE;
193 l_gs_version_number igs_as_su_stmptout.version_number%TYPE;
194 l_grade igs_as_su_stmptout.grade%TYPE;
195 l_mark igs_as_su_stmptout.mark%TYPE;
196 l_origin_course_cd igs_ps_ver.course_cd%TYPE;
197
198 BEGIN
199 IF p_override_achievable_cp IS NULL THEN
200 OPEN c_sua_cp (p_uoo_id,
201 p_unit_cd,
202 p_version_number);
203 FETCH c_sua_cp INTO l_earned_cp;
204 CLOSE c_sua_cp;
205 ELSE l_earned_cp := p_override_achievable_cp;
206 END IF;
207
208 -- Use the repeat functionality to determine whether the
209 -- CP is to be returned or not
210
211 /*
212
213 OPEN ISSUE
214
215 */
216
217 -- check the outcome for the unit attempt
218 l_result_type := igs_as_gen_003.assp_get_sua_outcome
219 (
220 p_person_id => p_person_id,
221 p_course_cd => p_course_cd,
222 p_unit_cd => p_unit_cd,
223 p_cal_type => p_teach_cal_type,
224 p_ci_sequence_number => p_teach_ci_sequence_number,
225 p_unit_attempt_status => p_unit_attempt_status,
226 p_finalised_ind => 'Y',
227 p_outcome_dt => l_outcome_dt,
228 p_grading_schema_cd => l_grading_schema_cd,
229 p_gs_version_number => l_gs_version_number,
230 p_grade => l_grade,
231 p_mark => l_mark,
232 p_origin_course_cd => l_origin_course_cd,
233 p_uoo_id => p_uoo_id,
234 p_use_released_ind => 'N'
235 );
236 IF l_result_type = 'PASS' THEN
237 RETURN l_earned_cp;
238 ELSE -- Result type is in WITHDRAWN, FAIL, AUDIT, INCOMP
239 RETURN NULL;
240 END IF;
241
242 END get_earned_cp;
243
244 FUNCTION get_avail_cp (p_person_id igs_as_su_stmptout.person_id%TYPE,
245 p_course_cd igs_as_su_stmptout.course_cd%TYPE,
246 p_core_ind_code igs_pr_ul_mark_dtl.core_indicator_code%TYPE,
247 p_unit_level igs_pr_ul_mark_cnfg.unit_level%TYPE)
248 RETURN NUMBER
249 IS
250 -- cursor to get the sum of available cp
251 CURSOR c_avail_cp (cp_person_id igs_as_su_stmptout.person_id%TYPE,
252 cp_course_cd igs_as_su_stmptout.course_cd%TYPE,
253 cp_core_ind_code igs_pr_ul_mark_dtl.core_indicator_code%TYPE,
254 cp_unit_level igs_pr_ul_mark_cnfg.unit_level%TYPE) IS
255 SELECT SUM(
256 get_earned_cp
257 (
258 sua.person_id,
259 sua.course_cd,
260 sua.unit_cd,
261 sua.version_number,
262 sua.unit_attempt_status,
263 sua.cal_type,
264 sua.ci_sequence_number,
265 sua.uoo_id,
266 sua.override_achievable_cp,
267 sua.override_enrolled_cp
268 )
269 ) avail_cp
270 FROM igs_en_su_attempt_all sua,
271 igs_ps_unit_lvl_all ul ,
272 igs_ps_unit_ver_all uv
273 WHERE sua.person_id = cp_person_id
274 AND sua.course_cd = p_course_cd
275 AND NVL(sua.core_indicator_code, 'ELECTIVE') = cp_core_ind_code --Core indicator filter
276 AND sua.unit_cd = uv.unit_cd
277 AND sua.version_number = uv.version_number
278 AND sua.course_cd = ul.course_cd(+)
279 AND sua.unit_cd = ul.unit_cd(+)
280 AND sua.version_number = ul.version_number(+)
281 AND NVL(UL.UNIT_LEVEL, UV.UNIT_LEVEL) = cp_unit_level
282 AND chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = 'TRUE';
283
284 l_avail_cp NUMBER:=0;
285 BEGIN
286 OPEN c_avail_cp (p_person_id ,
287 p_course_cd ,
288 p_core_ind_code ,
289 p_unit_level );
290 FETCH c_avail_cp INTO l_avail_cp;
291 CLOSE c_avail_cp;
292 RETURN l_avail_cp;
293
294 END get_avail_cp;
295
296 -- ================Utilities Method Section Ends=================
297
298 FUNCTION fn_calc_unit_lvl_mark (
299 p_person_id IN NUMBER,
300 p_course_cd IN VARCHAR2,
301 p_unit_level IN VARCHAR2,
302 x_return_status OUT NOCOPY VARCHAR2,
303 x_msg_data OUT NOCOPY VARCHAR2,
304 x_msg_count OUT NOCOPY NUMBER
305 )
306 RETURN NUMBER
307 IS
308 /*************************************************************
309 Created By : smanglm
310 Date Created on : 13-Oct-2003
311 Purpose : This package is created as part iof Summary Measurement
312 of attainment build.
313 This program unit calculate the unit level mark for the
314 given
315 p_person_id -- name of the student
316 p_course_cd -- program for which the calculation is to
317 be done
318 p_unit_level -- unit level for which the mark is being
319 calcualted
320 Change History
321 Who When What
322 Nalin Kumar 10-Feb-2004 Modified the fn_calc_unit_lvl_mark function to fix Bug# 3427366
323
324 (reverse chronological order - newest change first)
325 ***************************************************************/
326
327 -- ref type cursor
328 TYPE ref_cur IS REF CURSOR;
329
330 c_cp_based ref_cur;
331 c_priority_based ref_cur;
332 l_unit_lvl_mark NUMBER;
333 l_total_unit_lvl_cp NUMBER;
334 l_total_unit_lvl_cp_config NUMBER;
335 exlude_cp NUMBER;
336
337 -- CURSOR to get the COURSE CODE version NUMBER
338 CURSOR c_course_version_number (
339 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
340 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
341 )
342 IS
343 SELECT version_number
344 FROM igs_en_stdnt_ps_att
345 WHERE person_id = cp_person_id AND course_cd = cp_course_cd;
346
347 l_course_version_number igs_en_stdnt_ps_att.version_number%TYPE;
348
349 -- CURSOR to get the total unit level
350 CURSOR c_total_unt_lvl_cp (
351 cp_unit_level igs_pr_ul_mark_cnfg.unit_level%TYPE,
352 cp_course_cd igs_pr_ul_mark_cnfg.course_cd%TYPE,
353 cp_version_number igs_pr_ul_mark_cnfg.version_number%TYPE
354 )
355 IS
356 SELECT total_unit_level_credits, mark_config_id, selection_method_code
357 FROM igs_pr_ul_mark_cnfg umc
358 WHERE umc.unit_level = cp_unit_level
359 AND ( ( umc.course_cd = cp_course_cd
360 AND umc.version_number = cp_version_number
361 )
362 OR ( umc.course_cd IS NULL
363 AND umc.version_number IS NULL
364 AND NOT EXISTS ( SELECT 1
365 FROM igs_pr_ul_mark_cnfg umc_in
366 WHERE umc_in.unit_level = cp_unit_level
367 AND umc_in.course_cd = cp_course_cd
368 AND umc_in.version_number =
369 cp_version_number)
370 )
371 );
372
373 l_mark_config_id igs_pr_ul_mark_cnfg.mark_config_id%TYPE;
374 l_selection_method_code igs_pr_ul_mark_cnfg.selection_method_code%TYPE;
375
376 -- CURSOR to get the sum of earned cp in case total unit_level_credits is
377 -- not available from the set up table
378 CURSOR c_total_unt_lvl_cp_alt (
379 cp_unit_level igs_pr_ul_mark_cnfg.unit_level%TYPE,
380 cp_course_cd igs_pr_ul_mark_cnfg.course_cd%TYPE,
381 cp_person_id igs_en_su_attempt.person_id%TYPE,
382 cp_include VARCHAR2
383 )
384 IS
385 SELECT SUM (
386 get_earned_cp (
387 sua.person_id,
388 sua.course_cd,
389 sua.unit_cd,
390 sua.version_number,
391 sua.unit_attempt_status,
392 sua.cal_type,
393 sua.ci_sequence_number,
394 sua.uoo_id,
395 sua.override_achievable_cp,
396 sua.override_enrolled_cp
397 )
398 ) total_cp
399 FROM igs_en_su_attempt_all sua,
400 igs_ps_unit_lvl_all ul,
401 igs_ps_unit_ver_all uv
402 WHERE sua.person_id = cp_person_id
403 AND sua.course_cd = cp_course_cd
404 AND sua.unit_cd = uv.unit_cd
405 AND sua.version_number = uv.version_number
406 AND sua.course_cd = ul.course_cd(+)
407 AND sua.unit_cd = ul.unit_cd(+)
408 AND sua.version_number = ul.version_number(+)
409 AND NVL (ul.unit_level, uv.unit_level) = cp_unit_level
410 AND chk_if_excluded_unit (
411 sua.uoo_id,
412 sua.unit_cd,
413 sua.version_number
414 ) = cp_include;
415
416 -- CURSOR to get cnfg details
417 CURSOR c_ul_mark_dtl (
418 cp_mark_config_id igs_pr_ul_mark_dtl.mark_config_id%TYPE
419 )
420 IS
421 SELECT core_indicator_code, total_credits, required_flag,
422 priority_num, unit_selection_code
423 FROM igs_pr_ul_mark_dtl
424 WHERE mark_config_id = cp_mark_config_id
425 ORDER BY priority_num ASC;
426
427 rec_ul_mark_dtl c_ul_mark_dtl%ROWTYPE;
428 -- define the local variables to store the above values
429 p1_core_indicator_code igs_pr_ul_mark_dtl.core_indicator_code%TYPE;
430 p1_total_credits igs_pr_ul_mark_dtl.total_credits%TYPE;
431 p1_required_flag igs_pr_ul_mark_dtl.required_flag%TYPE;
432 p1_priority_num igs_pr_ul_mark_dtl.priority_num%TYPE;
433 p1_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
434 p2_core_indicator_code igs_pr_ul_mark_dtl.core_indicator_code%TYPE;
435 p2_total_credits igs_pr_ul_mark_dtl.total_credits%TYPE;
436 p2_required_flag igs_pr_ul_mark_dtl.required_flag%TYPE;
437 p2_priority_num igs_pr_ul_mark_dtl.priority_num%TYPE;
438 p2_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
439 p3_core_indicator_code igs_pr_ul_mark_dtl.core_indicator_code%TYPE;
440 p3_total_credits igs_pr_ul_mark_dtl.total_credits%TYPE;
441 p3_required_flag igs_pr_ul_mark_dtl.required_flag%TYPE;
442 p3_priority_num igs_pr_ul_mark_dtl.priority_num%TYPE;
443 p3_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
444 -- following local variable to manipulate the derived cp at each unit level
445 p1_avail_cp NUMBER := 0;
446 p1_config_cp NUMBER := 0;
447 p1_required_cp NUMBER := 0;
448 p1_excess_cp NUMBER := 0;
449 p1_final_derived_cp NUMBER := 0;
450 p2_avail_cp NUMBER := 0;
451 p2_config_cp NUMBER := 0;
452 p2_required_cp NUMBER := 0;
453 p2_excess_cp NUMBER := 0;
454 p2_final_derived_cp NUMBER := 0;
455 p3_avail_cp NUMBER := 0;
456 p3_config_cp NUMBER := 0;
457 p3_required_cp NUMBER := 0;
458 p3_excess_cp NUMBER := 0;
459 p3_final_derived_cp NUMBER := 0;
460
461 -- cursor to get the config details based on mark_config_id
462 CURSOR c_cnfg_dtls (
463 cp_mark_config_id igs_pr_ul_mark_dtl.mark_config_id%TYPE
464 )
465 IS
466 SELECT core_indicator_code, total_credits, required_flag,
467 priority_num, unit_selection_code
468 FROM igs_pr_ul_mark_dtl
469 WHERE mark_config_id = cp_mark_config_id
470 ORDER BY priority_num ASC;
471
472 /* jhanda */
473 CURSOR c_av_ulvl_marks (
474 cp_person_id igs_as_su_stmptout_all.person_id%TYPE,
475 cp_course_cd igs_as_su_stmptout_all.course_cd%TYPE,
476 cp_unit_level igs_ps_unit_lvl.unit_level%TYPE
477 )
478 IS
479 SELECT NVL(SUM (NVL (ulvl.unit_level_mark, 0)),0) avstdmarks,
480 SUM (NVL (ulvl.credit_points, 0)) avstdcp
481 FROM igs_av_stnd_unit_lvl_all ulvl, igs_av_adv_standing_all advstd
482 WHERE ulvl.person_id = advstd.person_id
483 AND ulvl.as_course_cd = advstd.course_cd
484 AND ulvl.as_version_number = advstd.version_number
485 AND ulvl.exemption_institution_cd = advstd.exemption_institution_cd
486 AND advstd.person_id = cp_person_id
487 AND advstd.course_cd = cp_course_cd
488 AND ulvl.unit_level = cp_unit_level
489 AND ulvl.s_adv_stnd_granting_status = 'GRANTED';
490 l_stmt_cp_based VARCHAR2 (4000)
491 := ' SELECT sua.unit_cd, '
492 || ' sua.uoo_id, '
493 || ' NVL(stmpt.mark,igs_as_calc_award_mark.get_mark '
494 || ' (stmpt.grading_schema_cd, '
495 || ' stmpt.version_number,stmpt.grade)) mark, '
496 || ' stmpt.grade, '
497 || ' igs_as_calc_award_mark.get_earned_cp '
498 || ' (stmpt.person_id,stmpt.course_cd,sua.unit_cd, '
499 || ' sua.version_number,sua.unit_attempt_status, '
500 || ' sua.cal_type,sua.ci_sequence_number, '
501 || ' sua.uoo_id,sua.override_achievable_cp, '
502 || ' sua.override_enrolled_cp ) earned_cp, '
503 || ' NVL(ul.wam_weighting,NVL( lvl.wam_weighting,1)) wam_weight '
504 || ' FROM igs_as_su_stmptout stmpt, igs_en_su_attempt sua ,IGS_PS_UNIT_LVL_ALL UL , IGS_PS_UNIT_VER_ALL UV , IGS_PS_UNIT_LEVEL_ALL LVL '
505 || ' WHERE stmpt.person_id = :1 AND stmpt.course_cd = :2 '
506 || ' AND stmpt.person_id = sua.person_id '
507 || ' AND stmpt.course_cd = sua.course_cd '
508 || ' AND stmpt.uoo_id = sua.uoo_id '
509 || ' AND NVL(sua.core_indicator_code, ''ELECTIVE'') = :3 AND '
510 || ' SUA.UNIT_CD = UV.UNIT_CD AND'
511 || ' SUA.VERSION_NUMBER = UV.VERSION_NUMBER AND'
512 || ' SUA.COURSE_CD = UL.COURSE_CD(+) AND'
513 || ' SUA.UNIT_CD = UL.UNIT_CD(+) AND'
514 || ' SUA.VERSION_NUMBER = UL.VERSION_NUMBER(+) AND'
515 || ' NVL(UL.UNIT_LEVEL, UV.UNIT_LEVEL) = :4 AND '
516 || ' LVL.UNIT_LEVEL= UV.UNIT_LEVEL'
517 || ' AND MARK IS NOT NULL '
518 || 'AND sua.uoo_id = stmpt.uoo_id'
519 || ' AND NVL(stmpt.mark,igs_as_calc_award_mark.get_mark '
520 || ' (stmpt.grading_schema_cd, '
521 || ' stmpt.version_number,stmpt.grade)) IS NOT NULL '
522 || ' AND stmpt.outcome_dt = ( SELECT max(outcome_dt) '
523 || ' FROM igs_as_su_stmptout suao '
524 || ' WHERE suao.person_id = stmpt.person_id '
525 || ' AND suao.course_cd =stmpt.course_cd '
526 || ' AND suao.outcome_dt = stmpt.outcome_dt '
527 || ' AND suao.grading_period_cd = stmpt. grading_period_cd '
528 || ' AND suao.uoo_id= stmpt.uoo_id ) '
529 || ' AND igs_as_calc_award_mark.chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = ''TRUE'' ';
530 l_stmt_cp_based_orig VARCHAR2 (4000) := l_stmt_cp_based;
531 l_stmt_priority_based VARCHAR2 (4000)
532 := ' SELECT sua.unit_cd, '
533 || ' sua.uoo_id, '
534 || ' NVL(suao.mark,igs_as_calc_award_mark.get_mark '
535 || ' (suao.grading_schema_cd,suao.version_number,suao.grade)) mark, '
536 || ' suao.grade, '
537 || ' NVL(ul.wam_weighting,NVL( lvl.wam_weighting,1)) wam_weight, '
538 || ' igs_as_calc_award_mark.get_earned_cp '
539 || ' (suao.person_id,suao.course_cd,sua.unit_cd, '
540 || ' sua.version_number,sua.unit_attempt_status, '
541 || ' sua.cal_type,sua.ci_sequence_number, '
542 || ' sua.uoo_id,sua.override_achievable_cp, '
543 || ' sua.override_enrolled_cp ) earned_cp '
544 || ' FROM igs_as_su_stmptout_all suao, igs_en_su_attempt_all sua, '
545 || ' igs_ps_unit_lvl_all ul, igs_ps_unit_ver_all uv , '
546 || ' igs_ps_unit_level_all lvl '
547 || ' WHERE suao.person_id = :1 AND suao.course_cd = :2 '
548 || ' AND suao.person_id= sua.person_id AND suao.course_cd = sua.course_cd '
549 || ' AND suao.uoo_id = sua.uoo_id AND NVL(sua.core_indicator_code, ''ELECTIVE'') = :3 '
550 || ' AND sua.unit_cd = uv.unit_cd AND sua.version_number = uv.version_number '
551 || ' AND sua.course_cd = ul.course_cd(+) AND sua.unit_cd = ul.unit_cd(+) '
552 || ' AND sua.version_number = ul.version_number(+) '
553 || ' AND NVL(ul.unit_level,uv.unit_level) = :4 '
554 || ' AND lvl.unit_level = uv.unit_level '
555 || ' AND NVL(suao.mark,igs_as_calc_award_mark.get_mark '
556 || ' (suao.grading_schema_cd,suao.version_number,suao.grade)) IS NOT NULL '
557 || ' AND suao.outcome_dt = ( SELECT max(outcome_dt) '
558 || ' FROM igs_as_su_stmptout_all suao2 '
559 || ' WHERE suao2.person_id = suao.person_id '
560 || ' AND suao2.course_cd = suao.course_cd '
561 || ' AND suao2.grading_period_cd = suao.grading_period_cd '
562 || ' AND suao2.uoo_id= suao.uoo_id ) '
563 || ' AND igs_as_calc_award_mark.chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = ''TRUE'' ';
564 l_stmt_priority_based_orig VARCHAR2 (4000) := l_stmt_priority_based;
565 -- local variables to store the output of the ref cursor
566 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
567 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
568 l_mark NUMBER;
569 l_grade igs_as_su_stmptout_all.grade%TYPE;
570 l_earned_cp NUMBER;
571 l_wam_weight igs_ps_unit_lvl.wam_weighting%TYPE;
572 /*jhanda */
573 l_advstnd_cp NUMBER;
574
575 -- cursor to fetch earned cp, mark and wam when there is no setup available
576 CURSOR c_unit_lvl_mark_wo_setup (
577 cp_person_id igs_as_su_stmptout_all.person_id%TYPE,
578 cp_course_cd igs_as_su_stmptout_all.course_cd%TYPE,
579 cp_unit_level igs_ps_unit_lvl.unit_level%TYPE
580 )
581 IS
582 SELECT sua.unit_cd, sua.uoo_id,
583 NVL (
584 suao.mark,
585 igs_as_calc_award_mark.get_mark (
586 suao.grading_schema_cd,
587 suao.version_number,
588 suao.grade
589 )
590 ) mark,
591 suao.grade,
592 NVL (ul.wam_weighting, NVL (lvl.wam_weighting, 1)) wam_weight,
593 igs_as_calc_award_mark.get_earned_cp (
594 suao.person_id,
595 suao.course_cd,
596 sua.unit_cd,
597 sua.version_number,
598 sua.unit_attempt_status,
599 sua.cal_type,
600 sua.ci_sequence_number,
601 sua.uoo_id,
602 sua.override_achievable_cp,
603 sua.override_enrolled_cp
604 )
605 earned_cp
606 FROM igs_as_su_stmptout_all suao,
607 igs_en_su_attempt_all sua,
608 igs_ps_unit_lvl_all ul,
609 igs_ps_unit_ver_all uv,
610 igs_ps_unit_level_all lvl
611 WHERE suao.person_id = cp_person_id
612 AND suao.course_cd = cp_course_cd
613 AND suao.person_id = sua.person_id
614 AND suao.course_cd = sua.course_cd
615 AND suao.uoo_id = sua.uoo_id
616 AND sua.unit_cd = uv.unit_cd
617 AND sua.version_number = uv.version_number
618 AND sua.course_cd = ul.course_cd(+)
619 AND sua.unit_cd = ul.unit_cd(+)
620 AND sua.version_number = ul.version_number(+)
621 AND NVL (ul.unit_level, uv.unit_level) = cp_unit_level
622 AND lvl.unit_level = uv.unit_level
623 AND NVL (
624 suao.mark,
625 igs_as_calc_award_mark.get_mark (
626 suao.grading_schema_cd,
627 suao.version_number,
628 suao.grade
629 )
630 ) IS NOT NULL
631 AND suao.outcome_dt = (SELECT MAX (outcome_dt)
632 FROM igs_as_su_stmptout_all suao2
633 WHERE suao2.person_id = suao.person_id
634 AND suao2.course_cd = suao.course_cd
635 AND suao2.grading_period_cd =
636 suao.grading_period_cd
637 AND suao2.uoo_id = suao.uoo_id)
638 AND chk_if_excluded_unit (
639 sua.uoo_id,
640 sua.unit_cd,
641 sua.version_number
642 ) = 'TRUE';
643 BEGIN -- main begin
644 --Initialize the variables
645 fnd_msg_pub.initialize;
646 l_total_unit_lvl_cp_config := NULL;
647 l_total_unit_lvl_cp := 0;
648 l_unit_lvl_mark := 0;
649 l_mark_config_id := NULL;
650
651 -- validate that all the IN parameters are passed
652 IF p_person_id IS NULL
653 OR p_course_cd IS NULL
654 OR p_unit_level IS NULL
655 THEN
656 fnd_message.set_name ('IGS', 'IGS_PR_CALC_UNIT_LVL_PARAM_REQ');
657 igs_ge_msg_stack.ADD;
658 RAISE fnd_api.g_exc_error;
659 END IF;
660
661 -- obtain the course version number
662 OPEN c_course_version_number (p_person_id, p_course_cd);
663 FETCH c_course_version_number INTO l_course_version_number;
664 CLOSE c_course_version_number;
665 -- check whether the set up is available or not
666 -- this cursor also serves the puprose of fetching
667 -- toal unit_level_credits,
668 -- mark_config_id, pk for the table
669 -- selection_method_code
670 OPEN c_total_unt_lvl_cp (
671 p_unit_level,
672 p_course_cd,
673 l_course_version_number
674 );
675 FETCH c_total_unt_lvl_cp INTO l_total_unit_lvl_cp_config,
676 l_mark_config_id,
677 l_selection_method_code;
678 CLOSE c_total_unt_lvl_cp;
679 /* jhanda
680 Fetch unit level advanced standing marks and cp .
681 (ijeddy) and set the adv standing marks to l_unit_lvl_mark.
682 */
683
684 OPEN c_av_ulvl_marks (p_person_id, p_course_cd, p_unit_level);
685 FETCH c_av_ulvl_marks INTO l_unit_lvl_mark, l_advstnd_cp;
686 CLOSE c_av_ulvl_marks;
687
688 IF l_mark_config_id IS NOT NULL
689 THEN --setup is available
690 /*
691 If the setup is available, following steps are carried out
692 1. Select the Total Unit Level Credits for a particular unit level
693 2. Select the various core unit indicators setups
694 3. Arrive at the CPs to be selected from each unit level
695 4. Select the student unit attempt outcomes based on the the setups from 1, 2 and 3
696 5. Calculate the unit level mark
697 */
698 -- 1. Select the Total Unit Level Credits for a particular unit level
699 -- this has been done at the time of setup check up
700 IF l_total_unit_lvl_cp_config IS NULL
701 THEN
702 -- obtain the sum of earned cp
703 -- The below cursor query basically tests for CP
704 -- at the sua attempt level. If this is not present,
705 -- then setup is checked for the unit section level.
706 -- If this is also not present then, the unit level
707 -- CP is taken into consideration
708 OPEN c_total_unt_lvl_cp_alt (
709 p_unit_level,
710 p_course_cd,
711 p_person_id,
712 'TRUE'
713 );
714 FETCH c_total_unt_lvl_cp_alt INTO l_total_unit_lvl_cp_config;
715 CLOSE c_total_unt_lvl_cp_alt;
716 ELSE
717 OPEN c_total_unt_lvl_cp_alt (
718 p_unit_level,
719 p_course_cd,
720 p_person_id,
721 'FALSE'
722 );
723 FETCH c_total_unt_lvl_cp_alt INTO exlude_cp;
724 CLOSE c_total_unt_lvl_cp_alt;
725 l_total_unit_lvl_cp_config :=
726 l_total_unit_lvl_cp_config
727 - NVL (exlude_cp, 0);
728 END IF;
729
730 -- 2. Select the various core unit indicators setups
731 OPEN c_ul_mark_dtl (l_mark_config_id);
732
733 LOOP
734 FETCH c_ul_mark_dtl INTO rec_ul_mark_dtl;
735 EXIT WHEN c_ul_mark_dtl%NOTFOUND;
736
737 -- below priority_num is assumed to have values 1,2 and 3
738 IF rec_ul_mark_dtl.priority_num = 1
739 THEN
740 p1_core_indicator_code := rec_ul_mark_dtl.core_indicator_code;
741 p1_total_credits := rec_ul_mark_dtl.total_credits;
742 p1_required_flag := rec_ul_mark_dtl.required_flag;
743 p1_priority_num := rec_ul_mark_dtl.priority_num;
744 p1_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
745 ELSIF rec_ul_mark_dtl.priority_num = 2
746 THEN
747 p2_core_indicator_code := rec_ul_mark_dtl.core_indicator_code;
748 p2_total_credits := rec_ul_mark_dtl.total_credits;
749 p2_required_flag := rec_ul_mark_dtl.required_flag;
750 p2_priority_num := rec_ul_mark_dtl.priority_num;
751 p2_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
752 ELSIF rec_ul_mark_dtl.priority_num = 3
753 THEN
754 p3_core_indicator_code := rec_ul_mark_dtl.core_indicator_code;
755 p3_total_credits := rec_ul_mark_dtl.total_credits;
756 p3_required_flag := rec_ul_mark_dtl.required_flag;
757 p3_priority_num := rec_ul_mark_dtl.priority_num;
758 p3_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
759 END IF;
760 END LOOP;
761
762 CLOSE c_ul_mark_dtl;
763
764 -- 3. Arrive at the CPs to be selected from each unit level
765
766 /*
767 This step is required only if the selection method is based on Credit Points.
768 If it is priority based, this step has to be skipped.
769
770 What is being done in this step?
771
772 In this step, the following is calculated : How much CP should be taken from
773 the students outcome table at each level. For example, if the setup states
774 that priority 1 should contribute, say, 72 cps , priority two should contribute
775 36 and priority 3 should contribute 12, but due to a shortfall in the students
776 CPs at a priority, the other priorities should compensate for this shortfall.
777 The shortfall in CPs should come from priority 1 if available, if not then it
778 should be compensated by priority 2, priority 3 depending on availibilty. Before
779 the suas are actually selected, how much each core indicator unit attempt should
780 contribute is being calculated here
781 */
782
783 -- check whether the selection criteria is credit points or priority
784 -- based on selection_method_code as obtained by above cursor c_total_unt_lvl_cp
785
786 IF l_selection_method_code = 'CREDITS'
787 THEN
788 -- store the sum of earned cp for each priority for the
789 -- given core_indicator code
790 p1_avail_cp := get_avail_cp (
791 p_person_id,
792 p_course_cd,
793 p1_core_indicator_code,
794 p_unit_level
795 );
796 p2_avail_cp := get_avail_cp (
797 p_person_id,
798 p_course_cd,
799 p2_core_indicator_code,
800 p_unit_level
801 );
802 p3_avail_cp := get_avail_cp (
803 p_person_id,
804 p_course_cd,
805 p3_core_indicator_code,
806 p_unit_level
807 );
808 --Add advanced standing credit points to the earned credit points for
809 --each of the priority levels
810 --ijeddy, since l_advstnd_cp may be NULL, added a nvl.
811 p1_avail_cp := p1_avail_cp
812 + NVL(l_advstnd_cp,0);
813 p1_config_cp := NVL (p1_total_credits, 0);
814 p2_config_cp := NVL (p2_total_credits, 0);
815 p3_config_cp := NVL (p3_total_credits, 0);
816 p1_required_cp := p1_config_cp;
817
818 IF NVL (p3_avail_cp, 0)
819 - NVL (p3_config_cp, 0) <= 0
820 THEN -- this section deals with shortage
821 IF p3_required_flag = 'N'
822 THEN
823 p1_required_cp :=
824 p1_required_cp
825 + NVL (( p3_config_cp
826 - p3_avail_cp
827 ), 0);
828 p3_final_derived_cp := p3_avail_cp;
829 ELSE
830 p3_final_derived_cp := p3_avail_cp;
831 END IF;
832 ELSE -- IF p3_avail_cp - p3_config_cp < 0 THEN i.e. this section deals with surplus
833 p3_final_derived_cp := p3_config_cp;
834 p3_excess_cp := NVL (( p3_avail_cp
835 - p3_config_cp
836 ), 0);
837 END IF;
838
839 IF NVL (p2_avail_cp, 0)
840 - NVL (p2_config_cp, 0) <= 0
841 THEN
842 IF p2_required_flag = 'N'
843 THEN
844 p1_required_cp :=
845 p1_required_cp
846 + NVL (( p2_config_cp
847 - p2_avail_cp
848 ), 0);
849 p2_final_derived_cp := p2_avail_cp;
850 ELSE
851 p2_final_derived_cp := p2_avail_cp;
852 END IF;
853 ELSE
854 p2_final_derived_cp := p2_config_cp;
855 p2_excess_cp := NVL (( p2_avail_cp
856 - p2_config_cp
857 ), 0);
858 END IF;
859
860 /*
861 The above section basically puts any shortfall in priorities 2 and 3 into 1.
862 The above also checks if the required indicator is checked at the priority
863 level is checked or not. If it is checked, then the compensation or rollover
864 should not occur.
865
866 The next section tries to compensate any shortfall in priority 1 through 2 and 3.
867 */
868 IF ( p1_avail_cp
869 - p1_required_cp
870 ) < 0
871 THEN
872 IF p2_excess_cp > 0
873 THEN
874 IF ( NVL (p2_excess_cp, 0)
875 - (NVL (( p1_required_cp
876 - p1_avail_cp
877 ), 0)
878 )
879 ) > 0
880 THEN
881 IF p1_required_flag = 'N'
882 THEN
883 p2_final_derived_cp :=
884 p2_config_cp
885 + ( p1_required_cp
886 - p1_avail_cp
887 );
888 p1_final_derived_cp := p1_avail_cp;
889 p3_final_derived_cp := p3_avail_cp;
890 ELSE -- required = 'Y'
891 p1_final_derived_cp := p1_config_cp;
892 p2_final_derived_cp := p2_avail_cp;
893 p1_final_derived_cp := p3_avail_cp;
894 END IF; -- IF p1_required_flag = 'N' THEN
895 ELSE -- IF (p2_excess_cp - (p1_required_cp - p1_avail_cp)) >=0 THEN
896 p2_final_derived_cp :=
897 NVL (p2_config_cp, 0)
898 + NVL (p2_excess_cp, 0);
899 p1_required_cp :=
900 NVL (p1_required_cp, 0)
901 - NVL (p2_excess_cp, 0);
902 p2_final_derived_cp := p1_required_cp;
903
904 IF NVL (p3_excess_cp, 0) > 0
905 THEN
906 IF p1_required_flag = 'N'
907 THEN
908 IF ( NVL (p3_excess_cp, 0)
909 - NVL (( p1_required_cp
910 - p1_avail_cp
911 ), 0)
912 ) > 0
913 THEN
914 p3_final_derived_cp :=
915 NVL (p3_config_cp, 0)
916 + ( ( NVL (p1_required_cp, 0)
917 - NVL (p1_avail_cp, 0)
918 )
919 - NVL (p3_excess_cp, 0)
920 );
921 p1_final_derived_cp := p1_avail_cp;
922 ELSE
923 p1_final_derived_cp := p1_config_cp;
924 p3_final_derived_cp := p3_config_cp;
925 END IF;
926 ELSE
927 p1_final_derived_cp := p1_config_cp;
928 p3_final_derived_cp := p3_config_cp;
929 END IF;
930 END IF; -- IF p3_excess_cp > 0 THEN
931 END IF; -- IF (p2_excess_cp - (p1_required_cp - p1_avail_cp)) >= THEN
932 ELSE -- IF p2_excess_cp > 0 THEN
933 IF NVL (p3_excess_cp, 0) > 0
934 THEN
935 IF ( NVL (p3_excess_cp, 0)
936 - ( NVL (p1_required_cp, 0)
937 - NVL (p1_avail_cp, 0)
938 )
939 ) > 0
940 THEN
941 IF p1_required_flag = 'N'
942 THEN
943 p3_final_derived_cp := NVL (p3_config_cp, 0)
944 + ( ( NVL (
945 p1_required_cp,
946 0
947 )
948 - NVL (p1_avail_cp, 0)
949 )
950 - NVL (p3_excess_cp, 0)
951 );
952 p1_final_derived_cp := p1_avail_cp;
953 ELSE
954 p1_final_derived_cp := NVL (p1_config_cp, 0);
955 p3_final_derived_cp := NVL (p3_config_cp, 0);
956 END IF;
957 ELSE -- IF (p3_excess_cp - (p1_required_cp - p1_avail_cp)) > 0 THEN
958 IF p1_required_flag = 'N'
959 THEN
960 p3_final_derived_cp := NVL (p3_avail_cp, 0);
961 p1_final_derived_cp := p1_avail_cp;
962 ELSE
963 p1_final_derived_cp := p1_config_cp;
964 p3_final_derived_cp := NVL (p3_config_cp, 0);
965 END IF;
966 END IF; -- IF (p3_excess_cp - (p1_required_cp - p1_avail_cp)) > 0 THEN
967 ELSE
968 p1_final_derived_cp := p1_avail_cp;
969 p2_final_derived_cp := NVL (p2_avail_cp, 0);
970 p3_final_derived_cp := NVL (p3_avail_cp, 0);
971 END IF; -- IF p3_excess_cp > 0 THEN
972 END IF; -- IF p2_excess_cp > 0 THEN
973 ELSE -- IF (p1_avail_cp - p1_required_cp) < 0 THEN
974 p1_final_derived_cp := p1_required_cp;
975
976 IF NVL (p2_config_cp, 0) > NVL (p2_avail_cp, 0)
977 THEN
978 p2_final_derived_cp := NVL (p2_avail_cp, 0);
979 ELSE
980 p2_final_derived_cp := p2_config_cp;
981 END IF;
982
983 IF NVL (p3_config_cp, 0) > NVL (p3_avail_cp, 0)
984 THEN
985 p3_final_derived_cp := NVL (p3_avail_cp, 0);
986 ELSE
987 p3_final_derived_cp := p3_config_cp;
988 END IF;
989 END IF; -- IF (p1_avail_cp - p1_required_cp) < 0 THEN
990 END IF; -- IF l_selection_method_code = 'CREDITS' THEN
991
992 /*
993 At the end of the above calculation,
994 p1_final_derived_cp, p2_final_derived_cp and p3_final_derived_cp are determined.
995 Thus how much each priority should contribute has been defined.
996 */
997
998 -- 4. Select the student unit attempt outcomes based on the the setups from 1, 2 and 3
999
1000 -- check out the selection method code
1001 IF l_selection_method_code = 'PRIORITY'
1002 THEN
1003 FOR rec_cnfg_dtls IN c_cnfg_dtls (l_mark_config_id)
1004 LOOP
1005 -- decide the order by clause
1006 IF rec_cnfg_dtls.unit_selection_code = 'BEST_MARK'
1007 THEN
1008 l_stmt_priority_based :=
1009 l_stmt_priority_based_orig
1010 || ' ORDER BY mark desc ';
1011 ELSE
1012 l_stmt_priority_based := l_stmt_priority_based_orig
1013 || ' ORDER BY suao.creation_date desc';
1014 END IF;
1015
1016 -- now open the ref cursor
1017 OPEN c_priority_based FOR l_stmt_priority_based
1018 USING p_person_id,
1019 p_course_cd,
1020 rec_cnfg_dtls.core_indicator_code,
1021 p_unit_level;
1022
1023 LOOP
1024 FETCH c_priority_based INTO l_unit_cd,
1025 l_uoo_id,
1026 l_mark,
1027 l_grade,
1028 l_wam_weight,
1029 l_earned_cp;
1030 EXIT WHEN c_priority_based%NOTFOUND;
1031
1032 IF ( l_total_unit_lvl_cp_config
1033 - ( l_total_unit_lvl_cp
1034 + l_earned_cp
1035 )
1036 ) >= 0
1037 THEN
1038 l_total_unit_lvl_cp := l_total_unit_lvl_cp
1039 + l_earned_cp;
1040 l_unit_lvl_mark := l_unit_lvl_mark
1041 + (l_wam_weight * l_earned_cp * l_mark);
1042 END IF;
1043 END LOOP;
1044 CLOSE c_priority_based;
1045 END LOOP; -- FOR rec_cnfg_dtls IN c_cnfg_dtls
1046 ELSE -- else of IF l_selection_method_code = 'PRIORITY' THEN i.e. CREDITS
1047 FOR rec_cnfg_dtls IN c_cnfg_dtls (l_mark_config_id)
1048 LOOP
1049 -- decide the order by clause
1050 IF rec_cnfg_dtls.unit_selection_code = 'BEST_MARK'
1051 THEN
1052 l_stmt_cp_based :=
1053 l_stmt_cp_based_orig
1054 || ' ORDER BY mark desc ';
1055 ELSE
1056 l_stmt_cp_based := l_stmt_cp_based_orig
1057 || ' ORDER BY stmpt.creation_date desc';
1058 END IF;
1059
1060 -- now open the ref cursor
1061 OPEN c_cp_based FOR l_stmt_cp_based
1062 USING p_person_id,
1063 p_course_cd,
1064 rec_cnfg_dtls.core_indicator_code,
1065 p_unit_level;
1066
1067 LOOP
1068 FETCH c_cp_based INTO l_unit_cd,
1069 l_uoo_id,
1070 l_mark,
1071 l_grade,
1072 l_earned_cp,
1073 l_wam_weight;
1074 EXIT WHEN c_cp_based%NOTFOUND;
1075
1076 -- below priority_num is assumed to have values 1,2 and 3
1077 IF rec_cnfg_dtls.priority_num = 1
1078 THEN
1079 IF p1_final_derived_cp
1080 - l_earned_cp >= 0
1081 THEN
1082 p1_final_derived_cp :=
1083 p1_final_derived_cp
1084 - l_earned_cp;
1085 l_unit_lvl_mark := l_unit_lvl_mark
1086 + ( NVL (l_wam_weight, 1)
1087 * l_earned_cp
1088 * l_mark
1089 );
1090 END IF;
1091 ELSIF rec_cnfg_dtls.priority_num = 2
1092 THEN
1093 IF p2_final_derived_cp
1094 - l_earned_cp >= 0
1095 THEN
1096 p2_final_derived_cp :=
1097 p2_final_derived_cp
1098 - l_earned_cp;
1099 l_unit_lvl_mark := l_unit_lvl_mark
1100 + ( NVL (l_wam_weight, 1)
1101 * l_earned_cp
1102 * l_mark
1103 );
1104 END IF;
1105 ELSIF rec_cnfg_dtls.priority_num = 3
1106 THEN
1107 IF p3_final_derived_cp
1108 - l_earned_cp >= 0
1109 THEN
1110 p3_final_derived_cp :=
1111 p3_final_derived_cp
1112 - l_earned_cp;
1113 l_unit_lvl_mark := l_unit_lvl_mark
1114 + ( NVL (l_wam_weight, 1)
1115 * l_earned_cp
1116 * l_mark
1117 );
1118 END IF;
1119 END IF; -- IF rec_cnfg_dtls.priority_num = 1 THEN
1120 END LOOP; -- OPEN c_cp_based FOR l_stmt_cp_based;
1121 CLOSE c_cp_based;
1122 END LOOP; -- FOR rec_cnfg_dtls IN c_cnfg_dtls
1123 END IF; -- end of IF l_selection_method_code = 'CREDITS' THEN
1124
1125 -- 5. Calculate the unit level mark
1126 l_unit_lvl_mark := l_unit_lvl_mark / NVL (l_total_unit_lvl_cp_config, 1);
1127 ELSE -- IF l_mark_config_id IS NOT NULL THEN i.e. setup is not available
1128 -- Get the total cp that is to be used as denominator for calculation of unit level mark
1129 OPEN c_total_unt_lvl_cp_alt (
1130 p_unit_level,
1131 p_course_cd,
1132 p_person_id,
1133 'TRUE'
1134 );
1135 FETCH c_total_unt_lvl_cp_alt INTO l_total_unit_lvl_cp_config;
1136 CLOSE c_total_unt_lvl_cp_alt;
1137
1138 FOR rec IN c_unit_lvl_mark_wo_setup (
1139 p_person_id,
1140 p_course_cd,
1141 p_unit_level
1142 )
1143 LOOP
1144 l_unit_lvl_mark :=
1145 l_unit_lvl_mark
1146 + (rec.mark * rec.earned_cp * rec.wam_weight);
1147 END LOOP;
1148
1149 -- jhanda
1150 l_total_unit_lvl_cp_config := NVL (l_total_unit_lvl_cp_config,0) + NVL(l_advstnd_cp,0);
1151 IF (l_total_unit_lvl_cp_config = 0 ) THEN
1152 l_total_unit_lvl_cp_config := 1;
1153 END IF;
1154 l_unit_lvl_mark := l_unit_lvl_mark / l_total_unit_lvl_cp_config;
1155 END IF; -- IF l_mark_config_id IS NOT NULL THEN
1156
1157 -- Initialize API return status to success.
1158 x_return_status := fnd_api.g_ret_sts_success;
1159 -- Standard call to get message count and if count is 1, get message
1160 -- info.
1161 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1162
1163 IF l_unit_lvl_mark IS NOT NULL
1164 THEN
1165 RETURN TO_NUMBER (TO_CHAR (l_unit_lvl_mark, '999.999'));
1166 ELSE
1167 RETURN l_unit_lvl_mark;
1168 END IF;
1169 EXCEPTION
1170 WHEN fnd_api.g_exc_error
1171 THEN
1172 l_unit_lvl_mark := NULL;
1173 x_return_status := fnd_api.g_ret_sts_error;
1174 fnd_msg_pub.count_and_get (
1175 p_count=> x_msg_count,
1176 p_data=> x_msg_data
1177 );
1178 RETURN TO_NUMBER (NULL);
1179 WHEN fnd_api.g_exc_unexpected_error
1180 THEN
1181 l_unit_lvl_mark := NULL;
1182 x_return_status := fnd_api.g_ret_sts_unexp_error;
1183 fnd_msg_pub.count_and_get (
1184 p_count=> x_msg_count,
1185 p_data=> x_msg_data
1186 );
1187 RETURN TO_NUMBER (NULL);
1188 WHEN OTHERS
1189 THEN
1190 l_unit_lvl_mark := NULL;
1191 x_return_status := fnd_api.g_ret_sts_unexp_error;
1192 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1193 fnd_message.set_token ('NAME', 'pr_calc_unit_lvl_mark : '
1194 || SQLERRM);
1195 fnd_msg_pub.ADD;
1196 fnd_msg_pub.count_and_get (
1197 p_count=> x_msg_count,
1198 p_data=> x_msg_data
1199 );
1200 RETURN l_unit_lvl_mark;
1201 END fn_calc_unit_lvl_mark;
1202
1203
1204 PROCEDURE get_hnr_grade(
1205 p_award_cd VARCHAR2,
1206 p_sum NUMBER ,
1207 p_honors_level OUT NOCOPY VARCHAR2,
1208 p_grad_sch_code OUT NOCOPY VARCHAR2,
1209 p_grad_Version_num OUT NOCOPY NUMBER) IS
1210 /*
1211 ||==============================================================================||
1212 || Created By : Nalin Kumar ||
1213 || Created On : 03-Jun-2004 ||
1214 || Purpose : To find the Grading Schema and version number attached to the Award||
1215 || Known limitations, enhancements or remarks : ||
1216 || Change History : ||
1217 || Who When What ||
1218 || (reverse chronological order - newest change first) ||
1219 ||==============================================================================||
1220 */
1221 -- cursor to derive the grade (honors level)
1222 CURSOR c_honors_level (cp_award_cd igs_ps_awd.award_cd%TYPE,
1223 cp_sum NUMBER) IS
1224 SELECT gsg.grade, gs.grading_schema_cd, gs.version_number
1225 FROM igs_as_grd_schema gs,
1226 igs_as_grd_sch_grade gsg,
1227 igs_ps_awd pa
1228 WHERE gs.grading_schema_cd = gsg.grading_schema_cd
1229 AND gs.version_number = gsg.version_number
1230 AND gs.grading_schema_type = 'HONORS'
1231 AND pa.award_cd = cp_award_cd
1232 AND pa.grading_schema_cd = gsg.grading_schema_cd
1233 AND pa.gs_version_number = gsg.version_number
1234 AND NVL(gsg.lower_mark_range, 1) <= NVL((cp_sum), NVL(gsg.lower_mark_range, 1))
1235 AND NVL(gsg.upper_mark_range, 1) >= NVL((cp_sum), NVL(gsg.upper_mark_range, 1));
1236
1237 CURSOR c_honors_level1 (cp_sum NUMBER) IS
1238 SELECT gsg.grade, gs.grading_schema_cd, gs.version_number
1239 FROM igs_as_grd_schema gs,
1240 igs_as_grd_sch_grade gsg
1241 WHERE gs.grading_schema_cd = gsg.grading_schema_cd
1242 AND gs.version_number = gsg.version_number
1243 AND gs.grading_schema_type = 'HONORS'
1244 AND gs.start_dt <= SYSDATE
1245 AND nvl(gs.end_dt,SYSDATE) >= SYSDATE
1246 AND gsg.lower_mark_range <= (cp_sum)
1247 AND gsg.upper_mark_range >= (cp_sum);
1248
1249 l_grading_schema_cd IGS_AS_GRD_SCH_GRADE.GRADING_SCHEMA_CD%TYPE;
1250 l_gs_version_number IGS_AS_GRD_SCH_GRADE.VERSION_NUMBER%TYPE;
1251 l_honors_level igs_as_grd_sch_grade.grade%TYPE; -- stores honors level
1252 l_honors_level_not_used VARCHAR2(30);
1253 BEGIN
1254 OPEN c_honors_level (p_award_cd,p_sum);
1255 FETCH c_honors_level INTO l_honors_level, l_grading_schema_cd, l_GS_VERSION_NUMBER;
1256 CLOSE c_honors_level;
1257
1258 IF l_grading_schema_cd IS NULL THEN
1259 OPEN c_honors_level1 (p_sum);
1260 FETCH c_honors_level1 INTO l_honors_level, l_grading_schema_cd, l_GS_VERSION_NUMBER;
1261 CLOSE c_honors_level1;
1262 END IF;
1263 --Cursor to get the grading schema and version associated with award. So that if none of the
1264 --GS cover the range of mark calculated, return the Grading schema and version
1265 -- so that user can select from the lov in the form.
1266
1267 IF l_grading_schema_cd IS NULL THEN
1268 OPEN c_honors_level (p_award_cd, TO_NUMBER(NULL));
1269 FETCH c_honors_level INTO l_honors_level_not_used, l_grading_schema_cd, l_GS_VERSION_NUMBER;
1270 CLOSE c_honors_level;
1271 END IF;
1272 p_honors_level := l_honors_level;
1273 p_grad_sch_code := l_grading_schema_cd;
1274 p_grad_Version_num := l_GS_VERSION_NUMBER;
1275 END get_hnr_grade;
1276
1277 PROCEDURE pr_calc_award_mark (
1278 p_person_id IN NUMBER,
1279 p_course_cd IN VARCHAR2,
1280 p_award_cd IN VARCHAR2,
1281 p_award_mark OUT NOCOPY NUMBER,
1282 p_honors_level OUT NOCOPY VARCHAR2,
1283 p_grading_schema_cd OUT NOCOPY VARCHAR2,
1284 p_version_number OUT NOCOPY NUMBER,
1285 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1286 X_MSG_DATA OUT NOCOPY VARCHAR2,
1287 X_MSG_COUNT OUT NOCOPY NUMBER) IS
1288 /*
1289 Description of the parameters:
1290 IN PARAMETERS
1291 p_person_id - The ID of the person to calculate an award
1292 p_course_cd - The Program (Course Code) for which the award mark
1293 has to be calculated
1294 p_award_cd - Award Code for the which the award nark has to be
1295 calculated
1296
1297 OUT PARAMETERS
1298 p_award_mark - Calculated award mark
1299 p_honors_level - Derived honours level based on the calculated
1300 award mark
1301 x_return_status - Stores the success or failure of the program unit
1302 x_msg_data - Stores the error message
1303 x_msg_count - stores the count of error
1304
1305 */
1306
1307 -- cursor to fetch all associated unit level with the passed award code. Note that if there is no configuration done here for
1308 -- the unit level in table igs_ps_awd_hnr_base and the student has attempted some unit at that level, then the weghted avg mark should be
1309 -- considered as 1.
1310 CURSOR c_unit_levels (cp_award_cd igs_ps_awd.award_cd%TYPE) IS
1311 SELECT unit_level, weighted_average
1312 FROM igs_ps_awd_hnr_base
1313 WHERE award_cd = cp_award_cd
1314 AND unit_level IS NOT NULL
1315 UNION
1316 SELECT NVL(ul.unit_level, uv.unit_level) AS unit_level, 1 AS weighted_average
1317 FROM igs_en_su_attempt_all sua ,
1318 igs_ps_unit_ver_all uv, igs_ps_unit_lvl_all ul
1319 WHERE
1320 sua.unit_cd = uv.unit_cd AND
1321 sua.version_number = uv.version_number AND
1322 sua.course_cd = ul.course_cd(+) AND
1323 sua.unit_cd = ul.unit_cd(+) AND
1324 sua.version_number = ul.version_number(+) AND
1325 sua.person_id = p_person_id and sua.course_cd = p_course_cd AND
1326 NOT EXISTS (SELECT 1 FROM igs_ps_awd_hnr_base hb WHERE NVL(ul.unit_level, uv.unit_level) = hb.unit_level);
1327
1328 -- cursor to get the stat details
1329 CURSOR c_stat_dtls (cp_award_cd igs_ps_awd.award_cd%TYPE) IS
1330 SELECT stat_type,
1331 s_stat_element,
1332 timeframe
1333 FROM igs_ps_awd_hnr_base
1334 WHERE award_cd = cp_award_cd
1335 AND stat_type IS NOT NULL;
1336
1337 rec_stat_dtls c_stat_dtls%ROWTYPE;
1338
1339 -- cursor to get the load cal tpe and sequence_number
1340 CURSOR c_load_dtls (cp_person_id igs_en_su_attempt.person_id%TYPE,
1341 cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
1342 SELECT load_cal_type,
1343 load_ci_sequence_number
1344 FROM igs_ca_teach_to_load_v
1345 WHERE (teach_cal_type,teach_ci_sequence_number)
1346 IN( SELECT cal_type, ci_sequence_number
1347 FROM (SELECT cal_type, ci_sequence_number
1348 FROM igs_en_su_attempt
1349 WHERE course_cd = cp_course_cd
1350 AND person_id = cp_person_id
1351 AND unit_attempt_status IN ('COMPLETED','DUPLICATE','ENROLLED')
1352 ORDER BY ci_start_dt DESC)
1353 WHERE rownum = 1)
1354 ORDER BY load_start_dt DESC;
1355 rec_load_dtls c_load_dtls%ROWTYPE;
1356
1357 --local variables
1358 l_unit_level_mark NUMBER; -- stores unit level mark
1359 l_sum NUMBER; -- sum of l_unit_level_mark*l_unit_level_wam
1360 l_honors_level igs_as_grd_sch_grade.grade%TYPE; -- stores honors level
1361 l_gpa_value NUMBER;
1362 l_gpa_cp NUMBER;
1363 l_gpa_quality_points NUMBER;
1364 l_acad_cal_type VARCHAR2(30);
1365 l_acad_ci_seq_num NUMBER;
1366 l_load_cal_type VARCHAR2(30);
1367 l_load_ci_seq_num NUMBER;
1368 l_load_ci_alt_code VARCHAR2(30);
1369 l_load_ci_start_dt DATE;
1370 l_load_ci_end_dt DATE;
1371 l_message_name VARCHAR2(30):=NULL;
1372 l_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
1373 l_gs_version_number igs_as_grd_sch_grade.version_number%TYPE;
1374 BEGIN
1375 --Initialize the variables
1376 FND_MSG_PUB.initialize;
1377 l_unit_level_mark :=0;
1378 l_sum :=0;
1379 -- validate the IN parameters for null values
1380 -- raise error msg IGS_PR_CALC_AWD_MARK_PARAM_REQ
1381 -- in case of violation
1382 IF p_person_id IS NULL OR
1383 p_course_cd IS NULL OR
1384 p_award_cd IS NULL THEN
1385 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_CALC_AWD_MARK_PARAM_REQ');
1386 IGS_GE_MSG_STACK.ADD;
1387 RAISE FND_API.G_EXC_ERROR;
1388 END IF;
1389
1390 -- check for the career enabled model
1391 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N' THEN -- i..e Program Centric
1392 -- Loop through the various unit levels fetched by the cursor
1393 -- make the call to calc_unit_level_mark(person_id,program, unit_level)
1394 -- and keep adding to a local variable
1395 FOR rec_unit_levels IN c_unit_levels (p_award_cd)LOOP
1396 l_unit_level_mark := fn_calc_unit_lvl_mark(
1397 p_person_id => p_person_id,
1398 p_course_cd => p_course_cd,
1399 p_unit_level => rec_unit_levels.unit_level,
1400 X_RETURN_STATUS => X_RETURN_STATUS,
1401 X_MSG_DATA => X_MSG_DATA,
1402 X_MSG_COUNT => X_MSG_COUNT);
1403 l_sum := l_sum + l_unit_level_mark*nvl(rec_unit_levels.weighted_average,1);
1404 END LOOP; --FOR rec_unit_levels IN c_unit_levels
1405 -- so, l_sum has the calculated award mark
1406 -- now, derive the honors level
1407
1408 get_hnr_grade(
1409 p_award_cd ,
1410 l_Sum ,
1411 l_honors_level ,
1412 l_grading_schema_cd,
1413 l_GS_VERSION_NUMBER);
1414
1415 p_grading_schema_cd := l_grading_schema_cd ;
1416 p_version_number := l_gs_version_number;
1417
1418
1419
1420
1421 ELSE -- i.e. Career Enabled
1422 -- get the stat details
1423 OPEN c_stat_dtls(p_award_cd);
1424 FETCH c_stat_dtls INTO rec_stat_dtls;
1425 CLOSE c_stat_dtls;
1426
1427 --Get the load cal and load ci sequence number
1428 -- this is to avoid dependency on igs_en_gen_015
1429 OPEN c_load_dtls(p_person_id,p_course_cd);
1430 FETCH c_load_dtls INTO rec_load_dtls;
1431 CLOSE c_load_dtls;
1432
1433 -- now get the GPA
1434 igs_pr_cp_gpa.get_gpa_stats
1435 ( p_person_id => p_person_id,
1436 p_course_cd => p_course_cd,
1437 p_stat_type => rec_stat_dtls.stat_type, --Pass NULL if no setup is done...
1438 p_load_cal_type => rec_load_dtls.load_cal_type,
1439 p_load_ci_sequence_number => rec_load_dtls.load_ci_sequence_number,
1440 p_system_stat => NULL,
1441 p_cumulative_ind => 'Y',
1442 p_gpa_value => l_gpa_value,
1443 p_gpa_cp => l_gpa_cp,
1444 p_gpa_quality_points => l_gpa_quality_points,
1445 p_init_msg_list => FND_API.G_TRUE,
1446 p_return_status => X_RETURN_STATUS,
1447 p_msg_count => X_MSG_COUNT,
1448 p_msg_data => X_MSG_DATA
1449 );
1450
1451 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1452 FND_MESSAGE.SET_NAME('IGS',X_MSG_DATA);
1453 IGS_GE_MSG_STACK.ADD;
1454 RAISE FND_API.G_EXC_ERROR;
1455 END IF;
1456 -- now derive the honofrs level
1457 l_sum := l_gpa_value;
1458 get_hnr_grade(
1459 p_award_cd,
1460 l_Sum,
1461 l_honors_level,
1462 l_grading_schema_cd,
1463 l_gs_version_number);
1464
1465 p_grading_schema_cd := l_grading_schema_cd;
1466 p_version_number := l_gs_version_number;
1467
1468 END IF;
1469
1470 --IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N' THEN
1471 -- now assign the out parameters
1472 p_award_mark := l_sum;
1473 p_honors_level := l_honors_level;
1474 -- Initialize API return status to success.
1475 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1476 -- Standard call to get message count and if count is 1, get message info.
1477 FND_MSG_PUB.Count_And_Get(
1478 p_count => x_MSG_COUNT,
1479 p_data => X_MSG_DATA);
1480 EXCEPTION
1481 WHEN FND_API.G_EXC_ERROR THEN
1482 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1483 FND_MSG_PUB.Count_And_Get(
1484 p_count => x_MSG_COUNT,
1485 p_data => X_MSG_DATA);
1486 RETURN;
1487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1488 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1489 FND_MSG_PUB.Count_And_Get(
1490 p_count => x_MSG_COUNT,
1491 p_data => X_MSG_DATA);
1492 RETURN;
1493 WHEN OTHERS THEN
1494 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1495 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1496 FND_MESSAGE.SET_TOKEN('NAME','pr_calc_award_mark : '||SQLERRM);
1497 FND_MSG_PUB.ADD;
1498 FND_MSG_PUB.Count_And_Get(
1499 p_count => x_MSG_COUNT,
1500 p_data => X_MSG_DATA);
1501 RETURN;
1502 END pr_calc_award_mark;
1503
1504 FUNCTION fn_calc_award_mark(
1505 p_person_id IN NUMBER,
1506 p_course_cd IN VARCHAR2,
1507 p_award_cd IN VARCHAR2 ,
1508 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1509 X_MSG_DATA OUT NOCOPY VARCHAR2,
1510 X_MSG_COUNT OUT NOCOPY NUMBER) RETURN NUMBER IS
1511
1512 -- define variable for OUT params to call pr_calc_award_mark
1513 l_award_mark NUMBER;
1514 l_honors_level VARCHAR2(1000);
1515 l_grading_schema_cd VARCHAR2(30);
1516 l_gs_version_number NUMBER(30);
1517 BEGIN
1518 -- call pr_calc_award_mark
1519 pr_calc_award_mark(
1520 p_person_id => p_person_id,
1521 p_course_cd => p_course_cd,
1522 p_award_cd => p_award_cd,
1523 p_award_mark => l_award_mark,
1524 p_honors_level => l_honors_level,
1525 p_grading_schema_cd => l_grading_schema_cd,
1526 p_version_number => l_gs_version_number,
1527 X_RETURN_STATUS => X_RETURN_STATUS,
1528 X_MSG_DATA => X_MSG_DATA,
1529 X_MSG_COUNT => X_MSG_COUNT);
1530
1531 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1532 FND_MESSAGE.SET_NAME('IGS',X_MSG_DATA);
1533 IGS_GE_MSG_STACK.ADD;
1534 RAISE FND_API.G_EXC_ERROR;
1535 END IF;
1536 --Calculation sucessful hence return properly with value.
1537 RETURN l_award_mark;
1538
1539 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1540 -- Standard call to get message count and if count is 1, get message info.
1541 FND_MSG_PUB.Count_And_Get(
1542 p_count => x_MSG_COUNT,
1543 p_data => X_MSG_DATA);
1544 EXCEPTION
1545 WHEN FND_API.G_EXC_ERROR THEN
1546 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1547 FND_MSG_PUB.Count_And_Get(
1548 p_count => x_MSG_COUNT,
1549 p_data => X_MSG_DATA);
1550 RETURN l_award_mark;
1551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1552 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1553 FND_MSG_PUB.Count_And_Get(
1554 p_count => x_MSG_COUNT,
1555 p_data => X_MSG_DATA);
1556 RETURN l_award_mark;
1557 WHEN OTHERS THEN
1558 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1559 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1560 FND_MESSAGE.SET_TOKEN('NAME','pr_calc_award_mark : '||SQLERRM);
1561 FND_MSG_PUB.ADD;
1562 FND_MSG_PUB.Count_And_Get(
1563 p_count => x_MSG_COUNT,
1564 p_data => X_MSG_DATA);
1565 RETURN l_award_mark;
1566 END fn_calc_award_mark;
1567
1568 FUNCTION fn_derive_honors_level (p_person_id IN NUMBER,
1569 p_course_cd IN VARCHAR2,
1570 p_award_cd IN VARCHAR2 ) RETURN VARCHAR2 IS
1571 -- define variable for OUT params to call pr_calc_award_mark
1572 l_award_mark NUMBER;
1573 l_honors_level VARCHAR2(1000);
1574 l_return_status VARCHAR2(100);
1575 l_msg_data VARCHAR2(2000);
1576 l_msg_count NUMBER;
1577 l_grading_schema_cd VARCHAR2(30);
1578 l_gs_version_number NUMBER(30);
1579 BEGIN
1580 -- call pr_calc_award_mark
1581 pr_calc_award_mark(
1582 p_person_id => p_person_id,
1583 p_course_cd => p_course_cd,
1584 p_award_cd => p_award_cd,
1585 p_award_mark => l_award_mark,
1586 p_honors_level => l_honors_level,
1587 p_grading_schema_cd => l_grading_schema_cd,
1588 p_version_number => l_gs_version_number,
1589 X_RETURN_STATUS => l_return_status,
1590 X_MSG_DATA => l_msg_data,
1591 X_MSG_COUNT => l_msg_count);
1592 RETURN l_honors_level;
1593 END fn_derive_honors_level;
1594
1595 PROCEDURE upgrade_awards (errbuff OUT NOCOPY VARCHAR2,
1596 retcode OUT NOCOPY NUMBER,
1597 p_award_cd igs_ps_awd.AWARD_CD%TYPE) IS
1598 -- Cursor to get all the award aims which are to be updated with the award marks and award grades...
1599 CURSOR cur_spaa(cp_awd_cd VARCHAR2) IS
1600 SELECT spaa.rowid row_id, spaa.*
1601 FROM igs_en_spa_awd_aim spaa
1602 WHERE spaa.AWARD_CD = cp_awd_cd AND
1603 spaa.AWARD_MARK IS NULL AND
1604 spaa.AWARD_GRADE IS NULL;
1605
1606 CURSOR Cur_awd_grd_sch IS
1607 SELECT pa.grading_schema_cd, pa.gs_version_number
1608 FROM igs_ps_awd pa
1609 WHERE pa.award_cd = p_award_cd;
1610
1611 CURSOR Cur_Awd_grd(cp_grd_sch VARCHAR2, cp_grd_ver NUMBER, cp_grade VARCHAR2) IS
1612 SELECT gsg.grade, gs.grading_schema_cd, gs.version_number,
1613 gsg.lower_mark_range, gsg.upper_mark_range
1614 FROM igs_as_grd_schema gs, igs_as_grd_sch_grade gsg
1615 WHERE gs.grading_schema_cd = gsg.grading_schema_cd
1616 AND gs.version_number = gsg.version_number
1617 AND gs.grading_schema_type = 'HONORS'
1618 AND gs.grading_schema_cd = cp_grd_sch
1619 AND gs.version_number = cp_grd_ver
1620 AND gsg.GRADE = cp_grade;
1621
1622 lAwdMark igs_en_spa_awd_aim.AWARD_MARK%TYPE;
1623 lAwadrdGrade IGS_as_grd_sch_grade.Grade%TYPE;
1624 lReturnStatus VARCHAR2(10);
1625 lMsgData VARCHAR2(2000);
1626 l_enc_msg VARCHAR2(2000);
1627 l_mesg_text VARCHAR2(4000);
1628 l_msg_index NUMBER;
1629 l_msg_count NUMBER;
1630 l_grading_schema_cd VARCHAR2(30);
1631 l_gs_version_number NUMBER(30);
1632 l_total_spa_rec NUMBER := 0;
1633 l_total_spa_updated_rec NUMBER := 0;
1634 lrow_awd_grd_sch Cur_awd_grd_sch%ROWTYPE;
1635 lrowAwd_grd Cur_Awd_grd%ROWTYPE;
1636
1637 BEGIN
1638
1639
1640 --initialize
1641 retcode:= 0;
1642 errbuff:= NULL;
1643 --start
1644 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
1645
1646 OPEN cur_awd_grd_sch;
1647 FETCH cur_awd_grd_sch INTO lrow_awd_grd_sch;
1648 CLOSE cur_awd_grd_sch;
1649
1650 FOR spaa_rec IN cur_spaa(p_award_cd) LOOP
1651
1652 IF lrow_awd_grd_sch.grading_schema_cd IS NOT NULL THEN
1653 OPEN cur_awd_grd(lrow_awd_grd_sch.grading_schema_cd,
1654 lrow_awd_grd_sch.gs_version_number,spaa_rec.honours_level);
1655 FETCH Cur_Awd_grd INTO lrowAwd_grd;
1656 CLOSE Cur_Awd_grd;
1657
1658 END IF;
1659
1660 -- increment the totla spa record selected by 1;
1661 l_total_spa_rec := l_total_spa_rec + 1;
1662 ---- Get the award mark, Honors level, grading schema and grading schem version
1663 -- by making a call to procedure pr_calc_award_mark of package igs_as_calc_award_mark.
1664
1665 igs_as_calc_award_mark.pr_calc_award_mark(
1666 p_person_id => spaa_rec.person_id,
1667 p_course_cd => spaa_rec.course_cd ,
1668 p_award_cd => spaa_rec.award_cd,
1669 p_award_mark => lAwdMark,
1670 p_honors_level => lAwadrdGrade,
1671 p_grading_schema_cd => l_grading_schema_cd,
1672 p_version_number => l_gs_version_number,
1673 x_return_status => lReturnStatus,
1674 x_msg_data => lmsgdata,
1675 x_msg_count => l_msg_count);
1676
1677
1678
1679 IF ((lReturnStatus <> FND_API.G_RET_STS_SUCCESS) OR (NVL(lrowawd_grd.grade, lawadrdgrade) IS NULL) )THEN
1680 -- Get the proper message and log it to the file
1681 FOR l_index IN 1..NVL(l_msg_count, 0) LOOP
1682
1683 FND_MSG_PUB.GET (
1684 FND_MSG_PUB.G_FIRST,
1685 FND_API.G_TRUE,
1686 l_enc_msg,
1687 l_msg_index
1688 );
1689 FND_MESSAGE.SET_ENCODED(l_enc_msg);
1690 lmsgdata := FND_MESSAGE.GET;
1691 l_mesg_text := l_mesg_text || lmsgdata || ';' ;
1692 FND_MSG_PUB.DELETE_MSG(l_msg_index);
1693 END LOOP;
1694
1695 fnd_file.put_line (fnd_file.LOG, ' Failed to Update ' || spaa_rec.person_id || ' - ' || spaa_rec.course_cd || ' - ' || P_AWARD_CD );
1696 fnd_file.put_line (fnd_file.LOG, l_mesg_text);
1697
1698 ELSE -- The mark and honors level were calculated successfully. Now update the SPAA record.
1699 fnd_file.put_line (fnd_file.LOG,'-------------------------***----------------------');
1700 BEGIN
1701 igs_en_spa_awd_aim_pkg.update_row(
1702 x_rowid => spaa_rec.row_id,
1703 x_person_id => spaa_rec.person_id,
1704 x_course_cd => spaa_rec.course_cd,
1705 x_award_cd => spaa_rec.award_cd,
1706 x_start_dt => spaa_rec.start_dt,
1707 x_end_dt => spaa_rec.end_dt,
1708 x_complete_ind => spaa_rec.complete_ind,
1709 x_honours_level => spaa_rec.honours_level,
1710 x_conferral_date => spaa_rec.conferral_date,
1711 x_award_mark => lawdmark,
1712 x_award_grade => NVL(lrowawd_grd.grade, lawadrdgrade),
1713 x_grading_schema_cd => NVL(lrowawd_grd.grading_schema_cd, l_grading_schema_cd),
1714 x_gs_version_number => NVL(lrowawd_grd.version_number, l_gs_version_number));
1715
1716 IF NVL(lrowawd_grd.grade, lawadrdgrade) IS NOT NULL THEN
1717 --Print the success in log...
1718 fnd_file.put_line (fnd_file.LOG,'Updated ' ||spaa_rec.person_id || ': ' || spaa_rec.course_cd || ': ' || spaa_rec.award_cd || ': With award grade :' || NVL(lrowawd_grd.grade, lawadrdgrade) );
1719 -- Increment the total updated records by 1
1720 l_total_spa_updated_rec := l_total_spa_updated_rec + 1;
1721 END IF;
1722
1723
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 fnd_file.put_line (fnd_file.LOG, 'Error Occured For '|| spaa_rec.PERSON_ID || ' - ' || spaa_rec.COURSE_CD || ' - ' || P_AWARD_CD || SQLERRM );
1727 END;
1728 END IF;
1729 END LOOP;
1730
1731 --Print the total statistics in log.
1732 fnd_file.put_line (fnd_file.LOG, 'Total program attempt award records selected : ' || l_total_spa_rec);
1733 fnd_file.put_line (fnd_file.LOG, 'Total program attempt award records updated successfully : ' || l_total_spa_updated_rec);
1734
1735 EXCEPTION
1736 WHEN FND_API.G_EXC_ERROR THEN
1737 FOR l_index IN 1..NVL(l_msg_count, 0) LOOP
1738 FND_MSG_PUB.GET (
1739 FND_MSG_PUB.G_FIRST,
1740 FND_API.G_TRUE,
1741 l_enc_msg,
1742 l_msg_index );
1743
1744 FND_MESSAGE.SET_ENCODED(l_enc_msg);
1745 lmsgdata := FND_MESSAGE.GET;
1746 l_mesg_text := l_mesg_text || lmsgdata || ';' ;
1747 FND_MSG_PUB.DELETE_MSG(l_msg_index);
1748 END LOOP;
1749 fnd_file.put_line (fnd_file.LOG, 'Error Ocuured : ' ||l_mesg_text );
1750
1751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1752 FOR l_index IN 1..NVL(l_msg_count, 0) LOOP
1753
1754 FND_MSG_PUB.GET (
1755 FND_MSG_PUB.G_FIRST,
1756 FND_API.G_TRUE,
1757 l_enc_msg,
1758 l_msg_index
1759 );
1760 FND_MESSAGE.SET_ENCODED(l_enc_msg);
1761 lmsgdata := FND_MESSAGE.GET;
1762 l_mesg_text := l_mesg_text || lmsgdata || ';' ;
1763 FND_MSG_PUB.DELETE_MSG(l_msg_index);
1764 END LOOP;
1765 fnd_file.put_line (fnd_file.LOG, 'Error Ocuured : ' ||l_mesg_text );
1766
1767 WHEN OTHERS THEN
1768 retcode:=2;
1769 fnd_file.put_line (fnd_file.LOG, 'Error encountered : ' || SQLERRM);
1770 END upgrade_awards;
1771
1772
1773
1774 FUNCTION fn_ret_unit_lvl_mark (p_person_id IN NUMBER,
1775 p_course_cd IN VARCHAR2,
1776 p_unit_level IN VARCHAR2
1777 ) RETURN NUMBER
1778 IS
1779 L_RETURN_STATUS VARCHAR2(1000);
1780 L_MSG_DATA VARCHAR2(1000);
1781 L_MSG_COUNT NUMBER;
1782 l_unit_level_mark NUMBER ;
1783
1784 Begin
1785
1786 l_unit_level_mark := fn_calc_unit_lvl_mark (p_person_id ,
1787 p_course_cd ,
1788 p_unit_level ,
1789 L_RETURN_STATUS ,
1790 L_MSG_DATA ,
1791 L_MSG_COUNT );
1792
1793
1794 return l_unit_level_mark;
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 fnd_file.put_line (fnd_file.LOG, 'Error encountered : ' || SQLERRM);
1798 End fn_ret_unit_lvl_mark;
1799
1800 END igs_as_calc_award_mark;
[Home] [Help]