1 PACKAGE BODY igs_uc_expunge_app AS
2 /* $Header: IGSUC38B.pls 120.1 2006/02/08 19:56:45 anwest noship $ */
3
4 PROCEDURE delete_ucas_interface_rec( p_app_no IN NUMBER) IS
5 /*************************************************************
6 Created By : rbezawad
7 Date Created By : 11-NOV-2002
8 Purpose : To delete Wrong Applicant records from UCAS Interface tables.
9
10 Know limitations, enhancements or remarks
11 Change History
12 Who When What
13 (reverse chronological order - newest change first)
14 rbezawad 16-Oct-03 Added logic to delete from interface (_INTS) tables w.r.t. ucfd209 build, bug: 2669228.
15 ***************************************************************/
16
17 CURSOR cur_ucap IS
18 SELECT ROWID
19 FROM igs_uc_applicants
20 WHERE app_no = p_app_no;
21
22 CURSOR cur_ucapcl IS
23 SELECT ROWID
24 FROM igs_uc_app_clearing
25 WHERE app_no = p_app_no;
26
27 CURSOR cur_ucapcr IS
28 SELECT ROWID
29 FROM igs_uc_app_clr_rnd
30 WHERE app_no = p_app_no;
31
32 CURSOR cur_ucapre IS
33 SELECT ROWID
34 FROM igs_uc_app_results
35 WHERE app_no = p_app_no;
36
37 CURSOR cur_ucapst IS
38 SELECT ROWID
39 FROM igs_uc_app_stats
40 WHERE app_no = p_app_no;
41
42 CURSOR cur_ucaddr IS
43 SELECT ROWID
44 FROM igs_uc_app_addreses
45 WHERE app_no = p_app_no;
46
47 CURSOR cur_ucnames IS
48 SELECT ROWID
49 FROM igs_uc_app_names
50 WHERE app_no = p_app_no;
51
52 CURSOR cur_ucref IS
53 SELECT ROWID
54 FROM igs_uc_app_referees
55 WHERE app_no = p_app_no;
56
57 CURSOR cur_ucfq IS
58 SELECT ROWID
59 FROM igs_uc_form_quals
60 WHERE app_no = p_app_no;
61
62 BEGIN
63
64 --Delete Wrong Applicant records from UCAS Interface tables by calling the corresponding TBH.
65 FOR x IN cur_ucapst LOOP
66 igs_uc_app_stats_pkg.delete_row ( x_rowid => x.ROWID );
67 END LOOP;
68
69 FOR x IN cur_ucapre LOOP
70 igs_uc_app_results_pkg.delete_row ( x_rowid => x.ROWID );
71 END LOOP;
72
73 FOR x IN cur_ucapcr LOOP
74 igs_uc_app_clr_rnd_pkg.delete_row ( x_rowid => x.ROWID );
75 END LOOP;
76
77 FOR x IN cur_ucapcl LOOP
78 igs_uc_app_clearing_pkg.delete_row ( x_rowid => x.ROWID );
79 END LOOP;
80
81 FOR x IN cur_ucaddr LOOP
82 igs_uc_app_addreses_pkg.delete_row ( x_rowid => x.ROWID );
83 END LOOP;
84
85 FOR x IN cur_ucnames LOOP
86 igs_uc_app_names_pkg.delete_row ( x_rowid => x.ROWID );
87 END LOOP;
88
89 FOR x IN cur_ucref LOOP
90 igs_uc_app_referees_pkg.delete_row ( x_rowid => x.ROWID );
91 END LOOP;
92
93 FOR x IN cur_ucfq LOOP
94 igs_uc_form_quals_pkg.delete_row ( x_rowid => x.ROWID );
95 END LOOP;
96
97 FOR x IN cur_ucap LOOP
98 igs_uc_applicants_pkg.delete_row ( x_rowid => x.ROWID );
99 END LOOP;
100
101 DELETE igs_uc_ifrmqul_ints WHERE appno = p_app_no;
102
103 DELETE igs_uc_iqual_ints WHERE appno = p_app_no;
104
105 DELETE igs_uc_irefrnc_ints WHERE appno = p_app_no;
106
107 DELETE igs_uc_istara_ints WHERE appno = p_app_no;
108
109 DELETE igs_uc_istarg_ints WHERE appno = p_app_no;
110
111 DELETE igs_uc_istarh_ints WHERE appno = p_app_no;
112
113 DELETE igs_uc_istarj_ints WHERE appno = p_app_no;
114
115 DELETE igs_uc_istark_ints WHERE appno = p_app_no;
116
117 DELETE igs_uc_istarn_ints WHERE appno = p_app_no;
118
119 DELETE igs_uc_istart_ints WHERE appno = p_app_no;
120
121 DELETE igs_uc_istarw_ints WHERE appno = p_app_no;
122
123 DELETE igs_uc_istarx_ints WHERE appno = p_app_no;
124
125 DELETE igs_uc_istarz1_ints WHERE appno = p_app_no;
126
127 DELETE igs_uc_istarz2_ints WHERE appno = p_app_no;
128
129 DELETE igs_uc_istmnt_ints WHERE appno = p_app_no;
130
131 DELETE igs_uc_istrpqr_ints WHERE appno = p_app_no;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
136 fnd_message.set_token('NAME','igs_uc_expunge_app.delete_ucas_interface_rec'||' - '||SQLERRM);
137 igs_ge_msg_stack.add;
138 app_exception.raise_exception;
139
140 END delete_ucas_interface_rec;
141
142
143 PROCEDURE expunge_proc( Errbuf OUT NOCOPY VARCHAR2,
144 Retcode OUT NOCOPY NUMBER,
145 p_app_no IN NUMBER
146 ) IS
147 /*************************************************************
148 Created By : rbezawad
149 Date Created By : 11-NOV-2002
150 Purpose : 1) The admissions decision import process is used to suspend the OSS Applications for the UCAS Wrong Application
151 and the Choice Number marked as lost.
152 2) The Person Alternate Id stored in IGS_PE_ALT_PERS_ID table for the UCAS Wrong Applicaton should be end dated.
153 3) Delete the Wrong Application related data from UCAS interface tables.
154 4) If all wrong application data is successfully deleted from UCAS Interface tables then mark the applicants as
155 expunged by setting the flag in IGS_UC_WRONG_APP.EXPUNGED to 'Y'.
156 5) log the message in the log file for the each step whether the processing is succussful or not.
157
158 Know limitations, enhancements or remarks
159 Change History
160 Who When What
161 pkpatel 2-DEC-2002 Bug No: 2599109
162 Modified igs_pe_alt_pers_id_pkg signature to include REGION_CD
163 rbezawad 16-Oct-03 Modified logic to expunge at Application Choice level rather only at Application
164 level w.r.t. ucfd209 build, bug: 2669228.
165 ssaleem 09-FEB-05 Bug 3882788 - DELETION OF INVALID ALTERNATE PERSON IDS
166 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
167 (reverse chronological order - newest change first)
168 ***************************************************************/
169
170 --To check whether the setup record is available FOR each System, which has an application to be expunged.
171 CURSOR cur_system_setup IS
172 SELECT 'X'
173 FROM igs_uc_defaults def,
174 igs_uc_applicants ucap
175 WHERE def.system_code (+) = ucap.system_code
176 AND def.system_code IS NULL
177 AND ucap.app_no IN ( SELECT app_no
178 FROM igs_uc_wrong_app wap
179 WHERE wap.expunge = 'Y'
180 AND wap.expunged <> 'Y'
181 AND wap.app_no = NVL(p_app_no, wap.app_no) );
182
183 --To check whether the all required values available in each UCAS Setup FOR the System, which has an application to be expunged.
184 CURSOR cur_system_val_setup IS
185 SELECT 'X'
186 FROM igs_uc_defaults def
187 WHERE system_code IN ( SELECT DISTINCT system_code
188 FROM igs_uc_applicants ucap,
189 igs_uc_wrong_app wap
190 WHERE ucap.app_no = wap.app_no
191 AND wap.expunge = 'Y'
192 AND wap.expunged <> 'Y'
193 AND wap.app_no = NVL(p_app_no, wap.app_no) )
194 AND ( def.obsolete_outcome_status IS NULL OR def.decision_make_id IS NULL OR def.decision_reason_id IS NULL );
195
196 --To loop through all the Applicant records to be expunged.
197 CURSOR cur_wrong_app IS
198 SELECT wap.ROWID row_id, wap.*
199 FROM igs_uc_wrong_app wap
200 WHERE wap.app_no = NVL(p_app_no, wap.app_no)
201 AND wap.expunge = 'Y'
202 AND wap.expunged <> 'Y'
203 ORDER BY wap.app_no;
204
205 CURSOR cur_defaults (cp_app_no igs_uc_wrong_app.app_no%TYPE) IS
206 SELECT obsolete_outcome_status,
207 decision_make_id,
208 decision_reason_id
209 FROM igs_uc_defaults def,
210 igs_uc_applicants ucap
211 WHERE def.system_code = ucap.system_code
212 AND ucap.app_no = cp_app_no;
213
214 --To identify the OSS Admission Application instances, which are created FOR UCAS Application number to be expunged
215 CURSOR cur_oss_ad_appl_inst (cp_app_no igs_uc_wrong_app.app_no%TYPE,
216 cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
217 SELECT aap.person_id,
218 aap.admission_appl_number,
219 aap.alt_appl_id,
220 aap.choice_number,
221 aap.acad_cal_type,
222 aap.acad_ci_sequence_number,
223 aap.adm_cal_type,
224 aap.adm_ci_sequence_number,
225 aap.admission_cat,
226 aap.s_admission_process_type,
227 apai.nominated_course_cd,
228 apai.crv_version_number,
229 apai.location_cd,
230 apai.attendance_mode,
231 apai.attendance_type,
232 apai.sequence_number,
233 apai.adm_outcome_status
234 FROM igs_ad_appl_all aap,
235 igs_ad_ps_appl_inst_all apai,
236 igs_ad_ou_stat aous
237 WHERE aap.alt_appl_id = TO_CHAR(cp_app_no)
238 AND aap.choice_number = NVL(cp_choice_no,aap.choice_number)
239 AND aap.person_id = apai.person_id
240 AND aap.admission_appl_number = apai. admission_appl_number
241 AND apai.adm_outcome_status = aous.adm_outcome_status
242 AND aous.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED')
243 ORDER BY aap.choice_number, aap.admission_appl_number, apai.preference_number;
244
245 -- To Get the interface Run ID which is used to populate the Admission Decision Import Interface table while Suspending the Application Instances.
246 CURSOR cur_interface_run_id IS
247 SELECT igs_ad_interface_ctl_s.NEXTVAL
248 FROM dual ;
249
250 --To get the Person Number FOR the give person ID
251 CURSOR cur_per_no (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
252 SELECT person_number
253 FROM igs_pe_person_base_v
254 WHERE person_id = cp_person_id;
255
256 --To get the Person alternate ID record to End Date
257 CURSOR cur_alt_pers_id (cp_app_no igs_uc_wrong_app.app_no%TYPE) IS
258 SELECT api.ROWID row_id, api.*
259 FROM igs_pe_alt_pers_id api,
260 igs_uc_applicants ucap
261 WHERE api.pe_person_id = ucap.oss_person_id
262 AND api.api_person_id = TO_CHAR(ucap.app_no)
263 AND ucap.app_no = cp_app_no
264 AND api.person_id_type= DECODE(ucap.system_code, 'U', 'UCASID', 'G', 'GTTRID', 'S', 'SWASID', 'N', 'NMASID')
265 AND (api.end_dt IS NULL OR (api.end_dt > SYSDATE AND api.end_dt <> api.start_dt));
266
267 CURSOR cur_ucapcc (cp_app_no igs_uc_app_choices.app_no%TYPE, cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
268 SELECT ROWID
269 FROM igs_uc_app_cho_cnds
270 WHERE app_no = cp_app_no
271 AND choice_no = cp_choice_no;
272
273 CURSOR cur_ucapch (cp_app_no igs_uc_app_choices.app_no%TYPE, cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
274 SELECT ROWID, choice_no
275 FROM igs_uc_app_choices
276 WHERE app_no = cp_app_no
277 AND choice_no = NVL(cp_choice_no, choice_no);
278
279 CURSOR cur_uctr (cp_app_no igs_uc_app_choices.app_no%TYPE, cp_choice_no igs_uc_app_choices.choice_no%TYPE) IS
280 SELECT ROWID
281 FROM igs_uc_transactions
282 WHERE app_no = cp_app_no
283 AND choice_no = cp_choice_no;
284
285 l_appl_inst_rec cur_oss_ad_appl_inst%ROWTYPE;
286 l_defaults_rec cur_defaults%ROWTYPE;
287 l_alt_pers_id_rec cur_alt_pers_id%ROWTYPE;
288
289 l_dec_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
290 l_interface_mkdes_id igs_ad_admde_int_all.interface_mkdes_id%TYPE;
291 l_interface_run_id igs_ad_admde_int_all.interface_run_id%TYPE;
292 l_error_message fnd_new_messages.message_text%TYPE;
293 l_person_no igs_pe_person_base_v.person_number%TYPE;
294 l_dec_imp_err fnd_new_messages.message_text%TYPE;
295 l_choice_no igs_uc_app_choices.choice_no%TYPE;
296
297 --Table Type to hold the batch_id created for diferrent system cycle calendars.
298 TYPE choice_det_table_type IS TABLE OF igs_uc_app_choices.choice_no%TYPE INDEX BY BINARY_INTEGER;
299
300 --Table/Collection variable to hold the records for batch ids created of diferrent system, cycle and calendars.
301 l_expunge_choice_det choice_det_table_type;
302 l_expunge_choice_loc NUMBER;
303
304 l_setup_comp VARCHAR2(1);
305 l_ucas_app_expunged VARCHAR2(1);
306 l_oss_app_inst_suspended VARCHAR2(1);
307 l_pe_alt_pers_id_closed VARCHAR2(1);
308 l_ucas_app_recs_deleted VARCHAR2(1);
309
310 ----Local variable to indicate whether the all Application choices marked are obsolete or not.
311 l_all_makred_app_inst_expunged VARCHAR2(1);
312
313 l_return_status VARCHAR2(100) ;
314 l_rowid VARCHAR2(50);
315 l_mesg_data VARCHAR2(2000);
316 l_msg_index NUMBER;
317
318 BEGIN
319
320 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
321 IGS_GE_GEN_003.SET_ORG_ID;
322
323 --Check whether the UCAS Setup is complete or not.
324 OPEN cur_system_setup;
325 FETCH cur_system_setup INTO l_setup_comp;
326 CLOSE cur_system_setup;
327
328 OPEN cur_system_val_setup;
329 FETCH cur_system_val_setup INTO l_setup_comp;
330 CLOSE cur_system_val_setup;
331
332 IF l_setup_comp = 'X' THEN
333 fnd_message.set_name( 'IGS','IGS_UC_OBS_SETUP_NOT_SET');
334 fnd_file.put_line(fnd_file.log, fnd_message.get);
335 -- end job in warning state
336 retcode := 1;
337 ELSE
338
339 --Loop through all the Wrong Applicants to be expunged.
340 FOR l_wrong_app_rec IN cur_wrong_app
341 LOOP
342
343 -- Get the default UCAS setup values and keep them in package variable c_defaults_rec
344 OPEN cur_defaults(l_wrong_app_rec.app_no);
345 FETCH cur_defaults INTO l_defaults_rec;
346 CLOSE cur_defaults;
347
348 l_all_makred_app_inst_expunged := 'Y';
349 l_expunge_choice_loc := 0;
350 l_expunge_choice_det.DELETE;
351
352 -- Get the Choice Number details of the Application to be expunged.
353 IF l_wrong_app_rec.choice1_lost = 'N' AND l_wrong_app_rec.choice2_lost = 'N' AND
354 l_wrong_app_rec.choice3_lost = 'N' AND l_wrong_app_rec.choice4_lost = 'N' AND
355 l_wrong_app_rec.choice5_lost = 'N' AND l_wrong_app_rec.choice6_lost = 'N' AND
356 l_wrong_app_rec.choice7_lost = 'N' THEN
357 --Need to consider as expunging the whole application i.e., expunge all choices available
358 FOR l_apch_rec IN cur_ucapch (l_wrong_app_rec.app_no, NULL) LOOP
359 l_expunge_choice_det(l_expunge_choice_loc) := l_apch_rec.choice_no;
360 l_expunge_choice_loc := l_expunge_choice_loc + 1;
361 END LOOP;
362
363 ELSE
364 IF l_wrong_app_rec.choice1_lost = 'Y' THEN
365 l_expunge_choice_det(l_expunge_choice_loc) := 1; l_expunge_choice_loc := l_expunge_choice_loc + 1;
366 END IF;
367 IF l_wrong_app_rec.choice2_lost = 'Y' THEN
368 l_expunge_choice_det(l_expunge_choice_loc) := 2; l_expunge_choice_loc := l_expunge_choice_loc + 1;
369 END IF;
370 IF l_wrong_app_rec.choice3_lost = 'Y' THEN
371 l_expunge_choice_det(l_expunge_choice_loc) := 3; l_expunge_choice_loc := l_expunge_choice_loc + 1;
372 END IF;
373 IF l_wrong_app_rec.choice4_lost = 'Y' THEN
374 l_expunge_choice_det(l_expunge_choice_loc) := 4; l_expunge_choice_loc := l_expunge_choice_loc + 1;
375 END IF;
376 IF l_wrong_app_rec.choice5_lost = 'Y' THEN
377 l_expunge_choice_det(l_expunge_choice_loc) := 5; l_expunge_choice_loc := l_expunge_choice_loc + 1;
378 END IF;
379 IF l_wrong_app_rec.choice6_lost = 'Y' THEN
380 l_expunge_choice_det(l_expunge_choice_loc) := 6; l_expunge_choice_loc := l_expunge_choice_loc + 1;
381 END IF;
382 IF l_wrong_app_rec.choice7_lost = 'Y' THEN
383 l_expunge_choice_det(l_expunge_choice_loc) := 7; l_expunge_choice_loc := l_expunge_choice_loc + 1;
384 END IF;
385 END IF;
386
387 --Loop through the Application Choice's pl/sql table for expunging the corresponding choice records.
388 FOR l_loc IN l_expunge_choice_det.FIRST..l_expunge_choice_det.LAST
389 LOOP
390
391 l_oss_app_inst_suspended := 'Y';
392
393 -- log Application Choice processing message.
394 fnd_file.put_line (fnd_file.log,' ');
395 fnd_message.set_name('IGS','IGS_UC_APPNO_CHOICE_PROC');
396 fnd_message.set_token('APPNO', TO_CHAR(l_wrong_app_rec.app_no));
397 fnd_message.set_token('CHOICE',TO_CHAR(l_expunge_choice_det(l_loc)));
398 fnd_file.put_line(fnd_file.log, fnd_message.get);
399
400 --Identify the OSS Admission Application instances to be suspended.
401 OPEN cur_oss_ad_appl_inst(l_wrong_app_rec.app_no, l_expunge_choice_det(l_loc));
402 FETCH cur_oss_ad_appl_inst INTO l_appl_inst_rec;
403
404 IF cur_oss_ad_appl_inst%NOTFOUND THEN
405
406 --When there are no Application Instances to Suspend then log the message and proceed with expunge process FOR next Application number.
407 CLOSE cur_oss_ad_appl_inst;
408 fnd_message.set_name( 'IGS','IGS_UC_NO_XPG_APPL_INST');
409 fnd_message.set_token('APP_NO',TO_CHAR(l_wrong_app_rec.app_no));
410 fnd_message.set_token('CHOICE_NO',TO_CHAR(l_expunge_choice_det(l_loc)));
411 fnd_file.put_line (fnd_file.log,fnd_message.get);
412
413 ELSE
414
415 CLOSE cur_oss_ad_appl_inst;
416
417 --Loop through all the Admission Application Instances which are to be Suspended FOR UCAS Wrong Application number.
418 FOR l_oss_ad_appl_inst_rec IN cur_oss_ad_appl_inst(l_wrong_app_rec.app_no, l_expunge_choice_det(l_loc))
419 LOOP
420
421 -- Insert a record into the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL with calendar details of Application Instance
422 -- This Batch ID will be used while populating the Admission Decision Import Process Interface Table
423 l_rowid := NULL;
424 l_dec_batch_id := NULL;
425 igs_ad_batc_def_det_pkg.insert_row ( x_rowid => l_rowid,
426 x_batch_id => l_dec_batch_id,
427 x_description => fnd_message.get_string('IGS','IGS_UC_XPG_DEC_BATCH'),
428 x_acad_cal_type => l_oss_ad_appl_inst_rec.acad_cal_type,
429 x_acad_ci_sequence_number => l_oss_ad_appl_inst_rec.acad_ci_sequence_number,
430 x_adm_cal_type => l_oss_ad_appl_inst_rec.adm_cal_type,
431 x_adm_ci_sequence_number => l_oss_ad_appl_inst_rec.adm_ci_sequence_number,
432 x_admission_cat => l_oss_ad_appl_inst_rec.admission_cat,
433 x_s_admission_process_type => l_oss_ad_appl_inst_rec.s_admission_process_type,
434 x_decision_make_id => NULL,
435 x_decision_date => NULL,
436 x_decision_reason_id => NULL,
437 x_pending_reason_id => NULL,
438 x_offer_dt => NULL,
439 x_offer_response_dt => NULL,
440 x_mode => 'R' );
441
442 -- Populate the Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL FOR the application instance which need to be Suspended.
443 l_rowid := NULL;
444 l_interface_mkdes_id := NULL ;
445 l_interface_run_id := NULL ;
446 l_error_message := NULL ;
447 l_return_status := NULL ;
448
449 OPEN cur_interface_run_id ;
450 FETCH cur_interface_run_id INTO l_interface_run_id ;
451 CLOSE cur_interface_run_id ;
452
453 igs_ad_admde_int_pkg.insert_row ( x_rowid => l_rowid,
454 x_interface_mkdes_id => l_interface_mkdes_id,
455 x_interface_run_id => l_interface_run_id ,
456 x_batch_id => l_dec_batch_id,
457 x_person_id => l_oss_ad_appl_inst_rec.person_id,
458 x_admission_appl_number => l_oss_ad_appl_inst_rec.admission_appl_number,
459 x_nominated_course_cd => l_oss_ad_appl_inst_rec.nominated_course_cd,
460 x_sequence_number => l_oss_ad_appl_inst_rec.sequence_number,
461 x_adm_outcome_status => l_defaults_rec.obsolete_outcome_status,
462 x_decision_make_id => l_defaults_rec.decision_make_id,
463 x_decision_date => SYSDATE,
464 x_decision_reason_id => l_defaults_rec.decision_reason_id,
465 x_pending_reason_id => NULL,
466 x_offer_dt => NULL,
467 x_offer_response_dt => NULL,
468 x_status => '2', -- pending status
469 x_error_code => NULL,
470 x_mode => 'R' );
471
472 -- Call the decision import process to obsolete old applications
473 igs_ad_imp_adm_des.prc_adm_outcome_status( p_person_id => l_oss_ad_appl_inst_rec.person_id ,
474 p_admission_appl_number => l_oss_ad_appl_inst_rec.admission_appl_number ,
475 p_nominated_course_cd => l_oss_ad_appl_inst_rec.nominated_course_cd ,
476 p_sequence_number => l_oss_ad_appl_inst_rec.sequence_number,
477 p_adm_outcome_status => l_defaults_rec.obsolete_outcome_status,
478 p_s_adm_outcome_status => 'SUSPEND',
479 p_acad_cal_type => l_oss_ad_appl_inst_rec.acad_cal_type ,
480 p_acad_ci_sequence_number => l_oss_ad_appl_inst_rec.acad_ci_sequence_number,
481 p_adm_cal_type => l_oss_ad_appl_inst_rec.adm_cal_type ,
482 p_adm_ci_sequence_number => l_oss_ad_appl_inst_rec.adm_ci_sequence_number ,
483 p_admission_cat => l_oss_ad_appl_inst_rec.admission_cat ,
484 p_s_admission_process_type => l_oss_ad_appl_inst_rec.s_admission_process_type ,
485 p_batch_id => l_dec_batch_id,
486 p_interface_run_id => l_interface_run_id ,
487 p_interface_mkdes_id => l_interface_mkdes_id,
488 p_error_message => l_error_message, -- Replaced error_code with error_message Bug 3297241
489 p_return_status => l_return_status ,
490 p_ucas_transaction => 'N' );
491
492 -- Check if the decision import completed succussfully or not.
493 IF l_error_message IS NOT NULL OR l_return_status = 'FALSE' THEN
494 l_oss_app_inst_suspended := 'N';
495 l_all_makred_app_inst_expunged := 'N';
496 l_dec_imp_err := ' - '||l_error_message;
497
498 fnd_message.set_name('IGS','IGS_UC_OBS_APP_DEC_IMP_ERR');
499 fnd_message.set_token('APP_NO', l_oss_ad_appl_inst_rec.alt_appl_id);
500 fnd_message.set_token('CHOICE_NO',l_oss_ad_appl_inst_rec.choice_number);
501 fnd_message.set_token('BATCH_ID', l_dec_batch_id);
502 fnd_file.put_line(fnd_file.log,fnd_message.get()||l_dec_imp_err);
503 ELSE
504 OPEN cur_per_no(l_oss_ad_appl_inst_rec.person_id);
505 FETCH cur_per_no INTO l_person_no;
506 CLOSE cur_per_no;
507 fnd_message.set_name('IGS','IGS_UC_OBS_APPL_INST_COMP');
508 fnd_message.set_token('PER_NO', l_person_no);
509 fnd_message.set_token('APPL_NUM', l_oss_ad_appl_inst_rec.admission_appl_number);
510 fnd_message.set_token('PROG_CD', l_oss_ad_appl_inst_rec.nominated_course_cd);
511 fnd_message.set_token('PROG_VER', l_oss_ad_appl_inst_rec.crv_version_number);
512 fnd_message.set_token('LOC', l_oss_ad_appl_inst_rec.location_cd);
513 fnd_message.set_token('ATT_TYPE', l_oss_ad_appl_inst_rec.attendance_mode);
514 fnd_message.set_token('ATT_MODE', l_oss_ad_appl_inst_rec.attendance_type);
515 fnd_file.put_line(fnd_file.log,fnd_message.get());
516 END IF ; -- decision import failed or passed
517
518 END LOOP; -- End of the Admission Application Instances Loop FOR UCAS Wrong Applications
519
520 END IF; -- End of OSS AD Application Instances Check.
521
522 --If either there are no OSS application instances to be obsolete or all corresponding application
523 -- instances are successfully obsolete i.e. no error is encountered on decision import then delete
524 -- the Application Choice data from the UCAS Interface tables.
525 IF l_oss_app_inst_suspended = 'Y' THEN
526 --Delete Wrong Applicant records from UCAS Interface tables by calling the corresponding TBH.
527 FOR x IN cur_ucapcc (l_wrong_app_rec.app_no, l_expunge_choice_det(l_loc)) LOOP
528 igs_uc_app_cho_cnds_pkg.delete_row ( x_rowid => x.ROWID );
529 END LOOP;
530
531 FOR x IN cur_ucapch (l_wrong_app_rec.app_no, l_expunge_choice_det(l_loc)) LOOP
532 igs_uc_app_choices_pkg.delete_row ( x_rowid => x.ROWID );
533 END LOOP;
534
535 FOR x IN cur_uctr (l_wrong_app_rec.app_no, l_expunge_choice_det(l_loc)) LOOP
536 igs_uc_transactions_pkg.delete_row ( x_rowid => x.ROWID );
537 END LOOP;
538
539 DELETE igs_uc_istarc_ints
540 WHERE appno = l_wrong_app_rec.app_no
541 AND choiceno = l_expunge_choice_det(l_loc);
542
543 DELETE igs_uc_ioffer_ints
544 WHERE appno = l_wrong_app_rec.app_no
545 AND choiceno = l_expunge_choice_det(l_loc);
546
547 fnd_message.set_name('IGS','IGS_UC_XPG_APP_CHO_REC_COMP');
548 fnd_message.set_token('APP_NO', TO_CHAR(l_wrong_app_rec.app_no));
549 fnd_message.set_token('CHOICE_NO',TO_CHAR(l_expunge_choice_det(l_loc)));
550 fnd_file.put_line(fnd_file.log,fnd_message.get());
551
552 END IF;
553
554 END LOOP; -- End of the Choices (pl/sql table) Loop FOR UCAS Wrong Application
555
556 --Check if any Application Choice records exists for the current Application Number
557 l_rowid := NULL;
558 l_ucas_app_expunged := 'N';
559 OPEN cur_ucapch(l_wrong_app_rec.app_no, NULL);
560 FETCH cur_ucapch INTO l_rowid, l_choice_no;
561 CLOSE cur_ucapch;
562
563 IF l_rowid IS NULL THEN
564 l_ucas_app_expunged := 'Y';
565 END IF;
566
567 l_pe_alt_pers_id_closed := 'N';
568
569 --When there are no applications choices available for the current applicant
570 IF l_ucas_app_expunged = 'Y' THEN
571 --If all are applications Instances suspended then do the End Date FOR Person Alternate ID records.
572 l_pe_alt_pers_id_closed := 'Y';
573 OPEN cur_alt_pers_id (l_wrong_app_rec.app_no);
574 FETCH cur_alt_pers_id INTO l_alt_pers_id_rec;
575
576 IF cur_alt_pers_id%FOUND THEN
577 BEGIN
578 igs_pe_alt_pers_id_pkg.Update_Row ( x_mode => 'R',
579 x_rowid => l_alt_pers_id_rec.row_id,
580 x_pe_person_id => l_alt_pers_id_rec.pe_person_id,
581 x_api_person_id => l_alt_pers_id_rec.api_person_id,
582 x_api_person_id_uf => l_alt_pers_id_rec.api_person_id_uf,
583 x_person_id_type => l_alt_pers_id_rec.person_id_type,
584 x_start_dt => l_alt_pers_id_rec.start_dt,
585 x_end_dt => SYSDATE ,
586 x_attribute_category => l_alt_pers_id_rec.attribute_category,
587 x_attribute1 => l_alt_pers_id_rec.attribute1,
588 x_attribute2 => l_alt_pers_id_rec.attribute2,
589 x_attribute3 => l_alt_pers_id_rec.attribute3,
590 x_attribute4 => l_alt_pers_id_rec.attribute4,
591 x_attribute5 => l_alt_pers_id_rec.attribute5,
592 x_attribute6 => l_alt_pers_id_rec.attribute6,
593 x_attribute7 => l_alt_pers_id_rec.attribute7,
594 x_attribute8 => l_alt_pers_id_rec.attribute8,
595 x_attribute9 => l_alt_pers_id_rec.attribute9,
596 x_attribute10 => l_alt_pers_id_rec.attribute10,
597 x_attribute11 => l_alt_pers_id_rec.attribute11,
598 x_attribute12 => l_alt_pers_id_rec.attribute12,
599 x_attribute13 => l_alt_pers_id_rec.attribute13,
600 x_attribute14 => l_alt_pers_id_rec.attribute14,
601 x_attribute15 => l_alt_pers_id_rec.attribute15,
602 x_attribute16 => l_alt_pers_id_rec.attribute16,
603 x_attribute17 => l_alt_pers_id_rec.attribute17,
604 x_attribute18 => l_alt_pers_id_rec.attribute18,
605 x_attribute19 => l_alt_pers_id_rec.attribute19,
606 x_attribute20 => l_alt_pers_id_rec.attribute20,
607 x_region_cd => l_alt_pers_id_rec.region_cd);
608
609 fnd_message.set_name('IGS','IGS_UC_END_DT_ALT_PID_COMP');
610 fnd_message.set_token('APP_NO', l_wrong_app_rec.app_no);
611 fnd_file.put_line(fnd_file.log,fnd_message.get());
612 EXCEPTION
613 WHEN OTHERS THEN
614 l_pe_alt_pers_id_closed := 'N';
615 l_mesg_data := NULL;
616 l_msg_index := NULL;
617 OPEN cur_per_no(l_alt_pers_id_rec.pe_person_id);
618 FETCH cur_per_no INTO l_person_no;
619 CLOSE cur_per_no;
620 IGS_GE_MSG_STACK.GET(IGS_GE_MSG_STACK.COUNT_MSG,FND_API.G_FALSE, l_mesg_data, l_msg_index);
621 fnd_message.set_name('IGS','IGS_UC_END_DT_ALT_PID_ERR');
622 fnd_message.set_token('PER_NO', l_person_no);
623 fnd_message.set_token('ALT_PID', l_alt_pers_id_rec.api_person_id);
624 fnd_message.set_token('PID_TYPE',l_alt_pers_id_rec.person_id_type);
625 fnd_file.put_line(fnd_file.log,fnd_message.get()||' - '||l_mesg_data);
626 END;
627 END IF;
628 CLOSE cur_alt_pers_id;
629
630 END IF; --End of check FOR all OSS Application Instances are suspended or not.
631
632 l_ucas_app_recs_deleted := 'N';
633
634 --Check if ucas application is expunged and Alternate IDs are end dated.
635 IF l_ucas_app_expunged = 'Y' AND l_pe_alt_pers_id_closed = 'Y' THEN
636 l_ucas_app_recs_deleted := 'Y';
637 BEGIN
638 --Call the sub procedure to delete Wrong Applicant records from UCAS Interface tables.
639 delete_ucas_interface_rec(l_wrong_app_rec.app_no);
640 fnd_message.set_name('IGS','IGS_UC_XPG_INT_REC_COMP');
641 fnd_file.put_line(fnd_file.log,fnd_message.get());
642 EXCEPTION
643 WHEN OTHERS THEN
644 l_ucas_app_recs_deleted := 'N';
645 l_mesg_data := NULL;
646 l_msg_index := NULL;
647 IGS_GE_MSG_STACK.GET(IGS_GE_MSG_STACK.COUNT_MSG,FND_API.G_FALSE, l_mesg_data, l_msg_index);
648 fnd_message.set_name('IGS','IGS_UC_XPG_INT_REC_ERR');
649 fnd_message.set_token('APP_NO', l_wrong_app_rec.app_no);
650 fnd_file.put_line(fnd_file.log,fnd_message.get()||' - '||l_mesg_data);
651 END;
652 END IF; ----End of Check FOR all oss applications are suspended and Alternate IDs are end dated.
653
654 -- IGS_UC_WRONG_APP.EXPUNGED can be set to 'Y' in following 2 conditions.
655 -- 1. All application choice details and related OSS application instances are expunged and
656 -- Alternate Person IDs are closed and Interface Records are also succussfully deleted
657 -- then mark the Wrong Applicant record as expunged.
658 -- 2. All application choice details maked as LOST are expunged and also the related OSS application
659 -- instances are suspended and there exists some Application Choices in IGS_UC_APP_CHOICES table
660 -- which are not marked as LOST then mark the Wrong Applicant as expunged.
661 IF ( l_ucas_app_expunged = 'Y' AND l_pe_alt_pers_id_closed = 'Y' AND l_ucas_app_recs_deleted = 'Y' ) OR
662 ( l_ucas_app_expunged = 'N' AND l_all_makred_app_inst_expunged = 'Y' ) THEN
663 BEGIN
664 igs_uc_wrong_app_pkg.update_row ( x_mode => 'R',
665 x_rowid => l_wrong_app_rec.row_id,
666 x_wrong_app_id => l_wrong_app_rec.wrong_app_id,
667 x_app_no => l_wrong_app_rec.app_no,
668 x_miscoded => l_wrong_app_rec.miscoded,
669 x_cancelled => l_wrong_app_rec.cancelled,
670 x_cancel_date => l_wrong_app_rec.cancel_date,
671 x_remark => l_wrong_app_rec.remark,
672 x_expunge => l_wrong_app_rec.expunge,
673 x_batch_id => l_wrong_app_rec.batch_id,
674 x_expunged => 'Y',
675 x_joint_admission_ind => l_wrong_app_rec.joint_admission_ind,
676 x_choice1_lost => l_wrong_app_rec.choice1_lost,
677 x_choice2_lost => l_wrong_app_rec.choice2_lost,
678 x_choice3_lost => l_wrong_app_rec.choice3_lost,
679 x_choice4_lost => l_wrong_app_rec.choice4_lost,
680 x_choice5_lost => l_wrong_app_rec.choice5_lost,
681 x_choice6_lost => l_wrong_app_rec.choice6_lost,
682 x_choice7_lost => l_wrong_app_rec.choice7_lost);
683 -- Display the Application level expunge message only when complete Application details are expunged.
684 IF l_ucas_app_expunged = 'Y' THEN
685 fnd_message.set_name('IGS','IGS_UC_XPG_APP_NO_REC_COMP');
686 fnd_message.set_token('APP_NO', l_wrong_app_rec.app_no);
687 fnd_file.put_line(fnd_file.log,fnd_message.get());
688 fnd_file.put_line(fnd_file.log,' ');
689 END IF;
690 EXCEPTION
691 WHEN OTHERS THEN
692 l_mesg_data := NULL;
693 l_msg_index := NULL;
694 IGS_GE_MSG_STACK.GET(IGS_GE_MSG_STACK.COUNT_MSG,FND_API.G_FALSE, l_mesg_data, l_msg_index);
695 fnd_message.set_name('IGS','IGS_UC_MARK_APP_EXPUNGED_ERR');
696 fnd_message.set_token('APP_NO', l_wrong_app_rec.app_no);
697 fnd_file.put_line(fnd_file.log,fnd_message.get()||' - '||l_mesg_data);
698 END;
699 END IF;
700
701 END LOOP; -- End of the UCAS Wrong Applications Loop
702
703 END IF; --End of Setup Complete Check.
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 ROLLBACK;
708 retcode := 2;
709 fnd_message.set_name( 'IGS','IGS_GE_UNHANDLED_EXP');
710 fnd_message.set_token('NAME','igs_uc_expunge_app.expunge_proc'||' - '||SQLERRM);
711 errbuf := fnd_message.get;
712 igs_ge_msg_stack.conc_exception_hndl;
713
714 END expunge_proc;
715
716 END igs_uc_expunge_app;
[Home] [Help]