1 PACKAGE BODY IGS_AD_VAL_AA AS
2 /* $Header: IGSAD76B.pls 120.0 2005/06/01 22:17:58 appldev noship $ */
3
4 --
5 -- Validate delete of an IGS_AD_APPL record.
6 FUNCTION admp_val_aa_delete(
7 p_adm_appl_status IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 BEGIN -- admp_val_aa_delete
11 -- Validate delete of an IGS_AD_APPL record.
12 DECLARE
13 v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
14 BEGIN
15 -- Set the default message number
16 p_message_name := null;
17 -- Get the system admission application status.
18 v_s_adm_appl_status := IGS_AD_GEN_007.ADMP_GET_SAAS(
19 p_adm_appl_status);
20 IF (v_s_adm_appl_status <> 'RECEIVED') THEN
21 p_message_name := 'IGS_AD_CANNOTDEL_ADMAPPL_PRD';
22 RETURN FALSE;
23 END IF;
24 RETURN TRUE;
25 END;
26 EXCEPTION
27 WHEN OTHERS THEN
28 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
29 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_delete');
30 IGS_GE_MSG_STACK.ADD;
31 App_Exception.Raise_Exception;
32 END admp_val_aa_delete;
33 --
34 -- Validate insert of an IGS_AD_APPL record.
35 FUNCTION admp_val_aa_insert(
36 p_person_id IN NUMBER ,
37 p_adm_cal_type IN VARCHAR2 ,
38 p_adm_ci_sequence_number IN NUMBER ,
39 p_s_admission_process_type IN VARCHAR2 ,
40 p_encmb_chk_ind IN VARCHAR2 DEFAULT 'N',
41 p_appl_dt IN DATE ,
42 p_title_required_ind IN VARCHAR2 DEFAULT 'N',
43 p_birth_dt_required_ind IN VARCHAR2 DEFAULT 'N',
44 p_message_name OUT NOCOPY VARCHAR2 ,
45 p_return_type OUT NOCOPY VARCHAR2 )
46 RETURN BOOLEAN AS
47 BEGIN
48 -- admp_val_aa_insert
49 -- Validate insert of an IGS_AD_APPL record.
50 -- * If the admission process type is short admission, the short admission
51 -- date alias for the admission period must be set.
52 -- * If the admission process type is short admission, the short admission
53 -- date must be greater than the current date.
54 -- * If encumbrance checking is applicable, warn if the person has an
55 -- encumbrance of revoke service or suspend service effective as at the
56 -- application date.
57 -- * If encumbrance checking is applicable, warn if the person has an
58 -- encumbrance of revoke service or suspend service effective as at the
59 -- encumbrance checking date.
60 -- * If the person?s title is required, warn if the persons?s title is not set.
61 -- * If the person?s birth date is required, warn if the persons?s birth date
62 -- is not set.
63 DECLARE
64 cst_error CONSTANT VARCHAR2(1) := 'E';
65 cst_warn CONSTANT VARCHAR2(1) := 'W';
66 v_person_found BOOLEAN DEFAULT FALSE;
67 v_short_adm_dt DATE;
68 v_encmb_chk_dt DATE;
69 v_effective_dt DATE;
70 v_message_name VARCHAR2(30);
71 CURSOR c_person IS
72 SELECT pb.title,
73 pb.birth_date,
74 ph.deceased_ind
75 FROM IGS_PE_PERSON_BASE_V pb, IGS_PE_HZ_PARTIES ph
76 WHERE pb.person_id = ph.party_id
77 AND pb.person_id = p_person_id;
78
79 v_person_rec c_person%ROWTYPE;
80 BEGIN
81 -- Set the default message number
82 p_message_name := null;
83 OPEN c_person;
84 FETCH c_person INTO v_person_rec;
85 IF (c_person%NOTFOUND) THEN
86 CLOSE c_person;
87 RETURN FALSE;
88 END IF;
89 CLOSE c_person;
90 -- Validate for deceased person.
91 IF (v_person_rec.deceased_ind = 'Y') THEN
92 p_message_name := 'IGS_AD_CANCREATE_ADMAPPL';
93 p_return_type := cst_error;
94 RETURN FALSE;
95 END IF;
96 -- Validate admission process type of short admission.
97 IF (p_s_admission_process_type = 'SHORT-ADM') THEN
98 -- Short admission date alias must exist for admission period and must
99 -- be greater than the current date.
100 v_short_adm_dt := IGS_AD_GEN_008.ADMP_GET_SHORT_DT (
101 p_adm_cal_type,
102 p_adm_ci_sequence_number);
103 IF (v_short_adm_dt IS NULL) THEN
104 p_message_name := 'IGS_AD_CANNOTINS_NO_SHORT_DT';
105 p_return_type := cst_error;
106 RETURN FALSE;
107 END IF;
108 IF (v_short_adm_dt > SYSDATE) THEN
109 p_message_name := 'IGS_AD_CANNOTINS_SHORT_DT';
110 p_return_type := cst_error;
111 RETURN FALSE;
112 END IF;
113 END IF;
114 -- Validate encumbrances.
115 IF (p_encmb_chk_ind = 'Y') THEN
116 --Warn if person is encumbered as at the application date.
117 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (
118 p_person_id,
119 NULL, -- Input parameter course code: not applicable
120 p_appl_dt,
121 v_message_name) THEN
122 p_message_name := 'IGS_AD_PERSON_ENCUMB_SUSPEND';
123 p_return_type := cst_warn;
124 RETURN FALSE;
125 END IF;
126 -- Determine the effective_dt for performing the
127 -- encumbrance check.
128 v_effective_dt := IGS_AD_GEN_006.ADMP_GET_ENCMB_DT(
129 p_adm_cal_type,
130 p_adm_ci_sequence_number);
131 IF v_effective_dt IS NOT NULL THEN
132 -- Warn if IGS_PE_PERSON is encumbered as at the
133 -- encumbrance checking date.
134 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (
135 p_person_id,
136 NULL, -- Input parameter course code: not applicable
137 v_effective_dt,
138 v_message_name) THEN
139 p_message_name := 'IGS_AD_ENCUMB_CHKING_DATE';
140 p_return_type := cst_warn;
141 RETURN FALSE;
142 END IF;
143 END IF;
144 END IF;
145 -- Validate person title.
146 IF (p_title_required_ind = 'Y') THEN
147 -- Warn if title is not set.
148 IF (v_person_rec.title IS NULL) THEN
149 p_message_name := 'IGS_AD_TITLE_TOBE_SET';
150 p_return_type := cst_warn;
151 RETURN FALSE;
152 END IF;
153 END IF;
154 -- Validate person birth date.
155 IF (p_birth_dt_required_ind = 'Y') THEN
156 -- Warn if birth date is not set.
157 IF (v_person_rec.birth_date IS NULL) THEN
158 p_message_name := 'IGS_AD_DOB_TOBE_SET';
159 p_return_type := cst_warn;
160 RETURN FALSE;
161 END IF;
162 END IF;
163 RETURN TRUE;
164 END;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
168 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_insert');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END admp_val_aa_insert;
172 --
173 -- Validate update of an IGS_AD_APPL record.
174 FUNCTION admp_val_aa_update(
175 p_adm_appl_status IN VARCHAR2 ,
176 p_message_name OUT NOCOPY VARCHAR2 )
177 RETURN BOOLEAN AS
178 BEGIN -- admp_val_aa_update
179 -- Validate update of an IGS_AD_APPL record.
180 DECLARE
181 v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
182 BEGIN
183 -- Set the default message number
184 p_message_name := null;
185 -- Get the system admission application status.
186 v_s_adm_appl_status := IGS_AD_GEN_007.ADMP_GET_SAAS(
187 p_adm_appl_status);
188 IF v_s_adm_appl_status IN ('COMPLETED', 'WITHDRAWN') THEN
189 p_message_name := 'IGS_AD_CANNOTUPD_STATUS_COMPL';
190 RETURN FALSE;
191 END IF;
192 RETURN TRUE;
193 END;
194 EXCEPTION
195 WHEN OTHERS THEN
196 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
197 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_update');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END admp_val_aa_update;
201 --
202 -- Validate the IGS_AD_APPL.appl_dt.
203 FUNCTION admp_val_aa_appl_dt(
204 p_appl_dt IN DATE ,
205 p_message_name OUT NOCOPY VARCHAR2 )
206 RETURN BOOLEAN AS
207 BEGIN
208 -- Validate the IGS_AD_APPL.appl_dt.
209 -- Validations are -
210 -- IGS_AD_APPL.appl_dt must be less than or equal to
211 -- the current date.
212 IF (TRUNC(p_appl_dt) > TRUNC(SYSDATE)) THEN
213 p_message_name := 'IGS_AD_APPLDT_LE_CURRENT_DT';
214 RETURN FALSE;
215 END IF;
216 p_message_name := null;
217 RETURN TRUE;
218 EXCEPTION
219 WHEN OTHERS THEN
220 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
221 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_appl_dt');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END admp_val_aa_appl_dt;
225 --
226 -- Validate the admission application academic calendar.
227 FUNCTION admp_val_aa_acad_cal(
228 p_acad_cal_type IN VARCHAR2 ,
229 p_acad_ci_sequence_number IN NUMBER ,
230 p_message_name OUT NOCOPY VARCHAR2 )
231 RETURN BOOLEAN AS
232 BEGIN -- admp_val_aa_acad_cal
233 -- Validate the admission application commencement period
234 -- (IGS_AD_APPL.acad_cal_type, IGS_AD_APPL.acad_ci_sequence_number).
235 -- Validations are -
236 -- ? IGS_AD_APPL.acad_cal_type must be an Academic calendar.
237 -- ? IGS_AD_APPL.acad_cal_type and IGS_AD_APPL.acad_ci_sequence_number
238 -- must be an Active calendar instance.
239 DECLARE
240 CURSOR c_ct (
241 cp_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE) IS
242 SELECT s_cal_cat
243 FROM IGS_CA_TYPE
244 WHERE cal_type = cp_acad_cal_type;
245 CURSOR c_ci_cs (
246 cp_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE,
247 cp_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE) IS
248 SELECT cs.s_cal_status
249 FROM IGS_CA_STAT cs,
250 IGS_CA_INST ci
251 WHERE ci.cal_type = cp_acad_cal_type AND
252 ci.sequence_number = cp_acad_ci_sequence_number AND
253 ci.cal_status = cs.cal_status;
254
255 v_ct_rec c_ct%ROWTYPE;
256 v_ci_cs_rec c_ci_cs%ROWTYPE;
257 cst_academic VARCHAR2(10) := 'ACADEMIC';
258 cst_active VARCHAR2(10) := 'ACTIVE';
259 BEGIN
260 -- Set the default message number
261 p_message_name := null;
262 -- Cursor handling
263 OPEN c_ct (p_acad_cal_type);
264 FETCH c_ct INTO v_ct_rec;
265 IF c_ct%FOUND THEN
266 CLOSE c_ct;
267 IF v_ct_rec.s_cal_cat <> cst_academic THEN
268 p_message_name := 'IGS_AD_CAT_AS_ACADEMIC';
269 RETURN FALSE;
270 END IF;
271 ELSE
272 CLOSE c_ct;
273 END IF;
274 OPEN c_ci_cs (
275 p_acad_cal_type,
276 p_acad_ci_sequence_number);
277 FETCH c_ci_cs INTO v_ci_cs_rec;
278 IF c_ci_cs%NOTFOUND THEN
279 CLOSE c_ci_cs;
280 p_message_name := 'IGS_AD_ADM_CAL_INSTNOT_DEFINE';
281 RETURN FALSE; -- Corrected, to return FALSE as part of the bug 2772337.
282 -- Removed the End_Date validation as part of bug 2974150
283 END IF;
284 CLOSE c_ci_cs;
285
286 IF v_ci_cs_rec.s_cal_status <> cst_active THEN --removed the planned status as per bug#2722785 --rghosh
287 p_message_name := 'IGS_AD_ACACAL_PLANNED_ACTIVE';
288 RETURN FALSE;
289 END IF;
290 -- Return the default value
291 RETURN TRUE;
292 END;
293 EXCEPTION
294 WHEN OTHERS THEN
295 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
296 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_acad_cal');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END admp_val_aa_acad_cal;
300 --
301 -- Validate the admission application admission calendar.
302 FUNCTION admp_val_aa_adm_cal(
303 p_adm_cal_type IN VARCHAR2 ,
304 p_adm_ci_sequence_number IN NUMBER ,
305 p_acad_cal_type IN VARCHAR2 ,
306 p_acad_ci_sequence_number IN NUMBER ,
307 p_admission_cat IN VARCHAR2 ,
308 p_s_admission_process_type IN VARCHAR2 ,
309 p_message_name OUT NOCOPY VARCHAR2 )
310 RETURN BOOLEAN AS
311 BEGIN
312 DECLARE
313 cst_admission CONSTANT VARCHAR2(10) := 'ADMISSION';
314 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
315 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
316 v_dummy VARCHAR2(1);
317 CURSOR c_cal_type (
318 cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
319 SELECT cat.s_cal_cat
320 FROM IGS_CA_TYPE cat
321 WHERE cat.cal_type = cp_cal_type;
322 --Modified the following cursor to fetch end_dt in addition with s_cal_status. Bug: 2772337
323 --Modified the following cursor not to fetch end_dt in addition with s_cal_status. Bug: 2974150
324 CURSOR c_cal_instance (
325 cp_cal_type IGS_CA_INST.cal_type%TYPE,
326 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
327 SELECT cs.s_cal_status
328 FROM IGS_CA_INST ci,
329 IGS_CA_STAT cs
330 WHERE ci.cal_status = cs.cal_status AND
331 ci.cal_type = cp_cal_type AND
332 ci.sequence_number = cp_sequence_number;
333 CURSOR c_cal_ins_rel (
334 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
335 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
336 cp_adm_cal_type IGS_CA_INST.cal_type%TYPE,
337 cp_adm_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
338 SELECT 'x'
339 FROM IGS_CA_INST_REL cir
340 WHERE cir.sup_cal_type =cp_acad_cal_type AND
341 cir.sup_ci_sequence_number = cp_acad_ci_sequence_number AND
342 cir.sub_cal_type = cp_adm_cal_type AND
343 cir.sub_ci_sequence_number = cp_adm_ci_sequence_number;
344 CURSOR c_adm_perd_adm_proc_cat (
345 cp_adm_cal_type IGS_CA_INST.cal_type%TYPE,
346 cp_adm_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
347 cp_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE,
348 cp_s_admission_process_type
349 IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%TYPE) IS
350 SELECT 'x'
351 FROM IGS_AD_PRD_AD_PRC_CA apapc
352 WHERE apapc.adm_cal_type = cp_adm_cal_type AND
353 apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
354 apapc.admission_cat = cp_admission_cat AND
355 apapc.s_admission_process_type = cp_s_admission_process_type AND
356 apapc.closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
357 v_s_cal_status c_cal_instance%ROWTYPE;
358 BEGIN
359 -- Validate the admission application admission calendar
360 -- (IGS_AD_APPL.adm_cal_type,
361 -- IGS_AD_APPL.adm_ci_sequence_number).
362 -- Validations are -
363 -- IGS_AD_APPL.acad_cal_type must be an Admission calendar.
364 -- IGS_AD_APPL.adm_cal_type and IGS_AD_APPL.adm_ci_sequence_number
365 -- must be
366 -- an Active calendar instance.
367 -- The Admission Calendar must be a child of the Academic Calendar.
368 -- This validation is enforced in the database via the foreign key AA_CIR_FK.
369 -- It is included in this module for Forms processing purposes only.
370 -- The Admission Calendar must be for the Admission Process Category. This
371 -- validation is enforced in the database via the foreign key AA_APAPC_FK.
372 -- It is included in this module for Forms processing purposes only.
373 p_message_name := null;
374 OPEN c_cal_type(
375 p_adm_cal_type);
376 FETCH c_cal_type INTO v_s_cal_cat;
377 IF(c_cal_type%FOUND) THEN
378 IF(v_s_cal_cat <> cst_admission) THEN
379 CLOSE c_cal_type;
380 p_message_name := 'IGS_AD_ADMCAL_CAT_AS_ADM';
381 RETURN FALSE;
382 END IF;
383 END IF;
384 CLOSE c_cal_type;
385
386 OPEN c_cal_instance(
387 p_adm_cal_type,
388 p_adm_ci_sequence_number);
389 FETCH c_cal_instance INTO v_s_cal_status;
390 IF(c_cal_instance%FOUND) THEN
391 -- Added the End_Date validation as part of the bug 2772337.
392 --Removed the End_Date validation as part of bug 2974150
393
394 IF(v_s_cal_status.s_cal_status<> cst_active) THEN
395 CLOSE c_cal_instance;
396 p_message_name := 'IGS_AD_ADMCAL_PLANNED_ACTIVE'; --removed the planned status as per bug#2722785 --rghosh
397 RETURN FALSE;
398 END IF;
399 END IF;
400 CLOSE c_cal_instance;
401
402 OPEN c_cal_ins_rel(
403 p_acad_cal_type,
404 p_acad_ci_sequence_number,
405 p_adm_cal_type,
406 p_adm_ci_sequence_number);
407 FETCH c_cal_ins_rel INTO v_dummy;
408 IF(c_cal_ins_rel%NOTFOUND) THEN
409 CLOSE c_cal_ins_rel;
410 p_message_name := 'IGS_AD_ADMCAL_CHILD_ACACAL';
411 RETURN FALSE;
412 END IF;
413 CLOSE c_cal_ins_rel;
414 OPEN c_adm_perd_adm_proc_cat(
415 p_adm_cal_type,
416 p_adm_ci_sequence_number,
417 p_admission_cat,
418 p_s_admission_process_type);
419 FETCH c_adm_perd_adm_proc_cat INTO v_dummy;
420 IF(c_adm_perd_adm_proc_cat%NOTFOUND) THEN
421 CLOSE c_adm_perd_adm_proc_cat;
422 p_message_name := 'IGS_AD_ADMCAL_NOTLINK_ADMCAT';
423 RETURN FALSE;
424 END IF;
425 CLOSE c_adm_perd_adm_proc_cat;
426 RETURN TRUE;
427 END;
428 EXCEPTION
429 WHEN OTHERS THEN
430 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
431 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_adm_cal');
432 IGS_GE_MSG_STACK.ADD;
433 App_Exception.Raise_Exception;
434 END admp_val_aa_adm_cal;
435 --
436 -- Validate the IGS_AD_APPL.admission_cat.
437 FUNCTION admp_val_aa_adm_cat(
438 p_admission_cat IN VARCHAR2 ,
439 p_message_name OUT NOCOPY VARCHAR2 )
440 RETURN BOOLEAN AS
441 BEGIN
442 DECLARE
443 v_closed_ind IGS_AD_CAT.closed_ind%TYPE;
444 v_count NUMBER;
445 v_admission_cat IGS_AD_CAT.admission_cat%TYPE;
446 CURSOR c_ac IS
447 SELECT ac.closed_ind
448 FROM IGS_AD_CAT ac
449 WHERE ac.admission_cat = p_admission_cat;
450 CURSOR c_ccm IS
451 SELECT ccm.admission_cat,
452 COUNT(*)
453 FROM IGS_CO_CAT_MAP ccm
454 WHERE ccm.admission_cat = p_admission_cat AND
455 ccm.dflt_cat_ind = 'Y'
456 GROUP BY ccm.admission_cat;
457 CURSOR c_ecm IS
458 SELECT ecm.admission_cat,
459 COUNT(*)
460 FROM IGS_EN_CAT_MAPPING ecm
461 WHERE ecm.admission_cat = p_admission_cat AND
462 ecm.dflt_cat_ind = 'Y'
463 GROUP BY ecm.admission_cat;
464 CURSOR c_fcm IS
465 SELECT fcm.admission_cat,
466 COUNT(*)
467 FROM IGS_FI_FEE_CAT_MAP fcm
468 WHERE fcm.admission_cat = p_admission_cat AND
469 fcm.dflt_cat_ind = 'Y'
470 GROUP BY fcm.admission_cat;
471 BEGIN
472 -- Validate the IGS_AD_APPL.admission_cat.
473 -- Validations are:
474 -- IGS_AD_APPL.admission_cat must not be closed.
475 -- IGS_AD_APPL.admission_cat must have one and only
476 -- one default IGS_CO_CAT_MAP record.
477 -- IGS_AD_APPL.admission_cat must have one and only
478 -- one default IGS_EN_CAT_MAPPING record.
479 -- IGS_AD_APPL.admission_cat must have one and only
480 -- one default IGS_FI_FEE_CAT_MAP record.
481 OPEN c_ac;
482 FETCH c_ac INTO v_closed_ind;
483 IF (c_ac%FOUND) THEN
484 IF (v_closed_ind = 'Y') THEN
485 CLOSE c_ac;
486 p_message_name := 'IGS_AD_ADM_CATEGORY_CLOSED';
487 RETURN FALSE;
488 END IF;
489 END IF;
490 CLOSE c_ac;
491 /* Commented out NOCOPY because correspondence functionality is removed. Bug# 1478593 P1, nsinha 25-Oct-00
492 OPEN c_ccm;
493 FETCH c_ccm INTO v_admission_cat,
494 v_count;
495 IF (c_ccm%NOTFOUND) THEN
496 CLOSE c_ccm;
497 p_message_name := 'IGS_AD_ADMCAT_NOT_DFLT_CORCAT';
498 RETURN FALSE;
499 END IF;
500 CLOSE c_ccm;
501 IF (v_count > 1) THEN
502 p_message_name := 'IGS_AD_ADMCAT_MORE_DFLT_CORCA';
503 RETURN FALSE;
504 END IF;
505 */
506
507 --These are not required while creating the application/instance.
508 -- Needs to be handled in the set up form
509
510 /* OPEN c_ecm;
511 FETCH c_ecm INTO v_admission_cat,
512 v_count;
513 IF (c_ecm%NOTFOUND) THEN
514 CLOSE c_ecm;
515 p_message_name := 'IGS_AD_ADMCAT_NOT_DFLT_ENRCAT';
516 RETURN FALSE;
517 END IF;
518 CLOSE c_ecm;
519 IF (v_count > 1) THEN
520 p_message_name := 'IGS_AD_ADMCAT_MORE_DFLT_ENRCA';
521 RETURN FALSE;
522 END IF;
523 OPEN c_fcm;
524 FETCH c_fcm INTO v_admission_cat,
525 v_count;
526 IF (c_fcm%NOTFOUND) THEN
527 CLOSE c_fcm;
528 p_message_name := 'IGS_AD_ADMCAT_NOT_DFLT_FEECAT';
529 RETURN FALSE;
530 END IF;
531 CLOSE c_fcm;
532 IF (v_count > 1) THEN
533 p_message_name := 'IGS_AD_ADMCAT_DFLT_FEECAT';
534 RETURN FALSE;
535 END IF;*/
536 p_message_name := null;
537 RETURN TRUE;
538 EXCEPTION
539 WHEN OTHERS THEN
540 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
541 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_adm_cat');
542 IGS_GE_MSG_STACK.ADD;
543 App_Exception.Raise_Exception;
544 END;
545 END admp_val_aa_adm_cat;
546 --
547 -- Validate the IGS_AD_APPL.adm_appl_status.
548 FUNCTION admp_val_aa_aas(
549 p_person_id IN NUMBER ,
550 p_admission_appl_number IN NUMBER ,
551 p_adm_appl_status IN VARCHAR2 ,
552 p_message_name OUT NOCOPY VARCHAR2 )
553 RETURN BOOLEAN AS
554 BEGIN -- admp_val_aa_aas
555 -- Validate the IGS_AD_APPL.adm_appl_status. Validations are -
556 -- * The admission application status must be open. (AAS01)
557 -- * If the admission application status has a system value of
558 -- received, then it must equal the derived value. (AAS02)
559 -- * If the admission application status has a system value of
560 -- completed, then it must equal the derived value. (AAS03)
561 -- * If the admission application status has a system value of
562 -- withdrawn, then it must equal the derived value. (AAS04)
563 -- * If the admission application status has a system value of
564 -- withdrawn, there must be no admission course application
565 -- instance that has been made an offer that has not been
566 -- resolved or accepted (including deferral). (AAS05)
567 DECLARE
568 CURSOR c_acai IS
569 SELECT aos.s_adm_outcome_status,
570 aors.s_adm_offer_resp_status
571 FROM IGS_AD_PS_APPL_INST acai,
572 IGS_AD_OU_STAT aos,
573 IGS_AD_OFR_RESP_STAT aors
574 WHERE acai.person_id = p_person_id AND
575 acai.admission_appl_number = p_admission_appl_number AND
576 aos.adm_outcome_status = acai.adm_outcome_status AND
577 aors.adm_offer_resp_status = acai.adm_offer_resp_status;
578 cst_received CONSTANT VARCHAR2(10) := 'RECEIVED';
579 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
580 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
581 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
582 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
583 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
584 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
585 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
586 v_message_name VARCHAR2(30);
587 v_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
588 v_derived_s_adm_appl_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
589 v_exit_loop BOOLEAN DEFAULT FALSE;
590 BEGIN
591 -- Set the default message number
592 p_message_name := null;
593 -- Validate the closed indicator.
594 IF NOT IGS_AD_VAL_AA.admp_val_aas_closed (
595 p_adm_appl_status,
596 v_message_name) THEN
597 p_message_name := v_message_name;
598 RETURN FALSE;
599 END IF;
600 -- Get the admission application status system value.
601 v_s_adm_appl_status := IGS_AD_GEN_007.ADMP_GET_SAAS (
602 p_adm_appl_status);
603 -- Get admission application status derived value.
604 v_derived_s_adm_appl_status := IGS_AD_GEN_002.ADMP_GET_AA_AAS (
605 p_person_id,
606 p_admission_appl_number,
607 p_adm_appl_status);
608 -- Validate when the application is received.
609 IF v_s_adm_appl_status = cst_received AND
610 IGS_AD_GEN_007.ADMP_GET_SAAS(v_derived_s_adm_appl_status) <> cst_received THEN
611 p_message_name := 'IGS_AD_APPLST_CANNOT_RECEIVED';
612 RETURN FALSE;
613 END IF;
614 -- Validate when the application is completed.
615 IF v_s_adm_appl_status = cst_completed AND
616 IGS_AD_GEN_007.ADMP_GET_SAAS(v_derived_s_adm_appl_status) <> cst_completed THEN
617 p_message_name := 'IGS_AD_APPLST_CANNOT_COMPLETE';
618 RETURN FALSE;
619 END IF;
620 -- Validate when the application is withdrawn.
621 IF v_s_adm_appl_status = cst_withdrawn THEN
622 IF IGS_AD_GEN_007.ADMP_GET_SAAS(v_derived_s_adm_appl_status) <> cst_withdrawn THEN
623 p_message_name := 'IGS_AD_APPLST_CANNOT_WITHDRAW';
624 RETURN FALSE;
625 END IF;
626 -- Validate if the admission application can be withdrawn.
627 -- Loop through IGS_AD_PS_APPL_INST records:
628 FOR v_acai_rec IN c_acai LOOP
629 -- If any admission IGS_PS_COURSE application instance has been
630 -- made an offer that has not been resolved or accepted
631 -- (including deferral), then the admission application
632 -- cannot be withdrawn. The applicant should reject the offer.
633 IF v_acai_rec.s_adm_outcome_status IN (
634 cst_offer,
635 cst_cond_offer) AND
636 v_acai_rec.s_adm_offer_resp_status NOT IN (
637 cst_rejected,
638 cst_lapsed,
639 cst_not_applic) THEN
640 v_exit_loop := TRUE;
641 EXIT;
642 END IF;
643 END LOOP;
644 IF v_exit_loop THEN
645 p_message_name := 'IGS_AD_APPLST_NOTBE_WITHDRAWN';
646 RETURN FALSE;
647 END IF;
648 END IF;
649 -- Return the default value
650 RETURN TRUE;
651 EXCEPTION
652 WHEN OTHERS THEN
653 IF c_acai%ISOPEN THEN
654 CLOSE c_acai;
655 END IF;
656 App_Exception.Raise_Exception;
657 END;
658 EXCEPTION
659 WHEN OTHERS THEN
660 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
661 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_aas');
662 IGS_GE_MSG_STACK.ADD;
663 App_Exception.Raise_Exception;
664 END admp_val_aa_aas;
665 --
666 -- Validate if IGS_AD_APPL_STAT.adm_appl_status is closed.
667 FUNCTION admp_val_aas_closed(
668 p_adm_appl_status IN VARCHAR2 ,
669 p_message_name OUT NOCOPY VARCHAR2 )
670 RETURN BOOLEAN AS
671 BEGIN -- admp_val_aas_closed
672 -- Validate if IGS_AD_APPL_STAT.adm_appl_status is closed
673 DECLARE
674 CURSOR c_aas IS
675 SELECT closed_ind
676 FROM IGS_AD_APPL_STAT
677 WHERE adm_appl_status = p_adm_appl_status;
678 v_closed_ind IGS_AD_APPL_STAT.closed_ind%TYPE;
679 BEGIN
680 -- Set the default message number
681 p_message_name := null;
682 -- Cursor handling
683 OPEN c_aas;
684 FETCH c_aas INTO v_closed_ind;
685 IF c_aas%FOUND THEN
686 IF (v_closed_ind = 'Y') THEN
687 CLOSE c_aas;
688 p_message_name := 'IGS_AD_ADMAPL_STATUS_CLOSED';
689 RETURN FALSE;
690 END IF;
691 END IF;
692 -- Return the default value
693 CLOSE c_aas;
694 RETURN TRUE;
695 END;
696 EXCEPTION
697 WHEN OTHERS THEN
698 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
699 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aas_closed');
700 IGS_GE_MSG_STACK.ADD;
701 App_Exception.Raise_Exception;
702 END admp_val_aas_closed;
703 --
704 -- Validate the IGS_AD_APPL.adm_fee_status.
705 FUNCTION admp_val_aa_afs(
706 p_person_id IN NUMBER ,
707 p_admission_appl_number IN NUMBER ,
708 p_adm_fee_status IN VARCHAR2 ,
709 p_fees_required_ind IN VARCHAR2 DEFAULT 'N',
710 p_cond_offer_fee_allowed IN VARCHAR2 DEFAULT 'N',
711 p_message_name OUT NOCOPY VARCHAR2 )
712 RETURN BOOLEAN AS
713 BEGIN -- admp_val_aa_afs
714 -- Validate the IGS_AD_APPL.adm_fee_status. Validations are
715 -- The adm_fee_status must be open.
716 -- If fee's are required the adm_fee_status must not have a system value of
717 -- not-applicable.
718 -- If fee's are not required the adm_fee_status must have a system value of
719 -- not-applicable.
720 -- The IGS_AD_PS_APPL_INST relating to the admission application have
721 -- valid statuses for adm_fee_status.
722 DECLARE
723 cst_not_applic CONSTANT VARCHAR2(10) :='NOT-APPLIC';
724 cst_pending CONSTANT VARCHAR2(7) :='PENDING';
725 cst_assessed CONSTANT VARCHAR2(8) :='ASSESSED';
726 cst_offer CONSTANT VARCHAR2(5) :='OFFER';
727 cst_cond_offer CONSTANT VARCHAR2(10) :='COND-OFFER';
728 cst_withdrawn CONSTANT VARCHAR2(9) :='WITHDRAWN';
729 cst_voided CONSTANT VARCHAR2(6) :='VOIDED';
730 cst_satisfied CONSTANT VARCHAR2(9) :='SATISFIED';
731 v_dummy VARCHAR2(1);
732 v_s_adm_fee_status IGS_AD_FEE_STAT.s_adm_fee_status%TYPE;
733 v_message_name VARCHAR2(30);
734 CURSOR c_chk_offer_pend IS
735 SELECT 'x'
736 FROM IGS_AD_PS_APPL_INST acai,
737 IGS_AD_OU_STAT aos
738 WHERE acai.person_id = p_person_id AND
739 acai.admission_appl_number = p_admission_appl_number AND
740 aos.s_adm_outcome_status IN (
741 cst_offer,
742 cst_cond_offer,
743 cst_withdrawn,
744 cst_voided) AND
745 acai.adm_outcome_status = aos.adm_outcome_status;
746 CURSOR c_chk_offer_ass IS
747 SELECT 'x'
748 FROM IGS_AD_PS_APPL_INST acai,
749 IGS_AD_OU_STAT aos
750 WHERE acai.person_id = p_person_id AND
751 acai.admission_appl_number = p_admission_appl_number AND
752 aos.s_adm_outcome_status = cst_offer AND
753 acai.adm_outcome_status = aos.adm_outcome_status;
754 CURSOR c_chk_cond_fee IS
755 SELECT 'x'
756 FROM IGS_AD_PS_APPL_INST acai,
757 IGS_AD_OU_STAT aos
758 WHERE acai.person_id = p_person_id AND
759 acai.admission_appl_number = p_admission_appl_number AND
760 aos.s_adm_outcome_status = cst_cond_offer AND
761 acai.adm_outcome_status = aos.adm_outcome_status;
762 CURSOR c_check_cndtnl IS
763 SELECT 'x'
764 FROM IGS_AD_PS_APPL_INST acai,
765 IGS_AD_OU_STAT aos,
766 IGS_AD_CNDNL_OFRSTAT acos
767 WHERE acai.person_id = p_person_id AND
768 acai.admission_appl_number = p_admission_appl_number AND
769 aos.s_adm_outcome_status = cst_cond_offer AND
770 acos.s_adm_cndtnl_offer_status = cst_satisfied AND
771 acai.adm_cndtnl_offer_status = acos.adm_cndtnl_offer_status AND
772 acai.adm_outcome_status = aos.adm_outcome_status;
773 BEGIN
774 -- Set the default message number
775 p_message_name := null;
776 -- Validate the closed indicator.
777 IF IGS_AD_VAL_ACAI_STATUS.admp_val_afs_closed(
778 p_adm_fee_status,
779 v_message_name) = FALSE THEN
780 p_message_name := v_message_name;
781 RETURN FALSE;
782 END IF;
783 -- Get the admission fee status system value.
784 v_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(p_adm_fee_status);
785 -- Validate when fee's are required.
786 IF (p_fees_required_ind = 'Y') THEN
787 IF (v_s_adm_fee_status = cst_not_applic) THEN
788 p_message_name := 'IGS_AD_APPLFEE_REQUIRED';
789 RETURN FALSE;
790 END IF;
791 -- Validate against admission IGS_PS_COURSE application outcome status
792 IF (v_s_adm_fee_status = cst_pending) THEN
793 OPEN c_chk_offer_pend;
794 FETCH c_chk_offer_pend INTO v_dummy;
795 IF c_chk_offer_pend%FOUND THEN
796 CLOSE c_chk_offer_pend;
797 -- Cannot be determining fees when offer has been made
798 p_message_name := 'IGS_AD_APPLFEE_NOTSET_OFRMADE';
799 RETURN FALSE;
800 END IF;
801 CLOSE c_chk_offer_pend;
802 END IF;
803 IF (v_s_adm_fee_status = cst_assessed) THEN
804 OPEN c_chk_offer_ass;
805 FETCH c_chk_offer_ass INTO v_dummy;
806 IF c_chk_offer_ass%FOUND THEN
807 CLOSE c_chk_offer_ass;
808 -- Cannot be assessing fees when offer is being made
809 p_message_name := 'IGS_AD_APPLFEE_NOTSET_ASSESED';
810 RETURN FALSE;
811 END IF;
812 CLOSE c_chk_offer_ass;
813 IF p_cond_offer_fee_allowed = 'N' THEN
814 OPEN c_chk_cond_fee;
815 FETCH c_chk_cond_fee INTO v_dummy;
816 IF c_chk_cond_fee%FOUND THEN
817 CLOSE c_chk_cond_fee;
818 -- Cannot be assessing fees when conditional offer is being made and fee
819 -- condtional offers are not allowed.
820 p_message_name := 'IGS_AD_FEEST_NOTBE_ASSESSED';
821 RETURN FALSE;
822 END IF;
823 CLOSE c_chk_cond_fee;
824 END IF;
825 OPEN c_check_cndtnl;
826 FETCH c_check_cndtnl INTO v_dummy;
827 IF c_check_cndtnl%FOUND THEN
828 CLOSE c_check_cndtnl;
829 -- Cannot be assessing fees when a conditional offer has been satisfied.
830 p_message_name := 'IGS_AD_APPLFEE_NOTBE_ASSESSED';
831 RETURN FALSE;
832 END IF;
833 CLOSE c_check_cndtnl;
834 END IF;
835 END IF;
836 -- Validate when fee's are not required.
837 IF p_fees_required_ind = 'N' AND
838 v_s_adm_fee_status <> cst_not_applic THEN
839 p_message_name := 'IGS_AD_APPLFEE_NOT_APPLICABLE';
840 RETURN FALSE;
841 END IF;
842 RETURN TRUE;
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF c_chk_offer_pend%ISOPEN THEN
846 CLOSE c_chk_offer_pend;
847 END IF;
848 IF c_chk_offer_ass%ISOPEN THEN
849 CLOSE c_chk_offer_ass;
850 END IF;
851 IF c_chk_cond_fee%ISOPEN THEN
852 CLOSE c_chk_cond_fee;
853 END IF;
854 IF c_check_cndtnl%ISOPEN THEN
855 CLOSE c_check_cndtnl;
856 END IF;
857 App_Exception.Raise_Exception;
858 END;
859 EXCEPTION
860 WHEN OTHERS THEN
861 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
862 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_afs');
863 IGS_GE_MSG_STACK.ADD;
864 App_Exception.Raise_Exception;
865 END admp_val_aa_afs;
866 --
867 -- Validate if IGS_AD_FEE_STAT.adm_fee_status is closed.
868
869 --
870 -- Validate the IGS_AD_APPL.tac_appl_ind.
871 FUNCTION admp_val_aa_tac_appl(
872 p_person_id IN NUMBER ,
873 p_tac_appl_ind IN VARCHAR2 DEFAULT 'N',
874 p_appl_dt IN DATE ,
875 p_s_admission_process_type IN VARCHAR2 ,
876 p_message_name OUT NOCOPY VARCHAR2 ,
877 p_return_type OUT NOCOPY VARCHAR2 )
878 RETURN BOOLEAN AS
879 BEGIN -- admp_val_aa_tac_appl
880 DECLARE -- Validate the IGS_AD_APPL.tac_appl_ind.
881 -- Validations are -
882 -- If the IGS_AD_APPL.tac_appl_ind = 'Y' then an alternate_person_id
883 -- record must exist for the person with an s_person_id_type of 'TAC'.
884 -- If the IGS_PE_ALT_PERS_ID record is dated, then it must be effective
885 -- as of the IGS_AD_APPL.appl_dt.
886 cst_error CONSTANT VARCHAR2(1) := 'E';
887 cst_warn CONSTANT VARCHAR2(1) := 'W';
888 cst_course CONSTANT IGS_AD_APPL.s_admission_process_type%TYPE := 'COURSE';
889 v_pe_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE;
890 -- gmaheswa: Added START_DT <> END_DT OR END_DT IS NULL condition as part of bug 3882788
891 CURSOR c_api_pit IS
892 SELECT api.pe_person_id
893 FROM IGS_PE_ALT_PERS_ID api,
894 IGS_PE_PERSON_ID_TYP pit
895 WHERE api.pe_person_id = p_person_id AND
896 (api.start_dt IS NULL OR
897 (api.start_dt <= p_appl_dt AND
898 NVL(api.end_dt, IGS_GE_DATE.IGSCHAR('9999/01/01')) >= p_appl_dt) AND
899 (api.end_dt IS NULL OR api.start_dt <> api.end_dt)) AND
900 pit.person_id_type = api.person_id_type AND
901 pit.s_person_id_type = 'TAC';
902 BEGIN
903 IF (p_tac_appl_ind = 'Y') THEN
904 IF (p_s_admission_process_type <> cst_course) THEN
905 p_message_name := 'IGS_AD_SYSADM_PRCTYPE_TACAPPL';
906 p_return_type := cst_error;
907 RETURN FALSE;
908 END IF;
909 OPEN c_api_pit;
910 FETCH c_api_pit INTO v_pe_person_id;
911 IF (c_api_pit%NOTFOUND) THEN
912 CLOSE c_api_pit;
913 p_message_name := 'IGS_AD_TACAPPL_INDICATOR_Y';
914 p_return_type := cst_warn;
915 RETURN FALSE;
916 END IF;
917 CLOSE c_api_pit;
918 END IF;
919 p_message_name := null;
920 RETURN TRUE;
921 EXCEPTION
922 WHEN OTHERS THEN
923 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
924 FND_MESSAGE.SET_TOKEN('NAME','IGS_AD_VAL_AA.admp_val_aa_tac_appl');
925 IGS_GE_MSG_STACK.ADD;
926 App_Exception.Raise_Exception;
927 END;
928 END admp_val_aa_tac_appl;
929 END IGS_AD_VAL_AA;
[Home] [Help]