1 PACKAGE BODY Igs_Ad_Imp_001 AS
2 /* $Header: IGSAD79B.pls 120.3 2006/02/21 22:50:38 arvsrini noship $ */
3 /* ------------------------------------------------------------------------------------------------------------------------
4 || Created By :
5 || Created On :
6 || Purpose : Main Import process package.
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || npalanis 17-FEB-2002 2758854 - New interface table race is created under person statistics
10 || source category
11 || ssawhney 7jan Changed IMP_ADM_DATA : Bug 2732600, HZ policy functions were giving issues.
12 Hence disabled the policy function.
13 || rrengara 11-Feb-2003 Changes for RCT Build 2664699
14 || Removed the procedure calls for importing inquiry programs, unitsets and program unitsets
15 || Added a call to import inquiry lines
16 || Also removed the references of old inquiry related tables and changed to IGS_RC tables
17 || pkpatel 6-NOV-2003 Bug 3130316 Added procedures print_stats and logerrormessage
18 || rbezawad 27-Feb-05 Added code to procedure update_parent_record_status() to execute a Dynamic Code block
19 when IGR functionality is enabled
20 ---------------------------------------------------------------------------------------------------------------------------*/
21 PROCEDURE logerrormessage(p_record IN VARCHAR2,
22 p_error IN VARCHAR2,
23 p_entity_name IN VARCHAR2 DEFAULT NULL,
24 p_match_ind IN VARCHAR2 DEFAULT NULL) AS
25 /*****************************************************************
26 Created By : asbala
27 Creation date : 9/23/2003
28 Purpose : This function is to print the statistics from igs_ad_imp_stats.
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34 l_context_token_str VARCHAR2(50);
35 BEGIN
36 IF p_entity_name IS NULL THEN
37 NULL;
38 ELSE
39 l_context_token_str := p_entity_name || ' - ';
40 END IF;
41
42 l_context_token_str := l_context_token_str || p_record;
43
44 IF p_match_ind IS NULL THEN
45 NULL;
46 ELSE
47 l_context_token_str := l_context_token_str || ' - ' || p_match_ind;
48 END IF;
49
50 -- Import Process Failed for Record: CONTEXT, Error: ERROR_CD
51 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_IMP_DET_ERROR');
52 FND_MESSAGE.SET_TOKEN('CONTEXT', l_context_token_str);
53 FND_MESSAGE.SET_TOKEN('ERROR_CD', p_error);
54
55 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
56 END logerrormessage;
57
58 PROCEDURE print_stats(p_interface_run_id IN igs_ad_interface_all.interface_run_id%TYPE) AS
59 /*****************************************************************
60 Created By : asbala
61 Creation date : 9/23/2003
62 Purpose : This function is to print the statistics from igs_ad_imp_stats.
63 Know limitations, enhancements or remarks
64 Change History
65 Who When What
66 (reverse chronological order - newest change first)
67 ***************************************************************/
68 CURSOR c_get_statistics (cp_lookup_type VARCHAR2,
69 cp_interface_run_id NUMBER) IS
70 SELECT total_rec_num, total_warn_num, total_success_num, total_error_num, meaning,entity_name
71 FROM igs_ad_imp_stats imp, igs_lookup_values lk
72 WHERE imp.src_cat_code = lk.lookup_code
73 AND lk.lookup_type = cp_lookup_type
74 AND imp.interface_run_id = cp_interface_run_id
75 ORDER BY meaning, entity_name;
76 BEGIN
77 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
78 FND_MESSAGE.SET_NAME('IGS','IGS_PE_IMP_HEADER1');
79 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
80 FND_MESSAGE.SET_NAME('IGS','IGS_PE_IMP_HEADER2');
81 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
82 /*
83 FND_FILE.PUT_LINE(FND_FILE.LOG,
84 'Category Entity Total No of Total No of Total No of Total No of');
85 FND_FILE.PUT_LINE(FND_FILE.LOG,
86 ' Records Processed Records Successful Records with Error Records with Warning');
87 */
88
89 FND_FILE.PUT_LINE(FND_FILE.LOG,
90 '---------------------------------------------------------------------------------------------------------------------------------------------------');
91 FOR get_statistics_rec IN c_get_statistics('IMP_CATEGORIES',p_interface_run_id)
92 LOOP
93 FND_FILE.PUT_LINE (FND_FILE.LOG, RPAD(get_statistics_rec.meaning,39,' ') ||
94 RPAD(get_statistics_rec.entity_name,29,' ') ||
95 RPAD(get_statistics_rec.total_rec_num,20,' ') ||
96 RPAD(get_statistics_rec.total_success_num,20,' ') ||
97 RPAD(get_statistics_rec.total_error_num,20,' ') ||
98 RPAD(get_statistics_rec.total_warn_num,20,' '));
99 END LOOP;
100 EXCEPTION
101 WHEN OTHERS THEN
102 NULL;
103 END print_stats;
104
105 PROCEDURE set_message(p_name IN VARCHAR2,
106 p_token_name IN VARCHAR2 DEFAULT NULL,
107 p_token_value IN VARCHAR2 DEFAULT NULL
108 ) AS
109 /*************************************************************
110 Created By : knag
111 Date Created By : 05-NOV-2003
112 Purpose : This procedure will accept message name, token name
113 and vale and write message text to logfile
114 Know limitations, enhancements or remarks
115 Change History
116 Who When What
117 (reverse chronological order - newest change first)
118 ***************************************************************/
119 BEGIN
120 FND_MESSAGE.SET_NAME('IGS',p_name);
121 IF p_token_name IS NOT NULL AND
122 p_token_value IS NOT NULL THEN
123 FND_MESSAGE.SET_TOKEN(p_token_name, p_token_value);
124 END IF;
125 Fnd_File.PUT_LINE(Fnd_File.LOG,FND_MESSAGE.GET);
126 END set_message;
127
128 PROCEDURE logHeader(p_proc_name VARCHAR2) AS
129 BEGIN
130 FND_FILE.PUT_LINE(FND_FILE.LOG,p_proc_name);
131 END;
132 PROCEDURE logdetail(p_debug_msg VARCHAR2) AS
133 BEGIN
134 FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_msg);
135 END;
136
137 PROCEDURE update_parent_record_status (p_source_type_id IN NUMBER,
138 p_batch_id IN NUMBER,
139 p_interface_run_id IN NUMBER
140 ) AS
141 /*************************************************************
142 Created By : knag
143 Date Created By : 05-NOV-2003
144 Purpose : This procedure will call all the procedures for admission and inquiry related categories
145 Know limitations, enhancements or remarks
146 Change History
147 Who When What
148 rbezawad 27-Feb-05 Added code to procedure update_parent_record_status() to execute a Dynamic Code block
149 when IGR functionality is enabled
150 (reverse chronological order - newest change first)
151 ***************************************************************/
152 l_prog_label VARCHAR2(4000);
153 l_label VARCHAR2(4000);
154 l_request_id NUMBER;
155 l_debug_str VARCHAR2(4000);
156
157 l_category_list VARCHAR2(32000) ;
158 l_entity_list VARCHAR2(32000) ;
159 start_pos_cat NUMBER;
160 end_pos_cat NUMBER;
161 cur_pos_cat NUMBER;
162 start_pos_tab NUMBER;
163 end_pos_tab NUMBER;
164 cur_pos_tab NUMBER;
165 l_category_name VARCHAR2(30);
166 l_entity_name VARCHAR2(30);
167
168 TYPE c_ref_cur_typ IS REF CURSOR;
169 c_ref_cur c_ref_cur_typ;
170
171 TYPE c_ref_cur_rec_typ IS RECORD (status VARCHAR2(1), reccount NUMBER);
172 c_ref_cur_rec c_ref_cur_rec_typ;
173
174 l_success NUMBER;
175 l_error NUMBER;
176 l_warning NUMBER;
177 l_total_rec NUMBER;
178 l_stmt VARCHAR2(2000);
179
180 BEGIN
181
182 l_prog_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status';
183 l_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status.';
184
185 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
186
187 IF (l_request_id IS NULL) THEN
188 l_request_id := fnd_global.conc_request_id;
189 END IF;
190
191 l_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status.begin';
192 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
193
194 fnd_log.string_with_context (fnd_log.level_procedure,
195 l_label,
196 l_debug_str,
197 NULL,NULL,NULL,NULL,NULL,
198 TO_CHAR(l_request_id));
199 END IF;
200
201 -- Based upon application instance child
202 UPDATE igs_ad_ps_appl_inst_int apinst
203 SET status = '4',
204 error_code = 'E347',
205 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
206 WHERE status = '1'
207 AND interface_run_id = p_interface_run_id
208 AND (
209 EXISTS (SELECT 1 FROM igs_ad_insthist_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
210 OR EXISTS (SELECT 1 FROM igs_ad_notes_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
211 OR EXISTS (SELECT 1 FROM igs_ad_unitsets_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
212 OR EXISTS (SELECT 1 FROM igs_ad_edugoal_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
213 );
214 COMMIT;
215
216 -- Based upon application child
217 UPDATE igs_ad_apl_int api
218 SET status = '4',
219 error_code = 'E347',
220 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
221 WHERE status = '1'
222 AND interface_run_id = p_interface_run_id
223 AND (
224 EXISTS (SELECT 1 FROM igs_ad_ps_appl_inst_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
225 OR EXISTS (SELECT 1 FROM igs_ad_othinst_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
226 OR EXISTS (SELECT 1 FROM igs_ad_acadint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
227 OR EXISTS (SELECT 1 FROM igs_ad_appint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
228 OR EXISTS (SELECT 1 FROM igs_ad_splint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
229 OR EXISTS (SELECT 1 FROM igs_ad_spltal_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
230 OR EXISTS (SELECT 1 FROM igs_ad_perstmt_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
231 OR EXISTS (SELECT 1 FROM igs_ad_fee_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
232 OR EXISTS (SELECT 1 FROM igs_ad_apphist_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
233 );
234 COMMIT;
235
236 -- Based upon transcript term child
237 UPDATE igs_ad_trmdt_int trmdt
238 SET status = '4',
239 error_code = 'E347',
240 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
241 WHERE status = '1'
242 AND interface_run_id = p_interface_run_id
243 AND EXISTS (SELECT 1 FROM igs_ad_tundt_int WHERE status <> '1' AND interface_term_dtls_id = trmdt.interface_term_dtls_id);
244 COMMIT;
245
246 -- Based upon transcript child
247 UPDATE igs_ad_txcpt_int txcpt
248 SET status = '4',
249 error_code = 'E347',
250 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
251 WHERE status = '1'
252 AND interface_run_id = p_interface_run_id
253 AND EXISTS (SELECT 1 FROM igs_ad_trmdt_int WHERE status <> '1' AND interface_transcript_id = txcpt.interface_transcript_id);
254 COMMIT;
255
256 -- Based upon academic history child
257 UPDATE igs_ad_acadhis_int_all acadhis
258 SET status = '4',
259 error_code = 'E347',
260 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
261 WHERE status = '1'
262 AND interface_run_id = p_interface_run_id
263 AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int WHERE status <> '1' AND interface_acadhis_id = acadhis.interface_acadhis_id);
264 COMMIT;
265
266 -- Based upon test result child
267 UPDATE igs_ad_test_int tst
268 SET status = '4',
269 error_code = 'E347',
270 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
271 WHERE status = '1'
272 AND interface_run_id = p_interface_run_id
273 AND EXISTS (SELECT 1 FROM igs_ad_test_segs_int WHERE status <> '1' AND interface_test_id = tst.interface_test_id);
274 COMMIT;
275
276 --Dynamic Code block to be executed when IGR functionality is enabled.
277 IF fnd_profile.value('IGS_RECRUITING_ENABLED') = 'Y' THEN
278 BEGIN
279 l_stmt := ' BEGIN
280 igr_imp_002.update_parent_record_status(:1);
281 END; ';
282 EXECUTE IMMEDIATE l_stmt USING p_interface_run_id;
283 EXCEPTION
284 WHEN OTHERS THEN
285 fnd_file.put_line(fnd_file.log,'Error occurred while calling IGR_IMP_002.UPDATE_PARENT_RECORD_STATUS() : '||SQLERRM);
286 END;
287 END IF;
288
289 -- Based upon person child
290 UPDATE igs_ad_interface ad
291 SET record_status = '3',
292 status = '4',
293 error_code = 'E347'
294 WHERE status = '1'
295 AND interface_run_id = p_interface_run_id
296 AND (
297 EXISTS (SELECT 1 FROM igs_ad_apl_int WHERE status <> '1' AND interface_id = ad.interface_id)
298 OR EXISTS (SELECT 1 FROM igs_ad_test_int WHERE status <> '1' AND interface_id = ad.interface_id)
299 OR EXISTS (SELECT 1 FROM igs_ad_recruit_int WHERE status <> '1' AND interface_id = ad.interface_id)
300 OR EXISTS (SELECT 1 FROM igs_uc_qual_ints WHERE status <> '1' AND interface_id = ad.interface_id)
301 OR EXISTS (SELECT 1 FROM igs_ad_acadhis_int_all WHERE status <> '1' AND interface_id = ad.interface_id)
302 OR EXISTS (SELECT 1 FROM igs_pe_cred_int WHERE status <> '1' AND interface_id = ad.interface_id)
303 );
304 COMMIT;
305
306 -- Based upon person
307 UPDATE igs_ad_interface_ctl
308 SET status = '3'
309 WHERE interface_run_id = p_interface_run_id
310 AND EXISTS (SELECT 1
311 FROM igs_ad_interface
312 WHERE interface_run_id = p_interface_run_id
313 AND (record_status <> '1' OR status <> '1'));
314
315 IF SQL%NOTFOUND THEN
316 UPDATE igs_ad_interface_ctl
317 SET status = '1'
318 WHERE interface_run_id = p_interface_run_id;
319 END IF;
320 COMMIT;
321
322 END update_parent_record_status;
323
324 PROCEDURE store_stats (p_source_type_id IN NUMBER,
325 p_batch_id IN NUMBER,
326 p_interface_run_id IN NUMBER,
327 p_category_entity_table IN g_category_entity_type_table
328 ) AS
329 /*************************************************************
330 Created By : knag
331 Date Created By : 05-NOV-2003
332 Purpose : This procedure will store process statistics of all
333 entities for included categories
334 Know limitations, enhancements or remarks
335 Change History
336 Who When What
337 (reverse chronological order - newest change first)
338 ***************************************************************/
339 l_prog_label VARCHAR2(4000);
340 l_label VARCHAR2(4000);
341 l_request_id NUMBER;
342 l_debug_str VARCHAR2(4000);
343
344 TYPE c_ref_cur_typ IS REF CURSOR;
345 c_ref_cur c_ref_cur_typ;
346
347 TYPE c_ref_cur_rec_typ IS RECORD (status VARCHAR2(1), reccount NUMBER);
348 c_ref_cur_rec c_ref_cur_rec_typ;
349
350 l_success NUMBER;
351 l_error NUMBER;
352 l_warning NUMBER;
353 l_total_rec NUMBER;
354
355 BEGIN
356
357 l_prog_label := 'igs.plsql.igs_ad_imp_001.store_stats';
358 l_label := 'igs.plsql.igs_ad_imp_001.store_stats.';
359
360 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
361
362 IF (l_request_id IS NULL) THEN
363 l_request_id := fnd_global.conc_request_id;
364 END IF;
365
366 l_label := 'igs.plsql.igs_ad_imp_001.store_stats.begin';
367 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
368
369 fnd_log.string_with_context (fnd_log.level_procedure,
370 l_label,
371 l_debug_str,
372 NULL,NULL,NULL,NULL,NULL,
373 TO_CHAR(l_request_id));
374 END IF;
375
376 FOR idx IN p_category_entity_table.first..p_category_entity_table.last
377 LOOP
378 l_success := 0;
379 l_error := 0;
380 l_warning := 0;
381 l_total_rec := 0;
382 IF igs_ad_gen_016.chk_src_cat (p_source_type_id, p_category_entity_table(idx).category_name) THEN
383 OPEN c_ref_cur FOR 'SELECT status, count(*) reccount FROM ' ||
384 p_category_entity_table(idx).entity_name ||
385 ' WHERE interface_run_id = :1 GROUP BY status'
386 USING p_interface_run_id;
387 LOOP
388 FETCH c_ref_cur INTO c_ref_cur_rec;
389 IF c_ref_cur%NOTFOUND THEN
390 CLOSE c_ref_cur;
391 EXIT;
392 END IF;
393
394 IF c_ref_cur_rec.status = '1' THEN
395 l_success := c_ref_cur_rec.reccount;
396 ELSIF c_ref_cur_rec.status = '3' THEN
397 l_error := c_ref_cur_rec.reccount;
398 ELSIF c_ref_cur_rec.status = '4' THEN
399 l_warning := c_ref_cur_rec.reccount;
400 END IF;
401 END LOOP;
402
403 IF l_success IS NULL THEN
404 l_success := 0;
405 END IF;
406 IF l_error IS NULL THEN
407 l_error := 0;
408 END IF;
409 IF l_warning IS NULL THEN
410 l_warning := 0;
411 END IF;
412
413 l_total_rec := l_success + l_error + l_warning;
414 /*********************************************************
415 dbms_output.put_line ('Category - ' || p_category_entity_table(idx).category_name ||' : '||
416 'Entity - ' || p_category_entity_table(idx).entity_name ||' : '||
417 'S - '|| to_char(l_success) ||' : '||
418 'E - '|| to_char(l_error) ||' : '||
419 'W - '|| to_char(l_warning) ||' : '||
420 'T - '|| to_char(l_total_rec));
421 *********************************************************/
422 INSERT INTO IGS_AD_IMP_STATS (
423 INTERFACE_RUN_ID,
424 SRC_CAT_CODE,
425 ENTITY_NAME,
426 TOTAL_REC_NUM,
427 TOTAL_WARN_NUM,
428 TOTAL_SUCCESS_NUM,
429 TOTAL_ERROR_NUM,
430 CREATED_BY,
431 CREATION_DATE,
432 LAST_UPDATED_BY,
433 LAST_UPDATE_DATE,
434 LAST_UPDATE_LOGIN,
435 REQUEST_ID,
436 PROGRAM_APPLICATION_ID,
437 PROGRAM_ID,
438 PROGRAM_UPDATE_DATE
439 ) VALUES (
440 p_interface_run_id,
441 p_category_entity_table(idx).category_name,
442 p_category_entity_table(idx).entity_name,
443 l_total_rec,
444 l_warning,
445 l_success,
446 l_error,
447 1,
448 sysdate,
449 1,
450 sysdate,
451 NULL,
452 NULL,
453 NULL,
454 NULL,
455 NULL
456 );
457 END IF ;
458 END LOOP;
459
460 END store_stats;
461
462 FUNCTION import_legacy_data (
463 p_batch_id NUMBER,
464 p_source_type_id NUMBER,
465 p_interface_run_id NUMBER
466 ) RETURN BOOLEAN IS
467
468 l_count1 NUMBER;
469 l_count2 NUMBER;
470
471 BEGIN
472
473 BEGIN
474 SELECT 1 INTO l_count1
475 FROM DUAL
476 WHERE EXISTS (SELECT 1
477 FROM igs_ad_interface int,
478 igs_uc_qual_ints qint
479 WHERE int.interface_id = qint.interface_id
480 AND int.source_type_id = p_source_type_id
481 AND int.batch_id = p_batch_id
482 AND int.status IN('1','4','2')
483 AND qint.status ='2');
484 EXCEPTION
485 WHEN OTHERS THEN
486 l_count1 := 0;
487 END;
488
489 BEGIN
490 SELECT 1 INTO l_count2
491 FROM DUAL
492 WHERE EXISTS (SELECT 1
493 FROM igs_ad_interface int,
494 igs_ad_apl_int aplint,
495 igs_ad_apphist_int applhist
496 WHERE int.interface_id = aplint.interface_id
497 AND aplint.interface_appl_id = applhist.interface_appl_id
498 AND int.source_type_id = p_source_type_id
499 AND int.batch_id = p_batch_id
500 AND int.status IN ( '1', '4', '2')
501 AND aplint.status IN ('1', '4', '2')
502 AND applhist.status = '2'
503 UNION ALL
504 SELECT 1
505 FROM igs_ad_interface int,
506 igs_ad_apl_int aplint,
507 igs_ad_ps_appl_inst_int aplinst,
508 igs_ad_insthist_int applinsthist
509 WHERE int.interface_id = aplint.interface_id
510 AND aplint.interface_appl_id = aplinst.interface_appl_id
511 AND applinsthist.interface_ps_appl_inst_id = aplinst.interface_ps_appl_inst_id
512 AND int.source_type_id = p_source_type_id
513 AND int.batch_id = p_batch_id
514 AND int.status IN ( '1', '4', '2')
515 AND aplint.status IN ('1', '4', '2')
516 AND aplinst.status IN ('1', '4', '2')
517 AND applinsthist.status = '2');
518 EXCEPTION
519 WHEN OTHERS THEN
520 l_count2 := 0;
521 END;
522
523 IF l_count1 = 1 OR l_count2 = 1 THEN
524 -- Failure Condition
525 UPDATE igs_ad_interface_ctl
526 SET status = '3'
527 WHERE interface_run_id = p_interface_run_id;
528 COMMIT;
529
530 IF l_count1 = 1 THEN
531 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cannot import Qualification Details in non-legacy mode of import');
532 ELSE
533 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cannot import Application History Details in non-legacy mode of import');
534 END IF;
535
536 RETURN TRUE;
537 END IF;
538 RETURN FALSE;
539
540 END import_legacy_data;
541
542 PROCEDURE imp_adm_data (
543 ERRBUF OUT NOCOPY VARCHAR2,
544 RETCODE OUT NOCOPY NUMBER ,
545 P_BATCH_ID IN NUMBER,
546 P_SOURCE_TYPE_ID IN NUMBER,
547 P_MATCH_SET_ID IN NUMBER,
548 P_LEGACY_IND IN VARCHAR2 ,
549 P_ENABLE_LOG IN VARCHAR2,
550 P_ACAD_CAL_TYPE IN VARCHAR2,
551 P_ACAD_SEQUENCE_NUMBER IN NUMBER,
552 P_ADM_CAL_TYPE IN VARCHAR2,
553 P_ADM_SEQUENCE_NUMBER IN NUMBER,
554 P_ADMISSION_CAT IN VARCHAR2,
555 P_S_ADMISSION_PROCESS_TYPE IN VARCHAR2,
556 P_INTERFACE_RUN_ID IN NUMBER,
557 P_ORG_ID IN NUMBER
558 ) AS
559 /*----------------------------------------------------------------------------------
560 || Created By : pkpatel
561 || Created On : 22-JUN-2001
562 || Purpose : This procedure process the Application
563 || Known limitations, enhancements or remarks :
564 || Change History :
565 || Who When What
566 || gmaheswa 17-Jan-06 4938278: disable Business Events before starting bulk import process and enable after import.
567 || rrengara 20-jan-2003 Bug 2711176 , Gather statistics
568 || ssawhney 7jan Bug 2732600, HZ policy functions were giving issues. Hence disabled the policy functions.
569 || pkpatel 25-DEC-2002 Bug No: 2702536
570 || Removed the duplicate checking procedure IGS_AD_IMP_FIND_DUP_PERSONS to IGSAD80B.
571 || Modified the signature of Igs_Ad_Imp_002.PRC_PE_DTLS
572 || Modified the count of record processed to be based on RECORD_STATUS
573 || pkpatel 17-DEC-2002 Bug No: 2695902
574 || Added delete logic for Residency Details
575 || Modified p_interface_run_id to l_interface_run_id so that the interface records can be updated with proper interface run id.
576 || gmuralid 4-DEC-2002 Change by gmuralid, removed reference to table igs_ad_intl_int,
577 || igs_pe_fund_dep_int.Included references to igs_pe_visa_int,
578 || igs_pe_vst_hist_int,igs_pe_passport_int,igs_pe_eit_int in delete logic
579 || As a part of BUG 2599109, SEVIS Build
580 || npalanis 21-May-2002 Code is added to update interface_run_id in igs_ad_interface records
581 || with status '1' ,'2' and '4' .The parameter p_interface_run_id passed
582 || to prc_pe_dtls is also removed as no more updation of interface_run_id
583 || is required there.
584 || rrengara 4-OCT-2002 Changed the ordering of the parameters batch id and source type id for the Build bug 2604395
585 || Called IGS_AD_INTERFACE_CTL tables TBH and assigned l_interface_run_id to the value from OUT NOCOPY parameter TBH
586 ||
587 || ssawhney 28-oct-2002 SWS104- Jan03 build residency details import added. moved acad honors to person level
588 || IGS_AD_REFS_INT table obsoleted.
589 || sjalsaut Oct 31, 02 SWSCR012 Bug 2435520 Removed College Activities references
590 || and changed extracurr act to PERSON_ACTIVITIES
591 || (reverse chronological order - newest change first)
592 ||--------------------------------------------------------------------------------*/
593 l_prog_label VARCHAR2(4000);
594 l_label VARCHAR2(4000);
595 l_request_id NUMBER;
596 l_debug_str VARCHAR2(4000);
597
598 l_return BOOLEAN;
599 l_status VARCHAR2(5);
600 l_industry VARCHAR2(5);
601 l_schema VARCHAR2(30);
602
603 l_batch_desc igs_ad_imp_batch_det.batch_desc%TYPE;
604 l_source_type igs_pe_src_types_all.system_source_type%TYPE;
605 l_match_set_name igs_pe_match_sets_all.match_set_name%TYPE;
606 l_interface_run_id igs_ad_interface_ctl.interface_run_id%TYPE;
607 l_rowid VARCHAR2(100);
608 l_cnt_dup_process_run NUMBER;
609 l_err_msg VARCHAR2(4000);
610 BEGIN
611 l_prog_label := 'igs.plsql.igs_ad_imp_001.imp_adm_data';
612 l_label := 'igs.plsql.igs_ad_imp_001.imp_adm_data.';
613
614 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
615
616 IF (l_request_id IS NULL) THEN
617 l_request_id := fnd_global.conc_request_id;
618 END IF;
619
620 l_label := 'igs.plsql.igs_ad_imp_001.imp_adm_data.begin';
621 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
622
623 fnd_log.string_with_context (fnd_log.level_procedure,
624 l_label,
625 l_debug_str,
626 NULL,NULL,NULL,NULL,NULL,
627 TO_CHAR(l_request_id));
628 END IF;
629
630 retcode := 0;
631 igs_ge_gen_003.set_org_id(p_org_id);
632
633 --Disable Business Event before running Bulk Process
634 IGS_PE_GEN_003.TURNOFF_TCA_BE (
635 P_TURNOFF => 'Y'
636 );
637
638 igs_ge_msg_stack.initialize;
639
640 BEGIN
641 SELECT batch_desc INTO l_batch_desc
642 FROM igs_ad_imp_batch_det
643 WHERE batch_id = p_batch_id ;
644
645 SELECT system_source_type INTO l_source_type
646 FROM igs_pe_src_types_all
647 WHERE source_type_id = p_source_type_id
648 AND NVL(closed_ind,'N') = 'N'
649 AND system_source_type IN ('APPLICATION', 'TEST_RESULTS', 'PROSPECT_LIST', 'PROSPECT_SS_WEB_INQUIRY', 'TRANSCRIPT');
650
651 SELECT match_set_name INTO l_match_set_name
652 FROM igs_pe_match_sets_all
653 WHERE match_set_id = p_match_set_id
654 AND closed_ind = 'N';
655 EXCEPTION
656 WHEN OTHERS THEN
657 l_batch_desc := NULL;
658 l_source_type := NULL;
659 l_match_set_name := NULL;
660 END;
661
662 IF l_batch_desc IS NULL OR
663 l_source_type IS NULL OR
664 l_match_set_name IS NULL THEN
665 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
666 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch ID :' || p_batch_id );
667 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Source Type ID :' || p_source_type_id );
668 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Match Set ID :' || p_match_set_id );
669 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Batch OR Source Type OR Match Set');
670 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
671 --Enable Business Event before quiting Bulk Process
672 IGS_PE_GEN_003.TURNOFF_TCA_BE (
673 P_TURNOFF => 'N'
674 );
675 RETURN;
676 ELSE
677 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
678 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch ID :' || p_batch_id ||' '|| 'Batch Description :' || l_batch_desc );
679 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Source Type ID :' || p_source_type_id ||' '|| 'Source Type :' || l_source_type );
680 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Match Set ID :' || p_match_set_id ||' '|| 'Match Set Name :' || l_match_set_name );
681 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
682 END IF;
683 igs_ad_interface_ctl_pkg.insert_row (
684 x_rowid => l_rowid,
685 x_interface_run_id => l_interface_run_id ,
686 x_source_type_id => p_source_type_id,
687 x_batch_id => p_batch_id,
688 x_match_set_id => p_match_set_id,
689 x_status => '2',
690 x_mode => 'R');
691 COMMIT;
692 SELECT COUNT (*) INTO l_cnt_dup_process_run
693 FROM igs_ad_interface_ctl
694 WHERE batch_id = p_batch_id
695 AND source_type_id = p_source_type_id
696 AND status = '2';
697 IF l_cnt_dup_process_run > 1 THEN
698 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Another import process with same batch and source type is currently under execution hence aborting.');
699 APP_EXCEPTION.RAISE_EXCEPTION;
700 ELSE
701 igs_ad_imp_001.g_interface_run_id := l_interface_run_id;
702 igs_ad_imp_001.g_enable_log := p_enable_log;
703
704 IF p_legacy_ind = 'N' THEN
705 IF import_legacy_data (
706 p_batch_id => p_batch_id,
707 p_source_type_id => p_source_type_id,
708 p_interface_run_id => l_interface_run_id ) THEN
709
710 --Enable Business Event before quiting Bulk Process
711 IGS_PE_GEN_003.TURNOFF_TCA_BE (
712 P_TURNOFF => 'N'
713 );
714
715 RETURN;
716 END IF;
717 END IF;
718 -- Update the interface_run_id for IGS_AD_INTERFACE records with status in 1,2,4 for Bug - 2377123
719 UPDATE igs_ad_interface_all
720 SET interface_run_id = l_interface_run_id
721 WHERE batch_id = p_batch_id
722 AND source_type_id = p_source_type_id
723 AND status IN ('1','2','4');
724 COMMIT;
725
726 -- Update the interface records if the interface ids are duplicate (within the batch and across the batch)
727 UPDATE igs_ad_interface_all int1
728 SET status ='3',
729 error_code = 'E712'
730 WHERE EXISTS ( SELECT 1 FROM igs_ad_interface_all
731 WHERE interface_id = int1.interface_id
732 AND rowid <> int1.rowid )
733 AND interface_run_id = l_interface_run_id;
734 COMMIT;
735
736 -- To fetch table schema name for gather statistics
737 l_return := fnd_installation.get_app_info('IGS', l_status, l_industry, l_schema);
738
739 -- Gather statistics of interface tables
740 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
741 tabname => 'IGS_AD_INTERFACE_ALL',
742 cascade => TRUE);
743
744 -------------------------------------------------------
745 -- disable HZ security policy before starting the import.
746 -------------------------------------------------------
747 HZ_COMMON_PUB.DISABLE_CONT_SOURCE_SECURITY ;
748 -- Process categories for import
749 igs_pe_pers_imp_001.prc_pe_category (p_batch_id => p_batch_id,
750 p_source_type_id => p_source_type_id,
751 p_match_set_id => p_match_set_id,
752 p_interface_run_id => l_interface_run_id);
753 igs_ad_imp_015.prc_ad_category (p_source_type_id => p_source_type_id,
754 p_batch_id => p_batch_id,
755 p_interface_run_id => l_interface_run_id,
756 p_enable_log => p_enable_log,
757 p_legacy_ind => p_legacy_ind);
758
759 -- Update category entities if child has failure (traverse parent to super parent)
760 -- Update parent based on p_interface_run_id
761 -- Select child based on FK link and not p_interface_run_id
762 igs_ad_imp_001.update_parent_record_status (p_source_type_id => p_source_type_id,
763 p_batch_id => p_batch_id,
764 p_interface_run_id => l_interface_run_id);
765
766 -- Create process statistics
767 igs_ad_imp_015.store_ad_stats (p_source_type_id => p_source_type_id,
768 p_batch_id => p_batch_id,
769 p_interface_run_id => l_interface_run_id);
770
771 -- Delete successfully imported records from the interface table with the interface_run_id value
772 igs_pe_pers_imp_001.del_cmpld_pe_records(p_batch_id);
773
774 igs_ad_imp_015.del_cmpld_ad_records (p_source_type_id => p_source_type_id,
775 p_batch_id => p_batch_id,
776 p_interface_run_id => l_interface_run_id);
777 DELETE FROM igs_ad_interface_all
778 WHERE status = '1'
779 AND record_status ='1'
780 AND interface_run_id = l_interface_run_id;
781
782 UPDATE igs_ad_interface_all
783 SET record_status = '3'
784 WHERE interface_run_id = l_interface_run_id
785 AND status <> '1';
786 COMMIT;
787 -- Write process statistics to logfile
788 igs_ad_imp_001.print_stats (l_interface_run_id);
789 -------------------------------------------------------
790 -- enable HZ security policy if abnormal termination
791 -------------------------------------------------------
792 HZ_COMMON_PUB.ENABLE_CONT_SOURCE_SECURITY ;
793 END IF;
794
795 --Enable Business Event before quiting Bulk Process
796 IGS_PE_GEN_003.TURNOFF_TCA_BE (
797 P_TURNOFF => 'N'
798 );
799 EXCEPTION
800 WHEN OTHERS THEN
801 retcode:=2;
802 l_err_msg := SQLERRM;
803
804 logdetail('EXCEPTION FROM Import Process' || l_err_msg);
805 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
806 igs_ge_msg_stack.conc_exception_hndl;
807
808 -- Failure Condition
809
810 IF l_interface_run_id IS NOT NULL THEN
811 UPDATE igs_ad_interface_ctl
812 SET status = '3'
813 WHERE rowid = l_rowid;
814 COMMIT;
815 END IF;
816
817 --Enable Business Event before quiting Bulk Process
818 IGS_PE_GEN_003.TURNOFF_TCA_BE (
819 P_TURNOFF => 'N'
820 );
821 -------------------------------------------------------
822 -- enable HZ security policy if abnormal termination
823 -------------------------------------------------------
824 HZ_COMMON_PUB.ENABLE_CONT_SOURCE_SECURITY ;
825
826 END imp_adm_data;
827
828
829 FUNCTION find_source_cat_rule(
830 P_Source_type_id IN NUMBER,
831 p_Category IN VARCHAR2 ) RETURN VARCHAR2
832 AS
833 /*
834 || Created By : nsinha
835 || Created On : 22-JUN-2001
836 || Purpose : This function returns the rule for a category for a source type
837 || Find out NOCOPY from IGS_AD_SRC_CAT the rule for the p_source_type_id and
838 || category_cd passed as the parameter.
839 || Known limitations, enhancements or remarks :
840 || Change History :
841 || Who When What
842 || pkpatel 22-Jun-2001 For Modeling and Forecasting DLD modified the code
843 || To return a value 'D' for Attribute level discrepancy rule.
844 || (reverse chronological order - newest change first)
845 */
846 CURSOR discrepancy_rule_cur IS
847 SELECT *
848 FROM igs_ad_source_cat
849 WHERE source_type_id = p_source_type_id
850 AND category_name = p_category;
851 discrepancy_rule_rec discrepancy_rule_cur%ROWTYPE;
852 l_disp_rule igs_ad_source_cat.discrepancy_rule_cd%TYPE;
853
854 BEGIN
855
856 OPEN discrepancy_rule_cur;
857 FETCH discrepancy_rule_cur INTO discrepancy_rule_rec;
858 CLOSE discrepancy_rule_cur;
859
860 IF NVL(discrepancy_rule_rec.detail_Level_Ind,'N') = 'Y' THEN
861 l_disp_rule := 'D'; -- Detail Level discrepancy rule is checked.
862 ELSE -- Check discrepancy rule at Table Level.
863 l_disp_rule := discrepancy_rule_rec.discrepancy_rule_cd;
864 END IF;
865
866 RETURN l_disp_rule;
867
868 END find_source_cat_rule;
869
870 END Igs_Ad_Imp_001;
[Home] [Help]