[Home] [Help]
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);
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:
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
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 = ');
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');
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:
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
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
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);
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
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);
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
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);
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
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
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) ');
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;
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
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
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(');
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
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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
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);
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;
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
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:
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);
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
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
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);
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.
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
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(')');
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;
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
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
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
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(');
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
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
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
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);
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
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
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);
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.
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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
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
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;
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;
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
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);
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);
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;
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;
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;
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:
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;
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;
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 (');
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 ');
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:
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
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)');
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:
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);
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
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 = ');
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'));
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
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 = ');
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);
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(')');
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:
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;
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:
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);
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;
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);
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);