[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_010
Source
1 PACKAGE BODY Igs_Ad_Imp_010 AS
2 /* $Header: IGSAD88B.pls 120.0 2005/06/02 03:46:30 appldev noship $ */
3 /******************************************************************
4 Created By:
5 Date Created By:
6 Purpose:
7 Known limitations,enhancements,remarks:
8 Change History
9 Who When What
10 samaresh 02-FEB-2002 Removed the procedure crt_appcln, as this happens
11 through igsad82b.pls.
12 The procedures admp_val_import_us,admp_ins_import_program,
13 admp_ins_import_acai are removed, as these are called
14 from crt_appcln
15 bug # 2191058
16 vchappid 29-Aug-2001 Added new parameters into function calls, Enh Bug#1964478
17 ******************************************************************/
18
19 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
20 cst_rule_val_I CONSTANT VARCHAR2(1) := 'I';
21 cst_rule_val_E CONSTANT VARCHAR2(1) := 'E';
22 cst_rule_val_R CONSTANT VARCHAR2(1) := 'R';
23
24 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
25 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
26 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
27 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
28 cst_mi_val_15 CONSTANT VARCHAR2(2) := '15';
29 cst_mi_val_16 CONSTANT VARCHAR2(2) := '16';
30 cst_mi_val_17 CONSTANT VARCHAR2(2) := '17';
31 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
32 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
33 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
34 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
35 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
36 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
37 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
38 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
39 cst_mi_val_27 CONSTANT VARCHAR2(2) := '27';
40
41 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
42 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
43 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
44 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
45
46 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
47 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
48
49 cst_insert CONSTANT VARCHAR2(6) := 'INSERT';
50 cst_update CONSTANT VARCHAR2(6) := 'UPDATE';
51 cst_unique_record CONSTANT NUMBER := 1;
52
53 cst_et_val_E700 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
54 cst_et_val_E701 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
55 cst_et_val_E678 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
56
57 cst_ec_val_E700 CONSTANT VARCHAR2(4) := 'E700';
58 cst_ec_val_E701 CONSTANT VARCHAR2(4) := 'E701';
59 cst_ec_val_E678 CONSTANT VARCHAR2(4) := 'E678';
60
61 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
62
63 -- Process the Applicant Notes
64 PROCEDURE admp_val_pappl_nots(p_interface_run_id IN NUMBER,
65 p_enable_log IN VARCHAR2,
66 p_category_meaning IN VARCHAR2,
67 p_rule IN VARCHAR2 )
68 AS
69 l_prog_label VARCHAR2(100);
70 l_label VARCHAR2(100);
71 l_debug_str VARCHAR2(2000);
72 l_request_id NUMBER;
73 l_error_code igs_ad_notes_int.error_code%TYPE;
74 l_records_processed NUMBER := 0;
75
76 -- local procedure
77 PROCEDURE crt_apcnt_notes(
78 p_interface_run_id IN NUMBER ) IS
79
80
81 CURSOR c_igs_ad_notes_int IS
82 SELECT cst_insert dmlmode, rowid, a.*
83 FROM igs_ad_notes_int a
84 WHERE interface_run_id = p_interface_run_id
85 AND status = cst_s_val_2;
86
87 l_Appl_Notes_Id NUMBER;
88 l_Rowid VARCHAR2(100);
89 l_Rowid2 VARCHAR2(25);
90 l_Ref_Notes_Id NUMBER;
91 l_msg_at_index NUMBER := 0;
92 l_error_text VARCHAR2(2000);
93 l_return_status VARCHAR2(1);
94 l_msg_count NUMBER ;
95 l_msg_data VARCHAR2(2000);
96 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
97
98 l_admission_cat igs_ad_appl.admission_cat%TYPE;
99 l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
100
101 BEGIN
102 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
103
104
105 l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.crt_apcnt_notes';
106 l_debug_str := 'Interface Run ID' || p_interface_run_id;
107
108 fnd_log.string_with_context( fnd_log.level_procedure,
109 l_label,
110 l_debug_str, NULL,
111 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
112 END IF;
113
114 l_records_processed := 0;
115
116 FOR notes_rec IN c_igs_ad_notes_int
117 LOOP
118 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => notes_rec.admission_application_type,
119 p_admission_cat => l_admission_cat,
120 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
121
122 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
123 p_s_admission_process_type => l_s_admission_process_type,
124 p_s_admission_step_type => 'APPL-NOTES') = 'FALSE' THEN
125 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
126 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
127 FND_MESSAGE.SET_TOKEN ('APPLTYPE', notes_rec.admission_application_type);
128 l_error_text := FND_MESSAGE.GET;
129 UPDATE igs_ad_notes_int
130 SET
131 status = cst_s_val_3
132 , error_code = cst_ec_val_E701
133 , error_text = l_error_text
134 WHERE rowid = notes_rec.rowid;
135 l_records_processed := l_records_processed + 1;
136
137 ELSIF NOT igs_ad_note_types_pkg.Get_UK2_For_Validation(
138 x_notes_type_id => notes_rec.Note_Type_Id,
139 x_notes_category => 'APPLICATION',
140 x_closed_ind => 'N') THEN
141 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_PK_UK_NOT_FOUND_CLOSED');
142 FND_MESSAGE.SET_TOKEN ('ATTRIBUTE', FND_MESSAGE.GET_STRING('IGS','IGS_AD_NOTE_TYPE'));
143 l_error_text := FND_MESSAGE.GET;
144 UPDATE igs_ad_notes_int
145 SET
146 status = cst_s_val_3
147 , error_code = cst_ec_val_E701
148 , error_text = l_error_text
149 WHERE rowid = notes_rec.rowid;
150 l_records_processed := l_records_processed + 1;
151
152 ELSE
153 BEGIN
154 SAVEPOINT before_create;
155 l_msg_at_index := igs_ge_msg_stack.count_msg;
156
157 igs_ad_appl_notes_pkg.INSERT_ROW(
158 X_ROWID => l_Rowid,
159 X_APPL_NOTES_ID => l_Appl_Notes_Id,
160 x_Person_Id => notes_rec.person_id,
161 X_Admission_Appl_Number => notes_rec.Admission_Appl_Number,
162 x_Nominated_Course_Cd => notes_rec.Nominated_Course_Cd,
163 x_Sequence_Number => notes_rec.Sequence_Number,
164 x_Note_Type_Id => notes_rec.Note_Type_Id,
165 x_Ref_Notes_Id => l_Ref_Notes_Id,
166 x_Mode => 'R');
167 igs_ge_note_pkg.INSERT_ROW(
168 X_ROWID => l_Rowid2,
169 X_REFERENCE_NUMBER => l_Ref_Notes_Id,
170 X_S_NOTE_FORMAT_TYPE => 'TEXT',
171 X_NOTE_TEXT => notes_rec.notes,
172 X_MODE => 'R');
173 UPDATE igs_ad_notes_int
174 SET
175 status = cst_s_val_1
176 WHERE rowid = notes_rec.rowid;
177 l_records_processed := l_records_processed + 1;
178 IF l_records_processed = 100 THEN
179 COMMIT;
180 l_records_processed := 0;
181 END IF;
182 EXCEPTION
183 WHEN OTHERS THEN
184 ROLLBACK TO before_create;
185 l_error_code := 'E322';
186 l_msg_data := SQLERRM;
187
188 igs_ad_gen_016.extract_msg_from_stack (
189 p_msg_at_index => l_msg_at_index,
190 p_return_status => l_return_status,
191 p_msg_count => l_msg_count,
192 p_msg_data => l_msg_data,
193 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
194 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
195
196 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
197 IF p_enable_log = 'Y' THEN
198 igs_ad_imp_001.logerrormessage(notes_rec.interface_notes_id,l_msg_data,'IGS_AD_NOTES_INT');
199 END IF;
200 ELSE
201 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
202
203 l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.crt_apcnt_notes.for_loop.execption'||l_error_code;
204
205 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
206 fnd_message.set_token('INTERFACE_ID',notes_rec.interface_notes_id);
207 fnd_message.set_token('ERROR_CD',l_error_code);
208
209 l_debug_str := fnd_message.get;
210 fnd_log.string_with_context( fnd_log.level_exception,
211 l_label,
212 l_debug_str, NULL,
213 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
214 END IF;
215 END IF;
216
217
218
219 UPDATE igs_ad_notes_int
220 SET status = cst_s_val_3
221 , error_code = l_error_code
222 , error_text = l_error_text
223 WHERE rowid = notes_rec.rowid;
224 l_records_processed := l_records_processed + 1;
225 END;
226 END IF;
227
228 ELSE
229 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
230 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
231 FND_MESSAGE.SET_TOKEN ('APPLTYPE', notes_rec.admission_application_type);
232 l_error_text := FND_MESSAGE.GET;
233
234 UPDATE igs_ad_notes_int
235 SET status = cst_s_val_3
236 , error_code = cst_ec_val_E701
237 , error_text = l_error_text
238 WHERE rowid = notes_rec.rowid;
239 l_records_processed := l_records_processed + 1;
240 END IF;
241 IF l_records_processed = 100 THEN
242 COMMIT;
243 l_records_processed := 0;
244 END IF;
245 END LOOP;
246
247 IF l_records_processed < 100 AND l_records_processed > 0 THEN
248 COMMIT;
249 END IF;
250 END crt_apcnt_notes;
251 -- Local Procedure crt_apcnt_notes end here
252 BEGIN
253
254 l_prog_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots';
255 l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.';
256 l_request_id := fnd_global.conc_request_id;
257
258 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
259 l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.begin';
260 l_debug_str := 'igs_ad_imp_010.admp_val_pappl_nots';
261 fnd_log.string_with_context(fnd_log.level_procedure,
262 l_label,
263 l_debug_str, NULL,
264 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
265 END IF;
266
267 -- Set STATUS to 3 when duplicate record is found
268
269 UPDATE igs_ad_notes_int in_rec
270 SET
271 status = cst_s_val_3
272 , error_code = cst_ec_val_E678
273 , error_text = cst_et_val_E678
274 WHERE interface_run_id = p_interface_run_id
275 AND status = cst_s_val_2
276 AND EXISTS ( SELECT 1
277 FROM igs_ad_appl_notes mn_rec
278 WHERE mn_rec.person_id = in_rec.person_id
279 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
280 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd
281 AND mn_rec.note_type_id = in_rec.note_type_id
282 AND mn_rec.sequence_number = in_rec.sequence_number);
283 COMMIT;
284
285 -- Create the OSS record after validating successfully the interface record
286 crt_apcnt_notes( p_interface_run_id);
287
288 END admp_val_pappl_nots ;
289
290 PROCEDURE prcs_applnt_edu_goal_dtls(p_interface_run_id IN NUMBER,
291 p_enable_log IN VARCHAR2,
292 p_category_meaning IN VARCHAR2,
293 p_rule IN VARCHAR2 ) AS
294
295 l_prog_label VARCHAR2(100);
296 l_label VARCHAR2(100);
297 l_debug_str VARCHAR2(2000);
298 l_request_id NUMBER;
299 l_error_code igs_ad_edugoal_int.error_code%TYPE;
300 l_records_processed NUMBER := 0;
301
302 --
303 -- Start of Local Procedure create_applicant_edu_goals
304 --
305 PROCEDURE create_applicant_edu_goals(
306 p_interface_run_id IN NUMBER ) IS
307
308 CURSOR c_igs_ad_edugoal_int IS
309 SELECT cst_insert dmlmode, rowid, a.*
310 FROM igs_ad_edugoal_int a
311 WHERE interface_run_id = p_interface_run_id
312 AND status = cst_s_val_2;
313
314 l_rowid ROWID;
315 l_post_edugoal_id igs_ad_edugoal.post_edugoal_id%TYPE;
316 l_msg_at_index NUMBER := 0;
317 l_return_status VARCHAR2(1);
318 l_error_text VARCHAR2(2000);
319 l_msg_count NUMBER ;
320 l_msg_data VARCHAR2(2000);
321 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
322
323 l_admission_cat igs_ad_appl.admission_cat%TYPE;
324 l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
325
326 BEGIN
327
328 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
329 l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.create_applicant_edu_goals';
330 l_debug_str := 'Interface Run ID' || p_interface_run_id;
331 fnd_log.string_with_context( fnd_log.level_procedure,
332 l_label,
333 l_debug_str, NULL,
334 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
335 END IF;
336
337 l_records_processed := 0;
338
339 FOR edugoal_rec IN c_igs_ad_edugoal_int
340 LOOP
341 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => edugoal_rec.admission_application_type,
342 p_admission_cat => l_admission_cat,
343 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
344
345 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
346 p_s_admission_process_type => l_s_admission_process_type,
347 p_s_admission_step_type => 'EDU-GOALS') = 'FALSE' THEN
348 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
349 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
350 FND_MESSAGE.SET_TOKEN ('APPLTYPE', edugoal_rec.admission_application_type);
351 l_error_text := FND_MESSAGE.GET;
352 UPDATE igs_ad_edugoal_int
353 SET
354 status = cst_s_val_3
355 , error_code = cst_ec_val_E701
356 , error_text = l_error_text
357 WHERE rowid = edugoal_rec.rowid;
358 l_records_processed := l_records_processed + 1;
359 ELSE
360 BEGIN
361 SAVEPOINT before_create;
362 l_msg_at_index := igs_ge_msg_stack.count_msg;
363
364 igs_ad_edugoal_pkg.insert_row
365 (
366 X_ROWID => l_rowid,
367 X_POST_EDUGOAL_ID => l_post_edugoal_id,
368 X_PERSON_ID => edugoal_rec.person_id,
369 X_ADMISSION_APPL_NUMBER => edugoal_rec.admission_appl_number,
370 X_NOMINATED_COURSE_CD => edugoal_rec.nominated_course_cd,
371 X_SEQUENCE_NUMBER => edugoal_rec.sequence_number,
372 X_EDU_GOAL_ID => edugoal_rec.edu_goal_id,
373 X_MODE => 'R'
374 );
375 UPDATE igs_ad_edugoal_int
376 SET status = cst_s_val_1
377 WHERE rowid = edugoal_rec.rowid;
378
379 l_records_processed := l_records_processed + 1;
380
381 IF l_records_processed = 100 THEN
382 COMMIT;
383 l_records_processed := 0;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 ROLLBACK TO before_create;
389 l_error_code := 'E322';
390 l_msg_data := SQLERRM;
391 igs_ad_gen_016.extract_msg_from_stack (
392 p_msg_at_index => l_msg_at_index,
393 p_return_status => l_return_status,
394 p_msg_count => l_msg_count,
395 p_msg_data => l_msg_data,
396 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
397
398 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
399
400 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
401 IF p_enable_log = 'Y' THEN
402 igs_ad_imp_001.logerrormessage(edugoal_rec.interface_edugoal_id,l_msg_data,'IGS_AD_EDUGOAL_INT');
403 END IF;
404 ELSE
405 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
406 l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.create_applicant_edu_goals.for_loop.execption'||l_error_code;
407 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
408 fnd_message.set_token('INTERFACE_ID',edugoal_rec.interface_edugoal_id);
409 fnd_message.set_token('ERROR_CD',l_error_code);
410 l_debug_str := fnd_message.get;
411 fnd_log.string_with_context( fnd_log.level_exception,
412 l_label,
413 l_debug_str, NULL,
414 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
415 END IF;
416 END IF;
417
418 UPDATE igs_ad_edugoal_int
419 SET status = cst_s_val_3
420 ,error_code = l_error_code
421 ,error_text = l_error_text
422 WHERE rowid = edugoal_rec.rowid;
423 l_records_processed := l_records_processed + 1;
424 END;
425 END IF;
426 ELSE
427 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
428 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
429 FND_MESSAGE.SET_TOKEN ('APPLTYPE', edugoal_rec.admission_application_type);
430 l_error_text := FND_MESSAGE.GET;
431 UPDATE igs_ad_edugoal_int
432 SET
433 status = cst_s_val_3
434 , error_code = cst_ec_val_E701
435 , error_text = l_error_text
436 WHERE rowid = edugoal_rec.rowid;
437 l_records_processed := l_records_processed + 1;
438 END IF;
439
440 IF l_records_processed = 100 THEN
441 COMMIT;
442 l_records_processed := 0;
443 END IF;
444 END LOOP;
445 IF l_records_processed < 100 AND l_records_processed > 0 THEN
446 COMMIT;
447 END IF;
448 END create_applicant_edu_goals;
449
450 BEGIN
451
452 l_prog_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls';
453 l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.';
454 l_request_id := fnd_global.conc_request_id;
455
456 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
457 l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.begin';
458 l_debug_str := 'igs_ad_imp_010.prcs_applnt_edu_goal_dtls';
459 fnd_log.string_with_context( fnd_log.level_procedure,
460 l_label,
461 l_debug_str, NULL,
462 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
463 END IF;
464
465 -- Set STATUS to 3 when duplicate record is found
466
467 UPDATE igs_ad_edugoal_int in_rec
468 SET status = cst_s_val_3
469 ,error_code = cst_ec_val_E678
470 ,error_text = cst_et_val_E678
471 WHERE interface_run_id = p_interface_run_id
472 AND status = cst_s_val_2
473 AND EXISTS ( SELECT 1
474 FROM igs_ad_edugoal mn_rec
475 WHERE mn_rec.person_id = in_rec.person_id
476 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
477 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd
478 AND mn_rec.edu_goal_id = in_rec.edu_goal_id
479 AND mn_rec.sequence_number = in_rec.sequence_number);
480 COMMIT;
481
482 -- Create the OSS record after validating successfully the interface record
483 create_applicant_edu_goals( p_interface_run_id);
484
485 END prcs_applnt_edu_goal_dtls;
486
487 PROCEDURE prc_apcnt_uset_apl( p_interface_run_id IN NUMBER,
488 p_enable_log IN VARCHAR2,
489 p_category_meaning IN VARCHAR2,
490 p_rule IN VARCHAR2 ) AS
491
492 l_prog_label VARCHAR2(100);
493 l_label VARCHAR2(100);
494 l_debug_str VARCHAR2(2000);
495 l_request_id NUMBER;
496 l_error_code igs_ad_unitsets_int.error_code%TYPE;
497
498
499 PROCEDURE crt_upd_apcnt_uset_apl(p_interface_run_id NUMBER) AS
500
501 CURSOR c_igs_ad_unitsets_int IS
502 SELECT cst_insert dmlmode, rowid, in_rec.*
503 FROM igs_ad_unitsets_int in_rec
504 WHERE interface_run_id = p_interface_run_id
505 AND status = cst_s_val_2
506 AND ( ( NVL(match_ind,'15') = '15'
507 AND NOT EXISTS ( SELECT 1
508 FROM igs_ad_unit_sets mn_rec
509 WHERE mn_rec.person_id = in_rec.person_id
510 AND mn_rec.sequence_number = in_rec.sequence_number
511 AND mn_rec.unit_set_cd = in_rec.unit_set_cd
512 AND mn_rec.version_number = in_rec.version_number
513 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
514 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd))
515 OR (p_rule = cst_rule_val_R
516 AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
517 UNION ALL
518 SELECT cst_update dmlmode, rowid, in_rec.*
519 FROM igs_ad_unitsets_int in_rec
520 WHERE interface_run_id = p_interface_run_id
521 AND status = cst_s_val_2
522 AND ( (p_rule = cst_rule_val_I)
523 OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
524 AND EXISTS ( SELECT 1
525 FROM igs_ad_unit_sets mn_rec
526 WHERE mn_rec.person_id = in_rec.person_id
527 AND mn_rec.sequence_number = in_rec.sequence_number
528 AND mn_rec.unit_set_cd = in_rec.unit_set_cd
529 AND mn_rec.version_number = in_rec.version_number
530 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
531 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
532
533 CURSOR c_null_hdlg_unitsets_cur_rec(cp_unit_set_cur c_igs_ad_unitsets_int%ROWTYPE) IS
534 SELECT ROWID, mn_rec.*
535 FROM igs_ad_unit_sets mn_rec
536 WHERE mn_rec.person_id = cp_unit_set_cur.person_id
537 AND mn_rec.sequence_number = cp_unit_set_cur.sequence_number
538 AND mn_rec.unit_set_cd = cp_unit_set_cur.unit_set_cd
539 AND mn_rec.version_number = cp_unit_set_cur.version_number
540 AND mn_rec.admission_appl_number = cp_unit_set_cur.admission_appl_number
541 AND mn_rec.nominated_course_cd = cp_unit_set_cur.nominated_course_cd;
542
543 c_null_hdlg_unitsets_rec c_null_hdlg_unitsets_cur_rec%ROWTYPE;
544
545 l_error_code VARCHAR2(30);
546 l_msg_at_index NUMBER := 0;
547 l_return_status VARCHAR2(1);
548 l_error_text VARCHAR2(2000);
549 l_msg_count NUMBER ;
550 l_msg_data VARCHAR2(2000);
551 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
552
553 l_records_processed NUMBER;
554 l_rowid VARCHAR2(30);
555 l_unit_set_id igs_ad_unit_sets.unit_set_id%TYPE;
556
557 l_admission_cat igs_ad_appl.admission_cat%TYPE;
558 l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
559
560 BEGIN
561
562 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
563 l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.crt_upd_apcnt_uset_apl';
564 l_debug_str := 'Interface Run ID' || p_interface_run_id;
565 fnd_log.string_with_context( fnd_log.level_procedure,
566 l_label,
567 l_debug_str, NULL,
568 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
569 END IF;
570 l_records_processed := 0;
571
572 FOR unitsets_rec IN c_igs_ad_unitsets_int
573 LOOP
574 IF igs_ad_gen_016.get_appl_type_apc (p_application_type => unitsets_rec.admission_application_type,
575 p_admission_cat => l_admission_cat,
576 p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
577 IF igs_ad_gen_016.get_apcs (p_admission_cat => l_admission_cat,
578 p_s_admission_process_type => l_s_admission_process_type,
579 p_s_admission_step_type => 'DES-UNITSETS') = 'FALSE' THEN
580
581 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
582 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
583 FND_MESSAGE.SET_TOKEN ('APPLTYPE', unitsets_rec.admission_application_type);
584 l_error_text := FND_MESSAGE.GET;
585
586 UPDATE igs_ad_unitsets_int
587 SET status = cst_s_val_3
588 ,match_ind = DECODE (unitsets_rec.dmlmode, cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
589 cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
590 ,error_code = cst_ec_val_E701
591 , error_text = l_error_text
592 WHERE rowid = unitsets_rec.rowid;
593
594 l_records_processed := l_records_processed + 1;
595 ELSE
596 BEGIN
597 SAVEPOINT before_creatupdate;
598 l_msg_at_index := igs_ge_msg_stack.count_msg;
599
600 IF unitsets_rec.dmlmode = cst_insert THEN
601 igs_ad_unit_sets_pkg.INSERT_ROW (
602 x_rowid => l_rowid,
603 x_unit_set_id => l_unit_set_id,
604 x_person_id => unitsets_rec.person_id,
605 x_admission_appl_number => unitsets_rec.admission_appl_number,
606 x_nominated_course_cd => unitsets_rec.nominated_course_cd,
607 x_sequence_number => unitsets_rec.sequence_number,
608 x_unit_set_cd => unitsets_rec.unit_set_cd,
609 x_version_number => unitsets_rec.version_number,
610 x_rank => unitsets_rec.rank,
611 x_mode => 'R'
612 );
613 ELSIF unitsets_rec.dmlmode = cst_update THEN
614 OPEN c_null_hdlg_unitsets_cur_rec(unitsets_rec);
615 FETCH c_null_hdlg_unitsets_cur_rec INTO c_null_hdlg_unitsets_rec;
616 CLOSE c_null_hdlg_unitsets_cur_rec;
617
618 igs_ad_unit_sets_pkg.update_row(
619 x_rowid => c_null_hdlg_unitsets_rec.rowid,
620 x_unit_set_id => c_null_hdlg_unitsets_rec.unit_set_id,
621 x_person_id => NVL(unitsets_rec.person_id,c_null_hdlg_unitsets_rec.person_id),
622 x_admission_appl_number=> NVL(unitsets_rec.admission_appl_number,c_null_hdlg_unitsets_rec.admission_appl_number),
623 x_nominated_course_cd => NVL(unitsets_rec.nominated_course_cd, c_null_hdlg_unitsets_rec.nominated_course_cd),
624 x_sequence_number => NVL(unitsets_rec.sequence_number,c_null_hdlg_unitsets_rec.sequence_number),
625 x_unit_set_cd => NVL(unitsets_rec.unit_set_cd,c_null_hdlg_unitsets_rec.unit_set_cd),
626 x_version_number => NVL(unitsets_rec.version_number,c_null_hdlg_unitsets_rec.version_number),
627 x_rank => NVL(unitsets_rec.rank,c_null_hdlg_unitsets_rec.rank),
628 x_mode =>'R');
629 END IF;
630
631 UPDATE igs_ad_unitsets_int
632 SET
633 status = cst_s_val_1
634 , match_ind = DECODE (unitsets_rec.dmlmode,cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
635 WHERE rowid = unitsets_rec.rowid;
636
637 l_records_processed := l_records_processed + 1;
638
639 IF l_records_processed = 100 THEN
640 COMMIT;
641 l_records_processed := 0;
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 ROLLBACK TO before_creatupdate;
647 l_msg_data := SQLERRM;
648
649 IF unitsets_rec.dmlmode = cst_insert THEN
650 l_error_code := 'E322'; -- Insertion Failed
651 ELSIF unitsets_rec.dmlmode = cst_update THEN
652 l_error_code := 'E014'; -- Update Failed
653 END IF;
654
655 igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index => l_msg_at_index,
656 p_return_status => l_return_status,
657 p_msg_count => l_msg_count,
658 p_msg_data => l_msg_data,
659 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
660 l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
661
662 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
663 IF p_enable_log = 'Y' THEN
664 igs_ad_imp_001.logerrormessage(unitsets_rec.interface_unitsets_id,l_msg_data,'IGS_AD_UNITSETS_INT');
665 END IF;
666 ELSE
667 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
668
669 l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.crt_upd_apcnt_uset_apl.for_loop.execption'||l_error_code;
670
671 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
672 fnd_message.set_token('INTERFACE_ID',unitsets_rec.interface_unitsets_id);
673 fnd_message.set_token('ERROR_CD',l_error_code);
674
675 l_debug_str := fnd_message.get;
676 fnd_log.string_with_context( fnd_log.level_exception,
677 l_label,
678 l_debug_str, NULL,
679 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
680 END IF;
681
682 END IF;
683
684 UPDATE igs_ad_unitsets_int
685 SET status = cst_s_val_3
686 , match_ind = DECODE ( unitsets_rec.dmlmode
687 ,cst_update, DECODE ( match_ind, NULL, cst_mi_val_12, match_ind)
688 ,cst_insert, DECODE ( p_rule
689 ,cst_rule_val_R, DECODE ( match_ind, NULL, cst_mi_val_11, match_ind)
690 ,cst_mi_val_11))
691 , error_code = l_error_code
692 , error_text = l_error_text
693 WHERE rowid = unitsets_rec.rowid;
694 l_records_processed := l_records_processed + 1;
695 END;
696 END IF;
697
698 ELSE
699 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
700 FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
701 FND_MESSAGE.SET_TOKEN ('APPLTYPE', unitsets_rec.admission_application_type);
702 l_error_text := FND_MESSAGE.GET;
703
704 UPDATE igs_ad_unitsets_int
705 SET status = cst_s_val_3
706 , match_ind = DECODE (unitsets_rec.dmlmode, cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
707 cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
708 , error_code = cst_ec_val_E701
709 , error_text = l_error_text
710 WHERE rowid = unitsets_rec.rowid;
711
712 l_records_processed := l_records_processed + 1;
713 END IF;
714
715 IF l_records_processed = 100 THEN
716 COMMIT;
717 l_records_processed := 0;
718 END IF;
719
720 END LOOP;
721
722 IF l_records_processed < 100 AND l_records_processed > 0 THEN
723 COMMIT;
724 END IF;
725
726 END crt_upd_apcnt_uset_apl; -- End of local procedure crt_upd_apcnt_uset_apl.
727
728 -- begin of main process prc_apcnt_uset_apl
729 BEGIN
730
731 l_prog_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl';
732 l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.';
733 l_request_id := fnd_global.conc_request_id;
734
735 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
736 l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.begin';
737 l_debug_str := 'igs_ad_imp_014.prc_apcnt_uset_apl';
738 fnd_log.string_with_context( fnd_log.level_procedure,
739 l_label,
740 l_debug_str, NULL,
741 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
742 END IF;
743
744 -- Set STATUS to 3 for interface records with RULE = E or I and MATCH IND
745 IF p_rule IN ('E','I') THEN
746 UPDATE igs_ad_unitsets_int
747 SET status = cst_s_val_3
748 , error_code = cst_ec_val_E700
749 , error_text = cst_et_val_E700
750 WHERE interface_run_id = p_interface_run_id
751 AND status = cst_s_val_2
752 AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;
753 COMMIT;
754 END IF;
755
756 -- Set STATUS to 1 for interface records with RULE = R and
757 -- MATCH IND = 17,18,19,22,23,24,27
758 IF p_rule IN ('R') THEN
759 UPDATE igs_ad_unitsets_int
760 SET status = cst_s_val_1
761 WHERE interface_run_id = p_interface_run_id
762 AND status = cst_s_val_2
763 AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
764 cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);
765 COMMIT;
766 END IF;
767
768 -- Set STATUS to 1 and MATCH IND to 19 for interface records with RULE =
769 -- E matching OSS record(s)
770 IF p_rule IN ('E') THEN
771 UPDATE igs_ad_unitsets_int in_rec
772 SET status = cst_s_val_1
773 , match_ind = cst_mi_val_19
774 WHERE interface_run_id = p_interface_run_id
775 AND status = cst_s_val_2
776 AND EXISTS ( SELECT 1
777 FROM igs_ad_unit_sets mn_rec
778 WHERE mn_rec.person_id = in_rec.person_id
779 AND mn_rec.sequence_number = in_rec.sequence_number
780 AND mn_rec.unit_set_cd = in_rec.unit_set_cd
781 AND mn_rec.version_number = in_rec.version_number
782 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
783 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
784 COMMIT;
785 END IF;
786
787 -- Create / Update the OSS record after validating successfully the interface record
788 -- Create
789 -- If RULE E/I/R (match indicator will be 15 or NULL by now no need to check) and
790 -- matching system record not found OR RULE = R and MATCH IND = 16, 25
791 -- Update
792 -- If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
793 -- RULE = R and MATCH IND = 21
794
795 -- Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying
796 -- the DML operation. This is done to have one code section for record validation, exception
797 -- handling and interface table update. This avoids call to separate PLSQL blocks, tuning
798 -- performance on stack maintenance during the process.
799
800 crt_upd_apcnt_uset_apl(p_interface_run_id);
801
802 -- Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching
803 -- OSS record(s) in ALL updateable column values, if column nullification is not
804 -- allowed then the 2 DECODE should be replaced by a single NVL
805 IF p_rule IN ('R') THEN
806 UPDATE igs_ad_unitsets_int in_rec
807 SET status = cst_s_val_1
808 , match_ind = cst_mi_val_23
809 WHERE interface_run_id = p_interface_run_id
810 AND status = cst_s_val_2
811 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
812 AND EXISTS ( SELECT 1
813 FROM igs_ad_unit_sets mn_rec
814 WHERE NVL(mn_rec.person_id, -99) = NVL(in_rec.person_id,NVL(mn_rec.person_id, -99) )
815 AND NVL(mn_rec.admission_appl_number, -99) = NVL(in_rec.admission_appl_number,NVL(mn_rec.admission_appl_number, -99) )
816 AND NVL(mn_rec.nominated_course_cd,'~') = NVL(in_rec.nominated_course_cd, NVL(mn_rec.nominated_course_cd,'~') )
817 AND NVL(mn_rec.sequence_number, -99) = NVL(in_rec.sequence_number, NVL(mn_rec.sequence_number, -99))
818 AND NVL(mn_rec.unit_set_cd, '~') = NVL(in_rec.unit_set_cd, NVL(mn_rec.unit_set_cd, '~'))
819 AND NVL(mn_rec.version_number, -99) = NVL(in_rec.version_number,NVL(mn_rec.version_number, -99) )
820 AND NVL(mn_rec.rank, -99) = NVL(in_rec.rank, NVL(mn_rec.rank, -99))
821 );
822 COMMIT;
823 END IF;
824
825 -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND
826 -- <> 21, 25, ones failed discrepancy check
827 IF p_rule IN ('R') THEN
828 UPDATE igs_ad_unitsets_int in_rec
829 SET
830 status = cst_s_val_3
831 , match_ind = cst_mi_val_20
832 WHERE interface_run_id = p_interface_run_id
833 AND status = cst_s_val_2
834 AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
835 AND EXISTS ( SELECT rowid
836 FROM igs_ad_unit_sets mn_rec
837 WHERE mn_rec.person_id = in_rec.person_id
838 AND mn_rec.sequence_number = in_rec.sequence_number
839 AND mn_rec.unit_set_cd = in_rec.unit_set_cd
840 AND mn_rec.version_number = in_rec.version_number
841 AND mn_rec.admission_appl_number = in_rec.admission_appl_number
842 AND mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
843 COMMIT;
844 END IF;
845
846 -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
847 IF p_rule IN ('R') THEN
848 UPDATE igs_ad_unitsets_int
849 SET status = cst_s_val_3
850 , error_code = cst_ec_val_E700
851 , error_text = cst_et_val_E700
852 WHERE interface_run_id = p_interface_run_id
853 AND status = cst_s_val_2
854 AND match_ind IS NOT NULL;
855 COMMIT;
856 END IF;
857
858 END prc_apcnt_uset_apl;
859
860 END Igs_Ad_Imp_010;