[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_003
Source
1 PACKAGE BODY IGS_AD_IMP_003 AS
2 /* $Header: IGSAD81B.pls 120.3 2006/07/31 06:25:35 apadegal noship $ */
3
4 -- Start of Main Procedure Process Applicant Academic Interests
5 /*----------------------------------------------------------------------------------
6 || Created By :
7 || Created On :
8 || Purpose : This procedure process the Application
9 || Known limitations, enhancements or remarks :
10 || Change History :
11 || Who When What
12 || samaresh 24-JAN-2002 The table Igs_ad_appl_int has been obsoleted
13 || new table igs_ad_apl_int has been created
14 || as a part of build ADI - Import Prc Changes
15 || bug# 2191058
16 || kumma 17-OCT-2002 Replaced eligibility_status_id with eligibility_status_cd and
17 || replaced athletic_prg_cd with athletic_prg_code.
18 || Replaced igs_ad_code_classes with igs_lookup_values 2608360
19 || npalanis 30-OCT-2002 Bug : 2608360
20 || upper function written for eligibility_status_cd and athletic_prg_code
21 || pkpatel 6-NOV-2003 Bug 3130316 (Import Process Enhancement) MOved all the Athletics related code to IGSAD90B.pls
22 --------------------------------------------------------------------------------*/
23
24 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
25 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
26 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
27 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
28
29 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
30 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
31 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
32 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
33 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
34 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
35 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
36 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
37 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
38 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
39 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
40 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
41 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
42 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
43 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
44 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
45
46 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
47 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
48 cst_ec_val_E702 CONSTANT VARCHAR2(4) := 'E702';
49 cst_ec_val_e700 CONSTANT VARCHAR2(4) := 'E700';
50 cst_ec_val_e701 CONSTANT VARCHAR2(4) := 'E701';
51
52 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
53 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
54 cst_unique_record CONSTANT NUMBER := 1;
55
56 PROCEDURE prc_acad_int(
57 p_interface_run_id IN NUMBER,
58 p_enable_log IN VARCHAR2,
59 p_category_meaning IN VARCHAR2,
60 p_rule IN VARCHAR2)
61 AS
62
63 CURSOR c_acadint IS
64 SELECT rowid,a.*
65 FROM igs_ad_acadint_int a
66 WHERE interface_run_id = p_interface_run_id
67 AND status = '2';
68
69 l_acadint_rec c_acadint%ROWTYPE;
70
71 l_records_processed NUMBER := 0;
72
73 l_msg_at_index NUMBER := 0;
74 l_return_status VARCHAR2(1);
75 l_msg_count NUMBER;
76 l_msg_data VARCHAR2(2000);
77 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
78
79 l_prog_label VARCHAR2(100);
80 p_error_code VARCHAR2(30);
81 p_status VARCHAR2(1);
82 l_error_code VARCHAR2(30);
83 l_request_id NUMBER;
84 l_label VARCHAR2(100);
85 l_debug_str VARCHAR2(2000);
86 l_enable_log VARCHAR2(1);
87 l_rowid VARCHAR2(25);
88 l_error_text VARCHAR2(2000);
89 l_error_text1 VARCHAR2(2000);
90 l_type VARCHAR2(1);
91 l_status VARCHAR2(1);
92 l_acad_int_id NUMBER;
93
94 l_admission_cat VARCHAR2(10);
95 l_s_admission_process_type VARCHAR2(30);
96
97 BEGIN
98
99 l_msg_at_index := igs_ge_msg_stack.count_msg;
100
101 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
102
103 IF (l_request_id IS NULL) THEN
104 l_request_id := fnd_global.conc_request_id;
105 END IF;
106
107 l_label := 'igs.plsql.igs_ad_imp_003.prc_acad_int.begin';
108
109 l_debug_str := 'Interface Academic Interest ID: '|| l_acadint_rec.interface_acadint_id;
110
111 fnd_log.string_with_context( fnd_log.level_procedure, l_label,l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
112 END IF;
113
114 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
115
116 UPDATE igs_ad_acadint_int a
117 SET status = '3',
118 error_code = 'E678',
119 error_text = l_error_text1
120 WHERE
121 interface_run_id = p_interface_run_id
122 AND status = '2'
123 AND EXISTS (SELECT 1 FROM igs_ad_acad_interest b
124 WHERE b.person_id = a.person_id
125 AND b.admission_appl_number = a.admission_appl_number
126 AND b.field_of_study = a.field_of_study );
127
128 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E420', 8405);
129
130 UPDATE igs_ad_acadint_int a
131 SET status = '3',
132 error_code = 'E420',
133 error_text = l_error_text1
134 WHERE interface_run_id = p_interface_run_id
135 AND status = '2'
136 AND NOT EXISTS ( SELECT 'X'
137 FROM igs_ps_fld_of_study_all b
138 WHERE b.field_of_study = a.field_of_study
139 AND b.closed_ind = 'N' );
140
141 FOR c_acadint_rec IN c_acadint LOOP
142 BEGIN
143
144 SAVEPOINT acadint_save;
145
146 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => c_acadint_rec.admission_application_type,
147 p_admission_cat => l_admission_cat,
148 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
149
150 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
151 p_s_admission_process_type => l_s_admission_process_type,
152 p_s_admission_step_type => 'ACAD-INTEREST') = 'FALSE' THEN
153
154 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
155 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
156 FND_MESSAGE.SET_TOKEN ('APPLTYPE', c_acadint_rec.admission_application_type);
157
158 l_error_text := FND_MESSAGE.GET;
159 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
160
161 UPDATE igs_ad_acadint_int
162 SET status = cst_s_val_3,
163 error_code = cst_ec_val_E701,
164 error_text = NVL(l_error_text,l_error_text1)
165 WHERE rowid = c_acadint_rec.rowid;
166
167 l_error_text := NULL;
168 l_error_text1 := NULL;
169
170 ELSE
171 l_rowid := NULL;
172 igs_ad_acad_interest_pkg.insert_row
173 (
174 x_rowid => l_rowid,
175 x_acad_interest_id => l_acad_int_id,
176 x_person_id => c_acadint_rec.person_id ,
177 x_admission_appl_number => c_acadint_rec.admission_appl_number,
178 x_field_of_study => c_acadint_rec.field_of_study ,
179 x_mode => 'R'
180 );
181
182 igs_ad_gen_016.extract_msg_from_stack (
183 p_msg_at_index => l_msg_at_index,
184 p_return_status => l_return_status,
185 p_msg_count => l_msg_count,
186 p_msg_data => l_msg_data,
187 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
188
189 IF l_msg_count > 0 THEN
190 l_error_text := l_msg_data;
191 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
192 END IF;
193
194 IF l_type = 'E' THEN
195 ROLLBACK TO acadint_save;
196 UPDATE igs_ad_acadint_int
197 SET status = cst_s_val_3,
198 error_code = cst_ec_val_E322,
199 error_text = l_error_text
200 WHERE rowid = c_acadint_rec.rowid;
201
202 IF l_enable_log = 'Y' THEN
203 igs_ad_imp_001.logerrormessage(c_acadint_rec.interface_acadint_id,l_msg_data);
204 END IF;
205
206 ELSIF l_type = 'S' THEN
207 UPDATE igs_ad_acadint_int
208 SET status = cst_s_val_4,
209 error_code = cst_ec_val_E702,
210 error_text = l_error_text
211 WHERE rowid = c_acadint_rec.rowid;
212
213 IF l_enable_log = 'Y' THEN
214 igs_ad_imp_001.logerrormessage(c_acadint_rec.interface_acadint_id,l_msg_data);
215 END IF;
216
217 ELSIF l_type IS NULL THEN
218 UPDATE igs_ad_acadint_int
219 SET status = cst_s_val_1,
220 error_code = NULL,
221 error_text = NULL
222 WHERE rowid = c_acadint_rec.rowid;
223
224 l_error_text := NULL;
225
226 END IF;
227
228 END IF;
229
230 ELSE
231
232 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
233 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
234 FND_MESSAGE.SET_TOKEN ('APPLTYPE', c_acadint_rec.admission_application_type);
235
236 l_error_text := FND_MESSAGE.GET;
237 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
238
239 UPDATE igs_ad_acadint_int
240 SET status = cst_s_val_3,
241 error_code = cst_ec_val_E701,
242 error_text = NVL(l_error_text,l_error_text1)
243 WHERE rowid = c_acadint_rec.rowid;
244
245 END IF;
246
247 l_error_text := NULL;
248 l_records_processed := l_records_processed +1;
249
250 EXCEPTION
251
252 WHEN OTHERS THEN
253
254 l_status := '3';
255 l_error_code := 'E322';
256
257 igs_ad_gen_016.extract_msg_from_stack (
258 p_msg_at_index => l_msg_at_index,
259 p_return_status => l_return_status,
260 p_msg_count => l_msg_count,
261 p_msg_data => l_msg_data,
262 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
263
264 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
265
266 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
267 IF l_enable_log = 'Y' THEN
268 igs_ad_imp_001.logerrormessage(c_acadint_rec.interface_acadint_id,l_msg_data);
269 END IF;
270 ELSE
271
272 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
273 l_label := 'igs.plsql.igs_ad_imp_003.prc_acad_int.exception '||'E322';
274
275 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
276 fnd_message.set_token('CONTEXT',c_acadint_rec.interface_appl_id);
277 fnd_message.set_token('ERROR', l_error_text);
278
279 l_debug_str := fnd_message.get;
280
281 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
282 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
283 END IF;
284 END IF;
285
286 ROLLBACK TO acadint_save;
287
288 UPDATE igs_ad_acadint_int
289 SET status = cst_s_val_3,
290 error_code = l_error_code ,
291 error_text = l_error_text
292 WHERE rowid = c_acadint_rec.rowid;
293
294 l_error_text := NULL;
295 l_records_processed := l_records_processed + 1;
296 END;
297
298 IF l_records_processed = 100 THEN
299 COMMIT;
300 l_records_processed := 0;
301 END IF;
302 END LOOP
303 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
304 COMMIT;
305 -- END IF;
306
307 END prc_acad_int;
308
309 --
310 -- End of Main Procedure PRC_ACAD_INT
311
312 PROCEDURE prc_apcnt_indt(
313 p_interface_run_id IN NUMBER,
314 p_enable_log IN VARCHAR2,
315 p_category_meaning IN VARCHAR2,
316 p_rule IN VARCHAR2)
317 AS
318
319 CURSOR c_appint IS
320 SELECT rowid,a.*
321 FROM igs_ad_appint_int a
322 WHERE interface_run_id = p_interface_run_id
323 AND status = '2';
324
325 l_appint_rec c_appint%ROWTYPE;
326
327 l_records_processed NUMBER := 0;
328
329 l_msg_at_index NUMBER := 0;
330 l_return_status VARCHAR2(1);
331 l_msg_count NUMBER;
332 l_msg_data VARCHAR2(2000);
333 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
334
335 l_prog_label VARCHAR2(100);
336 p_error_code VARCHAR2(30);
337 p_status VARCHAR2(1);
338 l_error_code VARCHAR2(30);
339 l_request_id NUMBER;
340 l_label VARCHAR2(100);
341 l_debug_str VARCHAR2(2000);
342 l_enable_log VARCHAR2(1);
343 l_rowid VARCHAR2(25);
344 l_error_text VARCHAR2(2000);
345 l_error_text1 VARCHAR2(2000);
346 l_type VARCHAR2(1);
347 l_status VARCHAR2(1);
348 l_app_int_id NUMBER;
349
350 l_admission_cat VARCHAR2(10);
351 l_s_admission_process_type VARCHAR2(30);
352
353 BEGIN
354
355 l_msg_at_index := igs_ge_msg_stack.count_msg;
356
357 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
358
359 IF (l_request_id IS NULL) THEN
360 l_request_id := fnd_global.conc_request_id;
361 END IF;
362
363 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_indt.begin';
364
365 l_debug_str := 'Interface Application Intent ID: '|| l_appint_rec.interface_appint_id;
366
367 fnd_log.string_with_context( fnd_log.level_procedure, l_label,l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
368 END IF;
369
370 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
371
372 UPDATE igs_ad_appint_int a
373 SET status = '3',
374 error_code = 'E678',
375 error_text = l_error_text1
376 WHERE
377 interface_run_id = p_interface_run_id
378 AND status = '2'
379 AND EXISTS (SELECT 1 FROM igs_ad_app_intent b
380 WHERE b.person_id = a.person_id
381 AND b.admission_appl_number = a.admission_appl_number
382 AND b.intent_type_id = a.intent_type_id);
383
384 FOR l_appint_rec IN c_appint LOOP
385 l_error_code := NULL; -- 5386694 - re-intialize this var to null
386
387 BEGIN
388
389 SAVEPOINT appint_save;
390
391 igs_ge_msg_stack.initialize;
392
393
394 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => l_appint_rec.admission_application_type,
395 p_admission_cat => l_admission_cat,
396 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
397
398 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
399 p_s_admission_process_type => l_s_admission_process_type,
400 p_s_admission_step_type => 'APPL-INTENT') = 'FALSE' THEN
401
402 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
403 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
404 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_appint_rec.admission_application_type);
405 l_error_text := FND_MESSAGE.GET;
406 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
407
408 UPDATE igs_ad_appint_int
409 SET status = cst_s_val_3,
410 error_code = cst_ec_val_E701,
411 error_text = NVL(l_error_text,l_error_code)
412 WHERE rowid = l_appint_rec.rowid;
413
414 l_error_text := NULL;
415 l_error_text1 := NULL;
416
417 ELSE
418
419 IF NOT IGS_AD_IMP_018.validate_desc_flex
420 (
421 p_attribute_category => l_appint_rec.attribute_category,
422 p_attribute1 => l_appint_rec.attribute1,
423 p_attribute2 => l_appint_rec.attribute2,
424 p_attribute3 => l_appint_rec.attribute3,
425 p_attribute4 => l_appint_rec.attribute4,
426 p_attribute5 => l_appint_rec.attribute5,
427 p_attribute6 => l_appint_rec.attribute6,
428 p_attribute7 => l_appint_rec.attribute7,
429 p_attribute8 => l_appint_rec.attribute8,
430 p_attribute9 => l_appint_rec.attribute9,
431 p_attribute10 => l_appint_rec.attribute10,
432 p_attribute11 => l_appint_rec.attribute11,
433 p_attribute12 => l_appint_rec.attribute12,
434 p_attribute13 => l_appint_rec.attribute13,
435 p_attribute14 => l_appint_rec.attribute14,
436 p_attribute15 => l_appint_rec.attribute15,
437 p_attribute16 => l_appint_rec.attribute16,
438 p_attribute17 => l_appint_rec.attribute17,
439 p_attribute18 => l_appint_rec.attribute18,
440 p_attribute19 => l_appint_rec.attribute19,
441 p_attribute20 => l_appint_rec.attribute20,
442 p_desc_flex_name => 'IGS_AD_APP_INTENT_FLEX'
443 ) THEN
444
445 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E423', 8405);
446
447 UPDATE igs_ad_appint_int
448 SET status = '3',
449 error_code = 'E423',
450 error_text = l_error_text1
451 WHERE rowid = l_appint_rec.rowid;
452
453 l_error_text1 := NULL;
454
455 END IF;
456 IF l_error_code IS NULL THEN
457 l_rowid := NULL;
458 igs_ad_app_intent_pkg.insert_row (
459 x_rowid => l_rowid,
460 x_app_intent_id => l_app_int_id,
461 x_person_id => l_appint_rec.person_id,
462 x_admission_appl_number => l_appint_rec.admission_appl_number,
463 x_intent_type_id => l_appint_rec.intent_type_id,
464 x_attribute_category => l_appint_rec.attribute_category,
465 x_attribute1 => l_appint_rec.attribute1,
466 x_attribute2 => l_appint_rec.attribute2,
467 x_attribute3 => l_appint_rec.attribute3,
468 x_attribute4 => l_appint_rec.attribute4,
469 x_attribute5 => l_appint_rec.attribute5,
470 x_attribute6 => l_appint_rec.attribute6,
471 x_attribute7 => l_appint_rec.attribute7,
472 x_attribute8 => l_appint_rec.attribute8,
473 x_attribute9 => l_appint_rec.attribute9,
474 x_attribute10 => l_appint_rec.attribute10,
475 x_attribute11 => l_appint_rec.attribute11,
476 x_attribute12 => l_appint_rec.attribute12,
477 x_attribute13 => l_appint_rec.attribute13,
478 x_attribute14 => l_appint_rec.attribute14,
479 x_attribute15 => l_appint_rec.attribute15,
480 x_attribute16 => l_appint_rec.attribute16,
481 x_attribute17 => l_appint_rec.attribute17,
482 x_attribute18 => l_appint_rec.attribute18,
483 x_attribute19 => l_appint_rec.attribute19,
484 x_attribute20 => l_appint_rec.attribute20,
485 x_mode => 'R'
486 );
487
488 igs_ad_gen_016.extract_msg_from_stack (
489 p_msg_at_index => l_msg_at_index,
490 p_return_status => l_return_status,
491 p_msg_count => l_msg_count,
492 p_msg_data => l_msg_data,
493 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
494
495 IF l_msg_count > 0 THEN
496 l_error_text := l_msg_data;
497 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
498 END IF;
499
500 IF l_type = 'E' THEN
501 ROLLBACK TO appint_save;
502 UPDATE igs_ad_appint_int
503 SET status = cst_s_val_3,
504 error_code = cst_ec_val_E322,
505 error_text = l_error_text
506 WHERE rowid = l_appint_rec.rowid;
507
508 IF l_enable_log = 'Y' THEN
509 igs_ad_imp_001.logerrormessage(l_appint_rec.interface_appint_id,l_msg_data);
510 END IF;
511
512 ELSIF l_type = 'S' THEN
513 UPDATE igs_ad_appint_int
514 SET status = cst_s_val_4,
515 error_code = cst_ec_val_E702,
516 error_text = l_error_text
517 WHERE rowid = l_appint_rec.rowid;
518
519 IF l_enable_log = 'Y' THEN
520 igs_ad_imp_001.logerrormessage(l_appint_rec.interface_appint_id,l_msg_data);
521 END IF;
522
523 ELSIF l_type IS NULL THEN
524 UPDATE igs_ad_appint_int
525 SET status = cst_s_val_1,
526 error_code = NULL,
527 error_text = NULL
528 WHERE rowid = l_appint_rec.rowid;
529
530 END IF;
531 END IF;
532 END IF;
533 ELSE
534
535 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
536 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
537 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_appint_rec.admission_application_type);
538
539 l_error_text := FND_MESSAGE.GET;
540 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
541
542 UPDATE igs_ad_appint_int
543 SET status = cst_s_val_3,
544 error_code = cst_ec_val_E701,
545 error_text = NVL(l_error_text,l_error_text1)
546 WHERE rowid = l_appint_rec.rowid;
547
548 END IF;
549
550 l_error_text := NULL;
551 l_error_text1 := NULL;
552
553 l_records_processed := l_records_processed +1;
554
555 EXCEPTION
556
557 WHEN OTHERS THEN
558 l_status := '3';
559 l_error_code := 'E322';
560
561 igs_ad_gen_016.extract_msg_from_stack (
562 p_msg_at_index => l_msg_at_index,
563 p_return_status => l_return_status,
564 p_msg_count => l_msg_count,
565 p_msg_data => l_msg_data,
566 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
567
568 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
569
570 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
571 IF l_enable_log = 'Y' THEN
572 igs_ad_imp_001.logerrormessage(l_appint_rec.interface_appint_id,l_msg_data);
573 END IF;
574
575 ELSE
576
577 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
578 l_label := 'igs.plsql.igs_ad_imp_003.prc_acad_int.exception '||'E322';
579
580 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
581 fnd_message.set_token('CONTEXT',l_appint_rec.interface_appl_id);
582 fnd_message.set_token('ERROR', l_error_text);
583
584 l_debug_str := fnd_message.get;
585
586 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
587 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
588 END IF;
589 END IF;
590
591 ROLLBACK TO appint_save;
592
593 UPDATE igs_ad_appint_int
594 SET status = cst_s_val_3,
595 error_code = l_error_code ,
596 error_text = l_error_text
597 WHERE rowid = l_appint_rec.rowid;
598
599 l_error_text := NULL;
600
601 l_records_processed := l_records_processed + 1;
602 END;
603
604 IF l_records_processed = 100 THEN
605 COMMIT;
606 l_records_processed := 0;
607 END IF;
608 END LOOP
609 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
610 COMMIT;
611 -- END IF;
612
613
614 END prc_apcnt_indt;
615
616 PROCEDURE prc_apcnt_oth_inst_apld(
617 p_interface_run_id IN NUMBER,
618 p_enable_log IN VARCHAR2,
619 p_category_meaning IN VARCHAR2,
620 p_rule IN VARCHAR2)
621 AS
622
623 CURSOR c_oth_inst IS
624 SELECT rowid,a.*
625 FROM igs_ad_othinst_int a
626 WHERE interface_run_id = p_interface_run_id
627 AND status = '2';
628
629 l_oth_inst_rec c_oth_inst%ROWTYPE;
630
631 l_records_processed NUMBER := 0;
632
633 l_msg_at_index NUMBER := 0;
634 l_return_status VARCHAR2(1);
635 l_msg_count NUMBER;
636 l_msg_data VARCHAR2(2000);
637 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
638
639 l_prog_label VARCHAR2(100);
640 p_error_code VARCHAR2(30);
641 p_status VARCHAR2(1);
642 l_error_code VARCHAR2(30);
643 l_request_id NUMBER;
644 l_label VARCHAR2(100);
645 l_debug_str VARCHAR2(2000);
646 l_enable_log VARCHAR2(1);
647 l_rowid VARCHAR2(25);
648 l_error_text VARCHAR2(2000);
649 l_error_text1 VARCHAR2(2000);
650 l_type VARCHAR2(1);
651 l_status VARCHAR2(1);
652 l_oth_inst_id NUMBER;
653
654 l_admission_cat VARCHAR2(10);
655 l_s_admission_process_type VARCHAR2(30);
656
657 BEGIN
658
659 l_msg_at_index := igs_ge_msg_stack.count_msg;
660
661 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
662
663 IF (l_request_id IS NULL) THEN
664 l_request_id := fnd_global.conc_request_id;
665 END IF;
666
667 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_oth_inst_apld.begin';
668
669 l_debug_str := 'Interface Other Institution ID: '|| l_oth_inst_rec.interface_othinst_id;
670
671 fnd_log.string_with_context( fnd_log.level_procedure, l_label,
672 l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
673 END IF;
674
675 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
676
677 UPDATE igs_ad_othinst_int a
678 SET status = '3',
679 error_code = 'E678',
680 error_text = l_error_text1
681 WHERE
682 interface_run_id = p_interface_run_id
683 AND status = '2'
684 AND EXISTS (SELECT 1 FROM igs_ad_other_inst b
685 WHERE b.person_id = a.person_id
686 AND b.admission_appl_number = a.admission_appl_number
687 AND b.institution_code = a.institution_cd );
688
689 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E425', 8405);
690
691 UPDATE igs_ad_othinst_int a
692 SET status = '3',
693 error_code = 'E425',
694 error_text = l_error_text1
695 WHERE interface_run_id = p_interface_run_id
696 AND status = '2'
697 AND NOT EXISTS (SELECT 'X'
698 FROM IGS_OR_INST_ORG_BASE_V b
699 WHERE a.institution_cd = b.party_number -- 5386694 (was wrongly compared with "ou_institution_cd" from the bug 4947103)
700 AND b.inst_org_ind = 'I'
701 AND institution_status IN
702 (SELECT institution_status
703 FROM igs_or_inst_stat
704 WHERE s_institution_status = 'ACTIVE')
705 );
706
707 FOR l_oth_inst_rec IN c_oth_inst LOOP
708 BEGIN
709
710 SAVEPOINT oth_inst_save;
711
712 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => l_oth_inst_rec.admission_application_type,
713 p_admission_cat => l_admission_cat,
714 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
715
716 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
717 p_s_admission_process_type => l_s_admission_process_type,
718 p_s_admission_step_type => 'OTH-INST-APPL') = 'FALSE' THEN
719
720 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
721 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
722 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_oth_inst_rec.admission_application_type);
723
724 l_error_text := FND_MESSAGE.GET;
725 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
726
727 UPDATE igs_ad_othinst_int
728 SET status = cst_s_val_3,
729 error_code = cst_ec_val_E701,
730 error_text = NVL(l_error_text,l_error_text1)
731 WHERE rowid = l_oth_inst_rec.rowid;
732
733 l_error_text := NULL;
734
735 ELSE
736 l_rowid := NULL;
737
738 igs_ad_other_inst_pkg.insert_row
739 (
740 x_rowid => l_rowid,
741 x_other_inst_id => l_oth_inst_id,
742 x_person_id => l_oth_inst_rec.person_id,
743 x_admission_appl_number => l_oth_inst_rec.admission_appl_number,
744 x_institution_code => l_oth_inst_rec.institution_cd,
745 x_mode => 'R'
746 );
747 igs_ad_gen_016.extract_msg_from_stack (
748 p_msg_at_index => l_msg_at_index,
749 p_return_status => l_return_status,
750 p_msg_count => l_msg_count,
751 p_msg_data => l_msg_data,
752 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
753
754 IF l_msg_count > 0 THEN
755 l_error_text := l_msg_data;
756 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
757 END IF;
758
759 IF l_type = 'E' THEN
760 ROLLBACK TO oth_inst_save;
761 UPDATE igs_ad_othinst_int
762 SET status = cst_s_val_3,
763 error_code = cst_ec_val_E322,
764 error_text = l_error_text
765 WHERE rowid = l_oth_inst_rec.rowid;
766
767 IF l_enable_log = 'Y' THEN
768 igs_ad_imp_001.logerrormessage(l_oth_inst_rec.interface_othinst_id,l_msg_data);
769 END IF;
770
771 ELSIF l_type = 'S' THEN
772 UPDATE igs_ad_othinst_int
773 SET status = cst_s_val_4,
774 error_code =cst_ec_val_E702,
775 error_text = l_error_text
776 WHERE rowid = l_oth_inst_rec.rowid;
777
778 IF l_enable_log = 'Y' THEN
779 igs_ad_imp_001.logerrormessage(l_oth_inst_rec.interface_othinst_id,l_msg_data);
780 END IF;
781
782 ELSIF l_type IS NULL THEN
783 UPDATE igs_ad_othinst_int
784 SET status = cst_s_val_1,
785 error_code = NULL,
786 error_text = NULL
787 WHERE rowid = l_oth_inst_rec.rowid;
788
789 l_error_text := NULL;
790
791 END IF;
792
793 END IF;
794
795 ELSE
796 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
797 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
798 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_oth_inst_rec.admission_application_type);
799
800 l_error_text := FND_MESSAGE.GET;
801 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
802
803 UPDATE igs_ad_othinst_int
804 SET status = cst_s_val_3,
805 error_code = cst_ec_val_E701,
806 error_text = NVL(l_error_text,l_error_text1)
807 WHERE rowid = l_oth_inst_rec.rowid;
808
809 END IF;
810
811 l_error_text := NULL;
812 l_records_processed := l_records_processed +1;
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 l_status := '3';
817 l_error_code := 'E322';
818
819 igs_ad_gen_016.extract_msg_from_stack (
820 p_msg_at_index => l_msg_at_index,
821 p_return_status => l_return_status,
822 p_msg_count => l_msg_count,
823 p_msg_data => l_msg_data,
824 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
825
826 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
827
828 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
829 IF l_enable_log = 'Y' THEN
830 igs_ad_imp_001.logerrormessage(l_oth_inst_rec.interface_othinst_id,l_msg_data);
831 END IF;
832 ELSE
833 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
834 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_oth_inst_apld.exception '||'E322';
835
836 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
837 fnd_message.set_token('CONTEXT',l_oth_inst_rec.interface_appl_id);
838 fnd_message.set_token('ERROR', l_error_text);
839
840 l_debug_str := fnd_message.get;
841
842 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
843 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
844 END IF;
845 END IF;
846
847 ROLLBACK TO oth_inst_save;
848
849 UPDATE igs_ad_othinst_int
850 SET status = cst_s_val_3,
851 error_code = l_error_code ,
852 error_text = l_error_text
853 WHERE rowid = l_oth_inst_rec.rowid;
854
855 l_error_text := NULL;
856 l_records_processed := l_records_processed + 1;
857 END;
858
859 IF l_records_processed = 100 THEN
860 COMMIT;
861 l_records_processed := 0;
862 END IF;
863 END LOOP
864 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
865 COMMIT;
866 -- END IF;
867
868 END prc_apcnt_oth_inst_apld;
869
870
871 PROCEDURE prc_apcnt_spl_intrst(
872 p_interface_run_id IN NUMBER,
873 p_enable_log IN VARCHAR2,
874 p_category_meaning IN VARCHAR2,
875 p_rule IN VARCHAR2)
876 AS
877
878 CURSOR c_spl_intrst IS
879 SELECT rowid,a.*
880 FROM igs_ad_splint_int a
881 WHERE interface_run_id = p_interface_run_id
882 AND status = '2';
883
884 l_spl_intrst_rec c_spl_intrst%ROWTYPE;
885
886 l_records_processed NUMBER := 0;
887
888 l_msg_at_index NUMBER := 0;
889 l_return_status VARCHAR2(1);
890 l_msg_count NUMBER;
891 l_msg_data VARCHAR2(2000);
892 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
893
894 l_prog_label VARCHAR2(100);
895 p_error_code VARCHAR2(30);
896 p_status VARCHAR2(1);
897 l_error_code VARCHAR2(30);
898 l_request_id NUMBER;
899 l_label VARCHAR2(100);
900 l_debug_str VARCHAR2(2000);
901 l_enable_log VARCHAR2(1);
902 l_rowid VARCHAR2(25);
903 l_error_text VARCHAR2(2000);
904 l_error_text1 VARCHAR2(2000);
905 l_type VARCHAR2(1);
906 l_status VARCHAR2(1);
907 l_spl_int_id NUMBER;
908
909 l_admission_cat VARCHAR2(10);
910 l_s_admission_process_type VARCHAR2(30);
911
912 BEGIN
913
914 l_msg_at_index := igs_ge_msg_stack.count_msg;
915
916 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
917
918 IF (l_request_id IS NULL) THEN
919 l_request_id := fnd_global.conc_request_id;
920 END IF;
921
922 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_spl_intrst.begin';
923
924 l_debug_str := 'Interface Special Interests ID: '|| l_spl_intrst_rec.interface_splint_id;
925
926 fnd_log.string_with_context( fnd_log.level_procedure, l_label,
927 l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
928 END IF;
929
930 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
931
932 UPDATE IGS_AD_SPLINT_INT a
933 SET status = '3',
934 error_code = 'E678',
935 error_text = l_error_text1
936 WHERE
937 interface_run_id = p_interface_run_id
938 AND status = '2'
939 AND EXISTS (SELECT 1 FROM igs_ad_spl_interests b
940 WHERE b.person_id = a.person_id
941 AND b.admission_appl_number = a.admission_appl_number
942 AND b.special_interest_type_id = a.special_interest_type_id);
943
944 FOR l_spl_intrst_rec IN c_spl_intrst LOOP
945 BEGIN
946
947 SAVEPOINT spl_intrst_save;
948
949 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => l_spl_intrst_rec.admission_application_type,
950 p_admission_cat => l_admission_cat,
951 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
952
953 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
954 p_s_admission_process_type => l_s_admission_process_type,
955 p_s_admission_step_type => 'SPL-INTEREST') = 'FALSE' THEN
956
957 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
958 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
959 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_spl_intrst_rec.admission_application_type);
960
961 l_error_text := FND_MESSAGE.GET;
962 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
963
964 UPDATE igs_ad_splint_int
965 SET status = cst_s_val_3,
966 error_code = cst_ec_val_E701,
967 error_text = NVL(l_error_text,l_error_text1)
968 WHERE rowid = l_spl_intrst_rec.rowid;
969
970 l_error_text := NULL;
971
972 ELSE
973 l_rowid := NULL;
974 igs_ad_spl_interests_pkg.insert_row (
975 x_rowid => l_rowid,
976 x_spl_interest_id => l_spl_int_id ,
977 x_person_id => l_spl_intrst_rec.person_id ,
978 x_admission_appl_number => l_spl_intrst_rec.admission_appl_number ,
979 x_special_interest_type_id => l_spl_intrst_rec.special_interest_type_id ,
980 x_mode => 'R'
981 );
982
983 igs_ad_gen_016.extract_msg_from_stack (
984 p_msg_at_index => l_msg_at_index,
985 p_return_status => l_return_status,
986 p_msg_count => l_msg_count,
987 p_msg_data => l_msg_data,
988 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
989
990 IF l_msg_count > 0 THEN
991 l_error_text := l_msg_data;
992 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
993 END IF;
994
995 IF l_type = 'E' THEN
996 ROLLBACK TO spl_intrst_save;
997 UPDATE IGS_AD_SPLINT_INT
998 SET status = cst_s_val_3,
999 error_code = cst_ec_val_E322,
1000 error_text = l_error_text
1001 WHERE rowid = l_spl_intrst_rec.rowid;
1002
1003 IF l_enable_log = 'Y' THEN
1004 igs_ad_imp_001.logerrormessage(l_spl_intrst_rec.interface_splint_id,l_msg_data);
1005 END IF;
1006
1007
1008 ELSIF l_type = 'S' THEN
1009 UPDATE IGS_AD_SPLINT_INT
1010 SET status = cst_s_val_4,
1011 error_code = cst_ec_val_E702,
1012 error_text = l_error_text
1013 WHERE rowid = l_spl_intrst_rec.rowid;
1014
1015 IF l_enable_log = 'Y' THEN
1016 igs_ad_imp_001.logerrormessage(l_spl_intrst_rec.interface_splint_id,l_msg_data);
1017 END IF;
1018
1019 ELSIF l_type IS NULL THEN
1020 UPDATE IGS_AD_SPLINT_INT
1021 SET status = cst_s_val_1,
1022 error_code = NULL,
1023 error_text = NULL
1024 WHERE rowid = l_spl_intrst_rec.rowid;
1025
1026 l_error_text := NULL;
1027
1028 END IF;
1029 END IF;
1030
1031 ELSE
1032
1033 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
1034 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
1035 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_spl_intrst_rec.admission_application_type);
1036
1037 l_error_text := FND_MESSAGE.GET;
1038 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
1039
1040 UPDATE igs_ad_splint_int
1041 SET status = cst_s_val_3,
1042 error_code = cst_ec_val_E701,
1043 error_text = NVL(l_error_text,l_error_text1)
1044 WHERE rowid = l_spl_intrst_rec.rowid;
1045 END IF;
1046
1047 l_error_text := NULL;
1048 l_records_processed := l_records_processed +1;
1049
1050 EXCEPTION
1051
1052 WHEN OTHERS THEN
1053
1054 l_status := '3';
1055 l_error_code := 'E322';
1056
1057 igs_ad_gen_016.extract_msg_from_stack (
1058 p_msg_at_index => l_msg_at_index,
1059 p_return_status => l_return_status,
1060 p_msg_count => l_msg_count,
1061 p_msg_data => l_msg_data,
1062 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1063
1064 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
1065
1066 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1067 IF l_enable_log = 'Y' THEN
1068 igs_ad_imp_001.logerrormessage(l_spl_intrst_rec.interface_splint_id,l_msg_data);
1069 END IF;
1070
1071 ELSE
1072
1073 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1074 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_spl_intrst.exception '||'E322';
1075
1076 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
1077 fnd_message.set_token('CONTEXT',l_spl_intrst_rec.interface_appl_id);
1078 fnd_message.set_token('ERROR', l_error_text);
1079
1080 l_debug_str := fnd_message.get;
1081
1082 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
1083 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1084 END IF;
1085 END IF;
1086
1087 ROLLBACK TO spl_intrst_save;
1088
1089 UPDATE IGS_AD_SPLINT_INT
1090 SET status = cst_s_val_3,
1091 error_code = l_error_code ,
1092 error_text = l_error_text
1093 WHERE rowid = l_spl_intrst_rec.rowid;
1094
1095 l_error_text := NULL;
1096 l_records_processed := l_records_processed + 1;
1097 END;
1098
1099 IF l_records_processed = 100 THEN
1100 COMMIT;
1101 l_records_processed := 0;
1102 END IF;
1103 END LOOP
1104 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
1105 COMMIT;
1106 -- END IF;
1107
1108 END prc_apcnt_spl_intrst;
1109
1110 PROCEDURE prc_apcnt_spl_tal(
1111 p_interface_run_id IN NUMBER,
1112 p_enable_log IN VARCHAR2,
1113 p_category_meaning IN VARCHAR2,
1114 p_rule IN VARCHAR2)
1115 AS
1116
1117 CURSOR c_spl_tal IS
1118 SELECT rowid,a.*
1119 FROM igs_ad_spltal_int a
1120 WHERE interface_run_id = p_interface_run_id
1121 AND status = '2';
1122
1123 l_spl_tal_rec c_spl_tal%ROWTYPE;
1124
1125 l_records_processed NUMBER := 0;
1126
1127 l_msg_at_index NUMBER := 0;
1128 l_return_status VARCHAR2(1);
1129 l_msg_count NUMBER;
1130 l_msg_data VARCHAR2(2000);
1131 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1132
1133 l_prog_label VARCHAR2(100);
1134 p_error_code VARCHAR2(30);
1135 p_status VARCHAR2(1);
1136 l_error_code VARCHAR2(30);
1137 l_request_id NUMBER;
1138 l_label VARCHAR2(100);
1139 l_debug_str VARCHAR2(2000);
1140 l_enable_log VARCHAR2(1);
1141 l_rowid VARCHAR2(25);
1142 l_error_text VARCHAR2(2000);
1143 l_error_text1 VARCHAR2(2000);
1144 l_type VARCHAR2(1);
1145 l_status VARCHAR2(1);
1146 l_spl_tal_id NUMBER;
1147
1148 l_admission_cat VARCHAR2(10);
1149 l_s_admission_process_type VARCHAR2(30);
1150
1151 BEGIN
1152
1153 l_msg_at_index := igs_ge_msg_stack.count_msg;
1154
1155 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1156
1157 IF (l_request_id IS NULL) THEN
1158 l_request_id := fnd_global.conc_request_id;
1159 END IF;
1160
1161 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_spl_tal.begin';
1162
1163 l_debug_str := 'Interface Special Talent ID: '|| l_spl_tal_rec.interface_spltal_id;
1164
1165 fnd_log.string_with_context( fnd_log.level_procedure, l_label,l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1166 END IF;
1167
1168 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
1169
1170 UPDATE IGS_AD_SPLTAL_INT a
1171 SET status = '3',
1172 error_code = 'E678',
1173 error_text = l_error_text1
1174 WHERE
1175 interface_run_id = p_interface_run_id
1176 AND status = '2'
1177 AND EXISTS (SELECT 1 FROM igs_ad_spl_talents b
1178 WHERE b.person_id = a.person_id
1179 AND b.admission_appl_number = a.admission_appl_number
1180 AND b.special_talent_type_id = a.special_talent_type_id);
1181
1182 FOR l_spl_tal_rec IN c_spl_tal LOOP
1183 BEGIN
1184
1185 SAVEPOINT spl_tal_save;
1186
1187 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => l_spl_tal_rec.admission_application_type,
1188 p_admission_cat => l_admission_cat,
1189 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
1190
1191 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
1192 p_s_admission_process_type => l_s_admission_process_type,
1193 p_s_admission_step_type => 'SPL-TALENT') = 'FALSE' THEN
1194
1195 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
1196 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
1197 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_spl_tal_rec.admission_application_type);
1198
1199 l_error_text := FND_MESSAGE.GET;
1200 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
1201
1202 UPDATE igs_ad_spltal_int
1203 SET status = cst_s_val_3,
1204 error_code = cst_ec_val_E701,
1205 error_text = NVL(l_error_text,l_error_text1)
1206 WHERE rowid = l_spl_tal_rec.rowid;
1207
1208 l_error_text := NULL;
1209
1210 ELSE
1211 l_rowid := NULL;
1212 igs_ad_spl_talents_pkg.insert_row(
1213 x_rowid => l_rowid,
1214 x_spl_talent_id => l_spl_tal_id,
1215 x_person_id => l_spl_tal_rec.person_id,
1216 x_admission_appl_number => l_spl_tal_rec.admission_appl_number ,
1217 x_special_talent_type_id => l_spl_tal_rec.special_talent_type_id,
1218 x_mode => 'R' );
1219
1220 igs_ad_gen_016.extract_msg_from_stack (
1221 p_msg_at_index => l_msg_at_index,
1222 p_return_status => l_return_status,
1223 p_msg_count => l_msg_count,
1224 p_msg_data => l_msg_data,
1225 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1226
1227 IF l_msg_count > 0 THEN
1228 l_error_text := l_msg_data;
1229 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
1230 END IF;
1231
1232 IF l_type = 'E' THEN
1233 ROLLBACK TO spl_tal_save;
1234 UPDATE IGS_AD_SPLTAL_INT
1235 SET status = cst_s_val_3,
1236 error_code = cst_ec_val_E322,
1237 error_text = l_error_text
1238 WHERE rowid = l_spl_tal_rec.rowid;
1239
1240 IF l_enable_log = 'Y' THEN
1241 igs_ad_imp_001.logerrormessage(l_spl_tal_rec.interface_spltal_id,l_msg_data);
1242 END IF;
1243
1244
1245 ELSIF l_type = 'S' THEN
1246 UPDATE IGS_AD_SPLTAL_INT
1247 SET status = cst_s_val_4,
1248 error_code = cst_ec_val_E702,
1249 error_text = l_error_text
1250 WHERE rowid = l_SPL_TAL_REC.rowid;
1251
1252 IF l_enable_log = 'Y' THEN
1253 igs_ad_imp_001.logerrormessage(l_spl_tal_rec.interface_spltal_id,l_msg_data);
1254 END IF;
1255
1256 ELSIF l_type IS NULL THEN
1257 UPDATE igs_ad_spltal_int
1258 SET status = cst_s_val_1,
1259 error_code = NULL,
1260 error_text = NULL
1261 WHERE rowid = l_spl_tal_rec.rowid;
1262
1263 END IF;
1264 END IF;
1265
1266 ELSE
1267
1268 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
1269 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
1270 FND_MESSAGE.SET_TOKEN ('APPLTYPE', l_spl_tal_rec.admission_application_type);
1271
1272 l_error_text := FND_MESSAGE.GET;
1273 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
1274
1275 UPDATE igs_ad_spltal_int
1276 SET status = cst_s_val_3,
1277 error_code = cst_ec_val_E701,
1278 error_text = NVL(l_error_text,l_error_text1)
1279 WHERE rowid = l_spl_tal_rec.rowid;
1280
1281 END IF;
1282
1283 l_error_text := NULL;
1284 l_records_processed := l_records_processed +1;
1285
1286 EXCEPTION
1287
1288 WHEN OTHERS THEN
1289
1290 l_status := '3';
1291 l_error_code := 'E322';
1292
1293 igs_ad_gen_016.extract_msg_from_stack (
1294 p_msg_at_index => l_msg_at_index,
1295 p_return_status => l_return_status,
1296 p_msg_count => l_msg_count,
1297 p_msg_data => l_msg_data,
1298 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1299
1300 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
1301
1302 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1303 IF l_enable_log = 'Y' THEN
1304 igs_ad_imp_001.logerrormessage(l_spl_tal_rec.interface_spltal_id,l_msg_data);
1305 END IF;
1306
1307 ELSE
1308
1309 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1310 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_spl_tal.exception '||'E322';
1311
1312 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
1313 fnd_message.set_token('CONTEXT',l_spl_tal_rec.interface_appl_id);
1314 fnd_message.set_token('ERROR', l_error_text);
1315
1316 l_debug_str := fnd_message.get;
1317
1318 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
1319 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1320 END IF;
1321 END IF;
1322
1323 ROLLBACK TO spl_tal_save;
1324
1325 UPDATE IGS_AD_SPLTAL_INT
1326 SET status = cst_s_val_3,
1327 error_code = l_error_code ,
1328 error_text = l_error_text
1329 WHERE rowid = l_spl_tal_rec.rowid;
1330
1331 l_error_text := NULL;
1332 l_records_processed := l_records_processed + 1;
1333 END;
1334
1335 IF l_records_processed = 100 THEN
1336 COMMIT;
1337 l_records_processed := 0;
1338 END IF;
1339 END LOOP
1340 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
1341 COMMIT;
1342 -- END IF;
1343
1344
1345 END prc_apcnt_spl_tal;
1346
1347
1348 PROCEDURE prc_pe_persstat_details(
1349 p_interface_run_id IN NUMBER,
1350 p_enable_log IN VARCHAR2,
1351 p_rule IN VARCHAR2)
1352 AS
1353
1354 CURSOR c_appl_pers IS
1355 SELECT rowid,a.*
1356 FROM igs_ad_perstmt_int a
1357 WHERE interface_run_id = p_interface_run_id
1358 AND status = '2';
1359
1360 l_appl_pers_rec c_appl_pers%ROWTYPE;
1361
1362 l_records_processed NUMBER := 0;
1363
1364 l_msg_at_index NUMBER := 0;
1365 l_return_status VARCHAR2(1);
1366 l_msg_count NUMBER;
1367 l_msg_data VARCHAR2(2000);
1368 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1369
1370 l_prog_label VARCHAR2(100);
1371 p_error_code VARCHAR2(30);
1372 p_status VARCHAR2(1);
1373 l_error_code VARCHAR2(30);
1374 l_request_id NUMBER;
1375 l_label VARCHAR2(100);
1376 l_debug_str VARCHAR2(2000);
1377 l_enable_log VARCHAR2(1);
1378 l_rowid VARCHAR2(25);
1379 l_error_text VARCHAR2(2000);
1380 l_error_text1 VARCHAR2(2000);
1381 l_type VARCHAR2(1);
1382 l_status VARCHAR2(1);
1383 l_appl_perstat_id NUMBER;
1384
1385
1386
1387 BEGIN
1388
1389 l_msg_at_index := igs_ge_msg_stack.count_msg;
1390
1391 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1392
1393 IF (l_request_id IS NULL) THEN
1394 l_request_id := fnd_global.conc_request_id;
1395 END IF;
1396
1397 l_label := 'igs.plsql.igs_ad_imp_003.prc_persstat_details.begin';
1398
1399 l_debug_str := 'Interface Personal Statement ID: '|| l_appl_pers_rec.interface_perstmt_id;
1400
1401 fnd_log.string_with_context( fnd_log.level_procedure, l_label,
1402 l_debug_str, NULL, NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1403 END IF;
1404
1405 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
1406
1407 UPDATE igs_ad_perstmt_int a
1408 SET status = '3',
1409 error_code = 'E678',
1410 error_text = l_error_text1
1411 WHERE
1412 interface_run_id = p_interface_run_id
1413 AND status = '2'
1414 AND EXISTS (SELECT 1 FROM igs_ad_appl_perstat b
1415 WHERE b.person_id = a.person_id
1416 AND b.admission_appl_number = a.admission_appl_number
1417 AND b.persl_stat_type = a.persl_stat_type
1418 AND TRUNC(b.date_received) = TRUNC(a.date_received));
1419
1420 FOR l_appl_pers_rec IN c_appl_pers LOOP
1421 BEGIN
1422
1423 SAVEPOINT appl_pers_save;
1424 l_rowid := NULL;
1425 igs_ad_appl_perstat_pkg.insert_row(
1426 x_rowid => l_rowid,
1427 x_appl_perstat_id => l_appl_perstat_id,
1428 x_person_id => l_appl_pers_rec.person_id,
1429 x_admission_appl_number => l_appl_pers_rec.admission_appl_number,
1430 x_persl_stat_type => l_appl_pers_rec.persl_stat_type,
1431 x_date_received => TRUNC(l_appl_pers_rec.date_received),
1432 x_mode => 'R');
1433
1434 igs_ad_gen_016.extract_msg_from_stack (
1435 p_msg_at_index => l_msg_at_index,
1436 p_return_status => l_return_status,
1437 p_msg_count => l_msg_count,
1438 p_msg_data => l_msg_data,
1439 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1440
1441 IF l_msg_count > 0 THEN
1442 l_error_text := l_msg_data;
1443 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
1444 END IF;
1445
1446 IF l_type = 'E' THEN
1447 ROLLBACK TO appl_pers_save;
1448 UPDATE igs_ad_perstmt_int
1449 SET status = cst_s_val_3,
1450 error_code = cst_ec_val_E322,
1451 error_text = l_error_text
1452 WHERE rowid = l_appl_pers_rec.rowid;
1453
1454 IF l_enable_log = 'Y' THEN
1455 igs_ad_imp_001.logerrormessage(l_appl_pers_rec.interface_perstmt_id,l_msg_data);
1456 END IF;
1457
1458 ELSIF l_type = 'S' THEN
1459 UPDATE igs_ad_perstmt_int
1460 SET status = cst_s_val_4,
1461 error_code = cst_ec_val_E702,
1462 error_text = l_error_text
1463 WHERE rowid = l_appl_pers_rec.rowid;
1464
1465 IF l_enable_log = 'Y' THEN
1466 igs_ad_imp_001.logerrormessage(l_appl_pers_rec.interface_perstmt_id,l_msg_data);
1467 END IF;
1468
1469 ELSIF l_type IS NULL THEN
1470 UPDATE igs_ad_perstmt_int
1471 SET status = cst_s_val_1,
1472 error_code = NULL,
1473 error_text = NULL
1474 WHERE rowid = l_appl_pers_rec.rowid;
1475
1476 END IF;
1477
1478 l_error_text := NULL;
1479 l_records_processed := l_records_processed +1;
1480
1481 EXCEPTION
1482
1483 WHEN OTHERS THEN
1484
1485 l_status := '3';
1486 l_error_code := 'E322';
1487
1488 igs_ad_gen_016.extract_msg_from_stack (
1489 p_msg_at_index => l_msg_at_index,
1490 p_return_status => l_return_status,
1491 p_msg_count => l_msg_count,
1492 p_msg_data => l_msg_data,
1493 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1494
1495 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
1496
1497 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1498 IF l_enable_log = 'Y' THEN
1499 igs_ad_imp_001.logerrormessage(l_appl_pers_rec.interface_perstmt_id,l_msg_data);
1500 END IF;
1501
1502 ELSE
1503
1504 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1505 l_label := 'igs.plsql.igs_ad_imp_003.prc_persstat_details.exception '||'E322';
1506
1507 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
1508 fnd_message.set_token('CONTEXT',l_appl_pers_rec.interface_appl_id);
1509 fnd_message.set_token('ERROR', l_error_text);
1510
1511 l_debug_str := fnd_message.get;
1512
1513 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
1514 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1515 END IF;
1516 END IF;
1517
1518 ROLLBACK TO appl_pers_save;
1519
1520 UPDATE igs_ad_perstmt_int
1521 SET status = cst_s_val_3,
1522 error_code = l_error_code ,
1523 error_text = l_error_text
1524 WHERE rowid = l_appl_pers_rec.rowid;
1525
1526 l_error_text := NULL;
1527 l_records_processed := l_records_processed + 1;
1528 END;
1529
1530 IF l_records_processed = 100 THEN
1531 COMMIT;
1532 l_records_processed := 0;
1533 END IF;
1534 END LOOP
1535 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
1536 COMMIT;
1537 -- END IF;
1538
1539 END prc_pe_persstat_details;
1540
1541 PROCEDURE prc_appl_fees(
1542 p_interface_run_id IN NUMBER,
1543 p_enable_log IN VARCHAR2,
1544 p_rule IN VARCHAR2)
1545 AS
1546
1547 CURSOR c_appl_fee IS
1548 SELECT cst_insert dmlmode,rowid,a.*
1549 FROM igs_ad_fee_int a
1550 WHERE interface_run_id = p_interface_run_id
1551 AND status = '2'
1552 AND (
1553 ( NVL(match_ind,'15') = '15'
1554 AND NOT EXISTS (SELECT 1 FROM igs_ad_app_req b
1555 WHERE b.person_id = a.person_id
1556 AND b.admission_appl_number = a.admission_appl_number
1557 AND b.applicant_fee_type = a.applicant_fee_type_id
1558 AND b.applicant_fee_status = a.applicant_fee_status_id
1559 AND TRUNC(b.fee_date) = TRUNC(a.fee_date))
1560 )
1561 OR (p_rule = 'R'
1562 AND match_ind IN ('16','25')
1563 )
1564 )
1565 UNION ALL
1566 SELECT cst_update dmlmode, rowid, a.*
1567 FROM igs_ad_fee_int a
1568 WHERE interface_run_id = p_interface_run_id
1569 AND status = '2'
1570 AND (
1571 (p_rule = 'I')
1572 OR (p_rule = 'R' AND match_ind = '21')
1573 )
1574 AND EXISTS ( SELECT 1 FROM igs_ad_app_req b
1575 WHERE b.person_id = a.person_id
1576 AND b.admission_appl_number = a.admission_appl_number
1577 AND b.applicant_fee_type = a.applicant_fee_type_id
1578 AND b.applicant_fee_status = a.applicant_fee_status_id
1579 AND TRUNC(b.fee_date) = TRUNC(a.fee_date)
1580 );
1581
1582 CURSOR c_dup_recd (l_fee_int_rec c_appl_fee%ROWTYPE) IS
1583 SELECT rowid, appreq.*
1584 FROM igs_ad_app_req appreq
1585 WHERE person_id = l_fee_int_rec.person_id
1586 AND admission_appl_number = l_fee_int_rec.admission_appl_number
1587 AND applicant_fee_type = l_fee_int_rec.applicant_fee_type_id
1588 AND applicant_fee_status = l_fee_int_rec.applicant_fee_status_id
1589 AND TRUNC(fee_date) = TRUNC(l_fee_int_rec.fee_date);
1590
1591 l_dup_recd c_dup_recd%ROWTYPE;
1592
1593
1594 l_records_processed NUMBER := 0;
1595
1596 l_msg_at_index NUMBER := 0;
1597 l_return_status VARCHAR2(1);
1598 l_msg_count NUMBER;
1599 l_msg_data VARCHAR2(2000);
1600 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
1601
1602 l_prog_label VARCHAR2(100);
1603 p_error_code VARCHAR2(30);
1604 p_status VARCHAR2(1);
1605 l_error_code VARCHAR2(30);
1606 l_request_id NUMBER;
1607 l_label VARCHAR2(100);
1608 l_debug_str VARCHAR2(2000);
1609 l_enable_log VARCHAR2(1);
1610 l_rowid VARCHAR2(25);
1611 l_error_text VARCHAR2(2000);
1612 l_error_text1 VARCHAR2(2000);
1613 l_type VARCHAR2(1);
1614 l_status VARCHAR2(1);
1615 l_app_req_id NUMBER;
1616
1617 BEGIN
1618
1619 l_msg_at_index := igs_ge_msg_stack.count_msg;
1620
1621 IF p_rule IN ('E' ,'I' ) THEN
1622
1623 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
1624
1625 UPDATE igs_ad_fee_int
1626 SET status = '3',
1627 error_code = 'E700',
1628 error_text = l_error_text1
1629 WHERE interface_run_id = p_interface_run_id
1630 AND status = '2'
1631 AND NVL(match_ind,'15') <> '15';
1632 END IF;
1633
1634 IF p_rule = 'R' THEN
1635 UPDATE igs_ad_fee_int
1636 SET status = '1',
1637 error_code = NULL,
1638 error_text = NULL
1639 WHERE interface_run_id = p_interface_run_id
1640 AND status = '2'
1641 AND match_ind IN ('17','18','19','22','23','24','27');
1642 END IF;
1643
1644 IF p_rule IN ( 'R', 'I') THEN
1645 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E681', 8405);
1646 UPDATE igs_ad_fee_int a
1647 SET status = '3'
1648 ,error_code = 'E681',
1649 error_text = l_error_text1
1650 WHERE interface_run_id = p_interface_run_id
1651 AND status = '2'
1652 AND 1 < (SELECT COUNT (*) FROM igs_ad_app_req b
1653 WHERE b.person_id = a.person_id
1654 AND b.admission_appl_number = a.admission_appl_number
1655 AND b.applicant_fee_type = a.applicant_fee_type_id
1656 AND b.applicant_fee_status = a.applicant_fee_status_id
1657 AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
1658 END IF;
1659
1660 IF p_rule = 'E' THEN
1661 UPDATE igs_ad_fee_int a
1662 SET status = '1',
1663 error_code = NULL,
1664 error_text = NULL,
1665 match_ind = '19'
1666 WHERE interface_run_id = p_interface_run_id
1667 AND status = '2'
1668 AND EXISTS ( SELECT 1 FROM igs_ad_app_req b
1669 WHERE b.person_id = a.person_id
1670 AND b.admission_appl_number = a.admission_appl_number
1671 AND b.applicant_fee_type = a.applicant_fee_type_id
1672 AND b.applicant_fee_status = a.applicant_fee_status_id
1673 AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
1674 END IF;
1675
1676 FOR l_fee_int_rec IN c_appl_fee LOOP
1677 BEGIN
1678
1679 SAVEPOINT fee_int_save;
1680
1681 IF l_fee_int_rec.dmlmode = cst_insert THEN
1682 l_rowid := NULL;
1683 igs_ad_app_req_pkg.insert_row(
1684 x_rowid => l_rowid,
1685 x_app_req_id => l_app_req_id,
1686 x_person_id => l_fee_int_rec.person_id,
1687 x_admission_appl_number => l_fee_int_rec.admission_appl_number,
1688 x_applicant_fee_type => l_fee_int_rec.applicant_fee_type_id,
1689 x_applicant_fee_status => l_fee_int_rec.applicant_fee_status_id,
1690 x_fee_date => TRUNC(l_fee_int_rec.fee_date),
1691 x_fee_payment_method => NULL,
1692 x_fee_amount => l_fee_int_rec.fee_amount,
1693 x_reference_num => l_fee_int_rec.reference_num,
1694 x_mode => 'R',
1695 x_credit_card_code => NULL,
1696 x_credit_card_holder_name => NULL,
1697 x_credit_card_number => NULL,
1698 x_credit_card_expiration_date => NULL,
1699 x_rev_gl_ccid => NULL,
1700 x_cash_gl_ccid => NULL,
1701 x_rev_account_cd => NULL,
1702 x_cash_account_cd => NULL,
1703 x_gl_date => NULL,
1704 x_gl_posted_date => NULL,
1705 x_posting_control_id => NULL,
1706 x_credit_card_tangible_cd => NULL,
1707 x_credit_card_payee_cd => NULL,
1708 x_credit_card_status_code => NULL
1709 );
1710
1711 ELSIF l_fee_int_rec.dmlmode = cst_update THEN
1712 OPEN c_dup_recd(l_fee_int_rec);
1713 FETCH c_dup_recd INTO l_dup_recd;
1714 CLOSE c_dup_recd ;
1715 igs_ad_app_req_pkg.update_row(
1716 x_rowid => l_dup_recd.rowid,
1717 x_app_req_id => l_dup_recd.app_req_id,
1718 x_person_id => l_fee_int_rec.PERSON_ID,
1719 x_admission_appl_numbeR => l_fee_int_rec.admission_appl_number,
1720 x_applicant_fee_type => NVL(l_fee_int_rec.applicant_fee_type_id, l_dup_recd.applicant_fee_type),
1721 x_applicant_fee_status => NVL(l_fee_int_rec.applicant_fee_status_id, l_dup_recd.applicant_fee_status),
1722 x_fee_date => TRUNC(NVL(l_fee_int_rec.fee_date, l_dup_recd.fee_date)),
1723 x_fee_payment_method => l_dup_recd.fee_payment_method,
1724 x_fee_amount => NVL(l_fee_int_rec.fee_amount, l_dup_recd.fee_amount),
1725 x_reference_num => NVL(l_fee_int_rec.reference_num, l_dup_recd.reference_num),
1726 x_mode => 'R',
1727 x_credit_card_code => l_dup_recd.credit_card_code,
1728 x_credit_card_holder_name => l_dup_recd.credit_card_holder_name,
1729 x_credit_card_number => l_dup_recd.credit_card_number,
1730 x_credit_card_expiration_date => l_dup_recd.credit_card_expiration_date,
1731 x_rev_gl_ccid => l_dup_recd.rev_gl_ccid,
1732 x_cash_gl_ccid => l_dup_recd.cash_gl_ccid,
1733 x_rev_account_cd => l_dup_recd.rev_account_cd,
1734 x_cash_account_cd => l_dup_recd.cash_account_cd,
1735 x_gl_date => l_dup_recd.gl_date,
1736 x_gl_posted_date => l_dup_recd.gl_posted_date,
1737 x_posting_control_id => l_dup_recd.posting_control_id,
1738 x_credit_card_tangible_cd => l_dup_recd.credit_card_tangible_cd,
1739 x_credit_card_payee_cd => l_dup_recd.credit_card_payee_cd,
1740 x_credit_card_status_code => l_dup_recd.credit_card_status_code
1741 );
1742 END IF;
1743
1744 UPDATE igs_ad_fee_int
1745 SET status = cst_s_val_1,
1746 error_code = NULL,
1747 error_text = NULL,
1748 match_ind = DECODE (l_fee_int_rec.dmlmode, cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
1749 WHERE rowid = l_fee_int_rec.rowid;
1750
1751 l_records_processed := l_records_processed +1;
1752
1753 EXCEPTION
1754 WHEN OTHERS THEN
1755
1756 l_status := '3';
1757
1758 IF l_fee_int_rec.dmlmode = cst_update THEN
1759 l_error_code := 'E014';
1760 ELSIF l_fee_int_rec.dmlmode = cst_insert THEN
1761 l_error_code := 'E322';
1762 END IF;
1763
1764 igs_ad_gen_016.extract_msg_from_stack (
1765 p_msg_at_index => l_msg_at_index,
1766 p_return_status => l_return_status,
1767 p_msg_count => l_msg_count,
1768 p_msg_data => l_msg_data,
1769 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
1770
1771 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
1772
1773 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
1774 IF l_enable_log = 'Y' THEN
1775 igs_ad_imp_001.logerrormessage(l_fee_int_rec.interface_fee_id,l_msg_data);
1776 END IF;
1777
1778 ELSE
1779
1780 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1781 l_label := 'igs.plsql.igs_ad_imp_003.prc_acad_int.exception '||l_error_code;
1782
1783 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
1784 fnd_message.set_token('CONTEXT',l_fee_int_rec.interface_appl_id);
1785 fnd_message.set_token('ERROR', l_error_text);
1786
1787 l_debug_str := fnd_message.get;
1788
1789 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
1790 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1791 END IF;
1792 END IF;
1793
1794 ROLLBACK TO fee_int_save;
1795
1796 UPDATE igs_ad_fee_int
1797 SET status = cst_s_val_3,
1798 error_code = l_error_code ,
1799 error_text = l_error_text
1800 WHERE rowid = l_fee_int_rec.rowid;
1801
1802 l_error_text := NULL;
1803 l_records_processed := l_records_processed + 1;
1804 END;
1805
1806
1807 IF l_records_processed = 100 THEN
1808 COMMIT;
1809 l_records_processed := 0;
1810 END IF;
1811 END LOOP;
1812 -- IF l_records_processed < 100 AND l_records_processed > 0 THEN
1813 COMMIT;
1814 -- END IF;
1815
1816
1817 UPDATE igs_ad_fee_int a
1818 SET status = cst_s_val_1,
1819 error_code = NULL,
1820 error_text = NULL,
1821 match_ind = cst_mi_val_23
1822 WHERE interface_run_id = p_interface_run_id
1823 AND p_rule = 'R'
1824 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
1825 AND EXISTS (
1826 SELECT rowid FROM igs_ad_app_req b
1827 WHERE b.person_id = a.person_id
1828 AND b.admission_appl_number = a.admission_appl_number
1829 AND b.applicant_fee_type = a.applicant_fee_type_id
1830 AND b.applicant_fee_status = a.applicant_fee_status_id
1831 AND TRUNC(b.fee_date) = TRUNC(a.fee_date)
1832 AND NVL(b.reference_num, '-1') = NVL( NVL(a.reference_num, b.reference_num ) , -1)
1833 AND b.fee_amount = a.fee_amount);
1834
1835
1836 IF p_rule = 'R' THEN
1837 UPDATE igs_ad_fee_int a
1838 SET
1839 status = cst_s_val_3
1840 , match_ind = cst_mi_val_20
1841 , dup_app_req_id = ( SELECT APP_REQ_ID FROM igs_ad_app_req b
1842 WHERE b.person_id = a.person_id
1843 AND b.admission_appl_number = a.admission_appl_number
1844 AND b.applicant_fee_type = a.applicant_fee_type_id
1845 AND b.applicant_fee_status = a.applicant_fee_status_id
1846 AND TRUNC(b.fee_date) = TRUNC(a.fee_date))
1847 WHERE interface_run_id = p_interface_run_id
1848 AND status = cst_s_val_2
1849 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
1850 AND EXISTS ( SELECT rowid FROM igs_ad_app_req b
1851 WHERE b.person_id = a.person_id
1852 AND b.admission_appl_number = a.admission_appl_number
1853 AND b.applicant_fee_type = a.applicant_fee_type_id
1854 AND b.applicant_fee_status = a.applicant_fee_status_id
1855 AND TRUNC(b.fee_date) = TRUNC(a.fee_date));
1856 COMMIT;
1857 END IF;
1858
1859 -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
1860 IF p_rule = 'R' THEN
1861 l_error_text1 := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
1862
1863 UPDATE igs_ad_fee_int
1864 SET
1865 status = cst_s_val_3
1866 , error_code = cst_ec_val_E700,
1867 error_text = l_error_text1
1868 WHERE interface_run_id = p_interface_run_id
1869 AND status = cst_s_val_2
1870 AND match_ind IS NOT NULL;
1871 COMMIT;
1872 END IF;
1873
1874 END prc_appl_fees;
1875
1876 END igs_ad_imp_003;