1 PACKAGE BODY igs_ad_val_acai AS
2 /* $Header: IGSAD22B.pls 120.6 2006/05/30 10:58:04 pbondugu ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --
7 -- bug id : 1956374
8 -- sjadhav , 28-aug-2001
9 -- removed function enrp_val_hpo_closed
10 -- change igs_ad_val_acai.enrp_val_hpo_closed
11 -- to igs_en_val_scho.enrp_val_hpo_closed
12 --
13 --smadathi 28-AUG-2001 Bug No. 1956374 .The exception part removed from genp_val_staff_prsn
14 --msrinivi bug 1956374. pointing finp_val_fc_closed to igs_fi_val_fcm
15
16 -- bug id : 1956374
17 -- sjadhav , 28-aug-2001
18 -- removed function enrp_val_ec_closed
19 -- change igs_ad_val_acai.enrp_val_ec_closed
20 -- to igs_ad_val_ecm.enrp_val_ec_closed
21
22 --samaresh 21-DEC-2001 Bug No. 2158524 . The cursor c_ar has been modified as the
23 -- the table IGS_AD_APP_ REQ has been moved from application
24 -- instance level to application level
25 -- ssawhney 24-oct-2002 admp_val_acai_comp : SWS104 build 2630860, AD_ACAD_HONOR reference moved to PE.
26 -- removed DEFAULTs in declarations and parameters
27 --hreddych 13-dec-2002 function admp_val_acai_insert modified to check for completed application
28 -- also when called with the parameter p_validate_aa_only set to TRUE .
29 --sarakshi 27-Feb-2003 Enh#2797116,modified procedure admp_val_acai_coo ,added delete_falg check in the where clause
30 -- of the cursor c_coo
31 -- rghosh 03-mar-2003 Changed the signature of the function ADMP_VAL_DFRMNT_CAL (bug#2765260)
32 -------------------------------------------------------------------------------------------
33
34 --
35 -- Validate insert of an IGS_AD_PS_APPL_INST record.
36
37 FUNCTION admp_val_acai_insert(
38 p_person_id IN NUMBER ,
39 p_admission_appl_number IN NUMBER ,
40 p_nominated_course_cd IN VARCHAR2 ,
41 p_acai_sequence_number IN NUMBER ,
42 p_course_cd IN VARCHAR2 ,
43 p_location_cd IN VARCHAR2 ,
44 p_attendance_mode IN VARCHAR2 ,
45 p_attendance_type IN VARCHAR2 ,
46 p_unit_set_cd IN VARCHAR2 ,
47 p_us_version_number IN NUMBER ,
48 p_adm_cal_type IN VARCHAR2 ,
49 p_adm_ci_sequence_number IN NUMBER ,
50 p_s_admission_process_type IN VARCHAR2 ,
51 p_pref_limit IN NUMBER ,
52 p_validate_aa_only IN BOOLEAN ,
53 p_message_name OUT NOCOPY VARCHAR2,
54 p_return_type OUT NOCOPY VARCHAR2 )
55 RETURN BOOLEAN AS
56 gv_validate_aa_only VARCHAR2(1);
57 BEGIN -- admp_val_acai_insert
58 -- Routine to validate the insert of an IGS_AD_PS_APPL_INST record.
59 DECLARE
60 cst_completed CONSTANT VARCHAR2(9) := 'COMPLETED';
61 cst_withdrawn CONSTANT VARCHAR2(9) := 'WITHDRAWN';
62 cst_warn CONSTANT VARCHAR2(1) := 'W';
63 cst_error CONSTANT VARCHAR2(1) := 'E';
64 v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
65 v_message_name VARCHAR2(30);
66 v_return BOOLEAN := TRUE;
67 CURSOR c_acaiv (
68 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
69 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE,
70 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
71 cp_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
72 cp_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE,
73 cp_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE,
74 cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE,
75 cp_unit_set_cd IGS_AD_PS_APPL_INST.unit_set_cd%TYPE,
76 cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
77 cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
78 cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
79 SELECT 'x'
80 FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
81 WHERE aav.person_id = acaiv.person_id AND
82 aav.admission_appl_number = acaiv.admission_appl_number AND
83 acaiv.person_id = cp_person_id AND
84 acaiv.course_cd = cp_course_cd AND
85 (acaiv.admission_appl_number <> cp_admission_appl_number OR
86 acaiv.sequence_number <> cp_acai_sequence_number) AND
87 NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
88 NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
89 NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
90 NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
91 NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
92 NVL(acaiv.adm_cal_type, aav.adm_cal_type) = cp_adm_cal_type AND
93 NVL(acaiv.adm_ci_sequence_number, aav.adm_ci_sequence_number)
94 = cp_adm_ci_sequence_number AND
95 -- Check for CANCELLED added for bug 2678766
96 NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status),'x') <> 'CANCELLED';
97
98 -- Added the following cursor definitions for UK Bug 2462198
99 -- by rrengara on 24-JUL-2002
100
101 CURSOR c_acaiv_uk (
102 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
103 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE,
104 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
105 cp_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
106 cp_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE,
107 cp_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE,
108 cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE,
109 cp_unit_set_cd IGS_AD_PS_APPL_INST.unit_set_cd%TYPE,
110 cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
111 cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
112 cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE,
113 cp_choice_number IGS_AD_APPL.choice_number%TYPE,
114 cp_alt_appl_id IGS_AD_APPL.alt_appl_id%TYPE) IS
115 SELECT 'x'
116 FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
117 WHERE aav.person_id = acaiv.person_id AND
118 aav.admission_appl_number = acaiv.admission_appl_number AND
119 acaiv.person_id = cp_person_id AND
120 acaiv.course_cd = cp_course_cd AND
121 (acaiv.admission_appl_number <> cp_admission_appl_number OR
122 acaiv.sequence_number <> cp_acai_sequence_number) AND
123 NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
124 NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
125 NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
126 NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
127 NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
128 NVL(acaiv.adm_cal_type, aav.adm_cal_type) = cp_adm_cal_type AND
129 NVL(acaiv.adm_ci_sequence_number, aav.adm_ci_sequence_number)
130 = cp_adm_ci_sequence_number AND
131 NVL(aav.choice_number, 0) = NVL(cp_choice_number,0) AND
132 NVL(aav.alt_appl_id, 0) = NVL(cp_alt_appl_id,0) AND
133 -- Check for CANCELLED added for bug 2678766
134 NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status),'x') <> 'CANCELLED';
135
136 CURSOR c_aav_uk(cp_person_id igs_ad_appl.person_id%TYPE, cp_admission_appl_number igs_ad_appl.admission_appl_number%TYPE) IS
137 SELECT
138 choice_number, alt_appl_id
139 FROM
140 igs_ad_appl aav
141 WHERE
142 aav.person_id = cp_person_id
143 AND aav.admission_appl_number = cp_admission_appl_number;
144
145 c_aav_uk_rec c_aav_uk%ROWTYPE;
146
147 CURSOR c_igs_pe_ucas(cp_n_person_id igs_pe_person.person_id%TYPE) IS
148 SELECT
149 api_person_id
150 FROM
151 igs_pe_alt_pers_id
152 WHERE
153 sysdate BETWEEN start_dt AND NVL(end_dt, sysdate)
154 AND person_id_type = 'UCASID'
155 AND pe_person_id = cp_n_person_id ;
156
157 rec_c_igs_pe_ucas c_igs_pe_ucas%ROWTYPE;
158
159 -- End definitions for UK Bug 2462198 by rrengara on 24-JUL-2002
160
161
162 CURSOR c_acaiv_aa (
163 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
164 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE,
165 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
166 cp_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
167 cp_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE,
168 cp_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE,
169 cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE,
170 cp_unit_set_cd IGS_AD_PS_APPL_INST.unit_set_cd%TYPE,
171 cp_us_version_number IGS_AD_PS_APPL_INST.us_version_number%TYPE,
172 cp_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE,
173 cp_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE) IS
174 SELECT aa.adm_appl_status
175 FROM IGS_AD_PS_APPL_INST acaiv,
176 IGS_AD_APPL aa
177 WHERE acaiv.person_id = cp_person_id AND
178 acaiv.nominated_course_cd = cp_course_cd AND
179 (acaiv.admission_appl_number <> cp_admission_appl_number OR
180 acaiv.sequence_number <> cp_acai_sequence_number) AND
181 NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
182 NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
183 NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
184 NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
185 NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
186 (NVL(acaiv.adm_cal_type,aa.adm_cal_type) <> cp_adm_cal_type OR
187 NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) <> cp_adm_ci_sequence_number) AND
188 acaiv.person_id = aa.person_id AND
189 acaiv.admission_appl_number = aa.admission_appl_number;
190
191 CURSOR c_aal (
192 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
193 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
194 IS
195 SELECT 'x'
196 FROM IGS_AD_APPL_LTR aal
197 WHERE aal.person_id = cp_person_id AND
198 aal.admission_appl_number = cp_admission_appl_number;
199 CURSOR c_aa (
200 cp_person_id IGS_AD_APPL.person_id%TYPE,
201 cp_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE) IS
202 SELECT aas.s_adm_appl_status
203 FROM IGS_AD_APPL aa,
204 IGS_AD_APPL_STAT aas
205 WHERE aa.person_id = cp_person_id AND
206 aa.admission_appl_number = cp_admission_appl_number AND
207 aas.adm_appl_status = aa.adm_appl_status;
208 BEGIN
209 p_message_name := NULL;
210 p_return_type := NULL;
211 -- Validate the system admission application status.
212 OPEN c_aa(
213 p_person_id,
214 p_admission_appl_number);
215 FETCH c_aa INTO v_s_adm_appl_status;
216 IF(c_aa%FOUND AND
217 v_s_adm_appl_status IN (cst_completed,cst_withdrawn)) THEN
218 CLOSE c_aa;
219 p_message_name := 'IGS_AD_NOTINS_ADMPRG_APPL';
220 p_return_type := cst_error;
221 RETURN FALSE;
222 END IF;
223 CLOSE c_aa;
224 IF p_validate_aa_only = FALSE THEN
225 -- Validate preferences.
226 IF(IGS_AD_VAL_ACA.admp_val_pref_limit (
227 p_person_id,
228 p_admission_appl_number,
229 p_nominated_course_cd,
230 p_acai_sequence_number,
231 p_s_admission_process_type,
232 p_pref_limit,
233 v_message_name) = FALSE) THEN
234 p_message_name := v_message_name;
235 p_return_type := cst_error;
236 RETURN FALSE;
237 END IF;
238
239 -- Added the following logics for UK Bug 2462198
240 -- If the UK profile is switched on and the ucas id is present
241 -- for the applicant then consider the choice number also
242 -- for duplicate application instance check
243 -- otherwise do not consider the choice number in duplicate check
244 -- by rrengara on 24-JUL-2002
245 -- Also added alt_appl_id for duplicate check when UK profile
246 -- is switched on for bug 2664410 by knag
247
248 OPEN c_igs_pe_ucas(p_person_id);
249 FETCH c_igs_pe_ucas INTO rec_c_igs_pe_ucas;
250
251 IF FND_PROFILE.VALUE('OSS_COUNTRY_CODE') = 'GB' AND c_igs_pe_ucas%FOUND THEN
252 OPEN c_aav_uk(p_person_id, p_admission_appl_number);
253 FETCH c_aav_uk INTO c_aav_uk_rec;
254 CLOSE c_aav_uk;
255 FOR v_acaiv_rec_uk IN c_acaiv_uk(
256 p_person_id,
257 p_course_cd,
258 p_admission_appl_number,
259 p_acai_sequence_number,
260 p_location_cd,
261 p_attendance_mode,
262 p_attendance_type,
263 p_unit_set_cd,
264 p_us_version_number,
265 p_adm_cal_type,
266 p_adm_ci_sequence_number,
267 c_aav_uk_rec.choice_number,
268 c_aav_uk_rec.alt_appl_id) LOOP
269 p_message_name := 'IGS_AD_ANOTHER_ADMAPPL_EXISTS';
270 p_return_type := cst_error;
271 v_return := FALSE;
272 EXIT;
273 END LOOP;
274 ELSE
275 -- Validate for a matching admission course application instance for the
276 -- IGS_PE_PERSON in the same admission period.
277 FOR v_acaiv_rec IN c_acaiv(
278 p_person_id,
279 p_course_cd,
280 p_admission_appl_number,
281 p_acai_sequence_number,
282 p_location_cd,
283 p_attendance_mode,
284 p_attendance_type,
285 p_unit_set_cd,
286 p_us_version_number,
287 p_adm_cal_type,
288 p_adm_ci_sequence_number) LOOP
289 p_message_name := 'IGS_AD_ANOTHER_ADMAPPL_EXISTS';
290 p_return_type := cst_error;
291 v_return := FALSE;
292 EXIT;
293 END LOOP;
294 END IF;
295
296 CLOSE c_igs_pe_ucas;
297
298 IF(v_return = TRUE) THEN
299 -- Validate for a matching admission course application instance for the
300 -- IGS_PE_PERSON
301 -- in a different admission period.
302 FOR v_acaiv_aa_rec IN c_acaiv_aa(
303 p_person_id,
304 p_course_cd,
305 p_admission_appl_number,
306 p_acai_sequence_number,
307 p_location_cd,
308 p_attendance_mode,
309 p_attendance_type,
310 p_unit_set_cd,
311 p_us_version_number,
312 p_adm_cal_type,
313 p_adm_ci_sequence_number) LOOP
314 IF(NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(
315 v_acaiv_aa_rec.adm_appl_status),'NULL')
316 NOT IN (cst_completed, cst_withdrawn)) THEN
317 p_message_name := 'IGS_AD_ADMPRG_MATCH_PRG';
318 p_return_type := cst_warn;
319 v_return := FALSE;
320 EXIT;
321 END IF;
322 END LOOP;
323 END IF;
324 END IF;
325 IF(v_return = TRUE) THEN
326 -- Validate if correspondence has been sent.
327 FOR v_aal_rec IN c_aal(
328 p_person_id,
329 p_admission_appl_number) LOOP
330 p_message_name := 'IGS_AD_COR_SET_ADMAPPL';
331 p_return_type := cst_warn;
332 v_return := FALSE;
333 EXIT;
334 END LOOP;
335 END IF;
336 IF(v_return = TRUE) THEN
337 RETURN TRUE;
338 ELSE
339 RETURN FALSE;
340 END IF;
341 END;
342 EXCEPTION
343 WHEN OTHERS THEN
344 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
345 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_insert');
346 IGS_GE_MSG_STACK.ADD;
347 APP_EXCEPTION.RAISE_EXCEPTION;
348 END admp_val_acai_insert;
349 --
350 -- Validate update of an IGS_AD_PS_APPL_INST record.
351 FUNCTION admp_val_acai_update(
352 p_adm_appl_status IN VARCHAR2 ,
353 p_person_id IN NUMBER ,
354 p_admission_appl_number IN NUMBER ,
355 p_nominated_course_cd IN VARCHAR2 ,
356 p_acai_sequence_number IN NUMBER ,
357 p_message_name OUT NOCOPY VARCHAR2 ,
358 p_update_non_enrol_detail_ind OUT NOCOPY VARCHAR2 )
359 RETURN BOOLEAN AS
360 BEGIN --admp_val_acai_update
361 --This module validates update of an IGS_AD_PS_APPL_INST record.
362 DECLARE
363 v_s_appl_inst_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
364 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
365 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
366 CURSOR c_sca (
367 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
368 cp_admission_appl_number
369 IGS_EN_STDNT_PS_ATT.adm_admission_appl_number%TYPE,
370 cp_nominated_course_cd IGS_EN_STDNT_PS_ATT.adm_nominated_course_cd%TYPE,
371 cp_acai_sequence_number IGS_EN_STDNT_PS_ATT.adm_sequence_number%TYPE) IS
372 SELECT sca.person_id
373 FROM IGS_EN_STDNT_PS_ATT sca
374 WHERE sca.person_id = cp_person_id AND
375 sca.adm_admission_appl_number IS NOT NULL AND
376 sca.adm_admission_appl_number = cp_admission_appl_number AND
377 sca.adm_nominated_course_cd IS NOT NULL AND
378 sca.adm_nominated_course_cd = cp_nominated_course_cd AND
379 sca.adm_sequence_number IS NOT NULL AND
380 sca.adm_sequence_number = cp_acai_sequence_number AND
381 sca.student_confirmed_ind = 'Y';
382
383 CURSOR c_ais IS --arvsrini igsm
384 SELECT appl_inst_status
385 FROM IGS_AD_PS_APPL_INST
386 WHERE person_id= p_person_id AND
387 admission_appl_number= p_admission_appl_number AND
388 nominated_course_cd= p_nominated_course_cd AND
389 sequence_number =p_acai_sequence_number;
390
391 BEGIN
392 --Set the default message number
393 p_message_name := NULL;
394 -- Set the default update status indicator.
395 p_update_non_enrol_detail_ind := 'N';
396 --Validate the system admission application status
397
398 OPEN c_ais; --arvsrini igsm
399 FETCH c_ais INTO v_s_appl_inst_status;
400 CLOSE c_ais;
401
402 IF v_s_appl_inst_status IS NOT NULL THEN
403 v_s_appl_inst_status := NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(
404 v_s_appl_inst_status), 'NULL');
405 END IF;
406
407 IF v_s_appl_inst_status = cst_withdrawn THEN
408 p_message_name := 'IGS_AD_APPL_INST_WITHD';
409 p_update_non_enrol_detail_ind := 'N';
410 RETURN FALSE;
411 ELSIF igs_ad_gen_002.Is_App_Inst_Complete(p_person_id, p_admission_appl_number, p_nominated_course_cd, p_acai_sequence_number) = 'Y' THEN
412 p_message_name := 'IGS_AD_APPL_INST_COMPL';
413 p_update_non_enrol_detail_ind := 'N';
414 RETURN FALSE;
415 END IF; --arvsrini igsm
416
417
418
419
420
421 -- Validate if the admission application has been pre-enrolled and confirmed.
422 IF p_person_id IS NOT NULL AND
423 p_admission_appl_number IS NOT NULL AND
424 p_nominated_course_cd IS NOT NULL AND
425 p_acai_sequence_number IS NOT NULL THEN
426 OPEN c_sca (
427 p_person_id,
428 p_admission_appl_number,
429 p_nominated_course_cd,
430 p_acai_sequence_number);
431 FETCH c_sca INTO v_person_id;
432 IF c_sca%FOUND = TRUE THEN
433 CLOSE c_sca;
434 p_message_name := 'IGS_AD_CANNOT_UPD_ENR_INFO';
435 p_update_non_enrol_detail_ind := 'Y';
436 RETURN FALSE;
437 END IF;
438 CLOSE c_sca;
439 END IF;
440 RETURN TRUE;
441 END;
442 EXCEPTION
443 WHEN OTHERS THEN
444 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
445 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_update');
446 IGS_GE_MSG_STACK.ADD;
447 APP_EXCEPTION.RAISE_EXCEPTION;
448 END admp_val_acai_update;
449 --
450 -- Validate delete of an IGS_AD_PS_APPL_INST record.
451 FUNCTION admp_val_acai_delete(
452 p_person_id IN NUMBER ,
453 p_admission_appl_number IN NUMBER ,
454 p_adm_outcome_status IN VARCHAR2 ,
455 p_message_name OUT NOCOPY VARCHAR2 ,
456 p_return_type OUT NOCOPY VARCHAR2 )
457 RETURN BOOLEAN AS
458 BEGIN -- admp_val_acai_delete
459 -- Validate delete of an IGS_AD_PS_APPL_INST record
460 DECLARE
461 CURSOR c_aal IS
462 SELECT 'x'
463 FROM IGS_AD_APPL_LTR
464 WHERE person_id = p_person_id AND
465 admission_appl_number = p_admission_appl_number;
466 CURSOR c_aas IS
467 SELECT 'x'
468 FROM IGS_AD_APPL aa,
469 IGS_AD_APPL_STAT aas
470 WHERE aa.person_id = p_person_id AND
471 aa.admission_appl_number = p_admission_appl_number AND
472 aas.adm_appl_status = aa.adm_appl_status AND
473 aas.s_adm_appl_status = 'WITHDRAWN';
474 v_aal_exists VARCHAR2(1);
475 v_aas_exists VARCHAR2(1);
476 cst_error CONSTANT VARCHAR2(1) := 'E';
477 cst_warn CONSTANT VARCHAR2(1) := 'W';
478 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE := IGS_AD_GEN_008.ADMP_GET_SAOS(p_adm_outcome_status);
479 BEGIN
480 -- Set the default message number
481 p_message_name := NULL;
482 -- Validate the system admission application status.
483 OPEN c_aas;
484 FETCH c_aas INTO v_aas_exists;
485 IF c_aas%FOUND THEN
486 CLOSE c_aas;
487 p_message_name := 'IGS_AD_NOTDEL_ADMPRG_APPL';
488 p_return_type := cst_error;
489 RETURN FALSE;
490 END IF;
491 CLOSE c_aas;
492 -- Validate the system admission outcome status
493 IF NVL(v_s_adm_outcome_status, 'NULL') <> 'PENDING' THEN
494 p_message_name := 'IGS_AD_CANDEL_ADMPRG_APPL';
495 p_return_type := cst_error;
496 RETURN FALSE;
497 END IF;
498 -- Validate if correspondence has been sent
499 OPEN c_aal;
500 FETCH c_aal INTO v_aal_exists;
501 IF c_aal%FOUND THEN
502 CLOSE c_aal;
503 p_message_name := 'IGS_AD_CANDEL_ADMPRG_COR_EXIS';
504 p_return_type := cst_error;
505 RETURN FALSE;
506 END IF;
507 CLOSE c_aal;
508 RETURN TRUE;
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF c_aas%ISOPEN THEN
512 CLOSE c_aas;
513 END IF;
514 IF c_aal%ISOPEN THEN
515 CLOSE c_aal;
516 END IF;
517 RAISE;
518 END;
519 EXCEPTION
520 WHEN OTHERS THEN
521 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
522 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_delete');
523 IGS_GE_MSG_STACK.ADD;
524 APP_EXCEPTION.RAISE_EXCEPTION;
525 END admp_val_acai_delete;
526 --
527 -- Validate change of preferences.
528 FUNCTION admp_val_chg_of_pref(
529 p_adm_cal_type IN VARCHAR2 ,
530 p_adm_ci_sequence_number IN NUMBER ,
531 p_admission_cat IN VARCHAR2 ,
532 p_s_admission_process_type IN VARCHAR2 ,
533 p_course_cd IN VARCHAR2 ,
534 p_crv_version_number IN NUMBER ,
535 p_acad_cal_type IN VARCHAR2 ,
536 p_location_cd IN VARCHAR2 ,
537 p_attendance_mode IN VARCHAR2 ,
538 p_attendance_type IN VARCHAR2 ,
539 p_message_name OUT NOCOPY VARCHAR2 )
540 RETURN BOOLEAN AS
541 BEGIN -- admp_val_chg_of_pref
542 -- Description: Validate change of preferences
543 DECLARE
544 v_chng_of_pref_dt_alias
545 IGS_AD_CAL_CONF.adm_appl_chng_of_pref_dt_alias%TYPE;
546 v_chng_of_pref_dt DATE;
547 CURSOR c_sacco IS
548 SELECT sacco.adm_appl_chng_of_pref_dt_alias
549 FROM IGS_AD_CAL_CONF sacco
550 WHERE sacco.s_control_num = 1;
551 BEGIN
552 p_message_name := NULL;
553 OPEN c_sacco;
554 FETCH c_sacco INTO v_chng_of_pref_dt_alias;
555 CLOSE c_sacco;
556 IF v_chng_of_pref_dt_alias IS NOT NULL THEN
557 v_chng_of_pref_dt := IGS_AD_GEN_003.ADMP_GET_ADM_PERD_DT (
558 v_chng_of_pref_dt_alias,
559 p_adm_cal_type,
560 p_adm_ci_sequence_number,
561 p_admission_cat,
562 p_s_admission_process_type,
563 p_course_cd,
564 p_crv_version_number,
565 p_acad_cal_type,
566 p_location_cd,
567 p_attendance_mode,
568 p_attendance_type);
569 IF v_chng_of_pref_dt IS NOT NULL AND
570 TRUNC(v_chng_of_pref_dt) < TRUNC(SYSDATE) THEN
571 p_message_name := 'IGS_AD_PREFS_NOT_CHANGED';
572 RETURN FALSE;
573 END IF;
574 END IF;
575 RETURN TRUE;
576 EXCEPTION
577 WHEN OTHERS THEN
578 IF (c_sacco%ISOPEN) THEN
579 CLOSE c_sacco;
580 END IF;
581 RAISE;
582 END;
583 EXCEPTION
584 WHEN OTHERS THEN
585 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
586 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_chg_of_pref');
587 IGS_GE_MSG_STACK.ADD;
588 APP_EXCEPTION.RAISE_EXCEPTION;
589 END admp_val_chg_of_pref;
590 --
591
592 -- Validate the course code of the admission application.
593 FUNCTION admp_val_acai_course(
594 p_course_cd IN VARCHAR2 ,
595 p_version_number IN NUMBER ,
596 p_admission_cat IN VARCHAR2 ,
597 p_s_admission_process_type IN VARCHAR2 ,
598 p_acad_cal_type IN VARCHAR2 ,
599 p_acad_ci_sequence_number IN NUMBER ,
600 p_adm_cal_type IN VARCHAR2 ,
601 p_adm_ci_sequence_number IN NUMBER ,
602 p_appl_dt IN DATE ,
603 p_late_appl_allowed IN VARCHAR2 ,
604 p_offer_ind IN VARCHAR2,
605 p_crv_version_number OUT NOCOPY NUMBER ,
606 p_message_name OUT NOCOPY VARCHAR2 ,
607 p_return_type OUT NOCOPY VARCHAR2 )
608 RETURN BOOLEAN AS
609 BEGIN -- admp_val_acai_course
610 -- Validate the course code of the admission application.
611 -- Validations are performed against the course version
612 -- and against the course offering option.
613 DECLARE
614 CURSOR c_crv IS
615 SELECT version_number
616 FROM IGS_PS_VER
617 WHERE course_cd = p_course_cd;
618 v_crv_rec c_crv%ROWTYPE;
619 cst_error CONSTANT VARCHAR2(1) := 'E';
620 cst_warn CONSTANT VARCHAR2(1) := 'W';
621 v_record_found BOOLEAN := FALSE;
622 v_exit_loop BOOLEAN := FALSE;
623 v_message_name VARCHAR2(30) := NULL;
624 v_version_number VARCHAR2(3);
625 v_return_type VARCHAR2(1);
626 v_late_ind VARCHAR2(1) := 'N';
627 BEGIN
628 -- Set the default message number
629 p_message_name := NULL;
630 -- Validate the input parameters
631 IF p_admission_cat IS NULL OR
632 p_s_admission_process_type IS NULL OR
633 p_acad_cal_type IS NULL OR
634 p_acad_ci_sequence_number IS NULL OR
635 p_adm_cal_type IS NULL OR
636 p_adm_ci_sequence_number IS NULL THEN
637 p_message_name := 'IGS_AD_PRG_CANVALID_ADMCAT';
638 p_return_type := cst_error;
639 RETURN FALSE;
640 END IF;
641 FOR v_crv_rec IN c_crv LOOP
642 -- Restrict the IGS_PS_VER records to match
643 -- on input parameters.
644 IF p_version_number IS NULL OR
645 p_version_number = v_crv_rec.version_number THEN
646 v_message_name := NULL;
647 v_return_type := NULL;
648 v_record_found := TRUE;
649 -- Validate the course version data
650 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
651 p_course_cd,
652 v_crv_rec.version_number,
653 p_s_admission_process_type,
654 p_offer_ind,
655 v_message_name) = FALSE THEN
656 v_return_type := cst_error;
657 GOTO continue;
658 END IF;
659 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat(
660 p_course_cd,
661 v_crv_rec.version_number,
662 p_acad_cal_type,
663 NULL,
664 NULL,
665 NULL,
666 p_admission_cat,
667 v_message_name) = FALSE THEN
668 v_return_type := cst_error;
669 GOTO continue;
670 END IF;
671 IF IGS_AD_VAL_ACAI.admp_val_acai_coo(
672 p_course_cd,
673 v_crv_rec.version_number,
674 NULL,
675 NULL,
676 NULL,
677 p_acad_cal_type,
678 p_acad_ci_sequence_number,
679 p_adm_cal_type,
680 p_adm_ci_sequence_number,
681 p_admission_cat,
682 p_s_admission_process_type,
683 p_offer_ind,
684 p_appl_dt,
685 p_late_appl_allowed,
686 'N', -- Deferred application.
687 v_message_name,
688 v_return_type,
689 v_late_ind) = FALSE THEN
690 IF v_return_type = cst_error THEN
691 GOTO continue;
692 END IF;
693 END IF;
694 -- If this point is reached, then all error validations have been successful,
695 -- however, there may be a warning
696 p_crv_version_number := v_crv_rec.version_number;
697 p_message_name := v_message_name;
698 p_return_type := v_return_type;
699 v_exit_loop := TRUE;
700 EXIT;
701 END IF;
702 << continue >>
703 NULL;
704 END LOOP; -- v_crv_rec IN c_crv
705 IF v_exit_loop THEN
706 IF v_message_name IS NULL THEN
707 RETURN TRUE;
708 ELSE
709 RETURN FALSE;
710 END IF;
711 END IF;
712 IF NOT v_record_found THEN
713 p_return_type := NULL;
714 RETURN TRUE;
715 END IF;
716 IF p_version_number IS NOT NULL THEN
717 -- Only one course version was processed.
718 p_message_name := v_message_name;
719 p_return_type := v_return_type;
720 ELSE
721 p_message_name := 'IGS_AD_PRG_NOTVALID_ADMAPPL';
722 p_return_type := cst_error;
723 END IF;
724 -- Return the default value
725 RETURN FALSE;
726 END;
727 EXCEPTION
728 WHEN OTHERS THEN
729 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
730 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_course');
731 IGS_GE_MSG_STACK.ADD;
732 APP_EXCEPTION.RAISE_EXCEPTION;
733 END admp_val_acai_course;
734 --
735 -- Perform encumbrance check for admission_course_appl_instance.course_cd
736 FUNCTION admp_val_acai_encmb(
737 p_person_id IN NUMBER ,
738 p_course_cd IN VARCHAR2 ,
739 p_adm_cal_type IN VARCHAR2 ,
740 p_adm_ci_sequence_number IN NUMBER ,
741 p_course_encmb_chk_ind IN VARCHAR2 ,
742 p_offer_ind IN VARCHAR2 ,
743 p_message_name OUT NOCOPY VARCHAR2 ,
744 p_return_type OUT NOCOPY VARCHAR2 )
745 RETURN BOOLEAN AS
746 BEGIN -- admp_val_acai_encmb
747 -- Perform encumbrance checks for the admission_course_appl_instance.course_cd.
748 DECLARE
749 v_message_name VARCHAR2(30) := NULL;
750 v_encmb_check_dt DATE;
751 cst_error CONSTANT VARCHAR2(1) := 'E';
752 cst_warn CONSTANT VARCHAR2(1) := 'W';
753 BEGIN
754 -- Set the default message number
755 p_message_name := NULL;
756 IF p_course_encmb_chk_ind = 'Y' THEN
757 -- Get the encumbrance checking date.
758 v_encmb_check_dt := NVL(IGS_AD_GEN_006.ADMP_GET_ENCMB_DT(
759 p_adm_cal_type,
760 p_adm_ci_sequence_number),SYSDATE); --arvsrini igsm
761 /* IF v_encmb_check_dt IS NULL THEN
762
763 IF p_offer_ind = 'Y' THEN
764 p_message_name := 'IGS_AD_ENCUMB_CANNOT_PERFORM';
765 p_return_type := cst_error;
766 ELSE
767 p_message_name := 'IGS_AD_ENCUMB_CHK_NOT_PERFORM';
768 p_return_type := cst_warn;
769 END IF;
770 RETURN FALSE;
771 END IF;
772 */
773 -- Validate for exclusion or suspension from the course
774 IF IGS_EN_VAL_ENCMB.enrp_val_excld_crs(
775 p_person_id,
776 p_course_cd,
777 v_encmb_check_dt,
778 v_message_name) = FALSE THEN
779 IF p_offer_ind = 'Y' THEN
780 p_message_name := 'IGS_AD_PRSN_ENCUMB_EXCLUDING';
781 p_return_type := cst_error;
782 ELSE
783 p_message_name := 'IGS_AD_PRSN_ENCUMBRAN_SUSPEND';
784 p_return_type := cst_warn;
785 END IF;
786 RETURN FALSE;
787 END IF;
788 END IF;
789 -- Return the default value
790 RETURN TRUE;
791 END;
792 EXCEPTION
793 WHEN OTHERS THEN
794 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
795 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_encmb');
796 IGS_GE_MSG_STACK.ADD;
797 APP_EXCEPTION.RAISE_EXCEPTION;
798 END admp_val_acai_encmb;
799 --
800 -- Validate course appl process type against the student course attempt.
801 FUNCTION admp_val_aca_sca(
802 p_person_id IN NUMBER ,
803 p_course_cd IN VARCHAR2 ,
804 p_appl_dt IN DATE ,
805 p_admission_cat IN VARCHAR2 ,
806 p_s_admission_process_type IN VARCHAR2 ,
807 p_fee_cat IN VARCHAR2 ,
808 p_correspondence_cat IN VARCHAR2 ,
809 p_enrolment_cat IN VARCHAR2 ,
810 p_offer_ind IN VARCHAR2 ,
811 p_message_name OUT NOCOPY VARCHAR2 ,
812 p_return_type OUT NOCOPY VARCHAR2 )
813 RETURN BOOLEAN AS
814 BEGIN -- admp_val_aca_process
815 -- Validate that the nominated course admission application is
816 -- valid for the admission application system process type and
817 -- any existing course attempts the student may have.
818 DECLARE
819 CURSOR c_sca IS
820 SELECT course_attempt_status,
821 fee_cat,
822 correspondence_cat,
823 discontinued_dt
824 FROM IGS_EN_STDNT_PS_ATT
825 WHERE person_id = p_person_id AND
826 course_cd = p_course_cd;
827 CURSOR c_fcm (
828 cp_fee_cat IGS_EN_STDNT_PS_ATT.fee_cat%TYPE) IS
829 SELECT 'x'
830 FROM IGS_FI_FEE_CAT_MAP
831 WHERE admission_cat = p_admission_cat AND
832 fee_cat = cp_fee_cat;
833
834 CURSOR c_ccm (
835 cp_correspondence_cat IGS_EN_STDNT_PS_ATT.correspondence_cat%TYPE) IS
836 SELECT 'x'
837 FROM IGS_CO_CAT_MAP
838 WHERE admission_cat = p_admission_cat AND
839 correspondence_cat = cp_correspondence_cat;
840
841 CURSOR c_scae IS
842 SELECT enrolment_cat
843 FROM IGS_AS_SC_ATMPT_ENR scae,
844 IGS_CA_INST ci
845 WHERE person_id = p_person_id AND
846 course_cd = p_course_cd AND
847 scae.cal_type = ci.cal_type AND
848 scae.ci_sequence_number = ci.sequence_number
849 ORDER BY ci.start_dt DESC;
850 CURSOR c_ecm (
851 cp_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE) IS
852 SELECT 'x'
853 FROM IGS_EN_CAT_MAPPING
854 WHERE admission_cat = p_admission_cat AND
855 enrolment_cat = cp_enrolment_cat ;
856 cst_error CONSTANT VARCHAR2(1) := 'E';
857 cst_warn CONSTANT VARCHAR2(1) := 'W';
858 v_sca_rec c_sca%ROWTYPE;
859 v_scae_rec c_scae%ROWTYPE;
860 v_fcm_exists VARCHAR2(1);
861 v_ccm_exists VARCHAR2(1);
862 v_ecm_exists VARCHAR2(1);
863 v_message_name VARCHAR2(30) := NULL;
864 BEGIN
865 p_message_name := NULL;
866 OPEN c_sca;
867 FETCH c_sca INTO v_sca_rec;
868 IF (c_sca%NOTFOUND) THEN
869 CLOSE c_sca;
870 IF p_s_admission_process_type = 'RE-ADMIT' THEN
871 p_return_type := cst_error;
872 p_message_name := 'IGS_AD_APPL_NOT_EXISTING_STUD';
873 RETURN FALSE;
874 END IF;
875 RETURN TRUE;
876 END IF;
877 CLOSE c_sca;
878 -- Validate re-admission
879 IF p_s_admission_process_type <> 'RE-ADMIT' THEN
880 -- Validate against all other system admission process types
881 IF v_sca_rec.course_attempt_status NOT IN ('DELETED', 'UNCONFIRM') THEN
882 p_return_type := cst_error;
883 p_message_name := 'IGS_AD_APPL_EXISTING_STUDPRG';
884 RETURN FALSE;
885 END IF;
886 RETURN TRUE;
887 END IF;
888 IF (v_sca_rec.course_attempt_status <> 'DISCONTIN') THEN
889 p_message_name := 'IGS_AD_APPL_CANNOT_READMITTED';
890 p_return_type := cst_error;
891 RETURN FALSE;
892 END IF;
893 -- Validate against any existing student course transfer details
894 IF IGS_AD_VAL_ACA.enrp_val_sca_trnsfr(
895 p_person_id,
896 p_course_cd,
897 NULL,
898 'A',
899 v_message_name) = FALSE THEN
900 p_return_type := cst_error;
901 p_message_name := v_message_name;
902 RETURN FALSE;
903 END IF;
904 -- Only validate the student course attempt fee category when
905 -- none has been specified for the admission course application instance.
906 IF p_fee_cat IS NULL AND
907 v_sca_rec.fee_cat IS NOT NULL THEN
908 -- Validate the Student course Attempt Fee Category Mapping.
909 OPEN c_fcm (
910 v_sca_rec.fee_cat);
911 FETCH c_fcm INTO v_fcm_exists;
912 IF (c_fcm%NOTFOUND) THEN
913 CLOSE c_fcm;
914 IF p_offer_ind = 'Y' THEN
915 p_message_name := 'IGS_AD_FEECAT_PRGAPPL';
916 p_return_type := cst_error;
917 ELSE
918 p_message_name := 'IGS_AD_FEECAT_PRG_APPL_READM';
919 p_return_type := cst_warn;
920 END IF;
921 RETURN FALSE;
922 END IF;
923 CLOSE c_fcm;
924 END IF;
925 -- Only validate the student course attempt correspondence category
926 -- when none has been specified for the admission course application instance.
927 IF p_correspondence_cat IS NULL AND
928 v_sca_rec.correspondence_cat IS NOT NULL THEN
929 -- Validate the Student course Attempt Correspondence Category Mapping.
930 OPEN c_ccm (
931 v_sca_rec.correspondence_cat);
932 FETCH c_ccm INTO v_ccm_exists;
933 IF (c_ccm%NOTFOUND) THEN
934 CLOSE c_ccm;
935 IF p_offer_ind = 'Y' THEN
936 p_message_name := 'IGS_AD_CORCAT_PRG_APPL';
937 p_return_type := cst_error;
938 ELSE
939 p_message_name := 'IGS_AD_CORCAT_PRGAPPLICANT';
940 p_return_type := cst_warn;
941 END IF;
942 RETURN FALSE;
943 END IF;
944 CLOSE c_ccm;
945 END IF;
946 -- Only validate the student course attempt enrolment category
947 -- when none has been specified for the admission course application instance.
948 IF p_enrolment_cat IS NULL THEN
949 -- Validate the Student course Attempt Enrolment Category Mapping.
950 OPEN c_scae;
951 FETCH c_scae INTO v_scae_rec;
952 IF (c_scae%FOUND) THEN
953 CLOSE c_scae;
954 OPEN c_ecm (
955 v_scae_rec.enrolment_cat);
956 FETCH c_ecm INTO v_ecm_exists;
957 IF (c_ecm%NOTFOUND) THEN
958 CLOSE c_ecm;
959 IF p_offer_ind = 'Y' THEN
960 p_message_name := 'IGS_AD_ENRCAT_PRG_APPL';
961 p_return_type := cst_error;
962 ELSE
963 p_message_name := 'IGS_AD_ENRCAT_PRGAPPLICANT';
964 p_return_type := cst_warn;
965 END IF;
966 RETURN FALSE;
967 END IF;
968 CLOSE c_ecm;
969 ELSE
970 CLOSE c_scae;
971 END IF;
972 -- Validate the discontinued date
973 IF p_appl_dt IS NOT NULL AND
974 v_sca_rec.discontinued_dt IS NOT NULL AND
975 MONTHS_BETWEEN(p_appl_dt, v_sca_rec.discontinued_dt) <= 12 THEN
976 p_message_name := 'IGS_AD_APPL_ENROLLED_12MONTHS';
977 p_return_type := cst_warn;
978 RETURN FALSE;
979 END IF;
980 END IF;
981 RETURN TRUE;
982 EXCEPTION
983 WHEN OTHERS THEN
984 IF c_sca%ISOPEN THEN
985 CLOSE c_sca;
986 END IF;
987 IF c_fcm%ISOPEN THEN
988 CLOSE c_fcm;
989 END IF;
990 IF c_ccm%ISOPEN THEN
991 CLOSE c_ccm;
992 END IF;
993 IF c_scae%ISOPEN THEN
994 CLOSE c_scae;
995 END IF;
996 IF c_ecm%ISOPEN THEN
997 CLOSE c_ecm;
998 END IF;
999 RAISE;
1000 END;
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1004 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_aca_sca');
1005 IGS_GE_MSG_STACK.ADD;
1006 APP_EXCEPTION.RAISE_EXCEPTION;
1007 END admp_val_aca_sca;
1008 --
1009 -- Validate the adm course application instance course offering pattern.
1010 FUNCTION admp_val_acai_cop(
1011 p_course_cd IN VARCHAR2 ,
1012 p_version_number IN NUMBER ,
1013 p_location_cd IN VARCHAR2 ,
1014 p_attendance_mode IN VARCHAR2 ,
1015 p_attendance_type IN VARCHAR2 ,
1016 p_acad_cal_type IN VARCHAR2 ,
1017 p_acad_ci_sequence_number IN NUMBER ,
1018 p_adm_cal_type IN VARCHAR2 ,
1019 p_adm_ci_sequence_number IN NUMBER ,
1020 p_admission_cat IN VARCHAR2 ,
1021 p_s_admission_process_type IN VARCHAR2 ,
1022 p_offer_ind IN VARCHAR2 ,
1023 p_appl_dt IN DATE ,
1024 p_late_appl_allowed IN VARCHAR2 ,
1025 p_deferred_appl IN VARCHAR2 ,
1026 p_message_name OUT NOCOPY VARCHAR2 ,
1027 p_return_type OUT NOCOPY VARCHAR2 ,
1028 p_late_ind OUT NOCOPY VARCHAR2 )
1029 RETURN BOOLEAN AS
1030 BEGIN -- admp_val_acai_cop
1031 -- Validate the admission course application instance course offering pattern
1032 DECLARE
1033 cst_error CONSTANT VARCHAR2(1) := 'E';
1034 cst_warn CONSTANT VARCHAR2(1) := 'W';
1035 v_ret_val BOOLEAN := TRUE;
1036 v_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE;
1037 v_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE;
1038 v_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE;
1039 v_message_name VARCHAR2(30);
1040 v_late_ind VARCHAR2(1) := 'N';
1041 v_return_type VARCHAR2(1);
1042 BEGIN
1043 p_message_name := NULL;
1044 p_late_ind := 'N';
1045 -- Validate the admission process category input parameters
1046 -- All must be set for validation to occur
1047 IF (p_acad_cal_type IS NULL OR
1048 p_acad_ci_sequence_number IS NULL OR
1049 p_adm_cal_type IS NULL OR
1050 p_adm_ci_sequence_number IS NULL OR
1051 p_admission_cat IS NULL OR
1052 p_s_admission_process_type IS NULL) THEN
1053 p_message_name := 'IGS_AD_PRGOFOP_CANNOT_VALID' ;
1054 p_return_type := cst_error;
1055 RETURN FALSE;
1056 END IF;
1057 -- course offering pattern is offered
1058 IF p_offer_ind = 'Y' THEN
1059 -- All components of the course offering pattern must be specified.
1060 IF (p_course_cd IS NULL OR
1061 p_version_number IS NULL OR
1062 p_location_cd IS NULL OR
1063 p_attendance_mode IS NULL OR
1064 p_attendance_type IS NULL) THEN
1065 p_message_name := 'IGS_AD_PRGOFOP_NOVALIDATE_COM';
1066 p_return_type := cst_error;
1067 RETURN FALSE;
1068 END IF;
1069 ELSE -- course offering pattern is nominated
1070 -- course component of course offering option must be set
1071 -- (other components may be null).
1072 IF (p_course_cd IS NULL OR
1073 p_version_number IS NULL) THEN
1074 p_message_name := 'IGS_AD_PRGOFOP_CANVLD_PRGCOMP';
1075 p_return_type := cst_error;
1076 RETURN FALSE;
1077 END IF;
1078 END IF;
1079 -- Set the IGS_AD_LOCATION code, attendance mode and attendance type
1080 v_location_cd := p_location_cd;
1081 v_attendance_mode := p_attendance_mode;
1082 v_attendance_type := p_attendance_type;
1083 -- Validate the course offering pattern
1084 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_coo (
1085 p_course_cd,
1086 p_version_number,
1087 v_location_cd,
1088 v_attendance_mode,
1089 v_attendance_type,
1090 p_acad_cal_type,
1091 p_acad_ci_sequence_number,
1092 p_adm_cal_type,
1093 p_adm_ci_sequence_number,
1094 p_admission_cat,
1095 p_s_admission_process_type,
1096 p_offer_ind,
1097 p_appl_dt,
1098 p_late_appl_allowed,
1099 p_deferred_appl,
1100 v_message_name,
1101 v_return_type,
1102 v_late_ind) THEN
1103 p_message_name := v_message_name;
1104 p_return_type := v_return_type;
1105 p_late_ind := v_late_ind;
1106 RETURN FALSE;
1107 END IF;
1108 -- admission course application instance course offering pattern is valid
1109 p_return_type := NULL;
1110 RETURN TRUE;
1111 END;
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1115 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_cop');
1116 IGS_GE_MSG_STACK.ADD;
1117 APP_EXCEPTION.RAISE_EXCEPTION;
1118 END admp_val_acai_cop;
1119 --
1120 -- Validate the adm course application instance course offering option.
1121 FUNCTION admp_val_acai_coo(
1122 p_course_cd IN VARCHAR2 ,
1123 p_version_number IN NUMBER ,
1124 p_location_cd IN VARCHAR2 ,
1125 p_attendance_mode IN VARCHAR2 ,
1126 p_attendance_type IN VARCHAR2 ,
1127 p_acad_cal_type IN VARCHAR2 ,
1128 p_acad_ci_sequence_number IN NUMBER ,
1129 p_adm_cal_type IN VARCHAR2 ,
1130 p_adm_ci_sequence_number IN NUMBER ,
1131 p_admission_cat IN VARCHAR2 ,
1132 p_s_admission_process_type IN VARCHAR2 ,
1133 p_offer_ind IN VARCHAR2 ,
1134 p_appl_dt IN DATE ,
1135 p_late_appl_allowed IN VARCHAR2 ,
1136 p_deferred_appl IN VARCHAR2 ,
1137 p_message_name OUT NOCOPY VARCHAR2 ,
1138 p_return_type OUT NOCOPY VARCHAR2 ,
1139 p_late_ind OUT NOCOPY VARCHAR2 )
1140 RETURN BOOLEAN AS
1141 BEGIN -- admp_val_acai_coo
1142 -- * This module assumes the course code component of the course offering
1143 -- option
1144 -- has already been validated (via a call to admp_val_acai_course).
1145 -- * On nomination of a course offering option a blank value in the IGS_AD_LOCATION
1146 -- code,
1147 -- attendance mode or attendance type component of the course offering option
1148 -- should be interpreted as any valid value.
1149 -- * On offer of a course offering option all components of the course offering
1150 -- option should be set.
1151 DECLARE
1152 CURSOR c_coo IS
1153 SELECT location_cd,
1154 attendance_mode,
1155 attendance_type
1156 FROM IGS_PS_OFR_OPT
1157 WHERE course_cd = p_course_cd AND
1158 version_number = p_version_number AND
1159 cal_type = p_acad_cal_type AND
1160 delete_flag= 'N';
1161 CURSOR c_cop (
1162 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE,
1163 cp_version_number IGS_AD_PS_APPL_INST.crv_version_number%TYPE,
1164 cp_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE,
1165 cp_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE,
1166 cp_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE,
1167 cp_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE,
1168 cp_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE) IS
1169 SELECT 'x'
1170 FROM IGS_PS_OFR_PAT
1171 WHERE course_cd = cp_course_cd AND
1172 version_number = cp_version_number AND
1173 cal_type = cp_acad_cal_type AND
1174 ci_sequence_number = cp_acad_ci_sequence_number AND
1175 location_cd = cp_location_cd AND
1176 attendance_mode = cp_attendance_mode AND
1177 attendance_type = cp_attendance_type AND
1178 offered_ind = 'Y' AND
1179 entry_point_ind = 'Y';
1180 cst_error CONSTANT VARCHAR2(1) := 'E';
1181 cst_warn CONSTANT VARCHAR2(1) := 'W';
1182 v_cop_exists VARCHAR2(1);
1183 v_location_cd IGS_AD_PS_APPL_INST.location_cd%TYPE;
1184 v_attendance_mode IGS_AD_PS_APPL_INST.attendance_mode%TYPE;
1185 v_attendance_type IGS_AD_PS_APPL_INST.attendance_type%TYPE;
1186 v_message_name VARCHAR2(30);
1187 v_record_found BOOLEAN := FALSE;
1188 v_cop_found BOOLEAN := FALSE;
1189 v_ac_match BOOLEAN := FALSE; -- admission process category
1190 v_ap_match BOOLEAN := FALSE; -- admission period
1191 v_ad_match BOOLEAN := FALSE; -- application date
1192 v_coo_match BOOLEAN := FALSE; -- course offering option
1193 v_exit_loop NUMBER(1) := 0;
1194 BEGIN
1195 p_message_name := NULL;
1196 p_late_ind := 'N';
1197 -- Validate input parameters. All must be set for validation to occur
1198 IF p_acad_cal_type IS NULL OR
1199 p_acad_ci_sequence_number IS NULL OR
1200 p_adm_cal_type IS NULL OR
1201 p_adm_ci_sequence_number IS NULL OR
1202 p_admission_cat IS NULL OR
1203 p_s_admission_process_type IS NULL THEN
1204 p_message_name := 'IGS_AD_PRGOFOP_NOVALID_ADMPRC';
1205 p_return_type := cst_error;
1206 RETURN FALSE;
1207 END IF;
1208 -- course componenet of course offering option must be set.
1209 IF p_course_cd IS NULL OR
1210 p_version_number IS NULL THEN
1211 p_message_name := 'IGS_AD_PRGOFOP_NOVALIDATE_PRG';
1212 p_return_type := cst_error;
1213 RETURN FALSE;
1214 END IF;
1215 -- Validate the course offering option data
1216 FOR v_coo_rec IN c_coo LOOP
1217 v_record_found := TRUE;
1218 -- Restrict the course offering options to match on input parameters.
1219 IF (p_location_cd IS NULL OR
1220 p_location_cd = v_coo_rec.location_cd) AND
1221 (p_attendance_mode IS NULL OR
1222 p_attendance_mode = v_coo_rec.attendance_mode) AND
1223 (p_attendance_type IS NULL OR
1224 p_attendance_type = v_coo_rec.attendance_type) THEN
1225 -- course Offering Option Match
1226 v_coo_match := TRUE;
1227 -- Check the existence of a course offering pattern that is both offered
1228 -- and an entry point for the academic period of the admission application.
1229 OPEN c_cop(
1230 p_course_cd,
1231 p_version_number,
1232 p_acad_cal_type,
1233 p_acad_ci_sequence_number,
1234 v_coo_rec.location_cd,
1235 v_coo_rec.attendance_mode,
1236 v_coo_rec.attendance_type);
1237 FETCH c_cop INTO v_cop_exists;
1238 IF c_cop%NOTFOUND THEN
1239 CLOSE c_cop;
1240 ELSE
1241 CLOSE c_cop;
1242 v_cop_found := TRUE;
1243 -- Determine if there is an Admission Category Match
1244 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat (
1245 p_course_cd,
1246 p_version_number,
1247 p_acad_cal_type,
1248 v_coo_rec.location_cd,
1249 v_coo_rec.attendance_mode,
1250 v_coo_rec.attendance_type,
1251 p_admission_cat,
1252 v_message_name) THEN
1253 -- Admission Process Category Match
1254 v_ac_match := TRUE;
1255 -- Determine if the course offering pattern is valid for the admission
1256 -- period
1257 IF IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_admperd (
1258 p_adm_cal_type,
1259 p_adm_ci_sequence_number,
1260 p_admission_cat,
1261 p_s_admission_process_type,
1262 p_course_cd,
1263 p_version_number,
1264 p_acad_cal_type,
1265 v_coo_rec.location_cd,
1266 v_coo_rec.attendance_mode,
1267 v_coo_rec.attendance_type,
1268 v_message_name) THEN
1269 -- The course offering pattern is valid for the admission process
1270 -- category and the admission period
1271 -- Admission Period Match
1272 v_ap_match := TRUE;
1273 -- Determine if the admission course application instance is late.
1274 IF IGS_AD_VAL_ACAI.admp_val_acai_late (
1275 p_appl_dt,
1276 p_course_cd,
1277 p_version_number,
1278 p_acad_cal_type,
1279 v_coo_rec.location_cd,
1280 v_coo_rec.attendance_mode,
1281 v_coo_rec.attendance_type,
1282 p_adm_cal_type,
1283 p_adm_ci_sequence_number,
1284 p_admission_cat,
1285 p_s_admission_process_type,
1286 p_late_appl_allowed,
1287 v_message_name) THEN
1288 -- Application Date Valid
1289 -- The course offering pattern is valid for the admission process
1290 -- category and the admission period and is valid for the
1291 -- application date.
1292 v_ad_match := TRUE;
1293 EXIT;
1294 END IF;
1295 END IF;
1296 END IF;
1297 END IF;
1298 END IF;
1299 END LOOP;
1300 IF v_ad_match THEN
1301 RETURN TRUE;
1302 END IF;
1303 -- No course offering option record found
1304 IF NOT v_coo_match THEN
1305 IF p_offer_ind = 'Y' THEN
1306 p_message_name := 'IGS_AD_NOMINATED_PRG_NOTEXIST';
1307 p_return_type := cst_error;
1308 ELSE
1309 IF p_deferred_appl = 'N' THEN
1310 p_message_name := 'IGS_AD_NOMINATED_PRG_NOTEXIST';
1311 ELSE
1312 p_message_name := 'IGS_AD_POO_DFRD_ADM_PERIOD';
1313 END IF;
1314 p_return_type := cst_warn;
1315 END IF;
1316 RETURN FALSE;
1317 END IF;
1318 -- No course offering pattern record found
1319 IF NOT v_cop_found THEN
1320 IF p_offer_ind = 'Y' THEN
1321 p_message_name := 'IGS_AD_PRGOFOP_OFR_ENRTYPOINT';
1322 p_return_type := cst_error;
1323 ELSE
1324 IF p_deferred_appl = 'N' THEN
1325 p_message_name := 'IGS_AD_NOMINATED_PRG_ENTRYPNT';
1326 ELSE
1327 p_message_name := 'IGS_AD_POO_PRG_OFRING_ADMPRD';
1328 END IF;
1329 p_return_type := cst_warn;
1330 END IF;
1331 RETURN FALSE;
1332 END IF;
1333 -- IF Admission Category Match
1334 IF v_ac_match THEN
1335 IF v_ap_match THEN
1336 -- There must have been no Application Date Match
1337 IF p_offer_ind = 'Y' THEN
1338 p_message_name := 'IGS_AD_APPL_CLOSINGDT_PASSED';
1339 p_return_type := cst_error;
1340 ELSE
1341 IF p_deferred_appl = 'N' THEN
1342 IF p_attendance_type IS NULL OR P_attendance_mode IS NULL THEN
1343 p_message_name := 'IGS_AD_APPL_CLOSINGDT_PASSED';
1344 p_return_type := cst_error;
1345 ELSE
1346 p_message_name := 'IGS_AD_APPLDT_HAS_PASSED';
1347 p_return_type := cst_warn;
1348 END IF;
1349 ELSE
1350 p_message_name := 'IGS_AD_POO_PRG_OFRING_LATEFEE';
1351 p_return_type := cst_warn;
1352 END IF;
1353 END IF;
1354 p_late_ind := 'Y';
1355 RETURN FALSE;
1356
1357 END IF;
1358 -- There must have been no Admission Period match
1359 IF p_offer_ind = 'Y' THEN
1360 p_message_name := 'IGS_AD_OFR_PRG_ENTRYPOINT_ADM';
1361 p_return_type := cst_error;
1362 ELSE
1363 IF p_deferred_appl = 'N' THEN
1364 p_message_name := 'IGS_AD_NOMINATE_PRG_OFR_ENTRY';
1365 ELSE
1366 p_message_name := 'IGS_AD_POO_PRG_OFRING_APPL';
1367 END IF;
1368 p_return_type := cst_warn;
1369 END IF;
1370 RETURN FALSE;
1371 ELSE
1372 -- There must have been no Admission Process Category Match
1373 p_message_name := 'IGS_AD_PRG_VALIED_ADMCAT';
1374 p_return_type := cst_error;
1375 RETURN FALSE;
1376 END IF;
1377 END;
1378 END admp_val_acai_coo;
1379 --
1380 -- Validate if the IGS_AD_PS_APPL_INST is late.
1381 FUNCTION admp_val_acai_late(
1382 p_appl_dt IN DATE ,
1383 p_course_cd IN VARCHAR2 ,
1384 p_crv_version_number IN NUMBER ,
1385 p_acad_cal_type IN VARCHAR2 ,
1386 p_location_cd IN VARCHAR2 ,
1387 p_attendance_mode IN VARCHAR2 ,
1388 p_attendance_type IN VARCHAR2 ,
1389 p_adm_cal_type IN VARCHAR2 ,
1390 p_adm_ci_sequence_number IN NUMBER ,
1391 p_admission_cat IN VARCHAR2 ,
1392 p_s_admission_process_type IN VARCHAR2 ,
1393 p_late_appl_allowed IN VARCHAR2 ,
1394 p_message_name OUT NOCOPY VARCHAR2 )
1395 RETURN BOOLEAN AS
1396 BEGIN -- admp_val_acai_late
1397 -- Validate if the IGS_AD_PS_APPL_INST is late.
1398 -- Validate against the due date when late applications are not allowed.
1399 -- Validate against the final date when late applications are allowed.
1400 DECLARE
1401 CURSOR c_sacc IS
1402 SELECT adm_appl_due_dt_alias,
1403 adm_appl_final_dt_alias
1404 FROM IGS_AD_CAL_CONF
1405 WHERE s_control_num = 1 AND
1406 adm_appl_due_dt_alias IS NOT NULL AND
1407 adm_appl_final_dt_alias IS NOT NULL;
1408 v_sacc_rec c_sacc%ROWTYPE;
1409 v_final_dt DATE := NULL;
1410 v_due_dt DATE := NULL;
1411 BEGIN
1412 -- Set the default message number
1413 p_message_name := NULL;
1414 -- Get the admission application due and final date aliases.
1415 OPEN c_sacc;
1416 FETCH c_sacc INTO v_sacc_rec;
1417 IF c_sacc%NOTFOUND THEN
1418 CLOSE c_sacc;
1419 p_message_name := 'IGS_AD_CHK_ADMCAL_CONFIG' ;
1420 RETURN TRUE;
1421 END IF;
1422 CLOSE c_sacc;
1423 -- Validate for a late application.
1424 IF p_late_appl_allowed = 'Y' THEN
1425 -- Validate against the final date when late applications are allowed
1426 -- Check the final date.
1427 -- Get admission period final date.
1428 v_final_dt := IGS_AD_GEN_003.ADMP_GET_ADM_PERD_DT(
1429 v_sacc_rec.adm_appl_final_dt_alias,
1430 p_adm_cal_type,
1431 p_adm_ci_sequence_number,
1432 p_admission_cat,
1433 p_s_admission_process_type,
1434 p_course_cd,
1435 p_crv_version_number,
1436 p_acad_cal_type,
1437 p_location_cd,
1438 p_attendance_mode,
1439 p_attendance_type);
1440 IF v_final_dt IS NOT NULL THEN
1441 IF p_appl_dt > v_final_dt THEN
1442 -- Admission course application instance is invalid
1443 -- because the application date is after the due date
1444 -- of the course offering option.
1445 p_message_name := 'IGS_AD_APLDT_ADMAPL_FINALDT';
1446 RETURN FALSE;
1447 END IF;
1448 END IF;
1449 ELSE -- p_late_appl_allowed = 'Y'
1450 -- Validate against the due date when late applications are not allowed.
1451 -- Check the final date.
1452 -- Get admission period final date.
1453 v_due_dt := IGS_AD_GEN_003.ADMP_GET_ADM_PERD_DT(
1454 v_sacc_rec.adm_appl_due_dt_alias,
1455 p_adm_cal_type,
1456 p_adm_ci_sequence_number,
1457 p_admission_cat,
1458 p_s_admission_process_type,
1459 p_course_cd,
1460 p_crv_version_number,
1461 p_acad_cal_type,
1462 p_location_cd,
1463 p_attendance_mode,
1464 p_attendance_type);
1465 IF v_due_dt IS NOT NULL THEN
1466 IF p_appl_dt > v_due_dt THEN
1467 -- Admission course application instance is invalid
1468 -- because the application date is after the due date
1469 -- of the course offering option.
1470 p_message_name := 'IGS_AD_APPLDT_ADMAPPL_DUEDT';
1471 RETURN FALSE;
1472 END IF;
1473 END IF;
1474 END IF;
1475 -- Return the default value
1476 RETURN TRUE;
1477 EXCEPTION
1478 WHEN OTHERS THEN
1479 IF c_sacc%ISOPEN THEN
1480 CLOSE c_sacc;
1481 END IF;
1482 RAISE;
1483 END;
1484 END admp_val_acai_late;
1485 --
1486 -- Validate the admission course appl instance offering option details.
1487 FUNCTION admp_val_acai_opt(
1488 p_course_cd IN VARCHAR2 ,
1489 p_version_number IN NUMBER ,
1490 p_acad_cal_type IN VARCHAR2 ,
1491 p_acad_ci_sequence_number IN NUMBER ,
1492 p_location_cd IN VARCHAR2 ,
1493 p_attendance_mode IN VARCHAR2 ,
1494 p_attendance_type IN VARCHAR2 ,
1495 p_adm_cal_type IN VARCHAR2 ,
1496 p_adm_ci_sequence_number IN NUMBER ,
1497 p_admission_cat IN VARCHAR2 ,
1498 p_s_admission_process_type IN VARCHAR2 ,
1499 p_offer_ind IN VARCHAR2 ,
1500 p_appl_dt IN DATE ,
1501 p_late_appl_allowed IN VARCHAR2 ,
1502 p_message_name OUT NOCOPY VARCHAR2 )
1503 RETURN BOOLEAN AS
1504 BEGIN -- admp_val_acai_opt
1505 -- Validate the admission course application instance offering option details.
1506 DECLARE
1507 v_rec_found BOOLEAN :=FALSE;
1508 v_location_cd IGS_PS_OFR_PAT.location_cd%TYPE;
1509 v_attendance_mode IGS_PS_OFR_PAT.attendance_mode%TYPE;
1510 v_attendance_type IGS_PS_OFR_PAT.attendance_type%TYPE;
1511 v_message_name VARCHAR2(30);
1512 CURSOR c_acopv IS
1513 SELECT location_cd,
1514 attendance_mode,
1515 attendance_type
1516 FROM IGS_PS_OFR_PAT_OFERPAT_V
1517 WHERE course_cd = p_course_cd AND
1518 version_number = p_version_number AND
1519 acad_cal_type = p_acad_cal_type AND
1520 acad_ci_sequence_number = p_acad_ci_sequence_number AND
1521 adm_cal_type = p_adm_cal_type AND
1522 adm_ci_sequence_number = p_adm_ci_sequence_number AND
1523 admission_cat = p_admission_cat AND
1524 s_admission_process_type = p_s_admission_process_type AND
1525 (p_location_cd IS NULL OR
1526 location_cd = p_location_cd) AND
1527 (p_attendance_mode IS NULL OR
1528 attendance_mode = p_attendance_mode) AND
1529 (p_attendance_type IS NULL OR
1530 attendance_type = p_attendance_type) AND
1531 (IGS_AD_GEN_013.ADMS_GET_COO_CRV(
1532 course_cd,
1533 version_number,
1534 s_admission_process_type,
1535 p_offer_ind) = 'Y') AND
1536 (IGS_AD_GEN_013.ADMS_GET_ACAI_COO (
1537 course_cd,
1538 version_number,
1539 location_cd,
1540 attendance_mode,
1541 attendance_type,
1542 acad_cal_type,
1543 acad_ci_sequence_number,
1544 adm_cal_type,
1545 adm_ci_sequence_number,
1546 admission_cat,
1547 s_admission_process_type,
1548 p_offer_ind,
1549 p_appl_dt,
1550 p_late_appl_allowed,
1551 'N') = 'Y'); -- Deferred application.
1552 --------------------------------------- SUB-FUNCTION---------------------------
1553 FUNCTION admpl_val_param
1554 RETURN BOOLEAN
1555 AS
1556 BEGIN -- admpl_val_param
1557 -- a sub-function that validates p_location_cd, p_attendance_mode,
1558 -- p_attendance_type
1559 DECLARE
1560 BEGIN
1561 v_message_name := NULL;
1562 IF (p_location_cd IS NOT NULL AND
1563 v_rec_found = TRUE) THEN
1564 -- Check if the IGS_AD_LOCATION is open and whether IGS_AD_LOCATION type is correct
1565 -- For bug # 1956374 changed the below call from IGS_AD_VAL_APCOO.crsp_val_loc_cd
1566 IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
1567 p_location_cd,
1568 v_message_name) = FALSE THEN
1569 RETURN FALSE;
1570 END IF;
1571 END IF;
1572 IF (p_attendance_mode IS NOT NULL AND
1573 v_rec_found = TRUE) THEN
1574 -- Check if the attendance mode is open
1575 IF IGS_AD_VAL_APCOO.crsp_val_am_closed(
1576 p_attendance_mode,
1577 v_message_name) = FALSE THEN
1578 RETURN FALSE;
1579 END IF;
1580 END IF;
1581 IF (p_attendance_type IS NOT NULL AND
1582 v_rec_found = TRUE) THEN
1583 -- Check if the attendance type is open
1584 IF IGS_AD_VAL_APCOO.crsp_val_att_closed (
1585 p_attendance_type,
1586 v_message_name) = FALSE THEN
1587 RETURN FALSE;
1588 END IF;
1589 END IF;
1590 RETURN TRUE;
1591 END;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1595 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admpl_val_param');
1596 IGS_GE_MSG_STACK.ADD;
1597 APP_EXCEPTION.RAISE_EXCEPTION;
1598 END admpl_val_param;
1599 ----------------------------------------- MAIN ---------------------------------
1600 BEGIN
1601 p_message_name := NULL;
1602 IF p_location_cd IS NOT NULL OR
1603 p_attendance_mode IS NOT NULL OR
1604 p_attendance_type IS NOT NULL THEN
1605 -- Check if the offering option is valid
1606 OPEN c_acopv;
1607 FETCH c_acopv INTO
1608 v_location_cd,
1609 v_attendance_mode,
1610 v_attendance_type;
1611 IF (c_acopv%FOUND) THEN
1612 v_rec_found := TRUE;
1613 END IF;
1614 CLOSE c_acopv;
1615 IF admpl_val_param = FALSE THEN
1616 p_message_name := v_message_name;
1617 RETURN FALSE;
1618 END IF;
1619 END IF;
1620 RETURN TRUE;
1621 END;
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1625 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_opt');
1626 IGS_GE_MSG_STACK.ADD;
1627 APP_EXCEPTION.RAISE_EXCEPTION;
1628 END admp_val_acai_opt;
1629 --
1630 -- Validate the admission course application unit set.
1631 FUNCTION admp_val_acai_us(
1632 p_unit_set_cd IN VARCHAR2 ,
1633 p_us_version_number IN NUMBER ,
1634 p_course_cd IN VARCHAR2 ,
1635 p_crv_version_number IN NUMBER ,
1636 p_acad_cal_type IN VARCHAR2 ,
1637 p_location_cd IN VARCHAR2 ,
1638 p_attendance_mode IN VARCHAR2 ,
1639 p_attendance_type IN VARCHAR2 ,
1640 p_admission_cat IN VARCHAR2 ,
1641 p_offer_ind IN VARCHAR2 ,
1642 p_unit_set_appl IN VARCHAR2 ,
1643 p_message_name OUT NOCOPY VARCHAR2 ,
1644 p_return_type OUT NOCOPY VARCHAR2 )
1645 RETURN BOOLEAN AS
1646 BEGIN -- admp_val_acai_us
1647 -- Validate the admission course application unit set.
1648 -- Validations are:
1649 -- unit set may only be specified when the 'unit set' step exists for the
1650 -- admission process category.
1651 -- If the unit set is offered then both the unit set and unit set version must
1652 -- be specified.
1653 -- If the unit set is nominated then the unit set status must be
1654 -- Active, however, if the unit set is offered the unit set status must be
1655 -- Active.
1656 -- The expiry date of the unit set must not be set.
1657 -- The unit set must be mapped to the course offering option (this is an error
1658 -- on offer but a warning on nomination).
1659 -- The course offering option unit set must be valid for the admission category
1660 -- of the admission application.
1661 DECLARE
1662 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
1663 cst_yes CONSTANT VARCHAR2(1) := 'Y';
1664 cst_error CONSTANT VARCHAR2(1) := 'E';
1665 cst_warn CONSTANT VARCHAR2(1) := 'W';
1666 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
1667 v_expiry_dt IGS_EN_UNIT_SET.expiry_dt%TYPE;
1668 v_message_name VARCHAR2(30);
1669 v_coousv_match BOOLEAN := FALSE;
1670 v_coacus_match BOOLEAN := FALSE;
1671 v_coacus_rec_found BOOLEAN := FALSE;
1672 CURSOR c_us_uss (
1673 cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
1674 cp_us_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
1675 SELECT uss.s_unit_set_status,
1676 us.expiry_dt
1677 FROM IGS_EN_UNIT_SET us,
1678 IGS_EN_UNIT_SET_STAT uss
1679 WHERE us.unit_set_cat IN
1680 ( SELECT usc.unit_set_cat
1681 FROM igs_en_unit_set_cat usc
1682 WHERE (fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND' ) <> 'Y'
1683 OR usc.s_unit_set_cat = 'PRENRL_YR') )
1684 AND us.UNIT_SET_STATUS = uss.UNIT_SET_STATUS
1685 AND us.unit_set_cd = cp_unit_set_cd
1686 AND us.version_number = cp_us_version_number;
1687
1688 CURSOR c_coousv (
1689 cp_unit_set_cd IGS_PS_OF_OPT_UNT_ST.unit_set_cd%TYPE,
1690 cp_us_version_number IGS_PS_OF_OPT_UNT_ST.us_version_number%TYPE,
1691 cp_course_cd IGS_PS_OF_OPT_UNT_ST.course_cd%TYPE,
1692 cp_crv_version_number IGS_PS_OF_OPT_UNT_ST.crv_version_number%TYPE,
1693 cp_acad_cal_type IGS_PS_OF_OPT_UNT_ST.cal_type%TYPE,
1694 cp_admission_cat IGS_PS_COO_AD_UNIT_S.admission_cat%TYPE,
1695 cp_location_cd IGS_PS_COO_AD_UNIT_S.location_cd%TYPE,
1696 cp_attendance_mode IGS_PS_COO_AD_UNIT_S.attendance_mode%TYPE,
1697 cp_attendance_type IGS_PS_COO_AD_UNIT_S.attendance_type%TYPE) IS
1698 SELECT 1
1699 FROM IGS_PS_OFR_OPT_UNIT_SET_V psusv
1700 WHERE psusv.course_cd = cp_course_cd
1701 AND psusv.crv_version_number = cp_crv_version_number
1702 AND psusv.unit_set_cd = cp_unit_set_cd
1703 AND psusv.us_version_number = cp_us_version_number
1704 AND psusv.cal_type = cp_acad_cal_type
1705 AND psusv.location_cd = NVL(cp_location_cd, psusv.location_cd)
1706 AND psusv.attendance_mode = NVL(cp_attendance_mode, psusv.attendance_mode)
1707 AND psusv.attendance_type = NVL(cp_attendance_type, psusv.attendance_type)
1708 AND NOT EXISTS
1709 (SELECT 1
1710 FROM igs_ps_coo_ad_unit_s psus
1711 WHERE psus.course_cd = psusv.course_cd
1712 AND psus.crv_version_number = psusv.crv_version_number
1713 AND psus.cal_type = psusv.cal_type
1714 AND psus.location_cd = psusv.location_cd
1715 AND psus.attendance_mode = psusv.attendance_mode
1716 AND psus.attendance_type = psusv.attendance_type
1717 AND psus.admission_cat = cp_admission_cat
1718 )
1719 AND psusv.UNIT_SET_STATUS IN
1720 (SELECT unit_set_status
1721 FROM igs_en_unit_set_stat uss
1722 WHERE psusv.unit_set_status = uss.unit_set_status
1723 AND uss.s_unit_set_status <> 'INACTIVE'
1724 )
1725 AND psusv.unit_set_cat IN
1726 (SELECT usc.unit_set_cat
1727 FROM igs_en_unit_set_cat usc
1728 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
1729 OR usc.s_unit_set_cat = 'PRENRL_YR'))
1730 )
1731 AND psusv.expiry_dt IS NULL
1732 UNION
1733 SELECT 1
1734 FROM igs_ps_coo_ad_unit_s psus,
1735 igs_en_unit_set us
1736 WHERE us.unit_set_cd = psus.unit_set_cd
1737 AND us.version_number = psus.us_version_number
1738 AND psus.unit_set_cd = cp_unit_set_cd
1739 AND psus.us_version_number = cp_us_version_number
1740 AND psus.course_cd = cp_course_cd
1741 AND psus.crv_version_number = cp_crv_version_number
1742 AND psus.cal_type = cp_acad_cal_type
1743 AND psus.location_cd = NVL(cp_location_cd, psus.location_cd)
1744 AND psus.attendance_mode = NVL(cp_attendance_mode, psus.attendance_mode)
1745 AND psus.attendance_type = NVL(cp_attendance_type, psus.attendance_type)
1746 AND psus.admission_cat = cp_admission_cat
1747 AND us.unit_set_status IN
1748 (SELECT unit_set_status
1749 FROM igs_en_unit_set_stat uss
1750 WHERE us.unit_set_status = uss.unit_set_status
1751 AND uss.s_unit_set_status <> 'INACTIVE'
1752 )
1753 AND us.unit_set_cat IN
1754 (SELECT usc.unit_set_cat
1755 FROM igs_en_unit_set_cat usc
1756 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
1757 OR usc.s_unit_set_cat = 'PRENRL_YR'))
1758 )
1759 AND us.expiry_dt IS NULL;
1760
1761
1762 CURSOR c_coacus (
1763 cp_course_cd IGS_PS_COO_AD_UNIT_S.course_cd%TYPE,
1764 cp_crv_version_number IGS_PS_COO_AD_UNIT_S.crv_version_number%TYPE,
1765 cp_acad_cal_type IGS_PS_COO_AD_UNIT_S.cal_type%TYPE,
1766 cp_admission_cat IGS_PS_COO_AD_UNIT_S.admission_cat%TYPE,
1767 cp_location_cd IGS_PS_COO_AD_UNIT_S.location_cd%TYPE,
1768 cp_attendance_mode IGS_PS_COO_AD_UNIT_S.attendance_mode%TYPE,
1769 cp_attendance_type IGS_PS_COO_AD_UNIT_S.attendance_type%TYPE) IS
1770 SELECT coacus.unit_set_cd,
1771 coacus.us_version_number,
1772 coacus.location_cd,
1773 coacus.attendance_mode,
1774 coacus.attendance_type
1775 FROM IGS_PS_COO_AD_UNIT_S coacus
1776 WHERE coacus.course_cd = cp_course_cd AND
1777 coacus.crv_version_number = cp_crv_version_number AND
1778 coacus.cal_type = cp_acad_cal_type AND
1779 coacus.admission_cat = cp_admission_cat AND
1780 coacus.location_cd = cp_location_cd AND
1781 coacus.attendance_mode = cp_attendance_mode AND
1782 coacus.attendance_type = cp_attendance_type;
1783 BEGIN
1784 -- Initialise out NOCOPY parameters
1785 p_message_name := NULL;
1786 p_return_type := NULL;
1787 IF p_unit_set_appl = 'N' THEN
1788 -- Ensure the unit set details are not specified for an admission application
1789 -- that does not allow unit sets.
1790 IF p_unit_set_cd IS NOT NULL OR
1791 p_us_version_number IS NOT NULL THEN
1792 p_message_name := 'IGS_AD_UNITSET_NOTSPECIFIED' ;
1793 p_return_type := cst_error;
1794 RETURN FALSE;
1795 END IF;
1796 ELSE -- unit sets are allowed for the application.
1797 IF p_unit_set_cd IS NOT NULL AND
1798 p_us_version_number IS NOT NULL THEN
1799 -- Retrieve unit set data
1800 OPEN c_us_uss(
1801 p_unit_set_cd,
1802 p_us_version_number);
1803 FETCH c_us_uss INTO v_s_unit_set_status,
1804 v_expiry_dt;
1805 IF(c_us_uss%FOUND) THEN
1806 -- Validate unit set status
1807 IF p_offer_ind = cst_yes THEN -- Offered
1808 IF v_s_unit_set_status <> cst_active THEN
1809 CLOSE c_us_uss;
1810 p_message_name := 'IGS_AD_UNITSET_MUSTBE_ACTIVE';
1811 p_return_type := cst_error;
1812 RETURN FALSE;
1813 END IF;
1814 ELSE -- Nominated
1815 IF v_s_unit_set_status <> cst_active THEN --removed the planned status as per bug#2722785 --rghosh
1816 CLOSE c_us_uss;
1817 p_message_name := 'IGS_AD_UNITSET_ACTIVE_PLANNED';
1818 p_return_type := cst_warn;
1819 RETURN FALSE;
1820 END IF;
1821 END IF;
1822 -- Validate expiry date
1823 IF v_expiry_dt IS NOT NULL THEN
1824 CLOSE c_us_uss;
1825 p_message_name := 'IGS_AD_UNITSET_EXPDT_NOTBESET';
1826 p_return_type := cst_error;
1827 RETURN FALSE;
1828 END IF;
1829 -- Validate that unit set is mapped to the course offering option.
1830 -- If the option details of the course offering option are specified,
1831 -- then an exact match must be found. If the option details are not
1832 -- specified then a match on the course offering and unit set is all
1833 -- that is needed.
1834 FOR v_coousv_rec IN c_coousv (
1835 p_unit_set_cd,
1836 p_us_version_number,
1837 p_course_cd,
1838 p_crv_version_number,
1839 p_acad_cal_type,
1840 p_admission_cat,
1841 p_location_cd,
1842 p_attendance_mode,
1843 p_attendance_type) LOOP
1844 v_coousv_match := TRUE;
1845 END LOOP;
1846 IF(v_coousv_match = FALSE) THEN
1847 IF p_offer_ind = cst_yes THEN -- Offered
1848 CLOSE c_us_uss;
1849 p_message_name := 'IGS_AD_UNITSET_NOTMAP_POO';
1850 p_return_type := cst_error;
1851 RETURN FALSE;
1852 ELSE -- Nominated
1853 CLOSE c_us_uss;
1854 p_message_name := 'IGS_AD_UNITSET_NOT_MAP_POO';
1855 p_return_type := cst_warn;
1856 RETURN FALSE;
1857 END IF;
1858 END IF;
1859 -- Validate the course offering option unit set is mapped to the admission
1860 -- category.
1861 -- This is a restriction table. If no records exist on the table for the
1862 -- course offering option, then the course offering option unit set is valid
1863 -- for the admission category. However, if any record exists on the table
1864 -- for the course offering option then one must exist for the unit set.
1865 FOR v_coacus_rec IN c_coacus(
1866 p_course_cd,
1867 p_crv_version_number,
1868 p_acad_cal_type,
1869 p_admission_cat,
1870 p_location_cd,
1871 p_attendance_mode,
1872 p_attendance_type) LOOP
1873 v_coacus_rec_found := TRUE;
1874 IF ((p_unit_set_cd = v_coacus_rec.unit_set_cd) AND
1875 (p_us_version_number = v_coacus_rec.us_version_number) AND
1876 (p_location_cd IS NULL OR
1877 p_location_cd = v_coacus_rec.location_cd) AND
1878 (p_attendance_mode IS NULL OR
1879 p_attendance_mode = v_coacus_rec.attendance_mode) AND
1880 (p_attendance_type IS NULL OR
1881 p_attendance_type = v_coacus_rec.attendance_type)) THEN
1882 v_coacus_match := TRUE;
1883 END IF;
1884 END LOOP;
1885 IF(v_coacus_rec_found = TRUE AND
1886 v_coacus_match = FALSE) THEN
1887 IF p_offer_ind = cst_yes THEN -- Offered
1888 CLOSE c_us_uss;
1889 p_message_name := 'IGS_AD_OFRPRG_NOT_VALID';
1890 p_return_type := cst_error;
1891 RETURN FALSE;
1892 ELSE -- Nominated
1893 CLOSE c_us_uss;
1894 p_message_name := 'IGS_AD_PRGOFOP_NOT_VALID';
1895 p_return_type := cst_warn;
1896 RETURN FALSE;
1897 END IF;
1898 END IF;
1899 IF v_coacus_rec_found = FALSE THEN
1900 -- Validate the unit set.
1901 IF IGS_AD_VAL_ACAI.crsp_val_cacus_sub (
1902 p_course_cd,
1903 p_crv_version_number,
1904 p_acad_cal_type,
1905 p_unit_set_cd,
1906 p_us_version_number,
1907 v_message_name) = FALSE THEN
1908 CLOSE c_us_uss;
1909 p_message_name := v_message_name;
1910 p_return_type := cst_error;
1911 RETURN FALSE;
1912 END IF;
1913 END IF;
1914 CLOSE c_us_uss;
1915 ELSE -- unit set record not found.
1916 CLOSE c_us_uss;
1917 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1918 p_return_type := cst_error;
1919 RETURN FALSE;
1920 END IF;
1921 ELSE -- unit set is not specified.
1922 IF p_offer_ind = cst_yes THEN
1923 p_message_name := 'IGS_AD_UNITSET_MUSTBE_SPECIFI';
1924 p_return_type := cst_error;
1925 RETURN FALSE;
1926 END IF;
1927 END IF;
1928 END IF;
1929 RETURN TRUE;
1930 END;
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1934 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_us');
1935 IGS_GE_MSG_STACK.ADD;
1936 APP_EXCEPTION.RAISE_EXCEPTION;
1937 END admp_val_acai_us;
1938 --
1939 -- Validate CACUS can only be created when US is not a subordinate
1940 FUNCTION crsp_val_cacus_sub(
1941 p_course_cd IN VARCHAR2 ,
1942 p_crv_version_number IN NUMBER ,
1943 p_cal_type IN VARCHAR2 ,
1944 p_unit_set_cd IN VARCHAR2 ,
1945 p_us_version_number IN NUMBER ,
1946 p_message_name OUT NOCOPY varchar2 )
1947 RETURN BOOLEAN AS
1948 BEGIN -- crsp_val_cacus_sub
1949 -- This module validates that a crs_adm_cat_unit_set record can only be
1950 -- created when:
1951 -- . the IGS_PS_OFR_UNIT_SET.only_as_sub_ind = 'N' and
1952 -- . the IGS_PS_OFR_UNIT_SET does not exist as a subordinate in the
1953 -- IGS_PS_OF_UNT_SET_RL table.
1954 DECLARE
1955 v_dummy VARCHAR2(1);
1956 CURSOR c_cousr IS
1957 SELECT 'x'
1958 FROM IGS_PS_OF_UNT_SET_RL cousr
1959 WHERE cousr.course_cd = p_course_cd AND
1960 cousr.crv_version_number = p_crv_version_number AND
1961 cousr.cal_type = p_cal_type AND
1962 cousr.sub_unit_set_cd = p_unit_set_cd AND
1963 cousr.sub_us_version_number = p_us_version_number;
1964 BEGIN
1965 -- set default vaule
1966 p_message_name := null;
1967 IF IGS_PS_GEN_003.CRSP_GET_COUS_SUBIND (
1968 p_course_cd,
1969 p_crv_version_number,
1970 p_cal_type,
1971 p_unit_set_cd,
1972 p_us_version_number) = 'Y' THEN
1973 p_message_name := 'IGS_PS_UNIT_SET_IND_Y';
1974 RETURN FALSE;
1975 END IF;
1976 OPEN c_cousr;
1977 FETCH c_cousr INTO v_dummy;
1978 IF c_cousr%FOUND THEN
1979 CLOSE c_cousr;
1980 p_message_name := 'IGS_PS_UNIT_SET_EXISTS_SUBORD';
1981 RETURN FALSE;
1982 END IF;
1983 CLOSE c_cousr;
1984 RETURN TRUE;
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 IF c_cousr%ISOPEN THEN
1988 CLOSE c_cousr;
1989 END IF;
1990 RAISE;
1991 END;
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
1995 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.crsp_val_cacus_sub');
1996 IGS_GE_MSG_STACK.ADD;
1997 APP_EXCEPTION.RAISE_EXCEPTION;
1998 END crsp_val_cacus_sub;
1999 --
2000 -- Do encumbrance checks for the IGS_AD_PS_APPL_INST.unit_set_cd.
2001 FUNCTION admp_val_us_encmb(
2002 p_person_id IN NUMBER ,
2003 p_course_cd IN VARCHAR2 ,
2004 p_unit_set_cd IN VARCHAR2 ,
2005 p_us_version_number IN NUMBER ,
2006 p_adm_cal_type IN VARCHAR2 ,
2007 p_adm_ci_sequence_number IN NUMBER ,
2008 p_course_encmb_chk_ind IN VARCHAR2 ,
2009 p_offer_ind IN VARCHAR2 ,
2010 p_message_name OUT NOCOPY VARCHAR2 ,
2011 p_return_type OUT NOCOPY VARCHAR2 )
2012 RETURN BOOLEAN AS
2013 BEGIN -- admp_val_us_encmb
2014 -- Perform encumberance checks for the
2015 -- admission_course_appl_instance.unit_set_cd
2016 DECLARE
2017 v_encmb_check_dt DATE;
2018 v_message_name VARCHAR2(30);
2019 BEGIN
2020 p_message_name := NULL;
2021 IF p_unit_set_cd IS NOT NULL AND
2022 p_us_version_number IS NOT NULL AND
2023 p_course_encmb_chk_ind = 'Y' THEN
2024 -- Get the encumberance checking date.
2025 v_encmb_check_dt := NVL(IGS_AD_GEN_006.ADMP_GET_ENCMB_DT(
2026 p_adm_cal_type,
2027 p_adm_ci_sequence_number),SYSDATE);
2028 /* IF v_encmb_check_dt IS NULL THEN
2029 IF p_offer_ind = 'Y' THEN
2030 p_message_name := 'IGS_AD_ENCUMB_CANNOT_PERFORM';
2031 p_return_type := 'E';
2032 ELSE
2033 p_message_name := 'IGS_AD_ENCUMB_CHK_NOT_PERFORM';
2034 p_return_type := 'W';
2035 END IF;
2036 RETURN FALSE;
2037 END IF;
2038 */
2039 -- Validate for exclusion or suspension from the
2040 -- unit set version within the course.
2041 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_us(
2042 p_person_id,
2043 p_course_cd,
2044 p_unit_set_cd,
2045 p_us_version_number,
2046 v_encmb_check_dt,
2047 v_message_name) THEN
2048 IF p_offer_ind = 'Y' THEN
2049 p_message_name := 'IGS_AD_PRSN_ENCUMB_EXCL_UNIT';
2050 p_return_type := 'E';
2051 ELSE
2052 p_message_name := 'IGS_AD_ENCUMB_EXCL_PRG';
2053 p_return_type := 'W';
2054 END IF;
2055 RETURN FALSE;
2056 END IF;
2057 END IF;
2058 RETURN TRUE;
2059 END;
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2063 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_us_encmb');
2064 IGS_GE_MSG_STACK.ADD;
2065 APP_EXCEPTION.RAISE_EXCEPTION;
2066 END admp_val_us_encmb;
2067 --
2068 -- Validate the IGS_AD_PS_APPL_INST.offer_dt.
2069 FUNCTION admp_val_offer_dt(
2070 p_offer_dt IN DATE ,
2071 p_adm_outcome_status IN VARCHAR2 ,
2072 p_adm_cal_type IN VARCHAR2 ,
2073 p_adm_ci_sequence_number IN NUMBER ,
2074 p_message_name OUT NOCOPY VARCHAR2 )
2075 RETURN BOOLEAN AS
2076 BEGIN -- admp_val_offer_dt
2077 -- Validate the IGS_AD_PS_APPL_INST.offer_dt.
2078 DECLARE
2079 v_s_adm_outcome_status VARCHAR2(255);
2080 v_course_start_dt DATE;
2081 BEGIN
2082 p_message_name := NULL;
2083 -- Validate offer date against the admission outcome status.
2084 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (
2085 p_adm_outcome_status);
2086 -- Offer date must be not be set for a pending admission course application
2087 -- instance.
2088 IF v_s_adm_outcome_status = 'PENDING' AND
2089 p_offer_dt IS NOT NULL THEN
2090 p_message_name := 'IGS_AD_OFRDT_SET_PENDING_AMD';
2091 RETURN FALSE;
2092 END IF;
2093 -- Offer date must be set for an offered admission course application instance.
2094 IF v_s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
2095 p_offer_dt IS NULL THEN
2096 p_message_name := 'IGS_AD_OFRDT_SET_ADMPRG_APPL';
2097 RETURN FALSE;
2098 END IF;
2099 -- The following code has been commented out.
2100 -- The users decided this check is no longer required.
2101
2102
2103 RETURN TRUE;
2104 END;
2105 EXCEPTION
2106 WHEN OTHERS THEN
2107 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2108 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_offer_dt');
2109 IGS_GE_MSG_STACK.ADD;
2110 APP_EXCEPTION.RAISE_EXCEPTION;
2111 END admp_val_offer_dt;
2112 --
2113 -- Validate the IGS_AD_PS_APPL_INST.offer_response_dt.
2114 FUNCTION admp_val_off_resp_dt(
2115 p_offer_response_dt IN DATE ,
2116 p_adm_outcome_status IN VARCHAR2 ,
2117 p_offer_dt IN DATE ,
2118 p_message_name OUT NOCOPY VARCHAR2 )
2119 RETURN BOOLEAN AS
2120 BEGIN -- admp_val_off_resp_dt
2121 -- Validate the IGS_AD_PS_APPL_INST.offer_response_dt.
2122 DECLARE
2123 v_s_adm_outcome_status VARCHAR2(255);
2124 BEGIN
2125 p_message_name := NULL;
2126 -- Validate offer response date against the admission outcome status.
2127 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (
2128 p_adm_outcome_status);
2129 -- Offer response date must be not be set for a pending admission course
2130 -- application instance.
2131 IF v_s_adm_outcome_status = 'PENDING' AND
2132 p_offer_response_dt IS NOT NULL THEN
2133 p_message_name := 'IGS_AD_OFR_RESPDT_SET_PENDING';
2134 RETURN FALSE;
2135 END IF;
2136 -- Offer response date must be set for an offered admission course application
2137 -- instance.
2138 IF v_s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
2139 p_offer_response_dt IS NULL THEN
2140 p_message_name := 'IGS_AD_OFR_RESPDT_SET_ODR_ADM';
2141 RETURN FALSE;
2142 END IF;
2143 -- Validate offer response date against offer date.
2144 IF TRUNC(p_offer_response_dt) < TRUNC(p_offer_dt) THEN
2145 p_message_name := 'IGS_AD_OFR_RSPDT_GE_OFRDT';
2146 RETURN FALSE;
2147 END IF;
2148 RETURN TRUE;
2149 END;
2150 EXCEPTION
2151 WHEN OTHERS THEN
2152 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2153 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_off_resp_dt');
2154 IGS_GE_MSG_STACK.ADD;
2155 APP_EXCEPTION.RAISE_EXCEPTION;
2156 END admp_val_off_resp_dt;
2157 --
2158 -- Validate the IGS_AD_PS_APPL_INST.actual_response_dt.
2159 FUNCTION admp_val_act_resp_dt(
2160 p_actual_response_dt IN DATE ,
2161 p_adm_offer_resp_status IN VARCHAR2 ,
2162 p_offer_dt IN DATE ,
2163 p_message_name OUT NOCOPY VARCHAR2 )
2164 RETURN BOOLEAN AS
2165 BEGIN -- admp_val_act_resp_dt
2166 -- Validate the IGS_AD_PS_APPL_INST.actual_response_dt.
2167 DECLARE
2168 v_s_adm_offer_resp_status VARCHAR2(255);
2169 BEGIN
2170 p_message_name := NULL;
2171 -- Validate actual response date against the admission offer response status.
2172 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
2173 p_adm_offer_resp_status);
2174 -- Actual response date must be not be set for an admission course application
2175 -- instance with an offer response status of pending, lapsed or not-applicable.
2176 IF v_s_adm_offer_resp_status IN ('PENDING', 'LAPSED', 'NOT-APPLIC') THEN
2177 IF p_actual_response_dt IS NOT NULL THEN
2178 p_message_name := 'IGS_AD_ACT_RSPDT_NOT_SET_ADM';
2179 RETURN FALSE;
2180 END IF;
2181 ELSE
2182 -- Actual response date must be set for an admission course application
2183 -- instance with
2184 -- an offer response status that is not pending, lapsed or not-applicable.
2185 IF (p_actual_response_dt IS NULL) THEN
2186 p_message_name := 'IGS_AD_ACT_RSPDT_SET_ADMPRG';
2187 RETURN FALSE;
2188 END IF;
2189 END IF;
2190 -- Validate actual response date against current date.
2191 IF TRUNC(p_actual_response_dt) > TRUNC(SYSDATE) THEN
2192 p_message_name := 'IGS_AD_ACT_RSPDT_LE_CURDT';
2193 RETURN FALSE;
2194 END IF;
2195 -- Validate actual response date against offer date.
2196 IF p_actual_response_dt IS NOT NULL AND
2197 TRUNC(p_actual_response_dt) < TRUNC(p_offer_dt) THEN
2198 p_message_name := 'IGS_AD_ACTRESPDT_GE_OFRDT';
2199 RETURN FALSE;
2200 END IF;
2201 RETURN TRUE;
2202 END;
2203 EXCEPTION
2204 WHEN OTHERS THEN
2205 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2206 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_act_resp_dt');
2207 IGS_GE_MSG_STACK.ADD;
2208 APP_EXCEPTION.RAISE_EXCEPTION;
2209 END admp_val_act_resp_dt;
2210 --
2211 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_satisfied_dt.
2212 FUNCTION admp_val_stsfd_dt(
2213 p_cndtnl_offer_satisfied_dt IN DATE ,
2214 p_adm_cndtnl_offer_status IN VARCHAR2 ,
2215 p_message_name OUT NOCOPY VARCHAR2 )
2216 RETURN BOOLEAN AS
2217 BEGIN -- admp_val_stsfd_dt
2218 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_satisfied_dt.
2219 DECLARE
2220 v_s_adm_cndtnl_offer_status VARCHAR2(255);
2221 BEGIN
2222 p_message_name := NULL;
2223 -- Validate conditional offer satisfied date against the admission conditional
2224 -- offer status.
2225 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS (
2226 p_adm_cndtnl_offer_status);
2227 -- Conditional offer satisfied date must be set when the conditional offer has
2228 -- been
2229 -- satisfied or waived.
2230 IF v_s_adm_cndtnl_offer_status IN ('SATISFIED', 'WAIVED') THEN
2231 IF (p_cndtnl_offer_satisfied_dt IS NULL) THEN
2232 p_message_name := 'IGS_AD_OFRDT_SET_CONDOFR';
2233 RETURN FALSE;
2234 END IF;
2235 ELSE
2236 -- Conditional offer satisfied date must not be set when the conditional offer
2237 -- has not
2238 -- been satisfied or waived.
2239 IF (p_cndtnl_offer_satisfied_dt IS NOT NULL) THEN
2240 p_message_name := 'IGS_AD_OFRDT_NOT_SET_CONDOFR';
2241 RETURN FALSE;
2242 END IF;
2243 END IF;
2244 -- Validate conditional offer satisfied date against current date.
2245 IF TRUNC(p_cndtnl_offer_satisfied_dt) > TRUNC(SYSDATE) THEN
2246 p_message_name := 'IGS_AD_COND_OFRDT_LE_CURDT';
2247 RETURN FALSE;
2248 END IF;
2249 RETURN TRUE;
2250 END;
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2254 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_stsfd_dt');
2255 IGS_GE_MSG_STACK.ADD;
2256 APP_EXCEPTION.RAISE_EXCEPTION;
2257 END admp_val_stsfd_dt;
2258 --
2259 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_cndtn.
2260 FUNCTION admp_val_offer_cndtn(
2261 p_cndtnl_offer_cndtn IN VARCHAR2 ,
2262 p_adm_cndtnl_offer_status IN VARCHAR2 ,
2263 p_message_name OUT NOCOPY VARCHAR2 )
2264 RETURN BOOLEAN AS
2265 BEGIN -- admp_val_offer_cndtn
2266 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_cndtn.
2267 -- Validations are:
2268 -- * The conditional offer condition may only have a value when a
2269 -- conditional offer has been made.
2270 DECLARE
2271 cst_not_applic CONSTANT VARCHAR2(10) :='NOT-APPLIC';
2272 v_s_adm_cndtnl_offer_status
2273 IGS_AD_CNDNL_OFRSTAT.s_adm_cndtnl_offer_status%TYPE;
2274 BEGIN
2275 -- Set the default message number
2276 p_message_name := NULL;
2277 -- Validate the conditional offer condition against the admission
2278 -- conditional offer status.
2279 IF p_cndtnl_offer_cndtn IS NOT NULL THEN
2280 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS (
2281 p_adm_cndtnl_offer_status);
2282 -- The conditional offer condition may only be specified when an
2283 -- conditional offer has been made.
2284 IF v_s_adm_cndtnl_offer_status = cst_not_applic THEN
2285 p_message_name := 'IGS_AD_CONDOFR_COND';
2286 RETURN FALSE;
2287 END IF;
2288 END IF;
2289 RETURN TRUE;
2290 END;
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2294 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_offer_cndtn');
2295 IGS_GE_MSG_STACK.ADD;
2296 APP_EXCEPTION.RAISE_EXCEPTION;
2297 END admp_val_offer_cndtn;
2298 --
2299 -- Validate the IGS_AD_PS_APPL_INST.applicant_acptnce_cndtn.
2300 FUNCTION admp_val_acpt_cndtn(
2301 p_applicant_acptnce_cndtn IN VARCHAR2 ,
2302 p_adm_offer_resp_status IN VARCHAR2 ,
2303 p_message_name OUT NOCOPY VARCHAR2 )
2304 RETURN BOOLEAN AS
2305 BEGIN -- admp_val_acpt_cndtn
2306 -- Validate the IGS_AD_PS_APPL_INST.applicant_acptnce_cndtn.
2307 -- Validations are:
2308 -- * The applicant acceptance condition may only have a value when the
2309 -- applicant has responded to an offer of admission.
2310 DECLARE
2311 cst_not_applic CONSTANT VARCHAR2(10) :='NOT-APPLIC';
2312 cst_pending CONSTANT VARCHAR2(7) :='PENDING';
2313 cst_lapsed CONSTANT VARCHAR2(6) :='LAPSED';
2314 v_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
2315 BEGIN
2316 -- Set the default message number
2317 p_message_name := NULL;
2318 -- Validate the applicant acceptance condition against the admission
2319 -- offer response status.
2320 IF p_applicant_acptnce_cndtn IS NOT NULL THEN
2321 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
2322 p_adm_offer_resp_status);
2323 -- The applicant acceptance condition may only be specified when an
2324 -- applicant has responded to an offer of admission.
2325 IF v_s_adm_offer_resp_status IN (cst_pending,
2326 cst_lapsed,
2327 cst_not_applic) THEN
2328 p_message_name := 'IGS_AD_APPL_RESP_COMMENTS';
2329 RETURN FALSE;
2330 END IF;
2331 END IF;
2332 RETURN TRUE;
2333 END;
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2337 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acpt_cndtn');
2338 IGS_GE_MSG_STACK.ADD;
2339 APP_EXCEPTION.RAISE_EXCEPTION;
2340 END admp_val_acpt_cndtn;
2341 --
2342 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind.
2343 FUNCTION admp_val_must_stsfd(
2344 p_cndtnl_off_must_be_stsfd_ind IN VARCHAR2 ,
2345 p_adm_cndtnl_offer_status IN VARCHAR2 ,
2346 p_adm_offer_resp_status IN VARCHAR2 ,
2347 p_cndtnl_offer_satisfied_dt IN DATE ,
2348 p_message_name OUT NOCOPY VARCHAR2 )
2349 RETURN BOOLEAN AS
2350 BEGIN -- admp_val_must_stsfd
2351 -- Validate the IGS_AD_PS_APPL_INST.cndtnl_offer_must_be_stsfd_ind.
2352 DECLARE
2353 v_s_adm_cndtnl_offer_status
2354 IGS_AD_CNDNL_OFRSTAT.adm_cndtnl_offer_status%TYPE;
2355 v_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.adm_offer_resp_status%TYPE;
2356 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
2357 cst_accepted CONSTANT VARCHAR2(8) := 'ACCEPTED';
2358
2359 BEGIN
2360 -- Set up the default return value.
2361 p_message_name := NULL;
2362
2363 IF p_cndtnl_off_must_be_stsfd_ind = 'Y' THEN
2364 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS(
2365 p_adm_cndtnl_offer_status);
2366 IF v_s_adm_cndtnl_offer_status = cst_not_applic THEN
2367 -- The conditional offer must be satisfied indicator cannot be set when
2368 -- the conditional offer status is not applicable.
2369 p_message_name := 'IGS_AD_SATISFIEDINDICATOR_SET';
2370 RETURN FALSE;
2371 END IF;
2372 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
2373 p_adm_offer_resp_status);
2374 IF v_s_adm_offer_resp_status = cst_accepted AND
2375 p_cndtnl_offer_satisfied_dt IS NULL THEN
2376 -- The conditional offer must be satisfied before it can be accepted.
2377 p_message_name := 'IGS_AD_CONDOFR_ACCEPTED';
2378 RETURN FALSE;
2379 END IF;
2380 END IF;
2381 RETURN TRUE;
2382 END;
2383 EXCEPTION
2384 WHEN OTHERS THEN
2385 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2386 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_must_stsfd');
2387 IGS_GE_MSG_STACK.ADD;
2388 APP_EXCEPTION.RAISE_EXCEPTION;
2389 END admp_val_must_stsfd;
2390 --
2391 -- Validate adm course application instance deferred admission calendar.
2392 FUNCTION admp_val_dfrmnt_cal(
2393 p_deferred_adm_cal_type IN VARCHAR2 ,
2394 p_deferred_adm_ci_sequence_num IN NUMBER ,
2395 p_acad_cal_type IN VARCHAR2 ,
2396 p_adm_offer_resp_status IN VARCHAR2 ,
2397 p_admission_cat IN VARCHAR2 ,
2398 p_s_admission_process_type IN VARCHAR2 ,
2399 p_appl_dt IN DATE ,
2400 p_course_cd IN VARCHAR2 ,
2401 p_crv_version_number IN NUMBER ,
2402 p_location_cd IN VARCHAR2 ,
2403 p_attendance_mode IN VARCHAR2 ,
2404 p_attendance_type IN VARCHAR2 ,
2405 p_unit_set_cd IN VARCHAR2 ,
2406 p_us_version_number IN NUMBER ,
2407 p_deferral_allowed IN VARCHAR2 ,
2408 p_late_appl_allowed IN VARCHAR2 ,
2409 p_message_name OUT NOCOPY VARCHAR2 ,
2410 p_return_type OUT NOCOPY VARCHAR2,
2411 p_def_acad_ci_sequence_num IN NUMBER
2412 )
2413 RETURN BOOLEAN AS
2414 BEGIN -- admp_val_dfrmnt_cal
2415 -- Validate the admission course application instance
2416 -- deferred admission calendar
2417 -- Validations are:
2418 -- - If deferral is not allowed, then the deferred
2419 -- admission calendar must not be specified
2420 -- - If the admission offer response status has a value of deferral,
2421 -- then the deferred admission calendar must be specified
2422 -- - If the deferred admission calendar is specified,
2423 -- then it must be an admission calendar
2424 -- - If the deferred admission calendar is specified,
2425 -- then it must be a active calendar instance
2426 -- - If the deferred admission calendar is specified,
2427 -- then it must be a child of an academic calendar that is the same
2428 -- calendar tyoe as the admission application academic calendar.
2429 -- - If the deferred admission calendar is specified,
2430 -- then it must be for the admission process category of the
2431 -- admission application
2432 -- - If the deferred admission calendar is specified,
2433 -- then the course offering pattern must be valid for the
2434 -- deferred admission period
2435 -- - If the deferred admission calendar is specified,
2436 -- and unit sets are applicable for the admission application
2437 -- and then the unit set must be valid for the deferred admission period.
2438 DECLARE
2439 cst_error CONSTANT VARCHAR2(1) := 'E';
2440 cst_deferral CONSTANT
2441 IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE := 'DEFERRAL';
2442 v_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
2443 v_message_name VARCHAR2(30);
2444 v_return_type VARCHAR2(1);
2445 v_late_ind VARCHAR2(1);
2446 BEGIN
2447 -- Validate if the deferred admission calendar can be specified
2448 IF (p_deferral_allowed = 'N' AND
2449 (p_deferred_adm_cal_type IS NOT NULL OR
2450 p_deferred_adm_ci_sequence_num IS NOT NULL)) THEN
2451 -- The deferred admission calendar must not be
2452 -- specified when deferments are not allowed.
2453 p_message_name := 'IGS_AD_DFRD_ADMCAL_NOTALLOW';
2454 p_return_type := cst_error;
2455 RETURN FALSE;
2456 END IF;
2457 -- Validate that the deferred admission calendar has been specified.
2458 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status);
2459 IF (v_s_adm_offer_resp_status = cst_deferral AND
2460 (p_deferred_adm_cal_type IS NULL OR
2461 p_deferred_adm_ci_sequence_num IS NULL)) THEN
2462 -- The deferred admission calendar must be
2463 -- specified when an offer has been deferred.
2464 p_message_name := 'IGS_AD_DFRD_ADMCAL_DEFFERED';
2465 p_return_type := cst_error;
2466 RETURN FALSE;
2467 END IF;
2468 IF (p_deferred_adm_cal_type IS NOT NULL) THEN
2469 -- Validate the deferred admission calendar
2470 -- Determine the calendar instance sequence number of the academic calendar
2471 IF (IGS_AD_VAL_AA.admp_val_aa_adm_cal(
2472 p_deferred_adm_cal_type,
2473 p_deferred_adm_ci_sequence_num,
2474 p_acad_cal_type,
2475 p_def_acad_ci_sequence_num,
2476 p_admission_cat,
2477 p_s_admission_process_type,
2478 v_message_name) = FALSE) THEN
2479 -- The deferred admission calendar is invalid
2480 p_message_name := v_message_name;
2481 p_return_type := cst_error;
2482 RETURN FALSE;
2483 END IF;
2484 -- Validate the course offering pattern in the deferred admission period
2485 IF (IGS_AD_VAL_ACAI.admp_val_acai_cop(
2486 p_course_cd,
2487 p_crv_version_number,
2488 p_location_cd,
2489 p_attendance_mode,
2490 p_attendance_type,
2491 p_acad_cal_type,
2492 p_def_acad_ci_sequence_num,
2493 p_deferred_adm_cal_type,
2494 p_deferred_adm_ci_sequence_num,
2495 p_admission_cat,
2496 p_s_admission_process_type,
2497 'Y', -- Offer indicator
2498 p_appl_dt,
2499 p_late_appl_allowed,
2500 'Y', -- Deferred application
2501 v_message_name,
2502 v_return_type,
2503 v_late_ind) = FALSE) THEN
2504 p_message_name := v_message_name;
2505 p_return_type := v_return_type;
2506 RETURN FALSE;
2507 END IF;
2508 -- Validate the unit set in the deferred admission period
2509 IF (p_unit_set_cd IS NOT NULL) THEN
2510 IF (IGS_AD_VAL_ACAI.admp_val_acai_us(
2511 p_unit_set_cd,
2512 p_us_version_number,
2513 p_course_cd,
2514 p_crv_version_number,
2515 p_acad_cal_type,
2516 p_location_cd,
2517 p_attendance_mode,
2518 p_attendance_type,
2519 p_admission_cat,
2520 'Y', -- Offer indicator
2521 'Y', -- unit Set application
2522 v_message_name,
2523 v_return_type) = FALSE) THEN
2524 p_message_name := v_message_name;
2525 p_return_type := cst_error;
2526 RETURN FALSE;
2527 END IF;
2528 END IF;
2529 END IF;
2530 p_message_name := NULL;
2531 RETURN TRUE;
2532 END;
2533 EXCEPTION
2534 WHEN OTHERS THEN
2535 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2536 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_dfrmnt_cal');
2537 IGS_GE_MSG_STACK.ADD;
2538 APP_EXCEPTION.RAISE_EXCEPTION;
2539 END admp_val_dfrmnt_cal;
2540 --
2541 -- Validate if admission course application instance corresponce cat.
2542 FUNCTION admp_val_acai_cc(
2543 p_admission_cat IN VARCHAR2 ,
2544 p_correspondence_cat IN VARCHAR2 ,
2545 p_message_name OUT NOCOPY VARCHAR2 )
2546 RETURN BOOLEAN AS
2547 BEGIN -- admp_val_acai_cc
2548 -- Validate that the nominated course admission application is valid
2549 -- for the admission application system process type and any existing
2550 -- course attempts the student may have
2551 DECLARE
2552 CURSOR c_ccm IS
2553 Select 'x'
2554 FROM IGS_CO_CAT_MAP
2555 WHERE correspondence_cat = p_correspondence_cat AND
2556 admission_cat = p_admission_cat;
2557 v_x VARCHAR2(1) := NULL;
2558 v_message_name VARCHAR2(30);
2559 BEGIN
2560 p_message_name := NULL;
2561 IF (p_correspondence_cat IS NULL) THEN
2562 RETURN TRUE;
2563 END IF;
2564 -- Validate that correspondence category is not closed
2565 IF IGS_AD_VAL_ACAI.corp_val_cc_closed(
2566 p_correspondence_cat,
2567 v_message_name) = FALSE THEN
2568 p_message_name := v_message_name;
2569 Return FALSE;
2570 END IF;
2571 -- Validate that correspondence category has a mapping to the
2572 -- admission category
2573 OPEN c_ccm;
2574 FETCH c_ccm INTO v_x;
2575 IF (c_ccm%NOTFOUND) THEN
2576 CLOSE c_ccm;
2577 p_message_name := 'IGS_AD_CORCAT_NOTVALID_ADMCAT';
2578 RETURN FALSE;
2579 END IF;
2580 CLOSE c_ccm;
2581 RETURN TRUE;
2582 END;
2583 EXCEPTION
2584 WHEN OTHERS THEN
2585 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2586 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_cc');
2587 IGS_GE_MSG_STACK.ADD;
2588 APP_EXCEPTION.RAISE_EXCEPTION;
2589 END admp_val_acai_cc;
2590 --
2591 -- Validate if IGS_CO_CAT.correspondence_cat is closed.
2592 FUNCTION corp_val_cc_closed(
2593 p_correspondence_cat IN VARCHAR2 ,
2594 p_message_name OUT NOCOPY VARCHAR2 )
2595 RETURN BOOLEAN AS
2596 BEGIN --corp_val_cc_closed
2597 --Validate if IGS_CO_CAT.correspondence_cat is closed
2598 DECLARE
2599 v_closed_ind IGS_CO_CAT.closed_ind%TYPE;
2600 CURSOR c_cc IS
2601 SELECT cc.closed_ind
2602 FROM IGS_CO_CAT cc
2603 WHERE cc.correspondence_cat = p_correspondence_cat;
2604 BEGIN
2605 --- Set the default message number
2606 p_message_name := NULL;
2607 OPEN c_cc;
2608 FETCH c_cc INTO v_closed_ind;
2609 IF (c_cc%FOUND)THEN
2610 IF (v_closed_ind = 'Y') THEN
2611 p_message_name := 'IGS_CO_CORCAT_IS_CLOSED' ;
2612 CLOSE c_cc;
2613 RETURN FALSE;
2614 END IF;
2615 END IF;
2616 CLOSE c_cc;
2617 RETURN TRUE;
2618 END;
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2622 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.corp_val_cc_closed');
2623 IGS_GE_MSG_STACK.ADD;
2624 APP_EXCEPTION.RAISE_EXCEPTION;
2625 END corp_val_cc_closed;
2626 --
2627 -- Validate if admission course application instance enrolment category.
2628 FUNCTION admp_val_acai_ec(
2629 p_admission_cat IN VARCHAR2 ,
2630 p_enrolment_cat IN VARCHAR2 ,
2631 p_message_name OUT NOCOPY VARCHAR2 )
2632 RETURN BOOLEAN AS
2633 BEGIN -- admp_val_acai_ec
2634 -- Validate that the nominated course admission application is valid
2635 -- for the admission application system process type and any
2636 -- existing course attempts the student may have
2637 DECLARE
2638 CURSOR c_ccm IS
2639 Select 'x'
2640 FROM IGS_EN_CAT_MAPPING
2641 WHERE enrolment_cat = p_enrolment_cat AND
2642 admission_cat = p_admission_cat;
2643 v_x VARCHAR2(1) := NULL;
2644 v_message_name VARCHAR2(30);
2645 BEGIN
2646 p_message_name := NULL;
2647 IF (p_enrolment_cat IS NULL) THEN
2648 RETURN TRUE;
2649 END IF;
2650 -- Validate that enrolment category is not closed
2651 IF IGS_AD_VAL_ECM.enrp_val_ec_closed(
2652 p_enrolment_cat,
2653 v_message_name) = FALSE THEN
2654 p_message_name := v_message_name;
2655 Return FALSE;
2656 END IF;
2657 -- Validate that enrolment category has a mapping to the admission
2658 -- category
2659 OPEN c_ccm;
2660 FETCH c_ccm INTO v_x;
2661 IF (c_ccm%NOTFOUND) THEN
2662 CLOSE c_ccm;
2663 p_message_name := 'IGS_AD_ENRCAT_NOTVALID_ADMCAT' ;
2664 RETURN FALSE;
2665 END IF;
2666 CLOSE c_ccm;
2667 RETURN TRUE;
2668 END;
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2672 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_ec');
2673 IGS_GE_MSG_STACK.ADD;
2674 APP_EXCEPTION.RAISE_EXCEPTION;
2675 END admp_val_acai_ec;
2676
2677
2678 --
2679 -- bug id : 1956374
2680 -- sjadhav , 28-aug-2001
2681 -- removed function enrp_val_ec_closed
2682 -- change igs_ad_val_acai.enrp_val_ec_closed
2683 -- to igs_ad_val_ecm.enrp_val_ec_closed
2684 --
2685 --
2686 -- Validate if admission course application instance fee category.
2687 FUNCTION admp_val_acai_fc(
2688 p_admission_cat IN VARCHAR2 ,
2689 p_fee_cat IN VARCHAR2 ,
2690 p_message_name OUT NOCOPY VARCHAR2 )
2691 RETURN BOOLEAN AS
2692 BEGIN -- admp_val_acai_fc
2693 DECLARE
2694 CURSOR c_fcm(
2695 cp_admission_cat IGS_FI_FEE_CAT_MAP.admission_cat%TYPE,
2696 cp_fee_cat IGS_FI_FEE_CAT_MAP.fee_cat%TYPE) IS
2697 SELECT 'x'
2698 FROM IGS_FI_FEE_CAT_MAP
2699 WHERE fee_cat = cp_fee_cat AND
2700 admission_cat = cp_admission_cat;
2701 v_fcm_rec c_fcm%ROWTYPE;
2702 v_message_name VARCHAR2(30);
2703 BEGIN
2704 -- Set the default message number
2705 p_message_name := NULL;
2706 IF (p_fee_cat IS NULL) THEN
2707 RETURN TRUE;
2708 END IF;
2709 IF IGS_AD_VAL_FCM.finp_val_fc_closed(
2710 p_fee_cat,
2711 v_message_name) = FALSE THEN
2712 p_message_name := v_message_name;
2713 RETURN FALSE;
2714 END IF;
2715 -- Cursor handling
2716 OPEN c_fcm(
2717 p_admission_cat,
2718 p_fee_cat);
2719 FETCH c_fcm INTO v_fcm_rec;
2720 IF c_fcm%NOTFOUND THEN
2721 CLOSE c_fcm;
2722 p_message_name := 'IGS_AD_FEECAT_NOTVALID_ADMCAT';
2723 RETURN FALSE;
2724 END IF;
2725 CLOSE c_fcm;
2726 -- Return the default value
2727 RETURN TRUE;
2728 END;
2729 EXCEPTION
2730 WHEN OTHERS THEN
2731 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2732 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_fc');
2733 IGS_GE_MSG_STACK.ADD;
2734 APP_EXCEPTION.RAISE_EXCEPTION;
2735 END admp_val_acai_fc;
2736 --
2737 -- Validate admission course application instance HECS payment option.
2738 FUNCTION admp_val_acai_hpo(
2739 p_admission_cat IN VARCHAR2 ,
2740 p_hecs_payment_option IN VARCHAR2 ,
2741 p_message_name OUT NOCOPY VARCHAR2 )
2742 RETURN BOOLEAN AS
2743 BEGIN -- admp_val_acai_hpo
2744 -- Validate the admission course application instance HECS Payment Option.
2745 DECLARE
2746 v_message_name VARCHAR2(30);
2747 v_achpo_exist VARCHAR2(1);
2748 CURSOR c_achpo IS
2749 SELECT 'x'
2750 FROM IGS_AD_CT_HECS_PAYOP
2751 WHERE admission_cat = p_admission_cat AND
2752 HECS_PAYMENT_OPTION = p_hecs_payment_option;
2753 BEGIN
2754 -- Set the default message number
2755 p_message_name := NULL;
2756 IF (p_hecs_payment_option IS NOT NULL) THEN
2757 -- Validate that HECS Payment Option is not closed.
2758 IF (igs_en_val_scho.enrp_val_hpo_closed(
2759 p_hecs_payment_option,
2760 v_message_name) = FALSE) THEN
2761 p_message_name := v_message_name;
2762 RETURN FALSE;
2763 END IF;
2764 -- Validate that HECS Payment Option has a mapping to the admission category
2765 OPEN c_achpo;
2766 FETCH c_achpo INTO v_achpo_exist;
2767 IF c_achpo%NOTFOUND THEN
2768 CLOSE c_achpo;
2769 p_message_name := 'IGS_AD_HECS_PRMT_INVALID';
2770 RETURN FALSE;
2771 END IF;
2772 CLOSE c_achpo;
2773 END IF;
2774 RETURN TRUE;
2775 END;
2776 EXCEPTION
2777 WHEN OTHERS THEN
2778 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2779 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_hpo');
2780 IGS_GE_MSG_STACK.ADD;
2781 APP_EXCEPTION.RAISE_EXCEPTION;
2782 END admp_val_acai_hpo;
2783 --
2784 --
2785 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status_auth_dt.
2786 FUNCTION admp_val_ovrd_dt(
2787 p_adm_outcome_status_auth_dt IN DATE ,
2788 p_override_outcome_allowed IN VARCHAR2 ,
2789 p_message_name OUT NOCOPY VARCHAR2 )
2790 RETURN BOOLEAN AS
2791 BEGIN -- admp_val_ovrd_dt
2792 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status_auth_dt so that
2793 -- * The admission outcome status authorising date must be null if the outcome
2794 -- status cannot be overridden for the admission process category of the
2795 -- admission application.
2796 -- * If set, the admission outcome status authorising date must be less than or
2797 -- equal to the current date.
2798 DECLARE
2799 BEGIN
2800 -- Set the default message number
2801 p_message_name := NULL;
2802 -- Check if the admission outcome status can be overridden
2803 IF p_override_outcome_allowed = 'N' AND
2804 p_adm_outcome_status_auth_dt IS NOT NULL THEN
2805 p_message_name := 'IGS_AD_ATHDT_SET_ADMOUTSOME';
2806 RETURN FALSE;
2807 END IF;
2808 -- Check if the overriding date is valid
2809 IF p_adm_outcome_status_auth_dt IS NOT NULL THEN
2810 IF p_adm_outcome_status_auth_dt > SYSDATE THEN
2811 p_message_name := 'IGS_AD_ATHDT_LE_CURDT';
2812 RETURN FALSE;
2813 END IF;
2814 END IF;
2815 RETURN TRUE;
2816 END;
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2820 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_ovrd_dt');
2821 IGS_GE_MSG_STACK.ADD;
2822 APP_EXCEPTION.RAISE_EXCEPTION;
2823 END admp_val_ovrd_dt;
2824 --
2825 -- Validate the IGS_AD_PS_APPL_INST.adm_otcm_status_auth_person_id.
2826 FUNCTION admp_val_ovrd_person(
2827 p_adm_otcm_status_auth_person IN NUMBER ,
2828 p_adm_outcome_status_auth_dt IN DATE ,
2829 p_override_outcome_allowed IN VARCHAR2 ,
2830 p_message_name OUT NOCOPY VARCHAR2 )
2831 RETURN BOOLEAN AS
2832 BEGIN -- admp_val_ovrd_person
2833 -- Validate the following :
2834 -- 1) The admission outcome status authorising IGS_PE_PERSON ID must be null if the
2835 -- outcome
2836 -- status cannot be overridden for the admission process category of the
2837 -- admission application
2838 -- 2) The admission outcome status authorising IGS_PE_PERSON ID must be null if the
2839 -- admission outcome
2840 -- status authorising date is not set
2841 -- 3) The admission outcome status authorising IGS_PE_PERSON ID must be set if the
2842 -- admission outcome
2843 -- status authorising date is set
2844 -- 4) If set, the admission outcome status authorising IGS_PE_PERSON ID must be a
2845 -- staff member.
2846 DECLARE
2847 v_message_name VARCHAR2(30);
2848 BEGIN
2849 -- Set the default message number
2850 p_message_name := NULL;
2851 -- Check if the admission outcome status can be overridden
2852 IF p_override_outcome_allowed = 'N' AND
2853 p_adm_otcm_status_auth_person IS NOT NULL THEN
2854 p_message_name := 'IGS_AD_AUTHORISING_PRSNID';
2855 RETURN FALSE;
2856 END IF;
2857 IF p_adm_outcome_status_auth_dt IS NULL AND
2858 p_adm_otcm_status_auth_person IS NOT NULL THEN
2859 p_message_name := 'IGS_AD_ARTH_PRSNID_SET';
2860 RETURN FALSE;
2861 END IF;
2862 -- Check if the admission outcome status should be set.
2863 IF p_adm_outcome_status_auth_dt IS NOT NULL AND
2864 p_adm_otcm_status_auth_person IS NULL THEN
2865 p_message_name := 'IGS_AD_ARTH_PRSNID_SET_ARTHDT' ;
2866 RETURN FALSE;
2867 END IF;
2868 -- Check if the overriding IGS_PE_PERSON ID is a staff member.
2869 IF p_adm_otcm_status_auth_person IS NOT NULL THEN
2870 IF IGS_AD_VAL_ACAI.genp_val_staff_prsn (
2871 p_adm_otcm_status_auth_person,
2872 v_message_name) = FALSE THEN
2873 p_message_name := v_message_name;
2874 RETURN FALSE;
2875 END IF;
2876 END IF;
2877 RETURN TRUE;
2878 END;
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
2882 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_ovrd_person');
2883 IGS_GE_MSG_STACK.ADD;
2884 APP_EXCEPTION.RAISE_EXCEPTION;
2885 END admp_val_ovrd_person;
2886 --
2887 -- Validate a IGS_PE_PERSON id to ensure the IGS_PE_PERSON is a staff member.
2888 FUNCTION genp_val_staff_prsn(
2889 p_person_id IN NUMBER ,
2890 p_message_name OUT NOCOPY VARCHAR2 )
2891 RETURN BOOLEAN AS
2892 BEGIN
2893 DECLARE
2894 CURSOR pe_typ_cd IS
2895 SELECT pti.person_type_code
2896 FROM igs_pe_person_types pt,igs_pe_typ_instances pti
2897 WHERE pti.person_id = p_person_id
2898 AND pti.person_type_code = pt.person_type_code
2899 AND pt.system_type = 'STAFF'
2900 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
2901 lv_pe_typ_cd pe_typ_cd%RowType;
2902
2903 BEGIN
2904 OPEN pe_typ_cd;
2905 FETCH pe_typ_cd INTO lv_pe_typ_cd;
2906 IF (pe_typ_cd%FOUND) THEN
2907 CLOSE pe_typ_cd;
2908 p_message_name := NULL;
2909 RETURN TRUE;
2910 ELSE
2911 CLOSE pe_typ_cd;
2912 p_message_name := 'IGS_GE_NOT_STAFF_MEMBER';
2913 RETURN FALSE;
2914 END IF;
2915 END;
2916 END genp_val_staff_prsn;
2917 --
2918 -- Validate a IGS_PE_PERSON id to ensure the IGS_PE_PERSON is a staff/Faculty member.
2919 FUNCTION genp_val_staff_fculty_prsn(
2920 p_person_id IN NUMBER ,
2921 p_message_name OUT NOCOPY VARCHAR2 )
2922 RETURN BOOLEAN AS
2923
2924 CURSOR pe_typ_cd IS
2925 SELECT pti.person_type_code
2926 FROM igs_pe_person_types pt,igs_pe_typ_instances pti
2927 WHERE pti.person_id = p_person_id
2928 AND pti.person_type_code = pt.person_type_code
2929 AND pt.system_type IN ('STAFF', 'FACULTY')
2930 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
2931 lv_pe_typ_cd pe_typ_cd%RowType;
2932
2933 CURSOR c_dmi IS
2934 SELECT 'X'
2935 FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
2936 WHERE base.person_id = p_person_id
2937 AND base.person_id = pd.party_id (+)
2938 AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
2939
2940 l_deceased igs_pe_person.person_id%TYPE := NULL;
2941
2942 BEGIN
2943
2944 OPEN pe_typ_cd;
2945 FETCH pe_typ_cd INTO lv_pe_typ_cd;
2946 CLOSE pe_typ_cd;
2947
2948 OPEN c_dmi;
2949 FETCH c_dmi INTO l_deceased;
2950 CLOSE c_dmi;
2951
2952
2953 IF lv_pe_typ_cd.person_type_code IS NULL OR l_deceased IS NOT NULL
2954 THEN
2955 p_message_name := 'IGS_AD_NOT_STAF_FAC_MEMBER';
2956 RETURN FALSE;
2957 END IF;
2958
2959 RETURN TRUE;
2960 END genp_val_staff_fculty_prsn;
2961
2962 -- Validate a IGS_PE_PERSON id to ensure the IGS_PE_PERSON is a staff/Faculty OR Evaluator member.
2963 FUNCTION genp_val_staff_fac_eva_prsn(
2964 p_person_id IN NUMBER ,
2965 p_message_name OUT NOCOPY VARCHAR2 )
2966 RETURN BOOLEAN AS
2967
2968 CURSOR pe_typ_cd IS
2969 SELECT pti.person_type_code
2970 FROM igs_pe_person_types pt,igs_pe_typ_instances pti
2971 WHERE pti.person_id = p_person_id
2972 AND pti.person_type_code = pt.person_type_code
2973 AND pt.system_type IN ('STAFF', 'FACULTY' , 'EVALUATOR')
2974 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
2975 lv_pe_typ_cd pe_typ_cd%RowType;
2976
2977 CURSOR c_dmi IS
2978 SELECT 'X'
2979 FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
2980 WHERE base.person_id = p_person_id
2981 AND base.person_id = pd.party_id (+)
2982 AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
2983
2984 l_deceased igs_pe_person.person_id%TYPE := NULL;
2985
2986 BEGIN
2987
2988 OPEN pe_typ_cd;
2989 FETCH pe_typ_cd INTO lv_pe_typ_cd;
2990 CLOSE pe_typ_cd;
2991
2992 OPEN c_dmi;
2993 FETCH c_dmi INTO l_deceased;
2994 CLOSE c_dmi;
2995
2996
2997 IF lv_pe_typ_cd.person_type_code IS NULL OR l_deceased IS NOT NULL
2998 THEN
2999 p_message_name := 'IGS_AD_NOT_STAF_FAC_EVA_MEMBER';
3000 RETURN FALSE;
3001 END IF;
3002
3003 RETURN TRUE;
3004 END genp_val_staff_fac_eva_prsn;
3005
3006 --
3007 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status_reason.
3008 FUNCTION admp_val_ovrd_reason(
3009 p_adm_outcome_status_reason IN VARCHAR2 ,
3010 p_adm_outcome_status_auth_dt IN DATE ,
3011 p_override_outcome_allowed IN VARCHAR2 ,
3012 p_message_name OUT NOCOPY VARCHAR2 )
3013 RETURN BOOLEAN AS
3014 BEGIN -- admp_val_ovrd_reason
3015 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status_reason so that
3016 -- * The admission outcome status reason must be null if the outcome
3017 -- status cannot be overridden for the admission process category of the
3018 -- admission application.
3019 -- * The admission outcome status reason must be null if the admission outcome
3020 -- status authorising date is not set
3021 DECLARE
3022 BEGIN
3023 p_message_name := NULL;
3024 IF (p_override_outcome_allowed = 'N' AND
3025 p_adm_outcome_status_reason IS NOT NULL) THEN
3026 p_message_name := 'IGS_AD_REASON_SET_ADMOUTCOME';
3027 RETURN FALSE;
3028 END IF;
3029 IF (p_adm_outcome_status_auth_dt IS NULL AND
3030 p_adm_outcome_status_reason IS NOT NULL) THEN
3031 p_message_name := 'IGS_AD_REASON_SET_ARTHDT';
3032 RETURN FALSE;
3033 END IF;
3034 RETURN TRUE;
3035 END;
3036 EXCEPTION
3037 WHEN OTHERS THEN
3038 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
3039 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_ovrd_reason');
3040 IGS_GE_MSG_STACK.ADD;
3041 APP_EXCEPTION.RAISE_EXCEPTION;
3042 END admp_val_ovrd_reason;
3043 --
3044 -- Validate that the course application is complete on offer.
3045 FUNCTION admp_val_offer_comp(
3046 p_person_id IN NUMBER ,
3047 p_admission_appl_number IN NUMBER ,
3048 p_nominated_course_cd IN VARCHAR2 ,
3049 p_acai_sequence_number IN NUMBER ,
3050 p_course_cd IN VARCHAR2 ,
3051 p_crv_version_number IN NUMBER ,
3052 p_admission_cat IN VARCHAR2 ,
3053 p_s_admission_process_type IN VARCHAR2 ,
3054 p_effective_dt IN DATE ,
3055 p_called_from IN VARCHAR2 ,
3056 p_message_name OUT NOCOPY VARCHAR2 )
3057 RETURN BOOLEAN AS
3058 BEGIN -- admp_val_offer_comp
3059 -- Validate that the course application is complete on offer
3060 DECLARE
3061 v_message_name VARCHAR2(30);
3062 v_mandatory_athletics BOOLEAN;
3063 v_mandatory_alternate BOOLEAN;
3064 v_mandatory_address BOOLEAN;
3065 v_mandatory_disability BOOLEAN;
3066 v_mandatory_visa BOOLEAN;
3067 v_mandatory_finance BOOLEAN;
3068 v_mandatory_notes BOOLEAN;
3069 v_mandatory_statistics BOOLEAN;
3070 v_mandatory_alias BOOLEAN;
3071 v_mandatory_tertiary BOOLEAN;
3072 v_mandatory_aus_sec_ed BOOLEAN;
3073 v_mandatory_os_sec_ed BOOLEAN;
3074 v_mandatory_employment BOOLEAN;
3075 v_mandatory_membership BOOLEAN;
3076 v_mandatory_dob BOOLEAN;
3077 v_mandatory_title BOOLEAN;
3078 v_mandatory_referee BOOLEAN;
3079 v_mandatory_scholarship BOOLEAN;
3080 v_mandatory_lang_prof BOOLEAN;
3081 v_mandatory_interview BOOLEAN;
3082 v_mandatory_exchange BOOLEAN;
3083 v_mandatory_adm_test BOOLEAN;
3084 v_mandatory_fee_assess BOOLEAN;
3085 v_mandatory_cor_category BOOLEAN;
3086 v_mandatory_enr_category BOOLEAN;
3087 v_mandatory_research BOOLEAN;
3088 v_mandatory_rank_app BOOLEAN;
3089 v_mandatory_completion BOOLEAN;
3090 v_mandatory_rank_set BOOLEAN;
3091 v_mandatory_basis_adm BOOLEAN;
3092 v_mandatory_crs_international BOOLEAN;
3093 v_mandatory_ass_tracking BOOLEAN;
3094 v_mandatory_adm_code BOOLEAN;
3095 v_mandatory_fund_source BOOLEAN;
3096 v_mandatory_location BOOLEAN;
3097 v_mandatory_att_mode BOOLEAN;
3098 v_mandatory_att_type BOOLEAN;
3099 v_mandatory_unit_set BOOLEAN;
3100 v_valid_athletics BOOLEAN;
3101 v_valid_alternate BOOLEAN;
3102 v_valid_address BOOLEAN;
3103 v_valid_disability BOOLEAN;
3104 v_valid_visa BOOLEAN;
3105 v_valid_finance BOOLEAN;
3106 v_valid_notes BOOLEAN;
3107 v_valid_statistics BOOLEAN;
3108 v_valid_alias BOOLEAN;
3109 v_valid_tertiary BOOLEAN;
3110 v_valid_aus_sec_ed BOOLEAN;
3111 v_valid_os_sec_ed BOOLEAN;
3112 v_valid_employment BOOLEAN;
3113 v_valid_membership BOOLEAN;
3114 v_valid_dob BOOLEAN;
3115 v_valid_title BOOLEAN;
3116 v_valid_referee BOOLEAN;
3117 v_valid_scholarship BOOLEAN;
3118 v_valid_lang_prof BOOLEAN;
3119 v_valid_interview BOOLEAN;
3120 v_valid_exchange BOOLEAN;
3121 v_valid_adm_test BOOLEAN;
3122 v_valid_fee_assess BOOLEAN;
3123 v_valid_cor_category BOOLEAN;
3124 v_valid_enr_category BOOLEAN;
3125 v_valid_research BOOLEAN;
3126 v_valid_rank_app BOOLEAN;
3127 v_valid_completion BOOLEAN;
3128 v_valid_rank_set BOOLEAN;
3129 v_valid_basis_adm BOOLEAN;
3130 v_valid_crs_international BOOLEAN;
3131 v_valid_ass_tracking BOOLEAN;
3132 v_valid_adm_code BOOLEAN;
3133 v_valid_fund_source BOOLEAN;
3134 v_valid_location BOOLEAN;
3135 v_valid_att_mode BOOLEAN;
3136 v_valid_att_type BOOLEAN;
3137 v_valid_unit_set BOOLEAN;
3138 v_valid_extrcurr BOOLEAN;
3139 v_mandatory_evaluation_tab BOOLEAN;
3140 v_mandatory_prog_approval BOOLEAN;
3141 v_mandatory_indices BOOLEAN;
3142 v_mandatory_tst_scores BOOLEAN;
3143 v_mandatory_outcome BOOLEAN ;
3144 v_mandatory_override BOOLEAN ;
3145 v_mandatory_spl_consider BOOLEAN ;
3146 v_mandatory_cond_offer BOOLEAN ;
3147 v_mandatory_offer_dead BOOLEAN ;
3148 v_mandatory_offer_resp BOOLEAN ;
3149 v_mandatory_offer_defer BOOLEAN ;
3150 v_mandatory_offer_compl BOOLEAN ;
3151 v_mandatory_transfer BOOLEAN ;
3152 v_mandatory_other_inst BOOLEAN ;
3153 v_mandatory_edu_goals BOOLEAN ;
3154 v_mandatory_acad_interest BOOLEAN ;
3155 v_mandatory_app_intent BOOLEAN ;
3156 v_mandatory_spl_interest BOOLEAN ;
3157 v_mandatory_spl_talents BOOLEAN ;
3158 v_mandatory_miscell BOOLEAN ;
3159 v_mandatory_fees BOOLEAN ;
3160 v_mandatory_program BOOLEAN ;
3161 v_mandatory_completness BOOLEAN ;
3162 v_mandatory_creden BOOLEAN ;
3163 v_mandatory_review_det BOOLEAN ;
3164 v_mandatory_recomm_det BOOLEAN ;
3165 v_mandatory_fin_aid BOOLEAN ;
3166 v_mandatory_acad_honors BOOLEAN ;
3167 v_mandatory_des_unitsets BOOLEAN ; -- added for 2382599
3168 v_mandatory_extrcurr BOOLEAN;
3169
3170
3171 BEGIN
3172 --set the default message number
3173 p_message_name := NULL;
3174 --get the mandatory admission steps
3175 IGS_AD_GEN_003.ADMP_GET_APCS_MNDTRY(
3176 p_admission_cat,
3177 p_s_admission_process_type,
3178 v_mandatory_athletics,
3179 v_mandatory_alternate,
3180 v_mandatory_address,
3181 v_mandatory_disability,
3182 v_mandatory_visa,
3183 v_mandatory_finance,
3184 v_mandatory_notes,
3185 v_mandatory_statistics,
3186 v_mandatory_alias,
3187 v_mandatory_tertiary,
3188 v_mandatory_aus_sec_ed,
3189 v_mandatory_os_sec_ed,
3190 v_mandatory_employment,
3191 v_mandatory_membership,
3192 v_mandatory_dob,
3193 v_mandatory_title,
3194 v_mandatory_referee,
3195 v_mandatory_scholarship,
3196 v_mandatory_lang_prof,
3197 v_mandatory_interview,
3198 v_mandatory_exchange,
3199 v_mandatory_adm_test,
3200 v_mandatory_fee_assess,
3201 v_mandatory_cor_category,
3202 v_mandatory_enr_category,
3203 v_mandatory_research,
3204 v_mandatory_rank_app,
3205 v_mandatory_completion,
3206 v_mandatory_rank_set,
3207 v_mandatory_basis_adm,
3208 v_mandatory_crs_international,
3209 v_mandatory_ass_tracking,
3210 v_mandatory_adm_code,
3211 v_mandatory_fund_source,
3212 v_mandatory_location,
3213 v_mandatory_att_mode,
3214 v_mandatory_att_type,
3215 v_mandatory_unit_set,
3216 v_mandatory_evaluation_tab,
3217 v_mandatory_prog_approval ,
3218 v_mandatory_indices ,
3219 v_mandatory_tst_scores ,
3220 v_mandatory_outcome ,
3221 v_mandatory_override ,
3222 v_mandatory_spl_consider ,
3223 v_mandatory_cond_offer ,
3224 v_mandatory_offer_dead ,
3225 v_mandatory_offer_resp ,
3226 v_mandatory_offer_defer ,
3227 v_mandatory_offer_compl ,
3228 v_mandatory_transfer ,
3229 v_mandatory_other_inst ,
3230 v_mandatory_edu_goals ,
3231 v_mandatory_acad_interest ,
3232 v_mandatory_app_intent ,
3233 v_mandatory_spl_interest ,
3234 v_mandatory_spl_talents ,
3235 v_mandatory_miscell ,
3236 v_mandatory_fees ,
3237 v_mandatory_program ,
3238 v_mandatory_completness ,
3239 v_mandatory_creden ,
3240 v_mandatory_review_det ,
3241 v_mandatory_recomm_det ,
3242 v_mandatory_fin_aid ,
3243 v_mandatory_acad_honors ,
3244 v_mandatory_des_unitsets,
3245 v_mandatory_extrcurr);
3246
3247 --validate if the admission application IGS_PE_PERSON details are complete
3248 IF (IGS_AD_VAL_ACAI.admp_val_pe_comp(
3249 p_person_id,
3250 p_effective_dt,
3251 v_mandatory_athletics,
3252 v_mandatory_alternate,
3253 v_mandatory_address,
3254 v_mandatory_disability,
3255 v_mandatory_visa,
3256 v_mandatory_finance,
3257 v_mandatory_notes,
3258 v_mandatory_statistics,
3259 v_mandatory_alias,
3260 v_mandatory_tertiary,
3261 v_mandatory_aus_sec_ed,
3262 v_mandatory_os_sec_ed,
3263 v_mandatory_employment,
3264 v_mandatory_membership,
3265 v_mandatory_dob,
3266 v_mandatory_title,
3267 v_mandatory_extrcurr,
3268 v_message_name,
3269 v_valid_athletics,
3270 v_valid_alternate,
3271 v_valid_address,
3272 v_valid_disability,
3273 v_valid_visa,
3274 v_valid_finance,
3275 v_valid_notes,
3276 v_valid_statistics,
3277 v_valid_alias,
3278 v_valid_tertiary,
3279 v_valid_aus_sec_ed,
3280 v_valid_os_sec_ed,
3281 v_valid_employment,
3282 v_valid_membership,
3283 v_valid_dob,
3284 v_valid_title,
3285 v_valid_extrcurr ) = FALSE) THEN
3286 p_message_name := v_message_name;
3287 RETURN FALSE;
3288 END IF;
3289 --Validate if the admission application course details are complete
3290 -- Only peform this validation if not called from the form.
3291 IF p_called_from <> 'FORM' THEN
3292 IF (IGS_AD_VAL_ACAI.admp_val_acai_comp(
3293 p_person_id,
3294 p_admission_appl_number,
3295 p_nominated_course_cd,
3296 p_acai_sequence_number,
3297 p_course_cd,
3298 p_crv_version_number,
3299 p_s_admission_process_type,
3300 p_effective_dt,
3301 v_mandatory_referee,
3302 v_mandatory_scholarship,
3303 v_mandatory_lang_prof,
3304 v_mandatory_interview,
3305 v_mandatory_exchange,
3306 v_mandatory_adm_test,
3307 v_mandatory_fee_assess,
3308 v_mandatory_cor_category,
3309 v_mandatory_enr_category,
3310 v_mandatory_research,
3311 v_mandatory_rank_app,
3312 v_mandatory_completion,
3313 v_mandatory_rank_set,
3314 v_mandatory_basis_adm,
3315 v_mandatory_crs_international,
3316 v_mandatory_ass_tracking,
3317 v_mandatory_adm_code,
3318 v_mandatory_fund_source,
3319 v_mandatory_location,
3320 v_mandatory_att_mode,
3321 v_mandatory_att_type,
3322 v_mandatory_unit_set,
3323 v_message_name,
3324 v_valid_referee,
3325 v_valid_scholarship,
3326 v_valid_lang_prof,
3327 v_valid_interview,
3328 v_valid_exchange,
3329 v_valid_adm_test,
3330 v_valid_fee_assess,
3331 v_valid_cor_category,
3332 v_valid_enr_category,
3333 v_valid_research,
3334 v_valid_rank_app,
3335 v_valid_completion,
3336 v_valid_rank_set,
3337 v_valid_basis_adm,
3338 v_valid_crs_international,
3339 v_valid_ass_tracking,
3340 v_valid_adm_code,
3341 v_valid_fund_source,
3342 v_valid_location,
3343 v_valid_att_mode,
3344 v_valid_att_type,
3345 v_valid_unit_set) = FALSE) THEN
3346 p_message_name := v_message_name;
3347 RETURN FALSE;
3348 END IF;
3349 END IF;
3350 RETURN TRUE;
3351 END;
3352 EXCEPTION
3353 WHEN OTHERS THEN
3354 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
3355 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_offer_comp');
3356 IGS_GE_MSG_STACK.ADD;
3357 APP_EXCEPTION.RAISE_EXCEPTION;
3358 END admp_val_offer_comp;
3359 --
3360 -- Validate if the specified IGS_AD_PS_APPL_INST is complete.
3361 FUNCTION admp_val_acai_comp(
3362 p_person_id IN NUMBER ,
3363 p_admission_appl_number IN NUMBER ,
3364 p_nominated_course_cd IN VARCHAR2 ,
3365 p_acai_sequence_number IN NUMBER ,
3366 p_course_cd IN VARCHAR2 ,
3367 p_crv_version_number IN NUMBER ,
3368 p_s_admission_process_type IN VARCHAR2 ,
3369 p_effective_dt IN DATE ,
3370 p_check_referee IN BOOLEAN ,
3371 p_check_scholarship IN BOOLEAN ,
3372 p_check_lang_prof IN BOOLEAN ,
3373 p_check_interview IN BOOLEAN ,
3374 p_check_exchange IN BOOLEAN ,
3375 p_check_adm_test IN BOOLEAN ,
3376 p_check_fee_assess IN BOOLEAN ,
3377 p_check_cor_category IN BOOLEAN ,
3378 p_check_enr_category IN BOOLEAN ,
3379 p_check_research IN BOOLEAN ,
3380 p_check_rank_app IN BOOLEAN ,
3381 p_check_completion IN BOOLEAN ,
3382 p_check_rank_set IN BOOLEAN ,
3383 p_check_basis_adm IN BOOLEAN ,
3384 p_check_crs_international IN BOOLEAN ,
3385 p_check_ass_tracking IN BOOLEAN ,
3386 p_check_adm_code IN BOOLEAN ,
3387 p_check_fund_source IN BOOLEAN ,
3388 p_check_location IN BOOLEAN ,
3389 p_check_att_mode IN BOOLEAN ,
3390 p_check_att_type IN BOOLEAN ,
3391 p_check_unit_set IN BOOLEAN ,
3392 p_message_name OUT NOCOPY VARCHAR2 ,
3393 p_valid_referee OUT NOCOPY BOOLEAN ,
3394 p_valid_scholarship OUT NOCOPY BOOLEAN ,
3395 p_valid_lang_prof OUT NOCOPY BOOLEAN ,
3396 p_valid_interview OUT NOCOPY BOOLEAN ,
3397 p_valid_exchange OUT NOCOPY BOOLEAN ,
3398 p_valid_adm_test OUT NOCOPY BOOLEAN ,
3399 p_valid_fee_assess OUT NOCOPY BOOLEAN ,
3400 p_valid_cor_category OUT NOCOPY BOOLEAN ,
3401 p_valid_enr_category OUT NOCOPY BOOLEAN ,
3402 p_valid_research OUT NOCOPY BOOLEAN ,
3403 p_valid_rank_app OUT NOCOPY BOOLEAN ,
3404 p_valid_completion OUT NOCOPY BOOLEAN ,
3405 p_valid_rank_set OUT NOCOPY BOOLEAN ,
3406 p_valid_basis_adm OUT NOCOPY BOOLEAN ,
3407 p_valid_crs_international OUT NOCOPY BOOLEAN ,
3408 p_valid_ass_tracking OUT NOCOPY BOOLEAN ,
3409 p_valid_adm_code OUT NOCOPY BOOLEAN ,
3410 p_valid_fund_source OUT NOCOPY BOOLEAN ,
3411 p_valid_location OUT NOCOPY BOOLEAN ,
3412 p_valid_att_mode OUT NOCOPY BOOLEAN ,
3413 p_valid_att_type OUT NOCOPY BOOLEAN ,
3414 p_valid_unit_set OUT NOCOPY BOOLEAN )
3415 RETURN BOOLEAN AS
3416 -------------------------------------------------------------------------------------------
3417 --Change History:
3418 --Who When What
3419 --samaresh 21-DEC-2001 Bug No. 2158524 . The cursor c_ar has been modified as the
3420 -- the table IGS_AD_APP_ REQ has been moved from application
3421 -- instance level to application level
3422 -- ssawhney 24-oct-2002 SWS104 build 2630860, AD_ACAD_HONOR reference moved to PE.
3423 -------------------------------------------------------------------------------------------
3424
3425 BEGIN -- admp_val_acai_comp
3426 -- Validate if the specified IGS_AD_PS_APPL_INST is complete
3427 DECLARE
3428 cst_re_admit VARCHAR2(10) := 'RE-ADMIT';
3429 v_check_research BOOLEAN := FALSE;
3430 v_valid_referee BOOLEAN := FALSE;
3431 v_valid_scholarship BOOLEAN := FALSE;
3432 v_valid_lang_prof BOOLEAN := FALSE;
3433 v_valid_interview BOOLEAN := FALSE;
3434 v_valid_exchange BOOLEAN := FALSE;
3435 v_valid_adm_test BOOLEAN := FALSE;
3436 v_valid_fee_assess BOOLEAN := FALSE;
3437 v_valid_cor_category BOOLEAN := FALSE;
3438 v_valid_enr_category BOOLEAN := FALSE;
3439 v_valid_research BOOLEAN := FALSE;
3440 v_valid_rank_app BOOLEAN := FALSE;
3441 v_valid_completion BOOLEAN := FALSE;
3442 v_valid_rank_set BOOLEAN := FALSE;
3443 v_valid_basis_adm BOOLEAN := FALSE;
3444 v_valid_crs_international BOOLEAN := FALSE;
3445 v_valid_ass_tracking BOOLEAN := FALSE;
3446 v_valid_adm_code BOOLEAN := FALSE;
3447 v_valid_fund_source BOOLEAN := FALSE;
3448 v_valid_location BOOLEAN := FALSE;
3449 v_valid_att_mode BOOLEAN := FALSE;
3450 v_valid_att_type BOOLEAN := FALSE;
3451 v_valid_unit_set BOOLEAN := FALSE;
3452 v_location_cd IGS_AD_PS_APPL_INST_APLINST_V.location_cd%TYPE;
3453 v_attendance_mode IGS_AD_PS_APPL_INST_APLINST_V.attendance_mode%TYPE;
3454 v_attendance_type IGS_AD_PS_APPL_INST_APLINST_V.attendance_type%TYPE;
3455 v_unit_set_cd IGS_AD_PS_APPL_INST_APLINST_V.unit_set_cd%TYPE;
3456 v_us_version_number IGS_AD_PS_APPL_INST_APLINST_V.us_version_number%TYPE;
3457 v_ass_rank IGS_AD_PS_APPL_INST_APLINST_V.ass_rank%TYPE;
3458 v_intrntnl_accptnce_advice_num
3459 IGS_AD_PS_APPL_INST_APLINST_V.intrntnl_acceptance_advice_num%TYPE;
3460 v_ass_tracking_id IGS_AD_PS_APPL_INST_APLINST_V.ass_tracking_id%TYPE;
3461 v_fee_cat IGS_AD_PS_APPL_INST_APLINST_V.fee_cat%TYPE;
3462 v_expected_completion_yr IGS_AD_PS_APPL_INST_APLINST_V.expected_completion_yr%TYPE;
3463 v_expected_completion_perd
3464 IGS_AD_PS_APPL_INST_APLINST_V.expected_completion_perd%TYPE;
3465 v_correspondence_cat IGS_AD_PS_APPL_INST_APLINST_V.correspondence_cat%TYPE;
3466 v_enrolment_cat IGS_AD_PS_APPL_INST_APLINST_V.enrolment_cat%TYPE;
3467 v_funding_source IGS_AD_PS_APPL_INST_APLINST_V.funding_source%TYPE;
3468 v_basis_for_admission_type
3469 IGS_AD_PS_APPL_INST_APLINST_V.basis_for_admission_type%TYPE;
3470 v_admission_cd IGS_AD_PS_APPL_INST_APLINST_V.admission_cd%TYPE;
3471 v_course_rank_set IGS_AD_PS_APPL_INST_APLINST_V.course_rank_set%TYPE;
3472 v_course_rank_schedule IGS_AD_PS_APPL_INST_APLINST_V.course_rank_schedule%TYPE;
3473 v_sca_fee_cat IGS_EN_STDNT_PS_ATT.fee_cat%TYPE;
3474 v_sca_funding_source IGS_EN_STDNT_PS_ATT.funding_source%TYPE;
3475 v_sca_correspondence_cat IGS_EN_STDNT_PS_ATT.correspondence_cat%TYPE;
3476 v_scae_enrolment_cat IGS_AS_SC_ATMPT_ENR.enrolment_cat%TYPE;
3477 v_dummy VARCHAR2(1);
3478 v_error_message_research VARCHAR2(30);
3479
3480 CURSOR c_acaiv IS
3481 SELECT acaiv.location_cd,
3482 acaiv.attendance_mode,
3483 acaiv.attendance_type,
3484 acaiv.unit_set_cd,
3485 acaiv.us_version_number,
3486 acaiv.ass_rank,
3487 acaiv.intrntnl_acceptance_advice_num,
3488 acaiv.ass_tracking_id,
3489 acaiv.fee_cat,
3490 acaiv.expected_completion_yr,
3491 acaiv.expected_completion_perd,
3492 acaiv.correspondence_cat,
3493 acaiv.enrolment_cat,
3494 acaiv.funding_source,
3495 acav.basis_for_admission_type,
3496 acav.admission_cd,
3497 acav.course_rank_set,
3498 acav.course_rank_schedule
3499 FROM IGS_AD_PS_APPL_INST acaiv,
3500 IGS_AD_PS_APPL acav
3501 WHERE acaiv.person_id = p_person_id AND
3502 acaiv.admission_appl_number = p_admission_appl_number AND
3503 acaiv.nominated_course_cd = p_nominated_course_cd AND
3504 acaiv.sequence_number = p_acai_sequence_number AND
3505 acav.person_id = acaiv.person_id AND
3506 acav.admission_appl_number = acaiv.admission_appl_number AND
3507 acav.nominated_course_cd = acav.nominated_course_cd;
3508 CURSOR c_sca IS
3509 SELECT sca.fee_cat,
3510 sca.correspondence_cat,
3511 sca.funding_source
3512 FROM IGS_EN_STDNT_PS_ATT sca
3513 WHERE sca.person_id = p_person_id AND
3514 sca.course_cd = p_course_cd;
3515 CURSOR c_scae IS
3516 SELECT scae.enrolment_cat
3517 FROM IGS_AS_SC_ATMPT_ENR scae
3518 WHERE scae.person_id = p_person_id AND
3519 scae.course_cd = p_course_cd;
3520 CURSOR c_crv IS
3521 SELECT 'x'
3522 FROM IGS_PS_VER crv,
3523 IGS_PS_TYPE cty
3524 WHERE crv.course_cd = p_course_cd AND
3525 crv.version_number = p_crv_version_number AND
3526 cty.course_type = crv.course_type AND
3527 cty.research_type_ind = 'Y';
3528
3529 v_return BOOLEAN := TRUE;
3530 v_person_id igs_pe_person_v.person_id%TYPE;
3531 CURSOR c_apcs IS
3532 SELECT apcs.s_admission_step_type
3533 FROM IGS_AD_PRCS_CAT_STEP apcs
3534 WHERE admission_cat= (SELECT admission_cat
3535 FROM IGS_AD_APPL /* Replaced IGS_AD_APPL_ADMAPPL_V with IGS_AD_APPL Bug 3150054 */
3536 WHERE person_id = p_person_id AND
3537 admission_appl_number=p_admission_appl_number) AND
3538 s_admission_process_type = p_s_admission_process_type AND
3539 mandatory_step_ind = 'Y' AND
3540 step_group_type <> 'TRACK';
3541
3542 CURSOR c_ae IS
3543 SELECT person_id
3544 FROM IGS_ad_appl_eval_v
3545 WHERE person_id = p_person_id AND
3546 admission_appl_number = p_admission_appl_number AND
3547 nominated_course_cd = p_nominated_course_cd;
3548 CURSOR c_pa IS
3549 SELECT person_id
3550 FROM igs_Ad_appl_pgmapprv_v
3551 WHERE person_id = p_person_id AND
3552 admission_appl_number = p_admission_appl_number AND
3553 nominated_course_cd = p_nominated_course_cd;
3554 CURSOR c_us IS
3555 SELECT person_id
3556 FROM igs_ad_unit_sets_v
3557 WHERE person_id = p_person_id AND
3558 admission_appl_number = p_admission_appl_number AND
3559 nominated_course_cd = p_nominated_course_cd;
3560 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3561 CURSOR c_oi IS
3562 SELECT person_id
3563 FROM igs_ad_other_inst
3564 WHERE person_id = p_person_id AND
3565 admission_appl_number = p_admission_appl_number;
3566 CURSOR c_eg IS
3567 SELECT person_id
3568 FROM igs_ad_edu_goal_v
3569 WHERE person_id = p_person_id AND
3570 admission_appl_number = p_admission_appl_number AND
3571 nominated_course_cd = p_nominated_course_cd;
3572 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3573 CURSOR c_ai IS
3574 SELECT person_id
3575 FROM igs_ad_acad_interest_v
3576 WHERE person_id = p_person_id AND
3577 admission_appl_number = p_admission_appl_number;
3578 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3579 CURSOR c_in IS
3580 SELECT person_id
3581 FROM igs_ad_app_intent_v
3582 WHERE person_id = p_person_id AND
3583 admission_appl_number = p_admission_appl_number;
3584 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3585 CURSOR c_si IS
3586 SELECT person_id
3587 FROM igs_ad_spl_interests_v
3588 WHERE person_id = p_person_id AND
3589 admission_appl_number = p_admission_appl_number;
3590 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3591 CURSOR c_st IS
3592 SELECT person_id
3593 FROM igs_ad_spl_talents_v
3594 WHERE person_id = p_person_id AND
3595 admission_appl_number = p_admission_appl_number;
3596 CURSOR c_ar IS
3597 SELECT person_id
3598 FROM igs_ad_app_req_v
3599 WHERE person_id = p_person_id AND
3600 admission_appl_number = p_admission_appl_number;
3601 -- Enh# 2177686, DAP Re-design. To revisit in Adimit to future term.
3602 CURSOR c_ac IS
3603 SELECT person_id
3604 FROM igs_pe_credentials
3605 WHERE person_id = p_person_id;
3606 -- Enh# 2177686, DAP Re-design. Removed the nominated_course_cd join.
3607 -- Enh# SWS104 build, 2630860 acad honors moved to PE
3608 CURSOR c_ah IS
3609 SELECT person_id
3610 FROM igs_pe_acad_honors_v
3611 WHERE person_id = p_person_id ;
3612 CURSOR c_md IS
3613 SELECT apply_for_finaid,
3614 app_file_location,
3615 late_adm_fee_status,
3616 enrolment_cat,
3617 fee_cat,
3618 hecs_payment_option,
3619 app_source_id,
3620 correspondence_cat,
3621 funding_source
3622 FROM igs_ad_ps_appl_inst
3623 WHERE person_id = p_person_id AND
3624 admission_appl_number = p_admission_appl_number AND
3625 nominated_course_cd = p_nominated_course_cd;
3626
3627 CURSOR c_id_exist IS
3628 SELECT person_id
3629 FROM igs_ad_panel_dtls
3630 WHERE person_id = p_person_id AND
3631 admission_appl_number = p_admission_appl_number AND
3632 nominated_course_cd = p_nominated_course_cd AND
3633 sequence_number = p_acai_sequence_number AND
3634 final_decision_type = 'FINAL_INTERVIEW';
3635 CURSOR c_id IS
3636 SELECT person_id
3637 FROM igs_ad_panel_dtls
3638 WHERE person_id = p_person_id AND
3639 admission_appl_number = p_admission_appl_number AND
3640 nominated_course_cd = p_nominated_course_cd AND
3641 sequence_number = p_acai_sequence_number AND
3642 final_decision_type = 'FINAL_INTERVIEW' AND
3643 EXISTS (SELECT 'x'
3644 FROM igs_ad_code_classes
3645 WHERE class = 'FINAL_INTERVIEW'
3646 AND name = final_decision_code
3647 AND system_status <> 'PENDING'
3648 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES');
3649
3650 v_app_file_location1 igs_ad_ps_appl_inst.app_file_location%TYPE;
3651 v_late_adm_fee_status1 igs_ad_ps_appl_inst.late_adm_fee_status%TYPE;
3652 v_enrolment_cat1 igs_ad_ps_appl_inst.enrolment_cat%TYPE;
3653 v_fee_cat1 igs_ad_ps_appl_inst.fee_cat%TYPE;
3654 v_hecs_payment_option1 igs_ad_ps_appl_inst.hecs_payment_option%TYPE;
3655 v_app_source_id1 igs_ad_ps_appl_inst.app_source_id%TYPE;
3656 v_correspondence_cat1 igs_ad_ps_appl_inst.correspondence_cat%TYPE;
3657 v_funding_source1 igs_ad_ps_appl_inst.funding_source%TYPE;
3658 v_apply_for_finaid VARCHAR2(1);
3659 BEGIN
3660 -- Initialise the output 'valid' parameters
3661 IF (p_check_referee) THEN
3662 p_valid_referee := FALSE;
3663 ELSE
3664 p_valid_referee := TRUE;
3665 END IF;
3666 IF (p_check_scholarship) THEN
3667 p_valid_scholarship := FALSE;
3668 ELSE
3669 p_valid_scholarship := TRUE;
3670 END IF;
3671 IF (p_check_lang_prof) THEN
3672 p_valid_lang_prof := FALSE;
3673 ELSE
3674 p_valid_lang_prof := TRUE;
3675 END IF;
3676 IF (p_check_interview) THEN
3677 p_valid_interview := FALSE;
3678 ELSE
3679 p_valid_interview := TRUE;
3680 END IF;
3681 IF (p_check_exchange) THEN
3682 p_valid_exchange := FALSE;
3683 ELSE
3684 p_valid_exchange := TRUE;
3685 END IF;
3686 IF (p_check_adm_test) THEN
3687 p_valid_adm_test := FALSE;
3688 ELSE
3689 p_valid_adm_test := TRUE;
3690 END IF;
3691 IF (p_check_fee_assess) THEN
3692 p_valid_fee_assess := FALSE;
3693 ELSE
3694 p_valid_fee_assess := TRUE;
3695 END IF;
3696 IF (p_check_cor_category) THEN
3697 p_valid_cor_category := FALSE;
3698 ELSE
3699 p_valid_cor_category := TRUE;
3700 END IF;
3701 IF (p_check_enr_category) THEN
3702 p_valid_enr_category := FALSE;
3703 ELSE
3704 p_valid_enr_category := TRUE;
3705 END IF;
3706 IF (p_check_research) THEN
3707 p_valid_research := FALSE;
3708 v_check_research := TRUE;
3709 ELSE
3710 -- Check if research data should be mandatory.
3711 -- Research data is mandatory for research courses.
3712 OPEN c_crv;
3713 FETCH c_crv INTO v_dummy;
3714 IF c_crv%FOUND THEN
3715 CLOSE c_crv;
3716 v_check_research := TRUE;
3717 p_valid_research := FALSE;
3718 ELSE
3719 CLOSE c_crv;
3720 v_check_research := FALSE;
3721 p_valid_research := TRUE;
3722 END IF;
3723 END IF;
3724 IF (p_check_rank_app) THEN
3725 p_valid_rank_app := FALSE;
3726 ELSE
3727 p_valid_rank_app := TRUE;
3728 END IF;
3729 IF (p_check_completion) THEN
3730 p_valid_completion := FALSE;
3731 ELSE
3732 p_valid_completion := TRUE;
3733 END IF;
3734 IF (p_check_rank_set) THEN
3735 p_valid_rank_set := FALSE;
3736 ELSE
3737 p_valid_rank_set := TRUE;
3738 END IF;
3739 IF (p_check_basis_adm) THEN
3740 p_valid_basis_adm := FALSE;
3741 ELSE
3742 p_valid_basis_adm := TRUE;
3743 END IF;
3744 IF (p_check_crs_international) THEN
3745 p_valid_crs_international := FALSE;
3746 ELSE
3747 p_valid_crs_international := TRUE;
3748 END IF;
3749 IF (p_check_ass_tracking) THEN
3750 p_valid_ass_tracking := FALSE;
3751 ELSE
3752 p_valid_ass_tracking := TRUE;
3753 END IF;
3754 IF (p_check_adm_code) THEN
3755 p_valid_adm_code := FALSE;
3756 ELSE
3757 p_valid_adm_code := TRUE;
3758 END IF;
3759 IF (p_check_fund_source) THEN
3760 p_valid_fund_source := FALSE;
3761 ELSE
3762 p_valid_fund_source := TRUE;
3763 END IF;
3764 IF (p_check_location) THEN
3765 p_valid_location := FALSE;
3766 ELSE
3767 p_valid_location := TRUE;
3768 END IF;
3769 IF (p_check_att_mode) THEN
3770 p_valid_att_mode := FALSE;
3771 ELSE
3772 p_valid_att_mode := TRUE;
3773 END IF;
3774 IF (p_check_att_type) THEN
3775 p_valid_att_type := FALSE;
3776 ELSE
3777 p_valid_att_type := TRUE;
3778 END IF;
3779 IF (p_check_unit_set) THEN
3780 p_valid_unit_set := FALSE;
3781 ELSE
3782 p_valid_unit_set := TRUE;
3783 END IF;
3784 -- Validate course level data
3785 IF (p_check_referee = TRUE OR
3786 p_check_scholarship = TRUE OR
3787 p_check_lang_prof = TRUE OR
3788 p_check_interview = TRUE OR
3789 p_check_exchange = TRUE OR
3790 p_check_adm_test = TRUE OR
3791 v_check_research = TRUE) THEN
3792 IGS_AD_GEN_004.ADMP_GET_CRS_EXISTS(
3793 p_person_id,
3794 p_admission_appl_number,
3795 p_nominated_course_cd,
3796 p_acai_sequence_number,
3797 p_course_cd,
3798 p_effective_dt,
3799 p_s_admission_process_type,
3800 p_check_referee,
3801 p_check_scholarship,
3802 p_check_lang_prof,
3803 p_check_interview,
3804 p_check_exchange,
3805 p_check_adm_test,
3806 v_check_research,
3807 v_valid_referee,
3808 v_valid_scholarship,
3809 v_valid_lang_prof,
3810 v_valid_interview,
3811 v_valid_exchange,
3812 v_valid_adm_test,
3813 v_valid_research,
3814 v_error_message_research);
3815
3816 IF v_error_message_research IS NULL THEN
3817 v_error_message_research := 'IGS_AD_INVALID_RESDET';
3818 END IF;
3819
3820 IF (v_valid_referee = TRUE) THEN
3821 p_valid_referee := TRUE;
3822 END IF;
3823 IF (v_valid_scholarship = TRUE) THEN
3824 p_valid_scholarship := TRUE;
3825 END IF;
3826 IF (v_valid_lang_prof = TRUE) THEN
3827 p_valid_lang_prof := TRUE;
3828 END IF;
3829 IF (v_valid_interview = TRUE) THEN
3830 p_valid_interview := TRUE;
3831 END IF;
3832 IF (v_valid_exchange = TRUE) THEN
3833 p_valid_exchange := TRUE;
3834 END IF;
3835 IF (v_valid_adm_test = TRUE) THEN
3836 p_valid_adm_test := TRUE;
3837 END IF;
3838 IF (v_valid_research = TRUE) THEN
3839 p_valid_research := TRUE;
3840 END IF;
3841 END IF;
3842 IF (p_check_fee_assess = TRUE OR
3843 p_check_cor_category = TRUE OR
3844 p_check_enr_category = TRUE OR
3845 p_check_rank_app = TRUE OR
3846 p_check_completion = TRUE OR
3847 p_check_rank_set = TRUE OR
3848 p_check_basis_adm = TRUE OR
3849 p_check_crs_international = TRUE OR
3850 p_check_ass_tracking = TRUE OR
3851 p_check_adm_code = TRUE OR
3852 p_check_fund_source = TRUE OR
3853 p_check_location = TRUE OR
3854 p_check_att_mode = TRUE OR
3855 p_check_att_type = TRUE OR
3856 p_check_unit_set = TRUE) THEN
3857 OPEN c_acaiv;
3858 FETCH c_acaiv INTO v_location_cd,
3859 v_attendance_mode,
3860 v_attendance_type,
3861 v_unit_set_cd,
3862 v_us_version_number,
3863 v_ass_rank,
3864 v_intrntnl_accptnce_advice_num,
3865 v_ass_tracking_id,
3866 v_fee_cat,
3867 v_expected_completion_yr,
3868 v_expected_completion_perd,
3869 v_correspondence_cat,
3870 v_enrolment_cat,
3871 v_funding_source,
3872 v_basis_for_admission_type,
3873 v_admission_cd,
3874 v_course_rank_set,
3875 v_course_rank_schedule;
3876 CLOSE c_acaiv;
3877 IF (p_check_fee_assess) THEN
3878 IF (v_fee_cat IS NOT NULL) THEN
3879 p_valid_fee_assess := TRUE;
3880 v_valid_fee_assess := TRUE;
3881 ELSE
3882 IF (p_s_admission_process_type = cst_re_admit) THEN
3883 OPEN c_sca;
3884 FETCH c_sca INTO v_sca_fee_cat,
3885 v_sca_correspondence_cat,
3886 v_sca_funding_source;
3887 CLOSE c_sca;
3888 IF (v_sca_fee_cat IS NOT NULL) THEN
3889 p_valid_fee_assess := TRUE;
3890 v_valid_fee_assess := TRUE;
3891 END IF;
3892 END IF;
3893 END IF;
3894 END IF;
3895 IF (p_check_cor_category) THEN
3896 IF (v_correspondence_cat IS NOT NULL) THEN
3897 p_valid_cor_category := TRUE;
3898 v_valid_cor_category := TRUE;
3899 ELSE
3900 IF (p_s_admission_process_type = cst_re_admit) THEN
3901 OPEN c_sca;
3902 FETCH c_sca INTO v_sca_fee_cat,
3903 v_sca_correspondence_cat,
3904 v_sca_funding_source;
3905 CLOSE c_sca;
3906 IF (v_sca_correspondence_cat IS NOT NULL) THEN
3907 p_valid_cor_category := TRUE;
3908 v_valid_cor_category := TRUE;
3909 END IF;
3910 END IF;
3911 END IF;
3912 END IF;
3913 IF (p_check_enr_category) THEN
3914 IF (v_enrolment_cat IS NOT NULL) THEN
3915 p_valid_enr_category := TRUE;
3916 v_valid_enr_category := TRUE;
3917 ELSE
3918 IF (p_s_admission_process_type = cst_re_admit) THEN
3919 OPEN c_scae;
3920 FETCH c_scae INTO v_scae_enrolment_cat;
3921 IF (c_scae%FOUND) THEN
3922 CLOSE c_scae;
3923 p_valid_enr_category := TRUE;
3924 v_valid_enr_category := TRUE;
3925 ELSE
3926 CLOSE c_scae;
3927 END IF;
3928 END IF;
3929 END IF;
3930 END IF;
3931 IF (p_check_rank_app = TRUE AND
3932 v_ass_rank IS NOT NULL) THEN
3933 p_valid_rank_app := TRUE;
3934 v_valid_rank_app := TRUE;
3935 END IF;
3936 IF (p_check_completion = TRUE AND
3937 v_expected_completion_yr IS NOT NULL AND
3938 v_expected_completion_perd IS NOT NULL) THEN
3939 p_valid_completion := TRUE;
3940 v_valid_completion := TRUE;
3941 END IF;
3942 IF (p_check_rank_set = TRUE AND
3943 v_course_rank_set IS NOT NULL AND
3944 v_course_rank_schedule IS NOT NULL) THEN
3945 p_valid_rank_set := TRUE;
3946 v_valid_rank_set := TRUE;
3947 END IF;
3948 IF (p_check_basis_adm = TRUE AND
3949 v_basis_for_admission_type IS NOT NULL) THEN
3950 p_valid_basis_adm := TRUE;
3951 v_valid_basis_adm := TRUE;
3952 END IF;
3953 IF (p_check_crs_international = TRUE AND
3954 v_intrntnl_accptnce_advice_num IS NOT NULL) THEN
3955 p_valid_crs_international := TRUE;
3956 v_valid_crs_international := TRUE;
3957 END IF;
3958 IF (p_check_ass_tracking = TRUE AND
3959 v_ass_tracking_id IS NOT NULL) THEN
3960 p_valid_ass_tracking := TRUE;
3961 v_valid_ass_tracking := TRUE;
3962 END IF;
3963 IF (p_check_adm_code = TRUE AND
3964 v_admission_cd IS NOT NULL) THEN
3965 p_valid_adm_code := TRUE;
3966 v_valid_adm_code := TRUE;
3967 END IF;
3968 IF p_check_fund_source = TRUE THEN
3969 IF v_funding_source IS NOT NULL THEN
3970 p_valid_fund_source := TRUE;
3971 v_valid_fund_source := TRUE;
3972 ELSE
3973 IF p_s_admission_process_type = cst_re_admit THEN
3974 OPEN c_sca;
3975 FETCH c_sca INTO v_sca_fee_cat,
3976 v_sca_correspondence_cat,
3977 v_sca_funding_source;
3978 CLOSE c_sca;
3979 IF v_sca_funding_source IS NOT NULL THEN
3980 p_valid_fund_source := TRUE;
3981 v_valid_fund_source := TRUE;
3982 END IF;
3983 END IF;
3984 END IF;
3985 END IF;
3986 IF (p_check_location = TRUE AND
3987 v_location_cd IS NOT NULL) THEN
3988 p_valid_location := TRUE;
3989 v_valid_location := TRUE;
3990 END IF;
3991 IF (p_check_att_mode = TRUE AND
3992 v_attendance_mode IS NOT NULL) THEN
3993 p_valid_att_mode := TRUE;
3994 v_valid_att_mode := TRUE;
3995 END IF;
3996 IF (p_check_att_type = TRUE AND
3997 v_attendance_type IS NOT NULL) THEN
3998 p_valid_att_type := TRUE;
3999 v_valid_att_type := TRUE;
4000 END IF;
4001 IF (p_check_unit_set = TRUE AND
4002 v_unit_set_cd IS NOT NULL) THEN
4003 p_valid_unit_set := TRUE;
4004 v_valid_unit_set := TRUE;
4005 END IF;
4006 END IF;
4007 -- Return false if the admission course application instance is incomplete.
4008 -- The spec differs from this code, in that you cannot test an OUT NOCOPY parameter
4009 -- for
4010 -- value, so we set up temporary local variables of the same name (swap the
4011 -- first p
4012 -- for a v) and gave them the same value (above).
4013
4014 -- by rrengara on 8-NOV-2002
4015 -- for bug no 2629077 (P) 2595982 (D) changed v_check_research to p_check_research
4016
4017
4018 IF (p_check_adm_test = TRUE AND
4019 v_valid_adm_test = FALSE) OR
4020 (p_check_fee_assess = TRUE AND
4021 v_valid_fee_assess = FALSE) OR
4022 (p_check_cor_category = TRUE AND
4023 v_valid_cor_category = FALSE) OR
4024 (p_check_enr_category = TRUE AND
4025 v_valid_enr_category = FALSE) OR
4026 (p_check_research = TRUE AND
4027 v_valid_research = FALSE) OR
4028 (p_check_rank_app = TRUE AND
4029 v_valid_rank_app = FALSE) OR
4030 (p_check_completion = TRUE AND
4031 v_valid_completion = FALSE) OR
4032 (p_check_rank_set = TRUE AND
4033 v_valid_rank_set = FALSE) OR
4034 (p_check_basis_adm = TRUE AND
4035 v_valid_basis_adm = FALSE) OR
4036 (p_check_crs_international = TRUE AND
4037 v_valid_crs_international = FALSE) OR
4038 (p_check_ass_tracking = TRUE AND
4039 v_valid_ass_tracking = FALSE) OR
4040 (p_check_adm_code = TRUE AND
4041 v_valid_adm_code = FALSE) OR
4042 (p_check_fund_source = TRUE AND
4043 v_valid_fund_source = FALSE) OR
4044 (p_check_location = TRUE AND
4045 v_valid_location = FALSE) OR
4046 (p_check_att_mode = TRUE AND
4047 v_valid_att_mode = FALSE) OR
4048 (p_check_att_type = TRUE AND
4049 v_valid_att_type = FALSE) OR
4050 (p_check_unit_set = TRUE AND
4051 v_valid_unit_set = FALSE) THEN
4052
4053 --SETS THE MESSAGE FOR INDIVIDUL INVALID OPTIONS,INSTEAD OF A GENERIC MESSAGE.
4054 IF v_valid_referee = FALSE AND p_check_referee = TRUE THEN
4055 p_message_name := 'IGS_AD_INVALID_REF';
4056 ELSIF v_valid_scholarship = FALSE AND p_check_scholarship = TRUE THEN
4057 p_message_name := 'IGS_AD_INVALID_SCHOL';
4058 ELSIF v_valid_lang_prof = FALSE AND p_check_lang_prof = TRUE THEN
4059 p_message_name := 'IGS_AD_INVALID_LANGPROF';
4060 ELSIF v_valid_interview = FALSE AND p_check_interview = TRUE THEN
4061 p_message_name := 'IGS_AD_INVALID_INTERVIEW';
4062 ELSIF v_valid_exchange = FALSE AND p_check_exchange = TRUE THEN
4063 p_message_name := 'IGS_AD_INVALID_EXCHANGE';
4064 ELSIF v_valid_adm_test = FALSE AND p_check_adm_test = TRUE THEN
4065 p_message_name := 'IGS_AD_INVALID_ADMTST';
4066 ELSIF v_valid_fee_assess = FALSE AND p_check_fee_assess = TRUE THEN
4067 p_message_name := 'IGS_AD_INVALID_FEEASS';
4068 ELSIF v_valid_cor_category = FALSE AND p_check_cor_category = TRUE THEN
4069 p_message_name := 'IGS_AD_INVALID_CORRCAT';
4070 ELSIF v_valid_enr_category = FALSE AND p_check_enr_category = TRUE THEN
4071 p_message_name := 'IGS_AD_INVALID_ENRCAT';
4072 ELSIF v_valid_research = FALSE AND p_check_research = TRUE THEN
4073 p_message_name := v_error_message_research;
4074 ELSIF v_valid_rank_app = FALSE AND p_check_rank_app = TRUE THEN
4075 p_message_name := 'IGS_AD_INVALID_RANKAPP';
4076 ELSIF v_valid_completion = FALSE AND p_check_completion = TRUE THEN
4077 p_message_name := 'IGS_AD_INVALID_PERCOMP';
4078 ELSIF v_valid_rank_set = FALSE AND p_check_rank_set = TRUE THEN
4079 p_message_name := 'IGS_AD_INVALID_PERRANK';
4080 ELSIF v_valid_basis_adm = FALSE AND p_check_basis_adm = TRUE THEN
4081 p_message_name := 'IGS_AD_INVALID_ADMBAS';
4082 ELSIF v_valid_crs_international = FALSE AND p_check_crs_international = TRUE THEN
4083 p_message_name := 'IGS_AD_INVALID_PRGINT';
4084 ELSIF v_valid_ass_tracking = FALSE AND p_check_ass_tracking = TRUE THEN
4085 p_message_name := 'IGS_AD_INVALID_TRCK';
4086 ELSIF v_valid_adm_code = FALSE AND p_check_adm_code = TRUE THEN
4087 p_message_name := 'IGS_AD_INVALID_ADMCD';
4088 ELSIF v_valid_fund_source = FALSE AND p_check_fund_source = TRUE THEN
4089 p_message_name := 'IGS_AD_INVALID_FUNDSRC';
4090 ELSIF v_valid_location = FALSE AND p_check_location = TRUE THEN
4091 p_message_name := 'IGS_AD_INVALID_LOC';
4092 ELSIF v_valid_att_mode = FALSE AND p_check_att_mode = TRUE THEN
4093 p_message_name := 'IGS_AD_INVALID_ATTNDMODE';
4094 ELSIF v_valid_att_type = FALSE AND p_check_att_type = TRUE THEN
4095 p_message_name := 'IGS_AD_INVALID_ATTNDTYPE';
4096 ELSIF v_valid_unit_set = FALSE AND p_check_unit_set = TRUE THEN
4097 p_message_name := 'IGS_AD_INVALID_UNTSET';
4098 END IF;
4099 RETURN FALSE;
4100 END IF;
4101
4102 FOR v_apcs_rec IN c_apcs LOOP
4103 IF (v_apcs_rec.s_admission_step_type = 'PGM-APPRV') THEN
4104 OPEN c_pa;
4105 FETCH c_pa INTO v_person_id;
4106 IF (c_pa%FOUND) THEN
4107 close c_pa;
4108 ELSE
4109 CLOSE c_pa;
4110 p_message_name:= 'IGS_AD_MND_PRGAPPRV';
4111 v_return := FALSE;
4112 EXIT;
4113 END IF;
4114
4115 ELSIF (v_apcs_rec.s_admission_step_type = 'DES-UNITSETS') THEN -- modified for 2382599
4116 OPEN c_us;
4117 FETCH c_us INTO v_person_id;
4118 IF (c_us%FOUND) THEN
4119 close c_us;
4120 ELSE
4121 CLOSE c_us;
4122 p_message_name:= 'IGS_AD_MND_UNTSETDL';
4123 v_return := FALSE;
4124 EXIT;
4125 END IF;
4126
4127 ELSIF (v_apcs_rec.s_admission_step_type='OTH-INST-APPL') THEN
4128 OPEN c_oi;
4129 FETCH c_oi INTO v_person_id;
4130 IF (c_oi%FOUND) THEN
4131 close c_oi;
4132 ELSE
4133 CLOSE c_oi;
4134 p_message_name:= 'IGS_AD_MND_OTUNS_APP';
4135 v_return := FALSE;
4136 EXIT;
4137 END IF;
4138
4139 ELSIF (v_apcs_rec.s_admission_step_type = 'EDU-GOALS') THEN
4140 OPEN c_eg;
4141 FETCH c_eg INTO v_person_id;
4142 IF (c_eg%FOUND) THEN
4143 close c_eg;
4144 ELSE
4145 CLOSE c_eg;
4146 p_message_name:= 'IGS_AD_MND_EDUGOAL';
4147 v_return := FALSE;
4148 EXIT;
4149 END IF;
4150
4151 ELSIF (v_apcs_rec.s_admission_step_type = 'ACAD-INTEREST') THEN
4152 OPEN c_ai;
4153 FETCH c_ai INTO v_person_id;
4154 IF (c_ai%FOUND) THEN
4155 close c_ai;
4156 ELSE
4157 CLOSE c_ai;
4158 p_message_name:= 'IGS_AD_MND_ACADINT';
4159 v_return := FALSE;
4160 EXIT;
4161 END IF;
4162
4163 ELSIF (v_apcs_rec.s_admission_step_type = 'APPL-INTENT') THEN
4164 OPEN c_in;
4165 FETCH c_in INTO v_person_id;
4166 IF (c_in%FOUND) THEN
4167 close c_in;
4168 ELSE
4169 CLOSE c_in;
4170 p_message_name:= 'IGS_AD_MND_ACINT_DET';
4171 v_return := FALSE;
4172 EXIT;
4173 END IF;
4174
4175 ELSIF (v_apcs_rec.s_admission_step_type = 'SPL-INTEREST') THEN
4176 OPEN c_si;
4177 FETCH c_si INTO v_person_id;
4178 IF (c_si%FOUND) THEN
4179 close c_si;
4180 ELSE
4181 CLOSE c_si;
4182 p_message_name:= 'IGS_AD_SPINTR_DL';
4183 v_return := FALSE;
4184 EXIT;
4185 END IF;
4186
4187 ELSIF (v_apcs_rec.s_admission_step_type = 'SPL-TALENT') THEN
4188 OPEN c_st;
4189 FETCH c_st INTO v_person_id;
4190 IF (c_st%FOUND) THEN
4191 close c_st;
4192 ELSE
4193 CLOSE c_st;
4194 p_message_name:= 'IGS_AD_MND_SPLTLNT';
4195 v_return := FALSE;
4196 EXIT;
4197 END IF;
4198
4199 ELSIF (v_apcs_rec.s_admission_step_type = 'FEE-DETAIL') THEN
4200 OPEN c_ar;
4201 FETCH c_ar INTO v_person_id;
4202 IF (c_ar%FOUND) THEN
4203 close c_ar;
4204 ELSE
4205 CLOSE c_ar;
4206 p_message_name:= 'IGS_AD_MND_APPFINDTL';
4207 v_return := FALSE;
4208 EXIT;
4209 END IF;
4210
4211 ELSIF (v_apcs_rec.s_admission_step_type = 'CREDENTAILS') THEN
4212 OPEN c_ac;
4213 FETCH c_ac INTO v_person_id;
4214 IF (c_ac%FOUND) THEN
4215 close c_ac;
4216 ELSE
4217 CLOSE c_ac;
4218 p_message_name:= 'IGS_AD_MND_CREDEN_DET';
4219 v_return := FALSE;
4220 EXIT;
4221 END IF;
4222
4223 ELSIF (v_apcs_rec.s_admission_step_type = 'ACAD-HONORS') THEN
4224 OPEN c_ah;
4225 FETCH c_ah INTO v_person_id;
4226 IF (c_ah%FOUND) THEN
4227 close c_ah;
4228 ELSE
4229 CLOSE c_ah;
4230 p_message_name:= 'IGS_AD_MND_HONORS';
4231 v_return := FALSE;
4232 EXIT;
4233 END IF;
4234
4235 ELSIF (v_apcs_rec.s_admission_step_type IN ('MISC-DTL','FINAID')) THEN
4236 OPEN c_md;
4237 FETCH c_md INTO
4238 v_apply_for_finaid,
4239 v_app_file_location1 ,
4240 v_late_adm_fee_status1,
4241 v_enrolment_cat1 ,
4242 v_fee_cat1 ,
4243 v_hecs_payment_option1 ,
4244 v_app_source_id1 ,
4245 v_correspondence_cat1,
4246 v_funding_source1 ;
4247 IF v_apcs_rec.s_admission_step_type='MISC-DTL' AND
4248 (v_app_file_location1 IS NOT NULL OR
4249 v_enrolment_cat1 IS NOT NULL OR
4250 v_fee_cat1 IS NOT NULL OR
4251 v_hecs_payment_option1 IS NOT NULL OR
4252 v_app_source_id1 IS NOT NULL OR
4253 v_correspondence_cat1 IS NOT NULL OR
4254 v_funding_source1 IS NOT NULL) THEN
4255 close c_md;
4256 ELSIF v_apcs_rec.s_admission_step_type='FINAID' AND (v_apply_for_finaid = 'Y') THEN
4257 close c_md;
4258 ELSE
4259 CLOSE c_md;
4260 IF v_apcs_rec.s_admission_step_type='MISC-DTL' THEN
4261 p_message_name:= 'IGS_AD_MND_MSCDTL';
4262 ELSE
4263 p_message_name:= 'IGS_AD_MND_FINAID';
4264 END IF;
4265 v_return := FALSE;
4266 EXIT;
4267 END IF;
4268
4269 -- START Added for 1366894
4270 -- Ordering of steps INTERVIEW_DTLS and FIN_INTERVIEW_DECS is importent
4271 ELSIF (v_apcs_rec.s_admission_step_type = 'INTERVIEW_DTLS') THEN
4272 OPEN c_id_exist;
4273 FETCH c_id_exist INTO v_person_id;
4274 IF (c_id_exist%FOUND) THEN
4275 CLOSE c_id_exist;
4276 ELSE
4277 CLOSE c_id_exist;
4278 p_message_name:= 'IGS_AD_MND_INTERVIEW_DETAILS';
4279 v_return := FALSE;
4280 EXIT;
4281 END IF;
4282
4283 ELSIF (v_apcs_rec.s_admission_step_type = 'FIN_INTERVIEW_DECS') THEN
4284 OPEN c_id;
4285 FETCH c_id INTO v_person_id;
4286 IF (c_id%FOUND) THEN
4287 CLOSE c_id;
4288 ELSE
4289 CLOSE c_id;
4290 p_message_name:= 'IGS_AD_MND_INTERVIEW_DECISION';
4291 v_return := FALSE;
4292 EXIT;
4293 END IF;
4294 -- END Added for 1366894
4295
4296 END IF;
4297
4298 END LOOP;
4299
4300 IF v_return = FALSE THEN
4301 RETURN v_return;
4302 ELSE
4303 p_message_name := NULL;
4304 RETURN TRUE;
4305 END IF;
4306
4307 EXCEPTION
4308 WHEN OTHERS THEN
4309 IF (c_acaiv%ISOPEN) THEN
4310 CLOSE c_acaiv;
4311 END IF;
4312 IF (c_sca%ISOPEN) THEN
4313 CLOSE c_sca;
4314 END IF;
4315 IF (c_scae%ISOPEN) THEN
4316 CLOSE c_scae;
4317 END IF;
4318 IF (c_crv%ISOPEN) THEN
4319 CLOSE c_crv;
4320 END IF;
4321 IF (c_apcs%ISOPEN) THEN
4322 CLOSE c_apcs;
4323 END IF;
4324 IF (c_ae%ISOPEN) THEN
4325 CLOSE c_ae;
4326 END IF;
4327 IF (c_pa%ISOPEN) THEN
4328 CLOSE c_pa;
4329 END IF;
4330 IF (c_us%ISOPEN) THEN
4331 CLOSE c_us;
4332 END IF;
4333
4334 IF (c_oi%ISOPEN) THEN
4335 CLOSE c_oi;
4336 END IF;
4337 IF (c_eg%ISOPEN) THEN
4338 CLOSE c_eg;
4339 END IF;
4340 IF (c_ai%ISOPEN) THEN
4341 CLOSE c_ai;
4342 END IF;
4343 IF (c_in%ISOPEN) THEN
4344 CLOSE c_in;
4345 END IF;
4346 IF (c_si%ISOPEN) THEN
4347 CLOSE c_si;
4348 END IF;
4349 IF (c_st%ISOPEN) THEN
4350 CLOSE c_st;
4351 END IF;
4352 IF (c_ar%ISOPEN) THEN
4353 CLOSE c_ar;
4354 END IF;
4355 IF (c_ac%ISOPEN) THEN
4356 CLOSE c_ac;
4357 END IF;
4358 IF (c_ah%ISOPEN) THEN
4359 CLOSE c_ah;
4360 END IF;
4361 IF (c_md%ISOPEN) THEN
4362 CLOSE c_md;
4363 END IF;
4364 IF (c_id_exist%FOUND) THEN
4365 CLOSE c_id_exist;
4366 END IF;
4367 IF (c_id%FOUND) THEN
4368 CLOSE c_id;
4369 END IF;
4370
4371 RAISE;
4372 END;
4373 EXCEPTION
4374 WHEN OTHERS THEN
4375 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4376 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_comp');
4377 IGS_GE_MSG_STACK.ADD;
4378 APP_EXCEPTION.RAISE_EXCEPTION;
4379 END admp_val_acai_comp;
4380 --
4381 -- Validate if the specified admission application IGS_PE_PERSON is complete.
4382 FUNCTION admp_val_pe_comp(
4383 p_person_id IN NUMBER ,
4384 p_effective_dt IN DATE ,
4385 p_check_athletics IN BOOLEAN,
4386 p_check_alternate IN BOOLEAN ,
4387 p_check_address IN BOOLEAN ,
4388 p_check_disability IN BOOLEAN ,
4389 p_check_visa IN BOOLEAN ,
4390 p_check_finance IN BOOLEAN ,
4391 p_check_notes IN BOOLEAN ,
4392 p_check_statistics IN BOOLEAN ,
4393 p_check_alias IN BOOLEAN ,
4394 p_check_tertiary IN BOOLEAN ,
4395 p_check_aus_sec_ed IN BOOLEAN ,
4396 p_check_os_sec_ed IN BOOLEAN ,
4397 p_check_employment IN BOOLEAN ,
4398 p_check_membership IN BOOLEAN ,
4399 p_check_dob IN BOOLEAN ,
4400 p_check_title IN BOOLEAN ,
4401 p_check_excurr IN BOOLEAN,
4402 p_message_name OUT NOCOPY VARCHAR2 ,
4403 p_valid_athletics OUT NOCOPY BOOLEAN ,
4404 p_valid_alternate OUT NOCOPY BOOLEAN ,
4405 p_valid_address OUT NOCOPY BOOLEAN ,
4406 p_valid_disability OUT NOCOPY BOOLEAN ,
4407 p_valid_visa OUT NOCOPY BOOLEAN ,
4408 p_valid_finance OUT NOCOPY BOOLEAN ,
4409 p_valid_notes OUT NOCOPY BOOLEAN ,
4410 p_valid_statistics OUT NOCOPY BOOLEAN ,
4411 p_valid_alias OUT NOCOPY BOOLEAN ,
4412 p_valid_tertiary OUT NOCOPY BOOLEAN ,
4413 p_valid_aus_sec_ed OUT NOCOPY BOOLEAN ,
4414 p_valid_os_sec_ed OUT NOCOPY BOOLEAN ,
4415 p_valid_employment OUT NOCOPY BOOLEAN ,
4416 p_valid_membership OUT NOCOPY BOOLEAN ,
4417 p_valid_dob OUT NOCOPY BOOLEAN ,
4418 p_valid_title OUT NOCOPY BOOLEAN,
4419 p_valid_excurr OUT NOCOPY BOOLEAN)
4420 RETURN BOOLEAN AS
4421
4422 BEGIN -- admp_val_pe_comp
4423 -- Validate if the specified admission application IGS_PE_PERSON is complete
4424 DECLARE
4425 v_valid_athletics BOOLEAN := FALSE;
4426 v_valid_alternate BOOLEAN := FALSE;
4427 v_valid_address BOOLEAN := FALSE;
4428 v_valid_disability BOOLEAN := FALSE;
4429 v_valid_visa BOOLEAN := FALSE;
4430 v_valid_finance BOOLEAN := FALSE;
4431 v_valid_notes BOOLEAN := FALSE;
4432 v_valid_statistics BOOLEAN := FALSE;
4433 v_valid_alias BOOLEAN := FALSE;
4434 v_valid_tertiary BOOLEAN := FALSE;
4435 v_valid_aus_sec_ed BOOLEAN := FALSE;
4436 v_valid_os_sec_ed BOOLEAN := FALSE;
4437 v_valid_employment BOOLEAN := FALSE;
4438 v_valid_membership BOOLEAN := FALSE;
4439 v_valid_dob BOOLEAN := FALSE;
4440 v_valid_title BOOLEAN := FALSE;
4441 v_birth_dt IGS_PE_PERSON.birth_dt%TYPE;
4442 v_title IGS_PE_PERSON.title%TYPE;
4443 v_valid_excurr BOOLEAN := FALSE;
4444 CURSOR c_pe IS
4445 SELECT pe.birth_date birth_dt,
4446 pe.title
4447 FROM IGS_PE_PERSON_BASE_V pe
4448 WHERE pe.person_id = p_person_id;
4449 v_list_element VARCHAR2(30);
4450 BEGIN
4451 --set default message number
4452 p_message_name := NULL;
4453 -- Initialise the output valid parameters
4454 IF (p_check_athletics) THEN
4455 p_valid_athletics := FALSE;
4456 ELSE
4457 p_valid_athletics := TRUE;
4458 END IF;
4459 IF (p_check_alternate) THEN
4460 p_valid_alternate := FALSE;
4461 ELSE
4462 p_valid_alternate := TRUE;
4463 END IF;
4464 IF (p_check_address) THEN
4465 p_valid_address := FALSE;
4466 ELSE
4467 p_valid_address := TRUE;
4468 END IF;
4469 IF (p_check_disability) THEN
4470 p_valid_disability := FALSE;
4471 ELSE
4472 p_valid_disability := TRUE;
4473 END IF;
4474 IF (p_check_visa) THEN
4475 p_valid_visa := FALSE;
4476 ELSE
4477 p_valid_visa := TRUE;
4478 END IF;
4479 IF (p_check_finance) THEN
4480 p_valid_finance := FALSE;
4481 ELSE
4482 p_valid_finance := TRUE;
4483 END IF;
4484 IF (p_check_notes) THEN
4485 p_valid_notes := FALSE;
4486 ELSE
4487 p_valid_notes := TRUE;
4488 END IF;
4489 IF (p_check_statistics) THEN
4490 p_valid_statistics := FALSE;
4491 ELSE
4492 p_valid_statistics := TRUE;
4493 END IF;
4494 IF (p_check_alias) THEN
4495 p_valid_alias := FALSE;
4496 ELSE
4497 p_valid_alias := TRUE;
4498 END IF;
4499 IF (p_check_tertiary) THEN
4500 p_valid_tertiary := FALSE;
4501 ELSE
4502 p_valid_tertiary := TRUE;
4503 END IF;
4504 IF (p_check_aus_sec_ed) THEN
4505 p_valid_aus_sec_ed := FALSE;
4506 ELSE
4507 p_valid_aus_sec_ed := TRUE;
4508 END IF;
4509 IF (p_check_os_sec_ed) THEN
4510 p_valid_os_sec_ed := FALSE;
4511 ELSE
4512 p_valid_os_sec_ed := TRUE;
4513 END IF;
4514 IF (p_check_employment) THEN
4515 p_valid_employment := FALSE;
4516 ELSE
4517 p_valid_employment := TRUE;
4518 END IF;
4519 IF (p_check_membership) THEN
4520 p_valid_membership := FALSE;
4521 ELSE
4522 p_valid_membership := TRUE;
4523 END IF;
4524 IF (p_check_dob) THEN
4525 p_valid_dob := FALSE;
4526 ELSE
4527 p_valid_dob := TRUE;
4528 END IF;
4529 IF (p_check_title) THEN
4530 p_valid_title := FALSE;
4531 ELSE
4532 p_valid_title := TRUE;
4533 END IF;
4534 IF (p_check_excurr) THEN
4535 p_valid_excurr := FALSE;
4536 ELSE
4537 p_valid_excurr := TRUE;
4538 END IF;
4539
4540 -- Validate IGS_PE_PERSON level data
4541 IF ( p_check_athletics = TRUE OR
4542 p_check_alternate = TRUE OR
4543 p_check_address = TRUE OR
4544 p_check_disability = TRUE OR
4545 p_check_visa = TRUE OR
4546 p_check_finance = TRUE OR
4547 p_check_notes = TRUE OR
4548 p_check_statistics = TRUE OR
4549 p_check_alias = TRUE OR
4550 p_check_tertiary = TRUE OR
4551 p_check_aus_sec_ed = TRUE OR
4552 p_check_os_sec_ed = TRUE OR
4553 p_check_employment = TRUE OR
4554 p_check_membership = TRUE OR
4555 p_check_excurr = TRUE) THEN
4556 IGS_AD_GEN_007.ADMP_GET_PE_EXISTS(
4557 p_person_id,
4558 p_effective_dt,
4559 p_check_athletics,
4560 p_check_alternate,
4561 p_check_address,
4562 p_check_disability,
4563 p_check_visa,
4564 p_check_finance,
4565 p_check_notes,
4566 p_check_statistics,
4567 p_check_alias,
4568 p_check_tertiary,
4569 p_check_aus_sec_ed,
4570 p_check_os_sec_ed,
4571 p_check_employment,
4572 p_check_membership,
4573 p_check_excurr,
4574 v_valid_athletics,
4575 v_valid_alternate,
4576 v_valid_address,
4577 v_valid_disability,
4578 v_valid_visa,
4579 v_valid_finance,
4580 v_valid_notes,
4581 v_valid_statistics,
4582 v_valid_alias,
4583 v_valid_tertiary,
4584 v_valid_aus_sec_ed,
4585 v_valid_os_sec_ed,
4586 v_valid_employment,
4587 v_valid_membership,
4588 v_valid_excurr);
4589 IF (v_valid_athletics = TRUE) THEN
4590 p_valid_athletics := TRUE;
4591 END IF;
4592 IF (v_valid_alternate = TRUE) THEN
4593 p_valid_alternate := TRUE;
4594 END IF;
4595 IF (v_valid_address = TRUE) THEN
4596 p_valid_address := TRUE;
4597 END IF;
4598 IF (v_valid_disability = TRUE) THEN
4599 p_valid_disability := TRUE;
4600 END IF;
4601 IF (v_valid_visa = TRUE) THEN
4602 p_valid_visa := TRUE;
4603 END IF;
4604 IF (v_valid_finance = TRUE) THEN
4605 p_valid_finance := TRUE;
4606 END IF;
4607 IF (v_valid_notes = TRUE) THEN
4608 p_valid_notes := TRUE;
4609 END IF;
4610 IF (v_valid_statistics = TRUE) THEN
4611 p_valid_statistics := TRUE;
4612 END IF;
4613 IF (v_valid_alias = TRUE) THEN
4614 p_valid_alias := TRUE;
4615 END IF;
4616 IF (v_valid_tertiary = TRUE) THEN
4617 p_valid_tertiary := TRUE;
4618 END IF;
4619 IF (v_valid_aus_sec_ed = TRUE) THEN
4620 p_valid_aus_sec_ed := TRUE;
4621 END IF;
4622 IF (v_valid_os_sec_ed = TRUE) THEN
4623 p_valid_os_sec_ed := TRUE;
4624 END IF;
4625 IF (v_valid_employment = TRUE) THEN
4626 p_valid_employment := TRUE;
4627 END IF;
4628 IF (v_valid_membership = TRUE) THEN
4629 p_valid_membership := TRUE;
4630 END IF;
4631 IF (v_valid_excurr = TRUE) THEN
4632 p_valid_excurr := TRUE;
4633 END IF;
4634 END IF;
4635 IF (p_check_dob = TRUE OR
4636 p_check_title = TRUE) THEN
4637 OPEN c_pe;
4638 FETCH c_pe INTO v_birth_dt,
4639 v_title;
4640 CLOSE c_pe;
4641 IF (p_check_dob = TRUE AND
4642 v_birth_dt IS NOT NULL) THEN
4643 p_valid_dob := TRUE;
4644 v_valid_dob := TRUE;
4645 END IF;
4646 IF (p_check_title = TRUE AND
4647 v_title IS NOT NULL) THEN
4648 p_valid_title := TRUE;
4649 v_valid_title := TRUE;
4650 END IF;
4651
4652
4653 END IF;
4654 --Return false if the admission course application instance is incomplete
4655 IF (p_check_athletics = TRUE AND
4656 v_valid_athletics = FALSE) OR
4657 (p_check_alternate = TRUE AND
4658 v_valid_alternate = FALSE) OR
4659 (p_check_address = TRUE AND
4660 v_valid_address = FALSE) OR
4661 (p_check_disability = TRUE AND
4662 v_valid_disability = FALSE) OR
4663 (p_check_visa = TRUE AND
4664 v_valid_visa = FALSE) OR
4665 (p_check_finance = TRUE AND
4666 v_valid_finance = FALSE) OR
4667 (p_check_notes = TRUE AND
4668 v_valid_notes = FALSE) OR
4669 (p_check_statistics = TRUE AND
4670 v_valid_statistics = FALSE) OR
4671 (p_check_alias = TRUE AND
4672 v_valid_alias = FALSE) OR
4673 (p_check_tertiary = TRUE AND
4674 v_valid_tertiary = FALSE) OR
4675 (p_check_aus_sec_ed = TRUE AND
4676 v_valid_aus_sec_ed = FALSE) OR
4677 (p_check_os_sec_ed = TRUE AND
4678 v_valid_os_sec_ed = FALSE) OR
4679 (p_check_employment = TRUE AND
4680 v_valid_employment = FALSE) OR
4681 (p_check_membership = TRUE AND
4682 v_valid_membership = FALSE) OR
4683 (p_check_dob = TRUE AND
4684 v_valid_dob = FALSE) OR
4685 (p_check_title = TRUE AND
4686 v_valid_title = FALSE) OR
4687 (p_check_excurr = TRUE AND
4688 v_valid_excurr = FALSE) THEN
4689
4690 --Set Messages for Individual Mandatory Details
4691 IF v_valid_dob = FALSE AND p_check_dob = TRUE THEN
4692 v_list_element := 'IGS_AD_INVALID_DOB';
4693
4694 ELSIF v_valid_title = FALSE AND p_check_title = TRUE THEN
4695 v_list_element := 'IGS_AD_INVALID_TITLE';
4696
4697 ELSIF v_valid_athletics = FALSE AND p_check_athletics = TRUE THEN
4698 v_list_element := 'IGS_AD_MND_ATHLETICS';
4699
4700 ELSIF v_valid_alternate = FALSE AND p_check_alternate = TRUE THEN
4701 v_list_element := 'IGS_AD_INVALID_ALTDET';
4702
4703 ELSIF v_valid_address = FALSE AND p_check_address = TRUE THEN
4704 v_list_element := 'IGS_AD_INVALID_ADDR';
4705
4706 ELSIF v_valid_disability = FALSE AND p_check_disability = TRUE THEN
4707 v_list_element := 'IGS_AD_INVALID_DISDTL';
4708
4709 ELSIF v_valid_visa = FALSE AND p_check_visa = TRUE THEN
4710 v_list_element := 'IGS_AD_INVALID_VISA';
4711
4712 ELSIF v_valid_finance = FALSE AND p_check_finance = TRUE THEN
4713 v_list_element := 'IGS_AD_INVALID_FINDET';
4714
4715 ELSIF v_valid_notes = FALSE AND p_check_notes = TRUE THEN
4716 v_list_element := 'IGS_AD_INVALID_NOTES';
4717
4718 ELSIF v_valid_statistics = FALSE AND p_check_statistics = TRUE THEN
4719 v_list_element := 'IGS_AD_INVALID_PERSTAT';
4720
4721 ELSIF v_valid_alias = FALSE AND p_check_alias = TRUE THEN
4722 v_list_element := 'IGS_AD_INVALID_ALIASDET';
4723
4724 ELSIF v_valid_tertiary = FALSE AND p_check_tertiary = TRUE THEN
4725 v_list_element := 'IGS_AD_INVALID_TEREDU';
4726
4727 ELSIF v_valid_aus_sec_ed = FALSE AND p_check_aus_sec_ed = TRUE THEN
4728 v_list_element := 'IGS_AD_INVALID_GOVSECEDU';
4729
4730 ELSIF v_valid_os_sec_ed = FALSE AND p_check_os_sec_ed = TRUE THEN
4731 v_list_element := 'IGS_AD_INVALID_SECEDU';
4732
4733 ELSIF v_valid_employment = FALSE AND p_check_employment = TRUE THEN
4734 v_list_element := 'IGS_AD_INVALID_EMPDTL';
4735
4736 ELSIF v_valid_membership = FALSE AND p_check_membership = TRUE THEN
4737 v_list_element := 'IGS_AD_INVALID_MEM';
4738
4739 ELSIF v_valid_excurr = FALSE AND p_check_excurr = TRUE THEN
4740 v_list_element := 'IGS_AD_MND_EXCURR';
4741
4742 END IF;
4743
4744 p_message_name := v_list_element;
4745 RETURN FALSE;
4746 END IF;
4747 p_message_name := NULL;
4748 RETURN TRUE;
4749 EXCEPTION
4750 WHEN OTHERS THEN
4751 IF (c_pe%ISOPEN) THEN
4752 CLOSE c_pe;
4753 END IF;
4754 RAISE;
4755 END;
4756 EXCEPTION
4757 WHEN OTHERS THEN
4758 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4759 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_pe_comp');
4760 IGS_GE_MSG_STACK.ADD;
4761 APP_EXCEPTION.RAISE_EXCEPTION;
4762 END admp_val_pe_comp;
4763 --
4764 -- Validate the deferment of IGS_AD_PS_APLINSTUNT records.
4765 FUNCTION admp_val_acaiu_defer(
4766 p_person_id IN NUMBER ,
4767 p_admission_appl_number IN NUMBER ,
4768 p_nominated_course_cd IN VARCHAR2 ,
4769 p_acai_sequence_number IN NUMBER ,
4770 p_acad_cal_type IN VARCHAR2 ,
4771 p_acad_ci_sequence_number IN NUMBER ,
4772 p_adm_cal_type IN VARCHAR2 ,
4773 p_adm_ci_sequence_number IN NUMBER ,
4774 p_s_admission_process_type IN VARCHAR2 ,
4775 p_message_name OUT NOCOPY VARCHAR2 )
4776 RETURN BOOLEAN AS
4777 BEGIN -- admp_val_acaiu_defer
4778 -- Validate the deferment of IGS_AD_PS_APLINSTUNT records
4779 DECLARE
4780 cst_offer CONSTANT
4781 IGS_AD_UNIT_OU_STAT.s_adm_outcome_status%TYPE := 'OFFER';
4782 v_message_name VARCHAR2(30);
4783 CURSOR c_acaiu_auos IS
4784 SELECT acaiu.unit_cd,
4785 acaiu.uv_version_number,
4786 acaiu.cal_type,
4787 acaiu.ci_sequence_number,
4788 acaiu.location_cd,
4789 acaiu.unit_class,
4790 acaiu.unit_mode
4791 FROM IGS_AD_PS_APLINSTUNT acaiu,
4792 IGS_AD_UNIT_OU_STAT auos
4793 WHERE acaiu.person_id = p_person_id AND
4794 acaiu.admission_appl_number = p_admission_appl_number AND
4795 acaiu.nominated_course_cd = p_nominated_course_cd AND
4796 acaiu.acai_sequence_number = p_acai_sequence_number AND
4797 auos.adm_unit_outcome_status = acaiu.adm_unit_outcome_status AND
4798 auos.s_adm_outcome_status = cst_offer;
4799 BEGIN
4800 -- Retrieve the admission course application instance units
4801 FOR v_acaiu_auos_rec IN c_acaiu_auos LOOP
4802 -- Validate the unit version
4803 IF (IGS_AD_VAL_ACAIU.admp_val_acaiu_uv(
4804 v_acaiu_auos_rec.unit_cd,
4805 v_acaiu_auos_rec.uv_version_number,
4806 p_s_admission_process_type,
4807 'Y', -- offered indicator
4808 v_message_name) = FALSE) THEN
4809 p_message_name := v_message_name;
4810 RETURN FALSE;
4811 END IF;
4812 -- Validate the unit offering option
4813 IF (IGS_AD_VAL_ACAIU.admp_val_acaiu_opt(
4814 v_acaiu_auos_rec.unit_cd,
4815 v_acaiu_auos_rec.uv_version_number,
4816 v_acaiu_auos_rec.cal_type,
4817 v_acaiu_auos_rec.ci_sequence_number,
4818 v_acaiu_auos_rec.location_cd,
4819 v_acaiu_auos_rec.unit_class,
4820 v_acaiu_auos_rec.unit_mode,
4821 p_adm_cal_type,
4822 p_adm_ci_sequence_number,
4823 p_acad_cal_type,
4824 p_acad_ci_sequence_number,
4825 'Y', -- offered indicator
4826 v_message_name) = FALSE) THEN
4827 p_message_name := v_message_name;
4828 RETURN FALSE;
4829 END IF;
4830 END LOOP;
4831 p_message_name := NULL;
4832 RETURN TRUE;
4833 EXCEPTION
4834 WHEN OTHERS THEN
4835 IF (c_acaiu_auos%ISOPEN) THEN
4836 CLOSE c_acaiu_auos;
4837 END IF;
4838 RAISE;
4839 END;
4840 EXCEPTION
4841 WHEN OTHERS THEN
4842 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4843 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acaiu_defer');
4844 IGS_GE_MSG_STACK.ADD;
4845 APP_EXCEPTION.RAISE_EXCEPTION;
4846 END admp_val_acaiu_defer;
4847 --
4848 -- Validate the IGS_AD_PS_APPL_INST.preference_number.
4849 FUNCTION admp_val_acai_pref(
4850 p_preference_number IN NUMBER ,
4851 p_pref_allowed IN VARCHAR2 ,
4852 p_pref_limit IN NUMBER ,
4853 p_s_admission_process_type IN VARCHAR2 ,
4854 p_message_name OUT NOCOPY VARCHAR2 )
4855 RETURN BOOLEAN AS
4856 BEGIN -- admp_val_acai_pref
4857 -- Validate the IGS_AD_PS_APPL_INST.preference_number.
4858 -- Validations are -
4859 -- - Preference number must be specified for a course preference application.
4860 -- - If specified, the preference number must not exceed the preference limit.
4861 DECLARE
4862 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
4863 BEGIN
4864 p_message_name := NULL;
4865 IF p_pref_allowed = 'Y' AND
4866 upper(p_s_admission_process_type) = cst_course THEN
4867 --Preference number must be specified for a course preference application.
4868 IF p_preference_number IS NULL THEN
4869 p_message_name := 'IGS_AD_PREFNUM_SPECIFIED';
4870 RETURN FALSE;
4871 ELSIF p_preference_number > p_pref_limit THEN
4872 --Preference number must be less than the preference limit.
4873 p_message_name := 'IGS_AD_PREFNUM_NOT_EXCEED';
4874 RETURN FALSE;
4875 END IF;
4876 END IF;
4877 RETURN TRUE;
4878 END;
4879 EXCEPTION
4880 WHEN OTHERS THEN
4881 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4882 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_pref');
4883 IGS_GE_MSG_STACK.ADD;
4884 APP_EXCEPTION.RAISE_EXCEPTION;
4885 END admp_val_acai_pref;
4886 --
4887 -- Validate adm course application instance expected completion details.
4888 FUNCTION admp_val_expctd_comp(
4889 p_expected_completion_yr IN NUMBER ,
4890 p_expected_completion_perd IN VARCHAR2 ,
4891 p_message_name OUT NOCOPY VARCHAR2 )
4892 RETURN BOOLEAN AS
4893 BEGIN -- admp_val_expctd_comp
4894 -- Description: Validate the admission course application
4895 -- instance expected completion details.
4896 DECLARE
4897 BEGIN
4898 p_message_name := NULL;
4899 IF p_expected_completion_yr IS NOT NULL AND
4900 p_expected_completion_perd IS NULL THEN
4901 p_message_name := 'IGS_AD_EXPCOMP_PER_SPECIFIED';
4902 RETURN FALSE;
4903 END IF;
4904 IF p_expected_completion_perd IS NOT NULL AND
4905 p_expected_completion_yr IS NULL THEN
4906 p_message_name := 'IGS_AD_EXPCOMP_YEAR_SPECIFIED';
4907 RETURN FALSE;
4908 END IF;
4909 RETURN TRUE;
4910 END;
4911 EXCEPTION
4912 WHEN OTHERS THEN
4913 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4914 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_expctd_comp');
4915 IGS_GE_MSG_STACK.ADD;
4916 APP_EXCEPTION.RAISE_EXCEPTION;
4917 END admp_val_expctd_comp;
4918 --
4919 -- Validate the admission course application instance funding source.
4920 FUNCTION admp_val_acai_fs(
4921 p_course_cd IN VARCHAR2 ,
4922 p_version_number IN NUMBER ,
4923 p_funding_source IN VARCHAR2 ,
4924 p_message_name OUT NOCOPY VARCHAR2 )
4925 RETURN BOOLEAN AS
4926 BEGIN -- admp_val_acai_fs
4927 -- This module validates the admission course application instance
4928 -- funding source.
4929 DECLARE
4930 v_fsr_found BOOLEAN := FALSE;
4931 v_fs_found BOOLEAN := FALSE;
4932 v_message_name VARCHAR2(30);
4933 CURSOR c_fsr IS
4934 SELECT fsr.funding_source
4935 FROM IGS_FI_FND_SRC_RSTN fsr
4936 WHERE fsr.course_cd = p_course_cd AND
4937 fsr.version_number = p_version_number AND
4938 fsr.restricted_ind = 'Y';
4939 BEGIN
4940 -- Set initial value
4941 p_message_name := NULL;
4942 v_fsr_found := FALSE;
4943 v_fs_found := FALSE;
4944 IF p_funding_source IS NOT NULL THEN
4945 -- Validate that the funding source is not closed.
4946 IF IGS_AD_VAL_SAFT.crsp_val_fs_closed(
4947 p_funding_source,
4948 v_message_name) = FALSE THEN
4949 p_message_name := v_message_name;
4950 RETURN FALSE;
4951 END IF;
4952 -- Validate that funding source is valid for the course version.
4953 FOR v_fsr_rec IN c_fsr LOOP
4954 v_fsr_found := TRUE;
4955 IF v_fsr_rec.funding_source = p_funding_source THEN
4956 v_fs_found := TRUE;
4957 EXIT;
4958 END IF;
4959 END LOOP;
4960 IF v_fsr_found = FALSE THEN
4961 -- No records found in IGS_FI_FND_SRC_RSTN
4962 RETURN TRUE;
4963 ELSIF v_fs_found = TRUE THEN
4964 -- Records found in IGS_FI_FND_SRC_RSTN
4965 -- found source is valid for the course version
4966 RETURN TRUE;
4967 ELSE
4968 -- Records found in IGS_FI_FND_SRC_RSTN
4969 -- found source is not valid for the course version
4970 p_message_name := 'IGS_AD_FUNDSRC_NOTVALID_APPL';
4971 RETURN FALSE;
4972 END IF;
4973 END IF; --IF p_funding_source IS NOT NULL
4974 RETURN TRUE;
4975 EXCEPTION
4976 WHEN OTHERS THEN
4977 IF c_fsr%ISOPEN THEN
4978 CLOSE c_fsr;
4979 END IF;
4980 RAISE;
4981 END;
4982 EXCEPTION
4983 WHEN OTHERS THEN
4984 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
4985 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_fs');
4986 IGS_GE_MSG_STACK.ADD;
4987 APP_EXCEPTION.RAISE_EXCEPTION;
4988 END admp_val_acai_fs; -- admp_val_acai_fs
4989
4990 END igs_ad_val_acai;
[Home] [Help]