1 PACKAGE BODY IGS_PR_GEN_001 AS
2 /* $Header: IGSPR01B.pls 115.7 2003/05/30 07:19:17 kdande ship $ */
3 FUNCTION PRGP_GET_CAL_STREAM(
4 P_COURSE_CD IN VARCHAR2 ,
5 P_VERSION_NUMBER IN NUMBER ,
6 P_PRG_CAL_TYPE IN VARCHAR2 ,
7 p_comparison_prg_cal_type IN VARCHAR2 )
8 RETURN VARCHAR2 AS
9 gv_other_detail VARCHAR2(255);
10 BEGIN -- prgp_get_cal_stream
11 -- Get whether a progression calendar instance is within the same stream as
12 -- another. This refers to the stream concepts held within the progression
13 -- configuration structure.
14 DECLARE
15 v_stream_num IGS_PR_S_CRV_PRG_CAL.stream_num%TYPE;
16 v_dummy VARCHAR2(1);
17 CURSOR c_scpc1 IS
18 SELECT scpc.stream_num
19 FROM IGS_PR_S_CRV_PRG_CAL scpc
20 WHERE scpc.course_cd = p_course_cd AND
21 scpc.version_number = p_version_number AND
22 scpc.prg_cal_type = p_prg_cal_type;
23 CURSOR c_scpc2 (
24 cp_stream_num IGS_PR_S_CRV_PRG_CAL.stream_num%TYPE) IS
25 SELECT 'X'
26 FROM IGS_PR_S_CRV_PRG_CAL scpc
27 WHERE scpc.course_cd = p_course_cd AND
28 scpc.version_number = p_version_number AND
29 scpc.prg_cal_type = p_comparison_prg_cal_type AND
30 scpc.stream_num = cp_stream_num;
31 CURSOR c_sopc1 IS
32 SELECT sopc.org_unit_cd,
33 sopc.ou_start_dt,
34 sopc.stream_num
35 FROM IGS_PR_S_OU_PRG_CAL sopc
36 WHERE prgp_get_crv_cmt(
37 p_course_cd,
38 p_version_number,
39 sopc.org_unit_cd,
40 sopc.ou_start_dt) = 'Y' AND
41 sopc.prg_cal_type = p_prg_cal_type;
42 v_sopc_rec c_sopc1%ROWTYPE;
43 CURSOR c_sopc2 (
44 cp_org_unit_cd IGS_PR_S_OU_PRG_CAL.org_unit_cd%TYPE,
45 cp_ou_start_dt IGS_PR_S_OU_PRG_CAL.ou_start_dt%TYPE,
46 cp_stream_num IGS_PR_S_OU_PRG_CAL.stream_num%TYPE) IS
47 SELECT 'X'
48 FROM IGS_PR_S_OU_PRG_CAL sopc
49 WHERE sopc.org_unit_cd = cp_org_unit_cd AND
50 sopc.ou_start_dt = cp_ou_start_dt AND
51 sopc.prg_cal_type = p_comparison_prg_cal_type AND
52 sopc.stream_num = cp_stream_num;
53 CURSOR c_spc1 IS
54 SELECT spc.stream_num
55 FROM IGS_PR_S_PRG_CAL spc
56 WHERE spc.s_control_num = 1 AND
57 spc.prg_cal_type = p_prg_cal_type;
58 CURSOR c_spc2 (
59 cp_stream_num IGS_PR_S_PRG_CAL.stream_num%TYPE) IS
60 SELECT 'X'
61 FROM IGS_PR_S_PRG_CAL spc
62 WHERE spc.s_control_num = 1 AND
63 spc.prg_cal_type = p_comparison_prg_cal_type AND
64 spc.stream_num = cp_stream_num;
65 BEGIN
66 -- Select from within IGS_PS_COURSE override structure
67 OPEN c_scpc1;
68 FETCH c_scpc1 INTO v_stream_num;
69 IF c_scpc1%FOUND THEN
70 CLOSE c_scpc1;
71 OPEN c_scpc2 (
72 v_stream_num);
73 FETCH c_scpc2 INTO v_dummy;
74 IF c_scpc2%FOUND THEN
75 CLOSE c_scpc2;
76 RETURN 'Y';
77 ELSE
78 CLOSE c_scpc2;
79 RETURN 'N';
80 END IF;
81 END IF;
82 CLOSE c_scpc1;
83 -- Select from within organisation IGS_PS_UNIT structure
84 OPEN c_sopc1;
85 FETCH c_sopc1 INTO v_sopc_rec;
86 IF c_sopc1%FOUND THEN
87 CLOSE c_sopc1;
88 OPEN c_sopc2 (
89 v_sopc_rec.org_unit_cd,
90 v_sopc_rec.ou_start_dt,
91 v_sopc_rec.stream_num);
92 FETCH c_sopc2 INTO v_dummy;
93 IF c_sopc2%FOUND THEN
94 CLOSE c_sopc2;
95 RETURN 'Y';
96 ELSE
97 CLOSE c_sopc2;
98 RETURN 'N';
99 END IF;
100 END IF;
101 CLOSE c_sopc1;
102 -- Select from within system default structure
103 OPEN c_spc1;
104 FETCH c_spc1 INTO v_stream_num;
105 IF c_spc1%FOUND THEN
106 CLOSE c_spc1;
107 OPEN c_spc2 (
108 v_stream_num);
109 FETCH c_spc2 INTO v_dummy;
110 IF c_spc2%FOUND THEN
111 CLOSE c_spc2;
112 RETURN 'Y';
113 ELSE
114 CLOSE c_spc2;
115 RETURN 'N';
116 END IF;
117 END IF;
118 CLOSE c_spc1;
119 RETURN 'N';
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF c_scpc1%ISOPEN THEN
123 CLOSE c_scpc1;
124 END IF;
125 IF c_scpc2%ISOPEN THEN
126 CLOSE c_scpc2;
127 END IF;
128 IF c_sopc1%ISOPEN THEN
129 CLOSE c_sopc1;
130 END IF;
131 IF c_sopc2%ISOPEN THEN
132 CLOSE c_sopc2;
133 END IF;
134 IF c_spc1%ISOPEN THEN
135 CLOSE c_spc1;
136 END IF;
137 IF c_spc2%ISOPEN THEN
138 CLOSE c_spc2;
139 END IF;
140 RAISE;
141 END;
142 EXCEPTION
143 WHEN OTHERS THEN
144 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
145 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_001.PRGP_GET_CAL_STREAM');
146 --IGS_GE_MSG_STACK.ADD;
147
148 END prgp_get_cal_stream;
149
150 FUNCTION PRGP_GET_CRV_CMT(
151 p_course_cd IN VARCHAR2 ,
152 p_version_number IN NUMBER ,
153 p_org_unit_cd IN VARCHAR2 ,
154 p_ou_start_dt IN DATE )
155 RETURN VARCHAR2 AS
156 gv_other_detail VARCHAR2(255);
157 BEGIN -- prgp_get_crv_cmt
158 -- Get whether IGS_PS_COURSE version is covered by the nominated committee structure
159 DECLARE
160 v_ou_rel_found BOOLEAN DEFAULT FALSE;
161 v_dummy VARCHAR2(1);
162 CURSOR c_crv_cow IS
163 SELECT crv.course_type,
164 cow.org_unit_cd,
165 cow.ou_start_dt
166 FROM IGS_PS_VER crv,
167 IGS_PS_OWN cow
168 WHERE crv.course_cd = p_course_cd AND
169 crv.version_number = p_version_number AND
170 crv.course_cd = cow.course_cd AND
171 crv.version_number = cow.version_number;
172 CURSOR c_our (
173 cp_cow_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
174 cp_cow_ou_start_dt IGS_OR_UNIT.start_dt%TYPE,
175 cp_course_type IGS_PS_VER.course_type%TYPE) IS
176 SELECT 'X'
177 FROM IGS_OR_UNIT_REL our
178 WHERE our.parent_org_unit_cd = p_org_unit_cd AND
179 our.parent_start_dt = p_ou_start_dt AND
180 our.child_org_unit_cd = cp_cow_org_unit_cd AND
181 our.child_start_dt = cp_cow_ou_start_dt AND
182 our.logical_delete_dt IS NULL AND
183 EXISTS (
184 SELECT 'X'
185 FROM IGS_OR_REL_PS_TYPE ourct
186 WHERE our.parent_org_unit_cd = ourct.parent_org_unit_cd AND
187 our.parent_start_dt = ourct.parent_start_dt AND
188 our.child_org_unit_cd = ourct.child_org_unit_cd AND
189 our.child_start_dt = ourct.child_start_dt AND
190 our.create_dt = ourct.our_create_dt AND
191 ourct.course_type = cp_course_type);
192 BEGIN
193 FOR v_crv_cow_rec IN c_crv_cow LOOP
194
195 --gjha Added the following missing code. This will return true if the direct match is found.
196 IF v_crv_cow_rec.org_unit_cd = p_org_unit_cd AND
197 v_crv_cow_rec.ou_start_dt = p_ou_start_dt THEN
198 RETURN 'Y';
199 END IF;
200
201 OPEN c_our (
202 v_crv_cow_rec.org_unit_cd,
203 v_crv_cow_rec.ou_start_dt,
204 v_crv_cow_rec.course_type);
205 FETCH c_our INTO v_dummy;
206 IF c_our%FOUND THEN
207 CLOSE c_our;
208 v_ou_rel_found := TRUE;
209 EXIT;
210 ELSE
211 CLOSE c_our;
212 IF IGS_OR_GEN_001.orgp_get_within_ou (
213 p_org_unit_cd,
214 p_ou_start_dt,
215 v_crv_cow_rec.org_unit_cd,
216 v_crv_cow_rec.ou_start_dt,
217 'N') = 'Y' THEN
218 v_ou_rel_found := TRUE;
219 EXIT;
220 END IF;
221 END IF;
222 END LOOP;
223 IF v_ou_rel_found THEN
224 RETURN 'Y';
225 END IF;
226 RETURN 'N';
227 EXCEPTION
228 WHEN OTHERS THEN
229 IF c_crv_cow%ISOPEN THEN
230 CLOSE c_crv_cow;
231 END IF;
232 IF c_our%ISOPEN THEN
233 CLOSE c_our;
234 END IF;
235 RETURN 'N';
236 /* RAISE replaced by return 'N' if any error occurs in the main code block
237 Outer exception will not return N or Y if any error occurs in
238 declaration section
239 */
240 END;
241 EXCEPTION
242 WHEN OTHERS THEN
243 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
244 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_001.PRGP_GET_CRV_CMT');
245 --IGS_GE_MSG_STACK.ADD;
246
247 END prgp_get_crv_cmt;
248
249 FUNCTION prgp_get_drtn_efctv(
250 p_prg_cal_type IN VARCHAR2 ,
251 p_prg_sequence_number IN NUMBER ,
252 p_person_id IN NUMBER ,
253 p_course_cd IN VARCHAR2 )
254 RETURN VARCHAR2 AS
255 gv_other_detail VARCHAR2(255);
256 BEGIN -- prgp_get_drtn_efctv
257 -- Get whether the student is effectively enrolled in a progression period
258 -- for the purposes of calculating a duration figure ; this is different
259 -- from 'effective for the purposes of IGS_GE_MEASUREMENT' as this only counts
260 -- progression periods within which the student ends enrolment.
261 DECLARE
262 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
263 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
264 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
265 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
266 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
267 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
268 v_sua_enrolled BOOLEAN DEFAULT FALSE;
269 v_cir2_rec_not_found BOOLEAN DEFAULT FALSE;
270 v_match_not_found BOOLEAN DEFAULT FALSE;
271 CURSOR c_cat_ci_cir_cs1 IS
272 SELECT cir.sub_cal_type,
273 cir.sub_ci_sequence_number
274 FROM IGS_CA_TYPE cat,
275 IGS_CA_INST ci,
276 IGS_CA_INST_REL cir,
277 IGS_CA_STAT cs
278 WHERE cir.sup_cal_type = p_prg_cal_type AND
279 cir.sup_ci_sequence_number = p_prg_sequence_number AND
280 ci.cal_type = cir.sub_cal_type AND
281 ci.sequence_number = cir.sub_ci_sequence_number AND
282 cs.cal_status = ci.cal_status AND
283 cs.s_cal_status = cst_active AND
284 cat.cal_type = ci.cal_type AND
285 cat.s_cal_cat = cst_teaching;
286 --
287 -- kdande; 22-Apr-2003; Bug# 2829262
288 -- Added uoo_id field to the SELECT clause of cursor c_sua_sca.
289 --
290 CURSOR c_sua_sca (
291 cp_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
292 cp_sub_ci_sequence_number IGS_CA_INST_REL.sub_ci_sequence_number%TYPE) IS
293 SELECT sua.unit_cd,
294 sua.cal_type,
295 sua.ci_sequence_number,
296 sua.unit_attempt_status,
297 sua.discontinued_dt,
298 sca.version_number,
299 sua.uoo_id
300 FROM IGS_EN_SU_ATTEMPT sua,
301 IGS_EN_STDNT_PS_ATT sca
302 WHERE sua.person_id = p_person_id AND
303 sua.course_cd = p_course_cd AND
304 sca.person_id = sua.person_id AND
305 sca.course_cd = sua.course_cd AND
306 sua.cal_type = cp_sub_cal_type AND
307 sua.ci_sequence_number = cp_sub_ci_sequence_number AND
308 sua.unit_attempt_status IN (
309 cst_enrolled,
310 cst_completed,
311 cst_discontin);
312 CURSOR c_cat_ci_cir_cs2 (
313 cp_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
314 cp_sua_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
315 cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
316 SELECT cir.sup_cal_type,
317 cir.sup_ci_sequence_number
318 FROM IGS_CA_TYPE cat,
319 IGS_CA_INST ci1,
320 IGS_CA_INST_REL cir,
321 IGS_CA_STAT cs
322 WHERE cir.sub_cal_type = cp_sua_cal_type AND
323 cir.sub_ci_sequence_number = cp_sua_ci_sequence_number AND
324 ci1.cal_type = cir.sup_cal_type AND
325 ci1.sequence_number = cir.sup_ci_sequence_number AND
326 cs.cal_status = ci1.cal_status AND
327 cs.s_cal_status = cst_active AND
328 cat.cal_type = ci1.cal_type AND
329 cat.s_cal_cat = cst_progress AND
330 prgp_get_cal_stream (
331 p_course_cd,
332 cp_sca_version_number,
333 p_prg_cal_type,
334 cir.sup_cal_type) = 'Y' AND
335 ci1.start_dt <
336 (SELECT ci2.start_dt
337 FROM IGS_CA_INST ci2
338 WHERE ci2.cal_type = p_prg_cal_type AND
339 ci2.sequence_number = p_prg_sequence_number);
340 BEGIN
341 FOR v_cir1_rec IN c_cat_ci_cir_cs1 LOOP
342 FOR v_sua_rec IN c_sua_sca (
343 v_cir1_rec.sub_cal_type,
344 v_cir1_rec.sub_ci_sequence_number) LOOP
345 IF v_sua_rec.unit_attempt_status = cst_enrolled THEN
346 v_sua_enrolled := TRUE;
347 EXIT;
348 END IF;
349 -- Call routine to determine to which period it applies
350 v_cir2_rec_not_found := TRUE;
351 v_match_not_found := TRUE;
352 FOR v_cir2_rec IN c_cat_ci_cir_cs2 (
353 v_sua_rec.cal_type,
354 v_sua_rec.ci_sequence_number,
355 v_sua_rec.version_number) LOOP
356 v_cir2_rec_not_found := FALSE;
357 --
358 -- kdande; 22-Apr-2003; Bug# 2829262
359 -- Added uoo_id parameter to the IGS_PR_GEN_002.prgp_get_sua_prg_prd FUNCTION call.
360 --
361 IF IGS_PR_GEN_002.prgp_get_sua_prg_prd (
362 v_cir2_rec.sup_cal_type,
363 v_cir2_rec.sup_ci_sequence_number,
364 p_person_id,
365 p_course_cd,
366 v_sua_rec.unit_cd,
367 v_sua_rec.cal_type,
368 v_sua_rec.ci_sequence_number,
369 'Y',
370 v_sua_rec.unit_attempt_status,
371 v_sua_rec.discontinued_dt,
372 v_sua_rec.uoo_id) = 'Y' THEN
373 v_match_not_found := FALSE;
374 EXIT;
375 END IF;
376 END LOOP;
377 IF v_cir2_rec_not_found OR
378 v_match_not_found THEN
379 EXIT;
380 END IF;
381 END LOOP;
382 IF v_sua_enrolled THEN
383 EXIT;
384 END IF;
385 IF v_cir2_rec_not_found OR
386 v_match_not_found THEN
387 EXIT;
388 END IF;
389 END LOOP;
390 IF v_sua_enrolled THEN
391 RETURN 'Y';
392 END IF;
393 IF v_cir2_rec_not_found OR
394 v_match_not_found THEN
395 RETURN 'Y';
396 END IF;
397 RETURN 'N';
398 EXCEPTION
399 WHEN OTHERS THEN
400 IF c_cat_ci_cir_cs1%ISOPEN THEN
401 CLOSE c_cat_ci_cir_cs1;
402 END IF;
403 IF c_sua_sca%ISOPEN THEN
404 CLOSE c_sua_sca;
405 END IF;
406 IF c_cat_ci_cir_cs2%ISOPEN THEN
407 CLOSE c_cat_ci_cir_cs2;
408 END IF;
409 RETURN 'N';
410 END;
411 EXCEPTION
412 WHEN OTHERS THEN
413 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
414 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_001.PRGP_GET_DRTN_EFCTV');
415 --IGS_GE_MSG_STACK.ADD;
416
417 END prgp_get_drtn_efctv;
418
419
420
421 FUNCTION prgp_get_msr_efctv(
422 p_prg_cal_type IN VARCHAR2 ,
423 p_prg_sequence_number IN NUMBER ,
424 p_person_id IN NUMBER ,
425 p_course_cd IN VARCHAR2 )
426 RETURN VARCHAR2 AS
430 -- for the purposes of progression IGS_GE_MEASUREMENT.
427 gv_other_detail VARCHAR2(255);
428 BEGIN -- prgp_get_msr_efctv
429 -- Get whether the student is effectively enrolled in a progression period
431 -- Routine returns 'Y', 'N' or 'P' - indicating that IGS_PS_UNIT is currently
432 -- enrolled and is potentially contributing to the period.
433 DECLARE
434 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
435 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
436 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
437 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
438 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
439 v_potential VARCHAR2(1) DEFAULT 'N';
440 v_effective_dt DATE;
441 v_period_found BOOLEAN DEFAULT FALSE;
442 CURSOR c_cat_ci_cir_cs IS
443 SELECT cir.sub_cal_type,
444 cir.sub_ci_sequence_number
445 FROM IGS_CA_TYPE cat,
446 IGS_CA_INST ci,
447 IGS_CA_INST_REL cir,
448 IGS_CA_STAT cs
449 WHERE cir.sup_cal_type = p_prg_cal_type AND
450 cir.sup_ci_sequence_number = p_prg_sequence_number AND
451 ci.cal_type = cir.sub_cal_type AND
452 ci.sequence_number = cir.sub_ci_sequence_number AND
453 ci.cal_status = cs.cal_status AND
454 cs.s_cal_status = cst_active AND
455 cat.cal_type = ci.cal_type AND
456 cat.s_cal_cat = cst_teaching;
457 --
458 -- kdande; 22-Apr-2003; Bug# 2829262
459 -- Added uoo_id field to the SELECT clause of the cursor c_sua_sca.
460 --
461 CURSOR c_sua_sca (
462 cp_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
463 cp_sub_ci_sequence_number
464 IGS_CA_INST_REL.sub_ci_sequence_number%TYPE) IS
465 SELECT sua.unit_cd,
466 sua.cal_type,
467 sua.ci_sequence_number,
468 sua.unit_attempt_status,
469 sua.discontinued_dt,
470 sua.uoo_id
471 FROM IGS_EN_SU_ATTEMPT sua
472 WHERE sua.person_id = p_person_id AND
473 sua.course_cd = p_course_cd AND
474 sua.cal_type = cp_sub_cal_type AND
475 sua.ci_sequence_number = cp_sub_ci_sequence_number AND
476 sua.unit_attempt_status IN (
477 cst_enrolled,
478 cst_completed,
479 cst_discontin);
480 BEGIN
481 FOR v_cir_rec IN c_cat_ci_cir_cs LOOP
482 FOR v_sua_rec IN c_sua_sca (
483 v_cir_rec.sub_cal_type,
484 v_cir_rec.sub_ci_sequence_number) LOOP
485 IF v_sua_rec.unit_attempt_status = cst_enrolled THEN
486 -- Determine if already beyond the period
487 v_effective_dt := prgp_get_prg_efctv(
488 p_prg_cal_type,
489 p_prg_sequence_number);
490 IF v_effective_dt IS NULL OR
491 v_effective_dt > SYSDATE THEN
492 v_potential := 'Y';
493 END IF;
494 ELSE
495 -- Call routine to determine which period it applies
496 v_period_found := FALSE;
497 --
498 -- kdande; 22-Apr-2003; Bug# 2829262
499 -- Added uoo_id parameter to the IGS_PR_GEN_002.prgp_get_sua_prg_prd FUNCTION call.
500 --
501 IF IGS_PR_GEN_002.prgp_get_sua_prg_prd (
502 p_prg_cal_type,
503 p_prg_sequence_number,
504 p_person_id,
505 p_course_cd,
506 v_sua_rec.unit_cd,
507 v_sua_rec.cal_type,
508 v_sua_rec.ci_sequence_number,
509 'Y',
510 v_sua_rec.unit_attempt_status,
511 v_sua_rec.discontinued_dt,
512 v_sua_rec.uoo_id) = 'Y' THEN
513 v_period_found := TRUE;
514 EXIT;
515 END IF;
516 END IF;
517 END LOOP;
518 IF v_period_found THEN
519 EXIT;
520 END IF;
521 END LOOP;
522 IF v_period_found THEN
523 RETURN 'Y';
524 END IF;
525 IF v_potential = 'Y' THEN
526 RETURN 'P';
527 ELSE
528 RETURN 'N';
529 END IF;
530 EXCEPTION
531 WHEN OTHERS THEN
532 IF c_cat_ci_cir_cs%ISOPEN THEN
533 CLOSE c_cat_ci_cir_cs;
534 END IF;
535 IF c_sua_sca%ISOPEN THEN
536 CLOSE c_sua_sca;
537 END IF;
538 RAISE;
539 END;
540 EXCEPTION
541 WHEN OTHERS THEN
542 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
543 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_001.PRGP_GET_MSR_EFCTV');
544 --IGS_GE_MSG_STACK.ADD;
545
546 END prgp_get_msr_efctv;
547
548 FUNCTION prgp_get_prg_efctv(
549 p_prg_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE ,
550 p_prg_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE )
551 RETURN DATE AS
552 BEGIN -- prgp_get_prg_efctv
553 -- Get the effective date of a nominated progression period.
554 -- This is retrieved from the IGS_CA_DA_INST table matching the value
555 -- stored in the progression configuration table. If no date alias is found,
556 -- then the end date of the progression period is returned
557 DECLARE
558 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
559 CURSOR c_dai IS
560 SELECT IGS_CA_GEN_001.calp_get_alias_val(
561 dai.dt_alias,
562 dai.sequence_number,
563 dai.cal_type,
564 dai.ci_sequence_number)
565 FROM IGS_CA_DA_INST dai
566 WHERE dai.cal_type = p_prg_cal_type AND
567 dai.ci_sequence_number = p_prg_sequence_number AND
568 dt_alias =
569 (SELECT sprgcc.effective_end_dt_alias
570 FROM IGS_PR_S_PRG_CONF sprgcc
571 WHERE sprgcc.s_control_num = 1)
572 ORDER BY 1 DESC; -- for latest if multiple dates exist
573 CURSOR c_ci IS
574 SELECT ci.end_dt
575 FROM IGS_CA_INST ci
576 WHERE ci.cal_type = p_prg_cal_type AND
577 ci.sequence_number = p_prg_sequence_number;
578 BEGIN
579 -- Search for alias value within the calendar
580 OPEN c_dai;
581 FETCH c_dai INTO v_alias_val;
582 IF c_dai%NOTFOUND THEN
583 CLOSE c_dai;
584 -- Search for the calendar instance end_dt
585 OPEN c_ci;
586 FETCH c_ci INTO v_alias_val;
587 CLOSE c_ci;
588 ELSE
589 CLOSE c_dai;
590 END IF;
591 RETURN v_alias_val;
592 EXCEPTION
593 WHEN OTHERS THEN
594 IF c_dai%ISOPEN THEN
595 CLOSE c_dai;
596 END IF;
597 IF c_ci%ISOPEN THEN
598 CLOSE c_ci;
599 END IF;
600 RAISE;
601 END;
602 END prgp_get_prg_efctv;
603
604 FUNCTION prgp_get_sca_elps_tm(
605 p_person_id IN NUMBER ,
606 p_course_cd IN VARCHAR2 ,
607 p_version_number IN NUMBER ,
608 p_commencement_dt IN DATE ,
609 p_effective_dt IN DATE DEFAULT SYSDATE)
610 RETURN NUMBER AS
611 gv_other_detail VARCHAR2(255);
612 BEGIN
613 DECLARE
614 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
615 v_time_elapsed NUMBER;
616 v_yrs_elapsed NUMBER;
617 CURSOR c_sca IS
618 SELECT sca.commencement_dt
619 FROM IGS_EN_STDNT_PS_ATT sca
620 WHERE sca.person_id = p_person_id AND
621 sca.course_cd = p_course_cd;
622 BEGIN
623 --Routine to calculate the elapsed time taken by a student for a
624 -- student IGS_PS_COURSE attempt. The routine will return the elapsed time
625 -- as a number (in years or fractions of years).
626 IF p_commencement_dt IS NULL THEN
627 OPEN c_sca;
628 FETCH c_sca INTO v_commencement_dt;
629 IF ((c_sca%NOTFOUND) OR v_commencement_dt IS NULL) THEN
630 CLOSE c_sca;
631 RETURN 0;
632 END IF;
633 CLOSE c_sca;
634 ELSE
635 v_commencement_dt := p_commencement_dt;
636 END IF;
637 v_time_elapsed := MONTHS_BETWEEN(p_effective_dt, v_commencement_dt);
638 v_yrs_elapsed := v_time_elapsed/12;
639 RETURN ROUND(v_yrs_elapsed, 2);
640 END;
641 EXCEPTION
642 WHEN OTHERS THEN
643 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
644 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_GEN_001.PRGP_GET_SCA_ELPS_TM');
645 --IGS_GE_MSG_STACK.ADD;
646
647 END prgp_get_sca_elps_tm;
648
649 FUNCTION PRGP_GET_SCA_GPA(
650 p_person_id IN NUMBER ,
651 p_course_cd IN VARCHAR2 ,
652 p_unit_set_cd IN VARCHAR2 ,
653 p_course_stage_type IN VARCHAR2 ,
654 p_prg_cal_type IN VARCHAR2 ,
655 p_prg_sequence_number IN NUMBER ,
656 p_best_worst IN VARCHAR2 ,
657 p_use_recommended_ind IN VARCHAR2 ,
658 p_use_first_attempt_ind IN VARCHAR2 ,
659 p_use_entered_grade_ind IN VARCHAR2 )
660 RETURN NUMBER AS
661 BEGIN
662 DECLARE
663 v_gpa_value NUMBER;
664 BEGIN
665 v_gpa_value := TO_NUMBER( IGS_RU_GEN_004.rulp_val_gpa (
666 p_person_id,
667 p_course_cd,
668 p_prg_cal_type,
669 p_prg_sequence_number,
670 p_best_worst,
671 p_use_recommended_ind) );
672 RETURN v_gpa_value;
673 EXCEPTION
674 WHEN VALUE_ERROR THEN
675 RETURN 0;
676 END;
677 END prgp_get_sca_gpa;
678 END IGS_PR_GEN_001;
[Home] [Help]