1 PACKAGE BODY IGS_EN_VAL_SCA AS
2 /* $Header: IGSEN61B.pls 120.12 2006/09/05 13:24:05 bdeviset ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --shtatiko 08-MAR-2004 Enh# 3167098, Removed finp_audit_fee_cat procedure.
7 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
8 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
9 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
10 --vchappid 28-Nov-01 Enh Bug No: 2122257, Added new procedure finp_audit_fee_cat
11 --smadathi 29-AUG-2001 Bug No. 1956374 .The function genp_val_sdtt_sess removed
12 --kkillams 11-11-2002 As part of Legacy Build bug no:2661533,
13 -- New parameter p_legacy is added to following functions
14 -- enrp_val_sca_lapse,enrp_val_sca_dr,enrp_val_sca_discont.
15 --amuthu 06-JAN-03 As part of Legacy Build bug no:2736125, changed the
16 -- the assignment of p_message_name in procedure enrp_val_sca_discont
17 -- also removed self reference in the package
18 --sarakshi 24-Feb-2003 Enh#2797116,modified cursor c_coo in enrp_val_coo_att function to include delete_flag
19 -- check in the where clause
20 --ptandon 18-Feb-2004 In the function resp_val_ca_dtl_comp, modified the call to function
21 -- igs_re_val_rsup.resp_val_rsup_perc to pass 'N' for the parameter
22 -- p_val_funding_perc_ind so that the validation for funding percentage
23 -- to be 100% doesn't take place. Bug# 3360665.
24 -- smaddali modified procedure enrp_val_sca_comm For Bug 3853476
25 -- amuthu 21-NOV-2004 Mofied the enrp_val_sca_comm as part of Program Transfer Build.
26 -- add logic to check if the commencement date is earlier than the
27 -- earlier end date of all term calendar in which there is an
28 -- an active unit attempt.
29 -- bdeviset 22-Dec-2004 Modifed cursor c_sct and status_date is used instead of transfer_dt
30 -- in enrp_val_sca_dr,enrp_val_sca_discont as part Bug#4083015.
31 -- ctyagi 30-Aug-2005 Added function handle_rederive_prog_att as a part of EN319 Build
32 --ckasu 02-May-2006 Modified as a part of bug#5191592
33 -- bdeviset 22-Aug-2006 Bug# 5507279.In Procedure admp_val_ca_comm_val Made the error message
34 -- IGS_RE_COMEN_DT_GE_ADM_ST_DT to warning.
35 -------------------------------------------------------------------------------------------
36 --msrinivi 27 Aug,2001 Bug 1956374 Removed duplicate func finp_val_fc_closed
37 -- bug id : 1956374
38 -- sjadhav , 28-aug-2001
39 -- removed function ENRP_VAL_SCA_TRNSFR
40 --
41
42 FUNCTION enrf_val_sua_term_sca_comm(
43 p_person_id IN NUMBER,
44 p_course_cd IN VARCHAR2,
45 p_commencement_dt IN DATE,
46 p_message_name OUT NOCOPY VARCHAR2
47 ) RETURN BOOLEAN;
48
49 -- Validate candidature proposed commencement date.
50 FUNCTION admp_val_ca_comm(
51 p_person_id IN NUMBER ,
52 p_course_cd IN VARCHAR2 ,
53 p_crv_version_number IN NUMBER ,
54 p_acai_admission_appl_number IN NUMBER ,
55 p_acai_nominated_course_cd IN VARCHAR2 ,
56 p_acai_sequence_number IN NUMBER ,
57 p_adm_outcome_status IN VARCHAR2 ,
58 p_commencement_dt IN DATE ,
59 p_min_submission_dt IN DATE ,
60 p_parent IN VARCHAR2 ,
61 p_ca_sequence_number IN OUT NOCOPY NUMBER ,
62 p_candidature_exists_ind OUT NOCOPY VARCHAR2 ,
63 p_message_name OUT NOCOPY VARCHAR2)
64 RETURN BOOLEAN AS
65 BEGIN -- admp_val_ca_comm
66 -- This module validates IGS_AD_PS_APPL_INST.prpsd_commencement_dt
67 -- in the context of research candidature.
68 DECLARE
69 cst_sca CONSTANT VARCHAR2(10) := 'SCA';
70 cst_acai CONSTANT VARCHAR2(10) := 'ACAI';
71 cst_ca CONSTANT VARCHAR2(10) := 'CA';
72 cst_research CONSTANT VARCHAR2(10) := 'RESEARCH';
73 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
74 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
75 v_message_name varchar2(30) ;
76 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
77 v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE;
78 v_s_adm_outcome_status IGS_AD_PS_APPL_INST.adm_outcome_status%TYPE;
79 CURSOR c_ca IS
80 SELECT ca.sequence_number,
81 ca.min_submission_dt
82 FROM IGS_RE_CANDIDATURE ca
83 WHERE ca.person_id = p_person_id AND (
84 (p_parent = cst_SCA AND
85 ca.sca_course_cd = p_course_cd) OR
86 (p_parent = cst_ACAI and
87 ca.acai_admission_appl_number = p_acai_admission_appl_number AND
88 ca.acai_nominated_course_cd = p_acai_nominated_course_cd AND
89 ca.acai_sequence_number = p_acai_sequence_number));
90 v_cty_res_typ_ind IGS_PS_TYPE.research_type_ind%TYPE;
91 CURSOR c_crv_cty IS
92 SELECT cty.research_type_ind
93 FROM IGS_PS_VER crv,
94 IGS_PS_TYPE cty
95 WHERE crv.course_cd = p_course_cd AND
96 crv.version_number = p_crv_version_number AND
97 crv.course_type = cty.course_type;
98 v_aa_apcs_exists VARCHAR2(1);
99 CURSOR c_aa_apcs IS
100 SELECT 'x'
101 FROM IGS_AD_APPL aa,
102 IGS_AD_PRCS_CAT_STEP apcs
103 WHERE aa.person_id = p_person_id AND
104 aa.admission_appl_number = p_acai_admission_appl_number AND
105 aa.admission_cat = apcs.admission_cat AND
106 aa.s_admission_process_type = apcs.s_admission_process_type AND
107 apcs.s_admission_step_type = cst_research AND
108 apcs.mandatory_step_ind = 'Y' AND
109 apcs.step_group_type <> 'TRACK'; --2402377
110 BEGIN
111 -- Set the defaults
112 p_message_name := null;
113 p_candidature_exists_ind := 'Y';
114 IF p_parent IN(cst_sca,cst_acai) THEN
115 OPEN c_ca;
116 FETCH c_ca INTO v_ca_sequence_number,
117 v_min_submission_dt;
118 IF c_ca%NOTFOUND THEN
119 CLOSE c_ca;
120 p_candidature_exists_ind := 'N';
121 RETURN TRUE;
122 END IF;
123 CLOSE c_ca;
124 p_ca_sequence_number := v_ca_sequence_number;
125 ELSE --p_parent N ...
126 v_min_submission_dt := p_min_submission_dt;
127 v_ca_sequence_number := p_ca_sequence_number;
128 END IF; -- p_parent IN ...
129 --Validate commencement date against minimum submission date
130 IF p_commencement_dt >= v_min_submission_dt THEN
131 p_message_name := 'IGS_RE_COMEN_DT_CANT_GE_SUBDT';
132 RETURN FALSE;
133 END IF;
134 IF v_ca_sequence_number IS NOT NULL THEN
135 -- Get system admission outcome status
136 IF p_adm_outcome_status IS NULL THEN
137 v_s_adm_outcome_status := NULL;
138 ELSE
139 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(
140 p_adm_outcome_status);
141 END IF;
142 IF p_parent = 'SCA' OR
143 (v_s_adm_outcome_status IS NOT NULL AND
144 v_s_adm_outcome_status IN (cst_offer,cst_cond_offer)) THEN
145 -- Validate that at least one research principal supervisor
146 -- exists on this date
147 -- If candidature is required by the course type
148 -- or admission course application offer
149 OPEN c_crv_cty;
150 FETCH c_crv_cty INTO v_cty_res_typ_ind;
151 IF c_crv_cty%NOTFOUND THEN
152 CLOSE c_crv_cty;
153 RETURN TRUE;
154 END IF;
155 CLOSE c_crv_cty;
156 IF v_cty_res_typ_ind = 'N' THEN
157 IF p_acai_admission_appl_number IS NOT NULL THEN
158 OPEN c_aa_apcs;
159 FETCH c_aa_apcs INTO v_aa_apcs_exists;
160 IF c_aa_apcs%NOTFOUND THEN
161 CLOSE c_aa_apcs;
162 RETURN TRUE;
163 END IF;
164 CLOSE c_aa_apcs;
165 ELSE
166 --Supervisor validation not required
167 RETURN TRUE;
168 END IF;
169 END IF; -- v_cty_res_type_ind
170 IF IGS_RE_VAL_RSUP.resp_val_rsup_princ(
171 p_person_id,
172 v_ca_sequence_number,
173 p_commencement_dt,
174 p_commencement_dt,
175 p_parent,
176 v_message_name) = FALSE THEN
177 p_message_name := v_message_name;
178 RETURN FALSE;
179 END IF;
180 END IF;
181 END IF;-- v_ca_sequence_number
182 -- Return the default value
183 RETURN TRUE;
184 EXCEPTION
185 WHEN OTHERS THEN
186 IF c_ca%ISOPEN THEN
187 CLOSE c_ca;
188 END IF;
189 IF c_crv_cty%ISOPEN THEN
190 CLOSE c_crv_cty;
191 END IF;
192 IF c_aa_apcs%ISOPEN THEN
193 CLOSE c_aa_apcs;
194 END IF;
195 RAISE;
196 END;
197 EXCEPTION
198 WHEN OTHERS THEN
199 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
200 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.admp_val_ca_comm');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END admp_val_ca_comm;
204 --
205 -- Validate candidature proposed commencement date value.
206 FUNCTION admp_val_ca_comm_val(
207 p_person_id IN NUMBER ,
208 p_acai_admission_appl_number IN NUMBER ,
209 p_acai_nominated_course_cd IN VARCHAR2 ,
210 p_acai_sequence_number IN NUMBER ,
211 p_adm_cal_type IN VARCHAR2 ,
212 p_adm_ci_sequence_number IN NUMBER ,
213 p_course_start_dt IN DATE ,
214 p_prpsd_commencement_dt IN DATE ,
215 p_parent IN VARCHAR2 ,
216 p_message_name OUT NOCOPY VARCHAR2)
217 RETURN BOOLEAN AS
218 BEGIN -- admp_val_ca_comm_val
219 -- This modules validates IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
220 -- Validations are:
221 -- * Prpsd_commencement_dt must be greater than the earlier of the course
222 -- start date or the admission academic period earliest research start date.
223 -- * Warn if the Prpsd_commencement_dt is prior to passed census dates for
224 -- the admission academic period.
225 DECLARE
226 cst_ca CONSTANT VARCHAR2(10):='CA';
227 cst_teaching CONSTANT IGS_CA_TYPE.s_cal_cat%TYPE := 'TEACHING';
228 v_course_start_dt IGS_AD_PS_APPL_INST.prpsd_commencement_dt%TYPE;
229 v_adm_cal_type IGS_AD_PS_APPL_INST_APLINST_V.adm_cal_type%TYPE;
230 v_adm_ci_sequence_number IGS_AD_PS_APPL_INST_APLINST_V.adm_ci_sequence_number%TYPE;
231 CURSOR c_acaiv IS
232 SELECT acaiv.adm_cal_type,
233 acaiv.adm_ci_sequence_number
234 FROM IGS_AD_PS_APPL_INST_APLINST_V acaiv
235 WHERE acaiv.person_id = p_person_id AND
236 acaiv.admission_appl_number = p_acai_admission_appl_number AND
237 acaiv.nominated_course_cd = p_acai_nominated_course_cd AND
238 acaiv.sequence_number = p_acai_sequence_number;
239 v_research_start_dt IGS_CA_DA_INST_V.alias_val%TYPE;
240 v_cal_type IGS_CA_DA_INST_V.cal_type%TYPE;
241 v_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE;
242 CURSOR c_cir_cat_daiv_srcc IS
243 SELECT daiv.alias_val,
244 daiv.cal_type,
245 daiv.ci_sequence_number
246 FROM IGS_CA_INST_REL cir,
247 IGS_CA_TYPE cat,
248 IGS_CA_DA_INST_V daiv,
249 IGS_RE_S_RES_CAL_CON srcc
250 WHERE cir.sub_cal_type = v_adm_cal_type AND
251 cir.sub_ci_sequence_number = v_adm_ci_sequence_number AND
252 cir.sup_cal_type = cat.cal_type AND
253 cat.s_cal_cat = cst_teaching AND
254 cir.sup_cal_type = daiv.cal_type AND
255 cir.sup_ci_sequence_number = daiv.ci_sequence_number AND
256 daiv.dt_alias = srcc.effective_strt_dt_alias AND
257 srcc.s_control_num = 1
258 ORDER BY daiv.alias_val ASC;
259 v_ccds_exists VARCHAR2(1);
260 CURSOR c_cir_cat_daiv_sgcc IS
261 SELECT 'x'
262 FROM IGS_CA_INST_REL cir,
263 IGS_CA_TYPE cat,
264 IGS_CA_DA_INST_V daiv,
265 IGS_GE_S_GEN_CAL_CON sgcc
266 WHERE cir.sub_cal_type = v_adm_cal_type AND
267 cir.sub_ci_sequence_number = v_adm_ci_sequence_number AND
268 cir.sup_cal_type = cat.cal_type AND
269 cat.s_cal_cat = cst_teaching AND
270 cir.sup_cal_type = daiv.cal_type AND
271 cir.sup_ci_sequence_number = daiv.ci_sequence_number AND
272 daiv.dt_alias = sgcc.census_dt_alias AND
273 sgcc.s_control_num = 1 AND
274 daiv.alias_val < v_course_start_dt AND
275 daiv.alias_val > p_prpsd_commencement_dt;
276 BEGIN
277 -- Set the default message number
278 p_message_name := null;
279 IF p_prpsd_commencement_dt IS NOT NULL THEN
280 --Validate commencment_dt value
281 IF p_parent = cst_ca THEN
282 -- get admission period details
283 OPEN c_acaiv;
284 FETCH c_acaiv INTO
285 v_adm_cal_type,
286 v_adm_ci_sequence_number;
287 IF c_acaiv%NOTFOUND THEN
288 CLOSE c_acaiv;
289 RETURN TRUE;
290 END IF;
291 CLOSE c_acaiv;
292 ELSE -- p_parent
293 v_adm_cal_type := p_adm_cal_type;
294 v_adm_ci_sequence_number := p_adm_ci_sequence_number;
295 END IF;--p_parent
296 -- validate against course start date
297 IF p_course_start_dt IS NULL THEN
298 v_course_start_dt := IGS_AD_GEN_005.ADMP_GET_CRV_STRT_DT(
299 v_adm_cal_type,
300 v_adm_ci_sequence_number);
301 ELSE
302 v_course_start_dt := p_course_start_dt;
303 END IF;
304 IF p_prpsd_commencement_dt >= v_course_start_dt THEN
305 --proposed commencement date is valid
306 RETURN TRUE;
307 END IF;
308 IF v_course_start_dt IS NULL THEN
309 v_course_start_dt := TRUNC(SYSDATE);
310 END IF;
311 --Validate against earlist research start date
312 OPEN c_cir_cat_daiv_srcc;
313 FETCH c_cir_cat_daiv_srcc INTO v_research_start_dt,
314 v_cal_type,
315 v_ci_sequence_number;
316 IF (c_cir_cat_daiv_srcc%NOTFOUND) OR
317 (c_cir_cat_daiv_srcc%FOUND AND
318 v_research_start_dt IS NULL ) THEN
319 CLOSE c_cir_cat_daiv_srcc;
320 IF p_prpsd_commencement_dt < v_course_start_dt THEN
321 p_message_name := 'IGS_RE_COMEN_DT_GE_ADM_ST_DT';
322 RETURN TRUE;
323 END IF;
324 ELSE -- %NOTFOUND
325 -- For the first record only
326 CLOSE c_cir_cat_daiv_srcc;
327 IF p_prpsd_commencement_dt < v_research_start_dt THEN
328 p_message_name := 'IGS_RE_COMEN_DT_CANT_LT_TEACH';
329 RETURN FALSE;
330 END IF;
331 END IF; -- %NOTFOUND
332 --Warn if commencement date prior to a passed census date
333 OPEN c_cir_cat_daiv_sgcc;
334 FETCH c_cir_cat_daiv_sgcc INTO v_ccds_exists;
335 IF c_cir_cat_daiv_sgcc%FOUND THEN
336 CLOSE c_cir_cat_daiv_sgcc;
337 p_message_name := 'IGS_AD_COMDT_PRIOR_CENSUSDT';
338 RETURN TRUE;
339 END IF;
340 CLOSE c_cir_cat_daiv_sgcc;
341 END IF; -- p_prpsd_commencement_dt
342 -- Return the default value
343 RETURN TRUE;
344 EXCEPTION
345 WHEN OTHERS THEN
346 IF c_acaiv%ISOPEN THEN
347 CLOSE c_acaiv;
348 END IF;
349 IF c_cir_cat_daiv_srcc%ISOPEN THEN
350 CLOSE c_cir_cat_daiv_srcc;
351 END IF;
352 IF c_cir_cat_daiv_sgcc%ISOPEN THEN
353 CLOSE c_cir_cat_daiv_sgcc;
354 END IF;
355 RAISE;
356 END;
357 EXCEPTION
358 WHEN OTHERS THEN
359 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
360 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.admp_val_ca_comm_val');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 END admp_val_ca_comm_val;
364 --
365 -- Validate candidature attendance percentage
366 FUNCTION resp_val_ca_att_perc(
367 p_person_id IN NUMBER ,
368 p_course_cd IN VARCHAR2 ,
369 p_student_confirmed_ind IN VARCHAR2,
370 p_attendance_type IN VARCHAR2 ,
371 p_attendance_percentage IN NUMBER ,
372 p_candidature_ind IN VARCHAR2,
373 p_message_name OUT NOCOPY VARCHAR2)
374 RETURN BOOLEAN AS
375 BEGIN -- resp_val_ca_att_perc
376 -- This module validates IGS_RE_CANDIDATURE.attendance_percentage and
377 -- IGS_EN_STDNT_PS_ATT.attendance_type.
378 -- Validations are:
379 -- * The load of the research at the nominated attendance percentage must be
380 -- within the upper and lower load ranges for the attendance type in the
381 -- load calendar targetted. This is a warning only.
382 -- Assumption:
383 -- * The student is only ever enrolled in one research unit attempt at any
384 -- point in time. The calendar instance of the unit attempt is only linked
385 -- to one load calendar instance.
386 DECLARE
387 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
388 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
389 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
390 v_student_confirmed_ind IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
391 v_load_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE;
392 v_load_ci_sequence_number
393 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE;
394 v_acad_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE;
395 v_acad_ci_sequence_number
396 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE;
397 v_teach_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
398 v_research_eftsu NUMBER;
399 v_lower_enr_load_range IGS_EN_ATD_TYPE_LOAD.lower_enr_load_range%TYPE;
400 v_upper_enr_load_range IGS_EN_ATD_TYPE_LOAD.upper_enr_load_range%TYPE;
401 CURSOR c_sca IS
402 SELECT sca.attendance_type,
403 sca.student_confirmed_ind
404 FROM IGS_EN_STDNT_PS_ATT sca
405 WHERE sca.person_id = p_person_id AND
406 sca.course_cd = p_course_cd;
407 CURSOR c_ca IS
408 SELECT ca.attendance_percentage
409 FROM IGS_RE_CANDIDATURE ca
410 WHERE ca.person_id = p_person_id AND
411 ca.sca_course_cd = p_course_cd;
412 CURSOR c_cir_ci_cat_sua_uv IS
413 SELECT cir.sup_cal_type,
414 cir.sup_ci_sequence_number,
415 sua.cal_type
416 FROM IGS_EN_SU_ATTEMPT sua,
417 IGS_CA_INST_REL cir,
418 IGS_CA_INST ci,
419 IGS_CA_TYPE cat,
420 IGS_PS_UNIT_VER uv
421 WHERE sua.person_id = p_person_id AND
422 sua.course_cd = p_course_cd AND
423 sua.unit_cd = uv.unit_cd AND
424 sua.version_number = uv.version_number AND
425 uv.research_unit_ind = 'Y' AND
426 sua.cal_type = ci.cal_type AND
427 sua.ci_sequence_number = ci.sequence_number AND
428 ci.start_dt <= TRUNC(SYSDATE) AND
429 ci.end_dt > TRUNC(SYSDATE) AND
430 sua.cal_type = cir.sub_cal_type AND
431 sua.ci_sequence_number = cir.sub_ci_sequence_number AND
432 cir.sup_cal_type = cat.cal_type AND
433 cat.s_cal_cat = cst_academic;
434 CURSOR c_cir (
435 cp_acad_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
436 cp_acad_ci_sequence_number
437 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE,
438 cp_teach_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE) IS
439 SELECT dla.cal_type,
440 dla.ci_sequence_number
441 FROM IGS_CA_INST_REL cir,
442 IGS_ST_DFT_LOAD_APPO dla
443 WHERE cir.sup_cal_type = cp_acad_cal_type AND
444 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number AND
445 cir.sub_cal_type = dla.cal_type AND
446 cir.sub_ci_sequence_number = dla.ci_sequence_number AND
447 dla.teach_cal_type = cp_teach_cal_type AND
448 dla.percentage = 100;
449 CURSOR c_atl (
450 cp_load_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
451 cp_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE) IS
452 SELECT atl.lower_enr_load_range,
453 atl.upper_enr_load_range
454 FROM IGS_EN_ATD_TYPE_LOAD atl
455 WHERE atl.cal_type = cp_load_cal_type AND
456 atl.attendance_type = cp_attendance_type;
457 BEGIN
458 -- Set the default message number
459 p_message_name := null;
460 IF p_course_cd IS NOT NULL THEN
461 IF p_candidature_ind = 'Y' THEN
462 v_attendance_percentage := p_attendance_percentage;
463 IF p_attendance_type IS NULL OR
464 p_student_confirmed_ind IS NULL THEN
465 OPEN c_sca;
466 FETCH c_sca INTO
467 v_attendance_type,
468 v_student_confirmed_ind;
469 IF c_sca%NOTFOUND OR
470 v_student_confirmed_ind = 'N' THEN
471 -- Check is not required for unconfirmed course attempt or
472 -- candidature that is still in application stage
473 CLOSE c_sca;
474 RETURN TRUE;
475 END IF;
476 CLOSE c_sca;
477 ELSE
478 v_attendance_type := p_attendance_type;
479 IF p_student_confirmed_ind = 'N' THEN
480 -- Check is not required for unconfirmed course attempt
481 RETURN TRUE;
482 END IF;
483 END IF;
484 ELSE
485 IF p_student_confirmed_ind = 'N' THEN
486 -- Check is not required for unconfirmed course attempt
487 RETURN TRUE;
488 END IF;
489 v_attendance_type := p_attendance_type;
490 IF p_attendance_percentage IS NULL THEN
491 OPEN c_ca;
492 FETCH c_ca INTO v_attendance_percentage;
493 IF c_ca%NOTFOUND THEN
494 -- Check is only for course attempt with research candidature
495 CLOSE c_ca;
496 RETURN TRUE;
497 ELSE -- RecordFOUND
498 CLOSE c_ca;
499 -- Check does not apply if attendance percentage is defaulting
500 -- from IGS_EN_ATD_TYPE
501 IF v_attendance_percentage IS NULL THEN
502 RETURN TRUE;
503 END IF;
504 END IF;
505 ELSE
506 v_attendance_percentage := p_attendance_percentage;
507 END IF;
508 END IF;
509 -- Get academic calendar of enrolled research unit attempt
510 OPEN c_cir_ci_cat_sua_uv;
511 FETCH c_cir_ci_cat_sua_uv INTO
512 v_acad_cal_type,
513 v_acad_ci_sequence_number,
514 v_teach_cal_type;
515 IF c_cir_ci_cat_sua_uv%NOTFOUND THEN
516 -- Cannot determine load
517 CLOSE c_cir_ci_cat_sua_uv;
518 RETURN TRUE;
519 END IF;
520 CLOSE c_cir_ci_cat_sua_uv;
521 -- Get load calendar of academic calendar
522 OPEN c_cir (
523 v_acad_cal_type,
524 v_acad_ci_sequence_number,
525 v_teach_cal_type);
526 FETCH c_cir INTO
527 v_load_cal_type,
528 v_load_ci_sequence_number;
529 IF c_cir%NOTFOUND THEN
530 -- Something is wrong, handled elsewhere
531 CLOSE c_cir;
532 RETURN TRUE;
533 END IF;
534 CLOSE c_cir;
535 -- Determine research load
536 v_research_eftsu := IGS_RE_GEN_001.RESP_CLC_LOAD_EFTSU (
537 v_acad_cal_type,
538 v_acad_ci_sequence_number,
539 v_load_cal_type,
540 v_load_ci_sequence_number) * (v_attendance_percentage/100);
541 -- Get lower and upper load ranges for attendance type load
542 OPEN c_atl(
543 v_load_cal_type,
544 v_attendance_type);
545 FETCH c_atl INTO
546 v_lower_enr_load_range,
547 v_upper_enr_load_range;
548 IF c_atl%NOTFOUND THEN
549 -- Something is wrong, handled elsewhere
550 CLOSE c_atl;
551 RETURN TRUE;
552 END IF;
553 CLOSE c_atl;
554 IF v_research_eftsu > v_upper_enr_load_range OR
555 v_research_eftsu < v_lower_enr_load_range THEN
556 -- Research candidature attendance percentage in not within the
557 -- current attendance type load range
558 IF p_candidature_ind = 'Y' THEN
559 p_message_name := 'IGS_RE_CAND_%_INVALID';
560 ELSE
561 p_message_name := 'IGS_RE_CAND_EXISTS_WITH_ATT_%';
562 END IF;
563 RETURN TRUE;
564 END IF;
565 END IF;
566 RETURN TRUE ;
567 EXCEPTION
568 WHEN OTHERS THEN
569 IF c_sca%ISOPEN THEN
570 CLOSE c_sca;
571 END IF;
572 IF c_ca%ISOPEN THEN
573 CLOSE c_ca;
574 END IF;
575 IF c_cir_ci_cat_sua_uv%ISOPEN THEN
576 CLOSE c_cir_ci_cat_sua_uv;
577 END IF;
578 IF c_cir%ISOPEN THEN
579 CLOSE c_cir;
580 END IF;
581 IF c_atl%ISOPEN THEN
582 CLOSE c_atl;
583 END IF;
584 RAISE;
585 END;
586 EXCEPTION
587 WHEN OTHERS THEN
588 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
589 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.resp_val_ca_att_perc');
590 IGS_GE_MSG_STACK.ADD;
591 App_Exception.Raise_Exception;
592
593 END resp_val_ca_att_perc;
594 --
595 -- Validate that conditional offer is valid for course enrolment.
596 FUNCTION enrp_val_acai_cndtnl(
597 p_adm_cndtnl_offer_status IN VARCHAR2 ,
598 p_cndtnl_off_must_be_stsfd_ind IN VARCHAR2,
599 p_s_adm_cndtnl_offer_status OUT NOCOPY VARCHAR2 ,
600 p_message_name OUT NOCOPY VARCHAR2)
601 RETURN BOOLEAN AS
602 BEGIN -- enrp_val_acai_cndtnl
603 -- This module determines if the admission course application conditional
604 -- offer can be accepted. The following is checked:
605 -- ? Return TRUE if either the conditional offer has been satisfied or waived,
606 -- or the conditional offer is still pending, but it is not a requirement
607 -- that the condition be satisfied for acceptance ie.
608 -- IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind is 'N'.
609 -- ? Return FALSE if conditional offer is unsatisfactory, or the conditional
610 -- offer is still pending and it is a requirement that the condition be
611 -- satisfied for acceptance ie.
612 -- IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind is 'Y'.
613 DECLARE
614 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
615 cst_unsatisfac CONSTANT VARCHAR2(10) := 'UNSATISFAC';
616 cst_satisfied CONSTANT VARCHAR2(9) := 'SATISFIED';
617 cst_waived CONSTANT VARCHAR2(6) := 'WAIVED';
618 cst_pending CONSTANT VARCHAR2(7) := 'PENDING';
619 v_s_adm_cndtnl_offer_status
620 IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status%TYPE;
621 BEGIN
622 -- Determine system conditional offer status
623 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS(
624 p_adm_cndtnl_offer_status);
625 p_s_adm_cndtnl_offer_status := v_s_adm_cndtnl_offer_status;
626 IF v_s_adm_cndtnl_offer_status = cst_not_applic THEN
627 -- Conditional offer does not apply
628 p_message_name := null;
629 RETURN TRUE;
630 END IF;
631 IF v_s_adm_cndtnl_offer_status = cst_unsatisfac THEN
632 -- Unsatisfactory conditional offers cannot be accepted
633 p_message_name := 'IGS_EN_STUD_PRGATT_NOTCONF';
634 RETURN FALSE;
635 END IF;
636 IF v_s_adm_cndtnl_offer_status IN (
637 cst_satisfied,
638 cst_waived) THEN
639 -- Satisfactory or waived conditional offers can be accepted
640 p_message_name := null;
641 RETURN TRUE;
642 END IF;
643 IF v_s_adm_cndtnl_offer_status = cst_pending THEN
644 -- Pending can only be accepted if it is not a requirement that
645 -- The conditional offer be satisfied
646 IF p_cndtnl_off_must_be_stsfd_ind = 'N' THEN
647 p_message_name := null;
648 RETURN TRUE;
649 ELSE
650 p_message_name := 'IGS_EN_STUD_PRG_NOTCONFIRM';
651 RETURN FALSE;
652 END IF;
653 END IF;
654 p_message_name := null;
655 RETURN TRUE;
656 END;
657 /*
658 EXCEPTION
659 WHEN OTHERS THEN
660 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
661 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_acai_cndtnl');
662 IGS_GE_MSG_STACK.ADD;
663 App_Exception.Raise_Exception;
664 */
665 END enrp_val_acai_cndtnl;
666 --
667 -- Validate that research detail is valid for enrolment.
668 FUNCTION enrp_val_res_elgbl(
669 p_person_id IN NUMBER ,
670 p_course_cd IN VARCHAR2 ,
671 p_crv_version_number IN NUMBER ,
672 p_message_name OUT NOCOPY VARCHAR2)
673 RETURN BOOLEAN AS
674 BEGIN -- enrp_val_res_elgbl
675 -- This module determines if research candidature details are required and
676 -- completed for the person to be eligible to enrol in the nominated course.
677 -- Validations are:
678 -- . The course attempted is defined as a research course and no research
679 -- candidature details exist, or the research candidature is incomplete.
680 DECLARE
681 v_cty_research_type_ind IGS_PS_TYPE.research_type_ind%TYPE;
682 v_message_name varchar2(30);
683 CURSOR c_cty IS
684 SELECT cty.research_type_ind
685 FROM IGS_PS_VER crv,
686 IGS_PS_TYPE cty
687 WHERE crv.course_cd = p_course_cd AND
688 crv.version_number = p_crv_version_number AND
689 crv.course_type = cty.course_type;
690 BEGIN
691 -- Set up the default message number
692 p_message_name := null;
693 -- Determine if the course attempt is a research course
694 OPEN c_cty;
695 FETCH c_cty INTO v_cty_research_type_ind;
696 IF c_cty%NOTFOUND THEN
697 -- Problems with course attempt, handled elsewhere
698 CLOSE c_cty;
699 RETURN TRUE;
700 END IF;
701 CLOSE c_cty;
702 IF v_cty_research_type_ind = 'N' THEN
703 -- course attempt is not a research course, validation is not required
704 RETURN TRUE;
705 END IF;
706 -- Validate research candidature detail
707 IF NOT resp_val_ca_dtl_comp(
708 p_person_id,
709 p_course_cd,
710 NULL,
711 NULL,
712 NULL,
713 'SCA',
714 v_message_name) THEN
715 IF (v_message_name = 'IGS_RE_CAND_DETAILS_INCOMPLET') THEN
716 -- Customise incomplete check message for enrolments
717 p_message_name := 'IGS_EN_PRG_ATT_DFN';
718 ELSE
719 p_message_name := v_message_name;
720 END IF;
721 RETURN FALSE;
722 END IF;
723 RETURN TRUE;
724 EXCEPTION
725 WHEN OTHERS THEN
726 IF c_cty %ISOPEN THEN
727 CLOSE c_cty;
728 END IF;
729 RAISE;
730 END;
731 /*
732 EXCEPTION
733 WHEN OTHERS THEN
734 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
735 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_res_elgbl');
736 IGS_GE_MSG_STACK.ADD;
737 App_Exception.Raise_Exception;
738 */
739 END enrp_val_res_elgbl;
740 --
741 -- Validate if research candidature details are complete.
742 FUNCTION resp_val_ca_dtl_comp(
743 p_person_id IN NUMBER ,
744 p_sca_course_cd IN VARCHAR2 ,
745 p_acai_admission_appl_number IN NUMBER ,
746 p_acai_nominated_course_cd IN VARCHAR2 ,
747 p_acai_sequence_number IN NUMBER ,
748 p_parent IN VARCHAR2 ,
749 p_message_name OUT NOCOPY VARCHAR2)
750 RETURN BOOLEAN AS
751 BEGIN -- resp_val_ca_dtl_comp
752 DECLARE
753 cst_acai CONSTANT VARCHAR2(4) := 'ACAI';
754 v_min_submission_dt IGS_RE_CANDIDATURE.min_submission_dt%TYPE := NULL;
755 v_max_submission_dt IGS_RE_CANDIDATURE.max_submission_dt%TYPE := NULL;
756 v_supervision_start_dt DATE;
757 -- Check for the existence of research details.
758 CURSOR c_ca IS
759 SELECT ca.sequence_number,
760 ca.attendance_percentage,
761 ca.max_submission_dt,
762 ca.min_submission_dt,
763 ca.research_topic
764 FROM IGS_RE_CANDIDATURE ca
765 WHERE ca.person_id = p_person_id AND
766 ((ca.acai_admission_appl_number = p_acai_admission_appl_number AND
767 ca.acai_nominated_course_cd =p_acai_nominated_course_cd AND
768 ca.acai_sequence_number = p_acai_sequence_number) OR
769 (p_parent <> cst_acai AND
770 ca.sca_course_cd = p_sca_course_cd)) AND
771 ca.research_topic IS NOT NULL;
772 v_ca_rec c_ca%ROWTYPE;
773 BEGIN
774 -- if research details are found then ensure that the minimum submission date
775 -- has a value (actual or derived).
776 OPEN c_ca;
777 FETCH c_ca INTO v_ca_rec;
778 IF c_ca%FOUND THEN
779 IF v_ca_rec.min_submission_dt IS NOT NULL THEN
780 v_min_submission_dt := v_ca_rec.min_submission_dt;
781 ELSE
782 v_min_submission_dt := IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN (
783 p_person_id,
784 v_ca_rec.sequence_number,
785 p_sca_course_cd,
786 p_acai_admission_appl_number,
787 p_acai_nominated_course_cd,
788 p_acai_sequence_number,
789 v_ca_rec.attendance_percentage,
790 NULL); -- commencement date
791 IF v_min_submission_dt IS NULL THEN
792 p_message_name := 'IGS_RE_MIN_SUBMISSION_REQR';
793 RETURN FALSE;
794 END IF;
795 END IF;
796 -- If research details are found and the minimum submission date has a value
797 -- then ensure that
798 -- the maximum submission date has a value (actual or derived).
799 IF v_ca_rec.max_submission_dt IS NOT NULL THEN
800 v_max_submission_dt := v_ca_rec.max_submission_dt;
801 ELSE
802 v_max_submission_dt := IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN (
803 p_person_id,
804 v_ca_rec.sequence_number,
805 p_sca_course_cd,
806 p_acai_admission_appl_number,
807 p_acai_nominated_course_cd,
808 p_acai_sequence_number,
809 v_ca_rec.attendance_percentage,
810 NULL); -- commencement date
811 IF v_max_submission_dt IS NULL THEN
812 p_message_name := 'IGS_RE_MAX_SUBMSIIION_REQR';
813 RETURN FALSE;
814 END IF;
815 END IF;
816 -- IF research details are found and the minimum and maximum
817 -- submission dates have a value then ensure that the research
818 -- supervisors are valid.
819 IF NOT(IGS_RE_VAL_RSUP.resp_val_rsup_perc(
820 p_person_id,
821 v_ca_rec.sequence_number,
822 p_sca_course_cd,
823 p_acai_admission_appl_number,
824 p_acai_nominated_course_cd,
825 p_acai_sequence_number,
826 'Y', -- validate supervision percentage
827 'N', -- do not validate funding percentage
828 p_parent,
829 v_supervision_start_dt,
830 p_message_name)) THEN
831 RETURN FALSE;
832 END IF;
833 IF NOT(IGS_RE_VAL_RSUP.resp_val_rsup_princ(
834 p_person_id,
835 v_ca_rec.sequence_number,
836 v_supervision_start_dt,
837 v_supervision_start_dt,
838 p_parent,
839 p_message_name)) THEN
840 RETURN FALSE;
841 ELSE
842 p_message_name := null;
843 RETURN TRUE;
844 END IF;
845 ELSE
846 CLOSE c_ca;
847 END IF;
848 -- If this point is reached, then the research candidature details are
849 -- incomplete.
850 p_message_name := 'IGS_RE_CAND_DETAILS_INCOMPLET';
851 RETURN FALSE;
852 EXCEPTION
853 WHEN OTHERS THEN
854 IF c_ca %ISOPEN THEN
855 CLOSE c_ca;
856 END IF;
857 RAISE;
858 END;
859 /*
860 EXCEPTION
861 WHEN OTHERS THEN
862 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
863 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.resp_val_ca_dtl_comp');
864 IGS_GE_MSG_STACK.ADD;
865 App_Exception.Raise_Exception;
866 */
867 END resp_val_ca_dtl_comp;
868 --
869 -- Routine to clear records saved in a PL/SQL RECORD from a prior commit.
870 --
871 -- To validate student course attempt enrolled units satisfy rules.
872 FUNCTION enrp_val_unit_rule(
873 p_person_id IN NUMBER ,
874 p_course_cd IN VARCHAR2 ,
875 p_rule_check_ind IN VARCHAR2,
876 p_unit_cd OUT NOCOPY VARCHAR2 ,
877 p_uv_version_number OUT NOCOPY NUMBER ,
878 p_message_name OUT NOCOPY VARCHAR2,
879 p_message_text OUT NOCOPY VARCHAR2 )
880 RETURN BOOLEAN AS
881 BEGIN -- enrp_val_unit_rule
882 -- This module validates that the IGS_EN_STDNT_PS_ATT enrolled units satisfy
883 -- unit rules. This routine is to be called when all changes have been posted.
884 DECLARE
885 cst_enrolled CONSTANT
886 IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'ENROLLED';
887 v_message_text VARCHAR2(2000);
888 l_failed_rule igs_en_su_attempt_all.failed_unit_rule%TYPE;
889 CURSOR c_sua IS
890 SELECT sua.unit_cd,
891 sua.version_number,
892 sua.cal_type,
893 sua.ci_sequence_number,
894 sua.rule_waived_dt,
895 sua.uoo_id
896 FROM IGS_EN_SU_ATTEMPT sua
897 WHERE sua.person_id = p_person_id AND
898 sua.course_cd = p_course_cd AND
899 sua.unit_attempt_status = cst_enrolled;
900 BEGIN
901 IF (p_rule_check_ind = 'Y') THEN
902 -- Validate all enrolled student unit attempts for the student course attempt
903 FOR v_sua_rec IN c_sua LOOP
904 IF v_sua_rec.rule_waived_dt IS NULL THEN
905 -- Determine if unit does not satisfy IGS_RU_RULE checks if
906 -- rules checking has not benn waived for the student unit attempt
907 IF (IGS_RU_VAL_UNIT_RULE.rulp_val_enrol_unit(
908 p_person_id,
909 p_course_cd,
910 NULL,
911 v_sua_rec.unit_cd,
912 v_sua_rec.version_number,
913 v_sua_rec.cal_type,
914 v_sua_rec.ci_sequence_number,
915 v_message_text,
916 v_sua_rec.uoo_id,
917 l_failed_rule) = FALSE) THEN
918 p_message_name := null;
919 p_message_text := v_message_text;
920 p_unit_cd := v_sua_rec.unit_cd;
921 p_uv_version_number := v_sua_rec.version_number;
922 RETURN FALSE;
923 END IF;
924 END IF;
925 END LOOP;
926 END IF;
927 p_message_name := null;
928 RETURN TRUE;
929 EXCEPTION
930 WHEN OTHERS THEN
931 IF (c_sua%ISOPEN) THEN
932 CLOSE c_sua;
933 END IF;
934 RAISE;
935 END;
936 EXCEPTION
937 WHEN OTHERS THEN
938 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
939 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_unit_rule');
940 IGS_GE_MSG_STACK.ADD;
941 App_Exception.Raise_Exception;
942 END enrp_val_unit_rule;
943 --
944 -- To validate sca unit calendars against academic calendar type
945 FUNCTION ENRP_VAL_SCA_CAT(
946 p_person_id IN NUMBER ,
947 p_course_cd IN VARCHAR2 ,
948 p_cal_type IN VARCHAR2 ,
949 p_message_name OUT NOCOPY VARCHAR2)
950 RETURN boolean AS
951 BEGIN -- enrp_val_sca_cat
952 -- Validate whether the nominated course attempt has UNCONFIRMED, ENROLLED,
953 -- or INVALID unit attempts which aren't linked to an instance of their
954 -- enrolled course academic calendar type.
955 -- This is the result of a change of course offering option calendar type
956 -- where there were units of these statuses.
957 DECLARE
958 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
959 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
960 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
961 v_alternate_cd IGS_CA_INST.alternate_code%TYPE;
962 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
963 v_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
964 v_acad_ci_start_dt IGS_CA_INST.start_dt%TYPE;
965 v_acad_ci_end_dt IGS_CA_INST.end_dt%TYPE;
966 v_message_name varchar2(30);
967 v_unconfirm_flag BOOLEAN := FALSE;
968 v_enrolled_flag BOOLEAN := FALSE;
969 CURSOR c_sua IS
970 SELECT sua.cal_type,
971 sua.ci_sequence_number,
972 sua.unit_attempt_status
973 FROM IGS_EN_SU_ATTEMPT sua
974 WHERE sua.person_id = p_person_id AND
975 sua.course_cd = p_course_cd AND
976 sua.unit_attempt_status IN (
977 cst_invalid,
978 cst_enrolled,
979 cst_unconfirm);
980 BEGIN
981 p_message_name := null;
982 FOR v_sua_record IN c_sua LOOP
983 v_alternate_cd := IGS_EN_GEN_002.ENRP_GET_ACAD_ALT_CD(
984 v_sua_record.cal_type,
985 v_sua_record.ci_sequence_number,
986 v_acad_cal_type,
987 v_acad_ci_sequence_number,
988 v_acad_ci_start_dt,
989 v_acad_ci_end_dt,
990 v_message_name);
991 IF v_acad_cal_type <> p_cal_type THEN
992 IF v_sua_record.unit_attempt_status = cst_unconfirm THEN
993 v_unconfirm_flag := TRUE;
994 ELSE
995 v_enrolled_flag := TRUE;
996 END IF;
997 END IF;
998 IF v_unconfirm_flag = TRUE AND
999 v_enrolled_flag = TRUE THEN
1000 -- Exit loop - no point continuing processing.
1001 EXIT;
1002 END IF;
1003 END LOOP;
1004 IF v_enrolled_flag = TRUE THEN
1005 p_message_name := 'IGS_EN_ENR_UNITATT_NOTLINKED';
1006 ELSIF v_unconfirm_flag = TRUE THEN
1007 p_message_name := 'IGS_EN_UNCONF_UA_EXISTS';
1008 END IF;
1009 RETURN TRUE;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 IF (c_sua%ISOPEN) THEN
1013 CLOSE c_sua;
1014 END IF;
1015 RAISE;
1016 END;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1020 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_cat');
1021 IGS_GE_MSG_STACK.ADD;
1022 App_Exception.Raise_Exception;
1023 END enrp_val_sca_cat;
1024 --
1025 -- To validate the IGS_EN_STDNT_PS_ATT.lapse_dt
1026 FUNCTION enrp_val_sca_lapse(
1027 p_course_attempt_status IN VARCHAR2 ,
1028 p_lapse_dt IN DATE ,
1029 p_message_name OUT NOCOPY VARCHAR2,
1030 p_legacy IN VARCHAR2)
1031 RETURN boolean AS
1032 /*-------------------------------------------------------------------------------------------------------
1033 || Created By :
1034 || Created On :
1035 || Purpose : validate the setting of lapse date against other enrolment details
1036 || (mostly within the IGS_EN_STDNT_PS_ATT table).
1037 || Known limitations, enhancements or remarks :
1038 || Change History :
1039 || Who When What
1040 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
1041 || if p_legacy set to 'Y' then error message should be stacked instead of
1042 || returning the function in the normal way else function should behave in
1043 || normal way.Legacy Build Bug no: 2661533
1044 ------------------------------------------------------------------------------------------------------------*/
1045 BEGIN
1046 DECLARE
1047 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
1048 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1049 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1050 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1051 BEGIN
1052 p_message_name := null;
1053 IF (p_lapse_dt IS NULL) THEN
1054 RETURN TRUE;
1055 END IF;
1056 IF p_legacy <> 'Y' THEN
1057 IF (p_course_attempt_status NOT IN (cst_inactive,cst_lapsed)) THEN
1058 p_message_name := 'IGS_EN_LAPSEDT_SET_INACTIVE';
1059 RETURN FALSE;
1060 END IF;
1061 IF (p_lapse_dt <> TRUNC(SYSDATE)) THEN
1062 p_message_name := 'IGS_EN_LAPSEDT_SET_CURRDT';
1063 RETURN FALSE;
1064 END IF;
1065 END IF;
1066 IF p_legacy = 'Y' THEN
1067 IF p_course_attempt_status IN (cst_unconfirm,cst_completed) THEN
1068 p_message_name := 'IGS_EN_SCA_NO_LP_DT_UNCOMFIRM';
1069 fnd_message.set_name('IGS','IGS_EN_SCA_NO_LP_DT_UNCOMFIRM');
1070 fnd_msg_pub.add;
1071 END IF;
1072 END IF;
1073 RETURN TRUE ;
1074 END;
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1078 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_lapse');
1079 IGS_GE_MSG_STACK.ADD;
1080 App_Exception.Raise_Exception;
1081 END enrp_val_sca_lapse;
1082 --
1083 --
1084 -- To validate acceptance of admission course transfer.
1085 FUNCTION enrp_val_trnsfr_acpt(
1086 p_person_id IN NUMBER ,
1087 p_course_cd IN VARCHAR2 ,
1088 p_student_confirmed_ind IN VARCHAR2,
1089 p_admission_appl_number IN NUMBER ,
1090 p_nominated_course_cd IN VARCHAR2 ,
1091 p_adm_offer_resp_status IN VARCHAR2 ,
1092 p_message_name OUT NOCOPY VARCHAR2)
1093 RETURN BOOLEAN AS
1094 BEGIN -- enrp_val_trnsfr_acpt
1095 -- This module validates that acceptance of an admission course transfer
1096 -- application can only occur when matching IGS_PS_STDNT_TRN details
1097 -- exist.
1098 -- This validation routine will be called from ENRF3000, ADMF3240,
1099 -- IGS_EN_STDNT_PS_ATT and IGS_AD_PS_APPL_INST database triggers,
1100 -- and the pre-enrolment process.
1101 DECLARE
1102 v_s_adm_offer_resp_status IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
1103 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
1104 v_dummy VARCHAR2(1);
1105 CURSOR c_aa IS
1106 SELECT s_admission_process_type
1107 FROM IGS_AD_APPL
1108 WHERE person_id = p_person_id AND
1109 admission_appl_number = p_admission_appl_number;
1110 CURSOR c_aca_sct IS
1111 SELECT 'x'
1112 FROM IGS_AD_PS_APPL aca,
1113 IGS_PS_STDNT_TRN sct
1114 WHERE aca.person_id = p_person_id AND
1115 aca.admission_appl_number = p_admission_appl_number AND
1116 aca.nominated_course_cd = p_nominated_course_cd AND
1117 sct.person_id = aca.person_id AND
1118 sct.course_cd = p_course_cd AND
1119 sct.transfer_course_cd = aca.transfer_course_cd;
1120 BEGIN
1121 IF p_admission_appl_number IS NULL OR
1122 p_nominated_course_cd IS NULL THEN
1123 -- This is not a IGS_EN_STDNT_PS_ATT inserted as a result of an admission
1124 -- application
1125 p_message_name := null;
1126 RETURN TRUE;
1127 END IF;
1128 -- determine system admission offer response status
1129 IF p_adm_offer_resp_status IS NULL THEN
1130 v_s_adm_offer_resp_status := NULL;
1131 ELSE
1132 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status);
1133 END IF;
1134 IF p_student_confirmed_ind = 'Y' OR
1135 v_s_adm_offer_resp_status = 'ACCEPTED' THEN
1136 -- determine if the admission_appilication is a course transfer
1137 OPEN c_aa;
1138 FETCH c_aa INTO v_s_admission_process_type;
1139 CLOSE c_aa;
1140 IF v_s_admission_process_type = 'TRANSFER' THEN
1141 -- Determine if student course transfer detail exists
1142 -- matching admission course application details
1143 OPEN c_aca_sct;
1144 FETCH c_aca_sct INTO v_dummy;
1145 IF (c_aca_sct%NOTFOUND) THEN
1146 IF (p_student_confirmed_ind = 'Y') THEN
1147 -- return_message for confirmation of
1148 -- IGS_EN_STDNT_PS_ATT
1149 p_message_name := 'IGS_EN_STUD_PRG_ATTEMPT';
1150 ELSE
1151 -- return message for acceptance of
1152 -- IGS_AD_PS_APPL_INST
1153 p_message_name := 'IGS_EN_APPL_PRG_TRANSFER';
1154 END IF;
1155 CLOSE c_aca_sct;
1156 RETURN FALSE;
1157 END IF;
1158 CLOSE c_aca_sct;
1159 END IF;
1160 END IF;
1161 p_message_name := null;
1162 RETURN TRUE;
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 IF (c_aa%ISOPEN) THEN
1166 CLOSE c_aa;
1167 END IF;
1168 IF (c_aca_sct%ISOPEN) THEN
1169 CLOSE c_aca_sct;
1170 END IF;
1171 RAISE;
1172 END;
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1176 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_trnsfr_acpt');
1177 IGS_GE_MSG_STACK.ADD;
1178 App_Exception.Raise_Exception;
1179 END enrp_val_trnsfr_acpt;
1180 --
1181 -- To validate whether a change of course offering option is allowed
1182 FUNCTION ENRP_VAL_CHGO_ALWD(
1183 p_person_id IN NUMBER ,
1184 p_course_cd IN VARCHAR2 ,
1185 p_message_name OUT NOCOPY VARCHAR2)
1186 RETURN boolean AS
1187 -- Change History :
1188 -- Who When What
1189 -- stutta 01-NOV-2004 Returning TRUE when program attempt status
1190 -- is completed. Enh #3959306
1191 BEGIN -- enrp_val_chgo_alwd
1192 -- Validate that the change of course offering option is allowed.
1193 DECLARE
1194 v_ret_val BOOLEAN := TRUE;
1195 CURSOR c_student_course_attempt (
1196 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1197 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1198 SELECT course_attempt_status,
1199 version_number
1200 FROM
1201 IGS_EN_STDNT_PS_ATT
1202 WHERE
1203 person_id = cp_person_id AND
1204 course_cd = cp_course_cd;
1205 BEGIN
1206 p_message_name := null;
1207 FOR v_sca_rec IN c_student_course_attempt(p_person_id, p_course_cd) LOOP
1208 IF (v_sca_rec.course_attempt_status = 'DISCONTIN') THEN
1209 p_message_name := 'IGS_EN_CHG_OPT_NOTALLOW_DISCN';
1210 v_ret_val := FALSE;
1211 ELSIF (v_sca_rec.course_attempt_status = 'LAPSED') THEN
1212 p_message_name := 'IGS_EN_CHG_OPT_NOTALLOW_LAPSE';
1213 v_ret_val := FALSE;
1214 ELSIF (v_sca_rec.course_attempt_status = 'DELETED') THEN
1215 p_message_name := 'IGS_EN_CHG_OPT_NOTALLOW_DEL';
1216 v_ret_val := FALSE;
1217 ELSIF (v_sca_rec.course_attempt_status = 'COMPLETED') THEN
1218 p_message_name := 'IGS_EN_CHG_OPT_NOTALLOW_COMPL';
1219 v_ret_val := TRUE;
1220 END IF;
1221 IF (v_ret_val = FALSE) THEN
1222 EXIT;
1223 END IF;
1224 END LOOP;
1225 RETURN v_ret_val;
1226 END;
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1230 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_chgo_alwd');
1231 IGS_GE_MSG_STACK.ADD;
1232 App_Exception.Raise_Exception;
1233 END enrp_val_chgo_alwd;
1234 --
1235 -- To validate all sua records against coo cross restrictions
1236 FUNCTION ENRP_VAL_SUA_COO(
1237 p_person_id IN NUMBER ,
1238 p_course_cd IN VARCHAR2 ,
1239 p_coo_id IN NUMBER ,
1240 p_cal_type IN VARCHAR2 ,
1241 p_sequence_number IN NUMBER ,
1242 p_message_name1 OUT NOCOPY VARCHAR2,
1243 p_message_name2 OUT NOCOPY VARCHAR2,
1244 p_message_name3 OUT NOCOPY VARCHAR2,
1245 p_load_or_teach_cal_type IN VARCHAR2,
1246 p_load_or_teach_seq_number IN NUMBER)
1247 RETURN boolean AS
1248 BEGIN
1249 DECLARE
1250 -- Need to declare p_mess1 and p_mess2 because an 'IF' statement
1251 -- cannot be performed on an 'OUT' parameter.
1252 p_mess1 varchar2(30);
1253 p_mess2 varchar2(30);
1254 p_mess3 varchar2(30);
1255 v_message_name varchar2(30);
1256 v_attendance_types VARCHAR2(100);
1257 CURSOR c_sua (
1258 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1259 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1260 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1261 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
1262 SELECT sua.location_cd,
1263 sua.unit_class
1264 FROM IGS_EN_SU_ATTEMPT sua
1265 WHERE sua.person_id = cp_person_id AND
1266 sua.course_cd = cp_course_cd AND
1267 sua.unit_attempt_status = 'ENROLLED' AND
1268 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
1269 cp_cal_type,
1270 cp_ci_sequence_number,
1271 sua.cal_type,
1272 sua.ci_sequence_number,
1273 'N') = 'Y';
1274 BEGIN
1275 p_mess1 := NULL;
1276 p_mess2 := NULL;
1277 p_mess3 := NULL;
1278 IF enrp_val_coo_att(p_person_id,
1279 p_coo_id,
1280 p_cal_type,
1281 p_sequence_number,
1282 v_message_name,
1283 v_attendance_types,
1284 p_load_or_teach_cal_type,
1285 p_load_or_teach_seq_number) = FALSE THEN
1286 p_mess1 := v_message_name;
1287 END IF;
1288 FOR v_sua_row IN c_sua(
1289 p_person_id,
1290 p_course_cd,
1291 p_cal_type,
1292 p_sequence_number) LOOP
1293 -- 1.1 If the cross-LOCATION check hasn't already
1294 -- failed then apply it.
1295 IF (p_mess2 is NULL) THEN
1296 IF (IGS_EN_VAL_SUA.enrp_val_coo_loc(
1297 p_coo_id,
1298 v_sua_row.location_cd,
1299 v_message_name) = FALSE) THEN
1300 p_mess2 := 'IGS_EN_UNITLOC_CONFLICTS';
1301 END IF;
1302 END IF;
1303 -- 1.2 If the cross-mode check hasn't already
1304 -- failed then apply it.
1305 IF (p_mess3 is NULL) THEN
1306 IF (IGS_EN_VAL_SUA.enrp_val_coo_mode(
1307 p_coo_id,
1308 v_sua_row.unit_class,
1309 v_message_name) = FALSE) THEN
1310 p_mess3 := 'IGS_EN_UNITMODE_CONFLICTS';
1311 END IF;
1312 END IF;
1313 -- 1.3 If the student has failed both checks there
1314 -- is no point in continuing - exit loop
1315 IF ((p_mess2 is not NULL) AND
1316 (p_mess3 is not NULL)) THEN
1317 EXIT;
1318 END IF;
1319 END LOOP;
1320 p_message_name1 := p_mess1;
1321 p_message_name2 := p_mess2;
1322 p_message_name3 := p_mess3;
1323 IF ((p_mess1 is not NULL) OR
1324 (p_mess2 is not NULL) OR
1325 (p_mess3 is not NULL)) THEN
1326 RETURN FALSE;
1327 ELSE
1328 RETURN TRUE;
1329 END IF;
1330 END;
1331 /* EXCEPTION
1332 WHEN OTHERS THEN
1333 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1334 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sua_coo');
1335 IGS_GE_MSG_STACK.ADD;
1336 App_Exception.Raise_Exception;*/
1337 END enrp_val_sua_coo;
1338 --
1339 -- To validate confirmed indicator on student course attempt
1340 FUNCTION enrp_val_sca_confirm(
1341 p_person_id IN NUMBER ,
1342 p_course_cd IN VARCHAR2 ,
1343 p_admission_appl_number IN NUMBER ,
1344 p_nominated_course_cd IN VARCHAR2 ,
1345 p_acai_sequence_number IN NUMBER ,
1346 p_student_confirmed_ind IN VARCHAR2,
1347 p_course_attempt_status IN VARCHAR2 ,
1348 p_message_name OUT NOCOPY VARCHAR2)
1349 RETURN BOOLEAN AS
1350 BEGIN -- enrp_val_sca_confirm.
1351 -- Validate the student_confirmed_ind from the
1352 -- IGS_EN_STDNT_PS_ATT :
1353 -- * course_attempt_status must be 'ENROLLED',
1354 -- 'INACTIVE' or 'UNCONFIRM' to unset the
1355 -- student_confrimed_ind.
1356 -- * student_confirmed_ind must not be unset
1357 -- when student_unit_attempts exist that are
1358 -- not unconfirmed.
1359 DECLARE
1360 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1361 cst_complete CONSTANT VARCHAR2(10) := 'COMPLETE';
1362 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
1363 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1364 cst_accepted CONSTANT VARChar2(10) := 'ACCEPTED';
1365 v_sua_found VARCHAR2(1);
1366 v_acai_status VARCHAR2(30);
1367 v_adm_outcome_status VARCHAR2(10);
1368 v_adm_offer_resp_status VARCHAR2(10);
1369 v_s_adm_offer_resp_status VARCHAR2(10);
1370 CURSOR c_sua IS
1371 SELECT 'x'
1372 FROM IGS_EN_SU_ATTEMPT
1373 WHERE person_id = p_person_id AND
1374 course_cd = p_course_cd AND
1375 unit_attempt_status <> cst_unconfirm;
1376 BEGIN
1377 -- check the course attempt status
1378 IF p_course_attempt_status IN (cst_discontin,
1379 cst_complete,
1380 cst_intermit) THEN
1381 p_message_name := 'IGS_EN_CONF_IND_ONLY_BE_CHANG';
1382 RETURN FALSE;
1383 END IF;
1384 -- check student unit attempts
1385 IF p_student_confirmed_ind = 'N' THEN
1386 OPEN c_sua;
1387 FETCH c_sua INTO v_sua_found;
1388 -- check if a record was found
1389 IF c_sua%FOUND THEN
1390 CLOSE c_sua;
1391 p_message_name := 'IGS_EN_PRG_ATT_CONF_ENR';
1392 RETURN FALSE;
1393 END IF;
1394 CLOSE c_sua;
1395 IF p_admission_appl_number IS NOT NULL THEN
1396 -- Get admission application response
1397 -- status.
1398 v_acai_status := IGS_AD_GEN_003.ADMP_GET_ACAI_STATUS (
1399 p_person_id,
1400 p_admission_appl_number,
1401 p_nominated_course_cd,
1402 p_acai_sequence_number,
1403 v_adm_outcome_status,
1404 v_adm_offer_resp_status);
1405 -- Get systemp offer response status
1406 IF v_adm_offer_resp_status IS NOT NULL THEN
1407 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(
1408 v_adm_offer_resp_status);
1409 IF v_s_adm_offer_resp_status = cst_accepted THEN
1410 p_message_name := 'IGS_EN_ASSOCIATE_ADMPRG_APPL';
1411 RETURN TRUE;
1412 END IF;
1413 END IF;
1414 END IF;
1415 END IF;
1416 -- set the default message number and return type
1417 p_message_name := null;
1418 RETURN TRUE;
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 IF c_sua%ISOPEN THEN
1422 CLOSE c_sua;
1423 END IF;
1424 RAISE;
1425 END;
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1429 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_confirm');
1430 IGS_GE_MSG_STACK.ADD;
1431 App_Exception.Raise_Exception;
1432 END enrp_val_sca_confirm;
1433 --
1434 -- To validate the sca attendance type against the coo restriction
1435 FUNCTION ENRP_VAL_COO_ATT(
1436 p_person_id IN NUMBER ,
1437 p_coo_id IN NUMBER ,
1438 p_cal_type IN VARCHAR2 ,
1439 p_ci_sequence_number IN NUMBER ,
1440 p_message_name OUT NOCOPY VARCHAR2,
1441 p_attendance_types OUT NOCOPY VARCHAR2,
1442 p_load_or_teach_cal_type IN VARCHAR2,
1443 p_load_or_teach_seq_number IN NUMBER)
1444 RETURN boolean AS
1445 /******************************************************************
1446 Created By : knaraset
1447 Date Created By : 12-Nov-2001
1448 Purpose : This procedure updates Enrolled_Cp and achieveable_Cp in SUA record
1449 when Approved Credit Points is created.
1450 Known limitations,
1451 enhancements,
1452 remarks :
1453 Change History
1454 Who When What
1455 ckasu 24-APR-2006 Modified as a part of bug#5191592 inorder to consider passed in Term Calendar
1456 alone during efstu caluculation when Term calendar or Teach calendar is passed
1457 else earlier logic of getting all the load calendar associated to the academic
1458 calendar is remained intact.
1459 *********************************************************************/
1460 BEGIN
1461 DECLARE
1462 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
1463 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
1464 v_course_cd IGS_PS_OFR_OPT.course_cd%TYPE;
1465 v_coo_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE;
1466 v_forced_att_type_ind IGS_PS_OFR_OPT.forced_att_type_ind%TYPE;
1467 v_lower_enr_load_range IGS_EN_ATD_TYPE.lower_enr_load_range%TYPE;
1468 v_upper_enr_load_range IGS_EN_ATD_TYPE.upper_enr_load_range%TYPE;
1469 v_validation_failed BOOLEAN;
1470 v_concat_attendance_type VARCHAR2(100);
1471 v_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
1472 v_period_load IGS_EN_ATD_TYPE_LOAD.lower_enr_load_range%TYPE;
1473 v_credit_points NUMBER;
1474 v_other_detail VARCHAR(255);
1475 CURSOR c_coo(
1476 cp_coo_id IGS_PS_OFR_OPT.coo_id%TYPE) IS
1477 SELECT COO.course_cd,
1478 COO.attendance_type,
1479 COO.forced_att_type_ind
1480 FROM IGS_PS_OFR_OPT COO
1481 WHERE COO.coo_id = cp_coo_id
1482 AND COO.delete_flag = 'N';
1483 CURSOR c_attendance_type(
1484 cp_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE) IS
1485 SELECT ATT.lower_enr_load_range,
1486 ATT.upper_enr_load_range
1487 FROM IGS_EN_ATD_TYPE ATT
1488 WHERE ATT.attendance_type = cp_attendance_type;
1489 CURSOR c_cal_type_instance(
1490 cp_cal_type IGS_CA_INST.cal_type%TYPE,
1491 cp_sequence_number IGS_CA_INST.sequence_number%TYPE)IS
1492 SELECT CI.cal_type,
1493 CI.sequence_number,
1494 CI.start_dt
1495 FROM IGS_CA_INST_REL CIR,
1496 IGS_CA_INST CI,
1497 IGS_CA_TYPE CT,
1498 IGS_CA_STAT CS
1499 WHERE CT.closed_ind = 'N' AND
1500 CT.s_cal_cat = cst_load AND
1501 CS.s_cal_status = cst_active AND
1502 CI.cal_status = CS.cal_status AND
1503 CI.cal_type = CT.cal_type AND
1504 CIR.sup_cal_type = cp_cal_type AND
1505 CIR.sup_ci_sequence_number = cp_sequence_number AND
1506 CIR.sub_cal_type = CI.cal_type AND
1507 CIR.sub_ci_sequence_number = CI.sequence_number
1508 ORDER BY CI.start_dt;
1509 CURSOR c_is_cal_load_or_teach(cp_cal_type IGS_CA_INST.cal_type%TYPE,
1510 cp_seq_number IGS_CA_INST.sequence_number%TYPE) IS
1511 SELECT ct.s_cal_cat
1512 FROM igs_ca_inst ci,
1513 igs_ca_type ct
1514 WHERE ci.cal_type = ct.cal_type
1515 AND ct.closed_ind = 'N'
1516 AND ci.cal_type = cp_cal_type
1517 AND ci.sequence_number = cp_seq_number;
1518
1519 CURSOR c_get_teach_to_load_cal(cp_cal_type IGS_CA_INST.cal_type%TYPE,
1520 cp_seq_number IGS_CA_INST.sequence_number%TYPE) IS
1521 SELECT load_cal_type,load_ci_sequence_number
1522 FROM igs_ca_teach_to_load_v
1523 WHERE teach_cal_type = cp_cal_type
1524 AND teach_ci_sequence_number = cp_seq_number
1525 ORDER BY load_start_dt;
1526
1527 l_cal_category IGS_CA_TYPE.s_cal_cat%TYPE;
1528 l_load_cal_type IGS_CA_INST.cal_type%TYPE;
1529 l_load_seq_number IGS_CA_INST.sequence_number%TYPE;
1530
1531 BEGIN
1532 -- Validate the nominated attendance type against IGS_PS_OFR_OPT
1533 -- IGS_AD_LOCATION code for the students enrolled course.
1534 -- The check is only done if :
1535 -- o the IGS_PS_OFR_OPT.forced_location_ind is set.
1536 -- o one of the attendance type load range values in the IGS_EN_ATD_TYPE table
1537 -- are set
1538 -- NOTE: This validation is reliant on the student unit attempts being checked
1539 -- against- having been committed to the database and having had the "unit
1540 -- attempt statuses" derived.
1541 p_message_name := null;
1542 p_attendance_types := NULL;
1543 -- Check that the attendance type for the course offering option is forced.
1544 OPEN c_coo(
1545 p_coo_id);
1546 FETCH c_coo INTO v_course_cd,
1547 v_coo_attendance_type,
1548 v_forced_att_type_ind;
1549 IF(c_coo%NOTFOUND) THEN
1550 CLOSE c_coo;
1551 RETURN TRUE;
1552 END IF;
1553 CLOSE c_coo;
1554 IF(v_forced_att_type_ind = 'N') THEN
1555 RETURN TRUE;
1556 END IF;
1557 -- Check whether any load ranges have been specified; if not the attendance
1558 -- type in which the student has enrolled if effectively an "unspecified"
1559 -- option,
1560 -- so no check is possible.
1561 OPEN c_attendance_type(
1562 v_coo_attendance_type);
1563 FETCH c_attendance_type INTO v_lower_enr_load_range,
1564 v_upper_enr_load_range;
1565 IF(c_attendance_type%NOTFOUND) THEN
1566 CLOSE c_attendance_type;
1567 RETURN TRUE;
1568 END IF;
1569 CLOSE c_attendance_type;
1570 IF((v_lower_enr_load_range = 0 OR v_lower_enr_load_range IS NULL) AND
1571 (v_upper_enr_load_range = 0 OR v_upper_enr_load_range IS NULL)) THEN
1572 RETURN TRUE;
1573 END IF;
1574 -- Loop through the load periods for the academic period and call the
1575 -- routines to get the effective load for that period.
1576 v_validation_failed := FALSE;
1577 v_concat_attendance_type := NULL;
1578
1579
1580 IF p_load_or_teach_cal_type IS NULL OR p_load_or_teach_seq_number IS NULL THEN
1581
1582 FOR v_cal_type_instance_rec IN c_cal_type_instance(
1583 p_cal_type,
1584 p_ci_sequence_number)
1585 LOOP
1586 -- Call ENRP_CLC_LOAD_TOTAL routine to get the load incurred within the
1587 -- current load period
1588 v_period_load := IGS_EN_PRC_LOAD.ENRP_CLC_EFTSU_TOTAL(
1589 p_person_id,
1590 v_course_cd,
1591 p_cal_type,
1592 p_ci_sequence_number,
1593 v_cal_type_instance_rec.cal_type,
1594 v_cal_type_instance_rec.sequence_number,
1595 'Y',
1596 'Y',
1597 NULL,
1598 NULL,
1599 v_credit_points);
1600 -- Call routine to determine the attendance type for the calculated load
1601 -- figure within the current load calendar
1602 v_attendance_type := IGS_EN_PRC_LOAD.ENRP_GET_LOAD_ATT(
1603 v_cal_type_instance_rec.cal_type,
1604 v_period_load);
1605 -- Concatenate the attendance type onto the variable.
1606 IF v_concat_attendance_type IS NULL THEN
1607 v_concat_attendance_type := NVL(v_attendance_type,'-');
1608 ELSE
1609 v_concat_attendance_type := v_concat_attendance_type || ',' ||
1610 NVL(v_attendance_type,'-');
1611 END IF;
1612 IF v_attendance_type IS NOT NULL THEN
1613 -- If the attendance type is different then set a flag indicating that the
1614 -- validation has failed. This will be picked up after the loop has
1615 -- completed.
1616 IF (v_attendance_type <> v_coo_attendance_type) THEN
1617 v_validation_failed := TRUE;
1618 END IF;
1619 END IF;
1620 END LOOP;
1621 ELSE
1622
1623 OPEN c_is_cal_load_or_teach(p_load_or_teach_cal_type,p_load_or_teach_seq_number);
1624 FETCH c_is_cal_load_or_teach INTO l_cal_category;
1625 CLOSE c_is_cal_load_or_teach;
1626
1627 IF l_cal_category = 'TEACHING' THEN
1628
1629 OPEN c_get_teach_to_load_cal( p_load_or_teach_cal_type,p_load_or_teach_seq_number);
1630 FETCH c_get_teach_to_load_cal INTO l_load_cal_type,l_load_seq_number;
1631 CLOSE c_get_teach_to_load_cal;
1632
1633 ELSE
1634
1635 l_load_cal_type := p_load_or_teach_cal_type;
1636 l_load_seq_number := p_load_or_teach_seq_number;
1637
1638 END IF; -- l_cal_category = 'TEACH' THEN
1639
1640 -- Call ENRP_CLC_LOAD_TOTAL routine to get the load incurred within the
1641 -- current load period
1642 v_period_load := IGS_EN_PRC_LOAD.ENRP_CLC_EFTSU_TOTAL(
1643 p_person_id,
1644 v_course_cd,
1645 p_cal_type,
1646 p_ci_sequence_number,
1647 l_load_cal_type,
1648 l_load_seq_number,
1649 'Y',
1650 'Y',
1651 NULL,
1652 NULL,
1653 v_credit_points);
1654 -- Call routine to determine the attendance type for the calculated load
1655 -- figure within the current load calendar
1656 v_attendance_type := IGS_EN_PRC_LOAD.ENRP_GET_LOAD_ATT(
1657 l_load_cal_type,
1658 v_period_load);
1659 -- Concatenate the attendance type onto the variable.
1660 IF v_concat_attendance_type IS NULL THEN
1661 v_concat_attendance_type := NVL(v_attendance_type,'-');
1662 ELSE
1663 v_concat_attendance_type := v_concat_attendance_type || ',' ||
1664 NVL(v_attendance_type,'-');
1665 END IF;
1666 IF v_attendance_type IS NOT NULL THEN
1667 -- If the attendance type is different then set a flag indicating that the
1668 -- validation has failed. This will be picked up after the loop has
1669 -- completed.
1670 IF (v_attendance_type <> v_coo_attendance_type) THEN
1671 v_validation_failed := TRUE;
1672 END IF;
1673 END IF;
1674
1675 END IF;-- end of IF l_cal_category = 'ACADEMIC' THEN
1676 -- Set the OUT NOCOPY parameter
1677 p_attendance_types := v_concat_attendance_type;
1678 IF v_validation_failed THEN
1679 p_message_name := 'IGS_EN_STUD_OUTSIDE_ENRATT_TY';
1680 RETURN FALSE;
1681 END IF;
1682 RETURN TRUE;
1683 /*EXCEPTION
1684 WHEN OTHERS THEN
1685 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1686 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_coo_att');
1687 IGS_GE_MSG_STACK.ADD;
1688 App_Exception.Raise_Exception;*/
1689 END;
1690 END enrp_val_coo_att;
1691 --
1692 -- To validate the SCA discontinuation reason code
1693 FUNCTION enrp_val_sca_dr(
1694 p_person_id IN NUMBER ,
1695 p_course_cd IN VARCHAR2 ,
1696 p_discontinuation_reason_cd IN VARCHAR2 ,
1697 p_discontinued_dt IN DATE ,
1698 p_message_name OUT NOCOPY VARCHAR2,
1699 p_legacy IN VARCHAR2)
1700 RETURN BOOLEAN AS
1701 /*----------------------------------------------------------------------------
1702 || Created By :
1703 || Created On :
1704 || Purpose : validate the IGS_EN_DCNT_REASONCD from the IGS_EN_STDNT_PS_ATT table
1705 ||
1706 || Known limitations, enhancements or remarks :
1707 || Change History :
1708 || Who When What
1709 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
1710 || if p_legacy set to 'Y' then error message should be stacked instead of
1711 || returning the function in the normal way else function should behave in
1712 || normal way.Legacy Build Bug no: 2661533
1713 || pradhakr 27-Dec-2002 The validation related to transfer discontinuation reason codes
1714 || should not be done as part of Legacy API, as this cannot be tested
1715 || at the import stage itself. Added a condition p_legacy <> 'Y' to display
1716 || the error message 'IGS_EN_PRG_TRSF_DISCONT' if it is called from any other
1717 || package other than the legacy API. Changes wrt Bug# 2728123
1718 || bdeviset 22-Dec-2004 Modifed cursor c_sct in enrp_val_sca_dr as part Bug#4083015.
1719 ------------------------------------------------------------------------------*/
1720 BEGIN
1721 DECLARE
1722 cst_transfer CONSTANT VARCHAR2(10) := 'TRANSFER';
1723 v_dummy VARCHAR2(1);
1724 CURSOR c_sca IS
1725 SELECT dr.closed_ind,
1726 dr.s_discontinuation_reason_type
1727 FROM IGS_EN_DCNT_REASONCD dr
1728 WHERE dr.discontinuation_reason_cd = p_discontinuation_reason_cd;
1729
1730 -- Modifed cursor to consider the status_flag while finding the transfer records
1731 CURSOR c_sct IS
1732 SELECT 'X'
1733 FROM IGS_PS_STDNT_TRN sct
1734 WHERE sct.person_id = p_person_id AND
1735 sct.transfer_course_cd = p_course_cd AND
1736 (sct.status_date >= p_discontinued_dt OR
1737 sct.status_flag = 'U');
1738 v_sca_rec c_sca%ROWTYPE;
1739 BEGIN
1740 p_message_name := null;
1741 IF (p_discontinuation_reason_cd IS NOT NULL) THEN
1742 OPEN c_sca;
1743 FETCH c_sca INTO v_sca_rec;
1744 IF (c_sca%FOUND) THEN
1745 CLOSE c_sca;
1746 IF (v_sca_rec.closed_ind = 'Y') AND (p_legacy <> 'Y') THEN
1747 p_message_name := 'IGS_EN_DISCONT_REAS_CD_CLOS';
1748 RETURN FALSE;
1749 END IF;
1750
1751 IF (v_sca_rec.s_discontinuation_reason_type = cst_transfer AND p_legacy <> 'Y' ) THEN
1752 OPEN c_sct;
1753 FETCH c_sct INTO v_dummy;
1754 IF (c_sct%NOTFOUND) THEN
1755 CLOSE c_sct;
1756 p_message_name := 'IGS_EN_PRG_TRSF_DISCONT';
1757 RETURN FALSE;
1758 ELSE
1759 CLOSE c_sct;
1760 END IF;
1761 END IF;
1762
1763 END IF;
1764 IF (c_sca%ISOPEN) THEN
1765 CLOSE c_sca;
1766 END IF;
1767 IF (p_discontinued_dt IS NULL) THEN
1768 p_message_name := 'IGS_EN_CANT_SET_DISCONT_REASO';
1769 IF p_legacy <> 'Y' THEN
1770 RETURN FALSE;
1771 ELSE
1772 fnd_message.set_name('IGS',p_message_name);
1773 fnd_msg_pub.add;
1774 END IF;
1775 END IF;
1776 ELSIF (p_discontinued_dt IS NOT NULL) THEN
1777 p_message_name := 'IGS_EN_CANT_SET_DISCONT_DATE';
1778 IF p_legacy <> 'Y' THEN
1779 RETURN FALSE;
1780 ELSE
1781 fnd_message.set_name('IGS',p_message_name);
1782 fnd_msg_pub.add;
1783 END IF;
1784 END IF;
1785 RETURN TRUE;
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 IF (c_sca%ISOPEN) THEN
1789 CLOSE c_sca;
1790 END IF;
1791 IF (c_sct%ISOPEN) THEN
1792 CLOSE c_sct;
1793 END IF;
1794 RAISE;
1795 END;
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1799 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_dr');
1800 IGS_GE_MSG_STACK.ADD;
1801 App_Exception.Raise_Exception;
1802 END enrp_val_sca_dr;
1803 --
1804 -- To validate the course attempt against funding source restrictions
1805 FUNCTION ENRP_VAL_SCA_FSR(
1806 p_course_cd IN VARCHAR2 ,
1807 p_version_number IN NUMBER ,
1808 p_funding_source IN VARCHAR2 ,
1809 p_message_name OUT NOCOPY VARCHAR2)
1810 RETURN boolean AS
1811 BEGIN
1812 DECLARE
1813 v_rec_found BOOLEAN;
1814 v_other_detail VARCHAR2(255);
1815 CURSOR c_fund_source_rest(
1816 cp_course_cd IGS_PS_VER.course_cd%TYPE,
1817 cp_version_number IGS_PS_VER.version_number%TYPE) IS
1818 SELECT funding_source,
1819 restricted_ind
1820 FROM IGS_FI_FND_SRC_RSTN
1821 WHERE course_cd = cp_course_cd AND
1822 version_number = cp_version_number AND
1823 restricted_ind = 'Y';
1824 BEGIN
1825 -- validates the funding source for a student course attempt according
1826 -- to the IGS_FI_FND_SRC_RSTN table held against the course
1827 p_message_name := null;
1828 v_rec_found := FALSE;
1829 FOR v_fund_source_rest_rec IN c_fund_source_rest(
1830 p_course_cd,
1831 p_version_number)
1832 LOOP
1833 v_rec_found := TRUE;
1834 IF(p_funding_source = v_fund_source_rest_rec.funding_source)THEN
1835 RETURN TRUE;
1836 END IF;
1837 END LOOP;
1838 IF(v_rec_found = FALSE) THEN
1839 RETURN TRUE;
1840 END IF;
1841 p_message_name := 'IGS_AD_FUNDING_SRC_RESTRICTIO';
1842 RETURN FALSE;
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1846 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_fsr');
1847 IGS_GE_MSG_STACK.ADD;
1848 App_Exception.Raise_Exception;
1849 END;
1850 END enrp_val_sca_fsr;
1851 --
1852 -- To validate the discontinuation date and the reason cd
1853 FUNCTION enrp_val_sca_discont(
1854 p_person_id IN NUMBER ,
1855 p_course_cd IN VARCHAR2 ,
1856 p_version_number IN NUMBER ,
1857 p_course_attempt_status IN VARCHAR2 ,
1858 p_discontinuation_reason_cd IN VARCHAR2 ,
1859 p_discontinued_dt IN DATE ,
1860 p_commencement_dt IN DATE ,
1861 p_message_name OUT NOCOPY VARCHAR2,
1862 p_legacy IN VARCHAR2)
1863 RETURN BOOLEAN AS
1864 /*----------------------------------------------------------------------------
1865 || Created By :
1866 || Created On :
1867 || Purpose : Validate the IGS_EN_DCNT_REASONCD and discontinued_dt
1868 || from the IGS_EN_STDNT_PS_ATT :
1869 || * If the discontinuation_reason code is set it must not be closed
1870 || * If both discontinued_dt and commencement_dt are set then
1871 || discontinued_dt must be >= commencement_dt
1872 || * If either reason or date are set then both must be set
1873 || * If the discontinued date is not set then course version must
1874 || be active.
1875 || * If the discontinued date is set then the course attempt status
1876 || must have been enrolled, inactive, suspended, intermitted or
1877 || discontinued. NOTE: course attempt status will be set to
1878 || DISCONTIN prior to update.
1879 || * If the discontinued date is set then it must be less than or equal to
1880 || the transfer date if the cours attempt has been transferred
1881 || * If the discontinued date is set and <= today?s date then there
1882 || should be no student unit attempts enrolled.
1883 || * If the discontinued date is set, then it must be greater than the
1884 || outcome date of any completed student unit attempts.
1885 || Known limitations, enhancements or remarks :
1886 || Change History :
1887 || Who When What
1888 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
1889 || if p_legacy set to 'Y' then error message should be stacked instead of
1890 || returning the function in the normal way else function should behave in
1891 || normal way.Legacy Build Bug no: 2661533
1892 || kkillams 29-04-2003 Modified the c_sua_comp cursor where clause due to change in the pk of
1893 || student unit attempt w.r.t. bug number 2829262
1894 || bdeviset 22-Dec-2004 Modifed cursor c_sct in enrp_val_sca_discont as part Bug#4083015.
1895 ------------------------------------------------------------------------------*/
1896 BEGIN
1897 DECLARE
1898 cst_active CONSTANT IGS_PS_STAT.s_course_status%TYPE :='ACTIVE';
1899 cst_discontin CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE :='DISCONTIN';
1900 cst_enrolled CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
1901 cst_inactive CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
1902 cst_intermit CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
1903 cst_lapsed CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'LAPSED';
1904 cst_completed CONSTANT IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
1905 l_dummy_boolean BOOLEAN;
1906 CURSOR c_cv IS
1907 SELECT 'x'
1908 FROM IGS_PS_VER cv,
1909 IGS_PS_STAT cs
1910 WHERE cv.course_cd = p_course_cd AND
1911 cv.version_number = p_version_number AND
1912 cs.course_status = cv.course_status AND
1913 cs.s_course_status <> cst_active;
1914 v_cv_exists VARCHAR2(1);
1915 CURSOR c_sua_enr IS
1916 SELECT 'x'
1917 FROM IGS_EN_SU_ATTEMPT sua
1918 WHERE sua.person_id = p_person_id AND
1919 sua.course_cd = p_course_cd AND
1920 sua.unit_attempt_status = cst_enrolled;
1921 v_sua_enr_exists VARCHAR2(1);
1922 CURSOR c_sua_comp IS
1923 SELECT 'x'
1924 FROM IGS_AS_SU_STMPTOUT suao,
1925 IGS_EN_SU_ATTEMPT sua
1926 WHERE sua.person_id = p_person_id AND
1927 sua.course_cd = p_course_cd AND
1928 sua.unit_attempt_status = cst_completed AND
1929 suao.person_id = sua.person_id AND
1930 suao.course_cd = sua.course_cd AND
1931 suao.uoo_id = sua.uoo_id AND
1932 TRUNC(suao.outcome_dt) > TRUNC(p_discontinued_dt) AND
1933 suao.finalised_outcome_ind = 'Y';
1934 v_sua_comp_exists VARCHAR2(1);
1935
1936 -- Modified cursor to consider status_date instead of transfer_dt and
1937 -- status_flag is set to 'T'
1938 CURSOR c_sct IS
1939 SELECT sct.transfer_course_cd,
1940 sct.status_date
1941 FROM IGS_PS_STDNT_TRN sct
1942 WHERE sct.person_id = p_person_id AND
1943 (sct.course_cd = p_course_cd OR
1944 sct.transfer_course_cd = p_course_cd) AND
1945 sct.status_flag = 'T'
1946 ORDER BY sct.status_date desc;
1947 v_sct_transfer_actual_dt IGS_PS_STDNT_TRN.status_date%TYPE;
1948 v_sct_transfer_course_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE;
1949 v_message_name varchar2(30);
1950 BEGIN
1951 p_message_name := null;
1952 IF p_discontinued_dt IS NOT NULL THEN
1953 -- Validate that student course attempt has status that can be discontinued
1954 IF p_course_attempt_status NOT IN (cst_discontin,
1955 cst_enrolled,
1956 cst_intermit,
1957 cst_inactive,
1958 cst_lapsed) THEN
1959 p_message_name := 'IGS_EN_ONLY_SPA_ST_ENROLLED';
1960 IF p_legacy <> 'Y' THEN
1961 RETURN FALSE;
1962 ELSE
1963 fnd_message.set_name('IGS',p_message_name);
1964 fnd_msg_pub.add;
1965 END IF;
1966 END IF;
1967 -- Validate that discontinued date is not prior to course commencement
1968 IF p_discontinued_dt < p_commencement_dt THEN
1969 p_message_name := 'IGS_EN_DISCONT_DT_LT_COMM_DT';
1970 IF p_legacy <> 'Y' THEN
1971 RETURN FALSE;
1972 ELSE
1973 fnd_message.set_name('IGS',p_message_name);
1974 fnd_msg_pub.add;
1975 END IF;
1976 END IF;
1977 IF p_legacy <> 'Y' THEN
1978 -- Validate that discontinued date is not greater than the course transfer
1979 -- date if the course attempt has been transferred
1980 OPEN c_sct;
1981 FETCH c_sct INTO v_sct_transfer_course_cd, v_sct_transfer_actual_dt;
1982 IF (c_sct%FOUND) THEN
1983 IF v_sct_transfer_course_cd = p_course_cd THEN -- this indicates transfer
1984 IF v_sct_transfer_actual_dt < p_discontinued_dt THEN
1985 CLOSE c_sct;
1986 p_message_name := 'IGS_EN_DISCONT_DATE_NOT_AFTER';
1987 Return FALSE;
1988 END IF;
1989 END IF;
1990 END IF;
1991 CLOSE c_sct;
1992 END IF; --p_legacy
1993 END IF;
1994 -- Validate discontinuation reason code
1995 IF p_legacy <> 'Y' THEN
1996 IF NOT enrp_val_sca_dr(
1997 p_person_id,
1998 p_course_cd,
1999 p_discontinuation_reason_cd,
2000 p_discontinued_dt,
2001 v_message_name,
2002 p_legacy) THEN
2003 p_message_name := v_message_name;
2004 RETURN FALSE;
2005 END IF;
2006 ELSE
2007 --In legacy mode, error message is stacked instead of returning false.
2008 --So there is no significance for the return values.
2009 v_message_name := null;
2010 l_dummy_boolean:=enrp_val_sca_dr(
2011 p_person_id,
2012 p_course_cd,
2013 p_discontinuation_reason_cd,
2014 p_discontinued_dt,
2015 v_message_name,
2016 p_legacy);
2017 p_message_name := NVL(v_message_name,p_message_name);
2018 END IF;
2019 IF p_legacy <> 'Y' THEN
2020 IF p_discontinued_dt IS NULL THEN
2021 -- Validate that the course version is still active
2022 OPEN c_cv;
2023 FETCH c_cv INTO v_cv_exists;
2024 IF c_cv%FOUND THEN
2025 CLOSE c_cv;
2026 p_message_name := 'IGS_EN_PRG_VERSION_INACTIVE';
2027 RETURN FALSE;
2028 END IF;
2029 CLOSE c_cv;
2030 -- Validate course transfer links
2031 -- NOTE: Comment out NOCOPY for now because of mutuating trigger issue
2032 -- This module will be called in ENRF3000 when unit discontinuation is cleared
2033 --IF IGS_AD_VAL_SCA.enrp_val_sca_trnsfr(
2034 -- p_person_id,
2035 -- p_course_cd,
2036 -- p_discontinued_dt,
2037 -- 'E',
2038 -- v_message_name) = FALSE THEN
2039 -- p_message_name := v_message_name;
2040 -- RETURN FALSE;
2041 --END IF;
2042 ELSE -- p_discontinued_dt IS NOT NULL
2043 -- Validate that discontinued is not prior to COMPLETED IGS_EN_SU_ATTEMPTs
2044 OPEN c_sua_comp;
2045 FETCH c_sua_comp INTO v_sua_comp_exists;
2046 IF c_sua_comp%FOUND THEN
2047 CLOSE c_sua_comp;
2048 p_message_name := 'IGS_EN_SPA_NOTDISCONT_PRIOR';
2049 RETURN FALSE;
2050 END IF;
2051 CLOSE c_sua_comp;
2052 IF p_discontinued_dt <= SYSDATE THEN
2053 -- Validate that there are no enrolled student unit attempts
2054 -- *** NOTE: this must be the last validation as the form
2055 -- ENRF3000 acts on this error code
2056 OPEN c_sua_enr;
2057 FETCH c_sua_enr INTO v_sua_enr_exists;
2058 IF c_sua_enr%FOUND THEN
2059 CLOSE c_sua_enr;
2060 p_message_name := 'IGS_EN_SPA_NOTDISCONT_SUA';
2061 RETURN FALSE;
2062 END IF;
2063 CLOSE c_sua_enr;
2064 END IF;
2065 END IF;
2066 END IF; --p_legacy
2067 --- Return default value
2068 RETURN TRUE;
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 IF c_cv%ISOPEN THEN
2072 CLOSE c_cv;
2073 END IF;
2074 IF c_sua_comp%ISOPEN THEN
2075 CLOSE c_sua_comp;
2076 END IF;
2077 IF c_sua_enr%ISOPEN THEN
2078 CLOSE c_sua_enr;
2079 END IF;
2080 RAISE;
2081 END;
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2085 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_discont');
2086 IGS_GE_MSG_STACK.ADD;
2087 App_Exception.Raise_Exception;
2088 END enrp_val_sca_discont;
2089 --
2090 -- Validate the course commencement date against the students birth date
2091 FUNCTION enrp_val_sca_comm(
2092 p_person_id IN NUMBER ,
2093 p_course_cd IN VARCHAR2 ,
2094 p_commencement_dt IN DATE ,
2095 p_acad_cal_type IN VARCHAR2 ,
2096 p_acad_ci_sequence_number IN NUMBER ,
2097 p_message_name OUT NOCOPY VARCHAR2)
2098 RETURN BOOLEAN AS
2099 BEGIN -- enrp_val_sca_comm
2100 -- Validate the student course attempt commencement date, checking for:
2101 -- Warn if the commencement_dt would make the student < 16 years old
2102 -- or > 100 years old
2103 -- Validate that commencement_dt is not after the end date of student unit
2104 -- attempts with unit_attempt_status other than 'UNCONFIRM', 'DUPLICATE'.
2105 -- If the course attempt originates from an offer other than process
2106 -- type TRANSFER, the date cannot be prior to the course start date of the
2107 -- students admission period OR the if research candidature is mandatory part
2108 -- of the offer, prior to the valid values for
2109 -- IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
2110 -- If the course attempt is the result of a course transfer from a generic
2111 -- course, then the date can be no earlier than the commencement date of the
2112 -- originating course attempt.
2113 -- If the course attempt is a result of a course transfer from a IGS_PS_COURSE
2114 -- attempt with research candidature, then the date can be no earlier than
2115 -- the commencement date of the transfer course attempt.
2116 -- If the course attempt doesn't originate from an offer, the date cannot be
2117 -- prior to the academic period commencement date if date of processing
2118 -- is after academic period commencement date, otherwise cannot be prior
2119 -- to current date
2120 -- If the course attempt has a research candidature, then commencement_dt
2121 -- must comply with the candidature minimum submission date
2122 -- and supervisor requirements.
2123 -- Change History :
2124 -- Who When What
2125 -- stutta 07-02-2006 Modified c_person for performance bug#5023479
2126 DECLARE
2127 --Constants
2128 cst_low_months CONSTANT NUMBER := 192; -- 16 years in months
2129 cst_high_months CONSTANT NUMBER := 1200;-- 100 years in months
2130 cst_transfer CONSTANT VARCHAR2(10) := 'TRANSFER';
2131 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
2132 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
2133 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
2134 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
2135 cst_sca CONSTANT VARCHAR2(4) := 'SCA';
2136 -- Variables
2137 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
2138 v_course_start_dt IGS_CA_DA_INST_V.alias_val%TYPE;
2139 v_birth_dt IGS_PE_PERSON.birth_dt%TYPE;
2140 v_candidature_exists_ind VARCHAR2(1);
2141 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
2142 v_message_name varchar2(30);
2143 -- Temporary variables
2144 v_dt_diff NUMBER;
2145 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
2146 v_only_one_rec_found BOOLEAN := FALSE;
2147 v_commencement_dt_validated BOOLEAN;
2148 CURSOR c_sct_sca_crv IS
2149 SELECT sca.commencement_dt
2150 FROM IGS_PS_STDNT_TRN sct,
2151 IGS_EN_STDNT_PS_ATT sca,
2152 IGS_PS_VER crv
2153 WHERE sct.person_id = p_person_id AND
2154 sca.person_id = sct.person_id AND
2155 sct.course_cd = p_course_cd AND
2156 sca.course_cd = sct.transfer_course_cd AND
2157 crv.course_cd = sca.course_cd AND
2158 crv.version_number = sca.version_number AND
2159 crv.generic_course_ind = 'Y'
2160 ORDER BY sca.commencement_dt ASC;
2161 CURSOR c_sca IS
2162 SELECT sca.version_number,
2163 sca.person_id,
2164 sca.adm_admission_appl_number,
2165 sca.adm_nominated_course_cd,
2166 sca.adm_sequence_number
2167 FROM IGS_EN_STDNT_PS_ATT sca
2168 WHERE sca.person_id = p_person_id AND
2169 sca.course_cd = p_course_cd;
2170 v_sca_rec c_sca%ROWTYPE;
2171 CURSOR c_acaiv (
2172 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2173 cp_adm_adm_appl_num IGS_EN_STDNT_PS_ATT.adm_admission_appl_number%TYPE,
2174 cp_adm_nom_course_cd IGS_EN_STDNT_PS_ATT.adm_nominated_course_cd%TYPE,
2175 cp_adm_sequence_number IGS_EN_STDNT_PS_ATT.adm_sequence_number%TYPE) IS
2176 SELECT acaiv.admission_appl_number,
2177 acaiv.nominated_course_cd,
2178 acaiv.sequence_number,
2179 acaiv.adm_cal_type,
2180 acaiv.adm_ci_sequence_number
2181 FROM IGS_AD_PS_APPL_INST_APLINST_V acaiv
2182 WHERE acaiv.person_id = cp_person_id AND
2183 acaiv.admission_appl_number = cp_adm_adm_appl_num AND
2184 acaiv.nominated_course_cd = cp_adm_nom_course_cd AND
2185 acaiv.sequence_number = cp_adm_sequence_number;
2186 v_acaiv_rec c_acaiv%ROWTYPE;
2187 CURSOR c_person IS
2188 SELECT birth_date date_of_birth
2189 FROM igs_pe_person_base_v
2190 WHERE person_id = p_person_id;
2191 CURSOR c_daiv_secc IS
2192 SELECT daiv.alias_val
2193 FROM IGS_CA_DA_INST_V daiv,
2194 IGS_EN_CAL_CONF secc
2195 WHERE daiv.cal_type = p_acad_cal_type AND
2196 daiv.ci_sequence_number = p_acad_ci_sequence_number AND
2197 secc.commencement_dt_alias = daiv.dt_alias AND
2198 secc.s_control_num = 1;
2199 CURSOR c_aa (
2200 cp_person_id IGS_AD_APPL.person_id%TYPE,
2201 cp_adm_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE) IS
2202 SELECT aa.s_admission_process_type
2203 FROM IGS_AD_APPL aa
2204 WHERE aa.person_id = cp_person_id AND
2205 aa.admission_appl_number = cp_adm_admission_appl_number;
2206 v_aa_rec c_aa%ROWTYPE;
2207 CURSOR c_sua_ci IS
2208 SELECT 'x'
2209 FROM IGS_EN_SU_ATTEMPT sua,
2210 IGS_CA_INST ci
2211 WHERE sua.person_id = p_person_id AND
2212 sua.course_cd = p_course_cd AND
2213 sua.unit_attempt_status IN(
2214 cst_enrolled,
2215 cst_discontin,
2216 cst_invalid,
2217 cst_completed) AND
2218 sua.cal_type = ci.cal_type AND
2219 sua.ci_sequence_number = ci.sequence_number AND
2220 sua.ci_end_dt < p_commencement_dt;
2221 v_sua_ci_exists VARCHAR2(1);
2222 CURSOR c_sct_ca_sca IS
2223 SELECT sca.commencement_dt
2224 FROM IGS_PS_STDNT_TRN sct,
2225 IGS_RE_CANDIDATURE ca,
2226 IGS_EN_STDNT_PS_ATT sca
2227 WHERE sct.person_id = p_person_id AND
2228 sct.course_cd = p_course_cd AND
2229 sct.person_id = ca.person_id AND
2230 sct.transfer_course_cd = ca.sca_course_cd AND
2231 ca.person_id = sca.person_id AND
2232 ca.sca_course_cd = sca.course_cd
2233 ORDER BY sct.status_date desc; -- (use latest record)
2234 FUNCTION enrpl_val_ca_start_dt
2235 RETURN BOOLEAN
2236 AS
2237 BEGIN --enrpl_val_ca_start_dt
2238 -- Validate candidature start date
2239 -- Validate against candidature commencement if candidature exists for the
2240 -- course attempt created via pre-enrolment
2241 DECLARE
2242 BEGIN
2243 IF NOT admp_val_ca_comm_val(
2244 p_person_id,
2245 v_acaiv_rec.admission_appl_number,
2246 v_acaiv_rec.nominated_course_cd,
2247 v_acaiv_rec.sequence_number,
2248 v_acaiv_rec.adm_cal_type,
2249 v_acaiv_rec.adm_ci_sequence_number,
2250 v_course_start_dt,
2251 p_commencement_dt,
2252 cst_sca,
2253 v_message_name) THEN
2254 p_message_name := v_message_name;
2255 RETURN FALSE;
2256 ELSE
2257 IF v_message_name IS NOT NULL THEN
2258 p_message_name := v_message_name;
2259 RETURN TRUE;
2260 END IF;
2261 END IF;
2262 RETURN TRUE;
2263 END;
2264 EXCEPTION
2265 WHEN OTHERS THEN
2266 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2267 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrpl_val_ca_start_dt');
2268 IGS_GE_MSG_STACK.ADD;
2269 App_Exception.Raise_Exception;
2270 END enrpl_val_ca_start_dt;
2271 BEGIN
2272 p_message_name := null;
2273 IF p_commencement_dt is NULL THEN
2274 RETURN TRUE;
2275 END IF;
2276 OPEN c_sca;
2277 FETCH c_sca INTO v_sca_rec;
2278 IF c_sca%NOTFOUND THEN
2279 CLOSE c_sca;
2280 RETURN TRUE;
2281 END IF;
2282 CLOSE c_sca;
2283 -- Validate that commencement date is not after the end date of any enrolled
2284 -- student unit attempts.
2285 OPEN c_sua_ci;
2286 FETCH c_sua_ci INTO v_sua_ci_exists;
2287 IF c_sua_ci%FOUND THEN
2288 CLOSE c_sua_ci;
2289 p_message_name := 'IGS_EN_COMMENC_DT_NOTBE_AFTER';
2290 RETURN FALSE;
2291 END IF;
2292 CLOSE c_sua_ci;
2293 --Validate research candidature details, if they exists
2294 v_candidature_exists_ind := 'N';
2295 IF admp_val_ca_comm(
2296 p_person_id,
2297 p_course_cd,
2298 v_sca_rec.version_number,
2299 v_sca_rec.adm_admission_appl_number,
2300 v_sca_rec.adm_nominated_course_cd,
2301 v_sca_rec.adm_sequence_number,
2302 NULL, -- admission outcome status
2303 p_commencement_dt,
2304 NULL, -- (minimun submission date)
2305 cst_sca,-- (indicates context is student course attempt)
2306 v_ca_sequence_number,
2307 v_candidature_exists_ind,
2308 v_message_name) = FALSE THEN
2309 p_message_name := v_message_name;
2310 RETURN FALSE;
2311 END IF;
2312 -- Check whether the course attempt has resulted in a transfer from a generic
2313 -- course.
2314 v_commencement_dt_validated := FALSE;
2315 OPEN c_sct_sca_crv;
2316 FETCH c_sct_sca_crv INTO v_commencement_dt;
2317 IF c_sct_sca_crv%FOUND THEN
2318 CLOSE c_sct_sca_crv;
2319 IF p_commencement_dt < v_commencement_dt THEN
2320 p_message_name := 'IGS_EN_COMDT_NOTEARLIER_COMDT';
2321 RETURN FALSE;
2322 ELSE
2323 v_commencement_dt_validated := TRUE;
2324 END IF;
2325 ELSE
2326 CLOSE c_sct_sca_crv;
2327 END IF;
2328 -- Check whether course attempt is the result of a course transfer that has
2329 -- associated research candidature
2330 IF NOT v_commencement_dt_validated AND
2331 v_candidature_exists_ind = 'Y' THEN
2332 OPEN c_sct_ca_sca;
2333 FETCH c_sct_ca_sca INTO v_commencement_dt;
2334 IF c_sct_ca_sca%FOUND THEN
2335 CLOSE c_sct_ca_sca;
2336 IF p_commencement_dt < v_commencement_dt THEN
2337 p_message_name := 'IGS_EN_PRG_COMMENCE_DT';
2338 RETURN FALSE;
2339 ELSE
2340 v_commencement_dt_validated := TRUE;
2341 END IF;
2342 ELSE
2343 CLOSE c_sct_ca_sca;
2344 END IF;
2345 END IF;
2346 IF NOT v_commencement_dt_validated THEN
2347 IF v_sca_rec.adm_admission_appl_number IS NOT NULL THEN
2348 -- The enrolment has originated from an admissions offer, so it cannot be
2349 -- earlier than the course start date.
2350 OPEN c_acaiv(
2351 v_sca_rec.person_id,
2352 v_sca_rec.adm_admission_appl_number,
2353 v_sca_rec.adm_nominated_course_cd,
2354 v_sca_rec.adm_sequence_number);
2355 FETCH c_acaiv INTO v_acaiv_rec;
2356 CLOSE c_acaiv;
2357 v_course_start_dt:= IGS_AD_GEN_005.ADMP_GET_CRV_STRT_DT(
2358 v_acaiv_rec.adm_cal_type,
2359 v_acaiv_rec.adm_ci_sequence_number);
2360 IF v_course_start_dt IS NOT NULL THEN
2361 IF p_commencement_dt < v_course_start_dt THEN
2362 -- Determine if admission application is a course transfer
2363 OPEN c_aa(
2364 v_sca_rec.person_id,
2365 v_sca_rec.adm_admission_appl_number);
2366 FETCH c_aa INTO v_aa_rec;
2367 CLOSE c_aa;
2368 IF v_aa_rec.s_admission_process_type = cst_transfer THEN
2369 IF p_commencement_dt < TRUNC(SYSDATE) THEN
2370 IF v_candidature_exists_ind ='Y' THEN
2371 -- validate candidature start date
2372 RETURN enrpl_val_ca_start_dt;
2373 -- comparison between current date and spa commencement date removed for Bug 3853476
2374 ELSE
2375 RETURN enrf_val_sua_term_sca_comm(
2376 p_person_id,
2377 p_course_cd,
2378 p_commencement_dt,
2379 p_message_name);
2380
2381 END IF;
2382 END IF;
2383 ELSE
2384 IF v_candidature_exists_ind ='Y' THEN
2385 -- validate candidature start date
2386 RETURN enrpl_val_ca_start_dt;
2387 ELSE
2388 -- For Bug 3853476 this message has to be shown as warning....hence the function shall return true henceforth
2389 p_message_name := 'IGS_EN_COMDT_NOTEARLIER_STDT';
2390 RETURN TRUE;
2391 END IF;
2392 END IF;
2393 END IF;
2394 ELSIF p_commencement_dt < TRUNC(SYSDATE) THEN
2395 IF v_candidature_exists_ind = 'Y' THEN
2396 --validate cadidature start date
2397 RETURN enrpl_val_ca_start_dt;
2398 -- comparison between current date and spa commencement date removed for Bug 3853476
2399 ELSE
2400 RETURN enrf_val_sua_term_sca_comm(
2401 p_person_id,
2402 p_course_cd,
2403 p_commencement_dt,
2404 p_message_name);
2405 END IF;
2406 END IF;
2407 ELSE
2408 -- check if the earliest term calendar in which there is a unit attempt
2409 -- has an end dt after the commencement date.
2410 IF NOT enrf_val_sua_term_sca_comm(
2411 p_person_id,
2412 p_course_cd,
2413 p_commencement_dt,
2414 p_message_name) THEN
2415 RETURN FALSE;
2416 END IF;
2417 -- v_sca_rec.adm_admission_appl_number IS NULL
2418 -- Check that the commencement date is not prior to the academic
2419 -- period commencement date if date of processing is after the
2420 -- academic period commencement date, otherwise must be >= current date.
2421 v_only_one_rec_found := FALSE;
2422 FOR v_daiv_secc_rec IN c_daiv_secc LOOP
2423 v_only_one_rec_found := TRUE;
2424 IF c_daiv_secc%ROWCOUNT > 1 THEN
2425 v_only_one_rec_found := FALSE;
2426 EXIT;
2427 END IF;
2428 v_alias_val := v_daiv_secc_rec.alias_val;
2429 END LOOP;
2430 IF v_only_one_rec_found AND v_alias_val < TRUNC(SYSDATE) AND p_commencement_dt < v_alias_val THEN
2431 -- For Bug 3853476 this message has to be shown as warning....hence the function shall return true henceforth
2432 p_message_name := 'IGS_EN_SPA_COMMEN_DT';
2433 RETURN TRUE;
2434 END IF;
2435 END IF; -- v_adm_adm_appl_number IS NOT NULL
2436 END IF; -- v_commencement_dt_validated
2437 --Retrieve the birth_dt from IGS_PE_PERSON where person_id = p_person_id
2438 OPEN c_person;
2439 FETCH c_person INTO v_birth_dt;
2440 CLOSE c_person;
2441 IF v_birth_dt IS NULL THEN
2442 RETURN TRUE;
2443 END IF;
2444 v_dt_diff := MONTHS_BETWEEN(p_commencement_dt, v_birth_dt);
2445 IF v_dt_diff < cst_low_months THEN
2446 p_message_name := 'IGS_EN_STUDENT_LT_16YRS';
2447 RETURN TRUE;
2448 END IF;
2449 IF v_dt_diff > cst_high_months THEN
2450 p_message_name := 'IGS_EN_STUDENT_GT_100YRS';
2451 RETURN TRUE;
2452 END IF;
2453 RETURN TRUE; -- NOTE: no false return as its only a warning
2454 EXCEPTION
2455 WHEN OTHERS THEN
2456 IF c_sct_sca_crv%ISOPEN THEN
2457 CLOSE c_sct_sca_crv;
2458 END IF;
2459 IF c_sca%ISOPEN THEN
2460 CLOSE c_sca;
2461 END IF;
2462 IF c_acaiv%ISOPEN THEN
2463 CLOSE c_acaiv;
2464 END IF;
2465 IF c_person%ISOPEN THEN
2466 CLOSE c_person;
2467 END IF;
2468 IF c_daiv_secc%ISOPEN THEN
2469 CLOSE c_daiv_secc;
2470 END IF;
2471 IF c_aa%ISOPEN THEN
2472 CLOSE c_aa;
2473 END IF;
2474 IF c_sua_ci%ISOPEN THEN
2475 CLOSE c_sua_ci;
2476 END IF;
2477 IF c_sct_ca_sca%ISOPEN THEN
2478 CLOSE c_sct_ca_sca;
2479 END IF;
2480 RAISE;
2481 END;
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2485 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_comm');
2486 IGS_GE_MSG_STACK.ADD;
2487 App_Exception.Raise_Exception;
2488 END enrp_val_sca_comm;
2489 --
2490 -- To validate the student course attempt funding source
2491 FUNCTION ENRP_VAL_SCA_FS(
2492 p_course_cd IN VARCHAR2 ,
2493 p_version_number IN NUMBER ,
2494 p_funding_source IN VARCHAR2 ,
2495 p_message_name OUT NOCOPY VARCHAR2)
2496 RETURN BOOLEAN AS
2497 BEGIN
2498 DECLARE
2499 v_closed_ind IGS_FI_FUND_SRC.closed_ind%TYPE;
2500 v_fsr_fs_exists VARCHAR2(1) := 'N';
2501 v_fsr_cv_exists VARCHAR2(1) := 'N';
2502 CURSOR c_fs_closed_ind IS
2503 SELECT closed_ind
2504 FROM IGS_FI_FUND_SRC
2505 WHERE funding_source = p_funding_source;
2506 CURSOR c_chk_fsr_fs IS
2507 SELECT 'Y'
2508 FROM IGS_FI_FND_SRC_RSTN
2509 WHERE course_cd = p_course_cd AND
2510 version_number = p_version_number AND
2511 funding_source = p_funding_source AND
2512 restricted_ind = 'Y';
2513 CURSOR c_chk_fsr_cv IS
2514 SELECT 'Y'
2515 FROM IGS_FI_FND_SRC_RSTN
2516 WHERE course_cd = p_course_cd AND
2517 version_number = p_version_number AND
2518 restricted_ind = 'Y';
2519 BEGIN
2520 -- This module validates the IGS_FI_FUND_SRC
2521 -- from the IGS_EN_STDNT_PS_ATT.
2522 -- checking if p_funding_source is not set
2523 IF (p_funding_source IS NULL) THEN
2524 p_message_name := null;
2525 RETURN TRUE;
2526 END IF;
2527 -- checking whether the IGS_FI_FUND_SRC is
2528 -- closed in the IGS_FI_FUND_SRC table
2529 OPEN c_fs_closed_ind;
2530 FETCH c_fs_closed_ind INTO v_closed_ind;
2531 CLOSE c_fs_closed_ind;
2532 IF (v_closed_ind = 'Y') THEN
2533 p_message_name := 'IGS_PS_FUND_SOURCE_CLOSED';
2534 RETURN FALSE;
2535 END IF;
2536 -- the IGS_FI_FUND_SRC isn't closed in
2537 -- the IGS_FI_FUND_SRC table so
2538 -- check that the IGS_FI_FUND_SRC in the
2539 -- IGS_FI_FND_SRC_RSTN table
2540 -- doesn't breach existing restrictions
2541 OPEN c_chk_fsr_cv;
2542 FETCH c_chk_fsr_cv INTO v_fsr_cv_exists;
2543 CLOSE c_chk_fsr_cv;
2544 -- If any restrictions exist for this course version, then one of them must
2545 -- be for the given funding source.
2546 IF v_fsr_cv_exists = 'Y' THEN
2547 OPEN c_chk_fsr_fs;
2548 FETCH c_chk_fsr_fs INTO v_fsr_fs_exists;
2549 CLOSE c_chk_fsr_fs;
2550 IF v_fsr_fs_exists = 'N' THEN
2551 p_message_name := 'IGS_EN_FUND_SOURCE_NOT_ALLOWD';
2552 RETURN FALSE;
2553 END IF;
2554 END IF;
2555 -- there were no closed_inds, and no IGS_FI_FUND_SRC
2556 -- restrictions
2557 p_message_name := null;
2558 RETURN TRUE;
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 IF c_fs_closed_ind%ISOPEN THEN
2562 CLOSE c_fs_closed_ind;
2563 END IF;
2564 IF c_chk_fsr_fs%ISOPEN THEN
2565 CLOSE c_chk_fsr_fs;
2566 END IF;
2567 IF c_chk_fsr_cv%ISOPEN THEN
2568 CLOSE c_chk_fsr_cv;
2569 END IF;
2570 RAISE;
2571 END;
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2575 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.ENRP_VAL_SCA_FS');
2576 IGS_GE_MSG_STACK.ADD;
2577 App_Exception.Raise_Exception;
2578 END ENRP_VAL_SCA_FS;
2579 --
2580 -- Validate the IGS_PS_OFR_PAT for a IGS_EN_STDNT_PS_ATT
2581 FUNCTION enrp_val_sca_cop(
2582 p_course_cd IN VARCHAR2 ,
2583 p_version_number IN NUMBER ,
2584 p_location_cd IN VARCHAR2 ,
2585 p_attendance_type IN VARCHAR2 ,
2586 p_attendance_mode IN VARCHAR2 ,
2587 p_cal_type IN VARCHAR2 ,
2588 p_ci_sequence_number IN NUMBER ,
2589 p_message_name OUT NOCOPY VARCHAR2)
2590 RETURN BOOLEAN AS
2591 BEGIN
2592 BEGIN
2593 DECLARE
2594 v_other_detail VARCHAR2(255);
2595 v_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
2596 v_ci_rec IGS_CA_INST%ROWTYPE;
2597 v_cs_scs IGS_CA_STAT.s_cal_status%TYPE;
2598 cst_active CONSTANT VARCHAR2(8):= 'ACTIVE';
2599 CURSOR c_cop_rec IS
2600 SELECT *
2601 FROM IGS_PS_OFR_PAT
2602 WHERE course_cd = p_course_cd AND
2603 version_number = p_version_number AND
2604 location_cd = p_location_cd AND
2605 attendance_mode = p_attendance_mode AND
2606 attendance_type = p_attendance_type AND
2607 cal_type = p_cal_type AND
2608 ci_sequence_number = p_ci_sequence_number;
2609 CURSOR c_ci_rec IS
2610 SELECT *
2611 FROM IGS_CA_INST
2612 WHERE cal_type = p_cal_type AND
2613 sequence_number = p_ci_sequence_number;
2614 CURSOR c_cs_scs IS
2615 SELECT s_cal_status
2616 FROM IGS_CA_STAT,
2617 IGS_CA_INST
2618 WHERE IGS_CA_STAT.cal_status = v_ci_rec.cal_status;
2619 -- WHERE IGS_CA_STAT.cal_status = IGS_CA_INST.cal_status;
2620 BEGIN
2621 -- This module validates the IGS_PS_OFR_PAT
2622 -- for the curent IGS_EN_STDNT_PS_ATT.
2623 -- checking if the IGS_PS_OFR_PAT
2624 -- offered_ind is set to 'N'
2625 OPEN c_cop_rec;
2626 FETCH c_cop_rec INTO v_cop_rec;
2627 -- a record has been found
2628 IF (c_cop_rec%FOUND) THEN
2629 -- if the IGS_PS_OFR_PAT offered_ind
2630 -- is set to 'N'
2631 IF (v_cop_rec.offered_ind = 'N') THEN
2632 CLOSE c_cop_rec;
2633 p_message_name := 'IGS_EN_INVALID_STUD_CRS_OFFER';
2634 RETURN FALSE;
2635 -- if the IGS_PS_OFR_PAT offered_ind
2636 -- is set to 'Y'
2637 ELSE
2638 IF (v_cop_rec.offered_ind = 'Y') THEN
2639 OPEN c_ci_rec;
2640 FETCH c_ci_rec INTO v_ci_rec;
2641 OPEN c_cs_scs;
2642 FETCH c_cs_scs INTO v_cs_scs;
2643 -- the offered_ind for IGS_PS_OFR_PAT was
2644 -- set to 'Y' and the s_cal_status is not set to 'ACTIVE'
2645 IF (v_cs_scs <> 'ACTIVE') THEN
2646 CLOSE c_cs_scs;
2647 CLOSE c_ci_rec;
2648 CLOSE c_cop_rec;
2649 p_message_name := 'IGS_EN_CAL_INST_NOT_ACTIVE';
2650 RETURN FALSE;
2651 ELSE
2652 IF (v_cs_scs = 'ACTIVE') THEN
2653 -- the offered_ind for IGS_PS_OFR_PAT was
2654 -- set to 'Y' and the s_cal_status is set to 'ACTIVE'
2655 CLOSE c_cs_scs;
2656 CLOSE c_ci_rec;
2657 CLOSE c_cop_rec;
2658 p_message_name := null;
2659 RETURN TRUE;
2660 END IF;
2661 END IF;
2662 END IF;
2663 END IF;
2664 ELSE
2665 -- a record hasn't been found
2666 CLOSE c_cop_rec;
2667 p_message_name := 'IGS_EN_INVALID_STUD_CRS_OFFER';
2668 RETURN FALSE;
2669 END IF;
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2673 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.enrp_val_sca_cop');
2674 IGS_GE_MSG_STACK.ADD;
2675 App_Exception.Raise_Exception;
2676 END;
2677 END enrp_val_sca_cop;
2678 END;
2679 --
2680
2681 -- A FUNCTION enrp_val_sca_fc in this package has been removed as this will not be invoked
2682 -- as per the build changes for the Fee clac Build (Bug 1851586)
2683 -- This function validates whether the Student Program Attempt had an assessment
2684 -- record with the specified Fee Category.
2685 -- was invoked from IGS_EN_STDNT_PS_ATT_PKG.
2686
2687 --
2688 -- Validate if IGS_FI_FEE_CAT.fee_cat is closed.
2689 FUNCTION finp_val_fc_closed(
2690 p_fee_cat IN VARCHAR2 ,
2691 p_message_name OUT NOCOPY VARCHAR2)
2692 RETURN BOOLEAN AS
2693 BEGIN --FINP_VAL_FC_CLOSED
2694 --Validate if IGS_FI_FEE_CAT.fee_cat is closed
2695 DECLARE
2696 v_closed_ind IGS_FI_FEE_CAT.closed_ind%type;
2697 CURSOR c_fc IS
2698 SELECT fc.closed_ind
2699 FROM IGS_FI_FEE_CAT fc
2700 WHERE fc.fee_cat = p_fee_cat;
2701 BEGIN
2702 --- Set the default message number
2703 p_message_name := null;
2704 OPEN c_fc;
2705 FETCH c_fc INTO v_closed_ind;
2706 IF (c_fc%FOUND)THEN
2707 IF (v_closed_ind = 'Y') THEN
2708 p_message_name := 'IGS_FI_FEECAT_CLOSED';
2709 CLOSE c_fc;
2710 RETURN FALSE;
2711 END IF;
2712 END IF;
2713 CLOSE c_fc;
2714 RETURN TRUE;
2715 END;
2716 /*
2717 EXCEPTION
2718 WHEN OTHERS THEN
2719 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2720 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCA.finp_val_fc_closed');
2721 IGS_GE_MSG_STACK.ADD;
2722 App_Exception.Raise_Exception;
2723 */
2724 END finp_val_fc_closed;
2725
2726 FUNCTION enrf_val_sua_term_sca_comm(
2727 p_person_id IN NUMBER,
2728 p_course_cd IN VARCHAR2,
2729 p_commencement_dt IN DATE,
2730 p_message_name OUT NOCOPY VARCHAR2
2731 ) RETURN BOOLEAN AS
2732
2733 CURSOR c_sua_term (cp_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,
2734 cp_course_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE) IS
2735 SELECT tlv.LOAD_END_DT
2736 From igs_ca_teach_to_load_V tlv,
2737 igs_en_su_attempt sua
2738 Where sua.person_id = cp_person_id
2739 AND sua.course_cd = cp_course_cd
2740 AND sua.unit_attempt_status NOT IN ('DROPPED','UNCONFIRM')
2741 AND teach_cal_type = sua.cal_type
2742 And teach_ci_sequence_number = sua.ci_sequence_number
2743 Order by LOAD_START_DT asc;
2744
2745 v_load_end_dt IGS_CA_INST.END_DT%TYPE;
2746
2747 BEGIN
2748 -- cursor selects the earlier term calendar in which there is an
2749 -- active unit attempt for the passed in person and program.
2750 OPEN c_sua_term(p_person_id, p_course_cd);
2751 FETCH c_sua_term INTO v_load_end_dt;
2752 IF c_sua_term%FOUND THEN
2753 CLOSE c_sua_term;
2754 -- if the commencement date is greater than the end date
2755 -- of the earliest term calendar then return false
2756 IF p_commencement_dt > v_load_end_dt THEN
2757 p_message_name := 'IGS_EN_COMM_LESS_SUA_TERM';
2758 RETURN FALSE;
2759 END IF;
2760 ELSE
2761 CLOSE c_sua_term;
2762 END IF;
2763
2764 RETURN TRUE;
2765
2766 END enrf_val_sua_term_sca_comm;
2767
2768 FUNCTION del_unconfirm_sua_for_reopen(
2769 p_person_id IN IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2770 p_course_cd IN IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
2771 RETURN BOOLEAN
2772 AS
2773 CURSOR c_sua IS
2774 SELECT uoo_id
2775 FROM IGS_EN_SU_ATTEMPT sua
2776 WHERE sua.person_id = p_person_id
2777 AND sua.course_cd = p_course_cd
2778 AND sua.unit_attempt_status = 'UNCONFIRM' ;
2779 returnFlag BOOLEAN := TRUE;
2780 BEGIN
2781 FOR v_sua_rec IN c_sua LOOP
2782
2783 IF IGS_EN_FUTURE_DT_TRANS.del_sua_for_reopen(p_person_id,p_course_cd,v_sua_rec.uoo_id) <> TRUE THEN
2784 returnFlag := FALSE;
2785
2786 EXIT ;
2787 END IF;
2788 END LOOP;
2789
2790 RETURN returnFlag ;
2791 END del_unconfirm_sua_for_reopen;
2792
2793 FUNCTION validate_unconfirm_program(
2794 cp_rowid ROWID)
2795 RETURN BOOLEAN
2796 IS
2797 --cursor to fetch the program attempts for the admission application in context
2798 Cursor cur_spa(cp_rowid ROWID) IS
2799 Select spa.rowid,spa.*
2800 from igs_en_stdnt_ps_att_all spa
2801 where spa.rowid = cp_rowid;
2802
2803 --cursor to check if any unit attempts other than unconfirmed unit attempts
2804 --exist for a program attempt
2805 Cursor cur_sua(cp_person_id NUMBER,
2806 cp_course_cd VARCHAR2) IS
2807 Select 'x'
2808 from IGS_EN_SU_ATTEMPT_ALL sua
2809 where sua.person_id=cp_person_id
2810 and sua.course_cd=cp_course_cd
2811 and sua.unit_attempt_status <> 'UNCONFIRM';
2812
2813 --cursor to fetch unit set attempts for the admission application in context
2814 Cursor cur_susa(cp_person_id NUMBER,
2815 cp_course_cd VARCHAR2) IS
2816 Select susa.rowid,susa.*
2817 from igs_as_su_setatmpt susa
2818 where susa.person_id=cp_person_id
2819 and susa.course_cd=cp_course_cd;
2820
2821 --cursor to check unconfirm unit attempts exist
2822 Cursor cur_sua_unconfirm(cp_person_id NUMBER,
2823 cp_course_cd VARCHAR2) IS
2824 Select 'x'
2825 from IGS_EN_SU_ATTEMPT_ALL sua
2826 where sua.person_id=cp_person_id
2827 and sua.course_cd=cp_course_cd
2828 and sua.unit_attempt_status = 'UNCONFIRM';
2829
2830 --cursor to find course type
2831 Cursor cur_ps_ctype(cp_course_cd VARCHAR2,
2832 cp_version_number NUMBER,
2833 cp_person_id NUMBER) IS
2834 Select ps.course_type
2835 from igs_ps_ver ps,
2836 igs_en_stdnt_ps_att sca
2837 where ps.course_cd=cp_course_cd
2838 and ps.version_number = cp_version_number
2839 and sca.course_cd = ps.course_cd
2840 and sca.version_number = ps.version_number
2841 and sca.person_id = cp_person_id;
2842
2843 --Cursor to check secondary prgoram exist for a career
2844 Cursor cur_ps_sec(cp_person_id NUMBER,
2845 cp_course_type VARCHAR2) IS
2846 SELECT spa.course_cd
2847 FROM igs_en_stdnt_ps_att spa,
2848 igs_ps_ver pv
2849 WHERE spa.person_id = cp_person_id
2850 AND spa.primary_program_type = 'SECONDARY'
2851 AND spa.STUDENT_CONFIRMED_IND = 'Y'
2852 AND spa.course_cd = pv.course_cd
2853 AND spa.version_number = pv.version_number
2854 AND pv.course_type = cp_course_type;
2855
2856 -- Cursor to check secondry program is destination of a future
2857 CURSOR cur_term_cal(cp_person_id NUMBER,
2858 cp_course_cd VARCHAR2) IS
2859 SELECT effective_term_cal_type,effective_term_sequence_num
2860 FROM IGS_PS_STDNT_TRN trnsf
2861 WHERE trnsf.person_id = cp_person_id
2862 AND trnsf.course_cd = cp_course_cd
2863 AND trnsf.STATUS_FLAG = 'U' ;
2864
2865 CURSOR cur_pri_prg(cp_person_id NUMBER,
2866 cp_course_type VARCHAR2) IS
2867 SELECT 'x'
2868 FROM igs_en_stdnt_ps_att spa,
2869 igs_ps_ver pv
2870 WHERE spa.person_id = cp_person_id
2871 AND spa.primary_program_type = 'PRIMARY'
2872 AND spa.course_cd = pv.course_cd
2873 AND spa.version_number = pv.version_number
2874 AND pv.course_type <> cp_course_type;
2875
2876 CURSOR cur_confirm_prg(cp_person_id NUMBER,
2877 cp_course_cd VARCHAR2) IS
2878 SELECT 'x'
2879 FROM igs_en_stdnt_ps_att spa
2880 WHERE spa.person_id = cp_person_id
2881 AND spa.course_cd <> cp_course_cd
2882 AND spa.student_confirmed_ind = 'Y' ;
2883
2884
2885 l_sua_check VARCHAR2(1);
2886 l_sua_unconfirm_check VARCHAR2(1);
2887 l_career igs_ps_ver.course_type%TYPE;
2888 l_sec_courseCD igs_en_stdnt_ps_att.course_cd%TYPE;
2889 l_primaryInd igs_en_stdnt_ps_att.primary_program_type%TYPE;
2890 l_pri_prg VARCHAR2(1);
2891
2892 BEGIN
2893 -- fetch all program attempts using the application context parameters passed.
2894 --loop through the program attempts found
2895 FOR vcur_spa IN cur_spa(cp_rowid) LOOP
2896
2897 l_primaryInd := vcur_spa.primary_program_type;
2898 -- check if any unit attempts exist for the student and program
2899 -- which are in a status other than Unconfirmed.
2900 OPEN cur_sua(vcur_spa.person_id,vcur_spa.course_cd);
2901 FETCH cur_sua INTO l_sua_check;
2902 -- if unit attempts in status other than in UNCONFIRM status exist
2903 IF cur_sua%FOUND THEN
2904 -- program attempt status cannot be changed
2905 -- have to pass back program code as part of error message to be displayed in admissions
2906
2907 CLOSE cur_sua;
2908
2909 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_PROG_FAIL');
2910 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
2911 IGS_GE_MSG_STACK.ADD;
2912
2913 RETURN FALSE;
2914 ELSE
2915 -- only unit attempts in status UNCONFIRM or no unit attempts exist
2916 CLOSE cur_sua;
2917
2918 --do program attempt processing
2919 --check if system is in career mode
2920 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
2921
2922 -- perform logic for primary and secondary programs
2923 -- if program being processed is primary and confirmed
2924 IF NVL(vcur_spa.PRIMARY_PROGRAM_TYPE,'SECONDARY') = 'PRIMARY'
2925 AND vcur_spa.STUDENT_CONFIRMED_IND = 'Y' THEN
2926
2927 OPEN cur_ps_ctype(vcur_spa.course_cd,vcur_spa.VERSION_NUMBER,vcur_spa.person_id);
2928 FETCH cur_ps_ctype into l_career;
2929 CLOSE cur_ps_ctype;
2930
2931
2932 IF vcur_spa.key_program = 'Y' THEN
2933 OPEN cur_pri_prg(vcur_spa.person_id,l_career);
2934 FETCH cur_pri_prg INTO l_pri_prg;
2935 IF cur_pri_prg%FOUND THEN
2936 CLOSE cur_pri_prg;
2937 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_KEYPRG_FAIL');
2938 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
2939 IGS_GE_MSG_STACK.ADD;
2940
2941 RETURN FALSE;
2942 END IF;
2943 CLOSE cur_pri_prg;
2944 END IF;
2945
2946 OPEN cur_sua_unconfirm(vcur_spa.person_id,vcur_spa.course_cd);
2947 FETCH cur_sua_unconfirm into l_sua_unconfirm_check;
2948
2949
2950 OPEN cur_ps_sec(vcur_spa.person_id,l_career);
2951
2952 FETCH cur_ps_sec into l_sec_courseCD;
2953 --unconfirm unit exist and secondary program also exist
2954 IF cur_sua_unconfirm%FOUND AND cur_ps_sec%FOUND THEN
2955 CLOSE cur_sua_unconfirm;
2956 CLOSE cur_ps_sec;
2957
2958 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_PROGPRIM_FAIL');
2959 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
2960 IGS_GE_MSG_STACK.ADD;
2961
2962
2963 RETURN FALSE;
2964
2965 END IF;
2966 --Only unconfirm unit exist
2967
2968
2969 IF del_unconfirm_sua_for_reopen(vcur_spa.person_id,vcur_spa.course_cd) <> TRUE THEN
2970 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_DELUNIT_FAIL');
2971 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
2972 IGS_GE_MSG_STACK.ADD;
2973 IF cur_sua_unconfirm%ISOPEN THEN
2974 CLOSE cur_sua_unconfirm;
2975 END IF;
2976
2977 IF cur_ps_sec%ISOPEN THEN
2978 CLOSE cur_ps_sec;
2979 END IF;
2980 RETURN FALSE;
2981 END IF ;
2982 l_primaryInd := null;
2983
2984
2985 IF cur_sua_unconfirm%ISOPEN THEN
2986 CLOSE cur_sua_unconfirm;
2987 END IF;
2988
2989 IF cur_ps_sec%ISOPEN THEN
2990 CLOSE cur_ps_sec;
2991 END IF;
2992
2993 ELSIF NVL(vcur_spa.PRIMARY_PROGRAM_TYPE,'SECONDARY') = 'PRIMARY'
2994 AND vcur_spa.STUDENT_CONFIRMED_IND <> 'Y' THEN
2995 --program is primary in career and unconfirmed
2996 IF del_unconfirm_sua_for_reopen(vcur_spa.person_id,vcur_spa.course_cd) <> TRUE THEN
2997 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_DELUNIT_FAIL');
2998 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
2999 IGS_GE_MSG_STACK.ADD;
3000
3001
3002 RETURN FALSE;
3003 END IF ;
3004 l_primaryInd := null;
3005
3006 ELSIF NVL(vcur_spa.PRIMARY_PROGRAM_TYPE,'SECONDARY') = 'SECONDARY' THEN
3007 --program type is null or secondary in the career
3008 IF del_unconfirm_sua_for_reopen(vcur_spa.person_id,vcur_spa.course_cd) <> TRUE THEN
3009 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_DELUNIT_FAIL');
3010 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
3011 IGS_GE_MSG_STACK.ADD;
3012
3013 RETURN FALSE;
3014 END IF ;
3015 l_primaryInd := vcur_spa.primary_program_type;
3016
3017 -- delete future dated transfer
3018 FOR vcur_termcal IN cur_term_cal(vcur_spa.person_id,vcur_spa.course_cd)
3019 LOOP
3020 IGS_EN_FUTURE_DT_TRANS.cleanup_dest_program(vcur_spa.person_id,
3021 vcur_spa.course_cd,
3022 vcur_termcal.effective_term_cal_type,
3023 vcur_termcal.effective_term_sequence_num,
3024 'CLEANUP');
3025
3026 END LOOP;
3027
3028 END IF; -- end of check for primary confirmed program
3029 ELSE -- system is in program mode
3030 IF vcur_spa.key_program = 'Y' THEN
3031 OPEN cur_confirm_prg(vcur_spa.person_id,vcur_spa.course_cd);
3032 FETCH cur_confirm_prg INTO l_pri_prg;
3033 IF cur_confirm_prg%FOUND THEN
3034 CLOSE cur_confirm_prg;
3035 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_KEYPRG_FAIL');
3036 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
3037 IGS_GE_MSG_STACK.ADD;
3038
3039 RETURN FALSE;
3040 END IF;
3041 CLOSE cur_confirm_prg;
3042 END IF;
3043
3044 IF del_unconfirm_sua_for_reopen(vcur_spa.person_id,vcur_spa.course_cd) <> TRUE THEN
3045 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_DELUNIT_FAIL');
3046 FND_MESSAGE.SET_TOKEN('PROGRAM_CD',vcur_spa.course_cd);
3047 IGS_GE_MSG_STACK.ADD;
3048 RETURN FALSE;
3049 END IF ;
3050 l_primaryInd := vcur_spa.primary_program_type;
3051
3052 END IF; -- end of check for career mode
3053
3054
3055 -- if the Pre-Enrollment Year profile option is set to Y
3056 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
3057
3058 -- do unit set processing
3059 --loop through the unit sets attempts for the program attempt
3060 FOR vcur_susa IN cur_susa(vcur_spa.person_id,vcur_spa.course_cd) LOOP
3061
3062 IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(
3063 X_ROWID => vcur_susa.rowid,
3064 X_PERSON_ID => vcur_susa.person_id ,
3065 X_COURSE_CD => vcur_susa.course_cd ,
3066 X_UNIT_SET_CD => vcur_susa.unit_set_cd ,
3067 X_SEQUENCE_NUMBER => vcur_susa.sequence_number,
3068 X_US_VERSION_NUMBER => vcur_susa.us_version_number,
3069 X_SELECTION_DT => NULL,
3070 X_STUDENT_CONFIRMED_IND => 'N',
3071 X_END_DT => NULL ,
3072 X_PARENT_UNIT_SET_CD => vcur_susa.parent_unit_set_cd ,
3073 X_PARENT_SEQUENCE_NUMBER => vcur_susa.parent_sequence_number ,
3074 X_PRIMARY_SET_IND => vcur_susa.primary_set_ind ,
3075 X_VOLUNTARY_END_IND => 'N' ,
3076 X_AUTHORISED_PERSON_ID => vcur_susa.authorised_person_id ,
3077 X_AUTHORISED_ON => vcur_susa.authorised_on ,
3078 X_OVERRIDE_TITLE => vcur_susa.override_title ,
3079 X_RQRMNTS_COMPLETE_IND => vcur_susa.rqrmnts_complete_ind ,
3080 X_RQRMNTS_COMPLETE_DT => NULL ,
3081 X_S_COMPLETED_SOURCE_TYPE => vcur_susa.s_completed_source_type ,
3082 X_CATALOG_CAL_TYPE => NULL ,
3083 X_CATALOG_SEQ_NUM => NULL ,
3084 X_ATTRIBUTE_CATEGORY => vcur_susa.attribute_category ,
3085 X_ATTRIBUTE1 => vcur_susa.attribute1 ,
3086 X_ATTRIBUTE2 => vcur_susa.attribute2 ,
3087 X_ATTRIBUTE3 => vcur_susa.attribute3 ,
3088 X_ATTRIBUTE4 => vcur_susa.attribute4 ,
3089 X_ATTRIBUTE5 => vcur_susa.attribute5 ,
3090 X_ATTRIBUTE6 => vcur_susa.attribute6 ,
3091 X_ATTRIBUTE7 => vcur_susa.attribute7 ,
3092 X_ATTRIBUTE8 => vcur_susa.attribute8 ,
3093 X_ATTRIBUTE9 => vcur_susa.attribute9 ,
3094 X_ATTRIBUTE10 => vcur_susa.attribute10 ,
3095 X_ATTRIBUTE11 => vcur_susa.attribute11 ,
3096 X_ATTRIBUTE12 => vcur_susa.attribute12 ,
3097 X_ATTRIBUTE13 => vcur_susa.attribute13 ,
3098 X_ATTRIBUTE14 => vcur_susa.attribute14 ,
3099 X_ATTRIBUTE15 => vcur_susa.attribute15 ,
3100 X_ATTRIBUTE16 => vcur_susa.attribute16 ,
3101 X_ATTRIBUTE17 => vcur_susa.attribute17 ,
3102 X_ATTRIBUTE18 => vcur_susa.attribute18 ,
3103 X_ATTRIBUTE19 => vcur_susa.attribute19 ,
3104 X_ATTRIBUTE20 => vcur_susa.attribute20 ,
3105 X_MODE => 'R');
3106
3107 END LOOP; -- end of looping through unit sets attempts
3108 END IF; --end of pre-enrollment year check
3109
3110 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
3111 X_ROWID => vcur_spa.rowid,
3112 X_PERSON_ID => vcur_spa.PERSON_ID,
3113 X_COURSE_CD => vcur_spa.COURSE_CD,
3114 X_ADVANCED_STANDING_IND => vcur_spa.ADVANCED_STANDING_IND,
3115 X_FEE_CAT => vcur_spa.FEE_CAT,
3116 X_CORRESPONDENCE_CAT => vcur_spa.CORRESPONDENCE_CAT,
3117 X_SELF_HELP_GROUP_IND => vcur_spa.SELF_HELP_GROUP_IND,
3118 X_LOGICAL_DELETE_DT => vcur_spa.LOGICAL_DELETE_DT,
3119 X_ADM_ADMISSION_APPL_NUMBER => vcur_spa.ADM_ADMISSION_APPL_NUMBER,
3120 X_ADM_NOMINATED_COURSE_CD => vcur_spa.ADM_NOMINATED_COURSE_CD,
3121 X_ADM_SEQUENCE_NUMBER => vcur_spa.ADM_SEQUENCE_NUMBER,
3122 X_VERSION_NUMBER => vcur_spa.VERSION_NUMBER,
3123 X_CAL_TYPE => vcur_spa.CAL_TYPE,
3124 X_LOCATION_CD => vcur_spa.LOCATION_CD,
3125 X_ATTENDANCE_MODE => vcur_spa.ATTENDANCE_MODE,
3126 X_ATTENDANCE_TYPE => vcur_spa.ATTENDANCE_TYPE,
3127 X_COO_ID => vcur_spa.COO_ID,
3128 X_STUDENT_CONFIRMED_IND => 'N',
3129 X_COMMENCEMENT_DT => NULL,
3130 X_COURSE_ATTEMPT_STATUS => 'UNCONFIRM',
3131 X_PROGRESSION_STATUS => vcur_spa.PROGRESSION_STATUS,
3132 X_DERIVED_ATT_TYPE => vcur_spa.DERIVED_ATT_TYPE,
3133 X_DERIVED_ATT_MODE => vcur_spa.DERIVED_ATT_MODE,
3134 X_PROVISIONAL_IND => vcur_spa.PROVISIONAL_IND ,
3135 X_DISCONTINUED_DT => vcur_spa.DISCONTINUED_DT,
3136 X_DISCONTINUATION_REASON_CD => vcur_spa.DISCONTINUATION_REASON_CD,
3137 X_LAPSED_DT => vcur_spa.LAPSED_DT,
3138 X_FUNDING_SOURCE => vcur_spa.FUNDING_SOURCE,
3139 X_EXAM_LOCATION_CD => vcur_spa.EXAM_LOCATION_CD,
3140 X_DERIVED_COMPLETION_YR => vcur_spa.DERIVED_COMPLETION_YR,
3141 X_DERIVED_COMPLETION_PERD => vcur_spa.DERIVED_COMPLETION_PERD,
3142 X_NOMINATED_COMPLETION_YR => vcur_spa.nominated_completion_yr,
3143 X_NOMINATED_COMPLETION_PERD => vcur_spa.NOMINATED_COMPLETION_PERD,
3144 X_RULE_CHECK_IND => vcur_spa.RULE_CHECK_IND,
3145 X_WAIVE_OPTION_CHECK_IND => vcur_spa.WAIVE_OPTION_CHECK_IND,
3146 X_LAST_RULE_CHECK_DT => vcur_spa.LAST_RULE_CHECK_DT,
3147 X_PUBLISH_OUTCOMES_IND => vcur_spa.PUBLISH_OUTCOMES_IND,
3148 X_COURSE_RQRMNT_COMPLETE_IND => vcur_spa.COURSE_RQRMNT_COMPLETE_IND,
3149 X_COURSE_RQRMNTS_COMPLETE_DT => vcur_spa.COURSE_RQRMNTS_COMPLETE_DT,
3150 X_S_COMPLETED_SOURCE_TYPE => vcur_spa.S_COMPLETED_SOURCE_TYPE,
3151 X_OVERRIDE_TIME_LIMITATION => vcur_spa.OVERRIDE_TIME_LIMITATION,
3152 X_MODE => 'R',
3153 x_last_date_of_attendance => vcur_spa.LAST_DATE_OF_ATTENDANCE,
3154 x_dropped_by => vcur_spa.DROPPED_BY,
3155 X_IGS_PR_CLASS_STD_ID => vcur_spa.IGS_PR_CLASS_STD_ID,
3156 x_primary_program_type => l_primaryInd,
3157 x_primary_prog_type_source => vcur_spa.PRIMARY_PROG_TYPE_SOURCE,
3158 x_catalog_cal_type => NULL,
3159 x_catalog_seq_num => NULL,
3160 x_key_program => 'N',
3161 x_override_cmpl_dt => vcur_spa.OVERRIDE_CMPL_DT,
3162 x_manual_ovr_cmpl_dt_ind => vcur_spa.MANUAL_OVR_CMPL_DT_IND,
3163 X_ATTRIBUTE_CATEGORY => vcur_spa.ATTRIBUTE_CATEGORY,
3164 X_ATTRIBUTE1 => vcur_spa.ATTRIBUTE1,
3165 X_ATTRIBUTE2 => vcur_spa.ATTRIBUTE2,
3166 X_ATTRIBUTE3 => vcur_spa.ATTRIBUTE3,
3167 X_ATTRIBUTE4 => vcur_spa.ATTRIBUTE4,
3168 X_ATTRIBUTE5 => vcur_spa.ATTRIBUTE5,
3169 X_ATTRIBUTE6 => vcur_spa.ATTRIBUTE6,
3170 X_ATTRIBUTE7 => vcur_spa.ATTRIBUTE7,
3171 X_ATTRIBUTE8 => vcur_spa.ATTRIBUTE8,
3172 X_ATTRIBUTE9 => vcur_spa.ATTRIBUTE9,
3173 X_ATTRIBUTE10 => vcur_spa.ATTRIBUTE10,
3174 X_ATTRIBUTE11 => vcur_spa.ATTRIBUTE11,
3175 X_ATTRIBUTE12 => vcur_spa.ATTRIBUTE12,
3176 X_ATTRIBUTE13 => vcur_spa.ATTRIBUTE13,
3177 X_ATTRIBUTE14 => vcur_spa.ATTRIBUTE14,
3178 X_ATTRIBUTE15 => vcur_spa.ATTRIBUTE15,
3179 X_ATTRIBUTE16 => vcur_spa.ATTRIBUTE16,
3180 X_ATTRIBUTE17 => vcur_spa.ATTRIBUTE17,
3181 X_ATTRIBUTE18 => vcur_spa.ATTRIBUTE18,
3182 X_ATTRIBUTE19 => vcur_spa.ATTRIBUTE19,
3183 X_ATTRIBUTE20 => vcur_spa.ATTRIBUTE20,
3184 X_FUTURE_DATED_TRANS_FLAG => vcur_spa.FUTURE_DATED_TRANS_FLAG);
3185
3186
3187
3188
3189
3190
3191
3192 END IF; -- end of check for unconfirmed unit attempts
3193
3194
3195 END LOOP; -- end of looping through the program attempts
3196
3197 RETURN TRUE;
3198 END validate_unconfirm_program; --end of function
3199
3200 FUNCTION handle_rederive_prog_att(
3201 p_person_id IN NUMBER ,
3202 p_admission_appl_number IN NUMBER ,
3203 p_nominated_course_cd IN VARCHAR2 ,
3204 p_sequence_number IN NUMBER,
3205 p_message OUT NOCOPY VARCHAR2)
3206 RETURN BOOLEAN
3207 IS
3208
3209 --cursor to fetch the program attempts for the admission application in context
3210 Cursor cur_spa(cp_person_id NUMBER,
3211 cp_adm_appl_number NUMBER,
3212 cp_adm_nom_course_cd VARCHAR2,
3213 cp_adm_sequence_num NUMBER) IS
3214 Select spa.rowid
3215 from igs_en_stdnt_ps_att_all spa
3216 where spa.person_id=cp_person_id
3217 and spa.adm_admission_appl_number=cp_adm_appl_number
3218 and spa.adm_nominated_course_cd=cp_adm_nom_course_cd
3219 and spa.adm_sequence_number = cp_adm_sequence_num;
3220
3221 -- Get the details of
3222 CURSOR cur_spa_en IS
3223 SELECT spa.rowid
3224 FROM igs_en_stdnt_ps_att_all spa
3225 WHERE spa.person_id = p_person_id
3226 AND spa.course_cd = p_nominated_course_cd;
3227
3228 l_message VARCHAR2(200);
3229 BEGIN
3230 -- fetch all program attempts using the application context parameters passed.
3231 --loop through the program attempts found
3232
3233 IF p_admission_appl_number IS NULL AND p_sequence_number IS NULL THEN
3234 FOR vcur_spa_en IN cur_spa_en LOOP
3235 IF NOT validate_unconfirm_program(vcur_spa_en.rowid) THEN
3236 RETURN FALSE;
3237 END IF;
3238 END LOOP;
3239 ELSE
3240 FOR vcur_spa IN cur_spa(p_person_id,
3241 p_admission_appl_number,
3242 p_nominated_course_cd,
3243 p_sequence_number) LOOP
3244
3245 IF NOT validate_unconfirm_program(vcur_spa.rowid) THEN
3246 RETURN FALSE;
3247 END IF;
3248
3249 END LOOP; -- end of looping through the program attempts
3250 END IF;
3251 RETURN TRUE;
3252 END handle_rederive_prog_att; --end of function
3253
3254
3255
3256
3257 END IGS_EN_VAL_SCA;
[Home] [Help]