1 PACKAGE BODY HZ_FUZZY_PUB as
2 /*$Header: ARHFUZYB.pls 120.9 2005/08/29 13:11:22 rchanamo ship $ */
3
4 -- Following two strings would be used in a translate function
5 -- to replace punctuation characters etc. The letter 'z' is added
6 -- here because the replace string cannot be an empty string for
7 -- translate function.
8 g_original_text VARCHAR2(50) := 'z!"#$%&''()*+,-./:;<=>?@[\]^_`{|}~';
9 g_replace_text VARCHAR2(50) := 'z';
10
11 -- Bug 3252909.
12 -- These variables will be used to store the word_list_id corresponding to
13 -- word_list_name = 'ORGANIZATION_NAME_DICTIONARY','PERSON_NAME_DICTIONARY'
14 -- and 'ADDRESS_DICTIONARY' respectively.`
15 g_org_word_list_id NUMBER(15) := NULL;
16 g_per_word_list_id NUMBER(15) := NULL;
17 g_add_word_list_id NUMBER(15) := NULL;
18
19
20
21
22 TYPE original_key IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
23 TYPE replacement_key IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
24
25 g_original_key original_key;
26 g_replacement_key replacement_key;
27 g_special_enabled VARCHAR2(1);
28
29 /************************* Private Routines *******************************/
30
31 /*===========================================================================+
32 | FUNCTION |
33 | cleanse |
34 | |
35 | DESCRIPTION |
36 | Function takes a string as input. It replaces double |
37 | consonants by single consonant and removes vowels that |
38 | appear inside a word. |
39 | |
40 | SCOPE - Private |
41 | |
42 | ARGUMENTS : IN : str |
43 | OUT: |
44 | IN/ OUT: |
45 | |
46 | RETURNS : varchar2 |
47 | |
48 | NOTES : Function is called solely from Replace_Word function |
49 | |
50 | MODIFICATION HISTORY |
51 | Indrajit Sen 10-OCT-01 Created |
55 FUNCTION cleanse
52 | |
53 +===========================================================================*/
54
56 (str IN varchar2
57 )
58 RETURN varchar2
59 IS
60 str2 varchar2(400) := str;
61 str3 varchar2(400);
62
63 BEGIN
64
65 -- if the input is null, return null as the processing has no
66 -- impact on that
67 if str is null then
68 return str;
69 end if;
70
71 -- Step 1. Replace any two or more consecutive same
72 -- letters by single letter
73
74 -- get the input string in a temporary string
75 str3 := str2;
76
77 -- loop from first letter to last but one letter
78 for i in 1..lengthb(str2)-1
79 loop
80 -- if two consecutive letters match, then replace two such letter
81 -- by one letter from the temporary string
82 if substrb(str2,i,1) = substrb(str2,i+1,1) then
83 str3 := replace(str3, substrb(str2,i,1)||substrb(str2,i+1,1), substrb(str2,i,1));
84 end if;
85 end loop;
86
87 str2 := str3;
88
89 -- Step 2. Replace Vowels only that occur inside
90 -- First we should build a temporary string
91 -- which would be the original string str2
92 -- stripped off the first letter
93 str3 := substrb(str2, 2);
94
95 -- Now call replace to remove all occurrences
96 -- of each vowel
97 str3 := replace(str3, 'A', '');
98 str3 := replace(str3, 'E', '');
99 str3 := replace(str3, 'I', '');
100 str3 := replace(str3, 'O', '');
101 str3 := replace(str3, 'U', '');
102
103 -- Now we have to build str2 back with
104 -- first letter of str2 and appending str3
105 str2 := substrb(str2, 1, 1)||str3;
106
107 -- return str2 which is the final clean word
108 return rtrim(str2);
109
110 END cleanse;
111
112
113 /*===========================================================================+
114 | FUNCTION |
115 | Replace_Word |
116 | |
117 | DESCRIPTION |
118 | Function takes a word (or string of words as input) and converts the |
119 | word into part of the TCA key needed for Fuzzy find. |
120 | The returned string is used in creating the TCA Key. |
121 | |
122 | SCOPE - Private |
123 | |
124 | ARGUMENTS : IN: |
125 | p_word |
126 | p_replacement_type |
127 | OUT: |
128 | IN/ OUT: |
129 | |
130 | RETURNS : l_key |
131 | |
132 | NOTES : Function is called solely from Generate_Key function |
133 | |
134 | MODIFICATION HISTORY |
135 | Indrajit Sen 10-OCT-01 Created |
136 | P.Suresh 29-MAY-02 Bug No : 2326168. Modified the Replace_Word |
137 | function to use word_list_id instead of type.|
138 | Rajib R Borah 21-JAN-04 Bug No : 3252909. Used global variables to |
139 | get the word_list_id instead of opening the |
140 | cursor c_wl_id inside the loop every time. |
141 | S.V.Sojwnaya 09-JUL-04 Bug No : 3686983. Modified the cursor c_key |
142 | to use word_list_id and word_list_name |
143 | instead of Type. |
144 | Ramesh Ch 03-JAN-05 Bug No:4098780.Modified C_Word_Rep and c_key |
145 | cursors not to pick the conditional |
146 | word replacements. |
147 | V.Ravichandran 08-AUG-05 Bug No:4361061.Modified the procedure replace|
148 | word to handle encrypted words composed of |
149 | multi-byte characters correctly. |
150 | |
151 | Ramesh Ch. 26-AUG-2005 Modified Replace_Word to pick |
152 | the word replacement pairs |
153 | based on the staged context. |
154 | (DQM Stabilization Project) |
155 +===========================================================================*/
156
157
158 FUNCTION Replace_Word(p_word VARCHAR2,
159 p_replacement_type VARCHAR2)
160 RETURN VARCHAR2
161 IS
162 l_source_text VARCHAR2(2000);
163 l_text_length NUMBER;
164 l_current_word VARCHAR2(2000);
165 l_key VARCHAR2(2000);
166 l_old_word VARCHAR2(2000);
167 l_count NUMBER := 0;
168 l_wl_id NUMBER;
169 l_replacement_type VARCHAR2(255);
170 l_pos number;
171 l_exit_flag VARCHAR2(1) := 'N';
172
173
174 CURSOR c_wl_id (c_word_list_name VARCHAR2) IS
175 SELECT word_list_id FROM HZ_WORD_LISTS
176 WHERE word_list_name = c_word_list_name;
177
178 CURSOR C_Word_Rep (x_current_word VARCHAR2,
179 cp_wl_id NUMBER) IS
180 SELECT upper(replacement_word)
181 FROM hz_word_replacements
182 WHERE upper(original_word) = x_current_word
183 AND word_list_id = cp_wl_id
184 AND ((HZ_TRANS_PKG.staging_context = 'Y' AND DELETE_FLAG = 'N')
185 OR (nvl(HZ_TRANS_PKG.staging_context,'N') = 'N' AND STAGED_FLAG = 'Y')
186 )
187 AND condition_id IS NULL; --Bug No:4098780
188
189 CURSOR c_key IS
190 SELECT HWR.ORIGINAL_WORD,
191 HWR.REPLACEMENT_WORD
192 FROM HZ_WORD_LISTS HWL, HZ_WORD_REPLACEMENTS HWR
193 WHERE HWL.WORD_LIST_NAME = 'KEY MODIFIERS' AND HWR.WORD_LIST_ID = HWL.WORD_LIST_ID
194 AND ((HZ_TRANS_PKG.staging_context = 'Y' AND HWR.DELETE_FLAG = 'N')
195 OR (nvl(HZ_TRANS_PKG.staging_context,'N') = 'N' AND HWR.STAGED_FLAG = 'Y')
196 )
197 AND HWR.CONDITION_ID IS NULL; --Bug No:4098780
198
199
200 BEGIN
201
202 IF p_replacement_type = 'ORGANIZATION' THEN
203 l_replacement_type := 'ORGANIZATION_NAME_DICTIONARY';
204 ELSIF p_replacement_type = 'PERSON' THEN
205 l_replacement_type := 'PERSON_NAME_DICTIONARY';
206 ELSIF p_replacement_type = 'ADDRESS' THEN
207 l_replacement_type := 'ADDRESS_DICTIONARY';
208 END IF;
209
210 -- Bug 3252909
211 IF g_org_word_list_id is null
212 then
213 open c_wl_id('ORGANIZATION_NAME_DICTIONARY');
214 fetch c_wl_id into g_org_word_list_id;
215 close c_wl_id;
216 end if;
217
218 IF g_per_word_list_id is null
219 then
220 open c_wl_id('PERSON_NAME_DICTIONARY');
221 fetch c_wl_id into g_per_word_list_id;
222 close c_wl_id;
223 end if;
224
225 IF g_add_word_list_id is null
226 then
227 open c_wl_id('ADDRESS_DICTIONARY');
228 fetch c_wl_id into g_add_word_list_id;
229 close c_wl_id;
230 end if;
231
232 -- Steps mentioned here are in the context of complete fuzzy key
233 -- generation process. (Step 1 is in Generate_Key)
234
235 -- Step 2.
236 -- We need to remove 'S so that WILLIAM'S becomes WILLIAM and
237 -- it can become BILL if there is a replacement rule from
238 -- original word WILLIAM to replacement word BILL
239 l_source_text := replace(p_word, '''S ', ' ');
240
241 -- Step 3.
242 -- We need to remove any punctuation characters etc.
243 -- For example, this will make 134/3, 134-3 etc mapped to 1343 in key for address.
244 l_source_text := ltrim(translate(l_source_text, g_original_text, g_replace_text));
245
246 -- Step 3.5.
247 -- This step is for removal of special characters.
248 -- The special characters will only be replaced if user has
249 -- has set up Key Modifiers rules.
250 -- This will replace any number of characters to any number of characters mapping for
251 -- many european language. See bug 1868161 for detail.
252
253 IF g_special_enabled IS NULL THEN
254 OPEN c_key;
255 FETCH c_key INTO g_original_key(l_count), g_replacement_key(l_count);
256 IF c_key%NOTFOUND THEN
257 g_special_enabled := 'N';
258 ELSE
259 g_special_enabled := 'Y';
260 END IF;
261
262 WHILE c_key%FOUND LOOP
263 l_count := l_count + 1;
264 FETCH c_key INTO g_original_key(l_count), g_replacement_key(l_count);
265 END LOOP;
266 CLOSE c_key;
267 END IF;
268
269 IF g_special_enabled = 'Y' THEN
270 FOR i IN g_original_key.FIRST..g_original_key.LAST LOOP
271 l_source_text := REPLACE(l_source_text, g_original_key(i), g_replacement_key(i));
272 END LOOP;
273 END IF;
274
275 -- Step 4.
276 -- We need to continue further processing on each word if a group
277 -- of words is the input parameter.
278 -- For example INTERNATIONAL BUSINESS MACHINES should have rules
279 -- applied to each word (INTERNATIONAL, BUSINESS, MACHINES) individually.
280 -- Append a blank space on the end of the text so that the loop can
281 -- always end with the last word.
282 l_source_text := l_source_text || ' ';
283
284 -- Bug 3252909.
285 -- Instead of gettin word_list_id from cursor inside loop( in Step 5),
286 -- read from global variable outside loop.
287
288
289
290 IF l_replacement_type = 'ORGANIZATION_NAME_DICTIONARY'
291 THEN
292 l_wl_id := g_org_word_list_id;
293 ELSIF l_replacement_type = 'PERSON_NAME_DICTIONARY'
294 THEN
295 l_wl_id := g_per_word_list_id;
296 ELSIF l_replacement_type = 'ADDRESS_DICTIONARY'
297 THEN
298 l_wl_id := g_add_word_list_id;
299 ELSE
300 RETURN p_word;-- RETURN l_source_text seems to be a better option.
301 END IF;
302
303
304
305 LOOP
306 l_text_length := NVL(lengthb(l_source_text),0);
307 l_pos := instrb(l_source_text,' ',1);
308 IF l_exit_flag='Y' or l_text_length=0
309 THEN
310
311 EXIT;
312 END IF;
313 l_current_word:=substrb(l_source_text,1,l_pos-1);
314 l_old_word:=l_current_word;
315
316 -- Fetch the replacement word for the current word.
317 -- If no replacement word is found, then use the original
318 -- word
319 --
320 -- Step 5.
321 -- Search a replacement word for the original word.
322 -- For example WILLIAM will be replaced by BILL if there is such rule.
323 -- If a replacement found, substitute the original word by it
324
325 /*Bug 3252909.Read from global variables instead of using the cursor.
326 | OPEN c_wl_id(l_replacement_type);
327 | FETCH c_wl_id INTO l_wl_id;
328 | IF c_wl_id%NOTFOUND THEN
329 | CLOSE c_wl_id;
330 | RETURN p_word;
331 | END IF;
332 | CLOSE c_wl_id;
333 */
334
335 OPEN C_Word_Rep(l_current_word, l_wl_id);
336 FETCH C_Word_Rep INTO l_current_word;
337 IF (C_Word_Rep%NOTFOUND)
338 THEN
339 l_current_word := l_old_word;
340 END IF;
341 CLOSE C_Word_Rep;
342
343 -- Step 7.
344 -- If profile for cleansing is set, then cleanse the word.
345 -- Cleanse converts double letters to single letter, removes
346 -- vowels inside a word.
347 -- For example : UNIVERSAL - UNVRSL, LITTLE - LTL etc.
348 -- Bug 2059524,added NVL function in the following line.
349 if NVL(fnd_profile.value('HZ_CLEANSE_FUZZY_KEY'),'Y') = 'Y' then
350 l_current_word := cleanse(l_current_word);
351 end if;
352
353 -- Step 8.
354 -- Build the key in a local variable
355 -- This removes the white spaces
356 l_key := l_key || l_current_word;
357 if l_text_length=l_pos
358 then
359 l_exit_flag:='Y';
360 end if;
361
362 l_source_text := substrb(l_source_text,l_pos+1);
363 END LOOP;
364 RETURN l_key;
365 END Replace_Word;
366
367
368 /*===========================================================================+
369 | PROCEDURE |
370 | Find_Duplicate_Party |
371 | |
372 | DESCRIPTION |
373 | Checks to see if there is a duplicate party record in the |
374 | database. If one is found then TRUE is returned along with the |
375 | party_id for the existing party. If a duplicate is not found, |
376 | then FALSE is returned. |
377 | |
378 | SCOPE - Private |
379 | |
380 | ARGUMENTS : IN: |
381 | p_party_name |
382 | p_party_key |
383 | p_key_search_flag |
384 | OUT: |
385 | p_party_id |
386 | p_is_duplicate |
387 | IN/ OUT: |
388 | |
389 | RETURNS : NONE |
390 | |
391 | NOTES : |
392 | A matching party is defined as: |
393 | the p_key_search_flag = 'T' and the party keys match |
394 | the p_key_search_flag = 'F' and the upper party names match |
395 | |
396 | MODIFICATION HISTORY |
397 | Indrajit Sen 22-JUN-00 Created |
398 | |
399 +===========================================================================*/
400
401 PROCEDURE Find_Duplicate_Party (
402 p_party_name IN VARCHAR2,
403 p_party_key IN VARCHAR2,
404 p_key_search_flag IN VARCHAR2,
405 p_party_id OUT NOCOPY NUMBER,
406 p_is_duplicate OUT NOCOPY VARCHAR2)
407 IS
408 CURSOR C_Duplicate_Names (X_party_Name VARCHAR2) IS
409 SELECT party_id
410 FROM hz_parties
411 WHERE upper(party_name) = upper(X_party_Name);
412
413 CURSOR C_Duplicate_Keys (X_party_Key VARCHAR2) IS
414 SELECT party_id
415 FROM hz_parties
416 WHERE customer_key = X_party_Key;
417
418 BEGIN
419 -- if the request is not to use the keys then use match by party name
420 IF upper(p_key_search_flag) = FND_API.G_FALSE
421 THEN
422 OPEN C_Duplicate_Names(p_party_name);
423 FETCH C_Duplicate_Names INTO p_party_id;
424 IF (C_Duplicate_Names%NOTFOUND)
425 THEN
426 p_is_duplicate := FND_API.G_FALSE;
427 ELSE
428 p_is_duplicate := FND_API.G_TRUE;
429 END IF;
430 CLOSE C_Duplicate_Names;
431
432 ELSIF upper(p_key_search_flag) = FND_API.G_TRUE -- if the request is to use the keys then use match by key
433 THEN
434 OPEN C_Duplicate_Keys( p_party_key);
435 FETCH C_Duplicate_Keys INTO p_party_id;
436 IF (C_Duplicate_Keys%NOTFOUND)
437 THEN
438 p_is_duplicate := FND_API.G_FALSE;
439 ELSE
440 p_is_duplicate := FND_API.G_TRUE;
441 END IF;
442 CLOSE C_Duplicate_Keys;
443
444 END IF;
445
449
446 EXCEPTION
447 WHEN OTHERS THEN
448 p_is_duplicate := FND_API.G_FALSE;
450 END Find_Duplicate_Party;
451
452
453 /*===========================================================================+
454 | PROCEDURE |
455 | Find_Duplicate_Address |
456 | |
457 | DESCRIPTION |
458 | Checks to see if there is a duplicate address/location record in the |
459 | database. If one is found then TRUE is returned along with the |
460 | location_id for the existing address. |
461 | If a duplicate is not found, then FALSE is returned. |
462 | |
463 | SCOPE - Private |
464 | |
465 | ARGUMENTS : IN: |
466 | p_address_rec |
467 | p_address_key |
468 | p_key_search_flag |
469 | OUT: |
470 | p_location_id |
471 | p_is_duplicate |
472 | IN/ OUT: |
476 | NOTES : |
473 | |
474 | RETURNS : NONE |
475 | |
477 | A matching address/location is defined as: |
478 | the p_key_search_flag = 'T' and the address keys match |
479 | the p_key_search_flag = 'F' and the concatenated upper string |
480 | of address1, address2, address3, address4, postal_code, state, |
481 | city, and country are equal |
482 | |
483 | MODIFICATION HISTORY |
484 | Indrajit Sen 22-JUN-00 Created |
485 | |
486 +===========================================================================*/
487
488 PROCEDURE Find_Duplicate_Address (
489 p_address_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
490 p_address_key IN VARCHAR2,
491 p_key_search_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE,
492 p_location_id OUT NOCOPY NUMBER,
493 p_is_duplicate OUT NOCOPY VARCHAR2)
494 IS
495 CURSOR C_Duplicate_Keys (X_Address_Key VARCHAR2) IS
496 SELECT location_id
497 FROM hz_locations
498 WHERE address_key = X_Address_Key;
499
500 CURSOR C_Duplicate_Address (
501 X_Address1 VARCHAR2,
502 X_Address2 VARCHAR2,
503 X_Address3 VARCHAR2,
504 X_Address4 VARCHAR2,
505 X_Postal_Code VARCHAR2,
506 X_State VARCHAR2,
507 X_City VARCHAR2,
508 X_Country VARCHAR2) IS
509 SELECT location_id
510 FROM hz_locations
511 WHERE upper(replace(translate(
512 X_Address1 || X_Address2 || X_Address3 || X_Address4 ||
513 X_Postal_Code || X_State || X_City || X_Country,
514 '#-_.,/\', ' ') ,' ' ) ) =
515 upper(replace(translate(
516 ADDRESS1 || ADDRESS2 || ADDRESS3 || ADDRESS4 ||
517 POSTAL_CODE || STATE || CITY || COUNTRY,
518 '#-_.,/\', ' ') ,' ' ) );
519
520 BEGIN
521 -- if the request is not to use the keys then use match by address fields
522 IF upper(p_key_search_flag) = FND_API.G_FALSE
523 THEN
524 OPEN C_Duplicate_Address (rtrim(p_address_rec.Address1,FND_API.G_MISS_CHAR),
525 rtrim(p_address_rec.Address2,FND_API.G_MISS_CHAR),
526 rtrim(p_address_rec.Address3,FND_API.G_MISS_CHAR),
527 rtrim(p_address_rec.Address4,FND_API.G_MISS_CHAR),
528 rtrim(p_address_rec.Postal_Code,FND_API.G_MISS_CHAR),
529 rtrim(p_address_rec.State,FND_API.G_MISS_CHAR),
530 rtrim(p_address_rec.City,FND_API.G_MISS_CHAR),
531 rtrim(p_address_rec.Country,FND_API.G_MISS_CHAR));
532 FETCH C_Duplicate_Address into p_location_id;
533 IF (C_Duplicate_Address%NOTFOUND)
534 THEN
535 p_is_duplicate := FND_API.G_FALSE;
536 ELSE
537 p_is_duplicate := FND_API.G_TRUE;
538 END IF;
539 CLOSE C_Duplicate_Address;
540 ELSIF p_key_search_flag = FND_API.G_TRUE -- if the request is to use the keys then use match by address key
541 THEN
542 OPEN C_Duplicate_Keys (p_address_key);
543 FETCH C_Duplicate_Keys into p_location_id;
544 IF (C_Duplicate_Keys%NOTFOUND)
545 THEN
546 p_is_duplicate := FND_API.G_FALSE;
547 ELSE
548 p_is_duplicate := FND_API.G_TRUE;
549 END IF;
550 CLOSE C_Duplicate_Keys;
551 END IF;
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 p_is_duplicate := FND_API.G_FALSE;
556
557 END Find_Duplicate_Address;
558
559
560 /*===========================================================================+
561 | FUNCTION |
562 | Get_Party_Rec |
563 | |
564 | DESCRIPTION |
565 | The function accepts party_id and returns the party record |
566 | for that particular party_id |
567 | |
568 | SCOPE - Private |
569 | |
570 | ARGUMENTS : IN: |
571 | p_party_id |
572 | OUT: |
573 | IN/ OUT: |
574 | |
575 | RETURNS : l_party_rec |
576 | |
577 | NOTES : This done not return the whole party record structure. |
578 | |
582 +===========================================================================*/
579 | MODIFICATION HISTORY |
580 | Indrajit Sen 22-JUN-00 Created |
581 | |
583
584 FUNCTION Get_Party_Rec (p_party_id NUMBER
585 ) RETURN PARTY_REC_TYPE
586 IS
587 l_party_rec PARTY_REC_TYPE;
588 BEGIN
589 SELECT
590 PARTY_ID,
591 PARTY_NAME,
592 PARTY_TYPE,
593 PERSON_FIRST_NAME,
594 PERSON_LAST_NAME
595 INTO
596 l_party_rec.PARTY_ID,
597 l_party_rec.PARTY_NAME,
598 l_party_rec.PARTY_TYPE,
599 l_party_rec.FIRST_NAME,
600 l_party_rec.LAST_NAME
601 FROM HZ_PARTIES
602 WHERE PARTY_ID = p_party_id;
603
604 RETURN l_party_rec;
605
606 END Get_Party_Rec;
607
608
609 /*===========================================================================+
610 | FUNCTION |
611 | Get_Location_Rec |
612 | |
613 | DESCRIPTION |
614 | The function accepts location_id and returns the location record |
615 | for that particular location_id |
616 | |
617 | SCOPE - Private |
618 | |
619 | ARGUMENTS : IN: |
620 | p_location_id |
621 | OUT: |
622 | IN/ OUT: |
623 | |
624 | RETURNS : l_location_rec |
625 | |
626 | NOTES : This done not return the whole location record structure. |
627 | |
628 | MODIFICATION HISTORY |
629 | Indrajit Sen 22-JUN-00 Created |
630 | |
631 +===========================================================================*/
632
633 FUNCTION Get_Location_Rec (p_location_id NUMBER
634 ) RETURN LOCATION_REC_TYPE
635 IS
636 l_location_rec LOCATION_REC_TYPE;
637 BEGIN
638 SELECT
639 LOCATION_ID,
640 ADDRESS1,
641 ADDRESS2,
642 ADDRESS3,
643 ADDRESS4,
644 POSTAL_CODE
645 INTO
646 l_location_rec.LOCATION_ID,
647 l_location_rec.ADDRESS1,
648 l_location_rec.ADDRESS2,
649 l_location_rec.ADDRESS3,
650 l_location_rec.ADDRESS4,
651 l_location_rec.POSTAL_CODE
652 FROM HZ_LOCATIONS
653 WHERE LOCATION_ID = p_location_id;
654
655 RETURN l_location_rec;
656 END Get_Location_Rec;
657
658
659
660 /********************************************************/
661 /******************* Public Routines *******************/
662 /********************************************************/
663
664
665 /*===========================================================================+
666 | FUNCTION |
667 | Generate_Key |
668 | |
669 | DESCRIPTION |
670 | Function to generate a party (organization/perosn/group) key, |
671 | address key for use in fuzzy find by TCA. |
672 | |
673 | SCOPE - Public |
674 | |
675 | ARGUMENTS : IN: |
676 | p_key_type |
677 | p_party_name |
678 | p_address1 |
679 | p_address2 |
680 | p_address3 |
681 | p_address4 |
682 | p_postal_code |
683 | p_first_name |
684 | p_last_name |
685 | OUT: |
686 | IN/ OUT: |
687 | |
691 | p_key_type must be 'ORGANIZATION', 'PERSON', 'GROUP', 'ADDRESS'. |
688 | RETURNS : l_key |
689 | |
690 | NOTES : |
692 | For key_type 'ORGANIZATION', 'GROUP' party name is needed |
693 | For key_type 'ADDRESS', address1/address2/address3/address4 and |
694 | postal_code are required |
695 | For key_type 'PERSON' first_name and last_name should be passed in |
696 | |
697 | MODIFICATION HISTORY |
698 | Indrajit Sen 22-JUN-00 Created |
699 | Jianying Huang 20-FEB-00 Bug 1651795: Replace 'CUSTOMER' with |
700 | P_KEY_TYPE when call Replace_Word for key type = |
701 | 'ORGANIZATION', 'GROUP' etc. |
702 | |
703 | H. Yu 05-MAR-01 Mod Generate_Key to use hz_common_pub.cleanse|
704 | Indrajit Sen 10-OCT-01 Cleanse function is put in this package |
705 | Indrajit Sen 25-OCT-01 G_MISS_CHAR is handled properly
706 +===========================================================================*/
707
708 FUNCTION Generate_Key (p_key_type VARCHAR2,
709 p_party_name VARCHAR2 DEFAULT NULL,
710 p_address1 VARCHAR2 DEFAULT NULL,
711 p_address2 VARCHAR2 DEFAULT NULL,
712 p_address3 VARCHAR2 DEFAULT NULL,
713 p_address4 VARCHAR2 DEFAULT NULL,
714 p_postal_code VARCHAR2 DEFAULT NULL,
715 p_first_name VARCHAR2 DEFAULT NULL,
716 p_last_name VARCHAR2 DEFAULT NULL
717 ) RETURN VARCHAR2
718 IS
719 l_word_count NUMBER;
720 l_word_length NUMBER;
721 l_address_index NUMBER;
722 l_key VARCHAR2(2000);
723 l_key2 VARCHAR2(2000);
724 l_party_word VARCHAR2(2000);
725 l_address_word VARCHAR2(2000);
726 l_postal_word VARCHAR2(2000);
727 l_key_type VARCHAR2(30);
728 l_party_name VARCHAR2(360);
729 l_first_name VARCHAR2(150);
730 l_last_name VARCHAR2(150);
731 l_address1 VARCHAR2(240);
732 l_address2 VARCHAR2(240);
733 l_address3 VARCHAR2(240);
734 l_address4 VARCHAR2(240);
735 l_postal_code VARCHAR2(60);
736
737 BEGIN
738
739 -- Step 1.
740 -- Convert all the input information to upper
741 -- so that rules are applied to upper words
742 l_key_type := upper(p_key_type);
743 l_party_name := upper(p_party_name);
744 l_first_name := upper(p_first_name);
745 l_last_name := upper(p_last_name);
746 l_address1 := upper(p_address1);
747 l_address2 := upper(p_address2);
748 l_address3 := upper(p_address3);
749 l_address4 := upper(p_address4);
750 l_postal_code := upper(p_postal_code);
751
752 -- for key type 'ORGANIZATION' / 'GROUP'
753 IF (l_key_type = 'ORGANIZATION') OR
754 (l_key_type = 'GROUP')
755 THEN
756 -- since group is similar to organization in terms of key generation
757 -- logic, we should set the key_type to ORGANIZATION so that replacement
758 -- rules for organization can be applied to group as weel.
759 l_key_type := 'ORGANIZATION';
760
761 -- Org or Group key is generated for HZ_CUSTOMER_KEY_WORD_COUNT words in the party name
762 -- We need to parse the party name to figure out how much information to pass to
763 -- the replace word function
764 l_word_count := to_number( (NVL(FND_PROFILE.Value('HZ_KEY_WORD_COUNT'), '4')) );
765 l_word_length := instrb(l_party_name, ' ', 1, l_word_count);
766 IF l_word_length = 0
767 THEN
768 l_word_length := lengthb(l_party_name);
769 END IF;
770 l_party_word := substrb(l_party_name, 1, l_word_length);
771
772 -- Generate the key for Org or Group
773 l_key := Replace_Word(l_party_word, l_key_type);
774
775 -- for key type 'ADDRESS'
776 ELSIF (l_key_type = 'ADDRESS')
777 THEN
778 -- First generate address line portion of key
779 --
780 -- The address key is either address1.postal_code or address2.postal_code
781 -- or address3.postal_code or address4.postal_code
782 -- depending upon profile HZ_ADDRESS_KEY_INDEX
783 --
784 -- l_address_index := to_number(NVL(FND_PROFILE.Value('HZ_ADDRESS_KEY_INDEX'), '1') );
785
786 -- The above code is remarked now as we want to generate keys using address1
787 -- only for the time being. Later when fuzzy search would be related to the
788 -- above profile option setting, the address column will be selected based
789 -- on the profile value.
790 l_address_index := 1;
791
792 IF (l_address_index = 1)
793 THEN
794 l_address_word := l_address1;
795 ELSIF (l_address_index = 2)
796 THEN
797 l_address_word := l_address2;
798 ELSIF (l_address_index = 3)
799 THEN
800 l_address_word := l_address3;
801 ELSE
802 l_address_word := l_address4;
803 END IF;
804
805 -- if FND_API.G_MISS_CHAR is passed for address word, then treat it as NULL
806 if l_address_word = FND_API.G_MISS_CHAR then
810 -- Generate the address line portion of the key
807 l_address_word := NULL;
808 end if;
809
811 --
812 l_key := Replace_Word(l_address_word, 'ADDRESS');
813
814 -- Truncate to be the length of the Address Key Length Profile
815 --
816 l_word_length := to_number(NVL(FND_PROFILE.Value('HZ_ADDRESS_KEY_LENGTH'), '15'));
817 l_key := substrb(l_key, 1, l_word_length);
818
819 -- Get the amount of the postal code to use for generating the key
820 --
821 l_word_length := to_number(NVL(FND_PROFILE.Value('HZ_POSTAL_CODE_KEY_LENGTH'), lengthb(l_postal_code) ));
822 l_postal_word := substrb(l_postal_code, 1, l_word_length);
823
824 -- if FND_API.G_MISS_CHAR has been passed for postal code, that should be treated as NULL
825 if l_postal_word = FND_API.G_MISS_CHAR then
826 l_postal_word := NULL;
827 end if;
828
829 -- Generate the postal code portion of key
830 --
831 l_key2 := Replace_Word(l_postal_word, 'ADDRESS');
832
833 -- Since only the address1 is guaranteed to be NOT NULL, we need to check if the parts
834 -- of the key are not NULL
835 IF l_key is NOT NULL
836 THEN
837 IF l_key2 is NOT NULL
838 THEN
839 l_key := l_key || '.' || l_key2;
840 END IF;
841 ELSIF l_key2 is NOT NULL
842 THEN
843 l_key := l_key2;
844 END IF;
845
846 -- for key type 'PERSON'
847 ELSIF l_key_type = 'PERSON'
848 THEN
849 -- if FND_API.G_MISS_CHAR has been passed for first name or last name,
850 -- then those should be treated as NULL
851 if l_first_name = FND_API.G_MISS_CHAR then
852 l_first_name := NULL;
853 end if;
854
855 if l_last_name = FND_API.G_MISS_CHAR then
856 l_last_name := NULL;
857 end if;
858
859 -- Generate the person key.
860 -- The key is made up of LAST_NAME.FIRST_NAME or LAST_NAME if first name is NULL
861 --
862 l_key := Replace_Word(l_last_name, 'PERSON');
863
864 IF (l_first_name is NOT NULL)
865 THEN
866 IF l_key is NOT NULL
867 THEN
868 l_key := l_key || '.';
869 END IF;
870 l_key := l_key || Replace_Word(l_first_name, 'PERSON');
871 END IF;
872 END IF;
873
874 RETURN l_key;
875
876 END Generate_Key;
877
878
879 /*===========================================================================+
880 | PROCEDURE |
881 | Generate_Full_Table_Key |
882 | |
883 | DESCRIPTION |
884 | Procedure to generate full table key for a particular type of key |
885 | This program is designed to be run as concurrent program |
886 | |
887 | SCOPE - Public |
888 | |
889 | ARGUMENTS : IN: |
890 | Errbuf |
891 | Retcode |
892 | p_key_type |
893 | p_new_rows |
894 | OUT: |
895 | IN/ OUT: |
896 | |
897 | RETURNS : l_location_rec |
898 | |
899 | NOTES : p_key_type can be PARTY, ADDRESS |
900 | p_new_rows can be 'Y', 'N' |
901 | |
902 | MODIFICATION HISTORY |
903 | Indrajit Sen 22-JUN-00 Created |
904 | Sisir 26-SEP-01 Bug No:1969345;Added who columns in the |
905 | update of HZ_PARTIES ,HZ_LOCATIONS tables. |
906 | Rajib R Borah 12-DEC-03 Bug 3142242.Reverted the changes done in |
907 | bug fix 1969345.Who columns should be updated|
908 | only when a business attribute is updated. |
909 +===========================================================================*/
910
911 PROCEDURE Generate_Full_Table_Key (
912 Errbuf OUT NOCOPY VARCHAR2,
913 Retcode OUT NOCOPY VARCHAR2,
914 p_key_type IN VARCHAR2 DEFAULT NULL,
915 p_new_rows IN VARCHAR2 DEFAULT 'Y'
916 )
917 IS
918 -- cursor to read all the party ids in the parties table.
919 CURSOR C_Party_Ids (l_party_id NUMBER) IS
920 SELECT party_id
921 FROM hz_parties
922 WHERE party_id > l_party_id
923 AND party_type in ('PERSON', 'ORGANIZATION', 'GROUP');
924
925 -- cursor to read all the party ids in the parties table where customer_key is null.
929 WHERE customer_key is NULL
926 CURSOR C_Party_Ids_New_Rows (l_party_id NUMBER) IS
927 SELECT party_id
928 FROM hz_parties
930 AND party_id > l_party_id
931 AND party_type in ('PERSON', 'ORGANIZATION', 'GROUP');
932
933 -- cursor to read all the location ids in the locations table.
934 CURSOR C_Location_Ids (l_location_id NUMBER) IS
935 SELECT location_id
936 FROM hz_locations
937 WHERE location_id > l_location_id;
938
939 -- cursor to read all the location ids in the locations table where address_key is null.
940 CURSOR C_Location_Ids_New_Rows (l_location_id NUMBER) IS
941 SELECT location_id
942 FROM hz_locations
943 WHERE address_key is NULL
944 AND location_id > l_location_id;
945
946 l_party_rec PARTY_REC_TYPE;
947 l_location_rec LOCATION_REC_TYPE;
948 l_key VARCHAR2(2000);
949 l_rec_count NUMBER := 0;
950 l_party_id HZ_PARTIES.PARTY_ID%TYPE ;
951 l_last_party_id HZ_PARTIES.PARTY_ID%TYPE := 0;
952 l_location_id HZ_LOCATIONS.LOCATION_ID%TYPE ;
953 l_last_location_id HZ_LOCATIONS.LOCATION_ID%TYPE := 0;
954 l_flag VARCHAR2(1);
955 BEGIN
956
957 -- send the run details to the log file
958 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Concurrent program ARXFZKEY - Generate Key.');
959 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
960 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Options - ');
961 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Key type : '||p_key_type);
962 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Only new rows : '||p_new_rows);
963 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
964
965 IF p_key_type = 'PARTY' -- generate keys for the hz_parties table
966 THEN
967 IF p_new_rows = 'Y'
968 THEN
969 LOOP
970 OPEN C_Party_Ids_New_Rows(l_last_party_id);
971 LOOP
972 BEGIN
973 FETCH C_Party_Ids_New_Rows INTO l_party_id;
974 EXCEPTION
975 WHEN OTHERS THEN
976 IF SQLCODE = -1555 THEN
977 CLOSE C_Party_Ids_New_Rows;
978 EXIT;
979 ELSE
980 RAISE;
981 END IF;
982 END;
983 IF C_Party_Ids_New_Rows%NOTFOUND THEN
984 l_flag := 'Y';
985 EXIT;
986 END IF;
987 l_party_rec := Get_Party_Rec (l_party_id);
988 l_key := Generate_Key (
989 p_key_type => l_party_rec.party_type,
990 p_party_name => l_party_rec.party_name,
991 p_address1 => NULL,
992 p_address2 => NULL,
993 p_address3 => NULL,
994 p_address4 => NULL,
995 p_postal_code => NULL,
996 p_first_name => l_party_rec.first_name,
997 p_last_name => l_party_rec.last_name);
998 -- update the parties record with the new key
999 UPDATE HZ_PARTIES
1000 SET customer_key = l_key/*Bug 3142242
1001 ,LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1002 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
1003 PROGRAM_APPLICATION_ID=HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
1004 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
1005 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
1006 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1007 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY */
1008 WHERE party_id = l_party_id;
1009 l_last_party_id := l_party_id;
1010 COMMIT;
1011 END LOOP;
1012 IF l_flag = 'Y' THEN
1013 EXIT;
1014 END IF;
1015 END LOOP;
1016 ELSIF p_new_rows = 'N'
1017 THEN
1018 LOOP
1019 OPEN C_Party_Ids(l_last_party_id);
1020 LOOP
1021 BEGIN
1022 FETCH C_Party_Ids INTO l_party_id;
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 IF SQLCODE = -1555 THEN
1026 CLOSE C_Party_Ids;
1027 EXIT;
1028 ELSE
1029 RAISE;
1030 END IF;
1031 END;
1032 IF C_Party_Ids%NOTFOUND THEN
1033 l_flag := 'Y';
1034 EXIT;
1035 END IF;
1036 l_party_rec := Get_Party_Rec (l_party_id);
1037 l_key := Generate_Key (
1038 p_key_type => l_party_rec.party_type,
1039 p_party_name => l_party_rec.party_name,
1040 p_address1 => NULL,
1041 p_address2 => NULL,
1042 p_address3 => NULL,
1043 p_address4 => NULL,
1044 p_postal_code => NULL,
1045 p_first_name => l_party_rec.first_name,
1046 p_last_name => l_party_rec.last_name);
1047
1048 -- update the parties record with the new key
1049 UPDATE HZ_PARTIES
1050 SET customer_key = l_key/*Bug 3142242
1051 ,LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1052 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
1053 PROGRAM_APPLICATION_ID=HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
1054 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
1055 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
1056 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1057 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY */
1058 WHERE party_id = l_party_id;
1059 l_last_party_id := l_party_id;
1060 COMMIT;
1061 END LOOP;
1062 IF l_flag = 'Y' THEN
1063 EXIT;
1064 END IF;
1065 END LOOP;
1066 END IF;
1067
1068 ELSIF p_key_type = 'ADDRESS'
1069 THEN
1070 IF p_new_rows = 'Y'
1071 THEN
1072 LOOP
1073 OPEN C_Location_Ids_New_Rows(l_last_location_id);
1074 LOOP
1075 BEGIN
1076 FETCH C_Location_Ids_New_Rows INTO l_location_id;
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 IF SQLCODE = -1555 THEN
1080 CLOSE C_Location_Ids_New_Rows;
1081 EXIT;
1082 ELSE
1083 RAISE;
1084 END IF;
1085 END;
1086 IF C_Location_Ids_New_Rows%NOTFOUND THEN
1087 l_flag := 'Y';
1088 EXIT;
1089 END IF;
1090 l_location_rec := Get_Location_Rec (l_location_id);
1091 l_key := Generate_Key (
1092 p_key_type => 'ADDRESS',
1093 p_party_name => NULL,
1094 p_address1 => l_location_rec.address1,
1095 p_address2 => l_location_rec.address2,
1096 p_address3 => l_location_rec.address3,
1097 p_address4 => l_location_rec.address4,
1098 p_postal_code => l_location_rec.postal_code,
1099 p_first_name => NULL,
1100 p_last_name => NULL);
1101 -- update the locations record with the new key
1102 UPDATE HZ_LOCATIONS
1103 SET address_key = l_key/*Bug 3142242
1104 ,LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1105 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
1106 PROGRAM_APPLICATION_ID=HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
1107 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
1108 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
1109 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1110 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY */
1111 WHERE location_id = l_location_id;
1112 l_last_location_id := l_location_id;
1113 COMMIT;
1114 END LOOP;
1115 IF l_flag = 'Y' THEN
1116 EXIT;
1117 END IF;
1118 END LOOP;
1119 ELSIF p_new_rows = 'N'
1120 THEN
1121 LOOP
1122 OPEN C_Location_Ids(l_last_location_id);
1123 LOOP
1124 BEGIN
1125 FETCH C_Location_Ids INTO l_location_id;
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 IF SQLCODE = -1555 THEN
1129 CLOSE C_Location_Ids;
1130 EXIT;
1131 ELSE
1132 RAISE;
1133 END IF;
1134 END;
1135 IF C_Location_Ids%NOTFOUND THEN
1136 l_flag := 'Y';
1137 EXIT;
1138 END IF;
1139 l_location_rec := Get_Location_Rec (l_location_id);
1140 l_key := Generate_Key (
1141 p_key_type => 'ADDRESS',
1142 p_party_name => NULL,
1143 p_address1 => l_location_rec.address1,
1144 p_address2 => l_location_rec.address2,
1145 p_address3 => l_location_rec.address3,
1146 p_address4 => l_location_rec.address4,
1147 p_postal_code => l_location_rec.postal_code,
1148 p_first_name => NULL,
1149 p_last_name => NULL);
1150 -- update the locations record with the new key
1151 UPDATE HZ_LOCATIONS
1152 SET address_key = l_key/*Bug 3142242
1153 ,LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1154 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
1158 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1155 PROGRAM_APPLICATION_ID=HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
1156 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
1157 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
1159 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY */
1160 WHERE location_id = l_location_id;
1161 l_last_location_id := l_location_id;
1162 COMMIT;
1163 END LOOP;
1164 IF l_flag = 'Y' THEN
1165 EXIT;
1166 END IF;
1167 END LOOP;
1168 END IF;
1169 END IF;
1170 COMMIT;
1171 -- send count info to the log file
1172 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1173 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1174 FND_FILE.PUT_LINE (FND_FILE.LOG, to_char(l_rec_count) || ' ' || p_key_type || ' keys generated.');
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 arp_util.debug('OTHERS : hz_fuzzy_pub.generate_full_table_key');
1179 Errbuf := fnd_message.get||' '||SQLERRM;
1180 Retcode := 2;
1181
1182 END Generate_Full_Table_Key;
1183
1184 /*===========================================================================+
1185 | PROCEDURE |
1186 | Is_Duplicate_Party |
1187 | |
1188 | DESCRIPTION |
1189 | Public procedure for checking if the passed party information |
1190 | is a duplicate of an existing party. If program finds that it is a |
1191 | duplicate record, it sets p_duplicate to fnd_api.g_true. Also a |
1192 | message is set and one matching party_id is returned. |
1193 | |
1194 | SCOPE - Public |
1195 | |
1196 | ARGUMENTS : IN: |
1197 | p_party_type |
1198 | p_person_first_name |
1199 | p_person_last_name |
1200 | p_party_name |
1201 | p_key_search_flag |
1202 | OUT: |
1203 | p_duplicate |
1204 | p_msg_count |
1205 | p_msg_data |
1206 | p_party_id |
1207 | IN/ OUT: |
1208 | |
1209 | RETURNS : None |
1210 | |
1211 | NOTES : It does not return all the matching parties. |
1212 | |
1213 | MODIFICATION HISTORY |
1214 | Indrajit Sen 22-JUN-00 Created |
1215 | |
1216 +===========================================================================*/
1217
1218 PROCEDURE Is_Duplicate_Party (
1219 p_party_type IN VARCHAR2,
1220 p_person_first_name IN VARCHAR2 DEFAULT NULL,
1221 p_person_last_name IN VARCHAR2 DEFAULT NULL,
1222 p_party_name IN VARCHAR2 DEFAULT NULL,
1223 p_key_search_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1224 p_duplicate OUT NOCOPY VARCHAR2,
1225 p_msg_count OUT NOCOPY NUMBER,
1226 p_msg_data OUT NOCOPY VARCHAR2,
1227 p_party_id OUT NOCOPY NUMBER
1228 ) IS
1229
1230 l_party_key hz_parties.customer_key%TYPE;
1231 l_party_id hz_parties.party_id%TYPE;
1232 l_party_name hz_parties.party_name%TYPE;
1233 l_subject_name hz_parties.party_name%TYPE;
1234 l_object_name hz_parties.party_name%TYPE;
1235 l_is_duplicate varchar2(10);
1236
1237 begin
1238 p_duplicate := fnd_api.g_false;
1239
1240 IF p_party_type = 'PERSON'
1241 THEN
1242 -- generate the party key for person (necessary for duplicate checking)
1243 l_party_key := Generate_Key (
1244 p_key_type => p_party_type,
1245 p_first_name => p_person_first_name,
1246 p_last_name => p_person_last_name);
1247
1248 -- now check if party record already exists
1249 -- pass the person name and key for the duplicate checking
1250 Find_Duplicate_Party (
1251 p_party_name => p_person_first_name||' '||p_person_last_name,
1252 p_party_key => l_party_key,
1253 p_key_search_flag => p_key_search_flag,
1254 p_party_id => l_party_id,
1255 p_is_duplicate => l_is_duplicate);
1256 ELSE
1257 l_party_name := p_party_name;
1258
1259 -- generate the party key for organization/group (necessary for duplicate checking)
1260 l_party_key := Generate_Key (
1261 p_key_type => p_party_type,
1262 p_party_name => l_party_name);
1263
1264 -- now check if party record already exists
1265 -- pass the person name and key for the duplicate checking
1266 Find_Duplicate_Party (
1267 p_party_name => l_party_name,
1268 p_party_key => l_party_key,
1269 p_key_search_flag => p_key_search_flag,
1270 p_party_id => l_party_id,
1271 p_is_duplicate => l_is_duplicate);
1272
1273 END IF;
1274
1275 p_duplicate := l_is_duplicate;
1276
1277 if fnd_api.to_boolean (l_is_duplicate)
1278 then
1279 p_party_id := l_party_id;
1280 p_duplicate := fnd_api.g_true;
1281
1282 fnd_message.set_name ('AR', 'HZ_MATCHING_PARTY_EXISTS');
1283 fnd_msg_pub.add;
1284 end if;
1285
1286 end Is_Duplicate_Party;
1287
1288
1289 /*===========================================================================+
1290 | PROCEDURE |
1291 | Is_Duplicate_Location |
1292 | |
1293 | DESCRIPTION |
1294 | Public procedure for checking if the passed location information |
1295 | is a duplicate of an existing location.If a duplicate location is |
1296 | found, p_duplicate is set to fnd_api.g_true and a message is also set |
1297 | stating that. p_key_search_flag determines whether to do a key match |
1298 | or exact match. |
1299 | |
1300 | SCOPE - Public |
1301 | |
1302 | ARGUMENTS : IN: |
1303 | p_address1 |
1304 | p_address2 |
1305 | p_address3 |
1306 | p_address4 |
1307 | p_postal_code |
1308 | p_state |
1309 | p_city |
1310 | p_country |
1311 | p_key_search_flag |
1312 | OUT: |
1313 | p_duplicate |
1314 | p_msg_count |
1315 | p_msg_data |
1316 | p_location_id |
1317 | IN/ OUT: |
1318 | |
1319 | RETURNS : None |
1320 | |
1321 | NOTES : It does not return all the matching locations. |
1322 | |
1323 | MODIFICATION HISTORY |
1324 | Indrajit Sen 22-JUN-00 Created |
1325 | |
1326 +===========================================================================*/
1327
1328
1329 PROCEDURE Is_Duplicate_Location(
1330 p_address1 IN VARCHAR2 DEFAULT NULL,
1331 p_address2 IN VARCHAR2 DEFAULT NULL,
1332 p_address3 IN VARCHAR2 DEFAULT NULL,
1333 p_address4 IN VARCHAR2 DEFAULT NULL,
1334 p_postal_code IN VARCHAR2 DEFAULT NULL,
1335 p_state IN VARCHAR2 DEFAULT NULL,
1336 p_city IN VARCHAR2 DEFAULT NULL,
1337 p_country IN VARCHAR2 DEFAULT NULL,
1338 p_key_search_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1339 p_duplicate OUT NOCOPY VARCHAR2,
1340 p_msg_count OUT NOCOPY NUMBER,
1341 p_msg_data OUT NOCOPY VARCHAR2,
1342 p_location_id OUT NOCOPY NUMBER) IS
1343
1344 l_address_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
1345 l_address_key VARCHAR2(2000);
1346 l_location_id NUMBER;
1347 l_is_duplicate VARCHAR2(10);
1348
1349 begin
1350 p_duplicate := fnd_api.g_false;
1351
1352 -- generate the address key (necessary for duplicate checking)
1353 l_address_key := Generate_Key('ADDRESS',
1354 p_address1,
1355 p_address2,
1356 p_address3,
1357 p_address4,
1358 p_postal_code);
1359
1360 l_address_rec.address1 := p_address1;
1361 l_address_rec.address2 := p_address2;
1362 l_address_rec.address3 := p_address3;
1363 l_address_rec.address4 := p_address4;
1364 l_address_rec.postal_code := p_postal_code;
1365 l_address_rec.state := p_state;
1366 l_address_rec.city := p_city;
1367 l_address_rec.country := p_country;
1368
1369 Find_Duplicate_Address (
1370 p_address_rec => l_address_rec,
1371 p_key_search_flag => p_key_search_flag,
1372 p_address_key => l_address_key,
1373 p_location_id => l_location_id,
1374 p_is_duplicate => l_is_duplicate);
1375
1376 p_duplicate := l_is_duplicate;
1377
1378 if fnd_api.to_boolean (l_is_duplicate)
1379 then
1380 p_location_id := l_location_id;
1381 p_duplicate := fnd_api.g_true;
1382
1383 fnd_message.set_name ('AR', 'MATCHING_LOCATION_EXISTS');
1384 fnd_msg_pub.add;
1385 end if;
1386
1387 end is_duplicate_location;
1388
1389
1390 /*===========================================================================+
1391 | PROCEDURE |
1392 | Fuzzy_Search_Address |
1393 | |
1394 | DESCRIPTION |
1395 | Public procedure for doing fuzzy search for address. Pass the address |
1396 | lines and the postal code. The number of duplicate records found |
1397 | is returned in p_count and list of location ids is returned in |
1398 | p_location_tbl. |
1399 | |
1400 | SCOPE - Public |
1401 | |
1402 | ARGUMENTS : IN: |
1403 | p_address1 |
1404 | p_address2 |
1405 | p_address3 |
1406 | p_address4 |
1407 | p_postal_code |
1408 | OUT: |
1409 | p_location_tbl |
1410 | p_count |
1411 | IN/ OUT: |
1412 | |
1413 | RETURNS : None |
1414 | |
1415 | NOTES : This does fuzzy search based on the generated key only. |
1416 | |
1417 | MODIFICATION HISTORY |
1421
1418 | Indrajit Sen 22-JUN-00 Created |
1419 | |
1420 +===========================================================================*/
1422 PROCEDURE Fuzzy_Search_Address(
1423 p_address1 IN HZ_LOCATIONS.ADDRESS1%TYPE DEFAULT NULL,
1424 p_address2 IN HZ_LOCATIONS.ADDRESS2%TYPE DEFAULT NULL,
1425 p_address3 IN HZ_LOCATIONS.ADDRESS3%TYPE DEFAULT NULL,
1426 p_address4 IN HZ_LOCATIONS.ADDRESS4%TYPE DEFAULT NULL,
1427 p_postal_code IN HZ_LOCATIONS.POSTAL_CODE%TYPE DEFAULT NULL,
1428 p_location_tbl OUT NOCOPY LOCATION_TBL_TYPE,
1429 p_count OUT NOCOPY NUMBER) IS
1430
1431 l_addr_key VARCHAR2(2000);
1432 l_post_key VARCHAR2(2000);
1433 l_location_id NUMBER;
1434 l_location_tbl LOCATION_TBL_TYPE;
1435 l_count NUMBER := 0;
1436
1437 CURSOR c_locations (X_Addr_Key VARCHAR2, X_Post_Key VARCHAR2)
1438 IS
1439 SELECT location_id
1440 FROM hz_locations
1441 WHERE address_key like X_Addr_Key||'%'||X_Post_Key||'%';
1442
1443 begin
1444 -- generate the key with only the address part (not including postal code)
1445 l_addr_key := Generate_Key (
1446 p_key_type => 'ADDRESS',
1447 p_address1 => p_address1,
1448 p_address2 => p_address2,
1449 p_address3 => p_address3,
1450 p_address4 => p_address4
1451 );
1452
1453 -- generate the key with only the postal code part (not including address fields)
1454 l_post_key := Generate_Key (
1455 p_key_type => 'ADDRESS',
1456 p_postal_code => p_postal_code
1457 );
1458
1459 -- get all the locations which has key value l_addr_key%l_post_key%
1460 FOR loc in c_locations (l_addr_key, l_post_key)
1461 LOOP
1462 l_count := l_count + 1;
1463 l_location_tbl (l_count) := loc.location_id;
1464 END LOOP;
1465
1466 p_location_tbl := l_location_tbl;
1467 p_count := l_count;
1468
1469 end Fuzzy_Search_Address;
1470
1471
1472 /*===========================================================================+
1473 | PROCEDURE |
1474 | Fuzzy_Search_Party |
1475 | |
1476 | DESCRIPTION |
1477 | Public procedure for doing fuzzy search for party. In case of an |
1478 | organization search, pass party_name and in case of person search |
1479 | pass forst_name and last_name. The number of duplicate records found |
1480 | is returned in p_count and list of party ids is returned in |
1481 | p_party_tbl. |
1482 | |
1483 | SCOPE - Public |
1484 | |
1485 | ARGUMENTS : IN: |
1486 | p_party_type |
1487 | p_party_name |
1488 | p_first_name |
1489 | p_last_name |
1490 | OUT: |
1491 | p_party_tbl |
1492 | p_count |
1493 | IN/ OUT: |
1494 | |
1495 | RETURNS : None |
1496 | |
1497 | NOTES : This does fuzzy search based on the generated key only. |
1498 | |
1499 | MODIFICATION HISTORY |
1500 | Indrajit Sen 22-JUN-00 Created |
1501 | |
1502 +===========================================================================*/
1503
1504
1505 PROCEDURE Fuzzy_Search_Party(
1506 p_party_type IN HZ_PARTIES.PARTY_TYPE%TYPE,
1507 p_party_name IN HZ_PARTIES.PARTY_NAME%TYPE DEFAULT NULL,
1508 p_first_name IN HZ_PARTIES.PERSON_FIRST_NAME%TYPE DEFAULT NULL,
1509 p_last_name IN HZ_PARTIES.PERSON_LAST_NAME%TYPE DEFAULT NULL,
1510 p_party_tbl OUT NOCOPY PARTY_TBL_TYPE,
1511 p_count OUT NOCOPY NUMBER) IS
1512
1513 l_first_name_key VARCHAR2(2000);
1514 l_last_name_key VARCHAR2(2000);
1515 l_party_name_key VARCHAR2(2000);
1516 l_party_id NUMBER;
1517 l_party_tbl PARTY_TBL_TYPE;
1518 l_count NUMBER := 0;
1519
1520 CURSOR c_org_parties (X_Party_Name_Key VARCHAR2)
1521 IS
1522 SELECT party_id
1523 FROM hz_parties
1524 WHERE customer_key like X_Party_Name_Key||'%'
1525 AND party_type = 'ORGANIZATION';
1526
1527 CURSOR c_per_parties (X_First_Name_Key VARCHAR2, X_Last_Name_Key VARCHAR2)
1528 IS
1529 SELECT party_id
1530 FROM hz_parties
1531 WHERE customer_key like X_Last_Name_Key||'%'||X_First_Name_Key||'%'
1532 AND party_type = 'PERSON';
1533
1534 begin
1535 -- if party type is organization then
1536 IF p_party_type = 'ORGANIZATION'
1537 THEN
1538 -- generate the key for the organization type party
1539 l_party_name_key := Generate_Key (
1540 p_key_type => 'ORGANIZATION',
1541 p_party_name => p_party_name
1542 );
1543
1544 -- get all the parties which has key value l_party_name_key%
1545 FOR org in c_org_parties (l_party_name_key)
1546 LOOP
1547 l_count := l_count + 1;
1548 l_party_tbl (l_count) := org.party_id;
1549 END LOOP;
1550 -- else if party type is person then
1551 ELSIF p_party_type = 'PERSON'
1552 THEN
1553 -- generate the key for the person type party
1554 -- first generate last name key
1555 l_last_name_key := Generate_Key (
1556 p_key_type => 'PERSON',
1557 p_last_name => p_last_name
1558 );
1559 -- then generate first name key
1560 l_first_name_key := Generate_Key (
1561 p_key_type => 'PERSON',
1562 p_first_name => p_first_name
1563 );
1564
1565 -- get all the parties which has key value l_last_name_key%l_first_name_key%
1566 FOR per in c_per_parties (l_first_name_key, l_last_name_key)
1567 LOOP
1568 l_count := l_count + 1;
1569 l_party_tbl(l_count) := per.party_id;
1570 END LOOP;
1571 END IF;
1572
1573 -- prepare the return parameters
1574 p_party_tbl := l_party_tbl;
1575 p_count := l_count;
1576
1577 end Fuzzy_Search_Party;
1578
1579 END HZ_FUZZY_PUB;
[Home] [Help]