1 PACKAGE BODY igs_pe_pers_imp_001 AS
2 /* $Header: IGSPE15B.pls 120.3 2006/04/27 07:38:54 prbhardw noship $ */
3
4
5 -- These are the package variables to hold the value of whether the particular category is included or not.
6
7 g_person_type_inc BOOLEAN;
8 g_person_stat_inc BOOLEAN;
9 g_person_addr_inc BOOLEAN;
10 g_person_alias_inc BOOLEAN;
11 g_person_id_types_inc BOOLEAN;
12 g_person_spcl_need_inc BOOLEAN;
13 g_person_emp_dtl_inc BOOLEAN;
14 g_person_int_dtl_inc BOOLEAN;
15 g_person_hlth_ins_inc BOOLEAN;
16 g_person_mil_dtl_inc BOOLEAN;
17 g_person_act_inc BOOLEAN;
18 g_person_rel_inc BOOLEAN;
19 g_person_ath_inc BOOLEAN;
20 g_person_lang_inc BOOLEAN;
21 g_person_contact_inc BOOLEAN;
22 g_person_disc_dtls_inc BOOLEAN;
23 g_person_housing_stat_inc BOOLEAN;
24 g_person_acad_honors_inc BOOLEAN;
25 g_person_res_dtl_inc BOOLEAN;
26 g_rel_acad_hist_inc BOOLEAN;
27 g_rel_addr_inc BOOLEAN;
28 g_rel_contact_inc BOOLEAN;
29 g_rel_empl_dtl_inc BOOLEAN;
30 g_privacy_dtl_inc BOOLEAN;
31 --These variables are added as part of Admissions Import process Enhancements Bug 3191401
32 g_person_creds_inc BOOLEAN;
33 g_acad_hist_inc BOOLEAN;
34
35 PROCEDURE prc_pe_category(
36 p_batch_id IN NUMBER,
37 p_source_type_id IN NUMBER,
38 p_match_set_id IN NUMBER,
39 p_interface_run_id IN NUMBER
40 )
41 AS
42 /*************************************************************
43 Created By :pkpatel
44 Date Created By :29-APR-2003
45 Purpose : This procedure will call all the procedures for person related categories
46 Know limitations, enhancements or remarks
47 Change History
48 Who When What
49 (reverse chronological order - newest change first)
50 asbala 13-OCT-2003 Bug 3130316. Import Process Logging Framework Related changes.
51 asbala 21-OCT-2003 Bug 3130316. Import Process - New logic to delete completed records.
52 ***************************************************************/
53 l_meaning igs_lookup_values.meaning%TYPE;
54 l_count NUMBER;
55 l_count1 NUMBER;
56 l_count2 NUMBER;
57 l_count3 NUMBER;
58 l_count4 NUMBER;
59 l_count5 NUMBER;
60 l_count6 NUMBER;
61 l_var VARCHAR2(1);
62 l_enable_log VARCHAR2(1);
63 l_interface_run_id IGS_AD_INTERFACE_CTL.interface_run_id%TYPE;
64 l_status VARCHAR2(5);
65 l_industry VARCHAR2(5);
66 l_schema VARCHAR2(30);
67 l_return BOOLEAN;
68
69 CURSOR meaning_cur(cp_lookup_code igs_lookup_values.lookup_code%TYPE,
70 cp_lookup_type igs_lookup_values.lookup_type%TYPE)
71 IS
72 SELECT meaning
73 FROM igs_lookup_values
74 WHERE lookup_type = cp_lookup_type AND
75 lookup_code = cp_lookup_code;
76
77 BEGIN
78 -- Process person related source categories
79 igs_pe_pers_imp_001.set_stat_matc_rvw_pers_rcds(p_source_type_id,
80 p_batch_id);
81
82 l_return := fnd_installation.get_app_info('IGS', l_status, l_industry, l_schema);
83
84 l_enable_log := igs_ad_imp_001.g_enable_log;
85 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
86
87 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
88 tabname => 'IGS_AD_INTERFACE_ALL',
89 cascade => TRUE);
90
91 -- The logic in this procedure is :
92 -- 1. The interface_run_id is updated in all tables and the statistics are gathered.
93 -- 2. Then all records with match_ind = '22' (ie., reviewed) are made status '1' and the respective processes are
94 -- called for further processing
95
96 -- Delete all the records before processing for duplicate check.
97 DELETE FROM igs_ad_imp_near_mtch_all
98 WHERE interface_id IN
99 (SELECT interface_id FROM igs_ad_interface_all
100 WHERE interface_run_id = l_interface_run_id AND
101 status='2');
102
103 -- Populating the child interface table with the interface_run_id value.
104 UPDATE igs_ad_api_int_all aapi
105 SET interface_run_id=l_interface_run_id
106 WHERE aapi.status='2' AND
107 EXISTS (SELECT 1
108 FROM igs_ad_interface_all ai
109 WHERE
110 ai.interface_id=aapi.interface_id AND
111 ai.status IN ('1','2') AND
112 ai.interface_run_id=l_interface_run_id);
113
114 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
115 tabname => 'IGS_AD_API_INT_ALL',
116 cascade => TRUE);
117
118 -- Populating the child interface table with the interface_run_id value.
119 UPDATE igs_ad_stat_int_all adi
120 SET interface_run_id=l_interface_run_id
121 WHERE adi.status='2' AND
122 EXISTS (SELECT 1
123 FROM igs_ad_interface_all ai
124 WHERE ai.interface_id=adi.interface_id AND
125 ai.status IN ('1','2') AND
126 ai.interface_run_id=l_interface_run_id);
127
128 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
129 tabname => 'IGS_AD_STAT_INT_ALL',
130 cascade => TRUE);
131
132 -- Populating the child interface table with the interface_run_id value.
133 UPDATE IGS_AD_ADDR_INT_ALL ait
134 SET
135 interface_run_id=l_interface_run_id
136 WHERE ait.status='2' AND
137 EXISTS (SELECT 1
138 FROM igs_ad_interface_all ai
139 WHERE
140 ai.interface_id=ait.interface_id AND
141 ai.status IN ('1','2') AND
142 ai.interface_run_id=l_interface_run_id);
143
144 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
145 tabname => 'IGS_AD_ADDR_INT_ALL',
146 cascade => TRUE);
147
148 -- Populating the child interface table with the interface_run_id value.
149 UPDATE IGS_AD_ADDRUSAGE_INT_ALL ait
150 SET interface_run_id=l_interface_run_id
151 WHERE ait.status='2' AND
152 EXISTS (SELECT 1
153 FROM IGS_AD_ADDR_INT_ALL ai
154 WHERE
155 ai.interface_addr_id = ait.interface_addr_id AND
156 ai.status IN ('1','2') AND
157 ai.interface_run_id=l_interface_run_id);
158
159 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
160 tabname => 'IGS_AD_ADDRUSAGE_INT_ALL',
161 cascade => TRUE);
162
163 -- Update records with match_ind '22' to status = '1'
164 -- Person Details
165 UPDATE IGS_AD_INTERFACE_all SET STATUS = '1'
166 WHERE PERSON_MATCH_IND = '22' AND STATUS = '2'
167 AND SOURCE_TYPE_ID = P_SOURCE_TYPE_ID
168 AND BATCH_ID = P_BATCH_ID;
169
170 UPDATE IGS_AD_STAT_INT_all SET STATUS = '1'
171 WHERE MATCH_IND = '22' AND STATUS = '2'
172 AND INTERFACE_RUN_ID = l_interface_run_id;
173
174 -- Address Details
175 UPDATE IGS_AD_ADDR_INT_all SET STATUS = '1'
176 WHERE MATCH_IND = '22' AND STATUS = '2'
177 AND INTERFACE_RUN_ID = l_interface_run_id;
178
179 -- Address Usages
180 UPDATE IGS_AD_ADDRUSAGE_INT_all iau SET STATUS = '1'
181 WHERE MATCH_IND = '22' AND STATUS = '2'
182 AND INTERFACE_RUN_ID = l_interface_run_id;
183
184 UPDATE IGS_AD_API_INT_all SET STATUS = '1'
185 WHERE MATCH_IND = '22' AND STATUS = '2'
186 AND INTERFACE_RUN_ID = l_interface_run_id;
187
188 igs_ad_imp_002.prc_pe_dtls
189 (p_d_batch_id => p_batch_id,
190 p_d_source_type_id => p_source_type_id,
191 p_match_set_id => p_match_set_id
192 );
193
194 IF g_person_type_inc THEN
195 OPEN meaning_cur('PERSON_TYPE','IMP_CATEGORIES');
196 FETCH meaning_cur INTO l_meaning;
197 CLOSE meaning_cur;
198
199 IF l_enable_log = 'Y' THEN
200 FND_MESSAGE.SET_NAME('IGS','IGS_PE_BEG_IMP');
201 FND_MESSAGE.SET_TOKEN('TYPE_NAME',l_meaning);
202 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
203 END IF;
204
205 -- pupulate the child table with the interface run ID from the package.
206
207 UPDATE igs_pe_type_int pti
208 SET interface_run_id=l_interface_run_id
209 WHERE pti.status='2' AND
210 EXISTS (SELECT 1
211 FROM igs_ad_interface_all ai
212 WHERE
213 ai.interface_id=pti.interface_id AND
214 ai.status IN ('1','4') AND
215 ai.interface_run_id=l_interface_run_id);
216
217 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
218 tabname => 'IGS_PE_TYPE_INT',
219 cascade => TRUE);
220
221 -- Person Types
222 UPDATE igs_pe_type_int SET status = '1'
223 WHERE match_ind = '22' AND status = '2'
224 AND INTERFACE_RUN_ID = l_interface_run_id;
225
226 igs_ad_imp_013.prc_pe_type(
227 p_source_type_id=>p_source_type_id ,
228 p_batch_id=>p_batch_id );
229 END IF;
230
231
232 IF g_person_stat_inc THEN
233 IF l_enable_log = 'Y' THEN
234 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_STAT');
235 END IF;
236
237
238 -- Populating the child interface table with the interface_run_id value.
239 UPDATE igs_pe_eit_int pei
240 SET
241 interface_run_id=l_interface_run_id
242 WHERE pei.status='2' AND
243 EXISTS (SELECT 1
244 FROM igs_ad_interface_all ai
245 WHERE
246 ai.interface_id=pei.interface_id AND
247 ai.status IN ('1','4') AND
248 ai.interface_run_id=l_interface_run_id);
249
250
251 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
252 tabname => 'IGS_PE_EIT_INT',
253 cascade => TRUE);
254
255 -- Populating the child interface table with the interface_run_id value.
256 UPDATE igs_pe_race_int adli
257 SET
258 interface_run_id=l_interface_run_id
259 WHERE adli.status='2' AND
260 EXISTS (SELECT 1
261 FROM igs_ad_interface_all ai
262 WHERE
263 ai.interface_id=adli.interface_id AND
264 ai.status IN ('1','4') AND
265 ai.interface_run_id=l_interface_run_id);
266
267 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
268 tabname => 'IGS_PE_RACE_INT',
269 cascade => TRUE);
270
271 UPDATE igs_pe_eit_int
272 SET status = '1'
273 WHERE match_ind = '22' AND
274 status = '2' AND
275 INTERFACE_RUN_ID = l_interface_run_id;
276
277 UPDATE igs_pe_race_int
278 SET status = '1'
279 WHERE match_ind = '22' AND
280 status = '2' AND
281 INTERFACE_RUN_ID = l_interface_run_id;
282
283 Igs_Ad_Imp_008.PRC_PE_STAT(
284 p_source_type_id=>p_source_type_id ,
285 p_batch_id=>p_batch_id );
286
287 END IF;
288
289 IF g_person_addr_inc THEN
290
291 IF l_enable_log = 'Y' THEN
292 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_ADDR');
293 END IF;
294
295
296 Igs_Ad_Imp_026.PRC_PE_ADDR(
297 p_source_type_id=>p_source_type_id,
298 p_batch_id=>p_batch_id );
299
300 END IF;
301
302
303 IF g_person_alias_inc THEN
304 IF l_enable_log = 'Y' THEN
305 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_ALIAS');
306 END IF;
307
308 -- Populating the child interface table with the interface_run_id value.
309 UPDATE igs_ad_alias_int_all adai
310 SET
311 interface_run_id=l_interface_run_id
312 WHERE adai.status='2' AND
313 EXISTS (SELECT 1
314 FROM igs_ad_interface_all ai
315 WHERE
316 ai.interface_id=adai.interface_id AND
317 ai.status IN ('1','4') AND
318 ai.interface_run_id=l_interface_run_id);
319
320 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
321 tabname => 'IGS_AD_ALIAS_INT_ALL',
322 cascade => TRUE);
323
324 UPDATE IGS_AD_ALIAS_INT_all SET STATUS = '1'
325 WHERE MATCH_IND = '22' AND STATUS = '2'
326 AND INTERFACE_RUN_ID = l_interface_run_id;
327
328 Igs_Ad_Imp_006.PRC_PE_ALIAS(
329 p_source_type_id=>p_source_type_id,
330 p_batch_id=>p_batch_id);
331 END IF;
332
333
334 IF g_person_id_types_inc THEN
335 IF l_enable_log = 'Y' THEN
336 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_ID_TYP');
337 END IF;
338
339 Igs_Ad_Imp_007.PRC_PE_ID_TYPES(
340 p_source_type_id=>p_source_type_id,
341 p_batch_id=>p_batch_id );
342 END IF;
343
344
345 IF g_person_spcl_need_inc THEN
346 IF l_enable_log = 'Y' THEN
347 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_SPL_NEED');
348 END IF;
349
350 -- pupulate the child table with the interface run ID from the package.
351 UPDATE igs_ad_disablty_int_all adi
352 SET interface_run_id=l_interface_run_id
353 WHERE adi.status='2' AND
354 EXISTS (SELECT 1
355 FROM igs_ad_interface_all ai
356 WHERE ai.interface_id=adi.interface_id AND
357 ai.status IN ('1','4') AND
358 ai.interface_run_id=l_interface_run_id);
359
360 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
361 tabname => 'IGS_AD_DISABLTY_INT_ALL',
362 cascade => TRUE);
363
364 -- pupulate the child table with the interface run ID from the package.
365 UPDATE IGS_AD_DISABLTY_INT_all SET STATUS = '1'
366 WHERE MATCH_IND = '22' AND STATUS = '2'
367 AND INTERFACE_RUN_ID = l_interface_run_id;
368
369 UPDATE igs_pe_sn_srvce_int snci
370 SET interface_run_id=l_interface_run_id
371 WHERE snci.status='2' AND
372 EXISTS (SELECT 1
373 FROM igs_ad_interface_all ai,
374 igs_ad_disablty_int_all adi
375 WHERE ai.interface_id=adi.interface_id AND
376 adi.INTERFACE_DISABLTY_ID=snci.INTERFACE_DISABLTY_ID AND
377 ai.status IN ('1','4') AND
378 ai.interface_run_id=l_interface_run_id);
379
380 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
381 tabname => 'IGS_PE_SN_SRVCE_INT',
382 cascade => TRUE);
383
384 -- pupulate the child table with the interface run ID from the package.
385 UPDATE igs_pe_sn_srvce_int
386 SET status = '1'
387 WHERE match_ind = '22' AND
388 status = '2' AND
389 INTERFACE_RUN_ID = l_interface_run_id;
390
391 UPDATE igs_pe_sn_conct_int psci
392 SET
393 interface_run_id=l_interface_run_id
394 WHERE psci.status='2' AND
395 EXISTS (SELECT 1
396 FROM igs_ad_interface_all ai,
397 igs_ad_disablty_int_all adi
398 WHERE
399 ai.interface_id=adi.interface_id AND
400 adi.INTERFACE_DISABLTY_ID=psci.INTERFACE_DISABLTY_ID AND
401 ai.status IN ('1','4') AND
402 ai.interface_run_id=l_interface_run_id);
403
404 -- gather statistics for the table after populating it's interface_run_id
405 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
406 tabname => 'IGS_PE_SN_CONCT_INT',
407 cascade => TRUE);
408 UPDATE igs_pe_sn_conct_int
409 SET status = '1'
410 WHERE match_ind = '22' AND
411 status = '2' AND
412 INTERFACE_RUN_ID = l_interface_run_id;
413
414 Igs_Ad_Imp_008.PRC_PE_SPL_NEEDS(
415 p_source_type_id=>p_source_type_id,
416 p_batch_id=>p_batch_id );
417
418 END IF;
419
420 IF g_person_emp_dtl_inc THEN
421 IF l_enable_log = 'Y' THEN
422 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_EMP_DTL');
423 END IF;
424
425 -- Populating the child interface table with the interface_run_id value.
426 UPDATE igs_ad_emp_int_all admpi
427 SET
428 interface_run_id=l_interface_run_id
429 WHERE admpi.status='2' AND
430 EXISTS (SELECT 1
431 FROM igs_ad_interface_all ai
432 WHERE
433 ai.interface_id=admpi.interface_id AND
434 ai.status IN ('1','4') AND
435 ai.interface_run_id=l_interface_run_id);
436
437 -- gather statistics for the table after populating it's interface_run_id
438 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
439 tabname => 'IGS_AD_EMP_INT_ALL',
440 cascade => TRUE);
441
442 UPDATE IGS_AD_EMP_INT_all SET STATUS = '1'
443 WHERE MATCH_IND = '22' AND STATUS = '2'
444 AND INTERFACE_RUN_ID = l_interface_run_id;
445
446 Igs_Ad_Imp_006.PRC_PE_EMPNT_DTLS(
447 p_source_type_id=>p_source_type_id,
448 p_batch_id=>p_batch_id );
449
450 END IF;
451
452 IF g_person_int_dtl_inc THEN
453 IF l_enable_log = 'Y' THEN
454 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_INTL_DTL');
455 END IF;
456
457 -- Populating the child interface table with the interface_run_id value.
458 UPDATE IGS_PE_VISA_INT pvi
459 SET
460 interface_run_id=l_interface_run_id
461 WHERE pvi.status IN ('1','2') AND
462 EXISTS (SELECT 1
463 FROM igs_ad_interface_all ai
464 WHERE
465 ai.interface_id=pvi.interface_id AND
466 ai.status IN ('1','4') AND
467 ai.interface_run_id=l_interface_run_id);
468
469 -- gather statistics for the table after populating it's interface_run_id
470 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
471 tabname => 'IGS_PE_VISA_INT',
472 cascade => TRUE);
473
474 UPDATE IGS_PE_VISA_INT SET STATUS = '1'
475 WHERE MATCH_IND = '22' AND STATUS = '2'
476 AND INTERFACE_RUN_ID = l_interface_run_id;
477
478 -- Populating the child interface table with the interface_run_id value.
479 UPDATE IGS_PE_PASSPORT_INT ppi
480 SET
481 interface_run_id=l_interface_run_id
482 WHERE ppi.status='2' AND
483 EXISTS (SELECT 1
484 FROM igs_ad_interface_all ai
485 WHERE
486 ai.interface_id=ppi.interface_id AND
487 ai.status IN ('1','4') AND
488 ai.interface_run_id=l_interface_run_id);
489
490 -- gather statistics for the table after populating it's interface_run_id
491 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
492 tabname => 'IGS_PE_PASSPORT_INT',
493 cascade => TRUE);
494 UPDATE IGS_PE_PASSPORT_INT SET STATUS = '1'
495 WHERE MATCH_IND = '22' AND STATUS = '2'
496 AND INTERFACE_RUN_ID = l_interface_run_id;
497
498 -- Populating the child interface table with the interface_run_id value.
499 UPDATE IGS_PE_VST_HIST_INT pvhi
500 SET
501 interface_run_id=l_interface_run_id
502 WHERE pvhi.status='2' AND
503 EXISTS (SELECT 1
504 FROM igs_ad_interface_all ai,
505 IGS_PE_VISA_INT pi
506 WHERE
507 pi.INTERFACE_VISA_ID=pvhi.INTERFACE_VISA_ID AND
508 ai.interface_id=pi.interface_id AND
509 ai.status IN ('1','4') AND
510 ai.interface_run_id=l_interface_run_id);
511
512 -- gather statistics for the table after populating it's interface_run_id
513 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
514 tabname => 'IGS_PE_VST_HIST_INT',
515 cascade => TRUE);
516
517 UPDATE IGS_PE_VST_HIST_INT SET STATUS = '1'
518 WHERE MATCH_IND = '22' AND STATUS = '2'
519 AND INTERFACE_RUN_ID = l_interface_run_id;
520
521 -- Populating the child interface table with the interface_run_id value.
522 --skpandey, Bug#4114660: Changed table alias name to optimize performance
523 UPDATE IGS_PE_EIT_INT pei
524 SET
525 interface_run_id=l_interface_run_id
526 WHERE pei.status='2' AND
527 EXISTS (SELECT 1
528 FROM igs_ad_interface_all ai
529 WHERE
530 ai.interface_id=pei.interface_id AND
531 ai.status IN ('1','4') AND
532 ai.interface_run_id=l_interface_run_id);
533
534 -- gather statistics for the table after populating it's interface_run_id
535 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
536 tabname => 'IGS_PE_EIT_INT',
537 cascade => TRUE);
538 UPDATE IGS_PE_EIT_INT SET STATUS = '1'
539 WHERE MATCH_IND = '22' AND STATUS = '2'
540 AND INFORMATION_TYPE = 'PE_INT_PERM_RES'
541 AND INTERFACE_RUN_ID = l_interface_run_id;
542
543 UPDATE igs_pe_citizen_int pci
544 SET
545 interface_run_id=l_interface_run_id
546 WHERE pci.status='2' AND
547 EXISTS (SELECT 1
548 FROM igs_ad_interface_all ai
549 WHERE
550 ai.interface_id=pci.interface_id AND
551 ai.status IN ('1','4') AND
552 ai.interface_run_id=l_interface_run_id);
553
554 -- gather statistics for the table after populating it's interface_run_id
555 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
556 tabname => 'IGS_PE_CITIZEN_INT',
557 cascade => TRUE);
558
559 UPDATE igs_pe_citizen_int SET status = '1'
560 WHERE match_ind = '22' AND status = '2' AND
561 INTERFACE_RUN_ID = l_interface_run_id;
562
563 UPDATE igs_pe_fund_src_int pfsi
564 SET
565 interface_run_id=l_interface_run_id
566 WHERE pfsi.status='2' AND
567 EXISTS (SELECT 1
568 FROM igs_ad_interface_all ai
569 WHERE
570 ai.interface_id=pfsi.interface_id AND
571 ai.status IN ('1','4') AND
572 ai.interface_run_id=l_interface_run_id);
573
574 -- gather statistics for the table after populating it's interface_run_id
575 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
576 tabname => 'IGS_PE_FUND_SRC_INT',
577 cascade => TRUE);
578 UPDATE igs_pe_fund_src_int SET status = '1'
579 WHERE match_ind = '22' AND status = '2'
580 AND INTERFACE_RUN_ID = l_interface_run_id;
581
582 Igs_Ad_Imp_007.PRC_PE_INTL_DTLS(
583 P_SOURCE_TYPE_ID=>P_SOURCE_TYPE_ID,
584 P_BATCH_ID=>P_BATCH_ID );
585 END IF;
586
587 IF g_person_hlth_ins_inc THEN
588 IF l_enable_log = 'Y' THEN
589 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_HLTH_INS');
590 END IF;
591
592 -- Populating the child interface table with the interface_run_id value.
593 UPDATE igs_pe_immu_dtl_int pidi
594 SET
595 interface_run_id=l_interface_run_id
596 WHERE pidi.status='2' AND
597 EXISTS (SELECT 1
598 FROM igs_ad_interface_all ai
599 WHERE
600 ai.interface_id=pidi.interface_id AND
601 ai.status IN ('1','4') AND
602 ai.interface_run_id=l_interface_run_id);
603
604 -- gather statistics for the table after populating it's interface_run_id
605 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
606 tabname => 'IGS_PE_IMMU_DTL_INT',
607 cascade => TRUE);
608
609
610 UPDATE igs_pe_immu_dtl_int
611 SET status = '1'
612 WHERE match_ind = '22' AND
613 status = '2' AND
614 INTERFACE_RUN_ID = l_interface_run_id;
615
616 -- Populating the child interface table with the interface_run_id value.
617 UPDATE igs_ad_hlth_ins_int_all adhi
618 SET
619 interface_run_id=l_interface_run_id
620 WHERE adhi.status='2' AND
621 EXISTS (SELECT 1
622 FROM igs_ad_interface_all ai
623 WHERE
624 ai.interface_id=adhi.interface_id AND
625 ai.status IN ('1','4') AND
626 ai.interface_run_id=l_interface_run_id);
627
628 -- gather statistics for the table after populating it's interface_run_id
629 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
630 tabname => 'IGS_AD_HLTH_INS_INT_ALL',
631 cascade => TRUE);
632
633 UPDATE IGS_AD_HLTH_INS_INT_all SET STATUS = '1'
634 WHERE MATCH_IND = '22' AND STATUS = '2'
635 AND INTERFACE_RUN_ID = l_interface_run_id;
636
637 Igs_Ad_Imp_007.PRC_PE_HLTH_DTLS(
638 p_source_type_id=>p_source_type_id,
639 p_batch_id=>p_batch_id );
640 END IF;
641
642 IF g_person_mil_dtl_inc THEN
643 IF l_enable_log = 'Y' THEN
644 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_MIL');
645 END IF;
646
647 -- Populating the child interface table with the interface_run_id value.
648 UPDATE igs_ad_military_int_all admi
649 SET
650 interface_run_id=l_interface_run_id
651 WHERE admi.status='2' AND
652 EXISTS (SELECT 1
653 FROM igs_ad_interface_all ai
654 WHERE
655 ai.interface_id=admi.interface_id AND
656 ai.status IN ('1','4') AND
657 ai.interface_run_id=l_interface_run_id);
658
659 -- gather statistics for the table after populating it's interface_run_id
660 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
661 tabname => 'IGS_AD_MILITARY_INT_ALL',
662 cascade => TRUE);
663
664 UPDATE IGS_AD_MILITARY_INT_all SET STATUS = '1'
665 WHERE MATCH_IND = '22' AND STATUS = '2'
666 AND INTERFACE_RUN_ID = l_interface_run_id;
667
668 Igs_Ad_Imp_007.PRC_PE_MLTRY_DTLS(
669 p_source_type_id=>p_source_type_id,
670 p_batch_id=>p_batch_id );
671
672 END IF;
673
674 IF g_person_act_inc THEN
675 IF l_enable_log = 'Y' THEN
676 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_EXTR_CUR');
677 END IF;
678
679
680
681 -- Populating the child interface table with the interface_run_id value.
682 UPDATE igs_ad_excurr_int_all adei
683 SET
684 interface_run_id=l_interface_run_id
685 WHERE adei.status='2' AND
686 EXISTS (SELECT 1
687 FROM igs_ad_interface_all ai
688 WHERE
689 ai.interface_id=adei.interface_id AND
690 ai.status IN ('1','4') AND
691 ai.interface_run_id=l_interface_run_id);
692
693 -- gather statistics for the table after populating it's interface_run_id
694 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
695 tabname => 'IGS_AD_EXCURR_INT_ALL',
696 cascade => TRUE);
697
698 UPDATE IGS_AD_EXCURR_INT_all SET STATUS = '1'
699 WHERE MATCH_IND = '22' AND STATUS = '2'
700 AND INTERFACE_RUN_ID = l_interface_run_id;
701
702 Igs_Ad_Imp_006.PRC_PE_EXTCLR_DTLS(
703 p_source_type_id=>p_source_type_id,
704 p_batch_id=>p_batch_id );
705 END IF;
706
707 IF g_person_rel_inc THEN
708 IF l_enable_log = 'Y' THEN
709 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_REL');
710 END IF;
711
712 -- Populating the child interface table with the interface_run_id value.
713 UPDATE IGS_AD_RELATIONS_INT_ALL ari
714 SET interface_run_id=l_interface_run_id
715 WHERE ari.status='2' AND
716 EXISTS (SELECT 1
717 FROM igs_ad_interface_all ai
718 WHERE
719 ai.interface_id=ari.interface_id AND
720 ai.status IN ('1','4') AND
721 ai.interface_run_id=l_interface_run_id);
722
723 -- gather statistics for the table after populating it's interface_run_id
724 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
725 tabname => 'IGS_AD_RELATIONS_INT_ALL',
726 cascade => TRUE);
727 UPDATE IGS_AD_RELATIONS_INT_all iar
728 SET STATUS = '1'
729 WHERE MATCH_IND = '22' AND STATUS = '2'
730 AND INTERFACE_RUN_ID = l_interface_run_id;
731
732 IF g_rel_addr_inc THEN
733
734 UPDATE igs_ad_reladdr_int_all ari1
735 SET interface_run_id=l_interface_run_id
736 WHERE ari1.status='2' AND
737 EXISTS (SELECT 1
738 FROM igs_ad_interface_all ai,
739 IGS_AD_RELATIONS_INT_ALL adi
740 WHERE
741 adi.INTERFACE_RELATIONS_ID=ari1.INTERFACE_RELATIONS_ID AND
742 ai.interface_id=adi.interface_id AND
743 ai.status IN ('1','4') AND
744 ai.interface_run_id=l_interface_run_id);
745
746 -- gather statistics for the table after populating it's interface_run_id
747 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
748 tabname => 'IGS_AD_RELADDR_INT_ALL',
749 cascade => TRUE);
750 UPDATE IGS_AD_RELADDR_INT_all iara
751 SET STATUS = '1'
752 WHERE MATCH_IND = '22' AND STATUS = '2'
753 AND INTERFACE_RUN_ID = l_interface_run_id;
754
755 END IF;
756
757 IF g_rel_empl_dtl_inc THEN
758 UPDATE igs_ad_relemp_int_all ari2
759 SET interface_run_id=l_interface_run_id
760 WHERE ari2.status='2' AND
761 EXISTS (SELECT 1
762 FROM igs_ad_interface_all ai,
763 IGS_AD_RELATIONS_INT_ALL adi
764 WHERE adi.INTERFACE_RELATIONS_ID=ari2.INTERFACE_RELATIONS_ID AND
765 ai.interface_id=adi.interface_id AND
766 ai.status IN ('1','4') AND
767 ai.interface_run_id=l_interface_run_id);
768
769 -- gather statistics for the table after populating it's interface_run_id
770 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
771 tabname => 'IGS_AD_RELEMP_INT_ALL',
772 cascade => TRUE);
773 UPDATE IGS_AD_RELEMP_INT_all ire
774 SET STATUS = '1'
775 WHERE MATCH_IND = '22' AND STATUS = '2'
776 AND INTERFACE_RUN_ID = l_interface_run_id;
777
778 END IF;
779
780
781 IF g_rel_acad_hist_inc THEN
782
783 UPDATE Igs_Ad_Relacad_Int_all ari3
784 SET interface_run_id=l_interface_run_id
785 WHERE ari3.status='2' AND
786 EXISTS (SELECT 1
787 FROM igs_ad_interface_all ai,
788 IGS_AD_RELATIONS_INT_ALL adi
789 WHERE adi.INTERFACE_RELATIONS_ID=ari3.INTERFACE_RELATIONS_ID AND
790 ai.interface_id=adi.interface_id AND
791 ai.status IN ('1','4') AND
792 ai.interface_run_id=l_interface_run_id);
793
794 -- gather statistics for the table after populating it's interface_run_id
795 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
796 tabname => 'IGS_AD_RELACAD_INT_ALL',
797 cascade => TRUE);
798 UPDATE IGS_AD_RELACAD_INT_ALL iara
799 SET STATUS = '1'
800 WHERE MATCH_IND = '22' AND STATUS = '2'
801 AND INTERFACE_RUN_ID = l_interface_run_id;
802
803 END IF;
804
805
806 IF g_rel_contact_inc THEN
807 UPDATE igs_ad_rel_con_int_all ari4
808 SET
809 interface_run_id=l_interface_run_id
810 WHERE ari4.status='2' AND
811 EXISTS (SELECT 1
812 FROM igs_ad_interface_all ai,
813 IGS_AD_RELATIONS_INT_ALL adi
814 WHERE
815 adi.INTERFACE_RELATIONS_ID=ari4.INTERFACE_RELATIONS_ID AND
816 ai.interface_id=adi.interface_id AND
817 ai.status IN ('1','4') AND
818 ai.interface_run_id=l_interface_run_id);
819 -- gather statistics for the table after populating it's interface_run_id
820 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
821 tabname => 'IGS_AD_REL_CON_INT_ALL',
822 cascade => TRUE);
823 UPDATE IGS_AD_REL_CON_INT_all iarc
824 SET STATUS = '1'
825 WHERE MATCH_IND = '22' AND STATUS = '2'
826 AND INTERFACE_RUN_ID = l_interface_run_id;
827
828 END IF;
829
830 Igs_Ad_Imp_008.PRC_PE_RELNS(
831 p_source_type_id=>p_source_type_id,
832 p_batch_id=>p_batch_id );
833 END IF;
834
835
836 IF g_person_ath_inc THEN
837 IF l_enable_log = 'Y' THEN
838 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_ATHL');
839 END IF;
840
841 -- Populating the child interface table with the interface_run_id value.
842 UPDATE igs_pe_ath_dtl_int adli
843 SET
844 interface_run_id=l_interface_run_id
845 WHERE adli.status='2' AND
846 EXISTS (SELECT 1
847 FROM igs_ad_interface_all ai
848 WHERE
849 ai.interface_id=adli.interface_id AND
850 ai.status IN ('1','4') AND
851 ai.interface_run_id=l_interface_run_id);
852
853 -- gather statistics for the table after populating it's interface_run_id
854 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
855 tabname => 'IGS_PE_ATH_DTL_INT',
856 cascade => TRUE);
857
858 UPDATE igs_pe_ath_dtl_int
859 SET status = '1'
860 WHERE match_ind = '22' AND status = '2' AND
861 INTERFACE_RUN_ID = l_interface_run_id;
862
863 -- Populating the child interface table with the interface_run_id value.
864 UPDATE igs_pe_ath_prg_int adli
865 SET
866 interface_run_id=l_interface_run_id
867 WHERE adli.status='2' AND
868 EXISTS (SELECT 1
869 FROM igs_ad_interface_all ai
870 WHERE
871 ai.interface_id=adli.interface_id AND
872 ai.status IN ('1','4') AND
873 ai.interface_run_id=l_interface_run_id);
874
875 -- gather statistics for the table after populating it's interface_run_id
876 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
877 tabname => 'IGS_PE_ATH_PRG_INT',
878 cascade => TRUE);
879 UPDATE igs_pe_ath_prg_int
880 SET status = '1'
881 WHERE match_ind = '22' AND status = '2'
882 AND INTERFACE_RUN_ID = l_interface_run_id;
883
884 igs_ad_imp_012.prc_apcnt_ath(
885 p_source_type_id=>p_source_type_id,
886 p_batch_id=>p_batch_id );
887 END IF;
888
889 IF g_person_lang_inc THEN
890 IF l_enable_log = 'Y' THEN
891 igs_ad_imp_001.set_message(p_name => 'IGS_PE_BEG_PE_LAN');
892 END IF;
893
894 -- Populating the child interface table with the interface_run_id value.
895 UPDATE igs_ad_language_int_all adli
896 SET
897 interface_run_id=l_interface_run_id
898 WHERE adli.status='2' AND
899 EXISTS (SELECT 1
900 FROM igs_ad_interface_all ai
901 WHERE
902 ai.interface_id=adli.interface_id AND
903 ai.status IN ('1','4') AND
904 ai.interface_run_id=l_interface_run_id);
905
906 -- gather statistics for the table after populating it's interface_run_id
907 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
908 tabname => 'IGS_AD_LANGUAGE_INT_ALL',
909 cascade => TRUE);
910 UPDATE IGS_AD_LANGUAGE_INT_all il
911 SET STATUS = '1'
912 WHERE MATCH_IND = '22' AND STATUS = '2'
913 AND INTERFACE_RUN_ID = l_interface_run_id;
914
915 igs_ad_imp_012.prc_pe_language(
916 p_source_type_id=>p_source_type_id,
917 p_batch_id=>p_batch_id );
918 END IF;
919
920
921
922 IF g_person_contact_inc THEN
923 IF l_enable_log = 'Y' THEN
924 igs_ad_imp_001.set_message(p_name => 'IGS_PE_BEG_PE_CON');
925 END IF;
926
927 -- Populating the child interface table with the interface_run_id value.
928 UPDATE igs_ad_contacts_int_all adci
929 SET
930 interface_run_id=l_interface_run_id
931 WHERE adci.status='2' AND
932 EXISTS (SELECT 1
933 FROM igs_ad_interface_all ai
934 WHERE
935 ai.interface_id=adci.interface_id AND
936 ai.status IN ('1','4') AND
937 ai.interface_run_id=l_interface_run_id);
938
939 -- gather statistics for the table after populating it's interface_run_id
940 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
941 tabname => 'IGS_AD_CONTACTS_INT_ALL',
942 cascade => TRUE);
943 UPDATE IGS_AD_CONTACTS_INT_all ic
944 SET STATUS = '1'
945 WHERE MATCH_IND = '22' AND STATUS = '2'
946 AND INTERFACE_RUN_ID = l_interface_run_id;
947
948 igs_ad_imp_012.prc_pe_cntct_dtls(
949 p_source_type_id=>p_source_type_id,
950 p_batch_id=>p_batch_id );
951 END IF;
952
953
954 IF g_person_disc_dtls_inc THEN
955 IF l_enable_log = 'Y' THEN
956 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_DISCIPLINARY');
957 END IF;
958
959 -- Update interface tables for Felony and hearing details.
960
961 UPDATE igs_pe_flny_dtl_int pfi
962 SET
963 interface_run_id=l_interface_run_id
964 WHERE pfi.status='2' AND
965 EXISTS (SELECT 1
966 FROM igs_ad_interface_all ai
967 WHERE
968 ai.interface_id=pfi.interface_id AND
969 ai.status IN ('1','4') AND
970 ai.interface_run_id=l_interface_run_id);
971 -- gather statistics for the table after populating it's interface_run_id
972 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
973 tabname => 'IGS_PE_FLNY_DTL_INT',
974 cascade => TRUE);
975 UPDATE igs_pe_flny_dtl_int
976 SET status = '1'
977 WHERE match_ind = '22' AND status = '2'
978 AND INTERFACE_RUN_ID = l_interface_run_id;
979
980 UPDATE igs_pe_hear_dtl_int phi
981 SET
982 interface_run_id=l_interface_run_id
983 WHERE phi.status='2' AND
984 EXISTS (SELECT 1
985 FROM igs_ad_interface_all ai
986 WHERE
987 ai.interface_id=phi.interface_id AND
988 ai.status IN ('1','4') AND
989 ai.interface_run_id=l_interface_run_id);
990 -- gather statistics for the table after populating it's interface_run_id
991 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
992 tabname => 'IGS_PE_HEAR_DTL_INT',
993 cascade => TRUE);
994 UPDATE igs_pe_hear_dtl_int
995 SET status = '1'
996 WHERE match_ind = '22' AND status = '2'
997 AND INTERFACE_RUN_ID = l_interface_run_id;
998
999 Igs_Ad_Imp_025.prc_pe_disciplinary_dtls(
1000 p_source_type_id => p_source_type_id,
1001 p_batch_id => p_batch_id );
1002
1003 END IF;
1004
1005 IF g_person_housing_stat_inc THEN
1006 IF l_enable_log = 'Y' THEN
1007 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_PE_HOUSING');
1008 END IF;
1009
1010 -- Populating the child interface table with the interface_run_id value.
1011 UPDATE igs_pe_housing_int phi
1012 SET
1013 interface_run_id=l_interface_run_id
1014 WHERE phi.status='2' AND
1015 EXISTS (SELECT 1
1016 FROM igs_ad_interface_all ai
1017 WHERE
1018 ai.interface_id=phi.interface_id AND
1019 ai.status IN ('1','4') AND
1020 ai.interface_run_id=l_interface_run_id);
1021
1022 -- gather statistics for the table after populating it's interface_run_id
1023 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
1024 tabname => 'IGS_PE_HOUSING_INT',
1025 cascade => TRUE);
1026 UPDATE igs_pe_housing_int
1027 SET status = '1'
1028 WHERE match_ind = '22' AND status = '2'
1029 AND INTERFACE_RUN_ID = l_interface_run_id;
1030
1031 Igs_Ad_Imp_025.prc_pe_house_status(
1032 p_source_type_id => p_source_type_id,
1033 p_batch_id => p_batch_id );
1034
1035 END IF;
1036
1037 IF g_person_acad_honors_inc THEN
1038 IF l_enable_log = 'Y' THEN
1039 igs_ad_imp_001.set_message(p_name => 'IGS_AD_BEG_ACAD_HONORS');
1040 END IF;
1041
1042
1043 -- Populating the child interface table with the interface_run_id value.
1044 UPDATE igs_ad_acadhonor_int_all ahi
1045 SET
1046 interface_run_id=l_interface_run_id
1047 WHERE ahi.status='2' AND
1048 EXISTS (SELECT 1
1049 FROM igs_ad_interface_all ai
1050 WHERE
1051 ai.interface_id=ahi.interface_id AND
1052 ai.status IN ('1','4') AND
1053 ai.interface_run_id=l_interface_run_id);
1054
1055 -- gather statistics for the table after populating it's interface_run_id
1056 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
1057 tabname => 'IGS_AD_ACADHONOR_INT_ALL',
1058 cascade => TRUE);
1059 UPDATE IGS_AD_ACADHONOR_INT_all iah
1060 SET STATUS = '1'
1061 WHERE MATCH_IND = '22' AND STATUS = '2'
1062 AND INTERFACE_RUN_ID = l_interface_run_id;
1063
1064 Igs_Ad_Imp_011.prc_apcnt_acadhnr_dtls(
1065 p_source_type_id=>p_source_type_id,
1066 p_batch_id=>p_batch_id );
1067
1068 END IF;
1069
1070 IF g_person_res_dtl_inc THEN
1071 IF l_enable_log = 'Y' THEN
1072 igs_ad_imp_001.set_message(p_name => 'IGS_PE_BEG_RES_DTLS');
1073 END IF;
1074
1075
1076 -- Update interface tables for Felony and hearing details.
1077
1078 UPDATE igs_pe_res_dtls_int rdi
1079 SET
1080 interface_run_id=l_interface_run_id
1081 WHERE rdi.status='2' AND
1082 EXISTS (SELECT 1
1083 FROM igs_ad_interface_all ai
1084 WHERE
1085 ai.interface_id=rdi.interface_id AND
1086 ai.status IN ('1','4') AND
1087 ai.interface_run_id=l_interface_run_id);
1088
1089 -- gather statistics for the table after populating it's interface_run_id
1090 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
1091 tabname => 'IGS_PE_RES_DTLS_INT',
1092 cascade => TRUE);
1093 UPDATE igs_pe_res_dtls_int iah
1094 SET STATUS = '1'
1095 WHERE MATCH_IND = '22' AND STATUS = '2'
1096 AND INTERFACE_RUN_ID = l_interface_run_id;
1097
1098 Igs_Ad_Imp_011.prc_pe_res_dtls(
1099 p_source_type_id=>p_source_type_id,
1100 p_batch_id=>p_batch_id );
1101
1102 END IF;
1103
1104 IF g_person_creds_inc THEN
1105
1106 IF l_enable_log = 'Y' THEN
1107 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'PERSON_CREDENTIALS', 8405);
1108 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
1109 p_token_name => 'TYPE_NAME',
1110 p_token_value => l_meaning);
1111 END IF;
1112
1113 -- Populating the interface table with the interface_run_id value
1114 UPDATE igs_pe_Cred_int a
1115 SET interface_run_id = l_interface_run_id
1116 WHERE EXISTS (SELECT 1
1117 FROM igs_ad_interface_all
1118 WHERE interface_run_id = l_interface_run_id
1119 AND interface_id = a.interface_id
1120 AND status IN ('1','4'));
1121
1122 -- Gather statistics of the table
1123 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
1124 tabname => 'IGS_PE_CRED_INT',
1125 cascade => TRUE);
1126
1127 -- Call category entity import procedure
1128 igs_ad_imp_013.prc_pe_cred_details (p_interface_run_id => l_interface_run_id,
1129 p_enable_log => l_enable_log,
1130 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'PERSON_CREDENTIALS'));
1131
1132 END IF; -- g_person_creds_inc
1133
1134 IF g_acad_hist_inc THEN
1135 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'PERSON_ACADEMIC_HISTORY', 8405);
1136
1137 IF l_enable_log = 'Y' THEN
1138 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
1139 p_token_name => 'TYPE_NAME',
1140 p_token_value => l_meaning);
1141 END IF;
1142
1143 -- Populating the interface table with the interface_run_id value
1144 UPDATE igs_ad_acadhis_int_all a
1145 SET interface_run_id = l_interface_run_id,
1146 person_id = (SELECT person_id
1147 FROM igs_ad_interface_all
1148 WHERE interface_id = a.interface_id)
1149 WHERE EXISTS (SELECT 1
1150 FROM igs_ad_interface_all
1151 WHERE interface_run_id = l_interface_run_id
1152 AND interface_id = a.interface_id
1153 AND status IN ('1','4'));
1154
1155 -- Gather statistics of the table
1156 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
1157 tabname => 'IGS_AD_ACADHIS_INT_ALL',
1158 cascade => TRUE);
1159
1160 -- Call category entity import procedure
1161 igs_ad_imp_013.prc_pe_acad_hist (p_interface_run_id => l_interface_run_id,
1162 p_enable_log => l_enable_log,
1163 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'PERSON_ACADEMIC_HISTORY'));
1164
1165 END IF; -- g_person_creds_inc
1166
1167 IF g_privacy_dtl_inc THEN
1168
1169 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'PRIVACY_DETAILS', 8405);
1170
1171 IF l_enable_log = 'Y' THEN
1172 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',p_token_name => 'TYPE_NAME',p_token_value => l_meaning);
1173 END IF;
1174
1175 -- Populating the interface table with the interface_run_id value
1176 UPDATE igs_pe_privacy_int a
1177 SET interface_run_id = l_interface_run_id
1178 WHERE EXISTS (SELECT 1
1179 FROM igs_ad_interface_all
1180 WHERE interface_run_id = l_interface_run_id
1181 AND interface_id = a.interface_id
1182 AND status IN ('1','4'));
1183
1184 -- Gather statistics of the table
1185 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'igs_pe_privacy_int',cascade => TRUE);
1186
1187 UPDATE igs_pe_privacy_int iah
1188 SET STATUS = '1'
1189 WHERE MATCH_IND = '22' AND STATUS = '2'
1190 AND INTERFACE_RUN_ID = l_interface_run_id;
1191
1192 -- Call category entity import procedure
1193 igs_ad_imp_025.prc_priv_dtls (p_source_type_id=>p_source_type_id,p_batch_id=>p_batch_id );
1194
1195 END IF;
1196
1197 --Raise Bulk address process notification
1198 IGS_PE_WF_GEN. ADDR_BULK_SYNCHRONIZATION(IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS);
1199
1200 END prc_pe_category;
1201
1202
1203 PROCEDURE del_cmpld_pe_records(
1204 p_batch_id IN NUMBER
1205 )AS
1206 /*************************************************************
1207 Created By :pkpatel
1208 Date Created By :29-APR-2003
1209 Purpose : This will delete from all the person related tables as per the record status in the IGS_AD_INTERFACE table.
1210 The delete will happen only if the category for the table is included.
1211 Know limitations, enhancements or remarks
1212 Change History
1213 Who When What
1214 vrathi 08-Jul-2003 Bug:3038248 Delete record from igs_ad_addrusage_int before deleting from igs_ad_addr_int
1215 pkpatel 11-DEC-2003 Bug 2863933 (Removed the individual UPDATE of IGS_AD_INTERFACE_ALL and made it single UPDATE)
1216 Added 3 intermediate COMMIT statements.
1217 nsidana 6/21/2004 Bug 3533035 : First need to update the records in relations_int table to 4 in case any child did not process
1218 successfully. Then we need to delete from relations_int table, the records with status 1.
1219 Previously, the reverse was happening, so the record in parent relations_int table was getting deleted even though
1220 some child errored out.
1221 (reverse chronological order - newest change first)
1222 ***************************************************************/
1223 l_prog_label VARCHAR2(4000);
1224 l_label VARCHAR2(4000);
1225 l_debug_str VARCHAR2(4000);
1226 l_enable_log VARCHAR2(1);
1227 l_request_id NUMBER(10);
1228 l_interface_run_id IGS_AD_INTERFACE_CTL.interface_run_id%TYPE;
1229
1230 BEGIN
1231 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1232 l_enable_log := igs_ad_imp_001.g_enable_log;
1233 l_prog_label := 'igs.plsql.igs_pe_pers_imp_001.del_cmpld_pe_records';
1234 l_label := 'igs.plsql.igs_pe_pers_imp_001.del_cmpld_pe_records.';
1235
1236 -- Commit all the pending transactions
1237 COMMIT;
1238
1239 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1240
1241 IF (l_request_id IS NULL) THEN
1242 l_request_id := fnd_global.conc_request_id;
1243 END IF;
1244
1245 l_label := 'igs.plsql.igs_pe_pers_imp_001.del_cmpld_pe_records.begin';
1246 l_debug_str := 'Batch Id : ' || p_batch_id;
1247
1248 fnd_log.string_with_context( fnd_log.level_procedure,
1249 l_label,
1250 l_debug_str, NULL,
1251 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1252 END IF;
1253
1254 -- update record status of all the records in igs_AD_interface with current interface_run_id to '1'
1255 UPDATE igs_ad_interface_all
1256 SET record_status = '1'
1257 WHERE interface_run_id = l_interface_run_id;
1258
1259 -- call the procedure to store statistics in igs_ad_imp_stats
1260 pe_cat_stats('PERSON');
1261
1262 -- Delete from the tables for Statistics
1263 pe_cat_stats('PERSON_STATISTICS_STAT');
1264
1265 DELETE FROM igs_ad_stat_int_all
1266 WHERE status = '1' AND interface_run_id = l_interface_run_id;
1267
1268 -- Delete from the table IGS_AD_ADDRUSAGE_INT
1269 pe_cat_stats('PERSON_ADDRESS');
1270
1271 -- new logic to delete processed records from interface table
1272 DELETE FROM igs_ad_addrusage_int_all WHERE
1273 STATUS = '1' AND interface_run_id = l_interface_run_id;
1274
1275 DELETE FROM igs_ad_addr_int_all WHERE
1276 STATUS = '1' AND interface_run_id = l_interface_run_id;
1277
1278 -- call the procedure to store statistics in igs_ad_imp_stats
1279 pe_cat_stats('PERSON_ID_TYPES');
1280
1281 DELETE FROM igs_ad_api_int_all WHERE
1282 STATUS = '1' AND interface_run_id = l_interface_run_id;
1283
1284
1285 IF g_person_stat_inc THEN
1286 pe_cat_stats('PERSON_STATISTICS');
1287
1288 -- new logic to delete processed records from interface table
1289 DELETE FROM igs_pe_race_int WHERE
1290 STATUS = '1' AND interface_run_id = l_interface_run_id;
1291
1292 DELETE FROM igs_pe_eit_int WHERE
1293 STATUS = '1' AND interface_run_id = l_interface_run_id;
1294
1295 END IF;
1296
1297 IF g_person_type_inc THEN
1298 pe_cat_stats('PERSON_TYPE');
1299
1300 DELETE FROM igs_pe_type_int WHERE
1301 STATUS = '1' AND interface_run_id = l_interface_run_id;
1302
1303 END IF;
1304
1305 IF g_person_alias_inc THEN
1306 -- call the procedure to store statistics in igs_ad_imp_stats
1307 pe_cat_stats('PERSON_ALIAS');
1308
1309 DELETE FROM IGS_AD_ALIAS_INT_all WHERE
1310 STATUS = '1' AND interface_run_id = l_interface_run_id;
1311
1312 END IF;
1313
1314 IF g_person_spcl_need_inc THEN
1315 -- call the procedure to store statistics in igs_ad_imp_stats
1316 pe_cat_stats('PERSON_SPECIAL_NEEDS');
1317
1318 DELETE FROM igs_pe_sn_srvce_int WHERE
1319 STATUS = '1' AND interface_run_id = l_interface_run_id;
1320
1321 DELETE FROM igs_pe_sn_conct_int WHERE
1322 STATUS = '1' AND interface_run_id = l_interface_run_id;
1323
1324 DELETE FROM IGS_AD_DISABLTY_INT_all WHERE
1325 STATUS = '1' AND interface_run_id = l_interface_run_id;
1326
1327 END IF;
1328
1329 IF g_person_emp_dtl_inc THEN
1330 -- call the procedure to store statistics in igs_ad_imp_stats
1331 pe_cat_stats('PERSON_EMPLOYMENT_DETAILS');
1332
1333 DELETE FROM IGS_AD_EMP_INT_all WHERE
1334 STATUS = '1' AND interface_run_id = l_interface_run_id;
1335
1336 END IF;
1337
1338
1339 IF g_person_int_dtl_inc THEN
1340 -- call the procedure to store statistics in igs_ad_imp_stats
1341 pe_cat_stats('PERSON_INTERNATIONAL_DETAILS');
1342
1343 DELETE FROM IGS_PE_VST_HIST_INT WHERE
1344 STATUS = '1' AND interface_run_id = l_interface_run_id;
1345
1346 UPDATE igs_pe_visa_int ad
1347 SET status = '4', error_code = 'E347'
1348 WHERE ad.interface_run_id = l_interface_run_id AND
1349 ad.status = '1' AND
1350 EXISTS (SELECT 1 FROM igs_pe_vst_hist_int ai WHERE ad.interface_visa_id = ai.interface_visa_id);
1351
1352 -- DELETE FROM TABLE IGS_PE_VISA_INT
1353 DELETE FROM IGS_PE_VISA_INT WHERE
1354 STATUS = '1' AND interface_run_id = l_interface_run_id;
1355
1356 DELETE FROM IGS_PE_PASSPORT_INT WHERE
1357 STATUS = '1' AND interface_run_id = l_interface_run_id;
1358
1359 DELETE FROM igs_pe_citizen_int WHERE
1360 STATUS = '1' AND interface_run_id = l_interface_run_id;
1361
1362 DELETE FROM igs_pe_fund_src_int WHERE
1363 STATUS = '1' AND interface_run_id = l_interface_run_id;
1364
1365 DELETE FROM igs_pe_eit_int WHERE
1366 STATUS = '1' AND interface_run_id = l_interface_run_id;
1367
1368 END IF;
1369
1370
1371 IF g_person_hlth_ins_inc THEN
1372 -- call the procedure to store statistics in igs_ad_imp_stats
1373 pe_cat_stats('PERSON_HEALTH_INSURANCE');
1374
1375 -- Delete from the table IGS_AD_HLTH_INS_INT
1376 DELETE FROM IGS_AD_HLTH_INS_INT_all WHERE
1377 STATUS = '1' AND interface_run_id = l_interface_run_id;
1378
1379 -- Delete from the table igs_pe_immu_dtl_int
1380 DELETE FROM igs_pe_immu_dtl_int WHERE
1381 STATUS = '1' AND interface_run_id = l_interface_run_id;
1382
1383 END IF;
1384
1385 -- Intermediate commit for all the transactions till this point
1386 COMMIT;
1387
1388 -- Delete from the table IGS_AD_MILITARY_INT
1389 IF g_person_mil_dtl_inc THEN
1390
1391 pe_cat_stats('PERSON_MILITARY_DETAILS');
1392
1393 DELETE FROM IGS_AD_MILITARY_INT_all WHERE
1394 STATUS = '1' AND interface_run_id = l_interface_run_id;
1395
1396 END IF;
1397
1398 -- Delete from the table IGS_AD_EXCURR_INT
1399 IF g_person_act_inc THEN
1400
1401 pe_cat_stats('PERSON_ACTIVITIES');
1402
1403 DELETE FROM IGS_AD_EXCURR_INT_all WHERE
1404 STATUS = '1' AND interface_run_id = l_interface_run_id;
1405
1406 END IF;
1407
1408
1409 -- Delete from the table IGS_PE_RES_DTL_INT
1410 IF g_person_res_dtl_inc THEN
1411
1412 pe_cat_stats('PERSON_RESIDENCY_DETAILS');
1413
1414 DELETE FROM IGS_PE_RES_DTLS_INT WHERE
1415 STATUS = '1' AND interface_run_id = l_interface_run_id;
1416
1417 END IF;
1418
1419 -- Delete from the table IGS_AD_ACADHONOR_INT
1420 IF g_person_acad_honors_inc THEN
1421 -- call the procedure to store statistics in igs_ad_imp_stats
1422 pe_cat_stats('PERSON_ACAD_HONORS');
1423
1424 DELETE FROM IGS_AD_ACADHONOR_INT_all WHERE
1425 STATUS = '1' AND interface_run_id = l_interface_run_id;
1426
1427 END IF;
1428
1429 IF g_rel_empl_dtl_inc THEN
1430 -- call the procedure to store statistics in igs_ad_imp_stats
1431 pe_cat_stats('RELATIONS_EMPLOYMENT_DETAILS');
1432
1433 DELETE FROM IGS_AD_RELEMP_INT_all WHERE
1434 STATUS = '1' AND interface_run_id = l_interface_run_id;
1435
1436 END IF;
1437
1438 IF g_rel_contact_inc THEN
1439 -- call the procedure to store statistics in igs_ad_imp_stats
1440 pe_cat_stats('RELATIONS_CONTACTS');
1441
1442 DELETE FROM igs_ad_rel_con_int_all WHERE
1443 status = '1' AND interface_run_id = l_interface_run_id;
1444
1445 END IF;
1446
1447 -- Delete from the table IGS_AD_RELADDR_INT
1448 IF g_rel_addr_inc THEN
1449
1450 pe_cat_stats('RELATIONS_ADDRESS');
1451
1452 DELETE FROM igs_ad_reladdr_int_all WHERE
1453 status = '1' AND interface_run_id = l_interface_run_id;
1454
1455 END IF;
1456
1457 IF g_rel_acad_hist_inc THEN
1458 -- call the procedure to store statistics in igs_ad_imp_stats
1459 pe_cat_stats('RELATIONS_ACAD_HISTORY');
1460
1461 DELETE FROM IGS_AD_RELACAD_INT_all WHERE
1462 STATUS = '1' AND interface_run_id = l_interface_run_id;
1463
1464 END IF;
1465
1466
1467 IF g_person_rel_inc THEN
1468 -- call the procedure to store statistics in igs_ad_imp_stats
1469 pe_cat_stats('PERSON_RELATIONS');
1470
1471
1472 -- nsidana Bug 3533035 : First update the relations_int table to status 4 in case any child was not processed successfully. Then delete the records having status 1.
1473 UPDATE IGS_AD_RELATIONS_INT_all ad
1474 SET status = '4', error_code = 'E347'
1475 WHERE ad.interface_run_id = l_interface_run_id AND
1476 ad.status = '1' AND
1477 ( EXISTS (SELECT 1 FROM igs_ad_relemp_int_all ai WHERE ad.interface_relations_id = ai.interface_relations_id)
1478 OR EXISTS (SELECT 1 FROM igs_ad_rel_con_int_all ai WHERE ad.interface_relations_id = ai.interface_relations_id)
1479 OR EXISTS (SELECT 1 FROM IGS_AD_RELACAD_INT_all ai WHERE ad.interface_relations_ID = ai.interface_relations_ID)
1480 OR EXISTS (SELECT 1 FROM igs_ad_reladdr_int_all ai WHERE ad.interface_relations_ID = ai.interface_relations_ID )
1481 );
1482
1483 DELETE FROM IGS_AD_RELATIONS_INT_all WHERE
1484 STATUS = '1' AND interface_run_id = l_interface_run_id;
1485 -- end of delete logic
1486
1487 END IF;
1488
1489 IF g_person_ath_inc THEN
1490
1491 -- Delete from the table igs_pe_ath_dtl_int
1492 pe_cat_stats('PERSON_ATHLETICS');
1493
1494 DELETE FROM igs_pe_ath_dtl_int WHERE
1495 STATUS = '1' AND interface_run_id = l_interface_run_id;
1496
1497 -- Delete from the table igs_pe_ath_prg_int
1498 DELETE FROM igs_pe_ath_prg_int WHERE
1499 STATUS = '1' AND interface_run_id = l_interface_run_id;
1500
1501 END IF;
1502
1503
1504 IF g_person_housing_stat_inc THEN
1505 pe_cat_stats('PERSON_HOUSING_STATUS');
1506
1507 -- Delete from the table igs_pe_housing_int
1508 DELETE FROM igs_pe_housing_int WHERE
1509 STATUS = '1' AND interface_run_id = l_interface_run_id;
1510
1511 END IF;
1512
1513
1514 IF g_person_disc_dtls_inc THEN
1515 pe_cat_stats('PERSON_DISCIPLINARY_DTLS');
1516
1517 -- Delete from the table igs_pe_flny_dtl_int
1518 DELETE FROM igs_pe_flny_dtl_int WHERE
1519 STATUS = '1' AND interface_run_id = l_interface_run_id;
1520
1521 -- Delete from the table igs_pe_hear_dtl_int
1522 DELETE FROM igs_pe_hear_dtl_int WHERE
1523 STATUS = '1' AND interface_run_id = l_interface_run_id;
1524
1525 END IF;
1526
1527 IF g_person_contact_inc THEN
1528 pe_cat_stats('PERSON_CONTACTS');
1529 -- new logic to delete processed records from interface table
1530 DELETE FROM IGS_AD_CONTACTS_INT_all WHERE
1531 STATUS = '1' AND interface_run_id = l_interface_run_id;
1532
1533 END IF;
1534
1535 IF g_person_lang_inc THEN
1536 pe_cat_stats('PERSON_LANGUAGES');
1537 -- new logic to delete processed records from interface table
1538 DELETE FROM IGS_AD_LANGUAGE_INT_all WHERE
1539 STATUS = '1' AND interface_run_id = l_interface_run_id;
1540
1541 END IF;
1542
1543 IF g_person_creds_inc THEN
1544 pe_cat_stats('PERSON_CREDENTIALS');
1545
1546 DELETE FROM IGS_PE_CRED_INT WHERE
1547 STATUS = '1' AND interface_run_id = l_interface_run_id;
1548
1549 END IF;
1550
1551 IF g_acad_hist_inc THEN
1552 pe_cat_stats('PERSON_ACADEMIC_HISTORY');
1553
1554 DELETE FROM IGS_AD_ACADHIS_INT_ALL WHERE
1555 STATUS = '1' AND interface_run_id = l_interface_run_id;
1556
1557 END IF;
1558
1559 IF g_privacy_dtl_inc THEN
1560 pe_cat_stats('PRIVACY_DETAILS');
1561
1562 DELETE FROM igs_pe_privacy_int WHERE
1563 STATUS = '1' AND interface_run_id = l_interface_run_id;
1564
1565 END IF;
1566
1567
1568 UPDATE igs_ad_interface_all ad
1569 SET record_status = '3'
1570 WHERE ad.interface_run_id = l_interface_run_id AND
1571 ( EXISTS (SELECT 1 FROM igs_ad_interface_all ai WHERE ad.interface_id = ai.interface_id AND status = '3')
1572 OR EXISTS (SELECT 1 FROM igs_ad_stat_int_all ai WHERE ad.interface_id = ai.interface_id)
1573 OR EXISTS (SELECT 1 FROM igs_ad_addr_int_all ai WHERE ad.interface_id = ai.interface_id)
1574 OR EXISTS (SELECT 1 FROM igs_ad_api_int_all ai WHERE ad.interface_id = ai.interface_id)
1575 OR EXISTS (SELECT 1 FROM igs_pe_race_int ai WHERE ad.interface_id = ai.interface_id)
1576 OR EXISTS (SELECT 1 FROM igs_pe_eit_int ai WHERE ad.interface_id = ai.interface_id)
1577 OR EXISTS (SELECT 1 FROM igs_pe_type_int ai WHERE ad.interface_id = ai.interface_id)
1578 OR EXISTS (SELECT 1 FROM igs_ad_alias_int_all ai WHERE ad.interface_id = ai.interface_id)
1579 OR EXISTS (SELECT 1 FROM igs_ad_disablty_int_all ai WHERE ad.interface_id = ai.interface_id)
1580 OR EXISTS (SELECT 1 FROM igs_ad_emp_int_all ai WHERE ad.interface_id = ai.interface_id)
1581 OR EXISTS (SELECT 1 FROM igs_pe_visa_int ai WHERE ad.interface_id = ai.interface_id)
1582 OR EXISTS (SELECT 1 FROM igs_pe_passport_int ai WHERE ad.interface_id = ai.interface_id)
1583 OR EXISTS (SELECT 1 FROM igs_pe_citizen_int ai WHERE ad.interface_id = ai.interface_id)
1584 OR EXISTS (SELECT 1 FROM igs_pe_fund_src_int ai WHERE ad.interface_id = ai.interface_id)
1585 OR EXISTS (SELECT 1 FROM igs_ad_hlth_ins_int_all ai WHERE ad.interface_id = ai.interface_id)
1586 OR EXISTS (SELECT 1 FROM igs_pe_immu_dtl_int ai WHERE ad.interface_id = ai.interface_id)
1587 OR EXISTS (SELECT 1 FROM igs_ad_military_int_all ai WHERE ad.interface_id = ai.interface_id)
1588 OR EXISTS (SELECT 1 FROM igs_ad_excurr_int_all ai WHERE ad.interface_id = ai.interface_id)
1589 OR EXISTS (SELECT 1 FROM igs_pe_res_dtls_int ai WHERE ad.interface_id = ai.interface_id)
1590 OR EXISTS (SELECT 1 FROM igs_ad_acadhonor_int_all ai WHERE ad.interface_id = ai.interface_id)
1591 OR EXISTS (SELECT 1 FROM igs_ad_relations_int_all ai WHERE ad.interface_id = ai.interface_id)
1592 OR EXISTS (SELECT 1 FROM igs_pe_ath_dtl_int ai WHERE ad.interface_id = ai.interface_id)
1593 OR EXISTS (SELECT 1 FROM igs_pe_ath_prg_int ai WHERE ad.interface_id = ai.interface_id)
1594 OR EXISTS (SELECT 1 FROM igs_pe_housing_int ai WHERE ad.interface_id = ai.interface_id)
1595 OR EXISTS (SELECT 1 FROM igs_pe_flny_dtl_int ai WHERE ad.interface_id = ai.interface_id)
1596 OR EXISTS (SELECT 1 FROM igs_pe_hear_dtl_int ai WHERE ad.interface_id = ai.interface_id)
1597 OR EXISTS (SELECT 1 FROM igs_ad_contacts_int_all ai WHERE ad.interface_id = ai.interface_id)
1598 OR EXISTS (SELECT 1 FROM igs_ad_language_int_all ai WHERE ad.interface_id = ai.interface_id)
1599 OR EXISTS (SELECT 1 FROM igs_pe_cred_int ai WHERE ad.interface_id = ai.interface_id)
1600 OR EXISTS (SELECT 1 FROM igs_ad_acadhis_int_all ai WHERE ad.interface_id = ai.interface_id)
1601 OR EXISTS (SELECT 1 FROM igs_pe_privacy_int ai WHERE ad.interface_id = ai.interface_id));
1602
1603 -- Commit all the transactions
1604 COMMIT;
1605
1606 END del_cmpld_pe_records;
1607
1608
1609
1610 PROCEDURE set_stat_matc_rvw_pers_rcds (
1611 p_source_type_id IN NUMBER,
1612 p_batch_id IN NUMBER
1613 )
1614 AS
1615 /*************************************************************
1616 Created By :pkpatel
1617 Date Created By :29-APR-2003
1618 Purpose : This procedure gets called at the beginning of import process.
1619 The package variables are initialized here as per the categories included or not and then
1620 used further.
1621 Here also the pending records with match_ind 22 are updated to status 1, and this happens as per the
1622 category is included or not.
1623 Know limitations, enhancements or remarks
1624 Change History
1625 Who When What
1626 (reverse chronological order - newest change first)
1627 ***************************************************************/
1628 l_prog_label VARCHAR2(4000);
1629 l_label VARCHAR2(4000);
1630 l_debug_str VARCHAR2(4000);
1631 l_enable_log VARCHAR2(1);
1632 l_request_id NUMBER(10);
1633
1634 BEGIN
1635 /* initialise variables to DEFAULT value FALSE*/
1636 g_person_type_inc := FALSE;
1637 g_person_stat_inc := FALSE;
1638 g_person_addr_inc := FALSE;
1639 g_person_alias_inc := FALSE;
1640 g_person_id_types_inc := FALSE;
1641 g_person_spcl_need_inc := FALSE;
1642 g_person_emp_dtl_inc := FALSE;
1643 g_person_int_dtl_inc := FALSE;
1644 g_person_hlth_ins_inc := FALSE;
1645 g_person_mil_dtl_inc := FALSE;
1646 g_person_act_inc := FALSE;
1647 g_person_rel_inc := FALSE;
1648 g_person_ath_inc := FALSE;
1649 g_person_lang_inc := FALSE;
1650 g_person_contact_inc := FALSE;
1651 g_person_disc_dtls_inc := FALSE;
1652 g_person_housing_stat_inc := FALSE;
1653 g_person_acad_honors_inc := FALSE;
1654 g_person_res_dtl_inc := FALSE;
1655 g_rel_acad_hist_inc := FALSE;
1656 g_rel_addr_inc := FALSE;
1657 g_rel_contact_inc := FALSE;
1658 g_rel_empl_dtl_inc := FALSE;
1659 g_person_creds_inc := FALSE;
1660 g_acad_hist_inc := FALSE;
1661 g_privacy_dtl_inc := FALSE;
1662
1663 l_enable_log := igs_ad_imp_001.g_enable_log;
1664 l_prog_label := 'igs.plsql.igs_pe_pers_imp_001.set_stat_matc_rvw_pers_rcds';
1665 l_label := 'igs.plsql.igs_pe_pers_imp_001.set_stat_matc_rvw_pers_rcds.';
1666
1667 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1668
1669 IF (l_request_id IS NULL) THEN
1670 l_request_id := fnd_global.conc_request_id;
1671 END IF;
1672
1673 l_label := 'igs.plsql.igs_pe_pers_imp_001.set_stat_matc_rvw_pers_rcds.begin';
1674 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID :' || p_batch_id;
1675
1676 fnd_log.string_with_context( fnd_log.level_procedure,
1677 l_label,
1678 l_debug_str, NULL,
1679 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1680 END IF;
1681
1682
1683 g_person_type_inc := igs_ad_gen_016.chk_src_cat( p_source_type_id, 'PERSON_TYPE');
1684 g_person_stat_inc := igs_ad_gen_016.chk_src_cat( p_source_type_id, 'PERSON_STATISTICS');
1685 g_person_addr_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ADDRESS');
1686 g_person_alias_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ALIAS');
1687 g_person_id_types_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ID_TYPES');
1688 g_person_spcl_need_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_SPECIAL_NEEDS');
1689 g_person_emp_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_EMPLOYMENT_DETAILS');
1690 g_person_int_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_INTERNATIONAL_DETAILS');
1691 g_person_hlth_ins_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_HEALTH_INSURANCE');
1692 g_person_mil_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_MILITARY_DETAILS');
1693 g_person_act_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ACTIVITIES');
1694 g_person_rel_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_RELATIONS');
1695 g_person_ath_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ATHLETICS' );
1696 g_person_lang_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_LANGUAGES');
1697 g_person_contact_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_CONTACTS');
1698 g_person_disc_dtls_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_DISCIPLINARY_DTLS');
1699 g_person_housing_stat_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_HOUSING_STATUS');
1700 g_person_acad_honors_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_ACAD_HONORS');
1701 g_person_res_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PERSON_RESIDENCY_DETAILS');
1702 g_rel_acad_hist_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'RELATIONS_ACAD_HISTORY');
1703 g_rel_addr_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'RELATIONS_ADDRESS');
1704 g_rel_contact_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'RELATIONS_CONTACTS');
1705 g_rel_empl_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'RELATIONS_EMPLOYMENT_DETAILS');
1706 g_privacy_dtl_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id, 'PRIVACY_DETAILS');
1707
1708 --Intialization of variables are added as part of Admissions Import process Enhancements Bug #3191401
1709 g_person_creds_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id,'PERSON_CREDENTIALS');
1710 g_acad_hist_inc := igs_ad_gen_016.chk_src_cat(p_source_type_id,'PERSON_ACADEMIC_HISTORY');
1711
1712
1713 END set_stat_matc_rvw_pers_rcds;
1714
1715 PROCEDURE prc_pe_imp_record_sts(
1716 p_interface_id IN igs_ad_interface_all.interface_id%TYPE
1717 )
1718 AS
1719 /*************************************************************
1720 Created By :pkpatel
1721 Date Created By :29-APR-2003
1722 Purpose : This procedure puts the logic for all the person categories to finally update the record status
1723 of the IGS_AD_INTERFACE table. Record Status '1' success and '3' failure.
1724 Know limitations, enhancements or remarks
1725 Change History
1726 Who When What
1727 (reverse chronological order - newest change first)
1728 ***************************************************************/
1729 -- asbala 15-10-2003 procedure stubbed. the delete logic is implemented differently now. See details
1730 -- in SWS: Import Process Enhancements Build
1731 BEGIN
1732 NULL;
1733 END prc_pe_imp_record_sts;
1734
1735 --< nsidana 9/23/2003 Admissions Import process enhancements : Lookups caching >
1736
1737 FUNCTION validate_lookup_type_code(p_lookup_type IN fnd_lookup_values.lookup_type%TYPE,
1738 p_lookup_code IN fnd_lookup_values.lookup_type%TYPE,
1739 p_application_id IN NUMBER)
1740 RETURN BOOLEAN IS
1741 /*****************************************************************
1742 Created By : nsidana
1743
1744 Creation date : 9/23/2003
1745
1746 Purpose : This function is to validate the lookup type and lookup
1747 code combination. It checks if the lookup type and lookup code combination
1748 is a valid one. It uses PL/SQL table to evaluate this.
1749
1750 Know limitations, enhancements or remarks
1751
1752 Change History
1753 Who When What
1754
1755 (reverse chronological order - newest change first)
1756 ***************************************************************/
1757
1758 -- Cursor to fetch all the lookup codes associated with a lookup type.
1759 -- Will be used to cache the lookups.
1760
1761 CURSOR c_fetch_lkups(cp_lkup_type VARCHAR2,cp_application_id fnd_lookup_values.view_application_id%TYPE,
1762 cp_security_group_id fnd_lookup_values.security_group_id%TYPE)
1763 IS
1764 SELECT lookup_type,lookup_code
1765 FROM fnd_lookup_values
1766 WHERE lookup_type = cp_lkup_type AND
1767 view_application_id = cp_application_id AND
1768 security_group_id = cp_security_group_id AND
1769 language = userenv('LANG') AND
1770 enabled_flag = 'Y';
1771
1772 CURSOR c_validate_lkup_code(cp_lkup_type VARCHAR2, cp_lkup_code VARCHAR2, cp_application_id fnd_lookup_values.view_application_id%TYPE,
1773 cp_security_group_id fnd_lookup_values.security_group_id%TYPE)
1774 IS
1775 SELECT 'X'
1776 FROM fnd_lookup_values
1777 WHERE lookup_type = cp_lkup_type AND
1778 lookup_code = cp_lkup_code AND
1779 view_application_id = cp_application_id AND
1780 security_group_id = cp_security_group_id AND
1781 language = userenv('LANG') AND
1782 enabled_flag = 'Y';
1783
1784 l_rec c_fetch_lkups%ROWTYPE;
1785 l_hash_code NUMBER;
1786 l_var VARCHAR2(1);
1787 l_var2 VARCHAR2(80);
1788
1789 BEGIN
1790
1791 IF ((p_lookup_type IS NOT NULL) AND (p_lookup_code IS NOT NULL) AND (p_application_id IS NOT NULL))
1792 THEN
1793
1794 -- all parameters passed. Proceed further...
1795
1796 l_hash_code := DBMS_UTILITY.GET_HASH_VALUE(p_lookup_type||'@*?'||p_lookup_code||'@*?'||p_application_id,1000,25000);
1797
1798 IF l_lookups_tab.EXISTS(l_hash_code)
1799 THEN
1800 RETURN(TRUE);
1801 ELSE
1802 -- check if the lookup type was cached or not.
1803
1804 l_hash_code := DBMS_UTILITY.GET_HASH_VALUE(p_lookup_type||'@*?'||p_application_id,1000,25000);
1805
1806 IF l_lookup_type_tab.EXISTS(l_hash_code)
1807 THEN
1808 -- Lookup type was cached, but the lookup code passed to the function is not associated with it. The combination is invalid.
1809 RETURN(FALSE);
1810 ELSE
1811 -- No cache hit. Validate the lookup type and code and cache it.
1812
1813 OPEN c_validate_lkup_code(p_lookup_type,p_lookup_code,p_application_id,0);
1814 FETCH c_validate_lkup_code INTO l_var;
1815 CLOSE c_validate_lkup_code;
1816
1817 IF (l_var = 'X') THEN
1818 -- cache the lookup type and the lookup codes.
1819
1820 l_hash_code:=DBMS_UTILITY.GET_HASH_VALUE(p_lookup_type||'@*?'||p_application_id,1000,25000);
1821
1822 l_lookup_type_tab(l_hash_code):=p_lookup_type;
1823
1824 -- cache the lookup codes for this type also.
1825
1826 OPEN c_fetch_lkups(p_lookup_type,p_application_id,0);
1827 LOOP
1828 FETCH c_fetch_lkups INTO l_rec;
1829 EXIT WHEN c_fetch_lkups%NOTFOUND;
1830
1831 l_var2:=NULL;
1832 l_var2:=l_rec.lookup_type||'@*?'||l_rec.lookup_code;
1833
1834 l_hash_code:=DBMS_UTILITY.GET_HASH_VALUE(l_rec.lookup_type||'@*?'||l_rec.lookup_code||'@*?'||p_application_id,1000,25000);
1835 l_lookups_tab(l_hash_code):=l_var2;
1836
1837 END LOOP;
1838 CLOSE c_fetch_lkups;
1839
1840 -- Lookups cached. Return TRUE.
1841
1842 RETURN(TRUE);
1843 ELSE
1844 -- Lookup type and code combination is not valid. Return FALSE.
1845 RETURN(FALSE);
1846 END IF;
1847 END IF;
1848 END IF;
1849 ELSE
1850 RETURN(FALSE); -- all parameters not passed.
1851 END IF;
1852
1853 END validate_lookup_type_code;
1854
1855
1856 PROCEDURE pe_cat_stats(p_source_category IN VARCHAR2) AS
1857 /*****************************************************************
1858 Created By : asbala
1859
1860 Creation date : 9/23/2003
1861
1862 Purpose : This function is to insert the statistics into igs_ad_imp_stats.
1863
1864 Know limitations, enhancements or remarks
1865
1866 Change History
1867 Who When What
1868 pkpatel 27-Mar-2006 Bug 5114924(Defined variables l_success .. as NUMBER instead of NUMBER(5))
1869 skpandey 25-JAN-2006 Bug#4114660: Used local variable in place of Literals to optimize performance
1870 pkpatel 11-DEC-2003 Bug 2863933 (Added the logic to populate for Credential and Academic History.
1871 Used local variables to populate WHO columns)
1872 (reverse chronological order - newest change first)
1873 ***************************************************************/
1874
1875 CURSOR cur_person_type (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1876 SELECT count(*) count1,status
1877 FROM IGS_PE_TYPE_INT
1878 WHERE interface_run_id = p_interface_run_id
1879 GROUP BY status;
1880
1881 CURSOR cur_person_stat_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1882 SELECT count(*) count1,status FROM IGS_AD_STAT_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1883
1884 CURSOR cur_person_stat_eit_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1885 SELECT count(*) count1,status FROM IGS_PE_EIT_INT WHERE interface_run_id = p_interface_run_id AND
1886 information_type IN ('PE_STAT_RES_COUNTRY','PE_STAT_RES_STATE', 'PE_STAT_RES_STATUS') GROUP BY status;
1887
1888 CURSOR cur_person_race_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1889 SELECT count(*) count1,status FROM IGS_PE_RACE_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1890
1891 CURSOR cur_person_addr_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1892 SELECT count(*) count1,status FROM IGS_AD_ADDR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1893
1894 CURSOR cur_person_addrusage_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1895 SELECT count(*) count1, status FROM IGS_AD_ADDRUSAGE_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1896
1897 CURSOR cur_person_alias_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1898 SELECT count(*) count1,status FROM IGS_AD_ALIAS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1899
1900 CURSOR cur_person_id_types_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1901 SELECT count(*) count1,status FROM IGS_AD_API_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1902
1903 CURSOR cur_person_spcl_need_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1904 SELECT count(*) count1,status FROM IGS_AD_DISABLTY_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1905
1906 CURSOR cur_person_srvc_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1907 SELECT count(*) count1,status FROM IGS_PE_SN_SRVCE_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1908
1909 CURSOR cur_person_conc_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1910 SELECT count(*) count1,status FROM IGS_PE_SN_CONCT_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1911
1912 CURSOR cur_person_emp_dtl_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1913 SELECT count(*) count1,status FROM IGS_AD_EMP_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1914
1915 CURSOR cur_person_visa_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1916 SELECT count(*) count1,status FROM IGS_PE_VISA_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1917
1918 CURSOR cur_person_passport_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1919 SELECT count(*) count1,status FROM IGS_PE_PASSPORT_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1920
1921 CURSOR cur_person_hist_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1922 SELECT count(*) count1,status FROM IGS_PE_VST_HIST_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1923
1924 CURSOR cur_person_eit_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE,p_information_type VARCHAR2) IS
1925 SELECT count(*) count1,status FROM IGS_PE_EIT_INT WHERE interface_run_id = p_interface_run_id AND
1926 information_type = p_information_type GROUP BY status;
1927
1928 CURSOR cur_person_citizen_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1929 SELECT count(*) count1,status FROM IGS_PE_CITIZEN_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1930
1931 CURSOR cur_person_fund_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1932 SELECT count(*) count1,status FROM IGS_PE_FUND_SRC_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1933
1934 CURSOR cur_person_immu_dtl_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1935 SELECT count(*) count1,status FROM IGS_PE_IMMU_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1936
1937 CURSOR cur_person_health_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1938 SELECT count(*) count1,status FROM IGS_AD_HLTH_INS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1939
1940 CURSOR cur_person_mil_dtl_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1941 SELECT count(*) count1,status FROM IGS_AD_MILITARY_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1942
1943 CURSOR cur_person_act_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1944 SELECT count(*) count1,status FROM IGS_AD_EXCURR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1945
1946 CURSOR cur_person_rel_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1947 SELECT count(*) count1,status FROM IGS_AD_RELATIONS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1948
1949 CURSOR cur_person_ath_dtl_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1950 SELECT count(*) count1,status FROM IGS_PE_ATH_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1951
1952 CURSOR cur_person_ath_prg_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1953 SELECT count(*) count1,status FROM IGS_PE_ATH_PRG_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1954
1955 CURSOR cur_person_lang_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1956 SELECT count(*) count1,status FROM IGS_AD_LANGUAGE_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1957
1958 CURSOR cur_person_contact_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1959 SELECT count(*) count1,status FROM IGS_AD_CONTACTS_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1960
1961 CURSOR cur_person_flny_dtls_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1962 SELECT count(*) count1,status FROM IGS_PE_FLNY_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1963
1964 CURSOR cur_person_hear_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1965 SELECT count(*) count1,status FROM IGS_PE_HEAR_DTL_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1966
1967 CURSOR cur_person_housing_stat_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1968 SELECT count(*) count1,status FROM IGS_PE_HOUSING_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1969
1970 CURSOR cur_person_acad_honors_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1971 SELECT count(*) count1,status FROM IGS_AD_ACADHONOR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1972
1973 CURSOR cur_person_res_dtl_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1974 SELECT count(*) count1,status FROM IGS_PE_RES_DTLS_INT WHERE interface_run_id = p_interface_run_id GROUP BY status;
1975
1976 CURSOR cur_relacad_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1977 SELECT count(*) count1,status FROM IGS_AD_RELACAD_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1978
1979 CURSOR cur_rel_addr_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1980 SELECT count(*) count1,status FROM IGS_AD_RELADDR_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1981
1982 CURSOR cur_relcon_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1983 SELECT count(*) count1,status FROM IGS_AD_REL_CON_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1984
1985 CURSOR cur_relemp_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1986 SELECT count(*) count1,status FROM IGS_AD_RELEMP_INT_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1987
1988 CURSOR cur_ad_interface_all (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1989 SELECT count(*) count1,status FROM IGS_AD_INTERFACE_ALL WHERE interface_run_id = p_interface_run_id GROUP BY status;
1990
1991 CURSOR cur_cred_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1992 SELECT count(*) count1,status FROM igs_pe_cred_int WHERE interface_run_id = p_interface_run_id GROUP BY status;
1993
1994 CURSOR cur_acadhis_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1995 SELECT count(*) count1,status FROM igs_ad_acadhis_int_all WHERE interface_run_id = p_interface_run_id GROUP BY status;
1996
1997 CURSOR cur_privacy_int (p_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE) IS
1998 SELECT count(*) count1,status FROM igs_pe_privacy_int WHERE interface_run_id = p_interface_run_id GROUP BY status;
1999
2000
2001 l_interface_run_id igs_ad_imp_001.g_interface_run_id%TYPE;
2002 l_success NUMBER;
2003 l_error NUMBER;
2004 l_warning NUMBER;
2005 l_total_rec NUMBER;
2006 l_sysdate DATE;
2007 l_user_id NUMBER;
2008 l_tab VARCHAR2(30);
2009 l_source_category VARCHAR2(30);
2010 BEGIN
2011 l_sysdate := SYSDATE;
2012 l_user_id := fnd_global.user_id;
2013 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
2014 l_success := 0;
2015 l_error := 0;
2016 l_warning := 0;
2017 l_total_rec := 0;
2018
2019 IF (p_source_category = 'PERSON') THEN
2020 FOR rec_ad_interface_all IN cur_ad_interface_all(l_interface_run_id)
2021 LOOP
2022 IF rec_ad_interface_all.status = '1' THEN
2023 l_success := rec_ad_interface_all.count1;
2024 ELSIF rec_ad_interface_all.status = '3' THEN
2025 l_error := rec_ad_interface_all.count1;
2026 ELSIF rec_ad_interface_all.status = '4' THEN
2027 l_warning := rec_ad_interface_all.count1;
2028 END IF;
2029 END LOOP;
2030
2031 IF l_success IS NULL THEN
2032 l_success := 0;
2033 END IF;
2034 IF l_error IS NULL THEN
2035 l_error := 0;
2036 END IF;
2037 IF l_warning IS NULL THEN
2038 l_warning := 0;
2039 END IF;
2040
2041 l_total_rec := l_success + l_error + l_warning;
2042 l_tab := 'IGS_AD_INTERFACE_ALL';
2043 INSERT INTO IGS_AD_IMP_STATS
2044 (
2045 INTERFACE_RUN_ID,
2046 SRC_CAT_CODE,
2047 ENTITY_NAME,
2048 TOTAL_REC_NUM,
2049 TOTAL_WARN_NUM,
2050 TOTAL_SUCCESS_NUM,
2051 TOTAL_ERROR_NUM,
2052 CREATED_BY,
2053 CREATION_DATE,
2054 LAST_UPDATED_BY,
2055 LAST_UPDATE_DATE
2056 )
2057 VALUES(
2058 l_interface_run_id,
2059 p_source_category,
2060 l_tab,
2061 l_total_rec,
2062 l_warning,
2063 l_success,
2064 l_error,
2065 l_user_id,
2066 l_sysdate,
2067 l_user_id,
2068 l_sysdate
2069 );
2070 END IF;
2071
2072 IF (p_source_category = 'PERSON_TYPE') THEN
2073
2074 FOR rec_person_type IN cur_person_type(l_interface_run_id)
2075 LOOP
2076 IF rec_person_type.status = '1' THEN
2077 l_success := rec_person_type.count1;
2078 ELSIF rec_person_type.status = '3' THEN
2079 l_error := rec_person_type.count1;
2080 ELSIF rec_person_type.status = '4' THEN
2081 l_warning := rec_person_type.count1;
2082 END IF;
2083 END LOOP;
2084
2085 IF l_success IS NULL THEN
2086 l_success := 0;
2087 END IF;
2088 IF l_error IS NULL THEN
2089 l_error := 0;
2090 END IF;
2091 IF l_warning IS NULL THEN
2092 l_warning := 0;
2093 END IF;
2094 l_total_rec := l_success + l_error + l_warning;
2095 l_tab := 'IGS_PE_TYPE_INT';
2096 INSERT INTO IGS_AD_IMP_STATS
2097 (
2098 INTERFACE_RUN_ID,
2099 SRC_CAT_CODE,
2100 ENTITY_NAME,
2101 TOTAL_REC_NUM,
2102 TOTAL_WARN_NUM,
2103 TOTAL_SUCCESS_NUM,
2104 TOTAL_ERROR_NUM,
2105 CREATED_BY,
2106 CREATION_DATE,
2107 LAST_UPDATED_BY,
2108 LAST_UPDATE_DATE
2109 )
2110 VALUES(
2111 l_interface_run_id,
2112 p_source_category,
2113 l_tab,
2114 l_total_rec,
2115 l_warning,
2116 l_success,
2117 l_error,
2118 l_user_id,
2119 l_sysdate,
2120 l_user_id,
2121 l_sysdate
2122 );
2123 END IF;
2124
2125 IF (p_source_category = 'PERSON_STATISTICS_STAT') THEN
2126 FOR rec_person_stat_int IN cur_person_stat_int(l_interface_run_id)
2127 LOOP
2128 IF rec_person_stat_int.status = '1' THEN
2129 l_success := rec_person_stat_int.count1;
2130 ELSIF rec_person_stat_int.status = '3' THEN
2131 l_error := rec_person_stat_int.count1;
2132 ELSIF rec_person_stat_int.status = '4' THEN
2133 l_warning := rec_person_stat_int.count1;
2134 END IF;
2135 END LOOP;
2136
2137 IF l_success IS NULL THEN
2138 l_success := 0;
2139 END IF;
2140 IF l_error IS NULL THEN
2141 l_error := 0;
2142 END IF;
2143 IF l_warning IS NULL THEN
2144 l_warning := 0;
2145 END IF;
2146
2147 l_total_rec := l_success + l_error + l_warning;
2148 l_tab := 'IGS_AD_STAT_INT_ALL';
2149 l_source_category := 'PERSON_STATISTICS';
2150 INSERT INTO IGS_AD_IMP_STATS
2151 (
2152 INTERFACE_RUN_ID,
2153 SRC_CAT_CODE,
2154 ENTITY_NAME,
2155 TOTAL_REC_NUM,
2156 TOTAL_WARN_NUM,
2157 TOTAL_SUCCESS_NUM,
2158 TOTAL_ERROR_NUM,
2159 CREATED_BY,
2160 CREATION_DATE,
2161 LAST_UPDATED_BY,
2162 LAST_UPDATE_DATE
2163 )
2164 VALUES(
2165 l_interface_run_id,
2166 l_source_category,
2167 l_tab,
2168 l_total_rec,
2169 l_warning,
2170 l_success,
2171 l_error,
2172 l_user_id,
2173 l_sysdate,
2174 l_user_id,
2175 sysdate
2176 );
2177
2178 END IF;
2179
2180 IF (p_source_category = 'PERSON_STATISTICS') THEN
2181
2182 FOR rec_person_stat_eit_int IN cur_person_stat_eit_int(l_interface_run_id)
2183 LOOP
2184 IF rec_person_stat_eit_int.status = '1' THEN
2185 l_success := rec_person_stat_eit_int.count1;
2186 ELSIF rec_person_stat_eit_int.status = '3' THEN
2187 l_error := rec_person_stat_eit_int.count1;
2188 ELSIF rec_person_stat_eit_int.status = '4' THEN
2189 l_warning := rec_person_stat_eit_int.count1;
2190 END IF;
2191 END LOOP;
2192
2193 IF l_success IS NULL THEN
2194 l_success := 0;
2195 END IF;
2196 IF l_error IS NULL THEN
2197 l_error := 0;
2198 END IF;
2199 IF l_warning IS NULL THEN
2200 l_warning := 0;
2201 END IF;
2202 l_total_rec := l_success + l_error + l_warning;
2203 l_tab := 'IGS_PE_EIT_INT-STAT';
2204 INSERT INTO IGS_AD_IMP_STATS
2205 (
2206 INTERFACE_RUN_ID,
2207 SRC_CAT_CODE,
2208 ENTITY_NAME,
2209 TOTAL_REC_NUM,
2210 TOTAL_WARN_NUM,
2211 TOTAL_SUCCESS_NUM,
2212 TOTAL_ERROR_NUM,
2213 CREATED_BY,
2214 CREATION_DATE,
2215 LAST_UPDATED_BY,
2216 LAST_UPDATE_DATE
2217 )
2218 VALUES(
2219 l_interface_run_id,
2220 p_source_category,
2221 l_tab,
2222 l_total_rec,
2223 l_warning,
2224 l_success,
2225 l_error,
2226 l_user_id,
2227 l_sysdate,
2228 l_user_id,
2229 l_sysdate
2230 );
2231
2232 l_success := 0;
2233 l_error := 0;
2234 l_warning := 0;
2235 l_total_rec := 0;
2236
2237 FOR rec_person_race_int IN cur_person_race_int(l_interface_run_id)
2238 LOOP
2239 IF rec_person_race_int.status = '1' THEN
2240 l_success := rec_person_race_int.count1;
2241 ELSIF rec_person_race_int.status = '3' THEN
2242 l_error := rec_person_race_int.count1;
2243 ELSIF rec_person_race_int.status = '4' THEN
2244 l_warning := rec_person_race_int.count1;
2245 END IF;
2246 END LOOP;
2247
2248 IF l_success IS NULL THEN
2249 l_success := 0;
2250 END IF;
2251 IF l_error IS NULL THEN
2252 l_error := 0;
2253 END IF;
2254 IF l_warning IS NULL THEN
2255 l_warning := 0;
2256 END IF;
2257
2258 l_total_rec := l_success + l_error + l_warning;
2259 l_tab := 'IGS_PE_RACE_INT';
2260 INSERT INTO IGS_AD_IMP_STATS
2261 (
2262 INTERFACE_RUN_ID,
2263 SRC_CAT_CODE,
2264 ENTITY_NAME,
2265 TOTAL_REC_NUM,
2266 TOTAL_WARN_NUM,
2267 TOTAL_SUCCESS_NUM,
2268 TOTAL_ERROR_NUM,
2269 CREATED_BY,
2270 CREATION_DATE,
2271 LAST_UPDATED_BY,
2272 LAST_UPDATE_DATE
2273 )
2274 VALUES(
2275 l_interface_run_id,
2276 p_source_category,
2277 l_tab,
2278 l_total_rec,
2279 l_warning,
2280 l_success,
2281 l_error,
2282 l_user_id,
2283 l_sysdate,
2284 l_user_id,
2285 l_sysdate
2286 );
2287
2288 END IF;
2289
2290 IF (p_source_category = 'PERSON_ADDRESS') THEN
2291 FOR rec_person_addr_int IN cur_person_addr_int(l_interface_run_id)
2292 LOOP
2293 IF rec_person_addr_int.status = '1' THEN
2294 l_success := rec_person_addr_int.count1;
2295 ELSIF rec_person_addr_int.status = '3' THEN
2296 l_error := rec_person_addr_int.count1;
2297 ELSIF rec_person_addr_int.status = '4' THEN
2298 l_warning := rec_person_addr_int.count1;
2299 END IF;
2300 END LOOP;
2301
2302 IF l_success IS NULL THEN
2303 l_success := 0;
2304 END IF;
2305 IF l_error IS NULL THEN
2306 l_error := 0;
2307 END IF;
2308 IF l_warning IS NULL THEN
2309 l_warning := 0;
2310 END IF;
2311
2312 l_total_rec := l_success + l_error + l_warning;
2313 l_tab := 'IGS_AD_ADDR_INT_ALL';
2314 INSERT INTO IGS_AD_IMP_STATS
2315 (
2316 INTERFACE_RUN_ID,
2317 SRC_CAT_CODE,
2318 ENTITY_NAME,
2319 TOTAL_REC_NUM,
2320 TOTAL_WARN_NUM,
2321 TOTAL_SUCCESS_NUM,
2322 TOTAL_ERROR_NUM,
2323 CREATED_BY,
2324 CREATION_DATE,
2325 LAST_UPDATED_BY,
2326 LAST_UPDATE_DATE
2327 )
2328 VALUES(
2329 l_interface_run_id,
2330 p_source_category,
2331 l_tab,
2332 l_total_rec,
2333 l_warning,
2334 l_success,
2335 l_error,
2336 l_user_id,
2337 l_sysdate,
2338 l_user_id,
2339 l_sysdate
2340 );
2341
2342 l_success := 0;
2343 l_error := 0;
2344 l_warning := 0;
2345 l_total_rec := 0;
2346
2347 FOR rec_person_addrusage_int IN cur_person_addrusage_int(l_interface_run_id)
2348 LOOP
2349 IF rec_person_addrusage_int.status = '1' THEN
2350 l_success := rec_person_addrusage_int.count1;
2351 ELSIF rec_person_addrusage_int.status = '3' THEN
2352 l_error := rec_person_addrusage_int.count1;
2353 ELSIF rec_person_addrusage_int.status = '4' THEN
2354 l_warning := rec_person_addrusage_int.count1;
2355 END IF;
2356 END LOOP;
2357
2358 IF l_success IS NULL THEN
2359 l_success := 0;
2360 END IF;
2361 IF l_error IS NULL THEN
2362 l_error := 0;
2363 END IF;
2364 IF l_warning IS NULL THEN
2365 l_warning := 0;
2366 END IF;
2367
2368 l_total_rec := l_success + l_error + l_warning;
2369 l_tab := 'IGS_AD_ADDRUSAGE_INT_ALL';
2370 INSERT INTO IGS_AD_IMP_STATS
2371 (
2372 INTERFACE_RUN_ID,
2373 SRC_CAT_CODE,
2374 ENTITY_NAME,
2375 TOTAL_REC_NUM,
2376 TOTAL_WARN_NUM,
2377 TOTAL_SUCCESS_NUM,
2378 TOTAL_ERROR_NUM,
2379 CREATED_BY,
2380 CREATION_DATE,
2381 LAST_UPDATED_BY,
2382 LAST_UPDATE_DATE
2383 )
2384 VALUES(
2385 l_interface_run_id,
2386 p_source_category,
2387 l_tab,
2388 l_total_rec,
2389 l_warning,
2390 l_success,
2391 l_error,
2392 l_user_id,
2393 l_sysdate,
2394 l_user_id,
2395 l_sysdate
2396 );
2397
2398 END IF;
2399
2400 IF (p_source_category = 'PERSON_ALIAS') THEN
2401 FOR rec_person_alias_int IN cur_person_alias_int(l_interface_run_id)
2402 LOOP
2403 IF rec_person_alias_int.status = '1' THEN
2404 l_success := rec_person_alias_int.count1;
2405 ELSIF rec_person_alias_int.status = '3' THEN
2406 l_error := rec_person_alias_int.count1;
2407 ELSIF rec_person_alias_int.status = '4' THEN
2408 l_warning := rec_person_alias_int.count1;
2409 END IF;
2410 END LOOP;
2411
2412 IF l_success IS NULL THEN
2413 l_success := 0;
2414 END IF;
2415 IF l_error IS NULL THEN
2416 l_error := 0;
2417 END IF;
2418 IF l_warning IS NULL THEN
2419 l_warning := 0;
2420 END IF;
2421
2422 l_total_rec := l_success + l_error + l_warning;
2423 l_tab := 'IGS_AD_ALIAS_INT_ALL';
2424 INSERT INTO IGS_AD_IMP_STATS
2425 (
2426 INTERFACE_RUN_ID,
2427 SRC_CAT_CODE,
2428 ENTITY_NAME,
2429 TOTAL_REC_NUM,
2430 TOTAL_WARN_NUM,
2431 TOTAL_SUCCESS_NUM,
2432 TOTAL_ERROR_NUM,
2433 CREATED_BY,
2434 CREATION_DATE,
2435 LAST_UPDATED_BY,
2436 LAST_UPDATE_DATE
2437 )
2438 VALUES(
2439 l_interface_run_id,
2440 p_source_category,
2441 l_tab,
2442 l_total_rec,
2443 l_warning,
2444 l_success,
2445 l_error,
2446 l_user_id,
2447 l_sysdate,
2448 l_user_id,
2449 l_sysdate
2450 );
2451
2452
2453 END IF;
2454
2455 IF (p_source_category = 'PERSON_ID_TYPES') THEN
2456
2457 FOR rec_person_id_types_int IN cur_person_id_types_int(l_interface_run_id)
2458 LOOP
2459 IF rec_person_id_types_int.status = '1' THEN
2460 l_success := rec_person_id_types_int.count1;
2461 ELSIF rec_person_id_types_int.status = '3' THEN
2462 l_error := rec_person_id_types_int.count1;
2463 ELSIF rec_person_id_types_int.status = '4' THEN
2464 l_warning := rec_person_id_types_int.count1;
2465 END IF;
2466 END LOOP;
2467
2468 IF l_success IS NULL THEN
2469 l_success := 0;
2470 END IF;
2471 IF l_error IS NULL THEN
2472 l_error := 0;
2473 END IF;
2474 IF l_warning IS NULL THEN
2475 l_warning := 0;
2476 END IF;
2477
2478 l_total_rec := l_success + l_error + l_warning;
2479 l_tab := 'IGS_AD_API_INT_ALL';
2480 INSERT INTO IGS_AD_IMP_STATS
2481 (
2482 INTERFACE_RUN_ID,
2483 SRC_CAT_CODE,
2484 ENTITY_NAME,
2485 TOTAL_REC_NUM,
2486 TOTAL_WARN_NUM,
2487 TOTAL_SUCCESS_NUM,
2488 TOTAL_ERROR_NUM,
2489 CREATED_BY,
2490 CREATION_DATE,
2491 LAST_UPDATED_BY,
2492 LAST_UPDATE_DATE
2493 )
2494 VALUES(
2495 l_interface_run_id,
2496 p_source_category,
2497 l_tab,
2498 l_total_rec,
2499 l_warning,
2500 l_success,
2501 l_error,
2502 l_user_id,
2503 l_sysdate,
2504 l_user_id,
2505 l_sysdate
2506 );
2507
2508 END IF;
2509
2510 IF (p_source_category = 'PERSON_SPECIAL_NEEDS') THEN
2511 FOR rec_person_spcl_need_int IN cur_person_spcl_need_int(l_interface_run_id)
2512 LOOP
2513 IF rec_person_spcl_need_int.status = '1' THEN
2514 l_success := rec_person_spcl_need_int.count1;
2515 ELSIF rec_person_spcl_need_int.status = '3' THEN
2516 l_error := rec_person_spcl_need_int.count1;
2517 ELSIF rec_person_spcl_need_int.status = '4' THEN
2518 l_warning := rec_person_spcl_need_int.count1;
2519 END IF;
2520 END LOOP;
2521
2522 IF l_success IS NULL THEN
2523 l_success := 0;
2524 END IF;
2525 IF l_error IS NULL THEN
2526 l_error := 0;
2527 END IF;
2528 IF l_warning IS NULL THEN
2529 l_warning := 0;
2530 END IF;
2531
2532 l_total_rec := l_success + l_error + l_warning;
2533 l_tab := 'IGS_AD_DISABLTY_INT_ALL';
2534 INSERT INTO IGS_AD_IMP_STATS
2535 (
2536 INTERFACE_RUN_ID,
2537 SRC_CAT_CODE,
2538 ENTITY_NAME,
2539 TOTAL_REC_NUM,
2540 TOTAL_WARN_NUM,
2541 TOTAL_SUCCESS_NUM,
2542 TOTAL_ERROR_NUM,
2543 CREATED_BY,
2544 CREATION_DATE,
2545 LAST_UPDATED_BY,
2546 LAST_UPDATE_DATE
2547 )
2548 VALUES(
2549 l_interface_run_id,
2550 p_source_category,
2551 l_tab,
2552 l_total_rec,
2553 l_warning,
2554 l_success,
2555 l_error,
2556 l_user_id,
2557 l_sysdate,
2558 l_user_id,
2559 l_sysdate
2560 );
2561
2562 l_success := 0;
2563 l_error := 0;
2564 l_warning := 0;
2565 l_total_rec := 0;
2566
2567 FOR rec_person_srvc_int IN cur_person_srvc_int(l_interface_run_id)
2568 LOOP
2569 IF rec_person_srvc_int.status = '1' THEN
2570 l_success := rec_person_srvc_int.count1;
2571 ELSIF rec_person_srvc_int.status = '3' THEN
2572 l_error := rec_person_srvc_int.count1;
2573 ELSIF rec_person_srvc_int.status = '4' THEN
2574 l_warning := rec_person_srvc_int.count1;
2575 END IF;
2576 END LOOP;
2577
2578 IF l_success IS NULL THEN
2579 l_success := 0;
2580 END IF;
2581 IF l_error IS NULL THEN
2582 l_error := 0;
2583 END IF;
2584 IF l_warning IS NULL THEN
2585 l_warning := 0;
2586 END IF;
2587
2588 l_total_rec := l_success + l_error + l_warning;
2589 l_tab := 'IGS_PE_SN_SRVCE_INT';
2590 INSERT INTO IGS_AD_IMP_STATS
2591 (
2592 INTERFACE_RUN_ID,
2593 SRC_CAT_CODE,
2594 ENTITY_NAME,
2595 TOTAL_REC_NUM,
2596 TOTAL_WARN_NUM,
2597 TOTAL_SUCCESS_NUM,
2598 TOTAL_ERROR_NUM,
2599 CREATED_BY,
2600 CREATION_DATE,
2601 LAST_UPDATED_BY,
2602 LAST_UPDATE_DATE
2603 )
2604 VALUES(
2605 l_interface_run_id,
2606 p_source_category,
2607 l_tab,
2608 l_total_rec,
2609 l_warning,
2610 l_success,
2611 l_error,
2612 l_user_id,
2613 l_sysdate,
2614 l_user_id,
2615 l_sysdate
2616 );
2617
2618 l_success := 0;
2619 l_error := 0;
2620 l_warning := 0;
2621 l_total_rec := 0;
2622
2623 FOR rec_person_conc_int IN cur_person_conc_int(l_interface_run_id)
2624 LOOP
2625 IF rec_person_conc_int.status = '1' THEN
2626 l_success := rec_person_conc_int.count1;
2627 ELSIF rec_person_conc_int.status = '3' THEN
2628 l_error := rec_person_conc_int.count1;
2629 ELSIF rec_person_conc_int.status = '4' THEN
2630 l_warning := rec_person_conc_int.count1;
2631 END IF;
2632 END LOOP;
2633
2634 IF l_success IS NULL THEN
2635 l_success := 0;
2636 END IF;
2637 IF l_error IS NULL THEN
2638 l_error := 0;
2639 END IF;
2640 IF l_warning IS NULL THEN
2641 l_warning := 0;
2642 END IF;
2643 l_total_rec := l_success + l_error + l_warning;
2644 l_tab := 'IGS_PE_SN_CONCT_INT';
2645 INSERT INTO IGS_AD_IMP_STATS
2646 (
2647 INTERFACE_RUN_ID,
2648 SRC_CAT_CODE,
2649 ENTITY_NAME,
2650 TOTAL_REC_NUM,
2651 TOTAL_WARN_NUM,
2652 TOTAL_SUCCESS_NUM,
2653 TOTAL_ERROR_NUM,
2654 CREATED_BY,
2655 CREATION_DATE,
2656 LAST_UPDATED_BY,
2657 LAST_UPDATE_DATE
2658 )
2659 VALUES(
2660 l_interface_run_id,
2661 p_source_category,
2662 l_tab,
2663 l_total_rec,
2664 l_warning,
2665 l_success,
2666 l_error,
2667 l_user_id,
2668 l_sysdate,
2669 l_user_id,
2670 l_sysdate
2671 );
2672
2673 END IF;
2674
2675 IF (p_source_category = 'PERSON_EMPLOYMENT_DETAILS') THEN
2676 FOR rec_person_emp_dtl_int IN cur_person_emp_dtl_int(l_interface_run_id)
2677 LOOP
2678 IF rec_person_emp_dtl_int.status = '1' THEN
2679 l_success := rec_person_emp_dtl_int.count1;
2680 ELSIF rec_person_emp_dtl_int.status = '3' THEN
2681 l_error := rec_person_emp_dtl_int.count1;
2682 ELSIF rec_person_emp_dtl_int.status = '4' THEN
2683 l_warning := rec_person_emp_dtl_int.count1;
2684 END IF;
2685 END LOOP;
2686
2687 IF l_success IS NULL THEN
2688 l_success := 0;
2689 END IF;
2690 IF l_error IS NULL THEN
2691 l_error := 0;
2692 END IF;
2693 IF l_warning IS NULL THEN
2694 l_warning := 0;
2695 END IF;
2696
2697 l_total_rec := l_total_rec + l_success + l_error + l_warning;
2698 l_tab := 'IGS_AD_EMP_INT_ALL';
2699 INSERT INTO IGS_AD_IMP_STATS
2700 (
2701 INTERFACE_RUN_ID,
2702 SRC_CAT_CODE,
2703 ENTITY_NAME,
2704 TOTAL_REC_NUM,
2705 TOTAL_WARN_NUM,
2706 TOTAL_SUCCESS_NUM,
2707 TOTAL_ERROR_NUM,
2708 CREATED_BY,
2709 CREATION_DATE,
2710 LAST_UPDATED_BY,
2711 LAST_UPDATE_DATE
2712 )
2713 VALUES(
2714 l_interface_run_id,
2715 p_source_category,
2716 l_tab,
2717 l_total_rec,
2718 l_warning,
2719 l_success,
2720 l_error,
2721 l_user_id,
2722 l_sysdate,
2723 l_user_id,
2724 l_sysdate
2725 );
2726
2727 END IF;
2728
2729 IF (p_source_category = 'PERSON_INTERNATIONAL_DETAILS') THEN
2730 FOR rec_person_visa_int IN cur_person_visa_int(l_interface_run_id)
2731 LOOP
2732 IF rec_person_visa_int.status = '1' THEN
2733 l_success := rec_person_visa_int.count1;
2734 ELSIF rec_person_visa_int.status = '3' THEN
2735 l_error := rec_person_visa_int.count1;
2736 ELSIF rec_person_visa_int.status = '4' THEN
2737 l_warning := rec_person_visa_int.count1;
2738 END IF;
2739 END LOOP;
2740
2741 IF l_success IS NULL THEN
2742 l_success := 0;
2743 END IF;
2744 IF l_error IS NULL THEN
2745 l_error := 0;
2746 END IF;
2747 IF l_warning IS NULL THEN
2748 l_warning := 0;
2749 END IF;
2750
2751 l_total_rec := l_success + l_error + l_warning;
2752 l_tab := 'IGS_PE_VISA_INT';
2753 INSERT INTO IGS_AD_IMP_STATS
2754 (
2755 INTERFACE_RUN_ID,
2756 SRC_CAT_CODE,
2757 ENTITY_NAME,
2758 TOTAL_REC_NUM,
2759 TOTAL_WARN_NUM,
2760 TOTAL_SUCCESS_NUM,
2761 TOTAL_ERROR_NUM,
2762 CREATED_BY,
2763 CREATION_DATE,
2764 LAST_UPDATED_BY,
2765 LAST_UPDATE_DATE
2766 )
2767 VALUES(
2768 l_interface_run_id,
2769 p_source_category,
2770 l_tab,
2771 l_total_rec,
2772 l_warning,
2773 l_success,
2774 l_error,
2775 l_user_id,
2776 l_sysdate,
2777 l_user_id,
2778 l_sysdate
2779 );
2780
2781 l_success := 0;
2782 l_error := 0;
2783 l_warning := 0;
2784 l_total_rec := 0;
2785
2786 FOR rec_person_passport_int IN cur_person_passport_int(l_interface_run_id)
2787 LOOP
2788 IF rec_person_passport_int.status = '1' THEN
2789 l_success := rec_person_passport_int.count1;
2790 ELSIF rec_person_passport_int.status = '3' THEN
2791 l_error := rec_person_passport_int.count1;
2792 ELSIF rec_person_passport_int.status = '4' THEN
2793 l_warning := rec_person_passport_int.count1;
2794 END IF;
2795 END LOOP;
2796
2797 IF l_success IS NULL THEN
2798 l_success := 0;
2799 END IF;
2800 IF l_error IS NULL THEN
2801 l_error := 0;
2802 END IF;
2803 IF l_warning IS NULL THEN
2804 l_warning := 0;
2805 END IF;
2806
2807 l_total_rec := l_success + l_error + l_warning;
2808 l_tab := 'IGS_PE_PASSPORT_INT';
2809 INSERT INTO IGS_AD_IMP_STATS
2810 (
2811 INTERFACE_RUN_ID,
2812 SRC_CAT_CODE,
2813 ENTITY_NAME,
2814 TOTAL_REC_NUM,
2815 TOTAL_WARN_NUM,
2816 TOTAL_SUCCESS_NUM,
2817 TOTAL_ERROR_NUM,
2818 CREATED_BY,
2819 CREATION_DATE,
2820 LAST_UPDATED_BY,
2821 LAST_UPDATE_DATE
2822 )
2823 VALUES(
2824 l_interface_run_id,
2825 p_source_category,
2826 l_tab,
2827 l_total_rec,
2828 l_warning,
2829 l_success,
2830 l_error,
2831 l_user_id,
2832 l_sysdate,
2833 l_user_id,
2834 l_sysdate
2835 );
2836
2837 l_success := 0;
2838 l_error := 0;
2839 l_warning := 0;
2840 l_total_rec := 0;
2841
2842 FOR rec_person_hist_int IN cur_person_hist_int(l_interface_run_id)
2843 LOOP
2844 IF rec_person_hist_int.status = '1' THEN
2845 l_success := rec_person_hist_int.count1;
2846 ELSIF rec_person_hist_int.status = '3' THEN
2847 l_error := rec_person_hist_int.count1;
2848 ELSIF rec_person_hist_int.status = '4' THEN
2849 l_warning := rec_person_hist_int.count1;
2850 END IF;
2851 END LOOP;
2852
2853 IF l_success IS NULL THEN
2854 l_success := 0;
2855 END IF;
2856 IF l_error IS NULL THEN
2857 l_error := 0;
2858 END IF;
2859 IF l_warning IS NULL THEN
2860 l_warning := 0;
2861 END IF;
2862
2863 l_total_rec := l_success + l_error + l_warning;
2864 l_tab := 'IGS_PE_VST_HIST_INT';
2865 INSERT INTO IGS_AD_IMP_STATS
2866 (
2867 INTERFACE_RUN_ID,
2868 SRC_CAT_CODE,
2869 ENTITY_NAME,
2870 TOTAL_REC_NUM,
2871 TOTAL_WARN_NUM,
2872 TOTAL_SUCCESS_NUM,
2873 TOTAL_ERROR_NUM,
2874 CREATED_BY,
2875 CREATION_DATE,
2876 LAST_UPDATED_BY,
2877 LAST_UPDATE_DATE
2878 )
2879 VALUES(
2880 l_interface_run_id,
2881 p_source_category,
2882 l_tab,
2883 l_total_rec,
2884 l_warning,
2885 l_success,
2886 l_error,
2887 l_user_id,
2888 l_sysdate,
2889 l_user_id,
2890 l_sysdate
2891 );
2892
2893 l_success := 0;
2894 l_error := 0;
2895 l_warning := 0;
2896 l_total_rec := 0;
2897
2898 FOR rec_person_eit_int IN cur_person_eit_int(l_interface_run_id,'PE_INT_PERM_RES')
2899 LOOP
2900 IF rec_person_eit_int.status = '1' THEN
2901 l_success := rec_person_eit_int.count1;
2902 ELSIF rec_person_eit_int.status = '3' THEN
2903 l_error := rec_person_eit_int.count1;
2904 ELSIF rec_person_eit_int.status = '4' THEN
2905 l_warning := rec_person_eit_int.count1;
2906 END IF;
2907 END LOOP;
2908
2909 IF l_success IS NULL THEN
2910 l_success := 0;
2911 END IF;
2912 IF l_error IS NULL THEN
2913 l_error := 0;
2914 END IF;
2915 IF l_warning IS NULL THEN
2916 l_warning := 0;
2917 END IF;
2918
2919 l_total_rec := l_success + l_error + l_warning;
2920 l_tab := 'IGS_PE_EIT_INT-INTL';
2921 INSERT INTO IGS_AD_IMP_STATS
2922 (
2923 INTERFACE_RUN_ID,
2924 SRC_CAT_CODE,
2925 ENTITY_NAME,
2926 TOTAL_REC_NUM,
2927 TOTAL_WARN_NUM,
2928 TOTAL_SUCCESS_NUM,
2929 TOTAL_ERROR_NUM,
2930 CREATED_BY,
2931 CREATION_DATE,
2932 LAST_UPDATED_BY,
2933 LAST_UPDATE_DATE
2934 )
2935 VALUES(
2936 l_interface_run_id,
2937 p_source_category,
2938 l_tab,
2939 l_total_rec,
2940 l_warning,
2941 l_success,
2942 l_error,
2943 l_user_id,
2944 l_sysdate,
2945 l_user_id,
2946 l_sysdate
2947 );
2948
2949 l_success := 0;
2950 l_error := 0;
2951 l_warning := 0;
2952 l_total_rec := 0;
2953
2954 FOR rec_person_citizen_int IN cur_person_citizen_int(l_interface_run_id)
2955 LOOP
2956 IF rec_person_citizen_int.status = '1' THEN
2957 l_success := rec_person_citizen_int.count1;
2958 ELSIF rec_person_citizen_int.status = '3' THEN
2959 l_error := rec_person_citizen_int.count1;
2960 ELSIF rec_person_citizen_int.status = '4' THEN
2961 l_warning := rec_person_citizen_int.count1;
2962 END IF;
2963 END LOOP;
2964
2965 IF l_success IS NULL THEN
2966 l_success := 0;
2967 END IF;
2968 IF l_error IS NULL THEN
2969 l_error := 0;
2970 END IF;
2971 IF l_warning IS NULL THEN
2972 l_warning := 0;
2973 END IF;
2974 l_total_rec := l_success + l_error + l_warning;
2975 l_tab := 'IGS_PE_CITIZEN_INT';
2976 INSERT INTO IGS_AD_IMP_STATS
2977 (
2978 INTERFACE_RUN_ID,
2979 SRC_CAT_CODE,
2980 ENTITY_NAME,
2981 TOTAL_REC_NUM,
2982 TOTAL_WARN_NUM,
2983 TOTAL_SUCCESS_NUM,
2984 TOTAL_ERROR_NUM,
2985 CREATED_BY,
2986 CREATION_DATE,
2987 LAST_UPDATED_BY,
2988 LAST_UPDATE_DATE
2989 )
2990 VALUES(
2991 l_interface_run_id,
2992 p_source_category,
2993 l_tab,
2994 l_total_rec,
2995 l_warning,
2996 l_success,
2997 l_error,
2998 l_user_id,
2999 l_sysdate,
3000 l_user_id,
3001 l_sysdate
3002 );
3003
3004 l_success := 0;
3005 l_error := 0;
3006 l_warning := 0;
3007 l_total_rec := 0;
3008
3009 FOR rec_person_fund_int IN cur_person_fund_int(l_interface_run_id)
3010 LOOP
3011 IF rec_person_fund_int.status = '1' THEN
3012 l_success := rec_person_fund_int.count1;
3013 ELSIF rec_person_fund_int.status = '3' THEN
3014 l_error := rec_person_fund_int.count1;
3015 ELSIF rec_person_fund_int.status = '4' THEN
3016 l_warning := rec_person_fund_int.count1;
3017 END IF;
3018 END LOOP;
3019
3020 IF l_success IS NULL THEN
3021 l_success := 0;
3022 END IF;
3023 IF l_error IS NULL THEN
3024 l_error := 0;
3025 END IF;
3026 IF l_warning IS NULL THEN
3027 l_warning := 0;
3028 END IF;
3029
3030 l_total_rec := l_success + l_error + l_warning;
3031 l_tab := 'IGS_PE_FUND_SRC_INT';
3032 INSERT INTO IGS_AD_IMP_STATS
3033 (
3034 INTERFACE_RUN_ID,
3035 SRC_CAT_CODE,
3036 ENTITY_NAME,
3037 TOTAL_REC_NUM,
3038 TOTAL_WARN_NUM,
3039 TOTAL_SUCCESS_NUM,
3040 TOTAL_ERROR_NUM,
3041 CREATED_BY,
3042 CREATION_DATE,
3043 LAST_UPDATED_BY,
3044 LAST_UPDATE_DATE
3045 )
3046 VALUES(
3047 l_interface_run_id,
3048 p_source_category,
3049 l_tab,
3050 l_total_rec,
3051 l_warning,
3052 l_success,
3053 l_error,
3054 l_user_id,
3055 l_sysdate,
3056 l_user_id,
3057 l_sysdate
3058 );
3059
3060 END IF;
3061
3062 IF (p_source_category = 'PERSON_HEALTH_INSURANCE') THEN
3063 FOR rec_person_immu_dtl_int IN cur_person_immu_dtl_int(l_interface_run_id)
3064 LOOP
3065 IF rec_person_immu_dtl_int.status = '1' THEN
3066 l_success := rec_person_immu_dtl_int.count1;
3067 ELSIF rec_person_immu_dtl_int.status = '3' THEN
3068 l_error := rec_person_immu_dtl_int.count1;
3069 ELSIF rec_person_immu_dtl_int.status = '4' THEN
3070 l_warning := rec_person_immu_dtl_int.count1;
3071 END IF;
3072 END LOOP;
3073
3074 IF l_success IS NULL THEN
3075 l_success := 0;
3076 END IF;
3077 IF l_error IS NULL THEN
3078 l_error := 0;
3079 END IF;
3080 IF l_warning IS NULL THEN
3081 l_warning := 0;
3082 END IF;
3083
3084 l_total_rec := l_success + l_error + l_warning;
3085 l_tab := 'IGS_PE_IMMU_DTL_INT';
3086 INSERT INTO IGS_AD_IMP_STATS
3087 (
3088 INTERFACE_RUN_ID,
3089 SRC_CAT_CODE,
3090 ENTITY_NAME,
3091 TOTAL_REC_NUM,
3092 TOTAL_WARN_NUM,
3093 TOTAL_SUCCESS_NUM,
3094 TOTAL_ERROR_NUM,
3095 CREATED_BY,
3096 CREATION_DATE,
3097 LAST_UPDATED_BY,
3098 LAST_UPDATE_DATE
3099 )
3100 VALUES(
3101 l_interface_run_id,
3102 p_source_category,
3103 l_tab,
3104 l_total_rec,
3105 l_warning,
3106 l_success,
3107 l_error,
3108 l_user_id,
3109 l_sysdate,
3110 l_user_id,
3111 l_sysdate
3112 );
3113
3114 l_success := 0;
3115 l_error := 0;
3116 l_warning := 0;
3117 l_total_rec := 0;
3118
3119 FOR rec_person_health_int IN cur_person_health_int(l_interface_run_id)
3120 LOOP
3121 IF rec_person_health_int.status = '1' THEN
3122 l_success := rec_person_health_int.count1;
3123 ELSIF rec_person_health_int.status = '3' THEN
3124 l_error := rec_person_health_int.count1;
3125 ELSIF rec_person_health_int.status = '4' THEN
3126 l_warning := rec_person_health_int.count1;
3127 END IF;
3128 END LOOP;
3129
3130 IF l_success IS NULL THEN
3131 l_success := 0;
3132 END IF;
3133 IF l_error IS NULL THEN
3134 l_error := 0;
3135 END IF;
3136 IF l_warning IS NULL THEN
3137 l_warning := 0;
3138 END IF;
3139 l_total_rec := l_success + l_error + l_warning;
3140 l_tab := 'IGS_AD_HLTH_INS_INT_ALL';
3141 INSERT INTO IGS_AD_IMP_STATS
3142 (
3143 INTERFACE_RUN_ID,
3144 SRC_CAT_CODE,
3145 ENTITY_NAME,
3146 TOTAL_REC_NUM,
3147 TOTAL_WARN_NUM,
3148 TOTAL_SUCCESS_NUM,
3149 TOTAL_ERROR_NUM,
3150 CREATED_BY,
3151 CREATION_DATE,
3152 LAST_UPDATED_BY,
3153 LAST_UPDATE_DATE
3154 )
3155 VALUES(
3156 l_interface_run_id,
3157 p_source_category,
3158 l_tab,
3159 l_total_rec,
3160 l_warning,
3161 l_success,
3162 l_error,
3163 l_user_id,
3164 l_sysdate,
3165 l_user_id,
3166 l_sysdate
3167 );
3168
3169 END IF;
3170
3171 IF (p_source_category = 'PERSON_MILITARY_DETAILS') THEN
3172 FOR rec_person_mil_dtl_int IN cur_person_mil_dtl_int(l_interface_run_id)
3173 LOOP
3174 IF rec_person_mil_dtl_int.status = '1' THEN
3175 l_success := rec_person_mil_dtl_int.count1;
3176 ELSIF rec_person_mil_dtl_int.status = '3' THEN
3177 l_error := rec_person_mil_dtl_int.count1;
3178 ELSIF rec_person_mil_dtl_int.status = '4' THEN
3179 l_warning := rec_person_mil_dtl_int.count1;
3180 END IF;
3181 END LOOP;
3182
3183 IF l_success IS NULL THEN
3184 l_success := 0;
3185 END IF;
3186 IF l_error IS NULL THEN
3187 l_error := 0;
3188 END IF;
3189 IF l_warning IS NULL THEN
3190 l_warning := 0;
3191 END IF;
3192
3193 l_total_rec := l_success + l_error + l_warning;
3194 l_tab := 'IGS_AD_MILITARY_INT_ALL';
3195 INSERT INTO IGS_AD_IMP_STATS
3196 (
3197 INTERFACE_RUN_ID,
3198 SRC_CAT_CODE,
3199 ENTITY_NAME,
3200 TOTAL_REC_NUM,
3201 TOTAL_WARN_NUM,
3202 TOTAL_SUCCESS_NUM,
3203 TOTAL_ERROR_NUM,
3204 CREATED_BY,
3205 CREATION_DATE,
3206 LAST_UPDATED_BY,
3207 LAST_UPDATE_DATE
3208 )
3209 VALUES(
3210 l_interface_run_id,
3211 p_source_category,
3212 l_tab,
3213 l_total_rec,
3214 l_warning,
3215 l_success,
3216 l_error,
3217 l_user_id,
3218 l_sysdate,
3219 l_user_id,
3220 l_sysdate
3221 );
3222
3223 END IF;
3224
3225 IF (p_source_category = 'PERSON_ACTIVITIES') THEN
3226 FOR rec_person_act_int IN cur_person_act_int(l_interface_run_id)
3227 LOOP
3228 IF rec_person_act_int.status = '1' THEN
3229 l_success := rec_person_act_int.count1;
3230 ELSIF rec_person_act_int.status = '3' THEN
3231 l_error := rec_person_act_int.count1;
3232 ELSIF rec_person_act_int.status = '4' THEN
3233 l_warning := rec_person_act_int.count1;
3234 END IF;
3235 END LOOP;
3236
3237 IF l_success IS NULL THEN
3238 l_success := 0;
3239 END IF;
3240 IF l_error IS NULL THEN
3241 l_error := 0;
3242 END IF;
3243 IF l_warning IS NULL THEN
3244 l_warning := 0;
3245 END IF;
3246
3247 l_total_rec := l_success + l_error + l_warning;
3248 l_tab := 'IGS_AD_EXCURR_INT_ALL';
3249 INSERT INTO IGS_AD_IMP_STATS
3250 (
3251 INTERFACE_RUN_ID,
3252 SRC_CAT_CODE,
3253 ENTITY_NAME,
3254 TOTAL_REC_NUM,
3255 TOTAL_WARN_NUM,
3256 TOTAL_SUCCESS_NUM,
3257 TOTAL_ERROR_NUM,
3258 CREATED_BY,
3259 CREATION_DATE,
3260 LAST_UPDATED_BY,
3261 LAST_UPDATE_DATE
3262 )
3263 VALUES(
3264 l_interface_run_id,
3265 p_source_category,
3266 l_tab,
3267 l_total_rec,
3268 l_warning,
3269 l_success,
3270 l_error,
3271 l_user_id,
3272 l_sysdate,
3273 l_user_id,
3274 l_sysdate
3275 );
3276
3277 END IF;
3278
3279 IF (p_source_category = 'PERSON_RELATIONS') THEN
3280 FOR rec_person_rel_int IN cur_person_rel_int(l_interface_run_id)
3281 LOOP
3282 IF rec_person_rel_int.status = '1' THEN
3283 l_success := rec_person_rel_int.count1;
3284 ELSIF rec_person_rel_int.status = '3' THEN
3285 l_error := rec_person_rel_int.count1;
3286 ELSIF rec_person_rel_int.status = '4' THEN
3287 l_warning := rec_person_rel_int.count1;
3288 END IF;
3289 END LOOP;
3290
3291 IF l_success IS NULL THEN
3292 l_success := 0;
3293 END IF;
3294 IF l_error IS NULL THEN
3295 l_error := 0;
3296 END IF;
3297 IF l_warning IS NULL THEN
3298 l_warning := 0;
3299 END IF;
3300
3301 l_total_rec := l_success + l_error + l_warning;
3302 l_tab := 'IGS_AD_RELATIONS_INT_ALL';
3303 INSERT INTO IGS_AD_IMP_STATS
3304 (
3305 INTERFACE_RUN_ID,
3306 SRC_CAT_CODE,
3307 ENTITY_NAME,
3308 TOTAL_REC_NUM,
3309 TOTAL_WARN_NUM,
3310 TOTAL_SUCCESS_NUM,
3311 TOTAL_ERROR_NUM,
3312 CREATED_BY,
3313 CREATION_DATE,
3314 LAST_UPDATED_BY,
3315 LAST_UPDATE_DATE
3316 )
3317 VALUES(
3318 l_interface_run_id,
3319 p_source_category,
3320 l_tab,
3321 l_total_rec,
3322 l_warning,
3323 l_success,
3324 l_error,
3325 l_user_id,
3326 l_sysdate,
3327 l_user_id,
3328 l_sysdate
3329 );
3330
3331 END IF;
3332
3333 IF (p_source_category = 'PERSON_ATHLETICS') THEN
3334 FOR rec_person_ath_dtl_int IN cur_person_ath_dtl_int(l_interface_run_id)
3335 LOOP
3336 IF rec_person_ath_dtl_int.status = '1' THEN
3337 l_success := rec_person_ath_dtl_int.count1;
3338 ELSIF rec_person_ath_dtl_int.status = '3' THEN
3339 l_error := rec_person_ath_dtl_int.count1;
3340 ELSIF rec_person_ath_dtl_int.status = '4' THEN
3341 l_warning := rec_person_ath_dtl_int.count1;
3342 END IF;
3343 END LOOP;
3344
3345 IF l_success IS NULL THEN
3346 l_success := 0;
3347 END IF;
3348 IF l_error IS NULL THEN
3349 l_error := 0;
3350 END IF;
3351 IF l_warning IS NULL THEN
3352 l_warning := 0;
3353 END IF;
3354
3355 l_total_rec := l_success + l_error + l_warning;
3356 l_tab := 'IGS_PE_ATH_DTL_INT';
3357 INSERT INTO IGS_AD_IMP_STATS
3358 (
3359 INTERFACE_RUN_ID,
3360 SRC_CAT_CODE,
3361 ENTITY_NAME,
3362 TOTAL_REC_NUM,
3363 TOTAL_WARN_NUM,
3364 TOTAL_SUCCESS_NUM,
3365 TOTAL_ERROR_NUM,
3366 CREATED_BY,
3367 CREATION_DATE,
3368 LAST_UPDATED_BY,
3369 LAST_UPDATE_DATE
3370 )
3371 VALUES(
3372 l_interface_run_id,
3373 p_source_category,
3374 l_tab,
3375 l_total_rec,
3376 l_warning,
3377 l_success,
3378 l_error,
3379 l_user_id,
3380 l_sysdate,
3381 l_user_id,
3382 l_sysdate
3383 );
3384
3385 l_success := 0;
3386 l_error := 0;
3387 l_warning := 0;
3388 l_total_rec := 0;
3389
3390 FOR rec_person_ath_prg_int IN cur_person_ath_prg_int(l_interface_run_id)
3391 LOOP
3392 IF rec_person_ath_prg_int.status = '1' THEN
3393 l_success := rec_person_ath_prg_int.count1;
3394 ELSIF rec_person_ath_prg_int.status = '3' THEN
3395 l_error := rec_person_ath_prg_int.count1;
3396 ELSIF rec_person_ath_prg_int.status = '4' THEN
3397 l_warning := rec_person_ath_prg_int.count1;
3398 END IF;
3399 END LOOP;
3400
3401 IF l_success IS NULL THEN
3402 l_success := 0;
3403 END IF;
3404 IF l_error IS NULL THEN
3405 l_error := 0;
3406 END IF;
3407 IF l_warning IS NULL THEN
3408 l_warning := 0;
3409 END IF;
3410
3411 l_total_rec := l_success + l_error + l_warning;
3412 l_tab := 'IGS_PE_ATH_PRG_INT';
3413 INSERT INTO IGS_AD_IMP_STATS
3414 (
3415 INTERFACE_RUN_ID,
3416 SRC_CAT_CODE,
3417 ENTITY_NAME,
3418 TOTAL_REC_NUM,
3419 TOTAL_WARN_NUM,
3420 TOTAL_SUCCESS_NUM,
3421 TOTAL_ERROR_NUM,
3422 CREATED_BY,
3423 CREATION_DATE,
3424 LAST_UPDATED_BY,
3425 LAST_UPDATE_DATE
3426 )
3427 VALUES(
3428 l_interface_run_id,
3429 p_source_category,
3430 l_tab,
3431 l_total_rec,
3432 l_warning,
3433 l_success,
3434 l_error,
3435 l_user_id,
3436 l_sysdate,
3437 l_user_id,
3438 l_sysdate
3439 );
3440
3441 END IF;
3442
3443 IF (p_source_category = 'PERSON_LANGUAGES') THEN
3444 FOR rec_person_lang_int IN cur_person_lang_int(l_interface_run_id)
3445 LOOP
3446 IF rec_person_lang_int.status = '1' THEN
3447 l_success := rec_person_lang_int.count1;
3448 ELSIF rec_person_lang_int.status = '3' THEN
3449 l_error := rec_person_lang_int.count1;
3450 ELSIF rec_person_lang_int.status = '4' THEN
3451 l_warning := rec_person_lang_int.count1;
3452 END IF;
3453 END LOOP;
3454
3455 IF l_success IS NULL THEN
3456 l_success := 0;
3457 END IF;
3458 IF l_error IS NULL THEN
3459 l_error := 0;
3460 END IF;
3461 IF l_warning IS NULL THEN
3462 l_warning := 0;
3463 END IF;
3464
3465 l_total_rec := l_success + l_error + l_warning;
3466 l_tab := 'IGS_AD_LANGUAGE_INT_ALL';
3467 INSERT INTO IGS_AD_IMP_STATS
3468 (
3469 INTERFACE_RUN_ID,
3470 SRC_CAT_CODE,
3471 ENTITY_NAME,
3472 TOTAL_REC_NUM,
3473 TOTAL_WARN_NUM,
3474 TOTAL_SUCCESS_NUM,
3475 TOTAL_ERROR_NUM,
3476 CREATED_BY,
3477 CREATION_DATE,
3478 LAST_UPDATED_BY,
3479 LAST_UPDATE_DATE
3480 )
3481 VALUES(
3482 l_interface_run_id,
3483 p_source_category,
3484 l_tab,
3485 l_total_rec,
3486 l_warning,
3487 l_success,
3488 l_error,
3489 l_user_id,
3490 l_sysdate,
3491 l_user_id,
3492 l_sysdate
3493 );
3494
3495 END IF;
3496
3497 IF (p_source_category = 'PERSON_CONTACTS') THEN
3498 FOR rec_person_contact_int IN cur_person_contact_int(l_interface_run_id)
3499 LOOP
3500 IF rec_person_contact_int.status = '1' THEN
3501 l_success := rec_person_contact_int.count1;
3502 ELSIF rec_person_contact_int.status = '3' THEN
3503 l_error := rec_person_contact_int.count1;
3504 ELSIF rec_person_contact_int.status = '4' THEN
3505 l_warning := rec_person_contact_int.count1;
3506 END IF;
3507 END LOOP;
3508
3509 IF l_success IS NULL THEN
3510 l_success := 0;
3511 END IF;
3512 IF l_error IS NULL THEN
3513 l_error := 0;
3514 END IF;
3515 IF l_warning IS NULL THEN
3516 l_warning := 0;
3517 END IF;
3518
3519 l_total_rec := l_success + l_error + l_warning;
3520 l_tab := 'IGS_AD_CONTACTS_INT_ALL';
3521 INSERT INTO IGS_AD_IMP_STATS
3522 (
3523 INTERFACE_RUN_ID,
3524 SRC_CAT_CODE,
3525 ENTITY_NAME,
3526 TOTAL_REC_NUM,
3527 TOTAL_WARN_NUM,
3528 TOTAL_SUCCESS_NUM,
3529 TOTAL_ERROR_NUM,
3530 CREATED_BY,
3531 CREATION_DATE,
3532 LAST_UPDATED_BY,
3533 LAST_UPDATE_DATE
3534 )
3535 VALUES(
3536 l_interface_run_id,
3537 p_source_category,
3538 l_tab,
3539 l_total_rec,
3540 l_warning,
3541 l_success,
3542 l_error,
3543 l_user_id,
3544 l_sysdate,
3545 l_user_id,
3546 l_sysdate
3547 );
3548
3549 END IF;
3550
3551 IF (p_source_category = 'PERSON_DISCIPLINARY_DTLS') THEN
3552 FOR rec_person_flny_dtls_int IN cur_person_flny_dtls_int(l_interface_run_id)
3553 LOOP
3554 IF rec_person_flny_dtls_int.status = '1' THEN
3555 l_success := rec_person_flny_dtls_int.count1;
3556 ELSIF rec_person_flny_dtls_int.status = '3' THEN
3557 l_error := rec_person_flny_dtls_int.count1;
3558 ELSIF rec_person_flny_dtls_int.status = '4' THEN
3559 l_warning := rec_person_flny_dtls_int.count1;
3560 END IF;
3561 END LOOP;
3562
3563 IF l_success IS NULL THEN
3564 l_success := 0;
3565 END IF;
3566 IF l_error IS NULL THEN
3567 l_error := 0;
3568 END IF;
3569 IF l_warning IS NULL THEN
3570 l_warning := 0;
3571 END IF;
3572
3573 l_total_rec := l_success + l_error + l_warning;
3574 l_tab := 'IGS_PE_FLNY_DTL_INT';
3575 INSERT INTO IGS_AD_IMP_STATS
3576 (
3577 INTERFACE_RUN_ID,
3578 SRC_CAT_CODE,
3579 ENTITY_NAME,
3580 TOTAL_REC_NUM,
3581 TOTAL_WARN_NUM,
3582 TOTAL_SUCCESS_NUM,
3583 TOTAL_ERROR_NUM,
3584 CREATED_BY,
3585 CREATION_DATE,
3586 LAST_UPDATED_BY,
3587 LAST_UPDATE_DATE
3588 )
3589 VALUES(
3590 l_interface_run_id,
3591 p_source_category,
3592 l_tab,
3593 l_total_rec,
3594 l_warning,
3595 l_success,
3596 l_error,
3597 l_user_id,
3598 l_sysdate,
3599 l_user_id,
3600 l_sysdate
3601 );
3602
3603 l_success := 0;
3604 l_error := 0;
3605 l_warning := 0;
3606 l_total_rec := 0;
3607
3608 FOR rec_person_hear_int IN cur_person_hear_int(l_interface_run_id)
3609 LOOP
3610 IF rec_person_hear_int.status = '1' THEN
3611 l_success := rec_person_hear_int.count1;
3612 ELSIF rec_person_hear_int.status = '3' THEN
3613 l_error := rec_person_hear_int.count1;
3614 ELSIF rec_person_hear_int.status = '4' THEN
3615 l_warning := rec_person_hear_int.count1;
3616 END IF;
3617 END LOOP;
3618
3619 IF l_success IS NULL THEN
3620 l_success := 0;
3621 END IF;
3622 IF l_error IS NULL THEN
3623 l_error := 0;
3624 END IF;
3625 IF l_warning IS NULL THEN
3626 l_warning := 0;
3627 END IF;
3628
3629 l_total_rec := l_success + l_error + l_warning;
3630 l_tab := 'IGS_PE_HEAR_DTL_INT';
3631 INSERT INTO IGS_AD_IMP_STATS
3632 (
3633 INTERFACE_RUN_ID,
3634 SRC_CAT_CODE,
3635 ENTITY_NAME,
3636 TOTAL_REC_NUM,
3637 TOTAL_WARN_NUM,
3638 TOTAL_SUCCESS_NUM,
3639 TOTAL_ERROR_NUM,
3640 CREATED_BY,
3641 CREATION_DATE,
3642 LAST_UPDATED_BY,
3643 LAST_UPDATE_DATE
3644 )
3645 VALUES(
3646 l_interface_run_id,
3647 p_source_category,
3648 l_tab,
3649 l_total_rec,
3650 l_warning,
3651 l_success,
3652 l_error,
3653 l_user_id,
3654 l_sysdate,
3655 l_user_id,
3656 l_sysdate
3657 );
3658
3659 END IF;
3660
3661 IF (p_source_category = 'PERSON_HOUSING_STATUS') THEN
3662 FOR rec_person_housing_stat_int IN cur_person_housing_stat_int(l_interface_run_id)
3663 LOOP
3664 IF rec_person_housing_stat_int.status = '1' THEN
3665 l_success := rec_person_housing_stat_int.count1;
3666 ELSIF rec_person_housing_stat_int.status = '3' THEN
3667 l_error := rec_person_housing_stat_int.count1;
3668 ELSIF rec_person_housing_stat_int.status = '4' THEN
3669 l_warning := rec_person_housing_stat_int.count1;
3670 END IF;
3671 END LOOP;
3672
3673 IF l_success IS NULL THEN
3674 l_success := 0;
3675 END IF;
3676 IF l_error IS NULL THEN
3677 l_error := 0;
3678 END IF;
3679 IF l_warning IS NULL THEN
3680 l_warning := 0;
3681 END IF;
3682 l_total_rec := l_success + l_error + l_warning;
3683 l_tab := 'IGS_PE_HOUSING_INT';
3684 INSERT INTO IGS_AD_IMP_STATS
3685 (
3686 INTERFACE_RUN_ID,
3687 SRC_CAT_CODE,
3688 ENTITY_NAME,
3689 TOTAL_REC_NUM,
3690 TOTAL_WARN_NUM,
3691 TOTAL_SUCCESS_NUM,
3692 TOTAL_ERROR_NUM,
3693 CREATED_BY,
3694 CREATION_DATE,
3695 LAST_UPDATED_BY,
3696 LAST_UPDATE_DATE
3697 )
3698 VALUES(
3699 l_interface_run_id,
3700 p_source_category,
3701 l_tab,
3702 l_total_rec,
3703 l_warning,
3704 l_success,
3705 l_error,
3706 l_user_id,
3707 l_sysdate,
3708 l_user_id,
3709 l_sysdate
3710 );
3711
3712 END IF;
3713
3714 IF (p_source_category = 'PERSON_ACAD_HONORS') THEN
3715 FOR rec_person_acad_honors_int IN cur_person_acad_honors_int(l_interface_run_id)
3716 LOOP
3717 IF rec_person_acad_honors_int.status = '1' THEN
3718 l_success := rec_person_acad_honors_int.count1;
3719 ELSIF rec_person_acad_honors_int.status = '3' THEN
3720 l_error := rec_person_acad_honors_int.count1;
3721 ELSIF rec_person_acad_honors_int.status = '4' THEN
3722 l_warning := rec_person_acad_honors_int.count1;
3723 END IF;
3724 END LOOP;
3725
3726 IF l_success IS NULL THEN
3727 l_success := 0;
3728 END IF;
3729 IF l_error IS NULL THEN
3730 l_error := 0;
3731 END IF;
3732 IF l_warning IS NULL THEN
3733 l_warning := 0;
3734 END IF;
3735
3736 l_total_rec := l_success + l_error + l_warning;
3737 l_tab := 'IGS_AD_ACADHONOR_INT_ALL';
3738 INSERT INTO IGS_AD_IMP_STATS
3739 (
3740 INTERFACE_RUN_ID,
3741 SRC_CAT_CODE,
3742 ENTITY_NAME,
3743 TOTAL_REC_NUM,
3744 TOTAL_WARN_NUM,
3745 TOTAL_SUCCESS_NUM,
3746 TOTAL_ERROR_NUM,
3747 CREATED_BY,
3748 CREATION_DATE,
3749 LAST_UPDATED_BY,
3750 LAST_UPDATE_DATE
3751 )
3752 VALUES(
3753 l_interface_run_id,
3754 p_source_category,
3755 l_tab,
3756 l_total_rec,
3757 l_warning,
3758 l_success,
3759 l_error,
3760 l_user_id,
3761 l_sysdate,
3762 l_user_id,
3763 l_sysdate
3764 );
3765
3766 END IF;
3767
3768 IF (p_source_category = 'PERSON_RESIDENCY_DETAILS') THEN
3769 FOR rec_person_res_dtl_int IN cur_person_res_dtl_int(l_interface_run_id)
3770 LOOP
3771 IF rec_person_res_dtl_int.status = '1' THEN
3772 l_success := rec_person_res_dtl_int.count1;
3773 ELSIF rec_person_res_dtl_int.status = '3' THEN
3774 l_error := rec_person_res_dtl_int.count1;
3775 ELSIF rec_person_res_dtl_int.status = '4' THEN
3776 l_warning := rec_person_res_dtl_int.count1;
3777 END IF;
3778 END LOOP;
3779
3780 IF l_success IS NULL THEN
3781 l_success := 0;
3782 END IF;
3783 IF l_error IS NULL THEN
3784 l_error := 0;
3785 END IF;
3786 IF l_warning IS NULL THEN
3787 l_warning := 0;
3788 END IF;
3789 l_total_rec := l_success + l_error + l_warning;
3790 l_tab := 'IGS_PE_RES_DTLS_INT';
3791 INSERT INTO IGS_AD_IMP_STATS
3792 (
3793 INTERFACE_RUN_ID,
3794 SRC_CAT_CODE,
3795 ENTITY_NAME,
3796 TOTAL_REC_NUM,
3797 TOTAL_WARN_NUM,
3798 TOTAL_SUCCESS_NUM,
3799 TOTAL_ERROR_NUM,
3800 CREATED_BY,
3801 CREATION_DATE,
3802 LAST_UPDATED_BY,
3803 LAST_UPDATE_DATE
3804 )
3805 VALUES(
3806 l_interface_run_id,
3807 p_source_category,
3808 l_tab,
3809 l_total_rec,
3810 l_warning,
3811 l_success,
3812 l_error,
3813 l_user_id,
3814 l_sysdate,
3815 l_user_id,
3816 l_sysdate
3817 );
3818
3819 END IF;
3820
3821 IF (p_source_category = 'RELATIONS_ACAD_HISTORY' ) THEN
3822 FOR rec_relacad_int IN cur_relacad_int(l_interface_run_id)
3823 LOOP
3824 IF rec_relacad_int.status = '1' THEN
3825 l_success := rec_relacad_int.count1;
3826 ELSIF rec_relacad_int.status = '3' THEN
3827 l_error := rec_relacad_int.count1;
3828 ELSIF rec_relacad_int.status = '4' THEN
3829 l_warning := rec_relacad_int.count1;
3830 END IF;
3831 END LOOP;
3832
3833 IF l_success IS NULL THEN
3834 l_success := 0;
3835 END IF;
3836 IF l_error IS NULL THEN
3837 l_error := 0;
3838 END IF;
3839 IF l_warning IS NULL THEN
3840 l_warning := 0;
3841 END IF;
3842
3843 l_total_rec := l_success + l_error + l_warning;
3844 l_tab := 'IGS_AD_RELACAD_INT_ALL';
3845 INSERT INTO IGS_AD_IMP_STATS
3846 (
3847 INTERFACE_RUN_ID,
3848 SRC_CAT_CODE,
3849 ENTITY_NAME,
3850 TOTAL_REC_NUM,
3851 TOTAL_WARN_NUM,
3852 TOTAL_SUCCESS_NUM,
3853 TOTAL_ERROR_NUM,
3854 CREATED_BY,
3855 CREATION_DATE,
3856 LAST_UPDATED_BY,
3857 LAST_UPDATE_DATE
3858 )
3859 VALUES(
3860 l_interface_run_id,
3861 p_source_category,
3862 l_tab,
3863 l_total_rec,
3864 l_warning,
3865 l_success,
3866 l_error,
3867 l_user_id,
3868 l_sysdate,
3869 l_user_id,
3870 l_sysdate
3871 );
3872
3873 END IF;
3874 IF (p_source_category = 'RELATIONS_ADDRESS') THEN
3875 FOR rec_rel_addr_int IN cur_rel_addr_int(l_interface_run_id)
3876 LOOP
3877 IF rec_rel_addr_int.status = '1' THEN
3878 l_success := rec_rel_addr_int.count1;
3879 ELSIF rec_rel_addr_int.status = '3' THEN
3880 l_error := rec_rel_addr_int.count1;
3881 ELSIF rec_rel_addr_int.status = '4' THEN
3882 l_warning := rec_rel_addr_int.count1;
3883 END IF;
3884 END LOOP;
3885
3886 IF l_success IS NULL THEN
3887 l_success := 0;
3888 END IF;
3889 IF l_error IS NULL THEN
3890 l_error := 0;
3891 END IF;
3892 IF l_warning IS NULL THEN
3893 l_warning := 0;
3894 END IF;
3895
3896 l_total_rec := l_success + l_error + l_warning;
3897 l_tab := 'IGS_AD_RELADDR_INT_ALL';
3898 INSERT INTO IGS_AD_IMP_STATS
3899 (
3900 INTERFACE_RUN_ID,
3901 SRC_CAT_CODE,
3902 ENTITY_NAME,
3903 TOTAL_REC_NUM,
3904 TOTAL_WARN_NUM,
3905 TOTAL_SUCCESS_NUM,
3906 TOTAL_ERROR_NUM,
3907 CREATED_BY,
3908 CREATION_DATE,
3909 LAST_UPDATED_BY,
3910 LAST_UPDATE_DATE
3911 )
3912 VALUES(
3913 l_interface_run_id,
3914 p_source_category,
3915 l_tab,
3916 l_total_rec,
3917 l_warning,
3918 l_success,
3919 l_error,
3920 l_user_id,
3921 l_sysdate,
3922 l_user_id,
3923 l_sysdate
3924 );
3925
3926 END IF;
3927 IF (p_source_category = 'RELATIONS_CONTACTS') THEN
3928 FOR rec_relcon_int IN cur_relcon_int(l_interface_run_id)
3929 LOOP
3930 IF rec_relcon_int.status = '1' THEN
3931 l_success := rec_relcon_int.count1;
3932 ELSIF rec_relcon_int.status = '3' THEN
3933 l_error := rec_relcon_int.count1;
3934 ELSIF rec_relcon_int.status = '4' THEN
3935 l_warning := rec_relcon_int.count1;
3936 END IF;
3937 END LOOP;
3938
3939 IF l_success IS NULL THEN
3940 l_success := 0;
3941 END IF;
3942 IF l_error IS NULL THEN
3943 l_error := 0;
3944 END IF;
3945 IF l_warning IS NULL THEN
3946 l_warning := 0;
3947 END IF;
3948 l_total_rec := l_success + l_error + l_warning;
3949 l_tab := 'IGS_AD_REL_CON_INT_ALL';
3950 INSERT INTO IGS_AD_IMP_STATS
3951 (
3952 INTERFACE_RUN_ID,
3953 SRC_CAT_CODE,
3954 ENTITY_NAME,
3955 TOTAL_REC_NUM,
3956 TOTAL_WARN_NUM,
3957 TOTAL_SUCCESS_NUM,
3958 TOTAL_ERROR_NUM,
3959 CREATED_BY,
3960 CREATION_DATE,
3961 LAST_UPDATED_BY,
3962 LAST_UPDATE_DATE
3963 )
3964 VALUES(
3965 l_interface_run_id,
3966 p_source_category,
3967 l_tab,
3968 l_total_rec,
3969 l_warning,
3970 l_success,
3971 l_error,
3972 l_user_id,
3973 l_sysdate,
3974 l_user_id,
3975 l_sysdate
3976 );
3977
3978 END IF;
3979 IF (p_source_category = 'RELATIONS_EMPLOYMENT_DETAILS') THEN
3980 FOR rec_relemp_int IN cur_relemp_int(l_interface_run_id)
3981 LOOP
3982 IF rec_relemp_int.status = '1' THEN
3983 l_success := rec_relemp_int.count1;
3984 ELSIF rec_relemp_int.status = '3' THEN
3985 l_error := rec_relemp_int.count1;
3986 ELSIF rec_relemp_int.status = '4' THEN
3987 l_warning := rec_relemp_int.count1;
3988 END IF;
3989 END LOOP;
3990
3991 IF l_success IS NULL THEN
3992 l_success := 0;
3993 END IF;
3994 IF l_error IS NULL THEN
3995 l_error := 0;
3996 END IF;
3997 IF l_warning IS NULL THEN
3998 l_warning := 0;
3999 END IF;
4000 l_total_rec := l_success + l_error + l_warning;
4001 l_tab := 'IGS_AD_RELEMP_INT_ALL';
4002 INSERT INTO IGS_AD_IMP_STATS
4003 (
4004 INTERFACE_RUN_ID,
4005 SRC_CAT_CODE,
4006 ENTITY_NAME,
4007 TOTAL_REC_NUM,
4008 TOTAL_WARN_NUM,
4009 TOTAL_SUCCESS_NUM,
4010 TOTAL_ERROR_NUM,
4011 CREATED_BY,
4012 CREATION_DATE,
4013 LAST_UPDATED_BY,
4014 LAST_UPDATE_DATE
4015 )
4016 VALUES(
4017 l_interface_run_id,
4018 p_source_category,
4019 l_tab,
4020 l_total_rec,
4021 l_warning,
4022 l_success,
4023 l_error,
4024 l_user_id,
4025 l_sysdate,
4026 l_user_id,
4027 l_sysdate
4028 );
4029
4030
4031 END IF;
4032
4033 IF (p_source_category = 'PERSON_CREDENTIALS') THEN
4034 FOR rec_cred_int IN cur_cred_int(l_interface_run_id)
4035 LOOP
4036 IF rec_cred_int.status = '1' THEN
4037 l_success := rec_cred_int.count1;
4038 ELSIF rec_cred_int.status = '3' THEN
4039 l_error := rec_cred_int.count1;
4040 ELSIF rec_cred_int.status = '4' THEN
4041 l_warning := rec_cred_int.count1;
4042 END IF;
4043 END LOOP;
4044
4045 IF l_success IS NULL THEN
4046 l_success := 0;
4047 END IF;
4048 IF l_error IS NULL THEN
4049 l_error := 0;
4050 END IF;
4051 IF l_warning IS NULL THEN
4052 l_warning := 0;
4053 END IF;
4054 l_total_rec := l_success + l_error + l_warning;
4055 l_tab := 'IGS_PE_CRED_INT';
4056 INSERT INTO IGS_AD_IMP_STATS
4057 (
4058 INTERFACE_RUN_ID,
4059 SRC_CAT_CODE,
4060 ENTITY_NAME,
4061 TOTAL_REC_NUM,
4062 TOTAL_WARN_NUM,
4063 TOTAL_SUCCESS_NUM,
4064 TOTAL_ERROR_NUM,
4065 CREATED_BY,
4066 CREATION_DATE,
4067 LAST_UPDATED_BY,
4068 LAST_UPDATE_DATE
4069 )
4070 VALUES(
4071 l_interface_run_id,
4072 p_source_category,
4073 l_tab,
4074 l_total_rec,
4075 l_warning,
4076 l_success,
4077 l_error,
4078 l_user_id,
4079 l_sysdate,
4080 l_user_id,
4081 l_sysdate
4082 );
4083
4084 END IF;
4085
4086 IF (p_source_category = 'PERSON_ACADEMIC_HISTORY') THEN
4087 FOR rec_acadhis_int IN cur_acadhis_int(l_interface_run_id)
4088 LOOP
4089 IF rec_acadhis_int.status = '1' THEN
4090 l_success := rec_acadhis_int.count1;
4091 ELSIF rec_acadhis_int.status = '3' THEN
4092 l_error := rec_acadhis_int.count1;
4093 ELSIF rec_acadhis_int.status = '4' THEN
4094 l_warning := rec_acadhis_int.count1;
4095 END IF;
4096 END LOOP;
4097
4098 IF l_success IS NULL THEN
4099 l_success := 0;
4100 END IF;
4101 IF l_error IS NULL THEN
4102 l_error := 0;
4103 END IF;
4104 IF l_warning IS NULL THEN
4105 l_warning := 0;
4106 END IF;
4107 l_total_rec := l_success + l_error + l_warning;
4108 l_tab := 'IGS_AD_ACADHIS_INT_ALL';
4109 INSERT INTO IGS_AD_IMP_STATS
4110 (
4111 INTERFACE_RUN_ID,
4112 SRC_CAT_CODE,
4113 ENTITY_NAME,
4114 TOTAL_REC_NUM,
4115 TOTAL_WARN_NUM,
4116 TOTAL_SUCCESS_NUM,
4117 TOTAL_ERROR_NUM,
4118 CREATED_BY,
4119 CREATION_DATE,
4120 LAST_UPDATED_BY,
4121 LAST_UPDATE_DATE
4122 )
4123 VALUES(
4124 l_interface_run_id,
4125 p_source_category,
4126 l_tab,
4127 l_total_rec,
4128 l_warning,
4129 l_success,
4130 l_error,
4131 l_user_id,
4132 l_sysdate,
4133 l_user_id,
4134 l_sysdate
4135 );
4136
4137 END IF;
4138
4139 IF (p_source_category = 'PRIVACY_DETAILS') THEN
4140 FOR rec_privacy_int IN cur_privacy_int(l_interface_run_id)
4141 LOOP
4142 IF rec_privacy_int.status = '1' THEN
4143 l_success := rec_privacy_int.count1;
4144 ELSIF rec_privacy_int.status = '3' THEN
4145 l_error := rec_privacy_int.count1;
4146 ELSIF rec_privacy_int.status = '4' THEN
4147 l_warning := rec_privacy_int.count1;
4148 END IF;
4149 END LOOP;
4150
4151 IF l_success IS NULL THEN
4152 l_success := 0;
4153 END IF;
4154 IF l_error IS NULL THEN
4155 l_error := 0;
4156 END IF;
4157 IF l_warning IS NULL THEN
4158 l_warning := 0;
4159 END IF;
4160 l_total_rec := l_success + l_error + l_warning;
4161 l_tab := 'IGS_PE_PRIVACY_INT';
4162 INSERT INTO IGS_AD_IMP_STATS
4163 (
4164 INTERFACE_RUN_ID,
4165 SRC_CAT_CODE,
4166 ENTITY_NAME,
4167 TOTAL_REC_NUM,
4168 TOTAL_WARN_NUM,
4169 TOTAL_SUCCESS_NUM,
4170 TOTAL_ERROR_NUM,
4171 CREATED_BY,
4172 CREATION_DATE,
4173 LAST_UPDATED_BY,
4174 LAST_UPDATE_DATE
4175 )
4176 VALUES(
4177 l_interface_run_id,
4178 p_source_category,
4179 l_tab,
4180 l_total_rec,
4181 l_warning,
4182 l_success,
4183 l_error,
4184 l_user_id,
4185 l_sysdate,
4186 l_user_id,
4187 l_sysdate
4188 );
4189
4190 END IF;
4191
4192 END pe_cat_stats;
4193
4194 PROCEDURE validate_ucas_id(p_api_id IN VARCHAR2,
4195 p_person_id IN NUMBER,
4196 p_api_type IN VARCHAR2,
4197 p_action OUT NOCOPY VARCHAR2,
4198 p_error_code OUT NOCOPY VARCHAR2)
4199 /****************************************************************
4200 || Created By : nsidana
4201 || Created On : 6/23/2004
4202 || Purpose : To validate if the UCAS ID need to be processed.
4203 || Known limitations, enhancements or remarks :
4204 || Change History :
4205 || Who When What
4206 || (reverse chronological order - newest change first)
4207 || gmaheswa 25-jan-05 Bug: 3882788 Removed the truncate caluse for sysdate inoder to process only active records
4208 ****************************************************************/
4209 AS
4210 CURSOR chk_any_ucas_active_id(cp_person_id NUMBER,cp_api_type VARCHAR2)
4211 IS
4212 SELECT api_person_id
4213 FROM IGS_PE_ALT_PERS_ID
4214 WHERE pe_person_id = cp_person_id
4215 AND person_id_type = cp_api_type
4216 AND SYSDATE BETWEEN TRUNC(START_DT) AND NVL(END_DT,SYSDATE);
4217
4218 l_ucas_id igs_pe_alt_pers_id.api_person_id%TYPE ;
4219 l_api_id igs_pe_alt_pers_id.api_person_id%TYPE;
4220 l_start_dt DATE;
4221 l_end_dt DATE;
4222
4223 BEGIN
4224 l_ucas_id := null;
4225 p_action :=null;
4226 p_error_code := null;
4227
4228 OPEN chk_any_ucas_active_id(p_person_id,p_api_type);
4229 FETCH chk_any_ucas_active_id INTO l_ucas_id;
4230 CLOSE chk_any_ucas_active_id;
4231
4232 IF (l_ucas_id IS NULL)
4233 THEN
4234 -- No active UCAS ID exists, process this interface record.
4235 p_action := 'P';
4236 p_error_code := null;
4237 ELSIF (l_ucas_id IS NOT NULL)
4238 THEN
4239 IF (l_ucas_id = p_api_id)
4240 THEN
4241 -- Skip this record as the record in the interface is same as the one in the actual table and is the active one.
4242 p_action := 'S';
4243 p_error_code := null;
4244 ELSE
4245 -- Error out this record as another active UCAS ID is present in the system.
4246 p_action := 'E';
4247 p_error_code := 'E560';
4248 END IF;
4249 END IF;
4250 END validate_ucas_id;
4251
4252 -- change for country code inconsistency bug 3738488
4253
4254 FUNCTION validate_country_code(p_country_code IN VARCHAR2)
4255 RETURN BOOLEAN
4256 /****************************************************************
4257 || Created By : prbhardw
4258 || Created On : 11/04/2006
4259 || Purpose : To validate if the country code is a valid ISO country.
4260 || Known limitations, enhancements or remarks :
4261 || Change History :
4262 || Who When What
4263 ****************************************************************/
4264 AS
4265 CURSOR chk_cntry_code(cp_country_code VARCHAR2)
4266 IS
4267 SELECT territory_short_name
4268 FROM fnd_territories_vl
4269 WHERE territory_code = cp_country_code;
4270
4271 l_country_name fnd_territories_vl.territory_short_name%TYPE ;
4272
4273 BEGIN
4274 l_country_name := NULL;
4275
4276 OPEN chk_cntry_code(p_country_code);
4277 FETCH chk_cntry_code INTO l_country_name;
4278 CLOSE chk_cntry_code;
4279
4280 IF (l_country_name IS NULL)
4281 THEN
4282 RETURN FALSE;
4283 ELSE
4284 RETURN TRUE;
4285 END IF;
4286 END validate_country_code;
4287
4288 END igs_pe_pers_imp_001;
[Home] [Help]