[Home] [Help]
PACKAGE BODY: APPS.HZ_TRANS_PKG
Source
1 PACKAGE BODY HZ_TRANS_PKG AS
2 /*$Header: ARHDQTRB.pls 120.32.12010000.2 2008/10/06 10:41:49 amstephe ship $ */
3
4 /*************** Globals *****************************/
5 g_threshold_length NUMBER := 6;
6
7 g_unprintables VARCHAR2(400) :=
8 fnd_global.local_chr(0)||fnd_global.local_chr(1)||fnd_global.local_chr(2)||
9 fnd_global.local_chr(3)||fnd_global.local_chr(4)||fnd_global.local_chr(5)||
10 fnd_global.local_chr(6)||fnd_global.local_chr(7)||fnd_global.local_chr(8)||
11 fnd_global.local_chr(9)||fnd_global.local_chr(10)|| fnd_global.local_chr(10)||
12 fnd_global.local_chr(11)||fnd_global.local_chr(12)||fnd_global.local_chr(13)||
13 fnd_global.local_chr(14)||fnd_global.local_chr(15)||fnd_global.local_chr(16)||
14 fnd_global.local_chr(17)||fnd_global.local_chr(18)||fnd_global.local_chr(19)||
15 fnd_global.local_chr(20)||fnd_global.local_chr(20)||fnd_global.local_chr(21)||
16 fnd_global.local_chr(22)||fnd_global.local_chr(23)||fnd_global.local_chr(24)||
17 fnd_global.local_chr(25)||fnd_global.local_chr(26)||fnd_global.local_chr(27)||
18 fnd_global.local_chr(28)||fnd_global.local_chr(29)||fnd_global.local_chr(30)||
19 fnd_global.local_chr(31);
20
21 g_last_wrperson_exact VARCHAR2(2000):='-1';
22 g_last_wrorg_exact VARCHAR2(2000):='-1';
23 g_last_wrnames_exact VARCHAR2(2000):='-1';
24 g_last_wraddr_exact VARCHAR2(2000):='-1';
25 g_last_wrstate_exact VARCHAR2(2000):='-1';
26 g_last_wrperson_orig VARCHAR2(2000):='-1';
27 g_last_wrorg_orig VARCHAR2(2000):='-1';
28 g_last_wrnames_orig VARCHAR2(2000):='-1';
29 g_last_wraddr_orig VARCHAR2(2000):='-1';
30 g_last_wrstate_orig VARCHAR2(2000):='-1';
31 g_last_wrstr VARCHAR2(4000):='-1';
32 g_last_wrstr_repl VARCHAR2(4000):='-1';
33 g_last_repl_set NUMBER := -1;
34
35 g_party_type VARCHAR2(30) := NULL;
36
37 g_match_rule_purpose VARCHAR2(1) := 'N';
38 g_dqm_wildchar_search boolean := false;
39 l_is_wildchar NUMBER;
40
41 -- VJN Introduced global variables for word_replace_noise_words_only to avoid caching
42 g_last_wrstr_1 VARCHAR2(4000):='-1';
43 g_last_wrstr_repl_1 VARCHAR2(4000):='-1';
44 g_last_repl_set_1 NUMBER := -1;
45
46 -- VJN Introduced global variable for Extension to Latin Characters.
47 g_latin_from VARCHAR2(32000);
48 g_latin_to VARCHAR2(32000);
49
50
51 TYPE charTab IS TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;
52 g_exact_for_cleansed charTab;
53
54 FUNCTION PARTYNAMES_EXACT_OLD_PRIVATE(
55 p_original_value IN VARCHAR2,
56 p_language IN VARCHAR2,
57 p_attribute_name IN VARCHAR2,
58 p_entity_name IN VARCHAR2,
59 p_context IN VARCHAR2)
60 RETURN VARCHAR2;
61
62 FUNCTION PARTYNAMES_EXACT_NEW_PRIVATE(
63 p_original_value IN VARCHAR2,
64 p_language IN VARCHAR2,
65 p_attribute_name IN VARCHAR2,
66 p_entity_name IN VARCHAR2,
67 p_context IN VARCHAR2)
68 RETURN VARCHAR2;
69
70 FUNCTION org_exact_old_private(
71 p_original_value IN VARCHAR2,
72 p_language IN VARCHAR2,
73 p_attribute_name IN VARCHAR2,
74 p_entity_name IN VARCHAR2,
75 p_context IN VARCHAR2)
76 RETURN VARCHAR2 ;
77
78
79 FUNCTION org_exact_new_private(
80 p_original_value IN VARCHAR2,
81 p_language IN VARCHAR2,
82 p_attribute_name IN VARCHAR2,
83 p_entity_name IN VARCHAR2,
84 p_context IN VARCHAR2)
85 RETURN VARCHAR2;
86
87 FUNCTION person_exact_old_private(
88 p_original_value IN VARCHAR2,
89 p_language IN VARCHAR2,
90 p_attribute_name IN VARCHAR2,
91 p_entity_name IN VARCHAR2,
92 p_context IN VARCHAR2)
93 RETURN VARCHAR2;
94
95 FUNCTION person_exact_new_private(
96 p_original_value IN VARCHAR2,
97 p_language IN VARCHAR2,
98 p_attribute_name IN VARCHAR2,
99 p_entity_name IN VARCHAR2,
100 p_context IN VARCHAR2)
101 RETURN VARCHAR2;
102
103 FUNCTION RM_PERCENTAGE(p_original_value IN VARCHAR2)
104 RETURN VARCHAR2;
105
106 FUNCTION RM_SPLCHAR_PRIVATE (
107 p_original_value IN VARCHAR2,
108 p_language IN VARCHAR2,
109 p_attribute_name IN VARCHAR2,
110 p_entity_name IN VARCHAR2)
111 RETURN VARCHAR2;
112
113 PROCEDURE set_party_type (
114 p_party_type VARCHAR2
115 ) IS
116 BEGIN
117 g_party_type:=p_party_type;
118 g_exact_for_cleansed.DELETE;
119 END;
120
121 PROCEDURE set_bulk_dup_id IS
122 BEGIN
123 g_match_rule_purpose:='Q';
124 END;
125
126 PROCEDURE clear_globals IS
127
128 BEGIN
129 g_last_wrperson_exact :='-1';
130 g_last_wrorg_exact :='-1';
131 g_last_wrnames_exact :='-1';
132 g_last_wraddr_exact:='-1';
133 g_last_wrstate_exact:='-1';
134 g_last_wrperson_orig :='-1';
135 g_last_wrorg_orig :='-1';
136 g_last_wrnames_orig :='-1';
137 g_last_wraddr_orig :='-1';
138 g_last_wrstate_orig :='-1';
139 END;
140
141 /*
142 FUNCTION unprintables RETURN VARCHAR2 IS
143
144 BEGIN
145 IF g_unprintables IS NULL THEN
146 g_unprintables :=
147 fnd_global.local_chr(0)||fnd_global.local_chr(1)||fnd_global.local_chr(2)||
148 fnd_global.local_chr(3)||fnd_global.local_chr(4)||fnd_global.local_chr(5)||
149 fnd_global.local_chr(6)||fnd_global.local_chr(7)||fnd_global.local_chr(8)||
150 fnd_global.local_chr(9)||fnd_global.local_chr(10)|| fnd_global.local_chr(10)||
151 fnd_global.local_chr(11)||fnd_global.local_chr(12)||fnd_global.local_chr(13)||
152 fnd_global.local_chr(14)||fnd_global.local_chr(15)||fnd_global.local_chr(16)||
153 fnd_global.local_chr(17)||fnd_global.local_chr(18)||fnd_global.local_chr(19)||
154 fnd_global.local_chr(20)||fnd_global.local_chr(20)||fnd_global.local_chr(21)||
155 fnd_global.local_chr(22)||fnd_global.local_chr(23)||fnd_global.local_chr(24)||
156 fnd_global.local_chr(25)||fnd_global.local_chr(26)||fnd_global.local_chr(27)||
157 fnd_global.local_chr(28)||fnd_global.local_chr(29)||fnd_global.local_chr(30)||
158 fnd_global.local_chr(31);
159 END IF;
160 RETURN g_unprintables;
161 END;
162 */
163
164 FUNCTION FIRST_WORD (
165 p_original_value IN VARCHAR2,
166 x_rem OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
167 l_exstr VARCHAR2(4000);
168 l_retstr VARCHAR2(4000);
169 l_spc_char NUMBER;
170
171 BEGIN
172 IF (p_original_value IS NULL OR p_original_value = '') THEN
173 return '';
174 end if;
175 l_spc_char := nvl(instr(p_original_value,' '),0);
176 IF l_spc_char = 0 THEN
177 x_rem := '';
178 RETURN p_original_value;
179 ELSE
180 x_rem := ltrim(substr(p_original_value,l_spc_char+1)||' ');
181 RETURN substr(p_original_value,1,l_spc_char-1);
182 END IF;
183 END;
184
185 FUNCTION CLEANSED (
186 p_original_value IN VARCHAR2)
187 RETURN VARCHAR2 IS
188
189 retstr varchar2(4000);
190
191 BEGIN
192
193 IF (p_original_value IS NULL OR p_original_value = '') THEN
194 return '';
195 end if;
196
197 retstr := p_original_value;
198
199 -- Remove Double letters
200 retstr := replace(retstr,'AA','A');
201 retstr := replace(retstr,'BB','B');
202 retstr := replace(retstr,'CC','C');
203 retstr := replace(retstr,'DD','D');
204 retstr := replace(retstr,'EE','E');
205 retstr := replace(retstr,'FF','F');
206 retstr := replace(retstr,'GG','G');
207 retstr := replace(retstr,'HH','H');
208 retstr := replace(retstr,'II','I');
209 retstr := replace(retstr,'JJ','J');
210 retstr := replace(retstr,'KK','K');
211 retstr := replace(retstr,'LL','L');
212 retstr := replace(retstr,'MM','M');
213 retstr := replace(retstr,'NN','N');
214 retstr := replace(retstr,'OO','O');
215 retstr := replace(retstr,'PP','P');
216 retstr := replace(retstr,'QQ','Q');
217 retstr := replace(retstr,'RR','R');
218 retstr := replace(retstr,'SS','S');
219 retstr := replace(retstr,'TT','T');
220 retstr := replace(retstr,'UU','U');
221 retstr := replace(retstr,'VV','V');
222 retstr := replace(retstr,'WW','W');
223 retstr := replace(retstr,'XX','X');
224 retstr := replace(retstr,'YY','Y');
225 retstr := replace(retstr,'ZZ','Z');
226
227 -- Remove Vowels (except from the 1st characters
228 retstr := upper(translate(initcap(lower(retstr)),
229 '%bcdfghjklmnpqrstvwxzyaeiou',
230 '%bcdfghjklmnpqrstvwxz'));
231
232 IF instr(retstr,'%')>0 THEN
233 retstr := replace(retstr,'%A','%');
234 retstr := replace(retstr,'%E','%');
235 retstr := replace(retstr,'%I','%');
236 retstr := replace(retstr,'%O','%');
237 retstr := replace(retstr,'%U','%');
238 retstr := replace(retstr,'%Y','%');
239 END IF;
240
241 RETURN retstr;
242
243 END CLEANSED;
244
245 FUNCTION cleansed_in_score_ctx (p_original_value VARCHAR2) RETURN VARCHAR2 IS
246 BEGIN
247 FOR I in 1..g_exact_for_cleansed.COUNT LOOP
248 IF g_exact_for_cleansed(I) = p_original_value THEN
249 RETURN CLEANSED(p_original_value);
250 END IF;
251 END LOOP;
252 RETURN NULL;
253 END;
254
255 -- VJN introduced procedure for non-delimited word replacement
256
257 -- GOOD EXAMPLES
258
259 -- WORD LIST ID = 10380
260 -- T -> TING MAS -> TUNG
261 -- T -> TING MAS -> TUNG
262 -- TOKEN -- THOMAS, TTHOMAS
263
264 -- WORD LIST ID = 10381
265 -- T -> TING 0 -> ODA Y -> CRAP
266 -- TOKEN -- TOMOY
267
268 -- WORD LIST ID = 10382
269 -- ASAP -> APPLE BASAP -> DUNG
270 -- TOKEN -- ASAPBASAP
271
272
273 -- WORD LIST ID = 10383
274 -- TOM -> TIM M -> MARY
275 -- TOKEN -- TOMMY
276
277
278 PROCEDURE get_repl_token (
279 wr_list_id IN NUMBER,
280 original_str IN VARCHAR2,
281 in_str IN OUT NOCOPY VARCHAR2,
282 repl_token OUT NOCOPY VARCHAR2,
283 remainder_str OUT NOCOPY VARCHAR2,
284 replacement_range IN VARCHAR2
285 )
286 IS
287 FOUND boolean := FALSE ;
288 starting_char VARCHAR2(2 CHAR) ;
289 BEGIN
290
291 starting_char := substr(in_str,1,1) || '%' ;
292 FOR wr_cur in
293 (
294 select WR1.original_word as org_w , WR1.replacement_word as rep_w,
295 WR1.user_spec_cond_value, WR1.condition_id
296 from hz_word_replacements WR1, hz_word_lists WL1
297 where WL1.word_list_id = wr_list_id
298 and WL1.word_list_id = WR1.word_list_id
299 and WR1.original_word like starting_char and instr( in_str, WR1.original_word) = 1
300 and ( ( staging_context = 'Y' and WR1.delete_flag = 'N')
301 OR (nvl(staging_context,'N') = 'N' and WR1.staged_flag = 'Y')
302 )
303 and (WR1.replacement_word IS NULL OR WR1.replacement_word like replacement_range)
304 order by length(WR1.ORIGINAL_WORD) desc
305 )
306 LOOP
307 --dbms_output.put_line( 'In loop original word is ' || original_str );
308 --dbms_output.put_line( 'In loop original string is ' || wr_cur.org_w );
309 --dbms_output.put_line( 'replacement range is ' || replacement_range );
310 --dbms_output.put_line( 'replacement word is ' || wr_cur.rep_w );
311 --dbms_output.put_line( 'condition id is ' || wr_cur.condition_id );
312
313 -- CHECK IF THERE ARE ANY CONDITIONS ASSOCIATED
314 IF wr_cur.condition_id IS NOT NULL
315 THEN
316 -- CONDITION SATISFIED GET THE HELL OUT OF HERE
317 IF HZ_WORD_CONDITIONS_PKG.evaluate_condition (
318 original_str,
319 wr_cur.org_w,
320 wr_cur.rep_w,
321 wr_cur.condition_id,
322 wr_cur.user_spec_cond_value )
323 THEN
324 --dbms_output.put_line('Condition is satisfied');
325 FOUND := TRUE ;
326 repl_token := wr_cur.rep_w ;
327 remainder_str := substr(in_str,length(wr_cur.org_w) + 1);
328 exit ;
329
330 END IF ;
331 -- NO CONDITIONS. GET THE HELL OUT OF HERE !!!!!!
332 ELSE
333 FOUND := TRUE ;
334 repl_token := wr_cur.rep_w ;
335 remainder_str := substr(in_str,length(wr_cur.org_w) + 1);
336 exit ;
337 END IF ;
338 END LOOP;
339 -- if no replacement is found then repl_token is the first character of in_str and
340 -- remainder is substring starting from the second character
341 IF NOT FOUND
342 THEN
343 repl_token := substr(in_str, 1, 1);
344 remainder_str := substr(in_str,2);
345 END IF;
346
347 -- This will be used in the next recursion
348 in_str := remainder_str ;
349
350 --dbms_output.put_line( '----------------------');
351 --dbms_output.put_line( 'in_str is' || in_str);
352 --dbms_output.put_line( 'remainder_str is' || remainder_str);
353 --dbms_output.put_line( 'repl_token is' || repl_token);
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 RAISE FND_API.G_EXC_ERROR;
357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 WHEN OTHERS THEN
360 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
361 FND_MESSAGE.SET_TOKEN('PROC','HZ_TRANS_PKG.replace_maximal_substr');
362 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
363 FND_MSG_PUB.ADD;
364 FND_MSG_PUB.ADD;
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END get_repl_token ;
367
368 -- word replace delimited word replacements
369 FUNCTION word_replace_delimited (
370 p_input_str IN VARCHAR2,
371 p_replacement_set IN NUMBER,
372 p_language IN VARCHAR2,
373 p_has_spc IN BOOLEAN DEFAULT FALSE,
374 p_noise_word_check IN VARCHAR2 DEFAULT 'N'
375 )
376 RETURN VARCHAR2 IS
377
378 -- VJN CHANGED CURSOR TO TAKE INTO ACCOUNT THAT THIS COULD BE OPENED
379 -- FOR THE FOLLOWING SITUATIONS :
380 -- 1. WORD REPLACEMENT FOR ANY WORD WHETHER IT IS A NOISE WORD OR NOT
381 -- 2. WORD REPLACEMENT FOR NOISE WORDS
382
383 CURSOR c_wl_repl_string(cp_wl_id NUMBER, cp_orig_word
384 VARCHAR2, cp_replacement_range VARCHAR2 ) IS
385 SELECT replacement_word, condition_id, user_spec_cond_value
386 FROM HZ_WORD_REPLACEMENTS
387 WHERE WORD_LIST_ID = cp_wl_id
388 AND ORIGINAL_WORD = cp_orig_word
389 AND ((staging_context = 'Y' AND DELETE_FLAG = 'N')
390 OR (nvl(staging_context,'N') = 'N' AND STAGED_FLAG = 'Y')
391 )
392 AND (REPLACEMENT_WORD IS NULL OR REPLACEMENT_WORD like cp_replacement_range)
393 and condition_id is not null
394 order by condition_id desc ;
395
396 exstr VARCHAR2(4000);
397 l_repl_str VARCHAR2(4000);
398 l_orig_word VARCHAR2(4000);
399 l_repl_word VARCHAR2(4000);
400 l_tok VARCHAR2(4000);
401 l_spc_char NUMBER;
402 l_wl_id NUMBER;
403 pos NUMBER;
404
405 -- VJN introduced variables
406 l_remainder_str VARCHAR2(4000);
407 l_repl_token VARCHAR2(4000);
408 l_in_str VARCHAR2(4000);
409 l_concat_repl_token VARCHAR2(4000);
410 l_non_delimited_flag VARCHAR2(1) ;
411 l_condition_id NUMBER ;
412 l_user_spec_cond_value VARCHAR2(2000);
413 l_replacement_range VARCHAR2(1);
414
415 NONE BOOLEAN := TRUE ;
416
417 BEGIN
418
419
420 IF (p_input_str IS NULL OR p_input_str = '') then
421 return '';
422 end if;
423
424 exstr := p_input_str ;
425 l_wl_id := p_replacement_set;
426
427 l_repl_str:='';
428 IF p_has_spc THEN
429 l_tok := HZ_DQM_SEARCH_UTIL.strtok(exstr,1,' DUMMY');
430 ELSE
431 l_tok := HZ_DQM_SEARCH_UTIL.strtok(exstr,1,' ');
432 END IF;
433
434 -- Determine the range of the word replacement
435 -- Is it for a noise word scenario or a general one
436
437 IF p_noise_word_check = 'Y'
438 THEN
439 --dbms_output.put_line('p_noise_word_check is ' || p_noise_word_check);
440 l_replacement_range := '' ;
441 ELSE
442 --dbms_output.put_line('p_noise_word_check is ' || p_noise_word_check );
443 l_replacement_range := '%' ;
444 END IF ;
445
446
447 ---VJN introduced changes to accomodate
448 -- word replacements which are conditional
449 WHILE l_tok IS NOT NULL ----> OUTER LOOP
450 LOOP
451 --dbms_output.put_line('l_tok is ' || l_tok);
452 l_repl_word := HZ_WORD_ARRAY_PKG.get_global_repl_word(p_replacement_set,l_tok) ;
453
454 -- NO REPLACEMENT WHATSOEVER EXISTS FOR L_TOK
455 IF l_repl_word = '-2'
456 THEN
457 l_repl_str := l_repl_str || ' '|| l_tok;
458
459 -- GLOBAL REPLACEMENT EXISTS EXISTS FOR L_TOK
460 ELSIF l_repl_word <> '-1' OR l_repl_word IS NULL
461 THEN
462
463 -- when we are doing the usual replacements, only non-null replacements matter.
464 IF p_noise_word_check = 'N' and l_repl_word IS NOT NULL
465 THEN
466 l_repl_str := l_repl_str || ' '|| l_repl_word ;
467 END IF ;
468
469 -- When we are doing noise word replacements, we basically ignore non-null replacements
470 IF p_noise_word_check = 'Y' and l_repl_word IS NOT NULL
471 THEN
472 l_repl_str := l_repl_str || ' '|| l_tok ;
473 END IF ;
474
475 -- CONDITIONAL :: Keep trying to do the replacement in a loop
476 -- until we do find a replacement, that passes the CONDITION.
477 ELSE
478 NONE := TRUE ;
479
480
481 OPEN c_wl_repl_string(l_wl_id,l_tok, l_replacement_range); --> CONDITIONAL LOOP FOR L_TOK BEGINS
482 LOOP
483 FETCH c_wl_repl_string INTO l_repl_word, l_condition_id, l_user_spec_cond_value;
484
485
486 --dbms_output.put_line('Before evaluating condition exstr is' || exstr );
487 --dbms_output.put_line('Before evaluating condition l_tok is' || l_tok );
488 --dbms_output.put_line('Before evaluating condition l_repl_word is' || l_repl_word );
489 --dbms_output.put_line('Before evaluating condition l_condition_id is' || l_condition_id );
490 --dbms_output.put_line('Before evaluating condition l_user_spec_cond_value is' || l_user_spec_cond_value);
491
492 -- Exit Loop when no rows found
493 IF c_wl_repl_string%NOTFOUND
494 THEN
495 exit ;
496 -- Evaluate condition and if satisfied exit loop after setting NONE
497 ELSIF HZ_WORD_CONDITIONS_PKG.evaluate_condition ( --------> CONDITION EVALUATION BEGINS
498 exstr,
499 l_tok,
500 l_repl_word,
501 l_condition_id,
502 l_user_spec_cond_value )
503 THEN
504 IF l_repl_word IS NOT NULL THEN
505 l_repl_str := l_repl_str || ' '|| l_repl_word;
506 END IF ;
507 NONE := FALSE ;
508 --dbms_output.put_line('After evaluating condition l_repl_str is' || l_repl_str );
509 exit ;
510
511
512 END IF; --------> CONDITION EVALUATION ENDS
513
514 END LOOP ; --> CONDITIONAL LOOP FOR L_TOK ENDS
515
516 -- NO CONDITIONS SATISFIED
517 IF NONE
518 THEN
519 l_repl_str := l_repl_str || ' '|| l_tok;
520 END IF ;
521
522
523 CLOSE c_wl_repl_string;
524
525 END IF ;
526
527
528 -- One time string tokenizer call per loop
529 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
530
531
532 END LOOP; ----> OUTER LOOP
533
534
535 l_repl_str := ltrim(l_repl_str);
536
537 --dbms_output.put_line('Returned String is' || l_repl_str );
538
539 RETURN l_repl_str;
540 EXCEPTION
541 WHEN FND_API.G_EXC_ERROR THEN
542 RAISE FND_API.G_EXC_ERROR;
543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 WHEN OTHERS THEN
546 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
547 FND_MESSAGE.SET_TOKEN('PROC','HZ_TRANS_PKG.word_replace_delimited');
548 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
549 FND_MSG_PUB.ADD;
550 FND_MSG_PUB.ADD;
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END word_replace_delimited ;
553
554 -- word replace non_delimited word replacements
555 FUNCTION word_replace_non_delimited (
556 p_input_str IN VARCHAR2,
557 p_replacement_set IN NUMBER,
558 p_language IN VARCHAR2,
559 p_has_spc IN BOOLEAN DEFAULT FALSE,
560 p_noise_word_check IN VARCHAR2 DEFAULT 'N'
561 )
562 RETURN VARCHAR2 IS
563
564 -- VJN CHANGED CURSOR TO TAKE INTO ACCOUNT THAT THIS COULD BE OPENED
565 -- FOR THE FOLLOWING SITUATIONS :
566 -- 1. WORD REPLACEMENT FOR ANY WORD WHETHER IT IS A NOISE WORD OR NOT
567 -- 2. WORD REPLACEMENT FOR NOISE WORDS
568
569 CURSOR c_wl_repl_string(cp_wl_id NUMBER, cp_orig_word
570 VARCHAR2, cp_replacement_range VARCHAR2 ) IS
571 SELECT replacement_word, condition_id, user_spec_cond_value
572 FROM HZ_WORD_REPLACEMENTS
573 WHERE WORD_LIST_ID = cp_wl_id
574 AND ORIGINAL_WORD = cp_orig_word
575 AND ((staging_context = 'Y' AND DELETE_FLAG = 'N')
576 OR (nvl(staging_context,'N') = 'N' AND STAGED_FLAG = 'Y')
577 )
578 AND (REPLACEMENT_WORD IS NULL OR REPLACEMENT_WORD like cp_replacement_range)
579 and condition_id is not null
580 order by condition_id desc ;
581
582 exstr VARCHAR2(4000);
583 l_repl_str VARCHAR2(4000);
584 l_orig_word VARCHAR2(4000);
585 l_repl_word VARCHAR2(4000);
586 l_tok VARCHAR2(4000);
587 l_spc_char NUMBER;
588 l_wl_id NUMBER;
589 pos NUMBER;
590
591 -- VJN introduced variables
592 l_remainder_str VARCHAR2(4000);
593 l_repl_token VARCHAR2(4000);
594 l_in_str VARCHAR2(4000);
595 l_concat_repl_token VARCHAR2(4000);
596 l_non_delimited_flag VARCHAR2(1) ;
597 l_condition_id NUMBER ;
598 l_user_spec_cond_value VARCHAR2(2000);
599 l_replacement_range VARCHAR2(1);
600
601 NONE BOOLEAN := TRUE ;
602
603 BEGIN
604
605
606 IF (p_input_str IS NULL OR p_input_str = '') then
607 return '';
608 end if;
609
610 exstr := p_input_str ;
611 l_wl_id := p_replacement_set;
612
613 l_repl_str:='';
614 IF p_has_spc THEN
615 l_tok := HZ_DQM_SEARCH_UTIL.strtok(exstr,1,' DUMMY');
616 ELSE
617 l_tok := HZ_DQM_SEARCH_UTIL.strtok(exstr,1,' ');
618 END IF;
619
620 -- Determine the range of the word replacement
621 -- Is it for a noise word scenario or a general one
622
623 IF p_noise_word_check = 'Y'
624 THEN
625 --dbms_output.put_line('p_noise_word_check is ' || p_noise_word_check);
626 l_replacement_range := '' ;
627 ELSE
628 --dbms_output.put_line('p_noise_word_check is ' || p_noise_word_check );
629 l_replacement_range := '%' ;
630 END IF ;
631
632
633 ---VJN introduced changes to accomodate
634 -- word replacements which are conditional
635 WHILE l_tok IS NOT NULL ----> OUTER LOOP
636 LOOP
637 --dbms_output.put_line('l_tok is ' || l_tok);
638 --dbms_output.put_line( 'l_repl_str is ' || '<' || l_repl_str || '>' );
639 l_repl_word := HZ_WORD_ARRAY_PKG.get_global_repl_word(p_replacement_set,l_tok) ;
640 --dbms_output.put_line( 'repl_word is ' || l_repl_word );
641
642
643 -- NO REPLACEMENT WHATSOEVER EXISTS FOR L_TOK
644 IF l_repl_word = '-2'
645 THEN
646 -- TRY NON-DELIMITED WORD REPLACEMENTS
647
648 -- initialize the in/out varibale to the token value during the first pass
649 l_in_str := l_tok ;
650 l_repl_token := '';
651 l_concat_repl_token := '';
652 l_remainder_str := '';
653 --dbms_output.put_line('About to enter non-delimited Loop');
654 LOOP ---> NON DELIMITED REPLACEMENT BEGINS
655 --dbms_output.put_line('Inside Loop');
656 IF l_in_str is null
657 THEN
658 EXIT;
659 END IF;
660 get_repl_token ( l_wl_id, l_tok, l_in_str, l_repl_token, l_remainder_str, l_replacement_range);
661 l_concat_repl_token := l_concat_repl_token || l_repl_token ;
662 --dbms_output.put_line('Concatented Replacement Token is ' || l_concat_repl_token );
663 END LOOP ; ---> NON DELIMITED REPLACEMENT ENDS
664 l_repl_str := l_repl_str || ' '|| l_concat_repl_token ;
665
666 -- GLOBAL REPLACEMENT EXISTS EXISTS FOR L_TOK
667 ELSIF l_repl_word <> '-1' OR l_repl_word IS NULL
668 THEN
669 -- when we are doing the usual replacements, only non-null replacements matter.
670 IF p_noise_word_check = 'N' and l_repl_word IS NOT NULL
671 THEN
672 l_repl_str := l_repl_str || ' '|| l_repl_word ;
673 END IF ;
674
675 -- When we are doing noise word replacements, we basically ignore non-null replacements
676 IF p_noise_word_check = 'Y' and l_repl_word IS NOT NULL
677 THEN
678 l_repl_str := l_repl_str || ' '|| l_tok ;
679 END IF ;
680
681 -- CONDITIONAL :: Keep trying to do the replacement in a loop
682 -- until we do find a replacement, that passes the CONDITION.
683 ELSE
684 NONE := TRUE ;
685
686 OPEN c_wl_repl_string(l_wl_id,l_tok, l_replacement_range); --> CONDITIONAL LOOP FOR L_TOK BEGINS
687 LOOP
688 FETCH c_wl_repl_string INTO l_repl_word, l_condition_id, l_user_spec_cond_value;
689
690
691 --dbms_output.put_line('Before evaluating condition exstr is' || exstr );
692 --dbms_output.put_line('Before evaluating condition l_tok is' || l_tok );
693 --dbms_output.put_line('Before evaluating condition l_repl_word is' || l_repl_word );
694 --dbms_output.put_line('Before evaluating condition l_condition_id is' || l_condition_id );
695 --dbms_output.put_line('Before evaluating condition l_user_spec_cond_value is' || l_user_spec_cond_value);
696
697 -- Exit Loop when no rows found
698 IF c_wl_repl_string%NOTFOUND
699 THEN
700 --dbms_output.put_line('Exiting with out evaluating condition');
701 exit ;
702 -- Evaluate condition and if true, exit after setting NONE
703 ELSIF HZ_WORD_CONDITIONS_PKG.evaluate_condition ( --------> CONDITION EVALUATION BEGINS
704 exstr,
705 l_tok,
706 l_repl_word,
707 l_condition_id,
708 l_user_spec_cond_value )
709 THEN
710 IF l_repl_word IS NOT NULL THEN
711 l_repl_str := l_repl_str || ' '|| l_repl_word;
712 END IF ;
713 NONE := FALSE ;
714 --dbms_output.put_line('After evaluating condition l_repl_str is' || l_repl_str );
715 exit ;
716
717
718 END IF; --------> CONDITION EVALUATION ENDS
719
720 END LOOP ; --> CONDITIONAL LOOP FOR L_TOK ENDS
721
722 CLOSE c_wl_repl_string;
723
724 -- NO CONDITIONS SATISFIED
725 IF NONE
726 THEN
727 -- TRY NON-DELIMITED WORD REPLACEMENTS
728 -- initialize the in/out varibale to the token value during the first pass
729 l_in_str := l_tok ;
730 l_repl_token := '';
731 l_concat_repl_token := '';
732 l_remainder_str := '';
733 --dbms_output.put_line('About to enter non-delimited Loop');
734 LOOP ---> NON DELIMITED REPLACEMENT BEGINS
735 -- --dbms_output.put_line('Inside Loop');
736 IF l_in_str is null
737 THEN
738 EXIT;
739 END IF;
740 get_repl_token ( l_wl_id, l_tok, l_in_str, l_repl_token, l_remainder_str, l_replacement_range);
741 l_concat_repl_token := l_concat_repl_token || l_repl_token ;
742 --dbms_output.put_line('Concatented Replacement Token is ' || l_concat_repl_token );
743 END LOOP ; ---> NON DELIMITED REPLACEMENT ENDS
744 l_repl_str := l_repl_str || ' '|| l_concat_repl_token ;
745 END IF ;
746
747 END IF ;
748
749
750 -- One time string tokenizer call per loop
751 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
752
753
754 END LOOP; ----> OUTER LOOP
755
756
757 l_repl_str := ltrim(l_repl_str);
758
759 --dbms_output.put_line('Returned String is' || l_repl_str );
760
761 RETURN l_repl_str;
762 EXCEPTION
763 WHEN FND_API.G_EXC_ERROR THEN
764 RAISE FND_API.G_EXC_ERROR;
765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 WHEN OTHERS THEN
768 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
769 FND_MESSAGE.SET_TOKEN('PROC','HZ_TRANS_PKG.word_replace_non_delimited');
770 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
771 FND_MSG_PUB.ADD;
772 FND_MSG_PUB.ADD;
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774 END word_replace_non_delimited ;
775
776
777 FUNCTION word_replace_private (
778 p_input_str IN VARCHAR2,
779 p_replacement_set IN NUMBER,
780 p_language IN VARCHAR2,
781 p_has_spc IN BOOLEAN DEFAULT FALSE,
782 p_noise_word_check IN VARCHAR2 DEFAULT 'N'
783 )
784 RETURN VARCHAR2 IS
785 BEGIN
786
787 -- Populate Word Array
788 HZ_WORD_ARRAY_PKG.populate_word_arrays(p_replacement_set);
789
790 -- Delimited Word Replacement
791 IF HZ_WORD_ARRAY_PKG.word_list_ndl_flag_lookup(p_replacement_set) = 'N'
792 THEN
793 RETURN word_replace_delimited (
794 p_input_str,
795 p_replacement_set,
796 p_language,
797 p_has_spc,
798 p_noise_word_check
799 ) ;
800
801
802 -- Non_Delimited Word Replacement
803 ELSE
804 RETURN word_replace_non_delimited (
805 p_input_str,
806 p_replacement_set,
807 p_language,
808 p_has_spc,
809 p_noise_word_check
810 ) ;
811
812 END IF ;
813
814
815 EXCEPTION
816 WHEN FND_API.G_EXC_ERROR THEN
817 RAISE FND_API.G_EXC_ERROR;
818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820 WHEN OTHERS THEN
821 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
822 FND_MESSAGE.SET_TOKEN('PROC','HZ_TRANS_PKG.word_replace_private');
823 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
824 FND_MSG_PUB.ADD;
825 FND_MSG_PUB.ADD;
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END word_replace_private ;
828
829
830
831
832
833
834 -- Version of word_replace that gets called with name of word list
835 FUNCTION word_replace (
836 p_input_str IN VARCHAR2,
837 p_word_list_name IN VARCHAR2,
838 p_language IN VARCHAR2)
839 RETURN VARCHAR2 IS
840
841 CURSOR c_wl_id IS
842 SELECT word_list_id FROM HZ_WORD_LISTS
843 WHERE word_list_name = p_word_list_name ;
844
845 l_wl_id NUMBER;
846
847 BEGIN
848
849 OPEN c_wl_id;
850 FETCH c_wl_id INTO l_wl_id;
851 IF c_wl_id%NOTFOUND THEN
852 CLOSE c_wl_id;
853 RETURN p_input_str;
854 ELSE
855 RETURN word_replace(p_input_str, l_wl_id , p_language) ;
856 END IF;
857
858 CLOSE c_wl_id;
859
860 EXCEPTION
861 WHEN FND_API.G_EXC_ERROR THEN
862 RAISE FND_API.G_EXC_ERROR;
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 WHEN OTHERS THEN
866 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
867 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.word_replace');
868 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
869 FND_MSG_PUB.ADD;
870 FND_MSG_PUB.ADD;
871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
872 END word_replace;
873
874 -- Version of word_replace that gets called with word list id
875 FUNCTION word_replace (
876 p_input_str IN VARCHAR2,
877 p_word_list_id IN NUMBER,
878 p_language IN VARCHAR2)
879 RETURN VARCHAR2 IS
880
881 BEGIN
882
883 RETURN word_replace_private( p_input_str, p_word_list_id, p_language) ;
884
885 EXCEPTION
886 WHEN FND_API.G_EXC_ERROR THEN
887 RAISE FND_API.G_EXC_ERROR;
888 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 WHEN OTHERS THEN
891 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
892 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.word_replace');
893 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
894 FND_MSG_PUB.ADD;
895 FND_MSG_PUB.ADD;
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897 END word_replace;
898
899
900 -- VJN Introduced change for Bug 3829926
901 -- This will do a word replacement, only when the replacement word is null.
902 -- In other words, it would only replace noise words in the input string
903
904 FUNCTION word_replace_noise_words_only (
905 p_input_str IN VARCHAR2,
906 p_word_list_id IN NUMBER,
907 p_language IN VARCHAR2,
908 p_has_spc IN BOOLEAN DEFAULT FALSE)
909 RETURN VARCHAR2 IS
910
911 BEGIN
912 --dbms_output.put_line(' calling word_replace with noise words flag as Y');
913 RETURN word_replace_private( p_input_str, p_word_list_id, p_language, p_has_spc, 'Y');
914
915 EXCEPTION
916 WHEN FND_API.G_EXC_ERROR THEN
917 RAISE FND_API.G_EXC_ERROR;
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920 WHEN OTHERS THEN
921 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
922 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.word_replace_noise_words_only');
923 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
924 FND_MSG_PUB.ADD;
925 FND_MSG_PUB.ADD;
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END word_replace_noise_words_only ;
928
929
930
931
932 /************* Simple Transformations *************************/
933
934 FUNCTION EXACT (
935 p_original_value IN VARCHAR2,
936 p_language IN VARCHAR2,
937 p_attribute_name IN VARCHAR2,
938 p_entity_name IN VARCHAR2)
939 RETURN VARCHAR2 IS
940
941 retstr varchar2(4000);
942 r1 varchar2(4000);
943
944 BEGIN
945 IF (p_original_value IS NULL OR p_original_value = '') then
946 return '';
947 END IF;
948 IF p_attribute_name = 'DUNS_NUMBER_C' THEN
949 RETURN RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
950 END IF;
951
952
953 retstr:= LTRIM(RTRIM(TRANSLATE(p_original_value,
954 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz&+-,./@'||g_latin_from ||'!"#$()''*:;<=>?[\]^_`{|}~'|| g_unprintables,
955 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ '||g_latin_to )));
956
957
958 WHILE instr(retstr,' ')>0 LOOP
959 r1 := retstr;
960 retstr := REPLACE(retstr, ' ',' ');
961 EXIT WHEN r1 = retstr;
962 END LOOP;
963
964 IF p_attribute_name like 'DUNS_NUMBER_C' then
965 return retstr||'='||lpad(retstr,9,'0');
966 END IF;
967
968 RETURN retstr;
969 EXCEPTION
970 WHEN FND_API.G_EXC_ERROR THEN
971 RAISE FND_API.G_EXC_ERROR;
972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 WHEN OTHERS THEN
975 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
976 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact');
977 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
978 FND_MSG_PUB.ADD;
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END EXACT;
981
982 FUNCTION CLEANSE (
983 p_original_value IN VARCHAR2,
984 p_language IN VARCHAR2,
985 p_attribute_name IN VARCHAR2,
986 p_entity_name IN VARCHAR2)
987 RETURN VARCHAR2 IS
988
989 retstr varchar2(4000);
990
991 BEGIN
992
993 IF (p_original_value IS NULL OR p_original_value = '') THEN
994 return '';
995 end if;
996
997 retstr := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
998
999 -- Remove Double letters
1000 retstr := replace(retstr,'AA','A');
1001 retstr := replace(retstr,'BB','B');
1002 retstr := replace(retstr,'CC','C');
1003 retstr := replace(retstr,'DD','D');
1004 retstr := replace(retstr,'EE','E');
1005 retstr := replace(retstr,'FF','F');
1006 retstr := replace(retstr,'GG','G');
1007 retstr := replace(retstr,'HH','H');
1008 retstr := replace(retstr,'II','I');
1009 retstr := replace(retstr,'JJ','J');
1010 retstr := replace(retstr,'KK','K');
1011 retstr := replace(retstr,'LL','L');
1012 retstr := replace(retstr,'MM','M');
1013 retstr := replace(retstr,'NN','N');
1014 retstr := replace(retstr,'OO','O');
1015 retstr := replace(retstr,'PP','P');
1016 retstr := replace(retstr,'QQ','Q');
1017 retstr := replace(retstr,'RR','R');
1018 retstr := replace(retstr,'SS','S');
1019 retstr := replace(retstr,'TT','T');
1020 retstr := replace(retstr,'UU','U');
1021 retstr := replace(retstr,'VV','V');
1022 retstr := replace(retstr,'WW','W');
1023 retstr := replace(retstr,'XX','X');
1024 retstr := replace(retstr,'YY','Y');
1025 retstr := replace(retstr,'ZZ','Z');
1026
1027 -- Remove Vowels (except from the 1st characters
1028 retstr := upper(translate(initcap(lower(retstr)),
1029 '%bcdfghjklmnpqrstvwxzyaeiou',
1030 '%bcdfghjklmnpqrstvwxz'));
1031 IF instr(retstr,'%')>0 THEN
1032 retstr := replace(retstr,'%A','%');
1033 retstr := replace(retstr,'%E','%');
1034 retstr := replace(retstr,'%I','%');
1035 retstr := replace(retstr,'%O','%');
1036 retstr := replace(retstr,'%U','%');
1037 retstr := replace(retstr,'%Y','%');
1038 END IF;
1039 RETURN retstr;
1040
1041 EXCEPTION
1042 WHEN FND_API.G_EXC_ERROR THEN
1043 RAISE FND_API.G_EXC_ERROR;
1044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1046 WHEN OTHERS THEN
1047 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1048 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact');
1049 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1050 FND_MSG_PUB.ADD;
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END CLEANSE;
1053
1054 FUNCTION Reverse_Phone_number(
1055 p_original_value IN VARCHAR2,
1056 p_language IN VARCHAR2,
1057 p_attribute_name IN VARCHAR2,
1058 p_entity_name IN VARCHAR2)
1059 RETURN VARCHAR2 IS
1060
1061 BEGIN
1062 RETURN substrb(hz_phone_number_pkg.transpose(p_original_value)
1063 ,1,7);
1064 END Reverse_Phone_number;
1065
1066 FUNCTION RM_SPLCHAR_CTX (
1067 p_original_value IN VARCHAR2,
1068 p_language IN VARCHAR2,
1069 p_attribute_name IN VARCHAR2,
1070 p_entity_name IN VARCHAR2,
1071 p_context IN VARCHAR2 )
1072 RETURN VARCHAR2 IS
1073
1074 BEGIN
1075 IF p_context='STAGE' THEN
1076 RETURN RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
1077 ELSIF p_context='SCORE' THEN
1078 RETURN NULL;
1079 ELSE
1080 RETURN RM_BLANKS(p_original_value,p_language,p_attribute_name,p_entity_name);
1081 END IF;
1082 END;
1083
1084 FUNCTION EXACT_DATE(
1085 p_original_value IN DATE,
1086 p_language IN VARCHAR2,
1087 p_attribute_name IN VARCHAR2,
1088 p_entity_name IN VARCHAR2)
1089 RETURN VARCHAR2 IS
1090 BEGIN
1091 RETURN TO_CHAR(p_original_value, 'DD-MON-YYYY');
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 RETURN null;
1095 END;
1096
1097 FUNCTION EXACT_NUMBER(
1098 p_original_value IN NUMBER,
1099 p_language IN VARCHAR2,
1100 p_attribute_name IN VARCHAR2,
1101 p_entity_name IN VARCHAR2)
1102 RETURN VARCHAR2 IS
1103 BEGIN
1104 RETURN TO_CHAR(p_original_value);
1105 EXCEPTION
1106 WHEN INVALID_NUMBER THEN
1107 RETURN NULL;
1108 WHEN OTHERS THEN
1109 RETURN NULL;
1110 END EXACT_NUMBER;
1111
1112 FUNCTION EXACT_EMAIL (
1113 p_original_value IN VARCHAR2,
1114 p_language IN VARCHAR2,
1115 p_attribute_name IN VARCHAR2,
1116 p_entity_name IN VARCHAR2)
1117 RETURN VARCHAR2 IS
1118
1119 retstr varchar2(1000);
1120
1121 BEGIN
1122 RETURN upper(p_original_value);
1123 EXCEPTION
1124 WHEN FND_API.G_EXC_ERROR THEN
1125 RAISE FND_API.G_EXC_ERROR;
1126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1128
1129 WHEN OTHERS THEN
1130 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1131 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.EXACT_EMAIL');
1132 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1133 FND_MSG_PUB.ADD;
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END EXACT_EMAIL;
1136
1137 FUNCTION RM_SPLCHAR (
1138 p_original_value IN VARCHAR2,
1139 p_language IN VARCHAR2,
1140 p_attribute_name IN VARCHAR2,
1141 p_entity_name IN VARCHAR2,
1142 p_context IN VARCHAR2 DEFAULT NULL) --bug 5128213
1143 RETURN VARCHAR2 IS
1144
1145 retstr varchar2(4000);
1146 r1 varchar2(4000);
1147 pos number;
1148 len number;
1149 BEGIN
1150 --bug 5128213 start
1151 IF p_context='STAGE'AND next_gen_dqm = 'Y' THEN
1152 return RM_SPLCHAR_BLANKS(p_original_value,p_language,p_attribute_name, p_entity_name,p_context);
1153 ELSE
1154 return RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);
1155 END IF;
1156 --bug 5128213 end
1157
1158 EXCEPTION
1159 WHEN FND_API.G_EXC_ERROR THEN
1160 RAISE FND_API.G_EXC_ERROR;
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163 WHEN OTHERS THEN
1164 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1165 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact');
1166 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1167 FND_MSG_PUB.ADD;
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END RM_SPLCHAR;
1170
1171 --bug 5128213 start
1172 FUNCTION RM_SPLCHAR_PRIVATE (
1173 p_original_value IN VARCHAR2,
1174 p_language IN VARCHAR2,
1175 p_attribute_name IN VARCHAR2,
1176 p_entity_name IN VARCHAR2)
1177 RETURN VARCHAR2 IS
1178
1179 retstr varchar2(4000);
1180 r1 varchar2(4000);
1181 pos number;
1182 len number;
1183 BEGIN
1184 IF (p_original_value IS NULL OR p_original_value = '') then
1185 return '';
1186 END IF;
1187
1188 retstr:= LTRIM(RTRIM(TRANSLATE(p_original_value,
1189 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.!"#$&'||g_latin_from ||'()''*+,-/:;<=>?@[\]^_`{|}~'|| g_unprintables,
1190 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ......'||g_latin_to )));
1191 WHILE instr(retstr,' ')>0 LOOP
1192 r1 := retstr;
1193 retstr := REPLACE(retstr, ' ',' ');
1194 EXIT WHEN r1 = retstr;
1195 END LOOP;
1196
1197 retstr:=REPLACE(retstr,' . ','.');
1198 retstr:=REPLACE(retstr,' .','.');
1199 retstr:=REPLACE(retstr,'. ','.');
1200
1201 pos := instr(retstr,'.');
1202 len := length(retstr);
1203 WHILE (pos > 0) LOOP
1204 IF pos + 2 > len THEN
1205 retstr := substr(retstr, 1, pos-1)||substr(retstr, pos+1);
1206 EXIT;
1207 ELSE
1208 IF (substr(retstr,pos+2,1) in (' ','.')) THEN
1209 retstr := substr(retstr, 1, pos-1)||substr(retstr, pos+1);
1210 len := len-2;
1211 pos := instr(retstr, '.');
1212 ELSE
1213 pos := instr(retstr, '.', pos+2);
1214 END IF;
1215 END IF;
1216 END LOOP;
1217 retstr:=REPLACE(retstr,'.',' ');
1218
1219 RETURN retstr;
1220 EXCEPTION
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 RAISE FND_API.G_EXC_ERROR;
1223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1225 WHEN OTHERS THEN
1226 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1227 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact');
1228 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1229 FND_MSG_PUB.ADD;
1230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1231 END RM_SPLCHAR_PRIVATE;
1232 --bug 5128213 end
1233
1234 FUNCTION RM_BLANKS (
1235 p_original_value IN VARCHAR2,
1236 p_language IN VARCHAR2,
1237 p_attribute_name IN VARCHAR2,
1238 p_entity_name IN VARCHAR2)
1239 RETURN VARCHAR2 IS
1240
1241 retstr varchar2(4000);
1242
1243 BEGIN
1244 IF (p_original_value IS NULL OR p_original_value = '') then
1245 return '';
1246 END IF;
1247
1248 retstr:= LTRIM(RTRIM(TRANSLATE(p_original_value,
1249 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'||g_latin_from || '!"#$&()''*+,-./:;<=>?@[\]^_`{|}~ '|| g_unprintables,
1250 '%0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'||g_latin_to)));
1251
1252 RETURN retstr;
1253 EXCEPTION
1254 WHEN FND_API.G_EXC_ERROR THEN
1255 RAISE FND_API.G_EXC_ERROR;
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1258 WHEN OTHERS THEN
1259 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1260 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact');
1261 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1262 FND_MSG_PUB.ADD;
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 END RM_BLANKS;
1265
1266 FUNCTION EXACT_URL (
1267 p_original_value IN VARCHAR2,
1268 p_language IN VARCHAR2,
1269 p_attribute_name IN VARCHAR2,
1270 p_entity_name IN VARCHAR2)
1271 RETURN VARCHAR2 IS
1272
1273 retstr varchar2(1000);
1274 l_pos NUMBER := 0;
1275 BEGIN
1276 IF (p_original_value IS NULL OR p_original_value = '') then
1277 return '';
1278 end if;
1279
1280 retstr := UPPER(p_original_value);
1281 IF instrb(retstr,'://')>0 THEN
1282 retstr := substrb(retstr,instrb(retstr,':/')+3);
1283 l_pos := instrb(retstr,'/');
1284 ELSIF instrb(retstr,':/')>0 THEN
1285 retstr := substrb(retstr,instrb(retstr,':/')+2);
1286 l_pos := instrb(retstr,'/');
1287 ELSE
1288 l_pos := instrb(retstr,'/');
1289 END IF;
1290
1291 IF l_pos > 1 THEN
1292 RETURN substrb(retstr,1,l_pos-1);
1293 ELSE
1294 RETURN retstr;
1295 END IF;
1296 EXCEPTION
1297 WHEN FND_API.G_EXC_ERROR THEN
1298 RAISE FND_API.G_EXC_ERROR;
1299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301
1302 WHEN OTHERS THEN
1303 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1304 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.CLEANSED_URL');
1305 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1306 FND_MSG_PUB.ADD;
1307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1308 END EXACT_URL;
1309
1310 FUNCTION SOUNDX (
1311 p_original_value VARCHAR2,
1312 p_language VARCHAR2,
1313 p_attribute_name VARCHAR2,
1314 p_entity_name VARCHAR2)
1315 RETURN VARCHAR2 IS
1316 BEGIN
1317
1318 RETURN soundex(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name));
1319
1320 EXCEPTION
1321 WHEN FND_API.G_EXC_ERROR THEN
1322 RAISE FND_API.G_EXC_ERROR;
1323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325
1326 WHEN OTHERS THEN
1327 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1328 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.SOUNDX');
1329 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1330 FND_MSG_PUB.ADD;
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END SOUNDX;
1333
1334 FUNCTION EXACT_PADDED (
1335 p_original_value IN VARCHAR2,
1336 p_language IN VARCHAR2,
1337 p_attribute_name IN VARCHAR2,
1338 p_entity_name IN VARCHAR2)
1339 RETURN VARCHAR2 IS
1340
1341 BEGIN
1342 return '#' || EXACT(p_original_value,p_language,p_attribute_name,p_entity_name) || '#'
1343 ;
1344 EXCEPTION
1345 WHEN FND_API.G_EXC_ERROR THEN
1346 RAISE FND_API.G_EXC_ERROR;
1347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349
1350 WHEN OTHERS THEN
1351 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1352 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.exact_padded');
1353 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1354 FND_MSG_PUB.ADD;
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 END EXACT_PADDED;
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375 /**************** Word Replacement Transformations **********/
1376
1377 FUNCTION CLEANSED_EMAIL (
1378 p_original_value IN VARCHAR2,
1379 p_language IN VARCHAR2,
1380 p_attribute_name IN VARCHAR2,
1381 p_entity_name IN VARCHAR2,
1382 p_context IN VARCHAR2)
1383 RETURN VARCHAR2 IS
1384
1385 retstr varchar2(2000);
1386
1387 BEGIN
1388 retstr := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1389
1390 IF (retstr IS NULL OR retstr = '') then
1391 RETURN '';
1392 END IF;
1393
1394 IF p_context = 'STAGE' THEN
1395 RETURN CLEANSED(word_replace(retstr, 5, p_language));
1396 ELSIF p_context = 'SCORE' THEN
1397 RETURN NULL;
1398 ELSE
1399 RETURN CLEANSED(word_replace(retstr, 5, p_language));
1400 END IF;
1401
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_ERROR THEN
1404 RAISE FND_API.G_EXC_ERROR;
1405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1407
1408 WHEN OTHERS THEN
1409 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1410 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.CLEANSED_EMAIL');
1411 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1412 FND_MSG_PUB.ADD;
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END CLEANSED_EMAIL;
1415
1416 FUNCTION CLEANSED_URL (
1417 p_original_value IN VARCHAR2,
1418 p_language IN VARCHAR2,
1419 p_attribute_name IN VARCHAR2,
1420 p_entity_name IN VARCHAR2,
1421 p_context IN VARCHAR2)
1422 RETURN VARCHAR2 IS
1423
1424 retstr varchar2(2000);
1425 BEGIN
1426 IF (p_original_value IS NULL OR p_original_value = '') then
1427 return '';
1428 end if;
1429
1430 retstr := EXACT_URL(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),
1431 p_language,p_attribute_name,p_entity_name);
1432
1433 IF (retstr IS NULL OR retstr = '') then
1434 RETURN '';
1435 END IF;
1436
1437 IF p_context = 'STAGE' THEN
1438 RETURN LTRIM(RTRIM(CLEANSED(word_replace(retstr, 5, p_language))));
1439 ELSIF p_context = 'SCORE' THEN
1440 RETURN NULL;
1441 ELSE
1442 RETURN LTRIM(RTRIM(CLEANSED(word_replace(retstr, 5, p_language))));
1443 END IF;
1444 EXCEPTION
1445 WHEN FND_API.G_EXC_ERROR THEN
1446 RAISE FND_API.G_EXC_ERROR;
1447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449
1450 WHEN OTHERS THEN
1451 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
1452 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.CLEANSED_URL');
1453 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1454 FND_MSG_PUB.ADD;
1455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1456 END CLEANSED_URL;
1457 FUNCTION person_exact_old_private(
1458 p_original_value IN VARCHAR2,
1459 p_language IN VARCHAR2,
1460 p_attribute_name IN VARCHAR2,
1461 p_entity_name IN VARCHAR2,
1462 p_context IN VARCHAR2)
1463 RETURN VARCHAR2 IS
1464 str VARCHAR2(2000);
1465 wrstr VARCHAR2(2000);
1466 first VARCHAR2(2000);
1467 rem VARCHAR2(2000);
1468 str1 VARCHAR2(2000);
1469
1470 -- VJN Introduced
1471 noise_removed_orig VARCHAR2(2000);
1472
1473 BEGIN
1474 IF p_context = 'STAGE' THEN
1475 BEGIN
1476 IF g_last_wrperson_orig = p_original_value THEN
1477 RETURN g_last_wrperson_exact;
1478 END IF;
1479 g_last_wrperson_orig := p_original_value;
1480 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1481 g_last_wrperson_exact:=str;
1482 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1483 IF str <> str1 THEN
1484 str := str ||' '||str1;
1485 END IF;
1486
1487 -- VJN Introduced change for Bug 3829926
1488 -- VJN Get a copy of the string to be word replaced, after removing the noise words
1489
1490 noise_removed_orig := word_replace_noise_words_only(str,3,p_language);
1491 wrstr := word_replace(str,3,p_language);
1492
1493 g_last_wrperson_exact:=wrstr;
1494
1495
1496 first := FIRST_WORD(noise_removed_orig,rem);
1497 IF noise_removed_orig <> wrstr THEN
1498 g_last_wrperson_exact := wrstr||' '||noise_removed_orig ||' '||first;
1499 ELSE
1500 g_last_wrperson_exact := noise_removed_orig ||' '||first;
1501 END IF;
1502 RETURN g_last_wrperson_exact;
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 IF sqlcode=-6502 THEN
1506 RETURN g_last_wrperson_exact;
1507 ELSE
1508 RAISE;
1509 END IF;
1510 END;
1511 ELSIF p_context = 'SCORE' THEN
1512 RETURN NULL;
1513 ELSE
1514 IF g_last_wrperson_orig = p_original_value THEN
1515 RETURN g_last_wrperson_exact;
1516 END IF;
1517 g_last_wrperson_orig := p_original_value;
1518 IF g_dqm_wildchar_search THEN
1519 g_last_wrperson_exact := word_replace(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),3,p_language);--bug 5128213
1520 ELSE
1521 g_last_wrperson_exact := word_replace(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),3,p_language);
1522 END IF;
1523
1524 RETURN g_last_wrperson_exact;
1525 END IF;
1526
1527 END person_exact_old_private;
1528
1529 FUNCTION WRPerson_Exact(
1530 p_original_value IN VARCHAR2,
1531 p_language IN VARCHAR2,
1532 p_attribute_name IN VARCHAR2,
1533 p_entity_name IN VARCHAR2,
1534 p_context IN VARCHAR2)
1535 RETURN VARCHAR2 IS
1536 BEGIN
1537 If next_gen_dqm = 'Y' THEN
1538 RETURN person_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1539 ELSE
1540 RETURN person_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1541 END IF;
1542 END WRPerson_Exact;
1543
1544 FUNCTION person_exact_new_private(
1545 p_original_value IN VARCHAR2,
1546 p_language IN VARCHAR2,
1547 p_attribute_name IN VARCHAR2,
1548 p_entity_name IN VARCHAR2,
1549 p_context IN VARCHAR2)
1550 RETURN VARCHAR2 IS
1551 str VARCHAR2(2000);
1552 wrstr VARCHAR2(2000);
1553 first VARCHAR2(2000);
1554 rem VARCHAR2(2000);
1555 str1 VARCHAR2(2000);
1556
1557 -- VJN Introduced
1558 noise_removed_orig VARCHAR2(2000);
1559
1560 BEGIN
1561 IF p_context = 'STAGE' THEN
1562 BEGIN
1563 IF g_last_wrperson_orig = p_original_value THEN
1564 RETURN g_last_wrperson_exact;
1565 END IF;
1566 g_last_wrperson_orig := p_original_value;
1567 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1568 g_last_wrperson_exact:=str;
1569 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1570 IF str <> str1 THEN
1571 str := str ||' '||str1;
1572 END IF;
1573
1574 wrstr := word_replace(str,3,p_language);
1575
1576 g_last_wrperson_exact:=wrstr;
1577 first := FIRST_WORD(g_last_wrperson_exact,rem);
1578 if(first <> g_last_wrperson_exact) then
1579 g_last_wrperson_exact := '!'||replace(g_last_wrperson_exact,' ')||'! !'||first||' '||g_last_wrperson_exact;
1580 else --there is no space
1581 g_last_wrperson_exact := '!'||g_last_wrperson_exact||'!';
1582 end if;
1583
1584 RETURN g_last_wrperson_exact;
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 IF sqlcode=-6502 THEN
1588 RETURN g_last_wrperson_exact;
1589 ELSE
1590 RAISE;
1591 END IF;
1592 END;
1593 ELSIF p_context = 'SCORE' THEN
1594 RETURN NULL;
1595 ELSE
1596 IF g_last_wrperson_orig = p_original_value THEN
1597 RETURN g_last_wrperson_exact;
1598 END IF;
1599 g_last_wrperson_orig := p_original_value;
1600 IF g_dqm_wildchar_search THEN
1601 g_last_wrperson_exact := word_replace(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),3,p_language);--bug 5128213
1602 ELSE
1603 g_last_wrperson_exact := word_replace(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),3,p_language);
1604 END IF;
1605
1606 RETURN g_last_wrperson_exact;
1607 END IF;
1608
1609 END person_exact_new_private;
1610
1611 FUNCTION WRPerson_Cleanse(
1612 p_original_value IN VARCHAR2,
1613 p_language IN VARCHAR2,
1614 p_attribute_name IN VARCHAR2,
1615 p_entity_name IN VARCHAR2,
1616 p_context IN VARCHAR2)
1617 RETURN VARCHAR2 IS
1618
1619 exstr VARCHAR2(4000);
1620 BEGIN
1621 IF p_context = 'STAGE' THEN
1622 RETURN CLEANSED(person_exact_old_private(p_original_value, p_language,p_attribute_name,
1623 p_entity_name,p_context));
1624 ELSIF p_context = 'SCORE' THEN
1625 RETURN cleansed_in_score_ctx(p_original_value);
1626 ELSE
1627 exstr := person_exact_old_private(p_original_value, p_language,p_attribute_name,
1628 p_entity_name,p_context);
1629 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
1630 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
1631 RETURN exstr;
1632 ELSE
1633 RETURN CLEANSED(exstr);
1634 END IF;
1635 END IF;
1636
1637 END WRPerson_Cleanse;
1638
1639 FUNCTION org_exact_old_private(
1640 p_original_value IN VARCHAR2,
1641 p_language IN VARCHAR2,
1642 p_attribute_name IN VARCHAR2,
1643 p_entity_name IN VARCHAR2,
1644 p_context IN VARCHAR2)
1645 RETURN VARCHAR2 IS
1646
1647 str VARCHAR2(2000);
1648 wrstr VARCHAR2(2000);
1649 first VARCHAR2(2000);
1650 rem VARCHAR2(2000);
1651 str1 VARCHAR2(2000);
1652
1653 -- VJN Introduced
1654 noise_removed_orig VARCHAR2(2000);
1655
1656 BEGIN
1657 IF p_context = 'STAGE' THEN
1658 BEGIN
1659 IF g_last_wrorg_orig = p_original_value THEN
1660 RETURN g_last_wrorg_exact;
1661 END IF;
1662 g_last_wrorg_orig := p_original_value;
1663
1664 str := EXACT(p_original_value,p_language,p_attribute_name, p_entity_name);
1665 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1666 IF str <> str1 THEN
1667 str := str ||' '||str1;
1668 END IF;
1669
1670 -- VJN Introduced change for Bug 3829926
1671 -- VJN Get a copy of the string to be word replaced, after removing the noise words
1672
1673 noise_removed_orig := word_replace_noise_words_only(str,2,p_language);
1674 g_last_wrorg_exact := word_replace(str,2,p_language);
1675
1676
1677
1678 --dbms_output.put_line('str is ' || str);
1679 --dbms_output.put_line('g_last_wrorg_exact is ' || g_last_wrorg_exact );
1680 --dbms_output.put_line('noise_removed_orig is ' || noise_removed_orig );
1681
1682 IF noise_removed_orig <> g_last_wrorg_exact THEN
1683 g_last_wrorg_exact := g_last_wrorg_exact||' '||noise_removed_orig;
1684 END IF;
1685
1686 RETURN g_last_wrorg_exact;
1687 EXCEPTION
1688 WHEN OTHERS THEN
1689 IF sqlcode=-6502 THEN
1690 RETURN g_last_wrorg_exact;
1691 ELSE
1692 RAISE;
1693 END IF;
1694 END;
1695 ELSIF p_context = 'SCORE' THEN
1696 RETURN NULL;
1697 ELSE
1698 IF g_last_wrorg_orig = p_original_value THEN
1699 RETURN g_last_wrorg_exact;
1700 END IF;
1701 g_last_wrorg_orig := p_original_value;
1702
1703 IF g_dqm_wildchar_search THEN
1704 g_last_wrorg_exact := word_replace(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),2,p_language);--bug 5128213
1705 ELSE
1706 g_last_wrorg_exact := word_replace(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),2,p_language);
1707 END IF;
1708
1709 RETURN g_last_wrorg_exact;
1710 END IF;
1711 END org_exact_old_private;
1712
1713 FUNCTION WROrg_Exact(
1714 p_original_value IN VARCHAR2,
1715 p_language IN VARCHAR2,
1716 p_attribute_name IN VARCHAR2,
1717 p_entity_name IN VARCHAR2,
1718 p_context IN VARCHAR2)
1719 RETURN VARCHAR2 IS
1720 BEGIN
1721 If next_gen_dqm = 'Y' THEN
1722 RETURN org_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1723 ELSE
1724 RETURN org_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1725 END IF;
1726 END WROrg_Exact;
1727
1728 FUNCTION org_exact_new_private(
1729 p_original_value IN VARCHAR2,
1730 p_language IN VARCHAR2,
1731 p_attribute_name IN VARCHAR2,
1732 p_entity_name IN VARCHAR2,
1733 p_context IN VARCHAR2)
1734 RETURN VARCHAR2 IS
1735
1736 str VARCHAR2(2000);
1737 wrstr VARCHAR2(2000);
1738 first VARCHAR2(2000);
1739 rem VARCHAR2(2000);
1740 str1 VARCHAR2(2000);
1741
1742 -- VJN Introduced
1743 noise_removed_orig VARCHAR2(2000);
1744
1745 BEGIN
1746 IF p_context = 'STAGE' THEN
1747 BEGIN
1748 IF g_last_wrorg_orig = p_original_value THEN
1749 RETURN g_last_wrorg_exact;
1750 END IF;
1751 g_last_wrorg_orig := p_original_value;
1752
1753 str := EXACT(p_original_value,p_language,p_attribute_name, p_entity_name);
1754 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1755 IF str <> str1 THEN
1756 str := str ||' '||str1;
1757 END IF;
1758
1759 g_last_wrorg_exact := word_replace(str,2,p_language);
1760 first := FIRST_WORD(g_last_wrorg_exact,rem);
1761 if(first <> g_last_wrorg_exact) then
1762 g_last_wrorg_exact := '!'||replace(g_last_wrorg_exact,' ')||'! !'||first||' '||g_last_wrorg_exact;
1763 else --no space
1764 g_last_wrorg_exact := '!'||g_last_wrorg_exact||'!';
1765 end if;
1766
1767 --dbms_output.put_line('str is ' || str);
1768 --dbms_output.put_line('g_last_wrorg_exact is ' || g_last_wrorg_exact );
1769 --dbms_output.put_line('noise_removed_orig is ' || noise_removed_orig );
1770
1771 RETURN g_last_wrorg_exact;
1772 EXCEPTION
1773 WHEN OTHERS THEN
1774 IF sqlcode=-6502 THEN
1775 RETURN g_last_wrorg_exact;
1776 ELSE
1777 RAISE;
1778 END IF;
1779 END;
1780 ELSIF p_context = 'SCORE' THEN
1781 RETURN NULL;
1782 ELSE
1783 IF g_last_wrorg_orig = p_original_value THEN
1784 RETURN g_last_wrorg_exact;
1785 END IF;
1786 g_last_wrorg_orig := p_original_value;
1787
1788 IF g_dqm_wildchar_search THEN
1789 g_last_wrorg_exact := word_replace(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),2,p_language);--bug 5128213
1790 ELSE
1791 g_last_wrorg_exact := word_replace(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),2,p_language);
1792 END IF;
1793
1794 RETURN g_last_wrorg_exact;
1795 END IF;
1796 END org_exact_new_private;
1797
1798
1799 FUNCTION WROrg_Cleanse(
1800 p_original_value IN VARCHAR2,
1801 p_language IN VARCHAR2,
1802 p_attribute_name IN VARCHAR2,
1803 p_entity_name IN VARCHAR2,
1804 p_context IN VARCHAR2)
1805 RETURN VARCHAR2 IS
1806
1807 exstr VARCHAR2(4000);
1808 BEGIN
1809 IF p_context = 'STAGE' THEN
1810 RETURN CLEANSED(org_exact_old_private(p_original_value, p_language,p_attribute_name,
1811 p_entity_name,p_context));
1812 ELSIF p_context = 'SCORE' THEN
1813 RETURN cleansed_in_score_ctx(p_original_value);
1814 ELSE
1815 exstr := org_exact_old_private(p_original_value, p_language,p_attribute_name,
1816 p_entity_name,p_context);
1817 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
1818 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
1819 RETURN exstr;
1820 ELSE
1821 RETURN CLEANSED(exstr);
1822 END IF;
1823 END IF;
1824 END WROrg_Cleanse;
1825
1826 FUNCTION PARTYNAMES_EXACT_OLD_PRIVATE(
1827 p_original_value IN VARCHAR2,
1828 p_language IN VARCHAR2,
1829 p_attribute_name IN VARCHAR2,
1830 p_entity_name IN VARCHAR2,
1831 p_context IN VARCHAR2)
1832 RETURN VARCHAR2 IS
1833 rem VARCHAR2(2000);
1834 str VARCHAR2(2000);
1835 wrstr VARCHAR2(2000);
1836 first VARCHAR2(2000);
1837 str1 VARCHAR2(2000);
1838 BEGIN
1839 IF p_context = 'STAGE' THEN
1840 IF g_last_wrnames_orig = p_original_value THEN
1841 RETURN g_last_wrnames_exact;
1842 END IF;
1843 g_last_wrnames_orig := p_original_value;
1844
1845 IF g_party_type = 'ORGANIZATION' THEN
1846 g_last_wrnames_exact := org_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1847 ELSIF g_party_type = 'PERSON' THEN
1848 g_last_wrnames_exact := person_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1849 ELSE
1850 g_last_wrnames_exact := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1851 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1852 IF g_last_wrnames_exact <> str1 THEN
1853 g_last_wrnames_exact := g_last_wrnames_exact ||' '||str1;
1854 END IF;
1855 END IF;
1856
1857 RETURN g_last_wrnames_exact;
1858 ELSIF p_context = 'SCORE' THEN
1859 RETURN NULL;
1860 ELSE
1861 IF g_last_wrnames_orig = p_original_value THEN
1862 RETURN g_last_wrnames_exact;
1863 END IF;
1864 g_last_wrnames_orig := p_original_value;
1865
1866 IF g_party_type = 'ORGANIZATION' THEN
1867 g_last_wrnames_exact := org_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1868 ELSIF g_party_type = 'PERSON' THEN
1869 g_last_wrnames_exact := person_exact_old_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1870 ELSE
1871 IF g_dqm_wildchar_search THEN
1872 str := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
1873 ELSE
1874 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1875 END IF;
1876 g_last_wrnames_exact:=str;
1877 END IF;
1878 RETURN g_last_wrnames_exact;
1879 END IF;
1880 END PARTYNAMES_EXACT_OLD_PRIVATE;
1881
1882 FUNCTION WRNames_Exact(
1883 p_original_value IN VARCHAR2,
1884 p_language IN VARCHAR2,
1885 p_attribute_name IN VARCHAR2,
1886 p_entity_name IN VARCHAR2,
1887 p_context IN VARCHAR2)
1888 RETURN VARCHAR2 IS
1889 l_original_value VARCHAR2(4000); --Bug No: 4084085
1890 BEGIN
1891 -- Start of Bug No: 4084085
1892 l_original_value := p_original_value;
1893 if(p_context IS NULL OR p_context <> 'SEARCH') then
1894 l_original_value := RM_PERCENTAGE(l_original_value);
1895 end if;
1896 -- End of Bug No: 4084085
1897 If next_gen_dqm = 'Y' THEN
1898 RETURN partynames_exact_new_private(l_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1899 ELSE
1900 RETURN partynames_exact_old_private(l_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1901 END IF;
1902 END WRNames_Exact;
1903
1904 FUNCTION partynames_exact_new_private(
1905 p_original_value IN VARCHAR2,
1906 p_language IN VARCHAR2,
1907 p_attribute_name IN VARCHAR2,
1908 p_entity_name IN VARCHAR2,
1909 p_context IN VARCHAR2)
1910 RETURN VARCHAR2 IS
1911
1912 rem VARCHAR2(2000);
1913 str VARCHAR2(2000);
1914 wrstr VARCHAR2(2000);
1915 first VARCHAR2(2000);
1916 str1 VARCHAR2(2000);
1917 BEGIN
1918 IF p_context = 'STAGE' THEN
1919 IF g_last_wrnames_orig = p_original_value THEN
1920 RETURN g_last_wrnames_exact;
1921 END IF;
1922 g_last_wrnames_orig := p_original_value;
1923
1924 IF g_party_type = 'ORGANIZATION' THEN
1925 g_last_wrnames_exact := org_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1926 ELSIF g_party_type = 'PERSON' THEN
1927 g_last_wrnames_exact := person_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1928 ELSE
1929 g_last_wrnames_exact := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1930 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);--bug 5128213
1931 IF g_last_wrnames_exact <> str1 THEN
1932 g_last_wrnames_exact := g_last_wrnames_exact ||' '||str1;
1933 END IF;
1934 END IF;
1935
1936 RETURN g_last_wrnames_exact;
1937 ELSIF p_context = 'SCORE' THEN
1938 RETURN NULL;
1939 ELSE
1940 IF g_last_wrnames_orig = p_original_value THEN
1941 RETURN g_last_wrnames_exact;
1942 END IF;
1943 g_last_wrnames_orig := p_original_value;
1944
1945 IF g_party_type = 'ORGANIZATION' THEN
1946 g_last_wrnames_exact := org_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1947 ELSIF g_party_type = 'PERSON' THEN
1948 g_last_wrnames_exact := person_exact_new_private(p_original_value,p_language,p_attribute_name,p_entity_name,p_context);
1949 ELSE
1950 IF g_dqm_wildchar_search THEN
1951 str := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
1952 ELSE
1953 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
1954 END IF;
1955 g_last_wrnames_exact:=str;
1956 END IF;
1957 RETURN g_last_wrnames_exact;
1958 END IF;
1959 END partynames_exact_new_private;
1960
1961 FUNCTION WRNames_Cleanse(
1962 p_original_value IN VARCHAR2,
1963 p_language IN VARCHAR2,
1964 p_attribute_name IN VARCHAR2,
1965 p_entity_name IN VARCHAR2,
1966 p_context IN VARCHAR2)
1967 RETURN VARCHAR2 IS
1968
1969 exstr VARCHAR2(4000);
1970 l_original_value VARCHAR2(4000); -- Bug No: 4084085
1971 BEGIN
1972 -- Start of Bug No: 4084085
1973 l_original_value := p_original_value;
1974 if(p_context IS NULL OR p_context <> 'SEARCH') then
1975 l_original_value := RM_PERCENTAGE(l_original_value);
1976 end if;
1977 -- End of Bug No: 4084085
1978 IF p_context = 'STAGE' THEN
1979 RETURN CLEANSED(partynames_exact_old_private(l_original_value, p_language,p_attribute_name,
1980 p_entity_name,p_context));
1981 ELSIF p_context = 'SCORE' THEN
1982 RETURN cleansed_in_score_ctx(l_original_value);
1983 ELSE
1984 exstr := partynames_exact_old_private(l_original_value, p_language,p_attribute_name,
1985 p_entity_name,p_context);
1986 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
1987 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
1988 RETURN exstr;
1989 ELSE
1990 RETURN CLEANSED(exstr);
1991 END IF;
1992 END IF;
1993 END WRNames_Cleanse;
1994
1995 FUNCTION WRAddress_Exact(
1996 p_original_value IN VARCHAR2,
1997 p_language IN VARCHAR2,
1998 p_attribute_name IN VARCHAR2,
1999 p_entity_name IN VARCHAR2,
2000 p_context IN VARCHAR2)
2001 RETURN VARCHAR2 IS
2002
2003 str VARCHAR2(2000);
2004 wrstr VARCHAR2(2000);
2005 first VARCHAR2(2000);
2006 str1 VARCHAR2(2000);
2007
2008 -- VJN Introduced
2009 noise_removed_orig VARCHAR2(2000);
2010 BEGIN
2011 IF p_context = 'STAGE' THEN
2012 IF g_last_wraddr_orig = p_original_value THEN
2013 RETURN g_last_wraddr_exact;
2014 END IF;
2015 g_last_wraddr_orig := p_original_value;
2016
2017 str := EXACT(p_original_value,p_language,p_attribute_name, p_entity_name);
2018 str1 := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name, p_entity_name);
2019 IF str <> str1 THEN
2020 str := str ||' '||str1;
2021 END IF;
2022
2023
2024 -- VJN Introduced change for Bug 3829926
2025 -- VJN Get a copy of the string to be word replaced, after removing the noise words
2026
2027 noise_removed_orig := word_replace_noise_words_only(str,1,p_language);
2028 g_last_wraddr_exact := word_replace(str,1,p_language);
2029
2030 IF noise_removed_orig <> g_last_wraddr_exact THEN
2031 g_last_wraddr_exact := g_last_wraddr_exact||' '||noise_removed_orig;
2032 END IF;
2033 RETURN g_last_wraddr_exact;
2034 ELSIF p_context = 'SCORE' THEN
2035 RETURN NULL;
2036 ELSE
2037 IF g_last_wraddr_orig = p_original_value THEN
2038 RETURN g_last_wraddr_exact;
2039 END IF;
2040 g_last_wraddr_orig := p_original_value;
2041
2042 IF g_dqm_wildchar_search THEN
2043 g_last_wraddr_exact := word_replace(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),1,p_language);--bug 5128213
2044 ELSE
2045 g_last_wraddr_exact := word_replace(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),1,p_language);
2046 END IF;
2047 RETURN g_last_wraddr_exact;
2048 END IF;
2049 END WRAddress_Exact;
2050
2051 FUNCTION WRAddress_Cleanse(
2052 p_original_value IN VARCHAR2,
2053 p_language IN VARCHAR2,
2054 p_attribute_name IN VARCHAR2,
2055 p_entity_name IN VARCHAR2,
2056 p_context IN VARCHAR2)
2057 RETURN VARCHAR2 IS
2058
2059 exstr VARCHAR2(4000);
2060 BEGIN
2061 IF p_context = 'STAGE' THEN
2062 RETURN CLEANSED(WRAddress_Exact(p_original_value, p_language,p_attribute_name,
2063 p_entity_name,p_context));
2064 ELSIF p_context = 'SCORE' THEN
2065 RETURN cleansed_in_score_ctx(p_original_value);
2066 ELSE
2067 exstr := WRAddress_Exact(p_original_value, p_language,p_attribute_name,
2068 p_entity_name,p_context);
2069 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2070 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
2071 RETURN exstr;
2072 ELSE
2073 RETURN CLEANSED(exstr);
2074 END IF;
2075 END IF;
2076 END WRAddress_Cleanse;
2077
2078 FUNCTION WRState_Exact(
2079 p_original_value IN VARCHAR2,
2080 p_language IN VARCHAR2,
2081 p_attribute_name IN VARCHAR2,
2082 p_entity_name IN VARCHAR2,
2083 p_context IN VARCHAR2)
2084 RETURN VARCHAR2 IS
2085
2086 str VARCHAR2(2000);
2087 wrstr VARCHAR2(2000);
2088 first VARCHAR2(2000);
2089 BEGIN
2090 IF g_last_wrstate_orig = p_original_value THEN
2091 RETURN g_last_wrstate_exact;
2092 END IF;
2093 g_last_wrstate_orig := p_original_value;
2094
2095 IF p_context = 'STAGE' THEN
2096 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
2097 g_last_wrstate_exact := word_replace_private(str,4,p_language,TRUE);
2098 IF str <> g_last_wrstate_exact THEN
2099 g_last_wrstate_exact := g_last_wrstate_exact ||' '||str;
2100 END IF;
2101 ELSIF p_context = 'SCORE' THEN
2102 g_last_wrstate_orig:='-1'; -- Bug No 7120851
2103 RETURN NULL;
2104 ELSE
2105 g_last_wrstate_exact := word_replace_private(EXACT(p_original_value,p_language,p_attribute_name,p_entity_name),4,p_language,TRUE);
2106 END IF;
2107
2108 RETURN g_last_wrstate_exact;
2109 END WRState_Exact;
2110
2111 FUNCTION WRState_Cleanse(
2112 p_original_value IN VARCHAR2,
2113 p_language IN VARCHAR2,
2114 p_attribute_name IN VARCHAR2,
2115 p_entity_name IN VARCHAR2,
2116 p_context IN VARCHAR2)
2117 RETURN VARCHAR2 IS
2118
2119 exstr VARCHAR2(4000);
2120 BEGIN
2121 IF p_context = 'STAGE' THEN
2122 RETURN CLEANSED(WRState_Exact(p_original_value, p_language,p_attribute_name,
2123 p_entity_name,p_context));
2124 ELSIF p_context = 'SCORE' THEN
2125 RETURN cleansed_in_score_ctx(p_original_value);
2126 ELSE
2127 exstr := WRState_Exact(p_original_value, p_language,p_attribute_name,
2128 p_entity_name,p_context);
2129 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2130 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
2131 RETURN exstr;
2132 ELSE
2133 RETURN CLEANSED(exstr);
2134 END IF;
2135 END IF;
2136 END WRState_Cleanse;
2137
2138 FUNCTION Basic_WRNames (
2139 p_original_value IN VARCHAR2,
2140 p_language IN VARCHAR2,
2141 p_attribute_name IN VARCHAR2,
2142 p_entity_name IN VARCHAR2,
2143 p_context IN VARCHAR2)
2144 RETURN VARCHAR2 IS
2145
2146 wrstr VARCHAR2(4000);
2147 str VARCHAR2(4000);
2148 BEGIN
2149 IF p_context='SCORE' THEN
2150 RETURN NULL;
2151 END IF;
2152
2153 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
2154 IF g_party_type = 'ORGANIZATION' THEN
2155 RETURN word_replace(str,2,p_language);
2156 ELSIF g_party_type = 'PERSON' THEN
2157 RETURN word_replace(str,3,p_language);
2158 ELSIF g_party_type IS NOT NULL THEN
2159 RETURN str;
2160 ELSE
2161 wrstr:=word_replace(str,3,p_language);
2162 IF (wrstr = str) THEN
2163 RETURN word_replace(str,2,p_language);
2164 ELSE
2165 RETURN wrstr;
2166 END IF;
2167 END IF;
2168 RETURN g_last_wrnames_exact;
2169 END;
2170
2171 FUNCTION Basic_Cleanse_WRNames (
2172 p_original_value IN VARCHAR2,
2173 p_language IN VARCHAR2,
2174 p_attribute_name IN VARCHAR2,
2175 p_entity_name IN VARCHAR2,
2176 p_context IN VARCHAR2)
2177 RETURN VARCHAR2 IS
2178
2179 exstr VARCHAR2(4000);
2180
2181 BEGIN
2182 IF g_match_rule_purpose = 'Q' THEN
2183 exstr := Basic_WRNames(p_original_value, p_language,p_attribute_name,
2184 p_entity_name,p_context);
2185 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2186 RETURN NULL;
2187 END IF;
2188 RETURN CLEANSED(exstr);
2189 ELSE
2190 IF p_context = 'STAGE' THEN
2191 RETURN CLEANSED(Basic_WRNames(p_original_value, p_language,p_attribute_name,
2192 p_entity_name,p_context));
2193 ELSIF p_context = 'SCORE' THEN
2194 RETURN cleansed_in_score_ctx(p_original_value);
2195 ELSE
2196 exstr := Basic_WRNames(p_original_value, p_language,p_attribute_name,
2197 p_entity_name,p_context);
2198 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2199 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
2200 RETURN exstr;
2201 ELSE
2202 RETURN CLEANSED(exstr);
2203 END IF;
2204 END IF;
2205 END IF;
2206 END Basic_Cleanse_WRNames;
2207
2208
2209 FUNCTION Basic_WRPerson (
2210 p_original_value IN VARCHAR2,
2211 p_language IN VARCHAR2,
2212 p_attribute_name IN VARCHAR2,
2213 p_entity_name IN VARCHAR2,
2214 p_context IN VARCHAR2)
2215 RETURN VARCHAR2 IS
2216
2217 wrstr VARCHAR2(4000);
2218 str VARCHAR2(4000);
2219 BEGIN
2220 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
2221 IF p_context='SCORE' THEN
2222 RETURN NULL;
2223 END IF;
2224
2225 RETURN word_replace(str,3,p_language);
2226 END;
2227
2228 FUNCTION Basic_Cleanse_WRPerson (
2229 p_original_value IN VARCHAR2,
2230 p_language IN VARCHAR2,
2231 p_attribute_name IN VARCHAR2,
2232 p_entity_name IN VARCHAR2,
2233 p_context IN VARCHAR2)
2234 RETURN VARCHAR2 IS
2235
2236 exstr VARCHAR2(4000);
2237
2238 BEGIN
2239 IF g_match_rule_purpose = 'Q' THEN
2240 exstr := Basic_WRPerson(p_original_value, p_language,p_attribute_name,
2241 p_entity_name,p_context);
2242 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2243 RETURN NULL;
2244 END IF;
2245 RETURN CLEANSED(exstr);
2246 ELSE
2247 IF p_context = 'STAGE' THEN
2248 RETURN CLEANSED(Basic_WRPerson(p_original_value, p_language,p_attribute_name,
2249 p_entity_name,p_context));
2250 ELSIF p_context = 'SCORE' THEN
2251 RETURN cleansed_in_score_ctx(p_original_value);
2252 ELSE
2253 exstr := Basic_WRPerson(p_original_value, p_language,p_attribute_name,
2254 p_entity_name,p_context);
2255 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2256 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
2257 RETURN exstr;
2258 ELSE
2259 RETURN CLEANSED(exstr);
2260 END IF;
2261 END IF;
2262 END IF;
2263 END Basic_Cleanse_WRPerson;
2264
2265 FUNCTION Basic_WRAddr (
2266 p_original_value IN VARCHAR2,
2267 p_language IN VARCHAR2,
2268 p_attribute_name IN VARCHAR2,
2269 p_entity_name IN VARCHAR2,
2270 p_context IN VARCHAR2)
2271 RETURN VARCHAR2 IS
2272
2273 wrstr VARCHAR2(4000);
2274 str VARCHAR2(4000);
2275 BEGIN
2276 str := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
2277 IF p_context='SCORE' THEN
2278 RETURN NULL;
2279 END IF;
2280
2281 RETURN word_replace(str,1,p_language);
2282 END;
2283
2284 FUNCTION Basic_Cleanse_WRAddr (
2285 p_original_value IN VARCHAR2,
2286 p_language IN VARCHAR2,
2287 p_attribute_name IN VARCHAR2,
2288 p_entity_name IN VARCHAR2,
2289 p_context IN VARCHAR2)
2290 RETURN VARCHAR2 IS
2291
2292 exstr VARCHAR2(4000);
2293
2294 BEGIN
2295 IF g_match_rule_purpose = 'Q' THEN
2296 exstr := Basic_WRAddr(p_original_value, p_language,p_attribute_name,
2297 p_entity_name,p_context);
2298 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2299 RETURN NULL;
2300 END IF;
2301 RETURN CLEANSED(exstr);
2302 ELSE
2303 IF p_context = 'STAGE' THEN
2304 RETURN CLEANSED(Basic_WRAddr(p_original_value, p_language,p_attribute_name,
2305 p_entity_name,p_context));
2306 ELSIF p_context = 'SCORE' THEN
2307 RETURN cleansed_in_score_ctx(p_original_value);
2308 ELSE
2309 exstr := Basic_WRAddr(p_original_value, p_language,p_attribute_name,
2310 p_entity_name,p_context);
2311 IF HZ_DQM_SEARCH_UTIL.estimated_length(exstr) <g_threshold_length THEN
2312 g_exact_for_cleansed(g_exact_for_cleansed.COUNT+1):=exstr;
2313 RETURN exstr;
2314 ELSE
2315 RETURN CLEANSED(exstr);
2316 END IF;
2317 END IF;
2318 END IF;
2319 END Basic_Cleanse_WRAddr;
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334 /************************* Unused Transformations ****************/
2335
2336 FUNCTION CLUSTER_WORD (
2337 p_original_value IN VARCHAR2,
2338 p_language IN VARCHAR2,
2339 p_attribute_name IN VARCHAR2,
2340 p_entity_name IN VARCHAR2)
2341 RETURN VARCHAR2 IS
2342
2343 l_exstr VARCHAR2(4000);
2344 l_retstr VARCHAR2(4000);
2345 l_spc_char NUMBER;
2346 l_len NUMBER;
2347
2348 BEGIN
2349
2350 l_exstr := EXACT(p_original_value,p_language,p_attribute_name,p_entity_name);
2351 IF (l_exstr IS NULL OR l_exstr = '') THEN
2352 return '';
2353 end if;
2354
2355 l_spc_char := nvl(instr(l_exstr,' '),0);
2356 l_len := length(l_exstr);
2357
2358 IF l_spc_char=0 THEN
2359 RETURN substr(l_exstr,1,least(3,l_len));
2360 ELSE
2361 l_retstr := substr(l_exstr,1,least(3,(l_spc_char-1)));
2362 IF l_len>l_spc_char THEN
2363 l_retstr := l_retstr || ' ' || substr(l_exstr,l_spc_char+1,least(3,(l_len-l_spc_char)));
2364 END IF;
2365 END IF;
2366
2367 RETURN l_retstr;
2368
2369 EXCEPTION
2370 WHEN FND_API.G_EXC_ERROR THEN
2371 RAISE FND_API.G_EXC_ERROR;
2372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374
2375 WHEN OTHERS THEN
2376 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
2377 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.clusterw');
2378 FND_MESSAGE.SET_TOKEN('ERROR ' ,'val ' || p_original_value || ' ' || SQLERRM);
2379 FND_MSG_PUB.ADD;
2380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381 END CLUSTER_WORD;
2382
2383 FUNCTION ACRONYM (
2384 p_original_value VARCHAR2,
2385 p_language VARCHAR2,
2386 p_attribute_name VARCHAR2,
2387 p_entity_name VARCHAR2)
2388 RETURN VARCHAR2 IS
2389 BEGIN
2390
2391 RETURN TRANSLATE(INITCAP(LOWER(
2392 RM_SPLCHAR_PRIVATE(p_original_value, p_language,p_attribute_name,--bug 5128213
2393 p_entity_name))),
2394 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ',
2395 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
2396
2397 EXCEPTION
2398 WHEN FND_API.G_EXC_ERROR THEN
2399 RAISE FND_API.G_EXC_ERROR;
2400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2402
2403 WHEN OTHERS THEN
2404 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_ERROR');
2405 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_TRANS_PKG.abbrev');
2406 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2407 FND_MSG_PUB.ADD;
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2409 END ACRONYM;
2410
2411 FUNCTION REVERSE_NAME (
2412 p_original_value IN VARCHAR2,
2413 p_language IN VARCHAR2,
2414 p_attribute_name IN VARCHAR2,
2415 p_entity_name IN VARCHAR2)
2416 RETURN VARCHAR2 IS
2417
2418 l_exstr VARCHAR2(4000);
2419 l_retstr VARCHAR2(4000);
2420 l_spc_char NUMBER;
2421 l_spc_char1 NUMBER;
2422 len NUMBER;
2423
2424 BEGIN
2425 IF (p_original_value IS NULL OR p_original_value = '') THEN
2426 return '';
2427 end if;
2428
2429 l_exstr := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
2430
2431 l_spc_char := nvl(instr(l_exstr,' '),0);
2432 IF l_spc_char = 0 THEN
2433 RETURN l_exstr;
2434 END IF;
2435
2436 l_retstr := substr(l_exstr,1,l_spc_char-1);
2437
2438 l_exstr := substr(l_exstr,l_spc_char+1);
2439 l_spc_char := nvl(instr(l_exstr,' '),0);
2440 WHILE l_spc_char <> 0 LOOP
2441 l_exstr := substr(l_exstr,l_spc_char+1);
2442 l_spc_char := nvl(instr(l_exstr,' '),0);
2443 END LOOP;
2444 RETURN l_exstr || ' ' || l_retstr;
2445
2446 END REVERSE_NAME;
2447
2448 FUNCTION WRPerson_Cluster(
2449 p_original_value IN VARCHAR2,
2450 p_language IN VARCHAR2,
2451 p_attribute_name IN VARCHAR2,
2452 p_entity_name IN VARCHAR2,
2453 p_context IN VARCHAR2)
2454 RETURN VARCHAR2 IS
2455
2456 BEGIN
2457 RETURN CLUSTER_WORD(WRPerson_exact(p_original_value, p_language,p_attribute_name,
2458 p_entity_name,p_context), p_language,p_attribute_name,p_entity_name);
2459 END WRPerson_Cluster;
2460
2461 FUNCTION WROrg_Cluster(
2462 p_original_value IN VARCHAR2,
2463 p_language IN VARCHAR2,
2464 p_attribute_name IN VARCHAR2,
2465 p_entity_name IN VARCHAR2,
2466 p_context IN VARCHAR2)
2467 RETURN VARCHAR2 IS
2468
2469 BEGIN
2470 RETURN CLUSTER_WORD(WROrg_Exact(p_original_value, p_language,p_attribute_name,
2471 p_entity_name,p_context), p_language,p_attribute_name,p_entity_name);
2472 END WROrg_Cluster;
2473
2474 FUNCTION WRNames_Cluster(
2475 p_original_value IN VARCHAR2,
2476 p_language IN VARCHAR2,
2477 p_attribute_name IN VARCHAR2,
2478 p_entity_name IN VARCHAR2,
2479 p_context IN VARCHAR2)
2480 RETURN VARCHAR2 IS
2481
2482 BEGIN
2483 RETURN CLUSTER_WORD(WRNames_Exact(p_original_value, p_language,p_attribute_name,
2484 p_entity_name,p_context), p_language,p_attribute_name,p_entity_name);
2485 END WRNames_Cluster;
2486
2487 FUNCTION Reverse_WRNames_Cluster(
2488 p_original_value IN VARCHAR2,
2489 p_language IN VARCHAR2,
2490 p_attribute_name IN VARCHAR2,
2491 p_entity_name IN VARCHAR2)
2492 RETURN VARCHAR2 IS
2493
2494 BEGIN
2495 RETURN CLUSTER_WORD(WORD_REPLACE(WORD_REPLACE(
2496 REVERSE_NAME(p_original_value,p_language,p_attribute_name,p_entity_name),
2497 2,p_language),
2498 3,p_language),
2499 p_language,p_attribute_name,p_entity_name);
2500 END Reverse_WRNames_Cluster;
2501
2502 FUNCTION Reverse_WRNames_Cleanse(
2503 p_original_value IN VARCHAR2,
2504 p_language IN VARCHAR2,
2505 p_attribute_name IN VARCHAR2,
2506 p_entity_name IN VARCHAR2)
2507 RETURN VARCHAR2 IS
2508
2509 BEGIN
2510 RETURN CLEANSE(WORD_REPLACE(WORD_REPLACE(
2511 REVERSE_NAME(p_original_value,p_language,p_attribute_name,p_entity_name),
2512 2,p_language),
2513 3,p_language),
2514 p_language,p_attribute_name,p_entity_name);
2515 END Reverse_WRNames_Cleanse;
2516
2517 FUNCTION Reverse_WRPerson_Cluster(
2518 p_original_value IN VARCHAR2,
2519 p_language IN VARCHAR2,
2520 p_attribute_name IN VARCHAR2,
2521 p_entity_name IN VARCHAR2)
2522 RETURN VARCHAR2 IS
2523
2524 BEGIN
2525 RETURN CLUSTER_WORD(WORD_REPLACE(
2526 REVERSE_NAME(p_original_value,p_language,p_attribute_name,p_entity_name),
2527 3,p_language),
2528 p_language,p_attribute_name,p_entity_name);
2529 END Reverse_WRPerson_Cluster;
2530
2531 FUNCTION Reverse_WRPerson_Cleanse(
2532 p_original_value IN VARCHAR2,
2533 p_language IN VARCHAR2,
2534 p_attribute_name IN VARCHAR2,
2535 p_entity_name IN VARCHAR2)
2536 RETURN VARCHAR2 IS
2537
2538 BEGIN
2539 RETURN CLEANSE(WORD_REPLACE(
2540 REVERSE_NAME(p_original_value,p_language,p_attribute_name,p_entity_name),
2541 3,p_language),
2542 p_language,p_attribute_name,p_entity_name);
2543 END Reverse_WRPerson_Cleanse;
2544
2545 PROCEDURE set_staging_context (p_staging_context varchar2)
2546 IS
2547 BEGIN
2548 staging_context := nvl(p_staging_context,'N');
2549 END;
2550 --Start of Bug No: 3515419
2551 FUNCTION RM_SPLCHAR_BLANKS(
2552 p_original_value IN VARCHAR2,
2553 p_language IN VARCHAR2,
2554 p_attribute_name IN VARCHAR2,
2555 p_entity_name IN VARCHAR2,
2556 p_context IN VARCHAR2)
2557 RETURN VARCHAR2
2558 IS
2559 l_str VARCHAR2(2000);
2560 BEGIN
2561 IF p_context ='STAGE' THEN
2562 l_str := RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name);--bug 5128213
2563 if(instr(l_str,' ') >0 ) then
2564 return l_str ||' '|| RM_BLANKS(l_str,p_language,p_attribute_name,p_entity_name);
2565 else
2566 return l_str;
2567 end if;
2568 ELSE
2569 RETURN RM_BLANKS(RM_SPLCHAR_PRIVATE(p_original_value,p_language,p_attribute_name,p_entity_name),--bug 5128213
2570 p_language,p_attribute_name,p_entity_name);
2571 END IF;
2572 END;
2573 --End of Bug No: 3515419
2574
2575 -- Start of Bug No: 4084085
2576 FUNCTION RM_PERCENTAGE(p_original_value IN VARCHAR2)
2577 RETURN VARCHAR2
2578 IS
2579 BEGIN
2580 RETURN replace(p_original_value,'%');
2581 END;
2582 -- End of Bug No: 4084085
2583 --- THIS IS MAIN CODE FOR THE PACKAGE THAT GETS EXECUTED ONCE IN A SESSION, WHEN THIS PACKAGE GETS CALLED
2584
2585 BEGIN
2586 SELECT count(*) INTO l_is_wildchar
2587 FROM HZ_DQM_STAGE_LOG
2588 WHERE operation = 'STAGE_FOR_WILDCHAR_SEARCH' AND ROWNUM = 1 ;
2589 IF l_is_wildchar > 0 THEN
2590 g_dqm_wildchar_search := TRUE;
2591 END IF;
2592 -- a total of 64 latins
2593 g_latin_from := fnd_global.local_chr(50049)||fnd_global.local_chr(50081)||fnd_global.local_chr(50048)
2594 ||fnd_global.local_chr(50080)||fnd_global.local_chr(50050)||fnd_global.local_chr(50082)
2595 ||fnd_global.local_chr(50052)||fnd_global.local_chr(50084)||fnd_global.local_chr(50051)
2596 ||fnd_global.local_chr(50083)||fnd_global.local_chr(50053)||fnd_global.local_chr(50085)
2597 ||fnd_global.local_chr(50055)||fnd_global.local_chr(50087)||fnd_global.local_chr(50064)
2598 ||fnd_global.local_chr(50096)||fnd_global.local_chr(50057)||fnd_global.local_chr(50089)
2599 ||fnd_global.local_chr(50056)||fnd_global.local_chr(50088)||fnd_global.local_chr(50058)
2600 ||fnd_global.local_chr(50090)||fnd_global.local_chr(50059)||fnd_global.local_chr(50091)
2601 ||fnd_global.local_chr(50061)||fnd_global.local_chr(50093)||fnd_global.local_chr(50060)
2602 ||fnd_global.local_chr(50092)||fnd_global.local_chr(50062)||fnd_global.local_chr(50094)
2603 ||fnd_global.local_chr(50063)||fnd_global.local_chr(50095)||fnd_global.local_chr(50065)
2604 ||fnd_global.local_chr(50097)||fnd_global.local_chr(50070)||fnd_global.local_chr(50102)
2605 ||fnd_global.local_chr(50067)||fnd_global.local_chr(50099)||fnd_global.local_chr(50066)
2606 ||fnd_global.local_chr(50098)||fnd_global.local_chr(50068)||fnd_global.local_chr(50100)
2607 ||fnd_global.local_chr(50069)||fnd_global.local_chr(50101)||fnd_global.local_chr(50072)
2608 ||fnd_global.local_chr(50104)||fnd_global.local_chr(15712189)||fnd_global.local_chr(15712189)
2609 ||fnd_global.local_chr(50076)||fnd_global.local_chr(50108)||fnd_global.local_chr(50074)
2610 ||fnd_global.local_chr(50106)||fnd_global.local_chr(50073)||fnd_global.local_chr(50105)
2611 ||fnd_global.local_chr(50075)||fnd_global.local_chr(50107)||fnd_global.local_chr(50077)
2612 ||fnd_global.local_chr(50109)||fnd_global.local_chr(15712189)||fnd_global.local_chr(50111)
2613 ||fnd_global.local_chr(15712189)||fnd_global.local_chr(15712189)||fnd_global.local_chr(49825)
2614 ||fnd_global.local_chr(49855) ;
2615
2616 -- a total of 62 replacements
2617 g_latin_to := 'AAAAAAAAAAAACCDDEEEEEEEEIIIIIIIINNOOOOOOOOOOOOSSUUUUUUUUYYYYZZ' ;
2618 END;