1 PACKAGE BODY Igs_Pe_Identify_Dups AS
2 /* $Header: IGSPE03B.pls 120.0 2005/06/01 15:41:13 appldev noship $ */
3
4 /*
5 || Created By : prabhat.patel
6 || Created On : 09-OCT-2002
7 || Purpose : Handles the Unique Constraint logic defined for the columns.
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || ssaleem 10-nov-2004 3877652 - Handling Inactive Persons
12 || ssawhney 25-jun-2003 3005910 - Devry MCS issue, making ADDRESS and PERSONIDGROUP changes
13 || npalanis 26-may-2003 2853529 - Implementation of bind variables for family details build
14 || npalanis 22-APR-2003 BUG:2832980
15 || variable l_location_id is removed as it is not used anywhere
16 || pkpatel 10-OCT-2002 Bug NO: 2603065
17 || REPLACE single quote with double quotes to form proper SELECT clause when any of the
18 || parameter value contains an Apostrophe in procedure form_dup_whereclause
19 || pkpatel 10-JUN-2003 Bug 2940810
20 || PKM SQL Bind issue(Modified literal to Bind variables)
21 || Stubbed the find_duplicates procedures
22 || gmaheswa 8-OCT-2003 Bug 3146324
23 || Match Criteria Sets Enhancement
24 || (reverse chronological order - newest change first)
25 */
26
27 PROCEDURE find_dup_main(
28 ERRBUF OUT NOCOPY VARCHAR2,
29 RETCODE OUT NOCOPY VARCHAR2,
30 x_match_set_id IN NUMBER,
31 x_report_requested IN VARCHAR2)
32 IS
33 BEGIN
34 NULL;
35 END find_dup_main;
36
37
38 PROCEDURE find_duplicates (
39 x_errbuf IN OUT NOCOPY VARCHAR2,
40 x_retcode IN OUT NOCOPY VARCHAR2,
41 x_match_set_id IN NUMBER)
42 IS
43 BEGIN
44 NULL;
45 END find_duplicates;
46
47
48 PROCEDURE find_duplicates_p(
49 x_errbuf IN OUT NOCOPY VARCHAR2,
50 x_retcode IN OUT NOCOPY VARCHAR2,
51 x_match_set_id IN NUMBER,
52 x_batch_id IN NUMBER)
53 IS
54 BEGIN
55 NULL;
56 END find_duplicates_p;
57
58 PROCEDURE find_duplicates_pp(
59 x_errbuf IN OUT NOCOPY VARCHAR2,
60 x_retcode IN OUT NOCOPY VARCHAR2,
61 x_match_set_id IN NUMBER,
62 x_batch_id IN NUMBER,
63 x_person_id_type IN VARCHAR2)
64 IS
65 BEGIN
66 NULL;
67 END find_duplicates_pp;
68
69 PROCEDURE find_duplicates_pa(
70 x_errbuf IN OUT NOCOPY VARCHAR2,
71 x_retcode IN OUT NOCOPY VARCHAR2,
72 x_match_set_id IN NUMBER,
73 x_batch_id IN NUMBER,
74 x_addr_type IN VARCHAR2)
75 IS
76 BEGIN
77 NULL;
78 END find_duplicates_pa;
79
80 PROCEDURE find_duplicates_ppa(
81 x_errbuf IN OUT NOCOPY VARCHAR2,
82 x_retcode IN OUT NOCOPY VARCHAR2,
83 x_match_set_id IN NUMBER,
84 x_batch_id IN NUMBER,
85 x_addr_type IN VARCHAR2,
86 x_person_id_type IN VARCHAR2)
87 IS
88 BEGIN
89 NULL;
90 END find_duplicates_ppa;
91
92
93 PROCEDURE form_dup_whereclause(
94 x_errbuf IN OUT NOCOPY VARCHAR2 ,
95 x_retcode IN OUT NOCOPY VARCHAR2 ,
96 x_match_set_id IN NUMBER,
97 x_match_category IN VARCHAR2,
98 x_view_name IN VARCHAR2,
99 x_person_id IN NUMBER,
100 x_surname IN VARCHAR2,
101 x_given_names IN VARCHAR2,
102 x_api_person_id IN VARCHAR2,
103 x_pref_alternate_id IN VARCHAR2,
104 x_person_id_type IN VARCHAR2,
105 x_birth_dt IN DATE,
106 x_sex IN VARCHAR2,
107 x_ethnic_origin IN VARCHAR2,
108 x_addr_type IN VARCHAR2,
109 x_addr_line_1 IN VARCHAR2,
110 x_addr_line_2 IN VARCHAR2,
111 x_addr_line_3 IN VARCHAR2,
112 x_addr_line_4 IN VARCHAR2,
113 x_city IN VARCHAR2,
114 x_state IN VARCHAR2,
115 x_province IN VARCHAR2,
116 x_county IN VARCHAR2,
117 x_country IN VARCHAR2,
118 x_postcode IN VARCHAR2,
119 x_select_clause IN OUT NOCOPY VARCHAR2
120
121 )
122 IS
123 /*
124 || Created By : prabhat.patel
125 || Created On : 09-OCT-2002
126 || Purpose : Handles the Unique Constraint logic defined for the columns.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || ssawhney 25-jun-2003 3005910 - Devry MCS issue, making ADDRESS and PERSONIDGROUP changes
131 || Addr_type and person_id_type to be appended with relevant fields
132 || in Partial Match, for Drop if null=N, addr_type not to be appended for Addr_line1 and country
133 || pkpatel 10-OCT-2002 Bug NO: 2593455
134 || REPLACE single quote with double quotes to form proper SELECT clause when any of the
135 || parameter value contains an Apostrophe
136 || Added the code for data element ALTERNATE_ID
137 || pkpatel 10-JUN-2003 Bug 2940810
138 || PKM SQL Bind issue(Modified literal to Bind variables)
139 || pkpatel 21-AUG-2003 Bug 3103195 (Removed Address line 1 from ANDing with other address element for partial match.
140 || Added DISTINCT clause for selecting the person id)
141 || asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
142 || gmaheswa 1-OCT-2003 Bug 3146324, Match Criteria Sets Enhancement
143 || asbala 28-nov-2003 Removed data element 'SURNAME_5_CHAR'
144 || gmaheswa 24-jan-2005 Bug: 3882788 Added the start_dt <> end_dt or end_dt is null check for person_id_type
145 || (reverse chronological order - newest change first)
146 */
147
148 l_surname igs_pe_dup_matches_ppa_v.surname%TYPE;
149 l_given_names igs_pe_dup_matches_ppa_v.given_names%TYPE;
150 l_given_name_1_char igs_pe_dup_matches_ppa_v.given_name_1_char%TYPE;
151 l_api_person_id igs_pe_dup_matches_ppa_v.api_person_id%TYPE;
152 l_pref_alternate_id igs_pe_dup_matches_ppa_v.pref_alternate_id%TYPE;
153 l_person_id_type igs_pe_dup_matches_ppa_v.person_id_type%TYPE;
154 l_sex igs_pe_dup_matches_ppa_v.sex%TYPE;
155 l_ethnic_origin igs_pe_dup_matches_ppa_v.ethnic_origin%TYPE;
156 l_addr_type igs_pe_dup_matches_ppa_v.addr_type%TYPE;
157 l_addr_line_1 igs_pe_dup_matches_ppa_v.addr_line_1%TYPE;
158 l_conct_address_lines igs_pe_dup_matches_prim_ppa_v.conct_address_lines%TYPE;
159 l_addr_line_2 igs_pe_dup_matches_ppa_v.addr_line_2%TYPE;
160 l_addr_line_3 igs_pe_dup_matches_ppa_v.addr_line_3%TYPE;
161 l_addr_line_4 igs_pe_dup_matches_ppa_v.addr_line_4%TYPE;
162 l_city igs_pe_dup_matches_ppa_v.city%TYPE;
163 l_state igs_pe_dup_matches_ppa_v.state%TYPE;
164 l_province igs_pe_dup_matches_ppa_v.province%TYPE;
165 l_county igs_pe_dup_matches_ppa_v.county%TYPE;
166 l_country igs_pe_dup_matches_ppa_v.country%TYPE;
167 l_postcode igs_pe_dup_matches_ppa_v.postcode%TYPE;
168
169
170 l_SelectClause VARCHAR2(2000);
171
172 l_default_sql_text VARCHAR2(2000);
173 l_final_sql_text VARCHAR2(32000);
174 l_partial_if_null BOOLEAN := FALSE;
175 l_partial_clause BOOLEAN := FALSE;
176
177 BEGIN
178
179 l_surname := UPPER(x_surname);
180 l_given_names := UPPER(x_given_names);
181 l_api_person_id :=UPPER(x_api_person_id);
182 l_pref_alternate_id :=UPPER(x_pref_alternate_id);
183 l_person_id_type := UPPER(x_person_id_type);
184 l_sex := UPPER(x_sex);
185 l_ethnic_origin := UPPER(x_ethnic_origin);
186 l_addr_type := UPPER(x_addr_type);
187 l_addr_line_1 := UPPER(x_addr_line_1);
188 l_addr_line_2 := UPPER(x_addr_line_2);
189 l_addr_line_3 := UPPER(x_addr_line_3);
190 l_addr_line_4 := UPPER(x_addr_line_4);
191 l_city := UPPER(x_city);
192 l_state := UPPER(x_state);
193 l_province := UPPER(x_province);
194 l_county := UPPER(x_county);
195 l_country := UPPER(x_country);
196 l_postcode := UPPER(x_postcode);
197
198 l_given_name_1_char := UPPER(SUBSTR(l_given_names, 1, 1));
199
200 l_conct_address_lines := l_addr_line_1||l_addr_line_2||l_addr_line_3||l_addr_line_4;
201
202
203 fnd_dsql.init;
204 fnd_dsql.add_text('SELECT DISTINCT person_id FROM '|| x_view_name||' WHERE UPPER(surname) = ');
205 fnd_dsql.add_bind(l_surname);
206 fnd_dsql.add_text(' AND UPPER(SUBSTR(given_names,1,1)) = ');
207 fnd_dsql.add_bind(l_given_name_1_char);
208
209 IF Igs_Pe_Identify_Dups.g_exclude_inactive_ind = 'Y' THEN
210 fnd_dsql.add_text(' AND STATUS = ');
211 fnd_dsql.add_bind('A');
212 END IF;
213
214
215 IF x_person_id IS NOT NULL THEN
216 fnd_dsql.add_text(' AND person_id <> ');
217 fnd_dsql.add_bind(x_person_id);
218 END IF;
219
220 -- Form the default select statement
221 l_default_sql_text := fnd_dsql.get_text(FALSE);
222
223 -- Form the match category is 'E', that exact append the where clause with 'AND'
224 IF x_match_category = 'E' THEN
225 FOR i IN 1..g_matchset_exact.COUNT LOOP
226
227 -- append the where clause with given name
228 IF g_matchset_exact(i).data_element = 'GIVEN_NAME' THEN
229 IF X_GIVEN_NAMES IS NULL THEN
230 IF g_partial_if_null = 'Y' THEN
231 l_partial_if_null := TRUE;
232 EXIT;
233 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
234 fnd_dsql.add_text(' AND given_names IS NULL');
235 END IF;
236 ELSE
237 fnd_dsql.add_text(' AND UPPER(given_names) = ');
238 fnd_dsql.add_bind(l_given_names);
239 END IF;
240 -- append the where clause with preferred alternate id
241 ELSIF g_matchset_exact(i).data_element = 'PREF_ALTERNATE_ID' THEN
242 IF x_pref_alternate_id IS NULL THEN
243 IF g_partial_if_null = 'Y' THEN
244 l_partial_if_null := TRUE;
245 EXIT;
246 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
247 fnd_dsql.add_text(' AND pref_alternate_id IS NULL');
248 END IF;
249 ELSE
250 fnd_dsql.add_text(' AND UPPER(pref_alternate_id) = ');
251 fnd_dsql.add_bind(l_pref_alternate_id);
252 END IF;
253 -- append the where clause with person id type
254 ELSIF g_matchset_exact(i).data_element = 'PERSON_ID_TYPE' THEN
255 IF x_api_person_id IS NULL THEN
256 IF g_partial_if_null = 'Y' THEN
257 l_partial_if_null := TRUE;
258 EXIT;
259 END IF;
260 ELSE
261 fnd_dsql.add_text(' AND person_id_type = ');
262 fnd_dsql.add_bind(l_person_id_type);
263 fnd_dsql.add_text(' AND UPPER(api_person_id) = ');
264 fnd_dsql.add_bind(l_api_person_id);
265 fnd_dsql.add_text(' AND (API_START_DATE <> API_END_DATE OR API_END_DATE IS NULL) ');
266 END IF;
267 -- append the where clause with birth date
268 ELSIF g_matchset_exact(i).data_element = 'BIRTH_DT' THEN
269 IF x_birth_dt IS NULL THEN
270 IF g_partial_if_null = 'Y' THEN
271 l_partial_if_null := TRUE;
272 EXIT;
273 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
274 fnd_dsql.add_text(' AND birth_dt IS NULL');
275 END IF;
276 ELSE
277 /* changes made to include trunc function for comparing birth date, for bug number 2158920 */
278 fnd_dsql.add_text(' AND TRUNC(birth_dt) = to_date( to_char(');
279 fnd_dsql.add_bind(x_birth_dt);
280 fnd_dsql.add_text(',''DD-MON-RRRR'') ,''DD-MON-RRRR'')');
281 END IF;
282 -- append the where clause with sex
283 ELSIF g_matchset_exact(i).data_element = 'SEX' THEN
284 IF x_sex IS NULL THEN
285 IF NVL(g_partial_if_null, 'N') = 'Y' THEN
286 l_partial_if_null := TRUE;
287 EXIT;
288 ELSIF NVL(g_matchset_exact(i).drop_if_null, 'N') = 'N' THEN
289 fnd_dsql.add_text(' AND sex IS NULL');
290 END IF;
291 ELSE
292 fnd_dsql.add_text(' AND UPPER(sex) = ');
293 fnd_dsql.add_bind(l_sex);
294 END IF;
295 -- append the where clause with ethnic origin
296 ELSIF g_matchset_exact(i).data_element = 'ETHNIC_ORIGIN' THEN
297 IF x_ethnic_origin IS NULL THEN
298 IF g_partial_if_null = 'Y' THEN
299 l_partial_if_null := TRUE;
300 EXIT;
301 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
302 fnd_dsql.add_text(' AND ethnic_origin IS NULL');
303 END IF;
304 ELSE
305 fnd_dsql.add_text(' AND UPPER(ethnic_origin) = ');
306 fnd_dsql.add_bind(l_ethnic_origin);
307 END IF;
308 -- append the where clause with address type
309 ELSIF g_matchset_exact(i).data_element = 'ADDR_TYPE' THEN
310 IF x_addr_type IS NULL THEN
311 IF g_partial_if_null = 'Y' THEN
312 l_partial_if_null := TRUE;
313 EXIT;
314 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN -- basically record will never come here, it will get a E001
315 fnd_dsql.add_text(' AND addr_type IS NULL');
316 END IF;
317 ELSE
318 fnd_dsql.add_text(' AND UPPER(addr_type) = ');
319 fnd_dsql.add_bind(l_addr_type);
320 END IF;
321 -- append the where clause with contact address lines
322 ELSIF g_matchset_exact(i).data_element = 'CONC_ADDR_LINES' THEN
323 IF l_conct_address_lines IS NULL THEN
324 IF g_partial_if_null = 'Y' THEN
325 l_partial_if_null := TRUE;
326 EXIT;
327 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
328 fnd_dsql.add_text(' AND conct_address_lines IS NULL');
329 END IF;
330 ELSE
331 fnd_dsql.add_text(' AND UPPER(conct_address_lines) = ');
332 fnd_dsql.add_bind(l_conct_address_lines);
333 END IF;
334 -- append the where clause with line 1
335 ELSIF g_matchset_exact(i).data_element = 'ADDR_LINE_1' THEN
336 IF x_addr_line_1 IS NULL THEN
337 IF g_partial_if_null = 'Y' THEN
338 l_partial_if_null := TRUE;
339 EXIT;
340 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
341 fnd_dsql.add_text(' AND addr_line_1 IS NULL');
342 END IF;
343 ELSE
344 fnd_dsql.add_text(' AND UPPER(addr_line_1) = ');
345 fnd_dsql.add_bind(l_addr_line_1);
346 END IF;
347 -- append the where clause with line 2
348 ELSIF g_matchset_exact(i).data_element = 'ADDR_LINE_2' THEN
349 IF x_addr_line_2 IS NULL THEN
350 IF g_partial_if_null = 'Y' THEN
351 l_partial_if_null := TRUE;
352 EXIT;
353 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
354 fnd_dsql.add_text(' AND addr_line_2 IS NULL');
355 END IF;
356 ELSE
357 fnd_dsql.add_text(' AND UPPER(addr_line_2) = ');
358 fnd_dsql.add_bind(l_addr_line_2);
359 END IF;
360 -- append the where clause with line 3
361 ELSIF g_matchset_exact(i).data_element = 'ADDR_LINE_3' THEN
362 IF x_addr_line_3 IS NULL THEN
363 IF g_partial_if_null = 'Y' THEN
364 l_partial_if_null := TRUE;
365 EXIT;
366 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
367 fnd_dsql.add_text(' AND addr_line_3 IS NULL');
368 END IF;
369 ELSE
370 fnd_dsql.add_text(' AND UPPER(addr_line_3) = ');
371 fnd_dsql.add_bind(l_addr_line_3);
372 END IF;
373 -- append the where clause with line 4
374 ELSIF g_matchset_exact(i).data_element = 'ADDR_LINE_4' THEN
375 IF x_addr_line_4 IS NULL THEN
376 IF g_partial_if_null = 'Y' THEN
377 l_partial_if_null := TRUE;
378 EXIT;
379 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
380 fnd_dsql.add_text(' AND addr_line_4 IS NULL');
381 END IF;
382 ELSE
383 fnd_dsql.add_text(' AND UPPER(addr_line_4) = ');
384 fnd_dsql.add_bind(l_addr_line_4);
385 END IF;
386 -- append the where clause with city
387 ELSIF g_matchset_exact(i).data_element = 'CITY' THEN
388 IF x_city IS NULL THEN
389 IF g_partial_if_null = 'Y' THEN
390 l_partial_if_null := TRUE;
391 EXIT;
392 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
393 fnd_dsql.add_text(' AND city IS NULL');
394 END IF;
395 ELSE
396 fnd_dsql.add_text(' AND UPPER(city) = ');
397 fnd_dsql.add_bind(l_city);
398 END IF;
399 -- append the where clause with state
400 ELSIF g_matchset_exact(i).data_element = 'STATE' THEN
401 IF x_state IS NULL THEN
402 IF g_partial_if_null = 'Y' THEN
403 l_partial_if_null := TRUE;
404 EXIT;
405 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
406 fnd_dsql.add_text(' AND state IS NULL');
407 END IF;
408 ELSE
409 fnd_dsql.add_text(' AND UPPER(state) = ');
410 fnd_dsql.add_bind(l_state);
411 END IF;
412 -- append the where clause with province
413 ELSIF g_matchset_exact(i).data_element = 'PROVINCE' THEN
414 IF x_province IS NULL THEN
415 IF g_partial_if_null = 'Y' THEN
416 l_partial_if_null := TRUE;
417 EXIT;
418 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
419 fnd_dsql.add_text(' AND province IS NULL');
420 END IF;
421 ELSE
422 fnd_dsql.add_text(' AND UPPER(province) = ');
423 fnd_dsql.add_bind(l_province);
424 END IF;
425 -- append the where clause with county
426 ELSIF g_matchset_exact(i).data_element = 'COUNTY' THEN
427 IF x_county IS NULL THEN
428 IF g_partial_if_null = 'Y' THEN
429 l_partial_if_null := TRUE;
430 EXIT;
431 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
432 fnd_dsql.add_text(' AND county IS NULL');
433 END IF;
434 ELSE
435 fnd_dsql.add_text(' AND UPPER(county) = ');
436 fnd_dsql.add_bind(l_county);
437 END IF;
438 -- append the where clause with country
439 ELSIF g_matchset_exact(i).data_element = 'COUNTRY' THEN
440 IF x_country IS NULL THEN
441 IF g_partial_if_null = 'Y' THEN
442 l_partial_if_null := TRUE;
443 EXIT;
444 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
445 fnd_dsql.add_text(' AND country IS NULL');
446 END IF;
447 ELSE
448 fnd_dsql.add_text(' AND UPPER(country) = ');
449 fnd_dsql.add_bind(l_country);
450 END IF;
451 -- append the where clause with postcode
452 ELSIF g_matchset_exact(i).data_element = 'POSTCODE' THEN
453 IF x_postcode IS NULL THEN
454 IF g_partial_if_null = 'Y' THEN
455 l_partial_if_null := TRUE;
456 EXIT;
457 ELSIF g_matchset_exact(i).drop_if_null = 'N' THEN
458 fnd_dsql.add_text(' AND postcode IS NULL');
459 END IF;
460 ELSE
461 fnd_dsql.add_text(' AND UPPER(postcode) = ');
462 fnd_dsql.add_bind(l_postcode);
463 END IF;
464 END IF;
465 END LOOP;
466 -- ssawhney : changes in the logic for Devry MCS issue bug 3005910
467
468 -- Form the select statement with the where clause formed.
469 IF l_partial_if_null THEN
470 l_SelectClause := 'PARTIAL_MATCH';
471 ELSE
472 l_SelectClause := fnd_dsql.get_text(FALSE);
473 END IF;
474
475 END IF;
476
477
478 -- If the match category is partial then form the where clause by appending the data elements with 'OR'
479 IF x_match_category = 'P' THEN
480
481 -- this logic was added, to diferentiate parital and exact if ONLY one element is select in the MCS for partial
482 fnd_dsql.add_text(' AND ( 1=2 ');
483
484 FOR i IN 1..g_matchset_partial.COUNT LOOP
485 l_partial_clause := TRUE;
486
487 -- append the where clause with given name
488 IF g_matchset_partial(i).data_element = 'GIVEN_NAME' THEN
489 IF X_GIVEN_NAMES IS NULL THEN
490 IF g_matchset_partial(i).drop_if_null = 'N' THEN
491 fnd_dsql.add_text(' OR given_names IS NULL');
492 END IF;
493 ELSE
494 fnd_dsql.add_text(' OR UPPER(given_names) = ');
495 fnd_dsql.add_bind(l_given_names);
496 END IF;
497 --Bug 3146324 obsoleted alternate Id
498 -- append the where clause with preferred alternate id
499 ELSIF g_matchset_partial(i).data_element = 'PREF_ALTERNATE_ID' THEN
500 IF x_pref_alternate_id IS NULL THEN
501 IF g_matchset_partial(i).drop_if_null = 'N' THEN
502 fnd_dsql.add_text(' OR pref_alternate_id IS NULL');
503 END IF;
504 ELSE
505 fnd_dsql.add_text(' OR UPPER(pref_alternate_id) = ');
506 fnd_dsql.add_bind(l_pref_alternate_id);
507 END IF;
508 -- append the where clause with person id type
509 -- devry matchset issue. person id type should not be treated in standalone mode.
510 -- code is uncommented as alternate id is made as obsolete
511 ELSIF g_matchset_partial(i).data_element = 'PERSON_ID_TYPE' THEN
512 IF x_api_person_id IS NOT NULL THEN
513 fnd_dsql.add_text(' OR ( UPPER(api_person_id) = ');
514 fnd_dsql.add_bind(l_api_person_id);
515 fnd_dsql.add_text(' AND person_id_type=');
516 fnd_dsql.add_bind(l_person_id_type);
517 fnd_dsql.add_text(')');
518 END IF;
519 -- append the where clause with birth date
520 ELSIF g_matchset_partial(i).data_element = 'BIRTH_DT' THEN
521 IF x_birth_dt IS NULL THEN
522 IF g_matchset_partial(i).drop_if_null = 'N' THEN
523 fnd_dsql.add_text(' OR birth_dt IS NULL');
524 END IF;
525 ELSE
526 /* changes made to include trunc function for comparing birth date, for bug number 2158920 */
527 fnd_dsql.add_text(' OR TRUNC(birth_dt) = to_date( to_char(');
528 fnd_dsql.add_bind(x_birth_dt);
529 fnd_dsql.add_text(',''DD-MON-RRRR'') ,''DD-MON-RRRR'')');
530 END IF;
531 -- append the where clause with sex
532 ELSIF g_matchset_partial(i).data_element = 'SEX' THEN
533 IF x_sex IS NULL THEN
534 IF g_matchset_partial(i).drop_if_null = 'N' THEN
535 fnd_dsql.add_text(' OR sex IS NULL');
536 END IF;
537 ELSE
538 fnd_dsql.add_text(' OR UPPER(sex) = ');
539 fnd_dsql.add_bind(l_sex);
540 END IF;
541 -- append the where clause with ethnic origin
542 ELSIF g_matchset_partial(i).data_element = 'ETHNIC_ORIGIN' THEN
543 IF x_ethnic_origin IS NULL THEN
544 IF g_matchset_partial(i).drop_if_null = 'N' THEN
545 fnd_dsql.add_text(' OR ethnic_origin IS NULL');
546 END IF;
547 ELSE
548 fnd_dsql.add_text(' OR UPPER(ethnic_origin) = ');
549 fnd_dsql.add_bind(l_ethnic_origin);
550 END IF;
551 -- append the where clause with address type
552 -- Devry MCS 3005910 address type not to be treated separately.
553 -- append the where clause with contact address lines
554 ELSIF g_matchset_partial(i).data_element = 'CONC_ADDR_LINES' THEN
555 IF l_conct_address_lines IS NULL THEN
556 IF g_matchset_partial(i).drop_if_null = 'N' THEN
557 fnd_dsql.add_text(' OR (conct_address_lines IS NULL');
558 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
559 fnd_dsql.add_text(' AND addr_type = ');
560 fnd_dsql.add_bind(l_addr_type);
561 END IF;
562 fnd_dsql.add_text(')');
563 END IF;
564 ELSE
565 fnd_dsql.add_text(' OR ( UPPER(conct_address_lines) = ');
566 fnd_dsql.add_bind(l_conct_address_lines);
567 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
568 fnd_dsql.add_text(' AND addr_type = ');
569 fnd_dsql.add_bind(l_addr_type);
570 END IF;
571 fnd_dsql.add_text(')');
572 END IF;
573 -- append the where clause with line 1
574 ELSIF g_matchset_partial(i).data_element = 'ADDR_LINE_1' THEN
575 IF x_addr_line_1 IS NULL THEN
576 IF g_matchset_partial(i).drop_if_null = 'N' THEN
577 fnd_dsql.add_text(' OR ( addr_line_1 IS NULL ');
578 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
579 fnd_dsql.add_text(' AND addr_type = ');
580 fnd_dsql.add_bind(l_addr_type);
581 END IF;
582 fnd_dsql.add_text(')');
583 END IF;
584 ELSE
585 fnd_dsql.add_text(' OR ( UPPER(addr_line_1) = ');
586 fnd_dsql.add_bind(l_addr_line_1);
587 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
588 fnd_dsql.add_text(' AND addr_type = ');
589 fnd_dsql.add_bind(l_addr_type);
590 END IF;
591 fnd_dsql.add_text(')');
592 END IF;
593 -- append the where clause with line 2
594 ELSIF g_matchset_partial(i).data_element = 'ADDR_LINE_2' THEN
595 IF x_addr_line_2 IS NULL THEN
596 IF g_matchset_partial(i).drop_if_null = 'N' THEN
597 fnd_dsql.add_text(' OR ( addr_line_2 IS NULL');
598 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
599 fnd_dsql.add_text (' AND addr_type = ');
600 fnd_dsql.add_bind(l_addr_type);
601 END IF;
602 fnd_dsql.add_text (' ) ');
603 END IF;
604 ELSE
605 fnd_dsql.add_text(' OR ( UPPER(addr_line_2) = ');
606 fnd_dsql.add_bind(l_addr_line_2);
607 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
608 fnd_dsql.add_text(' AND addr_type = ');
609 fnd_dsql.add_bind(l_addr_type);
610 END IF;
611 fnd_dsql.add_text(')');
612 END IF;
613 -- append the where clause with line 3
614 ELSIF g_matchset_partial(i).data_element = 'ADDR_LINE_3' THEN
615 IF x_addr_line_3 IS NULL THEN
616 IF g_matchset_partial(i).drop_if_null = 'N' THEN
617 fnd_dsql.add_text(' OR ( addr_line_3 IS NULL');
618 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
619 fnd_dsql.add_text (' AND addr_type = ');
620 fnd_dsql.add_bind(l_addr_type);
621 END IF;
622 fnd_dsql.add_text (' ) ');
623 END IF;
624 ELSE
625 fnd_dsql.add_text(' OR ( UPPER(addr_line_3) = ');
626 fnd_dsql.add_bind(l_addr_line_3);
627 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
628 fnd_dsql.add_text(' AND addr_type = ');
629 fnd_dsql.add_bind(l_addr_type);
630 END IF;
631 fnd_dsql.add_text(')');
632 END IF;
633 -- append the where clause with line 4
634 ELSIF g_matchset_partial(i).data_element = 'ADDR_LINE_4' THEN
635 IF x_addr_line_4 IS NULL THEN
636 IF g_matchset_partial(i).drop_if_null = 'N' THEN
637 fnd_dsql.add_text(' OR ( addr_line_4 IS NULL');
638 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
639 fnd_dsql.add_text(' AND addr_type = ');
640 fnd_dsql.add_bind(l_addr_type);
641 END IF;
642 fnd_dsql.add_text(')');
643 END IF;
644 ELSE
645 fnd_dsql.add_text(' OR ( UPPER(addr_line_4) = ');
646 fnd_dsql.add_bind(l_addr_line_4);
647 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
648 fnd_dsql.add_text(' AND addr_type = ');
649 fnd_dsql.add_bind(l_addr_type);
650 END IF;
651 fnd_dsql.add_text(')');
652 END IF;
653 -- append the where clause with city
654 ELSIF g_matchset_partial(i).data_element = 'CITY' THEN
655 IF x_city IS NULL THEN
656 IF g_matchset_partial(i).drop_if_null = 'N' THEN
657 fnd_dsql.add_text(' OR ( city IS NULL');
658 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
659 fnd_dsql.add_text(' AND addr_type = ');
660 fnd_dsql.add_bind(l_addr_type);
661 END IF;
662 fnd_dsql.add_text(')');
663 END IF;
664 ELSE
665 fnd_dsql.add_text(' OR ( UPPER(city) = ');
666 fnd_dsql.add_bind(l_city);
667 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
668 fnd_dsql.add_text(' AND addr_type = ');
669 fnd_dsql.add_bind(l_addr_type);
670 END IF;
671 fnd_dsql.add_text(')');
672 END IF;
673 -- append the where clause with state
674 ELSIF g_matchset_partial(i).data_element = 'STATE' THEN
675 IF x_state IS NULL THEN
676 IF g_matchset_partial(i).drop_if_null = 'N' THEN
677 fnd_dsql.add_text(' OR ( state IS NULL');
678 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
679 fnd_dsql.add_text(' AND addr_type = ');
680 fnd_dsql.add_bind(l_addr_type);
681 END IF;
682 fnd_dsql.add_text(')');
683 END IF;
684 ELSE
685 fnd_dsql.add_text(' OR ( UPPER(state) = ');
686 fnd_dsql.add_bind(l_state);
687 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
688 fnd_dsql.add_text(' AND addr_type = ');
689 fnd_dsql.add_bind(l_addr_type);
690 END IF;
691 fnd_dsql.add_text(')');
692 END IF;
693 -- append the where clause with province
694 ELSIF g_matchset_partial(i).data_element = 'PROVINCE' THEN
695 IF x_province IS NULL THEN
696 IF g_matchset_partial(i).drop_if_null = 'N' THEN
697 fnd_dsql.add_text(' OR ( province IS NULL');
698 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
699 fnd_dsql.add_text(' AND addr_type = ');
700 fnd_dsql.add_bind(l_addr_type);
701 END IF;
702 fnd_dsql.add_text(')');
703 END IF;
704 ELSE
705 fnd_dsql.add_text(' OR ( UPPER(province) = ');
706 fnd_dsql.add_bind(l_province);
707 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
708 fnd_dsql.add_text(' AND addr_type = ');
709 fnd_dsql.add_bind(l_addr_type);
710 END IF;
711 fnd_dsql.add_text(')');
712 END IF;
713 -- append the where clause with county
714 ELSIF g_matchset_partial(i).data_element = 'COUNTY' THEN
715 IF x_county IS NULL THEN
716 IF g_matchset_partial(i).drop_if_null = 'N' THEN
717 fnd_dsql.add_text(' OR ( county IS NULL');
718 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
719 fnd_dsql.add_text(' AND addr_type = ');
720 fnd_dsql.add_bind(l_addr_type);
721 END IF;
722 fnd_dsql.add_text(')');
723 END IF;
724 ELSE
725 fnd_dsql.add_text(' OR ( UPPER(county) = ');
726 fnd_dsql.add_bind(l_county);
727 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
728 fnd_dsql.add_text(' AND addr_type = ');
729 fnd_dsql.add_bind(l_addr_type);
730 END IF;
731 fnd_dsql.add_text(')');
732 END IF;
733 -- append the where clause with country
734 ELSIF g_matchset_partial(i).data_element = 'COUNTRY' THEN
735 IF x_country IS NULL THEN
736 IF g_matchset_partial(i).drop_if_null = 'N' THEN
737 fnd_dsql.add_text(' OR (country IS NULL');
738 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
739 fnd_dsql.add_text(' AND addr_type = ');
740 fnd_dsql.add_bind(l_addr_type);
741 END IF;
742 fnd_dsql.add_text(')');
743 END IF;
744 ELSE
745 fnd_dsql.add_text(' OR ( UPPER(country) = ');
746 fnd_dsql.add_bind(l_country);
747 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
748 fnd_dsql.add_text(' AND addr_type = ');
749 fnd_dsql.add_bind(l_addr_type);
750 END IF;
751 fnd_dsql.add_text(')');
752 END IF;
753 -- append the where clause with postcode
754 ELSIF g_matchset_partial(i).data_element = 'POSTCODE' THEN
755 IF x_postcode IS NULL THEN
756 IF g_matchset_partial(i).drop_if_null = 'N' THEN
757 fnd_dsql.add_text(' OR ( postcode IS NULL');
758 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
759 fnd_dsql.add_text(' AND addr_type = ');
760 fnd_dsql.add_bind(l_addr_type);
761 END IF;
762 fnd_dsql.add_text(')');
763 END IF;
764 ELSE
765 fnd_dsql.add_text(' OR ( UPPER(postcode) = ');
766 fnd_dsql.add_bind(l_postcode);
767 IF Igs_Pe_Identify_Dups.g_primary_addr_flag = 'N' THEN
768 fnd_dsql.add_text(' AND addr_type = ');
769 fnd_dsql.add_bind(l_addr_type);
770 END IF;
771 fnd_dsql.add_text(')');
772 END IF;
773 END IF;
774 END LOOP;
775
776 -- Form the where clause by appending the select clause with where clause
777 IF l_partial_clause THEN
778 fnd_dsql.add_text(')');
779 l_SelectClause := fnd_dsql.get_text(FALSE);
780 ELSE
781 l_SelectClause := l_default_sql_text;
782 END IF;
783 END IF;
784 -- Return the select statement to the called procedure.
785 -- ssawhney display output.
786 -- Fnd_File.PUT_LINE(Fnd_File.log, l_selectclause);
787 x_select_clause := l_SelectClause;
788 EXCEPTION
789 WHEN OTHERS THEN
790 x_retcode := '2';
791 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
792 FND_MESSAGE.SET_TOKEN('NAME','igs_pe_identify_dups.form_dup_whereclause'||'-'||SQLERRM);
793 APP_EXCEPTION.RAISE_EXCEPTION;
794 END;
795
796 PROCEDURE Find_dup_rel_per(
797 P_REL_DUP_REC IN r_record_dup_rel,
798 P_MATCH_FOUND OUT NOCOPY VARCHAR2
799 ) IS
800
801 /*
802 Created By: Npalanis , 28-MAY-2003
803 Purpose : Import Process Relationship duplicate match
804 Change History
805 Who when What
806 */
807
808 l_rowid VARCHAR2(30);
809 l_near_mtch_ind igs_ad_imp_near_mtch_all.near_mtch_id%TYPE;
810 l_count NUMBER(7) := 0;
811 l_stmt VARCHAR2(2000);
812 l_person_id igs_pe_person_base_v.person_id%TYPE;
813
814 TYPE dup_check_cur IS REF CURSOR;
815 dup_check_rec dup_check_cur;
816 l_cursor_id NUMBER(15);
817 l_num_of_rows NUMBER(10);
818
819 BEGIN
820
821 /* Delete from igs_ad_near_match_int all old occurances */
822 DECLARE
823 CURSOR tbh_cur IS
824 SELECT ROWID
825 FROM igs_ad_imp_near_mtch_all
826 WHERE interface_relations_id = P_REL_DUP_REC.INTERFACE_RELATIONS_ID;
827 tbh_rec tbh_cur%ROWTYPE;
828 BEGIN
829 OPEN tbh_cur;
830 LOOP
831 FETCH tbh_cur INTO tbh_rec;
832 EXIT WHEN tbh_cur%NOTFOUND;
833 Igs_Ad_Imp_Near_Mtch_Pkg.delete_row(tbh_rec.ROWID);
834 END LOOP;
835 CLOSE tbh_cur;
836 END;
837
838 fnd_dsql.init;
839
840 fnd_dsql.add_text('SELECT person_id FROM igs_pe_person_base_v WHERE UPPER(first_name) =UPPER(');
841 fnd_dsql.add_bind(P_REL_DUP_REC.FIRST_NAME);
842 fnd_dsql.add_text(') AND UPPER (last_name) = UPPER (');
843 fnd_dsql.add_bind(P_REL_DUP_REC.SURNAME);
844 fnd_dsql.add_text(') AND ( 1 = 2 OR ');
845
846 IF P_REL_DUP_REC.GENDER IS NULL AND P_REL_DUP_REC.BIRTH_DATE IS NULL THEN
847 fnd_dsql.add_text(' gender IS NULL OR birth_date IS NULL)');
848
849 ELSIF P_REL_DUP_REC.GENDER IS NOT NULL AND P_REL_DUP_REC.BIRTH_DATE IS NULL THEN
850 fnd_dsql.add_text(' gender = ');
851 fnd_dsql.add_bind(P_REL_DUP_REC.gender);
852 fnd_dsql.add_text(' OR birth_date IS NULL)');
853
854 ELSIF P_REL_DUP_REC.GENDER IS NULL AND P_REL_DUP_REC.BIRTH_DATE IS NOT NULL THEN
855 fnd_dsql.add_text(' gender IS NULL OR birth_date = ');
856 fnd_dsql.add_bind(TO_DATE(TO_CHAR(P_REL_DUP_REC.birth_date,'DD-MON-RRRR'),'DD/MM/RRRR'));
857 fnd_dsql.add_text(')');
858
859 ELSE
860 fnd_dsql.add_text(' gender = ');
861 fnd_dsql.add_bind(P_REL_DUP_REC.gender);
862 fnd_dsql.add_text(' OR birth_date = ');
863 fnd_dsql.add_bind(TO_DATE(TO_CHAR(P_REL_DUP_REC.birth_date,'DD-MON-RRRR'),'DD/MM/RRRR'));
864 fnd_dsql.add_text(')');
865
866 END IF;
867
868 l_stmt := fnd_dsql.get_text(FALSE);
869 l_cursor_id := dbms_sql.open_cursor;
870 fnd_dsql.set_cursor(l_cursor_id);
871 dbms_sql.parse(l_cursor_id, l_stmt, dbms_sql.native);
872 fnd_dsql.do_binds;
873
874 dbms_sql.define_column(l_cursor_id, 1, l_person_id);
875
876 l_num_of_rows := dbms_sql.EXECUTE(l_cursor_id);
877
878 LOOP
879
880 IF dbms_sql.fetch_rows(l_cursor_id) > 0 THEN
881 dbms_sql.column_value(l_cursor_id, 1, l_person_id);
882 ELSE
883 EXIT;
884 END IF;
885 l_count := l_count + 1;
886 IGS_AD_IMP_NEAR_MTCH_PKG.INSERT_ROW(
887 X_ROWID => l_rowid ,
888 X_ORG_ID => NULL,
889 X_NEAR_MTCH_ID => l_near_mtch_ind ,
890 X_INTERFACE_ID => P_REL_DUP_REC.INTERFACE_ID,
891 X_PERSON_ID => l_person_id,
892 X_MATCH_IND => 'P',
893 X_ACTION => null,
894 X_ADDR_TYPE => null,
895 X_PERSON_ID_TYPE => Null,
896 X_MATCH_SET_ID => P_REL_DUP_REC.match_set_id,
897 X_MODE => 'I',
898 X_PARTY_SITE_ID => null,
899 X_INTERFACE_RELATIONS_ID =>P_REL_DUP_REC.INTERFACE_RELATIONS_ID);
900
901 END LOOP;
902
903 IF l_count = 0 THEN
904 p_match_found := 'N';
905 ELSIF l_count > 0 THEN
906 p_match_found := 'Y';
907 END IF;
908
909
910 END Find_dup_rel_per;
911
912 END Igs_Pe_Identify_Dups;
[Home] [Help]