[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_SUSPEND_APPL_PKG
Source
1 PACKAGE BODY IGS_AD_SUSPEND_APPL_PKG AS
2 /* $Header: IGSADC3B.pls 120.2 2006/01/16 20:24:47 rghosh ship $ */
3 /******************************************************************
4 Created By: [email protected]
5 Date Created: 09-16-2002
6 Purpose: ADCR040 - Change Suspended Applications to Withdrawn
7 Known limitations,enhancements,remarks:
8 Change History
9 Who When What
10 ******************************************************************/
11 PROCEDURE prc_suspend_adm_appl(
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER,
14 p_acad_perd IN VARCHAR2,
15 p_adm_perd IN VARCHAR2,
16 p_admission_process_category IN VARCHAR2) AS
17
18 /*****************************************************************************************
19 Created By: [email protected]
20 Date Created : 09-16-2002
21 Purpose: ADCR040 - Change Suspended Applications to Withdrawn, Main Proc
22 Known limitations,enhancements,remarks:
23 Change History
24 Who When What
25 nshee 17-DEC-2002 changed the check, additional check to log data when program start date is in future too., bug 2630250
26 *****************************************************************************************/
27
28 CURSOR c_cal_abs_val(cp_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE,cp_adm_ci_seq_number igs_ad_appl_all.adm_ci_sequence_number%TYPE) IS
29 SELECT c.absolute_val absolute_value
30 FROM IGS_CA_DA_INST c
31 WHERE
32 c.cal_type = cp_adm_cal_type
33 AND c.ci_sequence_number = cp_adm_ci_seq_number
34 AND c.dt_alias = (select ADM_APPL_COURSE_STRT_DT_ALIAS from IGS_AD_CAL_CONF);
35 c_cal_abs_val_rec c_cal_abs_val%ROWTYPE;
36
37 CURSOR c_all_records_case_1 IS
38 SELECT a.*
39 FROM igs_ad_ps_appl_inst a
40 WHERE a.adm_outcome_status IN
41 ( SELECT adm_outcome_status
42 FROM igs_ad_ou_stat
43 WHERE s_adm_outcome_status = 'SUSPEND') ;
44
45 CURSOR c_all_records_case_2(cp_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,cp_acad_ci_sequence_number igs_ad_appl_all.acad_ci_sequence_number%TYPE) IS
46 SELECT a.*
47 FROM IGS_AD_PS_APPL_INST a,
48 IGS_AD_APPL_ALL b
49 WHERE
50 a.person_id=b.person_id AND
51 a.admission_appl_number = b.admission_appl_number AND
52 b.acad_cal_type = cp_acad_cal_type AND
53 b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
54 a.adm_outcome_status IN (SELECT adm_outcome_status
55 FROM igs_ad_ou_stat
56 WHERE s_adm_outcome_status = 'SUSPEND');
57
58 CURSOR c_all_records_case_3(cp_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
59 cp_acad_ci_sequence_number igs_ad_appl_all.acad_ci_sequence_number%TYPE,
60 cp_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE,
61 cp_adm_ci_sequence_number igs_ad_appl_all.adm_ci_sequence_number%TYPE) IS
62 SELECT a.*
63 FROM IGS_AD_PS_APPL_INST a, IGS_AD_APPL_ALL b
64 WHERE a.person_id=b.person_id AND
65 a.admission_appl_number = b.admission_appl_number AND
66 b.acad_cal_type = cp_acad_cal_type AND
67 b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
68 b.adm_cal_type = cp_adm_cal_type AND
69 b.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
70 a.adm_outcome_status IN (SELECT adm_outcome_status
71 FROM igs_ad_ou_stat
72 WHERE s_adm_outcome_status = 'SUSPEND');
73
74 CURSOR c_all_records_case_4(cp_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE,
75 cp_acad_ci_sequence_number igs_ad_appl_all.acad_ci_sequence_number%TYPE,
76 cp_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE,
77 cp_adm_ci_sequence_number igs_ad_appl_all.adm_ci_sequence_number%TYPE,
78 cp_adm_cat igs_ad_prd_ad_prc_ca.admission_cat%TYPE,
79 cp_admission_process_type igs_ad_prd_ad_prc_ca.s_admission_process_type%TYPE) IS
80 SELECT a.*
81 FROM IGS_AD_PS_APPL_INST a, IGS_AD_APPL_ALL b
82 WHERE a.person_id=b.person_id AND
83 a.admission_appl_number = b.admission_appl_number AND
84 b.acad_cal_type = cp_acad_cal_type AND
85 b.acad_ci_sequence_number = cp_acad_ci_sequence_number AND
86 b.adm_cal_type = cp_adm_cal_type AND
87 b.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
88 b.admission_cat = cp_adm_cat AND
89 b.s_admission_process_type = cp_admission_process_type AND
90 a.adm_outcome_status IN (SELECT adm_outcome_status
91 FROM igs_ad_ou_stat
92 WHERE s_adm_outcome_status = 'SUSPEND');
93
94
95 CURSOR c_get_alt_code(cp_cal_type igs_ca_inst.cal_type%TYPE,cp_seq_no igs_ca_inst.sequence_number%TYPE) IS
96 SELECT alternate_code
97 FROM IGS_CA_INST
98 WHERE cal_type = cp_cal_type AND
99 sequence_number = cp_seq_no;
100
101
102 lv_acad_cal_type igs_ad_appl_all.acad_cal_type%TYPE;
103 lv_acad_ci_sequence_number igs_ad_appl_all.acad_ci_sequence_number%TYPE;
104 lv_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE;
105 lv_adm_ci_sequence_number igs_ad_appl_all.adm_ci_sequence_number%TYPE;
106 lv_admission_cat igs_ad_prd_ad_prc_ca.admission_cat%TYPE ;
107 lv_admission_process_type igs_ad_prd_ad_prc_ca.s_admission_process_type%TYPE;
108 lv_alt_code_acad igs_ca_inst.alternate_code%TYPE;
109 lv_alt_code_adm igs_ca_inst.alternate_code%TYPE;
110
111
112 CASE_1 BOOLEAN;
113 CASE_2 BOOLEAN;
114 CASE_3 BOOLEAN;
115 CASE_4 BOOLEAN;
116
117 PROCEDURE logerror(p_pid IN igs_ad_ps_appl_inst.person_id%TYPE,
118 p_aid IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
119 p_pc IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
120 p_seq IN igs_ad_ps_appl_inst.sequence_number%TYPE,
121 p_message IN VARCHAR2) AS
122 /*****************************************************************************************
123 Created By: [email protected]
124 Date Created : 09-16-2002
125 Purpose: ADCR040 - Change Suspended Applications to Withdrawn: Log Error Procedure
126 Known limitations,enhancements,remarks:
127 Change History
128 Who When What
129 *****************************************************************************************/
130 CURSOR c_get_person_number IS SELECT party_number FROM hz_parties
131 WHERE party_id = p_pid;
132 l_person_number hz_parties.party_number%TYPE;
133 l_err_msg VARCHAR2(2000);
134 BEGIN
135 OPEN c_get_person_number ;
136 FETCH c_get_person_number INTO l_person_number;
137 CLOSE c_get_person_number ;
138 FND_MESSAGE.SET_NAME ('IGS', p_message);
139 l_err_msg := FND_MESSAGE.GET();
140 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_ERROR_MESSAGE');
141 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad(l_person_number,30)||rpad(p_aid,19)||rpad(p_pc,13)||rpad(p_seq,16)||fnd_global.newline||FND_MESSAGE.GET()||l_err_msg );
142 END logerror;
143
144 PROCEDURE update_appl_inst(cp_all_records_rec c_all_records_case_1%ROWTYPE)
145 IS
146 /*****************************************************************************************
147 Created By: [email protected]
148 Date Created : 09-16-2002
149 Purpose: ADCR040 - Change Suspended Applications to Withdrawn: Updates Application instance
150 Known limitations,enhancements,remarks:
151 Change History
152 Who When What
153 *****************************************************************************************/
154 lv_msg VARCHAR2(2000);
155 BEGIN
156 IGS_AD_PS_APPL_INST_PKG.UPDATE_ROW (
157 X_Mode => 'R',
158 X_RowId => cp_all_records_rec.row_id,
159 X_Person_Id => cp_all_records_rec.Person_Id ,
160 X_Admission_Appl_Number => cp_all_records_rec.Admission_Appl_Number ,
161 X_Nominated_Course_Cd => cp_all_records_rec.Nominated_Course_Cd ,
162 X_Sequence_Number => cp_all_records_rec.Sequence_Number ,
163 X_Predicted_Gpa => cp_all_records_rec.Predicted_Gpa ,
164 X_Academic_Index => cp_all_records_rec.Academic_Index ,
165 X_Adm_Cal_Type => cp_all_records_rec.Adm_Cal_Type ,
166 X_App_File_Location => cp_all_records_rec.App_File_Location ,
167 X_Adm_Ci_Sequence_Number => cp_all_records_rec.Adm_Ci_Sequence_Number ,
168 X_Course_Cd => cp_all_records_rec.Course_Cd ,
169 X_App_Source_Id => cp_all_records_rec.App_Source_Id ,
170 X_Crv_Version_Number => cp_all_records_rec.Crv_Version_Number ,
171 X_Waitlist_Rank => cp_all_records_rec.Waitlist_Rank ,
172 X_Location_Cd => cp_all_records_rec.Location_Cd ,
173 X_Attent_Other_Inst_Cd => cp_all_records_rec.Attent_Other_Inst_Cd ,
174 X_Attendance_Mode => cp_all_records_rec.Attendance_Mode ,
175 X_Edu_Goal_Prior_Enroll_Id => cp_all_records_rec.Edu_Goal_Prior_Enroll_Id ,
176 X_Attendance_Type => cp_all_records_rec.Attendance_Type ,
177 X_Decision_Make_Id => cp_all_records_rec.Decision_Make_Id ,
178 X_Unit_Set_Cd => cp_all_records_rec.Unit_Set_Cd ,
179 X_Decision_Date => cp_all_records_rec.Decision_Date ,
180 X_Attribute_Category => cp_all_records_rec.Attribute_Category ,
181 X_Attribute1 => cp_all_records_rec.Attribute1 ,
182 X_Attribute2 => cp_all_records_rec.Attribute2 ,
183 X_Attribute3 => cp_all_records_rec.Attribute3 ,
184 X_Attribute4 => cp_all_records_rec.Attribute4 ,
185 X_Attribute5 => cp_all_records_rec.Attribute5 ,
186 X_Attribute6 => cp_all_records_rec.Attribute6 ,
187 X_Attribute7 => cp_all_records_rec.Attribute7 ,
188 X_Attribute8 => cp_all_records_rec.Attribute8 ,
189 X_Attribute9 => cp_all_records_rec.Attribute9 ,
190 X_Attribute10 => cp_all_records_rec.Attribute10 ,
191 X_Attribute11 => cp_all_records_rec.Attribute11 ,
192 X_Attribute12 => cp_all_records_rec.Attribute12 ,
193 X_Attribute13 => cp_all_records_rec.Attribute13 ,
194 X_Attribute14 => cp_all_records_rec.Attribute14 ,
195 X_Attribute15 => cp_all_records_rec.Attribute15 ,
196 X_Attribute16 => cp_all_records_rec.Attribute16 ,
197 X_Attribute17 => cp_all_records_rec.Attribute17 ,
198 X_Attribute18 => cp_all_records_rec.Attribute18 ,
199 X_Attribute19 => cp_all_records_rec.Attribute19 ,
200 X_Attribute20 => cp_all_records_rec.Attribute20 ,
201 X_Decision_Reason_Id => cp_all_records_rec.Decision_Reason_Id ,
202 X_Us_Version_Number => cp_all_records_rec.Us_Version_Number ,
203 X_Decision_Notes => cp_all_records_rec.Decision_Notes ,
204 X_Pending_Reason_Id => cp_all_records_rec.Pending_Reason_Id ,
205 X_Preference_Number => cp_all_records_rec.Preference_Number ,
206 X_Adm_Doc_Status => cp_all_records_rec.Adm_Doc_Status ,
207 X_Adm_Entry_Qual_Status => cp_all_records_rec.Adm_Entry_Qual_Status ,
208 X_Deficiency_In_Prep => cp_all_records_rec.Deficiency_In_Prep ,
209 X_Late_Adm_Fee_Status => cp_all_records_rec.Late_Adm_Fee_Status ,
210 X_Spl_Consider_Comments => cp_all_records_rec.Spl_Consider_Comments ,
211 X_Apply_For_Finaid => cp_all_records_rec.Apply_For_Finaid ,
212 X_Finaid_Apply_Date => cp_all_records_rec.Finaid_Apply_Date ,
213 X_Adm_Outcome_Status => igs_ad_gen_009.admp_get_sys_aos('WITHDRAWN') ,
214 X_Adm_Otcm_Stat_Auth_Per_Id => cp_all_records_rec.adm_otcm_status_auth_person_id,
215 X_Adm_Outcome_Status_Auth_Dt => cp_all_records_rec.Adm_Outcome_Status_Auth_Dt ,
216 X_Adm_Outcome_Status_Reason => cp_all_records_rec.Adm_Outcome_Status_Reason ,
217 X_Offer_Dt => cp_all_records_rec.Offer_Dt ,
218 X_Offer_Response_Dt => cp_all_records_rec.Offer_Response_Dt ,
219 X_Prpsd_Commencement_Dt => cp_all_records_rec.Prpsd_Commencement_Dt ,
220 X_Adm_Cndtnl_Offer_Status => cp_all_records_rec.Adm_Cndtnl_Offer_Status ,
221 X_Cndtnl_Offer_Satisfied_Dt => cp_all_records_rec.Cndtnl_Offer_Satisfied_Dt ,
222 X_Cndnl_Ofr_Must_Be_Stsfd_Ind => cp_all_records_rec.cndtnl_offer_must_be_stsfd_ind ,
223 X_Adm_Offer_Resp_Status => cp_all_records_rec.Adm_Offer_Resp_Status ,
224 X_Actual_Response_Dt => cp_all_records_rec.Actual_Response_Dt ,
225 X_Adm_Offer_Dfrmnt_Status => cp_all_records_rec.Adm_Offer_Dfrmnt_Status ,
226 X_Deferred_Adm_Cal_Type => cp_all_records_rec.Deferred_Adm_Cal_Type ,
227 X_Deferred_Adm_Ci_Sequence_Num => cp_all_records_rec.Deferred_Adm_Ci_Sequence_Num ,
228 X_Deferred_Tracking_Id => cp_all_records_rec.Deferred_Tracking_Id ,
229 X_Ass_Rank => cp_all_records_rec.Ass_Rank ,
230 X_Secondary_Ass_Rank => cp_all_records_rec.Secondary_Ass_Rank ,
231 X_Intr_Accept_Advice_Num => cp_all_records_rec.intrntnl_acceptance_advice_num ,
232 X_Ass_Tracking_Id => cp_all_records_rec.Ass_Tracking_Id ,
233 X_Fee_Cat => cp_all_records_rec.Fee_Cat ,
234 X_Hecs_Payment_Option => cp_all_records_rec.Hecs_Payment_Option ,
235 X_Expected_Completion_Yr => cp_all_records_rec.Expected_Completion_Yr ,
236 X_Expected_Completion_Perd => cp_all_records_rec.Expected_Completion_Perd ,
237 X_Correspondence_Cat => cp_all_records_rec.Correspondence_Cat ,
238 X_Enrolment_Cat => cp_all_records_rec.Enrolment_Cat ,
239 X_Funding_Source => cp_all_records_rec.Funding_Source ,
240 X_Applicant_Acptnce_Cndtn => cp_all_records_rec.Applicant_Acptnce_Cndtn ,
241 X_Cndtnl_Offer_Cndtn => cp_all_records_rec.Cndtnl_Offer_Cndtn ,
242 X_SS_APPLICATION_ID => cp_all_records_rec.SS_APPLICATION_ID ,
243 X_SS_PWD => cp_all_records_rec.SS_PWD ,
244 X_AUTHORIZED_DT => cp_all_records_rec.AUTHORIZED_DT ,
245 X_AUTHORIZING_PERS_ID => cp_all_records_rec.AUTHORIZING_PERS_ID ,
246 X_ENTRY_STATUS => cp_all_records_rec.ENTRY_STATUS ,
247 X_ENTRY_LEVEL => cp_all_records_rec.ENTRY_LEVEL ,
248 X_SCH_APL_TO_ID => cp_all_records_rec.SCH_APL_TO_ID ,
249 X_IDX_CALC_DATE => cp_all_records_rec.IDX_CALC_DATE ,
250 X_WAITLIST_STATUS => cp_all_records_rec.WAITLIST_STATUS ,
251 X_Attribute21 => cp_all_records_rec.Attribute21 ,
252 X_Attribute22 => cp_all_records_rec.Attribute22 ,
253 X_Attribute23 => cp_all_records_rec.Attribute23 ,
254 X_Attribute24 => cp_all_records_rec.Attribute24 ,
255 X_Attribute25 => cp_all_records_rec.Attribute25 ,
256 X_Attribute26 => cp_all_records_rec.Attribute26 ,
257 X_Attribute27 => cp_all_records_rec.Attribute27 ,
258 X_Attribute28 => cp_all_records_rec.Attribute28 ,
259 X_Attribute29 => cp_all_records_rec.Attribute29 ,
260 X_Attribute30 => cp_all_records_rec.Attribute30 ,
261 X_Attribute31 => cp_all_records_rec.Attribute31 ,
262 X_Attribute32 => cp_all_records_rec.Attribute32 ,
263 X_Attribute33 => cp_all_records_rec.Attribute33 ,
264 X_Attribute34 => cp_all_records_rec.Attribute34 ,
265 X_Attribute35 => cp_all_records_rec.Attribute35 ,
266 X_Attribute36 => cp_all_records_rec.Attribute36 ,
267 X_Attribute37 => cp_all_records_rec.Attribute37 ,
268 X_Attribute38 => cp_all_records_rec.Attribute38 ,
269 X_Attribute39 => cp_all_records_rec.Attribute39 ,
270 X_Attribute40 => cp_all_records_rec.Attribute40 ,
271 x_fut_acad_cal_type => cp_all_records_rec.future_acad_cal_type ,
272 x_fut_acad_ci_sequence_number => cp_all_records_rec.future_acad_ci_sequence_number ,
273 x_fut_adm_cal_type => cp_all_records_rec.future_adm_cal_type ,
274 x_fut_adm_ci_sequence_number => cp_all_records_rec.future_adm_ci_sequence_number ,
275 x_prev_term_adm_appl_number => cp_all_records_rec.previous_term_adm_appl_number ,
276 x_prev_term_sequence_number => cp_all_records_rec.previous_term_sequence_number ,
277 x_fut_term_adm_appl_number => cp_all_records_rec.future_term_adm_appl_number ,
278 x_fut_term_sequence_number => cp_all_records_rec.future_term_sequence_number ,
279 x_def_acad_cal_type => cp_all_records_rec.def_acad_cal_type ,
280 x_def_acad_ci_sequence_num => cp_all_records_rec.def_acad_ci_sequence_num ,
281 x_def_prev_term_adm_appl_num => cp_all_records_rec.def_prev_term_adm_appl_num ,
282 x_def_prev_appl_sequence_num => cp_all_records_rec.def_prev_appl_sequence_num ,
283 x_def_term_adm_appl_num => cp_all_records_rec.def_term_adm_appl_num ,
284 x_def_appl_sequence_num => cp_all_records_rec.def_appl_sequence_num,
285 x_appl_inst_status => cp_all_records_rec.appl_inst_status,
286 x_ais_reason => cp_all_records_rec.ais_reason,
287 x_decline_ofr_reason => cp_all_records_rec.decline_ofr_reason
288 );
289 EXCEPTION
290 WHEN OTHERS THEN
291 fnd_message.RETRIEVE(lv_msg);
292 lv_msg := replace(lv_msg,chr(0),'/');
293 lv_msg := substr(lv_msg,instr(lv_msg,'/')+1);
294 lv_msg := substr(lv_msg,1,instr(lv_msg,'/')-1);
295 LOGERROR(p_pid =>cp_all_records_rec.person_id,
296 p_aid => cp_all_records_rec.admission_appl_number,
297 p_pc => cp_all_records_rec.nominated_course_cd,
298 p_seq => cp_all_records_rec.sequence_number,
299 p_message =>lv_msg);
300 END update_appl_inst;
301 BEGIN
302
303 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955192
304 igs_ge_gen_003.set_org_id(null);
305
306 ---------------------------------------------------------------------------------
307 --Intialize local var
308 ---------------------------------------------------------------------------------
309 retcode := 0;
310 lv_acad_cal_type := RTRIM(SUBSTR(p_acad_perd,101, 10));
311 lv_acad_ci_sequence_number := (SUBSTR(p_acad_perd,112));
312 lv_adm_cal_type := RTRIM(SUBSTR(p_adm_perd,1, 10));
313 lv_adm_ci_sequence_number:= RTRIM(SUBSTR(p_adm_perd,11));
314 lv_admission_cat := RTRIM(SUBSTR(p_admission_process_category, 1, 10));
315 lv_admission_process_type:= RTRIM(SUBSTR( p_admission_process_category, 14));
316
317 CASE_1 := FALSE;
318 CASE_2 := FALSE;
319 CASE_3 := FALSE;
320 CASE_4 := FALSE;
321
322 ---------------------------------------------------------------------------------
323 --Get Alternate Code
324 ---------------------------------------------------------------------------------
325 OPEN c_get_alt_code(lv_acad_cal_type,lv_acad_ci_sequence_number);
326 FETCH c_get_alt_code INTO lv_alt_code_acad;
327 CLOSE c_get_alt_code;
328
329 OPEN c_get_alt_code(lv_adm_cal_type,lv_adm_ci_sequence_number);
330 FETCH c_get_alt_code INTO lv_alt_code_adm;
331 CLOSE c_get_alt_code;
332
333 ---------------------------------------------------------------------------------
334 --Define Log File
335 ---------------------------------------------------------------------------------
336 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APPL_ACADCL_DTLS');
337 FND_MESSAGE.SET_TOKEN('ACADCL',lv_alt_code_acad);
338 FND_FILE.PUT_LINE (FND_FILE.LOG,lv_acad_cal_type||'/'||FND_MESSAGE.GET());
339
340 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_APPL_ADMCL_DTLS');
341 FND_MESSAGE.SET_TOKEN('ADMCL', lv_alt_code_adm);
342 FND_FILE.PUT_LINE (FND_FILE.LOG,lv_adm_cal_type||'/'||FND_MESSAGE.GET());
343
344 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_APP_LG_APC');
345 FND_MESSAGE.SET_TOKEN ('APC', p_admission_process_category);
346 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.GET());
347
348 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================================');
349 FND_MESSAGE.SET_NAME('IGS','IGS_AD_SUSPEND_APPL_LOG_HDR');
350 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET());
351 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================================');
352
353 ---------------------------------------------------------------------------------
354 --Judge Cases, Set Boolean TRUE for cases to execute
355 ---------------------------------------------------------------------------------
356 IF (p_acad_perd IS NULL AND p_adm_perd IS NULL AND p_admission_process_category IS NULL) THEN
357 CASE_1 := TRUE;
358 CASE_2 := FALSE;
359 CASE_3 := FALSE;
360 CASE_4 := FALSE;
361 ELSIF ((p_acad_perd IS NOT NULL AND p_adm_perd IS NULL AND p_admission_process_category IS NULL)) THEN
362 CASE_1 := FALSE;
363 CASE_2 := TRUE;
364 CASE_3 := FALSE;
365 CASE_4 := FALSE;
366 ELSIF ((p_acad_perd IS NOT NULL AND p_adm_perd IS NOT NULL AND p_admission_process_category IS NULL)) THEN
367 CASE_1 := FALSE;
368 CASE_2 := FALSE;
369 CASE_3 := TRUE;
370 CASE_4 := FALSE;
371 ELSIF ((p_acad_perd IS NOT NULL AND p_adm_perd IS NOT NULL AND p_admission_process_category IS NOT NULL)) THEN
372 CASE_1 := FALSE;
373 CASE_2 := FALSE;
374 CASE_3 := FALSE;
375 CASE_4 := TRUE;
376 END IF;
377
378 ---------------------------------------------------------------------------------
379 --Executing Cases
380 ---------------------------------------------------------------------------------
381 IF CASE_1 THEN
382 FOR c_all_records_case_1_rec in c_all_records_case_1 LOOP
383 OPEN c_cal_abs_val(c_all_records_case_1_rec.adm_cal_type,c_all_records_case_1_rec.adm_ci_sequence_number);
384 FETCH c_cal_abs_val INTO c_cal_abs_val_rec;
385 -- IF c_cal_abs_val%NOTFOUND THEN --nshee bug 2630250
386 IF (c_cal_abs_val%NOTFOUND OR c_cal_abs_val_rec.absolute_value IS NULL OR TRUNC(c_cal_abs_val_rec.absolute_value)>TRUNC(SYSDATE)) THEN
387 ---------------------------------------------------------------------------------
388 --Log Error if abs value not found
389 ---------------------------------------------------------------------------------
390 LOGERROR(p_pid =>c_all_records_case_1_rec.person_id,
391 p_aid => c_all_records_case_1_rec.admission_appl_number,
392 p_pc => c_all_records_case_1_rec.nominated_course_cd,
393 p_seq => c_all_records_case_1_rec.sequence_number,
394 p_message =>'IGS_AD_APPL_CANNOT_WITHDRAW');
395 ELSE
396 IF TRUNC(c_cal_abs_val_rec.absolute_value)<=TRUNC(SYSDATE) THEN
397 ---------------------------------------------------------------------------------
398 --Update Application Instance if everything is correct
399 ---------------------------------------------------------------------------------
400 update_appl_inst(c_all_records_case_1_rec);
401 END IF;
402 END IF;
403 CLOSE c_cal_abs_val;
404 END LOOP;
405 ELSIF CASE_2 THEN
406 FOR c_all_records_case_2_rec in c_all_records_case_2(lv_acad_cal_type,lv_acad_ci_sequence_number) LOOP
407 OPEN c_cal_abs_val(c_all_records_case_2_rec.adm_cal_type,c_all_records_case_2_rec.adm_ci_sequence_number);
408 FETCH c_cal_abs_val INTO c_cal_abs_val_rec;
409 -- IF c_cal_abs_val%NOTFOUND THEN --nshee bug 2630250
410 IF (c_cal_abs_val%NOTFOUND OR c_cal_abs_val_rec.absolute_value IS NULL OR TRUNC(c_cal_abs_val_rec.absolute_value)>TRUNC(SYSDATE)) THEN
411 LOGERROR(p_pid =>c_all_records_case_2_rec.person_id,
412 p_aid => c_all_records_case_2_rec.admission_appl_number,
413 p_pc => c_all_records_case_2_rec.nominated_course_cd,
414 p_seq => c_all_records_case_2_rec.sequence_number,
415 p_message =>'IGS_AD_APPL_CANNOT_WITHDRAW');
416 ELSE
417 IF TRUNC(c_cal_abs_val_rec.absolute_value)<=TRUNC(SYSDATE) THEN
418 update_appl_inst(c_all_records_case_2_rec);
419 END IF;
420 END IF;
421 CLOSE c_cal_abs_val;
422 END LOOP;
423 ELSIF CASE_3 THEN
424 FOR c_all_records_case_3_rec in c_all_records_case_3(lv_acad_cal_type,lv_acad_ci_sequence_number,lv_adm_cal_type,lv_adm_ci_sequence_number) LOOP
425 OPEN c_cal_abs_val(c_all_records_case_3_rec.adm_cal_type,c_all_records_case_3_rec.adm_ci_sequence_number);
426 FETCH c_cal_abs_val INTO c_cal_abs_val_rec;
427 -- IF c_cal_abs_val%NOTFOUND THEN --nshee bug 2630250
428 IF (c_cal_abs_val%NOTFOUND OR c_cal_abs_val_rec.absolute_value IS NULL OR TRUNC(c_cal_abs_val_rec.absolute_value)>TRUNC(SYSDATE)) THEN
429 LOGERROR(p_pid =>c_all_records_case_3_rec.person_id,
430 p_aid => c_all_records_case_3_rec.admission_appl_number,
431 p_pc => c_all_records_case_3_rec.nominated_course_cd,
432 p_seq => c_all_records_case_3_rec.sequence_number,
433 p_message =>'IGS_AD_APPL_CANNOT_WITHDRAW');
434 ELSE
435 IF TRUNC(c_cal_abs_val_rec.absolute_value)<=TRUNC(SYSDATE) THEN
436 update_appl_inst(c_all_records_case_3_rec);
437 END IF;
438 END IF;
439 CLOSE c_cal_abs_val;
440 END LOOP;
441 ELSIF CASE_4 THEN
442 FOR c_all_records_case_4_rec in c_all_records_case_4(lv_acad_cal_type,lv_acad_ci_sequence_number,
443 lv_adm_cal_type,lv_adm_ci_sequence_number,
444 lv_admission_cat,
445 lv_admission_process_type) LOOP
446 OPEN c_cal_abs_val(c_all_records_case_4_rec.adm_cal_type,c_all_records_case_4_rec.adm_ci_sequence_number);
447 FETCH c_cal_abs_val INTO c_cal_abs_val_rec;
448 -- IF c_cal_abs_val%NOTFOUND THEN --nshee bug 2630250
449 IF ( c_cal_abs_val%NOTFOUND OR c_cal_abs_val_rec.absolute_value IS NULL OR TRUNC(c_cal_abs_val_rec.absolute_value)>TRUNC(SYSDATE)) THEN
450 LOGERROR(p_pid =>c_all_records_case_4_rec.person_id,
451 p_aid => c_all_records_case_4_rec.admission_appl_number,
452 p_pc => c_all_records_case_4_rec.nominated_course_cd,
453 p_seq => c_all_records_case_4_rec.sequence_number,
454 p_message =>'IGS_AD_APPL_CANNOT_WITHDRAW');
455 ELSE
456 IF TRUNC(c_cal_abs_val_rec.absolute_value)<=TRUNC(SYSDATE) THEN
457 update_appl_inst(c_all_records_case_4_rec);
458 END IF;
459 END IF;
460 CLOSE c_cal_abs_val;
461 END LOOP;
462 END IF;
463 EXCEPTION
464 WHEN OTHERS THEN
465 retcode:=2;
466 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
467 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
468 END prc_suspend_adm_appl;
469
470 END IGS_AD_SUSPEND_APPL_PKG;