[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PRC_TAC_OFFER
Source
1 PACKAGE BODY Igs_Ad_Prc_Tac_Offer AS
2 /* $Header: IGSAD15B.pls 120.2 2005/10/18 02:48:51 appldev ship $ */
3
4 /******************************************************************
5 Created By:
6 Date Created By:
7 Purpose:
8 Known limitations,enhancements,remarks:
9 Change History
10 Who When What
11 sarakshi 20-May-2003 Enh#2858431,admp_ins_tac_course modified to replace TAC-FEE,TAC-HECS to OTHERS
12 vchappid 29-Aug-2001 Added new parameters into function calls, Enh Bug#1964478
13 ssawhney feb15 bug 2225917 SWCR008 remove customer creation.
14 cdcruz feb18 bug 2217104 Admit to future term Enhancement,updated tbh call for
15 new columns being added to IGS_AD_PS_APPL_INST
16 knag 21-Nov-2002 Added new parameters to admp_ins_adm_appl for bug 2664410
17 and new parameters to call to igs_ad_appl_pkg.insert_row
18 pkpatel 01-DEC-2002 Bug NO: 2599109 (Sevis DLD)
19 Modified the the signatute of call to TBH IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW
20 anilk 18-FEB-2003 Bug#2784198
21 Added closed_ind = 'N' to where clause for cursor c_hl
22 asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row and update_row changed
23 gmaheswa 19-Nov-2003 3227107: Modified cursor c_pa in admp_ins_person_addr to select records having active status.
24 ******************************************************************/
25
26 --
27 -- Get the admission category for this COURSE offering option
28 FUNCTION admp_get_ac_cooac(
29 p_coo_id IN NUMBER ,
30 p_admission_cat IN OUT NOCOPY VARCHAR2 ,
31 p_message_name OUT NOCOPY VARCHAR2 )
32 RETURN BOOLEAN IS
33 BEGIN -- admp_get_ac_cooac
34 -- This module retrieves the admission category from the IGS_PS_COURSE
35 -- offering option admission category table
36 DECLARE
37 v_x VARCHAR2(1) DEFAULT NULL;
38 v_ret_val BOOLEAN DEFAULT TRUE;
39 CURSOR c_cooac IS
40 SELECT admission_cat
41 FROM IGS_PS_OF_OPT_AD_CAT
42 WHERE coo_id = p_coo_id AND
43 system_default_ind = 'Y';
44 CURSOR c_cooac2 IS
45 SELECT 'x'
46 FROM IGS_PS_OF_OPT_AD_CAT
47 WHERE coo_id = p_coo_id AND
48 admission_cat = p_admission_cat;
49 BEGIN
50 p_message_name := NULL;
51 IF p_admission_cat IS NULL THEN
52 -- Select default admission category
53 OPEN c_cooac;
54 LOOP
55 FETCH c_cooac INTO p_admission_cat;
56 IF (c_cooac%ROWCOUNT = 0 OR
57 c_cooac%ROWCOUNT > 1) THEN
58 p_message_name := 'IGS_AD_CANNOTFIND_ADMCAT';
59 v_ret_val := FALSE;
60 END IF;
61 -- EXIT WHEN statement must be after IF statement as
62 -- p_message_name has to be set if no records found before
63 -- exiting the loop
64 EXIT WHEN (c_cooac%NOTFOUND);
65 END LOOP;
66 CLOSE c_cooac;
67 ELSE
68 -- Check that COURSE offering option admission category mapping exists
69 -- Select anything from course_offering_option_adm_cat
70 OPEN c_cooac2;
71 FETCH c_cooac2 INTO v_x;
72 IF (c_cooac2%NOTFOUND) THEN
73 CLOSE c_cooac2;
74 p_message_name := 'IGS_GE_INVALID_VALUE';
75 RETURN FALSE;
76 END IF;
77 CLOSE c_cooac2;
78 END IF;
79 RETURN v_ret_val;
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF (c_cooac%ISOPEN) THEN
83 CLOSE c_cooac;
84 END IF;
85 IF (c_cooac2%ISOPEN) THEN
86 CLOSE c_cooac2;
87 END IF;
88 RETURN FALSE;
89 END;
90 EXCEPTION
91 WHEN OTHERS THEN
92 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
93 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_get_ac_cooac');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 END admp_get_ac_cooac;
97 --
98 -- Insert an Australian secondary education record
99 FUNCTION admp_ins_aus_scn_edu(
100 p_person_id IN NUMBER ,
101 p_result_obtained_yr IN NUMBER ,
102 p_score IN NUMBER ,
103 p_state_cd IN VARCHAR2 ,
104 p_candidate_number IN NUMBER ,
105 p_aus_scndry_edu_ass_type IN VARCHAR2 ,
106 p_secondary_school_cd IN VARCHAR2 ,
107 p_ase_sequence_number OUT NOCOPY NUMBER ,
108 p_message_name OUT NOCOPY VARCHAR2 )
109 RETURN BOOLEAN IS
110 BEGIN -- admp_ins_aus_scn_edu
111 -- This procedure inserts a new IGS_AD_AUS_SEC_EDU record
112 DECLARE
113 v_secondary_school_cd IGS_AD_AUS_SEC_ED_SC.secondary_school_cd%TYPE := NULL;
114 v_ase_sequence_number IGS_AD_AUS_SEC_EDU.sequence_number%TYPE;
115 CURSOR c_ass IS
116 SELECT secondary_school_cd
117 FROM IGS_AD_AUS_SEC_ED_SC
118 WHERE secondary_school_cd = p_secondary_school_cd AND
119 closed_ind = 'N' AND
120 state_cd = p_state_cd;
121 CURSOR c_aseat IS
122 SELECT aus_scndry_edu_ass_type,
123 state_cd
124 FROM IGS_AD_AUSE_ED_AS_TY
125 WHERE aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type OR
126 p_aus_scndry_edu_ass_type IS NULL OR
127 tac_aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type;
128 CURSOR c_ases IS
129 SELECT NVL(MAX(ase_sequence_number), 0) +1
130 FROM IGS_AD_AUS_SEC_ED_SU
131 WHERE person_id = p_person_id;
132 v_ass_rec c_ass%ROWTYPE;
133 v_aseat_rec c_aseat%ROWTYPE;
134 v_aseat_found BOOLEAN := FALSE;
135 v_match_found BOOLEAN := FALSE;
136 v_aus_scndry_edu_ass_type VARCHAR2(255);
137 v_state_cd VARCHAR2(255);
138 lv_rowid VARCHAR2(25);
139 BEGIN
140 p_message_name := NULL;
141 -- Validate Australian Secondary Education information
142 IF p_state_cd IS NULL OR
143 p_score IS NULL OR
144 p_aus_scndry_edu_ass_type IS NULL THEN
145 p_message_name := 'IGS_AD_CANINS_ONE_STCD';
146 RETURN FALSE;
147 END IF;
148 -- Loop though all the states which match the
149 -- secondary education assessment type
150 FOR v_aseat_rec IN c_aseat LOOP
151 v_aus_scndry_edu_ass_type := v_aseat_rec.aus_scndry_edu_ass_type;
152 v_state_cd := v_aseat_rec.state_cd;
153 v_aseat_found := TRUE;
154 -- Validate the state code input parameter
155 IF (v_aseat_rec.state_cd IS NOT NULL AND
156 v_aseat_rec.state_cd = p_state_cd) OR
157 v_aseat_rec.state_cd IS NULL THEN
158 v_match_found := TRUE;
159 EXIT;
160 END IF;
161 END LOOP;
162 -- Record is not found
163 IF NOT v_aseat_found THEN
164 p_message_name := 'IGS_AD_ASSTYPE_NOTVALID_ASSTY';
165 RETURN FALSE;
166 END IF;
167 -- No assessment type match is found
168 IF NOT v_match_found THEN
169 p_message_name := 'IGS_AD_ASSTYPE_NOTVALID_STCD';
170 RETURN FALSE;
171 END IF;
172 -- Validate the secondary school code and include it
173 IF p_secondary_school_cd IS NOT NULL THEN
174 OPEN c_ass;
175 FETCH c_ass INTO v_ass_rec;
176 IF c_ass%FOUND THEN
177 v_secondary_school_cd := v_ass_rec.secondary_school_cd;
178 END IF;
179 CLOSE c_ass;
180 END IF;
181 -- Get the next sequence number for IGS_AD_AUS_SEC_ED_SC for PERSON
182 -- A number will always be returned, at least 1.
183 OPEN c_ases;
184 FETCH c_ases INTO v_ase_sequence_number;
185 CLOSE c_ases;
186 -- Insert the record
187 Igs_Ad_Aus_Sec_Edu_Pkg.Insert_Row (
188 X_Mode => 'R',
189 X_RowId => lv_rowid,
190 X_Person_Id => p_person_id,
191 X_Sequence_Number => v_ase_sequence_number,
192 X_State_Cd => p_state_cd,
193 X_Result_Obtained_Yr => p_result_obtained_yr,
194 X_Score => p_score,
195 X_Aus_Scndry_Edu_Ass_Type => v_aus_scndry_edu_ass_type,
196 X_Candidate_Number => p_candidate_number,
197 X_Secondary_School_Cd => v_secondary_school_cd
198 );
199
200 p_ase_sequence_number := v_ase_sequence_number;
201 RETURN TRUE;
202 END;
203 EXCEPTION
204 WHEN OTHERS THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
206 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_aus_scn_edu');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END admp_ins_aus_scn_edu;
210 --
211 -- Insert an Australian secondary education subject record
212 FUNCTION admp_ins_aus_scn_sub(
213 p_person_id IN NUMBER ,
214 p_ase_sequence_number IN NUMBER ,
215 p_subject_result_yr IN NUMBER ,
216 p_subject_cd IN VARCHAR2 ,
217 p_subject_desc IN VARCHAR2 ,
218 p_subject_mark IN VARCHAR2 ,
219 p_subject_mark_level IN VARCHAR2 ,
220 p_subject_weighting IN VARCHAR2 ,
221 p_subject_ass_type IN VARCHAR2 ,
222 p_notes IN VARCHAR2 ,
223 p_message_name OUT NOCOPY VARCHAR2 )
224 RETURN BOOLEAN IS
225 BEGIN -- admp_ins_aus_scn_sub
226 -- This procedure inserts a new IGS_AD_AUS_SEC_ED_SC record
227 DECLARE
228 CURSOR c_aseat IS
229 SELECT aus_scndry_edu_ass_type
230 FROM IGS_AD_AUSE_ED_AS_TY
231 WHERE aus_scndry_edu_ass_type = p_subject_ass_type OR
232 tac_aus_scndry_edu_ass_type = p_subject_ass_type;
233 v_aseat_rec c_aseat%ROWTYPE;
234 v_subject_ass_type IGS_AD_AUS_SEC_ED_SU.subject_ass_type%TYPE DEFAULT NULL;
235 lv_rowid VARCHAR2(25);
236 BEGIN
237 p_message_name := NULL;
238 -- Validate Parameters
239 IF p_person_id IS NULL OR
240 p_ase_sequence_number IS NULL OR
241 p_subject_result_yr IS NULL OR
242 p_subject_cd IS NULL THEN
243 p_message_name := 'IGS_AD_SEC_EDU_CANINS_PRSNID';
244 RETURN FALSE;
245 END IF;
246 -- Get assessment type
247 OPEN c_aseat;
248 FETCH c_aseat INTO v_aseat_rec;
249 IF c_aseat%FOUND THEN
250 v_subject_ass_type := v_aseat_rec.aus_scndry_edu_ass_type;
251 END IF;
252 CLOSE c_aseat;
253
254 Igs_Ad_Aus_Sec_Ed_Su_Pkg.Insert_Row (
255 X_Mode => 'R',
256 X_RowId => lv_rowid,
257 X_Person_Id => p_person_id,
258 X_Ase_Sequence_Number => p_ase_sequence_number,
259 X_Subject_Result_Yr => p_subject_result_yr,
260 X_Subject_Cd => p_subject_cd,
261 X_Subject_Desc => p_Subject_Desc,
262 X_Subject_Mark => p_Subject_Mark,
263 X_Subject_Mark_Level => p_Subject_Mark_Level,
264 X_Subject_Weighting => p_Subject_Weighting,
265 X_Subject_Ass_Type => p_Subject_Ass_Type,
266 X_Notes => NULL
267 );
268
269 RETURN TRUE;
270 END;
271 EXCEPTION
272 WHEN OTHERS THEN
273 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
274 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_aus_scn_sub');
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception;
277 END admp_ins_aus_scn_sub;
278 --
279 -- Retrieves the admission code and basis for admission type
280 --
281 -- Finds the user defined tertiary edu level of completion.
282 FUNCTION ADMP_GET_LVL_COMP(
283 p_tac_level_of_comp IN VARCHAR2 )
284 RETURN VARCHAR2 IS
285 BEGIN -- ADMP_GET_LVL_COMP
286 --This module finds the user defined tertiary education
287 --level of completion from the TAC level of completion
288 DECLARE
289 v_tertiary_edu_lvl_comp IGS_AD_TER_ED_LV_COM.tertiary_edu_lvl_comp%TYPE;
290 CURSOR c_teloc IS
291 SELECT teloc.tertiary_edu_lvl_comp
292 FROM IGS_AD_TER_ED_LV_COM teloc
293 WHERE teloc.tac_level_of_comp = p_tac_level_of_comp AND
294 teloc.closed_ind = 'N';
295 BEGIN
296 OPEN c_teloc;
297 FETCH c_teloc INTO v_tertiary_edu_lvl_comp;
298 IF (c_teloc%NOTFOUND) THEN
299 v_tertiary_edu_lvl_comp := NULL;
300 END IF;
301 --if multiple records are found just return the first one
302 CLOSE c_teloc;
303 RETURN v_tertiary_edu_lvl_comp;
304 END;
305 EXCEPTION
306 WHEN OTHERS THEN
307 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
308 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_get_lvl_comp');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END ADMP_GET_LVL_COMP;
312 --
313 -- Inserts an admission application record
314 FUNCTION admp_ins_adm_appl(
315 p_person_id IN NUMBER ,
316 p_appl_dt IN DATE ,
317 p_acad_cal_type IN VARCHAR2 ,
318 p_acad_ci_sequence_number IN NUMBER ,
319 p_adm_cal_type IN VARCHAR2 ,
320 p_adm_ci_sequence_number IN NUMBER ,
321 p_admission_cat IN VARCHAR2 ,
322 p_s_admission_process_type IN VARCHAR2 ,
323 p_adm_appl_status IN VARCHAR2 ,
324 p_adm_fee_status IN OUT NOCOPY VARCHAR2 ,
325 p_tac_appl_ind IN VARCHAR2,
326 p_adm_appl_number OUT NOCOPY NUMBER ,
327 p_message_name OUT NOCOPY VARCHAR2,
328 p_spcl_grp_1 IN NUMBER , --Bug#1964478, Parameter added
329 p_spcl_grp_2 IN NUMBER , --Bug#1964478, Parameter added
330 p_common_app IN VARCHAR2, --Bug#1964478, Parameter added
331 p_application_type IN VARCHAR2,
332 p_choice_number IN VARCHAR2,
333 p_routeb_pref IN VARCHAR2,
334 p_alt_appl_id IN VARCHAR2 ) -- Added for Bug 2664410
335 RETURN BOOLEAN IS
336 /******************************************************************
337 Created By:
338 Date Created By:
339 Purpose:
340 Known limitations,enhancements,remarks:
341 Change History
342 Who When What
343 vchappid 29-Aug-2001 Added new parameters into function calls, Enh Bug#1964478
344 rboddu 04-OCT-2002 Creating application with Application_Type. Bug :2599457
345 ******************************************************************/
346
347 BEGIN --admp_ins_adm_appl
348 --Procedure inserts a new IGS_AD_APPL record. It uses an
349 --output parameter to pass back the new admission_appl_number used
350 DECLARE
351 v_dummy CHAR;
352 v_adm_fee_status VARCHAR2(10);
353 v_adm_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
354 v_message_name VARCHAR2(30);
355 v_return_type VARCHAR2(1);
356 v_title_required_ind VARCHAR2(1) DEFAULT 'Y';
357 v_birth_dt_required_ind VARCHAR2(1) DEFAULT 'Y';
358 v_fees_required_ind VARCHAR2(1) DEFAULT 'N';
359 v_person_encmb_chk_ind VARCHAR2(1) DEFAULT 'N';
360 v_cond_offer_fee_allowed_ind VARCHAR2(1) DEFAULT 'N';
361 cst_error CONSTANT VARCHAR2(1) := 'E';
362 cst_warn CONSTANT VARCHAR2(1) := 'W';
363 CURSOR c_apcs (
364 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
365 cp_s_admission_process_type
366 IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
367 SELECT s_admission_step_type
368 FROM IGS_AD_PRCS_CAT_STEP
369 WHERE admission_cat = cp_admission_cat AND
370 s_admission_process_type = cp_s_admission_process_type
371 AND step_group_type <> 'TRACK';
372 -- Have to find the last admission_appl_number used.
373 CURSOR c_aa IS
374 SELECT NVL(MAX(admission_appl_number),0) + 1
375 FROM igs_ad_appl
376 WHERE person_id = p_person_id;
377
378 CURSOR c_sys_def_appl_type(cp_adm_cat igs_ad_appl_all.admission_cat%TYPE,
379 cp_s_adm_prc_typ igs_ad_appl_all.s_admission_process_type%TYPE
380 )
381 IS
382 SELECT admission_application_type
383 FROM igs_ad_ss_appl_typ
384 WHERE admission_cat = cp_adm_cat
385 AND S_admission_process_type = cp_s_adm_prc_typ
386 AND System_default = 'Y'
387 AND NVL(closed_ind, 'N') <> 'Y';
388
389 l_application_type igs_ad_appl_all.application_type%TYPE;
390 lv_rowid VARCHAR2(25);
391 l_org_id NUMBER(15);
392 BEGIN
393 -- Work out NOCOPY the new admission_appl_number.
394 OPEN c_aa;
395 FETCH c_aa
396 INTO v_adm_appl_number;
397 IF c_aa%NOTFOUND THEN
398 RAISE NO_DATA_FOUND;
399 END IF;
400 CLOSE c_aa;
401 --
402 -- Determine the admission process category steps.
403 --
404 FOR v_apcs_rec IN c_apcs (
405 p_admission_cat,
406 p_s_admission_process_type)
407 LOOP
408 IF v_apcs_rec.s_admission_step_type = 'UN-IGS_PE_TITLE' THEN
409 v_title_required_ind := 'N';
410 ELSIF v_apcs_rec.s_admission_step_type = 'UN-DOB' THEN
411 v_birth_dt_required_ind := 'N';
412 ELSIF v_apcs_rec.s_admission_step_type = 'APP-FEE' THEN
413 v_fees_required_ind := 'Y';
414 ELSIF v_apcs_rec.s_admission_step_type = 'CHKPENCUMB' THEN
415 v_person_encmb_chk_ind := 'Y';
416 ELSIF v_apcs_rec.s_admission_step_type = 'FEE-COND' THEN
417 v_cond_offer_fee_allowed_ind := 'Y';
418 END IF;
419 END LOOP;
420 -- Set fee status
421 IF v_fees_required_ind = 'Y' THEN
422 p_adm_fee_status := 'EXEMPT';
423 END IF;
424 --
425 -- Validate insert of the admission application record.
426 --
427 IF Igs_Ad_Val_Aa.admp_val_aa_insert (
428 p_person_id,
429 p_adm_cal_type,
430 p_adm_ci_sequence_number,
431 p_s_admission_process_type,
432 v_person_encmb_chk_ind,
433 p_appl_dt,
434 v_title_required_ind,
435 v_birth_dt_required_ind,
436 v_message_name,
437 v_return_type) = FALSE THEN
438 IF NVL(v_return_type, '-1') = cst_error THEN
439 p_message_name := v_message_name;
440 RETURN FALSE;
441 END IF;
442 END IF;
443 --
444 -- Validate the Academic Calendar.
445 --
446 IF Igs_Ad_Val_Aa.admp_val_aa_acad_cal (
447 p_acad_cal_type,
448 p_acad_ci_sequence_number,
449 v_message_name) = FALSE THEN
450 p_message_name := v_message_name;
451 RETURN FALSE;
452 END IF;
453 --
454 -- Validate the Admission Calendar.
455 --
456 IF Igs_Ad_Val_Aa.admp_val_aa_adm_cal (
457 p_adm_cal_type,
458 p_adm_ci_sequence_number,
459 p_acad_cal_type,
460 p_acad_ci_sequence_number,
461 p_admission_cat,
462 p_s_admission_process_type,
463 v_message_name) = FALSE THEN
464 p_message_name := v_message_name;
465 RETURN FALSE;
466 END IF;
467 --
468 -- Validate the Admission Category.
469 --
470 IF Igs_Ad_Val_Aa.admp_val_aa_adm_cat (
471 p_admission_cat,
472 v_message_name) = FALSE THEN
473 p_message_name := v_message_name;
474 RETURN FALSE;
475 END IF;
476 --
477 -- Validate the Application Date.
478 --
479 IF Igs_Ad_Val_Aa.admp_val_aa_appl_dt (
480 p_appl_dt,
481 v_message_name) = FALSE THEN
482 p_message_name := v_message_name;
483 RETURN FALSE;
484 END IF;
485 --
486 -- Validate the Admission Application Status.
487 --
488 IF Igs_Ad_Val_Aa.admp_val_aa_aas (
489 p_person_id,
490 v_adm_appl_number,
491 p_adm_appl_status,
492 v_message_name) = FALSE THEN
493 p_message_name := v_message_name;
494 RETURN FALSE;
495 END IF;
496 --
497 -- Validate the Admission Fee Status.
498 --
499 IF Igs_Ad_Val_Aa.admp_val_aa_afs (
500 p_person_id,
501 v_adm_appl_number,
502 p_adm_fee_status,
503 v_fees_required_ind,
504 v_cond_offer_fee_allowed_ind,
505 v_message_name) = FALSE THEN
506 p_message_name := v_message_name;
507 RETURN FALSE;
508 END IF;
509 --
510 -- Validate the TAC Application Indicator.
511 --
512 IF Igs_Ad_Val_Aa.admp_val_aa_tac_appl (
513 p_person_id,
514 p_tac_appl_ind,
515 p_appl_dt,
516 p_s_admission_process_type,
517 v_message_name,
518 v_return_type) = FALSE THEN
519 IF NVL(v_return_type, '-1') = cst_error THEN
520 p_message_name := v_message_name;
521 RETURN FALSE;
522 END IF;
523 END IF;
524 --Now insert the new record
525 --Populate the org id
526 l_org_id := igs_ge_gen_003.get_org_id;
527
528 OPEN c_sys_def_appl_type(p_admission_cat,p_s_admission_process_type);
529 FETCH c_sys_def_appl_type INTO l_application_type;
530 CLOSE c_sys_def_appl_type;
531
532 --Validate the Application Type and fail the validation if Application Type passed is NULL. Bug: 2599457
533 IF l_application_type IS NULL THEN
534 p_message_name:= 'IGS_AD_APPL_TYPE_NULL';
535 RETURN FALSE;
536 END IF;
537
538 Igs_Ad_Appl_Pkg.Insert_Row (
539 X_Mode => 'R',
540 X_RowId => lv_rowid,
541 X_Person_Id => p_Person_Id,
542 X_Admission_Appl_Number => v_adm_appl_number,
543 X_Appl_Dt => p_Appl_Dt,
544 X_Acad_Cal_Type => p_Acad_Cal_Type,
545 X_Acad_Ci_Sequence_Number => p_Acad_Ci_Sequence_Number,
546 X_Adm_Cal_Type => p_Adm_Cal_Type,
547 X_Adm_Ci_Sequence_Number => p_Adm_Ci_Sequence_Number,
548 X_Admission_Cat => p_Admission_Cat,
549 X_S_Admission_Process_Type => p_S_Admission_Process_Type,
550 X_Adm_Appl_Status => p_Adm_Appl_Status,
551 X_Adm_Fee_Status => p_Adm_Fee_Status,
552 X_Tac_Appl_Ind => p_Tac_Appl_Ind,
553 X_Org_Id => l_org_id,
554 X_Spcl_Grp_1 => p_spcl_grp_1, -- bug# 1964478, parameter added
555 X_Spcl_Grp_2 => p_spcl_grp_2, -- bug# 1964478, parameter added
556 X_Common_App => p_common_app, -- bug# 1964478, parameter added
557 x_application_type => l_application_type, -- Added as part of 2599457
558 x_choice_number => p_choice_number,
559 x_routeb_pref => p_routeb_pref,
560 x_alt_appl_id => p_alt_appl_id -- Added for bug 2664410
561 );
562
563 p_adm_appl_number := v_adm_appl_number;
564 p_message_name := NULL;
565 RETURN TRUE;
566 END;
567 EXCEPTION
568 WHEN OTHERS THEN
569 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
570 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_adm_appl');
571 IGS_GE_MSG_STACK.ADD;
572 App_Exception.Raise_Exception;
573 END admp_ins_adm_appl;
574 --
575 -- Inserts TAC details to form an admission COURSE application instance
576 FUNCTION admp_ins_tac_acai(
577 p_person_id IN NUMBER ,
578 p_admission_appl_number IN NUMBER ,
579 p_acad_cal_type IN VARCHAR2 ,
580 p_acad_ci_sequence_number IN NUMBER ,
581 p_adm_cal_type IN VARCHAR2 ,
582 p_adm_ci_sequence_number IN NUMBER ,
583 p_admission_cat IN VARCHAR2 ,
584 p_appl_dt IN DATE ,
585 p_adm_fee_status IN VARCHAR2 ,
586 p_preference_number IN NUMBER ,
587 p_offer_dt IN DATE ,
588 p_offer_response_dt IN DATE ,
589 p_course_cd IN VARCHAR2 ,
590 p_crv_version_number IN NUMBER ,
591 p_location_cd IN VARCHAR2 ,
592 p_attendance_mode IN VARCHAR2 ,
593 p_attendance_type IN VARCHAR2 ,
594 p_unit_set_cd IN VARCHAR2 ,
595 p_us_version_number IN NUMBER ,
596 p_fee_cat IN VARCHAR2 ,
597 p_hecs_payment_option IN VARCHAR2 ,
598 p_correspondence_cat IN VARCHAR2 ,
599 p_enrolment_cat IN VARCHAR2 ,
600 p_insert_outcome_ind IN VARCHAR2 ,
601 p_pre_enrol_ind IN VARCHAR2 ,
602 p_return_type OUT NOCOPY VARCHAR2 ,
603 p_message_name OUT NOCOPY VARCHAR2 )
604 RETURN BOOLEAN IS
605 /******************************************************************
606 Created By:
607 Date Created By:
608 Purpose:
609 Known limitations,enhancements,remarks:
610 Change History
611 Who When What
612 knag 28-Oct-2002 Called func igs_ad_gen_003.get_core_or_optional_unit for bug 2647482
613 ******************************************************************/
614
615 BEGIN -- admp_ins_tac_acai
616 -- This procedure inserts a new IGS_AD_PS_APPL_INST record for the TAC
617 -- offer load process.
618 DECLARE
619 e_resource_busy EXCEPTION;
620 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
621 p_dummy VARCHAR2(10);
622 v_check CHAR;
623 v_s_admission_process_type IGS_AD_PRCS_CAT.s_admission_process_type%TYPE;
624 v_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE;
625 v_enrolment_cat IGS_EN_ENROLMENT_CAT.enrolment_cat%TYPE;
626 v_correspondence_cat IGS_CO_CAT.correspondence_cat%TYPE;
627 v_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE;
628 v_description IGS_FI_FEE_CAT.description%TYPE;
629 v_message_name VARCHAR2(30);
630 v_hecs_message_name VARCHAR2(30);
631 v_pre_enrol_message_name VARCHAR2(30);
632 v_outcome_message_name VARCHAR2(30);
633 v_apcs_pref_limit_ind VARCHAR2(127);
634 v_apcs_app_fee_ind VARCHAR2(127);
635 v_apcs_late_app_ind VARCHAR2(127);
636 v_apcs_late_fee_ind VARCHAR2(127);
637 v_apcs_chkpencumb_ind VARCHAR2(127);
638 v_apcs_fee_assess_ind VARCHAR2(127);
639 v_apcs_corcategry_ind VARCHAR2(127);
640 v_apcs_enrcategry_ind VARCHAR2(127);
641 v_apcs_chkcencumb_ind VARCHAR2(127);
642 v_apcs_unit_set_ind VARCHAR2(127);
643 v_apcs_un_crs_us_ind VARCHAR2(127);
644 v_apcs_chkuencumb_ind VARCHAR2(127);
645 v_apcs_unit_restr_ind VARCHAR2(127);
646 v_apcs_unit_restriction_num VARCHAR2(127);
647 v_apcs_un_dob_ind VARCHAR2(127);
648 v_apcs_un_title_ind VARCHAR2(127);
649 v_apcs_asses_cond_ind VARCHAR2(127);
650 v_apcs_fee_cond_ind VARCHAR2(127);
651 v_apcs_doc_cond_ind VARCHAR2(127);
652 v_apcs_multi_off_ind VARCHAR2(127);
653 v_apcs_multi_off_restn_num VARCHAR2(127);
654 v_apcs_set_otcome_ind VARCHAR2(127);
655 v_apcs_override_o_ind VARCHAR2(127);
656 v_apcs_defer_ind VARCHAR2(127);
657 v_apcs_ack_app_ind VARCHAR2(127);
658 v_apcs_outcome_lt_ind VARCHAR2(127);
659 v_apcs_pre_enrol_ind VARCHAR2(127);
660 v_hecs_pmnt_option_found BOOLEAN DEFAULT TRUE;
661 v_offer_letter_ins BOOLEAN DEFAULT TRUE;
662 v_pre_enr_done BOOLEAN DEFAULT TRUE;
663 v_adm_doc_status VARCHAR2(127);
664 v_adm_entry_qual_status VARCHAR2(127);
665 v_adm_pending_outcome_status VARCHAR2(127);
666 v_adm_cndtnl_offer_status VARCHAR2(127);
667 v_adm_offer_dfrmnt_status VARCHAR2(127);
668 v_late_adm_fee_status VARCHAR2(127);
669 v_acai_sequence_number IGS_CA_INST.sequence_number%TYPE;
670 v_offer_adm_outcome_status VARCHAR2(127);
671 v_adm_offer_resp_status VARCHAR2(127);
672 v_new_adm_offer_resp_status VARCHAR2(127);
673 v_offer_response_dt DATE;
674 v_return_type VARCHAR2(1);
675 cst_error CONSTANT VARCHAR2(1):= 'E';
676 CURSOR c_nxt_acai_seq_num IS
677 SELECT NVL(MAX(sequence_number), 0) + 1
678 FROM IGS_AD_PS_APPL_INST
679 WHERE
680 person_id = p_person_id AND
681 admission_appl_number = p_admission_appl_number AND
682 nominated_course_cd = p_course_cd;
683 CURSOR c_upd_acai IS
684 SELECT ROWID, APAI.*
685 FROM IGS_AD_PS_APPL_INST APAI
686 WHERE
687 person_id = p_person_id AND
688 admission_appl_number = p_admission_appl_number AND
689 nominated_course_cd = p_course_cd AND
690 sequence_number = v_acai_sequence_number
691 FOR UPDATE OF person_id NOWAIT;
692 Rec_IGS_AD_PS_APPL_Inst c_upd_acai%ROWTYPE;
693 LV_ROWID VARCHAR2(25);
694 l_org_id NUMBER(15);
695 BEGIN
696 /*
697 || Change History :
698 || Who When What
699 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
700 || rrengara 26-JUL-2001 Bug Enh No: 1891835 : For the DLD Process Student Response to Offer. Added two columns in TBH
701 || (reverse chronological order - newest change first)
702 */
703
704 p_message_name := NULL;
705 v_s_admission_process_type := 'IGS_PS_COURSE';
706 --------------------------------------------------
707 -- Get the admission process category steps.
708 --------------------------------------------------
709 Igs_Ad_Gen_004.ADMP_GET_APCS_VAL(
710 p_admission_cat,
711 v_s_admission_process_type,
712 v_apcs_pref_limit_ind, -- OUT NOCOPY
713 v_apcs_app_fee_ind, -- OUT NOCOPY
714 v_apcs_late_app_ind, -- OUT NOCOPY
715 v_apcs_late_fee_ind, -- OUT NOCOPY
716 v_apcs_chkpencumb_ind, -- OUT NOCOPY
717 v_apcs_fee_assess_ind, -- OUT NOCOPY
718 v_apcs_corcategry_ind, -- OUT NOCOPY
719 v_apcs_enrcategry_ind, -- OUT NOCOPY
720 v_apcs_chkcencumb_ind, -- OUT NOCOPY
721 v_apcs_unit_set_ind, -- OUT NOCOPY
722 v_apcs_un_crs_us_ind, -- OUT NOCOPY
723 v_apcs_chkuencumb_ind, -- OUT NOCOPY
724 v_apcs_unit_restr_ind, -- OUT NOCOPY
725 v_apcs_unit_restriction_num, -- OUT NOCOPY
726 v_apcs_un_dob_ind, -- OUT NOCOPY
727 v_apcs_un_title_ind, -- OUT NOCOPY
728 v_apcs_asses_cond_ind, -- OUT NOCOPY
729 v_apcs_fee_cond_ind, -- OUT NOCOPY
730 v_apcs_doc_cond_ind, -- OUT NOCOPY
731 v_apcs_multi_off_ind, -- OUT NOCOPY
732 v_apcs_multi_off_restn_num, -- OUT NOCOPY
733 v_apcs_set_otcome_ind, -- OUT NOCOPY
734 v_apcs_override_o_ind, -- OUT NOCOPY
735 v_apcs_defer_ind, -- OUT NOCOPY
736 v_apcs_ack_app_ind, -- OUT NOCOPY
737 v_apcs_outcome_lt_ind, -- OUT NOCOPY
738 v_apcs_pre_enrol_ind); -- OUT NOCOPY
739 --------------------------------
740 -- Set fee category
741 --------------------------------
742 IF p_fee_cat IS NULL THEN
743 -- Derive the fee category
744 v_fee_cat := Igs_Ad_Gen_005.ADMP_GET_DFLT_FCM(
745 p_admission_cat,
746 v_description);
747 ELSIF Igs_Ad_Val_Acai.admp_val_acai_fc(
748 p_admission_cat,
749 p_fee_cat,
750 v_message_name) = FALSE THEN
751 v_fee_cat := NULL;
752 ELSE
753 v_fee_cat := p_fee_cat;
754 END IF;
755 --------------------------------
756 -- Set enrolment category
757 --------------------------------
758 IF p_enrolment_cat IS NULL THEN
759 -- Derive the enrolment category
760 v_enrolment_cat := Igs_Ad_Gen_005.ADMP_GET_DFLT_ECM(
761 p_admission_cat,
762 v_description);
763 ELSIF Igs_Ad_Val_Acai.admp_val_acai_ec(
764 p_admission_cat,
765 p_enrolment_cat,
766 v_message_name) = FALSE THEN
767 v_enrolment_cat := NULL;
768 ELSE
769 v_enrolment_cat := p_enrolment_cat;
770 END IF;
771 --------------------------------
772 -- Set correspondence category
773 --------------------------------
774 IF p_correspondence_cat IS NULL THEN
775 -- Derive the correspondence category
776 v_correspondence_cat := Igs_Ad_Gen_005.ADMP_GET_DFLT_CCM(
777 p_admission_cat,
778 v_description);
779 ELSIF Igs_Ad_Val_Acai.admp_val_acai_cc(
780 p_admission_cat,
781 p_correspondence_cat,
782 v_message_name) = FALSE THEN
783 v_correspondence_cat := NULL;
784 ELSE
785 v_correspondence_cat := p_correspondence_cat;
786 END IF;
787 --------------------------------
788 -- Validate HECS payment option
789 --------------------------------
790 IF p_hecs_payment_option IS NOT NULL THEN
791 -- Validate HECS payment option
792 IF Igs_Ad_Val_Acai.admp_val_acai_hpo(
793 p_admission_cat,
794 p_hecs_payment_option,
795 v_message_name) = FALSE THEN
796 -- Set variable to indicate HECS payment option could not be determined
797 v_hecs_pmnt_option_found := FALSE;
798 v_hecs_message_name := v_message_name;
799 v_hecs_payment_option := NULL;
800 ELSE
801 v_hecs_payment_option := p_hecs_payment_option;
802 END IF;
803 ELSE
804 v_hecs_payment_option := NULL;
805 END IF;
806 --------------------------------------------------------------------------
807 -- Set admission COURSE application instance statuses for PENDING outcome
808 --------------------------------------------------------------------------
809 v_adm_doc_status := Igs_Ad_Gen_009.ADMP_GET_SYS_ADS('SATISFIED');
810 v_adm_entry_qual_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AEQS('QUALIFIED');
811 v_adm_pending_outcome_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AOS('PENDING');
812 v_adm_cndtnl_offer_status := Igs_Ad_Gen_009.ADMP_GET_SYS_ACOS('NOT-APPLIC');
813 v_adm_offer_resp_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AORS('NOT-APPLIC');
814 v_adm_offer_dfrmnt_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AODS('NOT-APPLIC');
815 IF v_apcs_late_fee_ind = 'Y' THEN
816 v_late_adm_fee_status := IGS_AD_GEN_009.ADMP_GET_SYS_AFS('EXEMPT');
817 ELSE
818 v_late_adm_fee_status := IGS_AD_GEN_009.ADMP_GET_SYS_AFS('NOT-APPLIC');
819 END IF;
820 --------------------------------
821 -- Get the next sequence number
822 --------------------------------
823 OPEN c_nxt_acai_seq_num;
824 FETCH c_nxt_acai_seq_num INTO v_acai_sequence_number;
825 CLOSE c_nxt_acai_seq_num;
826 ---------------------------------------------------
827 -- Validate the admission COURSE offering IGS_PS_UNIT set
828 ---------------------------------------------------
829 IF Igs_Ad_Val_Acai.admp_val_acai_us (
830 p_unit_set_cd,
831 p_us_version_number,
832 p_course_cd,
833 p_crv_version_number,
834 p_acad_cal_type,
835 p_location_cd,
836 p_attendance_mode,
837 p_attendance_type,
838 p_admission_cat,
839 'N',
840 v_apcs_unit_set_ind,
841 v_message_name,
842 v_return_type) = FALSE THEN
843 IF NVL(v_return_type, '-1') = cst_error THEN
844 p_message_name := v_message_name;
845 p_return_type := v_return_type;
846 RETURN FALSE;
847 END IF;
848 END IF;
849 --------------------------------------------------------------------------
850 -- Insert an admission COURSE application instance with PENDING outcome
851 --------------------------------------------------------------------------
852 l_org_id := igs_ge_gen_003.get_org_id;
853 Igs_Ad_Ps_Appl_Inst_Pkg.Insert_Row (
854 X_Mode => 'R',
855 X_RowId => lv_rowid,
856 X_Person_Id => p_Person_Id,
857 X_Admission_Appl_Number => p_Admission_Appl_Number,
858 X_Nominated_Course_Cd => p_course_cd,
859 X_Sequence_Number => v_acai_sequence_number,
860 X_PREDICTED_GPA => NULL,
861 X_ACADEMIC_INDEX => NULL,
862 X_Adm_Cal_Type => NULL,
863 X_APP_FILE_LOCATION => NULL,
864 X_Adm_Ci_Sequence_Number => NULL,
865 X_Course_Cd => p_Course_Cd,
866 X_APP_SOURCE_ID => NULL,
867 X_Crv_Version_Number => p_Crv_Version_Number,
868 X_WAITLIST_RANK => NULL,
869 X_Location_Cd => p_Location_Cd,
870 X_ATTENT_OTHER_INST_CD => NULL,
871 X_Attendance_Mode => p_Attendance_Mode,
872 X_EDU_GOAL_PRIOR_ENROLL_ID => NULL,
873 X_Attendance_Type => p_Attendance_Type,
874 X_DECISION_MAKE_ID => NULL,
875 X_Unit_Set_Cd => p_Unit_Set_Cd,
876 X_DECISION_DATE => NULL,
877 X_ATTRIBUTE_CATEGORY => NULL,
878 X_ATTRIBUTE1 => NULL,
879 X_ATTRIBUTE2 => NULL,
880 X_ATTRIBUTE3 => NULL,
881 X_ATTRIBUTE4 => NULL,
882 X_ATTRIBUTE5 => NULL,
883 X_ATTRIBUTE6 => NULL,
884 X_ATTRIBUTE7 => NULL,
885 X_ATTRIBUTE8 => NULL,
886 X_ATTRIBUTE9 => NULL,
887 X_ATTRIBUTE10 => NULL,
888 X_ATTRIBUTE11 => NULL,
889 X_ATTRIBUTE12 => NULL,
890 X_ATTRIBUTE13 => NULL,
891 X_ATTRIBUTE14 => NULL,
892 X_ATTRIBUTE15 => NULL,
893 X_ATTRIBUTE16 => NULL,
894 X_ATTRIBUTE17 => NULL,
895 X_ATTRIBUTE18 => NULL,
896 X_ATTRIBUTE19 => NULL,
897 X_ATTRIBUTE20 => NULL,
898 X_DECISION_REASON_ID => NULL,
899 X_Us_Version_Number => p_Us_Version_Number,
900 X_DECISION_NOTES => NULL,
901 X_PENDING_REASON_ID => NULL,
902 X_Preference_Number => p_Preference_Number,
903 X_Adm_Doc_Status => v_Adm_Doc_Status,
904 X_Adm_Entry_Qual_Status => v_Adm_Entry_Qual_Status,
905 X_DEFICIENCY_IN_PREP => NULL,
906 X_Late_Adm_Fee_Status => v_Late_Adm_Fee_Status,
907 X_SPL_CONSIDER_COMMENTS => NULL,
908 X_APPLY_FOR_FINAID => NULL,
909 X_FINAID_APPLY_DATE => NULL,
910 X_Adm_Outcome_Status => v_adm_pending_outcome_status,
911 X_ADM_OTCM_STAT_AUTH_PER_ID => NULL,
912 X_Adm_Outcome_Status_Auth_Dt => NULL,
913 X_Adm_Outcome_Status_Reason => NULL,
914 X_Offer_Dt => NULL,
915 X_Offer_Response_Dt => NULL,
916 X_Prpsd_Commencement_Dt => NULL,
917 X_Adm_Cndtnl_Offer_Status => v_adm_cndtnl_offer_status,
918 X_Cndtnl_Offer_Satisfied_Dt => NULL,
919 X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
920 X_Adm_Offer_Resp_Status => v_adm_offer_resp_status,
921 X_Actual_Response_Dt => NULL,
922 X_Adm_Offer_Dfrmnt_Status => v_adm_offer_dfrmnt_status,
923 X_Deferred_Adm_Cal_Type => NULL,
924 X_Deferred_Adm_Ci_Sequence_Num => NULL,
925 X_Deferred_Tracking_Id => NULL,
926 X_Ass_Rank => NULL,
927 X_Secondary_Ass_Rank => NULL,
928 X_INTR_ACCEPT_ADVICE_NUM => NULL,
929 X_Ass_Tracking_Id => NULL,
930 X_Fee_Cat => v_Fee_Cat,
931 X_Hecs_Payment_Option => v_Hecs_Payment_Option,
932 X_Expected_Completion_Yr => NULL,
933 X_Expected_Completion_Perd => NULL,
934 X_Correspondence_Cat => v_Correspondence_Cat,
935 X_Enrolment_Cat => v_Enrolment_Cat,
936 X_Funding_Source => NULL,
937 X_Applicant_Acptnce_Cndtn => NULL,
938 X_Cndtnl_Offer_Cndtn => NULL,
939 X_ss_application_id => NULL,
940 X_ss_pwd => NULL,
941 X_AUTHORIZED_DT => NULL, -- BUG ENH NO : 1891835 Added this column in table
942 X_AUTHORIZING_PERS_ID => NULL, -- BUG ENH NO : 1891835 Added this column in table
943 X_IDX_CALC_DATE => NULL,
944 X_Org_id => l_org_id,
945 x_entry_status => NULL, -- Enh Bug#1964478 added three parameters
946 x_entry_level => NULL, -- Enh Bug#1964478 added three parameters
947 x_sch_apl_to_id => NULL, -- Enh Bug#1964478 added three parameters
948 X_FUT_ACAD_CAL_TYPE => NULL , -- Bug # 2217104
949 X_FUT_ACAD_CI_SEQUENCE_NUMBER => NULL ,-- Bug # 2217104
950 X_FUT_ADM_CAL_TYPE => NULL , -- Bug # 2217104
951 X_FUT_ADM_CI_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
952 X_PREV_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
953 X_PREV_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
954 X_FUT_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
955 X_FUT_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
956 X_DEF_ACAD_CAL_TYPE => NULL, -- Bug 2395510
957 X_DEF_ACAD_CI_SEQUENCE_NUM => NULL,-- Bug 2395510
958 X_DEF_PREV_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
959 X_DEF_PREV_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
960 X_DEF_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
961 X_DEF_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
962 X_ATTRIBUTE21 => NULL,
963 X_ATTRIBUTE22 => NULL,
964 X_ATTRIBUTE23 => NULL,
965 X_ATTRIBUTE24 => NULL,
966 X_ATTRIBUTE25 => NULL,
967 X_ATTRIBUTE26 => NULL,
968 X_ATTRIBUTE27 => NULL,
969 X_ATTRIBUTE28 => NULL,
970 X_ATTRIBUTE29 => NULL,
971 X_ATTRIBUTE30 => NULL,
972 X_ATTRIBUTE31 => NULL,
973 X_ATTRIBUTE32 => NULL,
974 X_ATTRIBUTE33 => NULL,
975 X_ATTRIBUTE34 => NULL,
976 X_ATTRIBUTE35 => NULL,
977 X_ATTRIBUTE36 => NULL,
978 X_ATTRIBUTE37 => NULL,
979 X_ATTRIBUTE38 => NULL,
980 X_ATTRIBUTE39 => NULL,
981 X_ATTRIBUTE40 => NULL
982 );
983
984 ------------------------------------------------------------------------------
985 -- Set admission COURSE application instance statuses for OFFER outcome
986 ------------------------------------------------------------------------------
987 v_offer_adm_outcome_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AOS('OFFER');
988 v_new_adm_offer_resp_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AORS('PENDING');
989 ------------------------------------------------------------------------------
990 -- Validate that admission COURSE application instance is valid for an offer
991 ------------------------------------------------------------------------------
992 IF Igs_Ad_Val_Acai_Status.admp_val_acai_aos(
993 p_person_id,
994 p_admission_appl_number,
995 p_course_cd, -- (nominated COURSE code is the same as COURSE code)
996 v_acai_sequence_number,
997 p_course_cd,
998 p_crv_version_number,
999 p_location_cd,
1000 p_attendance_mode,
1001 p_attendance_type,
1002 p_unit_set_cd,
1003 p_us_version_number,
1004 p_acad_cal_type,
1005 p_acad_ci_sequence_number,
1006 p_adm_cal_type,
1007 p_adm_ci_sequence_number,
1008 p_admission_cat,
1009 v_s_admission_process_type,
1010 p_appl_dt,
1011 v_fee_cat,
1012 v_correspondence_cat,
1013 v_enrolment_cat,
1014 v_offer_adm_outcome_status,
1015 v_adm_pending_outcome_status,
1016 v_adm_doc_status,
1017 p_adm_fee_status,
1018 v_late_adm_fee_status,
1019 v_adm_cndtnl_offer_status,
1020 v_adm_entry_qual_status,
1021 v_new_adm_offer_resp_status,
1022 v_adm_offer_resp_status,
1023 NULL, -- (outcome override authorisation date)
1024 v_apcs_set_otcome_ind,
1025 v_apcs_asses_cond_ind,
1026 v_apcs_fee_cond_ind,
1027 v_apcs_doc_cond_ind,
1028 v_apcs_late_app_ind,
1029 v_apcs_app_fee_ind,
1030 v_apcs_multi_off_ind,
1031 v_apcs_multi_off_restn_num,
1032 v_apcs_pref_limit_ind,
1033 v_apcs_unit_set_ind,
1034 v_apcs_chkpencumb_ind,
1035 v_apcs_chkcencumb_ind,
1036 'FORM', -- (want same validation as in form)
1037 v_message_name) = FALSE THEN
1038 p_message_name := v_message_name;
1039 p_return_type := 'W';
1040 RETURN FALSE;
1041 END IF;
1042 -------------------------------
1043 -- Validate the offer date
1044 -------------------------------
1045 IF Igs_Ad_Val_Acai.admp_val_offer_dt(
1046 p_offer_dt,
1047 v_offer_adm_outcome_status,
1048 p_adm_cal_type,
1049 p_adm_ci_sequence_number,
1050 v_message_name) = FALSE THEN
1051 p_message_name := v_message_name;
1052 p_return_type := 'W';
1053 RETURN FALSE;
1054 END IF;
1055 -------------------------------
1056 -- Set the offer response date
1057 -------------------------------
1058 IF p_offer_response_dt IS NULL THEN
1059 -- Calculate the offer response date
1060 v_offer_response_dt := Igs_Ad_Gen_007.ADMP_GET_RESP_DT(
1061 p_course_cd,
1062 p_crv_version_number,
1063 p_acad_cal_type,
1064 p_location_cd,
1065 p_attendance_mode,
1066 p_attendance_type,
1067 p_admission_cat,
1068 v_s_admission_process_type,
1069 p_adm_cal_type,
1070 p_adm_ci_sequence_number,
1071 p_offer_dt);
1072 IF v_offer_response_dt IS NULL THEN
1073 p_message_name := 'IGS_AD_INVALID_OFFER_RESPDATE';
1074 p_return_type := 'W';
1075 RETURN FALSE;
1076 END IF;
1077 ELSE
1078 v_offer_response_dt := p_offer_response_dt;
1079 END IF;
1080 ---------------------------------------------------
1081 -- Validate the admission COURSE offering IGS_PS_UNIT set
1082 ---------------------------------------------------
1083 IF Igs_Ad_Val_Acai.admp_val_acai_us (
1084 p_unit_set_cd,
1085 p_us_version_number,
1086 p_course_cd,
1087 p_crv_version_number,
1088 p_acad_cal_type,
1089 p_location_cd,
1090 p_attendance_mode,
1091 p_attendance_type,
1092 p_admission_cat,
1093 'Y', --- offer ind
1094 v_apcs_unit_set_ind,
1095 v_message_name,
1096 v_return_type) = FALSE THEN
1097 IF NVL(v_return_type, '-1') = cst_error THEN
1098 p_message_name := v_message_name;
1099 p_return_type := v_return_type;
1100 RETURN FALSE;
1101 END IF;
1102 END IF;
1103 ------------------------------------------------------------
1104 -- Update admission COURSE application instance with OFFER
1105 ------------------------------------------------------------
1106 BEGIN
1107 OPEN c_upd_acai;
1108 -- FETCH c_upd_acai INTO v_check;
1109 FETCH c_upd_acai INTO Rec_IGS_AD_PS_APPL_Inst;
1110
1111 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
1112 X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
1113 X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
1114 X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
1115 X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
1116 X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
1117 X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
1118 X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
1119 X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
1120 X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
1121 X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
1122 X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
1123 X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
1124 X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
1125 X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
1126 X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
1127 X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
1128 X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
1129 X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
1130 X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
1131 X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
1132 X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
1133 X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
1134 X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
1135 X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
1136 X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
1137 X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
1138 X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
1139 X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
1140 X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
1141 X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
1142 X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
1143 X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
1144 X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
1145 X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
1146 X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
1147 X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
1148 X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
1149 X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
1150 X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
1151 X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
1152 X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
1153 X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
1154 X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
1155 X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
1156 X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
1157 X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
1158 X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
1159 X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
1160 X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
1161 X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
1162 X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
1163 X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
1164 X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
1165 X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
1166 X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
1167 X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
1168 X_ADM_OUTCOME_STATUS => v_offer_adm_outcome_status,
1169 X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
1170 X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
1171 X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
1172 X_OFFER_DT => p_offer_dt,
1173 X_OFFER_RESPONSE_DT => v_offer_response_dt,
1174 X_PRPSD_COMMENCEMENT_DT => Rec_IGS_AD_PS_APPL_Inst.Prpsd_Commencement_Dt,
1175 X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
1176 X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
1177 X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
1178 X_ADM_OFFER_RESP_STATUS => v_new_adm_offer_resp_status,
1179 X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
1180 X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
1181 X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
1182 X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
1183 X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
1184 X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
1185 X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
1186 X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
1187 X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
1188 X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
1189 X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
1190 X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
1191 X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
1192 X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
1193 X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
1194 X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
1195 X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
1196 X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
1197 X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
1198 X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
1199 X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
1200 X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
1201 X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
1202 X_MODE => 'R',
1203 X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Enh Bug#1964478 added three parameters
1204 X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Enh Bug#1964478 added three parameters
1205 X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Enh Bug#1964478 added three parameters
1206 X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
1207 X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
1208 X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
1209 X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
1210 X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
1211 X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
1212 X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
1213 X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
1214 X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
1215 X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
1216 X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
1217 X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
1218 X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
1219 X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
1220 X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
1221 X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
1222 X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
1223 X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
1224 X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
1225 X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
1226 X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
1227 X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
1228 X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
1229 X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
1230 X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
1231 X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
1232 X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
1233 X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
1234 X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
1235 X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
1236 X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
1237 X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
1238 X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
1239 X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
1240 X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
1241 x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
1242 x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
1243
1244 );
1245
1246 CLOSE c_upd_acai;
1247 EXCEPTION
1248 -- check for locking on update, although this should never occur
1249 WHEN e_resource_busy THEN
1250 p_message_name := 'IGS_AD_APPL_NOTSET_LOCKING';
1251 p_return_type := 'W';
1252 RETURN FALSE;
1253 END;
1254 --------------------------
1255 -- Insert outcome letter
1256 --------------------------
1257 IF p_insert_outcome_ind = 'Y' AND
1258 v_apcs_outcome_lt_ind = 'Y' THEN
1259 -- Insert offer letter
1260 IF Igs_Ad_Gen_010.ADMP_INS_AAL(
1261 p_person_id,
1262 p_admission_appl_number,
1263 'OUTCOME-LT',
1264 p_admission_cat,
1265 v_s_admission_process_type,
1266 v_message_name) = FALSE THEN
1267 -- Set variable to indicate that the letter could not be inserted
1268 v_offer_letter_ins := FALSE;
1269 IF v_message_name = 'IGS_AD_UNISSUED_LETTER_EXISTS' THEN
1270 v_outcome_message_name := 'IGS_PR_OUTCOMELETTER_NOTCREAT';
1271 ELSE
1272 v_outcome_message_name := v_message_name;
1273 END IF;
1274 END IF;
1275 END IF;
1276 ------------------------------
1277 -- Pre-enrol admission COURSE
1278 ------------------------------
1279 IF p_pre_enrol_ind = 'Y' AND
1280 v_apcs_pre_enrol_ind = 'Y' THEN
1281 -- Pre-enrol admission COURSE application instance
1282 IF igs_ad_upd_initialise.perform_pre_enrol(
1283 p_person_id,
1284 p_admission_appl_number,
1285 p_course_cd,
1286 v_acai_sequence_number,
1287 'N', -- Confirm course indicator.
1288 'N', -- Perform eligibility check indicator.
1289 v_message_name) = FALSE THEN
1290 -- Set variable to indicate pre-enrolment could not be done
1291 v_pre_enr_done := FALSE;
1292 v_pre_enrol_message_name := v_message_name;
1293 ELSE
1294 v_pre_enrol_message_name := v_message_name;
1295 END IF;
1296 END IF;
1297
1298 IF v_hecs_pmnt_option_found = FALSE THEN
1299 IF v_offer_letter_ins = FALSE THEN
1300 IF v_pre_enr_done = FALSE THEN
1301 p_message_name := 'IGS_AD_HECS_PYMT_PREENRL';
1302 ELSE
1303 p_message_name := 'IGS_AD_HECS_PYMT_CORTYPE';
1304 END IF;
1305 ELSE
1306 IF v_pre_enr_done = FALSE THEN
1307 p_message_name := 'IGS_AD_HECS_PYMT_PRE_ENRL';
1308 ELSE
1309 -- Outcome and pre-enrolment were successful
1310 p_message_name := v_hecs_message_name;
1311 END IF;
1312 END IF;
1313 p_return_type := 'W';
1314 RETURN FALSE;
1315 END IF;
1316 IF v_offer_letter_ins = FALSE THEN
1317 IF v_pre_enr_done = FALSE THEN
1318 p_message_name := 'IGS_AD_OFRLETTER_CORTYPE_ENR';
1319 ELSE
1320 p_message_name := v_outcome_message_name;
1321 END IF;
1322 p_return_type := 'W';
1323 RETURN FALSE;
1324 END IF;
1325 IF v_pre_enrol_message_name IS NOT NULL AND
1326 v_pre_enrol_message_name > 0 THEN
1327 p_message_name := v_pre_enrol_message_name;
1328 p_return_type := 'W';
1329 RETURN FALSE;
1330 END IF;
1331 p_message_name := NULL;
1332 RETURN TRUE;
1333 END;
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1337 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_tac_acai');
1338 IGS_GE_MSG_STACK.ADD;
1339 App_Exception.Raise_Exception;
1340 END admp_ins_tac_acai;
1341 --
1342 -- Inserts TAC details to form an admission COURSE
1343 FUNCTION admp_ins_tac_course(
1344 p_acad_cal_type IN VARCHAR2 ,
1345 p_acad_ci_sequence_number IN NUMBER ,
1346 p_adm_cal_type IN VARCHAR2 ,
1347 p_adm_ci_sequence_number IN NUMBER ,
1348 p_admission_cat IN VARCHAR2 ,
1349 p_fee_cat IN VARCHAR2 ,
1350 p_enrolment_cat IN VARCHAR2 ,
1351 p_correspondence_cat IN VARCHAR2 ,
1352 p_person_id IN NUMBER ,
1353 p_tac_course_cd IN VARCHAR2 ,
1354 p_preference_number IN NUMBER ,
1355 p_appl_dt IN DATE ,
1356 p_offer_dt IN DATE ,
1357 p_basis_for_admission_type IN VARCHAR2 ,
1358 p_admission_cd IN VARCHAR2 ,
1359 p_fee_paying_appl_ind IN VARCHAR2 ,
1360 p_hecs_payment_option IN VARCHAR2 ,
1361 p_insert_outcome_letter_ind IN VARCHAR2,
1362 p_pre_enrol_ind IN VARCHAR2 ,
1363 p_course_cd OUT NOCOPY VARCHAR2 ,
1364 p_tac_course_match_ind OUT NOCOPY VARCHAR2 ,
1365 p_return_type OUT NOCOPY VARCHAR2 ,
1366 p_message_name OUT NOCOPY VARCHAR2 )
1367 RETURN BOOLEAN IS
1368 BEGIN -- admp_ins_tac_course
1369 -- This process insert an admission COURSE application from a
1370 -- TAC offer load process.
1371 DECLARE
1372 e_resource_busy EXCEPTION;
1373 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
1374 v_message_name VARCHAR2(30);
1375 v_return_type VARCHAR2(127);
1376 v_admission_cat IGS_AD_CAT.admission_cat%TYPE DEFAULT NULL;
1377 v_course_cd IGS_PS_ENT_PT_REF_CD.course_cd%TYPE;
1378 v_version_number IGS_PS_ENT_PT_REF_CD.version_number%TYPE;
1379 v_cal_type IGS_PS_ENT_PT_REF_CD.cal_type%TYPE;
1380 v_location_cd IGS_PS_ENT_PT_REF_CD.location_cd%TYPE;
1381 v_attendance_mode IGS_PS_ENT_PT_REF_CD.attendance_mode%TYPE;
1382 v_attendance_type IGS_PS_ENT_PT_REF_CD.attendance_type%TYPE;
1383 v_unit_set_cd VARCHAR2(255);
1384 v_us_version_number NUMBER(6);
1385 v_coo_id IGS_PS_ENT_PT_REF_CD.coo_id%TYPE;
1386 v_ref_cd_type IGS_GE_REF_CD_TYPE.reference_cd_type%TYPE;
1387 v_acai_admission_appl_number
1388 IGS_AD_PS_APPL_INST.admission_appl_number%TYPE;
1389 v_acai_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE;
1390 v_acai_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE;
1391 v_acai_hecs_payment_option IGS_AD_PS_APPL_INST.hecs_payment_option%TYPE;
1392 v_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE;
1393 v_adm_appl_status IGS_AD_APPL_STAT.adm_appl_status%TYPE;
1394 v_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE;
1395 v_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
1396 v_cnt_aa_acai_rec NUMBER(2);
1397 CURSOR c_aa_acai (
1398 cp_course_cd IGS_AD_PS_APPL_INST.course_cd%TYPE) IS
1399 SELECT COUNT(*),
1400 acai.admission_appl_number,
1401 acai.nominated_course_cd,
1402 acai.sequence_number,
1403 acai.hecs_payment_option
1404 FROM
1405 IGS_AD_APPL aa,
1406 IGS_AD_PS_APPL_INST acai
1407 WHERE
1408 aa.person_id = acai.person_id AND
1409 aa.admission_appl_number = acai.admission_appl_number AND
1410 acai.person_id = p_person_id AND
1411 acai.nominated_course_cd = cp_course_cd AND
1412 aa.acad_cal_type = p_acad_cal_type AND
1413 aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
1414 aa.adm_cal_type = p_adm_cal_type AND
1415 aa.adm_ci_sequence_number = p_adm_ci_sequence_number AND
1416 aa.tac_appl_ind = 'Y'
1417 GROUP BY
1418 acai.admission_appl_number,
1419 acai.nominated_course_cd,
1420 acai.sequence_number,
1421 acai.hecs_payment_option;
1422 CURSOR c_upd_acai (
1423 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1424 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
1425 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) IS
1426
1427 SELECT ROWID, acai.*
1428 FROM IGS_AD_PS_APPL_INST acai
1429 WHERE
1430 acai.person_id = p_person_id AND
1431 acai.admission_appl_number = cp_admission_appl_number AND
1432 acai.nominated_course_cd = cp_nominated_course_cd AND
1433 acai.sequence_number = cp_sequence_number
1434 FOR UPDATE OF hecs_payment_option NOWAIT;
1435
1436 Rec_IGS_AD_PS_APPL_Inst c_upd_acai%ROWTYPE;
1437
1438 BEGIN
1439 /*
1440 || Change History :
1441 || Who When What
1442 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
1443 || rrengara 26-JUL-2001 Bug Enh No: 1891835 : For the DLD Process Student Response to Offer. Added two columns in TBH
1444 || (reverse chronological order - newest change first)
1445 */
1446
1447 p_message_name := NULL;
1448 ----------------------------------
1449 -- Validate all input parameters
1450 ----------------------------------
1451 p_tac_course_match_ind := 'N';
1452 IF p_acad_cal_type IS NULL OR
1453 p_acad_ci_sequence_number IS NULL OR
1454 p_adm_cal_type IS NULL OR
1455 p_adm_ci_sequence_number IS NULL OR
1456 p_person_id IS NULL OR
1457 p_tac_course_cd IS NULL OR
1458 p_appl_dt IS NULL OR
1459 p_offer_dt IS NULL OR
1460 p_fee_paying_appl_ind IS NULL OR
1461 p_insert_outcome_letter_ind IS NULL OR
1462 p_pre_enrol_ind IS NULL THEN
1463 p_message_name := 'IGS_AD_PARAMETERS_NOT_SUFFICI';
1464 p_return_type := 'E';
1465 RETURN FALSE;
1466 END IF;
1467 IF p_admission_cat IS NOT NULL THEN
1468 v_admission_cat := p_admission_cat;
1469 END IF;
1470 ---------------------------------------------------------------
1471 -- We need to convert the TAC COURSE code to a Callista code.
1472 ---------------------------------------------------------------
1473 IF Igs_Ad_Gen_010.ADMP_GET_TAC_CEPRC(
1474 p_adm_cal_type,
1475 p_adm_ci_sequence_number,
1476 p_acad_cal_type,
1477 p_tac_course_cd,
1478 v_admission_cat, -- IN/OUT
1479 v_course_cd, -- OUT NOCOPY
1480 v_version_number, -- OUT NOCOPY
1481 v_cal_type, -- OUT NOCOPY
1482 v_location_cd, -- OUT NOCOPY
1483 v_attendance_mode, -- OUT NOCOPY
1484 v_attendance_type, -- OUT NOCOPY
1485 v_unit_set_cd, -- OUT NOCOPY
1486 v_us_version_number, -- OUT NOCOPY
1487 v_coo_id, -- OUT NOCOPY
1488 v_ref_cd_type, -- OUT NOCOPY
1489 v_message_name) = FALSE THEN
1490 IF v_message_name <> 'IGS_GE_INVALID_VALUE' THEN
1491 -- no record found to match TAC IGS_PS_COURSE
1492 p_tac_course_match_ind := 'Y';
1493 p_course_cd := v_course_cd;
1494 END IF;
1495 p_message_name := v_message_name;
1496 p_return_type := 'E';
1497 RETURN FALSE;
1498 ELSE
1499 p_tac_course_match_ind := 'Y';
1500 p_course_cd := v_course_cd;
1501 END IF;
1502 -------------------------------------------------------------------------------
1503 -- Validate that a matching admission COURSE has not already been inserted for
1504 -- this IGS_PE_PERSON via the TAC process in this admission period (this may be a
1505 -- result of different payment options)
1506 -------------------------------------------------------------------------------
1507 OPEN c_aa_acai (v_course_cd);
1508 FETCH c_aa_acai INTO
1509 v_cnt_aa_acai_rec,
1510 v_acai_admission_appl_number,
1511 v_acai_nominated_course_cd,
1512 v_acai_sequence_number,
1513 v_acai_hecs_payment_option;
1514 IF (c_aa_acai%FOUND) THEN
1515 CLOSE c_aa_acai;
1516 IF v_cnt_aa_acai_rec > 1 THEN
1517 -- Something is wrong!! Offer should not be made to same IGS_PS_COURSE twice.
1518 p_message_name := 'IGS_AD_INVALID_APPLICANT';
1519 p_return_type := 'E';
1520 RETURN FALSE;
1521 END IF;
1522 IF v_acai_hecs_payment_option IS NOT NULL AND
1523 ((v_ref_cd_type = 'OTHER' AND p_fee_paying_appl_ind = 'U') OR
1524 p_fee_paying_appl_ind = 'N') THEN
1525 -- Update existing HECS payment option, this should only be specified
1526 -- when forcing payment with an offer.
1527 BEGIN
1528 OPEN c_upd_acai (
1529 v_acai_admission_appl_number,
1530 v_acai_nominated_course_cd,
1531 v_acai_sequence_number);
1532 -- FETCH c_upd_acai INTO v_hecs_payment_option;
1533 FETCH c_upd_acai INTO Rec_IGS_AD_PS_APPL_Inst;
1534
1535 IF (c_upd_acai%FOUND) THEN
1536
1537 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
1538 X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
1539 X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
1540 X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
1541 X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
1542 X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
1543 X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
1544 X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
1545 X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
1546 X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
1547 X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
1548 X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
1549 X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
1550 X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
1551 X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
1552 X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
1553 X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
1554 X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
1555 X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
1556 X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
1557 X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
1558 X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
1559 X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
1560 X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
1561 X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
1562 X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
1563 X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
1564 X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
1565 X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
1566 X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
1567 X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
1568 X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
1569 X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
1570 X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
1571 X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
1572 X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
1573 X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
1574 X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
1575 X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
1576 X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
1577 X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
1578 X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
1579 X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
1580 X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
1581 X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
1582 X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
1583 X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
1584 X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
1585 X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
1586 X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
1587 X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
1588 X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
1589 X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
1590 X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
1591 X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
1592 X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
1593 X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
1594 X_ADM_OUTCOME_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS ,
1595 X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
1596 X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
1597 X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
1598 X_OFFER_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_DT ,
1599 X_OFFER_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_RESPONSE_DT ,
1600 X_PRPSD_COMMENCEMENT_DT => Rec_IGS_AD_PS_APPL_Inst.PRPSD_COMMENCEMENT_DT ,
1601 X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
1602 X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
1603 X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
1604 X_ADM_OFFER_RESP_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_RESP_STATUS ,
1605 X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
1606 X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
1607 X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
1608 X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
1609 X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
1610 X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
1611 X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
1612 X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
1613 X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
1614 X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
1615 X_HECS_PAYMENT_OPTION => NULL,
1616 X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
1617 X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
1618 X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
1619 X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
1620 X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
1621 X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
1622 X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
1623 X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
1624 X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
1625 X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
1626 X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
1627 X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
1628 X_MODE => 'R',
1629 X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Enh Bug#1964478 added three parameters
1630 X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Enh Bug#1964478 added three parameters
1631 X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Enh Bug#1964478 added three parameters
1632 X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
1633 X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
1634 X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
1635 X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
1636 X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
1637 X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
1638 X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
1639 X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
1640 X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
1641 X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
1642 X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
1643 X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
1644 X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
1645 X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
1646 X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
1647 X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
1648 X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
1649 X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
1650 X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
1651 X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
1652 X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
1653 X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
1654 X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
1655 X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
1656 X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
1657 X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
1658 X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
1659 X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
1660 X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
1661 X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
1662 X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
1663 X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
1664 X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
1665 X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
1666 X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
1667 x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
1668 x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
1669 );
1670
1671 END IF;
1672 CLOSE c_upd_acai;
1673 EXCEPTION
1674 WHEN e_resource_busy THEN
1675 p_message_name := 'IGS_AD_HECS_PAYMENT_NOT_RESET';
1676 p_return_type := 'W';
1677 RETURN FALSE;
1678 END;
1679 -- Warn that this applicant has been made more
1680 -- than one offer with different payment options
1681 p_message_name := 'IGS_AD_APPL_MADE_INVALIDOFFER';
1682 p_return_type := 'W';
1683 RETURN FALSE;
1684 ELSIF v_acai_hecs_payment_option IS NULL AND
1685 ((v_ref_cd_type = 'OTHER' AND p_fee_paying_appl_ind = 'U') OR
1686 p_fee_paying_appl_ind = 'Y') THEN
1687 -- Warn that this applicant has been made more
1688 -- than one offer with different payment options
1689 p_message_name := 'IGS_AD_APPL_MADE_INVALIDOFFER';
1690 p_return_type := 'W';
1691 RETURN FALSE;
1692 ELSE
1693 -- Something is wrong!! Offer should not be made to same IGS_PS_COURSE twice.
1694 p_message_name := 'IGS_AD_INVALID_APPLICANT';
1695 p_return_type := 'E';
1696 RETURN FALSE;
1697 END IF;
1698 END IF;
1699 CLOSE c_aa_acai;
1700 ----------------------------------
1701 -- Insert admission application
1702 ----------------------------------
1703 SAVEPOINT sp_save_point1;
1704 v_adm_appl_status := Igs_Ad_Gen_008.ADMP_GET_SYS_AAS(
1705 'RECEIVED');
1706 v_adm_fee_status := Igs_Ad_Gen_009.ADMP_GET_SYS_AFS(
1707 'NOT-APPLIC');
1708 IF Igs_Ad_Prc_Tac_Offer.admp_ins_adm_appl(
1709 p_person_id,
1710 p_appl_dt,
1711 p_acad_cal_type,
1712 p_acad_ci_sequence_number,
1713 p_adm_cal_type,
1714 p_adm_ci_sequence_number,
1715 v_admission_cat,
1716 'COURSE',
1717 v_adm_appl_status,
1718 v_adm_fee_status, -- IN OUT NOCOPY
1719 'Y', -- (TAC application indicator)
1720 v_admission_appl_number, -- OUT NOCOPY
1721 v_message_name,
1722 null,
1723 null,
1724 null,
1725 null,
1726 null,
1727 null,
1728 null) = FALSE THEN
1729 ROLLBACK TO sp_save_point1;
1730 p_message_name := v_message_name;
1731 p_return_type := 'E';
1732 RETURN FALSE;
1733 END IF;
1734 ----------------------------------------
1735 -- Insert admission COURSE application
1736 ----------------------------------------
1737 IF Igs_Ad_Prc_Tac_Offer.admp_ins_adm_crs_app(
1738 p_person_id,
1739 v_admission_appl_number,
1740 v_course_cd,
1741 p_basis_for_admission_type,
1742 p_admission_cd,
1743 'N', -- (request for reconsideration indicator)
1744 'N', -- (request for advanced standing indicator)
1745 v_message_name) = FALSE THEN
1746 ROLLBACK TO sp_save_point1;
1747 p_message_name := v_message_name;
1748 p_return_type := 'E';
1749 RETURN FALSE;
1750 END IF;
1751 --------------------------------------------------
1752 -- Determine if HECS payment option should be set
1753 --------------------------------------------------
1754 IF ( p_fee_paying_appl_ind = 'Y' OR
1755 p_fee_paying_appl_ind = 'U' AND v_ref_cd_type = 'OTHER') THEN
1756 v_hecs_payment_option := p_hecs_payment_option;
1757 ELSE
1758 v_hecs_payment_option := NULL;
1759 END IF;
1760 -- Insert admission COURSE application instance
1761 IF Igs_Ad_Prc_Tac_Offer.admp_ins_tac_acai (
1762 p_person_id,
1763 v_admission_appl_number,
1764 p_acad_cal_type,
1765 p_acad_ci_sequence_number,
1766 p_adm_cal_type,
1767 p_adm_ci_sequence_number,
1768 v_admission_cat,
1769 p_appl_dt,
1770 v_adm_fee_status,
1771 p_preference_number,
1772 SYSDATE, -- offer date
1773 NULL, -- offer response date
1774 v_course_cd,
1775 v_version_number,
1776 v_location_cd,
1777 v_attendance_mode,
1778 v_attendance_type,
1779 v_unit_set_cd,
1780 v_us_version_number,
1781 p_fee_cat,
1782 v_hecs_payment_option,
1783 p_correspondence_cat,
1784 p_enrolment_cat,
1785 p_insert_outcome_letter_ind,
1786 p_pre_enrol_ind,
1787 v_return_type,
1788 v_message_name) = FALSE THEN
1789 IF v_return_type = 'E' THEN
1790 ROLLBACK TO sp_save_point1;
1791 END IF;
1792 p_message_name := v_message_name;
1793 p_return_type := v_return_type;
1794 RETURN FALSE;
1795 END IF;
1796 RETURN TRUE;
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 IF (c_aa_acai%ISOPEN) THEN
1800 CLOSE c_aa_acai;
1801 END IF;
1802 IF (c_upd_acai%ISOPEN) THEN
1803 CLOSE c_upd_acai;
1804 END IF;
1805 App_Exception.Raise_Exception;
1806 END;
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1810 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_tac_course');
1811 IGS_GE_MSG_STACK.ADD;
1812 App_Exception.Raise_Exception;
1813 END admp_ins_tac_course;
1814 --
1815 -- Insert a tertiary education record
1816 FUNCTION admp_ins_tert_edu(
1817 p_person_id IN NUMBER ,
1818 p_exclusion_ind IN VARCHAR2 ,
1819 p_tertiary_edu_lvl_comp IN VARCHAR2 ,
1820 p_enrolment_first_yr IN NUMBER ,
1821 p_institution_cd IN VARCHAR2 ,
1822 p_enrolment_latest_yr IN NUMBER ,
1823 p_grade_point_average IN NUMBER ,
1824 p_language_of_tuition IN VARCHAR2 ,
1825 p_qualification IN VARCHAR2 ,
1826 p_institution_name IN VARCHAR2 ,
1827 p_equiv_full_time_yrs_enr IN NUMBER ,
1828 p_student_id IN VARCHAR2 ,
1829 p_course_cd IN VARCHAR2 ,
1830 p_course_title IN VARCHAR2 ,
1831 p_state_cd IN VARCHAR2 ,
1832 p_level_of_achievement_type IN VARCHAR2 ,
1833 p_field_of_study IN VARCHAR2 ,
1834 p_language_component IN VARCHAR2 ,
1835 p_country_cd IN VARCHAR2 ,
1836 p_tertiary_edu_lvl_qual IN VARCHAR2 ,
1837 p_honours_level IN VARCHAR2 ,
1838 p_notes IN VARCHAR2 ,
1839 p_message_name OUT NOCOPY VARCHAR2 ,
1840 p_inserted_ind OUT NOCOPY VARCHAR2 )
1841 RETURN BOOLEAN IS
1842 BEGIN --admp_ins_tert_edu
1843 -- This function inserts a new IGS_AD_TER_EDU record.
1844 DECLARE
1845 CURSOR c_ins IS
1846 SELECT institution_cd,
1847 local_institution_ind
1848 FROM IGS_OR_INSTITUTION
1849 WHERE govt_institution_cd = p_institution_cd OR
1850 institution_cd = p_institution_cd;
1851 CURSOR c_lc (
1852 cp_language_cd IGS_PE_LANGUAGE_CD.language_cd%TYPE) IS
1853 SELECT DISTINCT(language_cd) language_cd
1854 FROM IGS_PE_LANGUAGE_CD
1855 WHERE govt_language_cd = cp_language_cd OR
1856 language_cd = cp_language_cd;
1857 CURSOR c_fos IS
1858 SELECT DISTINCT(field_of_study) field_of_study
1859 FROM IGS_PS_FLD_OF_STUDY
1860 WHERE govt_field_of_study = p_field_of_study OR
1861 field_of_study = p_field_of_study;
1862 CURSOR c_cd IS
1863 SELECT DISTINCT(country_cd) country_cd
1864 FROM IGS_PE_COUNTRY_CD
1865 WHERE govt_country_cd = p_country_cd OR
1866 country_cd = p_country_cd;
1867 CURSOR c_hl IS
1868 SELECT DISTINCT(honours_level) honours_level
1869 FROM IGS_GR_HONOURS_LEVEL
1870 WHERE ( govt_honours_level = p_honours_level OR
1871 honours_level = p_honours_level ) AND
1872 closed_ind = 'N';
1873
1874 v_ins_rec c_ins%ROWTYPE;
1875 v_institution_cd IGS_AD_TER_EDU.institution_cd%TYPE DEFAULT NULL;
1876 v_language_of_tuition
1877 IGS_AD_TER_EDU.language_of_tuition%TYPE DEFAULT NULL;
1878 v_language_cd IGS_PE_LANGUAGE_CD.language_cd%TYPE DEFAULT NULL;
1879 v_tertiary_edu_lvl_qual
1880 IGS_AD_TER_EDU.tertiary_edu_lvl_qual%TYPE DEFAULT NULL;
1881 v_tertiary_edu_lvl_comp
1882 IGS_AD_TER_EDU.tertiary_edu_lvl_comp%TYPE DEFAULT NULL;
1883 v_field_of_study IGS_AD_TER_EDU.field_of_study%TYPE DEFAULT NULL;
1884 v_language_component IGS_AD_TER_EDU.language_component%TYPE DEFAULT NULL;
1885 v_country_cd IGS_AD_TER_EDU.country_cd%TYPE DEFAULT NULL;
1886 v_honours_level IGS_AD_TER_EDU.honours_level%TYPE DEFAULT NULL;
1887 v_enrolment_latest_yr
1888 IGS_AD_TER_EDU.enrolment_latest_yr%TYPE DEFAULT NULL;
1889 v_institution_name IGS_AD_TER_EDU.institution_name%TYPE DEFAULT NULL;
1890 v_message_name VARCHAR2(30) DEFAULT 0;
1891 v_inserted_ind VARCHAR2(1) DEFAULT 'N';
1892 v_institution_cd_found BOOLEAN := TRUE;
1893
1894 CURSOR C_IGS_AD_TER_EDU_SEQ_NUM_S IS
1895 SELECT IGS_AD_TER_EDU_SEQ_NUM_S.NEXTVAL FROM DUAL;
1896
1897 lv_NextVal NUMBER;
1898 lv_rowid VARCHAR2(25);
1899 BEGIN
1900 p_message_name := NULL;
1901 p_inserted_ind := 'N';
1902 -- Validate tertiary education parameters
1903 -- Validate tertiary education level of completion
1904 IF p_tertiary_edu_lvl_comp IS NULL THEN
1905 p_message_name := 'IGS_AD_TRTYEDU_CANINS_LOC_NS';
1906 RETURN FALSE;
1907 ELSE
1908 v_tertiary_edu_lvl_comp := ADMP_GET_LVL_COMP(p_tertiary_edu_lvl_comp);
1909 IF v_tertiary_edu_lvl_comp IS NULL THEN
1910 p_message_name := 'IGS_AD_TRTYEDU_CANINS_LOC_NE';
1911 RETURN FALSE;
1912 END IF;
1913 END IF;
1914 -- Validate tertiary education level of qualification
1915 IF p_tertiary_edu_lvl_qual IS NOT NULL THEN
1916 v_tertiary_edu_lvl_qual := Igs_Ad_Gen_006.ADMP_GET_LVL_QUAL(p_tertiary_edu_lvl_qual);
1917 END IF;
1918 -- Validate tertiary education INSTITUTION details
1919 IF p_institution_cd IS NOT NULL THEN
1920 OPEN c_ins;
1921 FETCH c_ins INTO v_ins_rec;
1922 IF c_ins%NOTFOUND THEN
1923 CLOSE c_ins;
1924 v_institution_cd_found := FALSE;
1925 ELSE
1926 CLOSE c_ins;
1927 IF v_ins_rec.local_institution_ind = 'Y' THEN
1928 -- Do NOT insert tertiary education details,
1929 -- these are available elsewhere in the database
1930 RETURN TRUE;
1931 ELSE
1932 v_institution_cd := v_ins_rec.institution_cd;
1933 END IF;
1934 END IF;
1935 IF NOT v_institution_cd_found THEN
1936 IF p_institution_name IS NOT NULL THEN
1937 v_institution_name := p_institution_name;
1938 ELSE
1939 p_message_name := 'IGS_AD_TRTYEDU_CANINS_INCD_NS';
1940 RETURN FALSE;
1941 END IF;
1942 END IF;
1943 ELSE -- p_institution_cd IS NULL
1944 IF p_institution_name IS NULL THEN
1945 p_message_name := 'IGS_AD_TRTYEDU_CANINS_INCD_NE';
1946 RETURN FALSE;
1947 ELSE
1948 v_institution_name := p_institution_name;
1949 END IF;
1950 END IF;
1951 -- Validate language of tutition
1952 IF p_language_of_tuition IS NOT NULL THEN
1953 OPEN c_lc(
1954 p_language_of_tuition);
1955 FETCH c_lc INTO v_language_of_tuition;
1956 CLOSE c_lc;
1957 END IF;
1958 -- Validate language component
1959 IF p_language_component IS NOT NULL THEN
1960 OPEN c_lc(
1961 p_language_component);
1962 FETCH c_lc INTO v_language_component;
1963 CLOSE c_lc;
1964 END IF;
1965 -- Validate field of study
1966 IF p_field_of_study IS NOT NULL THEN
1967 OPEN c_fos;
1968 FETCH c_fos INTO v_field_of_study;
1969 CLOSE c_fos;
1970 END IF;
1971 -- Validate country code
1972 IF p_country_cd IS NOT NULL THEN
1973 OPEN c_cd;
1974 FETCH c_cd INTO v_country_cd;
1975 CLOSE c_cd;
1976 END IF;
1977 -- Validate honours level
1978 IF p_honours_level IS NOT NULL THEN
1979 OPEN c_hl;
1980 FETCH c_hl INTO v_honours_level;
1981 CLOSE c_hl;
1982 END IF;
1983 -- Validate enrolment years
1984 IF Igs_Ad_Val_Te.admp_val_te_enr_yr(
1985 p_enrolment_first_yr,
1986 p_enrolment_latest_yr,
1987 v_message_name) <> FALSE THEN
1988 v_enrolment_latest_yr := p_enrolment_latest_yr;
1989 END IF;
1990
1991 OPEN C_IGS_AD_TER_EDU_SEQ_NUM_S;
1992 FETCH C_IGS_AD_TER_EDU_SEQ_NUM_S INTO lv_NextVal;
1993 IF C_IGS_AD_TER_EDU_SEQ_NUM_S%NOTFOUND THEN
1994 RAISE NO_DATA_FOUND;
1995 END IF;
1996 CLOSE C_IGS_AD_TER_EDU_SEQ_NUM_S;
1997
1998 Igs_Ad_Ter_Edu_Pkg.Insert_Row (
1999 X_Mode => 'R',
2000 X_RowId => lv_rowid,
2001 X_Person_Id => p_person_id,
2002 X_Sequence_Number => lv_NextVal,
2003 X_Tertiary_Edu_Lvl_Comp => v_tertiary_edu_lvl_comp,
2004 X_Exclusion_Ind => p_exclusion_ind,
2005 X_Institution_Cd => v_institution_cd,
2006 X_Institution_Name => v_institution_name,
2007 X_Enrolment_First_Yr => p_enrolment_first_yr,
2008 X_Enrolment_Latest_Yr => v_enrolment_latest_yr,
2009 X_Course_Cd => p_course_cd,
2010 X_Course_Title => p_course_title,
2011 X_Field_Of_Study => v_field_of_study,
2012 X_Language_Component => v_language_component,
2013 X_Student_Id => p_student_id,
2014 X_Equiv_Full_Time_Yrs_Enr => p_equiv_full_time_yrs_enr,
2015 X_Tertiary_Edu_Lvl_Qual => v_tertiary_edu_lvl_qual,
2016 X_Qualification => p_qualification,
2017 X_Honours_Level => v_honours_level,
2018 X_Level_Of_Achievement_Type => p_level_of_achievement_type,
2019 X_Grade_Point_Average => p_grade_point_average,
2020 X_Language_Of_Tuition => v_language_of_tuition,
2021 X_State_Cd => p_state_cd,
2022 X_Country_Cd => v_country_cd,
2023 X_Notes => p_notes
2024 );
2025
2026 p_inserted_ind := 'Y';
2027 RETURN TRUE;
2028 EXCEPTION
2029 WHEN NO_DATA_FOUND THEN
2030 CLOSE C_IGS_AD_TER_EDU_SEQ_NUM_S;
2031 WHEN OTHERS THEN
2032 IF(c_ins%ISOPEN) THEN
2033 CLOSE c_ins;
2034 END IF;
2035 IF(c_lc%ISOPEN) THEN
2036 CLOSE c_lc;
2037 END IF;
2038 IF(c_fos%ISOPEN) THEN
2039 CLOSE c_fos;
2040 END IF;
2041 IF(c_cd%ISOPEN) THEN
2042 CLOSE c_cd;
2043 END IF;
2044 IF(c_hl%ISOPEN) THEN
2045 CLOSE c_hl;
2046 END IF;
2047 App_Exception.Raise_Exception;
2048 END;
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2052 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_tert_edu');
2053 IGS_GE_MSG_STACK.ADD;
2054 App_Exception.Raise_Exception;
2055 END admp_ins_tert_edu;
2056
2057 --
2058 -- inserts a new record into the IGS_PE_ALT_PERS_ID table
2059 PROCEDURE admp_ins_alt_prsn_id(
2060 p_alt_person_id IN VARCHAR2 ,
2061 p_alt_person_id_type IN VARCHAR2 ,
2062 p_person_id IN NUMBER ,
2063 p_start_dt IN DATE ,
2064 p_end_dt IN DATE )
2065 IS
2066 BEGIN
2067 DECLARE
2068 v_api_person_id IGS_PE_ALT_PERS_ID.api_person_id%TYPE;
2069 CURSOR c_api IS
2070 SELECT api.api_person_id
2071 FROM IGS_PE_ALT_PERS_ID api
2072 WHERE pe_person_id = p_person_id AND
2073 api_person_id = p_alt_person_id AND
2074 person_id_type = p_alt_person_id_type;
2075 lv_rowid VARCHAR2(25);
2076 BEGIN -- Procedure looks for an existing alternate PERSON ID matched from
2077 -- the parameters. If it does not exist, a new record is inserted.
2078 OPEN c_api;
2079 FETCH c_api INTO v_api_person_id;
2080 IF (c_api%NOTFOUND) THEN
2081
2082 IGS_PE_ALT_PERS_ID_Pkg.Insert_Row (
2083 X_Mode => 'R',
2084 X_RowId => lv_rowid,
2085 X_Pe_Person_Id => p_person_id,
2086 X_Api_Person_Id => p_alt_person_id,
2087 X_Person_Id_Type => p_alt_person_id_type,
2088 X_Start_Dt => TRUNC(p_start_dt),
2089 X_End_Dt => TRUNC(p_end_dt) ,
2090 X_ATTRIBUTE_CATEGORY => NULL,
2091 X_ATTRIBUTE1 => NULL,
2092 X_ATTRIBUTE2 => NULL,
2093 X_ATTRIBUTE3 => NULL,
2094 X_ATTRIBUTE4 => NULL,
2095 X_ATTRIBUTE5 => NULL,
2096 X_ATTRIBUTE6 => NULL,
2097 X_ATTRIBUTE7 => NULL,
2098 X_ATTRIBUTE8 => NULL,
2099 X_ATTRIBUTE9 => NULL,
2100 X_ATTRIBUTE10 => NULL,
2101 X_ATTRIBUTE11 => NULL,
2102 X_ATTRIBUTE12 => NULL,
2103 X_ATTRIBUTE13 => NULL,
2104 X_ATTRIBUTE14 => NULL,
2105 X_ATTRIBUTE15 => NULL,
2106 X_ATTRIBUTE16 => NULL,
2107 X_ATTRIBUTE17 => NULL,
2108 X_ATTRIBUTE18 => NULL,
2109 X_ATTRIBUTE19 => NULL,
2110 X_ATTRIBUTE20 => NULL,
2111 X_REGION_CD => NULL
2112 );
2113
2114
2115 END IF;
2116 CLOSE c_api;
2117 END;
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2121 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_alt_prsn_id');
2122 IGS_GE_MSG_STACK.ADD;
2123 App_Exception.Raise_Exception;
2124 END admp_ins_alt_prsn_id;
2125 --
2126 -- Inserts a IGS_PE_PERSON and alternate PERSON ID record with data from TAC
2127 -- ssawhney 2225917 : remove customer creation
2128 FUNCTION admp_ins_tac_prsn(
2129 p_person_id IN NUMBER ,
2130 p_tac_person_id IN VARCHAR2 ,
2131 p_surname IN VARCHAR2 ,
2132 p_given_names IN VARCHAR2 ,
2133 p_sex IN VARCHAR2 ,
2134 p_birth_dt IN DATE ,
2135 p_alt_person_id_type IN VARCHAR2 ,
2136 p_new_person_id OUT NOCOPY NUMBER ,
2137 p_message_out OUT NOCOPY VARCHAR2 )
2138 RETURN BOOLEAN IS
2139 /******************************************************************
2140 Created By:
2141 Date Created By:
2142 Purpose:
2143 Known limitations,enhancements,remarks:
2144 Change History
2145 Who When What
2146 skpandey 21-SEP-2005 Bug: 3663505
2147 Description: Added ATTRIBUTES 21 TO 24 to store additional information
2148 ******************************************************************/
2149
2150 BEGIN -- admp_ins_tac_prsn
2151 -- This module uses information from the TAC offer load process
2152 -- to create IGS_PE_PERSON and alternate PERSON ID records if they
2153 -- don't already exist.
2154 DECLARE
2155 v_new_person_id IGS_PE_PERSON.person_id%TYPE;
2156 v_new_person_number IGS_PE_PERSON.person_number%TYPE;
2157 v_message_name VARCHAR2(30);
2158 v_api_x VARCHAR2(1) DEFAULT NULL;
2159 v_new_sex VARCHAR2(1) DEFAULT NULL;
2160 l_sex Varchar2(30) DEFAULT NULL;
2161 CURSOR c_api IS
2162 SELECT 'x'
2163 FROM IGS_PE_ALT_PERS_ID
2164 WHERE pe_person_id = p_person_id AND
2165 api_person_id = p_tac_person_id AND
2166 person_id_type = p_alt_person_id_type;
2167 lv_rowid VARCHAR2(25);
2168 l_msg_count NUMBER;
2169 l_msg_data VARCHAR2(2000);
2170 l_return_status VARCHAR2(1);
2171
2172 --lv_acc_no VARCHAR2(1);
2173 l_object_version_number NUMBER;
2174
2175 BEGIN
2176 p_message_out := NULL;
2177 IF NVL(p_person_id,0) <> 0 THEN
2178 -- we matched a student
2179 p_message_out := 'TAC student' || ' ' ||
2180 p_tac_person_id || ' ' ||
2181 p_surname || ' ' ||
2182 p_given_names || ' ' ||
2183 'matched Callista person' || ' ' ||
2184 IGS_GE_NUMBER.TO_CANN(p_person_id);
2185 -- Record the new TAC ID
2186 -- First check if this TAC ID already exists in
2187 -- IGS_PE_ALT_PERS_ID rec
2188 OPEN c_api;
2189 FETCH c_api INTO v_api_x;
2190 IF (c_api%NOTFOUND) THEN
2191 admp_ins_alt_prsn_id(
2192 p_tac_person_id,
2193 p_alt_person_id_type,
2194 p_person_id,
2195 SYSDATE,
2196 NULL);
2197 END IF;
2198 CLOSE c_api;
2199 ELSE
2200 -- PERSON not found, need to create a new ID number
2201 -- first must call IGS_GE_GEN_002.GENP_GET_NXT_PRSN_ID to return
2202 -- the next person_id value by calling
2203 IF IGS_GE_GEN_002.GENP_GET_NXT_PRSN_ID(
2204 v_new_person_id,
2205 v_message_name) = FALSE THEN
2206 -- Cannot generate new ID
2207 p_message_out := p_tac_person_id || ' ' || fnd_message.get_string('IGS',v_message_name);
2208 RETURN FALSE;
2209 ELSE
2210 -- New ID generated, set output parameter
2211 p_new_person_id := v_new_person_id;
2212 -- Get fields for insert of new PERSON details
2213 IF p_sex <> 'M' AND
2214 p_sex <> 'F' THEN
2215 v_new_sex := 'U';
2216 ELSE
2217 v_new_sex := p_sex;
2218 END IF;
2219 p_message_out := 'Creating ID '|| IGS_GE_NUMBER.TO_CANN(v_new_person_id);
2220
2221 -- Code added for Leap Frogging after customer bug#1700178 on ver 1.7 -tray -(03-05-2001)
2222 -- changed by ssawhney bug 2225917 -- OSS Will not create customer account
2223
2224 --Bug# 3562134
2225 IF p_sex = 'M' THEN
2226 l_sex := 'MALE';
2227 ELSIF p_sex = 'F' THEN
2228 l_sex := 'FEMALE';
2229 ELSE
2230 l_sex := 'UNKNOWN';
2231 END IF;
2232
2233 IGS_PE_PERSON_PKG.Insert_Row(
2234 X_MSG_COUNT => l_msg_count,
2235 X_MSG_DATA => l_msg_data,
2236 X_RETURN_STATUS => l_return_status,
2237 X_ROWID => lv_rowId,
2238 X_PERSON_ID => v_new_person_id,
2239 X_PERSON_NUMBER => v_new_person_number,
2240 X_SURNAME => p_surname,
2241 X_MIDDLE_NAME => NULL,
2242 X_GIVEN_NAMES => p_given_names,
2243 X_SEX => l_sex,
2244 X_TITLE => NULL,
2245 X_STAFF_MEMBER_IND => 'N',
2246 X_DECEASED_IND => 'N',
2247 X_SUFFIX => NULL,
2248 X_PRE_NAME_ADJUNCT => NULL,
2249 X_ARCHIVE_EXCLUSION_IND => 'N',
2250 X_ARCHIVE_DT => NULL,
2251 X_PURGE_EXCLUSION_IND => 'N',
2252 X_PURGE_DT => NULL,
2253 X_DECEASED_DATE => NULL,
2254 X_PROOF_OF_INS => NULL,
2255 X_PROOF_OF_IMMU => NULL,
2256 X_BIRTH_DT => P_BIRTH_DT,
2257 X_SALUTATION => NULL,
2258 X_ORACLE_USERNAME => NULL,
2259 X_PREFERRED_GIVEN_NAME => NULL,
2260 X_EMAIL_ADDR => NULL,
2261 X_LEVEL_OF_QUAL_ID => NULL,
2262 X_MILITARY_SERVICE_REG=> NULL,
2263 X_VETERAN=> NULL,
2264 X_HZ_PARTIES_OVN => l_object_version_number,
2265 X_ATTRIBUTE_CATEGORY=> NULL,
2266 X_ATTRIBUTE1=> NULL,
2267 X_ATTRIBUTE2=> NULL,
2268 X_ATTRIBUTE3=> NULL,
2269 X_ATTRIBUTE4=> NULL,
2270 X_ATTRIBUTE5=> NULL,
2271 X_ATTRIBUTE6=> NULL,
2272 X_ATTRIBUTE7=> NULL,
2273 X_ATTRIBUTE8=> NULL,
2274 X_ATTRIBUTE9=> NULL,
2275 X_ATTRIBUTE10=> NULL,
2276 X_ATTRIBUTE11=> NULL,
2277 X_ATTRIBUTE12=> NULL,
2278 X_ATTRIBUTE13=> NULL,
2279 X_ATTRIBUTE14=> NULL,
2280 X_ATTRIBUTE15=> NULL,
2281 X_ATTRIBUTE16=> NULL,
2282 X_ATTRIBUTE17=> NULL,
2283 X_ATTRIBUTE18=> NULL,
2284 X_ATTRIBUTE19=> NULL,
2285 X_ATTRIBUTE20=> NULL,
2286 X_PERSON_ID_TYPE=> NULL,
2287 X_API_PERSON_ID=> NULL,
2288 X_ATTRIBUTE21=> NULL,
2289 X_ATTRIBUTE22=> NULL,
2290 X_ATTRIBUTE23=> NULL,
2291 X_ATTRIBUTE24=> NULL
2292 );
2293
2294
2295
2296
2297 admp_ins_alt_prsn_id(
2298 p_tac_person_id,
2299 p_alt_person_id_type,
2300 v_new_person_id,
2301 SYSDATE,
2302 NULL);
2303 END IF; -- insert of new IGS_PE_PERSON
2304 END IF; -- matched person_id <> 0
2305 RETURN TRUE;
2306 END;
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2310 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_tac_prsn');
2311 IGS_GE_MSG_STACK.ADD;
2312 App_Exception.Raise_Exception;
2313 END admp_ins_tac_prsn;
2314 --
2315 -- Insert a new PERSON address record and end date the previous record
2316 -- ssawhney : 2225917 SWCR008, made changes due to change in IGS_PE_PERSON_ADDR_PKG
2317 --
2318 FUNCTION admp_ins_person_addr(
2319 p_person_id IN NUMBER ,
2320 p_addr_type IN VARCHAR2 ,
2321 p_start_dt IN DATE ,
2322 p_addr_line_1 IN VARCHAR2 ,
2323 p_addr_line_2 VARCHAR2 ,
2324 p_addr_line_3 IN VARCHAR2 ,
2325 p_addr_line_4 IN VARCHAR2 ,
2326 p_aust_postcode IN NUMBER ,
2327 p_os_code IN VARCHAR2 ,
2328 p_phone_1 IN VARCHAR2 ,
2329 p_phone_2 IN VARCHAR2 ,
2330 p_message_name OUT NOCOPY VARCHAR2 )
2331 RETURN BOOLEAN IS
2332 BEGIN --admp_ins_person_addr
2333 --Procedure inserts a new PERSON address record
2334 DECLARE
2335 CURSOR c_pa IS
2336 -- SELECT pa.start_dt
2337 SELECT pa.*
2338 FROM IGS_PE_PERSON_ADDR_V pa
2339 WHERE pa.person_id = p_person_id AND
2340 pa.end_dt IS NULL AND
2341 pa.status = 'A' AND
2342 pa.addr_type IN ( SELECT adt.addr_type
2343 FROM IGS_PE_PERSON_ADDR adt
2344 WHERE adt.correspondence_ind = 'Y');
2345 -- FOR UPDATE OF pa.end_dt NOWAIT;
2346 /* This logic will not work as expected because this cursor may retrieve more than one record
2347 Also, the update will not take place since l_location_id is null */
2348
2349 v_pa_rec c_pa%ROWTYPE;
2350 v_aust_postcode NUMBER;
2351 Rec_IGS_PE_PERSON_Addr c_pa%ROWTYPE;
2352 lv_rowid VARCHAR2(25);
2353
2354 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
2355 l_location_ovn hz_locations.object_version_number%TYPE;
2356
2357 L_RETURN_STATUS VARCHAR2(1);
2358 L_MSG_DATA VARCHAR2(2000);
2359 l_party_site_id NUMBER;
2360 l_party_type VARCHAR2(100);
2361
2362 l_LAST_UPDATE_DATE DATE;
2363 l_location_id NUMBER;
2364
2365 BEGIN
2366 --Set the end date for the previous IGS_PE_PERSON_ADDR record
2367 --of this address type
2368 OPEN c_pa;
2369 -- FETCH c_pa INTO v_pa_rec;
2370 FETCH c_pa INTO Rec_IGS_PE_PERSON_Addr;
2371 IF (c_pa%FOUND) THEN
2372 IF TRUNC(v_pa_rec.start_dt) <> TRUNC(p_start_dt) THEN
2373
2374 l_party_site_ovn := Rec_IGS_PE_PERSON_Addr.party_site_ovn;
2375 l_location_ovn := Rec_IGS_PE_PERSON_Addr.location_ovn;
2376
2377 IGS_PE_PERSON_ADDR_PKG.Update_Row(
2378 P_ACTION => 'U',
2379 P_ROWID => lv_RowId,
2380 P_LOCATION_ID => l_location_Id, -- This is wrong: location_id should not be null
2381 P_START_DT => Rec_IGS_PE_PERSON_Addr.Start_Dt,
2382 P_END_DT => TRUNC(p_start_dt - 1),
2383 P_COUNTRY => Rec_IGS_PE_PERSON_Addr.COUNTRY,
2384 P_ADDRESS_STYLE => NULL,
2385 P_ADDR_LINE_1 => Rec_IGS_PE_PERSON_Addr.Addr_Line_1,
2386 P_ADDR_LINE_2=> Rec_IGS_PE_PERSON_Addr.Addr_Line_2,
2387 P_ADDR_LINE_3 => Rec_IGS_PE_PERSON_Addr.Addr_Line_3,
2388 P_ADDR_LINE_4 => Rec_IGS_PE_PERSON_Addr.Addr_Line_4,
2389 P_DATE_LAST_VERIFIED => Rec_IGS_PE_PERSON_Addr.DATE_LAST_VERIFIED,
2390 P_CORRESPONDENCE => Rec_IGS_PE_PERSON_Addr.CORRESPONDENCE_ind,
2391 P_CITY => Rec_IGS_PE_PERSON_Addr.CITY,
2392 P_STATE => Rec_IGS_PE_PERSON_Addr.STATE,
2393 P_PROVINCE => Rec_IGS_PE_PERSON_Addr.PROVINCE,
2394 P_COUNTY => Rec_IGS_PE_PERSON_Addr.COUNTY,
2395 P_POSTAL_CODE => Rec_IGS_PE_PERSON_Addr.postal_code,
2396 P_ADDRESS_LINES_PHONETIC => NULL,
2397 P_DELIVERY_POINT_CODE => Rec_IGS_PE_PERSON_Addr.DELIVERY_POINT_CODE,
2398 P_OTHER_DETAILS_1 => Rec_IGS_PE_PERSON_Addr.other_details_1,
2399 P_OTHER_DETAILS_2 => Rec_IGS_PE_PERSON_Addr.other_details_2,
2400 P_OTHER_DETAILS_3 => Rec_IGS_PE_PERSON_Addr.other_details_3,
2401 L_RETURN_STATUS => l_return_status,
2402 L_MSG_DATA => l_msg_data,
2403 P_PARTY_ID => Rec_IGS_PE_PERSON_Addr.Person_Id,
2404 P_PARTY_SITE_ID => l_party_site_id,
2405 P_PARTY_TYPE => l_party_type,
2406 P_LAST_UPDATE_DATE => l_last_update_date,
2407 p_party_site_ovn => l_party_site_ovn,
2408 p_location_ovn => l_location_ovn,
2409 p_status => Rec_IGS_PE_PERSON_Addr.status
2410 );
2411
2412 CLOSE c_pa;
2413 ELSE
2414 CLOSE c_pa;
2415 p_message_name := 'IGS_AD_CORR_ADDRESS_EXISTS';
2416 RETURN FALSE;
2417 END IF;
2418 END IF;
2419
2420 IGS_PE_PERSON_ADDR_PKG.Insert_Row(
2421 P_ACTION => 'I',
2422 P_ROWID => lv_RowId,
2423 P_LOCATION_ID => l_location_Id,
2424 P_START_DT => NULL,
2425 P_END_DT => TRUNC(p_start_dt - 1),
2426 P_COUNTRY => Rec_IGS_PE_PERSON_Addr.COUNTRY,
2427 P_ADDRESS_STYLE => NULL,
2428 P_ADDR_LINE_1 => P_Addr_Line_1,
2429 P_ADDR_LINE_2=> P_Addr_Line_2,
2430 P_ADDR_LINE_3 => P_Addr_Line_3,
2431 P_ADDR_LINE_4 => P_Addr_Line_4,
2432 P_DATE_LAST_VERIFIED => Rec_IGS_PE_PERSON_Addr.DATE_LAST_VERIFIED,
2433 P_CORRESPONDENCE => Rec_IGS_PE_PERSON_Addr.CORRESPONDENCE_ind,
2434 P_CITY => Rec_IGS_PE_PERSON_Addr.CITY,
2435 P_STATE => Rec_IGS_PE_PERSON_Addr.STATE,
2436 P_PROVINCE => Rec_IGS_PE_PERSON_Addr.PROVINCE,
2437 P_COUNTY => Rec_IGS_PE_PERSON_Addr.COUNTY,
2438 P_POSTAL_CODE => Rec_IGS_PE_PERSON_Addr.postal_code,
2439 P_ADDRESS_LINES_PHONETIC => NULL,
2440 P_DELIVERY_POINT_CODE => Rec_IGS_PE_PERSON_Addr.DELIVERY_POINT_CODE,
2441 P_OTHER_DETAILS_1 => Rec_IGS_PE_PERSON_Addr.other_details_1,
2442 P_OTHER_DETAILS_2 => Rec_IGS_PE_PERSON_Addr.other_details_2,
2443 P_OTHER_DETAILS_3 => Rec_IGS_PE_PERSON_Addr.other_details_3,
2444 L_RETURN_STATUS => l_return_status,
2445 L_MSG_DATA => l_msg_data,
2446 P_PARTY_ID => Rec_IGS_PE_PERSON_Addr.Person_Id,
2447 P_PARTY_SITE_ID => l_party_site_id,
2448 P_PARTY_TYPE => l_party_type,
2449 p_last_update_date => l_last_update_date,
2450 p_party_site_ovn => l_party_site_ovn,
2451 p_location_ovn => l_location_ovn,
2452 p_status => Rec_IGS_PE_PERSON_Addr.status
2453 );
2454
2455
2456 IF v_aust_postcode = 9999 THEN
2457 p_message_name := 'IGS_GE_INVALID_VALUE';
2458 RETURN FALSE;
2459 ELSE
2460 p_message_name := NULL;
2461 RETURN TRUE;
2462 END IF;
2463 END;
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2467 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_person_addr');
2468 IGS_GE_MSG_STACK.ADD;
2469 App_Exception.Raise_Exception;
2470 END admp_ins_person_addr;
2471 --
2472 -- Insert a admission COURSE application record
2473 FUNCTION admp_ins_adm_crs_app(
2474 p_person_id IN NUMBER ,
2475 p_adm_appl_number IN NUMBER ,
2476 p_nominated_course_cd IN VARCHAR2 ,
2477 p_basis_for_admission_type IN VARCHAR2 ,
2478 p_admission_cd IN VARCHAR2 ,
2479 p_req_for_reconsideration_ind IN VARCHAR2,
2480 p_req_for_adv_standing_ind IN VARCHAR2,
2481 p_message_name OUT NOCOPY VARCHAR2 )
2482 RETURN BOOLEAN IS
2483 e_integrity_exception EXCEPTION;
2484 PRAGMA EXCEPTION_INIT(e_integrity_exception, -2291);
2485 BEGIN -- admp_ins_adm_crs_app
2486 -- This module validate IGS_AD_PS_APLINSTUNT unit version.
2487 DECLARE
2488 v_admission_cat IGS_AD_APPL.admission_cat%TYPE DEFAULT NULL;
2489 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE DEFAULT
2490 NULL;
2491 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE DEFAULT NULL;
2492 v_acad_ci_sequence_number
2493 IGS_AD_APPL.acad_ci_sequence_number%TYPE DEFAULT 0;
2494 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE DEFAULT NULL;
2495 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE DEFAULT 0;
2496 v_appl_dt IGS_AD_APPL.appl_dt%TYPE DEFAULT NULL;
2497 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE DEFAULT NULL;
2498 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE DEFAULT NULL;
2499 v_crv_version_number IGS_PS_VER.version_number%TYPE DEFAULT 0;
2500 v_message_name VARCHAR2(30) DEFAULT 0;
2501 v_return_type VARCHAR2(1) DEFAULT NULL;
2502 v_pref_limit
2503 IGS_AD_PRCS_CAT_STEP.step_type_restriction_num%TYPE DEFAULT NULL;
2504 v_late_appl_allowed_ind VARCHAR2(1) DEFAULT 'N';
2505 v_req_reconsider_allowed_ind VARCHAR2(1) DEFAULT 'N';
2506 v_req_adv_standing_allowed_ind VARCHAR2(1) DEFAULT 'N';
2507 CURSOR c_apcs(
2508 cp_admission_cat IGS_AD_APPL.admission_cat%TYPE) IS
2509 SELECT apcs.s_admission_step_type,
2510 step_type_restriction_num
2511 FROM IGS_AD_PRCS_CAT_STEP apcs
2512 WHERE apcs.admission_cat = cp_admission_cat AND
2513 apcs.s_admission_process_type = 'COURSE' AND
2514 apcs.step_group_type <> 'TRACK'; --2402377
2515 lv_rowid VARCHAR2(25);
2516 l_org_id NUMBER(15);
2517 BEGIN
2518 p_message_name := NULL;
2519 -- Get admission application details required for validation
2520 Igs_Ad_Gen_002.ADMP_GET_AA_DTL(
2521 p_person_id,
2522 p_adm_appl_number,
2523 v_admission_cat,
2524 v_s_admission_process_type,
2525 v_acad_cal_type,
2526 v_acad_ci_sequence_number,
2527 v_adm_cal_type,
2528 v_adm_ci_sequence_number,
2529 v_appl_dt,
2530 v_adm_appl_status,
2531 v_adm_fee_status);
2532 IF v_appl_dt IS NULL THEN
2533 p_message_name := 'IGS_AD_ADMAPPL_NOT_FOUND';
2534 RETURN FALSE;
2535 END IF;
2536 -- Determine the admission process category steps.
2537 FOR v_apcs_rec IN c_apcs(
2538 v_admission_cat ) LOOP
2539 IF v_apcs_rec.s_admission_step_type = 'PREF-LIMIT' THEN
2540 v_pref_limit := v_apcs_rec.step_type_restriction_num;
2541 END IF;
2542 IF v_apcs_rec.s_admission_step_type = 'LATE-APP' THEN
2543 v_late_appl_allowed_ind := 'Y';
2544 END IF;
2545 IF v_apcs_rec.s_admission_step_type = 'RECONSIDER' THEN
2546 v_req_reconsider_allowed_ind := 'Y';
2547 END IF;
2548 IF v_apcs_rec.s_admission_step_type = 'ADVSTAND' THEN
2549 v_req_adv_standing_allowed_ind := 'Y';
2550 END IF;
2551 END LOOP;
2552 -- Validate preference limit
2553 IF Igs_Ad_Val_Aca.admp_val_pref_limit(
2554 p_person_id,
2555 p_adm_appl_number,
2556 p_nominated_course_cd,
2557 -1, -- (acai sequence number not yet known)
2558 'COURSE',
2559 v_pref_limit,
2560 v_message_name) = FALSE THEN
2561 p_message_name := v_message_name;
2562 RETURN FALSE;
2563 END IF;
2564 -- Validate the nominated COURSE code
2565 IF NOT Igs_Ad_Val_Acai.admp_val_acai_course(
2566 p_nominated_course_cd,
2567 NULL, -- COURSE version number
2568 v_admission_cat,
2569 v_s_admission_process_type,
2570 v_acad_cal_type,
2571 v_acad_ci_sequence_number,
2572 v_adm_cal_type,
2573 v_adm_ci_sequence_number,
2574 v_appl_dt,
2575 v_late_appl_allowed_ind,
2576 'N', -- offer indicator
2577 v_crv_version_number, -- out NOCOPY parameters
2578 v_message_name,
2579 v_return_type) THEN
2580 IF v_return_type = 'E' THEN
2581 p_message_name := v_message_name;
2582 RETURN FALSE;
2583 END IF;
2584 END IF;
2585 -- Validate against current student COURSE attempt
2586 IF NOT Igs_Ad_Val_Acai.admp_val_aca_sca(
2587 p_person_id,
2588 p_nominated_course_cd,
2589 v_appl_dt,
2590 v_admission_cat,
2591 v_s_admission_process_type,
2592 NULL, -- Fee category.
2593 NULL, -- Correspondence category.
2594 NULL, -- Enrolment category.
2595 'N', -- Offer indicator
2596 v_message_name,
2597 v_return_type) THEN
2598 IF v_return_type = 'E' THEN
2599 p_message_name := v_message_name;
2600 RETURN FALSE;
2601 END IF;
2602 END IF;
2603 -- Validate basis for admission type closed indicator
2604 IF p_basis_for_admission_type IS NOT NULL THEN
2605 IF NOT Igs_Ad_Val_Aca.admp_val_bfa_closed(
2606 p_basis_for_admission_type,
2607 v_message_name) THEN
2608 p_message_name := v_message_name;
2609 RETURN FALSE;
2610 END IF;
2611 END IF;
2612 -- Validate admission code closed indicator
2613 IF p_admission_cd IS NOT NULL THEN
2614 IF NOT Igs_Ad_Val_Aca.admp_val_aco_closed(
2615 p_admission_cd,
2616 v_message_name) THEN
2617 p_message_name := v_message_name;
2618 RETURN FALSE;
2619 END IF;
2620 END IF;
2621 IF Igs_Ad_Val_Aca.admp_val_aca_req_rec(
2622 p_req_for_reconsideration_ind,
2623 v_req_reconsider_allowed_ind,
2624 v_message_name) = FALSE THEN
2625 p_message_name := v_message_name;
2626 RETURN FALSE;
2627 END IF;
2628 IF Igs_Ad_Val_Aca.admp_val_aca_req_adv(
2629 p_req_for_adv_standing_ind,
2630 v_req_adv_standing_allowed_ind,
2631 v_message_name) = FALSE THEN
2632 p_message_name := v_message_name;
2633 RETURN FALSE;
2634 END IF;
2635 -- Insert the record after all validation has been performed
2636 l_org_id := igs_ge_gen_003.get_org_id;
2637 Igs_Ad_Ps_Appl_Pkg.Insert_Row (
2638 X_Mode => 'R',
2639 X_RowId => lv_rowid,
2640 X_Person_Id => p_person_id,
2641 X_Admission_Appl_Number => p_adm_appl_number,
2642 X_Nominated_Course_Cd => p_nominated_course_cd,
2643 X_Transfer_Course_Cd => NULL,
2644 X_Basis_For_Admission_Type => p_basis_for_admission_type,
2645 X_Admission_Cd => p_admission_cd,
2646 X_Course_Rank_Set => NULL,
2647 X_Course_Rank_Schedule => NULL,
2648 X_Req_For_Reconsideration_Ind => p_req_for_reconsideration_ind,
2649 X_Req_For_Adv_Standing_Ind => p_req_for_adv_standing_ind,
2650 X_Org_Id => l_org_id
2651 );
2652 RETURN TRUE;
2653 END;
2654 EXCEPTION
2655 WHEN e_integrity_exception THEN
2656 Fnd_Message.Set_Name('IGS','IGS_AD_ADM_APPL_NOT_INS');
2657 App_Exception.Raise_Exception;
2658
2659 RETURN FALSE;
2660 WHEN OTHERS THEN
2661 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2662 Fnd_Message.Set_Token('NAME','IGS_AD_PRC_TAC_OFFER.admp_ins_adm_crs_app');
2663 IGS_GE_MSG_STACK.ADD;
2664 App_Exception.Raise_Exception;
2665 END admp_ins_adm_crs_app;
2666
2667 END Igs_Ad_Prc_Tac_Offer;