1 PACKAGE BODY IGS_AD_VAL_ACA AS
2 /* $Header: IGSAD21B.pls 115.5 2002/11/28 21:26:52 nsidana ship $ */
3 --
4 -- To validate discontinuation and student course transfer
5 FUNCTION enrp_val_sca_trnsfr(
6 p_person_id IN NUMBER ,
7 p_course_cd IN VARCHAR2 ,
8 p_discontinued_dt IN DATE ,
9 p_validation_ind IN VARCHAR2 DEFAULT 'N',
10 p_message_name OUT NOCOPY varchar2 )
11 RETURN BOOLEAN IS
12 BEGIN -- enrp_val_sca_trnsfr
13 -- This module validates IGS_PS_STDNT_TRN links to a
14 -- IGS_EN_STDNT_PS_ATT when lifting discontinuation.
15 -- Do not allow lifting of discontinuation if IGS_PS_STDNT_TRN
16 -- exists where
17 -- * IGS_PS_STDNT_TRN details already exist against the course
18 -- attempt and the last transfer was not 'to' the course.
19 -- * The transfer_course_cd has IGS_PS_STDNT_TRN links where the
20 -- course transferred to is currently enrolled, inactive, intermitted
21 -- or lapsed.
22 DECLARE
23 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
24 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
25 cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
26 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
27 v_trnsfr_crs_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE;
28 v_link_found BOOLEAN DEFAULT FALSE;
29 v_dummy VARCHAR2(1);
30 CURSOR c_sct IS
31 SELECT sct.transfer_course_cd
32 FROM IGS_PS_STDNT_TRN sct
33 WHERE sct.person_id = p_person_id AND
34 (sct.course_cd = p_course_cd OR
35 sct.transfer_course_cd = p_course_cd)
36 ORDER BY transfer_dt desc;
37 CURSOR c_sct_sca_course_cd IS
38 SELECT sca.course_cd
39 FROM IGS_EN_STDNT_PS_ATT sca,
40 IGS_PS_STDNT_TRN sct
41 WHERE sct.person_id = p_person_id AND
42 sct.course_cd = p_course_cd AND
43 sca.person_id = sct.person_id AND
44 sca.course_cd = sct.transfer_course_cd;
45 CURSOR c_sct_sca(
46 cp_transfer_course_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE) IS
47 SELECT 'X'
48 FROM IGS_PS_STDNT_TRN sct,
49 IGS_EN_STDNT_PS_ATT sca
50 WHERE sct.person_id = p_person_id AND
51 sct.transfer_course_cd = cp_transfer_course_cd AND
52 (sct.course_cd <> p_course_cd) AND
53 sca.person_id = sct.person_id AND
54 sca.course_cd = sct.course_cd AND
55 sca.course_attempt_status IN (
56 cst_enrolled,
57 cst_inactive,
58 cst_intermit,
59 cst_lapsed);
60 BEGIN
61 p_message_name := null;
62 IF p_discontinued_dt IS NULL THEN
63 -- Validate that if student course transfer details exist, then the last was
64 -- a transfer to this course
65 OPEN c_sct;
66 FETCH c_sct INTO v_trnsfr_crs_cd;
67 IF (c_sct%FOUND) THEN
68 IF v_trnsfr_crs_cd = p_course_cd THEN
69 CLOSE c_sct;
70 -- Cannot lift discontinuation unless last transfer was to this course
71 IF p_validation_ind = 'E' THEN
72 -- Do not allow lifting of student course attempt discontinuation
73 p_message_name := 'IGS_EN_DISCONT_NOTLIFT_PRGATT';
74 RETURN FALSE;
75 ELSE
76 -- Disallow application for course re_admission
77 p_message_name := 'IGS_EN_READM_NOT_VALID';
78 RETURN FALSE;
79 END IF;
80 END IF;
81 END IF;
82 CLOSE c_sct;
83 -- Validate that student course transfers do not exist for the 'from' course
84 -- attempt that are active.
85 FOR v_transfer_course_cd IN c_sct_sca_course_cd LOOP
86 -- Determine if transferred from course has other transfer links that
87 -- are enrolled.
88 OPEN c_sct_sca(v_transfer_course_cd.course_cd);
89 FETCH c_sct_sca INTO v_dummy;
90 IF (c_sct_sca%FOUND) THEN
91 CLOSE c_sct_sca;
92 v_link_found := TRUE;
93 IF p_validation_ind = 'E' THEN
94 p_message_name := 'IGS_EN_DISCONT_NOT_FROM_TRNS';
95 ELSE
96 p_message_name := 'IGS_EN_READM_INVALID';
97 END IF;
98 EXIT;
99 END IF;
100 CLOSE c_sct_sca;
101 END LOOP;
102 IF v_link_found = TRUE THEN
103 RETURN FALSE;
104 END IF;
105 END IF;
106 RETURN TRUE;
107 EXCEPTION
108 WHEN OTHERS THEN
109 IF (c_sct%ISOPEN) THEN
110 CLOSE c_sct;
111 END IF;
112 IF (c_sct_sca_course_cd%ISOPEN) THEN
113 CLOSE c_sct_sca_course_cd;
114 END IF;
115 IF (c_sct_sca%ISOPEN) THEN
116 CLOSE c_sct_sca;
117 END IF;
118 RAISE;
119 END;
120 EXCEPTION
121 WHEN OTHERS THEN
122 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
123 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.enrp_val_sca_trnsfr');
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END enrp_val_sca_trnsfr;
127 --
128
129 -- The following procedures have been commented to handle mutation logic
130
131 -- To validate the admission application preference limit.
132 FUNCTION admp_val_pref_limit(
133 p_person_id IN NUMBER ,
134 p_admission_appl_number IN NUMBER ,
135 p_nominated_course_cd IN VARCHAR2 ,
136 p_acai_sequence_number IN NUMBER ,
137 p_s_admission_process_type IN VARCHAR2 ,
138 p_pref_limit NUMBER ,
139 p_message_name OUT NOCOPY VARCHAR2 )
140 RETURN BOOLEAN IS
141 BEGIN -- admp_val_pref_limit
142 -- Validate preference limit.
143 DECLARE
144 cst_course CONSTANT VARCHAR2(6) := 'COURSE';
145 v_count_acai NUMBER;
146 CURSOR c_acai (
147 cp_person_id IGS_AD_APPL.person_id%TYPE,
148 cp_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
149 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
150 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) IS
151 SELECT COUNT(*)
152 FROM IGS_AD_PS_APPL_INST acai
153 WHERE acai.person_id = cp_person_id AND
154 acai.admission_appl_number = cp_admission_appl_number AND
155 NOT (acai.nominated_course_cd = cp_nominated_course_cd AND
156 acai.sequence_number = cp_sequence_number);
157 BEGIN
158 p_message_name := NULL;
159 -- Determine if preferences are allowed for the admission application
160 IF(p_s_admission_process_type = cst_course AND
161 p_pref_limit IS NOT NULL) THEN
162 -- Preferences are allowed.
163 -- Determine if the preference limit has been exceeded.
164 OPEN c_acai(
165 p_person_id,
166 p_admission_appl_number,
167 p_nominated_course_cd,
168 p_acai_sequence_number);
169 FETCH c_acai INTO v_count_acai;
170 IF(v_count_acai >= p_pref_limit) THEN
171 CLOSE c_acai;
172 p_message_name := 'IGS_AD_PREFLIMIT_ADMAPL_REACH';
173 RETURN FALSE;
174 END IF;
175 CLOSE c_acai;
176 ELSE
177 -- Preferences are not allowed.
178 -- Determine if a record already exists for the application.
179 OPEN c_acai(
180 p_person_id,
181 p_admission_appl_number,
182 p_nominated_course_cd,
183 p_acai_sequence_number);
184 FETCH c_acai INTO v_count_acai;
185 IF(v_count_acai > 0) THEN
186 CLOSE c_acai;
187 p_message_name := 'IGS_AD_PREF_NOTALLOW_ADMAPL';
188 RETURN FALSE;
189 END IF;
190 CLOSE c_acai;
191 END IF;
192 RETURN TRUE;
193 END;
194 EXCEPTION
195 WHEN OTHERS THEN
196 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
197 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_pref_limit');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END admp_val_pref_limit;
201 --
202 -- Validate the course code of the admission application.
203 --
204 -- Perform encumbrance check for admission_course_appl_instance.course_cd
205 -- Validate course appl process type against the student course attempt.
206 --
207 -- Validate admission course application transfer details.
208 FUNCTION admp_val_aca_trnsfr(
209 p_person_id IN NUMBER ,
210 p_nominated_course_cd IN VARCHAR2 ,
211 p_nominated_version_number IN NUMBER ,
212 p_transfer_course_cd IN VARCHAR2 ,
213 p_s_admission_process_type IN VARCHAR2 ,
214 p_course_encmb_chk_ind IN VARCHAR2 DEFAULT 'N',
215 p_adm_cal_type IN VARCHAR2 ,
216 p_adm_ci_sequence_number IN NUMBER ,
217 p_message_name OUT NOCOPY VARCHAR2 ,
218 p_return_type OUT NOCOPY VARCHAR2 )
219 RETURN BOOLEAN IS
220 BEGIN -- admp_val_aca_trnsfr
221 -- Validate that the nominated course and transfer course are valid for the
222 -- system admission process type course Transfer.
223 DECLARE
224 cst_error CONSTANT VARCHAR2(1) := 'E';
225 cst_warn CONSTANT VARCHAR2(1) := 'W';
226 cst_enrolled CONSTANT VARCHAR2(8) := 'ENROLLED';
227 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
228 cst_intermit CONSTANT VARCHAR2(8) := 'INTERMIT';
229 cst_lapsed CONSTANT VARCHAR2(6) := 'LAPSED';
230 cst_discontin CONSTANT VARCHAR2(9) := 'DISCONTIN';
231 cst_completed CONSTANT VARCHAR2(9) := 'COMPLETED';
232 v_message_name VARCHAR2(30);
233 v_encmb_chk_dt IGS_CA_DA_INST_V.alias_val%TYPE;
234 v_generic_course_ind IGS_PS_VER.generic_course_ind%TYPE;
235 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
236 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
237 v_transfer_course_group_found BOOLEAN DEFAULT FALSE;
238 v_nominated_course_found BOOLEAN DEFAULT FALSE;
239 v_check CHAR;
240 CURSOR c_cv IS
241 SELECT generic_course_ind
242 FROM IGS_PS_VER
243 WHERE course_cd = p_nominated_course_cd AND
244 version_number = p_nominated_version_number;
245 CURSOR c_sca IS
246 SELECT course_attempt_status,
247 version_number
248 FROM IGS_EN_STDNT_PS_ATT
249 WHERE person_id = p_person_id AND
250 course_cd = p_transfer_course_cd;
251 CURSOR c_get_course_group_cd (
252 cp_version_number IGS_PS_GRP_MBR.version_number%TYPE) IS
253 SELECT cgr.course_group_cd
254 FROM IGS_PS_GRP_MBR cgm,
255 IGS_PS_GRP cgr,
256 IGS_PS_GRP_TYPE cgt
257 WHERE cgm.course_cd = p_transfer_course_cd AND
258 cgm.version_number = cp_version_number AND
259 cgm.course_group_cd = cgr.course_group_cd AND
260 cgr.course_group_type = cgt.course_group_type AND
261 cgt.s_course_group_type = 'ADMTRANSFR';
262 CURSOR c_cgm ( cp_course_cd IGS_PS_GRP_MBR.course_cd%TYPE,
263 cp_version_number IGS_PS_GRP_MBR.version_number%TYPE,
264 cp_course_group_cd IGS_PS_GRP_MBR.course_group_cd%TYPE) IS
265 SELECT 'x'
266 FROM IGS_PS_GRP_MBR
267 WHERE course_cd = cp_course_cd AND
268 version_number = cp_version_number AND
269 course_group_cd = cp_course_group_cd;
270 BEGIN
271 p_message_name := NULL;
272 IF (p_s_admission_process_type <> 'TRANSFER') THEN
273 -- Transfer code is not required
274 IF (p_transfer_course_cd IS NULL) THEN
275 RETURN TRUE;
276 ELSE
277 p_message_name := 'IGS_AD_TRANSFERCD_REQ_PRGTRNS';
278 p_return_type := cst_error;
279 RETURN FALSE;
280 END IF;
281 END IF;
282 -- Validate that the nominated course is not a generic course
283 OPEN c_cv;
284 FETCH c_cv INTO v_generic_course_ind;
285 IF (c_cv%NOTFOUND) THEN
286 CLOSE c_cv;
287 RETURN TRUE;
288 END IF;
289 CLOSE c_cv;
290 IF (v_generic_course_ind = 'Y') THEN
291 p_message_name := 'IGS_AD_PRG_TRANSFERED_CANNOT';
292 p_return_type := cst_error;
293 RETURN FALSE;
294 END IF;
295 -- Tranfer course code must exist
296 IF (p_transfer_course_cd IS NULL) THEN
297 p_message_name := 'IGS_AD_SPECIFY_TRANSFERCD';
298 p_return_type := cst_error;
299 RETURN FALSE;
300 END IF;
301 -- Validate transfer code exists as a student course attempt
302 OPEN c_sca;
303 FETCH c_sca INTO v_course_attempt_status,
304 v_version_number;
305 IF (c_sca%NOTFOUND) THEN
306 CLOSE c_sca;
307 p_return_type := cst_error;
308 p_message_name := 'IGS_AD_STUDPRG_DOESNOT_EXIST';
309 RETURN FALSE;
310 END IF;
311 CLOSE c_sca;
312 IF v_course_attempt_status NOT IN ( cst_enrolled,
313 cst_inactive,
314 cst_intermit,
315 cst_lapsed,
316 cst_discontin,
317 cst_completed) THEN
318 -- Invalid course attempt status to allow admission transfer
319 p_return_type := cst_error;
320 p_message_name := 'IGS_AD_CRSTRNSFER_ADMPRC';
321 RETURN FALSE;
322 END IF;
323 -- Validate that transfer student course attempt belongs to
324 -- admission course transfer group
325 FOR v_crg_rec IN c_get_course_group_cd(
326 v_version_number) LOOP
327 v_transfer_course_group_found := TRUE;
328 -- Validate that nominated course belongs to the same
329 -- admission course transfer group
330 OPEN c_cgm (
331 p_nominated_course_cd,
332 p_nominated_version_number,
333 v_crg_rec.course_group_cd);
334 FETCH c_cgm INTO v_check;
335 IF (c_cgm%FOUND) THEN
336 v_nominated_course_found := TRUE;
337 CLOSE c_cgm;
338 EXIT;
339 END IF;
340 CLOSE c_cgm;
341 END LOOP;
342 IF (v_transfer_course_group_found = FALSE) THEN
343 p_return_type := cst_error;
344 p_message_name := 'IGS_AD_INVALID_TRANSFERCD_CHK';
345 RETURN FALSE;
346 END IF;
347 IF (v_nominated_course_found = FALSE) THEN
348 p_return_type := cst_error;
349 p_message_name := 'IGS_AD_INVALID_ADM_PRGCD';
350 RETURN FALSE;
351 END IF;
352 --Validate Encumbrances
353 IF (p_course_encmb_chk_ind = 'Y') THEN
354 --Validate encumbrances
355 v_encmb_chk_dt := IGS_AD_GEN_006.ADMP_GET_ENCMB_DT(
356 p_adm_cal_type,
357 p_adm_ci_sequence_number);
358 IF (v_encmb_chk_dt IS NULL) THEN
359 p_message_name := 'IGS_AD_NO_ENCUMB_DTALIAS_PRD';
360 p_return_type := cst_warn;
361 RETURN FALSE;
362 END IF;
363 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_crs(
364 p_person_id,
365 p_nominated_course_cd,
366 v_encmb_chk_dt,
367 v_message_name) = FALSE) THEN
368 p_message_name := 'IGS_AD_PRSN_ENCUMB_SUSPEND';
369 p_return_type := cst_warn;
370 RETURN FALSE;
371 END IF;
372 END IF;
373 RETURN TRUE;
374 END;
375 EXCEPTION
376 WHEN OTHERS THEN
377 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
378 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_aca_trnsfr');
379 IGS_GE_MSG_STACK.ADD;
380 App_Exception.Raise_Exception;
381 END admp_val_aca_trnsfr;
382 --
383 -- Validate if IGS_AD_CD.admission_cd is closed.
384 FUNCTION admp_val_aco_closed(
385 p_admission_cd IN VARCHAR2 ,
386 p_message_name OUT NOCOPY VARCHAR2 )
387 RETURN BOOLEAN IS
388 BEGIN -- admp_val_aco_closed
389 -- Validate the admission_cd closed indicator
390 DECLARE
391 CURSOR c_aco(
392 cp_admission_cd IGS_AD_CD.admission_cd%TYPE) IS
393 SELECT closed_ind
394 FROM IGS_AD_CD
395 WHERE admission_cd = cp_admission_cd;
396 v_aco_rec c_aco%ROWTYPE;
397 cst_yes CONSTANT CHAR := 'Y';
398 BEGIN
399 -- Set the default message number
400 p_message_name := NULL;
401 -- Cursor handling
402 OPEN c_aco(
403 p_admission_cd);
404 FETCH c_aco INTO v_aco_rec;
405 IF c_aco%NOTFOUND THEN
406 CLOSE c_aco;
407 RETURN TRUE;
408 END IF;
409 CLOSE c_aco;
410 IF (v_aco_rec.closed_ind = cst_yes) THEN
411 p_message_name := 'IGS_AD_ADMCD_CLOSED';
412 RETURN FALSE;
413 END IF;
414 -- Return the default value
415 RETURN TRUE;
416 END;
417 EXCEPTION
418 WHEN OTHERS THEN
422 App_Exception.Raise_Exception;
419 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
420 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_aco_closed');
421 IGS_GE_MSG_STACK.ADD;
423 END admp_val_aco_closed;
424 --
425 -- Validate if IGS_AD_BASIS_FOR_AD.basis_for_admission_type is closed.
426 FUNCTION admp_val_bfa_closed(
427 p_basis_for_admission_type IN VARCHAR2 ,
428 p_message_name OUT NOCOPY VARCHAR2 )
429 RETURN BOOLEAN IS
430 BEGIN
431 DECLARE
432 v_closed_ind IGS_AD_BASIS_FOR_AD.closed_ind%TYPE;
433 CURSOR c_bfa IS
434 SELECT bfa.closed_ind
435 FROM IGS_AD_BASIS_FOR_AD bfa
436 WHERE bfa.basis_for_admission_type = p_basis_for_admission_type;
437 BEGIN
438 -- Validate if IGS_AD_BASIS_FOR_AD.basis_for_admission_type is closed.
439 OPEN c_bfa;
440 FETCH c_bfa INTO v_closed_ind;
441 IF (c_bfa%FOUND) THEN
442 IF (v_closed_ind = 'Y') THEN
443 CLOSE c_bfa;
444 p_message_name := 'IGS_AD_BASIS_ADM_TYPE_CLOSED' ;
445 RETURN FALSE;
446 END IF;
447 END IF;
448 CLOSE c_bfa;
449 p_message_name := NULL;
450 RETURN TRUE;
451 EXCEPTION
452 WHEN OTHERS THEN
453 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
454 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_bfa_closed');
455 IGS_GE_MSG_STACK.ADD;
456 App_Exception.Raise_Exception;
457 END;
458 END admp_val_bfa_closed;
459 --
460 -- Validate IGS_AD_PS_APPL.req_for_reconsideration_ind.
461 FUNCTION admp_val_aca_req_rec(
462 p_req_for_reconsideration_ind IN VARCHAR2 DEFAULT 'N',
463 p_req_reconsider_allowed IN VARCHAR2 DEFAULT 'N',
464 p_message_name OUT NOCOPY VARCHAR2 )
465 RETURN BOOLEAN IS
466 BEGIN -- admp_val_aca_req_rec
467 -- Validate IGS_AD_PS_APPL.req_for_reconsideration_ind
468 DECLARE
469 BEGIN
470 -- Validate if the request for reconsideration indicator can be set.
471 IF (p_req_for_reconsideration_ind = 'Y' AND
472 p_req_reconsider_allowed = 'N') THEN
473 p_message_name := 'IGS_AD_NO_RECONSIDERATION';
474 RETURN FALSE;
475 END IF;
476 p_message_name := NULL;
477 RETURN TRUE;
478 END;
479 EXCEPTION
480 WHEN OTHERS THEN
481 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
482 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_aca_req_rec');
483 IGS_GE_MSG_STACK.ADD;
484 App_Exception.Raise_Exception;
485 END admp_val_aca_req_rec;
486 --
487 -- Validate IGS_AD_PS_APPL.req_for_adv_standing_ind.
488 FUNCTION admp_val_aca_req_adv(
489 p_req_for_adv_standing_ind IN VARCHAR2 DEFAULT 'N',
490 p_req_adv_standing_allowed IN VARCHAR2 DEFAULT 'N',
491 p_message_name OUT NOCOPY VARCHAR2 )
492 RETURN BOOLEAN IS
493 BEGIN -- admp_val_aca_req_adv
494 -- Validate IGS_AD_PS_APPL.req_for_adv_standing_ind
495 DECLARE
496 BEGIN
497 -- Validate if the request for advanced standing indicator can be set.
498 IF (p_req_for_adv_standing_ind = 'Y' AND
499 p_req_adv_standing_allowed = 'N') THEN
500 p_message_name := 'IGS_AD_ADV_NOTREQ_ADM_APPL';
501 RETURN FALSE;
502 END IF;
503 p_message_name := NULL;
504 RETURN TRUE;
505 END;
506 EXCEPTION
507 WHEN OTHERS THEN
508 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
509 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACA.admp_val_aca_req_adv');
510 IGS_GE_MSG_STACK.ADD;
511 App_Exception.Raise_Exception;
512 END admp_val_aca_req_adv;
513
514 END IGS_AD_VAL_ACA;
[Home] [Help]