[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_CANCEL_RECONSIDER
Source
1 PACKAGE BODY igs_ad_cancel_reconsider AS
2 /* $Header: IGSADC9B.pls 120.14 2006/05/26 07:21:58 pfotedar ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL Body for package: IGS_AD_CANCEL_RECONSIDER |
11 | |
12 | NOTES : This job will close all applicatoins that are part of a person|
13 | id group, or are in a specified academic admission calendar |
14 | and admission process category with Outcome status 'Rejected' |
15 | or 'No-Quota' and for which the reconsideration flag is set to|
16 | Yes, that the institution does not want to reconsider for the |
17 | current term or any future term. The job runs and unchecks the|
18 | Request for reconsideration flag for such application |
19 | instances |
20 | HISTORY |
21 | Who When What |
22 | rghosh 21-Oct-2003 Added the REF CURSOR c_dyn_pig_check and |
23 | hence the logic for supporting dynamic |
24 | Person ID Group |
25 | (Enh# 3194295 , ADCR043: Person ID Group) |
26 *=======================================================================*/
27
28 FUNCTION get_person_number ( p_person_id hz_parties.party_id%TYPE ) RETURN VARCHAR2 IS
29
30 CURSOR c_person_num IS
31 SELECT party_number
32 FROM hz_parties hp
33 WHERE hp.party_id = p_person_id;
34
35 lv_person_number hz_parties.party_number%TYPE DEFAULT NULL;
36 BEGIN
37
38 OPEN c_person_num;
39 FETCH c_person_num INTO lv_person_number;
40 CLOSE c_person_num;
41
42 RETURN lv_person_number;
43 END get_person_number;
44
45 PROCEDURE cancel_reconsider_appl (
46 Errbuf OUT NOCOPY VARCHAR2,
47 Retcode OUT NOCOPY NUMBER,
48 P_person_id_group IN NUMBER,
49 P_calendar_details IN VARCHAR2,
50 P_application_type IN VARCHAR2,
51 p_recon_no_future IN VARCHAR2,
52 p_recon_future IN VARCHAR2,
53 p_pend_future IN VARCHAR2) IS
54
55
56 TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
57 c_dyn_pig_check c_dyn_pig_checkCurTyp;
58
59 TYPE c_dyn_pig_checkrecTyp IS RECORD ( row_id igs_ad_ps_appl.row_id%TYPE,
60 person_id igs_ad_ps_appl.person_id%TYPE,
61 admission_appl_number igs_ad_ps_appl.admission_appl_number%TYPE,
62 nominated_course_cd igs_ad_ps_appl.nominated_course_cd%TYPE,
63 transfer_course_cd igs_ad_ps_appl.transfer_course_cd%TYPE,
64 basis_for_admission_type igs_ad_ps_appl.basis_for_admission_type%TYPE,
65 admission_cd igs_ad_ps_appl.admission_cd%TYPE,
66 course_rank_set igs_ad_ps_appl.course_rank_set%TYPE,
67 course_rank_schedule igs_ad_ps_appl.course_rank_schedule%TYPE,
68 req_for_adv_standing_ind igs_ad_ps_appl.req_for_adv_standing_ind%TYPE );
69 c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;
70
71 TYPE c_dyn_pig_check_table IS TABLE OF c_dyn_pig_check_rec%TYPE INDEX BY BINARY_INTEGER;
72
73 c_dyn_pig_check_table_rec c_dyn_pig_check_table;
74 l_dyn_pig_check_table_count NUMBER;
75
76 TYPE c_pend_futCurTyp IS REF CURSOR;
77 c_pend_fut c_pend_futCurTyp;
78
79 TYPE c_pend_futrecTyp IS RECORD (rowid igs_ad_ps_appl_inst.ROW_ID%TYPE,
80 person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
81 admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
82 nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
83 sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE,
84 predicted_gpa igs_ad_ps_appl_inst_all.predicted_gpa%TYPE,
85 academic_index igs_ad_ps_appl_inst_all.academic_index%TYPE,
86 adm_cal_type igs_ad_ps_appl_inst_all.adm_cal_type%TYPE,
87 app_file_location igs_ad_ps_appl_inst_all.app_file_location%TYPE,
88 adm_ci_sequence_number igs_ad_ps_appl_inst_all.adm_ci_sequence_number%TYPE,
89 course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
90 app_source_id igs_ad_ps_appl_inst_all.app_source_id%TYPE,
91 crv_version_number igs_ad_ps_appl_inst_all.crv_version_number%TYPE,
92 waitlist_rank igs_ad_ps_appl_inst_all.waitlist_rank%TYPE,
93 location_cd igs_ad_ps_appl_inst_all.location_cd%TYPE,
94 attent_other_inst_cd igs_ad_ps_appl_inst_all.attent_other_inst_cd%TYPE,
95 attendance_mode igs_ad_ps_appl_inst_all.attendance_mode%TYPE,
96 edu_goal_prior_enroll_id igs_ad_ps_appl_inst_all.edu_goal_prior_enroll_id%TYPE,
97 attendance_type igs_ad_ps_appl_inst_all.attendance_type%TYPE,
98 decision_make_id igs_ad_ps_appl_inst_all.decision_make_id%TYPE,
99 unit_set_cd igs_ad_ps_appl_inst_all.unit_set_cd%TYPE,
100 decision_date igs_ad_ps_appl_inst_all.decision_date%TYPE,
101 attribute_category igs_ad_ps_appl_inst_all.attribute_category%TYPE,
102 attribute1 igs_ad_ps_appl_inst_all.attribute1%TYPE,
103 attribute2 igs_ad_ps_appl_inst_all.attribute2%TYPE,
104 attribute3 igs_ad_ps_appl_inst_all.attribute3%TYPE,
105 attribute4 igs_ad_ps_appl_inst_all.attribute4%TYPE,
106 attribute5 igs_ad_ps_appl_inst_all.attribute5%TYPE,
107 attribute6 igs_ad_ps_appl_inst_all.attribute6%TYPE,
108 attribute7 igs_ad_ps_appl_inst_all.attribute7%TYPE,
109 attribute8 igs_ad_ps_appl_inst_all.attribute8%TYPE,
110 attribute9 igs_ad_ps_appl_inst_all.attribute9%TYPE,
111 attribute10 igs_ad_ps_appl_inst_all.attribute10%TYPE,
112 attribute11 igs_ad_ps_appl_inst_all.attribute11%TYPE,
113 attribute12 igs_ad_ps_appl_inst_all.attribute12%TYPE,
114 attribute13 igs_ad_ps_appl_inst_all.attribute13%TYPE,
115 attribute14 igs_ad_ps_appl_inst_all.attribute14%TYPE,
116 attribute15 igs_ad_ps_appl_inst_all.attribute15%TYPE,
117 attribute16 igs_ad_ps_appl_inst_all.attribute16%TYPE,
118 attribute17 igs_ad_ps_appl_inst_all.attribute17%TYPE,
119 attribute18 igs_ad_ps_appl_inst_all.attribute18%TYPE,
120 attribute19 igs_ad_ps_appl_inst_all.attribute19%TYPE,
121 attribute20 igs_ad_ps_appl_inst_all.attribute20%TYPE,
122 decision_reason_id igs_ad_ps_appl_inst_all.decision_reason_id%TYPE,
123 us_version_number igs_ad_ps_appl_inst_all.us_version_number%TYPE,
124 decision_notes igs_ad_ps_appl_inst_all.decision_notes%TYPE,
125 pending_reason_id igs_ad_ps_appl_inst_all.pending_reason_id%TYPE,
126 preference_number igs_ad_ps_appl_inst_all.preference_number%TYPE,
127 adm_doc_status igs_ad_ps_appl_inst_all.adm_doc_status%TYPE,
128 adm_entry_qual_status igs_ad_ps_appl_inst_all.adm_entry_qual_status%TYPE,
129 deficiency_in_prep igs_ad_ps_appl_inst_all.deficiency_in_prep%TYPE,
130 late_adm_fee_status igs_ad_ps_appl_inst_all.late_adm_fee_status%TYPE,
131 spl_consider_comments igs_ad_ps_appl_inst_all.spl_consider_comments%TYPE,
132 apply_for_finaid igs_ad_ps_appl_inst_all.apply_for_finaid%TYPE,
133 finaid_apply_date igs_ad_ps_appl_inst_all.finaid_apply_date%TYPE,
134 adm_outcome_status igs_ad_ps_appl_inst_all.adm_outcome_status%TYPE,
135 adm_otcm_status_auth_person_id igs_ad_ps_appl_inst_all.ADM_OTCM_STATUS_AUTH_PERSON_ID%TYPE,
136 adm_outcome_status_auth_dt igs_ad_ps_appl_inst_all.adm_outcome_status_auth_dt%TYPE,
137 adm_outcome_status_reason igs_ad_ps_appl_inst_all.adm_outcome_status_reason%TYPE,
138 offer_dt igs_ad_ps_appl_inst_all.offer_dt%TYPE,
139 offer_response_dt igs_ad_ps_appl_inst_all.offer_response_dt%TYPE,
140 prpsd_commencement_dt igs_ad_ps_appl_inst_all.prpsd_commencement_dt%TYPE,
141 adm_cndtnl_offer_status igs_ad_ps_appl_inst_all.adm_cndtnl_offer_status%TYPE,
142 cndtnl_offer_satisfied_dt igs_ad_ps_appl_inst_all.cndtnl_offer_satisfied_dt%TYPE,
143 cndtnl_offer_must_be_stsfd_ind igs_ad_ps_appl_inst_all.CNDTNL_OFFER_MUST_BE_STSFD_IND%TYPE,
144 adm_offer_resp_status igs_ad_ps_appl_inst_all.adm_offer_resp_status%TYPE,
145 actual_response_dt igs_ad_ps_appl_inst_all.actual_response_dt%TYPE,
146 adm_offer_dfrmnt_status igs_ad_ps_appl_inst_all.adm_offer_dfrmnt_status%TYPE,
147 deferred_adm_cal_type igs_ad_ps_appl_inst_all.deferred_adm_cal_type%TYPE,
148 deferred_adm_ci_sequence_num igs_ad_ps_appl_inst_all.deferred_adm_ci_sequence_num%TYPE,
149 deferred_tracking_id igs_ad_ps_appl_inst_all.deferred_tracking_id%TYPE,
150 ass_rank igs_ad_ps_appl_inst_all.ass_rank%TYPE,
151 secondary_ass_rank igs_ad_ps_appl_inst_all.secondary_ass_rank%TYPE,
152 intrntnl_acceptance_advice_num igs_ad_ps_appl_inst_all.intrntnl_acceptance_advice_num%TYPE,
153 ass_tracking_id igs_ad_ps_appl_inst_all.ass_tracking_id%TYPE,
154 fee_cat igs_ad_ps_appl_inst_all.fee_cat%TYPE,
155 hecs_payment_option igs_ad_ps_appl_inst_all.hecs_payment_option%TYPE,
156 expected_completion_yr igs_ad_ps_appl_inst_all.expected_completion_yr%TYPE,
157 expected_completion_perd igs_ad_ps_appl_inst_all.expected_completion_perd%TYPE,
158 correspondence_cat igs_ad_ps_appl_inst_all.correspondence_cat%TYPE,
159 enrolment_cat igs_ad_ps_appl_inst_all.enrolment_cat%TYPE,
160 funding_source igs_ad_ps_appl_inst_all.funding_source%TYPE,
161 applicant_acptnce_cndtn igs_ad_ps_appl_inst_all.applicant_acptnce_cndtn%TYPE,
162 cndtnl_offer_cndtn igs_ad_ps_appl_inst_all.cndtnl_offer_cndtn%TYPE,
163 ss_application_id igs_ad_ps_appl_inst_all.ss_application_id%TYPE,
164 ss_pwd igs_ad_ps_appl_inst_all.ss_pwd%TYPE,
165 authorized_dt igs_ad_ps_appl_inst_all.authorized_dt%TYPE,
166 authorizing_pers_id igs_ad_ps_appl_inst_all.authorizing_pers_id%TYPE,
167 entry_status igs_ad_ps_appl_inst_all.entry_status%TYPE,
168 entry_level igs_ad_ps_appl_inst_all.entry_level%TYPE,
169 sch_apl_to_id igs_ad_ps_appl_inst_all.sch_apl_to_id%TYPE,
170 idx_calc_date igs_ad_ps_appl_inst_all.idx_calc_date%TYPE,
171 waitlist_status igs_ad_ps_appl_inst_all.waitlist_status%TYPE,
172 attribute21 igs_ad_ps_appl_inst_all.attribute21%TYPE,
173 attribute22 igs_ad_ps_appl_inst_all.attribute22%TYPE,
174 attribute23 igs_ad_ps_appl_inst_all.attribute23%TYPE,
175 attribute24 igs_ad_ps_appl_inst_all.attribute24%TYPE,
176 attribute25 igs_ad_ps_appl_inst_all.attribute25%TYPE,
177 attribute26 igs_ad_ps_appl_inst_all.attribute26%TYPE,
178 attribute27 igs_ad_ps_appl_inst_all.attribute27%TYPE,
179 attribute28 igs_ad_ps_appl_inst_all.attribute28%TYPE,
180 attribute29 igs_ad_ps_appl_inst_all.attribute29%TYPE,
181 attribute30 igs_ad_ps_appl_inst_all.attribute30%TYPE,
182 attribute31 igs_ad_ps_appl_inst_all.attribute31%TYPE,
183 attribute32 igs_ad_ps_appl_inst_all.attribute32%TYPE,
184 attribute33 igs_ad_ps_appl_inst_all.attribute33%TYPE,
185 attribute34 igs_ad_ps_appl_inst_all.attribute34%TYPE,
186 attribute35 igs_ad_ps_appl_inst_all.attribute35%TYPE,
187 attribute36 igs_ad_ps_appl_inst_all.attribute36%TYPE,
188 attribute37 igs_ad_ps_appl_inst_all.attribute37%TYPE,
189 attribute38 igs_ad_ps_appl_inst_all.attribute38%TYPE,
190 attribute39 igs_ad_ps_appl_inst_all.attribute39%TYPE,
191 attribute40 igs_ad_ps_appl_inst_all.attribute40%TYPE,
192 future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
193 future_acad_ci_sequence_number igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
194 future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
195 future_adm_ci_sequence_number igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
196 previous_term_adm_appl_number igs_ad_ps_appl_inst_all.PREVIOUS_TERM_ADM_APPL_NUMBER%TYPE,
197 previous_term_sequence_number igs_ad_ps_appl_inst_all.PREVIOUS_TERM_SEQUENCE_NUMBER%TYPE,
198 future_term_adm_appl_number igs_ad_ps_appl_inst_all.future_term_adm_appl_number%TYPE,
199 future_term_sequence_number igs_ad_ps_appl_inst_all.future_term_sequence_number%TYPE,
200 def_acad_cal_type igs_ad_ps_appl_inst_all.def_acad_cal_type%TYPE,
201 def_acad_ci_sequence_num igs_ad_ps_appl_inst_all.def_acad_ci_sequence_num%TYPE,
202 def_prev_term_adm_appl_num igs_ad_ps_appl_inst_all.def_prev_term_adm_appl_num%TYPE,
203 def_prev_appl_sequence_num igs_ad_ps_appl_inst_all.def_prev_appl_sequence_num%TYPE,
204 def_term_adm_appl_num igs_ad_ps_appl_inst_all.def_term_adm_appl_num%TYPE,
205 def_appl_sequence_num igs_ad_ps_appl_inst_all.def_appl_sequence_num%TYPE,
206 appl_inst_status igs_ad_ps_appl_inst_all.appl_inst_status%TYPE,
207 ais_reason igs_ad_ps_appl_inst_all.ais_reason%TYPE,
208 decline_ofr_reason igs_ad_ps_appl_inst_all.decline_ofr_reason%TYPE);
209
210 c_pend_fut_rec c_pend_futrecTyp;
211
212 TYPE c_pend_fut_table IS TABLE OF c_pend_fut_rec%TYPE INDEX BY BINARY_INTEGER;
213 c_pend_fut_table_rec c_pend_fut_table;
214
215 l_pend_fut_table_count NUMBER;
216
217 lv_status VARCHAR2(1) ; /*Defaulted to 'S' and the function will return 'F' in case of failure */
218 lv_sql_stmt VARCHAR(32767) ;
219
220
221
222 CURSOR c_get_ad_ps_appl (cp_acad_cal_type Igs_ad_appl.acad_cal_type%TYPE,
223 cp_acad_sequence_number Igs_ad_appl.acad_ci_sequence_number%TYPE,
224 cp_adm_cal_type Igs_ad_appl.adm_cal_type%TYPE,
225 cp_adm_sequence_number Igs_ad_appl.adm_ci_sequence_number%TYPE,
226 cp_admission_cat Igs_ad_appl.admission_cat%TYPE,
227 cp_s_adm_process_typ Igs_ad_appl.s_admission_process_type%TYPE) IS
228 SELECT distinct psapl.*
229 FROM igs_ad_ps_appl_inst apai,
230 igs_ad_ps_appl psapl,
231 igs_ad_ou_stat aos,
232 igs_ad_appl aa,
233 igs_ad_appl_stat aps
234 WHERE (( Aa.acad_cal_type = cp_acad_cal_type AND cp_acad_cal_type IS NOT NULL) OR ( cp_acad_cal_type IS NULL))AND
235 (( Aa.acad_ci_sequence_number = cp_acad_sequence_number AND cp_acad_sequence_number IS NOT NULL)OR (cp_acad_sequence_number IS NULL) ) AND
236 (( Aa.adm_cal_type = cp_adm_cal_type AND cp_adm_cal_type IS NOT NULL) OR (cp_adm_cal_type IS NULL) ) AND
237 (( Aa.adm_ci_sequence_number = cp_adm_sequence_number AND cp_adm_sequence_number IS NOT NULL) OR (cp_adm_sequence_number IS NULL) ) AND
238 (( Aa.admission_cat = cp_admission_cat AND cp_admission_cat IS NOT NULL) OR (cp_admission_cat IS NULL)) AND
239 (( Aa.s_admission_process_type = cp_s_adm_process_typ AND cp_s_adm_process_typ IS NOT NULL) OR (cp_s_adm_process_typ IS NULL) ) AND
240 Apai.adm_outcome_status = aos.adm_outcome_status AND
241 Aos.s_adm_outcome_status = 'PENDING' AND
242 NVL(apai.appl_inst_status,'RECEIVED') NOT IN (SELECT adm_appl_status FROM igs_ad_appl_stat WHERE S_ADM_APPL_STATUS = 'WITHDRAWN') AND
243 Psapl.req_for_reconsideration_ind = 'Y' AND
244 Apai.person_id = psapl.person_id AND
245 Apai.admission_appl_number = psapl.admission_appl_number AND
246 Apai.nominated_course_cd = psapl.nominated_course_cd AND
247 Aa.person_id=apai.person_id AND
248 Aa.admission_appl_number = apai.admission_appl_number AND
249 Aa.adm_appl_status = aps.adm_appl_status AND
250 (((p_recon_no_future = 'Y') AND (apai.future_acad_cal_type IS NULL AND apai.future_acad_ci_sequence_number IS NULL
251 AND apai.future_adm_cal_type IS NULL AND apai.future_adm_ci_sequence_number IS NULL))
252 OR ((p_recon_future = 'Y') AND (apai.future_acad_cal_type IS NOT NULL AND apai.future_acad_ci_sequence_number IS NOT NULL
253 AND apai.future_adm_cal_type IS NOT NULL AND apai.future_adm_ci_sequence_number IS NOT NULL)));
254
255 l_get_ad_ps_appl_rec c_get_ad_ps_appl%ROWTYPE;
256
257 CURSOR c_get_fut_appl_inst(cp_acad_cal_type Igs_ad_appl.acad_cal_type%TYPE,
258 cp_acad_sequence_number Igs_ad_appl.acad_ci_sequence_number%TYPE,
259 cp_adm_cal_type Igs_ad_appl.adm_cal_type%TYPE,
260 cp_adm_sequence_number Igs_ad_appl.adm_ci_sequence_number%TYPE,
261 cp_admission_cat Igs_ad_appl.admission_cat%TYPE,
262 cp_s_adm_process_typ Igs_ad_appl.s_admission_process_type%TYPE)IS
263 SELECT apai.rowid, apai.*
264 FROM Igs_ad_ps_appl_inst apai,
265 Igs_ad_ps_appl psapl,
266 Igs_ad_ou_stat aos,
267 Igs_ad_appl_all Aa
268 WHERE (( Aa.acad_cal_type = cp_acad_cal_type AND cp_acad_cal_type IS NOT NULL) OR ( cp_acad_cal_type IS NULL))AND
269 (( Aa.acad_ci_sequence_number = cp_acad_sequence_number AND cp_acad_sequence_number IS NOT NULL)OR (cp_acad_sequence_number IS NULL) ) AND
270 (( Aa.adm_cal_type = cp_adm_cal_type AND cp_adm_cal_type IS NOT NULL) OR (cp_adm_cal_type IS NULL) ) AND
271 (( Aa.adm_ci_sequence_number = cp_adm_sequence_number AND cp_adm_sequence_number IS NOT NULL) OR (cp_adm_sequence_number IS NULL) ) AND
272 (( Aa.admission_cat = cp_admission_cat AND cp_admission_cat IS NOT NULL) OR (cp_admission_cat IS NULL)) AND
273 (( Aa.s_admission_process_type = cp_s_adm_process_typ AND cp_s_adm_process_typ IS NOT NULL) OR (cp_s_adm_process_typ IS NULL) ) AND
274 Apai.adm_outcome_status = aos.adm_outcome_status AND
275 Aos.s_adm_outcome_status = 'PENDING' AND
276 NVL(apai.appl_inst_status,'RECEIVED') NOT IN (SELECT adm_appl_status FROM igs_ad_appl_stat WHERE S_ADM_APPL_STATUS = 'WITHDRAWN') AND
277 Psapl.req_for_reconsideration_ind <> 'Y' AND
278 Apai.person_id = psapl.person_id AND
279 Apai.admission_appl_number = psapl.admission_appl_number AND
280 Apai.nominated_course_cd = psapl.nominated_course_cd AND
281 apai.person_id = aa.person_id AND
282 apai.admission_appl_number = aa.admission_appl_number AND
283 Apai.future_acad_cal_type IS NOT NULL AND
284 Apai.future_acad_ci_sequence_number IS NOT NULL AND
285 Apai.future_adm_cal_type IS NOT NULL AND
286 Apai.future_adm_ci_sequence_number IS NOT NULL;
287
288 l_get_fut_appl_inst c_get_fut_appl_inst%ROWTYPE;
289
290 CURSOR c_get_alternate_code (cp_cal_type igs_ca_inst_all.cal_type%TYPE,
291 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
292 SELECT alternate_code
293 FROM igs_ca_inst_all
294 WHERE cal_type = cp_cal_type
295 AND sequence_number = cp_sequence_number;
296
297 l_acad_alternate_code igs_ca_inst_all.alternate_code%TYPE;
298 l_adm_alternate_code igs_ca_inst_all.alternate_code%TYPE;
299
300 CURSOR c_get_adm_process_type (cp_s_admission_process_type igs_lookup_values.lookup_code%TYPE) IS
301 SELECT meaning
302 FROM igs_lookup_values
303 WHERE lookup_type = 'ADMISSION_PROCESS_TYPE'
304 AND lookup_code = cp_s_admission_process_type;
305
306 l_get_adm_process_type igs_ad_prcs_cat_v.meaning%TYPE;
307
308 CURSOR c_get_application_id (cp_person_id igs_ad_appl_all.person_id%TYPE,
309 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE) IS
310 SELECT application_id
311 FROM igs_ad_appl_all
312 WHERE person_id = cp_person_id
313 AND admission_appl_number = cp_admission_appl_number;
314
315 CURSOR c_get_inst_dtls(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
316 cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
317 cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
318 SELECT apai.rowid,apai.*
319 FROM igs_ad_ps_appl_inst_all apai
320 WHERE apai.person_id = cp_person_id
321 AND apai.admission_appl_number = cp_admission_appl_number
322 AND apai.nominated_course_cd = cp_nominated_course_cd
323 AND NVL(apai.appl_inst_status,'RECEIVED') NOT IN (SELECT adm_appl_status FROM igs_ad_appl_stat WHERE S_ADM_APPL_STATUS = 'WITHDRAWN');
324
325 l_get_inst_dtls_rec c_get_inst_dtls%ROWTYPE;
326
327 l_get_application_id igs_ad_appl_all.application_id%TYPE;
328
329 CURSOR c_get_aplinsthist_dtls(cp_person_id igs_ad_ps_aplinsthst_all.person_id%TYPE,
330 cp_admission_appl_number igs_ad_ps_aplinsthst_all.admission_appl_number%TYPE,
331 cp_nominated_course_cd igs_ad_ps_aplinsthst_all.nominated_course_cd%TYPE,
332 cp_sequence_number igs_ad_ps_aplinsthst_all.sequence_number%TYPE) IS
333 SELECT hist.*
334 FROM igs_ad_ps_aplinsthst_all hist
335 WHERE person_id = cp_person_id
336 AND admission_appl_number = cp_admission_appl_number
337 AND nominated_course_cd = cp_nominated_course_cd
338 AND sequence_number = cp_sequence_number;
339
340 l_get_aplinsthist_dtls_rec c_get_aplinsthist_dtls%ROWTYPE;
341
342 CURSOR c_get_hist_dt (cp_person_id igs_ad_ps_aplinsthst_all.person_id%TYPE,
343 cp_admission_appl_number igs_ad_ps_aplinsthst_all.admission_appl_number%TYPE,
344 cp_nominated_course_cd igs_ad_ps_aplinsthst_all.nominated_course_cd%TYPE,
345 cp_sequence_number igs_ad_ps_aplinsthst_all.sequence_number%TYPE) IS
346 SELECT MAX(hist_start_dt) latest_record
347 FROM igs_ad_ps_aplinsthst_all
348 WHERE person_id = cp_person_id
349 AND admission_appl_number = cp_admission_appl_number
350 AND nominated_course_cd = cp_nominated_course_cd
351 AND sequence_number = cp_sequence_number
352 AND NVL(reconsider_flag,'N') = 'N';
353
354 l_get_hist_dt DATE;
355
356 CURSOR c_get_pending_hist (cp_person_id igs_ad_ps_aplinsthst_all.person_id%TYPE,
357 cp_admission_appl_number igs_ad_ps_aplinsthst_all.admission_appl_number%TYPE,
358 cp_nominated_course_cd igs_ad_ps_aplinsthst_all.nominated_course_cd%TYPE,
359 cp_sequence_number igs_ad_ps_aplinsthst_all.sequence_number%TYPE,
360 cp_hist_start_dt igs_ad_ps_aplinsthst_all.hist_start_dt%TYPE) IS
361 SELECT hist.*
362 FROM igs_ad_ps_aplinsthst_all hist
363 WHERE person_id = cp_person_id
364 AND admission_appl_number = cp_admission_appl_number
365 AND nominated_course_cd = cp_nominated_course_cd
366 AND sequence_number = cp_sequence_number
367 AND hist_start_dt > cp_hist_start_dt
368 AND NVL(adm_outcome_status,'PENDING') <> 'PENDING';
369
370 l_get_pending_hist_rec c_get_pending_hist%ROWTYPE;
371
372 CURSOR c_admission_type (cp_admission_type IGS_AD_SS_APPL_TYP.ADMISSION_APPLICATION_TYPE%TYPE) IS
373 SELECT admission_cat, s_admission_process_type
374 FROM igs_ad_ss_appl_typ
375 WHERE admission_application_type = cp_admission_type;
376
377 l_admission_type c_admission_type%ROWTYPE;
378
379 CURSOR c_apcs(cp_admission_cat IGS_AD_PRCS_CAT_STEP.admission_cat%TYPE, cp_s_admission_process_type IGS_AD_PRCS_CAT_STEP.s_admission_process_type%TYPE) IS
380 SELECT 'Y'
381 FROM IGS_AD_PRCS_CAT_STEP
382 WHERE admission_cat = cp_admission_cat
383 AND s_admission_process_type = cp_s_admission_process_type
384 AND s_admission_step_type = 'PRE-ENROL'
385 AND step_group_type <> 'TRACK';
386
387 l_apcs VARCHAR2(1);
388
389 CURSOR c_adm_cat(cp_person_id igs_ad_ps_aplinsthst_all.person_id%TYPE,
390 cp_admission_appl_number igs_ad_ps_aplinsthst_all.admission_appl_number%TYPE) IS
391 SELECT admission_cat,s_admission_process_type
392 FROM igs_ad_appl_all
393 WHERE person_id = cp_person_id
394 AND admission_appl_number = cp_admission_appl_number;
395
396 l_adm_cat c_adm_cat%ROWTYPE;
397
398 l_acad_cal_type Igs_ad_appl.acad_cal_type%TYPE ;
399 l_acad_sequence_number Igs_ad_appl.acad_ci_sequence_number%TYPE ;
400 l_adm_cal_type Igs_ad_appl.adm_cal_type%TYPE ;
401 l_adm_sequence_number Igs_ad_appl.adm_ci_sequence_number%TYPE ;
402 l_admission_cat Igs_ad_appl.admission_cat%TYPE ;
403 l_s_adm_process_typ Igs_ad_appl.s_admission_process_type%TYPE;
404 l_processing_ind VARCHAR2(1);
405
406 -- this indicator will keep a track whether the cancel reconsideration job failed for any of the application programs.
407 l_record_errored VARCHAR2(1) ;
408
409 l_recon_future VARCHAR2(1);
410 l_recon_no_future VARCHAR2(1);
411
412 l_record_failed VARCHAR2(1);
413 lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
414
415 l_sql_stmt1 VARCHAR2(32676) := 'SELECT distinct psapl.row_id,psapl.person_id, psapl.admission_appl_number,psapl.nominated_course_cd,
416 psapl.transfer_course_cd,psapl.basis_for_admission_type,psapl.admission_cd,
417 psapl.course_rank_set,psapl.course_rank_schedule, psapl.req_for_adv_standing_ind
418 FROM
419 Igs_ad_ps_appl_inst apai,
420 igs_ad_ps_appl psapl,
421 Igs_ad_ou_stat aos,
422 Igs_ad_appl aa,
423 Igs_ad_appl_stat aps
424 WHERE
425 Apai.person_id IN ';
426
427 l_sql_stmt2 VARCHAR2(4000) := ' AND
428 Aa.acad_cal_type = NVL ( :1 , aa.acad_cal_type) AND
429 Aa.acad_ci_sequence_number = DECODE ( :2, -1,aa.acad_ci_sequence_number, :2 ) AND
430 Aa.adm_cal_type = NVL ( :3 , aa.adm_cal_type) AND
431 Aa.adm_ci_sequence_number = DECODE ( :4, -1,aa.adm_ci_sequence_number, :4 ) AND
432 Aa.admission_cat = NVL ( :5, aa.admission_cat) AND
433 Aa.s_admission_process_type = NVL ( :6 , aa.s_admission_process_type) AND
434 Apai.adm_outcome_status = aos.adm_outcome_status AND
435 Aos.s_adm_outcome_status = ''PENDING'' AND
436 NVL(apai.appl_inst_status,''RECEIVED'') NOT IN (SELECT adm_appl_status FROM igs_ad_appl_stat WHERE S_ADM_APPL_STATUS = ''WITHDRAWN'') AND
437 Psapl.req_for_reconsideration_ind = ''Y'' AND
438 Apai.person_id = psapl.person_id AND
439 Apai.admission_appl_number = psapl.admission_appl_number AND
440 Apai.nominated_course_cd = psapl.nominated_course_cd AND
441 Aa.person_id=apai.person_id AND
442 Aa.admission_appl_number = apai.admission_appl_number AND
443 Aa.adm_appl_status = aps.adm_appl_status AND
444 (((:7 = ''Y'') AND
445 (apai.future_acad_cal_type IS NULL AND apai.future_acad_ci_sequence_number IS NULL AND
446 apai.future_adm_cal_type IS NULL AND apai.future_adm_ci_sequence_number IS NULL))
447 OR ((:8 = ''Y'') AND
448 (apai.future_acad_cal_type IS NOT NULL AND apai.future_acad_ci_sequence_number IS NOT NULL AND
449 apai.future_adm_cal_type IS NOT NULL AND apai.future_adm_ci_sequence_number IS NOT NULL)))';
450
451 l_sql_stmt3 VARCHAR2(32676) :=
452 'SELECT
453 apai.row_id,
454 apai.person_id,
455 apai.admission_appl_number,
456 apai.nominated_course_cd,
457 apai.sequence_number,
458 apai.predicted_gpa,
459 apai.academic_index,
460 apai.adm_cal_type,
461 apai.app_file_location,
462 apai.adm_ci_sequence_number,
463 apai.course_cd,
464 apai.app_source_id,
465 apai.crv_version_number,
466 apai.waitlist_rank,
467 apai.location_cd,
468 apai.attent_other_inst_cd,
469 apai.attendance_mode,
470 apai.edu_goal_prior_enroll_id,
471 apai.attendance_type,
472 apai.decision_make_id,
473 apai.unit_set_cd,
474 apai.decision_date,
475 apai.attribute_category,
476 apai.attribute1,
477 apai.attribute2,
478 apai.attribute3,
479 apai.attribute4,
483 apai.attribute8,
480 apai.attribute5,
481 apai.attribute6,
482 apai.attribute7,
484 apai.attribute9,
485 apai.attribute10,
486 apai.attribute11,
487 apai.attribute12,
488 apai.attribute13,
489 apai.attribute14,
490 apai.attribute15,
491 apai.attribute16,
492 apai.attribute17,
493 apai.attribute18,
494 apai.attribute19,
495 apai.attribute20,
496 apai.decision_reason_id,
497 apai.us_version_number,
498 apai.decision_notes,
499 apai.pending_reason_id,
500 apai.preference_number,
501 apai.adm_doc_status,
502 apai.adm_entry_qual_status,
503 apai.deficiency_in_prep,
504 apai.late_adm_fee_status,
505 apai.spl_consider_comments,
506 apai.apply_for_finaid,
507 apai.finaid_apply_date,
508 apai.adm_outcome_status,
509 apai.adm_otcm_status_auth_person_id,
510 apai.adm_outcome_status_auth_dt,
511 apai.adm_outcome_status_reason,
512 apai.offer_dt,
513 apai.offer_response_dt,
514 apai.prpsd_commencement_dt,
515 apai.adm_cndtnl_offer_status,
516 apai.cndtnl_offer_satisfied_dt,
517 apai.cndtnl_offer_must_be_stsfd_ind,
518 apai.adm_offer_resp_status,
519 apai.actual_response_dt,
520 apai.adm_offer_dfrmnt_status,
521 apai.deferred_adm_cal_type,
522 apai.deferred_adm_ci_sequence_num,
523 apai.deferred_tracking_id,
524 apai.ass_rank,
525 apai.secondary_ass_rank,
526 apai.intrntnl_acceptance_advice_num,
527 apai.ass_tracking_id,
528 apai.fee_cat,
529 apai.hecs_payment_option,
530 apai.expected_completion_yr,
531 apai.expected_completion_perd,
532 apai.correspondence_cat,
533 apai.enrolment_cat,
534 apai.funding_source,
535 apai.applicant_acptnce_cndtn,
536 apai.cndtnl_offer_cndtn,
537 apai.ss_application_id,
538 apai.ss_pwd,
539 apai.authorized_dt,
540 apai.authorizing_pers_id,
541 apai.entry_status,
542 apai.entry_level,
543 apai.sch_apl_to_id,
544 apai.idx_calc_date,
545 apai.waitlist_status,
546 apai.attribute21,
547 apai.attribute22,
548 apai.attribute23,
549 apai.attribute24,
550 apai.attribute25,
551 apai.attribute26,
552 apai.attribute27,
553 apai.attribute28,
554 apai.attribute29,
555 apai.attribute30,
556 apai.attribute31,
557 apai.attribute32,
558 apai.attribute33,
559 apai.attribute34,
560 apai.attribute35,
561 apai.attribute36,
562 apai.attribute37,
563 apai.attribute38,
564 apai.attribute39,
565 apai.attribute40,
566 apai.future_acad_cal_type,
567 apai.future_acad_ci_sequence_number,
568 apai.future_adm_cal_type,
569 apai.future_adm_ci_sequence_number,
570 apai.previous_term_adm_appl_number,
571 apai.previous_term_sequence_number,
572 apai.future_term_adm_appl_number,
573 apai.future_term_sequence_number,
574 apai.def_acad_cal_type,
575 apai.def_acad_ci_sequence_num,
576 apai.def_prev_term_adm_appl_num,
577 apai.def_prev_appl_sequence_num,
578 apai.def_term_adm_appl_num,
579 apai.def_appl_sequence_num,
580 apai.appl_inst_status,
581 apai.ais_reason,
582 apai.decline_ofr_reason
583 FROM Igs_ad_ps_appl_inst apai,
584 Igs_ad_ps_appl psapl,
585 Igs_ad_ou_stat aos,
586 Igs_ad_appl_all Aa
587 WHERE Apai.person_id IN ';
588
589 l_sql_stmt4 VARCHAR2(4000) ;
590
591
592 BEGIN
593
594 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
595 igs_ge_gen_003.set_org_id(null);
596
597 retcode := 0;
598 errbuf := NULL;
599 lv_status := 'S';
600 lv_sql_stmt := igs_pe_dynamic_persid_group.get_dynamic_sql (p_person_id_group,lv_status,lv_group_type);
601 l_acad_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 1, 10));
602 l_acad_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 14, 6));
603 l_adm_cal_type := RTRIM ( SUBSTR ( p_calendar_details, 23, 10));
604 l_adm_sequence_number := IGS_GE_NUMBER.TO_NUM ( SUBSTR ( p_calendar_details, 37, 6));
605 l_record_errored := 'N';
606 igs_ge_msg_stack.initialize;
610
607
608 -- If the person id group as well as the session details are not entered then
609 -- it should give an error, since for job atleast one of the person id group or the session details are mandatory
611 OPEN c_get_alternate_code(l_acad_cal_type,l_acad_sequence_number);
612 FETCH c_get_alternate_code INTO l_acad_alternate_code;
613 CLOSE c_get_alternate_code;
614
615 OPEN c_get_alternate_code(l_adm_cal_type,l_adm_sequence_number);
616 FETCH c_get_alternate_code INTO l_adm_alternate_code;
617 CLOSE c_get_alternate_code;
618
619 OPEN c_get_adm_process_type(l_s_adm_process_typ);
620 FETCH c_get_adm_process_type INTO l_get_adm_process_type;
621 CLOSE c_get_adm_process_type;
622 FND_FILE.PUT_LINE(FND_FILE.LOG,'Person ID Group :' || p_person_id_group);
623
624 IF (l_acad_alternate_code IS NOT NULL AND l_adm_alternate_code IS NOT NULL) THEN
625 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calendar Details :' || l_acad_alternate_code || '/' || l_adm_alternate_code);
626 ELSE
627 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calendar Details :' );
628 END IF;
629
630 IF l_get_adm_process_type IS NOT NULL THEN
631 FND_FILE.PUT_LINE(FND_FILE.LOG,'Application Type :' || p_application_type);
632 ELSE
633 FND_FILE.PUT_LINE(FND_FILE.LOG,'Application Type :' );
634 END IF;
635
636 FND_FILE.PUT_LINE(FND_FILE.LOG,'' );
637 FND_FILE.PUT_LINE(FND_FILE.LOG,'Application Program Information :' );
638
639 OPEN c_admission_type(p_application_type);
640 FETCH c_admission_type INTO l_admission_type;
641 CLOSE c_admission_type;
642
643 l_admission_cat := l_admission_type.admission_cat;
644 l_s_adm_process_typ := l_admission_type.s_admission_process_type;
645
646 -- This indicator will keep a track whether any of the applicaton programs are processed.
647 l_processing_ind := 'N';
648 IF (P_person_id_group IS NULL) THEN
649
650
651 IF p_recon_future = 'Y' OR p_recon_no_future = 'Y' THEN
652
653
654
655 OPEN c_get_ad_ps_appl (l_acad_cal_type,
656 l_acad_sequence_number,
657 l_adm_cal_type,
658 l_adm_sequence_number,
659 l_admission_cat,
660 l_s_adm_process_typ);
661
662 LOOP
663
664
665 FETCH c_get_ad_ps_appl INTO l_get_ad_ps_appl_rec;
666 EXIT WHEN c_get_ad_ps_appl%NOTFOUND;
667
668 l_processing_ind := 'Y';
669
670 OPEN c_get_application_id(l_get_ad_ps_appl_rec.person_id,l_get_ad_ps_appl_rec.admission_appl_number);
671 FETCH c_get_application_id INTO l_get_application_id;
672 CLOSE c_get_application_id;
673
674 SAVEPOINT c_create_prog;
675
676 DECLARE
677
678 l_max_msg_count NUMBER;
679 l_msg_data VARCHAR2(2000);
680 l_msg_index_out NUMBER;
681 l_app_name VARCHAR2(2000);
682 v_message_name VARCHAR2(2000);
683
684 BEGIN
685
686 igs_ad_ps_appl_pkg.update_row (
687 x_rowid => l_get_ad_ps_appl_rec.row_id,
688 x_person_id => l_get_ad_ps_appl_rec.person_id,
689 x_admission_appl_number => l_get_ad_ps_appl_rec.admission_appl_number,
690 x_nominated_course_cd => l_get_ad_ps_appl_rec.nominated_course_cd,
691 x_transfer_course_cd => l_get_ad_ps_appl_rec.transfer_course_cd,
692 x_basis_for_admission_type => l_get_ad_ps_appl_rec.basis_for_admission_type,
693 x_admission_cd => l_get_ad_ps_appl_rec.admission_cd,
694 x_course_rank_set => l_get_ad_ps_appl_rec.course_rank_set,
695 x_course_rank_schedule => l_get_ad_ps_appl_rec.course_rank_schedule,
696 x_req_for_reconsideration_ind => 'N',
697 x_req_for_adv_standing_ind => l_get_ad_ps_appl_rec.req_for_adv_standing_ind ,
698 x_mode => 'R' ) ;
699
700 FOR l_get_inst_dtls_rec IN c_get_inst_dtls(l_get_ad_ps_appl_rec.person_id,
701 l_get_ad_ps_appl_rec.admission_appl_number,
702 l_get_ad_ps_appl_rec.nominated_course_cd) LOOP
703
704 FND_FILE.PUT_LINE(FND_FILE.LOG,' Person Number: ' || RPAD(get_person_number(l_get_ad_ps_appl_rec.person_id),15,' ') || ' Application ID: ' ||
705 RPAD(l_get_application_id,15,' ') || ' Program: ' || RPAD(l_get_ad_ps_appl_rec.nominated_course_cd,6,' ')
706 || 'Sequence Number: ' || RPAD(l_get_inst_dtls_rec.sequence_number,6,' '));
707
708 l_record_failed := 'N';
709
710 OPEN c_get_hist_dt(l_get_inst_dtls_rec.person_id,
711 l_get_inst_dtls_rec.admission_appl_number,
712 l_get_inst_dtls_rec.nominated_course_cd,
713 l_get_inst_dtls_rec.sequence_number);
714 FETCH c_get_hist_dt INTO l_get_hist_dt;
715 CLOSE c_get_hist_dt;
716
717 OPEN c_get_pending_hist(l_get_inst_dtls_rec.person_id,
718 l_get_inst_dtls_rec.admission_appl_number,
719 l_get_inst_dtls_rec.nominated_course_cd,
720 l_get_inst_dtls_rec.sequence_number,
721 l_get_hist_dt);
725
722 FETCH c_get_pending_hist INTO l_get_pending_hist_rec;
723
724 IF c_get_pending_hist%FOUND THEN
726 igs_ad_cancel_reconsider.g_cancel_recons_on := 'Y';
727
728 igs_ad_ps_appl_inst_pkg.update_row (
729 X_ROWID => l_get_inst_dtls_rec.ROWID,
730 x_PERSON_ID => l_get_inst_dtls_rec.PERSON_ID,
731 x_ADMISSION_APPL_NUMBER => l_get_inst_dtls_rec.ADMISSION_APPL_NUMBER,
732 x_NOMINATED_COURSE_CD => l_get_inst_dtls_rec.NOMINATED_COURSE_CD,
733 x_SEQUENCE_NUMBER => l_get_inst_dtls_rec.SEQUENCE_NUMBER,
734 x_PREDICTED_GPA => l_get_inst_dtls_rec.PREDICTED_GPA,
735 x_ACADEMIC_INDEX => l_get_inst_dtls_rec.ACADEMIC_INDEX,
736 x_ADM_CAL_TYPE => l_get_inst_dtls_rec.ADM_CAL_TYPE,
737 x_APP_FILE_LOCATION => l_get_inst_dtls_rec.APP_FILE_LOCATION,
738 x_ADM_CI_SEQUENCE_NUMBER => l_get_inst_dtls_rec.ADM_CI_SEQUENCE_NUMBER,
739 x_COURSE_CD => l_get_inst_dtls_rec.COURSE_CD,
740 x_APP_SOURCE_ID => l_get_inst_dtls_rec.APP_SOURCE_ID,
741 x_CRV_VERSION_NUMBER => l_get_inst_dtls_rec.CRV_VERSION_NUMBER,
742 x_WAITLIST_RANK => l_get_pending_hist_rec.WAITLIST_RANK,
743 x_LOCATION_CD => l_get_inst_dtls_rec.LOCATION_CD,
744 x_ATTENT_OTHER_INST_CD => l_get_inst_dtls_rec.ATTENT_OTHER_INST_CD,
745 x_ATTENDANCE_MODE => l_get_inst_dtls_rec.ATTENDANCE_MODE,
746 x_EDU_GOAL_PRIOR_ENROLL_ID => l_get_inst_dtls_rec.EDU_GOAL_PRIOR_ENROLL_ID,
747 x_ATTENDANCE_TYPE => l_get_inst_dtls_rec.ATTENDANCE_TYPE,
748 x_DECISION_MAKE_ID => l_get_pending_hist_rec.DECISION_MAKE_ID,
749 x_UNIT_SET_CD => l_get_inst_dtls_rec.UNIT_SET_CD,
750 x_DECISION_DATE => l_get_pending_hist_rec.DECISION_DATE,
751 x_ATTRIBUTE_CATEGORY => l_get_inst_dtls_rec.ATTRIBUTE_CATEGORY,
752 x_ATTRIBUTE1 => l_get_inst_dtls_rec.ATTRIBUTE1,
753 x_ATTRIBUTE2 => l_get_inst_dtls_rec.ATTRIBUTE2,
754 x_ATTRIBUTE3 => l_get_inst_dtls_rec.ATTRIBUTE3,
755 x_ATTRIBUTE4 => l_get_inst_dtls_rec.ATTRIBUTE4,
756 x_ATTRIBUTE5 => l_get_inst_dtls_rec.ATTRIBUTE5,
757 x_ATTRIBUTE6 => l_get_inst_dtls_rec.ATTRIBUTE6,
758 x_ATTRIBUTE7 => l_get_inst_dtls_rec.ATTRIBUTE7,
759 x_ATTRIBUTE8 => l_get_inst_dtls_rec.ATTRIBUTE8,
760 x_ATTRIBUTE9 => l_get_inst_dtls_rec.ATTRIBUTE9,
761 x_ATTRIBUTE10 => l_get_inst_dtls_rec.ATTRIBUTE10,
762 x_ATTRIBUTE11 => l_get_inst_dtls_rec.ATTRIBUTE11,
763 x_ATTRIBUTE12 => l_get_inst_dtls_rec.ATTRIBUTE12,
764 x_ATTRIBUTE13 => l_get_inst_dtls_rec.ATTRIBUTE13,
765 x_ATTRIBUTE14 => l_get_inst_dtls_rec.ATTRIBUTE14,
766 x_ATTRIBUTE15 => l_get_inst_dtls_rec.ATTRIBUTE15,
767 x_ATTRIBUTE16 => l_get_inst_dtls_rec.ATTRIBUTE16,
768 x_ATTRIBUTE17 => l_get_inst_dtls_rec.ATTRIBUTE17,
769 x_ATTRIBUTE18 => l_get_inst_dtls_rec.ATTRIBUTE18,
770 x_ATTRIBUTE19 => l_get_inst_dtls_rec.ATTRIBUTE19,
771 x_ATTRIBUTE20 => l_get_inst_dtls_rec.ATTRIBUTE20,
772 x_DECISION_REASON_ID => l_get_pending_hist_rec.DECISION_REASON_ID,
773 x_US_VERSION_NUMBER => l_get_inst_dtls_rec.US_VERSION_NUMBER,
774 x_DECISION_NOTES => l_get_inst_dtls_rec.DECISION_NOTES,
775 x_PENDING_REASON_ID => l_get_pending_hist_rec.PENDING_REASON_ID,
776 x_PREFERENCE_NUMBER => l_get_inst_dtls_rec.PREFERENCE_NUMBER,
777 x_ADM_DOC_STATUS => l_get_inst_dtls_rec.ADM_DOC_STATUS,
778 x_ADM_ENTRY_QUAL_STATUS => l_get_inst_dtls_rec.ADM_ENTRY_QUAL_STATUS,
779 x_DEFICIENCY_IN_PREP => l_get_inst_dtls_rec.DEFICIENCY_IN_PREP,
780 x_LATE_ADM_FEE_STATUS => l_get_inst_dtls_rec.LATE_ADM_FEE_STATUS,
781 x_SPL_CONSIDER_COMMENTS => l_get_inst_dtls_rec.SPL_CONSIDER_COMMENTS,
785 x_adm_otcm_stat_auth_per_id => l_get_inst_dtls_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
782 x_APPLY_FOR_FINAID => l_get_inst_dtls_rec.APPLY_FOR_FINAID,
783 x_FINAID_APPLY_DATE => l_get_inst_dtls_rec.FINAID_APPLY_DATE,
784 x_ADM_OUTCOME_STATUS => l_get_pending_hist_rec.ADM_OUTCOME_STATUS,
786 x_ADM_OUTCOME_STATUS_AUTH_DT => l_get_inst_dtls_rec.ADM_OUTCOME_STATUS_AUTH_DT,
787 x_ADM_OUTCOME_STATUS_REASON => l_get_inst_dtls_rec.ADM_OUTCOME_STATUS_REASON,
788 x_OFFER_DT => l_get_pending_hist_rec.OFFER_DT,
789 x_OFFER_RESPONSE_DT => l_get_pending_hist_rec.OFFER_RESPONSE_DT,
790 x_PRPSD_COMMENCEMENT_DT => l_get_pending_hist_rec.PRPSD_COMMENCEMENT_DT,
791 x_ADM_CNDTNL_OFFER_STATUS => NVL(l_get_pending_hist_rec.ADM_CNDTNL_OFFER_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_ACOS('NOT-APPLIC')),
792 x_CNDTNL_OFFER_SATISFIED_DT => l_get_pending_hist_rec.CNDTNL_OFFER_SATISFIED_DT,
793 x_cndnl_ofr_must_be_stsfd_ind => l_get_pending_hist_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND,
794 x_ADM_OFFER_RESP_STATUS => NVL(l_get_pending_hist_rec.ADM_OFFER_RESP_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_AORS('NOT-APPLIC')),
795 x_ACTUAL_RESPONSE_DT => l_get_pending_hist_rec.ACTUAL_RESPONSE_DT,
796 x_ADM_OFFER_DFRMNT_STATUS => NVL(l_get_pending_hist_rec.ADM_OFFER_DFRMNT_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_AODS('NOT-APPLIC')),
797 x_DEFERRED_ADM_CAL_TYPE => l_get_pending_hist_rec.DEFERRED_ADM_CAL_TYPE,
798 x_DEFERRED_ADM_CI_SEQUENCE_NUM => l_get_pending_hist_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
799 x_DEFERRED_TRACKING_ID => l_get_inst_dtls_rec.DEFERRED_TRACKING_ID,
800 x_ASS_RANK => l_get_inst_dtls_rec.ASS_RANK,
801 x_SECONDARY_ASS_RANK => l_get_inst_dtls_rec.SECONDARY_ASS_RANK,
802 x_intr_accept_advice_num => l_get_inst_dtls_rec.intrntnl_acceptance_advice_num,
803 x_ASS_TRACKING_ID => l_get_inst_dtls_rec.ASS_TRACKING_ID,
804 x_FEE_CAT => l_get_inst_dtls_rec.FEE_CAT,
805 x_HECS_PAYMENT_OPTION => l_get_inst_dtls_rec.HECS_PAYMENT_OPTION,
806 x_EXPECTED_COMPLETION_YR => l_get_inst_dtls_rec.EXPECTED_COMPLETION_YR,
807 x_EXPECTED_COMPLETION_PERD => l_get_inst_dtls_rec.EXPECTED_COMPLETION_PERD,
808 x_CORRESPONDENCE_CAT => l_get_inst_dtls_rec.CORRESPONDENCE_CAT,
809 x_ENROLMENT_CAT => l_get_inst_dtls_rec.ENROLMENT_CAT,
810 x_FUNDING_SOURCE => l_get_inst_dtls_rec.FUNDING_SOURCE,
811 x_APPLICANT_ACPTNCE_CNDTN => l_get_pending_hist_rec.APPLICANT_ACPTNCE_CNDTN,
812 x_CNDTNL_OFFER_CNDTN => l_get_pending_hist_rec.CNDTNL_OFFER_CNDTN,
813 X_MODE => 'S',
814 X_SS_APPLICATION_ID => l_get_inst_dtls_rec.SS_APPLICATION_ID,
815 X_SS_PWD => l_get_inst_dtls_rec.SS_PWD,
816 X_AUTHORIZED_DT => l_get_inst_dtls_rec.AUTHORIZED_DT,
817 X_AUTHORIZING_PERS_ID => l_get_inst_dtls_rec.AUTHORIZING_PERS_ID,
818 x_entry_status => l_get_inst_dtls_rec.entry_status,
819 x_entry_level => l_get_inst_dtls_rec.entry_level,
820 x_sch_apl_to_id => l_get_inst_dtls_rec.sch_apl_to_id,
821 x_idx_calc_date => l_get_inst_dtls_rec.idx_calc_date,
822 x_waitlist_status => l_get_pending_hist_rec.waitlist_status,
823 x_ATTRIBUTE21 => l_get_inst_dtls_rec.ATTRIBUTE21,
824 x_ATTRIBUTE22 => l_get_inst_dtls_rec.ATTRIBUTE22,
825 x_ATTRIBUTE23 => l_get_inst_dtls_rec.ATTRIBUTE23,
826 x_ATTRIBUTE24 => l_get_inst_dtls_rec.ATTRIBUTE24,
827 x_ATTRIBUTE25 => l_get_inst_dtls_rec.ATTRIBUTE25,
828 x_ATTRIBUTE26 => l_get_inst_dtls_rec.ATTRIBUTE26,
829 x_ATTRIBUTE27 => l_get_inst_dtls_rec.ATTRIBUTE27,
830 x_ATTRIBUTE28 => l_get_inst_dtls_rec.ATTRIBUTE28,
831 x_ATTRIBUTE29 => l_get_inst_dtls_rec.ATTRIBUTE29,
835 x_ATTRIBUTE33 => l_get_inst_dtls_rec.ATTRIBUTE33,
832 x_ATTRIBUTE30 => l_get_inst_dtls_rec.ATTRIBUTE30,
833 x_ATTRIBUTE31 => l_get_inst_dtls_rec.ATTRIBUTE31,
834 x_ATTRIBUTE32 => l_get_inst_dtls_rec.ATTRIBUTE32,
836 x_ATTRIBUTE34 => l_get_inst_dtls_rec.ATTRIBUTE34,
837 x_ATTRIBUTE35 => l_get_inst_dtls_rec.ATTRIBUTE35,
838 x_ATTRIBUTE36 => l_get_inst_dtls_rec.ATTRIBUTE36,
839 x_ATTRIBUTE37 => l_get_inst_dtls_rec.ATTRIBUTE37,
840 x_ATTRIBUTE38 => l_get_inst_dtls_rec.ATTRIBUTE38,
841 x_ATTRIBUTE39 => l_get_inst_dtls_rec.ATTRIBUTE39,
842 x_ATTRIBUTE40 => l_get_inst_dtls_rec.ATTRIBUTE40,
843 x_fut_acad_cal_type => l_get_pending_hist_rec.future_acad_cal_type,
844 x_fut_acad_ci_sequence_number => l_get_pending_hist_rec.future_acad_ci_sequence_num,
845 x_fut_adm_cal_type => l_get_pending_hist_rec.future_adm_cal_type,
846 x_fut_adm_ci_sequence_number => l_get_pending_hist_rec.future_adm_ci_sequence_num,
847 x_prev_term_adm_appl_number => l_get_inst_dtls_rec.PREVIOUS_TERM_ADM_APPL_NUMBER,
848 x_prev_term_sequence_number => l_get_inst_dtls_rec.PREVIOUS_TERM_SEQUENCE_NUMBER,
849 x_fut_term_adm_appl_number => l_get_inst_dtls_rec.future_term_adm_appl_number,
850 x_fut_term_sequence_number => l_get_inst_dtls_rec.future_term_sequence_number,
851 x_def_acad_cal_type => l_get_pending_hist_rec.def_acad_cal_type,
852 x_def_acad_ci_sequence_num => l_get_pending_hist_rec.def_acad_ci_sequence_num,
853 x_def_prev_term_adm_appl_num => l_get_inst_dtls_rec.def_prev_term_adm_appl_num,
854 x_def_prev_appl_sequence_num => l_get_inst_dtls_rec.def_prev_appl_sequence_num,
855 x_def_term_adm_appl_num => l_get_inst_dtls_rec.def_term_adm_appl_num,
856 x_def_appl_sequence_num => l_get_inst_dtls_rec.def_appl_sequence_num,
857 x_appl_inst_status => l_get_inst_dtls_rec.appl_inst_status,
858 x_ais_reason => l_get_inst_dtls_rec.ais_reason,
859 x_decline_ofr_reason => l_get_pending_hist_rec.decline_ofr_reason);
860
861 igs_ad_cancel_reconsider.g_cancel_recons_on := 'N';
862
863 igs_ad_wf_001.wf_raise_event ( p_person_id => l_get_inst_dtls_rec.Person_Id,
864 p_raised_for => 'AOD',
865 p_admission_appl_number => l_get_inst_dtls_rec.Admission_Appl_Number,
866 p_nominated_course_cd => l_get_inst_dtls_rec.Nominated_Course_cd,
867 p_sequence_number => l_get_inst_dtls_rec.Sequence_Number,
868 p_old_outcome_status => l_get_inst_dtls_rec.adm_outcome_status,
869 p_new_outcome_status => l_get_pending_hist_rec.ADM_OUTCOME_STATUS
870 );
871
872 OPEN c_adm_cat(l_get_inst_dtls_rec.person_id,l_get_inst_dtls_rec.admission_appl_number);
873 FETCH c_adm_cat INTO l_adm_cat;
874 CLOSE c_adm_cat;
875
876
877 OPEN c_apcs(l_adm_cat.admission_cat, l_adm_cat.s_admission_process_type);
878 FETCH c_apcs INTO l_apcs;
879 CLOSE c_apcs;
880
881 IF NVL(igs_ad_gen_008.admp_get_saors(l_get_pending_hist_rec.ADM_OFFER_RESP_STATUS),'NULL') = 'ACCEPTED' THEN
882 IF igs_ad_upd_initialise.perform_pre_enrol(l_get_inst_dtls_rec.Person_Id,
883 l_get_inst_dtls_rec.Admission_Appl_Number,
884 l_get_inst_dtls_rec.Nominated_Course_cd,
885 l_get_inst_dtls_rec.Sequence_Number,
886 'Y', -- Confirm course indicator.
887 'Y', -- Perform eligibility check indicator.
888 v_message_name) = FALSE THEN
889 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cancel Reconsideration Failed: ' || v_message_name);
890 l_record_failed := 'Y';
891 ROLLBACK TO c_create_prog;
892 END IF;
893 ELSIF (igs_ad_gen_008.admp_get_saos(l_get_pending_hist_rec.ADM_OUTCOME_STATUS)) IN ('COND-OFFER', 'OFFER') AND l_apcs = 'Y' THEN
894 IF igs_ad_upd_initialise.perform_pre_enrol(l_get_inst_dtls_rec.Person_Id,
895 l_get_inst_dtls_rec.Admission_Appl_Number,
896 l_get_inst_dtls_rec.Nominated_Course_cd,
897 l_get_inst_dtls_rec.Sequence_Number,
901 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cancel Reconsideration Failed: ' || v_message_name);
898 'N', -- Confirm course indicator.
899 'N', -- Perform eligibility check indicator.
900 v_message_name) = FALSE THEN
902 l_record_failed := 'Y';
903 ROLLBACK TO c_create_prog;
904 END IF;
905 END IF;
906
907 END IF;
908 CLOSE c_get_pending_hist;
909
910 END LOOP;
911
912 IF l_record_failed = 'N' THEN
913
914 igs_ad_gen_002.ins_dummy_pend_hist_rec ( l_get_ad_ps_appl_rec.person_id,
915 l_get_ad_ps_appl_rec.admission_appl_number,
916 l_get_ad_ps_appl_rec.nominated_course_cd
917 );
918
919 FND_FILE.PUT_LINE(FND_FILE.LOG,' Cancelled Reconsideration Request.' );
920
921 END IF;
922
923 EXCEPTION
924 WHEN OTHERS THEN
925
926 IF NVL(igs_ad_cancel_reconsider.g_cancel_recons_on,'N') = 'Y' THEN
927 igs_ad_cancel_reconsider.g_cancel_recons_on := 'N';
928 END IF;
929
930 IF c_get_pending_hist%ISOPEN THEN
931 CLOSE c_get_pending_hist;
932 END IF;
933
934 ROLLBACK TO c_create_prog;
935
936 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
937
938 l_record_errored := 'Y';
939
940 END;
941
942 END LOOP;
943 CLOSE c_get_ad_ps_appl;
944 END IF;
945
946 IF p_pend_future = 'Y' THEN
947
948 FOR l_get_fut_appl_inst IN c_get_fut_appl_inst (l_acad_cal_type,
949 l_acad_sequence_number,
950 l_adm_cal_type,
951 l_adm_sequence_number,
952 l_admission_cat,
953 l_s_adm_process_typ) LOOP
954
955 SAVEPOINT c_update_inst;
956 l_processing_ind := 'Y';
957
958 DECLARE
959
960 l_max_msg_count NUMBER;
961 l_msg_data VARCHAR2(2000);
962 l_msg_index_out NUMBER;
963 l_app_name VARCHAR2(2000);
964 v_message_name VARCHAR2(2000);
965
966 BEGIN
967
968 /* Begin Apadegal - 4747281 */
969 OPEN c_get_application_id(l_get_fut_appl_inst.PERSON_ID,l_get_fut_appl_inst.ADMISSION_APPL_NUMBER);
970 FETCH c_get_application_id INTO l_get_application_id;
971 CLOSE c_get_application_id;
972
973 FND_FILE.PUT_LINE(FND_FILE.LOG,' Person Number: ' || RPAD(get_person_number(l_get_fut_appl_inst.PERSON_ID),15,' ') || ' Application ID: ' ||
974 RPAD(l_get_application_id,15,' ') || ' Program: ' || RPAD(l_get_fut_appl_inst.NOMINATED_COURSE_CD,6,' ')
975 || 'Sequence Number: ' || RPAD(l_get_fut_appl_inst.SEQUENCE_NUMBER,6,' '));
976 l_get_application_id := NULL;
977 /* End Apadegal - 4747281 */
978
979 igs_ad_ps_appl_inst_pkg.update_row (
980 X_ROWID => l_get_fut_appl_inst.ROWID,
981 x_PERSON_ID => l_get_fut_appl_inst.PERSON_ID,
982 x_ADMISSION_APPL_NUMBER => l_get_fut_appl_inst.ADMISSION_APPL_NUMBER,
983 x_NOMINATED_COURSE_CD => l_get_fut_appl_inst.NOMINATED_COURSE_CD,
984 x_SEQUENCE_NUMBER => l_get_fut_appl_inst.SEQUENCE_NUMBER,
985 x_PREDICTED_GPA => l_get_fut_appl_inst.PREDICTED_GPA,
986 x_ACADEMIC_INDEX => l_get_fut_appl_inst.ACADEMIC_INDEX,
987 x_ADM_CAL_TYPE => l_get_fut_appl_inst.ADM_CAL_TYPE,
988 x_APP_FILE_LOCATION => l_get_fut_appl_inst.APP_FILE_LOCATION,
989 x_ADM_CI_SEQUENCE_NUMBER => l_get_fut_appl_inst.ADM_CI_SEQUENCE_NUMBER,
990 x_COURSE_CD => l_get_fut_appl_inst.COURSE_CD,
991 x_APP_SOURCE_ID => l_get_fut_appl_inst.APP_SOURCE_ID,
992 x_CRV_VERSION_NUMBER => l_get_fut_appl_inst.CRV_VERSION_NUMBER,
993 x_WAITLIST_RANK => l_get_fut_appl_inst.WAITLIST_RANK,
994 x_LOCATION_CD => l_get_fut_appl_inst.LOCATION_CD,
995 x_ATTENT_OTHER_INST_CD => l_get_fut_appl_inst.ATTENT_OTHER_INST_CD,
996 x_ATTENDANCE_MODE => l_get_fut_appl_inst.ATTENDANCE_MODE,
997 x_EDU_GOAL_PRIOR_ENROLL_ID => l_get_fut_appl_inst.EDU_GOAL_PRIOR_ENROLL_ID,
998 x_ATTENDANCE_TYPE => l_get_fut_appl_inst.ATTENDANCE_TYPE,
1002 x_ATTRIBUTE_CATEGORY => l_get_fut_appl_inst.ATTRIBUTE_CATEGORY,
999 x_DECISION_MAKE_ID => l_get_fut_appl_inst.DECISION_MAKE_ID,
1000 x_UNIT_SET_CD => l_get_fut_appl_inst.UNIT_SET_CD,
1001 x_DECISION_DATE => l_get_fut_appl_inst.DECISION_DATE,
1003 x_ATTRIBUTE1 => l_get_fut_appl_inst.ATTRIBUTE1,
1004 x_ATTRIBUTE2 => l_get_fut_appl_inst.ATTRIBUTE2,
1005 x_ATTRIBUTE3 => l_get_fut_appl_inst.ATTRIBUTE3,
1006 x_ATTRIBUTE4 => l_get_fut_appl_inst.ATTRIBUTE4,
1007 x_ATTRIBUTE5 => l_get_fut_appl_inst.ATTRIBUTE5,
1008 x_ATTRIBUTE6 => l_get_fut_appl_inst.ATTRIBUTE6,
1009 x_ATTRIBUTE7 => l_get_fut_appl_inst.ATTRIBUTE7,
1010 x_ATTRIBUTE8 => l_get_fut_appl_inst.ATTRIBUTE8,
1011 x_ATTRIBUTE9 => l_get_fut_appl_inst.ATTRIBUTE9,
1012 x_ATTRIBUTE10 => l_get_fut_appl_inst.ATTRIBUTE10,
1013 x_ATTRIBUTE11 => l_get_fut_appl_inst.ATTRIBUTE11,
1014 x_ATTRIBUTE12 => l_get_fut_appl_inst.ATTRIBUTE12,
1015 x_ATTRIBUTE13 => l_get_fut_appl_inst.ATTRIBUTE13,
1016 x_ATTRIBUTE14 => l_get_fut_appl_inst.ATTRIBUTE14,
1017 x_ATTRIBUTE15 => l_get_fut_appl_inst.ATTRIBUTE15,
1018 x_ATTRIBUTE16 => l_get_fut_appl_inst.ATTRIBUTE16,
1019 x_ATTRIBUTE17 => l_get_fut_appl_inst.ATTRIBUTE17,
1020 x_ATTRIBUTE18 => l_get_fut_appl_inst.ATTRIBUTE18,
1021 x_ATTRIBUTE19 => l_get_fut_appl_inst.ATTRIBUTE19,
1022 x_ATTRIBUTE20 => l_get_fut_appl_inst.ATTRIBUTE20,
1023 x_DECISION_REASON_ID => l_get_fut_appl_inst.DECISION_REASON_ID,
1027 x_PREFERENCE_NUMBER => l_get_fut_appl_inst.PREFERENCE_NUMBER,
1024 x_US_VERSION_NUMBER => l_get_fut_appl_inst.US_VERSION_NUMBER,
1025 x_DECISION_NOTES => l_get_fut_appl_inst.DECISION_NOTES,
1026 x_PENDING_REASON_ID => l_get_fut_appl_inst.PENDING_REASON_ID,
1028 x_ADM_DOC_STATUS => l_get_fut_appl_inst.ADM_DOC_STATUS,
1029 x_ADM_ENTRY_QUAL_STATUS => l_get_fut_appl_inst.ADM_ENTRY_QUAL_STATUS,
1030 x_DEFICIENCY_IN_PREP => l_get_fut_appl_inst.DEFICIENCY_IN_PREP,
1031 x_LATE_ADM_FEE_STATUS => l_get_fut_appl_inst.LATE_ADM_FEE_STATUS,
1032 x_SPL_CONSIDER_COMMENTS => l_get_fut_appl_inst.SPL_CONSIDER_COMMENTS,
1033 x_APPLY_FOR_FINAID => l_get_fut_appl_inst.APPLY_FOR_FINAID,
1034 x_FINAID_APPLY_DATE => l_get_fut_appl_inst.FINAID_APPLY_DATE,
1035 x_ADM_OUTCOME_STATUS => l_get_fut_appl_inst.ADM_OUTCOME_STATUS,
1036 x_adm_otcm_stat_auth_per_id => l_get_fut_appl_inst.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1037 x_ADM_OUTCOME_STATUS_AUTH_DT => l_get_fut_appl_inst.ADM_OUTCOME_STATUS_AUTH_DT,
1038 x_ADM_OUTCOME_STATUS_REASON => l_get_fut_appl_inst.ADM_OUTCOME_STATUS_REASON,
1039 x_OFFER_DT => l_get_fut_appl_inst.OFFER_DT,
1040 x_OFFER_RESPONSE_DT => l_get_fut_appl_inst.OFFER_RESPONSE_DT,
1041 x_PRPSD_COMMENCEMENT_DT => l_get_fut_appl_inst.PRPSD_COMMENCEMENT_DT,
1042 x_ADM_CNDTNL_OFFER_STATUS => l_get_fut_appl_inst.ADM_CNDTNL_OFFER_STATUS,
1043 x_CNDTNL_OFFER_SATISFIED_DT => l_get_fut_appl_inst.CNDTNL_OFFER_SATISFIED_DT,
1044 x_cndnl_ofr_must_be_stsfd_ind => l_get_fut_appl_inst.CNDTNL_OFFER_MUST_BE_STSFD_IND,
1045 x_ADM_OFFER_RESP_STATUS => l_get_fut_appl_inst.ADM_OFFER_RESP_STATUS,
1046 x_ACTUAL_RESPONSE_DT => l_get_fut_appl_inst.ACTUAL_RESPONSE_DT,
1047 x_ADM_OFFER_DFRMNT_STATUS => l_get_fut_appl_inst.ADM_OFFER_DFRMNT_STATUS,
1048 x_DEFERRED_ADM_CAL_TYPE => l_get_fut_appl_inst.DEFERRED_ADM_CAL_TYPE,
1049 x_DEFERRED_ADM_CI_SEQUENCE_NUM => l_get_fut_appl_inst.DEFERRED_ADM_CI_SEQUENCE_NUM,
1050 x_DEFERRED_TRACKING_ID => l_get_fut_appl_inst.DEFERRED_TRACKING_ID,
1051 x_ASS_RANK => l_get_fut_appl_inst.ASS_RANK,
1052 x_SECONDARY_ASS_RANK => l_get_fut_appl_inst.SECONDARY_ASS_RANK,
1053 x_intr_accept_advice_num => l_get_fut_appl_inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1054 x_ASS_TRACKING_ID => l_get_fut_appl_inst.ASS_TRACKING_ID,
1055 x_FEE_CAT => l_get_fut_appl_inst.FEE_CAT,
1056 x_HECS_PAYMENT_OPTION => l_get_fut_appl_inst.HECS_PAYMENT_OPTION,
1057 x_EXPECTED_COMPLETION_YR => l_get_fut_appl_inst.EXPECTED_COMPLETION_YR,
1058 x_EXPECTED_COMPLETION_PERD => l_get_fut_appl_inst.EXPECTED_COMPLETION_PERD,
1059 x_CORRESPONDENCE_CAT => l_get_fut_appl_inst.CORRESPONDENCE_CAT,
1060 x_ENROLMENT_CAT => l_get_fut_appl_inst.ENROLMENT_CAT,
1061 x_FUNDING_SOURCE => l_get_fut_appl_inst.FUNDING_SOURCE,
1062 x_APPLICANT_ACPTNCE_CNDTN => l_get_fut_appl_inst.APPLICANT_ACPTNCE_CNDTN,
1063 x_CNDTNL_OFFER_CNDTN => l_get_fut_appl_inst.CNDTNL_OFFER_CNDTN,
1064 X_MODE => 'S',
1065 X_SS_APPLICATION_ID => l_get_fut_appl_inst.SS_APPLICATION_ID,
1066 X_SS_PWD => l_get_fut_appl_inst.SS_PWD,
1067 X_AUTHORIZED_DT => l_get_fut_appl_inst.AUTHORIZED_DT,
1068 X_AUTHORIZING_PERS_ID => l_get_fut_appl_inst.AUTHORIZING_PERS_ID,
1069 x_entry_status => l_get_fut_appl_inst.entry_status,
1070 x_entry_level => l_get_fut_appl_inst.entry_level,
1071 x_sch_apl_to_id => l_get_fut_appl_inst.sch_apl_to_id,
1072 x_idx_calc_date => l_get_fut_appl_inst.idx_calc_date,
1073 x_waitlist_status => l_get_fut_appl_inst.waitlist_status,
1074 x_ATTRIBUTE21 => l_get_fut_appl_inst.ATTRIBUTE21,
1075 x_ATTRIBUTE22 => l_get_fut_appl_inst.ATTRIBUTE22,
1076 x_ATTRIBUTE23 => l_get_fut_appl_inst.ATTRIBUTE23,
1077 x_ATTRIBUTE24 => l_get_fut_appl_inst.ATTRIBUTE24,
1078 x_ATTRIBUTE25 => l_get_fut_appl_inst.ATTRIBUTE25,
1082 x_ATTRIBUTE29 => l_get_fut_appl_inst.ATTRIBUTE29,
1079 x_ATTRIBUTE26 => l_get_fut_appl_inst.ATTRIBUTE26,
1080 x_ATTRIBUTE27 => l_get_fut_appl_inst.ATTRIBUTE27,
1081 x_ATTRIBUTE28 => l_get_fut_appl_inst.ATTRIBUTE28,
1083 x_ATTRIBUTE30 => l_get_fut_appl_inst.ATTRIBUTE30,
1084 x_ATTRIBUTE31 => l_get_fut_appl_inst.ATTRIBUTE31,
1085 x_ATTRIBUTE32 => l_get_fut_appl_inst.ATTRIBUTE32,
1086 x_ATTRIBUTE33 => l_get_fut_appl_inst.ATTRIBUTE33,
1087 x_ATTRIBUTE34 => l_get_fut_appl_inst.ATTRIBUTE34,
1088 x_ATTRIBUTE35 => l_get_fut_appl_inst.ATTRIBUTE35,
1089 x_ATTRIBUTE36 => l_get_fut_appl_inst.ATTRIBUTE36,
1090 x_ATTRIBUTE37 => l_get_fut_appl_inst.ATTRIBUTE37,
1091 x_ATTRIBUTE38 => l_get_fut_appl_inst.ATTRIBUTE38,
1092 x_ATTRIBUTE39 => l_get_fut_appl_inst.ATTRIBUTE39,
1093 x_ATTRIBUTE40 => l_get_fut_appl_inst.ATTRIBUTE40,
1094 x_fut_acad_cal_type => NULL,
1095 x_fut_acad_ci_sequence_number => NULL,
1096 x_fut_adm_cal_type => NULL,
1097 x_fut_adm_ci_sequence_number => NULL,
1098 x_prev_term_adm_appl_number => l_get_fut_appl_inst.PREVIOUS_TERM_ADM_APPL_NUMBER,
1099 x_prev_term_sequence_number => l_get_fut_appl_inst.PREVIOUS_TERM_SEQUENCE_NUMBER,
1100 x_fut_term_adm_appl_number => l_get_fut_appl_inst.FUTURE_TERM_ADM_APPL_NUMBER,
1101 x_fut_term_sequence_number => l_get_fut_appl_inst.FUTURE_TERM_SEQUENCE_NUMBER,
1102 x_def_acad_cal_type => l_get_fut_appl_inst.def_acad_cal_type,
1103 x_def_acad_ci_sequence_num => l_get_fut_appl_inst.def_acad_ci_sequence_num,
1104 x_def_prev_term_adm_appl_num => l_get_fut_appl_inst.def_prev_term_adm_appl_num,
1105 x_def_prev_appl_sequence_num => l_get_fut_appl_inst.def_prev_appl_sequence_num,
1106 x_def_term_adm_appl_num => l_get_fut_appl_inst.def_term_adm_appl_num,
1107 x_def_appl_sequence_num => l_get_fut_appl_inst.def_appl_sequence_num,
1108 x_appl_inst_status => l_get_fut_appl_inst.appl_inst_status,
1109 x_ais_reason => l_get_fut_appl_inst.ais_reason,
1110 x_decline_ofr_reason => l_get_fut_appl_inst.decline_ofr_reason);
1111
1112 FND_FILE.PUT_LINE(FND_FILE.LOG,' Cancelled Reconsideration Request.' );
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116
1117 ROLLBACK TO c_update_inst;
1118
1119 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cancel Reconsideration Failed: ' || FND_MESSAGE.GET);
1120
1121 l_record_errored := 'Y';
1122
1123 END;
1124
1125 END LOOP;
1126
1127 END IF;
1128
1129 ELSIF (P_person_id_group IS NOT NULL) THEN
1130
1131
1132 -- if l_acad_sequence_number is NULL (ie, calendar details are not entered) then
1133 -- it is assigned a value of -1, if we keep it NULL then the value cannot be used while
1134 -- opening the REF CURSOR c_dyn_pig_check.
1135
1136 IF l_acad_sequence_number IS NULL THEN
1137 l_acad_sequence_number := -1 ;
1138 END IF;
1139
1140 -- if l_adm_sequence_number is NULL (ie, calendar details are not entered) then
1141 -- it is assigned a value of -1, if we keep it NULL then the value cannot be used while
1142 -- opening the REF CURSOR c_dyn_pig_check.
1143
1144 IF l_adm_sequence_number IS NULL THEN
1145 l_adm_sequence_number := -1;
1146 END IF;
1147
1148 IF p_recon_future = 'Y' OR p_recon_no_future = 'Y' THEN
1149
1150 IF p_recon_future IS NULL THEN
1151 l_recon_future := 'N';
1152 ELSE
1153 l_recon_future := p_recon_future;
1154 END IF;
1155
1156 IF p_recon_no_future IS NULL THEN
1157 l_recon_no_future := 'N';
1158 ELSE
1159 l_recon_no_future := p_recon_no_future;
1160 END IF;
1161
1162 l_sql_stmt1 := l_sql_stmt1 || '(' || lv_sql_stmt || ')' || l_sql_stmt2;
1163 -- opening the REF CURSOR c_dyn_pig_check that will pick up application instance details based on the
1164 -- value of the parameters entered.
1165 IF lv_group_type = 'STATIC' THEN
1166
1167 OPEN c_dyn_pig_check FOR l_sql_stmt1 USING p_person_id_group, l_acad_cal_type, l_acad_sequence_number,l_acad_sequence_number,
1168 l_adm_cal_type, l_adm_sequence_number,l_adm_sequence_number, l_admission_cat, l_s_adm_process_typ, l_recon_no_future, l_recon_future;
1169
1170 FETCH c_dyn_pig_check BULK COLLECT INTO c_dyn_pig_check_table_rec;
1171 CLOSE c_dyn_pig_check;
1172
1173 ELSIF lv_group_type = 'DYNAMIC' THEN
1174
1178 FETCH c_dyn_pig_check BULK COLLECT INTO c_dyn_pig_check_table_rec;
1175 OPEN c_dyn_pig_check FOR l_sql_stmt1 USING l_acad_cal_type, l_acad_sequence_number,l_acad_sequence_number,
1176 l_adm_cal_type, l_adm_sequence_number,l_adm_sequence_number, l_admission_cat, l_s_adm_process_typ, l_recon_no_future, l_recon_future;
1177
1179 CLOSE c_dyn_pig_check;
1180
1181 END IF;
1182
1183
1184 IF c_dyn_pig_check_table_rec.COUNT >0 THEN
1185 FOR l_dyn_pig_check_table_count IN c_dyn_pig_check_table_rec.FIRST..c_dyn_pig_check_table_rec.LAST LOOP
1186
1187 c_dyn_pig_check_rec := c_dyn_pig_check_table_rec(l_dyn_pig_check_table_count);
1188
1189
1190 l_processing_ind := 'Y';
1191
1192 OPEN c_get_application_id(c_dyn_pig_check_rec.person_id,c_dyn_pig_check_rec.admission_appl_number);
1193 FETCH c_get_application_id INTO l_get_application_id;
1194 CLOSE c_get_application_id;
1195
1196 SAVEPOINT c_create_prog;
1197
1198 DECLARE
1199
1200 l_max_msg_count NUMBER;
1201 l_msg_data VARCHAR2(2000);
1202 l_msg_index_out NUMBER;
1203 l_app_name VARCHAR2(2000);
1204 v_message_name VARCHAR2(2000);
1205
1206 BEGIN
1207 igs_ad_ps_appl_pkg.update_row (
1208 x_rowid => c_dyn_pig_check_rec.row_id,
1209 x_person_id => c_dyn_pig_check_rec.person_id,
1210 x_admission_appl_number => c_dyn_pig_check_rec.admission_appl_number,
1211 x_nominated_course_cd => c_dyn_pig_check_rec.nominated_course_cd,
1212 x_transfer_course_cd => c_dyn_pig_check_rec.transfer_course_cd,
1213 x_basis_for_admission_type => c_dyn_pig_check_rec.basis_for_admission_type,
1214 x_admission_cd => c_dyn_pig_check_rec.admission_cd,
1215 x_course_rank_set => c_dyn_pig_check_rec.course_rank_set,
1216 x_course_rank_schedule => c_dyn_pig_check_rec.course_rank_schedule,
1217 x_req_for_reconsideration_ind => 'N',
1218 x_req_for_adv_standing_ind => c_dyn_pig_check_rec.req_for_adv_standing_ind ,
1219 x_mode => 'R' ) ;
1220
1221 FOR l_get_inst_dtls_rec IN c_get_inst_dtls(c_dyn_pig_check_rec.person_id,
1222 c_dyn_pig_check_rec.admission_appl_number,
1223 c_dyn_pig_check_rec.nominated_course_cd) LOOP
1224
1225 FND_FILE.PUT_LINE(FND_FILE.LOG,' Person Number: ' || RPAD( get_person_number(l_get_ad_ps_appl_rec.person_id),15,' ') || ' Application ID: ' ||
1226 RPAD(l_get_application_id,15,' ') || ' Program: ' || RPAD(l_get_ad_ps_appl_rec.nominated_course_cd,6,' ')
1227 || 'Sequence Number: ' || RPAD(l_get_inst_dtls_rec.sequence_number,6,' '));
1228
1229 l_record_failed := 'N';
1230
1231 OPEN c_get_hist_dt(l_get_inst_dtls_rec.person_id,
1232 l_get_inst_dtls_rec.admission_appl_number,
1233 l_get_inst_dtls_rec.nominated_course_cd,
1234 l_get_inst_dtls_rec.sequence_number);
1235 FETCH c_get_hist_dt INTO l_get_hist_dt;
1236 CLOSE c_get_hist_dt;
1237
1238 OPEN c_get_pending_hist(l_get_inst_dtls_rec.person_id,
1239 l_get_inst_dtls_rec.admission_appl_number,
1240 l_get_inst_dtls_rec.nominated_course_cd,
1241 l_get_inst_dtls_rec.sequence_number,
1242 l_get_hist_dt);
1243 FETCH c_get_pending_hist INTO l_get_pending_hist_rec;
1244
1245 IF c_get_pending_hist%FOUND THEN
1246
1247 igs_ad_cancel_reconsider.g_cancel_recons_on := 'Y';
1248
1249 igs_ad_ps_appl_inst_pkg.update_row (
1250 X_ROWID => l_get_inst_dtls_rec.ROWID,
1251 x_PERSON_ID => l_get_inst_dtls_rec.PERSON_ID,
1252 x_ADMISSION_APPL_NUMBER => l_get_inst_dtls_rec.ADMISSION_APPL_NUMBER,
1253 x_NOMINATED_COURSE_CD => l_get_inst_dtls_rec.NOMINATED_COURSE_CD,
1254 x_SEQUENCE_NUMBER => l_get_inst_dtls_rec.SEQUENCE_NUMBER,
1255 x_PREDICTED_GPA => l_get_inst_dtls_rec.PREDICTED_GPA,
1256 x_ACADEMIC_INDEX => l_get_inst_dtls_rec.ACADEMIC_INDEX,
1257 x_ADM_CAL_TYPE => l_get_inst_dtls_rec.ADM_CAL_TYPE,
1258 x_APP_FILE_LOCATION => l_get_inst_dtls_rec.APP_FILE_LOCATION,
1259 x_ADM_CI_SEQUENCE_NUMBER => l_get_inst_dtls_rec.ADM_CI_SEQUENCE_NUMBER,
1260 x_COURSE_CD => l_get_inst_dtls_rec.COURSE_CD,
1261 x_APP_SOURCE_ID => l_get_inst_dtls_rec.APP_SOURCE_ID,
1262 x_CRV_VERSION_NUMBER => l_get_inst_dtls_rec.CRV_VERSION_NUMBER,
1263 x_WAITLIST_RANK => l_get_pending_hist_rec.WAITLIST_RANK,
1264 x_LOCATION_CD => l_get_inst_dtls_rec.LOCATION_CD,
1268 x_ATTENDANCE_TYPE => l_get_inst_dtls_rec.ATTENDANCE_TYPE,
1265 x_ATTENT_OTHER_INST_CD => l_get_inst_dtls_rec.ATTENT_OTHER_INST_CD,
1266 x_ATTENDANCE_MODE => l_get_inst_dtls_rec.ATTENDANCE_MODE,
1267 x_EDU_GOAL_PRIOR_ENROLL_ID => l_get_inst_dtls_rec.EDU_GOAL_PRIOR_ENROLL_ID,
1269 x_DECISION_MAKE_ID => l_get_pending_hist_rec.DECISION_MAKE_ID,
1270 x_UNIT_SET_CD => l_get_inst_dtls_rec.UNIT_SET_CD,
1271 x_DECISION_DATE => l_get_pending_hist_rec.DECISION_DATE,
1272 x_ATTRIBUTE_CATEGORY => l_get_inst_dtls_rec.ATTRIBUTE_CATEGORY,
1273 x_ATTRIBUTE1 => l_get_inst_dtls_rec.ATTRIBUTE1,
1274 x_ATTRIBUTE2 => l_get_inst_dtls_rec.ATTRIBUTE2,
1275 x_ATTRIBUTE3 => l_get_inst_dtls_rec.ATTRIBUTE3,
1276 x_ATTRIBUTE4 => l_get_inst_dtls_rec.ATTRIBUTE4,
1277 x_ATTRIBUTE5 => l_get_inst_dtls_rec.ATTRIBUTE5,
1278 x_ATTRIBUTE6 => l_get_inst_dtls_rec.ATTRIBUTE6,
1279 x_ATTRIBUTE7 => l_get_inst_dtls_rec.ATTRIBUTE7,
1280 x_ATTRIBUTE8 => l_get_inst_dtls_rec.ATTRIBUTE8,
1281 x_ATTRIBUTE9 => l_get_inst_dtls_rec.ATTRIBUTE9,
1282 x_ATTRIBUTE10 => l_get_inst_dtls_rec.ATTRIBUTE10,
1283 x_ATTRIBUTE11 => l_get_inst_dtls_rec.ATTRIBUTE11,
1284 x_ATTRIBUTE12 => l_get_inst_dtls_rec.ATTRIBUTE12,
1285 x_ATTRIBUTE13 => l_get_inst_dtls_rec.ATTRIBUTE13,
1286 x_ATTRIBUTE14 => l_get_inst_dtls_rec.ATTRIBUTE14,
1287 x_ATTRIBUTE15 => l_get_inst_dtls_rec.ATTRIBUTE15,
1288 x_ATTRIBUTE16 => l_get_inst_dtls_rec.ATTRIBUTE16,
1289 x_ATTRIBUTE17 => l_get_inst_dtls_rec.ATTRIBUTE17,
1290 x_ATTRIBUTE18 => l_get_inst_dtls_rec.ATTRIBUTE18,
1291 x_ATTRIBUTE19 => l_get_inst_dtls_rec.ATTRIBUTE19,
1292 x_ATTRIBUTE20 => l_get_inst_dtls_rec.ATTRIBUTE20,
1293 x_DECISION_REASON_ID => l_get_pending_hist_rec.DECISION_REASON_ID,
1294 x_US_VERSION_NUMBER => l_get_inst_dtls_rec.US_VERSION_NUMBER,
1295 x_DECISION_NOTES => l_get_inst_dtls_rec.DECISION_NOTES,
1296 x_PENDING_REASON_ID => l_get_pending_hist_rec.PENDING_REASON_ID,
1297 x_PREFERENCE_NUMBER => l_get_inst_dtls_rec.PREFERENCE_NUMBER,
1298 x_ADM_DOC_STATUS => l_get_inst_dtls_rec.ADM_DOC_STATUS,
1299 x_ADM_ENTRY_QUAL_STATUS => l_get_inst_dtls_rec.ADM_ENTRY_QUAL_STATUS,
1300 x_DEFICIENCY_IN_PREP => l_get_inst_dtls_rec.DEFICIENCY_IN_PREP,
1301 x_LATE_ADM_FEE_STATUS => l_get_inst_dtls_rec.LATE_ADM_FEE_STATUS,
1302 x_SPL_CONSIDER_COMMENTS => l_get_inst_dtls_rec.SPL_CONSIDER_COMMENTS,
1303 x_APPLY_FOR_FINAID => l_get_inst_dtls_rec.APPLY_FOR_FINAID,
1304 x_FINAID_APPLY_DATE => l_get_inst_dtls_rec.FINAID_APPLY_DATE,
1305 x_ADM_OUTCOME_STATUS => l_get_pending_hist_rec.ADM_OUTCOME_STATUS,
1306 x_adm_otcm_stat_auth_per_id => l_get_inst_dtls_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1307 x_ADM_OUTCOME_STATUS_AUTH_DT => l_get_inst_dtls_rec.ADM_OUTCOME_STATUS_AUTH_DT,
1308 x_ADM_OUTCOME_STATUS_REASON => l_get_inst_dtls_rec.ADM_OUTCOME_STATUS_REASON,
1309 x_OFFER_DT => l_get_pending_hist_rec.OFFER_DT,
1310 x_OFFER_RESPONSE_DT => l_get_pending_hist_rec.OFFER_RESPONSE_DT,
1311 x_PRPSD_COMMENCEMENT_DT => l_get_pending_hist_rec.PRPSD_COMMENCEMENT_DT,
1312 x_ADM_CNDTNL_OFFER_STATUS => NVL(l_get_pending_hist_rec.ADM_CNDTNL_OFFER_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_ACOS('NOT-APPLIC')),
1313 x_CNDTNL_OFFER_SATISFIED_DT => l_get_pending_hist_rec.CNDTNL_OFFER_SATISFIED_DT,
1314 x_cndnl_ofr_must_be_stsfd_ind => l_get_pending_hist_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND,
1315 x_ADM_OFFER_RESP_STATUS => NVL(l_get_pending_hist_rec.ADM_OFFER_RESP_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_AORS('NOT-APPLIC')),
1316 x_ACTUAL_RESPONSE_DT => l_get_pending_hist_rec.ACTUAL_RESPONSE_DT,
1317 x_ADM_OFFER_DFRMNT_STATUS => NVL(l_get_pending_hist_rec.ADM_OFFER_DFRMNT_STATUS,IGS_AD_GEN_009.ADMP_GET_SYS_AODS('NOT-APPLIC')),
1321 x_ASS_RANK => l_get_inst_dtls_rec.ASS_RANK,
1318 x_DEFERRED_ADM_CAL_TYPE => l_get_pending_hist_rec.DEFERRED_ADM_CAL_TYPE,
1319 x_DEFERRED_ADM_CI_SEQUENCE_NUM => l_get_pending_hist_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
1320 x_DEFERRED_TRACKING_ID => l_get_inst_dtls_rec.DEFERRED_TRACKING_ID,
1322 x_SECONDARY_ASS_RANK => l_get_inst_dtls_rec.SECONDARY_ASS_RANK,
1323 x_intr_accept_advice_num => l_get_inst_dtls_rec.intrntnl_acceptance_advice_num,
1324 x_ASS_TRACKING_ID => l_get_inst_dtls_rec.ASS_TRACKING_ID,
1325 x_FEE_CAT => l_get_inst_dtls_rec.FEE_CAT,
1326 x_HECS_PAYMENT_OPTION => l_get_inst_dtls_rec.HECS_PAYMENT_OPTION,
1327 x_EXPECTED_COMPLETION_YR => l_get_inst_dtls_rec.EXPECTED_COMPLETION_YR,
1328 x_EXPECTED_COMPLETION_PERD => l_get_inst_dtls_rec.EXPECTED_COMPLETION_PERD,
1329 x_CORRESPONDENCE_CAT => l_get_inst_dtls_rec.CORRESPONDENCE_CAT,
1330 x_ENROLMENT_CAT => l_get_inst_dtls_rec.ENROLMENT_CAT,
1331 x_FUNDING_SOURCE => l_get_inst_dtls_rec.FUNDING_SOURCE,
1332 x_APPLICANT_ACPTNCE_CNDTN => l_get_pending_hist_rec.APPLICANT_ACPTNCE_CNDTN,
1333 x_CNDTNL_OFFER_CNDTN => l_get_pending_hist_rec.CNDTNL_OFFER_CNDTN,
1334 X_MODE => 'S',
1335 X_SS_APPLICATION_ID => l_get_inst_dtls_rec.SS_APPLICATION_ID,
1336 X_SS_PWD => l_get_inst_dtls_rec.SS_PWD,
1337 X_AUTHORIZED_DT => l_get_inst_dtls_rec.AUTHORIZED_DT,
1338 X_AUTHORIZING_PERS_ID => l_get_inst_dtls_rec.AUTHORIZING_PERS_ID,
1339 x_entry_status => l_get_inst_dtls_rec.entry_status,
1340 x_entry_level => l_get_inst_dtls_rec.entry_level,
1341 x_sch_apl_to_id => l_get_inst_dtls_rec.sch_apl_to_id,
1342 x_idx_calc_date => l_get_inst_dtls_rec.idx_calc_date,
1343 x_waitlist_status => l_get_pending_hist_rec.waitlist_status,
1344 x_ATTRIBUTE21 => l_get_inst_dtls_rec.ATTRIBUTE21,
1345 x_ATTRIBUTE22 => l_get_inst_dtls_rec.ATTRIBUTE22,
1346 x_ATTRIBUTE23 => l_get_inst_dtls_rec.ATTRIBUTE23,
1347 x_ATTRIBUTE24 => l_get_inst_dtls_rec.ATTRIBUTE24,
1348 x_ATTRIBUTE25 => l_get_inst_dtls_rec.ATTRIBUTE25,
1349 x_ATTRIBUTE26 => l_get_inst_dtls_rec.ATTRIBUTE26,
1350 x_ATTRIBUTE27 => l_get_inst_dtls_rec.ATTRIBUTE27,
1351 x_ATTRIBUTE28 => l_get_inst_dtls_rec.ATTRIBUTE28,
1352 x_ATTRIBUTE29 => l_get_inst_dtls_rec.ATTRIBUTE29,
1353 x_ATTRIBUTE30 => l_get_inst_dtls_rec.ATTRIBUTE30,
1354 x_ATTRIBUTE31 => l_get_inst_dtls_rec.ATTRIBUTE31,
1355 x_ATTRIBUTE32 => l_get_inst_dtls_rec.ATTRIBUTE32,
1356 x_ATTRIBUTE33 => l_get_inst_dtls_rec.ATTRIBUTE33,
1357 x_ATTRIBUTE34 => l_get_inst_dtls_rec.ATTRIBUTE34,
1358 x_ATTRIBUTE35 => l_get_inst_dtls_rec.ATTRIBUTE35,
1359 x_ATTRIBUTE36 => l_get_inst_dtls_rec.ATTRIBUTE36,
1360 x_ATTRIBUTE37 => l_get_inst_dtls_rec.ATTRIBUTE37,
1361 x_ATTRIBUTE38 => l_get_inst_dtls_rec.ATTRIBUTE38,
1362 x_ATTRIBUTE39 => l_get_inst_dtls_rec.ATTRIBUTE39,
1363 x_ATTRIBUTE40 => l_get_inst_dtls_rec.ATTRIBUTE40,
1364 x_fut_acad_cal_type => l_get_pending_hist_rec.future_acad_cal_type,
1365 x_fut_acad_ci_sequence_number => l_get_pending_hist_rec.future_acad_ci_sequence_num,
1366 x_fut_adm_cal_type => l_get_pending_hist_rec.future_adm_cal_type,
1367 x_fut_adm_ci_sequence_number => l_get_pending_hist_rec.future_adm_ci_sequence_num,
1368 x_prev_term_adm_appl_number => l_get_inst_dtls_rec.PREVIOUS_TERM_ADM_APPL_NUMBER,
1369 x_prev_term_sequence_number => l_get_inst_dtls_rec.PREVIOUS_TERM_SEQUENCE_NUMBER,
1370 x_fut_term_adm_appl_number => l_get_inst_dtls_rec.future_term_adm_appl_number,
1371 x_fut_term_sequence_number => l_get_inst_dtls_rec.future_term_sequence_number,
1375 x_def_prev_appl_sequence_num => l_get_inst_dtls_rec.def_prev_appl_sequence_num,
1372 x_def_acad_cal_type => l_get_pending_hist_rec.def_acad_cal_type,
1373 x_def_acad_ci_sequence_num => l_get_pending_hist_rec.def_acad_ci_sequence_num,
1374 x_def_prev_term_adm_appl_num => l_get_inst_dtls_rec.def_prev_term_adm_appl_num,
1376 x_def_term_adm_appl_num => l_get_inst_dtls_rec.def_term_adm_appl_num,
1377 x_def_appl_sequence_num => l_get_inst_dtls_rec.def_appl_sequence_num,
1378 x_appl_inst_status => l_get_inst_dtls_rec.appl_inst_status,
1379 x_ais_reason => l_get_inst_dtls_rec.ais_reason,
1380 x_decline_ofr_reason => l_get_pending_hist_rec.decline_ofr_reason);
1381
1382 igs_ad_cancel_reconsider.g_cancel_recons_on := 'N';
1383
1384 igs_ad_wf_001.wf_raise_event ( p_person_id => l_get_inst_dtls_rec.Person_Id,
1385 p_raised_for => 'AOD',
1386 p_admission_appl_number => l_get_inst_dtls_rec.Admission_Appl_Number,
1387 p_nominated_course_cd => l_get_inst_dtls_rec.Nominated_Course_cd,
1388 p_sequence_number => l_get_inst_dtls_rec.Sequence_Number,
1389 p_old_outcome_status => l_get_inst_dtls_rec.adm_outcome_status,
1390 p_new_outcome_status => l_get_pending_hist_rec.adm_outcome_status
1391 );
1392
1393 OPEN c_adm_cat(l_get_inst_dtls_rec.person_id,l_get_inst_dtls_rec.admission_appl_number);
1394 FETCH c_adm_cat INTO l_adm_cat;
1395 CLOSE c_adm_cat;
1396
1397
1398 OPEN c_apcs(l_adm_cat.admission_cat, l_adm_cat.s_admission_process_type);
1399 FETCH c_apcs INTO l_apcs;
1400 CLOSE c_apcs;
1401
1402 IF NVL(igs_ad_gen_008.admp_get_saors(l_get_pending_hist_rec.ADM_OFFER_RESP_STATUS),'NULL') = 'ACCEPTED' THEN
1403 IF igs_ad_upd_initialise.perform_pre_enrol(l_get_inst_dtls_rec.Person_Id,
1404 l_get_inst_dtls_rec.Admission_Appl_Number,
1405 l_get_inst_dtls_rec.Nominated_Course_cd,
1406 l_get_inst_dtls_rec.Sequence_Number,
1407 'Y', -- Confirm course indicator.
1408 'Y', -- Perform eligibility check indicator.
1409 v_message_name) = FALSE THEN
1410 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cancel Reconsideration Failed: ' || v_message_name);
1411 l_record_failed := 'Y';
1412 ROLLBACK TO c_create_prog;
1413 END IF;
1414 END IF;
1415
1416
1417 IF (igs_ad_gen_008.admp_get_saos(l_get_pending_hist_rec.ADM_OUTCOME_STATUS)) IN ('COND-OFFER', 'OFFER') AND l_apcs = 'Y' THEN
1418 IF igs_ad_upd_initialise.perform_pre_enrol(l_get_inst_dtls_rec.Person_Id,
1419 l_get_inst_dtls_rec.Admission_Appl_Number,
1420 l_get_inst_dtls_rec.Nominated_Course_cd,
1421 l_get_inst_dtls_rec.Sequence_Number,
1422 'N', -- Confirm course indicator.
1423 'N', -- Perform eligibility check indicator.
1424 v_message_name) = FALSE THEN
1425 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cancel Reconsideration Failed: ' || v_message_name);
1426 l_record_failed := 'Y';
1427 ROLLBACK TO c_create_prog;
1428 END IF;
1429 END IF;
1430
1431 END IF;
1432 CLOSE c_get_pending_hist;
1433
1434 END LOOP;
1435 IF l_record_failed = 'N' THEN
1436 igs_ad_gen_002.ins_dummy_pend_hist_rec ( c_dyn_pig_check_rec.person_id,
1437 c_dyn_pig_check_rec.admission_appl_number,
1438 c_dyn_pig_check_rec.nominated_course_cd
1439 );
1440 FND_FILE.PUT_LINE(FND_FILE.LOG,' Cancelled Reconsideration Request.' );
1441 END IF;
1442
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445
1446 IF NVL(igs_ad_cancel_reconsider.g_cancel_recons_on,'N') = 'Y' THEN
1447 igs_ad_cancel_reconsider.g_cancel_recons_on := 'N';
1448 END IF;
1449
1450 IF c_get_pending_hist%ISOPEN THEN
1451 CLOSE c_get_pending_hist;
1452 END IF;
1453 ROLLBACK TO c_create_prog;
1454
1455 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1456
1457 l_record_errored := 'Y';
1458
1459 END;
1463
1460 END LOOP;
1461
1462 END IF;
1464 END IF;
1465
1466 IF p_pend_future = 'Y' THEN
1467
1468 l_sql_stmt4 := 'AND
1469 Aa.acad_cal_type = NVL ( :1 , aa.acad_cal_type) AND
1470 Aa.acad_ci_sequence_number = DECODE ( :2, -1,aa.acad_ci_sequence_number, :2 ) AND
1471 Aa.adm_cal_type = NVL (:3 , aa.adm_cal_type) AND
1472 Aa.adm_ci_sequence_number = DECODE ( :4, -1,aa.adm_ci_sequence_number, :4 ) AND
1473 Aa.admission_cat = NVL ( :5 , aa.admission_cat) AND
1474 Aa.s_admission_process_type = NVL ( :6 , aa.s_admission_process_type) AND
1475 Apai.adm_outcome_status = aos.adm_outcome_status AND
1476 Aos.s_adm_outcome_status = ''PENDING'' AND
1477 NVL(apai.appl_inst_status,''RECEIVED'') NOT IN (SELECT adm_appl_status FROM igs_ad_appl_stat WHERE S_ADM_APPL_STATUS = ''WITHDRAWN'') AND
1478 Psapl.req_for_reconsideration_ind <> ''Y'' AND
1479 Apai.person_id = psapl.person_id AND
1480 Apai.admission_appl_number = psapl.admission_appl_number AND
1481 Apai.nominated_course_cd = psapl.nominated_course_cd AND
1482 apai.person_id = aa.person_id AND
1483 apai.admission_appl_number = aa.admission_appl_number AND
1484 Apai.future_acad_cal_type IS NOT NULL AND
1485 Apai.future_acad_ci_sequence_number IS NOT NULL AND
1486 Apai.future_adm_cal_type IS NOT NULL AND
1487 Apai.future_adm_ci_sequence_number IS NOT NULL';
1488
1489 l_sql_stmt3 := l_sql_stmt3 || '(' || lv_sql_stmt || ')' || l_sql_stmt4;
1490
1491 IF lv_group_type = 'STATIC' THEN
1492
1493 OPEN c_pend_fut FOR l_sql_stmt3 USING p_person_id_group, l_acad_cal_type, l_acad_sequence_number,l_acad_sequence_number, l_adm_cal_type, l_adm_sequence_number,l_adm_sequence_number, l_admission_cat, l_s_adm_process_typ;
1494
1495
1496 FETCH c_pend_fut BULK COLLECT INTO c_pend_fut_table_rec;
1497 CLOSE c_pend_fut;
1498
1499 ELSIF lv_group_type = 'DYNAMIC' THEN
1500
1501 OPEN c_pend_fut FOR l_sql_stmt3 USING l_acad_cal_type, l_acad_sequence_number,l_acad_sequence_number, l_adm_cal_type, l_adm_sequence_number,l_adm_sequence_number, l_admission_cat, l_s_adm_process_typ;
1502
1503
1504 FETCH c_pend_fut BULK COLLECT INTO c_pend_fut_table_rec;
1505 CLOSE c_pend_fut;
1506
1507 END IF;
1508
1509 IF c_pend_fut_table_rec.COUNT>0 THEN
1510 FOR l_pend_fut_table_count IN c_pend_fut_table_rec.FIRST..c_pend_fut_table_rec.LAST LOOP
1511
1512 c_pend_fut_rec := c_pend_fut_table_rec(l_pend_fut_table_count);
1513
1514 l_processing_ind := 'Y';
1515
1516 SAVEPOINT c_update_inst;
1517
1518 DECLARE
1519
1520 l_max_msg_count NUMBER;
1521 l_msg_data VARCHAR2(2000);
1522 l_msg_index_out NUMBER;
1523 l_app_name VARCHAR2(2000);
1524 v_message_name VARCHAR2(2000);
1525
1526
1527 BEGIN
1528 /* Begin Apadegal - 4747281 */
1529 OPEN c_get_application_id(c_pend_fut_rec.PERSON_ID,c_pend_fut_rec.ADMISSION_APPL_NUMBER);
1530 FETCH c_get_application_id INTO l_get_application_id;
1531 CLOSE c_get_application_id;
1532
1533 FND_FILE.PUT_LINE(FND_FILE.LOG,' Person Number: ' || RPAD(get_person_number(c_pend_fut_rec.PERSON_ID),15,' ') || ' Application ID: ' ||
1534 RPAD(l_get_application_id,15,' ') || ' Program: ' || RPAD(c_pend_fut_rec.NOMINATED_COURSE_CD,6,' ')
1535 || 'Sequence Number: ' || RPAD(c_pend_fut_rec.SEQUENCE_NUMBER,6,' '));
1536 l_get_application_id := NULL;
1537 /* End Apadegal - 4747281 */
1538 igs_ad_ps_appl_inst_pkg.update_row (
1539 X_ROWID => c_pend_fut_rec.ROWID,
1540 x_PERSON_ID => c_pend_fut_rec.PERSON_ID,
1541 x_ADMISSION_APPL_NUMBER => c_pend_fut_rec.ADMISSION_APPL_NUMBER,
1542 x_NOMINATED_COURSE_CD => c_pend_fut_rec.NOMINATED_COURSE_CD,
1543 x_SEQUENCE_NUMBER => c_pend_fut_rec.SEQUENCE_NUMBER,
1544 x_PREDICTED_GPA => c_pend_fut_rec.PREDICTED_GPA,
1545 x_ACADEMIC_INDEX => c_pend_fut_rec.ACADEMIC_INDEX,
1546 x_ADM_CAL_TYPE => c_pend_fut_rec.ADM_CAL_TYPE,
1547 x_APP_FILE_LOCATION => c_pend_fut_rec.APP_FILE_LOCATION,
1548 x_ADM_CI_SEQUENCE_NUMBER => c_pend_fut_rec.ADM_CI_SEQUENCE_NUMBER,
1549 x_COURSE_CD => c_pend_fut_rec.COURSE_CD,
1550 x_APP_SOURCE_ID => c_pend_fut_rec.APP_SOURCE_ID,
1551 x_CRV_VERSION_NUMBER => c_pend_fut_rec.CRV_VERSION_NUMBER,
1552 x_WAITLIST_RANK => c_pend_fut_rec.WAITLIST_RANK,
1553 x_LOCATION_CD => c_pend_fut_rec.LOCATION_CD,
1554 x_ATTENT_OTHER_INST_CD => c_pend_fut_rec.ATTENT_OTHER_INST_CD,
1555 x_ATTENDANCE_MODE => c_pend_fut_rec.ATTENDANCE_MODE,
1559 x_UNIT_SET_CD => c_pend_fut_rec.UNIT_SET_CD,
1556 x_EDU_GOAL_PRIOR_ENROLL_ID => c_pend_fut_rec.EDU_GOAL_PRIOR_ENROLL_ID,
1557 x_ATTENDANCE_TYPE => c_pend_fut_rec.ATTENDANCE_TYPE,
1558 x_DECISION_MAKE_ID => c_pend_fut_rec.DECISION_MAKE_ID,
1560 x_DECISION_DATE => c_pend_fut_rec.DECISION_DATE,
1561 x_ATTRIBUTE_CATEGORY => c_pend_fut_rec.ATTRIBUTE_CATEGORY,
1562 x_ATTRIBUTE1 => c_pend_fut_rec.ATTRIBUTE1,
1563 x_ATTRIBUTE2 => c_pend_fut_rec.ATTRIBUTE2,
1564 x_ATTRIBUTE3 => c_pend_fut_rec.ATTRIBUTE3,
1565 x_ATTRIBUTE4 => c_pend_fut_rec.ATTRIBUTE4,
1566 x_ATTRIBUTE5 => c_pend_fut_rec.ATTRIBUTE5,
1567 x_ATTRIBUTE6 => c_pend_fut_rec.ATTRIBUTE6,
1568 x_ATTRIBUTE7 => c_pend_fut_rec.ATTRIBUTE7,
1569 x_ATTRIBUTE8 => c_pend_fut_rec.ATTRIBUTE8,
1570 x_ATTRIBUTE9 => c_pend_fut_rec.ATTRIBUTE9,
1571 x_ATTRIBUTE10 => c_pend_fut_rec.ATTRIBUTE10,
1572 x_ATTRIBUTE11 => c_pend_fut_rec.ATTRIBUTE11,
1573 x_ATTRIBUTE12 => c_pend_fut_rec.ATTRIBUTE12,
1574 x_ATTRIBUTE13 => c_pend_fut_rec.ATTRIBUTE13,
1575 x_ATTRIBUTE14 => c_pend_fut_rec.ATTRIBUTE14,
1576 x_ATTRIBUTE15 => c_pend_fut_rec.ATTRIBUTE15,
1577 x_ATTRIBUTE16 => c_pend_fut_rec.ATTRIBUTE16,
1578 x_ATTRIBUTE17 => c_pend_fut_rec.ATTRIBUTE17,
1579 x_ATTRIBUTE18 => c_pend_fut_rec.ATTRIBUTE18,
1580 x_ATTRIBUTE19 => c_pend_fut_rec.ATTRIBUTE19,
1581 x_ATTRIBUTE20 => c_pend_fut_rec.ATTRIBUTE20,
1582 x_DECISION_REASON_ID => c_pend_fut_rec.DECISION_REASON_ID,
1583 x_US_VERSION_NUMBER => c_pend_fut_rec.US_VERSION_NUMBER,
1584 x_DECISION_NOTES => c_pend_fut_rec.DECISION_NOTES,
1585 x_PENDING_REASON_ID => c_pend_fut_rec.PENDING_REASON_ID,
1586 x_PREFERENCE_NUMBER => c_pend_fut_rec.PREFERENCE_NUMBER,
1587 x_ADM_DOC_STATUS => c_pend_fut_rec.ADM_DOC_STATUS,
1588 x_ADM_ENTRY_QUAL_STATUS => c_pend_fut_rec.ADM_ENTRY_QUAL_STATUS,
1589 x_DEFICIENCY_IN_PREP => c_pend_fut_rec.DEFICIENCY_IN_PREP,
1590 x_LATE_ADM_FEE_STATUS => c_pend_fut_rec.LATE_ADM_FEE_STATUS,
1591 x_SPL_CONSIDER_COMMENTS => c_pend_fut_rec.SPL_CONSIDER_COMMENTS,
1592 x_APPLY_FOR_FINAID => c_pend_fut_rec.APPLY_FOR_FINAID,
1593 x_FINAID_APPLY_DATE => c_pend_fut_rec.FINAID_APPLY_DATE,
1594 x_ADM_OUTCOME_STATUS => c_pend_fut_rec.ADM_OUTCOME_STATUS,
1595 x_adm_otcm_stat_auth_per_id => c_pend_fut_rec.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1596 x_ADM_OUTCOME_STATUS_AUTH_DT => c_pend_fut_rec.ADM_OUTCOME_STATUS_AUTH_DT,
1597 x_ADM_OUTCOME_STATUS_REASON => c_pend_fut_rec.ADM_OUTCOME_STATUS_REASON,
1598 x_OFFER_DT => c_pend_fut_rec.OFFER_DT,
1599 x_OFFER_RESPONSE_DT => c_pend_fut_rec.OFFER_RESPONSE_DT,
1600 x_PRPSD_COMMENCEMENT_DT => c_pend_fut_rec.PRPSD_COMMENCEMENT_DT,
1601 x_ADM_CNDTNL_OFFER_STATUS => c_pend_fut_rec.ADM_CNDTNL_OFFER_STATUS,
1602 x_CNDTNL_OFFER_SATISFIED_DT => c_pend_fut_rec.CNDTNL_OFFER_SATISFIED_DT,
1603 x_cndnl_ofr_must_be_stsfd_ind => c_pend_fut_rec.CNDTNL_OFFER_MUST_BE_STSFD_IND,
1604 x_ADM_OFFER_RESP_STATUS => c_pend_fut_rec.ADM_OFFER_RESP_STATUS,
1605 x_ACTUAL_RESPONSE_DT => c_pend_fut_rec.ACTUAL_RESPONSE_DT,
1606 x_ADM_OFFER_DFRMNT_STATUS => c_pend_fut_rec.ADM_OFFER_DFRMNT_STATUS,
1607 x_DEFERRED_ADM_CAL_TYPE => c_pend_fut_rec.DEFERRED_ADM_CAL_TYPE,
1608 x_DEFERRED_ADM_CI_SEQUENCE_NUM => c_pend_fut_rec.DEFERRED_ADM_CI_SEQUENCE_NUM,
1609 x_DEFERRED_TRACKING_ID => c_pend_fut_rec.DEFERRED_TRACKING_ID,
1610 x_ASS_RANK => c_pend_fut_rec.ASS_RANK,
1611 x_SECONDARY_ASS_RANK => c_pend_fut_rec.SECONDARY_ASS_RANK,
1615 x_HECS_PAYMENT_OPTION => c_pend_fut_rec.HECS_PAYMENT_OPTION,
1612 x_intr_accept_advice_num => c_pend_fut_rec.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1613 x_ASS_TRACKING_ID => c_pend_fut_rec.ASS_TRACKING_ID,
1614 x_FEE_CAT => c_pend_fut_rec.FEE_CAT,
1616 x_EXPECTED_COMPLETION_YR => c_pend_fut_rec.EXPECTED_COMPLETION_YR,
1617 x_EXPECTED_COMPLETION_PERD => c_pend_fut_rec.EXPECTED_COMPLETION_PERD,
1618 x_CORRESPONDENCE_CAT => c_pend_fut_rec.CORRESPONDENCE_CAT,
1619 x_ENROLMENT_CAT => c_pend_fut_rec.ENROLMENT_CAT,
1620 x_FUNDING_SOURCE => c_pend_fut_rec.FUNDING_SOURCE,
1621 x_APPLICANT_ACPTNCE_CNDTN => c_pend_fut_rec.APPLICANT_ACPTNCE_CNDTN,
1622 x_CNDTNL_OFFER_CNDTN => c_pend_fut_rec.CNDTNL_OFFER_CNDTN,
1623 X_MODE => 'S',
1624 X_SS_APPLICATION_ID => c_pend_fut_rec.SS_APPLICATION_ID,
1625 X_SS_PWD => c_pend_fut_rec.SS_PWD,
1626 X_AUTHORIZED_DT => c_pend_fut_rec.AUTHORIZED_DT,
1627 X_AUTHORIZING_PERS_ID => c_pend_fut_rec.AUTHORIZING_PERS_ID,
1628 x_entry_status => c_pend_fut_rec.entry_status,
1629 x_entry_level => c_pend_fut_rec.entry_level,
1630 x_sch_apl_to_id => c_pend_fut_rec.sch_apl_to_id,
1631 x_idx_calc_date => c_pend_fut_rec.idx_calc_date,
1632 x_waitlist_status => c_pend_fut_rec.waitlist_status,
1633 x_ATTRIBUTE21 => c_pend_fut_rec.ATTRIBUTE21,
1634 x_ATTRIBUTE22 => c_pend_fut_rec.ATTRIBUTE22,
1635 x_ATTRIBUTE23 => c_pend_fut_rec.ATTRIBUTE23,
1636 x_ATTRIBUTE24 => c_pend_fut_rec.ATTRIBUTE24,
1637 x_ATTRIBUTE25 => c_pend_fut_rec.ATTRIBUTE25,
1638 x_ATTRIBUTE26 => c_pend_fut_rec.ATTRIBUTE26,
1639 x_ATTRIBUTE27 => c_pend_fut_rec.ATTRIBUTE27,
1640 x_ATTRIBUTE28 => c_pend_fut_rec.ATTRIBUTE28,
1641 x_ATTRIBUTE29 => c_pend_fut_rec.ATTRIBUTE29,
1642 x_ATTRIBUTE30 => c_pend_fut_rec.ATTRIBUTE30,
1643 x_ATTRIBUTE31 => c_pend_fut_rec.ATTRIBUTE31,
1644 x_ATTRIBUTE32 => c_pend_fut_rec.ATTRIBUTE32,
1645 x_ATTRIBUTE33 => c_pend_fut_rec.ATTRIBUTE33,
1646 x_ATTRIBUTE34 => c_pend_fut_rec.ATTRIBUTE34,
1647 x_ATTRIBUTE35 => c_pend_fut_rec.ATTRIBUTE35,
1648 x_ATTRIBUTE36 => c_pend_fut_rec.ATTRIBUTE36,
1649 x_ATTRIBUTE37 => c_pend_fut_rec.ATTRIBUTE37,
1650 x_ATTRIBUTE38 => c_pend_fut_rec.ATTRIBUTE38,
1651 x_ATTRIBUTE39 => c_pend_fut_rec.ATTRIBUTE39,
1652 x_ATTRIBUTE40 => c_pend_fut_rec.ATTRIBUTE40,
1653 x_fut_acad_cal_type => NULL,
1654 x_fut_acad_ci_sequence_number => NULL,
1655 x_fut_adm_cal_type => NULL,
1656 x_fut_adm_ci_sequence_number => NULL,
1657 x_prev_term_adm_appl_number => c_pend_fut_rec.PREVIOUS_TERM_ADM_APPL_NUMBER,
1658 x_prev_term_sequence_number => c_pend_fut_rec.PREVIOUS_TERM_SEQUENCE_NUMBER,
1659 x_fut_term_adm_appl_number => c_pend_fut_rec.FUTURE_TERM_ADM_APPL_NUMBER,
1660 x_fut_term_sequence_number => c_pend_fut_rec.FUTURE_TERM_SEQUENCE_NUMBER,
1661 x_def_acad_cal_type => c_pend_fut_rec.def_acad_cal_type,
1662 x_def_acad_ci_sequence_num => c_pend_fut_rec.def_acad_ci_sequence_num,
1663 x_def_prev_term_adm_appl_num => c_pend_fut_rec.def_prev_term_adm_appl_num,
1664 x_def_prev_appl_sequence_num => c_pend_fut_rec.def_prev_appl_sequence_num,
1665 x_def_term_adm_appl_num => c_pend_fut_rec.def_term_adm_appl_num,
1666 x_def_appl_sequence_num => c_pend_fut_rec.def_appl_sequence_num,
1667 x_appl_inst_status => c_pend_fut_rec.appl_inst_status,
1668 x_ais_reason => c_pend_fut_rec.ais_reason,
1669 x_decline_ofr_reason => c_pend_fut_rec.decline_ofr_reason);
1670 FND_FILE.PUT_LINE(FND_FILE.LOG,' Cancelled Reconsideration Request.' ); -- 4747281
1671 EXCEPTION
1672
1673 WHEN OTHERS THEN
1674
1675 ROLLBACK TO c_update_inst;
1676
1677 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cancel Reconsideration Failed: ' || FND_MESSAGE.GET);
1678
1679 l_record_errored := 'Y';
1680
1681 END;
1682
1683 END LOOP;
1684 END IF;
1685 END IF;
1686
1687
1688 END IF;
1689 -- if none of the application programs are processed then give the appropiate log message
1690 IF l_processing_ind = 'N' THEN
1691 FND_FILE.PUT_LINE(FND_FILE.LOG,' No Application Instance Matching The Given Criteria Is Found.' );
1692 -- if the cancel reconsideration job fails for any of the application programs then give the appropiate log message.
1693 ELSIF l_record_errored = 'Y' THEN
1694 FND_FILE.PUT_LINE(FND_FILE.LOG,' For Some Application Instance Cancel Reconsideration Job Failed.' );
1695 END IF;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
1700 retcode:=2;
1701 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1702 igs_ge_msg_stack.conc_exception_hndl;
1703 END cancel_reconsider_appl;
1704
1705 END igs_ad_cancel_reconsider;