1 PACKAGE BODY igs_pr_gen_005 AS
2 /* $Header: IGSPR26B.pls 120.0 2005/07/05 12:13:31 appldev noship $ */
3 FUNCTION igs_pr_clc_apl_expry (
4 p_course_cd IN VARCHAR2,
5 p_version_number IN NUMBER,
6 p_prg_cal_type IN VARCHAR2,
7 p_prg_sequence_number IN NUMBER,
8 p_progression_rule_cat IN VARCHAR2,
9 p_pra_sequence_number IN NUMBER,
10 p_sequence_number IN NUMBER
11 ) RETURN DATE IS
12 gv_other_detail VARCHAR2 (255);
13 BEGIN -- IGS_PR_clc_apl_expry
14 -- Calculate the appeal expiry date for a nominated rule within a nominated
15 -- progression calendar. This routine also considers whether appeal is
16 -- actually permitted ; if not, the date is returned as null
17 DECLARE
18 cst_ap CONSTANT VARCHAR2 (2) := 'AP';
19 v_expiry_dt DATE;
20 v_cutoff_dt DATE;
21 v_level VARCHAR2 (10);
22 v_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
23 v_ou_start_dt igs_or_unit.start_dt%TYPE;
24 v_stream_number igs_pr_s_prg_cal.stream_num%TYPE;
25 v_show_cause_length igs_pr_s_prg_cal.show_cause_length%TYPE;
26 v_appeal_length igs_pr_s_prg_cal.appeal_length%TYPE;
27 BEGIN
28 IF igs_pr_gen_005.igs_pr_get_appeal_alwd (
29 p_progression_rule_cat,
30 p_pra_sequence_number,
31 p_sequence_number,
32 p_course_cd,
33 p_version_number
34 ) = 'N' THEN
35 RETURN NULL;
36 END IF;
37 igs_pr_gen_003.igs_pr_get_cal_parm (
38 p_course_cd,
39 p_version_number,
40 p_prg_cal_type,
41 v_level,
42 v_org_unit_cd,
43 v_ou_start_dt,
44 v_stream_number,
45 v_show_cause_length,
46 v_appeal_length
47 );
48 IF v_level IS NULL THEN
49 RETURN NULL;
50 ELSE
51 v_expiry_dt := TRUNC (SYSDATE) + NVL (v_appeal_length, 0);
52 v_cutoff_dt := igs_pr_gen_005.igs_pr_get_prg_dai (
53 p_course_cd,
54 p_version_number,
55 p_prg_cal_type,
56 p_prg_sequence_number,
57 cst_ap
58 );
59 IF v_cutoff_dt IS NOT NULL
60 AND v_expiry_dt > v_cutoff_dt THEN
61 IF v_cutoff_dt < SYSDATE THEN
62 v_expiry_dt := TRUNC (SYSDATE);
63 ELSE
64 v_expiry_dt := TRUNC (v_cutoff_dt);
65 END IF;
66 END IF;
67 END IF;
68 RETURN v_expiry_dt;
69 END;
70 EXCEPTION
71 WHEN OTHERS THEN
72 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
73 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_CLC_APL_EXPRY');
74 igs_ge_msg_stack.ADD;
75 app_exception.raise_exception;
76 END igs_pr_clc_apl_expry;
77
78 FUNCTION igs_pr_clc_cause_expry (
79 p_course_cd IN VARCHAR2,
80 p_version_number IN NUMBER,
81 p_prg_cal_type IN VARCHAR2,
82 p_prg_sequence_number IN NUMBER,
83 p_progression_rule_cat IN VARCHAR2,
84 p_pra_sequence_number IN NUMBER,
85 p_sequence_number IN NUMBER
86 ) RETURN DATE IS
87 gv_other_detail VARCHAR2 (255);
88 BEGIN -- IGS_PR_clc_cause_expiry
89 -- Calculate the show case expiry date for a nominated rule within a
90 -- nominated progression calender. This routine also considers whether
91 -- show case is actually permitted; if not, the date is returned as null.
92 DECLARE
93 cst_sc CONSTANT VARCHAR2 (10) := 'SC';
94 v_level VARCHAR2 (10);
95 v_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
96 v_ou_start_dt igs_or_unit.start_dt%TYPE;
97 v_stream_number igs_pr_s_prg_cal.stream_num%TYPE;
98 v_show_cause_length igs_pr_s_prg_cal.show_cause_length%TYPE;
99 v_appeal_length igs_pr_s_prg_cal.appeal_length%TYPE;
100 v_expiry_dt DATE;
101 v_cutoff_dt DATE;
102 BEGIN
103 IF igs_pr_gen_005.igs_pr_get_cause_alwd (
104 p_progression_rule_cat,
105 p_pra_sequence_number,
106 p_sequence_number,
107 p_course_cd,
108 p_version_number
109 ) = 'N' THEN
110 RETURN NULL;
111 END IF;
112 igs_pr_gen_003.igs_pr_get_cal_parm (
113 p_course_cd,
114 p_version_number,
115 p_prg_cal_type,
116 v_level,
117 v_org_unit_cd,
118 v_ou_start_dt,
119 v_stream_number,
120 v_show_cause_length,
121 v_appeal_length
122 );
123 IF v_level IS NULL THEN
124 -- Could not determine from configuration structure
125 RETURN NULL;
126 ELSE
127 v_expiry_dt := TRUNC (SYSDATE) + NVL (v_show_cause_length, 0);
128 v_cutoff_dt := igs_pr_gen_005.igs_pr_get_prg_dai (
129 p_course_cd,
130 p_version_number,
131 p_prg_cal_type,
132 p_prg_sequence_number,
133 cst_sc
134 );
135 IF v_cutoff_dt IS NOT NULL
136 AND v_expiry_dt > v_cutoff_dt THEN
137 IF v_cutoff_dt < SYSDATE THEN
138 v_expiry_dt := TRUNC (SYSDATE);
139 ELSE
140 v_expiry_dt := v_cutoff_dt;
141 END IF;
142 END IF;
143 END IF;
144 RETURN v_expiry_dt;
145 END;
146 EXCEPTION
147 WHEN OTHERS THEN
148 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
149 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_CLC_CAUSE_EXPRY');
150 igs_ge_msg_stack.ADD;
151 app_exception.raise_exception;
152 END igs_pr_clc_cause_expry;
153
154 FUNCTION igs_pr_clc_stdnt_comp (
155 p_person_id IN NUMBER,
156 p_sca_course_cd IN VARCHAR2,
157 p_sca_version_number IN NUMBER,
158 p_course_cd IN VARCHAR2,
159 p_version_number IN NUMBER,
160 p_unit_set_cd IN VARCHAR2,
161 p_us_version_number NUMBER,
162 p_cst_sequence_number IN NUMBER,
163 p_predicted_ind IN VARCHAR2 DEFAULT 'N',
164 p_s_rule_call_cd IN VARCHAR2,
165 p_key IN VARCHAR2,
166 p_evaluate_ind IN VARCHAR2 DEFAULT 'N',
167 p_log_dt OUT NOCOPY DATE,
168 p_message_name OUT NOCOPY VARCHAR2
169 ) RETURN BOOLEAN IS
170 gv_other_detail VARCHAR2 (255);
171 BEGIN -- IGS_PR_clc_stdnt_comp
172 -- This routine is used by the functionality associated with the form PRGF9030
173 -- Inquire On Student Completion. The form displays the completion rules
174 -- associated with a course. This routine is called to evaluate the completion
175 -- rules for a student's course attempt. It can be at different levels,
176 -- eg. course, course stage, unit set or alternative exit. This routine will
177 -- call the appropriate routine in the rules sub-system which will return the
178 -- result of the evaulation in a text field. This routine will pass the text
179 -- fields to genp_ins_ssp_cmp_dtl which will break up the text fields and
180 -- store them into a temporary table where the data will be queried and
181 -- displayed by the form.The evaluation parameter will allow for the display
182 -- of the appropriate rules without the need to wait for the evaluation.
183 DECLARE
184 v_rule_text igs_ru_named_rule.rule_text%TYPE;
185 v_message_text igs_ru_named_rule.rule_text%TYPE;
186 v_rule_status VARCHAR2 (50);
187 v_ssp_sequence_number igs_pr_s_scratch_pad.sequence_number%TYPE;
188 v_course_cd igs_en_stdnt_ps_att.course_cd%TYPE;
189 v_version_number igs_en_stdnt_ps_att.version_number%TYPE;
190 CURSOR c_cvr IS
191 SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
192 FROM igs_ps_ver_ru
193 WHERE course_cd = v_course_cd
194 AND version_number = v_version_number
195 AND s_rule_call_cd = p_s_rule_call_cd;
196 CURSOR c_csr IS
197 SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
198 FROM igs_ps_stage_ru
199 WHERE course_cd = v_course_cd
200 AND version_number = v_version_number
201 AND cst_sequence_number = p_cst_sequence_number
202 AND s_rule_call_cd = p_s_rule_call_cd;
203 CURSOR c_usr IS
204 SELECT igs_ru_gen_003.rulp_get_rule (rul_sequence_number)
205 FROM igs_en_unit_set_rule
206 WHERE unit_set_cd = p_unit_set_cd
207 AND version_number = p_us_version_number
208 AND s_rule_call_cd = p_s_rule_call_cd;
209 BEGIN
210 -- Set the default message name
211 p_message_name := NULL;
212 --
213 -- kdande; 31-Dec-2003; Bug# 3213317;
214 -- Removed the TRUNC for the SYSDATE; Removed the call to delete stmt
215 --
216 p_log_dt := SYSDATE;
217 v_course_cd := p_course_cd;
218 v_version_number := p_version_number;
219 IF p_course_cd IS NULL THEN
220 v_course_cd := p_sca_course_cd;
221 END IF;
222 IF p_version_number IS NULL THEN
223 v_version_number := p_sca_version_number;
224 END IF;
225 v_rule_status := NULL;
226 v_message_text := NULL;
227 IF p_s_rule_call_cd = 'CRS-COMP' THEN
228 OPEN c_cvr;
229 FETCH c_cvr INTO v_rule_text;
230 IF c_cvr%NOTFOUND THEN
231 CLOSE c_cvr;
232 p_message_name := 'IGS_PR_NO_RU_EXT';
233 RETURN FALSE;
234 END IF;
235 CLOSE c_cvr;
236 IF p_evaluate_ind = 'Y' THEN
237 IF igs_ru_gen_005.rulp_val_sca_comp (
238 p_person_id,
239 p_sca_course_cd,
240 p_sca_version_number,
241 v_course_cd,
242 v_version_number,
243 p_predicted_ind,
244 v_message_text
245 ) THEN
246 -- Determine if the course completion or alternative exit functionality
247 -- is being called.
248 IF p_sca_course_cd = v_course_cd
249 AND p_sca_version_number = v_version_number THEN
250 -- Calling course completion
251 IF p_predicted_ind = 'N' THEN
252 v_rule_status := 'COURSE COMPLETION RULES SATISFIED|';
253 ELSE
254 v_rule_status := 'CAN COMPLETE COURSE|';
255 END IF;
256 ELSE
257 -- Calling alternative exit completion
258 IF p_predicted_ind = 'N' THEN
259 v_rule_status := 'ALTERNATIVE EXIT COMPLETION RULES SATISFIED|';
260 ELSE
261 v_rule_status := 'CAN COMPLETE ALTERNATIVE EXIT|';
262 END IF;
263 END IF;
264 ELSE
265 -- Determine if the course completion or alternative exit functionality
266 -- is being called.
267 IF p_sca_course_cd = v_course_cd
268 AND p_sca_version_number = v_version_number THEN
269 -- Calling course completion
270 IF p_predicted_ind = 'N' THEN
271 v_rule_status := 'COURSE COMPLETION RULES NOT SATISFIED|';
272 ELSE
273 v_rule_status := 'CANNOT COMPLETE COURSE|';
274 END IF;
275 ELSE
276 -- Calling alternative exit completion
277 IF p_predicted_ind = 'N' THEN
278 v_rule_status := 'ALTERNATIVE EXIT COMPLETION RULES NOT SATISFIED|';
279 ELSE
280 v_rule_status := 'CANNOT COMPLETE ALTERNATIVE EXIT|';
281 END IF;
282 END IF;
283 END IF;
284 END IF;
285 ELSIF p_s_rule_call_cd = 'STG-COMP' THEN
286 OPEN c_csr;
287 FETCH c_csr INTO v_rule_text;
288 IF c_csr%NOTFOUND THEN
289 CLOSE c_csr;
290 p_message_name := 'IGS_PR_NO_RU_EXT';
291 RETURN FALSE;
292 END IF;
293 CLOSE c_csr;
294 IF p_evaluate_ind = 'Y' THEN
295 IF igs_ru_gen_005.rulp_val_stg_comp (
296 p_person_id,
297 p_sca_course_cd,
298 p_sca_version_number,
299 v_course_cd,
300 v_version_number,
301 p_cst_sequence_number,
302 p_predicted_ind,
303 v_message_text
304 ) THEN
305 IF p_predicted_ind = 'N' THEN
306 v_rule_status := 'COURSE STAGE COMPLETION RULES SATISFIED|';
307 ELSE
308 v_rule_status := 'CAN COMPLETE STAGE|';
309 END IF;
310 ELSE
311 IF p_predicted_ind = 'N' THEN
312 v_rule_status := 'COURSE STAGE COMPLETION RULES NOT SATISFIED|';
313 ELSE
314 v_rule_status := 'CANNOT COMPLETE STAGE|';
315 END IF;
316 END IF;
317 END IF;
318 ELSIF p_s_rule_call_cd = 'US-COMP' THEN
319 OPEN c_usr;
320 FETCH c_usr INTO v_rule_text;
321 IF c_usr%NOTFOUND THEN
322 CLOSE c_usr;
323 p_message_name := 'IGS_PR_NO_RU_EXT';
324 RETURN FALSE;
325 END IF;
326 CLOSE c_usr;
327 IF p_evaluate_ind = 'Y' THEN
328 IF igs_ru_gen_005.rulp_val_susa_comp (
329 p_person_id,
330 p_sca_course_cd,
331 p_sca_version_number,
332 v_course_cd,
333 v_version_number,
334 p_unit_set_cd,
335 p_us_version_number,
336 p_predicted_ind,
337 v_message_text
338 ) THEN
339 IF p_predicted_ind = 'N' THEN
340 v_rule_status := 'UNIT SET COMPLETION RULES SATISFIED|';
341 ELSE
342 v_rule_status := 'CAN COMPLETE UNIT SET|';
343 END IF;
344 ELSE
345 IF p_predicted_ind = 'N' THEN
346 v_rule_status := 'UNIT SET COMPLETION RULES NOT SATISFIED|';
347 ELSE
348 v_rule_status := 'CANNOT COMPLETE UNIT SET|';
349 END IF;
350 END IF;
351 END IF;
352 END IF;
353 --Insert the status of the rule into the temporary table (s_scratch_pad).
354 -- Note: the status will be null if p_evaluate_ind = 'N'
355 igs_pr_gen_003.igs_pr_ins_ssp (
356 p_log_dt,
357 p_key || '|' || p_s_rule_call_cd || '|RULE_STATUS',
358 NULL,
359 v_rule_status,
360 v_ssp_sequence_number
361 );
362 --Insert the result of the evaluation of the rules.
363 --Note: the v_message_text will be null if p_evaluate_ind = 'N'
364 IF igs_pr_gen_006.igs_pr_ins_ssp_cmp_dtl (
365 v_rule_text,
366 v_message_text,
367 p_log_dt,
368 p_key || '|' || p_s_rule_call_cd || '|DETAIL',
369 p_message_name
370 ) = FALSE THEN
371 RETURN FALSE;
372 END IF;
373 RETURN TRUE;
374 EXCEPTION
375 WHEN OTHERS THEN
376 IF c_cvr%ISOPEN THEN
377 CLOSE c_cvr;
378 END IF;
379 IF c_csr%ISOPEN THEN
380 CLOSE c_csr;
381 END IF;
382 IF c_usr%ISOPEN THEN
383 CLOSE c_usr;
384 END IF;
385 RAISE;
386 END;
387 EXCEPTION
388 WHEN OTHERS THEN
389 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
390 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_CLC_STDNT_COMP');
391 igs_ge_msg_stack.ADD;
392 app_exception.raise_exception;
393 END igs_pr_clc_stdnt_comp;
394
395 FUNCTION igs_pr_get_appeal_alwd (
396 p_progression_rule_cat IN VARCHAR2,
397 p_pra_sequence_number IN NUMBER,
398 p_sequence_number IN NUMBER,
399 p_course_cd IN VARCHAR2,
400 p_version_number IN NUMBER
401 ) RETURN VARCHAR2 IS
402 gv_other_detail VARCHAR2 (255);
403 BEGIN -- IGS_PR_get_appeal_alwd
404 -- Determine whether an appeal is permitted on a nominated outcome.
405 DECLARE
406 v_override_appeal_ind igs_pr_ru_ou.override_appeal_ind%TYPE;
407 v_apply_start_dt_alias igs_pr_s_prg_conf.apply_start_dt_alias%TYPE;
408 v_apply_end_dt_alias igs_pr_s_prg_conf.apply_end_dt_alias%TYPE;
409 v_end_benefit_dt_alias igs_pr_s_prg_conf.end_benefit_dt_alias%TYPE;
410 v_end_penalty_dt_alias igs_pr_s_prg_conf.end_penalty_dt_alias%TYPE;
411 v_show_cause_cutoff_dt_alias igs_pr_s_prg_conf.show_cause_cutoff_dt_alias%TYPE;
412 v_appeal_cutoff_dt_alias igs_pr_s_prg_conf.appeal_cutoff_dt_alias%TYPE;
413 v_show_cause_ind igs_pr_s_prg_conf.show_cause_ind%TYPE;
414 v_apply_before_show_ind igs_pr_s_prg_conf.apply_before_show_ind%TYPE;
415 v_appeal_ind igs_pr_s_prg_conf.appeal_ind%TYPE;
416 v_apply_before_appeal_ind igs_pr_s_prg_conf.apply_before_appeal_ind%TYPE;
417 v_count_sus_in_time_ind igs_pr_s_prg_conf.count_sus_in_time_ind%TYPE;
418 v_count_exc_in_time_ind igs_pr_s_prg_conf.count_exc_in_time_ind%TYPE;
419 v_calculate_wam_ind igs_pr_s_prg_conf.calculate_wam_ind%TYPE;
420 v_calculate_gpa_ind igs_pr_s_prg_conf.calculate_gpa_ind%TYPE;
421 v_outcome_check_type igs_pr_s_prg_conf.outcome_check_type%TYPE;
422 CURSOR c_pro IS
423 SELECT pro.override_appeal_ind
424 FROM igs_pr_rule_out_v pro
425 WHERE pro.progression_rule_cat = p_progression_rule_cat
426 AND pro.pra_sequence_number = p_pra_sequence_number
427 AND pro.sequence_number = p_sequence_number;
428 BEGIN
429 IF p_progression_rule_cat IS NOT NULL THEN
430 OPEN c_pro;
431 FETCH c_pro INTO v_override_appeal_ind;
432 IF c_pro%FOUND THEN
433 CLOSE c_pro;
434 IF v_override_appeal_ind IS NOT NULL THEN
435 RETURN v_override_appeal_ind;
436 END IF;
437 ELSE
438 CLOSE c_pro;
439 END IF;
440 END IF;
441 igs_pr_gen_003.igs_pr_get_config_parm (
442 p_course_cd,
443 p_version_number,
444 v_apply_start_dt_alias,
445 v_apply_end_dt_alias,
446 v_end_benefit_dt_alias,
447 v_end_penalty_dt_alias,
448 v_show_cause_cutoff_dt_alias,
449 v_appeal_cutoff_dt_alias,
450 v_show_cause_ind,
451 v_apply_before_show_ind,
452 v_appeal_ind,
453 v_apply_before_appeal_ind,
454 v_count_sus_in_time_ind,
455 v_count_exc_in_time_ind,
456 v_calculate_wam_ind,
457 v_calculate_gpa_ind,
458 v_outcome_check_type
459 );
460 RETURN v_appeal_ind;
461 EXCEPTION
462 WHEN OTHERS THEN
463 IF c_pro%ISOPEN THEN
464 CLOSE c_pro;
465 END IF;
466 RAISE;
467 END;
468 EXCEPTION
469 WHEN OTHERS THEN
470 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
471 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_APPEAL_ALWD');
472 igs_ge_msg_stack.ADD;
473 app_exception.raise_exception;
474 END igs_pr_get_appeal_alwd;
475
476 FUNCTION igs_pr_get_cause_alwd (
477 p_progression_rule_cat IN VARCHAR2,
478 p_pra_sequence_number IN NUMBER,
479 p_sequence_number IN NUMBER,
480 p_course_cd IN VARCHAR2,
481 p_version_number IN NUMBER
482 ) RETURN VARCHAR2 IS
483 gv_other_detail VARCHAR2 (255);
484 BEGIN -- IGS_PR_get_cause_alwd
485 -- Determine whether a show cause is permitted on a nominated outcome.
486 DECLARE
487 v_override_show_cause_ind igs_pr_ru_ou.override_show_cause_ind%TYPE;
488 v_apply_start_dt_alias igs_pr_s_prg_conf.apply_start_dt_alias%TYPE;
489 v_apply_end_dt_alias igs_pr_s_prg_conf.apply_end_dt_alias%TYPE;
490 v_end_benefit_dt_alias igs_pr_s_prg_conf.end_benefit_dt_alias%TYPE;
491 v_end_penalty_dt_alias igs_pr_s_prg_conf.end_penalty_dt_alias%TYPE;
492 v_show_cause_cutoff_dt_alias igs_pr_s_prg_conf.show_cause_cutoff_dt_alias%TYPE;
493 v_appeal_cutoff_dt_alias igs_pr_s_prg_conf.appeal_cutoff_dt_alias%TYPE;
494 v_show_cause_ind igs_pr_s_prg_conf.show_cause_ind%TYPE;
495 v_apply_before_show_ind igs_pr_s_prg_conf.apply_before_show_ind%TYPE;
496 v_appeal_ind igs_pr_s_prg_conf.appeal_ind%TYPE;
497 v_apply_before_appeal_ind igs_pr_s_prg_conf.apply_before_appeal_ind%TYPE;
498 v_count_sus_in_time_ind igs_pr_s_prg_conf.count_sus_in_time_ind%TYPE;
499 v_count_exc_in_time_ind igs_pr_s_prg_conf.count_exc_in_time_ind%TYPE;
500 v_calculate_wam_ind igs_pr_s_prg_conf.calculate_wam_ind%TYPE;
501 v_calculate_gpa_ind igs_pr_s_prg_conf.calculate_gpa_ind%TYPE;
502 v_outcome_check_type igs_pr_s_prg_conf.outcome_check_type%TYPE;
503 CURSOR c_pro IS
504 SELECT pro.override_show_cause_ind
505 FROM igs_pr_rule_out_v pro
506 WHERE pro.progression_rule_cat = p_progression_rule_cat
507 AND pro.pra_sequence_number = p_pra_sequence_number
508 AND pro.sequence_number = p_sequence_number;
509 BEGIN
510 IF p_progression_rule_cat IS NOT NULL THEN
511 OPEN c_pro;
512 FETCH c_pro INTO v_override_show_cause_ind;
513 IF c_pro%FOUND THEN
514 CLOSE c_pro;
515 IF v_override_show_cause_ind IS NOT NULL THEN
516 RETURN v_override_show_cause_ind;
517 END IF;
518 ELSE
519 CLOSE c_pro;
520 END IF;
521 END IF;
522 igs_pr_gen_003.igs_pr_get_config_parm (
523 p_course_cd,
524 p_version_number,
525 v_apply_start_dt_alias,
526 v_apply_end_dt_alias,
527 v_end_benefit_dt_alias,
528 v_end_penalty_dt_alias,
529 v_show_cause_cutoff_dt_alias,
530 v_appeal_cutoff_dt_alias,
531 v_show_cause_ind,
532 v_apply_before_show_ind,
533 v_appeal_ind,
534 v_apply_before_appeal_ind,
535 v_count_sus_in_time_ind,
536 v_count_exc_in_time_ind,
537 v_calculate_wam_ind,
538 v_calculate_gpa_ind,
539 v_outcome_check_type
540 );
541 RETURN v_show_cause_ind;
542 EXCEPTION
543 WHEN OTHERS THEN
544 IF c_pro%ISOPEN THEN
545 CLOSE c_pro;
546 END IF;
547 RAISE;
548 END;
549 EXCEPTION
550 WHEN OTHERS THEN
551 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
552 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_CAUSE_ALWD');
553 igs_ge_msg_stack.ADD;
554 app_exception.raise_exception;
555 END igs_pr_get_cause_alwd;
556
557 FUNCTION igs_pr_get_num_fail (
558 p_person_id IN NUMBER,
559 p_course_cd IN VARCHAR2,
560 p_version_number IN NUMBER,
561 p_progression_rule_cat IN VARCHAR2,
562 p_pra_sequence_number IN NUMBER,
563 p_prg_cal_type IN VARCHAR2,
564 p_prg_sequence_number IN NUMBER,
565 p_prg_rule_repeat_fail_type IN VARCHAR2
566 ) RETURN NUMBER IS
567 gv_other_detail VARCHAR2 (255);
568 BEGIN -- IGS_PR_get_num_fail
569 -- Get the number of failures of a nominated rule application by a student ;
570 -- handles both repeat failures and consecutive-repeat failures.
571 -- Note: This routine assumes that the latest failure of the rule has been
572 -- stored on the database ; it is expecting to find the failure when
573 -- calculating the number of fails.
574 DECLARE
575 cst_consecrpt CONSTANT VARCHAR2 (10) := 'CONSECRPT';
576 v_number_of_failures NUMBER DEFAULT 0;
577 v_passed_ind igs_pr_sdt_pr_ru_ck.passed_ind%TYPE;
578
579 CURSOR c_spc IS
580 SELECT DISTINCT spc.prg_cal_type,
581 spc.prg_ci_sequence_number,
582 ci1.start_dt
583 FROM igs_pr_stdnt_pr_ck spc,
584 igs_ca_inst ci1
585 WHERE spc.person_id = p_person_id
586 AND spc.course_cd = p_course_cd
587 AND igs_pr_gen_001.prgp_get_cal_stream (
588 p_course_cd,
589 p_version_number,
590 p_prg_cal_type,
591 spc.prg_cal_type
592 ) = 'Y'
593 AND ci1.cal_type = spc.prg_cal_type
594 AND ci1.sequence_number = spc.prg_ci_sequence_number
595 AND ci1.start_dt <= (SELECT ci2.start_dt
596 FROM igs_ca_inst ci2
597 WHERE ci2.cal_type = p_prg_cal_type
598 AND ci2.sequence_number = p_prg_sequence_number)
599 ORDER BY ci1.start_dt DESC;
600 CURSOR c_sprc (
601 cp_prg_cal_type igs_pr_sdt_pr_ru_ck.prg_cal_type%TYPE,
602 cp_prg_ci_sequence_number igs_pr_sdt_pr_ru_ck.prg_ci_sequence_number%TYPE
603 ) IS
604 SELECT sprc.passed_ind
605 FROM igs_pr_sdt_pr_ru_ck sprc
606 WHERE sprc.person_id = p_person_id
607 AND sprc.course_cd = p_course_cd
608 AND sprc.prg_cal_type = cp_prg_cal_type
609 AND sprc.prg_ci_sequence_number = cp_prg_ci_sequence_number
610 AND sprc.progression_rule_cat = p_progression_rule_cat
611 AND sprc.pra_sequence_number = p_pra_sequence_number
612 ORDER BY sprc.rule_check_dt DESC;
613 BEGIN
614 -- If repeat type then retrieve the number of failures from previous checks
615 -- within the same calendar stream. Only consider the latest check of the
616 -- applicable rule within each progression calendar.
617 FOR v_spc_rec IN c_spc LOOP
618 OPEN c_sprc (v_spc_rec.prg_cal_type, v_spc_rec.prg_ci_sequence_number);
619 FETCH c_sprc INTO v_passed_ind;
620 IF c_sprc%FOUND THEN
621 CLOSE c_sprc;
622 IF v_passed_ind = 'N' THEN
623 v_number_of_failures := v_number_of_failures + 1;
624 ELSIF p_prg_rule_repeat_fail_type = cst_consecrpt THEN
625 -- Once a gap is found the consecutive period ends
626 EXIT;
627 END IF;
628 ELSE
629 CLOSE c_sprc;
630 END IF;
631 END LOOP;
632 RETURN v_number_of_failures;
633 EXCEPTION
634 WHEN OTHERS THEN
635 IF c_spc%ISOPEN THEN
636 CLOSE c_spc;
637 END IF;
638 IF c_sprc%ISOPEN THEN
639 CLOSE c_sprc;
640 END IF;
641 RAISE;
642 END;
643 EXCEPTION
644 WHEN OTHERS THEN
645 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
646 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_NUM_FAIL');
647 igs_ge_msg_stack.ADD;
648 app_exception.raise_exception;
649 END igs_pr_get_num_fail;
650
651 FUNCTION igs_pr_get_prg_dai (
652 p_course_cd IN VARCHAR2,
653 p_version_number IN NUMBER,
654 p_prg_cal_type IN VARCHAR2,
655 p_prg_sequence_number IN NUMBER,
656 p_alias_type IN VARCHAR2
657 ) RETURN DATE IS
658 gv_other_detail VARCHAR2 (255);
659 BEGIN -- IGS_PR_get_prg_dai
660 -- get the appropriate date alias value applicable to a nominated course
661 -- version / calendar instance
662 -- routine refers to the progression configuration structure to get the date
663 -- alias to use and then queries for it
664 DECLARE
665 cst_sa CONSTANT VARCHAR2 (2) := 'SA';
666 cst_ea CONSTANT VARCHAR2 (2) := 'EA';
667 cst_sc CONSTANT VARCHAR2 (2) := 'SC';
668 cst_ap CONSTANT VARCHAR2 (2) := 'AP';
669 cst_eb CONSTANT VARCHAR2 (2) := 'EB';
670 cst_ep CONSTANT VARCHAR2 (2) := 'EP';
671 v_dt_alias VARCHAR2 (10);
672 v_alias_val DATE;
673 v_apply_start_dt_alias igs_pr_s_prg_conf.apply_start_dt_alias%TYPE;
674 v_apply_end_dt_alias igs_pr_s_prg_conf.apply_end_dt_alias%TYPE;
675 v_end_benefit_dt_alias igs_pr_s_prg_conf.end_benefit_dt_alias%TYPE;
676 v_end_penalty_dt_alias igs_pr_s_prg_conf.end_penalty_dt_alias%TYPE;
677 v_show_cause_cutoff_dt_alias igs_pr_s_prg_conf.show_cause_cutoff_dt_alias%TYPE;
678 v_appeal_cutoff_dt_alias igs_pr_s_prg_conf.appeal_cutoff_dt_alias%TYPE;
679 v_show_cause_ind igs_pr_s_prg_conf.show_cause_ind%TYPE;
680 v_apply_before_show_ind igs_pr_s_prg_conf.apply_before_show_ind%TYPE;
681 v_appeal_ind igs_pr_s_prg_conf.appeal_ind%TYPE;
682 v_apply_before_appeal_ind igs_pr_s_prg_conf.apply_before_appeal_ind%TYPE;
683 v_count_sus_in_time_ind igs_pr_s_prg_conf.count_sus_in_time_ind%TYPE;
684 v_count_exc_in_time_ind igs_pr_s_prg_conf.count_exc_in_time_ind%TYPE;
685 v_calculate_wam_ind igs_pr_s_prg_conf.calculate_wam_ind%TYPE;
686 v_calculate_gpa_ind igs_pr_s_prg_conf.calculate_gpa_ind%TYPE;
687 v_outcome_check_type igs_pr_s_prg_conf.outcome_check_type%TYPE;
688 CURSOR c_dai (cp_dt_alias VARCHAR2) IS
689 SELECT igs_ca_gen_001.calp_get_alias_val (
690 dai.dt_alias,
691 dai.sequence_number,
692 dai.cal_type,
693 dai.ci_sequence_number
694 )
695 FROM igs_ca_da_inst dai
696 WHERE dai.cal_type = p_prg_cal_type
697 AND dai.ci_sequence_number = p_prg_sequence_number
698 AND dai.dt_alias = cp_dt_alias
699 ORDER BY 1;
700 BEGIN
701 igs_pr_gen_003.igs_pr_get_config_parm (
702 p_course_cd,
703 p_version_number,
704 v_apply_start_dt_alias,
705 v_apply_end_dt_alias,
706 v_end_benefit_dt_alias,
707 v_end_penalty_dt_alias,
708 v_show_cause_cutoff_dt_alias,
709 v_appeal_cutoff_dt_alias,
710 v_show_cause_ind,
711 v_apply_before_show_ind,
712 v_appeal_ind,
713 v_apply_before_appeal_ind,
714 v_count_sus_in_time_ind,
715 v_count_exc_in_time_ind,
716 v_calculate_wam_ind,
717 v_calculate_gpa_ind,
718 v_outcome_check_type
719 );
720 IF p_alias_type = cst_sa THEN
721 v_dt_alias := v_apply_start_dt_alias;
722 ELSIF p_alias_type = cst_ea THEN
723 v_dt_alias := v_apply_end_dt_alias;
724 ELSIF p_alias_type = cst_sc THEN
725 v_dt_alias := v_show_cause_cutoff_dt_alias;
726 ELSIF p_alias_type = cst_ap THEN
727 v_dt_alias := v_appeal_cutoff_dt_alias;
728 ELSIF p_alias_type = cst_eb THEN
729 v_dt_alias := v_end_benefit_dt_alias;
730 ELSIF p_alias_type = cst_ep THEN
731 v_dt_alias := v_end_penalty_dt_alias;
732 END IF;
733 OPEN c_dai (v_dt_alias);
734 FETCH c_dai INTO v_alias_val;
735 IF c_dai%NOTFOUND THEN
736 CLOSE c_dai;
737 RETURN NULL;
738 ELSE
739 CLOSE c_dai;
740 RETURN v_alias_val;
741 END IF;
742 EXCEPTION
743 WHEN OTHERS THEN
744 IF c_dai%ISOPEN THEN
745 CLOSE c_dai;
746 END IF;
747 RAISE;
748 END;
749 EXCEPTION
750 WHEN OTHERS THEN
751 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
752 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_DAI');
753 igs_ge_msg_stack.ADD;
754 app_exception.raise_exception;
755 END igs_pr_get_prg_dai;
756
757 FUNCTION igs_pr_get_prg_pen_end (
758 p_prg_cal_type IN VARCHAR2,
759 p_prg_sequence_number IN NUMBER
760 ) RETURN DATE IS
761 gv_other_detail VARCHAR2 (255);
762 BEGIN -- IGS_PR_get_prg_pen_end
763 -- Get the encumbrance end date of the nominated progression period.
764 -- This is retrieved from the IGS_CA_DA_INST table matching the value
765 -- stored in the progression configuration table. If no date alias is found,
766 -- then the end date of the progression period is returned.
767 DECLARE
768 v_alias_val DATE;
769 v_end_dt DATE;
770 CURSOR c_dai_spc IS
771 SELECT NVL (
772 dai.absolute_val,
773 igs_ca_gen_001.calp_get_alias_val (
774 dai.dt_alias,
775 dai.sequence_number,
776 dai.cal_type,
777 dai.ci_sequence_number
778 )
779 )
780 FROM igs_ca_da_inst dai,
781 igs_pr_s_prg_conf spc
782 WHERE dai.cal_type = p_prg_cal_type
783 AND dai.ci_sequence_number = p_prg_sequence_number
784 AND dai.dt_alias = spc.encumb_end_dt_alias
785 ORDER BY 1 DESC;
786 CURSOR c_ci IS
787 SELECT ci.end_dt
788 FROM igs_ca_inst ci
789 WHERE ci.cal_type = p_prg_cal_type
790 AND ci.sequence_number = p_prg_sequence_number;
791 BEGIN
792 OPEN c_dai_spc;
793 FETCH c_dai_spc INTO v_alias_val;
794 IF c_dai_spc%NOTFOUND THEN
795 CLOSE c_dai_spc;
796 OPEN c_ci;
797 FETCH c_ci INTO v_end_dt;
798 CLOSE c_ci;
799 RETURN v_end_dt;
800 ELSE
801 CLOSE c_dai_spc;
802 RETURN v_alias_val;
803 END IF;
804 EXCEPTION
805 WHEN OTHERS THEN
806 IF c_dai_spc%ISOPEN THEN
807 CLOSE c_dai_spc;
808 END IF;
809 IF c_ci%ISOPEN THEN
810 CLOSE c_ci;
811 END IF;
812 RAISE;
813 END;
814 EXCEPTION
815 WHEN OTHERS THEN
816 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
817 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_PEN_END');
818 igs_ge_msg_stack.ADD;
819 app_exception.raise_exception;
820 END igs_pr_get_prg_pen_end;
821
822 FUNCTION igs_pr_get_prg_status (
823 p_person_id IN NUMBER,
824 p_course_cd IN VARCHAR2,
825 p_version_number IN NUMBER,
826 p_prg_cal_type IN VARCHAR2,
827 p_prg_ci_sequence_number IN NUMBER
828 )
829 RETURN VARCHAR2 IS
830 gv_other_detail VARCHAR2 (255);
831 BEGIN
832 -- Derive the progression status for a nominated student course attempt,
833 -- being one of :
834 -- UNDCONSID Under Consideration ; Outcomes are currently pending awaiting
835 -- approval / waiving.
836 -- SHOWCAUSE Show Cause ; Student is still within the applicable show cause
837 -- period, or has shown cause an no outcome has yet been entered.
838 -- PROBATION Probation ; Student is currently has a probation outcome
839 -- applicable.
840 -- SUSPENSION Suspension ; Student is currently suspended as the result of a
841 -- progression breach.
842 -- EXCLUSION Exclusion ; Student is currently excluded as the result of a
843 -- progression breach.
844 -- EXPULSION Expulsion ; Student has been expelled as the result of a
845 -- progression breach.
846 DECLARE
847 cst_approved CONSTANT VARCHAR2 (10) := 'APPROVED';
848 cst_pending CONSTANT VARCHAR2 (10) := 'PENDING';
849 cst_showcause CONSTANT VARCHAR2 (10) := 'SHOWCAUSE';
850 cst_expulsion CONSTANT VARCHAR2 (10) := 'EXPULSION';
851 cst_exclusion CONSTANT VARCHAR2 (10) := 'EXCLUSION';
852 cst_suspension CONSTANT VARCHAR2 (10) := 'SUSPENSION';
853 cst_probation CONSTANT VARCHAR2 (10) := 'PROBATION';
854 cst_undconsid CONSTANT VARCHAR2 (10) := 'UNDCONSID';
855 cst_goodstand CONSTANT VARCHAR2 (10) := 'GOODSTAND';
856 v_show_cause BOOLEAN DEFAULT FALSE;
857 v_expulsion BOOLEAN DEFAULT FALSE;
858 v_exclusion BOOLEAN DEFAULT FALSE;
859 v_suspension BOOLEAN DEFAULT FALSE;
860 v_probation BOOLEAN DEFAULT FALSE;
861 v_pending BOOLEAN DEFAULT FALSE;
862 v_latest_cal_type igs_ca_inst.cal_type%TYPE;
863 v_latest_sequence_number igs_ca_inst.sequence_number%TYPE;
864 CURSOR c_spo_ci IS
865 SELECT spo.prg_cal_type,
866 spo.prg_ci_sequence_number
867 FROM igs_pr_stdnt_pr_ou spo,
868 igs_ca_inst ci,
869 igs_pr_ou_type pot
870 WHERE spo.person_id = p_person_id
871 AND spo.course_cd = p_course_cd
872 AND spo.decision_status IN (cst_approved, cst_pending)
873 AND ci.cal_type = spo.prg_cal_type
874 AND ci.sequence_number = spo.prg_ci_sequence_number
875 AND ((p_prg_cal_type IS NOT NULL
876 AND p_prg_ci_sequence_number IS NOT NULL
877 AND p_prg_cal_type = spo.prg_cal_type
878 AND p_prg_ci_sequence_number = spo.prg_ci_sequence_number
879 )
880 OR (p_prg_cal_type IS NULL
881 OR p_prg_ci_sequence_number IS NULL
882 )
883 )
884 AND spo.progression_outcome_type = pot.progression_outcome_type
885 AND pot.positive_outcome_ind = 'N'
886 ORDER BY ci.start_dt DESC;
887 CURSOR c_spo (
888 cp_latest_cal_type igs_ca_inst.cal_type%TYPE,
889 cp_latest_sequence_number igs_ca_inst.sequence_number%TYPE
890 ) IS
891 SELECT spo.course_cd,
892 spo.sequence_number,
893 spo.progression_outcome_type,
894 spo.decision_status,
895 spo.show_cause_expiry_dt,
896 spo.show_cause_dt,
897 spo.show_cause_outcome_dt,
898 spo.encmb_course_group_cd
899 FROM igs_pr_stdnt_pr_ou spo
900 WHERE spo.person_id = p_person_id
901 AND spo.course_cd = p_course_cd
902 AND spo.decision_status IN (cst_approved, cst_pending)
903 AND spo.prg_cal_type = cp_latest_cal_type
904 AND spo.prg_ci_sequence_number = cp_latest_sequence_number
905 AND igs_pr_gen_006.igs_pr_get_spo_expiry (
906 spo.person_id,
907 spo.course_cd,
908 spo.sequence_number,
909 spo.expiry_dt) <> 'EXPIRED';
910 FUNCTION prgpl_course_match (
911 pl_spo_course_cd igs_pr_stdnt_pr_ou.course_cd%TYPE,
912 pl_spo_sequence_number igs_pr_stdnt_pr_ou.sequence_number%TYPE
913 ) RETURN BOOLEAN IS
914 gvl_other_detail VARCHAR2 (255);
915 BEGIN -- prgpl_course_match
916 DECLARE
917 v_dummy VARCHAR2 (1);
918 CURSOR c_spc IS
919 SELECT 'X'
920 FROM igs_pr_stdnt_pr_ps spc
921 WHERE spc.person_id = p_person_id
922 AND spc.spo_course_cd = pl_spo_course_cd
923 AND spc.spo_sequence_number = pl_spo_sequence_number
924 AND spc.course_cd = p_course_cd;
925 CURSOR c_person (cp_party_id NUMBER) IS
926 SELECT party_number
927 FROM hz_parties
928 WHERE party_id = cp_party_id;
929 lv_person_number hz_parties.party_number%TYPE;
930 BEGIN
931 OPEN c_spc;
932 FETCH c_spc INTO v_dummy;
933 IF c_spc%FOUND THEN
934 CLOSE c_spc;
935 RETURN TRUE;
936 ELSIF c_spc%NOTFOUND THEN
937 CLOSE c_spc;
938 OPEN c_person (p_person_id);
939 FETCH c_person INTO lv_person_number;
940 CLOSE c_person;
941 fnd_file.put_line (
942 fnd_file.LOG,
943 'There is no Excluded Courses given for the Person := '
944 || lv_person_number
945 || 'Course code := '
946 || p_course_cd
947 || 'Skipping the record .. '
948 );
949 RETURN FALSE;
950 END IF;
951 EXCEPTION
952 WHEN OTHERS THEN
953 IF c_spc%ISOPEN THEN
954 CLOSE c_spc;
955 END IF;
956 RAISE;
957 END;
958 EXCEPTION
959 WHEN OTHERS THEN
960 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
961 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_STATUS.PRGPL_COURSE_MATCH');
962 igs_ge_msg_stack.ADD;
963 app_exception.raise_exception;
964 END prgpl_course_match;
965
966 FUNCTION prgpl_course_group_match (
967 pl_encmb_course_group_cd igs_pr_stdnt_pr_ou.encmb_course_group_cd%TYPE
968 ) RETURN BOOLEAN IS
969 gvl_other_detail VARCHAR2 (255);
970 BEGIN -- prgpl_course_group_match
971 DECLARE
972 v_dummy VARCHAR2 (1);
973 CURSOR c_cgm IS
974 SELECT 'X'
975 FROM igs_ps_grp_mbr cgm
976 WHERE cgm.course_cd = p_course_cd
977 AND cgm.version_number = p_version_number
978 AND course_group_cd = pl_encmb_course_group_cd;
979 CURSOR c_person (cp_party_id NUMBER) IS
980 SELECT party_number
981 FROM hz_parties
982 WHERE party_id = cp_party_id;
983 lv_person_number hz_parties.party_number%TYPE;
984 BEGIN
985 IF pl_encmb_course_group_cd IS NOT NULL THEN
986 OPEN c_cgm;
987 FETCH c_cgm INTO v_dummy;
988 IF c_cgm%FOUND THEN
989 CLOSE c_cgm;
990 RETURN TRUE;
991 ELSIF c_cgm%NOTFOUND THEN
992 CLOSE c_cgm;
993 OPEN c_person (p_person_id);
994 FETCH c_person INTO lv_person_number;
995 CLOSE c_person;
996 fnd_file.put_line (
997 fnd_file.LOG,
998 'There is no matching course group defined for Person :='
999 || lv_person_number
1000 || 'Course code := '
1001 || p_course_cd
1002 || 'Skipping the record .. '
1003 );
1004 RETURN FALSE;
1005 END IF;
1006 END IF;
1007 RETURN FALSE;
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 IF c_cgm%ISOPEN THEN
1011 CLOSE c_cgm;
1012 END IF;
1013 RAISE;
1014 END;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1018 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_STATUS.PRGPL_COURSE_GROUP_MATCH');
1019 igs_ge_msg_stack.ADD;
1020 app_exception.raise_exception;
1021 END prgpl_course_group_match;
1022
1023 PROCEDURE prgpl_determine_outcome_level (
1024 pl_spo_course_cd igs_pr_stdnt_pr_ou.course_cd%TYPE,
1025 pl_spo_sequence_number igs_pr_stdnt_pr_ou.sequence_number%TYPE,
1026 pl_progression_outcome_type igs_pr_ou_type.progression_outcome_type%TYPE,
1027 pl_encmb_course_group_cd igs_pr_stdnt_pr_ou.encmb_course_group_cd%TYPE
1028 ) IS
1029 gvl_other_detail VARCHAR2 (255);
1030 BEGIN -- prgpl_determine_outcome_level
1031 DECLARE
1032 cst_exc_course CONSTANT VARCHAR2 (10) := 'EXC_COURSE';
1033 cst_exc_crs_gp CONSTANT VARCHAR2 (10) := 'EXC_CRS_GP';
1034 cst_manual CONSTANT VARCHAR2 (10) := 'MANUAL';
1035 cst_nopenalty CONSTANT VARCHAR2 (10) := 'NOPENALTY';
1036 cst_probation CONSTANT VARCHAR2 (10) := 'PROBATION';
1037 cst_expulsion CONSTANT VARCHAR2 (10) := 'EXPULSION';
1038 v_s_prg_outcome_type igs_pr_ou_type.s_progression_outcome_type%TYPE;
1039 v_encumbrance_type igs_pr_ou_type.encumbrance_type%TYPE;
1040 v_dummy VARCHAR2 (1);
1041 CURSOR c_pot IS
1042 SELECT pot.s_progression_outcome_type,
1043 pot.encumbrance_type
1044 FROM igs_pr_ou_type pot
1045 WHERE pot.progression_outcome_type = pl_progression_outcome_type;
1046 CURSOR c_etde (cp_encumbrance_type igs_pr_ou_type.encumbrance_type%TYPE) IS
1047 SELECT 'X'
1048 FROM igs_fi_enc_dflt_eft etde
1049 WHERE encumbrance_type = cp_encumbrance_type
1050 AND s_encmb_effect_type IN (cst_exc_course, cst_exc_crs_gp);
1051 BEGIN
1052 OPEN c_pot;
1053 FETCH c_pot INTO v_s_prg_outcome_type,
1054 v_encumbrance_type;
1055 IF c_pot%FOUND THEN
1056 CLOSE c_pot;
1057 IF v_s_prg_outcome_type <> cst_nopenalty THEN
1058 IF v_s_prg_outcome_type IN (cst_probation, cst_manual) THEN
1059 v_probation := TRUE;
1060 ELSIF v_s_prg_outcome_type = cst_expulsion THEN
1061 IF prgpl_course_group_match (pl_encmb_course_group_cd)
1062 OR prgpl_course_match (pl_spo_course_cd, pl_spo_sequence_number) THEN
1063 v_expulsion := TRUE;
1064 END IF;
1065 ELSIF v_s_prg_outcome_type = cst_suspension THEN
1066 IF prgpl_course_match (pl_spo_course_cd, pl_spo_sequence_number) THEN
1067 v_suspension := TRUE;
1068 END IF;
1069 ELSIF v_s_prg_outcome_type = cst_exclusion THEN
1070 OPEN c_etde (v_encumbrance_type);
1071 FETCH c_etde INTO v_dummy;
1072 IF c_etde%FOUND THEN
1073 -- Determine if course group or course exclusion apply to
1074 -- the students course
1075 IF prgpl_course_group_match (pl_encmb_course_group_cd)
1076 OR prgpl_course_match (pl_spo_course_cd, pl_spo_sequence_number) THEN
1077 v_exclusion := TRUE;
1078 END IF;
1079 END IF;
1080 END IF;
1081 END IF;
1082 ELSE
1083 CLOSE c_pot;
1084 END IF;
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 IF c_pot%ISOPEN THEN
1088 CLOSE c_pot;
1089 END IF;
1090 IF c_etde%ISOPEN THEN
1091 CLOSE c_etde;
1092 END IF;
1093 RAISE;
1094 END;
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1098 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_STATUS.PRGPL_DETERMINE_OUTCOME_LEVEL');
1099 igs_ge_msg_stack.ADD;
1100 app_exception.raise_exception;
1101 END prgpl_determine_outcome_level;
1102 BEGIN -- IGS_PR_get_prg_status
1103 -- Determine the latest period with pending/active outcomes
1104 OPEN c_spo_ci;
1105 FETCH c_spo_ci INTO v_latest_cal_type,
1106 v_latest_sequence_number;
1107 IF c_spo_ci%NOTFOUND THEN
1108 CLOSE c_spo_ci;
1109 RETURN cst_goodstand;
1110 END IF;
1111 CLOSE c_spo_ci;
1112 FOR v_spo_rec IN c_spo (v_latest_cal_type, v_latest_sequence_number) LOOP
1113 IF v_spo_rec.decision_status = cst_pending THEN
1114 v_pending := TRUE;
1115 ELSE
1116 IF (v_spo_rec.show_cause_dt IS NOT NULL
1117 AND v_spo_rec.show_cause_outcome_dt IS NULL
1118 )
1119 OR (v_spo_rec.show_cause_expiry_dt IS NOT NULL
1120 AND v_spo_rec.show_cause_expiry_dt > TRUNC (SYSDATE)
1121 ) THEN
1122 v_show_cause := TRUE;
1123 ELSE
1124 prgpl_determine_outcome_level (
1125 v_spo_rec.course_cd,
1126 v_spo_rec.sequence_number,
1127 v_spo_rec.progression_outcome_type,
1128 v_spo_rec.encmb_course_group_cd
1129 );
1130 END IF;
1131 END IF;
1132 END LOOP;
1133 IF v_show_cause THEN
1134 RETURN cst_showcause;
1135 ELSIF v_expulsion THEN
1136 RETURN cst_expulsion;
1137 ELSIF v_exclusion THEN
1138 RETURN cst_exclusion;
1139 ELSIF v_suspension THEN
1140 RETURN cst_suspension;
1141 ELSIF v_probation THEN
1142 RETURN cst_probation;
1143 ELSIF v_pending THEN
1144 RETURN cst_undconsid;
1145 ELSE
1146 RETURN cst_goodstand;
1147 END IF;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 IF c_spo_ci%ISOPEN THEN
1151 CLOSE c_spo_ci;
1152 END IF;
1153 IF c_spo%ISOPEN THEN
1154 CLOSE c_spo;
1155 END IF;
1156 RAISE;
1157 END;
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1161 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_PRG_STATUS');
1162 igs_ge_msg_stack.ADD;
1163 app_exception.raise_exception;
1164 END igs_pr_get_prg_status;
1165
1166 FUNCTION igs_pr_get_sca_appeal (
1167 p_person_id IN NUMBER,
1168 p_course_cd IN VARCHAR2
1169 ) RETURN VARCHAR2 IS
1170 gv_other_detail VARCHAR2 (255);
1171 BEGIN -- IGS_PR_get_sca_appeal
1172 -- Get whether student course attempt currently has an appeal in progress
1173 DECLARE
1174 cst_approved CONSTANT VARCHAR2 (10) := 'APPROVED';
1175 v_dummy VARCHAR2 (1);
1176 CURSOR c_spo IS
1177 SELECT 'X'
1178 FROM igs_pr_stdnt_pr_ou spo
1179 WHERE spo.person_id = p_person_id
1180 AND spo.course_cd = p_course_cd
1181 AND spo.decision_status = cst_approved
1182 AND spo.appeal_dt IS NOT NULL
1183 AND spo.appeal_outcome_dt IS NULL;
1184 BEGIN
1185 OPEN c_spo;
1186 FETCH c_spo INTO v_dummy;
1187 IF c_spo%FOUND THEN
1188 CLOSE c_spo;
1189 RETURN 'Y';
1190 ELSE
1191 CLOSE c_spo;
1192 RETURN 'N';
1193 END IF;
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 IF c_spo%ISOPEN THEN
1197 CLOSE c_spo;
1198 END IF;
1199 RAISE;
1200 END;
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1204 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_SCA_APPEAL');
1205 igs_ge_msg_stack.ADD;
1206 app_exception.raise_exception;
1207 END igs_pr_get_sca_appeal;
1208
1209 FUNCTION igs_pr_get_sca_appl (
1210 p_person_id IN NUMBER,
1211 p_course_cd IN VARCHAR2,
1212 p_course_version_number IN NUMBER,
1213 p_course_type IN VARCHAR2,
1214 p_progression_rule_cat IN VARCHAR2,
1215 p_pra_sequence_number IN NUMBER,
1216 p_prg_cal_type IN VARCHAR2,
1217 p_prg_ci_sequence_number IN NUMBER,
1218 p_start_effective_period IN NUMBER,
1219 p_num_of_applications IN NUMBER,
1220 p_pra_s_relation_type IN VARCHAR2,
1221 p_pra_sca_person_id IN NUMBER,
1222 p_pra_sca_course_cd IN VARCHAR2,
1223 p_pra_crv_course_cd IN VARCHAR2,
1224 p_pra_crv_version_number IN NUMBER,
1225 p_pra_ou_org_unit_cd IN VARCHAR2,
1226 p_pra_ou_start_dt IN DATE,
1227 p_pra_course_type IN VARCHAR2
1228 ) RETURN VARCHAR2 IS
1229 gv_other_detail VARCHAR2 (255);
1230 BEGIN -- IGS_PR_get_sca_appl
1231 -- Get whether a nominated student course attempt rule still applies to a
1232 -- nominated student course attempt. This routine factors in the
1233 -- progression_rule_cal_type.start_effective_period, num_of_applications.
1234 -- Note: this routine assumes that the start/end periods of the
1235 -- progression_rule_cal_type have already been checked.
1236 DECLARE
1237 cst_active CONSTANT VARCHAR2 (10) := 'ACTIVE';
1238 cst_progress CONSTANT VARCHAR2 (10) := 'PROGRESS';
1239 cst_enrolled CONSTANT VARCHAR2 (10) := 'ENROLLED';
1240 cst_completed CONSTANT VARCHAR2 (10) := 'COMPLETED';
1241 cst_discontin CONSTANT VARCHAR2 (10) := 'DISCONTIN';
1242 v_count_of_records INTEGER DEFAULT 0;
1243 CURSOR c_sprc IS
1244 SELECT DISTINCT sprc.prg_cal_type,
1245 sprc.prg_ci_sequence_number
1246 FROM igs_pr_sdt_pr_ru_ck sprc,
1247 igs_ca_inst ci1
1248 WHERE sprc.person_id = p_person_id
1249 AND sprc.course_cd = p_course_cd
1250 AND sprc.progression_rule_cat = p_progression_rule_cat
1251 AND sprc.pra_sequence_number = p_pra_sequence_number
1252 AND sprc.prg_cal_type = ci1.cal_type
1253 AND sprc.prg_ci_sequence_number = ci1.sequence_number
1254 AND ci1.start_dt <= --gjha Changed to <= from <
1255 (SELECT ci2.start_dt
1256 FROM igs_ca_inst ci2
1257 WHERE ci2.cal_type = p_prg_cal_type
1258 AND ci2.sequence_number = p_prg_ci_sequence_number);
1259 CURSOR c_ci_ct_cs IS
1260 SELECT ci1.cal_type,
1261 ci1.sequence_number
1262 FROM igs_ca_inst ci1,
1263 igs_ca_type ct,
1264 igs_ca_stat cs
1265 WHERE ct.cal_type = ci1.cal_type
1266 AND ct.s_cal_cat = cst_progress
1267 AND cs.cal_status = ci1.cal_status
1268 AND cs.s_cal_status = cst_active
1269 AND ci1.start_dt <= (SELECT ci2.start_dt
1270 FROM igs_ca_inst ci2
1271 WHERE ci2.cal_type = p_prg_cal_type
1272 AND ci2.sequence_number = p_prg_ci_sequence_number)
1273 AND (-- Logic from CALP_GET_CAL_STREAM.
1274 EXISTS ( SELECT 'x'
1275 FROM igs_pr_s_prg_cal spc1,
1276 igs_pr_s_prg_cal spc2
1277 WHERE spc1.s_control_num = 1
1278 AND spc2.s_control_num = 1
1279 AND spc1.prg_cal_type = p_prg_cal_type
1280 AND spc2.prg_cal_type = ci1.cal_type
1281 AND spc1.stream_num = spc2.stream_num)
1282 OR EXISTS ( SELECT 'x'
1283 FROM igs_pr_s_ou_prg_cal sopc1,
1284 igs_pr_s_ou_prg_cal sopc2
1285 WHERE igs_pr_gen_001.prgp_get_crv_cmt (
1286 p_course_cd,
1287 p_course_version_number,
1288 sopc1.org_unit_cd,
1289 sopc1.ou_start_dt
1290 ) = 'Y'
1291 AND sopc1.prg_cal_type = p_prg_cal_type
1292 AND sopc2.org_unit_cd = sopc1.org_unit_cd
1293 AND sopc2.ou_start_dt = sopc1.ou_start_dt
1294 AND sopc2.prg_cal_type = ci1.cal_type
1295 AND sopc1.stream_num = sopc2.stream_num)
1296 OR EXISTS ( SELECT 'x'
1297 FROM igs_pr_s_crv_prg_cal scpc1,
1298 igs_pr_s_crv_prg_cal scpc2
1299 WHERE scpc1.course_cd = p_course_cd
1300 AND scpc1.version_number = p_course_version_number
1301 AND scpc1.prg_cal_type = p_prg_cal_type
1302 AND scpc2.course_cd = scpc1.course_cd
1303 AND scpc2.version_number = scpc1.version_number
1304 AND scpc2.prg_cal_type = ci1.cal_type
1305 AND scpc1.stream_num = scpc2.stream_num)
1306 )
1307 AND EXISTS ( -- Units must exist within the progression calendar.
1308 SELECT 'X'
1309 FROM igs_en_su_attempt sua,
1310 igs_ca_inst_rel cir
1311 WHERE sua.person_id = p_person_id
1312 AND sua.course_cd = p_course_cd
1313 AND cir.sup_cal_type = ci1.cal_type
1314 AND cir.sup_ci_sequence_number = ci1.sequence_number
1315 AND cir.sub_cal_type = sua.cal_type
1316 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
1317 AND sua.unit_attempt_status IN (cst_enrolled, cst_discontin, cst_completed));
1318 BEGIN
1319 -- Ensure that progression rule application matches the appropriate
1320 -- characteristics of the student being applied.
1321 IF p_pra_s_relation_type = 'SCA' THEN
1322 IF p_pra_sca_person_id <> p_person_id
1323 OR p_pra_sca_course_cd <> p_course_cd THEN
1324 RETURN 'N';
1325 END IF;
1326 ELSIF p_pra_s_relation_type = 'CRV' THEN
1327 IF p_course_cd <> p_pra_crv_course_cd
1328 OR p_course_version_number <> p_pra_crv_version_number THEN
1329 RETURN 'N';
1330 END IF;
1331 ELSIF p_pra_s_relation_type = 'OU' THEN
1332 IF igs_pr_gen_001.prgp_get_crv_cmt (
1333 p_course_cd,
1334 p_course_version_number,
1335 p_pra_ou_org_unit_cd,
1336 p_pra_ou_start_dt
1337 ) = 'N' THEN
1338 RETURN 'N';
1339 END IF;
1340 ELSIF p_pra_s_relation_type = 'CTY' THEN
1341 IF p_course_type <> p_pra_course_type THEN
1342 RETURN 'N';
1343 END IF;
1344 ELSE
1345 -- Not a relation type that applies at this level.
1346 RETURN 'N';
1347 END IF;
1348 IF p_num_of_applications IS NOT NULL THEN
1349 -- Check whether student has had the nominated number of applications
1350 FOR v_sprc_rec IN c_sprc LOOP
1351 v_count_of_records := v_count_of_records + 1;
1352 END LOOP;
1353 IF v_count_of_records >= p_num_of_applications THEN
1354 -- Already been applied the specified number of times
1355 RETURN 'N';
1356 END IF;
1357 END IF;
1358 IF p_start_effective_period IS NULL
1359 OR p_start_effective_period < 2 THEN
1360 -- Start immediately
1361 RETURN 'Y';
1362 ELSE
1363 v_count_of_records := 0;
1364 FOR v_ci_rec IN c_ci_ct_cs LOOP
1365 IF igs_pr_gen_001.prgp_get_drtn_efctv (
1366 v_ci_rec.cal_type,
1367 v_ci_rec.sequence_number,
1368 p_person_id,
1369 p_course_cd
1370 ) = 'Y' THEN
1371 v_count_of_records := v_count_of_records + 1;
1372 END IF;
1373 END LOOP;
1374 IF v_count_of_records < p_start_effective_period THEN
1375 -- Not yet enough records
1376 RETURN 'N';
1377 END IF;
1378 END IF;
1379 RETURN 'Y';
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 IF c_sprc%ISOPEN THEN
1383 CLOSE c_sprc;
1384 END IF;
1385 IF c_ci_ct_cs%ISOPEN THEN
1386 CLOSE c_ci_ct_cs;
1387 END IF;
1388 RAISE;
1389 END;
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1393 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_SCA_APPL');
1394 igs_ge_msg_stack.ADD;
1395 app_exception.raise_exception;
1396 END igs_pr_get_sca_appl;
1397
1398 FUNCTION igs_pr_get_sca_cmt (
1399 p_person_id IN NUMBER,
1400 p_course_cd IN VARCHAR2,
1401 p_version_number IN NUMBER,
1402 p_org_unit_cd IN VARCHAR2,
1403 p_ou_start_dt IN DATE
1404 ) RETURN VARCHAR2 IS
1405 gv_other_detail VARCHAR2 (255);
1406 BEGIN -- IGS_PR_get_sca_cmt
1407 -- Get whether student course attempt is covered by the nominated
1408 -- committee structure
1409 DECLARE
1410 v_ou_rel_found BOOLEAN DEFAULT FALSE;
1411 v_dummy VARCHAR2 (1);
1412 CURSOR c_crv_cow IS
1413 SELECT crv.course_type,
1414 cow.org_unit_cd,
1415 cow.ou_start_dt
1416 FROM igs_en_stdnt_ps_att sca,
1417 igs_ps_ver crv,
1418 igs_ps_own cow
1419 WHERE sca.person_id = p_person_id
1420 AND sca.course_cd = p_course_cd
1421 AND (sca.version_number = p_version_number
1422 OR p_version_number IS NULL
1423 )
1424 AND crv.course_cd = sca.course_cd
1425 AND crv.version_number = sca.version_number
1426 AND crv.course_cd = cow.course_cd
1427 AND crv.version_number = cow.version_number;
1428 CURSOR c_our (
1429 cp_cow_org_unit_cd igs_or_unit.org_unit_cd%TYPE,
1430 cp_cow_ou_start_dt igs_or_unit.start_dt%TYPE,
1431 cp_course_type igs_ps_ver.course_type%TYPE
1432 ) IS
1433 SELECT 'X'
1434 FROM igs_or_unit_rel our
1435 WHERE our.parent_org_unit_cd = p_org_unit_cd
1436 AND our.parent_start_dt = p_ou_start_dt
1437 AND our.child_org_unit_cd = cp_cow_org_unit_cd
1438 AND our.child_start_dt = cp_cow_ou_start_dt
1439 AND our.logical_delete_dt IS NULL
1440 AND EXISTS ( SELECT 'X'
1441 FROM igs_or_rel_ps_type ourct
1442 WHERE our.parent_org_unit_cd = ourct.parent_org_unit_cd
1443 AND our.parent_start_dt = ourct.parent_start_dt
1444 AND our.child_org_unit_cd = ourct.child_org_unit_cd
1445 AND our.child_start_dt = ourct.child_start_dt
1446 AND our.create_dt = ourct.our_create_dt
1447 AND ourct.course_type = cp_course_type);
1448 BEGIN
1449 FOR v_crv_cow_rec IN c_crv_cow LOOP
1450 IF v_crv_cow_rec.org_unit_cd = p_org_unit_cd
1451 AND v_crv_cow_rec.ou_start_dt = p_ou_start_dt THEN
1452 RETURN 'Y';
1453 END IF;
1454 -- Firstly search for a direct match to an organisational unit with the
1455 -- course type qualification, if doesn't THEN move onto a standard ou
1456 -- relationship test.
1457 OPEN c_our (v_crv_cow_rec.org_unit_cd, v_crv_cow_rec.ou_start_dt, v_crv_cow_rec.course_type);
1458 FETCH c_our INTO v_dummy;
1459 IF c_our%FOUND THEN
1460 CLOSE c_our;
1461 v_ou_rel_found := TRUE;
1462 EXIT;
1463 ELSE
1464 CLOSE c_our;
1465 IF igs_or_gen_001.orgp_get_within_ou (
1466 p_org_unit_cd,
1467 p_ou_start_dt,
1468 v_crv_cow_rec.org_unit_cd,
1469 v_crv_cow_rec.ou_start_dt,
1470 'N'
1471 ) = 'Y' THEN
1472 v_ou_rel_found := TRUE;
1473 EXIT;
1474 END IF;
1475 END IF;
1476 END LOOP;
1477 IF v_ou_rel_found THEN
1478 RETURN 'Y';
1479 END IF;
1480 RETURN 'N';
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 IF c_crv_cow%ISOPEN THEN
1484 CLOSE c_crv_cow;
1485 END IF;
1486 IF c_our%ISOPEN THEN
1487 CLOSE c_our;
1488 END IF;
1489 RAISE;
1490 END;
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1494 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_SCA_CMT');
1495 igs_ge_msg_stack.ADD;
1496 app_exception.raise_exception;
1497 END igs_pr_get_sca_cmt;
1498
1499 FUNCTION igs_pr_get_sca_state (
1500 p_person_id IN NUMBER,
1501 p_course_cd IN VARCHAR2,
1502 p_prg_cal_type IN VARCHAR2,
1503 p_prg_sequence_number IN NUMBER
1504 ) RETURN VARCHAR2 IS
1505 gv_other_detail VARCHAR2 (255);
1506 BEGIN -- IGS_PR_get_sca_state
1507 -- Routine to determine the state of unit attempts for a nominated
1508 -- IGS_EN_STDNT_PS_ATT that are applicable to a progression calendar.
1509 --This routine can return one of four outcomes :
1510 --NONE There are no unit attempts within the calendar
1511 --FINAL All required /entered grades are finalised
1512 --RECOMMEND There are recommended grades which will contribute to the calendar
1513 --MISSING There are required grades that are missing (ie. not recommended)
1514 DECLARE
1515 cst_enrolled CONSTANT VARCHAR2 (10) := 'ENROLLED';
1516 cst_completed CONSTANT VARCHAR2 (10) := 'COMPLETED';
1517 cst_discontin CONSTANT VARCHAR2 (10) := 'DISCONTIN';
1518 cst_none CONSTANT VARCHAR2 (10) := 'NONE';
1519 cst_missing CONSTANT VARCHAR2 (10) := 'MISSING';
1520 cst_recommend CONSTANT VARCHAR2 (10) := 'RECOMMEND';
1521 cst_final CONSTANT VARCHAR2 (10) := 'FINAL';
1522 v_finalised BOOLEAN DEFAULT FALSE;
1523 v_recommended BOOLEAN DEFAULT FALSE;
1524 v_missing BOOLEAN DEFAULT FALSE;
1525 v_not_incomplete BOOLEAN DEFAULT FALSE;
1526 v_dummy VARCHAR2 (1);
1527 v_sua_found BOOLEAN;
1528 v_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
1529 v_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE;
1530 v_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE;
1531 v_gs_version_number igs_as_su_stmptout.version_number%TYPE;
1532 v_grade igs_as_su_stmptout.grade%TYPE;
1533 v_mark igs_as_su_stmptout.mark%TYPE;
1534 v_original_course_cd igs_en_stdnt_ps_att.course_cd%TYPE;
1535 --
1536 -- kdande; 22-Apr-2003; Bug# 2829262
1537 -- Added uoo_id field to the SELECT clause of cursor c_sua
1538 --
1539 CURSOR c_sua IS
1540 SELECT sua.person_id,
1541 sua.course_cd,
1542 sua.unit_cd,
1543 sua.cal_type,
1544 sua.ci_sequence_number,
1545 sua.unit_attempt_status,
1546 sua.discontinued_dt,
1547 sua.uoo_id
1548 FROM igs_en_su_attempt sua,
1549 igs_ca_inst_rel cir
1550 WHERE sua.person_id = p_person_id
1551 AND sua.course_cd = p_course_cd
1552 AND sua.unit_attempt_status IN (cst_enrolled, cst_discontin, cst_completed)
1553 AND cir.sup_cal_type = p_prg_cal_type
1554 AND cir.sup_ci_sequence_number = p_prg_sequence_number
1555 AND cir.sub_cal_type = sua.cal_type
1556 AND cir.sub_ci_sequence_number = sua.ci_sequence_number
1557 AND (sua.administrative_unit_status IS NULL
1558 OR sua.administrative_unit_status IN (SELECT aus.administrative_unit_status
1559 FROM igs_ad_adm_unit_stat aus
1560 WHERE aus.effective_progression_ind = 'Y')
1561 )
1562 ORDER BY DECODE (sua.unit_attempt_status, cst_enrolled, 1, cst_discontin, 2, cst_completed, 3);
1563 BEGIN
1564 v_sua_found := FALSE;
1565 FOR v_sua_rec IN c_sua LOOP
1566 v_sua_found := TRUE;
1567 --
1568 -- kdande; 22-Apr-2003; Bug# 2829262
1569 -- Added uoo_id parameter to the igs_pr_gen_002.prgp_get_sua_prg_prd FUNCTION call
1570 --
1571 IF igs_pr_gen_002.prgp_get_sua_prg_prd (
1572 p_prg_cal_type,
1573 p_prg_sequence_number,
1574 p_person_id,
1575 p_course_cd,
1576 v_sua_rec.unit_cd,
1577 v_sua_rec.cal_type,
1578 v_sua_rec.ci_sequence_number,
1579 'Y',
1580 v_sua_rec.unit_attempt_status,
1581 v_sua_rec.discontinued_dt,
1582 v_sua_rec.uoo_id
1583 ) = 'Y' THEN
1584 IF v_sua_rec.unit_attempt_status <> 'DISCONTIN' THEN
1585 --
1586 -- kdande; 22-Apr-2003; Bug# 2829262
1587 -- Added uoo_id parameter to the igs_as_gen_003.assp_get_sua_outcome FUNCTION call
1588 --
1589 v_result_type := igs_as_gen_003.assp_get_sua_outcome (
1590 p_person_id,
1591 p_course_cd,
1592 v_sua_rec.unit_cd,
1593 v_sua_rec.cal_type,
1594 v_sua_rec.ci_sequence_number,
1595 v_sua_rec.unit_attempt_status,
1596 'N',
1597 v_outcome_dt,
1598 v_grading_schema_cd,
1599 v_gs_version_number,
1600 v_grade,
1601 v_mark,
1602 v_original_course_cd,
1603 v_sua_rec.uoo_id,
1604 --added by LKAKI---
1605 'N');
1606 IF v_result_type <> 'INCOMP' THEN
1607 v_not_incomplete := TRUE;
1608 END IF;
1609 ELSE
1610 v_result_type := NULL;
1611 v_not_incomplete := TRUE;
1612 END IF;
1613 IF (v_sua_rec.unit_attempt_status = cst_completed
1614 AND v_result_type <> 'INCOMP'
1615 )
1616 OR v_sua_rec.unit_attempt_status = cst_discontin THEN
1617 v_finalised := TRUE;
1618 EXIT;
1619 END IF;
1620 IF v_sua_rec.unit_attempt_status = cst_enrolled THEN
1621 IF v_result_type IS NOT NULL THEN
1622 v_recommended := TRUE;
1623 ELSE
1624 v_missing := TRUE;
1625 EXIT;
1626 END IF;
1627 END IF;
1628 END IF;
1629 END LOOP;
1630 IF v_sua_found = FALSE THEN
1631 -- sua not found
1632 RETURN cst_none;
1633 ELSE
1634 IF v_missing = TRUE THEN
1635 RETURN cst_missing;
1636 ELSIF v_not_incomplete = FALSE THEN
1637 -- No non-incomplete grade was matched.
1638 RETURN cst_missing;
1639 ELSIF v_recommended = TRUE THEN
1640 RETURN cst_recommend;
1641 ELSIF v_finalised = TRUE THEN
1642 RETURN cst_final;
1643 ELSE
1644 RETURN cst_none;
1645 END IF;
1646 END IF;
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 IF c_sua%ISOPEN THEN
1650 CLOSE c_sua;
1651 END IF;
1652 RAISE;
1653 END;
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1657 fnd_message.set_token ('NAME', 'IGS_PR_GEN_005.IGS_PR_GET_SCA_STATE');
1658 igs_ge_msg_stack.ADD;
1659 app_exception.raise_exception;
1660 END igs_pr_get_sca_state;
1661
1662 FUNCTION igs_pr_get_scpm_value (
1663 p_person_id IN NUMBER,
1664 p_course_cd IN VARCHAR2,
1665 p_prg_cal_type IN VARCHAR2,
1666 p_prg_ci_sequence_number IN NUMBER,
1667 p_s_prg_measure_type IN VARCHAR2
1668 ) RETURN NUMBER IS
1669 BEGIN -- IGS_PR_get_scpm_value
1670 -- Get the IGS_PR_SDT_PS_PR_MSR value for the student course attempt,
1671 -- progression period and system progression measure type supplied.
1672 DECLARE
1673 v_value NUMBER;
1674 CURSOR c_scpm IS
1675 SELECT scpm.VALUE
1676 FROM igs_pr_sdt_ps_pr_msr scpm
1677 WHERE scpm.person_id = p_person_id
1678 AND scpm.course_cd = p_course_cd
1679 AND scpm.prg_cal_type = p_prg_cal_type
1680 AND scpm.prg_ci_sequence_number = p_prg_ci_sequence_number
1681 AND scpm.s_prg_measure_type = p_s_prg_measure_type
1682 AND scpm.calculation_dt = (SELECT MAX (scpm2.calculation_dt)
1683 FROM igs_pr_sdt_ps_pr_msr scpm2
1684 WHERE scpm2.person_id = scpm.person_id
1685 AND scpm2.course_cd = scpm.course_cd
1686 AND scpm2.prg_cal_type = scpm.prg_cal_type
1687 AND scpm2.prg_ci_sequence_number = scpm.prg_ci_sequence_number
1688 AND scpm2.s_prg_measure_type = scpm.s_prg_measure_type);
1689 BEGIN
1690 -- Set the default expiry date
1691 IF p_person_id IS NULL
1692 OR p_course_cd IS NULL
1693 OR p_prg_cal_type IS NULL
1694 OR p_prg_ci_sequence_number IS NULL
1695 OR p_s_prg_measure_type IS NULL THEN
1696 RETURN NULL;
1697 END IF;
1698 -- Select IGS_PR_SDT_PS_PR_MSR record
1699 OPEN c_scpm;
1700 FETCH c_scpm INTO v_value;
1701 IF c_scpm%NOTFOUND THEN
1702 CLOSE c_scpm;
1703 RETURN NULL;
1704 END IF;
1705 CLOSE c_scpm;
1706 RETURN v_value;
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 IF c_scpm%ISOPEN THEN
1710 CLOSE c_scpm;
1711 END IF;
1712 RAISE;
1713 END;
1714 END igs_pr_get_scpm_value;
1715 END igs_pr_gen_005;
[Home] [Help]