[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_DEF_APPL_PKG
Source
1 PACKAGE BODY igs_ad_def_appl_pkg AS
2 /* $Header: IGSADC1B.pls 120.8 2006/05/29 12:25:04 apadegal ship $ */
3
4 FUNCTION get_person_number ( p_person_id hz_parties.party_id%TYPE ) RETURN VARCHAR2 IS
5
6 CURSOR c_person_num IS
7 SELECT party_number
8 FROM hz_parties hp
9 WHERE hp.party_id = p_person_id;
10
11 lv_person_number hz_parties.party_number%TYPE DEFAULT NULL;
12 BEGIN
13
14 OPEN c_person_num;
15 FETCH c_person_num INTO lv_person_number;
16 CLOSE c_person_num;
17
18 RETURN lv_person_number;
19 END get_person_number;
20
21 /*******************************************************************************
22 Created by : Ramesh Rengarajan
23 Date created: 06 AUG 2002
24
25 Purpose:
26 To create deferred term application
27
28 Known limitations/enhancements and/or remarks:
29
30 Change History: (who, when, what: )
31 Who When What
32 nsinha 15-Nov-2002 Bug :2664410 - Release Of Build For Adcr049 - Alternate Application Id.
33 * In Function handle_application:
34 * Passed p_alt_appl_id parameter to igs_ad_gen_014.insert_adm_appl
35 *******************************************************************************/
36 PROCEDURE admp_val_offer_defer_term(
37 errbuf out NOCOPY varchar2,
38 retcode out NOCOPY number ,
39 p_person_id hz_parties.party_id%TYPE,
40 p_group_id igs_pe_persid_group.group_id%TYPE,
41 p_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%TYPE,
42 p_prev_acad_adm_cal VARCHAR2,
43 p_def_acad_adm_cal VARCHAR2,
44 p_offer_dt VARCHAR2,
45 p_offer_response_dt VARCHAR2
46 ) IS
47 /*******************************************************************************
48 Created by : Ramesh Rengarajan
49 Date created: 06 SEP 2002
50
51 Purpose:
52 To create deferred term application
53
54 Known limitations/enhancements and/or remarks:
55
56 Change History: (who, when, what: )
57 Who When What
58 *******************************************************************************/
59
60 -- Variable Declarations ----------------------------------------------------------------------------------------------------
61
62 -- Resolve the parameters to get calendar types and sequence numbers
63 p_prev_acad_cal_type igs_ad_appl.acad_cal_type%TYPE ;
64 p_prev_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE;
65 p_prev_adm_cal_type igs_ad_appl.adm_cal_type%TYPE ;
66 p_prev_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE ;
67
68 p_def_acad_cal_type igs_ad_appl.acad_cal_type%TYPE ;
69 p_def_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE;
70 p_def_adm_cal_type igs_ad_appl.adm_cal_type%TYPE ;
71 p_def_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE ;
72
73 l_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE;
74 l_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE;
75
76 l_message_name VARCHAR2(1000);
77 l_new_admission_appl_number igs_ad_appl.admission_appl_number%TYPE;
78 l_new_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
79 cst_deferral CONSTANT VARCHAR2(9) := 'DEFERRAL';
80 cst_approved CONSTANT VARCHAR2(9) := 'APPROVED';
81
82 l_person_number hz_parties.party_number%TYPE;
83 l_group_desc igs_pe_persid_group.description%TYPE;
84
85
86 ---End Variable Declarations--------------------------------------------------------------------------------------------
87
88 -------------Cursor Declarations ----------------------------------------------------------------------------------------
89
90
91 CURSOR c_pernum_cur IS
92 SELECT
93 person_number
94 FROM
95 igs_pe_person_base_v
96 WHERE
97 person_id = p_person_id;
98
99
100 CURSOR c_pergr_cur IS
101 SELECT
102 description
103 FROM
104 igs_pe_persid_group
105 WHERE
106 group_id = p_group_id;
107
108 -- Cursor for getting the alternade academic/admission code for a given academic/admission cal type and sequence number
109
110 CURSOR acad_adm_alt_code (
111 p_acad_cal_type igs_ca_inst.cal_type%TYPE,
112 p_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
113 p_adm_cal_type igs_ca_inst.cal_type%TYPE,
114 p_adm_ci_sequence_number igs_ca_inst.sequence_number%TYPE
115 ) IS
116 SELECT c1.alternate_code||' / '||c2.alternate_code
117 FROM igs_ca_inst c1, igs_ca_inst c2
118 WHERE c1.cal_type = p_acad_cal_type
119 AND c1.sequence_number = p_acad_ci_sequence_number
120 AND c2.cal_type = p_adm_cal_type
121 AND c2.sequence_number = p_adm_ci_sequence_number;
122
123 cur_acad_adm_alt_code VARCHAR2(200);
124 def_acad_adm_alt_code VARCHAR2(200);
125
126 -- Cursor to get the current application attributes -----
127 CURSOR c_appl_inst(p_person_id hz_parties.party_id%TYPE) IS
128 SELECT
129 acai.*
130 FROM
131 igs_ad_appl aa,
132 igs_ad_ps_appl_inst acai, /* Replaced igs_ad_ps_appl_inst_aplinst_v with base tables Bug 3150054 */
133 igs_ad_ofr_resp_stat aous,
134 igs_ad_ofrdfrmt_stat aods
135 WHERE
136 aa.person_id = acai.person_id
137 AND aa.admission_appl_number = acai.admission_appl_number
138 AND acai.adm_offer_resp_status = aous.adm_offer_resp_status
139 AND aous.s_adm_offer_resp_status = cst_deferral
140 AND acai.def_acad_cal_type = NVL(p_def_acad_cal_type, acai.def_acad_cal_type)
141 AND acai.def_acad_ci_sequence_num = NVL(p_def_acad_cal_seq_no, acai.def_acad_ci_sequence_num)
142 AND acai.deferred_adm_cal_type = NVL(p_def_adm_cal_type, acai.deferred_adm_cal_type)
143 AND acai.deferred_adm_ci_sequence_num = NVL(p_def_adm_cal_seq_no, acai.deferred_adm_ci_sequence_num)
144 AND acai.def_term_adm_appl_num IS NULL
145 AND acai.def_appl_sequence_num IS NULL
146 AND acai.person_id = p_person_id
147 AND acai.nominated_course_cd = NVL (p_nominated_course_cd, acai.nominated_course_cd)
148 AND aa.acad_cal_type = NVL ( p_prev_acad_cal_type, aa.acad_cal_type)
149 AND aa.acad_ci_sequence_number = NVL ( p_prev_acad_cal_seq_no, aa.acad_ci_sequence_number)
150 AND NVL(acai.adm_cal_type,aa.adm_cal_type) = NVL( p_prev_adm_cal_type, acai.adm_cal_type)
151 AND NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) = NVL ( p_prev_adm_cal_seq_no, acai.adm_ci_sequence_number)
152 AND acai.adm_offer_dfrmnt_status = aods.adm_offer_dfrmnt_status
153 AND aods.s_adm_offer_dfrmnt_status IN ('APPROVED','CONFIRM');
154
155
156 c_appl_inst_rec c_appl_inst%ROWTYPE;
157
158 CURSOR c_group_id(p_person_id hz_parties.party_id%TYPE,p_group_id igs_pe_persid_group.group_id%TYPE) IS
159 SELECT 'X'
160 FROM igs_pe_prsid_grp_mem
161 WHERE person_id = p_person_id
162 AND group_id = p_group_id
163 AND NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
164 AND NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
165
166 l_exists VARCHAR2(1);
167 -- pfotedar bug no. 3713735
168 l_query VARCHAR2(1000);
169 TYPE c_ref_cur_typ IS REF CURSOR;
170 c_ref_cur c_ref_cur_typ;
171 TYPE c_ref_cur_rec_typ IS RECORD (person_id NUMBER);
172 c_ref_cur_rec c_ref_cur_rec_typ;
173
174 -- for bug 5245277 and sql id 17651648
175 l_status VARCHAR2(1);
176 l_group_type IGS_PE_PERSID_GROUP_V.group_type%type;
177
178 -- End cursor declarations -----------------------------------------------------------------------------------------------
179 BEGIN
180
181 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
182 igs_ge_gen_003.set_org_id(null);
183
184 p_prev_acad_cal_type := rtrim (substr (p_prev_acad_adm_cal, 1,10));
185 p_prev_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr(p_prev_acad_adm_cal, 13,7));
186 p_prev_adm_cal_type := rtrim (substr (p_prev_acad_adm_cal, 23,10));
187 p_prev_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_prev_acad_adm_cal, 35,7));
188 p_def_acad_cal_type := rtrim (substr (p_def_acad_adm_cal, 1,10));
189 p_def_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_def_acad_adm_cal, 13,7));
190 p_def_adm_cal_type := rtrim (substr (p_def_acad_adm_cal, 23,10));
191 p_def_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr (p_def_acad_adm_cal, 35,7));
192
193 -- Cursor for getting the person number --rghosh bug#2767294
194 OPEN c_pernum_cur;
195 FETCH c_pernum_cur INTO l_person_number;
196 CLOSE c_pernum_cur;
197
198 --Cursor for getting the person group --rghosh bug#2767294
199 OPEN c_pergr_cur;
200 FETCH c_pergr_cur INTO l_group_desc;
201 CLOSE c_pergr_cur;
202
203 -- added the following lines for displaying the parameters person number, person id group and program --rghosh bug #2767294
204 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Person Number',38) || ' : ' || l_person_number);
205 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Person ID Group' ,38) || ' : ' || l_group_desc);
206 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Program',38) || ' : ' || p_nominated_course_cd);
207
208 IF p_prev_acad_adm_cal IS NOT NULL THEN
209 OPEN acad_adm_alt_code( p_prev_acad_cal_type,
210 p_prev_acad_cal_seq_no,
211 p_prev_adm_cal_type,
212 p_prev_adm_cal_seq_no);
213 FETCH acad_adm_alt_code INTO cur_acad_adm_alt_code;
214 CLOSE acad_adm_alt_code;
215 END IF;
216
217 IF p_def_acad_adm_cal IS NOT NULL THEN
218 OPEN acad_adm_alt_code(p_def_acad_cal_type,
219 p_def_acad_cal_seq_no,
220 p_def_adm_cal_type,
221 p_def_adm_cal_seq_no);
222 FETCH acad_adm_alt_code INTO def_acad_adm_alt_code;
223 CLOSE acad_adm_alt_code;
224 END IF;
225
226 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Current Academic / Admission Calendar',38) || ' : ' || cur_acad_adm_alt_code );
227 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Deferred Academic / Admission Calendar',38) || ' : ' || def_acad_adm_alt_code );
228
229 l_offer_dt := igs_ge_date.igsdate(p_offer_dt);
230 l_offer_response_dt := igs_ge_date.igsdate(p_offer_response_dt);
231
232 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Offer Date',38) || ' : ' || l_offer_dt);
233 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Override Offer Response Date',38) || ' : ' || l_offer_response_dt);
234 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
235
236 -- check whether the parameter combination passed is correct
237 -- if user gives both person group and person
238 -- then it should check whether the person exists in that group
239 -- if the person doesnot exists, it gives a message --rghosh bug#2767294
240
241 IF (p_group_id IS NULL AND p_person_id IS NULL)THEN
242 fnd_file.put_line(fnd_file.log, 'Application could not be created');
243 fnd_file.put_line(fnd_file.log, 'Either Person Number or Person ID Group should be passed');
244 ELSIF (p_group_id IS NOT NULL AND p_person_id IS NOT NULL) THEN
245 OPEN c_group_id(p_person_id,p_group_id);
246 FETCH c_group_id INTO l_exists;
247 IF c_group_id%NOTFOUND THEN
248 fnd_file.put_line(fnd_file.log, 'Application could not be created');
249 fnd_file.put_line(fnd_file.log, 'Person does not exists in the Person ID Group');
250 END IF;
251 CLOSE c_group_id;
252 END IF;
253
254 -- If the user wants to do the process for list of persons
255 -- use the record group he has given in the parameter and iterate on the list
256 -- of persons in the group
257
258 IF p_group_id IS NOT NULL THEN
259
260 ---- begin bug 5245277 for sql id 17651648
261 l_query := IGS_PE_DYNAMIC_PERSID_GROUP.GET_DYNAMIC_SQL(p_group_id, l_status, l_group_type);
262
263 IF (l_query IS NOT NULL AND l_status ='S')
264 THEN
265 ---- end bug 5245277 for sql id 17651648
266
267
268 IF p_person_id IS NOT NULL THEN --When p_group_id is not null and p_person_id is not null
269
270 l_query := l_query || ' and person_id = :2'; --bug 5245277 and sql id 17651648
271 OPEN c_ref_cur FOR l_query USING p_group_id, p_person_id;
272 ELSE --When p_group_id is not null and p_person_id is null
273 OPEN c_ref_cur FOR l_query USING p_group_id;
274 END IF;
275 LOOP
276 FETCH c_ref_cur INTO c_ref_cur_rec;
277 EXIT WHEN c_ref_cur%NOTFOUND;
278 -- Find out NOCOPY the list applications
279 FOR c_appl_inst_rec IN c_appl_inst(c_ref_cur_rec.person_id) LOOP
280
281 -- UPDATE the log file with application details
282 fnd_file.put_line(fnd_file.log, 'Creating Deferred Application for ' );
283 fnd_file.put_line(fnd_file.log,RPAD( ' Person Number',29) || ' : ' || get_person_number(IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.person_id)));
284 fnd_file.put_line(fnd_file.log, RPAD(' Admission Application Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.admission_appl_number));
285 fnd_file.put_line(fnd_file.log, RPAD(' Nominated course Code',29) || ' : ' || c_appl_inst_rec.nominated_course_cd);
286 fnd_file.put_line(fnd_file.log, RPAD(' Sequence Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN( c_appl_inst_rec.sequence_number ));
287
288 -- Call cmn_handle_application which will create application, copying child record
289 -- changing the entry qualification status , completness status and offer validations and update the same
290
291 cmn_handle_application(
292 p_person_id => c_ref_cur_rec.person_id,
293 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
294 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
295 p_sequence_number => c_appl_inst_rec.sequence_number,
296 p_def_acad_cal_type => c_appl_inst_rec.def_acad_cal_type ,
297 p_def_acad_cal_seq_no => c_appl_inst_rec.def_acad_ci_sequence_num,
298 p_def_adm_cal_type => c_appl_inst_rec.deferred_adm_cal_type,
299 p_def_adm_cal_seq_no => c_appl_inst_rec.deferred_adm_ci_sequence_num,
300 p_offer_dt => l_offer_dt,
301 p_offer_response_dt => l_offer_response_dt);
302 END LOOP; -- end loop for loop application instances
303 END LOOP;
304 END IF; -- if l_query is not null and status ='S'
305 ELSE
306 -- This case will come into picture if the user has given only Person Id
307 -- Find out NOCOPY the list applications
308 FOR c_appl_inst_rec IN c_appl_inst(p_person_id) LOOP
309 fnd_file.put_line(fnd_file.log, 'Creating Deferred Application for ' );
310 fnd_file.put_line(fnd_file.log, RPAD(' Person Number',29) || ' : ' || get_person_number(IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.person_id)));
311 fnd_file.put_line(fnd_file.log, RPAD(' Admission Application Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.admission_appl_number));
312 fnd_file.put_line(fnd_file.log, RPAD(' Nominated course Code',29) || ' : ' || c_appl_inst_rec.nominated_course_cd);
313 fnd_file.put_line(fnd_file.log, RPAD(' Sequence Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN( c_appl_inst_rec.sequence_number ));
314 -- Call cmn_hanlde_applicaiton which will create application, copying child record
315 -- changing the entry qualification status , completness status and offer validations and update the same
316 cmn_handle_application(
317 p_person_id => p_person_id,
318 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
319 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
320 p_sequence_number => c_appl_inst_rec.sequence_number,
321 p_def_acad_cal_type => c_appl_inst_rec.def_acad_cal_type ,
322 p_def_acad_cal_seq_no => c_appl_inst_rec.def_acad_ci_sequence_num,
323 p_def_adm_cal_type => c_appl_inst_rec.deferred_adm_cal_type,
324 p_def_adm_cal_seq_no => c_appl_inst_rec.deferred_adm_ci_sequence_num,
325 p_offer_dt => l_offer_dt,
326 p_offer_response_dt => l_offer_response_dt);
327 END LOOP; -- end loop list of application instances
328 END IF;
329 EXCEPTION
330 WHEN OTHERS THEN
331 retcode:=2;
332 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
333 Igs_Ge_Msg_Stack.conc_exception_hndl;
334 RETURN;
335 END admp_val_offer_defer_term;
336
337 PROCEDURE cmn_handle_application
338 (
339 p_person_id hz_parties.party_id%TYPE,
343 p_def_acad_cal_type igs_ad_appl.acad_cal_type%TYPE ,
340 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
341 p_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
342 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ,
344 p_def_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE,
345 p_def_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
346 p_def_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE,
347 p_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE,
348 p_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE
349 ) IS
350
351 CURSOR cur_sys_offr_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
352 p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
353 p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
354 p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
355 SELECT aods.s_adm_offer_dfrmnt_status
356 FROM igs_ad_ps_appl_inst apai,
357 igs_ad_ofrdfrmt_stat_v aods
358 WHERE apai.person_id =p_person_id
359 AND apai.admission_appl_number =p_admission_appl_number
360 AND apai.nominated_course_cd =p_nominated_course_cd
361 AND apai.sequence_number =p_sequence_number
362 AND apai.adm_offer_dfrmnt_status = aods.adm_offer_dfrmnt_status ;
363
364 l_message_name VARCHAR2(1000);
365 l_new_admission_appl_number igs_ad_appl.admission_appl_number%TYPE;
366 l_new_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
367 l_s_adm_offer_dfrmnt_status igs_ad_ofrdfrmt_stat_v.s_adm_offer_dfrmnt_status%TYPE;
368
369 v_start_dt DATE;
370
371 CURSOR c_get_acad_cal_info(cp_person_id igs_ad_appl_all.person_id%TYPE,
372 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
373 SELECT acad_cal_type,acad_ci_sequence_number
374 FROM igs_ad_appl_all
375 WHERE person_id = cp_person_id
376 AND admission_appl_number = cp_admission_appl_number;
377
378 l_get_acad_cal_info c_get_acad_cal_info%ROWTYPE;
379
380 BEGIN
381 /*******************************************************************************
382 Created by : Ramesh Rengarajan
383 Date created: 20 FEB 2002
384
385 Purpose:
386 To Create deferred term application , copy child records, copy entry and completness status , give offer with validation
387
388 Known limitations/enhancements and/or remarks:
389
390 Change History: (who, when, what: )
391 Who When What
392
393 *******************************************************************************/
394 -- Set the save point here
395 -- Since when the application related transaction fails we need to rollback the transaction for that application
396 -- and it should proceed to the next record in the cursor
397 SAVEPOINT sp_save_point1;
398
399 -- Call handle application
400 -- this procedure returns true if the application, application program and application instance insert is success
401 -- otherwise it returns FALSE. This procedure also inserts the new application and instances for the deferred term
402 -- if the new application is created it returns the new admission application number and new sequence number as an
403 -- out NOCOPY parameter. This variables will be used for copying the child related records from the old to the new application
404 IF handle_application(
405 p_person_id => p_person_id,
406 p_admission_appl_number => p_admission_appl_number,
407 p_nominated_course_cd => p_nominated_course_cd,
408 p_sequence_number => p_sequence_number,
409 p_def_acad_cal_type => p_def_acad_cal_type ,
410 p_def_acad_cal_seq_no => p_def_acad_cal_seq_no,
411 p_def_adm_cal_type => p_def_adm_cal_type,
412 p_def_adm_cal_seq_no => p_def_adm_cal_seq_no,
413 p_new_admission_appl_number=> l_new_admission_appl_number,
414 p_new_sequence_number => l_new_sequence_number) THEN
415
416 -- if the creation is successful then copy all the child records
417 -- if any error occurs during copying of any of the child record
418 -- rollback upto save point 1 and proceed the next record in the cursor
419 -- Note : copy child records for future term application has been called
420 -- from here. Because same functionality is being done there also
421 -- If we pass p_process as 'D' , it will make all the fee details
422 -- record's applicant_fee_status to 'WAIVED'
423
424 OPEN c_get_acad_cal_info(p_person_id,l_new_admission_appl_number);
425 FETCH c_get_acad_cal_info INTO l_get_acad_cal_info;
426 CLOSE c_get_acad_cal_info;
427
428
429 v_start_dt := igs_en_gen_002.enrp_get_acad_comm(
430 l_get_acad_cal_info.acad_cal_type,
431 l_get_acad_cal_info.acad_ci_sequence_number,
432 p_person_id,
433 p_nominated_course_cd,
434 l_new_admission_appl_number,
435 p_nominated_course_cd,
436 l_new_sequence_number,
437 'Y');
438
439 IF IGS_AD_VAL_ACAI_FTR_OFFER.copy_child_records(
440 p_new_admission_appl_number => l_new_admission_appl_number,
441 p_new_sequence_number => l_new_sequence_number,
442 p_person_id => p_person_id,
443 p_old_admission_appl_number => p_admission_appl_number,
444 p_old_sequence_number => p_sequence_number,
448
445 p_nominated_course_cd => p_nominated_course_cd,
446 p_start_dt => v_start_dt,
447 p_process => 'D') THEN
449 -- if all the validations and copying is successful then update the entry and doc status from old applicaiton to new future term
450 IF copy_entrycomp_qual_status(p_person_id => p_person_id,
451 p_nominated_course_cd => p_nominated_course_cd,
452 p_admission_appl_number => p_admission_appl_number,
453 p_sequence_number => p_sequence_number,
454 p_new_admission_appl_number => l_new_admission_appl_number,
455 p_new_sequence_number => l_new_sequence_number ) THEN
456 -- do the offer validation and update the out NOCOPY come status to offer
457 IF validate_offer_validations(p_person_id => p_person_id,
458 p_nominated_course_cd => p_nominated_course_cd,
459 p_admission_appl_number => l_new_admission_appl_number,
460 p_sequence_number => l_new_sequence_number,
461 p_old_admission_appl_number => p_admission_appl_number,
462 p_old_sequence_number => p_sequence_number,
463 p_offer_dt => p_offer_dt,
464 p_offer_response_dt => p_offer_response_dt,
465 p_def_acad_cal_type => p_def_acad_cal_type,
466 p_def_acad_cal_seq_no => p_def_acad_cal_seq_no,
467 p_def_adm_cal_type => p_def_adm_cal_type,
468 p_def_adm_cal_seq_no => p_def_adm_cal_seq_no,
469 p_start_dt => v_start_dt
470 ) THEN
471 OPEN cur_sys_offr_dfrmnt_status(p_person_id,p_admission_appl_number,
472 p_nominated_course_cd,p_sequence_number);
473 FETCH cur_sys_offr_dfrmnt_status INTO l_s_adm_offer_dfrmnt_status;
474 CLOSE cur_sys_offr_dfrmnt_status;
475 IF l_s_adm_offer_dfrmnt_status = 'CONFIRM' THEN
476 IF NOT Update_offer_response_accepted(p_person_id => p_person_id,
477 p_admission_appl_number => l_new_admission_appl_number,
478 p_nominated_course_cd => p_nominated_course_cd,
479 p_sequence_number => l_new_sequence_number)
480 THEN
481
482 ROLLBACK TO sp_save_point1; -- to rollback, if response cannot be set to ACCEPTED
483 END IF;
484
485 END IF;
486 ELSE
487 ROLLBACK TO sp_save_point1;
488 END IF;
489 ELSE
490 ROLLBACK TO sp_save_point1;
491 END IF;
492 ELSE -- Else if the child record copy failed
493 ROLLBACK TO sp_save_point1;
494 END IF; -- End if for Copy Child Records
495 ELSE -- Application Creation Failed
496 ROLLBACK TO sp_save_point1;
497 END IF; -- End if for handle application
498 END cmn_handle_application;
499
500 FUNCTION handle_application(
501 p_person_id hz_parties.party_id%TYPE,
502 p_admission_appl_number igs_ad_appl.admission_appl_number%TYPE,
503 p_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%TYPE,
504 p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE,
505 p_def_acad_cal_type igs_ad_appl.acad_cal_type%TYPE,
506 p_def_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE,
507 p_def_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
508 p_def_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE,
509 p_new_admission_appl_number OUT NOCOPY igs_ad_appl.admission_appl_number%TYPE,
510 p_new_sequence_number OUT NOCOPY igs_ad_ps_appl_inst.sequence_number%TYPE
511 ) RETURN BOOLEAN IS
512 /*******************************************************************************
513 Created by : Ramesh Rengarajan
514 Date created: 20 FEB 2002
515
516 Purpose:
517 To Create deferred term application
518
519 Known limitations/enhancements and/or remarks:
520
521 Change History: (who, when, what: )
522 Who When What
523 rboddu 04-OCT-2002 Creating application with Application_Type. Bug :2599457
524 nsinha 15-Nov-2002 Bug :2664410 - Release Of Build For Adcr049 - Alternate Application Id.
525 * In Function handle_application:
526 * Passed p_alt_appl_id parameter to igs_ad_gen_014.insert_adm_appl
527 pbondugu 28-Mar-2003 Passed funding_source as NULL to procedure call IGS_AD_GEN_014.insert_adm_appl_prog_inst
528 *******************************************************************************/
529 -- Cursor Declarations-------------------------------------------------------------------
530 -- Cursor to get the applications
531 CURSOR c_appl_inst IS
532 SELECT
533 acai.*,
534 aa.appl_dt, aa.admission_cat, aa.s_admission_process_type,
535 aa.spcl_grp_1, aa.spcl_grp_2,aa.common_app,
536 aa.adm_appl_status, aa.choice_number, aa.routeb_pref,
537 aa.application_type, aa.adm_fee_status, aa.alt_appl_id,
538 aa.acad_cal_type, aa.acad_ci_sequence_number,
539 aca.admission_cd, aca.transfer_course_cd,
543 NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) final_adm_ci_sequence_number
540 aca.basis_for_admission_type,aca.req_for_reconsideration_ind,
541 aca.req_for_adv_standing_ind,
542 NVL(acai.adm_cal_type,aa.adm_cal_type) final_adm_cal_type,
544 FROM
545 igs_ad_ps_appl_inst acai, /* Replaced igs_ad_ps_appl_inst_aplinst_v with base tables Bug 3150054 */
546 igs_ad_appl aa,
547 igs_ad_ps_appl aca
548 WHERE
549 acai.admission_appl_number = p_admission_appl_number
550 AND acai.sequence_number = p_sequence_number
551 AND acai.nominated_course_cd = p_nominated_course_cd
552 AND aa.person_id = acai.person_id
553 AND aa.admission_appl_number = acai.admission_appl_number
554 AND acai.person_id = p_person_id
555 AND aca.person_id = acai.person_id
556 AND aca.admission_appl_number = acai.admission_appl_number
557 AND aca.nominated_course_cd = acai.nominated_course_cd;
558
559
560 CURSOR c_sys_def_appl_type(cp_adm_cat igs_ad_appl_all.admission_cat%TYPE,
561 cp_s_adm_prc_typ igs_ad_appl_all.s_admission_process_type%TYPE
562 )
563 IS
564 SELECT admission_application_type
565 FROM igs_ad_ss_appl_typ
566 WHERE admission_cat = cp_adm_cat
567 AND S_admission_process_type = cp_s_adm_prc_typ
568 AND System_default = 'Y'
569 AND NVL(closed_ind, 'N') <> 'Y';
570
571 -----End cursor Declarations------------------------------------------------------------------
572
573 -------------------Variable Declarations------------------------------------------------------
574 l_message_name VARCHAR2(1000);
575 l_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
576 l_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
577 l_return_type VARCHAR2(100);
578 l_error_code VARCHAR2(100);
579 l_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
580 l_admission_appl_number igs_ad_appl.admission_appl_number%TYPE;
581 l_application_type igs_ad_appl_all.application_type%TYPE;
582 ------------------- End Variable Declarations------------------------------------------------------
583 BEGIN
584 FOR c_appl_inst_rec IN c_appl_inst LOOP
585
586 l_application_type:= c_appl_inst_rec.application_type;
587 --If Application Type of existing Deferred Application is NULL, then take the System Default Application Type
588 -- And create the application with this application type. -- Added as part of 2599457
589 IF l_application_type IS NULL THEN
590 OPEN c_sys_def_appl_type(c_appl_inst_rec.admission_cat,c_appl_inst_rec.s_admission_process_type);
591 FETCH c_sys_def_appl_type INTO l_application_type;
592 CLOSE c_sys_def_appl_type;
593 END IF;
594 -- Create Admission application
595 IF igs_ad_gen_014.insert_adm_appl(
596 p_person_id => p_person_id,
597 p_appl_dt => c_appl_inst_rec.appl_dt,
598 p_acad_cal_type => p_def_acad_cal_type ,
599 p_acad_ci_sequence_number => p_def_acad_cal_seq_no ,
600 p_adm_cal_type => p_def_adm_cal_type ,
601 p_adm_ci_sequence_number => p_def_adm_cal_seq_no ,
602 p_admission_cat => c_appl_inst_rec.admission_cat,
603 p_s_admission_process_type => c_appl_inst_rec.s_admission_process_type,
604 p_adm_appl_status => igs_ad_gen_008.admp_get_sys_aas('RECEIVED'), -- Bug no 2744528 by rrengara
605 -- When ever we create an application it should be in received status
606 -- In case the deferred application has the single instance and the offer deferment status is confirmed
607 -- at save the appl processing status becomes complete.
608 -- So if the old processing status is passed to the new application, it will not create application with
609 -- complete status.
610 p_adm_fee_status => c_appl_inst_rec.adm_fee_status, --IN/OUT
611 p_tac_appl_ind => 'N',
612 p_adm_appl_number => l_admission_appl_number, --OUT
613 p_message_name => l_message_name, --OUT
614 p_spcl_grp_1 => c_appl_inst_rec.spcl_grp_1,
615 p_spcl_grp_2 => c_appl_inst_rec.spcl_grp_2,
616 p_common_app => c_appl_inst_rec.common_app,
617 p_application_type => l_application_type, -- Added as part of 2599457
618 p_choice_number => c_appl_inst_rec.choice_number,
619 p_routeb_pref => c_appl_inst_rec.routeb_pref,
620 p_alt_appl_id => c_appl_inst_rec.alt_appl_id ) = FALSE THEN
621
622 fnd_file.put_line(fnd_file.log, 'Application could not be created');
623 fnd_message.set_name('IGS', l_message_name);
624 fnd_file.put_line(fnd_file.log, fnd_message.get);
625 RETURN FALSE;
626
627 ELSE -- Else for Application
628 IF IGS_AD_GEN_014.insert_adm_appl_prog(
629 p_person_id=> p_person_id,
630 p_adm_appl_number=>l_admission_appl_number,
631 p_nominated_course_cd=>p_nominated_course_cd,
632 p_transfer_course_cd=>c_appl_inst_rec.transfer_course_cd,
633 p_basis_for_admission_type=>c_appl_inst_rec.basis_for_admission_type,
634 p_admission_cd=>c_appl_inst_rec.admission_cd,
635 p_req_for_reconsideration_ind=> c_appl_inst_rec.req_for_reconsideration_ind,
636 p_req_for_adv_standing_ind=> c_appl_inst_rec.req_for_adv_standing_ind,
637 p_message_name => l_message_name) THEN
638
639 -- Create Admission Application Porgram Instance
640 IF IGS_AD_GEN_014.insert_adm_appl_prog_inst (
641 p_person_id=>p_person_id,
642 p_admission_appl_number=>l_admission_appl_number,
646 p_adm_ci_sequence_number=>p_def_adm_cal_seq_no,
643 p_acad_cal_type=>p_def_acad_cal_type,
644 p_acad_ci_sequence_number=>p_def_acad_cal_seq_no ,
645 p_adm_cal_type=>p_def_adm_cal_type ,
647 p_admission_cat=>c_appl_inst_rec.admission_cat,
648 p_s_admission_process_type=>c_appl_inst_rec.s_admission_process_type,
649 p_appl_dt=>c_appl_inst_rec.appl_dt,
650 p_adm_fee_status=>c_appl_inst_rec.adm_fee_status,
651 p_preference_number=>c_appl_inst_rec.preference_number,
652 p_offer_dt=>NULL,
653 p_offer_response_dt=>NULL,
654 p_course_cd=>c_appl_inst_rec.nominated_course_cd,
655 p_crv_version_number=>c_appl_inst_rec.crv_version_number,
656 p_location_cd=>c_appl_inst_rec.location_cd,
657 p_attendance_mode=>c_appl_inst_rec.attendance_mode,
658 p_attendance_type=>c_appl_inst_rec.attendance_type,
659 p_unit_set_cd=>c_appl_inst_rec.unit_set_cd,
660 p_us_version_number=>c_appl_inst_rec.us_version_number,
661 p_fee_cat=>c_appl_inst_rec.fee_cat,
662 p_correspondence_cat=>c_appl_inst_rec.correspondence_cat,
663 p_enrolment_cat=>c_appl_inst_rec.enrolment_cat,
664 p_funding_source=>c_appl_inst_rec.funding_source,
665 p_edu_goal_prior_enroll=>c_appl_inst_rec.edu_goal_prior_enroll_id,
666 p_app_source_id=>c_appl_inst_rec.app_source_id,
667 p_apply_for_finaid=>c_appl_inst_rec.apply_for_finaid,
668 p_finaid_apply_date=>c_appl_inst_rec.finaid_apply_date,
669 p_attribute_category=>c_appl_inst_rec.attribute_category,
670 p_attribute1=>c_appl_inst_rec.attribute1,
671 p_attribute2=>c_appl_inst_rec.attribute2,
672 p_attribute3=>c_appl_inst_rec.attribute3,
673 p_attribute4=>c_appl_inst_rec.attribute4,
674 p_attribute5=>c_appl_inst_rec.attribute5,
675 p_attribute6=>c_appl_inst_rec.attribute6,
676 p_attribute7=>c_appl_inst_rec.attribute7,
677 p_attribute8=>c_appl_inst_rec.attribute8,
678 p_attribute9=>c_appl_inst_rec.attribute9,
679 p_attribute10=>c_appl_inst_rec.attribute10,
680 p_attribute11=>c_appl_inst_rec.attribute11,
681 p_attribute12=>c_appl_inst_rec.attribute12,
682 p_attribute13=>c_appl_inst_rec.attribute13,
683 p_attribute14=>c_appl_inst_rec.attribute14,
684 p_attribute15=>c_appl_inst_rec.attribute15,
685 p_attribute16=>c_appl_inst_rec.attribute16,
686 p_attribute17=>c_appl_inst_rec.attribute17,
687 p_attribute18=>c_appl_inst_rec.attribute18,
688 p_attribute19=>c_appl_inst_rec.attribute19,
689 p_attribute20=>c_appl_inst_rec.attribute20,
690 p_attribute21=>c_appl_inst_rec.attribute21,
691 p_attribute22=>c_appl_inst_rec.attribute22,
692 p_attribute23=>c_appl_inst_rec.attribute23,
693 p_attribute24=>c_appl_inst_rec.attribute24,
694 p_attribute25=>c_appl_inst_rec.attribute25,
695 p_attribute26=>c_appl_inst_rec.attribute26,
696 p_attribute27=>c_appl_inst_rec.attribute27,
697 p_attribute28=>c_appl_inst_rec.attribute28,
698 p_attribute29=>c_appl_inst_rec.attribute29,
699 p_attribute30=>c_appl_inst_rec.attribute30,
700 p_attribute31=>c_appl_inst_rec.attribute31,
701 p_attribute32=>c_appl_inst_rec.attribute32,
702 p_attribute33=>c_appl_inst_rec.attribute33,
703 p_attribute34=>c_appl_inst_rec.attribute34,
704 p_attribute35=>c_appl_inst_rec.attribute35,
705 p_attribute36=>c_appl_inst_rec.attribute36,
706 p_attribute37=>c_appl_inst_rec.attribute37,
707 p_attribute38=>c_appl_inst_rec.attribute38,
708 p_attribute39=>c_appl_inst_rec.attribute39,
709 p_attribute40=>c_appl_inst_rec.attribute40,
710 p_ss_application_id =>NULL,
711 p_sequence_number =>l_sequence_number,
712 p_return_type =>l_return_type,
713 p_error_code =>l_error_code,
714 p_message_name =>l_message_name,
715 p_entry_status =>c_appl_inst_rec.entry_status,
716 p_entry_level =>c_appl_inst_rec.entry_level,
717 p_sch_apl_to_id =>c_appl_inst_rec.sch_apl_to_id) THEN
718 p_new_admission_appl_number := l_admission_appl_number;
719 p_new_sequence_number := l_sequence_number;
720 RETURN TRUE;
721 ELSE -- Else of Application Instance
722 IF l_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
723 l_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
724 fnd_message.set_name('IGS', l_message_name);
725 fnd_message.set_token('PGM', c_appl_inst_rec.nominated_course_cd);
726 fnd_message.set_token('ALTCODE', c_appl_inst_rec.acad_cal_type || ',' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.acad_ci_sequence_number)
727 || '/' || c_appl_inst_rec.final_adm_cal_type || ',' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.final_adm_ci_sequence_number) );
728 fnd_file.put_line(fnd_file.log, 'Application could not be created');
729 fnd_file.put_line(fnd_file.log, fnd_message.get);
733 fnd_file.put_line(fnd_file.log, fnd_message.get);
730 ELSE
731 fnd_file.put_line(fnd_file.log, 'Application could not be created');
732 fnd_message.set_name('IGS', l_message_name);
734 END IF;
735 RETURN FALSE;
736 END IF;
737 RETURN FALSE;
738 ELSE -- Else for Application Program
739 fnd_file.put_line(fnd_file.log, 'Application could not be created');
740 fnd_message.set_name('IGS', l_message_name);
741 fnd_file.put_line(fnd_file.log, fnd_message.get);
742 RETURN FALSE;
743 END IF;
744 END IF;
745 END LOOP;
746 RETURN TRUE;
747 EXCEPTION WHEN OTHERS THEN
748 fnd_file.put_line(fnd_file.log, 'Exception From handle application log ' || l_message_name);
749 RETURN FALSE;
750 END handle_application;
751
752 FUNCTION validate_offer_validations(p_person_id HZ_PARTIES.PARTY_ID%TYPE,
753 p_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
754 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
755 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
756 p_old_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
757 p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
758 p_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE,
759 p_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE,
760 p_def_acad_cal_type igs_ad_appl.acad_cal_type%TYPE,
761 p_def_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE,
762 p_def_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
763 p_def_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE,
764 p_start_dt DATE
765 ) RETURN BOOLEAN IS
766 /*******************************************************************************
767 Created by : Ramesh Rengarajan
768 Date created: 6 SEP 2002
769
770 Purpose:
771 To validate and update the outcome status to Offer
772
773 Known limitations/enhancements and/or remarks:
774
775 Change History: (who, when, what: )
776 Who When What
777 rrengara 30-oct-2002 Build core vs optional Bug 2647482 Pre enrollment's parameter unit set has been called with the function api
778 apadegal 06-oct-2005 Retruned false if pre-enrollment process fails
779 *******************************************************************************/
780 -- Cursor Declarations ----------------------------------------------------------------------------
781
782 CURSOR c_appl_inst_old_cur IS
783 SELECT
784 apai.*
785 FROM
786 igs_ad_ps_appl_inst apai
787 WHERE
788 apai.person_id = p_person_id
789 AND apai.admission_appl_number = p_old_admission_appl_number
790 AND apai.nominated_course_cd = p_nominated_course_cd
791 AND apai.sequence_number = p_old_sequence_number;
792
793 c_appl_inst_old_rec c_appl_inst_old_cur%ROWTYPE;
794
795
796 CURSOR c_appl_offer_cur IS
797 SELECT
798 acai.*,
799 aa.appl_dt, aa.admission_cat, aa.s_admission_process_type,
800 aa.spcl_grp_1, aa.spcl_grp_2,aa.common_app,
801 aa.adm_appl_status, aa.choice_number, aa.routeb_pref,
802 aa.application_type, aa.adm_fee_status, aa.alt_appl_id,
803 aa.acad_cal_type, aa.acad_ci_sequence_number,
804 NVL(acai.adm_cal_type,aa.adm_cal_type) final_adm_cal_type,
805 NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) final_adm_ci_sequence_number
806
807 FROM
808 igs_ad_ps_appl_inst acai, /* Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst Bug 3150054 */
809 igs_ad_appl aa
810 WHERE
811 acai.person_id = p_person_id
812 AND acai.nominated_course_cd = p_nominated_course_cd
813 AND acai.sequence_number = p_sequence_number
814 AND acai.admission_appl_number = p_admission_appl_number
815 AND aa.person_id = acai.person_id
816 AND aa.admission_appl_number = acai.admission_appl_number;
817
818 CURSOR c_admcat_cur IS
819 SELECT
820 admission_cat, s_admission_process_type, appl_dt
821 FROM
822 igs_ad_appl
823 WHERE
824 person_id = p_person_id AND
825 admission_appl_number = p_admission_appl_number;
826
827 c_admcat_rec c_admcat_cur%ROWTYPE;
828
829 CURSOR c_apcs_cur (
830 cp_admission_cat igs_ad_prcs_cat_step.admission_cat%TYPE,
831 cp_s_admission_process_type igs_ad_prcs_cat_step.s_admission_process_type%TYPE
832 )
833 IS
834 SELECT
835 s_admission_step_type, step_type_restriction_num
836 FROM
837 igs_ad_prcs_cat_step
838 WHERE
839 admission_cat = cp_admission_cat
840 AND s_admission_process_type = cp_s_admission_process_type
841 AND step_group_type <> 'TRACK';
842
843 CURSOR c_upd_acai_cur IS
844 SELECT
845 ROWID, APAI.*
846 FROM
847 igs_ad_ps_appl_inst apai
848 WHERE
849 apai.person_id = p_person_id
850 AND apai.admission_appl_number = p_admission_appl_number
851 AND apai.nominated_course_cd = p_nominated_course_cd
852 AND apai.sequence_number = p_sequence_number;
856
853 -- End Cursor Declarations ----------------------------------------------------------------------------
854
855 -- Variable Declarations -------------------------------------------------------------------------------
857 l_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE;
858 v_pref_allowed_ind VARCHAR2 (1);
859 v_pref_limit NUMBER;
860 v_cond_offer_doc_allowed_ind VARCHAR2 (1);
861 v_cond_offer_fee_allowed_ind VARCHAR2 (1);
862 v_cond_offer_ass_allowed_ind VARCHAR2 (1);
863 v_late_appl_allowed_ind VARCHAR2 (1);
864 v_late_fees_required_ind VARCHAR2 (1);
865 v_fees_required_ind VARCHAR2 (1);
866 v_override_outcome_allowed_ind VARCHAR2 (1);
867 v_set_outcome_allowed_ind VARCHAR2 (1);
868 v_mult_offer_allowed_ind VARCHAR2 (1);
869 v_multi_offer_limit NUMBER;
870 v_unit_set_appl_ind VARCHAR2 (1);
871 v_check_person_encumb VARCHAR2 (1);
872 v_check_course_encumb VARCHAR2 (1);
873 v_deferral_allowed_ind VARCHAR2 (1);
874 v_pre_enrol_ind VARCHAR2 (1);
875 v_warn_level VARCHAR2(10);
876 v_message_name VARCHAR2(100);
877 l_adm_cat IGS_AD_APPL.admission_cat%TYPE;
878 l_s_adm_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
879 l_appl_dt IGS_AD_APPL.appl_dt%TYPE;
880 cst_offer CONSTANT VARCHAR2(9) := 'OFFER';
881 cst_received CONSTANT VARCHAR2(9) := 'RECEIVED';
882 cst_pending CONSTANT VARCHAR2(9) := 'PENDING';
883 -- End Variable Declarations -------------------------------------------------------------------------------
884
885 BEGIN
886
887 v_pref_allowed_ind := 'N';
888 v_cond_offer_doc_allowed_ind := 'N';
889 v_cond_offer_fee_allowed_ind := 'N';
890 v_cond_offer_ass_allowed_ind := 'N';
891 v_late_appl_allowed_ind := 'N';
892 v_late_fees_required_ind := 'N';
893 v_fees_required_ind := 'N';
894 v_override_outcome_allowed_ind := 'N';
895 v_set_outcome_allowed_ind := 'N';
896 v_mult_offer_allowed_ind := 'N';
897 v_unit_set_appl_ind := 'N';
898 v_check_person_encumb := 'N';
899 v_check_course_encumb := 'N';
900 v_deferral_allowed_ind := 'N';
901 v_pre_enrol_ind := 'N';
902
903 FOR c_appl_offer_rec IN c_appl_offer_cur LOOP
904 OPEN c_admcat_cur;
905 FETCH c_admcat_cur INTO l_adm_cat, l_s_adm_process_type, l_appl_dt;
906 CLOSE c_admcat_cur;
907
908 -- This cursor has to be opened to copy the decision related values to the new application
909
910 OPEN c_appl_inst_old_cur;
911 FETCH c_appl_inst_old_cur INTO c_appl_inst_old_rec;
912 CLOSE c_appl_inst_old_cur;
913 --
914 -- Determine the admission process category steps.
915 --
916 FOR l_c_apcs_rec IN c_apcs_cur (
917 l_adm_cat,
918 l_s_adm_process_type) LOOP
919 IF l_c_apcs_rec.s_admission_step_type = 'CHKCENCUMB' THEN
920 v_check_course_encumb := 'Y';
921
922 ELSIF l_c_apcs_rec.s_admission_step_type = 'CHKPENCUMB' THEN
923 v_check_person_encumb := 'Y';
924
925 ELSIF l_c_apcs_rec.s_admission_step_type = 'PREF-LIMIT' THEN
926 v_pref_allowed_ind := 'Y';
927 v_pref_limit := l_c_apcs_rec.step_type_restriction_num;
928
929 ELSIF l_c_apcs_rec.s_admission_step_type = 'DOC-COND' THEN
930 v_cond_offer_doc_allowed_ind := 'Y';
931
932 ELSIF l_c_apcs_rec.s_admission_step_type = 'FEE-COND' THEN
933 v_cond_offer_fee_allowed_ind := 'Y';
934
935 ELSIF l_c_apcs_rec.s_admission_step_type = 'ASSES-COND' THEN
936 v_cond_offer_ass_allowed_ind := 'Y';
937
938 ELSIF l_c_apcs_rec.s_admission_step_type = 'LATE-APP' THEN
939 v_late_appl_allowed_ind := 'Y';
940
941 ELSIF l_c_apcs_rec.s_admission_step_type = 'LATE-FEE' THEN
942 v_late_fees_required_ind := 'Y';
943
944 ELSIF l_c_apcs_rec.s_admission_step_type = 'APP-FEE' THEN
945 v_fees_required_ind := 'Y';
946
947 ELSIF l_c_apcs_rec.s_admission_step_type = 'OVERRIDE-O' THEN
948 v_override_outcome_allowed_ind := 'Y';
949
950 ELSIF l_c_apcs_rec.s_admission_step_type = 'SET-OTCOME' THEN
951 v_set_outcome_allowed_ind := 'Y';
952
953 ELSIF l_c_apcs_rec.s_admission_step_type = 'MULTI-OFF' THEN
954 v_mult_offer_allowed_ind := 'Y';
955 v_multi_offer_limit := l_c_apcs_rec.step_type_restriction_num;
956
957 ELSIF l_c_apcs_rec.s_admission_step_type = 'UNIT-SET' THEN
958 v_unit_set_appl_ind := 'Y';
959
960 ELSIF l_c_apcs_rec.s_admission_step_type = 'DEFER' THEN
961 v_deferral_allowed_ind := 'Y';
962
963 ELSIF l_c_apcs_rec.s_admission_step_type = 'PRE-ENROL' THEN
964 v_pre_enrol_ind := 'Y';
965 END IF;
966 END LOOP;
967 IF IGS_AD_VAL_ACAI.admp_val_offer_dt (
968 p_offer_dt,
969 igs_ad_gen_009.admp_get_sys_aos(cst_offer),
970 c_appl_offer_rec.adm_cal_type,
971 c_appl_offer_rec.adm_ci_sequence_number,
972 v_message_name) = FALSE THEN
973 fnd_message.set_name('IGS', v_message_name);
974 fnd_file.put_line(fnd_file.log, fnd_message.get);
975 END IF;
976 -- calculate the offer response date if the user doesn't passes the value
977 IF p_offer_response_dt IS NULL THEN
978
982 c_appl_offer_rec.crv_version_number,
979 -- Calculate the Offer response date by calling the following procedure
980 l_offer_response_dt := IGS_AD_GEN_007.ADMP_GET_RESP_DT (
981 c_appl_offer_rec.course_cd,
983 c_appl_offer_rec.acad_cal_type,
984 c_appl_offer_rec.location_cd,
985 c_appl_offer_rec.attendance_mode,
986 c_appl_offer_rec.attendance_type,
987 l_adm_cat,
988 l_s_adm_process_type,
989 c_appl_offer_rec.adm_cal_type,
990 c_appl_offer_rec.adm_ci_sequence_number,
991 p_offer_dt );
992 ELSE
993 l_offer_response_dt := p_offer_response_dt;
994 END IF;
995
996 IF l_offer_response_dt IS NOT NULL THEN
997 IF l_offer_response_dt < p_offer_dt THEN
998 fnd_message.set_name('IGS', 'IGS_AD_OFR_RSPDT_GE_OFRDT');
999 fnd_file.put_line(fnd_file.log, 'Application could not be created');
1000 fnd_file.put_line(fnd_file.log, fnd_message.get);
1001 RETURN FALSE;
1002 END IF;
1003 END IF;
1004
1005 IF igs_ad_val_acai_status.admp_val_acai_aos (
1006 c_appl_offer_rec.person_id,
1007 c_appl_offer_rec.admission_appl_number,
1008 c_appl_offer_rec.nominated_course_cd,
1009 c_appl_offer_rec.sequence_number,
1010 c_appl_offer_rec.course_cd,
1011 c_appl_offer_rec.crv_version_number,
1012 c_appl_offer_rec.location_cd,
1013 c_appl_offer_rec.attendance_mode,
1014 c_appl_offer_rec.attendance_type,
1015 c_appl_offer_rec.unit_set_cd,
1016 c_appl_offer_rec.us_version_number,
1017 c_appl_offer_rec.acad_cal_type,
1018 c_appl_offer_rec.acad_ci_sequence_number,
1019 c_appl_offer_rec.adm_cal_type,
1020 c_appl_offer_rec.adm_ci_sequence_number,
1021 l_adm_cat,
1022 l_s_adm_process_type,
1023 l_appl_dt,
1024 c_appl_offer_rec.fee_cat,
1025 c_appl_offer_rec.correspondence_cat,
1026 c_appl_offer_rec.enrolment_cat,
1027 igs_ad_gen_009.admp_get_sys_aos(cst_offer),
1028 c_appl_offer_rec.adm_outcome_status,
1029 c_appl_offer_rec.adm_doc_status,
1030 c_appl_offer_rec.adm_fee_status,
1031 IGS_AD_GEN_008.ADMP_GET_SAFS(c_appl_inst_old_rec.late_adm_fee_status), -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
1032 c_appl_offer_rec.adm_cndtnl_offer_status,
1033 c_appl_offer_rec.adm_entry_qual_status,
1034 igs_ad_gen_009.admp_get_sys_aors(cst_pending),
1035 c_appl_offer_rec.adm_offer_resp_status, --old
1036 c_appl_offer_rec.adm_outcome_status_auth_dt,
1037 v_set_outcome_allowed_ind,
1038 v_cond_offer_ass_allowed_ind,
1039 v_cond_offer_fee_allowed_ind,
1040 v_cond_offer_doc_allowed_ind,
1041 v_late_appl_allowed_ind,
1042 v_fees_required_ind,
1043 v_mult_offer_allowed_ind,
1044 v_multi_offer_limit,
1045 v_pref_allowed_ind,
1046 v_unit_set_appl_ind,
1047 v_check_person_encumb,
1048 v_check_course_encumb,
1049 'FORM',
1050 v_message_name
1051 ) THEN
1052
1053 FOR c_upd_acai_rec IN c_upd_acai_cur LOOP
1054
1055 -- Offer validations is successful then update the offer
1056 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
1057 x_rowid => c_upd_acai_rec.row_id ,
1058 x_person_id => c_upd_acai_rec.person_id ,
1059 x_admission_appl_number => c_upd_acai_rec.admission_appl_number ,
1060 x_nominated_course_cd => c_upd_acai_rec.nominated_course_cd ,
1061 x_sequence_number => c_upd_acai_rec.sequence_number ,
1062 x_predicted_gpa => c_appl_inst_old_rec.predicted_gpa ,
1063 x_academic_index => c_appl_inst_old_rec.academic_index,
1064 x_adm_cal_type => c_upd_acai_rec.adm_cal_type ,
1065 x_app_file_location => c_upd_acai_rec.app_file_location ,
1066 x_adm_ci_sequence_number => c_upd_acai_rec.adm_ci_sequence_number ,
1067 x_course_cd => c_upd_acai_rec.course_cd ,
1068 x_app_source_id => c_upd_acai_rec.app_source_id ,
1069 x_crv_version_number => c_upd_acai_rec.crv_version_number ,
1070 x_waitlist_rank => c_upd_acai_rec.waitlist_rank,
1071 x_waitlist_status => c_upd_acai_rec.waitlist_status,
1072 x_location_cd => c_upd_acai_rec.location_cd ,
1073 x_attent_other_inst_cd => c_upd_acai_rec.attent_other_inst_cd,
1077 x_decision_make_id => c_appl_inst_old_rec.decision_make_id,
1074 x_attendance_mode => c_upd_acai_rec.attendance_mode ,
1075 x_edu_goal_prior_enroll_id => c_upd_acai_rec.edu_goal_prior_enroll_id,
1076 x_attendance_type => c_upd_acai_rec.attendance_type ,
1078 x_unit_set_cd => c_upd_acai_rec.unit_set_cd ,
1079 x_decision_date => c_appl_inst_old_rec.decision_date,
1080 x_attribute_category => c_upd_acai_rec.attribute_category,
1081 x_attribute1 => c_upd_acai_rec.attribute1,
1082 x_attribute2 => c_upd_acai_rec.attribute2,
1083 x_attribute3 => c_upd_acai_rec.attribute3,
1084 x_attribute4 => c_upd_acai_rec.attribute4,
1085 x_attribute5 => c_upd_acai_rec.attribute5,
1086 x_attribute6 => c_upd_acai_rec.attribute6,
1087 x_attribute7 => c_upd_acai_rec.attribute7,
1088 x_attribute8 => c_upd_acai_rec.attribute8,
1089 x_attribute9 => c_upd_acai_rec.attribute9,
1090 x_attribute10 => c_upd_acai_rec.attribute10,
1091 x_attribute11 => c_upd_acai_rec.attribute11,
1092 x_attribute12 => c_upd_acai_rec.attribute12,
1093 x_attribute13 => c_upd_acai_rec.attribute13,
1094 x_attribute14 => c_upd_acai_rec.attribute14,
1095 x_attribute15 => c_upd_acai_rec.attribute15,
1096 x_attribute16 => c_upd_acai_rec.attribute16,
1097 x_attribute17 => c_upd_acai_rec.attribute17,
1098 x_attribute18 => c_upd_acai_rec.attribute18,
1099 x_attribute19 => c_upd_acai_rec.attribute19,
1100 x_attribute20 => c_upd_acai_rec.attribute20,
1101 x_decision_reason_id => c_appl_inst_old_rec.decision_reason_id,
1102 x_us_version_number => c_upd_acai_rec.us_version_number ,
1103 x_decision_notes => c_appl_inst_old_rec.decision_notes,
1104 x_pending_reason_id => c_upd_acai_rec.pending_reason_id,
1105 x_preference_number => c_upd_acai_rec.preference_number ,
1106 x_adm_doc_status => c_upd_acai_rec.adm_doc_status ,
1107 x_adm_entry_qual_status => c_upd_acai_rec.adm_entry_qual_status,
1108 x_deficiency_in_prep => c_upd_acai_rec.deficiency_in_prep ,
1109 x_late_adm_fee_status => c_appl_inst_old_rec.late_adm_fee_status , -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
1110 x_spl_consider_comments => c_upd_acai_rec.spl_consider_comments,
1111 x_adm_outcome_status => c_appl_inst_old_rec.adm_outcome_status,
1112 x_adm_otcm_stat_auth_per_id => c_upd_acai_rec.adm_otcm_status_auth_person_id ,
1113 x_adm_outcome_status_auth_dt => c_upd_acai_rec.adm_outcome_status_auth_dt ,
1114 x_adm_outcome_status_reason => c_upd_acai_rec.adm_outcome_status_reason ,
1115 x_offer_dt => p_offer_dt,
1116 x_offer_response_dt => l_offer_response_dt,
1117 x_prpsd_commencement_dt => p_start_dt,
1118 x_adm_cndtnl_offer_status => c_appl_inst_old_rec.adm_cndtnl_offer_status ,
1119 x_cndtnl_offer_satisfied_dt => c_appl_inst_old_rec.cndtnl_offer_satisfied_dt ,
1120 x_cndnl_ofr_must_be_stsfd_ind => c_appl_inst_old_rec.cndtnl_offer_must_be_stsfd_ind ,
1121 x_adm_offer_resp_status => igs_ad_gen_009.admp_get_sys_aors(cst_pending),
1122 x_actual_response_dt => c_upd_acai_rec.actual_response_dt ,
1123 x_adm_offer_dfrmnt_status => c_upd_acai_rec.adm_offer_dfrmnt_status ,
1124 x_deferred_adm_cal_type => c_upd_acai_rec.deferred_adm_cal_type,
1125 x_deferred_adm_ci_sequence_num => c_upd_acai_rec.deferred_adm_ci_sequence_num ,
1126 x_deferred_tracking_id => c_upd_acai_rec.deferred_tracking_id ,
1127 x_ass_rank => c_upd_acai_rec.ass_rank ,
1128 x_secondary_ass_rank => c_upd_acai_rec.secondary_ass_rank ,
1129 x_intr_accept_advice_num => c_upd_acai_rec.intrntnl_acceptance_advice_num ,
1133 x_expected_completion_yr => c_upd_acai_rec.expected_completion_yr ,
1130 x_ass_tracking_id => c_upd_acai_rec.ass_tracking_id ,
1131 x_fee_cat => c_upd_acai_rec.fee_cat ,
1132 x_hecs_payment_option => c_upd_acai_rec.hecs_payment_option ,
1134 x_expected_completion_perd => c_upd_acai_rec.expected_completion_perd ,
1135 x_correspondence_cat => c_upd_acai_rec.correspondence_cat ,
1136 x_enrolment_cat => c_upd_acai_rec.enrolment_cat ,
1137 x_funding_source => c_upd_acai_rec.funding_source ,
1138 x_applicant_acptnce_cndtn => c_upd_acai_rec.applicant_acptnce_cndtn ,
1139 x_cndtnl_offer_cndtn => c_upd_acai_rec.cndtnl_offer_cndtn ,
1140 x_ss_application_id => c_upd_acai_rec.ss_application_id,
1141 x_ss_pwd => c_upd_acai_rec.ss_pwd,
1142 x_authorized_dt => c_upd_acai_rec.authorized_dt,
1143 x_authorizing_pers_id => c_upd_acai_rec.authorizing_pers_id ,
1144 x_idx_calc_date => c_appl_inst_old_rec.idx_calc_date,
1145 x_mode => 'R',
1146 x_fut_acad_cal_type => NULL,--p_fut_acad_cal_type,
1147 x_fut_acad_ci_sequence_number => NULL,--p_fut_acad_cal_seq_no,
1148 x_fut_adm_cal_type => NULL,--p_fut_adm_cal_type,
1149 x_fut_adm_ci_sequence_number => NULL,--p_fut_adm_cal_seq_no ,
1150 x_prev_term_adm_appl_number => c_upd_acai_rec.previous_term_adm_appl_number,
1151 x_prev_term_sequence_number => c_upd_acai_rec.previous_term_sequence_number,
1152 x_fut_term_adm_appl_number => c_upd_acai_rec.future_term_adm_appl_number,
1153 x_fut_term_sequence_number => c_upd_acai_rec.future_term_sequence_number,
1154 x_def_acad_cal_type => c_upd_acai_rec.def_acad_cal_type,
1155 x_def_acad_ci_sequence_num => c_upd_acai_rec.def_acad_ci_sequence_num,
1156 x_def_prev_term_adm_appl_num => p_old_admission_appl_number,
1157 x_def_prev_appl_sequence_num => p_old_sequence_number,
1158 x_def_term_adm_appl_num => c_upd_acai_rec.def_term_adm_appl_num,
1159 x_def_appl_sequence_num => c_upd_acai_rec.def_appl_sequence_num,
1160 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631843
1161 -- by rrengara on 2-DEC-2002
1162 x_entry_status => c_appl_inst_old_rec.entry_status,
1163 x_entry_level => c_appl_inst_old_rec.entry_level,
1164 x_sch_apl_to_id => c_appl_inst_old_rec.sch_apl_to_id,
1165 x_apply_for_finaid => c_appl_inst_old_rec.apply_for_finaid,
1166 x_finaid_apply_date => c_appl_inst_old_rec.finaid_apply_date,
1167 x_appl_inst_status => c_appl_inst_old_rec.appl_inst_status,
1168 x_ais_reason => c_appl_inst_old_rec.ais_reason,
1169 x_decline_ofr_reason => c_appl_inst_old_rec.decline_ofr_reason
1170 );
1171 END LOOP;
1172
1173 -- Run the pre-enrollment process
1174 IF v_pre_enrol_ind = 'Y' THEN
1175 -- Validate the Enrollment Category mapping
1176 IF IGS_AD_VAL_ACAI.admp_val_acai_ec (
1177 l_adm_cat,
1178 c_appl_offer_rec.enrolment_cat,
1179 v_message_name) = FALSE THEN
1180 fnd_message.set_name('IGS', v_message_name);
1181 fnd_file.put_line(fnd_file.log, 'Application could not be created');
1182 fnd_file.put_line(fnd_file.log, fnd_message.get);
1183 RETURN FALSE;
1184 END IF;
1185
1186 -- rrengara
1187 -- on 28-oct-2002 for Build Core Vs optional Bug 2647482
1188 -- get the unit set mapping by calling the apc for the application
1189 -- then pass the value to the Pre-enrollment procedure for the parameter p_units_indicator
1190
1191 IF igs_ad_upd_initialise.perform_pre_enrol(
1192 c_appl_offer_rec.person_id,
1193 c_appl_offer_rec.admission_appl_number,
1194 c_appl_offer_rec.nominated_course_cd,
1195 c_appl_offer_rec.sequence_number,
1196 'N', -- Confirm course indicator.
1197 'N', -- Perform eligibility check indicator.
1198 v_message_name) = FALSE THEN
1199 fnd_message.set_name('IGS', v_message_name);
1200 fnd_file.put_line(fnd_file.log, 'Application could not be created'); -- added - apadegal adtd001 igs.m
1201 fnd_file.put_line(fnd_file.log, fnd_message.get);
1202 RETURN FALSE; -- added - apadegal adtd001 igs.m
1203 END IF;
1204 END IF; -- PRE-ENROLL IND = 'Y'
1205 ELSE
1206 IF v_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
1207 v_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
1211 || '/' || c_appl_offer_rec.final_adm_cal_type || ',' || IGS_GE_NUMBER.TO_CANN(c_appl_offer_rec.final_adm_ci_sequence_number) );
1208 fnd_message.set_name('IGS', v_message_name);
1209 fnd_message.set_token('PGM', c_appl_offer_rec.nominated_course_cd);
1210 fnd_message.set_token('ALTCODE', c_appl_offer_rec.acad_cal_type || ',' || IGS_GE_NUMBER.TO_CANN(c_appl_offer_rec.acad_ci_sequence_number)
1212 fnd_file.put_line(fnd_file.log, 'Application could not be created');
1213 fnd_file.put_line(fnd_file.log, fnd_message.get);
1214 ELSE
1215 fnd_file.put_line(fnd_file.log, 'Application could not be created');
1216 fnd_message.set_name('IGS', v_message_name);
1217 fnd_file.put_line(fnd_file.log, fnd_message.get);
1218 END IF;
1219 RETURN FALSE;
1220 END IF;
1221 END LOOP;
1222 RETURN TRUE;
1223 EXCEPTION WHEN OTHERS THEN
1224 fnd_file.put_line(fnd_file.log, 'Error from validate_offer_validations: ' || SQLERRM);
1225 fnd_file.put_line(fnd_file.log, 'Person Number : ' || get_person_number(IGS_GE_NUMBER.TO_CANN(p_person_id)));
1226 fnd_file.put_line(fnd_file.log, 'Admission Appl Number : ' || IGS_GE_NUMBER.TO_CANN(p_admission_appl_number));
1227 fnd_file.put_line(fnd_file.log, 'Nominated Course Code : ' || p_nominated_course_cd);
1228 fnd_file.put_line(fnd_file.log, 'Sequence Number : ' || p_sequence_number);
1229 RETURN FALSE;
1230 END validate_offer_validations;
1231
1232 FUNCTION copy_entrycomp_qual_status(p_person_id HZ_PARTIES.PARTY_ID%TYPE,
1233 p_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1234 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1235 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
1236 p_new_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1237 p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) RETURN BOOLEAN IS
1238 /*******************************************************************************
1239 Created by : Ramesh Rengarajan
1240 Date created: 06 SEP 2002
1241
1242 Purpose:
1243 To update entry qualification status and compleness status
1244
1245 Known limitations/enhancements and/or remarks:
1246
1247 Change History: (who, when, what: )
1248 Who When What
1249 *******************************************************************************/
1250 CURSOR c_upd_acai_new_cur IS
1251 SELECT
1252 apai.*
1253 FROM
1254 igs_ad_ps_appl_inst apai
1255 WHERE
1256 apai.person_id = p_person_id
1257 AND apai.admission_appl_number = p_new_admission_appl_number
1258 AND apai.nominated_course_cd = p_nominated_course_cd
1259 AND apai.sequence_number = p_new_sequence_number;
1260
1261
1262 CURSOR c_upd_acai_old_cur IS
1263 SELECT
1264 apai.*
1265 FROM
1266 igs_ad_ps_appl_inst apai
1267 WHERE
1268 apai.person_id = p_person_id
1269 AND apai.admission_appl_number = p_admission_appl_number
1270 AND apai.nominated_course_cd = p_nominated_course_cd
1271 AND apai.sequence_number = p_sequence_number;
1272
1273 cst_received CONSTANT VARCHAR2(9) := 'RECEIVED';
1274
1275 BEGIN
1276
1277 -- This procedure updates only entry qualification status from old to new future term application
1278 FOR c_upd_acai_old_rec IN c_upd_acai_old_cur LOOP
1279 FOR c_upd_acai_new_rec IN c_upd_acai_new_cur LOOP
1280 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
1281 x_rowid => c_upd_acai_new_rec.row_id ,
1282 x_person_id => c_upd_acai_new_rec.person_id ,
1283 x_admission_appl_number => c_upd_acai_new_rec.admission_appl_number ,
1284 x_nominated_course_cd => c_upd_acai_new_rec.nominated_course_cd ,
1285 x_sequence_number => c_upd_acai_new_rec.sequence_number ,
1286 x_predicted_gpa => c_upd_acai_new_rec.predicted_gpa ,
1287 x_academic_index => c_upd_acai_old_rec.academic_index,
1288 x_adm_cal_type => c_upd_acai_new_rec.adm_cal_type ,
1289 x_app_file_location => c_upd_acai_old_rec.app_file_location ,
1290 x_adm_ci_sequence_number => c_upd_acai_new_rec.adm_ci_sequence_number ,
1291 x_course_cd => c_upd_acai_new_rec.course_cd ,
1292 x_app_source_id => c_upd_acai_new_rec.app_source_id ,
1293 x_crv_version_number => c_upd_acai_new_rec.crv_version_number ,
1294 x_waitlist_rank => c_upd_acai_new_rec.waitlist_rank,
1295 x_waitlist_status => c_upd_acai_new_rec.waitlist_status,
1296 x_location_cd => c_upd_acai_new_rec.location_cd ,
1297 x_attent_other_inst_cd => c_upd_acai_old_rec.attent_other_inst_cd,
1298 x_attendance_mode => c_upd_acai_new_rec.attendance_mode ,
1299 x_edu_goal_prior_enroll_id => c_upd_acai_new_rec.edu_goal_prior_enroll_id,
1300 x_attendance_type => c_upd_acai_new_rec.attendance_type ,
1304 x_attribute_category => c_upd_acai_new_rec.attribute_category,
1301 x_decision_make_id => c_upd_acai_new_rec.decision_make_id,
1302 x_unit_set_cd => c_upd_acai_new_rec.unit_set_cd ,
1303 x_decision_date => c_upd_acai_new_rec.decision_date,
1305 x_attribute1 => c_upd_acai_new_rec.attribute1,
1306 x_attribute2 => c_upd_acai_new_rec.attribute2,
1307 x_attribute3 => c_upd_acai_new_rec.attribute3,
1308 x_attribute4 => c_upd_acai_new_rec.attribute4,
1309 x_attribute5 => c_upd_acai_new_rec.attribute5,
1310 x_attribute6 => c_upd_acai_new_rec.attribute6,
1311 x_attribute7 => c_upd_acai_new_rec.attribute7,
1312 x_attribute8 => c_upd_acai_new_rec.attribute8,
1313 x_attribute9 => c_upd_acai_new_rec.attribute9,
1314 x_attribute10 => c_upd_acai_new_rec.attribute10,
1315 x_attribute11 => c_upd_acai_new_rec.attribute11,
1316 x_attribute12 => c_upd_acai_new_rec.attribute12,
1317 x_attribute13 => c_upd_acai_new_rec.attribute13,
1318 x_attribute14 => c_upd_acai_new_rec.attribute14,
1319 x_attribute15 => c_upd_acai_new_rec.attribute15,
1320 x_attribute16 => c_upd_acai_new_rec.attribute16,
1321 x_attribute17 => c_upd_acai_new_rec.attribute17,
1322 x_attribute18 => c_upd_acai_new_rec.attribute18,
1323 x_attribute19 => c_upd_acai_new_rec.attribute19,
1324 x_attribute20 => c_upd_acai_new_rec.attribute20,
1325 x_decision_reason_id => c_upd_acai_new_rec.decision_reason_id,
1326 x_us_version_number => c_upd_acai_new_rec.us_version_number ,
1327 x_decision_notes => c_upd_acai_new_rec.decision_notes,
1328 x_pending_reason_id => c_upd_acai_new_rec.pending_reason_id,
1329 x_preference_number => c_upd_acai_new_rec.preference_number ,
1330 x_adm_doc_status => c_upd_acai_old_rec.adm_doc_status , -- updating doc status
1331 x_adm_entry_qual_status => c_upd_acai_old_rec.adm_entry_qual_status, -- updating entry qualification status
1332 x_deficiency_in_prep => c_upd_acai_new_rec.deficiency_in_prep ,
1333 x_late_adm_fee_status => c_upd_acai_old_rec.late_adm_fee_status, -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
1334 x_spl_consider_comments => c_upd_acai_new_rec.spl_consider_comments,
1335 x_adm_outcome_status => c_upd_acai_new_rec.adm_outcome_status,
1336 x_adm_otcm_stat_auth_per_id => c_upd_acai_new_rec.adm_otcm_status_auth_person_id,
1337 x_adm_outcome_status_auth_dt => c_upd_acai_new_rec.adm_outcome_status_auth_dt,
1338 x_adm_outcome_status_reason => c_upd_acai_new_rec.adm_outcome_status_reason,
1339 x_offer_dt => c_upd_acai_new_rec.offer_dt,
1340 x_offer_response_dt => c_upd_acai_new_rec.offer_response_dt,
1341 x_prpsd_commencement_dt => c_upd_acai_new_rec.prpsd_commencement_dt,
1342 x_adm_cndtnl_offer_status => c_upd_acai_new_rec.adm_cndtnl_offer_status,
1343 x_cndtnl_offer_satisfied_dt => c_upd_acai_new_rec.cndtnl_offer_satisfied_dt,
1344 x_cndnl_ofr_must_be_stsfd_ind => c_upd_acai_new_rec.cndtnl_offer_must_be_stsfd_ind,
1345 x_adm_offer_resp_status => c_upd_acai_new_rec.adm_offer_resp_status,
1346 x_actual_response_dt => c_upd_acai_new_rec.actual_response_dt,
1347 x_adm_offer_dfrmnt_status => c_upd_acai_new_rec.adm_offer_dfrmnt_status ,
1348 x_deferred_adm_cal_type => c_upd_acai_new_rec.deferred_adm_cal_type,
1349 x_deferred_adm_ci_sequence_num => c_upd_acai_new_rec.deferred_adm_ci_sequence_num ,
1350 x_deferred_tracking_id => c_upd_acai_old_rec.deferred_tracking_id ,
1351 x_ass_rank => c_upd_acai_old_rec.ass_rank ,
1352 x_secondary_ass_rank => c_upd_acai_new_rec.secondary_ass_rank ,
1353 x_intr_accept_advice_num => c_upd_acai_new_rec.intrntnl_acceptance_advice_num ,
1354 x_ass_tracking_id => c_upd_acai_old_rec.ass_tracking_id ,
1355 x_fee_cat => c_upd_acai_new_rec.fee_cat ,
1359 x_correspondence_cat => c_upd_acai_new_rec.correspondence_cat ,
1356 x_hecs_payment_option => c_upd_acai_new_rec.hecs_payment_option ,
1357 x_expected_completion_yr => c_upd_acai_old_rec.expected_completion_yr, --c_upd_acai_new_rec.expected_completion_yr ,
1358 x_expected_completion_perd => c_upd_acai_old_rec.expected_completion_perd, --c_upd_acai_new_rec.expected_completion_perd ,
1360 x_enrolment_cat => c_upd_acai_new_rec.enrolment_cat ,
1361 x_funding_source => c_upd_acai_old_rec.funding_source ,
1362 x_applicant_acptnce_cndtn => c_upd_acai_new_rec.applicant_acptnce_cndtn ,
1363 x_cndtnl_offer_cndtn => c_upd_acai_new_rec.cndtnl_offer_cndtn ,
1364 x_ss_application_id => c_upd_acai_new_rec.ss_application_id,
1365 x_ss_pwd => NULL,
1366 x_authorized_dt => c_upd_acai_new_rec.authorized_dt,
1367 x_authorizing_pers_id => c_upd_acai_new_rec.authorizing_pers_id ,
1368 x_idx_calc_date => c_upd_acai_new_rec.idx_calc_date,
1369 x_mode => 'R',
1370 x_fut_acad_cal_type => c_upd_acai_new_rec.future_acad_cal_type,
1371 x_fut_acad_ci_sequence_number => c_upd_acai_new_rec.future_acad_ci_sequence_number,
1372 x_fut_adm_cal_type => c_upd_acai_new_rec.future_adm_cal_type,
1373 x_fut_adm_ci_sequence_number => c_upd_acai_new_rec.future_adm_ci_sequence_number ,
1374 x_prev_term_adm_appl_number => c_upd_acai_new_rec.previous_term_adm_appl_number,
1375 x_prev_term_sequence_number => c_upd_acai_new_rec.previous_term_sequence_number,
1376 x_fut_term_adm_appl_number => c_upd_acai_new_rec.future_term_adm_appl_number,
1377 x_fut_term_sequence_number => c_upd_acai_new_rec.future_term_sequence_number,
1378 x_def_acad_cal_type => c_upd_acai_new_rec.def_acad_cal_type,
1379 x_def_acad_ci_sequence_num => c_upd_acai_new_rec.def_acad_ci_sequence_num,
1380 x_def_prev_term_adm_appl_num => c_upd_acai_new_rec.def_prev_term_adm_appl_num,
1381 x_def_prev_appl_sequence_num => c_upd_acai_new_rec.def_prev_appl_sequence_num,
1382 x_def_term_adm_appl_num => c_upd_acai_new_rec.def_term_adm_appl_num,
1383 x_def_appl_sequence_num => c_upd_acai_new_rec.def_appl_sequence_num,
1384 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631843
1385 -- by rrengara on 2-DEC-2002
1386 x_entry_status => c_upd_acai_old_rec.entry_status,
1387 x_entry_level => c_upd_acai_old_rec.entry_level,
1388 x_sch_apl_to_id => c_upd_acai_old_rec.sch_apl_to_id,
1389 x_apply_for_finaid => c_upd_acai_old_rec.apply_for_finaid,
1390 x_finaid_apply_date => c_upd_acai_old_rec.finaid_apply_date,
1391 x_appl_inst_status => c_upd_acai_old_rec.appl_inst_status,
1392 x_ais_reason => c_upd_acai_old_rec.ais_reason,
1393 x_decline_ofr_reason => c_upd_acai_old_rec.decline_ofr_reason
1394 );
1395
1396 -- Also update the future application number in the old application number for link
1397 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
1398 x_rowid => c_upd_acai_old_rec.row_id ,
1399 x_person_id => c_upd_acai_old_rec.person_id ,
1400 x_admission_appl_number => c_upd_acai_old_rec.admission_appl_number ,
1401 x_nominated_course_cd => c_upd_acai_old_rec.nominated_course_cd ,
1402 x_sequence_number => c_upd_acai_old_rec.sequence_number ,
1403 x_predicted_gpa => c_upd_acai_old_rec.predicted_gpa ,
1404 x_academic_index => c_upd_acai_old_rec.academic_index,
1405 x_adm_cal_type => c_upd_acai_old_rec.adm_cal_type ,
1406 x_app_file_location => c_upd_acai_old_rec.app_file_location ,
1407 x_adm_ci_sequence_number => c_upd_acai_old_rec.adm_ci_sequence_number ,
1408 x_course_cd => c_upd_acai_old_rec.course_cd ,
1409 x_app_source_id => c_upd_acai_old_rec.app_source_id ,
1410 x_crv_version_number => c_upd_acai_old_rec.crv_version_number ,
1411 x_waitlist_rank => c_upd_acai_old_rec.waitlist_rank,
1412 x_waitlist_status => c_upd_acai_old_rec.waitlist_status,
1413 x_location_cd => c_upd_acai_old_rec.location_cd ,
1414 x_attent_other_inst_cd => c_upd_acai_old_rec.attent_other_inst_cd,
1415 x_attendance_mode => c_upd_acai_old_rec.attendance_mode ,
1416 x_edu_goal_prior_enroll_id => c_upd_acai_old_rec.edu_goal_prior_enroll_id,
1417 x_attendance_type => c_upd_acai_old_rec.attendance_type ,
1421 x_attribute_category => c_upd_acai_old_rec.attribute_category,
1418 x_decision_make_id => c_upd_acai_old_rec.decision_make_id,
1419 x_unit_set_cd => c_upd_acai_old_rec.unit_set_cd ,
1420 x_decision_date => c_upd_acai_old_rec.decision_date,
1422 x_attribute1 => c_upd_acai_old_rec.attribute1,
1423 x_attribute2 => c_upd_acai_old_rec.attribute2,
1424 x_attribute3 => c_upd_acai_old_rec.attribute3,
1425 x_attribute4 => c_upd_acai_old_rec.attribute4,
1426 x_attribute5 => c_upd_acai_old_rec.attribute5,
1427 x_attribute6 => c_upd_acai_old_rec.attribute6,
1428 x_attribute7 => c_upd_acai_old_rec.attribute7,
1429 x_attribute8 => c_upd_acai_old_rec.attribute8,
1430 x_attribute9 => c_upd_acai_old_rec.attribute9,
1431 x_attribute10 => c_upd_acai_old_rec.attribute10,
1432 x_attribute11 => c_upd_acai_old_rec.attribute11,
1433 x_attribute12 => c_upd_acai_old_rec.attribute12,
1434 x_attribute13 => c_upd_acai_old_rec.attribute13,
1435 x_attribute14 => c_upd_acai_old_rec.attribute14,
1436 x_attribute15 => c_upd_acai_old_rec.attribute15,
1437 x_attribute16 => c_upd_acai_old_rec.attribute16,
1438 x_attribute17 => c_upd_acai_old_rec.attribute17,
1439 x_attribute18 => c_upd_acai_old_rec.attribute18,
1440 x_attribute19 => c_upd_acai_old_rec.attribute19,
1441 x_attribute20 => c_upd_acai_old_rec.attribute20,
1442 x_decision_reason_id => c_upd_acai_old_rec.decision_reason_id,
1443 x_us_version_number => c_upd_acai_old_rec.us_version_number ,
1444 x_decision_notes => c_upd_acai_old_rec.decision_notes,
1445 x_pending_reason_id => c_upd_acai_old_rec.pending_reason_id,
1446 x_preference_number => c_upd_acai_old_rec.preference_number ,
1447 x_adm_doc_status => c_upd_acai_old_rec.adm_doc_status , -- updating doc status
1448 x_adm_entry_qual_status => c_upd_acai_old_rec.adm_entry_qual_status, -- updating entry qualification status
1449 x_deficiency_in_prep => c_upd_acai_old_rec.deficiency_in_prep ,
1450 x_late_adm_fee_status => c_upd_acai_old_rec.late_adm_fee_status ,
1451 x_spl_consider_comments => c_upd_acai_old_rec.spl_consider_comments,
1452 x_adm_outcome_status => c_upd_acai_old_rec.adm_outcome_status,
1453 x_adm_otcm_stat_auth_per_id => c_upd_acai_old_rec.adm_otcm_status_auth_person_id,
1454 x_adm_outcome_status_auth_dt => c_upd_acai_old_rec.adm_outcome_status_auth_dt,
1455 x_adm_outcome_status_reason => c_upd_acai_old_rec.adm_outcome_status_reason,
1456 x_offer_dt => c_upd_acai_old_rec.offer_dt,
1457 x_offer_response_dt => c_upd_acai_old_rec.offer_response_dt,
1458 x_prpsd_commencement_dt => c_upd_acai_old_rec.prpsd_commencement_dt,
1459 x_adm_cndtnl_offer_status => c_upd_acai_old_rec.adm_cndtnl_offer_status,
1460 x_cndtnl_offer_satisfied_dt => c_upd_acai_old_rec.cndtnl_offer_satisfied_dt,
1461 x_cndnl_ofr_must_be_stsfd_ind => c_upd_acai_old_rec.cndtnl_offer_must_be_stsfd_ind,
1462 x_adm_offer_resp_status => c_upd_acai_old_rec.adm_offer_resp_status,
1463 x_actual_response_dt => c_upd_acai_old_rec.actual_response_dt,
1464 x_adm_offer_dfrmnt_status => c_upd_acai_old_rec.adm_offer_dfrmnt_status ,
1465 x_deferred_adm_cal_type => c_upd_acai_old_rec.deferred_adm_cal_type,
1466 x_deferred_adm_ci_sequence_num => c_upd_acai_old_rec.deferred_adm_ci_sequence_num ,
1467 x_deferred_tracking_id => c_upd_acai_old_rec.deferred_tracking_id ,
1468 x_ass_rank => c_upd_acai_old_rec.ass_rank ,
1469 x_secondary_ass_rank => c_upd_acai_old_rec.secondary_ass_rank ,
1470 x_intr_accept_advice_num => c_upd_acai_old_rec.intrntnl_acceptance_advice_num ,
1471 x_ass_tracking_id => c_upd_acai_old_rec.ass_tracking_id ,
1472 x_fee_cat => c_upd_acai_old_rec.fee_cat ,
1473 x_hecs_payment_option => c_upd_acai_old_rec.hecs_payment_option ,
1474 x_expected_completion_yr => c_upd_acai_old_rec.expected_completion_yr ,
1478 x_funding_source => c_upd_acai_old_rec.funding_source ,
1475 x_expected_completion_perd => c_upd_acai_old_rec.expected_completion_perd ,
1476 x_correspondence_cat => c_upd_acai_old_rec.correspondence_cat ,
1477 x_enrolment_cat => c_upd_acai_old_rec.enrolment_cat ,
1479 x_applicant_acptnce_cndtn => c_upd_acai_old_rec.applicant_acptnce_cndtn ,
1480 x_cndtnl_offer_cndtn => c_upd_acai_old_rec.cndtnl_offer_cndtn ,
1481 x_ss_application_id => c_upd_acai_old_rec.ss_application_id,
1482 x_ss_pwd => c_upd_acai_old_rec.ss_pwd,
1483 x_authorized_dt => c_upd_acai_old_rec.authorized_dt,
1484 x_authorizing_pers_id => c_upd_acai_old_rec.authorizing_pers_id ,
1485 x_idx_calc_date => c_upd_acai_old_rec.idx_calc_date,
1486 x_mode => 'R',
1487 x_fut_acad_cal_type => c_upd_acai_old_rec.future_acad_cal_type,
1488 x_fut_acad_ci_sequence_number => c_upd_acai_old_rec.future_acad_ci_sequence_number,
1489 x_fut_adm_cal_type => c_upd_acai_old_rec.future_adm_cal_type,
1490 x_fut_adm_ci_sequence_number => c_upd_acai_old_rec.future_adm_ci_sequence_number ,
1491 x_prev_term_adm_appl_number => c_upd_acai_old_rec.previous_term_adm_appl_number,
1492 x_prev_term_sequence_number => c_upd_acai_old_rec.previous_term_sequence_number,
1493 x_fut_term_adm_appl_number => c_upd_acai_old_rec.future_term_adm_appl_number,
1494 x_fut_term_sequence_number => c_upd_acai_old_rec.future_term_sequence_number,
1495 x_def_acad_cal_type => c_upd_acai_old_rec.def_acad_cal_type,
1496 x_def_acad_ci_sequence_num =>c_upd_acai_old_rec.def_acad_ci_sequence_num,
1497 x_def_prev_term_adm_appl_num => c_upd_acai_old_rec.def_prev_term_adm_appl_num,
1498 x_def_prev_appl_sequence_num => c_upd_acai_old_rec.def_prev_appl_sequence_num,
1499 x_def_term_adm_appl_num => p_new_admission_appl_number,
1500 x_def_appl_sequence_num => p_new_sequence_number,
1501 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631843
1502 -- by rrengara on 2-DEC-2002
1503 x_entry_status => c_upd_acai_old_rec.entry_status,
1504 x_entry_level => c_upd_acai_old_rec.entry_level,
1505 x_sch_apl_to_id => c_upd_acai_old_rec.sch_apl_to_id,
1506 x_apply_for_finaid => c_upd_acai_old_rec.apply_for_finaid,
1507 x_finaid_apply_date => c_upd_acai_old_rec.finaid_apply_date,
1508 x_appl_inst_status => c_upd_acai_old_rec.appl_inst_status,
1509 x_ais_reason => c_upd_acai_old_rec.ais_reason,
1510 x_decline_ofr_reason => c_upd_acai_old_rec.decline_ofr_reason
1511 );
1512 END LOOP;
1513 END LOOP;
1514 RETURN TRUE;
1515 EXCEPTION WHEN OTHERS THEN
1516 fnd_file.put_line(fnd_file.log, 'Error from copy_entrycomp_qual_status: ' || SQLERRM);
1517 fnd_file.put_line(fnd_file.log, 'Person Number : ' || get_person_number(IGS_GE_NUMBER.TO_CANN(p_person_id)));
1518 fnd_file.put_line(fnd_file.log, 'Admission Appl Number : ' || IGS_GE_NUMBER.TO_CANN(p_admission_appl_number));
1519 fnd_file.put_line(fnd_file.log, 'Nominated Course Code : ' || p_nominated_course_cd);
1520 fnd_file.put_line(fnd_file.log, 'Sequence Number : ' || p_sequence_number);
1521 RETURN FALSE;
1522 END copy_entrycomp_qual_status;
1523
1524
1525 FUNCTION Update_offer_response_accepted (p_person_id HZ_PARTIES.party_id%TYPE,
1526 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1527 p_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1528 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) RETURN BOOLEAN IS
1529 /*******************************************************************************
1530 Created by : hreddych
1531 Date created: 16 OCT 2002
1532
1533 Purpose:
1534 To update the Offer response status of the new application to 'ACCEPTED'
1535 if the offer deferment status of the old application is 'CONFIRM'
1536 and to initiate the pre-enrollments Process.
1537
1538 Change History: (who, when, what: )
1539 Who When What
1540 apadegal 06-oct-2005 Changed from procedure to function, to return false, if any thing fails
1541 Retruned false if pre-enrollment process fails
1542 *******************************************************************************/
1543
1544 -- Cursor Declarations-------------------------------------------------------------------
1545 CURSOR cur_appl_details (p_person_id Igs_Ad_Ps_Appl_Inst.person_id%TYPE,
1546 p_admission_appl_number Igs_Ad_Ps_Appl_Inst.admission_appl_number%TYPE,
1547 p_nominated_course_cd Igs_Ad_Ps_Appl_Inst.nominated_course_cd%TYPE,
1548 p_sequence_number Igs_Ad_Ps_Appl_Inst.sequence_number%TYPE ) IS
1549 SELECT apai.*, apai.rowid
1550 FROM igs_ad_ps_appl_inst apai /* Replaced igs_ad_ps_appl_inst_aplinst_v with base tables Bug 3150054 */
1551 WHERE apai.person_id = p_person_id
1552 AND apai.admission_appl_number = p_admission_appl_number
1553 AND apai.nominated_course_cd = p_nominated_course_cd
1557 p_admission_appl_number Igs_Ad_Ps_Appl_Inst.admission_appl_number%TYPE,
1554 AND apai.sequence_number = p_sequence_number;
1555
1556 CURSOR cur_application_details (p_person_id Igs_Ad_Ps_Appl_Inst.person_id%TYPE,
1558 p_nominated_course_cd Igs_Ad_Ps_Appl_Inst.nominated_course_cd%TYPE,
1559 p_sequence_number Igs_Ad_Ps_Appl_Inst.sequence_number%TYPE ) IS
1560 SELECT apai.*
1561 FROM igs_ad_ps_appl_inst apai
1562 WHERE apai.person_id = p_person_id
1563 AND apai.admission_appl_number = p_admission_appl_number
1564 AND apai.nominated_course_cd = p_nominated_course_cd
1565 AND apai.sequence_number = p_sequence_number;
1566
1567 CURSOR cur_adm_offer_resp_status IS
1568 SELECT adm_offer_resp_status
1569 FROM igs_ad_ofr_resp_stat
1570 WHERE s_adm_offer_resp_status ='ACCEPTED'
1571 AND system_default_ind ='Y';
1572
1573 -----End cursor Declarations------------------------------------------------------------------
1574
1575 -------------------Variable Declarations------------------------------------------------------
1576 l_adm_offer_resp_status Igs_Ad_Ps_Appl_Inst.adm_offer_resp_status%TYPE;
1577 cur_appl_details_rec cur_appl_details%ROWTYPE;
1578 cur_application_details_rec cur_application_details%ROWTYPE;
1579 v_message_name VARCHAR2(100);
1580 v_warn_level VARCHAR2(10);
1581 ------------------- End Variable Declarations------------------------------------------------------
1582 BEGIN
1583
1584 OPEN cur_adm_offer_resp_status ;
1585 FETCH cur_adm_offer_resp_status INTO l_adm_offer_resp_status;
1586 CLOSE cur_adm_offer_resp_status;
1587
1588 OPEN cur_appl_details (p_person_id,
1589 p_admission_appl_number,
1590 p_nominated_course_cd,
1591 p_sequence_number);
1592 FETCH cur_appl_details INTO cur_appl_details_rec;
1593 CLOSE cur_appl_details;
1594
1595
1596 igs_ad_ps_appl_inst_pkg.update_row (
1597 X_ROWID => cur_appl_details_rec.ROWID ,
1598 x_PERSON_ID => cur_appl_details_rec.PERSON_ID ,
1599 x_ADMISSION_APPL_NUMBER => cur_appl_details_rec.ADMISSION_APPL_NUMBER ,
1600 x_NOMINATED_COURSE_CD => cur_appl_details_rec.NOMINATED_COURSE_CD ,
1601 x_SEQUENCE_NUMBER => cur_appl_details_rec.SEQUENCE_NUMBER ,
1602 x_PREDICTED_GPA => cur_appl_details_rec.PREDICTED_GPA ,
1603 x_ACADEMIC_INDEX => cur_appl_details_rec.ACADEMIC_INDEX ,
1604 x_ADM_CAL_TYPE => cur_appl_details_rec.ADM_CAL_TYPE ,
1605 x_APP_FILE_LOCATION => cur_appl_details_rec.APP_FILE_LOCATION ,
1606 x_ADM_CI_SEQUENCE_NUMBER => cur_appl_details_rec.ADM_CI_SEQUENCE_NUMBER ,
1607 x_COURSE_CD => cur_appl_details_rec.COURSE_CD ,
1608 x_APP_SOURCE_ID => cur_appl_details_rec.APP_SOURCE_ID ,
1609 x_CRV_VERSION_NUMBER => cur_appl_details_rec.CRV_VERSION_NUMBER ,
1610 x_WAITLIST_RANK => cur_appl_details_rec.WAITLIST_RANK ,
1611 x_LOCATION_CD => cur_appl_details_rec.LOCATION_CD ,
1612 x_ATTENT_OTHER_INST_CD => cur_appl_details_rec.ATTENT_OTHER_INST_CD ,
1613 x_ATTENDANCE_MODE => cur_appl_details_rec.ATTENDANCE_MODE ,
1614 x_EDU_GOAL_PRIOR_ENROLL_ID => cur_appl_details_rec.EDU_GOAL_PRIOR_ENROLL_ID ,
1615 x_ATTENDANCE_TYPE => cur_appl_details_rec.ATTENDANCE_TYPE ,
1616 x_DECISION_MAKE_ID => cur_appl_details_rec.DECISION_MAKE_ID ,
1617 x_UNIT_SET_CD => cur_appl_details_rec.UNIT_SET_CD ,
1618 x_DECISION_DATE => cur_appl_details_rec.DECISION_DATE ,
1619 x_ATTRIBUTE_CATEGORY => cur_appl_details_rec.ATTRIBUTE_CATEGORY ,
1620 x_ATTRIBUTE1 => cur_appl_details_rec.ATTRIBUTE1 ,
1621 x_ATTRIBUTE2 => cur_appl_details_rec.ATTRIBUTE2 ,
1622 x_ATTRIBUTE3 => cur_appl_details_rec.ATTRIBUTE3 ,
1623 x_ATTRIBUTE4 => cur_appl_details_rec.ATTRIBUTE4 ,
1624 x_ATTRIBUTE5 => cur_appl_details_rec.ATTRIBUTE5 ,
1625 x_ATTRIBUTE6 => cur_appl_details_rec.ATTRIBUTE6 ,
1626 x_ATTRIBUTE7 => cur_appl_details_rec.ATTRIBUTE7 ,
1627 x_ATTRIBUTE8 => cur_appl_details_rec.ATTRIBUTE8 ,
1628 x_ATTRIBUTE9 => cur_appl_details_rec.ATTRIBUTE9 ,
1629 x_ATTRIBUTE10 => cur_appl_details_rec.ATTRIBUTE10 ,
1630 x_ATTRIBUTE11 => cur_appl_details_rec.ATTRIBUTE11 ,
1631 x_ATTRIBUTE12 => cur_appl_details_rec.ATTRIBUTE12 ,
1632 x_ATTRIBUTE13 => cur_appl_details_rec.ATTRIBUTE13 ,
1633 x_ATTRIBUTE14 => cur_appl_details_rec.ATTRIBUTE14 ,
1634 x_ATTRIBUTE15 => cur_appl_details_rec.ATTRIBUTE15 ,
1635 x_ATTRIBUTE16 => cur_appl_details_rec.ATTRIBUTE16 ,
1639 x_ATTRIBUTE20 => cur_appl_details_rec.ATTRIBUTE20 ,
1636 x_ATTRIBUTE17 => cur_appl_details_rec.ATTRIBUTE17 ,
1637 x_ATTRIBUTE18 => cur_appl_details_rec.ATTRIBUTE18 ,
1638 x_ATTRIBUTE19 => cur_appl_details_rec.ATTRIBUTE19 ,
1640 x_DECISION_REASON_ID => cur_appl_details_rec.DECISION_REASON_ID ,
1641 x_US_VERSION_NUMBER => cur_appl_details_rec.US_VERSION_NUMBER ,
1642 x_DECISION_NOTES => cur_appl_details_rec.DECISION_NOTES ,
1643 x_PENDING_REASON_ID => cur_appl_details_rec.PENDING_REASON_ID ,
1644 x_PREFERENCE_NUMBER => cur_appl_details_rec.PREFERENCE_NUMBER ,
1645 x_ADM_DOC_STATUS => cur_appl_details_rec.ADM_DOC_STATUS ,
1646 x_ADM_ENTRY_QUAL_STATUS => cur_appl_details_rec.ADM_ENTRY_QUAL_STATUS ,
1647 x_DEFICIENCY_IN_PREP => cur_appl_details_rec.DEFICIENCY_IN_PREP ,
1648 x_LATE_ADM_FEE_STATUS => cur_appl_details_rec.LATE_ADM_FEE_STATUS ,
1649 x_SPL_CONSIDER_COMMENTS => cur_appl_details_rec.SPL_CONSIDER_COMMENTS ,
1650 x_APPLY_FOR_FINAID => cur_appl_details_rec.APPLY_FOR_FINAID ,
1651 x_FINAID_APPLY_DATE => cur_appl_details_rec.FINAID_APPLY_DATE ,
1652 x_ADM_OUTCOME_STATUS => cur_appl_details_rec.ADM_OUTCOME_STATUS ,
1653 x_ADM_OTCM_STAT_AUTH_PER_ID => cur_appl_details_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1654 x_ADM_OUTCOME_STATUS_AUTH_DT => cur_appl_details_rec.ADM_OUTCOME_STATUS_AUTH_DT ,
1655 x_ADM_OUTCOME_STATUS_REASON => cur_appl_details_rec.ADM_OUTCOME_STATUS_REASON ,
1656 x_OFFER_DT => cur_appl_details_rec.OFFER_DT ,
1657 x_OFFER_RESPONSE_DT => cur_appl_details_rec.OFFER_RESPONSE_DT ,
1658 x_PRPSD_COMMENCEMENT_DT => cur_appl_details_rec.PRPSD_COMMENCEMENT_DT ,
1659 x_ADM_CNDTNL_OFFER_STATUS => cur_appl_details_rec.ADM_CNDTNL_OFFER_STATUS ,
1660 x_CNDTNL_OFFER_SATISFIED_DT => cur_appl_details_rec.CNDTNL_OFFER_SATISFIED_DT ,
1661 x_CNDNL_OFR_MUST_BE_STSFD_IND => cur_appl_details_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
1662 x_ADM_OFFER_RESP_STATUS => l_adm_offer_resp_status ,
1663 x_ACTUAL_RESPONSE_DT => TRUNC(SYSDATE) ,
1664 x_ADM_OFFER_DFRMNT_STATUS => cur_appl_details_rec.ADM_OFFER_DFRMNT_STATUS ,
1665 x_DEFERRED_ADM_CAL_TYPE => cur_appl_details_rec.DEFERRED_ADM_CAL_TYPE ,
1666 x_DEFERRED_ADM_CI_SEQUENCE_NUM => cur_appl_details_rec.DEFERRED_ADM_CI_SEQUENCE_NUM ,
1667 x_DEFERRED_TRACKING_ID => cur_appl_details_rec.DEFERRED_TRACKING_ID ,
1668 x_ASS_RANK => cur_appl_details_rec.ASS_RANK ,
1669 x_SECONDARY_ASS_RANK => cur_appl_details_rec.SECONDARY_ASS_RANK ,
1670 X_INTR_ACCEPT_ADVICE_NUM => cur_appl_details_rec.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1671 x_ASS_TRACKING_ID => cur_appl_details_rec.ASS_TRACKING_ID ,
1672 x_FEE_CAT => cur_appl_details_rec.FEE_CAT ,
1673 x_HECS_PAYMENT_OPTION => cur_appl_details_rec.HECS_PAYMENT_OPTION ,
1674 x_EXPECTED_COMPLETION_YR => cur_appl_details_rec.EXPECTED_COMPLETION_YR ,
1675 x_EXPECTED_COMPLETION_PERD => cur_appl_details_rec.EXPECTED_COMPLETION_PERD ,
1676 x_CORRESPONDENCE_CAT => cur_appl_details_rec.CORRESPONDENCE_CAT ,
1677 x_ENROLMENT_CAT => cur_appl_details_rec.ENROLMENT_CAT ,
1678 x_FUNDING_SOURCE => cur_appl_details_rec.FUNDING_SOURCE ,
1679 x_APPLICANT_ACPTNCE_CNDTN => cur_appl_details_rec.APPLICANT_ACPTNCE_CNDTN ,
1680 x_CNDTNL_OFFER_CNDTN => cur_appl_details_rec.CNDTNL_OFFER_CNDTN ,
1681 X_MODE => 'R' ,
1682 X_SS_APPLICATION_ID => cur_appl_details_rec.SS_APPLICATION_ID ,
1683 X_SS_PWD => cur_appl_details_rec.SS_PWD ,
1684 X_AUTHORIZED_DT => cur_appl_details_rec.AUTHORIZED_DT ,
1685 X_AUTHORIZING_PERS_ID => cur_appl_details_rec.AUTHORIZING_PERS_ID ,
1686 x_entry_status => cur_appl_details_rec.entry_status ,
1687 x_entry_level => cur_appl_details_rec.entry_level ,
1688 x_sch_apl_to_id => cur_appl_details_rec.sch_apl_to_id ,
1689 x_idx_calc_date => cur_appl_details_rec.idx_calc_date ,
1690 X_WAITLIST_STATUS => cur_appl_details_rec.WAITLIST_STATUS ,
1691 x_attribute21 => cur_appl_details_rec.attribute21 ,
1692 x_attribute22 => cur_appl_details_rec.attribute22 ,
1693 x_attribute23 => cur_appl_details_rec.attribute23 ,
1694 x_attribute24 => cur_appl_details_rec.attribute24 ,
1695 x_attribute25 => cur_appl_details_rec.attribute25 ,
1696 x_attribute26 => cur_appl_details_rec.attribute26 ,
1697 x_attribute27 => cur_appl_details_rec.attribute27 ,
1698 x_attribute28 => cur_appl_details_rec.attribute28 ,
1699 x_attribute29 => cur_appl_details_rec.attribute29 ,
1700 x_attribute30 => cur_appl_details_rec.attribute30 ,
1701 x_attribute31 => cur_appl_details_rec.attribute31 ,
1702 x_attribute32 => cur_appl_details_rec.attribute32 ,
1703 x_attribute33 => cur_appl_details_rec.attribute33 ,
1704 x_attribute34 => cur_appl_details_rec.attribute34 ,
1705 x_attribute35 => cur_appl_details_rec.attribute35 ,
1706 x_attribute36 => cur_appl_details_rec.attribute36 ,
1707 x_attribute37 => cur_appl_details_rec.attribute37 ,
1708 x_attribute38 => cur_appl_details_rec.attribute38 ,
1709 x_attribute39 => cur_appl_details_rec.attribute39 ,
1710 x_attribute40 => cur_appl_details_rec.attribute40 ,
1711 x_fut_acad_cal_type => cur_appl_details_rec.future_acad_cal_type ,
1712 x_fut_acad_ci_sequence_number => cur_appl_details_rec.future_acad_ci_sequence_number ,
1713 x_fut_adm_cal_type => cur_appl_details_rec.future_adm_cal_type ,
1714 x_fut_adm_ci_sequence_number => cur_appl_details_rec.future_adm_ci_sequence_number ,
1715 x_prev_term_adm_appl_number => cur_appl_details_rec.previous_term_adm_appl_number ,
1716 x_prev_term_sequence_number => cur_appl_details_rec.previous_term_sequence_number ,
1717 x_fut_term_adm_appl_number => cur_appl_details_rec.future_term_adm_appl_number ,
1718 x_fut_term_sequence_number => cur_appl_details_rec.future_term_sequence_number ,
1719 x_def_acad_cal_type => cur_appl_details_rec.def_acad_cal_type ,
1720 x_def_acad_ci_sequence_num => cur_appl_details_rec.def_acad_ci_sequence_num ,
1721 x_def_prev_term_adm_appl_num => cur_appl_details_rec.def_prev_term_adm_appl_num ,
1722 x_def_prev_appl_sequence_num => cur_appl_details_rec.def_prev_appl_sequence_num ,
1723 x_def_term_adm_appl_num => cur_appl_details_rec.def_term_adm_appl_num ,
1724 x_def_appl_sequence_num => cur_appl_details_rec.def_appl_sequence_num ,
1725 x_appl_inst_status => cur_appl_details_rec.appl_inst_status,
1726 x_ais_reason => cur_appl_details_rec.ais_reason,
1727 x_decline_ofr_reason => cur_appl_details_rec.decline_ofr_reason
1728
1729 );
1730
1731
1732 OPEN cur_application_details (p_person_id,
1733 p_admission_appl_number,
1734 p_nominated_course_cd,
1735 p_sequence_number);
1736 FETCH cur_application_details INTO cur_application_details_rec;
1737 CLOSE cur_application_details;
1738
1739 -- rrengara
1740 -- on 28-oct-2002 for Build Core Vs optional Bug 2647482
1741 -- get the unit set mapping by calling the apc for the application
1742 -- then pass the value to the Pre-enrollment procedure for the parameter p_units_indicator
1743
1744
1745 IF igs_ad_upd_initialise.perform_pre_enrol(
1746 cur_application_details_rec.person_id,
1747 cur_application_details_rec.admission_appl_number,
1748 cur_application_details_rec.nominated_course_cd,
1749 cur_application_details_rec.sequence_number,
1750 'Y', -- Confirm course indicator.
1751 'Y', -- Perform eligibility check indicator.
1752 v_message_name) = FALSE THEN
1753 fnd_message.set_name('IGS', v_message_name);
1754 fnd_file.put_line(fnd_file.log, 'Application could not be created'); -- added - apadegal adtd001 igs.m
1755 fnd_file.put_line(fnd_file.log, fnd_message.get);
1756 RETURN FALSE ; -- added - apadegal adtd001 igs.m
1757 END IF;
1758
1759 RETURN TRUE; -- added - apadegal adtd001 igs.m
1760
1761 EXCEPTION WHEN OTHERS THEN
1762 -- fnd_file.put_line(fnd_file.log, 'Exception From Update offer response log ' || v_message_name);
1763
1764 -- begin - apadegal adtd001 igs.m
1765 fnd_file.put_line(fnd_file.log, 'Exception From Update offer response log ' || SQLERRM);
1766 RETURN FALSE;
1767 -- end - apadegal adtd001 igs.m
1768 END update_offer_response_accepted;
1769
1770 END igs_ad_def_appl_pkg;