[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_INT_RECONSIDER
Source
1 PACKAGE BODY igs_ad_int_reconsider AS
2 /* $Header: IGSADD6B.pls 120.3 2006/05/26 07:17:44 pfotedar noship $ */
3
4 FUNCTION copy_candidacy_records(p_new_admission_appl_number IGS_AD_APPL_ALL.admission_appl_number%TYPE,
5 p_new_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
6 p_person_id HZ_PARTIES.party_id%TYPE,
7 p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
8 p_old_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
9 p_nominated_course_cd IGS_AD_PS_APPL_ALL.nominated_course_cd%TYPE,
10 p_start_dt DATE)
11 RETURN BOOLEAN AS
12
13 /*******************************************************************************
14 Created by : Rishi Ghosh
15 Date created: 01-JUN-2004
16
17 Purpose: To copy candidacy records from the current applicationto the
18 Future-Term application. This procedure is also used for
19 creating deferment application. (Bug#3656905)
20
21
22 Known limitations/enhancements and/or remarks:
23
24 Change History: (who, when, what: )
25 Who When What
26 *******************************************************************************/
27
28 l_get_modified_comm_dt DATE;
29
30 CURSOR c_get_candidature (cp_person_id igs_re_candidature.person_id%TYPE,
31 cp_acai_admission_appl_number igs_re_candidature.acai_admission_appl_number%TYPE,
32 cp_acai_nominated_course_cd igs_re_candidature.acai_nominated_course_cd%TYPE,
33 cp_acai_sequence_number igs_re_candidature.acai_sequence_number%TYPE) IS
34 SELECT *
35 FROM igs_re_candidature
36 WHERE person_id = cp_person_id
37 AND acai_admission_appl_number = cp_acai_admission_appl_number
38 AND acai_nominated_course_cd = cp_acai_nominated_course_cd
39 AND acai_sequence_number = cp_acai_sequence_number;
40
41 CURSOR c_get_candidature_sequence IS
42 SELECT igs_re_candidature_seq_num_s.nextval
43 FROM dual;
44
45 l_get_candidature_sequence igs_re_candidature.sequence_number%TYPE;
46
47 CURSOR c_get_thesis (cp_person_id igs_re_thesis_all.person_id%TYPE,
48 cp_ca_sequence_number igs_re_thesis_all.ca_sequence_number%TYPE) IS
49 SELECT *
50 FROM igs_re_thesis_all
51 WHERE person_id = cp_person_id
52 AND ca_sequence_number = cp_ca_sequence_number;
53
54 CURSOR c_get_thesis_sequence IS
55 SELECT igs_re_thesis_seq_num_s.nextval
56 FROM dual;
57
58 l_get_thesis_sequence igs_re_thesis_all.sequence_number%TYPE;
59
60 CURSOR c_get_cdt_fld_of_sy (cp_person_id igs_re_cdt_fld_of_sy.person_id%TYPE,
61 cp_ca_sequence_number igs_re_cdt_fld_of_sy.ca_sequence_number%TYPE) IS
62 SELECT *
63 FROM igs_re_cdt_fld_of_sy
64 WHERE person_id = cp_person_id
65 AND ca_sequence_number = cp_ca_sequence_number;
66
67 CURSOR c_get_cand_seo_cls (cp_person_id igs_re_cand_seo_cls.person_id%TYPE,
68 cp_ca_sequence_number igs_re_cand_seo_cls.ca_sequence_number%TYPE) IS
69 SELECT *
70 FROM igs_re_cand_seo_cls
71 WHERE person_id = cp_person_id
72 AND ca_sequence_number = cp_ca_sequence_number;
73
74 CURSOR c_get_scholarship (cp_person_id igs_re_scholarship_all.person_id%TYPE,
75 cp_ca_sequence_number igs_re_scholarship_all.ca_sequence_number%TYPE) IS
76 SELECT *
77 FROM igs_re_scholarship_all
78 WHERE person_id = cp_person_id
79 AND ca_sequence_number = cp_ca_sequence_number;
80
81 CURSOR c_get_milestone (cp_person_id igs_pr_milestone_all.person_id%TYPE,
82 cp_ca_sequence_number igs_pr_milestone_all.ca_sequence_number%TYPE) IS
83 SELECT *
84 FROM igs_pr_milestone_all
85 WHERE person_id = cp_person_id
86 AND ca_sequence_number = cp_ca_sequence_number;
87
88 CURSOR c_get_supervisor (cp_ca_person_id igs_re_sprvsr.ca_person_id%TYPE,
89 cp_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE) IS
90 SELECT *
91 FROM igs_re_sprvsr
92 WHERE ca_person_id = cp_ca_person_id
93 AND ca_sequence_number = cp_ca_sequence_number
94 ORDER BY start_dt,end_dt;
95
96 CURSOR c_get_thesis_exam (cp_person_id igs_re_thesis_exam.person_id%TYPE,
97 cp_ca_sequence_number igs_re_thesis_exam.ca_sequence_number%TYPE,
98 cp_the_sequence_number igs_re_thesis_exam.the_sequence_number%TYPE) IS
99 SELECT *
100 FROM igs_re_thesis_exam
101 WHERE person_id = cp_person_id
102 AND ca_sequence_number = cp_ca_sequence_number
103 AND the_sequence_number = cp_the_sequence_number;
104
105 CURSOR c_get_thesis_panel_memb (cp_ca_person_id igs_re_ths_pnl_mbr.ca_person_id%TYPE,
106 cp_ca_sequence_number igs_re_ths_pnl_mbr.ca_sequence_number%TYPE,
107 cp_the_sequence_number igs_re_ths_pnl_mbr.the_sequence_number%TYPE,
108 cp_creation_dt igs_re_ths_pnl_mbr.creation_dt%TYPE) IS
109 SELECT *
110 FROM igs_re_ths_pnl_mbr
111 WHERE ca_person_id = cp_ca_person_id
112 AND ca_sequence_number = cp_ca_sequence_number
113 AND the_sequence_number = cp_the_sequence_number
114 AND creation_dt = cp_creation_dt;
115
116
117 l_rowid VARCHAR2(30);
118 l_rowid_child1 VARCHAR2(30);
119 l_rowid_child2 VARCHAR2(30);
120 l_rowid_child3 VARCHAR2(30);
121
122 l_min_submission_dt igs_re_candidature.min_submission_dt%TYPE;
123 l_max_submission_dt igs_re_candidature.max_submission_dt%TYPE;
124
125 l_supvsr_start_dt igs_re_sprvsr.start_dt%TYPE;
126 l_supvsr_end_dt igs_re_sprvsr.end_dt%TYPE;
127 l_comm_date_offset NUMBER;
128
129
130 l_submission_dt igs_re_thesis_exam.submission_dt%TYPE;
131 l_expected_submission_dt igs_re_thesis.expected_submission_dt%TYPE;
132 l_embargo_expiry_dt igs_re_thesis.embargo_expiry_dt%TYPE;
133
134 l_sysdate DATE ;
135
136 v_message_name VARCHAR2(30);
137
138
139 BEGIN
140
141 l_sysdate := SYSDATE;
142
143
144 l_get_modified_comm_dt := IGS_RE_GEN_001.RESP_GET_CA_COMM(
145 p_person_id,
146 NULL,
147 p_old_admission_appl_number,
148 p_nominated_course_cd,
149 p_old_sequence_number);
150
151
152 FOR l_get_candidature_rec IN c_get_candidature(p_person_id,
153 p_old_admission_appl_number,
154 p_nominated_course_cd,
155 p_old_sequence_number) LOOP
156
157 v_message_name:= 'IGS_AD_RSCH_CAND';
158
159
160 l_comm_date_offset := TRUNC(p_start_dt) - TRUNC(l_get_modified_comm_dt);
161
162 l_min_submission_dt := l_get_candidature_rec.min_submission_dt + l_comm_date_offset;
163 l_max_submission_dt := l_get_candidature_rec.max_submission_dt + l_comm_date_offset;
164
165 OPEN c_get_candidature_sequence;
166 FETCH c_get_candidature_sequence INTO l_get_candidature_sequence;
167 CLOSE c_get_candidature_sequence;
168
169 l_rowid := NULL;
170
171 IGS_RE_CANDIDATURE_PKG.INSERT_ROW(
172 X_ROWID => l_rowid,
173 X_PERSON_ID => p_person_id,
174 X_SEQUENCE_NUMBER => l_get_candidature_sequence,
175 X_SCA_COURSE_CD => l_get_candidature_rec.sca_course_cd,
176 X_ACAI_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
177 X_ACAI_NOMINATED_COURSE_CD => p_nominated_course_cd,
178 X_ACAI_SEQUENCE_NUMBER => p_new_sequence_number,
179 X_ATTENDANCE_PERCENTAGE => l_get_candidature_rec.attendance_percentage,
180 X_GOVT_TYPE_OF_ACTIVITY_CD => l_get_candidature_rec.govt_type_of_activity_cd,
181 X_MAX_SUBMISSION_DT => l_max_submission_dt,
182 X_MIN_SUBMISSION_DT => l_min_submission_dt,
183 X_RESEARCH_TOPIC => l_get_candidature_rec.research_topic,
184 X_INDUSTRY_LINKS => l_get_candidature_rec.industry_links,
185 X_MODE => 'R',
186 X_ORG_ID => l_get_candidature_rec.org_id
187 );
188
189 FOR l_get_thesis_rec IN c_get_thesis(p_person_id,l_get_candidature_rec.sequence_number) LOOP
190
191 v_message_name:= 'IGS_AD_RSCH_THS';
192
193 l_expected_submission_dt := l_get_thesis_rec.expected_submission_dt + l_comm_date_offset;
194 l_embargo_expiry_dt := l_get_thesis_rec.embargo_expiry_dt + l_comm_date_offset;
195
196 IF l_get_thesis_rec.logical_delete_dt IS NULL THEN
197
198 OPEN c_get_thesis_sequence;
199 FETCH c_get_thesis_sequence INTO l_get_thesis_sequence;
200 CLOSE c_get_thesis_sequence;
201
202 l_rowid_child1 := NULL;
203
204 IGS_RE_THESIS_PKG.INSERT_ROW(
205 X_ROWID => l_rowid_child1,
206 X_PERSON_ID => p_person_id,
207 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
208 X_SEQUENCE_NUMBER => l_get_thesis_sequence,
209 X_TITLE => l_get_thesis_rec.title,
210 X_FINAL_TITLE_IND => l_get_thesis_rec.final_title_ind,
211 X_SHORT_TITLE => l_get_thesis_rec.short_title,
212 X_ABBREVIATED_TITLE => l_get_thesis_rec.abbreviated_title,
213 X_THESIS_RESULT_CD => l_get_thesis_rec.thesis_result_cd,
214 X_EXPECTED_SUBMISSION_DT => l_expected_submission_dt,
215 X_LIBRARY_LODGEMENT_DT => l_get_thesis_rec.library_lodgement_dt,
216 X_LIBRARY_CATALOGUE_NUMBER => l_get_thesis_rec.library_catalogue_number,
217 X_EMBARGO_EXPIRY_DT => l_get_thesis_rec.embargo_expiry_dt,
218 X_THESIS_FORMAT => l_get_thesis_rec.thesis_format,
219 X_LOGICAL_DELETE_DT => l_get_thesis_rec.logical_delete_dt,
220 X_EMBARGO_DETAILS => l_embargo_expiry_dt,
221 X_THESIS_TOPIC => l_get_thesis_rec.thesis_topic,
222 X_CITATION => l_get_thesis_rec.citation,
223 X_COMMENTS => l_get_thesis_rec.comments,
224 X_MODE => 'R',
225 X_ORG_ID => l_get_thesis_rec.org_id
226 );
227
228 FOR l_get_thesis_exam_rec IN c_get_thesis_exam (p_person_id,
229 l_get_candidature_rec.sequence_number,
230 l_get_thesis_rec.sequence_number) LOOP
231
232 v_message_name:= 'IGS_AD_RSCH_EXAM';
233
234 l_submission_dt := l_get_thesis_exam_rec.submission_dt + l_comm_date_offset;
235 l_rowid_child2 := NULL;
236
237 IGS_RE_THESIS_EXAM_PKG.INSERT_ROW(
238 X_ROWID => l_rowid_child2,
239 X_PERSON_ID => p_person_id,
240 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
241 X_THE_SEQUENCE_NUMBER => l_get_thesis_sequence,
242 X_CREATION_DT => l_sysdate,
243 X_SUBMISSION_DT => l_submission_dt,
244 X_THESIS_EXAM_TYPE => l_get_thesis_exam_rec.thesis_exam_type,
245 X_THESIS_PANEL_TYPE => l_get_thesis_exam_rec.thesis_panel_type,
246 X_TRACKING_ID => l_get_thesis_exam_rec.tracking_id,
247 X_THESIS_RESULT_CD => l_get_thesis_exam_rec.thesis_result_cd,
248 X_MODE => 'R'
249 );
250
251 FOR l_get_thesis_panel_memb_rec IN c_get_thesis_panel_memb (p_person_id,
252 l_get_candidature_rec.sequence_number,
253 l_get_thesis_rec.sequence_number,
254 l_get_thesis_exam_rec.creation_dt) LOOP
255
256 v_message_name:= 'IGS_AD_RSCH_EXAM_PNL_MEM';
257
258 l_rowid_child3 := NULL;
259
260 IGS_RE_THS_PNL_MBR_PKG.INSERT_ROW(
261 X_ROWID => l_rowid_child3,
262 X_CA_PERSON_ID => p_person_id,
263 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
264 X_THE_SEQUENCE_NUMBER => l_get_thesis_sequence,
265 X_CREATION_DT => l_sysdate,
266 X_PERSON_ID => l_get_thesis_panel_memb_rec.person_id,
267 X_PANEL_MEMBER_TYPE => l_get_thesis_panel_memb_rec.panel_member_type,
268 X_CONFIRMED_DT => l_get_thesis_panel_memb_rec.confirmed_dt,
269 X_DECLINED_DT => l_get_thesis_panel_memb_rec.declined_dt,
270 X_ANONYMITY_IND => l_get_thesis_panel_memb_rec.anonymity_ind,
271 X_THESIS_RESULT_CD => l_get_thesis_panel_memb_rec.thesis_result_cd,
272 X_PAID_DT => l_get_thesis_panel_memb_rec.paid_dt,
273 X_TRACKING_ID => l_get_thesis_panel_memb_rec.tracking_id,
274 X_RECOMMENDATION_SUMMARY => l_get_thesis_panel_memb_rec.recommendation_summary,
275 X_MODE => 'R'
276 );
277
278 END LOOP;
279
280 END LOOP;
281
282 END IF;
283
284 END LOOP;
285
286 FOR l_get_cdt_fld_of_sy_rec IN c_get_cdt_fld_of_sy(p_person_id,l_get_candidature_rec.sequence_number) LOOP
287
288 v_message_name:= 'IGS_AD_RSCH_CAND_FOS';
289
290 l_rowid_child1 := NULL;
291
292 IGS_RE_CDT_FLD_OF_SY_PKG.INSERT_ROW(
293 X_ROWID => l_rowid_child1,
294 X_PERSON_ID => p_person_id,
295 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
296 X_FIELD_OF_STUDY => l_get_cdt_fld_of_sy_rec.field_of_study,
297 X_PERCENTAGE => l_get_cdt_fld_of_sy_rec.percentage,
298 X_MODE => 'R'
299 );
300
301 END LOOP;
302
303 FOR l_get_cand_seo_cls_rec IN c_get_cand_seo_cls(p_person_id,l_get_candidature_rec.sequence_number) LOOP
304
305 v_message_name:= 'IGS_AD_RSCH_SEO';
306
307 l_rowid_child1 := NULL;
308
309 IGS_RE_CAND_SEO_CLS_PKG.INSERT_ROW(
310 X_ROWID => l_rowid_child1,
311 X_PERSON_ID => p_person_id,
312 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
313 X_SEO_CLASS_CD => l_get_cand_seo_cls_rec.seo_class_cd,
314 X_PERCENTAGE => l_get_cand_seo_cls_rec.percentage,
315 X_MODE => 'R'
316 );
317
318 END LOOP;
319
320 FOR l_get_scholarship_rec IN c_get_scholarship(p_person_id,l_get_candidature_rec.sequence_number) LOOP
321
322 v_message_name:= 'IGS_AD_RSCH_SCH';
323
324 l_rowid_child1 := NULL;
325
326 IGS_RE_SCHOLARSHIP_PKG.INSERT_ROW(
327 X_ROWID => l_rowid_child1,
328 X_PERSON_ID => p_person_id,
329 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
330 X_SCHOLARSHIP_TYPE => l_get_scholarship_rec.scholarship_type,
331 X_START_DT => l_get_scholarship_rec.start_dt,
332 X_END_DT => l_get_scholarship_rec.end_dt,
333 X_DOLLAR_VALUE => l_get_scholarship_rec.dollar_value,
334 X_DESCRIPTION => l_get_scholarship_rec.description,
335 X_OTHER_BENEFITS => l_get_scholarship_rec.other_benefits,
336 X_CONDITIONS => l_get_scholarship_rec.conditions,
337 X_MODE => 'R',
338 X_ORG_ID => l_get_scholarship_rec.org_id
339 );
340
341 END LOOP;
342
343 FOR l_get_milestone_rec IN c_get_milestone(p_person_id,l_get_candidature_rec.sequence_number) LOOP
344
345 v_message_name:= 'IGS_AD_RSCH_PR_MLSTN';
346
347 l_rowid_child1 := NULL;
348
349 IGS_PR_MILESTONE_PKG.INSERT_ROW(
350 X_ROWID => l_rowid_child1,
351 X_PERSON_ID => p_person_id,
352 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
353 X_SEQUENCE_NUMBER => l_get_milestone_rec.sequence_number,
354 X_MILESTONE_TYPE => l_get_milestone_rec.milestone_type,
355 X_MILESTONE_STATUS => l_get_milestone_rec.milestone_status,
356 X_DUE_DT => l_get_milestone_rec.due_dt,
357 X_DESCRIPTION => l_get_milestone_rec.description,
358 X_ACTUAL_REACHED_DT => l_get_milestone_rec.actual_reached_dt,
359 X_PRECED_SEQUENCE_NUMBER => l_get_milestone_rec.preced_sequence_number,
360 X_OVRD_NTFCTN_IMMINENT_DAYS => l_get_milestone_rec.ovrd_ntfctn_imminent_days,
361 X_OVRD_NTFCTN_REMINDER_DAYS => l_get_milestone_rec.ovrd_ntfctn_reminder_days,
362 X_OVRD_NTFCTN_RE_REMINDER_DAYS => l_get_milestone_rec.ovrd_ntfctn_re_reminder_days,
363 X_COMMENTS => l_get_milestone_rec.comments,
364 X_MODE => 'R',
365 X_ORG_ID => l_get_milestone_rec.org_id
366 );
367
368 END LOOP;
369
370 FOR l_get_supervisor_rec IN c_get_supervisor(p_person_id,l_get_candidature_rec.sequence_number) LOOP
371
372 v_message_name:= 'IGS_AD_RSCH_SPVSR';
373
374 l_supvsr_start_dt := l_get_supervisor_rec.start_dt + l_comm_date_offset;
375 l_supvsr_end_dt := l_get_supervisor_rec.end_dt + l_comm_date_offset;
376
377 l_rowid_child1 := NULL;
378
379 IGS_RE_SPRVSR_PKG.INSERT_ROW(
380 X_ROWID => l_rowid_child1,
381 X_CA_PERSON_ID => p_person_id,
382 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
383 X_PERSON_ID => l_get_supervisor_rec.person_id,
384 X_SEQUENCE_NUMBER => l_get_supervisor_rec.sequence_number,
385 X_START_DT => l_supvsr_start_dt,
386 X_END_DT => l_supvsr_end_dt,
387 X_RESEARCH_SUPERVISOR_TYPE => l_get_supervisor_rec.research_supervisor_type,
388 X_SUPERVISOR_PROFESSION => l_get_supervisor_rec.supervisor_profession,
389 X_SUPERVISION_PERCENTAGE => l_get_supervisor_rec.supervision_percentage,
390 X_FUNDING_PERCENTAGE => l_get_supervisor_rec.funding_percentage,
391 X_ORG_UNIT_CD => l_get_supervisor_rec.org_unit_cd,
392 X_OU_START_DT => l_get_supervisor_rec.ou_start_dt,
393 X_REPLACED_PERSON_ID => l_get_supervisor_rec.replaced_person_id,
394 X_REPLACED_SEQUENCE_NUMBER => l_get_supervisor_rec.replaced_sequence_number,
395 X_COMMENTS => l_get_supervisor_rec.comments,
396 X_MODE => 'R'
397 );
398
399
400 END LOOP;
401
402 END LOOP;
403
404 RETURN TRUE;
405
406 EXCEPTION WHEN OTHERS THEN
407
408 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CAND_COPY_FAIL');
409 Fnd_File.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
410
411 FND_MESSAGE.SET_NAME('IGS',v_message_name);
412 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET || SQLERRM);
413
414 RETURN FALSE;
415
416 END copy_candidacy_records;
417
418
419 FUNCTION copy_application_child_records (p_person_id HZ_PARTIES.party_id%TYPE,
420 p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
421 p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
422 p_nominated_course_cd IGS_AD_PS_APPL_INST_ALL.nominated_course_cd%TYPE,
423 p_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE)
424 RETURN BOOLEAN IS
425
426 -- other institutitons
427
428 CURSOR c_othinst_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
429 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
430 IS
431 SELECT
432 other_inst_id,
433 person_id ,
434 admission_appl_number ,
435 nominated_course_cd ,
436 sequence_number ,
437 institution_code,
438 new_institution
439 FROM
440 igs_ad_other_inst
441 WHERE
442 person_id = cp_person_id and
443 admission_appl_number = cp_admission_appl_number;
444
445
446 -- personal statements
447 CURSOR c_perstat_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
448 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
449 IS
450 SELECT
451 appl_perstat_id,
452 person_id ,
453 admission_appl_number ,
454 persl_stat_type ,
455 date_received
456 FROM
457 igs_ad_appl_perstat
458 WHERE
459 person_id = cp_person_id and
460 admission_appl_number = cp_admission_appl_number;
461
462
463 -- academic interests
464 CURSOR c_acadint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
465 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
466 IS
467 SELECT
468 acad_interest_id ,
469 person_id ,
470 admission_appl_number ,
471 field_of_study
472 FROM
473 igs_ad_acad_interest
474 WHERE
475 person_id = cp_person_id and
476 admission_appl_number = cp_admission_appl_number;
477
478 --applicant intent
479 CURSOR c_appint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
480 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
481 IS
482 SELECT
483 app_intent_id,
484 person_id ,
485 admission_appl_number,
486 intent_type_id ,
487 attribute_category,
488 attribute1 ,
489 attribute2 ,
490 attribute3 ,
491 attribute4 ,
492 attribute5 ,
493 attribute6 ,
494 attribute7 ,
495 attribute8 ,
496 attribute9 ,
497 attribute10 ,
498 attribute11 ,
499 attribute12 ,
500 attribute13 ,
501 attribute14 ,
502 attribute15 ,
503 attribute16 ,
504 attribute17 ,
505 attribute18 ,
506 attribute19 ,
507 attribute20
508 FROM
509 igs_ad_app_intent
510 WHERE
511 person_id = cp_person_id and
512 admission_appl_number = cp_admission_appl_number;
513
514
515 -- Special Interests
516 CURSOR c_splint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
517 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
518 IS
519 SELECT
520 spl_interest_id ,
521 person_id ,
522 admission_appl_number ,
523 special_interest_type_id
524 FROM
525 igs_ad_spl_interests
526 WHERE
527 person_id = cp_person_id and
528 admission_appl_number = cp_admission_appl_number;
529
530 --spl talents
531 CURSOR c_spltal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
532 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
533 IS
534 SELECT
535 spl_talent_id ,
536 person_id ,
537 admission_appl_number ,
538 special_talent_type_id
539 FROM
540 igs_ad_spl_talents
541 WHERE
542 person_id = cp_person_id and
543 admission_appl_number = cp_admission_appl_number;
544
545 -- Fees
546 CURSOR c_fee_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
547 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE
548 )IS
549 SELECT
550 app_req_id ,
551 person_id ,
552 admission_appl_number ,
553 applicant_fee_type,
554 applicant_fee_status,
555 fee_date,
556 fee_payment_method,
557 fee_amount,
558 reference_num,
559 credit_card_code,
560 credit_card_holder_name,
561 credit_card_number,
562 credit_card_expiration_date,
563 rev_gl_ccid,
564 cash_gl_ccid,
565 rev_account_cd,
566 cash_account_cd,
567 gl_date,
568 gl_posted_date,
569 posting_control_id,
570 credit_card_tangible_cd,
571 credit_card_payee_cd,
572 credit_card_status_code
573 FROM
574 igs_ad_app_req
575 WHERE
576 person_id = cp_person_id AND
577 admission_appl_number = cp_admission_appl_number;
578
579 l_last_error VARCHAR2(50);
580
581 l_rowid ROWID;
582
583 l_primary_key NUMBER(15);
584
585 BEGIN
586
587 --OTHER INSTITUTIONS
588
589 FOR c_othinst_rec IN c_othinst_cur(p_person_id, p_old_admission_appl_number) LOOP
590 l_last_error := 'IGS_AD_CHILD_OTH_INST';
591 IGS_AD_OTHER_INST_PKG.INSERT_ROW(
592 X_ROWID => l_rowid,
593 X_OTHER_INST_ID => l_primary_key,
594 X_PERSON_ID => c_othinst_rec.person_id,
595 X_ADMISSION_APPL_NUMBER =>p_new_admission_appl_number,
596 X_INSTITUTION_CODE => c_othinst_rec.institution_code,
597 X_MODE => 'R',
598 X_NEW_INSTITUTION => c_othinst_rec.new_institution );
599 END LOOP;
600
601 l_rowid := NULL;
602
603 -- Personal Statements
604 FOR c_perstat_rec IN c_perstat_cur(p_person_id, p_old_admission_appl_number) LOOP
605 l_last_error := 'IGS_AD_CHILD_PER_STAT';
606 igs_ad_appl_perstat_pkg.insert_row(
607 x_rowid => l_rowid,
608 x_appl_perstat_id => c_perstat_rec.appl_perstat_id,
609 x_person_id => c_perstat_rec.person_id,
610 x_admission_appl_number => p_new_admission_appl_number,
611 x_persl_stat_type => c_perstat_rec.persl_stat_type,
612 x_date_received => c_perstat_rec.date_received,
613 x_mode => 'R');
614 END LOOP;
615
616
617 l_rowid := NULL;
618
619 -- ACADMEIC INTERESTS
620 FOR c_acadint_rec IN c_acadint_cur(p_person_id, p_old_admission_appl_number) LOOP
621 l_last_error := 'IGS_AD_CHILD_ACAD_INT';
622 igs_ad_acad_interest_pkg.INSERT_ROW(
623 X_ROWID => l_rowid,
624 X_ACAD_INTEREST_ID => l_primary_key,
625 X_PERSON_ID => c_acadint_rec.person_id,
626 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
627 X_FIELD_OF_STUDY => c_acadint_rec.field_of_study,
628 X_MODE => 'R');
629 END LOOP;
630
631
632 l_rowid := NULL;
633
634
635 --applicant intent
636 FOR c_appint_rec IN c_appint_cur(p_person_id, p_old_admission_appl_number) LOOP
637 l_last_error := 'IGS_AD_CHILD_APPL_INTENT';
638 igs_ad_app_intent_pkg.INSERT_ROW(
639 X_ROWID => l_rowid,
640 X_APP_INTENT_ID => l_primary_key,
641 X_PERSON_ID => c_appint_rec.person_id,
642 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
643 X_INTENT_TYPE_ID => c_appint_rec.intent_type_id,
644 X_ATTRIBUTE_CATEGORY => c_appint_rec.attribute_category,
645 X_ATTRIBUTE1 => c_appint_rec.attribute1,
646 X_ATTRIBUTE2 => c_appint_rec.attribute2,
647 X_ATTRIBUTE3 => c_appint_rec.attribute3,
648 X_ATTRIBUTE4 => c_appint_rec.attribute4 ,
649 X_ATTRIBUTE5 => c_appint_rec.attribute5,
650 X_ATTRIBUTE6 => c_appint_rec.attribute6,
651 X_ATTRIBUTE7 => c_appint_rec.attribute7 ,
652 X_ATTRIBUTE8 => c_appint_rec.attribute8 ,
653 X_ATTRIBUTE9 => c_appint_rec.attribute9 ,
654 X_ATTRIBUTE10 => c_appint_rec.attribute10 ,
655 X_ATTRIBUTE11 => c_appint_rec.attribute11 ,
656 X_ATTRIBUTE12 => c_appint_rec.attribute12 ,
657 X_ATTRIBUTE13 => c_appint_rec.attribute13 ,
658 X_ATTRIBUTE14 => c_appint_rec.attribute14 ,
659 X_ATTRIBUTE15 => c_appint_rec.attribute15 ,
660 X_ATTRIBUTE16 => c_appint_rec.attribute16 ,
661 X_ATTRIBUTE17 => c_appint_rec.attribute17 ,
662 X_ATTRIBUTE18 => c_appint_rec.attribute18 ,
663 X_ATTRIBUTE19 => c_appint_rec.attribute19 ,
664 X_ATTRIBUTE20 => c_appint_rec.attribute20 ,
665 X_MODE => 'R');
666 END LOOP;
667
668 l_rowid := NULL;
669
670
671 -- Special Interests
672 FOR c_splint_rec IN c_splint_cur(p_person_id, p_old_admission_appl_number) LOOP
673 l_last_error := 'IGS_AD_CHILD_SPL_INT';
674 igs_ad_spl_interests_PKG.INSERT_ROW(
675 X_ROWID => l_rowid,
676 X_SPL_INTEREST_ID => l_primary_key,
677 X_PERSON_ID => c_splint_rec.person_id,
678 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
679 X_SPECIAL_INTEREST_TYPE_ID => c_splint_rec.special_interest_type_id,
680 X_MODE => 'R');
681 END LOOP;
682
683 l_rowid := NULL;
684
685 -- Special Talents
686 FOR c_spltal_rec IN c_spltal_cur(p_person_id, p_old_admission_appl_number) LOOP
687 l_last_error := 'IGS_AD_CHILD_SPL_TAL';
688 igs_ad_spl_talents_PKG.INSERT_ROW(
689 X_ROWID => l_rowid,
690 X_SPL_TALENT_ID => l_primary_key,
691 X_PERSON_ID => c_spltal_rec.person_id,
692 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
693 X_SPECIAL_TALENT_TYPE_ID => c_spltal_rec.special_talent_type_id,
694 X_MODE => 'R');
695 END LOOP;
696
697 l_rowid := NULL;
698
699
700 -- Fess ( Requirements )
701
702
703 FOR c_fee_rec IN c_fee_cur(p_person_id, p_old_admission_appl_number) LOOP
704 l_last_error := 'IGS_AD_CHILD_FEE_DET';
705
706 igs_ad_app_req_pkg.insert_row(
707 x_rowid => l_rowid,
708 x_app_req_id => l_primary_key,
709 x_person_id => c_fee_rec.person_id,
710 x_admission_appl_number => p_new_admission_appl_number,
711 x_applicant_fee_type => c_fee_rec.applicant_fee_type,
712 x_applicant_fee_status => c_fee_rec.applicant_fee_status,
713 x_fee_date => c_fee_rec.fee_date,
714 x_fee_payment_method => c_fee_rec.fee_payment_method,
715 x_fee_amount => c_fee_rec.fee_amount,
716 x_reference_num => c_fee_rec.reference_num,
717 x_mode => 'R',
718 x_credit_card_code => c_fee_rec.credit_card_code,
719 x_credit_card_holder_name => c_fee_rec.credit_card_holder_name,
720 x_credit_card_number => c_fee_rec.credit_card_number,
721 x_credit_card_expiration_date => c_fee_rec.credit_card_expiration_date,
722 x_rev_gl_ccid => c_fee_rec.rev_gl_ccid,
723 x_cash_gl_ccid => c_fee_rec.cash_gl_ccid,
724 x_rev_account_cd => c_fee_rec.rev_account_cd,
725 x_cash_account_cd => c_fee_rec.cash_account_cd,
726 x_gl_date => c_fee_rec.gl_date,
727 x_gl_posted_date => c_fee_rec.gl_posted_date,
728 x_posting_control_id => c_fee_rec.posting_control_id,
729 x_credit_card_tangible_cd => c_fee_rec.credit_card_tangible_cd,
730 x_credit_card_payee_cd => c_fee_rec.credit_card_payee_cd,
731 x_credit_card_status_code => c_fee_rec.credit_card_status_code
732 );
733 END LOOP;
734
735 l_rowid := NULL;
736
737 RETURN TRUE;
738
739 EXCEPTION WHEN OTHERS THEN
740
741 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(p_person_id,15,' ') || '; Admission Application Number: ' ||
742 RPAD(p_old_admission_appl_number,2,' ') || '; Course Code: ' || RPAD(p_nominated_course_cd,6,' ') || '; Sequence Number: '||
743 RPAD(p_sequence_number,6,' ') || ' Reason: ');
744
745 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CHILD_COPY_FAILED');
746 Fnd_File.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
747
748 FND_MESSAGE.SET_NAME('IGS',l_last_error);
749 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET || SQLERRM);
750
751 RETURN FALSE;
752 END copy_application_child_records;
753
754 FUNCTION copy_instance_child_records (p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
755 p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
756 p_person_id HZ_PARTIES.party_id%TYPE,
757 p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
758 p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
759 p_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
760 p_start_dt DATE)
761 RETURN BOOLEAN IS
762 /*******************************************************************************
763 Created by : Ramesh Rengarajan
764 Date created: 20 FEB 2002
765
766 Purpose:
767 To create future term application
768
769 Known limitations/enhancements and/or remarks:
770
771 Change History: (who, when, what: )
772 Who When What
773 *******************************************************************************/
774 -------------------------Cursor Declarations-------------------------------------------------------------------
775 -- Unitsets
776 CURSOR c_unitsets_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
777 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
778 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
779 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
780 IS
781 SELECT
782 unit_set_id,
783 person_id ,
784 admission_appl_number ,
785 nominated_course_cd,
786 sequence_number,
787 unit_set_cd,
788 version_number,
789 rank
790 FROM
791 igs_ad_unit_sets
792 WHERE
793 person_id = cp_person_id and
794 nominated_course_cd = cp_nominated_course_cd and
795 sequence_number = cp_sequence_number and
796 admission_appl_number = cp_admission_appl_number;
797
798
799
800 -- education goals
801 CURSOR c_edugoal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
802 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
803 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
804 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
805 IS
806 SELECT
807 post_edugoal_id ,
808 person_id ,
809 admission_appl_number ,
810 nominated_course_cd,
811 sequence_number ,
812 edu_goal_id
813 FROM
814 igs_ad_edugoal
815 WHERE
816 person_id = cp_person_id and
817 nominated_course_cd = cp_nominated_course_cd and
818 sequence_number = cp_sequence_number and
819 admission_appl_number = cp_admission_appl_number;
820
821 -- rrengara
822 -- for Build Movement Academic Honors Bug 2647482
823 -- on 28-oct-2002
824 --
825 -- academic honors has been moved to Person
826 -- So cursor to select the values from the old applicaiton has been removed
827
828
829 -- special consideration
830 CURSOR c_splcns_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
831 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
832 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
833 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
834 IS
835 SELECT
836 spl_adm_cat_id ,
837 person_id ,
838 admission_appl_number ,
839 nominated_course_cd ,
840 sequence_number ,
841 spl_adm_cat
842 FROM
843 igs_ad_spl_adm_cat
844 WHERE
845 person_id = cp_person_id and
846 nominated_course_cd = cp_nominated_course_cd and
847 sequence_number = cp_sequence_number and
848 admission_appl_number = cp_admission_appl_number;
849
850
851 -- Trackings
852 CURSOR c_tracking_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
853 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
854 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
855 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
856 IS
857 SELECT
858 aplins_admreq_id ,
859 person_id ,
860 admission_appl_number,
861 course_cd ,
862 sequence_number ,
863 tracking_id
864 FROM
865 igs_ad_aplins_admreq
866 WHERE
867 person_id = cp_person_id and
868 course_cd = cp_nominated_course_cd and
869 sequence_number = cp_sequence_number and
870 admission_appl_number = cp_admission_appl_number;
871
872 -- program approval
873 CURSOR c_pgmapp_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
874 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
875 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
876 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
877 IS
878 SELECT
879 appl_pgmapprv_id ,
880 person_id ,
881 admission_appl_number,
882 nominated_course_cd ,
883 sequence_number ,
884 pgm_approver_id ,
885 assign_type ,
886 assign_date ,
887 program_approval_date,
888 program_approval_status,
889 approval_notes
890 FROM
891 igs_ad_appl_pgmapprv
892 WHERE
893 person_id = cp_person_id and
894 nominated_course_cd = cp_nominated_course_cd and
895 sequence_number = cp_sequence_number and
896 admission_appl_number = cp_admission_appl_number;
897
898 -- Test Scores
899 CURSOR c_test_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
900 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
901 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
902 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
903 IS
904 SELECT
905 tstscr_used_id ,
906 comments ,
907 person_id ,
908 admission_appl_number,
909 nominated_course_cd ,
910 sequence_number ,
911 attribute_category ,
912 attribute1 ,
913 attribute2 ,
914 attribute3 ,
915 attribute4 ,
916 attribute5 ,
917 attribute6 ,
918 attribute7 ,
919 attribute8 ,
920 attribute9 ,
921 attribute10 ,
922 attribute11 ,
923 attribute12 ,
924 attribute13 ,
925 attribute14 ,
926 attribute15 ,
927 attribute16 ,
928 attribute17 ,
929 attribute18 ,
930 attribute19 ,
931 attribute20
932 FROM
933 igs_ad_tstscr_used
934 WHERE
935 person_id = cp_person_id and
936 nominated_course_cd = cp_nominated_course_cd and
937 sequence_number = cp_sequence_number and
938 admission_appl_number = cp_admission_appl_number;
939
940
941 -- notes
942 CURSOR c_notes_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
943 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
944 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
945 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
946 IS
947 SELECT
948 appl_notes_id ,
949 person_id ,
950 admission_appl_number ,
951 nominated_course_cd ,
952 sequence_number ,
953 note_type_id ,
954 ref_notes_id
955 FROM
956 igs_ad_appl_notes
957 WHERE
958 person_id = cp_person_id and
959 nominated_course_cd = cp_nominated_course_cd and
960 sequence_number = cp_sequence_number and
961 admission_appl_number = cp_admission_appl_number;
962
963 CURSOR c_evaluators_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
964 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
965 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
966 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
967
968 IS
969 SELECT
970 appl_eval_id,
971 person_id ,
972 admission_appl_number,
973 nominated_course_cd ,
974 sequence_number ,
975 evaluator_id ,
976 assign_type ,
977 assign_date ,
978 evaluation_date ,
979 rating_type_id ,
980 rating_values_id ,
981 rating_notes ,
982 evaluation_sequence ,
983 rating_scale_id,
984 closed_ind -- added the closed ind check -- rghosh (bug#2871426)
985 FROM
986 igs_ad_appl_eval
987 WHERE
988 person_id = cp_person_id and
989 nominated_course_cd = cp_nominated_course_cd and
990 sequence_number = cp_sequence_number and
991 admission_appl_number = cp_admission_appl_number;
992
993
994 CURSOR c_applrep_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
995 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
996 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
997 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
998
999 IS
1000 SELECT
1001 appl_arp_id ,
1002 person_id ,
1003 admission_appl_number ,
1004 nominated_course_cd ,
1005 sequence_number ,
1006 appl_rev_profile_id ,
1007 appl_revprof_revgr_id
1008 FROM
1009 igs_ad_appl_arp
1010 WHERE
1011 person_id = cp_person_id and
1012 nominated_course_cd = cp_nominated_course_cd and
1013 sequence_number = cp_sequence_number and
1014 admission_appl_number = cp_admission_appl_number;
1015
1016 -- bug 2395510 ( Deferment changes)
1017 -- by rrengara on 12-SEP-2002
1018
1019 CURSOR c_fee_status IS
1020 SELECT
1021 ccl.code_id
1022 FROM
1023 igs_ad_code_classes ccl , igs_lookup_values lkup
1024 WHERE
1025 ccl.system_status = lkup.lookup_code
1026 AND lkup.lookup_type = 'SYS_FEE_STATUS'
1027 AND ccl.system_status = 'WAIVED'
1028 AND ccl.system_default = 'Y';
1029
1030 CURSOR c_appl_qual_code(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1031 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1032 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1033 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) IS
1034 SELECT qual.*
1035 FROM igs_ad_appqual_code qual
1036 WHERE person_id = cp_person_id
1037 AND nominated_course_cd = cp_nominated_course_cd
1038 AND sequence_number = cp_sequence_number
1039 AND admission_appl_number = cp_admission_appl_number;
1040
1041 CURSOR c_intvw_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1042 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1043 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nomINATED_COURSE_CD%TYPE,
1044 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
1045 SELECT pndt.*
1046 FROM igs_ad_panel_dtls pndt
1047 WHERE person_id = cp_person_id
1048 AND admission_appl_number = cp_admission_appl_number
1049 AND nominated_course_cd = cp_nominated_course_cd
1050 AND sequence_number = cp_sequence_number;
1051
1052 CURSOR c_intvw_pnmem_dtls (cp_panel_dtls_id igs_ad_pnmembr_dtls.panel_dtls_id%TYPE) IS
1053 SELECT pnmdt.*
1054 FROM igs_ad_pnmembr_dtls pnmdt
1055 WHERE panel_dtls_id = cp_panel_dtls_id;
1056
1057
1058 ---End cursor Declarations-----------------------------------------------------------------------------
1059
1060 ---- Variable declarations-----------------------------------------------------------------------------
1061 l_primary_key NUMBER(15);
1062 l_rowid VARCHAR2(30);
1063 l_last_error VARCHAR2(100);
1064 l_panel_dtls_id igs_ad_panel_dtls.panel_dtls_id%TYPE;
1065
1066 ------End variable declarations------------------------------------------------------------------------
1067
1068 BEGIN
1069
1070 -- Unitsets
1071 FOR c_unitsets_rec IN c_unitsets_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1072 l_last_error := 'IGS_AD_CHILD_UNIT_SETS';
1073 Igs_Ad_Unit_Sets_PKG.INSERT_ROW(
1074 X_ROWID => l_rowid,
1075 X_UNIT_SET_ID => l_primary_key,
1076 X_PERSON_ID => c_unitsets_rec.person_id ,
1077 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1078 X_NOMINATED_COURSE_CD => c_unitsets_rec.nominated_course_cd,
1079 X_SEQUENCE_NUMBER =>p_new_sequence_number,
1080 X_UNIT_SET_CD => c_unitsets_rec.unit_set_cd,
1081 X_VERSION_NUMBER => c_unitsets_rec.version_number,
1082 X_RANK => c_unitsets_rec.rank,
1083 X_MODE => 'R'
1084 );
1085 END LOOP;
1086
1087
1088 l_rowid := NULL;
1089
1090 -- edu goals
1091 FOR c_edugoal_rec IN c_edugoal_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1092 l_last_error := 'IGS_AD_CHILD_EDU_GOAL';
1093 Igs_Ad_Edugoal_pkg.INSERT_ROW(
1094 X_ROWID => l_rowid,
1095 X_POST_EDUGOAL_ID => l_primary_key,
1096 X_PERSON_ID => c_edugoal_rec.person_id,
1097 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1098 X_NOMINATED_COURSE_CD => c_edugoal_rec.nominated_course_cd,
1099 X_SEQUENCE_NUMBER => p_new_sequence_number,
1100 X_EDU_GOAL_ID => c_edugoal_rec.edu_goal_id,
1101 X_MODE => 'R');
1102 END LOOP;
1103
1104
1105 -- Special Consideration
1106
1107 FOR c_splcns_rec IN c_splcns_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1108 l_last_error := 'IGS_AD_CHILD_SPL_CON';
1109 igs_ad_spl_adm_cat_PKG.INSERT_ROW(
1110 X_ROWID => l_rowid,
1111 X_SPL_ADM_CAT_ID => l_primary_key,
1112 X_PERSON_ID => c_splcns_rec.person_id,
1113 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1114 X_NOMINATED_COURSE_CD => c_splcns_rec.nominated_course_cd,
1115 X_SEQUENCE_NUMBER => c_splcns_rec.sequence_number,
1116 X_SPL_ADM_CAT => c_splcns_rec.spl_adm_cat,
1117 X_MODE => 'R');
1118 END LOOP;
1119
1120 l_rowid := NULL;
1121
1122 -- Trackings
1123
1124
1125 FOR c_tracking_rec IN c_tracking_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1126 l_last_error := 'IGS_AD_CHILD_TRACK';
1127 IGS_AD_APLINS_ADMREQ_pkg.INSERT_ROW(
1128 X_ROWID => l_rowid,
1129 X_APLINS_ADMREQ_ID => l_primary_key,
1130 X_PERSON_ID => c_tracking_rec.person_id,
1131 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1132 X_COURSE_CD => c_tracking_rec.course_cd,
1133 X_SEQUENCE_NUMBER => p_new_sequence_number ,
1134 X_TRACKING_ID => c_tracking_rec.tracking_id,
1135 X_MODE => 'R');
1136 END LOOP;
1137
1138 l_rowid := NULL;
1139 -- program approval
1140
1141 FOR c_pgmapp_rec IN c_pgmapp_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1142 l_last_error := 'IGS_AD_CHILD_PRG_APPR';
1143 IGS_AD_APPL_PGMAPPRV_pkg.INSERT_ROW(
1144 X_ROWID => l_rowid,
1145 X_APPL_PGMAPPRV_ID => l_primary_key,
1146 X_PERSON_ID => c_pgmapp_rec.person_id,
1147 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1148 X_NOMINATED_COURSE_CD => c_pgmapp_rec.nominated_course_cd,
1149 X_SEQUENCE_NUMBER => p_new_sequence_number,
1150 X_PGM_APPROVER_ID => c_pgmapp_rec.pgm_approver_id,
1151 X_ASSIGN_TYPE => c_pgmapp_rec.assign_type,
1152 X_ASSIGN_DATE => c_pgmapp_rec.assign_date,
1153 X_PROGRAM_APPROVAL_DATE => c_pgmapp_rec.program_approval_date,
1154 X_PROGRAM_APPROVAL_STATUS => c_pgmapp_rec.program_approval_status,
1155 X_APPROVAL_NOTES => c_pgmapp_rec.approval_notes,
1156 X_MODE => 'R');
1157 END LOOP;
1158
1159 l_rowid := NULL;
1160 -- testscores
1161
1162
1163 FOR c_test_rec IN c_test_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1164 l_last_error := 'IGS_AD_CHILD_TST_SCORE';
1165 IGS_AD_TSTSCR_USED_pkg.INSERT_ROW(
1166 X_ROWID => l_rowid,
1167 X_TSTSCR_USED_ID => l_primary_key,
1168 X_COMMENTS => c_test_rec.comments,
1169 X_PERSON_ID => c_test_rec.person_id,
1170 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1171 X_NOMINATED_COURSE_CD => c_test_rec.nominated_course_cd,
1172 X_SEQUENCE_NUMBER => p_new_sequence_number,
1173 X_ATTRIBUTE_CATEGORY => c_test_rec.attribute_category,
1174 X_ATTRIBUTE1 => c_test_rec.attribute1,
1175 X_ATTRIBUTE2 => c_test_rec.attribute2,
1176 X_ATTRIBUTE3 => c_test_rec.attribute3,
1177 X_ATTRIBUTE4 => c_test_rec.attribute4 ,
1178 X_ATTRIBUTE5 => c_test_rec.attribute5,
1179 X_ATTRIBUTE6 => c_test_rec.attribute6,
1180 X_ATTRIBUTE7 => c_test_rec.attribute7 ,
1181 X_ATTRIBUTE8 => c_test_rec.attribute8 ,
1182 X_ATTRIBUTE9 => c_test_rec.attribute9 ,
1183 X_ATTRIBUTE10 => c_test_rec.attribute10 ,
1184 X_ATTRIBUTE11 => c_test_rec.attribute11 ,
1185 X_ATTRIBUTE12 => c_test_rec.attribute12 ,
1186 X_ATTRIBUTE13 => c_test_rec.attribute13 ,
1187 X_ATTRIBUTE14 => c_test_rec.attribute14 ,
1188 X_ATTRIBUTE15 => c_test_rec.attribute15 ,
1189 X_ATTRIBUTE16 => c_test_rec.attribute16 ,
1190 X_ATTRIBUTE17 => c_test_rec.attribute17 ,
1191 X_ATTRIBUTE18 => c_test_rec.attribute18 ,
1192 X_ATTRIBUTE19 => c_test_rec.attribute19 ,
1193 X_ATTRIBUTE20 => c_test_rec.attribute20 ,
1194 X_MODE => 'R');
1195 END LOOP;
1196
1197 l_rowid := NULL;
1198
1199 -- NOTES
1200 FOR c_notes_rec IN c_notes_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1201 l_last_error := 'IGS_AD_CHILD_APPL_NOTES';
1202 igs_ad_appl_notes_pkg.INSERT_ROW(
1203 X_ROWID => l_rowid,
1204 X_APPL_NOTES_ID => l_primary_key,
1205 X_PERSON_ID => c_notes_rec.person_id,
1206 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1207 X_NOMINATED_COURSE_CD => c_notes_rec.nominated_course_cd,
1208 X_SEQUENCE_NUMBER => p_new_sequence_number,
1209 X_NOTE_TYPE_ID => c_notes_rec.note_type_id,
1210 X_REF_NOTES_ID => c_notes_rec.ref_notes_id,
1211 X_MODE => 'R'
1212 );
1213 END LOOP;
1214
1215 l_rowid := NULL;
1216
1217 -- Evaluators
1218 FOR c_evaluators_rec IN c_evaluators_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1219 l_last_error := 'IGS_AD_CHILD_EVAL';
1220 igs_ad_appl_eval_pkg.INSERT_ROW(
1221 x_rowid => l_rowid,
1222 x_person_id => c_evaluators_rec.person_id,
1223 x_admission_appl_number => p_new_admission_appl_number,
1224 x_nominated_course_cd => c_evaluators_rec.nominated_course_cd,
1225 x_sequence_number => p_new_sequence_number,
1226 x_appl_eval_id => l_primary_key,
1227 x_evaluator_id => c_evaluators_rec.evaluator_id,
1228 x_assign_type => c_evaluators_rec.assign_type,
1229 x_assign_date => c_evaluators_rec.assign_date,
1230 x_evaluation_date => c_evaluators_rec.evaluation_date,
1231 x_rating_type_id => c_evaluators_rec.rating_type_id,
1232 x_rating_values_id => c_evaluators_rec.rating_values_id,
1233 x_rating_notes => c_evaluators_rec.rating_notes,
1234 x_evaluation_sequence => c_evaluators_rec.evaluation_sequence,
1235 x_rating_scale_id => c_evaluators_rec.rating_scale_id,
1236 x_mode => 'R',
1237 x_closed_ind => c_evaluators_rec.closed_ind -- added the parameter closed ind -- rghosh(bug#2871426)
1238 );
1239 END LOOP;
1240
1241 l_rowid := NULL;
1242
1243 -- Evaluators Review group
1244 FOR c_applrep_rec IN c_applrep_cur(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1245 l_last_error := 'IGS_AD_CHILD_EVAL_RGRP';
1246 igs_ad_appl_arp_pkg.INSERT_ROW(
1247 X_ROWID => l_rowid,
1248 X_PERSON_ID => c_applrep_rec.person_id,
1249 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1250 X_NOMINATED_COURSE_CD => c_applrep_rec.nominated_course_cd,
1251 X_SEQUENCE_NUMBER => p_new_sequence_number,
1252 X_APPL_ARP_ID => c_applrep_rec.appl_arp_id,
1253 X_APPL_REV_PROFILE_ID => c_applrep_rec.APPL_REV_PROFILE_ID,
1254 X_APPL_REVPROF_REVGR_ID => c_applrep_rec.APPL_REVPROF_REVGR_ID,
1255 X_MODE => 'R'
1256 );
1257 END LOOP;
1258
1259
1260 l_rowid := NULL;
1261
1262 --Qualification Codes
1263 FOR c_appl_qual_rec IN c_appl_qual_code(p_person_id, p_nominated_course_cd, p_old_admission_appl_number, p_old_sequence_number) LOOP
1264 l_last_error := 'IGS_AD_CHILD_QUAL_CODE';
1265 igs_ad_appqual_code_pkg.INSERT_ROW(
1266 x_rowid => l_rowid,
1267 x_person_id => c_appl_qual_rec.person_id,
1268 x_admission_appl_number => p_new_admission_appl_number,
1269 x_nominated_course_cd => c_appl_qual_rec.nominated_course_cd,
1270 x_sequence_number => p_new_sequence_number,
1271 x_qualifying_type_code => c_appl_qual_rec.qualifying_type_code,
1272 x_qualifying_code_id => c_appl_qual_rec.qualifying_code_id,
1273 x_qualifying_value => c_appl_qual_rec.qualifying_value,
1274 x_mode => 'R');
1275
1276 END LOOP;
1277
1278 -- Interview Details
1279
1280 FOR l_intvw_dtls_rec IN c_intvw_dtls(p_person_id, p_old_admission_appl_number, p_nominated_course_cd, p_old_sequence_number) LOOP
1281 l_last_error := 'IGS_AD_INTV_PNL_DTLS';
1282
1283 igs_ad_panel_dtls_pkg.insert_row(
1284 x_rowid => l_rowid,
1285 x_panel_dtls_id => l_panel_dtls_id,
1286 x_person_id => l_intvw_dtls_rec.person_id,
1287 x_admission_appl_number => l_intvw_dtls_rec.admission_appl_number,
1288 x_nominated_course_cd => l_intvw_dtls_rec.nominated_course_cd,
1289 x_sequence_number => l_intvw_dtls_rec.sequence_number,
1290 x_panel_code => l_intvw_dtls_rec.panel_code,
1291 x_interview_date => l_intvw_dtls_rec.interview_date,
1292 x_interview_time => l_intvw_dtls_rec.interview_time,
1293 x_location_cd => l_intvw_dtls_rec.location_cd,
1294 x_room_id => l_intvw_dtls_rec.room_id,
1295 x_final_decision_code => l_intvw_dtls_rec.final_decision_code,
1296 x_final_decision_type => l_intvw_dtls_rec.final_decision_type,
1297 x_final_decision_date => l_intvw_dtls_rec.final_decision_date,
1298 x_closed_flag => l_intvw_dtls_rec.closed_flag,
1299 x_attribute_category => l_intvw_dtls_rec.attribute_category,
1300 x_attribute1 => l_intvw_dtls_rec.attribute1,
1301 x_attribute2 => l_intvw_dtls_rec.attribute2,
1302 x_attribute3 => l_intvw_dtls_rec.attribute3,
1303 x_attribute4 => l_intvw_dtls_rec.attribute4,
1304 x_attribute5 => l_intvw_dtls_rec.attribute5,
1305 x_attribute6 => l_intvw_dtls_rec.attribute6,
1306 x_attribute7 => l_intvw_dtls_rec.attribute7,
1307 x_attribute8 => l_intvw_dtls_rec.attribute8,
1308 x_attribute9 => l_intvw_dtls_rec.attribute9,
1309 x_attribute10 => l_intvw_dtls_rec.attribute10,
1310 x_attribute11 => l_intvw_dtls_rec.attribute11,
1311 x_attribute12 => l_intvw_dtls_rec.attribute12,
1312 x_attribute13 => l_intvw_dtls_rec.attribute13,
1313 x_attribute14 => l_intvw_dtls_rec.attribute14,
1314 x_attribute15 => l_intvw_dtls_rec.attribute15,
1315 x_attribute16 => l_intvw_dtls_rec.attribute16,
1316 x_attribute17 => l_intvw_dtls_rec.attribute17,
1317 x_attribute18 => l_intvw_dtls_rec.attribute18,
1318 x_attribute19 => l_intvw_dtls_rec.attribute19,
1319 x_attribute20 => l_intvw_dtls_rec.attribute20,
1320 x_mode => 'R'
1321 );
1322
1323
1324 FOR l_intvw_pnmem_dtls_rec IN c_intvw_pnmem_dtls(l_intvw_dtls_rec.panel_dtls_id) LOOP
1325 l_last_error := 'IGS_AD_PNL_MEM_DTLS';
1326
1327 igs_ad_pnmembr_dtls_pkg.insert_row(
1328 x_rowid => l_rowid,
1329 x_panel_dtls_id => l_intvw_pnmem_dtls_rec.panel_dtls_id,
1330 x_role_type_code => l_intvw_pnmem_dtls_rec.role_type_code,
1331 x_member_person_id => l_intvw_pnmem_dtls_rec.member_person_id,
1332 x_interview_date => l_intvw_pnmem_dtls_rec.interview_date,
1333 x_interview_time => l_intvw_pnmem_dtls_rec.interview_time,
1334 x_location_cd => l_intvw_pnmem_dtls_rec.location_cd,
1335 x_room_id => l_intvw_pnmem_dtls_rec.room_id,
1336 x_member_decision_code => l_intvw_pnmem_dtls_rec.member_decision_code,
1337 x_member_decision_type => l_intvw_pnmem_dtls_rec.member_decision_type,
1338 x_member_decision_date => l_intvw_pnmem_dtls_rec.member_decision_date,
1339 x_attribute_category => l_intvw_pnmem_dtls_rec.attribute_category,
1340 x_attribute1 => l_intvw_pnmem_dtls_rec.attribute1,
1341 x_attribute2 => l_intvw_pnmem_dtls_rec.attribute2,
1342 x_attribute3 => l_intvw_pnmem_dtls_rec.attribute3,
1343 x_attribute4 => l_intvw_pnmem_dtls_rec.attribute4,
1344 x_attribute5 => l_intvw_pnmem_dtls_rec.attribute5,
1345 x_attribute6 => l_intvw_pnmem_dtls_rec.attribute6,
1346 x_attribute7 => l_intvw_pnmem_dtls_rec.attribute7,
1347 x_attribute8 => l_intvw_pnmem_dtls_rec.attribute8,
1348 x_attribute9 => l_intvw_pnmem_dtls_rec.attribute9,
1349 x_attribute10 => l_intvw_pnmem_dtls_rec.attribute10,
1350 x_attribute11 => l_intvw_pnmem_dtls_rec.attribute11,
1351 x_attribute12 => l_intvw_pnmem_dtls_rec.attribute12,
1352 x_attribute13 => l_intvw_pnmem_dtls_rec.attribute13,
1353 x_attribute14 => l_intvw_pnmem_dtls_rec.attribute14,
1354 x_attribute15 => l_intvw_pnmem_dtls_rec.attribute15,
1355 x_attribute16 => l_intvw_pnmem_dtls_rec.attribute16,
1356 x_attribute17 => l_intvw_pnmem_dtls_rec.attribute17,
1357 x_attribute18 => l_intvw_pnmem_dtls_rec.attribute18,
1358 x_attribute19 => l_intvw_pnmem_dtls_rec.attribute19,
1359 x_attribute20 => l_intvw_pnmem_dtls_rec.attribute20,
1360 x_mode => 'R'
1361 );
1362
1363 END LOOP;
1364
1365 END LOOP;
1366
1367 IF copy_candidacy_records (
1368 p_new_admission_appl_number => p_new_admission_appl_number,
1369 p_new_sequence_number => p_new_sequence_number,
1370 p_person_id => p_person_id,
1371 p_old_admission_appl_number => p_old_admission_appl_number,
1372 p_old_sequence_number => p_old_sequence_number,
1373 p_nominated_course_cd => p_nominated_course_cd,
1374 p_start_dt => p_start_dt) = FALSE THEN
1375 RETURN FALSE;
1376 END IF;
1377
1378 RETURN TRUE;
1379
1380 EXCEPTION WHEN OTHERS THEN
1381
1382 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(p_person_id,15,' ') || '; Admission Application Number: ' ||
1383 RPAD(p_old_admission_appl_number,2,' ') || '; Course Code: ' || RPAD(p_nominated_course_cd,6,' ') || '; Sequence Number: '||
1384 RPAD(p_old_sequence_number,6,' ') || ' Reason: ');
1385
1386 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CHILD_COPY_FAILED');
1387 Fnd_File.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1388
1389 FND_MESSAGE.SET_NAME('IGS',l_last_error);
1390 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET || SQLERRM);
1391
1392 RETURN FALSE;
1393 END copy_instance_child_records;
1394
1395
1396
1397 PROCEDURE admp_init_reconsider(
1398 errbuf out NOCOPY varchar2,
1399 retcode out NOCOPY number ,
1400 p_curr_acad_adm_cal VARCHAR2,
1401 p_future_acad_adm_cal VARCHAR2,
1402 p_application_type VARCHAR2,
1403 p_group_id igs_pe_persid_group.group_id%TYPE,
1404 p_application_id NUMBER,
1405 p_decision_date VARCHAR2,
1406 p_dec_maker_id igs_pe_person_base_v.person_id%TYPE,
1407 p_dec_reason_id IGS_AD_CODE_CLASSES.code_id%TYPE
1408 ) IS
1409 /*******************************************************************************
1410 Created by : Rishi Ghosh
1411 Date created: 12 September 2005
1412
1413 Purpose:
1414 This is the main procedure that is called from the Admission Initialize
1415 Reconsideration job. If the current application is in the PENDING status
1416 and future term academic and admission calendars are specified, then this
1417 job will create an application in the future term with PENDING status and
1418 make the original application CANCELLED.
1419
1420 Known limitations/enhancements and/or remarks:
1421
1422 Change History: (who, when, what: )
1423 Who When What
1424 *******************************************************************************/
1425
1426
1427 TYPE c_pers_group_rec IS RECORD (PERSON_ID IGS_PE_PRSID_GRP_MEM_ALL.PERSON_ID%TYPE);
1428 c_person_group_rec c_pers_group_rec;
1429
1430 /*This cursor returns the person id from the person id group */
1431 TYPE c_pers_group_ref IS REF CURSOR;
1432 c_person_group c_pers_group_ref;
1433
1434 lv_status VARCHAR2(1) ;
1435 lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
1436 lv_sql_stmt VARCHAR(32767) ;
1437
1438
1439 /* This cursor returns the application to be processed for a given combination of person_id, current calendars and future calendars */
1440 CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441 cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442 cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443 cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444 cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445 cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446 cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447 cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448 cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1449 cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
1450 cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE
1451 ) IS
1452 SELECT acai.*,
1453 aa.appl_dt,
1454 aa.admission_cat,
1455 aa.s_admission_process_type,
1456 aa.spcl_grp_1,
1457 aa.spcl_grp_2,
1458 aa.common_app,
1459 aa.adm_appl_status,
1460 aa.choice_number,
1461 aa.routeb_pref,
1462 aa.application_type,
1463 aa.adm_fee_status,
1464 aa.alt_appl_id,
1465 aa.acad_cal_type,
1466 aa.acad_ci_sequence_number,
1467 aprog.transfer_course_cd,
1468 aprog.basis_for_admission_type,
1469 aprog.admission_cd,
1470 aprog.req_for_reconsideration_ind,
1471 aprog.req_for_adv_standing_ind
1472 FROM igs_ad_appl aa,
1473 igs_ad_ps_appl_inst acai,
1474 igs_ad_ou_stat aous,
1475 igs_ad_ps_appl aprog
1476 WHERE acai.adm_outcome_status = aous.adm_outcome_status
1477 AND aous.s_adm_outcome_status = 'PENDING'
1478 AND acai.future_acad_cal_type = NVL(cp_fut_acad_cal_type, acai.future_acad_cal_type)
1479 AND acai.future_acad_ci_sequence_number = NVL(cp_fut_acad_cal_seq_no, acai.future_acad_ci_sequence_number)
1480 AND acai.future_adm_cal_type = NVL(cp_fut_adm_cal_type, acai.future_adm_cal_type)
1481 AND acai.future_adm_ci_sequence_number = NVL(cp_fut_adm_cal_seq_no, acai.future_adm_ci_sequence_number)
1482 AND acai.future_term_adm_appl_number IS NULL
1483 AND acai.future_term_sequence_number IS NULL
1484 AND acai.person_id = NVL(cp_person_id, acai.person_id)
1485 AND aa.person_id = acai.person_id
1486 AND aa.admission_appl_number = acai.admission_appl_number
1487 AND aa.acad_cal_type = NVL ( cp_prev_acad_cal_type, aa.acad_cal_type)
1488 AND aa.acad_ci_sequence_number = NVL ( cp_prev_acad_cal_seq_no, aa.acad_ci_sequence_number)
1489 AND NVL(acai.adm_cal_type,aa.adm_cal_type) = NVL( cp_prev_adm_cal_type, acai.adm_cal_type)
1490 AND NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) = NVL ( cp_prev_adm_cal_seq_no, acai.adm_ci_sequence_number)
1491 AND aa.application_id = nvl( p_application_id,aa.application_id)
1492 AND aa.admission_cat = NVL(cp_admission_cat,aa.admission_cat)
1493 AND aa.s_admission_process_type = NVL(cp_s_adm_process_type, aa.s_admission_process_type)
1494 AND aprog.person_id = acai.person_id
1495 AND aprog.admission_appl_number = acai.admission_appl_number
1496 AND aprog.nominated_course_cd = acai.nominated_course_cd
1497 AND acai.future_acad_cal_type IS NOT NULL
1498 AND acai.future_acad_ci_sequence_number IS NOT NULL
1499 AND acai.future_adm_cal_type IS NOT NULL
1500 AND acai.future_adm_ci_sequence_number IS NOT NULL
1501 ORDER BY acai.person_id, acai.admission_appl_number, acai.Future_acad_cal_type, acai.future_acad_ci_sequence_number,
1502 acai.future_adm_cal_type, acai.future_adm_ci_sequence_number, acai.nominated_course_cd;
1503
1504 l_appl_inst_rec c_appl_inst%ROWTYPE;
1505
1506
1507 /* This cursor will return the application instance details for a given combination of
1508 person id, appl number, course cd and sequence number */
1509 CURSOR c_get_appl_instance (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1510 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513 SELECT acai.rowid,acai.*
1514 FROM igs_ad_ps_appl_inst_all acai
1515 WHERE person_id = cp_person_id
1516 AND admission_appl_number = cp_admission_appl_number
1517 AND nominated_course_cd = cp_nominated_course_cd
1518 AND sequence_number = cp_sequence_number;
1519
1520 l_get_appl_instance c_get_appl_instance%ROWTYPE;
1521
1522
1523 /* This cursor will return the calenar information for a given application */
1524 CURSOR c_get_acad_cal_info(cp_person_id igs_ad_appl_all.person_id%TYPE,
1525 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
1526 SELECT acad_cal_type,acad_ci_sequence_number
1527 FROM igs_ad_appl_all
1528 WHERE person_id = cp_person_id
1529 AND admission_appl_number = cp_admission_appl_number;
1530
1531 l_get_acad_cal_info c_get_acad_cal_info%ROWTYPE;
1532
1533
1534 /* This cursor will return the future calendars (if any) of an application instance*/
1535 CURSOR c_get_fut_acad_adm_cal_info (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1536 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538 cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539 SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1540 FROM igs_ad_ps_appl_inst_all
1541 WHERE person_id = cp_person_id
1542 AND admission_appl_number = cp_admission_appl_number
1543 AND nominated_course_cd = cp_nominated_course_cd
1544 AND sequence_number = cp_sequence_number;
1545
1546 l_get_fut_acad_adm_cal_info c_get_fut_acad_adm_cal_info%ROWTYPE;
1547
1548 CURSOR c_get_prog_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1549 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1550 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
1551 SELECT aprog.*
1552 FROM igs_ad_ps_appl aprog
1553 WHERE aprog.person_id = cp_person_id
1554 AND aprog.admission_appl_number = cp_admission_appl_number
1555 AND aprog.nominated_course_cd = cp_nominated_course_cd;
1556
1557 l_get_prog_dtls c_get_prog_dtls%ROWTYPE;
1558
1559 CURSOR c_admission_type (cp_admission_type IGS_AD_SS_APPL_TYP.ADMISSION_APPLICATION_TYPE%TYPE) IS
1560 SELECT admission_cat, s_admission_process_type
1561 FROM igs_ad_ss_appl_typ
1562 WHERE admission_application_type = cp_admission_type;
1563
1564 l_admission_type c_admission_type%ROWTYPE;
1565
1566 l_new_admission_appl_number NUMBER;
1567
1568 l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569 l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570 l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571 l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572 l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573 l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574 l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1575
1576 l_prev_acad_cal_type VARCHAR2(30);
1577 l_prev_acad_cal_seq_no NUMBER;
1578 l_prev_adm_cal_type VARCHAR2(30);
1579 l_prev_adm_cal_seq_no NUMBER;
1580
1581 l_fut_acad_cal_type VARCHAR2(30);
1582 l_fut_acad_cal_seq_no NUMBER;
1583 l_fut_adm_cal_type VARCHAR2(30);
1584 l_fut_adm_cal_seq_no NUMBER;
1585
1586 l_message_name VARCHAR2(30);
1587 l_application_type igs_ad_appl_all.application_type%TYPE;
1588
1589 l_sequence_number NUMBER;
1590
1591 l_return_type VARCHAR2(100);
1592 l_error_code VARCHAR2(100);
1593
1594 v_start_dt DATE;
1595
1596 l_application_created BOOLEAN;
1597 l_program_created BOOLEAN;
1598 l_instance_created BOOLEAN;
1599
1600 l_decision_date DATE;
1601
1602 l_total_records NUMBER;
1603 l_successful_records NUMBER;
1604 l_failed_records NUMBER;
1605
1606 l_msg_at_index NUMBER;
1607 l_return_status VARCHAR2(1);
1608 l_msg_count NUMBER;
1609 l_msg_data VARCHAR2(2000);
1610 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1611
1612 BEGIN
1613
1614 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
1615 igs_ge_gen_003.set_org_id(null);
1616
1617 retcode := 0;
1618
1619 l_msg_at_index := igs_ge_msg_stack.count_msg;
1620
1621 l_total_records := 0;
1622 l_successful_records := 0;
1623 l_failed_records := 0;
1624
1625 l_decision_date := igs_ge_date.igsdate(p_decision_date);
1626
1627 l_person_id := NULL;
1628 l_admission_appl_number := NULL;
1629 l_future_acad_cal_type := NULL;
1630 l_fut_acad_ci_seq_no := NULL;
1631 l_future_adm_cal_type := NULL;
1632 l_fut_adm_ci_seq_no := NULL;
1633 l_nominated_course_cd := NULL;
1634
1635 l_prev_acad_cal_type := rtrim (substr (p_curr_acad_adm_cal, 1,10));
1636 l_prev_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr(p_curr_acad_adm_cal, 13,7));
1637 l_prev_adm_cal_type := rtrim (substr (p_curr_acad_adm_cal, 23,10));
1638 l_prev_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_curr_acad_adm_cal, 35,7));
1639
1640 l_fut_acad_cal_type := rtrim (substr (p_future_acad_adm_cal, 1,10));
1641 l_fut_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_future_acad_adm_cal, 13,7));
1642 l_fut_adm_cal_type := rtrim (substr (p_future_acad_adm_cal, 23,10));
1643 l_fut_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr (p_future_acad_adm_cal, 35,7));
1644
1645
1646 OPEN c_admission_type(p_application_type);
1647 FETCH c_admission_type INTO l_admission_type;
1648 CLOSE c_admission_type;
1649
1650 IF p_group_id IS NOT NULL THEN /* IF 1*/
1651 --FOR l_person_group_rec IN c_person_group LOOP /* FOR 1 */
1652 lv_sql_stmt := igs_pe_dynamic_persid_group.get_dynamic_sql (p_group_id,lv_status,lv_group_type);
1653 OPEN c_person_group FOR lv_sql_stmt USING p_group_id;
1654 LOOP
1655 FETCH c_person_group INTO c_person_group_rec;
1656 EXIT WHEN c_person_group%NOTFOUND;
1657 FOR l_appl_inst_rec IN c_appl_inst(c_person_group_rec.person_id,
1658 l_fut_acad_cal_type,
1659 l_fut_acad_cal_seq_no,
1660 l_fut_adm_cal_type,
1661 l_fut_adm_cal_seq_no,
1662 l_prev_acad_cal_type,
1663 l_prev_acad_cal_seq_no,
1664 l_prev_adm_cal_type,
1665 l_prev_adm_cal_seq_no,
1666 l_admission_type.admission_cat,
1667 l_admission_type.s_admission_process_type) LOOP
1668 l_total_records := l_total_records + 1;
1669
1670 l_application_created := TRUE;
1671 l_program_created := TRUE;
1672 l_instance_created := TRUE;
1673
1674 SAVEPOINT c_create_application;
1675
1676 IF l_appl_inst_rec.person_id <> nvl(l_person_id,-1) OR /* IF 2*/
1677 l_appl_inst_rec.admission_appl_number <> nvl(l_admission_appl_number,-1) OR
1678 l_appl_inst_rec.future_acad_cal_type <> nvl(l_future_acad_cal_type,-1) OR
1679 l_appl_inst_rec.future_acad_ci_sequence_number <> nvl(l_fut_acad_ci_seq_no,-1) OR
1680 l_appl_inst_rec.future_adm_cal_type <> nvl(l_future_adm_cal_type,-1) OR
1681 l_appl_inst_rec.future_adm_ci_sequence_number <> nvl(l_fut_adm_ci_seq_no,-1) THEN
1682
1683 BEGIN
1684
1685 IF IGS_AD_GEN_014.insert_adm_appl( /* IF 3*/
1686 p_person_id => l_appl_inst_rec.person_id,
1687 p_appl_dt => l_appl_inst_rec.appl_dt,
1688 p_acad_cal_type => l_appl_inst_rec.future_acad_cal_type ,
1689 p_acad_ci_sequence_number => l_appl_inst_rec.future_acad_ci_sequence_number ,
1690 p_adm_cal_type => l_appl_inst_rec.future_adm_cal_type ,
1691 p_adm_ci_sequence_number => l_appl_inst_rec.future_adm_ci_sequence_number ,
1692 p_admission_cat => l_appl_inst_rec.admission_cat,
1693 p_s_admission_process_type => l_appl_inst_rec.s_admission_process_type,
1694 p_adm_appl_status => l_appl_inst_rec.adm_appl_status,
1695 p_adm_fee_status => l_appl_inst_rec.adm_fee_status,
1696 p_tac_appl_ind => 'N',
1697 p_adm_appl_number => l_new_admission_appl_number,
1698 p_message_name => l_message_name,
1699 p_spcl_grp_1 => l_appl_inst_rec.spcl_grp_1,
1700 p_spcl_grp_2 => l_appl_inst_rec.spcl_grp_2,
1701 p_common_app => l_appl_inst_rec.common_app,
1702 p_application_type => l_appl_inst_rec.application_type,
1703 p_choice_number => l_appl_inst_rec.choice_number,
1704 p_routeb_pref => l_appl_inst_rec.routeb_pref,
1705 p_alt_appl_id => l_appl_inst_rec.alt_appl_id,
1706 p_log => 'N') THEN
1707
1708 IF copy_application_child_records (
1709 p_person_id => l_appl_inst_rec.person_id,
1710 p_new_admission_appl_number => l_new_admission_appl_number,
1711 p_old_admission_appl_number => l_appl_inst_rec.admission_appl_number,
1712 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
1713 p_sequence_number => l_appl_inst_rec.sequence_number) = FALSE THEN
1714
1715 l_failed_records := l_failed_records + 1;
1716
1717 ROLLBACK TO c_create_application;
1718 l_application_created := FALSE;
1719 END IF;
1720
1721 ELSE
1722 l_application_created := FALSE;
1723 l_failed_records := l_failed_records + 1;
1724
1725 ROLLBACK TO c_create_application;
1726 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
1727 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
1728 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
1729
1730 IF (l_message_name IS NULL) THEN
1731 igs_ad_gen_016.extract_msg_from_stack (
1732 p_msg_at_index => l_msg_at_index,
1733 p_return_status => l_return_status,
1734 p_msg_count => l_msg_count,
1735 p_msg_data => l_msg_data,
1736 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1737
1738 IF (l_msg_count > 0) THEN
1739 fnd_file.put_line(fnd_file.log, l_msg_data);
1740 ELSE
1741 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating the new application');
1742 END IF;
1743 ELSE
1744 fnd_file.put_line(fnd_file.log, l_message_name);
1745 END IF;
1746
1747 END IF;
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 l_application_created := FALSE;
1752 l_failed_records := l_failed_records + 1;
1753
1754 ROLLBACK TO c_create_application;
1755 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
1756 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
1757 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
1758
1759 IF (l_message_name IS NULL) THEN
1760 igs_ad_gen_016.extract_msg_from_stack (
1761 p_msg_at_index => l_msg_at_index,
1762 p_return_status => l_return_status,
1763 p_msg_count => l_msg_count,
1764 p_msg_data => l_msg_data,
1765 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1766
1767 IF (l_msg_count > 0) THEN
1768 fnd_file.put_line(fnd_file.log, l_msg_data);
1769 ELSE
1770 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating the new application');
1771 END IF;
1772 ELSE
1773 fnd_file.put_line(fnd_file.log, l_message_name);
1774 END IF;
1775 END;
1776
1777 END IF;
1778
1779
1780 IF ((l_appl_inst_rec.person_id <> nvl(l_person_id,-1) OR
1781 l_appl_inst_rec.admission_appl_number <> nvl(l_admission_appl_number,-1) OR
1782 l_appl_inst_rec.future_acad_cal_type <> nvl(l_future_acad_cal_type,-1) OR
1783 l_appl_inst_rec.future_acad_ci_sequence_number <> nvl(l_fut_acad_ci_seq_no,-1) OR
1784 l_appl_inst_rec.future_adm_cal_type <> nvl(l_future_adm_cal_type,-1) OR
1785 l_appl_inst_rec.future_adm_ci_sequence_number <> nvl(l_fut_adm_ci_seq_no,-1) OR
1786 l_appl_inst_rec.nominated_course_cd <> nvl(l_nominated_course_cd,-1)) AND
1787 l_application_created = TRUE ) THEN
1788
1789 BEGIN
1790
1791 IF IGS_AD_GEN_014.insert_adm_appl_prog(
1792 p_person_id => l_appl_inst_rec.person_id,
1793 p_adm_appl_number => l_new_admission_appl_number,
1794 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
1795 p_transfer_course_cd => l_appl_inst_rec.transfer_course_cd,
1796 p_basis_for_admission_type => l_appl_inst_rec.basis_for_admission_type,
1797 p_admission_cd => l_appl_inst_rec.admission_cd,
1798 p_req_for_reconsideration_ind => l_appl_inst_rec.req_for_reconsideration_ind,
1799 p_req_for_adv_standing_ind => l_appl_inst_rec.req_for_adv_standing_ind,
1800 p_message_name => l_message_name,
1801 p_log => 'N') THEN
1802
1803 OPEN c_get_prog_dtls(l_appl_inst_rec.person_id, l_appl_inst_rec.admission_appl_number, l_appl_inst_rec.nominated_course_cd);
1804 FETCH c_get_prog_dtls INTO l_get_prog_dtls;
1805 CLOSE c_get_prog_dtls;
1806
1807
1808 IF NVL(l_get_prog_dtls.req_for_reconsideration_ind,'N') = 'Y' THEN
1809
1810 BEGIN
1811
1812 igs_ad_ps_appl_pkg.update_row(
1813 x_rowid => l_get_prog_dtls.row_id,
1814 x_person_id => l_get_prog_dtls.person_id,
1815 x_admission_appl_number => l_get_prog_dtls.admission_appl_number,
1816 x_nominated_course_cd => l_get_prog_dtls.nominated_course_cd,
1817 x_transfer_course_cd => l_get_prog_dtls.transfer_course_cd,
1818 x_basis_for_admission_type => l_get_prog_dtls.basis_for_admission_type,
1819 x_admission_cd => l_get_prog_dtls.admission_cd,
1820 x_course_rank_set => l_get_prog_dtls.course_rank_set,
1821 x_course_rank_schedule => l_get_prog_dtls.course_rank_schedule,
1822 x_req_for_reconsideration_ind => 'N',
1823 x_req_for_adv_standing_ind => l_get_prog_dtls.req_for_adv_standing_ind,
1824 x_mode => 'R');
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828
1829 ROLLBACK TO c_create_application;
1830 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
1831 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
1832 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
1833 fnd_message.set_name('IGS', 'Failed to update the Request for Reconsideration Checkbox: ' || SQLERRM );
1834 l_program_created := FALSE;
1835
1836 END;
1837
1838 END IF;
1839
1840 ELSE
1841
1842 l_program_created := FALSE;
1843
1844 l_failed_records := l_failed_records + 1;
1845
1846 ROLLBACK TO c_create_application;
1847 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
1848 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
1849 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
1850
1851
1852 IF (l_message_name IS NULL) THEN
1853 igs_ad_gen_016.extract_msg_from_stack (
1854 p_msg_at_index => l_msg_at_index,
1855 p_return_status => l_return_status,
1856 p_msg_count => l_msg_count,
1857 p_msg_data => l_msg_data,
1858 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1859
1860 IF (l_msg_count > 0) THEN
1861 fnd_file.put_line(fnd_file.log, l_msg_data);
1862 ELSE
1863 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application program for the new application');
1864 END IF;
1865 ELSE
1866 fnd_file.put_line(fnd_file.log, l_message_name);
1867 END IF;
1868 END IF;
1869
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 l_program_created := FALSE;
1873
1874 l_failed_records := l_failed_records + 1;
1875
1876 ROLLBACK TO c_create_application;
1877 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
1878 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
1879 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
1880 IF (l_message_name IS NULL) THEN
1881 igs_ad_gen_016.extract_msg_from_stack (
1882 p_msg_at_index => l_msg_at_index,
1883 p_return_status => l_return_status,
1884 p_msg_count => l_msg_count,
1885 p_msg_data => l_msg_data,
1886 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1887
1888 IF (l_msg_count > 0) THEN
1889 fnd_file.put_line(fnd_file.log, l_msg_data);
1890 ELSE
1891 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application program for the new application');
1892 END IF;
1893 ELSE
1894 fnd_file.put_line(fnd_file.log, l_message_name);
1895 END IF;
1896 END;
1897
1898 END IF;
1899
1900 IF l_application_created = TRUE AND l_program_created = TRUE THEN
1901
1902 -- Create Admission Application Program Instance
1903 BEGIN
1904
1905 IF IGS_AD_GEN_014.insert_adm_appl_prog_inst ( /* IF 6*/
1906 p_person_id => l_appl_inst_rec.person_id,
1907 p_admission_appl_number => l_new_admission_appl_number,
1908 p_acad_cal_type => l_appl_inst_rec.future_acad_cal_type ,
1909 p_acad_ci_sequence_number => l_appl_inst_rec.future_acad_ci_sequence_number ,
1910 p_adm_cal_type => l_appl_inst_rec.future_adm_cal_type ,
1911 p_adm_ci_sequence_number => l_appl_inst_rec.future_adm_ci_sequence_number ,
1912 p_admission_cat => l_appl_inst_rec.admission_cat,
1913 p_s_admission_process_type => l_appl_inst_rec.s_admission_process_type,
1914 p_appl_dt => l_appl_inst_rec.appl_dt,
1915 p_adm_fee_status => l_appl_inst_rec.adm_fee_status,
1916 p_preference_number => l_appl_inst_rec.preference_number,
1917 p_offer_dt => NULL,
1918 p_offer_response_dt => NULL,
1919 p_course_cd => l_appl_inst_rec.nominated_course_cd,
1920 p_crv_version_number => l_appl_inst_rec.crv_version_number,
1921 p_location_cd => l_appl_inst_rec.location_cd,
1922 p_attendance_mode => l_appl_inst_rec.attendance_mode,
1923 p_attendance_type => l_appl_inst_rec.attendance_type,
1924 p_unit_set_cd => l_appl_inst_rec.unit_set_cd,
1925 p_us_version_number => l_appl_inst_rec.us_version_number,
1926 p_fee_cat => l_appl_inst_rec.fee_cat,
1927 p_correspondence_cat => l_appl_inst_rec.correspondence_cat,
1928 p_enrolment_cat => l_appl_inst_rec.enrolment_cat,
1929 p_funding_source => l_appl_inst_rec.funding_source,
1930 p_edu_goal_prior_enroll => l_appl_inst_rec.edu_goal_prior_enroll_id,
1931 p_app_source_id => l_appl_inst_rec.app_source_id,
1932 p_apply_for_finaid => l_appl_inst_rec.apply_for_finaid,
1933 p_finaid_apply_date => l_appl_inst_rec.finaid_apply_date,
1934 p_attribute_category => l_appl_inst_rec.attribute_category,
1935 p_attribute1 => l_appl_inst_rec.attribute1,
1936 p_attribute2 => l_appl_inst_rec.attribute2,
1937 p_attribute3 => l_appl_inst_rec.attribute3,
1938 p_attribute4 => l_appl_inst_rec.attribute4,
1939 p_attribute5 => l_appl_inst_rec.attribute5,
1940 p_attribute6 => l_appl_inst_rec.attribute6,
1941 p_attribute7 => l_appl_inst_rec.attribute7,
1942 p_attribute8 => l_appl_inst_rec.attribute8,
1943 p_attribute9 => l_appl_inst_rec.attribute9,
1944 p_attribute10 => l_appl_inst_rec.attribute10,
1945 p_attribute11 => l_appl_inst_rec.attribute11,
1946 p_attribute12 => l_appl_inst_rec.attribute12,
1947 p_attribute13 => l_appl_inst_rec.attribute13,
1948 p_attribute14 => l_appl_inst_rec.attribute14,
1949 p_attribute15 => l_appl_inst_rec.attribute15,
1950 p_attribute16 => l_appl_inst_rec.attribute16,
1951 p_attribute17 => l_appl_inst_rec.attribute17,
1952 p_attribute18 => l_appl_inst_rec.attribute18,
1953 p_attribute19 => l_appl_inst_rec.attribute19,
1954 p_attribute20 => l_appl_inst_rec.attribute20,
1955 p_attribute21 => l_appl_inst_rec.attribute21,
1956 p_attribute22 => l_appl_inst_rec.attribute22,
1957 p_attribute23 => l_appl_inst_rec.attribute23,
1958 p_attribute24 => l_appl_inst_rec.attribute24,
1959 p_attribute25 => l_appl_inst_rec.attribute25,
1960 p_attribute26 => l_appl_inst_rec.attribute26,
1961 p_attribute27 => l_appl_inst_rec.attribute27,
1962 p_attribute28 => l_appl_inst_rec.attribute28,
1963 p_attribute29 => l_appl_inst_rec.attribute29,
1964 p_attribute30 => l_appl_inst_rec.attribute30,
1965 p_attribute31 => l_appl_inst_rec.attribute31,
1966 p_attribute32 => l_appl_inst_rec.attribute32,
1967 p_attribute33 => l_appl_inst_rec.attribute33,
1968 p_attribute34 => l_appl_inst_rec.attribute34,
1969 p_attribute35 => l_appl_inst_rec.attribute35,
1970 p_attribute36 => l_appl_inst_rec.attribute36,
1971 p_attribute37 => l_appl_inst_rec.attribute37,
1972 p_attribute38 => l_appl_inst_rec.attribute38,
1973 p_attribute39 => l_appl_inst_rec.attribute39,
1974 p_attribute40 => l_appl_inst_rec.attribute40,
1975 p_ss_application_id => NULL,
1976 p_sequence_number => l_sequence_number,
1977 p_return_type => l_return_type,
1978 p_error_code => l_error_code,
1979 p_message_name => l_message_name,
1980 p_entry_status => l_appl_inst_rec.entry_status,
1981 p_entry_level => l_appl_inst_rec.entry_level,
1982 p_sch_apl_to_id => l_appl_inst_rec.sch_apl_to_id,
1983 p_log => 'N') THEN
1984
1985 OPEN c_get_acad_cal_info(l_appl_inst_rec.person_id,l_new_admission_appl_number);
1986 FETCH c_get_acad_cal_info INTO l_get_acad_cal_info;
1987 CLOSE c_get_acad_cal_info;
1988
1989 v_start_dt := igs_en_gen_002.enrp_get_acad_comm(
1990 l_get_acad_cal_info.acad_cal_type,
1991 l_get_acad_cal_info.acad_ci_sequence_number,
1992 l_appl_inst_rec.person_id,
1993 l_appl_inst_rec.nominated_course_cd,
1994 l_new_admission_appl_number,
1995 l_appl_inst_rec.nominated_course_cd,
1996 l_sequence_number,
1997 'Y');
1998
1999 IF copy_instance_child_records (
2000 p_new_admission_appl_number => l_new_admission_appl_number,
2001 p_new_sequence_number => l_sequence_number,
2002 p_person_id => l_appl_inst_rec.person_id,
2003 p_old_admission_appl_number => l_appl_inst_rec.admission_appl_number,
2004 p_old_sequence_number => l_appl_inst_rec.sequence_number,
2005 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
2006 p_start_dt => v_start_dt) THEN
2007
2008 l_person_id := l_appl_inst_rec.person_id;
2009 l_admission_appl_number := l_appl_inst_rec.admission_appl_number;
2010 l_future_acad_cal_type := l_appl_inst_rec.future_acad_cal_type;
2011 l_fut_acad_ci_seq_no := l_appl_inst_rec.future_acad_ci_sequence_number;
2012 l_future_adm_cal_type := l_appl_inst_rec.future_adm_cal_type;
2013 l_fut_adm_ci_seq_no := l_appl_inst_rec.future_adm_ci_sequence_number;
2014 l_nominated_course_cd := l_appl_inst_rec.nominated_course_cd;
2015
2016 ELSE
2017
2018 l_failed_records := l_failed_records + 1;
2019
2020 ROLLBACK TO c_create_application;
2021 l_instance_created := FALSE;
2022
2023 END IF;
2024
2025 /* Update the existing application instance to CANCELLED and populate the values of
2026 FUTURE_TERM_ADM_APPL_NUMBER and FUTURE_TERM_SEQUENCE_NUMBER
2027 to link with the new application instance*/
2028
2029 IF l_instance_created = TRUE THEN
2030 igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2031 X_ROWID => l_appl_inst_rec.row_id,
2032 X_PERSON_ID => l_appl_inst_rec.person_id,
2033 X_ADMISSION_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
2034 X_NOMINATED_COURSE_CD => l_appl_inst_rec.NOMINATED_COURSE_CD,
2035 X_SEQUENCE_NUMBER => l_appl_inst_rec.SEQUENCE_NUMBER,
2036 X_PREDICTED_GPA => l_appl_inst_rec.PREDICTED_GPA,
2037 X_ACADEMIC_INDEX => l_appl_inst_rec.ACADEMIC_INDEX,
2038 X_ADM_CAL_TYPE => l_appl_inst_rec.ADM_CAL_TYPE,
2039 X_APP_FILE_LOCATION => l_appl_inst_rec.APP_FILE_LOCATION,
2040 X_ADM_CI_SEQUENCE_NUMBER => l_appl_inst_rec.ADM_CI_SEQUENCE_NUMBER,
2041 X_COURSE_CD => l_appl_inst_rec.COURSE_CD,
2042 X_APP_SOURCE_ID => l_appl_inst_rec.APP_SOURCE_ID,
2043 X_CRV_VERSION_NUMBER => l_appl_inst_rec.CRV_VERSION_NUMBER,
2044 X_WAITLIST_RANK => l_appl_inst_rec.WAITLIST_RANK,
2045 X_LOCATION_CD => l_appl_inst_rec.LOCATION_CD,
2046 X_ATTENT_OTHER_INST_CD => l_appl_inst_rec.ATTENT_OTHER_INST_CD,
2047 X_ATTENDANCE_MODE => l_appl_inst_rec.ATTENDANCE_MODE,
2048 X_EDU_GOAL_PRIOR_ENROLL_ID => l_appl_inst_rec.EDU_GOAL_PRIOR_ENROLL_ID,
2049 X_ATTENDANCE_TYPE => l_appl_inst_rec.ATTENDANCE_TYPE,
2050 X_DECISION_MAKE_ID => p_dec_maker_id,
2051 X_UNIT_SET_CD => l_appl_inst_rec.UNIT_SET_CD,
2052 X_DECISION_DATE => l_decision_date,
2053 X_ATTRIBUTE_CATEGORY => l_appl_inst_rec.ATTRIBUTE_CATEGORY,
2054 X_ATTRIBUTE1 => l_appl_inst_rec.ATTRIBUTE1,
2055 X_ATTRIBUTE2 => l_appl_inst_rec.ATTRIBUTE2,
2056 X_ATTRIBUTE3 => l_appl_inst_rec.ATTRIBUTE3,
2057 X_ATTRIBUTE4 => l_appl_inst_rec.ATTRIBUTE4,
2058 X_ATTRIBUTE5 => l_appl_inst_rec.ATTRIBUTE5,
2059 X_ATTRIBUTE6 => l_appl_inst_rec.ATTRIBUTE6,
2060 X_ATTRIBUTE7 => l_appl_inst_rec.ATTRIBUTE7,
2061 X_ATTRIBUTE8 => l_appl_inst_rec.ATTRIBUTE8,
2062 X_ATTRIBUTE9 => l_appl_inst_rec.ATTRIBUTE9,
2063 X_ATTRIBUTE10 => l_appl_inst_rec.ATTRIBUTE10,
2064 X_ATTRIBUTE11 => l_appl_inst_rec.ATTRIBUTE11,
2065 X_ATTRIBUTE12 => l_appl_inst_rec.ATTRIBUTE12,
2066 X_ATTRIBUTE13 => l_appl_inst_rec.ATTRIBUTE13,
2067 X_ATTRIBUTE14 => l_appl_inst_rec.ATTRIBUTE14,
2068 X_ATTRIBUTE15 => l_appl_inst_rec.ATTRIBUTE15,
2069 X_ATTRIBUTE16 => l_appl_inst_rec.ATTRIBUTE16,
2070 X_ATTRIBUTE17 => l_appl_inst_rec.ATTRIBUTE17,
2071 X_ATTRIBUTE18 => l_appl_inst_rec.ATTRIBUTE18,
2072 X_ATTRIBUTE19 => l_appl_inst_rec.ATTRIBUTE19,
2073 X_ATTRIBUTE20 => l_appl_inst_rec.ATTRIBUTE20,
2074 X_DECISION_REASON_ID => p_dec_reason_id,
2075 X_US_VERSION_NUMBER => l_appl_inst_rec.US_VERSION_NUMBER,
2076 X_DECISION_NOTES => l_appl_inst_rec.DECISION_NOTES,
2077 X_PENDING_REASON_ID => NULL,
2078 X_PREFERENCE_NUMBER => l_appl_inst_rec.PREFERENCE_NUMBER,
2079 X_ADM_DOC_STATUS => l_appl_inst_rec.ADM_DOC_STATUS,
2080 X_ADM_ENTRY_QUAL_STATUS => l_appl_inst_rec.ADM_ENTRY_QUAL_STATUS,
2081 X_DEFICIENCY_IN_PREP => l_appl_inst_rec.DEFICIENCY_IN_PREP,
2082 X_LATE_ADM_FEE_STATUS => l_appl_inst_rec.LATE_ADM_FEE_STATUS,
2083 X_SPL_CONSIDER_COMMENTS => l_appl_inst_rec.SPL_CONSIDER_COMMENTS,
2084 X_APPLY_FOR_FINAID => l_appl_inst_rec.APPLY_FOR_FINAID,
2085 X_FINAID_APPLY_DATE => l_appl_inst_rec.FINAID_APPLY_DATE,
2086 X_ADM_OUTCOME_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('CANCELLED'),
2087 X_ADM_OTCM_STAT_AUTH_PER_ID => l_appl_inst_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
2088 X_ADM_OUTCOME_STATUS_AUTH_DT => l_appl_inst_rec.ADM_OUTCOME_STATUS_AUTH_DT,
2089 X_ADM_OUTCOME_STATUS_REASON => l_appl_inst_rec.ADM_OUTCOME_STATUS_REASON,
2090 X_OFFER_DT => l_appl_inst_rec.OFFER_DT,
2091 X_OFFER_RESPONSE_DT => l_appl_inst_rec.OFFER_RESPONSE_DT,
2092 X_PRPSD_COMMENCEMENT_DT => l_appl_inst_rec.PRPSD_COMMENCEMENT_DT,
2093 X_ADM_CNDTNL_OFFER_STATUS => l_appl_inst_rec.ADM_CNDTNL_OFFER_STATUS,
2094 X_CNDTNL_OFFER_SATISFIED_DT => l_appl_inst_rec.CNDTNL_OFFER_SATISFIED_DT,
2095 X_CNDNL_OFR_MUST_BE_STSFD_IND => l_appl_inst_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND,
2096 X_ADM_OFFER_RESP_STATUS => l_appl_inst_rec.ADM_OFFER_RESP_STATUS,
2097 X_ACTUAL_RESPONSE_DT => l_appl_inst_rec.ACTUAL_RESPONSE_DT,
2098 X_ADM_OFFER_DFRMNT_STATUS => l_appl_inst_rec.ADM_OFFER_DFRMNT_STATUS,
2099 X_DEFERRED_ADM_CAL_TYPE => l_appl_inst_rec.DEFERRED_ADM_CAL_TYPE,
2100 X_DEFERRED_ADM_CI_SEQUENCE_NUM => l_appl_inst_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
2101 X_DEFERRED_TRACKING_ID => l_appl_inst_rec.DEFERRED_TRACKING_ID,
2102 X_ASS_RANK => l_appl_inst_rec.ASS_RANK,
2103 X_SECONDARY_ASS_RANK => l_appl_inst_rec.SECONDARY_ASS_RANK,
2104 X_INTR_ACCEPT_ADVICE_NUM => l_appl_inst_rec.intrntnl_acceptance_advice_num,
2105 X_ASS_TRACKING_ID => l_appl_inst_rec.ASS_TRACKING_ID,
2106 X_FEE_CAT => l_appl_inst_rec.FEE_CAT,
2107 X_HECS_PAYMENT_OPTION => l_appl_inst_rec.HECS_PAYMENT_OPTION,
2108 X_EXPECTED_COMPLETION_YR => l_appl_inst_rec.EXPECTED_COMPLETION_YR,
2109 X_EXPECTED_COMPLETION_PERD => l_appl_inst_rec.EXPECTED_COMPLETION_PERD,
2110 X_CORRESPONDENCE_CAT => l_appl_inst_rec.CORRESPONDENCE_CAT,
2111 X_ENROLMENT_CAT => l_appl_inst_rec.ENROLMENT_CAT,
2112 X_FUNDING_SOURCE => l_appl_inst_rec.FUNDING_SOURCE,
2113 X_APPLICANT_ACPTNCE_CNDTN => l_appl_inst_rec.APPLICANT_ACPTNCE_CNDTN,
2114 X_CNDTNL_OFFER_CNDTN => l_appl_inst_rec.CNDTNL_OFFER_CNDTN,
2115 X_MODE => 'R',
2116 X_SS_APPLICATION_ID => l_appl_inst_rec.SS_APPLICATION_ID,
2117 X_SS_PWD => l_appl_inst_rec.SS_PWD,
2118 X_AUTHORIZED_DT => l_appl_inst_rec.AUTHORIZED_DT,
2119 X_AUTHORIZING_PERS_ID => l_appl_inst_rec.AUTHORIZING_PERS_ID,
2120 X_ENTRY_STATUS => l_appl_inst_rec.ENTRY_STATUS,
2121 X_ENTRY_LEVEL => l_appl_inst_rec.ENTRY_LEVEL,
2122 X_SCH_APL_TO_ID => l_appl_inst_rec.SCH_APL_TO_ID,
2123 X_IDX_CALC_DATE => l_appl_inst_rec.IDX_CALC_DATE,
2124 X_WAITLIST_STATUS => 'NOT-APPLIC',
2125 X_ATTRIBUTE21 => l_appl_inst_rec.ATTRIBUTE21,
2126 X_ATTRIBUTE22 => l_appl_inst_rec.ATTRIBUTE22,
2127 X_ATTRIBUTE23 => l_appl_inst_rec.ATTRIBUTE23,
2128 X_ATTRIBUTE24 => l_appl_inst_rec.ATTRIBUTE24,
2129 X_ATTRIBUTE25 => l_appl_inst_rec.ATTRIBUTE25,
2130 X_ATTRIBUTE26 => l_appl_inst_rec.ATTRIBUTE26,
2131 X_ATTRIBUTE27 => l_appl_inst_rec.ATTRIBUTE27,
2132 X_ATTRIBUTE28 => l_appl_inst_rec.ATTRIBUTE28,
2133 X_ATTRIBUTE29 => l_appl_inst_rec.ATTRIBUTE29,
2134 X_ATTRIBUTE30 => l_appl_inst_rec.ATTRIBUTE30,
2135 X_ATTRIBUTE31 => l_appl_inst_rec.ATTRIBUTE31,
2136 X_ATTRIBUTE32 => l_appl_inst_rec.ATTRIBUTE32,
2137 X_ATTRIBUTE33 => l_appl_inst_rec.ATTRIBUTE33,
2138 X_ATTRIBUTE34 => l_appl_inst_rec.ATTRIBUTE34,
2139 X_ATTRIBUTE35 => l_appl_inst_rec.ATTRIBUTE35,
2140 X_ATTRIBUTE36 => l_appl_inst_rec.ATTRIBUTE36,
2141 X_ATTRIBUTE37 => l_appl_inst_rec.ATTRIBUTE37,
2142 X_ATTRIBUTE38 => l_appl_inst_rec.ATTRIBUTE38,
2143 X_ATTRIBUTE39 => l_appl_inst_rec.ATTRIBUTE39,
2144 X_ATTRIBUTE40 => l_appl_inst_rec.ATTRIBUTE40,
2145 X_FUT_ACAD_CAL_TYPE => l_appl_inst_rec.FUTURE_ACAD_CAL_TYPE,
2146 X_FUT_ACAD_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
2147 X_FUT_ADM_CAL_TYPE => l_appl_inst_rec.FUTURE_ADM_CAL_TYPE,
2148 X_FUT_ADM_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ADM_CI_SEQUENCE_NUMBER,
2149 X_PREV_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_ADM_APPL_NUMBER,
2150 X_PREV_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_SEQUENCE_NUMBER,
2151 X_FUT_TERM_ADM_APPL_NUMBER => l_new_admission_appl_number,
2152 X_FUT_TERM_SEQUENCE_NUMBER => l_sequence_number,
2153 X_DEF_ACAD_CAL_TYPE => l_appl_inst_rec.DEF_ACAD_CAL_TYPE,
2154 X_DEF_ACAD_CI_SEQUENCE_NUM => l_appl_inst_rec.DEF_ACAD_CI_SEQUENCE_NUM,
2155 X_DEF_PREV_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_PREV_TERM_ADM_APPL_NUM,
2156 X_DEF_PREV_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_PREV_APPL_SEQUENCE_NUM,
2157 X_DEF_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_TERM_ADM_APPL_NUM,
2158 X_DEF_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_APPL_SEQUENCE_NUM,
2159 X_APPL_INST_STATUS => l_appl_inst_rec.APPL_INST_STATUS,
2160 X_AIS_REASON => l_appl_inst_rec.AIS_REASON,
2161 X_DECLINE_OFR_REASON => l_appl_inst_rec.DECLINE_OFR_REASON );
2162
2163 /* Update the new application instance PREVIOUS_TERM_ADM_APPL_NUMBER and
2164 PREVIOUS_TERM_SEQUENCE_NUMBER columns to link it with the old application */
2165
2166 OPEN c_get_appl_instance (l_appl_inst_rec.person_id, l_new_admission_appl_number,
2167 l_appl_inst_rec.nominated_course_cd, l_sequence_number);
2168 FETCH c_get_appl_instance INTO l_get_appl_instance;
2169 CLOSE c_get_appl_instance;
2170
2171 igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2172 X_ROWID => l_get_appl_instance.ROWID,
2173 X_PERSON_ID => l_get_appl_instance.PERSON_ID,
2174 X_ADMISSION_APPL_NUMBER => l_get_appl_instance.ADMISSION_APPL_NUMBER,
2175 X_NOMINATED_COURSE_CD => l_get_appl_instance.NOMINATED_COURSE_CD,
2176 X_SEQUENCE_NUMBER => l_get_appl_instance.SEQUENCE_NUMBER,
2177 X_PREDICTED_GPA => l_get_appl_instance.PREDICTED_GPA,
2178 X_ACADEMIC_INDEX => l_get_appl_instance.ACADEMIC_INDEX,
2179 X_ADM_CAL_TYPE => l_get_appl_instance.ADM_CAL_TYPE,
2180 X_APP_FILE_LOCATION => l_get_appl_instance.APP_FILE_LOCATION,
2181 X_ADM_CI_SEQUENCE_NUMBER => l_get_appl_instance.ADM_CI_SEQUENCE_NUMBER,
2182 X_COURSE_CD => l_get_appl_instance.COURSE_CD,
2183 X_APP_SOURCE_ID => l_get_appl_instance.APP_SOURCE_ID,
2184 X_CRV_VERSION_NUMBER => l_get_appl_instance.CRV_VERSION_NUMBER,
2185 X_WAITLIST_RANK => l_get_appl_instance.WAITLIST_RANK,
2186 X_LOCATION_CD => l_get_appl_instance.LOCATION_CD,
2187 X_ATTENT_OTHER_INST_CD => l_get_appl_instance.ATTENT_OTHER_INST_CD,
2188 X_ATTENDANCE_MODE => l_get_appl_instance.ATTENDANCE_MODE,
2189 X_EDU_GOAL_PRIOR_ENROLL_ID => l_get_appl_instance.EDU_GOAL_PRIOR_ENROLL_ID,
2190 X_ATTENDANCE_TYPE => l_get_appl_instance.ATTENDANCE_TYPE,
2191 X_DECISION_MAKE_ID => l_get_appl_instance.DECISION_MAKE_ID,
2192 X_UNIT_SET_CD => l_get_appl_instance.UNIT_SET_CD,
2193 X_DECISION_DATE => l_get_appl_instance.DECISION_DATE,
2194 X_ATTRIBUTE_CATEGORY => l_get_appl_instance.ATTRIBUTE_CATEGORY,
2195 X_ATTRIBUTE1 => l_get_appl_instance.ATTRIBUTE1,
2196 X_ATTRIBUTE2 => l_get_appl_instance.ATTRIBUTE2,
2197 X_ATTRIBUTE3 => l_get_appl_instance.ATTRIBUTE3,
2198 X_ATTRIBUTE4 => l_get_appl_instance.ATTRIBUTE4,
2199 X_ATTRIBUTE5 => l_get_appl_instance.ATTRIBUTE5,
2200 X_ATTRIBUTE6 => l_get_appl_instance.ATTRIBUTE6,
2201 X_ATTRIBUTE7 => l_get_appl_instance.ATTRIBUTE7,
2202 X_ATTRIBUTE8 => l_get_appl_instance.ATTRIBUTE8,
2203 X_ATTRIBUTE9 => l_get_appl_instance.ATTRIBUTE9,
2204 X_ATTRIBUTE10 => l_get_appl_instance.ATTRIBUTE10,
2205 X_ATTRIBUTE11 => l_get_appl_instance.ATTRIBUTE11,
2206 X_ATTRIBUTE12 => l_get_appl_instance.ATTRIBUTE12,
2207 X_ATTRIBUTE13 => l_get_appl_instance.ATTRIBUTE13,
2208 X_ATTRIBUTE14 => l_get_appl_instance.ATTRIBUTE14,
2209 X_ATTRIBUTE15 => l_get_appl_instance.ATTRIBUTE15,
2210 X_ATTRIBUTE16 => l_get_appl_instance.ATTRIBUTE16,
2211 X_ATTRIBUTE17 => l_get_appl_instance.ATTRIBUTE17,
2212 X_ATTRIBUTE18 => l_get_appl_instance.ATTRIBUTE18,
2213 X_ATTRIBUTE19 => l_get_appl_instance.ATTRIBUTE19,
2214 X_ATTRIBUTE20 => l_get_appl_instance.ATTRIBUTE20,
2215 X_DECISION_REASON_ID => l_get_appl_instance.DECISION_REASON_ID,
2216 X_US_VERSION_NUMBER => l_get_appl_instance.US_VERSION_NUMBER,
2217 X_DECISION_NOTES => l_get_appl_instance.DECISION_NOTES,
2218 X_PENDING_REASON_ID => l_get_appl_instance.PENDING_REASON_ID,
2219 X_PREFERENCE_NUMBER => l_get_appl_instance.PREFERENCE_NUMBER,
2220 X_ADM_DOC_STATUS => l_get_appl_instance.ADM_DOC_STATUS,
2221 X_ADM_ENTRY_QUAL_STATUS => l_get_appl_instance.ADM_ENTRY_QUAL_STATUS,
2222 X_DEFICIENCY_IN_PREP => l_get_appl_instance.DEFICIENCY_IN_PREP,
2223 X_LATE_ADM_FEE_STATUS => l_get_appl_instance.LATE_ADM_FEE_STATUS,
2224 X_SPL_CONSIDER_COMMENTS => l_get_appl_instance.SPL_CONSIDER_COMMENTS,
2225 X_APPLY_FOR_FINAID => l_get_appl_instance.APPLY_FOR_FINAID,
2226 X_FINAID_APPLY_DATE => l_get_appl_instance.FINAID_APPLY_DATE,
2227 X_ADM_OUTCOME_STATUS => l_get_appl_instance.ADM_OUTCOME_STATUS,
2228 X_ADM_OTCM_STAT_AUTH_PER_ID => l_get_appl_instance.ADM_OTCM_STATUS_AUTH_PERSON_ID,
2229 X_ADM_OUTCOME_STATUS_AUTH_DT => l_get_appl_instance.ADM_OUTCOME_STATUS_AUTH_DT,
2230 X_ADM_OUTCOME_STATUS_REASON => l_get_appl_instance.ADM_OUTCOME_STATUS_REASON,
2231 X_OFFER_DT => l_get_appl_instance.OFFER_DT,
2232 X_OFFER_RESPONSE_DT => l_get_appl_instance.OFFER_RESPONSE_DT,
2233 X_PRPSD_COMMENCEMENT_DT => l_get_appl_instance.PRPSD_COMMENCEMENT_DT,
2234 X_ADM_CNDTNL_OFFER_STATUS => l_get_appl_instance.ADM_CNDTNL_OFFER_STATUS,
2235 X_CNDTNL_OFFER_SATISFIED_DT => l_get_appl_instance.CNDTNL_OFFER_SATISFIED_DT,
2236 X_CNDNL_OFR_MUST_BE_STSFD_IND => l_get_appl_instance.CNDTNL_OFFER_MUST_BE_STSFD_IND,
2237 X_ADM_OFFER_RESP_STATUS => l_get_appl_instance.ADM_OFFER_RESP_STATUS,
2238 X_ACTUAL_RESPONSE_DT => l_get_appl_instance.ACTUAL_RESPONSE_DT,
2239 X_ADM_OFFER_DFRMNT_STATUS => l_get_appl_instance.ADM_OFFER_DFRMNT_STATUS,
2240 X_DEFERRED_ADM_CAL_TYPE => l_get_appl_instance.DEFERRED_ADM_CAL_TYPE,
2241 X_DEFERRED_ADM_CI_SEQUENCE_NUM => l_get_appl_instance.DEFERRED_ADM_CI_SEQUENCE_NUM,
2242 X_DEFERRED_TRACKING_ID => l_get_appl_instance.DEFERRED_TRACKING_ID,
2243 X_ASS_RANK => l_get_appl_instance.ASS_RANK,
2244 X_SECONDARY_ASS_RANK => l_get_appl_instance.SECONDARY_ASS_RANK,
2245 X_INTR_ACCEPT_ADVICE_NUM => l_get_appl_instance.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
2246 X_ASS_TRACKING_ID => l_get_appl_instance.ASS_TRACKING_ID,
2247 X_FEE_CAT => l_get_appl_instance.FEE_CAT,
2248 X_HECS_PAYMENT_OPTION => l_get_appl_instance.HECS_PAYMENT_OPTION,
2249 X_EXPECTED_COMPLETION_YR => l_get_appl_instance.EXPECTED_COMPLETION_YR,
2250 X_EXPECTED_COMPLETION_PERD => l_get_appl_instance.EXPECTED_COMPLETION_PERD,
2251 X_CORRESPONDENCE_CAT => l_get_appl_instance.CORRESPONDENCE_CAT,
2252 X_ENROLMENT_CAT => l_get_appl_instance.ENROLMENT_CAT,
2253 X_FUNDING_SOURCE => l_get_appl_instance.FUNDING_SOURCE,
2254 X_APPLICANT_ACPTNCE_CNDTN => l_get_appl_instance.APPLICANT_ACPTNCE_CNDTN,
2255 X_CNDTNL_OFFER_CNDTN => l_get_appl_instance.CNDTNL_OFFER_CNDTN,
2256 X_MODE => 'R',
2257 X_SS_APPLICATION_ID => l_get_appl_instance.SS_APPLICATION_ID,
2258 X_SS_PWD => l_get_appl_instance.SS_PWD,
2259 X_AUTHORIZED_DT => l_get_appl_instance.AUTHORIZED_DT,
2260 X_AUTHORIZING_PERS_ID => l_get_appl_instance.AUTHORIZING_PERS_ID,
2261 X_ENTRY_STATUS => l_get_appl_instance.ENTRY_STATUS,
2262 X_ENTRY_LEVEL => l_get_appl_instance.ENTRY_LEVEL,
2263 X_SCH_APL_TO_ID => l_get_appl_instance.SCH_APL_TO_ID,
2264 X_IDX_CALC_DATE => l_get_appl_instance.IDX_CALC_DATE,
2265 X_WAITLIST_STATUS => l_get_appl_instance.WAITLIST_STATUS,
2266 X_ATTRIBUTE21 => l_get_appl_instance.ATTRIBUTE21,
2267 X_ATTRIBUTE22 => l_get_appl_instance.ATTRIBUTE22,
2268 X_ATTRIBUTE23 => l_get_appl_instance.ATTRIBUTE23,
2269 X_ATTRIBUTE24 => l_get_appl_instance.ATTRIBUTE24,
2270 X_ATTRIBUTE25 => l_get_appl_instance.ATTRIBUTE25,
2271 X_ATTRIBUTE26 => l_get_appl_instance.ATTRIBUTE26,
2272 X_ATTRIBUTE27 => l_get_appl_instance.ATTRIBUTE27,
2273 X_ATTRIBUTE28 => l_get_appl_instance.ATTRIBUTE28,
2274 X_ATTRIBUTE29 => l_get_appl_instance.ATTRIBUTE29,
2275 X_ATTRIBUTE30 => l_get_appl_instance.ATTRIBUTE30,
2276 X_ATTRIBUTE31 => l_get_appl_instance.ATTRIBUTE31,
2277 X_ATTRIBUTE32 => l_get_appl_instance.ATTRIBUTE32,
2278 X_ATTRIBUTE33 => l_get_appl_instance.ATTRIBUTE33,
2279 X_ATTRIBUTE34 => l_get_appl_instance.ATTRIBUTE34,
2280 X_ATTRIBUTE35 => l_get_appl_instance.ATTRIBUTE35,
2281 X_ATTRIBUTE36 => l_get_appl_instance.ATTRIBUTE36,
2282 X_ATTRIBUTE37 => l_get_appl_instance.ATTRIBUTE37,
2283 X_ATTRIBUTE38 => l_get_appl_instance.ATTRIBUTE38,
2284 X_ATTRIBUTE39 => l_get_appl_instance.ATTRIBUTE39,
2285 X_ATTRIBUTE40 => l_get_appl_instance.ATTRIBUTE40,
2286 X_FUT_ACAD_CAL_TYPE => l_get_appl_instance.FUTURE_ACAD_CAL_TYPE,
2287 X_FUT_ACAD_CI_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
2288 X_FUT_ADM_CAL_TYPE => l_get_appl_instance.FUTURE_ADM_CAL_TYPE,
2289 X_FUT_ADM_CI_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_ADM_CI_SEQUENCE_NUMBER,
2290 X_PREV_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
2291 X_PREV_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.SEQUENCE_NUMBER,
2292 X_FUT_TERM_ADM_APPL_NUMBER => l_get_appl_instance.FUTURE_TERM_ADM_APPL_NUMBER,
2293 X_FUT_TERM_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_TERM_SEQUENCE_NUMBER,
2294 X_DEF_ACAD_CAL_TYPE => l_get_appl_instance.DEF_ACAD_CAL_TYPE,
2295 X_DEF_ACAD_CI_SEQUENCE_NUM => l_get_appl_instance.DEF_ACAD_CI_SEQUENCE_NUM,
2296 X_DEF_PREV_TERM_ADM_APPL_NUM => l_get_appl_instance.DEF_PREV_TERM_ADM_APPL_NUM,
2297 X_DEF_PREV_APPL_SEQUENCE_NUM => l_get_appl_instance.DEF_PREV_APPL_SEQUENCE_NUM,
2298 X_DEF_TERM_ADM_APPL_NUM => l_get_appl_instance.DEF_TERM_ADM_APPL_NUM,
2299 X_DEF_APPL_SEQUENCE_NUM => l_get_appl_instance.DEF_APPL_SEQUENCE_NUM,
2300 X_APPL_INST_STATUS => l_get_appl_instance.APPL_INST_STATUS,
2301 X_AIS_REASON => l_get_appl_instance.AIS_REASON,
2302 X_DECLINE_OFR_REASON => l_get_appl_instance.DECLINE_OFR_REASON);
2303
2304
2305 l_successful_records := l_successful_records + 1;
2306
2307 COMMIT;
2308 END IF;
2309
2310 ELSE
2311
2312 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2313 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2314 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2315
2316 IF (l_message_name IS NULL) THEN
2317 igs_ad_gen_016.extract_msg_from_stack (
2318 p_msg_at_index => l_msg_at_index,
2319 p_return_status => l_return_status,
2320 p_msg_count => l_msg_count,
2321 p_msg_data => l_msg_data,
2322 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2323
2324 IF (l_msg_count > 0) THEN
2325 fnd_file.put_line(fnd_file.log, l_msg_data);
2326 ELSE
2327 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application instance of the new program');
2328 END IF;
2329 ELSE
2330 fnd_file.put_line(fnd_file.log, l_message_name);
2331 END IF;
2332
2333 l_failed_records := l_failed_records + 1;
2334 ROLLBACK TO c_create_application;
2335
2336 END IF;
2337 --ELSE -- Else of Application Instance
2338 EXCEPTION
2339 WHEN OTHERS THEN
2340 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2341 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2342 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2343
2344 l_failed_records := l_failed_records + 1;
2345
2346
2347
2348 IF l_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
2349
2350 l_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
2351 fnd_message.set_name('IGS', l_message_name);
2352 fnd_message.set_token('PGM', l_appl_inst_rec.nominated_course_cd);
2353 fnd_message.set_token('ALTCODE',l_appl_inst_rec.future_acad_cal_type||','||
2354 IGS_GE_NUMBER.TO_CANN(l_appl_inst_rec.future_acad_ci_sequence_number)
2355 ||'/'||l_appl_inst_rec.future_adm_cal_type||','||
2356 IGS_GE_NUMBER.TO_CANN(l_appl_inst_rec.future_adm_ci_sequence_number));
2357 fnd_file.put_line(fnd_file.log, fnd_message.get);
2358
2359 ELSE
2360
2361 fnd_message.set_name('IGS', l_message_name);
2362 fnd_file.put_line(fnd_file.log, fnd_message.get);
2363
2364 END IF;
2365 ROLLBACK TO c_create_application;
2366
2367 END;
2368
2369 END IF;
2370
2371
2372 END LOOP;
2373
2374 END LOOP;
2375 CLOSE c_person_group;
2376
2377 ELSE
2378
2379 FOR l_appl_inst_rec IN c_appl_inst(NULL,
2380 l_fut_acad_cal_type,
2381 l_fut_acad_cal_seq_no,
2382 l_fut_adm_cal_type,
2383 l_fut_adm_cal_seq_no,
2384 l_prev_acad_cal_type,
2385 l_prev_acad_cal_seq_no,
2386 l_prev_adm_cal_type,
2387 l_prev_adm_cal_seq_no,
2388 l_admission_type.admission_cat,
2389 l_admission_type.s_admission_process_type) LOOP
2390
2391 l_total_records := l_total_records + 1;
2392
2393 l_application_created := TRUE;
2394 l_program_created := TRUE;
2395 l_instance_created := TRUE;
2396
2397 SAVEPOINT c_create_application;
2398
2399 IF l_appl_inst_rec.person_id <> nvl(l_person_id,-1) OR
2400 l_appl_inst_rec.admission_appl_number <> nvl(l_admission_appl_number,-1) OR
2401 l_appl_inst_rec.future_acad_cal_type <> nvl(l_future_acad_cal_type,-1) OR
2402 l_appl_inst_rec.future_acad_ci_sequence_number <> nvl(l_fut_acad_ci_seq_no,-1) OR
2403 l_appl_inst_rec.future_adm_cal_type <> nvl(l_future_adm_cal_type,-1) OR
2404 l_appl_inst_rec.future_adm_ci_sequence_number <> nvl(l_fut_adm_ci_seq_no,-1) THEN
2405
2406 BEGIN
2407
2408 IF IGS_AD_GEN_014.insert_adm_appl(
2409 p_person_id => l_appl_inst_rec.person_id,
2410 p_appl_dt => l_appl_inst_rec.appl_dt,
2411 p_acad_cal_type => l_appl_inst_rec.future_acad_cal_type ,
2412 p_acad_ci_sequence_number => l_appl_inst_rec.future_acad_ci_sequence_number ,
2413 p_adm_cal_type => l_appl_inst_rec.future_adm_cal_type ,
2414 p_adm_ci_sequence_number => l_appl_inst_rec.future_adm_ci_sequence_number ,
2415 p_admission_cat => l_appl_inst_rec.admission_cat,
2416 p_s_admission_process_type => l_appl_inst_rec.s_admission_process_type,
2417 p_adm_appl_status => l_appl_inst_rec.adm_appl_status,
2418 p_adm_fee_status => l_appl_inst_rec.adm_fee_status,
2419 p_tac_appl_ind => 'N',
2420 p_adm_appl_number => l_new_admission_appl_number,
2421 p_message_name => l_message_name,
2422 p_spcl_grp_1 => l_appl_inst_rec.spcl_grp_1,
2423 p_spcl_grp_2 => l_appl_inst_rec.spcl_grp_2,
2424 p_common_app => l_appl_inst_rec.common_app,
2425 p_application_type => l_appl_inst_rec.application_type,
2426 p_choice_number => l_appl_inst_rec.choice_number,
2427 p_routeb_pref => l_appl_inst_rec.routeb_pref,
2428 p_alt_appl_id => l_appl_inst_rec.alt_appl_id,
2429 p_log => 'N') THEN
2430
2431 IF copy_application_child_records (
2432 p_person_id => l_appl_inst_rec.person_id,
2433 p_new_admission_appl_number => l_new_admission_appl_number,
2434 p_old_admission_appl_number => l_appl_inst_rec.admission_appl_number,
2435 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
2436 p_sequence_number => l_appl_inst_rec.sequence_number) = FALSE THEN
2437
2438 l_failed_records := l_failed_records + 1;
2439
2440 ROLLBACK TO c_create_application;
2441 l_application_created := FALSE;
2442 END IF;
2443 ELSE
2444
2445 l_application_created := FALSE;
2446 l_failed_records := l_failed_records + 1;
2447 ROLLBACK TO c_create_application;
2448
2449 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2450 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2451 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2452 IF (l_message_name IS NULL) THEN
2453 igs_ad_gen_016.extract_msg_from_stack (
2454 p_msg_at_index => l_msg_at_index,
2455 p_return_status => l_return_status,
2456 p_msg_count => l_msg_count,
2457 p_msg_data => l_msg_data,
2458 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2459
2460 IF (l_msg_count > 0) THEN
2461 fnd_file.put_line(fnd_file.log, l_msg_data);
2462 ELSE
2463 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating the new application');
2464 END IF;
2465 ELSE
2466 fnd_file.put_line(fnd_file.log, l_message_name);
2467 END IF;
2468 END IF;
2469 EXCEPTION
2470 WHEN OTHERS THEN
2471 l_application_created := FALSE;
2472 l_failed_records := l_failed_records + 1;
2473
2474 ROLLBACK TO c_create_application;
2475 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2476 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2477 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2478
2479 IF (l_message_name IS NULL) THEN
2480 igs_ad_gen_016.extract_msg_from_stack (
2481 p_msg_at_index => l_msg_at_index,
2482 p_return_status => l_return_status,
2483 p_msg_count => l_msg_count,
2484 p_msg_data => l_msg_data,
2485 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2486
2487 IF (l_msg_count > 0) THEN
2488 fnd_file.put_line(fnd_file.log, l_msg_data);
2489 ELSE
2490 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating the new application');
2491 END IF;
2492 ELSE
2493 fnd_file.put_line(fnd_file.log, l_message_name);
2494 END IF;
2495 END;
2496
2497 END IF;
2498
2499 IF ((l_appl_inst_rec.person_id <> nvl(l_person_id,-1) OR
2500 l_appl_inst_rec.admission_appl_number <> nvl(l_admission_appl_number,-1) OR
2501 l_appl_inst_rec.future_acad_cal_type <> nvl(l_future_acad_cal_type,-1) OR
2502 l_appl_inst_rec.future_acad_ci_sequence_number <> nvl(l_fut_acad_ci_seq_no,-1) OR
2503 l_appl_inst_rec.future_adm_cal_type <> nvl(l_future_adm_cal_type,-1) OR
2504 l_appl_inst_rec.future_adm_ci_sequence_number <> nvl(l_fut_adm_ci_seq_no,-1) OR
2505 l_appl_inst_rec.nominated_course_cd <> nvl(l_nominated_course_cd,-1)) AND
2506 l_application_created = TRUE) THEN
2507
2508
2509 BEGIN
2510
2511 IF IGS_AD_GEN_014.insert_adm_appl_prog(
2512 p_person_id => l_appl_inst_rec.person_id,
2513 p_adm_appl_number => l_new_admission_appl_number,
2514 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
2515 p_transfer_course_cd => l_appl_inst_rec.transfer_course_cd,
2516 p_basis_for_admission_type => l_appl_inst_rec.basis_for_admission_type,
2517 p_admission_cd => l_appl_inst_rec.admission_cd,
2518 p_req_for_reconsideration_ind => l_appl_inst_rec.req_for_reconsideration_ind,
2519 p_req_for_adv_standing_ind => l_appl_inst_rec.req_for_adv_standing_ind,
2520 p_message_name => l_message_name,
2521 p_log => 'N') THEN
2522
2523
2524 OPEN c_get_prog_dtls(l_appl_inst_rec.person_id, l_appl_inst_rec.admission_appl_number, l_appl_inst_rec.nominated_course_cd);
2525 FETCH c_get_prog_dtls INTO l_get_prog_dtls;
2526 CLOSE c_get_prog_dtls;
2527
2528
2529 IF NVL(l_get_prog_dtls.req_for_reconsideration_ind,'N') = 'Y' THEN
2530
2531 BEGIN
2532
2533 igs_ad_ps_appl_pkg.update_row(
2534 x_rowid => l_get_prog_dtls.row_id,
2535 x_person_id => l_get_prog_dtls.person_id,
2536 x_admission_appl_number => l_get_prog_dtls.admission_appl_number,
2537 x_nominated_course_cd => l_get_prog_dtls.nominated_course_cd,
2538 x_transfer_course_cd => l_get_prog_dtls.transfer_course_cd,
2539 x_basis_for_admission_type => l_get_prog_dtls.basis_for_admission_type,
2540 x_admission_cd => l_get_prog_dtls.admission_cd,
2541 x_course_rank_set => l_get_prog_dtls.course_rank_set,
2542 x_course_rank_schedule => l_get_prog_dtls.course_rank_schedule,
2543 x_req_for_reconsideration_ind => 'N',
2544 x_req_for_adv_standing_ind => l_get_prog_dtls.req_for_adv_standing_ind,
2545 x_mode => 'R');
2546
2547 EXCEPTION
2548 WHEN OTHERS THEN
2549 ROLLBACK TO c_create_application;
2550 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2551 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2552 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2553 fnd_message.set_name('IGS', 'Failed to update the Request for Reconsideration Checkbox: ' || SQLERRM );
2554 l_program_created:= FALSE;
2555 END;
2556
2557 END IF;
2558
2559
2560 ELSE
2561
2562 l_program_created:= FALSE;
2563
2564 l_failed_records := l_failed_records + 1;
2565
2566 ROLLBACK TO c_create_application;
2567 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2568 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2569 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2570
2571 IF (l_message_name IS NULL) THEN
2572 igs_ad_gen_016.extract_msg_from_stack (
2573 p_msg_at_index => l_msg_at_index,
2574 p_return_status => l_return_status,
2575 p_msg_count => l_msg_count,
2576 p_msg_data => l_msg_data,
2577 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2578
2579 IF (l_msg_count > 0) THEN
2580 fnd_file.put_line(fnd_file.log, l_msg_data);
2581 ELSE
2582 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application program for the new application');
2583 END IF;
2584 ELSE
2585 fnd_file.put_line(fnd_file.log, l_message_name);
2586 END IF;
2587 END IF;
2588 EXCEPTION
2589
2590 WHEN OTHERS THEN
2591 l_program_created := FALSE;
2592
2593 l_failed_records := l_failed_records + 1;
2594
2595 ROLLBACK TO c_create_application;
2596 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
2597 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
2598 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
2599 IF (l_message_name IS NULL) THEN
2600 igs_ad_gen_016.extract_msg_from_stack (
2601 p_msg_at_index => l_msg_at_index,
2602 p_return_status => l_return_status,
2603 p_msg_count => l_msg_count,
2604 p_msg_data => l_msg_data,
2605 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
2606
2607 IF (l_msg_count > 0) THEN
2608 fnd_file.put_line(fnd_file.log, l_msg_data);
2609 ELSE
2610 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application program for the new application');
2611 END IF;
2612 ELSE
2613 fnd_file.put_line(fnd_file.log, l_message_name);
2614 END IF;
2615 END;
2616
2617
2618 END IF;
2619
2620 IF l_application_created = TRUE AND l_program_created = TRUE THEN
2621
2622 BEGIN
2623 IF IGS_AD_GEN_014.insert_adm_appl_prog_inst (
2624 p_person_id => l_appl_inst_rec.person_id,
2625 p_admission_appl_number => l_new_admission_appl_number,
2626 p_acad_cal_type => l_appl_inst_rec.future_acad_cal_type,
2627 p_acad_ci_sequence_number => l_appl_inst_rec.future_acad_ci_sequence_number,
2628 p_adm_cal_type => l_appl_inst_rec.future_adm_cal_type,
2629 p_adm_ci_sequence_number => l_appl_inst_rec.future_adm_ci_sequence_number,
2630 p_admission_cat => l_appl_inst_rec.admission_cat,
2631 p_s_admission_process_type => l_appl_inst_rec.s_admission_process_type,
2632 p_appl_dt => l_appl_inst_rec.appl_dt,
2633 p_adm_fee_status => l_appl_inst_rec.adm_fee_status,
2634 p_preference_number => l_appl_inst_rec.preference_number,
2635 p_offer_dt => NULL,
2636 p_offer_response_dt => NULL,
2637 p_course_cd => l_appl_inst_rec.nominated_course_cd,
2638 p_crv_version_number => l_appl_inst_rec.crv_version_number,
2639 p_location_cd => l_appl_inst_rec.location_cd,
2640 p_attendance_mode => l_appl_inst_rec.attendance_mode,
2641 p_attendance_type => l_appl_inst_rec.attendance_type,
2642 p_unit_set_cd => l_appl_inst_rec.unit_set_cd,
2643 p_us_version_number => l_appl_inst_rec.us_version_number,
2644 p_fee_cat => l_appl_inst_rec.fee_cat,
2645 p_correspondence_cat => l_appl_inst_rec.correspondence_cat,
2646 p_enrolment_cat => l_appl_inst_rec.enrolment_cat,
2647 p_funding_source => l_appl_inst_rec.funding_source,
2648 p_edu_goal_prior_enroll => l_appl_inst_rec.edu_goal_prior_enroll_id,
2649 p_app_source_id => l_appl_inst_rec.app_source_id,
2650 p_apply_for_finaid => l_appl_inst_rec.apply_for_finaid,
2651 p_finaid_apply_date => l_appl_inst_rec.finaid_apply_date,
2652 p_attribute_category => l_appl_inst_rec.attribute_category,
2653 p_attribute1 => l_appl_inst_rec.attribute1,
2654 p_attribute2 => l_appl_inst_rec.attribute2,
2655 p_attribute3 => l_appl_inst_rec.attribute3,
2656 p_attribute4 => l_appl_inst_rec.attribute4,
2657 p_attribute5 => l_appl_inst_rec.attribute5,
2658 p_attribute6 => l_appl_inst_rec.attribute6,
2659 p_attribute7 => l_appl_inst_rec.attribute7,
2660 p_attribute8 => l_appl_inst_rec.attribute8,
2661 p_attribute9 => l_appl_inst_rec.attribute9,
2662 p_attribute10 => l_appl_inst_rec.attribute10,
2663 p_attribute11 => l_appl_inst_rec.attribute11,
2664 p_attribute12 => l_appl_inst_rec.attribute12,
2665 p_attribute13 => l_appl_inst_rec.attribute13,
2666 p_attribute14 => l_appl_inst_rec.attribute14,
2667 p_attribute15 => l_appl_inst_rec.attribute15,
2668 p_attribute16 => l_appl_inst_rec.attribute16,
2669 p_attribute17 => l_appl_inst_rec.attribute17,
2670 p_attribute18 => l_appl_inst_rec.attribute18,
2671 p_attribute19 => l_appl_inst_rec.attribute19,
2672 p_attribute20 => l_appl_inst_rec.attribute20,
2673 p_attribute21 => l_appl_inst_rec.attribute21,
2674 p_attribute22 => l_appl_inst_rec.attribute22,
2675 p_attribute23 => l_appl_inst_rec.attribute23,
2676 p_attribute24 => l_appl_inst_rec.attribute24,
2677 p_attribute25 => l_appl_inst_rec.attribute25,
2678 p_attribute26 => l_appl_inst_rec.attribute26,
2679 p_attribute27 => l_appl_inst_rec.attribute27,
2680 p_attribute28 => l_appl_inst_rec.attribute28,
2681 p_attribute29 => l_appl_inst_rec.attribute29,
2682 p_attribute30 => l_appl_inst_rec.attribute30,
2683 p_attribute31 => l_appl_inst_rec.attribute31,
2684 p_attribute32 => l_appl_inst_rec.attribute32,
2685 p_attribute33 => l_appl_inst_rec.attribute33,
2686 p_attribute34 => l_appl_inst_rec.attribute34,
2687 p_attribute35 => l_appl_inst_rec.attribute35,
2688 p_attribute36 => l_appl_inst_rec.attribute36,
2689 p_attribute37 => l_appl_inst_rec.attribute37,
2690 p_attribute38 => l_appl_inst_rec.attribute38,
2691 p_attribute39 => l_appl_inst_rec.attribute39,
2692 p_attribute40 => l_appl_inst_rec.attribute40,
2693 p_ss_application_id => NULL,
2694 p_sequence_number => l_sequence_number,
2695 p_return_type => l_return_type,
2696 p_error_code => l_error_code,
2697 p_message_name => l_message_name,
2698 p_entry_status => l_appl_inst_rec.entry_status,
2699 p_entry_level => l_appl_inst_rec.entry_level,
2700 p_sch_apl_to_id => l_appl_inst_rec.sch_apl_to_id,
2701 p_log => 'N') THEN
2702
2703
2704 OPEN c_get_acad_cal_info(l_appl_inst_rec.person_id,l_new_admission_appl_number);
2705 FETCH c_get_acad_cal_info INTO l_get_acad_cal_info;
2706 CLOSE c_get_acad_cal_info;
2707
2708 v_start_dt := igs_en_gen_002.enrp_get_acad_comm(
2709 l_get_acad_cal_info.acad_cal_type,
2710 l_get_acad_cal_info.acad_ci_sequence_number,
2711 l_appl_inst_rec.person_id,
2712 l_appl_inst_rec.nominated_course_cd,
2713 l_new_admission_appl_number,
2714 l_appl_inst_rec.nominated_course_cd,
2715 l_sequence_number,
2716 'Y');
2717
2718 IF copy_instance_child_records (
2719 p_new_admission_appl_number => l_new_admission_appl_number,
2720 p_new_sequence_number => l_sequence_number,
2721 p_person_id => l_appl_inst_rec.person_id,
2722 p_old_admission_appl_number => l_appl_inst_rec.admission_appl_number,
2723 p_old_sequence_number => l_appl_inst_rec.sequence_number,
2724 p_nominated_course_cd => l_appl_inst_rec.nominated_course_cd,
2725 p_start_dt => v_start_dt) THEN
2726
2727 l_person_id := l_appl_inst_rec.person_id;
2728 l_admission_appl_number := l_appl_inst_rec.admission_appl_number;
2729 l_future_acad_cal_type := l_appl_inst_rec.future_acad_cal_type;
2730 l_fut_acad_ci_seq_no := l_appl_inst_rec.future_acad_ci_sequence_number;
2731 l_future_adm_cal_type := l_appl_inst_rec.future_adm_cal_type;
2732 l_fut_adm_ci_seq_no := l_appl_inst_rec.future_adm_ci_sequence_number;
2733 l_nominated_course_cd := l_appl_inst_rec.nominated_course_cd;
2734 ELSE
2735
2736 l_failed_records := l_failed_records + 1;
2737
2738 ROLLBACK TO c_create_application;
2739 l_instance_created := FALSE;
2740 END IF;
2741 /* Update the existing application instance to CANCELLED and populate the values of
2742 FUTURE_TERM_ADM_APPL_NUMBER and FUTURE_TERM_SEQUENCE_NUMBER
2743 to link with the new application instance*/
2744
2745 IF l_instance_created = TRUE THEN
2746 igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2747 X_ROWID => l_appl_inst_rec.row_id,
2748 X_PERSON_ID => l_appl_inst_rec.person_id,
2749 X_ADMISSION_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
2750 X_NOMINATED_COURSE_CD => l_appl_inst_rec.NOMINATED_COURSE_CD,
2751 X_SEQUENCE_NUMBER => l_appl_inst_rec.SEQUENCE_NUMBER,
2752 X_PREDICTED_GPA => l_appl_inst_rec.PREDICTED_GPA,
2753 X_ACADEMIC_INDEX => l_appl_inst_rec.ACADEMIC_INDEX,
2754 X_ADM_CAL_TYPE => l_appl_inst_rec.ADM_CAL_TYPE,
2755 X_APP_FILE_LOCATION => l_appl_inst_rec.APP_FILE_LOCATION,
2756 X_ADM_CI_SEQUENCE_NUMBER => l_appl_inst_rec.ADM_CI_SEQUENCE_NUMBER,
2757 X_COURSE_CD => l_appl_inst_rec.COURSE_CD,
2758 X_APP_SOURCE_ID => l_appl_inst_rec.APP_SOURCE_ID,
2759 X_CRV_VERSION_NUMBER => l_appl_inst_rec.CRV_VERSION_NUMBER,
2760 X_WAITLIST_RANK => l_appl_inst_rec.WAITLIST_RANK,
2761 X_LOCATION_CD => l_appl_inst_rec.LOCATION_CD,
2762 X_ATTENT_OTHER_INST_CD => l_appl_inst_rec.ATTENT_OTHER_INST_CD,
2763 X_ATTENDANCE_MODE => l_appl_inst_rec.ATTENDANCE_MODE,
2764 X_EDU_GOAL_PRIOR_ENROLL_ID => l_appl_inst_rec.EDU_GOAL_PRIOR_ENROLL_ID,
2765 X_ATTENDANCE_TYPE => l_appl_inst_rec.ATTENDANCE_TYPE,
2766 X_DECISION_MAKE_ID => p_dec_maker_id,
2767 X_UNIT_SET_CD => l_appl_inst_rec.UNIT_SET_CD,
2768 X_DECISION_DATE => l_decision_date,
2769 X_ATTRIBUTE_CATEGORY => l_appl_inst_rec.ATTRIBUTE_CATEGORY,
2770 X_ATTRIBUTE1 => l_appl_inst_rec.ATTRIBUTE1,
2771 X_ATTRIBUTE2 => l_appl_inst_rec.ATTRIBUTE2,
2772 X_ATTRIBUTE3 => l_appl_inst_rec.ATTRIBUTE3,
2773 X_ATTRIBUTE4 => l_appl_inst_rec.ATTRIBUTE4,
2774 X_ATTRIBUTE5 => l_appl_inst_rec.ATTRIBUTE5,
2775 X_ATTRIBUTE6 => l_appl_inst_rec.ATTRIBUTE6,
2776 X_ATTRIBUTE7 => l_appl_inst_rec.ATTRIBUTE7,
2777 X_ATTRIBUTE8 => l_appl_inst_rec.ATTRIBUTE8,
2778 X_ATTRIBUTE9 => l_appl_inst_rec.ATTRIBUTE9,
2779 X_ATTRIBUTE10 => l_appl_inst_rec.ATTRIBUTE10,
2780 X_ATTRIBUTE11 => l_appl_inst_rec.ATTRIBUTE11,
2781 X_ATTRIBUTE12 => l_appl_inst_rec.ATTRIBUTE12,
2782 X_ATTRIBUTE13 => l_appl_inst_rec.ATTRIBUTE13,
2783 X_ATTRIBUTE14 => l_appl_inst_rec.ATTRIBUTE14,
2784 X_ATTRIBUTE15 => l_appl_inst_rec.ATTRIBUTE15,
2785 X_ATTRIBUTE16 => l_appl_inst_rec.ATTRIBUTE16,
2786 X_ATTRIBUTE17 => l_appl_inst_rec.ATTRIBUTE17,
2787 X_ATTRIBUTE18 => l_appl_inst_rec.ATTRIBUTE18,
2788 X_ATTRIBUTE19 => l_appl_inst_rec.ATTRIBUTE19,
2789 X_ATTRIBUTE20 => l_appl_inst_rec.ATTRIBUTE20,
2790 X_DECISION_REASON_ID => p_dec_reason_id,
2791 X_US_VERSION_NUMBER => l_appl_inst_rec.US_VERSION_NUMBER,
2792 X_DECISION_NOTES => l_appl_inst_rec.DECISION_NOTES,
2793 X_PENDING_REASON_ID => NULL,
2794 X_PREFERENCE_NUMBER => l_appl_inst_rec.PREFERENCE_NUMBER,
2795 X_ADM_DOC_STATUS => l_appl_inst_rec.ADM_DOC_STATUS,
2796 X_ADM_ENTRY_QUAL_STATUS => l_appl_inst_rec.ADM_ENTRY_QUAL_STATUS,
2797 X_DEFICIENCY_IN_PREP => l_appl_inst_rec.DEFICIENCY_IN_PREP,
2798 X_LATE_ADM_FEE_STATUS => l_appl_inst_rec.LATE_ADM_FEE_STATUS,
2799 X_SPL_CONSIDER_COMMENTS => l_appl_inst_rec.SPL_CONSIDER_COMMENTS,
2800 X_APPLY_FOR_FINAID => l_appl_inst_rec.APPLY_FOR_FINAID,
2801 X_FINAID_APPLY_DATE => l_appl_inst_rec.FINAID_APPLY_DATE,
2802 X_ADM_OUTCOME_STATUS => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('CANCELLED'),
2803 X_ADM_OTCM_STAT_AUTH_PER_ID => l_appl_inst_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
2804 X_ADM_OUTCOME_STATUS_AUTH_DT => l_appl_inst_rec.ADM_OUTCOME_STATUS_AUTH_DT,
2805 X_ADM_OUTCOME_STATUS_REASON => l_appl_inst_rec.ADM_OUTCOME_STATUS_REASON,
2806 X_OFFER_DT => l_appl_inst_rec.OFFER_DT,
2807 X_OFFER_RESPONSE_DT => l_appl_inst_rec.OFFER_RESPONSE_DT,
2808 X_PRPSD_COMMENCEMENT_DT => l_appl_inst_rec.PRPSD_COMMENCEMENT_DT,
2809 X_ADM_CNDTNL_OFFER_STATUS => l_appl_inst_rec.ADM_CNDTNL_OFFER_STATUS,
2810 X_CNDTNL_OFFER_SATISFIED_DT => l_appl_inst_rec.CNDTNL_OFFER_SATISFIED_DT,
2811 X_CNDNL_OFR_MUST_BE_STSFD_IND => l_appl_inst_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND,
2812 X_ADM_OFFER_RESP_STATUS => l_appl_inst_rec.ADM_OFFER_RESP_STATUS,
2813 X_ACTUAL_RESPONSE_DT => l_appl_inst_rec.ACTUAL_RESPONSE_DT,
2814 X_ADM_OFFER_DFRMNT_STATUS => l_appl_inst_rec.ADM_OFFER_DFRMNT_STATUS,
2815 X_DEFERRED_ADM_CAL_TYPE => l_appl_inst_rec.DEFERRED_ADM_CAL_TYPE,
2816 X_DEFERRED_ADM_CI_SEQUENCE_NUM => l_appl_inst_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
2817 X_DEFERRED_TRACKING_ID => l_appl_inst_rec.DEFERRED_TRACKING_ID,
2818 X_ASS_RANK => l_appl_inst_rec.ASS_RANK,
2819 X_SECONDARY_ASS_RANK => l_appl_inst_rec.SECONDARY_ASS_RANK,
2820 X_INTR_ACCEPT_ADVICE_NUM => l_appl_inst_rec.intrntnl_acceptance_advice_num,
2821 X_ASS_TRACKING_ID => l_appl_inst_rec.ASS_TRACKING_ID,
2822 X_FEE_CAT => l_appl_inst_rec.FEE_CAT,
2823 X_HECS_PAYMENT_OPTION => l_appl_inst_rec.HECS_PAYMENT_OPTION,
2824 X_EXPECTED_COMPLETION_YR => l_appl_inst_rec.EXPECTED_COMPLETION_YR,
2825 X_EXPECTED_COMPLETION_PERD => l_appl_inst_rec.EXPECTED_COMPLETION_PERD,
2826 X_CORRESPONDENCE_CAT => l_appl_inst_rec.CORRESPONDENCE_CAT,
2827 X_ENROLMENT_CAT => l_appl_inst_rec.ENROLMENT_CAT,
2828 X_FUNDING_SOURCE => l_appl_inst_rec.FUNDING_SOURCE,
2829 X_APPLICANT_ACPTNCE_CNDTN => l_appl_inst_rec.APPLICANT_ACPTNCE_CNDTN,
2830 X_CNDTNL_OFFER_CNDTN => l_appl_inst_rec.CNDTNL_OFFER_CNDTN,
2831 X_MODE => 'R',
2832 X_SS_APPLICATION_ID => l_appl_inst_rec.SS_APPLICATION_ID,
2833 X_SS_PWD => l_appl_inst_rec.SS_PWD,
2834 X_AUTHORIZED_DT => l_appl_inst_rec.AUTHORIZED_DT,
2835 X_AUTHORIZING_PERS_ID => l_appl_inst_rec.AUTHORIZING_PERS_ID,
2836 X_ENTRY_STATUS => l_appl_inst_rec.ENTRY_STATUS,
2837 X_ENTRY_LEVEL => l_appl_inst_rec.ENTRY_LEVEL,
2838 X_SCH_APL_TO_ID => l_appl_inst_rec.SCH_APL_TO_ID,
2839 X_IDX_CALC_DATE => l_appl_inst_rec.IDX_CALC_DATE,
2840 X_WAITLIST_STATUS => 'NOT-APPLIC',
2841 X_ATTRIBUTE21 => l_appl_inst_rec.ATTRIBUTE21,
2842 X_ATTRIBUTE22 => l_appl_inst_rec.ATTRIBUTE22,
2843 X_ATTRIBUTE23 => l_appl_inst_rec.ATTRIBUTE23,
2844 X_ATTRIBUTE24 => l_appl_inst_rec.ATTRIBUTE24,
2845 X_ATTRIBUTE25 => l_appl_inst_rec.ATTRIBUTE25,
2846 X_ATTRIBUTE26 => l_appl_inst_rec.ATTRIBUTE26,
2847 X_ATTRIBUTE27 => l_appl_inst_rec.ATTRIBUTE27,
2848 X_ATTRIBUTE28 => l_appl_inst_rec.ATTRIBUTE28,
2849 X_ATTRIBUTE29 => l_appl_inst_rec.ATTRIBUTE29,
2850 X_ATTRIBUTE30 => l_appl_inst_rec.ATTRIBUTE30,
2851 X_ATTRIBUTE31 => l_appl_inst_rec.ATTRIBUTE31,
2852 X_ATTRIBUTE32 => l_appl_inst_rec.ATTRIBUTE32,
2853 X_ATTRIBUTE33 => l_appl_inst_rec.ATTRIBUTE33,
2854 X_ATTRIBUTE34 => l_appl_inst_rec.ATTRIBUTE34,
2855 X_ATTRIBUTE35 => l_appl_inst_rec.ATTRIBUTE35,
2856 X_ATTRIBUTE36 => l_appl_inst_rec.ATTRIBUTE36,
2857 X_ATTRIBUTE37 => l_appl_inst_rec.ATTRIBUTE37,
2858 X_ATTRIBUTE38 => l_appl_inst_rec.ATTRIBUTE38,
2859 X_ATTRIBUTE39 => l_appl_inst_rec.ATTRIBUTE39,
2860 X_ATTRIBUTE40 => l_appl_inst_rec.ATTRIBUTE40,
2861 X_FUT_ACAD_CAL_TYPE => l_appl_inst_rec.FUTURE_ACAD_CAL_TYPE,
2862 X_FUT_ACAD_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
2863 X_FUT_ADM_CAL_TYPE => l_appl_inst_rec.FUTURE_ADM_CAL_TYPE,
2864 X_FUT_ADM_CI_SEQUENCE_NUMBER => l_appl_inst_rec.FUTURE_ADM_CI_SEQUENCE_NUMBER,
2865 X_PREV_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_ADM_APPL_NUMBER,
2866 X_PREV_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.PREVIOUS_TERM_SEQUENCE_NUMBER,
2867 X_FUT_TERM_ADM_APPL_NUMBER => l_new_admission_appl_number,
2868 X_FUT_TERM_SEQUENCE_NUMBER => l_sequence_number,
2869 X_DEF_ACAD_CAL_TYPE => l_appl_inst_rec.DEF_ACAD_CAL_TYPE,
2870 X_DEF_ACAD_CI_SEQUENCE_NUM => l_appl_inst_rec.DEF_ACAD_CI_SEQUENCE_NUM,
2871 X_DEF_PREV_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_PREV_TERM_ADM_APPL_NUM,
2872 X_DEF_PREV_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_PREV_APPL_SEQUENCE_NUM,
2873 X_DEF_TERM_ADM_APPL_NUM => l_appl_inst_rec.DEF_TERM_ADM_APPL_NUM,
2874 X_DEF_APPL_SEQUENCE_NUM => l_appl_inst_rec.DEF_APPL_SEQUENCE_NUM,
2875 X_APPL_INST_STATUS => l_appl_inst_rec.APPL_INST_STATUS,
2876 X_AIS_REASON => l_appl_inst_rec.AIS_REASON,
2877 X_DECLINE_OFR_REASON => l_appl_inst_rec.DECLINE_OFR_REASON
2878 );
2879
2880 /* Update the new application instance PREVIOUS_TERM_ADM_APPL_NUMBER and PREVIOUS_TERM_SEQUENCE_NUMBER columns to link
2881 it with the old application */
2882
2883 OPEN c_get_appl_instance (l_appl_inst_rec.person_id, l_new_admission_appl_number,
2884 l_appl_inst_rec.nominated_course_cd, l_sequence_number);
2885 FETCH c_get_appl_instance INTO l_get_appl_instance;
2886 CLOSE c_get_appl_instance;
2887
2888 /* Update the new application instance PREVIOUS_TERM_ADM_APPL_NUMBER and
2889 PREVIOUS_TERM_SEQUENCE_NUMBER columns to link it with the old application */
2890
2891
2892 igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2893 X_ROWID => l_get_appl_instance.ROWID,
2894 X_PERSON_ID => l_get_appl_instance.PERSON_ID,
2895 X_ADMISSION_APPL_NUMBER => l_get_appl_instance.ADMISSION_APPL_NUMBER,
2896 X_NOMINATED_COURSE_CD => l_get_appl_instance.NOMINATED_COURSE_CD,
2897 X_SEQUENCE_NUMBER => l_get_appl_instance.SEQUENCE_NUMBER,
2898 X_PREDICTED_GPA => l_get_appl_instance.PREDICTED_GPA,
2899 X_ACADEMIC_INDEX => l_get_appl_instance.ACADEMIC_INDEX,
2900 X_ADM_CAL_TYPE => l_get_appl_instance.ADM_CAL_TYPE,
2901 X_APP_FILE_LOCATION => l_get_appl_instance.APP_FILE_LOCATION,
2902 X_ADM_CI_SEQUENCE_NUMBER => l_get_appl_instance.ADM_CI_SEQUENCE_NUMBER,
2903 X_COURSE_CD => l_get_appl_instance.COURSE_CD,
2904 X_APP_SOURCE_ID => l_get_appl_instance.APP_SOURCE_ID,
2905 X_CRV_VERSION_NUMBER => l_get_appl_instance.CRV_VERSION_NUMBER,
2906 X_WAITLIST_RANK => l_get_appl_instance.WAITLIST_RANK,
2907 X_LOCATION_CD => l_get_appl_instance.LOCATION_CD,
2908 X_ATTENT_OTHER_INST_CD => l_get_appl_instance.ATTENT_OTHER_INST_CD,
2909 X_ATTENDANCE_MODE => l_get_appl_instance.ATTENDANCE_MODE,
2910 X_EDU_GOAL_PRIOR_ENROLL_ID => l_get_appl_instance.EDU_GOAL_PRIOR_ENROLL_ID,
2911 X_ATTENDANCE_TYPE => l_get_appl_instance.ATTENDANCE_TYPE,
2912 X_DECISION_MAKE_ID => l_get_appl_instance.DECISION_MAKE_ID,
2913 X_UNIT_SET_CD => l_get_appl_instance.UNIT_SET_CD,
2914 X_DECISION_DATE => l_get_appl_instance.DECISION_DATE,
2915 X_ATTRIBUTE_CATEGORY => l_get_appl_instance.ATTRIBUTE_CATEGORY,
2916 X_ATTRIBUTE1 => l_get_appl_instance.ATTRIBUTE1,
2917 X_ATTRIBUTE2 => l_get_appl_instance.ATTRIBUTE2,
2918 X_ATTRIBUTE3 => l_get_appl_instance.ATTRIBUTE3,
2919 X_ATTRIBUTE4 => l_get_appl_instance.ATTRIBUTE4,
2920 X_ATTRIBUTE5 => l_get_appl_instance.ATTRIBUTE5,
2921 X_ATTRIBUTE6 => l_get_appl_instance.ATTRIBUTE6,
2922 X_ATTRIBUTE7 => l_get_appl_instance.ATTRIBUTE7,
2923 X_ATTRIBUTE8 => l_get_appl_instance.ATTRIBUTE8,
2924 X_ATTRIBUTE9 => l_get_appl_instance.ATTRIBUTE9,
2925 X_ATTRIBUTE10 => l_get_appl_instance.ATTRIBUTE10,
2926 X_ATTRIBUTE11 => l_get_appl_instance.ATTRIBUTE11,
2927 X_ATTRIBUTE12 => l_get_appl_instance.ATTRIBUTE12,
2928 X_ATTRIBUTE13 => l_get_appl_instance.ATTRIBUTE13,
2929 X_ATTRIBUTE14 => l_get_appl_instance.ATTRIBUTE14,
2930 X_ATTRIBUTE15 => l_get_appl_instance.ATTRIBUTE15,
2931 X_ATTRIBUTE16 => l_get_appl_instance.ATTRIBUTE16,
2932 X_ATTRIBUTE17 => l_get_appl_instance.ATTRIBUTE17,
2933 X_ATTRIBUTE18 => l_get_appl_instance.ATTRIBUTE18,
2934 X_ATTRIBUTE19 => l_get_appl_instance.ATTRIBUTE19,
2935 X_ATTRIBUTE20 => l_get_appl_instance.ATTRIBUTE20,
2936 X_DECISION_REASON_ID => l_get_appl_instance.DECISION_REASON_ID,
2937 X_US_VERSION_NUMBER => l_get_appl_instance.US_VERSION_NUMBER,
2938 X_DECISION_NOTES => l_get_appl_instance.DECISION_NOTES,
2939 X_PENDING_REASON_ID => l_get_appl_instance.PENDING_REASON_ID,
2940 X_PREFERENCE_NUMBER => l_get_appl_instance.PREFERENCE_NUMBER,
2941 X_ADM_DOC_STATUS => l_get_appl_instance.ADM_DOC_STATUS,
2942 X_ADM_ENTRY_QUAL_STATUS => l_get_appl_instance.ADM_ENTRY_QUAL_STATUS,
2943 X_DEFICIENCY_IN_PREP => l_get_appl_instance.DEFICIENCY_IN_PREP,
2944 X_LATE_ADM_FEE_STATUS => l_get_appl_instance.LATE_ADM_FEE_STATUS,
2945 X_SPL_CONSIDER_COMMENTS => l_get_appl_instance.SPL_CONSIDER_COMMENTS,
2946 X_APPLY_FOR_FINAID => l_get_appl_instance.APPLY_FOR_FINAID,
2947 X_FINAID_APPLY_DATE => l_get_appl_instance.FINAID_APPLY_DATE,
2948 X_ADM_OUTCOME_STATUS => l_get_appl_instance.ADM_OUTCOME_STATUS,
2949 X_ADM_OTCM_STAT_AUTH_PER_ID => l_get_appl_instance.ADM_OTCM_STATUS_AUTH_PERSON_ID,
2950 X_ADM_OUTCOME_STATUS_AUTH_DT => l_get_appl_instance.ADM_OUTCOME_STATUS_AUTH_DT,
2951 X_ADM_OUTCOME_STATUS_REASON => l_get_appl_instance.ADM_OUTCOME_STATUS_REASON,
2952 X_OFFER_DT => l_get_appl_instance.OFFER_DT,
2953 X_OFFER_RESPONSE_DT => l_get_appl_instance.OFFER_RESPONSE_DT,
2954 X_PRPSD_COMMENCEMENT_DT => l_get_appl_instance.PRPSD_COMMENCEMENT_DT,
2955 X_ADM_CNDTNL_OFFER_STATUS => l_get_appl_instance.ADM_CNDTNL_OFFER_STATUS,
2956 X_CNDTNL_OFFER_SATISFIED_DT => l_get_appl_instance.CNDTNL_OFFER_SATISFIED_DT,
2957 X_CNDNL_OFR_MUST_BE_STSFD_IND => l_get_appl_instance.CNDTNL_OFFER_MUST_BE_STSFD_IND,
2958 X_ADM_OFFER_RESP_STATUS => l_get_appl_instance.ADM_OFFER_RESP_STATUS,
2959 X_ACTUAL_RESPONSE_DT => l_get_appl_instance.ACTUAL_RESPONSE_DT,
2960 X_ADM_OFFER_DFRMNT_STATUS => l_get_appl_instance.ADM_OFFER_DFRMNT_STATUS,
2961 X_DEFERRED_ADM_CAL_TYPE => l_get_appl_instance.DEFERRED_ADM_CAL_TYPE,
2962 X_DEFERRED_ADM_CI_SEQUENCE_NUM => l_get_appl_instance.DEFERRED_ADM_CI_SEQUENCE_NUM,
2963 X_DEFERRED_TRACKING_ID => l_get_appl_instance.DEFERRED_TRACKING_ID,
2964 X_ASS_RANK => l_get_appl_instance.ASS_RANK,
2965 X_SECONDARY_ASS_RANK => l_get_appl_instance.SECONDARY_ASS_RANK,
2966 X_INTR_ACCEPT_ADVICE_NUM => l_get_appl_instance.intrntnl_acceptance_advice_num,
2967 X_ASS_TRACKING_ID => l_get_appl_instance.ASS_TRACKING_ID,
2968 X_FEE_CAT => l_get_appl_instance.FEE_CAT,
2969 X_HECS_PAYMENT_OPTION => l_get_appl_instance.HECS_PAYMENT_OPTION,
2970 X_EXPECTED_COMPLETION_YR => l_get_appl_instance.EXPECTED_COMPLETION_YR,
2971 X_EXPECTED_COMPLETION_PERD => l_get_appl_instance.EXPECTED_COMPLETION_PERD,
2972 X_CORRESPONDENCE_CAT => l_get_appl_instance.CORRESPONDENCE_CAT,
2973 X_ENROLMENT_CAT => l_get_appl_instance.ENROLMENT_CAT,
2974 X_FUNDING_SOURCE => l_get_appl_instance.FUNDING_SOURCE,
2975 X_APPLICANT_ACPTNCE_CNDTN => l_get_appl_instance.APPLICANT_ACPTNCE_CNDTN,
2976 X_CNDTNL_OFFER_CNDTN => l_get_appl_instance.CNDTNL_OFFER_CNDTN,
2977 X_MODE => 'R',
2978 X_SS_APPLICATION_ID => l_get_appl_instance.SS_APPLICATION_ID,
2979 X_SS_PWD => l_get_appl_instance.SS_PWD,
2980 X_AUTHORIZED_DT => l_get_appl_instance.AUTHORIZED_DT,
2981 X_AUTHORIZING_PERS_ID => l_get_appl_instance.AUTHORIZING_PERS_ID,
2982 X_ENTRY_STATUS => l_get_appl_instance.ENTRY_STATUS,
2983 X_ENTRY_LEVEL => l_get_appl_instance.ENTRY_LEVEL,
2984 X_SCH_APL_TO_ID => l_get_appl_instance.SCH_APL_TO_ID,
2985 X_IDX_CALC_DATE => l_get_appl_instance.IDX_CALC_DATE,
2986 X_WAITLIST_STATUS => l_get_appl_instance.WAITLIST_STATUS,
2987 X_ATTRIBUTE21 => l_get_appl_instance.ATTRIBUTE21,
2988 X_ATTRIBUTE22 => l_get_appl_instance.ATTRIBUTE22,
2989 X_ATTRIBUTE23 => l_get_appl_instance.ATTRIBUTE23,
2990 X_ATTRIBUTE24 => l_get_appl_instance.ATTRIBUTE24,
2991 X_ATTRIBUTE25 => l_get_appl_instance.ATTRIBUTE25,
2992 X_ATTRIBUTE26 => l_get_appl_instance.ATTRIBUTE26,
2993 X_ATTRIBUTE27 => l_get_appl_instance.ATTRIBUTE27,
2994 X_ATTRIBUTE28 => l_get_appl_instance.ATTRIBUTE28,
2995 X_ATTRIBUTE29 => l_get_appl_instance.ATTRIBUTE29,
2996 X_ATTRIBUTE30 => l_get_appl_instance.ATTRIBUTE30,
2997 X_ATTRIBUTE31 => l_get_appl_instance.ATTRIBUTE31,
2998 X_ATTRIBUTE32 => l_get_appl_instance.ATTRIBUTE32,
2999 X_ATTRIBUTE33 => l_get_appl_instance.ATTRIBUTE33,
3000 X_ATTRIBUTE34 => l_get_appl_instance.ATTRIBUTE34,
3001 X_ATTRIBUTE35 => l_get_appl_instance.ATTRIBUTE35,
3002 X_ATTRIBUTE36 => l_get_appl_instance.ATTRIBUTE36,
3003 X_ATTRIBUTE37 => l_get_appl_instance.ATTRIBUTE37,
3004 X_ATTRIBUTE38 => l_get_appl_instance.ATTRIBUTE38,
3005 X_ATTRIBUTE39 => l_get_appl_instance.ATTRIBUTE39,
3006 X_ATTRIBUTE40 => l_get_appl_instance.ATTRIBUTE40,
3007 X_FUT_ACAD_CAL_TYPE => l_get_appl_instance.FUTURE_ACAD_CAL_TYPE,
3008 X_FUT_ACAD_CI_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_ACAD_CI_SEQUENCE_NUMBER,
3009 X_FUT_ADM_CAL_TYPE => l_get_appl_instance.FUTURE_ADM_CAL_TYPE,
3010 X_FUT_ADM_CI_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_ADM_CI_SEQUENCE_NUMBER,
3011 X_PREV_TERM_ADM_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
3012 X_PREV_TERM_SEQUENCE_NUMBER => l_appl_inst_rec.SEQUENCE_NUMBER,
3013 X_FUT_TERM_ADM_APPL_NUMBER => l_get_appl_instance.FUTURE_TERM_ADM_APPL_NUMBER,
3014 X_FUT_TERM_SEQUENCE_NUMBER => l_get_appl_instance.FUTURE_TERM_SEQUENCE_NUMBER,
3015 X_DEF_ACAD_CAL_TYPE => l_get_appl_instance.DEF_ACAD_CAL_TYPE,
3016 X_DEF_ACAD_CI_SEQUENCE_NUM => l_get_appl_instance.DEF_ACAD_CI_SEQUENCE_NUM,
3017 X_DEF_PREV_TERM_ADM_APPL_NUM => l_get_appl_instance.DEF_PREV_TERM_ADM_APPL_NUM,
3018 X_DEF_PREV_APPL_SEQUENCE_NUM => l_get_appl_instance.DEF_PREV_APPL_SEQUENCE_NUM,
3019 X_DEF_TERM_ADM_APPL_NUM => l_get_appl_instance.DEF_TERM_ADM_APPL_NUM,
3020 X_DEF_APPL_SEQUENCE_NUM => l_get_appl_instance.DEF_APPL_SEQUENCE_NUM,
3021 X_APPL_INST_STATUS => l_get_appl_instance.APPL_INST_STATUS,
3022 X_AIS_REASON => l_get_appl_instance.AIS_REASON,
3023 X_DECLINE_OFR_REASON => l_get_appl_instance.DECLINE_OFR_REASON
3024 );
3025
3026 l_successful_records := l_successful_records + 1;
3027
3028 COMMIT;
3029 END IF;
3030
3031 ELSE -- Else of Application Instance
3032
3033 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
3034 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
3035 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
3036
3037 IF (l_message_name IS NULL) THEN
3038 igs_ad_gen_016.extract_msg_from_stack (
3039 p_msg_at_index => l_msg_at_index,
3040 p_return_status => l_return_status,
3041 p_msg_count => l_msg_count,
3042 p_msg_data => l_msg_data,
3043 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
3044
3045 IF (l_msg_count > 0) THEN
3046 fnd_file.put_line(fnd_file.log, l_msg_data);
3047 ELSE
3048 FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed while creating application instance of the new program');
3049 END IF;
3050 ELSE
3051 fnd_file.put_line(fnd_file.log, l_message_name);
3052 END IF;
3053 l_failed_records := l_failed_records + 1;
3054
3055 ROLLBACK TO c_create_application;
3056 END IF;
3057 EXCEPTION
3058 WHEN OTHERS THEN
3059
3060 fnd_file.put_line(fnd_file.log, 'Application Instance Creation Failed for Person ID: '|| RPAD(l_appl_inst_rec.person_id,15,' ') || '; Admission Application Number: ' ||
3061 RPAD(l_appl_inst_rec.admission_appl_number,2,' ') || '; Course Code: ' || RPAD(l_appl_inst_rec.nominated_course_cd,6,' ') || '; Sequence Number: '||
3062 RPAD(l_appl_inst_rec.sequence_number,6,' ') || ' Reason: ');
3063
3064 IF l_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
3065 l_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
3066 fnd_message.set_name('IGS', l_message_name);
3067 fnd_message.set_token('PGM', l_appl_inst_rec.nominated_course_cd);
3068 fnd_message.set_token('ALTCODE',l_appl_inst_rec.future_acad_cal_type||','||
3069 IGS_GE_NUMBER.TO_CANN(l_appl_inst_rec.future_acad_ci_sequence_number)
3070 ||'/'||l_appl_inst_rec.future_adm_cal_type||','||
3071 IGS_GE_NUMBER.TO_CANN(l_appl_inst_rec.future_adm_ci_sequence_number));
3072 fnd_file.put_line(fnd_file.log, fnd_message.get);
3073 ELSE
3074 fnd_message.set_name('IGS', l_message_name);
3075 fnd_file.put_line(fnd_file.log, fnd_message.get);
3076
3077 END IF;
3078 ROLLBACK TO c_create_application;
3079 END;
3080
3081 END IF;
3082
3083 END LOOP;
3084
3085 END IF;
3086
3087 fnd_file.put_line(fnd_file.log,'Total Number of Records Processed: ' || l_total_records);
3088 fnd_file.put_line(fnd_file.log,'Total Number of Successful Records: ' || l_successful_records);
3089 fnd_file.put_line(fnd_file.log,'Total Number of Unsucessful Records: ' || l_failed_records);
3090
3091 EXCEPTION WHEN OTHERS THEN
3092
3093 fnd_file.put_line(fnd_file.log, 'Exception From handle application log ' || l_message_name);
3094 retcode:=2;
3095
3096 END admp_init_reconsider;
3097
3098 END igs_ad_int_reconsider;