1 PACKAGE BODY igs_ad_val_acai_status AS
2 /* $Header: IGSAD24B.pls 120.8 2005/11/25 04:51:02 appldev ship $ */
3 --bug 1956374 msrinivi Removed duplicate func enrp_val_trnsfr_act 27 aug,01
4 -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
5 -- hreddych #2602077 SF Integration Added the FUNCTION admp_val_aods_update
6 --sarakshi 27-Feb-2003 Enh#2797116,modified procedure admp_val_lafs_coo ,added delete_falg check in the where clause
7 -- of the cursor c_coo
8 FUNCTION admp_val_acai_aeqs(
9 p_adm_entry_qual_status IN VARCHAR2 ,
10 p_adm_outcome_status IN VARCHAR2 ,
11 p_s_admission_process_type IN VARCHAR2 ,
12 p_message_name OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14 BEGIN -- admp_val_acai_aeqs
15 -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
16 DECLARE
17 v_message_name VARCHAR2(30);
18 v_s_adm_entry_qual_status igs_ad_ent_qf_stat.s_adm_entry_qual_status%TYPE;
19 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
20 BEGIN
21 -- Set the default message number
22 p_message_name := NULL;
23 -- Perform item level validations.
24 IF IGS_AD_VAL_ACAI_STATUS.admp_val_aeqs_item (
25 p_adm_entry_qual_status,
26 p_s_admission_process_type,
27 v_message_name) = FALSE THEN
28 p_message_name := v_message_name;
29 RETURN FALSE;
30 END IF;
31 -- Perform cross-status validations.
32 -- Get the system status values.
33 v_s_adm_entry_qual_status := IGS_AD_GEN_007.ADMP_GET_SAEQS (p_adm_entry_qual_status);
34 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (p_adm_outcome_status);
35 -- Validate against the admission outcome status.
36 IF IGS_AD_VAL_ACAI_STATUS.admp_val_aeqs_aos (
37 v_s_adm_entry_qual_status,
38 v_s_adm_outcome_status,
39 v_message_name) = FALSE THEN
40 p_message_name := v_message_name;
41 RETURN FALSE;
42 END IF;
43 RETURN TRUE;
44 END;
45 EXCEPTION
46 WHEN OTHERS THEN
47 IF p_message_name <> 'IGS_GE_UNHANDLED_EXP' AND FND_MSG_PUB.Count_Msg < 1 THEN
48 p_message_name := 'IGS_GE_UNHANDLED_EXP';
49 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
50 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aeqs');
51 IGS_GE_MSG_STACK.ADD;
52 END IF;
53 App_Exception.Raise_Exception;
54
55 END admp_val_acai_aeqs;
56 --
57 -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
58 FUNCTION admp_val_aeqs_item(
59 p_adm_entry_qual_status IN VARCHAR2 ,
60 p_s_admission_process_type IN VARCHAR2 ,
61 p_message_name OUT NOCOPY VARCHAR2 )
62 RETURN BOOLEAN AS
63 BEGIN -- admp_val_aeqs_item
64 -- Validate the IGS_AD_PS_APPL_INST.adm_entry_qual_status.
65 -- It must be open and valid.
66 DECLARE
67 v_message_name VARCHAR2(30);
68 v_s_adm_entry_qual_status igs_ad_ent_qf_stat.s_adm_entry_qual_status%TYPE;
69 BEGIN
70 -- Set the default message number
71 p_message_name := NULL;
72 -- Validate the closed indicator
73 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aeqs_closed(
74 p_adm_entry_qual_status,
75 v_message_name) THEN
76 p_message_name := v_message_name;
77 RETURN FALSE;
78 END IF;
79 -- Validate against the system admission process type.
80 v_s_adm_entry_qual_status := NVL(IGS_AD_GEN_007.ADMP_GET_SAEQS(
81 p_adm_entry_qual_status), 'NULL');
82 IF p_s_admission_process_type = 'NON-AWARD' THEN
83 -- The admission entry qualification status must be
84 -- Not Applicable for Non Award applications
85 IF v_s_adm_entry_qual_status <> 'NOT-APPLIC' THEN
86 p_message_name := 'IGS_AD_ADMENTRY_QUALIFY_ST';
87 RETURN FALSE;
88 END IF;
89 ELSE
90 -- The admission entry qualification status must be
91 -- a value other than Not Applicable for applications
92 -- that are not Non Award
93 IF v_s_adm_entry_qual_status = 'NOT-APPLIC' THEN
94 p_message_name := 'IGS_AD_ADM_ENTRY_QUALIFYST' ;
95 RETURN FALSE;
96 END IF;
97 END IF;
98 RETURN TRUE;
99 END;
100 END admp_val_aeqs_item;
101 --
102 -- Validate if IGS_AD_ENT_QF_STAT.adm_entry_qual_status is closed.
103 FUNCTION admp_val_aeqs_closed(
104 p_adm_entry_qual_status IN VARCHAR2 ,
105 p_message_name OUT NOCOPY VARCHAR2 )
106 RETURN BOOLEAN AS
107 BEGIN -- admp_val_aeqs_closed
108 -- Validate the adm_entry_qual_status closed indicator
109 DECLARE
110 CURSOR c_aeqs(
111 cp_adm_entry_qual_status IGS_AD_ENT_QF_STAT.adm_entry_qual_status%TYPE) IS
112 SELECT closed_ind
113 FROM IGS_AD_ENT_QF_STAT
114 WHERE adm_entry_qual_status = cp_adm_entry_qual_status;
115 v_aeqs_rec c_aeqs%ROWTYPE;
116 cst_yes CONSTANT CHAR := 'Y';
117 BEGIN
118 -- Set the default message number
119 p_message_name := NULL;
120 -- Cursor handling
121 OPEN c_aeqs(
122 p_adm_entry_qual_status);
123 FETCH c_aeqs INTO v_aeqs_rec;
124 IF c_aeqs%NOTFOUND THEN
125 CLOSE c_aeqs;
126 RETURN TRUE;
127 END IF;
128 CLOSE c_aeqs;
129 IF (v_aeqs_rec.closed_ind = cst_yes) THEN
130 p_message_name := 'IGS_AD_ADM_ENTRY_CLS_ST_CLOSE';
131 RETURN FALSE;
132 END IF;
133 -- Return the default value
134 RETURN TRUE;
135 END;
136 END admp_val_aeqs_closed;
137 --
138 -- Validates adm_entry_qual_status against adm_outcome_status.
139 FUNCTION admp_val_aeqs_aos(
140 p_s_adm_entry_qual_status IN VARCHAR2 ,
141 p_s_adm_outcome_status IN VARCHAR2 ,
142 p_message_name OUT NOCOPY VARCHAR2 )
143 RETURN BOOLEAN AS
144 BEGIN -- admp_val_aeqs_aos
145 -- This module validates IGS_AD_PS_APPL_INST.adm_entry_qual_status
146 -- against IGS_AD_PS_APPL_INST.adm_outcome_status
147 -- Validations are:
148 -- If the admission entry qualification status is applicable for the
149 -- admission application, then it cannot be pending if an offer has been made.
150 -- If the admission entry qualification status is applicable for the admission
151 -- application, then it cannot be not-qualified if an offer is being made.
152 DECLARE
153 cst_not_applic CONSTANT
154 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE:= 'NOT-APPLIC';
155 cst_pending CONSTANT
156 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'PENDING';
157 cst_not_qual CONSTANT
158 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'NOT-QUAL';
159 cst_offer CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'OFFER';
160 cst_cond_offer CONSTANT
161 IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'COND-OFFER';
162 cst_withdrawn CONSTANT
163 IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'WITHDRAWN';
164 cst_voided CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'VOIDED';
165 BEGIN
166 p_message_name := NULL;
167 -- Only validate the admission entry qualification status if
168 -- it has a system value other then not-applicable
169 IF (p_s_adm_entry_qual_status <> cst_not_applic) THEN
170 -- The admission entry qualification status cannot
171 -- be pending if an offer has been made.
172 IF (p_s_adm_entry_qual_status = cst_pending AND
173 p_s_adm_outcome_status IN (
174 cst_offer,
175 cst_cond_offer,
176 cst_withdrawn,
177 cst_voided)) THEN
178 p_message_name := 'IGS_AD_NOTBE_PENDING_OFR_MADE';
179 RETURN FALSE;
180 END IF;
181 -- The admission entry qualification status cannot
182 -- be not-qualified if an offer is being made.
183 IF (p_s_adm_entry_qual_status = cst_not_qual AND
184 p_s_adm_outcome_status IN (
185 cst_offer,
186 cst_cond_offer)) THEN
187 p_message_name := 'IGS_AD_NOTBE_NOTQUALIF_OFRMAD';
188 RETURN FALSE;
189 END IF;
190 END IF;
191
192 RETURN TRUE;
193 END;
194 EXCEPTION
195 WHEN OTHERS THEN
196 p_message_name := 'IGS_GE_UNHANDLED_EXP';
197 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
198 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aeqs_aos');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END admp_val_aeqs_aos;
202 --
203 -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
204 FUNCTION admp_val_acai_ads(
205 p_adm_doc_status IN VARCHAR2 ,
206 p_adm_outcome_status IN VARCHAR2 ,
207 p_adm_cndtnl_offer_status IN VARCHAR2 ,
208 p_s_admission_process_type IN VARCHAR2 ,
209 p_cond_offer_doc_allowed IN VARCHAR2,
210 p_message_name OUT NOCOPY VARCHAR2 )
211 RETURN BOOLEAN AS
212 BEGIN -- admp_val_acai_ads
213 -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
214 DECLARE
215 v_message_name VARCHAR2(30);
216 v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
217 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
218 v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
219 BEGIN
220 -- Set the default message number
221 p_message_name := NULL;
222 -- Perform item level validations.
223 IF IGS_AD_VAL_ACAI_STATUS.admp_val_ads_item (
224 p_adm_doc_status,
225 p_s_admission_process_type,
226 v_message_name) = FALSE THEN
227 p_message_name := v_message_name;
228 RETURN FALSE;
229 END IF;
230 -- Perform cross-status validations.
231 -- Get the system status values.
232 v_s_adm_doc_status := IGS_AD_GEN_007.ADMP_GET_SADS (p_adm_doc_status);
233 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (p_adm_outcome_status);
234 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS (p_adm_cndtnl_offer_status);
235 -- Validate against the admission outcome status.
236 IF IGS_AD_VAL_ACAI_STATUS.admp_val_ads_aos (
237 v_s_adm_doc_status,
238 v_s_adm_outcome_status,
239 v_s_adm_cndtnl_offer_status,
240 p_cond_offer_doc_allowed,
241 v_message_name) = FALSE THEN
242 p_message_name := v_message_name;
243 RETURN FALSE;
244 END IF;
245 RETURN TRUE;
246 END;
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF p_message_name <> 'IGS_GE_UNHANDLED_EXP' AND FND_MSG_PUB.Count_Msg < 1 THEN
250 p_message_name := 'IGS_GE_UNHANDLED_EXP';
251 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
252 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_ads');
253 IGS_GE_MSG_STACK.ADD;
254 END IF;
255 App_Exception.Raise_Exception;
256
257 END admp_val_acai_ads;
258 --
259 -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status.
260 FUNCTION admp_val_ads_item(
261 p_adm_doc_status IN VARCHAR2 ,
262 p_s_admission_process_type IN VARCHAR2 ,
263 p_message_name OUT NOCOPY VARCHAR2 )
264 RETURN BOOLEAN AS
265 BEGIN -- admp_val_ads_item
266 -- Validate the IGS_AD_PS_APPL_INST.adm_doc_status,
267 -- It must be open and valid
268 DECLARE
269 v_message_name VARCHAR2(30);
270 v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
271 BEGIN
272 -- Set the default message number
273 p_message_name := NULL;
274 -- Validate the closed indicator
275 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_ads_closed(
276 p_adm_doc_status,
277 v_message_name) THEN
278 p_message_name := v_message_name;
279 RETURN FALSE;
280 END IF;
281 -- Validate against the system admission process type.
282 v_s_adm_doc_status := NVL(IGS_AD_GEN_007.ADMP_GET_SADS(
283 p_adm_doc_status), 'NULL');
284 IF p_s_admission_process_type = 'NON-AWARD' THEN
285 -- The admission documentation status must be
286 -- Not Applicable for Non Award applications
287 IF v_s_adm_doc_status <> 'NOT-APPLIC' THEN
288 p_message_name := 'IGS_AD_ADMDOC_STATUS';
289 RETURN FALSE;
290 END IF;
291 ELSE
292 -- The admission documentation status must be
293 -- a value other than Not Applicable for applications
294 -- that are not Non Award
295 IF v_s_adm_doc_status = 'NOT-APPLIC' THEN
296 p_message_name := 'IGS_AD_ADM_DOC_STATUS';
297 RETURN FALSE;
298 END IF;
299 END IF;
300 RETURN TRUE;
301 END;
302 END admp_val_ads_item;
303 --
304 -- Validate if IGS_AD_DOC_STAT.adm_doc_status is closed.
305 FUNCTION admp_val_ads_closed(
306 p_adm_doc_status IN VARCHAR2 ,
307 p_message_name OUT NOCOPY VARCHAR2 )
308 RETURN BOOLEAN AS
309 BEGIN -- admp_val_ads_closed
310 -- Validate the IGS_AD_DOC_STAT closed indicator
311 DECLARE
312 CURSOR c_ads(
313 cp_adm_doc_status IGS_AD_DOC_STAT.adm_doc_status%TYPE) IS
314 SELECT closed_ind
315 FROM IGS_AD_DOC_STAT
316 WHERE adm_doc_status = cp_adm_doc_status;
317 v_ads_rec c_ads%ROWTYPE;
318 cst_yes CONSTANT CHAR := 'Y';
319 BEGIN
320 -- Set the default message number
321 p_message_name := NULL;
322 -- Cursor handling
323 OPEN c_ads(
324 p_adm_doc_status);
325 FETCH c_ads INTO v_ads_rec;
326 IF c_ads%NOTFOUND THEN
327 CLOSE c_ads;
328 RETURN TRUE;
329 END IF;
330 CLOSE c_ads;
331 IF (v_ads_rec.closed_ind = cst_yes) THEN
332 p_message_name := 'IGS_AD_ADMDOC_STATUS_CLOSED';
333 RETURN FALSE;
334 END IF;
335 -- Return the default value
336 RETURN TRUE;
337 END;
338 END admp_val_ads_closed;
339 --
340 -- Validates adm_doc_status against adm_outcome_status.
341 FUNCTION admp_val_ads_aos(
342 p_s_adm_doc_status IN VARCHAR2 ,
343 p_s_adm_outcome_status IN VARCHAR2 ,
344 p_s_adm_cndtnl_offer_status IN VARCHAR2 ,
345 p_cond_offer_doc_allowed IN VARCHAR2,
346 p_message_name OUT NOCOPY VARCHAR2 )
347 RETURN BOOLEAN AS
348 BEGIN -- admp_val_ads_aos
349 -- This module validates IGS_AD_PS_APPL_INST.adm_doc_status against
350 -- IGS_AD_PS_APPL_INST.adm_outcome_status.
351 -- Validations are:
352 -- If the admission documentation status is applicable for the admission
353 -- application, then it cannot be pending if an offer has been made.
354 -- The admission documentation status cannot be incomplete, unsatisfactory
355 -- or rejected if a non-conditional offer has been made or a conditional offer
356 -- has been made but documentation conditional offers are not allowed.
357 -- The admission documentation status cannot be incomple, unsatisfactory or
358 -- rejected if a conditional offer has been satisfied.
359 DECLARE
360 cst_not_applic CONSTANT
361 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'NOT-APPLIC';
362 cst_pending CONSTANT
363 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'PENDING';
364 cst_incomplete CONSTANT
365 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'INCOMPLETE';
366 cst_unsatisfac CONSTANT
367 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'UNSATISFAC';
368 cst_rejected_f CONSTANT
369 IGS_AD_ENT_QF_STAT.s_adm_entry_qual_status%TYPE := 'REJECTED-F';
370 cst_offer CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'OFFER';
371 cst_cond_offer CONSTANT
372 IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'COND-OFFER';
373 cst_withdrawn CONSTANT
374 IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'WITHDRAWN';
375 cst_voided CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE := 'VOIDED';
376 cst_satisfied CONSTANT
377 IGS_AD_CNDNL_OFRSTAT.s_adm_cndtnl_offer_status%TYPE := 'SATISFIED';
378 BEGIN
379 p_message_name := NULL;
380 -- Only validate the admission documentation status if
381 -- it has a system value other then not-applicable.
382 IF (p_s_adm_doc_status <> cst_not_applic) THEN
383 -- The admission documentation status cannot
384 -- be pending if an offer has been made.
385 IF (p_s_adm_doc_status = cst_pending AND
386 p_s_adm_outcome_status IN (
387 cst_offer,
388 cst_cond_offer,
389 cst_withdrawn,
390 cst_voided)) THEN
391 p_message_name := 'IGS_AD_NOTBE_PENDNG_OFR_MADE';
392 RETURN FALSE;
393 END IF;
394 -- The admission documentation status cannot be incomplete, unsatisfactory
395 -- or rejected if a non-conditional offer has been made, or a conditional
396 -- offer has been made, but documentation conditional offers are not
397 -- allowed.
398 IF p_s_adm_doc_status IN (
399 cst_incomplete,
400 cst_unsatisfac,
401 cst_rejected_f) THEN
402 IF p_s_adm_outcome_status = cst_offer THEN
403 p_message_name := 'IGS_AD_NOTBE_INCOMPL_OFR_MADE';
404 RETURN FALSE;
405 END IF;
406 IF p_s_adm_outcome_status = cst_cond_offer AND
407 p_cond_offer_doc_allowed = 'N' THEN
408 p_message_name := 'IGS_AD_ADMDOC_NOTBE_IMCOMPL';
409 RETURN FALSE;
410 END IF;
411 END IF;
412 -- The admission documentation status cannot be unsatisfactory
413 -- or rejected if a conditional offer has been satisfied.
414 IF (p_s_adm_doc_status IN (cst_incomplete, cst_unsatisfac, cst_rejected_f) AND
415 p_s_adm_outcome_status = cst_cond_offer AND
416 p_s_adm_cndtnl_offer_status = cst_satisfied) THEN
417 p_message_name := 'IGS_AD_NOTBE_INCOMP_OFR_MADE';
418 RETURN FALSE;
419 END IF;
420 END IF;
421
422 RETURN TRUE;
423 END;
424 EXCEPTION
425 WHEN OTHERS THEN
426 p_message_name := 'IGS_GE_UNHANDLED_EXP';
427 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
428 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_ads_aos');
429 IGS_GE_MSG_STACK.ADD;
430 App_Exception.Raise_Exception;
431 END admp_val_ads_aos;
432 --
433 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
434 FUNCTION admp_val_acai_aods(
435 p_person_id IN NUMBER ,
436 p_admission_appl_number IN NUMBER ,
437 p_nominated_course_cd IN VARCHAR2 ,
438 p_acai_sequence_number IN NUMBER ,
439 p_course_cd IN VARCHAR2 ,
440 p_adm_offer_dfrmnt_status IN VARCHAR2 ,
441 p_old_adm_dfrmnt_status IN VARCHAR2 ,
442 p_adm_offer_resp_status IN VARCHAR2 ,
443 p_deferral_allowed IN VARCHAR2,
444 p_s_admission_process_type IN VARCHAR2 ,
445 p_message_name OUT NOCOPY VARCHAR2 )
446 RETURN BOOLEAN AS
447 BEGIN -- admp_val_acai_aods
448 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
449 DECLARE
450 v_s_adm_offer_dfrmnt_status
451 IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
452 v_old_s_adm_dfrmnt_status
453 IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status%TYPE;
454 v_s_adm_offer_resp_status
455 IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
456 v_message_name VARCHAR2(30);
457 BEGIN
458 -- Set the default message number
459 p_message_name := NULL;
460 -- Perform item level validations.
461 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aods_item (
462 p_person_id,
463 p_admission_appl_number,
464 p_nominated_course_cd,
465 p_acai_sequence_number,
466 p_course_cd,
467 p_adm_offer_dfrmnt_status,
468 p_s_admission_process_type,
469 p_deferral_allowed,
470 v_message_name) THEN
471 p_message_name := v_message_name;
472 RETURN FALSE;
473 END IF;
474 -- Set local variables to system values
475 v_s_adm_offer_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS (
476 p_adm_offer_dfrmnt_status);
477 v_old_s_adm_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS (
478 p_old_adm_dfrmnt_status);
479 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
480 p_adm_offer_resp_status);
481 -- Validate against the admission offer response status.
482 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aods_aors (
483 v_s_adm_offer_dfrmnt_status,
484 v_old_s_adm_dfrmnt_status,
485 v_s_adm_offer_resp_status,
486 v_message_name) THEN
487 p_message_name := v_message_name;
488 RETURN FALSE;
489 END IF;
490 -- Return the default value
491 RETURN TRUE;
492 END;
493 EXCEPTION
494 WHEN OTHERS THEN
495 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
496 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aods');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 END admp_val_acai_aods;
500 --
501 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
502 FUNCTION admp_val_aods_item(
503 p_person_id IN NUMBER ,
504 p_admission_appl_number IN NUMBER ,
505 p_nominated_course_cd IN VARCHAR2 ,
506 p_acai_sequence_number IN NUMBER ,
507 p_course_cd IN VARCHAR2 ,
508 p_adm_offer_dfrmnt_status IN VARCHAR2 ,
509 p_s_admission_process_type IN VARCHAR2 ,
510 p_deferral_allowed IN VARCHAR2,
511 p_message_name OUT NOCOPY VARCHAR2 )
512 RETURN BOOLEAN AS
513 BEGIN -- admp_val_aods_item
514 -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status.
515 -- Perform item level validations only.
516 -- Validations are:
517 -- ? The adm_offer_dfrmnt_status must be open. (AODS01)
518 -- ? If the deferment is not allowed for the admission application,
519 -- then the admission offer deferment status must have a value of
520 -- not-applicable. (AODS02)
521 -- ? If the admission application is re-admission, course transfer
522 -- or non-Award, then the admission offer deferment status must have
523 -- a value of not-applicable. (AODS03)
524 -- ? Deferment cannot be rejected if the course has been
525 -- confirmed. (AODS04)
526 DECLARE
527 CURSOR c_sca IS
528 SELECT 'x'
529 FROM IGS_EN_STDNT_PS_ATT sca
530 WHERE sca.person_id = p_person_id AND
531 NVL(sca.adm_nominated_course_cd, 'NULL') =
532 NVL(p_nominated_course_cd, 'NULL') AND
533 NVL(sca.adm_admission_appl_number, -1) = NVL(p_admission_appl_number, -1) AND
534 NVL(sca.adm_sequence_number, -1) = NVL(p_acai_sequence_number, -1) AND
535 sca.student_confirmed_ind = 'Y';
536 v_sca_exists VARCHAR2(1);
537 v_s_adm_offer_dfrmnt_status
538 IGS_AD_OFRDFRMT_STAT.s_adm_offer_dfrmnt_status%TYPE;
539 v_message_name VARCHAR2(30);
540 BEGIN
541 -- Set the default message number
542 p_message_name := NULL;
543 -- Validate the closed indicator.
544 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aods_closed (
545 p_adm_offer_dfrmnt_status,
546 v_message_name) THEN
547 p_message_name := v_message_name;
548 RETURN FALSE;
549 END IF;
550 v_s_adm_offer_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS(
551 p_adm_offer_dfrmnt_status);
552 -- Validate the offer response status against the system admission
553 -- process type and the admission process category step.
554 IF v_s_adm_offer_dfrmnt_status <> 'NOT-APPLIC' THEN
555 -- Deferments are not allowed for this admission application.
556 -- The admission offer deferment status must be set to not-applicable.
557 IF p_deferral_allowed = 'N' THEN
558 -- deferment is not allowed
559 p_message_name := 'IGS_AD_DFRMNT_NOT_ALLOW';
560 RETURN FALSE;
561 END IF;
562 -- Validate the offer response status against the
563 -- system admission process type.
564 -- Deferment is not applicable for re-admission, course transfer
565 -- or non-Award admission applications. The admission offer
566 -- deferment status must be set to not-applicable.
567 IF p_s_admission_process_type IN (
568 'RE-ADMIT',
569 'TRANSFER',
570 'NON-AWARD') THEN
571 p_message_name := 'IGS_AD_DFRMNT_NOT_APPLICABLE';
572 RETURN FALSE;
573 END IF;
574 END IF;
575 -- Validate against student course attempt
576 IF v_s_adm_offer_dfrmnt_status = 'REJECTED' THEN
577 -- Cannot reject deferral if the course is confirmed
578 -- If the applicant accepted the initial offer,
579 -- then this status can be set to withdrawn.
580 OPEN c_sca;
581 FETCH c_sca INTO v_sca_exists;
582 IF c_sca%FOUND THEN
583 CLOSE c_sca;
584 p_message_name := 'IGS_AD_DFRMNT_NOT_REJECT';
585 RETURN FALSE;
586 END IF;
587 CLOSE c_sca;
588 END IF;
589 -- Return the default value
590 RETURN TRUE;
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF c_sca%ISOPEN THEN
594 CLOSE c_sca;
595 END IF;
596 RAISE;
597 END;
598 EXCEPTION
599 WHEN OTHERS THEN
600 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
601 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aods_item');
602 IGS_GE_MSG_STACK.ADD;
603 App_Exception.Raise_Exception;
604 END admp_val_aods_item;
605 --
606 -- Validate if IGS_AD_OFRDFRMT_STAT.adm_offer_dfrmnt_status is closed.
607 FUNCTION admp_val_aods_closed(
608 p_adm_offer_dfrmnt_status IN VARCHAR2 ,
609 p_message_name OUT NOCOPY VARCHAR2 )
610 RETURN BOOLEAN AS
611 BEGIN -- admp_val_aods_closed
612 -- Validate the adm_offer_dfrmnt_status closed indicator
613 DECLARE
614 CURSOR c_aods(
615 cp_adm_offer_dfrmnt_status
616 IGS_AD_OFRDFRMT_STAT.adm_offer_dfrmnt_status%TYPE) IS
617 SELECT closed_ind
618 FROM IGS_AD_OFRDFRMT_STAT
619 WHERE adm_offer_dfrmnt_status = cp_adm_offer_dfrmnt_status;
620 v_aods_rec c_aods%ROWTYPE;
621 cst_yes CONSTANT CHAR := 'Y';
622 BEGIN
623 -- Set the default message number
624 p_message_name := NULL;
625 -- Cursor handling
626 OPEN c_aods(
627 p_adm_offer_dfrmnt_status);
628 FETCH c_aods INTO v_aods_rec;
629 IF c_aods%NOTFOUND THEN
630 CLOSE c_aods;
631 RETURN TRUE;
632 END IF;
633 CLOSE c_aods;
634 IF (v_aods_rec.closed_ind = cst_yes) THEN
635 p_message_name := 'IGS_AD_ADMOFR_DEFER_ST_CLOSED';
636 RETURN FALSE;
637 END IF;
638 -- Return the default value
639 RETURN TRUE;
640 END;
641 EXCEPTION
642 WHEN OTHERS THEN
643 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
644 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aods_closed');
645 IGS_GE_MSG_STACK.ADD;
646 App_Exception.Raise_Exception;
647 END admp_val_aods_closed;
648 --
649 -- Validates adm_offer_dfrmnt_status against adm_offer_resp_status.
650 FUNCTION admp_val_aods_aors(
651 p_s_adm_offer_dfrmnt_status IN VARCHAR2 ,
652 p_old_s_adm_dfrmnt_status IN VARCHAR2 ,
653 p_s_adm_offer_resp_status IN VARCHAR2 ,
654 p_message_name OUT NOCOPY VARCHAR2 )
655 RETURN BOOLEAN AS
656 BEGIN -- admp_val_aods_aors
657 -- This module validates IGS_AD_PS_APPL_INST.adm_offer_dfrmnt_status
658 -- against IGS_AD_PS_APPL_INST.adm_offer_resp_status.
659 -- Validations are:
660 -- * The admission offer deferment status must have a system value
661 -- of not-applicable when the admission offer response status is
662 -- pending, lapsed or not-applicable. (AODS05)
663 -- * The admission offer deferment status cannot be changed from
664 -- not-applicable when the admission offer response status does
665 -- not have a system value of deferral. (AODS06)
666 -- * The admission offer deferment status must not have a system
667 -- value of not-applicable when the admission offer response
668 -- status is deferral. (AODS07, AORS10)
669 DECLARE
670 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
671 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
672 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
673 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
674 BEGIN
675 -- Set the default message number
676 p_message_name := NULL;
677 IF p_s_adm_offer_dfrmnt_status <> cst_not_applic THEN
678 -- Cannot set to values other than not applicable if the
679 -- offer response status is pending, lapsed or not applicable
680 IF p_s_adm_offer_resp_status IN (
681 cst_pending,
682 cst_lapsed,
683 cst_not_applic) THEN
684 p_message_name := 'IGS_AD_OFFER_DFRMNT_STATUS';
685 RETURN FALSE;
686 END IF;
687 -- Cannot change from not applicable if the offer
688 -- response status is not deferral
689 IF p_old_s_adm_dfrmnt_status = cst_not_applic AND
690 p_s_adm_offer_resp_status <> cst_deferral THEN
691 p_message_name := 'IGS_AD_DFRMNT_STATUS_NOT_CHG';
692 RETURN FALSE;
693 END IF;
694 END IF;
695 -- Cannot be not applicable if offer response
696 -- status is deferral.
697 IF p_s_adm_offer_dfrmnt_status = cst_not_applic AND
698 p_s_adm_offer_resp_status = cst_deferral THEN
699 p_message_name := 'IGS_AD_DFRMNT_ST_NOT_APPL';
700 Return FALSE;
701 END IF;
702 -- Return the default value
703 RETURN TRUE;
704 END;
705 EXCEPTION
706 WHEN OTHERS THEN
707 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
708 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aods_aors');
709 IGS_GE_MSG_STACK.ADD;
710 App_Exception.Raise_Exception;
711 END admp_val_aods_aors;
712 --
713 -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
714 FUNCTION admp_val_acai_lafs(
715 p_late_adm_fee_status IN VARCHAR2 ,
716 p_late_appl_allowed IN VARCHAR2,
717 p_late_fees_required IN VARCHAR2,
718 p_cond_offer_fee_allowed IN VARCHAR2,
719 p_appl_dt IN DATE ,
720 p_course_cd IN VARCHAR2 ,
721 p_crv_version_number IN NUMBER ,
722 p_acad_cal_type IN VARCHAR2 ,
723 p_location_cd IN VARCHAR2 ,
724 p_attendance_mode IN VARCHAR2 ,
725 p_attendance_type IN VARCHAR2 ,
726 p_adm_cal_type IN VARCHAR2 ,
727 p_adm_ci_sequence_number IN NUMBER ,
728 p_admission_cat IN VARCHAR2 ,
729 p_s_admission_process_type IN VARCHAR2 ,
730 p_adm_outcome_status IN VARCHAR2 ,
731 p_adm_cndtnl_offer_status IN VARCHAR2 ,
732 p_message_name OUT NOCOPY VARCHAR2 )
733 RETURN BOOLEAN AS
734 BEGIN -- admp_val_acai_lafs
735 -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
736 DECLARE
737 v_s_late_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
738 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
739 v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE ;
740 v_message_name VARCHAR2(30);
741 BEGIN
742 -- Set the default message number
743 p_message_name := NULL;
744 -- Perform item level validations.
745 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_item (
746 p_late_adm_fee_status,
747 p_late_appl_allowed,
748 v_message_name) THEN
749 p_message_name := v_message_name;
750 RETURN FALSE;
751 END IF;
752 v_s_late_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS (
753 p_late_adm_fee_status);
754 -- Validate against the course offering option details.
755 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_coo (
756 v_s_late_adm_fee_status,
757 p_late_appl_allowed,
758 p_late_fees_required,
759 p_appl_dt,
760 p_course_cd,
761 p_crv_version_number,
762 p_acad_cal_type,
763 p_location_cd,
764 p_attendance_mode,
765 p_attendance_type,
766 p_adm_cal_type,
767 p_adm_ci_sequence_number,
768 p_admission_cat,
769 p_s_admission_process_type,
770 v_message_name) THEN
771 p_message_name := v_message_name;
772 RETURN FALSE;
773 END IF;
774 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (
775 p_adm_outcome_status);
776 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS (
777 p_adm_cndtnl_offer_status);
778 -- Validate against the admission outcome status.
779 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_aos (
780 v_s_late_adm_fee_status,
781 v_s_adm_outcome_status,
782 v_s_adm_cndtnl_offer_status,
783 p_cond_offer_fee_allowed,
784 v_message_name) THEN
785 p_message_name := v_message_name;
786 RETURN FALSE;
787 END IF;
788 -- Return the default value
789 RETURN TRUE;
790 END;
791 EXCEPTION
792 WHEN OTHERS THEN
793 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
794 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_lafs');
795 IGS_GE_MSG_STACK.ADD;
796 App_Exception.Raise_Exception;
797 END admp_val_acai_lafs;
798 --
799 -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
800 FUNCTION admp_val_lafs_item(
801 p_late_adm_fee_status IN VARCHAR2 ,
802 p_late_appl_allowed IN VARCHAR2,
803 p_message_name OUT NOCOPY VARCHAR2 )
804 RETURN BOOLEAN AS
805 BEGIN -- admp_val_lafs_item
806 -- Validate the IGS_AD_PS_APPL_INST.late_adm_fee_status.
807 -- Perform item level validations only.
808 -- Validations are:
809 -- * The late_adm_fee_status must be open. (LAFS01)
810 -- * If late applications are not allowed the late_adm_fee_status
811 -- must have a system value of not-applicable. (LAFS02)
812 DECLARE
813 v_message_name VARCHAR2(30);
814 v_s_late_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
815 BEGIN
816 -- Set the default message number
817 p_message_name := NULL;
818 -- Validate the closed indicator
819 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_afs_closed (
820 p_late_adm_fee_status,
821 v_message_name) THEN
822 p_message_name := v_message_name;
823 RETURN FALSE;
824 END IF;
825 -- Get the late admission fee status system value.
826 v_s_late_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(
827 p_late_adm_fee_status);
828 -- Validate when late applications are not allowed.
829 IF p_late_appl_allowed = 'N' AND
830 v_s_late_adm_fee_status <> 'NOT-APPLIC' THEN
831 -- If late applications are not allowed the late admission
832 -- fee status must have a value of not-applicable.
833 p_message_name := 'IGS_AD_LATE_ADMFEE_STATUS';
834 RETURN FALSE;
835 END IF;
836 -- Return the default value
837 RETURN TRUE;
838 END;
839 EXCEPTION
840 WHEN OTHERS THEN
841 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
842 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_item');
843 IGS_GE_MSG_STACK.ADD;
844 App_Exception.Raise_Exception;
845 END admp_val_lafs_item;
846 --
847 -- Validate if IGS_AD_FEE_STAT.adm_fee_status is closed.
848 FUNCTION admp_val_afs_closed(
849 p_adm_fee_status IN VARCHAR2 ,
850 p_message_name OUT NOCOPY VARCHAR2 )
851 RETURN BOOLEAN AS
852 BEGIN -- admp_val_afs_closed
853 -- Validate if IGS_AD_FEE_STAT.adm_fee_status is closed
854 DECLARE
855 CURSOR c_afs IS
856 SELECT closed_ind
857 FROM IGS_AD_FEE_STAT
858 WHERE adm_fee_status = p_adm_fee_status;
859 v_closed_ind IGS_AD_FEE_STAT.closed_ind%TYPE;
860 BEGIN
861 -- Set the default message number
862 p_message_name := NULL;
863 -- Cursor handling
864 OPEN c_afs;
865 FETCH c_afs INTO v_closed_ind;
866 IF c_afs%FOUND THEN
867 IF (v_closed_ind = 'Y') THEN
868 CLOSE c_afs;
869 p_message_name := 'IGS_AD_ADMFEE_STATUS_CLOSED';
870 RETURN FALSE;
871 END IF;
872 END IF;
873 -- Return the default value
874 CLOSE c_afs;
875 RETURN TRUE;
876 END;
877 EXCEPTION
878 WHEN OTHERS THEN
879 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
880 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_afs_closed');
881 IGS_GE_MSG_STACK.ADD;
882 App_Exception.Raise_Exception;
883 END admp_val_afs_closed;
884 --
885 -- Validates late_adm_fee_status against the course offering option.
886 FUNCTION admp_val_lafs_coo(
887 p_s_late_adm_fee_status IN VARCHAR2 ,
888 p_late_appl_allowed IN VARCHAR2,
889 p_late_fees_required IN VARCHAR2,
890 p_appl_dt IN DATE ,
891 p_course_cd IN VARCHAR2 ,
892 p_crv_version_number IN NUMBER ,
893 p_acad_cal_type IN VARCHAR2 ,
894 p_location_cd IN VARCHAR2 ,
895 p_attendance_mode IN VARCHAR2 ,
896 p_attendance_type IN VARCHAR2 ,
897 p_adm_cal_type IN VARCHAR2 ,
898 p_adm_ci_sequence_number IN NUMBER ,
899 p_admission_cat IN VARCHAR2 ,
900 p_s_admission_process_type IN VARCHAR2 ,
901 p_message_name OUT NOCOPY VARCHAR2
902 )
903 RETURN BOOLEAN AS
904 BEGIN -- admp_val_lafs_coo
905 -- This module validates the IGS_AD_PS_APPL_INST.late_adm_fee_status
906 -- against the course offering option details of the admission course
907 -- application instance.
908 -- Validations are :
909 -- If late applications are allowed and the application is late and late
910 -- fees are required, then the late_adm_fee_status must not have a system
911 -- value of not-applicable. (LAFS03)
912 DECLARE
913 CURSOR c_coo IS
914 SELECT coo.location_cd,
915 coo.attendance_mode,
916 coo.attendance_type
917 FROM IGS_PS_OFR_OPT coo
918 WHERE coo.course_cd = p_course_cd AND
919 coo.version_number = p_crv_version_number AND
920 coo.cal_type = p_acad_cal_type AND
921 coo.delete_flag = 'N';
922 v_coo_match BOOLEAN DEFAULT TRUE;
923 v_return_flag BOOLEAN DEFAULT FALSE;
924 v_message_name VARCHAR2(30) DEFAULT NULL;
925 BEGIN
926 -- Set the default message number
927 p_message_name := NULL;
928 -- Validate when late applications are allowed and the application is
929 -- late and late fees are required.
930 IF p_late_appl_allowed = 'Y' AND
931 p_late_fees_required = 'Y' THEN
932 v_coo_match := FALSE;
933 FOR v_coo_rec IN c_coo LOOP
934 -- Restrict the course offering options to match on input parameters
935 IF (p_location_cd IS NULL OR
936 v_coo_rec.location_cd = p_location_cd) AND
937 (p_attendance_mode IS NULL OR
938 v_coo_rec.attendance_mode = p_attendance_mode) AND
939 (p_attendance_type IS NULL OR
940 v_coo_rec.attendance_type = p_attendance_type) THEN
941 v_coo_match := TRUE;
942 -- Check if the app[lication is late.
943 IF IGS_AD_VAL_ACAI.admp_val_acai_late(
944 p_appl_dt,
945 p_course_cd,
946 p_crv_version_number,
947 p_acad_cal_type,
948 v_coo_rec.location_cd,
949 v_coo_rec.attendance_mode,
950 v_coo_rec.attendance_type,
951 p_adm_cal_type,
952 p_adm_ci_sequence_number,
953 p_admission_cat,
954 p_s_admission_process_type,
955 p_late_appl_allowed,
956 v_message_name) THEN
957 v_return_flag := TRUE;
958 EXIT;
959 END IF;
960 END IF;
961 END LOOP;
962 IF v_return_flag THEN
963 RETURN TRUE;
964 END IF;
965 -- Check if any course offering options were validated.
966 -- If they were, and this point is reached, then the
967 -- admission course application instance must be late.
968 IF v_coo_match THEN
969 IF p_s_late_adm_fee_status = 'NOT-APPLIC' THEN
970 -- The late admission fee status must have a value
971 -- other than not-applicable for a late application
972 -- that requires late fees.
973 p_message_name := 'IGS_AD_LATE_ADMFEE';
974 RETURN FALSE;
975 END IF;
976 END IF;
977 END IF;
978 -- Return the default value
979 RETURN TRUE;
980 EXCEPTION
981 WHEN OTHERS THEN
982 IF c_coo%ISOPEN THEN
983 CLOSE c_coo;
984 END IF;
985 RAISE;
986 END;
987 EXCEPTION
988 WHEN OTHERS THEN
989 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
990 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_coo');
991 IGS_GE_MSG_STACK.ADD;
992 App_Exception.Raise_Exception;
993 END admp_val_lafs_coo;
994 --
995 -- Validates late_adm_fee_status against adm_outcome_status.
996 FUNCTION admp_val_lafs_aos(
997 p_s_late_adm_fee_status IN VARCHAR2 ,
998 p_s_adm_outcome_status IN VARCHAR2 ,
999 p_s_adm_cndtnl_offer_status IN VARCHAR2 ,
1000 p_cond_offer_fee_allowed IN VARCHAR2,
1001 p_message_name OUT NOCOPY VARCHAR2 )
1002 RETURN BOOLEAN AS
1003 BEGIN -- admp_val_lafs_aos
1004 -- This module validates IGS_AD_PS_APPL_INST.adm_late_fee_status
1005 -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1006 -- Validations are:
1007 -- * The late admission fee status cannot be pending
1008 -- if an offer has been made. (LAFS04)
1009 -- * An offer cannot be made if late fees have been
1010 -- assessed but not received in total. (LAFS05)
1011 -- * Fees cannot be outstanding for a satisfied conditional offer.
1012 DECLARE
1013 BEGIN
1014 -- Set the default message number
1015 p_message_name := 0;
1016 -- Validate when late fees are pending and an offer has been made.
1017 -- Cannot be determining late fees if an offer has been made.
1018 IF p_s_late_adm_fee_status = 'PENDING' AND
1019 p_s_adm_outcome_status IN (
1020 'OFFER',
1021 'COND-OFFER',
1022 'WITHDRAWN',
1023 'VOIDED') THEN
1024 p_message_name := 'IGS_AD_LATE_ADMFEE_NOTPENDING';
1025 RETURN FALSE;
1026 END IF;
1027 -- Validate when late fees are assessed and an offer is being made.
1028 IF p_s_late_adm_fee_status = 'ASSESSED' THEN
1029 -- Cannot make an offer if late fees have been assessed but not
1030 -- received in total.
1031 IF p_s_adm_outcome_status = 'OFFER' THEN
1032 p_message_name := 'IGS_AD_OFFER_CANNOT_MADE';
1033 RETURN FALSE;
1034 END IF;
1035 IF p_s_adm_outcome_status = 'COND-OFFER' AND
1036 p_cond_offer_fee_allowed = 'N' THEN
1037 p_message_name := 'IGS_AD_LATEFEE_CANNOT_ASSESS';
1038 RETURN FALSE;
1039 END IF;
1040 -- Cannot have outstanding fees for a conditional offer
1041 -- that has been satisfied
1042 IF p_s_adm_outcome_status = 'COND-OFFER' AND
1043 p_s_adm_cndtnl_offer_status = 'SATISFIED' THEN
1044 p_message_name := 'IGS_AD_FEE_CANNOT_BE_OUTSTAND';
1045 RETURN FALSE;
1046 END IF;
1047 END IF;
1048 -- Return the default value
1049 RETURN TRUE;
1050 END;
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1054 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_aos');
1055 IGS_GE_MSG_STACK.ADD;
1056 App_Exception.Raise_Exception;
1057 END admp_val_lafs_aos;
1058 --
1059 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1060 FUNCTION admp_val_acai_aors(
1061 p_person_id IN NUMBER ,
1062 p_admission_appl_number IN NUMBER ,
1063 p_nominated_course_cd IN VARCHAR2 ,
1064 p_acai_sequence_number IN NUMBER ,
1065 p_course_cd IN VARCHAR2 ,
1066 p_adm_offer_resp_status IN VARCHAR2 ,
1067 p_old_adm_offer_resp_status IN VARCHAR2 ,
1068 p_adm_outcome_status IN VARCHAR2 ,
1069 p_adm_offer_dfrmnt_status IN VARCHAR2 ,
1070 p_old_adm_offer_dfrmnt_status IN VARCHAR2 ,
1071 p_adm_outcome_status_auth_dt IN DATE ,
1072 p_actual_response_dt IN DATE ,
1073 p_adm_cal_type IN VARCHAR2 ,
1074 p_adm_ci_sequence_number IN NUMBER ,
1075 p_admission_cat IN VARCHAR2 ,
1076 p_s_admission_process_type IN VARCHAR2 ,
1077 p_deferral_allowed IN VARCHAR2,
1078 p_multi_offer_allowed IN VARCHAR2,
1079 p_multi_offer_limit IN NUMBER ,
1080 p_pre_enrol_step IN VARCHAR2 ,
1081 p_cndtnl_off_must_be_stsfd_ind IN VARCHAR2,
1082 p_cndtnl_offer_satisfied_dt IN DATE ,
1083 p_called_from IN VARCHAR2 ,
1084 p_message_name OUT NOCOPY VARCHAR2,
1085 p_decline_ofr_reason IN VARCHAR2, -- IGSM
1086 p_attent_other_inst_cd IN VARCHAR2 -- igsm
1087 )
1088 RETURN BOOLEAN AS
1089 BEGIN -- admp_val_acai_aors
1090 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1091 DECLARE
1092 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
1093 cst_form CONSTANT VARCHAR2(5) := 'FORM';
1094 cst_trg_br CONSTANT VARCHAR2(7) := 'TRG_BR';
1095 cst_trg_as CONSTANT VARCHAR2(7) := 'TRG_AS';
1096 cst_notcoming CONSTANT VARCHAR2(10) := 'NOT-COMING';
1097 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
1098 cst_otherinst CONSTANT VARCHAR2(10) := 'OTHER-INST';
1099 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
1100 v_old_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
1101 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
1102 v_s_adm_offer_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
1103 v_old_s_adm_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
1104 v_message_name VARCHAR2(30);
1105
1106 BEGIN
1107 -- Set the default message number
1108 p_message_name := NULL;
1109 IF (p_called_from IN (cst_form, cst_trg_br)) THEN
1110 -- Perform item level validations.
1111 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aors_item (
1112 p_person_id,
1113 p_admission_appl_number,
1114 p_nominated_course_cd,
1115 p_acai_sequence_number,
1116 p_course_cd,
1117 p_adm_offer_resp_status,
1118 p_actual_response_dt,
1119 p_s_admission_process_type,
1120 p_deferral_allowed,
1121 p_pre_enrol_step,
1122 v_message_name,
1123 p_decline_ofr_reason,
1124 p_attent_other_inst_cd ) THEN
1125 p_message_name := v_message_name;
1126 RETURN FALSE;
1127 END IF;
1128
1129 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
1130 p_adm_offer_resp_status);
1131 v_old_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (
1132 p_old_adm_offer_resp_status);
1133 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (
1134 p_adm_outcome_status);
1135
1136 IF v_s_adm_offer_resp_status IN (cst_notcoming, cst_rejected) AND p_decline_ofr_reason IS NULL THEN --arvsrini igsm
1137 p_message_name := 'IGS_AD_ADMOFR_WITH_REAS';
1138 RETURN FALSE;
1139 END IF;
1140
1141 IF p_decline_ofr_reason = cst_otherinst AND p_attent_other_inst_cd IS NULL THEN --arvsrini igsm
1142 p_message_name := 'IGS_AD_NO_OTH_INST';
1143 RETURN FALSE;
1144 END IF;
1145
1146
1147 -- Validate against the admission outcome status.
1148 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aors_aos (
1149 v_s_adm_offer_resp_status,
1150 v_old_s_adm_offer_resp_status,
1151 v_s_adm_outcome_status,
1152 p_adm_outcome_status_auth_dt,
1153 v_message_name) THEN
1154 p_message_name := v_message_name;
1155 RETURN FALSE;
1156 END IF;
1157 v_s_adm_offer_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS (
1158 p_adm_offer_dfrmnt_status);
1159 v_old_s_adm_dfrmnt_status := IGS_AD_GEN_008.ADMP_GET_SAODS (
1160 p_old_adm_offer_dfrmnt_status);
1161 -- Validate against the admission offer deferment status.
1162 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_aods_aors (
1163 v_s_adm_offer_dfrmnt_status,
1164 v_old_s_adm_dfrmnt_status,
1165 v_s_adm_offer_resp_status,
1166 v_message_name) THEN
1167 p_message_name := v_message_name;
1168 RETURN FALSE;
1169 END IF;
1170 -- Validate a conditional offer that must be satisfied before
1171 -- it can be accepted.
1172 IF v_s_adm_offer_resp_status = cst_accepted AND
1173 p_cndtnl_off_must_be_stsfd_ind = 'Y' AND
1174 p_cndtnl_offer_satisfied_dt IS NULL THEN
1175 p_message_name := 'IGS_AD_CONDOFR_ACCEPTED';
1176 RETURN FALSE;
1177 END IF;
1178 END IF;
1179 -- Perform the following validations when the module has been called from the
1180 -- Form or from the after statement database trigger.
1181 IF (p_called_from IN (cst_form, cst_trg_as)) THEN
1182 -- Determine the system admission outcome status and system admission
1183 -- offer response status. Will have already been determined if
1184 -- called from FORM.
1185 IF p_called_from = cst_trg_as THEN
1186 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (p_adm_outcome_status);
1187 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS (p_adm_offer_resp_status);
1188 END IF;
1189 -- Validate multiple offers.
1190 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_offer_mult (
1191 p_person_id,
1192 p_admission_appl_number,
1193 p_nominated_course_cd,
1194 p_acai_sequence_number,
1195 p_course_cd,
1196 v_s_adm_outcome_status,
1197 v_s_adm_offer_resp_status,
1198 p_adm_cal_type,
1199 p_adm_ci_sequence_number,
1200 p_admission_cat,
1201 p_s_admission_process_type,
1202 p_multi_offer_allowed,
1203 p_multi_offer_limit,
1204 p_message_name) THEN
1205 RETURN FALSE;
1206 END IF;
1207 END IF;
1208 RETURN TRUE;
1209 END;
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1213 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aors');
1214 IGS_GE_MSG_STACK.ADD;
1215 App_Exception.Raise_Exception;
1216 END admp_val_acai_aors;
1217 --
1218 -- Validate the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1219 FUNCTION admp_val_aors_item(
1220 p_person_id IN NUMBER ,
1221 p_admission_appl_number IN NUMBER ,
1222 p_nominated_course_cd IN VARCHAR2 ,
1223 p_acai_sequence_number IN NUMBER ,
1224 p_course_cd IN VARCHAR2 ,
1225 p_adm_offer_resp_status IN VARCHAR2 ,
1226 p_actual_response_dt IN DATE ,
1227 p_s_admission_process_type IN VARCHAR2 ,
1228 p_deferral_allowed IN VARCHAR2 ,
1229 p_pre_enrol_step IN VARCHAR2,
1230 p_message_name OUT NOCOPY VARCHAR2,
1231 p_decline_ofr_reason IN VARCHAR2,
1232 p_attent_other_inst_cd IN VARCHAR2
1233
1234 )
1235 RETURN BOOLEAN AS
1236 BEGIN -- admp_val_aors_item
1237 -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_resp_status.
1238 -- Perform item level validations only.
1239 -- Validations are:
1240 -- * The adm_offer_resp_status must be open. (AORS01)
1241 -- * The admission offer response status cannot be set to pending for
1242 -- a re-admission application. (AORS02)
1243 -- * The admission offer response status cannot be set to pending when
1244 -- the applicant has already responded. (AORS06)
1245 -- * The admission offer response status cannot be set to rejected when
1246 -- the applicant has confirmed their course attempt. (AORS08)
1247 -- * If the deferment is not allowed for the admission application, then
1248 -- the admission offer response status cannot be set to deferral. (AORS05)
1249 -- * If the admission application is re-admission, course transfer or
1250 -- non-Award, then the admission offer response status cannot be set
1251 -- to deferral. (AORS04)
1252 -- * The admission offer response status cannot be set to deferral when
1253 -- the applicant has confirmed their course attempt. (AORS09)
1254 -- * The admission offer response status cannot be set to lapsed for a
1255 -- re-admission application. (AORS03)
1256 -- * The admission offer response status cannot be set to lapsed when the
1257 -- applicant has already responded. (AORS07)
1258 -- * The admission offer response status cannot be set to accepted if the
1259 -- admission course application is a transfer and IGS_PS_STDNT_TRN
1260 -- details do not exist. (AORS17)
1261 -- (these are created via ENRF4150 ? course Transfer)
1262 DECLARE
1263 CURSOR c_sca_conf IS
1264 SELECT sca.course_attempt_status
1265 FROM IGS_EN_STDNT_PS_ATT sca
1266 WHERE sca.person_id = p_person_id AND
1267 sca.course_cd = p_course_cd AND
1268 sca.adm_admission_appl_number IS NOT NULL AND
1269 sca.adm_admission_appl_number = p_admission_appl_number AND
1270 sca.adm_nominated_course_cd IS NOT NULL AND
1271 sca.adm_nominated_course_cd = p_nominated_course_cd AND
1272 sca.adm_sequence_number IS NOT NULL AND
1273 sca.adm_sequence_number = p_acai_sequence_number AND
1274 sca.student_confirmed_ind = 'Y';
1275 v_sca_conf_rec c_sca_conf%ROWTYPE;
1276 v_message_name VARCHAR2(30);
1277 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
1278 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
1279 cst_readmit CONSTANT VARCHAR2(10) := 'RE-ADMIT';
1280 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
1281 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1282 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1283 cst_transfer CONSTANT VARCHAR2(10) := 'TRANSFER';
1284 cst_nonaward CONSTANT VARCHAR2(10) := 'NON-AWARD';
1285 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
1286 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
1287 BEGIN
1288 -- Set the default message number
1289 p_message_name := NULL;
1290 -- Validate the closed indicator.
1291 IF IGS_AD_VAL_ACAI_STATUS.admp_val_aors_closed (
1292 p_adm_offer_resp_status,
1293 v_message_name) = FALSE THEN
1294 p_message_name := v_message_name;
1295 RETURN FALSE;
1296 END IF;
1297 -- Validate each value of offer response status.
1298 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(
1299 p_adm_offer_resp_status);
1300 IF v_s_adm_offer_resp_status = cst_pending THEN
1301 -- Re-admission only has accepted or rejected response status.
1302 IF p_s_admission_process_type = cst_readmit THEN
1303 p_message_name := 'IGS_AD_ADMOFR_READM_APPL';
1304 RETURN FALSE;
1305 END IF;
1306 -- Cannot set to pending if a response has been given
1307 -- (this is recognised by the actual response date being set).
1308 IF p_actual_response_dt IS NOT NULL THEN
1309 p_message_name := 'IGS_AD_ADMOFR_RESPONDED';
1310 RETURN FALSE;
1311 END IF;
1312 END IF;
1313
1314 /*
1315 IF v_s_adm_offer_resp_status = cst_rejected THEN
1316 -- Can only reject if applicant has not already enrolled
1317 OPEN c_sca_conf;
1318 FETCH c_sca_conf INTO v_sca_conf_rec;
1319 IF c_sca_conf%FOUND THEN
1320 CLOSE c_sca_conf;
1321 IF p_s_admission_process_type = cst_readmit THEN
1322 IF v_sca_conf_rec.course_attempt_status NOT IN (
1323 cst_discontin,
1324 cst_lapsed) THEN
1325 p_message_name := 'IGS_AD_ADMOFR_NOT_SET_REJECT' ;
1326 RETURN FALSE;
1327 END IF;
1328 ELSE
1329 p_message_name := 'IGS_AD_ADMOFR_NOT_SET_REJECT';
1330 RETURN FALSE;
1331 END IF;
1332 ELSE -- %NOTFOUND
1333 CLOSE c_sca_conf;
1334 END IF;
1335 END IF;
1336 */
1337 IF v_s_adm_offer_resp_status = cst_deferral THEN
1338 IF p_deferral_allowed = 'N' THEN
1339 -- deferment not allowed
1340 p_message_name := 'IGS_AD_DFRMNT_NOT_ALLOW_ADM';
1341 RETURN FALSE;
1342 END IF;
1343 -- Can only defer if process types are course or SHORT-ADM
1344 IF p_s_admission_process_type IN (
1345 cst_readmit,
1346 cst_transfer,
1347 cst_nonaward) THEN
1348 p_message_name := 'IGS_AD_DFRMNT_NOT_APPLI_READM';
1349 RETURN FALSE;
1350 END IF;
1351
1352 /*
1353 -- Can only defer if the applicant has not already enrolled
1354 -- (NOTE, can use same cursor as With rejected)
1355 OPEN c_sca_conf;
1356 FETCH c_sca_conf INTO v_sca_conf_rec;
1357 IF c_sca_conf%FOUND THEN
1358 CLOSE c_sca_conf;
1359 p_message_name := 'IGS_AD_OFRST_NOTSET_DEFERRAL';
1360 RETURN FALSE;
1361 END IF;
1362 CLOSE c_sca_conf;
1363 */
1364 END IF;
1365 IF v_s_adm_offer_resp_status = cst_lapsed THEN
1366 -- Re-admission only has accepted or rejected response status
1367 IF p_s_admission_process_type = cst_readmit THEN
1368 p_message_name := 'IGS_AD_OFRST_NOTSET_LAPSED';
1369 RETURN FALSE;
1370 END IF;
1371 -- Can only set to lapsed if pending or lapsed (lapsing
1372 -- is done by a background process)
1373 -- This is recognised by the setting of the actual response date
1374 IF p_actual_response_dt IS NOT NULL THEN
1375 p_message_name := 'IGS_AD_OFRST_NOTSET_RESPONDED';
1376 RETURN FALSE;
1377 END IF;
1378 END IF;
1379 IF v_s_adm_offer_resp_status = cst_accepted THEN
1380 -- Can only accept admission application for transfer if
1381 -- IGS_PS_STDNT_TRN details exist
1382 IF p_s_admission_process_type = cst_transfer THEN
1383 IF IGS_EN_VAL_SCA.enrp_val_trnsfr_acpt(
1384 p_person_id,
1385 p_course_cd,
1386 NULL, -- student confirmed ind
1387 p_admission_appl_number,
1388 p_nominated_course_cd,
1389 p_adm_offer_resp_status,
1390 v_message_name) = FALSE THEN
1391 p_message_name := v_message_name;
1392 RETURN FALSE;
1393 END IF;
1394 END IF;
1395 END IF;
1396
1397
1398
1399 -- Return the default value
1400 RETURN TRUE;
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 IF c_sca_conf%ISOPEN THEN
1404 CLOSE c_sca_conf;
1405 END IF;
1406 RAISE;
1407 END;
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1411 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aors_item');
1412 IGS_GE_MSG_STACK.ADD;
1413 App_Exception.Raise_Exception;
1414 END admp_val_aors_item;
1415 --
1416 -- Validate if IGS_AD_OFR_RESP_STAT.adm_offer_resp_status is closed.
1417 FUNCTION admp_val_aors_closed(
1418 p_adm_offer_resp_status IN VARCHAR2 ,
1419 p_message_name OUT NOCOPY VARCHAR2 )
1420 RETURN BOOLEAN AS
1421 BEGIN
1422 DECLARE
1423 v_closed_ind IGS_AD_OFR_RESP_STAT.closed_ind%TYPE;
1424 CURSOR c_aors IS
1425 SELECT aors.closed_ind
1426 FROM IGS_AD_OFR_RESP_STAT aors
1427 WHERE aors.adm_offer_resp_status = p_adm_offer_resp_status;
1428 BEGIN
1429 -- Validate if IGS_AD_OFR_RESP_STAT.adm_offer_resp_status is closed.
1430 OPEN c_aors;
1431 FETCH c_aors INTO v_closed_ind;
1432 IF (c_aors%FOUND) THEN
1433 IF (v_closed_ind = 'Y') THEN
1434 CLOSE c_aors;
1435 p_message_name := 'IGS_AD_ADMOFR_RESP_ST_CLOSED';
1436 RETURN FALSE;
1437 END IF;
1438 END IF;
1439 CLOSE c_aors;
1440 p_message_name := NULL;
1441 RETURN TRUE;
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1445 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aors_closed');
1446 IGS_GE_MSG_STACK.ADD;
1447 App_Exception.Raise_Exception;
1448 END;
1449 END admp_val_aors_closed;
1450 --
1451 -- Validates adm_offer_resp_status against adm_outcome_status.
1452 FUNCTION admp_val_aors_aos(
1453 p_s_adm_offer_resp_status IN VARCHAR2 ,
1454 p_old_s_adm_offer_resp_status IN VARCHAR2 ,
1455 p_s_adm_outcome_status IN VARCHAR2 ,
1456 p_adm_outcome_status_auth_dt IN DATE ,
1457 p_message_name OUT NOCOPY VARCHAR2 )
1458 RETURN BOOLEAN AS
1459 BEGIN -- admp_val_aors_aos
1460 -- This module validates the IGS_AD_PS_APPL_INST.adm_offer_resp_status
1461 -- against IGS_AD_PS_APPL_INST.adm_outcome_status.
1462 -- Validations are:
1463 -- ? The admission offer response status cannot be pending if an offer
1464 -- has not been made. (AORS11)
1465 -- ? The admission offer response status cannot be accepted, rejected
1466 -- or deferral unless an offer has been made. (AORS12)
1467 -- ? The admission offer response status cannot be changed to accepted,
1468 -- rejected or deferral from lapsed unless overriding of status
1469 -- validation is permitted. (AORS13)
1470 -- ? The admission offer response cannot be lapsed unless an offer has
1471 -- been made. (AORS14)
1472 -- ? The admission offer response cannot be not-applicable when an offer
1473 -- has been made. (AORS15)
1474 DECLARE
1475 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
1476 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
1477 cst_no_quota CONSTANT VARCHAR2(10) := 'NO-QUOTA';
1478 cst_accepted CONSTANT VARCHAR2(10) := 'ACCEPTED';
1479 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
1480 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
1481 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
1482 cst_voided CONSTANT VARCHAR2(10) := 'VOIDED';
1483 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
1484 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
1485 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
1486 BEGIN
1487 -- Set the default message number
1488 p_message_name := NULL;
1489 -- Cannot be pending without having made an offer
1490 IF p_s_adm_offer_resp_status = cst_pending AND
1491 p_s_adm_outcome_status IN (
1492 cst_pending,
1493 cst_rejected,
1494 cst_no_quota) THEN
1495 p_message_name := 'IGS_AD_OFRST_NOTPENDING';
1496 RETURN FALSE;
1497 END IF;
1498 IF p_s_adm_offer_resp_status IN (
1499 cst_accepted,
1500 cst_rejected,
1501 cst_deferral) THEN
1502 -- Cannot have an offer response if not being made an offer
1503 IF p_s_adm_outcome_status NOT IN (
1504 cst_offer,
1505 cst_cond_offer,
1506 cst_withdrawn,
1507 cst_voided) THEN
1508 p_message_name := 'IGS_AD_OFRST_NOTACCEPTED';
1509 RETURN FALSE;
1510 END IF;
1511
1512 END IF;
1513 -- Cannot lapse if offer has not been made
1514 IF p_s_adm_offer_resp_status = cst_lapsed AND
1515 p_s_adm_outcome_status NOT IN (
1516 cst_offer,
1517 cst_cond_offer,
1518 cst_withdrawn,
1519 cst_voided) THEN
1520 p_message_name := 'IGS_AD_OFR_RESP_NOTLAPSED';
1521 RETURN FALSE;
1522 END IF;
1523 -- Cannot be not applicable if offer is being made
1524 IF p_s_adm_offer_resp_status = cst_not_applic AND
1525 p_s_adm_outcome_status IN (
1526 cst_offer,
1527 cst_cond_offer) THEN
1528 p_message_name := 'IGS_AD_OFR_RESP_NOTAPPLICABLE';
1529 RETURN FALSE;
1530 END IF;
1531 -- Return the default value
1532 RETURN TRUE;
1533 END;
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1537 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aors_aos');
1538 IGS_GE_MSG_STACK.ADD;
1539 App_Exception.Raise_Exception;
1540 END admp_val_aors_aos;
1541 --
1542 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1543 FUNCTION admp_val_acai_aos(
1544 p_person_id IN NUMBER ,
1545 p_admission_appl_number IN NUMBER ,
1546 p_nominated_course_cd IN VARCHAR2 ,
1547 p_acai_sequence_number IN NUMBER ,
1548 p_course_cd IN VARCHAR2 ,
1549 p_crv_version_number IN NUMBER ,
1550 p_location_cd IN VARCHAR2 ,
1551 p_attendance_mode IN VARCHAR2 ,
1552 p_attendance_type IN VARCHAR2 ,
1553 p_unit_set_cd IN VARCHAR2 ,
1554 p_us_version_number IN NUMBER ,
1555 p_acad_cal_type IN VARCHAR2 ,
1556 p_acad_ci_sequence_number IN NUMBER ,
1557 p_adm_cal_type IN VARCHAR2 ,
1558 p_adm_ci_sequence_number IN NUMBER ,
1559 p_admission_cat IN VARCHAR2 ,
1560 p_s_admission_process_type IN VARCHAR2 ,
1561 p_appl_dt IN DATE ,
1562 p_fee_cat IN VARCHAR2 ,
1563 p_correspondence_cat IN VARCHAR2 ,
1564 p_enrolment_cat IN VARCHAR2 ,
1565 p_adm_outcome_status IN VARCHAR2 ,
1566 p_old_adm_outcome_status IN VARCHAR2 ,
1567 p_adm_doc_status IN VARCHAR2 ,
1568 p_adm_fee_status IN VARCHAR2 ,
1569 p_late_adm_fee_status IN VARCHAR2 ,
1570 p_adm_cndtnl_offer_status IN VARCHAR2 ,
1571 p_adm_entry_qual_status IN VARCHAR2 ,
1572 p_adm_offer_resp_status IN VARCHAR2 ,
1573 p_old_adm_offer_resp_status IN VARCHAR2 ,
1574 p_adm_outcome_status_auth_dt IN DATE ,
1575 p_set_outcome_allowed IN VARCHAR2 ,
1576 p_cond_offer_assess_allowed IN VARCHAR2,
1577 p_cond_offer_fee_allowed IN VARCHAR2 ,
1578 p_cond_offer_doc_allowed IN VARCHAR2,
1579 p_late_appl_allowed IN VARCHAR2 ,
1580 p_fees_required IN VARCHAR2 ,
1581 p_multi_offer_allowed IN VARCHAR2 ,
1582 p_multi_offer_limit IN NUMBER ,
1583 p_pref_allowed IN VARCHAR2,
1584 p_unit_set_appl IN VARCHAR2,
1585 p_check_person_encumb IN VARCHAR2,
1586 p_check_course_encumb IN VARCHAR2,
1587 p_called_from IN VARCHAR2 ,
1588 p_message_name OUT NOCOPY VARCHAR2 )
1589 RETURN BOOLEAN AS
1590 BEGIN -- admp_val_acai_aos
1591 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status
1592 DECLARE
1593 cst_form CONSTANT VARCHAR2(6) := 'FORM';
1594 cst_trg_br CONSTANT VARCHAR2(6) := 'TRG_BR';
1595 cst_trg_as CONSTANT VARCHAR2(6) := 'TRG_AS';
1596 v_message_name VARCHAR2(30);
1597 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
1598 v_old_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
1599 v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
1600 v_s_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
1601 v_late_s_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
1602 v_s_adm_entry_qual_status igs_ad_ent_qf_stat.s_adm_entry_qual_status%TYPE;
1603 v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
1604 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
1605 v_old_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
1606 BEGIN
1607 -- Perform the following validations when the module has been called
1608 -- from the form or from the before row database trigger
1609 IF (p_called_from IN (
1610 cst_form,
1611 cst_trg_br)) THEN
1612 -- Perform item level validations
1613 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_aos_item(
1614 p_adm_outcome_status,
1615 p_old_adm_outcome_status,
1616 p_adm_fee_status,
1617 p_set_outcome_allowed,
1618 p_cond_offer_assess_allowed,
1619 p_cond_offer_fee_allowed,
1620 p_cond_offer_doc_allowed,
1621 p_person_id,
1622 p_admission_appl_number,
1623 p_nominated_course_cd,
1624 p_acai_sequence_number,
1625 v_message_name) = FALSE) THEN
1626 p_message_name := v_message_name;
1627 RETURN FALSE;
1628 END IF;
1629 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(p_adm_outcome_status);
1630 v_old_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(p_old_adm_outcome_status);
1631 v_s_adm_doc_status := IGS_AD_GEN_007.ADMP_GET_SADS(p_adm_doc_status);
1632 v_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(p_adm_fee_status);
1633 v_late_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(p_late_adm_fee_status);
1634 -- Validate against the other admission course
1635 -- application instance status values
1636 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_aos_status(
1637 v_s_adm_outcome_status,
1638 v_old_s_adm_outcome_status,
1639 v_s_adm_doc_status,
1640 v_s_adm_fee_status,
1641 v_late_s_adm_fee_status,
1642 p_cond_offer_assess_allowed,
1643 p_cond_offer_fee_allowed,
1644 p_cond_offer_doc_allowed,
1645 p_late_appl_allowed,
1646 p_fees_required,
1647 v_message_name) = FALSE) THEN
1648 p_message_name := v_message_name;
1649 RETURN FALSE;
1650 END IF;
1651 v_s_adm_entry_qual_status := IGS_AD_GEN_007.ADMP_GET_SAEQS(p_adm_entry_qual_status);
1652 -- Validate the admission outcome status against
1653 -- the admission entry qualification status
1654 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_aeqs_aos(
1655 v_s_adm_entry_qual_status,
1656 v_s_adm_outcome_status,
1657 v_message_name) = FALSE) THEN
1658 p_message_name := v_message_name;
1659 RETURN FALSE;
1660 END IF;
1661 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS(p_adm_cndtnl_offer_status);
1662 -- Validate the admission outcome status
1663 -- against the admission documentation status
1664 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_ads_aos(
1665 v_s_adm_doc_status,
1666 v_s_adm_outcome_status,
1667 v_s_adm_cndtnl_offer_status,
1668 p_cond_offer_doc_allowed,
1669 v_message_name) = FALSE) THEN
1670 p_message_name := v_message_name;
1671 RETURN FALSE;
1672 END IF;
1673 -- Validate the admission outcome status
1674 -- against the late admission fee status
1675 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_lafs_aos(
1676 v_late_s_adm_fee_status,
1677 v_s_adm_outcome_status,
1678 v_s_adm_cndtnl_offer_status,
1679 p_cond_offer_fee_allowed,
1680 v_message_name) = FALSE) THEN
1681 p_message_name := v_message_name;
1682 RETURN FALSE;
1683 END IF;
1684 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status);
1685 v_old_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(p_old_adm_offer_resp_status);
1686 -- Validate the admission outcome status against
1687 -- the admission offer response status
1688 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_aors_aos(
1689 v_s_adm_offer_resp_status,
1690 v_old_s_adm_offer_resp_status,
1691 v_s_adm_outcome_status,
1692 p_adm_outcome_status_auth_dt,
1693 v_message_name) = FALSE) THEN
1694 p_message_name := v_message_name;
1695 RETURN FALSE;
1696 END IF;
1697 END IF;
1698 IF (p_called_from IN (
1699 cst_form,
1700 cst_trg_as)) THEN
1701 -- Determine the system admission outcome status and system admission offer
1702 -- response status.
1703 -- If called from Form then this will have already been determined.
1704 IF p_called_from = cst_trg_as THEN
1705 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(p_adm_outcome_status);
1706 v_s_adm_offer_resp_status := IGS_AD_GEN_008.ADMP_GET_SAORS(p_adm_offer_resp_status);
1707 END IF;
1708 -- Validate the admission course application instance outcome
1709 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_acai_otcome(
1710 p_person_id,
1711 p_admission_appl_number,
1712 p_nominated_course_cd,
1713 p_acai_sequence_number,
1714 p_course_cd,
1715 p_crv_version_number,
1716 p_location_cd,
1717 p_attendance_mode,
1718 p_attendance_type,
1719 p_unit_set_cd,
1720 p_us_version_number,
1721 p_acad_cal_type,
1722 p_acad_ci_sequence_number,
1723 p_adm_cal_type,
1724 p_adm_ci_sequence_number,
1725 p_admission_cat,
1726 p_s_admission_process_type,
1727 p_appl_dt,
1728 p_fee_cat,
1729 p_correspondence_cat,
1730 p_enrolment_cat,
1731 v_s_adm_outcome_status,
1732 p_adm_outcome_status_auth_dt,
1733 p_check_person_encumb,
1734 p_check_course_encumb,
1735 p_pref_allowed,
1736 p_late_appl_allowed,
1737 p_unit_set_appl,
1738 p_called_from,
1739 v_message_name) = FALSE) THEN
1740 p_message_name := v_message_name;
1741 RETURN FALSE;
1742 END IF;
1743 -- Validate multiple offers
1744 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_offer_mult(
1745 p_person_id,
1746 p_admission_appl_number,
1747 p_nominated_course_cd,
1748 p_acai_sequence_number,
1749 p_course_cd,
1750 v_s_adm_outcome_status,
1751 v_s_adm_offer_resp_status,
1752 p_adm_cal_type,
1753 p_adm_ci_sequence_number,
1754 p_admission_cat,
1755 p_s_admission_process_type,
1756 p_multi_offer_allowed,
1757 p_multi_offer_limit,
1758 v_message_name) = FALSE) THEN
1759 p_message_name := v_message_name;
1760 RETURN FALSE;
1761 END IF;
1762 END IF;
1763 p_message_name := NULL;
1764 RETURN TRUE;
1765 END;
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1769 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_aos');
1770 IGS_GE_MSG_STACK.ADD;
1771 App_Exception.Raise_Exception;
1772 END admp_val_acai_aos;
1773 --
1774 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1775 FUNCTION admp_val_aos_item(
1776 p_adm_outcome_status IN VARCHAR2 ,
1777 p_old_adm_outcome_status IN VARCHAR2 ,
1778 p_adm_fee_status IN VARCHAR2 ,
1779 p_set_outcome_allowed IN VARCHAR2 ,
1780 p_cond_offer_assess_allowed IN VARCHAR2,
1781 p_cond_offer_fee_allowed IN VARCHAR2,
1782 p_cond_offer_doc_allowed IN VARCHAR2,
1783 p_person_id IN NUMBER ,
1784 p_admission_appl_number IN NUMBER ,
1785 p_nominated_course_cd IN VARCHAR2 ,
1786 p_acai_sequence_number IN NUMBER ,
1787 p_message_name OUT NOCOPY VARCHAR2 )
1788 RETURN BOOLEAN AS
1789 BEGIN -- admp_val_aos_item
1790 -- This module validates the IGS_AD_PS_APPL_INST.adm_outcome_status.
1791 -- The adm_outcome_status must be open. (AOS01)
1792 -- The admission outcome status may not be set to a value other than pending
1793 -- when setting of the admission outcome status is not allowed for the
1794 -- admission application. (AOS02)
1795 -- A conditional offer cannot be made when conditional offers are not allowed
1796 -- for the admission application. (AOS22)
1797 -- The admission outcome status can only be changed to withdrawn or voided
1798 -- after an offer has been made. (AOS17)
1799 -- A conditional offer cannot be made when application fees are outstanding
1800 -- and fee conditional offers are not allowed. (AOS25)
1801
1802 -- Enh# : 2217104, DLD Admit to Future Term.
1803 -- Navin Sinha 13-Feb-2002, Added condition for 'OFFER-FUTURE-TERM'.
1804 -- 1. s_adm_outcome_status can be set to 'OFFER-FUTURE-TERM' only if s_admission_process_type IN ('COURSE', 'NON-AWARD', 'SHORT-ADM')
1805 DECLARE
1806 cst_pending CONSTANT VARCHAR2(7) := 'PENDING';
1807 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
1808 cst_withdrawn CONSTANT VARCHAR2(9) := 'WITHDRAWN';
1809 cst_voided CONSTANT VARCHAR2(6) := 'VOIDED';
1810 cst_rejected CONSTANT VARCHAR2(8) := 'REJECTED';
1811 cst_no_quota CONSTANT VARCHAR2(8) := 'NO-QUOTA';
1812 cst_received CONSTANT VARCHAR2(8) := 'RECEIVED';
1813 cst_exempt CONSTANT VARCHAR2(8) := 'EXEMPT';
1814 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
1815 cst_deleted CONSTANT VARCHAR2(7) := 'DELETED';
1816 cst_unconfirmed CONSTANT VARCHAR2(11) := 'UNCONFIRM';
1817 cst_offer_future_term CONSTANT VARCHAR2(20) := 'OFFER-FUTURE-TERM';
1818 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
1819 v_old_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
1820 v_s_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
1821 v_message_name VARCHAR2(30);
1822 v_dummy VARCHAR2(1);
1823 CURSOR c_sca IS
1824 SELECT 'x'
1825 FROM IGS_EN_STDNT_PS_ATT sca
1826 WHERE sca.person_id = p_person_id AND
1827 sca.adm_admission_appl_number IS NOT NULL AND
1828 sca.adm_admission_appl_number = p_admission_appl_number AND
1829 sca.adm_nominated_course_cd IS NOT NULL AND
1830 sca.adm_nominated_course_cd = p_nominated_course_cd AND
1831 sca.adm_sequence_number IS NOT NULL AND
1832 sca.adm_sequence_number = p_acai_sequence_number AND
1833 sca.course_attempt_status NOT IN (cst_deleted, cst_unconfirmed);
1834
1835 CURSOR c_sap(cp_person_id NUMBER, cp_admission_appl_number NUMBER) IS
1836 SELECT s_admission_process_type
1837 FROM igs_ad_appl
1838 WHERE person_id = cp_person_id
1839 AND admission_appl_number = cp_admission_appl_number;
1840 c_sap_rec c_sap%ROWTYPE;
1841 BEGIN
1842 p_message_name := NULL;
1843 -- Validate the closed indicator
1844 IF(IGS_AD_VAL_ACAI_STATUS.admp_val_aos_closed (
1845 p_adm_outcome_status,
1846 v_message_name) = FALSE) THEN
1847 p_message_name := v_message_name;
1848 RETURN FALSE;
1849 END IF;
1850 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS (
1851 p_adm_outcome_status);
1852 -- Validate the admission outcome status against the admission process
1853 -- category steps.
1854 -- Validate if outcome can be set.
1855 IF(p_set_outcome_allowed = 'N' AND
1856 v_s_adm_outcome_status <> cst_pending AND
1857 p_adm_outcome_status <> p_old_adm_outcome_status) THEN
1858 -- The admission outcome status must be pending when
1859 -- the outcome cannot be set.
1860 p_message_name := 'IGS_AD_OFRST_VALUE_PENDING';
1861 RETURN FALSE;
1862 END IF;
1863 -- Validate if a conditional offer can be made.
1864 IF(p_cond_offer_assess_allowed = 'N' AND
1865 p_cond_offer_fee_allowed = 'N' AND
1866 p_cond_offer_doc_allowed = 'N' AND
1867 v_s_adm_outcome_status = cst_cond_offer) THEN
1868 -- The admission outcome status status must be conditional offers
1869 -- when conditional offers are not allowed.
1870 p_message_name := 'IGS_AD_CONDOFR_NOT_MADE';
1871 RETURN FALSE;
1872 END IF;
1873 v_old_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(
1874 p_old_adm_outcome_status);
1875 -- Validate change of the admission outcome status value.
1876 IF(v_s_adm_outcome_status IN (cst_withdrawn, cst_voided) AND
1877 v_old_s_adm_outcome_status IN (cst_pending, cst_rejected, cst_no_quota) AND
1878 NVL(igs_ad_cancel_reconsider.g_cancel_recons_on,'N') <> 'Y' )THEN
1879 -- Cannot withdraw or void and unless an offer is made first. In case the update is made from the cancel
1880 -- reconsideration job, this restriction should not be there.
1881 p_message_name := 'IGS_AD_OUTCOME_STATUS_CHG';
1882 RETURN FALSE;
1883 END IF;
1884 IF v_s_adm_outcome_status IN (cst_pending, cst_rejected, cst_no_quota) THEN
1885 OPEN c_sca;
1886 FETCH c_sca INTO v_dummy;
1887 IF (c_sca%FOUND) THEN
1888 -- Cannot set outcome pending, rejected or no-quota when the
1889 -- applicant has an existing course attempt.
1890 CLOSE c_sca;
1891 p_message_name := 'IGS_AD_OUTCOME_CANNOT_SETTO';
1892 RETURN FALSE;
1893 END IF;
1894 CLOSE c_sca;
1895 END IF;
1896
1897 -- Enh# : 2217104, DLD Admit to Future Term.
1898 IF v_s_adm_outcome_status = cst_offer_future_term THEN
1899 OPEN c_sap(p_person_id, p_admission_appl_number);
1900 FETCH c_sap INTO c_sap_rec;
1901 IF (c_sap%FOUND) AND c_sap_rec.s_admission_process_type NOT IN ('COURSE', 'NON-AWARD', 'SHORT-ADM') THEN
1902 CLOSE c_sap;
1903 p_message_name := 'IGS_AD_OUTCOME_FUTURE_TERM';
1904 RETURN FALSE;
1905 END IF;
1906 CLOSE c_sap;
1907 END IF;
1908
1909 v_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS (
1910 p_adm_fee_status);
1911 -- Validate a non-fee conditional offer.
1912 IF(v_s_adm_outcome_status = cst_cond_offer AND
1913 p_cond_offer_fee_allowed = 'N' AND
1914 v_s_adm_fee_status NOT IN (cst_received, cst_exempt, cst_not_applic)) THEN
1915 p_message_name := 'IGS_AD_APPLFEES_OUTSTANDING';
1916 RETURN FALSE;
1917 END IF;
1918 RETURN TRUE;
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 IF c_sca%ISOPEN THEN
1922 CLOSE c_sca;
1923 END IF;
1924 IF c_sap%ISOPEN THEN
1925 CLOSE c_sap;
1926 END IF;
1927 RAISE;
1928 END;
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1932 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aos_item'|| SQLERRM);
1933 IGS_GE_MSG_STACK.ADD;
1934 App_Exception.Raise_Exception;
1935 END admp_val_aos_item;
1936 --
1937 -- Validate if IGS_AD_OU_STAT.adm_outcome_status is closed.
1938 FUNCTION admp_val_aos_closed(
1939 p_adm_outcome_status IN VARCHAR2 ,
1940 p_message_name OUT NOCOPY VARCHAR2 )
1941 RETURN BOOLEAN AS
1942 BEGIN
1943 DECLARE
1944 v_closed_ind IGS_AD_OU_STAT.closed_ind%TYPE;
1945 CURSOR c_aos IS
1946 SELECT aos.closed_ind
1947 FROM IGS_AD_OU_STAT aos
1948 WHERE aos.adm_outcome_status = p_adm_outcome_status;
1949 BEGIN
1950 -- Validate if IGS_AD_OU_STAT.adm_outcome_status is closed.
1951 OPEN c_aos;
1952 FETCH c_aos INTO v_closed_ind;
1953 IF (c_aos%FOUND) THEN
1954 IF (v_closed_ind = 'Y') THEN
1955 CLOSE c_aos;
1956 p_message_name := 'IGS_AD_ADM_OUCOME_ST_CLOSED';
1957 RETURN FALSE;
1958 END IF;
1959 END IF;
1960 CLOSE c_aos;
1961 p_message_name := NULL;
1962 RETURN TRUE;
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1966 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aos_closed');
1967 IGS_GE_MSG_STACK.ADD;
1968 App_Exception.Raise_Exception;
1969 END;
1970 END admp_val_aos_closed;
1971 --
1972 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
1973 FUNCTION admp_val_aos_status(
1974 p_s_adm_outcome_status IN VARCHAR2 ,
1975 p_old_s_adm_outcome_status IN VARCHAR2 ,
1976 p_s_adm_doc_status IN VARCHAR2 ,
1977 p_s_adm_fee_status IN VARCHAR2 ,
1978 p_late_s_adm_fee_status IN VARCHAR2 ,
1979 p_cond_offer_assess_allowed IN VARCHAR2,
1980 p_cond_offer_fee_allowed IN VARCHAR2,
1981 p_cond_offer_doc_allowed IN VARCHAR2,
1982 p_late_appl_allowed IN VARCHAR2,
1983 p_fees_required IN VARCHAR2,
1984 p_message_name OUT NOCOPY VARCHAR2 )
1985 RETURN BOOLEAN AS
1986 BEGIN -- admp_val_aos_status
1987 -- This module validates the IGS_AD_PS_APPL_INST.adm_outcome_status
1988 -- against
1989 -- other statuses in IGS_AD_PS_APPL_INST.
1990 -- Validations are:
1991 -- If a documentation conditional offer is being made, then the admission
1992 -- documentation status must be incomplete. (AOS24).
1993 -- If a fee conditional offer is being made, then the admission application
1994 -- fee status must be assessed or the late admission fee status must be
1995 -- assessed. (AOS27).
1996 -- An offer cannot be made while there are outstanding admission application
1997 -- fees. (AFS04, AFS05, AOS20)
1998 DECLARE
1999 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
2000 cst_incomplete CONSTANT VARCHAR2(10) := 'INCOMPLETE';
2001 cst_assessed CONSTANT VARCHAR2(8) := 'ASSESSED';
2002 cst_offer CONSTANT VARCHAR2(5) := 'OFFER';
2003 cst_pending CONSTANT VARCHAR2(7) := 'PENDING';
2004 BEGIN
2005 p_message_name := NULL;
2006 -- Validate when outcome status is changed to conditional offer.
2007 IF(p_s_adm_outcome_status = cst_cond_offer AND
2008 p_old_s_adm_outcome_status <> cst_cond_offer) THEN
2009 IF(p_cond_offer_assess_allowed = 'N') THEN -- assessor conditions allowed
2010 IF(p_cond_offer_doc_allowed = 'Y') THEN-- documentation conditions allowed
2011 --Check for a documentation condition
2012 IF(p_s_adm_doc_status = cst_incomplete) THEN
2013 p_message_name := NULL;
2014 RETURN TRUE;
2015 END IF;
2016 END IF;
2017 IF(p_cond_offer_fee_allowed = 'Y') THEN -- fee conditions allowed
2018 IF(p_s_adm_fee_status = cst_assessed) THEN
2019 p_message_name := NULL;
2020 RETURN TRUE;
2021 END IF;
2022 -- Check for late fee condition
2023 IF(p_late_appl_allowed = 'Y') THEN
2024 IF(p_late_s_adm_fee_status = cst_assessed) THEN
2025 p_message_name := NULL;
2026 RETURN TRUE;
2027 END IF;
2028 END IF;
2029 END IF;
2030 -- If reached here, then it is invalid to make a conditional offer
2031 p_message_name := 'IGS_AD_INVALID_COND_OFFER';
2032 RETURN FALSE;
2033 END IF;
2034 END IF;
2035 -- Validate the application fees when outcome status is set to offer.
2036 IF(p_s_adm_outcome_status = cst_offer) THEN
2037 IF(p_fees_required = 'Y') THEN
2038 -- Cannot make an offer if admission fees are outstanding
2039 IF(p_s_adm_fee_status IN (cst_pending, cst_assessed)) THEN
2040 p_message_name := 'IGS_AD_ADMAPL_FEES_OUTSTANDIN';
2041 RETURN FALSE;
2042 END IF;
2043 END IF;
2044 END IF;
2045 RETURN TRUE;
2046 END;
2047 EXCEPTION
2048 WHEN OTHERS THEN
2049 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2050 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aos_status');
2051 IGS_GE_MSG_STACK.ADD;
2052 App_Exception.Raise_Exception;
2053 END admp_val_aos_status;
2054 --
2055 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
2056 FUNCTION admp_val_acai_otcome(
2057 p_person_id IN NUMBER ,
2058 p_admission_appl_number IN NUMBER ,
2059 p_nominated_course_cd IN VARCHAR2 ,
2060 p_acai_sequence_number IN NUMBER ,
2061 p_course_cd IN VARCHAR2 ,
2062 p_crv_version_number IN NUMBER ,
2063 p_location_cd IN VARCHAR2 ,
2064 p_attendance_mode IN VARCHAR2 ,
2065 p_attendance_type IN VARCHAR2 ,
2066 p_unit_set_cd IN VARCHAR2 ,
2067 p_us_version_number IN NUMBER ,
2068 p_acad_cal_type IN VARCHAR2 ,
2069 p_acad_ci_sequence_number IN NUMBER ,
2070 p_adm_cal_type IN VARCHAR2 ,
2071 p_adm_ci_sequence_number IN NUMBER ,
2072 p_admission_cat IN VARCHAR2 ,
2073 p_s_admission_process_type IN VARCHAR2 ,
2074 p_appl_dt IN DATE ,
2075 p_fee_cat IN VARCHAR2 ,
2076 p_correspondence_cat IN VARCHAR2 ,
2077 p_enrolment_cat IN VARCHAR2 ,
2078 p_s_adm_outcome_status IN VARCHAR2 ,
2079 p_adm_outcome_status_auth_dt IN DATE ,
2080 p_check_person_encumb IN VARCHAR2,
2081 p_check_course_encumb IN VARCHAR2,
2082 p_pref_allowed IN VARCHAR2 ,
2083 p_late_appl_allowed IN VARCHAR2,
2084 p_unit_set_appl IN VARCHAR2,
2085 p_called_from IN VARCHAR2 ,
2086 p_message_name OUT NOCOPY VARCHAR2 )
2087 RETURN BOOLEAN AS
2088 BEGIN -- admp_val_acai_otcome
2089 -- Validate the IGS_AD_PS_APPL_INST.adm_outcome_status.
2090 -- Validations are -
2091 -- * The outcome cannot be set to pending for a non-preference application for
2092 -- which an outcome letter has been created. (This is with the exception of
2093 -- reconsideration and deferment processing). (AOS03)
2094 -- * The outcome cannot be set to pending for a preference application for
2095 -- which an outcome letter has been created and there are no other admission
2096 -- course application instances with an outcome of other than pending.
2097 -- (This is with the exception of reconsideration and deferment processing).
2098 -- (AOS04)
2099 -- * An admission course application instance cannot be offered while the
2100 -- outcome of admission units is still pending. (AOS05)
2101 -- * An admission course application instance cannot be offered if it is
2102 -- incomplete. (AOS06)
2103 -- * An admission course application instance cannot be offered if IGS_PE_PERSON
2104 -- encumbrance checking is required for the admission application and the
2105 -- IGS_PE_PERSON has an encumbrance. (AOS07)
2106 -- * An admission course application instance cannot be offered if course
2107 -- encumbrance checking is required for the admission application and the
2108 -- IGS_PE_PERSON has an encumbrance on the course being offered. (AOS08)
2109 -- * An admission course application instance cannot be offered if the full
2110 -- components of a valid course offering pattern are not specified. NOTE,
2111 -- late applications can be overridden. (AOS09)
2112 -- * An admission course application instance cannot be offered if the
2113 -- existing student course attempt is not valid. (AOS10)
2114 -- * An admission course application instance cannot be offered if IGS_PS_UNIT sets
2115 -- are applicable for the admission application and the IGS_PS_UNIT set is not vaild.
2116 -- (AOS11)
2117 -- * An admission course application instance cannot be offered if course
2118 -- encumbrance checking is required for the admission application and IGS_PS_UNIT
2119 -- sets are applicable for the admission application and the IGS_PE_PERSON has an
2120 -- encumbrance on the IGS_PS_UNIT set being offered. (AOS11-A)
2121 -- * An admission course application instance cannot be offered if the
2122 -- proposed commencement date is not valid (AOS32).
2123 -- * An admission course application instance can only be withdrawn or voided
2124 -- if the IGS_PE_PERSON is not enrolled the admission course. (AOS17-A)
2125 DECLARE
2126 v_check CHAR;
2127 cst_error CONSTANT VARCHAR2(1) := 'E';
2128 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
2129 cst_outcome_lt CONSTANT VARCHAR2(10) := 'OUTCOME-LT';
2130 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
2131 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
2132 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
2133 cst_voided CONSTANT VARCHAR2(10) := 'VOIDED';
2134 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
2135 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
2136 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
2137 cst_lapsed CONSTANT VARCHAR2(10) := 'LAPSED';
2138 v_deferment_processing BOOLEAN ;
2139 v_reconsideration_processing BOOLEAN ;
2140 v_defermt_processing BOOLEAN ;
2141 v_reconsideratn_processing BOOLEAN ;
2142 v_message_name VARCHAR2(30);
2143 v_return_type VARCHAR2(1);
2144 v_late_ind VARCHAR2(1);
2145 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
2146 v_adm_outcome_status IGS_AD_OU_STAT.adm_outcome_status%TYPE;
2147 v_effective_dt DATE;
2148 v_prpsd_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
2149 v_ca_sequence_number NUMBER;
2150 CURSOR c_aal IS
2151 SELECT 'x'
2152 FROM IGS_AD_APPL_LTR aal
2153 WHERE aal.person_id = p_person_id AND
2154 aal.admission_appl_number = p_admission_appl_number AND
2155 aal.correspondence_type = cst_outcome_lt;
2156 CURSOR c_acai_aos IS
2157 SELECT 'x'
2158 FROM IGS_AD_PS_APPL_INST acai,
2159 IGS_AD_OU_STAT aos
2160 WHERE acai.person_id = p_person_id AND
2161 acai.admission_appl_number = p_admission_appl_number AND
2162 (acai.nominated_course_cd <> p_nominated_course_cd OR
2163 acai.sequence_number <> p_acai_sequence_number) AND
2164 acai.adm_outcome_status = aos.adm_outcome_status AND
2165 aos.s_adm_outcome_status <> cst_pending;
2166 CURSOR c_acaiu_auos IS
2167 SELECT 'x'
2168 FROM IGS_AD_PS_APLINSTUNT acaiu,
2169 IGS_AD_UNIT_OU_STAT auos
2170 WHERE acaiu.person_id = p_person_id AND
2171 acaiu.admission_appl_number = p_admission_appl_number AND
2172 acaiu.nominated_course_cd = p_nominated_course_cd AND
2173 acaiu.acai_sequence_number = p_acai_sequence_number AND
2174 auos.s_adm_outcome_status = cst_pending AND
2175 acaiu.adm_unit_outcome_status = auos.adm_unit_outcome_status;
2176 CURSOR c_sca IS
2177 SELECT sca.course_attempt_status
2178 FROM IGS_EN_STDNT_PS_ATT sca
2179 WHERE sca.person_id = p_person_id AND
2180 sca.course_cd = p_course_cd AND
2181 sca.adm_admission_appl_number = p_admission_appl_number AND
2182 sca.adm_nominated_course_cd = p_nominated_course_cd AND
2183 sca.adm_sequence_number = p_acai_sequence_number;
2184
2185
2186 v_cty_res_typ_ind IGS_PS_TYPE.research_type_ind%TYPE;
2187
2188 CURSOR c_crv_cty IS
2189 SELECT cty.research_type_ind
2190 FROM IGS_PS_VER crv,
2191 IGS_PS_TYPE cty
2192 WHERE crv.course_cd = p_course_cd AND
2193 crv.version_number = p_crv_version_number AND
2194 crv.course_type = cty.course_type;
2195
2196 --------------------------------------------SUB-FUNCTION-----------------------
2197 FUNCTION admpl_chk_res_or_def(
2198 p_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
2199 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2200 p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
2201 p_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2202 v_defermt_processing IN OUT NOCOPY BOOLEAN,
2203 v_reconsideratn_processing IN OUT NOCOPY BOOLEAN)
2204 RETURN BOOLEAN
2205 AS
2206 BEGIN -- admpl_chk_res_or_def
2207 -- Determine if reconsideration or deferment is being processed
2208 DECLARE
2209 cst_trg_as CONSTANT VARCHAR2(10) := 'TRG_AS';
2210 cst_deferral CONSTANT VARCHAR2(10) := 'DEFERRAL';
2211 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
2212 cst_no_quota CONSTANT VARCHAR2(10) := 'NO-QUOTA';
2213 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
2214 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
2215 v_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
2216 v_s_adm_offer_dfrmnt_status IGS_LOOKUPS_VIEW.lookup_code%TYPE;
2217 v_ret_val BOOLEAN;
2218 v_check CHAR := NULL;
2219 v_counter NUMBER := 0;
2220 CURSOR c_recent_his IS
2221 SELECT aos.s_adm_outcome_status,
2222 aors.s_adm_offer_resp_status,
2223 aods.s_adm_offer_dfrmnt_status
2224 FROM IGS_AD_PS_APLINSTHST acaih,
2225 IGS_AD_OU_STAT aos,
2226 IGS_AD_OFR_RESP_STAT aors,
2227 IGS_AD_OFRDFRMT_STAT aods
2228 WHERE acaih.person_id = p_person_id AND
2229 acaih.admission_appl_number = p_admission_appl_number AND
2230 acaih.nominated_course_cd = p_nominated_course_cd AND
2231 acaih.sequence_number = p_acai_sequence_number AND
2232 acaih.adm_outcome_status = aos.adm_outcome_status (+) AND
2233 acaih.adm_offer_resp_status = aors.adm_offer_resp_status (+)AND
2234 acaih.adm_offer_dfrmnt_status = aods.adm_offer_dfrmnt_status (+)
2235 ORDER BY acaih.hist_start_dt DESC;
2236 CURSOR c_sca IS
2237 SELECT 'x'
2238 FROM IGS_AD_PS_APPL
2239 WHERE person_id = p_person_id AND
2240 admission_appl_number = p_admission_appl_number AND
2241 nominated_course_cd = p_nominated_course_cd AND
2242 req_for_reconsideration_ind = 'Y';
2243 BEGIN
2244 v_ret_val := FALSE;
2245 IF p_called_from = cst_trg_as THEN
2246 v_defermt_processing := FALSE;
2247 v_reconsideratn_processing := FALSE;
2248 -- Select the first record only.
2249 -- (This is the most recent history record inserted. This history record
2250 -- will have been
2251 -- inserted as a part of reconsideration or deferment processing.)
2252 -- Determine if deferment is being processed
2253 FOR v_recent_his_rec IN c_recent_his LOOP
2254 IF c_recent_his%ROWCOUNT = 1 THEN
2255 IF v_recent_his_rec.s_adm_offer_resp_status IS NOT NULL AND
2256 v_recent_his_rec.s_adm_offer_resp_status = cst_deferral AND
2257 v_recent_his_rec.s_adm_offer_dfrmnt_status IS NOT NULL AND
2258 v_recent_his_rec.s_adm_offer_dfrmnt_status = cst_approved THEN
2259 v_defermt_processing := TRUE;
2260 v_ret_val := TRUE;
2261 EXIT;
2262 END IF;
2263 -- Determine if reconsideration is being processed.
2264 IF NOT v_defermt_processing THEN
2265 IF v_recent_his_rec.s_adm_outcome_status IN (
2266 cst_no_quota,
2267 cst_rejected) THEN
2268 OPEN c_sca;
2269 FETCH c_sca INTO v_check;
2270 IF c_sca%FOUND THEN
2271 CLOSE c_sca;
2272 v_reconsideratn_processing := TRUE;
2273 v_ret_val := TRUE;
2274 EXIT;
2275 END IF;
2276 CLOSE c_sca;
2277 END IF;
2278 END IF; -- NOT v_defermt_processing
2279 END IF; -- if first record
2280 END LOOP;
2281 END IF; -- p_called_from = cst_trg_as
2282 RETURN v_ret_val;
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 IF c_recent_his%ISOPEN THEN
2286 CLOSE c_recent_his;
2287 END IF;
2288 IF c_sca%ISOPEN THEN
2289 CLOSE c_sca;
2290 END IF;
2291 END;
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2295 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admpl_chk_res_or_def');
2296 IGS_GE_MSG_STACK.ADD;
2297 App_Exception.Raise_Exception;
2298 END admpl_chk_res_or_def;
2299 --------------------------------------------------MAIN-------------------------
2300 BEGIN
2301 v_deferment_processing := FALSE;
2302 v_reconsideration_processing := FALSE;
2303 v_defermt_processing := FALSE;
2304 v_reconsideratn_processing := FALSE;
2305
2306 p_message_name := NULL;
2307 ------------------------------------------------------------------------------
2308 -- Validate an outcome of pending.
2309 ------------------------------------------------------------------------------
2310 IF p_s_adm_outcome_status = cst_pending THEN
2311 -- Check for the existence of an admission application letter
2312 OPEN c_aal;
2313 FETCH c_aal INTO v_check;
2314 IF c_aal%FOUND THEN
2315 CLOSE c_aal;
2316 IF p_pref_allowed = 'N' THEN
2317 IF NOT admpl_chk_res_or_def(
2318 p_person_id,
2319 p_admission_appl_number,
2320 p_nominated_course_cd,
2321 p_acai_sequence_number,
2322 v_defermt_processing,
2323 v_reconsideratn_processing) THEN
2324 v_deferment_processing := v_defermt_processing;
2325 v_reconsideration_processing := v_reconsideratn_processing;
2326 -- Determine if reconsideration or deferment is being processed.
2327 IF NOT v_deferment_processing AND
2328 NOT v_reconsideration_processing THEN
2329 -- (only one course to an application)
2330 -- Outcome status cannot be pending if an admission outcome
2331 -- letter has already been inserted (the letter will need to be deleted).
2332 p_message_name := 'IGS_AD_ST_NOTBE_PENDING_DEL';
2333 RETURN FALSE;
2334 END IF;
2335 END IF;
2336 ELSE -- p_pref_allowed = 'N'
2337 -- Cannot have an admission outcome letter if setting this instance to
2338 -- pending means that there are no IGS_AD_PS_APPL_INST with an outcome
2339 -- in the
2340 -- admission application (exclude this instance in select)
2341 OPEN c_acai_aos;
2342 FETCH c_acai_aos INTO v_check;
2343 IF c_acai_aos%NOTFOUND THEN
2344 CLOSE c_acai_aos;
2345 IF NOT admpl_chk_res_or_def(
2346 p_person_id,
2347 p_admission_appl_number,
2348 p_nominated_course_cd,
2349 p_acai_sequence_number,
2350 v_defermt_processing,
2351 v_reconsideratn_processing) THEN
2352 v_deferment_processing := v_defermt_processing;
2353 v_reconsideration_processing := v_reconsideratn_processing;
2354 -- Determine if reconsideration or deferment is being processed.
2355 IF NOT v_deferment_processing AND
2356 NOT v_reconsideration_processing THEN
2357 p_message_name := 'IGS_AD_ST_NOTBE_PENDING';
2358 RETURN FALSE;
2359 END IF;
2360 END IF; -- admpl_chk_res_or_def(...)
2361 ELSE -- c_acai_aos%NOTFOUND
2362 CLOSE c_acai_aos;
2363 END IF; -- c_acai_aos%NOTFOUND
2364 END IF; -- p_pref_allowed = 'N'
2365 ELSE -- c_aal%FOUND
2366 CLOSE c_aal;
2367 END IF;
2368 END IF; -- (PENDING)
2369 ------------------------------------------------------------------------------
2370 -- Validate an outcome of offer or conditional offer.
2371 ------------------------------------------------------------------------------
2372 IF p_s_adm_outcome_status IN ( cst_offer,
2373 cst_cond_offer) THEN
2374 ------------------------------------------------------------------------------
2375 -- Cannot make an offer if pending IGS_PS_UNIT outcome status exists.
2376 ------------------------------------------------------------------------------
2377 OPEN c_acaiu_auos;
2378 FETCH c_acaiu_auos INTO v_check;
2379 IF c_acaiu_auos%FOUND THEN
2380 p_message_name := 'IGS_AD_CANNOT_MAKE_OFR_PENDIN';
2381 CLOSE c_acaiu_auos;
2382 RETURN FALSE;
2383 END IF;
2384 CLOSE c_acaiu_auos;
2385 ------------------------------------------------------------------------------
2386 -- Cannot make an offer if the admission application is incomplete.
2387 ------------------------------------------------------------------------------
2388 -- Determine the effective date for performing the complete check.
2389 -- v_effective_dt := IGS_AD_GEN_005.ADMP_GET_CRV_STRT_DT (
2390 -- p_adm_cal_type,
2391 -- p_adm_ci_sequence_number);
2392 v_effective_dt := TRUNC(SYSDATE);
2393 -- Perform complete check.
2394 IF NOT IGS_AD_VAL_ACAI.admp_val_offer_comp(
2395 p_person_id,
2396 p_admission_appl_number,
2397 p_nominated_course_cd,
2398 p_acai_sequence_number,
2399 p_course_cd,
2400 p_crv_version_number,
2401 p_admission_cat,
2402 p_s_admission_process_type,
2403 v_effective_dt,
2404 p_called_from,
2405 p_message_name) THEN
2406 RETURN FALSE;
2407 END IF;
2408 -----------------------------------------------------------------------------
2409 -- Cannot make an offer if IGS_PE_PERSON encumbrance checking is required and the
2410 -- applicant is encumbered.
2411 -----------------------------------------------------------------------------
2412 IF p_check_person_encumb = 'Y' THEN
2413 -- Determine the effective date for performing the encumbrance check.
2414 v_effective_dt := NVL(IGS_AD_GEN_006.ADMP_GET_ENCMB_DT (
2415 p_adm_cal_type,
2416 p_adm_ci_sequence_number),SYSDATE);
2417 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (
2418 p_person_id,
2419 NULL, -- Input parameter course code: not applicable
2420 v_effective_dt,
2421 v_message_name) THEN
2422 p_message_name := 'IGS_AD_OFR_CANNOT_BEMADE';
2423 RETURN FALSE;
2424 END IF;
2425 END IF;
2426 -----------------------------------------------------------------------------
2427 -- Cannot make an offer if course encumbrance checking is required and the
2428 -- applicant course
2429 -- is encumbered.
2430 -----------------------------------------------------------------------------
2431 IF p_check_course_encumb = 'Y' THEN
2432 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_encmb(
2433 p_person_id,
2434 p_course_cd,
2435 p_adm_cal_type,
2436 p_adm_ci_sequence_number,
2437 p_check_course_encumb,
2438 'Y', -- Offer indicator
2439 p_message_name,
2440 v_return_type) THEN
2441 IF v_return_type = cst_error THEN -- (cst_error - 'E')
2442 RETURN FALSE;
2443 END IF;
2444 END IF;
2445 END IF;
2446 -----------------------------------------------------------------------------
2447 -- Cannot make an offer if course version is planned.
2448 -----------------------------------------------------------------------------
2449 IF NOT IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
2450 p_course_cd,
2451 p_crv_version_number,
2452 p_s_admission_process_type,
2453 'Y', -- Offer indicator,
2454 p_message_name) THEN
2455 RETURN FALSE;
2456 END IF;
2457 -----------------------------------------------------------------------------
2458 -- Cannot make an offer if the course offering option is not valid.
2459 -----------------------------------------------------------------------------
2460 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_cop (
2461 p_course_cd,
2462 p_crv_version_number,
2463 p_location_cd,
2464 p_attendance_mode,
2465 p_attendance_type,
2466 p_acad_cal_type,
2467 p_acad_ci_sequence_number,
2468 p_adm_cal_type,
2469 p_adm_ci_sequence_number,
2470 p_admission_cat,
2471 p_s_admission_process_type,
2472 'Y', -- Offer indicator,
2473 p_appl_dt,
2474 p_late_appl_allowed,
2475 'N', -- Deferred application
2476 p_message_name,
2477 v_return_type,
2478 v_late_ind) THEN
2479 IF v_return_type = cst_error THEN
2480 IF v_late_ind = 'Y' THEN
2481 -- If overriding outcome is allowed for the admission application,
2482 -- then the late application is valid.
2483 IF p_adm_outcome_status_auth_dt IS NULL THEN
2484 RETURN FALSE;
2485 END IF;
2486 ELSE
2487 RETURN FALSE;
2488 END IF;
2489 END IF;
2490 END IF;
2491 -----------------------------------------------------------------------------
2492 -- Cannot make an offer if the course is not valid against existing student
2493 -- course attempts.
2494 -----------------------------------------------------------------------------
2495 IF NOT IGS_AD_VAL_ACAI.admp_val_aca_sca (
2496 p_person_id,
2497 p_course_cd,
2498 p_appl_dt,
2499 p_admission_cat,
2500 p_s_admission_process_type,
2501 p_fee_cat,
2502 p_correspondence_cat,
2503 p_enrolment_cat,
2504 'Y', -- Offer indicator,
2505 p_message_name,
2506 v_return_type) THEN
2507 IF v_return_type = cst_error THEN
2508 RETURN FALSE;
2509 END IF;
2510 END IF;
2511 -----------------------------------------------------------------------------
2512 -- Cannot make offer if IGS_PS_UNIT set is applicable for the admission application
2513 -- and the IGS_PS_UNIT set is invalid.
2514 -----------------------------------------------------------------------------
2515 IF p_unit_set_appl = 'Y' THEN
2516 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_us (
2517 p_unit_set_cd,
2518 p_us_version_number,
2519 p_course_cd,
2520 p_crv_version_number,
2521 p_acad_cal_type,
2522 p_location_cd,
2523 p_attendance_mode,
2524 p_attendance_type,
2525 p_admission_cat,
2526 'Y', -- Offer indicator,
2527 p_unit_set_appl,
2528 p_message_name,
2529 v_return_type) THEN
2530 IF v_return_type = cst_error THEN
2531 RETURN FALSE;
2532 END IF;
2533 END IF;
2534 ----------------------------------------------------------------------------
2535 -- Cannot make an offer if course encumbrance checking is required and IGS_PS_UNIT
2536 -- set is applicable for the application and the applicant IGS_PS_UNIT set is
2537 -- encumbered.
2538 ----------------------------------------------------------------------------
2539 IF p_check_course_encumb = 'Y' THEN
2540 IF NOT IGS_AD_VAL_ACAI.admp_val_us_encmb (
2541 p_person_id,
2542 p_course_cd,
2543 p_unit_set_cd,
2544 p_us_version_number,
2545 p_adm_cal_type,
2546 p_adm_ci_sequence_number,
2547 p_check_course_encumb, -- Check course encumbrance indicator,
2548 'Y', -- Offer indicator,
2549 p_message_name,
2550 v_return_type) THEN
2551 IF v_return_type = cst_error THEN
2552 RETURN FALSE;
2553 END IF;
2554 END IF;
2555 END IF; -- p_check_course_encumb = 'Y'
2556 END IF; -- p_unit_set_appl = 'Y'
2557 -----------------------------------------------------------------------------
2558 -- Cannot make an offer if the proposed commencement date is not valid.
2559 -----------------------------------------------------------------------------
2560 --Determine the proposed commencement date.
2561 v_prpsd_commencement_dt := IGS_EN_GEN_002.ENRP_GET_ACAD_COMM (
2562 p_acad_cal_type,
2563 p_acad_ci_sequence_number,
2564 p_person_id,
2565 p_course_cd,
2566 p_admission_appl_number,
2567 p_nominated_course_cd,
2568 p_acai_sequence_number,
2569 'Y'); -- Check proposed commencement date indicator.
2570 --Determine the user defined outcome status.
2571 v_adm_outcome_status := IGS_AD_GEN_009.ADMP_GET_SYS_AOS(
2572 p_s_adm_outcome_status);
2573
2574 -- Need to by pass the check for research programs
2575
2576 OPEN c_crv_cty;
2577 FETCH c_crv_cty INTO v_cty_res_typ_ind;
2578
2579 IF c_crv_cty%FOUND THEN
2580 -- bug no 2124050
2581 -- ADDED the CLOSE c_cr_cty after checking FOUND in the cursor
2582 -- previously it was done before checking the cursor
2583 -- by rrengara on 12-MAR-2002
2584 CLOSE c_crv_cty;
2585 IF v_cty_res_typ_ind = 'Y' THEN
2586 IF NOT IGS_RE_VAL_CA.admp_val_acai_comm (
2587 p_person_id,
2588 p_course_cd,
2589 p_crv_version_number,
2590 p_admission_appl_number,
2591 p_nominated_course_cd,
2592 p_acai_sequence_number,
2593 p_adm_cal_type,
2594 p_adm_ci_sequence_number,
2595 v_adm_outcome_status,
2596 v_prpsd_commencement_dt,
2597 NULL, -- Minimum_Submission_Date
2598 v_ca_sequence_number, -- IGS_RE_CANDIDATURE Sequence Number
2599 'ACAI', -- Parent
2600 p_message_name) THEN
2601 RETURN FALSE;
2602 END IF;
2603 END IF;
2604 END IF;
2605 END IF; -- (OFFER, COND-OFFER)
2606 ------------------------------------------------------------------------------
2607 -- Validate an outcome of withdrawn or voided.
2608 ------------------------------------------------------------------------------
2609 IF p_s_adm_outcome_status IN ( cst_withdrawn,
2610 cst_voided) THEN
2611 -- Cannot withdraw or void offer if applicant is currently enrolled in their
2612 -- course of admission.
2613 OPEN c_sca;
2614 FETCH c_sca INTO v_course_attempt_status;
2615 IF c_sca%FOUND THEN
2616 IF v_course_attempt_status NOT IN (
2617 cst_unconfirm,
2618 cst_discontin,
2619 cst_deleted,
2620 cst_lapsed) THEN
2621 CLOSE c_sca;
2622 p_message_name := 'IGS_AD_INST_NOTBE_WITHDRAWN';
2623 RETURN FALSE;
2624 END IF;
2625 END IF;
2626 CLOSE c_sca;
2627 END IF; -- (WITHDRAWN, VOIDED)
2628 p_message_name := NULL;
2629 RETURN TRUE;
2630 EXCEPTION
2631 WHEN OTHERS THEN
2632 IF c_aal%ISOPEN THEN
2633 CLOSE c_aal;
2634 END IF;
2635 IF c_acai_aos%ISOPEN THEN
2636 CLOSE c_acai_aos;
2637 END IF;
2638 IF c_acaiu_auos%ISOPEN THEN
2639 CLOSE c_acaiu_auos;
2640 END IF;
2641 IF c_sca%ISOPEN THEN
2642 CLOSE c_sca;
2643 END IF;
2644 RAISE;
2645 END;
2646 EXCEPTION
2647 WHEN OTHERS THEN
2648 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2649 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_otcome');
2650 IGS_GE_MSG_STACK.ADD;
2651 App_Exception.Raise_Exception;
2652 END admp_val_acai_otcome;
2653 --
2654 -- This module validates multiple IGS_AD_PS_APPL_INST offers.
2655 FUNCTION admp_val_offer_mult(
2656 p_person_id IN NUMBER ,
2657 p_admission_appl_number IN NUMBER ,
2658 p_nominated_course_cd IN VARCHAR2 ,
2659 p_acai_sequence_number IN NUMBER ,
2660 p_course_cd IN VARCHAR2 ,
2661 p_s_adm_outcome_status IN VARCHAR2 ,
2662 p_s_adm_offer_resp_status IN VARCHAR2 ,
2663 p_adm_cal_type IN VARCHAR2 ,
2664 p_adm_ci_sequence_number IN NUMBER ,
2665 p_admission_cat IN VARCHAR2 ,
2666 p_s_admission_process_type IN VARCHAR2 ,
2667 p_multi_offer_allowed IN VARCHAR2,
2668 p_multi_offer_limit IN NUMBER ,
2669 p_message_name OUT NOCOPY VARCHAR2 )
2670 RETURN BOOLEAN AS
2671 BEGIN -- Validates multiple IGS_AD_PS_APPL_INST offers
2672 -- Validations are
2673 -- * If the admission application does not allow multiple offers then only one
2674 -- admission
2675 -- course application instance can be currently offered to the IGS_PE_PERSON within
2676 -- the
2677 -- admission period and admission process category. (AOS13)
2678 -- * If the admission application allows multiple offers then the number of
2679 -- current offers
2680 -- for the IGS_PE_PERSON within the same admission period and admission process
2681 -- category must
2682 -- be less than or equal to the number of offers allowed for the admission
2683 -- process
2684 -- category (AOS14)
2685 -- * The admission course application instance cannot be offered if the IGS_PE_PERSON
2686 -- has already
2687 -- been has a current offered for the same course in the same admission
2688 -- period. (AOS15)
2689 DECLARE
2690 v_check CHAR;
2691 v_count NUMBER(5);
2692 cst_offer CONSTANT VARCHAR2(10) := 'OFFER';
2693 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
2694 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
2695 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
2696 CURSOR c_acaiv_aos IS
2697 SELECT 'x'
2698 FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2699 IGS_AD_OU_STAT aos,
2700 IGS_AD_OFR_RESP_STAT aors
2701 WHERE acaiv.person_id = p_person_id AND
2702 (acaiv.admission_appl_number <> p_admission_appl_number OR
2703 acaiv.nominated_course_cd <> p_nominated_course_cd OR
2704 acaiv.sequence_number <> p_acai_sequence_number) AND
2705 acaiv.course_cd = p_course_cd AND
2706 acaiv.adm_cal_type = p_adm_cal_type AND
2707 acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
2708 aos.s_adm_outcome_status IN (cst_offer, cst_cond_offer) AND
2709 aors.s_adm_offer_resp_status NOT IN (cst_rejected, cst_not_applic) AND
2710 acaiv.adm_outcome_status = aos.adm_outcome_status AND
2711 acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
2712 CURSOR c_cnt_acaiv_aa_aos IS
2713 SELECT count(*)
2714 FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2715 IGS_AD_APPL aa,
2716 IGS_AD_OU_STAT aos,
2717 IGS_AD_OFR_RESP_STAT aors
2718 WHERE acaiv.person_id = p_person_id AND
2719 (acaiv.admission_appl_number <> p_admission_appl_number OR
2720 acaiv.nominated_course_cd <> p_nominated_course_cd OR
2721 acaiv.sequence_number <> p_acai_sequence_number) AND
2722 acaiv.adm_cal_type = p_adm_cal_type AND
2723 acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
2724 aa.admission_cat = p_admission_cat AND
2725 aa.s_admission_process_type = p_s_admission_process_type AND
2726 aos.s_adm_outcome_status IN (cst_offer, cst_cond_offer) AND
2727 aors.s_adm_offer_resp_status NOT IN (cst_rejected, cst_not_applic) AND
2728 aa.person_id = acaiv.person_id AND
2729 aa.admission_appl_number = acaiv.admission_appl_number AND
2730 -- aa.acad_cal_type = acaiv.acad_cal_type AND -- Commented this line as the join is not required Bug 3150054
2731 -- aa.acad_ci_sequence_number = acaiv.acad_ci_sequence_number AND -- Commented this line as the join is not required Bug 3150054
2732 aos.adm_outcome_status = acaiv.adm_outcome_status AND
2733 acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
2734 BEGIN
2735 p_message_name := NULL;
2736 -- Only perform multiple offer validations when the admission course
2737 -- application instance has been
2738 -- offered or conditionally offered.
2739 IF p_s_adm_outcome_status IN (cst_offer, cst_cond_offer) AND
2740 p_s_adm_offer_resp_status NOT IN (cst_rejected,cst_not_applic)THEN
2741 OPEN c_cnt_acaiv_aa_aos;
2742 FETCH c_cnt_acaiv_aa_aos INTO v_count;
2743 CLOSE c_cnt_acaiv_aa_aos;
2744 -- Validate when multiple offers are not allowed.
2745 IF p_multi_offer_allowed = 'N' THEN
2746 IF v_count > 0 THEN
2747 -- Cannot make an offer to more than one course in the same admission
2748 -- process category if multiple offers are not allowed.
2749 p_message_name := 'IGS_AD_MULTIPLE_OFRS_NOTALLOW';
2750 RETURN FALSE;
2751 END IF;
2752 ELSE
2753 -- Validate when multiple offers are allowed.
2754 IF v_count >= p_multi_offer_limit THEN
2755 -- Multiple offer limit has been reached. No more offers can be made.
2756 p_message_name := 'IGS_AD_MULTIPLE_OFFER_LIMIT';
2757 RETURN FALSE;
2758 END IF;
2759 END IF;
2760 -- Validate offers within the same admission period
2761 OPEN c_acaiv_aos;
2762 FETCH c_acaiv_aos INTO v_check;
2763 IF (c_acaiv_aos%FOUND) THEN
2764 -- Cannot make an offer if this course is already being offered to this
2765 -- applicant in this admission period.
2766 CLOSE c_acaiv_aos;
2767 p_message_name := 'IGS_AD_PRSN_CANNOTOFR_SAMEPRG';
2768 RETURN FALSE;
2769 END IF;
2770 CLOSE c_acaiv_aos;
2771 END IF;
2772 RETURN TRUE;
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 IF (c_acaiv_aos%ISOPEN) THEN
2776 CLOSE c_acaiv_aos;
2777 END IF;
2778 IF (c_cnt_acaiv_aa_aos%ISOPEN) THEN
2779 CLOSE c_acaiv_aos;
2780 END IF;
2781 END;
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2785 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_offer_mult');
2786 IGS_GE_MSG_STACK.ADD;
2787 App_Exception.Raise_Exception;
2788 END admp_val_offer_mult;
2789 --
2790 -- Validate offers across admission process categories (same adm period).
2791 FUNCTION admp_val_offer_x_apc(
2792 p_person_id IN NUMBER ,
2793 p_admission_appl_number IN NUMBER ,
2794 p_nominated_course_cd IN VARCHAR2 ,
2795 p_acai_sequence_number IN NUMBER ,
2796 p_adm_outcome_status IN VARCHAR2 ,
2797 p_adm_offer_resp_status IN VARCHAR2 ,
2798 p_adm_cal_type IN VARCHAR2 ,
2799 p_adm_ci_sequence_number IN NUMBER ,
2800 p_admission_cat IN VARCHAR2 ,
2801 p_s_admission_process_type IN VARCHAR2 ,
2802 p_message_name OUT NOCOPY VARCHAR2 ,
2803 p_return_type OUT NOCOPY VARCHAR2 )
2804 RETURN BOOLEAN AS
2805 BEGIN -- admp_val_offer_x_apc
2806 -- Validate admission application offers across admission process categories
2807 -- within the same admission period.
2808 -- Validations are :
2809 -- * Warn if the IGS_PE_PERSON is being made an offer and they already have a
2810 -- current offer in another admission process category for
2811 -- this admission period.
2812 DECLARE
2813 cst_offer CONSTANT IGS_AD_OU_STAT.s_adm_outcome_status%TYPE :='OFFER';
2814 cst_cond_offer CONSTANT
2815 IGS_AD_OU_STAT.s_adm_outcome_status%TYPE :='COND-OFFER';
2816 cst_rejected CONSTANT
2817 IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE :='REJECTED';
2818 cst_not_applic CONSTANT
2819 IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE :='NOT-APPLIC';
2820 cst_warn CONSTANT VARCHAR2(1) := 'W';
2821 cst_error CONSTANT VARCHAR2(1) := 'E';
2822 v_aaaa_exists VARCHAR2(1);
2823 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
2824 v_s_adm_offer_resp_status igs_ad_ofr_resp_stat.s_adm_offer_resp_status%TYPE;
2825 CURSOR c_aaaa IS
2826 SELECT 'x'
2827 FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
2828 IGS_AD_APPL aa,
2829 IGS_AD_OU_STAT aos,
2830 IGS_AD_OFR_RESP_STAT aors
2831 WHERE acaiv.person_id = p_person_id AND
2832 (acaiv.admission_appl_number <> p_admission_appl_number OR
2833 acaiv.nominated_course_cd <> p_nominated_course_cd OR
2834 acaiv.sequence_number <> p_acai_sequence_number) AND
2835 aa.person_id = acaiv.person_id AND
2836 aa.admission_appl_number = acaiv.admission_appl_number AND
2837 (aa.admission_cat <> p_admission_cat OR
2838 aa.s_admission_process_type <> p_s_admission_process_type) AND
2839 acaiv.adm_cal_type = p_adm_cal_type AND
2840 acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
2841 aos.s_adm_outcome_status IN (
2842 cst_offer,
2843 cst_cond_offer) AND
2844 aors.s_adm_offer_resp_status NOT IN (
2845 cst_rejected,
2846 cst_not_applic) AND
2847 acaiv.adm_outcome_status = aos.adm_outcome_status AND
2848 acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
2849 BEGIN
2850 -- Set the default message number
2851 p_message_name := NULL;
2852 p_return_type := NULL;
2853 -- Get the status system values.
2854 v_s_adm_outcome_status := (IGS_AD_GEN_008.ADMP_GET_SAOS (p_adm_outcome_status));
2855 v_s_adm_offer_resp_status := (IGS_AD_GEN_008.ADMP_GET_SAORS (p_adm_offer_resp_status));
2856 IF v_s_adm_outcome_status IN (
2857 cst_offer,
2858 cst_cond_offer) AND
2859 v_s_adm_offer_resp_status not in (cst_rejected, cst_not_applic) THEN
2860 OPEN c_aaaa;
2861 FETCH c_aaaa INTO v_aaaa_exists;
2862 IF c_aaaa%FOUND THEN
2863 CLOSE c_aaaa;
2864 p_message_name := 'IGS_AD_PRSN_CUROFR_ADMPRC';
2865 p_return_type := cst_warn;
2866 RETURN FALSE;
2867 END IF;
2868 CLOSE c_aaaa;
2869 END IF;
2870 -- Return the default value
2871 RETURN TRUE;
2872 EXCEPTION
2873 WHEN OTHERS THEN
2874 IF c_aaaa%ISOPEN THEN
2875 CLOSE c_aaaa;
2876 END IF;
2877 RAISE;
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_STATUS.admp_val_offer_x_apc');
2883 IGS_GE_MSG_STACK.ADD;
2884 App_Exception.Raise_Exception;
2885 END admp_val_offer_x_apc;
2886 --
2887 -- Validate update of the admission outcome status.
2888 FUNCTION admp_val_aos_update(
2889 p_person_id IN NUMBER ,
2890 p_admission_appl_number IN NUMBER ,
2891 p_adm_outcome_status IN VARCHAR2 ,
2892 p_old_adm_outcome_status IN VARCHAR2 ,
2893 p_message_name OUT NOCOPY VARCHAR2 ,
2894 p_return_type OUT NOCOPY VARCHAR2 )
2895 RETURN BOOLEAN AS
2896 BEGIN -- admp_val_aos_update
2897 -- Validate update of the admission outcome status.
2898 -- On update of the admission outcome status warn if outcome letter
2899 -- correspondence is yet to be issued and phrases relating to the old status
2900 -- are attached
2901 DECLARE
2902 v_aal_seq_no IGS_AD_APPL_LTR.sequence_number%TYPE;
2903 v_sent_dt DATE;
2904 cst_warn CONSTANT VARCHAR2(1) := 'W';
2905 cst_error CONSTANT VARCHAR2(1) := 'E';
2906 CURSOR c_seq_no IS
2907 SELECT aal.sequence_number
2908 FROM IGS_AD_APPL_LTR aal,
2909 IGS_AD_APPL_LTR_PHR aalp
2910 WHERE aalp.person_id = p_person_id AND
2911 aalp.admission_appl_number = p_admission_appl_number AND
2912 aalp.correspondence_type = 'OUTCOME-LT' AND
2913 aal.person_id = aalp.person_id AND
2914 aal.admission_appl_number = aalp.admission_appl_number AND
2915 aal.correspondence_type = aalp.correspondence_type AND
2916 aal.sequence_number = aalp.aal_sequence_number;
2917 BEGIN
2918 -- Check if the outcome status has been updated
2919 IF p_adm_outcome_status <> p_old_adm_outcome_status THEN
2920 -- Warn on change of outcome status if outcome letter correspondence
2921 -- is yet to be issued and has phrases relating to the old status attached
2922 OPEN c_seq_no;
2923 FETCH c_seq_no INTO v_aal_seq_no;
2924 IF (c_seq_no%FOUND)THEN
2925 -- Determine if the correspondence has been sent
2926 v_sent_dt := IGS_AD_GEN_002.ADMP_GET_AAL_SENT_DT (
2927 p_person_id,
2928 p_admission_appl_number,
2929 'OUTCOME-LT',
2930 v_aal_seq_no);
2931 IF v_sent_dt IS NULL THEN
2932 -- Current correspondence may need to be corrected
2933 p_message_name := 'IGS_AD_CHG_ADMOUTCOME_STATUS';
2934 p_return_type := cst_warn;
2935 CLOSE c_seq_no;
2936 RETURN FALSE;
2937 END IF;
2938 END IF;
2939 CLOSE c_seq_no;
2940 END IF;
2941 p_message_name := NULL;
2942 p_return_type := NULL;
2943 RETURN TRUE;
2944 END;
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2948 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aos_update');
2949 IGS_GE_MSG_STACK.ADD;
2950 App_Exception.Raise_Exception;
2951 END admp_val_aos_update;
2952 --
2953 -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
2954 FUNCTION admp_val_acai_acos(
2955 p_adm_cndtnl_offer_status IN VARCHAR2 ,
2956 p_old_adm_cndtnl_offer_status IN VARCHAR2 ,
2957 p_adm_outcome_status IN VARCHAR2 ,
2958 p_adm_doc_status IN VARCHAR2 ,
2959 p_late_adm_fee_status IN VARCHAR2 ,
2960 p_adm_fee_status IN VARCHAR2 ,
2961 p_late_appl_allowed IN VARCHAR2,
2962 p_fees_required IN VARCHAR2,
2963 p_cond_offer_assess_allowed IN VARCHAR2,
2964 p_cond_offer_fee_allowed IN VARCHAR2,
2965 p_cond_offer_doc_allowed IN VARCHAR2,
2966 p_message_name OUT NOCOPY VARCHAR2 )
2967 RETURN BOOLEAN AS
2968 BEGIN --admp_val_acai_acos
2969 --Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status
2970 DECLARE
2971 v_message_name VARCHAR2(30);
2972 v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
2973 v_old_s_adm_cndtnl_offer_stat igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
2974 v_s_adm_outcome_status igs_ad_ou_stat.s_adm_outcome_status%TYPE;
2975 v_s_adm_doc_status igs_ad_doc_stat.s_adm_doc_status%TYPE;
2976 v_late_s_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
2977 BEGIN
2978 --set the default message number
2979 p_message_name := NULL;
2980 --Perform item level validations.
2981 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_acos_item (
2982 p_adm_cndtnl_offer_status,
2983 p_adm_fee_status,
2984 p_fees_required,
2985 p_cond_offer_assess_allowed,
2986 p_cond_offer_fee_allowed,
2987 p_cond_offer_doc_allowed,
2988 v_message_name) = FALSE) THEN
2989 p_message_name := v_message_name;
2990 RETURN FALSE;
2991 END IF;
2992 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS(
2993 p_adm_cndtnl_offer_status);
2994 v_old_s_adm_cndtnl_offer_stat := IGS_AD_GEN_007.ADMP_GET_SACOS(
2995 p_old_adm_cndtnl_offer_status);
2996 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(
2997 p_adm_outcome_status);
2998 v_s_adm_doc_status := IGS_AD_GEN_007.ADMP_GET_SADS(
2999 p_adm_doc_status);
3000 v_late_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(
3001 p_late_adm_fee_status);
3002 --Validate against the other admission course application instance status
3003 -- values.
3004 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_acos_status (
3005 v_s_adm_cndtnl_offer_status,
3006 v_old_s_adm_cndtnl_offer_stat,
3007 v_s_adm_outcome_status,
3008 v_s_adm_doc_status,
3009 v_late_s_adm_fee_status,
3010 p_late_appl_allowed,
3011 v_message_name) = FALSE) THEN
3012 p_message_name := v_message_name;
3013 RETURN FALSE;
3014 END IF;
3015 RETURN TRUE;
3016 END;
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3020 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_acos');
3021 IGS_GE_MSG_STACK.ADD;
3022 App_Exception.Raise_Exception;
3023 END admp_val_acai_acos;
3024 --
3025 -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3026 FUNCTION admp_val_acos_item(
3027 p_adm_cndtnl_offer_status IN VARCHAR2 ,
3028 p_adm_fee_status IN VARCHAR2 ,
3029 p_fees_required IN VARCHAR2 ,
3030 p_cond_offer_assess_allowed IN VARCHAR2 ,
3031 p_cond_offer_fee_allowed IN VARCHAR2 ,
3032 p_cond_offer_doc_allowed IN VARCHAR2 ,
3033 p_message_name OUT NOCOPY VARCHAR2 )
3034 RETURN BOOLEAN AS
3035 BEGIN --admp_val_acos_item
3036 --Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3037 --The IGS_AD_CNDNL_OFRSTAT must be open. (ACOS01)
3038 --The conditional offer status must be not-applicable when conditional
3039 --offers are not allowed. (ACOS02)
3040 --If fees are required for the application, then fees must not be outstanding
3041 --for the conditional offer to be satisfied. (ACOS08)
3042 DECLARE
3043 v_message_name VARCHAR2(30);
3044 v_s_adm_fee_status igs_ad_fee_stat.s_adm_fee_status%TYPE;
3045 v_s_adm_cndtnl_offer_status igs_ad_cndnl_ofrstat.s_adm_cndtnl_offer_status%TYPE;
3046 BEGIN
3047 --set the default message number
3048 p_message_name := NULL;
3049 --validate the closed indicator
3050 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_acos_closed (
3051 p_adm_cndtnl_offer_status,
3052 v_message_name) = FALSE) THEN
3053 p_message_name := v_message_name;
3054 RETURN FALSE;
3055 END IF;
3056 v_s_adm_cndtnl_offer_status := IGS_AD_GEN_007.ADMP_GET_SACOS (p_adm_cndtnl_offer_status);
3057 --Validate the conditional offer status against
3058 ---the admission process category steps.
3059 IF (p_cond_offer_assess_allowed = 'N' AND
3060 p_cond_offer_fee_allowed = 'N' AND
3061 p_cond_offer_doc_allowed = 'N' AND
3062 v_s_adm_cndtnl_offer_status <> 'NOT-APPLIC') THEN
3063 --The conditional offer status must be not-applicable
3064 --when conditional offers are not allowed.
3065 p_message_name := 'IGS_AD_CONDOFRST_NOTAPPLICABL';
3066 RETURN FALSE;
3067 END IF;
3068 --Validate the conditional offer status against the admission application fee
3069 -- status.
3070 IF (p_fees_required = 'Y' AND
3071 v_s_adm_cndtnl_offer_status = 'SATISFIED') THEN
3072 --The admission conditional offer status cannot be satisfied if
3073 --application fees are still being assessed or haven't been determined.
3074 v_s_adm_fee_status := IGS_AD_GEN_008.ADMP_GET_SAFS(p_adm_fee_status);
3075 IF (v_s_adm_fee_status IN ('PENDING', 'ASSESSED')) THEN
3076 p_message_name := 'IGS_AD_CONDOFR_NOTSATISFIED';
3077 RETURN FALSE;
3078 END IF;
3079 END IF;
3080 RETURN TRUE;
3081 END;
3082 EXCEPTION
3083 WHEN OTHERS THEN
3084 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3085 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acos_item');
3086 IGS_GE_MSG_STACK.ADD;
3087 App_Exception.Raise_Exception;
3088 END admp_val_acos_item;
3089 --
3090 -- Validate if IGS_AD_CNDNL_OFRSTAT.adm_cndtnl_offer_status is closed.
3091 FUNCTION admp_val_acos_closed(
3092 p_adm_cndtnl_offer_status IN VARCHAR2 ,
3093 p_message_name OUT NOCOPY VARCHAR2 )
3094 RETURN BOOLEAN AS
3095 BEGIN -- admp_val_acos_closed
3096 -- Validate the IGS_AD_CNDNL_OFRSTAT closed indicator
3097 DECLARE
3098 CURSOR c_acos(
3099 cp_adm_cndtnl_offer_status
3100 IGS_AD_CNDNL_OFRSTAT.adm_cndtnl_offer_status%TYPE) IS
3101 SELECT closed_ind
3102 FROM IGS_AD_CNDNL_OFRSTAT
3103 WHERE adm_cndtnl_offer_status =cp_adm_cndtnl_offer_status;
3104 v_acos_rec c_acos%ROWTYPE;
3105 cst_yes CONSTANT CHAR := 'Y';
3106 BEGIN
3107 -- Set the default message number
3108 p_message_name := NULL;
3109 -- Cursor handling
3110 OPEN c_acos(
3111 p_adm_cndtnl_offer_status);
3112 FETCH c_acos INTO v_acos_rec;
3113 IF c_acos%NOTFOUND THEN
3114 CLOSE c_acos;
3115 RETURN TRUE;
3116 END IF;
3117 CLOSE c_acos;
3118 IF (v_acos_rec.closed_ind = cst_yes) THEN
3119 p_message_name := 'IGS_AD_ADMCOND_OFR_ST_CLOSED';
3120 RETURN FALSE;
3121 END IF;
3122 -- Return the default value
3123 RETURN TRUE;
3124 END;
3125 EXCEPTION
3126 WHEN OTHERS THEN
3127 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3128 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acos_closed');
3129 IGS_GE_MSG_STACK.ADD;
3130 App_Exception.Raise_Exception;
3131 END admp_val_acos_closed;
3132 --
3133 -- Validate the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status.
3134 FUNCTION admp_val_acos_status(
3135 p_s_adm_cndtnl_offer_status IN VARCHAR2 ,
3136 p_old_s_adm_cndtnl_offer_sts IN VARCHAR2 ,
3137 p_s_adm_outcome_status IN VARCHAR2 ,
3138 p_s_adm_doc_status IN VARCHAR2 ,
3139 p_late_s_adm_fee_status IN VARCHAR2 ,
3140 p_late_appl_allowed IN VARCHAR2,
3141 p_message_name OUT NOCOPY VARCHAR2 )
3142 RETURN BOOLEAN AS
3143 BEGIN --admp_val_acos_status
3144 --This module validates the IGS_AD_PS_APPL_INST.adm_cndtnl_offer_status
3145 --against other IGS_AD_PS_APPL_INST statuses.
3146 DECLARE
3147 BEGIN
3148 --set the default message number
3149 p_message_name := NULL;
3150 --Validate the admission conditional offer status against the admission outcome
3151 -- status.
3152 IF (p_s_adm_cndtnl_offer_status <> 'NOT-APPLIC' AND
3153 p_old_s_adm_cndtnl_offer_sts = 'NOT-APPLIC' AND
3154 p_s_adm_outcome_status <> 'COND-OFFER') THEN
3155 --The admission conditional offer status cannot be changed from
3156 --not-applicable if the admission outcome status is not conditional offer.
3157 p_message_name := 'IGS_AD_CONDOFR_NOTCHANGED' ;
3158 RETURN FALSE;
3159 END IF;
3160 IF (p_s_adm_cndtnl_offer_status = 'PENDING' AND
3161 p_s_adm_outcome_status NOT IN('COND-OFFER', 'WITHDRAWN', 'VOIDED')) THEN
3162 --The admission conditional offer status cannot be pending
3163 --if a conditional offer has not been made.
3164 p_message_name := 'IGS_AD_CONDOFR_NOTPENDING';
3165 RETURN FALSE;
3166 END IF;
3167 IF (p_s_adm_cndtnl_offer_status = 'SATISFIED') THEN
3168 IF( p_s_adm_outcome_status NOT IN ('COND-OFFER', 'WITHDRAWN', 'VOIDED')) THEN
3169 --The admission conditional offer status cannot be satisfied if a
3170 --conditional offer has not been made.
3171 p_message_name := 'IGS_AD_CONDOFR_ST_NOT_SATISFI';
3172 RETURN FALSE;
3173 END IF;
3174 --Validate the admission conditional offer status against the
3175 --admission documentation status.
3176 IF( p_s_adm_doc_status IN (
3177 'PENDING', 'UNSATISFAC', 'REJECTED-F', 'INCOMPLETE')) THEN
3178 --The admission conditional offer status cannot be satisfied if
3179 --documentation is unsatisfactory, rejected, incomplete or hasn't been
3180 -- determined.
3181 p_message_name := 'IGS_AD_CONDOFR_NOT_SATISFIED';
3182 RETURN FALSE;
3183 END IF;
3184 --Validate the admission conditional offer status against the late
3185 --admission fee status.
3186 IF (p_late_appl_allowed = 'Y' AND
3187 p_late_s_adm_fee_status IN('PENDING', 'ASSESSED')) THEN
3188 --The admission conditional offer status cannot be satisfied
3189 --if late fees are still being assessed or haven't been determined.
3190 p_message_name := 'IGS_AD_OFRST_LATE_FEES';
3191 RETURN FALSE;
3192 END IF;
3193 END IF;
3194 IF (p_s_adm_cndtnl_offer_status = 'WAIVED' AND
3195 p_s_adm_outcome_status NOT IN('COND-OFFER', 'WITHDRAWN', 'VOIDED')) THEN
3196 --The admission conditional offer status cannot be waived if a
3197 --conditional offer has not been made.
3198 p_message_name := 'IGS_AD_CONDOFR_NOTWAIVED';
3199 RETURN FALSE;
3200 END IF;
3201 --Removed this validation as part of the bug 2395305
3202 /* IF (p_s_adm_cndtnl_offer_status = 'UNSATISFAC' AND
3203 p_s_adm_outcome_status NOT IN('WITHDRAWN', 'VOIDED')) THEN
3204 --The admission conditional offer status cannot be unsatisfactory
3205 --if the conditional offer has not been withdrawn or voided.
3206 p_message_name := 'IGS_AD_ST_NOTBE_UNSATISFACTOR';
3207 RETURN FALSE;
3208 END IF;
3209 */
3210 IF (p_s_adm_cndtnl_offer_status = 'NOT-APPLIC' AND
3211 p_s_adm_outcome_status = 'COND-OFFER') THEN
3212 --The admission conditional offer status cannot be not-applicable
3213 --if a conditional offer has been made.
3214 p_message_name := 'IGS_AD_CONDOFR_NOT_NOTAPPL';
3215 RETURN FALSE;
3216 END IF;
3217 RETURN TRUE;
3218 END;
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3222 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acos_status');
3223 IGS_GE_MSG_STACK.ADD;
3224 App_Exception.Raise_Exception;
3225 END admp_val_acos_status;
3226
3227 FUNCTION admp_val_aods_update(
3228 p_person_id IN NUMBER ,
3229 p_admission_appl_number IN NUMBER ,
3230 p_nominated_course_cd IN VARCHAR2 ,
3231 p_sequence_number IN NUMBER ,
3232 p_offer_deferment_status IN VARCHAR2,
3233 p_message_name OUT NOCOPY VARCHAR2)
3234 RETURN BOOLEAN AS
3235 BEGIN
3236 -- hreddych #2602077 SF Integration Added the FUNCTION admp_val_aods_update
3237 -- This function validates the change in the offer deferment status
3238 -- The offer deferment status can be moved to CONFIRM only when the
3239 -- status is APPROVED.
3240 DECLARE
3241 CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
3242 p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
3243 p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
3244 p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
3245 SELECT adm_offer_dfrmnt_status
3246 FROM igs_ad_ps_appl_inst
3247 WHERE person_id = p_person_id AND
3248 admission_appl_number = p_admission_appl_number AND
3249 nominated_course_cd = p_nominated_course_cd AND
3250 sequence_number = p_sequence_number ;
3251
3252 v_new_ofr_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
3253 v_ofr_dfrmnt_status igs_ad_ps_appl_inst.adm_offer_dfrmnt_status%TYPE;
3254 v_s_ofr_dfrmnt_status igs_ad_ofrdfrmt_stat.s_adm_offer_dfrmnt_status%TYPE;
3255 BEGIN
3256 v_new_ofr_dfrmnt_status := igs_ad_gen_008.admp_get_saods(p_offer_deferment_status);
3257 IF v_new_ofr_dfrmnt_status = 'CONFIRM' THEN
3258 OPEN cur_offer_dfrmnt_status (p_person_id,p_admission_appl_number,p_nominated_course_cd,
3259 p_sequence_number);
3260 FETCH cur_offer_dfrmnt_status INTO v_ofr_dfrmnt_status ;
3261 CLOSE cur_offer_dfrmnt_status;
3262 v_s_ofr_dfrmnt_status :=igs_ad_gen_008.admp_get_saods(v_ofr_dfrmnt_status) ;
3263 IF v_s_ofr_dfrmnt_status NOT IN ('APPROVED','CONFIRM') THEN
3264 p_message_name := 'IGS_AD_CONF_WITHOUT_APR';
3265 RETURN FALSE;
3266 ELSE
3267 p_message_name :=NULL;
3268 RETURN TRUE;
3269 END IF;
3270 ELSE
3271 p_message_name :=NULL;
3272 RETURN TRUE;
3273 END IF;
3274 END ;
3275 EXCEPTION
3276 WHEN OTHERS THEN
3277 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3278 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aods_update');
3279 IGS_GE_MSG_STACK.ADD;
3280 App_Exception.Raise_Exception;
3281 END admp_val_aods_update;
3282
3283
3284
3285 FUNCTION admp_val_acai_ais( --arvsrini igsm
3286 p_appl_inst_status IN VARCHAR2 ,
3287 p_ais_reason IN VARCHAR2,
3288 p_adm_outcome_status IN VARCHAR2 ,
3289 p_message_name OUT NOCOPY VARCHAR2 )
3290 RETURN BOOLEAN AS
3291 BEGIN -- admp_val_acai_ais
3292 -- Validate the IGS_AD_PS_APPL_INST.appl_inst_status
3293 DECLARE
3294 v_s_appl_inst_status IGS_AD_PS_APPL_INST.appl_inst_status%TYPE;
3295 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
3296 v_message_name VARCHAR2(30);
3297 BEGIN
3298 -- Set the default message number
3299 p_message_name := NULL;
3300 -- Perform item level validations.
3301 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_ais_item (
3302 p_appl_inst_status,
3303 p_ais_reason,
3304 v_message_name) THEN
3305 p_message_name := v_message_name;
3306 RETURN FALSE;
3307 END IF;
3308
3309 -- Set local variables to system values
3310 v_s_appl_inst_status := IGS_AD_GEN_007.ADMP_GET_SAAS(p_appl_inst_status);
3311 v_s_adm_outcome_status := IGS_AD_GEN_008.ADMP_GET_SAOS(p_adm_outcome_status);
3312
3313 IF v_s_appl_inst_status = 'WITHDRAWN' and p_ais_reason IS NULL THEN
3314 p_message_name := 'IGS_AD_WITHD_RSN_MISSING';
3315 RETURN FALSE;
3316 END IF;
3317
3318
3319 -- Validate against the admission outcome status
3320 IF NOT IGS_AD_VAL_ACAI_STATUS.admp_val_ais_aos (
3321 v_s_appl_inst_status,
3322 v_s_adm_outcome_status,
3323 v_message_name) THEN
3324 p_message_name := v_message_name;
3325 RETURN FALSE;
3326 END IF;
3327 -- Return the default value
3328 RETURN TRUE;
3329 END;
3330 EXCEPTION
3331 WHEN OTHERS THEN
3332 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3333 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_acai_ais');
3334 IGS_GE_MSG_STACK.ADD;
3335 App_Exception.Raise_Exception;
3336 END admp_val_acai_ais;
3337
3338
3339
3340
3341 FUNCTION admp_val_ais_item( --arvsrini igsm
3342 p_appl_inst_status IN VARCHAR2 ,
3343 p_ais_reason IN VARCHAR2,
3344 p_message_name OUT NOCOPY VARCHAR2 )
3345 RETURN BOOLEAN AS
3346 -- admp_val_ais_item
3347 -- This module validates the IGS_AD_PS_APPL_INST.appl_inst_status.
3348 -- Validations are:
3349 -- The appl_inst_status must be open.
3350 -- If the appl_inst_status is present then the ais_reason should also be present
3351
3352 v_message_name VARCHAR2(30);
3353 BEGIN
3354 -- Set the default message number
3355 p_message_name := NULL;
3356 -- Validate the closed indicator.
3357 IF (IGS_AD_VAL_ACAI_STATUS.admp_val_ais_closed (
3358 p_appl_inst_status,
3359 v_message_name)=FALSE) THEN
3360 p_message_name := v_message_name;
3361 RETURN FALSE;
3362 END IF;
3363
3364 -- Return the default value
3365 RETURN TRUE;
3366
3367 EXCEPTION
3368 WHEN OTHERS THEN
3369 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3370 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_ais_item');
3371 IGS_GE_MSG_STACK.ADD;
3372 App_Exception.Raise_Exception;
3373 END admp_val_ais_item;
3374
3375
3376
3377 FUNCTION admp_val_ais_closed( --arvsrini igsm
3378 p_appl_inst_status IN VARCHAR2 ,
3379 p_message_name OUT NOCOPY VARCHAR2 )
3380 RETURN BOOLEAN AS
3381 v_closed_ind igs_ad_appl_stat.closed_ind%TYPE;
3382 CURSOR c_ais IS
3383 SELECT ais.closed_ind
3384 FROM igs_ad_appl_stat ais
3385 WHERE ais.adm_appl_status = p_appl_inst_status;
3386 BEGIN
3387 -- Validate if igs_ad_appl_stat.adm_appl_status is closed.
3388 OPEN c_ais;
3389 FETCH c_ais INTO v_closed_ind;
3390 IF (c_ais%FOUND) THEN
3391 IF (v_closed_ind = 'Y') THEN
3392 CLOSE c_ais;
3393 p_message_name := 'IGS_AD_AIS_CLOSED';
3394 RETURN FALSE;
3395 END IF;
3396 END IF;
3397 CLOSE c_ais;
3398 p_message_name := NULL;
3399 RETURN TRUE;
3400 EXCEPTION
3401 WHEN OTHERS THEN
3402 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3403 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_ais_closed');
3404 IGS_GE_MSG_STACK.ADD;
3405 App_Exception.Raise_Exception;
3406 END admp_val_ais_closed;
3407
3408
3409
3410 FUNCTION admp_val_ais_aos( --arvsrini igsm
3411 p_s_appl_inst_status IN VARCHAR2 ,
3412 p_s_adm_outcome_status IN VARCHAR2 ,
3413 p_message_name OUT NOCOPY VARCHAR2 )
3414 RETURN BOOLEAN AS
3415
3416 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
3417 cst_withdrawn CONSTANT VARCHAR2(10) := 'WITHDRAWN';
3418 BEGIN
3419 -- Set the default message number
3420 p_message_name := NULL;
3421 IF p_s_appl_inst_status = cst_withdrawn AND p_s_adm_outcome_status <> cst_pending THEN
3422 p_message_name := 'IGS_AD_AIS_OUT_PEND';
3423 RETURN FALSE;
3424 END IF;
3425 -- Return the default value
3426 RETURN TRUE;
3427
3428 EXCEPTION
3429 WHEN OTHERS THEN
3430 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3431 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_ais_aos');
3432 IGS_GE_MSG_STACK.ADD;
3433 App_Exception.Raise_Exception;
3434 END admp_val_ais_aos;
3435
3436 END igs_ad_val_acai_status;
[Home] [Help]