[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_ACAD_DETAILS
Source
1 PACKAGE BODY IGS_PR_ACAD_DETAILS AS
2 /* $Header: IGSPR34B.pls 120.1 2005/09/15 03:19:26 appldev ship $ */
3 --
4 -- kdande; 23-Apr-2003; Bug# 2829262
5 -- Added p_uoo_id parameter to the PROCEDURE populate_sua_table
6 -- swaghmar 15-Sep-2005 Bug# 4491456
7 --
8 PROCEDURE populate_sua_table
9 (
10 p_person_id IN igs_en_su_attempt.person_id%TYPE,
11 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
12 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
13 p_version_number IN igs_en_su_attempt.version_number%TYPE,
14 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
15 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
16 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
17 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
18 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
19 ) AS
20 --
21 v_gpa NUMBER;
22 v_gpa_cp NUMBER;
23 v_gpa_qp NUMBER;
24 v_earned_cp NUMBER;
25 v_attempted_cp NUMBER;
26 v_init_msg_list VARCHAR2(20):= FND_API.G_TRUE;
27 v_return_status VARCHAR2(1);
28 v_msg_count NUMBER;
29 v_msg_data VARCHAR2(2000);
30 v_s_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
31 v_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
32 v_gs_version_number igs_as_grd_sch_grade.version_number%TYPE;
33 v_grade igs_as_grd_sch_grade.grade%TYPE;
34 v_mark igs_as_su_stmptout.mark%TYPE;
35 v_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE;
36 v_origin_course_cd igs_as_su_stmptout.course_cd%TYPE;
37 v_dummy VARCHAR2(1);
38 --
39 CURSOR c_gsg (
40 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
41 cp_version_number igs_as_grd_sch_grade.version_number%TYPE,
42 cp_grade igs_as_grd_sch_grade.grade%TYPE
43 ) IS
44 SELECT 'X'
45 FROM igs_as_grd_sch_grade gsg
46 WHERE cp_grading_schema_cd = gsg.grading_schema_cd
47 AND cp_version_number = gsg.version_number
48 AND cp_grade = gsg.grade
49 AND gsg.show_on_official_ntfctn_ind = 'Y';
50 --
51 BEGIN
52 --
53 -- kdande; 23-Apr-2003; Bug# 2829262
54 -- Added uoo_id parameter to the igs_as_gen_003.assp_get_sua_outcome
55 -- FUNCTION call
56 --
57 v_s_result_type := igs_as_gen_003.assp_get_sua_outcome (
58 p_person_id,
59 p_course_cd,
60 p_unit_cd,
61 p_teach_cal_type,
62 p_teach_ci_sequence_number,
63 p_unit_attempt_status,
64 'Y',
65 v_outcome_dt,
66 v_grading_schema_cd,
67 v_gs_version_number,
68 v_grade,
69 v_mark,
70 v_origin_course_cd,
71 p_uoo_id,
72 --added by LKAKI----
73 'N');
74 --
75 OPEN c_gsg (v_grading_schema_cd,
76 v_gs_version_number,
77 v_grade);
78 FETCH c_gsg INTO v_dummy;
79 IF c_gsg%NOTFOUND THEN
80 v_grade := NULL;
81 v_mark := NULL;
82 END IF;
83 CLOSE c_gsg;
84 --
85 -- Get the Student Unit Attempt CP and GPA values
86 --
87 -- As part of FA111 build bug 2708843 replaced the calls to
88 -- igs_pr_cp_gpa.get_sua_gpa and igs_pr_cp_gpa.get_sua_cp
89 -- with a single call to igs_pr_cp_gpa.get_sua_all
90 --
91 -- kdande; 23-Apr-2003; Bug# 2829262
92 -- Added uoo_id parameter to the igs_pr_cp_gpa.get_sua_all FUNCTION call
93 --
94 igs_pr_cp_gpa.get_sua_all (
95 p_person_id => p_person_id,
96 p_course_cd => p_course_cd,
97 p_unit_cd => p_unit_cd,
98 p_unit_version_number => p_version_number,
99 p_teach_cal_type => p_teach_cal_type,
100 p_teach_ci_sequence_number => p_teach_ci_sequence_number,
101 p_stat_type => p_stat_type,
102 p_system_stat => NULL,
103 p_earned_cp => v_earned_cp,
104 p_attempted_cp => v_attempted_cp,
105 p_gpa_value => v_gpa,
106 p_gpa_cp => v_gpa_cp,
107 p_gpa_quality_points => v_gpa_qp,
108 p_init_msg_list => v_init_msg_list,
109 p_return_status => v_return_status,
110 p_msg_count => v_msg_count,
111 p_msg_data => v_msg_data,
112 p_uoo_id => p_uoo_id
113 );
114
115 --
116 -- Store all of the values in the SUA temp table
117 --
118 sua_table(1).person_id := p_person_id;
119 sua_table(1).course_cd := p_course_cd;
120 sua_table(1).unit_cd := p_unit_cd;
121 sua_table(1).version_number := p_version_number;
122 sua_table(1).teach_cal_type := p_teach_cal_type;
123 sua_table(1).teach_ci_sequence_number := p_teach_ci_sequence_number;
124 sua_table(1).grade := v_grade;
125 sua_table(1).mark := v_mark;
126 sua_table(1).gpa := v_gpa;
127 sua_table(1).gpa_cp := v_gpa_cp;
128 sua_table(1).gpa_qp := v_gpa_qp;
129 sua_table(1).earned_cp := v_earned_cp;
130 sua_table(1).attempted_cp := v_attempted_cp;
131 sua_table(1).uoo_id := p_uoo_id;
132 --
133 END populate_sua_table;
134 --
135 --swaghmar 15-Sep-2005 Bug# 4491456
136 --
137 PROCEDURE populate_load_table
138 (
139 p_person_id IN igs_en_su_attempt.person_id%TYPE,
140 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
141 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
142 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
143 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
144 ) AS
145 --
146 v_load_gpa igs_as_grd_sch_grade.gpa_val%TYPE;
147 v_load_gpa_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
148 v_load_gpa_qp igs_ps_unit_ver.achievable_credit_points%TYPE;
149 v_load_earned_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
150 v_load_attempted_cp igs_ps_unit_ver.achievable_credit_points%TYPE;
151 v_cum_gpa NUMBER;
152 v_cum_gpa_cp NUMBER;
153 v_cum_gpa_qp NUMBER;
154 v_cum_earned_cp NUMBER;
155 v_cum_attempted_cp NUMBER;
156 v_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
157 v_return_status VARCHAR2(1);
158 v_msg_count NUMBER;
159 v_msg_data VARCHAR2(2000);
160 --
161 BEGIN
162 --
163 -- Get the Term Student Program Attempt CP and
164 -- Term Student Program Attempt GPA values
165 --
166 /*
167 As part of FA111 build bug 2708843
168 replaced the calls to igs_pr_cp_gpa.get_cp_stats
169 and igs_pr_cp_gpa.get_gpa_stats with a single call to
170 igs_pr_cp_gpa.get_all_stats
171 */
172 igs_pr_cp_gpa.get_all_stats(
173 p_person_id => p_person_id,
174 p_course_cd => p_course_cd,
175 p_stat_type => p_stat_type,
176 p_load_cal_type => p_load_cal_type,
177 p_load_ci_sequence_number => p_load_ci_sequence_number,
178 p_system_stat => NULL,
179 p_cumulative_ind => 'N',
180 p_earned_cp => v_load_earned_cp,
181 p_attempted_cp => v_load_attempted_cp,
182 p_gpa_value => v_load_gpa,
183 p_gpa_cp => v_load_gpa_cp,
184 p_gpa_quality_points => v_load_gpa_qp,
185 p_init_msg_list => v_init_msg_list,
186 p_return_status => v_return_status,
187 p_msg_count => v_msg_count,
188 p_msg_data => v_msg_data
189 );
190
191 --
192 -- Get the Cum Student Program Attempt CP values
193 -- and Student Program Attempt GPA values
194 --
195 /*
196 As part of FA111 build bug 2708843
197 replaced the calls to igs_pr_cp_gpa.get_cp_stats
198 and igs_pr_cp_gpa.get_gpa_stats with a single call to
199 igs_pr_cp_gpa.get_all_stats
200 */
201 igs_pr_cp_gpa.get_all_stats(
202 p_person_id => p_person_id,
203 p_course_cd => p_course_cd,
204 p_stat_type => p_stat_type,
205 p_load_cal_type => p_load_cal_type,
206 p_load_ci_sequence_number => p_load_ci_sequence_number,
207 p_system_stat => NULL,
208 p_cumulative_ind => 'Y',
209 p_earned_cp => v_cum_earned_cp,
210 p_attempted_cp => v_cum_attempted_cp,
211 p_gpa_value => v_cum_gpa,
212 p_gpa_cp => v_cum_gpa_cp,
213 p_gpa_quality_points => v_cum_gpa_qp,
214 p_init_msg_list => v_init_msg_list,
215 p_return_status => v_return_status,
216 p_msg_count => v_msg_count,
217 p_msg_data => v_msg_data
218 );
219
220
221 --
222 -- Store all of the values in the SUA temp table
223 --
224 load_table(1).person_id := p_person_id;
225 load_table(1).course_cd := p_course_cd;
226 load_table(1).load_cal_type := p_load_cal_type;
227 load_table(1).load_ci_sequence_number := p_load_ci_sequence_number;
228 load_table(1).load_gpa := v_load_gpa;
229 load_table(1).load_gpa_cp := v_load_gpa_cp;
230 load_table(1).load_gpa_qp := v_load_gpa_qp;
231 load_table(1).load_earned_cp := v_load_earned_cp;
232 load_table(1).load_attempted_cp := v_load_attempted_cp;
233 load_table(1).cum_gpa := v_cum_gpa;
234 load_table(1).cum_gpa_cp := v_cum_gpa_cp;
235 load_table(1).cum_gpa_qp := v_cum_gpa_qp;
236 load_table(1).cum_earned_cp := v_cum_earned_cp;
237 load_table(1).cum_attempted_cp := v_cum_attempted_cp;
238 --
239 END populate_load_table;
240 --
241 -- kdande; 23-Apr-2003; Bug# 2829262
242 -- Added p_uoo_id parameter to the FUNCTION get_sua_gpa
243 --
244 FUNCTION get_sua_gpa
245 (
246 p_person_id IN igs_en_su_attempt.person_id%TYPE,
247 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
248 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
249 p_version_number IN igs_en_su_attempt.version_number%TYPE,
250 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
251 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
252 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
253 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
254 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
255 ) RETURN NUMBER AS
256 --
257 BEGIN
258 --
259 IF sua_table.EXISTS(1) AND
260 sua_table(1).person_id = p_person_id AND
261 sua_table(1).course_cd = p_course_cd AND
262 sua_table(1).unit_cd = p_unit_cd AND
263 sua_table(1).version_number = p_version_number AND
264 sua_table(1).teach_cal_type = p_teach_cal_type AND
265 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
266 sua_table(1).uoo_id = p_uoo_id THEN
267 --
268 RETURN sua_table(1).gpa;
269 --
270 ELSE
271 --
272 -- kdande; 23-Apr-2003; Bug# 2829262
273 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
274 --
275 populate_sua_table (
276 p_person_id,
277 p_course_cd,
278 p_unit_cd,
279 p_version_number,
280 p_teach_cal_type,
281 p_teach_ci_sequence_number,
282 p_unit_attempt_status,
283 p_stat_type,
284 p_uoo_id
285 );
286 --
287 RETURN sua_table(1).gpa;
288 --
289 END IF;
290 --
291 END get_sua_gpa;
292 --
293 -- kdande; 23-Apr-2003; Bug# 2829262
294 -- Added p_uoo_id parameter to the FUNCTION get_sua_gpa_cp
295 --
296 FUNCTION get_sua_gpa_cp
297 (
298 p_person_id IN igs_en_su_attempt.person_id%TYPE,
299 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
300 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
301 p_version_number IN igs_en_su_attempt.version_number%TYPE,
302 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
303 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
304 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
305 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
306 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
307 ) RETURN NUMBER AS
308 --
309 BEGIN
310 --
311 IF sua_table.EXISTS(1) AND
312 sua_table(1).person_id = p_person_id AND
313 sua_table(1).course_cd = p_course_cd AND
314 sua_table(1).unit_cd = p_unit_cd AND
315 sua_table(1).version_number = p_version_number AND
316 sua_table(1).teach_cal_type = p_teach_cal_type AND
317 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
318 sua_table(1).uoo_id = p_uoo_id THEN
319 --
320 RETURN sua_table(1).gpa_cp;
321 --
322 ELSE
323 --
324 -- kdande; 23-Apr-2003; Bug# 2829262
325 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
326 --
327 populate_sua_table (
328 p_person_id,
329 p_course_cd,
330 p_unit_cd,
331 p_version_number,
332 p_teach_cal_type,
333 p_teach_ci_sequence_number,
334 p_unit_attempt_status,
335 p_stat_type,
336 p_uoo_id
337 );
338 --
339 RETURN sua_table(1).gpa_cp;
340 --
341 END IF;
342 --
343 END get_sua_gpa_cp;
344 --
345 -- kdande; 23-Apr-2003; Bug# 2829262
346 -- Added p_uoo_id parameter to the FUNCTION get_sua_gpa_qp
347 --
348 FUNCTION get_sua_gpa_qp
349 (
350 p_person_id IN igs_en_su_attempt.person_id%TYPE,
351 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
352 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
353 p_version_number IN igs_en_su_attempt.version_number%TYPE,
354 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
355 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
356 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
357 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
358 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
359 ) RETURN NUMBER AS
360 --
361 BEGIN
362 --
363 IF sua_table.EXISTS(1) AND
364 sua_table(1).person_id = p_person_id AND
365 sua_table(1).course_cd = p_course_cd AND
366 sua_table(1).unit_cd = p_unit_cd AND
367 sua_table(1).version_number = p_version_number AND
368 sua_table(1).teach_cal_type = p_teach_cal_type AND
369 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
370 sua_table(1).uoo_id = p_uoo_id THEN
371 --
372 RETURN sua_table(1).gpa_qp;
373 --
374 ELSE
375 --
376 -- kdande; 23-Apr-2003; Bug# 2829262
377 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
378 --
379 populate_sua_table (
380 p_person_id,
381 p_course_cd,
382 p_unit_cd,
383 p_version_number,
384 p_teach_cal_type,
385 p_teach_ci_sequence_number,
386 p_unit_attempt_status,
387 p_stat_type,
388 p_uoo_id
389 );
390 --
391 RETURN sua_table(1).gpa_qp;
392 --
393 END IF;
394 --
395 END get_sua_gpa_qp;
396 --
397 -- kdande; 23-Apr-2003; Bug# 2829262
398 -- Added p_uoo_id parameter to the FUNCTION get_sua_earned_cp
399 --
400 FUNCTION get_sua_earned_cp
401 (
402 p_person_id IN igs_en_su_attempt.person_id%TYPE,
403 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
404 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
405 p_version_number IN igs_en_su_attempt.version_number%TYPE,
406 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
407 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
408 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
409 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
410 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
411 ) RETURN NUMBER AS
412 --
413 BEGIN
414 --
415 IF sua_table.EXISTS(1) AND
416 sua_table(1).person_id = p_person_id AND
417 sua_table(1).course_cd = p_course_cd AND
418 sua_table(1).unit_cd = p_unit_cd AND
419 sua_table(1).version_number = p_version_number AND
420 sua_table(1).teach_cal_type = p_teach_cal_type AND
421 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
422 sua_table(1).uoo_id = p_uoo_id THEN
423 --
424 RETURN sua_table(1).earned_cp;
425 --
426 ELSE
427 --
428 -- kdande; 23-Apr-2003; Bug# 2829262
429 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
430 --
431 populate_sua_table (
432 p_person_id,
433 p_course_cd,
434 p_unit_cd,
435 p_version_number,
436 p_teach_cal_type,
437 p_teach_ci_sequence_number,
438 p_unit_attempt_status,
439 p_stat_type,
440 p_uoo_id
441 );
442 --
443 RETURN sua_table(1).earned_cp;
444 --
445 END IF;
446 --
447 END get_sua_earned_cp;
448 --
449 -- kdande; 23-Apr-2003; Bug# 2829262
450 -- Added p_uoo_id parameter to the FUNCTION get_sua_attempted_cp
451 --
452 FUNCTION get_sua_attempted_cp
453 (
454 p_person_id IN igs_en_su_attempt.person_id%TYPE,
455 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
456 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
457 p_version_number IN igs_en_su_attempt.version_number%TYPE,
458 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
459 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
460 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
461 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
462 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
463 ) RETURN NUMBER AS
464 --
465 BEGIN
466 --
467 IF sua_table.EXISTS(1) AND
468 sua_table(1).person_id = p_person_id AND
469 sua_table(1).course_cd = p_course_cd AND
470 sua_table(1).unit_cd = p_unit_cd AND
471 sua_table(1).version_number = p_version_number AND
472 sua_table(1).teach_cal_type = p_teach_cal_type AND
473 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
474 sua_table(1).uoo_id = p_uoo_id THEN
475 --
476 RETURN sua_table(1).attempted_cp;
477 --
478 ELSE
479 --
480 -- kdande; 23-Apr-2003; Bug# 2829262
481 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
482 --
483 populate_sua_table (
484 p_person_id,
485 p_course_cd,
486 p_unit_cd,
487 p_version_number,
488 p_teach_cal_type,
489 p_teach_ci_sequence_number,
490 p_unit_attempt_status,
491 p_stat_type,
492 p_uoo_id
493 );
494 --
495 RETURN sua_table(1).attempted_cp;
496 --
497 END IF;
498 --
499 END get_sua_attempted_cp;
500 --
501 -- kdande; 23-Apr-2003; Bug# 2829262
502 -- Added p_uoo_id parameter to the FUNCTION get_sua_grade
503 --
504 FUNCTION get_sua_grade
505 (
506 p_person_id IN igs_en_su_attempt.person_id%TYPE,
507 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
508 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
509 p_version_number IN igs_en_su_attempt.version_number%TYPE,
510 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
511 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
512 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
513 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
514 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
515 ) RETURN VARCHAR2 AS
516 --
517 BEGIN
518 --
519 IF sua_table.EXISTS(1) AND
520 sua_table(1).person_id = p_person_id AND
521 sua_table(1).course_cd = p_course_cd AND
522 sua_table(1).unit_cd = p_unit_cd AND
523 sua_table(1).version_number = p_version_number AND
524 sua_table(1).teach_cal_type = p_teach_cal_type AND
525 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
526 sua_table(1).uoo_id = p_uoo_id THEN
527 --
528 RETURN sua_table(1).grade;
529 --
530 ELSE
531 --
532 -- kdande; 23-Apr-2003; Bug# 2829262
533 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
534 --
535 populate_sua_table (
536 p_person_id,
537 p_course_cd,
538 p_unit_cd,
539 p_version_number,
540 p_teach_cal_type,
541 p_teach_ci_sequence_number,
542 p_unit_attempt_status,
543 p_stat_type,
544 p_uoo_id
545 );
546 --
547 RETURN sua_table(1).grade;
548 --
549 END IF;
550 --
551 END get_sua_grade;
552 --
553 -- kdande; 23-Apr-2003; Bug# 2829262
554 -- Added p_uoo_id parameter to the FUNCTION get_sua_mark
555 --
556 FUNCTION get_sua_mark
557 (
558 p_person_id IN igs_en_su_attempt.person_id%TYPE,
559 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
560 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
561 p_version_number IN igs_en_su_attempt.version_number%TYPE,
562 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
563 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
564 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
565 p_stat_type IN igs_pr_org_stat.stat_type%TYPE,
566 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
567 ) RETURN NUMBER AS
568 --
569 BEGIN
570 --
571 IF sua_table.EXISTS(1) AND
572 sua_table(1).person_id = p_person_id AND
573 sua_table(1).course_cd = p_course_cd AND
574 sua_table(1).unit_cd = p_unit_cd AND
575 sua_table(1).version_number = p_version_number AND
576 sua_table(1).teach_cal_type = p_teach_cal_type AND
577 sua_table(1).teach_ci_sequence_number = p_teach_ci_sequence_number AND
578 sua_table(1).uoo_id = p_uoo_id THEN
579 --
580 RETURN sua_table(1).mark;
581 --
582 ELSE
583 --
584 -- kdande; 23-Apr-2003; Bug# 2829262
585 -- Added uoo_id parameter to the populate_sua_table PROCEDURE call
586 --
587 populate_sua_table (
588 p_person_id,
589 p_course_cd,
590 p_unit_cd,
591 p_version_number,
592 p_teach_cal_type,
593 p_teach_ci_sequence_number,
594 p_unit_attempt_status,
595 p_stat_type,
596 p_uoo_id
597 );
598 --
599 RETURN sua_table(1).mark;
600 --
601 END IF;
602 --
603 END get_sua_mark;
604 --
605 --
606 --
607 FUNCTION get_sua_yop
608 (
609 p_person_id IN igs_en_su_attempt.person_id%TYPE,
610 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
611 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
612 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE
613 ) RETURN VARCHAR2 AS
614 --
615 -- This function returns the Unit Set Code of any YOP specific Student Unit
616 -- Set Attempts which have Selection and Requirements Complete/End Dates
617 -- which span the Census Date of the Teaching Period provided. If more than
618 -- one exists the one with the latest selection date will be returned
619 --
620 v_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE;
621 --
622 CURSOR c_susa IS
623 SELECT us.unit_set_cd
624 FROM IGS_AS_SU_SETATMPT SUSA,
625 IGS_EN_UNIT_SET US,
626 IGS_EN_UNIT_SET_CAT USC
627 WHERE p_person_id = susa.person_id
628 AND p_course_cd = susa.course_cd
629 AND (igs_en_gen_015.get_effective_census_date(
630 NULL, NULL, p_teach_cal_type, p_teach_ci_sequence_number)
631 BETWEEN susa.selection_dt AND NVL(susa.rqrmnts_complete_dt,
632 NVL(susa.end_dt, fnd_date.canonical_to_date('9999/12/31'))))
633 AND susa.unit_set_cd = us.unit_set_cd
634 AND us.unit_set_cat = usc.unit_set_cat
635 AND usc.s_unit_set_cat = 'PRENRL_YR'
636 ORDER BY susa.selection_dt DESC;
637 --
638 BEGIN
639 --
640 OPEN c_susa;
641 FETCH c_susa INTO v_unit_set_cd;
642 --
643 IF c_susa%FOUND THEN
644 CLOSE c_susa;
645 RETURN v_unit_set_cd;
646 ELSE
647 CLOSE c_susa;
648 RETURN NULL;
649 END IF;
650 --
651 END get_sua_yop;
652 --
653 --
654 --
655 FUNCTION get_load_gpa
656 (
657 p_person_id IN igs_en_su_attempt.person_id%TYPE,
658 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
659 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
660 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
661 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
662 ) RETURN NUMBER AS
663 --
664 BEGIN
665 --
666 IF load_table.EXISTS(1) AND
667 load_table(1).person_id = p_person_id AND
668 load_table(1).course_cd = p_course_cd AND
669 load_table(1).load_cal_type = p_load_cal_type AND
670 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
671 --
672 RETURN load_table(1).load_gpa;
673 --
674 ELSE
675 --
676 populate_load_table (
677 p_person_id,
678 p_course_cd,
679 p_load_cal_type,
680 p_load_ci_sequence_number,
681 p_stat_type
682 );
683 --
684 RETURN load_table(1).load_gpa;
685 --
686 END IF;
687 --
688 END get_load_gpa;
689 --
690 --
691 --
692 FUNCTION get_load_gpa_cp
693 (
694 p_person_id IN igs_en_su_attempt.person_id%TYPE,
695 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
696 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
697 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
698 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
699 ) RETURN NUMBER AS
700 --
701 BEGIN
702 --
703 IF load_table.EXISTS(1) AND
704 load_table(1).person_id = p_person_id AND
705 load_table(1).course_cd = p_course_cd AND
706 load_table(1).load_cal_type = p_load_cal_type AND
707 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
708 --
709 RETURN load_table(1).load_gpa_cp;
710 --
711 ELSE
712 --
713 populate_load_table (
714 p_person_id,
715 p_course_cd,
716 p_load_cal_type,
717 p_load_ci_sequence_number,
718 p_stat_type
719 );
720 --
721 RETURN load_table(1).load_gpa_cp;
722 --
723 END IF;
724 --
725 END get_load_gpa_cp;
726 --
727 --
728 --
729 FUNCTION get_load_gpa_qp
730 (
731 p_person_id IN igs_en_su_attempt.person_id%TYPE,
732 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
733 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
734 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
735 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
736 ) RETURN NUMBER AS
737 --
738 BEGIN
739 --
740 IF load_table.EXISTS(1) AND
741 load_table(1).person_id = p_person_id AND
742 load_table(1).course_cd = p_course_cd AND
743 load_table(1).load_cal_type = p_load_cal_type AND
744 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
745 --
746 RETURN load_table(1).load_gpa_qp;
747 ELSE
748 --
749 populate_load_table (
750 p_person_id,
751 p_course_cd,
752 p_load_cal_type,
753 p_load_ci_sequence_number,
754 p_stat_type
755 );
756 --
757 RETURN load_table(1).load_gpa_qp;
758 --
759 END IF;
760 --
761 END get_load_gpa_qp;
762 --
763 --
764 --
765 FUNCTION get_load_earned_cp
766 (
767 p_person_id IN igs_en_su_attempt.person_id%TYPE,
768 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
769 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
770 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
771 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
772 ) RETURN NUMBER AS
773 --
774 BEGIN
775 --
776 IF load_table.EXISTS(1) AND
777 load_table(1).person_id = p_person_id AND
778 load_table(1).course_cd = p_course_cd AND
779 load_table(1).load_cal_type = p_load_cal_type AND
780 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
781 --
782 RETURN load_table(1).load_earned_cp;
783 --
784 ELSE
785 --
786 populate_load_table (
787 p_person_id,
788 p_course_cd,
789 p_load_cal_type,
790 p_load_ci_sequence_number,
791 p_stat_type
792 );
793 --
794 RETURN load_table(1).load_earned_cp;
795 --
796 END IF;
797 --
798 END get_load_earned_cp;
799 --
800 --
801 --
802 FUNCTION get_load_attempted_cp
803 (
804 p_person_id IN igs_en_su_attempt.person_id%TYPE,
805 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
806 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
807 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
808 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
809 ) RETURN NUMBER AS
810 --
811 BEGIN
812 --
813 IF load_table.EXISTS(1) AND
814 load_table(1).person_id = p_person_id AND
815 load_table(1).course_cd = p_course_cd AND
816 load_table(1).load_cal_type = p_load_cal_type AND
817 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
818 --
819 RETURN load_table(1).load_attempted_cp;
820 ELSE
821 --
822 populate_load_table (
823 p_person_id,
824 p_course_cd,
825 p_load_cal_type,
826 p_load_ci_sequence_number,
827 p_stat_type
828 );
829 --
830 RETURN load_table(1).load_attempted_cp;
831 --
832 END IF;
833 --
834 END get_load_attempted_cp;
835 --
836 --
837 --
838 FUNCTION get_cum_gpa
839 (
840 p_person_id IN igs_en_su_attempt.person_id%TYPE,
841 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
842 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
843 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
844 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
845 ) RETURN NUMBER AS
846 --
847 BEGIN
848 --
849 IF load_table.EXISTS(1) AND
850 load_table(1).person_id = p_person_id AND
851 load_table(1).course_cd = p_course_cd AND
852 load_table(1).load_cal_type = p_load_cal_type AND
853 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
854 --
855 RETURN load_table(1).cum_gpa;
856 ELSE
857 --
858 populate_load_table (
859 p_person_id,
860 p_course_cd,
861 p_load_cal_type,
862 p_load_ci_sequence_number,
863 p_stat_type
864 );
865 --
866 RETURN load_table(1).cum_gpa;
867 --
868 END IF;
869 --
870 END get_cum_gpa;
871 --
872 --
873 --
874 FUNCTION get_cum_gpa_cp
875 (
876 p_person_id IN igs_en_su_attempt.person_id%TYPE,
877 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
878 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
879 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
880 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
881 ) RETURN NUMBER AS
882 --
883 BEGIN
884 --
885 IF load_table.EXISTS(1) AND
886 load_table(1).person_id = p_person_id AND
887 load_table(1).course_cd = p_course_cd AND
888 load_table(1).load_cal_type = p_load_cal_type AND
889 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
890 --
891 RETURN load_table(1).cum_gpa_cp;
892 ELSE
893 --
894 populate_load_table (
895 p_person_id,
896 p_course_cd,
897 p_load_cal_type,
898 p_load_ci_sequence_number,
899 p_stat_type
900 );
901 --
902 RETURN load_table(1).cum_gpa_cp;
903 --
904 END IF;
905 --
906 END get_cum_gpa_cp;
907 --
908 --
909 --
910 FUNCTION get_cum_gpa_qp
911 (
912 p_person_id IN igs_en_su_attempt.person_id%TYPE,
913 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
914 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
915 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
916 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
917 ) RETURN NUMBER AS
918 --
919 BEGIN
920 --
921 IF load_table.EXISTS(1) AND
922 load_table(1).person_id = p_person_id AND
923 load_table(1).course_cd = p_course_cd AND
924 load_table(1).load_cal_type = p_load_cal_type AND
925 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
926 --
927 RETURN load_table(1).cum_gpa_qp;
928 ELSE
929 --
930 populate_load_table (
931 p_person_id,
932 p_course_cd,
933 p_load_cal_type,
934 p_load_ci_sequence_number,
935 p_stat_type
936 );
937 --
938 RETURN load_table(1).cum_gpa_qp;
939 --
940 END IF;
941 --
942 END get_cum_gpa_qp;
943 --
944 --
945 --
946 FUNCTION get_cum_earned_cp
947 (
948 p_person_id IN igs_en_su_attempt.person_id%TYPE,
949 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
950 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
951 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
952 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
953 ) RETURN NUMBER AS
954 --
955 BEGIN
956 --
957 IF load_table.EXISTS(1) AND
958 load_table(1).person_id = p_person_id AND
959 load_table(1).course_cd = p_course_cd AND
960 load_table(1).load_cal_type = p_load_cal_type AND
961 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
962 --
963 RETURN load_table(1).cum_earned_cp;
964 ELSE
965 --
966 populate_load_table (
967 p_person_id,
968 p_course_cd,
969 p_load_cal_type,
970 p_load_ci_sequence_number,
971 p_stat_type
972 );
973 --
974 RETURN load_table(1).cum_earned_cp;
975 --
976 END IF;
977 --
978 END get_cum_earned_cp;
979 --
980 --
981 --
982 FUNCTION get_cum_attempted_cp
983 (
984 p_person_id IN igs_en_su_attempt.person_id%TYPE,
985 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
986 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
987 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
988 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
989 ) RETURN NUMBER AS
990 --
991 BEGIN
992 --
993 IF load_table.EXISTS(1) AND
994 load_table(1).person_id = p_person_id AND
995 load_table(1).course_cd = p_course_cd AND
996 load_table(1).load_cal_type = p_load_cal_type AND
997 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
998 --
999 RETURN load_table(1).cum_attempted_cp;
1000 ELSE
1001 --
1002 populate_load_table (
1003 p_person_id,
1004 p_course_cd,
1005 p_load_cal_type,
1006 p_load_ci_sequence_number,
1007 p_stat_type
1008 );
1009 --
1010 RETURN load_table(1).cum_attempted_cp;
1011 --
1012 END IF;
1013 --
1014 END get_cum_attempted_cp;
1015 --
1016 END IGS_PR_ACAD_DETAILS;