1 PACKAGE BODY igs_pr_val_spo AS
2 /* $Header: IGSPR21B.pls 115.10 2003/04/14 09:51:58 anilk ship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 -- bug 1956374 msrinivi removed duplicate code genp_prc_clear_row_id
8 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_att_closed"
9 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "crsp_val_crv_active"
10 /*
11 ||=========================================================================================||
12 || kdande --Bug ID 1956374 - Removal of Duplicate Program Units from OSS. ||
13 || Removed program unit (PRGP_VAL_POT_CLOSED) - from the spec and body. ||
14 ||=========================================================================================||
15 || NALKUMAR 19-NOV-2002 Bug NO: 2658550. Modified 'prgp_val_spo_rqrd' ||
16 || and 'prgp_val_spo_approve' function as per the FA110 PR Enh. ||
17 ||=========================================================================================||
18 || NALKUMAR 13-JAN-2003 Bug NO: 2728493. Modified 'prgp_val_spo_dcsn' procedure to ||
19 || display error IGS_PR_CNTCH_DST_APRC_WAP_OAP when the ||
20 || decision status is changed to WAIVED/PENDING for an Approved ||
21 || applied outcome ||
22 ||=========================================================================================||
23 */
24 -------------------------------------------------------------------------------------------
25
26 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
27
28 FUNCTION prgp_prc_spo_rowids(
29 p_inserting IN BOOLEAN ,
30 p_updating IN BOOLEAN ,
31 p_deleting IN BOOLEAN ,
32 p_message_name OUT NOCOPY VARCHAR2 )
33 RETURN BOOLEAN IS
34 v_index BINARY_INTEGER;
35 v_other_detail VARCHAR(255);
36 v_message_name VARCHAR(30);
37 BEGIN
38 -- Process saved rows.
39 FOR v_index IN 1..gv_table_index - 1 LOOP
40 -- Validate student progression outcome
41 IF IGS_PR_GEN_006.IGS_PR_UPD_SCA_STATUS (
42 gt_rowid_table(v_index).person_id,
43 gt_rowid_table(v_index).course_cd,
44 NULL,
45 NULL,
46 v_message_name) = FALSE THEN
47 p_message_name := v_message_name;
48 RETURN FALSE;
49 END IF;
50 -- Validate insert
51 IF p_inserting THEN
52 IF igs_pr_val_spo.prgp_val_spo_ins (
53 gt_rowid_table(v_index).person_id,
54 gt_rowid_table(v_index).course_cd,
55 gt_rowid_table(v_index).prg_cal_type,
56 gt_rowid_table(v_index).prg_ci_sequence_number,
57 gt_rowid_table(v_index).rule_check_dt,
58 gt_rowid_table(v_index).progression_rule_cat,
59 gt_rowid_table(v_index).pra_sequence_number,
60 v_message_name) = FALSE THEN
61 p_message_name := v_message_name;
62 RETURN FALSE;
63 END IF;
64 END IF;
65 -- Check the decision status can be changed
66 IF p_inserting OR (p_updating AND
67 gt_rowid_table(v_index).new_decision_status <>
68 gt_rowid_table(v_index).old_decision_status) THEN
69 IF igs_pr_val_spo.prgp_val_spo_dcsn (
70 gt_rowid_table(v_index).person_id,
71 gt_rowid_table(v_index).course_cd,
72 gt_rowid_table(v_index).prg_cal_type,
73 gt_rowid_table(v_index).prg_ci_sequence_number,
74 gt_rowid_table(v_index).rule_check_dt,
75 gt_rowid_table(v_index).progression_rule_cat,
76 gt_rowid_table(v_index).pra_sequence_number,
77 gt_rowid_table(v_index).progression_outcome_type,
78 gt_rowid_table(v_index).old_decision_status,
79 gt_rowid_table(v_index).new_decision_status,
80 gt_rowid_table(v_index).decision_dt,
81 gt_rowid_table(v_index).decision_org_unit_cd,
82 gt_rowid_table(v_index).decision_ou_start_dt,
83 gt_rowid_table(v_index).applied_dt,
84 gt_rowid_table(v_index).expiry_dt,
85 v_message_name) = FALSE THEN
86 p_message_name := v_message_name;
87 RETURN FALSE;
88 END IF;
89 END IF;
90 -- Check the decision status can be set to approved
91 IF p_inserting OR (p_updating AND
92 gt_rowid_table(v_index).new_decision_status <>
93 gt_rowid_table(v_index).old_decision_status) THEN
94 IF igs_pr_val_spo.prgp_val_spo_approve (
95 gt_rowid_table(v_index).person_id,
96 gt_rowid_table(v_index).course_cd,
97 gt_rowid_table(v_index).sequence_number,
98 gt_rowid_table(v_index).progression_outcome_type,
99 gt_rowid_table(v_index).old_decision_status,
100 gt_rowid_table(v_index).new_decision_status,
101 gt_rowid_table(v_index).encmb_course_group_cd,
102 gt_rowid_table(v_index).restricted_enrolment_cp,
103 gt_rowid_table(v_index).restricted_attendance_type,
104 v_message_name) = FALSE THEN
105 p_message_name := v_message_name;
106 RETURN FALSE;
107 END IF;
108 END IF;
109 -- Check the duration/duration type can be changed
110 IF p_inserting OR (p_updating AND
111 NVL(gt_rowid_table(v_index).new_duration, -1) <>
112 NVL(gt_rowid_table(v_index).old_duration, -1) OR
113 NVL(gt_rowid_table(v_index).new_duration_type, ' ') <>
114 NVL(gt_rowid_table(v_index).old_duration_type, ' ')) THEN
115 IF igs_pr_val_spo.prgp_val_spo_drtn (
116 gt_rowid_table(v_index).person_id,
117 gt_rowid_table(v_index).course_cd,
118 gt_rowid_table(v_index).sequence_number,
119 gt_rowid_table(v_index).new_decision_status,
120 gt_rowid_table(v_index).old_duration,
121 gt_rowid_table(v_index).new_duration,
122 gt_rowid_table(v_index).old_duration_type,
123 gt_rowid_table(v_index).new_duration_type,
124 gt_rowid_table(v_index).expiry_dt,
125 v_message_name) = FALSE THEN
126 p_message_name := v_message_name;
127 RETURN FALSE;
128 END IF;
129 END IF;
130 END LOOP;
131 RETURN TRUE;
132 END prgp_prc_spo_rowids;
133
134 --
135 -- Validate student progression outcome decision status changes
136 FUNCTION prgp_val_spo_dcsn(
137 p_person_id IN NUMBER ,
138 p_course_cd IN VARCHAR2 ,
139 p_prg_cal_type IN VARCHAR2 ,
140 p_prg_ci_sequence_number IN NUMBER ,
141 p_rule_check_dt IN DATE ,
142 p_progression_rule_cat IN VARCHAR2 ,
143 p_pra_sequence_number IN NUMBER ,
144 p_progression_outcome_type IN VARCHAR2 ,
145 p_old_decision_status IN VARCHAR2 ,
146 p_new_decision_status IN VARCHAR2 ,
147 p_decision_dt IN DATE ,
148 p_decision_org_unit_cd IN VARCHAR2 ,
149 p_decision_ou_start_dt IN DATE ,
150 p_applied_dt IN DATE ,
151 p_expiry_dt IN DATE ,
152 p_message_name OUT NOCOPY VARCHAR2 )
153 RETURN BOOLEAN IS
154 gv_other_detail VARCHAR2(255);
155 BEGIN -- prgp_val_spo_dcsn
156 -- Validate changes to the student _progression_outcome decision_status:
157 -- PENDING => APPROVED
158 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt must be set
159 -- spo records for future test of the same rule cannot exist
160 -- PENDING => WAIVED
161 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt must be set
162 -- PENDING => <OTHER>
163 -- not allowed
164 -- APPROVED => WAIVED
165 -- applied_dt cannot be set
166 -- APPROVED => REMOVED
167 -- if expiry_dt is set it must be < SYSDATE
168 -- APPROVED => CANCELLED
169 -- if expiry_dt is set it must be < SYSDATE
170 -- applied_dt must be set
171 -- APPROVED => PENDING
172 -- applied_dt cannot be set
173 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt cannot be set
174 -- WAIVED => PENDING
175 -- spo records for future test of the same rule cannot exist
176 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt cannot be set
177 -- WAIVED => APPROVED
178 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt must be set
179 -- spo records for future test of the same rule cannot exist
180 -- WAIVED => <OTHER>
181 -- not allowed
182 -- CANCELLED => APPROVED
183 -- decision_dt, decision_org_unit_cd, decision_ou_start_dt must be set
184 -- CANCELLED => <OTHER>
185 -- not allowed
186 -- REMOVED => <OTHER>
187 -- not allowed
188 DECLARE
189 cst_pending CONSTANT VARCHAR(10) := 'PENDING';
190 cst_approved CONSTANT VARCHAR(10) := 'APPROVED';
191 cst_waived CONSTANT VARCHAR(10) := 'WAIVED';
192 cst_cancelled CONSTANT VARCHAR(10) := 'CANCELLED';
193 cst_removed CONSTANT VARCHAR(10) := 'REMOVED';
194 v_dummy VARCHAR2(1);
195 CURSOR c_spo IS
196 SELECT 'X'
197 FROM IGS_PR_STDNT_PR_OU spo
198 WHERE spo.person_id = p_person_id AND
199 spo.course_cd = p_course_cd AND
200 spo.prg_cal_type = p_prg_cal_type AND
201 spo.prg_ci_sequence_number = p_prg_ci_sequence_number AND
202 spo.rule_check_dt IS NOT NULL AND
203 spo.rule_check_dt > p_rule_check_dt AND
204 spo.progression_rule_cat = p_progression_rule_cat AND
205 spo.pra_sequence_number = p_pra_sequence_number;
206 BEGIN
207 p_message_name := null;
208 IF p_person_id IS NULL OR
209 p_course_cd IS NULL OR
210 p_prg_cal_type IS NULL OR
211 p_prg_ci_sequence_number IS NULL OR
212 p_new_decision_status IS NULL THEN
213 RETURN TRUE;
214 END IF;
215 IF p_old_decision_status = cst_pending AND
216 p_new_decision_status NOT IN ( cst_pending,
217 cst_approved,
218 cst_waived) THEN
219 p_message_name := 'IGS_PR_CAOCH_DST_PEN_RE_APWA';
220 RETURN FALSE;
221 END IF;
222 IF p_old_decision_status = cst_waived AND
223 p_new_decision_status NOT IN ( cst_waived,
224 cst_approved,
225 cst_pending) THEN
226 p_message_name := 'IGS_PR_CACH_DEST_WAD_RE_APPE';
227 RETURN FALSE;
228 END IF;
229 IF p_old_decision_status = cst_cancelled AND
230 p_new_decision_status NOT IN ( cst_cancelled,
231 cst_approved) THEN
232 p_message_name := 'IGS_PR_CACH_DEST_CAN_RE_AP';
233 RETURN FALSE;
234 END IF;
235 IF p_old_decision_status = cst_removed AND
236 p_new_decision_status <> cst_removed THEN
237 p_message_name := 'IGS_PR_CANT_CHDE_RED_REC';
238 RETURN FALSE;
239 END IF;
240 IF p_new_decision_status = cst_pending THEN
241 IF p_decision_dt IS NOT NULL OR
242 p_decision_org_unit_cd IS NOT NULL OR
243 p_decision_ou_start_dt IS NOT NULL THEN
244 p_message_name := 'IGS_PR_DEDT_DORG_CNT_DEST_PEN';
245 RETURN FALSE;
246 END IF;
247 END IF;
248 IF p_new_decision_status IN ( cst_approved,
249 cst_waived) THEN
250 IF p_decision_dt IS NULL OR
251 p_decision_org_unit_cd IS NULL OR
252 p_decision_ou_start_dt IS NULL THEN
253 p_message_name := 'IGS_PR_DEDT_DEOR_CNT_DEST_PEN';
254 RETURN FALSE;
255 END IF;
256 END IF;
257 IF p_new_decision_status IN ( cst_approved,
258 cst_pending) AND
259 p_old_decision_status NOT IN ( cst_approved,
260 cst_pending) THEN
261 OPEN c_spo;
262 FETCH c_spo INTO v_dummy;
263 IF c_spo%FOUND THEN
264 CLOSE c_spo;
265 p_message_name := 'IGS_PR_CNCH_DEST_APPEN_OTRE_RUCK';
266 RETURN FALSE;
267 END IF;
268 CLOSE c_spo;
269 END IF;
270 IF p_old_decision_status = cst_approved AND
271 p_new_decision_status = cst_cancelled AND
272 p_applied_dt IS NULL THEN
273 p_message_name := 'IGS_PR_CNT_CHDES_CA_OUHNT_AP';
274 RETURN FALSE;
275 END IF;
276 IF p_old_decision_status = cst_approved AND
277 p_new_decision_status IN ( cst_waived,
278 cst_pending) AND
279 p_applied_dt IS NOT NULL THEN
280 p_message_name := 'IGS_PR_CNTCH_DST_APRC_WAP_OAP';
281 RETURN FALSE;
282 END IF;
283 IF p_old_decision_status = cst_approved AND
284 p_new_decision_status IN ( cst_removed,
285 cst_cancelled) AND
286 TRUNC(NVL(p_expiry_dt, igs_ge_date.igsdate('9999/01/01'))) <=
287 TRUNC(SYSDATE) THEN
288 p_message_name := 'IGS_PR_CNTCH_DEST_APRE_EXDT_AP';
289 RETURN FALSE;
290 END IF;
291 RETURN TRUE;
292 EXCEPTION
293 WHEN OTHERS THEN
294 IF c_spo%ISOPEN THEN
295 CLOSE c_spo;
296 END IF;
297 RAISE;
298 END;
299 EXCEPTION
300 WHEN OTHERS THEN
301 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 END prgp_val_spo_dcsn;
305
306 --
307 -- Validate student progression outcome decision date
308 FUNCTION prgp_val_spo_dcsn_dt(
309 p_decision_dt IN DATE ,
310 p_message_name OUT NOCOPY VARCHAR2 )
311 RETURN BOOLEAN IS
312 gv_other_detail VARCHAR2(255);
313 BEGIN -- prgp_val_spo_dcsn_dt
314 -- Validate that the IGS_PR_STDNT_PR_OU decision_dt
315 -- is not future dated.
316 BEGIN
317 -- Set the default message number
318 p_message_name := null;
319 IF p_decision_dt IS NULL THEN
320 RETURN TRUE;
321 END IF;
322 IF TRUNC(p_decision_dt) > TRUNC(SYSDATE) THEN
323 p_message_name := 'IGS_PR_DECDT_CNT_IN_FUT';
324 RETURN FALSE;
325 END IF;
326 RETURN TRUE;
327 END;
328 EXCEPTION
329 WHEN OTHERS THEN
330
331 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
332 IGS_GE_MSG_STACK.ADD;
333 App_Exception.Raise_Exception;
334 END prgp_val_spo_dcsn_dt;
335
336 --
337 -- Validate student progression outcome show cause date
338 FUNCTION prgp_val_spo_sc_dt(
339 p_person_id IN NUMBER ,
340 p_course_cd IN VARCHAR2 ,
341 p_applied_dt IN DATE ,
342 p_decision_dt IN DATE ,
343 p_decision_status IN VARCHAR2 ,
344 p_show_cause_expiry_dt IN DATE ,
345 p_old_show_cause_dt IN DATE ,
346 p_new_show_cause_dt IN DATE ,
347 p_show_cause_outcome_dt IN DATE ,
348 p_appeal_dt IN DATE ,
349 p_message_name OUT NOCOPY VARCHAR2 )
350 RETURN BOOLEAN IS
351 gv_other_detail VARCHAR2(255);
352 BEGIN -- prgp_val_spo_sc_dt
353 -- Validate the IGS_PR_STDNT_PR_OU show_cause_dt:
354 -- The show_cause_dt cannot be set unless decision_dt has been set
355 -- The show_cause_dt cannot be set if decision_status is WAIVED
356 -- Cannot set show_cause_dt if the appeal_dt is already set
357 -- Cannot alter show_cause_dt once show_cause_outcome_dt has been set
358 -- If 'applicable' apply_before_show_ind is N then cannot set show_cause_dt
359 -- once applied_dt has been set.
360 DECLARE
361 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
362 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
363 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
364 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
365 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
366 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
367 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
368 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
369 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
370 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
371 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
372 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
373 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
374 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
375 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
376 v_outcome_check_type VARCHAR2(10);
377 CURSOR c_sca IS
378 SELECT sca.version_number
379 FROM IGS_EN_STDNT_PS_ATT sca
380 WHERE sca.person_id = p_person_id AND
381 sca.course_cd = p_course_cd;
382 BEGIN
383 p_message_name := null;
384 IF p_person_id IS NULL OR
385 p_course_cd IS NULL THEN
386 RETURN TRUE;
387 END IF;
388 IF TRUNC(NVL(p_new_show_cause_dt, igs_ge_date.igsdate('0001/01/01'))) >
389 TRUNC(SYSDATE) THEN
390 p_message_name := 'IGS_PR_SHCA_DT_CNT_FUT';
391 RETURN FALSE;
392 END IF;
393 IF p_new_show_cause_dt IS NOT NULL AND
394 p_decision_dt IS NULL THEN
395 p_message_name := 'IGS_PR_SHCDT_CNT_ST_DEDT_NST';
396 RETURN FALSE;
397 END IF;
398 IF p_new_show_cause_dt IS NOT NULL AND
399 p_decision_status <> 'APPROVED' THEN
400 p_message_name := 'IGS_PR_SHCA_DTCNT_DEST_NTAP';
401 RETURN FALSE;
402 END IF;
403 IF p_show_cause_expiry_dt IS NULL AND
404 p_new_show_cause_dt IS NOT NULL THEN
405 p_message_name := 'IGS_PR_SCADT_CNT_SHEX_DTNST';
406 RETURN FALSE;
407 END IF;
408 IF p_new_show_cause_dt IS NOT NULL AND
409 p_appeal_dt IS NOT NULL THEN
410 p_message_name := 'IGS_PR_SHCDT_CNT_ST_APDT_ST';
411 RETURN FALSE;
412 END IF;
413 IF TRUNC(NVL(p_new_show_cause_dt, igs_ge_date.igsdate('0001/01/01'))) <>
414 TRUNC(NVL(p_old_show_cause_dt, igs_ge_date.igsdate('0001/01/01'))) AND
415 p_show_cause_outcome_dt IS NOT NULL AND
416 p_old_show_cause_dt IS NOT NULL THEN
417 p_message_name := 'IGS_PR_SHCA_DTCNT_AL_OUDTS';
418 RETURN FALSE;
419 END IF;
420 IF p_new_show_cause_dt IS NOT NULL AND
421 p_applied_dt IS NOT NULL THEN
422 OPEN c_sca;
423 FETCH c_sca INTO v_version_number;
424 CLOSE c_sca;
425 IGS_PR_GEN_003.IGS_PR_GET_CONFIG_PARM(
426 p_course_cd,
427 v_version_number,
428 v_apply_start_dt_alias,
429 v_apply_end_dt_alias,
430 v_end_benefit_dt_alias,
431 v_end_penalty_dt_alias,
432 v_show_cause_cutoff_dt,
433 v_appeal_cutoff_dt,
434 v_show_cause_ind,
435 v_apply_before_show_ind,
436 v_appeal_ind,
437 v_apply_before_appeal_ind,
438 v_count_sus_in_time_ind,
439 v_count_exc_in_time_ind,
440 v_calculate_wam_ind,
441 v_calculate_gpa_ind,
442 v_outcome_check_type);
443 IF v_apply_before_show_ind = 'N' THEN
444 p_message_name := 'IGS_PR_SHCA_DTCNT_APDT_BSNTA';
445 RETURN FALSE;
446 END IF;
447 END IF;
448 RETURN TRUE;
449 EXCEPTION
450 WHEN OTHERS THEN
451 IF c_sca%ISOPEN THEN
452 CLOSE c_sca;
453 END IF;
454 RAISE;
455 END;
456 EXCEPTION
457 WHEN OTHERS THEN
458
459 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END prgp_val_spo_sc_dt;
463
464 --
465 -- Validate student progression outcome show cause expiry date
466 FUNCTION prgp_val_spo_sc_exp(
467 p_person_id IN NUMBER ,
468 p_course_cd IN VARCHAR2 ,
469 p_prg_cal_type IN VARCHAR2 ,
470 p_prg_ci_sequence_number IN NUMBER ,
471 p_decision_status IN VARCHAR2 ,
472 p_old_show_cause_expiry_dt IN DATE ,
473 p_new_show_cause_expiry_dt IN DATE ,
474 p_show_cause_dt IN DATE ,
475 p_show_cause_outcome_dt IN DATE ,
476 p_appeal_expiry_dt IN DATE ,
477 p_message_name OUT NOCOPY VARCHAR2 )
478 RETURN BOOLEAN IS
479 gv_other_detail VARCHAR2(255);
480 BEGIN -- prgp_val_spo_sc_exp
481 -- Validate the IGS_PR_STDNT_PR_OU show_cause_expiry_dt:
482 -- The show_cause_expiry_dt cannot be set to a past date
483 -- The show_cause_expiry_dt cannot be set to if decision_status is WAIVED
484 -- The show_cause_expiry_dt cannot be after the appeal_expiry_dt (if set)
485 -- Cannot alter show_cause_expiry_dt once show_cause_outcome_dt has been set
486 -- Warn if the show_cause_expiry_dt is after the applicable show cause
487 -- cut-off date for the student's course version.
488 DECLARE
489 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
490 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
491 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
492 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
493 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
494 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
495 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
496 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
497 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
498 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
499 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
500 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
501 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
502 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
503 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
504 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
505 v_outcome_check_type VARCHAR2(10);
506 CURSOR c_sca IS
507 SELECT sca.version_number
508 FROM IGS_EN_STDNT_PS_ATT sca
509 WHERE sca.person_id = p_person_id AND
510 sca.course_cd = p_course_cd;
511 CURSOR c_daiv
512 (cp_shw_cse_ctoff_dt_alias IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE) IS
513 SELECT MAX(daiv.alias_val)
514 FROM IGS_CA_DA_INST_V daiv
515 WHERE daiv.dt_alias = cp_shw_cse_ctoff_dt_alias AND
516 daiv.cal_type = p_prg_cal_type AND
517 daiv.ci_sequence_number = p_prg_ci_sequence_number;
518 BEGIN
519 p_message_name := null;
520 IF p_person_id IS NULL OR
521 p_course_cd IS NULL THEN
522 RETURN TRUE;
523 END IF;
524 IF TRUNC(NVL(p_new_show_cause_expiry_dt,
525 igs_ge_date.igsdate('9999/01/01'))) < TRUNC(SYSDATE) THEN
526 p_message_name := 'IGS_PR_SHCA_EXPDT_CNTB_TODAT';
527 RETURN FALSE;
528 END IF;
529 IF p_new_show_cause_expiry_dt IS NOT NULL AND
530 p_decision_status <> 'APPROVED' THEN
531 p_message_name := 'IGS_PR_SHCA_EXPDT_CNTB_DSTNA';
532 RETURN FALSE;
533 END IF;
534 IF TRUNC(NVL(p_new_show_cause_expiry_dt,
535 igs_ge_date.igsdate('0001/01/01'))) >
536 TRUNC(NVL(p_appeal_expiry_dt,
537 igs_ge_date.igsdate('9999/01/01'))) THEN
538 p_message_name := 'IGS_PR_SH_EXPDT_CNT_APEXDT';
539 RETURN FALSE;
540 END IF;
541 IF TRUNC(NVL(p_new_show_cause_expiry_dt,
542 igs_ge_date.igsdate('0001/01/01'))) <>
543 TRUNC(NVL(p_old_show_cause_expiry_dt,
544 igs_ge_date.igsdate('0001/01/01'))) AND
545 p_show_cause_dt IS NOT NULL THEN
546 p_message_name := 'IGS_PR_SHEXP_CNT_AL_SCDST';
547 RETURN FALSE;
548 END IF;
549 IF TRUNC(NVL(p_new_show_cause_expiry_dt,
550 igs_ge_date.igsdate('0001/01/01'))) <>
551 TRUNC(NVL(p_old_show_cause_expiry_dt,
552 igs_ge_date.igsdate('0001/01/01'))) AND
553 p_show_cause_outcome_dt IS NOT NULL THEN
554 p_message_name := 'IGS_PR_SHEXP_DTCNT_ASHOU_DTST';
555 RETURN FALSE;
556 END IF;
557 IF p_new_show_cause_expiry_dt IS NOT NULL THEN
558 OPEN c_sca;
559 FETCH c_sca INTO v_version_number;
560 CLOSE c_sca;
561 IGS_PR_GEN_003.IGS_PR_GET_CONFIG_PARM(
562 p_course_cd,
563 v_version_number,
564 v_apply_start_dt_alias,
565 v_apply_end_dt_alias,
566 v_end_benefit_dt_alias,
567 v_end_penalty_dt_alias,
568 v_show_cause_cutoff_dt,
569 v_appeal_cutoff_dt,
570 v_show_cause_ind,
571 v_apply_before_show_ind,
572 v_appeal_ind,
573 v_apply_before_appeal_ind,
574 v_count_sus_in_time_ind,
575 v_count_exc_in_time_ind,
576 v_calculate_wam_ind,
577 v_calculate_gpa_ind,
578 v_outcome_check_type);
579 OPEN c_daiv(v_show_cause_cutoff_dt);
580 FETCH c_daiv INTO v_alias_val;
581 CLOSE c_daiv;
582 IF TRUNC(NVL(p_new_show_cause_expiry_dt,
583 igs_ge_date.igsdate('0001/01/01'))) >
584 TRUNC(v_alias_val) THEN
585 p_message_name := 'IGS_PR_WA_SCA_EXDT_ASCT_DT';
586 RETURN TRUE; -- warning only
587 END IF;
588 END IF;
589 RETURN TRUE;
590 EXCEPTION
591 WHEN OTHERS THEN
592 IF c_sca%ISOPEN THEN
593 CLOSE c_sca;
594 END IF;
595 IF c_daiv%ISOPEN THEN
596 CLOSE c_daiv;
597 END IF;
598 RAISE;
599 END;
600 EXCEPTION
601 WHEN OTHERS THEN
602
603 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
604 IGS_GE_MSG_STACK.ADD;
605 App_Exception.Raise_Exception;
606 END prgp_val_spo_sc_exp;
607
608 --
609 -- Validate student progression outcome show cause outcome date
610 FUNCTION prgp_val_spo_sc_out(
611 p_decision_status IN VARCHAR2 ,
612 p_show_cause_dt IN DATE ,
613 p_show_cause_outcome_dt IN DATE ,
614 p_show_cause_outcome_type IN VARCHAR2 ,
615 p_message_name OUT NOCOPY VARCHAR2 )
616 RETURN BOOLEAN IS
617 gv_other_detail VARCHAR2(255);
618 BEGIN -- prgp_val_spo_sc_out
619 -- Validate the IGS_PR_STDNT_PR_OU show_cause_outcome_dt and
620 -- show_cause_outcome_type:
621 -- * The show_cause_outcome_dt, show_cause_outcome_type cannot be set
622 -- where show_cause_dt is not set
623 -- * Both show_cause_outcome_dt, show_cause_outcome_type must be set
624 -- and unset together.
625 DECLARE
626 BEGIN
627 p_message_name := null;
628 IF p_show_cause_dt IS NULL AND
629 (p_show_cause_outcome_dt IS NOT NULL OR
630 p_show_cause_outcome_type IS NOT NULL) THEN
631 p_message_name := 'IGS_PR_SHCA_OTY_ODTCT_SCDT_NT';
632 RETURN FALSE;
633 END IF;
634 IF TRUNC(NVL(p_show_cause_outcome_dt,
635 igs_ge_date.igsdate('0001/01/01'))) > TRUNC(SYSDATE) THEN
636 p_message_name := 'IGS_PR_SHCA_OUDT_CNT_FUT';
637 RETURN FALSE;
638 END IF;
639 IF p_show_cause_outcome_dt IS NOT NULL AND
640 p_decision_status <> 'APPROVED' THEN
641 p_message_name := 'IGS_PR_SHCA_OUT_DTTY_CNTST';
642 RETURN FALSE;
643 END IF;
644 IF p_show_cause_outcome_dt IS NULL AND
645 p_show_cause_outcome_type IS NOT NULL THEN
646 p_message_name := 'IGS_PR_SCADT_MST_SHOTY_ST';
647 RETURN FALSE;
648 END IF;
649 IF p_show_cause_outcome_dt IS NOT NULL AND
650 p_show_cause_outcome_type IS NULL THEN
651 p_message_name := 'IGS_PR_SHOT_TYMST_SCO_DTST';
652 RETURN FALSE;
653 END IF;
654 RETURN TRUE;
655 END;
656 EXCEPTION
657 WHEN OTHERS THEN
658
659 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
660 IGS_GE_MSG_STACK.ADD;
661 App_Exception.Raise_Exception;
662 END prgp_val_spo_sc_out;
663
664 --
665 -- Validate student progression outcome appeal date
666 FUNCTION prgp_val_spo_apl_dt(
667 p_person_id IN NUMBER ,
668 p_course_cd IN VARCHAR2 ,
669 p_applied_dt IN DATE ,
670 p_decision_dt IN DATE ,
671 p_decision_status IN VARCHAR2 ,
672 p_appeal_expiry_dt IN DATE ,
673 p_old_appeal_dt IN DATE ,
674 p_new_appeal_dt IN DATE ,
675 p_appeal_outcome_dt IN DATE ,
676 p_show_cause_dt IN DATE ,
677 p_message_name OUT NOCOPY VARCHAR2 )
678 RETURN BOOLEAN IS
679 gv_other_detail VARCHAR2(255);
680 BEGIN -- prgp_val_spo_apl_dt
681 -- Validate the IGS_PR_STDNT_PR_OU appeal_dt:
682 -- The appeal_dt cannot be set unless decision_dt is set
683 -- The appeal_dt cannot be before the show_cause_dt (if set)
684 -- Cannot alter appeal_dt once appeal_outcome_dt has been set
685 -- If 'applicable' apply_before_appeal_ind is N then cannot
686 -- set appeal_dt once applied_dt has been set.
687 DECLARE
688 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
689 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
690 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
691 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
692 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
693 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
694 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
695 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
696 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
697 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
698 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
699 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
700 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
701 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
702 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
703 v_outcome_check_type VARCHAR2(10);
704 CURSOR c_sca IS
705 SELECT sca.version_number
706 FROM IGS_EN_STDNT_PS_ATT sca
707 WHERE sca.person_id = p_person_id AND
708 sca.course_cd = p_course_cd;
709 BEGIN
710 p_message_name := null;
711 IF p_person_id IS NULL OR
712 p_course_cd IS NULL THEN
713 RETURN TRUE;
714 END IF;
715 IF TRUNC(NVL(p_new_appeal_dt, igs_ge_date.igsdate('0001/01/01'))) >
716 TRUNC(SYSDATE) THEN
717 p_message_name := 'IGS_PR_APOUT_DT_CNT_FUT';
718 RETURN FALSE;
719 END IF;
720 IF p_new_appeal_dt IS NOT NULL AND
721 p_decision_status <> 'APPROVED' THEN
722 p_message_name := 'IGS_PR_APDT_CNT_SDCST_NTAP';
723 RETURN FALSE;
724 END IF;
725 IF p_appeal_expiry_dt IS NULL AND
726 p_new_appeal_dt IS NOT NULL THEN
727 p_message_name := 'IGS_PR_APDT_CNT_SAPEX_DTNTS';
728 RETURN FALSE;
729 END IF;
730 IF TRUNC(NVL(p_new_appeal_dt, igs_ge_date.igsdate('9999/01/01'))) <
731 TRUNC(NVL(p_show_cause_dt, igs_ge_date.igsdate('0001/01/01'))) THEN
732 p_message_name := 'IGS_PR_APDT_CNTS_BSHDT';
733 RETURN FALSE;
734 END IF;
735 IF TRUNC(NVL(p_new_appeal_dt, igs_ge_date.igsdate('0001/01/01'))) <>
736 TRUNC(NVL(p_old_appeal_dt, igs_ge_date.igsdate('0001/01/01'))) AND
737 p_appeal_outcome_dt IS NOT NULL AND
738 p_old_appeal_dt IS NOT NULL THEN
739 p_message_name := 'IGS_PR_APDT_CNT_AL_AODT_ST';
740 RETURN FALSE;
741 END IF;
742 IF p_new_appeal_dt IS NOT NULL AND
743 p_applied_dt IS NOT NULL THEN
744 OPEN c_sca;
745 FETCH c_sca INTO v_version_number;
746 CLOSE c_sca;
747 IGS_PR_GEN_003.IGS_PR_GET_CONFIG_PARM(
748 p_course_cd,
749 v_version_number,
750 v_apply_start_dt_alias,
751 v_apply_end_dt_alias,
752 v_end_benefit_dt_alias,
753 v_end_penalty_dt_alias,
754 v_show_cause_cutoff_dt,
755 v_appeal_cutoff_dt,
756 v_show_cause_ind,
757 v_apply_before_show_ind,
758 v_appeal_ind,
759 v_apply_before_appeal_ind,
760 v_count_sus_in_time_ind,
761 v_count_exc_in_time_ind,
762 v_calculate_wam_ind,
763 v_calculate_gpa_ind,
764 v_outcome_check_type);
765 IF v_apply_before_appeal_ind = 'N' THEN
766 p_message_name := 'IGS_PR_APDT_CNT_APDT_SABA_NAL';
767 RETURN FALSE;
768 END IF;
769 END IF;
770 RETURN TRUE;
771 EXCEPTION
772 WHEN OTHERS THEN
773 IF c_sca%ISOPEN THEN
774 CLOSE c_sca;
775 END IF;
776 RAISE;
777 END;
778 EXCEPTION
779 WHEN OTHERS THEN
780
781 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
782 IGS_GE_MSG_STACK.ADD;
783 App_Exception.Raise_Exception;
784 END prgp_val_spo_apl_dt;
785
786 --
787 -- Validate student progression outcome appeal expiry date
788 FUNCTION prgp_val_spo_apl_exp(
789 p_person_id IN NUMBER ,
790 p_course_cd IN VARCHAR2 ,
791 p_prg_cal_type IN VARCHAR2 ,
792 p_prg_ci_sequence_number IN NUMBER ,
793 p_decision_status IN VARCHAR2 ,
794 p_old_appeal_expiry_dt IN DATE ,
795 p_new_appeal_expiry_dt IN DATE ,
796 p_appeal_dt IN DATE ,
797 p_appeal_outcome_dt IN DATE ,
798 p_show_cause_expiry_dt IN DATE ,
799 p_message_name OUT NOCOPY VARCHAR2 )
800 RETURN BOOLEAN IS
801 gv_other_detail VARCHAR2(255);
802 BEGIN -- prgp_val_spo_apl_exp
803 -- Validate the IGS_PR_STDNT_PR_OU appeal_expiry_dt:
804 -- The appeal_expiry_dt cannot be set to a past date
805 -- The appeal_expiry_dt cannot be before the show_cause_expiry_dt (if set)
806 -- Cannot alter appeal_expiry_dt once appeal_outcome_dt has been set
807 -- Warn if the appeal_expiry_dt is after the applicable appeal cut-off date
808 -- for the student's course version.
809 DECLARE
810 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
811 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
812 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
813 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
814 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
815 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
816 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
817 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
818 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
819 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
820 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
821 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
822 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
823 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
824 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
825 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
826 v_outcome_check_type VARCHAR2(10);
827 CURSOR c_sca IS
828 SELECT sca.version_number
829 FROM IGS_EN_STDNT_PS_ATT sca
830 WHERE sca.person_id = p_person_id AND
831 sca.course_cd = p_course_cd;
832 CURSOR c_daiv (cp_apl_ctoff_dt_alias IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE) IS
833 SELECT MAX(daiv.alias_val)
834 FROM IGS_CA_DA_INST_V daiv
835 WHERE daiv.dt_alias = cp_apl_ctoff_dt_alias AND
836 daiv.cal_type = p_prg_cal_type AND
837 daiv.ci_sequence_number = p_prg_ci_sequence_number;
838 BEGIN
839 p_message_name := null;
840 IF p_person_id IS NULL OR
841 p_course_cd IS NULL THEN
842 RETURN TRUE;
843 END IF;
844 IF TRUNC(NVL(p_new_appeal_expiry_dt, igs_ge_date.igsdate('9999/01/01'))) <
845 TRUNC(SYSDATE) THEN
846 p_message_name := 'IGS_PR_APEXDT_CNTS_BTODT';
847 RETURN FALSE;
848 END IF;
849 IF TRUNC(NVL(p_new_appeal_expiry_dt, igs_ge_date.igsdate('9999/01/01'))) <
850 TRUNC(NVL(p_show_cause_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) THEN
851 p_message_name := 'IGS_PR_APEXDT_CNTB_SEXDT';
852 RETURN FALSE;
853 END IF;
854 IF TRUNC(NVL(p_new_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) <>
855 TRUNC(NVL(p_old_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) AND
856 p_appeal_dt IS NOT NULL THEN
857 p_message_name := 'IGS_PR_APEDT_CNT_AL_APDT_HST';
858 RETURN FALSE;
859 END IF;
860 IF TRUNC(NVL(p_new_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) <>
861 TRUNC(NVL(p_old_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) AND
862 p_appeal_outcome_dt IS NOT NULL THEN
863 p_message_name := 'IGS_PR_APEXDT_CNTAL_AODT_HST';
864 RETURN FALSE;
865 END IF;
866 IF p_new_appeal_expiry_dt IS NOT NULL AND
867 p_decision_status <> 'APPROVED' THEN
868 p_message_name := 'IGS_PR_AEXDT_CNT_ST_DECST_NTAP';
869 RETURN FALSE;
870 END IF;
871 IF p_new_appeal_expiry_dt IS NOT NULL THEN
872 OPEN c_sca;
873 FETCH c_sca INTO v_version_number;
874 CLOSE c_sca;
875 IGS_PR_GEN_003.IGS_PR_GET_CONFIG_PARM(
876 p_course_cd,
877 v_version_number,
878 v_apply_start_dt_alias,
879 v_apply_end_dt_alias,
880 v_end_benefit_dt_alias,
881 v_end_penalty_dt_alias,
882 v_show_cause_cutoff_dt,
883 v_appeal_cutoff_dt,
884 v_show_cause_ind,
885 v_apply_before_show_ind,
886 v_appeal_ind,
887 v_apply_before_appeal_ind,
888 v_count_sus_in_time_ind,
889 v_count_exc_in_time_ind,
890 v_calculate_wam_ind,
891 v_calculate_gpa_ind,
892 v_outcome_check_type);
893 OPEN c_daiv(v_appeal_cutoff_dt);
894 FETCH c_daiv INTO v_alias_val;
895 CLOSE c_daiv;
896 IF TRUNC(NVL(p_new_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) >
897 TRUNC(v_alias_val) THEN
898 p_message_name := 'IGS_PR_WA_APEX_DTAT_APCT_DT';
899 RETURN TRUE; -- warning only
900 END IF;
901 END IF;
902 RETURN TRUE;
903 EXCEPTION
904 WHEN OTHERS THEN
905 IF c_sca%ISOPEN THEN
906 CLOSE c_sca;
907 END IF;
908 IF c_daiv%ISOPEN THEN
909 CLOSE c_daiv;
910 END IF;
911 RAISE;
912 END;
913 EXCEPTION
914 WHEN OTHERS THEN
915
916 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
917 IGS_GE_MSG_STACK.ADD;
918 App_Exception.Raise_Exception;
919 END prgp_val_spo_apl_exp;
920
921 --
922 -- Validate student progression outcome appeal outcome date
923 FUNCTION prgp_val_spo_apl_out(
924 p_old_decision_status IN VARCHAR2 ,
925 p_appeal_dt IN DATE ,
926 p_appeal_outcome_dt IN DATE ,
927 p_appeal_outcome_type IN VARCHAR2 ,
928 p_message_name OUT NOCOPY VARCHAR2 )
929 RETURN BOOLEAN IS
930 gv_other_detail VARCHAR2(255);
931 BEGIN -- prgp_val_spo_apl_out
932 -- Validate the IGS_PR_STDNT_PR_OU appeal_outcome_dt and
933 -- appeal_outcome_type:
934 -- * The appeal_outcome_dt, appeal_outcome_type cannot be set where appeal_dt
935 -- is not set
936 -- * Both appeal_outcome_dt, appeal_outcome_type must be set and unset together
937 DECLARE
938 BEGIN
939 p_message_name := null;
940 IF p_appeal_dt IS NULL AND
941 (p_appeal_outcome_dt IS NOT NULL OR
942 p_appeal_outcome_type IS NOT NULL) THEN
943 p_message_name := 'IGS_PR_APOTY_DTCNT_APDT_NST';
944 RETURN FALSE;
945 END IF;
946 IF TRUNC(NVL(p_appeal_outcome_dt, igs_ge_date.igsdate('0001/01/01'))) >
947 TRUNC(SYSDATE) THEN
948 p_message_name := 'IGS_PR_APOUT_DT_CNT_FUT';
949 RETURN FALSE;
950 END IF;
951 IF p_appeal_outcome_dt IS NOT NULL AND
952 p_old_decision_status <> 'APPROVED' THEN
953 p_message_name := 'IGS_PR_AOUT_DTTY_CNTS_DEST_NAP';
954 RETURN FALSE;
955 END IF;
956 IF p_appeal_outcome_dt IS NULL AND
957 p_appeal_outcome_type IS NOT NULL THEN
958 p_message_name := 'IGS_PR_APODT_MST_AOTY_ST';
959 RETURN FALSE;
960 END IF;
961 IF p_appeal_outcome_dt IS NOT NULL AND
962 p_appeal_outcome_type IS NULL THEN
963 p_message_name := 'IGS_PR_APOTY_MST_AODT_ST';
964 RETURN FALSE;
965 END IF;
966 RETURN TRUE;
967 END;
968 EXCEPTION
969 WHEN OTHERS THEN
970
971 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
972 IGS_GE_MSG_STACK.ADD;
973 App_Exception.Raise_Exception;
974 END prgp_val_spo_apl_out;
975
976 --
977 -- Get applied date if student progression outcome detail has changed
978 FUNCTION prgp_GET_SPO_APLY_DT(
979 p_decision_status IN VARCHAR2 ,
980 p_old_applied_dt IN DATE ,
981 p_new_applied_dt IN DATE ,
982 p_old_encmb_course_group_cd IN VARCHAR2 ,
983 p_new_encmb_course_group_cd IN VARCHAR2 ,
984 p_old_restricted_enrolment_cp IN NUMBER ,
985 p_new_restricted_enrolment_cp IN NUMBER ,
986 p_old_restricted_attend_type IN VARCHAR2 ,
987 p_new_restricted_attend_type IN VARCHAR2 ,
988 p_old_expiry_dt IN DATE ,
989 p_new_expiry_dt IN DATE ,
990 p_old_duration IN NUMBER ,
991 p_new_duration IN NUMBER ,
992 p_old_duration_type IN VARCHAR2 ,
993 p_new_duration_type IN VARCHAR2 ,
994 p_out_applied_dt OUT NOCOPY DATE )
995 RETURN BOOLEAN IS
996 gv_other_detail VARCHAR2(255);
997 BEGIN -- IGS_PR_GEN_004.IGS_PR_GET_SPO_APLY_DT
998 -- If the student progression outcome details have been changed
999 -- return the correct applied date.
1000 p_out_applied_dt := NULL;
1001 IF p_decision_status <> 'APPROVED' OR
1002 TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01'))) <>
1003 TRUNC(NVL(p_old_applied_dt, igs_ge_date.igsdate('9999/01/01'))) THEN
1004 RETURN TRUE;
1005 END IF;
1006 IF NVL(p_old_encmb_course_group_cd, 'NULL') <>
1007 NVL(p_new_encmb_course_group_cd, 'NULL') OR
1008 NVL(p_old_restricted_enrolment_cp, 0) <>
1009 NVL(p_new_restricted_enrolment_cp, 0) OR
1010 NVL(p_old_restricted_attend_type, 'NULL') <>
1011 NVL(p_new_restricted_attend_type, 'NULL') OR
1012 TRUNC(NVL(p_old_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) <>
1013 TRUNC(NVL(p_new_expiry_dt, igs_ge_date.igsdate('0001/01/01'))) OR
1014 NVL(p_old_duration, 0) <>
1015 NVL(p_new_duration, 0) OR
1016 NVL(p_old_duration_type, 'NULL') <>
1017 NVL(p_new_duration_type, 'NULL') THEN
1018 If TRUNC(p_new_applied_dt) <>
1019 TRUNC(igs_ge_date.igsdate('0001/01/01')) THEN
1020 p_out_applied_dt := igs_ge_date.igsdate('0001/01/01');
1021 RETURN FALSE;
1022 END IF;
1023 END IF;
1024 RETURN TRUE;
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027
1028 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1029 IGS_GE_MSG_STACK.ADD;
1030 App_Exception.Raise_Exception;
1031 END PRGP_GET_SPO_APLY_DT;
1032 --
1033 --
1034 -- Routine to save key in a PL/SQL TABLE for the current commit.
1035 PROCEDURE prgp_set_spo_rowid(
1036 p_person_id IN NUMBER ,
1037 p_course_cd IN VARCHAR2 ,
1038 p_sequence_number IN NUMBER ,
1039 p_prg_cal_type IN VARCHAR2 ,
1040 p_prg_ci_sequence_number IN NUMBER ,
1041 p_rule_check_dt IN DATE ,
1042 p_progression_rule_cat IN VARCHAR2 ,
1043 p_pra_sequence_number IN NUMBER ,
1044 p_progression_outcome_type IN VARCHAR2 ,
1045 p_old_decision_status IN VARCHAR2 ,
1046 p_new_decision_status IN VARCHAR2 ,
1047 p_decision_dt IN DATE ,
1048 p_decision_org_unit_cd IN VARCHAR2 ,
1049 p_decision_ou_start_dt IN DATE ,
1050 p_applied_dt IN DATE ,
1051 p_expiry_dt IN DATE ,
1052 p_encmb_course_group_cd IN VARCHAR2 ,
1053 p_restricted_enrolment_cp IN NUMBER ,
1054 p_restricted_attendance_type IN VARCHAR2 ,
1055 p_old_duration IN NUMBER ,
1056 p_new_duration IN NUMBER ,
1057 p_old_duration_type IN VARCHAR2 ,
1058 p_new_duration_type IN VARCHAR2 )
1059 IS
1060 v_index BINARY_INTEGER;
1061 v_spo_found BOOLEAN DEFAULT FALSE;
1062 BEGIN
1063 -- Check if record already exists in gt_rowid_table
1064 FOR v_index IN 1..gv_table_index - 1 LOOP
1065 IF gt_rowid_table(v_index).person_id = p_person_id AND
1066 gt_rowid_table(v_index).course_cd = p_course_cd AND
1067 gt_rowid_table(v_index).sequence_number = p_sequence_number THEN
1068 v_spo_found := TRUE;
1069 EXIT;
1070 END IF;
1071 END LOOP;
1072 IF NOT v_spo_found THEN
1073 --save student progression outcome person_id, course_cd key details
1074 gt_rowid_table(gv_table_index).person_id := p_person_id;
1075 gt_rowid_table(gv_table_index).course_cd := p_course_cd;
1076 gt_rowid_table(gv_table_index).sequence_number := p_sequence_number;
1077 gt_rowid_table(gv_table_index).prg_cal_type := p_prg_cal_type;
1078 gt_rowid_table(gv_table_index).prg_ci_sequence_number :=
1079 p_prg_ci_sequence_number;
1080 gt_rowid_table(gv_table_index).rule_check_dt := p_rule_check_dt;
1081 gt_rowid_table(gv_table_index).progression_rule_cat := p_progression_rule_cat;
1082 gt_rowid_table(gv_table_index).pra_sequence_number := p_pra_sequence_number;
1083 gt_rowid_table(gv_table_index).progression_outcome_type :=
1084 p_progression_outcome_type;
1085 gt_rowid_table(gv_table_index).old_decision_status := p_old_decision_status;
1086 gt_rowid_table(gv_table_index).new_decision_status := p_new_decision_status;
1087 gt_rowid_table(gv_table_index).decision_dt := p_decision_dt;
1088 gt_rowid_table(gv_table_index).decision_org_unit_cd := p_decision_org_unit_cd;
1089 gt_rowid_table(gv_table_index).decision_ou_start_dt := p_decision_ou_start_dt;
1090 gt_rowid_table(gv_table_index).applied_dt := p_applied_dt;
1091 gt_rowid_table(gv_table_index).expiry_dt := p_expiry_dt;
1092 gt_rowid_table(gv_table_index).encmb_course_group_cd :=
1093 p_encmb_course_group_cd;
1094 gt_rowid_table(gv_table_index).restricted_enrolment_cp :=
1095 p_restricted_enrolment_cp;
1096 gt_rowid_table(gv_table_index).restricted_attendance_type :=
1097 p_restricted_attendance_type;
1098 gt_rowid_table(gv_table_index).old_duration := p_old_duration;
1099 gt_rowid_table(gv_table_index).new_duration := p_new_duration;
1100 gt_rowid_table(gv_table_index).old_duration_type := p_old_duration_type;
1101 gt_rowid_table(gv_table_index).new_duration_type := p_new_duration_type;
1102 gv_table_index := gv_table_index +1;
1103 END IF;
1104 END prgp_set_spo_rowid;
1105
1106 --
1107 -- Validate progression calendar instance
1108 FUNCTION prgp_val_prg_ci(
1109 p_cal_type IN VARCHAR2 ,
1110 p_ci_sequence_number IN NUMBER ,
1111 p_message_name OUT NOCOPY VARCHAR2 )
1112 RETURN BOOLEAN IS
1113 gv_other_detail VARCHAR2(255);
1114 BEGIN -- Validate for active and progression calender instance.
1115 DECLARE
1116 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
1117 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
1118 v_s_cal_type IGS_CA_TYPE.s_cal_cat%TYPE;
1119 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
1120 CURSOR c_ci_cat_cs IS
1121 SELECT cat.s_cal_cat,
1122 cs.s_cal_status
1123 FROM IGS_CA_INST ci,
1124 IGS_CA_TYPE cat,
1125 IGS_CA_STAT cs
1126 WHERE cat.cal_type = p_cal_type AND
1127 ci.cal_type = cat.cal_type AND
1128 ci.sequence_number = p_ci_sequence_number AND
1129 cs.cal_status = ci.cal_status;
1130 BEGIN
1131 -- Set the default message number
1132 p_message_name := null;
1133 OPEN c_ci_cat_cs;
1134 FETCH c_ci_cat_cs INTO v_s_cal_type, v_s_cal_status;
1135 IF c_ci_cat_cs%NOTFOUND THEN
1136 CLOSE c_ci_cat_cs;
1137 RETURN TRUE;
1138 END IF;
1139 CLOSE c_ci_cat_cs;
1140 IF v_s_cal_type <>cst_progress OR
1141 v_s_cal_status<>cst_active THEN
1142 p_message_name := 'IGS_PR_CAL_INS_OACT_PR_CAL';
1143 RETURN FALSE;
1144 END IF;
1145 RETURN TRUE;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF c_ci_cat_cs%ISOPEN THEN
1149 CLOSE c_ci_cat_cs;
1150 END IF;
1151 RAISE;
1152 END;
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155
1156 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1157 IGS_GE_MSG_STACK.ADD;
1158 App_Exception.Raise_Exception;
1159 END prgp_val_prg_ci;
1160
1161 --
1162 -- Validate student progression outcome can be inserted
1163 FUNCTION prgp_val_spo_ins(
1164 p_person_id IN NUMBER ,
1165 p_course_cd IN VARCHAR2 ,
1166 p_prg_cal_type IN VARCHAR2 ,
1167 p_prg_ci_sequence_number IN NUMBER ,
1168 p_rule_check_dt IN DATE ,
1169 p_progression_rule_cat IN VARCHAR2 ,
1170 p_pra_sequence_number IN NUMBER ,
1171 p_message_name OUT NOCOPY VARCHAR2 )
1172 RETURN BOOLEAN IS
1173 gv_other_detail VARCHAR2(255);
1174 BEGIN -- prgp_val_spo_ins
1175 -- Validate insert of IGS_PR_STDNT_PR_OU record, validating for
1176 -- related course attempt must have status of ENROLLED/INACTIVE/INTERMIT/
1177 -- LAPSED / DISCONTIN
1178 -- If related to IGS_PR_SDT_PR_RU_CK cannot be record against passed rule.
1179 -- If related to student rule check, must be recorded against the latest
1180 --check of a given rule within the IGS_PR_SDT_PR_RU_CK table.
1181 DECLARE
1182 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1183 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
1184 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
1185 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1186 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1187 v_s_progression_outcome_type
1188 IGS_PR_OU_TYPE.s_progression_outcome_type%TYPE;
1189 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1190 v_passed_ind IGS_PR_SDT_PR_RU_CK.passed_ind%TYPE;
1191 v_dummy VARCHAR2(1);
1192 CURSOR c_sca IS
1193 SELECT course_attempt_status
1194 FROM IGS_EN_STDNT_PS_ATT sca
1195 WHERE person_id = p_person_id AND
1196 course_cd = p_course_cd;
1197 CURSOR c_spra1 IS
1198 SELECT passed_ind
1199 FROM IGS_PR_SDT_PR_RU_CK sprc
1200 WHERE person_id = p_person_id AND
1201 course_cd = p_course_cd AND
1202 prg_cal_type = p_prg_cal_type AND
1203 prg_ci_sequence_number = p_prg_ci_sequence_number AND
1204 rule_check_dt = p_rule_check_dt AND
1205 progression_rule_cat = p_progression_rule_cat AND
1206 pra_sequence_number = p_pra_sequence_number;
1207 CURSOR c_spra2 IS
1208 SELECT 'X'
1209 FROM IGS_PR_SDT_PR_RU_CK sprc
1210 WHERE person_id = p_person_id AND
1211 course_cd = p_course_cd AND
1212 prg_cal_type = p_prg_cal_type AND
1213 prg_ci_sequence_number = p_prg_ci_sequence_number AND
1214 pra_sequence_number = p_pra_sequence_number AND
1215 rule_check_dt > p_rule_check_dt;
1216 BEGIN
1217 -- Set the default message number
1218 p_message_name := null;
1219 OPEN c_sca;
1220 FETCH c_sca INTO v_course_attempt_status;
1221 IF c_sca%NOTFOUND THEN
1222 CLOSE c_sca;
1223 RETURN TRUE;
1224 END IF;
1225 CLOSE c_sca;
1226 IF v_course_attempt_status NOT IN (
1227 cst_enrolled,
1228 cst_inactive,
1229 cst_intermit,
1230 cst_lapsed,
1231 cst_discontin)THEN
1232 p_message_name := 'IGS_PR_CA_REC_PROU_AENRO_INT';
1233 RETURN FALSE;
1234 END IF;
1235 IF p_rule_check_dt is NOT NULL THEN
1236 OPEN c_spra1;
1237 FETCH c_spra1 INTO v_passed_ind;
1238 IF c_spra1%FOUND THEN
1239 IF v_passed_ind = 'Y' THEN
1240 CLOSE c_spra1;
1241 p_message_name := 'IGS_PR_CNT_CROUT_APRRU_CHPAS';
1242 RETURN FALSE;
1243 END IF;
1244 ELSE
1245 CLOSE c_spra1;
1246 END IF;
1247 OPEN c_spra2;
1248 FETCH c_spra2 INTO v_dummy;
1249 IF c_spra2%FOUND THEN
1250 CLOSE c_spra2;
1251 p_message_name := 'IGS_PR_OUT_REC_ALAT_CH_PRCAL';
1252 RETURN FALSE;
1253 END IF;
1254 CLOSE c_spra2;
1255 END IF;
1256 RETURN TRUE;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 IF c_sca%ISOPEN THEN
1260 CLOSE c_sca;
1261 END IF;
1262 IF c_spra1%ISOPEN THEN
1263 CLOSE c_spra1;
1264 END IF;
1265 IF c_spra2%ISOPEN THEN
1266 CLOSE c_spra2;
1267 END IF;
1268 RAISE;
1269 END;
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272
1273 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1274 IGS_GE_MSG_STACK.ADD;
1275 App_Exception.Raise_Exception;
1276 END prgp_val_spo_ins;
1277
1278 --
1279 -- Validate student progression outcome has the required details
1280 FUNCTION prgp_val_spo_rqrd(
1281 p_progression_outcome_type IN VARCHAR2 ,
1282 p_duration IN NUMBER ,
1283 p_duration_type IN VARCHAR2 ,
1284 p_message_name OUT NOCOPY VARCHAR2 )
1285 RETURN BOOLEAN IS
1286 gv_other_detail VARCHAR2(255);
1287 BEGIN -- prgp_val_spo_rqrd
1288 -- Validate that if student _progression_outcome has the required details:
1289 -- If duration_type is specified (as NORMAL or EFFECTIVE) then a duration
1290 -- must be specified, and visa versa
1291 -- If related s_progression_outcome_type is SUSPENSION,
1292 -- then duration and duration_type must be specified
1293 -- If related s_progression_outcome_type is EXCLUSION, EXPULSION, NOPENALTY,
1294 -- MANUAL or EX_FUND , then duration and duration_type cannot be specified
1295 -- If related s_progress_outcome_type not PROBATION then duration_type cannot
1296 -- be EFFECTIVE.
1297 /*
1298 ||Change History:
1299 || Who When What
1300 ||==============================================================================||
1301 || NALKUMAR 19-NOV-2002 Bug NO: 2658550. Modified this function as per the||
1302 || FA110 PR Enh. ||
1303 ||==============================================================================||
1304 */
1305 DECLARE
1306 cst_normal CONSTANT VARCHAR(10) := 'NORMAL';
1307 cst_effective CONSTANT VARCHAR(10) := 'EFFECTIVE';
1308 cst_suspension CONSTANT VARCHAR(10) := 'SUSPENSION';
1309 cst_exclusion CONSTANT VARCHAR(10) := 'EXCLUSION';
1310 cst_expulsion CONSTANT VARCHAR(10) := 'EXPULSION';
1311 cst_nopenalty CONSTANT VARCHAR(10) := 'NOPENALTY';
1312 cst_manual CONSTANT VARCHAR(10) := 'MANUAL';
1313 cst_probation CONSTANT VARCHAR(10) := 'PROBATION';
1314 cst_ex_fund CONSTANT VARCHAR2(10):= 'EX_FUND';
1315 v_s_progression_outcome_type IGS_PR_OU_TYPE.s_progression_outcome_type%TYPE;
1316 CURSOR c_pot IS
1317 SELECT s_progression_outcome_type
1318 FROM IGS_PR_OU_TYPE pot
1319 WHERE pot.progression_outcome_type = p_progression_outcome_type;
1320 BEGIN
1321 -- Set the default message number
1322 p_message_name := null;
1323 IF p_duration_type IS NULL AND
1324 p_duration IS NOT NULL THEN
1325 p_message_name := 'IGS_PR_DU_SET_DTYP_MSET';
1326 RETURN FALSE;
1327 END IF;
1328 IF p_duration_type IS NOT NULL AND
1329 p_duration IS NULL THEN
1330 p_message_name := 'IGS_PR_DUTY_SET_DU_MSET';
1331 RETURN FALSE;
1332 END IF;
1333 IF p_progression_outcome_type IS NULL THEN
1334 RETURN TRUE;
1335 END IF;
1336 OPEN c_pot;
1337 FETCH c_pot INTO v_s_progression_outcome_type;
1338 IF c_pot%NOTFOUND THEN
1339 CLOSE c_pot;
1340 RETURN TRUE;
1341 END IF;
1342 CLOSE c_pot;
1343 IF v_s_progression_outcome_type = cst_suspension AND
1344 p_duration IS NULL THEN
1345 p_message_name := 'IGS_PR_DU_DUTY_SUS';
1346 RETURN FALSE;
1347 END IF;
1348 IF v_s_progression_outcome_type IN (
1349 cst_exclusion,
1350 cst_expulsion,
1351 cst_nopenalty,
1352 cst_ex_fund) AND
1353 p_duration IS NOT NULL THEN
1354 p_message_name := 'IGS_PR_DUTY_PRTY_EXC_NOP';
1355 RETURN FALSE;
1356 END IF;
1357 IF v_s_progression_outcome_type NOT IN (cst_probation, cst_manual) AND
1358 p_duration_type = cst_effective THEN
1359 p_message_name := 'IGS_PR_DTYP_CNTEF_PRO_MAN';
1360 RETURN FALSE;
1361 END IF;
1362 RETURN TRUE;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 IF c_pot%ISOPEN THEN
1366 CLOSE c_pot;
1367 END IF;
1368 RAISE;
1369 END;
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1373 IGS_GE_MSG_STACK.ADD;
1374 App_Exception.Raise_Exception;
1375 END prgp_val_spo_rqrd;
1376
1377 --
1378 -- Validate student progression outcome restricted attendance type
1379 FUNCTION prgp_val_spo_att(
1380 p_progression_outcome_type IN VARCHAR2,
1381 p_restricted_attendance_type IN VARCHAR2,
1382 p_message_name OUT NOCOPY VARCHAR2)
1383 RETURN BOOLEAN IS
1384 gv_other_detail VARCHAR2(255);
1385 BEGIN -- prgp_val_spo_att
1386 -- Validate that if IGS_PR_STDNT_PR_OU.restricted_attendance_type
1387 -- is set that the progression_outcome_type relates to a
1388 -- s_encmb_effect_type of RSTR_AT_TY.
1389 DECLARE
1390 cst_rstr_at_ty CONSTANT VARCHAR(10) := 'RSTR_AT_TY';
1391 v_dummy VARCHAR2(1);
1392 CURSOR c_pot_etde IS
1393 SELECT 'X'
1394 FROM IGS_PR_OU_TYPE pot,
1395 IGS_FI_ENC_DFLT_EFT etde
1396 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
1397 pot.encumbrance_type = etde.encumbrance_type AND
1398 etde.s_encmb_effect_type = cst_rstr_at_ty;
1399 BEGIN
1400 -- Set the default message number
1401 p_message_name := null;
1402 IF p_progression_outcome_type IS NULL THEN
1403 RETURN TRUE;
1404 END IF;
1405 OPEN c_pot_etde;
1406 FETCH c_pot_etde INTO v_dummy;
1407 IF c_pot_etde%NOTFOUND THEN
1408 IF p_restricted_attendance_type IS NOT NULL THEN
1409 CLOSE c_pot_etde;
1410 p_message_name := 'IGS_PR_RSTR_AT_TY';
1411 RETURN FALSE;
1412 END IF;
1413 ELSE
1414 IF p_restricted_attendance_type IS NULL THEN
1415 CLOSE c_pot_etde;
1416 p_message_name := 'IGS_PR_PROU_TYICM_EPOC_RE';
1417 RETURN FALSE;
1418 END IF;
1419 END IF;
1420 CLOSE c_pot_etde;
1421 RETURN TRUE;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 IF c_pot_etde%ISOPEN THEN
1425 CLOSE c_pot_etde;
1426 END IF;
1427 RAISE;
1428 END;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431
1432 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1433 IGS_GE_MSG_STACK.ADD;
1434 App_Exception.Raise_Exception;
1435 END prgp_val_spo_att;
1436
1437 --
1438 -- Validate student progression outcome encumbered course group code
1439 FUNCTION prgp_val_spo_cgr(
1440 p_progression_outcome_type IN VARCHAR2 ,
1441 p_encmb_course_group_cd IN VARCHAR2 ,
1442 p_message_name OUT NOCOPY VARCHAR2 )
1443 RETURN BOOLEAN IS
1444 gv_other_detail VARCHAR2(255);
1445 BEGIN -- prgp_val_spo_cgr
1446 -- Validate that if IGS_PR_STDNT_PR_OU.encmb_course_group_cd
1447 -- is set that the progression_outcome_type relates to a
1448 -- s_encmb_effect_type of EXC_CRS_GP.
1449 DECLARE
1450 cst_exc_crs_gp CONSTANT VARCHAR(10) := 'EXC_CRS_GP';
1451 v_dummy VARCHAR2(1);
1452 CURSOR c_pot_etde IS
1453 SELECT 'X'
1454 FROM IGS_PR_OU_TYPE pot,
1455 IGS_FI_ENC_DFLT_EFT etde
1456 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
1457 pot.encumbrance_type = etde.encumbrance_type AND
1458 etde.s_encmb_effect_type = cst_exc_crs_gp;
1459 BEGIN
1460 -- Set the default message number
1461 p_message_name := null;
1462 IF p_progression_outcome_type IS NULL THEN
1463 RETURN TRUE;
1464 END IF;
1465 OPEN c_pot_etde;
1466 FETCH c_pot_etde INTO v_dummy;
1467 IF c_pot_etde%NOTFOUND THEN
1468 IF p_encmb_course_group_cd IS NOT NULL THEN
1469 CLOSE c_pot_etde;
1470 p_message_name := 'IGS_PR_ENCGP_EXC_CRS_GP';
1471 RETURN FALSE;
1472 END IF;
1473 ELSE
1474 IF p_encmb_course_group_cd IS NULL THEN
1475 CLOSE c_pot_etde;
1476 p_message_name := 'IGS_PR_ENCUM_CGP_MEN_EXC';
1477 RETURN FALSE;
1478 END IF;
1479 END IF;
1480 CLOSE c_pot_etde;
1481 RETURN TRUE;
1482 EXCEPTION
1483 WHEN OTHERS THEN
1484 IF c_pot_etde%ISOPEN THEN
1485 CLOSE c_pot_etde;
1486 END IF;
1487 RAISE;
1488 END;
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491
1492 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1493 IGS_GE_MSG_STACK.ADD;
1494 App_Exception.Raise_Exception;
1495 END prgp_val_spo_cgr;
1496
1497 --
1498 -- Validate student progression outcome restricted enrolment cp's
1499 FUNCTION prgp_val_spo_cp(
1500 p_progression_outcome_type IN VARCHAR2 ,
1501 p_restricted_enrolment_cp IN NUMBER ,
1502 p_message_name OUT NOCOPY VARCHAR2 )
1503 RETURN BOOLEAN IS
1504 gv_other_detail VARCHAR2(255);
1505 BEGIN -- prgp_val_spo_cp
1506 -- Validate that if IGS_PR_STDNT_PR_OU.restricted_enrolment_cp is
1507 -- set that the IGS_PR_OU_TYPE relates to a s_encmb_effect_type of
1508 -- RSTR_GE_CP or RSTR_LE_CP.
1509 DECLARE
1510 cst_rstr_ge_cp CONSTANT VARCHAR(10) := 'RSTR_GE_CP';
1511 cst_rstr_le_cp CONSTANT VARCHAR(10) := 'RSTR_LE_CP';
1512 v_dummy VARCHAR2(1);
1513 CURSOR c_pot_etde IS
1514 SELECT 'X'
1515 FROM IGS_PR_OU_TYPE pot,
1516 IGS_FI_ENC_DFLT_EFT etde
1517 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
1518 pot.encumbrance_type = etde.encumbrance_type AND
1519 etde.s_encmb_effect_type IN (
1520 cst_rstr_ge_cp,
1521 cst_rstr_le_cp);
1522 BEGIN
1523 -- Set the default message number
1524 p_message_name := null;
1525 IF p_progression_outcome_type IS NULL THEN
1526 RETURN TRUE;
1527 END IF;
1528 OPEN c_pot_etde;
1529 FETCH c_pot_etde INTO v_dummy;
1530 IF c_pot_etde%NOTFOUND THEN
1531 IF p_restricted_enrolment_cp IS NOT NULL THEN
1532 CLOSE c_pot_etde;
1533 p_message_name := 'IGS_PR_RSTR_GE_LE_CP';
1534 RETURN FALSE;
1535 END IF;
1536 ELSE
1537 IF p_restricted_enrolment_cp IS NULL THEN
1538 CLOSE c_pot_etde;
1539 p_message_name := 'IGS_PR_RERN_CPO_MEN_URE';
1540 RETURN FALSE;
1541 END IF;
1542 END IF;
1543 CLOSE c_pot_etde;
1544 RETURN TRUE;
1545 EXCEPTION
1546 WHEN OTHERS THEN
1547 IF c_pot_etde%ISOPEN THEN
1548 CLOSE c_pot_etde;
1549 END IF;
1550 RAISE;
1551 END;
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554
1555 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1556 IGS_GE_MSG_STACK.ADD;
1557 App_Exception.Raise_Exception;
1558 END prgp_val_spo_cp;
1559
1560 --
1561 -- Validate student progression outcome duration/duration type changes
1562 FUNCTION prgp_val_spo_drtn(
1563 p_person_id IN NUMBER ,
1564 p_course_cd IN VARCHAR2 ,
1565 p_sequence_number IN NUMBER ,
1566 p_decision_status IN VARCHAR2 ,
1567 p_old_duration IN NUMBER ,
1568 p_new_duration IN NUMBER ,
1569 p_old_duration_type IN VARCHAR2 ,
1570 p_new_duration_type IN VARCHAR2 ,
1571 p_expiry_dt IN DATE ,
1572 p_message_name OUT NOCOPY VARCHAR2 )
1573 RETURN BOOLEAN IS
1574 gv_other_detail VARCHAR2(255);
1575 BEGIN -- prgp_val_spo_drtn
1576 -- If the IGS_PR_STDNT_PR_OU.duration or duration_type is being
1577 -- changed and the decision_status has been set to approved then check that:
1578 -- the expiry_dt has not already passed
1579 -- the new expiry date would not have already passed
1580 DECLARE
1581 cst_approved CONSTANT VARCHAR(10) := 'APPROVED';
1582 cst_expired CONSTANT VARCHAR(10) := 'EXPIRED';
1583 v_expiry_dt IGS_PR_STDNT_PR_OU.expiry_dt%TYPE;
1584 v_apply_automatically_ind
1585 IGS_PR_RU_OU.apply_automatically_ind%TYPE;
1586 CURSOR c_spo_pro IS
1587 SELECT pro.apply_automatically_ind
1588 FROM IGS_PR_STDNT_PR_OU spo,
1589 IGS_PR_RU_OU pro
1590 WHERE spo.person_id = p_person_id AND
1591 spo.course_cd = p_course_cd AND
1592 spo.sequence_number = p_sequence_number AND
1593 pro.progression_rule_cat (+) = spo.progression_rule_cat AND
1594 pro.pra_sequence_number (+) = spo.pro_pra_sequence_number AND
1595 pro.sequence_number (+) = spo.pro_sequence_number;
1596 BEGIN
1597 -- Set the default message number
1598 p_message_name := null;
1599 IF p_person_id IS NULL OR
1600 p_course_cd IS NULL OR
1601 p_sequence_number IS NULL OR
1602 p_decision_status <> cst_approved THEN
1603 RETURN TRUE;
1604 END IF;
1605 IF NVL(p_old_duration, 0) <> NVL(p_new_duration, 0) OR
1606 NVL(p_old_duration_type, 'NULL') <> NVL(p_new_duration_type, 'NULL') THEN
1607 IF TRUNC(NVL(p_expiry_dt, igs_ge_date.igsdate('9999/01/01')))
1608 < TRUNC(SYSDATE) THEN
1609 p_message_name := 'IGS_PR_DUTY_CNTCH_STPR_OAEXP';
1610 RETURN FALSE;
1611 END IF;
1612 IF p_old_duration IS NULL AND
1613 p_old_duration_type IS NULL THEN
1614 OPEN c_spo_pro;
1615 FETCH c_spo_pro INTO v_apply_automatically_ind;
1616 CLOSE c_spo_pro;
1617 ELSE
1618 v_apply_automatically_ind := NULL;
1619 END IF;
1620 IF v_apply_automatically_ind IS NULL OR
1621 v_apply_automatically_ind = 'N' THEN
1622 IF IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
1623 p_person_id,
1624 p_course_cd,
1625 p_sequence_number,
1626 NULL,
1627 v_expiry_dt) = cst_expired THEN
1628 p_message_name := 'IGS_PR_DUTY_CNTCH_STPR_OPEXDT';
1629 RETURN FALSE;
1630 END IF;
1631 END IF;
1632 END IF;
1633 RETURN TRUE;
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 IF c_spo_pro%ISOPEN THEN
1637 CLOSE c_spo_pro;
1638 END IF;
1639 RAISE;
1640 END;
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643
1644 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1645 IGS_GE_MSG_STACK.ADD;
1646 App_Exception.Raise_Exception;
1647 END prgp_val_spo_drtn;
1648
1649 --
1650 -- Validate if IGS_PS_GRP.course_group_cd is closed.
1651 FUNCTION crsp_val_cgr_closed(
1652 p_course_group_cd IN VARCHAR2 ,
1653 p_message_name OUT NOCOPY VARCHAR2 )
1654 RETURN BOOLEAN IS
1655 gv_other_detail VARCHAR2(255);
1656 BEGIN -- crsp_val_cgr_closed
1657 -- Validate if IGS_PS_GRP.course_group_cd is closed.
1658 DECLARE
1659 CURSOR c_cg(
1660 cp_course_group_cd IGS_PS_GRP.course_group_cd%TYPE) IS
1661 SELECT closed_ind
1662 FROM IGS_PS_GRP
1663 WHERE course_group_cd = cp_course_group_cd;
1664 v_closed_ind IGS_PS_GRP.closed_ind%TYPE;
1665 cst_yes CONSTANT CHAR := 'Y';
1666 BEGIN
1667 -- Set the default message number
1668 p_message_name := null;
1669 -- Cursor handling
1670 OPEN c_cg(p_course_group_cd);
1671 FETCH c_cg INTO v_closed_ind;
1672 IF c_cg%NOTFOUND THEN
1673 CLOSE c_cg;
1674 RETURN TRUE;
1675 END IF;
1676 CLOSE c_cg;
1677 IF v_closed_ind = cst_yes THEN
1678 p_message_name := 'IGS_PS_PRGGRP_CODE_CLOSED';
1679 RETURN FALSE;
1680 END IF;
1681 -- Return the default value
1682 RETURN TRUE;
1683 END;
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686
1687 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1688 IGS_GE_MSG_STACK.ADD;
1689 App_Exception.Raise_Exception;
1690 END crsp_val_cgr_closed;
1691
1692 --
1693 -- Validate student progression outcome decision status of APPROVED
1694 FUNCTION prgp_val_spo_approve(
1695 p_person_id IN NUMBER ,
1696 p_course_cd IN VARCHAR2 ,
1697 p_sequence_number IN NUMBER ,
1698 p_progression_outcome_type IN VARCHAR2 ,
1699 p_old_decision_status IN VARCHAR2 ,
1700 p_new_decision_status IN VARCHAR2 ,
1701 p_encmb_course_group_cd IN VARCHAR2 ,
1702 p_restricted_enrolment_cp IN NUMBER ,
1703 p_restricted_attendance_type IN VARCHAR2 ,
1704 p_message_name OUT NOCOPY VARCHAR2 )
1705 RETURN BOOLEAN IS
1706 gv_other_detail VARCHAR2(255);
1707 /*
1708 ||Change History:
1709 || Who When What
1710 ||================================================================================||
1711 || NALKUMAR 19-NOV-2002 Bug NO: 2658550. Modified this function as per the ||
1712 || FA110 PR Enh. Added check related to the FUND holds.||
1713 ||================================================================================||
1714 */
1715 BEGIN
1716 -- prgp_val_spo_approve
1717 -- If the IGS_PR_STDNT_PR_OU.decision_status is being set to approved
1718 -- then check the encmb_dflt_effect_type.s_encmb_effect_type records related
1719 -- to the IGS_PR_OU_TYPE.encumbrance_type defined for the
1720 -- student _progression_outcome.IGS_PR_OU_TYPE.
1721 DECLARE
1722 cst_approved CONSTANT VARCHAR(10) := 'APPROVED';
1723 cst_sus_course CONSTANT VARCHAR(10) := 'SUS_COURSE';
1724 cst_exc_course CONSTANT VARCHAR(10) := 'EXC_COURSE';
1725 cst_exc_crs_us CONSTANT VARCHAR(10) := 'EXC_CRS_US';
1726 cst_exc_crs_u CONSTANT VARCHAR(10) := 'EXC_CRS_U';
1727 cst_excluded CONSTANT VARCHAR(10) := 'EXCLUDED';
1728 cst_rqrd_crs_u CONSTANT VARCHAR(10) := 'RQRD_CRS_U';
1729 cst_required CONSTANT VARCHAR(10) := 'REQUIRED';
1730 cst_rstr_ge_cp CONSTANT VARCHAR(10) := 'RSTR_GE_CP';
1731 cst_rstr_le_cp CONSTANT VARCHAR(10) := 'RSTR_LE_CP';
1732 cst_rstr_at_ty CONSTANT VARCHAR(10) := 'RSTR_AT_TY';
1733 cst_exc_crs_gp CONSTANT VARCHAR(10) := 'EXC_CRS_GP';
1734 cst_ex_sp_awd CONSTANT VARCHAR(10) := 'EX_SP_AWD';
1735 cst_ex_sp_disb CONSTANT VARCHAR(15) := 'EX_SP_DISB';
1736 cst_expired CONSTANT VARCHAR(10) := 'EXPIRED';
1737 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1738 v_dummy VARCHAR2(1);
1739 v_expiry_dt IGS_PR_STDNT_PR_OU.expiry_dt%TYPE;
1740 v_exit BOOLEAN DEFAULT FALSE;
1741 v_apply_automatically_ind VARCHAR2(1);
1742 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1743 CURSOR c_sca IS
1744 SELECT course_attempt_status
1745 FROM IGS_EN_STDNT_PS_ATT
1746 WHERE person_id = p_person_id AND
1747 course_cd = p_course_cd;
1748 CURSOR c_pot_etde IS
1749 SELECT etde.s_encmb_effect_type
1750 FROM IGS_PR_OU_TYPE pot,
1751 IGS_FI_ENC_DFLT_EFT etde
1752 WHERE pot.progression_outcome_type = p_progression_outcome_type AND
1753 pot. encumbrance_type = etde.encumbrance_type;
1754 CURSOR c_spc IS
1755 SELECT 'X'
1756 FROM IGS_PR_STDNT_PR_PS
1757 WHERE person_id = p_person_id AND
1758 spo_course_cd = p_course_cd AND
1759 spo_sequence_number = p_sequence_number;
1760 CURSOR c_spus IS
1761 SELECT 'X'
1762 FROM IGS_PR_SDT_PR_UNT_ST
1763 WHERE person_id = p_person_id AND
1764 course_cd = p_course_cd AND
1765 spo_sequence_number = p_sequence_number;
1766 CURSOR c_spuf IS
1767 SELECT 'X'
1768 FROM IGS_PR_STDNT_PR_FND
1769 WHERE person_id = p_person_id AND
1770 course_cd = p_course_cd AND
1771 spo_sequence_number = p_sequence_number;
1772 CURSOR c_spu_1 IS
1773 SELECT 'X'
1774 FROM IGS_PR_STDNT_PR_UNIT
1775 WHERE person_id = p_person_id AND
1776 course_cd = p_course_cd AND
1777 spo_sequence_number = p_sequence_number AND
1778 s_unit_type = cst_excluded;
1779 CURSOR c_spu_2 IS
1780 SELECT 'X'
1781 FROM IGS_PR_STDNT_PR_UNIT
1782 WHERE person_id = p_person_id AND
1783 course_cd = p_course_cd AND
1784 spo_sequence_number = p_sequence_number AND
1785 s_unit_type = cst_required;
1786 CURSOR c_spo_pro IS
1787 SELECT pro.apply_automatically_ind
1788 FROM IGS_PR_STDNT_PR_OU spo,
1789 IGS_PR_RU_OU pro
1790 WHERE spo.person_id = p_person_id AND
1791 spo.course_cd = p_course_cd AND
1792 spo.sequence_number = p_sequence_number AND
1793 pro.progression_rule_cat (+) = spo.progression_rule_cat AND
1794 pro.pra_sequence_number (+) = spo.pro_pra_sequence_number AND
1795 pro.sequence_number (+) = spo.pro_sequence_number;
1796 BEGIN
1797 -- Set the default message number
1798 p_message_name := null;
1799 IF p_person_id IS NULL OR
1800 p_course_cd IS NULL OR
1801 p_sequence_number IS NULL OR
1802 p_progression_outcome_type IS NULL OR
1803 p_old_decision_status IS NULL OR
1804 p_new_decision_status IS NULL THEN
1805 RETURN TRUE;
1806 END IF;
1807 IF p_new_decision_status = cst_approved THEN
1808 OPEN c_sca;
1809 FETCH c_sca INTO v_course_attempt_status;
1810 IF c_sca%FOUND THEN
1811 CLOSE c_sca;
1812 IF v_course_attempt_status = cst_completed THEN
1813 p_message_name := 'IGS_PR_CNT_APOT_CRAT_HCMT';
1814 RETURN FALSE;
1815 END IF;
1816 ELSE
1817 CLOSE c_sca;
1818 END IF;
1819 FOR v_pot_etde_rec IN c_pot_etde LOOP
1820 -- If related encumbrance effects contain SUS_COURSE or EXC_COURSE,
1821 -- then at least one record must exist in the IGS_PR_STDNT_PR_PS table
1822 IF v_pot_etde_rec.s_encmb_effect_type IN (
1823 cst_sus_course,
1824 cst_exc_course) THEN
1825 OPEN c_spc;
1826 FETCH c_spc INTO v_dummy;
1827 IF c_spc%NOTFOUND THEN
1828 CLOSE c_spc;
1829 p_message_name := 'IGS_PR_AT_OSTPR_CR_MCR_CSU_EXC';
1830 v_exit := TRUE;
1831 EXIT;
1832 END IF;
1833 CLOSE c_spc;
1834 END IF;
1835 -- If related encumbrance effects contain EXC_CRS_US, then at least one
1836 -- record must exist in the IGS_PR_SDT_PR_RU_CK table
1837 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_us THEN
1838 OPEN c_spus;
1839 FETCH c_spus INTO v_dummy;
1840 IF c_spus%NOTFOUND THEN
1841 CLOSE c_spus;
1842 p_message_name := 'IGS_PR_AT_OSTPR_CR_MCR_UST_EXC';
1843 v_exit := TRUE;
1844 EXIT;
1845 END IF;
1846 CLOSE c_spus;
1847 END IF;
1848 -- If related encumbrance effects contain EXC_CRS_U, then a record must
1849 -- exist in the IGS_PR_STDNT_PR_UNIT table with s_unit_type of 'EXCLUDE
1850 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_u THEN
1851 OPEN c_spu_1;
1852 FETCH c_spu_1 INTO v_dummy;
1853 IF c_spu_1%NOTFOUND THEN
1854 CLOSE c_spu_1;
1855 p_message_name := 'IGS_PR_ATON_STPR_UREC_MCR_UEX';
1856 v_exit := TRUE;
1857 EXIT;
1858 END IF;
1859 CLOSE c_spu_1;
1860 END IF;
1861 -- If related encumbrance effects contain RQRD_CRS_U, then a record must
1862 -- exist in the IGS_PR_STDNT_PR_UNIT table with s_unit_type of 'REQUIRED'
1863 IF v_pot_etde_rec.s_encmb_effect_type = cst_rqrd_crs_u THEN
1864 OPEN c_spu_2;
1865 FETCH c_spu_2 INTO v_dummy;
1866 IF c_spu_2%NOTFOUND THEN
1867 CLOSE c_spu_2;
1868 p_message_name := 'IGS_PR_ATON_STPR_UREC_MCR_URE';
1869 v_exit := TRUE;
1870 EXIT;
1871 END IF;
1872 CLOSE c_spu_2;
1873 END IF;
1874 -- If related encumbrance effects contain RSTR_{GE,LE}_CP then
1875 -- spo.restricted_enrolment_cp must be set
1876 IF v_pot_etde_rec.s_encmb_effect_type IN (
1877 cst_rstr_ge_cp,
1878 cst_rstr_le_cp) AND
1879 NVL(p_restricted_enrolment_cp, 0) = 0 THEN
1880 p_message_name := 'IGS_PR_RERN_CPO_MEN_URE';
1881 v_exit := TRUE;
1882 EXIT;
1883 END IF;
1884 -- If related encumbrance effects contain RSTR_AT_TY, then
1885 -- spo.restricted_attendance_type must be set
1886 IF v_pot_etde_rec.s_encmb_effect_type = cst_rstr_at_ty AND
1887 p_restricted_attendance_type IS NULL THEN
1888 p_message_name := 'IGS_PR_REATY_MEN_PROT_ATRES';
1889 v_exit := TRUE;
1890 EXIT;
1891 END IF;
1892 -- If related encumbrance effects contain EXC_CRS_GP, then
1893 -- spo.encmb_course_group_cd must be set
1894 IF v_pot_etde_rec.s_encmb_effect_type = cst_exc_crs_gp AND
1895 p_encmb_course_group_cd IS NULL THEN
1896 p_message_name := 'IGS_PR_ENCUM_CGP_MEN_EXC';
1897 v_exit := TRUE;
1898 EXIT;
1899 END IF;
1900
1901 --
1902 -- Start of new code as per the FA110 TD. Bug# 2658550.
1903 --
1904 -- If related encumbrance effects contain 'EX_SP_AWD' or 'EX_SP_DISB', then
1905 -- a record must exist in the IGS_PR_STDNT_PR_FND table
1906 --
1907 IF v_pot_etde_rec.s_encmb_effect_type IN (cst_ex_sp_awd,cst_ex_sp_disb) THEN
1908 OPEN c_spuf;
1909 FETCH c_spuf INTO v_dummy;
1910 IF c_spuf%NOTFOUND THEN
1911 CLOSE c_spuf;
1912 p_message_name := 'IGS_PR_ATON_STPR_FNEC_MCR';
1913 v_exit := TRUE;
1914 EXIT;
1915 END IF;
1916 CLOSE c_spuf;
1917 END IF;
1918 --
1919 -- End of new code as per the FA110 TD. Bug# 2658550.
1920 --
1921 END LOOP;
1922 IF v_exit THEN
1923 RETURN FALSE;
1924 END IF;
1925 OPEN c_spo_pro;
1926 FETCH c_spo_pro INTO v_apply_automatically_ind;
1927 IF c_spo_pro%FOUND AND
1928 (v_apply_automatically_ind IS NULL OR
1929 v_apply_automatically_ind = 'N') THEN
1930 CLOSE c_spo_pro;
1931 -- The outcome cannot be approved if it will already have expired
1932 IF IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
1933 p_person_id,
1934 p_course_cd,
1935 p_sequence_number,
1936 NULL,
1937 v_expiry_dt) = cst_expired THEN
1938 p_message_name := 'IGS_PR_OUCNT_AP_ALEXP';
1939 RETURN FALSE;
1940 END IF;
1941 ELSE
1942 CLOSE c_spo_pro;
1943 END IF;
1944 END IF;
1945 RETURN TRUE;
1946 EXCEPTION
1947 WHEN OTHERS THEN
1948 IF c_pot_etde%ISOPEN THEN
1949 CLOSE c_pot_etde;
1950 END IF;
1951 IF c_spc%ISOPEN THEN
1952 CLOSE c_spc;
1953 END IF;
1954 IF c_spus%ISOPEN THEN
1955 CLOSE c_spus;
1956 END IF;
1957 IF c_spu_1%ISOPEN THEN
1958 CLOSE c_spu_1;
1959 END IF;
1960 IF c_spu_2%ISOPEN THEN
1961 CLOSE c_spu_2;
1962 END IF;
1963 IF c_spo_pro%ISOPEN THEN
1964 CLOSE c_spo_pro;
1965 END IF;
1966 IF c_sca%ISOPEN THEN
1967 CLOSE c_sca;
1968 END IF;
1969 RAISE;
1970 END;
1971 EXCEPTION
1972 WHEN OTHERS THEN
1973
1974 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1975 IGS_GE_MSG_STACK.ADD;
1976 App_Exception.Raise_Exception;
1977 END prgp_val_spo_approve;
1978
1979 --
1980 -- Validate student progression outcome applied date
1981 FUNCTION prgp_val_spo_aply_dt(
1982 p_person_id IN NUMBER ,
1983 p_course_cd IN VARCHAR2 ,
1984 p_old_applied_dt IN DATE ,
1985 p_new_applied_dt IN DATE ,
1986 p_decision_status IN VARCHAR2 ,
1987 p_decision_dt IN DATE ,
1988 p_show_cause_expiry_dt IN DATE ,
1989 p_show_cause_outcome_dt IN DATE ,
1990 p_appeal_expiry_dt IN DATE ,
1991 p_appeal_outcome_dt IN DATE ,
1992 p_message_name OUT NOCOPY VARCHAR2 )
1993 RETURN BOOLEAN IS
1994 gv_other_detail VARCHAR2(255);
1995 BEGIN -- Validate that if IGS_PR_STDNT_PR_OU.applied_dt.
1996 -- Cannot set applied_dt unless decision_status is APPROVED
1997 -- Cannot unset applied_dt unless decision_status is CANCELLED or REMOVED
1998 -- The applied_dt cannot be a future date
1999 -- The applied_dt must be on or after the decision_dt
2000 -- If 'applicable' apply_before_show_ind is N and not beyond
2001 --show_cause_expiry_dt then cannot set applied_dt
2002 -- If 'applicable' apply_before_appeal_ind is N and not beyond
2003 --show_cause_expiry_dt then cannot set applied_dt
2004 DECLARE
2005 cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
2006 cst_removed CONSTANT VARCHAR2(10) := 'REMOVED';
2007 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
2008 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
2009 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
2010 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
2011 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
2012 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
2013 v_show_cause_cutoff_dt_alias IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
2014 v_appeal_cutoff_dt_alias IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
2015 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
2016 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
2017 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
2018 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
2019 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
2020 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
2021 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
2022 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
2023 v_outcome_check_type VARCHAR2(10);
2024 CURSOR c_sca IS
2025 SELECT sca.version_number
2026 FROM IGS_EN_STDNT_PS_ATT sca
2027 WHERE sca.person_id = p_person_id AND
2028 sca.course_cd = p_course_cd;
2029 BEGIN
2030 -- Set the default message number
2031 p_message_name := null;
2032 IF p_person_id IS NULL OR
2033 p_course_cd IS NULL THEN
2034 p_message_name := null;
2035 RETURN TRUE;
2036 END IF;
2037 IF TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('0001/01/01'))) <>
2038 TRUNC(NVL(p_old_applied_dt, igs_ge_date.igsdate('0001/01/01'))) THEN
2039 IF p_new_applied_dt IS NULL THEN
2040 IF p_decision_status NOT IN (
2041 cst_cancelled,
2042 cst_removed) THEN
2043 p_message_name := 'IGS_PR_CNT_APDT_UNDE_ST_CNREM';
2044 RETURN FALSE;
2045 END IF;
2046 ELSE
2047 IF p_decision_status <> cst_approved THEN
2048 p_message_name := 'IGS_PR_CNT_APDT_UNL_DEDT_AP';
2049 RETURN FALSE;
2050 END IF;
2051 IF TRUNC(p_new_applied_dt) > TRUNC(SYSDATE) Then
2052 p_message_name := 'IGS_PR_APDT_CNT_FUDT';
2053 RETURN FALSE;
2054 END IF;
2055 END IF;
2056 IF TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01'))) <
2057 TRUNC(NVL(p_decision_dt, igs_ge_date.igsdate('0001/01/01'))) AND
2058 TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('0001/01/01'))) <>
2059 TRUNC(igs_ge_date.igsdate('0001/01/01')) THEN
2060 p_message_name := 'IGS_PR_APDT_MST_ONAF_DEDT';
2061 RETURN FALSE;
2062 END IF;
2063 OPEN c_sca;
2064 FETCH c_sca INTO v_version_number;
2065 CLOSE c_sca;
2066 IGS_PR_GEN_003.IGS_PR_GET_CONFIG_PARM (
2067 p_course_cd,
2068 v_version_number,
2069 v_apply_start_dt_alias,
2070 v_apply_end_dt_alias,
2071 v_end_benefit_dt_alias,
2072 v_end_penalty_dt_alias,
2073 v_show_cause_cutoff_dt_alias,
2074 v_appeal_cutoff_dt_alias,
2075 v_show_cause_ind,
2076 v_apply_before_show_ind,
2077 v_appeal_ind,
2078 v_apply_before_appeal_ind,
2079 v_count_sus_in_time_ind,
2080 v_count_exc_in_time_ind,
2081 v_calculate_wam_ind,
2082 v_calculate_gpa_ind,
2083 v_outcome_check_type);
2084 IF v_show_cause_ind = 'Y' AND
2085 v_apply_before_show_ind = 'N' AND
2086 p_show_cause_outcome_dt IS NULL THEN
2087 IF NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01')) <>
2088 igs_ge_date.igsdate('0001/01/01') AND
2089 (TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01'))) <
2090 TRUNC(NVL(p_show_cause_expiry_dt,
2091 igs_ge_date.igsdate('0001/01/01')))) THEN
2092 p_message_name := 'IGS_PR__APDT_CNT_BFSCA_EXDT';
2093 RETURN FALSE;
2094 END IF;
2095 END IF;
2096 IF v_appeal_ind = 'Y' AND
2097 v_apply_before_appeal_ind ='N' AND
2098 p_appeal_outcome_dt IS NULL THEN
2099 IF NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01')) <>
2100 igs_ge_date.igsdate('0001/01/01') AND
2101 (TRUNC(NVL(p_new_applied_dt, igs_ge_date.igsdate('9999/01/01'))) <
2102 TRUNC(NVL(p_appeal_expiry_dt, igs_ge_date.igsdate('0001/01/01')))) THEN
2103 p_message_name := 'IGS_PR_APDT_CNT_BFAP_EXP_APNT';
2104 RETURN FALSE;
2105 END IF;
2106 END IF;
2107 END IF;
2108 RETURN TRUE;
2109 EXCEPTION
2110 WHEN OTHERS THEN
2111 IF c_sca%ISOPEN THEN
2112 CLOSE c_sca;
2113 END IF;
2114 RAISE;
2115 END;
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118
2119 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2120 IGS_GE_MSG_STACK.ADD;
2121 App_Exception.Raise_Exception;
2122 END prgp_val_spo_aply_dt;
2123
2124 --
2125 -- Validate student progression outcome show cause
2126 FUNCTION prgp_val_spo_cause(
2127 p_person_id IN NUMBER ,
2128 p_course_cd IN VARCHAR2 ,
2129 p_progression_rule_cat IN VARCHAR2 ,
2130 p_pro_pra_sequence_number IN NUMBER ,
2131 p_pro_sequence_number IN NUMBER ,
2132 p_show_cause_dt IN DATE ,
2133 p_show_cause_expiry_dt IN DATE ,
2134 p_show_cause_outcome_dt IN DATE ,
2135 p_show_cause_outcome_type IN VARCHAR2 ,
2136 p_message_name OUT NOCOPY VARCHAR2 )
2137 RETURN BOOLEAN IS
2138 gv_other_detail VARCHAR2(255);
2139 BEGIN -- prgp_val_spo_cause
2140 -- Validate the IGS_PR_STDNT_PR_OU show cause details.
2141 -- If show cause not allowed then show_cause_expiry_dt, show_cause_dt,
2142 -- show_cause_outcome_dt and show_cause_outcome_type cannot be set.
2143 DECLARE
2144 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
2145 CURSOR c_sca IS
2146 SELECT sca.version_number
2147 FROM IGS_EN_STDNT_PS_ATT sca
2148 WHERE sca.person_id = p_person_id AND
2149 sca.course_cd = p_course_cd;
2150 BEGIN
2151 p_message_name := null;
2152 -- -- Check parameters and test if any of the show cause parameters are set.
2153 IF p_person_id IS NULL OR
2154 p_course_cd IS NULL OR
2155 (p_show_cause_dt IS NULL AND
2156 p_show_cause_expiry_dt IS NULL AND
2157 p_show_cause_outcome_dt IS NULL AND
2158 p_show_cause_outcome_type IS NULL) THEN
2159 RETURN TRUE;
2160 END IF;
2161 OPEN c_sca;
2162 FETCH c_sca INTO v_version_number;
2163 CLOSE c_sca;
2164 IF IGS_PR_GEN_005.IGS_PR_GET_CAUSE_ALWD(
2165 p_progression_rule_cat,
2166 p_pro_pra_sequence_number,
2167 p_pro_sequence_number,
2168 p_course_cd,
2169 v_version_number) = 'N' THEN
2170 p_message_name := 'IGS_PR_SHCA_DTCNT_SHCA_NTALD';
2171 RETURN FALSE;
2172 END IF;
2173 RETURN TRUE;
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 IF c_sca%ISOPEN THEN
2177 CLOSE c_sca;
2178 END IF;
2179 RAISE;
2180 END;
2181 EXCEPTION
2182 WHEN OTHERS THEN
2183
2184 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2185 IGS_GE_MSG_STACK.ADD;
2186 App_Exception.Raise_Exception;
2187 END prgp_val_spo_cause;
2188
2189 --
2190 -- Validate student progression outcome appeal
2191 FUNCTION prgp_val_spo_appeal(
2192 p_person_id IN NUMBER ,
2193 p_course_cd IN VARCHAR2 ,
2194 p_progression_rule_cat IN VARCHAR2 ,
2195 p_pro_pra_sequence_number IN NUMBER ,
2196 p_pro_sequence_number IN NUMBER ,
2197 p_appeal_dt IN DATE ,
2198 p_appeal_expiry_dt IN DATE ,
2199 p_appeal_outcome_dt IN DATE ,
2200 p_appeal_outcome_type IN VARCHAR2 ,
2201 p_message_name OUT NOCOPY VARCHAR2 )
2202 RETURN BOOLEAN IS
2203 gv_other_detail VARCHAR2(255);
2204 BEGIN -- prgp_val_spo_appeal
2205 -- Validate the IGS_PR_STDNT_PR_OU appeal details.
2206 -- If appeal not allowed then appeal_expiry_dt, appeal_dt,
2207 -- appeal_outcome_dt and appeal_outcome_type cannot be set.
2208 DECLARE
2209 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
2210 CURSOR c_sca IS
2211 SELECT sca.version_number
2212 FROM IGS_EN_STDNT_PS_ATT sca
2213 WHERE sca.person_id = p_person_id AND
2214 sca.course_cd = p_course_cd;
2215 BEGIN
2216 p_message_name := null;
2217 IF p_person_id IS NULL OR
2218 p_course_cd IS NULL OR
2219 (p_appeal_dt IS NULL AND
2220 p_appeal_expiry_dt IS NULL AND
2221 p_appeal_outcome_dt IS NULL AND
2222 p_appeal_outcome_type IS NULL) THEN
2223 RETURN TRUE;
2224 END IF;
2225 OPEN c_sca;
2226 FETCH c_sca INTO v_version_number;
2227 CLOSE c_sca;
2228 IF IGS_PR_GEN_005.IGS_PR_GET_APPEAL_ALWD(
2229 p_progression_rule_cat,
2230 p_pro_pra_sequence_number,
2231 p_pro_sequence_number,
2232 p_course_cd,
2233 v_version_number) = 'N' THEN
2234 p_message_name := 'IGS_PR_APDT_CNT_APNT_AL';
2235 RETURN FALSE;
2236 END IF;
2237 RETURN TRUE;
2238 EXCEPTION
2239 WHEN OTHERS THEN
2240 IF c_sca%ISOPEN THEN
2241 CLOSE c_sca;
2242 END IF;
2243 RAISE;
2244 END;
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247
2248 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2249 IGS_GE_MSG_STACK.ADD;
2250 App_Exception.Raise_Exception;
2251 END prgp_val_spo_appeal;
2252
2253 --
2254 -- Validate student progression outcome outcome type is not being changed
2255 FUNCTION prgp_val_spo_pot(
2256 p_old_progression_outcome_type IN VARCHAR2 ,
2257 p_new_progression_outcome_type IN VARCHAR2 ,
2258 p_message_name OUT NOCOPY VARCHAR2 )
2259 RETURN BOOLEAN IS
2260 gv_other_detail VARCHAR2(255);
2261 BEGIN -- prgp_val_spo_pot
2262 -- Prevent changes to IGS_PR_STDNT_PR_OU.IGS_PR_OU_TYPE
2263 p_message_name := null;
2264 IF p_old_progression_outcome_type IS NULL OR
2265 p_new_progression_outcome_type IS NULL THEN
2266 RETURN TRUE;
2267 END IF;
2268 IF p_old_progression_outcome_type <> p_new_progression_outcome_type THEN
2269 p_message_name := 'IGS_PR_PROUT_TY_CNT_ALT';
2270 RETURN FALSE;
2271 END IF;
2272 RETURN TRUE;
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2276 IGS_GE_MSG_STACK.ADD;
2277 App_Exception.Raise_Exception;
2278 END prgp_val_spo_pot;
2279
2280 --
2281 -- Validate student progression outcome expiry date
2282 FUNCTION prgp_val_spo_exp_dt(
2283 p_expiry_dt IN DATE ,
2284 p_message_name OUT NOCOPY VARCHAR2 )
2285 RETURN BOOLEAN IS
2286 gv_other_detail VARCHAR2(255);
2287 BEGIN -- prgp_val_spo_exp_dt
2288 -- Validate that the IGS_PR_STDNT_PR_OU expiry_dt
2289 -- is not future dated. FORM ONLY!!!
2290 BEGIN
2291 -- Set the default message number
2292 p_message_name := null;
2293 IF p_expiry_dt IS NULL THEN
2294 RETURN TRUE;
2295 END IF;
2296 IF TRUNC(p_expiry_dt) > TRUNC(SYSDATE) THEN
2297 p_message_name := 'IGS_PR_EXPDT_CNT_MAN_STFU';
2298 RETURN FALSE;
2299 END IF;
2300 RETURN TRUE;
2301 END;
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304
2305 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2306 IGS_GE_MSG_STACK.ADD;
2307 App_Exception.Raise_Exception;
2308 END prgp_val_spo_exp_dt;
2309 END igs_pr_val_spo;
[Home] [Help]