[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_009
Source
1 PACKAGE BODY Igs_Ad_Imp_009 AS
2 /* $Header: IGSAD87B.pls 120.3 2006/03/24 02:34:32 gmaheswa noship $ */
3
4 /*************************************
5 || Change History
6 || who when what
7 || ssaleem 13_OCT_2003 Bug : 3130316
8 || Logging is modified to include logging mechanism
9 || npalanis 22-APR-2003 BUG:2832980 location_id is not selected in form_dup where clause because it is not used any where
10 || pkpatel 22-JUN-2001 Bug no.2702536
11 || Added the parameters p_match_ind, p_person_id, p_addr_type and p_person_id_type to IGS_AD_IMP_FIND_DUP_PERSONS
12 || And implemented the new record level duplicate check.
13 || gmuralid 4 -DEC-2002 Change by gmuralid, removed reference to table igs_ad_intl_int,
14 || igs_pe_fund_dep_int.Included references to igs_pe_visa_int,
15 || igs_pe_vst_hist_int,igs_pe_passport_int,igs_pe_eit_int
16 || As a part of BUG 2599109, SEVIS Build
17 ||
18 || ssawhney 21-may-2002 Bug 2381539, %imp_p% procedures, incorrect ref to variable x_lvcExactSelectClause in case of partial match
19 || because of which it was not going into match at all.
20 || pkpatel 30-MAY-2002 Bug 2377580, parameters were missing in the call to Igs_Pe_Identify_Dups.form_dup_whereclause
21 || npalanis 5-Jun-2002 Bug 2397849 , The function for match indicator 15 ,16 and 17 are handled.
22 || pkpatel 10-OCT-2002 Bug No: 2603065
23 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
24 || ssawhney 22-oct-2002 SWS104, Bug 2630860. Introduced validations from making STATISTICS not mandatory.
25 || modified for ACADHONORS.SWS104 obsoleted IGS_AD_REFS_INT
26 || sjalasut 31st oct SWS105 ad_collact table obsoleted
27 || sjalasut Jan 20, 2003 changed the references of IGS_AD_INQ_CHAR_INT to IGS_RC_I_CHAR_INT for RCT101 Build. bug 2664699
28 || rrengara 14 Feb 2003 changes for RCT Build. Removed the obsolete table names and replaced the new table names
29 || ssawhney 24-feb-2003 REF CUR dup_matches_cur was not being closed for exact match of DUP_MATCHES_P and PP
30 || vrathi 26-Jun-2003 Bug 3001974 Added specific messages in duplicate check fiunctions. + sswhney - valriable lenght increased
31 for whereclause execution.
32 || asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
33 || gmaheswa 9-OCT-2003 Bug 3146324, Match Criteria sets Enhancement
34 */
35
36 -- constants to replace match indicator values for sql performance tuning
37 cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
38 cst_mi_val_12 CONSTANT VARCHAR2(2) := '12';
39 cst_mi_val_13 CONSTANT VARCHAR2(2) := '13';
40 cst_mi_val_14 CONSTANT VARCHAR2(2) := '14';
41 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
42
43 cst_err_val_1 CONSTANT VARCHAR2(4) := 'E001';
44 cst_err_val_2 CONSTANT VARCHAR2(4) := 'E002';
45 cst_err_val_3 CONSTANT VARCHAR2(4) := 'E003';
46
47 cst_stat_val_3 CONSTANT VARCHAR2(2) := '3';
48
49 lnOrg_ID NUMBER := igs_ge_gen_003.get_org_id;
50 lnParty_Site_ID NUMBER;
51
52 PROCEDURE igs_ad_find_duplicates_imp_p
53 (p_d_match_set_id IN NUMBER,
54 p_d_interface_id IN NUMBER,
55 p_d_batch_id IN NUMBER,
56 p_c_addr_type IN VARCHAR2,
57 p_c_person_id_type IN VARCHAR2,
58 p_person_id OUT NOCOPY igs_ad_interface.person_id%TYPE,
59 p_match_ind OUT NOCOPY igs_ad_interface.match_ind%TYPE)
60 AS
61 /*
62 || Created By : prabhat.patel
63 || Created On : 09-OCT-2002
64 || Purpose : Handles the Unique Constraint logic defined for the columns.
65 || Known limitations, enhancements or remarks :
66 || Change History :
67 || Who When What
68 || pkpatel 10-OCT-2002 Bug NO: 2603065
69 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
70 || pkpatel 22-JUN-2001 Bug no.2702536
71 || Added the parameters p_match_ind, p_person_id
72 || pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
73 || (reverse chronological order - newest change first)
74 */
75 CURSOR imp_person_cur(cp_d_interface_id igs_ad_imp_matches_p_v.interface_id%TYPE) IS
76 SELECT *
77 FROM igs_ad_imp_matches_p_v
78 WHERE interface_id = cp_d_interface_id;
79
80 l_prog_label VARCHAR2(100);
81 l_label VARCHAR2(100);
82 l_debug_str VARCHAR2(2000);
83 l_enable_log VARCHAR2(1);
84 l_request_id NUMBER;
85
86 x_lvcExactSelectClause VARCHAR2(32000);
87 x_lvcPartialSelectClause VARCHAR2(32000);
88 x_match_cnt NUMBER := 0;
89
90 i NUMBER(3):=0;
91 l_rowid VARCHAR2(25);
92 l_pk NUMBER(15);
93 l_person_id igs_pe_person.person_id%TYPE;
94 l_errbuf VARCHAR2(10);
95 l_retcode NUMBER;
96
97 l_cursor_id NUMBER(15);
98 l_cursor_id1 NUMBER(15);
99 l_num_of_rows NUMBER(15);
100 l_dsql_debug VARCHAR2(4000);
101
102 BEGIN
103
104 l_prog_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_p';
105 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_p.';
106 l_enable_log := igs_ad_imp_001.g_enable_log;
107
108 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
109
110 IF (l_request_id IS NULL) THEN
111 l_request_id := fnd_global.conc_request_id;
112 END IF;
113
114 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_p.begin';
115 l_debug_str := 'igs_ad_imp_009.igs_ad_find_duplicates_imp_p';
116
117 fnd_log.string_with_context( fnd_log.level_procedure,
118 l_label,
119 l_debug_str, NULL,
120 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
121 END IF;
122
123
124 FOR imp_person_rec IN imp_person_cur(p_d_interface_id) LOOP
125
126 i := i+1;
127
128 -- First call the form_dup_whereclause with Exact match
129 -- for that pass x_match_category as 'E'
130
131 Igs_Pe_Identify_Dups.form_dup_whereclause(
132 x_errbuf => l_errbuf,
133 x_retcode => l_retcode,
134 x_match_set_id =>p_d_match_set_id,
135 x_match_category =>'E',
136 x_view_name =>'IGS_PE_DUP_MATCHES_P_V',
137 x_surname =>imp_person_rec.surname,
138 x_given_names =>imp_person_rec.given_names,
139 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
140 x_birth_dt =>imp_person_rec.birth_dt,
141 x_sex =>imp_person_rec.sex,
142 x_ethnic_origin =>imp_person_rec.ethnic_origin,
143 x_select_clause => x_lvcExactSelectClause);
144
145 -- The above procedure will return 'PARTIAL_MATCH', if the exact match is not found
146
147 IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
148 -- Open the Dynamic Cursor with the select statement returned for Exact Match
149
150 l_cursor_id := dbms_sql.open_cursor;
151 fnd_dsql.set_cursor(l_cursor_id);
152
153 dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
154 fnd_dsql.do_binds;
155
156 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
157
158 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
159
160 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.*/
161 l_dsql_debug := fnd_dsql.get_text(TRUE);
162
163 LOOP
164 -- fetch a row
165 IF dbms_sql.fetch_rows(l_cursor_id) > 0 THEN
166 x_match_cnt := x_match_cnt+1;
167 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
168
169 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
170 (x_rowid =>l_rowid,
171 x_org_id => lnOrg_ID,
172 x_near_mtch_id=>l_pk,
173 x_interface_id=>p_d_interface_id,
174 x_person_id=>l_person_id,
175 x_match_ind=>'E',
176 x_action=>'D',
177 x_addr_type=> p_c_addr_type,
178 x_person_id_type=>p_c_person_id_type,
179 x_match_set_id=>p_d_match_set_id,
180 x_mode =>'I',
181 x_Party_Site_ID => NULL);
182 -- fetch columns from the row
183 ELSE
184 EXIT;
185 END IF;
186 END LOOP;
187 dbms_sql.close_cursor(l_cursor_id);
188
189 /*If the dynamic Query returns only one row, then Update the
190 igs_ad_interface_table */
191
192 IF x_match_cnt = 1 THEN /* Only One Match is Found Match_Ind 12 is - Match to a Single Person*/
193 UPDATE igs_ad_interface
194 SET match_ind = cst_mi_val_12,
195 person_id = l_person_id
196 WHERE interface_id = imp_person_rec.interface_id;
197
198 p_person_id := l_person_id;
199 p_match_ind := '12';
200 RETURN;
201 ELSIF x_match_cnt > 1 THEN -- if more than one duplicate is found then update match_ind to 13
202 -- 13 - Match to Multiple Persons
203 UPDATE igs_ad_interface
204 SET match_ind = cst_mi_val_13,
205 ERROR_CODE = cst_err_val_2,
206 STATUS = cst_stat_val_3
207 WHERE interface_id = imp_person_rec.interface_id;
208
209 p_match_ind := '13';
210
211 IF l_enable_log = 'Y' THEN
212 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E002', p_match_ind => '13');
213 END IF;
214
215 RETURN;
216 END IF;
217 END IF;
218 -- If partial Match returns 0 records or the select clause from
219 -- form_dup_where cluase is 'PARTIAL_MATCH' then the control come here
220 IF x_match_cnt = 0 THEN
221
222 /* If Exact Match is not found then go for Partial Match . pass the match_category as 'P'*/
223 Igs_Pe_Identify_Dups.form_dup_whereclause(
224 x_errbuf => l_errbuf,
225 x_retcode => l_retcode,
226 x_match_set_id =>p_d_match_set_id,
227 x_match_category =>'P',
228 x_view_name =>'IGS_PE_DUP_MATCHES_P_V',
229 x_surname =>imp_person_rec.surname,
230 x_given_names =>imp_person_rec.given_names,
231 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
232 x_birth_dt =>imp_person_rec.birth_dt,
233 x_sex =>imp_person_rec.sex,
234 x_ethnic_origin =>imp_person_rec.ethnic_origin,
235 x_select_clause => x_lvcPartialSelectClause);
236
237 IF x_lvcPartialSelectClause IS NOT NULL THEN
238
239 -- Execute the Dynamic SQL
240 l_cursor_id1 := dbms_sql.open_cursor;
241 fnd_dsql.set_cursor(l_cursor_id1);
242
243 dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
244 fnd_dsql.do_binds;
245
246 dbms_sql.define_column(l_cursor_id1, 1, l_person_id);
247
248 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id1);
249
250 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.*/
251 l_dsql_debug := fnd_dsql.get_text(TRUE);
252
253 LOOP
254 -- fetch a row
255 IF dbms_sql.fetch_rows(l_cursor_id1) > 0 THEN
256 x_match_cnt := x_match_cnt+1;
257 dbms_sql.column_value(l_cursor_id1, 1, l_person_id);
258
259 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
260 (x_rowid =>l_rowid,
261 x_org_id => lnOrg_ID,
262 x_near_mtch_id=>l_pk,
263 x_interface_id=>p_d_interface_id,
264 x_person_id=>l_person_id,
265 x_match_ind=>'P',
266 x_action=>'D',
267 x_addr_type=> p_c_addr_type,
268 x_person_id_type=>p_c_person_id_type,
269 x_match_set_id=>p_d_match_set_id,
270 x_mode =>'R',
271 x_Party_Site_ID => NULL);
272 ELSE
273 EXIT;
274 END IF;
275 END LOOP;
276
277 dbms_sql.close_cursor(l_cursor_id1);
278 IF x_match_cnt = 0 THEN /* Partial match not found */
279 UPDATE igs_ad_interface
280 SET match_ind = cst_mi_val_11
281 WHERE interface_id = imp_person_rec.interface_id;
282 p_match_ind := '11';
283 RETURN;
284 ELSE
285 UPDATE igs_ad_interface
286 SET match_ind = cst_mi_val_14,
287 ERROR_CODE = cst_err_val_3,
288 STATUS = cst_stat_val_3
289 WHERE interface_id = imp_person_rec.interface_id;
290 p_match_ind := '14';
291
292 IF l_enable_log = 'Y' THEN
293 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E003', p_match_ind => '14');
294 END IF;
295
296 RETURN;
297 END IF;
298 END IF;
299 END IF;
300 END LOOP;
301 IF i = 0 THEN
302 UPDATE igs_ad_interface
303 SET status = cst_stat_val_3,
304 match_ind = cst_mi_val_24,
305 ERROR_CODE = cst_err_val_1
306 WHERE interface_id = p_d_interface_id;
307 p_match_ind := '24';
308
309 IF l_enable_log = 'Y' THEN
310 igs_ad_imp_001.logerrormessage(p_record => p_d_interface_id, p_error => 'E001', p_match_ind => '24');
311 END IF;
312
313 END IF;
314 END igs_ad_find_duplicates_imp_p;
315
316 PROCEDURE igs_ad_find_duplicates_imp_pp
317 (p_d_match_set_id IN NUMBER,
318 p_d_interface_id IN NUMBER,
319 p_d_batch_id IN NUMBER,
320 p_c_addr_type IN VARCHAR2,
321 p_c_person_id_type IN VARCHAR2,
322 p_person_id OUT NOCOPY igs_ad_interface.person_id%TYPE,
323 p_match_ind OUT NOCOPY igs_ad_interface.match_ind%TYPE)
324 AS
325 /*
326 || Created By : prabhat.patel
327 || Created On : 09-OCT-2002
328 || Purpose : Handles the Unique Constraint logic defined for the columns.
329 || Known limitations, enhancements or remarks :
330 || Change History :
331 || Who When What
332 || pkpatel 10-OCT-2002 Bug No: 2603065
333 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
334 || pkpatel 22-JUN-2001 Bug no.2702536
335 || Added the parameters p_match_ind, p_person_id
336 || pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
337 || (reverse chronological order - newest change first)
338 */
339
340 l_prog_label VARCHAR2(100);
341 l_label VARCHAR2(100);
342 l_debug_str VARCHAR2(2000);
343 l_enable_log VARCHAR2(1);
344 l_request_id NUMBER;
345
346 CURSOR imp_person_cur(cp_d_interface_id igs_ad_imp_matches_pp_v.interface_id%TYPE,
347 cp_c_person_id_type igs_ad_imp_matches_pp_v.person_id_type%TYPE) IS
348 SELECT *
349 FROM igs_ad_imp_matches_pp_v
350 WHERE interface_id = cp_d_interface_id AND
351 ( person_id_type = cp_c_person_id_type OR person_id_type IS NULL );
352
353 x_lvcExactSelectClause VARCHAR2(32000);
354 x_lvcPartialSelectClause VARCHAR2(32000);
355 x_match_cnt NUMBER := 0;
356 l_errbuf VARCHAR2(10);
357 l_retcode NUMBER;
358 l_person_id igs_pe_person.person_id%TYPE;
359 i NUMBER(3):=0;
360 l_rowid VARCHAR2(25);
361 l_pk NUMBER(15);
362
363 l_cursor_id NUMBER(15);
364 l_cursor_id1 NUMBER(15);
365 l_num_of_rows NUMBER(15);
366 l_dsql_debug VARCHAR2(4000);
367
368 BEGIN
369
370 -- Call Log header
371
372 l_prog_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_pp';
373 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_pp.';
374 l_enable_log := igs_ad_imp_001.g_enable_log;
375
376 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
377
378 IF (l_request_id IS NULL) THEN
379 l_request_id := fnd_global.conc_request_id;
380 END IF;
381
382 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_pp.begin';
383 l_debug_str := 'Igs_Ad_Imp_009.igs_ad_find_duplicates_imp_pp';
384
385 fnd_log.string_with_context( fnd_log.level_procedure,
386 l_label,
387 l_debug_str, NULL,
388 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
389 END IF;
390
391 FOR imp_person_rec IN imp_person_cur(p_d_interface_id,p_c_person_id_type) LOOP
392 IF ((Igs_Pe_Identify_Dups.g_person_id_type_din = 'N') AND
393 ((imp_person_rec.ALTERNATE_ID IS NULL) OR (imp_person_rec.PERSON_ID_TYPE IS NULL))) THEN
394 i:= 0;
395 ELSE
396 i := i+1;
397 Igs_Pe_Identify_Dups.form_dup_whereclause(
398 x_errbuf => l_errbuf,
399 x_retcode => l_retcode,
400 x_match_set_id =>p_d_match_set_id,
401 x_match_category =>'E',
402 x_view_name =>'IGS_PE_DUP_MATCHES_PP_V',
403 x_surname =>imp_person_rec.surname,
404 x_given_names =>imp_person_rec.given_names,
405 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
406 x_birth_dt =>imp_person_rec.birth_dt,
407 x_sex =>imp_person_rec.sex,
408 x_ethnic_origin =>imp_person_rec.ethnic_origin,
409 x_select_clause => x_lvcExactSelectClause,
410 x_api_person_id =>imp_person_rec.alternate_id,
411 x_person_id_type =>imp_person_rec.person_id_type
412 );
413 -- The above procedure will return 'PARTIAL_MATCH', if the exact match is not found
414
415 IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
416
417 l_cursor_id := dbms_sql.open_cursor;
418 fnd_dsql.set_cursor(l_cursor_id);
419
420 dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
421 fnd_dsql.do_binds;
422
423 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
424
425 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
426 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
427 l_dsql_debug := fnd_dsql.get_text(TRUE);
428 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
429 */
430 LOOP
431 -- fetch a row
432 IF dbms_sql.fetch_rows(l_cursor_id) > 0 THEN
433 x_match_cnt := x_match_cnt+1;
434 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
435
436 /* Insert into igs_ad_imp_near_match, all the duplicate records */
437 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
438 (x_rowid =>l_rowid,
439 x_Org_ID => lnOrg_ID,
440 x_near_mtch_id=>l_pk,
441 x_interface_id=>p_d_interface_id,
442 x_person_id=> l_person_id,
443 x_match_ind=>'E',
444 x_action=>'D',
445 x_addr_type=>p_c_addr_type,
446 x_person_id_type=>p_c_person_id_type,
447 x_match_set_id=>p_d_match_set_id,
448 x_mode =>'R',
449 x_Party_Site_ID => NULL);
450
451 ELSE
452 EXIT;
453 END IF;
454 END LOOP; /* End Loop for dup_matches_cur */
455 dbms_sql.close_cursor(l_cursor_id);
456 /*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
457 IF x_match_cnt = 1 THEN /* Only One Match is Found */
458 UPDATE igs_ad_interface
459 SET match_ind = cst_mi_val_12,
460 person_id = l_person_id
461 WHERE interface_id = imp_person_rec.interface_id;
462 p_person_id := l_person_id;
463 p_match_ind := '12';
464 RETURN;
465 ELSIF x_match_cnt > 1 THEN
466 UPDATE igs_ad_interface
467 SET match_ind = cst_mi_val_13,
468 error_code = cst_err_val_2,
469 STATUS = cst_stat_val_3
470 WHERE interface_id = imp_person_rec.interface_id;
471 p_match_ind := '13';
472
473 IF l_enable_log = 'Y' THEN
474 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E002', p_match_ind => '13');
475 END IF;
476
477 RETURN;
478 END IF;
479 END IF;
480
481 IF x_match_cnt = 0 THEN
482 /* If Exact Match is not found then go for Partial Match */
483 Igs_Pe_Identify_Dups.form_dup_whereclause(
484 x_errbuf => l_errbuf,
485 x_retcode => l_retcode,
486 x_match_set_id =>p_d_match_set_id,
487 x_match_category =>'P',
488 x_view_name =>'IGS_PE_DUP_MATCHES_PP_V',
489 x_surname =>imp_person_rec.surname,
490 x_given_names =>imp_person_rec.given_names,
491 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
492 x_birth_dt =>imp_person_rec.birth_dt,
493 x_sex =>imp_person_rec.sex,
494 x_ethnic_origin =>imp_person_rec.ethnic_origin,
495 x_api_person_id =>imp_person_rec.alternate_id,
496 x_person_id_type =>imp_person_rec.person_id_type,
497 x_select_clause => x_lvcPartialSelectClause);
498
499
500 IF x_lvcPartialSelectClause IS NOT NULL THEN
501
502 /* Exceute the Partial Select Clause */
503 l_cursor_id1 := dbms_sql.open_cursor;
504 fnd_dsql.set_cursor(l_cursor_id1);
505
506 dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
507 fnd_dsql.do_binds;
508
509 dbms_sql.define_column(l_cursor_id1, 1, l_person_id);
510
511 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id1);
512
513 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
514 l_dsql_debug := fnd_dsql.get_text(TRUE);
515 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
516 */
517
518 LOOP
519 -- fetch a row
520 IF dbms_sql.fetch_rows(l_cursor_id1) > 0 THEN
521
522 x_match_cnt := x_match_cnt+1;
523
524 dbms_sql.column_value(l_cursor_id1, 1, l_person_id);
525
526 /* Insert into igs_ad_imp_near_match_int, all the duplicate records */
527 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
528 (x_rowid =>l_rowid,
529 x_Org_ID => lnOrg_ID,
530 x_near_mtch_id=>l_pk,
531 x_interface_id=>p_d_interface_id,
532 x_person_id=> l_person_id,
533 x_match_ind=>'P',
534 x_action=>'D',
535 x_addr_type=>p_c_addr_type,
536 x_person_id_type=>p_c_person_id_type,
537 x_match_set_id=>p_d_match_set_id,
538 x_mode =>'R',
539 x_party_Site_ID => NULL);
540
541 ELSE
542 EXIT;
543 END IF;
544
545 END LOOP; /* End Loop for dup_matches_cur */
546
547 dbms_sql.close_cursor(l_cursor_id1);
548
549 IF x_match_cnt = 0 THEN /* No Partial match not found */
550 UPDATE igs_ad_interface
551 SET match_ind = cst_mi_val_11
552 WHERE interface_id = imp_person_rec.interface_id;
553
554 p_match_ind := '11';
555
556 RETURN;
557 ELSE
558 UPDATE igs_ad_interface
559 SET match_ind = cst_mi_val_14,
560 ERROR_CODE = cst_err_val_3,
561 STATUS = cst_stat_val_3
562 WHERE interface_id = imp_person_rec.interface_id;
563
564 p_match_ind := '14';
565
566 IF l_enable_log = 'Y' THEN
567 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E003', p_match_ind => '14');
568 END IF;
569
570 RETURN;
571 END IF;
572 END IF;
573 END IF;
574 END IF;
575 END LOOP;
576 IF i = 0 THEN
577 UPDATE igs_ad_interface
578 SET status = cst_stat_val_3,
579 match_ind = cst_mi_val_24,
580 error_code = cst_err_val_1
581 WHERE interface_id = p_d_interface_id;
582 p_match_ind := '24';
583 IF l_enable_log = 'Y' THEN
584 --vrathi: Add specific message to log
585 Igs_Ad_Imp_001.set_message('IGS_PE_PID_INFO_MISS');
586 igs_ad_imp_001.logerrormessage(p_record => p_d_interface_id, p_error => 'E001', p_match_ind => '24');
587 END IF;
588 END IF;
589 END igs_ad_find_duplicates_imp_pp;
590
591 PROCEDURE igs_ad_find_duplicates_imp_pa
592 (p_d_match_set_id IN NUMBER,
593 p_d_interface_id IN NUMBER,
594 p_d_batch_id IN NUMBER,
595 p_c_addr_type IN VARCHAR2,
596 p_c_person_id_type IN VARCHAR2,
597 p_person_id OUT NOCOPY igs_ad_interface.person_id%TYPE,
598 p_match_ind OUT NOCOPY igs_ad_interface.match_ind%TYPE) AS
599
600 /*
601 || Created By : pkpatel
602 || Created On : 10-DEC-2001
603 || Purpose :
604 || Known limitations, enhancements or remarks :
605 || Change History :
606 || Who When What
607 || (reverse chronological order - newest change first)
608 || pkpatel 14-MAY-2002 Bug No: 2373468
609 || Removed the CLOSE imp_person_cur inside the FOR LOOP.
610 || ssawhney 21 may Bug 2381539, incorrect variables usages with dup match check
611 || pkpatel 30-MAY-2002 Bug 2377580, The addr_type was added in the WHERE clause of CURSOR imp_person_cur
612 || The parameter ethnic_origin was passed in the call to the procedure Igs_Pe_Identify_Dups.form_dup_whereclause
613 || pkpatel 10-OCT-2002 Bug No: 2603065
614 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
615 || pkpatel 22-JUN-2001 Bug no.2702536
616 || Added the parameters p_match_ind, p_person_id
617 || pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
618 */
619
620
621 l_prog_label VARCHAR2(100);
622 l_label VARCHAR2(100);
623 l_debug_str VARCHAR2(2000);
624 l_enable_log VARCHAR2(1);
625 l_request_id NUMBER;
626
627 CURSOR imp_person_cur(cp_d_interface_id igs_ad_imp_matches_pa_v.interface_id%TYPE,
628 cp_c_addr_type igs_ad_imp_matches_pa_v.addr_type%TYPE)
629 IS
630 SELECT *
631 FROM igs_ad_imp_matches_pa_v
632 WHERE interface_id = cp_d_interface_id
633 AND
634 ( addr_type = cp_c_addr_type OR addr_type IS NULL OR Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y');
635
636
637 CURSOR party_site_cur(cp_person_id igs_pe_person.person_id%TYPE,
638 cp_addr_type igs_ad_imp_matches_pa_v.addr_type%TYPE) IS
639 SELECT PS.party_site_id
640 FROM hz_party_sites PS,hz_party_site_uses PSU
641 WHERE PS.party_site_id = PSU.party_site_id AND
642 PS.party_id = cp_person_id AND
643 PSU.site_use_type = cp_addr_type;
644
645 CURSOR prim_party_site_cur(cp_person_id igs_pe_person.person_id%TYPE) IS
646 SELECT PS.party_site_id
647 FROM hz_party_sites PS
648 WHERE PS.party_id = cp_person_id AND
649 PS.identifying_address_flag = 'Y';
650
651 x_lvcExactSelectClause VARCHAR2(32000);
652 x_lvcPartialSelectClause VARCHAR2(32000);
653 x_match_cnt NUMBER := 0;
654 l_errbuf VARCHAR2(10);
655 l_retcode NUMBER;
656 l_person_id igs_pe_person.person_id%TYPE;
657 i NUMBER(3):=0;
658 l_rowid VARCHAR2(25);
659 l_pk NUMBER(15);
660
661 l_cursor_id NUMBER(15);
662 l_cursor_id1 NUMBER(15);
663 l_num_of_rows NUMBER(15);
664 l_dsql_debug VARCHAR2(4000);
665 l_view_passed VARCHAR2(50);
666 BEGIN
667 -- Call Log header
668
669 l_prog_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_pa';
670 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_pa.';
671 l_enable_log := igs_ad_imp_001.g_enable_log;
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_009.igs_ad_find_duplicates_imp_pa.begin';
680 l_debug_str := 'Igs_Ad_Imp_009.igs_ad_find_duplicates_imp_pa';
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
689 IF (Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N') THEN
690 l_view_passed := 'IGS_PE_DUP_MATCHES_PA_V';
691 ELSE
692 l_view_passed := 'IGS_PE_DUP_MATCHES_PRIM_PA_V';
693 END IF;
694
695 FOR imp_person_rec IN imp_person_cur(p_d_interface_id,p_c_addr_type) LOOP
696
697 IF ((Igs_Pe_Identify_Dups.g_addr_type_din = 'N' AND Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N')
698 AND((imp_person_rec.COUNTRY IS NULL) OR (imp_person_rec.ADDR_TYPE IS NULL)))-- new code
699 THEN
700 i:= 0;
701 ELSE
702 i := i+1;
703 Igs_Pe_Identify_Dups.form_dup_whereclause(
704 x_errbuf => l_errbuf,
705 x_retcode => l_retcode,
706 x_match_set_id =>p_d_match_set_id,
707 x_match_category =>'E',
708 x_view_name =>l_view_passed,
709 x_surname =>imp_person_rec.surname,
710 x_given_names =>imp_person_rec.given_names,
711 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
712 x_addr_line_1 =>imp_person_rec.addr_line_1,
713 x_addr_line_2 =>imp_person_rec.addr_line_2,
714 x_addr_line_3 =>imp_person_rec.addr_line_3,
715 x_addr_line_4 =>imp_person_rec.addr_line_4,
716 x_birth_dt =>imp_person_rec.birth_dt,
717 x_sex =>imp_person_rec.sex,
718 x_ethnic_origin =>imp_person_rec.ethnic_origin,
719 x_select_clause => x_lvcExactSelectClause,
720 x_addr_type =>imp_person_rec.addr_type,
721 x_city =>imp_person_rec.city,
722 x_state =>imp_person_rec.state,
723 x_province =>imp_person_rec.province,
724 x_county =>imp_person_rec.county,
725 x_country =>imp_person_rec.country,
726 x_postcode =>imp_person_rec.postcode
727 );
728
729 IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
730 l_cursor_id := dbms_sql.open_cursor;
731 fnd_dsql.set_cursor(l_cursor_id);
732
733 dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
734 fnd_dsql.do_binds;
735
736 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
737
738 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
739 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
740 l_dsql_debug := fnd_dsql.get_text(TRUE);
741 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
742 */
743 LOOP
744 -- fetch a row
745 IF dbms_sql.fetch_rows(l_cursor_id) > 0 THEN
746 x_match_cnt := x_match_cnt+1;
747 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
748
749 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
750 FOR temp_cur IN Party_Site_Cur(l_person_ID,p_c_addr_type) LOOP
751 lnParty_Site_ID := temp_cur.Party_Site_ID;
752 END LOOP;
753 ELSIF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' THEN
754 FOR temp_cur IN prim_Party_Site_Cur(l_person_ID) LOOP
755 lnParty_Site_ID := temp_cur.Party_Site_ID;
756 END LOOP;
757 END IF;
758
759
760 /* Insert into igs_ad_imp_near_match, all the duplicate records */
761 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
762 (x_rowid =>l_rowid,
763 x_Org_ID => lnOrg_ID,
764 x_near_mtch_id=>l_pk,
765 x_interface_id=>p_d_interface_id,
766 x_person_id=>l_person_id,
767 x_match_ind=>'E',
768 x_action=>'D',
769 x_addr_type=>p_c_addr_type,
770 x_person_id_type=>p_c_person_id_type,
771 x_match_set_id=>p_d_match_set_id,
772 x_mode =>'R',
773 x_party_SITE_ID => lnParty_Site_ID);
774
775 ELSE
776 EXIT;
777 END IF;
778 END LOOP; /* End Loop for dup_matches_cur */
779 dbms_sql.close_cursor(l_cursor_id);
780
781 /*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
782 IF x_match_cnt = 1 THEN /* Only One Match is Found */
783 UPDATE igs_ad_interface
784 SET match_ind = cst_mi_val_12,
785 person_id = l_person_id
786 WHERE interface_id = imp_person_rec.interface_id;
787
788 p_person_id := l_person_id;
789 p_match_ind := '12';
790 RETURN;
791
792 ELSIF x_match_cnt > 1 THEN
793 UPDATE igs_ad_interface
794 SET match_ind = cst_mi_val_13,
795 error_code = cst_err_val_2,
796 STATUS = cst_stat_val_3
797 WHERE interface_id = imp_person_rec.interface_id;
798
799 p_match_ind := '13';
800
801 IF l_enable_log = 'Y' THEN
802 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E002', p_match_ind => '13');
803 END IF;
804
805
806 RETURN;
807 END IF;
808 END IF;
809
810 IF x_match_cnt = 0 THEN
811 /* If Exact Match is not found then go for Partial Match */
812 Igs_Pe_Identify_Dups.form_dup_whereclause(
813 x_errbuf => l_errbuf,
814 x_retcode => l_retcode,
815 x_match_set_id =>p_d_match_set_id,
816 x_match_category =>'P', -- bug Bug 2381539, it was being passed as E
817 x_view_name =>l_view_passed,
818 x_surname =>imp_person_rec.surname,
819 x_given_names =>imp_person_rec.given_names,
820 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
821 x_birth_dt =>imp_person_rec.birth_dt,
822 x_sex =>imp_person_rec.sex,
823 x_ethnic_origin =>imp_person_rec.ethnic_origin,
824 x_select_clause => x_lvcPartialSelectClause, -- bug Bug 2381539, it was being taken as exactselect
825 x_addr_type =>imp_person_rec.addr_type,
826 x_addr_line_1 =>imp_person_rec.addr_line_1,
827 x_addr_line_2 =>imp_person_rec.addr_line_2,
828 x_addr_line_3 =>imp_person_rec.addr_line_3,
829 x_addr_line_4 =>imp_person_rec.addr_line_4,
830 x_city =>imp_person_rec.city,
831 x_state =>imp_person_rec.state,
832 x_province =>imp_person_rec.province,
833 x_county =>imp_person_rec.county,
834 x_country =>imp_person_rec.country,
835 x_postcode =>imp_person_rec.postcode
836 );
837
838 IF x_lvcPartialSelectClause IS NOT NULL THEN
839
840 /* Execute the Partial Select Clause */
841 l_cursor_id1 := dbms_sql.open_cursor;
842 fnd_dsql.set_cursor(l_cursor_id1);
843
844 dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
845 fnd_dsql.do_binds;
846
847 dbms_sql.define_column(l_cursor_id1, 1, l_person_id);
848
849 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id1);
850
851 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
852 l_dsql_debug := fnd_dsql.get_text(TRUE);
853 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
854 */
855 LOOP
856 -- fetch a row
857 IF dbms_sql.fetch_rows(l_cursor_id1) > 0 THEN
858
859 x_match_cnt := x_match_cnt+1;
860
861 dbms_sql.column_value(l_cursor_id1, 1, l_person_id);
862
863 --Get Party_Site_ID From the Cursor
864 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
865 FOR temp_cur IN Party_Site_Cur(l_person_id,p_c_addr_type) LOOP
866 lnParty_Site_ID := temp_cur.Party_Site_ID;
867 END LOOP;
868 ELSIF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' THEN
869 FOR temp_cur IN prim_Party_Site_Cur(l_person_id) LOOP
870 lnParty_Site_ID := temp_cur.Party_Site_ID;
871 END LOOP;
872 END IF;
873
874 /* Insert into igs_ad_imp_near_match_int, all the duplicate records */
875 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
876 (x_rowid =>l_rowid,
877 x_Org_ID => lnOrg_ID,
878 x_near_mtch_id=>l_pk,
879 x_interface_id=>p_d_interface_id,
880 x_person_id=>l_person_id,
881 x_match_ind=>'P',
882 x_action=>'D',
883 x_addr_type=>p_c_addr_type,
884 x_person_id_type=>p_c_person_id_type,
885 x_match_set_id=>p_d_match_set_id,
886 x_mode =>'R',
887 x_party_Site_ID => lnParty_Site_ID);
888
889 ELSE
890 EXIT;
891 END IF;
892
893 END LOOP; /* End Loop for dup_matches_cur */
894
895 dbms_sql.close_cursor(l_cursor_id1);
896
897 IF x_match_cnt = 0 THEN /* No Partial match not found */
898 UPDATE igs_ad_interface
899 SET match_ind = cst_mi_val_11
900 WHERE interface_id = imp_person_rec.interface_id;
901
902 p_match_ind := '11';
903
904 RETURN;
905 ELSE
906 UPDATE igs_ad_interface
907 SET match_ind = '14',
908 ERROR_CODE = 'E003',
909 STATUS = '3'
910 WHERE interface_id = imp_person_rec.interface_id;
911
912 p_match_ind := '14';
913
914 IF l_enable_log = 'Y' THEN
915 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E003', p_match_ind => '14');
916 END IF;
917
918 RETURN;
919 END IF;
920 END IF;
921 END IF;
922 END IF;
923 END LOOP;
924
925 IF i = 0 THEN
926 UPDATE igs_ad_interface
927 SET status = '3',
928 match_ind = '24',
929 error_code = 'E001'
930 WHERE interface_id = p_d_interface_id;
931
932 p_match_ind := '24';
933
934 IF l_enable_log = 'Y' THEN
935 --vrathi: Add specific message to log
936 Igs_Ad_Imp_001.set_message('IGS_PE_ADDR_INFO_MISS');
937 igs_ad_imp_001.logerrormessage(p_record => p_d_interface_id, p_error => 'E001', p_match_ind => '24');
938 END IF;
939
940 END IF;
941
942 END igs_ad_find_duplicates_imp_pa;
943
944 PROCEDURE igs_ad_find_duplicates_imp_ppa
945 (p_d_match_set_id IN NUMBER,
946 p_d_interface_id IN NUMBER,
947 p_d_batch_id IN NUMBER,
948 p_c_addr_type IN VARCHAR2,
949 p_c_person_id_type IN VARCHAR2,
950 p_person_id OUT NOCOPY igs_ad_interface.person_id%TYPE,
951 p_match_ind OUT NOCOPY igs_ad_interface.match_ind%TYPE)
952 AS
953 /*
954 || Created By : pkpatel
955 || Created On : 10-DEC-2001
956 || Purpose :
957 || Known limitations, enhancements or remarks :
958 || Change History :
959 || Who When What
960 || (reverse chronological order - newest change first)
961 || pkpatel 30-MAY-2002 Bug 2377580, The parameters x_person_id_type,x_api_person_id were passed in the call
962 || to the procedure Igs_Pe_Identify_Dups.form_dup_whereclause
963 || pkpatel 10-OCT-2002 Bug No: 2603065
964 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
965 || pkpatel 22-JUN-2001 Bug no.2702536
966 || Added the parameters p_match_ind, p_person_id
967 || pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
968 || gmaheswa 24-March-2006 Bug 4218763 Modified imp_person_cur to condsider Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' condition.
969 */
970
971
972 l_prog_label VARCHAR2(100);
973 l_label VARCHAR2(100);
974 l_debug_str VARCHAR2(2000);
975 l_enable_log VARCHAR2(1);
976 l_request_id NUMBER;
977
978 CURSOR imp_person_cur(cp_d_interface_id igs_ad_imp_matches_ppa_v.interface_id%TYPE,
979 cp_c_addr_type igs_ad_imp_matches_ppa_v.addr_type%TYPE,
980 cp_c_person_id_type igs_ad_imp_matches_ppa_v.person_id_type%TYPE )IS
981 SELECT *
982 FROM igs_ad_imp_matches_ppa_v
983 WHERE interface_id = cp_d_interface_id
984 AND (addr_type = cp_c_addr_type OR addr_type IS NULL OR Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y')
985 AND (person_id_type = cp_c_person_id_type OR person_id_type IS NULL);
986
987
988 CURSOR party_site_cur(cp_person_id igs_pe_person.person_id%TYPE,
989 cp_addr_type igs_ad_imp_matches_pa_v.addr_type%TYPE) IS
990 SELECT PS.party_site_id
991 FROM hz_party_sites PS,hz_party_site_uses PSU
992 WHERE PS.party_site_id = PSU.party_site_id AND
993 PS.party_id = cp_person_id AND
994 PSU.site_use_type = cp_addr_type;
995
996 CURSOR prim_party_site_cur(cp_person_id igs_pe_person.person_id%TYPE) IS
997 SELECT PS.party_site_id
998 FROM hz_party_sites PS
999 WHERE PS.party_id = cp_person_id AND
1000 PS.identifying_address_flag = 'Y';
1001
1002 x_match_cnt NUMBER := 0;
1003 l_errbuf VARCHAR2(10);
1004 l_retcode NUMBER;
1005 x_lvcExactSelectClause VARCHAR2(32000);
1006 x_lvcPartialSelectClause VARCHAR2(32000);
1007 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE;
1008 i NUMBER(3):=0;
1009 l_rowid VARCHAR2(25);
1010 l_pk NUMBER(15);
1011
1012 l_cursor_id NUMBER(15);
1013 l_cursor_id1 NUMBER(15);
1014 l_num_of_rows NUMBER(15);
1015 l_dsql_debug VARCHAR2(4000);
1016 l_view_passed VARCHAR2(50);
1017 BEGIN
1018
1019 -- Call Log header
1020
1021 l_prog_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_ppa';
1022 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_ppa.';
1023 l_enable_log := igs_ad_imp_001.g_enable_log;
1024
1025 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1026
1027 IF (l_request_id IS NULL) THEN
1028 l_request_id := fnd_global.conc_request_id;
1029 END IF;
1030
1031 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_duplicates_imp_ppa.begin';
1032 l_debug_str := 'Igs_Ad_Imp_009.igs_ad_find_duplicates_imp_ppa';
1033
1034 fnd_log.string_with_context( fnd_log.level_procedure,
1035 l_label,
1036 l_debug_str, NULL,
1037 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1038 END IF;
1039
1040 IF (Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N')THEN
1041 l_view_passed := 'IGS_PE_DUP_MATCHES_PPA_V';
1042 ELSE
1043 l_view_passed := 'IGS_PE_DUP_MATCHES_PRIM_PPA_V';
1044 END IF;
1045
1046 FOR imp_person_rec IN imp_person_cur(p_d_interface_id,p_c_addr_type,p_c_person_id_type) LOOP
1047
1048 IF ((Igs_Pe_Identify_Dups.g_addr_type_din = 'N' AND
1049 Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N')AND
1050 ((imp_person_rec.COUNTRY IS NULL) OR
1051 (imp_person_rec.ADDR_TYPE IS NULL))) OR
1052 ((Igs_Pe_Identify_Dups.g_person_id_type_din = 'N') AND
1053 ((imp_person_rec.ALTERNATE_ID IS NULL)
1054 OR (imp_person_rec.PERSON_ID_TYPE IS NULL)))
1055
1056 THEN
1057 i:= 0;
1058 ELSE
1059
1060 i := i+1;
1061 Igs_Pe_Identify_Dups.form_dup_whereclause(
1062 x_errbuf => l_errbuf,
1063 x_retcode => l_retcode,
1064 x_match_set_id =>p_d_match_set_id,
1065 x_match_category =>'E',
1066 x_view_name => l_view_passed,
1067 x_surname =>imp_person_rec.surname,
1068 x_given_names =>imp_person_rec.given_names,
1069 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
1070 x_person_id_type =>imp_person_rec.person_id_type,
1071 x_api_person_id =>imp_person_rec.alternate_id,
1072 x_addr_line_1 =>imp_person_rec.addr_line_1,
1073 x_addr_line_2 =>imp_person_rec.addr_line_2,
1074 x_addr_line_3 =>imp_person_rec.addr_line_3,
1075 x_addr_line_4 =>imp_person_rec.addr_line_4,
1076 x_birth_dt =>imp_person_rec.birth_dt,
1077 x_sex =>imp_person_rec.sex,
1078 x_ethnic_origin =>imp_person_rec.ethnic_origin,
1079 x_select_clause =>x_lvcExactSelectClause,
1080 x_addr_type =>imp_person_rec.addr_type,
1081 x_city =>imp_person_rec.city,
1082 x_state =>imp_person_rec.state,
1083 x_province =>imp_person_rec.province,
1084 x_county =>imp_person_rec.county,
1085 x_country =>imp_person_rec.country,
1086 x_postcode =>imp_person_rec.postcode
1087 );
1088
1089 IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
1090 l_cursor_id := dbms_sql.open_cursor;
1091 fnd_dsql.set_cursor(l_cursor_id);
1092
1093 dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
1094 fnd_dsql.do_binds;
1095
1096 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
1097
1098 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
1099 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
1100 l_dsql_debug := fnd_dsql.get_text(TRUE);
1101 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
1102 */
1103 LOOP
1104 -- fetch a row
1105 IF dbms_sql.fetch_rows(l_cursor_id) > 0 THEN
1106
1107 x_match_cnt := x_match_cnt+1;
1108
1109 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
1110
1111 --Get Party_Site_ID From the Cursor based on primary address indicator
1112 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
1113 FOR temp_cur IN Party_Site_Cur(l_person_ID,p_c_addr_type) LOOP
1114 lnParty_Site_ID := temp_cur.Party_Site_ID;
1115 END LOOP;
1116 ELSIF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' THEN
1117 FOR temp_cur IN prim_Party_Site_Cur(l_person_ID) LOOP
1118 lnParty_Site_ID := temp_cur.Party_Site_ID;
1119 END LOOP;
1120 END IF;
1121
1122 /* Insert into igs_ad_imp_near_match, all the duplicate records */
1123 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
1124 (x_rowid =>l_rowid,
1125 x_Org_ID => lnOrg_ID,
1126 x_near_mtch_id=>l_pk,
1127 x_interface_id=>p_d_interface_id,
1128 x_person_id=>l_person_id,
1129 x_match_ind=>'E',
1130 x_action=>'D',
1131 x_addr_type=>p_c_addr_type,
1132 x_person_id_type=>p_c_person_id_type,
1133 x_match_set_id=>p_d_match_set_id,
1134 x_mode =>'R',
1135 x_party_Site_ID => lnParty_Site_ID);
1136
1137 ELSE
1138 EXIT;
1139 END IF;
1140 END LOOP; /* End Loop for dup_matches_cur */
1141 dbms_sql.close_cursor(l_cursor_id);
1142
1143 /*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
1144 IF x_match_cnt = 1 THEN /* Only One Match is Found */
1145
1146 UPDATE igs_ad_interface
1147 SET match_ind = cst_mi_val_12,
1148 person_id = l_person_id
1149 WHERE interface_id = imp_person_rec.interface_id;
1150
1151 p_person_id := l_person_id;
1152 p_match_ind := '12';
1153
1154 RETURN;
1155 ELSIF x_match_cnt > 1 THEN
1156 UPDATE igs_ad_interface
1157 SET match_ind = '13',
1158 ERROR_CODE = 'E002',
1159 STATUS = '3'
1160 WHERE interface_id = imp_person_rec.interface_id;
1161
1162 p_match_ind := '13';
1163
1164 IF l_enable_log = 'Y' THEN
1165 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E002', p_match_ind => '13');
1166 END IF;
1167
1168 RETURN;
1169 END IF;
1170 END IF;
1171 IF x_match_cnt = 0 THEN
1172 /* If Exact Match is not found then go for Partial Match */
1173
1174 -- Bug 2377580, the parameters x_person_id_type and x_api_person_id were added
1175 Igs_Pe_Identify_Dups.form_dup_whereclause(
1176 x_errbuf => l_errbuf,
1177 x_retcode => l_retcode,
1178 x_match_set_id =>p_d_match_set_id,
1179 x_match_category =>'P', -- bug Bug 2381539, this was being passed as E.
1180 x_view_name =>l_view_passed,
1181 x_surname =>imp_person_rec.surname,
1182 x_given_names =>imp_person_rec.given_names,
1183 x_pref_alternate_id =>imp_person_rec.pref_alternate_id,
1184 x_birth_dt =>imp_person_rec.birth_dt,
1185 x_sex =>imp_person_rec.sex,
1186 x_person_id_type =>imp_person_rec.person_id_type,
1187 x_api_person_id =>imp_person_rec.alternate_id,
1188 x_ethnic_origin =>imp_person_rec.ethnic_origin,
1189 x_select_clause =>x_lvcPartialSelectClause, -- bug Bug 2381539, it was being taken as exactselect
1190 x_addr_type =>imp_person_rec.addr_type,
1191 x_addr_line_1 =>imp_person_rec.addr_line_1,
1192 x_addr_line_2 =>imp_person_rec.addr_line_2,
1193 x_addr_line_3 =>imp_person_rec.addr_line_3,
1194 x_addr_line_4 =>imp_person_rec.addr_line_4,
1195 x_city =>imp_person_rec.city,
1196 x_state =>imp_person_rec.state,
1197 x_province =>imp_person_rec.province,
1198 x_county =>imp_person_rec.county,
1199 x_country =>imp_person_rec.country,
1200 x_postcode =>imp_person_rec.postcode
1201 );
1202
1203 IF x_lvcPartialSelectClause IS NOT NULL THEN
1204 /* Exceute the Partial Select Clause */
1205 l_cursor_id1 := dbms_sql.open_cursor;
1206 fnd_dsql.set_cursor(l_cursor_id1);
1207
1208 dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
1209 fnd_dsql.do_binds;
1210
1211 dbms_sql.define_column(l_cursor_id1, 1, l_person_id);
1212
1213 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id1);
1214
1215 /* This will print the Dynamic SQL statement prepared. Can be uncommented when testing.
1216 l_dsql_debug := fnd_dsql.get_text(TRUE);
1217 Igs_Ad_Imp_001.logDetail('l_dsql_debug :'||l_dsql_debug);
1218 */
1219 LOOP
1220 -- fetch a row
1221 IF dbms_sql.fetch_rows(l_cursor_id1) > 0 THEN
1222
1223 x_match_cnt := x_match_cnt+1;
1224
1225 dbms_sql.column_value(l_cursor_id1, 1, l_person_id);
1226
1227 --Get Party_Site_ID From the Cursor Based on primary address indicator
1228 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
1229 FOR temp_cur IN Party_Site_Cur(l_person_ID,p_c_addr_type) LOOP
1230 lnParty_Site_ID := temp_cur.Party_Site_ID;
1231 END LOOP;
1232 ELSIF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' THEN
1233 FOR temp_cur IN prim_Party_Site_Cur(l_person_ID) LOOP
1234 lnParty_Site_ID := temp_cur.Party_Site_ID;
1235 END LOOP;
1236 END IF;
1237
1238 Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
1239 (x_rowid =>l_rowid,
1240 x_Org_ID => lnOrg_ID,
1241 x_near_mtch_id=>l_pk,
1242 x_interface_id=>p_d_interface_id,
1243 x_person_id=>l_person_id,
1244 x_match_ind=>'P',
1245 x_action=>'D',
1246 x_addr_type=>p_c_addr_type,
1247 x_person_id_type=>p_c_person_id_type,
1248 x_match_set_id=>p_d_match_set_id,
1249 x_mode =>'R',
1250 x_party_Site_ID => lnParty_Site_ID );
1251 ELSE
1252 EXIT;
1253 END IF;
1254 END LOOP; /* End Loop for dup_matches_cur */
1255
1256 dbms_sql.close_cursor(l_cursor_id1);
1257
1258 IF x_match_cnt = 0 THEN /* No Partial match not found */
1259 UPDATE igs_ad_interface
1260 SET match_ind = cst_mi_val_11
1261 WHERE interface_id = imp_person_rec.interface_id;
1262
1263 p_match_ind := '11';
1264
1265 RETURN;
1266 ELSE
1267 UPDATE igs_ad_interface
1268 SET match_ind = cst_mi_val_14,
1269 ERROR_CODE = cst_err_val_3,
1270 STATUS = cst_stat_val_3
1271 WHERE interface_id = imp_person_rec.interface_id;
1272
1273 p_match_ind := '14';
1274
1275 IF l_enable_log = 'Y' THEN
1276 igs_ad_imp_001.logerrormessage(p_record => imp_person_rec.interface_id, p_error => 'E003', p_match_ind => '14');
1277 END IF;
1278
1279 RETURN;
1280 END IF;
1281 END IF;
1282 END IF;
1283 END IF;
1284 END LOOP;
1285 IF i = 0 THEN
1286 UPDATE igs_ad_interface
1287 SET status = cst_stat_val_3,
1288 match_ind = cst_mi_val_24,
1289 error_code = cst_err_val_1
1290 WHERE interface_id = p_d_interface_id;
1291
1292 p_match_ind := '24';
1293 --vrathi: Add specific message to log
1294
1295 IF l_enable_log = 'Y' THEN
1296 Igs_Ad_Imp_001.set_message('IGS_PE_ADDR_PID_MISS');
1297 igs_ad_imp_001.logerrormessage(p_record => p_d_interface_id, p_error => 'E001', p_match_ind => '24');
1298 END IF;
1299 END IF;
1300
1301 END igs_ad_find_duplicates_imp_ppa;
1302
1303 PROCEDURE igs_ad_imp_find_dup_persons
1304 (p_d_batch_id IN NUMBER,
1305 p_d_source_type_id IN NUMBER,
1306 p_d_match_set_id IN NUMBER,
1307 p_interface_id IN igs_ad_interface.interface_id%TYPE,
1308 p_match_ind IN OUT NOCOPY igs_ad_interface.match_ind%TYPE,
1309 p_person_id OUT NOCOPY igs_ad_interface.person_id%TYPE,
1310 p_addr_type IN igs_pe_mtch_set_data.VALUE%TYPE,
1311 p_person_id_type IN igs_pe_mtch_set_data.VALUE%TYPE) AS
1312 /*
1313 || Created By : pkpatel
1314 || Created On : 10-DEC-2001
1315 || Purpose :
1316 || Known limitations, enhancements or remarks :
1317 || Change History :
1318 || Who When What
1319 || (reverse chronological order - newest change first)
1320 || pkpatel 30-MAY-2002 Bug 2377580, The parameters x_person_id_type,x_api_person_id were passed in the call
1321 || to the procedure Igs_Pe_Identify_Dups.form_dup_whereclause
1322 || pkpatel 10-OCT-2002 Bug No: 2603065
1323 || Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
1324 || pkpatel 22-JUN-2001 Bug no.2702536
1325 || Added the parameters p_match_ind, p_person_id
1326 || pkpatel 22-JUN-2001 Bug no.2702536
1327 || Added the parameters p_match_ind, p_person_id, p_addr_type and p_person_id_type.
1328 || Implemented the new record level duplicate check.
1329 || asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
1330 Calling the logic to DELETE from igs_ad_near_mtch for the interface ids at one shot.
1331 */
1332
1333 l_prog_label VARCHAR2(100);
1334 l_label VARCHAR2(100);
1335 l_debug_str VARCHAR2(2000);
1336 l_enable_log VARCHAR2(1);
1337 l_request_id NUMBER;
1338
1339 -- ssawhney SWS104, check for ethnic origin if it exists in the matchset.
1340 CURSOR c_stat_data_element(cp_d_match_set_id igs_pe_mtch_set_data.match_set_id%TYPE,
1341 cp_lookup_code igs_pe_mtch_set_data.data_element%TYPE,
1342 cp_din VARCHAR2)
1343 IS
1344 SELECT md.exact_include
1345 FROM igs_pe_mtch_set_data md
1346 WHERE md.match_set_id = cp_d_match_set_id
1347 AND md.data_element = cp_lookup_code
1348 AND md.drop_if_null = cp_din;
1349
1350 CURSOR c_stat_data_exists (cp_interface_id IGS_AD_INTERFACE.INTERFACE_ID%TYPE,
1351 cp_status IGS_AD_INTERFACE.STATUS%TYPE) IS
1352 SELECT 'X'
1353 FROM igs_ad_stat_int
1354 WHERE interface_id = cp_interface_id AND
1355 status = cp_status;
1356
1357 stat_data_element_rec c_stat_data_element%ROWTYPE;
1358 stat_data_exists_rec c_stat_data_exists%ROWTYPE;
1359
1360 BEGIN
1361
1362 -- Call Log header
1363
1364 l_prog_label := 'igs.plsql.igs_ad_imp_009.igs_ad_imp_find_dup_persons';
1365 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_imp_find_dup_persons.';
1366 l_enable_log := igs_ad_imp_001.g_enable_log;
1367
1368 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1369
1370 IF (l_request_id IS NULL) THEN
1371 l_request_id := fnd_global.conc_request_id;
1372 END IF;
1373
1374 l_label := 'igs.plsql.igs_ad_imp_009.igs_ad_find_dup_persons.begin';
1375 l_debug_str := 'Igs_Ad_Imp_009.igs_ad_find_dup_persons for Interface ID: '||p_interface_id||' with Address type: '||p_addr_type
1376 ||' Person ID Type: '||p_person_id_type;
1377
1378 fnd_log.string_with_context( fnd_log.level_procedure,
1379 l_label,
1380 l_debug_str, NULL,
1381 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1382 END IF;
1383
1384 IF p_match_ind IN ('15','16','17') THEN
1385 NULL;
1386 ELSE
1387 --ssawhney SWS104, remove statistics to be mandatory.
1388 OPEN c_stat_data_element(p_d_match_set_id,'ETHNIC_ORIGIN','N');
1389 FETCH c_stat_data_element INTO stat_data_element_rec;
1390 IF stat_data_element_rec.exact_include ='Y' THEN
1391 -- this means ethnic origin is included in the matchset.check if stat data exists in interface table
1392 OPEN c_stat_data_exists(p_interface_id,'2');
1393 FETCH c_stat_data_exists INTO stat_data_exists_rec;
1394 IF c_stat_data_exists%NOTFOUND THEN
1395 -- this means ethnic origin is included but there is no statistics record in the interface table.ERROR
1396 CLOSE c_stat_data_exists;
1397 RAISE no_data_found;
1398 END IF;
1399 IF c_stat_data_exists%ISOPEN THEN
1400 CLOSE c_stat_data_exists;
1401 END IF;
1402 END IF;
1403 CLOSE c_stat_data_element;
1404
1405 IF p_addr_type IS NULL AND p_person_id_type IS NULL AND IGS_PE_IDENTIFY_DUPS.g_primary_addr_flag = 'N' THEN
1406 igs_ad_find_duplicates_imp_p(p_d_match_set_id =>p_d_match_set_id,
1407 p_d_batch_id =>p_d_batch_id,
1408 p_d_interface_id =>p_interface_id,
1409 p_c_addr_type =>p_addr_type,
1410 p_c_person_id_type =>p_person_id_type,
1411 p_person_id =>p_person_id,
1412 p_match_ind =>p_match_ind);
1413
1414 ELSIF (p_person_id_type IS NULL) AND ( p_addr_type IS NOT NULL OR IGS_PE_IDENTIFY_DUPS.g_primary_addr_flag = 'Y') THEN
1415 igs_ad_find_duplicates_imp_pa(p_d_match_set_id =>p_d_match_set_id,
1416 p_d_batch_id =>p_d_batch_id,
1417 p_d_interface_id =>p_interface_id,
1418 p_c_addr_type =>p_addr_type,
1419 p_c_person_id_type =>p_person_id_type,
1420 p_person_id =>p_person_id,
1421 p_match_ind =>p_match_ind);
1422 ELSIF p_addr_type IS NULL AND p_person_id_type IS NOT NULL AND IGS_PE_IDENTIFY_DUPS.g_primary_addr_flag = 'N' THEN
1423 igs_ad_find_duplicates_imp_pp(p_d_match_set_id =>p_d_match_set_id,
1424 p_d_batch_id =>p_d_batch_id,
1425 p_d_interface_id =>p_interface_id,
1426 p_c_addr_type =>p_addr_type,
1427 p_c_person_id_type =>p_person_id_type,
1428 p_person_id =>p_person_id,
1429 p_match_ind =>p_match_ind);
1430 ELSIF p_person_id_type IS NOT NULL AND (p_addr_type IS NOT NULL OR IGS_PE_IDENTIFY_DUPS.g_primary_addr_flag = 'Y') THEN
1431 igs_ad_find_duplicates_imp_ppa(p_d_match_set_id =>p_d_match_set_id,
1432 p_d_batch_id =>p_d_batch_id,
1433 p_d_interface_id =>p_interface_id,
1434 p_c_addr_type =>p_addr_type,
1435 p_c_person_id_type=>p_person_id_type,
1436 p_person_id =>p_person_id,
1437 p_match_ind =>p_match_ind);
1438 END IF;
1439 END IF;
1440
1441
1442 EXCEPTION
1443 WHEN NO_DATA_FOUND THEN
1444 IF c_stat_data_exists%ISOPEN THEN
1445 CLOSE c_stat_data_exists;
1446 END IF;
1447 IF c_stat_data_element%ISOPEN THEN
1448 CLOSE c_stat_data_element;
1449 END IF;
1450 UPDATE igs_ad_interface
1451 SET status='3', ERROR_CODE='E177'
1452 WHERE interface_id = p_interface_id;
1453
1454 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1455
1456 IF (l_request_id IS NULL) THEN
1457 l_request_id := fnd_global.conc_request_id;
1458 END IF;
1459
1460 l_label := 'igs_ad_imp_009.igs_ad_imp_find_dup_persons.exception';
1461
1462 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1463 fnd_message.set_token('INTERFACE_ID',p_interface_id);
1464 fnd_message.set_token('ERROR_CD','E177');
1465
1466 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1467
1468 fnd_log.string_with_context( fnd_log.level_exception,
1469 l_label,
1470 l_debug_str, NULL,
1471 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1472 END IF;
1473
1474 IF l_enable_log = 'Y' THEN
1475 igs_ad_imp_001.logerrormessage(p_interface_id,'E177');
1476 END IF;
1477
1478 END igs_ad_imp_find_dup_persons;
1479 -- 2
1480 /*removed the procedure SET_STAT_MATC_RVW_DIS_RCDS as part of bug 3191401*/
1481
1482 END Igs_Ad_Imp_009;