[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_VAL_ACAI_FTR_OFFER
Source
1 PACKAGE BODY igs_ad_val_acai_ftr_offer AS
2 /* $Header: IGSADB5B.pls 120.15 2006/05/29 12:07:07 apadegal ship $ */
3 /*******************************************************************************
4 Created by : Ramesh Rengarajan
5 Date created: 20 FEB 2002
6
7 Purpose:
8 To create future term application
9
10 Known limitations/enhancements and/or remarks:
11
12 Change History: (who, when, what: )
13 Who When What
14 ravishar 5-Sep-2005 Bug:- 4506750 added two new parametrs to Future term job and
15 added to code to copy qualifying code details to new application
16 pathipat 17-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
17 Modified TBH call to igs_ad_app_req_pkg to include 3 new columns
18 rrengara 3-DEC-2002 Fix for the Bug 2631918
19 vvutukur 27-Nov-2002 Enh#2584986.GL Interface Build.Modifications done in function copy_child_records.
20 nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build
21 RRENGARA 13-SEP-2002 Bug 2395510 added p_process parameter to call copy child records
22 rghosh 15-may-2003 Bug#2871426(Evaluator Entry and Assignment) Added the closed ind check in the cursor c_evaluators_cur
23 and in the call to insert row of igs_ad_appl_eval_pkg
24 *******************************************************************************/
25
26 FUNCTION copy_candidacy_records(p_new_admission_appl_number IGS_AD_APPL_ALL.admission_appl_number%TYPE,
27 p_new_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
28 p_person_id HZ_PARTIES.party_id%TYPE,
29 p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
30 p_old_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
31 p_nominated_course_cd IGS_AD_PS_APPL_ALL.nominated_course_cd%TYPE,
32 p_start_dt DATE)
33 RETURN BOOLEAN AS
34
35 /*******************************************************************************
36 Created by : Rishi Ghosh
37 Date created: 01-JUN-2004
38
39 Purpose: To copy candidacy records from the current applicationto the
40 Future-Term application. This procedure is also used for
41 creating deferment application. (Bug#3656905)
42
43
44 Known limitations/enhancements and/or remarks:
45
46 Change History: (who, when, what: )
47 Who When What
48 *******************************************************************************/
49
50 l_get_modified_comm_dt DATE;
51
52 CURSOR c_get_candidature (cp_person_id igs_re_candidature.person_id%TYPE,
53 cp_acai_admission_appl_number igs_re_candidature.acai_admission_appl_number%TYPE,
54 cp_acai_nominated_course_cd igs_re_candidature.acai_nominated_course_cd%TYPE,
55 cp_acai_sequence_number igs_re_candidature.acai_sequence_number%TYPE) IS
56 SELECT *
57 FROM igs_re_candidature
58 WHERE person_id = cp_person_id
59 AND acai_admission_appl_number = cp_acai_admission_appl_number
60 AND acai_nominated_course_cd = cp_acai_nominated_course_cd
61 AND acai_sequence_number = cp_acai_sequence_number;
62
63 CURSOR c_get_candidature_sequence IS
64 SELECT igs_re_candidature_seq_num_s.nextval
65 FROM dual;
66
67 l_get_candidature_sequence igs_re_candidature.sequence_number%TYPE;
68
69 CURSOR c_get_thesis (cp_person_id igs_re_thesis_all.person_id%TYPE,
70 cp_ca_sequence_number igs_re_thesis_all.ca_sequence_number%TYPE) IS
71 SELECT *
72 FROM igs_re_thesis_all
73 WHERE person_id = cp_person_id
74 AND ca_sequence_number = cp_ca_sequence_number;
75
76 CURSOR c_get_thesis_sequence IS
77 SELECT igs_re_thesis_seq_num_s.nextval
78 FROM dual;
79
80 l_get_thesis_sequence igs_re_thesis_all.sequence_number%TYPE;
81
82 CURSOR c_get_cdt_fld_of_sy (cp_person_id igs_re_cdt_fld_of_sy.person_id%TYPE,
83 cp_ca_sequence_number igs_re_cdt_fld_of_sy.ca_sequence_number%TYPE) IS
84 SELECT *
85 FROM igs_re_cdt_fld_of_sy
86 WHERE person_id = cp_person_id
87 AND ca_sequence_number = cp_ca_sequence_number;
88
89 CURSOR c_get_cand_seo_cls (cp_person_id igs_re_cand_seo_cls.person_id%TYPE,
90 cp_ca_sequence_number igs_re_cand_seo_cls.ca_sequence_number%TYPE) IS
91 SELECT *
92 FROM igs_re_cand_seo_cls
93 WHERE person_id = cp_person_id
94 AND ca_sequence_number = cp_ca_sequence_number;
95
96 CURSOR c_get_scholarship (cp_person_id igs_re_scholarship_all.person_id%TYPE,
97 cp_ca_sequence_number igs_re_scholarship_all.ca_sequence_number%TYPE) IS
98 SELECT *
99 FROM igs_re_scholarship_all
100 WHERE person_id = cp_person_id
101 AND ca_sequence_number = cp_ca_sequence_number;
102
103 CURSOR c_get_milestone (cp_person_id igs_pr_milestone_all.person_id%TYPE,
104 cp_ca_sequence_number igs_pr_milestone_all.ca_sequence_number%TYPE) IS
105 SELECT *
106 FROM igs_pr_milestone_all
107 WHERE person_id = cp_person_id
108 AND ca_sequence_number = cp_ca_sequence_number;
109
110 CURSOR c_get_supervisor (cp_ca_person_id igs_re_sprvsr.ca_person_id%TYPE,
111 cp_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE) IS
112 SELECT *
113 FROM igs_re_sprvsr
114 WHERE ca_person_id = cp_ca_person_id
115 AND ca_sequence_number = cp_ca_sequence_number
116 ORDER BY start_dt,end_dt;
117
118 CURSOR c_get_thesis_exam (cp_person_id igs_re_thesis_exam.person_id%TYPE,
119 cp_ca_sequence_number igs_re_thesis_exam.ca_sequence_number%TYPE,
120 cp_the_sequence_number igs_re_thesis_exam.the_sequence_number%TYPE) IS
121 SELECT *
122 FROM igs_re_thesis_exam
123 WHERE person_id = cp_person_id
124 AND ca_sequence_number = cp_ca_sequence_number
125 AND the_sequence_number = cp_the_sequence_number;
126
127 CURSOR c_get_thesis_panel_memb (cp_ca_person_id igs_re_ths_pnl_mbr.ca_person_id%TYPE,
128 cp_ca_sequence_number igs_re_ths_pnl_mbr.ca_sequence_number%TYPE,
129 cp_the_sequence_number igs_re_ths_pnl_mbr.the_sequence_number%TYPE,
130 cp_creation_dt igs_re_ths_pnl_mbr.creation_dt%TYPE) IS
131 SELECT *
132 FROM igs_re_ths_pnl_mbr
133 WHERE ca_person_id = cp_ca_person_id
134 AND ca_sequence_number = cp_ca_sequence_number
135 AND the_sequence_number = cp_the_sequence_number
136 AND creation_dt = cp_creation_dt;
137
138
139 l_rowid VARCHAR2(30);
140 l_rowid_child1 VARCHAR2(30);
141 l_rowid_child2 VARCHAR2(30);
142 l_rowid_child3 VARCHAR2(30);
143
144 l_min_submission_dt igs_re_candidature.min_submission_dt%TYPE;
145 l_max_submission_dt igs_re_candidature.max_submission_dt%TYPE;
146
147 l_supvsr_start_dt igs_re_sprvsr.start_dt%TYPE;
148 l_supvsr_end_dt igs_re_sprvsr.end_dt%TYPE;
149 l_comm_date_offset NUMBER;
150
151
152 l_submission_dt igs_re_thesis_exam.submission_dt%TYPE;
153 l_expected_submission_dt igs_re_thesis.expected_submission_dt%TYPE;
154 l_embargo_expiry_dt igs_re_thesis.embargo_expiry_dt%TYPE;
155
156 l_sysdate DATE ;
157
158 v_message_name VARCHAR2(30);
159
160
161 BEGIN
162
163 l_sysdate := SYSDATE;
164
165
166 l_get_modified_comm_dt := IGS_RE_GEN_001.RESP_GET_CA_COMM(
167 p_person_id,
168 NULL,
169 p_old_admission_appl_number,
170 p_nominated_course_cd,
171 p_old_sequence_number);
172
173
174 FOR l_get_candidature_rec IN c_get_candidature(p_person_id,
175 p_old_admission_appl_number,
176 p_nominated_course_cd,
177 p_old_sequence_number) LOOP
178
179 v_message_name:= 'IGS_AD_RSCH_CAND';
180
181
182 l_comm_date_offset := TRUNC(p_start_dt) - TRUNC(l_get_modified_comm_dt);
183
184 l_min_submission_dt := l_get_candidature_rec.min_submission_dt + l_comm_date_offset;
185 l_max_submission_dt := l_get_candidature_rec.max_submission_dt + l_comm_date_offset;
186
187 OPEN c_get_candidature_sequence;
188 FETCH c_get_candidature_sequence INTO l_get_candidature_sequence;
189 CLOSE c_get_candidature_sequence;
190
191 l_rowid := NULL;
192
193 IGS_RE_CANDIDATURE_PKG.INSERT_ROW(
194 X_ROWID => l_rowid,
195 X_PERSON_ID => p_person_id,
196 X_SEQUENCE_NUMBER => l_get_candidature_sequence,
197 X_SCA_COURSE_CD => l_get_candidature_rec.sca_course_cd,
198 X_ACAI_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
199 X_ACAI_NOMINATED_COURSE_CD => p_nominated_course_cd,
200 X_ACAI_SEQUENCE_NUMBER => p_new_sequence_number,
201 X_ATTENDANCE_PERCENTAGE => l_get_candidature_rec.attendance_percentage,
202 X_GOVT_TYPE_OF_ACTIVITY_CD => l_get_candidature_rec.govt_type_of_activity_cd,
203 X_MAX_SUBMISSION_DT => l_max_submission_dt,
204 X_MIN_SUBMISSION_DT => l_min_submission_dt,
205 X_RESEARCH_TOPIC => l_get_candidature_rec.research_topic,
206 X_INDUSTRY_LINKS => l_get_candidature_rec.industry_links,
207 X_MODE => 'R',
208 X_ORG_ID => l_get_candidature_rec.org_id
209 );
210
211 FOR l_get_thesis_rec IN c_get_thesis(p_person_id,l_get_candidature_rec.sequence_number) LOOP
212
213 v_message_name:= 'IGS_AD_RSCH_THS';
214
215 l_expected_submission_dt := l_get_thesis_rec.expected_submission_dt + l_comm_date_offset;
216 l_embargo_expiry_dt := l_get_thesis_rec.embargo_expiry_dt + l_comm_date_offset;
217
218 IF l_get_thesis_rec.logical_delete_dt IS NULL THEN
219
220 OPEN c_get_thesis_sequence;
221 FETCH c_get_thesis_sequence INTO l_get_thesis_sequence;
222 CLOSE c_get_thesis_sequence;
223
224 l_rowid_child1 := NULL;
225
226 IGS_RE_THESIS_PKG.INSERT_ROW(
227 X_ROWID => l_rowid_child1,
228 X_PERSON_ID => p_person_id,
229 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
230 X_SEQUENCE_NUMBER => l_get_thesis_sequence,
231 X_TITLE => l_get_thesis_rec.title,
232 X_FINAL_TITLE_IND => l_get_thesis_rec.final_title_ind,
233 X_SHORT_TITLE => l_get_thesis_rec.short_title,
234 X_ABBREVIATED_TITLE => l_get_thesis_rec.abbreviated_title,
235 X_THESIS_RESULT_CD => l_get_thesis_rec.thesis_result_cd,
236 X_EXPECTED_SUBMISSION_DT => l_expected_submission_dt,
237 X_LIBRARY_LODGEMENT_DT => l_get_thesis_rec.library_lodgement_dt,
238 X_LIBRARY_CATALOGUE_NUMBER => l_get_thesis_rec.library_catalogue_number,
239 X_EMBARGO_EXPIRY_DT => l_get_thesis_rec.embargo_expiry_dt,
240 X_THESIS_FORMAT => l_get_thesis_rec.thesis_format,
241 X_LOGICAL_DELETE_DT => l_get_thesis_rec.logical_delete_dt,
242 X_EMBARGO_DETAILS => l_embargo_expiry_dt,
243 X_THESIS_TOPIC => l_get_thesis_rec.thesis_topic,
244 X_CITATION => l_get_thesis_rec.citation,
245 X_COMMENTS => l_get_thesis_rec.comments,
246 X_MODE => 'R',
247 X_ORG_ID => l_get_thesis_rec.org_id
248 );
249
250 FOR l_get_thesis_exam_rec IN c_get_thesis_exam (p_person_id,
251 l_get_candidature_rec.sequence_number,
252 l_get_thesis_rec.sequence_number) LOOP
253
254 v_message_name:= 'IGS_AD_RSCH_EXAM';
255
256 l_submission_dt := l_get_thesis_exam_rec.submission_dt + l_comm_date_offset;
257 l_rowid_child2 := NULL;
258
259 IGS_RE_THESIS_EXAM_PKG.INSERT_ROW(
260 X_ROWID => l_rowid_child2,
261 X_PERSON_ID => p_person_id,
262 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
263 X_THE_SEQUENCE_NUMBER => l_get_thesis_sequence,
264 X_CREATION_DT => l_sysdate,
265 X_SUBMISSION_DT => l_submission_dt,
266 X_THESIS_EXAM_TYPE => l_get_thesis_exam_rec.thesis_exam_type,
267 X_THESIS_PANEL_TYPE => l_get_thesis_exam_rec.thesis_panel_type,
268 X_TRACKING_ID => l_get_thesis_exam_rec.tracking_id,
269 X_THESIS_RESULT_CD => l_get_thesis_exam_rec.thesis_result_cd,
270 X_MODE => 'R'
271 );
272
273 FOR l_get_thesis_panel_memb_rec IN c_get_thesis_panel_memb (p_person_id,
274 l_get_candidature_rec.sequence_number,
275 l_get_thesis_rec.sequence_number,
276 l_get_thesis_exam_rec.creation_dt) LOOP
277
278 v_message_name:= 'IGS_AD_RSCH_EXAM_PNL_MEM';
279
280 l_rowid_child3 := NULL;
281
282 IGS_RE_THS_PNL_MBR_PKG.INSERT_ROW(
283 X_ROWID => l_rowid_child3,
284 X_CA_PERSON_ID => p_person_id,
285 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
286 X_THE_SEQUENCE_NUMBER => l_get_thesis_sequence,
287 X_CREATION_DT => l_sysdate,
288 X_PERSON_ID => l_get_thesis_panel_memb_rec.person_id,
289 X_PANEL_MEMBER_TYPE => l_get_thesis_panel_memb_rec.panel_member_type,
290 X_CONFIRMED_DT => l_get_thesis_panel_memb_rec.confirmed_dt,
291 X_DECLINED_DT => l_get_thesis_panel_memb_rec.declined_dt,
292 X_ANONYMITY_IND => l_get_thesis_panel_memb_rec.anonymity_ind,
293 X_THESIS_RESULT_CD => l_get_thesis_panel_memb_rec.thesis_result_cd,
294 X_PAID_DT => l_get_thesis_panel_memb_rec.paid_dt,
295 X_TRACKING_ID => l_get_thesis_panel_memb_rec.tracking_id,
296 X_RECOMMENDATION_SUMMARY => l_get_thesis_panel_memb_rec.recommendation_summary,
297 X_MODE => 'R'
298 );
299
300 END LOOP;
301
302 END LOOP;
303
304 END IF;
305
306 END LOOP;
307
308 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
309
310 v_message_name:= 'IGS_AD_RSCH_CAND_FOS';
311
312 l_rowid_child1 := NULL;
313
314 IGS_RE_CDT_FLD_OF_SY_PKG.INSERT_ROW(
315 X_ROWID => l_rowid_child1,
316 X_PERSON_ID => p_person_id,
317 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
318 X_FIELD_OF_STUDY => l_get_cdt_fld_of_sy_rec.field_of_study,
319 X_PERCENTAGE => l_get_cdt_fld_of_sy_rec.percentage,
320 X_MODE => 'R'
321 );
322
323 END LOOP;
324
325 FOR l_get_cand_seo_cls_rec IN c_get_cand_seo_cls(p_person_id,l_get_candidature_rec.sequence_number) LOOP
326
327 v_message_name:= 'IGS_AD_RSCH_SEO';
328
329 l_rowid_child1 := NULL;
330
331 IGS_RE_CAND_SEO_CLS_PKG.INSERT_ROW(
332 X_ROWID => l_rowid_child1,
333 X_PERSON_ID => p_person_id,
334 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
335 X_SEO_CLASS_CD => l_get_cand_seo_cls_rec.seo_class_cd,
336 X_PERCENTAGE => l_get_cand_seo_cls_rec.percentage,
337 X_MODE => 'R'
338 );
339
340 END LOOP;
341
342 FOR l_get_scholarship_rec IN c_get_scholarship(p_person_id,l_get_candidature_rec.sequence_number) LOOP
343
344 v_message_name:= 'IGS_AD_RSCH_SCH';
345
346 l_rowid_child1 := NULL;
347
348 IGS_RE_SCHOLARSHIP_PKG.INSERT_ROW(
349 X_ROWID => l_rowid_child1,
350 X_PERSON_ID => p_person_id,
351 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
352 X_SCHOLARSHIP_TYPE => l_get_scholarship_rec.scholarship_type,
353 X_START_DT => l_get_scholarship_rec.start_dt,
354 X_END_DT => l_get_scholarship_rec.end_dt,
355 X_DOLLAR_VALUE => l_get_scholarship_rec.dollar_value,
356 X_DESCRIPTION => l_get_scholarship_rec.description,
357 X_OTHER_BENEFITS => l_get_scholarship_rec.other_benefits,
358 X_CONDITIONS => l_get_scholarship_rec.conditions,
359 X_MODE => 'R',
360 X_ORG_ID => l_get_scholarship_rec.org_id
361 );
362
363 END LOOP;
364
365 FOR l_get_milestone_rec IN c_get_milestone(p_person_id,l_get_candidature_rec.sequence_number) LOOP
366
367 v_message_name:= 'IGS_AD_RSCH_PR_MLSTN';
368
369 l_rowid_child1 := NULL;
370
371 IGS_PR_MILESTONE_PKG.INSERT_ROW(
372 X_ROWID => l_rowid_child1,
373 X_PERSON_ID => p_person_id,
374 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
375 X_SEQUENCE_NUMBER => l_get_milestone_rec.sequence_number,
376 X_MILESTONE_TYPE => l_get_milestone_rec.milestone_type,
377 X_MILESTONE_STATUS => l_get_milestone_rec.milestone_status,
378 X_DUE_DT => l_get_milestone_rec.due_dt,
379 X_DESCRIPTION => l_get_milestone_rec.description,
380 X_ACTUAL_REACHED_DT => l_get_milestone_rec.actual_reached_dt,
381 X_PRECED_SEQUENCE_NUMBER => l_get_milestone_rec.preced_sequence_number,
382 X_OVRD_NTFCTN_IMMINENT_DAYS => l_get_milestone_rec.ovrd_ntfctn_imminent_days,
383 X_OVRD_NTFCTN_REMINDER_DAYS => l_get_milestone_rec.ovrd_ntfctn_reminder_days,
384 X_OVRD_NTFCTN_RE_REMINDER_DAYS => l_get_milestone_rec.ovrd_ntfctn_re_reminder_days,
385 X_COMMENTS => l_get_milestone_rec.comments,
386 X_MODE => 'R',
387 X_ORG_ID => l_get_milestone_rec.org_id
388 );
389
390 END LOOP;
391
392 FOR l_get_supervisor_rec IN c_get_supervisor(p_person_id,l_get_candidature_rec.sequence_number) LOOP
393
394 v_message_name:= 'IGS_AD_RSCH_SPVSR';
395
396 l_supvsr_start_dt := l_get_supervisor_rec.start_dt + l_comm_date_offset;
397 l_supvsr_end_dt := l_get_supervisor_rec.end_dt + l_comm_date_offset;
398
399 l_rowid_child1 := NULL;
400
401 IGS_RE_SPRVSR_PKG.INSERT_ROW(
402 X_ROWID => l_rowid_child1,
403 X_CA_PERSON_ID => p_person_id,
404 X_CA_SEQUENCE_NUMBER => l_get_candidature_sequence,
405 X_PERSON_ID => l_get_supervisor_rec.person_id,
406 X_SEQUENCE_NUMBER => l_get_supervisor_rec.sequence_number,
407 X_START_DT => l_supvsr_start_dt,
408 X_END_DT => l_supvsr_end_dt,
409 X_RESEARCH_SUPERVISOR_TYPE => l_get_supervisor_rec.research_supervisor_type,
410 X_SUPERVISOR_PROFESSION => l_get_supervisor_rec.supervisor_profession,
411 X_SUPERVISION_PERCENTAGE => l_get_supervisor_rec.supervision_percentage,
412 X_FUNDING_PERCENTAGE => l_get_supervisor_rec.funding_percentage,
413 X_ORG_UNIT_CD => l_get_supervisor_rec.org_unit_cd,
414 X_OU_START_DT => l_get_supervisor_rec.ou_start_dt,
415 X_REPLACED_PERSON_ID => l_get_supervisor_rec.replaced_person_id,
416 X_REPLACED_SEQUENCE_NUMBER => l_get_supervisor_rec.replaced_sequence_number,
417 X_COMMENTS => l_get_supervisor_rec.comments,
418 X_MODE => 'R'
419 );
420
421
422 END LOOP;
423
424 END LOOP;
425
426 RETURN TRUE;
427
428 EXCEPTION WHEN OTHERS THEN
429
430 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CAND_COPY_FAIL');
431 Fnd_File.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
432
433 FND_MESSAGE.SET_NAME('IGS',v_message_name);
434 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET || SQLERRM);
435
436 RETURN FALSE;
437
438 END copy_candidacy_records;
439
440
441 PROCEDURE admp_val_offer_future_term(
442 errbuf out NOCOPY varchar2,
443 retcode out NOCOPY number ,
444 p_person_id hz_parties.party_id%TYPE,
445 p_group_id igs_pe_persid_group.group_id%TYPE,
446 p_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%TYPE,
447 p_prev_acad_adm_cal VARCHAR2,
448 p_future_acad_adm_cal VARCHAR2,
449 p_offer_dt VARCHAR2,
450 p_offer_response_dt VARCHAR2,
451 p_application_type VARCHAR2 DEFAULT NULL,
452 p_application_id NUMBER DEFAULT NULL
453 ) IS
454 /*******************************************************************************
455 Created by : Ramesh Rengarajan
456 Date created: 20 FEB 2002
457
458 Purpose:
459 To create future term application
460
461 Known limitations/enhancements and/or remarks:
462
463 Change History: (who, when, what: )
464 Who When What
465 ********************************************************************************
466 ravishar 5-Sep-2005 Added two new parameters p_application_type,
467 p_application_id to the Procedure
468 */
469
470 -- Variable Declarations ----------------------------------------------------------------------------------------------------
471
472 -- Resolve the parameters to get calendar types and sequence numbers
473 p_prev_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
474 p_prev_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE;
475 p_prev_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
476 p_prev_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE;
477
478 p_fut_acad_cal_type igs_ad_appl.acad_cal_type%TYPE;
479 p_fut_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE;
480 p_fut_adm_cal_type igs_ad_appl.adm_cal_type%TYPE;
481 p_fut_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE;
482
483 l_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE;
484 l_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE;
485
486 l_message_name VARCHAR2(1000);
487 l_new_admission_appl_number igs_ad_appl.admission_appl_number%TYPE;
488 l_new_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
489
490 l_person_number hz_parties.party_number%TYPE;
491 l_group_desc igs_pe_persid_group.description%TYPE;
492
493 ---End Variable Declarations--------------------------------------------------------------------------------------------
494
495 -------------Cursor Declarations ----------------------------------------------------------------------------------------
496
497 CURSOR c_pernum_cur IS
498 SELECT
499 person_number
500 FROM
501 igs_pe_person_base_v
502 WHERE
503 person_id = p_person_id;
504
505
506 CURSOR c_pergr_cur IS
507 SELECT
508 description
509 FROM
510 igs_pe_persid_group
511 WHERE
512 group_id = p_group_id;
513
514 -- Cursor for getting the alternade academic/admission code for a given academic/admission cal type and sequence number
515
516 CURSOR acad_adm_alt_code (
517 p_acad_cal_type igs_ca_inst.cal_type%TYPE,
518 p_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
519 p_adm_cal_type igs_ca_inst.cal_type%TYPE,
520 p_adm_ci_sequence_number igs_ca_inst.sequence_number%TYPE
521 ) IS
522 SELECT c1.alternate_code||' / '||c2.alternate_code
523 FROM igs_ca_inst c1, igs_ca_inst c2
524 WHERE c1.cal_type = p_acad_cal_type
525 AND c1.sequence_number = p_acad_ci_sequence_number
526 AND c2.cal_type = p_adm_cal_type
527 AND c2.sequence_number = p_adm_ci_sequence_number;
528
529 cur_acad_adm_alt_code VARCHAR2(200);
530 fut_acad_adm_alt_code VARCHAR2(200);
531
532 -- Cursor to get the current application attributes -----
533
534 CURSOR c_appl_inst(p_person_id hz_parties.party_id%TYPE) IS
535 SELECT
536 acai.*
537 FROM
538 igs_ad_appl aa,
539 igs_ad_ps_appl_inst acai, /* Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst Bug 3150054 */
540 igs_ad_ou_stat aous
541 WHERE
542 acai.adm_outcome_status = aous.adm_outcome_status
543 AND aous.s_adm_outcome_status = 'OFFER-FUTURE-TERM'
544 AND acai.future_acad_cal_type = NVL(p_fut_acad_cal_type, acai.future_acad_cal_type)
545 AND acai.future_acad_ci_sequence_number = NVL(p_fut_acad_cal_seq_no, acai.future_acad_ci_sequence_number)
546 AND acai.future_adm_cal_type = NVL(p_fut_adm_cal_type, acai.future_adm_cal_type)
547 AND acai.future_adm_ci_sequence_number = NVL(p_fut_adm_cal_seq_no, acai.future_adm_ci_sequence_number)
548 AND acai.future_term_adm_appl_number IS NULL
549 AND acai.future_term_sequence_number IS NULL
550 AND acai.person_id = p_person_id
551 AND acai.nominated_course_cd = NVL (p_nominated_course_cd, acai.nominated_course_cd)
552 AND aa.person_id = acai.person_id
553 AND aa.admission_appl_number = acai.admission_appl_number
554 AND aa.acad_cal_type = NVL ( p_prev_acad_cal_type, aa.acad_cal_type)
555 AND aa.acad_ci_sequence_number = NVL ( p_prev_acad_cal_seq_no, aa.acad_ci_sequence_number)
556 AND NVL(acai.adm_cal_type,aa.adm_cal_type) = NVL( p_prev_adm_cal_type, acai.adm_cal_type)
557 AND NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) = NVL ( p_prev_adm_cal_seq_no, acai.adm_ci_sequence_number)
558 AND aa.APPLICATION_TYPE = NVL(p_application_type,aa.APPLICATION_TYPE)
559 AND aa.APPLICATION_ID = NVL(p_application_id,aa.APPLICATION_ID);
560
561 CURSOR c_group_id(p_person_id hz_parties.party_id%TYPE,p_group_id igs_pe_persid_group.group_id%TYPE) IS
562 SELECT 'X'
563 FROM igs_pe_prsid_grp_mem
564 WHERE person_id = p_person_id
565 AND group_id = p_group_id
566 AND NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
567 AND NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
568
569 l_exists VARCHAR2(1);
570 l_pers_grp VARCHAR2(1);
571
572 CURSOR c_get_acad_cal_info(cp_person_id igs_ad_appl_all.person_id%TYPE,
573 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
574 SELECT acad_cal_type,acad_ci_sequence_number
575 FROM igs_ad_appl_all
576 WHERE person_id = cp_person_id
577 AND admission_appl_number = cp_admission_appl_number;
578
579 l_get_acad_cal_info c_get_acad_cal_info%ROWTYPE;
580
581 v_start_dt DATE;
582 l_applcreated_flag BOOLEAN;
583
584 -- pfotedar bug no. 3713735
585 l_query VARCHAR2(1000);
586 TYPE c_ref_cur_typ IS REF CURSOR;
587 c_ref_cur c_ref_cur_typ;
588
589 TYPE c_ref_cur_rec_typ IS RECORD (person_id NUMBER);
590 c_ref_cur_rec c_ref_cur_rec_typ;
591
592 -- for bug 5245277 and sql id 17651699 and 17651677
593 l_status VARCHAR2(1);
594 l_group_type IGS_PE_PERSID_GROUP_V.group_type%type;
595
596 -- End cursor declarations -----------------------------------------------------------------------------------------------
597 BEGIN
598
599 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
600 igs_ge_gen_003.set_org_id(null);
601
602 p_prev_acad_cal_type := rtrim (substr (p_prev_acad_adm_cal, 1,10));
603 p_prev_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr(p_prev_acad_adm_cal, 13,7));
604 p_prev_adm_cal_type := rtrim (substr (p_prev_acad_adm_cal, 23,10));
605 p_prev_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_prev_acad_adm_cal, 35,7));
606
607 p_fut_acad_cal_type := rtrim (substr (p_future_acad_adm_cal, 1,10));
608 p_fut_acad_cal_seq_no := IGS_GE_NUMBER.TO_NUM (substr (p_future_acad_adm_cal, 13,7));
609 p_fut_adm_cal_type := rtrim (substr (p_future_acad_adm_cal, 23,10));
610 p_fut_adm_cal_seq_no := IGS_GE_NUMBER.TO_NUM(substr (p_future_acad_adm_cal, 35,7));
611
612 -- Added the Log file entries for the passed parameters
613 -- By rrengara for Bug 2690354 on 19-DEC-2002
614
615 OPEN c_pernum_cur;
616 FETCH c_pernum_cur INTO l_person_number;
617 CLOSE c_pernum_cur;
618
619 OPEN c_pergr_cur;
620 FETCH c_pergr_cur INTO l_group_desc;
621 CLOSE c_pergr_cur;
622
623 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Person Number',38) || ' : ' || l_person_number);
624 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Person ID Group' ,38) || ' : ' || l_group_desc);
625 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Program',38) || ' : ' || p_nominated_course_cd);
626
627 IF p_prev_acad_adm_cal IS NOT NULL THEN
628 OPEN acad_adm_alt_code( p_prev_acad_cal_type,
629 p_prev_acad_cal_seq_no,
630 p_prev_adm_cal_type,
631 p_prev_adm_cal_seq_no);
632 FETCH acad_adm_alt_code INTO cur_acad_adm_alt_code;
633 CLOSE acad_adm_alt_code;
634 END IF;
635
636 IF p_future_acad_adm_cal IS NOT NULL THEN
637 OPEN acad_adm_alt_code(p_fut_acad_cal_type,
638 p_fut_acad_cal_seq_no,
639 p_fut_adm_cal_type,
640 p_fut_adm_cal_seq_no);
641 FETCH acad_adm_alt_code INTO fut_acad_adm_alt_code;
642 CLOSE acad_adm_alt_code;
643 END IF;
644
645 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Current Academic / Admission Calendar',38) || ' : ' || cur_acad_adm_alt_code );
646 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Future Academic / Admission Calendar',38) || ' : ' || fut_acad_adm_alt_code );
647
648
649 l_offer_dt := igs_ge_date.igsdate(p_offer_dt);
650 l_offer_response_dt := igs_ge_date.igsdate(p_offer_response_dt);
651
652 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Offer Date',38) || ' : ' || l_offer_dt);
653 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('Override Offer Response Date',38) || ' : ' || l_offer_response_dt);
654 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
655
656 -- check whether the parameter combination passed is correct
657 -- if user gives both person group and person
658 -- then it should check whether the person exists in that group
659 -- if the person doesnot exists, it gives a message --rghosh bug#2767294
660
661 IF (p_group_id IS NULL AND p_person_id IS NULL AND p_application_id IS NULL)THEN
662 fnd_file.put_line(fnd_file.log, 'Application could not be created');
663 fnd_file.put_line(fnd_file.log, 'Either Person Number or Person ID Group or Application ID should be passed');
664 ELSIF (p_group_id IS NOT NULL AND p_person_id IS NOT NULL) THEN
665 OPEN c_group_id(p_person_id,p_group_id);
666 FETCH c_group_id INTO l_exists;
667 IF c_group_id%NOTFOUND THEN
668 fnd_file.put_line(fnd_file.log, 'Application could not be created');
669 fnd_file.put_line(fnd_file.log, 'Person does not exists in the Person ID Group');
670 END IF;
671 CLOSE c_group_id;
672 END IF;
673
674 -- If the user wants to do the process for list of persons
675 -- use the record group he has given in the parameter and iterate on the list
676 -- of persons in the group
677 l_applcreated_flag := TRUE;
678
679 IF p_group_id IS NOT NULL THEN
680
681 ---- begin through bug 5245277 for sql ids 17651699, 17651677
682 l_query := IGS_PE_DYNAMIC_PERSID_GROUP.GET_DYNAMIC_SQL(p_group_id, l_status, l_group_type);
683
684 IF (l_query IS NOT NULL AND l_status ='S')
685 THEN
686 ---- end through bug 5245277 for sql ids 17651699, 17651677
687 IF p_person_id IS NOT NULL THEN --When p_group_id is not null and p_person_id is not null
688
689 l_query := l_query || ' and person_id = :2'; --bug 5245277 and sql id 17651699, 17651677
690 OPEN c_ref_cur FOR l_query USING p_group_id, p_person_id;
691
692 ELSE --When p_group_id is not null and p_person_id is null
693 OPEN c_ref_cur FOR l_query USING p_group_id;
694 END IF;
695 LOOP
696 FETCH c_ref_cur INTO c_ref_cur_rec;
697 EXIT WHEN c_ref_cur%NOTFOUND;
698 -- Find out NOCOPY the list applications
699 FOR c_appl_inst_rec IN c_appl_inst(c_ref_cur_rec.person_id) LOOP
700 l_applcreated_flag := FALSE;
701
702 -- UPDATE the log file with application details
703 fnd_file.put_line(fnd_file.log, 'Creating Future Term Application for ' );
704 fnd_file.put_line(fnd_file.log,RPAD( ' Person Id',29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.person_id));
705 fnd_file.put_line(fnd_file.log, RPAD(' Admission Application Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.admission_appl_number));
706 fnd_file.put_line(fnd_file.log, RPAD(' Nominated course Code',29) || ' : ' || c_appl_inst_rec.nominated_course_cd);
707 fnd_file.put_line(fnd_file.log,RPAD(' Sequence Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN( c_appl_inst_rec.sequence_number ));
708
709 -- Set the save point here
710 -- Since when the application related transaction fails we need to rollback the transaction for that application
711 -- and it should proceed to the next record in the cursor
712
713 SAVEPOINT sp_save_point1;
714
715 -- Call handle application
716 -- this procedure returns true if the application, application program and application instance insert is success
717 -- otherwise it returns FALSE. This procedure also inserts the new application and instances for the future term
718 -- if the new application is created it returns the new admission application number and new sequence number as an
719 -- out NOCOPY parameter. This variables will be used for copying the child related records from the old to the new application
720
721 IF handle_application(
722 p_person_id => c_ref_cur_rec.person_id,
723 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
724 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
725 p_sequence_number => c_appl_inst_rec.sequence_number,
726 p_fut_acad_cal_type => c_appl_inst_rec.future_acad_cal_type ,
727 p_fut_acad_cal_seq_no => c_appl_inst_rec.future_acad_ci_sequence_number,
728 p_fut_adm_cal_type => c_appl_inst_rec.future_adm_cal_type,
729 p_fut_adm_cal_seq_no => c_appl_inst_rec.future_adm_ci_sequence_number,
730 p_new_admission_appl_number=> l_new_admission_appl_number,
731 p_new_sequence_number => l_new_sequence_number) THEN
732
733
734 -- if the creation is successful then copy all the child records
735 -- if any error occurs during copying of any of the child record
736 -- rollback upto save point 1 and proceed the next record in the cursor
737
738 OPEN c_get_acad_cal_info(c_ref_cur_rec.person_id,l_new_admission_appl_number);
739 FETCH c_get_acad_cal_info INTO l_get_acad_cal_info;
740 CLOSE c_get_acad_cal_info;
741
742
743 v_start_dt := igs_en_gen_002.enrp_get_acad_comm(
744 l_get_acad_cal_info.acad_cal_type,
745 l_get_acad_cal_info.acad_ci_sequence_number,
746 c_ref_cur_rec.person_id,
747 c_appl_inst_rec.nominated_course_cd,
748 l_new_admission_appl_number,
749 c_appl_inst_rec.nominated_course_cd,
750 l_new_sequence_number,
751 'Y');
752
753
754 IF copy_child_records(
755 p_new_admission_appl_number => l_new_admission_appl_number,
756 p_new_sequence_number => l_new_sequence_number,
757 p_person_id => c_ref_cur_rec.person_id,
758 p_old_admission_appl_number => c_appl_inst_rec.admission_appl_number,
759 p_old_sequence_number => c_appl_inst_rec.sequence_number,
760 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
761 p_start_dt => v_start_dt,
762 p_process => 'F') THEN
763 -- Added p_process for Deferment changes 2395510
764 -- by rrengara on 12-sep-2002
765 -- reusablity of the same code by both defermetn and future term
766
767 -- if all the validations and copying is successful then update the entry and doc status from old applicaiton to new future term
768 IF copy_entrycomp_qual_status(p_person_id => c_ref_cur_rec.person_id,
769 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
770 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
771 p_sequence_number => c_appl_inst_rec.sequence_number,
772 p_new_admission_appl_number => l_new_admission_appl_number,
773 p_new_sequence_number => l_new_sequence_number ) THEN
774
775
776 -- do the offer validation and update the out NOCOPY come status to offer
777 IF validate_offer_validations(p_person_id => c_ref_cur_rec.person_id,
778 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
779 p_admission_appl_number => l_new_admission_appl_number,
780 p_sequence_number => l_new_sequence_number,
781 p_old_admission_appl_number => c_appl_inst_rec.admission_appl_number,
782 p_old_sequence_number => c_appl_inst_rec.sequence_number,
783 p_offer_dt => l_offer_dt,
784 p_offer_response_dt => l_offer_response_dt,
785 p_fut_acad_cal_type => p_fut_acad_cal_type,
786 p_fut_acad_cal_seq_no => p_fut_acad_cal_seq_no,
787 p_fut_adm_cal_type => p_fut_adm_cal_type,
788 p_fut_adm_cal_seq_no => p_fut_adm_cal_seq_no,
789 p_start_dt => v_start_dt
790 ) THEN
791
792
793 -- Proceed to the next record in the cursor
794 NULL;
795 ELSE
796 ROLLBACK TO sp_save_point1;
797 END IF;
798 ELSE
799 ROLLBACK TO sp_save_point1;
800 END IF;
801 ELSE -- Else if the child record copy failed
802 ROLLBACK TO sp_save_point1;
803 END IF; -- End if for Copy Child Records
804 ELSE -- Application Creation Failed
805 ROLLBACK TO sp_save_point1;
806 END IF; -- End if for handle application
807 END LOOP; -- end loop for loop application instances
808 END LOOP; -- End loop for Ref cursor
809 END IF;
810
811 ELSE
812 -- This case will come into picture if the user has given only Person Id
813
814 -- Find out NOCOPY the list applications
815 FOR c_appl_inst_rec IN c_appl_inst(p_person_id) LOOP
816
817 l_applcreated_flag := FALSE;
818
819 -- Set the save point here
820 -- Since when the application related transaction fails we need to rollback the transaction for that application
821 -- and it should proceed to the next record in the cursor
822
823 fnd_file.put_line(fnd_file.log, 'Creating Future Term Application for ' );
824 fnd_file.put_line(fnd_file.log,RPAD(' Person Id' , 29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.person_id));
825 fnd_file.put_line(fnd_file.log, RPAD(' Admission Application Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.admission_appl_number));
826 fnd_file.put_line(fnd_file.log,RPAD(' Nominated course Code',29) || ' : ' || c_appl_inst_rec.nominated_course_cd);
827 fnd_file.put_line(fnd_file.log,RPAD(' Sequence Number',29) || ' : ' || IGS_GE_NUMBER.TO_CANN( c_appl_inst_rec.sequence_number ));
828
829 SAVEPOINT sp_save_point2;
830
831 -- Call handle application
832 -- this procedure returns true if the application, application program and application instance insert is success
833 -- otherwise it returns FALSE. This procedure also inserts the new application and instances for the future term
834 -- if the new application is created it returns the new admission application number and new sequence number as an
835 -- out NOCOPY parameter. This variables will be used for copying the child related records from the old to the new application
836
837 IF handle_application(
838 p_person_id => p_person_id,
839 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
840 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
841 p_sequence_number => c_appl_inst_rec.sequence_number,
842 p_fut_acad_cal_type => c_appl_inst_rec.future_acad_cal_type ,
843 p_fut_acad_cal_seq_no => c_appl_inst_rec.future_acad_ci_sequence_number,
844 p_fut_adm_cal_type => c_appl_inst_rec.future_adm_cal_type,
845 p_fut_adm_cal_seq_no => c_appl_inst_rec.future_adm_ci_sequence_number,
846 p_new_admission_appl_number => l_new_admission_appl_number,
847 p_new_sequence_number => l_new_sequence_number
848 ) THEN
849
850 -- if the creation is successful then copy all the child records
851 -- if any error occurs during copying of any of the child record
852 -- rollback upto save point 2 and proceed the next record in the cursor
853
854 OPEN c_get_acad_cal_info(p_person_id,l_new_admission_appl_number);
855 FETCH c_get_acad_cal_info INTO l_get_acad_cal_info;
856 CLOSE c_get_acad_cal_info;
857
858
859 v_start_dt := igs_en_gen_002.enrp_get_acad_comm(
860 l_get_acad_cal_info.acad_cal_type,
861 l_get_acad_cal_info.acad_ci_sequence_number,
862 p_person_id,
863 c_appl_inst_rec.nominated_course_cd,
864 l_new_admission_appl_number,
865 c_appl_inst_rec.nominated_course_cd,
866 l_new_sequence_number,
867 'Y');
868
869
870 IF copy_child_records(p_new_admission_appl_number => l_new_admission_appl_number,
871 p_new_sequence_number => l_new_sequence_number,
872 p_person_id => c_appl_inst_rec.person_id,
873 p_old_admission_appl_number => c_appl_inst_rec.admission_appl_number,
874 p_old_sequence_number => c_appl_inst_rec.sequence_number,
875 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
876 p_start_dt => v_start_dt,
877 p_process => 'F') THEN
878 -- Added p_process for Deferment changes 2395510
879 -- by rrengara on 12-sep-2002
880 -- reusablity of the same code by both defermetn and future term
881
882
883
884 -- if all the validations and copying is successful then update the entry and doc status from old applicaiton to new future term
885 IF copy_entrycomp_qual_status(p_person_id => c_appl_inst_rec.person_id,
886 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
887 p_admission_appl_number => c_appl_inst_rec.admission_appl_number,
888 p_sequence_number => c_appl_inst_rec.sequence_number,
889 p_new_admission_appl_number => l_new_admission_appl_number,
890 p_new_sequence_number => l_new_sequence_number ) THEN
891
892
893 -- do the offer validation and update the out NOCOPY come status to offer
894 IF validate_offer_validations(p_person_id => c_appl_inst_rec.person_id,
895 p_nominated_course_cd => c_appl_inst_rec.nominated_course_cd,
896 p_admission_appl_number => l_new_admission_appl_number,
897 p_sequence_number => l_new_sequence_number,
898 p_old_admission_appl_number => c_appl_inst_rec.admission_appl_number,
899 p_old_sequence_number => c_appl_inst_rec.sequence_number,
900 p_offer_dt => l_offer_dt,
901 p_offer_response_dt => l_offer_response_dt,
902 p_fut_acad_cal_type => p_fut_acad_cal_type,
903 p_fut_acad_cal_seq_no => p_fut_acad_cal_seq_no,
904 p_fut_adm_cal_type => p_fut_adm_cal_type,
905 p_fut_adm_cal_seq_no => p_fut_adm_cal_seq_no,
906 p_start_dt => v_start_dt
907 ) THEN
908 -- Proceed to the next record in the cursor
909 NULL;
910 ELSE
911 ROLLBACK TO sp_save_point2;
912 END IF;
913 ELSE
914 ROLLBACK TO sp_save_point2;
915 END IF;
916 -- Do offer validations
917 ELSE -- if the copy child records failed
918 ROLLBACK TO sp_save_point2;
919 END IF; -- end if copy child records
920 ELSE -- if the create application failed
921 ROLLBACK TO sp_save_point2;
922 END IF; -- end if create application
923 END LOOP; -- end loop list of application instances
924 END IF;
925 IF l_applcreated_flag THEN
926 --Put the message into log that application could not be found for given parameters.
927 fnd_file.put_line(fnd_file.log, fnd_message.get_string('IGS','IGS_AD_FTAPP_NOT_FOUND'));
928 END IF;
929 EXCEPTION
930 WHEN OTHERS THEN
931 retcode:=2;
932 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
933 Igs_Ge_Msg_Stack.conc_exception_hndl;
934 RETURN;
935 END admp_val_offer_future_term;
936
937 FUNCTION handle_application(
938 p_person_id hz_parties.party_id%TYPE,
939 p_admission_appl_number igs_ad_appl.admission_appl_number%TYPE,
940 p_nominated_course_cd igs_ad_ps_appl.nominated_course_cd%TYPE,
941 p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE,
942 p_fut_acad_cal_type igs_ad_appl.acad_cal_type%TYPE,
943 p_fut_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE,
944 p_fut_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
945 p_fut_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE,
946 p_new_admission_appl_number OUT NOCOPY igs_ad_appl.admission_appl_number%TYPE,
947 p_new_sequence_number OUT NOCOPY igs_ad_ps_appl_inst.sequence_number%TYPE
948 ) RETURN BOOLEAN IS
949 /*******************************************************************************
950 Created by : Ramesh Rengarajan
951 Date created: 20 FEB 2002
952
953 Purpose:
954 To create future term application
955
956 Known limitations/enhancements and/or remarks:
957
958 Change History: (who, when, what: )
959 Who When What
960 rrengara 11-jul-2002 Added UK Parameters choice_number and routeb pref to insert_adm_appl procedure for bug 2448262 (D) and 2455053 (P)
961 Also changed the cursor c_appl_inst to add the above said parameters
962 rboddu 04-OCT-2002 Creating application with Application_Type. Bug :2599457
963 knag 21-NOV-2002 Added alt_appl_id param to call to insert_adm_appl for bug 2664410
964 pbondugu 28-Mar-2003 Passed funding_source to procedure call IGS_AD_GEN_014.insert_adm_appl_prog_inst
965 *******************************************************************************/
966 -- Cursor Declarations-------------------------------------------------------------------
967 -- Cursor to get the applications
968 CURSOR c_appl_inst IS
969 SELECT
970 acai.*,
971 aa.appl_dt, aa.admission_cat, aa.s_admission_process_type,
972 aa.spcl_grp_1, aa.spcl_grp_2,aa.common_app,
973 aa.adm_appl_status, aa.choice_number, aa.routeb_pref,
974 aa.application_type, aa.adm_fee_status, aa.alt_appl_id,
975 aa.acad_cal_type, aa.acad_ci_sequence_number,
976 aca.admission_cd, aca.transfer_course_cd,
977 aca.basis_for_admission_type,aca.req_for_reconsideration_ind,
978 aca.req_for_adv_standing_ind,
979 NVL(acai.adm_cal_type,aa.adm_cal_type) final_adm_cal_type,
980 NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) final_adm_ci_sequence_number
981 FROM
982 igs_ad_ps_appl_inst acai, /* Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst Bug 3150054 */
983 igs_ad_appl aa,
984 igs_ad_ps_appl aca
985 WHERE
986 acai.admission_appl_number = p_admission_appl_number
987 AND acai.sequence_number = p_sequence_number
988 AND acai.nominated_course_cd = p_nominated_course_cd
989 AND aa.person_id = acai.person_id
990 AND aa.admission_appl_number = acai.admission_appl_number
991 AND acai.person_id = p_person_id
992 AND aca.person_id = acai.person_id
993 AND aca.admission_appl_number = acai.admission_appl_number
994 AND aca.nominated_course_cd = acai.nominated_course_cd;
995
996 CURSOR c_sys_def_appl_type(cp_adm_cat igs_ad_appl_all.admission_cat%TYPE,
997 cp_s_adm_prc_typ igs_ad_appl_all.s_admission_process_type%TYPE
998 )
999 IS
1000 SELECT admission_application_type
1001 FROM igs_ad_ss_appl_typ
1002 WHERE admission_cat = cp_adm_cat
1003 AND S_admission_process_type = cp_s_adm_prc_typ
1004 AND System_default = 'Y'
1005 AND NVL(closed_ind, 'N') <> 'Y';
1006
1007
1008 -----End cursor Declarations------------------------------------------------------------------
1009
1010 -------------------Variable Declarations------------------------------------------------------
1011 l_message_name VARCHAR2(1000);
1012 l_adm_fee_status igs_ad_appl.adm_fee_status%TYPE;
1013 l_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE;
1014 l_return_type VARCHAR2(100);
1015 l_error_code VARCHAR2(100);
1016 l_adm_appl_status igs_ad_appl.adm_appl_status%TYPE;
1017 l_admission_appl_number igs_ad_appl.admission_appl_number%TYPE;
1018 l_application_type igs_ad_appl_all.application_type%TYPE;
1019 ------------------- End Variable Declarations------------------------------------------------------
1020 BEGIN
1021
1022 FOR c_appl_inst_rec IN c_appl_inst LOOP
1023
1024 l_application_type:= c_appl_inst_rec.application_type;
1025 --If Application Type of existing Future Term Application is NULL, then take the System Default Application Type
1026 -- And create the application with this application type. Bug: 2599457
1027 IF l_application_type IS NULL THEN
1028 OPEN c_sys_def_appl_type(c_appl_inst_rec.admission_cat,c_appl_inst_rec.s_admission_process_type);
1029 FETCH c_sys_def_appl_type INTO l_application_type;
1030 CLOSE c_sys_def_appl_type;
1031 END IF;
1032
1033 -- Create Admission application
1034 IF IGS_AD_GEN_014.insert_adm_appl(
1035 p_person_id => p_person_id,
1036 p_appl_dt => c_appl_inst_rec.appl_dt,
1037 p_acad_cal_type => p_fut_acad_cal_type ,
1038 p_acad_ci_sequence_number => p_fut_acad_cal_seq_no ,
1039 p_adm_cal_type => p_fut_adm_cal_type ,
1040 p_adm_ci_sequence_number => p_fut_adm_cal_seq_no ,
1041 p_admission_cat => c_appl_inst_rec.admission_cat,
1042 p_s_admission_process_type => c_appl_inst_rec.s_admission_process_type,
1043 p_adm_appl_status => c_appl_inst_rec.adm_appl_status,
1044 p_adm_fee_status => c_appl_inst_rec.adm_fee_status, --IN/OUT
1045 p_tac_appl_ind =>'N',
1046 p_adm_appl_number =>l_admission_appl_number, --OUT
1047 p_message_name =>l_message_name, --OUT
1048 p_spcl_grp_1 =>c_appl_inst_rec.spcl_grp_1,
1049 p_spcl_grp_2 =>c_appl_inst_rec.spcl_grp_2,
1050 p_common_app =>c_appl_inst_rec.common_app,
1051 p_application_type =>l_application_type,-- Added as part of 2599457
1052 p_choice_number =>c_appl_inst_rec.choice_number,
1053 p_routeb_pref =>c_appl_inst_rec.routeb_pref,
1054 p_alt_appl_id =>c_appl_inst_rec.alt_appl_id) = FALSE THEN
1055
1056 fnd_message.set_name('IGS', l_message_name);
1057 fnd_file.put_line(fnd_file.log, fnd_message.get);
1058
1059 RETURN FALSE;
1060
1061 ELSE -- Else for Application
1062 IF IGS_AD_GEN_014.insert_adm_appl_prog(
1063 p_person_id=> p_person_id,
1064 p_adm_appl_number=>l_admission_appl_number,
1065 p_nominated_course_cd=>p_nominated_course_cd,
1066 p_transfer_course_cd=>c_appl_inst_rec.transfer_course_cd,
1067 p_basis_for_admission_type=>c_appl_inst_rec.basis_for_admission_type,
1068 p_admission_cd=>c_appl_inst_rec.admission_cd,
1069 p_req_for_reconsideration_ind=> c_appl_inst_rec.req_for_reconsideration_ind,
1070 p_req_for_adv_standing_ind=> c_appl_inst_rec.req_for_adv_standing_ind,
1071 p_message_name => l_message_name) THEN
1072
1073 -- Create Admission Application Porgram Instance
1074
1075 IF IGS_AD_GEN_014.insert_adm_appl_prog_inst (
1076 p_person_id=>p_person_id,
1077 p_admission_appl_number=>l_admission_appl_number,
1078 p_acad_cal_type=>p_fut_acad_cal_type,
1079 p_acad_ci_sequence_number=>p_fut_acad_cal_seq_no ,
1080 p_adm_cal_type=>p_fut_adm_cal_type ,
1081 p_adm_ci_sequence_number=>p_fut_adm_cal_seq_no,
1082 p_admission_cat=>c_appl_inst_rec.admission_cat,
1083 p_s_admission_process_type=>c_appl_inst_rec.s_admission_process_type,
1084 p_appl_dt=>c_appl_inst_rec.appl_dt,
1085 /* p_adm_fee_status=>l_adm_fee_status, */
1086 p_adm_fee_status=>c_appl_inst_rec.adm_fee_status,
1087 p_preference_number=>c_appl_inst_rec.preference_number,
1088 p_offer_dt=>NULL,
1089 p_offer_response_dt=>NULL,
1090 p_course_cd=>c_appl_inst_rec.nominated_course_cd,
1091 p_crv_version_number=>c_appl_inst_rec.crv_version_number,
1092 p_location_cd=>c_appl_inst_rec.location_cd,
1093 p_attendance_mode=>c_appl_inst_rec.attendance_mode,
1094 p_attendance_type=>c_appl_inst_rec.attendance_type,
1095 p_unit_set_cd=>c_appl_inst_rec.unit_set_cd,
1096 p_us_version_number=>c_appl_inst_rec.us_version_number,
1097 p_fee_cat=>c_appl_inst_rec.fee_cat,
1098 p_correspondence_cat=>c_appl_inst_rec.correspondence_cat,
1099 p_enrolment_cat=>c_appl_inst_rec.enrolment_cat,
1100 p_funding_source=>c_appl_inst_rec.funding_source,
1101 p_edu_goal_prior_enroll=>c_appl_inst_rec.edu_goal_prior_enroll_id,
1102 p_app_source_id=>c_appl_inst_rec.app_source_id,
1103 p_apply_for_finaid=>c_appl_inst_rec.apply_for_finaid,
1104 p_finaid_apply_date=>c_appl_inst_rec.finaid_apply_date,
1105 p_attribute_category=>c_appl_inst_rec.attribute_category,
1106 p_attribute1=>c_appl_inst_rec.attribute1,
1107 p_attribute2=>c_appl_inst_rec.attribute2,
1108 p_attribute3=>c_appl_inst_rec.attribute3,
1109 p_attribute4=>c_appl_inst_rec.attribute4,
1110 p_attribute5=>c_appl_inst_rec.attribute5,
1111 p_attribute6=>c_appl_inst_rec.attribute6,
1112 p_attribute7=>c_appl_inst_rec.attribute7,
1113 p_attribute8=>c_appl_inst_rec.attribute8,
1114 p_attribute9=>c_appl_inst_rec.attribute9,
1115 p_attribute10=>c_appl_inst_rec.attribute10,
1116 p_attribute11=>c_appl_inst_rec.attribute11,
1117 p_attribute12=>c_appl_inst_rec.attribute12,
1118 p_attribute13=>c_appl_inst_rec.attribute13,
1119 p_attribute14=>c_appl_inst_rec.attribute14,
1120 p_attribute15=>c_appl_inst_rec.attribute15,
1121 p_attribute16=>c_appl_inst_rec.attribute16,
1122 p_attribute17=>c_appl_inst_rec.attribute17,
1123 p_attribute18=>c_appl_inst_rec.attribute18,
1124 p_attribute19=>c_appl_inst_rec.attribute19,
1125 p_attribute20=>c_appl_inst_rec.attribute20,
1126 p_attribute21=>c_appl_inst_rec.attribute21,
1127 p_attribute22=>c_appl_inst_rec.attribute22,
1128 p_attribute23=>c_appl_inst_rec.attribute23,
1129 p_attribute24=>c_appl_inst_rec.attribute24,
1130 p_attribute25=>c_appl_inst_rec.attribute25,
1131 p_attribute26=>c_appl_inst_rec.attribute26,
1132 p_attribute27=>c_appl_inst_rec.attribute27,
1133 p_attribute28=>c_appl_inst_rec.attribute28,
1134 p_attribute29=>c_appl_inst_rec.attribute29,
1135 p_attribute30=>c_appl_inst_rec.attribute30,
1136 p_attribute31=>c_appl_inst_rec.attribute31,
1137 p_attribute32=>c_appl_inst_rec.attribute32,
1138 p_attribute33=>c_appl_inst_rec.attribute33,
1139 p_attribute34=>c_appl_inst_rec.attribute34,
1140 p_attribute35=>c_appl_inst_rec.attribute35,
1141 p_attribute36=>c_appl_inst_rec.attribute36,
1142 p_attribute37=>c_appl_inst_rec.attribute37,
1143 p_attribute38=>c_appl_inst_rec.attribute38,
1144 p_attribute39=>c_appl_inst_rec.attribute39,
1145 p_attribute40=>c_appl_inst_rec.attribute40,
1146 p_ss_application_id =>NULL,
1147 p_sequence_number =>l_sequence_number,
1148 p_return_type =>l_return_type,
1149 p_error_code =>l_error_code,
1150 p_message_name =>l_message_name,
1151 p_entry_status =>c_appl_inst_rec.entry_status,
1152 p_entry_level =>c_appl_inst_rec.entry_level,
1153 p_sch_apl_to_id =>c_appl_inst_rec.sch_apl_to_id) THEN
1154 p_new_admission_appl_number := l_admission_appl_number;
1155 p_new_sequence_number := l_sequence_number;
1156 RETURN TRUE;
1157
1158 ELSE -- Else of Application Instance
1159
1160 IF l_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
1161 l_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
1162 fnd_message.set_name('IGS', l_message_name);
1163 fnd_message.set_token('PGM', c_appl_inst_rec.nominated_course_cd);
1164 fnd_message.set_token('ALTCODE',c_appl_inst_rec.acad_cal_type||','||IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.acad_ci_sequence_number)
1165 ||'/'||c_appl_inst_rec.final_adm_cal_type||','||IGS_GE_NUMBER.TO_CANN(c_appl_inst_rec.final_adm_ci_sequence_number));
1166 fnd_file.put_line(fnd_file.log, fnd_message.get);
1167 ELSE
1168 fnd_message.set_name('IGS', l_message_name);
1169 fnd_file.put_line(fnd_file.log, fnd_message.get);
1170 END IF;
1171 RETURN FALSE;
1172 END IF;
1173 RETURN FALSE;
1174 ELSE -- Else for Application Program
1175 fnd_message.set_name('IGS', l_message_name);
1176 fnd_file.put_line(fnd_file.log, fnd_message.get);
1177 RETURN FALSE;
1178 END IF;
1179 END IF;
1180 END LOOP;
1181 RETURN TRUE;
1182 EXCEPTION WHEN OTHERS THEN
1183 fnd_file.put_line(fnd_file.log, 'Exception From handle application log ' || SQLERRM);
1184 RETURN FALSE;
1185 END handle_application;
1186
1187 FUNCTION copy_child_records(p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
1188 p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
1189 p_person_id HZ_PARTIES.party_id%TYPE,
1190 p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
1191 p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
1192 p_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
1193 p_start_dt DATE,
1194 p_process VARCHAR2)
1195 RETURN BOOLEAN IS
1196 /*******************************************************************************
1197 Created by : Ramesh Rengarajan
1198 Date created: 20 FEB 2002
1199
1200 Purpose:
1201 To create future term application
1202
1203 Known limitations/enhancements and/or remarks:
1204
1205 Change History: (who, when, what: )
1206 Who When What
1207 pathipat 17-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
1208 Modified TBH call to igs_ad_app_req_pkg to include 3 new columns
1209 vvutukur 27-Nov-2002 Enh#2584986.Modified cursor c_fee_cur and also tbh call to igs_ad_app_req_pkg.insert_row
1210 to include 11 new columns related to credit card details,accounting information and
1211 gl date.
1212 rrengara 30-oct-2002 Removed Academic Honros copying Bug 2647482
1213 rrengara 13-sep-2002 Added p_process to the procedure. If p_process is 'D' then copy the fee details fee status as 'WAIVED'
1214 *******************************************************************************/
1215 -------------------------Cursor Declarations-------------------------------------------------------------------
1216 -- Unitsets
1217 CURSOR c_unitsets_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1218 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1219 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1220 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1221 IS
1222 SELECT
1223 unit_set_id,
1224 person_id ,
1225 admission_appl_number ,
1226 nominated_course_cd,
1227 sequence_number,
1228 unit_set_cd,
1229 version_number,
1230 rank
1231 FROM
1232 igs_ad_unit_sets
1233 WHERE
1234 person_id = cp_person_id and
1235 nominated_course_cd = cp_nominated_course_cd and
1236 sequence_number = cp_sequence_number and
1237 admission_appl_number = cp_admission_appl_number;
1238
1239 -- other institutitons
1240
1241 CURSOR c_othinst_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1242 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1243 IS
1244 SELECT
1245 other_inst_id,
1246 person_id ,
1247 admission_appl_number ,
1248 nominated_course_cd ,
1249 sequence_number ,
1250 institution_code,
1251 new_institution
1252 FROM
1253 igs_ad_other_inst
1254 WHERE
1255 person_id = cp_person_id and
1256 admission_appl_number = cp_admission_appl_number;
1257
1258
1259 -- education goals
1260 CURSOR c_edugoal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1261 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1262 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1263 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1264 IS
1265 SELECT
1266 post_edugoal_id ,
1267 person_id ,
1268 admission_appl_number ,
1269 nominated_course_cd,
1270 sequence_number ,
1271 edu_goal_id
1272 FROM
1273 igs_ad_edugoal
1274 WHERE
1275 person_id = cp_person_id and
1276 nominated_course_cd = cp_nominated_course_cd and
1277 sequence_number = cp_sequence_number and
1278 admission_appl_number = cp_admission_appl_number;
1279
1280 -- rrengara
1281 -- for Build Movement Academic Honors Bug 2647482
1282 -- on 28-oct-2002
1283 --
1284 -- academic honors has been moved to Person
1285 -- So cursor to select the values from the old applicaiton has been removed
1286
1287
1288 -- personal statements
1289 CURSOR c_perstat_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1290 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1291 IS
1292 SELECT
1293 appl_perstat_id,
1294 person_id ,
1295 admission_appl_number ,
1296 persl_stat_type ,
1297 date_received
1298 FROM
1299 igs_ad_appl_perstat
1300 WHERE
1301 person_id = cp_person_id and
1302 admission_appl_number = cp_admission_appl_number;
1303
1304
1305 -- academic interests
1306 CURSOR c_acadint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1307 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1308 IS
1309 SELECT
1310 acad_interest_id ,
1311 person_id ,
1312 admission_appl_number ,
1313 field_of_study
1314 FROM
1315 igs_ad_acad_interest
1316 WHERE
1317 person_id = cp_person_id and
1318 admission_appl_number = cp_admission_appl_number;
1319
1320 --applicant intent
1321 CURSOR c_appint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1322 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1323 IS
1324 SELECT
1325 app_intent_id,
1326 person_id ,
1327 admission_appl_number,
1328 intent_type_id ,
1329 attribute_category,
1330 attribute1 ,
1331 attribute2 ,
1332 attribute3 ,
1333 attribute4 ,
1334 attribute5 ,
1335 attribute6 ,
1336 attribute7 ,
1337 attribute8 ,
1338 attribute9 ,
1339 attribute10 ,
1340 attribute11 ,
1341 attribute12 ,
1342 attribute13 ,
1343 attribute14 ,
1344 attribute15 ,
1345 attribute16 ,
1346 attribute17 ,
1347 attribute18 ,
1348 attribute19 ,
1349 attribute20
1350 FROM
1351 igs_ad_app_intent
1352 WHERE
1353 person_id = cp_person_id and
1354 admission_appl_number = cp_admission_appl_number;
1355
1356
1357 -- Special Interests
1358 CURSOR c_splint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1359 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1360 IS
1361 SELECT
1362 spl_interest_id ,
1363 person_id ,
1364 admission_appl_number ,
1365 special_interest_type_id
1366 FROM
1367 igs_ad_spl_interests
1368 WHERE
1369 person_id = cp_person_id and
1370 admission_appl_number = cp_admission_appl_number;
1371
1372 --spl talents
1373 CURSOR c_spltal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1374 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
1375 IS
1376 SELECT
1377 spl_talent_id ,
1378 person_id ,
1379 admission_appl_number ,
1380 special_talent_type_id
1381 FROM
1382 igs_ad_spl_talents
1383 WHERE
1384 person_id = cp_person_id and
1385 admission_appl_number = cp_admission_appl_number;
1386
1387 -- special consideration
1388 CURSOR c_splcns_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1389 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1390 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1391 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1392 IS
1393 SELECT
1394 spl_adm_cat_id ,
1395 person_id ,
1396 admission_appl_number ,
1397 nominated_course_cd ,
1398 sequence_number ,
1399 spl_adm_cat
1400 FROM
1401 igs_ad_spl_adm_cat
1402 WHERE
1403 person_id = cp_person_id and
1404 nominated_course_cd = cp_nominated_course_cd and
1405 sequence_number = cp_sequence_number and
1406 admission_appl_number = cp_admission_appl_number;
1407
1408 -- Fees
1409 CURSOR c_fee_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1410 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE
1411 )IS
1412 SELECT
1413 app_req_id ,
1414 person_id ,
1415 admission_appl_number ,
1416 applicant_fee_type,
1417 applicant_fee_status,
1418 fee_date,
1419 fee_payment_method,
1420 fee_amount,
1421 reference_num,
1422 credit_card_code,
1423 credit_card_holder_name,
1424 credit_card_number,
1425 credit_card_expiration_date,
1426 rev_gl_ccid,
1427 cash_gl_ccid,
1428 rev_account_cd,
1429 cash_account_cd,
1430 gl_date,
1431 gl_posted_date,
1432 posting_control_id,
1433 credit_card_tangible_cd,
1434 credit_card_payee_cd,
1435 credit_card_status_code
1436 FROM
1437 igs_ad_app_req
1438 WHERE
1439 person_id = cp_person_id AND
1440 admission_appl_number = cp_admission_appl_number;
1441
1442 -- Trackings
1443 CURSOR c_tracking_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1444 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1445 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1446 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1447 IS
1448 SELECT
1449 aplins_admreq_id ,
1450 person_id ,
1451 admission_appl_number,
1452 course_cd ,
1453 sequence_number ,
1454 tracking_id
1455 FROM
1456 igs_ad_aplins_admreq
1457 WHERE
1458 person_id = cp_person_id and
1459 course_cd = cp_nominated_course_cd and
1460 sequence_number = cp_sequence_number and
1461 admission_appl_number = cp_admission_appl_number;
1462
1463 -- program approval
1464 CURSOR c_pgmapp_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1465 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1466 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1467 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1468 IS
1469 SELECT
1470 appl_pgmapprv_id ,
1471 person_id ,
1472 admission_appl_number,
1473 nominated_course_cd ,
1474 sequence_number ,
1475 pgm_approver_id ,
1476 assign_type ,
1477 assign_date ,
1478 program_approval_date,
1479 program_approval_status,
1480 approval_notes
1481 FROM
1482 igs_ad_appl_pgmapprv
1483 WHERE
1484 person_id = cp_person_id and
1485 nominated_course_cd = cp_nominated_course_cd and
1486 sequence_number = cp_sequence_number and
1487 admission_appl_number = cp_admission_appl_number;
1488
1489 -- Test Scores
1490 CURSOR c_test_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1491 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1492 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1493 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1494 IS
1495 SELECT
1496 tstscr_used_id ,
1497 comments ,
1498 person_id ,
1499 admission_appl_number,
1500 nominated_course_cd ,
1501 sequence_number ,
1502 attribute_category ,
1503 attribute1 ,
1504 attribute2 ,
1505 attribute3 ,
1506 attribute4 ,
1507 attribute5 ,
1508 attribute6 ,
1509 attribute7 ,
1510 attribute8 ,
1511 attribute9 ,
1512 attribute10 ,
1513 attribute11 ,
1514 attribute12 ,
1515 attribute13 ,
1516 attribute14 ,
1517 attribute15 ,
1518 attribute16 ,
1519 attribute17 ,
1520 attribute18 ,
1521 attribute19 ,
1522 attribute20
1523 FROM
1524 igs_ad_tstscr_used
1525 WHERE
1526 person_id = cp_person_id and
1527 nominated_course_cd = cp_nominated_course_cd and
1528 sequence_number = cp_sequence_number and
1529 admission_appl_number = cp_admission_appl_number;
1530
1531
1532 -- notes
1533 CURSOR c_notes_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1534 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1535 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1536 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1537 IS
1538 SELECT
1539 appl_notes_id ,
1540 person_id ,
1541 admission_appl_number ,
1542 nominated_course_cd ,
1543 sequence_number ,
1544 note_type_id ,
1545 ref_notes_id
1546 FROM
1547 igs_ad_appl_notes
1548 WHERE
1549 person_id = cp_person_id and
1550 nominated_course_cd = cp_nominated_course_cd and
1551 sequence_number = cp_sequence_number and
1552 admission_appl_number = cp_admission_appl_number;
1553
1554 CURSOR c_evaluators_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1555 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1556 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1557 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1558
1559 IS
1560 SELECT
1561 appl_eval_id,
1562 person_id ,
1563 admission_appl_number,
1564 nominated_course_cd ,
1565 sequence_number ,
1566 evaluator_id ,
1567 assign_type ,
1568 assign_date ,
1569 evaluation_date ,
1570 rating_type_id ,
1571 rating_values_id ,
1572 rating_notes ,
1573 evaluation_sequence ,
1574 rating_scale_id,
1575 closed_ind -- added the closed ind check -- rghosh (bug#2871426)
1576 FROM
1577 igs_ad_appl_eval
1578 WHERE
1579 person_id = cp_person_id and
1580 nominated_course_cd = cp_nominated_course_cd and
1581 sequence_number = cp_sequence_number and
1582 admission_appl_number = cp_admission_appl_number;
1583
1584
1585 CURSOR c_applrep_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1586 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1587 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1588 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
1589
1590 IS
1591 SELECT
1592 appl_arp_id ,
1593 person_id ,
1594 admission_appl_number ,
1595 nominated_course_cd ,
1596 sequence_number ,
1597 appl_rev_profile_id ,
1598 appl_revprof_revgr_id
1599 FROM
1600 igs_ad_appl_arp
1601 WHERE
1602 person_id = cp_person_id and
1603 nominated_course_cd = cp_nominated_course_cd and
1604 sequence_number = cp_sequence_number and
1605 admission_appl_number = cp_admission_appl_number;
1606
1607 -- bug 2395510 ( Deferment changes)
1608 -- by rrengara on 12-SEP-2002
1609
1610 CURSOR c_fee_status IS
1611 SELECT
1612 ccl.code_id
1613 FROM
1614 igs_ad_code_classes ccl , igs_lookup_values lkup
1615 WHERE
1616 ccl.system_status = lkup.lookup_code
1617 AND lkup.lookup_type = 'SYS_FEE_STATUS'
1618 AND ccl.system_status = 'WAIVED'
1619 AND ccl.system_default = 'Y'
1620 AND ccl.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
1621
1622
1623 CURSOR c_appl_qual_code(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1624 cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1625 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1626 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) IS
1627 SELECT QUALIFYING_TYPE_CODE,QUALIFYING_CODE_ID,QUALIFYING_VALUE ,PERSON_ID,NOMINATED_COURSE_CD
1628 FROM igs_ad_appqual_code
1629 WHERE person_id = cp_person_id
1630 AND nominated_course_cd = cp_nominated_course_cd
1631 AND sequence_number = cp_sequence_number
1632 AND admission_appl_number = cp_admission_appl_number;
1633
1634 ---End cursor Declarations-----------------------------------------------------------------------------
1635
1636 ---- Variable declarations-----------------------------------------------------------------------------
1637 l_primary_key NUMBER(15);
1638 l_rowid VARCHAR2(30);
1639 l_last_error VARCHAR2(100);
1640 l_waived_fee_status igs_ad_app_req.applicant_fee_status%TYPE;
1641
1642 ------End variable declarations------------------------------------------------------------------------
1643
1644 BEGIN
1645
1646 -- Unitsets
1647 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
1648 l_last_error := 'IGS_AD_CHILD_UNIT_SETS';
1649 Igs_Ad_Unit_Sets_PKG.INSERT_ROW(
1650 X_ROWID => l_rowid,
1651 X_UNIT_SET_ID => l_primary_key,
1652 X_PERSON_ID => c_unitsets_rec.person_id ,
1653 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1654 X_NOMINATED_COURSE_CD => c_unitsets_rec.nominated_course_cd,
1655 X_SEQUENCE_NUMBER =>p_new_sequence_number,
1656 X_UNIT_SET_CD => c_unitsets_rec.unit_set_cd,
1657 X_VERSION_NUMBER => c_unitsets_rec.version_number,
1658 X_RANK => c_unitsets_rec.rank,
1659 X_MODE => 'R'
1660 );
1661 END LOOP;
1662
1663 l_rowid := NULL;
1664
1665 --OTHER INSTITUTIONS
1666
1667 FOR c_othinst_rec IN c_othinst_cur(p_person_id, p_old_admission_appl_number) LOOP
1668 l_last_error := 'IGS_AD_CHILD_OTH_INST';
1669 IGS_AD_OTHER_INST_PKG.INSERT_ROW(
1670 X_ROWID => l_rowid,
1671 X_OTHER_INST_ID => l_primary_key,
1672 X_PERSON_ID => c_othinst_rec.person_id,
1673 X_ADMISSION_APPL_NUMBER =>p_new_admission_appl_number,
1674 X_INSTITUTION_CODE => c_othinst_rec.institution_code,
1675 X_MODE => 'R',
1676 X_NEW_INSTITUTION => c_othinst_rec.new_institution );
1677 END LOOP;
1678
1679 l_rowid := NULL;
1680
1681 -- edu goals
1682 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
1683 l_last_error := 'IGS_AD_CHILD_EDU_GOAL';
1684 Igs_Ad_Edugoal_pkg.INSERT_ROW(
1685 X_ROWID => l_rowid,
1686 X_POST_EDUGOAL_ID => l_primary_key,
1687 X_PERSON_ID => c_edugoal_rec.person_id,
1688 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1689 X_NOMINATED_COURSE_CD => c_edugoal_rec.nominated_course_cd,
1690 X_SEQUENCE_NUMBER => p_new_sequence_number,
1691 X_EDU_GOAL_ID => c_edugoal_rec.edu_goal_id,
1692 X_MODE => 'R');
1693 END LOOP;
1694
1695
1696 -- rrengara
1697 -- for Build Movement Academic Honors
1698 -- on 28-oct-2002
1699 --
1700 -- academic honors has been moved to Person
1701 -- Removed the TBH call which will insert the values for the new application from here
1702
1703
1704
1705 l_rowid := NULL;
1706 -- Personal Statements
1707 FOR c_perstat_rec IN c_perstat_cur(p_person_id, p_old_admission_appl_number) LOOP
1708 l_last_error := 'IGS_AD_CHILD_PER_STAT';
1709 igs_ad_appl_perstat_pkg.insert_row(
1710 x_rowid => l_rowid,
1711 x_appl_perstat_id => c_perstat_rec.appl_perstat_id,
1712 x_person_id => c_perstat_rec.person_id,
1713 x_admission_appl_number => p_new_admission_appl_number,
1714 x_persl_stat_type => c_perstat_rec.persl_stat_type,
1715 x_date_received => c_perstat_rec.date_received,
1716 x_mode => 'R');
1717 END LOOP;
1718
1719
1720 l_rowid := NULL;
1721
1722 -- ACADMEIC INTERESTS
1723 FOR c_acadint_rec IN c_acadint_cur(p_person_id, p_old_admission_appl_number) LOOP
1724 l_last_error := 'IGS_AD_CHILD_ACAD_INT';
1725 igs_ad_acad_interest_pkg.INSERT_ROW(
1726 X_ROWID => l_rowid,
1727 X_ACAD_INTEREST_ID => l_primary_key,
1728 X_PERSON_ID => c_acadint_rec.person_id,
1729 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1730 X_FIELD_OF_STUDY => c_acadint_rec.field_of_study,
1731 X_MODE => 'R');
1732 END LOOP;
1733
1734
1735 l_rowid := NULL;
1736
1737
1738 --applicant intent
1739 FOR c_appint_rec IN c_appint_cur(p_person_id, p_old_admission_appl_number) LOOP
1740 l_last_error := 'IGS_AD_CHILD_APPL_INTENT';
1741 igs_ad_app_intent_pkg.INSERT_ROW(
1742 X_ROWID => l_rowid,
1743 X_APP_INTENT_ID => l_primary_key,
1744 X_PERSON_ID => c_appint_rec.person_id,
1745 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1746 X_INTENT_TYPE_ID => c_appint_rec.intent_type_id,
1747 X_ATTRIBUTE_CATEGORY => c_appint_rec.attribute_category,
1748 X_ATTRIBUTE1 => c_appint_rec.attribute1,
1749 X_ATTRIBUTE2 => c_appint_rec.attribute2,
1750 X_ATTRIBUTE3 => c_appint_rec.attribute3,
1751 X_ATTRIBUTE4 => c_appint_rec.attribute4 ,
1752 X_ATTRIBUTE5 => c_appint_rec.attribute5,
1753 X_ATTRIBUTE6 => c_appint_rec.attribute6,
1754 X_ATTRIBUTE7 => c_appint_rec.attribute7 ,
1755 X_ATTRIBUTE8 => c_appint_rec.attribute8 ,
1756 X_ATTRIBUTE9 => c_appint_rec.attribute9 ,
1757 X_ATTRIBUTE10 => c_appint_rec.attribute10 ,
1758 X_ATTRIBUTE11 => c_appint_rec.attribute11 ,
1759 X_ATTRIBUTE12 => c_appint_rec.attribute12 ,
1760 X_ATTRIBUTE13 => c_appint_rec.attribute13 ,
1761 X_ATTRIBUTE14 => c_appint_rec.attribute14 ,
1762 X_ATTRIBUTE15 => c_appint_rec.attribute15 ,
1763 X_ATTRIBUTE16 => c_appint_rec.attribute16 ,
1764 X_ATTRIBUTE17 => c_appint_rec.attribute17 ,
1765 X_ATTRIBUTE18 => c_appint_rec.attribute18 ,
1766 X_ATTRIBUTE19 => c_appint_rec.attribute19 ,
1767 X_ATTRIBUTE20 => c_appint_rec.attribute20 ,
1768 X_MODE => 'R');
1769 END LOOP;
1770
1771 l_rowid := NULL;
1772
1773
1774 -- Special Interests
1775 FOR c_splint_rec IN c_splint_cur(p_person_id, p_old_admission_appl_number) LOOP
1776 l_last_error := 'IGS_AD_CHILD_SPL_INT';
1777 igs_ad_spl_interests_PKG.INSERT_ROW(
1778 X_ROWID => l_rowid,
1779 X_SPL_INTEREST_ID => l_primary_key,
1780 X_PERSON_ID => c_splint_rec.person_id,
1781 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1782 X_SPECIAL_INTEREST_TYPE_ID => c_splint_rec.special_interest_type_id,
1783 X_MODE => 'R');
1784 END LOOP;
1785
1786 l_rowid := NULL;
1787
1788 -- Special Talents
1789 FOR c_spltal_rec IN c_spltal_cur(p_person_id, p_old_admission_appl_number) LOOP
1790 l_last_error := 'IGS_AD_CHILD_SPL_TAL';
1791 igs_ad_spl_talents_PKG.INSERT_ROW(
1792 X_ROWID => l_rowid,
1793 X_SPL_TALENT_ID => l_primary_key,
1794 X_PERSON_ID => c_spltal_rec.person_id,
1795 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1796 X_SPECIAL_TALENT_TYPE_ID => c_spltal_rec.special_talent_type_id,
1797 X_MODE => 'R');
1798 END LOOP;
1799
1800 l_rowid := NULL;
1801
1802
1803 -- Special Consideration
1804
1805 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
1806 l_last_error := 'IGS_AD_CHILD_SPL_CON';
1807 igs_ad_spl_adm_cat_PKG.INSERT_ROW(
1808 X_ROWID => l_rowid,
1809 X_SPL_ADM_CAT_ID => l_primary_key,
1810 X_PERSON_ID => c_splcns_rec.person_id,
1811 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1812 X_NOMINATED_COURSE_CD => c_splcns_rec.nominated_course_cd,
1813 X_SEQUENCE_NUMBER => c_splcns_rec.sequence_number,
1814 X_SPL_ADM_CAT => c_splcns_rec.spl_adm_cat,
1815 X_MODE => 'R');
1816 END LOOP;
1817
1818 l_rowid := NULL;
1819
1820 -- Fess ( Requirements )
1821
1822
1823 FOR c_fee_rec IN c_fee_cur(p_person_id, p_old_admission_appl_number) LOOP
1824 l_last_error := 'IGS_AD_CHILD_FEE_DET';
1825
1826 -- Bug 2395510
1827 -- If the process is deferred then set the fee status to 'WAIVED' for all the records
1828 IF p_process = 'D' THEN
1829 OPEN c_fee_status;
1830 FETCH c_fee_status INTO l_waived_fee_status;
1831 IF c_fee_status%NOTFOUND THEN
1832 CLOSE c_fee_status;
1833 RAISE NO_DATA_FOUND;
1834 END IF;
1835 CLOSE c_fee_status;
1836 ELSE
1837 l_waived_fee_status := c_fee_rec.applicant_fee_status;
1838 END IF;
1839
1840 igs_ad_app_req_pkg.insert_row(
1841 x_rowid => l_rowid,
1842 x_app_req_id => l_primary_key,
1843 x_person_id => c_fee_rec.person_id,
1844 x_admission_appl_number => p_new_admission_appl_number,
1845 x_applicant_fee_type => c_fee_rec.applicant_fee_type,
1846 x_applicant_fee_status => l_waived_fee_status,
1847 x_fee_date => c_fee_rec.fee_date,
1848 x_fee_payment_method => c_fee_rec.fee_payment_method,
1849 x_fee_amount => c_fee_rec.fee_amount,
1850 x_reference_num => c_fee_rec.reference_num,
1851 x_mode => 'R',
1852 x_credit_card_code => c_fee_rec.credit_card_code,
1853 x_credit_card_holder_name => c_fee_rec.credit_card_holder_name,
1854 x_credit_card_number => c_fee_rec.credit_card_number,
1855 x_credit_card_expiration_date => c_fee_rec.credit_card_expiration_date,
1856 x_rev_gl_ccid => c_fee_rec.rev_gl_ccid,
1857 x_cash_gl_ccid => c_fee_rec.cash_gl_ccid,
1858 x_rev_account_cd => c_fee_rec.rev_account_cd,
1859 x_cash_account_cd => c_fee_rec.cash_account_cd,
1860 x_gl_date => c_fee_rec.gl_date,
1861 x_gl_posted_date => c_fee_rec.gl_posted_date,
1862 x_posting_control_id => c_fee_rec.posting_control_id,
1863 x_credit_card_tangible_cd => c_fee_rec.credit_card_tangible_cd,
1864 x_credit_card_payee_cd => c_fee_rec.credit_card_payee_cd,
1865 x_credit_card_status_code => c_fee_rec.credit_card_status_code
1866 );
1867 END LOOP;
1868
1869 l_rowid := NULL;
1870 -- Trackings
1871
1872
1873 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
1874 l_last_error := 'IGS_AD_CHILD_TRACK';
1875 IGS_AD_APLINS_ADMREQ_pkg.INSERT_ROW(
1876 X_ROWID => l_rowid,
1877 X_APLINS_ADMREQ_ID => l_primary_key,
1878 X_PERSON_ID => c_tracking_rec.person_id,
1879 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1880 X_COURSE_CD => c_tracking_rec.course_cd,
1881 X_SEQUENCE_NUMBER => p_new_sequence_number ,
1882 X_TRACKING_ID => c_tracking_rec.tracking_id,
1883 X_MODE => 'R');
1884 END LOOP;
1885
1886 l_rowid := NULL;
1887 -- program approval
1888
1889 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
1890 l_last_error := 'IGS_AD_CHILD_PRG_APPR';
1891 IGS_AD_APPL_PGMAPPRV_pkg.INSERT_ROW(
1892 X_ROWID => l_rowid,
1893 X_APPL_PGMAPPRV_ID => l_primary_key,
1894 X_PERSON_ID => c_pgmapp_rec.person_id,
1895 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1896 X_NOMINATED_COURSE_CD => c_pgmapp_rec.nominated_course_cd,
1897 X_SEQUENCE_NUMBER => p_new_sequence_number,
1898 X_PGM_APPROVER_ID => c_pgmapp_rec.pgm_approver_id,
1899 X_ASSIGN_TYPE => c_pgmapp_rec.assign_type,
1900 X_ASSIGN_DATE => c_pgmapp_rec.assign_date,
1901 X_PROGRAM_APPROVAL_DATE => c_pgmapp_rec.program_approval_date,
1902 X_PROGRAM_APPROVAL_STATUS => c_pgmapp_rec.program_approval_status,
1903 X_APPROVAL_NOTES => c_pgmapp_rec.approval_notes,
1904 X_MODE => 'R');
1905 END LOOP;
1906
1907 l_rowid := NULL;
1908 -- testscores
1909
1910
1911 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
1912 l_last_error := 'IGS_AD_CHILD_TST_SCORE';
1913 IGS_AD_TSTSCR_USED_pkg.INSERT_ROW(
1914 X_ROWID => l_rowid,
1915 X_TSTSCR_USED_ID => l_primary_key,
1916 X_COMMENTS => c_test_rec.comments,
1917 X_PERSON_ID => c_test_rec.person_id,
1918 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1919 X_NOMINATED_COURSE_CD => c_test_rec.nominated_course_cd,
1920 X_SEQUENCE_NUMBER => p_new_sequence_number,
1921 X_ATTRIBUTE_CATEGORY => c_test_rec.attribute_category,
1922 X_ATTRIBUTE1 => c_test_rec.attribute1,
1923 X_ATTRIBUTE2 => c_test_rec.attribute2,
1924 X_ATTRIBUTE3 => c_test_rec.attribute3,
1925 X_ATTRIBUTE4 => c_test_rec.attribute4 ,
1926 X_ATTRIBUTE5 => c_test_rec.attribute5,
1927 X_ATTRIBUTE6 => c_test_rec.attribute6,
1928 X_ATTRIBUTE7 => c_test_rec.attribute7 ,
1929 X_ATTRIBUTE8 => c_test_rec.attribute8 ,
1930 X_ATTRIBUTE9 => c_test_rec.attribute9 ,
1931 X_ATTRIBUTE10 => c_test_rec.attribute10 ,
1932 X_ATTRIBUTE11 => c_test_rec.attribute11 ,
1933 X_ATTRIBUTE12 => c_test_rec.attribute12 ,
1934 X_ATTRIBUTE13 => c_test_rec.attribute13 ,
1935 X_ATTRIBUTE14 => c_test_rec.attribute14 ,
1936 X_ATTRIBUTE15 => c_test_rec.attribute15 ,
1937 X_ATTRIBUTE16 => c_test_rec.attribute16 ,
1938 X_ATTRIBUTE17 => c_test_rec.attribute17 ,
1939 X_ATTRIBUTE18 => c_test_rec.attribute18 ,
1940 X_ATTRIBUTE19 => c_test_rec.attribute19 ,
1941 X_ATTRIBUTE20 => c_test_rec.attribute20 ,
1942 X_MODE => 'R');
1943 END LOOP;
1944
1945 l_rowid := NULL;
1946
1947 -- NOTES
1948 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
1949 l_last_error := 'IGS_AD_CHILD_APPL_NOTES';
1950 igs_ad_appl_notes_pkg.INSERT_ROW(
1951 X_ROWID => l_rowid,
1952 X_APPL_NOTES_ID => l_primary_key,
1953 X_PERSON_ID => c_notes_rec.person_id,
1954 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1955 X_NOMINATED_COURSE_CD => c_notes_rec.nominated_course_cd,
1956 X_SEQUENCE_NUMBER => p_new_sequence_number,
1957 X_NOTE_TYPE_ID => c_notes_rec.note_type_id,
1958 X_REF_NOTES_ID => c_notes_rec.ref_notes_id,
1959 X_MODE => 'R'
1960 );
1961 END LOOP;
1962
1963 l_rowid := NULL;
1964
1965 -- Evaluators
1966 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
1967 l_last_error := 'IGS_AD_CHILD_EVAL';
1968 igs_ad_appl_eval_pkg.INSERT_ROW(
1969 x_rowid => l_rowid,
1970 x_person_id => c_evaluators_rec.person_id,
1971 x_admission_appl_number => p_new_admission_appl_number,
1972 x_nominated_course_cd => c_evaluators_rec.nominated_course_cd,
1973 x_sequence_number => p_new_sequence_number,
1974 x_appl_eval_id => l_primary_key,
1975 x_evaluator_id => c_evaluators_rec.evaluator_id,
1976 x_assign_type => c_evaluators_rec.assign_type,
1977 x_assign_date => c_evaluators_rec.assign_date,
1978 x_evaluation_date => c_evaluators_rec.evaluation_date,
1979 x_rating_type_id => c_evaluators_rec.rating_type_id,
1980 x_rating_values_id => c_evaluators_rec.rating_values_id,
1981 x_rating_notes => c_evaluators_rec.rating_notes,
1982 x_evaluation_sequence => c_evaluators_rec.evaluation_sequence,
1983 x_rating_scale_id => c_evaluators_rec.rating_scale_id,
1984 x_mode => 'R',
1985 x_closed_ind => c_evaluators_rec.closed_ind -- added the parameter closed ind -- rghosh(bug#2871426)
1986 );
1987 END LOOP;
1988
1989 l_rowid := NULL;
1990
1991 -- Evaluators Review group
1992 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
1993 l_last_error := 'IGS_AD_CHILD_EVAL_RGRP';
1994 igs_ad_appl_arp_pkg.INSERT_ROW(
1995 X_ROWID => l_rowid,
1996 X_PERSON_ID => c_applrep_rec.person_id,
1997 X_ADMISSION_APPL_NUMBER => p_new_admission_appl_number,
1998 X_NOMINATED_COURSE_CD => c_applrep_rec.nominated_course_cd,
1999 X_SEQUENCE_NUMBER => p_new_sequence_number,
2000 X_APPL_ARP_ID => c_applrep_rec.appl_arp_id,
2001 X_APPL_REV_PROFILE_ID => c_applrep_rec.APPL_REV_PROFILE_ID,
2002 X_APPL_REVPROF_REVGR_ID => c_applrep_rec.APPL_REVPROF_REVGR_ID,
2003 X_MODE => 'R'
2004 );
2005 END LOOP;
2006
2007 --Qualification Codes
2008 l_rowid := NULL;
2009
2010 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
2011 l_last_error := 'IGS_AD_CHILD_QUAL_CODE';
2012 igs_ad_appqual_code_pkg.INSERT_ROW(
2013 x_rowid => l_rowid,
2014 x_person_id => c_appl_qual_rec.person_id,
2015 x_admission_appl_number => p_new_admission_appl_number,
2016 x_nominated_course_cd => c_appl_qual_rec.nominated_course_cd,
2017 x_sequence_number => p_new_sequence_number,
2018 x_qualifying_type_code => c_appl_qual_rec.qualifying_type_code,
2019 x_qualifying_code_id => c_appl_qual_rec.qualifying_code_id,
2020 x_qualifying_value => c_appl_qual_rec.qualifying_value,
2021 x_mode => 'R');
2022
2023 END LOOP;
2024
2025 l_rowid := NULL;
2026
2027 IF copy_candidacy_records ( p_new_admission_appl_number => p_new_admission_appl_number,
2028 p_new_sequence_number => p_new_sequence_number,
2029 p_person_id => p_person_id,
2030 p_old_admission_appl_number => p_old_admission_appl_number,
2031 p_old_sequence_number => p_old_sequence_number,
2032 p_nominated_course_cd => p_nominated_course_cd,
2033 p_start_dt => p_start_dt) = FALSE THEN
2034 RETURN FALSE;
2035 END IF;
2036
2037 RETURN TRUE;
2038
2039 EXCEPTION WHEN OTHERS THEN
2040 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CHILD_COPY_FAILED');
2041 Fnd_File.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2042
2043 FND_MESSAGE.SET_NAME('IGS',l_last_error);
2044 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET || SQLERRM);
2045
2046 RETURN FALSE;
2047 END copy_child_records;
2048
2049 FUNCTION validate_offer_validations(p_person_id HZ_PARTIES.PARTY_ID%TYPE,
2050 p_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
2051 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2052 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2053 p_old_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2054 p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2055 p_offer_dt igs_ad_ps_appl_inst.offer_dt%TYPE,
2056 p_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE,
2057 p_fut_acad_cal_type igs_ad_appl.acad_cal_type%TYPE,
2058 p_fut_acad_cal_seq_no igs_ad_appl.acad_ci_sequence_number%TYPE,
2059 p_fut_adm_cal_type igs_ad_appl.adm_cal_type%TYPE,
2060 p_fut_adm_cal_seq_no igs_ad_appl.adm_ci_sequence_number%TYPE,
2061 p_start_dt DATE
2062 ) RETURN BOOLEAN IS
2063 /*******************************************************************************
2064 Created by : Ramesh Rengarajan
2065 Date created: 20 FEB 2002
2066
2067 Purpose:
2068 To create future term application
2069
2070 Known limitations/enhancements and/or remarks:
2071
2072 Change History: (who, when, what: )
2073 Who When What
2074 rrengara 30-oct-2002 Build 2647482 Core Vs Optional
2075 *******************************************************************************/
2076 -- Cursor Declarations ----------------------------------------------------------------------------
2077
2078 CURSOR c_appl_inst_old_cur IS
2079 SELECT
2080 apai.*
2081 FROM
2082 igs_ad_ps_appl_inst apai
2083 WHERE
2084 apai.person_id = p_person_id
2085 AND apai.admission_appl_number = p_old_admission_appl_number
2086 AND apai.nominated_course_cd = p_nominated_course_cd
2087 AND apai.sequence_number = p_old_sequence_number;
2088
2089 c_appl_inst_old_rec c_appl_inst_old_cur%ROWTYPE;
2090
2091
2092 CURSOR c_appl_offer_cur IS
2093 SELECT
2094 acai.*, aa.acad_cal_type, aa.acad_ci_sequence_number, aa.adm_fee_status, aa.alt_appl_id,
2095 NVL(acai.adm_cal_type,aa.adm_cal_type) final_adm_cal_type,
2096 NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) final_adm_ci_sequence_number
2097 FROM
2098 igs_ad_appl aa,
2099 igs_ad_ps_appl_inst acai /* Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst Bug 3150054 */
2100 WHERE
2101 acai.person_id = p_person_id AND
2102 acai.nominated_course_cd = p_nominated_course_cd AND
2103 acai.sequence_number = p_sequence_number AND
2104 acai.admission_appl_number = p_admission_appl_number AND
2105 acai.person_id = aa.person_id AND
2106 acai.admission_appl_number = aa.admission_appl_number;
2107
2108 CURSOR c_admcat_cur IS
2109 SELECT
2110 admission_cat, s_admission_process_type, appl_dt
2111 FROM
2112 igs_ad_appl
2113 WHERE
2114 person_id = p_person_id AND
2115 admission_appl_number = p_admission_appl_number;
2116
2117
2118 c_admcat_rec c_admcat_cur%ROWTYPE;
2119
2120 CURSOR c_apcs_cur (
2121 cp_admission_cat igs_ad_prcs_cat_step.admission_cat%TYPE,
2122 cp_s_admission_process_type igs_ad_prcs_cat_step.s_admission_process_type%TYPE
2123 )
2124 IS
2125 SELECT
2126 s_admission_step_type, step_type_restriction_num
2127 FROM
2128 igs_ad_prcs_cat_step
2129 WHERE
2130 admission_cat = cp_admission_cat
2131 AND s_admission_process_type = cp_s_admission_process_type
2132 AND step_group_type <> 'TRACK';
2133
2134 CURSOR c_upd_acai_cur IS
2135 SELECT
2136 ROWID, APAI.*
2137 FROM
2138 igs_ad_ps_appl_inst apai
2139 WHERE
2140 apai.person_id = p_person_id
2141 AND apai.admission_appl_number = p_admission_appl_number
2142 AND apai.nominated_course_cd = p_nominated_course_cd
2143 AND apai.sequence_number = p_sequence_number;
2144 -- End Cursor Declarations ----------------------------------------------------------------------------
2145
2146 -- Variable Declarations -------------------------------------------------------------------------------
2147
2148 l_offer_response_dt igs_ad_ps_appl_inst.offer_response_dt%TYPE;
2149 v_pref_allowed_ind VARCHAR2 (1);
2150 v_pref_limit NUMBER;
2151 v_cond_offer_doc_allowed_ind VARCHAR2 (1);
2152 v_cond_offer_fee_allowed_ind VARCHAR2 (1);
2153 v_cond_offer_ass_allowed_ind VARCHAR2 (1);
2154 v_late_appl_allowed_ind VARCHAR2 (1);
2155 v_late_fees_required_ind VARCHAR2 (1);
2156 v_fees_required_ind VARCHAR2 (1);
2157 v_override_outcome_allowed_ind VARCHAR2 (1);
2158 v_set_outcome_allowed_ind VARCHAR2 (1);
2159 v_mult_offer_allowed_ind VARCHAR2 (1);
2160 v_multi_offer_limit NUMBER;
2161 v_unit_set_appl_ind VARCHAR2 (1);
2162 v_check_person_encumb VARCHAR2 (1);
2163 v_check_course_encumb VARCHAR2 (1);
2164 v_deferral_allowed_ind VARCHAR2 (1);
2165 v_pre_enrol_ind VARCHAR2 (1);
2166 v_warn_level VARCHAR2(10);
2167 v_message_name VARCHAR2(100);
2168 l_adm_cat IGS_AD_APPL.admission_cat%TYPE;
2169 l_s_adm_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
2170 l_appl_dt IGS_AD_APPL.appl_dt%TYPE;
2171 -- End Variable Declarations -------------------------------------------------------------------------------
2172
2173 BEGIN
2174
2175 v_pref_allowed_ind := 'N';
2176 v_cond_offer_doc_allowed_ind := 'N';
2177 v_cond_offer_fee_allowed_ind := 'N';
2178 v_cond_offer_ass_allowed_ind := 'N';
2179 v_late_appl_allowed_ind := 'N';
2180 v_late_fees_required_ind := 'N';
2181 v_fees_required_ind := 'N';
2182 v_override_outcome_allowed_ind := 'N';
2183 v_set_outcome_allowed_ind := 'N';
2184 v_mult_offer_allowed_ind := 'N';
2185 v_unit_set_appl_ind := 'N';
2186 v_check_person_encumb := 'N';
2187 v_check_course_encumb := 'N';
2188 v_deferral_allowed_ind := 'N';
2189 v_pre_enrol_ind := 'N';
2190
2191 FOR c_appl_offer_rec IN c_appl_offer_cur LOOP
2192
2193 OPEN c_admcat_cur;
2194 FETCH c_admcat_cur INTO l_adm_cat, l_s_adm_process_type, l_appl_dt;
2195 CLOSE c_admcat_cur;
2196
2197 -- This cursor has to be opened to copy the decision related values to the new application
2198
2199 OPEN c_appl_inst_old_cur;
2200 FETCH c_appl_inst_old_cur INTO c_appl_inst_old_rec;
2201 CLOSE c_appl_inst_old_cur;
2202
2203 --
2204 -- Determine the admission process category steps.
2205 --
2206 FOR l_c_apcs_rec IN c_apcs_cur (
2207 l_adm_cat,
2208 l_s_adm_process_type) LOOP
2209 IF l_c_apcs_rec.s_admission_step_type = 'CHKCENCUMB' THEN
2210 v_check_course_encumb := 'Y';
2211
2212 ELSIF l_c_apcs_rec.s_admission_step_type = 'CHKPENCUMB' THEN
2213 v_check_person_encumb := 'Y';
2214
2215 ELSIF l_c_apcs_rec.s_admission_step_type = 'PREF-LIMIT' THEN
2216 v_pref_allowed_ind := 'Y';
2217 v_pref_limit := l_c_apcs_rec.step_type_restriction_num;
2218
2219 ELSIF l_c_apcs_rec.s_admission_step_type = 'DOC-COND' THEN
2220 v_cond_offer_doc_allowed_ind := 'Y';
2221
2222 ELSIF l_c_apcs_rec.s_admission_step_type = 'FEE-COND' THEN
2223 v_cond_offer_fee_allowed_ind := 'Y';
2224
2225 ELSIF l_c_apcs_rec.s_admission_step_type = 'ASSES-COND' THEN
2226 v_cond_offer_ass_allowed_ind := 'Y';
2227
2228 ELSIF l_c_apcs_rec.s_admission_step_type = 'LATE-APP' THEN
2229 v_late_appl_allowed_ind := 'Y';
2230
2231 ELSIF l_c_apcs_rec.s_admission_step_type = 'LATE-FEE' THEN
2232 v_late_fees_required_ind := 'Y';
2233
2234 ELSIF l_c_apcs_rec.s_admission_step_type = 'APP-FEE' THEN
2235 v_fees_required_ind := 'Y';
2236
2237 ELSIF l_c_apcs_rec.s_admission_step_type = 'OVERRIDE-O' THEN
2238 v_override_outcome_allowed_ind := 'Y';
2239
2240 ELSIF l_c_apcs_rec.s_admission_step_type = 'SET-OTCOME' THEN
2241 v_set_outcome_allowed_ind := 'Y';
2242
2243 ELSIF l_c_apcs_rec.s_admission_step_type = 'MULTI-OFF' THEN
2244 v_mult_offer_allowed_ind := 'Y';
2245 v_multi_offer_limit := l_c_apcs_rec.step_type_restriction_num;
2246
2247 ELSIF l_c_apcs_rec.s_admission_step_type = 'UNIT-SET' THEN
2248 v_unit_set_appl_ind := 'Y';
2249
2250 ELSIF l_c_apcs_rec.s_admission_step_type = 'DEFER' THEN
2251 v_deferral_allowed_ind := 'Y';
2252
2253 ELSIF l_c_apcs_rec.s_admission_step_type = 'PRE-ENROL' THEN
2254 v_pre_enrol_ind := 'Y';
2255 END IF;
2256 END LOOP;
2257
2258 IF IGS_AD_VAL_ACAI.admp_val_offer_dt (
2259 p_offer_dt,
2260 igs_ad_gen_009.admp_get_sys_aos('OFFER'),
2261 c_appl_offer_rec.adm_cal_type,
2262 c_appl_offer_rec.adm_ci_sequence_number,
2263 v_message_name) = FALSE THEN
2264 fnd_message.set_name('IGS', v_message_name);
2265 fnd_file.put_line(fnd_file.log, fnd_message.get);
2266 END IF;
2267
2268
2269 -- calculate the offer response date if the user doesn't passes the value
2270 IF p_offer_response_dt IS NULL THEN
2271
2272 -- Calculate the Offer response date by calling the following procedure
2273 l_offer_response_dt := IGS_AD_GEN_007.ADMP_GET_RESP_DT (
2274 c_appl_offer_rec.course_cd,
2275 c_appl_offer_rec.crv_version_number,
2276 c_appl_offer_rec.acad_cal_type,
2277 c_appl_offer_rec.location_cd,
2278 c_appl_offer_rec.attendance_mode,
2279 c_appl_offer_rec.attendance_type,
2280 l_adm_cat,
2281 l_s_adm_process_type,
2282 c_appl_offer_rec.adm_cal_type,
2283 c_appl_offer_rec.adm_ci_sequence_number,
2284 p_offer_dt );
2285 ELSE
2286 l_offer_response_dt := p_offer_response_dt;
2287 END IF;
2288
2289 IF igs_ad_val_acai_status.admp_val_acai_aos (
2290 c_appl_offer_rec.person_id,
2291 c_appl_offer_rec.admission_appl_number,
2292 c_appl_offer_rec.nominated_course_cd,
2293 c_appl_offer_rec.sequence_number,
2294 c_appl_offer_rec.course_cd,
2295 c_appl_offer_rec.crv_version_number,
2296 c_appl_offer_rec.location_cd,
2297 c_appl_offer_rec.attendance_mode,
2298 c_appl_offer_rec.attendance_type,
2299 c_appl_offer_rec.unit_set_cd,
2300 c_appl_offer_rec.us_version_number,
2301 c_appl_offer_rec.acad_cal_type,
2302 c_appl_offer_rec.acad_ci_sequence_number,
2303 c_appl_offer_rec.adm_cal_type,
2304 c_appl_offer_rec.adm_ci_sequence_number,
2305 l_adm_cat,
2306 l_s_adm_process_type,
2307 l_appl_dt,
2308 c_appl_offer_rec.fee_cat,
2309 c_appl_offer_rec.correspondence_cat,
2310 c_appl_offer_rec.enrolment_cat,
2311 igs_ad_gen_009.admp_get_sys_aos('OFFER'),
2312 c_appl_offer_rec.adm_outcome_status,
2313 c_appl_offer_rec.adm_doc_status,
2314 c_appl_offer_rec.adm_fee_status,
2315 igs_ad_gen_008.admp_get_safs(c_appl_inst_old_rec.late_adm_fee_status), -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
2316 c_appl_offer_rec.adm_cndtnl_offer_status,
2317 c_appl_offer_rec.adm_entry_qual_status,
2318 igs_ad_gen_009.admp_get_sys_aors('PENDING'),
2319 c_appl_offer_rec.adm_offer_resp_status, --old
2320 c_appl_offer_rec.adm_outcome_status_auth_dt,
2321 v_set_outcome_allowed_ind,
2322 v_cond_offer_ass_allowed_ind,
2323 v_cond_offer_fee_allowed_ind,
2324 v_cond_offer_doc_allowed_ind,
2325 v_late_appl_allowed_ind,
2326 v_fees_required_ind,
2327 v_mult_offer_allowed_ind,
2328 v_multi_offer_limit,
2329 v_pref_allowed_ind,
2330 v_unit_set_appl_ind,
2331 v_check_person_encumb,
2332 v_check_course_encumb,
2333 'FORM',
2334 v_message_name
2335 ) THEN
2336
2337 FOR c_upd_acai_rec IN c_upd_acai_cur LOOP
2338
2339 -- Offer validations is successful then update the offer
2340 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
2341 x_rowid => c_upd_acai_rec.row_id ,
2342 x_person_id => c_upd_acai_rec.person_id ,
2343 x_admission_appl_number => c_upd_acai_rec.admission_appl_number ,
2344 x_nominated_course_cd => c_upd_acai_rec.nominated_course_cd ,
2345 x_sequence_number => c_upd_acai_rec.sequence_number ,
2346 x_predicted_gpa => c_upd_acai_rec.predicted_gpa ,
2347 x_academic_index => c_upd_acai_rec.academic_index,
2348 x_adm_cal_type => c_upd_acai_rec.adm_cal_type ,
2349 x_app_file_location => c_upd_acai_rec.app_file_location ,
2350 x_adm_ci_sequence_number => c_upd_acai_rec.adm_ci_sequence_number ,
2351 x_course_cd => c_upd_acai_rec.course_cd ,
2352 x_app_source_id => c_upd_acai_rec.app_source_id ,
2353 x_crv_version_number => c_upd_acai_rec.crv_version_number ,
2354 x_waitlist_rank => c_upd_acai_rec.waitlist_rank,
2355 x_waitlist_status => c_upd_acai_rec.waitlist_status,
2356 x_location_cd => c_upd_acai_rec.location_cd ,
2357 x_attent_other_inst_cd => c_upd_acai_rec.attent_other_inst_cd,
2358 x_attendance_mode => c_upd_acai_rec.attendance_mode ,
2359 x_edu_goal_prior_enroll_id => c_upd_acai_rec.edu_goal_prior_enroll_id,
2360 x_attendance_type => c_upd_acai_rec.attendance_type ,
2361 x_decision_make_id => c_appl_inst_old_rec.decision_make_id,
2362 x_unit_set_cd => c_upd_acai_rec.unit_set_cd ,
2363 x_decision_date => c_appl_inst_old_rec.decision_date,
2364 x_attribute_category => c_upd_acai_rec.attribute_category,
2365 x_attribute1 => c_upd_acai_rec.attribute1,
2366 x_attribute2 => c_upd_acai_rec.attribute2,
2367 x_attribute3 => c_upd_acai_rec.attribute3,
2368 x_attribute4 => c_upd_acai_rec.attribute4,
2369 x_attribute5 => c_upd_acai_rec.attribute5,
2370 x_attribute6 => c_upd_acai_rec.attribute6,
2371 x_attribute7 => c_upd_acai_rec.attribute7,
2372 x_attribute8 => c_upd_acai_rec.attribute8,
2373 x_attribute9 => c_upd_acai_rec.attribute9,
2374 x_attribute10 => c_upd_acai_rec.attribute10,
2375 x_attribute11 => c_upd_acai_rec.attribute11,
2376 x_attribute12 => c_upd_acai_rec.attribute12,
2377 x_attribute13 => c_upd_acai_rec.attribute13,
2378 x_attribute14 => c_upd_acai_rec.attribute14,
2379 x_attribute15 => c_upd_acai_rec.attribute15,
2380 x_attribute16 => c_upd_acai_rec.attribute16,
2381 x_attribute17 => c_upd_acai_rec.attribute17,
2382 x_attribute18 => c_upd_acai_rec.attribute18,
2383 x_attribute19 => c_upd_acai_rec.attribute19,
2384 x_attribute20 => c_upd_acai_rec.attribute20,
2385 x_decision_reason_id => c_appl_inst_old_rec.decision_reason_id,
2386 x_us_version_number => c_upd_acai_rec.us_version_number ,
2387 x_decision_notes => c_appl_inst_old_rec.decision_notes,
2388 x_pending_reason_id => c_upd_acai_rec.pending_reason_id,
2389 x_preference_number => c_upd_acai_rec.preference_number ,
2390 x_adm_doc_status => c_upd_acai_rec.adm_doc_status ,
2391 x_adm_entry_qual_status => c_upd_acai_rec.adm_entry_qual_status,
2392 x_deficiency_in_prep => c_upd_acai_rec.deficiency_in_prep ,
2393 x_late_adm_fee_status => c_appl_inst_old_rec.late_adm_fee_status , -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
2394 x_spl_consider_comments => c_upd_acai_rec.spl_consider_comments,
2395 x_adm_outcome_status => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('OFFER'),
2396 x_adm_otcm_stat_auth_per_id => c_upd_acai_rec.adm_otcm_status_auth_person_id ,
2397 x_adm_outcome_status_auth_dt => c_upd_acai_rec.adm_outcome_status_auth_dt ,
2398 x_adm_outcome_status_reason => c_upd_acai_rec.adm_outcome_status_reason ,
2399 x_offer_dt => p_offer_dt,
2400 x_offer_response_dt => l_offer_response_dt,
2401 x_prpsd_commencement_dt => p_start_dt,
2402 x_adm_cndtnl_offer_status => c_upd_acai_rec.adm_cndtnl_offer_status ,
2403 x_cndtnl_offer_satisfied_dt => c_upd_acai_rec.cndtnl_offer_satisfied_dt ,
2404 x_cndnl_ofr_must_be_stsfd_ind => c_upd_acai_rec.cndtnl_offer_must_be_stsfd_ind ,
2405 x_adm_offer_resp_status => igs_ad_gen_009.admp_get_sys_aors('PENDING'),
2406 x_actual_response_dt => c_upd_acai_rec.actual_response_dt ,
2407 x_adm_offer_dfrmnt_status => c_upd_acai_rec.adm_offer_dfrmnt_status ,
2408 x_deferred_adm_cal_type => c_upd_acai_rec.deferred_adm_cal_type,
2409 x_deferred_adm_ci_sequence_num => c_upd_acai_rec.deferred_adm_ci_sequence_num ,
2410 x_deferred_tracking_id => c_upd_acai_rec.deferred_tracking_id ,
2411 x_ass_rank => c_upd_acai_rec.ass_rank ,
2412 x_secondary_ass_rank => c_upd_acai_rec.secondary_ass_rank ,
2413 x_intr_accept_advice_num => c_upd_acai_rec.intrntnl_acceptance_advice_num ,
2414 x_ass_tracking_id => c_upd_acai_rec.ass_tracking_id ,
2415 x_fee_cat => c_upd_acai_rec.fee_cat ,
2416 x_hecs_payment_option => c_upd_acai_rec.hecs_payment_option ,
2417 x_expected_completion_yr => c_upd_acai_rec.expected_completion_yr ,
2418 x_expected_completion_perd => c_upd_acai_rec.expected_completion_perd ,
2419 x_correspondence_cat => c_upd_acai_rec.correspondence_cat ,
2420 x_enrolment_cat => c_upd_acai_rec.enrolment_cat ,
2421 x_funding_source => c_appl_inst_old_rec.funding_source,
2422 x_applicant_acptnce_cndtn => c_upd_acai_rec.applicant_acptnce_cndtn ,
2423 x_cndtnl_offer_cndtn => c_upd_acai_rec.cndtnl_offer_cndtn ,
2424 x_ss_application_id => c_upd_acai_rec.ss_application_id,
2425 x_ss_pwd => c_upd_acai_rec.ss_pwd,
2426 x_authorized_dt => c_upd_acai_rec.authorized_dt,
2427 x_authorizing_pers_id => c_upd_acai_rec.authorizing_pers_id ,
2428 x_idx_calc_date => c_upd_acai_rec.idx_calc_date,
2429 x_mode => 'R',
2430 x_fut_acad_cal_type => NULL,--p_fut_acad_cal_type,
2431 x_fut_acad_ci_sequence_number => NULL,--p_fut_acad_cal_seq_no,
2432 x_fut_adm_cal_type => NULL,--p_fut_adm_cal_type,
2433 x_fut_adm_ci_sequence_number => NULL,--p_fut_adm_cal_seq_no ,
2434 x_prev_term_adm_appl_number => p_old_admission_appl_number,
2435 x_prev_term_sequence_number => p_old_sequence_number,
2436 x_fut_term_adm_appl_number => c_upd_acai_rec.future_term_adm_appl_number,
2437 x_fut_term_sequence_number => c_upd_acai_rec.future_term_sequence_number,
2438 X_DEF_ACAD_CAL_TYPE => c_upd_acai_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
2439 X_DEF_ACAD_CI_SEQUENCE_NUM => c_upd_acai_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
2440 X_DEF_PREV_TERM_ADM_APPL_NUM => c_upd_acai_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
2441 X_DEF_PREV_APPL_SEQUENCE_NUM => c_upd_acai_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
2442 X_DEF_TERM_ADM_APPL_NUM => c_upd_acai_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
2443 X_DEF_APPL_SEQUENCE_NUM => c_upd_acai_rec.DEF_APPL_SEQUENCE_NUM, --Bug 2395510
2444 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631918
2445 -- by rrengara on 3-DEC-2002
2446 x_entry_status => c_appl_inst_old_rec.entry_status,
2447 x_entry_level => c_appl_inst_old_rec.entry_level,
2448 x_sch_apl_to_id => c_appl_inst_old_rec.sch_apl_to_id,
2449 x_apply_for_finaid => c_appl_inst_old_rec.apply_for_finaid,
2450 x_finaid_apply_date => c_appl_inst_old_rec.finaid_apply_date,
2451 x_appl_inst_status => c_appl_inst_old_rec.appl_inst_status,
2452 x_ais_reason => c_appl_inst_old_rec.ais_reason,
2453 x_decline_ofr_reason => c_appl_inst_old_rec.decline_ofr_reason
2454 );
2455 END LOOP;
2456
2457 -- Run the pre-enrollment process
2458 IF v_pre_enrol_ind = 'Y' THEN
2459 -- Validate the Enrollment Category mapping
2460 IF IGS_AD_VAL_ACAI.admp_val_acai_ec (
2461 l_adm_cat,
2462 c_appl_offer_rec.enrolment_cat,
2463 v_message_name) = FALSE THEN
2464 fnd_message.set_name('IGS', v_message_name);
2465 fnd_file.put_line(fnd_file.log, fnd_message.get);
2466 RETURN FALSE;
2467 END IF;
2468
2469
2470 -- rrengara
2471 -- on 28-oct-2002 for Build Core Vs optional Bug 2647482
2472 -- get the unit set mapping by calling the apc for the application
2473 -- then pass the value to the Pre-enrollment procedure for the parameter p_units_indicator
2474
2475
2476 IF igs_ad_upd_initialise.perform_pre_enrol(
2477 c_appl_offer_rec.person_id,
2478 c_appl_offer_rec.admission_appl_number,
2479 c_appl_offer_rec.nominated_course_cd,
2480 c_appl_offer_rec.sequence_number,
2481 'N', -- Confirm course indicator.
2482 'N', -- Perform eligibility check indicator.
2483 v_message_name) = FALSE THEN
2484 fnd_message.set_name('IGS', v_message_name);
2485 fnd_file.put_line(fnd_file.log, fnd_message.get);
2486 END IF;
2487 END IF; -- PRE-ENROLL IND = 'Y'
2488
2489 ELSE
2490 IF v_message_name IN ('IGS_AD_NOMINATE_PRG_OFR_ENTRY', 'IGS_AD_NOMINATED_PRG_ENTRYPNT') THEN
2491 v_message_name := 'IGS_AD_CAL_PGM_NOT_OFFER';
2492 fnd_message.set_name('IGS', v_message_name);
2493 fnd_message.set_token('PGM', c_appl_offer_rec.nominated_course_cd);
2494 fnd_message.set_token('ALTCODE',c_appl_offer_rec.acad_cal_type||','||IGS_GE_NUMBER.TO_CANN(c_appl_offer_rec.acad_ci_sequence_number)
2495 ||'/'||c_appl_offer_rec.final_adm_cal_type||','||IGS_GE_NUMBER.TO_CANN(c_appl_offer_rec.final_adm_ci_sequence_number));
2496 fnd_file.put_line(fnd_file.log, fnd_message.get);
2497 ELSE
2498 fnd_message.set_name('IGS', v_message_name);
2499 fnd_file.put_line(fnd_file.log, fnd_message.get);
2500 END IF;
2501 RETURN FALSE;
2502 END IF;
2503 END LOOP;
2504 RETURN TRUE;
2505 EXCEPTION WHEN OTHERS THEN
2506 fnd_file.put_line(fnd_file.log, SQLERRM);
2507 RETURN FALSE;
2508 END validate_offer_validations;
2509
2510 FUNCTION copy_entrycomp_qual_status(p_person_id HZ_PARTIES.PARTY_ID%TYPE,
2511 p_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
2512 p_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2513 p_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
2514 p_new_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
2515 p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) RETURN BOOLEAN IS
2516 /*******************************************************************************
2517 Created by : Ramesh Rengarajan
2518 Date created: 20 FEB 2002
2519
2520 Purpose:
2521 To create future term application
2522
2523 Known limitations/enhancements and/or remarks:
2524
2525 Change History: (who, when, what: )
2526 Who When What
2527 *******************************************************************************/
2528 CURSOR c_upd_acai_new_cur IS
2529 SELECT
2530 apai.*
2531 FROM
2532 igs_ad_ps_appl_inst apai
2533 WHERE
2534 apai.person_id = p_person_id
2535 AND apai.admission_appl_number = p_new_admission_appl_number
2536 AND apai.nominated_course_cd = p_nominated_course_cd
2537 AND apai.sequence_number = p_new_sequence_number;
2538
2539
2540 CURSOR c_upd_acai_old_cur IS
2541 SELECT
2542 apai.*
2543 FROM
2544 igs_ad_ps_appl_inst apai
2545 WHERE
2546 apai.person_id = p_person_id
2547 AND apai.admission_appl_number = p_admission_appl_number
2548 AND apai.nominated_course_cd = p_nominated_course_cd
2549 AND apai.sequence_number = p_sequence_number;
2550
2551 BEGIN
2552
2553 -- This procedure updates only entry qualification status from old to new future term application
2554 FOR c_upd_acai_old_rec IN c_upd_acai_old_cur LOOP
2555 FOR c_upd_acai_new_rec IN c_upd_acai_new_cur LOOP
2556 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
2557 x_rowid => c_upd_acai_new_rec.row_id ,
2558 x_person_id => c_upd_acai_new_rec.person_id ,
2559 x_admission_appl_number => c_upd_acai_new_rec.admission_appl_number ,
2560 x_nominated_course_cd => c_upd_acai_new_rec.nominated_course_cd ,
2561 x_sequence_number => c_upd_acai_new_rec.sequence_number ,
2562 x_predicted_gpa => c_upd_acai_new_rec.predicted_gpa ,
2563 x_academic_index => c_upd_acai_new_rec.academic_index,
2564 x_adm_cal_type => c_upd_acai_new_rec.adm_cal_type ,
2565 x_app_file_location => c_upd_acai_new_rec.app_file_location ,
2566 x_adm_ci_sequence_number => c_upd_acai_new_rec.adm_ci_sequence_number ,
2567 x_course_cd => c_upd_acai_new_rec.course_cd ,
2568 x_app_source_id => c_upd_acai_new_rec.app_source_id ,
2569 x_crv_version_number => c_upd_acai_new_rec.crv_version_number ,
2570 x_waitlist_rank => c_upd_acai_new_rec.waitlist_rank,
2571 x_waitlist_status => c_upd_acai_new_rec.waitlist_status,
2572 x_location_cd => c_upd_acai_new_rec.location_cd ,
2573 x_attent_other_inst_cd => c_upd_acai_new_rec.attent_other_inst_cd,
2574 x_attendance_mode => c_upd_acai_new_rec.attendance_mode ,
2575 x_edu_goal_prior_enroll_id => c_upd_acai_new_rec.edu_goal_prior_enroll_id,
2576 x_attendance_type => c_upd_acai_new_rec.attendance_type ,
2577 x_decision_make_id => c_upd_acai_new_rec.decision_make_id,
2578 x_unit_set_cd => c_upd_acai_new_rec.unit_set_cd ,
2579 x_decision_date => c_upd_acai_new_rec.decision_date,
2580 x_attribute_category => c_upd_acai_new_rec.attribute_category,
2581 x_attribute1 => c_upd_acai_new_rec.attribute1,
2582 x_attribute2 => c_upd_acai_new_rec.attribute2,
2583 x_attribute3 => c_upd_acai_new_rec.attribute3,
2584 x_attribute4 => c_upd_acai_new_rec.attribute4,
2585 x_attribute5 => c_upd_acai_new_rec.attribute5,
2586 x_attribute6 => c_upd_acai_new_rec.attribute6,
2587 x_attribute7 => c_upd_acai_new_rec.attribute7,
2588 x_attribute8 => c_upd_acai_new_rec.attribute8,
2589 x_attribute9 => c_upd_acai_new_rec.attribute9,
2590 x_attribute10 => c_upd_acai_new_rec.attribute10,
2591 x_attribute11 => c_upd_acai_new_rec.attribute11,
2592 x_attribute12 => c_upd_acai_new_rec.attribute12,
2593 x_attribute13 => c_upd_acai_new_rec.attribute13,
2594 x_attribute14 => c_upd_acai_new_rec.attribute14,
2595 x_attribute15 => c_upd_acai_new_rec.attribute15,
2596 x_attribute16 => c_upd_acai_new_rec.attribute16,
2597 x_attribute17 => c_upd_acai_new_rec.attribute17,
2598 x_attribute18 => c_upd_acai_new_rec.attribute18,
2599 x_attribute19 => c_upd_acai_new_rec.attribute19,
2600 x_attribute20 => c_upd_acai_new_rec.attribute20,
2601 x_decision_reason_id => c_upd_acai_new_rec.decision_reason_id,
2602 x_us_version_number => c_upd_acai_new_rec.us_version_number ,
2603 x_decision_notes => c_upd_acai_new_rec.decision_notes,
2604 x_pending_reason_id => c_upd_acai_new_rec.pending_reason_id,
2605 x_preference_number => c_upd_acai_new_rec.preference_number ,
2606 x_adm_doc_status => c_upd_acai_old_rec.adm_doc_status , -- updating doc status
2607 x_adm_entry_qual_status => c_upd_acai_old_rec.adm_entry_qual_status, -- updating entry qualification status
2608 x_deficiency_in_prep => c_upd_acai_new_rec.deficiency_in_prep ,
2609 x_late_adm_fee_status => c_upd_acai_old_rec.late_adm_fee_status, -- passing the user defined late adm fee status of the old application instead of 'RECEIVED' (bug#3011405 , rghosh)
2610 x_spl_consider_comments => c_upd_acai_new_rec.spl_consider_comments,
2611 x_adm_outcome_status => c_upd_acai_new_rec.adm_outcome_status,
2612 x_adm_otcm_stat_auth_per_id => c_upd_acai_new_rec.adm_otcm_status_auth_person_id,
2613 x_adm_outcome_status_auth_dt => c_upd_acai_new_rec.adm_outcome_status_auth_dt,
2614 x_adm_outcome_status_reason => c_upd_acai_new_rec.adm_outcome_status_reason,
2615 x_offer_dt => c_upd_acai_new_rec.offer_dt,
2616 x_offer_response_dt => c_upd_acai_new_rec.offer_response_dt,
2617 x_prpsd_commencement_dt => c_upd_acai_new_rec.prpsd_commencement_dt,
2618 x_adm_cndtnl_offer_status => c_upd_acai_new_rec.adm_cndtnl_offer_status,
2619 x_cndtnl_offer_satisfied_dt => c_upd_acai_new_rec.cndtnl_offer_satisfied_dt,
2620 x_cndnl_ofr_must_be_stsfd_ind => c_upd_acai_new_rec.cndtnl_offer_must_be_stsfd_ind,
2621 x_adm_offer_resp_status => c_upd_acai_new_rec.adm_offer_resp_status,
2622 x_actual_response_dt => c_upd_acai_new_rec.actual_response_dt,
2623 x_adm_offer_dfrmnt_status => c_upd_acai_new_rec.adm_offer_dfrmnt_status ,
2624 x_deferred_adm_cal_type => c_upd_acai_new_rec.deferred_adm_cal_type,
2625 x_deferred_adm_ci_sequence_num => c_upd_acai_new_rec.deferred_adm_ci_sequence_num ,
2626 x_deferred_tracking_id => c_upd_acai_new_rec.deferred_tracking_id ,
2627 x_ass_rank => c_upd_acai_new_rec.ass_rank ,
2628 x_secondary_ass_rank => c_upd_acai_new_rec.secondary_ass_rank ,
2629 x_intr_accept_advice_num => c_upd_acai_new_rec.intrntnl_acceptance_advice_num ,
2630 x_ass_tracking_id => c_upd_acai_new_rec.ass_tracking_id ,
2631 x_fee_cat => c_upd_acai_new_rec.fee_cat ,
2632 x_hecs_payment_option => c_upd_acai_new_rec.hecs_payment_option ,
2633 x_expected_completion_yr => c_upd_acai_new_rec.expected_completion_yr ,
2634 x_expected_completion_perd => c_upd_acai_new_rec.expected_completion_perd ,
2635 x_correspondence_cat => c_upd_acai_new_rec.correspondence_cat ,
2636 x_enrolment_cat => c_upd_acai_new_rec.enrolment_cat ,
2637 x_funding_source => c_upd_acai_old_rec.funding_source ,
2638 x_applicant_acptnce_cndtn => c_upd_acai_new_rec.applicant_acptnce_cndtn ,
2639 x_cndtnl_offer_cndtn => c_upd_acai_new_rec.cndtnl_offer_cndtn ,
2640 x_ss_application_id => c_upd_acai_new_rec.ss_application_id,
2641 x_ss_pwd => c_upd_acai_new_rec.ss_pwd,
2642 x_authorized_dt => c_upd_acai_new_rec.authorized_dt,
2643 x_authorizing_pers_id => c_upd_acai_new_rec.authorizing_pers_id ,
2644 x_idx_calc_date => c_upd_acai_new_rec.idx_calc_date,
2645 x_mode => 'R',
2646 x_fut_acad_cal_type => c_upd_acai_new_rec.future_acad_cal_type,
2647 x_fut_acad_ci_sequence_number => c_upd_acai_new_rec.future_acad_ci_sequence_number,
2648 x_fut_adm_cal_type => c_upd_acai_new_rec.future_adm_cal_type,
2649 x_fut_adm_ci_sequence_number => c_upd_acai_new_rec.future_adm_ci_sequence_number ,
2650 x_prev_term_adm_appl_number => c_upd_acai_new_rec.previous_term_adm_appl_number,
2651 x_prev_term_sequence_number => c_upd_acai_new_rec.previous_term_sequence_number,
2652 x_fut_term_adm_appl_number => c_upd_acai_new_rec.future_term_adm_appl_number,
2653 x_fut_term_sequence_number => c_upd_acai_new_rec.future_term_sequence_number,
2654 X_DEF_ACAD_CAL_TYPE => c_upd_acai_new_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
2655 X_DEF_ACAD_CI_SEQUENCE_NUM => c_upd_acai_new_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
2656 X_DEF_PREV_TERM_ADM_APPL_NUM => c_upd_acai_new_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
2657 X_DEF_PREV_APPL_SEQUENCE_NUM => c_upd_acai_new_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
2658 X_DEF_TERM_ADM_APPL_NUM => c_upd_acai_new_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
2659 X_DEF_APPL_SEQUENCE_NUM => c_upd_acai_new_rec.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
2660 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631918
2661 -- by rrengara on 3-DEC-2002
2662 x_entry_status => c_upd_acai_old_rec.entry_status,
2663 x_entry_level => c_upd_acai_old_rec.entry_level,
2664 x_sch_apl_to_id => c_upd_acai_old_rec.sch_apl_to_id,
2665 x_apply_for_finaid => c_upd_acai_old_rec.apply_for_finaid,
2666 x_finaid_apply_date => c_upd_acai_old_rec.finaid_apply_date,
2667 x_appl_inst_status => c_upd_acai_old_rec.appl_inst_status,
2668 x_ais_reason => c_upd_acai_old_rec.ais_reason,
2669 x_decline_ofr_reason => c_upd_acai_old_rec.decline_ofr_reason
2670 );
2671
2672 -- Also update the future application number in the old application number for link
2673 Igs_Ad_Ps_Appl_Inst_Pkg.UPDATE_ROW (
2674 x_rowid => c_upd_acai_old_rec.row_id ,
2675 x_person_id => c_upd_acai_old_rec.person_id ,
2676 x_admission_appl_number => c_upd_acai_old_rec.admission_appl_number ,
2677 x_nominated_course_cd => c_upd_acai_old_rec.nominated_course_cd ,
2678 x_sequence_number => c_upd_acai_old_rec.sequence_number ,
2679 x_predicted_gpa => c_upd_acai_old_rec.predicted_gpa ,
2680 x_academic_index => c_upd_acai_old_rec.academic_index,
2681 x_adm_cal_type => c_upd_acai_old_rec.adm_cal_type ,
2682 x_app_file_location => c_upd_acai_old_rec.app_file_location ,
2683 x_adm_ci_sequence_number => c_upd_acai_old_rec.adm_ci_sequence_number ,
2684 x_course_cd => c_upd_acai_old_rec.course_cd ,
2685 x_app_source_id => c_upd_acai_old_rec.app_source_id ,
2686 x_crv_version_number => c_upd_acai_old_rec.crv_version_number ,
2687 x_waitlist_rank => c_upd_acai_old_rec.waitlist_rank,
2688 x_waitlist_status => c_upd_acai_old_rec.waitlist_status,
2689 x_location_cd => c_upd_acai_old_rec.location_cd ,
2690 x_attent_other_inst_cd => c_upd_acai_old_rec.attent_other_inst_cd,
2691 x_attendance_mode => c_upd_acai_old_rec.attendance_mode ,
2692 x_edu_goal_prior_enroll_id => c_upd_acai_old_rec.edu_goal_prior_enroll_id,
2693 x_attendance_type => c_upd_acai_old_rec.attendance_type ,
2694 x_decision_make_id => c_upd_acai_old_rec.decision_make_id,
2695 x_unit_set_cd => c_upd_acai_old_rec.unit_set_cd ,
2696 x_decision_date => TRUNC(SYSDATE),--c_upd_acai_old_rec.decision_date,
2697 x_attribute_category => c_upd_acai_old_rec.attribute_category,
2698 x_attribute1 => c_upd_acai_old_rec.attribute1,
2699 x_attribute2 => c_upd_acai_old_rec.attribute2,
2700 x_attribute3 => c_upd_acai_old_rec.attribute3,
2701 x_attribute4 => c_upd_acai_old_rec.attribute4,
2702 x_attribute5 => c_upd_acai_old_rec.attribute5,
2703 x_attribute6 => c_upd_acai_old_rec.attribute6,
2704 x_attribute7 => c_upd_acai_old_rec.attribute7,
2705 x_attribute8 => c_upd_acai_old_rec.attribute8,
2706 x_attribute9 => c_upd_acai_old_rec.attribute9,
2707 x_attribute10 => c_upd_acai_old_rec.attribute10,
2708 x_attribute11 => c_upd_acai_old_rec.attribute11,
2709 x_attribute12 => c_upd_acai_old_rec.attribute12,
2710 x_attribute13 => c_upd_acai_old_rec.attribute13,
2711 x_attribute14 => c_upd_acai_old_rec.attribute14,
2712 x_attribute15 => c_upd_acai_old_rec.attribute15,
2713 x_attribute16 => c_upd_acai_old_rec.attribute16,
2714 x_attribute17 => c_upd_acai_old_rec.attribute17,
2715 x_attribute18 => c_upd_acai_old_rec.attribute18,
2716 x_attribute19 => c_upd_acai_old_rec.attribute19,
2717 x_attribute20 => c_upd_acai_old_rec.attribute20,
2718 x_decision_reason_id => c_upd_acai_old_rec.decision_reason_id,
2719 x_us_version_number => c_upd_acai_old_rec.us_version_number ,
2720 x_decision_notes => c_upd_acai_old_rec.decision_notes,
2721 x_pending_reason_id => c_upd_acai_old_rec.pending_reason_id,
2722 x_preference_number => c_upd_acai_old_rec.preference_number ,
2723 x_adm_doc_status => c_upd_acai_old_rec.adm_doc_status , -- updating doc status
2724 x_adm_entry_qual_status => c_upd_acai_old_rec.adm_entry_qual_status, -- updating entry qualification status
2725 x_deficiency_in_prep => c_upd_acai_old_rec.deficiency_in_prep ,
2726 x_late_adm_fee_status => c_upd_acai_old_rec.late_adm_fee_status ,
2727 x_spl_consider_comments => c_upd_acai_old_rec.spl_consider_comments,
2728 x_adm_outcome_status => IGS_AD_GEN_009.ADMP_GET_SYS_AOS('CANCELLED'),
2729 x_adm_otcm_stat_auth_per_id => c_upd_acai_old_rec.adm_otcm_status_auth_person_id,
2730 x_adm_outcome_status_auth_dt => c_upd_acai_old_rec.adm_outcome_status_auth_dt,
2731 x_adm_outcome_status_reason => c_upd_acai_old_rec.adm_outcome_status_reason,
2732 x_offer_dt => c_upd_acai_old_rec.offer_dt,
2733 x_offer_response_dt => c_upd_acai_old_rec.offer_response_dt,
2734 x_prpsd_commencement_dt => c_upd_acai_old_rec.prpsd_commencement_dt,
2735 x_adm_cndtnl_offer_status => c_upd_acai_old_rec.adm_cndtnl_offer_status,
2736 x_cndtnl_offer_satisfied_dt => c_upd_acai_old_rec.cndtnl_offer_satisfied_dt,
2737 x_cndnl_ofr_must_be_stsfd_ind => c_upd_acai_old_rec.cndtnl_offer_must_be_stsfd_ind,
2738 x_adm_offer_resp_status => c_upd_acai_old_rec.adm_offer_resp_status,
2739 x_actual_response_dt => c_upd_acai_old_rec.actual_response_dt,
2740 x_adm_offer_dfrmnt_status => c_upd_acai_old_rec.adm_offer_dfrmnt_status ,
2741 x_deferred_adm_cal_type => c_upd_acai_old_rec.deferred_adm_cal_type,
2742 x_deferred_adm_ci_sequence_num => c_upd_acai_old_rec.deferred_adm_ci_sequence_num ,
2743 x_deferred_tracking_id => c_upd_acai_old_rec.deferred_tracking_id ,
2744 x_ass_rank => c_upd_acai_old_rec.ass_rank ,
2745 x_secondary_ass_rank => c_upd_acai_old_rec.secondary_ass_rank ,
2746 x_intr_accept_advice_num => c_upd_acai_old_rec.intrntnl_acceptance_advice_num ,
2747 x_ass_tracking_id => c_upd_acai_old_rec.ass_tracking_id ,
2748 x_fee_cat => c_upd_acai_old_rec.fee_cat ,
2749 x_hecs_payment_option => c_upd_acai_old_rec.hecs_payment_option ,
2750 x_expected_completion_yr => c_upd_acai_old_rec.expected_completion_yr ,
2751 x_expected_completion_perd => c_upd_acai_old_rec.expected_completion_perd ,
2752 x_correspondence_cat => c_upd_acai_old_rec.correspondence_cat ,
2753 x_enrolment_cat => c_upd_acai_old_rec.enrolment_cat ,
2754 x_funding_source => c_upd_acai_old_rec.funding_source ,
2755 x_applicant_acptnce_cndtn => c_upd_acai_old_rec.applicant_acptnce_cndtn ,
2756 x_cndtnl_offer_cndtn => c_upd_acai_old_rec.cndtnl_offer_cndtn ,
2757 x_ss_application_id => c_upd_acai_old_rec.ss_application_id,
2758 x_ss_pwd => c_upd_acai_old_rec.ss_pwd,
2759 x_authorized_dt => c_upd_acai_old_rec.authorized_dt,
2760 x_authorizing_pers_id => c_upd_acai_old_rec.authorizing_pers_id ,
2761 x_idx_calc_date => c_upd_acai_old_rec.idx_calc_date,
2762 x_mode => 'R',
2763 x_fut_acad_cal_type => c_upd_acai_old_rec.future_acad_cal_type,
2764 x_fut_acad_ci_sequence_number => c_upd_acai_old_rec.future_acad_ci_sequence_number,
2765 x_fut_adm_cal_type => c_upd_acai_old_rec.future_adm_cal_type,
2766 x_fut_adm_ci_sequence_number => c_upd_acai_old_rec.future_adm_ci_sequence_number ,
2767 x_prev_term_adm_appl_number => c_upd_acai_old_rec.previous_term_adm_appl_number,
2768 x_prev_term_sequence_number => c_upd_acai_old_rec.previous_term_sequence_number,
2769 x_fut_term_adm_appl_number => p_new_admission_appl_number,
2770 x_fut_term_sequence_number => p_new_sequence_number,
2771 X_DEF_ACAD_CAL_TYPE => c_upd_acai_old_rec.DEF_ACAD_CAL_TYPE, --Bug 2395510
2772 X_DEF_ACAD_CI_SEQUENCE_NUM => c_upd_acai_old_rec.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
2773 X_DEF_PREV_TERM_ADM_APPL_NUM => c_upd_acai_old_rec.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
2774 X_DEF_PREV_APPL_SEQUENCE_NUM => c_upd_acai_old_rec.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
2775 X_DEF_TERM_ADM_APPL_NUM => c_upd_acai_old_rec.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
2776 X_DEF_APPL_SEQUENCE_NUM => c_upd_acai_old_rec.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
2777 -- Added entry Status, entry level and school applying id and fin aid columns here for Bug 2631918
2778 -- by rrengara on 3-DEC-2002
2779 x_entry_status => c_upd_acai_old_rec.entry_status,
2780 x_entry_level => c_upd_acai_old_rec.entry_level,
2781 x_sch_apl_to_id => c_upd_acai_old_rec.sch_apl_to_id,
2782 x_apply_for_finaid => c_upd_acai_old_rec.apply_for_finaid,
2783 x_finaid_apply_date => c_upd_acai_old_rec.finaid_apply_date,
2784 x_appl_inst_status => c_upd_acai_old_rec.appl_inst_status,
2785 x_ais_reason => c_upd_acai_old_rec.ais_reason,
2786 x_decline_ofr_reason => c_upd_acai_old_rec.decline_ofr_reason
2787 );
2788
2789 END LOOP;
2790 END LOOP;
2791 RETURN TRUE;
2792 EXCEPTION WHEN OTHERS THEN
2793 fnd_file.put_line(fnd_file.log, SQLERRM);
2794 RETURN FALSE;
2795 END copy_entrycomp_qual_status;
2796
2797
2798 END igs_ad_val_acai_ftr_offer;