1 PACKAGE BODY IGS_PR_GEN_002 AS
2 /* $Header: IGSPR02B.pls 120.2 2006/04/28 01:54:11 sepalani ship $ */
3
4 /************************************************************************
5 Know limitations, enhancements or remarks
6 Change History
7 Who When What
8 sepalani 28-Apr-2006 Bug # 5076203
9 ***************************************************************/
10
11 FUNCTION PRGP_GET_SCA_WAM(
12 p_person_id IN NUMBER ,
13 p_course_cd IN VARCHAR2 ,
14 p_course_version IN NUMBER ,
15 p_unit_set_cd IN VARCHAR2 ,
16 p_course_stage_type IN VARCHAR2 ,
17 p_prg_cal_type IN VARCHAR2 ,
18 p_prg_sequence_number IN NUMBER ,
19 p_use_recommended_ind IN VARCHAR2 ,
20 p_abort_when_missing_ind IN VARCHAR2 )
21 RETURN NUMBER AS
22 BEGIN
23 DECLARE
24 v_wam_value NUMBER;
25 BEGIN
26 v_wam_value := TO_NUMBER( IGS_RU_GEN_004.rulp_val_wam (
27 p_person_id,
28 p_course_cd,
29 p_course_version,
30 p_prg_cal_type,
31 p_prg_sequence_number,
32 p_use_recommended_ind,
33 p_abort_when_missing_ind) );
34 RETURN v_wam_value;
35 EXCEPTION
36 WHEN VALUE_ERROR THEN
37 RETURN 0;
38 END;
39 END prgp_get_sca_wam;
40
41 FUNCTION PRGP_GET_STG_COMP(
42 p_person_id IN NUMBER ,
43 p_course_cd IN VARCHAR2 ,
44 p_crv_version_number IN NUMBER ,
45 p_course_stage_type IN VARCHAR2 )
46 RETURN VARCHAR2 AS
47 v_message_text VARCHAR2(2000);
48
49 BEGIN
50 IF igs_ru_gen_005.rulp_val_stg_comp (
51 p_person_id => p_person_id,
52 p_sca_course_cd => p_course_cd,
53 p_sca_course_version => p_crv_version_number,
54 p_course_cd => p_course_cd,
55 p_course_version => p_crv_version_number,
56 p_cst_sequence_number => p_course_stage_type,
57 p_predicted_ind => 'N',
58 p_message_text => v_message_text
59 ) THEN
60 RETURN 'Y';
61 ELSE
62 RETURN 'N';
63 END IF;
64 RETURN 'N';
65 END;
66
67 --
68 -- kdande; 22-Apr-2003; Bug# 2829262
69 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_gpa_val
70 --
71 FUNCTION prgp_get_sua_gpa_val(
72 p_person_id IN IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE ,
73 p_course_cd IN IGS_EN_SU_ATTEMPT_ALL.course_cd%TYPE ,
74 p_unit_cd IN IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE ,
75 p_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE ,
76 p_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
77 p_best_worst IN VARCHAR2 ,
78 p_recommended_ind IN VARCHAR2,
79 p_uoo_id IN NUMBER)
80 RETURN IGS_AS_GRD_SCH_GRADE.gpa_val%TYPE AS
81 gv_other_detail VARCHAR2(255);
82 BEGIN -- prgp_get_sua_gpa_val
83 -- Get the GPA value for a nominated IGS_PS_UNIT attempt
84 -- Contains the options to search for:
85 -- Best/Worst possible grade - given the grading schema of the IGS_PS_UNIT attempt
86 -- Allow recommended - whether to consider recommended grades.
87 DECLARE
88 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
89 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
90 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
91 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
92 v_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
93 v_finalised_ind VARCHAR2(1);
94 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
95 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
96 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
97 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
98 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
99 v_origin_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
100 v_gpa_val IGS_AS_GRD_SCH_GRADE.gpa_val%TYPE DEFAULT NULL;
101 --
102 -- kdande; 22-Apr-2003; Bug# 2829262
103 -- Added uoo_id field to the WHERE clause of cursor c_sua.
104 --
105 CURSOR c_sua IS
106 SELECT version_number,
107 location_cd,
108 unit_class,
109 unit_attempt_status
110 FROM IGS_EN_SU_ATTEMPT sua
111 WHERE sua.person_id = p_person_id AND
112 sua.course_cd = p_course_cd AND
113 sua.uoo_id = p_uoo_id;
114 v_sua_rec c_sua%ROWTYPE;
115 CURSOR c_gsg (
116 cp_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE,
117 cp_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE,
118 cp_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE) IS
119 SELECT gsg.gpa_val
120 FROM IGS_AS_GRD_SCH_GRADE gsg
121 WHERE gsg.grading_schema_cd = cp_grading_schema_cd AND
122 gsg.version_number = cp_version_number AND
123 gsg.grade = cp_grade;
124 FUNCTION prgp_get_best_worst (
125 p_person_id IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE,
126 p_course_cd IGS_EN_SU_ATTEMPT_ALL.course_cd%TYPE,
127 p_unit_cd IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE,
128 p_version_number IGS_EN_SU_ATTEMPT_ALL.version_number%TYPE,
129 p_cal_type IGS_EN_SU_ATTEMPT_ALL.cal_type%TYPE,
130 p_ci_sequence_number IGS_EN_SU_ATTEMPT_ALL.ci_sequence_number%TYPE,
131 p_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE,
132 p_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE,
133 p_best_worst VARCHAR2)
134 RETURN NUMBER
135 AS
136 gv_other_detail VARCHAR2(255);
137 BEGIN -- prgp_get_best_worst
138 DECLARE
139 v_returned BOOLEAN;
140 v_grading_schema IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
141 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
142 v_max_gpa_val IGS_AS_GRD_SCH_GRADE.gpa_val%TYPE DEFAULT NULL;
143 v_min_gpa_val IGS_AS_GRD_SCH_GRADE.gpa_val%TYPE DEFAULT NULL;
144 CURSOR c_gsg (
145 cp_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE,
146 cp_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE) IS
147 SELECT MAX(gsg.gpa_val),
148 MIN(gsg.gpa_val)
149 FROM IGS_AS_GRD_SCH_GRADE gsg
150 WHERE gsg.grading_schema_cd = cp_grading_schema_cd AND
151 gsg.version_number = cp_version_number AND
152 gsg.gpa_val IS NOT NULL;
153 BEGIN
154 v_returned := IGS_AS_GEN_003.assp_get_sua_gs (
155 p_person_id,
156 p_course_cd,
157 p_unit_cd,
158 p_version_number,
159 p_cal_type,
160 p_ci_sequence_number,
161 p_location_cd,
162 p_unit_class,
163 v_grading_schema,
164 v_gs_version_number);
165 OPEN c_gsg (
166 v_grading_schema,
167 v_gs_version_number);
168 FETCH c_gsg INTO
169 v_max_gpa_val,
170 v_min_gpa_val;
171 CLOSE c_gsg;
172 IF NVL(p_best_worst,'X') = 'B' THEN
173 RETURN v_max_gpa_val;
174 ELSE
175 RETURN v_min_gpa_val;
176 END IF;
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF c_gsg%ISOPEN THEN
180 CLOSE c_gsg;
181 END IF;
182 RAISE;
183 END;
184 EXCEPTION
185 WHEN OTHERS THEN
186 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
187 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_002.PRGP_GET_BEST_WORST');
188 --IGS_GE_MSG_STACK.ADD;
189
190 END prgp_get_best_worst;
191 BEGIN
192 OPEN c_sua;
193 FETCH c_sua INTO v_sua_rec;
194 IF c_sua%NOTFOUND THEN
195 CLOSE c_sua;
196 RETURN NULL;
197 END IF;
198 CLOSE c_sua;
199 IF v_sua_rec.unit_attempt_status NOT IN (
200 cst_completed,
201 cst_duplicate,
202 cst_discontin,
203 cst_enrolled) THEN
204 RETURN NULL;
205 ELSE
206 IF p_recommended_ind = 'N' THEN
207 v_finalised_ind := 'Y';
208 ELSE
209 v_finalised_ind := 'N';
210 END IF;
211 --
212 -- kdande; 22-Apr-2003; Bug# 2829262
213 -- Added uoo_id parameter to the IGS_AS_GEN_003.assp_get_sua_outcome
214 -- FUNCTION call.
215 --
216 IF IGS_AS_GEN_003.assp_get_sua_outcome (
217 p_person_id,
218 p_course_cd,
219 p_unit_cd,
220 p_cal_type,
221 p_ci_sequence_number,
222 v_sua_rec.unit_attempt_status,
223 v_finalised_ind,
224 v_outcome_dt,
225 v_grading_schema_cd,
226 v_gs_version_number,
227 v_grade,
228 v_mark,
229 v_origin_course_cd,
230 p_uoo_id,
231 --added by LKAKI----
232 'N') IS NULL THEN
233 IF NVL(p_best_worst,'X') NOT IN (
234 'B',
235 'W') THEN
236 RETURN NULL;
237 ELSE
238 v_gpa_val := prgp_get_best_worst(
239 p_person_id,
240 p_course_cd,
241 p_unit_cd,
242 v_sua_rec.version_number,
243 p_cal_type,
244 p_ci_sequence_number,
245 v_sua_rec.location_cd,
246 v_sua_rec.unit_class,
247 p_best_worst);
248 RETURN v_gpa_val;
249 END IF;
250 ELSE
251 OPEN c_gsg (
252 v_grading_schema_cd,
253 v_gs_version_number,
254 v_grade);
255 FETCH c_gsg INTO v_gpa_val;
256 IF c_gsg%NOTFOUND THEN
257 CLOSE c_gsg;
258 RETURN NULL;
259 END IF;
260 CLOSE c_gsg;
261 RETURN v_gpa_val;
262 END IF;
263 END IF;
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF c_sua%ISOPEN THEN
267 CLOSE c_sua;
268 END IF;
269 IF c_gsg%ISOPEN THEN
270 CLOSE c_gsg;
271 END IF;
272 RAISE;
273 END;
274 EXCEPTION
275 WHEN OTHERS THEN
276 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
277 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_002.PRGP_GET_SUA_GPA_VAL');
278 --IGS_GE_MSG_STACK.ADD;
279
280 END prgp_get_sua_gpa_val;
281 --
282 -- kdande; 22-Apr-2003; Bug# 2829262
283 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_prg_num.
284 --
285 FUNCTION prgp_get_sua_prg_num(
286 p_prg_cal_type IN VARCHAR ,
287 p_prg_sequence_number IN NUMBER ,
288 p_number_of_periods IN NUMBER ,
289 p_person_id IN NUMBER ,
290 p_course_cd IN VARCHAR2 ,
291 p_unit_cd IN VARCHAR2 ,
292 p_cal_type IN VARCHAR2 ,
293 p_ci_sequence_number IN NUMBER,
294 p_uoo_id IN NUMBER)
295 RETURN VARCHAR2 AS
296 gv_other_detail VARCHAR2(255);
297 BEGIN -- prgp_get_sua_prg_num
298 -- Determine whether student IGS_PS_UNIT attempt is effective in the progression
299 -- period and a previous number of periods. This routine calls the
300 -- prgp_get_sua_prg_prd routine where appropriate.
301 DECLARE
302 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
303 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
304 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
305 v_number_processed NUMBER DEFAULT 0;
306 v_enrolled_in_prg_period BOOLEAN DEFAULT FALSE;
307 v_contributes_to_period BOOLEAN DEFAULT FALSE;
308 CURSOR c_sca IS
309 SELECT sca.version_number
310 FROM IGS_EN_STDNT_PS_ATT sca
311 WHERE sca.person_id = p_person_id AND
312 sca.course_cd = p_course_cd;
313 CURSOR c_cat_ci (
314 cp_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
315 SELECT ci1.cal_type,
316 ci1.sequence_number
317 FROM IGS_CA_TYPE cat,
318 IGS_CA_STAT cs,
319 IGS_CA_INST ci1
320 WHERE cat.cal_type = ci1.cal_type AND
321 cat.s_cal_cat = cst_progress AND
322 cs.cal_status = ci1.cal_status AND
323 cs.s_cal_status = cst_active AND
324 IGS_PR_GEN_001.prgp_get_cal_stream (
325 p_course_cd,
326 cp_version_number,
327 p_prg_cal_type,
328 ci1.cal_type)
329 = 'Y' AND
330 ci1.start_dt <
331 (SELECT ci2.start_dt
332 FROM IGS_CA_INST ci2
333 WHERE ci2.cal_type = p_prg_cal_type AND
334 ci2.sequence_number = p_prg_sequence_number)
335 ORDER BY ci1.start_dt DESC;
336 BEGIN
337 -- If effective in parameter period then no further processing required
338 --
339 -- kdande; 22-Apr-2003; Bug# 2829262
340 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_prg_prd.
341 --
342 IF prgp_get_sua_prg_prd (
343 p_prg_cal_type,
344 p_prg_sequence_number,
345 p_person_id,
346 p_course_cd,
347 p_unit_cd,
348 p_cal_type,
349 p_ci_sequence_number,
350 'N',
351 NULL,
352 NULL,
353 p_uoo_id) = 'Y' THEN
354 RETURN 'Y';
355 END IF;
356 -- If only 1 period then don't go back into past periods
357 IF p_number_of_periods = 1 THEN
358 RETURN 'N';
359 END IF;
360 v_number_processed := 1;
361 -- Get the version number for student IGS_PS_COURSE attempt
362 OPEN c_sca;
363 FETCH c_sca INTO v_version_number;
364 CLOSE c_sca;
365 -- Loop through the specified number of past periods
366 FOR v_cat_ci_rec IN c_cat_ci (
367 v_version_number) LOOP
368 v_enrolled_in_prg_period := TRUE;
369 -- Determine if the student is effectively enrolled in the progression period
370 IF IGS_PR_GEN_001.prgp_get_msr_efctv (
371 v_cat_ci_rec.cal_type,
372 v_cat_ci_rec.sequence_number,
373 p_person_id,
374 p_course_cd) = 'N' THEN
375 -- The period is not counted as effective
376 v_enrolled_in_prg_period := FALSE;
377 END IF;
378 IF v_enrolled_in_prg_period THEN
379 -- Determine if the IGS_PS_UNIT attempt contributes to the period
380 --
381 -- kdande; 22-Apr-2003; Bug# 2829262
382 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_prg_prd.
383 --
384 IF prgp_get_sua_prg_prd (
385 v_cat_ci_rec.cal_type,
386 v_cat_ci_rec.sequence_number,
387 p_person_id,
388 p_course_cd,
389 p_unit_cd,
390 p_cal_type,
391 p_ci_sequence_number,
392 'N',
393 NULL,
394 NULL,
395 p_uoo_id) = 'Y' THEN
396 v_contributes_to_period := TRUE;
397 EXIT;
398 END IF;
399 v_number_processed := v_number_processed + 1;
400 IF v_number_processed = p_number_of_periods THEN
401 EXIT;
402 END IF;
403 END IF;
404 END LOOP;
405 IF v_contributes_to_period THEN
406 RETURN 'Y';
407 END IF;
408 RETURN 'N';
409 EXCEPTION
410 WHEN OTHERS THEN
411 IF c_sca%ISOPEN THEN
412 CLOSE c_sca;
413 END IF;
414 IF c_cat_ci%ISOPEN THEN
415 CLOSE c_cat_ci;
416 END IF;
417 RAISE;
418 END;
419 EXCEPTION
420 WHEN OTHERS THEN
421 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
422 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_002.PRGP_GET_SUA_PRG_NUM');
423 --IGS_GE_MSG_STACK.ADD;
424
425 END prgp_get_sua_prg_num;
426
427 --
428 -- kdande; 22-Apr-2003; Bug# 2829262
429 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_prg_prd
430 --
431 FUNCTION prgp_get_sua_prg_prd(
432 p_prg_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE ,
433 p_prg_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
437 p_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE ,
434 p_person_id IN IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE ,
435 p_course_cd IN IGS_EN_SU_ATTEMPT_ALL.course_cd%TYPE ,
436 p_unit_cd IN IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE ,
438 p_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
439 p_details_ind IN VARCHAR2 ,
440 p_unit_attempt_status IN VARCHAR2 ,
441 p_discontinued_dt IN DATE,
442 p_uoo_id IN NUMBER)
443 RETURN VARCHAR2 AS
444 gv_other_detail VARCHAR2(255);
445 BEGIN -- prgp_get_sua_prg_prd
446 -- Return whether student IGS_PS_UNIT attempt contributes to a nominated progression
447 -- period. Will take into consideration early exit from units due to
448 -- discontinuation of early results in self-paced units.
449 -- IGS_GE_NOTE: Discontinuation date is optional and if not passed will be loaded
450 -- from the student IGS_PS_UNIT attempt. This has been included as the rules engine
451 -- often
452 DECLARE
453 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
454 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
455 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
456 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
457 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
458 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
459 v_discontinued_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
460 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
461 v_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
462 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
463 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
464 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
465 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
466 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
467 v_origin_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
468 v_prev_prg_cal_type IGS_CA_INST.cal_type%TYPE;
469 v_prev_prg_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
470 v_previous_cutoff_dt IGS_CA_DA_INST.absolute_val%TYPE;
471 v_cutoff_dt IGS_CA_DA_INST.absolute_val%TYPE;
472 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
473 v_dummy VARCHAR2(1);
474 CURSOR c_cir IS
475 SELECT 'X'
476 FROM IGS_CA_INST_REL cir
477 WHERE cir.sub_cal_type = p_cal_type AND
478 cir.sub_ci_sequence_number = p_ci_sequence_number AND
479 cir.sup_cal_type = p_prg_cal_type AND
480 cir.sup_ci_sequence_number = p_prg_ci_sequence_number;
481 --
482 -- kdande; 22-Apr-2003; Bug# 2829262
483 -- Added uoo_id field to the SELECT clause of cursor c_sua.
484 --
485 CURSOR c_sua IS
486 SELECT sua.discontinued_dt,
487 sua.unit_attempt_status
488 FROM IGS_EN_SU_ATTEMPT sua
489 WHERE sua.person_id = p_person_id AND
490 sua.course_cd = p_course_cd AND
491 sua.uoo_id = p_uoo_id;
492 CURSOR c_sca IS
493 SELECT sca.version_number
494 FROM IGS_EN_STDNT_PS_ATT sca
495 WHERE sca.person_id = p_person_id AND
496 sca.course_cd = p_course_cd;
497 CURSOR c_ci_cir_ct_cs1 (
498 cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
499 SELECT cir.sup_cal_type,
500 cir.sup_ci_sequence_number
501 FROM IGS_CA_INST ci1,
502 IGS_CA_INST_REL cir,
503 IGS_CA_TYPE ct,
504 IGS_CA_STAT cs
505 WHERE cir.sub_cal_type = p_cal_type AND
506 cir.sub_ci_sequence_number = p_ci_sequence_number AND
507 ct.cal_type = cir.sup_cal_type AND
508 ct.s_cal_cat = cst_progress AND
509 ci1.cal_type = cir.sup_cal_type AND
510 ci1.sequence_number = cir.sup_ci_sequence_number AND
511 cs.cal_status = ci1.cal_status AND
512 cs.s_cal_status = cst_active AND
513 IGS_PR_GEN_001.prgp_get_cal_stream (
514 p_course_cd,
515 cp_sca_version_number,
516 p_prg_cal_type,
517 cir.sup_cal_type) = 'Y' AND
518 ci1.start_dt <
519 (SELECT ci2.start_dt
520 FROM IGS_CA_INST ci2
521 WHERE ci2.cal_type = p_prg_cal_type AND
522 ci2.sequence_number = p_prg_ci_sequence_number)
523 ORDER BY ci1.start_dt DESC;
524 CURSOR c_ci_cir_ct_cs2 (
525 cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
526 SELECT 'X'
527 FROM IGS_CA_INST ci1,
528 IGS_CA_INST_REL cir,
529 IGS_CA_TYPE ct,
530 IGS_CA_STAT cs
531 WHERE cir.sub_cal_type = p_cal_type AND
532 cir.sub_ci_sequence_number = p_ci_sequence_number AND
533 ct.cal_type = cir.sup_cal_type AND
534 ct.s_cal_cat = cst_progress AND
535 ci1.cal_type = cir.sup_cal_type AND
536 ci1.sequence_number = cir.sup_ci_sequence_number AND
537 cs.cal_status = ci1.cal_status AND
538 cs.s_cal_status = cst_active AND
539 IGS_PR_GEN_001.prgp_get_cal_stream (
540 p_course_cd,
541 cp_sca_version_number,
542 p_prg_cal_type,
543 cir.sup_cal_type) = 'Y' AND
544 ci1.start_dt >
545 (SELECT ci2.start_dt
546 FROM IGS_CA_INST ci2
550 -- Check calendar relationship between progression and teaching calendar
547 WHERE ci2.cal_type = p_prg_cal_type AND
548 ci2.sequence_number = p_prg_ci_sequence_number);
549 BEGIN
551 OPEN c_cir;
552 FETCH c_cir INTO v_dummy;
553 IF c_cir%NOTFOUND THEN
554 CLOSE c_cir;
555 RETURN 'N';
556 ELSE
557 CLOSE c_cir;
558 END IF;
559 -- Check whether details have been passed
560 IF p_details_ind = 'N' THEN
561 OPEN c_sua;
562 FETCH c_sua INTO
563 v_discontinued_dt,
564 v_unit_attempt_status;
565 IF c_sua%NOTFOUND THEN
566 CLOSE c_sua;
567 RETURN 'N';
568 END IF;
569 CLOSE c_sua;
570 ELSE
571 v_discontinued_dt := p_discontinued_dt;
572 v_unit_attempt_status := p_unit_attempt_status;
573 END IF;
574 -- Get the IGS_EN_STDNT_PS_ATT version_number from student IGS_PS_COURSE attempt
575 OPEN c_sca;
576 FETCH c_sca INTO v_sca_version_number;
577 CLOSE c_sca;
578 -- Eliminate status which don't apply
579 IF v_unit_attempt_status NOT IN (
580 cst_enrolled,
581 cst_discontin,
582 cst_completed,
583 cst_duplicate) THEN
584 RETURN 'N';
585 END IF;
586 -- Get the outcome if applicable
587 IF v_unit_attempt_status IN (
588 cst_completed,
589 cst_duplicate,
590 cst_enrolled) THEN
591 --
592 -- kdande; 22-Apr-2003; Bug# 2829262
593 -- Added uoo_id parameter to the IGS_AS_GEN_003.assp_get_sua_outcome
594 -- FUNCTION call.
595 --
596 v_result_type := IGS_AS_GEN_003.assp_get_sua_outcome(
597 p_person_id,
598 p_course_cd,
599 p_unit_cd,
600 p_cal_type,
601 p_ci_sequence_number,
602 v_unit_attempt_status,
603 'N',
604 v_outcome_dt,
605 v_grading_schema_cd,
606 v_gs_version_number,
607 v_grade,
608 v_mark,
609 v_origin_course_cd,
610 p_uoo_id,
611 --added by LKAKI----
612 'N');
613 IF v_result_type IS NULL THEN
614 v_outcome_dt := NULL;
615 END IF;
616 ELSE
617 v_outcome_dt := NULL;
618 END IF;
619 -- Get the cutoff date from the previous period
620 OPEN c_ci_cir_ct_cs1 (
621 v_sca_version_number);
622 FETCH c_ci_cir_ct_cs1 INTO
623 v_prev_prg_cal_type,
624 v_prev_prg_ci_sequence_number;
625 IF c_ci_cir_ct_cs1%FOUND THEN
626 CLOSE c_ci_cir_ct_cs1;
627 v_previous_cutoff_dt := IGS_PR_GEN_001.prgp_get_prg_efctv(
628 v_prev_prg_cal_type,
629 v_prev_prg_ci_sequence_number);
630 ELSE
631 CLOSE c_ci_cir_ct_cs1;
632 END IF;
633 -- Check for contribution to a previous period
634 IF v_unit_attempt_status = cst_discontin THEN
635 IF TRUNC(v_discontinued_dt) <= v_previous_cutoff_dt THEN
636 RETURN 'N';
637 END IF;
638 ELSIF v_unit_attempt_status IN (
639 cst_completed,
640 cst_duplicate) OR
641 (v_unit_attempt_status = 'ENROLLED' AND
642 v_outcome_dt IS NOT NULL) THEN
643 IF v_outcome_dt <= v_previous_cutoff_dt THEN
644 RETURN 'N';
645 END IF;
646 END IF;
647 -- Check which progression period
648 OPEN c_ci_cir_ct_cs2 (
649 v_sca_version_number);
650 FETCH c_ci_cir_ct_cs2 INTO v_dummy;
651 IF c_ci_cir_ct_cs2%FOUND THEN
652 CLOSE c_ci_cir_ct_cs2;
653 v_cutoff_dt := IGS_PR_GEN_001.prgp_get_prg_efctv(
654 p_prg_cal_type,
655 p_prg_ci_sequence_number);
656 IF v_discontinued_dt IS NULL THEN
657 IF v_unit_attempt_status = cst_enrolled AND
658 v_outcome_dt IS NULL THEN
659 RETURN 'N';
660 ELSE
661 IF v_outcome_dt <= v_cutoff_dt THEN
662 RETURN 'Y';
663 END IF;
664 END IF;
665 ELSE
666 IF TRUNC(v_discontinued_dt) <= v_cutoff_dt THEN
667 RETURN 'Y';
668 END IF;
669 END IF;
670 ELSE
671 CLOSE c_ci_cir_ct_cs2;
672 RETURN 'Y';
673 END IF;
674 RETURN 'N';
675 EXCEPTION
676 WHEN OTHERS THEN
677 IF c_cir%ISOPEN THEN
678 CLOSE c_cir;
679 END IF;
680 IF c_sua%ISOPEN THEN
681 CLOSE c_sua;
682 END IF;
683 IF c_sca%ISOPEN THEN
684 CLOSE c_sca;
685 END IF;
686 IF c_ci_cir_ct_cs1%ISOPEN THEN
687 CLOSE c_ci_cir_ct_cs1;
688 END IF;
689 IF c_ci_cir_ct_cs2%ISOPEN THEN
690 CLOSE c_ci_cir_ct_cs2;
691 END IF;
692 RAISE;
693 END;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
698 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_002.PRGP_GET_SUA_PRG_PRD');
699 --IGS_GE_MSG_STACK.ADD;
700
701 END prgp_get_sua_prg_prd;
702 --
703 -- kdande; 22-Apr-2003; Bug# 2829262
704 -- Added p_uoo_id parameter to the FUNCTION prgp_get_sua_wam
705 --
706 FUNCTION prgp_get_sua_wam (
707 p_person_id IN NUMBER ,
708 p_course_cd IN VARCHAR2 ,
709 p_unit_cd IN VARCHAR2 ,
710 p_cal_type IN VARCHAR2 ,
711 p_ci_sequence_number IN NUMBER ,
712 p_use_recommended_ind IN VARCHAR2 ,
713 p_abort_when_missing_ind IN VARCHAR2,
714 p_wam_type IN VARCHAR2 DEFAULT 'COURSE',
715 p_uoo_id IN NUMBER)
716 RETURN NUMBER AS
717 gv_other_detail VARCHAR2(255);
718 BEGIN -- prgp_get_sua_wam
719 -- Get the mark value applicable to WAM for a nominated student IGS_PS_UNIT attempt,
720 -- considering the options that are available to the WAM calculation.
721 -- A return of -1000000 means that the overall WAM check should be aborted
722 -- due to a missing grade.
723 DECLARE
724 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
725 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
726 cst_abort_WAM CONSTANT NUMBER := -1000000;
727 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
728 v_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
729 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
730 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
731 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
732 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
733 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
734 v_origin_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
735 v_finalised_ind VARCHAR2(1);
736 v_administrative_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE;
737 v_effective_progression_ind IGS_AD_ADM_UNIT_STAT.effective_progression_ind%TYPE;
738
739 --
740 -- kdande; 22-Apr-2003; Bug# 2829262
741 -- Added uoo_id field to the SELECT clause of cursor c_sua.
742 --
743 CURSOR c_sua IS
744 SELECT sua.unit_attempt_status,sua.administrative_unit_status
745 FROM IGS_EN_SU_ATTEMPT sua
746 WHERE sua.person_id = p_person_id AND
747 sua.course_cd = p_course_cd AND
748 sua.uoo_id = p_uoo_id;
749 CURSOR c_aus (
750 cp_administrative_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE) IS
751 SELECT effective_progression_ind
752 FROM IGS_AD_ADM_UNIT_STAT
753 WHERE administrative_unit_status=cp_administrative_unit_status;
754 BEGIN
755 OPEN c_sua;
756 FETCH c_sua INTO v_unit_attempt_status,v_administrative_unit_status;
757 IF c_sua%NOTFOUND THEN
758 CLOSE c_sua;
759 RETURN NULL;
760 END IF;
761 CLOSE c_sua;
762 -- Discontinued outcomes get a mark of zero
763 IF v_unit_attempt_status = cst_discontin THEN
764
765 -- RETURN 0;
766 OPEN c_aus(v_administrative_unit_status);
767 FETCH c_aus INTO v_effective_progression_ind;
768 CLOSE c_aus;
769 -- If the admin unit status is not effective for progression then ignore
770
771 IF v_effective_progression_ind = 'N' THEN
772 RETURN NULL;
773 ELSE
774 RETURN 0;
775 END IF;
776
777 END IF;
778 IF v_unit_attempt_status = cst_enrolled AND p_use_recommended_ind = 'N' THEN
779 -- If enrolled return value appropriate to parameter
780
781 IF p_wam_type = 'PERIOD' AND p_abort_when_missing_ind = 'Y' THEN
782 RETURN cst_abort_WAM;
783 ELSE
784 RETURN NULL;
785 END IF;
786 ELSE
787 -- Retrieve the latest outcome
788 IF p_use_recommended_ind = 'N' THEN
789 v_finalised_ind := 'Y';
790 ELSE
791 v_finalised_ind := 'N';
792 END IF;
793 --
794 -- kdande; 22-Apr-2003; Bug# 2829262
795 -- Added uoo_id parameter to the IGS_AS_GEN_003.assp_get_sua_outcome
796 -- FUNCTION call.
797 --
798 v_s_result_type := IGS_AS_GEN_003.assp_get_sua_outcome (
799 p_person_id,
800 p_course_cd,
801 p_unit_cd,
802 p_cal_type,
803 p_ci_sequence_number,
804 v_unit_attempt_status,
805 v_finalised_ind,
806 v_outcome_dt,
807 v_grading_schema_cd,
808 v_gs_version_number,
809 v_grade,
810 v_mark,
811 v_origin_course_cd,
812 p_uoo_id,
813 --added by LKAKI---
814 'N');
815 IF v_s_result_type IS NULL OR
816 v_mark IS NULL THEN
817 IF p_wam_type = 'COURSE' THEN
818 IF v_s_result_type IS NOT NULL AND
819 v_mark IS NOT NULL AND
820 p_abort_when_missing_ind = 'Y' THEN
821 RETURN cst_abort_WAM;
822 ELSE
823 RETURN NULL;
824 END IF;
825 ELSE
826 -- Handle missing mark according to parameter
827 IF p_abort_when_missing_ind = 'Y' THEN
828 RETURN cst_abort_WAM;
829 ELSE
830 RETURN NULL;
831 END IF;
832 END IF;
833
834 ELSE
835 -- Return the mark retrieved
836 RETURN v_mark;
837 END IF;
838 END IF;
839 EXCEPTION
840 WHEN OTHERS THEN
841 IF c_sua%ISOPEN THEN
842 CLOSE c_sua;
843 END IF;
844 RAISE;
845 END;
846 EXCEPTION
847 WHEN OTHERS THEN
848 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
849 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_002.PRGP_GET_SUA_WAM');
850 --IGS_GE_MSG_STACK.ADD;
851
852 END prgp_get_sua_wam;
853
854 END IGS_PR_GEN_002;
[Home] [Help]