[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_026
Source
1 PACKAGE BODY IGS_AD_IMP_026 AS
2 /* $Header: IGSPE13B.pls 120.4 2006/04/27 07:42:37 prbhardw ship $ */
3
4 /*
5 || Created By : gmuralid
6 || Date : 2-DEC-2002
7 || Build : SEVIS
8 || Bug No : 2599109
9
10 || Change History :
11 || Who When What
12 || npalanis 6-JAN-2003 Bug : 2734697
13 || code added to commit after import of every
14 || 100 records .New variable l_processed_records added
15 || pkpatel 24-FEB-2003 Bug : 2783882
16 || Modified the code for implementing the overlap chack from TBH fpr visa and visit histry
17 ||
18 ||kumma 03-MAY-2003 2941138, Modified dynamic query variable l_select_clause inside function validate_record for sqlbind bug of PKM_ISSUE
19 || ssaleem 7-OCT-2003 Bug : 3130316
20 || Validations done for individual records inside the main loop are removed
21 || Instead they are done for of bulk records before the start of main loop
22 || Logging is modified to include logging mechanism
23 || ssaleem 25 Aug 2004 Moving the validate_record function in visa, passport and visit histry outside to the package level
24 || Added new procedures validate_visa_pub,validate_passport_pub and visit histry pub that will be called by the Visa, Passport and Visit Histry Public APIs.
25 || Changes as part of Bug # 3847525
26 || vredkar 14-Oct-2005 Bug#4654248,replaced generic duplicate/overlap
27 || exists messages with component specific messages
28 || skpandey 3_FEB-2006 Bug: 4937960
29 || Description: Change call from GET_WHERE_CLAUSE to GET_WHERE_CLAUSE_API as a part of Literal fix
30 */
31
32
33 CURSOR visa_dtls(cp_interface_run_id igs_pe_visa_int.interface_run_id%TYPE) IS
34 SELECT vi.*, i.person_id
35 FROM igs_pe_visa_int vi,
36 igs_ad_interface_all i
37 WHERE vi.interface_id = i.interface_id
38 AND vi.STATUS = '2'
39 AND vi.interface_run_id = cp_interface_run_id
40 AND i.interface_run_id = cp_interface_run_id;
41
42
43 CURSOR visit_dtls(cp_vh_status_2 igs_pe_vst_hist_int.status%TYPE,
44 cp_vi_status_1 igs_pe_visa_int.status%TYPE,
45 cp_interface_run_id igs_pe_vst_hist_int.interface_run_id%TYPE) IS
46 SELECT vh.*, i.person_id,pev.visa_id,pev.visa_issue_date issue_date,pev.visa_expiry_date expiry_date
47 FROM igs_pe_vst_hist_int vh,
48 igs_ad_interface_all i,
49 igs_pe_visa_int vi,
50 igs_pe_visa pev
51 WHERE vh.interface_visa_id = vi.interface_visa_id
52 AND vi.interface_id = i.interface_id
53 AND pev.person_id = i.person_id
54 AND vh.STATUS = cp_vh_status_2
55 AND vi.status = cp_vi_status_1
56 AND vh.interface_run_id = cp_interface_run_id
57 AND pev.visa_type = UPPER(vi.visa_type)
58 AND pev.visa_issue_date = TRUNC(vi.visa_issue_date);
59
60 CURSOR pass_dtls(cp_interface_run_id igs_pe_passport_int.interface_run_id%TYPE) IS
61 SELECT pi.*, i.person_id
62 FROM igs_pe_passport_int pi,
63 igs_ad_interface_all i
64 WHERE pi.interface_id = i.interface_id
65 AND pi.STATUS = '2'
66 AND pi.interface_run_id = cp_interface_run_id
67 AND i.interface_run_id = cp_interface_run_id;
68
69 FUNCTION validate_visa(visa_rec IN visa_dtls%ROWTYPE,
70 p_error_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE,
71 p_mode IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN IS
72
73 CURSOR birth_dt_cur(cp_person_id igs_ad_interface.person_id%TYPE) IS
74 SELECT birth_date birth_dt
75 FROM igs_pe_person_base_v
76 WHERE person_id = cp_person_id;
77
78 --kumma, 2941138, PKM_ISSUE, Used the bind variable instead of using the conact for preparing the statement
79 l_select_clause VARCHAR2(2000):=
80 ' SELECT ou1.org_unit_cd FROM igs_or_unit ou1,igs_or_status org_status WHERE org_status.s_org_status = ''ACTIVE''
81 AND org_status.org_status = ou1.org_status AND ou1.org_unit_cd = :agent_org_unit_cd';
82
83 TYPE org_unit_ref_cur IS REF CURSOR;
84 org_unit_cur org_unit_ref_cur;
85 l_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
86
87 CURSOR party_id_cur IS
88 SELECT person_id
89 FROM igs_pe_person_base_v
90 WHERE person_id = visa_rec.AGENT_PERSON_ID ;
91
92 CURSOR valid_pas_id IS
93 SELECT passport_number
94 FROM igs_pe_passport p
95 WHERE p.person_id = visa_rec.person_id AND
96 p.passport_id = visa_rec.passport_id;
97
98 CURSOR visa_issue_match_cur(cp_lookup_type igs_lookup_values.lookup_type%TYPE,
99 cp_enabled_flag igs_lookup_values.enabled_flag%TYPE,
100 cp_visa_issuing_post igs_lookup_values.lookup_code%TYPE,
101 cp_visa_issuing_country igs_lookup_values.tag%TYPE) IS
102 SELECT 'X'
103 FROM igs_lookup_values
104 WHERE lookup_type = cp_lookup_type AND
105 lookup_code = cp_visa_issuing_post AND
106 tag = cp_visa_issuing_country AND
107 enabled_flag = cp_enabled_flag;
108
109 pas_id_rec valid_pas_id%ROWTYPE;
110 party_id_rec party_id_cur%ROWTYPE;
111
112 l_error VARCHAR2(30);
113 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
114 l_cnt NUMBER;
115 l_where_clause VARCHAR2(2000);
116 l_exists VARCHAR2(1);
117
118 l_enable_log VARCHAR2(1);
119 l_prog_label VARCHAR2(100);
120
121 l_request_id NUMBER;
122 l_label VARCHAR2(100);
123 l_debug_str VARCHAR2(2000);
124 l_func_name VARCHAR2(10) := 'IGSEN027';
125
126 BEGIN
127 --VALIDATE VISA ISSUE POST
128 l_error := NULL;
129
130 l_enable_log := igs_ad_imp_001.g_enable_log;
131 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visa';
132
133 IF visa_rec.visa_issuing_post IS NOT NULL THEN
134
135 IF NOT igs_pe_pers_imp_001.validate_lookup_type_code('PE_US_VISA_ISSUE_LOC',visa_rec.visa_issuing_post,8405) THEN
136 l_error := 'E190';
137 RAISE NO_DATA_FOUND;
138 END IF;
139 END IF;
140
141 -- VALIDATE VISA TYPE
142
143 IF NOT igs_pe_pers_imp_001.validate_lookup_type_code('PER_US_VISA_TYPES',visa_rec.visa_type,3) THEN
144 l_error := 'E191';
145 RAISE no_data_found;
146 END IF;
147
148 IF visa_rec.visa_issue_date > visa_rec.visa_expiry_date THEN
149 l_error := 'E194';
150 RAISE no_data_found;
151 END IF;
152
153 OPEN birth_dt_cur(visa_rec.person_id);
154 FETCH birth_dt_cur INTO l_birth_date;
155 IF l_birth_date IS NOT NULL THEN
156 IF (visa_rec.visa_issue_date < l_birth_date) THEN
157 l_error := 'E195';
158 RAISE no_data_found;
159 END IF;
160 END IF;
161 CLOSE birth_dt_cur;
162
163 IF visa_rec.passport_id IS NOT NULL THEN
164 OPEN valid_pas_id;
165 FETCH valid_pas_id INTO pas_id_rec;
166 IF valid_pas_id%NOTFOUND THEN
167 l_error := 'E196';
168 RAISE no_data_found;
169 END IF;
170 CLOSE valid_pas_id;
171 END IF;
172
173 IF visa_rec.agent_org_unit_cd IS NOT NULL THEN
174 IGS_OR_GEN_012_PKG.GET_WHERE_CLAUSE_API ('IGSEN027', l_where_clause);
175 IF l_where_clause IS NOT NULL THEN
176 l_select_clause := l_select_clause||' AND '||l_where_clause;
177 --skpandey, 3-FEB-2006, Bug: 4937960: Added logic and additional parameter in using CLAUSE as a part of Literal fix
178 OPEN org_unit_cur FOR l_select_clause USING visa_rec.agent_org_unit_cd, l_func_name;
179 ELSE
180 OPEN org_unit_cur FOR l_select_clause USING visa_rec.agent_org_unit_cd;
181 END IF;
182
183 FETCH org_unit_cur INTO l_org_unit_cd;
184 IF org_unit_cur%NOTFOUND THEN
185 l_error := 'E197';
186 RAISE no_data_found;
187 END IF;
188 CLOSE org_unit_cur;
189 END IF;
190
191 IF visa_rec.agent_person_id IS NOT NULL THEN
192 OPEN party_id_cur;
193 FETCH party_id_cur INTO party_id_rec;
194 IF party_id_cur%NOTFOUND THEN
195 l_error := 'E198';
196 RAISE no_data_found;
197 END IF;
198 CLOSE party_id_cur;
199 END IF;
200
201 IF visa_rec.visa_issuing_country IS NOT NULL THEN
202 IF visa_rec.visa_issuing_country <> 'US' THEN
203 IF NOT (igs_pe_pers_imp_001.validate_country_code(visa_rec.visa_issuing_country)) -- change for country code inconsistency bug 3738488
204 THEN
205 l_error := 'E554';
206 RAISE NO_DATA_FOUND;
207 END IF;
208 END IF;
209 END IF;
210
211 IF visa_rec.visa_issuing_country IS NOT NULL AND visa_rec.visa_issuing_post IS NOT NULL THEN
212 OPEN visa_issue_match_cur('PE_US_VISA_ISSUE_LOC','Y',visa_rec.visa_issuing_post,visa_rec.visa_issuing_country);
213 FETCH visa_issue_match_cur INTO l_exists;
214 IF visa_issue_match_cur%NOTFOUND THEN
215 l_error := 'E555';
216 RAISE NO_DATA_FOUND;
217 END IF;
218 CLOSE visa_issue_match_cur;
219 END IF;
220
221 IF NOT igs_ad_imp_018.validate_desc_flex(
222 p_attribute_category => visa_rec.attribute_category,
223 p_attribute1 => visa_rec.attribute1 ,
224 p_attribute2 => visa_rec.attribute2 ,
225 p_attribute3 => visa_rec.attribute3 ,
226 p_attribute4 => visa_rec.attribute4 ,
227 p_attribute5 => visa_rec.attribute5 ,
228 p_attribute6 => visa_rec.attribute6 ,
229 p_attribute7 => visa_rec.attribute7 ,
230 p_attribute8 => visa_rec.attribute8 ,
231 p_attribute9 => visa_rec.attribute9 ,
232 p_attribute10 => visa_rec.attribute10 ,
233 p_attribute11 => visa_rec.attribute11 ,
234 p_attribute12 => visa_rec.attribute12 ,
235 p_attribute13 => visa_rec.attribute13 ,
236 p_attribute14 => visa_rec.attribute14 ,
237 p_attribute15 => visa_rec.attribute15 ,
238 p_attribute16 => visa_rec.attribute16 ,
239 p_attribute17 => visa_rec.attribute17 ,
240 p_attribute18 => visa_rec.attribute18 ,
241 p_attribute19 => visa_rec.attribute19 ,
242 p_attribute20 => visa_rec.attribute20 ,
243 p_desc_flex_name => 'IGS_PE_INTL_VISA_FLEX' ) THEN
244
245 l_error:='E255';
246 RAISE NO_DATA_FOUND;
247 END IF;
248
249
250 -- IF VALIDATIONS SUCCESSFUL
251 l_error := NULL;
252 p_error_code := l_error;
253
254 IF p_mode IS NULL THEN
255 UPDATE igs_pe_visa_int
256 SET status = '1',
257 error_code = l_error
258 WHERE interface_visa_id = visa_rec.interface_visa_id;
259 END IF;
260
261 RETURN TRUE;
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265
266
267 IF visa_issue_match_cur%ISOPEN THEN
268 CLOSE visa_issue_match_cur;
269 END IF;
270
271 IF birth_dt_cur%ISOPEN THEN
272 CLOSE birth_dt_cur;
273 END IF;
274
275 IF org_unit_cur%ISOPEN THEN
276 CLOSE org_unit_cur;
277 END IF;
278
279 IF party_id_cur%ISOPEN THEN
280 CLOSE party_id_cur;
281 END IF;
282
283 IF valid_pas_id%ISOPEN THEN
284 CLOSE valid_pas_id;
285 END IF;
286
287 p_error_code := l_error;
288
289 IF l_error = 'E555' THEN
290 IF p_mode IS NULL THEN
291 UPDATE igs_pe_visa_int
292 SET status = '4',
293 error_code = l_error
294 WHERE interface_visa_id = visa_rec.interface_visa_id;
295
296 -- CALL LOG DETAIL
297 IF l_enable_log = 'Y' THEN
298 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
299 END IF;
300 END IF;
301 RETURN TRUE;
302 ELSE
303 IF p_mode IS NULL THEN
304 UPDATE igs_pe_visa_int
305 SET status = '3',
306 error_code = l_error
307 WHERE interface_visa_id = visa_rec.interface_visa_id;
308
309 -- CALL LOG DETAIL
310 IF l_enable_log = 'Y' THEN
311 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
312 END IF;
313 END IF;
314 RETURN FALSE;
315 END IF;
316
317 WHEN OTHERS THEN
318 IF p_mode IS NULL THEN
319 UPDATE igs_pe_visa_int
320 SET status = '3',
321 error_code = l_error
322 WHERE interface_visa_id = visa_rec.interface_visa_id;
323 END IF;
324
325 p_error_code := l_error;
326 -- CALL LOG DETAIL
327
328 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
329
330 IF (l_request_id IS NULL) THEN
331 l_request_id := fnd_global.conc_request_id;
332 END IF;
333
334 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visa.val_exception' || l_error;
335
336 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
337 fnd_message.set_token('INTERFACE_ID',visa_rec.interface_visa_id);
338 fnd_message.set_token('ERROR_CD',l_error);
339
340 l_debug_str := fnd_message.get || ' ' || SQLERRM;
341
342 fnd_log.string_with_context( fnd_log.level_exception,
343 l_label,
344 l_debug_str, NULL,
345 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
346 END IF;
347
348 IF l_enable_log = 'Y' THEN
349 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
350 END IF;
351 RETURN FALSE;
352 END Validate_Visa;
353
354
355 PROCEDURE prc_pe_visa(
356 p_source_type_id IN NUMBER,
357 p_batch_id IN NUMBER )
358
359 AS
360 /*
361 || Created By : gmuralid - Visa Import Process
362 || Date : 2-DEC-2002
363 || Build : SEVIS
364 || Bug No : 2599109
365
366 || Change History :
367 || Who When What
368 || npalanis 5-MAR-2003 Bug No :2791137
369 || Validation added to prevent association
370 || of expired passport to visa
371 || npalanis 16-DEC-2002 Bug :2738327 removing the code l_visaid := visa_rec.interface_visa_id
372 || from crt_pe_visa procedure
373 || because the pk value should not be passed to tbh before calling the
374 || insert row
375 || pkpatel 24-FEB-2003 Bug : 2783882
376 || Modified the code for implementing the overlap chack from TBH
377 ||
378 || ssaleem 7-OCT-2003 Bug : 3130316
379 || Validations done for individual records inside the main loop are removed
380 || Instead they are done for of bulk records before the start of main loop
381 ||
385 */
382 || ssaleem 25 Aug 2004 Moving the validate_record function in prc_pe_visa procedure outside the package level
383 || Added a new procedure that will be called by the Visa Public API.
384 || Changes as part of Bug # 3847525
386
387 CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
388 cp_visa_type igs_pe_visa.visa_type%TYPE ,
389 cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
390 SELECT rowid,vi.*
391 FROM IGS_PE_VISA vi
392 WHERE person_id = cp_person_id AND
393 visa_type = cp_visa_type AND
394 visa_issue_date = cp_visa_issue_date; -- end_date IS NULL check removed
395
396 l_var VARCHAR2(1);
397 l_rule VARCHAR2(1);
398 l_count NUMBER;
399 lvcAction VARCHAR2(1);
400 l_error_code VARCHAR2(10);
401 l_status VARCHAR2(10);
402 l_dup_var BOOLEAN;
403 visa_rec visa_dtls%ROWTYPE;
404 -- The below variable will get populated during duplicate check
405 l_visa_rec chk_duplicate%ROWTYPE;
406 l_prog_label VARCHAR2(100);
407 l_label VARCHAR2(100);
408 l_debug_str VARCHAR2(2000);
409 l_enable_log VARCHAR2(1);
410 l_request_id NUMBER;
411 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
412 l_processed_records NUMBER(5) := 0;
413
414 -- VALIDATE RECORD FUNCTION
415
416
417 -- LOCAL PROCEDURE TO CREATE INTL VISA DTLS created by gmuralid
418 PROCEDURE crt_pe_visa(visa_rec IN visa_dtls%ROWTYPE)
419 AS
420
421 l_rowid ROWID := NULL;
422 l_visaid IGS_PE_VISA.VISA_ID%TYPE;
423 l_error VARCHAR2(30);
424 l_message_name VARCHAR2(30);
425 l_app VARCHAR2(50);
426
427 BEGIN
428 SAVEPOINT before_insert;
429
430 IGS_PE_VISA_PKG.INSERT_ROW(
431 X_ROWID => l_rowid,
432 X_VISA_ID => l_visaid,
433 X_PERSON_ID => visa_rec.person_id,
434 X_VISA_TYPE => visa_rec.VISA_TYPE ,
435 X_VISA_NUMBER => visa_rec.VISA_NUMBER,
436 X_VISA_ISSUE_DATE => visa_rec.VISA_ISSUE_DATE ,
437 X_VISA_EXPIRY_DATE => visa_rec.VISA_EXPIRY_DATE,
438 X_VISA_CATEGORY => visa_rec.VISA_CATEGORY ,
439 X_VISA_ISSUING_POST => visa_rec.VISA_ISSUING_POST,
440 X_PASSPORT_ID => visa_rec.PASSPORT_ID,
441 X_AGENT_ORG_UNIT_CD => visa_rec.AGENT_ORG_UNIT_CD ,
442 X_AGENT_PERSON_ID => visa_rec.AGENT_PERSON_ID ,
443 X_AGENT_CONTACT_NAME => visa_rec.AGENT_CONTACT_NAME ,
444 X_ATTRIBUTE_CATEGORY => visa_rec.ATTRIBUTE_CATEGORY ,
445 X_ATTRIBUTE1 => visa_rec.ATTRIBUTE1 ,
446 X_ATTRIBUTE2 => visa_rec.ATTRIBUTE2 ,
447 X_ATTRIBUTE3 => visa_rec.ATTRIBUTE3 ,
448 X_ATTRIBUTE4 => visa_rec.ATTRIBUTE4 ,
449 X_ATTRIBUTE5 => visa_rec.ATTRIBUTE5 ,
450 X_ATTRIBUTE6 => visa_rec.ATTRIBUTE6 ,
451 X_ATTRIBUTE7 => visa_rec.ATTRIBUTE7 ,
452 X_ATTRIBUTE8 => visa_rec.ATTRIBUTE8 ,
453 X_ATTRIBUTE9 => visa_rec.ATTRIBUTE9 ,
454 X_ATTRIBUTE10 => visa_rec.ATTRIBUTE10 ,
455 X_ATTRIBUTE11 => visa_rec.ATTRIBUTE11 ,
456 X_ATTRIBUTE12 => visa_rec.ATTRIBUTE12 ,
457 X_ATTRIBUTE13 => visa_rec.ATTRIBUTE13 ,
458 X_ATTRIBUTE14 => visa_rec.ATTRIBUTE14 ,
459 X_ATTRIBUTE15 => visa_rec.ATTRIBUTE15 ,
460 X_ATTRIBUTE16 => visa_rec.ATTRIBUTE16 ,
461 X_ATTRIBUTE17 => visa_rec.ATTRIBUTE17 ,
462 X_ATTRIBUTE18 => visa_rec.ATTRIBUTE18 ,
463 X_ATTRIBUTE19 => visa_rec.ATTRIBUTE19 ,
464 X_ATTRIBUTE20 => visa_rec.ATTRIBUTE20 ,
465 x_visa_issuing_country => visa_rec.visa_issuing_country,
466 X_MODE => 'R');
467
468 -- IF SUCCESSFUL INSERT THEN
469
470 l_error := NULL;
471 UPDATE igs_pe_visa_int
472 SET status = '1',
473 error_code = NULL
474 WHERE interface_visa_id = visa_rec.interface_visa_id;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478
479 ROLLBACK TO before_insert;
480 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
481
482 IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN
483 l_error:='E558';
484 ELSIF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
485 l_error:='E287';
486 ELSE
487 l_error := 'E322';
488 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
489
490 IF (l_request_id IS NULL) THEN
491 l_request_id := fnd_global.conc_request_id;
492 END IF;
493
497 fnd_message.set_token('INTERFACE_ID',visa_rec.interface_visa_id);
494 l_label := 'igs.plsql.igs_ad_imp_026.crt_pe_visa.exception' || l_error;
495
496 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
498 fnd_message.set_token('ERROR_CD',l_error);
499
500 l_debug_str := fnd_message.get || ' ' || SQLERRM;
501
502 fnd_log.string_with_context( fnd_log.level_exception,
503 l_label,
504 l_debug_str, NULL,
505 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
506 END IF;
507
508 END IF;
509
510 IF l_enable_log = 'Y' THEN
511 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
512 END IF;
513
514
515 UPDATE igs_pe_visa_int
516 SET status = '3',
517 error_code = l_error
518 WHERE interface_visa_id = visa_rec.interface_visa_id;
519
520 END crt_pe_visa;
521 -- START local procedure for updating existing record based on discepancy rule;
522
523 PROCEDURE upd_pe_visa( visa_rec IN visa_dtls%ROWTYPE,
524 dup_visa_rec IN chk_duplicate%ROWTYPE)
525 AS
526
527 l_error VARCHAR2(30);
528 l_status igs_pe_visa_int.status%TYPE;
529 l_exists VARCHAR2(1) := NULL;
530 l_message_name VARCHAR2(30);
531 l_app VARCHAR2(50);
532
533 CURSOR visit_histry_date(cp_visa_rec visa_dtls%ROWTYPE) IS
534 SELECT 'X'
535 FROM igs_pe_visit_histry_v
536 WHERE person_id = cp_visa_rec.person_id AND
537 visa_type = cp_visa_rec.visa_type AND
538 visa_issue_date = cp_visa_rec.visa_issue_date AND
539 visit_end_date NOT BETWEEN cp_visa_rec.visa_issue_date AND (cp_visa_rec.visa_expiry_date+30);
540
541 BEGIN
542
543 SAVEPOINT before_update;
544
545 OPEN visit_histry_date(visa_rec);
546 FETCH visit_histry_date INTO l_exists;
547 CLOSE visit_histry_date;
548
549 IF l_exists IS NOT NULL THEN
550 UPDATE igs_pe_visa_int
551 SET status = '3',
552 error_code = 'E559'
553 WHERE interface_visa_id = visa_rec.interface_visa_id;
554
555 IF l_enable_log = 'Y' THEN
556 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E559','IGS_PE_VISA_INT');
557 END IF;
558 ELSE
559 IGS_PE_VISA_PKG.UPDATE_ROW (
560 X_ROWID => dup_visa_rec.rowid,
561 X_VISA_ID => dup_visa_rec.visa_id,
562 X_PERSON_ID => NVL(visa_rec.person_id,dup_visa_rec.person_id),
563 X_VISA_TYPE => NVL(visa_rec.visa_type,dup_visa_rec.visa_type),
564 X_VISA_NUMBER => NVL(visa_rec.visa_number,dup_visa_rec.VISA_NUMBER),
565 X_VISA_ISSUE_DATE => NVL(visa_rec.VISA_ISSUE_DATE,dup_visa_rec.VISA_ISSUE_DATE),
566 X_VISA_EXPIRY_DATE => NVL(visa_rec.VISA_EXPIRY_DATE,dup_visa_rec.VISA_EXPIRY_DATE),
567 X_VISA_CATEGORY => NVL(visa_rec.VISA_CATEGORY,dup_visa_rec.VISA_CATEGORY),
568 X_VISA_ISSUING_POST => NVL(visa_rec.VISA_ISSUING_POST,dup_visa_rec.VISA_ISSUING_POST),
569 X_PASSPORT_ID => NVL(visa_rec.PASSPORT_ID,dup_visa_rec.PASSPORT_ID),
570 X_AGENT_ORG_UNIT_CD => NVL(visa_rec.AGENT_ORG_UNIT_CD,dup_visa_rec.AGENT_ORG_UNIT_CD),
571 X_AGENT_PERSON_ID => NVL(visa_rec.AGENT_PERSON_ID,dup_visa_rec.AGENT_PERSON_ID) ,
572 X_AGENT_CONTACT_NAME => NVL(visa_rec.AGENT_CONTACT_NAME,dup_visa_rec.AGENT_CONTACT_NAME) ,
573 X_ATTRIBUTE_CATEGORY => NVL(visa_rec.attribute_category,dup_visa_rec.attribute_category) ,
574 X_ATTRIBUTE1 => NVL(visa_rec.attribute1, dup_visa_rec.attribute1) ,
575 X_ATTRIBUTE2 => NVL(visa_rec.attribute2, dup_visa_rec.attribute2) ,
576 X_ATTRIBUTE3 => NVL(visa_rec.attribute3, dup_visa_rec.attribute3) ,
577 X_ATTRIBUTE4 => NVL(visa_rec.attribute4, dup_visa_rec.attribute4) ,
578 X_ATTRIBUTE5 => NVL(visa_rec.attribute5, dup_visa_rec.attribute5) ,
579 X_ATTRIBUTE6 => NVL(visa_rec.attribute6, dup_visa_rec.attribute6) ,
580 X_ATTRIBUTE7 => NVL(visa_rec.attribute7, dup_visa_rec.attribute7) ,
581 X_ATTRIBUTE8 => NVL(visa_rec.attribute8, dup_visa_rec.attribute8) ,
582 X_ATTRIBUTE9 => NVL(visa_rec.attribute9, dup_visa_rec.attribute9) ,
583 X_ATTRIBUTE10 => NVL(visa_rec.attribute10,dup_visa_rec.attribute10) ,
584 X_ATTRIBUTE11 => NVL(visa_rec.attribute11,dup_visa_rec.attribute11) ,
585 X_ATTRIBUTE12 => NVL(visa_rec.attribute12,dup_visa_rec.attribute12) ,
586 X_ATTRIBUTE13 => NVL(visa_rec.attribute13,dup_visa_rec.attribute13) ,
587 X_ATTRIBUTE14 => NVL(visa_rec.attribute14,dup_visa_rec.attribute14) ,
591 X_ATTRIBUTE18 => NVL(visa_rec.attribute18,dup_visa_rec.attribute18) ,
588 X_ATTRIBUTE15 => NVL(visa_rec.attribute15,dup_visa_rec.attribute15) ,
589 X_ATTRIBUTE16 => NVL(visa_rec.attribute16,dup_visa_rec.attribute16) ,
590 X_ATTRIBUTE17 => NVL(visa_rec.attribute17,dup_visa_rec.attribute17) ,
592 X_ATTRIBUTE19 => NVL(visa_rec.attribute19,dup_visa_rec.attribute19) ,
593 X_ATTRIBUTE20 => NVL(visa_rec.attribute20,dup_visa_rec.attribute20) ,
594 X_visa_issuing_country => NVL(visa_rec.visa_issuing_country,dup_visa_rec.visa_issuing_country) ,
595 X_MODE => 'R');
596
597 UPDATE igs_pe_visa_int
598 SET status = '1',
599 error_code = NULL,
600 match_ind = '18'
601 WHERE interface_visa_id = visa_rec.interface_visa_id;
602 END IF;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 ROLLBACK TO before_update;
607 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
608 IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN
609 UPDATE igs_pe_visa_int
610 SET status = '3',
611 error_code = 'E558'
612 WHERE interface_visa_id = visa_rec.interface_visa_id;
613
614 IF l_enable_log = 'Y' THEN
615 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E558','IGS_PE_VISA_INT');
616 END IF;
617
618 ELSIF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
619 UPDATE igs_pe_visa_int
620 SET status = '3',
621 error_code = 'E287'
622 WHERE interface_visa_id = visa_rec.interface_visa_id;
623
624 IF l_enable_log = 'Y' THEN
625 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E287','IGS_PE_VISA_INT');
626 END IF;
627
628 ELSE
629 UPDATE igs_pe_visa_int
630 SET status = '3',
631 error_code = 'E014'
632 WHERE interface_visa_id = visa_rec.interface_visa_id;
633
634 -- CALL LOG DETAIL
635
636 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
637
638 IF (l_request_id IS NULL) THEN
639 l_request_id := fnd_global.conc_request_id;
640 END IF;
641
642 l_label := 'igs.plsql.igs_ad_imp_026.upd_pe_visa.exception' || 'E014';
643
644 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
645 fnd_message.set_token('INTERFACE_ID',visa_rec.interface_visa_id);
646 fnd_message.set_token('ERROR_CD','E014');
647
648 l_debug_str := fnd_message.get || ' ' || SQLERRM;
649
650 fnd_log.string_with_context( fnd_log.level_exception,
651 l_label,
652 l_debug_str, NULL,
653 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
654 END IF;
655
656 IF l_enable_log = 'Y' THEN
657 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E014','IGS_PE_VISA_INT');
658 END IF;
659
660 END IF;
661
662 END upd_pe_visa;
663
664 --MAIN PROCEDURE BEGINS NOW
665
666 BEGIN
667
668 l_enable_log := igs_ad_imp_001.g_enable_log;
669 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visa';
670 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visa.';
671 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
672
673 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
674
675 IF (l_request_id IS NULL) THEN
676 l_request_id := fnd_global.conc_request_id;
677 END IF;
678
679 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visa.begin';
680 l_debug_str := 'IGS_AD_IMP_026.prc_pe_visa';
681
682 fnd_log.string_with_context( fnd_log.level_procedure,
683 l_label,
684 l_debug_str, NULL,
685 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
686 END IF;
687
688 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
689
690 IF l_rule = 'E' OR l_rule = 'I' THEN
691
692 UPDATE igs_pe_visa_int
693 SET status='3',
694 error_code = 'E695'
695 WHERE
696 interface_run_id=l_interface_run_id
697 AND STATUS = '2'
698 AND match_ind IS NOT NULL;
699
700 IF l_rule = 'E' THEN
701
702 UPDATE igs_pe_visa_int vi
703 SET status='1', match_ind='19'
704 WHERE interface_run_id=l_interface_run_id
705 AND STATUS = '2'
706 AND EXISTS( SELECT vs.rowid
707 FROM igs_pe_visa vs,
708 igs_ad_interface_all ad
709 WHERE ad.interface_id = vi.interface_id AND
710 ad.interface_run_id = l_interface_run_id AND
711 vs.person_id = ad.person_id AND
712 vs.visa_type = UPPER(vi.visa_type) AND
713 vs.visa_issue_date = TRUNC(vi.visa_issue_date));
714 END IF;
715
716 ELSIF l_rule = 'R' THEN
717
721 interface_run_id=l_interface_run_id
718 UPDATE igs_pe_visa_int
719 SET status = '1'
720 WHERE
722 AND status = '2'
723 AND match_ind IN ('18','19','22','23');
724
725 UPDATE igs_pe_visa_int
726 SET status = '3',
727 error_code = 'E695'
728 WHERE
729 interface_run_id=l_interface_run_id
730 AND status = '2'
731 AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
732
733 UPDATE igs_pe_visa_int vi
734 SET status='1',
735 match_ind = '23'
736 WHERE
737 interface_run_id=l_interface_run_id
738 AND status = '2'
739 AND match_ind IS NULL
740 AND EXISTS( SELECT vs.rowid
741 FROM igs_pe_visa vs,
742 igs_ad_interface_all ad
743 WHERE ad.interface_id = vi.interface_id AND
744 ad.interface_run_id = l_interface_run_id AND
745 vs.visa_type = UPPER(vi.visa_type) AND
746 UPPER(vs.visa_number) = UPPER(vi.visa_number) AND
747 vs.person_id = ad.person_id AND
748 ((UPPER(vs.agent_org_unit_cd)= UPPER(vi.agent_org_unit_cd)) OR ((vs.agent_org_unit_cd IS NULL) AND (vi.agent_org_unit_cd IS NULL))) AND
749 ((vs.agent_person_id = vi.agent_person_id) OR ((vs.agent_person_id IS NULL) AND (vi.agent_person_id IS NULL))) AND
750 ((UPPER(vs.agent_contact_name) = UPPER(vi.agent_contact_name)) OR ((vs.agent_contact_name IS NULL) AND (vi.agent_contact_name IS NULL))) AND
751 vs.visa_issue_date = TRUNC(vi.visa_issue_date) AND
752 TRUNC(vs.visa_expiry_date) = TRUNC(vi.visa_expiry_date) AND
753 ((vs.passport_id = vi.passport_id) OR ((vs.passport_id IS NULL) AND (vi.passport_id IS NULL))) AND
754 ((UPPER(vs.visa_issuing_post) = UPPER(vi.visa_issuing_post)) OR ((vs.visa_issuing_post IS NULL) AND (vi.visa_issuing_post IS NULL))) AND
755 ((UPPER(vs.visa_category) = UPPER(vi.visa_category)) OR ((vs.visa_category IS NULL) AND ( vi.visa_category is NULL))) AND
756 ((UPPER(vs.attribute_category) = UPPER(vi.attribute_category)) OR ((vs.attribute_category IS NULL) AND (vi.attribute_category IS NULL))) AND
757 ((UPPER(vs.attribute1) = UPPER(vi.attribute1)) OR ((vs.attribute1 IS NULL) AND (vi.attribute1 IS NULL))) AND
758 ((UPPER(vs.attribute2) = UPPER(vi.attribute2)) OR ((vs.attribute2 IS NULL) AND (vi.attribute2 IS NULL))) AND
759 ((UPPER(vs.attribute3) = UPPER(vi.attribute3)) OR ((vs.attribute3 IS NULL) AND (vi.attribute3 IS NULL))) AND
760 ((UPPER(vs.attribute4) = UPPER(vi.attribute4)) OR ((vs.attribute4 IS NULL) AND (vi.attribute4 IS NULL))) AND
761 ((UPPER(vs.attribute5) = UPPER(vi.attribute5)) OR ((vs.attribute5 IS NULL) AND (vi.attribute5 IS NULL))) AND
762 ((UPPER(vs.attribute6) = UPPER(vi.attribute6)) OR ((vs.attribute6 IS NULL) AND (vi.attribute6 IS NULL))) AND
763 ((UPPER(vs.attribute7) = UPPER(vi.attribute7)) OR ((vs.attribute7 IS NULL) AND (vi.attribute7 IS NULL))) AND
764 ((UPPER(vs.attribute8) = UPPER(vi.attribute8)) OR ((vs.attribute8 IS NULL) AND (vi.attribute8 IS NULL))) AND
765 ((UPPER(vs.attribute9) = UPPER(vi.attribute9)) OR ((vs.attribute9 IS NULL) AND (vi.attribute9 IS NULL))) AND
766 ((UPPER(vs.attribute10) = UPPER(vi.attribute10)) OR ((vs.attribute10 IS NULL) AND (vi.attribute10 IS NULL))) AND
767 ((UPPER(vs.attribute11) = UPPER(vi.attribute11)) OR ((vs.attribute11 IS NULL) AND (vi.attribute11 IS NULL))) AND
768 ((UPPER(vs.attribute12) = UPPER(vi.attribute12)) OR ((vs.attribute12 IS NULL) AND (vi.attribute12 IS NULL))) AND
769 ((UPPER(vs.attribute13) = UPPER(vi.attribute13)) OR ((vs.attribute13 IS NULL) AND (vi.attribute13 IS NULL))) AND
770 ((UPPER(vs.attribute14) = UPPER(vi.attribute14)) OR ((vs.attribute14 IS NULL) AND (vi.attribute14 IS NULL))) AND
771 ((UPPER(vs.attribute15) = UPPER(vi.attribute15)) OR ((vs.attribute15 IS NULL) AND (vi.attribute15 IS NULL))) AND
772 ((UPPER(vs.attribute16) = UPPER(vi.attribute16)) OR ((vs.attribute16 IS NULL) AND (vi.attribute16 IS NULL))) AND
773 ((UPPER(vs.attribute17) = UPPER(vi.attribute17)) OR ((vs.attribute17 IS NULL) AND (vi.attribute17 IS NULL))) AND
774 ((UPPER(vs.attribute18) = UPPER(vi.attribute18)) OR ((vs.attribute18 IS NULL) AND (vi.attribute18 IS NULL))) AND
775 ((UPPER(vs.attribute19) = UPPER(vi.attribute19)) OR ((vs.attribute19 IS NULL) AND (vi.attribute19 IS NULL))) AND
776 ((UPPER(vs.attribute20) = UPPER(vi.attribute20)) OR ((vs.attribute20 IS NULL) AND (vi.attribute20 IS NULL))) AND
777 ((UPPER(vs.visa_issuing_country) = UPPER(vi.visa_issuing_country)) OR ((vs.visa_issuing_country IS NULL) AND (vi.visa_issuing_country IS NULL))));
778
779 UPDATE igs_pe_visa_int vi
780 SET status = '3',
781 match_ind='20',
782 dup_visa_id = (SELECT visa_id
783 FROM igs_pe_visa vs,
784 igs_ad_interface_all ad
785 WHERE ad.interface_id = vi.interface_id AND
786 ad.interface_run_id = l_interface_run_id AND
787 vs.person_id = ad.person_id AND
788 vs.visa_type = UPPER(vi.visa_type) AND
789 vs.visa_issue_date = TRUNC(vi.visa_issue_date) )
793 match_ind IS NULL AND
790 WHERE
791 interface_run_id=l_interface_run_id AND
792 status = '2' AND
794 EXISTS (SELECT vs.rowid
795 FROM igs_pe_visa vs,
796 igs_ad_interface_all ad
797 WHERE ad.interface_id = vi.interface_id AND
798 ad.interface_run_id = l_interface_run_id AND
799 vs.person_id = ad.person_id AND
800 vs.visa_type = UPPER(vi.visa_type) AND
801 vs.visa_issue_date = TRUNC(vi.visa_issue_date));
802 END IF;
803
804 FOR visa_rec IN visa_dtls(l_interface_run_id) LOOP
805
806 l_processed_records := l_processed_records + 1;
807
808 -- user uppers truncs
809 visa_rec.visa_issuing_post := UPPER(visa_rec.visa_issuing_post);
810 visa_rec.VISA_TYPE := UPPER(visa_rec.VISA_TYPE);
811 visa_rec.visa_issuing_country := UPPER(visa_rec.visa_issuing_country);
812 visa_rec.visa_issue_date := TRUNC(visa_rec.visa_issue_date);
813 visa_rec.visa_expiry_date := TRUNC(visa_rec.visa_expiry_date);
814
815 IF validate_visa(visa_rec,l_error_code) THEN
816
817 l_visa_rec.visa_id := NULL;
818 OPEN chk_duplicate(visa_rec.person_id,visa_rec.visa_type,visa_rec.visa_issue_date);
819 FETCH chk_duplicate INTO l_visa_rec;
820 CLOSE chk_duplicate;
821
822 IF l_visa_rec.visa_id IS NOT NULL THEN
823 l_dup_var := TRUE;
824 END IF;
825
826 IF l_dup_var THEN
827
828 -- IF DUPLICATE RECORDS FOUND THEN FOLLOW DISCREPANCY RULE,GMURALD
829
830 IF l_rule = 'I' THEN
831 upd_pe_visa( visa_rec => visa_rec, dup_visa_rec => l_visa_rec);
832 ELSIF l_rule = 'R' THEN -- MATCH REVIEWED TO BE IMPORTED
833 IF visa_rec.match_ind = '21' THEN
834 upd_pe_visa( visa_rec => visa_rec, dup_visa_rec => l_visa_rec);
835 END IF;
836 END IF;
837 ELSE
838 crt_pe_visa(visa_rec => visa_rec) ;
839 END IF;
840 END IF;
841
842 IF l_error_code = 'E555' THEN
843 UPDATE igs_pe_visa_int
844 SET status = '4',
845 error_code = l_error_code
846 WHERE interface_visa_id = visa_rec.interface_visa_id;
847
848 -- CALL LOG DETAIL
849
850 IF l_enable_log = 'Y' THEN
851 igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error_code,'IGS_PE_VISA_INT');
852 END IF;
853 END IF;
854
855 IF l_processed_records = 100 THEN
856 COMMIT;
857 l_processed_records := 0;
858 END IF;
859
860 END LOOP;
861 END prc_pe_visa;
862
863 FUNCTION validate_visa_pub(api_visa_rec IGS_PE_VISAPASS_PUB.visa_rec_type,
864 p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
865
866 l_visa_rec visa_dtls%ROWTYPE;
867 l_return_value BOOLEAN;
868
869 BEGIN
870
871 l_visa_rec.person_id := api_visa_rec.person_id;
872 l_visa_rec.visa_type := api_visa_rec.visa_type;
873 l_visa_rec.visa_number := api_visa_rec.visa_number;
874 l_visa_rec.visa_issue_date := api_visa_rec.visa_issue_date;
875 l_visa_rec.visa_expiry_date := api_visa_rec.visa_expiry_date;
876
877 l_visa_rec.agent_org_unit_cd := api_visa_rec.agent_org_unit_cd;
878 l_visa_rec.agent_person_id := api_visa_rec.agent_person_id;
879 l_visa_rec.agent_contact_name := api_visa_rec.agent_contact_name;
880 l_visa_rec.visa_issuing_post := api_visa_rec.visa_issuing_post;
881 l_visa_rec.passport_id := api_visa_rec.passport_id;
882 l_visa_rec.visa_issuing_country := api_visa_rec.visa_issuing_country;
883
884 l_visa_rec.attribute_category := api_visa_rec.attribute_category;
885 l_visa_rec.attribute1 := api_visa_rec.attribute1;
886 l_visa_rec.attribute2 := api_visa_rec.attribute2;
887 l_visa_rec.attribute3 := api_visa_rec.attribute3;
888 l_visa_rec.attribute4 := api_visa_rec.attribute4;
889 l_visa_rec.attribute5 := api_visa_rec.attribute5;
890 l_visa_rec.attribute6 := api_visa_rec.attribute6;
891 l_visa_rec.attribute7 := api_visa_rec.attribute7;
892 l_visa_rec.attribute8 := api_visa_rec.attribute8;
893 l_visa_rec.attribute9 := api_visa_rec.attribute9;
894 l_visa_rec.attribute10 := api_visa_rec.attribute10;
895 l_visa_rec.attribute11 := api_visa_rec.attribute11;
896 l_visa_rec.attribute12 := api_visa_rec.attribute12;
897 l_visa_rec.attribute13 := api_visa_rec.attribute13;
898 l_visa_rec.attribute14 := api_visa_rec.attribute14;
899 l_visa_rec.attribute15 := api_visa_rec.attribute15;
900 l_visa_rec.attribute16 := api_visa_rec.attribute16;
901 l_visa_rec.attribute17 := api_visa_rec.attribute17;
902 l_visa_rec.attribute18 := api_visa_rec.attribute18;
903 l_visa_rec.attribute19 := api_visa_rec.attribute19;
904 l_visa_rec.attribute20 := api_visa_rec.attribute20;
905
906 l_return_value := validate_visa(visa_rec => l_visa_rec,
907 p_error_code => p_err_code,
908 p_mode => 'PUB');
909
910 return l_return_value;
911
912 END validate_visa_pub;
913
914 FUNCTION Validate_Passport(pass_rec IN pass_dtls%ROWTYPE, p_err_code OUT NOCOPY VARCHAR2, p_mode IN VARCHAR2 DEFAULT NULL ) RETURN BOOLEAN IS
915
916 CURSOR birth_dt_cur(cp_person_id igs_ad_interface.person_id%TYPE) IS
920
917 SELECT BIRTH_DATE Birth_dt
918 FROM IGS_PE_PERSON_BASE_V
919 WHERE person_id = cp_person_id;
921 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
922
923 l_enable_log VARCHAR2(1);
924 l_prog_label VARCHAR2(100);
925
926 l_request_id NUMBER;
927 l_label VARCHAR2(100);
928 l_debug_str VARCHAR2(2000);
929
930 BEGIN
931 --BEGIN OF VALIDATE RECORD FUNCTION
932 -- start validations
933
934 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport';
935 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.';
936 l_enable_log := igs_ad_imp_001.g_enable_log;
937
938 p_err_code := NULL;
939
940 --IF NOT igs_pe_pers_imp_001.validate_lookup_type_code('PER_US_COUNTRY_CODE',pass_rec.passport_cntry_code,3) THEN
941 IF NOT (igs_pe_pers_imp_001.validate_country_code(pass_rec.passport_cntry_code)) -- change for country code inconsistency bug 3738488
942 THEN
943 p_err_code := 'E553';
944 RAISE no_data_found;
945 END IF;
946
947 OPEN birth_dt_cur(pass_rec.person_id);
948 FETCH birth_dt_cur INTO l_birth_date;
949 IF l_birth_date IS NOT NULL THEN
950 IF pass_rec.passport_expiry_date < l_birth_date THEN
951 p_err_code := 'E556';
952 RAISE no_data_found;
953 END IF;
954 END IF;
955 CLOSE birth_dt_cur;
956
957 --ALL VALIDATIONS ARE OK
958
959 p_err_code := NULL;
960
961 IF p_mode IS NULL THEN
962 UPDATE igs_pe_passport_int
963 SET status = '1',
964 error_code = p_err_code
965 WHERE interface_passport_id = pass_rec.interface_passport_id;
966 END IF;
967
968 RETURN TRUE;
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972
973 IF birth_dt_cur%ISOPEN THEN
974 CLOSE birth_dt_cur;
975 END IF;
976
977 IF p_mode IS NULL THEN
978 UPDATE igs_pe_passport_int
979 SET status = '3',
980 error_code = p_err_code
981 WHERE interface_passport_id = pass_rec.interface_passport_id;
982
983 IF l_enable_log = 'Y' THEN
984 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');
985 END IF;
986 END IF;
987
988 RETURN FALSE;
989 WHEN OTHERS THEN
990
991 IF p_mode IS NULL THEN
992 UPDATE igs_pe_passport_int
993 SET status = '3',
994 error_code = p_err_code
995 WHERE interface_passport_id = pass_rec.interface_passport_id;
996 END IF;
997
998 -- CALL LOG DETAIL
999
1000 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1001
1002 IF (l_request_id IS NULL) THEN
1003 l_request_id := fnd_global.conc_request_id;
1004 END IF;
1005
1006 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.val_exception' || p_err_code;
1007
1008 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1009 fnd_message.set_token('INTERFACE_ID',pass_rec.interface_passport_id);
1010 fnd_message.set_token('ERROR_CD',p_err_code);
1011
1012 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1013
1014 fnd_log.string_with_context( fnd_log.level_exception,
1015 l_label,
1016 l_debug_str, NULL,
1017 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1018 END IF;
1019
1020 IF l_enable_log = 'Y' THEN
1021 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_err_code,'IGS_PE_PASSPORT_INT');
1022 END IF;
1023
1024 RETURN FALSE;
1025 END Validate_Passport;
1026
1027 FUNCTION validate_passport_pub(api_pass_rec IGS_PE_VISAPASS_PUB.passport_rec_type,
1028 p_err_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1029
1030 l_pass_rec pass_dtls%ROWTYPE;
1031 l_return_value BOOLEAN;
1032
1033 BEGIN
1034
1035 l_pass_rec.person_id := api_pass_rec.person_id;
1036 l_pass_rec.passport_number := api_pass_rec.passport_number;
1037 l_pass_rec.passport_expiry_date := api_pass_rec.passport_expiry_date;
1038 l_pass_rec.passport_cntry_code := api_pass_rec.passport_cntry_code;
1039
1040 l_return_value := Validate_passport(pass_rec => l_pass_rec,p_err_code => p_err_code);
1041
1042 return l_return_value;
1043
1044 END validate_passport_pub;
1045
1046
1047
1048 PROCEDURE prc_pe_passport(
1049 p_source_type_id IN NUMBER,
1050 p_batch_id IN NUMBER )
1051 AS
1052 /*
1053 || Created By : gmuralid - Passport Import Process
1054 || Date : 2-DEC-2002
1055 || Build : SEVIS
1056 || Bug No : 2599109
1057
1058 || Change History :
1059 || Who When What
1060 || npalanis 6-JAN-2003 Bug : 2734697
1061 || code added to commit after import of every
1062 || 100 records .New variable l_processed_records added
1063 ||
1064 || ssaleem 8-OCT-2003 Bug no : 3130316
1065 || Performance enhancements done, validations and status
1069 CURSOR chk_duplicate(cp_person_id igs_pe_passport.person_id%TYPE,
1066 || updations done outside the main loop
1067 */
1068
1070 cp_passport_number igs_pe_passport.passport_number%TYPE ,
1071 cp_passport_cntry_code igs_pe_passport.passport_cntry_code%TYPE) IS
1072 SELECT rowid, pi.*
1073 FROM igs_pe_passport pi
1074 WHERE person_id = cp_person_id AND
1075 UPPER(passport_number) = UPPER(cp_passport_number) AND
1076 passport_cntry_code = UPPER(cp_passport_cntry_code);
1077
1078 l_var VARCHAR2(1);
1079 l_rule VARCHAR2(1);
1080 l_count NUMBER;
1081 lvcAction VARCHAR2(1);
1082 l_error_code VARCHAR2(30);
1083 l_status VARCHAR2(10);
1084 l_dup_var BOOLEAN;
1085 pass_rec pass_dtls%ROWTYPE;
1086 l_dup_id igs_pe_passport.passport_id%TYPE;
1087 l_processed_records NUMBER(5) := 0;
1088 -- l_pass_rec variable will get populated during duplicate check
1089 l_pass_rec chk_duplicate%ROWTYPE;
1090
1091
1092 l_prog_label VARCHAR2(100);
1093 l_label VARCHAR2(100);
1094 l_debug_str VARCHAR2(2000);
1095 l_enable_log VARCHAR2(1);
1096 l_request_id NUMBER;
1097 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1098
1099 PROCEDURE crt_pe_pass(pass_rec IN pass_dtls%ROWTYPE,
1100 error_code OUT NOCOPY VARCHAR2,
1101 status OUT NOCOPY VARCHAR2)
1102 AS
1103 l_rowid ROWID := NULL;
1104 l_error VARCHAR2(30);
1105 l_pass_id IGS_PE_PASSPORT.passport_id%TYPE;
1106
1107 BEGIN
1108 --CALL TO PASSPORT INSERT RECORD
1109
1110 IGS_PE_PASSPORT_PKG.INSERT_ROW(
1111 X_ROWID => l_rowid,
1112 X_PASSPORT_ID => l_pass_id ,
1113 X_PERSON_ID => pass_rec.person_id,
1114 X_PASSPORT_NUMBER => pass_rec.passport_number,
1115 X_PASSPORT_EXPIRY_DATE => pass_rec.passport_expiry_date,
1116 X_PASSPORT_CNTRY_CODE => pass_rec.passport_cntry_code ,
1117 X_ATTRIBUTE_CATEGORY => pass_rec.attribute_category ,
1118 X_ATTRIBUTE1 => pass_rec.attribute1 ,
1119 X_ATTRIBUTE2 => pass_rec.attribute2 ,
1120 X_ATTRIBUTE3 => pass_rec.attribute3 ,
1121 X_ATTRIBUTE4 => pass_rec.attribute4 ,
1122 X_ATTRIBUTE5 => pass_rec.attribute5 ,
1123 X_ATTRIBUTE6 => pass_rec.attribute6 ,
1124 X_ATTRIBUTE7 => pass_rec.attribute7 ,
1125 X_ATTRIBUTE8 => pass_rec.attribute8 ,
1126 X_ATTRIBUTE9 => pass_rec.attribute9 ,
1127 X_ATTRIBUTE10 => pass_rec.attribute10 ,
1128 X_ATTRIBUTE11 => pass_rec.attribute11 ,
1129 X_ATTRIBUTE12 => pass_rec.attribute12 ,
1130 X_ATTRIBUTE13 => pass_rec.attribute13 ,
1131 X_ATTRIBUTE14 => pass_rec.attribute14 ,
1132 X_ATTRIBUTE15 => pass_rec.attribute15 ,
1133 X_ATTRIBUTE16 => pass_rec.attribute16 ,
1134 X_ATTRIBUTE17 => pass_rec.attribute17 ,
1135 X_ATTRIBUTE18 => pass_rec.attribute18 ,
1136 X_ATTRIBUTE19 => pass_rec.attribute19 ,
1137 X_ATTRIBUTE20 => pass_rec.attribute20 ,
1138 X_MODE => 'R'
1139 );
1140
1141 -- IF SUCCESSFUL INSERT THEN
1142
1143 l_error := NULL;
1144 UPDATE igs_pe_passport_int
1145 SET status = '1',
1146 error_code = l_error
1147 WHERE interface_passport_id = pass_rec.interface_passport_id;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 l_error := 'E322';
1152
1153 UPDATE igs_pe_passport_int
1154 SET status = '3',
1155 error_code = l_error
1156 WHERE interface_passport_id = pass_rec.interface_passport_id;
1157
1158 -- CALL LOG DETAIL
1159
1160 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1161
1162 IF (l_request_id IS NULL) THEN
1163 l_request_id := fnd_global.conc_request_id;
1164 END IF;
1165
1166 l_label := 'igs.plsql.igs_ad_imp_026.crt_pe_pass.exception' || l_error;
1167
1168 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1169 fnd_message.set_token('INTERFACE_ID',pass_rec.interface_passport_id);
1170 fnd_message.set_token('ERROR_CD',l_error);
1171
1172 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1173
1174 fnd_log.string_with_context( fnd_log.level_exception,
1175 l_label,
1179
1176 l_debug_str, NULL,
1177 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1178 END IF;
1180 IF l_enable_log = 'Y' THEN
1181 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,l_error,'IGS_PE_PASSPORT_INT');
1182 END IF;
1183
1184 END crt_pe_pass;
1185
1186
1187 -- START local procedure for updating existing record based on discepancy rule;
1188
1189 PROCEDURE upd_pe_pass( pass_rec IN pass_dtls%ROWTYPE,
1190 dup_pass_rec IN chk_duplicate%ROWTYPE,
1191 p_error_code OUT NOCOPY VARCHAR2,
1192 p_status OUT NOCOPY VARCHAR2)
1193 AS
1194
1195 l_error VARCHAR2(30);
1196 l_message_name VARCHAR2(30);
1197 l_app VARCHAR2(50);
1198
1199 BEGIN
1200
1201 -- MAKE CALL TO THE TBH i.e IGS_PE_PASSPORT_PKG.UPDATE_ROW
1202 igs_pe_passport_pkg.update_row(
1203 X_ROWID => dup_pass_rec.rowid,
1204 X_PASSPORT_ID => dup_pass_rec.passport_id,
1205 X_PERSON_ID => NVL(pass_rec.person_id,dup_pass_rec.person_id),
1206 X_PASSPORT_NUMBER => NVL(pass_rec.passport_number,dup_pass_rec.passport_number),
1207 X_PASSPORT_EXPIRY_DATE => NVL(pass_rec.passport_expiry_date,dup_pass_rec.passport_expiry_date),
1208 X_PASSPORT_CNTRY_CODE => NVL(pass_rec.passport_cntry_code,dup_pass_rec.passport_cntry_code),
1209 X_ATTRIBUTE_CATEGORY => NVL(pass_rec.attribute_category,dup_pass_rec.attribute_category) ,
1210 X_ATTRIBUTE1 => NVL(pass_rec.attribute1,dup_pass_rec.attribute1),
1211 X_ATTRIBUTE2 => NVL(pass_rec.attribute2,dup_pass_rec.attribute2),
1212 X_ATTRIBUTE3 => NVL(pass_rec.attribute3,dup_pass_rec.attribute3),
1213 X_ATTRIBUTE4 => NVL(pass_rec.attribute4,dup_pass_rec.attribute4),
1214 X_ATTRIBUTE5 => NVL(pass_rec.attribute5,dup_pass_rec.attribute5),
1215 X_ATTRIBUTE6 => NVL(pass_rec.attribute6,dup_pass_rec.attribute6),
1216 X_ATTRIBUTE7 => NVL(pass_rec.attribute7,dup_pass_rec.attribute7),
1217 X_ATTRIBUTE8 => NVL(pass_rec.attribute8,dup_pass_rec.attribute8),
1218 X_ATTRIBUTE9 => NVL(pass_rec.attribute9,dup_pass_rec.attribute9),
1219 X_ATTRIBUTE10 => NVL(pass_rec.attribute10,dup_pass_rec.attribute10),
1220 X_ATTRIBUTE11 => NVL(pass_rec.attribute11,dup_pass_rec.attribute11),
1221 X_ATTRIBUTE12 => NVL(pass_rec.attribute12,dup_pass_rec.attribute12),
1222 X_ATTRIBUTE13 => NVL(pass_rec.attribute13,dup_pass_rec.attribute13),
1223 X_ATTRIBUTE14 => NVL(pass_rec.attribute14,dup_pass_rec.attribute14),
1224 X_ATTRIBUTE15 => NVL(pass_rec.attribute15,dup_pass_rec.attribute15),
1225 X_ATTRIBUTE16 => NVL(pass_rec.attribute16,dup_pass_rec.attribute16),
1226 X_ATTRIBUTE17 => NVL(pass_rec.attribute17,dup_pass_rec.attribute17),
1227 X_ATTRIBUTE18 => NVL(pass_rec.attribute18,dup_pass_rec.attribute18),
1228 X_ATTRIBUTE19 => NVL(pass_rec.attribute19,dup_pass_rec.attribute19),
1229 X_ATTRIBUTE20 => NVL(pass_rec.attribute20,dup_pass_rec.attribute20),
1230 X_MODE => 'R'
1231 );
1232
1233 -- IF SUCCESFUL UPDATE THEN
1234
1235 p_error_code := NULL;
1236 p_status := '1';
1237
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240
1241 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1242
1243 IF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
1244 p_error_code := 'E288';
1245 p_status := '3';
1246
1247 -- CALL LOG DETAIL
1248
1249 ELSE
1250 p_error_code := 'E014';
1251 p_status := '3';
1252
1253 -- CALL LOG DETAIL
1254
1255 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1256
1257 IF (l_request_id IS NULL) THEN
1258 l_request_id := fnd_global.conc_request_id;
1259 END IF;
1260
1261 l_label := 'igs.plsql.igs_ad_imp_026.upd_pe_pass.exception' || p_error_code;
1262
1263 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1264 fnd_message.set_token('INTERFACE_ID',pass_rec.interface_passport_id);
1265 fnd_message.set_token('ERROR_CD',p_error_code);
1266
1267 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1268
1269 fnd_log.string_with_context( fnd_log.level_exception,
1270 l_label,
1271 l_debug_str, NULL,
1272 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1273 END IF;
1274
1275 END IF;
1276
1277 IF l_enable_log = 'Y' THEN
1281 UPDATE igs_pe_passport_int
1278 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,p_error_code,'IGS_PE_PASSPORT_INT');
1279 END IF;
1280
1282 SET status = p_status,
1283 error_code = p_error_code
1284 WHERE interface_passport_id = pass_rec.interface_passport_id;
1285
1286 END upd_pe_pass;
1287
1288
1289 --MAIN PROCEDURE BEGINS NOW
1290 BEGIN
1291
1292 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport';
1293 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.';
1294 l_enable_log := igs_ad_imp_001.g_enable_log;
1295 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1296 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1297
1298 IF (l_request_id IS NULL) THEN
1299 l_request_id := fnd_global.conc_request_id;
1300 END IF;
1301
1302 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.begin';
1303 l_debug_str := 'IGS_AD_IMP_026.prc_pe_passport';
1304
1305 fnd_log.string_with_context( fnd_log.level_procedure,
1306 l_label,
1307 l_debug_str, NULL,
1308 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1309 END IF;
1310
1311 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
1312
1313 IF l_rule = 'E' OR l_rule = 'I' THEN
1314
1315 UPDATE igs_pe_passport_int
1316 SET status='3',
1317 error_code = 'E695'
1318 WHERE
1319 interface_run_id=l_interface_run_id
1320 AND STATUS = '2'
1321 AND match_ind IS NOT NULL;
1322
1323 IF l_rule = 'E' THEN
1324
1325 UPDATE igs_pe_passport_int pi
1326 SET status='1',
1327 match_ind='19'
1328 WHERE
1329 interface_run_id=l_interface_run_id
1330 AND STATUS = '2'
1331 AND EXISTS( SELECT ps.rowid
1332 FROM igs_pe_passport ps,
1333 igs_ad_interface_all ad
1334 WHERE ad.interface_id = pi.interface_id AND
1335 ad.interface_run_id = l_interface_run_id AND
1336 ps.person_id = ad.person_id AND
1337 ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
1338 UPPER(ps.passport_number) = UPPER(pi.passport_number));
1339 END IF;
1340
1341 ELSIF l_rule = 'R' THEN
1342
1343 UPDATE igs_pe_passport_int
1344 SET status = '1'
1345 WHERE
1346 interface_run_id=l_interface_run_id
1347 AND status = '2'
1348 AND match_ind IN ('18','19','22','23');
1349
1350 UPDATE igs_pe_passport_int
1351 SET status = '3',
1352 error_code = 'E695'
1353 WHERE
1354 interface_run_id=l_interface_run_id
1355 AND status = '2'
1356 AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
1357
1358 UPDATE igs_pe_passport_int pi
1359 SET status='1',
1360 match_ind = '23'
1361 WHERE
1362 interface_run_id=l_interface_run_id
1363 AND status = '2'
1364 AND match_ind IS NULL
1365 AND EXISTS( SELECT ps.rowid
1366 FROM igs_pe_passport ps,
1367 igs_ad_interface_all ad
1368 WHERE ad.interface_id = pi.interface_id AND
1369 ad.interface_run_id = l_interface_run_id AND
1370 ps.person_id = ad.person_id AND
1371 ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
1372 UPPER(ps.passport_number) = UPPER(pi.passport_number) AND
1373 TRUNC(ps.passport_expiry_date) = TRUNC(pi.passport_expiry_date) AND
1374 ((ps.attribute_category = pi.attribute_category) OR ((ps.attribute_category IS NULL) AND (pi.attribute_category IS NULL))) AND
1375 ((ps.attribute1 = pi.attribute1) OR ((ps.attribute1 IS NULL) AND (pi.attribute1 IS NULL))) AND
1376 ((ps.attribute2 = pi.attribute2) OR ((ps.attribute2 IS NULL) AND (pi.attribute2 IS NULL))) AND
1377 ((ps.attribute3 = pi.attribute3) OR ((ps.attribute3 IS NULL) AND (pi.attribute3 IS NULL))) AND
1378 ((ps.attribute4 = pi.attribute4) OR ((ps.attribute4 IS NULL) AND (pi.attribute4 IS NULL))) AND
1379 ((ps.attribute5 = pi.attribute5) OR ((ps.attribute5 IS NULL) AND (pi.attribute5 IS NULL))) AND
1380 ((ps.attribute6 = pi.attribute6) OR ((ps.attribute6 IS NULL) AND (pi.attribute6 IS NULL))) AND
1381 ((ps.attribute7 = pi.attribute7) OR ((ps.attribute7 IS NULL) AND (pi.attribute7 IS NULL))) AND
1382 ((ps.attribute8 = pi.attribute8) OR ((ps.attribute8 IS NULL) AND (pi.attribute8 IS NULL))) AND
1383 ((ps.attribute9 = pi.attribute9) OR ((ps.attribute9 IS NULL) AND (pi.attribute9 IS NULL))) AND
1384 ((ps.attribute10 = pi.attribute10) OR ((ps.attribute10 IS NULL) AND (pi.attribute10 IS NULL))) AND
1385 ((ps.attribute11 = pi.attribute11) OR ((ps.attribute11 IS NULL) AND (pi.attribute11 IS NULL))) AND
1389 ((ps.attribute15 = pi.attribute15) OR ((ps.attribute15 IS NULL) AND (pi.attribute15 IS NULL))) AND
1386 ((ps.attribute12 = pi.attribute12) OR ((ps.attribute12 IS NULL) AND (pi.attribute12 IS NULL))) AND
1387 ((ps.attribute13 = pi.attribute13) OR ((ps.attribute13 IS NULL) AND (pi.attribute13 IS NULL))) AND
1388 ((ps.attribute14 = pi.attribute14) OR ((ps.attribute14 IS NULL) AND (pi.attribute14 IS NULL))) AND
1390 ((ps.attribute16 = pi.attribute16) OR ((ps.attribute16 IS NULL) AND (pi.attribute16 IS NULL))) AND
1391 ((ps.attribute17 = pi.attribute17) OR ((ps.attribute17 IS NULL) AND (pi.attribute17 IS NULL))) AND
1392 ((ps.attribute18 = pi.attribute18) OR ((ps.attribute18 IS NULL) AND (pi.attribute18 IS NULL))) AND
1393 ((ps.attribute19 = pi.attribute19) OR ((ps.attribute19 IS NULL) AND (pi.attribute19 IS NULL))) AND
1394 ((ps.attribute20 = pi.attribute20) OR ((ps.attribute20 IS NULL) AND (pi.attribute20 IS NULL))));
1395
1396 UPDATE igs_pe_passport_int pi
1397 SET status = '3',
1398 match_ind='20',
1399 dup_passport_id = (SELECT passport_id
1400 FROM igs_pe_passport ps,
1401 igs_ad_interface_all ad
1402 WHERE ad.interface_id = pi.interface_id AND
1403 ad.interface_run_id = l_interface_run_id AND
1404 ps.person_id = ad.person_id AND
1405 ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
1406 UPPER(ps.passport_number) = UPPER(pi.passport_number))
1407 WHERE interface_run_id=l_interface_run_id AND
1408 status = '2' AND
1409 match_ind IS NULL AND
1410 EXISTS (SELECT ps.rowid
1411 FROM igs_pe_passport ps,
1412 igs_ad_interface_all ad
1413 WHERE ad.interface_id = pi.interface_id AND
1414 ps.person_id = ad.person_id AND
1415 ad.interface_run_id = l_interface_run_id AND
1416 ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
1417 UPPER(ps.passport_number) = UPPER(pi.passport_number));
1418 END IF;
1419
1420
1421 FOR pass_rec in pass_dtls(l_interface_run_id) LOOP
1422
1423 l_processed_records := l_processed_records + 1;
1424
1425 -- user uppers and truncs
1426 pass_rec.passport_cntry_code := UPPER(pass_rec.passport_cntry_code);
1427 pass_rec.PASSPORT_EXPIRY_DATE := TRUNC(pass_rec.PASSPORT_EXPIRY_DATE);
1428
1429 IF validate_passport(pass_rec, l_error_code) THEN
1430
1431 l_dup_var := FALSE;
1432 l_pass_rec.passport_id := NULL;
1433 OPEN chk_duplicate(pass_rec.person_id,pass_rec.passport_number,pass_rec.passport_cntry_code);
1434 FETCH chk_duplicate INTO l_pass_rec;
1435 CLOSE chk_duplicate;
1436
1437 IF l_pass_rec.passport_id IS NOT NULL THEN
1438 l_dup_var := TRUE;
1439 END IF;
1440
1441 IF l_dup_var THEN
1442
1443 -- IF DUPLICATE RECORDS FOUND THEN FOLLOW DISCREPANCY RULE,GMURALD
1444
1445 IF l_rule = 'I' THEN
1446 BEGIN
1447 upd_pe_pass( pass_rec => pass_rec,
1448 dup_pass_rec => l_pass_rec,
1449 p_error_code => l_error_code,
1450 p_status => l_status);
1451
1452 UPDATE igs_pe_passport_int
1453 SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
1454 status = l_status ,
1455 error_code = l_error_code
1456 WHERE interface_passport_id= pass_rec.interface_passport_id;
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460
1461 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1462
1463 IF (l_request_id IS NULL) THEN
1464 l_request_id := fnd_global.conc_request_id;
1465 END IF;
1466
1467 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.exception' || 'E014';
1468
1469 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1470 fnd_message.set_token('INTERFACE_ID',pass_rec.interface_passport_id);
1471 fnd_message.set_token('ERROR_CD','E014');
1472
1473 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1474
1475 fnd_log.string_with_context( fnd_log.level_exception,
1476 l_label,
1477 l_debug_str, NULL,
1478 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1479 END IF;
1480
1481 IF l_enable_log = 'Y' THEN
1482 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1483 END IF;
1484
1485 UPDATE igs_pe_passport_int
1486 SET match_ind = '18',
1487 status = '3',
1491
1488 error_code = 'E014'
1489 WHERE interface_passport_id= pass_rec.interface_passport_id;
1490 END;
1492 ELSIF l_rule = 'R' THEN -- MATCH REVIEWED TO BE IMPORTED
1493 IF pass_rec.match_ind = '21' THEN
1494 BEGIN
1495 upd_pe_pass(pass_rec => pass_rec,
1496 dup_pass_rec => l_pass_rec,
1497 p_error_code => l_error_code,
1498 p_status => l_status);
1499
1500 UPDATE igs_pe_passport_int
1501 SET status = l_status ,
1502 error_code = l_error_code
1503 WHERE interface_passport_id= pass_rec.interface_passport_id;
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507
1508 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1509
1510 IF (l_request_id IS NULL) THEN
1511 l_request_id := fnd_global.conc_request_id;
1512 END IF;
1513
1514 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_passport.exception1' || 'E014';
1515
1516 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1517 fnd_message.set_token('INTERFACE_ID',pass_rec.interface_passport_id);
1518 fnd_message.set_token('ERROR_CD','E014');
1519
1520 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1521
1522 fnd_log.string_with_context( fnd_log.level_exception,
1523 l_label,
1524 l_debug_str, NULL,
1525 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1526 END IF;
1527
1528 IF l_enable_log = 'Y' THEN
1529 igs_ad_imp_001.logerrormessage(pass_rec.interface_passport_id,'E014','IGS_PE_PASSPORT_INT');
1530 END IF;
1531
1532 UPDATE igs_pe_passport_int
1533 SET status = '3',
1534 error_code = 'E014'
1535 WHERE interface_passport_id= pass_rec.interface_passport_id;
1536 END;
1537 END IF;
1538 END IF;
1539 ELSE
1540 crt_pe_pass(pass_rec => pass_rec,
1541 error_code => l_error_code,
1542 status => l_status) ;
1543 END IF;
1544 END IF;
1545
1546 IF l_processed_records = 100 THEN
1547 COMMIT;
1548 l_processed_records := 0;
1549 END IF;
1550
1551 END LOOP;
1552
1553 END prc_pe_passport;
1554
1555 FUNCTION Validate_visit_histry(visit_rec IN visit_dtls%ROWTYPE,
1556 p_err_code OUT NOCOPY VARCHAR2,
1557 p_mode IN VARCHAR2 DEFAULT NULL )
1558 RETURN BOOLEAN IS
1559
1560 CURSOR birth_dt_cur(cp_person_id igs_ad_interface.person_id%TYPE) IS
1561 SELECT BIRTH_DATE Birth_dt
1562 FROM IGS_PE_PERSON_BASE_V
1563 WHERE
1564 person_id = cp_person_id;
1565
1566
1567 CURSOR valid_entry_date(cp_person_id igs_ad_interface.person_id%TYPE,
1568 cp_visa_id igs_pe_visa.visa_id%TYPE,
1569 cp_visit_start_date igs_pe_visa.visa_issue_date%TYPE) IS
1570 SELECT 'Y' FROM IGS_PE_VISA
1571 WHERE person_id = cp_person_id AND
1572 visa_id = cp_visa_id AND
1573 cp_visit_start_date BETWEEN visa_issue_date AND visa_expiry_date;
1574
1575 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1576
1577 l_enable_log VARCHAR2(1);
1578 l_prog_label VARCHAR2(100);
1579
1580 l_request_id NUMBER;
1581 l_label VARCHAR2(100);
1582 l_debug_str VARCHAR2(2000);
1583
1584 BEGIN
1585 --BEGIN OF VALIDATE RECORD FUNCTION
1586
1587 p_err_code := NULL;
1588
1589 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry';
1590 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.';
1591 l_enable_log := igs_ad_imp_001.g_enable_log;
1592
1593 IF NOT igs_pe_pers_imp_001.validate_lookup_type_code('PE_US_PORT_OF_ENTRY',visit_rec.port_of_entry,8405) THEN
1594 p_err_code := 'E557';
1595 RAISE no_data_found;
1596 END IF;
1597
1598
1599 IF visit_rec.visit_end_date IS NOT NULL THEN
1600 IF visit_rec.visit_end_date < visit_rec.visit_start_date THEN
1601 p_err_code := 'E561';
1602 RAISE no_data_found;
1603 END IF;
1604 END IF;
1605
1606 OPEN birth_dt_cur(visit_rec.person_id);
1607 FETCH birth_dt_cur INTO l_birth_date;
1608 IF l_birth_date IS NOT NULL THEN
1609
1610 IF visit_rec.visit_start_date < l_birth_date THEN
1611 p_err_code := 'E562';
1612 RAISE no_data_found;
1613 END IF;
1614
1615 IF visit_rec.visit_end_date IS NOT NULL THEN
1616 IF visit_rec.visit_end_date < l_birth_date THEN
1617 p_err_code := 'E563';
1618 RAISE no_data_found;
1619 END IF;
1620 END IF;
1621 END IF;
1625 IF (visit_rec.visit_start_date) BETWEEN visit_rec.issue_date AND visit_rec.expiry_date THEN
1622 CLOSE birth_dt_cur;
1623
1624
1626 NULL;
1627 ELSE
1628 p_err_code := 'E565';
1629 RAISE no_data_found;
1630 END IF;
1631
1632 IF visit_rec.visit_end_date IS NOT NULL THEN
1633 IF (visit_rec.visit_end_date) BETWEEN visit_rec.issue_date AND (visit_rec.expiry_date + 30) THEN
1634 NULL;
1635 ELSE
1636 p_err_code := 'E572';
1637 RAISE no_data_found;
1638 END IF;
1639 END IF;
1640
1641 --ALL VALIDATIONS ARE OK
1642
1643 p_err_code := NULL;
1644
1645 IF p_mode IS NULL THEN
1646 UPDATE igs_pe_vst_hist_int
1647 SET status = '1',
1648 error_code = p_err_code
1649 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1650 END IF;
1651
1652 RETURN TRUE;
1653
1654 EXCEPTION
1655 WHEN NO_DATA_FOUND THEN
1656
1657 IF birth_dt_cur%ISOPEN THEN
1658 CLOSE birth_dt_cur;
1659 END IF;
1660
1661 IF p_mode IS NULL THEN
1662 UPDATE igs_pe_vst_hist_int
1663 SET status = '3',
1664 error_code = p_err_code
1665 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1666
1667 IF l_enable_log = 'Y' THEN
1668 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,p_err_code,'IGS_PE_VST_HIST_INT');
1669 END IF;
1670 END IF;
1671
1672 RETURN FALSE;
1673 WHEN OTHERS THEN
1674 IF p_mode IS NULL THEN
1675 UPDATE igs_pe_vst_hist_int
1676 SET status = '3',
1677 error_code = p_err_code
1678 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1679 END IF;
1680
1681 -- CALL LOG DETAIL
1682
1683 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1684
1685 IF (l_request_id IS NULL) THEN
1686 l_request_id := fnd_global.conc_request_id;
1687 END IF;
1688
1689 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.val_exception' || p_err_code;
1690
1691 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1692 fnd_message.set_token('INTERFACE_ID',visit_rec.interface_visit_histry_id);
1693 fnd_message.set_token('ERROR_CD',p_err_code);
1694
1695 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1696
1697 fnd_log.string_with_context( fnd_log.level_exception,
1698 l_label,
1699 l_debug_str, NULL,
1700 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1701 END IF;
1702
1703 IF l_enable_log = 'Y' THEN
1704 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,p_err_code,'IGS_PE_VST_HIST_INT');
1705 END IF;
1706
1707 RETURN FALSE;
1708 END Validate_visit_histry;
1709
1710 FUNCTION validate_visit_histry_pub(api_visit_rec IGS_PE_VISAPASS_PUB.visit_hstry_rec_type,
1711 p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
1712
1713 l_visit_rec visit_dtls%ROWTYPE;
1714 l_return_value BOOLEAN;
1715
1716 CURSOR visit_visa_dtls(cp_visa_id igs_pe_visa.visa_id%TYPE) IS
1717 SELECT person_id,visa_issue_date issue_date,visa_expiry_date expiry_date
1718 FROM igs_pe_visa
1719 WHERE visa_id = cp_visa_id;
1720
1721 visit_visa_rec visit_visa_dtls%ROWTYPE;
1722
1723 BEGIN
1724
1725 l_visit_rec.visa_id := api_visit_rec.visa_id;
1726
1727 OPEN visit_visa_dtls(l_visit_rec.visa_id);
1728 FETCH visit_visa_dtls INTO visit_visa_rec;
1729 CLOSE visit_visa_dtls;
1730
1731 IF visit_visa_rec.issue_date IS NOT NULL THEN
1732 l_visit_rec.issue_date := visit_visa_rec.issue_date;
1733 l_visit_rec.expiry_date := visit_visa_rec.expiry_date;
1734 l_visit_rec.person_id := visit_visa_rec.person_id;
1735 ELSE
1736 fnd_message.set_name ('IGS', 'IGS_EN_INV');
1737 fnd_message.set_token('PARAM','VISA_ID');
1738 igs_ge_msg_stack.add;
1739 app_exception.raise_exception;
1740 END IF;
1741
1742 l_visit_rec.port_of_entry := api_visit_rec.port_of_entry;
1743 l_visit_rec.cntry_entry_form_num := api_visit_rec.cntry_entry_form_num;
1744
1745 l_visit_rec.visit_start_date := api_visit_rec.visit_start_date;
1746 l_visit_rec.visit_end_date := api_visit_rec.visit_end_date;
1747 l_visit_rec.remarks := api_visit_rec.remarks;
1748
1749 l_return_value := Validate_visit_histry(visit_rec => l_visit_rec,p_err_code => p_err_code,p_mode => 'PUB');
1750
1751 return l_return_value;
1752
1753 END validate_visit_histry_pub;
1754
1755
1756 PROCEDURE prc_pe_visit_histry(
1757 p_source_type_id IN NUMBER,
1758 p_batch_id IN NUMBER )
1759 AS
1760 /*
1761 || Created By : gmuralid - Visit Histry Import Process
1762 || Date : 2-DEC-2002
1763 || Build : SEVIS
1764 || Bug No : 2599109
1765
1766 || Change History :
1767 || Who When What
1768 || npalanis 6-JAN-2003 Bug : 2734697
1769 || code added to commit after import of every
1770 || 100 records .New variable l_processed_records added
1771 || pkpatel 24-FEB-2003 Bug : 2783882
1775 || updations done outside the main loop
1772 || Modified the code for implementing the overlap chack from TBH
1773 || ssaleem 8-OCT-2003 Bug no : 3130316
1774 || Performance enhancements done, validations and status
1776 || ssaleem 27-AUG-2003 Moved the Validate Record to the package level
1777 ||
1778 */
1779
1780 CURSOR chk_duplicate(cp_port_of_entry igs_pe_visit_histry.port_of_entry%TYPE,
1781 cp_cntry_entry_form_num igs_pe_visit_histry.cntry_entry_form_num%TYPE) IS
1782 SELECT rowid,vh.*
1783 FROM igs_pe_visit_histry vh
1784 WHERE port_of_entry = cp_port_of_entry AND
1785 cntry_entry_form_num = cp_cntry_entry_form_num;
1786
1787
1788 l_var VARCHAR2(1);
1789 l_rule VARCHAR2(1);
1790 l_count NUMBER;
1791 lvcAction VARCHAR2(1);
1792 l_error_code VARCHAR2(30);
1793 l_status VARCHAR2(10);
1794 l_dup_var BOOLEAN;
1795 visit_rec visit_dtls%ROWTYPE;
1796 l_dup_pe igs_pe_vst_hist_int.dup_port_of_entry%TYPE;
1797 l_dup_efn igs_pe_vst_hist_int.dup_cntry_entry_form_num%TYPE;
1798 l_processed_records NUMBER(5) := 0;
1799 -- The below variable will get populated during duplicate check
1800 l_visit_rec chk_duplicate%ROWTYPE;
1801
1802 l_prog_label VARCHAR2(100);
1803 l_label VARCHAR2(100);
1804 l_debug_str VARCHAR2(2000);
1805 l_enable_log VARCHAR2(1);
1806 l_request_id NUMBER;
1807 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1808
1809 PROCEDURE crt_pe_visit_histry(visit_rec IN visit_dtls%ROWTYPE,
1810 error_code OUT NOCOPY VARCHAR2,
1811 status OUT NOCOPY VARCHAR2)
1812 AS
1813 l_rowid ROWID := NULL;
1814 l_error VARCHAR2(30);
1815 l_count NUMBER(5);
1816 l_message_name VARCHAR2(30);
1817 l_app VARCHAR2(50);
1818
1819 BEGIN
1820
1821 SAVEPOINT before_insert;
1822
1823 igs_pe_visit_histry_pkg.insert_row(
1824 X_ROWID => l_rowid,
1825 X_PORT_OF_ENTRY => visit_rec.port_of_entry,
1826 X_CNTRY_ENTRY_FORM_NUM => visit_rec.cntry_entry_form_num ,
1827 X_VISA_ID => visit_rec.visa_id ,
1828 X_VISIT_START_DATE => visit_rec.visit_start_date ,
1829 X_VISIT_END_DATE => visit_rec.visit_end_date ,
1830 X_REMARKS => visit_rec.remarks ,
1831 X_ATTRIBUTE_CATEGORY => visit_rec.attribute_category ,
1832 X_ATTRIBUTE1 => visit_rec.attribute1 ,
1833 X_ATTRIBUTE2 => visit_rec.attribute2 ,
1834 X_ATTRIBUTE3 => visit_rec.attribute3 ,
1835 X_ATTRIBUTE4 => visit_rec.attribute4 ,
1836 X_ATTRIBUTE5 => visit_rec.attribute5 ,
1837 X_ATTRIBUTE6 => visit_rec.attribute6 ,
1838 X_ATTRIBUTE7 => visit_rec.attribute7 ,
1839 X_ATTRIBUTE8 => visit_rec.attribute8 ,
1840 X_ATTRIBUTE9 => visit_rec.attribute9 ,
1841 X_ATTRIBUTE10 => visit_rec.attribute10 ,
1842 X_ATTRIBUTE11 => visit_rec.attribute11 ,
1843 X_ATTRIBUTE12 => visit_rec.attribute12 ,
1844 X_ATTRIBUTE13 => visit_rec.attribute13 ,
1845 X_ATTRIBUTE14 => visit_rec.attribute14 ,
1846 X_ATTRIBUTE15 => visit_rec.attribute15 ,
1847 X_ATTRIBUTE16 => visit_rec.attribute16 ,
1848 X_ATTRIBUTE17 => visit_rec.attribute17 ,
1849 X_ATTRIBUTE18 => visit_rec.attribute18 ,
1850 X_ATTRIBUTE19 => visit_rec.attribute19 ,
1851 X_ATTRIBUTE20 => visit_rec.attribute20 ,
1852 X_MODE => 'R');
1853
1854 -- IF SUCCESSFUL INSERT THEN
1855
1856 l_error := NULL;
1857 UPDATE igs_pe_vst_hist_int
1858 SET status = '1',
1859 error_code = l_error
1860 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1861
1862
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 ROLLBACK TO before_insert;
1866 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1867
1868 IF l_message_name = 'IGS_PE_PORT_DATE_OVERLAP' THEN
1869 l_error:='E564';
1870 ELSE
1871 l_error := 'E322';
1872
1873 -- CALL LOG DETAIL
1874
1875 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1876
1880
1877 IF (l_request_id IS NULL) THEN
1878 l_request_id := fnd_global.conc_request_id;
1879 END IF;
1881 l_label := 'igs.plsql.igs_ad_imp_026.crt_pe_visit_histry.exception' || l_error;
1882
1883 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1884 fnd_message.set_token('INTERFACE_ID',visit_rec.interface_visit_histry_id);
1885 fnd_message.set_token('ERROR_CD',l_error);
1886
1887 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1888
1889 fnd_log.string_with_context( fnd_log.level_exception,
1890 l_label,
1891 l_debug_str, NULL,
1892 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1893 END IF;
1894
1895 END IF;
1896
1897 IF l_enable_log = 'Y' THEN
1898 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,l_error,'IGS_PE_VST_HIST_INT');
1899 END IF;
1900
1901 UPDATE igs_pe_vst_hist_int
1902 SET status = '3',
1903 error_code = l_error
1904 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1905
1906 END crt_pe_visit_histry;
1907
1908 -- START local procedure for updating existing record based on discepancy rule
1909
1910 PROCEDURE upd_pe_visit_histry( visit_rec IN visit_dtls%ROWTYPE,
1911 dup_visit_rec IN chk_duplicate%ROWTYPE,
1912 p_error_code OUT NOCOPY VARCHAR2,
1913 p_status OUT NOCOPY VARCHAR2)
1914 AS
1915
1916 l_error VARCHAR2(30);
1917 l_message_name VARCHAR2(30);
1918 l_app VARCHAR2(50);
1919 l_visit_end_date igs_pe_visit_histry.visit_end_date%TYPE;
1920
1921 BEGIN
1922
1923 SAVEPOINT before_update;
1924
1925 l_visit_end_date := NVL(visit_rec.visit_end_date,dup_visit_rec.visit_end_date);
1926
1927 IF visit_rec.visa_id <> dup_visit_rec.visa_id THEN
1928 IF visit_rec.visit_end_date IS NULL THEN
1929 l_visit_end_date := NULL;
1930 END IF;
1931 END IF;
1932
1933 igs_pe_visit_histry_pkg.update_row(
1934 X_ROWID => dup_visit_rec.rowid,
1935 X_PORT_OF_ENTRY => NVL(visit_rec.port_of_entry,dup_visit_rec.port_of_entry),
1936 X_CNTRY_ENTRY_FORM_NUM => NVL(visit_rec.cntry_entry_form_num,dup_visit_rec.cntry_entry_form_num),
1937 X_VISA_ID => NVL(visit_rec.visa_id ,dup_visit_rec.visa_id),
1938 X_VISIT_START_DATE => NVL(visit_rec.visit_start_date,dup_visit_rec.visit_start_date),
1939 X_VISIT_END_DATE => l_visit_end_date,
1940 X_REMARKS => NVL(visit_rec.remarks,dup_visit_rec.remarks),
1941 X_ATTRIBUTE_CATEGORY => NVL(visit_rec.attribute_category,dup_visit_rec.attribute_category),
1942 X_ATTRIBUTE1 => NVL(visit_rec.attribute1,dup_visit_rec.attribute1),
1943 X_ATTRIBUTE2 => NVL(visit_rec.attribute2,dup_visit_rec.attribute2),
1944 X_ATTRIBUTE3 => NVL(visit_rec.attribute3,dup_visit_rec.attribute3),
1945 X_ATTRIBUTE4 => NVL(visit_rec.attribute4,dup_visit_rec.attribute4),
1946 X_ATTRIBUTE5 => NVL(visit_rec.attribute5,dup_visit_rec.attribute5),
1947 X_ATTRIBUTE6 => NVL(visit_rec.attribute6,dup_visit_rec.attribute6),
1948 X_ATTRIBUTE7 => NVL(visit_rec.attribute7,dup_visit_rec.attribute7),
1949 X_ATTRIBUTE8 => NVL(visit_rec.attribute8,dup_visit_rec.attribute8),
1950 X_ATTRIBUTE9 => NVL(visit_rec.attribute9,dup_visit_rec.attribute9),
1951 X_ATTRIBUTE10 => NVL(visit_rec.attribute10,dup_visit_rec.attribute10),
1952 X_ATTRIBUTE11 => NVL(visit_rec.attribute11,dup_visit_rec.attribute11),
1953 X_ATTRIBUTE12 => NVL(visit_rec.attribute12,dup_visit_rec.attribute12),
1954 X_ATTRIBUTE13 => NVL(visit_rec.attribute13,dup_visit_rec.attribute13),
1955 X_ATTRIBUTE14 => NVL(visit_rec.attribute14,dup_visit_rec.attribute14),
1956 X_ATTRIBUTE15 => NVL(visit_rec.attribute15,dup_visit_rec.attribute15),
1957 X_ATTRIBUTE16 => NVL(visit_rec.attribute16,dup_visit_rec.attribute16),
1958 X_ATTRIBUTE17 => NVL(visit_rec.attribute17,dup_visit_rec.attribute17),
1959 X_ATTRIBUTE18 => NVL(visit_rec.attribute18,dup_visit_rec.attribute18),
1960 X_ATTRIBUTE19 => NVL(visit_rec.attribute19,dup_visit_rec.attribute19),
1961 X_ATTRIBUTE20 => NVL(visit_rec.attribute20,dup_visit_rec.attribute20),
1962 X_MODE => 'R');
1963
1964 p_error_code := NULL;
1968 WHEN OTHERS THEN
1965 p_status := '1';
1966
1967 EXCEPTION
1969 ROLLBACK TO before_update;
1970 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1971
1972 IF l_message_name = 'IGS_PE_PORT_DATE_OVERLAP' THEN
1973 p_error_code := 'E564';
1974 p_status := '3';
1975
1976 UPDATE igs_pe_vst_hist_int
1977 SET status = '3',
1978 error_code = 'E014'
1979 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1980
1981 ELSE
1982
1983 p_error_code := 'E014';
1984 p_status := '3';
1985
1986 UPDATE igs_pe_vst_hist_int
1987 SET status = '3',
1988 error_code = 'E014'
1989 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
1990
1991 -- CALL LOG DETAIL
1992 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1993
1994 IF (l_request_id IS NULL) THEN
1995 l_request_id := fnd_global.conc_request_id;
1996 END IF;
1997
1998 l_label := 'igs.plsql.igs_ad_imp_026.upd_pe_visit_histry.exception' || 'E014';
1999
2000 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2001 fnd_message.set_token('INTERFACE_ID',visit_rec.interface_visit_histry_id);
2002 fnd_message.set_token('ERROR_CD','E014');
2003
2004 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2005
2006 fnd_log.string_with_context( fnd_log.level_exception,
2007 l_label,
2008 l_debug_str, NULL,
2009 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2010 END IF;
2011
2012 IF l_enable_log = 'Y' THEN
2013 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,l_error,'IGS_PE_VST_HIST_INT');
2014 END IF;
2015
2016 END IF;
2017 END upd_pe_visit_histry;
2018
2019 --MAIN PROCEDURE BEGINS NOW
2020
2021 BEGIN
2022
2023 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry';
2024 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.';
2025 l_enable_log := igs_ad_imp_001.g_enable_log;
2026 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
2027
2028 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
2029
2030 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2031
2032 IF (l_request_id IS NULL) THEN
2033 l_request_id := fnd_global.conc_request_id;
2034 END IF;
2035
2036 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.begin';
2037 l_debug_str := 'IGS_AD_IMP_026.prc_pe_visit_histry';
2038
2039 fnd_log.string_with_context( fnd_log.level_procedure,
2040 l_label,
2041 l_debug_str, NULL,
2042 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2043 END IF;
2044
2045 IF l_rule = 'E' OR l_rule = 'I' THEN
2046
2047 UPDATE igs_pe_vst_hist_int
2048 SET status='3',
2049 error_code = 'E695'
2050 WHERE
2051 interface_run_id=l_interface_run_id
2052 AND STATUS = '2'
2053 AND match_ind IS NOT NULL;
2054
2055
2056 IF l_rule = 'E' THEN
2057
2058 UPDATE igs_pe_vst_hist_int vh
2059 SET status='1',
2060 match_ind='19'
2061 WHERE interface_run_id=l_interface_run_id
2062 AND STATUS = '2'
2063 AND EXISTS( SELECT vs.rowid
2064 FROM igs_pe_visit_histry vs
2065 WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
2066 UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2067 AND EXISTS( SELECT vi.rowid
2068 FROM igs_pe_visa_int vi
2069 WHERE vi.interface_visa_id = vh.interface_visa_id AND
2070 vi.status = '1');
2071 END IF;
2072
2073 ELSIF l_rule = 'R' THEN
2074
2075 UPDATE igs_pe_vst_hist_int vh
2076 SET status = '1'
2077 WHERE interface_run_id=l_interface_run_id
2078 AND status = '2'
2079 AND match_ind IN ('18','19','22','23')
2080 AND EXISTS( SELECT vi.rowid
2081 FROM igs_pe_visa_int vi
2082 WHERE vi.interface_visa_id = vh.interface_visa_id AND
2083 vi.status = '1');
2084
2085 UPDATE igs_pe_vst_hist_int vh
2086 SET status = '3',
2087 error_code = 'E695'
2088 WHERE interface_run_id=l_interface_run_id
2089 AND status = '2'
2090 AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25')
2091 AND EXISTS( SELECT vi.rowid
2095
2092 FROM igs_pe_visa_int vi
2093 WHERE vi.interface_visa_id = vh.interface_visa_id AND
2094 vi.status = '1');
2096 UPDATE igs_pe_vst_hist_int vh
2097 SET status='1',
2098 match_ind = '23'
2099 WHERE interface_run_id=l_interface_run_id
2100 AND status = '2'
2101 AND match_ind IS NULL
2102 AND EXISTS( SELECT vi.rowid
2103 FROM igs_pe_visa_int vi
2104 WHERE vi.interface_visa_id = vh.interface_visa_id AND
2105 vi.status = '1')
2106 AND EXISTS( SELECT vs.rowid
2107 FROM igs_pe_visit_histry vs ,
2108 igs_pe_visa pev
2109 WHERE vs.visa_id = pev.visa_id AND
2110 vs.port_of_entry = UPPER(vh.port_of_entry) AND
2111 UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) AND
2112 TRUNC(vs.visit_start_date) = TRUNC(vh.visit_start_date) AND
2113 ((TRUNC(vs.visit_end_date) = TRUNC(vh.visit_end_date)) OR ((vs.visit_end_date IS NULL) AND (vh.visit_end_date IS NULL))) AND
2114 ((UPPER(vs.remarks) = UPPER(vh.remarks)) OR ((vs.remarks IS NULL) AND (vh.remarks IS NULL))) AND
2115 ((vs.attribute_category = vh.attribute_category) OR ((vs.attribute_category IS NULL) AND (vh.attribute_category IS NULL))) AND
2116 ((vs.attribute1 = vh.attribute1) OR ((vs.attribute1 IS NULL) AND (vh.attribute1 IS NULL))) AND
2117 ((vs.attribute2 = vh.attribute2) OR ((vs.attribute2 IS NULL) AND (vh.attribute2 IS NULL))) AND
2118 ((vs.attribute3 = vh.attribute3) OR ((vs.attribute3 IS NULL) AND (vh.attribute3 IS NULL))) AND
2119 ((vs.attribute4 = vh.attribute4) OR ((vs.attribute4 IS NULL) AND (vh.attribute4 IS NULL))) AND
2120 ((vs.attribute5 = vh.attribute5) OR ((vs.attribute5 IS NULL) AND (vh.attribute5 IS NULL))) AND
2121 ((vs.attribute6 = vh.attribute6) OR ((vs.attribute6 IS NULL) AND (vh.attribute6 IS NULL))) AND
2122 ((vs.attribute7 = vh.attribute7) OR ((vs.attribute7 IS NULL) AND (vh.attribute7 IS NULL))) AND
2123 ((vs.attribute8 = vh.attribute8) OR ((vs.attribute8 IS NULL) AND (vh.attribute8 IS NULL))) AND
2124 ((vs.attribute9 = vh.attribute9) OR ((vs.attribute9 IS NULL) AND (vh.attribute9 IS NULL))) AND
2125 ((vs.attribute10 = vh.attribute10) OR ((vs.attribute10 IS NULL) AND (vh.attribute10 IS NULL))) AND
2126 ((vs.attribute11 = vh.attribute11) OR ((vs.attribute11 IS NULL) AND (vh.attribute11 IS NULL))) AND
2127 ((vs.attribute12 = vh.attribute12) OR ((vs.attribute12 IS NULL) AND (vh.attribute12 IS NULL))) AND
2128 ((vs.attribute13 = vh.attribute13) OR ((vs.attribute13 IS NULL) AND (vh.attribute13 IS NULL))) AND
2129 ((vs.attribute14 = vh.attribute14) OR ((vs.attribute14 IS NULL) AND (vh.attribute14 IS NULL))) AND
2130 ((vs.attribute15 = vh.attribute15) OR ((vs.attribute15 IS NULL) AND (vh.attribute15 IS NULL))) AND
2131 ((vs.attribute16 = vh.attribute16) OR ((vs.attribute16 IS NULL) AND (vh.attribute16 IS NULL))) AND
2132 ((vs.attribute17 = vh.attribute17) OR ((vs.attribute17 IS NULL) AND (vh.attribute17 IS NULL))) AND
2133 ((vs.attribute18 = vh.attribute18) OR ((vs.attribute18 IS NULL) AND (vh.attribute18 IS NULL))) AND
2134 ((vs.attribute19 = vh.attribute19) OR ((vs.attribute19 IS NULL) AND (vh.attribute19 IS NULL))) AND
2135 ((vs.attribute20 = vh.attribute20) OR ((vs.attribute20 IS NULL) AND (vh.attribute20 IS NULL))));
2136
2137 UPDATE igs_pe_vst_hist_int vh
2138 SET status = '3',
2139 match_ind='20',
2140 (dup_port_of_entry,dup_cntry_entry_form_num) = (SELECT port_of_entry,cntry_entry_form_num
2141 FROM igs_pe_visit_histry vs
2142 WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
2143 UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2144 WHERE interface_run_id=l_interface_run_id AND
2145 status = '2' AND
2146 EXISTS( SELECT vsi.rowid
2147 FROM igs_pe_visa_int vsi
2148 WHERE vsi.interface_visa_id = vh.interface_visa_id AND
2149 vsi.status = '1') AND
2150 match_ind IS NULL AND
2151 EXISTS (SELECT rowid
2152 FROM igs_pe_visit_histry
2153 WHERE port_of_entry = UPPER(vh.port_of_entry) AND
2154 UPPER(cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) );
2155 END IF;
2156
2157 FOR visit_rec in visit_dtls('2','1',l_interface_run_id) LOOP
2158
2159 l_processed_records := l_processed_records + 1;
2160
2161 visit_rec.port_of_entry := UPPER(visit_rec.port_of_entry);
2162 visit_rec.visit_end_date := TRUNC(visit_rec.visit_end_date);
2166 IF Validate_visit_histry(visit_rec => visit_rec, p_err_code => l_error_code) THEN
2163 visit_rec.visit_start_date := TRUNC(visit_rec.visit_start_date);
2164
2165
2167
2168 l_dup_var := FALSE;
2169 l_visit_rec.port_of_entry := NULL;
2170 OPEN chk_duplicate(visit_rec.port_of_entry,visit_rec.cntry_entry_form_num);
2171 FETCH chk_duplicate INTO l_visit_rec;
2172 CLOSE chk_duplicate;
2173
2174 IF l_visit_rec.port_of_entry IS NOT NULL THEN
2175 l_dup_var := TRUE;
2176 END IF;
2177
2178 IF l_dup_var THEN
2179 -- IF DUPLICATE RECORDS FOUND THEN FOLLOW DISCREPANCY RULE,GMURALD
2180 IF l_rule = 'I' THEN
2181 BEGIN
2182 upd_pe_visit_histry(visit_rec => visit_rec,
2183 dup_visit_rec => l_visit_rec,
2184 p_error_code => l_error_code,
2185 p_status => l_status );
2186
2187 UPDATE igs_pe_vst_hist_int
2188 SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
2189 status = l_status ,
2190 error_code = l_error_code
2191 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
2192
2193 EXCEPTION
2194 WHEN OTHERS THEN
2195
2196 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2197
2198 IF (l_request_id IS NULL) THEN
2199 l_request_id := fnd_global.conc_request_id;
2200 END IF;
2201
2202 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.exception' || 'E014';
2203
2204 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2205 fnd_message.set_token('INTERFACE_ID',visit_rec.interface_visit_histry_id);
2206 fnd_message.set_token('ERROR_CD','E014');
2207
2208 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2209
2210 fnd_log.string_with_context( fnd_log.level_exception,
2211 l_label,
2212 l_debug_str, NULL,
2213 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2214 END IF;
2215
2216 IF l_enable_log = 'Y' THEN
2217 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,'E014','IGS_PE_VST_HIST_INT');
2218 END IF;
2219
2220 UPDATE igs_pe_vst_hist_int
2221 SET match_ind = '18',
2222 status = '3',
2223 error_code = 'E014'
2224 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
2225 END;
2226
2227
2228 ELSIF l_rule = 'R' THEN -- MATCH REVIEWED TO BE IMPORTED
2229 IF visit_rec.match_ind = '21' THEN
2230 BEGIN
2231 upd_pe_visit_histry(visit_rec => visit_rec,
2232 dup_visit_rec => l_visit_rec,
2233 p_error_code => l_error_code,
2234 p_status => l_status);
2235
2236 UPDATE igs_pe_vst_hist_int
2237 SET status = l_status ,
2238 error_code = l_error_code
2239 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
2240
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243
2244 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2245
2246 IF (l_request_id IS NULL) THEN
2247 l_request_id := fnd_global.conc_request_id;
2248 END IF;
2249
2250 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_visit_histry.exception1' || 'E014';
2251
2252 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2253 fnd_message.set_token('INTERFACE_ID',visit_rec.interface_visit_histry_id);
2254 fnd_message.set_token('ERROR_CD','E014');
2255
2256 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2257
2258 fnd_log.string_with_context( fnd_log.level_exception,
2259 l_label,
2260 l_debug_str, NULL,
2261 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2262 END IF;
2263
2264 IF l_enable_log = 'Y' THEN
2265 igs_ad_imp_001.logerrormessage(visit_rec.interface_visit_histry_id,'E014','IGS_PE_VST_HIST_INT');
2266 END IF;
2267
2268 UPDATE igs_pe_vst_hist_int
2269 SET status = '3',
2270 error_code = 'E014'
2274 END IF;
2271 WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
2272 END;
2273 END IF;
2275 ELSE
2276 crt_pe_visit_histry(visit_rec => visit_rec,
2277 error_code => l_error_code,
2278 status => l_status) ;
2279 END IF;
2280 END IF;
2281
2282 IF l_processed_records = 100 THEN
2283 COMMIT;
2284 l_processed_records := 0;
2285 END IF;
2286
2287 END LOOP;
2288 END prc_pe_visit_histry;
2289
2290 PROCEDURE prc_pe_eit(
2291 p_source_type_id IN NUMBER,
2292 p_batch_id IN NUMBER )
2293 AS
2294 /*
2295 || Created By : gmuralid - Residence details import process
2296 || Date : 2-DEC-2002
2297 || Build : SEVIS
2298 || Bug No : 2599109
2299
2300 || Change History :
2301 || Who When What
2302 || npalanis 6-JAN-2003 Bug : 2734697
2303 || code added to commit after import of every
2304 || 100 records .New variable l_processed_records added
2305 ||
2306 || ssaleem 8-OCT-2003 Bug no : 3130316
2307 || Performance enhancements done, validations and status
2308 || updations done outside the main loop
2309 */
2310
2311
2312 CURSOR chk_duplicate(cp_person_id igs_pe_eit.person_id%TYPE,
2313 cp_information_type igs_pe_eit.information_type%TYPE,
2314 cp_start_date igs_pe_eit.start_date%TYPE)
2315 IS
2316 SELECT rowid,ei.*
2317 FROM igs_pe_eit ei
2318 WHERE person_id = cp_person_id AND
2319 UPPER(information_type) = UPPER(cp_information_type) AND
2320 TRUNC(start_date) = TRUNC(cp_start_date) ;
2321
2322 CURSOR eit_dtls(cp_ei_status_2 igs_pe_eit_int.status%TYPE,
2323 cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,
2324 cp_information_type igs_pe_eit_int.information_type%TYPE) IS
2325
2326 SELECT ei.*, i.person_id
2327 FROM igs_pe_eit_int ei,
2328 igs_ad_interface_all i
2329 WHERE ei.interface_id = i.interface_id
2330 AND ei.STATUS = cp_ei_status_2
2331 AND ei.interface_run_id = cp_interface_run_id
2332 AND i.interface_run_id = cp_interface_run_id
2333 AND ei.information_type =cp_information_type;
2334
2335 l_var VARCHAR2(1);
2336 l_rule VARCHAR2(1);
2337 l_count NUMBER;
2338 lvcAction VARCHAR2(1);
2339 l_error_code VARCHAR2(10);
2340 l_status VARCHAR2(10);
2341 l_dup_var BOOLEAN;
2342 eit_rec eit_dtls%ROWTYPE;
2343 l_dup_id igs_pe_eit.pe_eit_id%TYPE;
2344 l_processed_records NUMBER(5) := 0;
2345 l_eit_rec chk_duplicate%ROWTYPE;
2346
2347 l_prog_label VARCHAR2(100);
2348 l_label VARCHAR2(100);
2349 l_debug_str VARCHAR2(2000);
2350 l_enable_log VARCHAR2(1);
2351 l_request_id NUMBER;
2352 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2353
2354 FUNCTION validate_record(eit_rec IN eit_dtls%ROWTYPE)
2355 RETURN BOOLEAN IS
2356
2357 CURSOR birth_dt_cur(cp_person_id igs_ad_interface.person_id%TYPE) IS
2358 SELECT BIRTH_DATE Birth_dt
2359 FROM IGS_PE_PERSON_BASE_V
2360 WHERE
2361 person_id = cp_person_id;
2362
2363 l_error VARCHAR2(30);
2364 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
2365
2366 BEGIN
2367 --BEGIN OF VALIDATE RECORD FUNCTION
2368
2369 -- start validations
2370
2371 l_error := NULL;
2372
2373 IF eit_rec.pei_information1 IS NOT NULL THEN
2374 IF NOT
2375 (igs_pe_pers_imp_001.validate_country_code(eit_rec.pei_information1)) -- change for country code inconsistency bug 3738488
2376 THEN
2377 l_error := 'E566';
2378 RAISE no_data_found;
2379 END IF;
2380 END IF;
2381
2382
2383 IF eit_rec.end_date IS NOT NULL THEN
2384 IF eit_rec.start_date > eit_rec.end_date THEN
2385 l_error := 'E568';
2386 RAISE no_data_found;
2387 END IF;
2388 END IF;
2389
2390 OPEN birth_dt_cur(eit_rec.person_id);
2391 FETCH birth_dt_cur INTO l_birth_date;
2392 IF l_birth_date IS NOT NULL THEN
2393 IF eit_rec.start_date < l_birth_date THEN
2394 l_error := 'E569';
2395 RAISE no_data_found;
2396 END IF;
2397
2398 IF eit_rec.end_date IS NOT NULL THEN
2399 IF eit_rec.end_date < l_birth_date THEN
2400 l_error := 'E570';
2401 RAISE no_data_found;
2402 END IF;
2403 END IF;
2404 END IF;
2405 CLOSE birth_dt_cur;
2406
2407 --ALL VALIDATIONS ARE OK
2408
2409 l_error := NULL;
2410
2411 UPDATE igs_pe_eit_int
2412 SET status = '1',
2413 error_code = l_error
2414 WHERE interface_eit_id = eit_rec.interface_eit_id;
2415
2419 WHEN NO_DATA_FOUND THEN
2416 RETURN TRUE;
2417
2418 EXCEPTION
2420
2421 IF birth_dt_cur%ISOPEN THEN
2422 CLOSE birth_dt_cur;
2423 END IF;
2424
2425 UPDATE igs_pe_eit_int
2426 SET status = '3',
2427 error_code = l_error
2428 WHERE interface_eit_id = eit_rec.interface_eit_id;
2429
2430 IF l_enable_log = 'Y' THEN
2431 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2432 END IF;
2433
2434 RETURN FALSE;
2435
2436 WHEN OTHERS THEN
2437 UPDATE igs_pe_eit_int
2438 SET status = '3',
2439 error_code = l_error
2440 WHERE interface_eit_id = eit_rec.interface_eit_id;
2441
2442 -- CALL LOG DETAIL
2443
2444 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2445
2446 IF (l_request_id IS NULL) THEN
2447 l_request_id := fnd_global.conc_request_id;
2448 END IF;
2449
2450 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.val_exception' || l_error;
2451
2452 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2453 fnd_message.set_token('INTERFACE_ID',eit_rec.interface_eit_id);
2454 fnd_message.set_token('ERROR_CD',l_error);
2455
2456 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2457
2458 fnd_log.string_with_context( fnd_log.level_exception,
2459 l_label,
2460 l_debug_str, NULL,
2461 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2462 END IF;
2463
2464 IF l_enable_log = 'Y' THEN
2465 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2466 END IF;
2467
2468 RETURN FALSE;
2469 END Validate_Record;
2470
2471
2472 PROCEDURE crt_pe_eit( eit_rec IN eit_dtls%ROWTYPE,
2473 error_code OUT NOCOPY VARCHAR2,
2474 status OUT NOCOPY VARCHAR2)
2475 AS
2476
2477 l_rowid ROWID := NULL;
2478 l_error VARCHAR2(30);
2479 l_eit_id igs_pe_eit.pe_eit_id%TYPE;
2480 l_count NUMBER(5);
2481
2482 CURSOR date_overlap(cp_eit_rec eit_dtls%ROWTYPE,
2483 cp_end_date VARCHAR2) IS
2484 SELECT count(1) FROM IGS_PE_EIT
2485 WHERE person_id = cp_eit_rec.person_id
2486 AND INFORMATION_TYPE = cp_eit_rec.information_type
2487 AND (NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
2488 OR
2489 cp_eit_rec.start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
2490 OR
2491 ( cp_eit_rec.start_date < START_DATE AND
2492 NVL(end_date,IGS_GE_DATE.igsdate(cp_end_date))< NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) ) );
2493
2494 BEGIN
2495 --CALL TO EIT INSERT RECORD
2496
2497 OPEN date_overlap(eit_rec,'9999/01/01');
2498 FETCH date_overlap INTO l_count;
2499 CLOSE date_overlap;
2500
2501 IF l_count > 0 THEN
2502
2503 l_error := 'E571';
2504 UPDATE igs_pe_eit_int
2505 SET status = '3',
2506 error_code = l_error
2507 WHERE interface_eit_id = eit_rec.interface_eit_id;
2508
2509 -- CALL LOG DETAIL
2510
2511 IF l_enable_log = 'Y' THEN
2512 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2513 END IF;
2514
2515 ELSE
2516 igs_pe_eit_pkg.insert_row(
2517 X_ROWID => l_rowid,
2518 X_PE_EIT_ID => l_eit_id,
2519 X_PERSON_ID => eit_rec.person_id ,
2520 X_INFORMATION_TYPE => eit_rec.information_type ,
2521 X_PEI_INFORMATION1 => eit_rec.pei_information1 ,
2522 X_PEI_INFORMATION2 => eit_rec.pei_information2 ,
2523 X_PEI_INFORMATION3 => eit_rec.pei_information3 ,
2524 X_PEI_INFORMATION4 => eit_rec.pei_information4 ,
2525 X_PEI_INFORMATION5 => eit_rec.pei_information5 ,
2526 X_START_DATE => eit_rec.start_date ,
2527 X_END_DATE => eit_rec.end_date ,
2528 X_MODE => 'R'
2529 );
2530
2531 -- IF SUCCESSFUL INSERT THEN
2532
2533 l_error := NULL;
2534 UPDATE igs_pe_eit_int
2535 SET status = '1',
2536 error_code = l_error
2537 WHERE interface_eit_id = eit_rec.interface_eit_id;
2538 END IF;
2539
2540 EXCEPTION
2541 WHEN OTHERS THEN
2542 l_error := 'E322';
2546 WHERE interface_eit_id = eit_rec.interface_eit_id;
2543 UPDATE igs_pe_eit_int
2544 SET status = '3',
2545 error_code = l_error
2547
2548 -- CALL LOG DETAIL
2549
2550 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2551
2552 IF (l_request_id IS NULL) THEN
2553 l_request_id := fnd_global.conc_request_id;
2554 END IF;
2555
2556 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.crt_exception' || l_error;
2557
2558 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2559 fnd_message.set_token('INTERFACE_ID',eit_rec.interface_eit_id);
2560 fnd_message.set_token('ERROR_CD',l_error);
2561
2562 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2563
2564 fnd_log.string_with_context( fnd_log.level_exception,
2565 l_label,
2566 l_debug_str, NULL,
2567 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2568 END IF;
2569
2570 IF l_enable_log = 'Y' THEN
2571 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2572 END IF;
2573
2574 END crt_pe_eit;
2575
2576 -- START local procedure for updating existing record based on discepancy rule;
2577
2578 PROCEDURE upd_pe_eit( eit_rec IN eit_dtls%ROWTYPE,
2579 dup_eit_rec IN chk_duplicate%ROWTYPE,
2580 p_error_code OUT NOCOPY VARCHAR2,
2581 p_status OUT NOCOPY VARCHAR2)
2582 AS
2583
2584
2585 l_error VARCHAR2(30);
2586
2587 l_count NUMBER(5);
2588
2589 CURSOR date_overlap(cp_eit_rec eit_dtls%ROWTYPE,
2590 cp_end_date VARCHAR2 ) IS
2591 SELECT count(1) FROM IGS_PE_EIT
2592 WHERE person_id = cp_eit_rec.person_id
2593 AND information_type = cp_eit_rec.information_type
2594 AND start_date <> cp_eit_rec.start_date
2595 AND (NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
2596 OR
2597 cp_eit_rec.start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
2598 OR
2599 ( cp_eit_rec.start_date < START_DATE AND
2600 NVL(end_date,IGS_GE_DATE.igsdate(cp_end_date))< NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) ) );
2601
2602 BEGIN
2603 --CALL TO EIT INSERT RECORD
2604
2605 OPEN date_overlap(eit_rec,'9999/01/01');
2606 FETCH date_overlap INTO l_count;
2607 CLOSE date_overlap;
2608
2609 IF l_count > 0 THEN
2610
2611 l_error := 'E571';
2612
2613 p_error_code := l_error;
2614 p_status := '3';
2615
2616 UPDATE igs_pe_eit_int
2617 SET status = '3',
2618 error_code = l_error
2619 WHERE interface_eit_id = eit_rec.interface_eit_id;
2620
2621 -- CALL LOG DETAIL
2622
2623 IF l_enable_log = 'Y' THEN
2624 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2625 END IF;
2626
2627 ELSE
2628
2629 -- MAKE CALL TO THE TBH i.e IGS_PE_EIT_PKG.UPDATE_ROW
2630
2631 igs_pe_eit_pkg.update_row(
2632 X_ROWID => dup_eit_rec.rowid,
2633 X_PE_EIT_ID => dup_eit_rec.pe_eit_id,
2634 X_PERSON_ID => NVL(eit_rec.person_id,dup_eit_rec.person_id),
2635 X_INFORMATION_TYPE => NVL(eit_rec.information_type,dup_eit_rec.information_type),
2636 X_PEI_INFORMATION1 => NVL(eit_rec.pei_information1,dup_eit_rec.pei_information1) ,
2637 X_PEI_INFORMATION2 => NVL(eit_rec.pei_information2,dup_eit_rec.pei_information2) ,
2638 X_PEI_INFORMATION3 => NVL(eit_rec.pei_information3,dup_eit_rec.pei_information3) ,
2639 X_PEI_INFORMATION4 => NVL(eit_rec.pei_information4,dup_eit_rec.pei_information4) ,
2640 X_PEI_INFORMATION5 => NVL(eit_rec.pei_information5,dup_eit_rec.pei_information5) ,
2641 X_START_DATE => NVL(eit_rec.start_date,dup_eit_rec.start_date),
2642 X_END_DATE => NVL(eit_rec.end_date,dup_eit_rec.end_date),
2643 X_MODE => 'R');
2644
2645 p_error_code := NULL;
2646 p_status := '1';
2647 END IF;
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 p_error_code := 'E014';
2652 p_status := '3';
2653
2654 UPDATE igs_pe_eit_int
2655 SET status = '3',
2656 error_code = 'E014'
2657 WHERE interface_eit_id = eit_rec.interface_eit_id;
2658
2659 -- CALL LOG DETAIL
2660
2661 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2662
2663 IF (l_request_id IS NULL) THEN
2664 l_request_id := fnd_global.conc_request_id;
2665 END IF;
2666
2670 fnd_message.set_token('INTERFACE_ID',eit_rec.interface_eit_id);
2667 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.upd_exception' || 'E014';
2668
2669 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2671 fnd_message.set_token('ERROR_CD','E014');
2672
2673 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2674
2675 fnd_log.string_with_context( fnd_log.level_exception,
2676 l_label,
2677 l_debug_str, NULL,
2678 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2679 END IF;
2680
2681 IF l_enable_log = 'Y' THEN
2682 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2683 END IF;
2684
2685 END upd_pe_eit;
2686
2687 --MAIN PROCEDURE BEGINS NOW
2688
2689 BEGIN
2690
2691 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit';
2692 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.';
2693 l_enable_log := igs_ad_imp_001.g_enable_log;
2694 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
2695 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2696
2697 IF (l_request_id IS NULL) THEN
2698 l_request_id := fnd_global.conc_request_id;
2699 END IF;
2700
2701 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.begin';
2702 l_debug_str := 'IGS_AD_IMP_026.prc_pe_eit';
2703
2704 fnd_log.string_with_context( fnd_log.level_procedure,
2705 l_label,
2706 l_debug_str, NULL,
2707 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2708 END IF;
2709
2710 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
2711
2712 IF l_rule = 'E' OR l_rule = 'I' THEN
2713
2714 UPDATE igs_pe_eit_int
2715 SET status='3',
2716 error_code = 'E695'
2717 WHERE interface_run_id=l_interface_run_id
2718 AND STATUS = '2'
2719 AND UPPER(information_type) ='PE_INT_PERM_RES'
2720 AND match_ind IS NOT NULL;
2721
2722 IF l_rule = 'E' THEN
2723
2724 UPDATE igs_pe_eit_int ei
2725 SET status='1',
2726 match_ind='19'
2727 WHERE interface_run_id=l_interface_run_id
2728 AND STATUS = '2'
2729 AND UPPER(information_type) ='PE_INT_PERM_RES'
2730 AND EXISTS( SELECT es.rowid
2731 FROM igs_pe_eit es,
2732 igs_ad_interface_all ad
2733 WHERE ad.interface_id = ei.interface_id AND
2734 es.person_id = ad.person_id AND
2735 ad.interface_run_id = l_interface_run_id AND
2736 es.information_type = UPPER(ei.information_type) AND
2737 es.start_date = TRUNC(ei.start_date));
2738 END IF;
2739
2740 ELSIF l_rule = 'R' THEN
2741
2742 UPDATE igs_pe_eit_int
2743 SET status = '1'
2744 WHERE interface_run_id=l_interface_run_id
2745 AND status = '2'
2746 AND UPPER(information_type) ='PE_INT_PERM_RES'
2747 AND match_ind IN ('18','19','22','23');
2748
2749 UPDATE igs_pe_eit_int
2750 SET status = '3',
2751 error_code = 'E695'
2752 WHERE interface_run_id=l_interface_run_id
2753 AND status = '2'
2754 AND UPPER(information_type) ='PE_INT_PERM_RES'
2755 AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
2756
2757 UPDATE igs_pe_eit_int ei
2758 SET status='1',
2759 match_ind = '23'
2760 WHERE interface_run_id=l_interface_run_id
2761 AND status = '2'
2762 AND UPPER(information_type) ='PE_INT_PERM_RES'
2763 AND match_ind IS NULL
2764 AND EXISTS( SELECT es.rowid
2765 FROM igs_pe_eit es,
2766 igs_ad_interface_all ad
2767 WHERE ad.interface_id = ei.interface_id AND
2768 es.person_id = ad.person_id AND
2769 ad.interface_run_id = l_interface_run_id AND
2770 es.information_type = UPPER(ei.information_type) AND
2771 TRUNC(es.start_date) = TRUNC(ei.start_date) AND
2772 ((UPPER(es.pei_information1) = UPPER(ei.pei_information1)) OR ((es.pei_information1 IS NULL) AND (ei.pei_information1 IS NULL))) AND
2773 ((UPPER(es.pei_information2) = UPPER(ei.pei_information2)) OR ((es.pei_information2 IS NULL) AND (ei.pei_information2 IS NULL))) AND
2774 ((UPPER(es.pei_information3) = UPPER(ei.pei_information3)) OR ((es.pei_information3 IS NULL) AND (ei.pei_information3 IS NULL))) AND
2775 ((UPPER(es.pei_information4) = UPPER(ei.pei_information4)) OR ((es.pei_information4 IS NULL) AND (ei.pei_information4 IS NULL))) AND
2776 ((UPPER(es.pei_information5) = UPPER(ei.pei_information5)) OR ((es.pei_information5 IS NULL) AND (ei.pei_information5 IS NULL))) AND
2777 ((TRUNC(es.end_date) = TRUNC(ei.end_date)) OR ((es.end_date IS NULL) AND (ei.end_date IS NULL))));
2778
2779 UPDATE igs_pe_eit_int ei
2780 SET status = '3',
2781 match_ind='20',
2782 dup_pe_eit_id = (SELECT pe_eit_id
2783 FROM igs_pe_eit es,
2784 igs_ad_interface_all ad
2785 WHERE ad.interface_id = ei.interface_id AND
2786 es.person_id = ad.person_id AND
2790 WHERE interface_run_id=l_interface_run_id AND
2787 ad.interface_run_id = l_interface_run_id AND
2788 es.information_type = UPPER(ei.information_type) AND
2789 es.start_date = TRUNC(ei.start_date) )
2791 status = '2' AND
2792 information_type ='PE_INT_PERM_RES' AND
2793 match_ind IS NULL AND
2794 EXISTS (SELECT es.rowid
2795 FROM igs_pe_eit es,
2796 igs_ad_interface_all ad
2797 WHERE ad.interface_id = ei.interface_id AND
2798 es.person_id = ad.person_id AND
2799 ad.interface_run_id = l_interface_run_id AND
2800 es.information_type = UPPER(ei.information_type) AND
2801 es.start_date = TRUNC(ei.start_date) );
2802 END IF;
2803
2804
2805 FOR eit_rec in eit_dtls('2',l_interface_run_id,'PE_INT_PERM_RES') LOOP
2806
2807 l_processed_records := l_processed_records + 1;
2808
2809 eit_rec.pei_information1 := UPPER(eit_rec.pei_information1);
2810 eit_rec.start_date := TRUNC(eit_rec.start_date) ;
2811 eit_rec.end_date := TRUNC(eit_rec.end_date);
2812 eit_rec.information_type := UPPER(eit_rec.information_type);
2813
2814 IF validate_record(eit_rec) THEN
2815
2816 l_dup_var := FALSE;
2817 l_eit_rec.pe_eit_id := NULL;
2818 OPEN chk_duplicate(eit_rec.person_id,eit_rec.information_type,eit_rec.start_date);
2819 FETCH chk_duplicate INTO l_eit_rec;
2820 CLOSE chk_duplicate;
2821
2822 IF l_eit_rec.pe_eit_id IS NOT NULL THEN
2823 l_dup_var := TRUE;
2824 END IF;
2825
2826 IF l_dup_var THEN
2827
2828 -- IF DUPLICATE RECORDS FOUND THEN FOLLOW DISCREPANCY RULE,GMURALD
2829
2830 IF l_rule = 'I' THEN
2831 BEGIN
2832 upd_pe_eit( eit_rec => eit_rec,
2833 dup_eit_rec => l_eit_rec,
2834 p_error_code => l_error_code,
2835 p_status => l_status);
2836
2837 UPDATE igs_pe_eit_int
2838 SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
2839 status = l_status ,
2840 error_code = l_error_code
2841 WHERE interface_eit_id = eit_rec.interface_eit_id;
2842
2843 EXCEPTION
2844 WHEN OTHERS THEN
2845
2846 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2847
2848 IF (l_request_id IS NULL) THEN
2849 l_request_id := fnd_global.conc_request_id;
2850 END IF;
2851
2852 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.exception1 ' || 'E014';
2853
2854 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2855 fnd_message.set_token('INTERFACE_ID',eit_rec.interface_eit_id);
2856 fnd_message.set_token('ERROR_CD','E014');
2857
2858 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2859
2860 fnd_log.string_with_context( fnd_log.level_exception,
2861 l_label,
2862 l_debug_str, NULL,
2863 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2864 END IF;
2865
2866 IF l_enable_log = 'Y' THEN
2867 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2868 END IF;
2869
2870 UPDATE igs_pe_eit_int
2871 SET match_ind = '18',
2872 status = '3',
2873 error_code = 'E014'
2874 WHERE interface_eit_id = eit_rec.interface_eit_id;
2875
2876 END;
2877
2878 ELSIF l_rule = 'R' THEN -- MATCH REVIEWED TO BE IMPORTED
2879 IF eit_rec.match_ind = '21' THEN
2880 BEGIN
2881 upd_pe_eit(eit_rec => eit_rec,
2882 dup_eit_rec => l_eit_rec,
2883 p_error_code => l_error_code,
2884 p_status => l_status);
2885
2886 UPDATE igs_pe_eit_int
2887 SET
2888 status = l_status ,
2889 error_code = l_error_code
2890 WHERE
2891 interface_eit_id = eit_rec.interface_eit_id;
2892
2893 EXCEPTION
2894 WHEN OTHERS THEN
2895
2896 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2897
2898 IF (l_request_id IS NULL) THEN
2899 l_request_id := fnd_global.conc_request_id;
2900 END IF;
2901
2902 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_eit.exception2 ' || 'E014';
2903
2904 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2905 fnd_message.set_token('INTERFACE_ID',eit_rec.interface_eit_id);
2906 fnd_message.set_token('ERROR_CD','E014');
2907
2908 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2909
2910 fnd_log.string_with_context( fnd_log.level_exception,
2911 l_label,
2912 l_debug_str, NULL,
2913 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2917 igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2914 END IF;
2915
2916 IF l_enable_log = 'Y' THEN
2918 END IF;
2919
2920 UPDATE igs_pe_eit_int
2921 SET status = '3',
2922 error_code = 'E014'
2923 WHERE interface_eit_id = eit_rec.interface_eit_id;
2924 END;
2925
2926 END IF;
2927 END IF;
2928 ELSE
2929 crt_pe_eit (eit_rec => eit_rec,
2930 error_code => l_error_code,
2931 status => l_status) ;
2932 END IF;
2933 END IF;
2934
2935 IF l_processed_records = 100 THEN
2936 COMMIT;
2937 l_processed_records := 0;
2938 END IF;
2939
2940 END LOOP;
2941 END prc_pe_eit;
2942
2943 PROCEDURE prc_pe_addr
2944 (
2945 p_source_type_id IN NUMBER,
2946 p_batch_id IN NUMBER ) AS
2947 /*
2948 || Created By : nsinha
2949 || Created On : 22-JUN-2001
2950 || Purpose : This procedure process the Application
2951 || Known limitations, enhancements or remarks :
2952 || Change History :
2953 || Who When What
2954 || ssaleem 8-OCT-2003 Bug no : 3130316
2955 || Performance enhancements done, validations and status
2956 || updations done outside the main loop
2957 || This procedure is brought in from IGSAD83B.pls
2958 || npalanis 6-JAN-2003 Bug : 2734697
2959 || code added to commit after import of every
2960 || 100 records .New variable l_processed_records added
2961 || pkpatel 22-JUN-2001 Bug no.2466466
2962 || Added the parameter p_party_site_id in update address.
2963 || Modified for performance.
2964 || gmaheswa 27-Jan-2006 Bug: 4938278: Call IGS_PE_WF_GEN. ADDR_BULK_SYNCHRONIZATION to raise bulk
2965 || address change notification after process address records of all persons.
2966 || (reverse chronological order - newest change first)
2967 */
2968
2969 lnDupExist NUMBER;
2970 lvcAction VARCHAR2(1);
2971 lvcRecordExist VARCHAR2(1);
2972 p_status VARCHAR2(1);
2973 p_error_code VARCHAR2(30);
2974 l_location_id hz_party_sites.location_id%TYPE;
2975 l_party_site_id hz_party_sites.party_site_id%TYPE;
2976 l_processed_records NUMBER(5) := 0;
2977
2978 l_prog_label VARCHAR2(100);
2979 l_label VARCHAR2(100);
2980 l_debug_str VARCHAR2(2000);
2981 l_enable_log VARCHAR2(1);
2982 l_request_id NUMBER;
2983 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2984
2985 CURSOR addr_cur(cp_interface_run_id igs_ad_addr_int_all.interface_run_id%TYPE) IS
2986 SELECT ai.*, i.person_id
2987 FROM igs_ad_addr_int_all ai, igs_ad_interface_all i
2988 WHERE ai.status = '2' AND
2989 i.interface_run_id = cp_interface_run_id AND
2990 ai.interface_id = i.interface_id AND
2991 i.status = '1';
2992
2993
2994 addr_rec addr_cur%ROWTYPE;
2995 l_addr_rec1 igs_ad_addr_int_all%ROWTYPE;
2996
2997 CURSOR check_dup_addr(cp_x_value VARCHAR2,
2998 cp_addr_rec addr_cur%ROWTYPE) IS
2999 SELECT hz_party_sites.rowid,hz_party_sites.*
3000 FROM hz_locations, hz_party_sites
3001 WHERE hz_party_sites.party_id = cp_addr_rec.person_id
3002 AND hz_party_sites.location_id = hz_locations.location_id
3003 AND UPPER(NVL(hz_locations.address1,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_1,cp_x_value))
3004 AND UPPER(NVL(hz_locations.address2,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_2,cp_x_value))
3005 AND UPPER(NVL(hz_locations.address3,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_3,cp_x_value))
3006 AND UPPER(NVL(hz_locations.address4,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_4,cp_x_value))
3007 AND UPPER(NVL(hz_locations.city,cp_x_value)) = UPPER(NVL(cp_addr_rec.city,cp_x_value))
3008 AND UPPER(NVL(hz_locations.state,cp_x_value)) = UPPER(NVL(cp_addr_rec.state,cp_x_value))
3009 AND hz_locations.country = cp_addr_rec.country
3010 AND UPPER(NVL(hz_locations.county,cp_x_value)) = UPPER(NVL(cp_addr_rec.county,cp_x_value))
3011 AND UPPER(NVL(hz_locations.province,cp_x_value)) = UPPER(NVL(cp_addr_rec.province,cp_x_value));
3012
3013 l_addr_rec check_dup_addr%ROWTYPE;
3014
3015 BEGIN
3016
3017 l_prog_label := 'igs.plsql.igs_ad_imp_026.prc_pe_addr';
3018 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_addr.';
3019 l_enable_log := igs_ad_imp_001.g_enable_log;
3020 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3021 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
3022
3023 IF (l_request_id IS NULL) THEN
3024 l_request_id := fnd_global.conc_request_id;
3025 END IF;
3026
3027 l_label := 'igs.plsql.igs_ad_imp_026.prc_pe_addr.begin';
3028 l_debug_str := 'Igs_Ad_Imp_005.PRC_PE_ADDR';
3029
3030 fnd_log.string_with_context( fnd_log.level_procedure,
3031 l_label,
3032 l_debug_str, NULL,
3033 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3034 END IF;
3035
3036 lvcAction := Igs_Ad_Imp_001.find_source_cat_rule(p_source_type_id,'PERSON_ADDRESS');
3037
3041 SET status='3',
3038 IF lvcAction = 'E' OR lvcAction = 'I' THEN
3039
3040 UPDATE igs_ad_addr_int_all
3042 error_code = 'E695'
3043 WHERE interface_run_id=l_interface_run_id
3044 AND STATUS = '2'
3045 AND match_ind IS NOT NULL;
3046
3047 IF lvcAction = 'E' THEN
3048
3049 UPDATE igs_ad_addr_int_all ai
3050 SET status='1',
3051 match_ind='19'
3052 WHERE interface_run_id=l_interface_run_id
3053 AND STATUS = '2'
3054 AND EXISTS( SELECT hs.rowid
3055 FROM hz_party_sites hs,
3056 igs_ad_interface_all ad,
3057 hz_locations hl
3058 WHERE ad.interface_id = ai.interface_id AND
3059 hs.party_id = ad.person_id AND
3060 hs.location_id = hl.location_id AND
3061 UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
3062 UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
3063 UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
3064 UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
3065 UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
3066 UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
3067 hl.country = UPPER(ai.country) AND
3068 UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
3069 UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')));
3070 END IF;
3071
3072 ELSIF lvcAction = 'R' THEN
3073
3074 UPDATE igs_ad_addr_int_all
3075 SET status = '1'
3076 WHERE interface_run_id=l_interface_run_id
3077 AND status = '2'
3078 AND match_ind IN ('18','19','22','23');
3079
3080 UPDATE igs_ad_addr_int_all
3081 SET status = '3',
3082 error_code = 'E695'
3083 WHERE interface_run_id=l_interface_run_id
3084 AND status = '2'
3085 AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
3086
3087 UPDATE igs_ad_addr_int_all ai
3088 SET status='1',
3089 match_ind = '23'
3090 WHERE interface_run_id=l_interface_run_id
3091 AND status = '2'
3092 AND match_ind IS NULL
3093 AND EXISTS( SELECT hs.rowid
3094 FROM hz_locations hl,
3095 hz_party_sites hs,
3096 igs_ad_interface_all ad
3097 WHERE ad.interface_id = ai.interface_id AND
3098 hs.party_id = ad.person_id AND
3099 hs.location_id = hl.location_id AND
3100 NVL(UPPER(hl.address1), 'X') = NVL(UPPER(ai.addr_line_1), 'X') AND
3101 NVL(UPPER(hl.address2), 'X') = NVL(UPPER(ai.addr_line_2), 'X') AND
3102 NVL(UPPER(hl.address3), 'X') = NVL(UPPER(ai.addr_line_3), 'X') AND
3103 NVL(UPPER(hl.address4), 'X') = NVL(UPPER(ai.addr_line_4), 'X') AND
3104 NVL(UPPER(hl.city), 'X') = NVL(UPPER(ai.city), 'X') AND
3105 NVL(UPPER(hl.state), 'X') = NVL(UPPER(ai.state), 'X') AND
3106 NVL(UPPER(hl.province), 'X') = NVL(UPPER(ai.province), 'X') AND
3107 NVL(UPPER(hl.county), 'X') = NVL(UPPER(ai.county), 'X') AND
3108 hl.country = UPPER(ai.country) AND
3109 NVL(UPPER(hl.postal_code), 'X') = NVL(UPPER(ai.postcode), 'X'));
3110
3111
3112 UPDATE igs_ad_addr_int_all ai
3113 SET status = '3',
3114 match_ind='20',
3115 dup_party_site_id = (SELECT hs.party_site_id
3116 FROM hz_party_sites hs,
3117 igs_ad_interface_all ad,
3118 hz_locations hl
3119 WHERE ad.interface_id = ai.interface_id AND
3120 ROWNUM = 1 AND
3121 hs.party_id = ad.person_id AND
3122 hs.location_id = hl.location_id AND
3123 UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
3124 UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
3125 UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
3126 UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
3127 UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
3128 UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
3129 hl.country = UPPER(ai.country) AND
3130 UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
3131 UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')))
3132 WHERE interface_run_id=l_interface_run_id AND
3133 status = '2' AND
3134 match_ind IS NULL AND
3135 EXISTS (SELECT hs.rowid
3136 FROM hz_party_sites hs,
3137 igs_ad_interface_all ad,
3138 hz_locations hl
3139 WHERE ad.interface_id = ai.interface_id AND
3140 hs.party_id = ad.person_id AND
3141 hs.location_id = hl.location_id AND
3145 UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
3142 UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
3143 UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
3144 UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
3146 UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
3147 UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
3148 hl.country = UPPER(ai.country) AND
3149 UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
3150 UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')) );
3151 END IF;
3152
3153 FOR addr_rec IN addr_cur(l_interface_run_id) LOOP
3154 Igs_Ad_Imp_002.g_addr_process := FALSE;
3155 -- initialize the columns of l_addr_rec
3156 addr_rec.country := UPPER(addr_rec.country);
3157
3158 l_addr_rec1.org_id := addr_rec.org_id;
3159 l_addr_rec1.interface_addr_id := addr_rec.interface_addr_id;
3160 l_addr_rec1.interface_id := addr_rec.interface_id;
3161 l_addr_rec1.addr_line_1 := addr_rec.addr_line_1;
3162 l_addr_rec1.addr_line_2 := addr_rec.addr_line_2;
3163 l_addr_rec1.addr_line_3 := addr_rec.addr_line_3;
3164 l_addr_rec1.addr_line_4 := addr_rec.addr_line_4;
3165 l_addr_rec1.postcode := addr_rec.postcode;
3166 l_addr_rec1.city := addr_rec.city ;
3167 l_addr_rec1.state := addr_rec.state ;
3168 l_addr_rec1.county := addr_rec.county ;
3169 l_addr_rec1.province := addr_rec.province;
3170 l_addr_rec1.country := addr_rec.country ;
3171 l_addr_rec1.other_details := addr_rec.other_details;
3172 l_addr_rec1.other_details_1 := addr_rec.other_details_1;
3173 l_addr_rec1.other_details_2 := addr_rec.other_details_2;
3174 l_addr_rec1.delivery_point_code := addr_rec.delivery_point_code;
3175 l_addr_rec1.other_details_3 := addr_rec.other_details_3;
3176 l_addr_rec1.correspondence_flag := addr_rec.correspondence_flag;
3177 l_addr_rec1.contact_person_id := addr_rec.contact_person_id;
3178 l_addr_rec1.date_last_verified := addr_rec.date_last_verified;
3179 l_addr_rec1.start_date := addr_rec.start_date;
3180 l_addr_rec1.end_date := addr_rec.end_date;
3181 l_addr_rec1.match_ind := addr_rec.match_ind;
3182 l_addr_rec1.status := addr_rec.status;
3183 l_addr_rec1.ERROR_CODE := addr_rec.ERROR_CODE;
3184 l_addr_rec1.dup_party_site_id := addr_rec.dup_party_site_id;
3185 l_addr_rec1.created_by := addr_rec.created_by;
3186 l_addr_rec1.creation_date := addr_rec.creation_date;
3187 l_addr_rec1.last_updated_by := addr_rec.last_updated_by;
3188 l_addr_rec1.last_update_date := addr_rec.last_update_date;
3189 l_addr_rec1.last_update_login := addr_rec.last_update_login;
3190 l_addr_rec1.request_id := addr_rec.request_id;
3191 l_addr_rec1.program_application_id := addr_rec.program_application_id;
3192 l_addr_rec1.program_id := addr_rec.program_id;
3193 l_addr_rec1.program_update_date := addr_rec.program_update_date;
3194 l_addr_rec1.interface_run_id := addr_rec.interface_run_id;
3195 --
3196 l_processed_records := l_processed_records + 1;
3197
3198 l_location_id := NULL;
3199 l_party_site_id := NULL;
3200 l_addr_rec.location_id:= NULL;
3201 OPEN check_dup_addr('X',addr_rec);
3202 FETCH check_dup_addr INTO l_addr_rec;
3203 CLOSE check_dup_addr;
3204
3205 IF l_addr_rec.location_id IS NOT NULL THEN
3206
3207 IF lvcAction = 'I' THEN
3208
3209 Igs_Ad_Imp_002.update_address(p_addr_rec => l_addr_rec1,
3210 p_person_id => addr_rec.person_id,
3211 p_location_id => l_addr_rec.location_id,
3212 p_party_site_id => l_addr_rec.party_site_id );
3213 ELSIF lvcAction = 'R' THEN
3214 IF addr_rec.match_ind = '21' THEN
3215
3216 --Make a call to IGS_AD_UPDATE_ADDRESS with the following parameters.
3217 Igs_Ad_Imp_002.update_address( p_addr_rec => l_addr_rec1,
3218 p_person_id => addr_rec.person_id,
3219 p_location_id => l_addr_rec.location_id,
3220 p_party_site_id => l_addr_rec.party_site_id);
3221 END IF;
3222 END IF;
3223
3224 ELSE
3225 --Make a call to IGS_AD_CREATE_ADDRESS with the following parameters.
3226
3227 --Make a call to Create ADDRESS with the following parameters.
3228 Igs_Ad_Imp_002.create_address(
3229 p_addr_rec => l_addr_rec1,
3230 p_person_id => addr_rec.person_id,
3231 p_status => p_status ,
3232 p_error_code => p_error_code );
3233
3234 END IF;
3235
3236 IF l_processed_records = 100 THEN
3237 COMMIT;
3238 l_processed_records := 0;
3239 END IF;
3240 IF (Igs_Ad_Imp_002.g_addr_process) THEN
3241 --populate IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS table with party id to generate notification at the end of process
3242 IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS(NVL(IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS.LAST,0)+1) := addr_rec.person_id;
3243 END IF;
3244 END LOOP;
3245 END prc_pe_addr;
3246
3247 END IGS_AD_IMP_026;