1 PACKAGE BODY IGS_AD_VAL_ACAIU AS
2 /* $Header: IGSAD23B.pls 115.9 2003/12/03 20:49:01 knag ship $ */
3
4 --
5 -- Validate the ins/upd/del admission course application instance unit
6 FUNCTION admp_val_acaiu_iud(
7 p_person_id IN NUMBER ,
8 p_admission_appl_number IN NUMBER ,
9 p_nominated_course_cd IN VARCHAR2 ,
10 p_acai_sequence_number IN NUMBER ,
11 p_unit_restr_ind IN VARCHAR2 DEFAULT 'N',
12 p_message_name OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14 BEGIN -- admp_val_acaiu_iud
15 -- This modules validates the insert/update/delete of
16 -- IGS_AD_PS_APLINSTUNT.
17 DECLARE
18 v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
19 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
20 CURSOR c_aa_aas IS
21 SELECT aas.s_adm_appl_status
22 FROM IGS_AD_APPL aa,
23 IGS_AD_APPL_STAT aas
24 WHERE
25 aa.person_id = p_person_id AND
26 aa.admission_appl_number= p_admission_appl_number AND
27 aa.adm_appl_status = aas.adm_appl_status;
28 CURSOR c_acai_aos IS
29 SELECT aos.s_adm_outcome_status
30 FROM IGS_AD_PS_APPL_INST acai,
31 IGS_AD_OU_STAT aos
32 WHERE acai.person_id = p_person_id AND
33 acai.admission_appl_number = p_admission_appl_number AND
34 acai.nominated_course_cd = p_nominated_course_cd AND
35 acai.sequence_number = p_acai_sequence_number AND
36 acai.adm_outcome_status = aos.adm_outcome_status;
37 BEGIN
38 p_message_name := NULL;
39 If (p_unit_restr_ind = 'N') THEN
40 -- Admission course application units should not be inserted/updated/deleted
41 p_message_name := 'IGS_AD_NO_IUD_ADMPRC_CAT';
42 RETURN FALSE;
43 END IF;
44 -- Validate against admission application status
45 OPEN c_aa_aas;
46 FETCH c_aa_aas INTO v_s_adm_appl_status;
47 IF (c_aa_aas%NOTFOUND) THEN
48 -- something is very wrong and will be handled elsewhere
49 CLOSE c_aa_aas;
50 RETURN TRUE;
51 END IF;
52 CLOSE c_aa_aas;
53 IF v_s_adm_appl_status = 'WITHDRAWN' OR v_s_adm_appl_status = 'COMPLETED' THEN
54 p_message_name := 'IGS_AD_NO_IUD_APPL_WITHDRAWN';
55 RETURN FALSE;
56 END IF;
57 -- Validate that the admission course application is not offered
58 OPEN c_acai_aos;
59 FETCH c_acai_aos INTO v_s_adm_outcome_status;
60 IF c_acai_aos%NOTFOUND THEN
61 -- something is very wrong and will be handled elsewhere
62 CLOSE c_acai_aos;
63 RETURN TRUE;
64 END IF;
65 CLOSE c_acai_aos;
66 IF v_s_adm_outcome_status IN (
67 'OFFER', 'COND-OFFER', 'WITHDRAWN', 'VOIDED')THEN
68 p_message_name := 'IGS_AD_NO_IUD_APPL_OFFERED';
69 RETURN FALSE;
70 END IF;
71 RETURN TRUE;
72 END;
73 EXCEPTION
74 WHEN OTHERS THEN
75 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
76 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_iud');
77 IGS_GE_MSG_STACK.ADD;
78 App_Exception.Raise_Exception;
79 END admp_val_acaiu_iud;
80
81 -- Validate the admission course application instance unit
82 FUNCTION admp_val_acaiu_unit(
83 p_unit_cd IN VARCHAR2 ,
84 p_uv_version_number IN NUMBER ,
85 p_adm_cal_type IN VARCHAR2 ,
86 p_adm_ci_sequence_number IN NUMBER ,
87 p_acad_cal_type IN VARCHAR2 ,
88 p_acad_ci_sequence_number IN NUMBER ,
89 p_s_admission_process_type IN VARCHAR2 ,
90 p_offered_ind IN VARCHAR2 DEFAULT 'N',
91 p_message_name OUT NOCOPY VARCHAR2 )
92 RETURN BOOLEAN AS
93 BEGIN -- admp_val_acaiu_unit
94 -- validate IGS_AD_PS_APLINSTUNT unit.
95 DECLARE
96 cst_yes CONSTANT VARCHAR2(1) := 'Y';
97 v_cir_1_rec_found BOOLEAN DEFAULT FALSE;
98 v_unit_version_valid BOOLEAN DEFAULT FALSE;
99 v_dummy VARCHAR2(1);
100 v_message_name VARCHAR2(30);
101 CURSOR c_cir_1 (
102 cp_unit_cd IGS_AD_PS_APLINSTUNT.unit_cd%TYPE,
103 cp_uv_version_number IGS_AD_PS_APLINSTUNT.uv_version_number%TYPE,
104 cp_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE,
105 cp_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE) IS
106 SELECT DISTINCT uoo.cal_type,
107 uoo.ci_sequence_number
108 FROM IGS_PS_UNIT_OFR_OPT uoo,
109 IGS_CA_INST_REL cir
110 WHERE uoo.unit_cd = cp_unit_cd AND
111 uoo.version_number = cp_uv_version_number AND
112 uoo.offered_ind = cst_yes AND
113 cir.sub_cal_type = uoo.cal_type AND
114 cir.sub_ci_sequence_number = uoo.ci_sequence_number AND
115 cir.sup_cal_type = cp_acad_cal_type AND
116 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number;
117 CURSOR c_cir_2 (
118 cp_adm_cal_type IGS_AD_APPL.acad_cal_type%TYPE,
119 cp_adm_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE,
120 cp_sup_cal_type IGS_AD_APPL.acad_cal_type%TYPE,
121 cp_sup_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE) IS
122 SELECT 'x'
123 FROM IGS_CA_INST_REL cir
124 WHERE cir.sub_cal_type = cp_adm_cal_type AND
125 cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
126 cir.sup_cal_type = cp_sup_cal_type AND
127 cir.sup_ci_sequence_number = cp_sup_ci_sequence_number;
128 BEGIN
129 p_message_name := NULL;
130 -- Validate unit version
131 IF(IGS_AD_VAL_ACAIU.admp_val_acaiu_uv (
132 p_unit_cd,
133 p_uv_version_number,
134 p_s_admission_process_type,
135 p_offered_ind,
136 v_message_name) = FALSE) THEN
137 p_message_name := v_message_name;
138 RETURN FALSE;
139 END IF;
140 FOR v_cir_1_rec IN c_cir_1(
141 p_unit_cd,
142 p_uv_version_number,
143 p_acad_cal_type,
144 p_acad_ci_sequence_number) LOOP
145 v_cir_1_rec_found := TRUE;
146 OPEN c_cir_2(
147 p_adm_cal_type,
148 p_adm_ci_sequence_number,
149 v_cir_1_rec.cal_type,
150 v_cir_1_rec.ci_sequence_number);
151 FETCH c_cir_2 INTO v_dummy;
152 IF(c_cir_2%FOUND) THEN
153 v_unit_version_valid := TRUE;
154 CLOSE c_cir_2;
155 EXIT;
156 ELSE
157 CLOSE c_cir_2;
158 END IF;
159 END LOOP;
160 IF(v_cir_1_rec_found = FALSE) THEN
161 -- unit version has no offered unit offering options in the
162 -- academic period of the admission course application.
163 p_message_name := 'IGS_AD_UNITVER_UOO_ACADEMIC';
164 RETURN FALSE;
165 END IF;
166 IF(v_unit_version_valid = FALSE) THEN
167 -- unit version is not valid.
168 p_message_name := 'IGS_AD_UNITVER_UOO_COMMENCPRD';
169 RETURN FALSE;
170 END IF;
171 RETURN TRUE;
172 END;
173 EXCEPTION
174 WHEN OTHERS THEN
175 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
176 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_unit');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END admp_val_acaiu_unit;
180
181 --
182 -- Validate the admission course application instance unit
183 FUNCTION admp_val_acaiu_opt(
184 p_unit_cd IN VARCHAR2 ,
185 p_uv_version_number IN NUMBER ,
186 p_cal_type IN VARCHAR2 ,
187 p_ci_sequence_number IN NUMBER ,
188 p_location_cd IN VARCHAR2 ,
189 p_unit_class IN VARCHAR2 ,
190 p_unit_mode IN VARCHAR2 ,
191 p_adm_cal_type IN VARCHAR2 ,
192 p_adm_ci_sequence_number IN NUMBER ,
193 p_acad_cal_type IN VARCHAR2 ,
194 p_acad_ci_sequence_number IN NUMBER ,
195 p_offered_ind IN VARCHAR2 DEFAULT 'N',
196 p_message_name OUT NOCOPY VARCHAR2 )
197 RETURN BOOLEAN AS
198 BEGIN -- admp_val_acaiu_opt
199 -- This module validates the unit offering option of the
200 -- IGS_AD_PS_APLINSTUNT
201 DECLARE
202 -- Select valid teaching periods for the admission course application
203 CURSOR c_cir IS
204 SELECT cir.sub_cal_type,
205 cir.sub_ci_sequence_number
206 FROM IGS_CA_INST_REL cir,
207 IGS_CA_TYPE cat1,
208 IGS_CA_TYPE cat2
209 WHERE cir.sup_cal_type = p_acad_cal_type AND
210 cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
211 cat1.s_cal_cat = 'TEACHING' AND
212 cat2.s_cal_cat = 'ACADEMIC' AND
213 cir.sub_cal_type = cat1.cal_type AND
214 cir.sup_cal_type = cat2.cal_type;
215 v_cal_type IGS_CA_INST.cal_type%TYPE;
216 v_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
217 v_message_name VARCHAR2(30);
218 v_val_off_optn_found BOOLEAN DEFAULT FALSE;
219 v_cir_found BOOLEAN DEFAULT FALSE;
220 v_admp_pk_ind BOOLEAN DEFAULT FALSE;
221 v_val_ind_type NUMBER;
222 v_val_ind_type_offer NUMBER;
223 ------------------------------SUB-FUNCTION-------------------------------------
224 ----------
225 FUNCTION admpl_val_offer_optn(
226 v_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
227 v_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE,
228 v_val_ind_type_offer OUT NOCOPY NUMBER)
229 RETURN BOOLEAN
230 AS
231 BEGIN -- admpl_val_off_optn
232 -- Validate unit offering option (if uoo.offered_ind = 'Y')
233 DECLARE
234 CURSOR c_uoo(
235 cp_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
236 cp_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
237 SELECT uoo.offered_ind,
238 uoo.unit_class
239 FROM IGS_PS_UNIT_OFR_OPT uoo
240 WHERE uoo.unit_cd = p_unit_cd AND
241 uoo.version_number = p_uv_version_number AND
242 uoo.cal_type = cp_cal_type AND
243 uoo.ci_sequence_number = cp_ci_sequence_number AND
244 (p_location_cd IS NULL OR
245 uoo.location_cd = p_location_cd) AND
246 (p_unit_class IS NULL OR
247 uoo.unit_class = p_unit_class);
248 v_offered_optn_found BOOLEAN DEFAULT FALSE;
249 v_offered_ind VARCHAR2(1) := 'N';
250 v_val_optn_found BOOLEAN DEFAULT FALSE;
251 BEGIN
252 FOR c_uoo_rec IN c_uoo (
253 v_cal_type,
254 v_ci_sequence_number) LOOP
255 v_val_optn_found := TRUE;
256 -- A valid option exists
257 IF c_uoo_rec.offered_ind = 'Y' THEN
258 v_offered_ind := 'Y';
259 -- Validate unit class/unit mode
260 IF p_unit_mode IS NOT NULL THEN
261 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_um(
262 c_uoo_rec.unit_class,
263 p_unit_mode,
264 v_message_name) = TRUE THEN
265 -- A valid offered option exists
266 v_offered_optn_found := TRUE;
267 EXIT; --(IGS_PS_UNIT_OFR_OPT)
268 END IF;
269 ELSE
270 -- A valid offered option exists
271 v_offered_optn_found := TRUE;
272 EXIT; --(IGS_PS_UNIT_OFR_OPT)
273 END IF;
274 END IF;
275 END LOOP;
276 IF (v_val_optn_found = FALSE OR
277 v_val_optn_found = TRUE AND v_offered_ind = 'N') THEN
278 v_val_ind_type_offer := 1; -- No valid option found -> 3509
279 RETURN FALSE;
280 ELSIF v_offered_optn_found = FALSE THEN
281 v_val_ind_type_offer := 2; -- No valid offered option found -> 3510
282 RETURN FALSE;
283 END IF;
284 RETURN TRUE;
285 EXCEPTION
286 WHEN OTHERS THEN
287 IF c_uoo%ISOPEN THEN
288 CLOSE c_uoo;
289 END IF;
290 RAISE;
291 END;
292 EXCEPTION
293 WHEN OTHERS THEN
294 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
295 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_offer_optn');
296 IGS_GE_MSG_STACK.ADD;
297 App_Exception.Raise_Exception;
298 END admpl_val_offer_optn;
299 --------------------------------------- MAIN ----------------------------------
300 ------------------
301 BEGIN
302 p_message_name := NULL;
303 IF (p_offered_ind = 'Y' AND
304 (p_cal_type IS NULL OR
305 p_ci_sequence_number IS NULL OR
306 p_location_cd IS NULL OR
307 p_unit_class IS NULL)) THEN
308 -- unit offering option components must all be specified when offered
309 p_message_name := 'IGS_AD_COMPONENTS_TOBE_SPECIF';
310 RETURN FALSE;
311 END IF;
312 IF (p_cal_type IS NOT NULL OR
313 p_location_cd IS NOT NULL OR
314 p_unit_class IS NOT NULL OR
315 p_unit_mode IS NOT NULL) THEN
316 -- Initialize input parameters first
317 v_val_ind_type := 0;
318 IF (p_cal_type IS NULL OR
319 p_ci_sequence_number IS NULL) THEN
320 -- Search for valid offering option
321 FOR c_cir_rec IN c_cir LOOP
322 v_cir_found := TRUE;
323 v_cal_type := c_cir_rec.sub_cal_type;
324 v_ci_sequence_number := c_cir_rec.sub_ci_sequence_number;
325 -- Validate the teaching period
326 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_ci(
327 c_cir_rec.sub_cal_type,
328 c_cir_rec.sub_ci_sequence_number,
329 p_adm_cal_type,
330 p_adm_ci_sequence_number,
331 p_acad_cal_type,
332 p_acad_ci_sequence_number,
333 p_offered_ind,
334 'Y', --(validate admission link only)
335 v_message_name) = TRUE THEN
336 v_admp_pk_ind := TRUE;
337 -- Validate unit offering option
338 IF admpl_val_offer_optn(
339 v_cal_type,
340 v_ci_sequence_number,
341 v_val_ind_type_offer) = TRUE THEN
342 v_val_ind_type := v_val_ind_type_offer;
343 v_val_off_optn_found := TRUE;
344 EXIT; -- (IGS_CA_INST_REL)
345 ELSE
346 v_val_ind_type := v_val_ind_type_offer;
347 v_val_off_optn_found := FALSE;
348 END IF;
349 END IF;
350 END LOOP; -- (IGS_CA_INST_REL)
351 -- set flag to 1 so that the error msg number could be returned
352 IF v_cir_found = FALSE OR v_admp_pk_ind = FALSE THEN
353 v_val_ind_type := 1;
354 END IF;
355 ELSE
356 -- Validate the teaching period
357 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_ci(
358 p_cal_type,
359 p_ci_sequence_number,
360 p_adm_cal_type,
361 p_adm_ci_sequence_number,
362 p_acad_cal_type,
363 p_acad_ci_sequence_number,
364 p_offered_ind,
365 'N', -- (validate admission link only)
366 v_message_name) = FALSE THEN
367 p_message_name := v_message_name;
368 RETURN FALSE;
369 END IF;
370 v_cal_type := p_cal_type;
371 v_ci_sequence_number := p_ci_sequence_number;
372 -- Validate unit offering option
373 IF admpl_val_offer_optn(
374 v_cal_type,
375 v_ci_sequence_number,
376 v_val_ind_type_offer) = TRUE THEN
377 v_val_ind_type := v_val_ind_type_offer;
378 v_val_off_optn_found := TRUE;
379 ELSE
380 v_val_ind_type := v_val_ind_type_offer;
381 END IF;
382 END IF;
383 IF v_val_off_optn_found = FALSE AND v_val_ind_type = 1 THEN
384 p_message_name:= 'IGS_AD_NO_UOO_MATCHING';
385 RETURN FALSE;
386 END IF;
387 IF v_val_off_optn_found = FALSE AND v_val_ind_type = 2 THEN
388 p_message_name := 'IGS_AD_NO_OFR_UOO_MATCHING';
389 RETURN FALSE;
390 END IF;
391 END IF;
392 RETURN TRUE;
393 END;
394 EXCEPTION
395 WHEN OTHERS THEN
396 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
397 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_opt');
398 IGS_GE_MSG_STACK.ADD;
399 App_Exception.Raise_Exception;
400 END admp_val_acaiu_opt;
401
402 --
403 -- Validate the admission course application instance unit outcome status
404 FUNCTION admp_val_acaiu_auos(
405 p_person_id IN NUMBER ,
406 p_admission_appl_number IN NUMBER ,
407 p_nominated_course_cd IN VARCHAR2 ,
408 p_acai_sequence_number IN NUMBER ,
409 p_adm_unit_outcome_status IN VARCHAR2 ,
410 p_message_name OUT NOCOPY VARCHAR2 )
411 RETURN BOOLEAN AS
412 BEGIN -- admp_val_acaiu_auos
413 -- This module validates IGS_AD_PS_APLINSTUNT outcome status.
414 DECLARE
415 cst_pending CONSTANT VARCHAR2(7) :='PENDING';
416 cst_offer CONSTANT VARCHAR2(5) :='OFFER';
417 cst_cond_offer CONSTANT VARCHAR2(10) :='COND-OFFER';
418 cst_withdrawn CONSTANT VARCHAR2(9) :='WITHDRAWN';
419 cst_voided CONSTANT VARCHAR2(6) :='VOIDED';
420 v_message_name VARCHAR2(30);
421 v_s_adm_outcome_status_aos IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
422 v_s_adm_outcome_status_auos IGS_AD_UNIT_OU_STAT.s_adm_outcome_status%TYPE;
423 CURSOR c_aos IS
424 SELECT aos.s_adm_outcome_status
425 FROM IGS_AD_PS_APPL_INST acai,
426 IGS_AD_OU_STAT aos
427 WHERE acai.adm_outcome_status = aos.adm_outcome_status AND
428 acai.person_id = p_person_id AND
429 acai.admission_appl_number = p_admission_appl_number AND
430 acai.nominated_course_cd = p_nominated_course_cd AND
431 acai.sequence_number = p_acai_sequence_number;
432 BEGIN
433 -- Set the default message number
434 p_message_name := NULL;
435 -- Validate that the status is not closed.
436 IF IGS_AD_VAL_ACAIU.admp_val_auos_closed(
437 p_adm_unit_outcome_status,
438 v_message_name) = FALSE THEN
439 p_message_name := v_message_name;
440 RETURN FALSE;
441 END IF;
442 -- Validate that status does not conflict with the admission course
443 -- application status.
444 OPEN c_aos;
445 FETCH c_aos INTO v_s_adm_outcome_status_aos;
446 IF c_aos%FOUND THEN
447 CLOSE c_aos;
448 -- Get system value for unit outcome status
449 v_s_adm_outcome_status_auos := IGS_AD_GEN_008.ADMP_GET_SAUOS(
450 p_adm_unit_outcome_status);
451 -- Validate statuses
452 IF v_s_adm_outcome_status_auos = cst_pending AND
453 v_s_adm_outcome_status_aos IN (
454 cst_offer,
455 cst_cond_offer,
456 cst_withdrawn,
457 cst_voided) THEN
458 p_message_name := 'IGS_PR_OUTCOME_ST_NOTBE_PEND';
459 RETURN FALSE;
460 END IF;
461 ELSE
462 CLOSE c_aos;
463 END IF;
464 RETURN TRUE;
465 EXCEPTION
466 WHEN OTHERS THEN
467 IF c_aos%ISOPEN THEN
468 CLOSE c_aos;
469 END IF;
470 RAISE;
471 END;
472 EXCEPTION
473 WHEN OTHERS THEN
474 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
475 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_auos');
476 IGS_GE_MSG_STACK.ADD;
477 App_Exception.Raise_Exception;
478 END admp_val_acaiu_auos;
479
480 --
481 -- Validate the admission course application instance unit cal. instance
482 FUNCTION admp_val_acaiu_ci(
483 p_teach_cal_type IN VARCHAR2 ,
484 p_teach_ci_sequence_number IN NUMBER ,
485 p_adm_cal_type IN VARCHAR2 ,
486 p_adm_ci_sequence_number IN NUMBER ,
487 p_acad_cal_type IN VARCHAR2 ,
488 p_acad_ci_sequence_number IN NUMBER ,
489 p_val_adm_only_ind IN VARCHAR2 DEFAULT 'N',
490 p_offered_ind IN VARCHAR2 DEFAULT 'N',
491 p_message_name OUT NOCOPY VARCHAR2 )
492 RETURN BOOLEAN AS
493 BEGIN -- admp_val_acaiu_ci
494 -- This module validates the teaching period IGS_CA_INST given for the
495 -- IGS_AD_PS_APLINSTUNT.
496 DECLARE
497 cst_teaching CONSTANT VARCHAR2(8) := 'TEACHING';
498 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
499 cst_planned CONSTANT VARCHAR2(7) := 'PLANNED';
500 cst_academic CONSTANT VARCHAR2(8) := 'ACADEMIC';
501 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
502 v_dummy VARCHAR2(1);
503 CURSOR c_teach_perd IS
504 SELECT cs.s_cal_status,
505 cat.s_cal_cat
506 FROM IGS_CA_INST ci,
507 IGS_CA_STAT cs,
508 IGS_CA_TYPE cat
509 WHERE ci.cal_status = cs.cal_status AND
510 ci.cal_type = cat.cal_type AND
511 ci.cal_type = p_teach_cal_type AND
512 ci.sequence_number = p_teach_ci_sequence_number;
513 v_teach_perd_rec c_teach_perd%ROWTYPE;
514 CURSOR c_chk_within_acad IS
515 SELECT 'x'
516 FROM IGS_CA_INST_REL cir,
517 IGS_CA_TYPE cat
518 WHERE cir.sup_cal_type = cat.cal_type AND
519 cir.sup_cal_type = p_acad_cal_type AND
520 cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
521 cir.sub_cal_type = p_teach_cal_type AND
522 cir.sub_ci_sequence_number = p_teach_ci_sequence_number AND
523 cat.s_cal_cat = cst_academic;
524 CURSOR c_chk_include_adm IS
525 SELECT 'x'
526 FROM IGS_CA_INST_REL cir,
527 IGS_CA_TYPE cat
528 WHERE cir.sub_cal_type = cat.cal_type AND
529 cir.sup_cal_type = p_teach_cal_type AND
530 cir.sup_ci_sequence_number = p_teach_ci_sequence_number AND
531 cir.sub_cal_type = p_adm_cal_type AND
532 cir.sub_ci_sequence_number = p_adm_ci_sequence_number AND
533 cat.s_cal_cat = cst_admission;
534 BEGIN
535 -- Set default value.
536 p_message_name := NULL;
537 -- Validate that the calendar instance is a teaching calendar of the
538 -- right status.
539 OPEN c_teach_perd;
540 LOOP
541 FETCH c_teach_perd INTO v_teach_perd_rec;
542 EXIT WHEN c_teach_perd%NOTFOUND;
543 END LOOP;
544 IF c_teach_perd%ROWCOUNT = 0 THEN
545 CLOSE c_teach_perd;
546 RETURN TRUE;
547 ELSE
548 CLOSE c_teach_perd;
549 IF v_teach_perd_rec.s_cal_cat <> cst_teaching THEN
550 p_message_name := 'IGS_AD_CALINST_NOT_TEACHING';
551 RETURN FALSE;
552 END IF;
553 IF p_offered_ind = 'N' AND
554 v_teach_perd_rec.s_cal_status = cst_inactive THEN
555 p_message_name := 'IGS_AD_CALST_NOT_ACTIVE_PLAN';
556 RETURN FALSE;
557 END IF;
558 IF p_offered_ind = 'Y' AND
559 v_teach_perd_rec.s_cal_status IN(
560 cst_inactive,
561 cst_planned) THEN
562 p_message_name := 'IGS_AD_CALST_NOT_ACTIVE';
563 RETURN FALSE;
564 END IF;
565 END IF;
566 -- Validate that the teaching period is a calendar instance in the
567 -- academic period of the admission course application.
568 IF p_val_adm_only_ind = 'N' THEN
569 OPEN c_chk_within_acad;
570 FETCH c_chk_within_acad INTO v_dummy;
571 IF c_chk_within_acad%NOTFOUND THEN
572 CLOSE c_chk_within_acad;
573 p_message_name := 'IGS_AD_TCHPRD_NOT_LINKED';
574 RETURN FALSE;
575 END IF;
576 CLOSE c_chk_within_acad;
577 END IF;
578 -- Validate that the teaching period is a superior calendar instance
579 -- for the admission period of the admission course application.
580 OPEN c_chk_include_adm;
581 FETCH c_chk_include_adm INTO v_dummy;
582 IF c_chk_include_adm%NOTFOUND THEN
583 CLOSE c_chk_include_adm;
584 p_message_name := 'IGS_AD_TEACHPRD_NOT_LINKED';
585 RETURN FALSE;
586 END IF;
587 CLOSE c_chk_include_adm;
588 RETURN TRUE;
589 EXCEPTION
590 WHEN OTHERS THEN
591 IF c_teach_perd%ISOPEN THEN
592 CLOSE c_teach_perd;
593 END IF;
594 IF c_chk_within_acad%ISOPEN THEN
595 CLOSE c_chk_within_acad;
596 END IF;
597 IF c_chk_include_adm%ISOPEN THEN
598 CLOSE c_chk_include_adm;
599 END IF;
600 RAISE;
601 END;
602 EXCEPTION
603 WHEN OTHERS THEN
604 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
605 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_ci');
606 IGS_GE_MSG_STACK.ADD;
607 App_Exception.Raise_Exception;
608 END admp_val_acaiu_ci;
609
610 --
611 -- Validate the unit mode of the admission course application inst unit.
612 FUNCTION admp_val_acaiu_um(
613 p_unit_class IN VARCHAR2 ,
614 p_unit_mode IN VARCHAR2 ,
615 p_message_name OUT NOCOPY VARCHAR2 )
616 RETURN BOOLEAN AS
617 BEGIN -- admp_val_acaiu_um
618 -- This module validates that the IGS_AD_PS_APLINSTUNT unit class
619 -- and unit mode do not conflict.
620 DECLARE
621 v_dummy VARCHAR2(1);
622 CURSOR c_um IS
623 SELECT 'x'
624 FROM IGS_AS_UNIT_CLASS uc
625 WHERE uc.unit_mode = p_unit_mode AND
626 uc.unit_class = p_unit_class;
627 BEGIN
628 -- Set default value.
629 p_message_name := NULL;
630 IF p_unit_mode IS NOT NULL AND
631 p_unit_class IS NOT NULL THEN
632 OPEN c_um;
633 FETCH c_um INTO v_dummy ;
634 IF c_um%NOTFOUND THEN
635 CLOSE c_um;
636 p_message_name := 'IGS_AD_UC_UM_INCOMPLATIBLE';
637 RETURN FALSE;
638 END IF;
639 CLOSE c_um;
640 END IF;
641 RETURN TRUE;
642 EXCEPTION
643 WHEN OTHERS THEN
644 IF c_um%ISOPEN THEN
645 CLOSE c_um;
646 END IF;
647 RAISE;
648 END;
649 EXCEPTION
650 WHEN OTHERS THEN
651 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
652 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_um');
653 IGS_GE_MSG_STACK.ADD;
654 App_Exception.Raise_Exception;
655 END admp_val_acaiu_um;
656
657 --
658 -- Validate the admission course application instance unit restr number
659 FUNCTION admp_val_acaiu_restr(
660 p_person_id IN NUMBER ,
661 p_admission_appl_number IN NUMBER ,
662 p_nominated_course_cd IN VARCHAR2 ,
663 p_acai_sequence_number IN NUMBER ,
664 p_unit_cd IN VARCHAR2 ,
665 p_unit_restriction_num IN NUMBER ,
666 p_message_name OUT NOCOPY VARCHAR2,
667 p_uv_version_number IN NUMBER , -- Added for bug 3083148
668 p_cal_type IN VARCHAR2 , -- Added for bug 3083148
669 p_ci_sequence_number IN NUMBER , -- Added for bug 3083148
670 p_location_cd IN VARCHAR2 , -- Added for bug 3083148
671 p_unit_class IN VARCHAR2 ) -- Added for bug 3083148
672 RETURN BOOLEAN AS
673 BEGIN -- admp_val_acaiu_restr
674 -- This module validates the IGS_AD_PS_APLINSTUNT unit
675 -- restriction number.
676 DECLARE
677 v_count NUMBER(5);
678 CURSOR c_count_acaiu IS
679 SELECT count(*)
680 FROM IGS_AD_PS_APLINSTUNT acaiu
681 WHERE acaiu.person_id = p_person_id AND
682 acaiu.admission_appl_number = p_admission_appl_number AND
683 acaiu.nominated_course_cd = p_nominated_course_cd AND
684 acaiu.acai_sequence_number = p_acai_sequence_number AND
685 acaiu.unit_cd <> p_unit_cd AND
686 acaiu.uv_version_number <> p_uv_version_number AND
687 NVL(acaiu.cal_type,'*-1') <> NVL(p_cal_type,'*-2') AND
688 NVL(acaiu.ci_sequence_number,-1) <> NVL(p_ci_sequence_number,-2) AND
689 NVL(acaiu.location_cd,'*-1') <> NVL(p_location_cd,'*-2') AND
690 NVL(acaiu.unit_class,'*-1') <> NVL(p_unit_class,'*-2');
691 BEGIN
692 -- Set default value.
693 p_message_name := NULL;
694 OPEN c_count_acaiu;
695 FETCH c_count_acaiu INTO v_count;
696 CLOSE c_count_acaiu;
697 IF v_count > NVL(p_unit_restriction_num,0) THEN
698 p_message_name := 'IGS_AD_UNITS_CANNOT_CREATED';
699 RETURN FALSE;
700 END IF;
701 RETURN TRUE;
702 EXCEPTION
703 WHEN OTHERS THEN
704 IF c_count_acaiu%ISOPEN THEN
705 CLOSE c_count_acaiu;
706 END IF;
707 RAISE;
708 END;
709 EXCEPTION
710 WHEN OTHERS THEN
711 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
712 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_restr');
713 IGS_GE_MSG_STACK.ADD;
714 App_Exception.Raise_Exception;
715 END admp_val_acaiu_restr;
716
717 --
718 -- Validate if IGS_AD_OU_STAT.adm_outcome_status is closed.
719 FUNCTION admp_val_auos_closed(
720 p_adm_unit_outcome_status IN VARCHAR2 ,
721 p_message_name OUT NOCOPY VARCHAR2 )
722 RETURN BOOLEAN AS
723 BEGIN --admp_val_auos_closed
724 --Validate if IGS_AD_UNIT_OU_STAT.adm_unit_outcome_status is closed
725 DECLARE
726 v_auos_exists VARCHAR2(1);
727 CURSOR c_auos IS
728 SELECT 'x'
729 FROM IGS_AD_UNIT_OU_STAT auos
730 WHERE adm_unit_outcome_status = p_adm_unit_outcome_status AND
731 closed_ind = 'Y';
732 BEGIN
733 --set the default message number
734 p_message_name := null ;
735 OPEN c_auos;
736 FETCH c_auos INTO v_auos_exists;
737 IF (c_auos%FOUND) THEN
738 CLOSE c_auos;
739 p_message_name := 'IGS_AD_UNIT_OUTCOME_ST_CLOSED';
740 RETURN FALSE;
741 END IF;
742 CLOSE c_auos;
743 RETURN TRUE;
744 EXCEPTION
745 WHEN OTHERS THEN
746 IF (c_auos%ISOPEN) THEN
747 CLOSE c_auos;
748 END IF;
749 RAISE;
750 END;
751 EXCEPTION
752 WHEN OTHERS THEN
753 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
754 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_auos_closed');
755 IGS_GE_MSG_STACK.ADD;
756 App_Exception.Raise_Exception;
757 END admp_val_auos_closed;
758
759 --
760 -- Validate the admission course application instance unit alternate code
761 FUNCTION admp_val_acaiu_altcd(
762 p_alternate_code IN VARCHAR2 ,
763 p_unit_cd IN VARCHAR2 ,
764 p_uv_version_number IN NUMBER ,
765 p_adm_cal_type IN VARCHAR2 ,
766 p_adm_ci_sequence_number IN NUMBER ,
767 p_acad_cal_type IN VARCHAR2 ,
768 p_acad_ci_sequence_number IN NUMBER ,
769 p_message_name OUT NOCOPY VARCHAR2 )
770 RETURN BOOLEAN AS
771 BEGIN -- admp_val_acaiu_altcd
772 -- This module validates the teaching period alternate code given
773 -- for the IGS_AD_PS_APLINSTUNT
774 DECLARE
775 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
776 CURSOR c_cat IS
777 SELECT cat.s_cal_cat
778 FROM IGS_CA_INST ci,
779 IGS_CA_TYPE cat
780 WHERE ci.alternate_code = p_alternate_code AND
781 ci.cal_type = cat.cal_type;
782 CURSOR c_cir IS
783 SELECT ci.cal_type,
784 ci.sequence_number,
785 cs.s_cal_status
786 FROM IGS_CA_INST_REL cir,
787 IGS_CA_INST ci,
788 IGS_CA_TYPE cat,
789 IGS_CA_STAT cs
790 WHERE cir.sup_cal_type = p_acad_cal_type AND
791 cir.sup_ci_sequence_number = p_acad_ci_sequence_number AND
792 ci.sequence_number = cir.sub_ci_sequence_number AND
793 ci.cal_type = cir.sub_cal_type AND
794 ci.alternate_code = p_alternate_code AND
795 cat.cal_type = ci.cal_type AND
796 cs.cal_status = ci.cal_status AND
797 cat.s_cal_cat = cst_teaching AND
798 ci.cal_status = cs.cal_status;
799 CURSOR c_cir_cat(
800 cp_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
801 cp_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
802 SELECT 'x'
803 FROM IGS_CA_INST_REL cir,
804 IGS_CA_TYPE cat
805 WHERE cir.sub_cal_type = p_adm_cal_type AND
806 cir.sub_ci_sequence_number = p_adm_ci_sequence_number AND
807 cir.sup_cal_type = cp_cal_type AND
808 cir.sup_ci_sequence_number = cp_sequence_number;
809 CURSOR c_uoo (
810 cp_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
811 cp_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
812 SELECT 'x'
813 FROM IGS_PS_UNIT_OFR_OPT uoo
814 WHERE uoo.unit_cd = p_unit_cd AND
815 uoo.version_number = p_uv_version_number AND
816 uoo.cal_type = cp_cal_type AND
817 uoo.ci_sequence_number = cp_sequence_number AND
818 uoo.offered_ind = 'Y';
819 v_cat_found BOOLEAN DEFAULT FALSE;
820 v_alternate_code_valid BOOLEAN DEFAULT FALSE;
821 v_cir_found BOOLEAN DEFAULT FALSE;
822 v_planned_active_found BOOLEAN DEFAULT FALSE;
823 v_cir_cat_exists VARCHAR2(1);
824 v_alternate_code_teach BOOLEAN DEFAULT FALSE;
825 v_uoo_exists VARCHAR2(1);
826 v_uoo_found BOOLEAN DEFAULT FALSE;
827 BEGIN
828 -- Set the default message number
829 p_message_name := NULL;
830 -- Validate alternate code
831 FOR v_cat_rec IN c_cat LOOP
832 v_cat_found := TRUE;
833 IF v_cat_rec.s_cal_cat = cst_teaching THEN
834 v_alternate_code_valid := TRUE;
835 EXIT;
836 END IF;
837 END LOOP;
838 IF NOT v_cat_found THEN
839 p_message_name := 'IGS_AD_TCHPRD_DOESNOT_EXIST';
840 RETURN FALSE;
841 END IF;
842 IF NOT v_alternate_code_valid THEN
843 p_message_name := 'IGS_AD_TCHPRD_MAPS_CALINST';
844 RETURN FALSE;
845 END IF;
846 -- Validate that alternate code has at least one calendar instance
847 -- in the academic period of the admission application that is
848 -- active.
849 FOR v_cir_rec IN c_cir LOOP
850 v_cir_found := TRUE;
851 IF v_cir_rec.s_cal_status IN ('ACTIVE') THEN --removed the planned status as per bug#2722785 --rghosh
852 v_planned_active_found := TRUE;
853 -- Validate that alternate code is teaching
854 -- period in the admission period.
855 OPEN c_cir_cat(
856 v_cir_rec.cal_type,
857 v_cir_rec.sequence_number);
858 FETCH c_cir_cat INTO v_cir_cat_exists;
859 IF c_cir_cat%FOUND THEN
860 v_alternate_code_teach := TRUE;
861 -- Validate unit offering option exists for the unit code
862 -- in the teaching period specified for the academic
863 -- period/admission.
864 OPEN c_uoo (
865 v_cir_rec.cal_type,
866 v_cir_rec.sequence_number);
867 FETCH c_uoo INTO v_uoo_exists;
868 IF c_uoo%FOUND THEN
869 CLOSE c_cir_cat;
870 CLOSE c_uoo;
871 v_uoo_found := TRUE;
872 EXIT;
873 END IF;
874 CLOSE c_uoo;
875 END IF;
876 CLOSE c_cir_cat;
877 END IF;
878 END LOOP;
879 IF NOT v_cir_found THEN
880 -- The alternate code is not a teaching calendar in
881 -- the academic period of the admission application.
882 p_message_name := 'IGS_AD_TCHPRD_NOTIN_ACADEMIC';
883 RETURN FALSE;
884 END IF;
885 IF NOT v_planned_active_found THEN
886 -- Alternate code has no active teaching
887 -- calendars in the academic period of the admission applcaition
888 p_message_name := 'IGS_AD_TCHPRD_EXISTS_ACADEMIC';
889 RETURN FALSE;
890 END IF;
891 IF NOT v_alternate_code_teach THEN
892 -- Alternate Code is not a teaching period in the
893 -- admission period of the admission application.
894 p_message_name := 'IGS_AD_TCHPRD_NOTIN_COMPRD';
895 RETURN FALSE;
896 END IF;
897 IF NOT v_uoo_found THEN
898 -- Alternate code is a teaching period in the admission
899 -- period but there are no offerings of the unit in the
900 -- academic period/admission period.
901 p_message_name := 'IGS_AD_TCHPRD_NO_UOO';
902 RETURN FALSE;
903 END IF;
904 -- Return the default value
905 RETURN TRUE;
906 END;
907 EXCEPTION
908 WHEN OTHERS THEN
909 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
910 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_altcd');
911 IGS_GE_MSG_STACK.ADD;
912 App_Exception.Raise_Exception;
913 END admp_val_acaiu_altcd;
914
915 --
916 -- Validate the unit version of the admission course application.
917 FUNCTION admp_val_acaiu_uv(
918 p_unit_cd IN VARCHAR2 ,
919 p_uv_version_number IN NUMBER ,
920 p_s_admission_process_type IN VARCHAR2 ,
921 p_offered_ind IN VARCHAR2 ,
922 p_message_name OUT NOCOPY VARCHAR2 )
923 RETURN BOOLEAN AS
924 BEGIN -- admp_val_acaiu_uv
925 -- This module validate IGS_AD_PS_APLINSTUNT unit version.
926 DECLARE
927 v_award_course_only_ind IGS_PS_UNIT_VER.award_course_only_ind%TYPE;
928 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
929 CURSOR c_val_unit_version IS
930 SELECT uv.award_course_only_ind,
931 us.s_unit_status
932 FROM IGS_PS_UNIT_VER uv,
933 IGS_PS_UNIT_STAT us
934 WHERE uv.unit_cd = p_unit_cd AND
935 uv.version_number = p_uv_version_number AND
936 us.unit_status = uv.unit_status;
937 BEGIN
938 -- Set the default message number
939 p_message_name := NULL;
940 -- Validate unit version
941 OPEN c_val_unit_version;
942 FETCH c_val_unit_version INTO v_award_course_only_ind,
943 v_s_unit_status;
944 IF c_val_unit_version%NOTFOUND THEN
945 CLOSE c_val_unit_version;
946 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
947 RETURN FALSE;
948 ELSE
949 CLOSE c_val_unit_version;
950 IF (v_s_unit_status <> 'ACTIVE') THEN --removed the planned status as per bug#2722785 --rghosh
951 -- unit Version must be active.
952 p_message_name := 'IGS_AD_UNITVER_DOESNOT_ACTIVE';
953 RETURN FALSE;
954 END IF;
955 IF (p_offered_ind = 'Y' AND
956 v_s_unit_status = 'PLANNED') THEN
957 -- unit Version must be active when offered
958 p_message_name := 'IGS_AD_UNITVER_ST_ACTIVE';
959 RETURN FALSE;
960 END IF;
961 IF (p_s_admission_process_type = 'NON-AWARD') THEN
962 IF(v_award_course_only_ind = 'Y') THEN
963 -- unit version is for award courses only
964 p_message_name := 'IGS_AD_UNITVER_AWARD_PRG';
965 RETURN FALSE;
966 END IF;
967 END IF;
968 END IF;
969 RETURN TRUE;
970 END;
971 EXCEPTION
972 WHEN OTHERS THEN
973 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
974 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_uv');
975 IGS_GE_MSG_STACK.ADD;
976 App_Exception.Raise_Exception;
977 END admp_val_acaiu_uv;
978
979 --
980 -- Do encumbrance check for admission_course_appl_instance_unit.unit_cd.
981 FUNCTION admp_val_acaiu_encmb(
982 p_person_id IN NUMBER ,
983 p_course_cd IN VARCHAR2 ,
984 p_unit_cd IN VARCHAR2 ,
985 p_adm_cal_type IN VARCHAR2 ,
986 p_adm_ci_sequence_number IN NUMBER ,
987 p_unit_encmb_chk_ind IN VARCHAR2 DEFAULT 'N',
988 p_offer_ind IN VARCHAR2 DEFAULT 'N',
989 p_message_name OUT NOCOPY VARCHAR2 ,
990 p_return_type OUT NOCOPY VARCHAR2 )
991 RETURN BOOLEAN AS
992 BEGIN -- admp_val_acaiu_encmb
993 -- Perform encumbrance checks for the
994 -- admission_course_appl_instance_unit.unit_cd
995 DECLARE
996 v_message_name VARCHAR2(30) DEFAULT NULL;
997 v_encmb_check_dt DATE;
998 cst_error CONSTANT VARCHAR2(1) := 'E';
999 cst_warn CONSTANT VARCHAR2(1) := 'W';
1000 BEGIN
1001 -- Set the default message number
1002 p_message_name := NULL;
1003 IF p_unit_encmb_chk_ind = 'Y' THEN
1004 -- Get the encumbrance checking date.
1005 v_encmb_check_dt := IGS_AD_GEN_006.ADMP_GET_ENCMB_DT(
1006 p_adm_cal_type,
1007 p_adm_ci_sequence_number);
1008 IF v_encmb_check_dt IS NULL THEN
1009 IF p_offer_ind = 'Y' THEN
1010 p_message_name := 'IGS_AD_ENCUMB_CANNOT_PERFORM';
1011 p_return_type := cst_error;
1012 ELSE
1013 p_message_name := 'IGS_AD_ENCUMB_CHK_NOT_PERFORM';
1014 p_return_type := cst_warn;
1015 END IF;
1016 RETURN FALSE;
1017 END IF;
1018 -- Validate for exclusion or suspension from the unit within the course
1019 IF IGS_EN_VAL_ENCMB.enrp_val_excld_unit(
1020 p_person_id,
1021 p_course_cd,
1022 p_unit_cd,
1023 v_encmb_check_dt,
1024 v_message_name) = FALSE THEN
1025 IF p_offer_ind = 'Y' THEN
1026 p_message_name := 'IGS_AD_PRSN_ENCUMB_EXC_PRG';
1027 p_return_type := cst_error;
1028 ELSE
1029 p_message_name := 'IGS_AD_PRSN_ENCUMB_EXC_UNIT';
1030 p_return_type := cst_warn;
1031 END IF;
1032 RETURN FALSE;
1033 END IF;
1034 END IF;
1035 -- Return the default value
1036 RETURN TRUE;
1037 END;
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1041 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_encmb');
1042 IGS_GE_MSG_STACK.ADD;
1043 App_Exception.Raise_Exception;
1044 END admp_val_acaiu_encmb;
1045
1046 --
1047 -- Validate an admission course application instance research unit.
1048 FUNCTION admp_val_res_unit(
1049 p_person_id IN NUMBER ,
1050 p_admission_appl_number IN NUMBER ,
1051 p_nominated_course_cd IN VARCHAR2 ,
1052 p_acai_sequence_number IN NUMBER ,
1053 p_unit_cd IN VARCHAR2 ,
1054 p_uv_version_number IN NUMBER ,
1055 p_course_cd IN VARCHAR2 ,
1056 p_offer_ind IN VARCHAR2 DEFAULT 'N',
1057 p_s_admission_process_type IN VARCHAR2 ,
1058 p_message_name OUT NOCOPY VARCHAR2 ,
1059 p_return_type OUT NOCOPY VARCHAR2 )
1060 RETURN BOOLEAN AS
1061 BEGIN -- admp_val_res_unit
1062 -- Description: Validate an admission course application instance
1063 -- research unit.
1064 DECLARE
1065 v_uv_rec VARCHAR2(1);
1066 v_can_rec VARCHAR2(1);
1067 v_admission_appl_number
1068 IGS_AD_PS_APLINSTUNT.admission_appl_number%TYPE;
1069 v_nominated_course_cd IGS_AD_PS_APLINSTUNT.nominated_course_cd%TYPE;
1070 v_acai_sequence_number
1071 IGS_AD_PS_APLINSTUNT.acai_sequence_number%TYPE;
1072 cst_error CONSTANT VARCHAR2(1) := 'E';
1073 cst_warn CONSTANT VARCHAR2(1) := 'W';
1074 cst_readmit CONSTANT VARCHAR2(9) := 'RE-ADMIT';
1075 CURSOR c_uv IS
1076 SELECT 'X'
1077 FROM IGS_PS_UNIT_VER uv
1078 WHERE uv.unit_cd = p_unit_cd AND
1079 uv.version_number = p_uv_version_number AND
1080 uv.research_unit_ind = 'Y';
1081 CURSOR c_can IS
1082 SELECT 'X'
1083 FROM IGS_RE_CANDIDATURE can
1084 WHERE can.person_id = p_person_id AND
1085 ((v_admission_appl_number IS NOT NULL AND
1086 can.acai_admission_appl_number = v_admission_appl_number AND
1087 v_nominated_course_cd IS NOT NULL AND
1088 can.acai_nominated_course_cd = v_nominated_course_cd AND
1089 v_acai_sequence_number IS NOT NULL AND
1090 can.acai_sequence_number = v_acai_sequence_number) OR
1091 (p_course_cd IS NOT NULL AND
1092 can.sca_course_cd = p_course_cd));
1093 BEGIN
1094 p_message_name := NULL;
1095 OPEN c_uv;
1096 FETCH c_uv INTO v_uv_rec;
1097 IF (c_uv%FOUND) THEN
1098 CLOSE c_uv;
1099 IF p_s_admission_process_type = cst_readmit THEN
1100 IGS_RE_GEN_002.RESP_GET_SCA_CA_ACAI (
1101 p_person_id,
1102 p_course_cd,
1103 p_admission_appl_number,
1104 p_nominated_course_cd,
1105 p_acai_sequence_number,
1106 v_admission_appl_number,
1107 v_nominated_course_cd,
1108 v_acai_sequence_number);
1109 ELSE
1110 v_admission_appl_number := p_admission_appl_number;
1111 v_nominated_course_cd := p_nominated_course_cd;
1112 v_acai_sequence_number := p_acai_sequence_number;
1113 END IF;
1114 OPEN c_can;
1115 FETCH c_can INTO v_can_rec;
1116 IF (c_can%NOTFOUND) THEN
1117 CLOSE c_can;
1118 IF p_offer_ind = 'Y' THEN
1119 p_message_name := 'IGS_AD_RESCAND_NOT_SUPPLIED';
1120 p_return_type := cst_error;
1121 RETURN FALSE;
1122 ELSE
1123 p_message_name := 'IGS_AD_RES_CANDIDATURE_MUSTEX';
1124 p_return_type := cst_warn;
1125 RETURN FALSE;
1126 END IF;
1127 END IF;
1128 CLOSE c_can;
1129 ELSE
1130 CLOSE c_uv;
1131 END IF;
1132 RETURN TRUE;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 IF (c_uv%ISOPEN) THEN
1136 CLOSE c_uv;
1137 END IF;
1138 IF (c_can%ISOPEN) THEN
1139 CLOSE c_can;
1140 END IF;
1141 RAISE;
1142 END;
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1146 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_res_unit');
1147 IGS_GE_MSG_STACK.ADD;
1148 App_Exception.Raise_Exception;
1149 END admp_val_res_unit;
1150
1151 --
1152 -- Validate the adm course appl inst unit against the teaching period.
1153 FUNCTION admp_val_acaiu_uv_ci(
1154 p_unit_cd IN VARCHAR2 ,
1155 p_uv_version_number IN NUMBER ,
1156 p_teach_cal_type IN VARCHAR2 ,
1157 p_teach_ci_sequence_number IN NUMBER ,
1158 p_message_name OUT NOCOPY VARCHAR2 )
1159 RETURN BOOLEAN AS
1160 BEGIN -- admp_val_acaiu_uv_ci
1161 -- Validate the admission course application instance unit version against
1162 -- the teaching calendar
1163 DECLARE
1164 v_expiry_dt IGS_PS_UNIT_VER.expiry_dt%TYPE;
1165 v_start_dt IGS_CA_INST.start_dt%TYPE;
1166 CURSOR c_uv IS
1167 SELECT uv.expiry_dt
1168 FROM IGS_PS_UNIT_VER uv
1169 WHERE uv.unit_cd = p_unit_cd AND
1170 uv.version_number = p_uv_version_number AND
1171 uv.expiry_dt IS NOT NULL;
1172 CURSOR c_ci IS
1173 SELECT ci.start_dt
1174 FROM IGS_CA_INST ci
1175 WHERE ci.cal_type = p_teach_cal_type AND
1176 ci.sequence_number = p_teach_ci_sequence_number;
1177 BEGIN
1178 -- Set the default message number
1179 p_message_name := NULL;
1180 -- Validate if the expiry date of the unit version is set
1181 OPEN c_uv;
1182 FETCH c_uv INTO v_expiry_dt;
1183 IF c_uv%FOUND THEN
1184 CLOSE c_uv;
1185 -- Determine the start date of the teaching period
1186 OPEN c_ci;
1187 FETCH c_ci INTO v_start_dt;
1188 IF c_ci%FOUND THEN
1189 CLOSE c_ci;
1190 IF v_expiry_dt < v_start_dt THEN
1191 p_message_name := 'IGS_AD_UNITVER_EXP_DT_AFTER';
1192 RETURN FALSE;
1193 END IF;
1194 ELSE
1195 CLOSE c_ci;
1196 END IF;
1197 ELSE
1198 CLOSE c_uv;
1199 END IF;
1200 RETURN TRUE ;
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 IF c_uv%ISOPEN THEN
1204 CLOSE c_uv;
1205 END IF;
1206 IF c_ci%ISOPEN THEN
1207 CLOSE c_ci;
1208 END IF;
1209 RAISE;
1210 END;
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1214 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAIU.admp_val_acaiu_uv_ci');
1215 IGS_GE_MSG_STACK.ADD;
1216 App_Exception.Raise_Exception;
1217 END admp_val_acaiu_uv_ci;
1218 END IGS_AD_VAL_ACAIU;
[Home] [Help]