[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_014
Source
1 PACKAGE BODY IGS_AD_GEN_014 AS
2 /* $Header: IGSADB9B.pls 120.14 2006/05/30 10:59:55 pbondugu ship $ */
3 /******************************************************************
4 Created By: [email protected]
5 Date Created: 12-27-2001
6 Purpose: Common Admissions API to
7 1.Insert Admission Application , proc: insert_adm_appl
8 2.Insert Admission Program Application , proc: insert_adm_appl_prog
9 3.Insert Admission Program Application Instance , proc: insert_adm_appl_prog_inst
10 Known limitations,enhancements,remarks:
11 Change History
12 Who When What
13 rrengara 17-DEC-2002 When we are creating appl , appl program and instance in when other displayed
14 the SQLERRM in the log file
15 cdcruz 18-feb-2002 Bug 2217104 Admit to future term Enhancement,updated tbh call for
16 new columns being added to IGS_AD_PS_APPL_INST
17 rbezawad 30-Oct-2004 Added logic to properly handle the security Policy errors IGS_SC_POLICY_EXCEPTION
18 and IGS_SC_POLICY_UPD_DEL_EXCEP w.r.t. bug fix 3919112.
19
20 ******************************************************************/
21
22 --Fwd Declarations
23 PROCEDURE logHeader(p_proc_name VARCHAR2
24 ,p_mode VARCHAR2 );
25 PROCEDURE logDetail(p_debug_msg VARCHAR2
26 ,p_mode VARCHAR2 );
27 FUNCTION validate_unit_sets(p_unit_set_cd IN VARCHAR2,
28 p_us_version_number IN NUMBER,
29 p_course_cd IN VARCHAR2,
30 p_crv_version_number IN NUMBER,
31 p_acad_cal_type IN VARCHAR2,
32 p_location_cd IN VARCHAR2,
33 p_attendance_mode IN VARCHAR2,
34 p_attendance_type IN VARCHAR2,
35 p_admission_cat IN VARCHAR2,
36 p_offer_ind IN VARCHAR2,
37 p_unit_set_appl IN VARCHAR2,
38 p_message_name OUT NOCOPY VARCHAR2,
39 p_error_code OUT NOCOPY VARCHAR2,
40 p_return_type OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN;
41 PROCEDURE get_adm_step_values(p_admission_cat IN VARCHAR2,
42 p_s_admission_process_type IN VARCHAR2,
43 p_location_cd_ind OUT NOCOPY VARCHAR2,
44 p_attendance_type_ind OUT NOCOPY VARCHAR2,
45 p_attendance_mode_ind OUT NOCOPY VARCHAR2);
46
47 --Proc Defs
48 FUNCTION insert_adm_appl(
49 p_person_id IN NUMBER ,
50 p_appl_dt IN DATE ,
51 p_acad_cal_type IN VARCHAR2 ,
52 p_acad_ci_sequence_number IN NUMBER ,
53 p_adm_cal_type IN VARCHAR2 ,
54 p_adm_ci_sequence_number IN NUMBER ,
55 p_admission_cat IN VARCHAR2 ,
56 p_s_admission_process_type IN VARCHAR2 ,
57 p_adm_appl_status IN VARCHAR2 ,
58 p_adm_fee_status IN OUT NOCOPY VARCHAR2 ,
59 p_tac_appl_ind IN VARCHAR2,
60 p_adm_appl_number OUT NOCOPY NUMBER ,
61 p_message_name OUT NOCOPY VARCHAR2,
62 p_spcl_grp_1 IN NUMBER ,
63 p_spcl_grp_2 IN NUMBER ,
64 p_common_app IN VARCHAR2 ,
65 p_application_type IN VARCHAR2 ,
66 p_choice_number IN NUMBER ,
67 p_routeb_pref IN VARCHAR2 ,
68 p_alt_appl_id IN VARCHAR2,
69 p_appl_fee_amt IN NUMBER DEFAULT NULL,
70 p_log IN VARCHAR2 DEFAULT 'Y'
71 )
72
73 RETURN BOOLEAN IS
74 /*****************************************************************************************
75 Created By: [email protected]
76 Date Created : 12-27-2001
77 Purpose: 1.This Functions inserts an admission application
78 2.Inserts record into igs_ad_appl table after validations
79 3.Returns boolean true if the record is inserted, boolean false if the proc fails.
80 4.Flow/Calls used in the procedure :
81 |-->IGS_AD_VAL_AA.admp_val_aa_insert (IGSAD76B.pls)
82 |-->IGS_AD_VAL_AA.admp_val_aa_acad_cal
83 |-->IGS_AD_VAL_AA.admp_val_aa_adm_cal
84 |-->IGS_AD_VAL_AA.admp_val_aa_adm_cat
85 |-->IGS_AD_VAL_AA.admp_val_aa_appl_dt
86 |-->IGS_AD_VAL_AA.admp_val_aa_aas
87 |-->IGS_AD_VAL_AA.admp_val_aa_afs
88 |-->IGS_AD_VAL_AA.admp_val_aa_tac_appl
89 |-->IGS_AD_APPL_PKG.insert_row
90
91 Known limitations,enhancements,remarks:
92 Change History
93 Who When What
94 rrengara 8-jul-2002 Added UK Parameters choice_number and routre pref to insert_adm_appl procedure for bug 2448262 and also added to igs_ad_appl_pkg.insert_row call
95 rboddu 10-OCT-2002 Added the check for NULL Application Type. Bug: 2599457
96 rghosh 14-nov-2002 Added UK Parameters alt_appl_id to insert_adm_appl procedure for bug 2664410 and also added to igs_ad_appl_pkg.insert_row call
97 *****************************************************************************************/
98
99 --Local variables to check if the Security Policy exception already set or not. Ref: Bug 3919112
100 l_sc_encoded_text VARCHAR2(4000);
101 l_sc_msg_count NUMBER;
102 l_sc_msg_index NUMBER;
103 l_sc_app_short_name VARCHAR2(50);
104 l_sc_message_name VARCHAR2(50);
105
106 BEGIN --admp_ins_adm_appl
107 --Procedure inserts a new IGS_AD_APPL record. It uses an
108 --output parameter to pass back the new admission_appl_number used
109 DECLARE
110 v_dummy CHAR;
111 v_adm_fee_status VARCHAR2(10);
112 v_adm_appl_number IGS_AD_APPL.admission_appl_number%TYPE;
113 v_message_name VARCHAR2(30);
114 v_return_type VARCHAR2(1);
115 v_title_required_ind VARCHAR2(1) DEFAULT 'Y';
116 v_birth_dt_required_ind VARCHAR2(1) DEFAULT 'Y';
117 v_fees_required_ind VARCHAR2(1) DEFAULT 'N';
118 v_person_encmb_chk_ind VARCHAR2(1) DEFAULT 'N';
119 v_cond_offer_fee_allowed_ind VARCHAR2(1) DEFAULT 'N';
120 cst_error CONSTANT VARCHAR2(1) := 'E';
121 cst_warn CONSTANT VARCHAR2(1) := 'W';
122 CURSOR c_apcs (
123 cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE,
124 cp_s_admission_process_type
125 IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
126 SELECT s_admission_step_type
127 FROM IGS_AD_PRCS_CAT_STEP
128 WHERE admission_cat = cp_admission_cat AND
129 s_admission_process_type = cp_s_admission_process_type AND
130 step_group_type <> 'TRACK'; --2402377
131 -- Have to find the last admission_appl_number used.
132 CURSOR c_aa IS
133 SELECT NVL(MAX(admission_appl_number),0) + 1
134 FROM IGS_AD_APPL /*Replaced as part of Bug 3150054*/
135 WHERE person_id = p_person_id;
136
137 CURSOR c_appl_type(p_admission_application_type igs_ad_ss_appl_typ.admission_application_type%TYPE) IS
138 SELECT a.admission_application_type
139 FROM igs_ad_ss_appl_typ a,
140 igs_ad_prcs_cat_v b
141 WHERE a.closed_ind = 'N'
142 AND a.admission_cat = b.admission_cat
143 AND a.s_admission_process_type = b.s_admission_process_type
144 AND b.closed_ind ='N'
145 AND a.admission_application_type = p_admission_application_type;
146
147 l_appl_type igs_ad_ss_appl_typ.admission_application_type%TYPE;
148 lv_rowid VARCHAR2(25);
149 l_org_id NUMBER(15);
150 v_effective_dt DATE;
151
152 BEGIN
153 -- Work out NOCOPY the new admission_appl_number.
154 OPEN c_aa;
155 FETCH c_aa
156 INTO v_adm_appl_number;
157 IF c_aa%NOTFOUND THEN
158 RAISE NO_DATA_FOUND;
159 END IF;
160 CLOSE c_aa;
161 --
162 -- Determine the admission process category steps.
163 --
164 FOR v_apcs_rec IN c_apcs (
165 p_admission_cat,
166 p_s_admission_process_type)
167 LOOP
168 IF v_apcs_rec.s_admission_step_type = 'UN-IGS_PE_TITLE' THEN
169 v_title_required_ind := 'N';
170 ELSIF v_apcs_rec.s_admission_step_type = 'UN-DOB' THEN
171 v_birth_dt_required_ind := 'N';
172 ELSIF v_apcs_rec.s_admission_step_type = 'APP-FEE' THEN
173 v_fees_required_ind := 'Y';
174 ELSIF v_apcs_rec.s_admission_step_type = 'CHKPENCUMB' THEN
175 v_person_encmb_chk_ind := 'Y';
176 ELSIF v_apcs_rec.s_admission_step_type = 'FEE-COND' THEN
177 v_cond_offer_fee_allowed_ind := 'Y';
178 END IF;
179 END LOOP;
180 -- Set fee status
181 IF v_fees_required_ind = 'Y' THEN
182 --modified the hardcoded text 'EXEMPT' to IGS_AD_GEN_009.ADMP_GET_SYS_AFS('EXEMPT') bug#2744113 (rghosh)
183 p_adm_fee_status := IGS_AD_GEN_009.ADMP_GET_SYS_AFS('EXEMPT');
184 END IF;
185 --
186 -- Validate insert of the admission application record.
187 --
188 IF IGS_AD_VAL_AA.admp_val_aa_insert (
189 p_person_id,
190 p_adm_cal_type,
191 p_adm_ci_sequence_number,
192 p_s_admission_process_type,
193 v_person_encmb_chk_ind,
194 p_appl_dt,
195 v_title_required_ind,
196 v_birth_dt_required_ind,
197 v_message_name,
198 v_return_type) = FALSE THEN
199 IF NVL(v_return_type, '-1') = cst_error THEN
200 p_message_name := v_message_name;
201 RETURN FALSE;
202 END IF;
203 END IF;
204 --
205 -- Validate the Academic Calendar.
206 --
207 IF IGS_AD_VAL_AA.admp_val_aa_acad_cal (
208 p_acad_cal_type,
209 p_acad_ci_sequence_number,
210 v_message_name) = FALSE THEN
211 p_message_name := v_message_name;
212 RETURN FALSE;
213 END IF;
214 --
215 -- Validate the Admission Calendar.
216 --
217 IF IGS_AD_VAL_AA.admp_val_aa_adm_cal (
218 p_adm_cal_type,
219 p_adm_ci_sequence_number,
220 p_acad_cal_type,
221 p_acad_ci_sequence_number,
222 p_admission_cat,
223 p_s_admission_process_type,
224 v_message_name) = FALSE THEN
225 p_message_name := v_message_name;
226 RETURN FALSE;
227 END IF;
228 --
229 -- Validate the Admission Category.
230 --
231 IF IGS_AD_VAL_AA.admp_val_aa_adm_cat (
232 p_admission_cat,
233 v_message_name) = FALSE THEN
234 p_message_name := v_message_name;
235 RETURN FALSE;
236 END IF;
237 --
238 -- Validate the Application Date.
239 --
240 IF IGS_AD_VAL_AA.admp_val_aa_appl_dt (
241 p_appl_dt,
242 v_message_name) = FALSE THEN
243 p_message_name := v_message_name;
244 RETURN FALSE;
245 END IF;
246 --
247 -- Validate the Admission Application Status.
248 --
249 IF IGS_AD_VAL_AA.admp_val_aa_aas (
250 p_person_id,
251 v_adm_appl_number,
252 p_adm_appl_status,
253 v_message_name) = FALSE THEN
254 p_message_name := v_message_name;
255 RETURN FALSE;
256 END IF;
257 --
258 -- Validate the Admission Fee Status.
259 --
260 IF IGS_AD_VAL_AA.admp_val_aa_afs (
261 p_person_id,
262 v_adm_appl_number,
263 p_adm_fee_status,
264 v_fees_required_ind,
265 v_cond_offer_fee_allowed_ind,
266 v_message_name) = FALSE THEN
267 p_message_name := v_message_name;
268 RETURN FALSE;
269 END IF;
270 --
271 -- Validate the TAC Application Indicator.
272 --
273 IF IGS_AD_VAL_AA.admp_val_aa_tac_appl (
274 p_person_id,
275 p_tac_appl_ind,
276 p_appl_dt,
277 p_s_admission_process_type,
278 v_message_name,
279 v_return_type) = FALSE THEN
280 IF NVL(v_return_type, '-1') = cst_error THEN
281 p_message_name := v_message_name;
282 RETURN FALSE;
283 END IF;
284 END IF;
285
286 --Validate the Application Type and fail the validation if Application Type passed is NULL. Bug: 2599457
287 IF p_application_type IS NULL THEN
288 p_message_name:= 'IGS_AD_APPL_TYPE_NULL';
289 RETURN FALSE;
290 ELSE
291 OPEN c_appl_type(p_application_type);
292 FETCH c_appl_type INTO l_appl_type;
293 CLOSE c_appl_type;
294 IF l_appl_type IS NULL THEN
295 p_message_name:= 'IGS_AD_APPL_TYP_APC_CLOSED';
296 RETURN FALSE;
297 END IF;
298 END IF;
299
300 -- Check if there are any person level holds that revoke/suspend services -- arvsrini igsm
301 IF v_person_encmb_chk_ind = 'Y' THEN
302 -- Determine the effective date for performing the encumbrance check.
303 v_effective_dt := NVL(IGS_AD_GEN_006.ADMP_GET_ENCMB_DT (
304 p_adm_cal_type,
305 p_adm_ci_sequence_number),SYSDATE);
306
307 IF NOT IGS_EN_VAL_ENCMB.enrp_val_excld_prsn (
308 p_person_id,
309 NULL, -- Input parameter course code: not applicable
310 v_effective_dt,
311 v_message_name) THEN
312 IF IGS_AD_TI_COMP.G_CALLED_FROM = 'S' THEN
313 p_message_name := 'IGS_AD_SS_PERS_HOLD_EXISTS';
314 ELSE
315 p_message_name := 'IGS_AD_OFR_CANNOT_BEMADE';
316 END IF;
317 RETURN FALSE;
318 END IF;
319 END IF;
320
321
322
323
324 --Now insert the new record
325 --Populate the org id
326 l_org_id := igs_ge_gen_003.get_org_id;
327
328 IGS_AD_APPL_PKG.insert_row (
329 X_Mode => 'R',
330 X_RowId => lv_rowid,
331 X_Person_Id => p_Person_Id,
332 X_Admission_Appl_Number => v_adm_appl_number,
333 X_Appl_Dt => p_Appl_Dt,
334 X_Acad_Cal_Type => p_Acad_Cal_Type,
335 X_Acad_Ci_Sequence_Number => p_Acad_Ci_Sequence_Number,
336 X_Adm_Cal_Type => p_Adm_Cal_Type,
337 X_Adm_Ci_Sequence_Number => p_Adm_Ci_Sequence_Number,
338 X_Admission_Cat => p_Admission_Cat,
339 X_S_Admission_Process_Type => p_S_Admission_Process_Type,
340 X_Adm_Appl_Status => p_Adm_Appl_Status,
341 X_Adm_Fee_Status => p_Adm_Fee_Status,
342 X_Tac_Appl_Ind => p_Tac_Appl_Ind,
343 X_Org_Id => l_org_id,
344 X_Spcl_Grp_1 => p_spcl_grp_1,
345 X_Spcl_Grp_2 => p_spcl_grp_2,
346 X_Common_App => p_common_app,
347 x_application_type => p_application_type,
348 x_choice_number => p_choice_number,
349 x_routeb_pref => p_routeb_pref,
350 x_alt_appl_id => p_alt_appl_id,
351 x_appl_fee_amt => NVL(p_appl_fee_amt,0)
352 );
353
354 p_adm_appl_number := v_adm_appl_number;
355 p_message_name := NULL;
356 RETURN TRUE;
357 END;
358 EXCEPTION
359 WHEN OTHERS THEN
360 --Loop through all messages in stack to check if there is Security Policy exception already set or not. Ref: Bug 3919112
361 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
362 WHILE l_sc_msg_count <> 0 loop
363 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
364 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
365 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
366 --Raise the exception to Higher Level with out setting any Unhandled exception.
367 p_message_name := 'IGS_SC_POLICY_EXCEPTION';
368 App_Exception.Raise_Exception;
369 END IF;
370 l_sc_msg_count := l_sc_msg_count - 1;
371 END LOOP;
372
373 IF FND_MSG_PUB.Count_Msg < 1 THEN
374 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
375 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_014.insert_adm_appl -'||SQLERRM);
376 IGS_GE_MSG_STACK.ADD;
377 END IF;
378 IF (p_log = 'Y') THEN
379 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
380 END IF;
381 p_message_name := 'IGS_GE_UNHANDLED_EXP';
382 App_Exception.Raise_Exception;
383
384 END insert_adm_appl;
385
386 FUNCTION insert_adm_appl_prog(
387 p_person_id IN igs_pe_person.person_id%type ,
388 p_adm_appl_number IN NUMBER ,
389 p_nominated_course_cd IN VARCHAR2 ,
390 p_transfer_course_cd IN VARCHAR2,
391 p_basis_for_admission_type IN VARCHAR2 ,
392 p_admission_cd IN VARCHAR2 ,
393 p_req_for_reconsideration_ind IN VARCHAR2 ,
394 p_req_for_adv_standing_ind IN VARCHAR2 ,
395 p_message_name out NOCOPY VARCHAR2,
396 p_log IN VARCHAR2 DEFAULT 'Y')
397 RETURN BOOLEAN IS
398 /*************************************************************************************************************************
399 Created By: [email protected]
400 Date Created : 12-27-2001
401 Purpose: 1.This Functions inserts an admission application Program
402 2.Inserts record into igs_ad_ps_appl table after validations
403 3.Returns boolean true if the record is inserted, boolean false if the proc fails.
404 4.Flow/Calls used in the procedure :
405 |-->IGS_AD_GEN_002.admp_get_aa_dtl
406 |-->IGS_AD_VAL_ACA.admp_val_pref_limit(Validate preference limit) -IGSAD21B.pls
407 |-->IGS_AD_VAL_ACAI.admp_val_acai_course(Validate the nominated COURSE code) -IGSAD22B.pls
408 |-->IGS_AD_VAL_ACAI.admp_val_aca_sca(Validate against current student COURSE attempt) -IGSAD22B.pls
409 |-->Validate basis for admission type closed indicator
410 |-->Validate admission code closed indicator
411 |-->IGS_AD_VAL_ACA.admp_val_aca_req_rec -IGSAD21B.pls
412 |-->IGS_AD_VAL_ACA.admp_val_aca_req_adv -IGSAD21B.pls
413 |-->IGS_AD_VAL_ACA.admp_val_aca_trnsfr(validation for course transfer)
414 |-->IGS_AD_PS_APPL_PKG.insert_row(Insert the record after all validation has been performed) -IGSAI16B.pls
415 Known limitations,enhancements,remarks:
416 Change History
417 Who When What
418 ***************************************************************************************************************************/
419
420 e_integrity_exception EXCEPTION;
421 PRAGMA EXCEPTION_INIT(e_integrity_exception, -2291);
422 lv_mode VARCHAR2(1) DEFAULT 'R';
423
424
425 BEGIN -- insert_adm_appl --Main Loop Begin
426 -- This module validate IGS_AD_PS_APLINSTUNT unit version.
427 logheader('insert_adm_appl',null);
428 DECLARE --i=Inner Loop Declare
429 v_admission_cat IGS_AD_APPL.admission_cat%TYPE DEFAULT NULL;
430 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE DEFAULT NULL;
431 v_acad_cal_type IGS_AD_APPL.acad_cal_type%TYPE DEFAULT NULL;
432 v_acad_ci_sequence_number IGS_AD_APPL.acad_ci_sequence_number%TYPE DEFAULT 0;
433 v_adm_cal_type IGS_AD_APPL.adm_cal_type%TYPE DEFAULT NULL;
434 v_adm_ci_sequence_number IGS_AD_APPL.adm_ci_sequence_number%TYPE DEFAULT 0;
435 v_appl_dt IGS_AD_APPL.appl_dt%TYPE DEFAULT NULL;
436 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE DEFAULT NULL;
437 v_adm_fee_status IGS_AD_APPL.adm_fee_status%TYPE DEFAULT NULL;
438 v_crv_version_number IGS_PS_VER.version_number%TYPE DEFAULT 0;
439 v_message_name VARCHAR2(30) DEFAULT 0;
440 v_return_type VARCHAR2(1) DEFAULT NULL;
441 v_pref_limit IGS_AD_PRCS_CAT_STEP.step_type_restriction_num%TYPE DEFAULT NULL;
442 v_late_appl_allowed_ind VARCHAR2(1) DEFAULT 'N';
443 v_req_reconsider_allowed_ind VARCHAR2(1) DEFAULT 'N';
444 v_req_adv_standing_allowed_ind VARCHAR2(1) DEFAULT 'N';
445 v_check_course_encumb VARCHAR2(1) DEFAULT 'N';
446 cst_error CONSTANT VARCHAR2(1) := 'E';
447
448
449 CURSOR c_apcs(
450 cp_admission_cat IGS_AD_APPL.admission_cat%TYPE,
451 cp_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE)
452 IS
453 SELECT s_admission_step_type,
454 step_type_restriction_num
455 FROM IGS_AD_PRCS_CAT_STEP
456 WHERE admission_cat = cp_admission_cat AND
457 s_admission_process_type = cp_s_admission_process_type AND
458 step_group_type <> 'TRACK'; -- 2402377
459
460 CURSOR c_prg_exists(cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
461 cp_appl_no IGS_AD_PS_APPL.admission_appl_number%TYPE,
462 cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE)
463 IS
464 SELECT tab.* --multiorg table , so rowid need not be selected explicitly
465 FROM IGS_AD_PS_APPL tab
466 WHERE person_id = cp_person_id AND
467 admission_appl_number = cp_appl_no AND
468 nominated_course_cd = cp_nominated_course_cd;
469
470 c_prg_exists_rec c_prg_exists%ROWTYPE;
471
472 lv_rowid VARCHAR2(25);
473 l_org_id NUMBER(15);
474 BEGIN --Inner Loop Begin
475 p_message_name := NULL;
476 -- Get admission application details required for validation
477 logDetail('Before Call to IGS_AD_GEN_002.admp_get_aa_dtl',null);
478 IGS_AD_GEN_002.admp_get_aa_dtl(
479 p_person_id,
480 p_adm_appl_number,
481 v_admission_cat,
482 v_s_admission_process_type,
483 v_acad_cal_type,
484 v_acad_ci_sequence_number,
485 v_adm_cal_type,
486 v_adm_ci_sequence_number,
487 v_appl_dt,
488 v_adm_appl_status,
489 v_adm_fee_status);
490 IF v_appl_dt IS NULL THEN
491 p_message_name := 'IGS_AD_ADMAPPL_NOT_FOUND';
492 RETURN FALSE;
493 END IF;
494 -- Determine the admission process category steps.
495 FOR v_apcs_rec IN c_apcs(
496 v_admission_cat,
497 v_s_admission_process_type ) LOOP
498 IF v_apcs_rec.s_admission_step_type = 'PREF-LIMIT' THEN
499 v_pref_limit := v_apcs_rec.step_type_restriction_num;
500 END IF;
501 IF v_apcs_rec.s_admission_step_type = 'LATE-APP' THEN
502 v_late_appl_allowed_ind := 'Y';
503 END IF;
504 IF v_apcs_rec.s_admission_step_type = 'RECONSIDER' THEN
505 v_req_reconsider_allowed_ind := 'Y';
506 END IF;
507 IF v_apcs_rec.s_admission_step_type = 'ADVSTAND' THEN
508 v_req_adv_standing_allowed_ind := 'Y';
509 END IF;
510 IF v_apcs_rec.s_admission_step_type = 'CHKCENCUMB' THEN
511 v_check_course_encumb := 'Y';
512 END IF;
513
514
515 END LOOP;
516 logDetail('Before Call to Igs_Ad_Val_Aca.admp_val_pref_limit',null);
517 -- Validate preference limit
518 IF Igs_Ad_Val_Aca.admp_val_pref_limit(
519 p_person_id,
520 p_adm_appl_number,
521 p_nominated_course_cd,
522 -1, -- (acai sequence number not yet known)
523 v_s_admission_process_type,
524 v_pref_limit,
525 v_message_name) = FALSE THEN
526 p_message_name := v_message_name;
527 RETURN FALSE;
528 END IF;
529 logDetail('Before Call to Igs_Ad_Val_Acai.admp_val_acai_course',null);
530 -- Validate the nominated COURSE code
531 IF NOT Igs_Ad_Val_Acai.admp_val_acai_course(
532 p_nominated_course_cd,
533 NULL,
534 v_admission_cat,
535 v_s_admission_process_type,
536 v_acad_cal_type,
537 v_acad_ci_sequence_number,
538 v_adm_cal_type,
539 v_adm_ci_sequence_number,
540 v_appl_dt,
541 v_late_appl_allowed_ind,
542 'N', -- offer indicator
543 v_crv_version_number, -- out NOCOPY parameters
544 v_message_name,
545 v_return_type) THEN
546 IF v_return_type = 'E' THEN
547 p_message_name := v_message_name;
548 RETURN FALSE;
549 END IF;
550 END IF;
551 logDetail('Before call to Igs_Ad_Val_Acai.admp_val_aca_sca',null);
552 -- Validate against current student COURSE attempt
553 IF NOT IGS_AD_VAL_ACAI.admp_val_aca_sca(
554 p_person_id,
555 p_nominated_course_cd,
556 v_appl_dt,
557 v_admission_cat,
558 v_s_admission_process_type,
559 NULL, -- Fee category.
560 NULL, -- Correspondence category.
561 NULL, -- Enrolment category.
562 'N', -- Offer indicator
563 v_message_name,
564 v_return_type) THEN
565 IF v_return_type = 'E' THEN
566 p_message_name := v_message_name;
567 RETURN FALSE;
568 END IF;
569 END IF;
570 logDetail('Before call to Igs_Ad_Val_Aca.admp_val_bfa_closed',null);
571 -- Validate basis for admission type closed indicator
572 IF p_basis_for_admission_type IS NOT NULL THEN
573 IF NOT Igs_Ad_Val_Aca.admp_val_bfa_closed(
574 p_basis_for_admission_type,
575 v_message_name) THEN
576 p_message_name := v_message_name;
577 RETURN FALSE;
578 END IF;
579 END IF;
580 -- Validate admission code closed indicator
581 logDetail('Before call to Igs_Ad_Val_Aca.admp_val_aco_closed',null);
582 IF p_admission_cd IS NOT NULL THEN
583 IF NOT Igs_Ad_Val_Aca.admp_val_aco_closed(
584 p_admission_cd,
585 v_message_name) THEN
586 p_message_name := v_message_name;
587 RETURN FALSE;
588 END IF;
589 END IF;
590 logDetail('Before Call to Igs_Ad_Val_Aca.admp_val_aca_req_rec',null);
591 IF Igs_Ad_Val_Aca.admp_val_aca_req_rec(
592 p_req_for_reconsideration_ind,
593 v_req_reconsider_allowed_ind,
594 v_message_name) = FALSE THEN
595 p_message_name := v_message_name;
596 RETURN FALSE;
597 END IF;
598 logDetail('Before Call to Igs_Ad_Val_Aca.admp_val_aca_req_adv',null);
599 IF Igs_Ad_Val_Aca.admp_val_aca_req_adv(
600 p_req_for_adv_standing_ind,
601 v_req_adv_standing_allowed_ind,
602 v_message_name) = FALSE THEN
603 p_message_name := v_message_name;
604 RETURN FALSE;
605 END IF;
606 IF v_s_admission_process_type = 'TRANSFER' THEN
607 logDetail('Before Call to Igs_Ad_Val_Aca.admp_val_aca_trnsfr',null);
608 /* Include here validation for course transfer */
609 IF Igs_Ad_Val_Aca.admp_val_aca_trnsfr(
610 p_person_id,
611 p_nominated_course_cd,
612 v_crv_version_number,
613 p_transfer_course_cd,
614 v_s_admission_process_type,
615 'N',
616 v_adm_cal_type,
617 v_adm_ci_sequence_number,
618 v_message_name,
619 v_return_type) = FALSE THEN
620 p_message_name := v_message_name;
621 RETURN FALSE;
622 END IF;
623 END IF;
624 -- checking holds on the program, to disallow inserting a program if that has been restricted for the person -- igsm arvsrini
625 IF v_check_course_encumb = 'Y' THEN
626 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_encmb(
627 p_person_id,
628 p_nominated_course_cd,
629 v_adm_cal_type,
630 v_adm_ci_sequence_number,
631 v_check_course_encumb,
632 'Y', -- Offer indicator
633 p_message_name,
634 v_return_type) THEN
635 IF v_return_type = cst_error THEN
636 IF IGS_AD_TI_COMP.G_CALLED_FROM = 'S' THEN
637 p_message_name:='IGS_AD_SS_PROG_HOLD_EXISTS';
638 END IF;
639 RETURN FALSE;
640 END IF;
641 END IF;
642 END IF;
643
644
645
646 -- Insert the record after all validation has been performed
647 l_org_id := igs_ge_gen_003.get_org_id;
648 logDetail('Before Call to Igs_Ad_Ps_Appl_Pkg.Insert_Row',null);
649 OPEN c_prg_exists(p_person_id,p_adm_appl_number,p_nominated_course_cd);
650 FETCH c_prg_exists INTO c_prg_exists_rec;
651
652 IF c_prg_exists%NOTFOUND THEN
653 IGS_AD_PS_APPL_PKG.Insert_Row (
654 X_Mode => 'R',
655 X_RowId => lv_rowid,
656 X_Person_Id => p_person_id,
657 X_Admission_Appl_Number => p_adm_appl_number,
658 X_Nominated_Course_Cd => p_nominated_course_cd,
659 X_Transfer_Course_Cd => p_transfer_course_cd,
660 X_Basis_For_Admission_Type => p_basis_for_admission_type,
661 X_Admission_Cd => p_admission_cd,
662 X_Course_Rank_Set => NULL,
663 X_Course_Rank_Schedule => NULL,
664 X_Req_For_Reconsideration_Ind => p_req_for_reconsideration_ind,
665 X_Req_For_Adv_Standing_Ind => p_req_for_adv_standing_ind,
666 X_Org_Id => l_org_id
667 );
668
669 ELSE
670
671 IF NVL(IGS_AD_SS_GEN_001.g_admin_security_on,'N') = 'Y'
672 THEN
673 lv_mode := 'S';
674 END IF;
675 IGS_AD_PS_APPL_PKG.update_Row (
676 X_RowId => c_prg_exists_rec.row_id,
677 X_Person_Id => NVL(p_person_id,c_prg_exists_rec.person_id),
678 X_Admission_Appl_Number => NVL(p_adm_appl_number,c_prg_exists_rec.admission_appl_number),
679 X_Nominated_Course_Cd => NVL(p_nominated_course_cd,c_prg_exists_rec.nominated_course_cd),
680 X_Transfer_Course_Cd => NVL(p_transfer_course_cd,c_prg_exists_rec.transfer_course_cd),
681 X_Basis_For_Admission_Type => NVL(p_basis_for_admission_type,c_prg_exists_rec.basis_for_admission_type),
682 X_Admission_Cd => NVL(p_admission_cd,c_prg_exists_rec.admission_cd),
683 X_Course_Rank_Set => c_prg_exists_rec.Course_Rank_Set,
684 X_Course_Rank_Schedule => c_prg_exists_rec.Course_Rank_Schedule,
685 X_Req_For_Reconsideration_Ind => NVL(p_req_for_reconsideration_ind,c_prg_exists_rec.req_for_reconsideration_ind),
686 X_Req_For_Adv_Standing_Ind => NVL(p_req_for_adv_standing_ind,c_prg_exists_rec.req_for_adv_standing_ind),
687 X_Mode => lv_mode -- enable security for Admin
688 );
689 END IF;
690 CLOSE c_prg_exists;
691 RETURN TRUE;
692
693 END;
694 EXCEPTION
695 WHEN e_integrity_exception THEN
696 Fnd_Message.Set_Name('IGS','IGS_AD_ADM_APPL_NOT_INS');
697 App_Exception.Raise_Exception;
698 RETURN FALSE;
699 WHEN OTHERS THEN
700 IF (p_log = 'Y') THEN
701 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
702 END IF;
703
704 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
705 App_Exception.Raise_Exception;
706 END insert_adm_appl_prog;
707
708
709 FUNCTION insert_adm_appl_prog_inst(
710 p_person_id IN igs_pe_person.person_id%type ,
711 p_admission_appl_number IN NUMBER ,
712 p_acad_cal_type IN VARCHAR2 ,
713 p_acad_ci_sequence_number IN NUMBER ,
714 p_adm_cal_type IN VARCHAR2 ,
715 p_adm_ci_sequence_number IN NUMBER ,
716 p_admission_cat IN VARCHAR2 ,
717 p_s_admission_process_type IN VARCHAR2,
718 p_appl_dt IN DATE ,
719 p_adm_fee_status IN VARCHAR2 ,
720 p_preference_number IN NUMBER ,
721 p_offer_dt IN DATE ,
722 p_offer_response_dt IN DATE ,
723 p_course_cd IN VARCHAR2 ,
724 p_crv_version_number IN NUMBER ,
725 p_location_cd IN VARCHAR2 ,
726 p_attendance_mode IN VARCHAR2 ,
727 p_attendance_type IN VARCHAR2 ,
728 p_unit_set_cd IN VARCHAR2 ,
729 p_us_version_number IN NUMBER ,
730 p_fee_cat IN VARCHAR2 ,
731 p_correspondence_cat IN VARCHAR2 ,
732 p_enrolment_cat IN VARCHAR2 ,
733 p_funding_source IN VARCHAR2,
734 p_edu_goal_prior_enroll IN NUMBER,
735 p_app_source_id IN NUMBER,
736 p_apply_for_finaid IN VARCHAR2,
737 p_finaid_apply_date IN DATE,
738 p_attribute_category IN VARCHAR2,
739 p_attribute1 IN VARCHAR2,
740 p_attribute2 IN VARCHAR2,
741 p_attribute3 IN VARCHAR2,
742 p_attribute4 IN VARCHAR2,
743 p_attribute5 IN VARCHAR2,
744 p_attribute6 IN VARCHAR2,
745 p_attribute7 IN VARCHAR2,
746 p_attribute8 IN VARCHAR2,
747 p_attribute9 IN VARCHAR2,
748 p_attribute10 IN VARCHAR2,
749 p_attribute11 IN VARCHAR2,
750 p_attribute12 IN VARCHAR2,
751 p_attribute13 IN VARCHAR2,
752 p_attribute14 IN VARCHAR2,
753 p_attribute15 IN VARCHAR2,
754 p_attribute16 IN VARCHAR2,
755 p_attribute17 IN VARCHAR2,
756 p_attribute18 IN VARCHAR2,
757 p_attribute19 IN VARCHAR2,
758 p_attribute20 IN VARCHAR2,
759 p_attribute21 IN VARCHAR2,
760 p_attribute22 IN VARCHAR2,
761 p_attribute23 IN VARCHAR2,
762 p_attribute24 IN VARCHAR2,
763 p_attribute25 IN VARCHAR2,
764 p_attribute26 IN VARCHAR2,
765 p_attribute27 IN VARCHAR2,
766 p_attribute28 IN VARCHAR2,
767 p_attribute29 IN VARCHAR2,
768 p_attribute30 IN VARCHAR2,
769 p_attribute31 IN VARCHAR2,
770 p_attribute32 IN VARCHAR2,
771 p_attribute33 IN VARCHAR2,
772 p_attribute34 IN VARCHAR2,
773 p_attribute35 IN VARCHAR2,
774 p_attribute36 IN VARCHAR2,
775 p_attribute37 IN VARCHAR2,
776 p_attribute38 IN VARCHAR2,
777 p_attribute39 IN VARCHAR2,
778 p_attribute40 IN VARCHAR2,
779 p_ss_application_id IN VARCHAR2,
780 p_sequence_number OUT NOCOPY NUMBER,
781 p_return_type OUT NOCOPY VARCHAR2 ,
782 p_error_code OUT NOCOPY VARCHAR2,
783 p_message_name OUT NOCOPY VARCHAR2,
784 p_entry_status IN NUMBER,
785 p_entry_level IN NUMBER,
786 p_sch_apl_to_id IN NUMBER,
787 p_hecs_payment_option IN VARCHAR2,
788 p_log IN VARCHAR2 DEFAULT 'Y')
789 RETURN BOOLEAN IS
790
791 /*************************************************************************************************************************
792 Created By: [email protected]
793 Date Created : 12-27-2001
794 Purpose: 1.This Functions inserts an admission application Program Instance
795 2.Inserts record into igs_ad_ps_appl_inst table after validations
796 3.Returns boolean true if the record is inserted, boolean false if the proc fails.
797 4.Flow/Calls used in the procedure :
798 |-->IGS_AD_GEN_004.admp_get_apcs_val(Get the admission process category steps.) -IGSAD04B.pls
799 |-->IGS_AD_VAL_ACAI.admp_val_acai_fc(Set fee category) -IGSAD22B.pls
800 |-->IGS_AD_GEN_005.admp_get_dflt_ecm(Set enrollment category) -IGSAD05B.pls
801 |-->IGS_AD_VAL_ACAI.admp_val_acai_cc(Set correspondence category) -IGSAD22B.pls
802 |-->Set admission COURSE application instance statuses for PENDING outcome
803 |-->validate_unit_sets(Validate the admission COURSE offering IGS_PS_UNIT set)
804 |-->admp_get_adm_step_values(get if location code, attendance mode and attendance type indicators are set)
805 |-->IGS_AD_VAL_ACAI.admp_val_acai_cop(Validate program offering patterns) -IGSAD22B.pls
806 |-->IGS_AD_PS_APPL_INST_PKG.insert_row(Insert an admission program application instance with PENDING outcome) -IGSAI18B.pls
807 Known limitations,enhancements,remarks:
808 Change History
809 Who When What
810 vdixit 19-Feb-2002 Added the logic to raise the business event in work flow
811 when a Self Service Admission Application is created enh : 2229679
812 nshee 29-Aug-2002 Added the six columns in the insert row for Deferments build - 2395510
813 tray 10-Sep-2002 Added Call to IGS_AD_GEN_003.get_entr_doc_apc for ADCR040 build - 2395510
814 pbondugu 28-Mar-2003 Added funding_source parameter in spec and body.
815 ***************************************************************************************************************************/
816 l_sc_encoded_text VARCHAR2(4000);
817 l_sc_msg_count NUMBER;
818 l_sc_msg_index NUMBER;
819 l_sc_app_short_name VARCHAR2(50);
820 l_sc_message_name VARCHAR2(50);
821
822 BEGIN
823 -- This procedure inserts a new IGS_AD_PS_APPL_INST record.
824 logHeader('insert_adm_appl_prog_inst',null);
825 DECLARE
826 e_resource_busy EXCEPTION;
827 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
828
829 p_dummy VARCHAR2(10);
830 v_check CHAR;
831 v_s_admission_process_type IGS_AD_PRCS_CAT.s_admission_process_type%TYPE;
832 v_fee_cat IGS_FI_FEE_CAT.fee_cat%TYPE;
833 v_enrolment_cat IGS_EN_ENROLMENT_CAT.enrolment_cat%TYPE;
834 v_correspondence_cat IGS_CO_CAT.correspondence_cat%TYPE;
835 v_funding_source IGS_FI_FUND_SRC.funding_source%TYPE;
836 v_hecs_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE;
837 v_description IGS_FI_FEE_CAT.description%TYPE;
838 v_message_name VARCHAR2(30);
839 v_hecs_message_name VARCHAR2(30);
840 v_pre_enrol_message_name VARCHAR2(30);
841 v_outcome_message_name VARCHAR2(30);
842 v_apcs_pref_limit_ind VARCHAR2(127);
843 v_apcs_app_fee_ind VARCHAR2(127);
844 v_apcs_late_app_ind VARCHAR2(127);
845 v_apcs_late_fee_ind VARCHAR2(127);
846 v_apcs_chkpencumb_ind VARCHAR2(127);
847 v_apcs_fee_assess_ind VARCHAR2(127);
848 v_apcs_corcategry_ind VARCHAR2(127);
849 v_apcs_enrcategry_ind VARCHAR2(127);
850 v_apcs_chkcencumb_ind VARCHAR2(127);
851 v_apcs_unit_set_ind VARCHAR2(127);
852 v_apcs_un_crs_us_ind VARCHAR2(127);
853 v_apcs_chkuencumb_ind VARCHAR2(127);
854 v_apcs_unit_restr_ind VARCHAR2(127);
855 v_apcs_unit_restriction_num VARCHAR2(127);
856 v_apcs_un_dob_ind VARCHAR2(127);
857 v_apcs_un_title_ind VARCHAR2(127);
858 v_apcs_asses_cond_ind VARCHAR2(127);
859 v_apcs_fee_cond_ind VARCHAR2(127);
860 v_apcs_doc_cond_ind VARCHAR2(127);
861 v_apcs_multi_off_ind VARCHAR2(127);
862 v_apcs_multi_off_restn_num VARCHAR2(127);
863 v_apcs_set_otcome_ind VARCHAR2(127);
864 v_apcs_override_o_ind VARCHAR2(127);
865 v_apcs_defer_ind VARCHAR2(127);
866 v_apcs_ack_app_ind VARCHAR2(127);
867 v_apcs_outcome_lt_ind VARCHAR2(127);
868 v_apcs_pre_enrol_ind VARCHAR2(127);
869 v_hecs_pmnt_option_found BOOLEAN DEFAULT TRUE;
870 v_offer_letter_ins BOOLEAN DEFAULT TRUE;
871 v_pre_enr_done BOOLEAN DEFAULT TRUE;
872 v_adm_doc_status VARCHAR2(127);
873 v_adm_entry_qual_status VARCHAR2(127);
874 v_adm_pending_outcome_status VARCHAR2(127);
875 v_adm_cndtnl_offer_status VARCHAR2(127);
876 v_adm_offer_dfrmnt_status VARCHAR2(127);
877 v_late_adm_fee_status VARCHAR2(127);
878 v_acai_sequence_number IGS_CA_INST.sequence_number%TYPE;
879 v_offer_adm_outcome_status VARCHAR2(127);
880 v_adm_offer_resp_status VARCHAR2(127);
881 v_new_adm_offer_resp_status VARCHAR2(127);
882 v_offer_response_dt DATE;
883 v_return_type VARCHAR2(1);
884 cst_error CONSTANT VARCHAR2(1):= 'E';
885 V_ERROR_CODE VARCHAR2(30);
886 L_CHECK VARCHAR2(1);
887 l_location_cd_ind VARCHAR2(1);
888 l_attendance_type_ind VARCHAR2(1);
889 l_attendance_mode_ind VARCHAR2(1);
890 l_var VARCHAR2(1);
891 l_late_ind VARCHAR2(1);
892 l_finaid_apply_date DATE;
893 l_msg_data VARCHAR2(2000);
894 v_admission_cat IGS_AD_APPL.admission_cat%TYPE DEFAULT NULL;
895
896
897 CURSOR c_nxt_acai_seq_num IS
898 SELECT NVL(MAX(sequence_number), 0) + 1
899 FROM IGS_AD_PS_APPL_INST
900 WHERE
901 person_id = p_person_id AND
902 admission_appl_number = p_admission_appl_number AND
903 nominated_course_cd = p_course_cd;
904 CURSOR c_upd_acai IS
905 SELECT ROWID, APAI.*
906 FROM IGS_AD_PS_APPL_INST APAI
907 WHERE
908 person_id = p_person_id AND
909 admission_appl_number = p_admission_appl_number AND
910 nominated_course_cd = p_course_cd AND
911 sequence_number = v_acai_sequence_number
912 FOR UPDATE OF person_id NOWAIT;
913
914 CURSOR get_app_source (p_app_source_id NUMBER) IS
915 SELECT system_status
916 FROM igs_ad_code_classes
917 WHERE code_id = p_app_source_id AND
918 class = 'SYS_APPL_SOURCE';
919
920 CURSOR c_apcs(
921 cp_admission_cat IGS_AD_APPL.admission_cat%TYPE,
922 cp_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE)
923 IS
924 SELECT s_admission_step_type,
925 step_type_restriction_num
926 FROM IGS_AD_PRCS_CAT_STEP
927 WHERE admission_cat = cp_admission_cat AND
928 s_admission_process_type = cp_s_admission_process_type AND
929 step_group_type <> 'TRACK'; -- 2402377
930
931
932
933 Rec_IGS_AD_PS_APPL_Inst c_upd_acai%ROWTYPE;
934 LV_ROWID VARCHAR2(25);
935 l_org_id NUMBER(15);
936 v_app_source igs_ad_code_classes.system_status%TYPE;
937 v_check_course_encumb VARCHAR2(1) DEFAULT 'N';
938 v_unit_set_appl_ind VARCHAR2(1) DEFAULT 'N';
939
940 --begin apadegal adtd001 igs.m
941 l_person_type_code IGS_PE_PERSON_TYPES.person_type_code%TYPE;
942 CURSOR c_person_type_code(l_system_type IGS_PE_PERSON_TYPES.system_type%TYPE)
943 IS
944 SELECT person_type_code
945 FROM igs_pe_person_types
946 WHERE system_type=l_system_type;
947 lv_rowid2 VARCHAR2(25) ;
948 lv_type_instance_id NUMBER(15);
949 lv_sysdate DATE := TRUNC(SYSDATE);
950 lv_mode VARCHAR2(1) DEFAULT 'R';
951 --end apadegal adtd001 igs.m
952
953 BEGIN
954 p_message_name := NULL;
955
956 FOR v_apcs_rec IN c_apcs (v_admission_cat, --igsm
957 v_s_admission_process_type)
958 LOOP
959 IF v_apcs_rec.s_admission_step_type = 'CHKCENCUMB' THEN
960 v_check_course_encumb := 'Y';
961 ELSIF v_apcs_rec.s_admission_step_type = 'UNIT-SET' THEN
962 v_unit_set_appl_ind := 'Y';
963 END IF;
964 END LOOP;
965
966 --------------------------------------------------
967 -- Get the admission process category steps.
968 --------------------------------------------------
969 logDetail('Before Call to IGS_AD_GEN_004.admp_get_apcs_val',null);
970 IGS_AD_GEN_004.admp_get_apcs_val(
971 p_admission_cat,
972 p_s_admission_process_type,
973 v_apcs_pref_limit_ind,
974 v_apcs_app_fee_ind,
975 v_apcs_late_app_ind,
976 v_apcs_late_fee_ind,
977 v_apcs_chkpencumb_ind,
978 v_apcs_fee_assess_ind,
979 v_apcs_corcategry_ind,
980 v_apcs_enrcategry_ind,
981 v_apcs_chkcencumb_ind,
982 v_apcs_unit_set_ind,
983 v_apcs_un_crs_us_ind,
984 v_apcs_chkuencumb_ind,
985 v_apcs_unit_restr_ind,
986 v_apcs_unit_restriction_num,
987 v_apcs_un_dob_ind,
988 v_apcs_un_title_ind,
989 v_apcs_asses_cond_ind,
990 v_apcs_fee_cond_ind,
991 v_apcs_doc_cond_ind,
992 v_apcs_multi_off_ind,
993 v_apcs_multi_off_restn_num,
994 v_apcs_set_otcome_ind,
995 v_apcs_override_o_ind,
996 v_apcs_defer_ind,
997 v_apcs_ack_app_ind,
998 v_apcs_outcome_lt_ind,
999 v_apcs_pre_enrol_ind);
1000
1001 --------------------------------
1002 -- Set fee category
1003 --------------------------------
1004 IF p_fee_cat IS NULL THEN
1005
1006 -- Derive the fee category
1007 logDetail('Before Call to IGS_AD_GEN_005.admp_get_dflt_fcm',null);
1008 v_fee_cat := IGS_AD_GEN_005.admp_get_dflt_fcm(
1009 p_admission_cat,
1010 v_description);
1011
1012 ELSIF IGS_AD_VAL_ACAI.admp_val_acai_fc(
1013 p_admission_cat,
1014 p_fee_cat,
1015 v_message_name) = FALSE THEN
1016 v_fee_cat := NULL;
1017 ELSE
1018 v_fee_cat := p_fee_cat;
1019 END IF;
1020
1021 --------------------------------
1022 -- Set enrollment category
1023 --------------------------------
1024 IF p_enrolment_cat IS NULL THEN
1025 -- Derive the enrolment category
1026 logDetail('Before Call to IGS_AD_GEN_005.admp_get_dflt_ecm',null);
1027 v_enrolment_cat := IGS_AD_GEN_005.admp_get_dflt_ecm(
1028 p_admission_cat,
1029 v_description);
1030 ELSIF Igs_Ad_Val_Acai.admp_val_acai_ec(
1031 p_admission_cat,
1032 p_enrolment_cat,
1033 v_message_name) = FALSE THEN
1034 v_enrolment_cat := NULL;
1035 ELSE
1036 v_enrolment_cat := p_enrolment_cat;
1037 END IF;
1038
1039 --------------------------------
1040 -- Set correspondence category
1041 --------------------------------
1042 IF p_correspondence_cat IS NULL THEN
1043 -- Derive the correspondence category
1044 logDetail('Before Call to IGS_AD_GEN_005.admp_get_dflt_ccm',null);
1045 v_correspondence_cat := IGS_AD_GEN_005.admp_get_dflt_ccm(
1046 p_admission_cat,
1047 v_description);
1048 ELSIF Igs_Ad_Val_Acai.admp_val_acai_cc(
1049 p_admission_cat,
1050 p_correspondence_cat,
1051 v_message_name) = FALSE THEN
1052 v_correspondence_cat := NULL;
1053 ELSE
1054 v_correspondence_cat := p_correspondence_cat;
1055 END IF;
1056
1057 --------------------------------
1058 -- Set Funding Source
1059 --------------------------------
1060 IF p_funding_source IS NULL THEN
1061 -- Derive the Funding Source
1062 logDetail('Before Call to IGS_AD_GEN_005.admp_get_dflt_fs',null);
1063 v_funding_source := IGS_AD_GEN_005.admp_get_dflt_fs(
1064 p_course_cd,
1065 p_crv_version_number,
1066 v_description);
1067 ELSIF IGS_AD_VAL_ACAI.admp_val_acai_fs(
1068 p_course_cd,
1069 p_crv_version_number,
1070 p_funding_source,
1071 v_message_name) = FALSE THEN
1072 v_funding_source := NULL;
1073 ELSE
1074 v_funding_source := p_funding_source;
1075 END IF;
1076 --------------------------------
1077 -- Set HECS Payment Option
1078 --------------------------------
1079 IF p_hecs_payment_option IS NULL THEN
1080 -- Derive the HECS Payment Option
1081 logDetail('Before Call to IGS_AD_GEN_005.admp_get_dflt_hpo',null);
1082 v_hecs_payment_option := IGS_AD_GEN_005.admp_get_dflt_hpo(
1083 p_admission_cat,
1084 v_description);
1085
1086 ELSIF IGS_AD_VAL_ACAI.admp_val_acai_hpo(
1087 p_admission_cat,
1088 p_hecs_payment_option,
1089 v_message_name) = FALSE THEN
1090 v_hecs_payment_option := NULL;
1091 ELSE
1092 v_hecs_payment_option := p_hecs_payment_option;
1093 END IF;
1094
1095 --------------------------------------------------------------------------
1096 -- Set admission COURSE application instance statuses for PENDING outcome
1097 --------------------------------------------------------------------------
1098 v_adm_pending_outcome_status := IGS_AD_GEN_009.admp_get_sys_aos('PENDING');
1099 v_adm_cndtnl_offer_status := IGS_AD_GEN_009.admp_get_sys_acos('NOT-APPLIC');
1100 v_adm_offer_resp_status := IGS_AD_GEN_009.admp_get_sys_aors('NOT-APPLIC');
1101 v_adm_offer_dfrmnt_status := IGS_AD_GEN_009.admp_get_sys_aods('NOT-APPLIC');
1102
1103 IF v_apcs_late_fee_ind = 'Y' THEN
1104 --modified the hardcoded text 'EXEMPT' to IGS_AD_GEN_009.ADMP_GET_SYS_AFS('EXEMPT') bug# 2744113 (rghosh)
1105 v_late_adm_fee_status := IGS_AD_GEN_009.ADMP_GET_SYS_AFS('EXEMPT');
1106 ELSE
1107 --modified the hardcoded text 'NOT-APPLIC' to IGS_AD_GEN_009.ADMP_GET_SYS_AFS('NOT-APPLIC') bug# 2744113 (rghosh)
1108 v_late_adm_fee_status := IGS_AD_GEN_009.ADMP_GET_SYS_AFS('NOT-APPLIC');
1109
1110 END IF;
1111
1112 ---------------------------------------------------
1113 -- Get the next sequence number for the application
1114 ---------------------------------------------------
1115 OPEN c_nxt_acai_seq_num;
1116 FETCH c_nxt_acai_seq_num INTO v_acai_sequence_number;
1117 CLOSE c_nxt_acai_seq_num;
1118
1119 ---------------------------------------------------------
1120 -- Validate the admission COURSE offering IGS_PS_UNIT set
1121 ---------------------------------------------------------
1122 logDetail('Before Call to Local Proc validate_unit_sets',null);
1123 IF validate_unit_sets (
1124 p_unit_set_cd,
1125 p_us_version_number,
1126 p_course_cd,
1127 p_crv_version_number,
1128 p_acad_cal_type,
1129 p_location_cd,
1130 p_attendance_mode,
1131 p_attendance_type,
1132 p_admission_cat,
1133 'N',
1134 v_apcs_unit_set_ind,
1135 v_message_name,
1136 v_error_code,
1137 v_return_type) = FALSE THEN
1138 IF NVL(v_return_type, '-1') = cst_error THEN
1139 p_message_name := v_message_name;
1140 p_return_type := v_return_type;
1141 RETURN FALSE;
1142 END IF;
1143 END IF;
1144
1145 --Call routine to determine of the location code, attendance mode and attendance type indicators
1146 --are set
1147 logDetail('Before Call to Local Proc get_adm_step_values',null);
1148 get_adm_step_values(p_admission_cat,
1149 p_s_admission_process_type,
1150 l_location_cd_ind,
1151 l_attendance_type_ind,
1152 l_attendance_mode_ind);
1153
1154 /* Write code here for validating the Location Code*/
1155 /* SQL for validation */
1156 BEGIN
1157 SELECT 'X'
1158 INTO l_var
1159 FROM
1160 DUAL
1161 WHERE
1162 NVL(l_location_cd_ind,'N') IN ('Y','N')
1163 AND NVL(l_attendance_mode_ind,'N') IN ('Y','N')
1164 AND NVL(l_attendance_type_ind,'N')IN ('Y','N');
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 logDetail('insert_adm_appl_prog_inst'|| 'Validation Failed for attendance_mode_ind or location_cd_ind or attendance_type_ind',null );
1168 RAISE;
1169 END;
1170
1171 -------------------------------------------------------------------------------------
1172 -- Validate program offering patterns
1173 --------------------------------------------------------------------------------------
1174 logDetail('Before Call to IGS_AD_VAL_ACAI.admp_val_acai_cop',null);
1175 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_cop(
1176 p_course_cd,
1177 p_crv_version_number,
1178 p_location_cd,
1179 p_attendance_mode,
1180 p_attendance_type,
1181 p_acad_cal_type,
1182 p_acad_ci_sequence_number,
1183 p_adm_cal_type,
1184 p_adm_ci_sequence_number,
1185 p_admission_cat,
1186 p_s_admission_process_type,
1187 'N',
1188 p_appl_dt,
1189 v_apcs_late_app_ind,
1190 'N',
1191 p_message_name,
1192 p_return_type,
1193 l_late_ind) THEN
1194 RETURN FALSE;
1195 END IF;
1196
1197 IF p_apply_for_finaid NOT IN ('Y') THEN
1198 l_finaid_apply_date := NULL;
1199 ELSE
1200 l_finaid_apply_date := p_finaid_apply_date;
1201 END IF;
1202
1203 ------------------------------------------------------------------------------
1204 -- Validate if there is any hold preventing the insert of the unitset selected -- igsm arvsrini
1205 ------------------------------------------------------------------------------
1206 IF v_unit_set_appl_ind = 'Y' THEN
1207 IF v_check_course_encumb = 'Y' THEN
1208 IF NOT IGS_AD_VAL_ACAI.admp_val_us_encmb (
1209 p_person_id,
1210 p_course_cd,
1211 p_unit_set_cd,
1212 p_us_version_number,
1213 p_adm_cal_type,
1214 p_adm_ci_sequence_number,
1215 v_check_course_encumb,
1216 'Y', -- Offer indicator,
1217 p_message_name,
1218 v_return_type) THEN
1219 IF v_return_type = cst_error THEN
1220 IF IGS_AD_TI_COMP.G_CALLED_FROM = 'S' THEN
1221 p_message_name:= 'IGS_AD_SS_USET_HOLD_EXISTS';
1222 END IF;
1223 RETURN FALSE;
1224 END IF;
1225 END IF;
1226 END IF;
1227 END IF;
1228
1229
1230
1231 --------------------------------------------------------------------------
1232 -- Insert an admission program application instance with PENDING outcome
1233 --------------------------------------------------------------------------
1234
1235 l_org_id := igs_ge_gen_003.get_org_id;
1236
1237 -----------------------------------------------------------------------------------------
1238 -- Get the Application Status and Entry Qualification Status for the application instance
1239 -----------------------------------------------------------------------------------------
1240 IGS_AD_GEN_003.get_entr_doc_apc (p_admission_cat => p_admission_cat,
1241 p_s_admission_process_type => p_s_admission_process_type,
1242 l_adm_doc_status => v_adm_doc_status,
1243 l_adm_entr_qual_status => v_adm_entry_qual_status);
1244
1245 logDetail('Before Call to Igs_Ad_Ps_Appl_Inst_Pkg.Insert_Row',null);
1246
1247 IF NVL(IGS_AD_SS_GEN_001.g_admin_security_on,'N') = 'Y'
1248 THEN
1249 lv_mode := 'S';
1250 END IF;
1251
1252 IGS_AD_PS_APPL_INST_PKG.Insert_Row (
1253 X_ROWID => lv_rowid,
1254 X_PERSON_ID => p_Person_Id,
1255 X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
1256 X_NOMINATED_COURSE_CD =>p_course_cd,
1257 X_SEQUENCE_NUMBER => v_acai_sequence_number,
1258 X_PREDICTED_GPA => NULL,
1259 X_ACADEMIC_INDEX => NULL,
1260 X_ADM_CAL_TYPE => p_adm_cal_type,
1261 X_APP_FILE_LOCATION => NULL,
1262 X_ADM_CI_SEQUENCE_NUMBER => p_adm_ci_sequence_number,
1263 X_COURSE_CD => p_Course_Cd,
1264 X_APP_SOURCE_ID => p_app_source_id,
1265 X_CRV_VERSION_NUMBER => p_Crv_Version_Number,
1266 X_WAITLIST_RANK => NULL,
1267 X_LOCATION_CD => p_Location_Cd,
1268 X_ATTENT_OTHER_INST_CD =>NULL,
1269 X_ATTENDANCE_MODE => p_Attendance_Mode,
1270 X_EDU_GOAL_PRIOR_ENROLL_ID => p_edu_goal_prior_enroll,
1271 X_ATTENDANCE_TYPE => p_Attendance_Type,
1272 X_DECISION_MAKE_ID => NULL,
1273 X_UNIT_SET_CD => p_Unit_Set_Cd,
1274 X_DECISION_DATE =>NULL,
1275 X_ATTRIBUTE_CATEGORY =>p_attribute_category ,
1276 X_ATTRIBUTE1=>P_ATTRIBUTE1,
1277 X_ATTRIBUTE2=>P_ATTRIBUTE2,
1278 X_ATTRIBUTE3=>P_ATTRIBUTE3,
1279 X_ATTRIBUTE4=>P_ATTRIBUTE4,
1280 X_ATTRIBUTE5=>P_ATTRIBUTE5,
1281 X_ATTRIBUTE6=>P_ATTRIBUTE6,
1282 X_ATTRIBUTE7=>P_ATTRIBUTE7,
1283 X_ATTRIBUTE8=>P_ATTRIBUTE8,
1284 X_ATTRIBUTE9=>P_ATTRIBUTE9,
1285 X_ATTRIBUTE10=>P_ATTRIBUTE10,
1286 X_ATTRIBUTE11=>P_ATTRIBUTE11,
1287 X_ATTRIBUTE12=>P_ATTRIBUTE12,
1288 X_ATTRIBUTE13=>P_ATTRIBUTE13,
1289 X_ATTRIBUTE14=>P_ATTRIBUTE14,
1290 X_ATTRIBUTE15=>P_ATTRIBUTE15,
1291 X_ATTRIBUTE16=>P_ATTRIBUTE16,
1292 X_ATTRIBUTE17=>P_ATTRIBUTE17,
1293 X_ATTRIBUTE18=>P_ATTRIBUTE18,
1294 X_ATTRIBUTE19=>P_ATTRIBUTE19,
1295 X_ATTRIBUTE20=>P_ATTRIBUTE20,
1296 X_WAITLIST_STATUS => NULL,
1297 X_ATTRIBUTE21=>P_ATTRIBUTE21,
1298 X_ATTRIBUTE22=>P_ATTRIBUTE22,
1299 X_ATTRIBUTE23=>P_ATTRIBUTE23,
1300 X_ATTRIBUTE24=>P_ATTRIBUTE24,
1301 X_ATTRIBUTE25=>P_ATTRIBUTE25,
1302 X_ATTRIBUTE26=>P_ATTRIBUTE26,
1303 X_ATTRIBUTE27=>P_ATTRIBUTE27,
1304 X_ATTRIBUTE28=>P_ATTRIBUTE28,
1305 X_ATTRIBUTE29=>P_ATTRIBUTE29,
1306 X_ATTRIBUTE30=>P_ATTRIBUTE30,
1307 X_ATTRIBUTE31=>P_ATTRIBUTE31,
1308 X_ATTRIBUTE32=>P_ATTRIBUTE32,
1309 X_ATTRIBUTE33=>P_ATTRIBUTE33,
1310 X_ATTRIBUTE34=>P_ATTRIBUTE34,
1311 X_ATTRIBUTE35=>P_ATTRIBUTE35,
1312 X_ATTRIBUTE36=>P_ATTRIBUTE36,
1313 X_ATTRIBUTE37=>P_ATTRIBUTE37,
1314 X_ATTRIBUTE38=>P_ATTRIBUTE38,
1315 X_ATTRIBUTE39=>P_ATTRIBUTE39,
1316 X_ATTRIBUTE40=>P_ATTRIBUTE40,
1317 X_SS_APPLICATION_ID=>P_SS_APPLICATION_ID,
1318 X_SS_PWD=> NULL,
1319 X_DECISION_REASON_ID=>NULL,
1320 X_US_VERSION_NUMBER => p_Us_Version_Number,
1321 X_DECISION_NOTES=>NULL,
1322 X_PENDING_REASON_ID=>NULL,
1323 X_PREFERENCE_NUMBER => p_Preference_Number,
1324 X_ADM_DOC_STATUS=> v_Adm_Doc_Status,
1325 X_ADM_ENTRY_QUAL_STATUS=> v_Adm_Entry_Qual_Status,
1326 X_DEFICIENCY_IN_PREP=>NULL,
1327 X_LATE_ADM_FEE_STATUS => v_Late_Adm_Fee_Status,
1328 X_SPL_CONSIDER_COMMENTS=>NULL,
1329 X_APPLY_FOR_FINAID =>p_apply_for_finaid,
1330 X_FINAID_APPLY_DATE=>l_finaid_apply_date,
1331 X_ADM_OUTCOME_STATUS => v_adm_pending_outcome_status,
1332 X_ADM_OTCM_STAT_AUTH_PER_ID=>NULL,
1333 X_ADM_OUTCOME_STATUS_AUTH_DT=> NULL,
1334 X_ADM_OUTCOME_STATUS_REASON =>NULL,
1335 X_OFFER_DT => NULL,
1336 X_OFFER_RESPONSE_DT => NULL,
1337 X_PRPSD_COMMENCEMENT_DT => NULL,
1338 X_ADM_CNDTNL_OFFER_STATUS => v_adm_cndtnl_offer_status,
1339 X_CNDTNL_OFFER_SATISFIED_DT => NULL,
1340 X_CNDNL_OFR_MUST_BE_STSFD_IND => 'N',
1341 X_ADM_OFFER_RESP_STATUS => v_adm_offer_resp_status,
1342 X_ACTUAL_RESPONSE_DT => NULL,
1343 X_ADM_OFFER_DFRMNT_STATUS => v_adm_offer_dfrmnt_status,
1344 X_DEFERRED_ADM_CAL_TYPE => NULL,
1345 X_DEFERRED_ADM_CI_SEQUENCE_NUM => NULL,
1346 X_DEFERRED_TRACKING_ID => NULL,
1347 X_ASS_RANK => NULL,
1348 X_SECONDARY_ASS_RANK => NULL,
1349 X_INTR_ACCEPT_ADVICE_NUM => NULL,
1350 X_ASS_TRACKING_ID => NULL,
1351 X_FEE_CAT=> v_Fee_Cat,
1352 X_HECS_PAYMENT_OPTION => v_Hecs_Payment_Option,
1353 X_EXPECTED_COMPLETION_YR => NULL,
1354 X_EXPECTED_COMPLETION_PERD => NULL,
1355 X_CORRESPONDENCE_CAT => v_Correspondence_Cat,
1356 X_ENROLMENT_CAT => v_Enrolment_Cat,
1357 X_FUNDING_SOURCE => v_funding_source,
1358 X_APPLICANT_ACPTNCE_CNDTN => NULL,
1359 X_CNDTNL_OFFER_CNDTN => NULL,
1360 X_AUTHORIZED_DT => NULL,
1361 X_AUTHORIZING_PERS_ID => NULL,
1362 X_IDX_CALC_DATE => NULL,
1363 X_MODE =>lv_mode, -- enable security for Admin
1364 X_FUT_ACAD_CAL_TYPE => NULL , -- Bug # 2217104
1365 X_FUT_ACAD_CI_SEQUENCE_NUMBER => NULL ,-- Bug # 2217104
1366 X_FUT_ADM_CAL_TYPE => NULL , -- Bug # 2217104
1367 X_FUT_ADM_CI_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1368 X_PREV_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
1369 X_PREV_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1370 X_FUT_TERM_ADM_APPL_NUMBER => NULL , -- Bug # 2217104
1371 X_FUT_TERM_SEQUENCE_NUMBER => NULL , -- Bug # 2217104
1372 X_DEF_ACAD_CAL_TYPE => NULL, -- Bug 2395510
1373 X_DEF_ACAD_CI_SEQUENCE_NUM => NULL,-- Bug 2395510
1374 X_DEF_PREV_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
1375 X_DEF_PREV_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
1376 X_DEF_TERM_ADM_APPL_NUM => NULL,-- Bug 2395510
1377 X_DEF_APPL_SEQUENCE_NUM => NULL,-- Bug 2395510
1378 X_Org_Id => l_org_id,
1379 X_ENTRY_STATUS => p_entry_status,
1380 X_ENTRY_LEVEL => p_entry_level,
1381 X_SCH_APL_TO_ID => p_sch_apl_to_id);
1382
1383 p_message_name := NULL;
1384 p_sequence_number := v_acai_sequence_number;
1385 logDetail('Before Call to Igs_Ad_Ps_Appl_Inst_Pkg.Insert_Row, returning TRUE',null);
1386
1387
1388 -- begin apadegal adtd001 igs.m
1389
1390 -- a person type record of type 'Applicant' need to be created when ever an application instance is created.
1391
1392 OPEN c_person_type_code('APPLICANT');
1393 FETCH c_person_type_code INTO l_person_type_code;
1394 CLOSE c_person_type_code;
1395
1396 IF NVL(IGS_AD_SS_GEN_001.g_admin_security_on,'N') = 'Y'
1397 THEN
1398 lv_mode := 'S';
1399 END IF;
1400
1401 IGS_PE_TYP_INSTANCES_PKG.INSERT_ROW(
1402 X_MODE => lv_mode, -- enable security for Admin
1403 X_RowId => lv_rowid2,
1404 X_TYPE_INSTANCE_ID => lv_type_instance_id,
1405 X_PERSON_TYPE_CODE => l_person_type_code,
1406 X_PERSON_ID => p_Person_Id,
1407 X_COURSE_CD => NULL,
1408 X_FUNNEL_STATUS => NULL,
1409 X_ADMISSION_APPL_NUMBER => p_Admission_Appl_Number,
1410 X_NOMINATED_COURSE_CD => p_course_cd,
1411 X_SEQUENCE_NUMBER => v_acai_sequence_number,
1412 X_START_DATE => lv_sysdate,
1413 X_END_DATE => NULL,
1414 X_CREATE_METHOD => 'CREATE_APPL_INSTANCE',
1415 X_ENDED_BY => NULL,
1416 X_END_METHOD => NULL,
1417 X_CC_VERSION_NUMBER => NULL,
1418 X_NCC_VERSION_NUMBER => p_Crv_Version_Number,
1419 X_Org_Id => l_org_id,
1420 X_EMPLMNT_CATEGORY_CODE => NULL
1421 );
1422
1423 -- end apadegal adtd001 igs.m
1424 -- Foll logic added for 2229679 to raise the business event in workflow
1425
1426 IF v_acai_sequence_number IS NOT NULL THEN
1427 -- Application Instance has been successfully created and the business event needs to be raised
1428
1429 OPEN get_app_source(p_app_source_id);
1430 FETCH get_app_source INTO v_app_source;
1431 CLOSE get_app_source;
1432
1433 IF v_app_source IN ('WEB_APPL','WEB_STAFF') THEN -- in case the application is created via Self Service then raise the event
1434
1435 igs_ad_wf_001.wf_raise_event(p_person_id => p_person_id,
1436 p_raised_for => 'SAC'
1437 );
1438 END IF;
1439 END IF;
1440
1441 -- Changes for 2229679 end here
1442
1443 RETURN TRUE;
1444 END;
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447
1448 logDetail('insert_adm_appl_prog_inst'|| 'Exception from insert_adm_appl_prog_inst: '||SQLERRM,null);
1449 l_sc_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
1450 WHILE l_sc_msg_count <> 0 loop
1451 igs_ge_msg_stack.get(l_sc_msg_count, 'T', l_sc_encoded_text, l_sc_msg_index);
1452 fnd_message.parse_encoded(l_sc_encoded_text, l_sc_app_short_name, l_sc_message_name);
1453 IF l_sc_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_sc_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
1454 --Raise the exception to Higher Level with out setting any Unhandled exception.
1455 p_message_name := 'IGS_SC_POLICY_EXCEPTION';
1456 App_Exception.Raise_Exception;
1457 END IF;
1458 l_sc_msg_count := l_sc_msg_count - 1;
1459 END LOOP;
1460
1461 IF FND_MSG_PUB.Count_Msg < 1 THEN
1462 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1463 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_014.insert_adm_appl_prog_inst -'||SQLERRM);
1464 p_message_name := 'IGS_GE_UNHANDLED_EXP';
1465 IF (p_log = 'Y') THEN
1466 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
1467 END IF;
1468 IGS_GE_MSG_STACK.ADD;
1469 END IF;
1470 App_Exception.Raise_Exception;
1471
1472 END insert_adm_appl_prog_inst;
1473
1474 FUNCTION validate_unit_sets(
1475 p_unit_set_cd IN VARCHAR2 ,
1476 p_us_version_number IN NUMBER ,
1477 p_course_cd IN VARCHAR2 ,
1478 p_crv_version_number IN NUMBER ,
1479 p_acad_cal_type IN VARCHAR2 ,
1480 p_location_cd IN VARCHAR2 ,
1481 p_attendance_mode IN VARCHAR2 ,
1482 p_attendance_type IN VARCHAR2 ,
1483 p_admission_cat IN VARCHAR2 ,
1484 p_offer_ind IN VARCHAR2,
1485 p_unit_set_appl IN VARCHAR2 ,
1486 p_message_name OUT NOCOPY VARCHAR2 ,
1487 p_error_code OUT NOCOPY VARCHAR2,
1488 p_return_type OUT NOCOPY VARCHAR2 )
1489 RETURN BOOLEAN
1490 IS
1491 /*************************************************************************************************************************
1492 Created By: [email protected]
1493 Date Created : 12-27-2001
1494 Purpose:
1495 -- validate_unit_sets
1496 -- Validate the admission course application unit set.
1497 -- Validations are:
1498 -- unit set may only be specified when the 'unit set' step exists for the
1499 -- admission process category.
1500 -- If the unit set is offered then both the unit set and unit set version must
1501 -- be specified.
1502 -- IF the unit SET IS nominated THEN the unit SET status must be
1503 -- Active, however, if the unit set is OFFERED the unit set status must be
1504 -- Active.
1505 -- The expiry date of the unit set must not be set.
1506 -- The unit set must be mapped to the course offering option (this is an error
1507 -- on offer but a warning on nomination).
1508 -- The course offering option unit set must be valid for the admission category
1509 -- of the admission application.
1510 Known limitations,enhancements,remarks:
1511 Change History
1512 Who When What
1513 ***************************************************************************************************************************/
1514 cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
1515 cst_yes CONSTANT VARCHAR2(1) := 'Y';
1516 cst_error CONSTANT VARCHAR2(1) := 'E';
1517 cst_warn CONSTANT VARCHAR2(1) := 'W';
1518 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
1519 v_expiry_dt IGS_EN_UNIT_SET.expiry_dt%TYPE;
1520 v_message_name VARCHAR2(30);
1521 v_coousv_match BOOLEAN DEFAULT FALSE;
1522 v_coacus_match BOOLEAN DEFAULT FALSE;
1523 v_coacus_rec_found BOOLEAN DEFAULT FALSE;
1524
1525 CURSOR c_us_uss (
1526 cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE,
1527 cp_us_version_number IGS_EN_UNIT_SET.version_number%TYPE) IS
1528 SELECT uss.s_unit_set_status,
1529 us.expiry_dt
1530 FROM IGS_EN_UNIT_SET us,
1531 IGS_EN_UNIT_SET_STAT uss
1532 WHERE us.unit_set_cat IN
1533 ( SELECT usc.unit_set_cat
1534 FROM igs_en_unit_set_cat usc
1535 WHERE (fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND' ) <> 'Y'
1536 OR usc.s_unit_set_cat = 'PRENRL_YR') )
1537 AND us.UNIT_SET_STATUS = uss.UNIT_SET_STATUS
1538 AND us.unit_set_cd = cp_unit_set_cd
1539 AND us.version_number = cp_us_version_number;
1540
1541
1542 CURSOR c_coousv (
1543 cp_unit_set_cd IGS_PS_OF_OPT_UNT_ST.unit_set_cd%TYPE,
1544 cp_us_version_number IGS_PS_OF_OPT_UNT_ST.us_version_number%TYPE,
1545 cp_course_cd IGS_PS_OF_OPT_UNT_ST.course_cd%TYPE,
1546 cp_crv_version_number IGS_PS_OF_OPT_UNT_ST.crv_version_number%TYPE,
1547 cp_acad_cal_type IGS_PS_OF_OPT_UNT_ST.cal_type%TYPE,
1548 cp_admission_cat IGS_PS_COO_AD_UNIT_S.admission_cat%TYPE,
1549 cp_location_cd IGS_PS_COO_AD_UNIT_S.location_cd%TYPE,
1550 cp_attendance_mode IGS_PS_COO_AD_UNIT_S.attendance_mode%TYPE,
1551 cp_attendance_type IGS_PS_COO_AD_UNIT_S.attendance_type%TYPE) IS
1552 SELECT 1
1553 FROM IGS_PS_OFR_OPT_UNIT_SET_V psusv
1554 WHERE psusv.course_cd = cp_course_cd
1555 AND psusv.crv_version_number = cp_crv_version_number
1556 AND psusv.unit_set_cd = cp_unit_set_cd
1557 AND psusv.us_version_number = cp_us_version_number
1558 AND psusv.cal_type = cp_acad_cal_type
1559 AND psusv.location_cd = NVL(cp_location_cd, psusv.location_cd)
1560 AND psusv.attendance_mode = NVL(cp_attendance_mode, psusv.attendance_mode)
1561 AND psusv.attendance_type = NVL(cp_attendance_type, psusv.attendance_type)
1562 AND NOT EXISTS
1563 (SELECT 1
1564 FROM igs_ps_coo_ad_unit_s psus
1565 WHERE psus.course_cd = psusv.course_cd
1566 AND psus.crv_version_number = psusv.crv_version_number
1567 AND psus.cal_type = psusv.cal_type
1568 AND psus.location_cd = psusv.location_cd
1569 AND psus.attendance_mode = psusv.attendance_mode
1570 AND psus.attendance_type = psusv.attendance_type
1571 AND psus.admission_cat = cp_admission_cat
1572 )
1573 AND psusv.UNIT_SET_STATUS IN
1574 (SELECT unit_set_status
1575 FROM igs_en_unit_set_stat uss
1576 WHERE psusv.unit_set_status = uss.unit_set_status
1577 AND uss.s_unit_set_status <> 'INACTIVE'
1578 )
1579 AND psusv.unit_set_cat IN
1580 (SELECT usc.unit_set_cat
1581 FROM igs_en_unit_set_cat usc
1582 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
1583 OR usc.s_unit_set_cat = 'PRENRL_YR'))
1584 )
1585 AND psusv.expiry_dt IS NULL
1586 UNION
1587 SELECT 1
1588 FROM igs_ps_coo_ad_unit_s psus,
1589 igs_en_unit_set us
1590 WHERE us.unit_set_cd = psus.unit_set_cd
1591 AND us.version_number = psus.us_version_number
1592 AND psus.unit_set_cd = cp_unit_set_cd
1593 AND psus.us_version_number = cp_us_version_number
1594 AND psus.course_cd = cp_course_cd
1595 AND psus.crv_version_number = cp_crv_version_number
1596 AND psus.cal_type = cp_acad_cal_type
1597 AND psus.location_cd = NVL(cp_location_cd, psus.location_cd)
1598 AND psus.attendance_mode = NVL(cp_attendance_mode, psus.attendance_mode)
1599 AND psus.attendance_type = NVL(cp_attendance_type, psus.attendance_type)
1600 AND psus.admission_cat = cp_admission_cat
1601 AND us.unit_set_status IN
1602 (SELECT unit_set_status
1603 FROM igs_en_unit_set_stat uss
1604 WHERE us.unit_set_status = uss.unit_set_status
1605 AND uss.s_unit_set_status <> 'INACTIVE'
1606 )
1607 AND us.unit_set_cat IN
1608 (SELECT usc.unit_set_cat
1609 FROM igs_en_unit_set_cat usc
1610 WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
1611 OR usc.s_unit_set_cat = 'PRENRL_YR'))
1612 )
1613 AND us.expiry_dt IS NULL;
1614
1615 CURSOR c_coacus (
1616 cp_course_cd IGS_PS_COO_AD_UNIT_S.course_cd%TYPE,
1617 cp_crv_version_number IGS_PS_COO_AD_UNIT_S.crv_version_number%TYPE,
1618 cp_acad_cal_type IGS_PS_COO_AD_UNIT_S.cal_type%TYPE,
1619 cp_admission_cat IGS_PS_COO_AD_UNIT_S.admission_cat%TYPE,
1620 cp_location_cd IGS_PS_COO_AD_UNIT_S.location_cd%TYPE,
1621 cp_attendance_mode IGS_PS_COO_AD_UNIT_S.attendance_mode%TYPE,
1622 cp_attendance_type IGS_PS_COO_AD_UNIT_S.attendance_type%TYPE) IS
1623 SELECT coacus.unit_set_cd,
1624 coacus.us_version_number,
1625 coacus.location_cd,
1626 coacus.attendance_mode,
1627 coacus.attendance_type
1628 FROM IGS_PS_COO_AD_UNIT_S coacus
1629 WHERE coacus.course_cd = cp_course_cd AND
1630 coacus.crv_version_number = cp_crv_version_number AND
1631 coacus.cal_type = cp_acad_cal_type AND
1632 coacus.admission_cat = cp_admission_cat AND
1633 coacus.location_cd = cp_location_cd AND
1634 coacus.attendance_mode = cp_attendance_mode AND
1635 coacus.attendance_type = cp_attendance_type;
1636
1637 BEGIN
1638 logHeader('validate_unit_sets',null);
1639
1640 -- Initialise out NOCOPY parameters
1641 p_message_name := NULL;
1642 p_return_type := NULL;
1643 IF NVL(p_unit_set_appl,'N') = 'N' THEN
1644 -- Ensure the unit set details are not specified for an admission application
1645 -- that does not allow unit sets.
1646 IF p_unit_set_cd IS NOT NULL OR
1647 p_us_version_number IS NOT NULL THEN
1648 p_message_name := 'IGS_AD_UNITSET_NOTSPECIFIED' ;
1649 p_return_type := cst_error;
1650 RETURN FALSE;
1651 END IF;
1652 ELSE -- unit sets are allowed for the application.
1653 IF p_unit_set_cd IS NOT NULL AND
1654 p_us_version_number IS NOT NULL THEN
1655 -- Retrieve unit set data
1656 OPEN c_us_uss(
1657 p_unit_set_cd,
1658 p_us_version_number);
1659 FETCH c_us_uss INTO v_s_unit_set_status,
1660 v_expiry_dt;
1661 IF(c_us_uss%FOUND) THEN
1662 -- Validate unit set status
1663 IF NVL(p_offer_ind,'N') = cst_yes THEN -- Offered
1664 IF v_s_unit_set_status <> cst_active THEN
1665 CLOSE c_us_uss;
1666 p_message_name := 'IGS_AD_UNITSET_MUSTBE_ACTIVE';
1667 p_return_type := cst_error;
1668 RETURN FALSE;
1669 END IF;
1670 ELSE
1671 -- Nominated
1672 IF v_s_unit_set_status <> cst_active THEN --removed the planned status as per bug#2722785 --rghosh
1673 CLOSE c_us_uss;
1674 p_message_name := 'IGS_AD_UNITSET_ACTIVE_PLANNED';
1675 p_return_type := cst_error;
1676 RETURN FALSE;
1677 END IF;
1678 END IF;
1679 -- Validate expiry date
1680 IF v_expiry_dt IS NOT NULL THEN
1681 CLOSE c_us_uss;
1682 p_message_name := 'IGS_AD_UNITSET_EXPDT_NOTBESET';
1683 p_return_type := cst_error;
1684 RETURN FALSE;
1685 END IF;
1686 -- Validate that unit set is mapped to the course offering option.
1687 -- If the option details of the course offering option are specified,
1688 -- then an exact match must be found. If the option details are not
1689 -- specified then a match on the course offering and unit set is all
1690 -- that is needed.
1691 FOR v_coousv_rec IN c_coousv (
1692 p_unit_set_cd,
1693 p_us_version_number,
1694 p_course_cd,
1695 p_crv_version_number,
1696 p_acad_cal_type,
1697 p_admission_cat,
1698 p_location_cd,
1699 p_attendance_mode,
1700 p_attendance_type
1701 ) LOOP
1702 v_coousv_match := TRUE;
1703 END LOOP;
1704 IF(v_coousv_match = FALSE) THEN
1705 IF NVL(p_offer_ind,'N') = cst_yes THEN -- Offered
1706 CLOSE c_us_uss;
1707 p_message_name := 'IGS_AD_UNITSET_NOTMAP_POO';
1708 p_return_type := cst_error;
1709 RETURN FALSE;
1710 ELSE
1711 -- Nominated
1712 CLOSE c_us_uss;
1713 p_message_name := 'IGS_AD_UNITSET_NOT_MAP_POO';
1714 p_return_type := cst_warn;
1715 RETURN FALSE;
1716 END IF;
1717 END IF;
1718 -- Validate the course offering option unit set is mapped to the admission
1719 -- category.
1720 -- This is a restriction table. If no records exist on the table for the
1721 -- course offering option, then the course offering option unit set is valid
1722 -- for the admission category. However, if any record exists on the table
1723 -- for the course offering option then one must exist for the unit set.
1724 FOR v_coacus_rec IN c_coacus(
1725 p_course_cd,
1726 p_crv_version_number,
1727 p_acad_cal_type,
1728 p_admission_cat,
1729 p_location_cd,
1730 p_attendance_mode,
1731 p_attendance_type) LOOP
1732 v_coacus_rec_found := TRUE;
1733 IF ((p_unit_set_cd = v_coacus_rec.unit_set_cd) AND
1734 (p_us_version_number = v_coacus_rec.us_version_number) AND
1735 (p_location_cd IS NULL OR
1736 p_location_cd = v_coacus_rec.location_cd) AND
1737 (p_attendance_mode IS NULL OR
1738 p_attendance_mode = v_coacus_rec.attendance_mode) AND
1739 (p_attendance_type IS NULL OR
1740 p_attendance_type = v_coacus_rec.attendance_type)) THEN
1741 v_coacus_match := TRUE;
1742 END IF;
1743 END LOOP;
1744 IF(v_coacus_rec_found = TRUE AND
1745 v_coacus_match = FALSE) THEN
1746 IF NVL(p_offer_ind,'N') = cst_yes THEN -- Offered
1747 CLOSE c_us_uss;
1748 p_message_name := 'IGS_AD_OFRPRG_NOT_VALID';
1749 p_return_type := cst_error;
1750 RETURN FALSE;
1751 ELSE -- Nominated
1752 CLOSE c_us_uss;
1753 p_message_name := 'IGS_AD_PRGOFOP_NOT_VALID';
1754 p_return_type := cst_warn;
1755 RETURN FALSE;
1756 END IF;
1757 END IF;
1758 IF v_coacus_rec_found = FALSE THEN
1759 -- Validate the unit set.
1760 logDetail('Before Call to IGS_AD_VAL_ACAI.crsp_val_cacus_sub',null);
1761 IF IGS_AD_VAL_ACAI.crsp_val_cacus_sub (
1762 p_course_cd,
1763 p_crv_version_number,
1764 p_acad_cal_type,
1765 p_unit_set_cd,
1766 p_us_version_number,
1767 v_message_name) = FALSE THEN
1768 CLOSE c_us_uss;
1769 p_message_name := v_message_name;
1770 p_return_type := cst_error;
1771 RETURN FALSE;
1772 END IF;
1773 END IF;
1774 CLOSE c_us_uss;
1775 ELSE -- unit set record not found.
1776 CLOSE c_us_uss;
1777 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1778 p_return_type := cst_error;
1779 RETURN FALSE;
1780 END IF;
1781 ELSE -- unit set is not specified.
1782 IF NVL(p_offer_ind,'N') = cst_yes THEN
1783 p_message_name := 'IGS_AD_UNITSET_MUSTBE_SPECIFI';
1784 p_return_type := cst_error;
1785 RETURN FALSE;
1786 END IF;
1787 END IF;
1788 END IF;
1789 RETURN TRUE;
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 logDetail('validate_unit_sets' || ' is not successul Message : ' || SQLERRM,null);
1793 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1794 App_Exception.Raise_Exception;
1795 END validate_unit_sets;
1796
1797 PROCEDURE get_adm_step_values(p_admission_cat IN VARCHAR2,
1798 p_s_admission_process_type IN VARCHAR2,
1799 p_location_cd_ind OUT NOCOPY VARCHAR2,
1800 p_attendance_type_ind OUT NOCOPY VARCHAR2,
1801 p_attendance_mode_ind OUT NOCOPY VARCHAR2) IS
1802 /*************************************************************************************************************************
1803 Created By: [email protected]
1804 Date Created : 12-27-2001
1805 Purpose:
1806 1.get location_id
1807 2.get attendance_mode
1808 3.get attendance_type depending on s_admission_step_type and admission category and
1809 Known limitations,enhancements,remarks:
1810 Change History
1811 Who When What
1812 ***************************************************************************************************************************/
1813
1814 l_var VARCHAR2(1);
1815 BEGIN
1816 logHeader('get_adm_step_values',null);
1817 BEGIN
1818 SELECT 'X'
1819 INTO L_VAR
1820 FROM
1821 IGS_AD_PRCS_CAT_STEP
1822 WHERE
1823 S_ADMISSION_STEP_TYPE = 'UN-CRS-LOC'
1824 AND ADMISSION_CAT = p_admission_cat
1825 AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
1826 AND step_group_type <> 'TRACK'; --2404377
1827 p_location_cd_ind:= 'Y';
1828
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 p_location_cd_ind:=NULL;
1832 END;
1833 BEGIN
1834 SELECT 'X'
1835 INTO L_VAR
1836 FROM
1837 IGS_AD_PRCS_CAT_STEP
1838 WHERE
1839 S_ADMISSION_STEP_TYPE = 'UN-CRS-MOD'
1840 AND ADMISSION_CAT = p_admission_cat
1841 AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
1842 AND step_group_type <> 'TRACK' ; --2402377
1843 p_attendance_mode_ind := 'Y' ;
1844
1845 EXCEPTION
1846 WHEN OTHERS THEN
1847 p_attendance_mode_ind :=NULL;
1848 END;
1849 BEGIN
1850 SELECT 'X'
1851 INTO L_VAR
1852 FROM
1853 IGS_AD_PRCS_CAT_STEP
1854 WHERE
1855
1856 S_ADMISSION_STEP_TYPE = 'UN-CRS-MOD'
1857 AND ADMISSION_CAT = p_admission_cat
1858 AND S_ADMISSION_PROCESS_TYPE = p_s_admission_process_type
1859 AND STEP_GROUP_TYPE <> 'TRACK'; -- 2402377
1860 p_attendance_type_ind := 'Y';
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 p_attendance_type_ind := NULL;
1864 END;
1865 END get_adm_step_values;
1866
1867 --This is a Debug Mode Proc, outputs which proc is being processed, to stop Debugging pass 'H' to p_mode param
1868 PROCEDURE logHeader(p_proc_name VARCHAR2
1869 ,p_mode VARCHAR2 ) AS
1870 BEGIN
1871
1872 IF NVL(p_mode,'S') = 'S' THEN
1873 -- FND_FILE.PUT_LINE(FND_FILE.LOG,p_proc_name);
1874 --dbms_output.put_line('*****************Inside Proc: '||p_proc_name||' **********************');
1875 null; --Last line commented for GSCC Check, uncomment to use logHeader
1876 ELSIF p_mode = 'H' THEN
1877 NULL;
1878 END IF;
1879 END;
1880
1881 --This is a Debug Mode Proc, outputs each call being processed, to stop Debugging pass 'H' to p_mode param
1882 PROCEDURE logDetail(p_debug_msg VARCHAR2
1883 ,p_mode VARCHAR2) AS
1884 BEGIN
1885 IF NVL(p_mode,'S') = 'S' THEN
1886 -- FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_msg);
1887 --dbms_output.put_line(p_debug_msg);
1888 null; --Last line commented for GSCC Check, uncomment to use logDetail
1889 ELSIF p_mode = 'H' THEN
1890 NULL;
1891 END IF;
1892 END;
1893
1894 PROCEDURE auto_assign_requirement(
1895 p_person_id IN NUMBER,
1896 p_admission_appl_number IN NUMBER,
1897 p_course_cd IN VARCHAR2,
1898 p_sequence_number IN NUMBER,
1899 p_called_from IN VARCHAR2,
1900 p_error_text OUT NOCOPY VARCHAR2,
1901 p_error_code OUT NOCOPY NUMBER
1902 ) AS
1903 l_errbuf VARCHAR2(2000);
1904 l_retcode NUMBER;
1905
1906 CURSOR c_adm_cat(cp_person_id IN NUMBER, cp_admission_Appl_number IN NUMBER) IS
1907 SELECT ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE FROM IGS_AD_APPL_ALL
1908 WHERE PERSON_ID = cp_person_id AND ADMISSION_APPL_NUMBER = cp_admission_Appl_number;
1909
1910 l_adm_cat_rec c_adm_cat%ROWTYPE;
1911
1912 CURSOR c_apc_step_included (cp_adm_pro_type IN VARCHAR2, cp_admission_cat IN VARCHAR2,cp_admission_step_type IN VARCHAR2) IS
1913 SELECT '1' FROM IGS_AD_PRCS_CAT_STEP_ALL
1914 WHERE ADMISSION_CAT = cp_admission_cat
1915 AND S_ADMISSION_PROCESS_TYPE =cp_adm_pro_type
1916 AND S_ADMISSION_STEP_TYPE = cp_admission_step_type;
1917
1918 l_apc_step_included VARCHAR2(1);
1919
1920 BEGIN
1921
1922 OPEN c_adm_cat (p_person_id,p_admission_appl_number);
1923 FETCH c_adm_cat INTO l_adm_cat_rec;
1924 CLOSE c_adm_cat;
1925
1926 --Check whether assign requimrent step is included for import/ss or not. IF included then call
1927 --assign requirment procedure
1928 l_apc_step_included := NULL;
1929 IF p_called_from ='SS' THEN--IF it is called from Self Service
1930 OPEN c_apc_step_included (l_adm_cat_rec.S_ADMISSION_PROCESS_TYPE,l_adm_cat_rec.admission_cat,'ASSI-REQ-SS');
1931 FETCH c_apc_step_included INTO l_apc_step_included;
1932 CLOSE c_apc_step_included;
1933 ELSIF p_called_from ='IM' THEN--IF it is called from Import
1934 IF igs_ad_gen_016.get_apcs(l_adm_cat_rec.admission_cat, l_adm_cat_rec.S_ADMISSION_PROCESS_TYPE,'ASSI-REQ-IMPORT')='TRUE' THEN
1935 l_apc_step_included :='1';
1936 ELSE
1937 l_apc_step_included :=NULL;
1938 END IF;
1939 ELSE
1940 l_apc_step_included := NULL;
1941 END IF;
1942
1943 p_error_text := NULL;
1944 p_error_code := NULL;
1945
1946 IF l_apc_step_included IS NOT NULL THEN
1947 --Assign the requirment to Aplication
1948 igs_ad_adm_req.ini_adm_trk_itm(
1949 errbuf => l_errbuf,
1950 retcode => l_retcode,
1951 p_person_id => p_person_id,
1952 p_calendar_details => NULL,
1953 p_admission_process_category => NULL,
1954 p_admission_appl_number => p_admission_appl_number,
1955 p_program_code => p_course_cd,
1956 p_sequence_number => p_sequence_number,
1957 p_person_id_group => NULL,
1958 p_requirements_type => 'ADM_PROCESSING',
1959 p_originator_person => p_person_id,
1960 p_org_id => NULL
1961 );
1962 p_error_text := l_errbuf;
1963 p_error_code := l_retcode;
1964 IF l_retcode = 0 THEN--raise the Business Event oracle.apps.igs.ad.app_requirement.
1965 --Call the admission tracking item completion procedure to Aplication
1966 igs_ad_ti_comp.upd_trk_itm_st(
1967 errbuf => l_errbuf,
1968 retcode => l_retcode,
1969 p_person_id => p_person_id,
1970 p_person_id_group => NULL,
1971 p_admission_appl_number => p_admission_appl_number,
1972 p_course_cd => p_course_cd,
1973 p_sequence_number => p_sequence_number,
1974 p_calendar_details => NULL,
1975 p_admission_process_category => NULL,
1976 p_org_id => NULL
1977 );
1978
1979 igs_ad_ac_comp.upd_apl_cmp_st(
1980 ERRBUF => l_errbuf,
1981 RETCODE => l_retcode,
1982 p_person_id => p_person_id,
1983 p_person_id_group => NULL,
1984 p_admission_appl_number => p_admission_appl_number,
1985 p_course_cd => p_course_cd,
1986 p_sequence_number => p_sequence_number,
1987 p_calendar_details => NULL,
1988 p_admission_process_category => NULL,
1989 p_org_id => NULL
1990 );
1991
1992 END IF;
1993
1994 END IF;
1995 END auto_assign_requirement;
1996
1997 PROCEDURE assign_qual_type (
1998 p_person_id IN NUMBER,
1999 p_admission_appl_number IN NUMBER,
2000 p_course_cd IN VARCHAR2,
2001 p_sequence_number IN NUMBER
2002 ) IS
2003
2004 l_rowid ROWID;
2005 CURSOR c_adm_cat(cp_person_id IN NUMBER, cp_admission_Appl_number IN NUMBER) IS
2006 SELECT ADMISSION_CAT, S_ADMISSION_PROCESS_TYPE FROM IGS_AD_APPL_ALL
2007 WHERE PERSON_ID = cp_person_id AND ADMISSION_APPL_NUMBER = cp_admission_Appl_number;
2008
2009 l_adm_cat_rec c_adm_cat%ROWTYPE;
2010
2011 CURSOR c_apc_qual_type (cp_admission_cat IN VARCHAR2, cp_admission_process_type IN VARCHAR2)
2012 IS
2013 SELECT QUALIFYING_TYPE_CODE
2014 FROM IGS_AD_QUAL_TYPE
2015 WHERE ADMISSION_CAT = cp_admission_cat AND S_ADMISSION_PROCESS_TYPE = cp_admission_process_type
2016 AND CLOSED_FLAG = 'N';
2017
2018 CURSOR c_def_qual_code(cp_qual_type IN VARCHAR2)
2019 IS
2020 SELECT CODE_ID FROM IGS_AD_CODE_CLASSES
2021 WHERE CLASS=cp_qual_type AND CLASS_TYPE_CODE = 'IGS_AD_QUAL_TYPE'
2022 AND SYSTEM_DEFAULT = 'Y';
2023
2024 l_def_qual_code IGS_AD_CODE_CLASSES.CODE_ID%TYPE;
2025
2026 BEGIN
2027
2028 OPEN c_adm_cat(p_person_id,p_admission_appl_number);
2029 FETCH c_adm_cat INTO l_adm_cat_rec;
2030 CLOSE c_adm_cat;
2031
2032 FOR l_apc_qual_type_rec IN c_apc_qual_type(l_adm_cat_rec.ADMISSION_CAT,l_adm_cat_rec.S_ADMISSION_PROCESS_TYPE) LOOP
2033 --Get system default qual code for this qual type.
2034 l_def_qual_code := NULL;
2035 OPEN c_def_qual_code(l_apc_qual_type_rec.QUALIFYING_TYPE_CODE);
2036 FETCH c_def_qual_code INTO l_def_qual_code;
2037 CLOSE c_def_qual_code;
2038 igs_ad_appqual_code_pkg.INSERT_ROW
2039 (
2040 x_rowid => l_rowid,
2041 x_person_id => p_person_id,
2042 x_admission_appl_number => p_admission_appl_number,
2043 x_nominated_course_cd => p_course_cd,
2044 x_sequence_number => p_sequence_number,
2045 x_qualifying_type_code => l_apc_qual_type_rec.QUALIFYING_TYPE_CODE,
2046 x_qualifying_code_id => l_def_qual_code,
2047 x_qualifying_value => NULL,
2048 x_mode => 'R'
2049 );
2050
2051 END LOOP;
2052
2053 END assign_qual_type;
2054
2055 END IGS_AD_GEN_014;