[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_008
Source
1 PACKAGE BODY igs_ad_gen_008 AS
2 /* $Header: IGSAD08B.pls 115.13 2003/11/28 13:19:45 rboddu ship $ */
3
4 Function Admp_Get_Safs(
5 p_adm_fee_status IN VARCHAR2 )
6 RETURN VARCHAR2 IS
7 gv_other_detail VARCHAR2(255);
8 BEGIN --admp_get_safs
9 --Get the s_adm_fee_status for a specified IGS_AD_FEE_STAT.
10 DECLARE
11 v_fee_status IGS_AD_FEE_STAT.s_adm_fee_status%TYPE;
12 CURSOR c_afs IS
13 SELECT s_adm_fee_status
14 FROM IGS_AD_FEE_STAT
15 WHERE adm_fee_status = p_adm_fee_status;
16 BEGIN
17 --initialise v_fee_status
18 v_fee_status := NULL;
19 OPEN c_afs ;
20 FETCH c_afs INTO v_fee_status;
21 CLOSE c_afs ;
22 RETURN v_fee_status;
23 END;
24 EXCEPTION
25 WHEN OTHERS THEN
26 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
27 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_safs');
28 IGS_GE_MSG_STACK.ADD;
29 App_Exception.Raise_Exception;
30 END admp_get_safs;
31
32 Function Admp_Get_Saods(
33 p_adm_offer_dfrmnt_status IN VARCHAR2 )
34 RETURN VARCHAR2 IS
35 gv_other_detail VARCHAR2(255);
36 BEGIN --admp_get_saods
37 --Get the s_adm_offer_dfrmnt_status for a specified IGS_AD_OFRDFRMT_STAT.
38 DECLARE
39 v_dfrmnt_status IGS_AD_OFRDFRMT_STAT.s_adm_offer_dfrmnt_status%TYPE;
40 CURSOR c_aods IS
41 SELECT s_adm_offer_dfrmnt_status
42 FROM IGS_AD_OFRDFRMT_STAT
43 WHERE adm_offer_dfrmnt_status = p_adm_offer_dfrmnt_status;
44 BEGIN
45 --initialise v_dfrmnt_status
46 v_dfrmnt_status := NULL;
47 OPEN c_aods ;
48 FETCH c_aods INTO v_dfrmnt_status;
49 CLOSE c_aods ;
50 RETURN v_dfrmnt_status;
51 END;
52 EXCEPTION
53 WHEN OTHERS THEN
54 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
55 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_saods');
56 IGS_GE_MSG_STACK.ADD;
57 App_Exception.Raise_Exception;
58 END admp_get_saods;
59
60 Function Admp_Get_Saors(
61 p_adm_offer_resp_status IN VARCHAR2 )
62 RETURN VARCHAR2 IS
63 gv_other_detail VARCHAR2(255);
64 BEGIN --admp_get_saors
65 --Get the s_adm_offer_resp_status for a specified IGS_AD_OFR_RESP_STAT.
66 DECLARE
67 v_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
68 CURSOR c_aors IS
69 SELECT s_adm_offer_resp_status
70 FROM IGS_AD_OFR_RESP_STAT
71 WHERE adm_offer_resp_status = p_adm_offer_resp_status;
72 BEGIN
73 --initialise v_resp_status
74 v_resp_status := NULL;
75 OPEN c_aors ;
76 FETCH c_aors INTO v_resp_status;
77 CLOSE c_aors ;
78 RETURN v_resp_status;
79 END;
80 EXCEPTION
81 WHEN OTHERS THEN
82 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
83 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_saors');
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 END admp_get_saors;
87
88 Function Admp_Get_Saos(
89 p_adm_outcome_status IN VARCHAR2 )
90 RETURN VARCHAR2 IS
91 gv_other_detail VARCHAR2(255);
92 --admp_get_saos
93 --Get the s_adm_outcome_status for a specified IGS_AD_OU_STAT.
94 v_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
95 CURSOR c_aos IS
96 SELECT s_adm_outcome_status
97 FROM IGS_AD_OU_STAT
98 WHERE adm_outcome_status = p_adm_outcome_status;
99 BEGIN
100 --initialise v_outcome_status
101 v_outcome_status := NULL;
102 OPEN c_aos ;
103 FETCH c_aos INTO v_outcome_status;
104 CLOSE c_aos ;
105 RETURN v_outcome_status;
106 END admp_get_saos;
107
108 Function Admp_Get_Sauos(
109 p_adm_unit_outcome_status IN VARCHAR2 )
110 RETURN VARCHAR2 IS
111 gv_other_detail VARCHAR2(255);
112 BEGIN --admp_get_sauos
113 --Get the s_adm_unit_outcome_status for a specified IGS_AD_UNIT_OU_STAT
114 DECLARE
115 v_adm_unit_outcome_status IGS_AD_UNIT_OU_STAT.s_adm_outcome_status%TYPE;
116 CURSOR c_auos IS
117 SELECT auos.s_adm_outcome_status
118 FROM IGS_AD_UNIT_OU_STAT auos
119 WHERE auos.adm_unit_outcome_status = p_adm_unit_outcome_status;
120 BEGIN
121 --initialise v_adm_unit_outcome_status
122 v_adm_unit_outcome_status := NULL;
123 OPEN c_auos ;
124 FETCH c_auos INTO v_adm_unit_outcome_status;
125 CLOSE c_auos ;
126 RETURN v_adm_unit_outcome_status;
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF (c_auos%ISOPEN) THEN
130 CLOSE c_auos;
131 END IF;
132 RAISE;
133 END;
134 EXCEPTION
135 WHEN OTHERS THEN
136 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
137 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_sauos');
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END admp_get_sauos;
141
142 Function Admp_Get_Short_Dt(
143 p_adm_cal_type IN VARCHAR2 ,
144 p_adm_ci_sequence_number IN NUMBER )
145 RETURN DATE IS
146 gv_other_detail VARCHAR2(255);
147 BEGIN -- admp_get_short_dt
148 -- Get the short admission date for an admission period. If no short admission
149 -- date alias exists for the admission period then null will be returned.
150 DECLARE
151 CURSOR c_short_dt IS
152 SELECT daiv.alias_val
153 FROM IGS_CA_DA_INST_V daiv,
154 IGS_AD_CAL_CONF sacc
155 WHERE daiv.cal_type = p_adm_cal_type AND
156 daiv.ci_sequence_number = p_adm_ci_sequence_number AND
157 daiv.dt_alias = sacc.adm_appl_short_strt_dt_alias
158 ORDER BY daiv.alias_val DESC;
159 v_short_dt_rec c_short_dt%ROWTYPE;
160 BEGIN
161 -- Get the short admission date for the admission period.
162 OPEN c_short_dt;
163 FETCH c_short_dt INTO v_short_dt_rec;
164 IF (c_short_dt%FOUND) THEN
165 CLOSE c_short_dt;
166 RETURN v_short_dt_rec.alias_val;
167 ELSE
168 CLOSE c_short_dt;
169 RETURN IGS_GE_DATE.IGSDATE(NULL);
170 END IF;
171 END;
172 EXCEPTION
173 WHEN OTHERS THEN
174 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
175 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_short_dt');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END admp_get_short_dt;
179
180 Function Admp_Get_Status_Rule(
181 p_person_id IN NUMBER ,
182 p_correspondence_type IN VARCHAR2 ,
183 p_admission_appl_number NUMBER ,
184 p_nominated_course_cd IN VARCHAR2 ,
185 p_acai_sequence_number IN NUMBER ,
186 p_s_letter_parameter_type IN VARCHAR2 )
187 RETURN VARCHAR2 IS
188 gv_other_detail VARCHAR2(255);
189 BEGIN -- admp_get_status_rule
190 -- This function determines status and other values and passes them into a call
191 -- to a rules function
192 -- which will return text to be displayed on a letter according to rules
193 -- defined for the passed parameters.
194 DECLARE
195 v_message_name varchar2(30);
196 v_effective_dt DATE;
197 v_out_string VARCHAR2(2000);
198 v_late BOOLEAN;
199 v_encumbrance BOOLEAN;
200 v_course_invalid BOOLEAN;
201 v_reconsideration BOOLEAN;
202 v_incomplete BOOLEAN;
203 v_late_ind CHAR;
204 v_return_type VARCHAR2(255);
205 v_apcs_pref_limit_ind VARCHAR2(255);
206 v_apcs_app_fee_ind VARCHAR2(255);
207 v_apcs_late_app_ind VARCHAR2(255);
208 v_apcs_late_fee_ind VARCHAR2(255);
209 v_apcs_chkpencumb_ind VARCHAR2(255);
210 v_apcs_fee_assess_ind VARCHAR2(255);
211 v_apcs_corcategry_ind VARCHAR2(255);
212 v_apcs_enrcategry_ind VARCHAR2(255);
213 v_apcs_chkcencumb_ind VARCHAR2(255);
214 v_apcs_unit_set_ind VARCHAR2(255);
215 v_apcs_un_crs_us_ind VARCHAR2(255);
216 v_apcs_chkuencmb_ind VARCHAR2(255);
217 v_apcs_unit_restr_ind VARCHAR2(255);
218 v_apcs_unit_restriction_num VARCHAR2(255);
219 v_apcs_un_dob_ind VARCHAR2(255);
220 v_apcs_un_title_ind VARCHAR2(255);
221 v_apcs_asses_cond_ind VARCHAR2(255);
222 v_apcs_fee_cond_ind VARCHAR2(255);
223 v_apcs_doc_cond_ind VARCHAR2(255);
224 v_apcs_multi_off_ind VARCHAR2(255);
225 v_apcs_multi_off_restrict_num VARCHAR2(255);
226 v_apcs_set_otcome_ind VARCHAR2(255);
227 v_apcs_override_o_ind VARCHAR2(255);
228 v_apcs_defer_ind_ind VARCHAR2(255);
229 v_apcs_ack_app_ind VARCHAR2(255);
230 v_apcs_outcome_lt_ind VARCHAR2(255);
231 v_apcs_pre_enrol_ind VARCHAR2(255);
232 v_mandatory_athletics BOOLEAN;
233 v_mandatory_alternate BOOLEAN;
234 v_mandatory_address BOOLEAN;
235 v_mandatory_disability BOOLEAN;
236 v_mandatory_visa BOOLEAN;
237 v_mandatory_finance BOOLEAN;
238 v_mandatory_notes BOOLEAN;
239 v_mandatory_statistics BOOLEAN;
240 v_mandatory_alias BOOLEAN;
241 v_mandatory_tertiary BOOLEAN;
242 v_mandatory_aus_sec_ed BOOLEAN;
243 v_mandatory_os_sec_ed BOOLEAN;
244 v_mandatory_employment BOOLEAN;
245 v_mandatory_membership BOOLEAN;
246 v_mandatory_dob BOOLEAN;
247 v_mandatory_title BOOLEAN;
248 v_mandatory_referee BOOLEAN;
249 v_mandatory_scholarship BOOLEAN;
250 v_mandatory_lang_prof BOOLEAN;
251 v_mandatory_interview BOOLEAN;
252 v_mandatory_exchange BOOLEAN;
253 v_mandatory_adm_test BOOLEAN;
254 v_mandatory_fee_assess BOOLEAN;
255 v_mandatory_cor_category BOOLEAN;
256 v_mandatory_enr_category BOOLEAN;
257 v_mandatory_research BOOLEAN;
258 v_mandatory_rank_app BOOLEAN;
259 v_mandatory_completion BOOLEAN;
260 v_mandatory_rank_set BOOLEAN;
261 v_mandatory_basis_adm BOOLEAN;
262 v_mandatory_crs_international BOOLEAN;
263 v_mandatory_ass_tracking BOOLEAN;
264 v_mandatory_adm_code BOOLEAN;
265 v_mandatory_fund_source BOOLEAN;
266 v_mandatory_location BOOLEAN;
267 v_mandatory_att_mode BOOLEAN;
268 v_mandatory_att_type BOOLEAN;
269 v_mandatory_unit_set BOOLEAN;
270 v_valid_athletics BOOLEAN;
271 v_valid_alternate BOOLEAN;
272 v_valid_address BOOLEAN;
273 v_valid_disability BOOLEAN;
274 v_valid_visa BOOLEAN;
275 v_valid_finance BOOLEAN;
276 v_valid_notes BOOLEAN;
277 v_valid_statistics BOOLEAN;
278 v_valid_alias BOOLEAN;
279 v_valid_tertiary BOOLEAN;
280 v_valid_aus_sec_ed BOOLEAN;
281 v_valid_os_sec_ed BOOLEAN;
282 v_valid_employment BOOLEAN;
283 v_valid_membership BOOLEAN;
284 v_valid_dob BOOLEAN;
285 v_valid_title BOOLEAN;
286 v_valid_referee BOOLEAN;
287 v_valid_scholarship BOOLEAN;
288 v_valid_lang_prof BOOLEAN;
289 v_valid_interview BOOLEAN;
290 v_valid_exchange BOOLEAN;
291 v_valid_adm_test BOOLEAN;
292 v_valid_fee_assess BOOLEAN;
293 v_valid_cor_category BOOLEAN;
294 v_valid_enr_category BOOLEAN;
295 v_valid_research BOOLEAN;
296 v_valid_rank_app BOOLEAN;
297 v_valid_completion BOOLEAN;
298 v_valid_rank_set BOOLEAN;
299 v_valid_basis_adm BOOLEAN;
300 v_valid_crs_international BOOLEAN;
301 v_valid_ass_tracking BOOLEAN;
302 v_valid_adm_code BOOLEAN;
303 v_valid_fund_source BOOLEAN;
304 v_valid_location BOOLEAN;
305 v_valid_att_mode BOOLEAN;
306 v_valid_att_type BOOLEAN;
307 v_valid_unit_set BOOLEAN;
308 v_valid_excurr BOOLEAN;
309 v_mandatory_evaluation_tab BOOLEAN;
310 v_mandatory_prog_approval BOOLEAN;
311 v_mandatory_indices BOOLEAN;
312 v_mandatory_tst_scores BOOLEAN;
313 v_mandatory_outcome BOOLEAN ;
314 v_mandatory_override BOOLEAN ;
315 v_mandatory_spl_consider BOOLEAN ;
316 v_mandatory_cond_offer BOOLEAN ;
317 v_mandatory_offer_dead BOOLEAN ;
318 v_mandatory_offer_resp BOOLEAN ;
319 v_mandatory_offer_defer BOOLEAN ;
320 v_mandatory_offer_compl BOOLEAN ;
321 v_mandatory_transfer BOOLEAN ;
322 v_mandatory_other_inst BOOLEAN ;
323 v_mandatory_edu_goals BOOLEAN ;
324 v_mandatory_acad_interest BOOLEAN ;
325 v_mandatory_app_intent BOOLEAN ;
326 v_mandatory_spl_interest BOOLEAN ;
327 v_mandatory_spl_talents BOOLEAN ;
328 v_mandatory_miscell BOOLEAN ;
329 v_mandatory_fees BOOLEAN ;
330 v_mandatory_program BOOLEAN ;
331 v_mandatory_completness BOOLEAN ;
332 v_mandatory_creden BOOLEAN ;
333 v_mandatory_review_det BOOLEAN ;
334 v_mandatory_recomm_det BOOLEAN ;
335 v_mandatory_fin_aid BOOLEAN ;
336 v_mandatory_acad_honors BOOLEAN ;
337 v_mandatory_des_unitsets BOOLEAN ; -- added for 2382599
338 v_mandatory_excurr BOOLEAN ; -- added for 2682078 Person actitivies by rrengara on 24-NOV-2002
339
340 CURSOR c_acaiv IS
341 SELECT aa.person_id,
342 aa.adm_appl_status,
343 aa.adm_fee_status,
344 aa.admission_cat,
345 aa.s_admission_process_type,
346 aa.appl_dt,
347 acaiv.adm_doc_status,
348 acaiv.adm_entry_qual_status,
349 acaiv.late_adm_fee_status,
350 acaiv.adm_outcome_status,
351 acaiv.adm_cndtnl_offer_status,
352 acaiv.adm_offer_resp_status,
353 acaiv.adm_offer_dfrmnt_status,
354 aca.req_for_reconsideration_ind,
355 acaiv.course_cd,
356 acaiv.crv_version_number,
357 aa.acad_cal_type,
358 aa.acad_ci_sequence_number,
359 acaiv.location_cd,
360 acaiv.attendance_mode,
361 acaiv.attendance_type,
362 acaiv.adm_cal_type,
363 acaiv.adm_ci_sequence_number
364 FROM
365 igs_ad_appl aa,
366 igs_ad_ps_appl_inst acaiv, /* Replacing IGS_AD_PS_APPL_INST_APLINST_V with base tables Bug: 3150054 */
367 igs_ad_ps_appl aca
368 WHERE
369 acaiv.person_id = p_person_id AND
370 acaiv.admission_appl_number = p_admission_appl_number AND
371 acaiv.nominated_course_cd = p_nominated_course_cd AND
372 acaiv.sequence_number = p_acai_sequence_number AND
373 aa.person_id = acaiv.person_id AND
374 aa.admission_appl_number = acaiv.admission_appl_number AND
375 aa.person_id = aca.person_id AND
376 acaiv.admission_appl_number = aca.admission_appl_number AND
377 acaiv.nominated_course_cd = aca.nominated_course_cd;
378 v_acaiv_rec c_acaiv%ROWTYPE;
379 BEGIN
380 -- Validate parameters
381 IF p_person_id IS NULL OR
382 p_admission_appl_number IS NULL OR
383 p_nominated_course_cd IS NULL OR
384 p_acai_sequence_number IS NULL THEN
385 RETURN NULL;
386 END IF;
387 v_effective_dt := TRUNC(SYSDATE);
388 OPEN c_acaiv;
389 FETCH c_acaiv INTO v_acaiv_rec;
390 CLOSE c_acaiv;
391 IGS_AD_GEN_004.admp_get_apcs_val(
392 v_acaiv_rec.admission_cat,
393 v_acaiv_rec.s_admission_process_type,
394 v_apcs_pref_limit_ind, -- OUT,
395 v_apcs_app_fee_ind, -- OUT,
396 v_apcs_late_app_ind, -- OUT,
397 v_apcs_late_fee_ind, -- OUT,
398 v_apcs_chkpencumb_ind, -- OUT,
399 v_apcs_fee_assess_ind, -- OUT,
400 v_apcs_corcategry_ind, -- OUT,
401 v_apcs_enrcategry_ind, -- OUT,
402 v_apcs_chkcencumb_ind, -- OUT,
403 v_apcs_unit_set_ind, -- OUT,
404 v_apcs_un_crs_us_ind, -- OUT,
405 v_apcs_chkuencmb_ind, -- OUT,
406 v_apcs_unit_restr_ind, -- OUT,
407 v_apcs_unit_restriction_num, -- OUT,
408 v_apcs_un_dob_ind, -- OUT,
409 v_apcs_un_title_ind, -- OUT,
410 v_apcs_asses_cond_ind, -- OUT,
411 v_apcs_fee_cond_ind, -- OUT,
412 v_apcs_doc_cond_ind, -- OUT,
413 v_apcs_multi_off_ind, -- OUT,
414 v_apcs_multi_off_restrict_num, -- OUT,
415 v_apcs_set_otcome_ind, -- OUT,
416 v_apcs_override_o_ind, -- OUT,
417 v_apcs_defer_ind_ind, -- OUT,
418 v_apcs_ack_app_ind, -- OUT,
419 v_apcs_outcome_lt_ind, -- OUT,
420 v_apcs_pre_enrol_ind); -- OUT)
421 IF IGS_AD_VAL_ACAI.admp_val_acai_late(
422 v_acaiv_rec.appl_dt,
423 v_acaiv_rec.course_cd,
424 v_acaiv_rec.crv_version_number,
425 v_acaiv_rec.acad_cal_type,
426 v_acaiv_rec.location_cd,
427 v_acaiv_rec.attendance_mode,
428 v_acaiv_rec.attendance_type,
429 v_acaiv_rec.adm_cal_type,
430 v_acaiv_rec.adm_ci_sequence_number,
431 v_acaiv_rec.admission_cat,
432 v_acaiv_rec.s_admission_process_type,
433 v_apcs_late_app_ind,
434 v_message_name) = FALSE THEN -- OUT NOCOPY
435 v_late := TRUE;
436 ELSE
437 v_late := FALSE;
438 END IF;
439 IF IGS_AD_VAL_ACAI.admp_val_acai_encmb(
440 v_acaiv_rec.person_id,
441 v_acaiv_rec.course_cd,
442 v_acaiv_rec.adm_cal_type,
443 v_acaiv_rec.adm_ci_sequence_number,
444 v_apcs_chkcencumb_ind,
445 'Y',
446 v_message_name, -- OUT,
447 v_return_type) = FALSE THEN -- OUT NOCOPY
448 v_encumbrance := TRUE;
449 ELSE
450 v_encumbrance := FALSE;
451 END IF;
452 IF IGS_AD_VAL_ACAI.admp_val_acai_cop(
453 v_acaiv_rec.course_cd,
454 v_acaiv_rec.crv_version_number,
455 v_acaiv_rec.location_cd,
456 v_acaiv_rec.attendance_mode,
457 v_acaiv_rec.attendance_type,
458 v_acaiv_rec.acad_cal_type,
459 v_acaiv_rec.acad_ci_sequence_number,
460 v_acaiv_rec.adm_cal_type,
461 v_acaiv_rec.adm_ci_sequence_number,
462 v_acaiv_rec.admission_cat,
463 v_acaiv_rec.s_admission_process_type,
464 'N',
465 v_acaiv_rec.appl_dt,
466 v_apcs_late_app_ind,
467 'N',
468 v_message_name, -- OUT,
469 v_return_type, -- OUT,
470 v_late_ind) = FALSE THEN -- OUT NOCOPY
471 v_course_invalid := TRUE;
472 ELSE
473 v_course_invalid := FALSE;
474 END IF;
475 IF v_acaiv_rec.req_for_reconsideration_ind = 'Y' THEN
476 v_reconsideration := TRUE;
477 ELSE
478 v_reconsideration := FALSE;
479 END IF;
480 -- Call procedure to find mandatory fields
481 IGS_AD_GEN_003.admp_get_apcs_mndtry(
482 v_acaiv_rec.admission_cat,
483 v_acaiv_rec.s_admission_process_type,
484 v_mandatory_athletics, -- OUT,
485 v_mandatory_alternate, -- OUT,
486 v_mandatory_address, -- OUT,
487 v_mandatory_disability, -- OUT,
488 v_mandatory_visa, -- OUT,
489 v_mandatory_finance, -- OUT,
490 v_mandatory_notes, -- OUT,
491 v_mandatory_statistics, -- OUT,
492 v_mandatory_alias, -- OUT,
493 v_mandatory_tertiary, -- OUT,
494 v_mandatory_aus_sec_ed, -- OUT,
495 v_mandatory_os_sec_ed, -- OUT,
496 v_mandatory_employment, -- OUT,
497 v_mandatory_membership, -- OUT,
498 v_mandatory_dob, -- OUT,
499 v_mandatory_title, -- OUT,
500 v_mandatory_referee, -- OUT,
501 v_mandatory_scholarship,-- OUT,
502 v_mandatory_lang_prof, -- OUT,
503 v_mandatory_interview, -- OUT,
504 v_mandatory_exchange, -- OUT,
505 v_mandatory_adm_test, -- added 17/11/98
506 v_mandatory_fee_assess, -- OUT,
507 v_mandatory_cor_category, -- OUT,
508 v_mandatory_enr_category, -- OUT,
509 v_mandatory_research, -- OUT,
510 v_mandatory_rank_app, -- OUT,
511 v_mandatory_completion, -- OUT,
512 v_mandatory_rank_set, -- OUT,
513 v_mandatory_basis_adm, -- OUT,
514 v_mandatory_crs_international, -- OUT,
515 v_mandatory_ass_tracking, -- OUT,
516 v_mandatory_adm_code, -- OUT,
517 v_mandatory_fund_source, --added 17/11/98
518 v_mandatory_location, -- OUT,
519 v_mandatory_att_mode, -- OUT,
520 v_mandatory_att_type, -- OUT,
521 v_mandatory_unit_set, -- OUT)
522 v_mandatory_evaluation_tab,
523 v_mandatory_prog_approval ,
524 v_mandatory_indices ,
525 v_mandatory_tst_scores ,
526 v_mandatory_outcome ,
527 v_mandatory_override ,
528 v_mandatory_spl_consider ,
529 v_mandatory_cond_offer ,
530 v_mandatory_offer_dead ,
531 v_mandatory_offer_resp ,
532 v_mandatory_offer_defer ,
533 v_mandatory_offer_compl ,
534 v_mandatory_transfer ,
535 v_mandatory_other_inst ,
536 v_mandatory_edu_goals ,
537 v_mandatory_acad_interest ,
538 v_mandatory_app_intent ,
539 v_mandatory_spl_interest ,
540 v_mandatory_spl_talents ,
541 v_mandatory_miscell ,
542 v_mandatory_fees ,
543 v_mandatory_program ,
544 v_mandatory_completness ,
545 v_mandatory_creden ,
546 v_mandatory_review_det ,
547 v_mandatory_recomm_det ,
548 v_mandatory_fin_aid ,
549 v_mandatory_acad_honors ,
550 v_mandatory_des_unitsets, -- added for 2382599
551 v_mandatory_excurr); -- added for Bug 2682078 Person activities by rrengara on 24-NOV-2002
552
553 -- Call function to validate any mandatory steps exists for IGS_PE_PERSON.
554 v_incomplete := FALSE;
555 IF IGS_AD_VAL_ACAI.admp_val_pe_comp(
556 p_person_id,
557 v_effective_dt,
558 v_mandatory_athletics,
559 v_mandatory_alternate,
560 v_mandatory_address,
561 v_mandatory_disability,
562 v_mandatory_visa,
563 v_mandatory_finance,
564 v_mandatory_notes,
565 v_mandatory_statistics,
566 v_mandatory_alias,
567 v_mandatory_tertiary,
568 v_mandatory_aus_sec_ed,
569 v_mandatory_os_sec_ed,
570 v_mandatory_employment,
571 v_mandatory_membership,
572 v_mandatory_dob,
573 v_mandatory_title,
574 v_mandatory_excurr,
575 v_message_name, -- OUT,
576 v_valid_athletics, -- OUT,
577 v_valid_alternate, -- OUT,
578 v_valid_address, -- OUT,
579 v_valid_disability, -- OUT,
580 v_valid_visa, -- OUT,
581 v_valid_finance, -- OUT,
582 v_valid_notes, -- OUT,
583 v_valid_statistics, -- OUT,
584 v_valid_alias, -- OUT,
585 v_valid_tertiary, -- OUT,
586 v_valid_aus_sec_ed, -- OUT,
587 v_valid_os_sec_ed, -- OUT,
588 v_valid_employment, -- OUT,
589 v_valid_membership, -- OUT,
590 v_valid_dob, -- OUT,
591 v_valid_title,
592 v_valid_excurr ) = FALSE THEN -- OUT NOCOPY
593 v_incomplete := TRUE;
594 END IF;
595 -- Call function to validate any mandatory steps exists for admission IGS_PS_COURSE
596 -- application instance.
597 IF IGS_AD_VAL_ACAI.admp_val_acai_comp(
598 p_person_id,
599 p_admission_appl_number,
600 p_nominated_course_cd,
601 p_acai_sequence_number,
602 v_acaiv_rec.course_cd,
603 v_acaiv_rec.crv_version_number, --added 17/11/98
604 v_acaiv_rec.s_admission_process_type,
605 v_effective_dt,
606 v_mandatory_referee,
607 v_mandatory_scholarship,
608 v_mandatory_lang_prof,
609 v_mandatory_interview,
610 v_mandatory_exchange,
611 v_mandatory_adm_test, --added 17/11/98
612 v_mandatory_fee_assess,
613 v_mandatory_cor_category,
614 v_mandatory_enr_category,
615 v_mandatory_research,
616 v_mandatory_rank_app,
617 v_mandatory_completion,
618 v_mandatory_rank_set,
619 v_mandatory_basis_adm,
620 v_mandatory_crs_international,
621 v_mandatory_ass_tracking,
622 v_mandatory_adm_code,
623 v_mandatory_fund_source, --added 17/11/98
624 v_mandatory_location,
625 v_mandatory_att_mode,
626 v_mandatory_att_type,
627 v_mandatory_unit_set,
628 v_message_name,
629 v_valid_referee, -- OUT,
630 v_valid_scholarship, -- OUT,
631 v_valid_lang_prof, -- OUT,
632 v_valid_interview, -- OUT,
633 v_valid_exchange, -- OUT,
634 v_valid_adm_test, --added 17/11/98
635 v_valid_fee_assess, -- OUT,
636 v_valid_cor_category, -- OUT,
637 v_valid_enr_category, -- OUT,
638 v_valid_research, -- OUT,
639 v_valid_rank_app, -- OUT,
640 v_valid_completion, -- OUT,
641 v_valid_rank_set, -- OUT,
642 v_valid_basis_adm, -- OUT,
643 v_valid_crs_international, -- OUT,
644 v_valid_ass_tracking, -- OUT,
645 v_valid_adm_code, -- OUT,
646 v_valid_fund_source, --added 17/11/98
647 v_valid_location, -- OUT,
648 v_valid_att_mode, -- OUT,
649 v_valid_att_type, -- OUT,
650 v_valid_unit_set) = FALSE THEN -- OUT NOCOPY
651 v_incomplete := TRUE;
652 END IF;
653 v_out_string := IGS_RU_GEN_004.rulp_val_adm_status(
654 p_s_letter_parameter_type,
655 v_acaiv_rec.adm_appl_status,
656 v_acaiv_rec.adm_fee_status,
657 v_acaiv_rec.adm_doc_status,
658 v_acaiv_rec.adm_entry_qual_status,
659 v_acaiv_rec.late_adm_fee_status,
660 v_acaiv_rec.adm_outcome_status,
661 v_acaiv_rec.adm_cndtnl_offer_status,
662 v_acaiv_rec.adm_offer_resp_status,
663 v_acaiv_rec.adm_offer_dfrmnt_status,
664 v_reconsideration,
665 v_encumbrance,
666 v_course_invalid,
667 v_late,
668 v_incomplete,
669 p_correspondence_type,
670 v_valid_alternate,
671 v_valid_address,
672 v_valid_disability,
673 v_valid_visa,
674 v_valid_finance,
675 v_valid_notes,
676 v_valid_statistics,
677 v_valid_alias,
678 v_valid_tertiary,
679 v_valid_aus_sec_ed,
680 v_valid_os_sec_ed,
681 v_valid_employment,
682 v_valid_membership,
683 v_valid_dob,
684 v_valid_title,
685 v_valid_referee,
686 v_valid_scholarship,
687 v_valid_lang_prof,
688 v_valid_interview,
689 v_valid_exchange,
690 v_valid_adm_test,
691 v_valid_fee_assess,
692 v_valid_cor_category,
693 v_valid_enr_category,
694 v_valid_research,
695 v_valid_rank_app,
696 v_valid_completion,
697 v_valid_rank_set,
698 v_valid_basis_adm,
699 v_valid_crs_international,
700 v_valid_ass_tracking,
701 v_valid_adm_code,
702 v_valid_fund_source,
703 v_valid_location,
704 v_valid_att_mode,
705 v_valid_att_type,
706 v_valid_unit_set);
707 RETURN v_out_string;
708 EXCEPTION
709 WHEN OTHERS THEN
710 IF(c_acaiv%ISOPEN) THEN
711 CLOSE c_acaiv;
712 END IF;
713 RAISE;
714 END;
715 EXCEPTION
716 WHEN OTHERS THEN
717 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
718 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_status_rule');
719 IGS_GE_MSG_STACK.ADD;
720 App_Exception.Raise_Exception;
721 END admp_get_status_rule ;
722
723 Function Admp_Get_Sys_Aas(
724 p_s_adm_appl_status IN VARCHAR2 )
725 RETURN VARCHAR2 IS
726 gv_other_detail VARCHAR2(255);
727 BEGIN -- admp_get_sys_aas
728 -- routine to return the user-defined system default admission application
729 -- status for the given system admission application status
730 DECLARE
731 CURSOR c_aas IS
732 SELECT aas.adm_appl_status
733 FROM IGS_AD_APPL_STAT aas
734 WHERE aas.s_adm_appl_status = p_s_adm_appl_status AND
735 aas.system_default_ind = 'Y' AND
736 aas.closed_ind = 'N';
737 v_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE := NULL;
738 BEGIN
739 FOR v_aas_rec IN c_aas LOOP
740 IF c_aas%ROWCOUNT > 1 THEN
741 v_adm_appl_status := NULL;
742 exit;
743 END IF;
744 v_adm_appl_status := v_aas_rec.adm_appl_status;
745 END LOOP;
746 -- return null if no records or more than one record found
747 RETURN v_adm_appl_status;
748 END;
749 EXCEPTION
750 WHEN OTHERS THEN
751 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
752 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.admp_get_sys_aas');
753 IGS_GE_MSG_STACK.ADD;
754 App_Exception.Raise_Exception;
755 END admp_get_sys_aas;
756
757 PROCEDURE get_acad_cal (
758 p_adm_cal_type IN OUT NOCOPY igs_ca_type.cal_type%TYPE,
759 p_adm_seq IN OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
760 p_acad_cal_type OUT NOCOPY igs_ca_type.cal_type%TYPE,
761 p_acad_seq OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
762 p_adm_alternate_code OUT NOCOPY igs_ca_inst.alternate_code%TYPE,
763 p_message OUT NOCOPY VARCHAR2
764 )
765 AS
766
767 ------------------------------------------------------------------
768 --Created by : Karthikeyan Mohan, Oracle India
769 --Date created: 31-AUG-2001
770 --
771 --Purpose: This procedure gets the Admission Sequence Number if not passed
772 -- Academic Calendar and the Academic Calendar Sequence
773 --
774 --
775 --Known limitations/enhancements and/or remarks:
776 --
777 --Change History:
778 --Who When What
779 -- kamohan 3/30/02 For bug 2252875
780 -- kamohan 5/may-2002 Bug 2321651 Modified the cursor query for defaulting the alternate code
781 --hreddych 16-jun-2003 2890319 Modified the cur_sequence query
782 -------------------------------------------------------------------
783 l_start_dt DATE;
784 l_end_dt DATE;
785 l_out VARCHAR2 (2000);
786
787
788 -- Cursor for getting the Calendar Sequence for the
789 -- Calendar Type. The Calendar Instance Selected for this calendar
790 -- should be the nearest one to the System Date
791 CURSOR cur_sequence (cp_cal_type igs_ca_type.cal_type%TYPE)
792 IS
793 SELECT ci_adm.sequence_number adm_sequence_number,
794 ci_acad.cal_type acad_cal_type,
795 ci_acad.sequence_number acad_sequence_number,
796 ci_adm.alternate_code adm_alternate_code
797 FROM igs_ca_type ct_adm,
798 igs_ca_inst ci_adm,
799 igs_ca_stat cs,
800 igs_ca_inst_rel cir,
801 igs_ca_inst ci_acad,
802 igs_ca_type ct_acad
803 WHERE ct_adm.cal_type = cp_cal_type
804 AND ct_adm.cal_type = ci_adm.cal_type
805 AND SYSDATE <= ci_adm.end_dt
806 AND ct_adm.s_cal_cat = 'ADMISSION'
807 AND ci_adm.cal_status = cs.cal_status
808 AND cs.s_cal_status = 'ACTIVE'
809 AND ci_adm.cal_type = cir.sub_cal_type
810 AND ci_adm.sequence_number = cir.sub_ci_sequence_number
811 AND ct_acad.cal_type = ci_acad.cal_type
812 AND ci_acad.cal_type = cir.sup_cal_type
813 AND ci_acad.sequence_number = cir.sup_ci_sequence_number
814 AND ct_acad.s_cal_cat = 'ACADEMIC'
815 ORDER BY ci_adm.start_dt;
816
817 sequence_rec cur_sequence%ROWTYPE;
818
819 CURSOR cur_adm_cal_conf
820 IS
821 SELECT inq_cal_type
822 FROM igs_ad_cal_conf;
823 BEGIN
824 p_message := NULL;
825
826
827 -- If the Admission Calendar Instance is not defined
828 -- then derive the Admisssion Calendar Instance
829
830 IF p_adm_seq IS NULL
831 THEN
832 IF p_adm_cal_type IS NULL
833 THEN
834 OPEN cur_adm_cal_conf;
835 FETCH cur_adm_cal_conf INTO p_adm_cal_type;
836 CLOSE cur_adm_cal_conf;
837
838 -- If there is no default calendar set up then
839 -- return the proper error message.
840
841 IF p_adm_cal_type IS NULL
842 THEN
843 p_message := 'IGS_AD_INQ_DFLT_ADMCAL_NOTDFN';
844 RETURN;
845 END IF;
846 END IF;
847
848 OPEN cur_sequence (p_adm_cal_type);
849 FETCH cur_sequence INTO sequence_rec;
850
851 -- If there is no admission calendar instance without any academic calendar associated with it and does not satisy the start date, end date
852 -- conditions provide proper error message.
853
854 IF cur_sequence%NOTFOUND
855 THEN
856 p_message := 'IGS_AD_INQ_ADMCAL_SEQ_NOTDFN';
857 RETURN;
858 END IF;
859
860 p_adm_seq := sequence_rec.adm_sequence_number;
861 p_acad_cal_type := sequence_rec.acad_cal_type;
862 p_acad_seq := sequence_rec.acad_sequence_number;
863 p_adm_alternate_code := sequence_rec.adm_alternate_code;
864 CLOSE cur_sequence;
865 END IF;
866 EXCEPTION
867 WHEN OTHERS
868 THEN
869 IF cur_adm_cal_conf%ISOPEN
870 THEN
871 CLOSE cur_adm_cal_conf;
872 END IF;
873
874 IF cur_sequence%ISOPEN
875 THEN
876 CLOSE cur_sequence;
877 END IF;
878 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
879 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_008.get_acad_cal');
880 IGS_GE_MSG_STACK.ADD;
881 App_Exception.Raise_Exception;
882 END get_acad_cal;
883
884 END igs_ad_gen_008;